Query to get project details in oracle apps

Query to get project details in oracle apps

Here we are going to discus about Query to get project details in oracle apps. This query helps to extract all the Oracle Projects relates information's in oracle apps. This Query also helps to provide Purchase orders information's against the Oracle Projects and  PO receiving information's too for these Projects tasks purchase orders. Please find below the complete sql query to get project details in oracle apps.

 

Key Tables uses in Oracle Projects Details Query

 

1.pa_projects_all
2.pa_tasks
3.pa_budget_versions_draft_v

4.pa_budget_types
5.po_distributions_all
6.rcv_transactions

 Example of SQL Query to get project details in oracle apps

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