Script to Indentify trafce location

DECLARE

DB_Version VARCHAR2(2);

Trace_location VARCHAR2(240);

BEGIN

SELECT SUBSTR(vi.version, 1, INSTR(vi.version, '.')-1) INTO DB_Version FROM v$instance vi;

IF TO_NUMBER(DB_Version) >= 11 THEN

/* Following Line included as Execute Immediate as V$DIAG_INFO does not exist in DB Versions prior to 11g */

EXECUTE IMMEDIATE 'SELECT value FROM V$DIAG_INFO WHERE NAME = ''Diag Trace''' INTO Trace_location ;

ELSE

SELECT value INTO Trace_location FROM v$parameter WHERE name = 'user_dump_dest';

END IF ;

dbms_output.put_line('Trace File should be located in the directory :- '|| trace_location);

END;

Script to submit Import Standard Purchase order program through backend

DECLARE
      l_request_id     NUMBER;
      l_org_id         NUMBER;
      l_user_id        NUMBER;
      l_resp_id        NUMBER;
      l_resp_appl_id   NUMBER;
      l_boolean        BOOLEAN;
      l_phase          VARCHAR2 (240);
      l_status         VARCHAR2 (240);
      l_dev_phase      VARCHAR2 (240);
      l_dev_status     VARCHAR2 (240);
      l_message        VARCHAR2 (240);
   BEGIN
  
         mo_global.init ('PO');
         mo_global.set_policy_context ('S', '121'); -- Pass ORG_ID
         apps.fnd_global.apps_initialize (user_id        => l_user_id,     -- Pass user id
                                          resp_id        => l_resp_id,     -- Pass responsibility id
                                          resp_appl_id   => l_resp_appl_id -- Pas responsibility application id
            );
         l_request_id :=
            fnd_request.submit_request (application   => 'PO' -- Application name
                                        ,program       => 'POXPOPDOI' -- Concurrent program shot name
                                        ,description   => NULL
                                        ,start_time    => NULL  -- To start immediately
                                        ,sub_request   => FALSE
                                        ,argument1     => ''        -- Buyer_ID
                                        ,argument2     => 'STANDARD' -- Doc Type
                                        ,argument3     => ''         -- doc subtype
                                        ,argument4     => 'N'       -- update items
                                        ,argument5     => '' -- create sourcing rules not used
                                        ,argument6     => 'APPROVED' -- Approval status
                                        ,argument7     => '' -- release generation method
                                        ,argument8     => 12222   -- batch_id                                        ,                             ,
                                        ,argument9     => 121  -- operating unit null
                                        ,argument10    => '' -- global agreement null
                                        ,argument11    => '' -- enable sourcing null
                                        ,argument12    => '' -- sourcing level null
                                        ,argument13    => '' -- inv org enabled null
                                        ,argument14    => ''    -- inv org null
                                        ,argument15    => ''      -- batch size
                                        ,argument16    => 'N'   -- gather stats
                                        );
           dbms_output.put_line('Request ID for PO Import:' || l_request_id);
         IF l_request_id > 0
         THEN
            l_boolean :=
               FND_CONCURRENT.WAIT_FOR_REQUEST (l_request_id --request_id IN number default NULL,
                                                            ,
                                                20 --Interval   IN number default 60, SECONDS
                                                  ,
                                                0 --max_wait   IN number default 0,
                                                 ,
                                                l_phase --phase      OUT varchar2,
                                                       ,
                                                l_status --status     OUT varchar2,
                                                        ,
                                                l_dev_phase --dev_phase  OUT varchar2,
                                                           ,
                                                l_dev_status --dev_status OUT varchar2,
                                                            ,
                                                l_message --message    OUT varchar2) return boolean
                                                         );
         END IF;
      COMMIT;
   EXCEPTION
      WHEN OTHERS
      THEN
       dbms_output.put_line('Un Expected Error:' || SQLERRM);
   END;

Scrpit to add new line in APPROVED Standard PO

Step 1: insert the data interface tables as below

DECLARE
insert into po_headers_interface
(INTERFACE_HEADER_ID,
 ACTION,
 ORG_ID,
 DOCUMENT_TYPE_CODE,
 PO_HEADER_ID,
 firm_flag
)
VALUES
(po_headers_interface_s.NEXTVAL,
 'UPDATE',
 82,-- ORG ID
 'STANDAD',
 67089, -- po header Id
 );

 insert into po_lines_interface
 (INTERFACE_LINE_ID,
  INTERFACE_HEADER_ID,
  ACTION,
  LINE_NUM,
  shipment_num,
  line_type,
  ITEM_ID,
  UNIT_PRICE,
  quantity,
  need_by_date)
  values
  (po_lines_interface_s.nextval,
    po_headers_interface_s.CURRVAL,
   2, -- Line num
   2, -- Ship num
   'Goods',
   2, -- Item id
   100000, unit pirce
   1,
   to_date('02-NOV-2015','DD-MON-YYYY')
   );
  
COMMIT;
END;  

Step 2 : Ran the standard "Import Standard Purchase order" program to move the data from interface tables to base tables.

Base tables : PO_HEADERS_ALL
                     PO_LINES_ALL
                     PO_LINE_LOCATIONS_ALL
                     PO_DISTRIBUTIONS_ALL

Error Table : PO_INTERFACE_ERRORS