Monday, July 30, 2012

Register, Attach, Submit, Delete Concurrent program using API


Register, Attach, Submit Concurrent program using API


The scripts in this blog can be used to:
1)    Register the executable and Program
2)    Attach Concurrent program to a Request Group
3)    Submit Concurrent program

1)    Registering the Executable from back end
          Usually we create executable in the front-end, but this can be done from the database tier i.e. back-end too.
          Below is the PL/SQL code to create an executable from back-end.
         BEGIN
              FND_PROGRAM.executable('XXMZ_EMPLOYEE' -- executable
                                                              , 'XXMZ Custom' -- application
                                                              , 'XXMZ_EMPLOYEE' -- short_name
                                                              , 'Executable for Employee INFORMATION' -- description
                                                              , 'PL/SQL Stored Procedure' -- execution_method
                                                              , 'XXMZ_EMPLOYEE' -- execution_file_name
                                                              , '' -- subroutine_name
                                                              , '' -- Execution File Path
                                                              , 'US' -- language_code
                                                              , '');
             COMMIT;
         END;
       Query in the front-end to see whether your executable is created.


2)    Registering the Concurrent program from back end
            Usually we create Concurrent program in the front-end, but this can be done from the database tier too.
            Below is the program to create a Concurrent program from back-end.
            BEGIN
                     FND_PROGRAM.register('Concurrent program for Employee Information' -- program
                                                                , 'XXMZ Custom' -- application
                                                                , 'Y' -- enabled
                                                                , 'XXMZ_EMPLOYEE' -- short_name
                                                                , ‘ Employee Information' -- description
                                                                , 'XXMZ_EMPLOYEE' -- executable_short_name
                                                                , 'XXMZ Custom' -- executable_application
                                                                , '' -- execution_options
                                                                , '' -- priority
                                                                , 'Y' -- save_output
                                                                , 'Y' -- print
                                                                , '' -- cols
                                                                , '' -- rows
                                                                , '' -- style
                                                                , 'N' -- style_required
                                                                , '' -- printer
                                                                , '' -- request_type
                                                                , '' -- request_type_application
                                                                , 'Y' -- use_in_srs
                                                                , 'N' -- allow_disabled_values
                                                                , 'N' -- run_alone
                                                                , 'TEXT' – output_type
                                                                , 'N' -- enable_trace
                                                                , 'Y' -- restart
                                                                , 'Y' -- nls_compliant
                                                                , '' -- icon_name
                                                                , 'US'); -- language_code
                        COMMIT;
            END;

 Query in the front-end to see whether your Concurrent program is created .

3)    Attaching the concurrent program to the request group
               Usually we Attach Concurrent program to the request group in the front-end, but this can be done from database tier too.
               Below is the program to Attach Concurrent program to the request group from back-end.
               BEGIN
                        FND_PROGRAM.add_to_group('XXMZ_EMPLOYEE' -- program_short_name
                                                                             , 'XXMZ Custom' -- application
                                                                             , 'xxmz Request Group' -- Report Group Name
                                                                             , 'XXMZ'); -- Report Group Application
                        COMMIT;
              END;


  Query in the front-end to see whether your Concurrent program is Attached to Request Group.

4)    Submitting Concurrent Program from Back-end
We first need to initialize oracle applications session using
                        fnd_global.apps_initialize(user_id,responsibility_id,application_responsibility_id) and then run fnd_request.submit_request
                         DECLARE
                               l_request_id NUMBER(30);
                         begin
                                  FND_GLOBAL.APPS_INITIALIZE (user_id => 1318, resp_id => 59966, resp_appl_id => 20064);
                                  l_request_id:= FND_REQUEST.SUBMIT_REQUEST ('XXMZ' --Application Short name,
'VENDOR_FORM'-- Concurrent Program Short Name );
                                  DBMS_OUTPUT.PUT_LINE(l_request_id);
                                  commit;
                         end;
                

Once the concurrent program is submitted from back-end, status of the concurrent program can be checked using below query.
                           SELECT * FROM FND_CONCURRENT_REQUESTS WHERE   REQUEST_ID= l_request_id;

You can use following code to wait for the request. It will return Boolean value.
FND_CONCURRENT.WAIT_FOR_REQUEST
                                   (request_id IN number default NULL,
                                    interval IN number default 60,
                                    max_wait IN number default 0,
                                    phase OUT varchar2,
                                    status OUT varchar2,
                                    dev_phase OUT varchar2,
                                    dev_status OUT varchar2,
                                    message OUT varchar2);

5) Delete a concurrent program from back-end

BEGIN   apps.fnd_program.remove_from_group (      program_short_name    => 'CTAR_AR_TRX_LINE_CONVERSION',      program_application   => 'CTAR',      request_group         => 'Receivables All',      group_application     => 'Receivables');   fnd_program.delete_program ('CTAR_AR_ACCTD_AMT_DUE_RMNG', 'CTAR');   fnd_program.delete_executable ('CTAR_AR_ACCTD_AMT_DUE_RMNG', 'CTAR');   COMMIT;END;