PAY Business Payrolls Survey

Description
Categories: BI Publisher, Human Resources
Application: Payroll
Source: Business Payrolls Survey (XML)
Short Name: PAYCABPS_XML
DB package: PAY_PAYCABPS_XMLP_PKG
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.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,:CF_Emp_Count,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
/*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,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) 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
Parameter Name SQL text Validation
Business Group Id
 
Number
Location
 
LOV Oracle
Province
 
LOV Oracle
Province or Location
 
LOV Oracle
GRE
 
LOV Oracle
Reference Month
 
LOV Oracle