[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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 = 'US';
lvc_message := 'No person was picked up for comparision based on selection parameters.' ||
' Ensure GRE has employees and all employees were successfully archived in the YEPP for the GRE.';
** paramaters selected in SRS the report will compare the the
** the Values and print the the values for the assignment that
** have diffrent live and archive balances. The output format of
** the report will be either a CSV format or an HTML format.
*****************************************************************/
PROCEDURE select_employee
(errbuf OUT nocopy varchar2,
retcode OUT nocopy number,
p_year IN VARCHAR2,
p_tax_unit_id IN NUMBER,
p_fed_state IN VARCHAR2,
p_is_state IN VARCHAR2,
p_state_code IN VARCHAR2,
p_box_type IN VARCHAR2, -- Bug 2554865
p_box_name IN VARCHAR2,
p_output_file_type IN VARCHAR2
)
IS
/************************************************************
** Cursor to get all the employee and assignment data.
** This cursor will return one row for each Assignment Action
** based on the Selection parameter entered by the user
** in the SRS.
************************************************************/
/**************************************************************
Parameter for the Cursor :c_select_assignment
c_end_of_year -- 31st Dec. of the Year in date format
c_state_of_year -- 1st of Jan of Year in date format
c_gre_id -- GRE id in character Format
**************************************************************/
CURSOR c_select_assignment(c_end_of_year date,
c_start_of_year date,
c_gre_id varchar2 )
IS
SELECT assignment_action_id ,
serial_number ,
tax_unit_id
FROM pay_payroll_actions ppa, -- Year End
pay_assignment_actions paa, -- Year End
per_assignments_f paf
WHERE ppa.report_type = 'YREND'
AND ppa.action_status = 'C'
AND ppa.effective_date = c_end_of_year
AND ppa.legislative_parameters like c_gre_id || ' TRANSFER%'
AND ppa.payroll_action_id = paa.payroll_action_id
AND paa.action_status = 'C'
AND paf.assignment_id = paa.assignment_id
AND paf.effective_start_date = ( SELECT max(paf2.effective_start_date)
FROM per_assignments_f paf2
WHERE paf2.assignment_id = paf.assignment_id
AND paf2.effective_start_date <= c_end_of_year )
AND paf.effective_end_date >= c_start_of_year;
SELECT paa.assignment_action_id
FROM
pay_assignment_actions paa,
per_assignments_f paf,
pay_payroll_actions ppa,
pay_action_classifications pac
WHERE paf.person_id = c_person_id
AND paa.assignment_id = paf.assignment_id
AND paa.tax_unit_id = c_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 c_start_of_year and
c_end_of_year
order by paa.action_sequence desc;
Cursor to get state Abbrevaiation for the Selected State
*************************************************************/
Cursor c_selected_state
IS SELECT state_abbrev
FROM pay_us_states
where state_code = p_state_code;
IS SELECT description,to_char(sysdate,'mm/dd/yyyy HH:MI')
FROM fnd_common_lookups
WHERE application_id = 801
AND lookup_type = c_lookup_type
AND meaning = c_meaning;
IS SELECT name
FROM hr_organization_units
WHERE organization_id = p_tax_unit_id;
IS SELECT employee_number
FROM per_people_f
WHERE person_id = c_person_id;
hr_utility.set_location(gv_package_name || '.select_employee', 10);
gvr_balance.delete;
OPEN c_selected_state;
FETCH c_selected_state INTO lvc_state_abbrev;
CLOSE c_selected_state;
hr_utility.set_location(gv_package_name || '.select_employee', 20);
for i in c_select_assignment(lvd_end_of_year,
lvd_start_of_year,
lvc_tax_unit_id ) loop
lvn_person_id := to_number(i.serial_number);
hr_utility.set_location(gv_package_name || '.select_employee', 30);
hr_utility.set_location(gv_package_name || '.select_employee', 40);
hr_utility.set_location(gv_package_name || '.select_employee', 50);
hr_utility.set_location(gv_package_name || '.select_employee', 60);
hr_utility.set_location(gv_package_name || '.select_employee', 70);
hr_utility.set_location(gv_package_name || '.select_employee', 80);
hr_utility.set_location(gv_package_name || '.select_employee', 90);
hr_utility.set_location(gv_package_name || '.select_employee', 100);
hr_utility.set_location(gv_package_name || '.select_employee', 110);
hr_utility.set_location(gv_package_name || '.select_employee', 130);
UPDATE fnd_concurrent_requests
SET output_file_type = 'HTML'
WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID ;
hr_utility.set_location(gv_package_name || '.select_employee', 160);
END select_employee;