In this query, we will try to fetch the Absence Plan Configuration like Plan Name, Plan Type, Entitlement Method, Carryover limit, Enrollment Type, Enrollment Rule Fast Formula, Accrual Method, Eligibility Profile Type and Eligibility Profile Name.
This would be the one query that would be required to check all the absence plan details at one place.
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.nameSample Data:
Contd. image
Contd. image
