Api to create po receipts in oracle apps
In this post, We will discuss how we can create
po receipts in oracle apps through API. API is the more advanced version of the
Oracle interfaces, In which we can create the External transactions from
External system in oracle application without using the Oracle Interface
tables. API helps to validate the External Transactions and then directly
insert the validated transactions in the Oracle application base tables. API is
more quick and easy way to do the integration and conversion from External
system to the Oracle application. Here we are going to Discuss api to create po
receipts in oracle apps. 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 Api to
create po receipts in oracle apps.
Tables Involved in the PO receipts Interface Tables in
oracle apps.
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 Api to create po receipts in oracle apps
DECLARE
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_open_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_open_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