PAY Workplace Gender Equality

Description
Categories: BI Publisher
Columns: Manager Category1, Employment Status1, Male Exc Cnt1, Female Exc Cnt1, Male Cnt1, Female Cnt1, Total Cnt1, Male Salary1, Female Salary1, Male Remuneration1 ...
Application: Payroll
Source: Workplace Gender Equality Report (XML)
Short Name: PYAUWPGER_XML
DB package:
select 
decode(hl1.lookup_code,'FP',hl2.meaning,' ') manager_category1,
hl1.meaning employment_status1,
sum(pai.action_information1)-sum(pai.action_information10)-sum(pai.action_information12) male_exc_cnt1,
sum(pai.action_information2)-sum(pai.action_information11)-sum(pai.action_information13) female_exc_cnt1,
sum(pai.action_information1) male_cnt1,
sum(pai.action_information2) female_cnt1,
sum(pai.action_information1)+sum(pai.action_information2) total_cnt1,
sum(nvl(to_number(pai.action_information6),0)) male_salary1,
sum(nvl(to_number(pai.action_information7),0)) female_salary1,
sum(nvl(to_number(pai.action_information8),0)) male_remuneration1,
sum(nvl(to_number(pai.action_information9),0)) female_remuneration1,
sum(pai.action_information10) male_grad_a_cnt1,
sum(pai.action_information11) female_grad_a_cnt1,
sum(pai.action_information12) male_grad_g_cnt1,
sum(pai.action_information13) female_grad_g_cnt1
from pay_action_information pai,
hr_lookups hl1,
hr_lookups hl2
where pai.action_context_id = :p_payroll_action_id 
and pai.action_information_category='AU_WORKLEDGE_DETAILS_NON_M'
and pai.action_context_type='PA'
and pai.action_information4=hl1.lookup_code
and hl1.lookup_type='AU_EMPLOYMENT_STATUS'
and pai.action_information3=hl2.lookup_code
and hl2.lookup_type='AU_MANAGER_CATEGORY'
group by hl1.meaning,
hl2.meaning,hl2.lookup_code,hl1.lookup_code
order by hl2.lookup_code,decode(hl1.lookup_code,'FP',1,'FC',2,'PP',3,'PC',4,'CA',5)