VIEW: APPS.HRI_MB_UTL_ABSNC_ELMNTS_V

Source

View Text - Preformatted

SELECT 1 row_indicator ,eev.screen_entry_value element_entry_value ,pee.effective_start_date effective_start_date ,pee.effective_end_date effective_end_date ,pee.date_earned date_earned ,asg.business_group_id business_group_id ,asg.person_id person_id ,asg.assignment_id assignment_id ,asg.organization_id organization_id ,NVL(asg.job_id,-1) job_id ,NVL(asg.position_id,-1) position_id ,NVL(asg.grade_id,-1) grade_id ,NVL(asg.location_id,-1) location_id ,NVL(asg.pay_basis_id,-1) pay_basis_id ,NVL(asg.payroll_id,-1) payroll_id ,pee.element_entry_id element_entry_id ,eev.element_entry_value_id element_entry_value_id ,eev.input_value_id input_value_id ,pee.entry_type element_entry_type_code ,piv.uom measure_unit_type_code ,pee.creation_date creation_date ,pee.last_update_date last_update_date ,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 pay_element_entries_f pee ,pay_input_values_f piv ,pay_element_entry_values_f eev ,per_assignments_f asg ,per_assignment_status_types ast WHERE piv.input_value_id = eev.input_value_id AND eev.element_entry_id = pee.element_entry_id AND pee.assignment_id = asg.assignment_id AND asg.assignment_status_type_id = ast.assignment_status_type_id AND pee.effective_start_date BETWEEN piv.effective_start_date AND piv.effective_end_date AND pee.effective_start_date BETWEEN eev.effective_start_date AND eev.effective_end_date AND pee.effective_start_date BETWEEN asg.effective_start_date AND asg.effective_end_date AND asg.assignment_type = 'E' AND ast.per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN') AND piv.input_value_id IN (SELECT abt.input_value_id FROM per_absence_attendance_types abt WHERE abt.input_value_id IS NOT NULL)

View Text - HTML Formatted

SELECT 1 ROW_INDICATOR
, EEV.SCREEN_ENTRY_VALUE ELEMENT_ENTRY_VALUE
, PEE.EFFECTIVE_START_DATE EFFECTIVE_START_DATE
, PEE.EFFECTIVE_END_DATE EFFECTIVE_END_DATE
, PEE.DATE_EARNED DATE_EARNED
, ASG.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, ASG.PERSON_ID PERSON_ID
, ASG.ASSIGNMENT_ID ASSIGNMENT_ID
, ASG.ORGANIZATION_ID ORGANIZATION_ID
, NVL(ASG.JOB_ID
, -1) JOB_ID
, NVL(ASG.POSITION_ID
, -1) POSITION_ID
, NVL(ASG.GRADE_ID
, -1) GRADE_ID
, NVL(ASG.LOCATION_ID
, -1) LOCATION_ID
, NVL(ASG.PAY_BASIS_ID
, -1) PAY_BASIS_ID
, NVL(ASG.PAYROLL_ID
, -1) PAYROLL_ID
, PEE.ELEMENT_ENTRY_ID ELEMENT_ENTRY_ID
, EEV.ELEMENT_ENTRY_VALUE_ID ELEMENT_ENTRY_VALUE_ID
, EEV.INPUT_VALUE_ID INPUT_VALUE_ID
, PEE.ENTRY_TYPE ELEMENT_ENTRY_TYPE_CODE
, PIV.UOM MEASURE_UNIT_TYPE_CODE
, PEE.CREATION_DATE CREATION_DATE
, PEE.LAST_UPDATE_DATE LAST_UPDATE_DATE
, 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 PAY_ELEMENT_ENTRIES_F PEE
, PAY_INPUT_VALUES_F PIV
, PAY_ELEMENT_ENTRY_VALUES_F EEV
, PER_ASSIGNMENTS_F ASG
, PER_ASSIGNMENT_STATUS_TYPES AST
WHERE PIV.INPUT_VALUE_ID = EEV.INPUT_VALUE_ID
AND EEV.ELEMENT_ENTRY_ID = PEE.ELEMENT_ENTRY_ID
AND PEE.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID
AND ASG.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
AND PEE.EFFECTIVE_START_DATE BETWEEN PIV.EFFECTIVE_START_DATE
AND PIV.EFFECTIVE_END_DATE
AND PEE.EFFECTIVE_START_DATE BETWEEN EEV.EFFECTIVE_START_DATE
AND EEV.EFFECTIVE_END_DATE
AND PEE.EFFECTIVE_START_DATE BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE
AND ASG.ASSIGNMENT_TYPE = 'E'
AND AST.PER_SYSTEM_STATUS IN ('ACTIVE_ASSIGN'
, 'SUSP_ASSIGN')
AND PIV.INPUT_VALUE_ID IN (SELECT ABT.INPUT_VALUE_ID
FROM PER_ABSENCE_ATTENDANCE_TYPES ABT
WHERE ABT.INPUT_VALUE_ID IS NOT NULL)