[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
25-AUG-04 fusman 115.9 3847970 Changed the code to not insert
a row with status of D for Canada.
Also, if user enters N in spreadsheet
the value is deleted from table.
26-AUG-04 fusman 115.10 3855943 Added nvl when checking valid status for CA
04-AUG-05 meshah 115.11 Added AEIC for state pretax and earnings
27-DEC-05 sudedas 115.12 4591127 Changed create_taxability_rules,
transfer_tax_type_values and
create_ca_prov_taxability
15-NOV-06 rpasumar 115.13 Modified create_taxability_rules for the bug# 5652699.
*******************************************************************/
-- Package Variables
g_package VARCHAR2(100);
select taxability_rules_date_id
from pay_taxability_rules_dates
where legislation_code = cp_legislation_code
and cp_effective_date between valid_date_from
and valid_date_to;
ltt_tax_types.delete;
/* No row exists. So insert a new row with status Null. */
if lv_status = 'N' then /*lv_status Check*/
/* No row exists. So insert a new row with status Null. */
if ptt_tax_type_values(i) = 'Y' then
hr_utility.trace('No row exists and user has passed Y.
So insert a new row with status Null.');
So insert a new row with status D.');
/*the user wanted to delete it. So set the status to 'D'. */
hr_utility.trace('Active row exists. User wanted to delete'||
' it so set the status to D.');
pay_taxability_rules_api.update_taxability_rules
(p_validate => FALSE
,p_jurisdiction => p_jurisdiction
,p_tax_type => ptt_tax_types(i)
,p_tax_category => p_tax_category
,p_classification_id => p_classification_id
,p_taxability_rules_date_id => p_taxability_rule_date_id
,p_legislation_code => p_legislation_code
,p_status => lv_valid_status
);
delete from pay_taxability_rules
where legislation_code = p_legislation_code
and tax_type = ptt_tax_types(i)
and jurisdiction_code = p_jurisdiction
and classification_id = p_classification_id
and tax_category = p_tax_category
and taxability_rules_date_id = p_taxability_rule_date_id;
/* But the user wanted to insert a row.
So set the status to null. */
hr_utility.trace('In active row exists. User wanted ' ||
'to insert a row. Set the status to null.');
pay_taxability_rules_api.update_taxability_rules
(p_validate => FALSE
,p_jurisdiction => p_jurisdiction
,p_tax_type => ptt_tax_types(i)
,p_tax_category => p_tax_category
,p_classification_id => p_classification_id
,p_taxability_rules_date_id => p_taxability_rule_date_id
,p_legislation_code => p_legislation_code
,p_status => lv_valid_status
);
SELECT lookup_code
FROM hr_lookups
WHERE upper(lookup_type) = upper(ltrim(rtrim(cp_lookup_type)))
AND (
upper(lookup_code) = upper(ltrim(rtrim(cp_lookup_code)))
OR
upper(meaning) = upper(ltrim(rtrim(cp_lookup_code))));
hr_utility.trace('SELECT lookup_code FROM hr_lookups WHERE upper(lookup_type) = upper(ltrim(rtrim(cp_lookup_type))) AND upper(meaning) = upper(ltrim(rtrim(cp_lookup_code)))');
select state_code
from pay_us_states pus
where pus.state_abbrev = upper(cp_state_abbrev);
select puc.county_code
from pay_us_counties puc
where puc.state_code = cp_state_code
and upper(puc.county_name) = upper(cp_county_name);
select city_code from pay_us_city_names
where state_code = cp_state_code
and upper(city_name) = upper(cp_city_name);
select classification_name
from pay_element_classifications
where classification_id = cp_classification_id
and legislation_code = cp_legislation_code;
PROCEDURE update_taxability_rules
(p_classification_id IN NUMBER
,p_tax_category IN VARCHAR2
,p_jurisdiction IN VARCHAR2
,p_legislation_code IN VARCHAR2
,p_input_tax_type_value1 IN VARCHAR2
,p_input_tax_type_value2 IN VARCHAR2
,p_input_tax_type_value3 IN VARCHAR2
,p_input_tax_type_value4 IN VARCHAR2
,p_input_tax_type_value5 IN VARCHAR2
,p_input_tax_type_value6 IN VARCHAR2
,p_input_tax_type_value7 IN VARCHAR2
,p_input_tax_type_value8 IN VARCHAR2
,p_input_tax_type_value9 IN VARCHAR2
,p_input_tax_type_value10 IN VARCHAR2
,p_spreadsheet_identifier IN VARCHAR2
)
IS
lv_procedure VARCHAR2(72);
lv_procedure := g_package||'update_taxability_rules';
END update_taxability_rules;