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

Delete concurrent program and Executable through backed

BEGIN
fnd_program.delete_program('program short name','schema');
fnd_program.delete_executable('program short name','schema');
COMMIT;
END;

Add request set to request group from Back end

DECLARE     
   new_requet_group    VARCHAR2 (30)                             := 'test_RG';     
   new_rg_appl_name    VARCHAR2 (240)           := 'Custom Application';     
   old_request_group   VARCHAR2 (30)                       := 'Test_Pavan_RG';     
   ol_rg_appl_name     VARCHAR2 (240)           := 'Custom Application';     
   new_rg_short_name   VARCHAR2 (8);
   CURSOR c1    -- to get the application short name of the new request group     
   IS     
      SELECT fa.application_short_name     
        FROM applsys.fnd_request_groups frg,     
             applsys.fnd_application_tl fat,     
             applsys.fnd_application fa     
       WHERE fat.application_id = frg.application_id     
         AND fa.application_id = fat.application_id     
         AND fat.LANGUAGE = 'US'     
         AND frg.request_group_name = new_requet_group     
         AND fat.application_name = new_rg_appl_name;
   CURSOR c2     
         -- to get the concurrent programs of the  old or parent request group     
            (     
      request_group_name_var   IN   VARCHAR2,     
      application_name_var     IN   VARCHAR2     
   )     
   IS     
      SELECT fcp.concurrent_program_name, fat1.application_name     
        FROM applsys.fnd_request_groups frg,     
             applsys.fnd_application_tl fat,     
             applsys.fnd_application_tl fat1,     
             applsys.fnd_request_group_units fgu,     
             applsys.fnd_concurrent_programs_tl fcpt,     
             applsys.fnd_concurrent_programs fcp     
       WHERE fat.application_id = frg.application_id     
         AND fgu.request_group_id = frg.request_group_id     
         AND fcpt.concurrent_program_id = fgu.request_unit_id     
         AND fcp.concurrent_program_id = fcpt.concurrent_program_id     
         AND fcpt.application_id = fat1.application_id     
         AND fat.LANGUAGE = fcpt.LANGUAGE     
         AND fat1.LANGUAGE = fat.LANGUAGE     
         AND fat.LANGUAGE = 'US'     
         AND fgu.request_unit_type = 'P'     
         AND frg.request_group_name = request_group_name_var     
         AND fat.application_name = application_name_var;
   CURSOR c3     -- to get the request set of the  old or parent request group     
            (     
      request_group_name_var   IN   VARCHAR2,     
      application_name_var     IN   VARCHAR2     
   )     
   IS     
      SELECT frst.request_set_name, fat1.application_short_name     
        FROM applsys.fnd_request_groups frg,     
             applsys.fnd_application_tl fat,     
             applsys.fnd_application fat1,     
             applsys.fnd_request_group_units fgu,     
             applsys.fnd_request_sets frst     
       WHERE fat.application_id = frg.application_id     
         AND fgu.request_group_id = frg.request_group_id     
         AND frst.request_set_id = fgu.request_unit_id     
         AND frst.application_id = fat1.application_id     
         AND fat.LANGUAGE = 'US'     
         AND fgu.request_unit_type = 'S'     
         AND frg.request_group_name = request_group_name_var     
         AND fat.application_name = application_name_var;
   prg_name_var        applsys.fnd_concurrent_programs.concurrent_program_name%TYPE;     
   prg_app_name_var    applsys.fnd_application_tl.application_name%TYPE;     
   set_name_var        applsys.fnd_request_sets_tl.user_request_set_name%TYPE;     
   set_app_name_var    applsys.fnd_application_tl.application_name%TYPE;     
BEGIN     
   OPEN c1;
   IF c1%ISOPEN     
   THEN     
      LOOP     
         FETCH c1     
          INTO new_rg_short_name;
         EXIT WHEN c1%NOTFOUND;     
      END LOOP;     
   END IF;
   OPEN c2 (old_request_group, ol_rg_appl_name);
   IF c2%ISOPEN     
   THEN     
      LOOP     
         FETCH c2     
          INTO prg_name_var, prg_app_name_var;
         EXIT WHEN c2%NOTFOUND;     
         ---DBMS_OUTPUT.put_line (prg_name_var || '---' || prg_app_name_var);     
         begin     
   fnd_program.add_to_group (prg_name_var,     
                                   prg_app_name_var,     
                                   new_requet_group,     
                                   new_rg_appl_name     
                                  );     
              
         EXCEPTION     
   WHEN OTHERS     
   THEN     
   DBMS_OUTPUT.PUT_LINE ('Error in attaching '|| prg_name_var ||' to equest group :-' || SQLERRM);     
   END;
      END LOOP;     
   END IF;     
   OPEN c3 (old_request_group, ol_rg_appl_name);
   IF c3%ISOPEN     
   THEN     
      LOOP     
         FETCH c3     
          INTO set_name_var, set_app_name_var;
         EXIT WHEN c3%NOTFOUND;     
        -- DBMS_OUTPUT.put_line (set_name_var || '---' || set_app_name_var);     
  begin     
           fnd_set.add_set_to_group (request_set            => set_name_var,     
                                   set_application        => set_app_name_var,     
                                   request_group          => new_requet_group,     
                                   group_application      => new_rg_short_name);     
            
   EXCEPTION     
   WHEN OTHERS     
   THEN     
     DBMS_OUTPUT.PUT_LINE ('Error in attaching '|| set_name_var ||' to equest group :-' || SQLERRM);     
   END;                             
         END LOOP;     
   END IF;     
END;

Submit request set thorugh backend

DECLARE
   l_request_set_exist   BOOLEAN := FALSE;
   l_request_id          INTEGER := 0;
   l_set_mode            BOOLEAN := FALSE;
   l_CONC_PROG_SUBMIT    BOOLEAN := FALSE;
   srs_failed            EXCEPTION;
   submitprog_failed     EXCEPTION;
   setmode_failed        EXCEPTION;
   submitset_failed      EXCEPTION;
   l_start_date          VARCHAR2 (250);
   l_req_data            VARCHAR2 (10);
   l_wait_for_req        BOOLEAN;
   l_phase               VARCHAR2 (100);
   l_status              VARCHAR2 (100);
   l_dev_phase           VARCHAR2 (100);
   l_dev_status          VARCHAR2 (100);
   l_message             VARCHAR2 (4000);
  
BEGIN
      l_set_mode := fnd_submit.set_mode (FALSE);
      IF (NOT l_set_mode)
      THEN
         RAISE setmode_failed;
      END IF;
       l_req_data := fnd_conc_global.request_data;
      IF (l_req_data IS NOT NULL)
      THEN
         p_status := 'N';
         RETURN;
      END IF;

      l_request_set_exist :=
         FND_SUBMIT.set_request_set (application   => 'OFA', -- Application short name
                                     request_set   => 'FNDRSSUB46' -- Request set shot name
          );
      IF (NOT l_request_set_exist)
      THEN
          RAISE srs_failed;
      END IF;
       dbms_output.put_line('Calling submit program first stage');
      l_conc_prog_submit :=
         fnd_submit.submit_program ('OFA',  -- Application short name
                                    'FAMAPT', -- Concurrent program shot name
                                    'STAGE10', -- Stage
                                    'FA CROP', --parameter for concurrent program
                                    'NORMAL' -- --parameter for concurrent program
         );
    
      IF (NOT l_conc_prog_submit)
      THEN
         RAISE submitprog_failed;
      END IF;
   dbms_output.put_line('Calling submit program second stage');
      l_conc_prog_submit :=
         fnd_submit.submit_program ('OFA', -- Application short name
                                    'FAS824',  -- Concurrent program shot name
                                    'STAGE20', -- Stage
                                    'FA CROP', --parameter for concurrent program
         );
      IF (NOT l_conc_prog_submit)
      THEN
         RAISE submitprog_failed;
      END IF;
      l_request_id := fnd_submit.submit_set (NULL, FALSE);
   
      COMMIT;
      IF (l_request_id = 0)
      THEN
         RAISE submitset_failed;
      END IF;

      l_wait_for_req :=
         fnd_concurrent.wait_for_request (request_id   => l_request_id,
                                          interval     => 2,
                                          max_wait     => 120,
                                          phase        => l_phase,
                                          status       => l_status,
                                          dev_phase    => l_dev_phase,
                                          dev_status   => l_dev_status,

EXCEPTION
   WHEN setmode_failed
   THEN
      dbms_output.put_line('Call to set mode failed');
   WHEN srs_failed
   THEN
      dbms_output.put_line('Call to set_request_set failed');
   WHEN submitprog_failed
   THEN
     dbms_output.put_line('Call to submit_program failed');
   WHEN submitset_failed
   THEN
      dbms_output.put_line( 'Call to submit_set failed');
   WHEN OTHERS
   THEN
      dbms_output.put_line('Request set submission failed');
     
END; 

Query to find execution time of concurrent program

SELECT
      f.request_id ,
      pt.user_concurrent_program_name user_conc_program_name,
      f.actual_start_date start_on,
      f.actual_completion_date end_on,
      floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)
        || ' HOURS ' ||
        floor((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600)/60)
        || ' MINUTES ' ||
        round((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600 -
        (floor((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600)/60)*60) ))
        || ' SECS ' time_difference,
      p.concurrent_program_name concurrent_program_name,
      decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase,
      f.status_code
from  apps.fnd_concurrent_programs p,
      apps.fnd_concurrent_programs_tl pt,
      apps.fnd_concurrent_requests f
where f.concurrent_program_id = p.concurrent_program_id
      and f.program_application_id = p.application_id
      and f.concurrent_program_id = pt.concurrent_program_id
      and f.program_application_id = pt.application_id
      AND pt.language = USERENV('Lang')
      and f.actual_start_date is not null
   and f.request_id=:p_request_id -- Provide request id for the concurrent program
order by
      f.actual_start_date desc;