3 Replies Latest reply on Mar 10, 2014 7:41 AM by Abhi83

    PO Summary Report

    User467130-OC


      Dear Friends,

       

      I have the following existing query.

      I have a new requirement where in i need to add the following 8 fields.

       

      1)Requisition Number (Requisition)

      2)Requisition Creation Date  (Requisition)

      3)Receiving date(Inventory Receipts)

      4)Delivery date(Inventory Receipts)

      5)Assignment set(Sourcing)

      6)Rank1 vendor name

      7)Rank1 vendor price

      8)Rank1 vendor UOM

       

      The above fields needs to be incorporated into the following query

       

      SELECT  DISTINCT--
      hou.attribute3                        
      "State",

      -- hou.name "Branch",

               POHI.PO_NUM ,

               POLI.LINE_NUM,

               POHI.SHIP_TO_LOCATION,

               POLI.VENDOR_NAME,

               POLI.VENDOR_SITE_CODE,

               POLI.ORDER_DATE,

               MSIB.SEGMENT1  "ITEM",

               POLI.ITEM_DESCRIPTION,

               POLI.CATEGORY_ID,

               POLI.QUANTITY  ,

             POLI.UNIT_PRICE ,

               POLI.UNIT_MEAS_LOOKUP_CODE,

               POLI.AMOUNT,

               POLI.CURRENCY_CODE,

               POLLO.QUANTITY_RECEIVED,

               POLLO.QUANTITY_CANCELLED,

               POLI.AUTHORIZATION_STATUS,

               PAV.AGENT_NAME             "Buyer",

               POHI.VENDOR_CONTACT,

               POHI.CLOSED_CODE,

               POHI.COMMENTS,

               POLLO.VENDOR_PRODUCT_NUM,

               POLLO.NOTE_TO_VENDOR,

               POLLO.NEED_BY_DATE,

               POLLO.PROMISED_DATE,

               mcb.segment1  category,

               POHI.terms_name,

             POHI.authorization_status "Status",

               POHI.approved_date,

               POHI.rate

        from  PO_HEADERS_INQ_V POHI,

              PO_LINES_INQ_V  POLI,

              PO_LINES_ALL POL,

              MTL_SYSTEM_ITEMS_B MSIB,

              inv.mtl_item_categories  mic,

              inv.mtl_categories_b  mcb,

              PO_AGENTS_V PAV,

              PO_LINE_LOCATIONS_INQ_V POLLO

        --      hr.hr_all_organization_units  hou

        where POHI.PO_HEADER_ID = POLI.PO_HEADER_ID

        and   POLI.PO_LINE_ID = POLLO.PO_LINE_ID

        and   POHI.PO_HEADER_ID =POL.PO_HEADER_ID

        and   msib.inventory_item_id =mic.inventory_item_id

        AND   msib.organization_id =mic.organization_id

        --and   hou.organization_id   = msib.organization_id

        AND    mic.category_set_id = 1

      AND    mic.category_id = mcb.category_id

        and   POLI.AGENT_ID = PAV.AGENT_ID

        AND   POLI.ITEM_ID =MSIB.INVENTORY_ITEM_ID

        AND   MSIB.ORGANIZATION_ID = 84

        and   POHI.SHIP_TO_LOCATION not like '%MT'

        and   POHI.SHIP_TO_LOCATION not like '%CAWA'

        and   POHI.SHIP_TO_LOCATION not like '%CFS'

       

       

       

      Thank you very much in advance.

       

      Kind regards

      Siva charan

        • 1. Re: PO Summary Report
          Abhi83

          Siva,

           

          The link between the Requisition and PO is at Distributions level. So the join must be written with the PO Distributions and Requisition distributions. I am not a techie, but this can be a pointer.

           

          Rgds,

          Abhi

          • 2. Re: PO Summary Report
            User467130-OC

            Thanks Abhi,

             

            Just wanted to know where to pick up the following columns as well.

             

            3)Receiving date(Inventory Receipts)

            4)Delivery date(Inventory Receipts)

            5)Assignment set(Sourcing)

            6)Rank1 vendor name

            7)Rank1 vendor price

            8)Rank1 vendor UOM

             

             

            Kind Regards

            Siva Charan

            • 3. Re: PO Summary Report
              Abhi83

              Check if the belowt query works for Receipt/ Requistion & PO. About sourcing.. I don't have a clue. This query is not written by me. I had it in my repository.

               

               

              SELECT

               

              ph.segment1 po_num,

              ood.organization_name,

              pol.po_line_id,

              pll.quantity,

              rsh. receipt_source_code,

              rsh. vendor_id,

              rsh. vendor_site_id,

              rsh. organization_id,

              rsh. shipment_num,

              rsh. receipt_num,

              rsh. ship_to_location_id,

              rsh. bill_of_lading,

              rsl.shipment_line_id,

              rsl.QUANTITY_SHIPPED,

              rsl.QUANTITY_RECEIVED ,

              rct.transaction_type,

              rct.transaction_id,

              nvl(rct.source_doc_quantity,0) transaction_qty

              from rcv_transactions rct

              , rcv_shipment_headers rsh

              , rcv_shipment_lines rsl

              , po_lines_all pol

              , po_line_locations_all pll

              , po_headers_all ph

              , org_organization_definitions ood

              where 1=1

              and to_char(rct.creation_date, 'YYYY') in ('2010', '2011')

              and rct.po_header_id = ph.po_header_id

              and rct.po_line_location_id = pll.line_location_id

              and rct.po_line_id = pol.po_line_id

              and rct.shipment_line_id=rsl.shipment_line_id

              and rsl.shipment_header_id=rsh.shipment_header_id

              and rsh.ship_to_org_id = ood.organization_id

              order by rct.transaction_id