CREATE OR REPLACE PROCEDURE APPS.XX_RCV_INT
IS
l_vendor_id NUMBER := NULL;
l_uom VARCHAR2 (20) := NULL;
l_currency VARCHAR2(20) := NULL;
CURSOR c1
IS
SELECT *
FROM xxfz.xx_receiving_f
WHERE receipt_number = 1218;--posted_flag = 'Y';
CURSOR c2 (recpt_num IN NUMBER)
IS
SELECT *
FROM xxfz.xx_rcv_lots_f
WHERE receipt_number = recpt_num;
BEGIN
FOR hdr IN c1
LOOP
--
--GETTING VENDOR FROM PO
--
SELECT vendor_id
INTO l_vendor_id
FROM po_headers_all
WHERE po_header_id = hdr.po_header_id;
--
--GETTING UNIT OF MEASURE
--
SELECT primary_unit_of_measure
INTO l_uom
FROM mtl_system_items_b
WHERE inventory_item_id = hdr.item_id AND organization_id = 102;
--
--GETTING CURRENCY CODE
--
SELECT currency_code
INTO l_currency
FROM po_headers_all
WHERE po_header_id = hdr.po_header_id;
INSERT INTO rcv_headers_interface
(header_interface_id,
GROUP_ID, processing_status_code, receipt_source_code,
transaction_type, last_update_date, last_updated_by,
last_update_login, vendor_id, expected_receipt_date,
validation_flag, receipt_num
)
VALUES (rcv_headers_interface_s.NEXTVAL,
rcv_interface_groups_s.NEXTVAL, 'PENDING', 'VENDOR',
'NEW', SYSDATE, -1,
0, l_vendor_id, SYSDATE,
'Y', hdr.receipt_number
);
FOR lines IN c2 (hdr.receipt_number)
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, item_id, quantity,
unit_of_measure, auto_transact_code,
receipt_source_code, to_organization_code,
source_document_code, po_header_id, -- document_num,
header_interface_id, validation_flag, currency_code
)
VALUES (rcv_transactions_interface_s.NEXTVAL,
rcv_interface_groups_s.CURRVAL, SYSDATE, -1,
SYSDATE, -1, 0,
'RECEIVE', SYSDATE,
'PENDING', 'BATCH',
'PENDING', hdr.item_id, lines.lot_number,
l_uom, 'RECEIVE',
'VENDOR', 'FGN',
'PO', hdr.po_header_id, --740,
rcv_headers_interface_s.CURRVAL, 'Y', l_currency
);
END LOOP;
END LOOP;
end;
/
