CAP-cashflow

SELECT

    :ReportingDate AS TIME_SK

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 AND CORDT.TIME_SK = :ReportingDate

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 AND CORDTMIN.TIME_SK = :ReportingDate

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

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 --AND MFACCRUAL.TIME_SK = :ReportingDate

LEFT OUTER
JOIN BMSTGCAPPS.MIRRMFUPLOAD MIRRMFUPLOAD ON COR.NOREK = MIRRMFUPLOAD.NOREK AND MIRRMFUPLOAD.SRC_STM_ID = COR.SRC_STM_ID
--AND MIRRMFUPLOAD.TIME_SK = :ReportingDate

LEFT OUTER
JOIN BMSTGCAPPS.CORACCOUNTJOIN CORACCOUNTJOIN ON COR.NOREK = CORACCOUNTJOIN.NOREK AND CORACCOUNTJOIN.SRC_STM_ID = COR.SRC_STM_ID
AND CORACCOUNTJOIN.TIME_SK = :ReportingDate

LEFT OUTER
JOIN BMSTGCAPPS.CORJOINFINANCE CORJOINFINANCE ON CORACCOUNTJOIN.JOINFINANCEID = CORJOINFINANCE.JOINFINANCEID AND CORJOINFINANCE.SRC_STM_ID = COR.SRC_STM_ID
AND CORJOINFINANCE.TIME_SK = :ReportingDate

LEFT OUTER
JOIN BMSTGCAPPS.MIRRWRITEOFF MIRRWRITEOFF ON MIRRWRITEOFF.NOREK = COR.NOREK AND MIRRWRITEOFF.SRC_STM_ID = COR.SRC_STM_ID
--AND MIRRWRITEOFF.TIME_SK = :ReportingDate

LEFT OUTER
JOIN BMSTGCAPPS.V_MFACCOUNTDETAIL V_MFACCOUNTDETAIL ON V_MFACCOUNTDETAIL.NOREK = COR.NOREK AND V_MFACCOUNTDETAIL.SRC_STM_ID = COR.SRC_STM_ID
AND V_MFACCOUNTDETAIL.TIME_SK = :ReportingDate

WHERE
    COR.SRC_STM_ID = 14 AND COR.TIME_SK = :ReportingDate
    AND COALESCE(COR.CURPOKOK,0) + COALESCE(V_MFACCOUNTDETAIL.TUNGGAKANPOKOK,0) > 0
    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))
    --COR.CLOSETYPE IN (0, 6)
    --OR (COR.CLOSETYPE = 7 AND COR.WRITEOFFDATE IS NOT NULL AND TO_CHAR(COR.WRITEOFFDATE, 'YYYYMM') = SUBSTR(COR.TIME_SK, 1, 6))
    --OR (COR.CLOSETYPE NOT IN (0,6,7) AND COR.CLOSEDATE IS NOT NULL AND TO_CHAR(COR.CLOSEDATE, 'YYYYMM') = SUBSTR(COR.TIME_SK, 1, 6))
    )