This discussion is archived
1 Reply Latest reply: Dec 5, 2012 2:36 PM by 870775 RSS

RCV_TRANSACTIONS_INTERFACE and quantity/tolerance error

870775 Newbie
Currently Being Moderated
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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points