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'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
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_ORGThere 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
)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 METADATA|JobTranslation|EffectiveStartDate|EffectiveEndDate|JobCode|Name|Language|SetCode
MERGE|JobTranslation|1901/01/01|4712/12/31|TEST_664|Japnese Test Officer|JA|COMMONMETADATA|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|||