SELECT a.*,
NVL (DECODE (classification,
'Earnings', rr_val,
'Supplemental Earnings', rr_val,
'Information', rr_val,
'Deductions', rr_val * -1,
0
),
0
) rr_val_sign
FROM (SELECT paa.assignment_id,
DECODE (NVL (ppa.run_type_id, '63'),
'63', 'Standard',
'64', 'Main',
'61', 'Pay Separately'
) process_type,
asg.assignment_number,
NVL (zain_hr_pkg.get_child_count (paa.assignment_id,
ppa.effective_date
),
0
) no_child,
zain_abs_pkg.get_entitlement (paa.assignment_id,
ppa.effective_date
) leave_ent,
ppa.payroll_id,
paa.assignment_action_id,
TO_CHAR (ppa.effective_date, 'Mon-yy') pay_period,
TO_NUMBER (TO_CHAR (effective_date, 'YYYYMM')) pay_period_sort,
ppa.action_type,
ppa.effective_date,
pet.element_name,
piv.NAME value_name,
prv.result_value,
piv.uom,
DECODE (piv.uom,
'D', TO_CHAR
(fnd_date.canonical_to_date (prv.result_value)
),
prv.result_value
) rr_val_a,
NVL (DECODE (piv.uom,
'M', fnd_number.canonical_to_number
(prv.result_value),
'N', fnd_number.canonical_to_number
(prv.result_value),
0
),
0
) rr_val,
NVL ((SELECT (NVL (rv1.result_value, 0))
FROM pay_run_result_values rv1,
pay_input_values_f
iv1
WHERE rv1.input_value_id = iv1.input_value_id
AND rv1.run_result_id = prv.run_result_id
AND iv1.NAME = 'Normal Overtime Hours'),
'NA'
) normal_overtime_hours,
NVL ((SELECT (NVL (rv1.result_value, 0))
FROM pay_run_result_values rv1,
pay_input_values_f iv1
WHERE rv1.input_value_id = iv1.input_value_id
AND rv1.run_result_id = prv.run_result_id
AND iv1.NAME = 'Normal Overtime Minutes'),
'NA'
) normal_overtime_minutess,
NVL ((SELECT (NVL (rv1.result_value, 0))
FROM pay_run_result_values rv1,
pay_input_values_f
iv1
WHERE rv1.input_value_id = iv1.input_value_id
AND rv1.run_result_id = prv.run_result_id
AND iv1.NAME = 'Holiday Overtime Hours'),
'NA'
) holiday_overtime_hours,
NVL ((SELECT (NVL (rv1.result_value, 0))
FROM pay_run_result_values rv1,
pay_input_values_f
iv1
WHERE rv1.input_value_id = iv1.input_value_id
AND rv1.run_result_id = prv.run_result_id
AND iv1.NAME = 'Holiday Overtime Minutes'),
'NA'
) holiday_overtime_minutes,
pet.element_type_id
element_type_id,
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,
DECODE (pet.processing_type,
'R', 'Recurring',
'Non Recurring'
) processing_type_meaning,
pet.input_currency_code,
pet.output_currency_code,
-- prr.assignment_action_id,
prr.element_entry_id,
DECODE (ppa.action_type, 'Q', 'Quick Pay', 'R', 'Run')
run_type
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_run_results prr,
pay_run_result_values prv,
pay_input_values_f piv,
pay_element_types_f pet,
apps.per_assignments_f2 asg,
pay_element_classifications pec
WHERE ppa.payroll_action_id = paa.payroll_action_id
AND paa.assignment_action_id = prr.assignment_action_id
AND prr.run_result_id = prv.run_result_id
AND prr.element_type_id = pet.element_type_id
AND piv.input_value_id = prv.input_value_id
-- AND paa.action_status = 'C'
AND ppa.effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
AND ppa.payroll_id = 63
AND ppa.business_group_id = 81
AND asg.assignment_id = paa.assignment_id
AND ppa.effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND pec.classification_id(+) = pet.classification_id
AND piv.NAME IN ('Pay Value', 'Costed')
--
and element_name like 'Over%'
AND UPPER (piv.NAME) NOT LIKE '%FUTURE%') a
No comments:
Post a Comment