HR Analytics Domain Values SQL for BI Apps 7.9.6.4

domainValues_AbsenceEvent_Status_oraVersion.csv

=============================================================

SELECT DISTINCT APPROVAL_STATUS

FROM PER_ABSENCE_ATTENDANCES

 

No Data Returned

 

domainValues_Employment_Cat_oraVersion.csv

=============================================================

SELECT DISTINCT SYSTEM_PERSON_TYPE, USER_PERSON_TYPE

FROM PER_PERSON_TYPES

WHERE SYSTEM_PERSON_TYPE IN (‘EMP’,’CWK’,’OTHER’,’EMP_APL’,’EX_EMP’,’EX_EMP_

APL’,’RETIREE’,’PRTN’)

ORDER BY 1,2

 

No Data Returned

 

domainValues_Employee_Ethnic_Group_Code_oraVersion.csv

============================================================

SELECT DISTINCT PER_INFORMATION1 FROM PER_ALL_PEOPLE_F

WHERE PER_INFORMATION1 in

(‘1′,’2′,’3′,’4′,’5′,’6′,’7′,’8′,’9′,’10’,’11’,’12’,’13’,’BA’,’BC’,’BO’,’C’,’I’

,’O’,’P’,’W’)

ORDER BY 1

 

domainValues_Employment_Full_Time_Flg_oraVersion.csv

============================================================

SELECT DISTINCT EMPLOYMENT_CATEGORY FROM PER_ALL_ASSIGNMENTS_F ORDER BY 1

 

domainValues_Employee_Sex_MF_oraVersion.csv

============================================================

SELECT DISTINCT SEX FROM PER_ALL_PEOPLE_F ORDER BY 1

 

domainValues_Employment_Status_oraVersion.csv

=============================================================

SELECT DISTINCT PER_SYSTEM_STATUS FROM PER_ASSIGNMENT_STATUS_TYPES

ORDER BY 1

 

domainValues_Employee_Veteran_Status_Code_oraVersion.csv

=============================================================

SELECT DISTINCT PER_INFORMATION5 FROM PER_ALL_PEOPLE_F

WHERE PER_INFORMATION_CATEGORY = ‘US’

AND PER_INFORMATION5 IS NOT NULL

ORDER BY 1

 

domainValues_Flsa_oraVersion.csv

=============================================================

SELECT

DISTINCT CASE WHEN JOB_INFORMATION_CATEGORY IN (‘US’,’CA’)

THEN JOB_INFORMATION3 END FLSA_STAT_CODE

FROM PER_JOBS

ORDER BY 1

 

domainValues_HRPosition_Active_Pos_Flg_oraVersion.csv

=============================================================

SELECT DISTINCT STATUS FROM HR_ALL_POSITIONS_F ORDER BY 1

 

domainValues_Pay_Type_Flg_oraVersion.csv

=============================================================

SELECT DISTINCT COSTING_DEBIT_OR_CREDIT FROM PAY_ELEMENT_CLASSIFICATIONS

ORDER BY 1

 

domainValues_Pay_Type_Grp_Code_oraVersion.csv

=============================================================

SELECT DISTINCT CLASSIFICATION_NAME, ELEMENT_NAME

FROM

PAY_ELEMENT_TYPES_F,

PAY_ELEMENT_CLASSIFICATIONS

WHERE

PAY_ELEMENT_CLASSIFICATIONS.CLASSIFICATION_ID = PAY_ELEMENT_TYPES_

F.CLASSIFICATION_ID AND

CLASSIFICATION_NAME NOT LIKE ‘%Information%’ AND

CLASSIFICATION_NAME NOT LIKE ‘%Employer%’ AND

CLASSIFICATION_NAME NOT LIKE ‘%Balance%’

ORDER BY 1, 2

 

 

domainValues_perf_nrml_rating_oraVersion.csv

=============================================================

SELECT perf_rating,rating_level_id,rating_desc,

CASE WHEN max_rating > 0 THEN

ROUND(100 * rnk_rating / max_rating, 0)

END NRMLIZED_RATING,

NULL PERF_BAND_CODE

FROM

(SELECT to_char(prl.step_value) perf_rating,prl.rating_level_id

rating_level_id,

prl.name rating_desc,

prl.step_value rnk_rating,

MAX(prl.step_value) KEEP (DENSE_RANK LAST ORDER BY prl.step_value) OVER

(PARTITION BY prl.rating_scale_id) max_rating

FROM per_rating_levels prl

WHERE prl.rating_scale_id IN

(

SELECT DISTINCT

lvl.rating_scale_id

FROM

per_rating_levels lvl,

per_appraisals appr

WHERE appr.overall_performance_level_id = lvl.rating_level_id)

UNION ALL

SELECT

lookup_code perf_rating,

to_number(null) rating_level_id,

meaning rating_desc,

RANK() OVER (ORDER BY lookup_code) rnk_rating,

SUM(1) OVER () max_rating

FROM hr_lookups

WHERE lookup_type = ‘PERFORMANCE_RATING’

)

ORDER BY 2, 1

 

domainValues_Recruitment_Event_Reason_oraVersion.csv

=============================================================

SELECT TO_CHAR(ASSIGNMENT_STATUS_TYPE_ID) STATUS_CODE, USER_STATUS STATUS_NAME

FROM PER_ASSIGNMENT_STATUS_TYPES

WHERE PER_SYSTEM_STATUS in

(‘ACTIVE_APL’,’INTERVIEW1′,’INTERVIEW2′,’OFFER’,’ACCEPTED’,’TERM_APL’)

 

 

 

1. Identify the assignment statuses in your Oracle source system by using the

following SQL:

SELECT TO_CHAR(ASSIGNMENT_STATUS_TYPE_ID) STATUS_CODE, USER_STATUS STATUS_NAME

FROM PER_ASSIGNMENT_STATUS_TYPES

WHERE PER_SYSTEM_STATUS in

(‘ACTIVE_APL’,’INTERVIEW1′,’INTERVIEW2′,’OFFER’,’ACCEPTED’,’TERM_APL’)

2. Identify the Job requisition statuses in your Oracle Source system by using the

following SQL:

SELECT LOOKUP_CODE, MEANING

FROM HR_STANDARD_LOOKUPS

WHERE LOOKUP_TYPE = ‘VACANCY_STATUS’

3. Identify the Assignment Change Code and Reasons in your Oracle source system

by using the following SQL:

SELECT LOOKUP_CODE, MEANING

FROM HR_STANDARD_LOOKUPS

WHERE LOOKUP_TYPE = ‘APL_ASSIGN_REASON’

4. Identify the Application Termination Code and Reasons in your Oracle source

system by using the following SQL:

SELECT LOOKUP_CODE, MEANING

FROM HR_STANDARD_LOOKUPS

WHERE LOOKUP_TYPE = ‘TERM_APL_REASON’

5. Using a text editor, open the domainValues_Recruitment_Event_Reason_

oraVersion.csv file located in the $PMServer\LkpFiles directory (for example,

INFA_HOME\server\infa_shared\LkpFiles).

6. Copy the assignment status type and name from the results of using the SQL

statement in step 1 to the STATUS_CODE and STATUS_NAME columns in the file

respectively. The data must be copied starting from the sixth line. Use commas to

separate the entries.

7. Copy the lookup code and meaning from the results of using the SQL statement in

step 2 to the STATUS_CODE and STATUS_NAME columns in the file

respectively. Append this data to the data copied in step 6. Use commas to

separate the entries.

8. The Lookup Code and Meaning in step 3 and step 4 are used to populate the

REASON_CODE and REASON_NAME columns in the file. Map the values of

STATUS_CODE and STATUS_NAME to valid values of REASON_CODE and

REASON_NAME columns in the file respectively. Use commas to separate the

entries.

9. To see the STATUS_CODE and REASON_CODE combinations currently used in

your Oracle source system for active application statuses, use the following SQL:

SELECT DISTINCT

STATUS_CODE,

STATUS_NAME,

REASON_CODE,

REASON_NAME

FROM (

SELECT ASG.ASSIGNMENT_STATUS_TYPE_ID STATUS_CODE,

STS.USER_STATUS STATUS_NAME,

ASG.CHANGE_REASON REASON_CODE,

LKP.MEANING REASON_NAME

FROM PER_ALL_ASSIGNMENTS_F ASG,

PER_ASSIGNMENT_STATUS_TYPES STS,

HR_STANDARD_LOOKUPS LKP

WHERE ASG.ASSIGNMENT_STATUS_TYPE_ID = STS.ASSIGNMENT_STATUS_TYPE_ID

AND ASG.CHANGE_REASON = LKP.LOOKUP_CODE(+)

AND LKP.LOOKUP_TYPE(+) = ‘APL_ASSIGN_REASON’

AND ASG.ASSIGNMENT_TYPE = ‘A’

UNION ALL

SELECT ASG.ASSIGNMENT_STATUS_TYPE_ID STATUS_CODE,

STS.USER_STATUS STATUS_NAME,

ASG.STATUS_CHANGE_REASON REASON_CODE,

LKP.MEANING REASON_NAME

FROM IRC_ASSIGNMENT_STATUSES ASG,

PER_ASSIGNMENT_STATUS_TYPES STS,

HR_STANDARD_LOOKUPS LKP

WHERE ASG.ASSIGNMENT_STATUS_TYPE_ID = STS.ASSIGNMENT_STATUS_TYPE_ID

AND ASG.STATUS_CHANGE_REASON = LKP.LOOKUP_CODE(+)

AND LKP.LOOKUP_TYPE(+) = ‘APL_ASSIGN_REASON’

)

10. The REASON_CODE and REASON_NAME for all the job requisition STATUS_

CODE and STATUS_NAME values obtained from step 2 should be ‘Unspecified.’

11. Map all the SOURCE_CODE, SOURCE_NAME, REASON_CODE, REASON_

NAME combinations with the following domain columns:

■ W_EVENT_CODE – Recruitment Event Code

■ W_EVENT_DESC – Recruitment Event Description

■ W_SUB_STAGE_CODE – Recruitment Sub Stage Code

■ W_SUB_STAGE_DESC – Recruitment Sub Stage Description

■ W_STAGE_CODE – Recruitment Stage Code

■ W_STAGE_DESC – Recruitment Stage Description

■ W_REASON_CODE – Recruitment Reason Code

■ W_REASON_DESC – Recruitment Reason Description

■ W_REASON_TYPE_CODE – Recruitment Reason Type Code

■ W_REASON_TYPE_DESC – Recruitment Reason Type Description

■ W_APL_EVENT_FLG – Applicant Event Flag. ‘Y’ for Applicant Events, ‘N’ for

others

■ W_REQ_EVENT_FLG – Job Requisition Event Flag. ‘Y’ for job Requisition

Events, ‘N’ for others

For a list of published domain values, see Oracle Business Analytics Warehouse Data

Model Reference.

12. Save and close the file.

 

domainValues_Recruitment_Source_Type_oraVersion.csv

=============================================================

SELECT

HR_STANDARD_LOOKUPS.LOOKUP_TYPE,

HR_STANDARD_LOOKUPS.LOOKUP_CODE,

HR_STANDARD_LOOKUPS.MEANING

FROM HR_STANDARD_LOOKUPS

WHERE LOOKUP_TYPE = ‘REC_TYPE’

 

domainValues_Requisition_Category_oraVersion.csv

=============================================================

Configuring Oracle Human Resources Analytics 7-25

SELECT

HR_STANDARD_LOOKUPS.LOOKUP_TYPE,

HR_STANDARD_LOOKUPS.LOOKUP_CODE,

HR_STANDARD_LOOKUPS.MEANING

FROM

HR_STANDARD_LOOKUPS

WHERE

LOOKUP_TYPE = ‘VACANCY_CATEGORY’

 

domainValues_Status_Recruitment_oraVersion.csv

=============================================================

SELECT

PER_ASSIGNMENT_STATUS_TYPES.PER_SYSTEM_STATUS AS LOOKUP_CODE,

PER_ASSIGNMENT_STATUS_TYPES.USER_STATUS AS MEANING FROM

PER_ASSIGNMENT_STATUS_TYPES

WHERE

PER_SYSTEM_STATUS in

(‘ACTIVE_APL’,’INTERVIEW1′,’INTERVIEW2′,’OFFER’,’ACCEPTED’,’TERM_APL’)

Group By PER_ASSIGNMENT_STATUS_TYPES.PER_SYSTEM_STATUS,

PER_ASSIGNMENT_STATUS_TYPES.USER_STATUS

 

domainValues_Status_Vacancy_oraVersion.csv

=============================================================

SELECT

HR_STANDARD_LOOKUPS.LOOKUP_TYPE,

HR_STANDARD_LOOKUPS.LOOKUP_CODE,

HR_STANDARD_LOOKUPS.MEANING

FROM

HR_STANDARD_LOOKUPS

WHERE

LOOKUP_TYPE = ‘VACANCY_STATUS’

 

domainValues_Wrkfc_EventType_oraVersion.csv

=============================================================

/* Change the language condition to your implementation language */

SELECT ‘ASG’ EVENT_TYPE

,LOOKUP_CODE EVENT_REASON

,’ANY’ ORG_CHANGE

,’ANY’ JOB_CHANGE

,’ANY’ POS_CHANGE

,’ANY’ GRD_CHANGE

,’ANY’ LOC_CHANGE

,’ANY’ SUP_CHANGE

,’ASG’ W_EVENT_CODE

,’ASSIGNMENT EVENT’ W_EVENT_DESC

,’ASG~OTHER’ W_EVENT_SUBG_CODE

,’ASSIGNMENT CHANGE’ W_EVENT_SUBG_DESC

,’ASG~OTHER’ W_EVENT_GRP_CODE

,’ASSIGNMENT CHANGE’ W_EVENT_GRP_DESC

,’N’ PROMOTION_EVENT_FLG

,’N’ TRANSFER_EVENT_FLG

FROM HR_STANDARD_LOOKUPS

WHERE LOOKUP_TYPE IN (‘EMP_ASSIGN_REASON’, ‘CWK_ASSIGN_REASON’)

UNION ALL

SELECT ‘TERM’ EVENT_TYPE

,LOOKUP_CODE EVENT_REASON

,’N’ ORG_CHANGE

,’N’ JOB_CHANGE

,’N’ POS_CHANGE

,’N’ GRD_CHANGE

,’N’ LOC_CHANGE

,’N’ SUP_CHANGE

,’TERM’ W_EVENT_CODE

,’TERMINATION’ W_EVENT_DESC

,’TERM~VOLUNTARY’ W_EVENT_SUBG_CODE

,’VOLUNTARY TERMINATION’ W_EVENT_SUBG_DESC

,’TERM~VOLUNTARY’ W_EVENT_GRP_CODE

,’VOLUNTARY TERMINATION’ W_EVENT_GRP_DESC

,’N’ PROMOTION_EVENT_FLG

,’N’ TRANSFER_EVENT_FLG

FROM HR_STANDARD_LOOKUPS

WHERE LOOKUP_TYPE IN (‘LEAV_REAS’, ‘HR_CWK_TERMINATION_REASONS’)

 

LearningEnrollment_Status_oraVersion.csv

=============================================================

SELECT DISTINCT

BOOKING_STATUS_TYPE_ID STATUS_CODE,

NAME STATUS_NAME

FROM

OTA_BOOKING_STATUS_TYPES

ORDER BY 1,2

 

domainValues_Wrkfc_Appraisals_oraVersion.csv

=============================================================

SELECT

APPRAISAL_TEMPLATE_ID APPRAISAL_TEMPLATE_ID,

NAME APPRAISAL_TEMPLATE_NAME,

‘Y’ EXCLUDE_FLAG

FROM PER_APPRAISAL_TEMPLATES;

 

file_Normal_Hours_Conversion_Factor_

oraVersion.csv

SELECT LOOKUP_CODE NORMAL_HOURS_FREQ, MEANING

FROM HR_STANDARD_LOOKUPS A

WHERE LOOKUP_TYPE = ‘FREQUENCY’

AND ENABLED_FLAG = ‘Y’;

 

Leave A Reply

Your email address will not be published. Required fields are marked *