[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.
20-Feb-2012 abellur 115.38 13029997 Added get_3rdparty_cheque to
support/archive third party
cheque information.
06-Mar-2012 abellur 115.39 13814029 Added fnd_date.date_to_canonical
to earned date and payment date
for third party cheques.
16-May-2012 sbachu 115.41 9600575 Changed cursor c_payment_info so
that context_id's are created under
correct assignment_id when primary
assignment_id is higher than other
assignment_id's.
13-Jun-2012 sbachu 115.42 14122654 Removed duplicate records in archiver
when only one GRE (T4A/RL1 or T4A/RL2)
is attached to employee and payroll
attached has multiple assignments flag enabled.
*******************************************************************/
/******************************************************************
** 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
where paa.source_action_id = cp_pre_pay_action_id
and paa.assignment_id = cp_assignment_id
and paa.action_status = 'C';
select ppp.value amount,
ppp.pre_payment_id,
popm.org_payment_method_id,
popm.org_payment_method_name,
pppm.personal_payment_method_id,
pppm.payee_id,
pppm.payee_type
from pay_assignment_actions paa,
pay_pre_payments ppp,
pay_org_payment_methods_f popm ,
pay_personal_payment_methods_f pppm,
pay_payment_types ppt
where ppt.payment_type_id = popm.payment_type_id
and ppt.category = 'CH'
and paa.assignment_action_id = cp_pre_pay_action_id
and ppp.assignment_action_id = paa.assignment_action_id
and paa.assignment_id = cp_assignment_id
and ( ( ppp.source_action_id is null
and cp_ppp_source_action_id is null)
or
-- is it a Normal or Process Separate specific
-- Payments should be included in the Standard
-- SOE. Only Separate Payments should be in
-- a Separate SOE.
(ppp.source_action_id is not null
and cp_ppp_source_action_id is null
and exists (
select ''
from pay_run_types_f prt,
pay_assignment_actions paa_run,
pay_payroll_actions ppa_run
where paa_run.assignment_action_id
= ppp.source_action_id
and paa_run.payroll_action_id
= ppa_run.payroll_action_id
and paa_run.run_type_id = prt.run_type_id
and prt.run_method in ('P', 'N')
and ppa_run.effective_date
between prt.effective_start_date
and prt.effective_end_date
)
)
or
(cp_ppp_source_action_id is not null
and ppp.source_action_id = cp_ppp_source_action_id)
)
and ppp.org_payment_method_id = popm.org_payment_method_id
and popm.defined_balance_id is null
and pppm.personal_payment_method_id(+)
= ppp.personal_payment_method_id
and cp_curr_pymt_eff_date between popm.effective_start_date
and popm.effective_end_date
and cp_curr_pymt_eff_date between nvl(pppm.effective_start_date,
cp_curr_pymt_eff_date)
and nvl(pppm.effective_end_date,
cp_curr_pymt_eff_date);
select initcap(rtrim(ppf.title))||' '||rtrim(ppf.first_name)||' '||rtrim(ppf.last_name),
addr.address_line1,
addr.address_line2,
addr.address_line3,
rtrim(addr.town_or_city),
addr.region_1,
addr.postal_code
from
per_addresses addr,
per_people_f ppf
where ppf.person_id = cp_payee_id
and ppf.business_group_id = cp_business_group_id
and cp_payment_date between ppf.effective_start_date
and ppf.effective_end_date
and addr.person_id(+) = ppf.person_id
and addr.primary_flag(+) = 'Y'
and cp_payment_date between addr.date_from(+) and nvl(addr.date_to, cp_payment_date);
select hou.name,
loc.address_line_1,
loc.address_line_2,
loc.address_line_3,
rtrim(loc.town_or_city),
loc.region_1,
loc.postal_code
from
hr_locations loc,
hr_organization_units hou
where hou.organization_id = cp_payee_id
and hou.business_group_id = cp_business_group_id
and cp_payment_date between hou.date_from
and nvl(hou.date_to, cp_payment_date)
and loc.location_id(+) = hou.location_id;
select org_information9
from hr_organization_information
where org_information_context = 'Business Group Information'
and organization_id = cp_business_group_id;
select substr(peev.screen_entry_value,1,20) garn_ref_no,
prrv.result_value amount,
run_ppa.effective_date payment_date,
run_ppa.date_earned date_earned,
prrv.run_result_id
from
pay_element_entry_values_f peev,
pay_input_values_f piv_att,
pay_element_entries_f peef,
pay_run_result_values prrv,
pay_input_values_f piv_pay,
pay_element_types_f pet,
pay_run_results prr,
pay_payroll_actions run_ppa,
pay_assignment_actions run_paa,
pay_action_interlocks pai,
pay_pre_payments ppp
WHERE prrv.result_value = to_char(cp_amount)
AND ppp.pre_payment_id = cp_pre_payment_id
AND ppp.assignment_action_id = pai.locking_action_id
AND pai.locked_action_id = run_paa.assignment_action_id
AND run_paa.payroll_action_id = run_ppa.payroll_action_id
AND run_paa.assignment_action_id = prr.assignment_action_id
AND prr.element_type_id = pet.element_type_id
AND pet.third_party_pay_only_flag = 'Y'
AND run_ppa.date_earned between pet.effective_start_date
and pet.effective_end_date
AND pet.element_type_id = piv_pay.element_type_id
AND upper(piv_pay.name) = 'PAY VALUE'
AND run_ppa.date_earned between piv_pay.effective_start_date
and piv_pay.effective_end_date
AND prr.run_result_id = prrv.run_result_id
AND piv_pay.input_value_id = prrv.input_value_id
AND run_paa.assignment_id = peef.assignment_id
and ppp.personal_payment_method_id = peef.personal_payment_method_id
AND EXISTS (select null from pay_element_links_f pelf
where pelf.element_link_id= peef.element_link_id
and pelf.element_type_id = pet.element_type_id
and run_ppa.date_earned between pelf.effective_start_date
and pelf.effective_end_date)
AND run_ppa.date_earned between peef.effective_start_date
and peef.effective_end_date
AND pet.element_type_id = piv_att.element_type_id
AND upper(piv_att.name) = 'ATTACHMENT NUMBER'
AND run_ppa.date_earned between piv_att.effective_start_date
and piv_att.effective_end_date
AND peef.element_entry_id = peev.element_entry_id
AND piv_att.input_value_id = peev.input_value_id
AND run_ppa.date_earned between peev.effective_start_date
and peev.effective_end_date
AND prrv.result_value is not null
ORDER by prrv.run_result_id;
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 paf.business_group_id, paf.payroll_id
from per_assignments_f paf
where paf.assignment_id = p_assignment_id
and p_curr_pymt_eff_date between paf.effective_start_date
and paf.effective_end_date;
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 ppiv.assignment_id
,nvl(ppiv.source_action_id,-999),paf.primary_flag/*Bug 9600575*/
from pay_payment_information_v ppiv,per_all_assignments_f paf
where ppiv.assignment_action_id = cp_prepay_action_id
and paf.assignment_id = ppiv.assignment_id
and cp_pmt_eff_date between paf.effective_start_date and paf.effective_end_date
order by 2,3 desc,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 distinct ppiv.assignment_id
,ppiv.tax_unit_id
,nvl(ppiv.source_action_id,-999),paf.primary_flag /*Bug 9600575*/
from pay_payment_information_v ppiv,per_all_assignments_f paf
where ppiv.assignment_action_id = cp_prepay_action_id
and paf.assignment_id = ppiv.assignment_id
and cp_pmt_eff_date between paf.effective_start_date and paf.effective_end_date
order by 3,4 desc,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;