-- MERGE: many LEFT JOINs aggregated into target via WHEN MATCHED/NOT MATCHED
MERGE INTO RPT.AGG_LOAN_DELINQUENCY u
USING (
    SELECT
        ea.LOAN_ID AS LOAN_ID,
        p_report_date AS REPORT_DT,
        od.DLQ_DPD AS CNT_DLQ_DPD,
        opd.CNT_DAYS AS CNT_DLQ_PRN_DPD,
        ond.CNT_DAYS AS CNT_DLQ_INT_DPD,
        ofod.CNT_DAYS AS CNT_DLQ_FEE_OP_DPD,
        ofid.CNT_DAYS AS CNT_DLQ_FEE_INT_DPD,
        on30f.NPL_30_FLG AS FLG_DLQ_NPL_30,
        on90f.NPL_90_FLG AS FLG_DLQ_NPL_90,
        o180c.DLQ_180_CNT AS CNT_DLQ_180,
        o180d.DLQ_180_DAYS_CNT AS CNT_DLQ_180_DAYS,
        rpd.REPAID_PRN_INT_DT AS DT_REPAID_PRN_INT,
        cod.CORR_DLQ_DPD AS CNT_CORR_DLQ_DPD,
        copd.CNT_DAYS AS CNT_CORR_DLQ_PRN_DPD,
        cond.CNT_DAYS AS CNT_CORR_DLQ_INT_DPD,
        cofod.CNT_DAYS AS CNT_CORR_DLQ_FEE_OP_DPD,
        cofid.CNT_DAYS AS CNT_CORR_DLQ_FEE_INT_DPD,
        con30f.CORR_NPL_30_FLG AS FLG_CORR_DLQ_NPL_30,
        con90f.CORR_NPL_90_FLG AS FLG_CORR_DLQ_NPL_90,
        co180c.CORR_DLQ_180_CNT AS CNT_CORR_DLQ_180,
        co180d.CORR_DLQ_180_DAYS_CNT AS CNT_CORR_DLQ_180_DAYS,
        crpd.CORR_REPAID_PRN_INT_DT AS DT_CORR_REPAID_PRN_INT,
        1 AS ID_SYSTEM,
        p_process_id AS ID_PROCESS,
        SYSDATE AS DTTM_INSERT,
        SYSDATE AS DTTM_UPDATE
    FROM STG.STG_LOAN_REPORT ea
    LEFT JOIN STG.STG_REPAID_DT rpd ON rpd.LOAN_ID = ea.LOAN_ID
    LEFT JOIN STG.STG_DLQ_PRN_DAYS opd ON opd.LOAN_ID = ea.LOAN_ID
    LEFT JOIN STG.STG_DLQ_INT_DAYS ond ON ond.LOAN_ID = ea.LOAN_ID
    LEFT JOIN STG.STG_DLQ_FEE_OP_DAYS ofod ON ofod.LOAN_ID = ea.LOAN_ID
    LEFT JOIN STG.STG_DLQ_FEE_INT_DAYS ofid ON ofid.LOAN_ID = ea.LOAN_ID
    LEFT JOIN STG.STG_DLQ_DPD od ON od.LOAN_ID = ea.LOAN_ID
    LEFT JOIN STG.STG_NPL30_FLG on30f ON on30f.LOAN_ID = ea.LOAN_ID
    LEFT JOIN STG.STG_NPL90_FLG on90f ON on90f.LOAN_ID = ea.LOAN_ID
    LEFT JOIN STG.STG_DLQ_180_CNT o180c ON o180c.LOAN_ID = ea.LOAN_ID
    LEFT JOIN STG.STG_DLQ_180_DAYS o180d ON o180d.LOAN_ID = ea.LOAN_ID
    LEFT JOIN STG.STG_CORR_REPAID_DT crpd ON crpd.LOAN_ID = ea.LOAN_ID
    LEFT JOIN STG.STG_CORR_DLQ_PRN_DAYS copd ON copd.LOAN_ID = ea.LOAN_ID
    LEFT JOIN STG.STG_CORR_DLQ_INT_DAYS cond ON cond.LOAN_ID = ea.LOAN_ID
    LEFT JOIN STG.STG_CORR_DLQ_FEE_OP_DAYS cofod ON cofod.LOAN_ID = ea.LOAN_ID
    LEFT JOIN STG.STG_CORR_DLQ_FEE_INT_DAYS cofid ON cofid.LOAN_ID = ea.LOAN_ID
    LEFT JOIN STG.STG_CORR_DLQ_DPD cod ON cod.LOAN_ID = ea.LOAN_ID
    LEFT JOIN STG.STG_CORR_NPL30_FLG con30f ON con30f.LOAN_ID = ea.LOAN_ID
    LEFT JOIN STG.STG_CORR_NPL90_FLG con90f ON con90f.LOAN_ID = ea.LOAN_ID
    LEFT JOIN STG.STG_CORR_DLQ_180_CNT co180c ON co180c.LOAN_ID = ea.LOAN_ID
    LEFT JOIN STG.STG_CORR_DLQ_180_DAYS co180d ON co180d.LOAN_ID = ea.LOAN_ID
) s
ON (u.LOAN_ID = s.LOAN_ID AND s.REPORT_DT = u.REPORT_DT)
WHEN MATCHED THEN UPDATE SET
    CNT_DLQ_DPD = s.CNT_DLQ_DPD,
    CNT_DLQ_PRN_DPD = s.CNT_DLQ_PRN_DPD,
    CNT_DLQ_INT_DPD = s.CNT_DLQ_INT_DPD,
    CNT_DLQ_FEE_OP_DPD = s.CNT_DLQ_FEE_OP_DPD,
    CNT_DLQ_FEE_INT_DPD = s.CNT_DLQ_FEE_INT_DPD,
    FLG_DLQ_NPL_30 = s.FLG_DLQ_NPL_30,
    FLG_DLQ_NPL_90 = s.FLG_DLQ_NPL_90,
    CNT_DLQ_180 = s.CNT_DLQ_180,
    CNT_DLQ_180_DAYS = s.CNT_DLQ_180_DAYS,
    DT_REPAID_PRN_INT = s.DT_REPAID_PRN_INT,
    CNT_CORR_DLQ_DPD = s.CNT_CORR_DLQ_DPD,
    CNT_CORR_DLQ_PRN_DPD = s.CNT_CORR_DLQ_PRN_DPD,
    CNT_CORR_DLQ_INT_DPD = s.CNT_CORR_DLQ_INT_DPD,
    CNT_CORR_DLQ_FEE_OP_DPD = s.CNT_CORR_DLQ_FEE_OP_DPD,
    CNT_CORR_DLQ_FEE_INT_DPD = s.CNT_CORR_DLQ_FEE_INT_DPD,
    FLG_CORR_DLQ_NPL_30 = s.FLG_CORR_DLQ_NPL_30,
    FLG_CORR_DLQ_NPL_90 = s.FLG_CORR_DLQ_NPL_90,
    CNT_CORR_DLQ_180 = s.CNT_CORR_DLQ_180,
    CNT_CORR_DLQ_180_DAYS = s.CNT_CORR_DLQ_180_DAYS,
    DT_CORR_REPAID_PRN_INT = s.DT_CORR_REPAID_PRN_INT,
    ID_SYSTEM = s.ID_SYSTEM,
    ID_PROCESS = s.ID_PROCESS,
    DTTM_UPDATE = SYSDATE
WHEN NOT MATCHED THEN INSERT
(LOAN_ID, REPORT_DT, CNT_DLQ_DPD, CNT_DLQ_PRN_DPD, CNT_DLQ_INT_DPD,
 CNT_DLQ_FEE_OP_DPD, CNT_DLQ_FEE_INT_DPD, FLG_DLQ_NPL_30, FLG_DLQ_NPL_90,
 CNT_DLQ_180, CNT_DLQ_180_DAYS, DT_REPAID_PRN_INT, CNT_CORR_DLQ_DPD,
 CNT_CORR_DLQ_PRN_DPD, CNT_CORR_DLQ_INT_DPD, CNT_CORR_DLQ_FEE_OP_DPD,
 CNT_CORR_DLQ_FEE_INT_DPD, FLG_CORR_DLQ_NPL_30, FLG_CORR_DLQ_NPL_90,
 CNT_CORR_DLQ_180, CNT_CORR_DLQ_180_DAYS, DT_CORR_REPAID_PRN_INT,
 ID_SYSTEM, ID_PROCESS, DTTM_INSERT, DTTM_UPDATE)
VALUES
(s.LOAN_ID, s.REPORT_DT, s.CNT_DLQ_DPD, s.CNT_DLQ_PRN_DPD, s.CNT_DLQ_INT_DPD,
 s.CNT_DLQ_FEE_OP_DPD, s.CNT_DLQ_FEE_INT_DPD, s.FLG_DLQ_NPL_30, s.FLG_DLQ_NPL_90,
 s.CNT_DLQ_180, s.CNT_DLQ_180_DAYS, s.DT_REPAID_PRN_INT, s.CNT_CORR_DLQ_DPD,
 s.CNT_CORR_DLQ_PRN_DPD, s.CNT_CORR_DLQ_INT_DPD, s.CNT_CORR_DLQ_FEE_OP_DPD,
 s.CNT_CORR_DLQ_FEE_INT_DPD, s.FLG_CORR_DLQ_NPL_30, s.FLG_CORR_DLQ_NPL_90,
 s.CNT_CORR_DLQ_180, s.CNT_CORR_DLQ_180_DAYS, s.DT_CORR_REPAID_PRN_INT,
 s.ID_SYSTEM, s.ID_PROCESS, SYSDATE, SYSDATE);
