<!DOCTYPE html><html><head>
<title>main</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<link rel="stylesheet" href="file:///c:\Users\dansh\.vscode\extensions\shd101wyy.markdown-preview-enhanced-0.5.13\node_modules\@shd101wyy\mume\dependencies\katex\katex.min.css">
<style>
/**
* prism.js Github theme based on GitHub's theme.
* @author Sam Clarke
*/
code[class*="language-"],
pre[class*="language-"] {
color: #333;
background: none;
font-family: Consolas, "Liberation Mono", Menlo, Courier, monospace;
text-align: left;
white-space: pre;
word-spacing: normal;
word-break: normal;
word-wrap: normal;
line-height: 1.4;
-moz-tab-size: 8;
-o-tab-size: 8;
tab-size: 8;
-webkit-hyphens: none;
-moz-hyphens: none;
-ms-hyphens: none;
hyphens: none;
}
/* Code blocks */
pre[class*="language-"] {
padding: .8em;
overflow: auto;
/* border: 1px solid #ddd; */
border-radius: 3px;
/* background: #fff; */
background: #f5f5f5;
}
/* Inline code */
:not(pre) > code[class*="language-"] {
padding: .1em;
border-radius: .3em;
white-space: normal;
background: #f5f5f5;
}
.token.comment,
.token.blockquote {
color: #969896;
}
.token.cdata {
color: #183691;
}
.token.doctype,
.token.punctuation,
.token.variable,
.token.macro.property {
color: #333;
}
.token.operator,
.token.important,
.token.keyword,
.token.rule,
.token.builtin {
color: #a71d5d;
}
.token.string,
.token.url,
.token.regex,
.token.attr-value {
color: #183691;
}
.token.property,
.token.number,
.token.boolean,
.token.entity,
.token.atrule,
.token.constant,
.token.symbol,
.token.command,
.token.code {
color: #0086b3;
}
.token.tag,
.token.selector,
.token.prolog {
color: #63a35c;
}
.token.function,
.token.namespace,
.token.pseudo-element,
.token.class,
.token.class-name,
.token.pseudo-class,
.token.id,
.token.url-reference .token.variable,
.token.attr-name {
color: #795da3;
}
.token.entity {
cursor: help;
}
.token.title,
.token.title .token.punctuation {
font-weight: bold;
color: #1d3e81;
}
.token.list {
color: #ed6a43;
}
.token.inserted {
background-color: #eaffea;
color: #55a532;
}
.token.deleted {
background-color: #ffecec;
color: #bd2c00;
}
.token.bold {
font-weight: bold;
}
.token.italic {
font-style: italic;
}
/* JSON */
.language-json .token.property {
color: #183691;
}
.language-markup .token.tag .token.punctuation {
color: #333;
}
/* CSS */
code.language-css,
.language-css .token.function {
color: #0086b3;
}
/* YAML */
.language-yaml .token.atrule {
color: #63a35c;
}
code.language-yaml {
color: #183691;
}
/* Ruby */
.language-ruby .token.function {
color: #333;
}
/* Markdown */
.language-markdown .token.url {
color: #795da3;
}
/* Makefile */
.language-makefile .token.symbol {
color: #795da3;
}
.language-makefile .token.variable {
color: #183691;
}
.language-makefile .token.builtin {
color: #0086b3;
}
/* Bash */
.language-bash .token.keyword {
color: #0086b3;
}
/* highlight */
pre[data-line] {
position: relative;
padding: 1em 0 1em 3em;
}
pre[data-line] .line-highlight-wrapper {
position: absolute;
top: 0;
left: 0;
background-color: transparent;
display: block;
width: 100%;
}
pre[data-line] .line-highlight {
position: absolute;
left: 0;
right: 0;
padding: inherit 0;
margin-top: 1em;
background: hsla(24, 20%, 50%,.08);
background: linear-gradient(to right, hsla(24, 20%, 50%,.1) 70%, hsla(24, 20%, 50%,0));
pointer-events: none;
line-height: inherit;
white-space: pre;
}
pre[data-line] .line-highlight:before,
pre[data-line] .line-highlight[data-end]:after {
content: attr(data-start);
position: absolute;
top: .4em;
left: .6em;
min-width: 1em;
padding: 0 .5em;
background-color: hsla(24, 20%, 50%,.4);
color: hsl(24, 20%, 95%);
font: bold 65%/1.5 sans-serif;
text-align: center;
vertical-align: .3em;
border-radius: 999px;
text-shadow: none;
box-shadow: 0 1px white;
}
pre[data-line] .line-highlight[data-end]:after {
content: attr(data-end);
top: auto;
bottom: .4em;
}html body{font-family:"Helvetica Neue",Helvetica,"Segoe UI",Arial,freesans,sans-serif;font-size:16px;line-height:1.6;color:#333;background-color:#fff;overflow:initial;box-sizing:border-box;word-wrap:break-word}html body>:first-child{margin-top:0}html body h1,html body h2,html body h3,html body h4,html body h5,html body h6{line-height:1.2;margin-top:1em;margin-bottom:16px;color:#000}html body h1{font-size:2.25em;font-weight:300;padding-bottom:.3em}html body h2{font-size:1.75em;font-weight:400;padding-bottom:.3em}html body h3{font-size:1.5em;font-weight:500}html body h4{font-size:1.25em;font-weight:600}html body h5{font-size:1.1em;font-weight:600}html body h6{font-size:1em;font-weight:600}html body h1,html body h2,html body h3,html body h4,html body h5{font-weight:600}html body h5{font-size:1em}html body h6{color:#5c5c5c}html body strong{color:#000}html body del{color:#5c5c5c}html body a:not([href]){color:inherit;text-decoration:none}html body a{color:#08c;text-decoration:none}html body a:hover{color:#00a3f5;text-decoration:none}html body img{max-width:100%}html body>p{margin-top:0;margin-bottom:16px;word-wrap:break-word}html body>ul,html body>ol{margin-bottom:16px}html body ul,html body ol{padding-left:2em}html body ul.no-list,html body ol.no-list{padding:0;list-style-type:none}html body ul ul,html body ul ol,html body ol ol,html body ol ul{margin-top:0;margin-bottom:0}html body li{margin-bottom:0}html body li.task-list-item{list-style:none}html body li>p{margin-top:0;margin-bottom:0}html body .task-list-item-checkbox{margin:0 .2em .25em -1.8em;vertical-align:middle}html body .task-list-item-checkbox:hover{cursor:pointer}html body blockquote{margin:16px 0;font-size:inherit;padding:0 15px;color:#5c5c5c;background-color:#f0f0f0;border-left:4px solid #d6d6d6}html body blockquote>:first-child{margin-top:0}html body blockquote>:last-child{margin-bottom:0}html body hr{height:4px;margin:32px 0;background-color:#d6d6d6;border:0 none}html body table{margin:10px 0 15px 0;border-collapse:collapse;border-spacing:0;display:block;width:100%;overflow:auto;word-break:normal;word-break:keep-all}html body table th{font-weight:bold;color:#000}html body table td,html body table th{border:1px solid #d6d6d6;padding:6px 13px}html body dl{padding:0}html body dl dt{padding:0;margin-top:16px;font-size:1em;font-style:italic;font-weight:bold}html body dl dd{padding:0 16px;margin-bottom:16px}html body code{font-family:Menlo,Monaco,Consolas,'Courier New',monospace;font-size:.85em !important;color:#000;background-color:#f0f0f0;border-radius:3px;padding:.2em 0}html body code::before,html body code::after{letter-spacing:-0.2em;content:"\00a0"}html body pre>code{padding:0;margin:0;font-size:.85em !important;word-break:normal;white-space:pre;background:transparent;border:0}html body .highlight{margin-bottom:16px}html body .highlight pre,html body pre{padding:1em;overflow:auto;font-size:.85em !important;line-height:1.45;border:#d6d6d6;border-radius:3px}html body .highlight pre{margin-bottom:0;word-break:normal}html body pre code,html body pre tt{display:inline;max-width:initial;padding:0;margin:0;overflow:initial;line-height:inherit;word-wrap:normal;background-color:transparent;border:0}html body pre code:before,html body pre tt:before,html body pre code:after,html body pre tt:after{content:normal}html body p,html body blockquote,html body ul,html body ol,html body dl,html body pre{margin-top:0;margin-bottom:16px}html body kbd{color:#000;border:1px solid #d6d6d6;border-bottom:2px solid #c7c7c7;padding:2px 4px;background-color:#f0f0f0;border-radius:3px}@media print{html body{background-color:#fff}html body h1,html body h2,html body h3,html body h4,html body h5,html body h6{color:#000;page-break-after:avoid}html body blockquote{color:#5c5c5c}html body pre{page-break-inside:avoid}html body table{display:table}html body img{display:block;max-width:100%;max-height:100%}html body pre,html body code{word-wrap:break-word;white-space:pre}}.markdown-preview{width:100%;height:100%;box-sizing:border-box}.markdown-preview .pagebreak,.markdown-preview .newpage{page-break-before:always}.markdown-preview pre.line-numbers{position:relative;padding-left:3.8em;counter-reset:linenumber}.markdown-preview pre.line-numbers>code{position:relative}.markdown-preview pre.line-numbers .line-numbers-rows{position:absolute;pointer-events:none;top:1em;font-size:100%;left:0;width:3em;letter-spacing:-1px;border-right:1px solid #999;-webkit-user-select:none;-moz-user-select:none;-ms-user-select:none;user-select:none}.markdown-preview pre.line-numbers .line-numbers-rows>span{pointer-events:none;display:block;counter-increment:linenumber}.markdown-preview pre.line-numbers .line-numbers-rows>span:before{content:counter(linenumber);color:#999;display:block;padding-right:.8em;text-align:right}.markdown-preview .mathjax-exps .MathJax_Display{text-align:center !important}.markdown-preview:not([for="preview"]) .code-chunk .btn-group{display:none}.markdown-preview:not([for="preview"]) .code-chunk .status{display:none}.markdown-preview:not([for="preview"]) .code-chunk .output-div{margin-bottom:16px}.scrollbar-style::-webkit-scrollbar{width:8px}.scrollbar-style::-webkit-scrollbar-track{border-radius:10px;background-color:transparent}.scrollbar-style::-webkit-scrollbar-thumb{border-radius:5px;background-color:rgba(150,150,150,0.66);border:4px solid rgba(150,150,150,0.66);background-clip:content-box}html body[for="html-export"]:not([data-presentation-mode]){position:relative;width:100%;height:100%;top:0;left:0;margin:0;padding:0;overflow:auto}html body[for="html-export"]:not([data-presentation-mode]) .markdown-preview{position:relative;top:0}@media screen and (min-width:914px){html body[for="html-export"]:not([data-presentation-mode]) .markdown-preview{padding:2em calc(50% - 457px + 2em)}}@media screen and (max-width:914px){html body[for="html-export"]:not([data-presentation-mode]) .markdown-preview{padding:2em}}@media screen and (max-width:450px){html body[for="html-export"]:not([data-presentation-mode]) .markdown-preview{font-size:14px !important;padding:1em}}@media print{html body[for="html-export"]:not([data-presentation-mode]) #sidebar-toc-btn{display:none}}html body[for="html-export"]:not([data-presentation-mode]) #sidebar-toc-btn{position:fixed;bottom:8px;left:8px;font-size:28px;cursor:pointer;color:inherit;z-index:99;width:32px;text-align:center;opacity:.4}html body[for="html-export"]:not([data-presentation-mode])[html-show-sidebar-toc] #sidebar-toc-btn{opacity:1}html body[for="html-export"]:not([data-presentation-mode])[html-show-sidebar-toc] .md-sidebar-toc{position:fixed;top:0;left:0;width:300px;height:100%;padding:32px 0 48px 0;font-size:14px;box-shadow:0 0 4px rgba(150,150,150,0.33);box-sizing:border-box;overflow:auto;background-color:inherit}html body[for="html-export"]:not([data-presentation-mode])[html-show-sidebar-toc] .md-sidebar-toc::-webkit-scrollbar{width:8px}html body[for="html-export"]:not([data-presentation-mode])[html-show-sidebar-toc] .md-sidebar-toc::-webkit-scrollbar-track{border-radius:10px;background-color:transparent}html body[for="html-export"]:not([data-presentation-mode])[html-show-sidebar-toc] .md-sidebar-toc::-webkit-scrollbar-thumb{border-radius:5px;background-color:rgba(150,150,150,0.66);border:4px solid rgba(150,150,150,0.66);background-clip:content-box}html body[for="html-export"]:not([data-presentation-mode])[html-show-sidebar-toc] .md-sidebar-toc a{text-decoration:none}html body[for="html-export"]:not([data-presentation-mode])[html-show-sidebar-toc] .md-sidebar-toc ul{padding:0 1.6em;margin-top:.8em}html body[for="html-export"]:not([data-presentation-mode])[html-show-sidebar-toc] .md-sidebar-toc li{margin-bottom:.8em}html body[for="html-export"]:not([data-presentation-mode])[html-show-sidebar-toc] .md-sidebar-toc ul{list-style-type:none}html body[for="html-export"]:not([data-presentation-mode])[html-show-sidebar-toc] .markdown-preview{left:300px;width:calc(100% - 300px);padding:2em calc(50% - 457px - 150px);margin:0;box-sizing:border-box}@media screen and (max-width:1274px){html body[for="html-export"]:not([data-presentation-mode])[html-show-sidebar-toc] .markdown-preview{padding:2em}}@media screen and (max-width:450px){html body[for="html-export"]:not([data-presentation-mode])[html-show-sidebar-toc] .markdown-preview{width:100%}}html body[for="html-export"]:not([data-presentation-mode]):not([html-show-sidebar-toc]) .markdown-preview{left:50%;transform:translateX(-50%)}html body[for="html-export"]:not([data-presentation-mode]):not([html-show-sidebar-toc]) .md-sidebar-toc{display:none}
/* Please visit the URL below for more information: */
/* https://shd101wyy.github.io/markdown-preview-enhanced/#/customize-css */
</style>
</head>
<body for="html-export">
<div class="mume markdown-preview ">
<h1 class="mume-header" id="etl%E8%AF%A6%E7%BB%86%E8%AE%BE%E8%AE%A1">ETL详细设计</h1>
<h2 class="mume-header" id="%E6%A8%A1%E5%9E%8B%E9%83%A8%E5%88%86"><strong>模型部分</strong></h2>
<h3 class="mume-header" id="%E5%BE%85%E6%B2%9F%E9%80%9A">待沟通</h3>
<ul>
<li>目前上游无法提供增量数据，所以每次批处理必须全量，效率低</li>
<li>受益人只有三要素证件号码、证件类型、姓名，上游客户号为null</li>
<li>客户等级是否有效的判断规则（有效标志IS_DELETE、失效日期END_DATE全为null，生效日期是否有作用），客户ID（CUSTOMER_ID）是否与核心客户号一致</li>
<li>数据字典中不同表之间存在大量的重复字段，且从测试环境来看，这些表存在差异，如保单数量:HEALTH_INSURANCE_LISTING << POLICY_INFORMATION < INSURANCEINFO，但没有文档说明，无法保证所取字段的正确性。</li>
<li>需提供结案日期</li>
<li>转办单主表(CC_SWFFLOWMAIN)中转办项目(complaintProject)为无意义字符串，子项目(sonproject)为空值</li>
<li>上游新供的保全表(edorinfo)中保全项(edortype)需要提供枚举值说明</li>
<li>投诉信息(CC_COMPLAINSPROMSG)主键(ComplainsProsId)与文档描述不一致，导致无法与投诉信息(cc_action_complaints)关联</li>
<li>POLICY_INFORMATION和INSURANCEINFO中都有保单状态字段APPFLAG，但枚举内容不一样，以哪个为准？</li>
<li>INSURANCEINFO.APPNTAGE职业名称都为空，OCCUPATIONNAME职业代码有值但需要codestring，UNDERWRITE_REPORT_LIST表是否可用？</li>
</ul>
<h3 class="mume-header" id="%E4%B8%80-%E4%B8%AA%E4%BA%BA%E4%BF%A1%E6%81%AFindividual">一、个人信息（Individual）</h3>
<ol>
<li>从保单主表（POLICY_INFORMATION）中获取投保人的上游客户号和五要素，得到<code>$投保人列表$</code></li>
</ol>
<p>code</p>
<pre class="language-text">SELECT CUSTOMERNO,NAME,sex,BIRTHDAY,IDTYPE,IDNO,min(CVALIDATE) FROM POLICY_INFORMATION GROUP BY CUSTOMERNO,NAME,sex,BIRTHDAY,IDTYPE,IDNO;
</pre>
<ol start="2">
<li>从个险承保清单自助分析表（POLICY_INFORMATION）中获取被保人的上游客户号和五要素，得到<code>$被保人列表$</code></li>
</ol>
<p>code</p>
<pre class="language-text">SELECT INSUREDNO,INSUREDNAME,INSUREDSEX,INSUREDBIRTHDAY,INSUREDIDTYPE,INSUREDIDNO,min(CVALIDATE) FROM POLICY_INFORMATION GROUP BY INSUREDNO,INSUREDNAME,INSUREDSEX,INSUREDBIRTHDAY,INSUREDIDTYPE,INSUREDIDNO;
</pre>
<ol start="3">
<li><s>从个险承保清单自助分析（HEALTH_INSURANCE_LISTING）中获取受益人的上游客户号和三要素，得到<code>$受益人列表$</code>，实际数据上游客户全为null，同时五要素不齐，故暂时不考虑</s></li>
</ol>
<p>code</p>
<pre class="language-text">SELECT BENEFICIARY_CUSTOMERNO,BENEFICIARY_NAME,BENEFICIARY_IDTYPE,BENEFICIARY_IDNO FROM HEALTH_INSURANCE_LISTING GROUP BY BENEFICIARY_CUSTOMERNO,BENEFICIARY_NAME,BENEFICIARY_IDTYPE,BENEFICIARY_IDNO;
</pre>
<ol start="4">
<li>将<code>$投保人列表$</code>，<code>$被保人列表$</code> 合并去重，得到<code>$投被保人列表$</code>，并按保单生效日期（CVALIDATE）从早到晚排序（客户号生成规则之一）</li>
<li>遍历<code>$投被保人列表$</code></li>
<li><strong>标识及证件信息</strong>：根据五要素生成CPID，并生成代理主键IndID同时将CPID以及列表中其他字段分别插入个人信息表（Individual）。注意：要根据证件类型，将证件号码插入不同的字段。（如：证件类型为0，则证件号码插入身份证字段）</li>
<li><strong>todo 等级信息</strong>：根据上游客户号，从客户等级表（T_CUSTOMER_CLASS）获取客户等级、贡献度分、家庭加分、续期加分、保单加分、总分值。</li>
</ol>
<p>code</p>
<pre class="language-text">select CLASS_VALUE,CONTRIBUTION_VALUE,AWARDED3,AWARDED2,AWARDED1,TOTAL_VALUE from T_CUSTOMER_CLASS where CUSTOMER_ID = 上游客户号
</pre>
<ol start="8">
<li><strong>籍贯、职业</strong>：根据上游客户号，从个险承保清单自助分析（HEALTH_INSURANCE_LISTING）获取投保人籍贯，投保人职业代码，投保人职业名称。注意，此表投保人和被保人都有职业代码名称，取最近的一条记录</li>
</ol>
<p>code</p>
<pre class="language-text">select APPNTHOMEADDRESS,APPNTOCCUPATIONCODE,APPNTOCCUPATIONNAME from HEALTH_INSURANCE_LISTING where APPNTNO = 上游客户号 order by POLAPPLYDATE desc
</pre>
<h3 class="mume-header" id="%E4%BA%8C-%E4%BF%9D%E5%8D%95insurancearrangement">二、保单（InsuranceArrangement）</h3>
<ol>
<li>从新契约签单明细清单（INSURANCEINFO）表中获取相关字段插入到InsuranceArrangement表，由于INSURANCEINFO表中是以保单号唯一的，所以直接插入。语法见下，字段映射详见模型文档</li>
</ol>
<p>code</p>
<pre class="language-text">INSERT INTO InsuranceArrangement(PolicyNo,PANo)
SELECT contno,prtno
FROM INSURANCEINFO
</pre>
<ol start="2">
<li>用保单信息表（POLICY_INFORMATION）去更新InsuranceArrangement，由于POLICY_INFORMATION不是以保单号唯一的，所以先要去重。由于星环inceptor不支持如下在update中使用group by的语法,所以可以对POLICY_INFORMATION按保单号去重后存入临时表<code>POLICY_INFORMATION_tmp</code></li>
</ol>
<p>code</p>
<pre class="language-text">insert into POLICY_INFORMATION_tmp(contno,CVALIDATE,ENDDATE)
SELECT contno,max(CVALIDATE),max(ENDDATE)
ROM POLICY_INFORMATION
GROUP BY contno
</pre>
<ol start="3">
<li>更新临时表<code>POLICY_INFORMATION_tmp</code>中投保人（ApplicantID），主被保险人（InsuredID）为individual表的个体ID(IndID),可根据POLICY_INFORMATION.CUSTOMERNO关联individual上游客户号（SCustID），得到IndID，支持update，临时表设置为ORC事务表</li>
</ol>
<p>code</p>
<pre class="language-text">UPDATE POLICY_INFORMATION_tmp
SET ApplicantID =
(SELECT ApplicantID
FROM individual
WHERE (POLICY_INFORMATION_tmp.CUSTOMERNO = individual.SCustID))
WHERE 1=1;
</pre>
<ol start="4">
<li>最后用临时表更新InsuranceArrangement（code2）,为了支持update，InsuranceArrangement表设置为ORC事务表。语法见下，字段映射详见模型文档</li>
</ol>
<p>code</p>
<pre class="language-text">UPDATE InsuranceArrangement
SET (PINDate,PMDate) =
(SELECT CVALIDATE,ENDDATE
FROM POLICY_INFORMATION_tmp
WHERE (POLICY_INFORMATION_tmp.contno = InsuranceArrangement.PolicyNo))
WHERE 1=1;
</pre>
<h3 class="mume-header" id="%E4%B8%89-%E7%90%86%E8%B5%94insuranceclaimthread">三、理赔（InsuranceClaimThread）</h3>
<ol>
<li>首先将个单理赔表（INSURANCE_CLAIM）按保单和赔案号去重并存入临时表<code>INSURANCE_CLAIM_tmp</code></li>
</ol>
<p>code</p>
<pre class="language-text">insert into INSURANCE_CLAIM_tmp(ontno,clmno,RISKDATE,INSUREDNO,RPTDATE,LLOCCURREASON,RGTDATE,ACCIDENTDETAIL,LLCLAIMSTATE)
SELECT contno,clmno,max(RISKDATE),max(INSUREDNO),max(RPTDATE),max(LLOCCURREASON),max(RGTDATE),max(ACCIDENTDETAIL),max(LLCLAIMSTATE)
FROM INSURANCE_CLAIM GROUP BY contno,clmno
</pre>
<ol start="2">
<li>更新<code>INSURANCE_CLAIM_tmp</code>中出险人为individual表的个体ID(IndID)</li>
<li>将<code>INSURANCE_CLAIM_tmp</code>相应字段直接插入到InsuranceClaimThread表中，字段映射详见模型文档。</li>
</ol>
<h3 class="mume-header" id="%E5%9B%9B-%E4%B8%AA%E4%BD%93%E5%85%B3%E7%B3%BBindrelationship">四、个体关系(IndRelationShip)</h3>
<ol>
<li>从保单信息表（POLICY_INFORMATION）获取投保人和被保人的关系并</li>
</ol>
<p>code</p>
<pre class="language-text">insert into IndRelationShip(RSType,IndID1,Name1,IDCard1,IndID2,Name2,IDCard2,RSSTime,Role2)
SELECT max(RELATIONTOAPPNT),CUSTOMERNO,max(NAME),max(IDNO),INSUREDNO,max(INSUREDNAME),max(INSUREDIDNO),min(CUSTOMGETPOLDATE),
CASE max(RELATIONTOAPPNT)
WHEN '子女' THEN case max(INSUREDSEX) when 1 THEN '女儿' WHEN 0 THEN '儿子' end
when '父母' THEN case max(INSUREDSEX) when 1 THEN '母亲' WHEN 0 THEN '父亲' end
WHEN '配偶' THEN case max(INSUREDSEX) when 1 THEN '妻子' WHEN 0 THEN '丈夫' end
when '祖父母、外祖父母' THEN case max(INSUREDSEX) when 1 THEN '(外)祖母' WHEN 0 THEN '(外)祖父' end
when '祖孙、外祖孙' THEN case max(INSUREDSEX) when 1 THEN '(外)孙女' WHEN 0 THEN '(外)祖孙' end
ELSE max(RELATIONTOAPPNT) END
FROM POLICY_INFORMATION
WHERE RELATIONTOAPPNT<>'本人'
GROUP BY CUSTOMERNO,INSUREDNO
</pre>
<ol start="2">
<li>更新IndRelationShip.Role1为IndRelationShip.Role2的相对角色，即role2为母亲则role1为父亲等</li>
<li>此时插入IndRelationShip的IndID1，IndID2为上游系统客户号，根据Individual.SCustID更新为Individual.IndID</li>
<li>根据Individual更新其他信息，如手机号等</li>
</ol>
<h3 class="mume-header" id="%E4%BA%94-%E6%8E%A5%E8%A7%A6communication">五、接触(Communication)</h3>
<ol>
<li></li>
</ol>
<h3 class="mume-header" id="%E5%85%AD-%E4%BA%8B%E4%BB%B6partytimeline">六、事件(PartyTimeLine)</h3>
<ol>
<li>目前主要有以下事件，一个事件一条记录，包括开始时间和结束时间</li>
</ol>
<ul>
<li><strong>投保</strong>：PRole='投保人'，Scenario='投保',SDate=InsuranceArrangement.PADate,EndDate=InsuranceArrangement.PISDate</li>
<li><strong>保全</strong>: PRole='投保人'，Scenario='保全',SDate=AUDIT_EDORLIST.CUSAPPDATE,EndDate = AUDIT_EDORLIST.EDORVALIDATE todo 等待上游确认edorinfo.edortype的保全项是否都已包含在AUDIT_EDORLIST.EDORNAME中？</li>
<li><strong>报案</strong>: PRole='投保人'，Scenario='报案',SDate=InsuranceClaimThread.CNDate,EndDate= TODO 等待上游提供结案日期字段</li>
<li><strong>投诉</strong>:</li>
<li><strong>咨询</strong>:</li>
</ul>
<h2 class="mume-header" id="%E6%A0%87%E7%AD%BE%E9%83%A8%E5%88%86"><strong>标签部分</strong></h2>
<ul>
<li>label1: 无来源。Individual.Education</li>
<li>label2: Individual.Occupation，逻辑见上</li>
<li>label3: 根据Individual.Birthday年代，更新为70后，80后等</li>
<li>Label4：</li>
<li>Label5</li>
<li>Label6</li>
<li>Label7</li>
<li>Label8</li>
<li>Label9:无来源。Individual.MaritalStat，查询个体关系表(IndRelationShip)，RSType = '配偶'，如果有记录则更新为已婚，否则更新为null</li>
<li>Label10</li>
<li>Label11</li>
<li>Label12：无来源。Individual.Drinking</li>
<li>Label13：无来源。Individual.Smoking</li>
<li>Label14</li>
<li>Label15</li>
<li>Label16</li>
<li>Label17</li>
<li>Label17</li>
<li>Label18</li>
<li>Label19</li>
<li>Label20</li>
<li>Label21</li>
<li>Label22</li>
<li>Label23</li>
<li>Label24：以上游客户号(Individual.SCustID)查询POLICY_INFORMATION表且APPFLAG = '有效'，若无记录则更新Individual.NOVPolicy为0，否则更新NOVPolicy为记录数。若NOVPolicy>0，则更新Individual.Label24为'持有寿险有效保单'，否则更新为'未持有寿险有效保单'</li>
<li>Label25：将Individual.NOVPolicy+Individual.NOIVPolicy的值更新ndividual.Label25</li>
<li>Label26：InsuranceArrangement.PolicyNo = AUDIT_LN_LIST.CONTNO，以AUDIT_LN_LIST.LNMONEY更新InsuranceArrangement.LoanMoney，以个体ID(Individual.IndID)查询InsuranceArrangement表(Individual.IndID=InsuranceArrangement.ApplicantID)，若无记录更新Individual.Label26为Null,若LoanMoney>0则更新Individual.Label26为'有贷款'，否则更新为'无贷款'</li>
<li>Label27</li>
<li>Label28</li>
<li>Label29</li>
<li>Label30：以上游客户号(Individual.SCustID)查询POLICY_INFORMATION表，若无记录则更新Individual.ISelf为NULL，若有记录且RELATIONTOAPPNT仅为'本人'则若无记录则更新Individual.ISelf为'仅本人投保'，若有记录且RELATIONTOAPPNT不为'本人'则若无记录则更新Individual.ISelf为'本人未投保'，否则更新Individual.ISelf为'本人已投保'</li>
<li>Label31：以上游客户号(Individual.SCustID)查询POLICY_INFORMATION表，若无记录则更新Individual.Iparent为NULL，若有记录且RELATIONTOAPPNT仅为'父母'则若无记录则更新Individual.Iparent为'仅父母投保'，若有记录且RELATIONTOAPPNT不为'父母'则若无记录则更新Individual.Iparent为'父母未投保'，否则更新Individual.Iparent为'父母已投保'</li>
<li>Label32：以上游客户号(Individual.SCustID)查询POLICY_INFORMATION表，若无记录则更新Individual.IChildren为NULL，若有记录且RELATIONTOAPPNT仅为'子女'则若无记录则更新Individual.IChildren为'仅子女投保'，若有记录且RELATIONTOAPPNT不为'子女'则若无记录则更新Individual.IChildren为'子女未投保'，否则更新Individual.IChildren为'子女已投保'</li>
<li>Label33：以上游客户号(Individual.SCustID)查询POLICY_INFORMATION表，若无记录则更新Individual.Imate为NULL，若有记录且RELATIONTOAPPNT仅为'配偶'则若无记录则更新Individual.Imate为'仅配偶投保'，若有记录且RELATIONTOAPPNT不为'配偶'则若无记录则更新Individual.Imate为'配偶未投保'，否则更新Individual.Imate为'配偶已投保'</li>
<li>Label34</li>
<li>Label35</li>
<li>Label36</li>
<li>Label37</li>
<li>Label38</li>
<li>Label39</li>
<li>Label40</li>
<li>Label41</li>
<li>Label42</li>
<li>Label43</li>
<li>Label44：以上游客户号(Individual.SCustID)查询POLICY_INFORMATION表且APPFLAG<>'有效'，若无记录则更新Individual.NOIVPolicy为0，否则更新NOIVPolicy为记录数。若NOIVPolicy>0，则更新Individual.Label44为'有失效保单'，否则更新为'无失效保单'</li>
<li>Label45：以上游客户号(Individual.SCustID)查询POLICY_INFORMATION表且riskperiod='长期险'，若无记录则更新Individual.Label45为NULL，若有记录且paycount= payendyear则更新Individual.Label45为'有缴费期满长险保单'，否则更新Individual.Label45为'无缴费期满长险保单'</li>
<li>Label46：以上游客户号(Individual.SCustID)查询POLICY_INFORMATION表，若无记录则更新Individual.Label46为NULL，若有记录且paycount= payendyear则更新Individual.Label46为'有续期缴费的保单'，否则更新Individual.Label46为'无有续期缴费的保单'</li>
<li>Label47</li>
<li>Label48：以个体ID(Individual.IndID)查询InsuranceArrangement表(Individual.IndID=InsuranceArrangement.ApplicantID)，取最早日期(PADate)更新Individual.FADate</li>
<li>Label49：以个体ID(Individual.IndID)查询InsuranceArrangement表(Individual.IndID=InsuranceArrangement.ApplicantID)，取最近日期(PADate)更新Individual.LAPPDate，最后用当前日期减去Individual.LAPPDate更新Label49</li>
<li>Label50：以个体ID(Individual.IndID)查询InsuranceArrangement表(Individual.IndID=InsuranceArrangement.ApplicantID)，以最近日期(PADate)的AgentChannel更新Individual.LPChannel</li>
<li>Label51：以个体ID(Individual.IndID)查询InsuranceArrangement表(Individual.IndID=InsuranceArrangement.ApplicantID)，以最近日期(PADate)的AgentOrg更新Individual.LPOrg</li>
<li>Label52：以个体ID(Individual.IndID)查询InsuranceArrangement表(Individual.IndID=InsuranceArrangement.ApplicantID)，以最早日期(PADate)的AgentChannel更新Individual.FAChannel</li>
<li>Label53：以个体ID(Individual.IndID)查询InsuranceArrangement表(Individual.IndID=InsuranceArrangement.ApplicantID)，以最早日期(PADate)的AgentOrg更新Individual.FAOrg</li>
<li>Label54：以个体ID(Individual.IndID)查询InsuranceArrangement表(Individual.IndID=InsuranceArrangement.ApplicantID)，以最近日期(PADate)的PolicyState更新Individual.LPState</li>
<li>Label55：以上游客户号(Individual.SCustID)查询AUDIT_EDORLIST表，更新Individual.LPOSType = EDORNAME，无记录则更新为NULL</li>
<li>Label56</li>
<li>Label57</li>
<li>Label58</li>
<li>Label59：以个体ID(Individual.IndID)查询InsuranceClaimThread表(Individual.IndID=InsuranceClaimThread.ApplicantID)， 以最近日期(CNDate)的ClaimCaseStatus更新Individual.CNStatus</li>
<li>Label61</li>
<li>Label62</li>
<li>Label63</li>
<li>Label64</li>
<li>Label65：以上游客户号(Individual.SCustID)查询POLICY_INFORMATION表，若无记录则更新Individual.Label65为NULL，若仅一条记录则更新Individual.Label65为'一单寿险客户'，否则更新为'非一单寿险客户'</li>
<li>Label66：以上游客户号(Individual.SCustID)查询POLICY_INFORMATION表，若无记录则更新Individual.Label66为NULL，若 paycount= payendyear更新Individual.Label66为'缴费期满客户'，否则更新为'非缴费期满客户'</li>
<li>Label67</li>
<li>Label68</li>
<li>Label69：Individual.CustClass，逻辑见上(一.7)</li>
<li>Label70</li>
<li>Label71</li>
<li>Label72</li>
<li>Label73</li>
<li>Label74</li>
<li>Label75</li>
<li>Label76</li>
<li>Label77：Individual.Birthday 距离当前日期5天内，更新Individual.Label77为'本人生日临近'，否则为NULL</li>
<li>Label78：以个体ID(Individual.IndID)查询IndRelationShip表，若Role2为'父亲'或'母亲'的IndID2的生日(通过IndID2再反向关联Individual)，则更新Individual.Label78为'父母生日临近'，否则为NULL</li>
<li>Label79：以个体ID(Individual.IndID)查询IndRelationShip表，若Role2为'儿子'或'女儿'的IndID2的生日(通过IndID2再反向关联Individual)，则更新Individual.Label79为'子女生日临近'，否则为NULL</li>
<li>Label80</li>
<li>Label81</li>
<li>Label82</li>
<li>Label83</li>
<li>Label84</li>
<li>Label85</li>
<li>Label86</li>
<li>Label87</li>
<li>Label88</li>
<li>Label89</li>
<li>Label90</li>
<li>Label91</li>
<li>Label92</li>
<li>Label93</li>
<li>Label94</li>
<li>Label95</li>
<li>Label96：根据Individual.Birthday算出当前年龄，如果50<=年龄<60，则更新Individual.Label96为'大龄'，否则为NULL</li>
<li>Label97：根据Individual.Birthday算出当前年龄，如果60<=年龄，则更新Individual.Label97为'高龄'，否则为NULL</li>
<li>Label98：根据Individual.Occupation，若为记者、律师、公务员、媒体、金融行业，则更新Individual.Label98为'敏感职业'，否则为NULL</li>
<li>Label99</li>
<li>Label100</li>
<li>Label101</li>
<li>Label102</li>
<li>Label103</li>
<li>Label104</li>
<li>Label105</li>
<li>Label106</li>
<li>Label107</li>
<li>Label108</li>
<li>Label109：以个体ID(Individual.IndID)查询InsuranceClaimThread，若ClaimCaseStatus='报案'且 InsuranceClaimThread.CNDate距当前日期超过7天，则更新Individual.Label109为'理赔报案追踪'</li>
<li>Label110：以个体ID(Individual.IndID)查询InsuranceClaimThread，若ClaimCaseStatus='受理' or '立案'，则更新Individual.Label110为'理赔处理中'</li>
<li>Label111</li>
<li>Label112：先以个体ID(Individual.IndID)得到InsuranceArrangement.PolicyNo，再以此关联AUDIT_EDORLIST，若有记录且保全状态EDORSTATE<>'确认生效' and '复核终止' and '强制终止'，则更新Individual.Label111为'保全处理中'</li>
<li>Label113</li>
<li>Label114</li>
<li>Label115</li>
<li>Label116</li>
<li>Label117</li>
<li>Label118</li>
</ul>
</div>
</body></html>