How to find Workflow User, Item Name, Function Message and Lookups

--Workflow Users/Roles
SELECT * FROM WF_USERS where name = 'XXUSER';
SELECT * FROM WF_ROLES where name = 'XXUSER';
SELECT * FROM WF_USER_ROLES where user_name = 'XXUSER';
SELECT * FROM WF_USER_ROLE_ASSIGNMENTS where user_name = 'XXUSER';

--Workflow Item Name and Attributes
SELECT * FROM WF_ITEM_TYPES where name = 'OKCAUKAP'; --PO Approval (POAPPRV) ; PO Requisition Approval (REQAPPRV); 
--OM Order Header (OEOH); OM Order Line (OEOL)
SELECT * FROM WF_ITEM_TYPES_VL where name = 'OKCAUKAP';
SELECT * FROM WF_ITEM_TYPES_VL where display_name like '%Approval';
SELECT * FROM WF_ITEM_TYPES_VL where description like '%Order%';
SELECT * FROM WF_ITEM_ATTRIBUTES where item_type = 'OKCAUKAP';
SELECT * FROM WF_ITEM_ATTRIBUTES_TL;

--Workflow Functions
SELECT * FROM WF_ACTIVITIES where item_type = 'OKCAUKAP' and version = 1;
SELECT * FROM WF_ACTIVITIES_TL where item_type = 'OKCAUKAP';
SELECT * FROM WF_ACTIVITY_ATTRIBUTES where activity_name = 'CREATE_SOA';
SELECT * FROM WF_ACTIVITY_ATTRIBUTES_TL;
SELECT * FROM WF_ACTIVITY_TRANSITIONS;

--Workflow Lookups
SELECT * FROM WF_LOOKUPS_TL where lookup_type = 'OKC_DB_RETRY';

--Workflow Messages
SELECT * FROM WF_MESSAGES where type = 'OKCAUKAP' and name = 'APPROVE_CONTRACT';
SELECT * FROM WF_MESSAGES_TL where type = 'OKCAUKAP' and name = 'APPROVE_CONTRACT';
SELECT * FROM WF_MESSAGE_ATTRIBUTES where message_type = 'OKCAUKAP' and message_name = 'APPROVE_CONTRACT';
SELECT * FROM WF_MESSAGE_ATTRIBUTES_TL where message_type = 'OKCAUKAP' and message_name = 'APPROVE_CONTRACT';


--Workflow Particular item run and values
SELECT * FROM WF_ITEMS where item_type = 'OKCAUKAP' order by begin_date desc;
--USER_KEY/ITEM_KEY => select * from okc_k_headers_b where contract_number = '3308531';
SELECT * FROM WF_ITEM_ATTRIBUTE_VALUES where item_type = 'OKCAUKAP' and item_key='20683312';
SELECT * FROM WF_PROCESS_ACTIVITIES where process_item_type = 'OKCAUKAP' and process_version = 1;

SELECT * FROM WF_NOTIFICATIONS WHERE MESSAGE_TYPE = 'OKCAUKAP' AND MESSAGE_NAME = 'APPROVE_CONTRACT';
SELECT * FROM WF_NOTIFICATION_ATTRIBUTES where notification_id = ;

SELECT * FROM WF_DEFERRED;

How To Find Descriptive Flexfield (DFF) Type

 DESCRIPTIVE FLEX FIELD

Example:
Application : Purchasing
Title : PO Headers
Context Field value : one or more type

SELECT  ATTRIBUTE_CATEGORY
FROM     PO_HEADERS_ALL
WHERE  SEGMENT1=1386

How to Find Document Entities

SELECT fde.document_entity_id, fde.data_object_code,
          fde.table_name, fde.application_id, fatl.application_name,
          tl.user_entity_name, tl.user_entity_prompt, fde.creation_date,
          fde.created_by, fde.last_update_date, fde.last_updated_by,
          fde.last_update_login, fde.pk1_column, fde.pk2_column,
          fde.pk3_column, fde.pk4_column, fde.pk5_column
     FROM fnd_application_tl fatl,
          fnd_document_entities_tl tl,
          fnd_document_entities fde
    WHERE fde.data_object_code = tl.data_object_code
      AND tl.LANGUAGE = USERENV ('LANG')
      AND fde.application_id = fatl.application_id
      AND fatl.LANGUAGE = USERENV ('LANG');

How to convert dob to age in oracle

Select
Trunc(months_between(sysdate,dob)/12) YEAR,
Trunc(Mod(months_between(sysdate,dob),12)) MONTH,
Trunc(sysdate - add_months(dob,Trunc(months_between(sysdate,dob)/12)*12
         +Trunc(mod(months_between(sysdate,dob),12)))) DAYS
From (Select to_date(:P_Date,'DD-MON-YYYY') dob from Dual);