Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- wf_m_fee_reversal
- s_m_fee_reversal
- --(AIW)SQ_fee_reversal
- SQL overwrite
- select fr.loan_id,
- fr.lpay_id,
- fr.fee_rev_dt,
- fr.delq_start_dt,
- fr.delq_end_dt,
- min(fll.chng_d) min_chng_d
- from
- fee_reversal fr, f_lc_loan_status_log fll
- where fr.loan_id=fll.loan_id
- and fll.new_status=10
- --and fr.loan_id=1062754--testing
- and chng_d > delq_start_dt
- group by 1,2,3,4,5
- --Exp_delq_end_dt
- Adds two output columns
- delq_end_dt_O = iif(min_chng_d > delq_start_dt, min_chng_d, delq_end_dt_old)
- aiw_Update_ts_O = sysdate
- --UPD_delq_end_dt
- dd_update
- --(AIW)RelTGG-fee_reveral_delq_end_dt
- target table is "fee_reversal"
- --(SVC) SQ_LC$SR
- ==============================
- select summary, loan_id, lpay_id, fee_reversal_d, fee_reversal_amt, max(chng_d), old_status
- from (
- SELECT summary,
- cast(to_number(sr.loan_id) as int) loan_id,
- CAST(TO_NUMBER(SUBSTR(DESCRIPTION,INSTR(UPPER(DESCRIPTION),'LPAYID=',1) + LENGTH('LPAYID='),INSTR(UPPER(DESCRIPTION),',',INSTR(UPPER(DESCRIPTION),'LPAYID=',1)) - (INSTR(UPPER(DESCRIPTION),'LPAYID=',1) + LENGTH('LPAYID=')))) AS INT) LPAY_ID,
- create_d fee_reversal_d,
- max(cast(to_number(SUBSTR(DESCRIPTION,INSTR(UPPER(DESCRIPTION),'FEE_BAL=',1) + LENGTH('FEE_BAL='),INSTR(UPPER(DESCRIPTION),',',INSTR(UPPER(DESCRIPTION),'FEE_BAL=',1))- (INSTR(UPPER(DESCRIPTION),'FEE_BAL=',1) + LENGTH('FEE_BAL=')))) as decimal(12,2))) fee_reversal_amt,
- ll.chng_d,
- ll.old_status
- FROM lc$sr sr, lc$loan_status_log ll
- WHERE
- sr.loan_id = ll.loan_id
- and UPPER(SUMMARY) = 'WAIVED FEES'
- AND INSTR(UPPER(DESCRIPTION),'LPAYID=NULL') = 0
- --and sr.loan_id=112216 --testing
- and old_status=10
- and to_number(to_char(create_d,'YYYYMMDD')) = (select etl_bus_date_key from etl_bus_date)
- --and trunc(create_d) = trunc(sysdate-1)
- GROUP BY sr.loan_id,summary, create_d,
- CAST(TO_NUMBER(SUBSTR(DESCRIPTION,INSTR(UPPER(DESCRIPTION),'LPAYID=',1) + LENGTH('LPAYID='),INSTR(UPPER(DESCRIPTION),',',INSTR(UPPER(DESCRIPTION),'LPAYID=',1)) - (INSTR(UPPER(DESCRIPTION),'LPAYID=',1) + LENGTH('LPAYID=')))) AS INT),
- ll.chng_d,ll.old_status
- HAVING max(cast(to_number(SUBSTR(DESCRIPTION,INSTR(UPPER(DESCRIPTION),'FEE_BAL=',1) + LENGTH('FEE_BAL='),INSTR(UPPER(DESCRIPTION),',',INSTR(UPPER(DESCRIPTION),'FEE_BAL=',1))- (INSTR(UPPER(DESCRIPTION),'FEE_BAL=',1) + LENGTH('FEE_BAL=')))) as decimal(12,2))) > 0
- )
- where fee_reversal_d > chng_d
- group by loan_id, summary, lpay_id, fee_reversal_d, fee_reversal_amt, old_status
- =================================SQL
- --EXP_fee_reversal
- Adds two output columns
- delq_end_dt_O to_date('31-DEC-9999','DD-MON-YYYY')
- sysdate_O sysdate
- --Fil_fee_rev_amt
- pass rows with "FEE_REVERSAL_D_O > chng_d"
- --AGG_fee_reversal
- groups by LOANID, SUMMARY, LPAY_ID_O, FEE_REVERSAL_D_O, FEE_REVERSAL_AMT_O,
- chng_d_O = min(chng_d)
- delq_end_dt_O = delq_end_dt_O ??????
- --(AIW) TGT (fee_reversal)
- Pre SQL=====>
- delete from fee_reversal where to_number(to_char(fee_rev_dt,'YYYYMMDD'))= (select etl_bus_date_key from etl_bus_date)
- --where trunc(fee_rev_dt)=trunc(sysdate-1)
- <====================
- Post SQL =====>
- update fee_reversal tgt
- set fee_rev_amt = src.running_fee_amt
- from (
- select loan_id, lpay_id, delq_start_dt, fee_rev_dt,sum(act_fee_rev_amt) over(partition by loan_id, delq_start_dt order by delq_start_dt,fee_rev_dt) as running_fee_amt
- from ai_dw.fee_reversal
- ) src
- where tgt.loan_id=src.loan_id
- and tgt.lpay_id=src.lpay_id
- and tgt.fee_rev_dt = src.fee_rev_dt
- <==========================
- s_m_fee_reversal_f_lpay_detail
- ==============================
- Runs the mapping m_fee_reversal_f_lpay_detail
- --(AIW) SQ_f_lpay_detail
- ============>
- select X.lpay_detail_key,
- X.id,
- X.loan_id,
- X.due_d,
- X.due_amt,
- X.adj_due_amt,
- X.chng_d,
- X.old_status,
- X.new_status,
- X.fee_rev_amt,
- X.fee_reversal_ind
- from (
- select fld.loan_id, fld.id, fld.lpay_detail_key, fld.due_d, fld.due_amt, fld.adj_due_amt, fll.chng_d, fll.old_status, fll.new_status, fr.fee_rev_amt, fll.gg_ts,fee_reversal_ind, row_number() over (partition by fld.loan_id, fld.id order by fld.gg_ts desc) rownum1
- from
- f_lpay_detail fld, fee_reversal fr,
- (select loan_id, max(fee_rev_dt) max_rev_dt from fee_reversal group by loan_id) fr1, f_lc_loan_status_log fll
- where fld.loan_id=fr.loan_id
- and fr.loan_id=fr1.loan_id
- and fld.loan_id=fll.loan_id
- and fld.due_amt > 0
- and trunc(fr.fee_rev_dt) = trunc(fr1.max_rev_dt)
- --and fld.loan_id=309299 --testing
- ) X, (select fld.due_d,fld.loan_id, fld.id,fld.due_amt, row_number() over (partition by fld.loan_id, fld.id order by fld.gg_ts desc) rownum2
- from F_LPAY_DETAIL fld) Y, fee_reversal Z, (select loan_id, max(fee_rev_dt) max_rev_dt from fee_reversal group by loan_id) fr1
- where X.rownum1=1
- and X.loan_id=Y.loan_id
- and Y.loan_id=Z.loan_id
- and Z.loan_id = fr1.loan_id
- and Y.id=Z.lpay_id
- and Y.due_amt > 0
- and Y.rownum2=1
- and X.due_d >= Y.due_d
- --and X.loan_id=309299 --testing
- and trunc(Z.fee_rev_dt) = trunc(fr1.max_rev_dt)
- <================
- --Exp_f_lpay_detail_fr
- does nothing....pass through
- __(AIW)tgt Relational (z_f_lpay_detail_fr)
- --AIW(rel reader)SQ_z_f_lpay_detail_fr
- SQL Overwrite ==========================>
- select fld.lpay_detail_key,
- fld.id,
- fld.loan_id,
- fld.due_d,
- fld.due_amt old_due_amt,
- fld.fee_rev_amt,
- (fld.due_amt - fld.fee_rev_amt) as adj_due_amt
- from z_f_lpay_detail_fr fld, fee_reversal fr
- where fld.loan_id=fr.loan_id
- and fld.due_d >= fr.delq_start_dt
- and fld.due_d <= fr.delq_end_dt
- <============================
- --EXP_f_lpay_detail
- Adds two new output columns
- fee_reversal_ind = 1
- aiw_update_ts_O = sysdate
- --UPD_f_lpay_detail
- dd_update
- --(AIW) f_lpay_detail_upd
- --(AIW)SQ_Dummy
- select dummy from dual
- --SQL_pre_prep
- ===============>
- Need to provide the custom SQL entered. Currently it is not readable by me?????
- <===============
- --dummy1
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement