Notes

select distinct tc.cif_no,tc.acct_no,status.cd,ptp.ptp_amount,ptp.dt_start,ptp.dt_end,a.dt_create,a.create_by, ptp.dt_update, tc.cpf_flag
from ebdcrs.t_act_react a
Inner join ebdcrs.t_mt
left join ebdcrs.t_ptp_details ptp on a.id = ptp.act_react_id
left join ebdcrs.t_mt_ptp_status status on status.id = ptp.mt_ptp_sts_id
inner join ebdcrs.t_cases tc on tc.id = a.tcase_id
where a.create_by in ('TUNT’, ‘ANH.LTM’)  
and tc.batch_id = 'CORP_CL'
and trunc(a.dt_create) >= trunc(to_date( '01/04/2020','dd/mm/yyyy'))


SELECT DISTINCT
--count(1) OVER () records,
b.cd branch_code,
cif.nm_line_1 customer_name,
cif.cif_no cif,
p.dscp current_pool,
nvl(acct.autho_limit,0) total_principal_amount ,
nvl(acct.amt_os_interest,0) total_interest_amount,
nvl(hc.valuation_val,0) the_latest_valuation_value_of_collateral
--cases.is_cured ,
--cases.is_out_of_bound
FROM t_cases cases
INNER JOIN t_assignments ta
ON ta.mcase_id = cases.mcase_id
INNER JOIN t_ht_cifs cif
ON cases.cif_no = cif.cif_no
AND cif.sys_apf_cd = cases.sys_apf_cd
LEFT JOIN t_mt_branches b
ON cases.mt_br_id = b.ID
AND b.is_del = 'N'
AND b.is_deactivate = 'N'
LEFT JOIN t_mt_pools p
ON p.ID = ta.curr_mt_pool_id
AND p.is_del = 'N'
AND p.is_deactivate = 'N'
LEFT JOIN t_ht_colls hc
ON hc.own_cif_no = cif.cif_no
LEFT JOIN t_ht_accts acct
ON cases.acct_no = acct.acct_no
--LEFT JOIN t_ht_acct_coll_rels ac
--ON ac.ht_acct_no = acct.acct_no
--AND ac.acct_sys_apf_cd = acct.sys_apf_cd
--AND ac.acct_mt_host_id = acct.mt_host_id
--INNER JOIN t_ht_colls hc
--ON hc.coll_id_core = ac.ht_coll_id
WHERE cases.is_cured = 'N'
AND cases.is_out_of_bound = 'N'
;