[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 i.name = 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
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 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 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(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
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 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,
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,
cnt.sd_exempt
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,
cty.sd_exempt
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
;
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
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
;
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
;