[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
update_ytd_withheld
Modified py_archive_date and
py_action_creation. Calling
get_multi_assignment_flag of
pay_ac_action_arch and
arch_pay_action_level_data of
pay_emp_action_arch.
19-FEB-2002 vpandya 115.7 Changed global variable name for
Multiple Assignment Payments.
12-Jun-2002 vpandya 115.8 Added
- procedure populate_fed_prov_bal
- get_context_value
Modified py_archinit, populating
PL/SQL table for defined balance id
for Tax Balances.
Modified py_archive_data,
added cursor cur_taxgrp to get
Tax Group Id and cursor cur_language
to get correspondance language of
person.
12-Jun-2002 vpandya 115.9 Modified populate_fed_prov_bal
archive jurisdiction_code as
'00-000-0000'
13-Jun-2002 vpandya 115.10 Modified get_context_value return
'-1' when 'No Tax Group' found.
24-Jun-2002 vpandya 115.11 Modified py_archinit to populate
PL/SQL table for all jurisdiction.
Also modified populate_fed_prov_bal
to archive taxes for all juris. Now
storing tax group id in the variable
gn_taxgrp_gre_id(static variable).
23-Jul-2002 vpandya 115.12 2476693 Setting context Tax Unit Id
for Non-Payroll Payment element.
20-NOV-2002 vpandya 115.14 Calling set_error_message function
of pay_emp_action_arch from all
exceptions to get error message
Remote Procedure Calls(RPC or Sub
program). Added exceptions in
all procedures and functions.
06-FEB-2003 vpandya 115.15 2657464 Changed for translation.
2705741 Getting base_language. If person's
2683634 correspondence language is not
US or FRC, or it is null then
setting base language as default.
10-FEB-2003 vpandya 115.16 Added two input paramters to
get_xfr_elements.
18-FEB-2003 vpandya 115.17 Added nocopy for gscc.
24-FEB-2003 vpandya 115.18 Added procedure
create_chld_act_for_multi_gre for
assignment action creation for
multi gre.
07-Mar-2003 vpandya 115.19 Changed procedure
create_chld_act_for_multi_gre, added
condition exit from the loop if
c_mst_prepay_act%notfound.
12-Mar-2003 vpandya 115.20 Changed proc create_child_actions
and create_chld_act_for_multi_gre:
added pay_org_payment_methods_f
to avoid to get pay_pre_payments of
'Third Party Payments'
02-Apr-2003 vpandya 115.21 2879620 Changed process_action:
Modified cursor c_time_period.
11-Apr-2003 vpandya 115.22 Changed archive_data:
create_child_actions_for_gre and
create_child_act_for_taxgrp.
Using view pay_payment_information_v
to archive assignments whether
it has zero and non zero payment.If
zero payment, then atleast earning
element has been processed.
28-Jul-2003 vpandya 115.23 3053917 Passing parameter
p_ytd_balcall_aaid to
get_personal_information.
10-Sep-2003 vpandya 115.24 Passing p_seperate_check_flag to
get_last_xfr_info as per teminated
asg changes done by ekim.
18-Sep-2003 vpandya 115.25 Changed range cursor to fix gscc
error on date conversion. Using
fnd_date.date_to_canonical instead
to_char and canonical_to_date
instead of to_date.
19-Jan-2004 vpandya 115.26 3356401 The SQL ID: 6194306 is for the
cursor c_prev_run_information, which
was in get_last_xfr_info procedure.
This procedure has been removed from
this package and same procedure of
pay_ac_action_arch is being called.
17-Apr-2004 rsethupa 115.27 3311866 SS Payslip Currency Format Enhancement
Current Amount and Ytd Amount for
category 'AC DEDUCTIONS' will be
archived in canonical format.
26-Apr-2004 rsethupa 115.28 3559626 In procedure process_actions,
assigned lv_person_lang to variable
pay_emp_action_arch.gv_correspondence_language
also.(For fetching Accrual Information
in the corresponding language)
02-Aug-2004 SSattini 115.29 3498653 Added functionality to archive
Balance adjustments and Reversals
for Canada legislation.
18-Oct-2004 SSattini 115.30 3940380 Added p_xfr_action_id parameter
to get_last_xfr_info procedure call
from process actions, part of fix
for bug#3940380.
26-Oct-2004 SSattini 115.31 3960157 Bugfix 3960157
02-Sep-2005 Saurgupt 115.33 4566656 Modified proc populate_fed_prov_bal.
Added 'PPIP EE Withheld' along with
QPP balances. Modified cur_def_bal,
added 'PPIP EE Withheld' in query.
26-APR-2006 ahanda 115.34 4675938 Changed priority for tax elements.
13-DEC-2006 meshah 115.36 5655448 changed action_creation, cursor
c_get_xfr_emp added a INDEX hint
and removed nvl for
consolidation_set.
18-JUL-2007 pganguly 115.37 6169715 Change the cursor cur_language,
added the missing date join with
per_people_f.
*******************************************************************/
/******************************************************************
** Package Local Variables
******************************************************************/
gv_package varchar2(100) := 'pay_ca_payroll_arch';
select context_value
from pay_action_contexts pac,
ff_contexts fc
where pac.assignment_action_id = p_asg_act_id
and pac.assignment_id = p_assignment_id
and pac.context_id = fc.context_id
and fc.context_name = p_context_name;
select org_information4
from hr_organization_information hoi
where hoi.org_information_context = 'Canada Employer Identification'
and hoi.organization_id = p_tax_unit_id;
select effective_date,
start_date,
business_group_id,
to_number(substr(legislative_parameters,
instr(legislative_parameters,
'TRANSFER_CONSOLIDATION_SET_ID=')
+ length('TRANSFER_CONSOLIDATION_SET_ID='))),
to_number(ltrim(rtrim(substr(legislative_parameters,
instr(legislative_parameters,
'TRANSFER_PAYROLL_ID=')
+ length('TRANSFER_PAYROLL_ID='),
(instr(legislative_parameters,
'TRANSFER_CONSOLIDATION_SET_ID=') - 1 )
- (instr(legislative_parameters,
'TRANSFER_PAYROLL_ID=')
+ length('TRANSFER_PAYROLL_ID='))))))
from pay_payroll_actions
where payroll_action_id = cp_payroll_action_id;
/*Insert this into the plsql table */
hr_utility.trace('Tax Balance Name : '|| dbt(i).bal_name );
select ppa.effective_date, paa.assignment_action_id
from pay_payroll_actions ppa,
pay_assignment_actions paa
where paa.assignment_id = p_assignment_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_type in ('U','P')
and ppa.effective_date < p_curr_pymt_eff_date
and ppa.effective_date in
( select max(ppa1.effective_date)
from pay_payroll_actions ppa1,
pay_assignment_actions paa1
where ppa1.payroll_action_id = paa1.payroll_action_id
and ppa1.action_type in ('U','P')
and paa1.assignment_id = p_assignment_id
and ppa1.effective_date < p_curr_pymt_eff_date);
select paa.assignment_action_id
from pay_assignment_actions paa,
pay_action_interlocks pai,
pay_payroll_actions ppa
where pai.locking_action_id = cp_prepayment_action_id
and paa.assignment_action_id = pai.locked_action_id
and paa.assignment_id = cp_assignment_id
and ppa.payroll_action_id = paa.payroll_action_id
and nvl(paa.run_type_id,0) <> cp_sepchk_run_type
and not exists ( select 1
from pay_run_types_f prt
where prt.legislation_code = 'CA'
and prt.run_type_id = nvl(paa.run_type_id,0)
and prt.run_method = 'C' )
and ((paa.source_action_id is not null) OR
(ppa.action_type = 'B' and paa.source_action_id is null))
/* and paa.source_action_id is not null -- old code */
order by paa.action_sequence desc;
select paa.assignment_action_id
from pay_assignment_actions paa,
pay_action_interlocks pai,
pay_payroll_actions ppa
where pai.locking_action_id = cp_prepayment_action_id
and paa.assignment_action_id = pai.locked_action_id
and paa.assignment_id = cp_assignment_id
and paa.tax_unit_id = cp_tax_unit_id
and ppa.payroll_action_id = paa.payroll_action_id
and nvl(paa.run_type_id,0) <> cp_sepchk_run_type
and not exists ( select 1
from pay_run_types_f prt
where prt.legislation_code = 'CA'
and prt.run_type_id = nvl(paa.run_type_id,0)
and prt.run_method = 'C' )
and paa.source_action_id is not null
order by paa.action_sequence desc;
select ptp.time_period_id,
ppa.date_earned,
ppa.effective_date
from pay_assignment_actions paa,
pay_payroll_actions ppa,
per_time_periods ptp
where paa.assignment_action_id = cp_run_assignment_action
and ppa.payroll_action_id = paa.payroll_action_id
and ptp.payroll_id = ppa.payroll_id
and ppa.date_earned between ptp.start_date and ptp.end_date;
select ppf.correspondence_language person_language
from per_assignments_f paf
, per_people_f ppf
where paf.assignment_id = p_assignment_id
and p_curr_pymt_eff_date between paf.effective_start_date
and paf.effective_end_date
and ppf.person_id = paf.person_id
and p_curr_pymt_eff_date between ppf.effective_start_date
and ppf.effective_end_date;
select org_information4
from hr_organization_information hoi
where hoi.org_information_context = 'Canada Employer Identification'
and hoi.organization_id = p_tax_unit_id;
select language_code
from fnd_languages
where installed_flag = 'B';
pay_emp_action_arch.insert_rows_thro_api_process(
p_action_context_id => p_xfr_action_id
,p_action_context_type=> 'AAP'
,p_assignment_id => p_assignment_id
,p_tax_unit_id => p_tax_unit_id
,p_curr_pymt_eff_date => p_curr_pymt_eff_date
,p_tab_rec_data => pay_ac_action_arch.lrr_act_tab
);
select distinct assignment_id
,nvl(source_action_id,-999)
from pay_payment_information_v
where assignment_action_id = cp_prepay_action_id
order by 2,1;
select assignment_action_id
from pay_assignment_actions
where source_action_id = cp_prepay_action_id
and assignment_id = cp_assignment_id
and tax_unit_id = cp_tax_unit_id;
select paa.assignment_action_id
from pay_action_interlocks pai
,pay_assignment_actions paa
,pay_payroll_actions ppa
where pai.locked_action_id = cp_source_action_id
and paa.assignment_action_id = pai.locking_action_id
and paa.source_action_id is not null
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_type in ( 'P', 'U' );
select paa.assignment_action_id,ppa_run.action_type
from pay_assignment_actions paa
,pay_action_interlocks pai
,pay_payroll_actions ppa_run
where pai.locking_action_id = cp_pp_asg_act_id
and paa.assignment_action_id = pai.locked_action_id
and paa.assignment_id = cp_assignment_id
and paa.source_action_id is null
/* Added these two line to avoid reversals 'V' */
and ppa_run.payroll_action_id = paa.payroll_action_id
and ppa_run.action_type <> 'V';
select paa.assignment_action_id
from pay_assignment_actions paa,
pay_action_interlocks pai
where pai.locking_action_id = cp_pp_asg_act_id
and paa.assignment_action_id = pai.locked_action_id
and paa.assignment_id = cp_assignment_id
and paa.tax_unit_id = cp_tax_unit_id
and nvl(paa.run_type_id,0) <> cp_sepchk_run_type
and not exists ( select 1
from pay_run_types_f prt
where prt.legislation_code = 'CA'
and prt.run_type_id = nvl(paa.run_type_id,0)
and prt.run_method = 'C' );
select pay_assignment_actions_s.nextval
into ln_child_xfr_action_id
from dual;
update pay_assignment_actions
set serial_number = lv_serial_number
where assignment_action_id = ln_child_xfr_action_id;
select assignment_id
,tax_unit_id
,nvl(source_action_id,-999)
from pay_payment_information_v
where assignment_action_id = cp_prepay_action_id
order by 3,1,2;
select assignment_action_id
from pay_assignment_actions
where source_action_id = cp_prepay_action_id
and assignment_id = cp_assignment_id
and tax_unit_id = cp_tax_unit_id;
select paa.assignment_action_id
from pay_action_interlocks pai
,pay_assignment_actions paa
,pay_payroll_actions ppa
where pai.locked_action_id = cp_source_action_id
and paa.assignment_action_id = pai.locking_action_id
and paa.source_action_id is not null
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_type in ( 'P', 'U' );
select paa.assignment_action_id, paa.source_action_id
from pay_assignment_actions paa
,pay_action_interlocks pai
where pai.locking_action_id = cp_pp_asg_act_id
and paa.assignment_action_id = pai.locked_action_id
and paa.assignment_id = cp_assignment_id
and paa.tax_unit_id = cp_tax_unit_id
and paa.source_action_id is not null
order by paa.action_sequence desc;
select paa.assignment_action_id
from pay_assignment_actions paa,
pay_action_interlocks pai
where pai.locking_action_id = cp_pp_asg_act_id
and paa.assignment_action_id = pai.locked_action_id
and paa.assignment_id = cp_assignment_id
and paa.tax_unit_id = cp_tax_unit_id
and nvl(paa.run_type_id,0) <> cp_sepchk_run_type
and not exists ( select 1
from pay_run_types_f prt
where prt.legislation_code = 'CA'
and prt.run_type_id = nvl(paa.run_type_id,0)
and prt.run_method = 'C' );
select pay_assignment_actions_s.nextval
into ln_child_xfr_action_id
from dual;
update pay_assignment_actions
set serial_number = lv_serial_number
where assignment_action_id = ln_child_xfr_action_id;
select distinct
paa.assignment_action_id
,paa.tax_unit_id
from pay_assignment_actions paa
,pay_pre_payments ppp
,pay_org_payment_methods popm
where paa.source_action_id = cp_master_prepay_act_id
and ppp.assignment_action_id = paa.assignment_action_id
and ppp.source_action_id is null
and nvl(ppp.value,0) <> 0
and ppp.org_payment_method_id = popm.org_payment_method_id
and popm.defined_balance_id is not null
and cp_curr_pymt_eff_date between popm.effective_start_date
and popm.effective_end_date;
select distinct
paa.assignment_action_id
,paa.tax_unit_id
from pay_assignment_actions paa
,pay_pre_payments ppp
,pay_org_payment_methods popm
where paa.assignment_action_id = cp_master_prepay_act_id
and ppp.assignment_action_id = paa.assignment_action_id
and ppp.source_action_id is null
and nvl(ppp.value,0) <> 0
and ppp.org_payment_method_id = popm.org_payment_method_id
and popm.defined_balance_id is not null
and p_curr_pymt_eff_date between popm.effective_start_date
and popm.effective_end_date;
select distinct paa.tax_unit_id
from pay_assignment_actions paa
,pay_action_interlocks pai
where pai.locking_action_id = cp_pp_asg_act_id
and paa.assignment_action_id = pai.locked_action_id
and paa.tax_unit_id is not null;
select paa.assignment_action_id, paa.source_action_id
from pay_assignment_actions paa
,pay_action_interlocks pai
where pai.locking_action_id = cp_pp_asg_act_id
and paa.assignment_action_id = pai.locked_action_id
and paa.source_action_id is not null
order by paa.action_sequence desc;
select pay_assignment_actions_s.nextval
into ln_child_xfr_action_id
from dual;
update pay_assignment_actions
set serial_number = lv_serial_number
where assignment_action_id = ln_child_xfr_action_id;
select distinct
paa.assignment_id,
paa.tax_unit_id,
paa.assignment_action_id,
ppp.source_action_id
from pay_payroll_actions ppa
,pay_assignment_actions paa
,pay_pre_payments ppp
,pay_org_payment_methods popm
where ppa.consolidation_set_id
= nvl(cp_cons_set_id,ppa.consolidation_set_id)
and paa.action_status = 'C'
and ppa.payroll_id = cp_payroll_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.business_group_id = cp_business_group_id
and ppa.action_status = 'C'
and ppa.effective_date between cp_start_date
and cp_end_date
and ppa.action_type in ('U','P')
and nvl(paa.source_action_id,paa.assignment_action_id)
= cp_master_prepay_action_id
and ppp.assignment_action_id = paa.assignment_action_id
and ppp.source_action_id is not null
and nvl(ppp.value,0) <> 0
and ppp.org_payment_method_id = popm.org_payment_method_id
and popm.defined_balance_id is not null
and cp_curr_pymt_eff_date between popm.effective_start_date
and popm.effective_end_date
order by 1,2,3,4;
select distinct
paa.assignment_id,
paa.tax_unit_id,
paa.assignment_action_id,
ppp.source_action_id
from pay_pre_payments ppp
,pay_assignment_actions paa
,pay_org_payment_methods popm
where paa.assignment_action_id = cp_prepayment_action_id
and ppp.assignment_action_id = paa.assignment_action_id
and nvl(ppp.value,0) <> 0
and ppp.source_action_id is not null
and ppp.org_payment_method_id = popm.org_payment_method_id
and popm.defined_balance_id is not null
and cp_curr_pymt_eff_date between popm.effective_start_date
and popm.effective_end_date
order by ppp.source_action_id;
select pai.locked_action_id
from pay_action_interlocks pai,
pay_assignment_actions paa
where pai.locking_action_id = cp_master_prepay_action_id
and paa.assignment_action_id = pai.locked_action_id
and paa.source_action_id is not null
and paa.run_type_id = cp_sepchk_run_type_id;
select paa.tax_unit_id
from pay_assignment_actions paa
where paa.assignment_action_id = cp_source_action_id;
select pay_assignment_actions_s.nextval
into ln_child_xfr_action_id
from dual;
update pay_assignment_actions
set serial_number = lv_serial_number
where assignment_action_id = ln_child_xfr_action_id;
select paa.payroll_action_id,
paa.assignment_action_id,
paa.assignment_id,
paa.tax_unit_id,
paa.serial_number,
paa.chunk_number
from pay_assignment_actions paa
where paa.assignment_action_id = cp_assignment_action;
select org_information9
from hr_organization_information
where org_information_context = 'Business Group Information'
and organization_id = cp_business_group;
select prt.run_type_id
from pay_run_types_f prt
where prt.shortname = 'SEP_PAY'
and prt.legislation_code = 'CA';
select distinct paa.assignment_id
from pay_action_interlocks pai,
pay_assignment_actions paa,
pay_payroll_actions ppa
where pai.locking_action_id = cp_prepayment_action_id
and paa.assignment_action_id = pai.locked_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_type in ('R', 'Q', 'B')
and ((ppa.run_type_id is null and
paa.source_action_id is null) or
(ppa.run_type_id is not null and
paa.source_action_id is not null))
and ppa.action_status = 'C';
select paa.assignment_action_id, paa.payroll_action_id,
ppa.action_type
from pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_action_interlocks pai
where pai.locking_action_Id = cp_prepayment_action_id
and pai.locked_action_id = paa.assignment_action_id
and paa.assignment_id = cp_assignment_id
and paa.source_action_id is null
and ppa.payroll_action_id = paa.payroll_action_id
order by paa.assignment_action_id desc;
select effective_date
from pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_action_id = cp_prepayment_action_id;
select count(*)
from pay_action_information
where action_context_id = cp_payroll_action_id
and action_context_type = 'PA';
select locked_action_id
from pay_action_interlocks
where locking_action_id = cp_xfr_action_id;
select distinct ppa.action_type
from pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_action_interlocks pai
where pai.locking_action_Id = cp_prepayment_action_id
and pai.locked_action_id = paa.assignment_action_id
and paa.assignment_id = cp_assignment_id
and paa.source_action_id is null
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_type <> 'V';
pay_emp_action_arch.insert_rows_thro_api_process(
p_action_context_id => p_xfr_action_id
,p_action_context_type=> 'AAP'
,p_assignment_id => ln_assignment_id
,p_tax_unit_id => ln_tax_unit_id
,p_curr_pymt_eff_date => ld_curr_pymt_eff_date
,p_tab_rec_data => pay_ac_action_arch.lrr_act_tab
);
pay_emp_action_arch.insert_rows_thro_api_process(
p_action_context_id => p_xfr_action_id
,p_action_context_type=> 'AAP'
,p_assignment_id => ln_assignment_id
,p_tax_unit_id => ln_tax_unit_id
,p_curr_pymt_eff_date => ld_curr_pymt_eff_date
,p_tab_rec_data => pay_ac_action_arch.lrr_act_tab
);
Purpose : This returns the select statement that is used to created the
range rows for the Canadian Payroll Archiver.
Arguments :
Notes : Calls procedure - get_payroll_action_info
******************************************************************/
PROCEDURE py_range_cursor( p_payroll_action_id in number
,p_sqlstr out nocopy varchar2)
IS
ld_end_date DATE;
'select distinct paa.assignment_id
from pay_assignment_actions paa,
pay_payroll_actions ppa
where ppa.business_group_id = ''' || ln_business_group_id || '''
and ppa.effective_date between fnd_date.canonical_to_date(''' ||
fnd_date.date_to_canonical(ld_start_date) || ''')
and fnd_date.canonical_to_date(''' ||
fnd_date.date_to_canonical(ld_end_date) || ''')
and ppa.action_type in (''U'',''P'',''B'',''V'')
and decode(ppa.action_type,
''B'', nvl(ppa.future_process_mode, ''Y''),
''N'') = ''N''
and ppa.action_status =''C''
and ppa.consolidation_set_id = ''' || ln_cons_set_id || '''
and ppa.payroll_id = ''' || ln_payroll_id || '''
and ppa.payroll_action_id = paa.payroll_action_id
and paa.action_status = ''C''
and paa.source_action_id is null
and not exists
(select ''x''
from pay_action_interlocks pai,
pay_assignment_actions paa1,
pay_payroll_actions ppa1
where pai.locked_action_id = paa.assignment_action_id
and paa1.assignment_action_id = pai.locking_action_id
and ppa1.payroll_action_id = paa1.payroll_action_id
and ppa1.action_type =''X''
and ppa1.report_type = ''PY_ARCHIVER'')
and :payroll_action_id > 0
order by paa.assignment_id';
select /*+ INDEX (PAA PAY_ASSIGNMENT_ACTIONS_N50) */
paa.assignment_id,
paa.tax_unit_id,
ppa.effective_date,
ppa.date_earned,
ppa.action_type,
paa.assignment_action_id,
paa.payroll_action_id
from pay_payroll_actions ppa,
pay_assignment_actions paa
where paa.assignment_id between cp_start_assignment_id
and cp_end_assignment_id
and ppa.consolidation_set_id = cp_cons_set_id
and paa.action_status = 'C'
and ppa.payroll_id = cp_payroll_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.business_group_id = cp_business_group_id
and ppa.action_status = 'C'
and ppa.effective_date between cp_start_date
and cp_end_date
and ppa.action_type in ('U','P','B','V')
and decode(ppa.action_type,
'B', nvl(ppa.future_process_mode, 'Y'),
'N') = 'N'
and paa.source_action_id is null
and not exists
(select 'x'
from pay_action_interlocks pai1,
pay_assignment_actions paa1,
pay_payroll_actions ppa1
where pai1.locked_action_id = paa.assignment_action_id
and paa1.assignment_action_id = pai1.locking_action_id
and ppa1.payroll_action_id = paa1.payroll_action_id
and ppa1.action_type ='X'
and ppa1.report_type = 'PY_ARCHIVER')
order by 1,2,3,4,5;
select max(paa.assignment_action_id)
from pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_action_interlocks pai
where pai.locking_action_Id = cp_prepayment_action_id
and pai.locked_action_id = paa.assignment_action_id
and paa.source_action_id is null
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_type in ('R', 'Q');
select paa.assignment_action_id
from pay_assignment_actions paa
where paa.source_action_id = cp_prepay_master_aa_id;
select pay_assignment_actions_s.nextval
into ln_xfr_action_id
from dual;
update pay_assignment_actions
set serial_number = lv_serial_number
where assignment_action_id = ln_xfr_action_id;
select org_information1
from hr_organization_information hoi,
pay_payroll_actions ppa
where ppa.payroll_action_id = p_pactid
and hoi.organization_id = ppa.business_group_id
and hoi.org_information_context = 'Payroll Archiver Level';
select pbt.balance_name,
decode(pbt.balance_name,
'CPP EE Withheld', 1,
'QPP EE Withheld', 2,
'EI EE Withheld', 3,
'PPIP EE Withheld',4,
'FED Withheld', 5,
'PROV Withheld', 6,
7) display_sequence,
pbt.balance_type_id
from pay_balance_types pbt
where pbt.legislation_code = 'CA'
and pbt.balance_name in ( 'FED Withheld',
'CPP EE Withheld',
'EI EE Withheld',
'PROV Withheld',
'QPP EE Withheld',
'PPIP EE Withheld')
order by 2;
select language, lookup_code, meaning
from fnd_lookup_values
where lookup_type = 'CA_SOE_SHORT_NAME';
select rule_mode
from pay_legislation_rules
where legislation_code = 'CA'
and rule_type = 'MULTI_TAX_UNIT_PAYMENT';
select balance_name,
balance_type_id
from pay_balance_types
where legislation_code = 'CA'
and balance_name in ( 'Gross Earnings', 'Payments' );
dbt.delete;
tax.delete;