PA PCO Report Data Template

        select 
             ROUND(NVL(direct_cost.DIRECT_COST_GRAND_TOTAL_PFC,0),2) DIRECT_COST_GRAND_TOTAL_PFC,
             ROUND(NVL(supplier_cost.SUPPLIER_COST_GRAND_TOTAL_PFC,0),2) SUPPLIER_COST_GRAND_TOTAL_PFC,
             ROUND((NVL(direct_cost.DIRECT_COST_GRAND_TOTAL_PFC,0)+NVL(supplier_cost.SUPPLIER_COST_GRAND_TOTAL_PFC,0)),2) TOTAL_COST_GRAND_TOTAL_PFC
             from
             (SELECT SUM(p.burdened_cost * rate.conversion_rate)/ Nvl(:pFactoring, 1) SUPPLIER_COST_GRAND_TOTAL_PFC
FROM   pa_control_items pci,
       pa_ci_supplier_details p,
       pa_budget_versions pbv,
       pa_resource_asgn_curr cur,
       (SELECT asg.budget_version_id,
               asg.resource_assignment_id,
               asg.txn_currency_code,
               SUM(NVL(asg.total_projfunc_burdened_cost,0))
               grand_total_pfc,
               SUM(NVL(asg.total_txn_burdened_cost,0))
               grand_total_txn,
               SUM(nvl(asg.total_projfunc_burdened_cost,0) )/SUM(NVL(asg.total_txn_burdened_cost,0))       conversion_rate
               FROM   pa_resource_asgn_curr asg
               WHERE  ( asg.budget_version_id, asg.resource_assignment_id ) IN (
                       SELECT DISTINCT pbv.budget_version_id,
               pcsd2.resource_assignment_id
               FROM   pa_ci_supplier_details pcsd2,
               pa_control_items pci2,
               pa_budget_versions pbv
               WHERE  pci2.original_ci_id = :pCRId
               AND
                  pci2.version_number=:pCRVersionNum and
               pci2.ci_id = pcsd2.ci_id
               AND pbv.ci_id = pcsd2.ci_id
               UNION
               SELECT DISTINCT pbv.budget_version_id,
               pcsd2.resource_assignment_id
               FROM   pa_ci_direct_cost_details pcsd2,
               pa_control_items pci2,
               pa_budget_versions pbv
               WHERE  pci2.original_ci_id= :pCRId
               AND
                  pci2.version_number=:pCRVersionNum
				  and  pci2.ci_id = pcsd2.ci_id
               AND pbv.ci_id = pcsd2.ci_id)
               AND asg.total_projfunc_burdened_cost IS NOT NULL
               GROUP  BY asg.budget_version_id,
                  asg.resource_assignment_id,
                  asg.txn_currency_code) rate
WHERE  pci.ci_id = p.ci_id
       AND
         pci.version_number=:pCRVersionNum and
       pci.original_ci_id = :pCRId
       AND pbv.ci_id = p.ci_id
       AND p.raw_cost IS NOT NULL
       AND cur.budget_version_id = pbv.budget_version_id
       AND cur.resource_assignment_id = p.resource_assignment_id
       AND cur.txn_currency_code = p.currency_code
       AND rate.budget_version_id = pbv.budget_version_id
       AND rate.resource_assignment_id = p.resource_assignment_id
       AND rate.txn_currency_code = p.currency_code) supplier_cost,
             (SELECT SUM(p.burdened_cost * rate.conversion_rate)/ Nvl(:pFactoring, 1) DIRECT_COST_GRAND_TOTAL_PFC
FROM   pa_control_items pci,
       pa_ci_direct_cost_details p,
       pa_budget_versions pbv,
       pa_resource_asgn_curr cur,
       (SELECT asg.budget_version_id,
               asg.resource_assignment_id,
               asg.txn_currency_code,
               SUM(NVL(asg.total_projfunc_burdened_cost,0))
               grand_total_pfc,
               SUM(NVL(asg.total_txn_burdened_cost,0))
               grand_total_txn,
               SUM(nvl(asg.total_projfunc_burdened_cost,0) )/SUM(NVL(asg.total_txn_burdened_cost,0))       conversion_rate
               FROM   pa_resource_asgn_curr asg
               WHERE  ( asg.budget_version_id, asg.resource_assignment_id ) IN (
                       SELECT DISTINCT pbv.budget_version_id,
               pcsd2.resource_assignment_id
               FROM   pa_ci_supplier_details pcsd2,
               pa_control_items pci2,
               pa_budget_versions pbv
               WHERE  pci2.original_ci_id = :pCRId
               AND
                  pci2.version_number=:pCRVersionNum and
               pci2.ci_id = pcsd2.ci_id
               AND pbv.ci_id = pcsd2.ci_id
               UNION
               SELECT DISTINCT pbv.budget_version_id,
               pcsd2.resource_assignment_id
               FROM   pa_ci_direct_cost_details pcsd2,
               pa_control_items pci2,
               pa_budget_versions pbv
               WHERE  pci2.original_ci_id= :pCRId
               AND
                  pci2.version_number=:pCRVersionNum
				  and  pci2.ci_id = pcsd2.ci_id
               AND pbv.ci_id = pcsd2.ci_id)
               AND asg.total_projfunc_burdened_cost IS NOT NULL
               GROUP  BY asg.budget_version_id,
                  asg.resource_assignment_id,
                  asg.txn_currency_code) rate
WHERE  pci.ci_id = p.ci_id
       AND
          pci.version_number=:pCRVersionNum and
       pci.original_ci_id = :pCRId
       AND pbv.ci_id = p.ci_id
       AND p.raw_cost IS NOT NULL
       AND cur.budget_version_id = pbv.budget_version_id
       AND cur.resource_assignment_id = p.resource_assignment_id
       AND cur.txn_currency_code = p.currency_code
       AND rate.budget_version_id = pbv.budget_version_id
       AND rate.resource_assignment_id = p.resource_assignment_id
       AND rate.txn_currency_code = p.currency_code) direct_cost