11113

SELECT H.*
FROM
(SELECT CON.SUB_CON_CONTRACT_NO,
       CON.SUB_CON_CODE,
       SUP.PARTY_NAME AS SUPPLIER_NAME,
       CON.PROJECT_NO,
       CON.SUBJECT,
       CON.RETENTION_PERCENT,
       CON.MAX_RETENTION_PERCENT,
       COALESCE(CON.MAX_RETENTION_AMOUNT, 0) CONTRACT_MAX_RETENTION_AMOUNT,
       COALESCE(CON.TOTAL_AMOUNT_HOME, 0) CONTRACT_TOTAL_AMOUNT,
       COALESCE(CON.TOTAL_ORIGINAL_HOME_AMT, 0) CONTRACT_TOTAL_ORIGINAL_AMT,
       (COALESCE(CON.TOTAL_ORIGINAL_HOME_AMT, 0) - COALESCE(CON.OMISSION_HOME_AMT, 0) + COALESCE(CON.TOTAL_VO_HOME_AMT, 0) ) CONTRACT_ADJUSTED_AMT,
       CL.CLM_SEQ_NO,
       CL.SUB_CON_CLM_VOUCHER_NO,
       CL.RESPONSE_DATE AS CLAIM_DATE,
       CL.PAYMENT_DUE_DATE,
       (CL.THIS_CUL_CLAIM_HOME_AMT - CL.CURRENT_CUL_CLAIM_HOME_AMT) AS THIS_CLAIM,
       (CL.THIS_CUL_CERTIFIED_HOME_AMT - CL.CURRENT_CUL_CERTIFIED_HOME_AMT) AS THIS_CERTIFIED,
       (CL.THIS_CUL_OTHER_RETENTION_HOME - CL.CUR_CUL_OTHER_RET_HOME) AS THIS_OTHER_RETENTION,
       (MAX_CLM.TOTAL_CLAIM - MAX_CERF.TOTAL_CERT) AS UNCERTIFIED,
       (CON.TOTAL_AMOUNT_HOME - MAX_CLM.TOTAL_CLAIM) BALANCE_CLAIM,
       CASE WHEN FINAL_CLM.SUB_CON_CONTRACT_NO IS NULL THEN 'N' ELSE 'Y' END AS BALANCE_CLAIM_DONE,
       COALESCE(CL.THIS_RETENTION_HOME_AMT,0) THIS_RETENTION_HOME_AMT,
       COALESCE(CL.TOTAL_BACK_CHARGE_HOME_AMT,0) TOTAL_BACK_CHARGE_HOME_AMT,
       COALESCE(INV.TOTAL_PRE_TAX_HOME_AMT,0) TOTAL_BEFORE_TAX_HOME_AMT,
       COALESCE(INV.TOTAL_SALES_TAX_HOME_AMT,0) THIS_CERT_SALES_TAX_HOME_AMT,
       COALESCE(INV.TOTAL_AFTER_TAX_HOME_AMT,0) TOTAL_AFTER_TAX_HOME_AMT,
       COALESCE(CL.THIS_CUL_OTHER_RETENTION_HOME,0) THIS_CUL_OTHER_RETENTION_HOME,
       COALESCE((SELECT CLAIM_PREV.THIS_CUL_OTHER_RETENTION_HOME
        FROM PJ_SUB_CLM_HDR CLAIM_PREV
        WHERE CON.SUB_CON_CONTRACT_NO = CLAIM_PREV.SUB_CON_CONTRACT_NO
        AND ( (CLAIM_PREV.CLAIM_STATUS IS NULL OR (CLAIM_PREV.CLAIM_STATUS <> 'X' AND CLAIM_PREV.CLAIM_STATUS <> 'A'))
        AND CLAIM_PREV.STATUS = 'H') AND CLAIM_PREV.CLM_SEQ_NO < CL.CLM_SEQ_NO ORDER BY CLAIM_PREV.CLM_SEQ_NO DESC FETCH FIRST 1 ROW ONLY
        ), 0) PREV_OTHER_RETENTION_HOME,
       INV.SUPPLIER_INVOICE_NO,
       INV.SUPPLIER_INVOICE_DATE,
       OSTAP.OUTSTANDING_HOME_AMT,
       OSTAP.INVOICE_NO,
       APPLY.PAYMENT_VOUCHER_NO_AND_APPLICATION_DATE,
       COALESCE((SELECT PROPERTY_VALUE FROM MODULE_CONFIG WHERE MODULE_CODE='PJ' AND PROPERTY_NAME='ENABLETORELEASERETENTIONFROMSUBCONCLAIM'), 'N') ENABLE_OTHER_RETENTION
FROM PJ_SUB_CON_HDR 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 (SELECT MAX(T.THIS_CUL_CERTIFIED_HOME_AMT) AS TOTAL_CERT,
                    T.SUB_CON_CONTRACT_NO
             FROM PJ_SUB_CLM_HDR T
                LEFT JOIN PJ_AP_INV_HST_HDR INV ON INV.SOURCE_VOUCHER_NO = T.SUB_CON_CLM_VOUCHER_NO
                WHERE (
                    (
                    ((T.CLAIM_STATUS IS NULL OR (T.CLAIM_STATUS <> 'X' AND T.CLAIM_STATUS <> 'A')) AND (T.RESPONSE_DATE <= $P{AS_AT_DATE}))
                    OR (T.CLAIM_STATUS = 'X' AND (T.RESPONSE_DATE <= $P{AS_AT_DATE}) AND (T.CANCELLATION_DATE > $P{AS_AT_DATE}))
                    ) AND $P{AS_AT_DATE_PASSED} = 1 OR $P{AS_AT_DATE_PASSED} = 0
                        )
                AND ((INV.INVOICE_STATUS <>'X' OR INV.INVOICE_STATUS IS NULL OR (INV.INVOICE_STATUS ='X' AND $P{INV_POSTING_DATE_PASSED} = 1 AND $P{INV_POSTING_DATE} < (SELECT CRN.AGE_DATE FROM AP_CRN_HST_HDR CRN WHERE CRN.CREDIT_NOTE_NO = INV.CREDIT_NOTE_NO FETCH FIRST 1 ROW ONLY))) OR ($P{INV_POSTING_DATE_PASSED} = 0))
                AND   (($P{INV_POSTING_DATE_PASSED} = 1 AND (INV.AGE_DATE <= $P{INV_POSTING_DATE})) OR ($P{INV_POSTING_DATE_PASSED} = 0))
                AND   T.STATUS = 'H' AND ($P{INV_POSTING_DATE_PASSED} = 1 OR $P{AS_AT_DATE_PASSED} = 1 OR (T.CLAIM_STATUS IS NULL AND $P{INV_POSTING_DATE_PASSED} = 0 AND $P{AS_AT_DATE_PASSED} = 0 ))
             GROUP BY T.SUB_CON_CONTRACT_NO) MAX_CERF ON MAX_CERF.SUB_CON_CONTRACT_NO = CON.SUB_CON_CONTRACT_NO
  LEFT JOIN (SELECT MAX(T.THIS_CUL_CLAIM_HOME_AMT) AS TOTAL_CLAIM,
                    T.SUB_CON_CONTRACT_NO
             FROM PJ_SUB_CLM_HDR T
                LEFT JOIN PJ_AP_INV_HST_HDR INV ON INV.SOURCE_VOUCHER_NO = T.SUB_CON_CLM_VOUCHER_NO
                WHERE (
                    (
                    ((T.CLAIM_STATUS IS NULL OR (T.CLAIM_STATUS <> 'X' AND T.CLAIM_STATUS <> 'A')) AND (T.RESPONSE_DATE <= $P{AS_AT_DATE}))
                    OR (T.CLAIM_STATUS = 'X' AND (T.RESPONSE_DATE <= $P{AS_AT_DATE}) AND (T.CANCELLATION_DATE > $P{AS_AT_DATE}))
                    ) AND $P{AS_AT_DATE_PASSED} = 1 OR $P{AS_AT_DATE_PASSED} = 0
                        )
                AND ((INV.INVOICE_STATUS <>'X' OR INV.INVOICE_STATUS IS NULL OR (INV.INVOICE_STATUS ='X' AND $P{INV_POSTING_DATE_PASSED} = 1 AND $P{INV_POSTING_DATE} < (SELECT CRN.AGE_DATE FROM AP_CRN_HST_HDR CRN WHERE CRN.CREDIT_NOTE_NO = INV.CREDIT_NOTE_NO FETCH FIRST 1 ROW ONLY))) OR ($P{INV_POSTING_DATE_PASSED} = 0))
                AND   (($P{INV_POSTING_DATE_PASSED} = 1 AND (INV.AGE_DATE <= $P{INV_POSTING_DATE})) OR ($P{INV_POSTING_DATE_PASSED} = 0))
                AND   T.STATUS = 'H' AND ($P{INV_POSTING_DATE_PASSED} = 1 OR $P{AS_AT_DATE_PASSED} = 1 OR (T.CLAIM_STATUS IS NULL AND $P{INV_POSTING_DATE_PASSED} = 0 AND $P{AS_AT_DATE_PASSED} = 0 ))
             GROUP BY T.SUB_CON_CONTRACT_NO) MAX_CLM ON MAX_CLM.SUB_CON_CONTRACT_NO = CON.SUB_CON_CONTRACT_NO
  LEFT JOIN (SELECT DISTINCT T.SUB_CON_CONTRACT_NO
             FROM PJ_SUB_CLM_HDR T
             LEFT JOIN PJ_AP_INV_HST_HDR INV ON INV.SOURCE_VOUCHER_NO = T.SUB_CON_CLM_VOUCHER_NO
                WHERE (
                    (
                    ((T.CLAIM_STATUS IS NULL OR (T.CLAIM_STATUS <> 'X' AND T.CLAIM_STATUS <> 'A')) AND (T.RESPONSE_DATE <= $P{AS_AT_DATE}))
                    OR (T.CLAIM_STATUS = 'X' AND (T.RESPONSE_DATE <= $P{AS_AT_DATE}) AND (T.CANCELLATION_DATE > $P{AS_AT_DATE}))
                    ) AND $P{AS_AT_DATE_PASSED} = 1 OR $P{AS_AT_DATE_PASSED} = 0
                        )
                AND ((INV.INVOICE_STATUS <>'X' OR INV.INVOICE_STATUS IS NULL OR (INV.INVOICE_STATUS ='X' AND $P{INV_POSTING_DATE_PASSED} = 1 AND $P{INV_POSTING_DATE} < (SELECT CRN.AGE_DATE FROM AP_CRN_HST_HDR CRN WHERE CRN.CREDIT_NOTE_NO = INV.CREDIT_NOTE_NO FETCH FIRST 1 ROW ONLY))) OR ($P{INV_POSTING_DATE_PASSED} = 0))
                AND   (($P{INV_POSTING_DATE_PASSED} = 1 AND (INV.AGE_DATE <= $P{INV_POSTING_DATE})) OR ($P{INV_POSTING_DATE_PASSED} = 0))
                AND   T.STATUS = 'H' AND ($P{INV_POSTING_DATE_PASSED} = 1 OR $P{AS_AT_DATE_PASSED} = 1 OR (T.CLAIM_STATUS IS NULL AND $P{INV_POSTING_DATE_PASSED} = 0 AND $P{AS_AT_DATE_PASSED} = 0 ))
             AND T.FINAL_CLAIM = 'Y') FINAL_CLM ON FINAL_CLM.SUB_CON_CONTRACT_NO = CON.SUB_CON_CONTRACT_NO
 LEFT JOIN (SELECT T.* FROM PJ_SUB_CLM_HDR T
                LEFT JOIN PJ_AP_INV_HST_HDR INV ON INV.SOURCE_VOUCHER_NO = T.SUB_CON_CLM_VOUCHER_NO
                WHERE (
                    (
                    ((T.CLAIM_STATUS IS NULL OR (T.CLAIM_STATUS <> 'X' AND T.CLAIM_STATUS <> 'A')) AND (T.RESPONSE_DATE <= $P{AS_AT_DATE}))
                    OR (T.CLAIM_STATUS = 'X' AND (T.RESPONSE_DATE <= $P{AS_AT_DATE}) AND (T.CANCELLATION_DATE > $P{AS_AT_DATE}))
                    ) AND $P{AS_AT_DATE_PASSED} = 1 OR $P{AS_AT_DATE_PASSED} = 0
                        )
                AND ((INV.INVOICE_STATUS <>'X' OR INV.INVOICE_STATUS IS NULL OR (INV.INVOICE_STATUS ='X' AND $P{INV_POSTING_DATE_PASSED} = 1 AND $P{INV_POSTING_DATE} < (SELECT CRN.AGE_DATE FROM AP_CRN_HST_HDR CRN WHERE CRN.CREDIT_NOTE_NO = INV.CREDIT_NOTE_NO FETCH FIRST 1 ROW ONLY))) OR ($P{INV_POSTING_DATE_PASSED} = 0))
                AND   (($P{INV_POSTING_DATE_PASSED} = 1 AND (INV.AGE_DATE <= $P{INV_POSTING_DATE})) OR ($P{INV_POSTING_DATE_PASSED} = 0))
                AND   T.STATUS = 'H' AND ($P{INV_POSTING_DATE_PASSED} = 1 OR $P{AS_AT_DATE_PASSED} = 1 OR (T.CLAIM_STATUS IS NULL AND $P{INV_POSTING_DATE_PASSED} = 0 AND $P{AS_AT_DATE_PASSED} = 0 ))
                ) CL ON CON.SUB_CON_CONTRACT_NO = CL.SUB_CON_CONTRACT_NO
  LEFT JOIN (SELECT INVOICE_STATUS, SOURCE_VOUCHER_NO,INVOICE_NO,AGE_DATE,TOTAL_PRE_TAX_HOME_AMT,TOTAL_SALES_TAX_HOME_AMT,TOTAL_AFTER_TAX_HOME_AMT,SUPPLIER_INVOICE_NO,SUPPLIER_INVOICE_DATE
                FROM PJ_AP_INV_HST_HDR HDR
                WHERE ((HDR.INVOICE_STATUS <>'X' OR HDR.INVOICE_STATUS IS NULL OR (HDR.INVOICE_STATUS ='X' AND $P{INV_POSTING_DATE_PASSED} = 1 AND $P{INV_POSTING_DATE} < (SELECT CRN.AGE_DATE FROM AP_CRN_HST_HDR CRN WHERE CRN.CREDIT_NOTE_NO = HDR.CREDIT_NOTE_NO FETCH FIRST 1 ROW ONLY))) OR ($P{INV_POSTING_DATE_PASSED} = 0)) AND ((HDR.AGE_DATE <= $P{AS_AT_DATE} AND $P{AS_AT_DATE_PASSED} = 1 ) OR $P{AS_AT_DATE_PASSED} = 0)
             UNION ALL
             SELECT
                INVOICE_STATUS,SOURCE_VOUCHER_NO,INVOICE_NO,AGE_DATE,TOTAL_PRE_TAX_HOME_AMT,TOTAL_SALES_TAX_HOME_AMT,TOTAL_AFTER_TAX_HOME_AMT,SUPPLIER_INVOICE_NO,SUPPLIER_INVOICE_DATE
                FROM PJ_AP_INV_OST_HDR
            ) INV ON INV.SOURCE_VOUCHER_NO = CL.SUB_CON_CLM_VOUCHER_NO
  LEFT JOIN AP_INV_OST_HDR OSTAP ON INV.INVOICE_NO = OSTAP.INVOICE_NO
  LEFT JOIN
    (
        SELECT
        APL.INVOICE_NO,
        REPLACE(REPLACE(XMLSERIALIZE(CONTENT XMLAGG(XMLTEXT(CONCAT(REPLACE(REPLACE(CONCAT(CONCAT(APL.PAYMENT_VOUCHER_NO, '@#$'), TO_CHAR(APL.APPLICATION_DATE, 'dd/MM/YYYY')),'&','{}!'),'<','{}@'), '@#$@#$'))) AS VARCHAR(2000)), '{}@', '<'), '{}!', '&') AS PAYMENT_VOUCHER_NO_AND_APPLICATION_DATE
        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
        (
        (
        $P{AS_AT_DATE_PASSED} = 1 AND $P{INV_POSTING_DATE_PASSED} = 0 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)))
        )
        OR
        (
        $P{AS_AT_DATE_PASSED} = 0 AND $P{INV_POSTING_DATE_PASSED} = 0 AND (APL.CANCELLED_FLAG IS NULL OR APL.CANCELLED_FLAG = 'N')
        )
        OR
        (
        $P{AS_AT_DATE_PASSED} = 0 AND $P{INV_POSTING_DATE_PASSED} = 1 AND DATE(APL.APPLICATION_DATE) <= CAST($P{INV_POSTING_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{INV_POSTING_DATE} AS DATE)))
        )
        )
        GROUP BY APL.INVOICE_NO
    )
  APPLY ON APPLY.INVOICE_NO = INV.INVOICE_NO
WHERE CON.STATUS <> 'D'
AND   (($P{INV_POSTING_DATE_PASSED} = 1 AND (INV.AGE_DATE <= $P{INV_POSTING_DATE})) OR ($P{INV_POSTING_DATE_PASSED} = 0))
AND   (($P{CONTRACT_VOUCHER_DATE_PASSED} = 0) OR ($P{CONTRACT_VOUCHER_DATE_PASSED} = 1 AND CON.CONTRACT_VOUCHER_DATE >= $P{CONTRACT_VOUCHER_DATE_FROM}) OR ($P{CONTRACT_VOUCHER_DATE_PASSED} = 2 AND CON.CONTRACT_VOUCHER_DATE <= $P{CONTRACT_VOUCHER_DATE_TO}) OR ($P{CONTRACT_VOUCHER_DATE_PASSED} = 3 AND CON.CONTRACT_VOUCHER_DATE BETWEEN $P{CONTRACT_VOUCHER_DATE_FROM} AND $P{CONTRACT_VOUCHER_DATE_TO})) AND (($P{SUBCON_PASSED} = 0) OR ($P{SUBCON_PASSED} = 1 AND CON.SUB_CON_CODE >= $P{SUBCON_FROM}) OR ($P{SUBCON_PASSED} = 2 AND CON.SUB_CON_CODE <= $P{SUBCON_TO}) OR ($P{SUBCON_PASSED} = 3 AND CON.SUB_CON_CODE >= $P{SUBCON_FROM} AND CON.SUB_CON_CODE <= $P{SUBCON_TO}))
AND   (($P{CONTRACT_NO_PASSED} = 0) OR ($P{CONTRACT_NO_PASSED} = 1 AND CON.SUB_CON_CONTRACT_NO >= $P{CONTRACT_NO_FROM}) OR ($P{CONTRACT_NO_PASSED} = 2 AND CON.SUB_CON_CONTRACT_NO <= $P{CONTRACT_NO_TO}) OR ($P{CONTRACT_NO_PASSED} = 3 AND CON.SUB_CON_CONTRACT_NO >= $P{CONTRACT_NO_FROM} AND CON.SUB_CON_CONTRACT_NO <= $P{CONTRACT_NO_TO}))
AND   (($P{PROJECT_NO_PASSED} = 0) OR ($P{PROJECT_NO_PASSED} = 1 AND CON.PROJECT_NO >= $P{PROJECT_NO_FROM}) OR ($P{PROJECT_NO_PASSED} = 2 AND CON.PROJECT_NO <= $P{PROJECT_NO_TO}) OR ($P{PROJECT_NO_PASSED} = 3 AND CON.PROJECT_NO >= $P{PROJECT_NO_FROM} AND CON.PROJECT_NO <= $P{PROJECT_NO_TO}))
AND   (($P{CUSTOMER_JOB_NO_PASSED} = 0) OR ($P{CUSTOMER_JOB_NO_PASSED} = 1 AND PJHDR.CUSTOMER_JOB_NO >= $P{CUSTOMER_JOB_NO_FROM}) OR ($P{CUSTOMER_JOB_NO_PASSED} = 2 AND PJHDR.CUSTOMER_JOB_NO <= $P{CUSTOMER_JOB_NO_TO}) OR ($P{CUSTOMER_JOB_NO_PASSED} = 3 AND PJHDR.CUSTOMER_JOB_NO >= $P{CUSTOMER_JOB_NO_FROM} AND PJHDR.CUSTOMER_JOB_NO <= $P{CUSTOMER_JOB_NO_TO}))
AND (($P{AS_AT_DATE_PASSED}=1 AND (CL.RESPONSE_DATE <= $P{AS_AT_DATE}))
    OR ($P{AS_AT_DATE_PASSED}=0))
 AND (
        $P{EXCLUDE_ZERO} = 'N'
        OR ($P{EXCLUDE_ZERO} = 'Y' AND  COALESCE(CON.TOTAL_AMOUNT_HOME,0)>0 AND CL.SUB_CON_CLM_VOUCHER_NO IS NOT NULL)
      )
UNION ALL
SELECT CON.SUB_CON_CONTRACT_NO,
       CON.SUB_CON_CODE,
       SUP.PARTY_NAME AS SUPPLIER_NAME,
       CON.PROJECT_NO,
       CON.SUBJECT,
       CON.RETENTION_PERCENT,
       CON.MAX_RETENTION_PERCENT,
       COALESCE(CON.MAX_RETENTION_AMOUNT, 0) CONTRACT_MAX_RETENTION_AMOUNT,
       COALESCE(CON.TOTAL_AMOUNT_HOME, 0) CONTRACT_TOTAL_AMOUNT,
       COALESCE(CON.TOTAL_ORIGINAL_HOME_AMT, 0) CONTRACT_TOTAL_ORIGINAL_AMT,
       (COALESCE(CON.TOTAL_ORIGINAL_HOME_AMT, 0) - COALESCE(CON.OMISSION_HOME_AMT, 0) + COALESCE(CON.TOTAL_VO_HOME_AMT, 0) ) CONTRACT_ADJUSTED_AMT,
       NULL CLM_SEQ_NO,
       INV_R.INVOICE_NO SUB_CON_CLM_VOUCHER_NO,
       INV_R.AGE_DATE CLAIM_DATE,
       NULL PAYMENT_DUE_DATE,
       NULL THIS_CLAIM,
       NULL THIS_CERTIFIED,
       NULL THIS_OTHER_RETENTION,
       (MAX_CLM.TOTAL_CLAIM - MAX_CERF.TOTAL_CERT) AS UNCERTIFIED,
       (CON.TOTAL_AMOUNT_HOME - MAX_CLM.TOTAL_CLAIM) BALANCE_CLAIM,
       CASE WHEN FINAL_CLM.SUB_CON_CONTRACT_NO IS NULL THEN 'N' ELSE 'Y' END AS BALANCE_CLAIM_DONE,
       COALESCE(-1*INV_R.TOTAL_PRE_TAX_HOME_AMT ,0) THIS_RETENTION_HOME_AMT,
       NULL TOTAL_BACK_CHARGE_HOME_AMT,
       COALESCE(INV_R.TOTAL_PRE_TAX_HOME_AMT,0) TOTAL_BEFORE_TAX_HOME_AMT,
       COALESCE(INV_R.TOTAL_SALES_TAX_HOME_AMT,0) THIS_CERT_SALES_TAX_HOME_AMT,
       COALESCE(INV_R.TOTAL_AFTER_TAX_HOME_AMT,0) TOTAL_AFTER_TAX_HOME_AMT,
       NULL THIS_CUL_OTHER_RETENTION_HOME,
       NULL  PREV_OTHER_RETENTION_HOME,
       INV_R.SUPPLIER_INVOICE_NO,
       INV_R.SUPPLIER_INVOICE_DATE,
       OSTAP.OUTSTANDING_HOME_AMT,
       OSTAP.INVOICE_NO,
       APPLY.PAYMENT_VOUCHER_NO_AND_APPLICATION_DATE,
       COALESCE((SELECT PROPERTY_VALUE FROM MODULE_CONFIG WHERE MODULE_CODE='PJ' AND PROPERTY_NAME='ENABLETORELEASERETENTIONFROMSUBCONCLAIM'), 'N') ENABLE_OTHER_RETENTION
FROM PJ_SUB_CON_HDR 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 (SELECT MAX(T.THIS_CUL_CERTIFIED_HOME_AMT) AS TOTAL_CERT,
                    T.SUB_CON_CONTRACT_NO
             FROM PJ_SUB_CLM_HDR T
                WHERE (
                    (
                    ((T.CLAIM_STATUS IS NULL OR (T.CLAIM_STATUS <> 'X' AND T.CLAIM_STATUS <> 'A')) AND (T.RESPONSE_DATE <= $P{AS_AT_DATE}))
                    OR (T.CLAIM_STATUS = 'X' AND (T.RESPONSE_DATE <= $P{AS_AT_DATE}) AND (T.CANCELLATION_DATE > $P{AS_AT_DATE}))
                    ) AND $P{AS_AT_DATE_PASSED} = 1 OR $P{AS_AT_DATE_PASSED} = 0
                        )
                AND   T.STATUS = 'H' AND ($P{INV_POSTING_DATE_PASSED} = 1 OR $P{AS_AT_DATE_PASSED} = 1 OR (T.CLAIM_STATUS IS NULL AND $P{INV_POSTING_DATE_PASSED} = 0 AND $P{AS_AT_DATE_PASSED} = 0 ))
             GROUP BY T.SUB_CON_CONTRACT_NO) MAX_CERF ON MAX_CERF.SUB_CON_CONTRACT_NO = CON.SUB_CON_CONTRACT_NO
  LEFT JOIN (SELECT MAX(T.THIS_CUL_CLAIM_HOME_AMT) AS TOTAL_CLAIM,
                    T.SUB_CON_CONTRACT_NO
             FROM PJ_SUB_CLM_HDR T
                WHERE (
                    (
                    ((T.CLAIM_STATUS IS NULL OR (T.CLAIM_STATUS <> 'X' AND T.CLAIM_STATUS <> 'A')) AND (T.RESPONSE_DATE <= $P{AS_AT_DATE}))
                    OR (T.CLAIM_STATUS = 'X' AND (T.RESPONSE_DATE <= $P{AS_AT_DATE}) AND (T.CANCELLATION_DATE > $P{AS_AT_DATE}))
                    ) AND $P{AS_AT_DATE_PASSED} = 1 OR $P{AS_AT_DATE_PASSED} = 0
                        )
                AND   T.STATUS = 'H' AND ($P{INV_POSTING_DATE_PASSED} = 1 OR $P{AS_AT_DATE_PASSED} = 1 OR (T.CLAIM_STATUS IS NULL AND $P{INV_POSTING_DATE_PASSED} = 0 AND $P{AS_AT_DATE_PASSED} = 0 ))
             GROUP BY T.SUB_CON_CONTRACT_NO) MAX_CLM ON MAX_CLM.SUB_CON_CONTRACT_NO = CON.SUB_CON_CONTRACT_NO
  LEFT JOIN (SELECT DISTINCT T.SUB_CON_CONTRACT_NO
             FROM PJ_SUB_CLM_HDR T
                WHERE (
                    (
                    ((T.CLAIM_STATUS IS NULL OR (T.CLAIM_STATUS <> 'X' AND T.CLAIM_STATUS <> 'A')) AND (T.RESPONSE_DATE <= $P{AS_AT_DATE}))
                    OR (T.CLAIM_STATUS = 'X' AND (T.RESPONSE_DATE <= $P{AS_AT_DATE}) AND (T.CANCELLATION_DATE > $P{AS_AT_DATE}))
                    ) AND $P{AS_AT_DATE_PASSED} = 1 OR $P{AS_AT_DATE_PASSED} = 0
                        )
                AND   T.STATUS = 'H' AND ($P{INV_POSTING_DATE_PASSED} = 1 OR $P{AS_AT_DATE_PASSED} = 1 OR (T.CLAIM_STATUS IS NULL AND $P{INV_POSTING_DATE_PASSED} = 0 AND $P{AS_AT_DATE_PASSED} = 0 ))
             AND T.FINAL_CLAIM = 'Y') FINAL_CLM ON FINAL_CLM.SUB_CON_CONTRACT_NO = CON.SUB_CON_CONTRACT_NO
  LEFT JOIN (SELECT INVOICE_STATUS,SOURCE_VOUCHER_NO,INVOICE_NO,AGE_DATE,TOTAL_PRE_TAX_HOME_AMT,TOTAL_SALES_TAX_HOME_AMT,TOTAL_AFTER_TAX_HOME_AMT,SUPPLIER_INVOICE_NO,SUPPLIER_INVOICE_DATE,INVOICE_TYPE
                FROM PJ_AP_INV_HST_HDR HDR
                WHERE (($P{INV_POSTING_DATE_PASSED} = 1 AND (HDR.INVOICE_STATUS <>'X' OR HDR.INVOICE_STATUS IS NULL OR (HDR.INVOICE_STATUS ='X' AND $P{INV_POSTING_DATE} < (SELECT CRN.AGE_DATE FROM AP_CRN_HST_HDR CRN WHERE CRN.CREDIT_NOTE_NO = HDR.CREDIT_NOTE_NO FETCH FIRST 1 ROW ONLY))) )
                OR (($P{INV_POSTING_DATE_PASSED} = 0) AND (HDR.INVOICE_STATUS <>'X' OR HDR.INVOICE_STATUS IS NULL))) AND ((HDR.AGE_DATE <= $P{AS_AT_DATE} AND $P{AS_AT_DATE_PASSED} = 1 ) OR $P{AS_AT_DATE_PASSED} = 0)
             UNION ALL
             SELECT
                INVOICE_STATUS,SOURCE_VOUCHER_NO,INVOICE_NO,AGE_DATE,TOTAL_PRE_TAX_HOME_AMT,TOTAL_SALES_TAX_HOME_AMT,TOTAL_AFTER_TAX_HOME_AMT,SUPPLIER_INVOICE_NO,SUPPLIER_INVOICE_DATE,INVOICE_TYPE
                FROM PJ_AP_INV_OST_HDR
            ) INV_R ON INV_R.SOURCE_VOUCHER_NO = CON.SUB_CON_CONTRACT_NO
                                        AND INV_R.INVOICE_TYPE='N'

  LEFT JOIN AP_INV_OST_HDR OSTAP ON INV_R.INVOICE_NO = OSTAP.INVOICE_NO
  LEFT JOIN
    (
        SELECT
        APL.INVOICE_NO,
        REPLACE(REPLACE(XMLSERIALIZE(CONTENT XMLAGG(XMLTEXT(CONCAT(REPLACE(REPLACE(CONCAT(CONCAT(APL.PAYMENT_VOUCHER_NO, '@#$'), TO_CHAR(APL.APPLICATION_DATE, 'dd/MM/YYYY')),'&','{}!'),'<','{}@'), '@#$@#$'))) AS VARCHAR(2000)), '{}@', '<'), '{}!', '&') AS PAYMENT_VOUCHER_NO_AND_APPLICATION_DATE
        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
        (
        (
        $P{AS_AT_DATE_PASSED} = 1 AND $P{INV_POSTING_DATE_PASSED} = 0 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)))
        )
        OR
        (
        $P{AS_AT_DATE_PASSED} = 0 AND $P{INV_POSTING_DATE_PASSED} = 0 AND (APL.CANCELLED_FLAG IS NULL OR APL.CANCELLED_FLAG = 'N')
        )
        OR
        (
        $P{AS_AT_DATE_PASSED} = 0 AND $P{INV_POSTING_DATE_PASSED} = 1 AND DATE(APL.APPLICATION_DATE) <= CAST($P{INV_POSTING_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{INV_POSTING_DATE} AS DATE)))
        )
        )
        GROUP BY APL.INVOICE_NO
    )
  APPLY ON APPLY.INVOICE_NO = INV_R.INVOICE_NO
WHERE CON.STATUS <> 'D'
AND   (($P{INV_POSTING_DATE_PASSED} = 1 AND (INV_R.AGE_DATE <= $P{INV_POSTING_DATE})) OR ($P{INV_POSTING_DATE_PASSED} = 0))
AND   (($P{CONTRACT_VOUCHER_DATE_PASSED} = 0) OR ($P{CONTRACT_VOUCHER_DATE_PASSED} = 1 AND CON.CONTRACT_VOUCHER_DATE >= $P{CONTRACT_VOUCHER_DATE_FROM}) OR ($P{CONTRACT_VOUCHER_DATE_PASSED} = 2 AND CON.CONTRACT_VOUCHER_DATE <= $P{CONTRACT_VOUCHER_DATE_TO}) OR ($P{CONTRACT_VOUCHER_DATE_PASSED} = 3 AND CON.CONTRACT_VOUCHER_DATE BETWEEN $P{CONTRACT_VOUCHER_DATE_FROM} AND $P{CONTRACT_VOUCHER_DATE_TO})) AND (($P{SUBCON_PASSED} = 0) OR ($P{SUBCON_PASSED} = 1 AND CON.SUB_CON_CODE >= $P{SUBCON_FROM}) OR ($P{SUBCON_PASSED} = 2 AND CON.SUB_CON_CODE <= $P{SUBCON_TO}) OR ($P{SUBCON_PASSED} = 3 AND CON.SUB_CON_CODE >= $P{SUBCON_FROM} AND CON.SUB_CON_CODE <= $P{SUBCON_TO}))
AND   (($P{CONTRACT_NO_PASSED} = 0) OR ($P{CONTRACT_NO_PASSED} = 1 AND CON.SUB_CON_CONTRACT_NO >= $P{CONTRACT_NO_FROM}) OR ($P{CONTRACT_NO_PASSED} = 2 AND CON.SUB_CON_CONTRACT_NO <= $P{CONTRACT_NO_TO}) OR ($P{CONTRACT_NO_PASSED} = 3 AND CON.SUB_CON_CONTRACT_NO >= $P{CONTRACT_NO_FROM} AND CON.SUB_CON_CONTRACT_NO <= $P{CONTRACT_NO_TO}))
AND   (($P{PROJECT_NO_PASSED} = 0) OR ($P{PROJECT_NO_PASSED} = 1 AND CON.PROJECT_NO >= $P{PROJECT_NO_FROM}) OR ($P{PROJECT_NO_PASSED} = 2 AND CON.PROJECT_NO <= $P{PROJECT_NO_TO}) OR ($P{PROJECT_NO_PASSED} = 3 AND CON.PROJECT_NO >= $P{PROJECT_NO_FROM} AND CON.PROJECT_NO <= $P{PROJECT_NO_TO}))
AND   (($P{CUSTOMER_JOB_NO_PASSED} = 0) OR ($P{CUSTOMER_JOB_NO_PASSED} = 1 AND PJHDR.CUSTOMER_JOB_NO >= $P{CUSTOMER_JOB_NO_FROM}) OR ($P{CUSTOMER_JOB_NO_PASSED} = 2 AND PJHDR.CUSTOMER_JOB_NO <= $P{CUSTOMER_JOB_NO_TO}) OR ($P{CUSTOMER_JOB_NO_PASSED} = 3 AND PJHDR.CUSTOMER_JOB_NO >= $P{CUSTOMER_JOB_NO_FROM} AND PJHDR.CUSTOMER_JOB_NO <= $P{CUSTOMER_JOB_NO_TO}))
AND COALESCE(INV_R.TOTAL_PRE_TAX_HOME_AMT,0) <> 0
AND (($P{AS_AT_DATE_PASSED}=1 AND (INV_R.AGE_DATE <= $P{AS_AT_DATE}))
    OR ($P{AS_AT_DATE_PASSED}=0))
AND   1 = 1
)H
ORDER BY SUPPLIER_NAME,
         SUB_CON_CONTRACT_NO,
         PROJECT_NO,
         CLM_SEQ_NO,
         CLAIM_DATE,
         PAYMENT_DUE_DATE,
         INVOICE_NO