1 Reply Latest reply: Dec 5, 2012 4:36 PM by 870775 RSS

    RCV_TRANSACTIONS_INTERFACE and quantity/tolerance error

    870775
      Hi gurus,

      We are using 11.5.10.2 and I am trying to return receipts that has never been invoiced. I am using the following code to insert data into the interface table for a single receipt:

      DECLARE
      V_GROUP_ID NUMBER;

      BEGIN
      V_GROUP_ID := rcv_interface_groups_s.nextval;

      FOR C IN (Select DISTINCT RSH.RECEIPT_NUM ,
      PH.SEGMENT1 PO_NUMBER,
      RT.TRANSACTION_ID ,
      RT.TRANSACTION_TYPE ,
      RT.TRANSACTION_DATE ,
      RT.QUANTITY ,
      RT.UNIT_OF_MEASURE ,
      RT.SHIPMENT_HEADER_ID ,
      RT.SHIPMENT_LINE_ID ,
      RT.SOURCE_DOCUMENT_CODE ,
      RT.DESTINATION_TYPE_CODE ,
      RT.EMPLOYEE_ID ,
      RT.PARENT_TRANSACTION_ID ,
      RT.PO_HEADER_ID ,
      RT.PO_LINE_ID ,
      PL.LINE_NUM ,
      PL.ITEM_ID ,
      PL.ITEM_DESCRIPTION ,
      RT.PO_UNIT_PRICE,
      PL.UNIT_PRICE ,
      RT.PO_LINE_LOCATION_ID ,
      RT.PO_DISTRIBUTION_ID ,
      RT.ROUTING_HEADER_ID,
      RT.ROUTING_STEP_ID ,
      RT.DELIVER_TO_PERSON_ID ,
      RT.DELIVER_TO_LOCATION_ID ,
      RT.VENDOR_ID ,
      RT.VENDOR_SITE_ID ,
      RT.ORGANIZATION_ID ,
      RT.SUBINVENTORY ,
      RT.LOCATOR_ID ,
      RT.LOCATION_ID,
      RSH.SHIP_TO_ORG_ID
      From
      RCV_TRANSACTIONS RT,
      RCV_SHIPMENT_HEADERS RSH,
      RCV_SHIPMENT_LINES RSL,
      PO_HEADERS_ALL PH,
      PO_LINES_ALL PL,
      XXAR_REVERSE_RECEIPTS_EXT X
      Where
      RSH.RECEIPT_NUM = X.RECEIPT_NUMBER
      AND PH.SEGMENT1 = X.PO_NUMBER
      AND PH.PO_HEADER_ID = PL.PO_HEADER_ID
      AND RT.PO_HEADER_ID = PH.PO_HEADER_ID
      AND RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
      and pl.line_num = X.PO_LINE
      and RT.DESTINATION_TYPE_CODE = X.DESTINATION_TYPE_CODE
      --AND RT.QUANTITY = X.RECEIPT_QTY
      AND RSL.LINE_NUM = X.RECEIPT_LINE
      AND RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID
      AND RT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
      AND X.RECEIPT_NUMBER = '68173') LOOP

      INSERT INTO RCV_TRANSACTIONS_INTERFACE
      (INTERFACE_TRANSACTION_ID,
      GROUP_ID,
      LAST_UPDATE_DATE,
      LAST_UPDATED_BY,
      CREATION_DATE,
      CREATED_BY,
      LAST_UPDATE_LOGIN,
      TRANSACTION_TYPE,
      TRANSACTION_DATE,
      PROCESSING_STATUS_CODE,
      PROCESSING_MODE_CODE,
      TRANSACTION_STATUS_CODE,
      QUANTITY,
      UNIT_OF_MEASURE,
      ITEM_ID,
      EMPLOYEE_ID,
      SHIPMENT_HEADER_ID,
      SHIPMENT_LINE_ID,
      RECEIPT_SOURCE_CODE,
      VENDOR_ID,
      FROM_ORGANIZATION_ID,
      FROM_SUBINVENTORY,
      FROM_LOCATOR_ID,
      SOURCE_DOCUMENT_CODE,
      PARENT_TRANSACTION_ID,
      PO_HEADER_ID,
      PO_LINE_ID,
      PO_LINE_LOCATION_ID,
      PO_DISTRIBUTION_ID,
      DESTINATION_TYPE_CODE,
      DELIVER_TO_PERSON_ID,
      LOCATION_ID,
      DELIVER_TO_LOCATION_ID,
      VALIDATION_FLAG
      )
      VALUES
      (rcv_transactions_interface_s.nextval, --INTERFACE_TRANSACTION_ID
      V_GROUP_ID, --GROUP_ID
      SYSDATE, --LAST_UPDATE_DATE
      1128, --LAST_UPDATE_BY
      SYSDATE, --CREATION_DATE
      1128, --CREATED_BY
      null, --LAST_UPDATE_LOGIN
      'RETURN TO VENDOR', --TRANSACTION_TYPE   OR 'RETURN TO RECEIVING'
      '25-OCT-2012', --TRANSACTION_DATE
      'PENDING', --PROCESSING_STATUS_CODE
      'BATCH', --PROCESSING_MODE_CODE
      'PENDING', --TRANSACTION_STATUS_CODE
      c.quantity, --QUANTITY
      c.unit_of_measure, --UNIT_OF_MEASURE
      c.item_id, --ITEM_ID
      NULL, --EMPLOYEE_ID
      c.shipment_header_id, --SHIPMENT_HEADER_ID
      c.shipment_line_id, --SHIPMENT_LINE_ID
      'VENDOR', --RECEIPT_SOURCE_CODE   ???
      c.vendor_id, --VENDOR_ID
      c.organization_id, --FROM_ORGANIZATION_ID
      null, --FROM_SUBINVENTORY
      null, --FROM_LOCATOR_ID
      c.source_document_code, --SOURCE_DOCUMENT_CODE
      c.parent_transaction_id, --PARENT_TRANSACTION_ID
      c.po_header_id, --PO_HEADER_ID
      c.po_line_id, --PO_LINE_ID
      c.po_line_location_id, --PO_LINE_LOCATION_ID
      c.po_distribution_id, --PO_DISTRIBUTION_ID
      c.destination_type_code, --DESTINATION_TYPE_CODE
      c.deliver_to_person_id, --DELIVER_TO_PERSON_ID
      null, --LOCATION_ID
      c.deliver_to_location_id, --DELIVER_TO_LOCATION_ID
      'Y' --VALIDATION_FLAG
      );
      END LOOP;
      commit;
      END;

      After running the ROI program, I get the following error: +"The Quantity entered for receipt is greater than the available Quantity. The sum of quantity to be received and already received, 47.84, is greater than the sum of ordered quantity and over receipt tolerance, 0"+

      Not sure why this is happening. I have no problems returning the receipt manually in the applications. Any help is appreciated.

      Thank you in advanced.