PAY Quarterly Employment Survey

select hou.name  Employer,
          hsck.segment1 Employer_Id,
          pay.payroll_id,
          pay.payroll_name,
          ptp.time_period_id,
          ptp.period_type,
          ptp.regular_payment_date,
          hlo.location_id,
          hlo.location_code,
          hlo.address_line_1,
          hlo.address_line_2,
          hlo.address_line_3,
          hlec.lookup_code  Emp_Cat_Code,
          hlec.meaning  Emp_Cat,
          hlwt.lookup_code Work_Time_Code,
          hlwt.meaning  Work_Time,
          fcl.lookup_code Sex_Code,
          decode(hlec.lookup_code, 'W',  pay_nz_qes_pkg.count_employees(hsck.segment1,
                                                               pay.payroll_id,
                                                               ptp.time_period_id,
                                                               hlo.location_id,
                                                               hlec.lookup_code,
                                                               hlwt.lookup_code,
                                                               fcl.lookup_code,
                                                               :P_SURVEY_DATE) ,
                      'E', pay_nz_qes_pkg.count_employees_using_balance(hsck.segment1,
                                                               pay.payroll_id,
                                                               ptp.time_period_id,
                                                               hlo.location_id,
                                                               hlec.lookup_code,
                                                               hlwt.lookup_code,
                                                               fcl.lookup_code,
                                                               :cp_week_hours,
                                                               :cp_week_frequency,
                                                               :P_SURVEY_DATE)) emp_count
from hr_lookups fcl,
        hr_lookups hlec,
        hr_lookups hlwt,
        hr_locations hlo,
        per_time_periods ptp,
        pay_payrolls_f pay,
        per_people_f pap,
        per_assignments_f paa,
        hr_soft_coding_keyflex hsck,
        hr_organization_units hou
where hou.business_group_id = :p_business_group_id
and     to_char(hou.organization_id) = hsck.segment1
and     hsck.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
and     paa.business_group_id = :p_business_group_id
and     pay.payroll_id = paa.payroll_id
and     hlo.location_id = paa.location_id
and     pap.person_id = paa.person_id
and     pay.payroll_id = ptp.payroll_id
and     ptp.regular_payment_date between paa.effective_start_date and paa.effective_end_date
and     fcl.application_id = :cp_application_id
and     fcl.lookup_type = 'SEX'
and     hlec.lookup_type = 'NZ_STATS_NZ_EMP_CAT'
and     hlwt.lookup_type = 'NZ_STATS_NZ_WORKING_TIME'
and     ptp.regular_payment_date = (select max(ptp_max.regular_payment_date)
                                                       from   per_time_periods ptp_max
                                                       where pay.payroll_id = ptp_max.payroll_id
                                                       and     ptp_max.regular_payment_date <= :p_survey_date)
group by hou.name, hsck.segment1, pay.payroll_id, pay.payroll_name,
              ptp.time_period_id, ptp.period_type, ptp.regular_payment_date,
              hlo.location_id, hlo.location_code, hlo.address_line_1,
              hlo.address_line_2, hlo.address_line_3, hlec.lookup_code,
              hlec.meaning, hlwt.lookup_code, hlwt.meaning, fcl.lookup_code
order by 1 ASC,2 ASC,4 ASC,6 ASC,7 ASC,9 ASC,10 ASC,11 ASC,12 ASC,3 ASC,5 ASC,8 ASC,13 DESC,14 ASC,15 ASC,16 ASC ,hou.name, pay.payroll_name, hlo.location_code, hlec.lookup_code desc,
              hlwt.lookup_code, fcl.lookup_code desc