PAY Business Payrolls Survey

➜ Back to Library
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
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