源代码：ZGLanguage 解析SQL数据血缘 之 显示复杂SQL脚本结构图
源代码：ZGLanguage 解析SQL数据血缘 之 显示复杂SQL脚本结构图
·
# 通过 ZGLanguage + Python 将以下复杂存储过程脚本转换成 Echarts 展示：
# 存储过程代码：
CREATE OR REPLACE PROCEDURE PROC_F_CWWS_LOAN
(
P_AS_OF_DATE IN DATE,
RET_FLG OUT VARCHAR2,
RET_MSG OUT VARCHAR2
) IS
/******************************************************************************
功能描述：xxxx业务数据ETL处理
源 表：
目 标 表：MA_F_LOAN
备 注：
******************************************************************************/
-- 声明变量并初始化
V_COUNT NUMBER := 0;
V_PROC_NAME VARCHAR2(200) := 'PROC_F_CWWS_LOAN';
V_PROC_DESC VARCHAR2(100) := 'xxxx业务数据ETL处理';
V_P_FREQ VARCHAR2(4) := '';
BEGIN
----------------------------------------------------------------
----------------------------------------------------------------
----------------------------------------------------------------
----------------------------------------------------------------
--写入初始日志
INSERT INTO M_RUNLOG VALUES (SYSDATE, V_PROC_NAME, 'it is 1');
COMMIT;
ETL.PROC_ETL_LOG(P_AS_OF_DATE,
V_PROC_NAME,
V_PROC_DESC,
0,
0,
NULL,
NULL);
--设置会话日期格式
EXECUTE IMMEDIATE ' ALTER SESSION SET NLS_DATE_FORMAT = ''YYYY-MM-DD''';
--查询参数表中，该程序对应的频率值
SELECT P_FREQ
INTO V_P_FREQ
FROM ETL_PROC_STATUS_DEF
WHERE PROC_NAME = V_PROC_NAME;
--判断是调度频率
IF P_AS_OF_DATE = FUNC_GET_FREQ_DAYS(P_AS_OF_DATE, V_P_FREQ) THEN
--调用分区维护程序
ETL.ETL_ADD_PARTITION('MA_F_LOAN', P_AS_OF_DATE, 'ETL');
--删除取上下次支付日临时表
DELETE TMP_XD_LAST_PAYDATE;
COMMIT;
DELETE TMP_XD_NEXT_PAYDATE;
COMMIT;
INSERT INTO M_RUNLOG VALUES (SYSDATE, V_PROC_NAME, 'it is 2');
COMMIT;
--从还款计划表中取每笔账户最近一次小于等于数据日期还款日，作为上次还款日
INSERT INTO ETL.TMP_XD_LAST_PAYDATE
(OBJECTNO, LAST_PAYDATE)
SELECT OBJECTNO, LAST_PAYDATE
FROM (SELECT T.OBJECTNO,
MAX(TO_DATE(PAYDATE, 'YYYY-MM-DD')) LAST_PAYDATE
FROM NYBDP.O_CWWS_ACCT_PAYMENT_SCHEDULE T
WHERE T.AS_OF_DATE = P_AS_OF_DATE
AND T.SEQID <> '999'
AND TO_DATE(T.PAYDATE, 'YYYY-MM-DD') < P_AS_OF_DATE
GROUP BY T.OBJECTNO);
INSERT INTO M_RUNLOG VALUES (SYSDATE, V_PROC_NAME, 'it is 3');
COMMIT;
--从还款计划表中取每笔账户最近一次大于数据日期还款日，作为下次还款日
INSERT INTO ETL.TMP_XD_NEXT_PAYDATE
(OBJECTNO, NEXT_PAYDATE)
SELECT OBJECTNO, NEXT_PAYDATE
FROM (SELECT T.OBJECTNO,
MIN(TO_DATE(PAYDATE, 'YYYY-MM-DD')) NEXT_PAYDATE
FROM NYBDP.O_CWWS_ACCT_PAYMENT_SCHEDULE T
WHERE T.AS_OF_DATE = P_AS_OF_DATE
AND T.SEQID <> '999'
AND TO_DATE(T.PAYDATE, 'YYYY-MM-DD') >= P_AS_OF_DATE
GROUP BY T.OBJECTNO);
INSERT INTO M_RUNLOG VALUES (SYSDATE, V_PROC_NAME, 'it is 4');
COMMIT;
--xxxx业务数据ETL处理
INSERT INTO MA_F_LOAN
(ACCOUNT_NUMBER,
ACCOUNT_STAT,
RESERVE_FLG,
ACCOUNT_ID,
AS_OF_DATE,
PRODUCT_ID,
GL_ACCOUNT_ID,
INT_GL_ACCOUNT_ID,
ISO_CURRENCY_CD,
ORG_UNIT_ID,
CUR_BOOK_BAL,
OVERDUE_BAL,
SUM_BOOK_BAL_M,
SUM_BOOK_BAL_Y,
AVG_BOOK_BAL_M,
AVG_BOOK_BAL_Y,
CUR_BOOK_BAL_C,
AVG_BOOK_BAL_M_C,
AVG_BOOK_BAL_Y_C,
SUM_OVERDUE_BAL_M,
SUM_OVERDUE_BAL_Y,
AVG_OVERDUE_BAL_M,
AVG_OVERDUE_BAL_Y,
CUR_OVERDUE_BAL_C,
AVG_OVERDUE_BAL_M_C,
AVG_OVERDUE_BAL_Y_C,
ORG_BOOK_BAL,
MATURITY_DATE,
ORG_TERM,
ORG_TERM_MULT,
ORIGINATION_DATE,
ACCRUAL_BASIS_CD,
CUR_NET_RATE,
MARGIN,
FLOAD_R,
FLOAD_M,
BENCHMARK_RATE,
AMRT_TYPE_CD,
CUR_PAYMENT_INT,
CUR_PAYMENT,
LAST_PAYMENT_DATE,
NEXT_PAYMENT_DATE,
PMT_FREQ,
PMT_FREQ_MULT,
REMAIN_NO_PMTS_C,
ADJUSTABLE_TYPE_CD,
LAST_REPRICE_DATE,
NEXT_REPRICE_DATE,
REPRICE_FREQ,
REPRICE_FREQ_MULT,
LRD_BALANCE,
INT_ADJUSTMENT,
ACCOUNT_OPEN_DATE,
CIF_KEY,
CIF_TYPE,
ACCRUED_INTEREST,
DATA_SOURCE,
INT_IE,
DEPARTMENT,
INDUSTRYTYPE,
CLASSIFY,
C_EVALUATE,
VOUCHTYPE,
ACCOUNT_MGR,
CHANNEL,
PRVN_AMT,
BUSINESS_DEPARTMENT,
INTEREST_SUM_M,
INTEREST_SUM_Y,
MINENT_FLAG,
GUARD_BAL,
GUARD_BAL_PART,
PRVN_GL_ACCOUNT_ID,
REPRICE_TYPE,
PRODUCT_CODE,
MANAGEORGID)
SELECT CASE
WHEN CL.LOAN_STATUS = '0' AND CL.DUE_BALANCE <> 0 THEN
'AK-' || AL.ACCOUNTNO || 'YQ'
ELSE
AL.ACCOUNTNO
END ACCOUNT_NUMBER, --20180319
-- AL.ACCOUNTNO ACCOUNT_NUMBER, --账号
DECODE(CL.LOAN_STATUS,
'0',
'1',
'1',
'1',
'20',
'20',
'6',
'9',
'8') ACCOUNT_STAT, --账户状态 0正常xxxx\1减值xxxx->1-正常（当前有效的）;6已核销->9-核销;OTHER->8-其他 --20卖出（资产腾挪）
DECODE(CL.LOAN_STATUS, '1', '1', '4', '1', 0) RESERVE_FLG, --减值状态 1减值xxxx\4减值结清->1-减值;OTHER->0-正常
CL.DRAW_NO ACCOUNT_ID, --账号
P_AS_OF_DATE AS_OF_DATE, --数据日期
SUBSTR(FUNC_GET_PRODUCT('CMS',
CASE
WHEN CL.LOAN_STATUS = '20' AND AL.BUSINESSTYPE = '1010010' AND
CL.GL_CODE = '70107' THEN
'1010010A'
WHEN CL.LOAN_STATUS = '20' AND AL.BUSINESSTYPE = '1170010' AND
CL.GL_CODE = '70107' THEN
'1170010A'
WHEN CL.LOAN_STATUS = '20' AND AL.BUSINESSTYPE = '1010020' AND
CL.GL_CODE = '70107' THEN
'1010020A'
WHEN CL.LOAN_STATUS = '20' AND AL.BUSINESSTYPE = '1130030' AND
CL.GL_CODE = '70107' THEN
'1130030A'
ELSE
AL.BUSINESSTYPE
END,
CASE
WHEN CL.LOAN_STATUS = '20' AND AL.BUSINESSTYPE = '1010010' AND
CL.GL_CODE = '70107' THEN
'9990101'
WHEN CL.LOAN_STATUS = '20' AND AL.BUSINESSTYPE = '1170010' AND
CL.GL_CODE = '70107' THEN
'9990102'
WHEN CL.LOAN_STATUS = '20' AND AL.BUSINESSTYPE = '1010020' AND
CL.GL_CODE = '70107' THEN
'9990103'
WHEN CL.LOAN_STATUS = '20' AND AL.BUSINESSTYPE = '1130030' AND
CL.GL_CODE = '70107' THEN
'9990104'
ELSE
CL.GL_CODE
END,
''),
INSTR(FUNC_GET_PRODUCT('CMS',
CASE
WHEN CL.LOAN_STATUS = '20' AND AL.BUSINESSTYPE = '1010010' AND
CL.GL_CODE = '70107' THEN
'1010010A'
WHEN CL.LOAN_STATUS = '20' AND AL.BUSINESSTYPE = '1170010' AND
CL.GL_CODE = '70107' THEN
'1170010A'
WHEN CL.LOAN_STATUS = '20' AND AL.BUSINESSTYPE = '1010020' AND
CL.GL_CODE = '70107' THEN
'1010020A'
WHEN CL.LOAN_STATUS = '20' AND AL.BUSINESSTYPE = '1130030' AND
CL.GL_CODE = '70107' THEN
'1130030A'
ELSE
AL.BUSINESSTYPE
END,
CASE
WHEN CL.LOAN_STATUS = '20' AND AL.BUSINESSTYPE = '1010010' AND
CL.GL_CODE = '70107' THEN
'9990101'
WHEN CL.LOAN_STATUS = '20' AND AL.BUSINESSTYPE = '1170010' AND
CL.GL_CODE = '70107' THEN
'9990102'
WHEN CL.LOAN_STATUS = '20' AND AL.BUSINESSTYPE = '1010020' AND
CL.GL_CODE = '70107' THEN
'9990103'
WHEN CL.LOAN_STATUS = '20' AND AL.BUSINESSTYPE = '1130030' AND
CL.GL_CODE = '70107' THEN
'9990104'
ELSE
CL.GL_CODE
END,
''),
'-',
1) + 1) PRODUCT_ID, --产品号 --增加9990103科目，所以新增一段casewhen代码 WZG 20170427
--增加9990104科目，所以新增一段casewhen代码 WZG 20170630
CASE
WHEN CL.LOAN_STATUS = '20' AND AL.BUSINESSTYPE = '1010010' AND
CL.GL_CODE = '70107' THEN
'9990101'
WHEN CL.LOAN_STATUS = '20' AND AL.BUSINESSTYPE = '1170010' AND
CL.GL_CODE = '70107' THEN
'9990102'
WHEN CL.LOAN_STATUS = '20' AND AL.BUSINESSTYPE = '1010020' AND
CL.GL_CODE = '70107' THEN
'9990103'
WHEN CL.LOAN_STATUS = '20' AND AL.BUSINESSTYPE = '1130030' AND
CL.GL_CODE = '70107' THEN
'9990104'
ELSE
CL.GL_CODE
END GL_ACCOUNT_ID, --科目号 --增加9990103科目，所以新增一段casewhen代码 WZG 20170427
--增加9990104科目，所以新增一段casewhen代码 WZG 20170630
B.INT_SUB_CODE INT_GL_ACCOUNT_ID, --利息科目号
CC.INTER_EN_NAME ISO_CURRENCY_CD, --币种
CL.BRANCH ORG_UNIT_ID, --机构号
CL.LOAN_BALANCE CUR_BOOK_BAL, --当前余额
CL.DUE_BALANCE OVERDUE_BAL, --逾期金额
CL.AGG_MTD SUM_BOOK_BAL_M, --原币月积数
CL.AGG_YTD SUM_BOOK_BAL_Y, --原币年积数
CL.AGG_MTD / CL.DAYS_MTD AVG_BOOK_BAL_M, --原币月日均余额
CL.AGG_YTD / CL.DAYS_YTD AVG_BOOK_BAL_Y, --原币年日均余额
NULL CUR_BOOK_BAL_C, --折人民币当前余额
NULL AVG_BOOK_BAL_M_C, --折人民币月日均余额
NULL AVG_BOOK_BAL_Y_C, --折人民币年日均余额
CL.DUE_BALANCE SUM_OVERDUE_BAL_M, --原币逾期月积数
CL.DUE_BALANCE SUM_OVERDUE_BAL_Y, --原币逾期年积数
CL.DUE_BALANCE / CL.DAYS_MTD AVG_OVERDUE_BAL_M, --原币逾期月日均余额
CL.DUE_BALANCE / CL.DAYS_YTD AVG_OVERDUE_BAL_Y, --原币逾期年日均余额
NULL CUR_OVERDUE_BAL_C, --逾期折人民币当前余额
NULL AVG_OVERDUE_BAL_M_C, --逾期折人民币月日均余额
NULL AVG_OVERDUE_BAL_Y_C, --逾期折人民币年日均余额
CL.LOAN_AMT ORG_BOOK_BAL, --原始金额
TO_DATE(CL.MATURITY_DATE, 'YYYYMMDD') MATURITY_DATE, --到期日
TO_DATE(CL.MATURITY_DATE, 'YYYYMMDD') -
TO_DATE(AL.PUTOUTDATE, 'YYYY/MM/DD') ORG_TERM, --原始期限
'D' ORG_TERM_MULT, --原始期限单位
TO_DATE(AL.PUTOUTDATE, 'YYYY/MM/DD') ORIGINATION_DATE, --起息日
2 ACCRUAL_BASIS_CD, --利息提存计算方式代码 ACTUAL/360
AL.LOANRATE CUR_NET_RATE, --当前执行利率
AL.LOANRATE - AL.LOANBASERATE MARGIN, --净利加減值
AL.LOANRATEFLOAT FLOAD_R, --浮动比（与基准利率）
CASE
WHEN NVL(AL.LOANRATEFLOATTYPE, BP.RATEFLOATTYPE) = 0 THEN
'2' --浮动比
WHEN NVL(AL.LOANRATEFLOATTYPE, BP.RATEFLOATTYPE) = 1 THEN
'1' --浮动点
ELSE
'0' --不浮动
END FLOAD_M, --FLOAD_M
AL.LOANBASERATE BENCHMARK_RATE, --基准利率（上次利率变动日的基准利率）
'800' AMRT_TYPE_CD, --摊还种类代码 800常规的还款计划
T.PAYINTEAMT CUR_PAYMENT_INT, --下次支付利息
T.PAYCORPUSAMT CUR_PAYMENT, --下次支付本金
NVL(LP.LAST_PAYDATE, TO_DATE(AL.PUTOUTDATE, 'YYYY/MM/DD')) LAST_PAYMENT_DATE, --上次支付日
NVL(NP.NEXT_PAYDATE, TO_DATE(CL.MATURITY_DATE, 'YYYYMMDD')) NEXT_PAYMENT_DATE, --下次支付日
NVL(NP.NEXT_PAYDATE, TO_DATE(CL.MATURITY_DATE, 'YYYYMMDD')) -
NVL(LP.LAST_PAYDATE, TO_DATE(AL.PUTOUTDATE, 'YYYY/MM/DD')) PMT_FREQ, --还款频率
'D' PMT_FREQ_MULT, --还款频率单位
CASE
WHEN NVL(NP.NEXT_PAYDATE,
TO_DATE(CL.MATURITY_DATE, 'YYYY/MM/DD')) =
NVL(LP.LAST_PAYDATE,
TO_DATE(AL.PUTOUTDATE, 'YYYY/MM/DD')) THEN
1
ELSE
CEIL((TO_DATE(CL.MATURITY_DATE, 'YYYY/MM/DD') - P_AS_OF_DATE) /
(NVL(NP.NEXT_PAYDATE,
TO_DATE(CL.MATURITY_DATE, 'YYYY/MM/DD')) -
NVL(LP.LAST_PAYDATE,
TO_DATE(AL.PUTOUTDATE, 'YYYY/MM/DD'))))
END REMAIN_NO_PMTS_C,
--LOANRATEMODE未联动，只取REPRICETYPE定类型
CASE
WHEN /*AL.LOANRATEMODE = '1' AND*/
NVL(AL.REPRICETYPE, '1') <> '7' THEN
'250'
ELSE
'0'
END ADJUSTABLE_TYPE_CD, --利率调整方式
ETL.GET_LOAN_LAST_REPRICEDAY(P_AS_OF_DATE,
AL.REPRICETYPE,
TO_DATE(AL.PUTOUTDATE,
'YYYY/MM/DD'),
DECODE(NVL(AL.CYCLEMONTHS, 1),
0,
1),
NVL(LP.LAST_PAYDATE,
TO_DATE(AL.PUTOUTDATE,
'YYYY/MM/DD'))) LAST_REPRICE_DATE, --上次重定价日
ETL.GET_LOAN_NEXT_REPRICEDAY(P_AS_OF_DATE,
AL.REPRICETYPE,
TO_DATE(AL.PUTOUTDATE,
'YYYY/MM/DD'),
TO_DATE(CL.MATURITY_DATE,
'YYYYMMDD'),
DECODE(NVL(AL.CYCLEMONTHS, 1),
0,
1),
NVL(NP.NEXT_PAYDATE,
TO_DATE(CL.MATURITY_DATE,
'YYYYMMDD'))) NEXT_REPRICE_DATE, --下次重定价日
--修改：上下次重定价相差整月，取整月数
CASE
WHEN MONTHS_BETWEEN(ETL.GET_LOAN_NEXT_REPRICEDAY(P_AS_OF_DATE,
AL.REPRICETYPE,
TO_DATE(AL.PUTOUTDATE,
'YYYY/MM/DD'),
TO_DATE(CL.MATURITY_DATE,
'YYYYMMDD'),
DECODE(NVL(AL.CYCLEMONTHS,
1),
0,
1),
NVL(NP.NEXT_PAYDATE,
TO_DATE(CL.MATURITY_DATE,
'YYYYMMDD'))),
ETL.GET_LOAN_LAST_REPRICEDAY(P_AS_OF_DATE,
AL.REPRICETYPE,
TO_DATE(AL.PUTOUTDATE,
'YYYY/MM/DD'),
DECODE(NVL(AL.CYCLEMONTHS,
1),
0,
1),
NVL(LP.LAST_PAYDATE,
TO_DATE(AL.PUTOUTDATE,
'YYYY/MM/DD')))) =
ROUND(MONTHS_BETWEEN(ETL.GET_LOAN_NEXT_REPRICEDAY(P_AS_OF_DATE,
AL.REPRICETYPE,
TO_DATE(AL.PUTOUTDATE,
'YYYY/MM/DD'),
TO_DATE(CL.MATURITY_DATE,
'YYYYMMDD'),
DECODE(NVL(AL.CYCLEMONTHS,
1),
0,
1),
NVL(NP.NEXT_PAYDATE,
TO_DATE(CL.MATURITY_DATE,
'YYYYMMDD'))),
ETL.GET_LOAN_LAST_REPRICEDAY(P_AS_OF_DATE,
AL.REPRICETYPE,
TO_DATE(AL.PUTOUTDATE,
'YYYY/MM/DD'),
DECODE(NVL(AL.CYCLEMONTHS,
1),
0,
1),
NVL(LP.LAST_PAYDATE,
TO_DATE(AL.PUTOUTDATE,
'YYYY/MM/DD'))))) --等于整数
THEN
MONTHS_BETWEEN(ETL.GET_LOAN_NEXT_REPRICEDAY(P_AS_OF_DATE,
AL.REPRICETYPE,
TO_DATE(AL.PUTOUTDATE,
'YYYY/MM/DD'),
TO_DATE(CL.MATURITY_DATE,
'YYYYMMDD'),
DECODE(NVL(AL.CYCLEMONTHS,
1),
0,
1),
NVL(NP.NEXT_PAYDATE,
TO_DATE(CL.MATURITY_DATE,
'YYYYMMDD'))),
ETL.GET_LOAN_LAST_REPRICEDAY(P_AS_OF_DATE,
AL.REPRICETYPE,
TO_DATE(AL.PUTOUTDATE,
'YYYY/MM/DD'),
DECODE(NVL(AL.CYCLEMONTHS,
1),
0,
1),
NVL(LP.LAST_PAYDATE,
TO_DATE(AL.PUTOUTDATE,
'YYYY/MM/DD'))))
ELSE --取天数
ETL.GET_LOAN_NEXT_REPRICEDAY(P_AS_OF_DATE,
AL.REPRICETYPE,
TO_DATE(AL.PUTOUTDATE,
'YYYY/MM/DD'),
TO_DATE(CL.MATURITY_DATE,
'YYYYMMDD'),
DECODE(NVL(AL.CYCLEMONTHS, 1),
0,
1),
NVL(NP.NEXT_PAYDATE,
TO_DATE(CL.MATURITY_DATE,
'YYYYMMDD'))) -
ETL.GET_LOAN_LAST_REPRICEDAY(P_AS_OF_DATE,
AL.REPRICETYPE,
TO_DATE(AL.PUTOUTDATE,
'YYYY/MM/DD'),
DECODE(NVL(AL.CYCLEMONTHS, 1),
0,
1),
NVL(LP.LAST_PAYDATE,
TO_DATE(AL.PUTOUTDATE,
'YYYY/MM/DD')))
END REPRICE_FREQ, --重定价频率
--修改：若重定价周期为整月 取整
CASE
WHEN MONTHS_BETWEEN(ETL.GET_LOAN_NEXT_REPRICEDAY(P_AS_OF_DATE,
AL.REPRICETYPE,
TO_DATE(AL.PUTOUTDATE,
'YYYY/MM/DD'),
TO_DATE(CL.MATURITY_DATE,
'YYYYMMDD'),
DECODE(NVL(AL.CYCLEMONTHS,
1),
0,
1),
NVL(NP.NEXT_PAYDATE,
TO_DATE(CL.MATURITY_DATE,
'YYYYMMDD'))),
ETL.GET_LOAN_LAST_REPRICEDAY(P_AS_OF_DATE,
AL.REPRICETYPE,
TO_DATE(AL.PUTOUTDATE,
'YYYY/MM/DD'),
DECODE(NVL(AL.CYCLEMONTHS,
1),
0,
1),
NVL(LP.LAST_PAYDATE,
TO_DATE(AL.PUTOUTDATE,
'YYYY/MM/DD')))) =
ROUND(MONTHS_BETWEEN(ETL.GET_LOAN_NEXT_REPRICEDAY(P_AS_OF_DATE,
AL.REPRICETYPE,
TO_DATE(AL.PUTOUTDATE,
'YYYY/MM/DD'),
TO_DATE(CL.MATURITY_DATE,
'YYYYMMDD'),
DECODE(NVL(AL.CYCLEMONTHS,
1),
0,
1),
NVL(NP.NEXT_PAYDATE,
TO_DATE(CL.MATURITY_DATE,
'YYYYMMDD'))),
ETL.GET_LOAN_LAST_REPRICEDAY(P_AS_OF_DATE,
AL.REPRICETYPE,
TO_DATE(AL.PUTOUTDATE,
'YYYY/MM/DD'),
DECODE(NVL(AL.CYCLEMONTHS,
1),
0,
1),
NVL(LP.LAST_PAYDATE,
TO_DATE(AL.PUTOUTDATE,
'YYYY/MM/DD'))))) --等于整数
THEN
'M'
ELSE
'D'
END REPRICE_FREQ_MULT, --重定价频率单位
CL.LOAN_AMT LRD_BALANCE, --上次重定价日余额
NVL(SL.INT_ADJUSTMENT, 0) INT_ADJUSTMENT, --利息调整
TO_DATE(AL.PUTOUTDATE, 'YYYY/MM/DD') ACCOUNT_OPEN_DATE, --开户日期
NVL(CI.MFCUSTOMERID, CI.CUSTOMERID) CIF_KEY, --客户号
CI.CUSTOMERTYPE CIF_TYPE, --客户类型
B.ACCRUED_INTEREST ACCRUED_INTEREST, --利息收入
'LOAN_CWWS_ACCT_LOAN' DATA_SOURCE, --数据来源
0 INT_IE, --实收利息(账户的实际收息、付息)（累计值）
SUBSTR(FUNC_GET_PRODUCT('CMS',
CASE
WHEN CL.LOAN_STATUS = '20' AND AL.BUSINESSTYPE = '1010010' AND
CL.GL_CODE = '70107' THEN
'1010010A'
WHEN CL.LOAN_STATUS = '20' AND AL.BUSINESSTYPE = '1170010' AND
CL.GL_CODE = '70107' THEN
'1170010A'
WHEN CL.LOAN_STATUS = '20' AND AL.BUSINESSTYPE = '1010020' AND
CL.GL_CODE = '70107' THEN
'1010020A'
WHEN CL.LOAN_STATUS = '20' AND AL.BUSINESSTYPE = '1130030' AND
CL.GL_CODE = '70107' THEN
'1130030A'
ELSE
AL.BUSINESSTYPE
END,
CASE
WHEN CL.LOAN_STATUS = '20' AND AL.BUSINESSTYPE = '1010010' AND
CL.GL_CODE = '70107' THEN
'9990101'
WHEN CL.LOAN_STATUS = '20' AND AL.BUSINESSTYPE = '1170010' AND
CL.GL_CODE = '70107' THEN
'9990102'
WHEN CL.LOAN_STATUS = '20' AND AL.BUSINESSTYPE = '1010020' AND
CL.GL_CODE = '70107' THEN
'9990103'
WHEN CL.LOAN_STATUS = '20' AND AL.BUSINESSTYPE = '1130030' AND
CL.GL_CODE = '70107' THEN
'9990104'
ELSE
CL.GL_CODE
END,
''),
1,
INSTR(FUNC_GET_PRODUCT('CMS',
CASE
WHEN CL.LOAN_STATUS = '20' AND AL.BUSINESSTYPE = '1010010' AND
CL.GL_CODE = '70107' THEN
'1010010A'
WHEN CL.LOAN_STATUS = '20' AND AL.BUSINESSTYPE = '1170010' AND
CL.GL_CODE = '70107' THEN
'1170010A'
WHEN CL.LOAN_STATUS = '20' AND AL.BUSINESSTYPE = '1010020' AND
CL.GL_CODE = '70107' THEN
'1010020A'
WHEN CL.LOAN_STATUS = '20' AND AL.BUSINESSTYPE = '1130030' AND
CL.GL_CODE = '70107' THEN
'1130030A'
ELSE
AL.BUSINESSTYPE
END,
CASE
WHEN CL.LOAN_STATUS = '20' AND AL.BUSINESSTYPE = '1010010' AND
CL.GL_CODE = '70107' THEN
'9990101'
WHEN CL.LOAN_STATUS = '20' AND AL.BUSINESSTYPE = '1170010' AND
CL.GL_CODE = '70107' THEN
'9990102'
WHEN CL.LOAN_STATUS = '20' AND AL.BUSINESSTYPE = '1010020' AND
CL.GL_CODE = '70107' THEN
'9990103'
WHEN CL.LOAN_STATUS = '20' AND AL.BUSINESSTYPE = '1130030' AND
CL.GL_CODE = '70107' THEN
'9990104'
ELSE
CL.GL_CODE
END,
''),
'-',
1) - 1) DEPARTMENT, --条线 --增加9990103科目，所以新增一段casewhen代码 WZG 20170427
--增加9990104科目，所以新增一段casewhen代码 WZG 20170630
CL.CLIENT_INDUSTRY INDUSTRYTYPE, --行业
CL.CMIS_FIVE_CLASS CLASSIFY, --五级分类
CI.C_EVALUATE C_EVALUATE, --信用等级
AL.VOUCHTYPE VOUCHTYPE, --担保方式
BC.MANAGEUSERID ACCOUNT_MGR, --客户经理
NULL CHANNEL, --渠道
NVL(SL.PRVN_AMT, 0) PRVN_AMT, --减值准备金额
CASE
WHEN BC.USEORGLIST = '1' THEN
'1'
ELSE
'2'
END BUSINESS_DEPARTMENT, --员工贷标识 1-是 2-否 modify by 盛夏冰
NULL INTEREST_SUM_M, --应计利息区间累积值(月）
NULL INTEREST_SUM_Y, --应计利息区间累积值（年）
CL.CORP_SIZE MINENT_FLAG, --企业规模
NULL GUARD_BAL, --担保金额
NULL GUARD_BAL_PART, --分户承担的担保金额
C.PRVN_GL_ACCOUNT_ID PRVN_GL_ACCOUNT_ID, --减值损失准备科目
AL.REPRICETYPE REPRICE_TYPE, --重定价方式
CASE
WHEN CL.LOAN_STATUS = '20' AND AL.BUSINESSTYPE = '1010010' AND
CL.GL_CODE = '70107' THEN
'1010010A'
WHEN CL.LOAN_STATUS = '20' AND AL.BUSINESSTYPE = '1170010' AND
CL.GL_CODE = '70107' THEN
'1170010A'
WHEN CL.LOAN_STATUS = '20' AND AL.BUSINESSTYPE = '1010020' AND
CL.GL_CODE = '70107' THEN
'1010020A'
WHEN CL.LOAN_STATUS = '20' AND AL.BUSINESSTYPE = '1130030' AND
CL.GL_CODE = '70107' THEN
'1130030A'
ELSE
AL.BUSINESSTYPE
END PRODUCT_CODE, --原产品号 --增加9990103科目，所以新增一段casewhen代码 WZG 20170427
--增加9990104科目，所以新增一段casewhen代码 WZG 20170630
BC.MANAGEORGID --管户机构
FROM NYBDP.O_SS_CL_LOAN_ACCT_STATIS CL --xxxx账户状态表
LEFT JOIN NYBDP.O_CWWS_ACCT_LOAN AL --xxxx账户统计表
ON (CL.DRAW_NO = AL.SERIALNO AND P_AS_OF_DATE = AL.AS_OF_DATE)
LEFT JOIN NYBDP.O_CWWS_BUSINESS_DUEBILL BD --借据表
ON (AL.SERIALNO = BD.SERIALNO AND P_AS_OF_DATE = BD.AS_OF_DATE)
LEFT JOIN NYBDP.O_CWWS_BUSINESS_CONTRACT BC --合同表
ON (BD.RELATIVESERIALNO2 = BC.SERIALNO AND
BC.AS_OF_DATE = P_AS_OF_DATE) --EDIT BY MSW20160302
-- ON (BD.RELATIVESERIALNO2=BC.SERIALNO AND BD.AS_OF_DATE=BC.AS_OF_DATE)
LEFT JOIN NYBDP.O_CWWS_BUSINESS_PUTOUT BP --业务出账表
ON (BD.RELATIVESERIALNO1 = BP.SERIALNO AND
BP.AS_OF_DATE = P_AS_OF_DATE) --EDIT BY MSW20160302
-- ON (BD.RELATIVESERIALNO1=BP.SERIALNO AND BD.AS_OF_DATE=BP.AS_OF_DATE)
--关联客户信息表，取客户信息
LEFT JOIN (SELECT T6.CUSTOMERID,
T6.MFCUSTOMERID,
T6.CUSTOMERTYPE,
T7.CREDITLEVEL C_EVALUATE
FROM (SELECT DISTINCT CUSTOMERID,
CUSTOMERTYPE,
MFCUSTOMERID
FROM NYBDP.O_CWWS_CUSTOMER_INFO
WHERE AS_OF_DATE = P_AS_OF_DATE) T6
LEFT JOIN (SELECT CUSTOMERID,
CREDITLEVEL,
OTHERCREDITLEVEL,
EVALUATELEVEL,
INDUSTRYTYPE
FROM NYBDP.O_CWWS_ENT_INFO
WHERE AS_OF_DATE = P_AS_OF_DATE) T7
ON T6.CUSTOMERID = T7.CUSTOMERID) CI
ON AL.CUSTOMERID = CI.CUSTOMERID
LEFT JOIN --取减值准备金额和利息调整金额
(SELECT ACCOUNTNO,
SUM(CASE
WHEN B.SUB_NAME LIKE '%准备%' THEN
DECODE(DIRECTION,
'D',
DEBITBALANCE - CREDITBALANCE,
CREDITBALANCE - DEBITBALANCE)
ELSE
0
END) PRVN_AMT,
SUM(CASE
WHEN B.SUB_NAME LIKE '%利息调整%' THEN
DECODE(DIRECTION,
'D',
DEBITBALANCE - CREDITBALANCE,
CREDITBALANCE - DEBITBALANCE)
ELSE
0
END) INT_ADJUSTMENT,
SUM(CASE
WHEN B.SUB_NAME LIKE '%利息收入%' THEN
DECODE(DIRECTION,
'D',
DEBITBALANCE - CREDITBALANCE,
CREDITBALANCE - DEBITBALANCE)
ELSE
0
END) ACCRUED_INTEREST
FROM ETL.O_CWWS_ACCT_SUBSIDIARY_LEDGER A,
(SELECT DISTINCT SUB_CODE, SUB_NAME
FROM ETL.O_CAS_COMC_SUBJECT
WHERE AS_OF_DATE = P_AS_OF_DATE) B
WHERE A.CORESUBJECTNO = B.SUB_CODE
AND DECODE(DIRECTION,
'D',
DEBITBALANCE - CREDITBALANCE,
CREDITBALANCE - DEBITBALANCE) <> 0
AND A.AS_OF_DATE = P_AS_OF_DATE
GROUP BY A.ACCOUNTNO) SL
ON AL.ACCOUNTNO = SL.ACCOUNTNO
LEFT JOIN NYBDP.O_CAS_COMC_CURR CC --币种表
ON (CL.CCY = CC.CUR_CODE AND P_AS_OF_DATE = CC.AS_OF_DATE)
LEFT JOIN ETL.TMP_XD_LAST_PAYDATE LP --上次支付日临时表
ON CL.DRAW_NO = LP.OBJECTNO
LEFT JOIN ETL.TMP_XD_NEXT_PAYDATE NP --下次支付日临时表
ON CL.DRAW_NO = NP.OBJECTNO
LEFT JOIN --关联还款计划表，取下次支付本金和下次支付利息
(SELECT A.OBJECTNO, A.PAYCORPUSAMT, A.PAYINTEAMT
FROM NYBDP.O_CWWS_ACCT_PAYMENT_SCHEDULE A
WHERE A.AS_OF_DATE = P_AS_OF_DATE
AND A.SEQID <> '999'
AND (A.OBJECTNO, TO_DATE(A.PAYDATE, 'YYYY/MM/DD')) IN
(SELECT B.OBJECTNO, B.NEXT_PAYDATE
FROM ETL.TMP_XD_NEXT_PAYDATE B)
AND A.SDATE <= TO_CHAR(P_AS_OF_DATE, 'YYYYMMDD') --add by msw 2016-03-14
AND A.EDATE >= TO_CHAR(P_AS_OF_DATE, 'YYYYMMDD') --add by msw 2016-03-14
) T
ON CL.DRAW_NO = T.OBJECTNO
LEFT JOIN --取利息收入和利息科目信息
(SELECT /*+PARALLEL(8)*/
T.ACCOUNTNO,
T.CORESUBJECTNO INT_SUB_CODE,
T1.SUB_NAME INT_SUB_NAME,
SUM(CASE
WHEN DIRECTION = 'C' THEN
T.CREDITAMT
WHEN DIRECTION = 'D' THEN
T.DEBITAMT --MODIFY BY ZYF 20180124
ELSE
0
END) ACCRUED_INTEREST
FROM ETL.O_CWWS_ACCT_SUBLEDGER_DETAIL T
JOIN ETL.O_CAS_COMC_SUBJECT T1
ON T.CORESUBJECTNO = T1.SUB_CODE
WHERE (T1.SUB_NAME LIKE '%利息收入%')
AND T.SENDFLAG = '1'
AND T.AS_OF_DATE = P_AS_OF_DATE
AND T1.AS_OF_DATE = P_AS_OF_DATE
GROUP BY T.ACCOUNTNO, T.CORESUBJECTNO, T1.SUB_NAME) B
ON AL.ACCOUNTNO = B.ACCOUNTNO
LEFT JOIN --取减值损失准备科目
(SELECT T.ACCOUNTNO,
T.CORESUBJECTNO PRVN_GL_ACCOUNT_ID,
T.SUB_NAME,
T.OBJECTNO
FROM (SELECT SL.ACCOUNTNO,
SL.SERIALNO,
SL.CORESUBJECTNO,
SL.OBJECTNO,
SUB.SUB_NAME,
RANK() OVER(PARTITION BY SL.ACCOUNTNO ORDER BY SL.SERIALNO DESC) SEQ
FROM ETL.O_CWWS_ACCT_SUBSIDIARY_LEDGER SL
JOIN ETL.O_CAS_COMC_SUBJECT SUB
ON SL.CORESUBJECTNO = SUB.SUB_CODE
WHERE SUB.SUB_NAME LIKE '%准备%'
AND SUB.AS_OF_DATE = P_AS_OF_DATE
AND SL.AS_OF_DATE = P_AS_OF_DATE) T
WHERE T.SEQ = 1) C
ON CL.DRAW_NO = C.ACCOUNTNO
/*WHERE CL.LOAN_TYPE NOT IN ('2070', '2110040', '2110010') --xxxx类型
AND CL.AGG_YTD <> 0
AND CL.AS_OF_DATE = P_AS_OF_DATE
AND AL.ACCOUNTNO IS NOT NULL;*/
WHERE (CL.LOAN_TYPE NOT IN ('2070', '2110040', '2110010') --xxxx类型
AND CL.AGG_YTD <> 0 AND CL.AS_OF_DATE = P_AS_OF_DATE AND
AL.ACCOUNTNO IS NOT NULL)
OR (CL.LOAN_TYPE NOT IN ('2070', '2110040', '2110010') --xxxx类型
AND CL.AGG_YTD = 0 AND CL.AS_OF_DATE = P_AS_OF_DATE AND
AL.ACCOUNTNO IS NOT NULL AND
(CL.LOAN_BALANCE <> 0 OR B.ACCRUED_INTEREST <> 0)); --将原来因为年积数为0导致过滤掉的提前还款的数据在不影响原来数据的情况下重新获取
--所以将原来的查询结果过滤条件改为其中一种情况，另一种情况为年积数为0，
--但当天余额或者当天发生额不为0的请况，两种情况只要符合其中一种就取数据插入表
--WZG 20170622
INSERT INTO M_RUNLOG VALUES (SYSDATE, V_PROC_NAME, 'it is 6');
COMMIT;
MERGE INTO ETL.MA_F_LOAN A
USING (SELECT /*+PARALLEL(8)*/
T.ACCOUNT_NUMBER, T.GL_ACCOUNT_ID, T.INT_GL_ACCOUNT_ID
FROM ETL.MA_F_LOAN T
INNER JOIN ETL.MA_D_GL_SUBJECT T1
ON T.INT_GL_ACCOUNT_ID = T1.SUBJECT_NO3
AND T1.SUBJECT_NAME3 LIKE '%已减值%'
AND T1.AS_OF_DATE = P_AS_OF_DATE
WHERE T.AS_OF_DATE = P_AS_OF_DATE
AND T.ACCOUNT_NUMBER IN
(SELECT ACCOUNT_NUMBER
FROM (SELECT /*+PARALLEL(8)*/
T2.ACCOUNT_NUMBER, COUNT(1)
FROM ETL.MA_F_LOAN T2
WHERE T2.AS_OF_DATE = P_AS_OF_DATE
GROUP BY T2.ACCOUNT_NUMBER
HAVING COUNT(1) > 1))) B
ON (A.ACCOUNT_NUMBER = B.ACCOUNT_NUMBER AND A.AS_OF_DATE = P_AS_OF_DATE AND A.GL_ACCOUNT_ID = B.GL_ACCOUNT_ID AND A.INT_GL_ACCOUNT_ID = B.INT_GL_ACCOUNT_ID)
WHEN MATCHED THEN
UPDATE SET A.CUR_BOOK_BAL = 0, A.OVERDUE_BAL = 0;
COMMIT;
--信贷系统12月14日上线新逻辑，导致原核销xxxx数据余额为0的上线后余额不再为0，需要更新核销xxxx数据的特定金额字段值更新为0。 WZG 20171220
UPDATE MA_F_LOAN T
SET T.CUR_BOOK_BAL = 0, T.SUM_BOOK_BAL_M = 0, T.AVG_BOOK_BAL_M = 0
WHERE T.AS_OF_DATE = P_AS_OF_DATE
AND EXISTS (SELECT 1
FROM NYBDP.O_CWWS_BUSINESS_DUEBILL BD
WHERE T.ACCOUNT_ID = BD.SERIALNO
AND BD.AS_OF_DATE = T.AS_OF_DATE
AND BD.BUSINESSSTATUS = '6');
INSERT INTO M_RUNLOG VALUES (SYSDATE, V_PROC_NAME, 'it is 6.2');
COMMIT;
--更新逾期xxxx上下次重订价日及重订价频率为起息日、到期日
UPDATE MA_F_LOAN A
SET LAST_REPRICE_DATE = A.ORIGINATION_DATE,
NEXT_REPRICE_DATE = A.MATURITY_DATE,
REPRICE_FREQ = A.ORG_TERM,
REPRICE_FREQ_MULT = A.ORG_TERM_MULT,
ADJUSTABLE_TYPE_CD = 0
WHERE A.MATURITY_DATE <= P_AS_OF_DATE
AND A.CUR_BOOK_BAL <> 0;
/*AND A.ADJUSTABLE_TYPE_CD = '250'*/
INSERT INTO M_RUNLOG VALUES (SYSDATE, V_PROC_NAME, 'it is 7');
--更新逾期xxxx固定利率重定价信息
/*UPDATE MA_F_LOAN A
SET LAST_REPRICE_DATE = P_AS_OF_DATE,
NEXT_REPRICE_DATE = P_AS_OF_DATE+1,
REPRICE_FREQ = 1,
REPRICE_FREQ_MULT = 'D'
WHERE A.MATURITY_DATE <= P_AS_OF_DATE
AND A.CUR_BOOK_BAL <> 0
AND A.ADJUSTABLE_TYPE_CD = '0'
AND A.LAST_REPRICE_DATE>=A.NEXT_REPRICE_DATE;*/
--更新逾期xxxx相关信息
/* UPDATE MA_F_LOAN A
SET A.ORIGINATION_DATE=P_AS_OF_DATE,
A.MATURITY_DATE=P_AS_OF_DATE+1，
A.ORG_TERM=1,
A.LAST_PAYMENT_DATE=P_AS_OF_DATE,
A.NEXT_PAYMENT_DATE=P_AS_OF_DATE+1，
A.PMT_FREQ=1,
A.CUR_PAYMENT=A.CUR_BOOK_BAL,
A.CUR_PAYMENT_INT=0
WHERE A.MATURITY_DATE < P_AS_OF_DATE;*/
--资产腾挪数据
UPDATE MA_F_LOAN
SET ACCOUNT_ID = ACCOUNT_ID || 'A'
WHERE AS_OF_DATE = P_AS_OF_DATE
AND GL_ACCOUNT_ID IN ('9990101', '9990102', '9990103', '9990104'); --增加9990103科目，所以新增一个条件 WZG 20170427
--增加9990104科目，所以新增一个条件 WZG 20170630
INSERT INTO M_RUNLOG VALUES (SYSDATE, V_PROC_NAME, 'it is 8');
COMMIT;
--更新资产腾挪xxxx为固定利率xxxx,计息方式为act/360
UPDATE MA_F_LOAN T
SET T.LAST_REPRICE_DATE = T.ORIGINATION_DATE,
T.NEXT_REPRICE_DATE = T.MATURITY_DATE,
T.ORG_TERM = T.MATURITY_DATE - T.ORIGINATION_DATE,
T.REPRICE_FREQ = T.MATURITY_DATE - T.ORIGINATION_DATE,
T.ADJUSTABLE_TYPE_CD = '0',
T.INT_GL_ACCOUNT_ID = '972301',
T.ACCRUED_INTEREST = ROUND(T.CUR_BOOK_BAL * T.CUR_NET_RATE / 360 /
100.00,
2)
WHERE T.AS_OF_DATE = P_AS_OF_DATE
AND T.GL_ACCOUNT_ID = '9990101';
INSERT INTO M_RUNLOG VALUES (SYSDATE, V_PROC_NAME, 'it is 9');
COMMIT;
END IF;
INSERT INTO M_RUNLOG VALUES (SYSDATE, V_PROC_NAME, 'it is 10');
COMMIT;
ETL.PROC_ETL_LOG(P_AS_OF_DATE,
V_PROC_NAME,
V_PROC_DESC,
V_COUNT,
1,
NULL,
NULL);
RET_FLG := '0';
RET_MSG := '执行成功';
EXCEPTION
WHEN OTHERS THEN
--写入异常日志
ETL.PROC_ETL_LOG(P_AS_OF_DATE,V_PROC_NAME,V_PROC_DESC,V_COUNT,-1,SQLCODE,SQLERRM);
RET_FLG := '1';
RET_MSG := SQLCODE || ':' || SQLERRM;
END;
/
点击源代码下载：http://kexuejisuan.com/download/project4python_sql_data_lineage.zip
腾讯云面向开发者汇聚海量精品云计算使用和开发经验，营造开放的云计算技术生态圈。
更多推荐
0
0- 0
已为社区贡献1条内容
所有评论(0)