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