Automatic Receipt Creation during Inter Organization Transfer in R12
In 11i, a receipt would be created automatically during Inter Organization Transfer.In case of R12, we have to create a receipt manually. The process described below helps to achieve automatic receipt creation automatically in R12 during Inter Organization Transfer.
This can be achieved through Receipt Interface.
During Inter Organization a record would be inserted in RCV_SHIPMENT_HEADERS and RCV_SHIPMENT_LINES be the system itself despite MTL_MATERIAL_TRANSACTIONS.So
we need to use Receipt Interface to create a record in RCV_TRANSACTIONS and a record would be created in MTL_MATERIAL_TRANSACTIONS indicating the item being taken into the receiving organization.
Step 1:
A trigger has to be written in RCV_SHIPMENT_LINES to trigger a custom procedure which would insert record into interface tables.
CREATE OR REPLACE TRIGGER APPS.XX_AUTO_RCPT_TRG
AFTER INSERT
ON PO.RCV_SHIPMENT_LINES
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
pragma AUTONOMOUS_TRANSACTION;
l_tran_type NUMBER;
l_return number;
BEGIN
fnd_global.apps_initialize (user_id, resp_id,appl_id );
l_return := Fnd_Request.submit_request(application=>'PO',
program=>'XX_INTERORG_RCPT_PRCE',
description=>'XX_INTERORG_RCPT_PRCE',
start_time=>TO_CHAR(SYSDATE + 2 / 1440,'dd-mon-yy hh24:mi:ss'),
sub_request=>FALSE,
argument1=>:NEW.shipment_line_id,
argument2=>:NEW.created_by,
argument3=>:NEW.shipment_header_id,
argument4=>:NEW.mmt_transaction_id,
argument5=>:NEW.quantity_shipped,
argument6=>:NEW.unit_of_measure,
argument7=>:NEW.item_id,
argument8=>:NEW.to_organization_id,
argument9=>:NEW.from_organization_id,
argument10=>:NEW.secondary_quantity_shipped,
argument11=>:NEW.secondary_unit_of_measure,
argument12=>:NEW.category_id,
argument13=>:NEW.item_description,
argument14=>:NEW.primary_unit_of_measure,
argument15=>:NEW.locator_id,
argument16=>:NEW.destination_context);
commit;
END;
Step 2:
A custom procedure is used to insert records into interface table and call the standard concurrent program Receiving Transaction Processor and India - Receiving Transaction Processor if India Localization is used.
CREATE OR REPLACE PROCEDURE APPS.XX_INTERORG_RCPT_PRCE(errbuf out varchar2,retbuf out varchar2,p_shipment_line_id NUMBER,
p_user_id NUMBER,
p_shipment_header_id NUMBER,
p_transaction_id NUMBER,
p_quantity_shipped NUMBER,
p_unit_of_measure VARCHAR2,
p_item_id NUMBER,
p_to_organization_id NUMBER,
p_from_organization_id NUMBER,
p_secondary_quantity_shipped NUMBER,
p_secondary_unit_of_measure VARCHAR2,
p_category_id NUMBER,
p_item_description VARCHAR2,
p_primary_unit_of_measure VARCHAR2,
p_locator_id NUMBER,
p_destination_context VARCHAR2)
IS
l_tran_type NUMBER;
l_uom_code VARCHAR2(5);
l_group_id NUMBER;
l_ou NUMBER;
l_return NUMBER;
l_header_check VARCHAR2(3);
l_header_check_cnt NUMBER;
l_header_check_tran NUMBER;
l_rtp_validate NUMBER;
l_hdr_interface_value NUMBER;
l_ret NUMBER;
BEGIN
fnd_file.put_line (fnd_file.LOG,'Inside the procedure');
BEGIN
select mmt.transaction_type_id INTO l_tran_type
from rcv_shipment_lines rsl,mtl_material_transactions mmt
where rsl.mmt_transaction_id = mmt.transaction_id
and rsl.shipment_line_id = p_shipment_line_id;
EXCEPTION
WHEN OTHERS THEN
l_tran_type := 2;
END;
BEGIN
SELECT uom_code INTO l_uom_code
FROM mtl_units_of_measure
WHERE unit_of_measure = p_unit_of_measure;
EXCEPTION
WHEN OTHERS THEN
l_uom_code := '';
END;
IF l_tran_type = 21 THEN
fnd_file.put_line (fnd_file.LOG,'Inside Transaction Type If');
BEGIN
SELECT transaction_id INTO l_header_check_tran
FROM
(SELECT transaction_id
FROM mtl_material_transactions
WHERE shipment_number in
(SELECT SHIPMENT_NUM FROM RCV_SHIPMENT_HEADERS
WHERE SHIPMENT_HEADER_ID = p_shipment_header_id)
AND organization_id = p_from_organization_id
ORDER BY transaction_id ASC)
WHERE ROWNUM = 1;
IF l_header_check_tran = p_transaction_id THEN
l_header_check := '2';
ELSE
l_header_check := '1';
END IF;
fnd_file.put_line (fnd_file.LOG,'l_header_check_tran:'||l_header_check_tran);
fnd_file.put_line (fnd_file.LOG,'l_header_check:'||l_header_check);
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.LOG,'Inside Header Check Exception');
END;
IF l_header_check = '2' THEN
fnd_file.put_line (fnd_file.LOG,'Inside Header Check IF');
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,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
EXPECTED_RECEIPT_DATE,
VALIDATION_FLAG,
COMMENTS,
SHIPMENT_NUM,
FROM_ORGANIZATION_ID,
SHIP_TO_ORGANIZATION_ID,
ORG_ID
)
(select RCV_HEADERS_INTERFACE_S.NEXTVAL, --HEADER_INTERFACE_ID
RCV_INTERFACE_GROUPS_S.NEXTVAL, --GROUP_ID
'PENDING', --- PROCESSING_STATUS_CODE,
'INVENTORY', --- RECEIPT_SOURCE_CODE,
'NEW', --- TRANSACTION_TYPE,
'RECEIVE', --- AUT_TRANSACT_CODE,
SYSDATE, --- LAST_UPDATE_DATE,
p_user_id, --- LAST_UPDATE_BY,
SYSDATE, --- CREATION_DATE,
p_user_id, --- CREATED_BY,
p_user_id, --- LAST_UPDATE_LOGIN,
SYSDATE, -- EXCEPTED_RECEIPT_DATE
'Y',--VALIDATION_FLAG
null,--COMMENTS
rsh.shipment_num,--SHIPMENT_NUMBER
rsh.organization_id,--FROM_ORGANIZATION_ID
ship_to_org_id, --SHIP_TO_ORGANIZATION_ID
ood.operating_unit
from rcv_shipment_headers rsh,org_organization_definitions ood
where rsh.shipment_header_id = p_shipment_header_id
and rsh.organization_id = ood.organization_id);
INSERT INTO rcv_transactions_interface
(
INTERFACE_TRANSACTION_ID,
HEADER_INTERFACE_ID,
GROUP_ID,
TRANSACTION_TYPE,
TRANSACTION_DATE,
PROCESSING_STATUS_CODE,
PROCESSING_MODE_CODE,
TRANSACTION_STATUS_CODE,
QUANTITY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
RECEIPT_SOURCE_CODE,
DESTINATION_TYPE_CODE,
AUTO_TRANSACT_CODE,
SOURCE_DOCUMENT_CODE,
UNIT_OF_MEASURE,
ITEM_ID,
UOM_CODE,
SHIPMENT_HEADER_ID,
SHIPMENT_LINE_ID,
TO_ORGANIZATION_ID,
FROM_ORGANIZATION_ID,
SHIPMENT_NUM ,
EXPECTED_RECEIPT_DATE ,
INTERFACE_SOURCE_CODE ,
VALIDATION_FLAG,
SECONDARY_QUANTITY,
SECONDARY_UNIT_OF_MEASURE,
ORG_ID,
CATEGORY_ID,
ITEM_DESCRIPTION,
PRIMARY_QUANTITY,
PRIMARY_UNIT_OF_MEASURE,
LOCATOR_ID,
DESTINATION_CONTEXT
)
(SELECT
RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL, --- INTERFACE_TRANSACTION_ID,
RCV_HEADERS_INTERFACE_S.CURRVAL, --- HEADER_INTERFACE_ID,
RCV_INTERFACE_GROUPS_S.CURRVAL, --- GROUP_ID,
'RECEIVE', --- TRANSACTION_TYPE,
SYSDATE, --- TRANSACTION_DATE,
'PENDING', --- PROCESSING_STATUS_CODE,
'BATCH', --- PROCESSING_MODE_CODE,
'PENDING', --- TRANSACTION_STATUS_CODE,
p_quantity_shipped,--QUANTITY_SHIPPED
SYSDATE, --- LAST_UPDATE_DATE,
p_user_id, --- LAST_UPDATE_BY,
SYSDATE, --- CREATION_DATE,
p_user_id, --- CREATED_BY,
p_user_id, --LAST_UPDATE_LOGIN
'INVENTORY', --- RECEIPT_SOURCE_CODE,
'INVENTORY', --- DESTINATION_TYPE_CODE,
'DELIVER', --- AUT_TRANSACT_CODE,
'INVENTORY', --- SOURCE_DOCUMENT_CODE,
p_unit_of_measure,--UNIT_OF_MEASURE
p_item_id,--ITEM_ID ,
l_uom_code,--UOM_CODE
rsh.SHIPMENT_HEADER_ID,--SHIPMENT_HEADER_ID
p_shipment_line_id,--SHIPMENT_LINE_ID
p_to_organization_id,--TO_ORGANIZATION_ID
p_from_organization_id,--FROM_ORGANIZATION_ID
rsh.SHIPMENT_NUM,--SHIPMENT_NUM
rsh.EXPECTED_RECEIPT_DATE,--EXPECTED_RECEIPT_DATE
'RCV',--INTERFACE_SOURCE_CODE
'Y',--VALIDATION_FLAG
p_secondary_quantity_shipped,--SECONDARY_QUANTITY_SHIPPED
p_secondary_unit_of_measure,--SECONDARY_UNIT_OF_MEASURE
ood.OPERATING_UNIT,--ORG_ID
p_category_id,--CATEGORY_ID
p_item_description,--ITEM_DESCRIPTION
p_quantity_shipped,--PRIMARY_QUANTITY
p_primary_unit_of_measure,
p_locator_id,
p_destination_context
from rcv_shipment_headers rsh,org_organization_definitions ood
where shipment_header_id = p_shipment_header_id
and rsh.organization_id = ood.organization_id
);
SELECT RCV_INTERFACE_GROUPS_S.CURRVAL INTO l_group_id
FROM DUAL;
COMMIT;
ELSE
fnd_file.put_line (fnd_file.LOG,'Inside Header Check Else');
dbms_lock.sleep (5);
SELECT header_interface_id INTO l_hdr_interface_value
FROM rcv_headers_interface
WHERE shipment_num IN
(SELECT SHIPMENT_NUM FROM RCV_SHIPMENT_HEADERS
WHERE SHIPMENT_HEADER_ID = p_shipment_header_id)
AND from_organization_id = p_from_organization_id;
fnd_file.put_line (fnd_file.LOG,'l_hdr_interface_value:'||l_hdr_interface_value);
SELECT group_id INTO l_group_id
FROM rcv_headers_interface
WHERE shipment_num IN
(SELECT SHIPMENT_NUM FROM RCV_SHIPMENT_HEADERS
WHERE SHIPMENT_HEADER_ID = p_shipment_header_id)
AND from_organization_id = p_from_organization_id;
fnd_file.put_line (fnd_file.LOG,'l_group_id:'||l_group_id);
INSERT INTO rcv_transactions_interface
(
INTERFACE_TRANSACTION_ID,
HEADER_INTERFACE_ID,
GROUP_ID,
TRANSACTION_TYPE,
TRANSACTION_DATE,
PROCESSING_STATUS_CODE,
PROCESSING_MODE_CODE,
TRANSACTION_STATUS_CODE,
QUANTITY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
RECEIPT_SOURCE_CODE,
DESTINATION_TYPE_CODE,
AUTO_TRANSACT_CODE,
SOURCE_DOCUMENT_CODE,
UNIT_OF_MEASURE,
ITEM_ID,
UOM_CODE,
SHIPMENT_HEADER_ID,
SHIPMENT_LINE_ID,
TO_ORGANIZATION_ID,
FROM_ORGANIZATION_ID,
SHIPMENT_NUM ,
EXPECTED_RECEIPT_DATE ,
INTERFACE_SOURCE_CODE ,
VALIDATION_FLAG,
SECONDARY_QUANTITY,
SECONDARY_UNIT_OF_MEASURE,
ORG_ID,
CATEGORY_ID,
ITEM_DESCRIPTION,
PRIMARY_QUANTITY,
PRIMARY_UNIT_OF_MEASURE,
LOCATOR_ID,
DESTINATION_CONTEXT
)
(SELECT
RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL, --- INTERFACE_TRANSACTION_ID,
l_hdr_interface_value, --- HEADER_INTERFACE_ID,
l_group_id, --- GROUP_ID,
'RECEIVE', --- TRANSACTION_TYPE,
SYSDATE, --- TRANSACTION_DATE,
'PENDING', --- PROCESSING_STATUS_CODE,
'BATCH', --- PROCESSING_MODE_CODE,
'PENDING', --- TRANSACTION_STATUS_CODE,
p_quantity_shipped,--QUANTITY_SHIPPED
SYSDATE, --- LAST_UPDATE_DATE,
p_user_id, --- LAST_UPDATE_BY,
SYSDATE, --- CREATION_DATE,
p_user_id, --- CREATED_BY,
p_user_id, --LAST_UPDATE_LOGIN
'INVENTORY', --- RECEIPT_SOURCE_CODE,
'INVENTORY', --- DESTINATION_TYPE_CODE,
'DELIVER', --- AUT_TRANSACT_CODE,
'INVENTORY', --- SOURCE_DOCUMENT_CODE,
p_unit_of_measure,--UNIT_OF_MEASURE
p_item_id,--ITEM_ID ,
l_uom_code,--UOM_CODE
rsh.SHIPMENT_HEADER_ID,--SHIPMENT_HEADER_ID
p_shipment_line_id,--SHIPMENT_LINE_ID
p_to_organization_id,--TO_ORGANIZATION_ID
p_from_organization_id,--FROM_ORGANIZATION_ID
rsh.SHIPMENT_NUM,--SHIPMENT_NUM
rsh.EXPECTED_RECEIPT_DATE,--EXPECTED_RECEIPT_DATE
'RCV',--INTERFACE_SOURCE_CODE
'Y',--VALIDATION_FLAG
p_secondary_quantity_shipped,--SECONDARY_QUANTITY_SHIPPED
p_secondary_unit_of_measure,--SECONDARY_UNIT_OF_MEASURE
ood.OPERATING_UNIT,--ORG_ID
p_category_id,--CATEGORY_ID
p_item_description,--ITEM_DESCRIPTION
p_quantity_shipped,--PRIMARY_QUANTITY
p_primary_unit_of_measure,
p_locator_id,
p_destination_context
from rcv_shipment_headers rsh,org_organization_definitions ood
where shipment_header_id = p_shipment_header_id
and rsh.organization_id = ood.organization_id
);
COMMIT;
END IF;
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)
(SELECT
mtl_material_transactions_s.nextval,--transaction_interface_id
SYSDATE, --last_update_date
p_user_id, --last_updated_by
SYSDATE, --creation_date
p_user_id, --created_by
p_user_id, --last_update_login
lot_number, --lot_number
abs(transaction_quantity), --transaction_quantity
abs(primary_quantity), --primary_quantity
NULL, --serial_transaction_temp_id
'RCV', --product_code
RCV_TRANSACTIONS_INTERFACE_S.CURRVAL --product_transaction_id
FROM MTL_TRANSACTION_LOT_NUMBERS
WHERE transaction_id = p_transaction_id
);
commit;
SELECT operating_unit INTO l_ou
FROM org_organization_definitions
WHERE organization_id = p_from_organization_id;
fnd_file.put_line (fnd_file.LOG,'l_ou:'||l_ou);
SELECT transaction_id INTO l_rtp_validate
FROM
(SELECT transaction_id
FROM mtl_material_transactions
WHERE shipment_number in
(SELECT SHIPMENT_NUM FROM RCV_SHIPMENT_HEADERS
WHERE SHIPMENT_HEADER_ID = p_shipment_header_id)
AND organization_id = p_from_organization_id
ORDER BY transaction_id DESC)
WHERE ROWNUM = 1;
fnd_file.put_line (fnd_file.LOG,'l_rtp_validate:'||l_rtp_validate);
IF p_transaction_id = l_rtp_validate THEN
fnd_global.apps_initialize (p_user_id, resp_id, appl_id);
l_return := Fnd_Request.submit_request(application=>'PO',
program=>'RVCTP',
description=>'Receiving Transaction Processor',
start_time=>SYSDATE,
sub_request=>FALSE,
argument1=>'BATCH',
argument2=>l_group_id,
argument3=>l_ou);
fnd_global.apps_initialize (p_user_id, resp_id, appl_id);
l_ret := FND_REQUEST.SUBMIT_REQUEST(
'JA', -- Modified by Ramananda for Bug# 4336482 (Changed PO to JA)
'JAINRVCTP',
'India - Receiving Transaction Processor', -- Commented "for Receipt" By Rallamse for Bug 4336482
TO_CHAR(SYSDATE + 2 / 1440,'dd-mon-yy hh24:mi:ss'), FALSE,
to_char(p_to_organization_id), '', '', '',
'', p_shipment_header_id, '', '', '', 'Y', 'JAINPORE', 'N', 'N'
);
END IF;
commit;
END IF;
END;
/
No comments:
Post a Comment