Absence Fast Formulas
SELECT FFV.FORMULA_NAME FAST_FORMULA_NAME_GFF
, FFTV.FORMULA_TYPE_NAME TYPE_GFF
, ( SELECT PLDGV.NAME
FROM PER_LEGISLATIVE_DATA_GROUPS_VL PLDGV
WHERE PLDGV.LEGISLATIVE_DATA_GROUP_ID = FFV.LEGISLATIVE_DATA_GROUP_ID
) LEGISLATIVE_DATA_GROUP_GFF
, CASE WHEN SYSDATE BETWEEN FFV.EFFECTIVE_START_DATE AND FFV.EFFECTIVE_END_DATE
THEN 'Yes'
ELSE 'No'
END EFFECTIVE_AS_OF_DATE_GFF
, TO_CHAR( FFV.EFFECTIVE_START_DATE,'MM/DD/RR' ) EFFECTIVE_START_DATE_GFF
, DECODE( TO_CHAR( FFV.EFFECTIVE_END_DATE ,'MM/DD/RRRR' )
, '12/31/4712', NULL
, TO_CHAR( FFV.EFFECTIVE_END_DATE ,'MM/DD/RR' )
) EFFECTIVE_END_DATE_GFF
, FFV.DESCRIPTION DESCRIPTION_GFF
, DECODE(FCI.STATUS,'V','Yes','No') COMPILE_STATUS_GFF
FROM FF_FORMULAS_VL FFV
, FF_FORMULA_TYPES_VL FFTV
, FF_COMPILED_INFO FCI
WHERE FFV.FORMULA_TYPE_ID = FFTV.FORMULA_TYPE_ID
AND FFV.FORMULA_ID = FCI.FORMULA_ID(+)
AND (UPPER(FFTV.FORMULA_TYPE_NAME) LIKE UPPER('%Absence%')
OR UPPER(FFTV.FORMULA_TYPE_NAME) = UPPER('Participation and Rate Eligibility'))
AND :P_AS_OF_DATE BETWEEN FFV.EFFECTIVE_START_DATE AND FFV.EFFECTIVE_END_DATE
AND (
(:P_CREATION_TYPE= 'Custom' and FFV.created_by <> 'SEED_DATA_FROM_APPLICATION' )
OR (:P_CREATION_TYPE = 'Seeded' and FFV.created_by = 'SEED_DATA_FROM_APPLICATION' )
OR (:P_CREATION_TYPE = 'Seeded Modified' and FFV.created_by = 'SEED_DATA_FROM_APPLICATION' and FFV. last_updated_by <> 'SEED_DATA_FROM_APPLICATION')
OR (:P_CREATION_TYPE = 'ALL')
)