[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select fnd_number.canonical_to_number(UE.creator_id)
from ff_database_items DI,
ff_user_entities UE
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 = 'US'; /* Bug: 2296797 */
select SR.jurisdiction_code
from pay_state_rules SR
where SR.state_code = p_state;
update pay_payroll_actions PA
set PA.action_status = 'E'
where PA.payroll_action_id = p_payroll_action_id;
hr_utility.set_location('pay_us_magtape_reporting.error_payroll_action - updated pay_ payrol_actions', 2);
procedure update_action_statuses
(
p_payroll_action_id number
) is
begin
--
-- Sets the payroll action to a status of 'C'omplete.
--
hr_utility.set_location('pay_us_magtape_reporting.update_action_statuses - updating pay_ payrol_actions', 1);
update pay_payroll_actions PA
set PA.action_status = 'C'
where PA.payroll_action_id = p_payroll_action_id;
hr_utility.set_location('pay_us_magtape_reporting.update_action_statuses - updating pay_ assignment_actions', 2);
update pay_assignment_actions AA
set AA.action_status = 'C'
where AA.payroll_action_id = p_payroll_action_id
and AA.action_status = 'U';
hr_utility.set_location('pay_us_magtape_reporting.update_action_statuses - commiting', 3);
end update_action_statuses;
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_state varchar2,
--
-- Quarter and year start and end dates for the period being reported on.
--
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.
--
-- Federal W2.
--
if p_report_type = 'W2' and p_state = 'FED' then
--
-- Default settings for Federal W2.
--
hr_utility.set_location('pay_us_magtape_reporting.get_selection_information - default settings for Federal W2', 1);
hr_utility.set_location('pay_us_magtape_reporting.get_selection_information - default settings for State W2', 2);
hr_utility.set_location('pay_us_magtape_reporting.get_selection_information - NY last quarter', 3);
hr_utility.set_location('pay_us_magtape_reporting.get_selection_information - in NY ', 3);
hr_utility.set_location('pay_us_magtape_reporting.get_selection_information - defalut setting for SQWL ', 4);
hr_utility.set_location('pay_us_magtape_reporting.get_selection_information - invalid report ', 4);
end get_selection_information;
select pay_payroll_actions_s.nextval
into l_payroll_action_id
from sys.dual;
insert into pay_payroll_actions
(payroll_action_id
,action_type
,business_group_id
,action_population_status
,action_status
,effective_date
,date_earned
,legislative_parameters
,object_version_number)
values
(l_payroll_action_id
,'X' -- (X) -> Magnetic Report
,p_business_group_id
,'U' -- (U)npopulated
,'U' -- (U)nprocessed
,p_period_end
,p_period_end
,'USMAGTAPE' || '-' ||
lpad(p_report_type, 5) || '-' ||
lpad(p_state , 5) || '-' ||
lpad(p_trans_legal_co_id, 5) || '-' ||
lpad(nvl(p_media_type, 'RT'), 5) -- SQWLD - save media value, 'PD' for PC Diskette
,1);
select AA.assignment_action_id
from pay_assignment_actions AA
where AA.payroll_action_id = p_payroll_action_id
and AA.assignment_id = p_assignment_id
and AA.tax_unit_id is null;
update pay_assignment_actions AA
set AA.tax_unit_id = p_tax_unit_id
where AA.assignment_action_id = l_assignment_action_id;
hr_utility.set_location('pay_us_magtape_reporting.create_assignment_action - updated pay_assignment_actions', 4);
select full_name, employee_number
from per_people_f
where person_id = p_person_id
and rownum = 1;
SELECT ASG.person_id person_id,
ASG.assignment_id assignment_id,
fnd_number.canonical_to_number(SCL.segment1) tax_unit_id,
max(ASG.effective_end_date) effective_end_date
FROM per_assignments_f ASG,
hr_soft_coding_keyflex SCL,
hr_tax_units_v TUV,
pay_payrolls_f PPY
WHERE ASG.business_group_id = l_bus_group_id
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 fnd_number.canonical_to_number(SCL.segment1) = TUV.tax_unit_id
AND TUV.US_1099R_TRANSMITTER_CODE IS NULL
AND PPY.payroll_id = ASG.payroll_id
AND l_state = l_state
GROUP BY ASG.person_id,
ASG.assignment_id,
fnd_number.canonical_to_number(SCL.segment1)
ORDER BY 1, 3, 4 DESC, 2;
SELECT ASG.person_id person_id,
ASG.assignment_id assignment_id,
fnd_number.canonical_to_number(SCL.segment1) tax_unit_id,
max(ASG.effective_end_date) effective_end_date
FROM per_assignments_f ASG,
hr_soft_coding_keyflex SCL,
hr_tax_units_v TUV,
pay_payrolls_f PPY,
pay_state_rules SR,
pay_element_types_f ET,
pay_input_values_f IV,
pay_element_links_f EL
WHERE ASG.business_group_id + 0 = l_bus_group_id
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 fnd_number.canonical_to_number(SCL.segment1) = TUV.tax_unit_id
AND TUV.US_1099R_TRANSMITTER_CODE IS NULL
AND PPY.payroll_id = ASG.payroll_id
AND SR.state_code = l_state
AND ET.element_name = 'VERTEX'
AND IV.element_type_id = ET.element_type_id
AND upper(IV.name) = 'JURISDICTION'
AND EL.element_type_id = ET.element_type_id
AND EL.business_group_id + 0 = ASG.business_group_id + 0
AND EXISTS (SELECT ''
FROM
pay_element_entries_f EE,
pay_element_entry_values_f EEV
WHERE EE.assignment_id = ASG.assignment_id
AND EE.element_link_id = EL.element_link_id
AND EEV.element_entry_id = EE.element_entry_id
AND EEV.input_value_id + 0 = IV.input_value_id
AND substr(SR.jurisdiction_code ,1,2) =
substr(EEV.screen_entry_value,1,2)
AND EE.effective_start_date <= l_period_end
AND EE.effective_end_date >= l_period_start)
GROUP BY ASG.person_id,
ASG.assignment_id,
fnd_number.canonical_to_number(SCL.segment1)
ORDER BY 1, 3, 4 DESC, 2;
hr_utility.set_location('pay_us_magtape_reporting.generate_people_list - get selection_information', 1);
get_selection_information
(p_report_type,
p_state,
p_quarter_start,
p_quarter_end,
p_year_start,
p_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);
SELECT parameter_value
INTO l_chunk_size
FROM pay_action_parameters
WHERE parameter_name = 'CHUNK_SIZE';
update pay_assignment_actions aa
set aa.serial_number = 999999
where aa.assignment_action_id = l_assignment_action_id;
update pay_payroll_actions PPA
set PPA.action_population_status = 'C'
where PPA.payroll_action_id = l_payroll_action_id;
select PA.effective_date,
ltrim(substr(PA.legislative_parameters, 11,5)),
ltrim(substr(PA.legislative_parameters, 17,5)),
to_char(PA.effective_date,'YYYY'),
decode(ltrim(substr(PA.legislative_parameters, 11,5)),
'W2' , to_char(PA.effective_date,'YYYY'),
'SQWL', to_char(PA.effective_date,'MMYY')),
ltrim(substr(PA.legislative_parameters, 23,5)),
ltrim(substr(PA.legislative_parameters, 29,5))
into l_effective_date,
l_report_type,
l_state,
l_reporting_year,
l_reporting_quarter,
l_trans_legal_co_id,
l_media_type
from pay_payroll_actions PA
where PA.payroll_action_id = p_payroll_action_id;
hr_utility.set_location('pay_us_magtape_reporting.redo - update pay_payroll_actions', 2);
update pay_payroll_actions pa
set PA.action_status = 'M'
where PA.payroll_action_id = p_payroll_action_id;
hr_utility.set_location('pay_us_magtape_reporting.redo - update pay_assignment_actions', 3);
update pay_assignment_actions AA
set AA.action_status = 'M'
where AA.payroll_action_id = p_payroll_action_id;
hr_utility.set_location('pay_us_magtape_reporting.redo - update pay_assignment_actions', 5);
update pay_assignment_actions AA
set AA.action_status = 'C'
where AA.payroll_action_id = p_payroll_action_id;
select a.person_id
from pay_assignment_actions aa, per_assignments_f a
where aa.payroll_action_id = p_payroll_action_id
and aa.assignment_id = a.assignment_id
and aa.serial_number is NOT NULL;
hr_utility.set_location('pay_us_magtape_reporting.run_magtape - update pay_payroll_actions', 5);
update pay_payroll_actions PPA
set PPA.request_id = l_request_id
where PPA.payroll_action_id = p_payroll_action_id;
select PA.payroll_action_id
from pay_payroll_actions PA
where PA.business_group_id = p_business_group_id
and PA.effective_date = p_period_end
and PA.legislative_parameters like 'USMAGTAPE' || '-' ||
lpad(p_report_type, 5) || '-' ||
lpad(p_state , 5) || '%';
select RM.report_format
from pay_report_format_mappings_f RM
where RM.report_type = p_report_type
and RM.report_qualifier = p_state
and RM.report_format not like '%D'
and p_period_end between RM.effective_start_date
and RM.effective_end_date;
select RM.report_format
from pay_report_format_mappings_f RM
where RM.report_type = p_report_type
and RM.report_qualifier = p_state
and RM.report_format like '%D'
and p_period_end between RM.effective_start_date
and RM.effective_end_date;
hr_utility.set_location('pay_us_magtape_reporting.run - update action status', 6);
update_action_statuses(l_payroll_action_id);