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!!
 

0 Responses to “R12 - Script to import Purchase order with one PO Line and one Shipment Line using PDOI”

Post a Comment

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.