SELECT :P_LE_NAME legal_entity
, pai.action_information2 payroll_name
, pai.action_information3 bal_cat
, decode (pai.action_information3, 'Income'
, 1, 'Allowances'
, 2, 'Fringe Benefit'
, 3, 'Lump Sum'
, 4, 'Deductions'
, 5, 'Employer Contributions'
, 6, 'Gross Amounts'
, 7) bal_cat_no
, pai.action_information4 rep_name
, pai.action_information5 code
, sum(nvl(pai.action_information6,0)) le_payroll_ytd_val
, sum(nvl(pai.action_information7,0)) le_payroll_rollup_val
FROM pay_assignment_actions paa
, pay_action_information pai
WHERE paa.payroll_action_id = :p_payroll_action_id
AND paa.assignment_action_id = pai.action_context_id
AND pai.action_context_type = 'AAP'
AND pai.action_information_category = 'ZA_PAYROLL_VAL'
group by pai.action_information2,pai.action_information3,pai.action_information4,pai.action_information5
ORDER BY payroll_name
, to_number(bal_cat_no)
, code |