[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select balance_type_id
from pay_balance_types
where balance_name = cp_bal_name;
select pdb.defined_balance_id
from pay_defined_balances pdb,
pay_balance_dimensions pbd
where pdb.balance_type_id = cp_bal_type_id
and pbd.dimension_name = cp_bal_dimension
and pbd.balance_dimension_id = pdb.balance_dimension_id
and ((pbd.legislation_code = cp_legislation_code and
pbd.business_group_id is null)
or (pbd.legislation_code is null and
pbd.business_group_id is not null));
/* 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 = 'RL2' 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 'Y'
from dual
where exists (select 'X'
from fnd_lookup_values
where ((lookup_type = 'PAY_CA_RL2_FOOTNOTES'
and lookup_code = p_footnote_code)
OR (lookup_type = 'PAY_CA_RL2_AUTOMATIC_FOOTNOTES'
and lookup_code = p_footnote_code))
);
Purpose : This procedure will delete the plsql tables used for
archiving the employee and employer data.
Arguments :
Notes :
*/
procedure initialization_process(p_data varchar2)
is
BEGIN
If p_data = 'EMPLOYEE_DATA' then
hr_utility.trace('deleting plsql table'|| p_data);
pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data.delete;
pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_ft_data.delete;
pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data2.delete;
pay_ca_eoy_rl2_archive.ltr_ppa_arch_data.delete;
pay_ca_eoy_rl2_archive.ltr_ppa_arch_er_data.delete;
Purpose : This procedure will insert values in to pay_action_information
table using the plsql table.
Arguments :
Notes :
*/
procedure archive_data_records(
p_action_context_id in number
,p_action_context_type in varchar2
,p_assignment_id in number
,p_tax_unit_id in number
,p_effective_date in date
,p_tab_rec_data in pay_ca_eoy_rl2_archive.action_info_table
)
IS
l_action_information_id_1 NUMBER ;
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 = 'RL2'
and jurisdiction_code = 'QC'
order by action_information4;
select nvl(action_information1,'NULL'),
nvl(action_information2,'NULL'),
nvl(action_information3,'NULL'),
nvl(action_information4,'NULL'),
nvl(action_information5,'NULL'),
nvl(action_information6,'NULL'),
nvl(action_information7,'NULL'),
nvl(action_information8,'NULL'),
nvl(action_information9,'NULL'),
nvl(action_information10,'NULL'),
nvl(action_information11,'NULL'),
nvl(action_information12,'NULL'),
nvl(action_information13,'NULL'),
nvl(action_information14,'NULL'),
nvl(action_information15,'NULL'),
nvl(action_information16,'NULL'),
nvl(action_information17,'NULL'),
nvl(action_information18,'NULL'),
nvl(action_information19,'NULL'),
nvl(action_information20,'NULL'),
nvl(action_information21,'NULL'),
nvl(action_information22,'NULL'),
nvl(action_information23,'NULL'),
nvl(action_information24,'NULL'),
nvl(action_information25,'NULL'),
nvl(action_information26,'NULL'),
nvl(action_information27,'NULL'),
nvl(action_information28,'NULL'),
nvl(action_information29,'NULL'),
nvl(action_information30,'NULL')
from pay_action_information
where action_context_id = cp_asg_act_id
and action_information_category = 'CAEOY RL2 EMPLOYEE INFO'
and action_context_type = 'AAP'
and jurisdiction_code = 'QC';
select nvl(action_information1,'NULL')
from pay_action_information
where action_context_id = cp_asg_act_id
and action_information_category = 'CAEOY RL2 EMPLOYEE INFO2'
and action_context_type = 'AAP'
and jurisdiction_code = 'QC';
ltr_amend_arch_data.delete;
ltr_yepp_arch_data.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.segment12)) 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_org_id
and hoi.org_information5 = 'T4A/RL2')
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_org_id
and hoi.org_information5 = 'T4A/RL2')
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 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,
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.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_org_id
and hoi.org_information5 = 'T4A/RL2')
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 ppr.payroll_action_id = pactid
AND ppr.chunk_number = chunk
AND ppr.person_id = ASG.person_id
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_org_id
and hoi.org_information5 = 'T4A/RL2';
SELECT assignment_id
from per_all_assignments_f paf
where person_id = p_person_id
and assignment_type = 'E'
and primary_flag = 'Y'
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,
-- Added for bug 10399514
report_qualifier,
report_category,
-- Added for bug 10399514
business_group_id,
pay_ca_eoy_rl1_amend_arch.get_parameter('PRE_ORGANIZATION_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_pre_org_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 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 the maximum assignment action id. Fix for bug#3638928 */
begin
open c_get_asg_act_id(l_person_id,l_tax_unit_id,
l_period_start,l_period_end);
end; /* end for select of max assignment action id */
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 RL2 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 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) RL2_Slip_Number,
decode(target1.org_information3,'Y',target1.organization_id,
to_number(target1.ORG_INFORMATION20)),
target1.ORG_INFORMATION3
from hr_organization_information target1,
hr_all_organization_units target2
where target1.organization_id = to_number(p_pre_org_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'
and target1.org_information4 = 'P02';
select to_char(effective_date,'YYYY'),business_group_id,effective_date
into l_taxation_year,l_business_group_id,l_effective_date
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
, O.name
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
, l_org_name
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;
/* Inserting rows into pay_action_information table
Transmitter PRE Information */
if ltr_ppa_arch_data.count >0 then
hr_utility.trace('Archiving PRE Data');
/* Inserting rows into pay_action_information table
Employer Information (Could be just a PRE or Transmitter PRE) */
if pay_ca_eoy_rl2_archive.ltr_ppa_arch_er_data.count >0 then
hr_utility.trace('Archiving Employer Data');
select 'Y'
from pay_action_information
where action_information1 = 'RL2'
and action_context_id = p_payroll_action_id;
select min(paa.chunk_number)
from pay_assignment_actions paa
where paa.payroll_action_id = p_payroll_action_id;
Purpose : Calculates and inserts check digit to PDF sequence number - 8500723
*/
FUNCTION getnext_seq_num (p_curr_seq IN NUMBER)
RETURN NUMBER IS
l_seq_number number;
select action_information3,
ACTION_INFORMATION_ID,
OBJECT_VERSION_NUMBER
from pay_action_information
where action_information_category = 'CAEOY RL2 EMPLOYEE INFO2'
and action_context_id = cp_aaid;
select ROW_LOW_RANGE_OR_NAME range_start,
ROW_HIGH_RANGE range_end
from pay_user_tables put,
pay_user_rows_f pur
where pur.USER_TABLE_ID=put.USER_TABLE_ID
and put.USER_TABLE_NAME = 'RL2 PDF Sequence Range'
and fnd_date.string_to_date('31/12/'||cp_run_year,'DD/MM/YYYY')
between pur.EFFECTIVE_START_DATE and pur.EFFECTIVE_END_DATE;
select PAY_CA_RL2_PDF_SEQ_COUNT_S.nextval into l_seq_offset
from dual;
pay_action_information_api.update_action_information(p_action_information_id=>l_act_info_id,
p_object_version_number=>l_obj_ver,
p_action_information3=>l_final_seq_num);
select to_number(target.ORG_INFORMATION1),to_number(target.ORG_INFORMATION2)
into l_rl2_starting_slip_num,l_rl2_ending_slip_num
from hr_organization_information target
where target.organization_id = p_transmitter_id
and target.org_information_context = 'Prov Reporting Est3'
and exists (select 'X' from hr_organization_information target1
where target1.organization_id = p_transmitter_id
and target1.org_information_context = 'Prov Reporting Est'
and target1.ORG_INFORMATION3 = 'Y');
select l_rl2_starting_slip_num + pay_ca_eoy_rl2_s.nextval - 1
into l_rl2_curr_slip_number from dual;
select hoi.organization_id
from pay_action_information pac,
pay_assignment_actions paa,
hr_organization_information hoi
where paa.assignment_action_id = asgactid
and pac.action_context_id = paa.payroll_action_id
and pac.action_information_category = 'CAEOY TRANSMITTER INFO'
and pac.action_information1 = 'RL2'
and pac.action_information27 = hoi.org_information2
and hoi.org_information_context = 'Canada Employer Identification'
order by 1;
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 paa.assignment_id = paf.assignment_id
and paa.tax_unit_id = cp_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 between paf.effective_start_date
and paf.effective_end_date
and ppa.effective_date between ppf.effective_start_date
and ppf.effective_end_date
and ppa.effective_date between cp_period_start and cp_period_end
AND pac1.assignment_action_id = paa.assignment_action_id
AND pac1.assignment_id = paa.assignment_id
AND fc.context_id = pac1.context_id
AND fc.context_name = 'JURISDICTION_CODE'
AND pac1.context_value = 'QC'
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 pay_ca_eoy_rl1_amend_arch.get_parameter('PRE_ORGANIZATION_ID',
legislative_parameters),
business_group_id
into l_pre_org_id,
l_business_group_id
from pay_payroll_actions
where payroll_action_id = l_payroll_action_id;
pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_further_data.delete;
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_RL2_FORM_NO'
order by pei_information7; -- bug 14701748
select hoi.organization_id ,
hoi.org_information5
from pay_action_information pac,
pay_assignment_actions paa,
hr_organization_information hoi
where paa.assignment_action_id = asgactid
and pac.action_context_id = paa.payroll_action_id
and pac.action_information_category = 'CAEOY TRANSMITTER INFO'
and pac.action_information1 = 'RL2'
and pac.action_information27 = hoi.org_information2
and hoi.org_information_context = 'Canada Employer Identification'
order by 1;
select hoi.organization_id ,
hoi.org_information5
from pay_action_information pac,
pay_assignment_actions paa,
hr_organization_information hoi
where paa.assignment_action_id = asgactid
and pac.action_context_id = paa.payroll_action_id
and pac.action_information_category = 'CAEOY TRANSMITTER INFO'
and pac.action_information1 = 'RL2'
and pac.action_information27 = hoi.org_information2
and hoi.org_information_context = 'Canada Employer Identification'
order by 1;
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_RL2_FOOTNOTES'
and flv.language = userenv('LANG')
order by pet.element_information19;
select address_line1,
address_line2,
address_line3,
town_or_city,
decode(country,'US',region_2,'CA',region_1,null),
replace(postal_code,' '),
country
from per_addresses pa
where pa.person_id = cp_person_id
and pa.primary_flag = 'Y'
and cp_date_earned between pa.date_from
and nvl(pa.date_to, cp_date_earned);
select address_line1,
address_line2,
address_line3,
town_or_city,
decode(country,'US',region_2,'CA',region_1,null),
replace(postal_code,' '),
country
from per_addresses pa
where pa.person_id = cp_person_id
and pa.primary_flag <> 'Y'
and cp_date_earned between pa.date_from
and nvl(pa.date_to, cp_date_earned)
order by pa.date_from desc;
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
from per_all_assignments_f ASSIGN
,per_all_people_f PEOPLE
where ASSIGN.assignment_id = cp_asg_id
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 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 paa.assignment_id = paf.assignment_id
and paa.tax_unit_id = cp_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 between paf.effective_start_date
and paf.effective_end_date
and ppa.effective_date between ppf.effective_start_date
and ppf.effective_end_date
and ppa.effective_date between cp_period_start and cp_period_end
AND pac1.assignment_action_id = paa.assignment_action_id
AND pac1.assignment_id = paa.assignment_id
AND fc.context_id = pac1.context_id
AND fc.context_name = 'JURISDICTION_CODE'
AND pac1.context_value = 'QC'
order by paa.action_sequence desc;
select pei_information2,
pei_information3,
pei_information4
from per_people_extra_info
where person_id = cp_person_id
and pei_information1 = cp_pre_org_id
and pei_information_category = 'PAY_CA_RL2_INFORMATION';
select information_value
from pay_ca_legislation_info
where lookup_type = 'RL2ARCHIVE'
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 pay_ca_eoy_rl1_amend_arch.get_parameter('PRE_ORGANIZATION_ID',
legislative_parameters),
business_group_id
into l_pre_org_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_id
from hr_organization_information hoi,
hr_all_organization_units hou
WHERE hou.business_group_id = l_business_group_id
and hoi.organization_id = hou.organization_id
and hoi.org_information_context = 'Prov Reporting Est'
and hoi.organization_id = to_number(l_pre_org_id)
and hoi.org_information4 = 'P02';
select hoi.org_information1,hoi.org_information2
into l_pre_source_of_income,l_pre_description
from hr_organization_information hoi
where hoi.organization_id = l_transmitter_id
and hoi.org_information_context = 'Prov Reporting Est2';
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 ppf.full_name,
replace(ppf.national_identifier,' ')
into l_beneficiary_name,
l_beneficiary_sin
from per_all_people_f ppf
where ppf.person_id = to_number(l_per_eit_beneficiary_id);
/* Inserting rows into pay_action_information table
RL2 Employee Data Archived */
if pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data.count >0 then
archive_data_records(
p_action_context_id => p_assactid
,p_action_context_type=> 'AAP'
,p_assignment_id => l_asgid
,p_tax_unit_id => l_rl2_tax_unit_id
,p_effective_date => p_effective_date
,p_tab_rec_data => pay_ca_eoy_rl2_archive.ltr_ppa_arch_ee_data);
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;
select pay_ca_eoy_rl1_amend_arch.get_parameter('PRE_ORGANIZATION_ID',
legislative_parameters),
trunc(effective_date,'Y'),
effective_date,
business_group_id
into l_pre_org_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_org_id ||''''||'
and hoi.org_information5 = ''T4A/RL2'')
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';