123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596 |
- CREATE OR REPLACE PROCEDURE shanglifeecif.init_communication() IS
- DECLARE
- BEGIN
- DELETE FROM shanglifeecif.communication;
- insert into shanglifeecif.communication (
- CommID,
- CPName,
- CPPhone,
- PolicyNo,
- CommTime,
- Reason,
- CommChannel,
- created_by ,-- '创建人',
- created_time -- '创建时间',
- ) select
- row_number()over(),
- econtactsName,
- econtactsPhone,
- contNos,
- startTime,
- reasonSecondName,
- '最近呼入' AS CommChannel,
- 'admin',
- sysdate()
- from cc_record_main WHERE contNos IS NOT null;
-
- END ;
- /
- CREATE OR REPLACE PROCEDURE shanglifeecif.init_communication_sp01() IS
- DECLARE
- BEGIN
- UPDATE shanglifeecif.communication sc set(
- sc.RSOApplicant,
- sc.CompLink,
- sc.CompReason,
- sc.ProcedureResult
-
- ) = (SELECT
- temp.complaintsRelation,
- temp.complaintsLink,
- temp.resultClassification,
- temp.procedureResult
-
- FROM (
-
- SELECT
- (CASE scac.complaintsRelation --201 本人 202 子女 203 配偶 204 父母 205 其他
- WHEN '201' THEN '本人'
- WHEN '202' THEN '子女'
- WHEN '203' THEN '配偶'
- WHEN '204' THEN '父母'
- WHEN '205' THEN '其他'
- END) AS complaintsRelation,
- (CASE scac.complaintsLink --:承保环节 : 211 销售环节 :212 、回访环节:213 、保全变更环节: 214 、理赔环节: 215 、售后服务环节: 216
- WHEN '211' THEN '承保环节'
- WHEN '212' THEN '销售环节'
- WHEN '213' THEN '回访环节'
- WHEN '214' THEN '保全变更环节'
- WHEN '215' THEN '理赔环节'
- WHEN '216' THEN '售后服务环节'
- END) AS complaintsLink,
- (CASE scac.resultClassification --:销售误导:101 代签名:102 理赔争议:103 服务人员态度:104 售后服务不满:105 售前服务不满:106 外部陌电骚扰:107
- WHEN '101' THEN '销售误导'
- WHEN '102' THEN '代签名'
- WHEN '103' THEN '理赔争议'
- WHEN '104' THEN '服务人员态度'
- WHEN '105' THEN '售后服务不满'
- WHEN '106' THEN '售前服务不满'
- WHEN '107' THEN '外部陌电骚扰'
- END) AS resultClassification,
- scac.procedureResult,
- row_number() over(partition by scac.productNos) rn,
- scac.productNos,
- scac.complaintsName
- FROM
- cc_action_complaints scac
- ) temp
- WHERE sc.PolicyNo = temp.productNos AND sc.CPName = temp.complaintsName AND temp.rn = 1)
-
- END ;
- /
- CREATE OR REPLACE PROCEDURE shanglifeecif.init_communication_sp02() IS
- DECLARE
- BEGIN
- UPDATE shanglifeecif.communication sc SET(
- IndID,
- CPCertID
- ) = (
- SELECT
- ApplicantID,
- AppCertID
- FROM
- shanglifeecif.InsuranceArrangement sia
- WHERE sia.PolicyNo = sc.PolicyNo AND sc.RSOApplicant = '本人'
- )
- END ;
|