3.1 subcon

SELECT
APINV.INVOICE_NO,
APINV.AGE_DATE ,
SCHDR.SUB_CON_CODE,
MP.PARTY_NAME SUBCON_NAME,
SUBHDR.CLAIM_STATUS,
SUBHDR.CLM_SEQ_NO,
APINV.CURRENCY_CODE,
SUM(SUBCBC.TOTAL_AMT) AMT,
SUM(SUBCBC.TOTAL_HOME_AMT) HOME_AMT
FROM PJ_MAINCONAPIN_ALLOC_DET MAD
JOIN PJ_SUB_CON_BACK_CHARGE SUBBC ON MAD.SUB_CON_CONTRACT_NO = SUBBC.SUB_CON_CONTRACT_NO AND MAD.SUB_CON_BACKCHARGE_SEQ_NO = SUBBC.SUB_SEQ_NO
JOIN PJ_SUB_CLM_BACK_CHARGE SUBCBC ON SUBBC.SUB_CON_CONTRACT_NO = SUBCBC.SUBCON_CONTRACT_NO AND SUBBC.SUB_SEQ_NO = SUBCBC.SOURCE_SEQ_NO
JOIN PJ_SUB_CLM_HDR SUBHDR ON SUBHDR.SUB_CON_CLM_VOUCHER_NO = SUBCBC.SUB_CON_CLM_VOUCHER_NO
LEFT JOIN PJ_AP_INV_HST_HDR APINV ON APINV.CLAIM_VOUCHER_NO = SUBCBC.SUB_CON_CLM_VOUCHER_NO
JOIN PJ_SUB_CON_HDR SCHDR ON SCHDR.SUB_CON_CONTRACT_NO = SUBHDR.SUB_CON_CONTRACT_NO
JOIN PJ_MAINCONAPIN_ALLOC A ON A.INVOICE_NO = MAD.INVOICE_NO AND A.PK_NO_ALLOC = MAD.PK_NO_ALLOC
LEFT JOIN MT_FINANCIAL_PERIOD FP ON A.ALLOCATION_DATE BETWEEN FP.PERIOD_START_DATE AND FP.PERIOD_CLOSING_DATE
LEFT JOIN MT_PARTY MP ON MP.PARTY_CODE = SCHDR.SUB_CON_CODE
WHERE ((SUBHDR.CLAIM_STATUS IS NULL AND SUBHDR.STATUS = 'H') OR (SUBHDR.CLAIM_STATUS IS NOT NULL AND SUBHDR.CLAIM_STATUS <> 'X'))
AND MAD.ALLOC_TYPE = 'S'
AND FP.FINANCIAL_YEAR*100 +FINANCIAL_PERIOD <= $P{YEAR}*100+$P{PERIOD}
AND MAD.PK_NO_DET = $P{PK_NO_DET} AND MAD.INVOICE_NO = $P{INVOICE_NO}
AND $P{FROM_WHERE} = 'MAINCONAPIN'
GROUP BY
APINV.INVOICE_NO,
APINV.AGE_DATE ,
SCHDR.SUB_CON_CODE,MP.PARTY_NAME,
SUBHDR.CLAIM_STATUS,
SUBHDR.CLM_SEQ_NO,
APINV.CURRENCY_CODE

UNION ALL
SELECT
APINV.CREDIT_NOTE_NO INVOICE_NO,
APCRN.AGE_DATE,
SCHDR.SUB_CON_CODE,
MP.PARTY_NAME SUBCON_NAME,
SUBHDR.CLAIM_STATUS,
SUBHDR.CLM_SEQ_NO,
APCRN.CURRENCY_CODE,
-SUM(SUBCBC.TOTAL_AMT) AMT,
-SUM(SUBCBC.TOTAL_HOME_AMT) HOME_AMT
FROM PJ_MAINCONAPIN_ALLOC_DET MAD
JOIN PJ_SUB_CON_BACK_CHARGE SUBBC ON MAD.SUB_CON_CONTRACT_NO = SUBBC.SUB_CON_CONTRACT_NO AND MAD.SUB_CON_BACKCHARGE_SEQ_NO = SUBBC.SUB_SEQ_NO
JOIN PJ_SUB_CLM_BACK_CHARGE SUBCBC ON SUBBC.SUB_CON_CONTRACT_NO = SUBCBC.SUBCON_CONTRACT_NO AND SUBBC.SUB_SEQ_NO = SUBCBC.SOURCE_SEQ_NO
JOIN PJ_SUB_CLM_HDR SUBHDR ON SUBHDR.SUB_CON_CLM_VOUCHER_NO = SUBCBC.SUB_CON_CLM_VOUCHER_NO
JOIN PJ_SUB_CON_HDR SCHDR ON SCHDR.SUB_CON_CONTRACT_NO = SUBHDR.SUB_CON_CONTRACT_NO
LEFT JOIN PJ_AP_INV_HST_HDR APINV ON APINV.CLAIM_VOUCHER_NO = SUBCBC.SUB_CON_CLM_VOUCHER_NO
LEFT JOIN AP_CRN_HST_HDR APCRN ON APINV.CREDIT_NOTE_NO = APCRN.CREDIT_NOTE_NO
JOIN PJ_MAINCONAPIN_ALLOC A ON A.INVOICE_NO = MAD.INVOICE_NO AND A.PK_NO_ALLOC = MAD.PK_NO_ALLOC
LEFT JOIN MT_FINANCIAL_PERIOD FP ON A.ALLOCATION_DATE BETWEEN FP.PERIOD_START_DATE AND FP.PERIOD_CLOSING_DATE
LEFT JOIN MT_PARTY MP ON MP.PARTY_CODE = SCHDR.SUB_CON_CODE
WHERE SUBHDR.STATUS = 'H' AND SUBHDR.CLAIM_STATUS ='X'
AND MAD.ALLOC_TYPE = 'S'
AND FP.FINANCIAL_YEAR*100 +FINANCIAL_PERIOD <= $P{YEAR}*100+$P{PERIOD}
AND MAD.PK_NO_DET = $P{PK_NO_DET} AND MAD.INVOICE_NO = $P{INVOICE_NO}
AND $P{FROM_WHERE} = 'MAINCONAPIN'
GROUP BY
APINV.CREDIT_NOTE_NO,
APCRN.AGE_DATE,
SCHDR.SUB_CON_CODE,MP.PARTY_NAME,
SUBHDR.CLAIM_STATUS,
SUBHDR.CLM_SEQ_NO,
APCRN.CURRENCY_CODE

UNION ALL
SELECT
'' INVOICE_NO,
CAST(NULL AS TIMESTAMP) AGE_DATE,
SCHDR.SUB_CON_CODE,
MP.PARTY_NAME SUBCON_NAME,
'' CLAIM_STATUS,
CAST (NULL AS INTEGER) CLM_SEQ_NO,
SCHDR.CURRENCY_CODE,
SUM(MAD.SUBCON_BACKCHARGE_AMT - SUBBC.BACK_CHARGE_CLAIMED_AMT) AS AMT,
SUM(MAD.ALLOC_HOME_AMT - SUBBC.BACK_CHARGE_CLAIMED_HOME_AMT) AS HOME_AMT
FROM PJ_MAINCONAPIN_ALLOC_DET MAD
  JOIN PJ_SUB_CON_BACK_CHARGE SUBBC ON MAD.SUB_CON_CONTRACT_NO = SUBBC.SUB_CON_CONTRACT_NO AND MAD.SUB_CON_BACKCHARGE_SEQ_NO = SUBBC.SUB_SEQ_NO
  JOIN PJ_SUB_CON_HDR SCHDR ON SCHDR.SUB_CON_CONTRACT_NO = SUBBC.SUB_CON_CONTRACT_NO
  LEFT JOIN MT_PARTY MP ON MP.PARTY_CODE = SCHDR.SUB_CON_CODE
  JOIN PJ_MAINCONAPIN_ALLOC A ON A.INVOICE_NO = MAD.INVOICE_NO AND A.PK_NO_ALLOC = MAD.PK_NO_ALLOC
LEFT JOIN MT_FINANCIAL_PERIOD FP ON A.ALLOCATION_DATE BETWEEN FP.PERIOD_START_DATE AND FP.PERIOD_CLOSING_DATE
WHERE (SUBBC.TOTAL_AMT - SUBBC.BACK_CHARGE_CLAIMED_AMT) > 0
AND FP.FINANCIAL_YEAR*100 +FINANCIAL_PERIOD <= $P{YEAR}*100+$P{PERIOD}
AND MAD.PK_NO_DET = $P{PK_NO_DET} AND MAD.INVOICE_NO = $P{INVOICE_NO}
AND $P{FROM_WHERE} = 'MAINCONAPIN'
GROUP BY
SCHDR.SUB_CON_CODE,
MP.PARTY_NAME,
SCHDR.CURRENCY_CODE

UNION ALL
SELECT
'' INVOICE_NO,
CAST(NULL AS TIMESTAMP) AGE_DATE,
SCHDR.SUB_CON_CODE,
MP.PARTY_NAME SUBCON_NAME,
'' CLAIM_STATUS,
CAST (NULL AS INTEGER) CLM_SEQ_NO,
SCHDR.CURRENCY_CODE,
-SUM(MAD.ALLOC_AMT) AMT,
-SUM(MAD.ALLOC_HOME_AMT) HOME_AMT
FROM PJ_MAINCONAPCN_ALLOC_DET MAD
JOIN PJ_SUB_CON_BACK_CHARGE SUBBC ON MAD.SUB_CON_CONTRACT_NO = SUBBC.SUB_CON_CONTRACT_NO AND MAD.SUB_CON_BACKCHARGE_SEQ_NO = SUBBC.SUB_SEQ_NO
JOIN PJ_SUB_CON_HDR SCHDR ON SCHDR.SUB_CON_CONTRACT_NO = SUBBC.SUB_CON_CONTRACT_NO
JOIN PJ_MAINCONAPCN_ALLOC A ON A.CREDIT_NOTE_NO = MAD.CREDIT_NOTE_NO AND A.PK_NO_ALLOC = MAD.PK_NO_ALLOC
LEFT JOIN MT_FINANCIAL_PERIOD FP ON A.ALLOCATION_DATE BETWEEN FP.PERIOD_START_DATE AND FP.PERIOD_CLOSING_DATE
LEFT JOIN MT_PARTY MP ON MP.PARTY_CODE = SCHDR.SUB_CON_CODE
WHERE 1=1
AND MAD.ALLOC_TYPE = 'S' AND A.STATUS = 'H'
AND FP.FINANCIAL_YEAR*100 +FINANCIAL_PERIOD <= $P{YEAR}*100+$P{PERIOD}
AND MAD.PK_NO_DET = $P{PK_NO_DET} AND MAD.CREDIT_NOTE_NO = $P{INVOICE_NO}
AND $P{FROM_WHERE} = 'MAINCONAPCN'
GROUP BY
SCHDR.SUB_CON_CODE,
MP.PARTY_NAME,
SCHDR.CURRENCY_CODE

ORDER BY AGE_DATE