Useful Workflow scripts

WF_WORKLIST_V

WF_WORKLIST_V is a view of WF_NOTIFICATIONS that is used to display messages in a work list.

View Definition:

CREATE VIEW WF_WORKLIST_V

AS

   SELECT WN.ROWID,

          WN.NOTIFICATION_ID,

          WN.PRIORITY,

          WIT.DISPLAY_NAME,

          WN.RECIPIENT_ROLE,

          Wf_Notification.GetSubject (notification_id),

          WN.BEGIN_DATE,

          WN.DUE_DATE,

          WN.END_DATE,

          WL.MEANING,

          WN.STATUS,

          WN.ORIGINAL_RECIPIENT,

          WN.MESSAGE_TYPE,

          WN.MESSAGE_NAME

     FROM WF_NOTIFICATIONS WN, WF_ITEM_TYPES_VL WIT, WF_LOOKUPS WL

    WHERE     WN.MESSAGE_TYPE = WIT.NAME

          AND WL.LOOKUP_TYPE = ’WF_NOTIFICATION_STATUS’

          AND WN.STATUS = WL.LOOKUP_CODE

WF_USER_ROLES

WF_USER_ROLES is a view of a customer’s HR tables. Each row includes the users matched with their role, as well as information about the system this information was drawn from.

View Definition

CREATE VIEW WF_USER_ROLES

AS

   SELECT USR.USER_NAME,

          ’PER’,

          PER.PERSON_ID,

          ’POS’ || ' :' || POS.POSITION_ID,

          'POS',

          POS.POSITION_ID

     FROM PER_ASSIGNMENTS_F ASS,

          PER_POSITIONS POS,

          FND_USER USR,

          PER_PEOPLE_F PER

    WHERE     ASS.POSITION_ID = POS.POSITION_ID

          AND ASS.PERSON_ID = USR.EMPLOYEE_ID

          AND SYSDATE > PER.EFFECTIVE_START_DATE

          AND SYSDATE < NVL (PER.EFFECTIVE_END_DATE, SYSDATE + 1)

          AND SYSDATE > ASS.EFFECTIVE_START_DATE

          AND SYSDATE < NVL (ASS.EFFECTIVE_END_DATE, SYSDATE + 1)

          AND PER.EMPLOYEE_NUMBER IS NOT NULL

          AND ASS.ASSIGNMENT_TYPE = 'E'                           /*Engineering approval roles */

   UNION ALL

   SELECT USR.USER_NAME,

          'PER',

          PER.PERSON_ID,

          'ENG_LIST' || ':' || EEAL.APPROVAL_LIST_ID,

          'ENG_LIST',

          EEAL.APPROVAL_LIST_ID

     FROM FND_USER USR,

          PER_PEOPLE_F PER,

          ENG_ECN_APPROVAL_LISTS EEAL,

          ENG_ECN_APPROVERS EEA

    WHERE     EEA.EMPLOYEE_ID = PER.PERSON_ID

          AND PER.PERSON_ID = USR.EMPLOYEE_ID

          AND EEA.APPROVAL_LIST_ID = EEAL.APPROVAL_LIST_ID

          AND SYSDATE > PER.EFFECTIVE_START_DATE

          AND SYSDATE < NVL (PER.EFFECTIVE_END_DATE, SYSDATE + 1)

          AND SYSDATE < NVL (EEA.DISABLE_DATE, SYSDATE + 1)

   UNION ALL

   /* User responsibility where user Not linked to an employee */

   SELECT DISTINCT

          U.USER_NAME,

          'FND_USR',

          U.USER_ID,

             'FND_RESP'

          || UR.RESPONSIBILITY_APPLICATION_ID

          || ':'

          || R.RESPONSIBILITY_ID,

          'FND_RESP' || UR.RESPONSIBILITY_APPLICATION_ID,

          R.RESPONSIBILITY_ID

     FROM FND_USER U, FND_USER_RESP_GROUPS UR, FND_RESPONSIBILITY R

    WHERE     U.USER_ID = UR.USER_ID

          AND UR.RESPONSIBILITY_ID = R.RESPONSIBILITY_ID

          AND UR.RESPONSIBILITY_APPLICATION_ID = R.APPLICATION_ID

          AND SYSDATE > UR.START_DATE

          AND SYSDATE < NVL (UR.END_DATE, SYSDATE + 1)

          AND U.EMPLOYEE_ID IS NULL

   UNION ALL

   SELECT DISTINCT

          U.USER_NAME,

          'PER',

          U.EMPLOYEE_ID,

             'FND_RESP'

          || UR.RESPONSIBILITY_APPLICATION_ID

          || ':'

          || R.RESPONSIBILITY_ID,

          'FND_RESP' || UR.RESPONSIBILITY_APPLICATION_ID,

          R.RESPONSIBILITY_ID

     FROM FND_USER U, FND_USER_RESP_GROUPS UR, FND_RESPONSIBILITY R

    WHERE     U.USER_ID = UR.USER_ID

          AND UR.RESPONSIBILITY_ID = R.RESPONSIBILITY_ID

          AND UR.RESPONSIBILITY_APPLICATION_ID = R.APPLICATION_ID

          AND SYSDATE > UR.START_DATE

          AND SYSDATE < NVL (UR.END_DATE, SYSDATE + 1)

          AND U.EMPLOYEE_ID IS NOT NULL

   UNION ALL

   SELECT GBU.USER_NAME,

          'GBU',

          GBU.GROUPBOX_USER_ID,

          GBX.NAME,

          'GBX',

          GBX.GROUPBOX_ID

     FROM GHR_GROUPBOXES GBX, GHR_GROUPBOX_USERS GBU

    WHERE GBU.GROUPBOX_ID = GBX.GROUPBOX_ID

   UNION ALL

   SELECT user_name,

          user_orig_system,

          user_orig_system_id,

          role_name,

          role_orig_system,

          role_orig_system_id

     FROM wf_local_user_roles /* everyuser has a role_name which is the same name as their user_name

     */

   UNION ALL

   SELECT NAME,

          ORIG_SYSTEM,

          ORIG_SYSTEM_ID,

          NAME,

          ORIG_SYSTEM,

          ORIG_SYSTEM_ID

     FROM WF_USERS

WF_USERS

WF_USERS is a view of a customer’s HR tables. Each row includes the user’s name and description, as well as the notification preference, language, and the information about the system this information was drawn from.

View Definition

CREATE VIEW WF_USERS

AS

   SELECT USR.USER_NAME,

          PER.FULL_NAME,

          PER.FULL_NAME,

          NVL (wf_pref.get_pref (USR.USER_NAME, 'MAILTYPE'), 'MAILHTML'),

          NVL (wf_pref.get_pref (USR.USER_NAME, 'LANGUAGE'),

               FNDL.NLS_LANGUAGE),

          NVL (wf_pref.get_pref (USR.USER_NAME, 'TERRITORY'),

               FNDL.NLS_TERRITORY),

          PER.EMAIL_ADDRESS,

          USR.FAX,

          'PER',

          PER.PERSON_ID,

          DECODE (usr.end_date, NULL, 'ACTIVE', 'INACTIVE'),

          TO_DATE (NULL)

     FROM FND_LANGUAGES FNDL, PER_PEOPLE_F PER, FND_USER USR

    WHERE     TRUNC (SYSDATE) BETWEEN PER.EFFECTIVE_START_DATE

                                  AND PER.EFFECTIVE_END_DATE

          AND PER.PERSON_ID = USR.EMPLOYEE_ID

          AND FNDL.INSTALLED_FLAG = 'B'

   /* fnd_users Not linked to per_people_f */

   UNION ALL

   SELECT USR.USER_NAME,

          USR.USER_NAME,

          USR.DESCRIPTION,

          NVL (wf_pref.get_pref (USR.USER_NAME, 'MAILTYPE'), 'MAILHTML'),

          NVL (wf_pref.get_pref (USR.USER_NAME, 'LANGUAGE'),

               FNDL.NLS_LANGUAGE),

          NVL (wf_pref.get_pref (USR.USER_NAME, 'TERRITORY'),

               FNDL.NLS_TERRITORY),

          USR.EMAIL_ADDRESS,

          USR.FAX,

          'FND_USR',

          USR.USER_ID,

          DECODE (USR.END_DATE, NULL, 'ACTIVE', 'INACTIVE'),

          TO_DATE (NULL)

     FROM FND_LANGUAGES FNDL, FND_USER USR

    WHERE USR.EMPLOYEE_ID IS NULL AND FNDL.INSTALLED_FLAG = 'B'

   /*Customer Address contacts*/

   UNION ALL

   SELECT 'CUST_CONT' || ' :' || cont.contact_id,

          cont.last_name,

          cont.last_name || ', ' || cont.first_name,

          'MAILTEXT',

          fndl.nls_language,

          fndt.nls_territory,

          cont.email_address,

          NULL,

          'CUST_CONT',

          cont.contact_id,

          DECODE (cont.status, 'I', 'INACTIVE', 'ACTIVE'),

          TO_DATE (NULL)

     FROM FND_LANGUAGES FNDL,

          FND_TERRITORIES FNDT,

          RA_ADDRESSES CUST_ADDR,

          RA_CUSTOMERS CUST,

          RA_CONTACTS CONT

    WHERE     cont.address_id = cust_addr.address_id

          AND cont.customer_id = cust.customer_id

          AND cust_addr.country = fndt.territory_code(+)

          AND fndt.nls_territory = fndl.nls_territory(+)

   UNION ALL

   SELECT name,

          display_name,

          description,

          NVL (wf_pref.get_pref (name, 'MAILTYPE'), notification_preference),

          NVL (wf_pref.get_pref (name, 'LANGUAGE'), language),

          NVL (wf_pref.get_pref (name, 'TERRITORY'), territory),

          email_address,

          fax,

          'WF_LOCAL_USERS',

          0,

          status,

          expiration_date

     FROM wf_local_users

WF_RUNNABLE_PROCESSES_V

This view shows all root processes that are runnable. It includes the type, process_name and display_name of the process.

View Definition

CREATE VIEW WF_RUNNABLE_PROCESSES_V

AS

   SELECT WFA_ACT.ITEM_TYPE ITEM_TYPE,

          WFA_ACT.NAME PROCESS_NAME,

          WFA_ACT.DISPLAY_NAME DISPLAY_NAME

     FROM wf_activities_vl wfa_act

    WHERE     wfa_act.runnable_flag = ’Y’

          AND wfa_act.TYPE = ’PROCESS’

          AND SYSDATE BETWEEN wfa_act.begin_date

                          AND NVL (wfa_act.end_date, SYSDATE)