ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D4713351A9E05362FB09050512 SELECT pp.payroll_name,ppa.payroll_id,nvl(count(ppf.full_name),0) emp_count, decode(ptp.period_type,'Week','W', 'Bi-Week','B', 'Semi-Month','S', 'Calendar Month','M', 'O') Period_type, decode(ppb.pay_basis,'HOURLY','HOURLY', 'ANNUAL','SALARIED', 'MONTHLY','SALARIED', 'PERIOD','SALARIED', 'OTHER') Pay_basis, ptp.start_date Start_date, ptp.end_date End_date, paa.tax_unit_id GRE, DECODE(:P_PROVINCE_OR_LOCATION, 'PROVINCE',hl.region_1, 'LOCATION',paf.location_id, hl.region_1) prov_or_loc2, DECODE(:P_PROVINCE_OR_LOCATION,'PROVINCE', pay_ca_group_level_bal_pkg.ca_group_level_balance_rb( 'Regular Earnings', 'PYDATE', ptp.end_date, ptp.start_date, null, paa.tax_unit_id, DECODE(:P_PROVINCE_OR_LOCATION, 'PROVINCE',hl.region_1, 'LOCATION',paf.location_id, hl.region_1), null, null, ppa.payroll_id, decode(ppb.pay_basis,'HOURLY','HOURLY', 'ANNUAL','SALARIED', 'MONTHLY','SALARIED', 'PERIOD','SALARIED', 'OTHER'), :cp_seed_bal_flag), 'LOCATION', pay_ca_group_level_bal_pkg.ca_group_level_balance_rb( 'Regular Earnings', 'PYDATE', ptp.end_date, ptp.start_date, null, paa.tax_unit_id, NULL, null, DECODE(:P_PROVINCE_OR_LOCATION, 'PROVINCE',hl.region_1, 'LOCATION',paf.location_id, hl.region_1), ppa.payroll_id, decode(ppb.pay_basis,'HOURLY','HOURLY', 'ANNUAL','SALARIED', 'MONTHLY','SALARIED', 'PERIOD','SALARIED', 'OTHER'), :cp_seed_bal_flag), pay_ca_group_level_bal_pkg.ca_group_level_balance_rb( 'Regular Earnings', 'PYDATE', ptp.end_date, ptp.start_date, null, paa.tax_unit_id, DECODE(:P_PROVINCE_OR_LOCATION, 'PROVINCE',hl.region_1, 'LOCATION',paf.location_id, hl.region_1), null, null, ppa.payroll_id, decode(ppb.pay_basis,'HOURLY','HOURLY', 'ANNUAL','SALARIED', 'MONTHLY','SALARIED', 'PERIOD','SALARIED', 'OTHER'), :cp_seed_bal_flag)) Regular_Gross, DECODE(:P_PROVINCE_OR_LOCATION,'PROVINCE', pay_ca_group_level_bal_pkg.ca_group_level_balance_rb( 'Overtime Hours', 'PYDATE', ptp.end_date, ptp.start_date, null, paa.tax_unit_id, DECODE(:P_PROVINCE_OR_LOCATION, 'PROVINCE',hl.region_1, 'LOCATION',paf.location_id, hl.region_1), null, null, ppa.payroll_id, decode(ppb.pay_basis,'HOURLY','HOURLY', 'ANNUAL','SALARIED', 'MONTHLY','SALARIED', 'PERIOD','SALARIED', 'OTHER'), :cp_seed_bal_flag), 'LOCATION', pay_ca_group_level_bal_pkg.ca_group_level_balance_rb( 'Overtime Hours', 'PYDATE', ptp.end_date, ptp.start_date, null, paa.tax_unit_id, NULL, null, DECODE(:P_PROVINCE_OR_LOCATION, 'PROVINCE',hl.region_1, 'LOCATION',paf.location_id, hl.region_1), ppa.payroll_id, decode(ppb.pay_basis,'HOURLY','HOURLY', 'ANNUAL','SALARIED', 'MONTHLY','SALARIED', 'PERIOD','SALARIED', 'OTHER'), :cp_seed_bal_flag), pay_ca_group_level_bal_pkg.ca_group_level_balance_rb( 'Overtime Hours', 'PYDATE', ptp.end_date, ptp.start_date, null, paa.tax_unit_id, DECODE(:P_PROVINCE_OR_LOCATION, 'PROVINCE',hl.region_1, 'LOCATION',paf.location_id, hl.region_1), null, null, ppa.payroll_id, decode(ppb.pay_basis,'HOURLY','HOURLY', 'ANNUAL','SALARIED', 'MONTHLY','SALARIED', 'PERIOD','SALARIED', 'OTHER'), :cp_seed_bal_flag)) Overtime_Hours, DECODE(:P_PROVINCE_OR_LOCATION,'PROVINCE', pay_ca_group_level_bal_pkg.ca_group_level_balance_rb( 'Overtime', 'PYDATE', ptp.end_date, ptp.start_date, null, paa.tax_unit_id, DECODE(:P_PROVINCE_OR_LOCATION, 'PROVINCE',hl.region_1, 'LOCATION',paf.location_id, hl.region_1), null, null, ppa.payroll_id, decode(ppb.pay_basis,'HOURLY','HOURLY', 'ANNUAL','SALARIED', 'MONTHLY','SALARIED', 'PERIOD','SALARIED', 'OTHER'), :cp_seed_bal_flag), 'LOCATION', pay_ca_group_level_bal_pkg.ca_group_level_balance_rb( 'Overtime', 'PYDATE', ptp.end_date, ptp.start_date, null, paa.tax_unit_id, NULL, null, DECODE(:P_PROVINCE_OR_LOCATION, 'PROVINCE',hl.region_1, 'LOCATION',paf.location_id, hl.region_1), ppa.payroll_id, decode(ppb.pay_basis,'HOURLY','HOURLY', 'ANNUAL','SALARIED', 'MONTHLY','SALARIED', 'PERIOD','SALARIED', 'OTHER'), :cp_seed_bal_flag), pay_ca_group_level_bal_pkg.ca_group_level_balance_rb( 'Overtime', 'PYDATE', ptp.end_date, ptp.start_date, null, paa.tax_unit_id, DECODE(:P_PROVINCE_OR_LOCATION, 'PROVINCE',hl.region_1, 'LOCATION',paf.location_id, hl.region_1), null, null, ppa.payroll_id, decode(ppb.pay_basis,'HOURLY','HOURLY', 'ANNUAL','SALARIED', 'MONTHLY','SALARIED', 'PERIOD','SALARIED', 'OTHER'), :cp_seed_bal_flag)) Overtime_Pay, DECODE(:P_PROVINCE_OR_LOCATION,'PROVINCE', pay_ca_group_level_bal_pkg.ca_group_level_balance_rb( decode(ppb.pay_basis,'HOURLY','Regular and Overtime Hours', 'ANNUAL','Regular Salary Hours', 'MONTHLY','Regular Salary Hours', 'PERIOD','Regular Salary Hours'), 'PYDATE', ptp.end_date, ptp.start_date, null, paa.tax_unit_id, DECODE(:P_PROVINCE_OR_LOCATION, 'PROVINCE',hl.region_1, 'LOCATION',paf.location_id, hl.region_1), null, null, ppa.payroll_id, decode(ppb.pay_basis,'HOURLY','HOURLY', 'ANNUAL','SALARIED', 'MONTHLY','SALARIED', 'PERIOD','SALARIED', 'OTHER'), :cp_seed_bal_flag), 'LOCATION', pay_ca_group_level_bal_pkg.ca_group_level_balance_rb( decode(ppb.pay_basis,'HOURLY','Regular and Overtime Hours', 'ANNUAL','Regular Salary Hours', 'MONTHLY','Regular Salary Hours', 'PERIOD','Regular Salary Hours'), 'PYDATE', ptp.end_date, ptp.start_date, null, paa.tax_unit_id, NULL, null, DECODE(:P_PROVINCE_OR_LOCATION, 'PROVINCE',hl.region_1, 'LOCATION',paf.location_id, hl.region_1), ppa.payroll_id, decode(ppb.pay_basis,'HOURLY','HOURLY', 'ANNUAL','SALARIED', 'MONTHLY','SALARIED', 'PERIOD','SALARIED', 'OTHER'), :cp_seed_bal_flag), pay_ca_group_level_bal_pkg.ca_group_level_balance_rb( decode(ppb.pay_basis,'HOURLY','Regular and Overtime Hours', 'ANNUAL','Regular Salary Hours', 'MONTHLY','Regular Salary Hours', 'PERIOD','Regular Salary Hours'), 'PYDATE', ptp.end_date, ptp.start_date, null, paa.tax_unit_id, DECODE(:P_PROVINCE_OR_LOCATION, 'PROVINCE',hl.region_1, 'LOCATION',paf.location_id, hl.region_1), null, null, ppa.payroll_id, decode(ppb.pay_basis,'HOURLY','HOURLY', 'ANNUAL','SALARIED', 'MONTHLY','SALARIED', 'PERIOD','SALARIED', 'OTHER'), :cp_seed_bal_flag)) Regular_Hrs_Worked, PAY_PAYCABPS_XMLP_PKG.l_all_emp_avg_hrsformula(DECODE(:P_PROVINCE_OR_LOCATION,'PROVINCE', pay_ca_group_level_bal_pkg.ca_group_level_balance_rb( decode(ppb.pay_basis,'HOURLY','Regular and Overtime Hours', 'ANNUAL','Regular Salary Hours', 'MONTHLY','Regular Salary Hours', 'PERIOD','Regular Salary Hours'), 'PYDATE', ptp.end_date, ptp.start_date, null, paa.tax_unit_id, DECODE(:P_PROVINCE_OR_LOCATION, 'PROVINCE',hl.region_1, 'LOCATION',paf.location_id, hl.region_1), null, null, ppa.payroll_id, decode(ppb.pay_basis,'HOURLY','HOURLY', 'ANNUAL','SALARIED', 'MONTHLY','SALARIED', 'PERIOD','SALARIED', 'OTHER'), :cp_seed_bal_flag), 'LOCATION', pay_ca_group_level_bal_pkg.ca_group_level_balance_rb( decode(ppb.pay_basis,'HOURLY','Regular and Overtime Hours', 'ANNUAL','Regular Salary Hours', 'MONTHLY','Regular Salary Hours', 'PERIOD','Regular Salary Hours'), 'PYDATE', ptp.end_date, ptp.start_date, null, paa.tax_unit_id, NULL, null, DECODE(:P_PROVINCE_OR_LOCATION, 'PROVINCE',hl.region_1, 'LOCATION',paf.location_id, hl.region_1), ppa.payroll_id, decode(ppb.pay_basis,'HOURLY','HOURLY', 'ANNUAL','SALARIED', 'MONTHLY','SALARIED', 'PERIOD','SALARIED', 'OTHER'), :cp_seed_bal_flag), pay_ca_group_level_bal_pkg.ca_group_level_balance_rb( decode(ppb.pay_basis,'HOURLY','Regular and Overtime Hours', 'ANNUAL','Regular Salary Hours', 'MONTHLY','Regular Salary Hours', 'PERIOD','Regular Salary Hours'), 'PYDATE', ptp.end_date, ptp.start_date, null, paa.tax_unit_id, DECODE(:P_PROVINCE_OR_LOCATION, 'PROVINCE',hl.region_1, 'LOCATION',paf.location_id, hl.region_1), null, null, ppa.payroll_id, decode(ppb.pay_basis,'HOURLY','HOURLY', 'ANNUAL','SALARIED', 'MONTHLY','SALARIED', 'PERIOD','SALARIED', 'OTHER'), :cp_seed_bal_flag)),PAY_PAYCABPS_XMLP_PKG.cf_emp_countformula(decode (ppb.pay_basis,'HOURLY','HOURLY','ANNUAL','SALARIED','MONTHLY','SALARIED','PERIOD','SALARIED','OTHER'),paa.tax_unit_id,pp.payroll_name),ptp.end_date,ptp.start_date) L_ALL_EMP_AVG_HRS, PAY_PAYCABPS_XMLP_PKG.cf_emp_countformula(decode (ppb.pay_basis,'HOURLY','HOURLY','ANNUAL','SALARIED','MONTHLY','SALARIED','PERIOD','SALARIED','OTHER'),paa.tax_unit_id,pp.payroll_name) CF_Emp_Count, PAY_PAYCABPS_XMLP_PKG.cf_parttime_emp_countformula(decode (ppb.pay_basis,'HOURLY','HOURLY','ANNUAL','SALARIED', 'MONTHLY','SALARIED','PERIOD','SALARIED','OTHER'),paa.tax_unit_id,pp.payroll_name) CF_Parttime_Emp_Count, PAY_PAYCABPS_XMLP_PKG.cf_total_hoursformula(DECODE(:P_PROVINCE_OR_LOCATION,'PROVINCE', pay_ca_group_level_bal_pkg.ca_group_level_balance_rb( 'Overtime Hours', 'PYDATE', ptp.end_date, ptp.start_date, null, paa.tax_unit_id, DECODE(:P_PROVINCE_OR_LOCATION, 'PROVINCE',hl.region_1, 'LOCATION',paf.location_id, hl.region_1), null, null, ppa.payroll_id, decode(ppb.pay_basis,'HOURLY','HOURLY', 'ANNUAL','SALARIED', 'MONTHLY','SALARIED', 'PERIOD','SALARIED', 'OTHER'), :cp_seed_bal_flag), 'LOCATION', pay_ca_group_level_bal_pkg.ca_group_level_balance_rb( 'Overtime Hours', 'PYDATE', ptp.end_date, ptp.start_date, null, paa.tax_unit_id, NULL, null, DECODE(:P_PROVINCE_OR_LOCATION, 'PROVINCE',hl.region_1, 'LOCATION',paf.location_id, hl.region_1), ppa.payroll_id, decode(ppb.pay_basis,'HOURLY','HOURLY', 'ANNUAL','SALARIED', 'MONTHLY','SALARIED', 'PERIOD','SALARIED', 'OTHER'), :cp_seed_bal_flag), pay_ca_group_level_bal_pkg.ca_group_level_balance_rb( 'Overtime Hours', 'PYDATE', ptp.end_date, ptp.start_date, null, paa.tax_unit_id, DECODE(:P_PROVINCE_OR_LOCATION, 'PROVINCE',hl.region_1, 'LOCATION',paf.location_id, hl.region_1), null, null, ppa.payroll_id, decode(ppb.pay_basis,'HOURLY','HOURLY', 'ANNUAL','SALARIED', 'MONTHLY','SALARIED', 'PERIOD','SALARIED', 'OTHER'), :cp_seed_bal_flag)),DECODE(:P_PROVINCE_OR_LOCATION,'PROVINCE', pay_ca_group_level_bal_pkg.ca_group_level_balance_rb( decode(ppb.pay_basis,'HOURLY','Regular and Overtime Hours', 'ANNUAL','Regular Salary Hours', 'MONTHLY','Regular Salary Hours', 'PERIOD','Regular Salary Hours'), 'PYDATE', ptp.end_date, ptp.start_date, null, paa.tax_unit_id, DECODE(:P_PROVINCE_OR_LOCATION, 'PROVINCE',hl.region_1, 'LOCATION',paf.location_id, hl.region_1), null, null, ppa.payroll_id, decode(ppb.pay_basis,'HOURLY','HOURLY', 'ANNUAL','SALARIED', 'MONTHLY','SALARIED', 'PERIOD','SALARIED', 'OTHER'), :cp_seed_bal_flag), 'LOCATION', pay_ca_group_level_bal_pkg.ca_group_level_balance_rb( decode(ppb.pay_basis,'HOURLY','Regular and Overtime Hours', 'ANNUAL','Regular Salary Hours', 'MONTHLY','Regular Salary Hours', 'PERIOD','Regular Salary Hours'), 'PYDATE', ptp.end_date, ptp.start_date, null, paa.tax_unit_id, NULL, null, DECODE(:P_PROVINCE_OR_LOCATION, 'PROVINCE',hl.region_1, 'LOCATION',paf.location_id, hl.region_1), ppa.payroll_id, decode(ppb.pay_basis,'HOURLY','HOURLY', 'ANNUAL','SALARIED', 'MONTHLY','SALARIED', 'PERIOD','SALARIED', 'OTHER'), :cp_seed_bal_flag), pay_ca_group_level_bal_pkg.ca_group_level_balance_rb( decode(ppb.pay_basis,'HOURLY','Regular and Overtime Hours', 'ANNUAL','Regular Salary Hours', 'MONTHLY','Regular Salary Hours', 'PERIOD','Regular Salary Hours'), 'PYDATE', ptp.end_date, ptp.start_date, null, paa.tax_unit_id, DECODE(:P_PROVINCE_OR_LOCATION, 'PROVINCE',hl.region_1, 'LOCATION',paf.location_id, hl.region_1), null, null, ppa.payroll_id, decode(ppb.pay_basis,'HOURLY','HOURLY', 'ANNUAL','SALARIED', 'MONTHLY','SALARIED', 'PERIOD','SALARIED', 'OTHER'), :cp_seed_bal_flag))) CF_Total_Hours FROM hr_locations_all hl ,per_all_people_f ppf ,per_pay_bases ppb ,per_all_assignments_f paf ,pay_payrolls_f pp ,pay_assignment_actions paa ,pay_payroll_actions ppa ,per_time_periods ptp WHERE ptp.time_period_id in (SELECT max(ptp2.time_period_id) FROM per_time_periods ptp2, pay_all_payrolls_f ppf2 WHERE to_char(ptp2.end_date,'YYYY/MM')=substr(:P_CANONICAL_REFERENCE_MONTH,1,7) AND ppf2.payroll_id=ptp2.payroll_id AND ppf2.business_group_id=:P_BUSINESS_GROUP_ID GROUP BY ppf2.payroll_id,ptp2.period_type) AND ptp.time_period_id=ppa.time_period_id AND ppa.effective_date between ptp.start_date and ptp.end_date AND ppa.action_type in ('R','Q') AND ppa.action_status='C' AND EXISTS (SELECT 'X' from pay_payroll_actions PPA2, pay_run_types_f PRT WHERE PPA2.payroll_action_id=PPA.payroll_action_id and nvl(PPA2.run_type_id,-1)=PRT.run_type_id and substr(PRT.shortname,1,1) in ('R','T')) AND ptp.payroll_id=ppa.payroll_id AND ppa.business_group_id+0=nvl(:P_BUSINESS_GROUP_ID,ppa.business_group_id) AND ppa.payroll_action_id=paa.payroll_action_id AND paa.action_status='C' AND ppa.payroll_id=pp.payroll_id AND ppa.effective_date between pp.effective_start_date and pp.effective_end_date AND paa.tax_unit_id=nvl(:tax_unit_id,paa.tax_unit_id) AND EXISTS (SELECT 'X' FROM hr_organization_information oi1, hr_all_organization_units ou WHERE ou.organization_id=nvl(:tax_unit_id,paa.tax_unit_id) AND ou.organization_id=oi1.organization_id AND oi1.org_information_context='Canada Employer Identification' AND oi1.org_information5='T4/RL1') AND paa.assignment_id=paf.assignment_id AND ppa.effective_date between paf.effective_start_date and paf.effective_end_date AND paf.pay_basis_id=ppb.pay_basis_id(+) AND paf.location_id=hl.location_id AND paf.person_id=ppf.person_id AND ppa.effective_date between ppf.effective_start_date and ppf.effective_end_date AND hl.region_1=nvl(:CP_PROVINCE,hl.region_1) AND paf.location_id=nvl(TO_NUMBER(:CP_LOCATION),paf.location_id) GROUP BY pp.payroll_name, ppa.payroll_id, ptp.period_type, pay_basis, ptp.start_date, ptp.end_date, paa.tax_unit_id, DECODE(:P_PROVINCE_OR_LOCATION, 'PROVINCE',hl.region_1, 'LOCATION',paf.location_id, hl.region_1) ORDER BY Pay_Basis,Payroll_Name Y PAY PAYCABPS_XML US PAY Business Payrolls Survey Imported from BI Publisher Description: Business Payrolls Survey Report Application: Payroll Source: Business Payrolls Survey (XML) Short Name: PAYCABPS_XML DB package: PAY_PAYCABPS_XMLP_PKG BI Publisher :c_business_group_name :c_end_date :c_gre_name :c_location_code :c_province_name :c_report_subtitle :c_start_date :cp_gre_name :cp_location :cp_location_code :cp_province :cp_seed_bal_flag :cp_supp_earn_flag :cp_supp_earn_view :lp_gre :lp_master_group :lp_province_or_location :p_business_group_id :p_canonical_reference_month :p_conc_request_id :p_end_date :p_gre :p_location :p_province :p_province_or_location :p_reference_month :p_report_title :p_session_date :pkg_hrly_count :pkg_other_count :pkg_payments_count :pkg_sal_count :tax_unit_id 1 -20 :p_business_group_id Number fnd_profile.value('PER_BUSINESS_GROUP_ID') Y US Business Group Id Business Group Id 2 10 :p_canonical_reference_month LOV Oracle PAY_CA_BPS_REFERENCE_MONTH Y select fnd_date.date_to_canonical(ppa.effective_date) id, to_char(ppa.effective_date,'MON-YYYY') value, null description from pay_payroll_actions ppa where ppa.payroll_action_id in (select max(ppa1.payroll_action_id) from pay_payroll_actions ppa1 where ppa1.business_group_id= fnd_global.per_business_group_id and ppa1.action_status='C' group by to_char(ppa1.effective_date,'MON-YYYY')) order by ppa.effective_date Y US Reference Month Reference Month 3 20 :p_gre LOV Oracle HR_CA_RL1_TAX_UNIT_NAME Y select txu.tax_unit_id id, txu.name value, null description from hr_ca_tax_units_v txu where txu.business_group_id=fnd_global.per_business_group_id and txu.reporting_type in ('T4/RL1','T4A/RL1') order by txu.name US GRE GRE 4 30 :p_province_or_location LOV Oracle PAY_PROVINCE_OR_LOCATION Y select ffvv.flex_value id, ffvv.flex_value value, ffvv.flex_value_meaning||nvl2(ffvv.description,': '||ffvv.description,null) description from fnd_flex_values_vl ffvv where ffvv.flex_value_set_id=1007281 order by ffvv.flex_value US Province or Location Province or Location 5 40 :p_province LOV Oracle PAY_CA_SRS_PROVINCES Y select province_abbrev id, province_abbrev value, province_name description from pay_ca_provinces_v where :$flex$.pay_province_or_location='PROVINCE' order by province_name US Province Province 6 50 :p_location LOV Oracle HR_CA_INTERNAL_LOCATIONS Y select hl.location_id id, hl.location_code value, null description from hr_locations_all hl where hl.location_id in (select max(hl1.location_id) from hr_locations_all hl1,hr_all_organization_units ou where ou.business_group_id= fnd_global.per_business_group_id and ou.internal_external_flag='INT' and ou.location_id=hl1.location_id and hl1.country='CA' group by hl1.location_code) and :$flex$.pay_province_or_location='LOCATION' order by hl.location_code US Location Location :$flex$.pay_province_or_location Province or Location Location :$flex$.pay_province_or_location Province or Location Province