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