script to 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);

Some prerequisite Before using API.

If we want to call any API using backend then we need to take care for the below mentioned points.

set policy context.
mo_global.set_policy_context(access_mode,org_id)

Calling Program Context before using any API.

fnd_global.apps_initialize ( user_id in number,

                                      resp_id in number,

                                     resp_appl_id in number,

                                     security_group_id in number default 0);

Return Status after using API. (x_return_status)

• Success (FND_API.G_RET_STS_SUCCESS)

• Error (FND_API.G_RET_STS_ERROR)

• Unexpected error (FND_API.G_RET_STS_UNEXP_ERROR)

Messages

FND_MSG_PUB.Initialize  or    p_init_msg_list   Default  FND_API.G_FALSE

Debug Messages after using API.

arp_standard.enable_file_debug(<pathname>, <filename>)

<pathname> 

select value from v$parameter where name = 'utl_file_dir',

arp_standard.enable_file_debug ('/sqlcom/log','txt.log')

To get the resp_id and resp_appl_id use the below queries

SELECT APPLICATION_ID, RESPONSIBILITY_ID

                          FROM FND_RESPONSIBILITY_TL

                          WHERE RESPONSIBILITY_NAME='xxmz Custom';

SELECT USER_ID FROM FND_USER WHERE USER_NAME='OPERATIONS';

API for getting Error messages in program after using API.

pa_interface_utils_pub. get_messages

                                    (p_encoded        IN   VARCHAR2  ,

                                      p_msg_index      IN NUMBER    ,

                                      p_msg_count      IN NUMBER    ,

                                      p_msg_data       IN  VARCHAR2  ,

                                      p_data             OUT NOCOPY           VARCHAR2,  

                                      p_msg_index_out  OUT NOCOPY    NUMBER  

                                     );

UTL_FILE Functions and Program

With the UTL_FILE package, PL/SQL programs can read and write operating system text files. UTL_FILE provides a restricted version of operating system stream file I/O.

Types

The contents of FILE_TYPE are private to the UTL_FILE package. You should not reference or change components of this record.

TYPE file_type IS RECORD (

   id       BINARY_INTEGER,

   datatype BINARY_INTEGER);

UTL_FILE Package Exceptions

Exception Name	Description
INVALID_PATH	File location is invalid.
INVALID_MODE	The open_mode parameter in FOPEN is invalid.
INVALID_FILEHANDLE	File handle is invalid.
INVALID_OPERATION	File could not be opened or operated on as requested.
READ_ERROR	Operating system error occurred during the read operation.
WRITE_ERROR	Operating system error occurred during the write operation.
INTERNAL_ERROR	Unspecified PL/SQL error
CHARSETMISMATCH	A file is opened using FOPEN_NCHAR, but later I/O operations use nonchar functions such as PUTF or GET_LINE.
FILE_OPEN	The requested operation failed because the file is open.
INVALID_MAXLINESIZE	The MAX_LINESIZE value for FOPEN() is invalid; it should be within the range 1 to 32767.
INVALID_FILENAME	The filename parameter is invalid.
ACCESS_DENIED	Permission to access to the file location is denied.
INVALID_OFFSET	Causes of the INVALID_OFFSET exception: ABSOLUTE_OFFSET = NULL and RELATIVE_OFFSET = NULL, or ABSOLUTE_OFFSET < 0, or Either offset caused a seek past the end of the file
DELETE_FAILED	The requested file delete operation failed.
RENAME_FAILED	The requested file rename operation failed.

  UTL_FILE Subprograms

Subprogram	Description
FCLOSE Procedure	Closes a file
FCLOSE_ALL Procedure	Closes all open file handles
FCOPY Procedure	Copies a contiguous portion of a file to a newly created file
FFLUSH Procedure	Physically writes all pending output to a file
FGETATTR Procedure	Reads and returns the attributes of a disk file
FGETPOS Function	Returns the current relative offset position within a file, in bytes
FOPEN Function	Opens a file for input or output
FOPEN_NCHAR Function	Opens a file in Unicode for input or output
FREMOVE Procedure	Deletes a disk file, assuming that you have sufficient privileges
FRENAME Procedure	Renames an existing file to a new name, similar to the UNIX mv function
FSEEK Procedure	Adjusts the file pointer forward or backward within the file by the number of bytes specified
GET_LINE Procedure	Reads text from an open file
GET_LINE_NCHAR Procedure	Reads text in Unicode from an open file
GET_RAW Function	Reads a RAW string value from a file and adjusts the file pointer ahead by the number of bytes read
IS_OPEN Function	Determines if a file handle refers to an open file
NEW_LINE Procedure	Writes one or more operating system-specific line terminators to a file
PUT Procedure	Writes a string to a file
PUT_LINE Procedure	Writes a line to a file, and so appends an operating system-specific line terminator
PUT_LINE_NCHAR Procedure	Writes a Unicode line to a file
PUT_NCHAR Procedure	Writes a Unicode string to a file
PUTF Procedure	A PUT procedure with formatting
PUTF_NCHAR Procedure	A PUT_NCHAR procedure with formatting, and writes a Unicode string to a file, with formatting
PUT_RAW Function	Accepts as input a RAW data value and writes the value to the output buffer

Syntax:

UTL_FILE.FCLOSE (

   file IN OUT FILE_TYPE);

UTL_FILE.FCLOSE_ALL;

UTL_FILE.FCOPY (

   location   IN VARCHAR2,

   filename   IN VARCHAR2,

   dest_dir   IN VARCHAR2,

   dest_file  IN VARCHAR2,

   start_line IN PLS_INTEGER DEFAULT 1,

   end_line   IN PLS_INTEGER DEFAULT NULL);

UTL_FILE.FFLUSH (

   file  IN FILE_TYPE);

invalid_maxlinesize  EXCEPTION;

UTL_FILE.FGETATTR(

   location    IN VARCHAR2,

   filename    IN VARCHAR2,

   exists      OUT BOOLEAN,

   file_length OUT NUMBER,

   blocksize   OUT NUMBER);

UTL_FILE.FGETPOS (

   fileid IN file_type)

 RETURN PLS_INTEGER;

UTL_FILE.FOPEN (

   location     IN VARCHAR2,

   filename     IN VARCHAR2,

   open_mode    IN VARCHAR2,

   max_linesize IN BINARY_INTEGER)

  RETURN file_type;

UTL_FILE.FOPEN_NCHAR (

   location     IN VARCHAR2,

   filename     IN VARCHAR2,

   open_mode    IN VARCHAR2,

   max_linesize IN BINARY_INTEGER)

RETURN file_type;

UTL_FILE.FREMOVE (

   location IN VARCHAR2,

   filename IN VARCHAR2);

UTL_FILE.FRENAME (

   location  IN VARCHAR2,

   filename  IN VARCHAR2,

   dest_dir  IN VARCHAR2,

   dest_file IN VARCHAR2,

   overwrite IN BOOLEAN DEFAULT FALSE);

UTL_FILE.FSEEK (

   fid             IN utl_file.file_type,

   absolute_offset IN PL_INTEGER DEFAULT NULL,

   relative_offset IN PLS_INTEGER DEFAULT NULL);

UTL_FILE.GET_LINE (

   file        IN  FILE_TYPE,

   buffer      OUT VARCHAR2,

   len         IN  PLS_INTEGER DEFAULT NULL);

UTL_FILE.GET_LINE_NCHAR (

   file        IN  FILE_TYPE,

   buffer      OUT NVARCHAR2,

   len         IN  PLS_INTEGER DEFAULT NULL);

UTL_FILE.GET_RAW (

   fid  IN  utl_file.file_type,

   r    OUT NOCOPY RAW,

   len  IN  PLS_INTEGER DEFAULT NULL);

UTL_FILE.IS_OPEN (

   file  IN FILE_TYPE)

  RETURN BOOLEAN;

UTL_FILE.NEW_LINE (

   file     IN FILE_TYPE,

   lines    IN NATURAL := 1);

UTL_FILE.PUT (

   file      IN FILE_TYPE,

   buffer    IN VARCHAR2);

UTL_FILE.PUT_LINE (

   file      IN FILE_TYPE,

   buffer    IN VARCHAR2,

   autoflush IN BOOLEAN DEFAULT FALSE);

UTL_FILE.PUT_LINE_NCHAR (

   file    IN FILE_TYPE,

   buffer  IN NVARCHAR2);

UTL_FILE.PUT_NCHAR (

   file      IN FILE_TYPE,

   buffer    IN NVARCHAR2);

UTL_FILE.PUTF (

   file    IN FILE_TYPE,

   format  IN VARCHAR2,

   [arg1   IN VARCHAR2  DEFAULT NULL,

   . . . 

   arg5    IN VARCHAR2  DEFAULT NULL]);

UTL_FILE.PUTF_NCHAR (

   file    IN FILE_TYPE,

   format  IN NVARCHAR2,

   [arg1   IN NVARCHAR2  DEFAULT NULL,

   . . . 

   arg5    IN NVARCHAR2  DEFAULT NULL]);

UTL_FILE.PUT_RAW (

   fid       IN utl_file.file_type,

   r         IN RAW,

   autoflush IN BOOLEAN DEFAULT FALSE);

Example::

DECLARE

  V1 VARCHAR2(32767);

  F1 UTL_FILE.FILE_TYPE;

BEGIN

  -- In this example MAX_LINESIZE is less than GET_LINE's length request

  -- so the number of bytes returned will be 256 or less if a line terminator is seen.

  F1 := UTL_FILE.FOPEN('MYDIR','MYFILE','R',256);

  UTL_FILE.GET_LINE(F1,V1,32767);

  UTL_FILE.FCLOSE(F1);

  -- In this example, FOPEN's MAX_LINESIZE is NULL and defaults to 1024,

  -- so the number of bytes returned will be 1024 or less if a line terminator is seen.

  F1 := UTL_FILE.FOPEN('MYDIR','MYFILE','R');

  UTL_FILE.GET_LINE(F1,V1,32767);

  UTL_FILE.FCLOSE(F1);

  -- In this example, GET_LINE doesn't specify a number of bytes, so it defaults to

  -- the same value as FOPEN's MAX_LINESIZE which is NULL in this case and defaults to 1024.

  -- So the number of bytes returned will be 1024 or less if a line terminator is seen.

  F1 := UTL_FILE.FOPEN('MYDIR','MYFILE','R');

  UTL_FILE.GET_LINE(F1,V1);

  UTL_FILE.FCLOSE(F1);

END;