Oracle HRMS Scripts(Queries) - Entry Value

SELECT

entry.assignment_id, asg.assignment_number, TYPE.element_name,

     CASE

          WHEN pec.classification_name = 'Earnings'

             THEN 'Earnings'

         WHEN pec.classification_name = 'Supplemental Earnings'

             THEN 'Supplemental Earnings'

--

          WHEN INSTR (pec.classification_name, 'Deductions') >=1

             THEN 'Deductions'

          WHEN INSTR (pec.classification_name, 'Information') >=1

             THEN 'Information'

          ELSE pec.classification_name

       END classification,

       CASE

          WHEN INSTR (pec.classification_name, 'Earning') >=  1

             THEN '1'

          WHEN INSTR (pec.classification_name, 'Deductions') >=

                                                           1

             THEN '2'

          WHEN INSTR (pec.classification_name,

                      'Information') >= 1

             THEN '3'

          ELSE pec.classification_name

       END class_sort_order,

       --

       TYPE.processing_type,

       DECODE (TYPE.processing_type,

               'R', 'Recurring',

               'Non Recurring'

              ) processing_type_meaning,

       TYPE.post_termination_rule,

       DECODE (TYPE.post_termination_rule,

               'L', 'Last Standard Process',

               'F', 'Final Close',

               'A', 'Acutal Termination'

              ) termination_rule_meaning,

       TYPE.input_currency_code, TYPE.output_currency_code, inpval.uom,

       DECODE (inpval.uom,

               'M', 'Money',

               'N', 'Number',

               'D', 'Date',

               'ND', 'Day',

               'C', 'Character'

              ) unit_of_measure,

       NVL

          (DECODE (inpval.uom,

                   'M', fnd_number.canonical_to_number (VALUE.screen_entry_value),

                   'N', fnd_number.canonical_to_number (VALUE.screen_entry_value)

                  ),

           0

          ) e_value_num,

       NVL (VALUE.screen_entry_value, '0') e_value, inpval.NAME value_name,

       VALUE.element_entry_id, inpval.effective_start_date eft_st_date_inpval,

       inpval.effective_end_date eft_ed_date_inpval,

       LINK.effective_start_date eft_st_date_link,

       LINK.effective_end_date eft_end_date_link,

       TYPE.effective_start_date eft_st_date_type,

       TYPE.effective_end_date eft_end_date_type,

       entry.effective_start_date eft_st_date_entry,

       entry.effective_end_date eft_end_date_entry,

       asg.effective_start_date eft_st_date_asg,

       asg.effective_end_date eft_end_date_asg,

       TYPE.element_type_id element_type_id

  FROM pay_element_types_f TYPE,

       pay_element_links_f LINK,

       pay_element_entries_f entry,

       pay_element_entry_values_f VALUE,

       pay_input_values_f inpval,

       pay_element_classifications pec,

       APPS.PER_ASSIGNMENTS_F2 asg

 WHERE TYPE.element_type_id = LINK.element_type_id

   AND entry.element_link_id = LINK.element_link_id

--AND ENTRY.ENTRY_TYPE IN ('A', 'R')

   --AND VALUE.element_entry_id(+) = entry.element_entry_id

   AND VALUE.element_entry_id = entry.element_entry_id  

   AND entry.effective_start_date between type.effective_start_date and type.effective_end_date

   AND entry.effective_start_date between link.effective_start_date and link.effective_end_date

   AND entry.effective_start_date between inpval.effective_start_date and inpval.effective_end_date

   AND entry.effective_start_date between value.effective_start_date and value.effective_end_date

   AND entry.effective_start_date between asg.effective_start_date and asg.effective_end_date

   --

   --AND VALUE.effective_start_date(+) = entry.effective_start_date

   --AND VALUE.effective_end_date(+) = entry.effective_end_date

   --AND inpval.input_value_id(+) = VALUE.input_value_id  

   AND inpval.input_value_id = VALUE.input_value_id

   AND pec.classification_id = TYPE.classification_id

   AND asg.assignment_id = entry.assignment_id

   AND (UPPER (inpval.NAME) NOT LIKE '%FUTU%' OR inpval.NAME IS NOT NULL)

   and assignment_number not like 'XX%'