Tuesday, March 10, 2015

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

No comments:

Post a Comment