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)

Regeneration of Roles

Post any cloud upgrade, it is recommended to regenerate all the roles to avoid security-related issues. I have enabled an option where one can regenerate all the roles in bulk.

Please navigate to My Workforce - ( on the left side panel, search for) - Manage Data Roles and Security Profiles.

On right side top corner, you will find Regenerate All Roles button. You can schedule a process to regenerate bulk roles with the following options:

Named Job Role	Regenerates any data roles that inherits the specified job role directly. Data roles that inherit the job role indirectly are not regenerated.
Named Data Role	Regenerates the specified data role only.
Named Abstract Role	Regenerates the specified abstract role only.
Process All Roles	Regenerates all roles to which security profiles are assigned. In this mode, secured access for all roles is recalculated and the secured access of all users is refreshed. The time taken to complete this process depends on the number of roles to be regenerated.

After each upgrade, you must choose the last option of Process All Roles. For Rel 13, we will do it for you but keep this email handy for your future upgrades.

Work Schedule Query

select * from per_schedule_assignments where schedule_id = 300000058913254 and resource_type <> 'ASSIGN' and end_date >= sysdate

--select * from zmm_sr_schedules_tl where schedule_id = 300000058913254

Fusion HCM Personalization Expression Builder based on Roles attached to User

#{securityContext.userInRole['XX_PER_EMPLOYEE_ABSTRACT_CUSTOM,ORA_ASM_APPLICATION_IMPLEMENTATION_CONSULTANT_JOB']}

Steps to create TCC Batch file

Steps to create TCC Batch file

 

1.       Open any configuration file in TCC

2.       Go to General tab à Enable Schedule Monitoring à Click on “Click Here to Create Schedule” à Build Command Line à Copy the path e.g. "D:\Taleo Connect Client\TaleoConnectClient.bat" "D:\Taleo_Interfaces\Dev\Outbound\TCC_Scripts\DefaultHiringManagerRecruiter_cfg.xml"

3.       Open Notepad

4.       Type call and path from step 2. It will be like call "D:\Taleo Connect Client\TaleoConnectClient.bat" "D:\Taleo_Interfaces\Dev\Outbound\TCC_Scripts\DefaultHiringManagerRecruiter_cfg.xml"

5.       Save the notepad file as “DefaultHiringManagerRecruiter.bat”

6.       Go to Task Scheduler of Server where TCC is installed.

7.       Click on Create Basic Task

8.       Define the schedule and under Program/script browse file created in step 5.

9.       If needed Task can be updated.

WF Comments in Fusion BIP Reports

SELECT txnh.module_identifier ProcessName,
  wft.creator Requestor,
  wft.assignees CurrentAssignee,
  wft.assigneddate AssignedDate,
  wft.title NotificationTitle,
  txnd.status TxnStatus,
  txnh.object ObjectName,  wfc.wfcomment
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,
  HCM_FUSION_SOAINFRA.wfcomments wfc

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 wfc.taskid = wft.taskid

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

Supervisor Query

SELECT    papf.person_number
         ,TO_CHAR (pps.date_start
                  ,'DD-Mon-YYYY'
                  ,'NLS_DATE_LANGUAGE=AMERICAN')
             person_hire_date
         ,TO_CHAR (pps.actual_termination_date
                  ,'DD-Mon-YYYY'
                  ,'NLS_DATE_LANGUAGE=AMERICAN')
             person_termination_date
         ,TO_CHAR (paaf.effective_start_date
                  ,'DD-Mon-YYYY'
                  ,'NLS_DATE_LANGUAGE=AMERICAN')
             assignment_start_date
         ,TO_CHAR (paaf.effective_end_date
                  ,'DD-Mon-YYYY'
                  ,'NLS_DATE_LANGUAGE=AMERICAN')
             assignment_end_date
         ,TO_CHAR (pasf.effective_start_date
                  ,'DD-Mon-YYYY'
                  ,'NLS_DATE_LANGUAGE=AMERICAN')
             manager_start_date
         ,TO_CHAR (pasf.effective_end_date
                  ,'DD-Mon-YYYY'
                  ,'NLS_DATE_LANGUAGE=AMERICAN')
             manager_end_date
         ,pasf.manager_type
         ,pasf.primary_flag
         ,paaf.assignment_number
         ,papfs.person_number manager_number
         ,TO_CHAR (ppss.date_start
                  ,'DD-Mon-YYYY'
                  ,'NLS_DATE_LANGUAGE=AMERICAN')
             manager_hire_date
         ,TO_CHAR (ppss.actual_termination_date
                  ,'DD-Mon-YYYY'
                  ,'NLS_DATE_LANGUAGE=AMERICAN')
             manager_termination_date
FROM      per_periods_of_service ppss
         ,per_all_people_f papfs
         ,per_assignment_supervisors_f pasf
         ,per_all_people_f papf
         ,per_all_assignments_m paaf
         ,per_periods_of_service pps
WHERE         ppss.person_id(+) = papfs.person_id
          AND pasf.effective_start_date BETWEEN papfs.effective_start_date(+) AND papfs.effective_end_date(+)
          AND papfs.person_id(+) = pasf.manager_id
          AND paaf.effective_start_date BETWEEN pasf.effective_start_date(+) AND pasf.effective_end_date(+)
          AND pasf.assignment_id(+) = paaf.assignment_id
          AND papf.person_number = NVL (:p_person_number, papf.person_number)
          AND pps.date_start BETWEEN papf.effective_start_date AND papf.effective_end_date
          AND papf.person_id = paaf.person_id
          AND paaf.effective_latest_change = 'Y'
          AND paaf.primary_assignment_flag = 'Y'
          AND paaf.primary_flag = 'Y'
          AND paaf.assignment_type = 'E'
          AND paaf.period_of_service_id = pps.period_of_service_id
ORDER BY  papf.person_number
         ,paaf.effective_start_date

Element Links

SELECT    pldgv.name legislative_data_group
         ,pect.classification_name
         ,pett.element_name
         ,pelf.element_link_name
         ,TO_CHAR (pelf.effective_start_date
                  ,'YYYY/MM/DD')
             effective_start_date
         ,TO_CHAR (pelf.effective_end_date
                  ,'YYYY/MM/DD')
             effective_end_date
         ,haou.name legal_entity
         ,ppg.segment1 people_group_segment1
         ,ppg.segment2 people_group_segment2
         ,ppg.segment3 people_group_segment3
         ,pcac.source_sub_type
         ,pcac.segment1 cost_segment1
         ,pcac.segment2 cost_segment2
         ,pcac.segment3 cost_segment3
         ,pcac.segment4 cost_segment4
         ,pcac.segment5 cost_segment5
         ,pcac.segment6 cost_segment6
         ,pcac.segment7 cost_segment7
         ,pcac.segment8 cost_segment8
         ,pcif.costable_type costing_type
         ,pcif.transfer_to_gl_flag
FROM      pay_cost_alloc_accounts pcac
         ,pay_cost_allocations_f pcaf
         ,pay_cost_info_f pcif
         ,per_people_groups ppg
         ,hr_all_organization_units haou
         ,pay_element_criteria pec
         ,pay_element_links_f pelf
         ,pay_ele_classifications_tl pect
         ,per_legislative_data_groups_vl pldgv
         ,pay_element_types_f petf
         ,pay_element_types_tl pett
WHERE         pcac.cost_allocation_record_id(+) = pcaf.cost_allocation_record_id
          AND pelf.effective_start_date BETWEEN pcaf.effective_start_date(+) AND pcaf.effective_end_date(+)
          AND pcaf.source_type(+) = 'EL'
          AND pcaf.source_id(+) = pelf.element_link_id
          AND pelf.effective_start_date BETWEEN pcif.effective_start_date(+) AND pcif.effective_end_date(+)
          AND pcif.source_type(+) = 'EL'
          AND pcif.source_id(+) = pelf.element_link_id
          AND ppg.people_group_id(+) = pec.people_group_id
          AND haou.organization_id(+) = pec.legal_employer_id
          AND pec.element_criteria_id(+) = pelf.element_criteria_id
          AND pelf.element_type_id(+) = pett.element_type_id
          AND pect.language = 'US'
          AND pect.classification_id = petf.classification_id
          AND pldgv.legislative_data_group_id = petf.legislative_data_group_id
          AND petf.effective_start_date = (SELECT  MAX (petfi.effective_start_date)
                                           FROM    pay_element_types_f petfi
                                           WHERE   petfi.element_type_id = petf.element_type_id)
          AND petf.element_type_id = pett.element_type_id
          AND pett.language = 'US'
ORDER BY  pect.classification_name
         ,pett.element_name
         ,pelf.element_link_name
         ,pelf.effective_start_date




SELECT    pect.classification_name link_classification_name
         ,pett.element_name link_element_name
         ,pelf.element_link_name link_element_link_name
         ,TO_CHAR (plivf.effective_start_date
                  ,'YYYY/MM/DD')
             link_effective_start_date
         ,TO_CHAR (plivf.effective_end_date
                  ,'YYYY/MM/DD')
             link_effective_end_date
         ,pivt.name link_input_name
         ,pcif.costable_type link_costable_type
         ,pcif.costed_flag link_costed_flag
         ,pcif.transfer_to_gl_flag link_transfer_to_gl_flag
FROM      pay_ele_classifications_tl pect
         ,pay_element_types_f petf
         ,pay_element_types_tl pett
         ,pay_element_links_f pelf
         ,pay_input_values_tl pivt
         ,pay_link_input_values_f plivf
         ,pay_cost_info_f pcif
WHERE         pect.language = 'US'
          AND pect.classification_id = petf.classification_id
          AND pcif.effective_start_date BETWEEN petf.effective_start_date AND petf.effective_end_date
          AND petf.element_type_id = pett.element_type_id
          AND pett.language = 'US'
          AND pett.element_type_id = pelf.element_type_id
          AND pcif.effective_start_date BETWEEN pelf.effective_start_date AND pelf.effective_end_date
          AND pelf.element_link_id = plivf.element_link_id
          AND pivt.language = 'US'
          AND pivt.input_value_id = plivf.input_value_id
          AND pcif.effective_start_date BETWEEN plivf.effective_start_date AND plivf.effective_end_date
          AND plivf.link_input_value_id = pcif.source_id
          AND pcif.source_type = 'LIV'
ORDER BY  pect.classification_name
         ,pett.element_name
         ,pelf.element_link_name
         ,pelf.effective_start_date



SELECT    pldg.name ele_leg_data_group_name
         ,pect.classification_name ele_classification_name
         ,pett.element_name ele_element_name
         ,pivf.display_sequence ele_display_sequence
         ,pivt.name ele_input_name
         ,pivf.user_enterable_flag ele_user_enterable_flag
         ,pivf.mandatory_flag ele_mandatory_flag
         ,pivf.hot_default_flag ele_hot_default_flag
         ,hr_general.decode_lookup ('UOM'
                                   ,pivf.uom)
             ele_uom
         ,pivf.user_display_flag ele_user_display_flag
         ,pivf.DEFAULT_VALUE ele_default_value
         ,pivf.lookup_type ele_lookup_type
         ,pivf.min_value ele_min_value
         ,pivf.max_value ele_max_value
         ,pivf.value_set_code ele_value_set_code
FROM      pay_input_values_tl pivt
         ,pay_input_values_f pivf
         ,per_legislative_data_groups_vl pldg
         ,pay_ele_classifications_tl pect
         ,pay_element_types_tl pett
         ,pay_element_types_f petf
WHERE         pivt.language = 'US'
          AND pivt.input_value_id = pivf.input_value_id
          AND petf.effective_start_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date
          AND pldg.legislative_data_group_id = petf.legislative_data_group_id
          AND pect.language = 'US'
          AND pect.classification_id = petf.classification_id
          AND pett.language = 'US'
          AND pett.element_type_id = petf.element_type_id
and 1=2
ORDER BY  pect.classification_name
         ,pett.element_name
         ,petf.effective_start_date
         ,pivf.display_sequence

Bank Details Query

SELECT   papf.person_number
,       ppnf.full_name
,           pp.attribute1 initials
,           bank_dtls.bank_account_name
,           bank_dtls.bank_name
,           bank_dtls.bank_branch_name
,          bank_dtls.bank_account_num
,           bank_dtls.iban_number
,           bank_dtls.currency_code
,           bank_dtls.base_org_pay_method_name
FROM    per_all_people_f papf
,    per_person_names_f ppnf
,    per_all_assignments_f paaf
,    pay_rel_groups_dn payrel
,           per_persons pp
,    (SELECT pba.bank_account_name
       ,       pba.bank_name
       ,       pba.bank_branch_name
       ,      pba.bank_account_num
       ,       pba.iban_number
       ,       popf.currency_code
       ,       popf.base_org_pay_method_name
     ,       pppmf.payroll_relationship_id
     FROM  pay_bank_accounts pba
     ,   pay_person_pay_methods_f pppmf
     ,   pay_org_pay_methods_f popf
     WHERE pppmf.bank_account_id = pba.bank_account_id
     AND     pppmf.org_payment_method_id = popf.org_payment_method_id
     AND TRUNC(SYSDATE) BETWEEN pppmf.effective_start_date AND pppmf.effective_end_date
     AND TRUNC(SYSDATE) BETWEEN popf.effective_start_date AND popf.effective_end_date
     AND TRUNC(SYSDATE) BETWEEN pba.start_date AND pba.end_date) bank_dtls
WHERE papf.person_id = ppnf.person_id
AND ppnf.name_type ='GLOBAL'
AND payrel.payroll_relationship_id = bank_dtls.payroll_relationship_id(+)
AND payrel.assignment_id = paaf.assignment_id
AND papf.person_id = paaf.person_id
AND   paaf.assignment_status_type = 'ACTIVE'
--AND papf.person_number = '44'
-- AND paaf.primary_flag = 'Y'
AND   papf.person_id = pp.person_id
AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
--AN D TRUNC(SYSDATE) BETWEen epbad.account_start_date and epbad.account_end_date
AND TRUNC(SYSDATE) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN payrel.start_date AND payrel.end_date

Assigned Payrolls

SELECT    papf.person_number
         ,paam.assignment_number
         ,TO_CHAR (pps.date_start
                  ,'YYYY/MM/DD')
             hire_date
         ,TO_CHAR (pps.actual_termination_date
                  ,'YYYY/MM/DD')
             actual_termination_date
         ,papfp.payroll_name
         ,TO_CHAR (papd.start_date
                  ,'YYYY/MM/DD')
             start_date
         ,TO_CHAR (papd.lsed
                  ,'YYYY/MM/DD')
             close_date
         ,TO_CHAR (papd.lspd
                  ,'YYYY/MM/DD')
             last_standard_process_date
         ,TO_CHAR (papd.fsed
                  ,'YYYY/MM/DD')
             final_process_date
FROM      pay_all_payrolls_f papfp
         ,pay_assigned_payrolls_dn papd
         ,pay_payroll_assignments ppay
         ,per_all_assignments_m paam
         ,per_all_people_f papf
         ,per_periods_of_service pps
WHERE         1 = 1
          AND papd.start_date BETWEEN papfp.effective_start_date(+) AND papfp.effective_end_date(+)
          AND papfp.payroll_id(+) = papd.payroll_id
          AND papd.payroll_term_id(+) = ppay.payroll_term_id
          AND ppay.hr_assignment_id(+) = paam.assignment_id
          AND LEAST (NVL (pps.actual_termination_date
                         ,TO_DATE ('31-12-4712'
                                  ,'DD-MM-YYYY'))
                    ,TO_DATE ('31-12-4712'
                             ,'DD-MM-YYYY')) BETWEEN paam.effective_start_date
                                                 AND paam.effective_end_date
          AND paam.effective_latest_change = 'Y'
          AND paam.assignment_type = 'E'
          AND paam.primary_assignment_flag = 'Y'
          AND paam.primary_flag = 'Y'
          AND paam.period_of_service_id = pps.period_of_service_id
          AND pps.date_start BETWEEN papf.effective_start_date AND papf.effective_end_date
          AND papf.person_id = pps.person_id
ORDER BY  papf.person_number
         ,papd.start_date

Area of Responsibility AOR Query

SELECT (SELECT DISTINCT PERSON_NUMBER FROM PER_ALL_PEOPLE_F PER WHERE PER.PERSON_ID = AOR.PERSON_ID) PERSON_NUMBER,
    (SELECT DISTINCT FULL_NAME FROM PER_PERSON_NAMES_F PER WHERE PER.PERSON_ID = AOR.PERSON_ID AND NAME_TYPE = 'GLOBAL') PERSON_NAME,
    AOR.RESPONSIBILITY_NAME,
    TO_CHAR(AOR.START_DATE,'DD-MON-YYYY','NLS_DATE_LANGUAGE = AMERICAN') START_DATE,
    TO_CHAR(AOR.END_DATE,'DD-MON-YYYY','NLS_DATE_LANGUAGE = AMERICAN') END_DATE,
    AOR.RESPONSIBILITY_TYPE,
    AOR.STATUS,
    (SELECT NAME FROM HR_ALL_ORGANIZATION_UNITS HOU WHERE HOU.ORGANIZATION_ID = AOR.BUSINESS_UNIT_ID) BUSINESS_UNIT,
    (SELECT NAME FROM HR_ALL_ORGANIZATION_UNITS HOU WHERE HOU.ORGANIZATION_ID = AOR.LEGAL_ENTITY_ID) LEGAL_ENTITY,
    (SELECT NAME FROM HR_ALL_ORGANIZATION_UNITS HOU WHERE HOU.ORGANIZATION_ID = AOR.ORGANIZATION_ID) DEPARTMENT,
    (SELECT LOCATION_NAME FROM HR_LOCATIONS HL WHERE HL.LOCATION_ID = AOR.LOCATION_ID) LOCATION,
    (SELECT DISTINCT NAME FROM HR_ALL_POSITIONS_F_VL PP WHERE PP.POSITION_ID = AOR.POSITION_ID) POSITION,
    (SELECT DISTINCT NAME FROM PER_JOBS_F_TL PJ WHERE PJ.JOB_ID = AOR.JOB_ID) JOB,
    (SELECT DISTINCT NAME FROM PER_GRADES_F_TL  PG WHERE PG.GRADE_ID = AOR.GRADE_ID) GRADE,
    AOR.ASSIGNMENT_CATEGORY,
    (SELECT PAY.PAYROLL_NAME
            FROM PAY_ALL_PAYROLLS_F    PAY
        WHERE PAY.PAYROLL_ID = AOR.PAYROLL_ID
             AND TRUNC(SYSDATE) BETWEEN PAY.EFFECTIVE_START_DATE  AND PAY.EFFECTIVE_END_DATE ) PAYROLL,
   (SELECT NAME
     FROM PER_LEGISLATIVE_DATA_GROUPS_VL PLD
      WHERE PLD.LEGISLATIVE_DATA_GROUP_ID = AOR.LEGISLATIVE_DATA_GROUP_ID) LEGISLATIVE_DATA_GROUP
FROM PER_ASG_RESPONSIBILITIES AOR

Absences Leave Entries

select  per.person_number,
            abs.name absence_type,
            to_char(apae.start_date,'DD-MON-YYYY','NLS_DATE_LANGUAGE = AMERICAN')  start_date,
            to_char(apae.end_date,'DD-MON-YYYY','NLS_DATE_LANGUAGE = AMERICAN')  end_date,
            duration
from ANC_PER_ABS_ENTRIES apae,
         PER_PERIODS_OF_SERVICE pps,
         PER_ALL_PEOPLE_F per,
   ANC_ABSENCE_TYPES_VL abs
where apae.period_of_service_id = pps.period_of_Service_id
    and apae.absence_type_id = abs.absence_type_id
    and pps.person_id = per.person_id
 and trunc(sysdate) between abs.effective_start_date and abs.effective_end_Date
 and trunc(sysdate) between per.effective_start_date and per.effective_end_Date
 order by per.person_number

Work Schedules Query

SELECT to_char(psa.start_date,'YYYY/MM/DD') START_DATE,to_char(psa.end_date,'YYYY/MM/DD') END_DATE,papf.person_number,zsst.schedule_name,zsst.SCHEDULE_ID,psa.SCHEDULE_ASSIGNMENT_ID
FROM   zmm_sr_schedules_tl zsst
      ,per_schedule_assignments psa
      ,per_all_assignments_m paam1
      ,per_all_people_f papf
WHERE      1 = 1
       AND zsst.language = 'US'
       AND zsst.schedule_id = psa.schedule_id
       AND TRUNC (SYSDATE) BETWEEN psa.start_date AND psa.end_date
       AND psa.resource_id = paam1.assignment_id
       AND psa.resource_type = 'ASSIGN'
       AND TRUNC (SYSDATE) BETWEEN paam1.effective_start_date
                               AND paam1.effective_end_date
       AND paam1.primary_flag = 'Y'
         AND paam1.effective_latest_change = 'Y'
       AND paam1.assignment_type = 'E'
       AND papf.person_id = paam1.person_id
       AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
                               AND papf.effective_end_date
--and person_number = '17'
ORDER BY LPAD(PAPF.PERSON_NUMBER,20,'0')

User Roles Query

select papf.person_number,
--pu.username,
prd.role_common_name
 from
 per_users pu,
 per_user_roles pr,
 per_roles_dn prd ,
 per_all_people_f papf
 where 1=1
 and papf.person_id = pu.person_id
 and pu.user_id = pr.user_id
 and pr.role_id = prd.role_id
--and papf.person_number = '119802'
 --and prd.role_common_name ='PAYROLL_SPECIALIST_DATA'