Oracl Payroll Query

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 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 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%'

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

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

   --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