[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
** assignment is selected.
** 05-NOV-2002 SSattini 115.25 Fixed the bug#2449037, archiving
** CAEOY_T4A_FOOTNOTE_CODE after
** checking t4a nonbox footnotes
** for an employee, so that nonbox
** footnote code is archived and
** displayed in T4A Box38.
** 07-NOV-2002 SSattini 115.26 Fixed the bug#2598802, archiving
** GRE's 'Fed Magnetic Reporting'
** using separate cursor
** c_get_gre_acct_info in
** eoy_archive_gre_data procedure.
** Changed the cursor employer_info
** in eoy_archive_gre_data procedure
** removed the part that archives
** GRE 'Fed Magnetic Reporting'.
** 12-NOV-2002 SSattini 115.27 Removed unnecessary archiving
** of db items with dimension
** _GRE_YTD from eoy_archive_gre_data
** procedure, those db items are
** CAEOY_T4_BOX20_GRE_YTD
** CAEOY_FED_WITHHELD_GRE_YTD
** CAEOY_T4_BOX52_GRE_YTD
** CAEOY_EI_EE_TAXABLE_GRE_YTD
** CAEOY_CPP_ER_LIABILITY_GRE_YTD
** CAEOY_EI_ER_LIABILITY_GRE_YTD.
**
** 02-DEC-2002 SSattini 115.28 Added 'nocopy' for out and in out
** parameters, GSCC compliance.
** 04-DEC-2002 SSattini 115.29 Fixed the bug#2695047, changed
** employee address portion.
** If country is CA then the province
** value should be archived from
** region_1 and if US then from
** region_2.
** 06-DEC-2002 SSattini 115.30 Fixed the bug#2598777, archiving
** PA amounts in dollars only.
**
** 27-AUG-2003 SSouresr 115.33 If the new balance 'T4A No Gross
** Earnings'
** is non zero then archiving will
** take place even if Gross Earnings is
** zero.
** 18-SEP-2003 mmukherj 115.34 Added proper error message if
** transmitter GRE is not found.
**
** 30-OCT-2003 SSattini 115.35 2696309 Added functionality to archive
** Pension Plan Registration Numbers
** in pay_action_information table
** to be reported in T4A Summary
** record (Employer Level).
** 02-FEB-2004 SSattini 115.38 Tuned c_eoy_gre cursor in
** action_creation procedure to fix
** performance bug#3416511.
** 02-JUL-2004 mmukherj 115.39 Tuned c_get_latest_asg cursor
** bug#3358776.
** 06-AUG-2004 SSattini 115.40 Modified cursor cur_non_box_mesg
** to archive balance adjustments
** for Non-box footnotes. Bug#3641353.
**
** 10-AUG-2004 ssouresr 115.42 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
** 24-AUG-2004 mmukherj 115.43 Archiving two more dbis
** CAEOY_TECHNICAL_CONTACT_EMAIL
** CAEOY_TECHNICAL_CONTACT_EXTN
** needed for T4A XML Magatpe.
**
** 02-OCT-2004 ssouresr 115.45 Employee Address is now archived
** for terminated employees
** 02-OCT-2004 ssouresr 115.46 The negative balance flag will be
** archived as Y if any box or nonbox
** footnote is negative
** 03-NOV-2004 rigarg 115.47 3922311, Modified the cursor employer_info
** 3973040 to remove check for transmitter code 901.
**
** 10-NOV-2004 ssouresr 115.48 Modified to use tables instead of views
** to remove problems with security groups
** 19-NOV-2004 mmukherj 115.49 bigfix 3913784
** 24-NOV-2004 mmukherj 115.50 Changed the code so that if the
** accounting contact info for GRE is
** not there then it archives the
** accounting contact info of Transmitter
** 02-DEC-2004 ssouresr 115.51 Added error message for security group
** 06-DEC-2004 mmukherj 115.52 Fix for not archiving the registration
** no if archiver value is null.
** 08-DEC-2004 mmukherj 115.53 Fix for PA registration no archiving.#3913784
** 14-SEP-2004 ssouresr 115.54 Added T4A Archiver Amendment functionality
** by creating function compare_archive_data
** and using it to archive the T4A amendment
** flag
** 01-FEB-2005 mmukherj 115.55 Fix for single footnote #4107278
** nonbox footnote #4118500 added.
** 02-FEB-2005 ssouresr 115.56 Nonbox footnotes with a value of zero
** will not be archived. In addition if the
** same nonbox footnote is processed multiple
** times this will be considered as only one
** footnote count for the purposes of box38
** 04-MAR-2005 ssouresr 115.57 The archiver uses a new NonBox Footnote Element
** which has a Jurisdiction input value from the
** beginning of 2006
** 26-APR-2005 ssouresr 115.58 The archiver will now recognize amendments
** made only to non box footnotes
** 08-JUN-2005 ssouresr 115.59 Removed error message for security group
** 15-JUL-2005 mmukherj 115.60 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.
** 05-AUG-2005 saurgupt 115.61 Bug 4517693: Added Address_line3 for
** T4A archiver.
** 26-AUG-2005 mmukherj 115.62 Commented out the use 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.
** 06-sep-2005 mmukherj 115.63 g_archive_flag is set to 'Y' after
** archiving the GRE data. Otherwise it was
** archiving Employer data multiple times
** in some cases where there are more than one
** chunks used in the process.
** 27-SEP-2005 ssouresr 115.64 Corrected the footnote condition in the
** function compare_archive_data
** 06-OCT-2005 ssouresr 115.65 Modified the range cursor to avoid the use
** of hr_soft_coding_keyflex.
** 26-OCT-2005 ssouresr 115.66 Modified the range cursor to add order hint
** 22-AUG-2007 ssmukher 115.67 Bug 4021563 Added code for Status
** Indian employee in eoy_archive_data
** procedure.
*/
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 = 'T4A' 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,
paa.tax_unit_id tax_unit_id,
ASG.effective_end_date effective_end_date
FROM per_all_assignments_f ASG,
pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE ppa.payroll_action_id >= 0
AND 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 = l_eoy_tax_unit_id
AND paa.assignment_id = ASG.assignment_id
AND ppa.business_group_id = ASG.business_group_id +0
AND ASG.person_id + 0 between stperson and endperson
AND ASG.assignment_type = 'E'
AND ppa.effective_date between ASG.effective_start_date
AND ASG.effective_end_date
ORDER BY 1, 3, 4 DESC, 2;
SELECT ASG.person_id person_id,
ASG.assignment_id assignment_id,
paa.tax_unit_id tax_unit_id,
ASG.effective_end_date effective_end_date
FROM per_all_assignments_f ASG,
pay_assignment_actions paa,
pay_payroll_actions ppa,
per_all_people_f ppf
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 = l_eoy_tax_unit_id
AND paa.assignment_id = ASG.assignment_id
AND ppa.business_group_id = ASG.business_group_id +0
AND ppf.person_id between stperson and endperson
AND ASG.person_id = ppf.person_id
AND ASG.assignment_type = 'E'
AND ppa.effective_date between ASG.effective_start_date
AND ASG.effective_end_date
AND ppa.effective_date between ppf.effective_start_date
AND ppf.effective_end_date
ORDER BY 1, 3, 4 DESC, 2;
SELECT ASG.person_id person_id,
ASG.assignment_id assignment_id,
to_number(SCL.segment11) 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 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 effective_date,
report_type,
business_group_id,
legislative_parameters
into l_effective_date,
l_report_type,
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 paa1.assignment_action_id
into l_max_aaid
from pay_assignment_actions paa1,
per_all_assignments_f paf2,
pay_payroll_actions ppa2,
pay_action_classifications pac2
where paf2.person_id = l_person_id
and paa1.assignment_id = paf2.assignment_id
and paa1.tax_unit_id = l_tax_unit_id
and paa1.payroll_action_id = ppa2.payroll_action_id
and ppa2.action_type = pac2.action_type
and pac2.classification_name = 'SEQUENCED'
and ppa2.effective_date between paf2.effective_start_date
and paf2.effective_end_date
and ppa2.effective_date between l_period_start and
l_period_end
and not exists (select ''
FROM pay_action_classifications pac,
pay_payroll_actions ppa,
pay_assignment_actions paa,
per_all_assignments_f paf1
WHERE paf1.person_id = l_person_id
AND paa.assignment_id = paf1.assignment_id
AND paa.tax_unit_id = l_tax_unit_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.effective_date between l_period_start
and l_period_end
AND paa.action_sequence > paa1.action_sequence
AND pac.action_type = ppa.action_type
AND pac.classification_name = 'SEQUENCED')
and rownum < 2;
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 W2 view will not have
** to do an additional checking 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 user_entity_id
into l_user_entity_id
from ff_database_items
where user_name = p_dbi_name;
SELECT fai1.context1,
fdi1.user_entity_id,
fai1.value
FROM ff_archive_items fai1,
ff_database_items fdi1
WHERE fai1.user_entity_id = fdi1.user_entity_id
AND fai1.context1 = cp_asg_act_id
AND fdi1.user_name <> 'CAEOY_T4A_AMENDMENT_FLAG'
order by fdi1.user_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 = 'T4A'
order by action_information4;
ltr_amend_arch_data.delete;
ltr_yepp_arch_data.delete;
ltr_amend_footnote.delete;
ltr_yepp_footnote.delete;
select nvl(hoi6.ORG_INFORMATION9,ou2.name) GRE_stat_report_name,
bg.business_group_id Business_group_id,
hoi6.ORG_INFORMATION1 Employer_identification_number,
hl2.ADDRESS_LINE_1 GRE_addrline1,
hl2.ADDRESS_LINE_2 GRE_addrline2,
hl2.ADDRESS_LINE_3 GRE_addrline3,
hl2.TOWN_OR_CITY GRE_town_or_city,
DECODE(hl2.STYLE , 'US' , hl2.REGION_2 ,
'CA' , hl2.REGION_1 ,
'CA_GLB',hl2.region_1, ' ') GRE_province,
hl2.POSTAL_CODE GRE_postal_code,
hl2.COUNTRY GRE_country,
nvl(hoi3.ORG_INFORMATION9,ou1.name) trans_stat_report_name,
hl1.ADDRESS_LINE_1 trans_addrline1,
hl1.ADDRESS_LINE_2 trans_addrline2,
hl1.ADDRESS_LINE_3 trans_addrline3,
hl1.TOWN_OR_CITY trans_town_or_city,
DECODE(hl1.STYLE , 'US' , hl1.REGION_2 ,
'CA' , hl1.REGION_1 ,
'CA_GLB',hl1.region_1, ' ') trans_province,
hl1.POSTAL_CODE trans_postal_code,
hl1.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,
hoi2.ORG_INFORMATION17 trans_tech_contact_extn,
hoi2.ORG_INFORMATION18 trans_tech_contact_email,
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
from hr_all_organization_units ou1, /* transmitter org */
hr_organization_information hoi1, /* Transmitter GRE to check
GRE/Legal Classification is enabled */
hr_organization_information hoi2, /* Transmitter GRE to check
'Fed Magnetic Reporting' */
hr_organization_information hoi3, /* Transmitter GRE to check
'Employer Identification' */
hr_locations_all hl1, /* trans location */
hr_all_organization_units ou2, /* GRE Org */
hr_organization_information hoi4, /* GRE to check GRE/Legal
Classification is enabled */
hr_organization_information hoi6, /* GRE to check
'Employer Identification'*/
hr_locations_all hl2, /* GRE location */
per_business_groups bg
where bg.business_group_id = ou1.business_group_id
and bg.legislation_code = 'CA'
and ou1.organization_id = p_transmitter_gre_id
and ou1.organization_id = hoi1.organization_id
and hoi1.org_information_context = 'CLASS'
and hoi1.org_information1 = 'HR_LEGAL'
and hoi1.org_information2 = 'Y'
and ou1.location_id = hl1.location_id
and ou1.organization_id = hoi2.organization_id
and hoi2.org_information_context = 'Fed Magnetic Reporting'
and hoi2.org_information1 = 'Y'
and ou1.organization_id = hoi3.organization_id
and hoi3.org_information_context = 'Canada Employer Identification'
and hoi3.org_information5 in ('T4A/RL1','T4A/RL2')
and bg.business_group_id = ou2.business_group_id
and ou2.organization_id = p_tax_unit_id
and ou2.organization_id = hoi4.organization_id
and hoi4.org_information_context = 'CLASS'
and hoi4.org_information1 = 'HR_LEGAL'
and hoi4.org_information2 = 'Y'
and ou2.location_id = hl2.location_id
and ou2.organization_id = hoi6.organization_id
and hoi6.org_information_context = 'Canada Employer Identification'
and hoi6.ORG_INFORMATION5 in ('T4A/RL1','T4A/RL2');
select hoi.ORG_INFORMATION10 GRE_acct_contact_name,
hoi.ORG_INFORMATION12 GRE_acct_contact_phone,
hoi.ORG_INFORMATION11 GRE_acct_contact_area_code,
hoi.ORG_INFORMATION13 GRE_acct_contact_extn,
hoi.ORG_INFORMATION14 GRE_Proprietor_SIN#1,
hoi.ORG_INFORMATION15 GRE_Proprietor_SIN#2,
hoi.ORG_INFORMATION16 GRE_Fedyouth_hire_Prgind
from hr_organization_information hoi
where hoi.organization_id = cp_gre_id
and hoi.org_information_context = 'Fed Magnetic Reporting';
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 distinct pet.element_information18,
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 pet.business_group_id = l_business_group_id
and pbt1.balance_type_id = pet.element_information10
and pet.element_information18 = flv.lookup_code
and flv.lookup_type = 'PAY_CA_T4A_FOOTNOTES'
and flv.language = userenv('LANG')
order by pet.element_information18;
select nvl(pet.element_information20,'NOT FOUND'),
pbt1.balance_name,pet.element_type_id,
pet.classification_id
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 pet.business_group_id = l_business_group_id
and pbt1.balance_type_id = pet.element_information10
-- and pet.element_information_category = 'CA_EARNINGS'
and pet.element_information20 is not null;
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_asgact_id
and arch_ppa.payroll_action_id = arch_paa.payroll_action_id
and hou.business_group_id = arch_ppa.business_group_id
and hou.organization_id = hoi.organization_id
and hoi.organization_id =
to_number(pycadar_pkg.get_parameter('TRANSFER_GRE',arch_ppa.legislative_parameters))
and hoi.org_information_context = 'Canada Employer Identification'
and hoi.org_information5 IN ('T4A/RL1','T4A/RL2')
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 --'T4A 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);
select action_information4,to_number(action_information5)
from pay_action_information
where action_context_id = cp_payroll_action_id
and effective_date = cp_eff_date
AND tax_unit_id = cp_tax_unit_id
and action_information_category = 'CAEOY PENSION PLAN INFO'
AND ACTION_INFORMATION4 = cp_reg_no;
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 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 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 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 business_group_id
into l_business_group_id
from hr_all_organization_units
where organization_id = l_tax_unit_id;
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;
update pay_action_information
set action_information5 = to_char(ln_emplr_regamt)
where action_context_id = l_payroll_action_id
and tax_unit_id = l_tax_unit_id
and effective_date = p_effective_date
and action_information_category = 'CAEOY PENSION PLAN INFO'
AND ACTION_INFORMATION4 = old_l_registration_no;
hr_utility.trace('in reg1 insert pay_action_information');
update pay_action_information
set action_information5 = to_char(ln_emplr_regamt1)
where action_context_id = l_payroll_action_id
and tax_unit_id = l_tax_unit_id
and effective_date = p_effective_date
and action_information_category = 'CAEOY PENSION PLAN INFO'
AND ACTION_INFORMATION4 = old_l_registration_no1;
hr_utility.trace('in reg2 insert pay_action_information');
update pay_action_information
set action_information5 = to_char(ln_emplr_regamt2)
where action_context_id = l_payroll_action_id
and tax_unit_id = l_tax_unit_id
and effective_date = p_effective_date
and action_information_category = 'CAEOY PENSION PLAN INFO'
AND ACTION_INFORMATION4 = old_l_registration_no2;
hr_utility.trace('Updated pay_action_information');
hr_utility.trace('in reg3 insert pay_action_information');
update pay_action_information
set action_information5 = to_char(ln_emplr_regamt)
where action_context_id = l_payroll_action_id
and tax_unit_id = l_tax_unit_id
and effective_date = p_effective_date
and action_information_category = 'CAEOY PENSION PLAN INFO'
AND ACTION_INFORMATION4 = old_l_registration_no;
update pay_action_information
set action_information5 = to_char(ln_emplr_regamt1)
where action_context_id = l_payroll_action_id
and tax_unit_id = l_tax_unit_id
and effective_date = p_effective_date
and action_information_category = 'CAEOY PENSION PLAN INFO'
AND ACTION_INFORMATION4 = old_l_registration_no1;
update pay_action_information
set action_information5 = to_char(ln_emplr_regamt2)
where action_context_id = l_payroll_action_id
and tax_unit_id = l_tax_unit_id
and effective_date = p_effective_date
and action_information_category = 'CAEOY PENSION PLAN INFO'
AND ACTION_INFORMATION4 = old_l_registration_no2;
hr_utility.trace('selecting people');
select PEOPLE.person_id,
PEOPLE.first_name,
PEOPLE.last_name,
PEOPLE.employee_number,
PEOPLE.WORK_TELEPHONE,
replace(PEOPLE.national_identifier,' '),
PEOPLE.middle_names, /* Bug:1474421 Changed pre_name_adjunct to middle_names */
ASSIGN.organization_id,
ASSIGN.location_id
into l_person_id,
l_first_name,
l_last_name,
l_employee_number,
l_work_telephone,
l_national_identifier,
l_middle_names, /* changed variable l_pre_name_adjunct to l_middle_names */
l_organization_id,
l_location_id
from
per_all_assignments_f ASSIGN
, per_all_people_f PEOPLE
, per_person_types PTYPE
, 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 SES.session_id = USERENV('SESSIONID') ;
select PHONE.phone_number
into l_employee_phone_no
from per_phones PHONE ,
fnd_sessions SES
where PHONE.parent_id (+) = l_person_id
and PHONE.parent_table (+)= 'PER_ALL_PEOPLE_F'
and PHONE.phone_type (+)= 'W1'
and l_date_earned BETWEEN NVL(PHONE.date_from,SES.effective_date)
AND NVL(PHONE.date_to,SES.effective_date)
and SES.session_id = USERENV('SESSIONID') ;
hr_utility.trace('selected people');
hr_utility.trace('selecting address');
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
into l_address_line1,
l_address_line2,
l_address_line3,
l_town_or_city,
l_province_code,
l_postal_code,
l_telephone_number,
l_country_code
from per_addresses addr,
fnd_territories_vl country
where addr.person_id = l_person_id
and addr.primary_flag = 'Y'
and p_effective_date
between nvl(addr.date_from,p_effective_date)
and nvl(addr.date_to, p_effective_date)
and country.territory_code = addr.country;
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'
and org_information5 in ('T4A/RL1','T4A/RL2');
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';