Monday 3 October 2016

Projrct Financial Plan CI Impact query

/* Formatted on 10/3/2016 8:03:57 PM (QP5 v5.114.809.3010) */
SELECT   pbv.ci_id,
         NVL (pe.name, p.name) task_name,
         rlm.alias planning_resource_name,
         (NVL (rac.total_projfunc_burdened_cost, 0) + NVL (NULL, 0))
            total_cost,
         DECODE (pfpt.plan_class_code,
                 'FORECAST', TO_NUMBER (NULL),
                 rac.total_txn_raw_cost)
            AS raw_cost_txn_cur,
         ROUND (
            DECODE (pra.rate_based_flag,
                    'Y', rac.txn_raw_cost_rate_override,
                    TO_NUMBER (NULL)),
            5
         )
            AS raw_cost_rate_override,
         DECODE (pfpt.plan_class_code,
                 'FORECAST', TO_NUMBER (NULL),
                 rac.total_projfunc_raw_cost)
            AS raw_cost_proj_func_cur,
         ROUND (
            DECODE (pra.rate_based_flag,
                    'Y', rac.txn_average_burden_cost_rate,
                    TO_NUMBER (NULL)),
            5
         )
            AS avg_burd_cost_rate,
         ROUND (
            DECODE (pra.rate_based_flag,
                    'Y', rac.txn_burden_cost_rate_override,
                    TO_NUMBER (NULL)),
            5
         )
            AS burd_cost_rate_override,
         DECODE (pfpt.plan_class_code,
                 'FORECAST', TO_NUMBER (NULL),
                 rac.total_projfunc_burdened_cost)
            AS burd_cost_proj_func_cur,
         DECODE (pfpt.plan_class_code,
                 'FORECAST', TO_NUMBER (NULL),
                 rac.total_txn_burdened_cost)
            AS burd_cost_txn_cur,
            NVL (pe.element_number, p.segment1)
         || '('
         || NVL (pe.name, p.name)
         || ')'
            task_name_num,
         NVL (ppe.element_version_id, 0) element_version_id
  FROM   pa_resource_assignments pra,
         pa_proj_elements pe,
         pa_resource_list_members rlm,
         pa_resource_asgn_curr rac,
         pa_budget_versions pbv,
         pa_fin_plan_types_b pfpt,
         pa_proj_fp_options po,
         pa_proj_element_versions ppe,
         pa_control_items ci,
         pa_projects_all p
 WHERE       pra.resource_assignment_id = rac.resource_assignment_id
         AND pra.resource_list_member_id = rlm.resource_list_member_id
         AND p.project_id = :p_project_id
         AND pra.project_id = p.project_id
         AND pbv.budget_version_id = rac.budget_version_id
         AND pbv.budget_version_id = pra.budget_version_id
         AND pbv.project_id = pra.project_id
         AND pbv.ci_id = ci.ci_id
         AND pbv.ci_id = :p_change_order_id
         AND pbv.fin_plan_type_id = pfpt.fin_plan_type_id
         AND pbv.budget_version_id = po.fin_plan_version_id
         AND po.fin_plan_option_level_code = 'PLAN_VERSION'
         AND po.fin_plan_version_id = pbv.budget_version_id
         AND pra.task_id = pe.proj_element_id(+)
         AND ppe.parent_structure_version_id(+) =
               pa_project_structure_utils.get_fin_struc_ver_id (
                  pe.project_id
               )
         AND pe.proj_element_id = ppe.proj_element_id(+)
         AND (   rac.total_quantity IS NOT NULL
              OR rac.txn_burden_cost_rate_override IS NOT NULL
              OR rac.total_txn_burdened_cost IS NOT NULL
              OR rac.txn_bill_rate_override IS NOT NULL
              OR rac.total_txn_revenue IS NOT NULL
              OR rac.txn_raw_cost_rate_override IS NOT NULL
              OR rac.total_txn_raw_cost IS NOT NULL);

No comments:

Post a Comment