1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to get the Purchased item details in R12

Discussion in 'Oracle SCM & Manufacturing' started by gurujothi, Aug 1, 2012.

  1. gurujothi

    gurujothi Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    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,received_quantity,
    returned_quantity.
    Thank you.

    Regards,
    Gurujothi.
     
  2. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    You need from Sales Order and Purchase Orders about the purchased items??
     
  3. gurujothi

    gurujothi Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Hi Bharat G,
    Thank you for your response,
    The purchased item details are stored in
    po_requisition_headers_all,po_requisition_lines_all,
    po_line_locations_all,RCV_TRANSACTIONS and also in other tables
    I want to get the details regarding,
    Lets say Item aaa,How much ordered(quantity),
    vendor_name,quantity_received,quantity_returned to the vendor.

    Thank you.
     
  4. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi gurujothi,

    Here is the query which gives your exact requirement.

    Code (Text):


    SELECT
    PHA.SEGMENT1 "PO Number"
    ,ASP.VENDOR_NAME "Vendor Name"
    ,PLA.ITEM_ID "Item Id"
    ,MSIB.SEGMENT1 "Item Name"
    ,MSIB.ITEM_TYPE "Item Type"
    ,SUM(PDA.QUANTITY_ORDERED) "Quantity Ordered"
    ,sum(RT1.QUANTITY) "Delivered Quantity"
    ,SUM(PDA.QUANTITY_CANCELLED)"Quantity Cancelled"
    ,sum(RT.QUANTITY) "Returned Quantity"

    FROM

    PO_HEADERS_ALL PHA
    ,PO_LINES_ALL PLA
    ,PO_DISTRIBUTIONS_ALL PDA
    ,AP_SUPPLIERS ASP
    ,RCV_TRANSACTIONS RT
    ,RCV_TRANSACTIONS RT1
    ,MTL_SYSTEM_ITEMS_B MSIB

    WHERE
    --    PLA.ITEM_ID = 212046
    --    PHA.SEGMENT1 = '17'
    AND PLA.PO_LINE_ID = PDA.PO_LINE_ID
    AND PHA.PO_HEADER_ID = PLA.PO_HEADER_ID
    AND ASP.VENDOR_ID (+) = PHA.VENDOR_ID
    AND RT.PO_DISTRIBUTION_ID (+) = PDA.PO_DISTRIBUTION_ID
    AND RT.TRANSACTION_TYPE (+) = 'RETURN TO VENDOR'
    AND RT1.PO_DISTRIBUTION_ID (+) = PDA.PO_DISTRIBUTION_ID
    AND RT1.TRANSACTION_TYPE (+) = 'DELIVER'
    AND MSIB.INVENTORY_ITEM_ID (+) = PLA.ITEM_ID

    GROUP BY PHA.SEGMENT1, pha.vendor_id, ASP.VENDOR_NAME, PLA.ITEM_ID, MSIB.SEGMENT1, MSIB.CONTRACT_ITEM_TYPE_CODE, MSIB.ITEM_TYPE;

     
    This query gives all required fields what you required. Please test it once and let me know if anything goes wrong in it.