TH6R_PJ_300047 GROUP BY Master Project No

WITH PJREV AS
(
SELECT H.PROJECT_NO, H.REVISION_NO,'OST' FROMWHERE,H.TOTAL_PRE_TAX_HOME_AMT
FROM PJ_EST_OST_HDR H
LEFT JOIN MT_FINANCIAL_PERIOD FP1 ON DATE(H.VARIATION_DATETIME) BETWEEN FP1.PERIOD_START_DATE AND FP1.PERIOD_CLOSING_DATE
WHERE 1=1
AND (H.VARIATION_DATETIME IS NULL OR ((FP1.FINANCIAL_YEAR*100+FP1.FINANCIAL_PERIOD) <= ($P{FINANCIAL_YEAR}*100+$P{FINANCIAL_PERIOD})))
UNION ALL
SELECT H.PROJECT_NO,HST.REVISION_NO,'HST' FROMWHERE,HST.TOTAL_PRE_TAX_HOME_AMT
FROM PJ_EST_REV_HST_HDR HST
LEFT JOIN PJ_EST_OST_HDR H ON H.PROJECT_NO = HST.PROJECT_NO
LEFT JOIN MT_FINANCIAL_PERIOD FP1 ON DATE(H.VARIATION_DATETIME) BETWEEN FP1.PERIOD_START_DATE AND FP1.PERIOD_CLOSING_DATE
LEFT JOIN
(SELECT MAX(REVISION_NO) REVISION_NO, PROJECT_NO
FROM PJ_EST_REV_HST_HDR HST
LEFT JOIN MT_FINANCIAL_PERIOD FP1 ON (COALESCE(HST.VARIATION_DATETIME, HST.ESTIMATION_DATE) BETWEEN FP1.PERIOD_START_DATE AND FP1.PERIOD_CLOSING_DATE)
WHERE 1=1
AND ((FP1.FINANCIAL_YEAR*100+FP1.FINANCIAL_PERIOD) <= ($P{FINANCIAL_YEAR}*100+$P{FINANCIAL_PERIOD}))
GROUP BY PROJECT_NO
) MAX_REV ON MAX_REV.PROJECT_NO = HST.PROJECT_NO
WHERE 1=1
AND MAX_REV.REVISION_NO = HST.REVISION_NO
AND (H.VARIATION_DATETIME IS NOT NULL AND ((FP1.FINANCIAL_YEAR*100+FP1.FINANCIAL_PERIOD) > ($P{FINANCIAL_YEAR}*100+$P{FINANCIAL_PERIOD})))
),

PJ_EST_OST_HDR_TEMP AS (
SELECT PJ.*, COALESCE(PJ.MASTER_PROJECT_NO, PJ.PROJECT_NO) AS MASTER_PROJECT_NO_
FROM PJ_EST_OST_HDR AS PJ
INNER JOIN PJREV ON PJ.PROJECT_NO = PJREV.PROJECT_NO
WHERE 1 = 1
$P!{__RPT__FILTER__PARAM}
AND (($P{PROJECT_STATUS_OPTION} = 'A')
    OR ($P{PROJECT_STATUS_OPTION} = 'H'
        AND PJ.PROJECT_NO IN (SELECT PROJECT_NO
                                FROM PJ_RCG_HST_HDR
                                WHERE FINAL_RECOG_FLAG = 'Y'
                                AND   (IS_REVERSED_FLAG IS NULL OR IS_REVERSED_FLAG <> 'Y')
                                AND   (YEAR_POSTED_TO*100 + PERIOD_POSTED_TO <= 100*$P{FINANCIAL_YEAR} + $P{FINANCIAL_PERIOD} ))
        )
    OR ($P{PROJECT_STATUS_OPTION} = 'O'
        AND PJ.PROJECT_NO NOT IN (SELECT PROJECT_NO
                                    FROM PJ_RCG_HST_HDR
                                    WHERE FINAL_RECOG_FLAG = 'Y'
                                    AND   (YEAR_POSTED_TO*100 + PERIOD_POSTED_TO <= 100*$P{FINANCIAL_YEAR} + $P{FINANCIAL_PERIOD} )))
)
-- 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 PJ.PROJECT_NO IN (SELECT PROJECT_NO FROM PJ_EST_OST_TEAM_MEMBER WHERE EMPLOYEE_CODE = $P{THIS_EMP})
    )
-- end Security.
),

PJREV2 AS (
SELECT SUM(PJREV.TOTAL_PRE_TAX_HOME_AMT) AS TOTAL_PRE_TAX_HOME_AMT, PEOHT.MASTER_PROJECT_NO_
FROM PJREV
INNER JOIN PJ_EST_OST_HDR_TEMP AS PEOHT ON PJREV.PROJECT_NO = PEOHT.PROJECT_NO
GROUP BY PEOHT.MASTER_PROJECT_NO_
),
BUDGET_CATEGORY_TBL AS(
SELECT
PEOHT.MASTER_PROJECT_NO_,
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
FROM PJ_BUDGET_OST_HDR H
INNER JOIN PJ_EST_OST_HDR_TEMP AS PEOHT ON H.PROJECT_NO = PEOHT.PROJECT_NO
LEFT JOIN PJ_BUDGET_OST_SCH BGT_SCH ON H.BUDGET_NO = BGT_SCH.BUDGET_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
WHERE 1=1
AND BGT_SCH.IS_LEAF = 'Y'
GROUP BY BCG.BUDGET_CATEGORY_GROUP_CODE,
BCG.BUDGET_CATEGORY_GROUP_DESC,
BGT_SCH.BUDGET_CATEGORY_CODE,
BC.BUDGET_CATEGORY_DESC,
PEOHT.MASTER_PROJECT_NO_

UNION
SELECT
PEOHT.MASTER_PROJECT_NO_,
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
FROM PJ_BUDGET_REV_HST_HDR HST
INNER JOIN PJ_EST_OST_HDR_TEMP AS PEOHT ON HST.PROJECT_NO = PEOHT.PROJECT_NO
LEFT JOIN PJ_BUDGET_REV_HST_SCH BGT_SCH ON HST.BUDGET_NO = BGT_SCH.BUDGET_NO AND HST.REVISION_NO = BGT_SCH.REVISION_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
WHERE 1=1
AND BGT_SCH.IS_LEAF = 'Y'
GROUP BY BCG.BUDGET_CATEGORY_GROUP_CODE,
BCG.BUDGET_CATEGORY_GROUP_DESC,
BGT_SCH.BUDGET_CATEGORY_CODE,
BC.BUDGET_CATEGORY_DESC,
PEOHT.MASTER_PROJECT_NO_
),
PJBUDGET_TBL AS
(
/*
display from Budget Category, Budget Category Group level only.
Pj_budget_ost/rev_hst_sch as at date.
Pj_budget_ost_hdr.variation_date is null or <= as at date -> read from ost table
Else, check rev_hst table, get latest revision for variation date as at date or variation date null
*/
SELECT SUM(H.AMT) AS AMT, H.MASTER_PROJECT_NO_, H.BUDGET_CATEGORY_GROUP_CODE, H.BUDGET_CATEGORY_GROUP_DESC, H.BUDGET_CATEGORY_CODE, H.BUDGET_CATEGORY_DESC
FROM (
SELECT
PEOHT.MASTER_PROJECT_NO_,
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(BGT_SCH.BUDGET_COST_IN_HOME_CCY) AMT
FROM PJ_BUDGET_OST_HDR H
INNER JOIN PJ_EST_OST_HDR_TEMP AS PEOHT ON H.PROJECT_NO = PEOHT.PROJECT_NO
LEFT JOIN MT_FINANCIAL_PERIOD FP1 ON DATE(H.VARIATION_DATE) BETWEEN FP1.PERIOD_START_DATE AND FP1.PERIOD_CLOSING_DATE
LEFT JOIN PJ_BUDGET_OST_SCH BGT_SCH ON H.BUDGET_NO = BGT_SCH.BUDGET_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
WHERE 1=1
AND BGT_SCH.IS_LEAF = 'Y'
AND (H.VARIATION_DATETIME IS NULL OR ((FP1.FINANCIAL_YEAR*100+FP1.FINANCIAL_PERIOD) <= 100*$P{FINANCIAL_YEAR} + $P{FINANCIAL_PERIOD} ))
GROUP BY BCG.BUDGET_CATEGORY_GROUP_CODE,
BCG.BUDGET_CATEGORY_GROUP_DESC,
BGT_SCH.BUDGET_CATEGORY_CODE,
BC.BUDGET_CATEGORY_DESC,
PEOHT.MASTER_PROJECT_NO_

UNION ALL

SELECT
PEOHT.MASTER_PROJECT_NO_,
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(BGT_SCH.BUDGET_COST_IN_HOME_CCY) AMT
FROM PJ_BUDGET_REV_HST_HDR HST
LEFT JOIN PJ_BUDGET_OST_HDR H ON H.BUDGET_NO = HST.BUDGET_NO
INNER JOIN PJ_EST_OST_HDR_TEMP AS PEOHT ON H.PROJECT_NO = PEOHT.PROJECT_NO
LEFT JOIN MT_FINANCIAL_PERIOD FP1 ON DATE(H.VARIATION_DATE) BETWEEN FP1.PERIOD_START_DATE AND FP1.PERIOD_CLOSING_DATE
LEFT JOIN
(SELECT MAX(REVISION_NO) REVISION_NO, BUDGET_NO
FROM PJ_BUDGET_REV_HST_HDR HST
LEFT JOIN MT_FINANCIAL_PERIOD FP1 ON HST.VARIATION_DATE BETWEEN FP1.PERIOD_START_DATE AND FP1.PERIOD_CLOSING_DATE
WHERE 1=1
AND (HST.VARIATION_DATE IS NULL OR (FP1.FINANCIAL_YEAR*100+FP1.FINANCIAL_PERIOD) <= (100*$P{FINANCIAL_YEAR} + $P{FINANCIAL_PERIOD} ))
GROUP BY BUDGET_NO
) MAX_REV ON MAX_REV.BUDGET_NO = HST.BUDGET_NO
LEFT JOIN PJ_BUDGET_REV_HST_SCH BGT_SCH ON H.BUDGET_NO = BGT_SCH.BUDGET_NO AND HST.REVISION_NO = BGT_SCH.REVISION_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
WHERE 1=1
AND BGT_SCH.IS_LEAF = 'Y'
AND MAX_REV.REVISION_NO = HST.REVISION_NO
AND (H.VARIATION_DATETIME IS NOT NULL AND ((FP1.FINANCIAL_YEAR*100+FP1.FINANCIAL_PERIOD) > (100*$P{FINANCIAL_YEAR} + $P{FINANCIAL_PERIOD} )))
GROUP BY BCG.BUDGET_CATEGORY_GROUP_CODE,
BCG.BUDGET_CATEGORY_GROUP_DESC,
BGT_SCH.BUDGET_CATEGORY_CODE,
BC.BUDGET_CATEGORY_DESC,
PEOHT.MASTER_PROJECT_NO_
) AS H GROUP BY H.MASTER_PROJECT_NO_, H.BUDGET_CATEGORY_GROUP_CODE, H.BUDGET_CATEGORY_GROUP_DESC, H.BUDGET_CATEGORY_CODE, H.BUDGET_CATEGORY_DESC
),
CURRENT_ACTUAL_COST_TBL AS
(
SELECT SUM(H.AMT) AS AMT, H.MASTER_PROJECT_NO_, H.BUDGET_CATEGORY_GROUP_CODE, H.BUDGET_CATEGORY_GROUP_DESC, H.BUDGET_CATEGORY_CODE, H.BUDGET_CATEGORY_DESC, H.ITEM_CATEGORY_CODE, H.ITEM_CATEGORY_DESC
FROM (
SELECT
PEOHT.MASTER_PROJECT_NO_,
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 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
  INNER JOIN PJ_EST_OST_HDR_TEMP AS PEOHT ON H.PROJECT_NO = PEOHT.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 BGT_SCH.IS_LEAF = 'Y'
AND CST.DT_TYPE = 'I'
AND (100*H.FINANCIAL_YEAR + H.FINANCIAL_PERIOD) = 100*$P{FINANCIAL_YEAR} + $P{FINANCIAL_PERIOD}


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,
PEOHT.MASTER_PROJECT_NO_

UNION ALL
SELECT
PEOHT.MASTER_PROJECT_NO_,
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 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
  INNER JOIN PJ_EST_OST_HDR_TEMP AS PEOHT ON H.PROJECT_NO = PEOHT.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 BGT_SCH.IS_LEAF = 'Y'
AND CST.DT_TYPE = 'S'
AND (100*H.FINANCIAL_YEAR + H.FINANCIAL_PERIOD) = 100*$P{FINANCIAL_YEAR} + $P{FINANCIAL_PERIOD}


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,
PEOHT.MASTER_PROJECT_NO_


UNION ALL
SELECT
PEOHT.MASTER_PROJECT_NO_,
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 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
  INNER JOIN PJ_EST_OST_HDR_TEMP AS PEOHT ON H.PROJECT_NO = PEOHT.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 BGT_SCH.IS_LEAF = 'Y'
AND CST.DT_TYPE = 'N'
AND (100*H.FINANCIAL_YEAR + H.FINANCIAL_PERIOD) = 100*$P{FINANCIAL_YEAR} + $P{FINANCIAL_PERIOD}


GROUP BY BCG.BUDGET_CATEGORY_GROUP_CODE,
BCG.BUDGET_CATEGORY_GROUP_DESC,
BGT_SCH.BUDGET_CATEGORY_CODE,
BC.BUDGET_CATEGORY_DESC,
PEOHT.MASTER_PROJECT_NO_

UNION ALL
SELECT
PEOHT.MASTER_PROJECT_NO_,
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
  INNER JOIN PJ_EST_OST_HDR_TEMP AS PEOHT ON H.PROJECT_NO = PEOHT.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 BGT_SCH.IS_LEAF = 'Y'
AND CST.DT_TYPE NOT IN ('N','S','I')
AND (100*H.FINANCIAL_YEAR + H.FINANCIAL_PERIOD) = 100*$P{FINANCIAL_YEAR} + $P{FINANCIAL_PERIOD}


GROUP BY BCG.BUDGET_CATEGORY_GROUP_CODE,
BCG.BUDGET_CATEGORY_GROUP_DESC,
BGT_SCH.BUDGET_CATEGORY_CODE,
BC.BUDGET_CATEGORY_DESC,
PEOHT.MASTER_PROJECT_NO_
) AS H GROUP BY H.MASTER_PROJECT_NO_, H.BUDGET_CATEGORY_GROUP_CODE, H.BUDGET_CATEGORY_GROUP_DESC, H.BUDGET_CATEGORY_CODE, H.BUDGET_CATEGORY_DESC, H.ITEM_CATEGORY_CODE, H.ITEM_CATEGORY_DESC

),
ACCUM_ACTUAL_COST_TBL AS
(
SELECT SUM(H.AMT) AS AMT, H.MASTER_PROJECT_NO_, H.BUDGET_CATEGORY_GROUP_CODE, H.BUDGET_CATEGORY_GROUP_DESC, H.BUDGET_CATEGORY_CODE, H.BUDGET_CATEGORY_DESC, H.ITEM_CATEGORY_CODE, H.ITEM_CATEGORY_DESC
FROM (
SELECT
PEOHT.MASTER_PROJECT_NO_,
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 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
  INNER JOIN PJ_EST_OST_HDR_TEMP AS PEOHT ON H.PROJECT_NO = PEOHT.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 BGT_SCH.IS_LEAF = 'Y'
AND CST.DT_TYPE = 'I'
AND (100*H.FINANCIAL_YEAR + H.FINANCIAL_PERIOD) <= 100*$P{FINANCIAL_YEAR} + $P{FINANCIAL_PERIOD}


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,
PEOHT.MASTER_PROJECT_NO_

UNION ALL
SELECT
PEOHT.MASTER_PROJECT_NO_,
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 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
  INNER JOIN PJ_EST_OST_HDR_TEMP AS PEOHT ON H.PROJECT_NO = PEOHT.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 BGT_SCH.IS_LEAF = 'Y'
AND CST.DT_TYPE = 'S'
AND (100*H.FINANCIAL_YEAR + H.FINANCIAL_PERIOD) <= 100*$P{FINANCIAL_YEAR} + $P{FINANCIAL_PERIOD}


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,
PEOHT.MASTER_PROJECT_NO_


UNION ALL
SELECT
PEOHT.MASTER_PROJECT_NO_,
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 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
  INNER JOIN PJ_EST_OST_HDR_TEMP AS PEOHT ON H.PROJECT_NO = PEOHT.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 BGT_SCH.IS_LEAF = 'Y'
AND CST.DT_TYPE = 'N'
AND (100*H.FINANCIAL_YEAR + H.FINANCIAL_PERIOD) <= 100*$P{FINANCIAL_YEAR} + $P{FINANCIAL_PERIOD}


GROUP BY BCG.BUDGET_CATEGORY_GROUP_CODE,
BCG.BUDGET_CATEGORY_GROUP_DESC,
BGT_SCH.BUDGET_CATEGORY_CODE,
BC.BUDGET_CATEGORY_DESC,
PEOHT.MASTER_PROJECT_NO_

UNION ALL
SELECT
PEOHT.MASTER_PROJECT_NO_,
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
  INNER JOIN PJ_EST_OST_HDR_TEMP AS PEOHT ON H.PROJECT_NO = PEOHT.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 BGT_SCH.IS_LEAF = 'Y'
AND CST.DT_TYPE NOT IN ('N','S','I')
AND (100*H.FINANCIAL_YEAR + H.FINANCIAL_PERIOD) <= 100*$P{FINANCIAL_YEAR} + $P{FINANCIAL_PERIOD}


GROUP BY BCG.BUDGET_CATEGORY_GROUP_CODE,
BCG.BUDGET_CATEGORY_GROUP_DESC,
BGT_SCH.BUDGET_CATEGORY_CODE,
BC.BUDGET_CATEGORY_DESC,
PEOHT.MASTER_PROJECT_NO_
) AS H GROUP BY H.MASTER_PROJECT_NO_, H.BUDGET_CATEGORY_GROUP_CODE, H.BUDGET_CATEGORY_GROUP_DESC, H.BUDGET_CATEGORY_CODE, H.BUDGET_CATEGORY_DESC, H.ITEM_CATEGORY_CODE, H.ITEM_CATEGORY_DESC

),
CURRENT_RECOG_COST_TBL AS
(
SELECT
PEOHT.MASTER_PROJECT_NO_,
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.COST_HOME_TO_RECOG) AMT
FROM PJ_RCG_HST_HDR H
INNER JOIN PJ_EST_OST_HDR_TEMP AS PEOHT ON H.PROJECT_NO = PEOHT.PROJECT_NO
LEFT JOIN PJ_RCG_HST_SCH D ON H.PROJECT_NO = D.PROJECT_NO AND H.RECOGNITION_NO = D.RECOGNITION_NO
LEFT JOIN PJ_BUDGET_OST_SCH BGT_SCH ON D.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_EST_OST_SUB_HDR PJSUB ON PJSUB.PROJECT_NO = H.PROJECT_NO
WHERE 1=1
AND BGT_SCH.IS_LEAF = 'Y'
AND (100*H.YEAR_POSTED_TO + H.PERIOD_POSTED_TO) = 100*$P{FINANCIAL_YEAR} + $P{FINANCIAL_PERIOD}
AND (H.IS_CANCELLED IS NULL OR H.IS_CANCELLED = 'N')


GROUP BY BCG.BUDGET_CATEGORY_GROUP_CODE,
BCG.BUDGET_CATEGORY_GROUP_DESC,
BGT_SCH.BUDGET_CATEGORY_CODE,
BC.BUDGET_CATEGORY_DESC,
PEOHT.MASTER_PROJECT_NO_
),

ACCUM_RECOG_COST_TBL AS
(
SELECT
PEOHT.MASTER_PROJECT_NO_,
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.COST_HOME_TO_RECOG) AMT
FROM PJ_RCG_HST_HDR H
INNER JOIN PJ_EST_OST_HDR_TEMP AS PEOHT ON H.PROJECT_NO = PEOHT.PROJECT_NO
LEFT JOIN PJ_RCG_HST_SCH D ON H.PROJECT_NO = D.PROJECT_NO AND H.RECOGNITION_NO = D.RECOGNITION_NO
LEFT JOIN PJ_BUDGET_OST_SCH BGT_SCH ON D.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_EST_OST_SUB_HDR PJSUB ON PJSUB.PROJECT_NO = H.PROJECT_NO
WHERE 1=1
AND BGT_SCH.IS_LEAF = 'Y'
AND (100*H.YEAR_POSTED_TO + H.PERIOD_POSTED_TO) <=100*$P{FINANCIAL_YEAR} + $P{FINANCIAL_PERIOD}
AND (H.IS_CANCELLED IS NULL OR H.IS_CANCELLED = 'N')


GROUP BY BCG.BUDGET_CATEGORY_GROUP_CODE,
BCG.BUDGET_CATEGORY_GROUP_DESC,
BGT_SCH.BUDGET_CATEGORY_CODE,
BC.BUDGET_CATEGORY_DESC,
PEOHT.MASTER_PROJECT_NO_
),
CURRENT_RECOG_COST_PJ_TBL AS
(
SELECT
PEOHT.MASTER_PROJECT_NO_,
SUM(H.COST_HOME_TO_RECOG) AMT
FROM PJ_RCG_HST_HDR H
INNER JOIN PJ_EST_OST_HDR_TEMP AS PEOHT ON H.PROJECT_NO = PEOHT.PROJECT_NO
LEFT JOIN PJ_EST_OST_SUB_HDR PJSUB ON PJSUB.PROJECT_NO = H.PROJECT_NO
WHERE 1=1
AND (100*H.YEAR_POSTED_TO + H.PERIOD_POSTED_TO) = 100*$P{FINANCIAL_YEAR} + $P{FINANCIAL_PERIOD}
AND (H.IS_CANCELLED IS NULL OR H.IS_CANCELLED = 'N')


GROUP BY PEOHT.MASTER_PROJECT_NO_
),

ACCUM_RECOG_COST_PJ_TBL AS
(
SELECT
PEOHT.MASTER_PROJECT_NO_,
SUM(H.COST_HOME_TO_RECOG) AMT
FROM PJ_RCG_HST_HDR H
INNER JOIN PJ_EST_OST_HDR_TEMP AS PEOHT ON H.PROJECT_NO = PEOHT.PROJECT_NO
LEFT JOIN PJ_EST_OST_SUB_HDR PJSUB ON PJSUB.PROJECT_NO = H.PROJECT_NO
WHERE 1=1
AND (100*H.YEAR_POSTED_TO + H.PERIOD_POSTED_TO) <=100*$P{FINANCIAL_YEAR} + $P{FINANCIAL_PERIOD}
AND (H.IS_CANCELLED IS NULL OR H.IS_CANCELLED = 'N')


GROUP BY PEOHT.MASTER_PROJECT_NO_
)
SELECT
PJ.MASTER_PROJECT_NO_ AS MASTER_PROJECT_NO
,COALESCE(PJREV2.TOTAL_PRE_TAX_HOME_AMT,0) ACCUM_BUDGETED_REVENUE
--BILLING SALES
,COALESCE(
    (SELECT SUM(BD.NETT_INV_PRE_TAX_HOME_AMT) AMT
    FROM PJ_EST_OST_CST_BRKDWN BD
    INNER JOIN PJ_EST_OST_HDR_TEMP AS PEOHT ON BD.PROJECT_NO = PEOHT.PROJECT_NO
    WHERE PEOHT.MASTER_PROJECT_NO_ = PJ.MASTER_PROJECT_NO_
    AND BD.YEAR_POSTED_TO*100 + BD.PERIOD_POSTED_TO = 100*$P{FINANCIAL_YEAR} + $P{FINANCIAL_PERIOD}
    )
,0) BS_CURRENT_ACTUAL_REVENUE
,COALESCE(
    (SELECT SUM(BD.NETT_INV_PRE_TAX_HOME_AMT) AMT
    FROM PJ_EST_OST_CST_BRKDWN BD
    INNER JOIN PJ_EST_OST_HDR_TEMP AS PEOHT ON BD.PROJECT_NO = PEOHT.PROJECT_NO
    WHERE PEOHT.MASTER_PROJECT_NO_ = PJ.MASTER_PROJECT_NO_
    AND BD.YEAR_POSTED_TO*100 + BD.PERIOD_POSTED_TO <= 100*$P{FINANCIAL_YEAR} + $P{FINANCIAL_PERIOD}
    )
,0) BS_ACCUM_ACTUAL_REVENUE
,COALESCE(
    (SELECT SUM(BD.RECOG_INV_PRE_TAX_HOME_AMT) AMT
    FROM PJ_EST_OST_CST_BRKDWN BD
    INNER JOIN PJ_EST_OST_HDR_TEMP AS PEOHT ON BD.PROJECT_NO = PEOHT.PROJECT_NO
    WHERE PEOHT.MASTER_PROJECT_NO_ = PJ.MASTER_PROJECT_NO_
    AND BD.YEAR_POSTED_TO*100 + BD.PERIOD_POSTED_TO = 100*$P{FINANCIAL_YEAR} + $P{FINANCIAL_PERIOD}
    )
,0) BS_CURRENT_RECOG_REVENUE
,COALESCE(
    (SELECT SUM(BD.RECOG_INV_PRE_TAX_HOME_AMT) AMT
    FROM PJ_EST_OST_CST_BRKDWN BD
    INNER JOIN PJ_EST_OST_HDR_TEMP AS PEOHT ON BD.PROJECT_NO = PEOHT.PROJECT_NO
    WHERE PEOHT.MASTER_PROJECT_NO_ = PJ.MASTER_PROJECT_NO_
    AND BD.YEAR_POSTED_TO*100 + BD.PERIOD_POSTED_TO <= 100*$P{FINANCIAL_YEAR} + $P{FINANCIAL_PERIOD}
    )
,0) BS_ACCUM_RECOG_REVENUE
--REVENUE ACCRUAL
,COALESCE(
    (SELECT SUM(BD.TOTAL_REVENUE_ACCRUAL_HOME_AMT) AMT
    FROM PJ_EST_OST_CST_BRKDWN BD
    INNER JOIN PJ_EST_OST_HDR_TEMP AS PEOHT ON BD.PROJECT_NO = PEOHT.PROJECT_NO
    WHERE PEOHT.MASTER_PROJECT_NO_ = PJ.MASTER_PROJECT_NO_
    AND BD.YEAR_POSTED_TO*100 + BD.PERIOD_POSTED_TO = 100*$P{FINANCIAL_YEAR} + $P{FINANCIAL_PERIOD}
    )
,0) RA_CURRENT_ACTUAL_REVENUE
,COALESCE(
    (SELECT SUM(BD.TOTAL_REVENUE_ACCRUAL_HOME_AMT) AMT
    FROM PJ_EST_OST_CST_BRKDWN BD
    INNER JOIN PJ_EST_OST_HDR_TEMP AS PEOHT ON BD.PROJECT_NO = PEOHT.PROJECT_NO
    WHERE PEOHT.MASTER_PROJECT_NO_ = PJ.MASTER_PROJECT_NO_
    AND BD.YEAR_POSTED_TO*100 + BD.PERIOD_POSTED_TO <= 100*$P{FINANCIAL_YEAR} + $P{FINANCIAL_PERIOD}
    )
,0) RA_ACCUM_ACTUAL_REVENUE
,BUDGET_CATEGORY_TBL.BUDGET_CATEGORY_GROUP_CODE
,BUDGET_CATEGORY_TBL.BUDGET_CATEGORY_GROUP_DESC
,BUDGET_CATEGORY_TBL.BUDGET_CATEGORY_CODE
,BUDGET_CATEGORY_TBL.BUDGET_CATEGORY_DESC
,COALESCE(CURRENT_RECOG_COST_PJ_TBL.AMT,0) CURRENT_RECOG_COST_PJ_AMT --AT PJ LEVEL
,COALESCE(ACCUM_RECOG_COST_PJ_TBL.AMT,0) ACCUM_RECOG_COST_PJ_AMT --AT PJ LEVEL
,COALESCE(PJBUDGET_TBL.AMT,0) PJBUDGET_AMT --AT BUDGET CAT LEVEL
,COALESCE(CURRENT_RECOG_COST_TBL.AMT,0) CURRENT_RECOG_COST_AMT --AT BUDGET CAT LEVEL
,COALESCE(ACCUM_RECOG_COST_TBL.AMT,0) ACCUM_RECOG_COST_AMT --AT BUDGET CAT LEVEL
,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 --AT ITEM CAT LEVEL
,COALESCE(ACCUM_ACTUAL_COST_TBL.AMT,0) ACCUM_ACTUAL_COST_AMT --AT ITEM CAT LEVEL
FROM
(SELECT DISTINCT MASTER_PROJECT_NO_ FROM PJ_EST_OST_HDR_TEMP) AS PJ
JOIN PJREV2 ON PJ.MASTER_PROJECT_NO_ = PJREV2.MASTER_PROJECT_NO_
LEFT JOIN BUDGET_CATEGORY_TBL ON BUDGET_CATEGORY_TBL.MASTER_PROJECT_NO_ = PJ.MASTER_PROJECT_NO_
LEFT JOIN PJBUDGET_TBL ON PJBUDGET_TBL.MASTER_PROJECT_NO_ = PJ.MASTER_PROJECT_NO_ AND PJBUDGET_TBL.BUDGET_CATEGORY_CODE = BUDGET_CATEGORY_TBL.BUDGET_CATEGORY_CODE AND PJBUDGET_TBL.BUDGET_CATEGORY_GROUP_CODE = BUDGET_CATEGORY_TBL.BUDGET_CATEGORY_GROUP_CODE
LEFT JOIN ACCUM_ACTUAL_COST_TBL ON ACCUM_ACTUAL_COST_TBL.MASTER_PROJECT_NO_ = PJ.MASTER_PROJECT_NO_
    AND ACCUM_ACTUAL_COST_TBL.BUDGET_CATEGORY_GROUP_CODE = BUDGET_CATEGORY_TBL.BUDGET_CATEGORY_GROUP_CODE
    AND ACCUM_ACTUAL_COST_TBL.BUDGET_CATEGORY_CODE = BUDGET_CATEGORY_TBL.BUDGET_CATEGORY_CODE
LEFT JOIN CURRENT_ACTUAL_COST_TBL ON CURRENT_ACTUAL_COST_TBL.MASTER_PROJECT_NO_ = PJ.MASTER_PROJECT_NO_
    AND 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 CURRENT_RECOG_COST_TBL ON CURRENT_RECOG_COST_TBL.MASTER_PROJECT_NO_ = PJ.MASTER_PROJECT_NO_
    AND BUDGET_CATEGORY_TBL.BUDGET_CATEGORY_GROUP_CODE = CURRENT_RECOG_COST_TBL.BUDGET_CATEGORY_GROUP_CODE
    AND BUDGET_CATEGORY_TBL.BUDGET_CATEGORY_CODE = CURRENT_RECOG_COST_TBL.BUDGET_CATEGORY_CODE
LEFT JOIN ACCUM_RECOG_COST_TBL ON ACCUM_RECOG_COST_TBL.MASTER_PROJECT_NO_ = PJ.MASTER_PROJECT_NO_
    AND BUDGET_CATEGORY_TBL.BUDGET_CATEGORY_GROUP_CODE = ACCUM_RECOG_COST_TBL.BUDGET_CATEGORY_GROUP_CODE
    AND BUDGET_CATEGORY_TBL.BUDGET_CATEGORY_CODE = ACCUM_RECOG_COST_TBL.BUDGET_CATEGORY_CODE
LEFT JOIN CURRENT_RECOG_COST_PJ_TBL ON CURRENT_RECOG_COST_PJ_TBL.MASTER_PROJECT_NO_ = PJ.MASTER_PROJECT_NO_
LEFT JOIN ACCUM_RECOG_COST_PJ_TBL ON ACCUM_RECOG_COST_PJ_TBL.MASTER_PROJECT_NO_ = PJ.MASTER_PROJECT_NO_
WHERE 1=1
--AND (COALESCE(PJBUDGET_TBL.AMT,0) <>0 OR COALESCE(ACCUM_RECOG_COST_PJ_TBL.AMT,0) <>0 OR COALESCE(ACCUM_ACTUAL_COST_TBL.AMT,0)<>0 )

ORDER BY MASTER_PROJECT_NO,BUDGET_CATEGORY_GROUP_CODE,BUDGET_CATEGORY_CODE