Thursday 21 July 2022

Oracle Fusion - SQL Query to get Project Plan Task Associated Resource Utilization Details

Navigation: Projects > Manage Financial Project Settings > Manage Project Plan > Click on Resource.




--SQL Query:--


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

No comments:

Post a Comment