Monday, February 25, 2013

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  

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect