PA PCO Report Data Template
Description
Run
PA PCO Report Data Template and other Oracle EBS reports with Blitz Report™ on our demo environment
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 |