4307

SELECT PJHDR.PROJECT_NO,
PJHDR.MASTER_PROJECT_NO,
       PJHDR.SUBJECT,
       COALESCE(ST.PROJECT_STATUS_DESC,'On-Going') STATUS,
       PJHDR.PROJECT_START_DATE,
       PJHDR.PROJECT_END_DATE,
       T.AMT,
       T.AMT_TYPE,T.IS_BOLD,
       T.ROW1,
       T.ROW2,
       T.ROW3
FROM PJ_EST_OST_HDR PJHDR
LEFT JOIN PJ_EST_OST_SUB_HDR RCG ON RCG.PROJECT_NO = PJHDR.PROJECT_NO
LEFT JOIN MT_PROJECT_STATUS ST ON ST.PROJECT_STATUS_CODE = RCG.PROJECT_STATUS_CODE
LEFT JOIN (
    --Original Project Sum
    SELECT
    HDR.PROJECT_NO,
    1 AS ROW1,
    'AMT' AS AMT_TYPE,'N' AS IS_BOLD,
    1 AS ROW2,
    'Original Project Sum' AS ROW3,
    HDR.ORIGINAL_CONTRACT_HOME_AMT AS AMT
    FROM PJ_EST_OST_HDR HDR

    --Variation Order as at Year (YYYY)
    UNION ALL
    SELECT
    HDR.PROJECT_NO,
    1 AS ROW1,
    'AMT' AS AMT_TYPE,'N' AS IS_BOLD,
    2 AS ROW2,
    'Variation Order as at Year ('||$P!{FINANCIAL_YEAR}||')' AS ROW3,
    HDR.VO_HOME_AMT AS AMT
    FROM PJ_EST_OST_HDR HDR
    WHERE YEAR(HDR.VARIATION_DATETIME) <= $P{FINANCIAL_YEAR}
    UNION ALL
    SELECT
    HDR.PROJECT_NO,
    1 AS ROW1,
    'AMT' AS AMT_TYPE,'N' AS IS_BOLD,
    2 AS ROW2,
    'Variation Order as at Year ('||$P!{FINANCIAL_YEAR}||')' AS ROW3,
    COALESCE((SELECT VO_HOME_AMT FROM PJ_EST_REV_HST_HDR REV WHERE REV.PROJECT_NO = HDR.PROJECT_NO AND YEAR(REV.VARIATION_DATETIME) = $P{FINANCIAL_YEAR} ORDER BY REVISION_NO fetch FIRST 1 ROW ONLY),
             (SELECT VO_HOME_AMT FROM PJ_EST_REV_HST_HDR REV WHERE REV.PROJECT_NO = HDR.PROJECT_NO AND REVISION_NO = 0),0
            ) AMT
    FROM PJ_EST_OST_HDR HDR
    WHERE (YEAR(HDR.VARIATION_DATETIME) > $P{FINANCIAL_YEAR} OR HDR.VARIATION_DATETIME IS NULL)

    UNION ALL
    --Additional VO after Year (YYYY)
    SELECT
    HDR.PROJECT_NO,
    1 AS ROW1,
    'AMT' AS AMT_TYPE,'N' AS IS_BOLD,
    3 AS ROW2,
    'Additional VO after Year ('||$P!{FINANCIAL_YEAR}||')' AS ROW3,
    HDR.TOTAL_PRE_TAX_HOME_AMT- HDR.VO_HOME_AMT - HDR.ORIGINAL_CONTRACT_HOME_AMT AS AMT
    FROM PJ_EST_OST_HDR HDR
    WHERE YEAR(HDR.VARIATION_DATETIME) <= $P{FINANCIAL_YEAR}
    UNION ALL
    SELECT
    HDR.PROJECT_NO,
    1 AS ROW1,
    'AMT' AS AMT_TYPE,'N' AS IS_BOLD,
    3 AS ROW2,
    'Additional VO after Year ('||$P!{FINANCIAL_YEAR}||')' AS ROW3,
    HDR.TOTAL_PRE_TAX_HOME_AMT - COALESCE((SELECT VO_HOME_AMT FROM PJ_EST_REV_HST_HDR REV WHERE REV.PROJECT_NO = HDR.PROJECT_NO AND YEAR(REV.VARIATION_DATETIME) = $P{FINANCIAL_YEAR} ORDER BY REVISION_NO fetch FIRST 1 ROW ONLY),
             (SELECT VO_HOME_AMT FROM PJ_EST_REV_HST_HDR REV WHERE REV.PROJECT_NO = HDR.PROJECT_NO AND REVISION_NO = 0),0
            ) - HDR.ORIGINAL_CONTRACT_HOME_AMT AS AMT
    FROM PJ_EST_OST_HDR HDR
    WHERE (YEAR(HDR.VARIATION_DATETIME) > $P{FINANCIAL_YEAR} OR HDR.VARIATION_DATETIME IS NULL)
    -- Adjusted Project Sum
    UNION ALL
    SELECT
    HDR.PROJECT_NO,
    1 AS ROW1,
    'AMT' AS AMT_TYPE,'Y' AS IS_BOLD,
    5 AS ROW2,
    'Adjusted Project Sum' AS ROW3,
    HDR.TOTAL_PRE_TAX_HOME_AMT AS AMT
    FROM PJ_EST_OST_HDR HDR
    --Estimated Cost
    UNION ALL
    SELECT
    HDR.PROJECT_NO,
    1 AS ROW1,
    'AMT' AS AMT_TYPE,'Y' AS IS_BOLD,
    6 AS ROW2,
    'Estimated Cost' AS ROW3,
    HDR.EST_TOTAL_COST_HOME AS AMT
    FROM PJ_EST_OST_HDR HDR
    --Estimated Profit
    UNION ALL
    SELECT
    HDR.PROJECT_NO,
    2 AS ROW1,
    'AMT' AS AMT_TYPE,'N' AS IS_BOLD,
    7 AS ROW2,
    'Estimated Profit' AS ROW3,
    (HDR.TOTAL_PRE_TAX_HOME_AMT - HDR.EST_TOTAL_COST_HOME) AS AMT
    FROM PJ_EST_OST_HDR HDR
    --GP Margin
    UNION ALL
    SELECT
    HDR.PROJECT_NO,
    2 AS ROW1,
    'PERCENT' AS AMT_TYPE,'N' AS IS_BOLD,
    8 AS ROW2,
    'GP Margin' AS ROW3,
    CASE WHEN COALESCE(HDR.TOTAL_PRE_TAX_HOME_AMT,0) = 0 THEN 0.0
        ELSE ((COALESCE(HDR.TOTAL_PRE_TAX_HOME_AMT,0) - COALESCE(HDR.EST_TOTAL_COST_HOME,0))/COALESCE(HDR.TOTAL_PRE_TAX_HOME_AMT,0)) END AS AMT
    FROM PJ_EST_OST_HDR HDR
    --Actual Cost Incurred
        --label
    UNION ALL
    SELECT
    HDR.PROJECT_NO,
    3 AS ROW1,
    'HIDE' AS AMT_TYPE,'Y' AS IS_BOLD,
    9 AS ROW2,
    'Actual Cost Incurred' AS ROW3,
    0.0 AS AMT
    FROM PJ_EST_OST_HDR HDR
        --Cost Accumulated to Year (YYYY-1)
    UNION ALL
    SELECT
    HDR.PROJECT_NO,
    3 AS ROW1,
    'AMT' AS AMT_TYPE,'N' AS IS_BOLD,
    10 AS ROW2,
    'Cost Accumulated to Year ('||($P{FINANCIAL_YEAR}-1)||')' AS ROW3,
    SUM(BRK.total_actual_cost_home) AS AMT
    FROM PJ_EST_OST_HDR HDR
    LEFT JOIN Pj_est_ost_cst_brkdwn BRK ON BRK.PROJECT_NO = HDR.PROJECT_NO AND BRK.YEAR_POSTED_TO < $P{FINANCIAL_YEAR}
    WHERE 1=1

    GROUP BY HDR.PROJECT_NO
        --Cost of the Year (YYYY)
    UNION ALL
    SELECT
    HDR.PROJECT_NO,
    3 AS ROW1,
    'AMT' AS AMT_TYPE,'N' AS IS_BOLD,
    11 AS ROW2,
    'Cost of the Year ('||($P!{FINANCIAL_YEAR})||')' AS ROW3,
    SUM(BRK.total_actual_cost_home) AS AMT
    FROM PJ_EST_OST_HDR HDR
    LEFT JOIN Pj_est_ost_cst_brkdwn BRK ON BRK.PROJECT_NO = HDR.PROJECT_NO AND BRK.YEAR_POSTED_TO = $P{FINANCIAL_YEAR}
    WHERE 1=1

    GROUP BY HDR.PROJECT_NO
        --Cost of Year (YYYY+1) To-Date
    UNION ALL
    SELECT
    HDR.PROJECT_NO,
    3 AS ROW1,
    'AMT' AS AMT_TYPE,'N' AS IS_BOLD,
    12 AS ROW2,
    'Cost of Year ('||($P{FINANCIAL_YEAR}+1)||') To-Date' AS ROW3,
    SUM(BRK.total_actual_cost_home) AS AMT
    FROM PJ_EST_OST_HDR HDR
    LEFT JOIN Pj_est_ost_cst_brkdwn BRK ON BRK.PROJECT_NO = HDR.PROJECT_NO AND BRK.YEAR_POSTED_TO > $P{FINANCIAL_YEAR}
    WHERE 1=1

    GROUP BY HDR.PROJECT_NO
        --Total Acutal Cost Incurred To-Date
    UNION ALL
    SELECT
    HDR.PROJECT_NO,
    3 AS ROW1,
    'AMT' AS AMT_TYPE,'Y' AS IS_BOLD,
    13 AS ROW2,
    'Total Acutal Cost Incurred To-Date' AS ROW3,
    SUM(BRK.TOTAL_ACTUAL_COST_HOME) AS AMT
    FROM PJ_EST_OST_HDR HDR
    LEFT JOIN PJ_EST_OST_CST_BRKDWN BRK ON BRK.PROJECT_NO = HDR.PROJECT_NO
    WHERE 1=1
    GROUP BY HDR.PROJECT_NO
    --End of Actual Cost Incurred

    --Cost to Complete
    UNION ALL
    SELECT
    HDR.PROJECT_NO,
    3 AS ROW1,
    'AMT' AS AMT_TYPE,'Y' AS IS_BOLD,
    14 AS ROW2,
    'Cost to Complete' AS ROW3,
    -SUM(BRK.TOTAL_ACTUAL_COST_HOME) AS AMT
    FROM PJ_EST_OST_HDR HDR
    LEFT JOIN PJ_EST_OST_CST_BRKDWN BRK ON BRK.PROJECT_NO = HDR.PROJECT_NO
    WHERE 1=1
    GROUP BY HDR.PROJECT_NO
    UNION ALL
    SELECT
    HDR.PROJECT_NO,
    3 AS ROW1,
    'AMT' AS AMT_TYPE,'Y' AS IS_BOLD,
    14 AS ROW2,
    'Cost to Complete' AS ROW3,
    HDR.EST_TOTAL_COST_HOME AS AMT
    FROM PJ_EST_OST_HDR HDR

    --Stage of Completion
    UNION ALL
    SELECT
    HDR.PROJECT_NO,
    3 AS ROW1,
    'PERCENT' AS AMT_TYPE,'Y' AS IS_BOLD,
    15 AS ROW2,
    'Stage of Completion' AS ROW3,
    CASE WHEN COALESCE(HDR.EST_TOTAL_COST_HOME,0)=0 THEN 0.0 ELSE COALESCE(BRK.AMT,0)/HDR.EST_TOTAL_COST_HOME END AS AMT
    FROM PJ_EST_OST_HDR HDR
    LEFT JOIN (SELECT PROJECT_NO,SUM(TOTAL_ACTUAL_COST_HOME) AS AMT FROM PJ_EST_OST_CST_BRKDWN GROUP BY PROJECT_NO) BRK ON BRK.PROJECT_NO = HDR.PROJECT_NO

    --Work Value Completed
    UNION ALL
    SELECT
    HDR.PROJECT_NO,
    3 AS ROW1,
    'AMT' AS AMT_TYPE,'Y' AS IS_BOLD,
    16 AS ROW2,
    'Work Value Completed' AS ROW3,
    CASE WHEN COALESCE(HDR.EST_TOTAL_COST_HOME,0)=0 THEN 0.0 ELSE HDR.TOTAL_PRE_TAX_HOME_AMT*COALESCE(BRK.AMT,0)/HDR.EST_TOTAL_COST_HOME END AS AMT
    FROM PJ_EST_OST_HDR HDR
    LEFT JOIN (SELECT PROJECT_NO,SUM(TOTAL_ACTUAL_COST_HOME) AS AMT FROM PJ_EST_OST_CST_BRKDWN GROUP BY PROJECT_NO) BRK ON BRK.PROJECT_NO = HDR.PROJECT_NO
    -- Progress Billing {
    --label
    UNION ALL
    SELECT
    HDR.PROJECT_NO,
    4 AS ROW1,
    'HIDE' AS AMT_TYPE,'Y' AS IS_BOLD,
    17 AS ROW2,
    'Progress Billing' AS ROW3,
    0.0 AS AMT
    FROM PJ_EST_OST_HDR HDR

    --Billing Accumulated to Year (YYYY - 1)
    UNION ALL
    SELECT
    HDR.PROJECT_NO,
    4 AS ROW1,
    'AMT' AS AMT_TYPE,'N' AS IS_BOLD,
    18 AS ROW2,
    'Billing Accumulated to Year ('||($P{FINANCIAL_YEAR}-1)||')' AS ROW3,
    SUM(BRK.nett_inv_pre_tax_home_amt) AS AMT
    FROM PJ_EST_OST_HDR HDR
    LEFT JOIN Pj_est_ost_cst_brkdwn BRK ON BRK.PROJECT_NO = HDR.PROJECT_NO AND BRK.YEAR_POSTED_TO < $P{FINANCIAL_YEAR}
    WHERE 1=1
    GROUP BY HDR.PROJECT_NO
        --sub part: +invoice-credit note{
    UNION ALL
    SELECT
    HDR.PROJECT_NO,
    4 AS ROW1,
    'AMT' AS AMT_TYPE,'N' AS IS_BOLD,
    18 AS ROW2,
    'Billing Accumulated to Year ('||($P{FINANCIAL_YEAR}-1)||')' AS ROW3,
    SUM(INV.total_pre_tax_home_amt) AS AMT
    FROM PJ_EST_OST_HDR HDR
    LEFT JOIN pj_ar_inv_hst_hdr INV ON INV.PROJECT_NO = HDR.PROJECT_NO AND INV.YEAR_POSTED_TO < $P{FINANCIAL_YEAR} AND INV.INVOICE_TYPE = 'R'
    WHERE 1=1
    GROUP BY HDR.PROJECT_NO
    UNION ALL
    SELECT
    HDR.PROJECT_NO,
    4 AS ROW1,
    'AMT' AS AMT_TYPE,'N' AS IS_BOLD,
    18 AS ROW2,
    'Billing Accumulated to Year ('||($P{FINANCIAL_YEAR}-1)||')' AS ROW3,
    -SUM(CRN.total_pre_tax_home_amt) AS AMT
    FROM PJ_EST_OST_HDR HDR
    LEFT JOIN PJ_AR_CRN_HST_HDR CRN ON CRN.PROJECT_NO = HDR.PROJECT_NO AND CRN.YEAR_POSTED_TO < $P{FINANCIAL_YEAR} AND CRN.CREDIT_NOTE_TYPE = 'R'
    WHERE 1=1
    GROUP BY HDR.PROJECT_NO
    UNION ALL
    SELECT
    HDR.PROJECT_NO,
    4 AS ROW1,
    'AMT' AS AMT_TYPE,'N' AS IS_BOLD,
    18 AS ROW2,
    'Billing Accumulated to Year ('||($P{FINANCIAL_YEAR}-1)||')' AS ROW3,
    -SUM(INV.RETENTION_HOME_AMT) AS AMT
    FROM PJ_EST_OST_HDR HDR
    LEFT JOIN pj_ar_inv_hst_hdr INV ON INV.PROJECT_NO = HDR.PROJECT_NO AND INV.YEAR_POSTED_TO < $P{FINANCIAL_YEAR}
    WHERE 1=1
    GROUP BY HDR.PROJECT_NO
    UNION ALL
    SELECT
    HDR.PROJECT_NO,
    4 AS ROW1,
    'AMT' AS AMT_TYPE,'N' AS IS_BOLD,
    18 AS ROW2,
    'Billing Accumulated to Year ('||($P{FINANCIAL_YEAR}-1)||')' AS ROW3,
    SUM(CRN.RETENTION_HOME_AMT) AS AMT
    FROM PJ_EST_OST_HDR HDR
    LEFT JOIN PJ_AR_CRN_HST_HDR CRN ON CRN.PROJECT_NO = HDR.PROJECT_NO AND CRN.YEAR_POSTED_TO < $P{FINANCIAL_YEAR}
    WHERE 1=1
    GROUP BY HDR.PROJECT_NO
        --end sub part: +invoice-credit note}
    --Billing of the Year (YYYY)
    UNION ALL
    SELECT
    HDR.PROJECT_NO,
    4 AS ROW1,
    'AMT' AS AMT_TYPE,'N' AS IS_BOLD,
    19 AS ROW2,
    'Billing of the Year ('||($P{FINANCIAL_YEAR})||')' AS ROW3,
    SUM(BRK.nett_inv_pre_tax_home_amt) AS AMT
    FROM PJ_EST_OST_HDR HDR
    LEFT JOIN Pj_est_ost_cst_brkdwn BRK ON BRK.PROJECT_NO = HDR.PROJECT_NO AND BRK.YEAR_POSTED_TO = $P{FINANCIAL_YEAR}
    WHERE 1=1
    GROUP BY HDR.PROJECT_NO
        --sub part: +invoice-credit note{
    UNION ALL
    SELECT
    HDR.PROJECT_NO,
    4 AS ROW1,
    'AMT' AS AMT_TYPE,'N' AS IS_BOLD,
    19 AS ROW2,
    'Billing of the Year ('||($P{FINANCIAL_YEAR})||')' AS ROW3,
    SUM(INV.total_pre_tax_home_amt) AS AMT
    FROM PJ_EST_OST_HDR HDR
    LEFT JOIN pj_ar_inv_hst_hdr INV ON INV.PROJECT_NO = HDR.PROJECT_NO AND INV.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND INV.INVOICE_TYPE = 'R'
    WHERE 1=1
    GROUP BY HDR.PROJECT_NO
    UNION ALL
    SELECT
    HDR.PROJECT_NO,
    4 AS ROW1,
    'AMT' AS AMT_TYPE,'N' AS IS_BOLD,
    19 AS ROW2,
    'Billing of the Year ('||($P{FINANCIAL_YEAR})||')' AS ROW3,
    -SUM(CRN.total_pre_tax_home_amt) AS AMT
    FROM PJ_EST_OST_HDR HDR
    LEFT JOIN PJ_AR_CRN_HST_HDR CRN ON CRN.PROJECT_NO = HDR.PROJECT_NO AND CRN.YEAR_POSTED_TO = $P{FINANCIAL_YEAR} AND CRN.CREDIT_NOTE_TYPE = 'R'
    WHERE 1=1
    GROUP BY HDR.PROJECT_NO
    UNION ALL
    SELECT
    HDR.PROJECT_NO,
    4 AS ROW1,
    'AMT' AS AMT_TYPE,'N' AS IS_BOLD,
    19 AS ROW2,
    'Billing of the Year ('||($P{FINANCIAL_YEAR})||')' AS ROW3,
    -SUM(INV.RETENTION_HOME_AMT) AS AMT
    FROM PJ_EST_OST_HDR HDR
    LEFT JOIN pj_ar_inv_hst_hdr INV ON INV.PROJECT_NO = HDR.PROJECT_NO AND INV.YEAR_POSTED_TO = $P{FINANCIAL_YEAR}
    WHERE 1=1
    GROUP BY HDR.PROJECT_NO
    UNION ALL
    SELECT
    HDR.PROJECT_NO,
    4 AS ROW1,
    'AMT' AS AMT_TYPE,'N' AS IS_BOLD,
    19 AS ROW2,
    'Billing of the Year ('||($P{FINANCIAL_YEAR})||')' AS ROW3,
    SUM(CRN.RETENTION_HOME_AMT) AS AMT
    FROM PJ_EST_OST_HDR HDR
    LEFT JOIN PJ_AR_CRN_HST_HDR CRN ON CRN.PROJECT_NO = HDR.PROJECT_NO AND CRN.YEAR_POSTED_TO = $P{FINANCIAL_YEAR}
    WHERE 1=1
    GROUP BY HDR.PROJECT_NO
        --end sub part: +invoice-credit note}
    --Billing Accumulated To-Date
    UNION ALL
    SELECT
    HDR.PROJECT_NO,
    4 AS ROW1,
    'AMT' AS AMT_TYPE,'N' AS IS_BOLD,
    20 AS ROW2,
    'Billing Accumulated To-Date' AS ROW3,
    SUM(BRK.nett_inv_pre_tax_home_amt) AS AMT
    FROM PJ_EST_OST_HDR HDR
    LEFT JOIN Pj_est_ost_cst_brkdwn BRK ON BRK.PROJECT_NO = HDR.PROJECT_NO AND BRK.YEAR_POSTED_TO > $P{FINANCIAL_YEAR}
    WHERE 1=1
    GROUP BY HDR.PROJECT_NO
        --sub part: +invoice-credit note{
    UNION ALL
    SELECT
    HDR.PROJECT_NO,
    4 AS ROW1,
    'AMT' AS AMT_TYPE,'N' AS IS_BOLD,
    20 AS ROW2,
    'Billing Accumulated To-Date' AS ROW3,
    SUM(INV.total_pre_tax_home_amt) AS AMT
    FROM PJ_EST_OST_HDR HDR
    LEFT JOIN pj_ar_inv_hst_hdr INV ON INV.PROJECT_NO = HDR.PROJECT_NO AND INV.YEAR_POSTED_TO > $P{FINANCIAL_YEAR} AND INV.INVOICE_TYPE = 'R'
    WHERE 1=1
    GROUP BY HDR.PROJECT_NO
    UNION ALL
    SELECT
    HDR.PROJECT_NO,
    4 AS ROW1,
    'AMT' AS AMT_TYPE,'N' AS IS_BOLD,
    20 AS ROW2,
    'Billing Accumulated To-Date' AS ROW3,
    -SUM(CRN.total_pre_tax_home_amt) AS AMT
    FROM PJ_EST_OST_HDR HDR
    LEFT JOIN PJ_AR_CRN_HST_HDR CRN ON CRN.PROJECT_NO = HDR.PROJECT_NO AND CRN.YEAR_POSTED_TO > $P{FINANCIAL_YEAR} AND CRN.CREDIT_NOTE_TYPE = 'R'
    WHERE 1=1
    GROUP BY HDR.PROJECT_NO
    UNION ALL
    SELECT
    HDR.PROJECT_NO,
    4 AS ROW1,
    'AMT' AS AMT_TYPE,'N' AS IS_BOLD,
    20 AS ROW2,
    'Billing Accumulated To-Date' AS ROW3,
    -SUM(INV.RETENTION_HOME_AMT) AS AMT
    FROM PJ_EST_OST_HDR HDR
    LEFT JOIN pj_ar_inv_hst_hdr INV ON INV.PROJECT_NO = HDR.PROJECT_NO AND INV.YEAR_POSTED_TO > $P{FINANCIAL_YEAR}
    WHERE 1=1
    GROUP BY HDR.PROJECT_NO
    UNION ALL
    SELECT
    HDR.PROJECT_NO,
    4 AS ROW1,
    'AMT' AS AMT_TYPE,'N' AS IS_BOLD,
    20 AS ROW2,
    'Billing Accumulated To-Date' AS ROW3,
    SUM(CRN.RETENTION_HOME_AMT) AS AMT
    FROM PJ_EST_OST_HDR HDR
    LEFT JOIN PJ_AR_CRN_HST_HDR CRN ON CRN.PROJECT_NO = HDR.PROJECT_NO AND CRN.YEAR_POSTED_TO > $P{FINANCIAL_YEAR}
    WHERE 1=1
    GROUP BY HDR.PROJECT_NO
        --end sub part: +invoice-credit note}
    --Retention Balance To-Date
    UNION ALL
    SELECT
    HDR.PROJECT_NO,
    4 AS ROW1,
    'AMT' AS AMT_TYPE,'N' AS IS_BOLD,
    21 AS ROW2,
    'Retention Balance To-Date' AS ROW3,
    -SUM(INV.total_pre_tax_home_amt) AS AMT
    FROM PJ_EST_OST_HDR HDR
    LEFT JOIN pj_ar_inv_hst_hdr INV ON INV.PROJECT_NO = HDR.PROJECT_NO  AND INV.INVOICE_TYPE = 'R'
    WHERE 1=1
    GROUP BY HDR.PROJECT_NO
    UNION ALL
    SELECT
    HDR.PROJECT_NO,
    4 AS ROW1,
    'AMT' AS AMT_TYPE,'N' AS IS_BOLD,
    21 AS ROW2,
    'Retention Balance To-Date' AS ROW3,
    SUM(CRN.total_pre_tax_home_amt) AS AMT
    FROM PJ_EST_OST_HDR HDR
    LEFT JOIN PJ_AR_CRN_HST_HDR CRN ON CRN.PROJECT_NO = HDR.PROJECT_NO  AND CRN.CREDIT_NOTE_TYPE = 'R'
    WHERE 1=1
    GROUP BY HDR.PROJECT_NO
    UNION ALL
    SELECT
    HDR.PROJECT_NO,
    4 AS ROW1,
    'AMT' AS AMT_TYPE,'N' AS IS_BOLD,
    21 AS ROW2,
    'Retention Balance To-Date' AS ROW3,
    SUM(INV.RETENTION_HOME_AMT) AS AMT
    FROM PJ_EST_OST_HDR HDR
    LEFT JOIN pj_ar_inv_hst_hdr INV ON INV.PROJECT_NO = HDR.PROJECT_NO
    WHERE 1=1
    GROUP BY HDR.PROJECT_NO
    UNION ALL
    SELECT
    HDR.PROJECT_NO,
    4 AS ROW1,
    'AMT' AS AMT_TYPE,'N' AS IS_BOLD,
    21 AS ROW2,
    'Retention Balance To-Date' AS ROW3,
    -SUM(CRN.RETENTION_HOME_AMT) AS AMT
    FROM PJ_EST_OST_HDR HDR
    LEFT JOIN PJ_AR_CRN_HST_HDR CRN ON CRN.PROJECT_NO = HDR.PROJECT_NO
    WHERE 1=1
    GROUP BY HDR.PROJECT_NO
    --Total Progress Billing To-Date
    UNION ALL
    SELECT
    HDR.PROJECT_NO,
    4 AS ROW1,
    'AMT' AS AMT_TYPE,'Y' AS IS_BOLD,
    22 AS ROW2,
    'Total Progress Billing To-Date' AS ROW3,
    SUM(BRK.nett_inv_pre_tax_home_amt) AS AMT
    FROM PJ_EST_OST_HDR HDR
    LEFT JOIN Pj_est_ost_cst_brkdwn BRK ON BRK.PROJECT_NO = HDR.PROJECT_NO
    WHERE 1=1
    GROUP BY HDR.PROJECT_NO
    --End of Progress Billing}

    --Net Amount due from/(to) Customers
    UNION ALL
    SELECT
    HDR.PROJECT_NO,
    5 AS ROW1,
    'AMT' AS AMT_TYPE,'N' AS IS_BOLD,
    23 AS ROW2,
    'Net Amount due from/(to) Customers' AS ROW3,
    CASE WHEN COALESCE(HDR.EST_TOTAL_COST_HOME,0)=0 THEN -BRK.AMT_BILL ELSE (HDR.TOTAL_PRE_TAX_HOME_AMT*COALESCE(BRK.AMT,0)/HDR.EST_TOTAL_COST_HOME - BRK.AMT_BILL) END AS AMT
    FROM PJ_EST_OST_HDR HDR
    LEFT JOIN (SELECT PROJECT_NO,SUM(TOTAL_ACTUAL_COST_HOME) AS AMT,SUM(NETT_INV_PRE_TAX_HOME_AMT) AS AMT_BILL FROM PJ_EST_OST_CST_BRKDWN GROUP BY PROJECT_NO) BRK ON BRK.PROJECT_NO = HDR.PROJECT_NO
    --Amount due from Customers (Accrued Revenue)
    UNION ALL
    SELECT
    HDR.PROJECT_NO,
    5 AS ROW1,
    'AMT' AS AMT_TYPE,'N' AS IS_BOLD,
    24 AS ROW2,
    'Amount due from Customers (Accrued Revenue)' AS ROW3,
    CASE WHEN COALESCE(HDR.EST_TOTAL_COST_HOME,0)=0 THEN 0
         WHEN (HDR.TOTAL_PRE_TAX_HOME_AMT*COALESCE(BRK.AMT,0)/HDR.EST_TOTAL_COST_HOME - BRK.AMT_BILL) <0 THEN 0
         ELSE (HDR.TOTAL_PRE_TAX_HOME_AMT*COALESCE(BRK.AMT,0)/HDR.EST_TOTAL_COST_HOME - BRK.AMT_BILL) END AS AMT
    FROM PJ_EST_OST_HDR HDR
    LEFT JOIN (SELECT PROJECT_NO,SUM(TOTAL_ACTUAL_COST_HOME) AS AMT,SUM(NETT_INV_PRE_TAX_HOME_AMT) AS AMT_BILL FROM PJ_EST_OST_CST_BRKDWN GROUP BY PROJECT_NO) BRK ON BRK.PROJECT_NO = HDR.PROJECT_NO
    WHERE 1=1
    --Amount due to Customers (Deferred Revenue)
    UNION ALL
    SELECT
    HDR.PROJECT_NO,
    5 AS ROW1,
    'AMT' AS AMT_TYPE,'N' AS IS_BOLD,
    25 AS ROW2,
    'Amount due to Customers (Deferred Revenue)' AS ROW3,
    CASE WHEN COALESCE(HDR.EST_TOTAL_COST_HOME,0)=0 THEN -BRK.AMT_BILL
         WHEN (HDR.TOTAL_PRE_TAX_HOME_AMT*COALESCE(BRK.AMT,0)/HDR.EST_TOTAL_COST_HOME - BRK.AMT_BILL) >0 THEN 0
         ELSE (HDR.TOTAL_PRE_TAX_HOME_AMT*COALESCE(BRK.AMT,0)/HDR.EST_TOTAL_COST_HOME - BRK.AMT_BILL) END AS AMT
    FROM PJ_EST_OST_HDR HDR
    LEFT JOIN (SELECT PROJECT_NO,SUM(TOTAL_ACTUAL_COST_HOME) AS AMT,SUM(NETT_INV_PRE_TAX_HOME_AMT) AS AMT_BILL FROM PJ_EST_OST_CST_BRKDWN GROUP BY PROJECT_NO) BRK ON BRK.PROJECT_NO = HDR.PROJECT_NO
    WHERE 1=1
) T  ON T.PROJECT_NO = PJHDR.PROJECT_NO

WHERE 1=1
$P!{__RPT__FILTER__PARAM}