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