6 Replies Latest reply: Sep 14, 2012 2:38 PM by Ashutosh(297484) RSS

    How to get the Purchased item details in R12

    Gurujothi
      Hello everyone, am using Oracle Application R12,
      I need the following details for the all purchased item,
      how to get the item_id,item_name,vendor_name,ordered_quantity,rec eived_quantity,
      returned_quantity.
      Thank you.

      Regards,
      Gurujothi.
        • 1. Re: How to get the Purchased item details in R12
          Mahendra
          See if the below query helps

          select a.QUANTITY_BILLED,a.QUANTITY_CANCELLED,a.QUANTITY_DELIVERED,a.QUANTITY_ORDERED,b.ITEM_ID,c.SEGMENT1 ITEMNAME,d.QUANTITY Returned_Quantity,e.VENDOR_NAME
          from
          PO_DISTRIBUTIONS_ALL a,
          PO_LINES_ALL b,
          mtl_system_items_b c,
          RCV_transactions d,
          po_vendors e
          where a.po_header_id=&po_header_id
          and a.PO_HEADER_ID=b.PO_HEADER_ID
          and b.ITEM_ID=c.INVENTORY_ITEM_ID
          and a.PO_HEADER_ID=d.PO_HEADER_ID
          and a.ORG_ID=c.ORGANIZATION_ID
          and d.VENDOR_ID=e.VENDOR_ID
          and d.TRANSACTION_TYPE='RETURN TO VENDOR'
          and a.ORG_ID=&ORG_ID --Purchasing OU

          HTH
          Mahendra
          • 2. Re: How to get the Purchased item details in R12
            Gurujothi
            Hi Mahendra,
            Thank you for your response,
            I am getting no rows returned for the above query.
            Thank you.
            • 3. Re: How to get the Purchased item details in R12
              Mahendra
              Gurujothi wrote:
              Hi Mahendra,
              Thank you for your response,
              I am getting no rows returned for the above query.
              Thank you.
              maybe for that particular PO..return was not performed..
              comment the po header id..condition...so that it will list all the po's which has a return to vendor transaction

              Mahendra
              • 4. Re: How to get the Purchased item details in R12
                Gurujothi
                Hi Mahendra,
                I have checked the table there is no Retun to Vendor details for the particular Po_header_id,I need all the item details even if it is not returned,so the rcv.quantity (returned_quantity) will be 0 for the item which are not returned. Right?So I need to get the all item details which are returned and as well as not returned. Kindly tell me How to modify the code?
                Thank you.
                • 5. Re: How to get the Purchased item details in R12
                  Sanjay Desai EBS
                  Hi Gurujothi,

                  pl.try the following SQL. it will give you complete details of purchasing.

                  SELECT A.SEGMENT1 PO_NO, B.LINE_NUM, C.SHIPMENT_NUM,
                  C.QUANTITY,C.QUANTITY_ACCEPTED,C.QUANTITY_BILLED,C.QUANTITY_CANCELLED,C.QUANTITY_RECEIVED,C.QUANTITY_REJECTED,D.QUANTITY QUANTITY_RETURNED,
                  B.ITEM_ID,E.DESCRIPTION,F.VENDOR_NAME
                  FROM PO_HEADERS_ALL A , PO_LINES_ALL B , PO_LINE_LOCATIONS_ALL C , RCV_TRANSACTIONS D, MTL_SYSTEM_ITEMS_B E , PO_VENDORS F
                  WHERE A.ORG_ID = &OU_NAME
                  AND A.PO_HEADER_ID = &PO_HEADER_ID
                  AND B.PO_HEADER_ID = A.PO_HEADER_ID
                  AND C.PO_LINE_ID = B.PO_LINE_ID
                  AND D.PO_LINE_LOCATION_ID (+) = C.LINE_LOCATION_ID
                  AND D.TRANSACTION_TYPE (+) ='RETURN TO VENDOR'
                  AND E.INVENTORY_ITEM_ID = B.ITEM_ID
                  AND E.ORGANIZATION_ID = C.SHIP_TO_ORGANIZATION_ID
                  AND F.VENDOR_ID=A.VENDOR_ID

                  regards
                  sanjay
                  • 6. Re: How to get the Purchased item details in R12
                    Ashutosh(297484)
                    my reply maybe too late but if not it is worth exploring oracle seeded reports.
                    They maybe providing the information you seek.
                    Your report requirement sounds pretty standard hence the thought.