[CFMAST] Detail query

SELECT DISTINCT * FROM (
    SELECT DISTINCT TO_CHAR(SRC_STM_ID) AS SRCID, TO_CHAR(CFCIF) AS CONTRACTNO, TO_CHAR(CFSNME) AS NAME, TO_CHAR(CFCIF) AS CIFNO, TO_CHAR(TIME_SK) AS TIME_SK, 'CFMAST' AS SourceTable
    FROM BMSTGCBSPS.CFMAST WHERE SRC_STM_ID = 1

    UNION

    SELECT DISTINCT TO_CHAR(COR.SRC_STM_ID) AS SRCID,
                    TO_CHAR(COR.CUSTID) AS CONTRACTNO,
                    TO_CHAR(CUST.CUSTNAME) AS NAME,
                    TO_CHAR(COR.CUSTID) AS CIFNO,
                    TO_CHAR(COR.TIME_SK) AS TIME_SK,
                    'CORACCOUNT' AS SourceTable
    FROM BMSTGCAPPS.CORACCOUNT COR

    LEFT OUTER JOIN (
        SELECT C.*
        FROM BMSTGCAPPS.CORACCOUNTDETAIL C
        INNER JOIN (SELECT NOREK, MAX(CORDT.CICILDATE) MAXCICILDATE FROM BMSTGCAPPS.CORACCOUNTDETAIL CORDT GROUP BY NOREK) TMP
        ON C.NOREK = TMP.NOREK AND C.CICILDATE = TMP.MAXCICILDATE
    ) CORDT ON COR.NOREK = CORDT.NOREK AND COR.SRC_STM_ID = CORDT.SRC_STM_ID

    LEFT OUTER JOIN (
        SELECT D.*
        FROM BMSTGCAPPS.CORACCOUNTDETAIL D
        INNER JOIN (SELECT NOREK, MIN(CORDTMIN.CICILDATE) AS MINCICILDATE FROM BMSTGCAPPS.CORACCOUNTDETAIL CORDTMIN GROUP BY NOREK) TMP
        ON D.NOREK = TMP.NOREK AND D.CICILDATE = TMP.MINCICILDATE
    ) CORDTMIN ON COR.NOREK = CORDTMIN.NOREK AND COR.SRC_STM_ID = CORDTMIN.SRC_STM_ID

    LEFT OUTER JOIN BMSTGCAPPS.GENCUSTOMER CUST ON COR.CUSTID = CUST.CUSTID AND COR.SRC_STM_ID = CUST.SRC_STM_ID

    LEFT OUTER JOIN (
        SELECT C.*
        FROM BMSTGCAPPS.MFACCRUAL C
        INNER JOIN (SELECT NOREK, MAX(ACCRUEDDATE) MAXACCRUEDDATE FROM BMSTGCAPPS.MFACCRUAL WHERE SOURCE = 'M' GROUP BY NOREK) TMP
        ON C.NOREK = TMP.NOREK AND C.ACCRUEDDATE = TMP.MAXACCRUEDDATE AND C.SOURCE = 'M'
    ) MFACCRUAL ON COR.NOREK = MFACCRUAL.NOREK AND COR.SRC_STM_ID = MFACCRUAL.SRC_STM_ID

    LEFT OUTER JOIN BMSTGCAPPS.MIRRMFUPLOAD MIRRMFUPLOAD ON COR.NOREK = MIRRMFUPLOAD.NOREK AND MIRRMFUPLOAD.SRC_STM_ID = COR.SRC_STM_ID
    LEFT OUTER JOIN BMSTGCAPPS.CORACCOUNTJOIN CORACCOUNTJOIN ON COR.NOREK = CORACCOUNTJOIN.NOREK AND CORACCOUNTJOIN.SRC_STM_ID = COR.SRC_STM_ID
    LEFT OUTER JOIN BMSTGCAPPS.CORJOINFINANCE CORJOINFINANCE ON CORACCOUNTJOIN.JOINFINANCEID = CORJOINFINANCE.JOINFINANCEID AND CORJOINFINANCE.SRC_STM_ID = COR.SRC_STM_ID
    LEFT OUTER JOIN BMSTGCAPPS.MIRRWRITEOFF MIRRWRITEOFF ON MIRRWRITEOFF.NOREK = COR.NOREK AND MIRRWRITEOFF.SRC_STM_ID = COR.SRC_STM_ID
    LEFT OUTER JOIN BMSTGCAPPS.V_MFACCOUNTDETAIL V_MFACCOUNTDETAIL ON V_MFACCOUNTDETAIL.NOREK = COR.NOREK AND V_MFACCOUNTDETAIL.SRC_STM_ID = COR.SRC_STM_ID
    LEFT OUTER JOIN BMSTGCAPPS.MFBALANCEEOM V_MFBALANCEEOM ON COR.NOREK=V_MFBALANCEEOM.NOREK AND COR.SRC_STM_ID=V_MFBALANCEEOM.SRC_STM_ID

    WHERE
        COR.SRC_STM_ID IN (4,14)
        --AND CORJOINFINANCE.groupid NOT IN ('EXECUTING')

    AND (
        COR.CLOSETYPE = 0
        OR (COR.CLOSETYPE !=0 AND COR.CLOSEDATE IS NOT NULL AND TO_CHAR(COR.CLOSEDATE, 'YYYYMM') = SUBSTR(COR.TIME_SK, 1, 6))
    )

    UNION

    SELECT DISTINCT TO_CHAR(cnpa.SRC_STM_ID) AS SRCID,
                    TO_CHAR(CNPA.CONTRACT_NO)   AS CONTRACTNO,
                    TO_CHAR(CUST.CUSTOMER_NAME) AS NAME,
                    TO_CHAR(CNPA.CUSTOMER_NO) AS CIFNO,
                    TO_CHAR(CNPA.TIME_SK) AS TIME_SK,
                    'T_CNP_ACCOUNT_WB' AS SourceTable

    FROM BMSTGCAPPS.T_CNP_ACCOUNT_WB CNPA
    LEFT OUTER JOIN (
    SELECT CONTRACT_NO, SUM(PRINCIPAL) PRINCIPAL, SUM(PRINCIPAL_PAID) PRINCIPAL_PAID, MAX(SUBSTR(DUE_DATE, 1, 2)) DUE_DATE FROM BMSTGCAPPS.T_CNP_ACCOUNT_DTL_WB GROUP BY CONTRACT_NO
    ) CNPADT ON CNPA.CONTRACT_NO = CNPADT.CONTRACT_NO
    LEFT OUTER JOIN BMSTGCAPPS.T_IRSRS_JF_MUF_DAILY T_IRSRS_JF_MUF_DAILY ON CONCAT(CNPA.NOREK,'001') = TRIM(T_IRSRS_JF_MUF_DAILY.ACCOUNTNO)
    LEFT OUTER JOIN BMSTGCAPPS.AR_MST_CUSTOMER CUST ON TRIM(CNPA.CUSTOMER_NO) = TRIM(CUST.CUSTOMER_ID) AND CUST.SRC_STM_ID = CNPA.SRC_STM_ID
    LEFT OUTER JOIN BMSTGCAPPS.T_IRSRS_JF_MUF T_IRSRS_JF_MUF ON TRIM(CNPA.CONTRACT_NO) = TRIM(T_IRSRS_JF_MUF.INITIALAGREEMENTNUMBER) AND TRIM(CNPA.CUSTOMER_NO) = TRIM(T_IRSRS_JF_MUF.CUSTOMERID) AND T_IRSRS_JF_MUF.SRC_STM_ID = CNPA.SRC_STM_ID
    LEFT OUTER JOIN BMSTGCAPPS.T_IRSRS_JF_MUF_F46 T_IRSRS_JF_MUF_F46 ON TRIM(CNPA.CONTRACT_NO) = TRIM(T_IRSRS_JF_MUF_F46.INITIALAGREEMENTNUMBER) AND TRIM(CNPA.CUSTOMER_NO) = TRIM(T_IRSRS_JF_MUF_F46.CUSTOMERID) AND T_IRSRS_JF_MUF_F46.SRC_STM_ID = CNPA.SRC_STM_ID
    LEFT OUTER JOIN BMSTGCAPPS.AR_AP_ORDER AR_AP_ORDER ON CNPA.CONTRACT_NO = AR_AP_ORDER.ORDER_NO AND AR_AP_ORDER.SRC_STM_ID = CNPA.SRC_STM_ID
    LEFT OUTER JOIN (
    SELECT C.*
    FROM BMSTGCAPPS.T_CNP_ACCOUNT_DTL_WB C
    INNER JOIN (SELECT CONTRACT_NO, MAX(CNPADT.DUE_DATE) MAXDUEDATE FROM BMSTGCAPPS.T_CNP_ACCOUNT_DTL_WB CNPADT GROUP BY CONTRACT_NO) TMP
    ON TRIM(C.CONTRACT_NO) = TRIM(TMP.CONTRACT_NO) AND C.DUE_DATE = TMP.MAXDUEDATE
    ) CNPADT ON TRIM(CNPA.CONTRACT_NO) = TRIM(CNPADT.CONTRACT_NO) AND CNPA.SRC_STM_ID = CNPADT.SRC_STM_ID
    WHERE
        CNPA.CLOSE_TYPE IN (0,2)
        AND T_IRSRS_JF_MUF_DAILY.NOMINALORI > 0

 ) CIF_TABLE

WHERE TIME_SK = (SELECT MAX(TIME_SK) FROM BMSTGCBSPS.CFMAST)

SELECT *