ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D4716C51A9E05362FB09050512Y 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 PAY PYNZQES_XML US PAY Quarterly Employment Survey Imported from BI Publisher Description: Quarterly Employment Survey (New Zealand) Application: Payroll Source: Quarterly Employment Survey (XML) Short Name: PYNZQES_XML DB package: PAY_PYNZQES_XMLP_PKG BI Publisher :cp_application_id :cp_balance_dimension :cp_hours_balance_name :cp_payout_balance_name :cp_report_name :cp_statistics_balance_name :cp_week_frequency :cp_week_hours :p_business_group_id :p_conc_request_id :p_survey_date 1 -20 :p_business_group_id Number fnd_profile.value('PER_BUSINESS_GROUP_ID') Y US Business Group ID 2 10 :p_survey_date Date Y US Survey Date