SELECT
main.resource_name,
SUM(nvl(main.planned_amt, 0)) planned_amt,
SUM(nvl(main.actual_amount, 0)) actual_amount,
SUM(nvl(main.actual_amount, 0)) - SUM(nvl(main.planned_amt, 0)) varience
FROM
(
SELECT
ppav.project_unit,
ppav.proj_number,
ppav.project_id,
ppav.proj_name,
ppav.start_date,
ppav.completion_date,
ppav.project_type,
ppav.proj_status,
ppav.proj_desc,
res.resource_name,
SUM(nvl(res.planned_amt, 0)) planned_amt,
nvl((
SELECT
SUM(nvl(pcd1.acct_raw_cost, 0))
FROM
pjc_cost_dist_lines_all pcd1,
pjf_resource_classes_b prc1,
pjf_resource_classes_tl prct1,
pjf_txn_base_header ptbh
WHERE
pcd1.txn_accum_header_id = ptbh.txn_accum_header_id
AND ptbh.resource_class_id = prc1.resource_class_id
AND prc1.resource_class_id = prct1.resource_class_id
AND prct1.language = userenv('LANG')
AND prct1.source_lang = userenv('LANG')
AND pcd1.project_id = ppav.project_id --300001798619442
AND prct1.name = res.resource_name
), 0) actual_amount
FROM
-- pjf_tasks_v ptv,
(
SELECT
pcp.contract_id,
ppa.project_id,
ppa.project_unit_id,
ppa.name proj_name,
ppa.segment1 proj_number,
ppa.org_id,
ppst.project_status_name proj_status,
ppa.start_date,
ppa.completion_date,
hov.name project_unit,
pptt.project_type,
ppa.description proj_desc,
CASE
WHEN ppa.attribute_category <> 'AMO Eng Bidding' --No Need to check AMO Eng category
THEN
ppa.attribute2
END bidding_prj_num,
ppa.attribute1 source_of_bidding,
to_char(ppa.attribute1_date, 'mm/dd/yyyy') q_and_a_date,
to_char(ppa.attribute2_date, 'mm/dd/yyyy') submission_date,
ppa.attribute2 earning_opportunity,
ppa.attribute7 project_segment_gl,
hou.name project_bu
FROM
pjf_projects_all_vl ppa,
pjb_cntrct_proj_links pcp,
pjf_project_statuses_tl ppst,
hr_organization_v hov,
pjf_project_types_tl pptt,
hr_operating_units hou
WHERE
1 = 1
AND ppa.project_id = pcp.project_id (+)
AND ppa.project_status_code = ppst.project_status_code
AND ppst.language = userenv('LANG')
AND ppst.source_lang = userenv('LANG')
AND hov.organization_id = ppa.project_unit_id
AND hov.classification_code = 'PRJ_PROJECT_UNIT' --Classification PRJ_PROJECT_UNIT
AND hov.status = 'A'
AND sysdate BETWEEN hov.effective_start_date AND hov.effective_end_date
AND ppa.project_type_id = pptt.project_type_id
AND pptt.source_lang = userenv('LANG') --Source Lang US
AND pptt.language = userenv('LANG') --Language US
AND ppa.org_id = hou.organization_id
GROUP BY
pcp.contract_id,
ppa.project_id,
ppa.project_unit_id,
ppa.name,
ppa.segment1,
ppa.org_id,
ppst.project_status_name,
ppa.start_date,
ppa.completion_date,
hov.name,
pptt.project_type,
ppa.description,
CASE
WHEN ppa.attribute_category <> 'AMO Eng Bidding' --No Need to check AMO Eng category
THEN
ppa.attribute2
END,
ppa.attribute1,
to_char(ppa.attribute1_date, 'mm/dd/yyyy'),
to_char(ppa.attribute2_date, 'mm/dd/yyyy'),
ppa.attribute2,
ppa.attribute7,
hou.name
) ppav,
(
SELECT
ppvv.project_id,
ppvv.plan_version_id,
ppvv.version_name,
ppvv.total_pc_brdnd_cost,
ppvv.total_pc_revenue
FROM
pjo_plan_versions_vl ppvv
WHERE
1 = 1
AND plan_status_code = 'B' --Baseline plan status data need to fetch
AND ppvv.plan_class_code = 'BUDGET' --Budget Version Detail need to fetch
AND ppvv.budgetary_controls_flag = 'Y'
AND version_number = (
SELECT
MAX(version_number)
FROM
pjo_plan_versions_vl
WHERE
project_id = ppvv.project_id
AND plan_status_code = 'B' --Baseline plan status data need to fetch
)
) pv,
(
SELECT
ppe.project_id,
ppe.plan_version_id,
prct.name resource_name, --v 2.0
SUM(nvl(ppl.total_tc_raw_cost, 0)) planned_amt
FROM
pjo_planning_elements ppe,
pjf_rbs_elements pre,
pjo_plan_lines ppl,
pjf_resource_classes_b prc,
pjf_resource_classes_tl prct
WHERE
ppe.rbs_element_id = pre.rbs_element_id
AND ppe.planning_element_id = ppl.planning_element_id
AND pre.resource_class_code = prc.resource_class_code
AND prc.resource_class_id = prct.resource_class_id
AND prct.language = userenv('LANG')
AND prct.source_lang = userenv('LANG')
GROUP BY
ppe.project_id,
ppe.plan_version_id,
prct.name
) res
WHERE
1 = 1
-- ppav.project_id = ptv.project_id
AND ppav.project_id = pv.project_id (+)
AND ppav.project_id = res.project_id
AND pv.plan_version_id = res.plan_version_id
-- AND ptv.task_id = res.task_id
AND ( ppav.project_bu IN (
:p_bu
)
OR 'ALL' IN (
'ALL' || :p_bu
) )
AND ( ppav.proj_number IN (
:p_project_num
)
OR 'ALL' IN (
'ALL' || :p_project_num
) )
AND ( ppav.proj_name IN (
:p_project_name
)
OR 'ALL' IN (
'ALL' || :p_project_name
) )
AND ( ppav.project_unit_id IN (
:p_project_unit
)
OR 'ALL' IN (
'ALL' || :p_project_unit
) )
--V 1.0 Parameter Removed
/*
AND inv.trx_date BETWEEN nvl(
:p_trx_from_date, inv.trx_date
) AND nvl(
:p_trx_to_date, inv.trx_date
)
AND ( ptv.task_id IN ( :p_task )
OR 'ALL' IN ( 'ALL' || :p_task ) )
AND ( ppav.org_id IN ( :p_bu )
OR 'ALL' IN ( 'ALL' || :p_bu ) )
AND ( ppav.project_unit_id IN ( :p_project_unit )
OR 'ALL' IN ( 'ALL' || :p_project_unit ) )
AND ( ppav.project_id IN ( :p_project_name )
OR 'ALL' IN ( 'ALL' || :p_project_name ) )
AND ppav.start_date BETWEEN nvl(
:p_from_date, ppav.start_date
) AND nvl(
:p_to_date, ppav.start_date
)
*/
AND ppav.org_id IN (
SELECT
org_id
FROM
fun_user_role_data_asgnmnts furda,
hr_operating_units hou
WHERE
user_guid = fnd_global.user_guid
AND active_flag != 'N'
AND hou.organization_id = furda.org_id
UNION
SELECT
hou.organization_id org_id
FROM
per_users pu,
per_all_people_f ppf,
per_all_assignments_f papf,
hr_operating_units hou
WHERE
ppf.person_id = pu.person_id
AND papf.person_id = ppf.person_id
AND hou.organization_id = papf.business_unit_id
AND pu.username = fnd_global.user_name
AND primary_assignment_flag = 'Y'
AND sysdate BETWEEN papf.effective_start_date AND papf.effective_end_date
)
GROUP BY
ppav.project_unit,
ppav.proj_number,
ppav.project_id,
ppav.proj_name,
ppav.start_date,
ppav.completion_date,
ppav.project_type,
ppav.proj_status,
ppav.proj_desc,
res.resource_name
ORDER BY
ppav.proj_number
) main
GROUP BY
main.resource_name
ORDER BY
main.resource_name
