Concurrent Program Scheduler Tool - Appworx (Part II)

1. Procedure to Create User -

Step 1 - From the Users selector window, click New as shown in Figure A.

Figure A. User Selector

Step 2 - AppWorx opens the Users window shown in Figure B. Complete the required                            details using Table A and click on Apply button, your user is Created..!!.

Table A. User General tab field description

Field	Description
User Name	The name the user will enter on the AppWorx Login Window. Entry can be 30 characters long.
First Name Last Name/Description	The user’s first and last name or description.
Active	Designates the user as active or inactivate.
Password	The password for the user. Entry can be 30 characters long.
Expire interval	The number of days until the user's password will expire (forcing them to specify a new one).
Last modified	This non-edit field displays the date and time the password was last changed

Figure B. User Creation Window

Step 2 - The AppWorx user options control user access to specific features. Select from the list and press up button from AppWorx of user group and click on OK.

Figure C. Assign User group to User Window

Concurrent Programs Scheduler Tool - Appworx (Part I)

1. Introduction to AppWorx 
     

   AppWorx provides a complete background-processing management system. With AppWorx, you can do all the traditional batch management tasks, as well as submit jobs and view output. However, instead of writing scripts to perform tasks, you create AppWorx modules that run programs. You combine modules to create chains. You create a schedule for each chain (or for each module if you wish) using a full complement of scheduling tools including custom calendars and execution conditions.

As with all modular systems, you create individual objects once, then use them in different combinations to accomplish a variety of tasks. In the context of batch management, this can save you hours of time compared to writing, debugging, and maintaining scripts. A diagram of the AppWorx modular approach to operations is shown in Figure A.

A Different Way of Thinking -

In a traditional operations environment, scripts drive operations. Scripts incorporate the information required to run one or more programs on a set schedule, direct output, and handle exceptions. The problem with scripts is the required time maintaining them when the system changes. For example, if a printer definition changes, you must change it in every script. AppWorx takes the individual components of a script such as programs, schedules, printers, and variables, and lets you define them as discrete objects. You can then combine the objects in an unlimited number of combinations to handle your operations. The advantage is being able to change an object in one place, and have the changes roll over to every use of the object.

Figure A. AppWorx uses a modular approach to replace the tedious tasks of writing and maintaining scripts.

---------------------------------------------------------------------------------------------

2. Objects in AppWorx

1. User 

The Users are created for Controlling access to AppWorx. You can assign names, access permissions, user options, and roles to AppWorx users.

2. User Authority

User authority is control user access to Application Manager window and can give Users and privileges for objects.

3. User Group

User group is control access to all areas of Application Manager. In a traditional system, you create a groups of User, Output, Devices and Applications. User groups can contain any combination of objects, and objects can be assigned to any User group.

4. Agent

Agents are instances of application manager. An agent is installed on each machine where new tasks are executed. An agent can be Local agent or Remote agent.

5. Module (Job)

Module is the basic building block in AppWorx. For each program which you want to run (for example: FTP, application, database load), you must create a module. A module contains all the information required to execute a program. Modules are run both individually and as components of AppWorx chains. Furthermore, a module can be a component of as many chains as you wish. If you change a module definition, the change is applied to every chain that includes it.

6. Chains (Process Flows)

Process flows are the combination of one or more jobs(Concurrent Programs from EBS), which are scheduled as per requirement. For running the chain, specific eligibility of its component and condition must meet.

7. Chain Components

Chain components are modules and chains assigned to the chain. When you add a component to a chain, AppWorx creates a pointer from the chain to the module or chain instead of adding a copy of it. This is in keeping with the AppWorx object-oriented approach. If you edit a module or chain definition, the update is effective immediately for all chains that include it.

8. Notification

Application Manager includes the notification which alerts you to unusual task runs. If you want to be notified when task does not completed or not completed task in expected time. You can receive notification by email.

9. Substitution Variables

It stores the values that can be used in Jobs and Process Flows. You can add the SQL statement to pull the value from database, variable name such as #XXOrganization_id, in prompts and execution conditions assigned to Jobs.

Basic SQL Queries used in Oracle Apps

Below query will fetch the data for Concurrent Program attached to which responsibility -

  SELECT DISTINCT fcpt.user_concurrent_program_name,
                             frt.responsibility_name,
                             frg.request_group_name,
                             frgu.request_unit_type,
                             frgu.request_unit_id
    FROM apps.fnd_Responsibility fr,
             apps.fnd_responsibility_tl frt,
             apps.fnd_request_groups frg,
             apps.fnd_request_group_units frgu,
             apps.fnd_concurrent_programs_tl fcpt
   WHERE     frt.responsibility_id = fr.responsibility_id
         AND frg.request_group_id = fr.request_group_id
         AND frgu.request_group_id = frg.request_group_id
         AND fcpt.concurrent_program_id = frgu.request_unit_id
         AND frt.language = USERENV ('LANG')
         AND fcpt.language = USERENV ('LANG')
         AND fcpt.user_concurrent_program_name LIKE  '%<Concurrent Program Name>%'
ORDER BY 2;

Below query will fetch the data from which responsibility request was submitted:-

SELECT   c.request_id, 
                 frv.responsibility_name
  FROM    fnd_responsibility_vl frv, 
                 fnd_concurrent_requests fcr
 WHERE  fcr.request_id = <Request ID>
       AND  fcr.responsibility_application_id = frv.application_id
       AND  fcr.responsibility_id = frv.responsibility_id;

Below query will fetch the data which function is attached to which responsibilities:-

SELECT  fr.responsibility_name, 

                fm.menu_name, 

                ff.user_function_name

  FROM   apps.fnd_responsibility_vl fr,

                apps.fnd_menus fm,

                apps.fnd_compiled_menu_functions fmf,

                apps.fnd_Form_functions_vl ff

 WHERE  fr.menu_id = fm.menu_id

       AND  ff.function_id = fmf.function_id

       AND  fmf.menu_id = fm.menu_id

       AND  ff.user_function_name LIKE '%<Function Name>%';

Below query will fetch the data of Request group and it's concurrent programs:-

SELECT cpt.user_concurrent_program_name "Concurrent Program Name",

               DECODE (rgu.request_unit_type,

                                     'P', 'Program',

                                     'S', 'Set',

                               rgu.request_unit_type)          "Unit Type",

               cp.concurrent_program_name "Concurrent Program Short Name",

               rg.application_id "Application ID",

               rg.request_group_name "Request Group Name",

               fat.application_name "Application Name",

               fa.application_short_name "Application Short Name",

               fa.basepath "Basepath"

  FROM  apps.fnd_request_groups rg,

               apps.fnd_request_group_units rgu,

               apps.fnd_concurrent_programs cp,

               apps.fnd_concurrent_programs_tl cpt,

               apps.fnd_application fa,

               apps.fnd_application_tl fat

 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 rg.application_id = fat.application_id

       AND fa.application_id = fat.application_id

       AND cpt.language = USERENV ('LANG')

       AND fat.language = USERENV ('LANG')

       AND rg.request_group_name LIKE '%<Request Group Name>%';

Below Query for Extracting the data for Alert in Oracle Apps:-

SELECT  fav.application_short_name,
                aa.alert_name,
                aa.description alert_description,
                aa.start_date_active,
                aa.enabled_flag,
                aa.sql_statement_text,
                aav.description alert_action_type,
                aav.to_recipients,
                aav.cc_recipients,
                aav.bcc_recipients
  FROM   apps.alr_actions_v aav, 
               apps.alr_alerts aa, 
               apps.fnd_application_vl fav
 WHERE aav.alert_id = aa.alert_id
       AND aav.application_id = aa.application_id
       AND fav.application_id  = aa.application_id
       AND (fav.application_short_name LIKE '%<Application Short Name>%' 
               OR  aa.alert_name LIKE '%<Alert Name>%');


Below Query will Find all the fields in a given Descriptive Flex Field(DFF):-

  SELECT   ffv.descriptive_flexfield_name DFF_Name,
                    ffv.application_table_name Table_Name,
                    ffv.title Title,
                    ap.application_name Application,
                    att.column_seq_num SegmentNumber,
                    att.form_left_prompt SegmentName,
                    att.application_column_name,
                    fvs.flex_value_set_name ValueSet,
                    att.required_flag
    FROM     apps.fnd_descriptive_flexs_vl ffv,
                    apps.fnd_descr_flex_contexts_vl ffc,
                    apps.fnd_descr_flex_col_usage_vl att,
                    apps.fnd_flex_value_sets fvs,
                    apps.fnd_application_vl ap
   WHERE   ffv.descriptive_flexfield_name = att.descriptive_flexfield_name
         AND   ap.application_id = ffv.application_id
         AND   ffv.descriptive_flexfield_name = ffc.descriptive_flexfield_name
         AND   ffv.application_id = ffc.application_id
         AND   ffc.descriptive_flex_context_code =
                    att.descriptive_flex_context_code
         AND  fvs.flex_value_set_id = att.flex_value_set_id
         AND  ffv.title IN ('<Descriptive FF Title>')
ORDER BY ffv.title;


Below Query will find User and it's attached responsibilities:-

SELECT   fu.user_name,
                 fr.responsibility_name,
                 furg.start_date,
                 furg.end_date
  FROM   apps.fnd_user_resp_groups_direct furg,
                apps.fnd_user fu,
                apps.fnd_responsibility_vl fr
 WHERE fu.user_name = UPPER ('<User Name>')
       AND furg.user_id = fu.user_id
       AND furg.responsibility_id = fr.responsibility_id;


Below Query will find Form Personalization and it's assigned responsibilities:-

SELECT rmcr.description Personalization,
              (CASE
                    WHEN fmcs.level_value =
                          (SELECT responsibility_id
                              FROM apps.fnd_responsibility_vl
                           WHERE responsibility_id = fmcs.level_value)
                    THEN
                          (SELECT responsibility_name
                              FROM apps.fnd_responsibility_vl
                           WHERE responsibility_id = fmcs.level_value)
              END)
               "Responsibility Name"
  FROM    apps.fnd_form_custom_scopes fmcs,
                apps.fnd_responsibility_vl frv,
                apps.fnd_form_custom_rules rmcr
 WHERE  level_id = 30
       AND frv.responsibility_id = fmcs.level_value
       AND rmcr.id = fmcs.rule_id

       AND rmcr.description LIKE '%<Personalization Name>%';


Below Query will find Concurrent Program's parameter:-

SELECT fcpl.user_concurrent_program_name,
               fcp.concurrent_program_name,
               fcp.concurrent_program_id,
               fav.application_short_name,
               fav.application_name,
               fav.application_id,
               fdfcuv.end_user_column_name,
               fdfcuv.form_left_prompt prompt,
               fdfcuv.enabled_flag,
               fdfcuv.required_flag,
               fdfcuv.display_flag
  FROM  apps.fnd_concurrent_programs fcp,
               apps.fnd_concurrent_programs_tl fcpl,
               apps.fnd_descr_flex_col_usage_vl fdfcuv,
               apps.fnd_application_vl fav
 WHERE fcp.concurrent_program_id = fcpl.concurrent_program_id
       AND fcpl.user_concurrent_program_name = '<User Concurrent Program Name>'
       AND fav.application_id = fcp.application_id
       AND fcpl.language = 'US'
       AND fdfcuv.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name;


Below Query will find Responsibility level Profile Options:-

  SELECT NVL (g.responsibility_name, c.level_value) "Responsibility",
               b.user_profile_option_name "Profile",
               c.profile_option_value "Profile Value",
               (SELECT application_name
                  FROM apps.fnd_application_vl
                WHERE application_id = g.application_id)
                "Application"
    FROM  apps.fnd_profile_options a,
                 apps.fnd_profile_options_vl b,
                 apps.fnd_profile_option_values c,
                 apps.fnd_user d,
                 apps.fnd_user e,
                 apps.fnd_responsibility_vl g,
                 apps.fnd_application h
   WHERE 1 = 1
         AND a.profile_option_name = b.profile_option_name
         AND a.profile_option_id = c.profile_option_id
         AND a.application_id = c.application_id
         AND c.last_updated_by = d.user_id(+)
         AND c.level_value = e.user_id(+)
         AND c.level_value = g.responsibility_id(+)
         AND c.level_value = h.application_id(+)
         AND c.level_id = 10003
         AND g.responsibility_name LIKE '%<Responsibility Name>%'
ORDER BY 1;