[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select_employee to make the
pkg global. Removed display of
input values from the report
22-dec-2005 rdhingra 115.1 4779018 Updated Function
formated_header_string to
make column headings global
19-JAN-2007 ydevi 115.2 4886285 adding p_pre_or_gre in the
definition of
formated_header_string
Change the code of
select_employees to deal with
the employees of RL1 and RL2
PRE.
******************************************************************************/
/************************************************************
** Local Package Variables
************************************************************/
gv_title VARCHAR2(100) := ' Year End Archive Missing Assignments Report';
SELECT message_text
FROM fnd_new_messages
WHERE message_name = 'HR_NATIONAL_ID_NUMBER_'|| lv_legislation_code
AND language_code = USERENV('LANG');
Function to display the details of the selected employee
***************************************************************/
FUNCTION formated_detail_string(
p_output_file_type in VARCHAR2
,p_year VARCHAR2
,p_gre VARCHAR2
,p_Employee_name VARCHAR2
,p_employee_ssn VARCHAR2
,p_emplyee_number VARCHAR2
) RETURN VARCHAR2
IS
lv_format1 VARCHAR2(22000);
Procedure to display message if no employees are selected
*************************************************************************/
PROCEDURE formated_zero_count(output_file_type VARCHAR2)
IS
lvc_message VARCHAR2(200);
lvc_message := 'No person was picked up based on selection parameters.' ||
' The YEPP Archive for the GRE has no missing assignments.';
Procedure to display the name of the assignment set to which the selected
assignments are added
************************************************************************/
PROCEDURE formated_assign_count(assignment_set_name in VARCHAR2,
assignment_set_id in number,
record_count in number,
assign_set_created in number,
output_file_type in VARCHAR2)
is
lvc_message1 VARCHAR2(400);
Name: select_employee
Description: This procedure fetches the assignments
archived in PAY_US_RPT_TOTALS by the
package PAY_ARCHIVE_MISSING_ASG_PKG
and generates the report in the specified
format.
*****************************************************/
PROCEDURE select_employee(p_payroll_action_id IN NUMBER,
p_effective_date IN VARCHAR2,
p_tax_unit_id IN NUMBER,
p_session_id in NUMBER)
is
CURSOR c_gre_or_pre_name(p_tax_unit_id number)
IS
SELECT name
FROM hr_organization_units
WHERE organization_id = p_tax_unit_id;
SELECT person_id,business_group_id
FROM per_all_assignments_f
WHERE assignment_id=c_assign_id;
SELECT employee_number,full_name,national_identifier
FROM per_people_f
WHERE person_id = c_person_id;
SELECT hr_assignment_sets_s.nextval
FROM dual;
SELECT assignment_set_id
FROM hr_assignment_sets
WHERE assignment_set_name=assign_set_name;
SELECT 1
FROM hr_assignment_set_amendments
WHERE assignment_set_id=c_assignment_set_id
AND assignment_id=c_assignment_id;
SELECT business_group_id
FROM hr_organization_units
WHERE organization_id = c_tax_unit_id;
SELECT distinct value1
FROM PAY_US_RPT_TOTALS
WHERE location_id = cp_payroll_action_id
AND tax_unit_id = cp_tax_unit_id
AND attribute1 = 'YEAR END MISSING ASSIGNMENTS';
SELECT legislation_code
FROM per_business_groups
WHERE business_group_id = cp_business_group_id;
select /*+ USE_NL(pet, pec)
INDEX( pet PAY_ELEMENT_TYPES_F_PK) */
distinct pet.element_name, pec.classification_name
from pay_element_types_f pet,
pay_element_classifications pec
where pet.classification_id = pec.classification_id
and pet.business_group_id = cp_business_group_id
and hr_api.return_legislation_code(cp_business_group_id) = cp_legislation_code
and ((pec.legislation_code = cp_legislation_code and pec.business_group_id is null) or
(pec.business_group_id = cp_business_group_id and pec.legislation_code is null)
)
and not exists
(
select 1
from pay_input_values_f piv,
pay_balance_feeds_f pbf,
pay_balance_types pbt,
pay_defined_balances pdb,
pay_balance_attributes pba,
pay_bal_attribute_definitions pbad
where piv.element_type_id = pet.element_type_id
and piv.uom = 'M'
and piv.input_value_id = pbf.input_value_id
and pbf.balance_type_id = pbt.balance_type_id
and pbt.balance_type_id = pdb.balance_type_id
and pdb.defined_balance_id = pba.defined_balance_id
and pba.attribute_id = pbad.attribute_id
and pbad.legislation_code = cp_legislation_code
and pbad.attribute_name in
(select distinct fcl.lookup_code
from fnd_common_lookups fcl,
fnd_lookup_values flv
where fcl.lookup_type = 'YE_ARCH_REPORTS_BAL_ATTRIBUTES'
and fcl.lookup_type = flv.lookup_type
and flv.tag = '+' || cp_legislation_code
and fcl.lookup_code = flv.lookup_code
)
);
hr_utility.set_location(gv_package_name || '.select_employee', 10);
select legislative_parameters
into lv_legislative_param
from pay_payroll_actions
where payroll_action_id = p_payroll_action_id;
select pay_us_payroll_utils.get_parameter(
'ASSIGNMENT_SET',
lv_legislative_param),
pay_us_payroll_utils.get_parameter(
'OUTPUT_TYPE',
lv_legislative_param)
into lv_assignment_set, lv_output_file_type
from dual;
hr_utility.set_location(gv_package_name || '.select_employee', 20);
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_assignment_sets_pkg.insert_row(row_id,
lv_assignment_set_id,
lv_business_group_id,
lv_payroll_id,
lv_assignment_set,
lv_formula_id);
hr_utility.set_location(gv_package_name || '.select_employee', 70);
hr_utility.set_location(gv_package_name || '.select_employee', 80);
/*** inserting into HR_ASSIGNMENT_SET_AMENDMENTS **/
hr_assignment_set_amds_pkg.insert_row(row_id,lv_assignment_id,lv_assignment_set_id,'I');
hr_assignment_set_amds_pkg.insert_row(row_id,lv_assignment_id,lv_assignment_set_id,'I');
hr_utility.set_location(gv_package_name || '.select_employee', 90);
hr_utility.set_location(gv_package_name || '.select_employee', 40);
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', 120);
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', 130);
hr_utility.set_location(gv_package_name || '.select_employee', 140);
hr_utility.set_location(gv_package_name || '.select_employee', 150);
hr_utility.set_location(gv_package_name || '.select_employee', 160);
hr_utility.set_location(gv_package_name || '.select_employee', 180);
END select_employee;