Tag: Fusion HCM - Page 4

Action Reason Usage, BIP, Configuration, Fusion HCM, HCM Data Loader, Report

Configuration – Pull configured actions/action reasons

Below query can be used to pull actions and configured actions for these reasons along with action type:

SELECT pav.action_type_code
      ,pav.action_code
      ,pav.action_name
      ,parv.action_reason_code
      ,parv.action_reason
      ,to_char(aru.start_date, 'yyyy/mm/dd') start_date
      ,to_char(aru.end_date, 'yyyy/mm/dd') end_date
  FROM PER_ACTION_REASON_USAGES aru,
       PER_ACTIONS_VL pav,
       PER_ACTION_REASONS_VL parv
 WHERE 1=1
   AND aru.action_id = pav.action_id
   AND aru.action_reason_id = parv.action_reason_id
   AND pav.action_code = 'CHANGE_SALARY'
ORDER BY 1,3,5

ByMandeep Gupta

Configuration, Fusion HCM, HCM Data Loader, Run Diagnostics

Fusion Diagnostics – Important MOS notes related to diagnostics

Below are some important my oracle support notes related to diagnostic framework:

Self-Service Data Integrity Framework for Employment Flows – Part 2 (Doc ID 2597759.1)
Self-Service Data Integrity Framework for Employment Flows – Part 1 (Doc ID 2548287.1)
Self-Service Data Integrity Framework for Person Flows (Doc ID 2548789.1)
Self-Service Data Integrity Framework for Workforce Structure Flows (Doc ID 2548827.1)
Fusion Global HR: Corruption Type in Person Diagnostic Auto Correct Report (Doc ID 2619978.1)

Check below link to see the privilege’s required for run Diagnostics:

Diagnostics – Run Diagnostics is missing

ByMandeep Gupta

Fusion HCM, HCM Data Loader, HDL

HDL – Loading Delivery Preferences for a Worker

PersonDeliveryMethod child business object of Worker can be used to upload delivery preferences for a worker.

Below is sample HDL file:

METADATA|PersonDeliveryMethod|DeliveryMethodId|DateStart|DateEnd|PersonId|PersonNumber|PreferredOrder|CommDlvryAddress|CommDlvryMethod|CommDlvryFkId|AddressType|AddressLine1|PhoneType|PhoneNumber|EmailType|EmailAddress|SourceSystemOwner|SourceSystemId
MERGE|PersonDeliveryMethod||2023/01/01|4712/12/31||1234|1||NORMAL||HOME|Address Line 1|||||HRC_SQLLOADER|1234_HOME_Address Line 1

ByMandeep Gupta

BIP, Configuration, Fusion HCM, HCM Data Loader, HDL, Report

HDL – Loaded Salary Data Extract in HDL Format

Select 'METADATA|Salary|AssignmentNumber|SalaryAmount|DateFrom|DateTo|SalaryBasisId|SalaryId|SourceSystemId|SourceSystemOwner|ActionCode|ActionReasonCode' Header, 1 data_flow_order
from dual
UNION
SELECT 'MERGE|Salary'||'|'||
paam.assignment_number||'|'||
SALARY_AMOUNT||'|'||
TO_CHAR(cs.date_from,'RRRR/MM/DD', 'nls_date_language=American')||'|'||
TO_CHAR(cs.date_to,'RRRR/MM/DD', 'nls_date_language=American')||'|'||
cs.salary_basis_id||'|'||
cs.salary_id||'|'||
hikm.source_system_id||'|'|| 
source_system_owner||'|'|| 
pav.action_code||'|'||
'XX_ANNL_REVIEW'  data_row,
2 data_flow_order
FROM cmp_salary cs,
per_all_assignments_m paam,
hrc_integration_key_map hikm,
PER_ACTIONS_VL pav,
PER_ACTION_REASONS_VL parv
WHERE cs.assignment_id= paam.assignment_id
AND trunc(sysdate) between paam.effective_start_date AND paam.effective_end_date
AND paam.assignment_type not like '%T'
AND cs.salary_id = hikm.surrogate_id
and cs.action_id = pav.action_id
and cs.action_reason_id = parv.action_reason_id
and parv.action_reason_code = 'CMP_ANNV'
ORDER BY data_flow_order

ByMandeep Gupta

cache, Configuration, environemnt, environment, Error, Fusion HCM

Environment – Error Accessing HCM Environment

Sometimes accessing an HCM environment causes in “Max HTTP Headers, Blocked by WAF” error as shown below:

Resolution:

This issue occurs due to browser cache issue. Clearing the cache or trying to access the environment in incognito mode or another browser should resolve the issue.

ByMandeep Gupta

BIP, Configuration, Fusion HCM, Jobs, Report

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

ByMandeep Gupta

Configuration, Fusion HCM, Legal Entity

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:

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

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

ByMandeep Gupta

BIP, Checklist, Configuration, Fusion HCM, Report

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'

ByMandeep Gupta

BIP, Configuration, Element Set, Fusion HCM, HDL, Object Group, Report

BIP – Query to extract Element Set Details

SELECT poga.object_id
      ,petft.element_name
      ,poga.include_or_exclude
  FROM pay_object_groups pog
      ,pay_object_group_amends poga
	  ,pay_element_types_tl petft
 WHERE poga.object_group_id = pog.object_group_id  
   AND pog.base_object_group_name='Test Element Set'
   AND petft.element_type_id = poga.object_id
   AND petft.language = 'US'

ByMandeep Gupta

Configuration, Fusion HCM, Run Diagnostics

Diagnostics – Run Diagnostics is missing

With latest HCM release in 2022, “Run Diagnostics” option was removed from user profile. A new role is now required in order to give access to this function.

Role Name – Application Diagnostics Advanced User

Role Code – ORA_FND_DIAG_ADVANCED_USER_JOB

ByMandeep Gupta

BIP, Configuration, Department, Fusion HCM, HCM Data Loader, HDL, inactive, Org Classification

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

ByMandeep Gupta

BIP, Fusion HCM, Job Profile, Jobs, Profiles, Report

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
				  )

ByMandeep Gupta

BIP, Fusion HCM, HCM Data Loader, HDL, reports

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

ByMandeep Gupta

BIP, Delete, Fusion HCM, HCM Data Loader, HDL, Position, Purge, Report

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
)

ByMandeep Gupta

attachment name, BIP, Document Record, Fusion HCM, Report, reports

BIP – Query to extract DOR attachment name

select papf.person_number 
      ,hdor.document_code
      ,hdor.document_name
      ,hdor.document_number
      ,fdt.file_name
      ,fdt.dm_version_number document_id
      ,fdt.dm_document_id UCM_file
 from fnd_attached_documents fad, hr_documents_of_record hdor, fnd_documents_tl fdt
   ,per_all_people_f papf
   ,hr_document_types_tl hdtt
 where ENTITY_NAME = 'HR_DOCUMENTS_OF_RECORD'
  and hdor.documents_of_record_id = fad.PK1_VALUE
  and fad.document_id = fdt.document_id
  and fdt.language = 'US'
  and hdor.person_id = papf.person_id
  and TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
  and hdor.document_type_id = hdtt.document_type_id
  and hdtt.language = 'US'
  and hdtt.DOCUMENT_TYPE = 'Test Doc'

ByMandeep Gupta

Fusion HCM, HCM Data Loader, HDL, learning item, Non Catalog, OLC

HDL – Sample HDL to load Non Catalog Learning items

METADATA|NoncatalogLearningItem|LearningItemId|EffectiveStartDate|EffectiveEndDate|LearningItemNumber|Title|Description|Duration|Price|CurrencyCode|OwnedByPersonId|OwnedByPersonNumber|NoncatalogURL|SourceId|SourceInfo|SourceType|SourceSystemId|SourceSystemOwner

MERGE|NoncatalogLearningItem||2022/05/01||CLASS_EXT_128|External Excel Course||||||12345||1231|CLASS_EXT_128|CLASS_EXT|CLASS_EXT_128|HRC_SQLLOADER

ByMandeep Gupta

BIP, Candidate, candidate email, Data Masking, email address, Fusion HCM, HCM Data Loader, HDL

HDL – Mask Candidate email addresses

Post P2T, there is always a requirement to mask the candidate email addresses in lower environments. Below SQL can be used to generate data masked email data in HDL format:

SELECT 'METADATA|CandidateEmail|EmailAddressId|PersonId|CandidateNumber|EmailAddress|DateFrom|DateTo|SourceSystemOwner|SourceSystemId' "Record"

 FROM DUAL

UNION

SELECT DISTINCT 'MERGE'    || '|'||

'CandidateEmail'        || '|'||

ICEA.EMAIL_ADDRESS_ID          || '|'||

ICEA.PERSON_ID             || '|'||

ICEA.CANDIDATE_NUMBER          || '|'||

replace(PEAD.EMAIL_ADDRESS,'.com','.invalidcomx')           || '|'||

TO_CHAR(PEAD.DATE_FROM,'YYYY/MM/DD')  || '|'||

TO_CHAR(PEAD.DATE_TO,'YYYY/MM/DD')   || '|'||

HIKM.SOURCE_SYSTEM_OWNER      || '|'||

    HIKM.SOURCE_SYSTEM_ID      "Record"

FROM IRC_CAND_EMAIL_ADDRESS_V ICEA,

PER_EMAIL_ADDRESSES PEAD,

HRC_INTEGRATION_KEY_MAP HIKM

WHERE ICEA.EMAIL_ADDRESS_ID = PEAD.EMAIL_ADDRESS_ID

  AND HIKM.SURROGATE_ID = PEAD.EMAIL_ADDRESS_ID

You can update the email address as per your requirement.

ByMandeep Gupta

Document Record, DOR, Fusion HCM, HCM Data Loader, HDL

HDL – Sample HDL file to upload DoR for a specific country

METADATA|DocumentsOfRecord|PersonNumber|Country|DocumentType|DocumentCode|DateFrom|DateTo

MERGE|DocumentsOfRecord|78652|United Kingdom|UK Vehicle Information|OVERTIME_AVERAGING_AGREEMENT_2020-10-01|2020/10/01|2022/10/01



METADATA|DocumentAttachment|PersonNumber|Country|DocumentType|DocumentCode|DataTypeCode|Title|URLorTextorFileName|File

MERGE|DocumentAttachment|78652|United Kingdom|UK Vehicle Information|OVERTIME_AVERAGING_AGREEMENT_2020-10-01|FILE|DD test document of record for HDL.pdf|DD test document of record for HDL.pdf|DD test document of record for HDL.pdf

Please note that under attribute country, you will need to pass the full country name. Passing country code like GB, will throw below error:

The values GB aren’t valid for the attribute LegislationCode.

ByMandeep Gupta

AOR, BIP, Fusion HCM, OTBI, Report

OTBI – Areas of Responsibility Subject area

Oracle has released a new subject area for Areas of Responsibility.

Subject Area Name – Workforce Management – Areas of Responsibility

This has simplified the process of extracting AoR data using OTBI.

To get any data in this subject area, you should run “Refresh Representative Data” ESS process. The process should be run post any changes done to AoR’s.

To get data in this SA below duties should be assigned:

FBI_AREAS_OF_RESPONSIBILITY_TRANSACTION_ANALYSIS_DUTY 
ORA_FBI_AREAS_OF_RESPONSIBILITY_TRANSACTION_ANALYSIS_DUTY_HCM

Please note that these duties should be added to Job Role not the Data Role.

ByMandeep Gupta

BIP, Fusion HCM, HDL, Seniority Date

BIP – How to check Termination version on your environment?

You can use per_empl_configurations table to verify the versions of Seniority dates/ Termination dates.

Below is an example to check the version for Termination dates:

select version_code 
  from fusion.per_empl_configurations 
 where configuration_type = 'ORA_TERMINATION'

If the version code value is NULL that means you are still on version V2.

Same table can be used to query Seniority Dates version.

Tag Archive Fusion HCM

Configuration – Pull configured actions/action reasons

Fusion Diagnostics – Important MOS notes related to diagnostics

HDL – Loading Delivery Preferences for a Worker

HDL – Loaded Salary Data Extract in HDL Format

Environment – Error Accessing HCM Environment

BIP – Extract Job Details

Configuration – Update Legal Entity Name

BIP – Extract Checklist configuration details

BIP – Query to extract Element Set Details

Diagnostics – Run Diagnostics is missing

HDL – Inactivate secondary Org classification

BIP – Query to find list of jobs without job profiles

BIP – Query to find Primary flags for a Worker

HDL – Script to DELETE positions data

BIP – Query to extract DOR attachment name

HDL – Sample HDL to load Non Catalog Learning items

HDL – Mask Candidate email addresses

HDL – Sample HDL file to upload DoR for a specific country

OTBI – Areas of Responsibility Subject area

BIP – How to check Termination version on your environment?

Latest Posts

Categories

Recent Comments

Archives