[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Procedure get_selection_information (
p_payroll_action_id in number,
p_year_start out nocopy date,
p_year_end out nocopy date,
p_state_code out nocopy varchar2,
p_state_abbrev out nocopy varchar2,
p_report_type out nocopy varchar2,
p_business_group_id out nocopy number,
p_tax_unit_id out nocopy number,
p_trans_cont_code out nocopy varchar2,
p_yrend_ppa_id out nocopy number)
is
-- Cursor to fetch the 1099R Transmitter Control Code for a particular gre
cursor tcc_1099R_cur(p_tax_unit_id NUMBER, p_business_group_id NUMBER) is
/*4583577 Perf change 1 start*/
select hoi2.org_information2
from hr_all_organization_units hou,
hr_organization_information hoi2 -- 1099R transmitter
where hou.business_group_id + 0 = p_business_group_id
and hou.organization_id = p_tax_unit_id
and hoi2.organization_id = hou.organization_id
and hoi2.org_information_context = '1099R Magnetic Report Rules'
and exists
(select 'Y'
from hr_all_organization_units hou1, hr_organization_information hoi
where hou1.business_group_id + 0 = p_business_group_id
and hou1.organization_id = p_tax_unit_id
and hou1.organization_id = hoi.organization_id
and hoi.org_information_context = 'CLASS'
and hoi.org_information1 = 'HR_LEGAL');
select ppa.start_date,
ppa.effective_date,
ppa.business_group_id,
ppa.report_qualifier,
ppa.report_type
FROM pay_payroll_actions ppa
WHERE payroll_action_id = p_payroll_action_id;
select ppa.payroll_action_id
from pay_payroll_actions ppa, -- YREND
pay_payroll_actions ppa1 -- 1099R
where ppa1.payroll_action_id = p_payroll_action_id -- 1099R
and ppa.report_type = 'YREND'
and ppa.effective_date = ppa1.effective_date
and ppa.business_group_id + 0 = ppa1.business_group_id
and ppa.action_status = 'C'
and rtrim(ltrim(Pay_Mag_Utils.Get_Parameter('TRANSFER_GRE',' ',ppa.legislative_parameters))) = p_tax_unit_id;
hr_utility.trace('Entering pay_us_1099r_mag_reporting.get_selection_information');
hr_utility.set_location('pay_us_1099r_mag_reporting.get_selection_information',10);
hr_utility.set_location('pay_us_1099r_mag_reporting.get_selection_information',20);
hr_utility.set_location('pay_us_1099r_mag_reporting.get_selection_information',30);
select state_code into lv_state_code
from pay_us_states
where state_abbrev = lv_report_qualifier;
hr_utility.set_location('pay_us_1099r_mag_reporting.get_selection_information',40);
select legislative_parameters
into lv_leg_param
from pay_payroll_actions ppa
where ppa.payroll_action_id = p_payroll_action_id;
hr_utility.set_location('pay_us_1099r_mag_reporting.get_selection_information',50);
hr_utility.set_location('pay_us_1099r_mag_reporting.get_selection_information',60);
hr_utility.set_location('pay_us_1099r_mag_reporting.get_selection_information',65);
hr_utility.set_location('pay_us_1099r_mag_reporting.get_selection_information',70);
hr_utility.trace('The year start from get_selection_information is: '||to_char(ld_year_start));
hr_utility.trace('The year end from get_selection_information is: '||to_char(ld_year_end));
hr_utility.trace('The state code from get_selection_information is: '||lv_state_code);
hr_utility.trace('The state abbrev from get_selection_information is: '||lv_report_qualifier);
hr_utility.trace('The report type from get_selection_information is: '||lv_report_type);
hr_utility.trace('The business group id from get_selection_information is: '||to_char(ln_business_group_id));
hr_utility.trace('The tax unit id from get_selection_information is: '||lv_tax_unit_id);
hr_utility.trace('The transmitter control code from get_selection_information is: '||lv_trans_cont_code);
hr_utility.trace('The year end payroll action id from get_selection_information is: '||to_char(ln_yrend_ppa_id));
hr_utility.trace('Exiting pay_us_1099r_mag_reporting.get_selection_information');
end get_selection_information;
select jurisdiction_code
from pay_state_rules
where state_code = cp_state_abbrev;
select to_number(ue.creator_id)
from ff_database_items fdi,
ff_user_entities ue
where fdi.user_name = cp_database_item
and ue.user_entity_id = fdi.user_entity_id
and ue.creator_type = 'B';
select hou.organization_id, hoi2.org_information1
from hr_all_organization_units hou,
hr_organization_information hoi,
hr_organization_information hoi2
where hou.business_group_id + 0 = p_business_group_id
and hou.organization_id = hoi.organization_id
and hoi.org_information_context = 'CLASS'
and hoi.org_information1 = 'HR_LEGAL'
and hoi.organization_id = hoi2.organization_id
and hoi2.org_information_context = '1099R Magnetic Report Rules'
and hou.organization_id in (
select organization_id
from hr_organization_information
where org_information_context = '1099R Magnetic Report Rules'
and org_information2 = p_trans_cont_code)
order by 2 desc;
select payroll_action_id
from pay_payroll_actions
where report_type = 'YREND'
and effective_date = cp_year_end
and start_date = cp_year_start
and business_group_id + 0 = cp_business_group_id
and substr(legislative_parameters,
(instr(legislative_parameters, 'TRANSFER_GRE=') +
length('TRANSFER_GRE='))) = cp_tax_unit_id;
select '1'
from dual
where exists (
select '1'
from pay_assignment_actions paa
where paa.payroll_action_id = cp_payroll_action_id
and paa.action_status = decode(cp_status_type,'R','M', --If R is passed we compare for retry
cp_status_type))
and not exists (
select '1'
from pay_action_parameters
where parameter_name = 'FORCE_MAG_REPORT'
and instr(parameter_value, cp_status_type) > 0);
select 'Y'
from hr_organization_information hoi
where hoi.organization_id = cp_tax_unit_id
and hoi.org_information_context = '1099R Magnetic Report Rules'
and hoi.org_information1 = 'Y'
and hoi.org_information2 = cp_trans_control_code
and replace(substr(hoi.org_information9,1,40),',') is not null
and replace(replace(replace(replace(replace(replace(replace(replace
(upper(substr(hoi.org_information10,1,15)),'-'),'.'),'('),')'),'E'),'X'), 'T'),' ') is not null;
select hoi.org_information11
from hr_organization_information hoi
where hoi.organization_id = cp_tax_unit_id
and hoi.org_information_context = '1099R Magnetic Report Rules'
and hoi.org_information1 = 'Y'
and hoi.org_information2 = cp_trans_control_code;
select 'Y' from hr_organization_information hoi
where hoi.organization_id = cp_tax_unit_id
and hoi.org_information_context = '1099R Magnetic Report Rules'
and hoi.org_information1 = 'Y'
and hoi.org_information2 = cp_trans_control_code
and hoi.org_information11 is not null
and hoi.org_information12 is not null
and hoi.org_information13 is not null
and hoi.org_information14 is not null
and hoi.org_information15 is not null
and hoi.org_information16 is not null
and hoi.org_information17 is not null
and hoi.org_information18 is not null
and hoi.org_information19 is not null ;
get_selection_information (
p_payroll_action_id,
ld_year_start,
ld_year_end,
lv_state_code,
lv_state_abbrev,
lv_report_type,
ln_business_group_id,
ln_tax_unit_id,
lv_trans_cont_code,
ln_yrend_ppa_id);
'select distinct paf.person_id
from --hr_soft_coding_keyflex hsck,
per_all_assignments_f paf,
pay_assignment_actions paa,
pay_payroll_actions ppa1,
pay_payroll_actions ppa
where ppa1.payroll_action_id = :p_payroll_action_id
and ppa.report_type = ''YREND''
and ppa.business_group_id + 0 = ppa1.business_group_id
and ppa.effective_date = ppa1.effective_date
and ppa.start_date = ppa1.start_date
and ppa.payroll_action_id = paa.payroll_action_id
and paa.action_status = ''C''
and paa.assignment_id = paf.assignment_id
and paf.assignment_type = ''E''
and paf.effective_start_date <= ppa.effective_date
and paf.effective_end_date >= ppa.start_date
and paf.business_group_id + 0 = ppa.business_group_id
--and paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
--and hsck.segment1 = paa.tax_unit_id
--and hsck.segment1 in
and paa.tax_unit_id in
(select hoi.organization_id
from hr_organization_information hoi
where hoi.org_information_context = ''1099R Magnetic Report Rules'')
order by paf.person_id';
'select distinct paf.person_id
from --hr_soft_coding_keyflex hsck,
hr_organization_units hou,
per_all_assignments_f paf,
pay_us_state_w2_v psv,
pay_payroll_actions ppa
where ppa.payroll_action_id = :p_payroll_action_id
and hou.business_group_id + 0 = ppa.business_group_id + 0
and psv.tax_unit_id = hou.organization_id
and psv.action_status = ''C''
and psv.year = to_number(to_char(ppa.effective_date, ''YYYY''))
and ( psv.state_ein <> ''FLI P.P. #'' and
decode(psv.state_abbrev, ''NY'', psv.w2_state_income_tax,
''WV'', psv.w2_state_income_tax,
''IN'', psv.w2_state_income_tax,
''CT'', psv.w2_state_income_tax,
''SC'', psv.w2_state_income_tax,
''AZ'', psv.w2_state_income_tax,
psv.w2_state_wages) >= 0 ) -- 4350849
and psv.state_abbrev = ppa.report_qualifier
and psv.assignment_id = paf.assignment_id
and paf.assignment_type = ''E''
and paf.effective_start_date <= ppa.effective_date
and paf.effective_end_date >= ppa.start_date
and paf.business_group_id + 0 = ppa.business_group_id
--and paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
--and hsck.segment1 = psv.tax_unit_id
--and hsck.segment1 in
and psv.tax_unit_id in
(select hoi.organization_id
from hr_organization_information hoi
where hoi.org_information_context = ''1099R Magnetic Report Rules'')
order by paf.person_id';
select paf.person_id,
paa.tax_unit_id,
paf.effective_end_date,
paf.assignment_id,
--pww.wages_tips_compensation
pww.gross_1099r
from pay_payroll_actions ppa,
pay_payroll_actions ppa1,
pay_us_wages_1099r_v pww,
per_all_assignments_f paf,
pay_assignment_actions paa
where ppa1.payroll_action_id = cp_payroll_action_id
and pww.year = to_number(to_char(ppa.effective_date, 'YYYY'))
and pww.assignment_id = paf.assignment_id
and pww.tax_unit_id = paa.tax_unit_id
and ppa.report_type = 'YREND'
and ppa.business_group_id + 0 = ppa1.business_group_id + 0
and ppa.effective_date = ppa1.effective_date
and ppa.start_date = ppa1.start_date
and paa.payroll_action_id = ppa.payroll_action_id
and paf.assignment_id = paa.assignment_id
and paf.person_id BETWEEN cp_start_person and cp_end_person
and paf.assignment_type = 'E'
and paf.effective_start_date <= ppa.effective_date
and paf.effective_end_date >= ppa.start_date
and to_char(paa.tax_unit_id) in (
select ffaic2.context
from ff_contexts ffc,
ff_user_entities ffue,
ff_archive_items ffai,
ff_archive_items ffai2,
ff_archive_item_contexts ffaic,
ff_archive_item_contexts ffaic2,
ff_contexts ffc2
where ffai.context1 = cp_yrend_ppa_id
and ffue.user_entity_id = ffai.user_entity_id
and ffue.user_entity_name = 'A_US_1099R_TRANSMITTER_CODE'
and ffai.archive_item_id = ffaic.archive_item_id
and ffaic.context_id = ffc.context_id
and ffc.context_name = 'TAX_UNIT_ID'
and ffai2.user_entity_id = ffai.user_entity_id
and ffai2.value = ffai.value
and ffai2.context1 in (select payroll_action_id
from pay_payroll_actions
where report_type = 'YREND'
and effective_date = ppa.effective_date)
and ffai2.archive_item_id = ffaic2.archive_item_id
and ffaic2.context_id = ffc2.context_id
and ffc2.context_name = 'TAX_UNIT_ID')
order by 1, 2, 3 desc, 4;
select paf.person_id,
psv.tax_unit_id, --to_number(hsck.segment1),
paf.effective_end_date,
paf.assignment_id,
psv.w2_state_wages,
psv.w2_state_income_tax
from per_all_assignments_f paf,
pay_us_state_w2_v psv,
pay_payroll_actions ppa
where ppa.payroll_action_id = cp_payroll_action_id
and psv.year = to_number(to_char(ppa.effective_date, 'YYYY'))
and psv.state_abbrev = ppa.report_qualifier
and psv.assignment_id = paf.assignment_id
and psv.state_ein <> 'FLI P.P. #' /* 9205571 */
and paf.assignment_type = 'E'
and paf.person_id between cp_start_person and cp_end_person
and paf.effective_start_date <= ppa.effective_date
and paf.effective_end_date >= ppa.start_date
and paf.business_group_id + 0 = ppa.business_group_id + 0
and to_char(psv.tax_unit_id) in
(select ffaic2.context
from ff_contexts ffc,
ff_user_entities ffue,
ff_archive_items ffai,
ff_archive_items ffai2,
ff_archive_item_contexts ffaic,
ff_archive_item_contexts ffaic2,
ff_contexts ffc2
where ffai.context1 = cp_yrend_ppa_id
and ffue.user_entity_id = ffai.user_entity_id
and ffue.user_entity_name = 'A_US_1099R_TRANSMITTER_CODE'
and ffai.archive_item_id = ffaic.archive_item_id
and ffaic.context_id = ffc.context_id
and ffc.context_name = 'TAX_UNIT_ID'
and ffai2.user_entity_id = ffai.user_entity_id
and ffai2.value = ffai.value
and ffai2.context1 in (select payroll_action_id
from pay_payroll_actions
where report_type = 'YREND'
and effective_date = ppa.effective_date)
and ffai2.archive_item_id = ffaic2.archive_item_id
and ffaic2.context_id = ffc2.context_id
and ffc2.context_name = 'TAX_UNIT_ID')
order by 1, 2, 3 desc, 4;
get_selection_information (
p_payroll_action_id,
ld_year_start,
ld_year_end,
lv_state_code,
lv_state_abbrev,
lv_report_type,
ln_business_group_id,
ln_tax_unit_id,
lv_trans_cont_code,
ln_yrend_ppa_id);
select pay_assignment_actions_s.nextval
into ln_lockingactid from dual;
select assignment_action_id into ln_lockedactid
from pay_assignment_actions paa,
per_all_assignments_f paf,
pay_payroll_actions ppa
where paa.payroll_action_id = ppa.payroll_action_id
and paa.assignment_id = paf.assignment_id
and paa.tax_unit_id = ln_tax_unit_id
and ppa.report_type = 'YREND'
and substr(legislative_parameters,
instr(legislative_parameters, 'TRANSFER_GRE=') +
length('TRANSFER_GRE=')) = to_char(ln_tax_unit_id)
and ppa.effective_date = ld_year_end
and ppa.start_date = ld_year_start
and paf.effective_end_date = ld_effective_end_date
and paf.assignment_id = ln_assignment_id
and paf.effective_start_date <= ppa.effective_date; -- 4583577 Perf Change 2.