SOA, Oracle Apps and OAF World: Script For Corrections of Purchase Order Receipts In Oracle E-Business Suite

Step1: Run the following script to find necessary information to be inserted into RCV_TRANSACTIONS_INTERFACE.

SELECT 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 ,
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,
PO_HEADERS_ALL PH,
PO_LINES_ALL PL
WHERE RSH.RECEIPT_NUM = '&RECEIPT_NUMBER'
AND PH.SEGMENT1 LIKE '&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;

Step: 2 Correct to Deliver to Inventory transaction for Purchase Order

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

rcv_interface_groups_s.nextval, --GROUP_ID

SYSDATE, --LAST_UPDATE_DATE

0, --LAST_UPDATE_BY

SYSDATE, --CREATION_DATE

0, --CREATED_BY

0, --LAST_UPDATE_LOGIN

'CORRECT', --TRANSACTION_TYPE

SYSDATE, --TRANSACTION_DATE

'PENDING', --PROCESSING_STATUS_CODE

'BATCH', --PROCESSING_MODE_CODE

'PENDING', --TRANSACTION_STATUS_CODE

50, --QUANTITY

'METRICTON', --UNIT_OF_MEASURE

208955, --ITEM_ID

25, --EMPLOYEE_ID

4874962 , --SHIPMENT_HEADER_ID

4865521, --SHIPMENT_LINE_ID

'VENDOR', --RECEIPT_SOURCE_CODE

557, --VENDOR_ID

204, --FROM_ORGANIZATION_ID

'Stores', --FROM_SUBINVENTORY

null, --FROM_LOCATOR_ID

'PO', --SOURCE_DOCUMENT_CODE

4935580 , --PARENT_TRANSACTION_ID

158205 , --PO_HEADER_ID

221717, --PO_LINE_ID

311543 , --PO_LINE_LOCATION_ID

317972, --PO_DISTRIBUTION_ID

'INVENTORY', --DESTINATION_TYPE_CODE

25, --DELIVER_TO_PERSON_ID

204, --LOCATION_ID

204, --DELIVER_TO_LOCATION_ID

'Y' --Validation_flag

);

Step: 3 Run the Receiving Transaction Processor for specific Group ID

Step: 4 Run the script to check weather data is inserted in proper way

Select * from RCV_TRANSACTIONS where PO_HEADER_ID=158205;

Step5: Correct to Receive transaction for Purchase Order