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