You are currently viewing SQL Query to extract Absence Plan Configuration
SQL Query to extract Absence Plan Configuration
- SQL Query to extract Absence Plan Configuration
Total Visits: 7

Absence Plan Configuration Query:

SELECT abvl.name AbsencePlan
	,decode(abvl.PLAN_PERIOD_TYPE, 'C', 'Calendar', 'RB', 'Rolling backward', abvl.PLAN_PERIOD_TYPE) PlanPeriodType
	,decode(abvl.PLAN_UOM, 'H', 'Hours', abvl.PLAN_UOM) PlanUnitOfMeasure
	,decode(abvl.ACC_DEFINITION_TYPE, 'M', 'Matrix', 'F', 'Formula', abvl.ACC_DEFINITION_TYPE) AccrualDefinitionType
	,aatv.name AbsenceType
	,DECODE(ABVL.ENTL_METHOD_CD, 'A', 'Accrual', 'N', 'No Entitlement', 'Q', 'Qualification', ABVL.ENTL_METHOD_CD) EntitlementMethod
	,ABVL.CARRY_OVER_FLAT_AMT
	,decode(ABVL.ENROLLMENT_START_RULE, 'F', 'Formula', 'E', 'As Of Event', 'ASD', 'Absence start date', ABVL.ENROLLMENT_START_RULE) EnrollmentStartRule
	,decode(ABVL.ENTL_DEFINITION_TYPE, 'M', 'Matrix', 'F', 'Formula', ABVL.ENTL_DEFINITION_TYPE) EntitlementType
	,FFV.FORMULA_NAME EnrollmentFormulaName
	,FFTV.FORMULA_TYPE_NAME EnrollmentFormulaType
	,FFV1.FORMULA_NAME CarryOverFormulaName
	,FFTV1.FORMULA_TYPE_NAME CarryOverFormulaType
	,FFV2.FORMULA_NAME PartialAccrualFormulaName
	,FFTV2.FORMULA_TYPE_NAME PartialAccrualFormulaType
	,ABVL.CALENDAR_START_MONTH StartMonth
	,ABVL.CALENDAR_START_DAY StartDay
	,decode(AAPF.ACCRUAL_METHOD_CD, 'F', 'Front-loaded', 'I', 'Incremental', AAPF.ACCRUAL_METHOD_CD) AccrualMethod
	,BEP.NAME EligibleProfileName
	,BEP.PROFILE_TYPE EligibleProfileType
FROM ANC_ABSENCE_PLANS_VL ABVL
	,ANC_ABSENCE_TYPE_PLANS_F ATPF
	,ANC_ABSENCE_TYPES_VL AATV
	,FF_FORMULAS_VL FFV
	,FF_FORMULAS_VL FFV1
	,FF_FORMULAS_VL FFV2
	,FF_FORMULA_TYPES_VL FFTV
	,FF_FORMULA_TYPES_VL FFTV1
	,FF_FORMULA_TYPES_VL FFTV2
	,ANC_ABSENCE_PLANS_F AAPF
	,ANC_PLAN_ELIG_PROFILES_F ELIG
	,BEN_ELIGY_PRFL BEP
WHERE ABVL.absence_plan_id = atpf.absence_plan_id
	AND aatv.absence_type_id = atpf.absence_type_id
	AND TRUNC(SYSDATE) BETWEEN ABVL.EFFECTIVE_START_DATE
		AND ABVL.EFFECTIVE_END_DATE
	AND TRUNC(SYSDATE) BETWEEN ATPF.EFFECTIVE_START_DATE
		AND ATPF.EFFECTIVE_END_DATE
	AND TRUNC(SYSDATE) BETWEEN FFV.EFFECTIVE_START_DATE(+)
		AND FFV.EFFECTIVE_END_DATE(+)
	AND TRUNC(SYSDATE) BETWEEN FFV1.EFFECTIVE_START_DATE(+)
		AND FFV1.EFFECTIVE_END_DATE(+)
	AND TRUNC(SYSDATE) BETWEEN FFV1.EFFECTIVE_START_DATE(+)
		AND FFV1.EFFECTIVE_END_DATE(+)
	AND TRUNC(SYSDATE) BETWEEN ELIG.EFFECTIVE_START_DATE
		AND ELIG.EFFECTIVE_END_DATE
	AND abvl.plan_status = 'A'
	AND FFV.FORMULA_ID(+) = ABVL.ENROLLMENT_START_FORMULA_ID
	AND FFV.FORMULA_TYPE_ID = FFTV.FORMULA_TYPE_ID(+)
	AND FFV1.FORMULA_TYPE_ID = FFTV1.FORMULA_TYPE_ID(+)
	AND FFV1.FORMULA_ID(+) = ABVL.CARRY_OVER_FORMULA_ID
	AND FFV2.FORMULA_TYPE_ID = FFTV2.FORMULA_TYPE_ID(+)
	AND FFV2.FORMULA_ID(+) = AAPF.PARTIAL_ACCRUAL_FORMULA_ID
	AND ATPF.ABSENCE_PLAN_ID = AAPF.ABSENCE_PLAN_ID
	AND ELIG.ABSENCE_PLAN_ID = AAPF.ABSENCE_PLAN_ID
	AND BEP.ELIGY_PRFL_ID(+) = ELIG.BEN_ELIG_PROFILE_ID
ORDER BY aatv.name