Query to fetch all the information provided while creating an element in Fusion HCM

 SELECT CLA.CLASSIFICATION_NAME,
SUBSTR(ET.EFFECTIVE_START_DATE, 1, 10) AS "EFFECTIVE START",
SUBSTR(ET.EFFECTIVE_END_DATE,1, 10) AS "EFFECTIVE END",
ET.BASE_ELEMENT_NAME, 
ET.ELEMENT_NAME,
ET.REPORTING_NAME,
ET.DESCRIPTION,
PLD.NAME AS LEGISLATIVE_DATA_GROUP,
(SELECT DEFINITION_NAME
   FROM PAY_TIME_DEFINITIONS
  WHERE TIME_DEFINITION_ID = ET.STARTING_TIME_DEF_ID) AS EARLIEST_ENTRY_DATE,
(SELECT DEFINITION_NAME
   FROM PAY_TIME_DEFINITIONS
  WHERE TIME_DEFINITION_ID = ET.ENDING_TIME_DEF_ID) AS LATEST_ENTRY_DATE,
ET.INPUT_CURRENCY_CODE,
CASE WHEN ET.USE_AT_REL_LEVEL = 'Y' THEN 'Relationship Level'
     WHEN ET.USE_AT_TERM_LEVEL = 'Y' THEN 'Term Level'
WHEN ET.USE_AT_ASG_LEVEL = 'Y' THEN 'Assignment Level'
ELSE '' END AS EMPLOYMENT_LEVEL,
(SELECT HL.MEANING
   FROM HCM_LOOKUPS HL
  WHERE ET.PROCESSING_TYPE = HL.LOOKUP_CODE
   AND HL.LOOKUP_TYPE = 'PAY_PROCESSING_TYPE') AS PROCESSING_TYPE,
ET.ONCE_EACH_PERIOD_FLAG,
ET.MULTIPLE_ENTRIES_ALLOWED_FLAG,
(SELECT HL1.MEANING
   FROM HCM_LOOKUPS HL1
  WHERE ET.PROCESS_MODE = HL1.LOOKUP_CODE
AND HL1.LOOKUP_TYPE = 'PAY_TMPLT_EARNING_PAY_PERIOD') AS PROCESS_MODE,
(SELECT HLC.MEANING
  FROM PAY_TEMPLATES PT,
       PAY_TMPLT_RULES_VL PTR,
   PAY_TMPLT_RULE_VALUES PTRV,
   HCM_LOOKUPS HLC
 WHERE PT.BASE_NAME = ET.ELEMENT_NAME
   AND PT.TEMPLATE_ID = PTRV.TEMPLATE_ID
   AND PTR.RULE_ID = PTRV.RULE_ID
   AND PTR.RULE_TXT = 'What is the calculation rule?'
   AND PTR.RULE_OPTIONS_VALUE = 'PAY_TMPLT_CALC_RULE'
   AND PTRV.RULE_VALUE = HLC.LOOKUP_CODE
   AND HLC.LOOKUP_TYPE = 'PAY_TMPLT_CALC_RULE') CALCULATION_RULE,
 
 (SELECT HLC.MEANING
  FROM PAY_TEMPLATES PT,
       PAY_TMPLT_RULES_VL PTR,
   PAY_TMPLT_RULE_VALUES PTRV,
   HCM_LOOKUPS HLC
 WHERE PT.BASE_NAME = ET.ELEMENT_NAME
   AND PT.TEMPLATE_ID = PTRV.TEMPLATE_ID
   AND PTR.RULE_ID = PTRV.RULE_ID
   AND PTR.RULE_TXT = 'Is this element subject to proration?'
   AND PTR.RULE_OPTIONS_VALUE = 'PAY_TMPLT_YES_NO'
   AND PTRV.RULE_VALUE = HLC.LOOKUP_CODE
   AND HLC.LOOKUP_TYPE = 'PAY_TMPLT_YES_NO') AS PRORATION,
   
   (SELECT HLC.MEANING
  FROM PAY_TEMPLATES PT,
       PAY_TMPLT_RULES_VL PTR,
   PAY_TMPLT_RULE_VALUES PTRV,
   HCM_LOOKUPS HLC
 WHERE PT.BASE_NAME = ET.ELEMENT_NAME
   AND PT.TEMPLATE_ID = PTRV.TEMPLATE_ID
   AND PTR.RULE_ID = PTRV.RULE_ID
   AND PTR.RULE_TXT = 'What is the proration method?'
   AND PTR.RULE_OPTIONS_VALUE = 'ORA_PAY_TMPLT_PRORATION_METHOD'
   AND PTRV.RULE_VALUE = HLC.LOOKUP_CODE
   AND HLC.LOOKUP_TYPE = 'ORA_PAY_TMPLT_PRORATION_METHOD') AS PRORATION_METHOD,
   
 (SELECT HLC.MEANING
  FROM PAY_TEMPLATES PT,
       PAY_TMPLT_RULES_VL PTR,
   PAY_TMPLT_RULE_VALUES PTRV,
   HCM_LOOKUPS HLC
 WHERE PT.BASE_NAME = ET.ELEMENT_NAME
   AND PT.TEMPLATE_ID = PTRV.TEMPLATE_ID
   AND PTR.RULE_ID = PTRV.RULE_ID
   AND PTR.RULE_TXT = 'Is this element subject to retroactive changes?'
   AND PTR.RULE_OPTIONS_VALUE = 'PAY_TMPLT_YES_NO'
   AND PTRV.RULE_VALUE = HLC.LOOKUP_CODE
   AND HLC.LOOKUP_TYPE = 'PAY_TMPLT_YES_NO') AS RETROACTIVE_CHANGES,
   
   (SELECT HLC.MEANING
  FROM PAY_TEMPLATES PT,
       PAY_TMPLT_RULES_VL PTR,
   PAY_TMPLT_RULE_VALUES PTRV,
   HCM_LOOKUPS HLC
 WHERE PT.BASE_NAME = ET.ELEMENT_NAME
   AND PT.TEMPLATE_ID = PTRV.TEMPLATE_ID
   AND PTR.RULE_ID = PTRV.RULE_ID
   AND PTR.RULE_TXT = 'Should this element be included in the earnings calculation of the FLSA overtime base rate?'
   AND PTR.RULE_OPTIONS_VALUE = 'PAY_TMPLT_YES_NO'
   AND PTRV.RULE_VALUE = HLC.LOOKUP_CODE
   AND HLC.LOOKUP_TYPE = 'PAY_TMPLT_YES_NO') AS EARNINGS_FLSA,
   
   (SELECT HLC.MEANING
  FROM PAY_TEMPLATES PT,
       PAY_TMPLT_RULES_VL PTR,
   PAY_TMPLT_RULE_VALUES PTRV,
   HCM_LOOKUPS HLC
 WHERE PT.BASE_NAME = ET.ELEMENT_NAME
   AND PT.TEMPLATE_ID = PTRV.TEMPLATE_ID
   AND PTR.RULE_ID = PTRV.RULE_ID
   AND PTR.RULE_TXT = 'Should this element be included in the hours calculation of the FLSA overtime base rate?'
   AND PTR.RULE_OPTIONS_VALUE = 'PAY_TMPLT_YES_NO'
   AND PTRV.RULE_VALUE = HLC.LOOKUP_CODE
   AND HLC.LOOKUP_TYPE = 'PAY_TMPLT_YES_NO') AS HOURS_FLSA,
   
    (SELECT HLC.MEANING
  FROM PAY_TEMPLATES PT,
       PAY_TMPLT_RULES_VL PTR,
   PAY_TMPLT_RULE_VALUES PTRV,
   HCM_LOOKUPS HLC
 WHERE 
   PT.BASE_NAME = ET.ELEMENT_NAME
   AND PT.TEMPLATE_ID = PTRV.TEMPLATE_ID
   AND PTR.RULE_ID = PTRV.RULE_ID
   AND PTR.RULE_TXT = 'What should happen when there are insufficient funds to cover the deductions?'
   AND PTR.RULE_OPTIONS_VALUE = 'PAY_TMPLT_DEDN_ARREAR'
   AND PTRV.RULE_VALUE = HLC.LOOKUP_CODE
   AND HLC.LOOKUP_TYPE = 'PAY_TMPLT_DEDN_ARREAR') AS INSUF_FNDS,
   
 ( SELECT HLC.MEANING
  FROM PAY_TEMPLATES PT,
       PAY_TMPLT_RULES_VL PTR,
   PAY_TMPLT_RULE_VALUES PTRV,
   HCM_LOOKUPS HLC
 WHERE 
   PT.BASE_NAME = ET.ELEMENT_NAME
   AND PT.TEMPLATE_ID = PTRV.TEMPLATE_ID
   AND PTR.RULE_ID = PTRV.RULE_ID
   AND PTR.RULE_TXT = 'Processing Stop when the Total is reached?'
   AND PTR.RULE_OPTIONS_VALUE = 'PAY_TMPLT_YES_NO'
   AND PTRV.RULE_VALUE = HLC.LOOKUP_CODE
   AND HLC.LOOKUP_TYPE = 'PAY_TMPLT_YES_NO') AS PRCESS_STP,
   
 ( SELECT HLC.MEANING
  FROM PAY_TEMPLATES PT,
       PAY_TMPLT_RULES_VL PTR,
   PAY_TMPLT_RULE_VALUES PTRV,
   HCM_LOOKUPS HLC
 WHERE 
   PT.BASE_NAME = ET.ELEMENT_NAME
   AND PT.TEMPLATE_ID = PTRV.TEMPLATE_ID
   AND PTR.RULE_ID = PTRV.RULE_ID
   AND PTR.RULE_TXT = 'Use this element to calculate a gross amount from a specified net amount?'
   AND PTR.RULE_OPTIONS_VALUE = 'PAY_TMPLT_YES_NO'
   AND PTRV.RULE_VALUE = HLC.LOOKUP_CODE
   AND HLC.LOOKUP_TYPE = 'PAY_TMPLT_YES_NO') AS CAL_GROSS,
 
 PEL.ELEMENT_LINK_NAME AS ELIGIBILITY_NAME,
 SUBSTR(PEL.EFFECTIVE_START_DATE,1,10) AS ELIGIBILITY_START_DATE,
 SUBSTR(PEL.EFFECTIVE_END_DATE,1,10) AS ELIGIBILITY_END_DATE
 
FROM PAY_ELEMENT_TYPES_VL ET
, PAY_ELE_CLASSIFICATIONS_TL CLA
, PER_LEGISLATIVE_DATA_GROUPS_VL PLD
, PAY_ELEMENT_LINKS_F PEL
WHERE CLA.classification_id = ET.classification_id
AND (ET.LEGISLATION_CODE IS NULL OR ET.LEGISLATION_CODE IS NOT NULL)
AND ET.LEGISLATIVE_DATA_GROUP_ID = PLD.LEGISLATIVE_DATA_GROUP_ID(+)
AND ET.ELEMENT_TYPE_ID = PEL.ELEMENT_TYPE_ID(+)
AND ET.ELEMENT_NAME NOT LIKE '%Calculator'
AND ET.ELEMENT_NAME NOT LIKE '%Retro'
AND ET.ELEMENT_NAME NOT LIKE '%Processor'
AND ET.ELEMENT_NAME NOT LIKE '%Distributor'
AND (ET.ELEMENT_NAME NOT LIKE '%Adjustment' OR ET.ELEMENT_NAME LIKE '%- Adjustment' OR ET.ELEMENT_NAME LIKE '%- Tobacco Adjustment')
AND ET.ELEMENT_NAME NOT LIKE '%Maintenance'
AND ET.ELEMENT_NAME NOT LIKE '%Rules'
-- AND ET.ELEMENT_NAME LIKE 'Accidental D and D'
AND CLA.CLASSIFICATION_NAME NOT IN ('Balance Initialization')
/* AND CLA.CLASSIFICATION_NAME IN ('Employer Charges','Imputed Earnings','Involuntary Deductions','Pretax Deductions',
'Standard Earnings','Supplemental Earnings','Tax Deductions','Voluntary Deductions','Taxable Benefits')*/
ORDER BY CLA.CLASSIFICATION_NAME, ET.ELEMENT_NAME,PEL.ELEMENT_LINK_NAME,PEL.EFFECTIVE_START_DATE