DECLARE
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;
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;
Good Blog, Thanks for sharing this informative article.
ReplyDeleteOracle Fusion HCM Online Training
Oracle Fusion SCM Online Training
Oracle Fusion Financials Online Training
Big Data and Hadoop Training In Hyderabad
Excellent blog I visit this blog it's really awesome. The important thing is that in this blog content written clearly and understandable. The content of information is very informative.
ReplyDeleteOracle Fusion HCM Online Training
Oracle Fusion SCM Online Training
Oracle Fusion Financials Online Training
Big Data and Hadoop Training In Hyderabad
Oracle Fusion HCM Training In Hyderabad
I would like to thank you for the efforts you have made in writing this article. I am hoping the same best work from you in the future as well.
ReplyDeleteOracle Fusion SCM Online Training
Oracle Integration Cloud Service Online Training