In this article we will look into how to export Jobs from a POD into a HDL file which can then be used to import jobs into another POD.
For Jobs, we can configure the DFF and EFF segments. so for every client, the query will be different based on the flex fields configured and the child information being used (Job Grades, Job Legislative Info, etc).
SQL Query to export Job Info
--Select the list of Jobs which needs to be exported in HDL format
WITH JOBS_LIST AS (
select JOB_ID FROM PER_JOBS
WHERE 1=1
AND JOB_CODE IN ('ABC','BCD','DEF')
)
select DATA from (
SELECT 'METADATA|Job|SourceSystemOwner|SourceSystemId|EffectiveStartDate|EffectiveEndDate|Language|JobCode|SetId|SetCode|Name|JobFamilyId|JobFamilyName|ActiveStatus|FullPartTime|JobFunctionCode|ManagerLevel|MedicalCheckupRequired|RegularTemporary|BenchmarkJobFlag|BenchmarkJobId|BenchmarkJobCode|ProgressionJobId|ProgressionJobCode|ApprovalAuthority|ActionReasonCode|CategoryCode|GUID|FLEX:PER_JOBS_DFF|DFFSeg3(PER_JOBS_DFF=Global Data Elements)|DFFSeg5(PER_JOBS_DFF=Global Data Elements)|DFFSeg3_Display(PER_JOBS_DFF=Global Data Elements)|DFFSeg4(PER_JOBS_DFF=Global Data Elements)|DFFSeg1(PER_JOBS_DFF=Global Data Elements)|DFFSeg1_Display(PER_JOBS_DFF=Global Data Elements)|DFFSeg2(PER_JOBS_DFF=Global Data Elements)' as DATA, 1 seq
FROM
DUAL
UNION
SELECT DISTINCT
('MERGE|Job|' ||
'HRC_SQLLOADER' || '|' || 'JC-'|| JOB.JOB_CODE || '|' ||
TO_CHAR(JOB.effective_start_date,'yyyy/mm/dd') || '|' ||
TO_CHAR(JOB.effective_end_date,'yyyy/mm/dd') || '|' ||
'|' ||
JOB.JOB_CODE || '|' ||
JOB.SET_ID || '|' ||
PJOB.SET_CODE || '|' ||
JOB.NAME || '|' ||
'' || '|' ||
fam.JOB_FAMILY_NAME || '|' ||
job.active_status || '|' ||
job.full_part_time || '|' ||
JFC_LKP.MEANING || '|' ||
ML_LKP.MEANING || '|' ||
job.med_checkup_req || '|' ||
job.regular_temporary || '|' ||
job.BENCHMARK_JOB_FLAG|| '|' ||
job.BENCHMARK_JOB_ID || '|' ||
'|' ||
job.PROGRESSION_JOB_ID || '|' ||
'|' ||
Job.Approval_Authority || '|' ||
res.action_reason_code || '|' ||
Job.ATTRIBUTE_CATEGORY || '|' ||
'|' ||
'Global Data Elements' || '|' ||
job.ATTRIBUTE3 || '|' ||
job.ATTRIBUTE5 || '|' ||
'' || '|' ||
job.ATTRIBUTE4 || '|' ||
job.ATTRIBUTE1 || '|' ||
'' || '|' ||
job.ATTRIBUTE2) as DATA, 2 seq
from per_jobs_f_vl job
,per_job_family_f_vl fam
,FND_SETID_SETS PJOB
,FND_LOOKUP_VALUES JFC_LKP
,FND_LOOKUP_VALUES ML_LKP
,per_action_occurrences ocr
,per_actions_b act
,per_action_reasons_b res
,JOBS_LIST JL
,hrc_integration_key_map hikm
where job.job_family_id = fam.job_family_id(+)
and JOB.SET_ID = PJOB.SET_ID
and PJOB.LANGUAGE = 'US'
and hikm.object_name ='Job'
and hikm.surrogate_id= job.JOB_ID
and job.job_id = jl.job_id
AND JFC_LKP.LOOKUP_TYPE(+)='JOB_FUNCTION_CODE'
AND JFC_LKP.LANGUAGE(+)='US'
AND JFC_LKP.LOOKUP_CODE(+)=JOB.JOB_FUNCTION_CODE
AND ML_LKP.LOOKUP_TYPE(+)='MANAGER_LEVEL'
AND ML_LKP.LANGUAGE (+)='US'
AND ML_LKP.LOOKUP_CODE(+)=JOB.MANAGER_LEVEL
and job.action_occurrence_id = ocr.action_occurrence_id
and ocr.action_id = act.action_id (+)
and ocr.action_reason_id = res.action_reason_id (+)
UNION
select 'METADATA|JobExtraInfo|SourceSystemOwner|SourceSystemId|FLEX:PER_JOBS_EIT_EFF|EFF_CATEGORY_CODE|EffectiveStartDate|ReplaceFirstEffectiveStartDate|JobId(SourceSystemId)|SystemIndicator_Display(PER_JOBS_EIT_EFF=<EFF_CATEGORY>)|curriculumCode(PER_JOBS_EIT_EFF=<EFF_CATEGORY>)' AS DATA, 3 SEQ from dual
UNION
select ('MERGE|JobExtraInfo|HRC_SQLLOADER|JCEIT-' || pjf.job_code || '-' || pjeif.JEI_INFORMATION_CATEGORY || rownum || '|' || pjeif.JEI_INFORMATION_CATEGORY || '|JOB_EIT|' || to_char(pjeif.effective_start_date,'YYYY/MM/DD') || '|Y|' || 'JC-' || pjf.job_code || '|' || pjeif.JEI_INFORMATION1 || '|') as DATA, 4 SEQ
from PER_JOB_EXTRA_INFO_F pjeif,JOBS_LIST JL, per_jobs_f pjf
where pjeif.JEI_INFORMATION_CATEGORY = '<EFF_CATEGORY>'
and pjeif.job_id = jl.job_id
and pjeif.job_id = pjf.job_id
and pjf.effective_start_date = pjeif.effective_start_date
and pjf.effective_end_date = pjeif.effective_end_date
UNION
select ('MERGE|JobExtraInfo|HRC_SQLLOADER|JCEIT-' || pjf.job_code || '-' || pjeif.JEI_INFORMATION_CATEGORY || rownum || '|' || pjeif.JEI_INFORMATION_CATEGORY || '|JOB_EIT|' || to_char(pjeif.effective_start_date,'YYYY/MM/DD') || '|Y|' || 'JC-' || pjf.job_code || '||' || pjeif.JEI_INFORMATION1) as DATA, 4 SEQ
from PER_JOB_EXTRA_INFO_F pjeif,JOBS_LIST JL, per_jobs_f pjf
where pjeif.JEI_INFORMATION_CATEGORY = '<EFF_CATEGORY>'
and pjeif.job_id = jl.job_id
and pjeif.job_id = pjf.job_id
and pjf.effective_start_date = pjeif.effective_start_date
and pjf.effective_end_date = pjeif.effective_end_date
UNION
select 'METADATA|JobLegislative|SourceSystemOwner|SourceSystemId|FLEX:PER_JOBS_LEG_EFF|LegislationCode|EFF_CATEGORY_CODE|InformationCategory|_FLSA_STATUS(PER_JOBS_LEG_EFF=HRX_US_JOBS)|_EEO1_CATEGORY(PER_JOBS_LEG_EFF=HRX_US_JOBS)|EffectiveStartDate|EffectiveEndDate|JobId(SourceSystemId)|SequenceNumber' AS DATA, 5 SEQ from dual
UNION
select
('MERGE|JobLegislative|HRC_SQLLOADER|' || 'JCLEG-' || pjf.job_code || '|' || 'HRX_US_JOBS|US|JOB_LEG|HRX_US_JOBS|'|| pjlf.INFORMATION1 ||'|'||pjlf.INFORMATION2|| '|' ||to_char(pjlf.effective_start_date,'YYYY/MM/DD')||'|'||to_char(pjlf.effective_end_date,'YYYY/MM/DD')||'|'|| 'JC-' || pjf.job_code ||'|'||pjlf.SEQUENCE_NUMBER) as DATA, 6 SEQ
from per_job_leg_f pjlf, per_jobs_f pjf,FND_SETID_SETS PJOB,JOBS_LIST JL
where pjlf.LEGISLATION_CODE = 'US'
and pjlf.job_id = pjf.job_id
and pjf.SET_ID = PJOB.SET_ID
and PJOB.LANGUAGE = 'US'
and JL.job_id = pjf.job_id
and pjf.effective_start_date = pjlf.effective_start_date
and pjf.effective_end_date = pjlf.effective_end_date
UNION
SELECT 'METADATA|JobGrade|SourceSystemOwner|SourceSystemId|EffectiveStartDate|JobId(SourceSystemId)|GradeCode' as DATA, 7 seq FROM DUAL
UNION
SELECT
('MERGE|JobGrade|HRC_SQLLOADER|' || 'JCGRD-' || JOB.job_code || '|' || TO_CHAR(JOB.EFFECTIVE_START_DATE,'YYYY/MM/DD') || '|' || 'JC-' || JOB.job_code || '|' || PGF.GRADE_CODE) as DATA, 8 SEQ
FROM
PER_JOBS_F_VL JOB,
PER_VALID_GRADES_F PVGF,
PER_GRADES_F PGF,
FND_SETID_SETS ST,JOBS_LIST JL,hrc_integration_key_map hikm,hrc_integration_key_map hikm_vg
WHERE ST.SET_ID = JOB.SET_ID
AND ST.LANGUAGE = 'US'
and hikm.object_name ='Job'
and hikm.surrogate_id= JOB.JOB_ID
and hikm_vg.object_name ='ValidGrade'
and hikm_vg.surrogate_id= PVGF.VALID_GRADE_ID
AND SYSDATE BETWEEN JOB.EFFECTIVE_START_DATE AND JOB.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN PGF.EFFECTIVE_START_DATE AND PGF.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN PVGF.EFFECTIVE_START_DATE AND PVGF.EFFECTIVE_END_DATE
AND PVGF.JOB_ID = JOB.JOB_ID
AND PGF.GRADE_ID = PVGF.GRADE_ID
and JOB.job_id = jl.job_id
)
order by seq
The above query cannot be used as-is as it has some info related to DFF and EFF segments. The query can be modified as per the client and can be used to export Jobs into HDL format.
Once we get the output HDL, save it as Job.dat, zip it and import it into target POD.
Hope this helps when you have to export/migrate Jobs into another POD.
If you like the content, please follow us on LinkedIn, Facebook, and Twitter to get updated with the latest content.
