| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549 |
- <!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>
- <li>INSURANCEINFO.APPFLAG,POLICY_INFORMATION.APPFLAG,PERSONAL_INSURANCE.CONTSTATE,数据字典解释均为保单状态，但实际数据各不相同，以哪个为准？</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：根据Individual.Birthday算出年龄，儿童:[0,13),少年:[13,19),青年:[19,41),中年:[41,66),老年:[66,)</li>
- <li>Label5: todo:只有收件地址（POSTAL_INSURANCE.HOMEADDRESS）且无记录</li>
- <li>Label6: todo:只有收件地址（POSTAL_INSURANCE.HOMEADDRESS）且无记录</li>
- <li>Label7：保留暂不实现</li>
- <li>Label8：保留暂不实现</li>
- <li>Label9:todo:上游无直接来源。目前根据Individual.MaritalStat，查询个体关系表(IndRelationShip)，RSType = '配偶'，如果有记录则更新为已婚，否则更新为null</li>
- <li>Label10：查询IndRelationShip，RSType='父母'，首先将返回记录数更新Individual.COChild，若记录数为0，则更新本字段为'无子女'，若记录数为1，则更新本字段为'1孩'，若记录数大于等于2，则更新本字段为'2孩及以上'</li>
- <li>Label11：todo:无来源，逻辑需要说明清楚</li>
- <li>Label12：todo:无来源，逻辑需要说明清楚。Individual.Drinking</li>
- <li>Label13：todo:无来源，逻辑需要说明清楚。Individual.Smoking</li>
- <li>Label14：todo:无来源，逻辑需要说明清楚。Individual.Cdiseases</li>
- <li>Label15：todo:无来源，逻辑需要说明清楚。</li>
- <li>Label16：todo:无来源，逻辑需要说明清楚。Individual.FMHistory</li>
- <li>Label17：todo:无来源，逻辑需要说明清楚。Individual.Pregnancy</li>
- <li>Label18：todo:无来源，逻辑需要说明清楚。</li>
- <li>Label19：保留暂不实现</li>
- <li>Label20：todo:无来源，逻辑需要说明清楚。</li>
- <li>Label21：todo:无来源，Individual.PIncome:[0,120000],'低收入';(120000,1000000],'中产';(1000000,),'富人'</li>
- <li>Label22：todo:无来源，Individual.COVehicle:0,'无车';1,'1辆车';(1,),'2辆车以上'</li>
- <li>Label23：todo:无来源，Individual.CORP:0,'无房';1,'1套房';(1,),'2套房以上'</li>
- <li>Label24：以上游客户号(Individual.SCustID)查询POLICY_INFORMATION表（CUSTOMERNO）且APPFLAG = '有效'，若无记录则更新Individual.NOVPolicy为0，否则更新NOVPolicy为记录数。若NOVPolicy>0，则更新Individual.Label24为'持有寿险有效保单'</li>
- <li>Label25：根据Individual.NOVPolicy+Individual.NOIVPolicy的值更新此标签为：无保单、1件保单、2件保单、3-5件保单、6-10件保单、11-20件保单、20-50件保单、50件以上保单</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：todo:无来源，逻辑需要说明清楚。</li>
- <li>Label28：保留暂不实现</li>
- <li>Label29：以上游客户号(Individual.SCustID)查询POLICY_INFORMATION表（CUSTOMERNO），POLICY_INFORMATION.APPFLAG='有效' and POLICY_INFORMATION.PAYINTV='趸缴'，若有记录则更新Individual.NOSPInsurance和本标签为'有趸交保单'</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表（CUSTOMERNO），若无记录则更新Individual.Iparent为NULL，若有记录且RELATIONTOAPPNT仅为'父母'则若无记录则更新Individual.Iparent为'仅父母投保'，若有记录且RELATIONTOAPPNT不为'父母'则若无记录则更新Individual.Iparent为'父母未投保'，否则更新Individual.Iparent为'父母已投保'</li>
- <li>Label32：以上游客户号(Individual.SCustID)查询POLICY_INFORMATION表（CUSTOMERNO），若无记录则更新Individual.IChildren为NULL，若有记录且RELATIONTOAPPNT仅为'子女'则若无记录则更新Individual.IChildren为'仅子女投保'，若有记录且RELATIONTOAPPNT不为'子女'则若无记录则更新Individual.IChildren为'子女未投保'，否则更新Individual.IChildren为'子女已投保'</li>
- <li>Label33：以上游客户号(Individual.SCustID)查询POLICY_INFORMATION表（CUSTOMERNO），若无记录则更新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：todo:逻辑需要说明清楚（到上游源表和字段）。</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为'有缴费期满长险保单'</li>
- <li>Label46：以上游客户号(Individual.SCustID)查询POLICY_INFORMATION表，若无记录则更新Individual.Label46为NULL，若有记录且paycount= payendyear则更新Individual.Label46为'有续期缴费的保单'，否则更新Individual.Label46为'无有续期缴费的保单'</li>
- <li>Label47：todo:逻辑需要说明清楚（到上游源表和字段）。</li>
- <li>Label48：以个体ID(Individual.IndID)查询InsuranceArrangement表(Individual.IndID=InsuranceArrangement.ApplicantID)，取最早日期(PADate)并更新此标签为：首次投保日期XXXX-XX-XX</li>
- <li>Label49：以个体ID(Individual.IndID)查询InsuranceArrangement表(Individual.IndID=InsuranceArrangement.ApplicantID)，取最近日期(PADate)更新Individual.LAPPDate，最后用当前日期减去Individual.LAPPDate更新此标签为：最后一次投保距今XXX天</li>
- <li>Label50：以个体ID(Individual.IndID)查询InsuranceArrangement表(Individual.IndID=InsuranceArrangement.ApplicantID)，以最近日期(PADate)的AgentChannel更新Individual.LPChannel，并更新此标签为：'最近保单'+Individual.LPChannel</li>
- <li>Label51：以个体ID(Individual.IndID)查询InsuranceArrangement表(Individual.IndID=InsuranceArrangement.ApplicantID)，以最近日期(PADate)的AgentOrg更新Individual.LPOrg，并更新此标签为：'最近'+Individual.LPOrg+'机构'</li>
- <li>Label52：以个体ID(Individual.IndID)查询InsuranceArrangement表(Individual.IndID=InsuranceArrangement.ApplicantID)，以最早日期(PADate)的AgentChannel更新Individual.FAChannel，并更新此标签为：'首单'+Individual.FAChannel</li>
- <li>Label53：以个体ID(Individual.IndID)查询InsuranceArrangement表(Individual.IndID=InsuranceArrangement.ApplicantID)，以最早日期(PADate)的AgentOrg更新Individual.FAOrg，并更新此标签为：'首单'+Individual.FAOrg+'机构'</li>
- <li>Label54：以个体ID(Individual.IndID)查询InsuranceArrangement表(Individual.IndID=InsuranceArrangement.ApplicantID)，以最近日期(PADate)的PolicyState更新Individual.LPState，todo:逻辑需要说明清楚（到上游源表和字段）。</li>
- <li>Label55：由于上游保全类型信息分散在AUDIT_EDORLIST.EDORNAME和edorinfo.edortype 中，同时edorinfo.edortype为code需要转为string（具体映射见下）。以上游客户号(Individual.SCustID)分别查询AUDIT_EDORLIST表和edorinfo表，取保全生效日期最近的记录的更新的保全类型更新Individual.LPOSType。todo:edorinfo.edortype为code，需要转为string，edorinfo中需要增加上游客户号，否则需要多一次关联，建议大数据平台处理完成。</li>
- <li>Label56：以上游客户号(Individual.SCustID)查询POLICY_INFORMATION.CONTNO,再根据CONTNO查询INSURANCEINFO表 and INSURANCEINFO.APPFLAG = '已退保',若有记录则更新此标签为'有退保保单'</li>
- <li>Label57：以上游客户号(Individual.SCustID)查询POLICY_INFORMATION.CONTNO,再根据CONTNO查询AUDIT_CLAIM_INSURANCE表，取AUDIT_CLAIM_INSURANCE.RGTDATE最近对应的AUDIT_CLAIM_INSURANCE.RISKTYPE更新此标签。todo:实际数据仅有寿险、意外险、健康险，取值是否正确？</li>
- <li>Label58：todo:逻辑需要说明清楚（到上游源表和字段）。</li>
- <li>Label59：以个体ID(Individual.IndID)查询InsuranceClaimThread表(Individual.IndID=InsuranceClaimThread.ApplicantID)， 以最近日期(CNDate)的ClaimCaseStatus更新Individual.CNStatus，并更新此标签</li>
- <li>Label60：保留暂不实现</li>
- <li>Label61：todo:逻辑需要说明清楚（到上游源表和字段）。</li>
- <li>Label62：保留暂不实现</li>
- <li>Label63：todo:逻辑需要说明清楚（到上游源表和字段）。</li>
- <li>Label64：todo:逻辑需要说明清楚（到上游源表和字段）。</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：若Individual.Birthday的月日与上海人寿司庆日月日相同，则设置此标签为：'生日与司庆日同天'。todo:需要确定司庆日是几月几号</li>
- <li>Label68:以上游客户号(Individual.SCustID)查询POLICY_INFORMATION表, and POLICY_INFORMATION.APPFLAG='有效'，若有记录则设置此标签为'持有有效保单'，否则则设置为'未持有有效保单'</li>
- <li>Label69：Individual.CustClass，逻辑见上(一.7)</li>
- <li>Label70:Individual.IndID = PartyTimeLine.PartyID,取PartyTimeLine.SDate最近的记录对应的Scenario更新此标签</li>
- <li>Label71：Individual.IndID = PartyTimeLine.PartyID,取PartyTimeLine.SDate最近的记录对应的Channel更新此标签</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>
|
