Oracle Knowledge Center: Intransit Receipts using Receiving Open Interface - How to Receive Intransit Shipment (Inter-org transfer) for Lot / Serial Controlled Items via Receiving Open Interface

Wednesday, August 5, 2015

Intransit Receipts using Receiving Open Interface - How to Receive Intransit Shipment (Inter-org transfer) for Lot / Serial Controlled Items via Receiving Open Interface

/* Formatted on 8/5/2015 2:46:34 PM (QP5 v5.265.14096.37972) */
DECLARE
l_transaction_id NUMBER;
L_GROUP_ID NUMBER := rcv_interface_groups_s.NEXTVAL;
BEGIN
l_transaction_id := rcv_transactions_interface_s.NEXTVAL;

INSERT INTO RCV_HEADERS_INTERFACE (HEADER_INTERFACE_ID,
GROUP_ID,
PROCESSING_STATUS_CODE,
RECEIPT_SOURCE_CODE,
TRANSACTION_TYPE,
AUTO_TRANSACT_CODE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
--VENDOR_ID,
from_organization_id,
SHIP_TO_ORGANIZATION_ID,
EXPECTED_RECEIPT_DATE,
VALIDATION_FLAG,
ORG_ID,
SHIPMENT_NUM)
VALUES (rcv_headers_interface_s.NEXTVAL, --Header_Interface_Id
L_GROUP_ID, --Group_Id
'PENDING', --Processing_Status_Code
'INVENTORY', --Receipt_Source_Code
'RECEIVE', --Transaction_Type
'DELIVER', --Auto_Transact_Code
SYSDATE, --Last_Update_Date
0, --Last_Updated_By
0, --Last_Update_Login
SYSDATE, --Creation_Date
0, --Created_By
-- 7927, --Vendor_Id
5809,
5682, --Ship_To_Organization_Id,
SYSDATE, --Expected_Receipt_Date
'Y', --Validation_Flag
83, --Org_id_Operating_Unit_Id
'427722');

INSERT INTO apps.rcv_transactions_interface (interface_transaction_id,
GROUP_ID,
last_update_date,
last_updated_by,
creation_date,
created_by,
transaction_type,
transaction_date,
processing_status_code,
processing_mode_code,
transaction_status_code,
quantity,
uom_code,
interface_source_code,
item_id,
employee_id,
shipment_header_id,
shipment_line_id,
receipt_source_code,
from_organization_id,
to_organization_id,
source_document_code,
destination_type_code,
subinventory,
shipment_num,
-- SHIPPED_DATE,
--expected_receipt_date,
validation_flag,
locator_id,
UNIT_OF_MEASURE,
AUTO_TRANSACT_CODE,
PRIMARY_QUANTITY,
PRIMARY_UNIT_OF_MEASURE,
ROUTING_HEADER_ID,
ROUTING_STEP_ID,
INSPECTION_STATUS_CODE,
USE_MTL_LOT,
-- USE_MTL_SERIAL,
CATEGORY_ID,
org_id,
deliver_to_location_id,
header_interface_id)
VALUES (l_transaction_id, --INTERFACE_TRANSACTION_ID
l_group_id, --GROUP_ID
SYSDATE, --LAST_UPDATE_DATE
19087, --LAST_UPDATED_BY
SYSDATE, --CREATION_DATE
19087, --CREATED_BY
'RECEIVE', --TRANSACTION_TYPE
SYSDATE, --nvl(fnd_date.canonical_to_date(p_receipt_date),sysdate),--TRANSACTION_DATE
'PENDING', --PROCESSING_STATUS_CODE
'BATCH', --PROCESSING_MODE_CODE
'PENDING', --TRANSACTION_STATUS_CODE
20, --QUANTITY
'EA', --UNIT_OF_MEASURE
'RCV', --INTERFACE_SOURCE_CODE
8419692, --ITEM_ID
19087, --EMPLOYEE_ID
--‘DELIVER’, --AUTO_TRANSACT_CODE
4214924, --SHIPMENT_HEADER_ID
8588696, --SHIPMENT_LINE_ID
'INVENTORY', --RECEIPT_SOURCE_CODE
5809, -- From org ID
5682, --TO_ORGANIZATION_ID
'INVENTORY', --SOURCE_DOCUMENT_CODE
'INVENTORY', --DESTINATION_TYPE_CODE
'FG', --SUBINVENTORY
'427722', --SHIPMENT_NUM
-- SYSDATE, --EXPECTED_RECEIPT_DATE,
'Y', --VALIDATION_FLAG
570721, --l_locator_id
'EACH',
'DELIVER',
20,
'EACH',
3,
1,
'NOT INSPECTED',
1,
-- 1,
144,
83,
183717,
rcv_headers_interface_s.CURRVAL);

INSERT INTO MTL_TRANSACTION_LOTS_INTERFACE (TRANSACTION_INTERFACE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
LOT_NUMBER,
TRANSACTION_QUANTITY,
PRIMARY_QUANTITY,
-- SERIAL_TRANSACTION_TEMP_ID,
PRODUCT_CODE,
PRODUCT_TRANSACTION_ID)
VALUES (MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL, --TRANSACTION_INTERFACE_ID
SYSDATE, --LAST_UPDATE_DATE
19087, --LAST_UPDATED_BY
SYSDATE, --CREATION_DATE
19087, --CREATED_BY
19087, --LAST_UPDATE_LOGIN
'41098035', --LOT_NUMBER
20, --TRANSACTION_QUANTITY
20, --PRIMARY_QUANTITY
--MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL, --SERIAL_TRANSACTION_TEMP_ID
'RCV', --PRODUCT_CODE
l_transaction_id --PRODUCT_TRANSACTION_ID
);

COMMIT;
END;
/