Saturday, February 17, 2018
R12 - Script to import Purchase order with one PO Line and one Shipment Line using PDOI
In this post, we have shared
the script used for importing a purchase order with one line and one shipment
data
Script:
SET SERVEROUTPUT ON;
DECLARE
ln_batch_id NUMBER DEFAULT TO_CHAR(SYSDATE,'DDMMHHMISSSSS');
ln_org_id NUMBER DEFAULT 165;
PROCEDURE
submit_po_import(pin_default_buyer_id IN NUMBER,
piv_approval_status IN VARCHAR2,
pin_batch_id IN NUMBER,
pin_org_id IN NUMBER
)
IS
lv_request_id NUMBER;
lv_result BOOLEAN;
lv_phase1 VARCHAR2(100);
lv_status1 VARCHAR2(100);
lv_dev_phase1 VARCHAR2(100);
lv_dev_status1 VARCHAR2(100);
lv_message1 VARCHAR2(100);
BEGIN
fnd_global.APPS_INITIALIZE(user_id=>2124,
resp_id=>50958,
resp_appl_id=>201);
mo_global.init('PO');
fnd_client_info.set_org_context(ln_org_id);
-- Submit Import Standard Purchase Orders
lv_request_id :=
fnd_request.submit_request
(application => 'PO',
program => 'POXPOPDOI',
description => NULL,
start_time => NULL,
sub_request => NULL,
argument1 =>
pin_default_buyer_id, -- Default Buyer
argument2 => 'STANDARD', --
Document Type
argument3 => NULL, -- Document SubType
argument4 => 'N', -- Create or Update Items
argument5 => 'N', --
Create Sourcing Rules
argument6 =>
piv_approval_status, -- Approval Status
argument7 => NULL, -- Release Generation argument8 => pin_batch_id, --
Batch Id
argument9 => pin_org_id, --
Operating Unit
argument10 => NULL, -- Global Agreement
argument11 => NULL, -- Enable Sourcing Level
argument12 => NULL, -- Sourcing Level
argument13 => NULL, -- Inv Org Enable
argument14 => NULL -- Inventory Organization
);
COMMIT;
IF lv_request_id = 0
THEN
dbms_output.put_line(' Failed to submit Process POXPOPDOI.' || fnd_message.get);
ELSE
lv_result :=
fnd_concurrent.wait_for_request(lv_request_id
,1
,0
,lv_phase1
,lv_status1
,lv_dev_phase1
,lv_dev_status1
,lv_message1
);
END IF;
IF NOT lv_result
THEN
dbms_output.put_line('No Status returned for the request Id: ' || lv_request_id);
ELSE
dbms_output.put_line('The Req-Id of POXPOPDOI Process is ' || lv_request_id);
END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Others exception while submitting the Import Standard
Purchase Orders. Error' ||SQLERRM);
END submit_po_import;
BEGIN
INSERT INTO po.po_headers_interface
(interface_header_id,
batch_id,
process_code,
action,
org_id,
document_type_code,
currency_code,
agent_id,
vendor_id,
vendor_site_id,
ship_to_location_id,
bill_to_location_id,
reference_num,
comments)
VALUES
(apps.po_headers_interface_s.NEXTVAL,
ln_batch_id,
'PENDING',
'ORIGINAL',
ln_org_id,
'STANDARD',
'CNY',
462, -- Your buyer
14006,
12006,
'1214',
'1231',
'PO_TAIWAN_IMP1',
'Automatic PO Imported for POC');
INSERT INTO po.po_lines_interface
(interface_line_id,
interface_header_id,
line_num,
shipment_num,
line_type,
item_description,
category_id,
uom_code,
quantity,
unit_price,
ship_to_organization_code,
ship_to_location,
SHIP_TO_LOCATION_ID,
line_loc_populated_flag,
negotiated_by_preparer_flag)
VALUES
(po_lines_interface_s.nextval,
po_headers_interface_s.currval,
1,
1,
'Goods',
'Test Item 1',
22123,
'EA',
30,
10,
'001',
'SHIPORG1',
1214,
'Y',
'N');
Insert into
PO.PO_LINE_LOCATIONS_INTERFACE
(INTERFACE_LINE_LOCATION_ID,
INTERFACE_HEADER_ID,
INTERFACE_LINE_ID,
SHIPMENT_TYPE,
SHIPMENT_NUM,
SHIP_TO_ORGANIZATION_ID,
SHIP_TO_LOCATION_ID,
NEED_BY_DATE,
PROMISED_DATE,
QUANTITY,
CREATION_DATE)
Values
(po_line_locations_interface_s.nextval,---
INTERFACE_LINE_LOCATION_ID,
po_headers_interface_s.currval,---
INTERFACE_HEADER_ID,
po_lines_interface_s.currval, ---
INTERFACE_LINE_ID,
'STANDARD', --- SHIPMENT_TYPE,
1, --- SHIPMENT_NUM,
185,--- SHIP_TO_ORGANIZATION_ID, select * from
org_organization_definitions where organization_id = 185;
1214,--- SHIP_TO_LOCATION_ID,
SYSDATE+10,--- NEED_BY_DATE,
SYSDATE+10,--- PROMISED_DATE,
30,--- QUANTITY,
SYSDATE);--- CREATION_DATE
INSERT INTO po.po_distributions_interface
(interface_header_id,
interface_line_id,
interface_line_location_id,
interface_distribution_Id,
distribution_num,
quantity_ordered,
charge_account_id,
variance_account_id,
destination_type_code,
destination_organization_id,
deliver_to_location_id,
deliver_to_person_id,
org_id
)
VALUES
(po_headers_interface_s.currval,
po_lines_interface_s.currval,
po_line_locations_interface_s.currval,
po_distributions_interface_s.nextval,
1,
30,
14003,
14003,
'EXPENSE',
185,
'1231',
'102',
ln_org_id
);
dbms_output.put_line('ln_batch_id:'||ln_batch_id);
submit_po_import(pin_default_buyer_id
=> 462,
piv_approval_status => 'APPROVED',
pin_batch_id =>
ln_batch_id,
pin_org_id =>
ln_org_id
);
END;
Hope this helps!!
Do you think this Article is useful?
Subscribe to:
Post Comments (Atom)
Disclaimer
The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.
0 Responses to “R12 - Script to import Purchase order with one PO Line and one Shipment Line using PDOI”
Post a Comment