Use this query in SQL Developer

The Two Ewes Driver enables you to run this query directly against your Oracle Fusion Applications, right from SQL Developer. Give it a try right now with a no obligation trial period.
Contributed By

Two Ewes
Send Email
Absence Derived Factors

ABSENCE

Absence Derived Factors


SELECT ABTV.NAME name_fads
	,HR_GENERAL.DECODE_LOOKUP('ANC_DISP_E_NE_TYPE', ABTR.emp_usage_cd) employees_fads
	,HR_GENERAL.DECODE_LOOKUP('ANC_DISP_E_NE_TYPE', ABTR.mgr_usage_cd) managers_fads
	,HR_GENERAL.DECODE_LOOKUP('ANC_DISP_E_NE_TYPE', ABTR.admin_usage_cd) administrators_fads
	,HR_GENERAL.DECODE_LOOKUP('ANC_ABSENCE_USAGES', ABTU.absence_usage_cd) usage_fads
	,HR_GENERAL.DECODE_LOOKUP('ANC_ABS_USAGE_TYPES', ABTU.absence_field_type) usage_type_fads
	,decode(ABTU.emp_usage_cd, 'ORA_ANC_NOTSPEC', HR_GENERAL.DECODE_LOOKUP('ORA_ANC_ADDL_PAYLOAD', ABTU.emp_usage_cd), 'ENABLE', HR_GENERAL.DECODE_LOOKUP('ANC_DISP_E_NE_TYPE', ABTU.emp_usage_cd), 'NOTENABLED', HR_GENERAL.DECODE_LOOKUP('ANC_DISP_E_NE_TYPE', ABTU.emp_usage_cd), HR_GENERAL.DECODE_LOOKUP('ANC_TYPE_USAGES_STATUS', ABTU.emp_usage_cd)) emp_self_service_fads
	,decode(ABTU.mgr_usage_cd, 'ORA_ANC_NOTSPEC', HR_GENERAL.DECODE_LOOKUP('ORA_ANC_ADDL_PAYLOAD', ABTU.mgr_usage_cd), 'ENABLE', HR_GENERAL.DECODE_LOOKUP('ANC_DISP_E_NE_TYPE', ABTU.mgr_usage_cd), 'NOTENABLED', HR_GENERAL.DECODE_LOOKUP('ANC_DISP_E_NE_TYPE', ABTU.emp_usage_cd), HR_GENERAL.DECODE_LOOKUP('ANC_TYPE_USAGES_STATUS', ABTU.mgr_usage_cd)) mgr_self_service_fads
	,decode(ABTU.admin_usage_cd, 'ORA_ANC_NOTSPEC', HR_GENERAL.DECODE_LOOKUP('ORA_ANC_ADDL_PAYLOAD', ABTU.admin_usage_cd), 'ENABLE', HR_GENERAL.DECODE_LOOKUP('ANC_DISP_E_NE_TYPE', ABTU.admin_usage_cd), 'NOTENABLED', HR_GENERAL.DECODE_LOOKUP('ANC_DISP_E_NE_TYPE', ABTU.admin_usage_cd), HR_GENERAL.DECODE_LOOKUP('ANC_TYPE_USAGES_STATUS', ABTU.admin_usage_cd)) admin_transaction_fads
FROM anc_absence_types_vl ABTV
	,anc_absence_type_regions_f ABTR
	,anc_abs_type_rgn_usages_f ABTU
WHERE ABTV.absence_type_id = ABTR.absence_type_id
	AND ABTR.ABSENCE_TYPE_REGION_ID = ABTU.ABSENCE_TYPE_REGION_ID
	AND ABTV.absence_type_id = NVL(:ABSENCE_TYPE_ID, ABTV.absence_type_id)
	AND :P_AS_OF_DATE BETWEEN ABTV.EFFECTIVE_START_DATE
		AND ABTV.EFFECTIVE_END_DATE
	AND :P_AS_OF_DATE BETWEEN ABTR.EFFECTIVE_START_DATE
		AND ABTR.EFFECTIVE_END_DATE
	AND :P_AS_OF_DATE BETWEEN ABTU.EFFECTIVE_START_DATE
		AND ABTU.EFFECTIVE_END_DATE
AND (
	(:P_CREATION_TYPE= 'Custom' and ABTV.created_by <> 'SEED_DATA_FROM_APPLICATION' )  
OR  (:P_CREATION_TYPE = 'Seeded' and ABTV.created_by =   'SEED_DATA_FROM_APPLICATION' )  
OR  (:P_CREATION_TYPE = 'Seeded Modified' and ABTV.created_by =   'SEED_DATA_FROM_APPLICATION' and ABTV.    last_updated_by  <> 'SEED_DATA_FROM_APPLICATION') 
OR 	(:P_CREATION_TYPE = 'ALL')
)
	/*AND   ((:P_CREATION_TYPE= 'Custom' and ABTR.CREATED_BY <> 'SEED_DATA_FROM_APPLICATION') OR  (:P_CREATION_TYPE = 'Seeded' and ABTR.CREATED_BY = 'SEED_DATA_FROM_APPLICATION'))
AND   ((:P_CREATION_TYPE= 'Custom' and ABTU.CREATED_BY <> 'SEED_DATA_FROM_APPLICATION') OR  (:P_CREATION_TYPE = 'Seeded' and ABTU.CREATED_BY = 'SEED_DATA_FROM_APPLICATION'))*/