4 Replies Latest reply: Apr 12, 2012 12:18 PM by Dipanjan Maitra RSS

    Oracle Purchasing / Receiving / Inspection

    749667
      Hi,
      I have tried searching for this in number of places. But, unsuccessful. I am looking for a query that will get me the items that are pending inspection - post receipt. We are still on 1.0.3. I know I know it a very old version. So, if you can get me a query for any ERP version, I should be able to tweak it to our version.

      Karthik
        • 1. Re: Oracle Purchasing / Receiving / Inspection
          Sandeep Gandhi, Consultant
          You can look into rcv_transactions for those po_line_ids that have a record with a transaction_type of RECEIVE but don't have record with a transaction_type of accept.

          Keep in mind that for a received quantity of 10, you may have delivered 3 first and then 1 later.
          So you need to add up all quantities with transaction type of ACCEPT and then compare to the RECEIVE quantities.

          Also keep in mind that corrections will add complexity to your query.

          Sandeep Gandhi
          • 2. Re: Oracle Purchasing / Receiving / Inspection
            Dipanjan Maitra
            SELECT rct.transaction_id parent_transaction_Id,
            rct.GROUP_ID,
            rsh.receipt_num,
            rct.quantity,
            pol.item_id,
            rct.unit_of_measure,
            poh.po_header_id,
            pol.po_line_id,
            poh.segment1 PO_Number,
            poh.attribute1 attribute1,
            poh.org_id org_id,
            pol.item_description item_description,
            pol.line_num line_num,
            poh.vendor_id,
            poh.vendor_site_id,
            rsh.ship_to_location_id,
            rsh.organization_id,
            pll.ship_to_organization_id,
            rct.employee_id,
            rct.destination_type_code,
            rct.deliver_to_person_id,
            rct.deliver_to_location_id,
            pll.line_location_id
            FROM apps.po_headers_all poh,
            apps.po_lines_all pol,
            apps.po_line_locations_all pll,
            apps.rcv_transactions rct,
            apps.rcv_shipment_headers rsh,
            apps.rcv_shipment_lines rsl
            WHERE rsh.shipment_header_id = rsl.shipment_header_id
            AND rsl.po_line_location_id =
            rct.po_Line_location_id
            AND rsl.po_line_location_id = pll.Line_location_id
            AND rct.po_line_location_id = pll.Line_location_id
            AND poh.po_header_id = pol.po_header_id
            AND poh.po_header_id = rct.po_header_id
            AND pol.po_line_id = rct.po_line_id
            AND rct.transaction_type IN ('RECEIVE')
            AND NOT EXISTS
            (SELECT 1
            FROM apps.rcv_transactions rct1
            WHERE poh.po_header_id = rct1.po_header_id
            AND pol.po_line_id = rct1.po_line_id
            AND rct1.transaction_type =
            'ACCEPT')
            • 3. Re: Oracle Purchasing / Receiving / Inspection
              Prakash Kumar
              Hi Karthik,

              Dipanjan's query will more or less serve your purpose. But the query doesn't consider the receipt routing type, which means it will give those records also where the routing was either "Standard" or "Direct". Also as Sandeep said, you have to consider the other scenarios also like when the item is being returned or receipt is being corrected or multiple receiving has been done as these records may give you repeated records for receipt number.


              Thanks
              Prakash Kumar
              • 4. Re: Oracle Purchasing / Receiving / Inspection
                Dipanjan Maitra
                Did the SQL meet your Scenario?

                Dipanjan