PAY Workplace Gender Equality

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)

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)