3.1 main

--FROM MAINCONAPIN
SELECT
H.PROJECT_NO,
H.AGE_DATE
,H.INVOICE_NO AS CUSTOMER_INVOICE_NO
,H.MAINCON_INVOICE_NO
,D.PK_NO_DET
,D.SERVICE_CODE
,MS.SERVICE_DESC
,CAST(D.LINE_ITEM_REMARKS AS VARCHAR(2000)) LINE_ITEM_REMARKS
,PJ.CURRENCY_CODE
,COALESCE(D.BEFORE_TAX_AMT,0) AS AMT_EXCL_GST
,COALESCE(D.BEFORE_TAX_HOME_AMT,0) AS AMT_EXCL_GST_HOME
,COALESCE(D.SALES_TAX_AMT,0) AS GST_AMT
,CONTRA1.AR_INVOICE_NO MAINCON_CLAIM_NO
,CONTRA2.CHEQUE_NO MAINCON_CHEQUE_NO
,COALESCE(SUBCON.AMT,0) SUBCON_AMT
,COALESCE(SUBCON.HOME_AMT,0) SUBCON_HOME_AMT
,COALESCE(BCAW.ALLOC_HOME_AMT,0) BCAW_ALLOC_HOME_AMT
,COALESCE(ABSORBED.ALLOC_AMT,0) ABSORBED_ALLOC_AMT
,COALESCE(ABSORBED.ALLOC_HOME_AMT,0) ABSORBED_ALLOC_HOME_AMT
,COALESCE(D.BEFORE_TAX_AMT,0) - COALESCE(ALLOC.AMT,0) UNALLOC_AMT
,'MAINCONAPIN' FROM_WHERE
FROM PJ_MAINCONAPIN_HDR H
LEFT JOIN PJ_MAINCONAPIN_DET D ON H.INVOICE_NO = D.INVOICE_NO
LEFT JOIN MT_SERVICE MS ON D.SERVICE_CODE = MS.SERVICE_CODE
LEFT JOIN PJ_EST_OST_HDR PJ ON PJ.PROJECT_NO = H.PROJECT_NO
--CONCAT ALL AR_INVOICE_NO GROUP BY H.INVOICE_NO
LEFT JOIN
(
SELECT SUBSTR(CAST(xml2clob (XMLAGG (xmltext (CONCAT (', ',AR_INVOICE_NO )))) AS VARCHAR(2000)),2) AS AR_INVOICE_NO,AP_INVOICE_NO
FROM
(
    SELECT AP_INVOICE_NO,AR_INVOICE_NO
    FROM PARTY_CONTRA_HST_APL_SPR SPR
     INNER JOIN PARTY_CONTRA_HST_APL_CTM CTM ON SPR.CONTRA_VOUCHER_NO = CTM.CONTRA_VOUCHER_NO
    GROUP BY AP_INVOICE_NO,AR_INVOICE_NO
) TP
 GROUP BY AP_INVOICE_NO
) CONTRA1 ON CONTRA1.AP_INVOICE_NO = H.INVOICE_NO
--CONCAT ALL CHEQUE_NO GROUP BY H.INVOICE_NO
LEFT JOIN
(
SELECT SUBSTR(CAST(xml2clob (XMLAGG (xmltext (CONCAT (', ',CHEQUE_NO )))) AS VARCHAR(2000)),2) AS CHEQUE_NO,AP_INVOICE_NO
FROM
(
    SELECT AP_INVOICE_NO,CHEQUE_NO
    FROM PARTY_CONTRA_HST_APL_SPR SPR
     INNER JOIN PARTY_CONTRA_HST_APL_CTM CTM ON SPR.CONTRA_VOUCHER_NO = CTM.CONTRA_VOUCHER_NO
     INNER JOIN AR_REC_HST_APL RECAPL ON CTM.AR_INVOICE_NO = RECAPL.INVOICE_NO
     INNER JOIN AR_REC_HST_HDR RECHDR ON RECHDR.RECEIPT_VOUCHER_NO = RECAPL.RECEIPT_VOUCHER_NO
    GROUP BY AP_INVOICE_NO,CHEQUE_NO
) TP
 GROUP BY AP_INVOICE_NO
) CONTRA2 ON CONTRA2.AP_INVOICE_NO = H.INVOICE_NO

-- back charge to subcon
LEFT JOIN
(
SELECT H.PK_NO_DET, H.INVOICE_NO,
    SUM(H.AMT) AMT,
    SUM(H.HOME_AMT) HOME_AMT
FROM
    (
    SELECT
    MAD.PK_NO_DET, MAD.INVOICE_NO,
    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.STATUS = 'H'
    AND MAD.ALLOC_TYPE = 'S'
    AND FP.FINANCIAL_YEAR*100 +FINANCIAL_PERIOD <= $P{YEAR}*100+$P{PERIOD}
    GROUP BY
    MAD.PK_NO_DET, MAD.INVOICE_NO

    UNION ALL
    SELECT
    MAD.PK_NO_DET, MAD.INVOICE_NO,
    -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}
    GROUP BY
    MAD.PK_NO_DET, MAD.INVOICE_NO

    UNION ALL
    SELECT
    MAD.PK_NO_DET, MAD.INVOICE_NO,
    SUM(SUBBC.TOTAL_AMT - SUBBC.BACK_CHARGE_CLAIMED_AMT) AS AMT,
    SUM(SUBBC.TOTAL_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}
    GROUP BY
    MAD.PK_NO_DET, MAD.INVOICE_NO
    ) H
    GROUP BY
    H.PK_NO_DET, H.INVOICE_NO
) SUBCON ON SUBCON.INVOICE_NO = H.INVOICE_NO AND SUBCON.PK_NO_DET = D.PK_NO_DET

--back charge to APP worker
LEFT JOIN
(
SELECT AD.INVOICE_NO, AD.PK_NO_DET
,SUM(AD.ALLOC_HOME_AMT) ALLOC_HOME_AMT
FROM PY_DEDUCTION_SERVICE DS
JOIN PJ_MAINCONAPIN_ALLOC_DET AD ON DS.MAINCONAPIN_INVOICE_NO = AD.INVOICE_NO AND DS.MAINCONAPIN_PK_NO_ALLOC = AD.PK_NO_ALLOC AND DS.MAINCONAPIN_PK_NO_ALLOC_DET = AD.PK_NO_ALLOC_DET
JOIN PJ_MAINCONAPIN_ALLOC A ON A.INVOICE_NO = AD.INVOICE_NO AND A.PK_NO_ALLOC = AD.PK_NO_ALLOC
JOIN PY_DEDUCTION PY ON PY.DEDUCTION_VOUCHER_NO = DS.DEDUCTION_VOUCHER_NO
LEFT JOIN MT_EMPLOYEE ME ON ME.EMPLOYEE_CODE = DS.EMPLOYEE_CODE
LEFT JOIN MT_FINANCIAL_PERIOD FP ON A.ALLOCATION_DATE BETWEEN FP.PERIOD_START_DATE AND FP.PERIOD_CLOSING_DATE
WHERE A.STATUS = 'H'
AND AD.ALLOC_TYPE = 'E'
AND FP.FINANCIAL_YEAR*100 +FINANCIAL_PERIOD <= $P{YEAR}*100+$P{PERIOD}
GROUP BY AD.INVOICE_NO, AD.PK_NO_DET
) BCAW ON BCAW.INVOICE_NO = H.INVOICE_NO AND BCAW.PK_NO_DET = D.PK_NO_DET
--end back charge to APP worker

--APP absorbed
LEFT JOIN
(
SELECT AD.INVOICE_NO, AD.PK_NO_DET,SUM(AD.ALLOC_AMT) ALLOC_AMT,SUM(AD.ALLOC_HOME_AMT) ALLOC_HOME_AMT
FROM  PJ_MAINCONAPIN_ALLOC_DET AD
JOIN PJ_MAINCONAPIN_ALLOC A ON A.INVOICE_NO = AD.INVOICE_NO AND A.PK_NO_ALLOC = AD.PK_NO_ALLOC
LEFT JOIN MT_FINANCIAL_PERIOD FP ON A.ALLOCATION_DATE BETWEEN FP.PERIOD_START_DATE AND FP.PERIOD_CLOSING_DATE
WHERE 1=1
AND A.STATUS = 'H' AND AD.ALLOC_TYPE = 'P'
AND FP.FINANCIAL_YEAR*100 +FINANCIAL_PERIOD <= $P{YEAR}*100+$P{PERIOD}
GROUP BY AD.INVOICE_NO, AD.PK_NO_DET
) ABSORBED  ON ABSORBED.INVOICE_NO = H.INVOICE_NO AND ABSORBED.PK_NO_DET = D.PK_NO_DET
--end APP absorbed
--UNALLOC_AMT
LEFT JOIN
(
SELECT
MAD.PK_NO_DET, MAD.INVOICE_NO,
SUM(MAD.ALLOC_AMT) AMT
FROM PJ_MAINCONAPIN_ALLOC_DET MAD
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 1=1
AND A.STATUS = 'H'
AND FP.FINANCIAL_YEAR*100 +FINANCIAL_PERIOD <= $P{YEAR}*100+$P{PERIOD}
GROUP BY
MAD.PK_NO_DET, MAD.INVOICE_NO
) ALLOC  ON ALLOC.INVOICE_NO = H.INVOICE_NO AND ALLOC.PK_NO_DET = D.PK_NO_DET
--END UNALLOC_AMT


LEFT JOIN MT_FINANCIAL_PERIOD FP ON H.AGE_DATE BETWEEN FP.PERIOD_START_DATE AND FP.PERIOD_CLOSING_DATE
WHERE 1=1
$P!{__RPT__FILTER__PARAM}
AND FP.FINANCIAL_YEAR*100 +FINANCIAL_PERIOD <= $P{YEAR}*100+$P{PERIOD}

UNION ALL
--FROM MAINCONAPCN
SELECT
H.PROJECT_NO,
H.AGE_DATE
,H.CREDIT_NOTE_NO AS CUSTOMER_INVOICE_NO
,H.MAINCON_CREDIT_NOTE_NO MAINCON_INVOICE_NO
,D.PK_NO_DET
,D.SERVICE_CODE
,MS.SERVICE_DESC
,'' LINE_ITEM_REMARKS
,PJ.CURRENCY_CODE
,-COALESCE(D.BEFORE_TAX_AMT,0) AS AMT_EXCL_GST
,-COALESCE(D.BEFORE_TAX_HOME_AMT,0) AS AMT_EXCL_GST_HOME
,-COALESCE(D.SALES_TAX_AMT,0) AS GST_AMT
,'' MAINCON_CLAIM_NO
,'' MAINCON_CHEQUE_NO
,-COALESCE(SUBCON.AMT,0) SUBCON_AMT
,-COALESCE(SUBCON.HOME_AMT,0) SUBCON_HOME_AMT
,-COALESCE(BCAW.ALLOC_HOME_AMT,0) BCAW_ALLOC_HOME_AMT
,-COALESCE(ABSORBED.ALLOC_AMT,0) ABSORBED_ALLOC_AMT
,-COALESCE(ABSORBED.ALLOC_HOME_AMT,0) ABSORBED_ALLOC_HOME_AMT
,-COALESCE(D.BEFORE_TAX_AMT,0)+COALESCE(ALLOC.AMT,0) UNALLOC_AMT
,'MAINCONAPCN' FROM_WHERE
FROM PJ_MAINCONAPCN_HDR H
LEFT JOIN PJ_MAINCONAPCN_DET D ON H.CREDIT_NOTE_NO = D.CREDIT_NOTE_NO
LEFT JOIN MT_SERVICE MS ON D.SERVICE_CODE = MS.SERVICE_CODE
LEFT JOIN PJ_EST_OST_HDR PJ ON PJ.PROJECT_NO = H.PROJECT_NO

-- back charge to subcon
LEFT JOIN
(
    SELECT
    MAD.PK_NO_DET, MAD.CREDIT_NOTE_NO,
    SUM(SUBCBC.TOTAL_AMT) AMT,
    SUM(SUBCBC.TOTAL_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_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
    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 SUBHDR.STATUS = 'H'
    AND MAD.ALLOC_TYPE = 'S'
    AND FP.FINANCIAL_YEAR*100 +FINANCIAL_PERIOD <= $P{YEAR}*100+$P{PERIOD}
    GROUP BY
    MAD.PK_NO_DET, MAD.CREDIT_NOTE_NO
) SUBCON ON SUBCON.CREDIT_NOTE_NO = H.CREDIT_NOTE_NO AND SUBCON.PK_NO_DET = D.PK_NO_DET

--back charge to APP worker
LEFT JOIN
(
SELECT AD.CREDIT_NOTE_NO, AD.PK_NO_DET
,SUM(AD.ALLOC_HOME_AMT) ALLOC_HOME_AMT
FROM PY_ADD_PMT_EMPLOYEE_SERVICE DS
JOIN PJ_MAINCONAPcn_ALLOC_DET AD ON DS.MAINCONAPCN_CREDIT_NOTE_NO = AD.CREDIT_NOTE_NO AND DS.MAINCONAPCN_PK_NO_ALLOC = AD.PK_NO_ALLOC AND DS.MAINCONAPCN_PK_NO_ALLOC_DET = AD.PK_NO_ALLOC_DET
JOIN PJ_MAINCONAPcn_ALLOC A ON A.CREDIT_NOTE_NO = AD.CREDIT_NOTE_NO AND A.PK_NO_ALLOC = AD.PK_NO_ALLOC
JOIN PY_ADDITIONAL_PAYMENT PY ON PY.ADDITIONAL_PAYMENT_VCH_NO = DS.ADDITIONAL_PAYMENT_VCH_NO
LEFT JOIN MT_EMPLOYEE ME ON ME.EMPLOYEE_CODE = DS.EMPLOYEE_CODE
LEFT JOIN MT_FINANCIAL_PERIOD FP ON A.ALLOCATION_DATE BETWEEN FP.PERIOD_START_DATE AND FP.PERIOD_CLOSING_DATE
WHERE A.STATUS = 'H'
AND AD.ALLOC_TYPE = 'E'
AND FP.FINANCIAL_YEAR*100 +FINANCIAL_PERIOD <= $P{YEAR}*100+$P{PERIOD}
GROUP BY AD.CREDIT_NOTE_NO, AD.PK_NO_DET
) BCAW ON BCAW.CREDIT_NOTE_NO = H.CREDIT_NOTE_NO AND BCAW.PK_NO_DET = D.PK_NO_DET
--end back charge to APP worker

--APP absorbed
LEFT JOIN
(
SELECT AD.CREDIT_NOTE_NO, AD.PK_NO_DET,SUM(AD.ALLOC_AMT) ALLOC_AMT,SUM(AD.ALLOC_HOME_AMT) ALLOC_HOME_AMT
FROM  PJ_MAINCONAPcn_ALLOC_DET AD
JOIN PJ_MAINCONAPcn_ALLOC A ON A.CREDIT_NOTE_NO = AD.CREDIT_NOTE_NO AND A.PK_NO_ALLOC = AD.PK_NO_ALLOC
LEFT JOIN MT_FINANCIAL_PERIOD FP ON A.ALLOCATION_DATE BETWEEN FP.PERIOD_START_DATE AND FP.PERIOD_CLOSING_DATE
WHERE 1=1
AND A.STATUS = 'H' AND AD.ALLOC_TYPE = 'P'
AND FP.FINANCIAL_YEAR*100 +FINANCIAL_PERIOD <= $P{YEAR}*100+$P{PERIOD}
GROUP BY AD.CREDIT_NOTE_NO, AD.PK_NO_DET
) ABSORBED  ON ABSORBED.CREDIT_NOTE_NO = H.CREDIT_NOTE_NO AND ABSORBED.PK_NO_DET = D.PK_NO_DET
--end APP absorbed
--UNALLOC_AMT
LEFT JOIN
(
SELECT
MAD.PK_NO_DET, MAD.CREDIT_NOTE_NO,
SUM(MAD.ALLOC_AMT) AMT
FROM PJ_MAINCONAPCN_ALLOC_DET MAD
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
WHERE 1=1
AND A.STATUS = 'H'
AND FP.FINANCIAL_YEAR*100 +FINANCIAL_PERIOD <= $P{YEAR}*100+$P{PERIOD}
GROUP BY
MAD.PK_NO_DET, MAD.CREDIT_NOTE_NO
) ALLOC  ON ALLOC.CREDIT_NOTE_NO = H.CREDIT_NOTE_NO AND ALLOC.PK_NO_DET = D.PK_NO_DET
--END UNALLOC_AMT

LEFT JOIN MT_FINANCIAL_PERIOD FP ON H.AGE_DATE BETWEEN FP.PERIOD_START_DATE AND FP.PERIOD_CLOSING_DATE
WHERE 1=1
$P!{__RPT__FILTER__PARAM}
AND FP.FINANCIAL_YEAR*100 +FINANCIAL_PERIOD <= $P{YEAR}*100+$P{PERIOD}

ORDER BY PROJECT_NO,AGE_DATE