[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
c_missing_assignments to select
only primary assignments.
29-AUG-2003 rsethupa 115.3 2527077 Added Comments.
10-SEP-2003 rsethupa 115.4 3135440 Modified call to procedure
hr_assignment_sets_pkg.insert_row
to create assignment set, by
passing the business_group_id
of the assignment instead of
tax unit ID/GRE ID
10-NOV-2003 sodhingr 115.5 3228332 Changed the procedure select_employee
- modified the cursor c_missing_assignment
- reset the flag lv_result_value.
07-APR-2004 ssmukher 115.6 3558449 Modified the cursor c_missing_assignments
in the procedure select_employee.
The RULE based hint in the cursor
c_non_zero_run_result is also removed
03-AUG-2004 meshah 115.7 3440806 Modified the cursor c_missing_assignments
removed the join that checks for
primary assignment.
17-SEP-2004 meshah 115.8 3505495 Changed CURSOR c_non_zero_run_result in
PROCEDURE select_employee.
added a check for balance feeds .
25-AUG-2005 rsethupa 115.9 4160436 1. Rewrite of the Package for
Multithreading of Report.
2. Added section to Display Elements
05-SEP-2005 rsethupa 115.10 Corrected name of lookup_type to
YE_ARCH_REPORTS_BAL_ATTRIBUTES in
Element Information section
16-SEP-2005 sdhole 115.11 4613898 Modified the Cursor
c_get_missing_assignments.
21-SEP-2005 sdhole 115.12 Commented the code which displays
the Element information.
/************************************************************
** Local Package Variables
************************************************************/
gv_title VARCHAR2(100) := ' Year End Archive Missing Assignments Report';
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_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 distinct element_name, classification_name,piv.name
from pay_element_types_f pet,
pay_element_classifications pec,
pay_input_values_f piv
where pet.element_type_id = piv.element_type_id
and piv.uom = 'M'
and pet.classification_id = pec.classification_id
and pet.business_group_id = cp_business_group_id
and (pec.legislation_code = cp_legislation_code or
pec.business_group_id = cp_business_group_id)
and piv.input_value_id not in
(
select distinct piv.input_value_id
from pay_element_types_f pet,
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 pet.element_type_id = piv.element_type_id
and pet.business_group_id = cp_business_group_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);
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;