[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
elements selected by the user
in query mode
Modified the function get_bal
to consider balance adjustments
done after actual termination date.
Modified the cursors
'c_get_element_runs' ,
'csr_element_assact_info',
'csr_element_assact_runs',
'c_element_assact_balances',
'c_element_asg_balances'
to retrieve ROWID.
06-Apr-2004 kvsankar 115.2 3541052 Modified the date passed for
getting the value of the defined
balances
12-Apr-2004 kvsankar 115.3 3311781 Corrected GSCC warnings
15-Apr-2004 kvsankar 115.4 3311781 Removed the cursor
'c_element_assact_balances' since
Balances for 'Balance
Initialization' cannot be seen
using assignment action mode. Also
made changes to cursors
'csr_element_assact_info',
'csr_element_assact_runs',
'c_element_asg_balances' to
include the changes for 'PER'
level balances.
19-Apr-2004 kvsankar 115.5 3369361 Added the condition
pac.tax_unit_id := p_tax_unit_id
in cursor c_action_type
11-May-2004 kvsankar 115.6 3300433 Changed the select query written
for Bug Fix 3300433 to return
final process date instead of
last standard process date
17-Mar-2005 meshah 115.7 4039299 changed cursor
csr_element_assact_runs and changed
the exists clause.
13-Jan-2006 rpasumar 115.8 4915420 Changed per_assignments_f to
per_all_assignments_f to
improve performance.
23-Aug-2006 kvsankar 115.9 5460886 Added a new cursor
csr_element_assact_info_dedn for
the following classifications
* Pre-Tax Deductions
* Involuntary Deductions
* Voluntary Deduction
******************************************************************************/
l_package VARCHAR2(30);
select pay.action_type
from pay_assignment_actions pac
,pay_payroll_actions pay
where pay.payroll_action_id = pac.payroll_action_id
and pac.assignment_id = p_assignment_id
and pac.action_status = 'C'
and pac.tax_unit_id = p_tax_unit_id
and exists (select 'x'
from pay_run_results prr
where prr.assignment_action_id = pac.assignment_action_id )
order by decode(pay.action_type,'Q','1','R','1','I','2','3');
select distinct pet.rowid
,pet.element_name
,pet.element_type_id
,pet.classification_id
,pet.element_information10
,pet.element_information11
,pet.element_information12
,pet.element_information14
from pay_element_types_f pet
, pay_element_types_f pet2
, pay_element_entries_f ee
WHERE pet2.classification_id = p_classification_id
AND pet2.element_information10 is not null
AND ee.effective_end_date >= p_pay_start_date
AND ee.effective_start_date <= nvl(p_action_date , p_session_date )
AND ee.effective_start_date between pet2.effective_start_date and pet2.effective_end_date
AND pet2.element_type_id = pet.element_type_id
AND PET.effective_start_date = (select max(pet1.effective_start_date)
from pay_element_types_f pet1
where pet1.element_type_id = pet.element_type_id
and pet1.effective_start_date <= p_session_date )
AND pet.element_name not like 'VERTEX%'
AND ee.assignment_id = p_assignment_id
AND EXISTS
(select prr.element_type_id
from pay_run_results prr
where prr.source_id = ee.element_entry_id
and prr.source_type in ( 'E' , 'I' )
and prr.element_type_id + 0 = pet.element_type_id
)
order by 2;
select /*+ index (pet pay_element_types_f_fk1) */ distinct pet.rowid
,pet.element_name
,pet.element_type_id
,pet.classification_id
,pet.element_information10
,pet.element_information11
,pet.element_information12
,pet.element_information14
,decode (p_balance_level,
'ASG',(PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM (UPPER(pbT.balance_name) ,
'ASG_GRE_PTD' ,
p_assignment_action_id ,
NULL,
NULL,
p_tax_unit_id,
p_business_group_id ,
NULL)),
'PER',NULL) PTD_VAL
,PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM (UPPER(pBT.balance_name) ,
l_dim_month ,
p_assignment_action_id ,
NULL,
NULL ,
p_tax_unit_id,
p_business_group_id ,
NULL) MONTH_VAL
,PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM (UPPER(pBT.balance_name) ,
l_dim_qtd ,
p_assignment_action_id ,
NULL,
NULL ,
p_tax_unit_id,
p_business_group_id ,
NULL) QTD_VAL
,PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM (UPPER(pBT.balance_name) ,
l_dim_ytd ,
p_assignment_action_id ,--global.assignment_action_id
NULL,
NULL ,
p_tax_unit_id,--control.tax_unit_id
p_business_group_id ,--ctlglobals.bg_id
NULL) YTD_VAL
from pay_element_types_f pet
, pay_element_classifications pec
, pay_defined_balances pdb
, pay_bal_attribute_definitions pbad
, pay_balance_attributes pba
, pay_balance_types pbt
, pay_assignment_actions paa
, pay_payroll_actions ppa
WHERE pbad.attribute_name = l_attribute_name
AND pbad.business_group_id is null
AND pbad.legislation_code = 'US'
AND pba.attribute_id = pbad.attribute_id
AND pdb.defined_balance_id = pba.defined_balance_id
AND pdb.balance_type_id =pbt.balance_type_id
AND pec.classification_id = p_classification_id
AND pec.classification_id = pet.classification_id
and pec.legislation_code = 'US'
AND pet.element_information10 is not null
AND nvl(ppa.date_earned,ppa.effective_date) between pet.effective_start_date
and pet.effective_end_date
AND paa.assignment_action_id =p_assignment_action_id
AND paa.payroll_action_id =ppa.payroll_action_id
AND pet.element_name not like 'VERTEX%'
and pet.element_information10= pDB.balance_type_id
AND EXISTS (select prb.balance_value
from pay_run_balances prb,
pay_defined_balances pdb
where prb.defined_balance_id = pdb.defined_balance_id
and prb.assignment_id = paa.assignment_id
and pdb.balance_type_id = pet.element_information10
and rownum < 2)
order by 2;
select /*+ index (pet pay_element_types_f_fk1) */ distinct pet.rowid
,pet.element_name
,pet.element_type_id
,pet.classification_id
,pet.element_information10
,pet.element_information11
,pet.element_information12
,pet.element_information14
,decode (p_balance_level,
'ASG',(PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM (UPPER(pbT.balance_name) ,
'ASG_GRE_PTD' ,
p_assignment_action_id ,
NULL,
NULL,
p_tax_unit_id,
p_business_group_id ,
NULL)),
'PER',NULL) PTD_VAL
,PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM (UPPER(pBT.balance_name) ,
l_dim_month ,
p_assignment_action_id ,
NULL,
NULL ,
p_tax_unit_id,
p_business_group_id ,
NULL) MONTH_VAL
,PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM (UPPER(pBT.balance_name) ,
l_dim_qtd ,
p_assignment_action_id ,
NULL,
NULL ,
p_tax_unit_id,
p_business_group_id ,
NULL) QTD_VAL
,PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM (UPPER(pBT.balance_name) ,
l_dim_ytd ,
p_assignment_action_id ,--global.assignment_action_id
NULL,
NULL ,
p_tax_unit_id,--control.tax_unit_id
p_business_group_id ,--ctlglobals.bg_id
NULL) YTD_VAL
from pay_element_types_f pet
, pay_element_classifications pec
, pay_defined_balances pdb
, pay_bal_attribute_definitions pbad
, pay_balance_attributes pba
, pay_balance_types pbt
, pay_assignment_actions paa
, pay_payroll_actions ppa
WHERE pbad.attribute_name = l_attribute_name
AND pbad.business_group_id is null
AND pbad.legislation_code = 'US'
AND pba.attribute_id = pbad.attribute_id
AND pdb.defined_balance_id = pba.defined_balance_id
AND pdb.balance_type_id =pbt.balance_type_id
AND pec.classification_id = p_classification_id
AND pec.classification_id = pet.classification_id
and pec.legislation_code = 'US'
AND pet.element_information10 is not null
AND nvl(ppa.date_earned,ppa.effective_date) between pet.effective_start_date
and pet.effective_end_date
AND paa.assignment_action_id =p_assignment_action_id
AND paa.payroll_action_id =ppa.payroll_action_id
AND pet.element_name not like 'VERTEX%'
and pet.element_information10= pDB.balance_type_id
AND EXISTS (select prb.balance_value
from pay_run_balances prb,
pay_defined_balances pdb
where prb.defined_balance_id = pdb.defined_balance_id
and prb.assignment_id = paa.assignment_id
and pdb.balance_type_id in (pet.element_information10
,pet.element_information11
,pet.element_information12
,pet.element_information14)
and rownum < 2)
order by 2;
select distinct pet.rowid
,pet.element_name
,pet.element_type_id
,pet.classification_id
,pet.element_information10
,pet.element_information11
,pet.element_information12
,pet.element_information14
,decode (p_balance_level,
'ASG',(PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM (UPPER(pbT.balance_name) ,
'ASG_GRE_PTD' ,
p_assignment_action_id ,
NULL,
NULL,
p_tax_unit_id,
p_business_group_id ,
NULL)),
'PER', NULL) PTD_VAL
,PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM (UPPER(pBT.balance_name) ,
l_dim_month ,
p_assignment_action_id ,
NULL,
NULL ,
p_tax_unit_id,
p_business_group_id ,
NULL) MONTH_VAL
,PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM (UPPER(pBT.balance_name) ,
l_dim_qtd,
p_assignment_action_id ,
NULL,
NULL ,
p_tax_unit_id,
p_business_group_id ,
NULL) QTD_VAL
,PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM (UPPER(pBT.balance_name) ,
l_dim_ytd,
p_assignment_action_id ,--global.assignment_action_id
NULL,
NULL ,
p_tax_unit_id,--control.tax_unit_id
p_business_group_id ,--ctlglobals.bg_id
NULL) YTD_VAL
from pay_element_types_f pet
, pay_payroll_actions ppa
, pay_assignment_actions paa
, pay_balance_types pbt
WHERE pet.classification_id = p_classification_id
AND pet.element_information10 is not null
AND PAA.ASSIGNMENT_ACTION_ID =p_assignment_action_id
and paa.payroll_action_id =ppa.payroll_action_id
AND nvl(ppa.date_earned,ppa.effective_date) between pet.effective_start_date
and pet.effective_end_date
AND pet.element_name not like 'VERTEX%'
AND paa.assignment_id = p_assignment_id
AND pet.element_information10 =pbt.balance_type_id
AND EXISTS (SELECT 'x'
FROM pay_payroll_actions pact,
pay_assignment_actions asg,
pay_run_results rr
where rr.element_type_id + 0 = pet.element_type_id
and rr.assignment_action_id = asg.assignment_action_id
and asg.assignment_id = paa.assignment_id
and asg.tax_unit_id = paa.tax_unit_id
and asg.payroll_action_id = pact.payroll_action_id
and pact.effective_date between trunc(ppa.effective_date,'YEAR')
and ppa.effective_date
and rr.source_type in ( 'E' , 'I' )
)
order by 2;
AND EXISTS (select prr.element_type_id
from pay_run_results prr
where prr.assignment_action_id = paa.assignment_action_id
and prr.source_type in ( 'E' , 'I' )
and prr.element_type_id + 0 = pet.element_type_id
)
*/
/******************************************************************************
* Cursor to get element information in case balance uploads are completed and
* mode is assignment mode for ASG/PER level balances
******************************************************************************/
CURSOR c_element_asg_balances
IS
select distinct pet2.rowid
,pet2. element_name
,pet2.element_type_id
,pet2.classification_id
,pet2.element_information10
,pet2.element_information11
,pet2.element_information12
,pet2.element_information14
,decode (p_balance_level,
'ASG', (PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM (UPPER(pbT.balance_name) ,
'ASG_GRE_PTD' ,
NULL,
l_temp_assignment_id,
l_date,
p_tax_unit_id,
p_business_group_id ,
NULL)),
'PER', NULL) PTD_VAL
,PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM (UPPER(pBT.balance_name) ,
l_dim_month,
NULL ,
l_temp_assignment_id,
l_date ,
p_tax_unit_id,
p_business_group_id ,
NULL) MONTH_VAL
,PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM (UPPER(pBT.balance_name) ,
l_dim_qtd,
NULL ,
l_temp_assignment_id,
nvl(l_qtd_date,l_date ),
p_tax_unit_id,
p_business_group_id ,
NULL) QTD_VAL
,PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM (UPPER(pBT.balance_name) ,
l_dim_ytd,
NULL ,--global.assignment_action_id
l_temp_assignment_id,
nvl(l_ytd_date,l_date) ,
p_tax_unit_id,--control.tax_unit_id
p_business_group_id ,--ctlglobals.bg_id
NULL) YTD_VAL
from pay_element_classifications ec
,pay_element_types_f et
,pay_element_links_f el
,pay_element_entries_f ee
,pay_element_entry_values_f eev
,pay_balance_feeds_f pbf
,pay_element_types_f pet2
,pay_element_classifications pec
,pay_input_values_f piv
,pay_balance_types pbt
where ec.classification_name = 'Balance Initialization'
and ec.legislation_code is null
and ee.assignment_id = p_assignment_id
and ee.element_link_id = el.element_link_id
and el.element_type_id = et.element_type_id
and et.classification_id = ec.classification_id
and ee.element_entry_id = eev.element_entry_id
and eev.input_value_id = pbf.input_value_id
and piv.input_value_id = pbf.input_value_id
and et.element_type_id = piv.element_type_id
and nvl(p_action_date ,p_session_date) between pbf.effective_start_date
and pbf.effective_end_date
and pbf.balance_type_id = pet2.element_information10
and pbt.balance_type_id = pet2.element_information10
and pet2.element_information10 is not null
and pet2.classification_id = pec.classification_id
and pec.classification_name = p_classification_name
and pec.legislation_code = 'US'
and nvl(p_action_date, p_session_date ) between pet2.effective_start_date
and pet2.effective_end_date
and eev.screen_entry_value is not null
order by 2;
SELECT balance_name INTO value
FROM pay_balance_types
WHERE balance_type_id =l_balance_type_id;
SELECT defined_balance_id
INTO v_defbal_id
FROM pay_defined_balances
WHERE balance_type_id = p_bal_id
AND balance_dimension_id = p_dim_id
AND nvl(business_group_id,p_business_group_id) = p_business_group_id
AND nvl(legislation_code,p_legislation_code) = p_legislation_code;
select paf.assignment_id
into l_temp_assignment_id
from per_assignments_f paf,
hr_soft_coding_keyflex hsk
where paf.assignment_id = p_assignment_id
and paf.soft_coding_keyflex_id = hsk.soft_coding_keyflex_id
and p_session_date between paf.effective_start_date
and paf.effective_end_date
and hsk.segment1 = to_char(p_tax_unit_id);
select paf2.assignment_id
into l_temp_assignment_id
from per_assignments_f paf1,
per_assignments_f paf2,
hr_soft_coding_keyflex hsk
where paf1.assignment_id = p_assignment_id
and paf2.person_id = paf1.person_id
and paf2.soft_coding_keyflex_id = hsk.soft_coding_keyflex_id
and p_session_date between paf2.effective_start_date
and paf2.effective_end_date
and hsk.segment1 = to_char(p_tax_unit_id)
and rownum=1;
select paf2.assignment_id
into l_temp_assignment_id
from per_all_assignments_f paf1,
per_all_assignments_f paf2,
hr_soft_coding_keyflex hsk
where paf1.assignment_id = p_assignment_id
and paf2.person_id = paf1.person_id
and paf2.soft_coding_keyflex_id = hsk.soft_coding_keyflex_id
and hsk.segment1 = to_char(p_tax_unit_id)
and paf2.effective_end_date < p_session_date
and paf2.effective_end_date >= trunc(p_session_date,'YYYY')
and paf2.effective_end_date =
(select MAX(paf3.effective_end_date)
from per_all_assignments_f paf3
where paf3.person_id = paf1.person_id
and paf3.effective_end_date < p_session_date
)
and rownum=1;
/* select paf2.assignment_id
into l_temp_assignment_id
from per_assignments_f paf1,
per_assignments_f paf2,
hr_soft_coding_keyflex hsk
where paf1.assignment_id = p_assignment_id
and paf2.person_id = paf1.person_id
and paf2.soft_coding_keyflex_id = hsk.soft_coding_keyflex_id
and hsk.segment1 = to_char(p_tax_unit_id)
and paf2.effective_end_date < p_session_date
and paf2.effective_end_date >= trunc(p_session_date,'YYYY')
and paf2.effective_end_date =
(select MAX(paf3.effective_end_date)
from per_assignments_f paf3
where paf3.person_id = paf1.person_id
and paf3.effective_end_date < p_session_date
)
and rownum=1; */
SELECT pps.final_process_date
INTO l_last_process_date
FROM per_periods_of_service pps
WHERE date_start <= p_session_date
AND pps.period_of_service_id = (
SELECT DISTINCT(period_of_service_id)
FROM per_all_assignments_f
WHERE assignment_id = l_temp_assignment_id
AND assignment_type = 'E'
);
SELECT trunc(add_months(l_date,12),'Y')-1
INTO l_last_process_date
FROM dual;
select paf.assignment_id
into l_temp_assignment_id
from per_assignments_f paf,
hr_soft_coding_keyflex hsk
where paf.assignment_id = p_assignment_id
and paf.soft_coding_keyflex_id = hsk.soft_coding_keyflex_id
and p_session_date between paf.effective_start_date
and paf.effective_end_date
and hsk.segment1 = to_char(p_tax_unit_id);
select paf2.assignment_id
into l_temp_assignment_id
from per_assignments_f paf1,
per_assignments_f paf2,
hr_soft_coding_keyflex hsk
where paf1.assignment_id = p_assignment_id
and paf2.person_id = paf1.person_id
and paf2.soft_coding_keyflex_id = hsk.soft_coding_keyflex_id
and p_session_date between paf2.effective_start_date
and paf2.effective_end_date
and hsk.segment1 = to_char(p_tax_unit_id)
and rownum=1;
select paf2.assignment_id
into l_temp_assignment_id
from per_all_assignments_f paf1,
per_all_assignments_f paf2,
hr_soft_coding_keyflex hsk
where paf1.assignment_id = p_assignment_id
and paf2.person_id = paf1.person_id
and paf2.soft_coding_keyflex_id = hsk.soft_coding_keyflex_id
and hsk.segment1 = to_char(p_tax_unit_id)
and paf2.effective_end_date < p_session_date
and paf2.effective_end_date >= trunc(p_session_date,'YYYY')
and paf2.effective_end_date =
(select MAX(paf3.effective_end_date)
from per_all_assignments_f paf3
where paf3.person_id = paf1.person_id
and paf3.effective_end_date < p_session_date
)
and rownum=1;
/* select paf2.assignment_id
into l_temp_assignment_id
from per_assignments_f paf1,
per_assignments_f paf2,
hr_soft_coding_keyflex hsk
where paf1.assignment_id = p_assignment_id
and paf2.person_id = paf1.person_id
and paf2.soft_coding_keyflex_id = hsk.soft_coding_keyflex_id
and hsk.segment1 = to_char(p_tax_unit_id)
and paf2.effective_end_date < p_session_date
and paf2.effective_end_date >= trunc(p_session_date,'YYYY')
and paf2.effective_end_date =
(select MAX(paf3.effective_end_date)
from per_assignments_f paf3
where paf3.person_id = paf1.person_id
and paf3.effective_end_date < p_session_date
)
and rownum=1; */
SELECT pps.final_process_date
into l_last_process_date
FROM per_periods_of_service pps
WHERE date_start <= p_session_date
AND pps.period_of_service_id =
( SELECT DISTINCT(period_of_service_id)
FROM per_all_assignments_f
WHERE assignment_id = l_temp_assignment_id
AND assignment_type = 'E');
SELECT trunc(add_months(l_date,12),'Y')-1
INTO l_last_process_date
FROM dual;
hr_utility.set_location('Balances Invalid ASG/PER level balances', 60); -- delete
p_earn_data.delete();
p_dedn_data_temp.delete();
p_earn_data.delete;
p_earn_data.delete();
p_dedn_data_temp.delete();