工作参考sql

BQ-GRP

insert into sd_policycancle(

    PolicyNo,
    CancelDate,
    MakeDate
)
select g.grpcontno PolicyNo, to_char(m.ENDOR_VALIDATE,'YYYYMMDDHH') CancelDate,sysdate
  from lcgrpcont g, t_css_endor_main m
 where g.grpcontno = m.policy_no
   and m.endor_type in ('CT', 'HT', 'PS')
   and g.managecom like '8606%'
   and exists(select 1 from sd_policy where policyno = g.grpcontno)

   and trunc(m.close_time) = trunc(sysdate - 1)

BQ-LIS

insert into sd_policycancle
  (PolicyNo, CancelDate, MakeDate)
  select it.contno, to_char(it.EdorValiDate, 'YYYYMMDDHH'), sysdate
    from lpedoritem it, lpedormain ma
   where it.edoracceptno = ma.edoracceptno
     and edortype in ('YT', 'XT', 'CT')
     and ma.confdate = trunc(sysdate - 1)
     and exists (select 1 from sd_policy where policyno = it.contno)

BQ-PER

--@
insert into sd_policycancle(
     PolicyNo,
     CancelDate,
     makedate
)
select app.POL_NO,
       to_char(m.ENDOR_VALIDATE,'YYYYMMDDHH'),
       sysdate
  from t_nbs_application         app,
       t_css_endor_main          m
 where m.policy_no = app.POL_NO
   and m.endor_type in ('CT', 'HT', 'PS')
   and not exists (select 1
          from t_nbs_plan tnp
         where tnp.plan_code = app.plan_code
           and tnp.plan_type in ('03', '06', '08', '09'))
   and exists (select 1 from sd_policy where policyno = app.pol_no and state = '0')
   and trunc(m.close_time) = trunc(sysdate - 1 )

Cl-JIE-GRP

--@
INSERT INTO sd_claimj
  (claimno,
   groupclaimno,
   claimtype,
   claimdate,
   lossdate,
   losscause,
   lossarea,
   NAME,
   gender,
   birthday,
   credentialtype,
   credentialno,
   lossresult,
   lossresultdate,
   claimclosedate,
   claimamount,
   claimresult,
   makedate)
  SELECT distinct rc.claim_no claimno,
         rr.register_no groupclaimno,
         (SELECT codealias
            FROM ldcode
           WHERE codetype = 'gclsClaimTypeSD'
             AND code = rr.source_code) claimtype,
         to_char(rr.register_end_time, 'YYYYMMDDHH') claimdate,
         (SELECT to_char(ra.accident_date, 'YYYYMMDDHH')
            FROM t_cls_register_accident ra
           WHERE ra.claim_no = rc.claim_no
             AND rownum = 1) lossdate,
         (SELECT codealias
            FROM ldcode
           WHERE codetype = 'gclsLossCauseSD'
             AND code = (SELECT ra.loss_occurred_type
                           FROM t_cls_register_accident ra
                          WHERE ra.claim_no = rc.claim_no
                            AND rownum = 1)) losscause,
         (SELECT ra.loss_occurred_place
            FROM t_cls_register_accident ra
           WHERE ra.claim_no = rc.claim_no
             AND rownum = 1) lossarea,
         rc.insured_name NAME,
         (SELECT codealias
            FROM ldcode
           WHERE codetype = 'gclsPersonnelSexSD'
             AND code = rc.insured_sex) gender,
         (CASE
           WHEN rr.source_code = 'GHIS' THEN
            (SELECT to_char(birthday, 'YYYYMMDD')
               FROM gr_lcinsured
              WHERE insuredno = rc.insured_no
                AND rownum = 1)
           else
            (SELECT to_char(birthdate, 'YYYYMMDD')
               FROM t_nbs_client
              WHERE client_no = rc.insured_no
                AND rownum = 1)
         END) birthday，
         (SELECT codealias
            FROM ldcode
           WHERE codetype = 'gnbsIdTypeSD'
             AND code = rc.certificate_type) certificatetype,
         rc.certificate_no certificateno,
         (SELECT codealias
            FROM ldcode
           WHERE codetype = 'gclsLossResultSD'
             AND code = (SELECT cc.coverage_type_code
                           FROM t_cls_register_coverage cc,
                                t_cls_register_accident ra
                          WHERE ra.claim_no = rc.claim_no
                            AND cc.accident_no = cc.accident_no
                            AND rownum = 1)) lossresult,
         (SELECT to_char(ra.loss_occurred_date, 'YYYYMMDDHH')
            FROM t_cls_register_accident ra
           WHERE ra.claim_no = rc.claim_no
             AND rownum = 1) lossresultdate,
         to_char(ec.end_case_end_time, 'YYYYMMDDHH') claimclosedate,
         (SELECT SUM(cp.payment_amount)
            FROM t_cls_claim_payment cp
           WHERE cp.claim_no = rc.claim_no) claimamount,
         (SELECT codealias
            FROM ldcode
           WHERE codetype = 'gclsClaimResultSD'
             AND code = (SELECT ac.audit_conclusion_code
                           FROM t_cls_claim_audit_confirm ac
                          WHERE ac.claim_no = rc.claim_no)) claimresult,
         sysdate
    FROM t_cls_register_case rc, t_cls_register rr, t_cls_end_case ec
   WHERE rr.register_no = rc.register_no
     and rc.claim_no = ec.claim_no
     AND trunc(ec.end_case_end_time) = trunc(SYSDATE - 1)
     AND ec.submit_flag IN ('1', '3')
     AND exists (select  1 from sd_claiml where claimno = rc.claim_no and credentialno = rc.certificate_no)

--@
INSERT INTO sd_claimpolicy
  (policyno, claimno, polclaimamount)
  SELECT cp.policy_no, cp.claim_no, SUM(cp.payment_amount)
    FROM t_cls_claim_payment cp
   WHERE  exists (select 1 from sd_claimj where claimno = cp.claim_no and state is null)
           GROUP BY cp.policy_no, cp.claim_no




--@
INSERT INTO SD_ClaimCoverage
  (policyno, claimno, ComCoverageCode,CovClaimAmount)
  SELECT cp.policy_no, cp.claim_no, cp.risk_code,sum(cp.payment_amount)
    FROM t_cls_claim_payment cp
   WHERE exists (select 1 from sd_claimj where claimno = cp.claim_no and state is null)
          and exists (select 1 from sd_policyrisk ri where ri.policyno = cp.policy_no)
           GROUP BY cp.policy_no, cp.claim_no ,cp.risk_code

CL-JIE-LIS

--@
insert into sd_claimj (
   ClaimNo,
   GroupClaimNo,
   ClaimType,
   ClaimDate,
   LossDate,
   LossCause,
   LossArea,
   Name,
   Gender,
   Birthday,
   CredentialType,
   CredentialNo,
   LossResult,
   LossResultDate,
   ClaimCloseDate,
   ClaimAmount,
   ClaimResult,
   makedate
)
select m.clmno ,
       '' ,
       '02' ,
       to_char(rg.rgtdate, 'yyyymmddhh') ,
       to_char(rg.accidentdate, 'yyyymmddhh') ,
       '01' ,
       '' ,
       lp.name ,
       lp.sex ,
       to_char(lp.birthday, 'yyyymmdd') ,
       (case trim(lp.idtype)
         when '0' then
          '01'
         when '1' then
          '07'
         when '2' then
          '04'
         when '3' then
          '03'
         when '5' then
          '02'
         when '6' then
          '09'
         else
          '99'
       end) ,
       lp.idno ,
       (case (select SUBSTR(lar.reasoncode, 2, 4)
            from llappclaimreason lar
           where lar.caseno = rg.rgtno
             and rownum = 1)
         when '01' then
          '03'
         when '03' then
          '02'
         when '02' then
          '01'
         else
          '99'
       end)  ,
       to_char(rg.accidentdate, 'yyyymmddhh') ,
       to_char(rg.endcasedate, 'yyyymmddhh') ,
       (select cm.realpay from llclaim cm where cm.clmno = m.clmno) ,
       (case
         when m.auditconclusion = '0' then
          '01'
         when m.auditconclusion = '1' then
          '04'
         when m.auditconclusion = '4' then
          '02'
         when m.auditconclusion = '3' then
          '05'
         when m.auditconclusion = '6' then
          '03'
         when m.auditconclusion = '6' then
          '03'
         else
          '01'
       end) ,
       sysdate
  from llclaimuwmain m, llregister rg, ldperson lp, llcase lc
 where m.clmno = rg.rgtno
   and m.clmno = lc.caseno
   and lc.customerno = lp.customerno
   and rg.mngcom like '8606%'
   and exists (select 1 from sd_claimlpolice la where la.claimno = m.clmno )
   and rg.endcasedate = trunc(sysdate - 1)

--@
insert into SD_ClaimPolicy
  (ClaimNo, PolicyNo, PolClaimAmount,makedate)
  select clmno, lcp.contno, sum(lcp.realpay),sysdate
    from llclaimpolicy lcp
   where lcp.clmno in
         (select ja.claimno from sd_claimj ja where state is null)
   group by lcp.contno, lcp.clmno

--@
insert into SD_ClaimCoverage
  (PolicyNo,
   ClaimNo,
   ComCoverageCode,
   CovClaimAmount,
   makedate
   )
  select lcp.contno, lcp.clmno, lcp.riskcode, sum(lcp.realpay),sysdate
    from llclaimpolicy lcp
   where exists (select 1
            from sd_claimpolicy
           where policyno = lcp.contno
             and claimno = lcp.clmno)
   and lcp.riskcode in (select code from ldcode where codetype = 'risktypeSD' )
   group by lcp.riskcode, lcp.contno, lcp.clmno

CL-LI_LIS

INSERT INTO sd_claiml
  (claimno,
   groupclaimno,
   claimtype,
   claimdate,
   lossdate,
   losscause,
   lossarea,
   NAME,
   gender,
   birthday,
   credentialtype,
   credentialno,
   lossresult,
   lossresultdate,
   iscancelclaim,
   canceldate,
   makedate)
  SELECT rc.claim_no claimno,
         rr.register_no groupclaimno,
         (SELECT codealias
            FROM ldcode
           WHERE codetype = 'gclsClaimTypeSD'
             AND code = rr.source_code) claimtype,
         to_char(rr.register_end_time, 'YYYYMMDDHH') claimdate,
         (SELECT to_char(ra.accident_date, 'YYYYMMDDHH')
            FROM t_cls_register_accident ra
           WHERE ra.claim_no = rc.claim_no
             AND rownum = 1) lossdate,
         (SELECT codealias
            FROM ldcode
           WHERE codetype = 'gclsLossCauseSD'
             AND code = (SELECT ra.loss_occurred_type
                           FROM t_cls_register_accident ra
                          WHERE ra.claim_no = rc.claim_no
                            AND rownum = 1)) losscause,
         (SELECT ra.loss_occurred_place
            FROM t_cls_register_accident ra
           WHERE ra.claim_no = rc.claim_no
             AND rownum = 1) lossarea,
         rc.insured_name NAME,
         (SELECT codealias
            FROM ldcode
           WHERE codetype = 'gclsPersonnelSexSD'
             AND code = rc.insured_sex) gender,
         (CASE
           WHEN rr.source_code = 'GHIS' THEN
            (SELECT to_char(birthday,'YYYYMMDD')
               FROM gr_lcinsured
              WHERE insuredno = rc.insured_no
                AND rownum = 1)
           else
            (SELECT to_char(birthdate,'YYYYMMDD')
               FROM t_nbs_client
              WHERE client_no = rc.insured_no
                AND rownum = 1)
         END) birthday， --出生日期理赔未保存。通过契约查询,
         (SELECT codealias
            FROM ldcode
           WHERE codetype = 'gnbsIdTypeSD'
             AND code = rc.certificate_type) certificatetype,
         rc.certificate_no certificateno,
         (SELECT codealias
            FROM ldcode
           WHERE codetype = 'gclsLossResultSD'
             AND code = (SELECT cc.coverage_type_code
                           FROM t_cls_register_coverage cc,
                                t_cls_register_accident ra
                          WHERE ra.claim_no = rc.claim_no
                            AND cc.accident_no = cc.accident_no
                            AND rownum = 1)) lossresult,
         (SELECT ra.loss_occurred_date
            FROM t_cls_register_accident ra
           WHERE ra.claim_no = rc.claim_no
             AND rownum = 1) lossresultdate,
         '0' iscancelclaim,
         '' canceldate,
         sysdate
    FROM t_cls_register_case rc, t_cls_register rr
   WHERE rr.register_no = rc.register_no
     AND trunc(rr.register_end_time) = trunc(SYSDATE - 1) ;
 
   insert into sd_claimlpolice(claimno, policyno, makedate)
    select rc.claim_no claimno, rr.policy_no, sysdate
            from t_cls_register_case rc, t_cls_register rr
           WHERE rr.register_no = rc.register_no
             and rc.claim_no in
                 (select ClaimNo from sd_claiml where state is null);
                 
                 
delete  sd_claimlpolice t1 where 1=1 and not exists (select 1 from sd_policy t2 where t1.policyno = t2.policyno and state = '0');

delete sd_claiml t1 where 1=1 and not exists (select 1 from sd_claimlpolice t2 where t1.claimno = t2.claimno);

NB-GRP

--@
insert into sd_claiml(
   ClaimNo,
   GroupClaimNo,
   ClaimType,
   ClaimDate,
   LossDate,
   LossCause,
   LossArea,
   Name,
   Gender,
   Birthday,
   CredentialType,
   CredentialNo,
   LossResult,
   LossResultDate,
   IsCancelClaim,
   CancelDate,
   makedate
)
select rg.rgtno,
       '',
       '02',
       to_char(rg.rgtdate, 'yyyymmddhh'),
       to_char(rg.accidentdate, 'yyyymmddhh'),
       '01',
       '',
       lp.name,
       lp.sex,
       to_char(lp.birthday, 'yyyymmdd'),
       (case trim(lp.idtype)
         when '0' then
          '01'
         when '1' then
          '07'
         when '2' then
          '04'
         when '3' then
          '03'
         when '5' then
          '02'
         when '6' then
          '09'
         else
          '99'
       end),
       lp.idno,
       (case (select SUBSTR(lar.reasoncode, 2, 4)
            from llappclaimreason lar
           where lar.caseno = rg.rgtno
             and rownum = 1)
         when '01' then
          '03'
         when '03' then
          '02'
         when '02' then
          '01'
         else
          '99'
       end),
       to_char(rg.accidentdate, 'yyyymmddhh'),
       '0',
       '',
       sysdate
  from llregister rg, ldperson lp, llcase lc
 where rg.rgtno = lc.caseno
   and lc.customerno = lp.customerno
   and rg.mngcom like '8606%'
   and rg.makedate = trunc(sysdate-1)
   
--@
insert into sd_claimlpolice
  (claimno, policyno, makedate)
  select distinct clmno, contno, sysdate
    from t_slis_cl_estimatedetail
   where clmno in (select ClaimNo from sd_claiml where state is null)
   and exists (select 1 from sd_policy where policyno = t_slis_cl_estimatedetail.conto and state = '0')

--@
delete sd_claiml where not exists (select 1 from sd_claimlpolice where sd_claiml.claimno = claimno)

NB-LIS

--@
insert into SD_Policy
   (PolicyNo,
   InsureDate,
   AreaFlag,
   BillDate,
   ReceiptDate,
   StartDate,
   EndDate,
   PolicyStatus,
   InsureForm,
   ApplicantType,
   ProducerType,
   ProducerName,
   PolCheckResult,
   CheckResultDate,
   RefuseReason,
   ApplicantName,
   Gender,
   Birthday,
   CredentialType,
   CredentialNo,
   InsuredRelationship,
   InsuredName,
   Gender1,
   Birthday1,
   CredentialType1,
   CredentialNo1,
   ApplicantRelationship,
   Address,
   IsAbnormal,
   InsuredType,
   MainInsRelationship,
   IsDesignBeneficiary,
   Makedate
   )
select  distinct a.contno ,
        to_char(a.polapplydate,'YYYYMMDDHH') ,
        (select CODEALIAS from ldcode where codetype = 'managecomSD' and code = substr(a.managecom,0,6)) ,
        to_char(a.signdate,'YYYYMMDDHH') ,
        to_char(a.getpoldate,'YYYYMMDDHH') ,
        to_char(a.cvalidate,'YYYYMMDDHH') ,
        (select to_char(enddate,'YYYYMMDDHH') from lcpol b where b.contno = a.contno and b.polno = b.mainpolno and rownum =1) ,
        (select CODEALIAS from ldcode where codetype = 'appflagSD' and code = a.appflag) ,
        '02' ,
        '02' ,
        (select comcode from ldcode where codetype = 'salechnlSD' and code = a.salechnl) ,
        (select name from laagent where agentcode = a.agentcode) ,
        (select codealias from ldcode where codetype = 'uwflagSD' and code = a.uwflag),
        to_char(a.uwdate,'YYYYMMDDHH') ,
        '' ,
       
        b.appntname ,
        b.appntsex ,
        to_char(b.appntbirthday,'YYYYMMDD') ,
        (select codealias from ldcode where code = b.idtype and codetype = 'idtypeSD'),
        b.idno ,
        (select codealias from ldcode where code = (select relationtoappnt from lcinsured where contno = b.contno) and codetype = 'relationSD') ,     
        c.name ,
        c.sex ,
        to_char(c.birthday,'YYYYMMDD'),
        (select codealias from ldcode where code = c.idtype and codetype = 'idtypeSD') ,
        c.idno ,
        (select codealias from ldcode where code = c.relationtoappnt and codetype = 'relationSD') ,
        (select homeaddress from lcaddress where addressno = c.addressno and customerno = c.insuredno) ,
        (case (select 1 from lccustomerimpart where contno = c.contno and impartcode <> '116C' and impartver = '02' and impartparammodle like '%Y%' union select 1 from lccustomerimpart where contno = c.contno and impartcode = '116C' and impartver = '02' and impartparammodle like '%N%')  when 1 then '1' else '0' end) ,
        '01' ,
        '01' ,
        (case (select 1 from lcbnf where contno = c.contno and insuredno = c.insuredno and rownum = 1)when 1 then '1' else '0' end),
        sysdate
from lccont a ,lcappnt b ,lcinsured c
where 1=1
and a.contno = b.contno and a.contno = c.contno
and exists (select 1 from lcpol m,lmriskapp n where m.contno = a.contno and m.polno = m.mainpolno and m.riskcode = n.riskcode and n.risktype = 'A')
and a.appflag = '1'
and a.signdate = trunc(sysdate-1)
and a.managecom like '8606%'


--@
insert into Sd_Policyrisk
  (PolicyNo,
   CovPolicyNo,
   CredentialNo,
   KindCode,
   CoverageCode,
   ComCoverageCode,
   ComCoverageName,
   CovStartDate,
   CovEndDate,
   CoverageNature,
   CovBasicAmount,
   CovMaxAmount,
   CoverageStatus,
   CovCheckResult,
   CovCheckResultDate,
   RefuseReason,
   Makedate)
  select contno,
         polno ,
         b.CredentialNo1,
         '100' ,
         (select comcode
            from ldcode
           where codetype = 'risktypeSD'
             and code = a.riskcode),
         riskcode ,
         (select riskname from lmriskapp where riskcode = a.riskcode) ,
         to_char(a.cvalidate, 'YYYYMMDDHH') ,
         to_char(a.enddate, 'YYYYMMDDHH') ,
         (case
          (select SubRiskFlag from lmriskapp where riskcode = a.riskcode)
           when 'M' then
            '01'
           else
            '02'
         end),
         amnt ,
         amnt ,
         (select CODEALIAS
            from ldcode
           where codetype = 'appflagSD'
             and code = a.appflag) ,
         (select codealias from ldcode where codetype = 'uwflagSD' and code = a.uwflag) ,
         to_char(uwdate, 'YYYYMMDDHH') ,
         (select upreportcontent from lcuwmaster where polno = a.polno) ,
         sysdate
    from lcpol a, SD_Policy b
   where 1 = 1
     and a.contno = b.policyno
     and a.insuredname = b.InsuredName
     and b.State is null
     and exists (select 1
            from lmriskapp m
           where m.riskcode = a.riskcode
             and m.risktype = 'A')
     and a.managecom like '8606%'


--@
insert into SD_PolicyBnf
  (PolicyNo,
   CredentialNo,
   BeneficiaryName,
   Gender,
   Birthday,
   CredentialType,
   CredentialNo1,
   InsuredRelationship,
   BenefitAllocation,
   BeneficiaryNo,
   Rate,
   Makedate)
  select a.contno ,
         b.CredentialNo,
         a.name ,
         a.sex ,
         to_char(a.birthday, 'yyyymmdd') ,
         (select codealias
            from ldcode
           where code = a.idtype
             and codetype = 'idtypeSD'),
         a.idno ,
         (select codealias
            from ldcode
           where code = a.relationtoinsured
             and codetype = 'relationSD') ,
         '03' ,
         a.bnfgrade,
         a.bnflot,
         sysdate
    from lcbnf a, Sd_Policyrisk b
   where 1 = 1
     and a.contno = b.policyno
     and a.polno = b.CovPolicyNo
     and exists (select 1
            from sd_policy c
           where c.policyno = b.policyno
             and c.credentialno1 = b.credentialno
             and c.state is null)

NB-PRE

--@
insert into SD_POLICY
(PolicyNo,
 InsureDate,
 AreaFlag,
 BillDate,
 ReceiptDate,
 StartDate,
 EndDate,
 PolicyStatus,
 InsureForm,
 ApplicantType,
 ProducerType,
 ProducerName,
 PolCheckResult,
 CheckResultDate,
 RefuseReason,
 ApplicantName,
 Gender,
 Birthday,
 CredentialType,
 CredentialNo,
 InsuredRelationship,
 GroupName,
 GroupAddress,
 GroCredentialCode,
 GroCredentialNo,
 OrganizationType,
 IndustryClassification,
 Corporation,
 ContactName,
 InsuredName,
 Gender1,
 Birthday1,
 CredentialType1,
 CredentialNo1,
 ApplicantRelationship,
 Address,
 IsAbnormal,
 InsuredType,
 MainInsRelationship,
 IsDesignBeneficiary,
 makedate)

select app.POL_NO as PolicyNo,
       to_char(app.INPUT_TIME, 'yyyymmddhh') as InsureDate,
       (select c1.codealias
          from ldcode c1
         where c1.codetype = 'managecomSD'
           and c1.code = substr(app.REGION_CODE, 1, 6)) as AreaFlag,
       to_char(app.UNDERWRITING_TIME, 'yyyymmddhh') as BillDate,
       to_char(app.underwriting_time, 'yyyymmddhh') as ReceiptDate,
       to_char(app.EFFECT_TIME, 'yyyymmddhh') as StartDate,
       to_char(app.MATU_TIME, 'yyyymmddhh') as EndDate,
       (select c1.codealias
          from ldcode c1
         where c1.codetype = 'gnbsStatusSD'
           and c1.code = app.STATUS) as PolicyStatus,
       (select c1.codealias
          from ldcode c1
         where c1.codetype = 'gnbsGroupSD'
           and c1.code = app.GROUP_FLAG) as InsureForm,
       (select c1.codealias
          from ldcode c1
         where c1.codetype = 'gnbsGroupSD'
           and c1.code = app.GROUP_FLAG) as ApplicantType,
       (select c1.comcode
          from ldcode c1
         where c1.codetype = 'gnbsSaleSD'
           and c1.code = app.sale_channel
           and (c1.othersign is null or c1.othersign = app.source)) as ProducerType,
       (select c.name from t_nbs_agent c where c.agent_code = app.AGENT_CODE) as ProducerName,
       '01' as PolCheckResult,
       to_char(app.underwriting_time, 'yyyymmddhh') as CheckResultDate,
       '' as RefuseReason,
       (select c1.name
          from t_nbs_client c1
         where c1.client_no = app.applicant_no) as ApplicantName,
       (select c1.codealias
          from ldcode c1
         where c1.codetype = 'gnbsGenderSD'
           and c1.code = (select c1.sex
                            from t_nbs_client c1
                           where c1.client_no = app.applicant_no)) as Gender,
       to_char((select c1.birthdate
                 from t_nbs_client c1
                where c1.client_no = app.applicant_no),
               'yyyymmdd') as Birthday,
       (select c1.codealias
          from ldcode c1
         where c1.codetype = 'gnbsIdTypeSD'
           and c1.code = (select c1.id_type
                            from t_nbs_client c1
                           where c1.client_no = app.applicant_no)) as CredentialType,
       (select c1.id_no
          from t_nbs_client c1
         where c1.client_no = app.applicant_no) as CredentialNo,
       (select c1.codealias
          from ldcode c1
         where c1.codetype = 'gnbsRelationSD'
           and c1.code = a.ins_appnt_relation) as InsuredRelationship,
       (select c2.name
          from t_nbs_group_client c2
         where c2.group_client_no = app.appnt_group_no) as GroupName,
       (select c2.address
          from t_nbs_group_client c2
         where c2.group_client_no = app.appnt_group_no) as GroupAddress,
       '' as GroCredentialCode,
       '' as GroCredentialNo,
       '' as OrganizationType,
       '' as IndustryClassification,
       '' as Corporation,
       (select c2.contact_person_name
          from t_nbs_group_client c2
         where c2.group_client_no = app.appnt_group_no) as ContactName,
       (select c1.name
          from t_nbs_client c1
         where c1.client_no = a.insured_no) as InsuredName,
       (select c1.codealias
          from ldcode c1
         where c1.codetype = 'gnbsGenderSD'
           and c1.code = (select c1.sex
                            from t_nbs_client c1
                           where c1.client_no = a.insured_no)) as Gender1,
       to_char((select c1.birthdate
                 from t_nbs_client c1
                where c1.client_no = a.insured_no),
               'yyyymmdd') as Birthday1,
       (select c1.codealias
          from ldcode c1
         where c1.codetype = 'gnbsIdTypeSD'
           and c1.code = (select c1.id_type
                            from t_nbs_client c1
                           where c1.client_no = a.insured_no)) as CredentialType1,
       (select c1.id_no
          from t_nbs_client c1
         where c1.client_no = a.insured_no) as CredentialNo1,
       '' as ApplicantRelationship,
       (select c.address
          from t_nbs_client c
         where c.client_no = a.insured_no) as Address,
       '0' as IsAbnormal,
       (case a.ins_appnt_relation
         when '0' then
          '01'
         else
          '02'
       end) as InsuredType,
       '' as MainInsRelationship,
       (case a.is_legal_beneficiary
         when 'Y' THEN
          '0'
         WHEN 'N' THEN
          '1'
       END) as IsDesignBeneficiary,
       sysdate
  from t_nbs_application app, t_nbs_application_insured a
 where app.app_no = a.app_no
   and app.region_code like '8606%'
   and app.group_flag = 'I'
   and exists (select 1
          from t_nbs_plan tnp
         where tnp.plan_code = app.plan_code
           and tnp.plan_type not in ('03', '06', '08', '09'))
   and CEIL(app.matu_time - app.effect_time) > 30
   and trunc(app.create_time) = trunc(sysdate - 1)


--@
insert into SD_PolicyRisk
  (PolicyNo,
   CovPolicyNo,
   CredentialNo,
   KindCode,
   CoverageCode,
   ComCoverageCode,
   ComCoverageName,
   CovStartDate,
   CovEndDate,
   CoverageNature,
   CovBasicAmount,
   CovMaxAmount,
   CoverageStatus,
   CovCheckResult,
   CovCheckResultDate,
   RefuseReason,
   makedate)

  select cc.POL_NO,
         cc.POL_NO as CovPolicyNo,
         (select c.id_no
            from t_nbs_client c
           where c.client_no = cc.insured_no) as CredentialNo,
         '100' as KindCode,
         (select c1.codealias
            from ldcode c1
           where c1.codetype = 'gnbsRiskSD'
             and c1.code = cc.risk_code) as CoverageCode,
         cc.risk_code as ComCoverageCode,
         cc.name as ComCoverageName,
         to_char(cc.effect_date, 'yyyymmddhh') as CovStartDate,
         to_char(cc.expire_date, 'yyyymmddhh') as CovEndDate,
         (case cc.rider_flag
           when 'P' THEN
            '01'
           WHEN 'R' THEN
            '02'
           WHEN 'A' THEN
            '02'
         END) as CoverageNature,
         cc.Sum_Insured as CovBasicAmount,
         cc.Sum_Insured * cc.unit as CovMaxAmount,
         '01' as CoverageStatus,
         '01' as CovCheckResult,
         to_char(cc.underwriting_time, 'yyyymmddhh') as CovCheckResultDate,
         '' as RefuseReason,
         sysdate
    from (select app.POL_NO,
                 api.insured_no,
                 r.risk_code,
                 r.name,
                 r.effect_date,
                 r.expire_date,
                 r.rider_flag,
                 sum(apbg.Sum_Insured) as sum_insured,
                 app.unit,
                 app.underwriting_time
            from t_nbs_application            app,
                 t_nbs_application_insured    api,
                 t_nbs_application_benf_group apbg,
                 t_nbs_benefit                b,
                 t_nbs_risk                   r
           where app.APP_NO = api.app_no
             and api.benf_group_id = apbg.group_id
             and apbg.benf_code = b.benefit_code
             and b.risk_code = r.risk_code
             and exists (select 1 from sd_policy where policyno = app.pol_no and state is null)
             and not exists
           (select 1
                    from t_nbs_plan tnp
                   where tnp.plan_code = app.plan_code
                     and tnp.plan_type in ('03', '06', '08', '09'))
             and CEIL(app.matu_time - app.effect_time) > 30
           and trunc(app.create_time) = trunc(sysdate - 1)
           group by app.POL_NO,
                    api.insured_no,
                    r.risk_code,
                    r.name,
                    r.effect_date,
                    r.expire_date,
                    r.rider_flag,
                    app.unit,
                    app.underwriting_time) cc




--@
insert into SD_PolicyBnf
  (policyno,
   BeneficiaryName,
   CredentialNo,
   Gender,
   Birthday,
   CredentialType,
   CredentialNo1,
   InsuredRelationship,
   BenefitAllocation,
   BeneficiaryNo,
   Rate,
   makedate)

  select app.pol_no,
         a.benef_name AS BeneficiaryName,
         (select c.id_no
            from t_nbs_client c
           where c.client_no = b.insured_no) as CredentialNo,                
         (select c1.codealias
            from ldcode c1
           where c1.codetype = 'gnbsGenderSD'
             and c1.code = a.sex) as Gender,
         to_char(a.birthdate, 'yyyymmdd') as Birthday,
         (select c1.codealias
            from ldcode c1
           where c1.codetype = 'gnbsIdTypeSD'
             and c1.code = a.id_type) as CredentialType,
         a.id_no as CredentialNo1,
         (select c1.codealias
            from ldcode c1
           where c1.codetype = 'gnbsRelationSD'
             and c1.code = a.benef_ins_relation) as InsuredRelationship,
         '01' as BenefitAllocation,
         a.benef_order as BeneficiaryNo,
         a.benef_ratio as Rate,
         sysdate
    from t_nbs_application_beneficiary a,
         t_nbs_application_insured     b,
         t_nbs_application             app
   where app.app_no = b.app_no
     and b.certain_app_no = a.certain_app_no
     and exists (select 1 from sd_policy where policyno = app.pol_no and state is null)
     and trunc(app.create_time) = trunc(sysdate - 1)
     and exists
   (select 1
            from t_nbs_plan tnp
           where tnp.plan_code = app.plan_code
             and tnp.plan_type not in ('03', '06', '08', '09'))
     and CEIL(app.matu_time - app.effect_time) > 30