-- MERGE: USING with UNION and ORA_HASH in WHERE
MERGE INTO RPT.DET_LOAN_DELINQUENCY dao
USING (
    SELECT
        x.LOAN_ID AS LOAN_ID,
        x.DEBT_TYPE_ID AS ID_DEBT_TYPE,
        x.DLQ_FIFO_START_DT AS DT_DLQ_FIFO_START,
        x.DEBT_START_DT AS DT_DEBT_START,
        x.DEBT_END_DT AS DT_DEBT_END,
        x.CORR_FLG AS FLG_CORR,
        0 AS IS_DELETED,
        x.ID_SYSTEM AS ID_SYSTEM,
        p_process_id AS ID_PROCESS,
        SYSDATE AS DTTM_INSERT,
        SYSDATE AS DTTM_UPDATE
    FROM (
        SELECT ta.LOAN_ID, ta.DEBT_TYPE_ID, ta.DLQ_FIFO_START_DT, ta.DEBT_START_DT, ta.DEBT_END_DT, ta.CORR_FLG, ta.ID_SYSTEM
        FROM STG.STG_LOAN_DLQ ta
        LEFT JOIN STG.STG_LOAN_ID t ON t.ID = ta.LOAN_ID
        WHERE 1 = 1 AND t.ID IS NULL
        UNION
        SELECT tc.LOAN_ID, tc.DEBT_TYPE_ID, tc.DLQ_FIFO_START_DT, tc.DEBT_START_DT, tc.DEBT_END_DT, tc.CORR_FLG, tc.ID_SYSTEM
        FROM STG.STG_CL_DLQ tc
    ) x
) SOURCE
ON (dao.LOAN_ID = SOURCE.LOAN_ID AND dao.ID_DEBT_TYPE = SOURCE.ID_DEBT_TYPE AND dao.DT_DEBT_START = SOURCE.DT_DEBT_START AND dao.FLG_CORR = SOURCE.FLG_CORR)
WHEN MATCHED THEN UPDATE SET
    dao.DT_DLQ_FIFO_START = SOURCE.DT_DLQ_FIFO_START,
    dao.DT_DEBT_END = SOURCE.DT_DEBT_END,
    dao.ID_SYSTEM = SOURCE.ID_SYSTEM,
    dao.IS_DELETED = SOURCE.IS_DELETED,
    dao.ID_PROCESS = p_process_id,
    dao.DTTM_UPDATE = SYSDATE
WHERE ORA_HASH(dao.DT_DLQ_FIFO_START || dao.DT_DEBT_END || dao.IS_DELETED || dao.ID_SYSTEM) != ORA_HASH(SOURCE.DT_DLQ_FIFO_START || SOURCE.DT_DEBT_END || SOURCE.IS_DELETED || SOURCE.ID_SYSTEM)
WHEN NOT MATCHED THEN INSERT (
    LOAN_ID, ID_DEBT_TYPE, DT_DLQ_FIFO_START, DT_DEBT_START, DT_DEBT_END, FLG_CORR, IS_DELETED, ID_SYSTEM, ID_PROCESS, DTTM_INSERT, DTTM_UPDATE
)
VALUES (
    SOURCE.LOAN_ID, SOURCE.ID_DEBT_TYPE, SOURCE.DT_DLQ_FIFO_START, SOURCE.DT_DEBT_START, SOURCE.DT_DEBT_END, SOURCE.FLG_CORR, SOURCE.IS_DELETED, SOURCE.ID_SYSTEM, p_process_id, SYSDATE, SYSDATE
);
