Payroll Element Configuration Query in HCM Oracle Cloud

 WITH pay_retro AS

(

SELECT rule_value, pet1.element_name

          FROM pay_tmplt_rule_values ptrv, 

               pay_tmplt_rules_f ptrf ,

               pay_element_types_vl pet1, 

               pay_templates pt

         WHERE ptrv.template_id = pt.template_id 

           AND pt.base_name = pet1.element_name

           AND ptrf.rule_id = ptrv.rule_id

           AND ptrf.rule_name = 'Retro'

),

pay_proration AS

(

SELECT rule_value, pet2.element_name

          FROM pay_tmplt_rule_values ptrv, 

               pay_tmplt_rules_f ptrf ,

               pay_element_types_vl pet2, 

               pay_templates pt

         WHERE ptrv.template_id = pt.template_id 

           AND pt.base_name = pet2.element_name

           AND ptrf.rule_id = ptrv.rule_id

           AND ptrf.rule_name = 'Proration'

)

SELECT ldg.name legislative_data_group

      ,pec.classification_name   primary_classification

      ,pec1.classification_name  secondary_classification

      ,NVL ((SELECT territory_short_name

      FROM fnd_territories_vl

      WHERE (territory_code = SUBSTR(pet.element_name,4,2))

     ),

(SELECT territory_short_name

      FROM fnd_territories_vl

      WHERE (territory_code = ldg.legislation_code))) country_name

      ,pet.element_name          name

      ,pet.reporting_name

      ,pet.description     

      ,TO_CHAR(pef.effective_start_date,'MM/DD/YYYY') effective_date

      ,TO_CHAR(pef.effective_end_date,'MM/DD/YYYY')   effective_end_date

      ,pef.input_currency_code   input_currency

      ,ptd1.definition_name      earliest_entry_date

      ,ptd2.definition_name      latest_entry_date

      ,DECODE(pet.use_at_asg_level, 'Y', 'Assignment level',

              DECODE(pet.use_at_term_level, 'Y', 'Term level', 'Pay relationship level')

             ) employment_level

      ,DECODE(hrl_petproc.meaning, 'Recurring', 'Yes', 'Nonrecurring', 'No') recurring

      ,pef.multiple_entries_allowed_flag multiple_entries_allowed

      ,pp.rule_value proration

      ,pr.rule_value retroactive

      ,pelf.element_link_name  element_eligibility_name

      ,pelf.standard_link_flag automatic_entry 

      , CASE WHEN :p_effective_start_date >= pef.effective_start_date AND :p_effective_start_date < pef.effective_end_date  

             THEN 'ACTIVE'

             ELSE 'INACTIVE'

             END AS            status

      ,pelf.attribute2         merit_eligible

      ,pelf.attribute1         bonus_eligible

      ,pelf.attribute3         pension_eligible

  FROM pay_element_types_vl       pet

      ,pay_ele_classifications_vl pec

      ,pay_ele_classifications_vl pec1

      ,pay_element_links_f        pelf

      ,pay_element_types_f        pef

      ,per_legislative_data_groups_vl ldg                     

      ,hr_lookups                 hrl_petproc

      ,pay_time_definitions       ptd1

      ,pay_time_definitions       ptd2

  ,pay_retro                  pr

  ,pay_proration              pp

 WHERE 1=1

   AND pet.classification_id           = pec.classification_id

   AND pet.secondary_classification_id = pec1.classification_id(+)

   AND pet.element_type_id             = pelf.element_type_id(+)

   AND pet.element_type_id             = pef.element_type_id

   AND ldg.legislative_data_group_id   = pet.legislative_data_group_id

   AND UPPER(pet.reporting_name) not like '%SALARY%'                

   AND pet.processing_type             = hrl_petproc.lookup_code

   AND hrl_petproc.lookup_type LIKE 'PAY_PROCESSING_TYPE'

   AND TRUNC(SYSDATE) BETWEEN hrl_petproc.start_date_active AND hrl_petproc.end_date_active

   AND pet.starting_time_def_id = ptd1.time_definition_id

   AND pet.ending_time_def_id   = ptd2.time_definition_id

   AND pet.element_name                = pp.element_name(+)

   AND pet.element_name                = pr.element_name(+)

   AND ((COALESCE(NULL, :param_ldg_name) IS NULL)

       OR (ldg.NAME IN (:param_ldg_name)))

   AND ((COALESCE(NULL, :param_element_name) IS NULL)

       OR (pet.element_type_id IN (:param_element_name)))

   AND ((COALESCE(NULL, :param_rep_name) IS NULL)                 

       OR (pet.reporting_name IN (:param_rep_name)))              

   AND (CASE WHEN :param_status = 'All'

             THEN 'All'

             WHEN :p_effective_start_date >= pef.effective_start_date AND :p_effective_start_date < pef.effective_end_date  

             THEN 'ACTIVE'

             ELSE 'INACTIVE'

         END)  = :param_status

   ORDER BY ldg.legislation_code, pet.element_name