Creating a Concurrent Program Using Stored Procedure

In this session we are creating a Concurrent Program of type Stored Procedure using the Oracle Apps API from database tier.

Pre-requisite: Create a stored procedure and compile it successfully.

The script for demo is

CREATE OR REPLACE PROCEDURE XX_CP_API (errbuf    OUT VARCHAR2,

                                       retcode   OUT VARCHAR2)

AS

   CURSOR po_cursor

   IS

      SELECT   PO_HEADER_ID POI,

               TYPE_LOOKUP_CODE TLC,

               VENDOR_ID VID,

               CURRENCY_CODE CC

        FROM   PO_HEADERS_ALL

       WHERE   ROWNUM < 10;

BEGIN

   FND_FILE.put_line (FND_FILE.output, 'Starting processing:');

   FOR po_rec IN po_cursor

   LOOP

      FND_FILE.put_line (FND_FILE.output,

                         po_rec.POI || po_rec.TLC || po_rec.VID || po_rec.CC);

   END LOOP;

   FND_FILE.put_line (FND_FILE.output, 'Done!');

   COMMIT;

   --Return 0 for successful completion.

   errbuf := '';

   retcode := '0';

EXCEPTION

   WHEN OTHERS

   THEN

      errbuf := SQLERRM;

      retcode := ‘2’;

END;

/

Step1:  Registering the Executable from back end.

The PL/SQL code to create an executable from back-end is,

From SQL * PLUS>

BEGIN

   FND_PROGRAM.executable (‘XX_CP_API’                       --executable name

                                      ,

                           'Payables'                            --application

                                     ,

                           'XX_CP_API'                           -- short_name

                                      ,

                           'Executable for Employee INFORMATION' --description

                                                                ,

                           'PL/SQL Stored Procedure’ -- execution_method',

                           'XX_CP_API'                  -- execution_file_name

                                      ,

                           ''                               -- subroutine_name

                             ,

                           ''                           -- Execution File Path

                             ,

                           'US'                               -- language_code

                               ,

                           '');

   COMMIT;

END;

/

PL/SQL procedure successfully completed.

Result of Step1: Query in the front-end to verify whether the executable is created or not.

System Administrator>>Concurrent>>Program>>Executable.

F11 AND ENTER XX_CP_API in the executable and press ctrl +f11 to see the result

Concurrent Program Executable

1] The above API inserts the new records in FND_EXECUTABLES andFND_EXECUTABLES_TL table.

Verify the new entries in these tables

select

APPLICATION_ID ,

EXECUTABLE_ID  ,

EXECUTABLE_NAME

from FND_EXECUTABLES where EXECUTABLE_NAME=’XX_CP_API';

Select APPLICATION_ID, EXECUTABLE_ID  , USER_EXECUTABLE_NAME   ,  DESCRIPTION From FND_EXECUTABLES_TL where USER_EXECUTABLE_NAME=’XX_CP_API';

2] You can use the below query to get all the Execution Methods available:

SELECT MEANING

  FROM fnd_lookup_values

  WHERE lookup_type = ‘CP_EXECUTION_METHOD_CODE’

 AND enabled_flag  = ‘Y’

/

Step 2:  Register the Concurrent program from back end

From SQL>

BEGIN

 FND_PROGRAM.register(‘XX_CP_API’ — program

, ‘Payables’ — application

, ‘Y’ — enabled

, ‘XX_CP_API’ — short_name

, ‘ Employee Information’ — description

, ‘XX_CP_API’ — executable_short_name

, ‘Payables’ — 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;

/

View this step from apps.

System Administrator>> Concurrent>> Program>> Define.

F11 and type XX_CP_API in the program and press ctrl+f11 to see the result

Defining a Concurrent Program

Verify the tables for new program entries:

select 

APPLICATION_ID,

 CONCURRENT_PROGRAM_ID,

 CONCURRENT_PROGRAM_NAME,

 EXECUTABLE_ID

from fnd_concurrent_programs where EXECUTABLE_ID=15973

/

select

APPLICATION_ID                 ,

CONCURRENT_PROGRAM_ID          ,

USER_CONCURRENT_PROGRAM_NAME  

from FND_CONCURRENT_PROGRAMS_TL

where USER_CONCURRENT_PROGRAM_NAME=’XX_CP_API’

/

Step3:

Attach the concurrent program to the request group: Below is the program to Attach Concurrent program to the request group from back-end.

BEGIN

        FND_PROGRAM.add_to_group(‘XX_CP_API’ — program_short_name

                , ‘Payables’ — application

        , ‘All Reports’ — Report Group Name

        , ‘SQLAP’); — Report Group Application

        COMMIT;

 END;

Verify from Oracle Apps, if the CP is attached to Request group.

System administrator>> security>> responsibility>> request

F11 and enter All Reports in the group and hit ctrl+f11. Select application aspayables and check for the request name (XX_CP_API). It should be visible.

Attaching Concurrent Program to a request group

Step4:  Submit the 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.

How to get the parameters user_id, responsibility_id, application_responsibility_id?

Select   USER_ID,   USER_NAME

   from FND_USER

   where USER_NAME=’OPERATIONS';

USER_ID =>1318

select application_id, Responsibility_id, responsibility_name

  from FND_RESPONSIBILITY_VL

  where responsibility_name like ‘Payables, Vision Operations (USA)’

/

RESPONSIBILITY_ID=>50554

RESPONSIBILITY_APPLICATION_ID=>200

DECLARE

l_request_id NUMBER(30);

      begin

   FND_GLOBAL.APPS_INITIALIZE (user_id => 1318, resp_id => 50554, resp_appl_id =>200);

   l_request_id:= FND_REQUEST.SUBMIT_REQUEST (‘SQLAP’,’XX_CP_API’);

    DBMS_OUTPUT.PUT_LINE(l_request_id);

   commit;

end;

/

Verify the result from Oracle apps:

Change to payables responsibility. View>>Requests>>Find.

Viewing a Concurrent Request

Click on View output:

Output of a Concurrent Program

Done…..