ln_user_id NUMBER:=1318;
ln_po_header_id NUMBER;
ln_vendor_id NUMBER;
lv_segment1 VARCHAR2(20);
ln_org_id NUMBER;
ln_line_num NUMBER;
ln_parent_txn_id NUMBER;
CURSOR po_line IS
SELECT pl.item_id,
pl.po_line_id,
pl.line_num,
pd.quantity_ordered quantity,
pd.po_distribution_id,
pl.unit_meas_lookup_code,
mp.organization_code,
pll.line_location_id,
pll.closed_code,
pll.quantity_received,
pll.cancel_flag,
pll.shipment_num
FROM po_lines_all pl,
po_line_locations_all pll,
po_distributions_all pd,
mtl_parameters mp
WHERE pl.po_header_id = ln_po_header_id
AND pl.po_line_id = pll.po_line_id
AND pd.line_location_id = pll.line_location_id
AND pd.po_line_id = pl.po_line_id
AND pll.ship_to_organization_id = mp.organization_id;
BEGIN
ln_user_id := 1318;
SELECT po_header_id,
vendor_id,
segment1,
org_id
INTO ln_po_header_id,
ln_vendor_id,
lv_segment1,
ln_org_id
FROM po_headers_all
WHERE segment1 =’6050′
AND org_id = 204;
INSERT INTO rcv_headers_interface
(header_interface_id,
group_id,
processing_status_code,
receipt_source_code,
transaction_type,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
vendor_id,
expected_receipt_date,
validation_flag,
org_id)
SELECT rcv_headers_interface_s.nextval,
rcv_interface_groups_s.nextval,
‘PENDING’,
‘VENDOR’,
‘NEW’,
sysdate,
ln_user_id,
sysdate,
ln_user_id,
0,
ln_vendor_id,
sysdate,
‘Y’,
ln_org_id
FROM dual;
FOR cur_po_line IN po_line
LOOP
IF cur_po_line.closed_code IN (‘APPROVED’, ‘OPEN’)
AND cur_po_line.quantity_received < cur_po_line.quantity
AND NVL(cur_po_line.cancel_flag,’N’) = ‘N’
THEN
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,
po_header_id,
po_line_id,
item_id,
quantity,
unit_of_measure,
po_line_location_id,
po_distribution_id,
destination_type_code,
destination_context,
auto_transact_code,
receipt_source_code,
to_organization_code,
source_document_code,
header_interface_id,
validation_flag,
org_id)
SELECT rcv_transactions_interface_s.nextval,
rcv_interface_groups_s.currval,
sysdate,
ln_user_id,
sysdate,
ln_user_id,
0,
‘RECEIVE’,
SYSDATE,
‘PENDING’,
‘BATCH’,
‘PENDING’,
ln_po_header_id,
cur_po_line.po_line_id,
cur_po_line.item_id,
cur_po_line.quantity,
cur_po_line.unit_meas_lookup_code,
cur_po_line.line_location_id,
cur_po_line.po_distribution_id,
‘RECEIVING’,
‘RECEIVING’,
‘RECEIVE’,
‘VENDOR’,
cur_po_line.organization_code,
‘PO’,
rcv_headers_interface_s.currval,
‘Y’,
ln_org_id
FROM dual;
ln_parent_txn_id := rcv_transactions_interface_s.currval;
INSERT INTO rcv_transactions_interface
(
parent_interface_txn_id,
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,
po_header_id,
po_line_id,
item_id,
quantity,
unit_of_measure,
po_line_location_id,
po_distribution_id,
destination_type_code,
destination_context,
auto_transact_code,
receipt_source_code,
to_organization_code,
source_document_code,
header_interface_id,
validation_flag,
org_id)
SELECT ln_parent_txn_id,
rcv_transactions_interface_s.nextval,
rcv_interface_groups_s.currval,
sysdate,
ln_user_id,
sysdate,
ln_user_id,
0,
‘DELIVER’,
SYSDATE,
‘PENDING’,
‘BATCH’,
‘PENDING’,
ln_po_header_id,
cur_po_line.po_line_id,
cur_po_line.item_id,
cur_po_line.quantity,
cur_po_line.unit_meas_lookup_code,
cur_po_line.line_location_id,
cur_po_line.po_distribution_id,
‘RECEIVING’,
‘RECEIVING’,
NULL,
‘VENDOR’,
cur_po_line.organization_code,
‘PO’,
rcv_headers_interface_s.currval,
‘Y’,
ln_org_id
FROM dual;
END IF;
END LOOP;
COMMIT;
END;
