User Defined Criteria
SELECT
bec.name UDC_NAME
,bec.short_code UDC_SHORT_CODE
,bec.description UDC_DESCRIPTION
,bec.access_table_name1 UDC_TABLE1
,bec.access_column_name1 UDC_COLUMN1
,bec.col1_lookup_type UDC_LOOKUP1
,( SELECT FFV.FORMULA_NAME
FROM FF_FORMULAS_VL FFV
WHERE FFV.FORMULA_ID = bec.access_calc_rule
AND :P_AS_OF_DATE BETWEEN FFV.EFFECTIVE_START_DATE AND FFV.EFFECTIVE_END_DATE
) ACCESS_FORMULA1
,bec.access_table_name2 UDC_TABLE2
,bec.access_column_name2 UDC_COLUMN2
,bec.col2_lookup_type UDC_LOOKUP2
,( SELECT FFV.FORMULA_NAME
FROM FF_FORMULAS_VL FFV
WHERE FFV.FORMULA_ID = bec.access_calc_rule2
AND :P_AS_OF_DATE BETWEEN FFV.EFFECTIVE_START_DATE AND FFV.EFFECTIVE_END_DATE
) ACCESS_FORMULA2
FROM
BEN_ELIGY_CRITERIA bec
WHERE
(
(:P_CREATION_TYPE= 'Custom' and bec.created_by <> 'SEED_DATA_FROM_APPLICATION' )
OR (:P_CREATION_TYPE = 'Seeded' and bec.created_by = 'SEED_DATA_FROM_APPLICATION' )
OR (:P_CREATION_TYPE = 'Seeded Modified' and bec.created_by = 'SEED_DATA_FROM_APPLICATION' and bec.last_updated_by <> 'SEED_DATA_FROM_APPLICATION')
OR (:P_CREATION_TYPE = 'ALL')
)
ORDER BY bec.name
