[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select clause as this cursor selects
the data for a particular GRE.
05-DEC-2003 SSattineni 115.51 The Negative Balance Exists flag
was archiving incorrect for some
employees, so initialised the
flag value with 'N'.
05-FEB-2004 SSattineni 115.52 Fixed the bug#3422384, added
additional logic to archive the
CPP/QPP Exempt flag and EI Exempt
flag correctly for an employee.
06-FEB-2004 mmukherj 115.53 Added cursor c_get_latest_actid to
improve performance of getting latest
assignment action id.
19-FEB-2004 SSattineni 115.55 Modified c_get_date_of_birth cursor
to address the terminated employees
issue for Box 28 validation. Part of
fix#3422384.
02-JUL-2004 mmukherj 115.56 Modified c_eoy_gre further to make
it more performant.
09-AUG-2004 SSattineni 115.58 Modified eoy_action_creation procedure
to check 'T4 Non Taxable Earnings',
'Gross Earnings' and 'T4 No Gross
Earnings' balance values before
creating the assignment action for T4.
Fix for bug#3267520.
20-AUG-2004 rigarg 115.59 Fix for bug#3564076
Added archiver for DBI's for Technical
Contact Extension and EMail.
24-AUG-2004 ssmukher 115.60 Fix for bug# 3447439.Modified the
cursor c_get_latest_asg to fetch the
earn date and assignment action id.
This earn date will be used to fetch
the CPP/QPP and EE exempt flag for
an employee in a particular province.
02-NOV-2004 rigarg 115.61 Fix for bug# 3973040. Removed
Transmitter Code 904 check.
10-NOV-2004 ssouresr 115.63 Modified to use tables instead of
views to remove problems with
security groups
12-NOV-2004 ssouresr 115.64 Added a date range to the cursor
c_get_latest_asg to make sure records
are only picked up in the year
22-NOV-2004 mmukherj 115.65 bugfix #4025926
01-DEC-2004 mmukherj 115.66 Archiving QPP Reduced Subject. Because
this amunt has to be printed in BOX26
for QC employee. Bugfix 4031227.
02-DEC-2004 ssouresr 115.67 Added error message for security group
07-DEC-2004 ssouresr 115.68 Removed the changes made for 3447439
in 115.60
as this was impacting performance
08-JUN-2005 ssouresr 115.69 Removed error message for security
group
13-JUN-2005 mmukherj 115.70 Bug fix #4026689. Added call to
eoy_archive_gre_data in
eoy_archive_data. So that when the
Retry process calls eoy_archive_data,
it re-archives the employer and
transmitter data.
29-JUL-2005 ssmukher 115.71 Bug Fix #4034155 Added code to remove
the other information amounts from
the Box 14
03-AUG-2005 ssmukher 115.72 Bug Fix #4034155 Added code for
checking the other information
amt total not to exceed the gross
earnings total displayed in Box 14.
Also modified the check condition for
flag l_negative_balance_exists
in eoy_archive_data procedure.
05-AUG-2005 saurgupt 115.73 Bug 4517693: Added Address_line3 for
T4 archiver.
11-Aug-2005 ssmukher 115.74 Bug 4547415 Substracted the amount
associated with code 31,53 and 78
from the Grosss Earnings(box 14)
26-AUG-2005 mmukherj 115.75 Commented out the use of two cursors
c_eoy_all and eoy_all_range. Since
GRE is a mandatory parameter for
Federal Yearend Archiver Process
these two cursors will never be used.
14-Sep-2005 ssmukher 115.76 Bug Fix 4028693 .Archive 0 value for
'Gross Earnings' when the Employment
code is either 11,12,13 and 17
26-OCT-2005 ssouresr 115.77 range_cursor has been modified to
avoid using hr_soft_coding_keyflex
04-NOV-2005 ssouresr 115.78 Removed archiving of the Federal Youth
Hire indicator flag
4-NOV-2005 pganguly 115.79 Fixed bug# 4033041. Commented out
archiver code for T4_BOX50.
3-MAR-2006 ssmukher 115.80 Fixed Bug #5041252 .Removed the
per_all_assignments_f table check
from the select statement in the
procedure eoy_archive_data to fetch
the CPP/QPP exempt flag
from pay_ca_emp_prov_tax_info_f.
25-Jul-2006 ssmukher 115.81 Made modification in the
eoy_archive_data procedure to
incorporate the PPIP tax.
28-AUG-2006 pganguly 115.82 Fixed bug# 4025900. Changed the code
for Box 14 so that it subtracts OTHER
_INFORMATION71 before archiving.
30-Aug-2007 ssmukher 115.83 Bug 5706114 fix.T4 Box44 and T4 Box20
should not be reported for Status
indian employee.Modified the proc
eoy_archive_data.
4-SEPT-2007 ssmukher 115.84 Fix for bug# 3447439.Modified the
cursor c_get_latest_asg in
eoy_archive_data to fetch the
earn date and assignment action id.
This earn date will be used to fetch
the CPP/QPP and EI exempt flag for
an employee in a particular province.
6-SEPT-2007 amigarg 115.85 Fix for bug# 5698016.Added the
T4_other_info_amount for code 81-85.
19-SEP-2007 amigarg 115.87 Fix for bug# 6399498.archived the
registration number for status_indian
11-DEC-2007 tclewis 115.88 In the package eoy_action_creation modified
The cursor c_eoy_gre removed the subquery
Modified the cursor c_get_latest_asg added
Hints.
19-SEP-2008 sneelapa 115.89 Fix for bug# 6399498.
During QA testing bug 6399498 was reopened.
Modified CURSOR LOOP of c_balance_feed_info
and IF condition before CURSOR LOOP
so that c_balance_feed_info CURSOR will
get the "registration number" value
of T4_BOX52 Element incase of Status Indian Employee
and for non status indian employee get
the reg number of T4_BOX52 if value
for T4_BOX52 exists else get reg number
of T4_BOX20.
23-SEP-2008 sneelapa 115.91 Fix for bug# 6399498.
25-SEP-2008 sneelapa 115.93 Fix for bug# 6399498.
Modified CURSOR Query of c_balance_feed_info
previous version of package date was hardcoded
as '31-DEC-4712', which is against coding standards.
26-SEP-2008 sneelapa 115.95 Fix for bug# 6399498.
Modified CURSOR Query of c_balance_feed_info
WHERE Condition pee.effective_start_date >= l_year_start
is modified as
pee.effective_start_date <= l_year_end
IF an Employee is having two PA elements
One attached in 2006 and second one in 2008
and Archiver is run for 2008, 2006 Element was
not picked up.
30-OCT-2009 aneghosh 115.96 Fix for Bug 8576897.
Removed the deduction of Box 53 from
Box14.
09-DEC-2009 sneelapa 115.97 Fix for Bug 9135405.
Modified eoy_archive_data procedure to
archive data for T4 Other Info new codes.
31-DEC-2009 sneelapa 115.98 Fix for Bug 9135405.
Modified eoy_archive_data procedure to
not to include Other Info Codes 66 to 69
in Gross Income (Box 14).
09-NOV-2010 sneelapa 115.99 Fix for Bug 10097860.
Modified eoy_archive_data procedure to
to archive Other Info Code 86
30-DEC-2010 sneelapa 115.100 Fix for Bug 10388148.
Modified eoy_archive_gre_data procedure to
to archive Accounting contact details
and Proprietor SIN numbers of
Transmitter GRE for Non Transmitter GRE
if Accounting contact details and
Proprietor SIN numbers are not feeded
for Non Transmitter GRE.
01-Aug-2011 rgottipa 115.101 Modified eoy_archive_data procedure to
archive correct registration number
for Box50.
29-Aug-2011 sneelapa 115.102 10399514 Introduced new CURSOR c_eoy_gre_range
it will be called in place of
c_eoy_gre CURSOR, if RANGE_PERSON_ID
is enabled.
30-Oct-2011 rgottipa 115.103 10244185 Introduced new CURSOR c_get_t4code_limits
to get max limit for code 87.
Not archiving code 53 from year 2011.
14-Nov-2011 sneelapa 115.104 7611439 Modified logic for archiving
CAEOY_T4_EMPLOYEE_REGISTRATION_NO to archive
Jurisdiction Code and Tax Unit ID contexts.
23-Nov-2011 sneelapa 115.105 7611439 Modified logic to decide based on which
balance c_balance_feed_info to be opened
for archiving CAEOY_T4_EMPLOYEE_REGISTRATION_NO.
02-Feb-2012 pracagra 115.106 13615110 Modified the hint to the cursor 'c_eoy_gre_range'
to improve the performance of the 'CA Year End
Preprocess'.
20-Jul-2012 rgottipa 115.107 13797428 Modified the cursor 'c_balance_feed_info' by
passing assignment_id as one of the parameter.
This is because to get the l_registration_no
for terminated assignments.
29-Aug-2012 rgottipa 115.108 13505953 added 'if' condition while archiving data for
'CAEOY_EMPLOYMENT_CODE'(Box29) to avoid
archiving of Box29 value for other prov
if employment code specified for specific
province.
04-Sep-2012 rgottipa 115.109 13501503 Trying to fetch l_cpp_exempt_flag and
l_ei_exempt_flag flags at each
assignment level with consideration of
jurisdiction. Modified the CURSOR
c_diff_assignments.
07-Sep-2012 sgotlasw 115.110 11655053 Added user entity for archiving 'EI ER Liability'.
18-Sep-2012 rgottipa 115.111 13505953 Added logic to set l_box14_flag when
employment_code (Box 29) exists.
22-Nov-2012 sneelapa 115.112 13087530 Added logic to add "Taxable Benefits without Remuneration"
balance value to CPP_EE_TAXABLE or CPP_EE_TAXABLE
depending on Employee Jurisdiction.
28-Feb-2012 rgottipa 115.114 16173065 Re written the CURSOR c_balance_feed_info
so that it will bring REGISTRATION NUMBER from the
elements which are processed for the employee
irrespective of how they have fed (directly or indirctly).
*/
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'
and UE.legislation_code = 'CA';
/* 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_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_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 = 'T4' then
/* Default settings for Year End Pre-process. */
p_period_start := p_year_start;
end get_selection_information;
/* 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
WHERE
ASG.business_group_id = l_bus_group_id AND
asg.assignment_type = 'E' AND
ASG.person_id between stperson and endperson AND
EXISTS
(SELECT 1
FROM pay_payroll_actions ppa,
pay_assignment_actions paa
WHERE
ppa.business_group_id = l_bus_group_id AND
ppa.payroll_action_id = paa.payroll_action_id AND
ppa.action_type in ('R','Q','V','B','I') AND
ppa.effective_date BETWEEN ASG.effective_start_date AND
ASG.effective_end_date AND
ppa.effective_date between l_period_start AND
l_period_end AND
paa.assignment_id = ASG.assignment_id AND
paa.tax_unit_id = l_eoy_tax_unit_id)
ORDER BY 1, 3 DESC, 2;
SELECT /*+ Ordered
INDEX (asg PER_ASSIGNMENTS_F_N12)
INDEX(paa PAY_ASSIGNMENT_ACTIONS_N51)
INDEX(ppa PAY_PAYROLL_ACTIONS_PK) */
DISTINCT ASG.person_id person_id
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.action_status = 'C'
and ppa.business_group_id + 0 = l_bus_group_id
and ppa.payroll_action_id = paa.payroll_action_id
and paa.tax_unit_id = l_eoy_tax_unit_id
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.person_id between stperson and endperson
AND ASG.assignment_type = 'E';
SELECT /*+ leading(ppr,asg,paa) */ --Modified for bug 13615110
DISTINCT ASG.person_id person_id
FROM
per_all_assignments_f ASG,
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_population_ranges ppr
WHERE ppa.effective_date between l_period_start
and l_period_end
and ppa.action_type in ('R','Q','V','B','I')
and ppa.action_status = 'C'
and ppa.business_group_id + 0 = l_bus_group_id
and ppa.payroll_action_id = paa.payroll_action_id
and paa.tax_unit_id = l_eoy_tax_unit_id
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.person_id between stperson and endperson
AND ppr.payroll_action_id = pactid
AND ppr.chunk_number = chunk
AND ppr.person_id = ASG.person_id
AND ASG.assignment_type = 'E';
/* SELECT DISTINCT
ASG.person_id person_id
FROM
per_all_assignments_f ASG,
pay_all_payrolls_f PPY
WHERE exists
(select /*+ INDEX(paa PAY_ASSIGNMENT_ACTIONS_N51)
INDEX(ppa PAY_PAYROLL_ACTIONS_PK) */
/* 'x'
from pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.effective_date between l_period_start
and l_period_end
and ppa.action_type in ('R','Q','V','B','I')
and ppa.action_status = 'C'
and ppa.business_group_id + 0 = l_bus_group_id
and ppa.payroll_action_id = paa.payroll_action_id
and paa.tax_unit_id = l_eoy_tax_unit_id
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.person_id between stperson and endperson
AND ASG.assignment_type = 'E'
AND PPY.payroll_id = ASG.payroll_id;
SELECT ASG.person_id person_id,
ASG.assignment_id assignment_id,
to_number(SCL.segment1) tax_unit_id,
ASG.effective_end_date effective_end_date
FROM per_all_assignments_f ASG,
hr_soft_coding_keyflex SCL,
pay_all_payrolls_f PPY
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 PPY.payroll_id = ASG.payroll_id
ORDER BY 1, 3, 4 DESC, 2;
SELECT assignment_id
from per_all_assignments_f paf
where person_id = p_person_id
and primary_flag = 'Y'
and paf.effective_start_date <= l_period_end
and paf.effective_end_date >= l_period_start
and paf.assignment_type = 'E'
ORDER BY assignment_id desc;
select /*+ Ordered
INDEX (asg PER_ASSIGNMENTS_F_N12)
INDEX(paa PAY_ASSIGNMENT_ACTIONS_N51)
INDEX(ppa PAY_PAYROLL_ACTIONS_PK) */
paa.assignment_action_id
from per_all_assignments_f paf,
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_action_classifications pac
where paf.person_id = p_person_id
and paa.assignment_id = paf.assignment_id
and paa.tax_unit_id = l_tax_unit_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type = pac.action_type
and pac.classification_name = 'SEQUENCED'
and ppa.effective_date +0 between paf.effective_start_date
and paf.effective_end_date
and ppa.effective_date +0 between l_year_start and
l_year_end
and ((nvl(paa.run_type_id, ppa.run_type_id) is null
and paa.source_action_id is null)
or (nvl(paa.run_type_id, ppa.run_type_id) is not null
and paa.source_action_id is not null )
or (ppa.action_type = 'V' and ppa.run_type_id is null
and paa.run_type_id is not null
and paa.source_action_id is null))
order by paa.action_sequence desc;
select paa.assignment_action_id
from pay_assignment_actions paa,
per_all_assignments_f paf,
pay_payroll_actions ppa,
pay_action_classifications pac
where paf.person_id = p_person_id
and paa.assignment_id = paf.assignment_id
and paa.tax_unit_id = l_tax_unit_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type = pac.action_type
and pac.classification_name = 'SEQUENCED'
and ppa.effective_date +0 between paf.effective_start_date
and paf.effective_end_date
and ppa.effective_date +0 between l_year_start and
l_year_end
and ((nvl(paa.run_type_id, ppa.run_type_id) is null
and paa.source_action_id is null)
or (nvl(paa.run_type_id, ppa.run_type_id) is not null
and paa.source_action_id is not null )
or (ppa.action_type = 'V' and ppa.run_type_id is null
and paa.run_type_id is not null
and paa.source_action_id is null))
order by paa.action_sequence desc;
select effective_date,
report_type,
-- Added for bug 10399514
report_qualifier,
report_category,
-- Added for bug 10399514
business_group_id,
legislative_parameters
into l_effective_date,
l_report_type,
-- Added for bug 10399514
l_state,
l_report_cat,
-- Added for bug 10399514
l_bus_group_id,
l_legislative_parameters
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_tax_unit_context,
l_jurisdiction_context);
select report_format
into l_report_format
from pay_report_format_mappings_f
where report_type = l_report_type
and report_qualifier = l_state
and report_category = l_report_cat ;
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 we can use in the Magnetic Media process
to do an additional check against the assignment table */
hr_utility.trace('updating assignment action');
update pay_assignment_actions aa
set aa.serial_number = to_char(l_person_id)
where aa.assignment_action_id = lockingactid;
select fdi.user_entity_id
into l_user_entity_id
from ff_database_items fdi,
ff_user_entities fui
where user_name = p_dbi_name
and fdi.user_entity_id = fui.user_entity_id
and fui.legislation_code = 'CA';
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 = cp_jurisdiction
AND FAI1.CONTEXT1 = cp_asg_act_id
AND fdi1.user_name <> 'CAEOY_T4_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;
select
business_group_id
from
hr_all_organization_units
where
organization_id = p_tax_unit_id1;
select
nvl(hoi6.ORG_INFORMATION9,hou.name) GRE_stat_report_name,
hoi6.ORG_INFORMATION1 Employer_identification_number,
hl.ADDRESS_LINE_1 GRE_addrline1,
hl.ADDRESS_LINE_2 GRE_addrline2,
hl.ADDRESS_LINE_3 GRE_addrline3,
hl.TOWN_OR_CITY GRE_town_or_city,
DECODE(hl.STYLE , 'US' , hl.REGION_2 ,
'CA' , hl.REGION_1 ,
'CA_GLB',hl.region_1, ' ') GRE_province,
hl.POSTAL_CODE GRE_postal_code,
hl.COUNTRY GRE_country,
hoi6.org_information3 ei_rate
from
hr_all_organization_units hou,
hr_organization_information hoi6,
hr_locations_all hl
where
hou.organization_id = p_tax_unit_id
and hou.organization_id = hoi6.organization_id
and hoi6.org_information_context = 'Canada Employer Identification'
and hoi6.org_information5 in ('T4/RL1','T4/RL2')
and hou.location_id = hl.location_id;
select
hoi5.ORG_INFORMATION10 GRE_acct_contact_name,
hoi5.ORG_INFORMATION12 GRE_acct_contact_phone,
hoi5.ORG_INFORMATION11 GRE_acct_contact_area_code,
hoi5.ORG_INFORMATION13 GRE_acct_contact_extn,
hoi5.ORG_INFORMATION14 GRE_Proprietor_SIN#1,
hoi5.ORG_INFORMATION15 GRE_Proprietor_SIN#2/*,
hoi5.ORG_INFORMATION16 GRE_Fedyouth_hire_Prgind*/
from
hr_organization_information hoi5
where
hoi5.organization_id = p_tax_unit_id
and hoi5.org_information_context = 'Fed Magnetic Reporting';
select
nvl(hoi3.ORG_INFORMATION9,hou.name) trans_stat_report_name,
hl.ADDRESS_LINE_1 trans_addrline1,
hl.ADDRESS_LINE_2 trans_addrline2,
hl.ADDRESS_LINE_3 trans_addrline3,
hl.TOWN_OR_CITY trans_town_or_city,
DECODE(hl.STYLE , 'US', hl.REGION_2,
'CA', hl.REGION_1,
'CA_GLB',hl.region_1, ' ') trans_province,
hl.POSTAL_CODE trans_postal_code,
hl.COUNTRY trans_country,
hoi2.org_information5 trans_type_indicator,
hoi2.ORG_INFORMATION4 trans_number,
hoi2.ORG_INFORMATION2 trans_type_code,
hoi2.ORG_INFORMATION3 trans_datatype_code,
hoi2.ORG_INFORMATION6 trans_tech_contact_name,
hoi2.ORG_INFORMATION8 trans_tech_contact_phone,
hoi2.ORG_INFORMATION7 trans_tech_contact_areacode,
hoi2.ORG_INFORMATION9 trans_tech_contact_lang,
-- Added for bug 10388148
hoi2.ORG_INFORMATION10 trans_acct_contact_name,
hoi2.ORG_INFORMATION11 trans_acct_contact_area_code,
hoi2.ORG_INFORMATION12 trans_acct_contact_phone,
hoi2.ORG_INFORMATION13 trans_acct_contact_extn,
hoi2.ORG_INFORMATION14 trans_proprietor_SIN#1,
hoi2.ORG_INFORMATION15 trans_proprietor_SIN#2,
-- Added for bug 10388148
hoi2.ORG_INFORMATION17 trans_tech_contact_extn,
hoi2.ORG_INFORMATION18 trans_tech_contact_email
from
hr_all_organization_units hou,
hr_organization_information hoi2,
hr_organization_information hoi3,
hr_locations_all hl
where
hou.organization_id = p_transmitter_gre_id
and hou.organization_id = hoi2.organization_id
and hoi2.org_information_context = 'Fed Magnetic Reporting'
and hoi2.org_information1 = 'Y'
-- and hoi2.org_information2 = '904' --comented for bug 3973040
and hou.organization_id = hoi3.organization_id
and hoi3.org_information_context = 'Canada Employer Identification'
and hou.location_id = hl.location_id;
select to_char(effective_date,'YYYY'),
add_months(trunc(effective_date, 'Y'),12) - 1
into l_taxation_year,
l_effective_date
from pay_payroll_actions
where payroll_action_id = p_payroll_action_id;
select context_id
into l_taxunit_context_id
from ff_contexts
where context_name = 'TAX_UNIT_ID';
select 'Y'
from dual
where exists (select null
from ff_archive_items fai
where fai.context1 = p_payroll_action_id
and archive_type = 'PA');
select min(paa.chunk_number)
from pay_assignment_actions paa
where paa.payroll_action_id = p_payroll_action_id;
SELECT
PEOPLE.person_id,
PEOPLE.first_name,
PEOPLE.last_name,
PEOPLE.employee_number,
replace(PEOPLE.national_identifier,' '),
PEOPLE.middle_names,
ASSIGN.organization_id,
ASSIGN.location_id
FROM
per_all_assignments_f ASSIGN,
per_all_people_f PEOPLE
WHERE ASSIGN.assignment_id = p_asgid
and l_date_earned BETWEEN ASSIGN.effective_start_date
AND ASSIGN.effective_end_date
AND PEOPLE.person_id = ASSIGN.person_id
AND PEOPLE.effective_end_date = (select max(effective_end_date) from
per_all_people_f PEOPLE1
where PEOPLE1.person_id = PEOPLE.person_id);
select distinct context_value
from pay_action_contexts pac
where pac.assignment_id = p_asgid;
select province_abbrev
from pay_ca_provinces_v pac;
select nvl(pet.element_information20,'NOT FOUND'),
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
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.element_information_category = 'CA_EARNINGS'
and pet.business_group_id = l_business_group_id
and pet.element_information20 is not null;
select nvl(pet.element_information20,'NOT FOUND'),
pbt1.balance_name,
pev.screen_entry_value
from pay_balance_feeds_f pbf,
pay_balance_types pbt,
pay_balance_types pbt1,
pay_input_values_f piv,
pay_element_types_f pet,
pay_element_entries_f pee,
pay_element_entry_values_f pev
where pbt.balance_name = p_balance_name
and pee.assignment_id = p_each_asgid
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_information20 is not null
and pet.element_type_id = pee.element_type_id
-- and trunc(p_effective_date) between pee.effective_start_date and pee.effective_end_date
and ((pee.effective_start_date <= l_year_end
and pee.effective_end_date = to_date('31-12-4712','DD-MM-RRRR'))
or
(pee.effective_end_date between l_year_start and l_year_end))
-- and trunc(p_effective_date) between pev.effective_start_date and pev.effective_end_date
and ((pev.effective_start_date <= l_year_end
and pev.effective_end_date = to_date('31-12-4712','DD-MM-RRRR'))
or
(pev.effective_end_date between l_year_start and l_year_end))
and pee.element_entry_id = pev.element_entry_id
-- and fnd_number.canonical_to_number(pev.screen_entry_value) >= 0
-- and pet.element_information_category = 'CA_EARNINGS'
-- and pev.input_value_id = piv.input_value_id
; */
select nvl(pet.element_information20,'NOT FOUND'),pbtl.balance_name,rv.result_value
from pay_assignment_actions pa,
pay_payroll_actions ppa,
per_all_assignments_f paf,
pay_element_types_f pet,
pay_run_results r,
pay_run_result_values rv,
pay_input_values_f piv,
pay_balance_feeds_f pbf,
pay_balance_types pbt,
pay_balance_types pbtl
where ppa.payroll_action_id = pa.payroll_action_id
and r.run_result_id=rv.run_result_id
and pet.element_type_id=r.element_type_id
and r.assignment_action_id=pa.assignment_action_id
and piv.input_value_id=rv.input_value_id
and paf.assignment_id = pa.assignment_id
and paf.assignment_id = p_each_asgid
and paf.business_group_id = l_business_group_id
and 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 pet.element_information20 is not null
and pbtl.balance_type_id = pet.element_information10;
select distinct(paf.assignment_id)
from per_all_assignments_f paf
,per_all_people_f ppf
,hr_soft_coding_keyflex_kfv hsc
,hr_locations_all hl
where ppf.person_id = p_person_id
and ppf.person_id = paf.person_id
and hsc.segment1 = l_tax_unit_id
and paf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
and DECODE(hl.STYLE , 'US' , hl.REGION_2 ,
'CA' , hl.REGION_1 ,
'CA_GLB',hl.region_1, ' ') = l_jurisdiction
and hl.location_id = paf.location_id
and paf.effective_start_date <= l_year_end
and paf.effective_end_date >= l_year_start;
select business_group_id
from hr_all_organization_units
where organization_id = p_tax_unit_id1;
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_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_information2,
pei_information3
from per_people_extra_info
where person_id = cp_person_id
and pei_information_category = 'ADDITIONAL_T4_INFORMATION'
and pei_information1 = cp_gre;
select /*+ Ordered */
paa.assignment_action_id,
ppa.date_earned
from per_all_assignments_f paf,
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_action_classifications pac,
pay_action_contexts pac1,
ff_contexts fc
where paf.person_id = p_person_id
and paa.assignment_id = paf.assignment_id
and paa.tax_unit_id = l_tax_unit_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type = pac.action_type
and pac.classification_name = 'SEQUENCED'
and ppa.effective_date +0 between paf.effective_start_date
and paf.effective_end_date
and ppa.effective_date +0 between l_year_start
and l_year_end
and ((nvl(paa.run_type_id, ppa.run_type_id) is null
and paa.source_action_id is null)
or (nvl(paa.run_type_id, ppa.run_type_id) is not null
and paa.source_action_id is not null )
or (ppa.action_type = 'V' and ppa.run_type_id is null
and paa.run_type_id is not null
and paa.source_action_id is null))
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 = p_jurisdiction
order by paa.action_sequence desc;
select paa.assignment_action_id,
ppa.date_earned
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+0 between l_year_start
and l_year_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 = cp_jurisdiction
order by paa.action_sequence desc;
select ppf.date_of_birth
from per_all_people_f ppf
where ppf.person_id = ln_person_id
and ppf.effective_end_date = (select max(ppf2.effective_end_date)
from per_all_people_f ppf2
where ppf2.person_id= ln_person_id
and ppf2.effective_start_date
<= ld_eff_date);
select ca_tax_information1
from pay_ca_emp_fed_tax_info_f pca
where pca.assignment_id = cp_assign
and cp_effec_date between pca.effective_start_date and
pca.effective_end_date;
select information_value
from pay_ca_legislation_info
where lookup_type = 'T4ARCHIVE'
and lookup_code = cp_lookup_code
and cp_eff_date between start_date and end_date;
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 org_information11
into l_transmitter_gre_id
from hr_organization_information
where organization_id = l_tax_unit_id
and org_information_context = 'Canada Employer Identification';
SELECT
paa1.assignment_action_id
INTO
l_aaid
FROM
pay_assignment_actions paa1,
per_all_assignments_f paf2
WHERE
paa1.assignment_id = paf2.assignment_id
and paa1.tax_unit_id = l_tax_unit_id
and (paa1.action_sequence , paf2.person_id) =
(SELECT MAX(paa.action_sequence), paf.person_id
FROM pay_action_classifications pac,
pay_payroll_actions ppa,
pay_assignment_actions paa,
per_all_assignments_f paf1,
per_all_assignments_f paf
WHERE paf.assignment_id = l_asgid
AND paf1.person_id = paf.person_id
AND paa.tax_unit_id = l_tax_unit_id
AND paa.assignment_id = paf1.assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.action_type = pac.action_type
AND pac.classification_name = 'SEQUENCED'
AND ppa.effective_date <= p_effective_date
group by paf.person_id)
and rownum < 2;
select distinct(person_id) into l_each_person_id
from per_all_assignments_f where assignment_id = l_asgid;
/* Removed the per_all_assignments_f join the select stmt for bug fix 5041252 */
SELECT decode(target.CPP_QPP_EXEMPT_FLAG,'Y','X',NULL),
decode(target.EI_EXEMPT_FLAG,'Y','X',NULL)
INTO l_cpp_exempt_flag,
l_ei_exempt_flag
FROM pay_ca_emp_fed_tax_info_f target
WHERE target.assignment_id = l_each_asgid
and lv_actual_date/*p_effective_date*/ between target.effective_start_date
and target.effective_end_date;
SELECT decode(target.QPP_EXEMPT_FLAG,'Y','X',NULL),
decode(target.PPIP_EXEMPT_FLAG,'Y','X',NULL)
INTO lv_qpp_exempt_flag,
l_ppip_exempt_flag
FROM pay_ca_emp_prov_tax_info_f target
WHERE target.assignment_id = l_each_asgid
and target.province_code = 'QC'
and lv_actual_date/*p_effective_date */ between target.effective_start_date
and target.effective_end_date;
select distinct(person_id) into l_each_person_id
from per_all_assignments_f where assignment_id = l_asgid;
hr_utility.trace('selecting people');
select formula_id,
effective_start_date
into l_formula_id,
l_effective_start_date
from ff_formulas_f
where formula_name='NI_VALIDATION'
and business_group_id is null
and legislation_code='CA'
and sysdate between effective_start_date and effective_end_date;
hr_utility.trace('selected people');
hr_utility.trace('selected address');
select effective_date,report_type
into ld_fapp_effective_date,lv_fapp_report_type
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_legislative_parameters varchar2(240);
select legislative_parameters,
trunc(effective_date,'Y'),
effective_date,
business_group_id
into l_legislative_parameters,
l_year_start,
l_year_end,
l_business_group
from pay_payroll_actions
where payroll_action_id = pactid;
select org_information11
into l_transmitter_gre_id
from hr_organization_information
where organization_id = l_eoy_tax_unit_id
and org_information_context = 'Canada Employer Identification';
sqlstr := 'select /*+ ORDERED INDEX (PPY PAY_PAYROLLS_F_FK2,
PPA PAY_PAYROLL_ACTIONS_N51,
PAA PAY_ASSIGNMENT_ACTIONS_N50,
ASG PER_ASSIGNMENTS_F_PK,
PPA1 PAY_PAYROLL_ACTIONS_PK)
USE_NL(PPY, PPA, PAA, ASG, PPA1) */
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 = '|| to_char(l_eoy_tax_unit_id)||'
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)||'
order by asg.person_id';