[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
in for update clause
09-JAN-2003 ahanda 115.13 Sort cursor changed ti Fix Bug 2743186
09-JAN-2003 asasthan 115.14 nocopy changes
07-AUG-2003 jgoswami 115.15 Action cursor changed to Fix Bug 2573628
split into two cursors as c_actions_with_asg_set
and c_actions_without_asg_set
11-AUG-2003 jgoswami 115.16 Commented the to_char(USERENV('SESSIONID'))
05-SEP-2003 ahanda 115.17 Changed sort_action to not go to secure view.
As the action is already created the sort_cursor
should go to the base table(Bug 3131302).
09-SEP-2004 rsethupa 115.18 Modified cursors in the action_creation
procedure to fetch only from
secure view per_assignments_f.
14-MAR-2005 sackumar 115.19 Bug 4222032
Change in the Range Cursor removing redundant
use of bind Variable (:pactid)
25-MAY-2005 ahanda 115.20 Bug 4378773
Changed function get_parameter to check for
exact name i.e. ' ' || name || '='
12-SEP-2005 ynegoro 115.21 Bug 2538173, added locality parameter
21-SEP-2005 ynegoro 115.22 Bug 2538173, Modifed for locality parameter
22-SEP-2005 ahanda 115.23 Changed action creation for locality param.
31-AUG-2006 saurgupt 115.24 Bug 3913757 : Made change to sort_action. Added the employee
name in the sort2 and sort3 if no sort option is provided in
sort2 and sort3.
12-MAY-2008 keyazawa 115.24 bug 5896290 added deinitialize_code
16-SEP-2008 asgugupt 115.25 Changed where Clause in action_creation procedure
when state_code is passed
*/
--
c_package constant varchar2(31) := 'pay_yrend_reports_pkg.';
select pay_yrend_reports_pkg.get_parameter('T_U_ID',ppa1.legislative_parameters),
pay_yrend_reports_pkg.get_parameter('P_YEAR',ppa1.legislative_parameters),
ppa.payroll_action_id
into l_tax_unit_id,
l_assign_year,
l_eoy_payroll_action_id
from pay_payroll_actions ppa, /* EOY payroll action id */
pay_payroll_actions ppa1 /* PYUGEN payroll action id */
where ppa1.payroll_action_id = pactid
and ppa.effective_date = to_date('31-DEC-'||
pay_yrend_reports_pkg.get_parameter
('P_YEAR',ppa1.legislative_parameters), 'DD-MON-YYYY')
and ppa.report_type = 'YREND'
and pay_yrend_reports_pkg.get_parameter
('T_U_ID',ppa1.legislative_parameters) =
pay_yrend_reports_pkg.get_parameter
('TRANSFER_GRE',ppa.legislative_parameters);
sqlstr := 'select distinct to_number(act.serial_number)
from pay_assignment_actions act /* W2 Register Information */
where :pactid is not null
and act.payroll_action_id = ' || l_eoy_payroll_action_id ||
' and act.tax_unit_id = ' || l_tax_unit_id ||
' order by to_number(act.serial_number)';
select act.assignment_action_id,
act.assignment_id,
act.tax_unit_id,
to_number(act.serial_number)
-- need to select person id to check for assignment set
from pay_assignment_actions act
where act.payroll_action_id = c_eoy_payroll_action_id
and act.tax_unit_id = c_tax_unit_id
and to_number(act.serial_number) between stperson and endperson
and exists ( select 1
from per_assignments_f paf,
hr_assignment_set_amendments hasa
where hasa.assignment_set_id = c_assign_set
and hasa.assignment_id = paf.assignment_id
and upper(hasa.include_or_exclude) = 'I'
and c_assign_set is not null
and paf.person_id = to_number(act.serial_number)
);
/* when assignment_set is not selected */
-- #3871087 Included join with per_assignments_f
CURSOR c_actions_without_asg_set
(
pactid number,
stperson number,
endperson number,
c_assign_year number,
c_tax_unit_id pay_assignment_actions.tax_unit_id%type,
c_eoy_payroll_action_id pay_payroll_actions.payroll_action_id%type,
c_effective_date pay_payroll_actions.effective_date%TYPE,
c_start_date pay_payroll_actions.start_date%TYPE
) is
select act.assignment_action_id,
act.assignment_id,
act.tax_unit_id,
to_number(act.serial_number)
-- need to select person id to check for assignment set
from pay_assignment_actions act,
per_assignments_f paf
where act.payroll_action_id = c_eoy_payroll_action_id
and act.tax_unit_id = c_tax_unit_id
and paf.assignment_id = act.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_effective_date )
AND paf.effective_end_date >= c_start_date
AND paf.assignment_type = 'E'
and to_number(act.serial_number) between stperson and endperson;
select context_id from ff_contexts
where context_name = p_context_name;
select user_entity_id
from ff_user_entities
where user_entity_name = p_user_entity_name
and legislation_code = 'US';
select pay_yrend_reports_pkg.get_parameter('ASSIGN_SET',ppa1.legislative_parameters),
pay_yrend_reports_pkg.get_parameter('T_U_ID',ppa1.legislative_parameters),
pay_yrend_reports_pkg.get_parameter('P_YEAR',ppa1.legislative_parameters),
ppa.payroll_action_id,
ppa.effective_date,
ppa.start_date
,hr_us_w2_mt.get_parameter('STATE',ppa1.legislative_parameters)
,hr_us_w2_mt.get_parameter('LOCALITY',ppa1.legislative_parameters)
into l_assign_set,
l_tax_unit_id,
l_assign_year,
l_eoy_payroll_action_id,
l_effective_date,
l_start_date
,l_state_code -- BUG2538173
,l_locality_code
from pay_payroll_actions ppa, /* W2 payroll action id */
pay_payroll_actions ppa1 /* PYUGEN payroll action id */
where ppa1.payroll_action_id = pactid
and ppa.effective_date = to_date('31-DEC-'|| pay_yrend_reports_pkg.get_parameter
('P_YEAR',ppa1.legislative_parameters), 'DD-MON-YYYY')
and ppa.report_type = 'YREND'
and pay_yrend_reports_pkg.get_parameter
('T_U_ID',ppa1.legislative_parameters) =
pay_yrend_reports_pkg.get_parameter
('TRANSFER_GRE',ppa.legislative_parameters);
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
/* update pay_assignment_actions with the year end assignment_actions into serial number
this might help in faster processing at report level and avoid some joins */
update pay_assignment_actions
set serial_number = lockedactid
where assignment_action_id = lockingactid;
'select act.assignment_action_id,
act.assignment_id,
act.tax_unit_id,
to_number(act.serial_number)
-- need to select person id to check for assignment set
from pay_assignment_actions act,
per_assignments_f paf
where act.payroll_action_id = ' || l_eoy_payroll_action_id || '
and act.tax_unit_id = ' || l_tax_unit_id || '
and paf.assignment_id = act.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 <= ''' ||
l_effective_date || ''' )
AND paf.effective_end_date >= ''' || l_start_date || '''
AND paf.assignment_type = ''E''
and to_number(act.serial_number) between ' || stperson || ' and ' ||endperson;
' AND exists ( select 1 from dual
where 1 =
--bug 7392315
-- (select decode(sign(nvl(sum(to_number(fai.value)),0)),-1,1,0,0,1)
(select decode(sign(nvl(sum(to_number(fai.value)),0)),-1,1,0,1,1)
--bug 7392315
from ff_archive_items fai,
ff_archive_item_contexts fic1,
ff_archive_item_contexts fic2
where fai.context1 = act.assignment_action_id
and fai.user_entity_id in (' || l_subj_whable || ',
' || l_subj_nwhable || ')
and fai.archive_item_id = fic1.archive_item_id
and fic1.context_id = ' || l_tuid_context || '
and ltrim(rtrim(fic1.context)) = to_char(act.tax_unit_id)
and fai.archive_item_id = fic2.archive_item_id
and fic2.context_id = ' || l_juri_context || '
and substr(ltrim(rtrim(fic2.context)),1,2) = ' || l_state_code || ' ))';
' AND exists ( select 1 from dual
where 1 =
(select decode(sign(nvl(sum(to_number(fai.value)),0)),-1,1,0,0,1)
from ff_archive_items fai,
ff_archive_item_contexts fic1,
ff_archive_item_contexts fic2
where fai.context1 = act.assignment_action_id
and fai.user_entity_id in (' || l_subj_whable || ',
' || l_subj_nwhable || ')
and fai.archive_item_id = fic1.archive_item_id
and fic1.context_id = ' || l_tuid_context || '
and ltrim(rtrim(fic1.context)) = to_char(act.tax_unit_id)
and fai.archive_item_id = fic2.archive_item_id
and fic2.context_id = ' || l_juri_context || '
and substr(ltrim(rtrim(fic2.context)),1,6) = substr(''' || l_locality_code || ''',1,6) ))';
' AND exists ( select 1 from dual
where 1 =
(select decode(sign(nvl(sum(to_number(fai.value)),0)),-1,1,0,0,1)
from ff_archive_items fai,
ff_archive_item_contexts fic1,
ff_archive_item_contexts fic2
where fai.context1 = act.assignment_action_id
and fai.user_entity_id in (' || l_subj_whable || ',
' || l_subj_nwhable || ')
and fai.archive_item_id = fic1.archive_item_id
and fic1.context_id = ' || l_tuid_context || '
and ltrim(rtrim(fic1.context)) = to_char(act.tax_unit_id)
and fai.archive_item_id = fic2.archive_item_id
and fic2.context_id = ' || l_juri_context || '
and substr(ltrim(rtrim(fic2.context)),1,11) = ''' || l_locality_code || ''' ))';
' AND exists ( select 1 from dual
where 1 =
(select decode(sign(nvl(sum(to_number(fai.value)),0)),-1,1,0,0,1)
from ff_archive_items fai,
ff_archive_item_contexts fic1,
ff_archive_item_contexts fic2
where fai.context1 = act.assignment_action_id
and fai.user_entity_id in (' || l_subj_whable || ',
' || l_subj_nwhable || ')
and fai.archive_item_id = fic1.archive_item_id
and fic1.context_id = ' || l_tuid_context || '
and ltrim(rtrim(fic1.context)) = to_char(act.tax_unit_id)
and fai.archive_item_id = fic2.archive_item_id
and fic2.context_id = ' || l_juri_context || '
and substr(ltrim(rtrim(fic2.context)),1,8) = ''' || l_locality_code || '''))';
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
update pay_assignment_actions
set serial_number = lockedactid
where assignment_action_id = lockingactid;
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
/* update pay_assignment_actions with the year end assignment_actions into serial number
this might help in faster processing at report level and avoid some joins */
update pay_assignment_actions
set serial_number = lockedactid
where assignment_action_id = lockingactid;
select hr_us_w2_mt.get_parameter('YEAR',ppa1.legislative_parameters),
hr_us_w2_mt.get_parameter('GRE_ID',ppa1.legislative_parameters),
hr_us_w2_mt.get_parameter('ORG_ID',ppa1.legislative_parameters),
hr_us_w2_mt.get_parameter('LOC_ID',ppa1.legislative_parameters),
hr_us_w2_mt.get_parameter('PER_ID',ppa1.legislative_parameters),
hr_us_w2_mt.get_parameter('SSN',ppa1.legislative_parameters),
hr_us_w2_mt.get_parameter('STATE',ppa1.legislative_parameters),
hr_us_w2_mt.get_parameter('P_S1',ppa1.legislative_parameters),
hr_us_w2_mt.get_parameter('P_S2',ppa1.legislative_parameters),
hr_us_w2_mt.get_parameter('P_S3',ppa1.legislative_parameters),
ppa1.effective_date,
ppa1.business_group_id
into l_year,
l_gre_id,
l_org_id,
l_loc_id,
l_per_id,
l_ssn,
l_state_code,
l_sort1 ,
l_sort2,
l_sort3,
l_dt, --session_date
l_bg_id
from pay_payroll_actions ppa1 /* PYUGEN payroll action id */
where ppa1.payroll_action_id = payactid;
sqlstr := 'select paa1.rowid
/* we need the row id of the assignment actions that are
created by PYUGEN */
from hr_all_organization_units hou,
hr_locations_all loc,
per_all_people_f ppf, -- #1894165
per_all_assignments_f paf,
/*pay_assignment_actions paa,*/
pay_payroll_actions ppa1,
pay_assignment_actions paa1 /* PYUGEN assignment action */
where ppa1.payroll_action_id = :pactid
and paa1.payroll_action_id = ppa1.payroll_action_id
and paf.assignment_id = paa1.assignment_id
and paf.effective_start_date =
(select max(paf2.effective_start_date)
from per_all_assignments_f paf2 -- #3871087
where paf2.assignment_id = paf.assignment_id
and paf2.effective_start_date <= ppa1.effective_date)
and paf.effective_end_date >= ppa1.start_date
and paf.assignment_type = ''E''
/* if assignments organization_id is null pick assignment
business_group_id to avoid assertion error. Bug No: 1894165 */
and hou.organization_id =
nvl(paf.organization_id,paf.business_group_id) -- #1894165
/* if assignments location_id is null pick assignments
organization/business groups location_id to avoid assertion
error. Bug No: 1894165 */
and loc.location_id = nvl(paf.location_id,hou.location_id)
and ppf.person_id = paf.person_id
and ppa1.effective_date between
ppf.effective_start_date and ppf.effective_end_date
order by
decode(' || '''' || l_sort1 || '''' ||
',''Employee_Name'', ppf.last_name||'' ''||ppf.first_name ||'' ''||ppf.middle_names,
''Social_Security_Number'',ppf.national_identifier,
''Organization'',hou.name,
''Location'',loc.location_code,
ppf.last_name||'' ''||ppf.first_name ||'' ''||ppf.middle_names),
decode(' || '''' || l_sort2 || '''' ||
',''Employee_Name'',ppf.last_name||'' ''||ppf.first_name ||'' ''||ppf.middle_names,
''Social_Security_Number'',ppf.national_identifier,
''Organization'',hou.name,
''Location'',loc.location_code,
ppf.last_name||'' ''||ppf.first_name ||'' ''||ppf.middle_names),
decode(' || '''' || l_sort3 || '''' ||
',''Employee_Name'',ppf.last_name||'' ''||ppf.first_name ||'' ''||ppf.middle_names,
''Social_Security_Number'',ppf.national_identifier,
''Organization'',hou.name,
''Location'',loc.location_code,
ppf.last_name||'' ''||ppf.first_name ||'' ''||ppf.middle_names)
';
-- for update of paf.assignment_id
--
--
len := length(sqlstr); -- return the length of the string.
select 'Y'
from dual
where exists(
select /*+ ORDERED */
null
from pay_payroll_actions ppa,
pay_us_rpt_totals purt
where ppa.payroll_action_id = p_payroll_action_id
and ppa.action_status = 'E'
and purt.session_id = p_payroll_action_id);
select rowid
from pay_us_rpt_totals
where session_id = p_payroll_action_id;
delete from pay_us_rpt_totals
where rowid = l_csr_del.rowid;
hr_utility.trace('pay_yrend_report_pkg delete '||to_char(l_del_cnt)||' records');