Po receipts interface in oracle apps r12
In this post, We will discuss how we can create po receipts
in oracle apps through
po receipts interface in oracle apps r12. Here we are going to Discuss
how to create po receipts in oracle apps through receiving interface tables. PO
receipts we do create to receive the Material from the suppliers against the
Purchase Orders. As such Oracle has not given any api to Create po receipts in
oracle apps. Here below is the complete code of po receipts interface in oracle apps r12.
Tables Involved in the po receipts interface in oracle apps r12
1.RCV_HEADERS_INTERFACE
2.RCV_TRANSACTIONS_INTERFACE
Step2: - We need to run the standard import program named “Receiving Transaction Processor”
to import the External Transactions from the Interface table to the PO
receipts Base Tables.
Step3:
- Once the standard
import program named “Receiving Transaction Processor” has been
completed successfully.
Example
of po receipts interface in oracle apps r12
ln_user_id NUMBER;
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,
--pll.quantity,
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 :=
144333;
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 =:PO_NUMBER
AND org_id =:P_ORG_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,
org_id)
SELECT rcv_headers_interface_s.nextval,
rcv_interface_groups_s.nextval,
'PENDING',
'VENDOR',
'NEW',
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,
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,
'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,
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,
NULL,--'RECEIVE',
'VENDOR',
cur_po_line.organization_code,
'PO',
rcv_headers_interface_s.currval,
'Y',
ln_org_id
FROM dual;
dbms_output.put_line('po line: ' || cur_po_line.line_num || ' shipment: ' || cur_po_line.shipment_num ||
' has been inserted sucessfully
inserted in the Interface tables.');
ELSE
dbms_output.put_line('po line ' || cur_po_line.line_num || ' is either closed, cancelled,
received or have some errors');
END IF;
END LOOP;
COMMIT;
END;
0 comments:
Post a Comment