5,889
社区成员
发帖
与我相关
我的任务
分享
drop table MIR000000;
create table MIR000000 (CURR_PRCS_DT date);
--insert into MIR000000 values (date('2010-12-31'));
commit;
drop table MIRPSh001;
CREATE TABLE MIRPSH001 AS (
select s.LNMLMS100_IPKEY as LNMLMS100_IPKEY
, s.SEQ
, s.DUE_DT
, coalesce(s.INST_AMT, 0) as INST_AMT
, coalesce(s.PRIN_AMT, 0) as PRIN_AMT
, coalesce(s.INT_AMT, 0) as INT_AMT
, coalesce(s.TAX_AMT, 0) as TAX_AMT
, coalesce(h.PAID_P, 0) as PAID_P
, coalesce(h.PAID_I, 0) as PAID_I
, coalesce(h.PAID_O, 0) as PAID_O
, coalesce(h.PAID_F, 0) as PAID_F
, coalesce(h.PAID_R, 0) as PAID_R
, coalesce(h.PAID_T, 0) as PAID_T
, h.MIN_VAL_DT
, h.MAX_VAL_DT
, h.MIN_TX_DT
, h.MAX_TX_DT
from LNMPSH100 s
left join (
select h.LNMLMS100_IPKEY, h.LNMPSH100_SEQ
, sum(case when h.PAY_CAT = 'P' then coalesce(h.CR_AMT, 0) - coalesce(h.DR_AMT, 0) else 0 end) as PAID_P
, sum(case when h.PAY_CAT = 'I' then coalesce(h.CR_AMT, 0) - coalesce(h.DR_AMT, 0) else 0 end) as PAID_I
, sum(case when h.PAY_CAT = 'O' then coalesce(h.CR_AMT, 0) - coalesce(h.DR_AMT, 0) else 0 end) as PAID_O
, sum(case when h.PAY_CAT = 'F' then coalesce(h.CR_AMT, 0) - coalesce(h.DR_AMT, 0) else 0 end) as PAID_F
, sum(case when h.PAY_CAT = 'R' then coalesce(h.CR_AMT, 0) - coalesce(h.DR_AMT, 0) else 0 end) as PAID_R
, sum(case when h.PAY_CAT = 'T' then coalesce(h.CR_AMT, 0) - coalesce(h.DR_AMT, 0) else 0 end) as PAID_T
, min(h.VAL_DT) MIN_VAL_DT
, max(h.VAL_DT) MAX_VAL_DT
, min(h.TX_DT) MIN_TX_DT
, max(h.TX_DT) MAX_TX_DT
from LNMPHS100 h
left join LNMPHS100 r on h.LNMLMS100_IPKEY = r.LNMLMS100_IPKEY and h.TX_NO = r.TX_NO and r.REV_IND = 1
where h.DEL_IND = 0 and r.IPKEY is null and h.LNMPSH100_SEQ > 0
group by h.LNMLMS100_IPKEY, h.LNMPSH100_SEQ
) h on s.LNMLMS100_IPKEY = h.LNMLMS100_IPKEY and h.LNMPSH100_SEQ = s.SEQ
where s.DEL_IND = 0
) DATA INITIALLY DEFERRED
REFRESH DEFERRED
;
refresh table MIRPSH001;
create index MIRPSH0011 on MIRPSH001(LNMLMS100_IPKEY, SEQ);
drop table MIRPHS001;
create table MIRPHS001 as (
select h.LNMLMS100_IPKEY, h.LN_NO, h.TX_NO, h.TX_TYP_CODE, h.LNMPSH100_SEQ
, max(h.TTL_AMT) as TTL_AMT
, sum(case when h.PAY_CAT = 'P' then coalesce(h.CR_AMT, 0) - coalesce(h.DR_AMT, 0) else 0 end) as PAID_P
, sum(case when h.PAY_CAT = 'I' then coalesce(h.CR_AMT, 0) - coalesce(h.DR_AMT, 0) else 0 end) as PAID_I
, sum(case when h.PAY_CAT = 'O' then coalesce(h.CR_AMT, 0) - coalesce(h.DR_AMT, 0) else 0 end) as PAID_O
, sum(case when h.PAY_CAT = 'F' then coalesce(h.CR_AMT, 0) - coalesce(h.DR_AMT, 0) else 0 end) as PAID_F
, sum(case when h.PAY_CAT = 'R' then coalesce(h.CR_AMT, 0) - coalesce(h.DR_AMT, 0) else 0 end) as PAID_R
, sum(case when h.PAY_CAT = 'T' then coalesce(h.CR_AMT, 0) - coalesce(h.DR_AMT, 0) else 0 end) as PAID_T
, max(coalesce(r.REV_IND, 0)) as REV_IND
, max(h.VAL_DT) as VAL_DT
, max(h.TX_DT) as TX_DT
, max(r.TX_DT) as REV_DT
from LNMPHS100 h
left join LNMPHS100 r on h.LNMLMS100_IPKEY = r.LNMLMS100_IPKEY and h.TX_NO = r.TX_NO and r.REV_IND = 1
where h.DEL_IND = 0
group by h.LNMLMS100_IPKEY, h.LN_NO, h.TX_NO, h.TX_TYP_CODE, h.LNMPSH100_SEQ
) DATA INITIALLY DEFERRED
REFRESH DEFERRED
;
refresh table MIRPHS001;
create index MIRPHS0011 on MIRPHS001(LNMLMS100_IPKEY, TX_NO);
drop table MIRAIT001;
create table MIRAIT001 as (
select comcoi000.REF, comait000.ASSET_REF, comatg000.ATR_GRP_NAME, comatr000.ATR_CODE
, COMATR000.DATA_TYP, COMATR000.FRMT
, CODAIT020.STR_VAL, CODAIT020.INT_VAL, CODAIT020.DEC_VAL
, CODAIT020.DT_VAL, codait020.TM_VAL
, CODAIT020.CCY_CODE, CODAIT020.CCY_AMT
, CODAIT020.COADR000_IPKEY, CODAIT020.CONAM000_IPKEY, CODAIT020.COPHO000_IPKEY
, COMATG000.SEQ as ATR_GRP_SEQ
, CODATG010.SEQ as ATR_SEQ
from COMCOI000
join COMAIT000 on comait000.COCOI000_IPKEY = comcoi000.IPKEY and comait000.DEL_IND = 0
join CODAIT020 on codait020.COAIT000_IPKEY = comait000.IPKEY and codait020.DEL_IND = 0
join CODATG010 on codatg010.IPKEY = CODAIT020.COATG010_IPKEY and codatg010.DEL_IND = 0
join COMATG000 on CODATG010.COATG000_IPKEY = comatg000.IPKEY and comatg000.DEL_IND = 0
join COMATR000 on codatg010.COATR000_IPKEY = comatr000.IPKEY and COMATR000.DEL_IND = 0
where comcoi000.DEL_IND = 0
)
DATA INITIALLY DEFERRED
REFRESH DEFERRED
;
refresh table MIRAIT001;
drop table MIRDOI001;
create table MIRDOI001 as (
select comcoi000.REF, comdoi000.REF as DOC_REF, CODDOT010.DOT_GRP_CODE, comatr000.ATR_CODE
, COMATR000.DATA_TYP, COMATR000.FRMT
, CODDOI010.STR_VAL, CODDOI010.INT_VAL, CODDOI010.DEC_VAL
, CODDOI010.DT_VAL, CODDOI010.TM_VAL
, CODDOI010.CCY_CODE, CODDOI010.CCY_AMT
, CODDOI010.COADR000_IPKEY, CODDOI010.CONAM000_IPKEY, CODDOI010.COPHO000_IPKEY
, CODDOT010.SEQ as ATR_GRP_SEQ
, CODDOT020.SEQ as ATR_SEQ
from COMCOI000
join comdoi000 on comdoi000.COCOI000_IPKEY = comcoi000.IPKEY and comdoi000.DEL_IND = 0
join CODDOI010 on CODDOI010.CODOI000_IPKEY = comdoi000.IPKEY and CODDOI010.DEL_IND = 0
join CODDOT020 on CODDOT020.IPKEY = CODDOI010.CODOT020_IPKEY and CODDOT020.DEL_IND = 0
join CODDOT010 on CODDOT020.CODOT010_IPKEY = CODDOT010.IPKEY and CODDOT010.DEL_IND = 0
join COMATR000 on CODDOT020.COATR000_IPKEY = comatr000.IPKEY and COMATR000.DEL_IND = 0
where comcoi000.DEL_IND = 0
)
DATA INITIALLY DEFERRED
REFRESH DEFERRED
;
refresh table MIRDOI001;
drop table MIRLMS001;
create table MIRLMS001 as (
select ln.*
, round(ln.FIN_AMT * r.BOOK_RATE, 2) as FIN_LCY
, round(ln.OS_BAL * r.BOOK_RATE, 2) as OS_LCY
from (
select ln.IPKEY as LNMLMS100_IPKEY, ln.LN_NO, ln.CIF_NO, ln.CIF_GP, ln.CCY_CODE
, ln.FIN_AMT, ln.OS_BAL, ln.RATE_CODE, ln.ALL_IN_RATE, ln.RATE_TYP, ln.LN_TYP, ln.NEXT_DUE_DT
, ln.TENOR, ln.PAID_TENOR, ln.COMING_DUE_SEQ, ln.STS, ln.CR_LINE_CODE
, ln.LN_CRT_DT, ln.LN_STR_DT, ln.MAT_DT, ln.PRIN_FIRST_PAY_DT, ln.FIRST_PAY_DT
, ln.DUE_DAY
, ln.CRT_DT, ln.LN_TYP_CAT, ln.LOAN_SCORE_STR
, ln.DEL_IND
from LNMLMS100 ln
where ln.DEL_IND = 0 and ln.STS >= '500'
) ln
left join (
CLMCRF0L0 line
join CLMCRFTP0 f on line.FACILITY_TYP = f.FACILITY_TYP and f.DEL_IND = 0
) on ln.CIF_NO = line.INDI_CIF_NO and ln.CR_LINE_CODE = line.LINE_REF
left join (
CMP004000 r
join MIR000000 ts on r.EFF_DT = ts.CURR_PRCS_DT
) on ln.CCY_CODE = r.CCY_CODE
)
DATA INITIALLY DEFERRED
REFRESH DEFERRED
;
refresh table MIRLMS001;
drop table MIRLMS002;
create table MIRLMS002 as (
select ln.LN_NO, ln.CIF_NO, ln.CIF_GP, ln.CR_LINE_CODE as LINE_REF
, ln.CCY_CODE, ln.FIN_AMT, ln.OS_BAL
, round(ln.FIN_AMT * r.BOOK_RATE, 2) as FIN_LCY
, round(ln.OS_BAL * r.BOOK_RATE, 2) as OS_LCY
, coalesce(npl.PROVISION_AMT, 0) as PROVISION_AMT
, coalesce(round(npl.PROVISION_AMT * r.BOOK_RATE, 2), 0) as PROVISION_LCY
, sum(coalesce(round(npl.PROVISION_AMT * r.BOOK_RATE, 2), 0))
over() GRAND_PROVISION_TOTAL
from (
select LN_NO, CIF_NO, CIF_GP, CR_LINE_CODE, CCY_CODE, FIN_AMT, OS_BAL
from LNMLMS100
where DEL_IND = 0 and OS_BAL > 0
union
select BILLS_REF, CIF_NO, CIF_GP, CR_LINE_CODE, CCY_CODE, BILLS_AMT, OS_BAL
from BLMBMS100
where DEL_IND = 0 and OS_BAL > 0
) ln
left join CLMNPLPA0 npl on
(
( (ln.CIF_GP is null or ln.CIF_GP = '') and ln.CIF_NO = npl.CIF_NO)
or ln.CIF_GP = npl.CIF_NO
) and ln.CR_LINE_CODE = npl.LINE_REF
left join (
CMP004000 r
join MIR000000 ts on r.EFF_DT = ts.CURR_PRCS_DT
) on ln.CCY_CODE = r.CCY_CODE
)
DATA INITIALLY DEFERRED
REFRESH DEFERRED
;
refresh table MIRLMS002;
drop table MIRCMV009;
create table MIRCMV009 as(
select co.CO_REF, co.CMV_LCY
, ln.SYS_REF, ref.CMV_LCY as CMV_USED
, sum(coalesce(ref.CMV_LCY, 0)) over (partition by co.CO_REF) CMV_USED_TOTAL
from MIRCO0010 co
left join (
MIRCMV030 ref
join
(
select CIF_NO, CR_LINE_CODE, LN_NO as SYS_REF
from LNMLMS100
where DEL_IND = 0 and STS >= '500' and STS < '999'
union all
select CIF_NO, CR_LINE_CODE, BILLS_REF as SYS_REF
from BLMBMS100
where DEL_IND = 0
) ln
on ref.INDI_CIF_NO = ln.CIF_NO and ref.LINE_REF = ln.CR_LINE_CODE
) on co.CO_REF = ref.CO_REF and co.EFF_DT = ref.EFF_DT
join MIR000000 ts on co.EFF_DT = ts.CURR_PRCS_DT
)
DATA INITIALLY DEFERRED
REFRESH DEFERRED
;
refresh table MIRCMV009;
commit;