[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
the for update of . . . added a for update
on the lock the created assignment_action_id.
06-DEC-2002 tclewis 115.11 Added NOCOPY directive and fixed some typo's
formatting issue with the 'YTD SUI EE Taxable'
and 'YTD SUI ER Taxable' messages
25-JUN-2003 vinaraya 2963239 115.13 Added extra check in prc_process_data for medicare
and SS balance check.(bug number 2963239)
Moved the call to prc_process_data from report to
action_creation code. prc_write_data definition has
been changed to include two new arguements.
30-JUN-2003 vinaraya 3005756 115.14 Modified code for caching and removal of unwanted
code as per review comments.
01-JUL-2003 vinaraya 3005756 115.15 Changed function fnc_get_tax_limit_rate to include
join for start date in the cursor c_sui_sdi_info.
03-JUL-2003 vinaraya 3005756 115.16 Included 4 new cursors for state,county,city and
school jurisdiction data fetch.Included check for
validity of run balances to make use of the new
cursors accordingly.
Moved state,county,city and school
balance checks to inline procedures.
08-JUL-2003 vinaraya 3005756 115.17 Restructured entire code to remove repeated code.
Removed action interlocking
27-AUG-2003 kaverma 3115988 115.18 Added difference calculation for FUTA
19-DEC-2003 saurgupt 3291736 115.19 In action_creation, procedure insert_action
is removed. Also, if no Unacceptable tax balances
are found then a dummy action is created. This will
happen only if payroll/prepayments have been run.
26-DEC-2003 saurgupt 3316599 115.20 Tax Unit id is added to where condition to decrease
the cost of query.
06-JAN-2004 sdahiya 3316599 115.21 Modified queries for performance enhancement.
24-MAR-2004 fusman 3418991 115.22 Modified cursors c_actions,c_get_latest_asg,
c_school_jurisdictions_valid and
c_school_jurisdictions.
17-NOV-2004 ahanda 3962872 115.23 Changed range code, action creation and
enabled RANGE_PERSON_ID.
18-NOV-2004 ahanda 115.24 Fixed GSCC issues.
18-NOV-2004 ahanda 115.25 Fixed GSCC issues.
08-NOV-2007 dduvvuri 6360505 115.26 Performance Improvements for Bug 6360505
05-May-2008 Pannapur 6719359 115.27 Reverted the peformance fix
01-Jul-2008 Pannapur 7174993 115.28 Perfomance Improvements for bug 7174993
21-Jul-2008 Pannapur 7174993 115.29 Perfomance Improvements for bug 7174993(removed the hint added
in previous version)
10-Jul-2009 emunisek 8665548 115.30 Modified cursor c_sui_sdi_info in function
fnc_get_tax_limit_rate to pick a state tax
record which is effective on "As of Date"
07-Jan-2010 pbalu 8754952 115.31 Added new error condition for Negative Reduced Subject whable
06-May-2010 nkjaladi 8606883 115.32 Added new debug statements and Modified Cursor
c_state_jurisdictions in pkg procedure
prc_process_data
29-Jul-2010 emunisek 9872952 115.33 Modified the report that Employees
having Federal Exempt from Wage Accumulation
will not be verified in Unacceptable Tax Balance
Report.Manual verification is required for
them and all those employees will be shown
at the end of report.
11-Aug-2010 emunisek 9872952 115.34 Modified procedure action_creation to consider the
Wage Accumulation setting before skipping the
Employees.
07-OCT-2010 tclewis 9721787 115.35 Modified the calls to check_balance_status to
pass L_business_id instead of L_gre_id for the
second parameter. 2) modfied the cursors
c_state_jurisdictions, c_county_jurisdictions,
c_city_jurisdictions, c_school_jurisdictions.
Use the max of max(assignment effective_end_date)
or adjustment date when fetching the jurisdictions.
26-Jan-2011 rosuri 10350917 115.36 Modified the procedure prc_state_balances
so that L_first_half_rate gets the value from
SUI ER Experience Rate 2 and L_second_half_rate
gets the value from SUI ER Experience Rate 1
07-Feb-2011 rosuri 10350917 115.37 Modified the procedure prc_state_balances.
Added a new variable l_assignment_id which is used as
parameter in function call pay_us_tax_bals_pkg.us_tax_balance.
07-Mar-2011 rosuri 10631126 115.38 SS_ER_RATE and SS_EE_RATE are not equal from 01-JAN-2011
Commented the messege "YTD SS EE Withheld does not =
YTD SS ER Liability"
14-Sep-2011 rosuri 12742758 115.39 Modified fnc_get_futa_credit_rate function
to return -1 if there is no override FUTA Credit
rate in "State Tax Rules" Flexfiled.
Modified prc_federal_balances procedure
to take the Net FUTA Rate directly
from pay_us_federal_tax_info_f table
in case override credit limit is not set.
otherwise it will take Gross Rate from
FED_INFORMATION11 to calculate net futa rate.
21-Sep-2011 emunisek 12742758 115.40 Created function get_calculated_fed_limit_tax to calculate
the FUTA,SS and MEDICARE calculated values by considering
in-between year changes. This function is used as of now
for FUTA in procedure prc_federal_balances.
22-Nov-2011 emunisek 13394260 115.42 Made changes to consider Default SDI EE Rate for California
Employees having "Use Default SDI EE Rate" selected as Yes
in State Tax Rules.
29-Nov-2011 nvelaga 13360446 115.43 Reverted the change made for bug#10631126 (version 115.38)
Added IF clause to check for SS ER and SS EE Rates.
05-Dec-2011 ybudamal 13434213 115.44 Modified the function get_calculated_fed_limit_tax to
calculate the balance for all the assignments
for the person instead of current one assignment.
Modified the procedure prc_get_balance to change the
p_asg_type parameter passed to the procedure us_tax_balance
in the package pay_us_tax_bals_pkg from 'PER' to 'ASG' to
calculate the Assignment Level balance.
02-SEP-2012 nvelaga 9796821 115.45 Modified the calculation of Medicare EE Tax based on new
limit and rate effective 2013.
22-NOV-2012 nvelaga 15852506 115.57 Modified the calculation of new Medicate EE Tax Limit.
Subtracted 0.01 from the new Medicare Limit insteadof 1.
******************************************************************************/
--c_fixed_futa_rt CONSTANT NUMBER(10,4) := 6.2;
G_dummy_action_inserted_flag VARCHAR2(1) := 'N';
SELECT NVL(org_information7,0)/100
FROM hr_organization_information
WHERE organization_id = IN_tax_unit_id
AND org_information_context = 'Federal Tax Rules';
SELECT effective_date, business_group_id, legislative_parameters
FROM pay_payroll_actions
WHERE payroll_action_id = IN_pact_id;
SELECT futa_rate
FROM pay_us_federal_tax_info_f
WHERE p_as_of_date
BETWEEN effective_start_date
AND effective_end_date
AND fed_information_category='401K LIMITS'
ORDER BY effective_start_date;
SELECT count(distinct futa_rate)
FROM pay_us_federal_tax_info_f
WHERE effective_start_date <= p_as_of_date
AND effective_end_date >= TRUNC(p_as_of_date,decode(p_dimension,'QTD','Q','YTD','Y'))
AND fed_information_category='401K LIMITS';
select effective_date,legislative_parameters
into ld_effective_date,lv_leg_param
from pay_payroll_actions
where payroll_action_id = IN_pactid;
select fnd_date.date_to_canonical(ld_cur_date)
into lv_cur_date
from dual;
'SELECT /*+ ORDERED
INDEX (ppa PAY_PAYROLL_ACTIONS_PK)
INDEX (pa1 PAY_PAYROLL_ACTIONS_N5)
INDEX (act PAY_ASSIGNMENT_ACTIONS_N50)
INDEX (paf PER_ASSIGNMENTS_F_PK) */
DISTINCT paf.person_id
FROM pay_payroll_actions ppa,
pay_payroll_actions pa1,
pay_assignment_actions act,
per_assignments_f paf
WHERE ppa.payroll_action_id = :payroll_action_id
AND pa1.effective_date >= fnd_date.canonical_to_date('''|| lv_cur_date ||''')
AND pa1.effective_date <= ppa.effective_date
AND pa1.payroll_action_id = act.payroll_action_id
AND paf.assignment_id = act.assignment_id
AND pa1.effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND pa1.action_type in (''B'',''I'',''R'',''Q'',''V'')
AND act.action_status = ''C''
AND paf.business_group_id +0 = ppa.business_group_id
AND act.tax_unit_id = ' || lv_tax_unit_id;
This will insert a record into pay_us_rpt_totals with state_code as E.
The PAYUSUNB.rdf fetches these employees and shows them in last of the
report as an exemption.
***************************************************************************/
PROCEDURE prc_write_data (IN_commit_count IN NUMBER,
IN_record_type IN VARCHAR2,
IN_asgn_action_id IN NUMBER,
IN_gre_id IN NUMBER,
IN_org_id IN NUMBER,
IN_location_id IN NUMBER,
IN_pact_id IN NUMBER,
IN_chunk_number IN NUMBER,
IN_person_id IN NUMBER,
IN_assignment_no IN VARCHAR2,
IN_balance_nm1 IN VARCHAR2,
IN_balance_nm2 IN VARCHAR2,
IN_taxable IN NUMBER,
IN_withheld IN NUMBER,
IN_calculated IN NUMBER,
IN_difference IN NUMBER,
IN_jurisdiction IN VARCHAR2,
IN_message IN VARCHAR2,
IN_sort_code IN VARCHAR2,
IN_locked_asg_action_id IN NUMBER,
IN_assign_id IN NUMBER,
IN_attribute IN VARCHAR2 ) IS
L_jurisdiction VARCHAR2(30);
hr_utility.trace('Inserting Data into pay_us_rpt_totals1');
INSERT INTO pay_us_rpt_totals
(state_code,
tax_unit_id,
organization_id,
location_id,
session_id,
business_group_id,
value1,
gre_name,
value6,
attribute45
)
VALUES
('E',
IN_gre_id,
IN_org_id,
IN_location_id,
IN_pact_id,
IN_chunk_number,
IN_person_id,
IN_assignment_no,
IN_asgn_action_id,
IN_attribute
);
-- insert the action record.
hr_nonrun_asact.insact(IN_asgn_action_id,IN_assign_id,IN_pact_id,IN_chunk_number,IN_gre_id);
G_dummy_action_inserted_flag := 'Y';
SELECT DISTINCT
paf.person_id person_id
FROM per_all_assignments_f paf,
pay_all_payrolls_f PPY
WHERE exists
(select /*+ INDEX(paa PAY_ASSIGNMENT_ACTIONS_N51)
INDEX(ppa PAY_PAYROLL_ACTIONS_PK) */
'x'
from pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.effective_date between cp_period_start
and cp_period_end
and ppa.action_type in ('R','Q','V','B','I')
and ppa.action_status = 'C'
and ppa.business_group_id + 0 = cp_business_group_id
and ppa.payroll_action_id = paa.payroll_action_id
and paa.tax_unit_id = cp_tax_unit_id
and paa.action_status = 'C'
and paa.assignment_id = paf.assignment_id
and ppa.business_group_id = paf.business_group_id +0
and ppa.effective_date between paf.effective_start_date
and paf.effective_end_date)
AND paf.person_id between cp_start_person_id and cp_end_person_id
AND paf.assignment_type = 'E'
AND (cp_organization_id is null OR
paf.organization_id = cp_organization_id)
AND (cp_location_id is null OR
paf.LOCATION_ID = cp_location_id)
AND PPY.payroll_id = paf.payroll_id;
SELECT ppr.person_id person_id
FROM pay_population_ranges ppr
where ppr.payroll_action_id = cp_payroll_Action_id
and ppr.chunk_number = cp_chunk_number;
SELECT /*+ ORDERED */
to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) assignment_action_id
FROM per_all_assignments_f paf,
pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_action_classifications pac
WHERE paf.person_id = cp_person_id
AND paf.payroll_id = ppa.payroll_id
AND (paf.organization_id = IN_org_id
OR IN_org_id IS NULL)
AND (paf.location_id = IN_location_id
OR IN_location_id IS NULL)
AND paa.assignment_id = paf.assignment_id
AND paa.tax_unit_id = cp_tax_unit_id
AND paa.payroll_action_id = ppa.payroll_action_id
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 ppa.effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND ppa.effective_date BETWEEN cp_start_date AND cp_as_of_date
AND ppa.action_type = pac.action_type
AND pac.classification_name = 'SEQUENCED';
SELECT paa.assignment_id ,
paf.location_id,
paf.organization_id,
paf.assignment_number
FROM pay_assignment_actions paa,
pay_payroll_actions ppa,
per_all_assignments_f paf
WHERE paa.assignment_action_id = cp_asg_act_id
AND paa.tax_unit_id = cp_tax_unit_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.effective_date BETWEEN cp_start_date AND cp_as_of_date
AND paf.assignment_id = paa.assignment_id
AND ppa.effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND paf.person_id = cp_person_id;
SELECT parameter_value
FROM pay_action_parameters
WHERE parameter_name = 'WAGE_ACCUMULATION_ENABLED';
SELECT pay_assignment_actions_s.NEXTVAL
INTO L_lockingactid
FROM dual;
the employees who have the FIT Exempt from Wage Accumulation selected will be shown
in exception section in the end of report.*/
L_direct_fed_bal := fnd_profile.value('PAY_DIRECT_US_FEDERAL_BALANCES');
/*Query to see if the Person has any assignments with FIT Exempt from Wage Accumulation selected */
SELECT count(0) INTO L_asg_count
FROM per_all_assignments_f paf,
pay_us_emp_fed_tax_rules_f peft
WHERE paf.person_id = L_person_id
AND paf.effective_end_date >= L_start_date
AND paf.effective_start_date <= L_as_of_date
AND peft.assignment_id = paf.assignment_id
AND peft.business_group_id = paf.business_group_id
AND peft.wage_exempt = 'Y'
AND peft.effective_end_date >= L_start_date
AND peft.effective_start_date <= L_as_of_date;
G_dummy_action_inserted_flag = 'N' THEN
hr_nonrun_asact.insact(L_lockingactid,L_assignid,
IN_pactid,IN_chunk,L_gre_id);
IO_sqlstr := 'SELECT paa1.rowid
/* we need the row id of the assignment actions that are
created by PYUGEN */
FROM hr_organization_units hou,
hr_organization_units hou1,
hr_locations loc,
per_people_f ppf,
per_all_assignments_f paf,
pay_assignment_actions paa1, /* PYUGEN assignment action */
pay_payroll_actions ppa1 /* PYUGEN payroll action id */
WHERE ppa1.payroll_action_id = :pactid
AND paa1.payroll_action_id = ppa1.payroll_action_id
AND paa1.assignment_id = paf.assignment_id
AND paf.effective_start_date =
(SELECT MAX(paf1.effective_start_date)
FROM per_assignments_f paf1
WHERE paf1.assignment_id = paf.assignment_id
AND paf1.effective_start_date <= ppa1.effective_date
AND paf1.effective_end_date >=
DECODE(payusunb_pkg.fnc_get_parameter(''B_Dim'',
ppa1.legislative_parameters),
''QTD'',
TRUNC(ppa1.effective_date,''Q''),
''YTD'',
TRUNC(ppa1.effective_date,''Y''))
)
AND hou1.organization_id = paa1.tax_unit_id
AND hou.organization_id = paf.organization_id
AND loc.location_id = paf.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
hou1.name, /* GRE */
DECODE(payusunb_pkg.fnc_get_parameter(
''SO1'',ppa1.legislative_parameters),
''Employee'',ppf.full_name,
''Social'',ppf.national_identifier,
''Organization'',hou.name,
''Location'',loc.location_code,null),
DECODE(payusunb_pkg.fnc_get_parameter(
''SO2'',ppa1.legislative_parameters),
''Employee'',ppf.full_name,
''Social'',ppf.national_identifier,
''Organization'',hou.name,
''Location'',loc.location_code,null),
DECODE(payusunb_pkg.fnc_get_parameter(
''SO3'',ppa1.legislative_parameters),
''Employee'',ppf.full_name,
''Social'',ppf.national_identifier,
''Organization'',hou.name,
''Location'',loc.location_code,null),
hou.name,
ppf.full_name
FOR UPDATE of paa1.assignment_action_id';
SELECT effective_start_date,
effective_end_date,
futa_rate,
ss_ee_rate,
ss_er_rate,
medi_ee_rate,
medi_er_rate
FROM pay_us_federal_tax_info_f
WHERE effective_start_date <= p_as_of_date
AND effective_end_date >= TRUNC(p_as_of_date,decode(p_dimension,'QTD','Q','YTD','Y'))
AND fed_information_category='401K LIMITS'
ORDER BY effective_start_date;
SELECT assignment_id
FROM pay_assignment_actions
WHERE assignment_action_id = p_assignment_action_id;
SELECT distinct assignment_id
FROM per_all_assignments_f
WHERE person_id = (SELECT person_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND ROWNUM < 2)
AND TRUNC(p1_as_of_date,decode(p1_dimension,'QTD','Q','YTD','Y'))<=effective_end_date;
SELECT state_code,sta_information5,sta_information6,sta_information3,sta_information4
FROM pay_us_state_tax_info_f pusif
WHERE IN_as_of_date between pusif.effective_start_date AND pusif.effective_end_date
--IN_as_of_date <= pusif.effective_end_date --Modified for Bug#8665548
--AND IN_start_date >= pusif.effective_start_date --Removed for Bug#8665548
AND pusif.sta_information_category = 'State tax limit rate info'
ORDER BY 1;
SELECT org_information1,org_information15
FROM hr_organization_information
WHERE organization_id = IN_organization_id
AND org_information_context = 'State Tax Rules';
SELECT state_abbrev
FROM pay_us_states
WHERE state_code = IN_sui_state_code;
SELECT pus.state_code,org_information6/100, org_information7/100 , org_information14/100
FROM hr_organization_information org, pay_us_states pus
WHERE org.org_information1 = pus.state_abbrev
AND pus.state_code between 00 and 99
AND org.organization_id = IN_tax_unit_id
AND org.org_information_context = 'State Tax Rules';
INSERT INTO pay_us_rpt_totals
(state_code,
tax_unit_id,
organization_id,
location_id,
session_id,
business_group_id,
value1,
gre_name,
value6
)
VALUES
('H',
IN_gre_id,
IN_org_id,
IN_location_id,
IN_pact_id,
IN_chunk_number,
IN_person_id,
IN_assignment_no,
IN_asgn_action_id
);
-- insert the action record.
hr_nonrun_asact.insact(IN_asgn_action_id,IN_assign_id,IN_pact_id,IN_chunk_number,IN_gre_id);
G_dummy_action_inserted_flag := 'Y';
INSERT INTO pay_us_rpt_totals
(state_code,
tax_unit_id,
session_id,
business_group_id,
organization_name,
location_name, -- NULL
value2,
value3,
value4,
value5,
value6,
state_name,
attribute1,
attribute2
)
VALUES
(IN_record_type,
IN_gre_id,
IN_pact_id,
IN_chunk_number,
IN_balance_nm1,
IN_balance_nm2, -- NULL
IN_taxable,
IN_withheld,
IN_calculated, -- NULL
IN_difference, -- NULL
IN_asgn_action_id,
L_jurisdiction,
IN_message,
IN_sort_code
);
SELECT city_tax,school_tax
FROM pay_us_city_tax_info_f
WHERE jurisdiction_code = IN_jurisdiction
AND IN_as_of_date BETWEEN effective_start_date AND effective_end_date;
SELECT county_tax,school_tax
FROM pay_us_county_tax_info_f
WHERE jurisdiction_code = IN_jurisdiction
AND IN_as_of_date BETWEEN effective_start_date AND effective_end_date;
SELECT DISTINCT
prb.jurisdiction_code||'-000-0000' jurisdiction_code,
pus.state_code state_code,
pus.state_abbrev
FROM pay_run_balances prb,
per_assignments_f paf,
pay_us_states pus
WHERE paf.person_id = IN_person_id
AND prb.effective_date BETWEEN IN_start_date and IN_as_of_date
AND prb.effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND prb.assignment_id = paf.assignment_id
AND prb.jurisdiction_code = pus.state_code
AND (pus.state_code = IN_state_code
OR IN_state_code IS NULL);
SELECT DISTINCT
prb.jurisdiction_code||'-0000' jurisdiction_code,
puc.county_name||','||pus.state_abbrev jurisdiction_name
FROM pay_run_balances prb,
per_assignments_f paf,
pay_us_states pus,
pay_us_counties puc
WHERE paf.person_id = IN_person_id
AND paf.effective_start_date <= IN_as_of_date
AND paf.effective_end_date >= IN_start_date
AND prb.assignment_id = paf.assignment_id
AND prb.effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND prb.effective_date BETWEEN IN_start_date AND IN_as_of_date
AND pus.state_code = prb.jurisdiction_comp1
AND (pus.state_code = IN_state_code
OR IN_state_code IS NULL)
AND prb.jurisdiction_code = puc.state_code||'-'||puc.county_code
AND pus.state_code = puc.state_code;
SELECT DISTINCT
prb.jurisdiction_code,
pun.city_name||','||pus.state_abbrev jurisdiction_name
FROM pay_run_balances prb,
per_assignments_f paf,
pay_us_states pus,
pay_us_city_names pun
WHERE paf.person_id = IN_person_id
AND paf.effective_start_date <= IN_as_of_date
AND paf.effective_end_date >= IN_start_date
AND paf.assignment_id = prb.assignment_id
AND prb.effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND prb.effective_date BETWEEN IN_start_date AND IN_as_of_date
AND prb.jurisdiction_code =
pun.state_code||'-'||pun.county_code||'-'||pun.city_code
AND pun.primary_flag = 'Y'
AND prb.jurisdiction_comp2 = pun.county_code
AND prb.jurisdiction_comp3 = pun.city_code
AND pun.state_code = pus.state_code
AND (pus.state_code = IN_state_code
OR IN_state_code IS NULL)
AND pus.state_code = prb.jurisdiction_comp1;
SELECT DISTINCT
prb.jurisdiction_code,
psd.school_dst_name||','||pus.state_abbrev jurisdiction_name,
psd.state_code||'-'||psd.county_code||'-'||psd.city_code reg_jurisdiction_cd
FROM pay_run_balances prb,
per_assignments_f paf,
pay_us_states pus,
pay_us_city_school_dsts psd
WHERE paf.person_id = IN_person_id
AND paf.effective_start_date <= IN_as_of_date
AND paf.effective_end_date >= IN_start_date
AND paf.assignment_id = prb.assignment_id
AND prb.effective_date BETWEEN IN_start_date AND IN_as_of_date
AND prb.effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND prb.jurisdiction_code = psd.state_code||'-'||psd.school_dst_code
AND prb.jurisdiction_comp2 = psd.school_dst_code
AND prb.jurisdiction_comp1 = psd.state_code
AND (pus.state_code = IN_state_code
OR IN_state_code IS NULL)
AND prb.jurisdiction_comp1 = pus.state_code
AND pus.state_code = psd.state_code
UNION ALL
SELECT /*+ ORDERED */DISTINCT
prb.jurisdiction_code,
psd.school_dst_name||','||pus.state_abbrev jurisdiction_name,
psd.state_code||'-'||psd.county_code||'-0000' reg_jurisdiction_cd
FROM per_assignments_f paf,
pay_run_balances prb,
pay_us_states pus,
pay_us_county_school_dsts psd
WHERE paf.person_id = IN_person_id
AND paf.effective_start_date <= IN_as_of_date
AND paf.effective_end_date >= IN_start_date
AND prb.assignment_id = paf.assignment_id
AND prb.effective_date BETWEEN IN_start_date AND IN_as_of_date
AND prb.effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND prb.jurisdiction_code = psd.state_code||'-'||psd.school_dst_code
AND prb.jurisdiction_comp2 = psd.school_dst_code
AND prb.jurisdiction_comp1 = psd.state_code
AND (pus.state_code = IN_state_code
OR IN_state_code IS NULL)
AND prb.jurisdiction_comp1 = pus.state_code
AND pus.state_code = psd.state_code;
SELECT DISTINCT
pes.jurisdiction_code,
pes.state_code,
pus.state_abbrev
FROM pay_us_emp_state_tax_rules_f pes,
per_assignments_f paf,
pay_us_states pus
WHERE pes.assignment_id = paf.assignment_id
AND pes.state_code = pus.state_code
AND paf.effective_start_date BETWEEN pes.effective_start_date
AND pes.effective_end_date
/* Change for Performance Bug 6360505 */
AND IN_as_of_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
/* Change for Performance Bug 6360505 */
/* 8606883 - commented as this wouldn't pick up records
if there is an assignment update between start_date and
as_of_date */
/* AND IN_start_date BETWEEN paf.effective_start_date
AND paf.effective_end_date*/
AND paf.person_id = IN_person_id
/* Change for Performance Bug 7174993 */
AND (pus.state_code = IN_state_code
OR IN_state_code IS NULL)
-- AND pus.state_code = NVL(IN_state_code, pus.state_code)
AND EXISTS (
SELECT 'X'
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_run_results prr
WHERE action_type IN ('B','I','R','Q','V')
AND ppa.action_status = 'C'
AND ppa.effective_date BETWEEN IN_start_date
AND IN_as_of_date
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.assignment_id = pes.assignment_id
AND prr.assignment_action_id = paa.assignment_action_id
AND prr.jurisdiction_code = pes.jurisdiction_code
AND rownum = 1); -- added rownum to improve performance (Bug 3316599)
SELECT DISTINCT pes.jurisdiction_code,
puc.county_name||','||pus.state_abbrev jurisdiction_name
FROM pay_us_emp_county_tax_rules_f pes,
per_assignments_f paf,
pay_us_states pus,
pay_us_counties puc
WHERE pes.assignment_id = paf.assignment_id
AND pes.state_code = pus.state_code
AND pes.county_code = puc.county_code
AND pes.state_code = puc.state_code
AND paf.effective_start_date BETWEEN pes.effective_start_date
AND pes.effective_end_date
/* Change for Performance Bug 6360505 */
AND IN_as_of_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
/* Change for Performance Bug 6360505 */
AND IN_start_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND paf.person_id = IN_person_id
/* Change for Performance Bug 7174993 */
AND (pus.state_code = IN_state_code
OR IN_state_code IS NULL)
--AND pus.state_code = NVL(IN_state_code, pus.state_code)
AND EXISTS (
SELECT 'X'
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_run_results prr
WHERE action_type IN ('B','I','R','Q','V')
AND ppa.action_status = 'C'
AND ppa.effective_date BETWEEN IN_start_date
AND IN_as_of_date
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.assignment_id = pes.assignment_id
AND prr.assignment_action_id = paa.assignment_action_id
AND prr.jurisdiction_code = pes.jurisdiction_code
AND rownum = 1); -- added rownum to improve performance (Bug 3316599)
SELECT DISTINCT
pes.jurisdiction_code,
pun.city_name||','||pus.state_abbrev jurisdiction_name
FROM pay_us_emp_city_tax_rules_f pes,
per_assignments_f paf,
pay_us_states pus,
pay_us_city_names pun
WHERE pes.assignment_id = paf.assignment_id
AND pes.state_code = pus.state_code
AND pes.state_code = pun.state_code
AND pes.county_code = pun.county_code
AND pes.city_code = pun.city_code
AND paf.effective_start_date BETWEEN pes.effective_start_date
AND pes.effective_end_date
AND pun.primary_flag = 'Y'
/* Change for Performance Bug 6360505 */
AND IN_as_of_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
/* Change for Performance Bug 6360505 */
AND IN_start_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND paf.person_id = IN_person_id
/* Change for Performance Bug 7174993 */
AND (pus.state_code = IN_state_code
OR IN_state_code IS NULL)
-- AND pus.state_code = NVL(IN_state_code, pus.state_code)
AND EXISTS (
SELECT 'X'
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_run_results prr
WHERE action_type IN ('B','I','R','Q','V')
AND ppa.action_status = 'C'
AND ppa.effective_date BETWEEN IN_start_date
AND IN_as_of_date
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.assignment_id = pes.assignment_id
AND prr.assignment_action_id = paa.assignment_action_id
AND prr.jurisdiction_code = pes.jurisdiction_code
AND rownum = 1); -- added rownum to improve performance (Bug 3316599)
SELECT DISTINCT
pes.state_code||'-'||pes.school_district_code jurisdiction_code,
psd.school_dst_name||','||pus.state_abbrev jurisdiction_name,
pes.jurisdiction_code reg_jurisdiction_cd
FROM pay_us_emp_city_tax_rules_f pes,
per_assignments_f paf,
pay_us_states pus,
pay_us_city_school_dsts psd
WHERE pes.assignment_id = paf.assignment_id
AND pes.school_district_code IS NOT NULL
AND pes.state_code = pus.state_code
AND pes.school_district_code = psd.school_dst_code
AND pes.state_code = psd.state_code
AND pes.county_code = psd.county_code
AND pes.city_code = psd.city_code
AND paf.effective_start_date BETWEEN pes.effective_start_date
AND pes.effective_end_date
/* Change for Performance Bug 6360505 */
AND IN_as_of_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
/* Change for Performance Bug 6360505 */
AND IN_start_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND paf.person_id = IN_person_id
--AND pus.state_code = NVL(IN_state_code, pus.state_code)
/* Change for Performance Bug 7174993 */
AND (pus.state_code = IN_state_code
OR IN_state_code IS NULL)
AND EXISTS (
SELECT 'X'
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_run_results prr
WHERE action_type IN ('B','I','R','Q','V')
AND ppa.action_status = 'C'
AND ppa.effective_date BETWEEN IN_start_date
AND IN_as_of_date
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.assignment_id = pes.assignment_id
AND prr.assignment_action_id = paa.assignment_action_id
AND prr.jurisdiction_code = pes.state_code||'-'||pes.school_district_code
AND rownum = 1) -- Added rownum for perfromance enhancement (Bug 3316599)
UNION
SELECT DISTINCT
pes.state_code||'-'||pes.school_district_code jurisdiction_code,
psd.school_dst_name||','||pus.state_abbrev jurisdiction_name,
pes.jurisdiction_code reg_jurisdiction_cd
FROM pay_us_emp_county_tax_rules_f pes,
per_assignments_f paf,
pay_us_states pus,
pay_us_county_school_dsts psd
WHERE pes.assignment_id = paf.assignment_id
AND pes.school_district_code IS NOT NULL
AND pes.state_code = pus.state_code
AND pes.school_district_code = psd.school_dst_code
AND pes.state_code = psd.state_code
AND pes.county_code = psd.county_code
AND paf.effective_start_date BETWEEN pes.effective_start_date
AND pes.effective_end_date
/* Change for Performance Bug 6360505 */
AND IN_as_of_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
/* Change for Performance Bug 6360505 */
AND IN_start_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND paf.person_id = IN_person_id
--AND pus.state_code = NVL(IN_state_code, pus.state_code)
/* Change for Performance Bug 7174993 */
AND (pus.state_code = IN_state_code
OR IN_state_code IS NULL)
AND EXISTS (
SELECT 'X'
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_run_results prr
WHERE action_type IN ('B','I','R','Q','V')
AND ppa.action_status = 'C'
AND ppa.effective_date BETWEEN IN_start_date
AND IN_as_of_date
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.assignment_id = pes.assignment_id
AND prr.assignment_action_id = paa.assignment_action_id
AND prr.jurisdiction_code = pes.state_code||'-'||pes.school_district_code
AND rownum = 1); -- Added rownum for perfromance enhancement (Bug 3316599)
SELECT NVL(sui_state_code,'00')
FROM pay_us_emp_fed_tax_rules_f
WHERE business_group_id = IN_business_id
AND assignment_id = IN_assignment_id
AND effective_start_date <= IN_start_date
AND effective_end_date >= IN_as_of_date;
SELECT medicare_tax_exempt
FROM pay_us_emp_fed_tax_rules_v
WHERE assignment_id = IN_assignment_id
AND effective_start_date <= IN_start_date
AND effective_end_date >= IN_as_of_date;
SELECT TO_NUMBER(fed_attribute1) - 0.01, /* Bug#15852506 */
fed_attribute2
INTO L_medi_ee_lmt1, L_medi_ee_rate1
FROM pay_us_federal_tax_info_f
WHERE fed_information_category = '401K LIMITS'
AND L_as_of_date BETWEEN effective_start_date AND effective_end_date;
SELECT pdb.defined_balance_id
INTO l_defined_balance_id
FROM pay_balance_types pbt,
pay_balance_dimensions pbd,
pay_defined_balances pdb
WHERE pbt.balance_type_id = pdb.balance_type_id
AND pbd.balance_dimension_id = pdb.balance_dimension_id
AND pbt.legislation_code = 'US'
AND pbt.business_group_id IS NULL
AND pbd.legislation_code = 'US'
AND pbd.business_group_id IS NULL
AND pdb.legislation_code = 'US'
AND pdb.business_group_id IS NULL
AND pbt.balance_name = 'Medicare EE Taxable Over Limit'
AND pbd.database_item_suffix = '_PER_GRE_' || L_dimension;
SELECT NVL(sta_information9,'N')
FROM pay_us_emp_state_tax_rules_f
WHERE assignment_id = IN_prc_assignment_id
AND state_code = curr_state_code
AND L_as_of_date BETWEEN effective_start_date
AND effective_end_date
ORDER BY effective_end_date desc;
select assignment_id into l_assignment_id
from pay_assignment_actions where assignment_action_id = L_asg_action_id; /* BUG# 10350917 */
select assignment_id into l_assignment_id
from pay_assignment_actions where assignment_action_id = L_asg_action_id; /* Bug 10350917 */
select count(0)
into l_asg_exists
from per_assignments_f
where assignment_id = IN_prc_assignment_id
and L_as_of_date between effective_start_date and effective_end_date;
select max(effective_end_date)
into l_cursor_fetch_date
from per_assignments_f
where assignment_id = IN_prc_assignment_id;
DELETE pay_us_rpt_totals
WHERE session_id = IN_pact_id
AND business_group_id = IN_chunk_no
AND tax_unit_id = IN_prc_tax_unit_id; -- Bug 3316599 to reduce the cost of query