PAY Business Payrolls Survey

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

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