How to find Profile Options Attached in various levels

How to find Profile Options Attached in various levels

select 

b.user_profile_option_name "Long Name"

, a.profile_option_name "Short Name"

, decode(to_char(c.level_id),'10001','Site'

,'10002','Application'

,'10003','Responsibility'

,'10004','User'

,'Unknown') "Level"

, decode(to_char(c.level_id),'10001','Site'

,'10002',nvl(h.application_short_name,to_char(c.level_value))

,'10003',nvl(g.responsibility_name,to_char(c.level_value))

,'10004',nvl(e.user_name,to_char(c.level_value))

,'Unknown') "Level Value"

, c.PROFILE_OPTION_VALUE "Profile Value"

, c.profile_option_id "Profile ID"

, to_char(c.LAST_UPDATE_DATE,'DD-MON-YYYY HH24:MI') "Updated Date"

, nvl(d.user_name,to_char(c.last_updated_by)) "Updated By"

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

--a.application_id = nvl(401, a.application_id)

--and a.profile_option_name = nvl('INV', a.profile_option_name)

and b.user_profile_option_name like 'HR%Sec%' -- 'AFLOG_ENABLED'

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.PROFILE_OPTION_VALUE=14063

order by 

b.user_profile_option_name, c.level_id, 

decode(to_char(c.level_id),'10001','Site'

,'10002',nvl(h.application_short_name,to_char(c.level_value))

,'10003',nvl(g.responsibility_name,to_char(c.level_value))

,'10004',nvl(e.user_name,to_char(c.level_value))

,'Unknown');

Absence Action History Details Script

Absence Approval Action History Script:


SELECT hats.information1, hats.information2, hats.information6,
       hats.information8, psa.user_name, psa.action
  FROM hr_api_transactions hat,
       hr_api_transaction_steps hats,
       apps.wf_items wi,
       pqh_ss_approval_history psa
 WHERE hat.selected_person_id = 501
   AND hat.transaction_ref_table = 'PER_ABSENCE_ATTENDANCES'
   AND hat.transaction_id = hats.transaction_id
   AND wi.item_key = hat.item_key
   AND wi.item_key = psa.transaction_item_key
   AND wi.item_type = 'HRSSA'
   AND end_date IS NOT NULL



Absence Pending History Sctipt:


SELECT wfn.notification_id, wi.item_key,
       TO_DATE (hats.information1, 'YYYY-MM-DD'), hats.information2,
       hats.information6, hats.information8, wfn.recipient_role, wfn.status
  FROM hr_api_transactions hat,
       hr_api_transaction_steps hats,
       apps.wf_items wi,
       apps.wf_notifications wfn
 WHERE hat.selected_person_id = 501
   AND hat.transaction_ref_table = 'PER_ABSENCE_ATTENDANCES'
   AND hat.transaction_id = hats.transaction_id
   AND wi.item_key = hat.item_key
--and wi.item_key=psa.TRANSACTION_ITEM_KEY
   AND wi.item_type = 'HRSSA'
   AND wi.end_date IS NULL
   AND wi.item_key = wfn.item_key
   AND wfn.notification_id IN (SELECT MAX (notification_id)
                                 FROM apps.wf_notifications
                                WHERE item_key = wi.item_key)
   AND TO_DATE (hats.information1, 'YYYY-MM-DD') >= '01-JAN-2017'

Element Input Validation Fast Formula Type

Element Input Validation Fast Formula Type

Element Input Validation

This can be used when we got to validate / restrict the element input value (Date/Number). There various types through which we can enable restriction. That are,

        a) Fast Formula (Date/Money),
        b) Lookup (Character),
        c) Value Set,
        d) Default Value,
        e) Minimum & Maximum

In this article we are going to see how to restrict/validate entry values in element input value.

Scenario 1

Restrict the input value for a element called "One time Bonus" not higher than Rs: 15000 /-
Steps:

1) Write fast formula to validate / restrict desired entry value (Date/ Money) in element input value,
(Navigation: HRMS: -> Total Compensation  ->  Basic  ->  Write Formula)

1.1) Type Formula Name,
1.2) Select Type as "Element Input Validation",
1.3) Enter description for the fast formula.

2) Enter the following formula

Inputs are entry_value (text)
If to_num (entry_value) > 15000
Then
(Formula_Status = 'e'
Formula_Message = 'Value Entered is above the limit')
Else
(Formula_Status = 's'
Formula_Message = 'Fine')
Return Formula_Status, Formula_Message

3) Define an element,
(Navigation: HRMS  -> Total Compensation -> Basic Element Description)

4) Enter input values for the element

4.1) Select "Money" in units,
4.2) Select the formula from the  LOV in the formula field.

5) Testing: try entering values within & above 15000 in element entries
(Navigation: People -> Enter & Maintain -> "Assignment"  ->  Entries  –  Entry Values)


Scenario 2

Restrict Entry date not prior to system date (Can be Today & Tomorrow Onwards)

1) Wirte fast formula to validate element input date,
(Navigation: HRMS:-> Total Compensation -> Basic -> Write Formula)

         1.1) Type Formula Name,
         1.2) Select Type as "Element Input Validation",
         1.3) Enter description for the fast formula.

2) Enter the following formula;

       Default for session_date IS '1900/01/01 00:00:00' (date)
        Inputs are entry_value(text)
        If(to_date(entry_value,'YYYY/MM/DD HH24:MI:SS') < session_date) then
       (formula_status ='e'
        formula_message ='Enter future date')
       else
      (formula_status='s')
      Return formula_status,formula_message

3) Define an element,
      (Navigation: HRMS -> Total Compensation -> Basic Element Description)

4) Enter input values for the element
        4.1) Select "Money" in units,
        4.2) Select the formula from the LOV in the formula field.

5) Testing: try entering date less than, equal to & greater than the session date.
(Navigation: People -> Enter & Maintain -> "Assignment" -> Entries – Entry Values)


Some Fast Formula Tables:   SQL Query to select tables:-

Select * from all_objects where object_type=’table’ and object_name like ‘ff%';
ff_functions
ff_function_parameters
ff_formulas_f
ff_formula_types
ff_database_items
ff_globals_ff