Monthly Archive 12 March 2023

ByMandeep Gupta

BIP – Query to find element entry counts

SELECT pett.element_name, count(peef.element_type_id)
  FROM pay_element_types_tl pett
      ,pay_element_entries_f peef
 WHERE pett.element_type_id = peef.element_type_id
   AND pett.language = 'US'
   AND to_date('01-04-2022','DD/MM/RRRR') BETWEEN peef.effective_start_date AND peef.effective_end_date
 GROUP BY pett.element_name
ByMandeep Gupta

BIP – Query to find persons with multiple active work relationships

SELECT papf.person_number
 FROM per_all_people_f papf
 WHERE 1=1
  AND EXISTS (SELECT 1 FROM per_periods_of_service ppos
               WHERE ppos.person_id = papf.person_id
                 AND (ppos.actual_termination_date IS NULL or ppos.actual_termination_date>= TRUNC(SYSDATE))
               GROUP BY ppos.person_id 
              HAVING COUNT (ppos.period_of_service_id) >1
            )
ByMandeep Gupta

BIP – Check bypass Approval Rules status

While performing SIT/UAT, sometimes it becomes mandatory to disable (bypass) all configured approval rules except for a few. One has to go in manually in Transaction console and check the status for each task.

An easy way to run the following SQL query and check the tasks for which approvals are yet not Bypassed:

SELECT HRPB.TASK_FILE_NAME TaskName
      ,DECODE(HAAO.APPROVAL_DISABLED,'true','Bypassed','false','Enabled') "Enabled Status"
      ,haao.last_update_date
      ,haao.last_updated_by
  FROM FUSION.HRC_ARM_PROCESS_B HRPB
      ,FUSION.HRC_ARM_APPROVAL_OPTIONS HAAO
 WHERE HRPB.PROCESS_ID=HAAO.PROCESS_ID
ByMandeep Gupta

BIP – List of workers without payment method

SELECT papf.person_number

  FROM PER_ALL_PEOPLE_F papf
      ,PER_PERIODS_OF_SERVICE ppos
 WHERE 1=1
  AND papf.person_id = ppos.person_id
  AND (ppos.actual_termination_date IS NULL OR ppos.actual_termination_date >= TRUNC(SYSDATE))
  AND NOT EXISTS (SELECT 'Found' FROM PAY_PAY_RELATIONSHIPS_DN pprd
                                     ,PAY_PERSON_PAY_METHODS_F pppmf
                                WHERE pprd.payroll_relationship_id = pppmf.payroll_relationship_id

                                  AND pprd.person_id = papf.person_id)
ByMandeep Gupta

HDL – Loading Local Person Names

By default, PersonName child object of Worker.dat loads GLOBAL name type. PersonName object can be used to load local name as well.

Two most important attributes of PersonName object for local name upload are:

Name Type – Legislation Code

CharSetContext – Short code for language.

Below is a sample HDL file to upload local names in Thai language:

METADATA|PersonName|EffectiveEndDate|EffectiveStartDate|LegislationCode|PersonId|NameType|FirstName|MiddleNames|LastName|Honors|KnownAs|PreNameAdjunct|PreviousLastName|Suffix|Title|MilitaryRank|CharSetContext

MERGE|PersonName|4712/12/31|2019/09/20|MY|300000189140970|MY|||XYZ||Test122First||||||TH

Language code can be found in Task – Manage Languages.

ByMandeep Gupta

Approvals – Approval Delegation Category Diagnostic

Approval delegation category can be defined at an individual level in BPM. This enables users to group tasks together which can be used while performing delegation.

For any issues related to custom delegation categories, you can run “Transaction Framework – Export Task Categories diagnostic” by navigating:

From homepage -> User name icon -> run diagnostic ->search for “Transaction Framework – Export Task Categories”” ->add to run ->run ->keep refreshing in Run status section ->one report is completed ,open the report against second folder(i.e Transaction Framework – Export Task Categories) and save it

Fusion HCM: Hiding/Renaming Task Category Values Displayed in Self-Service >> Approval Delegation (Doc ID 2724671.1)

ByMandeep Gupta

HDL – Load Worker Images

HCM Data Loader can be used to mass upload worker images. The actual image file should be put in a BlobFiles folder and should be referenced in actual Worker.dat file.

Post this, Worker.dat will be zipped together with BlobFiles folder and uploaded into HCM using Import and Load.

Sample HDL file:

METADATA|PersonImage|Image|ImageName|PersonNumber|SourceSystemOwner|SourceSystemId
MERGE|PersonImage|XYZ_12364.jpg|XYZ_12364|12364|EBS-HR|12364

XYZ_12364.jpg file should be present under BlobFiles.