[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
pay_element_entries_f can be updated
after the insert_element_entry() api
110.3 sbilling 15-Jul-98 Major changes. Added new function
process_limits() to do limit
processing on limit based taxes
(eg. Medicare_EE/Medicare_ER). The limits
for taxes are fetched from the tables
PAY_US_FEDERAL_TAX_INFO_F/
PAY_US_STATE_TAX_INFO_F for federal
and state taxes respectively. Also
added the extra fields:
- futa_er
- sui_er
- sdi_er
- sch_dist_wh_ee
- sch_dist_jur
to the corresponding form PAYWSTBA.
These are used to handle the ER components
of the adjustments and to allow school
district adjustments to be made. NB. The
chosen school district's jurisdiction is
passed down to
create_tax_balance_adjustment() via the
p_sch_dist_jur parameter.
Also note, the taxable balances for all
taxes where limit processing may apply are
fetched before any limit processing is
done. The values are stored in global
parameters.
08-apr99 djoshi Verfied and converted for Canonical
Complience of Date
19-apr99 alogue Fix to previous change.
115.1 21-apr-99 scgrant Multi-radix changes.
115.7 07-JUL-99 RAMURTHY Incorporated functional fixes
from 10.7.
115.8 19-AUG-99 KKAWOL Support for date UOM 'D'. 'D_DDMONYY','D_DDMONYYYY'
'D_DDMMYY','D_DDMMYYYY','D_MMDDYY','D_MMDDYYYY' do
not exist any more.
115.9 22-NOV-1999 MHANDA Added fed_information_category = 401K
in the where clause for cursor for
pay_us_federal_tax_info_f.
115.10 27-DEC-1999 tclewis modifed csr_chk_taxability to accept
jurisdiction code as a parameter. This
fixes a problem with checkin the taxability
rules for city and county records.
I also added code the check state level
taxablility rules if no rows are returned
on the city or county level.
115.11 15-feb-2000 tclewis bugs 983727 and 1151395. Modified csr_sui_geocode
to check business_group_id on assignment record
so that only one row is returned (in the case of
multiple business groups). Removed check of
gross_pay <> 0 when validating jurisdiction level
needed. Added a check for a valid city jurisdiction
code before making an adjustment to the
city_subject_wk balance.
112.12 24-MAY-2000 tclewis Implemented the tax_exists functionality for city
and county. Also added a check for tax_exists
before processing elements city_subject_wk and
count_subbject_wk, when gross pay is greated than
0.
115.16 13-sep-2000 irgonzal Bug Fix 1398865. Modified csr_sdi_check cursor
to check business_group_id on assignment record
to ensure only one row is returned when same
assignment number exist in different business groups.
115.17 11-jan-2001 tclewis bug fix 1569312. SUI and SDI taxable were only
being adjusted when an adjustment abount was
entered for SUI / SDI liablity. I removed the
code (if statements) where we check if l_sui_er /
l_sdi_er (or ee) were eneterd before we process
the adjustment.
115.18 05-OCT-2005 sackumar 4650486 Removed GSCC Errors and Warnings
-------------------------------------------------------------------------- */
-- global variables
g_classification VARCHAR2(80);
hr_entry_api.insert_element_entry(
p_effective_start_date => l_adjustment_date,
p_effective_end_date => l_dummy_date,
p_element_entry_id => l_element_entry_id,
p_assignment_id => p_assignment_id,
p_element_link_id => p_element_link_id,
p_creator_type => 'B', -- (B)alance Adjustment
p_entry_type => 'B', -- (B)alance Adjustment
p_num_entry_values => p_num_entry_values,
p_input_value_id_tbl => p_input_value_id_tbl,
p_entry_value_tbl => p_entry_value_tbl );
UPDATE PAY_ELEMENT_ENTRIES_F
SET balance_adj_cost_flag = p_cost
WHERE element_entry_id = l_element_entry_id
and effective_start_date = l_adjustment_date
and effective_end_date = l_dummy_date
;
UPDATE PAY_RUN_RESULTS
SET source_id = p_original_entry_id
WHERE source_id = l_element_entry_id
and source_type = 'E';
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
;
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
;
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
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
effective_start_date AND effective_end_date
;
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 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 p_adjustment_date BETWEEN
asg.effective_start_date AND asg.effective_end_date
and asg.primary_flag = 'Y'
and asg.location_id = loc.location_id
;
SELECT sui_jurisdiction_code,
pus.state_abbrev
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 a.assignment_id,
a.business_group_id
INTO l_assignment_id,
l_bg_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 a.assignment_id,
a.business_group_id
INTO l_assignment_id,
l_bg_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
;
UPDATE pay_payroll_actions
SET legislative_parameters = l_grp_key
WHERE payroll_action_id = g_tax_adj_pactid_tbl(l_counter);
UPDATE pay_payroll_actions
SET legislative_parameters = l_grp_key
WHERE payroll_action_id = g_tax_adj_pactid_tbl(l_counter);