[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
dscully 21-jun-2000 40.9 Modified check_delete_row to
prevent deletion of work, tax, and
live locations in all situations
ahanda 22-JUL-2000 40.10 Changed the procedure
get_all_work_codes for performance
reasons.
dscully 9-AUG-2000 40.11/110.15 Changed reference to sysdate
to p_session_date in
get_all_work_codes
tclewis 26-JAN-2004 115.9 11.5.10 performance changes on the
following cursors:
csr_check_state_purge, csr_check_county_purge,
csr_check_city_purge, csr_chk_payroll
ardsouza 29-JAN-2004 115.10 Added dbdrv: command, SET VERIFY OFF
and NOCOPY hint for GSCC compliance.
sudedas 20-APR-2006 115.11 4563092 One message added to check_payroll_run
sudedas 11-Sep-2006 115.12 5486281 Turning Off SUI Wage Base Override Functionality .
========================================================================
*/
/* Check override state,county,city */
FUNCTION check_resi_override ( p_assignment_id in number,
p_session_date date,
p_state_code in varchar2,
p_county_code in varchar2,
p_city_code in varchar2
)
RETURN varchar2
IS
CURSOR csr_chk_res_state
IS
SELECT pus1.state_code,
pus.state_code
FROM pay_us_states pus,
pay_us_states pus1,
per_addresses pa,
per_assignments_f paf
WHERE paf.assignment_id = p_assignment_id
AND p_session_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND paf.person_id = pa.person_id
AND p_session_date BETWEEN pa.date_from
AND nvl(pa.date_to,to_date('12/31/4712','mm/dd/yyyy'))
AND pus.state_abbrev = nvl(pa.add_information17,pa.region_2)
AND pus1.state_abbrev = pa.region_2
AND pa.primary_flag = 'Y';
SELECT pus1.state_code,
puc1.county_code,
pus.state_code,
puc.county_code
FROM pay_us_states pus,
pay_us_states pus1,
pay_us_counties puc,
pay_us_counties puc1,
per_addresses pa,
per_assignments_f paf
WHERE paf.assignment_id = p_assignment_id
AND p_session_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND paf.person_id = pa.person_id
AND p_session_date BETWEEN pa.date_from
AND nvl(pa.date_to,to_date('12/31/4712','mm/dd/yyyy'))
AND pus.state_abbrev = nvl(pa.add_information17,pa.region_2)
AND pus.state_code = puc.state_code
AND puc.county_name = nvl(pa.add_information19,pa.region_1)
AND pus1.state_abbrev = pa.region_2
AND pus1.state_code = puc1.state_code
AND puc1.county_name = pa.region_1
AND pa.primary_flag = 'Y';
IS SELECT pus1.state_code,
puc1.county_code,
pucy1.city_code,
pus.state_code,
puc.county_code,
pucy.city_code
FROM pay_us_states pus,
pay_us_states pus1,
pay_us_counties puc,
pay_us_counties puc1,
pay_us_city_names pucy,
pay_us_city_names pucy1,
per_addresses pa,
per_assignments_f paf
WHERE paf.assignment_id = p_assignment_id
AND p_session_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND paf.person_id = pa.person_id
AND p_session_date BETWEEN pa.date_from
AND nvl(pa.date_to,to_date('12/31/4712','mm/dd/yyyy'))
AND pus.state_abbrev = nvl(pa.add_information17,pa.region_2)
AND pus.state_code = puc.state_code
AND puc.county_name = nvl(pa.add_information19,pa.region_1)
AND pus1.state_abbrev = pa.region_2
AND pus1.state_code = puc1.state_code
AND puc1.county_name = pa.region_1
AND pucy.state_code = pus.state_code
AND pucy.state_code = puc.state_code
AND pucy.county_code = puc.county_code
AND pucy1.state_code = puc1.state_code
AND pucy1.county_code = puc1.county_code
AND pucy.city_name = nvl(pa.add_information18,pa.town_or_city)
AND pucy1.city_name = pa.town_or_city
AND pa.primary_flag = 'Y';
IS SELECT pus1.state_code,
pus.state_code,
paf.location_id,
hscf.segment18
FROM pay_us_states pus,
pay_us_states pus1,
hr_locations hl,
hr_locations hl1,
hr_soft_coding_keyflex hscf,
per_assignments_f paf
WHERE paf.assignment_id = p_assignment_id
AND p_session_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND hscf.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
AND hl.location_id = nvl(hscf.segment18,paf.location_id)
AND pus.state_abbrev = nvl(hl.loc_information17,hl.region_2)
AND hl1.location_id = paf.location_id
AND pus1.state_abbrev = nvl(hl1.loc_information17,hl1.region_2);
IS SELECT pus1.state_code,
puc1.county_code,
pus.state_code,
puc.county_code,
paf.location_id,
hscf.segment18
FROM pay_us_states pus,
pay_us_states pus1,
pay_us_counties puc,
pay_us_counties puc1,
hr_locations hl,
hr_locations hl1,
hr_soft_coding_keyflex hscf,
per_assignments_f paf
WHERE paf.assignment_id = p_assignment_id
AND p_session_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND hscf.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
AND hl.location_id = nvl(hscf.segment18,paf.location_id)
AND pus.state_abbrev = nvl(hl.loc_information17,hl.region_2)
AND hl1.location_id = paf.location_id
AND pus1.state_abbrev = nvl(hl1.loc_information17,hl1.region_2)
AND pus1.state_code = puc1.state_code
AND pus.state_code = puc.state_code
AND puc.county_name = nvl(hl.loc_information19,hl.region_1)
and puc1.county_name = nvl(hl1.loc_information19,hl1.region_1);
IS SELECT pus1.state_code,
puc1.county_code,
pucy1.city_code,
pus.state_code,
puc.county_code,
pucy.city_code,
paf.location_id,
hscf.segment18
FROM pay_us_states pus,
pay_us_states pus1,
pay_us_counties puc,
pay_us_counties puc1,
pay_us_city_names pucy,
pay_us_city_names pucy1,
hr_locations hl,
hr_locations hl1,
hr_soft_coding_keyflex hscf,
per_assignments_f paf
WHERE paf.assignment_id = p_assignment_id
AND p_session_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND hscf.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
AND hl.location_id = nvl(hscf.segment18,paf.location_id)
AND pus.state_abbrev = nvl(hl.loc_information17,hl.region_2)
AND hl1.location_id = paf.location_id
AND pus1.state_abbrev = nvl(hl1.loc_information17,hl1.region_2)
AND pus1.state_code = puc1.state_code
AND pus.state_code = puc.state_code
AND puc.county_name = nvl(hl.loc_information19,hl.region_1)
and puc1.county_name = nvl(hl1.loc_information19,hl1.region_1)
AND pucy.state_code = pus.state_code
AND pucy.county_code = puc.county_code
AND pucy.city_name = nvl(hl.loc_information18,hl.town_or_city)
AND pucy1.state_code = puc1.state_code
AND pucy1.county_code = puc1.county_code
AND pucy1.city_name = nvl(hl1.loc_information18,hl1.town_or_city);
Purpose : If datetrack mode is 'DELETE_NEXT_CHANGE' or 'FUTURE_CHANGE'
the procedure will check whether there is any future record
with different location id. If there is it will give and
error message to the user.
For all modes if location id is changed it will check
if a payroll has been run for the assignment,
within a given time period. The return value will be set
accordingly which will be used to raise warning message
*/
function check_payroll_run ( p_assignment_id in number,
p_new_location_code in varchar2,
p_new_location_id in number,
p_session_date in date,
p_effective_start_date in date,
p_effective_end_date in date,
p_mode in varchar2) return varchar2 is
l_code number;
select paf.location_id
from PER_ASSIGNMENTS_F paf
where paf.assignment_id = p_assignment_id
and p_session_date between paf.effective_start_date
and paf.effective_end_date;
select paf1.location_id
from per_assignments_f paf1
where paf1.assignment_id = p_assignment_id
and paf1.effective_start_date > p_effective_end_date
order by paf1.effective_start_date;
select 1
from per_assignments_f paf
,pay_payroll_actions ppa
,pay_assignment_actions paa
where paf.assignment_id = p_assignment_id
and ppa.payroll_id = paf.payroll_id
and ppa.action_type in ('E', 'Q','R')
and ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_id = paf.assignment_id
and trunc(ppa.date_earned) between p_effective_start_date
and p_effective_end_date;
select 1
from PAY_ASSIGNMENT_ACTIONS paa
where paa.assignment_id = p_assignment_id
and exists(
select 1
from PAY_PAYROLL_ACTIONS ppa
where ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_type in ('E','Q','R')
and trunc(ppa.date_earned) between p_effective_start_date
and p_effective_end_date );
select 'Y'
from dual
where exists (select null
from PAY_US_EMP_FED_TAX_RULES_F pef
where pef.assignment_id = p_assignment_id);
select pus.state_code
from pay_us_states pus,
hr_locations hl
where hl.location_id = p_location_id
and pus.state_abbrev = nvl(loc_information17,region_2);
if p_mode = 'DELETE_NEXT_CHANGE' and l_payroll_installed then
hr_utility.set_location('pay_us_emp_dt_tax_rules.check_payroll_run - opening cursor ',1);
end if; /* end checking of DELETE_NEXT_CHANGE */
/* This is checked because if mode is DELETE-NEXT-CHANGE */
/* or DELETE-FUTURE-CHANGE and location_id is different */
/* in any of the next records user will get an error */
/* and there is no need to check for the payroll run */
open csr_chk_payroll;
select 1
from HR_LOCATIONS hrl,
PER_ASSIGNMENTS_F paf
where paf.assignment_id = p_assignment_id
and hrl.location_id = paf.location_id
and exists (select null
from PAY_US_STATES pus
where pus.state_abbrev in (hrl.loc_information17,hrl.region_2)
and pus.state_code = p_state_code);
select 1
from HR_LOCATIONS hrl,
HR_SOFT_CODING_KEYFLEX hscf,
PER_ASSIGNMENTS_F paf
where paf.assignment_id = p_assignment_id
and hscf.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
and hrl.location_id = hscf.segment18
and exists (select null
from PAY_US_STATES pus
where pus.state_abbrev in (hrl.loc_information17,hrl.region_2)
and pus.state_code = p_state_code);
select 1
from HR_LOCATIONS hrl,
PER_ASSIGNMENTS_F paf
where paf.assignment_id = p_assignment_id
and hrl.location_id = paf.location_id
and exists (select null
from PAY_US_COUNTIES puc,
PAY_US_STATES pus
where ((pus.state_abbrev = hrl.loc_information17
and puc.county_name = hrl.loc_information19)
or
(pus.state_abbrev = hrl.region_2
and puc.county_name = hrl.region_1))
and pus.state_code = p_state_code
and puc.state_code = pus.state_code
and puc.county_code = p_county_code);
select 1
from HR_LOCATIONS hrl,
HR_SOFT_CODING_KEYFleX hscf,
PER_ASSIGNMENTS_F paf
where paf.assignment_id = p_assignment_id
and hscf.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
and hrl.location_id = hscf.segment18
and exists (select null
from PAY_US_COUNTIES puc,
PAY_US_STATES pus
where ((pus.state_abbrev = hrl.loc_information17
and puc.county_name = hrl.loc_information19)
or
(pus.state_abbrev = hrl.region_2
and puc.county_name = hrl.region_1))
and pus.state_code = p_state_code
and puc.state_code = pus.state_code
and puc.county_code = p_county_code);
select 1
from HR_LOCATIONS hrl,
PER_ASSIGNMENTS_F paf
where paf.assignment_id = p_assignment_id
and hrl.location_id = paf.location_id
and exists (select null
from PAY_US_CITY_NAMES puci,
PAY_US_COUNTIES puco,
PAY_US_STATES pus
where ((pus.state_abbrev = hrl.loc_information17
and puco.county_name = hrl.loc_information19
and puci.city_name = hrl.loc_information18)
or
(pus.state_abbrev = hrl.region_2
and puco.county_name = hrl.region_1
and puci.city_name = hrl.town_or_city))
and pus.state_code = p_state_code
and puco.state_code = pus.state_code
and puco.county_code = p_county_code
and puci.state_code = pus.state_code
and puci.county_code = puco.county_code
and puci.city_code = p_city_code);
select 1
from HR_LOCATIONS hrl,
HR_SOFT_CODING_KEYFLEX hscf,
PER_ASSIGNMENTS_F paf
where paf.assignment_id = p_assignment_id
and hscf.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
and hrl.location_id = hscf.segment18
and exists (select null
from PAY_US_CITY_NAMES puci,
PAY_US_COUNTIES puco,
PAY_US_STATES pus
where ((pus.state_abbrev = hrl.loc_information17
and puco.county_name = hrl.loc_information19
and puci.city_name = hrl.loc_information18)
or
(pus.state_abbrev = hrl.region_2
and puco.county_name = hrl.region_1
and puci.city_name = hrl.town_or_city))
and pus.state_code = p_state_code
and puco.state_code = pus.state_code
and puco.county_code = p_county_code
and puci.state_code = pus.state_code
and puci.county_code = puco.county_code
and puci.city_code = p_city_code);
select 1
from PER_ADDRESSES pa,
PER_ASSIGNMENTS_F paf
where paf.assignment_id = p_assignment_id
and pa.person_id = paf.person_id
and exists (select null
from PAY_US_STATES pus
where pus.state_abbrev in (pa.add_information17,pa.region_2)
and pus.state_code = p_state_code);
select 1
from PER_ADDRESSES pa,
PER_ASSIGNMENTS_F paf
where paf.assignment_id = p_assignment_id
and pa.person_id = paf.person_id
and exists (select null
from PAY_US_COUNTIES puc,
PAY_US_STATES pus
where ((pus.state_abbrev = pa.add_information17
and puc.county_name = pa.add_information19)
or
(pus.state_abbrev = pa.region_2
and puc.county_name = pa.region_1))
and pus.state_code = p_state_code
and puc.state_code = pus.state_code
and puc.county_code = p_county_code);
select 1
from PER_ADDRESSES pa,
PER_ASSIGNMENTS_F paf
where paf.assignment_id = p_assignment_id
and pa.person_id = paf.person_id
and exists (select null
from PAY_US_CITY_NAMES puci,
PAY_US_COUNTIES puco,
PAY_US_STATES pus
where ((pus.state_abbrev = pa.add_information17
and puco.county_name = pa.add_information19
and puci.city_name = pa.add_information18)
or
(pus.state_abbrev = pa.region_2
and puco.county_name = pa.region_1
and puci.city_name = pa.town_or_city))
and pus.state_code = p_state_code
and puco.state_code = pus.state_code
and puco.county_code = p_county_code
and puci.state_code = pus.state_code
and puci.county_code = puco.county_code
and puci.city_code = p_city_code);
select 1
from per_assignments_f paf
,pay_payroll_actions ppa
,pay_assignment_actions paa
,pay_run_results prr
where substr(prr.jurisdiction_code,1,2) = p_state_code
and paf.assignment_id = p_assignment_id
and ppa.payroll_id = paf.payroll_id
and ppa.action_type in ('E', 'Q','R')
and ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_id = paf.assignment_id
and paa.assignment_action_id = prr.assignment_action_id ;
/* select 1
from pay_run_results prr,
pay_assignment_actions paa
where substr(prr.jurisdiction_code,1,2) = p_state_code
and paa.assignment_action_id = prr.assignment_action_id
and paa.assignment_id = p_assignment_id
and exists ( select null
from pay_payroll_actions ppa
where ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_type in ('E','Q','R'));
select 1
from per_assignments_f paf
,pay_payroll_actions ppa
,pay_assignment_actions paa
,pay_run_results prr
where substr(prr.jurisdiction_code,1,6) = p_state_code || '-'||
p_county_code
and paf.assignment_id = p_assignment_id
and ppa.payroll_id = paf.payroll_id
and ppa.action_type in ('E', 'Q','R')
and ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_id = paf.assignment_id
and paa.assignment_action_id = prr.assignment_action_id ;
select 1
from pay_run_results prr,
pay_assignment_actions paa
where substr(prr.jurisdiction_code,1,6) = p_state_code || '-'||
p_county_code
and paa.assignment_action_id = prr.assignment_action_id
and paa.assignment_id = p_assignment_id
and exists ( select null
from pay_payroll_actions ppa
where ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_type in ('E','Q','R'));
select 1
from per_assignments_f paf
,pay_payroll_actions ppa
,pay_assignment_actions paa
,pay_run_results prr
where prr.jurisdiction_code = p_state_code || '-'||
p_county_code ||'-'||p_city_code
and paf.assignment_id = p_assignment_id
and ppa.payroll_id = paf.payroll_id
and ppa.action_type in ('E', 'Q','R')
and ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_id = paf.assignment_id
and paa.assignment_action_id = prr.assignment_action_id ;
/* select 1
from pay_run_results prr,
pay_assignment_actions paa
where prr.jurisdiction_code = p_state_code || '-'||
p_county_code ||'-'||p_city_code
and paa.assignment_action_id = prr.assignment_action_id
and paa.assignment_id = p_assignment_id
and exists ( select null
from pay_payroll_actions ppa
where ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_type in ('E','Q','R'));
select 1
from PAY_US_EMP_COUNTY_TAX_RULES_F pcf
where pcf.assignment_id = p_assignment
and pcf.school_district_code is not null
and pcf.effective_end_date >= p_start_date
and pcf.effective_start_date <= p_end_date
UNION ALL
select 1
from PAY_US_EMP_CITY_TAX_RULES_F pcif
where pcif.assignment_id = p_assignment
and pcif.school_district_code is not null
and pcif.effective_end_date >= p_start_date
and pcif.effective_start_date <= p_end_date;
select 1
from PAY_US_EMP_COUNTY_TAX_RULES_F pcf
where pcf.assignment_id = p_assignment
and pcf.school_district_code is not null
and pcf.effective_end_date >= p_start_date
and pcf.effective_start_date <= p_end_date
and rowid <> chartorowid(p_rowid)
UNION ALL
select 1
from PAY_US_EMP_CITY_TAX_RULES_F pcif
where pcif.assignment_id = p_assignment
and pcif.school_district_code is not null
and pcif.effective_end_date >= p_start_date
and pcif.effective_start_date <= p_end_date
and rowid <> chartorowid(p_rowid);
select location_id,
effective_start_date,
effective_end_date
from per_assignments_f
where assignment_id = p_assignment_id
and business_group_id + 0 = p_business_group_id
and p_effective_start_date between effective_start_date
and effective_end_date;
select location_id
from per_assignments_f
where assignment_id = p_assignment_id
and business_group_id + 0 = p_business_group_id
and effective_start_date > p_validation_date;
select pus.state_code,
puc.county_code,
puci.city_code,
pus.state_name,
puc.county_name,
puci.city_name
from PAY_US_CITY_NAMES puci,
PAY_US_COUNTIES puc,
PAY_US_STATES pus,
PER_ADDRESSES pa,
PER_ASSIGNMENTS_F paf
where paf.assignment_id = p_assignment_id
and p_session_date between paf.effective_start_date and
paf.effective_end_date
and pa.person_id = paf.person_id
and pa.primary_flag = 'Y'
and p_session_date between pa.date_from and
nvl(pa.date_to,p_session_date)
and pus.state_abbrev = nvl(pa.add_information17,pa.region_2)
and puc.state_code = pus.state_code
and puc.county_name = nvl(pa.add_information19,pa.region_1)
and puci.state_code = pus.state_code
and puci.county_code = puc.county_code
and puci.city_name = nvl(pa.add_information18,pa.town_or_city);
select pus.state_code,
puc.county_code,
puci.city_code,
pus.state_name,
puc.county_name,
puci.city_name
from PAY_US_CITY_NAMES puci,
PAY_US_COUNTIES puc,
PAY_US_STATES pus,
HR_LOCATIONS hrl,
HR_SOFT_CODING_KEYFLEX hscf,
PER_ASSIGNMENTS_F paf
where paf.assignment_id = p_assignment_id
and p_session_date between paf.effective_start_date and
paf.effective_end_date
and hscf.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
and nvl(hscf.segment18,paf.location_id) = hrl.location_id
and pus.state_abbrev = nvl(hrl.loc_information17,hrl.region_2)
and puc.state_code = pus.state_code
and puc.county_name = nvl(hrl.loc_information19,hrl.region_1)
and puci.state_code = pus.state_code
and puci.county_code = puc.county_code
and puci.city_name = nvl(hrl.loc_information18,hrl.town_or_city);
select 1
from PAY_US_EMP_STATE_TAX_RULES_F str
where str.assignment_id = p_assignment_id
and str.jurisdiction_code = p_jurisdiction;
select 1
from PAY_US_EMP_COUNTY_TAX_RULES_F ctr
where ctr.assignment_id = p_assignment_id
and ctr.jurisdiction_code = p_jurisdiction;
select 1
from PAY_US_EMP_CITY_TAX_RULES_F ctr
where ctr.assignment_id = p_assignment_id
and ctr.jurisdiction_code = p_jurisdiction;
procedure check_delete_tax_row ( p_assignment_id in number,
p_state_code in varchar2,
p_county_code in varchar2,
p_city_code in varchar2) is
l_ret_code number;
/* fnd_message.set_name('Cannot delete. State assigned to resident address') */
fnd_message.set_name('PAY', 'PAY_52296_TAX_STDEL_RES');
/* fnd_message.set_name('Cannot delete. County assigned to resident address') */
fnd_message.set_name('PAY', 'PAY_52297_TAX_CODEL_RES');
/* fnd_message.set_name('Cannot delete. City assigned to resident address') */
fnd_message.set_name('PAY', 'PAY_52298_TAX_CIDEL_RES');
/* fnd_message.set_name('Cannot delete. Payroll has been run ') */
fnd_message.set_name('PAY', 'PAY_52235_TAX_RULE_DELETE');
/* fnd_message.set_name('Cannot delete. Payroll has been run ') */
fnd_message.set_name('PAY', 'PAY_52235_TAX_RULE_DELETE');
/* fnd_message.set_name('Cannot delete. Payroll has been run ') */
fnd_message.set_name('PAY', 'PAY_52235_TAX_RULE_DELETE');
end check_delete_tax_row;
select paf.location_id, hsc.segment18
from HR_SOFT_CODING_KEYFLEX hsc,
PER_ASSIGNMENTS_F paf
where paf.assignment_id = p_assignment_id
and p_session_date between paf.effective_start_date and
paf.effective_end_date
and hsc.soft_coding_keyflex_id = paf.soft_coding_keyflex_id;
select pus.state_code,
puc.county_code,
puci.city_code,
pus.state_name,
puc.county_name,
puci.city_name
from PAY_US_CITY_NAMES puci,
PAY_US_COUNTIES puc,
PAY_US_STATES pus,
HR_LOCATIONS hrl
where hrl.location_id = cp_location_id
and pus.state_abbrev = hrl.region_2
and puc.state_code = pus.state_code
and puc.county_name = hrl.region_1
and puci.state_code = pus.state_code
and puci.county_code = puc.county_code
and puci.city_name = hrl.town_or_city;
select pus.state_code,
puc.county_code,
puci.city_code,
pus.state_name,
puc.county_name,
puci.city_name
from PAY_US_CITY_NAMES puci,
PAY_US_COUNTIES puc,
PAY_US_STATES pus,
HR_LOCATIONS hrl
where hrl.location_id = cp_location_id
and pus.state_abbrev = hrl.loc_information17
and puc.state_code = pus.state_code
and puc.county_name = hrl.loc_information19
and puci.state_code = pus.state_code
and puci.county_code = puc.county_code
and puci.city_name = hrl.loc_information18;
select pus.state_code,
puc.county_code,
puci.city_code,
pus.state_name,
puc.county_name,
puci.city_name
from PAY_US_CITY_NAMES puci,
PAY_US_COUNTIES puc,
PAY_US_STATES pus,
PER_ADDRESSES pa,
PER_ASSIGNMENTS_F paf
where paf.assignment_id = p_assignment_id
and p_session_date between paf.effective_start_date and
paf.effective_end_date
and pa.person_id = paf.person_id
and pa.primary_flag = 'Y'
and p_session_date between pa.date_from and
nvl(pa.date_to,to_date('12/31/4712','MM/DD/YYYY'))
and pus.state_abbrev = pa.region_2
and puc.state_code = pus.state_code
and puc.county_name = pa.region_1
and puci.state_code = pus.state_code
and puci.county_code = puc.county_code
and puci.city_name = pa.town_or_city;