PAY Business Payrolls Survey
Description
Categories: BI Publisher
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
Description: Business Payrolls Survey Report
Application: Payroll
Source: Business Payrolls Survey (XML)
Short Name: PAYCABPS_XML
DB package: PAY_PAYCABPS_XMLP_PKG
Run
PAY Business Payrolls Survey and other Oracle EBS reports with Blitz Report™ on our demo environment
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 |