杨佩 2019/8/13 20:08:34
--理财经理短信补发
INSERT INTO XTCRM.INFOSEND
(ID,
CREATETIME,
PRE_SEND_TIME,
GW_RTN_TIME,
SENDTIME,
REPORT_TIME,
PSFS,
HOSTNAME,
INFOTYPE,
SERVICEID,
INFOTO,
INFOFROM,
USERID,
RECVUSERID,
RECVUSERTYPE,
TITLE,
CONTENT,
CONTENTSIZE,
ATTACHMENT,
ATTACHMENT2,
ATTACHMENT3,
YYB,
STATUS,
RETRYTIMES,
BATCHID,
PERSONALFLAG,
IMPORTANCE)
SELECT XTCRM.SEQ_INFOSEND.NEXTVAL, --ID,
SYSDATE, --CREATETIME,
SYSDATE, --PRE_SEND_TIME,
NULL, --GW_RTN_TIME,
SYSDATE, --SENDTIME,
NULL, --REPORT_TIME,
0, --PSFS,
NULL, --HOSTNAME,
8, --INFOTYPE,
NULL, --SERVICEID,
C.SJ, --INFOTO,
NULL, --INFOFROM,
0, --USERID,
NULL, --RECVUSERID,
1, --RECVUSERTYPE,--目标接收人类型 正式客户
'资金进账审核通过短信通知', --TITLE,
'尊敬的客户经理:您好!您的客户' || A.KHXM || '购买' || (SELECT XMMC FROM TXSXM WHERE ID = A.XSXM) ||
'已进帐,进账金额为:' || (CASE
WHEN A.CGMJZJE IS NOT NULL THEN
TO_CHAR((NVL(A.CGMJZJE, 0) + A.JZJE) / 10000, 'FM99999990.00')
ELSE
TO_CHAR(A.JZJE / 10000, 'FM999999990.00')
END) || (CASE
WHEN A.CGMJZJE IS NOT NULL THEN
'万,超出实际预约规模' || TO_CHAR(A.CGMJZJE / 10000, 'FM999999990.00') || '万。'
ELSE
'万。'
END),
--CONTENT,
LENGTH('尊敬的客户经理:您好!您的客户' || A.KHXM || '购买' ||
(SELECT XMMC FROM TXSXM WHERE ID = A.XSXM) || '已进帐,进账金额为:' || (CASE
WHEN A.CGMJZJE IS NOT NULL THEN
TO_CHAR((NVL(A.CGMJZJE, 0) + A.JZJE) / 10000, 'FM99999990.00')
ELSE
TO_CHAR(A.JZJE / 10000, 'FM999999990.00')
END) || (CASE
WHEN A.CGMJZJE IS NOT NULL THEN
'万,超出实际预约规模' || TO_CHAR(A.CGMJZJE / 10000, 'FM999999990.00') || '万。'
ELSE
'万。'
END)), --CONTENTSIZE,
NULL, --ATTACHMENT,
NULL, --ATTACHMENT2,
NULL, --ATTACHMENT3,
1, --YYB,
0, --STATUS,
0, --RETRYTIMES,
NULL, --BATCHID,
1, --PERSONALFLAG,
0 --IMPORTANCE,
FROM XTCRM.TZJJZ A, XTCRM.TRYXX C
WHERE A.DJYH = C.DYYH AND C.SJ IS NOT NULL AND A.ZJZT!=-1 AND A.DJRQ=20190813;
--客户短信补发 INSERT INTO XTCRM.INFOSEND
(ID,
CREATETIME,
PRE_SEND_TIME,
GW_RTN_TIME,
SENDTIME,
REPORT_TIME,
PSFS,
HOSTNAME,
INFOTYPE,
SERVICEID,
INFOTO,
INFOFROM,
USERID,
RECVUSERID,
RECVUSERTYPE,
TITLE,
CONTENT,
CONTENTSIZE,
ATTACHMENT,
ATTACHMENT2,
ATTACHMENT3,
YYB,
STATUS,
RETRYTIMES,
BATCHID,
PERSONALFLAG,
IMPORTANCE)
SELECT XTCRM.SEQ_INFOSEND.NEXTVAL, --ID,
SYSDATE, --CREATETIME,
SYSDATE, --PRE_SEND_TIME,
NULL, --GW_RTN_TIME,
SYSDATE, --SENDTIME,
NULL, --REPORT_TIME,
0, --PSFS,
NULL, --HOSTNAME,
8, --INFOTYPE,
NULL, --SERVICEID,
CASE
WHEN CP.KHH IS NOT NULL THEN
(SELECT FN_DECRYPT(KH.SJ) FROM TKHXX KH WHERE KH.ID = CP.KHH AND KH.SJ IS NOT NULL)
ELSE
(SELECT KH.SJ FROM TQZKHXX KH WHERE KH.ID = CP.QZKHID AND KH.SJ IS NOT NULL)
END, --INFOTO,
NULL, --INFOFROM,
0, --USERID,
NULL, --RECVUSERID,
1, --RECVUSERTYPE,--目标接收人类型 正式客户
'资金到账短信通知', --TITLE,
'尊敬的' || A.KHXM || (CASE
WHEN CP.KHH IS NOT NULL THEN
(SELECT CASE
WHEN KH.SEX = 1 AND KH.KHLX = 0 THEN
'先生,'
WHEN KH.SEX = 2 AND KH.KHLX = 0 THEN
'女士,'
ELSE
','
END
FROM TKHXX KH
WHERE KH.ID = CP.KHH)
ELSE
(SELECT CASE
WHEN KH.SEX = 1 AND KH.KHLX = 0 THEN
'先生,'
WHEN KH.SEX = 2 AND KH.KHLX = 0 THEN
'女士,'
ELSE
','
END
FROM TQZKHXX KH
WHERE KH.ID = CP.QZKHID)
END) || '您认购' || (SELECT XSXM.XMMC FROM TXSXM XSXM WHERE XSXM.ID = A.XSXM) ||
'项目的资金' || (CASE
WHEN A.CGMJZJE IS NOT NULL THEN
TO_CHAR((NVL(A.CGMJZJE, 0) + A.JZJE) / 10000, 'FM99999990.00')
ELSE
TO_CHAR(A.JZJE / 10000, 'FM999999990.09')
END) || '万元已于' || TO_CHAR(TO_DATE(A.JZRQ, 'YYYYMMDD'), 'YYYY"年"MM"月"DD"日"') || '到账' ||
(CASE
WHEN A.CGMJZJE IS NOT NULL THEN
',超出实际预约规模' || TO_CHAR(A.CGMJZJE / 10000, 'FM999999990.00') || '万。'
ELSE
'。'
END) || '本条短信仅作为入账通知,不作为认购成功的依据。认购成功时,我公司将另行短信通知。', --CONTENT,
LENGTH('尊敬的' || A.KHXM || (CASE
WHEN CP.KHH IS NOT NULL THEN
(SELECT CASE
WHEN KH.SEX = 1 AND KH.KHLX = 0 THEN
'先生,'
WHEN KH.SEX = 2 AND KH.KHLX = 0 THEN
'女士,'
ELSE
','
END
FROM TKHXX KH
WHERE KH.ID = CP.KHH)
ELSE
(SELECT CASE
WHEN KH.SEX = 1 AND KH.KHLX = 0 THEN
'先生,'
WHEN KH.SEX = 2 AND KH.KHLX = 0 THEN
'女士,'
ELSE
','
END
FROM TQZKHXX KH
WHERE KH.ID = CP.QZKHID)
END) || '您认购' || (SELECT XSXM.XMMC FROM TXSXM XSXM WHERE XSXM.ID = A.XSXM) ||
'项目的资金' || (CASE
WHEN A.CGMJZJE IS NOT NULL THEN
TO_CHAR((NVL(A.CGMJZJE, 0) + A.JZJE) / 10000, 'FM99999990.00')
ELSE
TO_CHAR(A.JZJE / 10000, 'FM999999990.09')
END) || '万元已于' || TO_CHAR(TO_DATE(A.JZRQ, 'YYYYMMDD'), 'YYYY"年"MM"月"DD"日"') || '到账' ||
(CASE
WHEN A.CGMJZJE IS NOT NULL THEN
',超出实际预约规模' || TO_CHAR(A.CGMJZJE / 10000, 'FM999999990.00') || '万。'
ELSE
'。'
END) || '本条短信仅作为入账通知,不作为认购成功的依据。认购成功时,我公司将另行短信通知。'), --CONTENTSIZE,
NULL, --ATTACHMENT,
NULL, --ATTACHMENT2,
NULL, --ATTACHMENT3,
1, --YYB,
0, --STATUS,
0, --RETRYTIMES,
NULL, --BATCHID,
1, --PERSONALFLAG,
0 --IMPORTANCE,
FROM XTCRM.TZJJZ A, XTCRM.TCPYY CP
WHERE A.CPYYID = CP.ID
AND A.ZJZT!=-1 AND A.DJRQ=20190813
AND NOT EXISTS
(SELECT 1 FROM TKHXX KH WHERE KH.ID = CP.KHH AND KH.KHJL IN (316, 317, 319, 374)) --后台客户不发送短信
AND NOT EXISTS (SELECT 1 FROM TKHXX KH WHERE KH.ID = CP.KHH AND KH.MDRXM = 1) --客户设置为免打扰不发送信息
AND EXISTS
(SELECT 1 FROM TFP_CPDM CPDM WHERE A.CPID = CPDM.ID AND /*CPDM.CPLX = 1*/CPDM.CPLX IN (1,2));--20190213 YANGPEI 资金池产品资金进账时也要短信通知客户