Search for:
Jun 26 2023
BIP – Extract Job Details

Once Jobs data is loaded/created, you can use below SQL to extract the setup data for Jobs:

Select pjft.name
      ,pjf.job_code
	  ,TO_CHAR(pjf.effective_start_date,'YYYY/MM/DD') start_date
      ,TO_CHAR(pjf.effective_end_date,'YYYY/MM/DD') end_Date
      ,(select add_set.set_code
          from fnd_setid_sets add_set
         where add_set.set_id = pjf.set_id
		   and add_set.language = USERENV('LANG')) set_code
      ,pjf.active_status
  from per_jobs_f pjf,
       per_jobs_f_tl pjft
 where 1=1
   and pjf.job_id = pjft.job_id
   and pjft.language = USERENV('LANG')
   and trunc(SYSDATE) between pjft.effective_start_date and pjft.effective_end_date
   and trunc(sysdate) between pjf.effective_start_date and pjf.effective_end_date
ORDER BY 2,4
Mandeep Gupta 0 Comments
Jun 4 2023
Configuration – Update Legal Entity Name

There are requirements when the existing legal entity name should be updated to a new name. One should note that the LE name in UI is displayed for a view – HR_ALL_ORGANIZATION_UNITS_F_VL.

Sometimes, even after updating the name in UI, correct name is not reflected on some pages like Manage Employment or Manage Legal Entity Information for HCM.

In such cases, one should verify the value in HR_ALL_ORGANIZATION_UNITS_F_VL view to make sure the updated value is showing here.

If updated value is not available in this view, please follow below steps:

  1. Search for legal entity from ‘Manage Legal Entity for HCM Information’ under My Client Groups -> Workforce Structures
  2. Open the legal entity -> Click on action and choose correct
  3. Submit the changes without doing any change.

This should force the name update to happen in HR_ALL_ORGANIZATION_UNITS_F_VL view.

Mandeep Gupta 0 Comments
Jun 4 2023
BIP – Extract Checklist configuration details

Below query can be used to extract checklist configuration details:

select PER_CHECKLISTS_TL.NAME
      ,PER_CHECKLISTS_TL.DESCRIPTION checklist_desc
      ,PER_TASKS_IN_CHECKLIST_B.CHECKLIST_TASK_NAME tin_ctn
      ,PER_TASKS_IN_CHECKLIST_B.DESCRIPTION task_desc
      ,PER_TASKS_IN_CHECKLIST_B.TASK_ACTION_ID
      ,PER_TASKS_IN_CHECKLIST_B.TASK_CONFIGURATION
      ,PER_CHECKLIST_TASKS_B.CHECKLIST_TASK_ID
      ,PER_CHECKLIST_TASKS_B.TASK_LEVEL_CODE
      ,PER_CHECKLIST_TASKS_B.TASK_LEVEL_VALUE
      ,PER_CHECKLIST_TASKS_B.CHECKLIST_TASK_CODE
      ,PER_CHECKLIST_TASKS_B.TASK_CATEGORY
      ,PER_CHECKLIST_TASKS_B.ACTION_TYPE
      ,PER_CHECKLIST_TASKS_B.TASK_ACTION_ID ctb_action_id
      ,PER_CHECKLIST_TASKS_B.TASK_ACTION_CODE
      ,PER_CHECKLIST_TASKS_TL.CHECKLIST_TASK_NAME ctc_ctn
      ,PER_CHECKLIST_TASKS_TL.DESCRIPTION
      ,PER_CHECKLIST_TASKS_TL.ACTION_URL
      ,PER_CHECKLIST_TASKS_TL.USER_DISPLAY_NAME
FROM  PER_CHECKLISTS_TL PER_CHECKLISTS_TL
     ,PER_TASKS_IN_CHECKLIST_VL PER_TASKS_IN_CHECKLIST_B 
     ,PER_CHECKLIST_TASKS_B PER_CHECKLIST_TASKS_B
     ,PER_CHECKLIST_TASKS_TL PER_CHECKLIST_TASKS_TL
WHERE PER_CHECKLIST_TASKS_B.ACTION_TYPE = 'ORA_CHK_APP_TASK'
AND PER_CHECKLIST_TASKS_B.CHECKLIST_TASK_ID = PER_CHECKLIST_TASKS_TL.CHECKLIST_TASK_ID
AND PER_CHECKLIST_TASKS_TL.LANGUAGE = 'US'
AND PER_CHECKLISTS_TL.LANGUAGE = 'US'
AND PER_TASKS_IN_CHECKLIST_B.CHECKLIST_ID = PER_CHECKLISTS_TL.CHECKLIST_ID
AND PER_TASKS_IN_CHECKLIST_B.TASK_ACTION_CODE = PER_CHECKLIST_TASKS_B.TASK_ACTION_CODE
AND PER_TASKS_IN_CHECKLIST_B.ACTION_TYPE = 'ORA_CHK_APP_TASK'
Mandeep Gupta 0 Comments
Jun 4 2023
HDL – Sample file to load Life events

HDL provides an option to upload Potential life events for a person in benefits. Please note that ‘Open Enrolment’ life event can’t be loaded using the potential life events file.

Provide the data in below format and save it as PotentialLifeEvents.dat

METADATA|PotentialLifeEvents|PersonNumber|LegalEmployer|BenefitRelationName|LifeEventName|LifeEventStatusCode|LifeEventOccuredDate|UnprocessedDate|NotificationDate|DetectedStatusDate|ManualStatusDate|ManualOverrideStatusDate|ProcessedDate|VoidedStatusDate|PtnlLerForPerSrcCd|SourceSystemId|SourceSystemOwner|LerId|PtnlLerForPerId|PersonId|LegalEntityId|BenefitRelationId|LifeEventTypeCode|ProdCd
MERGE|PotentialLifeEvents|123718|XX Test|DFLT|New Hire|UNPROCD|2023/02/01|2023/02/01||||||||123718_2023/02/01|HRC_SQLLOADER|||||||
Mandeep Gupta 0 Comments
May 28 2023
HDL – Inactivate secondary Org classification

Oracle HCM allows an Organization to be classified as multiple Orgs. However, sometime there is a need to inactivate one org classification while keeping the primary classification as Active.

Below is a sample HDL file which can be used for this purpose:

METADATA|Organization|OrganizationId|EffectiveStartDate|EffectiveEndDate|ClassificationCode
MERGE|Organization|300000085401190|2023/04/01||PA_EXPENDITURE_ORG

METADATA|OrgUnitClassification|OrganizationId|OrgUnitClassificationId|EffectiveStartDate|EffectiveEndDate|Status|ClassificationCode
MERGE|OrgUnitClassification|300000085401190|300000261056753|2023/04/01||I|PA_EXPENDITURE_ORG
Mandeep Gupta 0 Comments
May 28 2023
BIP – Query to find list of jobs without job profiles

There is no easy way in OTBI to find list of jobs which don’t have a model profile associated with them. This can be achieved easily using BIP query.

Sample Query:

SELECT * FROM PER_JOBS_F pjf
 WHERE trunc(sysdate) between pjf.effective_start_date and pjf.effective_end_Date
   AND NOT EXISTS ( SELECT 1
                      FROM HRT_PROFILE_ITEMS hpi
                          ,HRT_PROFILES_B hpb
                          ,HRT_PROFILE_RELATIONS hpr
                     WHERE hpi.profile_id = hpb.profile_id
                       AND hpb.profile_usage_code = 'M'
                       AND hpi.profile_id = hpr.profile_id
                       AND pjf.job_id = hpr.object_id
				  )
Mandeep Gupta 0 Comments
May 28 2023
BIP – Query to find Primary flags for a Worker

There are multiple primary flags for a worker in assignment table. Namely – Primary Assignment Flag, Primary Work Terms flag and Primary flag.

Below query can be used to check these flags:

--> Primary Flags Query
SELECT papf.person_number
      ,ppnf.first_name
      ,ppnf.last_name
      ,to_char(ppos.date_start,'RRRR/MM/DD') start_date
	  ,paam.assignment_number
      ,to_char(paam.effective_start_date,'RRRR/MM/DD') asg_eff_start_date
      ,to_char(paam.effective_end_date,'RRRR/MM/DD') asg_eff_end_date
	  ,paam.action_code
	  ,pastt.user_status
	  ,paam.assignment_status_type
	  ,paam.primary_flag
	  ,paam.primary_assignment_flag
	  ,paam.primary_work_relation_flag
	  ,ppos.primary_flag "WR Table Primary Flag"
  FROM PER_ALL_PEOPLE_F papf
      ,PER_PERSON_NAMES_F ppnf
      ,PER_ALL_ASSIGNMENTS_M paam
	  ,PER_PERIODS_OF_SERVICE ppos
      ,PER_ASSIGNMENT_STATUS_TYPES_TL pastt
WHERE papf.person_id = ppnf.person_id
  AND ppnf.name_type = 'GLOBAL'
  AND papf.person_id = paam.person_id
  AND paam.period_of_service_id = ppos.period_of_service_id
  AND paam.assignment_status_type_id = pastt.assignment_status_type_id
  AND paam.effective_sequence = 1
  AND paam.assignment_type NOT LIKE '%T'
  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 
  AND pastt.language = 'US'
  AND papf.person_number IN ('500035','500036')
ORDER BY 1, 6
Mandeep Gupta 0 Comments
May 28 2023
HDL – Sample File to load Contact Records
METADATA|Contact|SourceSystemOwner|SourceSystemId|PersonNumber|StartDate|EffectiveStartDate|EffectiveEndDate|CorrespondenceLanguage|DateOfBirth|DateOfDeath|CountryOfBirth|RegionOfBirth|TownOfBirth|CategoryCode
MERGE|Contact|HRC_SQLLOADER|CONTACT_1292001|1292001|2019/06/08|2019/06/08|4712/12/31||1973/06/15|||||

METADATA|ContactName|SourceSystemOwner|SourceSystemId|PersonNumber|PersonId(SourceSystemId)|EffectiveStartDate|EffectiveEndDate|LastName|NameType|LegislationCode|FirstName|MiddleNames|Title|Honors|KnownAs|PreNameAdjunct|PreviousLastName|Suffix|CharSetContext
MERGE|ContactName|HRC_SQLLOADER|CONTACT_NAME_1292001|1292001|CONTACT_1292001|2019/06/08|4712/12/31|Last Name|GLOBAL|GB|FName||MR.||NameLP||||US

METADATA|ContactLegislativeData|SourceSystemOwner|SourceSystemId|PersonNumber|PersonId(SourceSystemId)|EffectiveStartDate|EffectiveEndDate|LegislationCode|HighestEducationLevel|MaritalStatus|MaritalStatusDate|Sex|FLEX:PER_PERSON_LEGISLATIVE_DFF|FLEX:PER_PERSON_LEGISLATIVE_DATA_LEG_DDF|nationality(PER_PERSON_LEGISLATIVE_DFF=Global Data Elements)
MERGE|ContactLegislativeData|HRC_SQLLOADER|PER_LEGSL_1292001_GB|1292001|CONTACT_1292001|2019/06/08|4712/12/31|GB||||M|||

METADATA|ContactRelationship|SourceSystemOwner|SourceSystemId|PersonId(SourceSystemId)|EffectiveStartDate|EffectiveEndDate|RelatedPersonNumber|ContactType|PersonNumber|BeneficiaryFlag|DependentFlag|EmergencyContactFlag|ExistingPerson|PrimaryContactFlag|SequenceNumber
MERGE|ContactRelationship|HRC_SQLLOADER|PER_CONTACT_RELTNSHP_1292001_S|CONTACT_1292001|2019/06/08|4712/12/31|1234123|S|1292001|N|N|Y|||
Mandeep Gupta 0 Comments
Mar 12 2023
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
Mandeep Gupta 0 Comments
Mar 12 2023
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)
Mandeep Gupta 0 Comments
Mar 12 2023
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.

Mandeep Gupta 0 Comments
Mar 12 2023
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)

Mandeep Gupta 0 Comments
Mar 12 2023
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.
Mandeep Gupta 0 Comments
Jan 6 2023
Configuration – Extract Table based value set information

Use below query to extract table based value set information from backend tables:

SELECT ffvs.flex_value_set_name
      ,ffvs.description vale_set_desc
      ,ffvs.validation_type
      ,ffvs.format_type
      ,ffvs.maximum_size
      ,ffvs.number_precision
      ,ffvs.alphanumeric_allowed_flag
      ,ffvs.uppercase_only_flag
      ,ffvt.value_column_name	  
      ,ffvt.value_column_type
      ,ffvt.value_column_size
      ,ffvt.meaning_column_name
      ,ffvt.meaning_column_type
      ,ffvt.meaning_column_size
      ,ffvt.id_column_name
      ,ffvt.id_column_type
      ,ffvt.id_column_size
      ,ffvt.enabled_column_name
      ,ffvt.start_date_column_name
      ,ffvt.end_date_column_name
      ,ffvt.summary_column_name
      ,ffvt.application_table_name
      ,ffvt.additional_where_clause
      ,ffvt.additional_quickpick_columns
  FROM fnd_flex_value_sets ffvs
      ,fnd_flex_validation_tables ffvt
 WHERE ffvs.flex_value_set_id = ffvt.flex_value_set_id
   AND ffvs.flex_value_set_name LIKE 'XX%'
Mandeep Gupta 1 Comments
Jan 6 2023
HDL – Script to DELETE positions data

In your test environments, you may encounter issues where you want to DELETE positions data. You can use below script for that:

SELECT DATA_ROW
FROM (
SELECT 'METADATA|Position|PositionId|EffectiveStartDate|EffectiveEndDate|SourceSystemId|SourceSystemOwner'  AS DATA_ROW
FROM DUAL
UNION all
select 'DELETE'||'|'||
       'Position' ||'|'||
       hapf.Position_Id||'|'||
       to_char(hapf.Effective_Start_Date,'RRRR/MM/DD')||'|'||
       to_char(hapf.Effective_End_Date,'RRRR/MM/DD') ||'|'||
      (select email_hrc.source_system_id
         from hrc_integration_key_map email_hrc
        WHERE hapf.Position_Id = email_hrc.surrogate_id) ||'|'||
      (select email_hrc.source_system_owner
         from hrc_integration_key_map email_hrc
        WHERE hapf.Position_Id = email_hrc.surrogate_id) AS DATA_ROW
  from hr_all_positions_f hapf
)
Mandeep Gupta 0 Comments