4270

WITH INV_TB AS
(SELECT INV.INVOICE_STATUS, INV.SOURCE_VOUCHER_NO,INV.INVOICE_NO,INV.AGE_DATE,INV.TOTAL_PRE_TAX_AMT,INV.TOTAL_SALES_TAX_HOME_AMT,INV.TOTAL_AFTER_TAX_HOME_AMT,INV.SUPPLIER_INVOICE_NO,INV.SUPPLIER_INVOICE_DATE,INV.INVOICE_TYPE,INV.TOTAL_CERT_AMT,INV.TOTAL_AFTER_TAX_AMT,INV.TOTAL_CERT_HOME_AMT,INV.AR_INVOICE_NO,INV.ADV_PAYMENT_ADD_ON,INV.ADV_PAYMENT_TO_REC,INV.CREDIT_NOTE_NO,CRN.AGE_DATE AS CRN_AGE_DATE,RETENTION_AMT
    FROM PJ_AP_INV_HST_HDR INV
    LEFT JOIN AP_CRN_HST_HDR CRN ON CRN.CREDIT_NOTE_NO = INV.CREDIT_NOTE_NO
    WHERE 1=1
)
,PJ_SUB_CON_HDR_REV AS
(
SELECT
SUB_CON_CONTRACT_NO,
SUB_CON_CODE,
PROJECT_NO,
SUBJECT,
CLOSED_DATETIME,
TOTAL_AMOUNT_HOME,
TOTAL_AMOUNT,
TOTAL_ORIGINAL_HOME_AMT,
TOTAL_VO_HOME_AMT,
TOTAL_VO_AMT,
OMISSION_AMT,
OMISSION_HOME_AMT,
DIFF_RET_PER_BY_LINE_ITM,
CASE WHEN CLOSED_DATETIME > CAST($P{AS_AT_DATE} AS DATE) THEN 'O'
ELSE STATUS
END AS STATUS,
CURRENCY_CODE,
TOTAL_ORIGINAL_AMT,
RETENTION_PERCENT,
MAX_RETENTION_AMOUNT
FROM PJ_SUB_CON_HDR HDR
WHERE HDR.STATUS <> 'D'
AND ((($P{CONTRACT_STATUS} = 'O' OR $P{CONTRACT_STATUS} = 'B')  AND (HDR.STATUS ='O'  OR (HDR.STATUS = 'H' AND HDR.CLOSED_DATETIME > CAST($P{AS_AT_DATE} AS DATE) AND (HDR.VARIATION_DATE IS NULL OR HDR.VARIATION_DATE <=CAST($P{AS_AT_DATE} AS DATE)) ) )) OR (($P{CONTRACT_STATUS} = 'C'  OR $P{CONTRACT_STATUS} = 'B') AND ((HDR.STATUS = 'H' AND CAST($P{AS_AT_DATE} AS DATE) IS NULL) OR (HDR.STATUS = 'H' AND HDR.CLOSED_DATETIME <=CAST($P{AS_AT_DATE} AS DATE))))
)

UNION ALL
SELECT
H.SUB_CON_CONTRACT_NO,
SUB_CON_CODE,
PROJECT_NO,
SUBJECT,
'0001-01-01' AS CLOSED_DATETIME,
TOTAL_AMOUNT_HOME,
TOTAL_AMOUNT,
TOTAL_ORIGINAL_HOME_AMT,
TOTAL_VO_HOME_AMT,
TOTAL_VO_AMT,
OMISSION_AMT,
OMISSION_HOME_AMT,
DIFF_RET_PER_BY_LINE_ITM,
'O' AS STATUS,
CURRENCY_CODE,
TOTAL_ORIGINAL_AMT,
RETENTION_PERCENT,
MAX_RETENTION_AMOUNT
FROM PJ_SUB_CON_REV_HDR H
JOIN (SELECT SUB_CON_CONTRACT_NO, MAX(REVISION_NO) REVISION_NO FROM PJ_SUB_CON_REV_HDR WHERE DATE(VARIATION_DATE)<=CAST($P{AS_AT_DATE} AS DATE) GROUP BY SUB_CON_CONTRACT_NO) AB ON H.SUB_CON_CONTRACT_NO = AB.SUB_CON_CONTRACT_NO AND H.REVISION_NO = AB.REVISION_NO
AND H.SUB_CON_CONTRACT_NO IN (SELECT SUB_CON_CONTRACT_NO FROM PJ_SUB_CON_HDR WHERE ($P{CONTRACT_STATUS} = 'O' OR $P{CONTRACT_STATUS} = 'B') AND STATUS = 'H' AND CLOSED_DATETIME > CAST($P{AS_AT_DATE} AS DATE)  AND  VARIATION_DATE > CAST($P{AS_AT_DATE} AS DATE))
WHERE 1=1 AND ($P{AS_AT_DATE_PASSED}=1)
)
SELECT H.*
FROM
(SELECT CON.SUB_CON_CONTRACT_NO,
       CON.STATUS,
       CON.SUB_CON_CODE,
       SUP.PARTY_NAME AS SUPPLIER_NAME,
       CON.PROJECT_NO,
       CON.SUBJECT,
       CON.RETENTION_PERCENT,
       CL.CLM_SEQ_NO,
       CL.SUB_CON_CLM_VOUCHER_NO,
       CASE WHEN (CLAIM_STATUS ='X'  AND  (DATE(CANCELLATION_DATE) > CAST($P{AS_AT_DATE} AS DATE))) THEN 'O' ELSE CL.CLAIM_STATUS END AS CLAIM_STATUS,
       CL.RESPONSE_DATE AS CLAIM_DATE,
       CL.PAYMENT_DUE_DATE,
       CON.CURRENCY_CODE,
       (CL.THIS_CUL_CLAIM_AMT - CL.CURRENT_CUL_CLAIM_AMT)  AS THIS_CLAIM,
       CL.THIS_CERTIFIED,
       CL.THIS_RETENTION_AMT,
       (CL.THIS_CUL_OTHER_RETENTION -  CL.CUR_CUL_OTHER_RET) AS THIS_OTHER_RET,
       CASE WHEN CRN.CREDIT_NOTE_NO IS NOT NULL AND (CL.SOURCE_TYPE <> 'O' OR CL.SOURCE_TYPE IS NULL) THEN COALESCE(INV.TOTAL_CERT_AMT,0) - COALESCE(CRN.TOTAL_CERT_AMT,0)
       WHEN CRN.CREDIT_NOTE_NO IS NOT NULL AND CL.SOURCE_TYPE = 'O' THEN COALESCE(CL.THIS_CERTIFIED,0)
       WHEN CRN.CREDIT_NOTE_NO IS NULL AND CL.SOURCE_TYPE = 'O' THEN COALESCE(CL.THIS_CERTIFIED,0)
       ELSE COALESCE(INV.TOTAL_CERT_AMT,0) END AS TOTAL_CERT_AMT,

       CASE WHEN CRN.CREDIT_NOTE_NO IS NOT NULL AND (CL.SOURCE_TYPE <> 'O' OR CL.SOURCE_TYPE IS NULL) THEN COALESCE(INV.TOTAL_CERT_HOME_AMT,0) - COALESCE(CRN.TOTAL_CERT_HOME_AMT,0)
       WHEN CRN.CREDIT_NOTE_NO IS NOT NULL AND CL.SOURCE_TYPE = 'O' THEN COALESCE(CL.THIS_CERTIFIED_HOME_AMT,0)
       WHEN CRN.CREDIT_NOTE_NO IS NULL AND CL.SOURCE_TYPE = 'O' THEN COALESCE(CL.THIS_CERTIFIED_HOME_AMT,0)
       ELSE COALESCE(INV.TOTAL_CERT_HOME_AMT,0) END TOTAL_CERT_HOME_AMT,

       CASE WHEN CRN.CREDIT_NOTE_NO IS NOT NULL THEN COALESCE(INV.TOTAL_AFTER_TAX_AMT,0) -COALESCE(CRN.TOTAL_AFTER_TAX_AMT,0) ELSE COALESCE(INV.TOTAL_AFTER_TAX_AMT,0)
       END AS TOTAL_AFTER_TAX_AMT,

       CASE WHEN CRN.CREDIT_NOTE_NO IS NOT NULL AND (CL.SOURCE_TYPE <> 'O' OR CL.SOURCE_TYPE IS NULL) THEN COALESCE(INV.RETENTION_AMT,0) - COALESCE(CRN.RETENTION_AMT,0)
       WHEN CRN.CREDIT_NOTE_NO IS NOT NULL AND CL.SOURCE_TYPE = 'O' THEN COALESCE(CL.THIS_RETENTION_AMT,0) + COALESCE(CL.THIS_OTHER_RET,0)
       WHEN CRN.CREDIT_NOTE_NO IS NULL AND CL.SOURCE_TYPE = 'O' THEN COALESCE(CL.THIS_RETENTION_AMT,0)+ COALESCE(CL.THIS_OTHER_RET,0)
       ELSE COALESCE(INV.RETENTION_AMT,0)  END AS RETENTION_AMT,

       CASE WHEN CRN.CREDIT_NOTE_NO IS NOT NULL AND (CL.SOURCE_TYPE <> 'O' OR CL.SOURCE_TYPE IS NULL) THEN COALESCE(INV.ADV_PAYMENT_ADD_ON,0) -COALESCE(INV.ADV_PAYMENT_TO_REC,0)-COALESCE(CRN.ADV_PAYMENT_ADD_ON,0) + COALESCE(CRN.ADV_PAYMENT_TO_REC,0)
       WHEN CRN.CREDIT_NOTE_NO IS NOT NULL AND CL.SOURCE_TYPE = 'O' THEN COALESCE(CL.ADV_PAYMENT_ADD_ON,0) - COALESCE(CL.ADV_PAYMENT_TO_REC,0)
       WHEN CRN.CREDIT_NOTE_NO IS NULL AND CL.SOURCE_TYPE = 'O' THEN COALESCE(CL.ADV_PAYMENT_ADD_ON,0)- COALESCE(CL.ADV_PAYMENT_TO_REC,0)
       ELSE COALESCE(INV.ADV_PAYMENT_ADD_ON,0) -COALESCE(INV.ADV_PAYMENT_TO_REC,0) END AS ADV_PAY_AMT,

       COALESCE(APPLY.APPLIED_INV_AMT,0) APPLIED_INV_AMT,
       INV.SUPPLIER_INVOICE_NO,
       INV.SUPPLIER_INVOICE_DATE,
       INV.AR_INVOICE_NO,
       (SELECT SUBSTR(CAST(xml2clob (XMLAGG (xmltext (CONCAT (', ',N_NO)))) AS VARCHAR(1024)),2) AS ALL_IN_ONE FROM
                    (
                    SELECT invoice_no,AR_REC_HST_APL.RECEIPT_VOUCHER_NO AS N_NO FROM AR_REC_HST_APL
                    UNION ALL
                    SELECT invoice_no,AR_CRN_HST_APL.CREDIT_NOTE_NO AS N_NO FROM AR_CRN_HST_APL
                    ) SUB1
        WHERE SUB1.invoice_no = INV.AR_INVOICE_NO
       ) AS BC_AP_NO,
       INV2.TOTAL_AFTER_TAX_AMT OUTSTANDING_AMT,
       PJHDR.CUSTOMER_JOB_NO,
       PJHDR.SUBJECT PROJECT_NAME,
       BC.BACK_CHARGE,
       INV.INVOICE_NO,
       (SELECT SUBSTR(CAST(xml2clob (XMLAGG (xmltext (CONCAT (', ',SUB1.APPLICATION_DATE)))) AS VARCHAR(1024)),2) AS ALL_IN_ONE FROM
                    (
                    SELECT VARCHAR_FORMAT(DATE(APPLICATION_DATE),'dd/mm/yyyy') APPLICATION_DATE,INVOICE_NO
                    FROM AP_PAY_HST_APL APL
                        LEFT JOIN AP_PYC_HST_HDR PYC ON PYC.PAYMENT_CANCELLATION_NO = APL.PAYMENT_CANCELLATION_NO
                        WHERE 1=1
                        AND DATE(APL.APPLICATION_DATE) <= CAST($P{AS_AT_DATE} AS DATE)
                        AND(APL.CANCELLED_FLAG IS NULL OR APL.CANCELLED_FLAG = 'N' OR (APL.CANCELLED_FLAG = 'Y' AND DATE(PYC.CANCELLATION_DATE) > CAST($P{AS_AT_DATE} AS DATE)))

                    ) SUB1
        WHERE SUB1.invoice_no = INV.INVOICE_NO
       ) AS PYM_DATE,
       (SELECT SUBSTR(CAST(xml2clob (XMLAGG (xmltext (CONCAT (', ',SUB1.PAYMENT_VOUCHER_NO)))) AS VARCHAR(1024)),3) AS ALL_IN_ONE FROM
                    (
                    SELECT APL.PAYMENT_VOUCHER_NO,APL.INVOICE_NO
                    FROM AP_PAY_HST_APL APL
                        LEFT JOIN AP_PYC_HST_HDR PYC ON PYC.PAYMENT_CANCELLATION_NO = APL.PAYMENT_CANCELLATION_NO
                        WHERE 1=1
                        AND DATE(APL.APPLICATION_DATE) <= CAST($P{AS_AT_DATE} AS DATE)
                        AND(APL.CANCELLED_FLAG IS NULL OR APL.CANCELLED_FLAG = 'N' OR (APL.CANCELLED_FLAG = 'Y' AND DATE(PYC.CANCELLATION_DATE) > CAST($P{AS_AT_DATE} AS DATE)))
                    ) SUB1
        WHERE SUB1.invoice_no = INV.INVOICE_NO
       ) AS PYM_NO,
       CRN.CREDIT_NOTE_NO,
       CRN.CRN_AGE_DATE
FROM PJ_SUB_CON_HDR_REV CON
  INNER JOIN MT_PARTY SUP ON CON.SUB_CON_CODE = SUP.PARTY_CODE
  LEFT JOIN PJ_EST_OST_HDR PJHDR ON PJHDR.PROJECT_NO = CON.PROJECT_NO
  LEFT OUTER JOIN
  (SELECT *
    FROM PJ_SUB_CLM_HDR
    WHERE
    ((
        ($P{AS_AT_DATE_PASSED}=1) AND
        (((CLAIM_STATUS IS NULL OR CLAIM_STATUS <> 'X' OR (CLAIM_STATUS ='X'  AND  (DATE(CANCELLATION_DATE) > CAST($P{AS_AT_DATE} AS DATE))))
        AND STATUS = 'H'
        AND (
             (RESPONSE_DATE IS NOT NULL AND DATE(RESPONSE_DATE) <= CAST( $P{AS_AT_DATE} AS DATE) )
             OR (RESPONSE_DATE IS NULL AND DATE(CLAIM_DATE) <= CAST( $P{AS_AT_DATE} AS DATE) )
            )
        )
        OR SUB_CON_CLM_VOUCHER_NO IN
                                    (
                                    SELECT SOURCE_VOUCHER_NO FROM INV_TB WHERE (DATE(AGE_DATE)<= CAST($P{AS_AT_DATE} AS DATE) AND (DATE(CRN_AGE_DATE)> CAST($P{AS_AT_DATE} AS DATE) OR CRN_AGE_DATE IS NULL)) OR (DATE(AGE_DATE)> CAST($P{AS_AT_DATE} AS DATE) AND DATE(CRN_AGE_DATE)<= CAST($P{AS_AT_DATE} AS DATE))
                                    )
        )
    )
    OR (
        ($P{AS_AT_DATE_PASSED}=0)
        AND (CLAIM_STATUS IS NULL OR CLAIM_STATUS <> 'X')
    ))
    AND (CLAIM_STATUS IS NULL OR CLAIM_STATUS <> 'A')
    ) CL ON CON.SUB_CON_CONTRACT_NO = CL.SUB_CON_CONTRACT_NO
  LEFT JOIN  INV_TB INV ON INV.SOURCE_VOUCHER_NO = CL.SUB_CON_CLM_VOUCHER_NO AND  (($P{AS_AT_DATE_PASSED}=1 AND DATE(INV.AGE_DATE)<= CAST($P{AS_AT_DATE} AS DATE))
    OR ($P{AS_AT_DATE_PASSED}=0))
    LEFT JOIN  INV_TB CRN ON CRN.SOURCE_VOUCHER_NO = CL.SUB_CON_CLM_VOUCHER_NO AND  (($P{AS_AT_DATE_PASSED}=1 AND DATE(CRN.CRN_AGE_DATE)<= CAST($P{AS_AT_DATE} AS DATE))
    OR ($P{AS_AT_DATE_PASSED}=0)) AND (INV.INVOICE_NO IS NULL OR INV.INVOICE_NO = CRN.INVOICE_NO)
    LEFT JOIN  PJ_AP_INV_OST_HDR INV2 ON INV2.SOURCE_VOUCHER_NO = CL.SUB_CON_CLM_VOUCHER_NO AND  (($P{AS_AT_DATE_PASSED}=1 AND (DATE(INV2.AGE_DATE) <= CAST($P{AS_AT_DATE} AS DATE)))
    OR ($P{AS_AT_DATE_PASSED}=0))
  LEFT JOIN
  (SELECT INVOICE_NO, SUM(APPLIED_INV_AMT) APPLIED_INV_AMT
  FROM AP_PAY_HST_APL APL
    LEFT JOIN AP_PYC_HST_HDR PYC ON PYC.PAYMENT_CANCELLATION_NO = APL.PAYMENT_CANCELLATION_NO
    WHERE 1=1
    AND DATE(APL.APPLICATION_DATE) <= CAST($P{AS_AT_DATE} AS DATE)
    AND(APL.CANCELLED_FLAG IS NULL OR APL.CANCELLED_FLAG = 'N' OR (APL.CANCELLED_FLAG = 'Y' AND DATE(PYC.CANCELLATION_DATE) > CAST($P{AS_AT_DATE} AS DATE)))
  GROUP BY INVOICE_NO
  ) APPLY ON  APPLY.INVOICE_NO = INV.INVOICE_NO
  LEFT JOIN (SELECT SUB_CON_CLM_VOUCHER_NO, SUM(TOTAL_AMT) AS BACK_CHARGE
                FROM PJ_SUB_CLM_BACK_CHARGE
                WHERE IS_APPROVED IS NULL OR IS_APPROVED = 'Y'
                GROUP BY SUB_CON_CLM_VOUCHER_NO
                    ) BC ON BC.SUB_CON_CLM_VOUCHER_NO = CL.SUB_CON_CLM_VOUCHER_NO
WHERE CON.STATUS <> 'D'
 AND (
        $P{EXCLUDE_ZERO} = 'N'
        OR ($P{EXCLUDE_ZERO} = 'Y' AND  (COALESCE(CON.TOTAL_AMOUNT,0) > 0 OR (COALESCE(CON.TOTAL_AMOUNT,0) = 0 AND CL.SUB_CON_CLM_VOUCHER_NO IS NOT NULL)))
      )
--for invoice type N
UNION ALL
SELECT CON.SUB_CON_CONTRACT_NO,
       CON.STATUS,
       CON.SUB_CON_CODE,
       SUP.PARTY_NAME AS SUPPLIER_NAME,
       CON.PROJECT_NO,
       CON.SUBJECT,
       CON.RETENTION_PERCENT,
       NULL CLM_SEQ_NO,
       INV.INVOICE_NO SUB_CON_CLM_VOUCHER_NO,
       INV.INVOICE_STATUS CLAIM_STATUS,
       INV.AGE_DATE CLAIM_DATE,
       NULL PAYMENT_DUE_DATE,
       CON.CURRENCY_CODE,
       NULL THIS_CLAIM,
       NULL THIS_CERTIFIED,
       CASE WHEN CRN.CREDIT_NOTE_NO IS NOT NULL THEN COALESCE(-1*INV.TOTAL_PRE_TAX_AMT,0) - COALESCE(-1*CRN.TOTAL_PRE_TAX_AMT,0) ELSE COALESCE(-1*INV.TOTAL_PRE_TAX_AMT,0) END AS THIS_RETENTION_AMT,
       NULL THIS_OTHER_RET,
       0 TOTAL_CERT_AMT,
       0 TOTAL_CERT_HOME_AMT,
       CASE WHEN CRN.CREDIT_NOTE_NO IS NOT NULL THEN COALESCE(INV.TOTAL_AFTER_TAX_AMT,0) -COALESCE(CRN.TOTAL_AFTER_TAX_AMT,0) ELSE COALESCE(INV.TOTAL_AFTER_TAX_AMT,0) END AS TOTAL_AFTER_TAX_AMT,
       CASE WHEN CRN.CREDIT_NOTE_NO IS NOT NULL THEN COALESCE(-1*INV.TOTAL_PRE_TAX_AMT,0) - COALESCE(-1*CRN.TOTAL_PRE_TAX_AMT,0) ELSE COALESCE(-1*INV.TOTAL_PRE_TAX_AMT,0) END  AS RETENTION_AMT,
       CASE WHEN CRN.CREDIT_NOTE_NO IS NOT NULL THEN COALESCE(INV.ADV_PAYMENT_ADD_ON,0) -COALESCE(INV.ADV_PAYMENT_TO_REC,0)-COALESCE(CRN.ADV_PAYMENT_ADD_ON,0) +COALESCE(CRN.ADV_PAYMENT_TO_REC,0) ELSE COALESCE(INV.ADV_PAYMENT_ADD_ON,0) -COALESCE(INV.ADV_PAYMENT_TO_REC,0) END AS ADV_PAY_AMT,
       COALESCE(APPLY.APPLIED_INV_AMT,0) APPLIED_INV_AMT,
       INV.SUPPLIER_INVOICE_NO,
       INV.SUPPLIER_INVOICE_DATE,
       INV.AR_INVOICE_NO,
       (SELECT SUBSTR(CAST(xml2clob (XMLAGG (xmltext (CONCAT (', ',N_NO)))) AS VARCHAR(1024)),2) AS ALL_IN_ONE FROM
                    (
                    SELECT invoice_no,AR_REC_HST_APL.RECEIPT_VOUCHER_NO AS N_NO FROM AR_REC_HST_APL
                    UNION ALL
                    SELECT invoice_no,AR_CRN_HST_APL.CREDIT_NOTE_NO AS N_NO FROM AR_CRN_HST_APL
                    ) SUB1
        WHERE SUB1.invoice_no = INV.AR_INVOICE_NO
       ) AS BC_AP_NO,
       INV2.TOTAL_AFTER_TAX_AMT OUTSTANDING_AMT,
       PJHDR.CUSTOMER_JOB_NO,
       PJHDR.SUBJECT PROJECT_NAME,
       BC.BACK_CHARGE,
       INV.INVOICE_NO,
       (SELECT SUBSTR(CAST(xml2clob (XMLAGG (xmltext (CONCAT (', ',SUB1.APPLICATION_DATE)))) AS VARCHAR(1024)),2) AS ALL_IN_ONE FROM
                    (
                    SELECT VARCHAR_FORMAT(DATE(APPLICATION_DATE),'dd/mm/yyyy') APPLICATION_DATE,INVOICE_NO
                    FROM AP_PAY_HST_APL APL
                        LEFT JOIN AP_PYC_HST_HDR PYC ON PYC.PAYMENT_CANCELLATION_NO = APL.PAYMENT_CANCELLATION_NO
                        WHERE 1=1
                        AND DATE(APL.APPLICATION_DATE) <= CAST($P{AS_AT_DATE} AS DATE)
                        AND(APL.CANCELLED_FLAG IS NULL OR APL.CANCELLED_FLAG = 'N' OR (APL.CANCELLED_FLAG = 'Y' AND DATE(PYC.CANCELLATION_DATE) > CAST($P{AS_AT_DATE} AS DATE)))
                    ) SUB1
        WHERE SUB1.invoice_no = INV.INVOICE_NO
       ) AS PYM_DATE,
       (SELECT SUBSTR(CAST(xml2clob (XMLAGG (xmltext (CONCAT (', ',SUB1.PAYMENT_VOUCHER_NO)))) AS VARCHAR(1024)),3) AS ALL_IN_ONE FROM
                    (
                    SELECT APL.PAYMENT_VOUCHER_NO,APL.INVOICE_NO
                    FROM AP_PAY_HST_APL APL
                        LEFT JOIN AP_PYC_HST_HDR PYC ON PYC.PAYMENT_CANCELLATION_NO = APL.PAYMENT_CANCELLATION_NO
                        WHERE 1=1
                        AND DATE(APL.APPLICATION_DATE) <= CAST($P{AS_AT_DATE} AS DATE)
                        AND(APL.CANCELLED_FLAG IS NULL OR APL.CANCELLED_FLAG = 'N' OR (APL.CANCELLED_FLAG = 'Y' AND DATE(PYC.CANCELLATION_DATE) > CAST($P{AS_AT_DATE} AS DATE)))
                    ) SUB1
        WHERE SUB1.invoice_no = INV.INVOICE_NO
       ) AS PYM_NO,
       CRN.CREDIT_NOTE_NO,
       CRN.CRN_AGE_DATE
FROM PJ_SUB_CON_HDR_REV CON
  INNER JOIN MT_PARTY SUP ON CON.SUB_CON_CODE = SUP.PARTY_CODE
  LEFT JOIN PJ_EST_OST_HDR PJHDR ON PJHDR.PROJECT_NO = CON.PROJECT_NO

  LEFT JOIN INV_TB INV ON INV.SOURCE_VOUCHER_NO = CON.SUB_CON_CONTRACT_NO
                                        AND INV.INVOICE_TYPE='N' AND (($P{AS_AT_DATE_PASSED}=1 AND (DATE(INV.AGE_DATE) <= CAST($P{AS_AT_DATE} AS DATE) ))
    OR ($P{AS_AT_DATE_PASSED}=0))
  LEFT JOIN INV_TB CRN ON CRN.SOURCE_VOUCHER_NO = CON.SUB_CON_CONTRACT_NO AND CRN.INVOICE_TYPE='N' AND (INV.INVOICE_NO IS NULL OR INV.INVOICE_NO = CRN.INVOICE_NO)
  LEFT JOIN  PJ_AP_INV_OST_HDR INV2 ON INV2.SOURCE_VOUCHER_NO = CON.SUB_CON_CONTRACT_NO AND  INV.INVOICE_TYPE='N'
  LEFT JOIN
  (SELECT INVOICE_NO, SUM(APPLIED_INV_AMT) APPLIED_INV_AMT
  FROM AP_PAY_HST_APL APL
    LEFT JOIN AP_PYC_HST_HDR PYC ON PYC.PAYMENT_CANCELLATION_NO = APL.PAYMENT_CANCELLATION_NO
    WHERE 1=1
    AND DATE(APL.APPLICATION_DATE) <= CAST($P{AS_AT_DATE} AS DATE)
    AND(APL.CANCELLED_FLAG IS NULL OR APL.CANCELLED_FLAG = 'N' OR (APL.CANCELLED_FLAG = 'Y' AND DATE(PYC.CANCELLATION_DATE) > CAST($P{AS_AT_DATE} AS DATE)))
  GROUP BY INVOICE_NO
  ) APPLY ON  APPLY.INVOICE_NO = INV.INVOICE_NO
  LEFT JOIN (SELECT SUB_CON_CLM_VOUCHER_NO, SUM(TOTAL_AMT) AS BACK_CHARGE
                FROM PJ_SUB_CLM_BACK_CHARGE
                WHERE IS_APPROVED IS NULL OR IS_APPROVED = 'Y'
                GROUP BY SUB_CON_CLM_VOUCHER_NO
                    ) BC ON BC.SUB_CON_CLM_VOUCHER_NO = INV.INVOICE_NO
WHERE CON.STATUS <> 'D'
AND COALESCE(INV.TOTAL_PRE_TAX_AMT,0) <> 0
AND ((
        ($P{AS_AT_DATE_PASSED}=1) AND INV.INVOICE_STATUS ='X' AND
        (
        DATE(INV.AGE_DATE) <= CAST($P{AS_AT_DATE} AS DATE) AND CRN.CRN_AGE_DATE > CAST($P{AS_AT_DATE} AS DATE)
        )
        OR
        (
        DATE(INV.AGE_DATE) > CAST($P{AS_AT_DATE} AS DATE) AND CRN.CRN_AGE_DATE <= CAST($P{AS_AT_DATE} AS DATE)
        )
    )
    OR (
        ($P{AS_AT_DATE_PASSED}=1) AND (INV.INVOICE_STATUS <>'X' OR INV.INVOICE_STATUS IS NULL) AND
        (
        DATE(INV.AGE_DATE) <= CAST($P{AS_AT_DATE} AS DATE)
        )
    )
    OR (
        ($P{AS_AT_DATE_PASSED}=0)
    ))
 AND (
        $P{EXCLUDE_ZERO} = 'N'
        OR ($P{EXCLUDE_ZERO} = 'Y' AND  (COALESCE(CON.TOTAL_AMOUNT,0) > 0 OR (COALESCE(CON.TOTAL_AMOUNT,0) = 0 AND INV.INVOICE_NO IS NOT NULL)))
      )

)H
WHERE 1=1
-- add Security Right Control by Project Team Member
-- IS_SUPERVISOR = Y with this FC OR Being a team member
AND (
    $P{IS_SUPERVISOR} = 'Y'
    OR H.PROJECT_NO IN (SELECT PROJECT_NO FROM PJ_EST_OST_TEAM_MEMBER WHERE EMPLOYEE_CODE = $P{THIS_EMP})
)
-- end Security.
$P!{__RPT__FILTER__PARAM}
$P!{__RPT__ORDERBY__PARAM}