Oracle HCM SQL Toolkit – Part 3: ESS Scheduled Process Details and Status

Oracle HCM SQL Toolkit – Part 3: ESS Scheduled Process Details and Status

Welcome to Part 3 of the Oracle HCM SQL Toolkit, a continuing series of practical Oracle HCM SQL queries every consultant should keep handy.

In this post, we move to another area that comes up frequently in Oracle HCM troubleshooting:

Scheduled Processes and ESS request history.

Oracle HCM performs a significant amount of background processing through Enterprise Scheduler Service (ESS).

When investigating a Scheduled Process, it is often useful to know:

• What all processes are scheduled, and you don’t have access to see from the UI
• What process was submitted?
• Who submitted it?
• When was it submitted?
• When did execution actually start?
• When did it finish?
• What is its current status?
• What is the technical process name?

The Query

SELECT
    erh.requestid AS "Process ID",
    SUBSTR(
        erh.definition,
        INSTR(erh.definition, '/', -1) + 1
    ) AS "Process Name",
    erh.username AS "Submitted By",
    TO_CHAR(
        erh.submission,
        'YYYY-MM-DD HH24:MI:SS'
    ) AS "Submission Time",
    TO_CHAR(
        erh.processstart,
        'YYYY-MM-DD HH24:MI:SS'
    ) AS "Start Time",
    TO_CHAR(
        erh.processend,
        'YYYY-MM-DD HH24:MI:SS'
    ) AS "End Time",
    DECODE(
        erh.state,
        1,  'Wait',
        2,  'Ready',
        3,  'Running',
        4,  'Completed',
        5,  'Blocked',
        6,  'Hold',
        7,  'Canceling',
        9,  'Canceled',
        10, 'Error',
        11, 'Warning',
        12, 'Succeeded',
        erh.state
    ) AS "Status",
    erh.definition AS "Full Definition Path"
FROM
    fusion.ess_request_history erh
WHERE 1 = 1
  AND erh.state IN (1,6)
ORDER BY
    erh.submission DESC;

In this example, the query returns requests in Wait and Hold status because of:

erh.state IN (1,6)

ESS State Values

State	Status
1	Wait
2	Ready
3	Running
4	Completed
5	Blocked
6	Hold
7	Canceling
9	Canceled
10	Error
11	Warning
12	Succeeded

Error and Warning Requests

AND erh.state IN (10,11)

Currently Running Requests

AND erh.state = 3

Technical Process Name vs. Scheduled Process Display Name

One particularly useful detail when working with ESS is that the internal technical process name may be different from the Scheduled Process name that functional users recognize in the application.

Technical Process Name	Scheduled Process / Display Name
TCDEventInternalizingProcessJob	Generate Time Cards from Time Collection Devices
HrcCommunicationManager	Manage Communication Responses
DataSecurityAclRefresh	Compute Users ACL by Events
SyncRolesJob	Retrieve Latest LDAP Changes
ACRPLENR	Update Accrual Plan Enrollments
ACRPRC	Calculate Accruals and Balances
BENDSGEL	Reevaluate Designee Eligibility
BENMNGLE	Evaluate Life Event Participation
FlowEssJobDefn	HCM Flow Secured
PurgeSpreadsheetLoaderHistoryJob	Delete HCM Spreadsheet Data Loader Stage Table Data
PurgeExtractPayrollActionsJob	Purge Extracts Archive Data
PurgeExtractsPartitionsAuto	Purge Extracts Archive Partitions
CloudMetricsNumberOfNamedUsers	Generate Cloud Usage Metrics
TimeComplianceRulesCheckJob	Generate Time Exceptions from Compliance Rules
BENCLENR	Close Enrollment
BENCLLPSLE	Collapse Life Events
BENTEMOD	Evaluate Temporal Event Participation
BENASLFE	Assign Corrective Potential Life Event
BENMNGBENREL	Assign and Update Benefits Relationships
HcmAtomFeedPurgeJob	Purge Atom Feed Entries from Oracle Fusion Schema
PurgeObsoletedSmartActions	Purge Obsoleted Smart Action Records
HcmAlertRunPurgeJob	Purge Alert Processing and Log Entries
GenericFeatureUpgrade	Generic Feature Upgrade
SendCommunication	Send Communication
HcmPurgeSensitiveDataAccessAuditJob	Purge Sensitive Data Access Audit
GroupsEvalJob	Evaluate Group Membership
SynchronizeManagerHierarchyJob	Synchronize Person Assignments from Position

for example-

The technical ESS process name is:

TimeComplianceRulesCheckJob

You can filter ESS history using:

AND erh.definition LIKE '%TimeComplianceRulesCheckJob%'

and review when requests were submitted, when they started, how long they ran, whether requests remained waiting, and whether they ended with errors or warnings.

Oracle HCM SQL Toolkit – Part 3 Recap

This query provides a useful view of:

Scheduled Process → Technical ESS Job → Status and Execution Details

It is particularly useful when troubleshooting background processing or trying to connect the functional Scheduled Process name with the underlying ESS technical process.

Query for attachments in oracle apps

You can get attached_document_id of approximate all applications transaction as shown below.

=============================================================

SELECT  fad.attached_document_id         attached_document_id
FROM    applsys.fnd_attached_documents   fad
WHERE   fad.entity_name                  = 'R_DOCUMENT_EXTRA_INFO'
AND     FAD.PK1_VALUE                    IN(SELECT  HDEI.DOCUMENT_EXTRA_INFO_ID  DOCUMENT_EXTRA_INFO_ID
                                            FROM    hr.hr_document_extra_info HDEI
                                           )
UNION
SELECT fad.attached_document_id          attached_document_id
FROM   applsys.fnd_attached_documents    fad
WHERE  fad.entity_name                   = 'PQH_SS_ATTACHMENT'
AND    fad.pk1_value                     IN(SELECT hat.transaction_id      transaction_id
                                            FROM   hr.hr_api_transactions  hat
                                            )
UNION
SELECT fad.attached_document_id          attached_document_id
FROM   applsys.fnd_attached_documents    fad
WHERE  fad.entity_name                   = 'AP_INVOICES'
AND    fad.pk1_value                     IN(SELECT aia.invoice_id      invoice_id
                                            FROM   ap.ap_invoices_all  aia
                                            )
UNION
SELECT fad.attached_document_id          attached_document_id
FROM   applsys.fnd_attached_documents    fad
WHERE  fad.entity_name                   IN ('PO_HEAD','PO_HEADERS')
AND    fad.pk1_value                     IN (SELECT pha.po_header_id   header_id
                                             FROM   po.po_headers_all  pha
                                             )
UNION
SELECT fad.attached_document_id           attached_document_id
FROM   applsys.fnd_attached_documents     fad
WHERE  fad.entity_name                    = 'OIE_HEADER_ATTACHMENTS'
AND    fad.pk1_value                      IN(SELECT  arha.report_header_id            report_header_id
                                             FROM    ap.ap_expense_report_headers_all arha
                                          )
UNION
SELECT fad.attached_document_id       attached_document_id
FROM   applsys.fnd_attached_documents fad
WHERE  fad.entity_name                = 'OKC_CONTRACT_DOCS'
AND    fad.pk2_value                  IN(SELECT ocd.business_document_id
                                         FROM   okc.okc_contract_docs ocd
                                          )
UNION
SELECT  fad.attached_document_id       attached_document_id
FROM    applsys.fnd_attached_documents fad
WHERE   fad.entity_name                = 'PO_LINES'
AND     fad.pk1_value                  IN(SELECT pla.po_line_id   po_line_id
                                          FROM   po.po_lines_all  pla            
                                          )
UNION
SELECT fad.attached_document_id        attached_document_id
FROM   applsys.fnd_attached_documents  fad
WHERE  fad.entity_name                 = 'OIE_LINE_ATTACHMENTS'
AND    fad.pk1_value                   IN(SELECT arla.report_header_id          report_header_id
                                          FROM   ap.ap_expense_report_lines_all arla
                                          )
UNION
SELECT fad.attached_document_id       attached_document_id
FROM   applsys.fnd_attached_documents fad
WHERE  fad.entity_name                = 'REQ_HEADERS'
AND    fad.pk1_value                  IN(SELECT prha.requisition_header_id    requisition_header_id
                                         FROM   po.po_requisition_headers_all prha
                                         )
UNION
SELECT fad.attached_document_id        attached_document_id
FROM   applsys.fnd_attached_documents  fad
WHERE  fad.entity_name                 ='REQ_LINES'
AND    fad.pk1_value                   IN(SELECT prla.requisition_line_id     requisition_line_id
                                          FROM   po.po_requisition_lines_all  prla
                                          )
UNION
SELECT fad.attached_document_id         attached_document_id
FROM   applsys.fnd_attached_documents   fad
WHERE  fad.entity_name                  ='PO_SHIPMENTS'
AND    fad.pk1_value                    IN(SELECT plla.line_location_id     line_location_id
                                           FROM   po.po_line_locations_all  plla
                                           )
UNION
SELECT fad.attached_document_id        attached_document_id
FROM   applsys.fnd_attached_documents  fad
WHERE  fad.entity_name                 = 'IBY_PAY_INSTRUCTIONS_ALL'
AND    fad.pk1_value                   IN(SELECT pia.payment_instruction_id     payment_insruction_id
                                          FROM   iby.iby_pay_instructions_all   pia
                                          )
UNION
SELECT fad.attached_document_id        attached_document_id
FROM   applsys.fnd_attached_documents  fad
WHERE  fad.entity_name                 = 'PER_ABSENCE_ATTENDANCES'
AND    fad.pk1_value                   IN(SELECT to_char(paa.absence_attendance_id) absence_attendance_id
                                          FROM   hr.per_absence_attendances         paa
                                          )
UNION
SELECT fad.attached_document_id        attached_document_id
FROM   applsys.fnd_attached_documents  fad
WHERE  fad.entity_name                 = 'XX_LOOKUP_DOCUMENT_ENTITY'
AND    fad.pk2_value                   LIKE '%BBW%%'
UNION
SELECT fad.attached_document_id        attached_document_id
FROM   applsys.fnd_attached_documents  fad
WHERE  fad.entity_name                 = 'PER_PEOPLE_F'
AND    fad.pk1_value                   IN(SELECT asg.person_id             person_id
                                          FROM   hr.per_all_assignments_f  asg
                                          )
UNION
SELECT fad.attached_document_id        attached_document_id
FROM   applsys.fnd_attached_documents  fad
WHERE  fad.entity_name                 = 'PAY_ELEMENT_ENTRIES_F'
AND    fad.pk1_value                   IN(SELECT peef.element_entry_id    element_entry_id
                                          FROM   hr.pay_element_entries_f peef
                                          )
UNION
SELECT fad.attached_document_id        attached_document_id
FROM   applsys.fnd_attached_documents  fad
WHERE  fad.entity_name                 = 'OTA_DELEGATE_BOOKINGS'
AND    fad.pk1_value                   IN(SELECT odb.booking_id             booking_id
                                          FROM   OTA.OTA_DELEGATE_BOOKINGS  ODB
                                          )

==================================================================

Once you have the attached_document_id

you can use below query


SELECT fd.* FROM FND_ATTACHED_DOCUMENTS FAD, FND_DOCUMENTS FD
WHERE FAD.DOCUMENT_ID=FD.DOCUMENT_ID;

based on datatype_id in fnd_documents

you can join with any of

,APPLSYS.FND_LOBS
,APPLSYS.FND_DOCUMENTS_SHORT_TEXT
,APPLSYS.FND_DOCUMENTS_LONG_TEXT

tables.

How to download attachment stored in fnd_lobs table.

How to download attachment stored in fnd_lobs table

-        -          Open employee’s record and click on attachment icon.

-      Attach any new document under category as show below.

Now you can see the attachment symbol.

Attachment will be stored in fnd_lobs table.

below are queries to identify the attachment record.

select * from fnd_attached_documents

where entity_name='PER_PEOPLE_F'

and pk1_value=33579

order by creation_date desc;

select * from fnd_documents

where document_id=2349427;

select * from fnd_lobs

where file_id=2990785;

Now if you download the attachment through pl/sql procedure.

prerequisite 

- server directory -  where you want to download the file - generally database tier

- read/write access on the that directory.

- proper privileges on UTL_file packages

you can below procedure to download above attachment.

CREATE OR REPLACE PROCEDURE APPS.xx_download_attachment

AS

   CURSOR cur_new_attmt

   IS

      SELECT fl.file_id, fl.file_name, fl.file_data,

             DBMS_LOB.getlength (fl.file_data) file_length

        FROM fnd_lobs fl

       WHERE fl.file_id =2990785;

   v_start           NUMBER             DEFAULT 1;

   v_bytelen         NUMBER             DEFAULT 32000;

   v_len_copy        NUMBER;

   v_raw_var         RAW (32000);

   v_output          UTL_FILE.file_type;

   v_inv_file_name   VARCHAR2 (100);

   v_position        NUMBER;

   v_first_rec       BOOLEAN            DEFAULT TRUE;

BEGIN

   v_position := 10;

   FOR rec_inv IN cur_new_attmt

   LOOP

      v_inv_file_name := NULL;

      v_inv_file_name := rec_inv.file_name;

      v_position := 20;

      -- define output directory AND OPEN THE file IN WRITE BYTE MODE

      v_output := UTL_FILE.fopen ('ODPDIR', v_inv_file_name, 'wb', 32760);

      v_position := 30;

      -- maximum size OF buffer parameter IS 32767 BEFORE

      -- which you have TO flush  your buffer

      IF rec_inv.file_length < 32760

      THEN

         UTL_FILE.put_raw (v_output, rec_inv.file_data);

         UTL_FILE.fflush (v_output);

      ELSE

         v_position := 40;

         v_start := 1;

         v_bytelen := 32000;

         v_len_copy := rec_inv.file_length;

         WHILE v_start < rec_inv.file_length AND v_bytelen > 0

         LOOP

            v_position := 50;

            DBMS_LOB.READ (rec_inv.file_data, v_bytelen, v_start, v_raw_var);

            v_position := 60;

            UTL_FILE.put_raw (v_output, v_raw_var);

            v_position := 70;

            UTL_FILE.fflush (v_output);

            v_start := v_start + v_bytelen;

            v_len_copy := v_len_copy - v_bytelen;

            IF v_len_copy < 32000

            THEN

               v_bytelen := v_len_copy;

            END IF;

         END LOOP;

         v_position := 80;

         UTL_FILE.fclose (v_output);

      END IF;

      v_position := 90;

      DBMS_OUTPUT.put_line (' File_name :' || rec_inv.file_name);

   END LOOP;

END xx_download_attachment;

/

=======================================================================

execute the procedure to download the attachment.

execute xx_download_attachment;

Note : 

-  'ODPDIR' is the directory name.

refer dba_directories for more detail.

select * from dba_directories;

How to change current session language

How to change current session language in oracle

select * from per_business_groups;

output : no rows selected.

sometime some views will not return any data because of current session language.
Language install in your application is 'US', but what if your current session language is 'GB' or 'AR'.

sometimes your current session language depends on the tool that you are using or some default setup.

Generally it returns 'US'.
=====================================

select userenv('LANG') from dual;

output : US

=====================================

If it's not then you can your current session language by using alter session command.

eg. For AR

alter session set NLS_LANGUAGE='ARABIC';

eg. For GB

alter session set NLS_LANGUAGE='ENGLISH';

eg. For US

alter session set NLS_LANGUAGE='AMERICAN';

=================================================================

Set application message and token and retrieve it / Oracle application messages / FND Messages

Create a new application message as shown below.

You can set the message ,it's tokens, retrieve the value and then you can use it user hooks,form personalizations and other scripts.

DECLARE

   lv_msgdata   VARCHAR2 (32000);

BEGIN

   apps.fnd_message.set_name ('PER', 'XX_MSG_1');

   apps.fnd_message.set_token ('MSG_NAME', 'XXXX', TRANSLATE => TRUE);

   apps.fnd_message.set_token ('MSG_START_DATE',

                               '01-Jan-2014',

                               TRANSLATE      => TRUE

                              );

   lv_msgdata := apps.fnd_message.get ();

END;

How to Hide Reassign and Request for information button in workflow notification in oracle apps

HIDE REASSIGN BUTTON

Create an attribute with below details and drag and drop under the message for which you want to hide reassign button.

HIDE REQUEST FOR INFORMATION BUTTON

Create an attribute with below details and drag and drop under the message for which you want to hide reassign button.

Running the pre-processor after registering user-hook in oracle apps / Alternative of pre-processor

To run the pre-processor run one of the following commands:

cd $PER_TOP/admin/sql
Log into SQLPLUS as the APPS user
SQL> @hrahkall.sql

or

SQL> @hrahkone.sql


The first script will create all hook package bodies, whilst the second will
create hook package bodies for one API module only, and prompt for that
api_module_id.

==========================================================

Sometimes if you don't have server credentials then you can use below alternative to run the pre-processor through sqlplus

Log in to database through apps

SQL> @hrahkall.sql

instead of running above script you can execute below statement

exec hr_api_user_hooks_utility.create_hooks_all_modules;

SQL> @hrahkone.sql

instead of running above script you can execute below statement

exec hr_api_user_hooks_utility.create_hooks_one_module(p_module_id);

How to send attachment in oracle workflow notification / blob documents as attachment in oracle workflow notification

Create normal workflow process with simple notification as below.

Create a new document type attribute with below details.

drag and drop this attribute under "Attachment_notification"(notification message). Set the Attributes property as shown below.

Your workflow is complete. Now write the following PL/SQL code and run the workflow to get the notification. 

Sequence for item key :::

create sequence xx_example_s

start with 1

increment by 1;

CREATE OR REPLACE PACKAGE xx_example

IS

   PROCEDURE xx_training_wf;

   PROCEDURE xx_attach_p (

      document_id     IN       VARCHAR2,

      display_type    IN       VARCHAR2,

      document        IN OUT   BLOB,

      document_type   IN OUT   VARCHAR2

   );

END;

CREATE OR REPLACE PACKAGE BODY xx_example

IS

   gv_document_id   VARCHAR2 (100) := '2955797';

   PROCEDURE xx_training_wf

   IS

      l_itemtype   VARCHAR2 (30)  := 'XXTEST';

      l_itemkey    VARCHAR2 (300);

   ---l_file_name   VARCHAR2 (100) := 'holidaylist.xls';

   --l_unique_id   VARCHAR2 (50)  := unique_id;

   BEGIN

      SELECT TO_CHAR (xx_example_s.NEXTVAL)

        INTO l_itemkey

        FROM DUAL;

      wf_engine.createprocess (l_itemtype, l_itemkey, 'XX_TEST_ATTACHMENT');

      wf_engine.setitemattrdocument

                         (itemtype        => l_itemtype,

                          itemkey         => l_itemkey,

                          aname           => 'XX_ATT1',

                          documentid      =>    'PLSQLBLOB:XX_EXAMPLE.XX_ATTACH_P/'

                                             || gv_document_id

                         );

      wf_engine.setitemattrtext (itemtype      => l_itemtype,

                                 itemkey       => l_itemkey,

                                 aname         => '#FROM_ROLE',

                                 avalue        => 'WF_EMP1'

                                );

      wf_engine.setitemattrtext (itemtype      => l_itemtype,

                                 itemkey       => l_itemkey,

                                 aname         => 'XXTIMEOUT',

                                 avalue        => 2880

                                );

      wf_engine.startprocess (l_itemtype, l_itemkey);

      COMMIT;

   END xx_training_wf;

   PROCEDURE xx_attach_p (

      document_id     IN       VARCHAR2,

      display_type    IN       VARCHAR2,

      document        IN OUT   BLOB,

      document_type   IN OUT   VARCHAR2

   )

   IS

      lv_file_name           VARCHAR2 (100) := NULL;

      lv_file_content_type   VARCHAR2 (100) := NULL;

      ld_document            BLOB;

   BEGIN

      SELECT file_name, file_content_type, file_data

        INTO lv_file_name, lv_file_content_type, ld_document

        FROM fnd_lobs

       WHERE file_id = 2955797;

      document_type := lv_file_content_type || ';name=' || lv_file_name;

      DBMS_LOB.COPY (document, ld_document, DBMS_LOB.getlength (ld_document));

      COMMIT;

   EXCEPTION

      WHEN OTHERS

      THEN

         wf_core.CONTEXT ('xx_example',

                          'xx_attach_p',

                          'document_id',

                          'display_type'

                         );

         RAISE;

   END xx_attach_p;

END xx_example;

====================================================

Now execute procedure  to initiate the workflow.

execute xx_example.xx_training_wf ;

====================================================

Attachment will look like this

TImeout functionality in workflow notification / Due date of notification in workflow

- Log in to workflow builder , open the workflow process

select the notification , right click and select properties then click on node tab.

it will be as below.

In the timeout section you can select timeout properties.

There are 3 options.


1- No timeout : It means no due date of the notification.


2 -  Relative Time : It is nothing but the constant time details .
you can set the value in days, hours and minutes combination.

3 - Item Attribute : You can create number type attribute and assign it here.

you can set the attribute value as below.


wf_engine.setitemattrtext (itemtype      => l_itemtype,
                                 itemkey       => l_itemkey,
                                 aname         => 'XXTIMEOUT',
                                 avalue        => 2880
                                );

2880 - it is  minutes.

if you want to set due date to 3 days then value should be =(24*60*3)= 4320.

How to send notification through pl/sql without creating a new workflow

You can use any existing workflow and send notification by using wf_notification.send

Just you need to set the attributes of workflow message that you want to send.


Below is the example:


Here we will use seeded CS_MSGS (Service Messages)  item type nothing but the message type.

You can any of the predefined seeded item type or any custom workflow item type.

you just need the internal name of item type that you can get as shown below.

log in to workflow builder , load the item type , right click on item type and get the internal name.

or you can use the below query to get item type and it's internal name.


select * from WF_ITEM_TYPES_vl
where name ='CS_MSGS';

Now there are 4 messages in this item type and you want to use Expanded FYI Message

right click and select properties to get internal name. EXPANDED_FYI_MSG.

Now you just need to set the attributes of the message.
But you don't know the message attributes, so right click on message and select body tab.

Now you have everything so it's time to write the code .




DECLARE

   v_notification_id        NUMBER                 := NULL;
   v_from_user_name    VARCHAR2 (500)    := 'XX_FROM_USER';
   l_to_user_name         VARCHAR2 (500)    := 'XX_TO_USER';
   v_subject_line           VARCHAR2 (500)    := 'Subject Line';
   v_message_line         VARCHAR2 (500)    := 'Message Line';

BEGIN

   v_notification_id :=
      wf_notification.send (UPPER (l_to_user_name),
                            'CS_MSGS',
                            'EXPANDED_FYI_MSG'
                           );

   wf_notification.setattrtext (v_notification_id,
                                '#FROM_ROLE',
                                v_from_user_name
                               );

   wf_notification.setattrtext (v_notification_id,
                                'OBJECT_TYPE',
                                v_subject_line
                               );

   wf_notification.setattrtext (v_notification_id, 'SENDER', v_from_user_name);

   wf_notification.setattrtext (v_notification_id,
                                'MESSAGE_TEXT',
                                v_message_line
                               );

   wf_notification.denormalize_notification (v_notification_id);

   COMMIT;

END;

===================================================================

wf_notification.denormalize_notification and COMMIT is mandatory at last.


==================================================================

This way you can send normal text as well as HTML notifications.

Just you need to identify proper item type and message name.

How to Purge old completed workflows

How to Purge old completed workflows 

After years few years of go-live ,

workflow transaction tables will become heavy and will lot of data.
It may cause performance issue for new workflow transactions.
So you can purge the old existing workflow transaction through below procedure.

You can loop through below procedure for all old workflow transactions.

Just you need to pass the item type and item key as parameters.


execute wf_purge.Total(itemtype =>'Test',itemkey => 23);

How to approve/reject or respond a notification through script

How to approve/reject or respond a notification through script

BEGIN
   wf_notification.setattrtext (nid         => '4754620',
                                aname       => 'RESULT',
                                avalue      => 'REJECTED'
                               );
   wf_notification.respond (nid         => '4754620',
                                     respond_comment =>  'Reject From backend',
                                     responder =>  'XXTEST');
   COMMIT;
END;


nid =  will be notification id

There are multiple ways to get notification id

-  Open the notification in the application through SYSADMIN or any other user
    in the notification below sent and to date, you can see the ID

or

-   you can search the notification in wf_notification table

aname = will be always RESULT

avalue = will be APPROVE,REJECTED or any other action .

Generally for avalue , perform the appropriate action on the notification through front end
and see the value of RESULT attribute in wf_notification_attributes table.

responder coment = any comment

responder => will be recipient role/user name