Oracle Projects sql queries

Oracle Projects sql queries

In this post , we will be discuss about Oracle Projects sql queries. We will be share different types of oracle project sql queries which helps to extract the different types of oracle project informations in oracle apps. These are the complete oracle project sql queries , which we can use in the oracle to extract the project informations. Here below is the detail list of Oracle Projects sql queries.

Oracle Projects sql queries

10 Most Important tables Used by Oracle Projects sql queries

1.pa_resource_assignments
2.pa_proj_elements
3.pa_resource_list_members
4.pa_resource_asgn_curr
5.pa_budget_versions
6.pa_fin_plan_types_b
7.pa_proj_fp_options
8.pa_proj_element_versions
9.pa_control_items
10.pa_projects_all

Detail List of Oracle Projects sql queries

SELECT p.name "Project Name",
pbv.ci_id,
  NVL(pe.name ,p.name) task_name,
    NVL(pe.element_number, p.segment1)
  || '('
  || NVL(pe.name ,p.name)
  || ')' task_name_num
  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,
  ROUND(DECODE(pra.rate_based_flag, 'Y', rac.txn_raw_cost_rate_override, to_number(NULL)),2)    AS raw_cost_rate,
  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_burden_cost_rate_override, TO_NUMBER(NULL)),2) 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
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 ( 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)
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 (+)

PA_COST_DISTRIBUTION_LINES_ALL

PA_CUST_EVENT_RDL_ALL

PA_CUST_REV_DIST_LINES_ALL

The following Projects tables have reference fields in the General Ledger table GL_JE_LINES:

1. Tables
   
   • PA_COST_DISTRIBUTION_LINES_ALL
   • PA_CUST_EVENT_RDL_ALL
   • PA_CUST_REV_DIST_LINES_ALL

1. All have reference fields in GL_JE_LINES.  The join conditions with these columns are:

1. BATCH_NAME             = GL_JE_LINES.REFERENCE_1
   DR_CODE_COMBINATION_ID = GL_JE_LINES.CODE_COMBINATION_ID
2. The PA_DRAFT_REVENUES_ALL table has the following join conditions
3. UNEARNED_BATCH_NAME          = GL_JE_LINES.REFERENCE_1
4. UNEARNED_CODE_COMBINATION_ID = GL_JE_LINES.CODE_COMBINATION_ID
   
   or
   
   
   UNBILLED_BATCH_NAME          = GL_JE_LINES.REFERENCE_1
   UNBILLED_CODE_COMBINATION_ID = GL_JE_LINES.CODE_COMBINATION_ID

Second Oracle Projects sql queries

select   pa.project_id,

          pa.org_id,

          pa.NAME,

          pa.long_name,

          pa.segment1,

          pa.project_status_code,

          pab.burdened_cost budget_cost,

          pa.created_by,

          pa.project_type,

          pa.start_date,

          pa.completion_date,

          pa.closed_date,

          pa.carrying_out_organization_id,

  NVL ((SELECT DISTINCT ppf.first_name || ' ' || ppf.last_name

                           FROM pa_project_players p, per_all_people_f ppf

                          WHERE ppf.person_id = p.person_id

                            AND project_role_type = 'PROJECT MANAGER'

                            AND p.project_id = pa.project_id),

               'Not Defined'

              ) Project_members,

          pt.task_id,

          pt.task_number,

          pt.attribute1 task_status,

          pt.task_name,

          pt.description,

         pab.budget_version_id,

 trunc(pab.cur_base_date) approval_date,

          (SELECT   SUM (burdened_cost)

               FROM pafv_budget_lines

              WHERE task_id = pt.task_id

                AND budget_version_id = pab.budget_version_id

           GROUP BY task_id) task_budget,

          pt.top_task_id,

          pt.wbs_level,

          pt.start_date,

          pt.completion_date,

          pod.DESTINATION_TYPE_CODE,

           (select item_id from po_lines_all

           where po_line_id=pod.po_line_id) item,

             (SELECT description

             FROM mtl_system_items_b k

             WHERE inventory_item_id = (select item_id from po_lines_all

                                       where po_line_id=pod.po_line_id)

              AND organization_id = pa.carrying_out_organization_id) item_desc,

          (SELECT segment1

             FROM mtl_system_items_b k

            WHERE inventory_item_id =(select item_id from po_lines_all

                                        where po_line_id=pod.po_line_id)

              AND organization_id = pa.carrying_out_organization_id) item_code,

                (select  pha.segment1 from po_headers_all pha

                     where pha.po_header_id=pod.po_header_id) po_num,

                (select  pv.vendor_name from po_headers_all pha,po_vendors pv

                     where pv.vendor_id=pha.vendor_id

                     and  pha.po_header_id=pod.po_header_id) vendor_name,  

                (select quantity from po_lines_all

                    where po_line_id=pod.po_line_id) PO_QTY,

                (select unit_price from po_lines_all

                     where po_line_id=pod.po_line_id) PO_unit_price,

                 (select (pl.unit_price * pl.quantity * nvl(ph.rate,1))

                           from po_lines_all pl,po_headers_all ph

                           where ph.po_header_id=pl.po_header_id

                           and pl.po_line_id=pod.po_line_id) PO_commit_cost,

                  (SELECT distinct receipt_num

                   FROM rcv_shipment_headers rsh, rcv_transactions rcv,po_lines_all pla

                   WHERE rsh.shipment_header_id = rcv.shipment_header_id

                   and rcv.po_line_id=pla.po_line_id

                   AND PO_DISTRIBUTION_ID=pod.PO_DISTRIBUTION_ID

                   AND transaction_id=rcv1.transaction_id) rec_num,

             (select distinct trunc(transaction_date) from rcv_transactions rcv

              where PO_DISTRIBUTION_ID=pod.PO_DISTRIBUTION_ID

              and transaction_type = 'DELIVER' and rownum=1) Rec_Date,

                      (select sum((decode (transaction_type,'DELIVER',quantity

                                                           ,'RETURN TO VENDOR', (quantity * (-1)))))

                      from rcv_transactions rcv

                      where PO_DISTRIBUTION_ID=pod.PO_DISTRIBUTION_ID

                      and shipment_line_id = rcv1.shipment_line_id

                      and transaction_type in ('DELIVER','RETURN TO VENDOR')) rec_qty,

                           (select distinct po_unit_price from rcv_transactions rcv

                            where PO_DISTRIBUTION_ID=pod.PO_DISTRIBUTION_ID

                            and transaction_type = 'DELIVER') Rec_Price ,

              ((select sum((decode (transaction_type,'DELIVER',quantity

                                                      ,'RETURN TO VENDOR', (quantity * (-1)))))

                from rcv_transactions rcv

                where PO_DISTRIBUTION_ID=pod.PO_DISTRIBUTION_ID

                and shipment_line_id = rcv1.shipment_line_id

                and transaction_type in ('DELIVER','RETURN TO VENDOR')) * (select distinct po_unit_price from rcv_transactions rcv

                where PO_DISTRIBUTION_ID=pod.PO_DISTRIBUTION_ID

                and transaction_type = 'DELIVER')) Rec_Val

 from

 pa_projects_all pa,

 pa_tasks pt,

 pa_budget_versions_draft_v pab,

 pa_budget_types ty,

 po_distributions_all pod,

 rcv_transactions rcv1

 where  pa.project_id=pt.project_id

 and pod.task_id=rcv1.task_id(+)

 and pod.po_distribution_id=rcv1.po_distribution_id(+)

  AND pa.org_id = :p_org_id

  AND pa.project_id = pab.project_id

  AND ty.budget_type_code = pab.budget_type_code

 and pod.task_id(+)=pt.task_id

      AND pa.segment1 = :p_project_number

 

Oracle Projects sql queries