Tag: HCM Data Loader - Page 5

Jun 28 2021

HDL – Sample File to DELETE documents of Record

Use the below query to extract document of record data which should be deleted. You can add additional filters by adding person numbers.

select 'DELETE' 					"METADATA"
      ,'DocumentsOfRecord'			"DocumentsOfRecord"
	  ,hdor.documents_of_record_id	"DocumentsOfRecordId"
	  ,hdor.document_type_id	    "DocumentTypeId"
	  ,hdor.document_code			"DocumentCode"
	  ,hdor.document_name			"DocumentName"
	  ,hdor.document_number			"DocumentNumber"
	  ,hikm.source_system_owner		"SourceSystemOwner"
	  ,hikm.source_system_id		"SourceSystemId"
  from HR_DOCUMENTS_OF_RECORD hdor
      ,HRC_INTEGRATION_KEY_MAP hikm
 where hdor.documents_of_record_id = hikm.surrogate_id
   and hdor.documents_of_record_id = 300000217125443

Query for Person Number and Document Type:

select DISTINCT 'DELETE' 					"METADATA"
      ,'DocumentsOfRecord'			"DocumentsOfRecord"
	  ,hdor.documents_of_record_id	"DocumentsOfRecordId"
	  ,hdor.document_type_id	    "DocumentTypeId"
	  ,hdor.document_code			"DocumentCode"
	  ,hdor.document_name			"DocumentName"
	  ,hdor.document_number			"DocumentNumber"
	  ,hikm.source_system_owner		"SourceSystemOwner"
	  ,hikm.source_system_id		"SourceSystemId"
  from HR_DOCUMENTS_OF_RECORD hdor
      ,HRC_INTEGRATION_KEY_MAP hikm
	  ,PER_ALL_PEOPLE_F papf
	  ,HR_DOCUMENT_TYPES_TL hdtt
 where hdor.documents_of_record_id = hikm.surrogate_id
   --and hdor.documents_of_record_id = 300000217125443
   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'
   and papf.person_number IN ('12','23')

Sample HDL File:

METADATA|DocumentsOfRecord|DocumentsOfRecordId|DocumentTypeId|DocumentCode|DocumentName|DocumentNumber|SourceSystemOwner|SourceSystemId
DELETE|DocumentsOfRecord|300000217125443|300000217168555|TMAD_001|Multiple Attachments||HRC_SQLLOADER|HRC_SQLLOADER_101_TMAD_001

Mandeep Gupta 0 Comments

Jun 12 2021

HDL – Update Goal Weight

Goal Weightage can be updated using GoalMeasurement metadata in Goal business object. First, we need to extract the uploaded measurements. Use the below query to extract the details:

SELECT HDL_DATA
FROM (
SELECT 'METADATA|GoalMeasurement|MeasurementId|MeasurementName|StartDate|EndDate|TargetValue|TargetPercentage|UomCode|MeasureTypeCode|TargetType|GoalId|SourceSystemId|SourceSystemOwner' HDL_DATA, 1 SEQ
  FROM DUAL
UNION ALL
SELECT 'MERGE'
     || CHR (124)
     || 'GoalMeasurement'
     || CHR (124)	 
	 || hgm.measurement_id
     || CHR (124)
	 || hgm.measurement_name
     || CHR (124)
	 || TO_CHAR(hgm.start_date,'RRRR/MM/DD')
     || CHR (124)
	 || TO_CHAR(hgm.end_date,'RRRR/MM/DD')
     || CHR (124)
	 || hgm.target_value
     || CHR (124)
	 || hgm.target_percentage
     || CHR (124)
	 || hgm.uom_code
     || CHR (124)
	 || hgm.measure_type_code
     || CHR (124)
	 || hgm.target_type
     || CHR (124)
	 || hg.goal_id
     || CHR (124)
	 || hikm.source_system_id
     || CHR (124)
	 || hikm.source_system_owner HDL_DATA, 2 SEQ
  FROM hrg_goals hg
      ,hrg_goal_measurements hgm
      ,hrc_integration_key_map hikm
 WHERE hg.goal_id = hgm.goal_id
   AND hgm.measurement_id = hikm.surrogate_id
   AND hgm.uom_code = 'PERCENT'
   --AND hg.goal_id = 300000215856607
)
ORDER BY SEQ

Save the data in Goal.dat HDL file. Update the target value as required. Save the .dat file and upload.

Sample HDL:

METADATA|GoalMeasurement|MeasurementId|MeasurementName|StartDate|EndDate|TargetValue|TargetPercentage|UomCode|MeasureTypeCode|TargetType|GoalId|SourceSystemId|SourceSystemOwner
MERGE|GoalMeasurement|300000215856608|Customer Survey Results|2019/01/01|2019/12/31|80||PERCENT|QUANTITATIVE|MIN|300000215856607|300000215856608|FUSION

Mandeep Gupta 0 Comments

May 24 2021

HDL – Sample File to Load Succession Plan

You can make use of HCM Data Loader to upload Succession Plans (Incumbent, Job and Position Types).

Use the below sample file to load Succession Plans:

METADATA|SuccessionPlan|PlanName|PlanType|Status|AccessTypeCode|Description|IncumbentPersonNumber|JobCode|JobSetCode|DepartmentName|DepartmentSetCode|GradeSetCode|GradeCode|PositionCode|BusinessUnitName|SourceSystemId|SourceSystemOwner
MERGE|SuccessionPlan|Test_Person_Succession_Plan|INCUMBENT|ACTIVE|PUBLIC|Sample description for Incument Plan Type.|111222|||||||||HRC_SQLLOADER_Test_Person_Succession_Plan_INCUMBENT|HRC_SQLLOADER
MERGE|SuccessionPlan|Test_Job_Succession_Plan|JOB|ACTIVE|PUBLIC|Sample description for Job Plan Type.||XX052|COMMON|Test HR Department|COMMON|COMMON|GRD_90||XX BU|HRC_SQLLOADER_Test_Job_Succession_Plan_JOB|HRC_SQLLOADER
MERGE|SuccessionPlan|Test_Position_Succession_Plan|POSITION|ACTIVE|PUBLIC|Sample description for Position Plan Type.||||Test HR Department|COMMON|||POS_0301|COMMON|HRC_SQLLOADER_Test_Position_Succession_Plan_POSITION|HRC_SQLLOADER

METADATA|SuccessionPlanOwner|PlanName|PlanOwnerPersonNumber|OwnerTypeCode|SourceSystemId|SourceSystemOwner
MERGE|SuccessionPlanOwner|Test_Person_Succession_Plan|99997|ADMINISTRATOR|HRC_SQLLOADER_ADMINISTRATOR_99997_Test_Person_Succession_Plan|HRC_SQLLOADER
MERGE|SuccessionPlanOwner|Test_Job_Succession_Plan|99997|ADMINISTRATOR|HRC_SQLLOADER_ADMINISTRATOR_99997_Test_Job_Succession_Plan|HRC_SQLLOADER
MERGE|SuccessionPlanOwner|Test_Position_Succession_Plan|99997|ADMINISTRATOR|HRC_SQLLOADER_ADMINISTRATOR_99997_Test_Position_Succession_Plan|HRC_SQLLOADER
MERGE|SuccessionPlanOwner|Test_Position_Succession_Plan|99998|ADMINISTRATOR|HRC_SQLLOADER_ADMINISTRATOR_99998_Test_Position_Succession_Plan|HRC_SQLLOADER

Once the data is loaded, you can run the query to check loaded plans in HRM_PLANS table.

Mandeep Gupta 0 Comments

Configuration HDL Reports

May 21 2021

BIP – Query to get Configure HCM Data Loader parameters

Configure HCM Data Loader is a task that is used to define the HCM Data Loader parameters. The parameters are broadly divided into following categories:- Availability, Diagnostic, File Definition, Performance and Scheduling Default. These are the default settings which are applied to any HCM Data Load.

You can override some of these parameters at the individual dat file level i.e. using SET commands or some of the parameters can be overridden while submitting the Import and Load job.

Please note that the default values vary from HDL to HSDL (Spreadsheet Loader).

You can use below mentioned query to get the details from backend using a BIP:

select PARAM_CATEGORY
          ,PARAM_NAME
	  ,DEFAULT_VALUE
	  ,HSDL_DEFAULT_VALUE
          ,HDL_ENABLED
	  ,HDL_OVERRIDE_ENABLED
	  ,HSDL_ENABLED
	  ,HSDL_OVERRIDE_ENABLED
	  ,VALUE_LOOKUP_TYPE
	  ,CREATED_BY
	  ,CREATION_DATE
	  ,LAST_UPDATED_BY
	  ,LAST_UPDATE_DATE
 from hrc_dl_all_parameters
 ORDER By 1,2

Query to get list of overridden values:

select hdap.PARAM_CATEGORY
      ,hdap.PARAM_NAME
	  ,hdap.DEFAULT_VALUE
	  ,hdap.HSDL_DEFAULT_VALUE
      ,hdap.HDL_ENABLED
	  ,hdap.HDL_OVERRIDE_ENABLED
	  ,hdap.HSDL_ENABLED
	  ,hdap.HSDL_OVERRIDE_ENABLED
	  ,hdap.VALUE_LOOKUP_TYPE
	  ,hdap.CREATED_BY
	  ,hdap.CREATION_DATE
	  ,hdap.LAST_UPDATED_BY
	  ,hdap.LAST_UPDATE_DATE
	  ,hdpo.OVERRIDE_LEVEL "OVERRIDDEN_AT_LEVEL"
	  ,hdpo.OVERRIDE_VALUE "OVERRIDDEN_VALUE"
 from hrc_dl_all_parameters hdap
     ,hrc_dl_parameter_overrides hdpo
where hdap.parameter_id = hdpo.parameter_id	 
 ORDER By 1,2

Mandeep Gupta 0 Comments

May 18 2021

HDL – Updating Work Day Information at Department Level

Organization.dat can be used to upload work day information at department level.

METADATA|OrgInformation|OrgInformationContext|SequenceNumber|FLEX:PER_ORGANIZATION_INFORMATION_EFF|EFF_CATEGORY_CODE|EffectiveStartDate|EffectiveEndDate|OrganizationName|ClassificationName|_STD_WORKING_HOURS(PER_ORGANIZATION_INFORMATION_EFF=PER_WORK_DAY_INFO)|_STD_WORKING_HOURS_FREQUENCY(PER_ORGANIZATION_INFORMATION_EFF=PER_WORK_DAY_INFO)|_STD_WORKING_HOURS_FREQUENCY_Display(PER_ORGANIZATION_INFORMATION_EFF=PER_WORK_DAY_INFO)|_WORK_END_TIME(PER_ORGANIZATION_INFORMATION_EFF=PER_WORK_DAY_INFO)|_WORK_START_TIME(PER_ORGANIZATION_INFORMATION_EFF=PER_WORK_DAY_INFO)|OrganizationId
MERGE|OrgInformation|PER_WORK_DAY_INFO|1|PER_WORK_DAY_INFO|DEPARTMENT|1951/01/01||XYZ Markets|Department|40|W||18:00|09:00|5000000026311

Please verify the updated information from UI:

Validate Version – 21A

Mandeep Gupta 0 Comments

May 10 2021

HDL – Loading CoverArt File at Course Level

While creating the Courses in Oracle Learning Cloud, there is a capability to load CoverArt for each course. If no cover art is loaded, the learner is presented with a blue strip film.

HCM Data Loaders provides an option to bulk upload the CoverArt file for multiple courses at one go. All the cover art related files (jpg, jepg) should be included in BlobFiles folder and the name of the jpg/jpeg file should be provided in the dat file.

Below is the sample Course dat file:

METADATA|Course|CourseId|EffectiveStartDate|EffectiveEndDate|CourseNumber|Title|ShortDescription|Syllabus|PublishStartDate|PublishEndDate|MinimumExpectedEffort|MaximumExpectedEffort|CurrencyCode|MinimumPrice|MaximumPrice|CoverArtFile|CoverArtFileName|TrailerLiId|TrailerLiNumber|OwnedByPersonId|OwnedByPersonNumber|SourceType|SourceId|SourceInfo|SourceSystemOwner|SourceSystemId
MERGE|Course||2016/01/01||COURSEN_1866|Test Staff Orientation|||2016/01/01||||USD|||Capture.jpg|Capture.jpg||||1010|TALEO|1111|TALEO_1111|HRC_SQLLOADER|HRC_SQLLOADER_1111

Once the BlobFiles Folder and dat file are ready, zip them together and trigger the HDL Import and Load process:

Once the Import and Load process is successful, login to Learning Admin to verify:

My Client Groups -> Learning -> Learning Catalog -> Courses

Mandeep Gupta 0 Comments

Apr 4 2021

HDL – Sample File to Load extended Lookup Codes

Oracle HCM makes use of extended lookups feature to support dependent lookup values. For example, while creating a VISA or Work Permit record for a Person for Singapore, the Category field is dependent upon Type of the pass chosen. The values of Category field are derived from Extended lookup.

Navigate to Setup and Maintenance -> Manage Extended Lookup codes -> Visa Permit Type

Now, let us take an example where we need to load 2 values for categories based on the lookup code S Pass.

Prepare the HDL file in below format:

METADATA|ExtendedLookupCode|ExtendedLookupCodeId|LookupType|LookupCode|LegislationCode|ExtendedLookupCode|ExtendedLookupCodeName|SourceSystemOwner|SourceSystemId
MERGE|ExtendedLookupCode||PER_VISA_PERMIT_TYPE|SG_SP|SG|TEST_SP1|S Pass Holder – Test 1|HRC_SQLLOADER|TEST_1
MERGE|ExtendedLookupCode||PER_VISA_PERMIT_TYPE|SG_SP|SG|TEST_SP2|S Pass Holder – Test 2|HRC_SQLLOADER|TEST_2

zip the file and upload using HCM Data Loader from Data Exchange.

On successful load, the new values can be verified from either of following two places on the UI:

From Manage Extended Lookup codes:

2. From Documents Tab on Person UI:

Mandeep Gupta 0 Comments

Mar 29 2021

HDL – Loading Contract History Using HDL

Sample HDL file to contract history:

METADATA|Worker|PersonId|EffectiveStartDate|EffectiveEndDate|PersonNumber|BloodType|CorrespondenceLanguage|StartDate|DateOfBirth|DateOfDeath|CountryOfBirth|RegionOfBirth|TownOfBirth|PersonDuplicateCheck|ActionCode|ReasonCode|GUID|SourceSystemOwner|SourceSystemId
MERGE|Worker||2016/08/27|4712/12/31|435282|||2016/08/27|1967/07/23||PH|Philippines|||HIRE|||LEGACY|PER_909090

METADATA|PersonName|PersonNameId|EffectiveStartDate|EffectiveEndDate|PersonId(SourceSystemId)|PersonNumber|LegislationCode|NameType|FirstName|MiddleNames|LastName|Honors|KnownAs|PreNameAdjunct|MilitaryRank|PreviousLastName|Suffix|Title|CharSetContext|NameInformation1|NameInformation2|NameInformation3|NameInformation4|NameInformation5|NameInformation6|NameInformation7|NameInformation8|NameInformation9|NameInformation10|NameInformation11|NameInformation12|NameInformation13|NameInformation14|NameInformation15|NameInformation16|NameInformation17|NameInformation18|NameInformation19|NameInformation20|NameInformation21|NameInformation22|NameInformation23|NameInformation24|NameInformation25|NameInformation26|NameInformation27|NameInformation28|NameInformation29|NameInformation30|GUID|SourceSystemOwner|SourceSystemId
MERGE|PersonName||2016/08/27|4712/12/31|PER_909090||US|GLOBAL|||Ashwin Ravi||Ashwin Ravi|||||MR.|||||||||||||||||||||||||||||||||LEGACY|909090_PER_NAME

METADATA|PersonLegislativeData|PersonLegislativeId|EffectiveStartDate|EffectiveEndDate|PersonId(SourceSystemId)|PersonNumber|LegislationCode|HighestEducationLevel|MaritalStatus|MaritalStatusDate|Sex|GUID|SourceSystemOwner|SourceSystemId
MERGE|PersonLegislativeData||2016/08/27|4712/12/31|PER_909090||US||M||M||LEGACY|909090_PER_LEG

METADATA|WorkRelationship|PeriodOfServiceId|LegalEmployerName|PersonId(SourceSystemId)|PersonNumber|DateStart|WorkerType|LegalEmployerSeniorityDate|ActualTerminationDate|LegalEntityId|Comments|EnterpriseSeniorityDate|LastWorkingDate|NotifiedTerminationDate|OnMilitaryServiceFlag|PrimaryFlag|ProjectedTerminationDate|RehireAuthorizerPersonId|RehireAuthorizor|RehireReason|RevokeUserAccess|WorkerNumber|RehireRecommendationFlag|NewStartDate|ActionCode|ReasonCode|DateOfDeath|HideUntilDate|DateForPrimaryFlagChange|CancelWorkRelationshipFlag|TerminateWorkRelationshipFlag|ReverseTerminationFlag|CorrectTerminationFlag|GUID|SourceSystemOwner|SourceSystemId|GlobalTransferFlag|ReadyToConvert|WorkerComments
MERGE|WorkRelationship||US Company|PER_909090|435282|2016/08/27|E||||||||N|Y|||||||||HIRE||||||||||LEGACY|455985_WR||||

METADATA|WorkTerms|AssignmentId|AssignmentNumber|EffectiveEndDate|EffectiveLatestChange|EffectiveSequence|EffectiveStartDate|PeriodOfServiceId(SourceSystemId)|PersonId(SourceSystemId)|PersonNumber|LegalEmployerName|DateStart|WorkerType|AssignmentName|PeopleGroup|AssignmentStatusTypeId|AssignmentStatusTypeCode|AssignmentType|PersonTypeId|PersonTypeCode|SystemPersonType|BargainingUnitCode|BillingTitle|BusinessUnitId|BusinessUnitShortCode|ContractId|DateProbationEnd|WorkerCategory|AssignmentCategory|EstablishmentId|ReportingEstablishment|ExpenseCheckSendToAddress|GradeId|GradeCode|GradeLadderPgmId|GradeLadderPgmName|HourlySalariedCode|InternalBuilding|InternalFloor|InternalLocation|InternalMailstop|InternalOfficeNumber|JobId|JobCode|LabourUnionMemberFlag|LocationId|LocationCode|ManagerFlag|NormalHours|Frequency|NoticePeriod|NoticePeriodUOM|OrganizationId|DepartmentName|PositionId|PositionCode|PositionOverrideFlag|PrimaryWorkTermsFlag|ProbationPeriod|ProbationUnit|ProjectedEndDate|ProjectedStartDate|ProposedWorkerType|ProposedUserPersonType|ReasonCode|RetirementAge|RetirementDate|SpecialCeilingStepId|SpecialCeilingStep|StepEntryDate|TaxAddressId|EndTime|StartTime|VendorSiteId|WorkAtHomeFlag|WorkTermsAssignmentId|VendorId|FreezeStartDate|FreezeUntilDate|ActionCode|CategoryCode|TaxReportingUnitId|TaxReportingUnit|GUID|CollectiveAgreementId|CollectiveAgreementIdCode|FullPartTime|PermanentTemporary|SourceSystemOwner|SourceSystemId
MERGE|WorkTerms||ET435282|2017/07/31|Y|1|2016/08/27|455985_WR|PER_909090|435282|US Company|2016/08/27|E||||ACTIVE_PROCESS|ET||Employee|EMP||||US Company|||||||||||||||||||||||||||||||||Y||||||||||||||||||||||HIRE|||||||||LEGACY|52361_WT
MERGE|WorkTerms||ET435282|2018/08/26|Y|1|2017/08/01|455985_WR|PER_909090|435282|US Company|2016/08/27|E||||ACTIVE_PROCESS|ET||Employee|EMP||||US Company|||||||||||||||||||||||||||||||||Y||||||||||||||||||||||ASG_CHANGE|||||||||LEGACY|52361_WT
MERGE|WorkTerms||ET435282|2019/08/26|Y|1|2018/08/27|455985_WR|PER_909090|435282|US Company|2016/08/27|E||||ACTIVE_PROCESS|ET||Employee|EMP||||US Company|||||||||||||||||||||||||||||||||Y||||||||||||||||||||||CONTRACT_EXTENSION|||||||||LEGACY|52361_WT
MERGE|WorkTerms||ET435282|4712/12/31|Y|1|2019/08/27|455985_WR|PER_909090|435282|US Company|2016/08/27|E||||ACTIVE_PROCESS|ET||Employee|EMP||||US Company|||||||||||||||||||||||||||||||||Y||||||||||||||||||||||CONTRACT_EXTENSION|||||||||LEGACY|52361_WT

METADATA|Assignment|AssignmentId|ActionCode|EffectiveStartDate|EffectiveEndDate|EffectiveSequence|EffectiveLatestChange|WorkTermsAssignmentId(SourceSystemId)|WorkTermsNumber|AssignmentType|AssignmentName|AssignmentNumber|PeopleGroup|AssignmentStatusTypeId|AssignmentStatusTypeCode|BargainingUnitCode|BillingTitle|BusinessUnitId|BusinessUnitShortCode|DateProbationEnd|WorkerCategory|AssignmentCategory|EstablishmentId|ReportingEstablishment|ExpenseCheckSendToAddress|GradeId(SourceSystemId)|GradeCode|GradeLadderPgmId|GradeLadderPgmName|HourlySalariedCode|InternalBuilding|InternalFloor|InternalLocation|InternalMailstop|InternalOfficeNumber|JobId(SourceSystemId)|JobCode|LabourUnionMemberFlag|LocationId|LocationCode|ManagerFlag|NormalHours|Frequency|NoticePeriod|NoticePeriodUOM|OrganizationId(SourceSystemId)|DepartmentName|PeriodOfServiceId(SourceSystemId)|PersonId(SourceSystemId)|PersonNumber|DateStart|WorkerType|LegalEmployerName|PersonTypeId|PersonTypeCode|SystemPersonType|PositionId(SourceSystemId)|PositionCode|PositionOverrideFlag|PrimaryAssignmentFlag|PrimaryFlag|ProbationPeriod|ProbationUnit|ProjectTitle|ProjectedEndDate|ProjectedStartDate|ProposedWorkerType|ProposedUserPersonType|ReasonCode|RetirementAge|RetirementDate|SpecialCeilingStepId|SpecialCeilingStep|TaxAddressId|EndTime|StartTime|VendorSiteId|WorkAtHomeFlag|VendorId|FreezeStartDate|FreezeUntilDate|CategoryCode|CollectiveAgreementId|CollectiveAgreementIdCode|FullPartTime|PermanentTemporary|DefaultExpenseAccount|GUID|SourceSystemOwner|SourceSystemId|GspEligibilityFlag|OvertimePeriodName|SeniorityBasis|UnionId|UnionName
MERGE|Assignment||HIRE|2016/08/27|2017/07/31|1|Y|52361_WT|ET435282|E||E435282|121||ACTIVE_PROCESS||||US Company||||||||103||||||||||413|N||US Company|N|8|D|||LEGACY_2856||455985_WR|PER_909090||2016/08/27|E|US Company||Employee|EMP|LEGACY_227441|||Y|Y||||||||||||||03:30|07:00||N|||||||||||LEGACY|52361_ASSIGN|||||
MERGE|Assignment||ASG_CHANGE|2017/08/01|2018/08/26|1|Y|52361_WT|ET435282|E||E435282|122||ACTIVE_PROCESS||||US Company||||||||103||||||||||413|N||US Company|N|8|D|||LEGACY_2856||455985_WR|PER_909090||2016/08/27|E|US Company||Employee|EMP|LEGACY_227441|||Y|Y||||||||||||||03:30|07:00||N|||||||||||LEGACY|52361_ASSIGN|||||
MERGE|Assignment||CONTRACT_EXTENSION|2018/08/27|2019/08/26|1|Y|52361_WT|ET435282|E||E435282|122||ACTIVE_PROCESS||||US Company||||||||103||||||||||413|N||US Company|N|8|D|||LEGACY_2856||455985_WR|PER_909090||2016/08/27|E|US Company||Employee|EMP|LEGACY_227441|||Y|Y||||||||||||||03:30|07:00||N|||||||||||LEGACY|52361_ASSIGN|||||
MERGE|Assignment||CONTRACT_EXTENSION|2019/08/27|4712/12/31|1|Y|52361_WT|ET435282|E||E435282|122||ACTIVE_PROCESS||||US Company||||||||103||||||||||413|N||US Company|N|8|D|||LEGACY_2856||455985_WR|PER_909090||2016/08/27|E|US Company||Employee|EMP|LEGACY_227441|||Y|Y||||||||||||||03:30|07:00||N|||||||||||LEGACY|52361_ASSIGN|||||

METADATA|Contract|ContractId|EffectiveStartDate|EffectiveEndDate|AssignmentId|AssignmentNumber|PersonNumber|ContractType|Description|Duration|DurationUnits|ExtensionPeriod|ExtensionPeriodUnits|ExtensionReason|PersonId(SourceSystemId)|GUID|SourceSystemOwner|SourceSystemId
MERGE|Contract||2016/08/27|2018/08/26||ET435282|435282|Flexiable 2 Years|Rolling Contract|2|Y||||PER_909090||LEGACY|52361_CONTRACT
MERGE|Contract||2018/08/27|2019/08/26||ET435282|435282|Flexiable 2 Years|Rolling Contract|||1|Y||PER_909090||LEGACY|52361_CONTRACT
MERGE|Contract||2019/08/27|4712/12/31||ET435282|435282|Flexiable 2 Years|Rolling Contract|||1|Y||PER_909090||LEGACY|52361_CONTRACT

Version Tested – 20B

Mandeep Gupta 0 Comments

Feb 5 2021

HDL – Query to find sectionId for Talent Profile Item load

With enhanced profiles (v2), sectionId is a required attribute while loading the Profile Items to a worker’s profile. Section Id can be found using below query:

select SECTION_ID

 from HRT_PROFILE_TYP_SECTIONS

WHERE CONTENT_TYPE_ID=110

AND SECTION_CONTEXT LIKE 'PERSON%'

select * 
  from HRT_PROFILE_SECTIONS_b
 where content_type_id=125
 order by creation_date desc

Mandeep Gupta 0 Comments

Jan 6 2021

HDL – Updating an attribute on Assignment

Technical consultants encounter many scenario’s where they have to update one or two attributes on the assignment data post initial data load. This can happen during any phase of the new implementation or post go-live. For example, there is a need to update the assignment category for a set of workers. In such scenario’s, the approach is to extract the already uploaded assignment data with minimum required attributes and append the attributes for assignment category and upload it back to Fusion using HDL.

The key thing here is to extract the minimum required data for Assignment and WorkTerms. The mandatory fields required for an update using HDL can be checked from:

My Client Groups -> Data Exchange -> View Business Objects -> Worker -> WorkTerms -> Assignment

Validated Version :- 20D

Sample Query to extract the minimum required attributes for Assignments and Workterms in order to update assignment category (AssignmentCategory):

SELECT HDL_DATA
FROM (
SELECT 'METADATA'
|| CHR (124)
|| 'WorkTerms'
|| CHR (124)
|| 'AssignmentId'
|| CHR (124)
|| 'PeriodOfServiceId'
|| CHR (124)
|| 'EffectiveLatestChange'
|| CHR (124)
|| 'EffectiveSequence'
|| CHR (124)
|| 'EffectiveStartDate'
|| CHR (124)
|| 'EffectiveEndDate'
|| CHR (124)
|| 'SourceSystemId'
|| CHR (124)
|| 'SourceSystemOwner'
|| CHR (124)
|| 'ActionCode'
HDL_DATA
, 1 SEQUENCE
FROM DUAL
UNION ALL
select 'MERGE'
|| CHR (124)
|| 'WorkTerms'
|| CHR (124)
|| paam.assignment_id
|| CHR (124)
|| paam.period_of_service_id
|| CHR (124)
|| paam.effective_latest_change
|| CHR (124)
|| paam.effective_sequence
|| CHR (124)
|| to_char(paam.effective_start_date, 'YYYY/MM/DD')
|| CHR (124)
|| to_char(paam.effective_end_date, 'YYYY/MM/DD')
|| CHR (124)
|| hikm.source_system_id
|| CHR (124)
|| hikm.source_system_owner
|| CHR (124)
|| paam.action_code HDL_DATA,
2 SEQUENCE
FROM per_all_assignments_m paam
,hrc_integration_key_map hikm
WHERE 1 = 1
AND paam.assignment_type IN ('ET','CT','PT')
AND hikm.surrogate_id = paam.assignment_id
UNION ALL
SELECT 'METADATA'
|| CHR (124)
|| 'Assignment'
|| CHR (124)
|| 'AssignmentId'
|| CHR (124)
|| 'WorkTermsAssignmentId'
|| CHR (124)
|| 'EffectiveLatestChange'
|| CHR (124)
|| 'EffectiveSequence'
|| CHR (124)
|| 'EffectiveStartDate'
|| CHR (124)
|| 'EffectiveEndDate'
|| CHR (124)
|| 'SourceSystemId'
|| CHR (124)
|| 'SourceSystemOwner'
|| CHR (124)
|| 'ActionCode'
|| CHR (124)
|| 'AssignmentCategory' HDL_DATA
, 3 SEQUENCE
FROM DUAL
UNION ALL
SELECT 'MERGE'
|| CHR (124)
|| 'Assignment'
|| CHR (124)
|| paam.assignment_id
|| CHR (124)
|| paam.work_terms_assignment_id
|| CHR (124)
|| paam.effective_latest_change
|| CHR (124)
|| paam.effective_sequence
|| CHR (124)
|| to_char(paam.effective_start_date, 'YYYY/MM/DD')
|| CHR (124)
|| to_char(paam.effective_end_date, 'YYYY/MM/DD')
|| CHR (124)
|| hikm.source_system_id
|| CHR (124)
|| hikm.source_system_owner
|| CHR (124)
|| paam.action_code
|| CHR (124)
|| 'FR' HDL_DATA
,4 SEQUENCE
FROM per_all_assignments_m paam
,hrc_integration_key_map hikm
WHERE 1 = 1
AND paam.assignment_type IN ('E','C','P')
AND hikm.surrogate_id = paam.assignment_id
)
ORDER BY SEQUENCE

You can modify the above query to include any attributes which you want to update. For example, sample file to update Notice Period will look like:

METADATA|WorkTerms|AssignmentId|PeriodOfServiceId|EffectiveLatestChange|EffectiveSequence|EffectiveStartDate|EffectiveEndDate|SourceSystemId|SourceSystemOwner|ActionCode
MERGE|WorkTerms|300000066966135|300000066966134|Y|1|2002/06/16|4712/12/31|300000066966135|FUSION|HIRE

METADATA|Assignment|AssignmentId|WorkTermsAssignmentId|EffectiveLatestChange|EffectiveSequence|EffectiveStartDate|EffectiveEndDate|SourceSystemId|SourceSystemOwner|ActionCode|NoticePeriod|NoticePeriodUOM
MERGE|Assignment|300000066966140|300000066966135|Y|1|2002/06/16|4712/12/31|300000066966140|FUSION|HIRE|3|M

Sample RTF File:

ASG_UPD_HDL-2 Download

Mandeep Gupta 0 Comments

Jan 2 2021

HDL – Sample HDL to load GL information at Department level

Below is the sample HDL to load the GL Costing information at department level using Organization.dat file. It is always advisable to get the latest METADATA attributes. Check the below link to see the process of Refreshing the HDL objects:

HDL – Refresh Business Objects

Once the latest .dat file is available, check the DFF attributes from attribute window in View business objects.

Sample HDL:

Save this as Organization.dat, zip the file and upload it.

Please Note:-

The GL Accounting should have been setup by financials in Fusion.
recordIdentifier(PER_ORGANIZATION_INFORMATION_EFF=PER_GL_COST_CENTER_INFO) : This is a unique value. It can be anything.
_COMPANY_VALUESET(PER_ORGANIZATION_INFORMATION_EFF=PER_GL_COST_CENTER_INFO) : Pass the value set Id attached to the segment.
_COMPANY(PER_ORGANIZATION_INFORMATION_EFF=PER_GL_COST_CENTER_INFO) : Pass the actual value under this attribute.

Check the below post to extract the loaded data from HCM Cloud:

https://fusionhcmconsulting.com/2021/10/bip-query-to-extract-gl-cost-details-from-department/

You can run below query to find value set ID:

select *
  from FND_VS_VALUE_SETS
 where value_set_code = 'XXX_COMPANY'

Mandeep Gupta 1 Comments

Jan 2 2021

HDL – Refresh Business Objects

Refresh Business objects is a process which is used to create the HDL template for a business object with the latest supported attributes.

When to run ‘Refresh Business Objects’ process:

It is advised to run this process:

After every upgrade
After any new DFF/EFF attributes are configured for any HDL supported business objects.

How to run ‘Refresh Business Objects’ process:

To run ‘Refresh Business Objects’ process, navigate to My Client Groups -> Data Exchange -> View Business Objects

On the View Business Objects UI, you will see 2 buttons:

a. Refresh Object – This is used when you want to refresh only one object at a time. This is usually run when any new DFF attributes are setup for a particular object. Once you click on ‘Refresh Object’, you will see the below screen:

Click on Submit button to submit the process.

You can then click on ‘View Process Results’ button to check the progress:

Once the process completes, you can see the last refreshed date:

b. Refresh All Objects – This is used when you want to refresh all the business objects. This is usually run post release upgrade.

Mandeep Gupta 0 Comments

MSPL

MSPL

HDL – Sample File to DELETE documents of Record

HDL – Update Goal Weight

HDL – Sample File to Load Succession Plan

BIP – Query to get Configure HCM Data Loader parameters

HDL – Updating Work Day Information at Department Level

HDL – Loading CoverArt File at Course Level

HDL – Sample File to Load extended Lookup Codes

HDL – Loading Contract History Using HDL

HDL – Query to find sectionId for Talent Profile Item load

HDL – Updating an attribute on Assignment

HDL – Sample HDL to load GL information at Department level

HDL – Refresh Business Objects

MSPL

MSPL

Tag Archives: HCM Data Loader