Oracle Enterprise Scheduler

Oracle Enterprise Scheduler (ESS) is an application plugged into Fusion Applications to define, schedule and run different types of jobs and do advance scheduling. This functions similar to Concurrent Manager/Program in EBS, but with lot more new functionalities and of course with more user friendly navigations.

Oracle Enterprise Scheduler lets you to
  • Run various jobs, supports PL/SQL, Java and other binary processes
  • Distribute job request processing across a grid of application servers
  • Group job requests into job sets, like request set in EBS
  • Schedule & Automate job requests based on recurrence expressions
  • Support sophisticated scheduling and workload management
  • Schedule a future time for a step in a business flow for business process management
  • Also to extend the standard application to manage job request submissions
Refer Fusion Applications Developer's Guide for Oracle Enterprise Scheduler document to learn more about this.

Extensible Flexfield (EFF)

Extensible Flexfield (EFF) is a new feature added in Fusion Application with existing Descriptive & Key Flexfields. It is similar to descriptive flexfield, but with the ability to add as many context-sensitive segments (attributes) to a flexfield as they need.

Some of the key benefits using Extensible Flexfields are
  1. Number of configurable segments are not fixed unlike fixed number of segments in DFF
  2. Attributes can be grouped into one or more attribute groups (ie contexts) rather than only one in DFF
  3. EFF supports one-to-many relationships between the row and the extended attribute rows
  4. Improved access controls than using DFF. It is possible to control the view and edit the attributes based on the context configurations.
To know more about this, refer Fusion Application Developer’s Guide & Extensibility Guide.

How to use ADF in E-Business Suite?

Oracle recently announced the different types of supported integration between EBS and ADF.

1. Integration to EBS using the Oracle SOA Suite and Application Development Framework 11g (ADF 11g). This type of integration typically uses the AppsDataSource feature but no other parts of EBS SDK.
2. Launch ADF Application from EBS, in this case the Oracle ADF user interface is launched from EBS home page. There is no further user interface interaction from ADF application with EBS.
3. Integration that allows users to move back and forth between the ADF user interface and EBS. For example, a user could go to an ADF page from an OA Framework page and back again, or ADF components could be embedded into an OAF page. This requires some context being shared between OAF and ADF, as well as session management between the two systems.

However option 2 and 3 supported only from R12, 3rd one is still not fully supported. For more detailed information refer Oracle Support Document (1296491.1).

Responsibilities using Concurrent Program

We know to use any Concurrent Program first it needs to be added to the Request Group and Request Group needs to be attached to Responsibility. Then we can able to submit the program using program request form.

One program can be attached to many Request Groups. Again Request Group can be attached to many Responsibilities. Because of One-Many-Many relationship, it is very difficult to find out responsibilities using particular concurrent program in UI forms.

But we can get the list from database using the following SQL...

SELECT
FRS.responsibility_key
,FRS.responsibility_name
,FRG.request_group_name
,FCP.concurrent_program_name
,FCP.user_concurrent_program_name
FROM
fnd_concurrent_programs_vl FCP
,fnd_request_group_units FRU
,fnd_request_groups FRG
,fnd_responsibility_vl FRS
WHERE FCP.concurrent_program_name = (Program_Name)
AND FRU.request_unit_id = FCP.concurrent_program_id
AND FRG.application_id = FRU.application_id
AND FRG.request_group_id = FRU.request_group_id
AND FRS.group_application_id = FRG.application_id
AND FRS.request_group_id = FRG.request_group_id

SQL to get list of Scheduled Concurrent Programs

Any Oracle Application (EBS) user can schedule Concurrent Programs, if he has access to it. Down the line, it is very difficult for users/administrators/developers to remember list of schedules concurrent programs. Following SQL will help to get the latest list of Scheduled Concurrent Programs.

SELECT FCR.request_id REQUEST_ID
,FCP.concurrent_program_name PROGRAM_SHORT_NAME
,FCP.user_concurrent_program_name PROGRAM_NAME
,FNU.user_name SUBMITTED_BY
,TO_CHAR(FCR.requested_start_date
,'DD-MON-YYYY HH24:MM:SS'
) REQUEST_START_DATE
,'Every '|| DECODE(LENGTH(FCL.class_info)
,39,FCL.class_info
,SUBSTR(FCL.class_info,1,INSTR(FCL.class_info,':',1)-1)||' '
|| DECODE(SUBSTR(FCL.class_info,INSTR(FCL.class_info,':',1)+1,1)
,'N','Minute(s) '
,'D','Day(s) '
,'H','Hour(s) '
,'M','Month(s) '
)
|| 'after '
|| DECODE(SUBSTR(FCL.class_info,INSTR(FCL.class_info,':',1,2)+1,1)
,'S','Start '
,'C','Completion '
)
|| 'of prior request'
) SCHEDULED_INTERVAL
,NVL(TO_CHAR(FCL.end_date_active
,'DD-MON-YYYY'),'forever'
) ENDING_ON
FROM APPS.fnd_concurrent_requests FCR
,APPS.fnd_concurrent_programs_vl FCP
,APPS.fnd_user FNU
,APPS.fnd_conc_release_classes FCL
WHERE FCR.phase_code = 'P'
AND FCR.status_code IN ('I','Q')
AND FCR.program_application_id = FCP.application_id
AND FCR.concurrent_program_id = FCP.concurrent_program_id
AND FCR.requested_by = FNU.user_id
AND FCR.release_class_app_id = FCL.application_id
AND FCR.release_class_id = FCL.release_class_id
ORDER BY FCP.concurrent_program_name
, FCR.requested_start_date

Submitting XML Publisher Report Concurrent Program from PL/SQL

XML Publisher template will be attached as layout when we submit concurrent program using request form based on the program short name. Whereas, if we submit the program using FND_REQUEST.submit_request API then we will find only XML output, template won’t be used.

To avoid this issue, program layout needs to be set using FND_REQUEST.add_layout API with template application, code, language, territory and output format before submit request.

Object Types in Integration PLSQL API using OA Adapter

If you are planning to use plsql API with plsql collection parameters for any integration using OA Adapter / DB Adapter then use Object Types rather than plsql collections (ie tables and records).

Advantages are

  1. Adapter can not use plsql tables and records directly. It will create similar database Object Type and use that in xsd creation, transformation etc. It also creates package to assign plsql collection into object types. This can be avoided if we use database object.
  2. If there are any changes then just we need to change the object type and respective xsd. If we use plsql tables and records then we have to make changes in too many places. Some time it creates major issues in particular if we have complex transformations and assignments.