[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT i.input_value_id
FROM pay_input_values_f i
WHERE i.element_type_id = v_element_type_id
AND (i.business_group_id = p_bg_id
OR i.business_group_id IS NULL)
AND upper(i.name) = upper(v_input_name)
AND p_adj_date BETWEEN
i.effective_start_date AND i.effective_end_date
;
SELECT 'Y'
FROM PAY_TAXABILITY_RULES
WHERE jurisdiction_code = v_jurisdiction_code
and tax_category = g_earnings_category
and tax_type = v_tax_type
and classification_id = g_classification_id
and nvl(status,'VALID') <> 'D'
;
SELECT 'Y'
FROM PAY_TAXABILITY_RULES
WHERE jurisdiction_code = g_fed_jd
and tax_category = g_earnings_category
and tax_type = v_tax_type
and classification_id = g_classification_id
and nvl(status,'VALID') <> 'D'
;
SELECT 'N'
FROM PAY_TAXABILITY_RULES
WHERE jurisdiction_code = v_jurisdiction_code
and nvl(status,'VALID') <> 'D'
;
SELECT 'Y'
FROM pay_us_county_school_dsts pcsd
WHERE pcsd.state_code = substr(g_sch_dist_jur,1,2)
AND pcsd.school_dst_code = substr(g_sch_dist_jur,4,5)
;
SELECT ftax.futa_wage_limit,
ftax.ss_ee_wage_limit,
ftax.ss_er_wage_limit
FROM PAY_US_FEDERAL_TAX_INFO_F ftax
WHERE v_effective_date BETWEEN ftax.effective_start_date
AND ftax.effective_end_date
AND ftax.fed_information_category = '401K LIMITS';
SELECT ti.sdi_ee_wage_limit,
ti.sdi_er_wage_limit,
ti.sui_ee_wage_limit,
ti.sui_er_wage_limit,
ti.STA_INFORMATION21
FROM PAY_US_STATES st,
PAY_US_STATE_TAX_INFO_F ti
WHERE v_effective_date BETWEEN
ti.effective_start_date AND ti.effective_end_date
and st.state_code =
ti.state_code
and st.state_abbrev = v_state_abbrev
;
SELECT e.element_type_id,
c.classification_name,
e.element_information_category earnings_lookup_type,
e.classification_id,
e.element_information1 earnings_category
FROM PAY_ELEMENT_CLASSIFICATIONS c,
PAY_ELEMENT_TYPES_F e,
hr_organization_information hoi
WHERE e.element_name = p_element_type
AND (e.business_group_id = p_bg_id
OR e.business_group_id IS NULL
)
AND e.classification_id = c.classification_id
AND p_adjustment_date BETWEEN
e.effective_start_date AND e.effective_end_date
AND hoi.organization_id = p_bg_id
AND hoi.org_information_context = 'Business Group Information'
AND c.legislation_code = hoi.org_information9
;
SELECT i.name INPUT_NAME,
i.input_value_id,
NVL(hr.meaning, NVL(i.default_value,
DECODE(i.uom,
'I', '0',
'M', '0',
'N', '0',
'T', '0',
'C', 'Unknown - US_TAX_BAL_ADJ',
'H_DECIMAL1', '0.0',
'H_DECIMAL2', '0.00',
'H_DECIMAL3', '0.000',
'H_HH', '12',
'H_HHMM', '12:00',
'H_HHMMSS', '12:00:00',
'D', fnd_date.date_to_displaydate(fnd_date.canonical_to_date(p_adjustment_date)),
'ND', To_Char(p_adjustment_date, 'Day')))
) default_value
FROM HR_LOOKUPS hr,
PAY_INPUT_VALUES_F i
WHERE i.element_type_id = v_element_type_id
AND i.mandatory_flag = 'Y'
AND i.default_value = hr.lookup_code (+)
AND i.lookup_type = hr.lookup_type (+)
AND i.name NOT IN ('Pay Value')
;
SELECT 'Y'
INTO l_medicare_taxability
FROM pay_taxability_rules
WHERE jurisdiction_code = g_fed_jd
AND tax_category = g_earnings_category
AND tax_type = 'MEDICARE'
AND classification_id = g_classification_id
AND NVL(status,'VALID') <> 'D';
SELECT TO_NUMBER(pft.fed_attribute1) - 0.01 /* Subtract 0.01 from this value as it stores the starting Limit */
INTO l_medi_ee_limit1 /* Bug#15852506 */
FROM pay_us_federal_tax_info_f pft
WHERE pft.fed_information_category = '401K LIMITS'
AND p_adjustment_date BETWEEN pft.effective_start_date AND pft.effective_end_date;
SELECT pet.element_type_id, piv.input_value_id
INTO l_element_type_id, l_input_value_id
FROM pay_element_types_f pet,
pay_input_values_f piv
WHERE pet.element_name = 'Medicare_EE_Over_Limit'
AND piv.element_type_id = pet.element_type_id
AND piv.name = 'TAXABLE'
AND pet.business_group_id IS NULL
AND pet.legislation_code = 'US'
AND p_adjustment_date BETWEEN pet.effective_start_date AND pet.effective_end_date
AND piv.business_group_id IS NULL
AND piv.legislation_code = 'US'
AND p_adjustment_date BETWEEN piv.effective_start_date AND piv.effective_end_date;
SELECT state_code
FROM PAY_US_STATES
WHERE state_abbrev = p_state_abbrev
;
SELECT cn.state_code,
cn.county_code
FROM PAY_US_COUNTIES cn,
PAY_US_STATES s
WHERE cn.county_name = p_county_name
and cn.state_code = s.state_code
and s.state_abbrev = p_state_abbrev
;
SELECT 'PASS'
FROM PAY_US_EMP_STATE_TAX_RULES st,
PAY_US_STATES pus
WHERE st.assignment_id = p_assignment_id
and st.state_code = pus.state_code
and pus.state_abbrev = p_state_abbrev
;
SELECT 'PASS'
FROM PAY_US_EMP_CITY_TAX_RULES
WHERE assignment_id = p_assignment_id
and jurisdiction_code = x_jd
UNION
SELECT 'PASS'
FROM PAY_US_EMP_COUNTY_TAX_RULES
WHERE assignment_id = p_assignment_id
and jurisdiction_code = x_jd
;
SELECT to_date('31/12/' || TO_CHAR(p_adjustment_date, 'YYYY'), 'DD/MM/YYYY')
FROM SYS.DUAL
;
SELECT 'Y'
FROM pay_us_state_tax_info_f
WHERE state_code = SUBSTR(p_jd_code, 1, 2)
AND sdi_er_wage_limit IS NOT NULL
AND p_adj_date BETWEEN effective_start_date AND effective_end_date;
SELECT 'Y'
FROM pay_us_state_tax_info_f
WHERE state_code = SUBSTR(p_jd_code, 1, 2)
AND sdi_ee_wage_limit IS NOT NULL
AND p_adj_date BETWEEN effective_start_date AND effective_end_date;
SELECT 'Y'
FROM pay_us_state_tax_info_f
WHERE state_code = SUBSTR(p_jd_code, 1, 2)
AND STA_INFORMATION21 IS NOT NULL
AND p_adj_date BETWEEN effective_start_date AND effective_end_date;
SELECT 'Y'
FROM pay_us_state_tax_info_f
WHERE state_code = substr(p_jd_code, 1, 2)
AND sui_er_wage_limit IS NOT NULL
AND p_adj_date BETWEEN effective_start_date AND effective_end_date;
SELECT 'Y'
FROM pay_us_state_tax_info_f
WHERE state_code = substr(p_jd_code, 1, 2)
AND sui_ee_wage_limit IS NOT NULL
AND p_adj_date BETWEEN effective_start_date AND effective_end_date;
SELECT sit_exists
FROM pay_us_state_tax_info_f
WHERE state_code = substr(p_jd_code, 1, 2)
AND p_adj_date BETWEEN effective_start_date AND effective_end_date;
SELECT county_tax
FROM pay_us_county_tax_info_f
WHERE jurisdiction_code = substr(p_jd_code, 1, 7)||'0000'
AND p_adj_date BETWEEN effective_start_date AND effective_end_date;
SELECT city_tax
FROM pay_us_city_tax_info_f
WHERE jurisdiction_code = p_jd_code
AND p_adj_date BETWEEN effective_start_date AND effective_end_date;
SELECT c.classification_name
FROM PAY_ELEMENT_CLASSIFICATIONS c,
PAY_ELEMENT_TYPES_F e,
hr_organization_information hoi
WHERE e.classification_id = c.classification_id
AND hoi.organization_id = p_bg_id
AND e.element_name = p_element_type
AND (e.business_group_id = p_bg_id
OR e.business_group_id IS NULL)
AND hoi.org_information_context = 'Business Group Information'
AND c.legislation_code = hoi.org_information9;
/* SELECT region_2 primary_asg_state
FROM HR_LOCATIONS loc,
PER_ASSIGNMENTS_F asg,
PER_BUSINESS_GROUPS bg
-- Bug fix 1398865. Ensures one row is returned
WHERE asg.assignment_number = p_assignment_number
and asg.business_group_id = bg.business_group_id
and bg.name ||'' = p_business_group_name
and asg.effective_start_date <= p_adjustment_date
AND asg.effective_end_date >= trunc(p_adjustment_date,'Y')
and asg.primary_flag = 'Y'
and asg.location_id = loc.location_id
and loc.region_2 = p_state_abbrev;
SELECT decode(nvl(asg.work_at_home, 'N'),
'N' , loc.region_2,
addr.region_2) primary_asg_state
FROM HR_LOCATIONS loc,
PER_ASSIGNMENTS_F asg,
PER_BUSINESS_GROUPS bg,
PER_ADDRESSES addr
-- Bug fix 1398865. Ensures one row is returned
WHERE asg.assignment_number = p_assignment_number
and asg.business_group_id = bg.business_group_id
and bg.name ||'' = p_business_group_name
and asg.effective_start_date <= p_adjustment_date
AND asg.effective_end_date >= trunc(p_adjustment_date,'Y')
and asg.primary_flag = 'Y'
and asg.location_id = loc.location_id
-- and loc.region_2 = p_state_abbrev,
and asg.person_id = addr.person_id
and addr.primary_flag = 'Y'
and p_adjustment_date between addr.date_from and
nvl(addr.date_to,to_date('31-12-4712','dd-mm-yyyy'));
select decode(hoi.org_information5,
NULL,'NOT_ENTERED',
hoi.org_information5)
from hr_organization_information hoi
where hoi.organization_id = p_tax_unit_id
and hoi.org_information_context = 'Federal Tax Rules'
;
SELECT sui_jurisdiction_code,
pus.state_abbrev,
fed.fit_exempt,
fed.futa_tax_exempt,
fed.medicare_tax_exempt,
fed.ss_tax_exempt,
nvl(fed.FED_INFORMATION2,'N')
FROM pay_us_emp_fed_tax_rules_f fed,
PER_ASSIGNMENTS_F a,
PER_BUSINESS_GROUPS bg,
pay_us_states pus
WHERE fed.assignment_id = a.assignment_id
and a.assignment_number = p_assignment_number
and a.business_group_id = bg.business_group_id
and bg.name ||'' = p_business_group_name
and p_adjustment_date between fed.effective_start_date
and fed.effective_end_date
and p_adjustment_date BETWEEN
a.effective_start_date and a.effective_end_date
and fed.sui_state_code = pus.state_code
;
select hl.meaning
from hr_organization_information hoi,
hr_lookups hl
where hoi.organization_id = p_tax_unit_id
and hoi.org_information_context = 'Federal Tax Rules'
and hoi.org_information3 = hl.LOOKUP_CODE
and hl.lookup_type = 'US_SELF_ADJUST_METHOD';
select hl.meaning
from hr_organization_information hoi,
hr_lookups hl
where hoi.organization_id = p_tax_unit_id
and hoi.org_information_context = 'Federal Tax Rules'
and hoi.org_information1 = hl.LOOKUP_CODE
and hl.lookup_type = 'US_SELF_ADJUST_METHOD';
select hl.meaning
from hr_organization_information hoi,
hr_lookups hl
where hoi.organization_id = p_tax_unit_id
and hoi.org_information_context = 'Federal Tax Rules'
and hoi.org_information2 = hl.LOOKUP_CODE
and hl.lookup_type = 'MEDI_SELF_ADJ_CALC_METHOD';
select hl.meaning
from hr_organization_information hoi,
hr_lookups hl
where hoi.organization_id = p_tax_unit_id
and hoi.org_information_context = 'State Tax Rules'
and hoi.org_information1 = p_state_abbrev
and hoi.org_information5 = hl.LOOKUP_CODE
and hl.lookup_type = 'US_SELF_ADJUST_METHOD';
select hl.meaning
from hr_organization_information hoi,
hr_lookups hl
where hoi.organization_id = p_tax_unit_id
and hoi.org_information_context = 'State Tax Rules2'
and hoi.org_information1 = p_state_abbrev
and hoi.org_information5 = hl.LOOKUP_CODE
and hl.lookup_type = 'US_SELF_ADJUST_METHOD';
select hl.meaning
from hr_organization_information hoi,
hr_lookups hl
where hoi.organization_id = p_tax_unit_id
and hoi.org_information_context = 'State Tax Rules'
and hoi.org_information1 = p_state_abbrev
and hoi.org_information4 = hl.LOOKUP_CODE --bug 3887144
-- and hoi.org_information5 = hl.LOOKUP_CODE
and hl.lookup_type = 'US_SELF_ADJUST_METHOD';
SELECT sta.sit_exempt,
sta.sdi_exempt,
NVL(sta.STA_INFORMATION5,'N'),
sta.sui_exempt
FROM pay_us_emp_state_tax_rules_f sta,
PER_ASSIGNMENTS_F a,
PER_BUSINESS_GROUPS bg,
pay_us_states pus
WHERE sta.assignment_id = a.assignment_id
and a.assignment_number = p_assignment_number
and a.business_group_id = bg.business_group_id
and bg.name ||'' = p_business_group_name
and p_adjustment_date between sta.effective_start_date
and sta.effective_end_date
and p_adjustment_date BETWEEN
a.effective_start_date and a.effective_end_date
and sta.jurisdiction_code = (substr(cp_jurisdiction_code,0,2) || '-000-0000')
;
SELECT cnt.lit_exempt,
NVL(cnt.sd_exempt,'N')
FROM pay_us_emp_county_tax_rules_f cnt,
PER_ASSIGNMENTS_F a,
PER_BUSINESS_GROUPS bg,
pay_us_states pus
WHERE cnt.assignment_id = a.assignment_id
and a.assignment_number = p_assignment_number
and a.business_group_id = bg.business_group_id
and bg.name ||'' = p_business_group_name
and p_adjustment_date between cnt.effective_start_date
and cnt.effective_end_date
and p_adjustment_date BETWEEN
a.effective_start_date and a.effective_end_date
and cnt.jurisdiction_code = (substr(cp_jurisdiction_code,0,6) || '-0000')
;
SELECT cty.lit_exempt,
NVL(cty.sd_exempt,'N')
FROM pay_us_emp_city_tax_rules_f cty,
PER_ASSIGNMENTS_F a,
PER_BUSINESS_GROUPS bg,
pay_us_states pus
WHERE cty.assignment_id = a.assignment_id
and a.assignment_number = p_assignment_number
and a.business_group_id = bg.business_group_id
and bg.name ||'' = p_business_group_name
and p_adjustment_date between cty.effective_start_date
and cty.effective_end_date
and p_adjustment_date BETWEEN
a.effective_start_date and a.effective_end_date
and cty.jurisdiction_code = cp_jurisdiction_code
;
SELECT nvl(target.automate_sui_wage_credit,'N')
FROM hr_tax_units_v target
WHERE target.tax_unit_id = p_tax_unit_id;
SELECT pdb.defined_balance_id
FROM pay_balance_types pbt,
pay_balance_dimensions pbd,
pay_defined_balances pdb
WHERE pbt.balance_name in ('SUI ER Taxable','SUI EE Taxable')
AND pbt.balance_type_id=pdb.balance_type_id
AND pbd.dimension_name = 'Person within Government Reporting Entity Year to Date'
AND pbd.balance_dimension_id=pdb.balance_dimension_id
ORDER BY pbt.balance_name;
SELECT parameter_value
FROM pay_action_parameters
WHERE parameter_name = 'WAGE_ACCUMULATION_ENABLED';
SELECT wage_exempt
FROM pay_us_emp_fed_tax_rules_f fed,
per_assignments_f a,
per_business_groups bg
WHERE fed.assignment_id = a.assignment_id
AND a.assignment_number = p_assignment_number
AND a.business_group_id = bg.business_group_id
AND bg.name ||'' = p_business_group_name
AND p_adjustment_date BETWEEN fed.effective_start_date
AND fed.effective_end_date
AND p_adjustment_date BETWEEN a.effective_start_date
AND a.effective_end_date;
SELECT 'Y'
FROM pay_us_city_school_dsts psd
WHERE psd.state_code = substr (cp_school_jur_code,1,2)
AND psd.school_dst_code = substr (cp_school_jur_code,4,5)
AND ROWNUM = 1;
SELECT 'Y'
FROM pay_us_county_school_dsts psd
WHERE psd.state_code = substr (cp_school_jur_code,1,2)
AND psd.school_dst_code = substr (cp_school_jur_code,4,5)
AND ROWNUM = 1;
INSERT INTO fnd_sessions(session_id, effective_date)
SELECT USERENV('sessionid'), SYSDATE
FROM DUAL
WHERE NOT EXISTS (SELECT '1'
FROM fnd_sessions
WHERE session_id = USERENV('sessionid'));
SELECT a.assignment_id,
a.business_group_id,
a.payroll_id
INTO l_assignment_id,
l_bg_id,
l_payroll_id
FROM per_business_groups bg,
per_assignments_f a,
hr_soft_coding_keyflex hsk
WHERE a.assignment_number = p_assignment_number
and a.business_group_id = bg.business_group_id
and bg.name ||'' = p_business_group_name
and p_adjustment_date BETWEEN
a.effective_start_date AND a.effective_end_date
/*Added for bug 7692482*/
and a.assignment_type='E'
and a.soft_coding_keyflex_id = hsk.soft_coding_keyflex_id
and hsk.segment1 = p_tax_unit_id
;
Select PUS.state_abbrev, -- state
nvl(ADDR.add_information19, ADDR.region_1), -- County
nvl(ADDR.add_information18, ADDR.town_or_city), -- City
nvl(ADDR.add_information20, ADDR.postal_code)
INTO l_per_state_abbrev,
l_per_county,
l_per_city,
l_per_zip_code
FROM per_addresses ADDR
,per_all_assignments_f ASSIGN
,pay_us_states PUS
where p_adjustment_date BETWEEN ASSIGN.effective_start_date
AND ASSIGN.effective_end_date
and ASSIGN.assignment_id = l_assignment_id
and ADDR.person_id = ASSIGN.person_id
and ADDR.primary_flag = 'Y'
and p_adjustment_date BETWEEN nvl(ADDR.date_from,p_adjustment_date)
AND nvl(ADDR.date_to, p_adjustment_date)
and PUS.state_abbrev = nvl(ADDR.add_information17, ADDR.region_2);
SELECT PUS.state_abbrev, -- State
nvl(HRLOC.loc_information19,HRLOC.region_1), -- County
nvl(HRLOC.loc_information18,HRLOC.town_or_city), -- City
nvl(HRLOC.loc_information20,HRLOC.postal_code)
INTO l_loc_state_abbrev,
l_loc_county,
l_loc_city,
l_loc_zip_code
FROM hr_locations HRLOC
, hr_soft_coding_keyflex HRSCKF
, per_all_assignments_f ASSIGN
, pay_us_states PUS
where p_adjustment_date BETWEEN ASSIGN.effective_start_date
AND ASSIGN.effective_end_date
and ASSIGN.assignment_id = l_assignment_id
and ASSIGN.soft_coding_keyflex_id = HRSCKF.soft_coding_keyflex_id
and nvl(HRSCKF.segment18,ASSIGN.location_id) = HRLOC.location_id
and PUS.state_abbrev = nvl(HRLOC.loc_information17,HRLOC.region_2);
select defined_balance_id
into l_ss_er_w11_def_bal_id
from pay_balance_types bt,
pay_balance_dimensions bd,
pay_defined_balances db
where bt.balance_name = 'SS ER W11 Taxable'
and bd.dimension_name = 'Person within Government Reporting Entity Year to Date'
and bd.legislation_code = 'US'
and db.balance_type_id = bt.balance_type_id
and bd.balance_dimension_id = db.balance_dimension_id;
SELECT consolidation_set_id
INTO l_consolidation_set_id
FROM PAY_CONSOLIDATION_SETS
WHERE consolidation_set_name = p_consolidation_set
and business_group_id = l_bg_id
;
/*First check if the Automate SUI Wage Credit is selected or not.If Automate SUI
Wage Credit is opted, then use the Person SUI Taxable YTD of all the states.Otherwise
use Person SUI Taxable YTD of current state alone. Since Minnesota does not give
credit to SUI paid in other states, Automate SUI Wage Credit Functionality will not
be considered for Mineesota(State Code - 24)*/
open get_automate_sui_wage_credit;
select parameter_value
into l_run_route
from PAY_ACTION_PARAMETERS
where parameter_name = 'RUN_ROUTE';
select count(0)
into l_asg_exists
from per_assignments_f
where assignment_id = l_assignment_id
and l_end_of_year_date between effective_start_date and effective_end_date;
select max(effective_end_date)
into l_balance_fetch_date
from per_assignments_f
where assignment_id = l_assignment_id;