[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)
******************************************************************************/
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 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;
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 pay_assignment_actions_s.NEXTVAL
INTO L_lockingactid
FROM dual;
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 state_code,sta_information5,sta_information6,sta_information3,sta_information4
FROM pay_us_state_tax_info_f pusif
WHERE IN_as_of_date <= pusif.effective_end_date
AND IN_start_date >= pusif.effective_start_date
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 */
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;
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