PA PCO Report Data Template

Description

Application: Projects
Source:
Short Name: PAPCOREP
DB package:

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Copy

        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

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

Download