[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
12-Sep-2003 kaverma 115.2 3137858 Correct the layout and insertion of
person_id when creating actions.
20-Nov-2003 sdahiya 115.3 3263078 Modified cursor c_w2_magtape_run_exists
to correctly identify whether state 1099R/W2 magtape
processes have been run for GREs in a given
business group. Removed tax_unit_id parameter
and modified statement opening this cursor.
21-Nov-2003 sdahiya 115.4 3263078 The cursor c_w2_magtape_run_exists is modified to
check existence of 1099R magtape runs for 1099R GRE and
W2 magtape runs for W2 GRE.
12-Dec-2003 kaverma 115.5 3228332 Report should not pick up rehired employee
if terminated employee is alreday archived in YEPP.
modified c_get_latest_asg cursor.
13-Dec-2003 sodhingr 115.6 3228332 changed the cursor c_get_latest_asg to check for
assignment_type = 'E' and also added the condition
to check if an assignment action is already created for the
same person
20-Aug-2004 meshah 115.7 3440806 changed the following
cursor c_get_latest_asg,
cursor c_get_processed_assignments,
PROCEDURE
report_secondary_assignments and
added CURSOR c_get_asg_id
26-Aug-2004 meshah 115.8 fixed gscc error.
01-Sep-2004 meshah 115.9 disabled the index on
pay_action_classification
in cursor c_get_latest_asg
04-Nov-2004 meshah 115.10 3984539 changed the sequence of
get_eligible_assignments and
get_processed_assignments in the
Main of add_actions_to_yepp.
commented the cursor
get_already_marked_assignments.
changed the date join conditions
for CURSOR c_get_asg_id.
18-Apr-2006 alikhar 115.12 5120818 Performance fix for cursor c_get_latest_asg.
Added Ordered hint.
25-Aug-2006 saurgupt 115.13 3829668 Added the procedure create_archive to insert record into
ff_archive_items while creating assignment actions.
29-AUG-2006 sodhingr 115.14 3829668 archive A_ADD_ARCHIVE= Y when an assigment
is added to archive
********************************************************************/
/********************************************************************
** Local Package Variables
********************************************************************/
gv_title VARCHAR2(100) := 'Add Assignment Actions 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 for
any of the four sections -
- Processed Assignments
- Eligible Assignments
- Not Eligible Assignments
- Secondary Assignments
********************************************************************/
PROCEDURE formated_zero_count(output_file_type varchar2,
p_flag varchar2)
IS
lvc_message1 varchar2(200);
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';
select 1 from dual
where exists (
select 1
from pay_payroll_actions ppa
where ppa.business_group_id = cp_business_group_id
and ppa.action_type = 'X'
and ppa.report_type = cp_gre_type
and ppa.report_category in ('RM', 'RT')
and ppa.effective_date + 0 = add_months(cp_effective_date,12) - 1
and ppa.action_status = 'C'
) ;
select decode(org_information_context,'1099R Magnetic Report Rules','1099R','W2') gre_type
from hr_organization_information
where organization_id = cp_tax_unit_id
and org_information_context in ('1099R Magnetic Report Rules','W2 Reporting Rules');
select name,business_group_id
from hr_organization_units
where organization_id = cp_tax_unit_id;
select person_id
from per_assignments_f
where assignment_id=cp_assign_id;
select employee_number,full_name,national_identifier
from per_people_f
where person_id = cp_person_id;
l_assignment_inserted number :=0;
select fdi.user_entity_id
from ff_database_items fdi,
ff_user_entities fue
where fdi.user_name = cp_dbi_name
and fue.user_entity_id = fdi.user_entity_id
and fue.legislation_code = 'US';
select context_id
into l_context_id
from ff_contexts
where context_name = 'TAX_UNIT_ID';
insert into ff_archive_items
(ARCHIVE_ITEM_ID,
USER_ENTITY_ID,
CONTEXT1,
VALUE)
values
(ff_archive_items_s.nextval,
l_user_entity_id,
cp_asg_action_id,
'Y');
insert into ff_archive_item_contexts
(ARCHIVE_ITEM_ID,
SEQUENCE_NO,
CONTEXT,
CONTEXT_ID)
values
(ff_archive_items_s.currval,
1,
cp_gre_id,
l_context_id);
select /*+ ORDERED */max(paa.assignment_action_id),
paf1.assignment_id,
paf.person_id
from hr_assignment_set_amendments has,
per_assignments_f paf,
per_assignments_f paf1,
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_action_classifications pac
where has.assignment_set_id = cp_assign_set_id
and has.include_or_exclude = 'I'
and paf.assignment_id = has.assignment_id
and paf.assignment_type = 'E'
and paf.person_id = paf1.person_id
/* we cannot check for primary assignment. Bug 3440806 */
-- and paf.primary_flag = 'Y'
-- and paa.assignment_id = has.assignment_id
and paa.assignment_id = paf1.assignment_id
and paa.tax_unit_id = cp_gre_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 between paf.effective_start_date
and paf.effective_end_date
and ppa.effective_date between paf1.effective_start_date
and paf1.effective_end_date
and ppa.effective_date between cp_effective_date
and add_months(cp_effective_date, 12) - 1
and ((nvl(paa.run_type_id, ppa.run_type_id) is null
and paa.source_action_id is null)
or (nvl(paa.run_type_id, ppa.run_type_id) is not null
and paa.source_action_id is not null )
or (ppa.action_type = 'V' and ppa.run_type_id is null
and paa.run_type_id is not null
and paa.source_action_id is null))
and not exists( SELECT 1
FROM pay_payroll_actions ppa1, -- Year End
pay_assignment_actions paa1 -- Year End
WHERE ppa1.report_type = 'YREND'
AND ppa1.action_status = 'C'
AND ppa1.effective_date = add_months(cp_effective_date, 12) - 1
AND to_number(substr(legislative_parameters,
instr(legislative_parameters,'TRANSFER_GRE=') +
length('TRANSFER_GRE='))) = cp_gre_id -- Bug 3228332
AND ppa1.payroll_action_id = paa1.payroll_action_id
/* we should be checking for existance, irrespective of the action type. If we check for
action status of C and M then the ones marked for retry will be selected and duplicate
actions will be created */
-- AND (paa1.action_status = 'C' or paa1.action_status = 'M')
AND paa1.serial_number = to_char(paf.person_id)) -- Bug 3228332
group by paf1.assignment_id,paf.person_id
order by paf1.assignment_id desc;
select payroll_action_id
from pay_payroll_actions
where action_type = 'X'
and action_status = 'C'
and report_type = 'YREND'
and pay_core_utils.get_parameter('TRANSFER_GRE',legislative_parameters) = cp_gre_id
and effective_date = add_months(cp_effective_date, 12) - 1;
select paa.assignment_id,
paa.assignment_action_id
from pay_assignment_actions paa,
hr_assignment_set_amendments has
where paa.payroll_action_id = cp_payroll_action_id
and paa.action_status = 'M'
and paa.tax_unit_id = cp_gre_id
and has.assignment_set_id = cp_assignment_set_id
and paa.assignment_id = has.assignment_id
and nvl(has.include_or_exclude,'I') = 'I';
assignment selected in the assignment set is secondary */
/* Get the primary assignment for the given person_id */
CURSOR c_get_asg_id (p_person_id number) IS
SELECT assignment_id
from per_all_assignments_f paf
where person_id = p_person_id
and primary_flag = 'Y'
and assignment_type = 'E'
and paf.effective_start_date <= add_months(p_effective_date, 12) - 1
and paf.effective_end_date >= p_effective_date
ORDER BY assignment_id desc;
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
/* Insert into pay_assignment_actions. */
hr_utility.trace('creating asg action');
/* insert into ff_archive_items */
-- Bug 3829668
hr_utility.trace('creating ff_archive_items entry');
/* Bug No : 3137858 Update the serial number column with the person id
So that retry us payroll process archives balance values*/
hr_utility.trace('updating asg action');
update pay_assignment_actions aa
set aa.serial_number = to_char(l_person_id)
where aa.assignment_action_id = lockingactid;
select distinct has.assignment_id
from hr_assignment_set_amendments has,
per_assignments_f paf
where has.assignment_set_id = cp_assignment_set_id
and paf.assignment_id = has.assignment_id
and paf.primary_flag = 'Y'
and exists
( select '1'
FROM pay_payroll_actions ppa, -- Year End
pay_assignment_actions paa -- Year End
WHERE ppa.report_type = 'YREND'
AND ppa.action_status = 'C'
AND ppa.effective_date = add_months(cp_effective_date, 12) - 1
AND instr(ppa.legislative_parameters, cp_gre_id)>0
AND ppa.payroll_action_id = paa.payroll_action_id
AND paa.action_status = 'C'
AND paa.assignment_id = has.assignment_id);
select distinct has.assignment_id
from hr_assignment_set_amendments has,
per_assignments_f paf
where has.assignment_set_id = cp_assignment_set_id
and paf.assignment_id = has.assignment_id
and exists( SELECT 1
FROM pay_payroll_actions ppa1, -- Year End
pay_assignment_actions paa1 -- Year End
WHERE ppa1.report_type = 'YREND'
AND ppa1.action_status = 'C'
AND ppa1.effective_date = add_months(cp_effective_date, 12) - 1
AND to_number(substr(legislative_parameters,
instr(legislative_parameters,'TRANSFER_GRE=') +
length('TRANSFER_GRE='))) = cp_gre_id -- Bug 3228332
AND ppa1.payroll_action_id = paa1.payroll_action_id
AND paa1.serial_number = to_char(paf.person_id)); -- Bug 3228332
select distinct has.assignment_id
from hr_assignment_set_amendments has,
per_assignments_f paf,
pay_us_asg_reporting puar
where has.assignment_set_id = cp_assignment_set_id
and paf.assignment_id = has.assignment_id
and nvl(has.include_or_exclude,'I') = 'I'
and paf.effective_start_date <= add_months(cp_effective_date, 12) - 1
and paf.effective_end_date >= cp_effective_date
and puar.assignment_id = paf.assignment_id
and puar.tax_unit_id = cp_gre_id
and paf.primary_flag = 'Y';
l_assignment_inserted := l_assignment_inserted + 1;
select distinct has.assignment_id
from hr_assignment_set_amendments has,
per_assignments_f paf,
pay_us_asg_reporting puar
where assignment_set_id = cp_assignment_set_id
and paf.assignment_id = has.assignment_id
and nvl(has.include_or_exclude,'I') = 'I'
and paf.effective_start_date <= add_months(cp_effective_date, 12) - 1
and paf.effective_end_date >= cp_effective_date
and puar.assignment_id = paf.assignment_id
and puar.tax_unit_id = cp_gre_id
and paf.primary_flag <> 'Y';
select distinct has.assignment_id, puar.tax_unit_id
from hr_assignment_set_amendments has,
per_assignments_f paf,
pay_us_asg_reporting puar
where assignment_set_id = cp_assignment_set_id
and paf.assignment_id = has.assignment_id
and nvl(has.include_or_exclude,'I') = 'I'
and paf.effective_start_date <= add_months(cp_effective_date, 12) - 1
and paf.effective_end_date >= cp_effective_date
and puar.assignment_id = paf.assignment_id
and puar.tax_unit_id <> cp_gre_id;
select name
from hr_organization_units
where organization_id = cp_gre_id;
looks for assignments for whom YEPP is not run and inserts a action with
M and get_processed_assignments looks for assignment in YEPP. If we have
get_eligible_assignments before get_processed_assignments we will always
have an action in YEPP.
*/
-- Call get_processed_assignments
hr_utility.set_location(gv_package_name || '.add_actions_to_yepp', 30);
if l_assignment_inserted=0 then
hr_utility.set_location(gv_package_name || '.add_actions_to_yepp', 110);
l_assignment_inserted := 0;
if l_assignment_inserted=0 then
hr_utility.set_location(gv_package_name || '.add_actions_to_yepp', 260);
l_assignment_inserted := 0;
if l_assignment_inserted=0 then
hr_utility.set_location(gv_package_name || '.add_actions_to_yepp', 230);
UPDATE fnd_concurrent_requests
SET output_file_type = 'HTML'
WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID ;