[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
10-NOV-2003 kaverma 115.2 3138331 updated code to consider the termination
of assignments for multiple assignments
12-NOV-2003 kaverma 115.3 3250653 Corrected populate_local_balance to fetch
school dst correcly. Also modified
populate_actions_ids.
17-NOV-2003 kaverma 115.4 3257504 corrected cursor c_get_max_action_id
21-NOV-2003 kaverma 115.5 3270646 Added exists check at places where plsql
table is accessed.
03-DEC-2003 kaverma 115.6 3275404 Modified populate_actions_ids and
populate_earn_bal. Removed
get_phbr_plsql_table,get_earn_plsql_table
and get_dedn_plsql_table. Moved the logic
to corres. plsql populate procedures and
passing the plsql table as out parameter.
06-JAN-2004 tclewis 115.7 2845480 Modified populate_state_balance.
added code to reverse the sign for
state EIC balances. Added code to
return STEIC balance as the fed
procedure does.
04-FEB-2004 ardsouza 115.9 3412605 Replaced table PAY_US_CITY_SCHOOL_DSTS
by the view PAY_US_SCHOOL_DSTS to handle
county level school districts of Kentucky.
26-FEB-2004 sdahiya 115.10 3464757 Modified cursor c_get_max_action_id
to use nvl(date_earned, effective_date)
instead of effective_date. Created a
branched version (115.6.11510.3) of
this file too.
31-MAY-2004 kaverma 115.11 3620872 Modified populate_dedn_balances to use
dedution run balance/run result view
10-JUN-2004 kaverma 115.12 3620872 Added Rule hint to earnings and deduction
queries for quick customer performance fix.
22-JUN-2004 kaverma 115.13 3620872 Changed the logic to fetch balances for
earnings and deductions.
06-SEP-2005 rmonge 115.14 3837653 Added a order by clause to the following
cursors in the
c_get_pay_rb_elements,
c_get_pre_earn_run_rb,
c_get_pre_earn_ytd_rb,
c_get_more_earn_elements
The order by clause matches the
Q_Earnigns order by clause
in order to retrieve the elements by
Earnings by reporting_name,
classification and processing_priority
24-NOV-2005 kvsankar 115.15 4004796 Modified the cursors
c_get_more_earn_elements
and c_get_more_dedn_elements
to correct the Date
Effective Joins present in them.
02-DEC-2004 ahanda 115.16 4004796 Changed Earnings and Deductions query to
not use view if Balances are not valid
instrad check run results.
This will ensure that indirect element
will show up if balances are not valid.
21-FEB-2005 sackumar 115.17 3334690 Remove a condition in populate earn balance when
balance status is invalid (<>'Y') which restrict to repeat the code
so current values is not sumed up in case of
Map enabled Multi Assignment
13-JAN-2006 rmonge 4883110 Changed the order by clause for the Q_Earnings again
to fix problem with customer not able to see all
Earning Elements displayed or printed when the
number of earning Elements is more than 8.
The new order by will display any earning elements
first regardless of their priority.
23-MAR-2006 saurgupt 115.20 4966938 Changed the cursor c_get_pay_assignment_dtl. Add ppa.effective_date
in the select statement.
Write the new queries for cursors c_get_earn_elements and
c_get_dedn_elements. Removed the table pay_element_entries_f.
Now pay_assignment_actions and pay_payroll_actions are used.
Also, now date_paid is used in place of date_earned. This resolves
the boundary issue if date earned and date paid are in different
years.
16-MAY-2006 sodhingr 115.20 5228817 changed cursor c_get_dedn_elements and c_get_earn_elements
to refer to ppa.effective_Date
20-JUN-2006 sjawid 115.22 5210560 Added a condition to the c_get_assignments
to avoid the overstated values in prepay soe
when person with person type both employee and applicant.
20-JUN-2006 sjawid 115.22 4743188 Changed Order by clause for the c_get_dedn_elements
in order to sort with element name.
Modified the logic in populate_earn_bal and populate_dedn_balance
in order to load p_earn_tab and p_dedn_tab tables in sorting order
when balances are invalid.
31-JUL-2006 saurgupt 115.23 5332346 Modified the procedure populate_actions_ids. Add p_balance_status to
check balance status. Removed cursor c_get_all_aaid_for_mast as it
does not work for 11.0 data. Instead added two new cursors,
c_get_all_aaid_for_mast_rb and c_get_all_aaid_for_mast_rr.
Modified cursor c_get_dedn_elements and c_get_earn_elements. Removed
the condition and paa1.source_action_id is not null.
02-AUG-2006 saurgupt 115.24 5332346 Reverse the changes done in populate_actions_ids. Only modified the
cursor c_get_all_aaid_for_mast by removing pay_run_types and instead
added pay_payroll_actions.
06-FEB-2007 kvsankar 115.25 5865549 Initialized the value of l_asg_action_id
to NULL before using it.
03-MAR-2008 sneelapa 115.26 6636807 Modified Procedure populate_action_ids
CURSOR c_get_max_action_id
Added 'V' in WHERE Condition for
pay_payroll_actions.action_type column
*****************************************************************************/
l_package VARCHAR2(30) := 'pay_us_soe_balances_pkg.';
select assact.assignment_action_id,
assact.assignment_id
from pay_action_interlocks intlk
,pay_assignment_actions assact
,pay_payroll_actions ppa
where intlk.locking_action_id = cp_run_action_id
and intlk.locked_action_id = assact.assignment_action_id
and assact.payroll_action_id = ppa.payroll_action_id
and ((ppa.run_type_id is null and assact.source_action_id is null) or
(ppa.run_type_id is not null and assact.source_action_id is not null))
and not exists
(select null
from pay_payroll_actions rpact
, pay_assignment_actions rassact
, pay_action_interlocks rintlk
where assact.assignment_action_id = rintlk.locked_action_id
and rintlk.locking_action_id = rassact.assignment_action_id
and rpact.payroll_action_id = rassact.payroll_action_id
and rpact.action_type = 'V')
order by assact.assignment_action_id;
select assact.assignment_action_id,
assact.assignment_id,
prt.shortname,
prt.run_type_id
from pay_action_interlocks intlk
,pay_assignment_actions assact
,pay_run_types_f prt
where intlk.locking_action_id = cp_run_action_id
and intlk.locked_action_id = assact.assignment_action_id
and assact.source_action_id is not null
and assact.run_type_id(+) = prt.run_type_id
and not exists
(select null
from pay_payroll_actions rpact
, pay_assignment_actions rassact
, pay_action_interlocks rintlk
where assact.assignment_action_id = rintlk.locked_action_id
and rintlk.locking_action_id = rassact.assignment_action_id
and rpact.payroll_action_id = rassact.payroll_action_id
and rpact.action_type = 'V')
order by assact.assignment_action_id;
select distinct paa1.assignment_id
from per_assignments_f paa,
per_assignments_f paa1,
per_people_f ppa
where paa.assignment_id = c_assignment_id
and paa.person_id = ppa.person_id
and paa1.person_id = ppa.person_id
and paa1.ASSIGNMENT_TYPE <>'A' -- bug5210560
and paa1.effective_end_date >= to_date('01/01/'||to_char(c_period_end_date,'YYYY'),'DD/MM/YYYY')
order by paa1.assignment_id;
select assact.assignment_action_id -- Bug 3257504
from pay_assignment_actions assact,
pay_payroll_actions pac
where assact.assignment_id = c_assignment_id
and assact.payroll_action_id = pac.payroll_action_id
and pac.action_type in ('R','Q','B','I','V')
-- 'V' action_type is added by sneelapa for bug 6636807
and nvl(pac.date_earned, pac.effective_date) <= c_period_end_date -- Bug 3464757
and nvl(pac.date_earned, pac.effective_date) >= trunc(c_period_start_date,'Y') -- Bug 3275404, 3464757
and not exists
(select null
from pay_payroll_actions rpact
, pay_assignment_actions rassact
, pay_action_interlocks rintlk
where assact.assignment_action_id = rintlk.locked_action_id
and rintlk.locking_action_id = rassact.assignment_action_id
and rpact.payroll_action_id = rassact.payroll_action_id
and rpact.action_type = 'V')
order by assact.assignment_action_id desc;
master_actions_tab.delete;
run_actions_tab.delete;
SELECT defined_balance_id
INTO v_defbal_id
FROM pay_defined_balances pdb
WHERE pdb.balance_type_id = p_bal_id
AND pdb.balance_dimension_id = p_dim_id
AND nvl(pdb.legislation_code,'US') = 'US';
select ytd_val
,reporting_name_alt
,run_val
,hours_run_val
,element_type_id
from pay_us_earnings_amounts_rbr_v
where assignment_action_id = c_run_assact_id
order by decode(reporting_name_alt, 'Regular Pay', 0,
'Regular Salary',0,
'Regular Wages',0,
'Time Entry Wages',1),
decode(classification_name,
'Earnings',1,
'Alien/Expat Earnings',2,
'Supplemental Earnings', 3,
'Inputed Earnings',4,
'Tax Credit',5,
'Non-payroll Payments',6),
processing_priority;
select reporting_name_alt
,run_val
,hours_run_val
,element_type_id
from pay_us_earnings_amounts_rbr_v pt
where pt.assignment_action_id = cp_run_action_id
order by decode( reporting_name_alt, 'Regular Pay', 0,
'Regular Salary',0,
'Regular Wages',0,
'Time Entry Wages',1),
decode(classification_name,
'Earnings',1,
'Alien/Expat Earnings',2,
'Supplemental Earnings', 3,
'Inputed Earnings',4,
'Tax Credit',5,
'Non-payroll Payments',6),
processing_priority;
select ytd_val
,pt.reporting_name_alt
,element_type_id
from pay_us_earnings_amounts_rbr_v pt
where pt.assignment_action_id = cp_master_action_id
order by decode( reporting_name_alt, 'Regular Pay', 0,
'Regular Salary',0,
'Regular Wages',0,
'Time Entry Wages',1),
decode(classification_name,
'Earnings',1,
'Alien/Expat Earnings',2,
'Supplemental Earnings', 3,
'Inputed Earnings',4,
'Tax Credit',5,
'Non-payroll Payments',6),
processing_priority;
select balance_dimension_id
from pay_balance_dimensions
where legislation_code = 'US'
and database_item_suffix = cp_database_item_suffix;
select paa.assignment_id,
ppa.date_earned,
ppa.effective_date
from pay_assignment_actions paa,
pay_payroll_actions ppa
where paa.assignment_action_id = cp_assignment_action_id
and paa.payroll_action_id = ppa.payroll_action_id;
select distinct pet.element_type_id,
nvl(pet.reporting_name, pet.element_name),
pet.element_information10,
pet.element_information12,
pet.business_group_id,
pec.classification_name,
pet.processing_priority
from pay_assignment_actions paa ,
pay_assignment_actions paa1 ,
pay_payroll_actions ppa ,
pay_run_results prr ,
pay_element_types_f pet ,
pay_element_classifications pec
where paa.assignment_action_id = cp_assignment_action_id
and paa1.assignment_id = paa.assignment_id
-- and paa1.source_action_id is not null --for bug 5332346
and ppa.payroll_action_id = paa1.payroll_action_id
and ppa.effective_date between trunc(cp_date_paid,'Y') and cp_date_paid
and prr.assignment_action_id = paa1.assignment_action_id
and prr.source_type in ( 'E', 'I' )
and pet.element_type_id >= 0
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 prr.element_type_id + 0 = pet.element_type_id
and pec.classification_name in ('Earnings',
'Alien/Expat Earnings',
'Non-payroll Payments',
'Imputed Earnings',
'Supplemental Earnings')
and pet.classification_id = pec.classification_id
order by decode(nvl(pet.reporting_name, pet.element_name),
'Regular Pay', 0,
'Regular Salary',0,
'Regular Wages',0,
'Time Entry Wages',1),
decode(pec.classification_name,
'Earnings',1,
'Alien/Expat Earnings',2,
'Supplemental Earnings', 3,
'Inputed Earnings',4,
'Tax Credit',5,
'Non-payroll Payments',6),
pet.processing_priority;
select /*+ ORDERED distinct
pet.element_type_id,
nvl(pet.reporting_name, pet.element_name),
pet.element_information10,
pet.element_information12,
pet.business_group_id,
pec.classification_name,
pet.processing_priority
from pay_element_entries_f pee,
pay_run_results prr,
pay_element_types_f pet,
pay_element_classifications pec
where pee.assignment_id = cp_assignment_id
--and pee.effective_end_date >= trunc(cp_date_earned, 'Y')
and pee.effective_start_date <= cp_date_earned
and prr.source_id = pee.element_entry_id
and prr.source_type in ( 'E', 'I' )
and pec.classification_name in ('Earnings',
'Alien/Expat Earnings',
'Non-payroll Payments',
'Imputed Earnings',
'Supplemental Earnings')
and pet.classification_id = pec.classification_id
and pet.element_information10 is not null
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 <= cp_date_earned)
and prr.element_type_id + 0 = pet.element_type_id
order by decode(nvl(pet.reporting_name, pet.element_name),
'Regular Pay', 0,
'Regular Salary',0,
'Regular Wages',0,
'Time Entry Wages',1),
decode(pec.classification_name,
'Earnings',1,
'Alien/Expat Earnings',2,
'Supplemental Earnings', 3,
'Inputed Earnings',4,
'Tax Credit',5,
'Non-payroll Payments',6),
pet.processing_priority;
p_earn_tab.delete;
earnings_elements_tab.delete;
earnings_elements_tab.delete;
p_earn_tab.delete;
SELECT user_reporting_name,
run_val,
ytd_val,
tax_type_code
FROM pay_us_fed_taxes_v
WHERE ee_or_er_code = 'EE'
AND balance_category_code in ('WITHHELD','ADVANCED')
AND assignment_action_id = l_assignment_action_id
ORDER BY user_reporting_name;
select pt.user_reporting_name
,sum(pt.run_val)
,pt.tax_type_code
from pay_us_fed_taxes_v pt
where pt.ee_or_er_code = 'EE'
and pt.balance_category_code in ('WITHHELD','ADVANCED')
and pt.assignment_action_id = cp_run_action_id
group by pt.user_reporting_name,tax_type_code
order by user_reporting_name;
select sum(pt.ytd_val) ,
pt.user_reporting_name,
tax_type_code
from pay_us_fed_taxes_v pt
where pt.ee_or_er_code = 'EE'
and pt.balance_category_code in ('WITHHELD','ADVANCED')
and pt.assignment_action_id = cp_master_action_id
group by pt.user_reporting_name,tax_type_code ;
SELECT user_reporting_name,
run_val,
ytd_val,
tax_type_code
FROM pay_us_fed_taxes_rbr_v
WHERE ee_or_er_code = 'EE'
AND balance_category_code in ('WITHHELD','ADVANCED')
AND assignment_action_id = l_assignment_action_id
order by user_reporting_name;
select pt.user_reporting_name
, sum(pt.run_val)
,pt.tax_type_code
from pay_us_fed_taxes_rbr_v pt
where pt.ee_or_er_code = 'EE'
and pt.balance_category_code in ('WITHHELD','ADVANCED')
and pt.assignment_action_id = cp_run_action_id
group by pt.user_reporting_name,tax_type_code
order by user_reporting_name;
select sum(pt.ytd_val) run_val,
pt.user_reporting_name,
tax_type_code
from pay_us_fed_taxes_rbr_v pt
where pt.ee_or_er_code = 'EE'
and pt.balance_category_code in ('WITHHELD','ADVANCED')
and pt.assignment_action_id = cp_master_action_id
group by pt.user_reporting_name,tax_type_code
order by user_reporting_name;
fed_tab.delete;
select state_abbrev,
user_reporting_name,
run_val,
tax_type_code,
jurisdiction_code,
ytd_val
from pay_us_state_taxes_v
where ee_or_er_code = 'EE'
and assignment_action_id = l_assignment_action_id
order by user_reporting_name;
select state_abbrev,
user_reporting_name,
sum(run_val),
tax_type_code
from pay_us_state_taxes_v pt
where pt.ee_or_er_code = 'EE'
and pt.assignment_action_id = cp_run_action_id
group by user_reporting_name, state_abbrev,tax_type_code
order by user_reporting_name;
select sum(pt.ytd_val),
user_reporting_name,
tax_type_code,
state_abbrev
from pay_us_state_taxes_v pt
where pt.ee_or_er_code = 'EE'
and pt.assignment_action_id = cp_master_action_id
group by user_reporting_name, state_abbrev,tax_type_code
order by user_reporting_name;
select state_abbrev,
user_reporting_name,
run_val,
tax_type_code,
jurisdiction_code,
ytd_val
from pay_us_state_taxes_rbr_v
where ee_or_er_code = 'EE'
and assignment_action_id = l_assignment_action_id
order by user_reporting_name;
select state_abbrev,
user_reporting_name,
sum(run_val),
tax_type_code
from pay_us_state_taxes_rbr_v pt
where pt.ee_or_er_code = 'EE'
and pt.assignment_action_id = cp_run_action_id
group by user_reporting_name, state_abbrev,tax_type_code
order by user_reporting_name;
select sum(pt.ytd_val)
,user_reporting_name
,tax_type_code
,state_abbrev
from pay_us_state_taxes_rbr_v pt
where pt.ee_or_er_code = 'EE'
and pt.assignment_action_id = cp_master_action_id
group by user_reporting_name, state_abbrev,tax_type_code
order by user_reporting_name;
state_tab.delete;
select city_name ,
jurisdiction_code,
tax_type_code,
user_reporting_name,
run_val,
ytd_val
from pay_us_local_taxes_v
where ee_or_er_code = 'EE'
and assignment_action_id = l_assignment_action_id
order by user_reporting_name;
select city_name,
jurisdiction_code,
tax_type_code,
user_reporting_name,
sum(run_val)
from pay_us_local_taxes_v pt
where pt.ee_or_er_code = 'EE'
and pt.assignment_action_id = cp_run_action_id
group by user_reporting_name, city_name,jurisdiction_code, tax_type_code
order by user_reporting_name;
select city_name,
sum(pt.ytd_val) ,
jurisdiction_code,
tax_type_code,
user_reporting_name
from pay_us_local_taxes_v pt
where pt.ee_or_er_code = 'EE'
and pt.assignment_action_id = cp_master_action_id
group by user_reporting_name, city_name,jurisdiction_code, tax_type_code
order by user_reporting_name;
select city_name ,
jurisdiction_code,
tax_type_code,
user_reporting_name,
run_val,
ytd_val
FROM pay_us_local_taxes_rbr_v
WHERE ee_or_er_code = 'EE'
AND assignment_action_id = l_assignment_action_id
order by user_reporting_name;
select city_name,
jurisdiction_code,
tax_type_code,
user_reporting_name,
sum(run_val)
from pay_us_local_taxes_rbr_v pt
where pt.ee_or_er_code = 'EE'
and pt.assignment_action_id = cp_run_action_id
group by user_reporting_name, city_name,jurisdiction_code, tax_type_code
order by user_reporting_name;
select city_name ,
sum(pt.ytd_val),
jurisdiction_code,
tax_type_code,
user_reporting_name
from pay_us_local_taxes_rbr_v pt
where pt.ee_or_er_code = 'EE'
and pt.assignment_action_id = cp_master_action_id
group by user_reporting_name, city_name,jurisdiction_code, tax_type_code
order by user_reporting_name;
local_tab.delete;
select county_name into l_county_name
from pay_us_counties
where county_code = substr(local_tab(i).juris_code,4,3)
and state_code = substr(local_tab(i).juris_code,1,2);
select distinct school_dst_name into l_school_name
from pay_us_school_dsts --Bug 3412605
where school_dst_code = substr(local_tab(i).juris_code,4,5)
and state_code = substr(local_tab(i).juris_code,1,2);
select ytd_val,
reporting_name_alt,
run_val,
element_type_id
from pay_us_deductions_rbr_v
where assignment_action_id = c_run_assact_id
order by reporting_name_alt;
select reporting_name_alt,
run_val,
element_type_id
from pay_us_deductions_rbr_v pt
where pt.assignment_action_id = cp_run_action_id
order by reporting_name_alt;
select ytd_val,
reporting_name_alt,
element_type_id
from pay_us_deductions_rbr_v pt
where pt.assignment_action_id = cp_master_action_id;
select distinct
pet.element_type_id,
nvl(pet.reporting_name, pet.element_name),
pet.element_information10,
pet.business_group_id,
pet.processing_priority
from pay_assignment_actions paa ,
pay_assignment_actions paa1 ,
pay_payroll_actions ppa ,
pay_run_results prr ,
pay_element_types_f pet ,
pay_element_classifications pec
where paa.assignment_action_id = cp_assignment_action_id
and paa1.assignment_id = paa.assignment_id
-- and paa1.source_action_id is not null --for bug 5332346
and ppa.payroll_action_id = paa1.payroll_action_id
and ppa.effective_date between trunc(cp_date_paid,'Y') and cp_date_paid
and prr.assignment_action_id = paa1.assignment_action_id
and prr.source_type in ( 'E', 'I' )
and pet.element_type_id >= 0
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 prr.element_type_id + 0 = pet.element_type_id
and pec.classification_name IN ('Pre-Tax Deductions',
'Voluntary Deductions',
'Involuntary Deductions')
and pet.classification_id = pec.classification_id
order by pet.processing_priority,nvl(pet.reporting_name, pet.element_name); --bug4743188
select /*+ ORDERED distinct
pet.element_type_id,
nvl(pet.reporting_name, pet.element_name),
pet.element_information10,
pet.business_group_id,
pet.processing_priority
from pay_element_entries_f pee,
pay_run_results prr,
pay_element_types_f pet,
pay_element_classifications pec
where pee.assignment_id = cp_assignment_id
and pee.effective_end_date >= trunc(cp_date_earned, 'Y')
and pee.effective_start_date <= cp_date_earned
and prr.source_id = pee.element_entry_id
and prr.source_type in ( 'E', 'I' )
and pec.classification_name IN ('Pre-Tax Deductions',
'Voluntary Deductions',
'Involuntary Deductions')
and pet.classification_id = pec.classification_id
and pet.element_information10 is not null
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 <= cp_date_earned)
and prr.element_type_id + 0 = pet.element_type_id
order by pet.processing_priority;
deduction_elements_tab.delete;
p_dedn_tab.delete;
deduction_elements_tab.delete;
p_dedn_tab.delete;