Get Concurrent Program Request Status and Wait For Request completion

FND_CONCURRENT.GET_REQUEST_STATUS

This API Returns the Status of a concurrent request. It also returns the completion text if the request is already completed. The return type is Boolean (Returns TRUE on successful retrieval of the information, FALSE otherwise).

function get_request_status(request_id     IN OUT NOCOPY number,                     appl_shortname IN varchar2 default NULL,                     program        IN varchar2 default NULL,                     phase      OUT NOCOPY varchar2,                     status     OUT NOCOPY varchar2,                     dev_phase  OUT NOCOPY varchar2,                     dev_status OUT NOCOPY varchar2,                     message    OUT NOCOPY varchar2) return boolean;

The parameters are:

• REQUEST_ID: Request ID of the program to be checked.
• APPL_SHORTNAME: Short name of the application associated with the program. The default is NULL.
• PROGRAM: Short name of the concurrent program. The default is NULL.
• PHASE: Request phase.
• STATUS: Request status.
• DEV_PHASE: Request phase as a string constant.
• DEV_STATUS: Request status as a string constant.
• MESSAGE: Request completion message.

FND_CONCURRENT.WAIT_FOR_REQUEST

This API waits for the request completion, then returns the request phase/status and completion message to the caller. It goes to sleep between checks for the request completion. The return type is Boolean (Returns TRUE on successful retrieval of the information, FALSE otherwise).

function wait_for_request(request_id IN number default NULL,         interval   IN  number default 60,         max_wait   IN  number default 0,         phase      OUT NOCOPY varchar2,         status     OUT NOCOPY varchar2,         dev_phase  OUT NOCOPY varchar2,         dev_status OUT NOCOPY varchar2,         message    OUT NOCOPY varchar2) return  boolean;

The parameters are:

• REQUEST_ID: Request ID of the request to wait on. The default is NULL.
• INTERVAL: Number of seconds to wait between checks. The default is 60 seconds.
• MAX_WAIT: Maximum number of seconds to wait for the request completion. The default is 00 seconds.
• PHASE: User-friendly Request phase.
• STATUS: User-friendly Request status.
• DEV_PHASE: Request phase as a constant string.
• DEV_STATUS: Request status as a constant string.
• MESSAGE: Request completion message.

There are few other useful apis too.

• FND_CONCURRENT.SET_COMPLETION_STATUS: Called from a concurrent request to set its completion status and message.
• FND_CONCURRENT.GET_REQUEST_PRINT_OPTIONS: Returns the print options for a concurrent request.
• FND_CONCURRENT.GET_SUB_REQUESTS: Get all sub-requests for a given request id. For each sub-request it provides request_id, phase,status, developer phase , developer status and completion text.
• FND_CONCURRENT.Cancel_Request: It cancels a given Concurrent Request.

For Detailed information of the above requests you can open the package FND_CONCURRENT in your instance.

FND Document Sequence Assignment From Back-end with a Script

Assign document sequences to multiple transaction types with a script. It will make it easier for you to assign a document sequences to transaction types in one go.

Use the below API to assign fnd document sequences to customer trx types:-

API Name :- FND_SEQNUM.assign_doc_seq

Package Name :- FND_SEQNUM

Function name and parameters:- 

function assign_doc_seq (

app_id in number,

docseq_name in fnd_document_sequences.name%TYPE,

cat_code         in fnd_doc_sequence_assignments.category_code%TYPE,

sob_id in fnd_doc_sequence_assignments.set_of_books_id%TYPE,

met_code in fnd_doc_sequence_assignments.method_code%TYPE,

p_startDate in date,

p_endDate in date default NULL

                );

You can call it with the below parameters :- 

ln_ret_num :=

            apps.FND_SEQNUM.assign_doc_seq (ln_appl_id,

                                         lc_seq_name,

                                         name,                                --lc_cat_code

                                         SET_OF_BOOKS_ID,     --ln_sob_id,

                                         NULL,                                 --lc_met_code,

                                         ld_start_date                        --Start Date of Duocument sequence assignment

                                            );

For an example I have written a script to assign sequence based on transaction types criteria.

-- Start of the script 

DECLARE

   ln_appl_id      NUMBER :=  222;    --   Fnd_application.application_id

   lc_seq_name     fnd_document_sequences.name%TYPE;          -- Sequence Name

   ln_ret_num      NUMBER;

   ld_start_date   DATE := SYSDATE;     -- Start Date of the sequence as SYSDATE

-- Below cursor can be modified based on the criteria you want to assign the sequence with

DECLARE

   CURSOR lcu_seq_assignemt

   IS

      SELECT rct.name,

             rct.TYPE,

             rct.set_of_books_id,

             rct.org_id

        FROM apps.ra_cust_Trx_types_All rct

       WHERE     rct.creation_date >= '01-AUG-2017'

             AND rct.end_date IS NULL

             AND NOT EXISTS

                        (SELECT 1

                           FROM APPS.FND_DOC_SEQUENCE_ASSIGNMENTS fd

                          WHERE     fd.category_code = rct.name

                                AND fd.set_of_books_id = rct.set_of_books_id

                                AND fd.end_date IS NULL);

BEGIN

   DBMS_OUTPUT.put_line ('START');

   FOR i IN lcu_seq_assignemt

   LOOP

      IF i.name = 'XYZ'

      THEN

         lc_seq_name := 'XYZ Sequene';

      ELSE

         lc_seq_name := 'NO_SEQ_ASSIGNED';

      END IF;

      IF lc_seq_name <> 'NO_SEQ_ASSIGNED'

      THEN

         ln_ret_num :=

            apps.FND_SEQNUM.assign_doc_seq (ln_appl_id,

                                            lc_seq_name,

                                            i.name,              --lc_cat_code

                                            i.SET_OF_BOOKS_ID,    --ln_sob_id,

                                            NULL,               --lc_met_code,

                                            ld_start_date);

         DBMS_OUTPUT.put_line ('Post API ');

         IF ln_ret_num = 0 -- For successful sequence creation this will return 0.

         THEN

            COMMIT;

            DBMS_OUTPUT.put_line ('Success ');

         ELSE

            DBMS_OUTPUT.put_line ('Fail :' || ln_ret_num);

            ROLLBACK;

         END IF;

      ELSE

         DBMS_OUTPUT.PUT_LINE (

            'No asssignment done for - i.BU_CODE::' || SQLERRM);

      END IF;

   END LOOP;

   DBMS_OUTPUT.put_line ('END ');

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line ('Érror : ' || SQLERRM);

END;

/

Disclaimer :- Please modify the criteria based on your requirement.

Submit Concurrent Program From Back-end in Oracle Apps

You can submit concurrent Program from backend in oracle apps using below script. There are mandatory parameters you need to pass for the standard procedure and Concurrent program parameters can be passed with arguments.

For examples:- argument1 will be first parameter for you concurrent program, the similar way argument2 will be second parameter of your concurrent program and So On.


Script :-

DECLARE
l_conc_request_id   fnd_concurrent_requests.request_id;      -- This is OUT parameter
l_program                VARCHAR2(30);                                  -- This concurrent program short name
l_application            VARCHAR2(10);                -- Application name like AR, OM
l_description            VARCHAR2(40);     -- Description of your program
l_patameter1            VARCHAR2(20);   -- first parameter
l_parameter2            VARCHAR2(20);   --second parameter

BEGIN

l_conc_request_id := fnd_request.submit_request
            (
                application => l_application,
                program     => l_program,
                description =>l_description,
                start_time  => null,
                sub_request => false,                                   -- False as its not a subrequest
                argument1   => l_patameter1,
                argument2   => l_patameter2
            );

COMMIT;

EXCEPTION

WHEN OTHERS THEN
dbms_output.put_line ('Exception' || SQLERRM);

END;