[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DECODE(pest.sdi_exempt, NULL, 'N', pest.sdi_exempt)
FROM pay_us_emp_state_tax_rules_f pest
WHERE pest.assignment_id = p_assign_id
AND TO_DATE(l_change_date, 'DD-MM-YYYY') BETWEEN
pest.effective_start_date AND pest.effective_end_date
AND pest.state_code = SUBSTR(p_jurisdiction_code, 1, 2);
SELECT DECODE(pest.sui_exempt, NULL, 'N', pest.sui_exempt)
FROM pay_us_emp_state_tax_rules_f pest
WHERE pest.assignment_id = p_assign_id
AND TO_DATE(l_change_date, 'DD-MM-YYYY') BETWEEN
pest.effective_start_date AND pest.effective_end_date
AND pest.state_code = SUBSTR(p_jurisdiction_code, 1, 2);
SELECT DECODE(pest.futa_tax_exempt, NULL, 'N', pest.futa_tax_exempt)
FROM pay_us_emp_fed_tax_rules_f pest
WHERE pest.assignment_id = p_assign_id
AND TO_DATE(l_change_date, 'DD-MM-YYYY') BETWEEN
pest.effective_start_date AND pest.effective_end_date;
SELECT DECODE(pest.ss_tax_exempt, NULL, 'N', pest.ss_tax_exempt)
FROM pay_us_emp_fed_tax_rules_f pest
WHERE pest.assignment_id = p_assign_id
AND TO_DATE(l_change_date, 'DD-MM-YYYY') BETWEEN
pest.effective_start_date AND pest.effective_end_date;
SELECT DECODE(pest.medicare_tax_exempt, NULL, 'N', pest.medicare_tax_exempt)
FROM pay_us_emp_fed_tax_rules_f pest
WHERE pest.assignment_id = p_assign_id
AND TO_DATE(l_change_date, 'DD-MM-YYYY') BETWEEN
pest.effective_start_date AND pest.effective_end_date;
SELECT puc.state_code || '-' || puc.county_code
FROM pay_us_counties puc,
pay_us_states pus,
per_addresses pa,
per_assignments_f paf
WHERE paf.assignment_id = p_assign_id
AND paf.person_id = pa.person_id
AND pa.primary_flag = 'Y'
AND TO_DATE(l_date_earned, 'DD-MM-YYYY') BETWEEN
paf.effective_start_date AND paf.effective_end_date
AND TO_DATE(l_date_earned, 'DD-MM-YYYY') BETWEEN
pa.date_from AND NVL(pa.date_to, TO_DATE('12-31-4712', 'mm-dd-yyyy'))
AND pus.state_abbrev = pa.add_information17 --override state
AND pus.state_code = '15' --for INDIANA
AND puc.state_code = pus.state_code
AND puc.county_name = pa.add_information19;
SELECT puc.state_code || '-' || puc.county_code,
pa.date_from
INTO l_rs_county_code, l_res_adr_date_start
FROM pay_us_counties puc,
pay_us_states pus,
per_addresses pa,
per_assignments_f paf
WHERE paf.assignment_id = p_assign_id
AND paf.person_id = pa.person_id
AND pa.primary_flag = 'Y'
AND TO_DATE(l_date_earned, 'DD-MM-YYYY') BETWEEN
paf.effective_start_date AND paf.effective_end_date
AND TO_DATE(l_date_earned, 'DD-MM-YYYY') BETWEEN
pa.date_from AND NVL(pa.date_to, TO_DATE('12-31-4712', 'mm-dd-yyyy'))
AND pus.state_abbrev = pa.region_2 --actual state
AND pus.state_code = '15' --for INDIANA
AND puc.state_code = pus.state_code
AND puc.county_name = pa.region_1; --actual county
select assignment_id
from per_all_assignments_f
where TO_DATE(p_date_earned, 'dd-mm-yyyy') between effective_start_date
and effective_end_date
and person_id in (select person_id
from per_all_assignments_f
where assignment_id =p_assign_id);
SELECT 'Y'
FROM pay_us_counties puc,
pay_us_states pus,
hr_locations hl,
hr_soft_coding_keyflex hscf,
per_assignments_f paf
WHERE paf.assignment_id = p_assign_id
/* Bug#8606659 */
-- AND l_date_earned BETWEEN /*6519715*/
AND TO_DATE(p_date_earned, 'dd-mm-yyyy') BETWEEN
/* Bug#8606659: changes end here */
paf.effective_start_date AND paf.effective_end_date
AND hscf.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
AND hscf.segment18 = hl.location_id
AND NVL(hl.loc_information17, hl.region_2) = pus.state_abbrev --actual state
AND pus.state_code = '15' --check only for Indiana
AND puc.state_code = pus.state_code
AND NVL(hl.loc_information19, hl.region_1) = puc.county_name --actual county
AND puc.state_code = SUBSTR(p_juri_code, 1, 2)
AND puc.county_code = SUBSTR(p_juri_code, 4, 3);
SELECT count(1)
INTO l_is_exist
FROM pay_us_counties puc,
pay_us_states pus,
hr_locations hl,
hr_soft_coding_keyflex hscf,
per_assignments_f paf
WHERE paf.assignment_id = rec_multiassgn.assignment_id
AND l_date_earned BETWEEN /*6519715*/
paf.effective_start_date AND paf.effective_end_date
AND hscf.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
AND paf.location_id = hl.location_id
AND NVL(hl.loc_information17, hl.region_2) = pus.state_abbrev
--actual state
AND pus.state_code = '15' --check only for Indiana
AND puc.state_code = pus.state_code
AND NVL(hl.loc_information19, hl.region_1) = puc.county_name
--actual county
AND puc.state_code = SUBSTR(p_juri_code, 1, 2)
AND puc.county_code = SUBSTR(p_juri_code, 4, 3);
/* SELECT count(1)
INTO l_is_exist
FROM pay_us_counties puc,
pay_us_states pus,
hr_locations hl,
hr_soft_coding_keyflex hscf,
per_assignments_f paf
WHERE paf.assignment_id = p_assign_id
AND l_date_earned BETWEEN /*6519715
paf.effective_start_date AND paf.effective_end_date
AND hscf.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
AND paf.location_id = hl.location_id
AND NVL(hl.loc_information17, hl.region_2) = pus.state_abbrev
--actual state
AND pus.state_code = '15' --check only for Indiana
AND puc.state_code = pus.state_code
AND NVL(hl.loc_information19, hl.region_1) = puc.county_name
--actual county
AND puc.state_code = SUBSTR(p_juri_code, 1, 2)
AND puc.county_code = SUBSTR(p_juri_code, 4, 3);
SELECT COUNTYTAX.county_tax
INTO l_county_tax_exists
FROM pay_us_county_tax_info_f COUNTYTAX
WHERE COUNTYTAX.JURISDICTION_CODE = SUBSTR(p_juri_code, 1, 6) || '-0000'
AND TO_DATE(l_date_earned, 'dd-mm-yyyy') BETWEEN
COUNTYTAX.effective_start_date AND COUNTYTAX.effective_end_date;
SELECT DECODE(hoi.ORG_INFORMATION19, 'ALL', 'Y',
'LOCALITIES', 'N',
'WORK_LOCALITIES','N',
'Y')
INTO l_state_level_tax_exists
FROM hr_organization_information hoi
WHERE hoi.org_information_context = 'State Tax Rules'
AND hoi.organization_id = p_tax_unit_id
AND hoi.org_information1 = l_state_abbrev;
SELECT DECODE(hoi.ORG_INFORMATION19,
'WORK_LOCALITIES','N',
'Y') -- State Tax Rules level
INTO l_wh_work_localities
FROM hr_organization_information hoi
WHERE hoi.org_information_context = 'State Tax Rules'
AND hoi.organization_id = p_tax_unit_id
AND hoi.org_information1 = l_state_abbrev;
SELECT DECODE(hoi.ORG_INFORMATION3,'Y','N','N','Y','Y')
--local level have to check if exempt
INTO l_local_level_tax_exists
FROM HR_ORGANIZATION_INFORMATION hoi
WHERE hoi.ORG_INFORMATION_CONTEXT = 'Local Tax Rules'
AND hoi.organization_id = p_tax_unit_id
AND hoi.org_information1 = SUBSTR(p_juri_code,1,6)||'-0000';
* This will return Y, if ALL Localities are selected under State Tax Rules
* and there's no record defined under local tax rules for the given
* locality and will return N, if option "LOCALITIES defined under Local Tax
* Rules" is selected under State Tax Rules and there's no record defined
* under local tax rules for the given locality
*/
RETURN (l_state_level_tax_exists);
select nvl(ADDR.add_information17,'ZZ')
from
per_addresses ADDR,
per_all_assignments_f ASSIGN
where TO_DATE(l_date_earned, 'dd-mm-yyyy')
between ASSIGN.effective_start_date
and ASSIGN.effective_end_date
and ASSIGN.assignment_id = p_assign_id
and ADDR.person_id = ASSIGN.person_id
and ADDR.primary_flag = 'Y'
and TO_DATE(l_date_earned, 'dd-mm-yyyy')
between nvl(ADDR.date_from, TO_DATE(l_date_earned, 'dd-mm-yyyy'))
and nvl(ADDR.date_to, TO_DATE(l_date_earned, 'dd-mm-yyyy'));
SELECT DISTINCT pus.state_abbrev
INTO l_state_abbrev
FROM pay_us_states pus
WHERE pus.state_code = SUBSTR(p_juri_code, 1, 2);
SELECT DISTINCT sit_exists
INTO l_sit_rs_exists
FROM pay_us_state_tax_info_f
WHERE state_code = SUBSTR(p_juri_code, 1, 2)
AND TO_DATE(l_date_earned, 'dd-mm-yyyy') BETWEEN
effective_start_date AND effective_end_date;
* business. So the select into will fail going to the exception handler
* which will return a l_sit_rs_exists = N for there is not state
* income tax.
*/
IF l_sit_rs_exists = 'Y' THEN /* 2 */
/*{*/
SELECT DECODE(hoi.ORG_INFORMATION2, 'ALL', 'Y', 'STATES', 'N', 'Y')
/* EI level */
INTO l_org_info2
FROM HR_ORGANIZATION_INFORMATION hoi
WHERE hoi.ORG_INFORMATION_CONTEXT = 'Employer Identification'
AND hoi.organization_id = p_tax_unit_id;
SELECT DECODE(hoi.ORG_INFORMATION18, 'Y', 'N', 'N', 'Y', 'Y')
/* state level have to check if exempt */
INTO l_sit_rs_exists
FROM HR_ORGANIZATION_INFORMATION hoi
WHERE hoi.ORG_INFORMATION_CONTEXT = 'State Tax Rules'
AND hoi.organization_id = p_tax_unit_id
AND hoi.org_information1 = l_state_abbrev;
SELECT DISTINCT sit_exists
INTO l_sit_wk_exists
FROM pay_us_state_tax_info_f
WHERE state_code = SUBSTR(p_juri_code, 1, 2)
AND TO_DATE(l_date_earned, 'dd-mm-yyyy') BETWEEN
effective_start_date AND effective_end_date;
select 'Y'
into l_wc_exists
from hr_organization_information hoi,
pay_us_states pus
where organization_id = p_tax_unit_id
and hoi.org_information_context = 'State Tax Rules'
and hoi.org_information1 = pus.state_abbrev
and pus.state_code = substr(p_juri_code,1,2)
and hoi.org_information8 is not null;
SELECT DISTINCT decode( nvl(str.wc_exempt,'N'),
'Y','N', -- if wc exemptthe don't take WC
'Y')
INTO l_wc_exists
FROM pay_us_emp_state_tax_rules_f str
WHERE str.state_code = SUBSTR(p_juri_code, 1, 2)
AND str.assignment_id = p_assign_id /* 5772548 */
AND TO_DATE(l_date_earned, 'dd-mm-yyyy') BETWEEN
str.effective_start_date AND str.effective_end_date;
SELECT STA_INFORMATION16
INTO l_misc1_state_tax
FROM pay_us_state_tax_info_f
WHERE state_code = SUBSTR(p_juri_code, 1, 2)
AND TO_DATE(l_date_earned, 'dd-mm-yyyy') BETWEEN
effective_start_date AND effective_end_date;
SELECT DISTINCT DECODE(STATETAX.sdi_ee_wage_limit,
NULL, 'N',
0, 'N',
'Y')
INTO l_sdi_ee_exists
FROM pay_us_state_tax_info_f STATETAX,
fnd_sessions SES
WHERE STATETAX.state_code = SUBSTR(p_juri_code, 1, 2)
AND SES.session_id = USERENV('SESSIONID')
AND SES.effective_date BETWEEN
STATETAX.effective_start_date AND STATETAX.effective_end_date;
SELECT DISTINCT DECODE(STATETAX.STA_INFORMATION1,
NULL, 'N',
0, 'N',
'Y')
INTO l_sdi_ee_exists
FROM pay_us_state_tax_info_f STATETAX,
fnd_sessions SES
WHERE STATETAX.state_code = SUBSTR(p_juri_code, 1, 2)
AND SES.session_id = USERENV('SESSIONID')
AND SES.effective_date BETWEEN
STATETAX.effective_start_date AND STATETAX.effective_end_date;
SELECT DECODE(STATETAX.sdi_er_wage_limit, NULL, 'N', 0, 'N', 'Y')
INTO l_sdi_er_exists
FROM pay_us_state_tax_info_f STATETAX,
fnd_sessions SES
WHERE STATETAX.state_code = SUBSTR(p_juri_code, 1, 2)
AND SES.session_id = USERENV('SESSIONID')
AND SES.effective_date BETWEEN
STATETAX.effective_start_date AND STATETAX.effective_end_date;
SELECT DISTINCT nvl(sta_information17,'N')
INTO l_eic_rs_exists
FROM pay_us_state_tax_info_f
WHERE state_code = SUBSTR(p_juri_code, 1, 2)
AND TO_DATE(l_date_earned, 'dd-mm-yyyy') BETWEEN
effective_start_date AND effective_end_date;
SELECT DISTINCT nvl(sta_information17,'N')
INTO l_eic_wk_exists
FROM pay_us_state_tax_info_f
WHERE state_code = SUBSTR(p_juri_code, 1, 2)
AND TO_DATE(l_date_earned, 'dd-mm-yyyy') BETWEEN
effective_start_date AND effective_end_date;
select to_char(effective_date,'dd-mm-yyyy')
into l_date
from pay_payroll_actions
where payroll_action_id = p_pact_id;
select to_char(effective_date,'dd-mm-yyyy')
into l_date
from pay_payroll_actions
where payroll_action_id = p_pact_id;
SELECT CITYTAX.city_tax
INTO l_city_rs_exists
FROM pay_us_city_tax_info_f CITYTAX,
fnd_sessions SES
WHERE CITYTAX.JURISDICTION_CODE = p_juri_code
AND SES.session_id = USERENV('SESSIONID')
AND SES.effective_date BETWEEN
CITYTAX.effective_start_date AND CITYTAX.effective_end_date;
SELECT DECODE(hoi.ORG_INFORMATION19,
'ALL','Y',
'LOCALITIES','N',
'WORK_LOCALITIES','N',
'Y') /* State Tax Rules level */
, hoi.ORG_INFORMATION19
-- new column added in the fetch for fixing bug 4711572
-- INTO l_org_info19
INTO l_org_info19,
l_local_tax_rules_type
FROM hr_organization_information hoi
WHERE hoi.org_information_context = 'State Tax Rules'
AND hoi.organization_id = p_tax_unit_id
AND hoi.org_information1 = l_state_abbrev;
SELECT DECODE(hoi.ORG_INFORMATION19,
'WORK_LOCALITIES','N',
'Y') /* State Tax Rules level */
INTO l_wh_work_localities
FROM hr_organization_information hoi
WHERE hoi.org_information_context = 'State Tax Rules'
AND hoi.organization_id = p_tax_unit_id
AND hoi.org_information1 = l_state_abbrev;
SELECT DECODE(hoi.ORG_INFORMATION3,'Y','N','N','Y','Y')
/* local level have to check if exempt */
INTO l_city_rs_exists
FROM HR_ORGANIZATION_INFORMATION hoi
WHERE hoi.ORG_INFORMATION_CONTEXT = 'Local Tax Rules'
AND hoi.organization_id = p_tax_unit_id
AND hoi.org_information1 = p_juri_code;
is selected at GRE level */
if (nvl(l_jd_type,'NL') = 'RW' or nvl(l_jd_type,'NL') = 'HW' )
then
hr_utility.trace('CITY_RS City Income Tax Exists set to withhold Tax: Y');
SELECT CITYTAX.head_tax
INTO l_ht_wk_exists
FROM pay_us_city_tax_info_f CITYTAX,
fnd_sessions SES
WHERE CITYTAX.JURISDICTION_CODE = p_juri_code
AND SES.session_id = USERENV('SESSIONID')
AND SES.effective_date BETWEEN
CITYTAX.effective_start_date AND CITYTAX.effective_end_date;
SELECT CITYTAX.city_tax
INTO l_city_wk_exists
FROM pay_us_city_tax_info_f CITYTAX,
fnd_sessions SES
WHERE CITYTAX.JURISDICTION_CODE = p_juri_code
AND SES.session_id = USERENV('SESSIONID')
AND SES.effective_date BETWEEN
CITYTAX.effective_start_date AND CITYTAX.effective_end_date;
SELECT puesrf.STATE_NON_RESIDENT_CERT
INTO l_nr_exists
FROM pay_us_emp_state_tax_rules_f puesrf,
fnd_sessions ses
WHERE puesrf.assignment_id = p_assign_id
AND SUBSTR(puesrf.jurisdiction_code,1,2) = SUBSTR(p_juri_code,1,2)
AND ses.session_id = USERENV('SESSIONID')
AND ses.effective_date BETWEEN
puesrf.effective_start_date AND puesrf.effective_end_date;
SELECT 'N'
INTO l_sui_exists
FROM hr_organization_information hoi
WHERE hoi.org_information_context = '1099R Magnetic Report Rules'
AND hoi.organization_id = p_tax_unit_id
AND hoi.org_information2 IS NOT NULL;
SELECT NVL(addr.add_information17,'ZZ')
FROM per_addresses addr,
per_all_assignments_f asg
WHERE TO_DATE(l_date_earned, 'dd-mm-yyyy')
BETWEEN asg.effective_start_date
AND asg.effective_end_date
AND asg.assignment_id = p_assign_id
AND addr.person_id = asg.person_id
AND addr.primary_flag = 'Y'
AND TO_DATE(l_date_earned, 'dd-mm-yyyy')
BETWEEN NVL(addr.date_from, TO_DATE(l_date_earned, 'dd-mm-yyyy'))
AND NVL(addr.date_to, TO_DATE(l_date_earned, 'dd-mm-yyyy'));
SELECT DECODE(hoi.org_information2, 'ALL', 'Y', 'STATES', 'N', 'Y')
FROM hr_organization_information hoi
WHERE hoi.org_information_context = 'Employer Identification'
AND hoi.organization_id = p_tax_unit_id;
SELECT DECODE(NVL(hoi.org_information18,'00'), 'N', '00', NVL(hoi.org_information18,'00'))
FROM hr_organization_information hoi
WHERE hoi.org_information_context = 'State Tax Rules'
AND hoi.organization_id = p_tax_unit_id
AND hoi.org_information1 = l_state_abbrev;
SELECT DECODE(hoi.org_information19,
'ALL','A',
'LOCALITIES','L',
'WORK_LOCALITIES','W',
'A')
FROM hr_organization_information hoi
WHERE hoi.org_information_context = 'State Tax Rules'
AND hoi.organization_id = p_tax_unit_id
AND hoi.org_information1 = l_state_abbrev;
SELECT DECODE(NVL(hoi.org_information3,'00'),'N','00',NVL(hoi.org_information3,'00'))
FROM hr_organization_information hoi
WHERE hoi.org_information_context = 'Local Tax Rules'
AND hoi.organization_id = p_tax_unit_id
AND hoi.org_information1 = p_juri_code;
SELECT DISTINCT sit_exists
FROM pay_us_state_tax_info_f
WHERE state_code = SUBSTR(p_juri_code, 1, 2)
AND TO_DATE(l_date_earned, 'DD-MM-YYYY') BETWEEN
effective_start_date AND effective_end_date;
SELECT city_tax
FROM pay_us_city_tax_info_f
WHERE jurisdiction_code = p_juri_code
AND TO_DATE(l_date_earned, 'DD-MM-YYYY') BETWEEN
effective_start_date AND effective_end_date;
SELECT county_tax
FROM pay_us_county_tax_info_f
WHERE jurisdiction_code = substr(p_juri_code, 1, 6) || '-0000'
AND to_date(l_date_earned, 'DD-MM-YYYY') BETWEEN
effective_start_date AND effective_end_date;
SELECT DISTINCT pus.state_abbrev
INTO l_state_abbrev
FROM pay_us_states pus
WHERE pus.state_code = SUBSTR(p_juri_code, 1, 2);
SELECT to_char(effective_date,'dd-mm-yyyy')
INTO l_date
FROM pay_payroll_actions
WHERE payroll_action_id = p_pact_id;
SELECT ppa.effective_date
FROM
pay_payroll_actions ppa
WHERE ppa.payroll_action_id=p_pact_id;
SELECT parameter_value
FROM pay_action_parameters
WHERE parameter_name = 'WAGE_ACCUMULATION_ENABLED';
SELECT parameter_value
FROM pay_action_parameters
WHERE parameter_name = 'WAGE_ACCUMULATION_YEAR';
SELECT effective_date
INTO l_effective_date
FROM pay_payroll_actions
WHERE payroll_action_id = p_pact_id;
tax_balances.delete;