Model Profile Query in Fusion HCM

 select hapf.position_code

,hpb.profile_code

,hpt.description

,hpt.summary

,hpeiv.DESCRIPTION

,hpeiv.RESPONSIBILITIES

,hpeiv.QUALIFICATIONS

from HRT_PROFILE_ITEMS hpi

,HRT_PROFILES_B hpb

,HRT_PROFILES_TL hpt

,HRT_PROFILE_RELATIONS hpr

,HRT_PROFILE_EXTRA_INFO_VL hpeiv

,HR_ALL_POSITIONS_F hapf

where hpi.profile_id = hpb.profile_id

and hpb.profile_usage_code = 'M'

and hpi.profile_id = hpr.profile_id

and hpi.profile_id = hpt.profile_id

and hpi.profile_id = hpeiv.profile_id

and hapf.position_id = hpr.object_id

and trunc(sysdate) between hapf.effective_start_date and hapf.effective_end_Date

and hapf.position_code= '1099'

and hpt.language = 'US'

Session Variables in Fusion HCM for BI

Session Variables in BI Publisher to get logged in user info

Session variables are used frequently in BI Publisher when the reports are being exposed to employees.

We need to write query to pull only the logged in user information and for this we will use the session variable.

Apart from this user, there are many other session variables and we are going to look at them now.

Logged in User Person ID - HRC_SESSION_UTIL.get_user_personid​

Logged in User Username - :xdo_user_name

Logged in User Username - FND_GLOBAL.USER_NAME

Logged in User GUID - fnd_global.user_guid

Logged in User SessionID - userenv('sessionid')

Logged in User Language - USERENV ('LANG')

Logged in User Business Group - fnd_profile.value('PER_BUSINESS_GROUP_ID')

Sample SQLs:

select * from per_persons​​ ​where person_id = HRC_SESSION_UTIL.get_user_personid​
select * from per_organization_units WHERE business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')

You can add these to the SQL query as per your requirement and get appropriate results.

Hope this is useful.. stay tuned for more updates.

Report for pending transactions and who they are pending with in Fusion

SELECT dp.person_number "Person Number",
  n.display_name "Worker Name",
  txnh.module_identifier "UI Process",
  wft.creator "Requestor",
  wft.assigneesdisplayname
  ||','
  ||wft.assignees "Current Assignee,User,Type",
  wft.assigneddate "Assigned Date",
  wft.approvers "Approved By",
  wft.title "Notification Title",
  wft.tasknumber "Task Number",
  txnd.status "HCM Transaction Status",
  wft.state "BPM Task State",
  txnh.object
FROM fusion.per_all_people_f dp,
  fusion.per_person_names_f_v n,
  fusion.per_all_assignments_m asg,
  fusion.hrc_txn_header txnh,
  fusion.hrc_txn_data txnd,
  hcm_fusion_soainfra.WFTASK wft
WHERE dp.person_id             =n.person_id
AND asg.person_id              =n.person_id
AND LENGTH(asg.assignment_type)=1
AND asg.assignment_id          =txnh.object_id
AND wft.identificationkey      =TO_CHAR(txnh.transaction_id)
AND txnh.object                ='PER_ALL_ASSIGNMENTS_M'
AND txnh.transaction_id        =txnd.transaction_id
AND sysdate BETWEEN asg.effective_start_date AND asg.effective_end_date
AND asg.effective_latest_change='Y'
AND sysdate BETWEEN dp.effective_start_date AND dp.effective_end_date
AND sysdate BETWEEN n.effective_start_date AND n.effective_end_date
AND txnd.status IN ('PENDING','ERROR')
UNION
SELECT dp.person_number "Person Number",
  n.display_name "Worker Name",
  txnh.module_identifier "UI Process",
  wft.creator "Requestor",
  wft.assigneesdisplayname
  ||','
  ||wft.assignees "Current Assignee,User,Type",
  wft.assigneddate "Assigned Date",
  wft.approvers "Approved By",
  wft.title "Notification Title",
  wft.tasknumber "Task Number",
  txnd.status "HCM Transaction Status",
  wft.state "BPM Task State",
  txnh.object
FROM fusion.per_all_people_f dp,
  fusion.per_person_names_f_v n,
  fusion.per_all_assignments_m asg,
  fusion.hrc_txn_header txnh,
  fusion.hrc_txn_data txnd,
  hcm_fusion_soainfra.WFTASK wft
WHERE dp.person_id             =n.person_id
AND asg.person_id              =n.person_id
AND LENGTH(asg.assignment_type)=1
AND asg.period_of_service_id   =txnh.object_id
AND wft.identificationkey      =TO_CHAR(txnh.transaction_id)
AND txnh.object                ='PER_PERIODS_OF_SERVICE'
AND txnh.transaction_id        =txnd.transaction_id
AND sysdate BETWEEN asg.effective_start_date AND asg.effective_end_date
AND asg.effective_latest_change='Y'
AND sysdate BETWEEN dp.effective_start_date AND dp.effective_end_date
AND sysdate BETWEEN n.effective_start_date AND n.effective_end_date
AND txnd.status IN ('PENDING','ERROR')
UNION
SELECT 'Create Worker' "Person Number",
  'Create Worker' "Worker Name",
  txnh.module_identifier "UI Process",
  wft.creator "Requestor",
  wft.assigneesdisplayname
  ||','
  ||wft.assignees "Current Assignee,User,Type",
  wft.assigneddate "Assigned Date",
  wft.approvers "Approved By",
  wft.title "Notification Title",
  wft.tasknumber "Task Number",
  txnd.status "HCM Transaction Status",
  wft.state "BPM Task State",
  txnh.object
FROM fusion.hrc_txn_header txnh,
  fusion.hrc_txn_data txnd,
  hcm_fusion_soainfra.WFTASK wft
WHERE wft.identificationkey =TO_CHAR(txnh.transaction_id)
AND txnh.object             ='PER_ALL_PEOPLE_F'
AND txnh.transaction_id     =txnd.transaction_id
AND txnd.status            IN ('PENDING','ERROR');

Accrual Plan Entry Details

Select * from ANC_PER_ACRL_ENTRY_DTLS

Where person_id = (select distinct person_id from per_all_people_f where person_number =’’)

Assigned User Roles Query in Fusion

SELECT pu.person_id,

  hz.party_id,

  'Person'           AS Type,

  ppn.display_name,

  pu.username,

  DECODE(pu.suspended, 'Y', 'Yes', 'No') AS Suspended,

  prdv.role_common_name,

  prdv.role_name,

  prdv.description,

  prdv.abstract_role,

  prdv.job_role,

  prdv.data_role,
  pur.last_updated_by,
  pur.last_update_date

FROM fusion.per_users pu,

  fusion.hz_parties hz,

  fusion.per_user_roles pur,

  fusion.per_roles_dn_vl prdv,

  fusion.per_person_names_f ppn

WHERE pu.user_id      = pur.user_id(+)

AND pu.user_guid      = hz.user_guid

AND prdv.role_guid(+) = pur.role_guid

AND pu.person_id     IS NOT NULL

AND ppn.name_type     = 'GLOBAL'

AND ppn.person_id     = pu.person_id

AND (sysdate BETWEEN ppn.effective_start_date AND ppn.effective_end_date)

and PU.ACTIVE_FLAG = 'Y'

and upper(pu.username) IN ('96040')

Departments not available in Organization Tree

SELECT to_char(haou.organization_id) organization_id, haou.NAME Department_name, haou.attribute2 Department_Category, hla.location_name,htt.territory_short_name country
  FROM hr_all_organization_units haou,
       hr_org_unit_classifications_f haouc,
   hr_locations_all hla,
   fnd_territories_tl htt
 WHERE haou.organization_id = haouc.organization_id 
   AND haouc.status <> 'I'
   AND haou.location_id = hla.location_id(+)
   and haouc.classification_code = 'DEPARTMENT'
   AND TRUNC (SYSDATE) BETWEEN haou.effective_start_date
                           AND haou.effective_end_date
   AND TRUNC (SYSDATE) BETWEEN haouc.effective_start_date
                           AND haouc.effective_end_date
   AND hla.country = htt.territory_code
   and htt.source_lang = 'US'
   and htt.territory_short_name = nvl(:p_country,htt.territory_short_name)
   AND haou.organization_id NOT IN (SELECT potn.pk1_start_value
                                      FROM per_org_tree_node potn
                                     WHERE 1=1
and potn.tree_code in( 'ORG_TREE_CODE')
)

Department List in Fusion

SELECT to_char(haou.organization_id) organization_id, haou.NAME Department_name, haou.attribute2 Department_Category, hla.location_name,htt.territory_short_name country
  FROM hr_all_organization_units haou,
       hr_org_unit_classifications_f haouc,
   hr_locations_all hla,
   fnd_territories_tl htt
 WHERE haou.organization_id = haouc.organization_id 
   AND haouc.status <> 'I'
   AND haou.location_id = hla.location_id(+)
   and haouc.classification_code = 'DEPARTMENT'
   AND TRUNC (SYSDATE) BETWEEN haou.effective_start_date
                           AND haou.effective_end_date
   AND TRUNC (SYSDATE) BETWEEN haouc.effective_start_date
                           AND haouc.effective_end_date
   AND hla.country = htt.territory_code
   and htt.source_lang = 'US'
   and htt.territory_short_name = nvl(:p_country,htt.territory_short_name)