Absence Types
SELECT b.name absence_type_name
, HR_GENERAL.DECODE_LOOKUP('ANC_ABSENCE_REGIONS',c.ABSENCE_REGION_CD) section
, HR_GENERAL.DECODE_LOOKUP(c.EMP_LOOKUP_TYPE,c.EMP_USAGE_CD) employees
, HR_GENERAL.DECODE_LOOKUP(c.MGR_LOOKUP_TYPE,c.MGR_USAGE_CD) managers
, HR_GENERAL.DECODE_LOOKUP(c.ADMIN_LOOKUP_TYPE,c.ADMIN_USAGE_CD) administrators
, HR_GENERAL.DECODE_LOOKUP('ANC_ABSENCE_USAGES',h.ABSENCE_USAGE_CD) usage
, HR_GENERAL.DECODE_LOOKUP('ANC_ABS_USAGE_TYPES',h.ABSENCE_FIELD_TYPE) Usage_Type
, HR_GENERAL.DECODE_LOOKUP(h.EMP_LOOKUP_TYPE,h.EMP_USAGE_CD)Employee_Self_Service
, HR_GENERAL.DECODE_LOOKUP(h.MGR_LOOKUP_TYPE,h.MGR_USAGE_CD)Manager_Self_Service
, HR_GENERAL.DECODE_LOOKUP(h.ADMIN_LOOKUP_TYPE,h.ADMIN_USAGE_CD) Administrative_Transaction
FROM ANC_ABSENCE_TYPES_F_TL b
, ANC_ABSENCE_TYPE_REGIONS_F c
, ANC_ABS_TYPE_RGN_USAGES_F h
WHERE :P_AS_OF_DATE BETWEEN b.EFFECTIVE_START_DATE AND b.EFFECTIVE_END_DATE
AND b.absence_type_id = c.absence_Type_id
AND b.language = 'US'
AND :P_AS_OF_DATE BETWEEN c.EFFECTIVE_START_DATE AND c.EFFECTIVE_END_DATE
AND c.absence_type_region_id = h.absence_type_region_id(+)
AND :P_AS_OF_DATE BETWEEN h.EFFECTIVE_START_DATE(+) AND h.EFFECTIVE_END_DATE(+)
AND (
( (:P_CREATION_TYPE= 'Custom' and h.created_by <> 'SEED_DATA_FROM_APPLICATION' )
OR
(:P_CREATION_TYPE = 'Seeded' and h.created_by = 'SEED_DATA_FROM_APPLICATION' )
OR
(:P_CREATION_TYPE = 'Seeded Modified' and h.created_by = 'SEED_DATA_FROM_APPLICATION' and h.last_updated_by <> 'SEED_DATA_FROM_APPLICATION')
OR
(:P_CREATION_TYPE = 'ALL')
)
OR
( (:P_CREATION_TYPE= 'Custom' and c.created_by <> 'SEED_DATA_FROM_APPLICATION' )
OR
(:P_CREATION_TYPE = 'Seeded' and c.created_by = 'SEED_DATA_FROM_APPLICATION' )
OR
(:P_CREATION_TYPE = 'Seeded Modified' and c.created_by = 'SEED_DATA_FROM_APPLICATION' and c.last_updated_by <> 'SEED_DATA_FROM_APPLICATION')
OR
(:P_CREATION_TYPE = 'ALL')
)
)
ORDER BY b.name, HR_GENERAL.DECODE_LOOKUP('ANC_ABSENCE_REGIONS',c.ABSENCE_REGION_CD)
