VIEW: APPS.HRI_MB_CMPTNC_PERASG_V

Source

View Text - Preformatted

SELECT 1 row_indicator , rat.step_value prof_level_value , CASE WHEN asg.assignment_type = 'E' THEN 1 ELSE 0 END emp_indicator , CASE WHEN asg.assignment_type = 'C' THEN 1 ELSE 0 END cwk_indicator , CASE WHEN asg.assignment_type = 'A' THEN 1 ELSE 0 END apl_indicator , GREATEST (NVL(cper.effective_date_from,hr_general.start_of_time) ,asg.effective_start_date) effective_start_date , LEAST (NVL(cper.effective_date_to,hr_general.end_of_time) ,asg.effective_end_date) effective_end_date , cper.effective_date_from prof_start_date , NVL(cper.effective_date_to,hr_general.end_of_time) prof_end_date , asg.person_id person_id , asg.assignment_id assignment_id , asg.business_group_id business_group_id , asg.organization_id organization_id , NVL(asg.supervisor_id,-1) supervisor_person_id , NVL(asg.location_id,-1) location_id , NVL(asg.job_id,-1) job_id , NVL(asg.grade_id,-1) grade_id , NVL(asg.position_id,-1) position_id , asg.primary_flag primary_flag_code , asg.assignment_type assignment_type_code , ast.assignment_status_type_id assignment_status_type_id , ast.per_system_status per_system_status_code , ast.pay_system_status pay_system_status_code , NVL(cper.competence_id,-1) competence_id , NVL(cper.proficiency_level_id,-1) prof_level_id , GREATEST(NVL(cper.last_update_date,hr_general.start_of_time) ,NVL(rat.last_update_date,hr_general.start_of_time) ,asg.last_update_date) last_change_date , CASE WHEN ( HR_SECURITY.VIEW_ALL = 'Y' OR HR_SECURITY.SHOW_RECORD('PER_ALL_ASSIGNMENTS_F' , asg.assignment_id , asg.person_id , asg.assignment_status_type_id ) = 'TRUE' ) THEN 'Y' ELSE 'N' END hr_security_flag_code , CASE WHEN asg.business_group_id = NVL(hr_bis.get_sec_profile_bg_id,asg.business_group_id) THEN 'Y' ELSE 'N' END bg_security_flag_code FROM per_competence_elements cper , per_rating_levels rat , per_all_assignments_f asg , per_assignment_status_types ast WHERE cper.type(+) = 'PERSONAL' AND asg.assignment_type IN ('A','E','C') AND ast.per_system_status <> 'TERM_ASSIGN' AND cper.proficiency_level_id = rat.rating_level_id (+) AND cper.person_id(+) = asg.person_id AND asg.assignment_status_type_id = ast.assignment_status_type_id AND ( cper.effective_date_from IS NULL OR asg.EFFECTIVE_START_DATE BETWEEN cper.EFFECTIVE_DATE_FROM AND NVL(cper.EFFECTIVE_DATE_TO,hr_general.end_of_time) OR cper.effective_date_from BETWEEN asg.effective_start_date AND asg.effective_end_date )

View Text - HTML Formatted

SELECT 1 ROW_INDICATOR
, RAT.STEP_VALUE PROF_LEVEL_VALUE
, CASE WHEN ASG.ASSIGNMENT_TYPE = 'E' THEN 1 ELSE 0 END EMP_INDICATOR
, CASE WHEN ASG.ASSIGNMENT_TYPE = 'C' THEN 1 ELSE 0 END CWK_INDICATOR
, CASE WHEN ASG.ASSIGNMENT_TYPE = 'A' THEN 1 ELSE 0 END APL_INDICATOR
, GREATEST (NVL(CPER.EFFECTIVE_DATE_FROM
, HR_GENERAL.START_OF_TIME)
, ASG.EFFECTIVE_START_DATE) EFFECTIVE_START_DATE
, LEAST (NVL(CPER.EFFECTIVE_DATE_TO
, HR_GENERAL.END_OF_TIME)
, ASG.EFFECTIVE_END_DATE) EFFECTIVE_END_DATE
, CPER.EFFECTIVE_DATE_FROM PROF_START_DATE
, NVL(CPER.EFFECTIVE_DATE_TO
, HR_GENERAL.END_OF_TIME) PROF_END_DATE
, ASG.PERSON_ID PERSON_ID
, ASG.ASSIGNMENT_ID ASSIGNMENT_ID
, ASG.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, ASG.ORGANIZATION_ID ORGANIZATION_ID
, NVL(ASG.SUPERVISOR_ID
, -1) SUPERVISOR_PERSON_ID
, NVL(ASG.LOCATION_ID
, -1) LOCATION_ID
, NVL(ASG.JOB_ID
, -1) JOB_ID
, NVL(ASG.GRADE_ID
, -1) GRADE_ID
, NVL(ASG.POSITION_ID
, -1) POSITION_ID
, ASG.PRIMARY_FLAG PRIMARY_FLAG_CODE
, ASG.ASSIGNMENT_TYPE ASSIGNMENT_TYPE_CODE
, AST.ASSIGNMENT_STATUS_TYPE_ID ASSIGNMENT_STATUS_TYPE_ID
, AST.PER_SYSTEM_STATUS PER_SYSTEM_STATUS_CODE
, AST.PAY_SYSTEM_STATUS PAY_SYSTEM_STATUS_CODE
, NVL(CPER.COMPETENCE_ID
, -1) COMPETENCE_ID
, NVL(CPER.PROFICIENCY_LEVEL_ID
, -1) PROF_LEVEL_ID
, GREATEST(NVL(CPER.LAST_UPDATE_DATE
, HR_GENERAL.START_OF_TIME)
, NVL(RAT.LAST_UPDATE_DATE
, HR_GENERAL.START_OF_TIME)
, ASG.LAST_UPDATE_DATE) LAST_CHANGE_DATE
, CASE WHEN ( HR_SECURITY.VIEW_ALL = 'Y' OR HR_SECURITY.SHOW_RECORD('PER_ALL_ASSIGNMENTS_F'
, ASG.ASSIGNMENT_ID
, ASG.PERSON_ID
, ASG.ASSIGNMENT_STATUS_TYPE_ID ) = 'TRUE' ) THEN 'Y' ELSE 'N' END HR_SECURITY_FLAG_CODE
, CASE WHEN ASG.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, ASG.BUSINESS_GROUP_ID) THEN 'Y' ELSE 'N' END BG_SECURITY_FLAG_CODE
FROM PER_COMPETENCE_ELEMENTS CPER
, PER_RATING_LEVELS RAT
, PER_ALL_ASSIGNMENTS_F ASG
, PER_ASSIGNMENT_STATUS_TYPES AST
WHERE CPER.TYPE(+) = 'PERSONAL'
AND ASG.ASSIGNMENT_TYPE IN ('A'
, 'E'
, 'C')
AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
AND CPER.PROFICIENCY_LEVEL_ID = RAT.RATING_LEVEL_ID (+)
AND CPER.PERSON_ID(+) = ASG.PERSON_ID
AND ASG.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
AND ( CPER.EFFECTIVE_DATE_FROM IS NULL OR ASG.EFFECTIVE_START_DATE BETWEEN CPER.EFFECTIVE_DATE_FROM
AND NVL(CPER.EFFECTIVE_DATE_TO
, HR_GENERAL.END_OF_TIME) OR CPER.EFFECTIVE_DATE_FROM BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE )