[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
08-DEC-2000 M.Mukherjee 115.21 added business group in the select
queries, otherwise it will fetch
duplicate data.
08-DEC-2000 M.Mukherjee 115.22 changed comments double dash to
slash/star,
otherwise adchkdrv will fail
08-DEC-2000 VPandya 115.23,26 Trying to solve PI on driver
says to replace dashes
12-DEC-2000 MMukherjee 115.27 Added parameter l_has_been_paid to
avoid archiving 0 salary records.
13-DEC-2000 MMukherjee 115.28 Stopped assignment action creation
if the employee has not been paid
anything in that year, even though
there is payroll run.
29-DEC-2000 P.Ganguly 115.29 Added a check if Taxable Benefits
for Federal is present then subtract
it from the gross earnings.
08-NOV-2001 VPandya 115.30 Added QPP Basic Exemption, QPP Exempt
Earnings. Archiving RL1 NonBox Footnotes
in pay_action_information table.
10-NOV-2001 vpandya 115.31 Added set veify off at top as per GSCC.
12-NOV-2001 vpandya 115.32 Added dbdrv line.
27-DEC-2001 vpandya 115.33 Archiving new DBI
CAEOY_EMPLOYEE_DATE_OF_BIRTH
08-Jan-2002 vpandya 115.34 Archiving new DBI
CAEOY_EMPLOYEE_HIRE_DATE
02-Jul-2002 vpandya 115.36 Commented out below clause while getting
max-assignment action id query
AND pac1.assignment_id = l_asgid
01-Aug-2002 vpandya 115.37 Indention of archive_data and not include
status indian(BOXR) in to Gross Earnings
(BOXA).
10-Aug-2002 mmukherj 115.38 Bugfix for #2458533. The cursor
employer_info has been changed so that
it checks the business_group_id.
16-Aug-2002 vpandya 115.39 Bugfix for 2192914:archiving termination
date.
Archiving transmitter name instead of
transmitter org id in DBI
CAEOY_RL1_TRANSMITTER_NAME(ref.2192914)
21-Aug-2002 vpandya 115.40 Bugfix for 2449408:archiving DBI
CAEOY_RL1_ACCOUNTING_CONTACT_LANGUAGE
Changed cursor employer_info, added
column org_information19 for Archiving
Accounting Resource Language, also given
alias to all information columns.
06-Oct-2002 vpandya 115.41 Changed archiver to archive Box-O
footnote.
08-Oct-2002 vpandya 115.43 Initializing variables l_footnote_amount
and l_footnote_amount_ue to avoid
duplicate archiving of footnotes.
22-Oct-2002 vpandya 115.44 Bug 2681250: changed cursor c_get_addr
of eoy_archive_data. If country is CA
take data from region_1 to get province
code and if it is US take data from
region_2 to get state code.
02-DEC-2002 vpandya 115.45 Added nocopy with out parameter
as per GSCC.
06-DEC-2002 vpandya 115.46 Bug 2698320,RL1 BOX-O codes RA to RZ
should be excluded from BOX A on the RL1.
Done using ln_boxo_exclude_from_boxa.
11-DEC-2002 vpandya 115.47 Bug 2698320,not excluding Box-O amount of
T4A/RL1 GRE from Box-A. Put this cond.
getting balance in ln_balance_value first
and summing up after for the same balance
for different GREs.
27-AUG-2003 ssouresr 115.49 If the balance 'RL1 No Gross Earnings' is
non zero then archiving takes place even
if Gross Earnings is zero.
Also the balance 'RL1 Non Taxable Earnings'
is deducted from Gross Earnings.
18-Sep-2003 vpandya 115.50 Archiving dates in canonical format
(YYYY/MM/DD HH:MI:SS) using
fnd_date.date_to_canonical_to_date
instead of using to_char with default
format to fix gscc date conversion error.
21-OCT-2003 ssouresr 115.51 Added RL1 Amendment Archiving logic
in eoy_archive_data procedure. Also
added new local function
compare_archive_data used for RL1
Amendment Archiver.
The organization_id of the Prov Reporting
Est will now be used instead of the QIN
04-NOV-2003 ssouresr 115.52 Converted the pre printed form number
select to a cursor as more than one
record can be returned
10-NOV-2003 ssouresr 115.53 Archiving pre printed form number both
for RL1 and RL1 Amendment. This will
ensure that the function
compare_archive_data compares the
correct data.
12-NOV-2003 ssouresr 115.54 Modified the function
compare_archive_data so that if the
number of archived items to be compared
is different then the amendment flag is
set to Y without checking all the
individual data records.
21-FEB-2004 pganguly 115.55 Fixed bug# 3459723. Changed the cursor
c_get_asg_id so that it picks
assignment of type 'E' only.
02-APR-2004 ssattini 115.56 11510 Changes to fix bug#3356533.
Added new cursor c_get_max_asg_act_id
in action_creation procedure. Modified
cursor c_all_gres_for_person and added
two new cursors c_get_max_asgactid_jd,
c_get_max_asgactid in eoy_archive_data
procedure.
23-APR-2004 ssouresr 115.57 Modified the cursor cur_non_box_mesg to
stop returning duplicate nonbox footnotes
06-JUN-2004 ssattini 115.60 Modified the cursors
c_get_max_asg_act_id,
c_get_max_asgactid_jd and
c_get_max_asgactid to get max asgact_id
based on person_id. Bug fix bug#3638928.
05-AUG-2004 ssouresr 115.61 Footnote codes for BoxQ can now be
archived Also added check to make sure
an appropriate error message is written
to the log if no transmitter has been
specified for the PRE. Bug#3353450.
05-AUG-2004 ssattini 115.62 Modified the cursor cur_non_box_mesg
to archive the balance adjustments
for Non-Box footnotes. Fix bug#3641353.
10-AUG-2004 ssouresr 115.63 Added the negative balance flag bug#3311402
Also modified the non box footnote logic
so that the amounts for identical footnote
codes are summed up bug#3641308
01-SEP-2004 ssouresr 115.64 BoxO can now have a negative balance
Bug 3863016, previously negative values
for this box were being ignored
02-SEP-2004 ssouresr 115.65 Changed to use the function get_parameter
to retrieve PRE_ORGANIZATION_ID
04-OCT-2004 ssouresr 115.66 The negative balance flag is archived as Y
when either a box or nonbox footnote is negative
05-NOV-2004 ssouresr 115.67 RL1 No Gross Earnings needs to be retrieved
across GREs
08-NOV-2004 ssouresr 115.68 All footnotes were reviewed.
BOXL and BOXO RW do not require any footnotes.
BOXO RX and BOX RY are not valid anymore. Also
BOXR has been changed to only have footnote
code 14 (Income from an office or employment)
17-NOV-2004 ssouresr 115.70 BoxO Code is now archived correctly
18-NOV-2004 ssouresr 115.71 Added BOXO_RR to list of balances to archive
19-NOV-2004 ssouresr 115.72 Footnotes for Gross Earnings(BOXA) are now archived
and archiving of BOXO_RZ has been removed
28-NOV-2004 ssouresr 115.73 Modified the cursor c_footnote_info to only return
RL1 footnotes, was previously returning RL2 footnotes
as well.
28-NOV-2004 ssouresr 115.74 Added date range to the latest assignment action cursor
29-NOV-2004 ssouresr 115.75 RL1 footnotes should be archived with Jurisdiction QC
30-NOV-2004 ssouresr 115.76 Archiving CAEOY_QPP_REDUCED_SUBJECT_PER_JD_YTD for BoxG
02-FEB-2005 ssouresr 115.77 NonBox Footnotes with a value of 0 are not archived
04-MAR-2005 ssouresr 115.78 The archiver uses a new NonBox Footnote Element which
has a Jurisdiction input value from the beginning of 2006
26-APR-2005 ssouresr 115.79 The archiver will now recognize amendments made
only to non box footnotes
05-AUG-2005 saurgupt 115.80 Bug 4517693: Added Address_line3 for RL1 archiver.
08-AUG-2005 mmukherj 115.81 The procedure eoy_archinit has been
modified to set the minimum chunk
no, which is required to re archive
the data while retrying the Archiver
in the payroll action level.
Bugfix: #4525642
31-AUG-2005 ssouresr 115.82 New RL1 Nonbox footnote for Taxable Benefits without pay
27-SEP-2005 ssouresr 115.83 Corrected footnote condition in the function
compare_archive_data
10-NOV-2005 ssouresr 115.84 Added Footnote for BOXO RN
07-FEB-2006 ssouresr 115.85 Modified range cursor and main action creation
query to remove the table hr_soft_coding_keyflex
13-Apr-2006 ssmukher 115.86 Modified the sqlstr statement in eoy_range_cursor
procedure for Bug #5120627 fix
07-Aug-2006 ydevi 115.87 5096509..Archiver archives two PPIP EE Withheld
and PPIP EE Taxable into database itens
CAEOY_PPIP_EE_WITHHELD_PER_JD_YTD
and CAEOY_PPIP_EE_TAXABLE_PER_JD_YTD
respectively
18-AUG-2006 meshah 115.88 5202869 For performance reason changed the
query to remove per_people_f and
also disabled some indexes. With this
change the cost of the query
increases however now the path taken
is now more correct. Cursor
c_eoy_qbin has been changed.
28-AUG-2006 meshah 115.89 5495704 the way indexes were disabled has
been changed from using +0 to ||
16-Nov-2006 ydevi 115.90 5159150 archiving RL1_BOXV and RL1_BOXW
into db item CAEOY_RL1_BOXV_PER_JD_YTD
and CAEOY_RL1_BOXW_PER_JD_YTD
21-Dec-2006 ssmukher 115.91 5706335 Archiving BoxI value into DBI
CAEOY_PPIP_REDUCED_SUBJECT_PER_JD_YTD
05-Feb-2007 meshah 115.92 5768390 Removed the if condition that would
not populate boxA when the GRE type
is T4A/RL1
21-Aug-2007 amigarg 115.93 5558604 Added date track and enabled flag
condtion in c_footnote_info
21-Sep-2007 amigarg 115.95 6440125 added date track condition in employee
archiving
10-Jan-2008 sapalani 115.96 6525899 Added check to not to archive the
RL1_BOXO_AMOUNT_RW balance from 2007
*/
sqwl_range varchar2(4000);
select to_number(UE.creator_id)
from ff_user_entities UE,
ff_database_items DI
where DI.user_name = p_db_item_name
and UE.user_entity_id = DI.user_entity_id
and Ue.creator_type = 'B';
/* Name : get_selection_information
Purpose : Returns information used in the selection of people to be reported on.
Arguments :
The following values are returned :
p_period_start - The start of the period over which to select
the people.
p_period_end - The end of the period over which to select
the people.
p_defined_balance_id - The balance which must be non zero for each
person to be included in the report.
p_group_by_gre - should the people be grouped by GRE.
p_group_by_medicare - Should the people ,be grouped by medicare
within GRE NB. this is not currently supported.
p_tax_unit_context - Should the TAX_UNIT_ID context be set up for
the testing of the balance.
p_jurisdiction_context - Should the JURISDICTION_CODE context be set up
for the testing of the balance.
Notes : This routine provides a way of coding explicit rules for
individual reports where they are different from the
standard selection criteria for the report type ie. in
NY state the selection of people in the 4th quarter is
different from the first 3.
*/
procedure get_selection_information
(
/* Identifies the type of report, the authority for which it is being run,
and the period being reported. */
p_report_type varchar2,
p_quarter_start date,
p_quarter_end date,
p_year_start date,
p_year_end date,
/* Information returned is used to control the selection of people to
report on. */
p_period_start in out nocopy date,
p_period_end in out nocopy date,
p_defined_balance_id in out nocopy number,
p_group_by_gre in out nocopy boolean,
p_group_by_medicare in out nocopy boolean,
p_tax_unit_context in out nocopy boolean,
p_jurisdiction_context in out nocopy boolean
) is
begin
/* Depending on the report being processed, derive all the information
required to be able to select the people to report on. */
if p_report_type = 'RL1' then
/* Default settings for Year End Preprocess. */
hr_utility.trace('in getting selection information ');
hr_utility.trace('in error of getting selection information ');
end get_selection_information;
select SR.jurisdiction_code
from pay_state_rules SR
where SR.state_code = p_state;
select user_entity_id
into l_user_entity_id
from ff_database_items
where user_name = p_dbi_name;
select user_entity_id
into l_user_entity_id
from ff_database_items
where user_name = p_dbi_name;
select action_information4,
action_information5
from pay_action_information
where action_context_id = cp_asg_act_id
and action_information_category = 'CA FOOTNOTES'
and action_context_type = 'AAP'
and action_information6 = 'RL1'
order by action_information4;
SELECT fai1.context1,
fdi1.user_entity_id,
fai1.value
FROM ff_archive_items fai1,
ff_database_items fdi1,
ff_archive_item_contexts faic,
ff_contexts fc
WHERE fai1.user_entity_id = fdi1.user_entity_id
AND fai1.archive_item_id = faic.archive_item_id
AND fc.context_id = faic.context_id
AND fc.context_name = 'JURISDICTION_CODE'
AND faic.context = 'QC'
AND fai1.CONTEXT1 = cp_asg_act_id
AND fdi1.user_name <> 'CAEOY_RL1_AMENDMENT_FLAG'
ORDER BY fdi1.user_name;
SELECT fai.context1,fai.user_entity_id,fai.value
FROM ff_archive_items fai
WHERE fai.user_entity_id = cp_dbi_ue_id
AND fai.context1 = cp_asg_act_id;
ltr_amend_arch_data.delete;
ltr_yepp_arch_data.delete;
ltr_amend_emp_data.delete;
ltr_yepp_emp_data.delete;
ltr_emp_ue_id.delete;
ltr_amend_footnote.delete;
ltr_yepp_footnote.delete;
/* Variables used to hold the select columns from the SQL statement.*/
l_person_id number;
SELECT ASG.person_id person_id,
ASG.assignment_id assignment_id,
ASG.effective_end_date effective_end_date
FROM per_all_assignments_f ASG,
pay_all_payrolls_f PPY,
hr_soft_coding_keyflex SCL
WHERE ASG.business_group_id + 0 = l_bus_group_id
AND ASG.person_id between stperson and endperson
AND ASG.assignment_type = 'E'
AND ASG.effective_start_date <= l_period_end
AND ASG.effective_end_date >= l_period_start
AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
AND (
(rtrim(ltrim(SCL.segment1)) in
(select to_char(hoi.organization_id)
from hr_organization_information hoi
where hoi.org_information_context = 'Canada Employer Identification'
and hoi.org_information2 = l_pre_organization_id))
or
(rtrim(ltrim(SCL.segment11)) in
(select to_char(hoi.organization_id)
from hr_organization_information hoi
where hoi.org_information_context = 'Canada Employer Identification'
and hoi.org_information2 = l_pre_organization_id))
)
AND PPY.payroll_id = ASG.payroll_id
and exists ( select 'X' from pay_action_contexts pac, ff_contexts fc
where pac.assignment_id = asg.assignment_id
and pac.context_id = fc.context_id
and fc.context_name = 'JURISDICTION_CODE'
and pac.context_value = 'QC' )
ORDER BY 1, 3 DESC, 2; */
SELECT asg.person_id person_id,
asg.assignment_id assignment_id,
asg.effective_end_date effective_end_date
FROM per_all_assignments_f asg,
pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE ppa.effective_date between l_period_start
and l_period_end
AND ppa.action_type in ('R','Q','V','B','I')
AND ppa.business_group_id +0 = l_bus_group_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND paa.tax_unit_id in (select hoi.organization_id
from hr_organization_information hoi
where hoi.org_information_context ||''= 'Canada Employer Identification'
and hoi.org_information2 = l_pre_organization_id
and hoi.org_information5 in ('T4/RL1','T4A/RL1'))
AND paa.assignment_id = asg.assignment_id
AND ppa.business_group_id = asg.business_group_id +0
AND asg.person_id between stperson and endperson
AND asg.assignment_type = 'E'
AND ppa.effective_date between asg.effective_start_date
and asg.effective_end_date
AND EXISTS (select 1
from pay_action_contexts pac,
ff_contexts fc
where pac.assignment_id = paa.assignment_id
and pac.assignment_action_id = paa.assignment_action_id
and pac.context_id = fc.context_id
and fc.context_name ||'' = 'JURISDICTION_CODE'
and pac.context_value ||'' = 'QC')
ORDER BY 1, 3 DESC, 2;
select hoi.organization_id
from hr_organization_information hoi
where hoi.org_information_context = 'Canada Employer Identification'
and hoi.org_information2 = l_pre_organization_id;
SELECT assignment_id
from per_all_assignments_f paf
where person_id = p_person_id
and primary_flag = 'Y'
and assignment_type = 'E'
and paf.effective_start_date <= l_period_end
and paf.effective_end_date >= l_period_start
ORDER BY assignment_id desc;
select paa.assignment_action_id
from pay_assignment_actions paa,
per_all_assignments_f paf,
per_all_people_f ppf,
pay_payroll_actions ppa,
pay_action_classifications pac
where ppf.person_id = cp_person_id
and paf.person_id = ppf.person_id
and paa.assignment_id = paf.assignment_id
and paa.tax_unit_id = cp_tax_unit_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.effective_date between cp_period_start and cp_period_end
and ppa.effective_date between ppf.effective_start_date
and ppf.effective_end_date
and ppa.effective_date between paf.effective_start_date
and paf.effective_end_date
and ppa.action_type = pac.action_type
and pac.classification_name = 'SEQUENCED'
order by paa.action_sequence desc;
select effective_date,
report_type,
business_group_id,
pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',
legislative_parameters)
into l_effective_date,
l_report_type,
l_bus_group_id,
l_pre_organization_id
from pay_payroll_actions
where payroll_action_id = pactid;
hr_utility.trace('getting selection information');
get_selection_information
(l_report_type,
l_quarter_start,
l_quarter_end,
l_year_start,
l_year_end,
l_period_start,
l_period_end,
l_defined_balance_id,
l_group_by_gre,
l_group_by_medicare,
l_tax_unit_context,
l_jurisdiction_context);
hr_utility.trace('Out of get selection information');
/* select the maximum assignment action id, removed the select stmt
and replaced it with cursor c_get_max_asg_act_id 11510 Changes
Bug#3356533. Passing person_id to fix bug#3638928 */
begin
open c_get_max_asg_act_id(l_person_id,
l_tax_unit_id,
l_period_start,
l_period_end);
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
/* Insert into pay_assignment_actions. */
hr_utility.trace('creating assignment action');
/* Update the serial number column with the person id
so that the mag routine and the RL1 view will not have
to do an additional checking against the assignment
table */
hr_utility.trace('updating assignment action' || to_char(lockingactid));
update pay_assignment_actions aa
set aa.serial_number = to_char(l_person_id)
where aa.assignment_action_id = lockingactid;
select frpv.value
into l_jursd_value
from ff_route_parameter_values frpv,
ff_route_parameters frp
where frpv.route_parameter_id = frp.route_parameter_id
and frpv.user_entity_id = p_user_entity_id
and frp.route_id = p_route_id
and frp.parameter_name = 'Jursd. Level';
select target1.organization_id,
target2.name,
target2.business_group_id,
target1.ORG_INFORMATION2 Prov_Identi_Number,
target1.ORG_INFORMATION7 Type_of_Transmitter,
target1.ORG_INFORMATION5 Transmitter_Number,
target1.ORG_INFORMATION4 Type_of_Data,
target1.ORG_INFORMATION6 Type_of_Package,
target1.ORG_INFORMATION8 Source_of_RL_slips_used,
target1.ORG_INFORMATION9 Tech_Res_Person_Name,
target1.ORG_INFORMATION11 Tech_Res_Phone,
target1.ORG_INFORMATION10 Tech_Res_Area_Code,
target1.ORG_INFORMATION12 Tech_Res_Extension,
decode(target1.ORG_INFORMATION13,'E','A',
target1.ORG_INFORMATION13) Tech_Res_Language,
target1.ORG_INFORMATION14 Acct_Res_Person_Name,
target1.ORG_INFORMATION16 Acct_Res_Phone,
target1.ORG_INFORMATION15 Acct_Res_Area_Code,
target1.ORG_INFORMATION17 Acct_Res_Extension,
decode(target1.ORG_INFORMATION19,'E','A',
target1.ORG_INFORMATION19) Acct_Res_Language,
substr(target1.ORG_INFORMATION18,1,8) RL1_Slip_Number,
decode(target1.org_information3,'Y',target1.organization_id,
target1.ORG_INFORMATION20),
target1.ORG_INFORMATION3
from hr_organization_information target1 ,
hr_all_organization_units target2
where target1.organization_id = to_number(p_pre_organization_id)
and target2.business_group_id = l_business_group_id
and target2.organization_id = target1.organization_id
and target1.org_information_context = 'Prov Reporting Est';
select to_char(effective_date,'YYYY'),business_group_id
into l_taxation_year,l_business_group_id
from pay_payroll_actions
where payroll_action_id = p_payroll_action_id;
select
L.ADDRESS_LINE_1
, L.ADDRESS_LINE_2
, L.ADDRESS_LINE_3
, L.TOWN_OR_CITY
, DECODE(L.STYLE,'US',L.REGION_2,'CA',L.REGION_1,'CA_GLB',L.REGION_1,' ')
, replace(L.POSTAL_CODE,' ')
, L.COUNTRY
into
l_address_line_1
, l_address_line_2
, l_address_line_3
, l_town_or_city
, l_province_code
, l_postal_code
, l_country_code
from hr_all_organization_units O,
hr_locations_all L
where L.LOCATION_ID = O.LOCATION_ID
AND O.ORGANIZATION_ID = l_organization_id_of_qin;
select name
into l_transmitter_name
from hr_all_organization_units
where organization_id = l_transmitter_org_id;
select 'Y'
from dual
where exists (select null
from ff_archive_items fai
where fai.context1 = p_payroll_action_id);
select min(paa.chunk_number)
from pay_assignment_actions paa
where paa.payroll_action_id = p_payroll_action_id;
select hoi.organization_id ,
hoi.org_information5
from pay_payroll_actions ppa,
pay_assignment_actions paa,
hr_organization_information hoi
where paa.assignment_action_id = asgactid
and ppa.payroll_action_id = paa.payroll_action_id
and hoi.org_information2 =
pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',
ppa.legislative_parameters)
and hoi.org_information_context = 'Canada Employer Identification'
and hoi.org_information5 in ('T4/RL1','T4A/RL1')
order by organization_id;
select hoi.organization_id ,
hoi.org_information5
from pay_payroll_actions ppa,
pay_assignment_actions paa,
hr_organization_information hoi
where paa.assignment_action_id = asgactid
and ppa.payroll_action_id = paa.payroll_action_id
and hoi.org_information2 =
pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',
ppa.legislative_parameters)
and hoi.org_information_context = 'Canada Employer Identification'
and hoi.org_information5 in ('T4/RL1','T4A/RL1')
order by organization_id;
/* !!To calculate CPP withheld select all the GREs
the person has worked in */
/* 11510 changes for bug#3356533, replaced the old query for
cursor c_all_gres_for_person with this to improve performance.
Using assignment_id instead of assignment_action_id
*/
cursor c_all_gres_for_person(cp_asg_id number,cp_eff_date date) is
select distinct paa.tax_unit_id
from pay_assignment_actions paa,
pay_payroll_actions ppa,
per_all_assignments_f paf
where paa.assignment_id = cp_asg_id
and paf.assignment_id = cp_asg_id
and paf.assignment_id = paa.assignment_id
and paa.action_status = 'C'
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.effective_date <= cp_eff_date
and ppa.action_type in ('R', 'Q')
and ppa.effective_date between paf.effective_start_date and paf.effective_end_date
and exists ( select 1 from pay_run_types_f prt
where prt.legislation_code = 'CA'
and prt.run_type_id = paa.run_type_id
and prt.run_method <> 'C' );
select distinct context_value
from pay_action_contexts pac
where pac.assignment_id = l_asgid;
select distinct pet.element_information19,
pbt1.balance_name
from pay_balance_feeds_f pbf,
pay_balance_types pbt,
pay_balance_types pbt1,
pay_input_values_f piv,
pay_element_types_f pet,
fnd_lookup_values flv
where pbt.balance_name = p_balance_name
and pbf.balance_type_id = pbt.balance_type_id
and pbf.input_value_id = piv.input_value_id
and piv.element_type_id = pet.element_type_id
and pbt1.balance_type_id = pet.element_information10
and pet.business_group_id = l_business_group_id
and pet.element_information19 = flv.lookup_code
and flv.lookup_type = 'PAY_CA_RL1_FOOTNOTES'
--bug 5558604 starts
and flv.enabled_flag = 'Y'
and l_date_earned between nvl(flv.start_Date_active,l_date_earned)
and nvl(flv.end_date_Active,l_date_earned)
--bug 5558604 starts
and flv.language = userenv('LANG')
order by pet.element_information19;
select addr.address_line1,
addr.address_line2,
addr.address_line3,
addr.town_or_city,
decode(addr.country,'CA', addr.region_1 , 'US' , addr.region_2 , ' '),
replace(addr.postal_code,' '),
addr.telephone_number_1,
country.territory_code
from per_addresses addr,
fnd_territories_vl country
where addr.person_id = l_person_id
and addr.primary_flag = 'Y'
and l_date_earned between nvl(addr.date_from, l_date_earned)
and nvl(addr.date_to, l_date_earned)
and country.territory_code = addr.country
order by date_from desc;
select distinct prrv1.result_value,
prrv2.result_value,
hoi.organization_id,
run_ppa.effective_date,
run_paa.assignment_action_id
from pay_run_result_values prrv1
, pay_run_result_values prrv2
, pay_run_results prr
, pay_element_types_f pet
, pay_input_values_f piv1
, pay_input_values_f piv2
, pay_assignment_actions run_paa
, pay_payroll_actions run_ppa
, pay_assignment_actions arch_paa
, pay_payroll_actions arch_ppa
, per_all_assignments_f arch_paf
, per_all_assignments_f all_paf
, hr_all_organization_units hou
, hr_organization_information hoi
where arch_paa.assignment_action_id = cp_asgactid
and arch_ppa.payroll_action_id = arch_paa.payroll_action_id
and hou.business_group_id + 0 = arch_ppa.business_group_id
and hou.organization_id = hoi.organization_id
and hoi.org_information2 = pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',
arch_ppa.legislative_parameters)
and hoi.org_information_context = 'Canada Employer Identification'
and run_paa.tax_unit_id = hou.organization_id
and run_ppa.payroll_action_id = run_paa.payroll_action_id
and run_ppa.action_type in ( 'R', 'Q','B' )
and to_char(run_ppa.effective_date,'YYYY' ) = to_char(cp_eff_date,'YYYY')
and run_paa.action_status = 'C'
and pet.element_name = lv_footnote_element --'RL1 NonBox Footnotes'
and prr.assignment_action_id = run_paa.assignment_action_id
and prr.element_type_id = pet.element_type_id
and piv1.element_type_id = pet.element_type_id
and piv1.name = 'Message'
and prrv1.run_result_id = prr.run_result_id
and prrv1.input_value_id = piv1.input_value_id
and piv2.element_type_id = pet.element_type_id
and piv2.name = 'Amount'
and prrv2.run_result_id = prrv1.run_result_id
and prrv2.input_value_id = piv2.input_value_id
and arch_paf.assignment_id = arch_paa.assignment_id
and to_char(cp_eff_date,'YYYY')
between to_char(arch_paf.effective_start_date,'YYYY')
and to_char(arch_paf.effective_end_date,'YYYY')
and all_paf.person_id = arch_paf.person_id
and to_char(cp_eff_date,'YYYY')
between to_char(all_paf.effective_start_date,'YYYY')
and to_char(all_paf.effective_end_date,'YYYY')
and run_paa.assignment_id = all_paf.assignment_id
and exists (select 1
from pay_action_contexts pac,ff_contexts ffc
where ffc.context_name = 'JURISDICTION_CODE'
and pac.context_id = ffc.context_id
and pac.assignment_id = run_paa.assignment_id
and pac.context_value = 'QC');
select fai.value
from ff_archive_items fai,
ff_database_items fdi
where fdi.user_entity_id = fai.user_entity_id
and fai.context1 = cp_assignment_action_id
and fdi.user_name = 'CAEOY_RL1_PROVINCE_OF_EMPLOYMENT';
select ppa.report_type
from pay_payroll_actions ppa,pay_assignment_actions paa
where paa.assignment_action_id = cp_locked_actid
and ppa.payroll_action_id = paa.payroll_action_id;
select locked_action_id
from pay_action_interlocks
where locking_action_id = cp_locking_act_id;
select pei_information5,
pei_information6,
pei_information7
from per_people_extra_info
where person_id = cp_person_id
and pei_information6 = to_char(cp_pre_org_id)
and pei_information_category = 'PAY_CA_RL1_FORM_NO';
select paa.assignment_action_id
from pay_assignment_actions paa,
per_all_assignments_f paf,
per_all_people_f ppf,
pay_payroll_actions ppa,
pay_action_classifications pac,
pay_action_contexts pac1,
ff_contexts fc
where ppf.person_id = cp_person_id
and paf.person_id = ppf.person_id
and paf.assignment_id = paa.assignment_id
and paa.tax_unit_id = cp_tax_unit_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.effective_date between cp_period_start and cp_period_end
and ppa.effective_date between ppf.effective_start_date
and ppf.effective_end_date
and ppa.effective_date between paf.effective_start_date
and paf.effective_end_date
and ppa.action_type = pac.action_type
and pac.classification_name = 'SEQUENCED'
AND pac1.assignment_action_id = paa.assignment_action_id
AND pac1.context_id = fc.context_id
AND fc.context_name = 'JURISDICTION_CODE'
AND pac1.context_value = 'QC'
order by paa.action_sequence desc;
select paa.assignment_action_id
from pay_assignment_actions paa,
per_all_assignments_f paf,
per_all_people_f ppf,
pay_payroll_actions ppa,
pay_action_classifications pac
where ppf.person_id = cp_person_id
and paf.person_id = ppf.person_id
and paf.assignment_id = paa.assignment_id
and paa.tax_unit_id = cp_tax_unit_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.effective_date between cp_period_start and cp_period_end
and ppa.effective_date between ppf.effective_start_date
and ppf.effective_end_date
and ppa.effective_date between paf.effective_start_date
and paf.effective_end_date
and ppa.action_type = pac.action_type
and pac.classification_name = 'SEQUENCED'
order by paa.action_sequence desc;
SELECT aa.assignment_id,
pay_magtape_generic.date_earned (p_effective_date,aa.assignment_id),
aa.tax_unit_id,
aa.chunk_number,
aa.payroll_action_id,
aa.serial_number
into l_asgid,
l_date_earned,
l_tax_unit_id,
l_chunk,
l_payroll_action_id,
lv_serial_number
FROM pay_assignment_actions aa
WHERE aa.assignment_action_id = p_assactid;
select pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',
legislative_parameters),
business_group_id
into l_pre_organization_id,l_business_group_id
from pay_payroll_actions
where payroll_action_id = l_payroll_action_id;
select context_id
into l_jursd_context_id
from ff_contexts
where context_name = 'JURISDICTION_CODE';
select context_id
into l_taxunit_context_id
from ff_contexts
where context_name = 'TAX_UNIT_ID';
select decode(hoi.org_information3,'Y',hoi.organization_id,
hoi.org_information20)
into l_transmitter_name1
from pay_payroll_actions ppa,
hr_organization_information hoi,
hr_all_organization_units hou
WHERE hou.business_group_id = ppa.business_group_id
and hoi.organization_id = hou.organization_id
and hoi.org_information_context='Prov Reporting Est'
and hoi.organization_id =
pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',
ppa.legislative_parameters )
and ppa.payroll_action_id = l_payroll_action_id
and hoi.org_information4 = 'P01';
select to_number(target.ORG_INFORMATION18)
into l_rl1_last_slip_number
from hr_organization_information target
where target.organization_id = l_transmitter_name1
and target.org_information_context = 'Prov Reporting Est'
and target.ORG_INFORMATION3 = 'Y';
select l_rl1_last_slip_number + pay_ca_eoy_rl1_s.nextval - 1
into l_rl1_curr_slip_number from dual;
select mod(l_rl1_curr_slip_number,7)
into l_rl1_slip_number_last_digit
from dual;
/* Removed select stmt to get max asgact_id and replaced it with
cursor c_get_max_asgactid_jd. 11510 Changes Bug#3356533.
Changed the cursor to get max asgact_id based on person_id to
fix bug#3638928 */
open c_get_max_asgactid_jd(to_number(lv_serial_number),
l_tax_unit_id,
l_year_start,
l_year_end);
select target1.business_group_id
into l_business_group_id
from hr_all_organization_units target1
where target1.organization_id = l_tax_unit_id;
/* Removed select stmt to get max asgact_id and replaced
it with cursor c_get_max_asgactid_jd, reusing the same
cursor used above. 11510 Changes Bug#3356533. Changed
cursor to get max asg_act_id based on person_id to
fix bug#3638928. */
open c_get_max_asgactid_jd(to_number(lv_serial_number),
l_ft_tax_unit_id,
l_year_start,
l_year_end);
select target1.business_group_id
into l_business_group_id
from hr_all_organization_units target1
where target1.organization_id = l_ft_tax_unit_id;
/* Removed the select stmt to get max asgact_id and replaced it
with cursor c_get_max_asgactid. 11510 changes for bug#3356533.
Changed the cursor to get max asg_act_id based on person_id
to fix bug#3638928. */
open c_get_max_asgactid(to_number(lv_serial_number),
l_tax_unit_id,
l_year_start,
l_year_end);
select fnd_number.canonical_to_number(information_value)
into lv_max_pensionable_earnings
from pay_ca_legislation_info
where information_type = 'MAX_CPP_EARNINGS'
and l_year_end between start_date
and end_date;
select PEOPLE.person_id,
PEOPLE.first_name,
PEOPLE.middle_names,
PEOPLE.last_name,
PEOPLE.employee_number,
PEOPLE.date_of_birth,
replace(PEOPLE.national_identifier,' '),
PEOPLE.pre_name_adjunct,
NVL(PHONE.phone_number,PEOPLE.work_telephone)
into l_person_id,
l_first_name,
l_middle_name,
l_last_name,
l_employee_number,
l_date_of_birth,
l_national_identifier,
l_pre_name_adjunct,
l_employee_phone_no
from per_all_assignments_f ASSIGN
,per_all_people_f PEOPLE
,per_person_types PTYPE
,per_phones PHONE
,fnd_sessions SES
where l_date_earned BETWEEN ASSIGN.effective_start_date
AND ASSIGN.effective_end_date
and ASSIGN.assignment_id = l_asgid
and PEOPLE.person_id = ASSIGN.person_id
and l_date_earned BETWEEN PEOPLE.effective_start_date
AND PEOPLE.effective_end_date
and PTYPE.person_type_id = PEOPLE.person_type_id
and PHONE.parent_id (+) = PEOPLE.person_id
and PHONE.parent_table (+)= 'PER_ALL_PEOPLE_F'
and PHONE.phone_type (+)= 'W1'
and l_date_earned
BETWEEN NVL(PHONE.date_from,l_date_earned)
AND NVL(PHONE.date_to,l_date_earned)
and SES.session_id = USERENV('SESSIONID');
select PEOPLE.person_id,
PEOPLE.first_name,
PEOPLE.middle_names,
PEOPLE.last_name,
PEOPLE.employee_number,
PEOPLE.date_of_birth,
replace(PEOPLE.national_identifier,' '),
PEOPLE.pre_name_adjunct
into l_person_id,
l_first_name,
l_middle_name,
l_last_name,
l_employee_number,
l_date_of_birth,
l_national_identifier,
l_pre_name_adjunct
from per_all_assignments_f ASSIGN
,per_all_people_f PEOPLE
where ASSIGN.assignment_id =l_asgid
and PEOPLE.person_id = ASSIGN.person_id
-- code fix started for 6440125
and l_date_earned BETWEEN ASSIGN.effective_start_date
AND ASSIGN.effective_end_date
and l_date_earned BETWEEN PEOPLE.effective_start_date
AND PEOPLE.effective_end_date;
select max(date_start)
,max(actual_termination_date)
into l_hire_date
,l_termination_date
from per_periods_of_service
where person_id = l_person_id;
select to_char(effective_date,'YYYY'),
report_type,
to_number(pay_ca_eoy_rl1_amend_arch.get_parameter('PRE_ORGANIZATION_ID'
,legislative_parameters))
into lv_fapp_effective_date,
lv_fapp_report_type,
ln_fapp_pre_org_id
from pay_payroll_actions
where payroll_action_id = l_payroll_action_id;
Purpose : This returns the select statement that is used to created the
range rows for the Year End Pre-Process.
Arguments :
Notes :
*/
procedure eoy_range_cursor (pactid in number, sqlstr out nocopy varchar2) is
l_pre_organization_id varchar2(50);
select pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',
legislative_parameters),
trunc(effective_date,'Y'),
effective_date,
business_group_id
into l_pre_organization_id,
l_year_start,
l_year_end,
l_business_group
from pay_payroll_actions
where payroll_action_id = pactid;
sqlstr := 'select distinct asg.person_id
from pay_all_payrolls_f ppy,
pay_payroll_actions ppa,
pay_assignment_actions paa,
per_all_assignments_f asg,
pay_payroll_actions ppa1
where ppa1.payroll_action_id = :payroll_action_id
and ppa.effective_date between
fnd_date.canonical_to_date('''||
fnd_date.date_to_canonical(l_year_start)||''') and
fnd_date.canonical_to_date('''||
fnd_date.date_to_canonical(l_year_end)||''')
and ppa.action_type in (''R'',''Q'',''V'',''B'',''I'')
and ppa.action_status = ''C''
and ppa.business_group_id + 0 = '||to_char(l_business_group)||'
and ppa.payroll_action_id = paa.payroll_action_id
and paa.tax_unit_id in
(select hoi.organization_id
from hr_organization_information hoi
where hoi.org_information_context = ''Canada Employer Identification''
and hoi.org_information2 = '''|| l_pre_organization_id ||''''||'
and hoi.org_information5 in (''T4/RL1'',''T4A/RL1''))
and paa.action_status = ''C''
and paa.assignment_id = asg.assignment_id
and ppa.business_group_id = asg.business_group_id + 0
and ppa.effective_date between asg.effective_start_date
and asg.effective_end_date
and asg.assignment_type = ''E''
and ppa.payroll_id = ppy.payroll_id
and ppy.business_group_id = '||to_char(l_business_group)||'
and exists (select 1
from pay_action_contexts pac,
ff_contexts fc
where pac.assignment_id = paa.assignment_id
and pac.assignment_action_id = paa.assignment_action_id
and pac.context_id = fc.context_id
and fc.context_name = ''JURISDICTION_CODE''
and pac.context_value = ''QC'' )
order by asg.person_id';