存储过程书写

CREATE OR REPLACE PROCEDURE P_T_policy_JH09--存储过程名称

(comcode IN VARCHAR2, startdate IN DATE, enddate IN DATE) IS

--变量定义

vStartDate DATE; --查询起期
vEndDate DATE; --查询止期
vComCode VARCHAR2(20); --机构代码
--变量赋值
BEGIN
vStartDate := startdate;
vEndDate := enddate;
vComCode := comcode;
DELETE FROM t_policy;
DELETE FROM vsmark_jh;
INSERT INTO vsmark_jh
(SELECT *
FROM vsmark
WHERE comcode LIKE vComCode || '%'
AND vsmark.usedate BETWEEN vStartDate AND vEndDate + 0.99999);
COMMIT;
INSERT INTO t_policy
(c_ply_no,
--保单号
c_pol_serial,
--保单流水号/印刷号
c_dpt_cde,
--出单机构代码
c_app_addr,
--投保人地址
c_app_nme,
--投保人名称（多投保人用分号分割）
c_app_tel,
--投保人电话
c_app_ownership_cde,
--投保人性质 （0、自然人 1、法人）对应circcompare表
c_cntct_prsn_nme,
--投保联系人
c_insrnt_addr,
--被保险人地址
c_city,
--被保险人所在地区邮政编码
c_insrnt_nme,
--被保险人名称
c_bnfc_nme,
--受益人名称（多受益人用分号分割）
c_bsns_typ,
--业务来源（对应circcompare表）
c_cmpny_agt_cde,
--中介机构代码（对应Busi_Src_Type_Tbl）
c_agent_cde,
--销售人员代码（对应salesman_info）
c_insrnc_cde,
--险种代码（对应plan_info）
n_amt,
--保险金额/责任限额
n_insrnc_vlu,
--保险价值
n_cmm,
--手续费折合人民币
n_ratio,
--短期费率系数（不足一年期的短期业务要求填入投保时的短期费率系数，如果没有短期费率系数（如意外、健康险），则填入默认值1。）
n_prm,
--主险保费合计
n_rdr_prm,
--附加险保费合计
n_sum_prm,
--保费合计
t_sum_prm_cur,
--保费币种（对应Cur_tbl 的C_CODE）
n_sum_prm_cnvt,
--保费合计折合人民币
n_pay_tms,
--缴费期数
t_app_tm,
--投保日期
t_insrnc_bgn_tm,
--保险起期
t_insrnc_end_tm,
--保险止期
t_sign_tm,
--签单日期
t_pol_input_tm,
--保单录入日期
t_underwriting_tm,
--核保通过日期
c_underwriter,
--核保人
c_relate_agro,
--涉农标志（1涉，0不涉农）
c_renew_mrk,
--续保标志（1续保，0新保）
c_coinsrnc_mrk,
--共保标志（0非共保，1首席承保牵头公司，2参与共保公司，3其他）
c_rinsrnc_in,
--分入标志（1直接业务，0分入业务）
c_rinsrnc_out,
--分出标志（0 全部自留，1非临分，2临分）
C_facultative_ym,
--临时分保日期（YYYYMM，取最后一个临时分保日期）
N_Share_Keep,
--自留保额
N_Prm_Keep) --自留保费
(SELECT /*+ parallel(gupolicycopymain)parallel(gupolicycopyrisk)*/
gupolicycopyrisk.subpolicyno, /*gupolicycopymain.printno,gupolicycopyrisk.policyvisaserialno,*/
(select vsmark_jh.visaserialno
from vsmark_jh, VsCodeSet
where VsCodeSet.Certitype = 'AE'
and VsCodeSet.Visacode = vsmark_jh.visacode
and vsmark_jh.comcode = VsCodeSet.Comcode
and vsmark_jh.businessno = gupolicycopyrisk.subpolicyno--gupolicycopymain.policyno
and rownum = 1),
gupolicycopymain.companycode,
--投保人地址
-- substr(gupolicycopymain.appliAddress, 0, 20),
(SELECT c.insuredAddress
FROM gupolicycopyrelatedparty c
WHERE c.policyno = gupolicycopyrisk.policyno
-- AND c.riskcode = gupolicycopyrisk.riskcode
AND c.endorseqno = '000'
AND c.insuredflag = '1') ,

substr(gupolicycopymain.appliname, 0, 20),
--投保人电话
(SELECT c.contactmobile
FROM gupolicycopyrelatedparty c
WHERE c.policyno = gupolicycopyrisk.policyno
-- AND c.riskcode = gupolicycopyrisk.riskcode
AND c.endorseqno = '000'
AND c.insuredflag = '1') AS MobilePhone,

(SELECT CASE
WHEN GUProposalRelatedParty.InsuredType = '1' THEN
'0'
ELSE
'1'
END
FROM GUProposalRelatedParty
WHERE gupolicycopymain.proposalno = GUProposalRelatedParty.proposalno
AND GUProposalRelatedParty.insuredflag = '1'),
--投保联系人
(SELECT c.contactname
FROM gupolicycopyrelatedparty c
WHERE c.policyno = gupolicycopymain.policyno
AND c.endorseqno = gupolicycopyrisk.endorseqno) AS ContactName,
--被保险人地址
substr(gupolicycopymain.InsuredAddress, 0, 20),
--被保险人所在地区邮政编码
(SELECT listagg(insuredPostCode,',') within group(order by insuredPostCode)
FROM gupolicycopyriskrelatedparty c
WHERE c.policyno = gupolicycopymain.policyno
AND c.endorseqno = gupolicycopymain.endorseqno) AS insuredPostCode,--被保险人所在地区邮政编码
--被保险人名称
substr(gupolicycopymain.insuredname, 0, 20),
(SELECT listagg(insuredName,',') within group(order by insuredName)
FROM gupolicycopyriskrelatedparty c
WHERE c.policyno = gupolicycopyrisk.policyno
AND c.riskcode = gupolicycopyrisk.riskcode
AND c.endorseqno = '000'
AND c.insuredflag = '2'),
(SELECT circcompare.circcode
FROM circcompare
WHERE circcompare.businesscode = gupolicycopymain.BusinessSource
AND circcompare.codetype = 'Busi_Src_Type_Tbl'), gupolicycopymain.IntermediaryCode,--中介机构代码
gupolicycopymain.SalesmanCode, gupolicycopyrisk.riskcode,
nvl(gupolicycopyrisk.suminsured, 0),
nvl((Select GuPolicyItemMotor.actualValue From GuPolicyItemMotor where subpolicyno=gupolicycopyrisk.subpolicyno and
GuPolicyItemMotor.Riskcode=gupolicycopyrisk.riskcode and GuPolicyItemMotor.Plancode=gupolicycopyrisk.plancode),0),
/*0 AS Sumvalue,*/
--手续费折成人民币
nvl((Select commissionamount From gupolicycommission where gupolicycommission.policyno=gupolicycopyrisk.policyno and
gupolicycommission.riskcode= gupolicycopyrisk.riskcode),0)
/*nvl(round((SELECT SUM(V_120.N_01* get_markrate(V_120.currency,'CNY',V_120.voucherdate))
FROM V_120
WHERE V_120.policyno = gupolicycopyrisk.policyno
AND V_120.riskcode = gupolicycopyrisk.riskcode
AND V_120.endorseqno = '000'), 2), 0)*/,
(SELECT GUPolicycopyItemKind.shortrate / 100
FROM GUPolicycopyItemKind
WHERE GUPolicycopyItemKind.Endorseqno = '000'
AND GUPolicycopyItemKind.policyno = gupolicycopyrisk.policyno
AND GUPolicycopyItemKind.Riskcode = gupolicycopyrisk.riskcode
AND Rownum = 1),
--主险保费合计(从GUPolicycopyItemKind表的币别到gupolicycopymain表的币别)
nvl(round((SELECT SUM(GUPolicycopyItemKind.GrossPremium * get_markrate(GUPolicycopyItemKind.currency,gupolicycopymain.currency,gupolicycopymain.inputdate))
FROM GUPolicycopyItemKind
WHERE GUPolicycopyItemKind.Endorseqno = '000'
AND GUPolicycopyItemKind.policyno = gupolicycopyrisk.policyno
AND GUPolicycopyItemKind.Riskcode = gupolicycopyrisk.Riskcode
AND GUPolicycopyItemKind.KindInd = '1'), 2), 0),
--附加险保费合计(从GUPolicycopyItemKind表的币别到gupolicycopymain表的币别)
nvl(round((SELECT SUM(GUPolicycopyItemKind.GrossPremium * get_markrate(GUPolicycopyItemKind.currency,gupolicycopymain.currency,gupolicycopymain.inputdate))
FROM GUPolicycopyItemKind
WHERE GUPolicycopyItemKind.Endorseqno = '000'
AND GUPolicycopyItemKind.policyno = gupolicycopymain.policyno
AND GUPolicycopyItemKind.Riskcode = gupolicycopyrisk.Riskcode
AND nvl(GUPolicycopyItemKind.KindInd, '2') = '2'), 2), 0),
nvl(gupolicycopyrisk.sumgrosspremium, 0),
(SELECT circcompare.circcode
FROM circcompare
WHERE circcompare.businesscode = gupolicycopymain.currency
AND circcompare.codetype = 'Cur_tbl'),
--保费合计折成人民币
nvl(round((SELECT SUM(GUPolicycopyItemKind.GrossPremium* get_markrate(GUPolicycopyItemKind.currency,'CNY',gupolicycopymain.inputdate))
FROM GUPolicycopyItemKind
WHERE GUPolicycopyItemKind.Endorseqno = '000'
AND GUPolicycopyItemKind.policyno = gupolicycopyrisk.policyno
AND GUPolicycopyItemKind.Riskcode = gupolicycopyrisk.Riskcode), 2), 0),
(SELECT MAX(gupolicycopypaymentplan.payno)
FROM gupolicycopypaymentplan
WHERE gupolicycopypaymentplan.policyno = gupolicycopymain.policyno
AND Endorseqno = '000'), trunc(gupolicycopymain.OperateDate),
trunc(gupolicycopyrisk.startdate), trunc(gupolicycopyrisk.enddate),
trunc(gupolicycopymain.IssueDate), trunc(gupolicycopymain.InputDate),
trunc(gupolicycopymain.underwriteenddate), gupolicycopymain.underwriteCode,
/*decode(gupolicycopymain.agricultureflag, '', '0', '1')*/
case
when gupolicycopymain.SalesCommissionerCode='2' then
'1'
else
'0'
end, gupolicycopymain.RenewInd,
gupolicycopyrisk.coinsind,
--gupolicycopymain.BusinessType,
CASE
WHEN gupolicycopymain.BusinessType = '0' THEN
'0'
ELSE
'1'
END,
--分出标志
nvl((SELECT temp.flag
FROM (SELECT policyno, riskcode,
CASE
WHEN lf > 0 THEN
'2'
WHEN lf IS NULL AND flf > 0 THEN
'1'
WHEN lf IS NULL AND flf IS NULL AND zl > 0 THEN
'0'
END AS flag
FROM (SELECT policyno, riskcode, SUM(lf) lf, SUM(flf) flf, SUM(zl) zl
FROM (SELECT policyno, a.riskcode,
CASE
WHEN Ttytype IN ('91', '92') THEN
1
END AS lf,
CASE
WHEN Ttytype IN ('31', '21') THEN
1
END AS flf,
CASE
WHEN Ttytype IN ('81', '82') THEN
1
END AS zl
FROM GRReplyShare a
WHERE a.RecTimes = 1) b
GROUP BY policyno, riskcode)) temp
WHERE temp.policyno = gupolicycopyrisk.policyno
AND temp.riskcode = gupolicycopyrisk.riskcode), 0),
(SELECT to_char(GRReplyShare.ReStartDate, 'YYYYMM')
FROM GRReplyShare
WHERE GRReplyShare.Policyno = gupolicycopyrisk.policyno
AND GRReplyShare.Riskcode = gupolicycopyrisk.riskcode
AND GRReplyShare.RecTimes = 1
AND rownum = 1),
nvl((SELECT SUM(GRReplyShare.RiSum)
FROM GRReplyShare
WHERE GRReplyShare.Ttytype IN ('81', '82')
AND GRReplyShare.Policyno = gupolicycopyrisk.policyno
AND GRReplyShare.Riskcode = gupolicycopyrisk.riskcode), 0),
nvl((SELECT SUM(GRReplyShare.GrsPrem)
FROM GRReplyShare
WHERE GRReplyShare.ttytype IN ('81', '82')
AND GRReplyShare.Policyno = gupolicycopyrisk.policyno
AND GRReplyShare.Riskcode = gupolicycopyrisk.riskcode), 0)
FROM gupolicycopymain, gupolicycopyrisk
WHERE gupolicycopymain.policyno = gupolicycopyrisk.policyno
AND gupolicycopymain.endorseqno = gupolicycopyrisk.endorseqno
AND gupolicycopymain.underwriteind = '6'
AND gupolicycopyrisk.riskcode<>'0601' --排除预约保险
AND gupolicycopymain.endorseqno = '000'
AND gupolicycopymain.companycode LIKE '01' || '%'
And gupolicycopymain.acceptdate <= vEndDate + 0.99999
AND gupolicycopyrisk.startdate BETWEEN vStartDate AND vEndDate + 0.99999);
COMMIT;

UPDATE t_policy
SET n_sum_prm = n_prm + n_rdr_prm;
COMMIT;
--主表存的是RMB,itemkind表是美元,折合RMB的时候会差1分钱.20090427修改.
UPDATE t_policy
SET n_sum_prm_cnvt = n_sum_prm
WHERE t_sum_prm_cur = 1
AND n_sum_prm <> n_sum_prm_cnvt;
COMMIT;
END P_T_policy_JH09;

 

转载于:https://www.cnblogs.com/qitianming/p/8478501.html