2 Replies Latest reply on Mar 15, 2016 9:15 PM by ppillai

    Link between PO and PA Modules

    2937583

      Hi All,

       

      Could you please help here to link the PO Module with PA Module.

       

      I need populate the Project Number to the Report:

      As of now below tables are readily available in the Report query:

      HR_ALL_ORGANIZATION_UNITS

      mtl_categories_b

      mtl_category_sets_tl

      mtl_item_categories

      MTL_PLANNERS

      MTL_SYSTEM_ITEMS_B

      ORG_ORGANIZATION_DEFINITIONS

      PER_all_PEOPLE_F

      po_action_history

      po_agents

      PO_APPROVED_SUPPLIER_LIST

      PO_ASL_ATTRIBUTES

      po_headers_all

      PO_LINE_LOCATIONS_all

      PO_LINE_LOCATIONS_ARCHIVE_ALL

      PO_Lines_all

      po_releases_all

      PO_VENDOR_CONTACTS

      po_vendor_sites_all

      PO_VENDORS

      rcv_transactions

       

      I need to include the PA_PROJECTS_ALL table so that I can get the Project Number.

       

      Please help me here to include the this PA_PROJECTS_ALL table by using above tables or is there any option to get the Project Number from Existing Tables.

       

      Thanks& Regards,

      Venkat

        • 1. Re: Link between PO and PA Modules
          Jagadekara

          Hi,

           

          The is a project and task id field on the po distribution record.


          Below query may give you an Idea.


          Link between Project and PO in Oracle Apps


          SELECT   DISTINCT c.po_header_id,
                            b.po_line_id,
                            c.segment1,
                            b.line_location_id,
                            b.po_distribution_id,
                            --  b.distribution_num,
                            a.unit_price,
                            (a.quantity - b.quantity_billed),
                            NULL,
                            a.item_id,
                            g.segment1 "Project Number",
                            h.task_name,
                            h.task_id,
                            b.expenditure_type,
                            b.expenditure_item_date,
                            b.project_accounting_context,
                            b.expenditure_organization_id
            --  j.shipment_num
            FROM   po_lines_all a,
                   po_distributions_all b,
                   po_headers_all c,
                   pa_projects_all g,
                   pa_tasks h,
                   pa_project_statuses i,
                   po_line_locations_all j
          WHERE       1 = 1
                   AND c.segment1 = rec_line.po_number
                   AND a.line_num = NVL (rec_line.po_line_number, a.line_num)
                   AND c.po_header_id = a.po_header_id
                   AND a.po_header_id = b.po_header_id
                   AND a.po_line_id = b.po_line_id
                   AND b.project_id = g.project_id
                   AND b.task_id = h.task_id
                   AND g.project_status_code = i.project_status_code
                   AND j.po_line_id = a.po_line_id
                   AND j.line_location_id = b.line_location_id
                   AND (a.quantity - b.quantity_billed) > 0
                   AND NVL (a.cancel_flag, 'N') <> 'Y'

          • 2. Re: Link between PO and PA Modules
            ppillai

            You may get the PA Project and Task info from PO Line Distribution ... see this query

             

            http://www.appsdepot.com/demo/php/displayContent.php?linkId=4&sublink=4002907&quote=yes