Cash Basic detail - Cost

--TH6R_PJ_300049
WITH
TMPTBL_TO_GET_RIGHT_DATE AS
(
SELECT H.PROJECT_NO,H.TRN_SEQ_NO,H.NO_OF_ALLOC
,T1.YEAR_POSTED_TO FINANCIAL_YEAR
,T1.PERIOD_POSTED_TO FINANCIAL_PERIOD
FROM PJ_COMMITTED_COST_TRACK_TRN_HDR H
JOIN
(
    SELECT  INVOICE_NO, YEAR_POSTED_TO, PERIOD_POSTED_TO  FROM AP_CRN_HST_APL
    UNION
    SELECT  INVOICE_NO, YEAR_POSTED_TO, PERIOD_POSTED_TO  FROM AP_PAY_HST_APL

    UNION
    SELECT AP_INVOICE_NO AS INVOICE_NO, YEAR_POSTED_TO, PERIOD_POSTED_TO  FROM PARTY_CONTRA_HST_APL_SPR
) T1 ON H.VOUCHER_NO = T1.INVOICE_NO

WHERE 1=1
AND H.MODULE_CODE||H.TRANSACTION_TYPE_CODE NOT IN ('PJCA','LGPSH','PJCL','APCN','PJCAP') AND H.MODULE_CODE NOT IN ('PY','IC')
UNION ALL
SELECT H.PROJECT_NO,H.TRN_SEQ_NO,H.NO_OF_ALLOC
,T1.YEAR_POSTED_TO FINANCIAL_YEAR
,T1.PERIOD_POSTED_TO FINANCIAL_PERIOD
FROM PJ_COMMITTED_COST_TRACK_TRN_HDR H
JOIN
(
    SELECT  CREDIT_NOTE_NO INVOICE_NO, YEAR_POSTED_TO, PERIOD_POSTED_TO  FROM AP_CRN_HST_APL

) T1 ON H.VOUCHER_NO = T1.INVOICE_NO

WHERE 1=1
AND H.MODULE_CODE||H.TRANSACTION_TYPE_CODE  = 'APCN'
UNION ALL
SELECT H.PROJECT_NO,H.TRN_SEQ_NO,H.NO_OF_ALLOC
,H.FINANCIAL_YEAR FINANCIAL_YEAR
,H.FINANCIAL_PERIOD FINANCIAL_PERIOD
FROM PJ_COMMITTED_COST_TRACK_TRN_HDR H
WHERE 1=1
AND H.MODULE_CODE||H.TRANSACTION_TYPE_CODE IN ('BKTRN')

UNION ALL
SELECT H.PROJECT_NO,H.TRN_SEQ_NO,H.NO_OF_ALLOC
,H.FINANCIAL_YEAR FINANCIAL_YEAR
,H.FINANCIAL_PERIOD FINANCIAL_PERIOD
FROM PJ_COMMITTED_COST_TRACK_TRN_HDR H
JOIN PJ_AP_INV_HST_HDR T1 ON T1.INVOICE_NO = H.VOUCHER_NO
JOIN PJ_SUB_CLM_HDR T2 ON T2.SUB_CON_CLM_VOUCHER_NO = T1.SOURCE_VOUCHER_NO
WHERE 1=1
AND T2.SOURCE_TYPE = 'O'
AND T1.INVOICE_STATUS IS NULL
AND H.MODULE_CODE||H.TRANSACTION_TYPE_CODE IN ('PJCAP')

UNION ALL
SELECT H.PROJECT_NO,H.TRN_SEQ_NO,H.NO_OF_ALLOC
,T1.YEAR_POSTED_TO FINANCIAL_YEAR
,T1.PERIOD_POSTED_TO FINANCIAL_PERIOD
FROM PJ_COMMITTED_COST_TRACK_TRN_HDR H
JOIN
(
    SELECT  INVOICE_NO, YEAR_POSTED_TO, PERIOD_POSTED_TO  FROM AP_CRN_HST_APL
    UNION
    SELECT  INVOICE_NO, YEAR_POSTED_TO, PERIOD_POSTED_TO  FROM AP_PAY_HST_APL

    UNION
    SELECT AP_INVOICE_NO AS INVOICE_NO, YEAR_POSTED_TO, PERIOD_POSTED_TO  FROM PARTY_CONTRA_HST_APL_SPR
) T1 ON H.VOUCHER_NO = T1.INVOICE_NO
JOIN PJ_AP_INV_HST_HDR T2 ON T2.INVOICE_NO = H.VOUCHER_NO
JOIN PJ_SUB_CLM_HDR T3 ON T3.SUB_CON_CLM_VOUCHER_NO = T2.SOURCE_VOUCHER_NO
WHERE 1=1
AND (T3.SOURCE_TYPE IS NULL OR T3.SOURCE_TYPE <> 'O' OR T2.INVOICE_STATUS IS NOT NULL)
AND H.MODULE_CODE||H.TRANSACTION_TYPE_CODE IN ('PJCAP')

UNION ALL
SELECT H.PROJECT_NO,H.TRN_SEQ_NO,H.NO_OF_ALLOC
,FP.FINANCIAL_YEAR FINANCIAL_YEAR
,FP.FINANCIAL_PERIOD FINANCIAL_PERIOD
FROM PJ_COMMITTED_COST_TRACK_TRN_HDR H
LEFT JOIN PJ_COST_ALC_HST_HDR C ON C.COST_ALLOCATION_NO = H.VOUCHER_NO
LEFT JOIN MT_FINANCIAL_PERIOD FP ON DATE(C.COST_ALLOCATION_DATE) BETWEEN FP.PERIOD_START_DATE AND FP.PERIOD_CLOSING_DATE
WHERE 1=1
AND H.MODULE_CODE||H.TRANSACTION_TYPE_CODE IN ('PJCL')
UNION ALL
SELECT H.PROJECT_NO,H.TRN_SEQ_NO,H.NO_OF_ALLOC
,T1.YEAR_POSTED_TO FINANCIAL_YEAR
,T1.PERIOD_POSTED_TO FINANCIAL_PERIOD
FROM PJ_COMMITTED_COST_TRACK_TRN_HDR H
LEFT JOIN AP_INV_HST_HDR T2 ON LOCATE(';'||H.VOUCHER_NO||';',';'||T2.SOURCE_VOUCHER_NO||';')>0
LEFT JOIN
(
    SELECT  INVOICE_NO, YEAR_POSTED_TO, PERIOD_POSTED_TO  FROM AP_CRN_HST_APL
    UNION
    SELECT  INVOICE_NO, YEAR_POSTED_TO, PERIOD_POSTED_TO  FROM AP_PAY_HST_APL
    UNION
    SELECT AP_INVOICE_NO AS INVOICE_NO, YEAR_POSTED_TO, PERIOD_POSTED_TO  FROM PARTY_CONTRA_HST_APL_SPR
) T1 ON T2.INVOICE_NO = T1.INVOICE_NO
WHERE 1=1
AND H.MODULE_CODE||H.TRANSACTION_TYPE_CODE IN ('LGPSH')
),
CURRENT_ACTUAL_COST_TBL AS
(
SELECT
COALESCE(BCG.BUDGET_CATEGORY_GROUP_CODE,'ZZZ') BUDGET_CATEGORY_GROUP_CODE,
BCG.BUDGET_CATEGORY_GROUP_DESC BUDGET_CATEGORY_GROUP_DESC,
COALESCE(BGT_SCH.BUDGET_CATEGORY_CODE,'ZZZ') BUDGET_CATEGORY_CODE,
BC.BUDGET_CATEGORY_DESC,
SUM(D.ACTUAL_COST_IN_HOME_CCY) AMT,
COALESCE(MI.INVENTORY_CATEGORY_CODE,'ZZZ') ITEM_CATEGORY_CODE,
CAT.INVENTORY_CATEGORY_DESC ITEM_CATEGORY_DESC
FROM TMPTBL_TO_GET_RIGHT_DATE H
  INNER JOIN PJ_COMMITTED_COST_TRACK_TRN_DET D ON H.PROJECT_NO = D.PROJECT_NO AND H.TRN_SEQ_NO = D.TRN_SEQ_NO AND H.NO_OF_ALLOC = D.NO_OF_ALLOC
  LEFT JOIN PJ_EST_OST_HDR PJ ON PJ.PROJECT_NO = H.PROJECT_NO
  LEFT JOIN PJ_EST_OST_SUB_HDR PJSUB ON PJSUB.PROJECT_NO = H.PROJECT_NO
  LEFT JOIN PJ_BUDGET_OST_HDR BGT_H ON H.PROJECT_NO = BGT_H.PROJECT_NO
  LEFT JOIN PJ_BUDGET_OST_SCH BGT_SCH ON BGT_H.BUDGET_NO = BGT_SCH.BUDGET_NO AND D.SCH_SEQ_NO = BGT_SCH.SCH_SEQ_NO
  LEFT JOIN MT_BUDGET_CATEGORY BC ON BC.BUDGET_CATEGORY_CODE = BGT_SCH.BUDGET_CATEGORY_CODE
  LEFT JOIN MT_BUDGET_CATEGORY_GROUP BCG ON BC.BUDGET_CATEGORY_GROUP_CODE =BCG.BUDGET_CATEGORY_GROUP_CODE
  LEFT JOIN PJ_BUDGET_OST_CST_ITM CST ON CST.CST_ITM_SEQ_NO = D.CST_ITM_SEQ_NO AND CST.BUDGET_NO = BGT_SCH.BUDGET_NO AND CST.SCH_SEQ_NO = D.SCH_SEQ_NO
  LEFT JOIN MT_INVENTORY MI ON MI.INVENTORY_CODE = CST.INVENTORY_CODE
  LEFT JOIN MT_INVENTORY_CATEGORY CAT ON MI.INVENTORY_CATEGORY_CODE = CAT.INVENTORY_CATEGORY_CODE
WHERE 1=1
AND CST.DT_TYPE = 'I'
AND (100*H.FINANCIAL_YEAR + H.FINANCIAL_PERIOD) = 100*$P{FINANCIAL_YEAR} + $P{FINANCIAL_PERIOD}
AND PJ.PROJECT_NO = $P{PROJECT_NO}
GROUP BY BCG.BUDGET_CATEGORY_GROUP_CODE,
BCG.BUDGET_CATEGORY_GROUP_DESC,
BGT_SCH.BUDGET_CATEGORY_CODE,
BC.BUDGET_CATEGORY_DESC,
MI.INVENTORY_CATEGORY_CODE,
CAT.INVENTORY_CATEGORY_DESC

UNION ALL
SELECT

COALESCE(BCG.BUDGET_CATEGORY_GROUP_CODE,'ZZZ') BUDGET_CATEGORY_GROUP_CODE,
BCG.BUDGET_CATEGORY_GROUP_DESC BUDGET_CATEGORY_GROUP_DESC,
COALESCE(BGT_SCH.BUDGET_CATEGORY_CODE,'ZZZ') BUDGET_CATEGORY_CODE,
BC.BUDGET_CATEGORY_DESC,
SUM(D.ACTUAL_COST_IN_HOME_CCY) AMT,
COALESCE(MI.SERVICE_CATEGORY_CODE,'ZZZ') ITEM_CATEGORY_CODE,
CAT.SERVICE_CATEGORY_DESC   ITEM_CATEGORY_DESC
FROM TMPTBL_TO_GET_RIGHT_DATE H
  INNER JOIN PJ_COMMITTED_COST_TRACK_TRN_DET D ON H.PROJECT_NO = D.PROJECT_NO AND H.TRN_SEQ_NO = D.TRN_SEQ_NO AND H.NO_OF_ALLOC = D.NO_OF_ALLOC
  LEFT JOIN PJ_EST_OST_HDR PJ ON PJ.PROJECT_NO = H.PROJECT_NO
  LEFT JOIN PJ_EST_OST_SUB_HDR PJSUB ON PJSUB.PROJECT_NO = H.PROJECT_NO
  LEFT JOIN PJ_BUDGET_OST_HDR BGT_H ON H.PROJECT_NO = BGT_H.PROJECT_NO
  LEFT JOIN PJ_BUDGET_OST_SCH BGT_SCH ON BGT_H.BUDGET_NO = BGT_SCH.BUDGET_NO AND D.SCH_SEQ_NO = BGT_SCH.SCH_SEQ_NO
  LEFT JOIN MT_BUDGET_CATEGORY BC ON BC.BUDGET_CATEGORY_CODE = BGT_SCH.BUDGET_CATEGORY_CODE
  LEFT JOIN MT_BUDGET_CATEGORY_GROUP BCG ON BC.BUDGET_CATEGORY_GROUP_CODE =BCG.BUDGET_CATEGORY_GROUP_CODE
  LEFT JOIN PJ_BUDGET_OST_CST_ITM CST ON CST.CST_ITM_SEQ_NO = D.CST_ITM_SEQ_NO AND CST.BUDGET_NO = BGT_SCH.BUDGET_NO AND CST.SCH_SEQ_NO = D.SCH_SEQ_NO
  LEFT JOIN MT_SERVICE MI ON MI.SERVICE_CODE = CST.SERVICE_CODE
  LEFT JOIN MT_SERVICE_CATEGORY CAT ON MI.SERVICE_CATEGORY_CODE = CAT.SERVICE_CATEGORY_CODE
WHERE 1=1
AND CST.DT_TYPE = 'S'
AND (100*H.FINANCIAL_YEAR + H.FINANCIAL_PERIOD) = 100*$P{FINANCIAL_YEAR} + $P{FINANCIAL_PERIOD}
AND PJ.PROJECT_NO = $P{PROJECT_NO}
GROUP BY BCG.BUDGET_CATEGORY_GROUP_CODE,
BCG.BUDGET_CATEGORY_GROUP_DESC,
BGT_SCH.BUDGET_CATEGORY_CODE,
BC.BUDGET_CATEGORY_DESC,
MI.SERVICE_CATEGORY_CODE,
CAT.SERVICE_CATEGORY_DESC


UNION ALL
SELECT

COALESCE(BCG.BUDGET_CATEGORY_GROUP_CODE,'ZZZ') BUDGET_CATEGORY_GROUP_CODE,
BCG.BUDGET_CATEGORY_GROUP_DESC BUDGET_CATEGORY_GROUP_DESC,
COALESCE(BGT_SCH.BUDGET_CATEGORY_CODE,'ZZZ') BUDGET_CATEGORY_CODE,
BC.BUDGET_CATEGORY_DESC,
SUM(D.ACTUAL_COST_IN_HOME_CCY) AMT,
'SUBCONCOST' ITEM_CATEGORY_CODE,
'Subcon Cost'   ITEM_CATEGORY_DESC
FROM TMPTBL_TO_GET_RIGHT_DATE H
  INNER JOIN PJ_COMMITTED_COST_TRACK_TRN_DET D ON H.PROJECT_NO = D.PROJECT_NO AND H.TRN_SEQ_NO = D.TRN_SEQ_NO AND H.NO_OF_ALLOC = D.NO_OF_ALLOC
  LEFT JOIN PJ_EST_OST_HDR PJ ON PJ.PROJECT_NO = H.PROJECT_NO
  LEFT JOIN PJ_EST_OST_SUB_HDR PJSUB ON PJSUB.PROJECT_NO = H.PROJECT_NO
  LEFT JOIN PJ_BUDGET_OST_HDR BGT_H ON H.PROJECT_NO = BGT_H.PROJECT_NO
  LEFT JOIN PJ_BUDGET_OST_SCH BGT_SCH ON BGT_H.BUDGET_NO = BGT_SCH.BUDGET_NO AND D.SCH_SEQ_NO = BGT_SCH.SCH_SEQ_NO
  LEFT JOIN MT_BUDGET_CATEGORY BC ON BC.BUDGET_CATEGORY_CODE = BGT_SCH.BUDGET_CATEGORY_CODE
  LEFT JOIN MT_BUDGET_CATEGORY_GROUP BCG ON BC.BUDGET_CATEGORY_GROUP_CODE =BCG.BUDGET_CATEGORY_GROUP_CODE
  LEFT JOIN PJ_BUDGET_OST_CST_ITM CST ON CST.CST_ITM_SEQ_NO = D.CST_ITM_SEQ_NO AND CST.BUDGET_NO = BGT_SCH.BUDGET_NO AND CST.SCH_SEQ_NO = D.SCH_SEQ_NO
  LEFT JOIN MT_SERVICE MI ON MI.SERVICE_CODE = CST.SERVICE_CODE

WHERE 1=1
AND CST.DT_TYPE = 'N'
AND (100*H.FINANCIAL_YEAR + H.FINANCIAL_PERIOD) = 100*$P{FINANCIAL_YEAR} + $P{FINANCIAL_PERIOD}
AND PJ.PROJECT_NO = $P{PROJECT_NO}
GROUP BY BCG.BUDGET_CATEGORY_GROUP_CODE,
BCG.BUDGET_CATEGORY_GROUP_DESC,
BGT_SCH.BUDGET_CATEGORY_CODE,
BC.BUDGET_CATEGORY_DESC

UNION ALL
SELECT

COALESCE(BCG.BUDGET_CATEGORY_GROUP_CODE,'ZZZ') BUDGET_CATEGORY_GROUP_CODE,
BCG.BUDGET_CATEGORY_GROUP_DESC BUDGET_CATEGORY_GROUP_DESC,
COALESCE(BGT_SCH.BUDGET_CATEGORY_CODE,'ZZZ') BUDGET_CATEGORY_CODE,
BC.BUDGET_CATEGORY_DESC,
SUM(D.ACTUAL_COST_IN_HOME_CCY) AMT,
'EMPLOYEECOST' ITEM_CATEGORY_CODE,
'Employee Cost' ITEM_CATEGORY_DESC
FROM PJ_COMMITTED_COST_TRACK_TRN_HDR H
  INNER JOIN PJ_COMMITTED_COST_TRACK_TRN_DET D ON H.PROJECT_NO = D.PROJECT_NO AND H.TRN_SEQ_NO = D.TRN_SEQ_NO AND H.NO_OF_ALLOC = D.NO_OF_ALLOC
  LEFT JOIN PJ_EST_OST_HDR PJ ON PJ.PROJECT_NO = H.PROJECT_NO
  LEFT JOIN PJ_EST_OST_SUB_HDR PJSUB ON PJSUB.PROJECT_NO = H.PROJECT_NO
  LEFT JOIN PJ_BUDGET_OST_HDR BGT_H ON H.PROJECT_NO = BGT_H.PROJECT_NO
  LEFT JOIN PJ_BUDGET_OST_SCH BGT_SCH ON BGT_H.BUDGET_NO = BGT_SCH.BUDGET_NO AND D.SCH_SEQ_NO = BGT_SCH.SCH_SEQ_NO
  LEFT JOIN MT_BUDGET_CATEGORY BC ON BC.BUDGET_CATEGORY_CODE = BGT_SCH.BUDGET_CATEGORY_CODE
  LEFT JOIN MT_BUDGET_CATEGORY_GROUP BCG ON BC.BUDGET_CATEGORY_GROUP_CODE =BCG.BUDGET_CATEGORY_GROUP_CODE
  LEFT JOIN PJ_BUDGET_OST_CST_ITM CST ON CST.CST_ITM_SEQ_NO = D.CST_ITM_SEQ_NO AND CST.BUDGET_NO = BGT_SCH.BUDGET_NO AND CST.SCH_SEQ_NO = D.SCH_SEQ_NO
  LEFT JOIN MT_SERVICE MI ON MI.SERVICE_CODE = CST.SERVICE_CODE

WHERE 1=1
AND CST.DT_TYPE NOT IN ('N','S','I')
AND (100*H.FINANCIAL_YEAR + H.FINANCIAL_PERIOD) = 100*$P{FINANCIAL_YEAR} + $P{FINANCIAL_PERIOD}
AND PJ.PROJECT_NO = $P{PROJECT_NO}
GROUP BY BCG.BUDGET_CATEGORY_GROUP_CODE,
BCG.BUDGET_CATEGORY_GROUP_DESC,
BGT_SCH.BUDGET_CATEGORY_CODE,
BC.BUDGET_CATEGORY_DESC

),
ACCUM_ACTUAL_COST_TBL AS
(
SELECT

COALESCE(BCG.BUDGET_CATEGORY_GROUP_CODE,'ZZZ') BUDGET_CATEGORY_GROUP_CODE,
BCG.BUDGET_CATEGORY_GROUP_DESC BUDGET_CATEGORY_GROUP_DESC,
COALESCE(BGT_SCH.BUDGET_CATEGORY_CODE,'ZZZ') BUDGET_CATEGORY_CODE,
BC.BUDGET_CATEGORY_DESC,
SUM(D.ACTUAL_COST_IN_HOME_CCY) AMT,
COALESCE(MI.INVENTORY_CATEGORY_CODE,'ZZZ') ITEM_CATEGORY_CODE,
CAT.INVENTORY_CATEGORY_DESC ITEM_CATEGORY_DESC
FROM TMPTBL_TO_GET_RIGHT_DATE H
  INNER JOIN PJ_COMMITTED_COST_TRACK_TRN_DET D ON H.PROJECT_NO = D.PROJECT_NO AND H.TRN_SEQ_NO = D.TRN_SEQ_NO AND H.NO_OF_ALLOC = D.NO_OF_ALLOC
  LEFT JOIN PJ_EST_OST_HDR PJ ON PJ.PROJECT_NO = H.PROJECT_NO
  LEFT JOIN PJ_EST_OST_SUB_HDR PJSUB ON PJSUB.PROJECT_NO = H.PROJECT_NO
  LEFT JOIN PJ_BUDGET_OST_HDR BGT_H ON H.PROJECT_NO = BGT_H.PROJECT_NO
  LEFT JOIN PJ_BUDGET_OST_SCH BGT_SCH ON BGT_H.BUDGET_NO = BGT_SCH.BUDGET_NO AND D.SCH_SEQ_NO = BGT_SCH.SCH_SEQ_NO
  LEFT JOIN MT_BUDGET_CATEGORY BC ON BC.BUDGET_CATEGORY_CODE = BGT_SCH.BUDGET_CATEGORY_CODE
  LEFT JOIN MT_BUDGET_CATEGORY_GROUP BCG ON BC.BUDGET_CATEGORY_GROUP_CODE =BCG.BUDGET_CATEGORY_GROUP_CODE
  LEFT JOIN PJ_BUDGET_OST_CST_ITM CST ON CST.CST_ITM_SEQ_NO = D.CST_ITM_SEQ_NO AND CST.BUDGET_NO = BGT_SCH.BUDGET_NO AND CST.SCH_SEQ_NO = D.SCH_SEQ_NO
  LEFT JOIN MT_INVENTORY MI ON MI.INVENTORY_CODE = CST.INVENTORY_CODE
  LEFT JOIN MT_INVENTORY_CATEGORY CAT ON MI.INVENTORY_CATEGORY_CODE = CAT.INVENTORY_CATEGORY_CODE
WHERE 1=1
AND CST.DT_TYPE = 'I'
AND (100*H.FINANCIAL_YEAR + H.FINANCIAL_PERIOD) <= 100*$P{FINANCIAL_YEAR} + $P{FINANCIAL_PERIOD}
AND PJ.PROJECT_NO = $P{PROJECT_NO}
GROUP BY BCG.BUDGET_CATEGORY_GROUP_CODE,
BCG.BUDGET_CATEGORY_GROUP_DESC,
BGT_SCH.BUDGET_CATEGORY_CODE,
BC.BUDGET_CATEGORY_DESC,
MI.INVENTORY_CATEGORY_CODE,
CAT.INVENTORY_CATEGORY_DESC

UNION ALL
SELECT

COALESCE(BCG.BUDGET_CATEGORY_GROUP_CODE,'ZZZ') BUDGET_CATEGORY_GROUP_CODE,
BCG.BUDGET_CATEGORY_GROUP_DESC BUDGET_CATEGORY_GROUP_DESC,
COALESCE(BGT_SCH.BUDGET_CATEGORY_CODE,'ZZZ') BUDGET_CATEGORY_CODE,
BC.BUDGET_CATEGORY_DESC,
SUM(D.ACTUAL_COST_IN_HOME_CCY) AMT,
COALESCE(MI.SERVICE_CATEGORY_CODE,'ZZZ') ITEM_CATEGORY_CODE,
CAT.SERVICE_CATEGORY_DESC   ITEM_CATEGORY_DESC
FROM TMPTBL_TO_GET_RIGHT_DATE H
  INNER JOIN PJ_COMMITTED_COST_TRACK_TRN_DET D ON H.PROJECT_NO = D.PROJECT_NO AND H.TRN_SEQ_NO = D.TRN_SEQ_NO AND H.NO_OF_ALLOC = D.NO_OF_ALLOC
  LEFT JOIN PJ_EST_OST_HDR PJ ON PJ.PROJECT_NO = H.PROJECT_NO
  LEFT JOIN PJ_EST_OST_SUB_HDR PJSUB ON PJSUB.PROJECT_NO = H.PROJECT_NO
  LEFT JOIN PJ_BUDGET_OST_HDR BGT_H ON H.PROJECT_NO = BGT_H.PROJECT_NO
  LEFT JOIN PJ_BUDGET_OST_SCH BGT_SCH ON BGT_H.BUDGET_NO = BGT_SCH.BUDGET_NO AND D.SCH_SEQ_NO = BGT_SCH.SCH_SEQ_NO
  LEFT JOIN MT_BUDGET_CATEGORY BC ON BC.BUDGET_CATEGORY_CODE = BGT_SCH.BUDGET_CATEGORY_CODE
  LEFT JOIN MT_BUDGET_CATEGORY_GROUP BCG ON BC.BUDGET_CATEGORY_GROUP_CODE =BCG.BUDGET_CATEGORY_GROUP_CODE
  LEFT JOIN PJ_BUDGET_OST_CST_ITM CST ON CST.CST_ITM_SEQ_NO = D.CST_ITM_SEQ_NO AND CST.BUDGET_NO = BGT_SCH.BUDGET_NO AND CST.SCH_SEQ_NO = D.SCH_SEQ_NO
  LEFT JOIN MT_SERVICE MI ON MI.SERVICE_CODE = CST.SERVICE_CODE
  LEFT JOIN MT_SERVICE_CATEGORY CAT ON MI.SERVICE_CATEGORY_CODE = CAT.SERVICE_CATEGORY_CODE
WHERE 1=1
AND CST.DT_TYPE = 'S'
AND (100*H.FINANCIAL_YEAR + H.FINANCIAL_PERIOD) <= 100*$P{FINANCIAL_YEAR} + $P{FINANCIAL_PERIOD}
AND PJ.PROJECT_NO = $P{PROJECT_NO}
GROUP BY BCG.BUDGET_CATEGORY_GROUP_CODE,
BCG.BUDGET_CATEGORY_GROUP_DESC,
BGT_SCH.BUDGET_CATEGORY_CODE,
BC.BUDGET_CATEGORY_DESC,
MI.SERVICE_CATEGORY_CODE,
CAT.SERVICE_CATEGORY_DESC


UNION ALL
SELECT

COALESCE(BCG.BUDGET_CATEGORY_GROUP_CODE,'ZZZ') BUDGET_CATEGORY_GROUP_CODE,
BCG.BUDGET_CATEGORY_GROUP_DESC BUDGET_CATEGORY_GROUP_DESC,
COALESCE(BGT_SCH.BUDGET_CATEGORY_CODE,'ZZZ') BUDGET_CATEGORY_CODE,
BC.BUDGET_CATEGORY_DESC,
SUM(D.ACTUAL_COST_IN_HOME_CCY) AMT,
'SUBCONCOST' ITEM_CATEGORY_CODE,
'Subcon Cost'   ITEM_CATEGORY_DESC
FROM TMPTBL_TO_GET_RIGHT_DATE H
  INNER JOIN PJ_COMMITTED_COST_TRACK_TRN_DET D ON H.PROJECT_NO = D.PROJECT_NO AND H.TRN_SEQ_NO = D.TRN_SEQ_NO AND H.NO_OF_ALLOC = D.NO_OF_ALLOC
  LEFT JOIN PJ_EST_OST_HDR PJ ON PJ.PROJECT_NO = H.PROJECT_NO
  LEFT JOIN PJ_EST_OST_SUB_HDR PJSUB ON PJSUB.PROJECT_NO = H.PROJECT_NO
  LEFT JOIN PJ_BUDGET_OST_HDR BGT_H ON H.PROJECT_NO = BGT_H.PROJECT_NO
  LEFT JOIN PJ_BUDGET_OST_SCH BGT_SCH ON BGT_H.BUDGET_NO = BGT_SCH.BUDGET_NO AND D.SCH_SEQ_NO = BGT_SCH.SCH_SEQ_NO
  LEFT JOIN MT_BUDGET_CATEGORY BC ON BC.BUDGET_CATEGORY_CODE = BGT_SCH.BUDGET_CATEGORY_CODE
  LEFT JOIN MT_BUDGET_CATEGORY_GROUP BCG ON BC.BUDGET_CATEGORY_GROUP_CODE =BCG.BUDGET_CATEGORY_GROUP_CODE
  LEFT JOIN PJ_BUDGET_OST_CST_ITM CST ON CST.CST_ITM_SEQ_NO = D.CST_ITM_SEQ_NO AND CST.BUDGET_NO = BGT_SCH.BUDGET_NO AND CST.SCH_SEQ_NO = D.SCH_SEQ_NO
WHERE 1=1
AND CST.DT_TYPE = 'N'
AND (100*H.FINANCIAL_YEAR + H.FINANCIAL_PERIOD) <= 100*$P{FINANCIAL_YEAR} + $P{FINANCIAL_PERIOD}
AND PJ.PROJECT_NO = $P{PROJECT_NO}
GROUP BY BCG.BUDGET_CATEGORY_GROUP_CODE,
BCG.BUDGET_CATEGORY_GROUP_DESC,
BGT_SCH.BUDGET_CATEGORY_CODE,
BC.BUDGET_CATEGORY_DESC

UNION ALL
SELECT

COALESCE(BCG.BUDGET_CATEGORY_GROUP_CODE,'ZZZ') BUDGET_CATEGORY_GROUP_CODE,
BCG.BUDGET_CATEGORY_GROUP_DESC BUDGET_CATEGORY_GROUP_DESC,
COALESCE(BGT_SCH.BUDGET_CATEGORY_CODE,'ZZZ') BUDGET_CATEGORY_CODE,
BC.BUDGET_CATEGORY_DESC,
SUM(D.ACTUAL_COST_IN_HOME_CCY) AMT,
'EMPLOYEECOST' ITEM_CATEGORY_CODE,
'Employee Cost' ITEM_CATEGORY_DESC
FROM PJ_COMMITTED_COST_TRACK_TRN_HDR H
  INNER JOIN PJ_COMMITTED_COST_TRACK_TRN_DET D ON H.PROJECT_NO = D.PROJECT_NO AND H.TRN_SEQ_NO = D.TRN_SEQ_NO AND H.NO_OF_ALLOC = D.NO_OF_ALLOC
  LEFT JOIN PJ_EST_OST_HDR PJ ON PJ.PROJECT_NO = H.PROJECT_NO
  LEFT JOIN PJ_EST_OST_SUB_HDR PJSUB ON PJSUB.PROJECT_NO = H.PROJECT_NO
  LEFT JOIN PJ_BUDGET_OST_HDR BGT_H ON H.PROJECT_NO = BGT_H.PROJECT_NO
  LEFT JOIN PJ_BUDGET_OST_SCH BGT_SCH ON BGT_H.BUDGET_NO = BGT_SCH.BUDGET_NO AND D.SCH_SEQ_NO = BGT_SCH.SCH_SEQ_NO
  LEFT JOIN MT_BUDGET_CATEGORY BC ON BC.BUDGET_CATEGORY_CODE = BGT_SCH.BUDGET_CATEGORY_CODE
  LEFT JOIN MT_BUDGET_CATEGORY_GROUP BCG ON BC.BUDGET_CATEGORY_GROUP_CODE =BCG.BUDGET_CATEGORY_GROUP_CODE
  LEFT JOIN PJ_BUDGET_OST_CST_ITM CST ON CST.CST_ITM_SEQ_NO = D.CST_ITM_SEQ_NO AND CST.BUDGET_NO = BGT_SCH.BUDGET_NO AND CST.SCH_SEQ_NO = D.SCH_SEQ_NO

WHERE 1=1
AND CST.DT_TYPE NOT IN ('N','S','I')
AND (100*H.FINANCIAL_YEAR + H.FINANCIAL_PERIOD) <= 100*$P{FINANCIAL_YEAR} + $P{FINANCIAL_PERIOD}
AND PJ.PROJECT_NO = $P{PROJECT_NO}
GROUP BY BCG.BUDGET_CATEGORY_GROUP_CODE,
BCG.BUDGET_CATEGORY_GROUP_DESC,
BGT_SCH.BUDGET_CATEGORY_CODE,
BC.BUDGET_CATEGORY_DESC

),
YTD_ACTUAL_COST_TBL AS
(
SELECT

COALESCE(BCG.BUDGET_CATEGORY_GROUP_CODE,'ZZZ') BUDGET_CATEGORY_GROUP_CODE,
BCG.BUDGET_CATEGORY_GROUP_DESC BUDGET_CATEGORY_GROUP_DESC,
COALESCE(BGT_SCH.BUDGET_CATEGORY_CODE,'ZZZ') BUDGET_CATEGORY_CODE,
BC.BUDGET_CATEGORY_DESC,
SUM(D.ACTUAL_COST_IN_HOME_CCY) AMT,
COALESCE(MI.INVENTORY_CATEGORY_CODE,'ZZZ') ITEM_CATEGORY_CODE,
CAT.INVENTORY_CATEGORY_DESC ITEM_CATEGORY_DESC
FROM TMPTBL_TO_GET_RIGHT_DATE H
  INNER JOIN PJ_COMMITTED_COST_TRACK_TRN_DET D ON H.PROJECT_NO = D.PROJECT_NO AND H.TRN_SEQ_NO = D.TRN_SEQ_NO AND H.NO_OF_ALLOC = D.NO_OF_ALLOC
  LEFT JOIN PJ_EST_OST_HDR PJ ON PJ.PROJECT_NO = H.PROJECT_NO
  LEFT JOIN PJ_EST_OST_SUB_HDR PJSUB ON PJSUB.PROJECT_NO = H.PROJECT_NO
  LEFT JOIN PJ_BUDGET_OST_HDR BGT_H ON H.PROJECT_NO = BGT_H.PROJECT_NO
  LEFT JOIN PJ_BUDGET_OST_SCH BGT_SCH ON BGT_H.BUDGET_NO = BGT_SCH.BUDGET_NO AND D.SCH_SEQ_NO = BGT_SCH.SCH_SEQ_NO
  LEFT JOIN MT_BUDGET_CATEGORY BC ON BC.BUDGET_CATEGORY_CODE = BGT_SCH.BUDGET_CATEGORY_CODE
  LEFT JOIN MT_BUDGET_CATEGORY_GROUP BCG ON BC.BUDGET_CATEGORY_GROUP_CODE =BCG.BUDGET_CATEGORY_GROUP_CODE
  LEFT JOIN PJ_BUDGET_OST_CST_ITM CST ON CST.CST_ITM_SEQ_NO = D.CST_ITM_SEQ_NO AND CST.BUDGET_NO = BGT_SCH.BUDGET_NO AND CST.SCH_SEQ_NO = D.SCH_SEQ_NO
  LEFT JOIN MT_INVENTORY MI ON MI.INVENTORY_CODE = CST.INVENTORY_CODE
  LEFT JOIN MT_INVENTORY_CATEGORY CAT ON MI.INVENTORY_CATEGORY_CODE = CAT.INVENTORY_CATEGORY_CODE
WHERE 1=1
AND CST.DT_TYPE = 'I'
AND H.FINANCIAL_YEAR = $P{FINANCIAL_YEAR} AND H.FINANCIAL_PERIOD <= $P{FINANCIAL_PERIOD}
AND PJ.PROJECT_NO = $P{PROJECT_NO}
GROUP BY BCG.BUDGET_CATEGORY_GROUP_CODE,
BCG.BUDGET_CATEGORY_GROUP_DESC,
BGT_SCH.BUDGET_CATEGORY_CODE,
BC.BUDGET_CATEGORY_DESC,
MI.INVENTORY_CATEGORY_CODE,
CAT.INVENTORY_CATEGORY_DESC

UNION ALL
SELECT

COALESCE(BCG.BUDGET_CATEGORY_GROUP_CODE,'ZZZ') BUDGET_CATEGORY_GROUP_CODE,
BCG.BUDGET_CATEGORY_GROUP_DESC BUDGET_CATEGORY_GROUP_DESC,
COALESCE(BGT_SCH.BUDGET_CATEGORY_CODE,'ZZZ') BUDGET_CATEGORY_CODE,
BC.BUDGET_CATEGORY_DESC,
SUM(D.ACTUAL_COST_IN_HOME_CCY) AMT,
COALESCE(MI.SERVICE_CATEGORY_CODE,'ZZZ') ITEM_CATEGORY_CODE,
CAT.SERVICE_CATEGORY_DESC   ITEM_CATEGORY_DESC
FROM TMPTBL_TO_GET_RIGHT_DATE H
  INNER JOIN PJ_COMMITTED_COST_TRACK_TRN_DET D ON H.PROJECT_NO = D.PROJECT_NO AND H.TRN_SEQ_NO = D.TRN_SEQ_NO AND H.NO_OF_ALLOC = D.NO_OF_ALLOC
  LEFT JOIN PJ_EST_OST_HDR PJ ON PJ.PROJECT_NO = H.PROJECT_NO
  LEFT JOIN PJ_EST_OST_SUB_HDR PJSUB ON PJSUB.PROJECT_NO = H.PROJECT_NO
  LEFT JOIN PJ_BUDGET_OST_HDR BGT_H ON H.PROJECT_NO = BGT_H.PROJECT_NO
  LEFT JOIN PJ_BUDGET_OST_SCH BGT_SCH ON BGT_H.BUDGET_NO = BGT_SCH.BUDGET_NO AND D.SCH_SEQ_NO = BGT_SCH.SCH_SEQ_NO
  LEFT JOIN MT_BUDGET_CATEGORY BC ON BC.BUDGET_CATEGORY_CODE = BGT_SCH.BUDGET_CATEGORY_CODE
  LEFT JOIN MT_BUDGET_CATEGORY_GROUP BCG ON BC.BUDGET_CATEGORY_GROUP_CODE =BCG.BUDGET_CATEGORY_GROUP_CODE
  LEFT JOIN PJ_BUDGET_OST_CST_ITM CST ON CST.CST_ITM_SEQ_NO = D.CST_ITM_SEQ_NO AND CST.BUDGET_NO = BGT_SCH.BUDGET_NO AND CST.SCH_SEQ_NO = D.SCH_SEQ_NO
  LEFT JOIN MT_SERVICE MI ON MI.SERVICE_CODE = CST.SERVICE_CODE
  LEFT JOIN MT_SERVICE_CATEGORY CAT ON MI.SERVICE_CATEGORY_CODE = CAT.SERVICE_CATEGORY_CODE
WHERE 1=1
AND CST.DT_TYPE = 'S'
AND H.FINANCIAL_YEAR = $P{FINANCIAL_YEAR} AND H.FINANCIAL_PERIOD <= $P{FINANCIAL_PERIOD}
AND PJ.PROJECT_NO = $P{PROJECT_NO}
GROUP BY BCG.BUDGET_CATEGORY_GROUP_CODE,
BCG.BUDGET_CATEGORY_GROUP_DESC,
BGT_SCH.BUDGET_CATEGORY_CODE,
BC.BUDGET_CATEGORY_DESC,
MI.SERVICE_CATEGORY_CODE,
CAT.SERVICE_CATEGORY_DESC


UNION ALL
SELECT

COALESCE(BCG.BUDGET_CATEGORY_GROUP_CODE,'ZZZ') BUDGET_CATEGORY_GROUP_CODE,
BCG.BUDGET_CATEGORY_GROUP_DESC BUDGET_CATEGORY_GROUP_DESC,
COALESCE(BGT_SCH.BUDGET_CATEGORY_CODE,'ZZZ') BUDGET_CATEGORY_CODE,
BC.BUDGET_CATEGORY_DESC,
SUM(D.ACTUAL_COST_IN_HOME_CCY) AMT,
'SUBCONCOST' ITEM_CATEGORY_CODE,
'Subcon Cost'   ITEM_CATEGORY_DESC
FROM TMPTBL_TO_GET_RIGHT_DATE H
  INNER JOIN PJ_COMMITTED_COST_TRACK_TRN_DET D ON H.PROJECT_NO = D.PROJECT_NO AND H.TRN_SEQ_NO = D.TRN_SEQ_NO AND H.NO_OF_ALLOC = D.NO_OF_ALLOC
  LEFT JOIN PJ_EST_OST_HDR PJ ON PJ.PROJECT_NO = H.PROJECT_NO
  LEFT JOIN PJ_EST_OST_SUB_HDR PJSUB ON PJSUB.PROJECT_NO = H.PROJECT_NO
  LEFT JOIN PJ_BUDGET_OST_HDR BGT_H ON H.PROJECT_NO = BGT_H.PROJECT_NO
  LEFT JOIN PJ_BUDGET_OST_SCH BGT_SCH ON BGT_H.BUDGET_NO = BGT_SCH.BUDGET_NO AND D.SCH_SEQ_NO = BGT_SCH.SCH_SEQ_NO
  LEFT JOIN MT_BUDGET_CATEGORY BC ON BC.BUDGET_CATEGORY_CODE = BGT_SCH.BUDGET_CATEGORY_CODE
  LEFT JOIN MT_BUDGET_CATEGORY_GROUP BCG ON BC.BUDGET_CATEGORY_GROUP_CODE =BCG.BUDGET_CATEGORY_GROUP_CODE
  LEFT JOIN PJ_BUDGET_OST_CST_ITM CST ON CST.CST_ITM_SEQ_NO = D.CST_ITM_SEQ_NO AND CST.BUDGET_NO = BGT_SCH.BUDGET_NO AND CST.SCH_SEQ_NO = D.SCH_SEQ_NO
WHERE 1=1
AND CST.DT_TYPE = 'N'
AND H.FINANCIAL_YEAR = $P{FINANCIAL_YEAR} AND H.FINANCIAL_PERIOD <= $P{FINANCIAL_PERIOD}
AND PJ.PROJECT_NO = $P{PROJECT_NO}
GROUP BY BCG.BUDGET_CATEGORY_GROUP_CODE,
BCG.BUDGET_CATEGORY_GROUP_DESC,
BGT_SCH.BUDGET_CATEGORY_CODE,
BC.BUDGET_CATEGORY_DESC

UNION ALL
SELECT

COALESCE(BCG.BUDGET_CATEGORY_GROUP_CODE,'ZZZ') BUDGET_CATEGORY_GROUP_CODE,
BCG.BUDGET_CATEGORY_GROUP_DESC BUDGET_CATEGORY_GROUP_DESC,
COALESCE(BGT_SCH.BUDGET_CATEGORY_CODE,'ZZZ') BUDGET_CATEGORY_CODE,
BC.BUDGET_CATEGORY_DESC,
SUM(D.ACTUAL_COST_IN_HOME_CCY) AMT,
'EMPLOYEECOST' ITEM_CATEGORY_CODE,
'Employee Cost' ITEM_CATEGORY_DESC
FROM PJ_COMMITTED_COST_TRACK_TRN_HDR H
  INNER JOIN PJ_COMMITTED_COST_TRACK_TRN_DET D ON H.PROJECT_NO = D.PROJECT_NO AND H.TRN_SEQ_NO = D.TRN_SEQ_NO AND H.NO_OF_ALLOC = D.NO_OF_ALLOC
  LEFT JOIN PJ_EST_OST_HDR PJ ON PJ.PROJECT_NO = H.PROJECT_NO
  LEFT JOIN PJ_EST_OST_SUB_HDR PJSUB ON PJSUB.PROJECT_NO = H.PROJECT_NO
  LEFT JOIN PJ_BUDGET_OST_HDR BGT_H ON H.PROJECT_NO = BGT_H.PROJECT_NO
  LEFT JOIN PJ_BUDGET_OST_SCH BGT_SCH ON BGT_H.BUDGET_NO = BGT_SCH.BUDGET_NO AND D.SCH_SEQ_NO = BGT_SCH.SCH_SEQ_NO
  LEFT JOIN MT_BUDGET_CATEGORY BC ON BC.BUDGET_CATEGORY_CODE = BGT_SCH.BUDGET_CATEGORY_CODE
  LEFT JOIN MT_BUDGET_CATEGORY_GROUP BCG ON BC.BUDGET_CATEGORY_GROUP_CODE =BCG.BUDGET_CATEGORY_GROUP_CODE
  LEFT JOIN PJ_BUDGET_OST_CST_ITM CST ON CST.CST_ITM_SEQ_NO = D.CST_ITM_SEQ_NO AND CST.BUDGET_NO = BGT_SCH.BUDGET_NO AND CST.SCH_SEQ_NO = D.SCH_SEQ_NO

WHERE 1=1
AND CST.DT_TYPE NOT IN ('N','S','I')
AND H.FINANCIAL_YEAR = $P{FINANCIAL_YEAR} AND H.FINANCIAL_PERIOD <= $P{FINANCIAL_PERIOD}
AND PJ.PROJECT_NO = $P{PROJECT_NO}
GROUP BY BCG.BUDGET_CATEGORY_GROUP_CODE,
BCG.BUDGET_CATEGORY_GROUP_DESC,
BGT_SCH.BUDGET_CATEGORY_CODE,
BC.BUDGET_CATEGORY_DESC

)
SELECT
ACCUM_ACTUAL_COST_TBL.BUDGET_CATEGORY_GROUP_CODE
,ACCUM_ACTUAL_COST_TBL.BUDGET_CATEGORY_GROUP_DESC
,ACCUM_ACTUAL_COST_TBL.BUDGET_CATEGORY_CODE
,ACCUM_ACTUAL_COST_TBL.BUDGET_CATEGORY_DESC
,ACCUM_ACTUAL_COST_TBL.ITEM_CATEGORY_CODE
,ACCUM_ACTUAL_COST_TBL.ITEM_CATEGORY_DESC
,COALESCE(CURRENT_ACTUAL_COST_TBL.AMT,0) CURRENT_ACTUAL_COST_AMT
,COALESCE(ACCUM_ACTUAL_COST_TBL.AMT,0) ACCUM_ACTUAL_COST_AMT
,COALESCE(YTD_ACTUAL_COST_TBL.AMT,0) YTD_ACTUAL_COST_AMT
,(SELECT PERIOD_CLOSING_DATE FROM MT_FINANCIAL_PERIOD
WHERE FINANCIAL_YEAR = $P{FINANCIAL_YEAR}
AND FINANCIAL_PERIOD = $P{FINANCIAL_PERIOD}) AS_AT_DATE
FROM ACCUM_ACTUAL_COST_TBL
LEFT JOIN CURRENT_ACTUAL_COST_TBL ON ACCUM_ACTUAL_COST_TBL.BUDGET_CATEGORY_GROUP_CODE = CURRENT_ACTUAL_COST_TBL.BUDGET_CATEGORY_GROUP_CODE
    AND ACCUM_ACTUAL_COST_TBL.BUDGET_CATEGORY_CODE = CURRENT_ACTUAL_COST_TBL.BUDGET_CATEGORY_CODE
    AND ACCUM_ACTUAL_COST_TBL.ITEM_CATEGORY_CODE = CURRENT_ACTUAL_COST_TBL.ITEM_CATEGORY_CODE
LEFT JOIN YTD_ACTUAL_COST_TBL ON ACCUM_ACTUAL_COST_TBL.BUDGET_CATEGORY_GROUP_CODE = YTD_ACTUAL_COST_TBL.BUDGET_CATEGORY_GROUP_CODE
    AND ACCUM_ACTUAL_COST_TBL.BUDGET_CATEGORY_CODE = YTD_ACTUAL_COST_TBL.BUDGET_CATEGORY_CODE
    AND ACCUM_ACTUAL_COST_TBL.ITEM_CATEGORY_CODE = YTD_ACTUAL_COST_TBL.ITEM_CATEGORY_CODE
WHERE 1=1
AND ACCUM_ACTUAL_COST_TBL.ITEM_CATEGORY_CODE IS NOT NULL

ORDER BY BUDGET_CATEGORY_GROUP_CODE,BUDGET_CATEGORY_CODE,ITEM_CATEGORY_CODE