BIP – Query to extract element eligibility details

SELECT fff.formula_id

   ,fff.formula_type_id

   ,fff.formula_name

   ,fff.creation_date

   ,fftt.formula_type_name

   ,petft.base_element_name "result rules element_name"

 ,pfrrf.element_type_id "result rules element_id"

   ,pfrrf.result_name

   ,pfrrf.result_rule_type

 ,petft_spr.base_element_name "status proc rules element_name"

   ,psprf.element_type_id "status proc rules element_id"

   ,pivf.base_name

 FROM PAY_FORMULA_RESULT_RULES_F pfrrf

   ,PAY_STATUS_PROC_RULES_F psprf

   ,FF_FORMULAS_F fff

   ,FF_FORMULA_TYPES_TL fftt

   ,PAY_ELEMENT_TYPES_F petft

   ,PAY_ELEMENT_TYPES_F petft_spr

,PAY_INPUT_VALUES_F pivf

WHERE pfrrf.STATUS_PROCESSING_RULE_ID = psprf.STATUS_PROCESSING_RULE_ID

 AND fff.FORMULA_TYPE_ID = fftt.FORMULA_TYPE_ID

 AND fftt.LANGUAGE = 'US'

 AND fff.FORMULA_ID = psprf.FORMULA_ID  

 AND petft_spr.ELEMENT_TYPE_ID = psprf.ELEMENT_TYPE_ID

 AND petft.ELEMENT_TYPE_ID = pfrrf.ELEMENT_TYPE_ID

 AND petft.ELEMENT_TYPE_ID = pivf.ELEMENT_TYPE_ID

 AND pfrrf.INPUT_VALUE_ID = pivf.INPUT_VALUE_ID

 AND TRUNC(SYSDATE) BETWEEN pfrrf.EFFECTIVE_START_DATE AND pfrrf.EFFECTIVE_END_DATE

 AND TRUNC(SYSDATE) BETWEEN psprf.EFFECTIVE_START_DATE AND psprf.EFFECTIVE_END_DATE

 AND TRUNC(SYSDATE) BETWEEN fff.EFFECTIVE_START_DATE AND fff.EFFECTIVE_END_DATE

 AND TRUNC(SYSDATE) BETWEEN petft.EFFECTIVE_START_DATE AND petft.EFFECTIVE_END_DATE

 AND TRUNC(SYSDATE) BETWEEN petft_spr.EFFECTIVE_START_DATE AND petft_spr.EFFECTIVE_END_DATE

 AND TRUNC(SYSDATE) BETWEEN pivf.EFFECTIVE_START_DATE AND pivf.EFFECTIVE_END_DATE

 --AND RESULT_RULE_TYPE = 'I'

 AND fff.formula_name = 'BASIC SALARY UK EARNINGS'