How to create concurrent programs from database?

Here are couple of APIs useful for creating any concurrent programs from the backend database.

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(executable => 'XXFIN TEST EXECUTABLE' , -- Executable Name
  application=>'XXFIN' , -- Application Short Name
  short_name=>'XXFINTSTEXE' , -- Executable Short Name
  description=>'Test Executable created from Backend' ,     -- Description,DEFAULT NULL
  execution_method=>'PL/SQL Stored Procedure',              -- Execution Method
  execution_file_name=>'XXFIN_TEST_PROC' ,                  -- Execution File Name,DEFAULT NULL
  subroutine_name=>NULL ,                                   -- Subroutine Name,DEFAULT NULL
  icon_name=>NULL ,                                         -- Icon Name,DEFAULT NULL
  language_code=>'US' ,                                     -- Language Code,DEFAULT 'US'
  execution_file_path=>NULL                                 -- Execution File Path, DEFAULT NULL
  );
  COMMIT;
END;

View from Frontend:

Notes:

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

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

SELECT MEANING “Execution Method”

FROM fnd_lookup_values

WHERE lookup_type = ‘CP_EXECUTION_METHOD_CODE’

AND enabled_flag  = ‘Y’;

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(program =>'Test CP from DB', -- CP Name
  application =>'XXFIN' , -- Application Short Name
  enabled =>'Y',                                    -- Flag to Enable/Disable a CP
  short_name =>'XXFINTSTCPDB', -- CP Short Name
  description =>'Test CP created from Backend' ,    -- Description,DEFAULT NULL
  executable_short_name =>'XXFINTSTEXE', -- Executable Short Name
  executable_application =>'XXFIN' , -- Executable Application Short Name
  execution_options => NULL,                        -- Execution Options,DEFAULT NULL,
  priority => NULL,                                 -- Priority,DEFAULT NULL,
  save_output =>'Y',                                -- Save Output,DEFAULT 'Y',
  PRINT =>'Y' ,                                     -- Print,DEFAULT 'Y',
  cols => NULL, -- DEFAULT NULL,
  rows => NULL, -- DEFAULT NULL,
  style => NULL,                                    -- DEFAULT NULL,
  style_required =>'N' ,                            -- DEFAULT 'N',
  printer => NULL,                                  -- DEFAULT NULL,
  request_type => NULL,                             -- DEFAULT NULL,
  request_type_application => NULL,                 -- DEFAULT NULL,
  use_in_srs =>'N' ,                                -- DEFAULT 'N',
  allow_disabled_values =>'N' ,                     -- DEFAULT 'N',
  run_alone =>'N' ,                                 -- DEFAULT 'N',
  output_type =>'TEXT',                             -- DEFAULT 'TEXT'
  enable_trace =>'N' ,                              -- DEFAULT 'N',
  restart =>'Y' ,                                   -- DEFAULT 'Y',
  nls_compliant =>'Y' ,                             -- DEFAULT 'Y',
  icon_name => NULL,                                -- DEFAULT NULL,
  language_code => 'US',                            -- DEFAULT 'US',
  mls_function_short_name => NULL,                  -- DEFAULT NULL,
  mls_function_application => NULL,                 -- DEFAULT NULL,
  incrementor => NULL, -- DEFAULT NULL,
  refresh_portlet => NULL                           -- DEFAULT NULL,
  );
  COMMIT;
END;

View from Frontend:

Notes:

1] The various output types are ‘PS’, ‘PDF’, ‘HTML’, ‘TEXT’, ‘PCL’, ‘XML’.

2] The above API inserts the new records in fnd_concurrent_programs and FND_CONCURRENT_PROGRAMS_TL

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('XXFINTSTCPDB', -- Concurrent Program Short Name
  'XXFIN' , -- Application Short Name
  'All Reports',                           -- Report Group Name
  'SQLAP'); -- Report Group Application
  COMMIT;
END;

Apart from these APIs, the above package also contains to create/delete parameters, delete executable, and delete concurrent programs and all.

Utility APIs for Concurrent Processing

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.

How to call a Concurrent Program from a Special Menu Item?

Well, we can run our concurrent programs from a Special Menu Item, and if you have a requirement of this sort, you can use the steps below to use Form Personalization Builtin to achieve this task in couple of minutes.

1] First create the Special Menu Item wherever required through Form Personalization. Here I have added a Special Menu Item called ‘Assign Item to a Subinventory’ to the Form-‘INVIDITM’. For that assign the Trigger Event as ‘WHEN-NEW-FORM-INSTANCE’.

2] In Actions Tab, choose the type as ‘MENU’ and select the Menu Entry and give a Proper Menu Label.

3] Create the Concurrent Program which you want to attach to this custom menu item. Once created, assign the program to the Request Group of the Responsibility. Also it is required to add the ‘Lunch SRS Form’ Function (Requests: Submit) to the main menu of that responsibility. If you don’t do this step you may get Form Personalization error in later steps.

4] Create one more entry in Form Personalization window with Trigger Event as your custom menu item.

5] Here select the Actions Type as ‘Builtin’ and Builtin Type as ‘Lunch SRS Form’. In the program name give the name of your concurrent program.

6] Validate and Apply Now

Once done, you will be able to view the custom menu item in the Form and when you will click it, it opens the SRS Form with your concurrent program.

Query to find the Request Group of a Concurrent Program

Many a times we need to find out the Request Group of a Concurrent Program. In such cases the below query will be a useful one.

SELECT
  RG.APPLICATION_ID "Request Group Application ID",
  RG.REQUEST_GROUP_ID "Request Group - Group ID",
  RG.REQUEST_GROUP_NAME,
  RG.DESCRIPTION,
  rgu.unit_application_id,
  rgu.request_group_id "Request Group Unit - Group ID",
  rgu.request_unit_id,cp.concurrent_program_id,
  cp.concurrent_program_name,
  cpt.user_concurrent_program_name,
  DECODE(rgu.request_unit_type,'P','Program','S','Set',rgu.request_unit_type) "Unit Type"
FROM
  fnd_request_groups rg,
  fnd_request_group_units rgu,
  fnd_concurrent_programs cp,
  FND_CONCURRENT_PROGRAMS_TL CPT
WHERE rg.request_group_id = rgu.request_group_id
  AND rgu.request_unit_id = cp.concurrent_program_id
  AND cp.concurrent_program_id = cpt.concurrent_program_id
  AND cpt.user_concurrent_program_name =’<Your_Concurrent_Program_Name>’;

Know your Concurrent Program’s Performance

Know your Concurrent Program’s Performance

The below query will give you the time taken to execute the concurrent Programs with the latest concurrent programs with least execution time comes first.

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
order by
      f.actual_start_date desc,
      f.actual_completion_date-f.actual_start_date ;

Steps To Register Unix Shell Script As A Concurrent Program

Steps To Register Unix Shell Script As A Concurrent Program

We can register an Unix shell script in our oracle application through a concurrent program. Here are the steps.

Step 1:

Copy the .prog script in ASCII mode to the bin directory of your application top directory. For example, call the script XXSHELL.prog and place it under $XXCUST_TOP/bin

step 2:

Check the file permissions. Sometimes it is required to give full permission to the script.

step 3:

Make a symbolic link from your script to $FND_TOP/bin/fndcpesr For example, if the script is called XXSHELL.prog , then use this:

cd $XXCUST_TOP/bin

ln -s $FND_TOP/bin/fndcpesr XXSHELL

This link should be named the same as your script without the .prog extension. Put the link for your script in the same directory where the script is located.

step 4:

Register the concurrent program, using an execution method of ‘Host’. Use the name of your script without the .prog extension as the name of the executable.

For the example above: Use XXSHELL as executable name.

Note:

Your script will be passed at least 4 parameters, from $1 to $4.

$1 = orauser/pwd
$2 = userid(apps)
$3 = username,
$4 = request_id

Any other parameters you define will be passed in as $5 and higher. Make sure your script returns an exit status also.

fndcpesr is a standard utility available in $FND_TOP directory. It is mainly used by the application to parse the above four arguments to the shell scripts.

Query to find Parameters and Value Sets associated with a Concurrent Program

Query to find Parameters and Value Sets associated with a Concurrent Program

The following query will fetch the Parameter List and associated Value Sets of a Concurrent Program.

 

SELECT
        fcpl.user_concurrent_program_name "Concurrent Program Name",
        fcp.concurrent_program_name "Short Name",
        fdfcuv.column_seq_num "Column Seq Number",
        fdfcuv.end_user_column_name "Parameter Name",
        fdfcuv.form_left_prompt "Prompt",
        fdfcuv.enabled_flag " Enabled Flag",
        fdfcuv.required_flag "Required Flag",
        fdfcuv.display_flag "Display Flag",
        fdfcuv.flex_value_set_id "Value Set Id",
        ffvs.flex_value_set_name "Value Set Name",
        flv.meaning "Default Type",
        fdfcuv.DEFAULT_VALUE "Default Value"

FROM
        fnd_concurrent_programs fcp,
        fnd_concurrent_programs_tl fcpl,
        fnd_descr_flex_col_usage_vl fdfcuv,
        fnd_flex_value_sets ffvs,
        fnd_lookup_values flv

WHERE
        fcp.concurrent_program_id = fcpl.concurrent_program_id
        AND    fcpl.user_concurrent_program_name = :conc_prg_name
        AND    fdfcuv.descriptive_flexfield_name = '$SRS$.'
                 || fcp.concurrent_program_name
        AND    ffvs.flex_value_set_id = fdfcuv.flex_value_set_id
        AND    flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'
        AND    flv.lookup_code(+) = fdfcuv.default_type
        AND    fcpl.LANGUAGE = USERENV ('LANG')
        AND    flv.LANGUAGE(+) = USERENV ('LANG')

ORDER BY fdfcuv.column_seq_num;

AOL Terminology

AOL Terminology

Responsibilities:

A responsibility is a level of authority in Oracle Applications that lets users access only those Oracle Applications functions and data appropriate to their roles in an organization.

Request Group:

It is a collection of concurrent Programs. It is used to request programs from the responsibility.

Data Group:

It is a collection of Modules used to integrate one or more Modules for cross application transfer of data, cross application reporting and cross application reference. If we want to get data from other Modules we need to define those modules in the Data Group.

Functions:

A function is a part of an application’s functionality that is registered under a unique name for the purpose of assigning it to, or excluding it from, a menu (and by extension, a responsibility).

Menu:

A menu is a collection of Sub‐Menus and Functions.

Concurrent Program:

It is an instance of an execution file, along with parameter definitions and incompatibilities. Several concurrent programs may use the same execution file to perform their specific tasks, each having different parameter defaults and incompatibilities.

Concurrent Program Executable:

It is an executable file that performs a specific task. The file may be a program written in a standard language, a reporting tool or an operating system language.

Concurrent Request:

It is a request to run a concurrent program as a concurrent process.

Concurrent Process:

It is an instance of a running concurrent program that runs simultaneously with other concurrent processes.

Concurrent Manager:

It is a program that processes user’s requests and runs concurrent programs. System Administrators define concurrent managers to run different kinds of requests.

Value Set:

The value set is a collection (or) container of values. It provides list of values to the end user to accept one of the values as report parameter value.

Profiles:

A user profile is a set of changeable options that affects the way your applications run. Oracle Application Object Library establishes a value for each option in a user’s profile when the user logs on or changes responsibility.

Key Flexfields:

They are used to capture mandatory or Key Business information of the Organization. Each Key Flex Field is having its own base Table.

Descriptive Flexfields:

They are used to capture the additional or extra Business information of the organization. DFF are used to add extra accounts, those changes from one business to another business. All DFF columns are defined as Attribute Columns. All these columns are defined in the transaction table itself. There are around 5000+ DFF available.

FlexField Qualifiers:

A Flex field qualifier identifies a particular segment of a key flex field. These are based on Key Flex Fields (KFF). FFQs are varry from one KFF to another KFF and it is not compulsory that all the KFF should have FFQs.

Segment Qualifiers:

A Segment Qualifier identifies a particular type of value in a single segment of a key flex field. Segment Qualifier is based on FFQs and it is not compulsory that all the FFQs should have Segment Qualifiers.

Dynamic Insertion:

Dynamic Insertion is the insertion of new valid combination into a Key Flexfields Combinations Table from a form other than the combinations form.

Alerts:

Oracle Alert facilitates the flow of information within your organization by letting you create entities called alerts. Oracle Alert will send messages or perform predefined actions in an action set when important events occur. Alert is a mechanism that checks your database for a specific exception condition. Alerts are used to monitor your business information and to notify you of the information you want.

Standard Request Submission:

SRS provides you with a set of windows for running reports and Programs and a set of windows for creating groups of reports and programs to run together.

Script to get all the Concurrent Program Request details

Script to get all the Concurrent Program Request details

Below is the script to get the Concurrent Program Request details by Various Users in a Particular Day.

select
    request_id,
    parent_request_id,
    fcpt.user_concurrent_program_name Request_Name,
    fcpt.user_concurrent_program_name program_name,
    DECODE(fcr.phase_code,
            'C','Completed',
            'I','Incactive',
            'P','Pending',
            'R','Running') phase,
    DECODE(fcr.status_code,
            'D','Cancelled',
            'U','Disabled',
            'E','Error',
            'M','No Manager',
            'R','Normal',
            'I','Normal',
            'C','Normal',
            'H','On Hold',
            'W','Paused',
            'B','Resuming',
            'P','Scheduled',
            'Q','Standby',
            'S','Suspended',
            'X','Terminated',
            'T','Terminating',
            'A','Waiting',
            'Z','Waiting',
            'G','Warning','N/A') status,
    round((fcr.actual_completion_date - fcr.actual_start_date),3) * 1440 as Run_Time,
    round(avg(round(to_number(actual_start_date - fcr.requested_start_date),3) * 1440),2) wait_time,
    fu.User_Name Requestor,
    fcr.argument_text parameters,
    to_char (fcr.requested_start_date, 'MM/DD HH24:mi:SS') requested_start,
    to_char(actual_start_date, 'MM/DD/YY HH24:mi:SS') ACT_START,
    to_char(actual_completion_date, 'MM/DD/YY HH24:mi:SS') ACT_COMP,
    fcr.completion_text

From

    apps.fnd_concurrent_requests fcr,
    apps.fnd_concurrent_programs fcp,
    apps.fnd_concurrent_programs_tl fcpt,
    apps.fnd_user fu

Where 1=1
    -- and fu.user_name = 'DJKOCH' '
    -- and fcr.request_id = 1565261
    -- and fcpt.user_concurrent_program_name = 'Payables Open Interface Import''
    and fcr.concurrent_program_id = fcp.concurrent_program_id
    and fcp.concurrent_program_id = fcpt.concurrent_program_id
    and fcr.program_application_id = fcp.application_id
    and fcp.application_id = fcpt.application_id
    and fcr.requested_by = fu.user_id
    and fcpt.language = 'US'
    and fcr.actual_start_date like sysdate
    -- and fcr.phase_code = 'C'
    -- and hold_flag = 'Y'
    -- and fcr.status_code = 'C'

GROUP BY
    request_id,
    parent_request_id,
    fcpt.user_concurrent_program_name,
    fcr.requested_start_date,
    fu.User_Name,
    fcr.argument_text,
    fcr.actual_completion_date,
    fcr.actual_start_date,
    fcr.phase_code,
    fcr.status_code,
    fcr.resubmit_interval,
    fcr.completion_text,
    fcr.resubmit_interval,
    fcr.resubmit_interval_unit_code,
    fcr.description

Order by 1 desc;

Registering SQL*Loader as a Concurrent Program

Registering SQL*Loader as a Concurrent Program

The following steps will describe the process to register a SQL*Loader program as a Concurrent Program in Oracle Apps.

Step 1]

Create the SQL*Loader Control and Data file and place them in Server(ex: $CUSTOM_TOP/bin). Create or check the interface table structures in the backend.

Control file: test.ctl

Data file: test.dat

CREATE TABLE testdept
(deptno NUMBER(2) NOT NULL,
 dname VARCHAR2(14),
 loc VARCHAR2(13));



 
Step 2]
Go to Application Developer > Concurrent > Executables. Define a Concurrent Program Executable. Choose the Execution Method as SQL*Loader and give the Execution File Name as the name of the SQL*Loader control file. Save your work.

Step 3]
Go to Application Developer > Concurrent > Program. Define the Concurrent Program. Attach the executable defined above.

Step 4]
Go to parameters of the concurrent program. Create a parameter to take the server path of the data file. You can also place the default value.

Step 5]
Attach the Concurrent program to a Responsibility through a Request Group.
Step 6]
Go to that Responsibility and Run the Concurrent Program. If successful check the output file that have all data uploading information.
Number to load: ALL

Number to skip: 0

Errors allowed: 50

Bind array: 64 rows, maximum of 256000 bytes

Continuation: none specified

Path used: Conventional
Table TESTDEPT, loaded from every logical record.

Insert option in effect for this table: INSERT
   Column Name                  Position   Len  Term Encl Datatype

—————————— ———- —– —- —- ———————

DEPTNO                               FIRST     *   ,  O(“) CHARACTER           

DNAME                                NEXT     *   ,  O(“) CHARACTER           

LOC                                     NEXT     *   ,  O(“) CHARACTER           
Table TESTDEPT:

  7 Rows successfully loaded.

  0 Rows not loaded due to data errors.

  0 Rows not loaded because all WHEN clauses were failed.

  0 Rows not loaded because all fields were null.
Space allocated for bind array:49536 bytes(64 rows)

Read   buffer bytes: 1048576
Total logical records skipped:           0

Total logical records read:               7

Total logical records rejected:          0

Total logical records discarded:        0
Run began on Thu Aug 12 09:41:55 2010

Run ended on Thu Aug 12 09:41:56 2010
Elapsed time was:      00:00:00.11

CPU time was:           00:00:00.01
Step 7]
Check in the backend whether the tables got updated or not.
 

 
The Bad and Discard files will be created in /conc/out file of the server.