[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT vat.taxable_basis,
vat.tax_code,
vat.enabled_flag,
vat.start_date,
vat.end_date,
rate.tax,
rate.tax_regime_code,
rate.content_owner_id
FROM ar_vat_tax_all vat, zx_rates_b rate
WHERE vat.taxable_basis IN ('AFTER_EPD', 'QUANTITY')
AND vat.tax_class = 'O'
AND vat.global_attribute_category is null
AND vat.tax_type <> 'TAX_GROUP'
AND vat.enabled_flag = 'Y'
AND vat.vat_tax_id = rate.tax_rate_id
ORDER BY vat.end_date nulls first;
SELECT tax_rel_upg.tax_group_id,
tax_rel_upg.tax_group_code,
zx_par_rate.tax, -- 8726049
tax_rel_upg.parent_precedence,
zx_par_rate.tax_regime_code,
tax_rel_upg.child_tax_code,
tax_rel_upg.child_precedence,
tax_rel_upg.child_regime_code,
tax_rel_upg.child_taxable_basis,
tax_rel_upg.branch_flag,
tax_rel_upg.content_owner_id,
grp.enabled_flag, -- for create_formula() and create_rules()
grp.start_date, -- for create_rules
grp.end_date, -- for create_rules
grp.tax_condition_id,
grp.tax_exception_id,
grp.tax_group_id,
zx_rate.tax_regime_code,
zx_rate.tax
FROM zx_tax_relations_t tax_rel_upg, ar_tax_group_codes_all grp, zx_rates_b zx_rate,
zx_rates_b zx_par_rate -- 8726049
WHERE tax_rel_upg.tax_group_id NOT IN (SELECT tax_group_id
FROM zx_tax_relations_t
WHERE TRUNC(child_precedence) <> child_precedence )
AND grp.compounding_precedence is not null
AND grp.tax_group_id = tax_rel_upg.tax_group_id
AND grp.tax_code_id = decode(grp.tax_group_type
,'AR',zx_rate.tax_rate_id
,NVL(zx_rate.source_id, zx_rate.tax_rate_id)
)
AND grp.tax_code_id = tax_rel_upg.child_tax_code_id --* new condition added
AND tax_rel_upg.parent_tax_code_id = decode(grp.tax_group_type
,'AR',zx_par_rate.tax_rate_id
,NVL(zx_par_rate.source_id, zx_par_rate.tax_rate_id)
) -- 8726049
ORDER BY tax_rel_upg.tax_group_id, child_precedence DESC;
SELECT tax_rel_upg.tax_group_id,
tax_rel_upg.tax_group_code,
zx_par_rate.tax, -- 8726049
tax_rel_upg.Parent_precedence,
zx_par_rate.tax_regime_code,
tax_rel_upg.Child_tax_code,
tax_rel_upg.Child_precedence,
tax_rel_upg.Child_regime_code,
tax_rel_upg.Child_Taxable_basis,
tax_rel_upg.branch_flag,
tax_rel_upg.content_owner_id,
grp.enabled_flag,
grp.start_date,
grp.end_date,
grp.tax_condition_id,
grp.tax_exception_id,
grp.tax_group_id,
zx_rate.tax_regime_code,
zx_rate.tax
FROM zx_tax_relations_t tax_rel_upg, ar_tax_group_codes_all grp, zx_rates_b zx_rate,
zx_rates_b zx_par_rate -- 8726049
WHERE tax_rel_upg.tax_group_id IN (SELECT tax_group_id
FROM zx_tax_relations_t
WHERE child_precedence > TRUNC(child_precedence) )
AND grp.compounding_precedence is not null
AND grp.tax_group_id = tax_rel_upg.tax_group_id
AND grp.tax_code_id = decode(grp.tax_group_type
,'AR',zx_rate.tax_rate_id
,NVL(zx_rate.source_id, zx_rate.tax_rate_id)
)
AND grp.tax_code_id = tax_rel_upg.child_tax_code_id
AND tax_rel_upg.parent_tax_code_id = decode(grp.tax_group_type
,'AR',zx_par_rate.tax_rate_id
,NVL(zx_par_rate.source_id, zx_par_rate.tax_rate_id)
) -- 8726049
ORDER BY tax_rel_upg.tax_group_id, child_precedence DESC;
SELECT group_id ,
tax_group_code ,
Parent_tax_code ,
Parent_precedence ,
Parent_regime_code ,
Child_tax_id ,
Child_tax_code ,
Child_precedence ,
Child_regime_code ,
Child_Taxable_basis ,
branch ,
content_owner_id
FROM zx_tax_relations_t
-- START WITH Parent_Regime_code IS NULL --* changed crsr
CONNECT BY PRIOR child_tax_code = parent_Tax_code
ORDER BY group_id, child_tax_code, child_precedence DESC;
arp_util_tax.debug('CASE 1: before select');
SELECT condition_group_id, condition_group_code
INTO l_condition_grp_id, l_condition_grp_cd
FROM zx_condition_groups_b
WHERE condition_group_code = l_tax_code;
arp_util_tax.debug('case 3:bef select');
SELECT vat.tax_constraint_id
INTO l_tax_constraint_id
FROM ar_vat_tax_all vat
WHERE vat.vat_tax_id = l_tax_group_id;
SELECT condition_group_id, condition_group_code
INTO l_condition_grp_id, l_condition_grp_cd
FROM zx_condition_groups_b
WHERE condition_group_code in (Decode(l_tax_constraint_id,NULL,SUBSTRB(l_tax_group_code,1,45)||'-'||'XOP',
substrb(l_tax_group_code,1,40)||'~'||l_tax_constraint_id)
,Decode(l_tax_constraint_id,NULL,l_tax_group_code,
substrb(l_tax_group_code,1,40)||'~'||l_tax_constraint_id)
);
arp_util_tax.debug('case 4:before select of cond grp');
SELECT vat.tax_constraint_id
INTO l_tax_constraint_id
FROM ar_vat_tax_all vat
WHERE vat.vat_tax_id = l_tax_group_id;
SELECT condition_group_id, condition_group_code
INTO l_condition_grp_id, l_condition_grp_cd
FROM zx_condition_groups_b
WHERE condition_group_code in (Decode(l_tax_constraint_id,NULL,SUBSTRB(l_tax_group_code,1,45)||'-'||'XOP',
substrb(l_tax_group_code,1,40)||'~'||l_tax_constraint_id)
,Decode(l_tax_constraint_id,NULL,l_tax_group_code,
substrb(l_tax_group_code,1,40)||'~'||l_tax_constraint_id)
);
UPDATE ZX_TAXES_B T
SET TAXABLE_BASIS_RULE_FLAG = 'Y'
WHERE RECORD_TYPE_CODE = 'MIGRATED'
AND EXISTS (SELECT 1 FROM ZX_RULES_B R
WHERE R.TAX_REGIME_CODE = T.TAX_REGIME_CODE
AND R.TAX = T.TAX
AND R.CONTENT_OWNER_ID = T.CONTENT_OWNER_ID
AND R.SERVICE_TYPE_CODE = 'DET_TAXABLE_BASIS'
AND R.RECORD_TYPE_CODE = 'MIGRATED'
AND R.ENABLED_FLAG = 'Y');
UPDATE ZX_TAXES_B T
SET TAXABLE_BASIS_RULE_FLAG = 'Y'
WHERE RECORD_TYPE_CODE = 'MIGRATED'
AND EXISTS (SELECT 1 FROM ZX_RULES_B R
WHERE R.TAX_REGIME_CODE = T.TAX_REGIME_CODE
AND R.TAX = T.TAX
AND R.CONTENT_OWNER_ID = T.CONTENT_OWNER_ID
AND R.SERVICE_TYPE_CODE = 'DET_TAXABLE_BASIS'
AND R.RECORD_TYPE_CODE = 'MIGRATED'
AND R.ENABLED_FLAG = 'Y');
INSERT INTO zx_formula_b_tmp (
formula_type_code,
formula_code,
tax_regime_code,
tax,
effective_from,
effective_to,
enabled_flag,
taxable_basis_type_code,
record_type_code,
base_rate_modifier,
cash_discount_appl_flag,
volume_discount_appl_flag,
trading_discount_appl_flag,
transfer_charge_appl_flag,
transport_charge_appl_flag,
insurance_charge_appl_flag,
other_charge_appl_flag,
formula_id,
content_owner_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_login_id,
object_version_number)
SELECT
'TAXABLE_BASIS',
p_formula_code, --tax_group_code||'_'||tax_code||'_TB'
p_tax_regime_code,
p_tax_code,
p_start_date,
p_end_date,
p_enabled_flag,
DECODE(p_taxable_basis,'PRIOR_TAX','PRIOR_TAX','LINE_AMOUNT'), --Bug Fix 5691957
'MIGRATED',
0, -- bug6718736
DECODE(p_taxable_basis, 'AFTER_EPD','Y','N'),
p_discount_flag,
p_discount_flag,
p_charge_flag,
p_charge_flag,
p_charge_flag,
p_charge_flag,
zx_formula_b_s.NEXTVAL l_formula_id,
p_content_owner_id,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.conc_login_id,
fnd_global.conc_request_id, --Request Id
fnd_global.prog_appl_id, --Program Application ID
fnd_global.conc_program_id, --Program Id
fnd_global.conc_login_id, --Program Login ID
1
FROM DUAL
WHERE
--Re-runnability
NOT EXISTS ( SELECT 1
FROM zx_formula_b
WHERE SUBSTRB(formula_code,1,24) = SUBSTRB(p_formula_code,1,24)
AND (effective_from BETWEEN p_start_date and nvl(p_end_date,SYSDATE)
OR NVL(effective_to,sysdate) BETWEEN p_start_date and nvl(p_end_date,sysdate))
AND tax_regime_code = p_tax_regime_code
AND content_owner_id = p_content_owner_id
AND enabled_flag = p_enabled_flag
);
INSERT INTO zx_formula_tl (
formula_id,
formula_name,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
language,
source_lang
)
SELECT
formula_id,
CASE WHEN formula_code = UPPER(formula_code)
THEN Initcap(formula_code)
ELSE
formula_code
END,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.conc_login_id,
l.language_code,
userenv('LANG')
FROM fnd_languages l, zx_formula_b formula
WHERE l.installed_flag IN ('I', 'B')
AND formula.record_type_code = 'MIGRATED'
AND formula.formula_code = p_formula_code
AND NOT EXISTS(SELECT NULL
FROM zx_formula_tl t
WHERE t.formula_id = formula.formula_id
AND t.language = l.language_code);
SELECT formula_id
INTO l_formula_id
FROM zx_formula_b
WHERE content_owner_id=p_content_owner_id
AND SUBSTRB(formula_code,1,24) = SUBSTRB(p_formula_code,1,24)
AND (effective_from BETWEEN p_start_date and nvl(p_end_date,SYSDATE)
OR NVL(effective_to,sysdate) BETWEEN p_start_date and nvl(p_end_date,sysdate))
AND tax_regime_code=p_tax_regime_code
AND enabled_flag = p_enabled_flag;
INSERT INTO zx_formula_details (
formula_detail_id,
formula_id,
compounding_tax_regime_code,
compounding_tax,
compounding_type_code,
record_type_code,
creation_date,
last_update_date,
created_by,
last_updated_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_login_id,
object_version_number
)
SELECT
zx_formula_details_s.NEXTVAL,
l_formula_id,
p_parent_regime_code,
p_parent_tax_code,
'ADD',
'MIGRATED',
SYSDATE,
SYSDATE,
fnd_global.user_id,
fnd_global.user_id,
fnd_global.conc_login_id,
fnd_global.conc_request_id, --Request Id
fnd_global.prog_appl_id, --Program Application ID
fnd_global.conc_program_id, --Program Id
fnd_global.conc_login_id, --Program Login ID
1
FROM zx_tax_relations_t t
WHERE t.child_regime_code = p_child_regime_code
AND t.child_tax_code = p_child_tax_code
AND t.tax_group_id = p_group_id
AND NOT EXISTS (SELECT 1
FROM zx_formula_details, zx_formula_b
WHERE zx_formula_details.formula_id = l_formula_id
AND compounding_tax_regime_code = p_parent_regime_code
AND compounding_tax = p_parent_tax_code
--AND contains(zx_formula_b.formula_code, t.tax_group_code) > 0
)
AND rownum = 1;
INSERT INTO zx_formula_details (
formula_detail_id,
formula_id,
compounding_tax,
compounding_tax_regime_code,
compounding_type_code,
record_type_code,
creation_date,
last_update_date,
created_by,
last_updated_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_login_id,
object_version_number
)
SELECT zx_formula_details_s.NEXTVAL,
l_formula_id, -- Bug 8429806
p_parent_tax_code, -- 8726049
parent_regime_code,
'ADD',
'MIGRATED',
SYSDATE,
SYSDATE,
fnd_global.user_id,
fnd_global.user_id,
fnd_global.conc_login_id,
fnd_global.conc_request_id, --Request Id
fnd_global.prog_appl_id, --Program Application ID
fnd_global.conc_program_id, --Program Id
fnd_global.conc_login_id, --Program Login ID
1
FROM zx_tax_relations_t t
WHERE t.child_regime_code = p_child_regime_code
AND t.child_tax_code = p_child_tax_code
AND t.tax_group_id = p_group_id
AND NOT EXISTS (SELECT 1
FROM zx_formula_details, zx_formula_b
WHERE zx_formula_details.formula_id = l_formula_id -- Bug 8429806
AND compounding_tax_regime_code = p_parent_regime_code
AND compounding_tax = p_parent_tax_code
-- AND contains(zx_formula_b.formula_code, t.tax_group_code) > 0
)
and rownum = 1;
SELECT DECODE(SIGN(LENGTHB('O_TB_' || p_tax) - 30),
1,
SUBSTRB('O_TB_' || p_tax,1,24)||ZX_MIGRATE_UTIL.GET_NEXT_SEQID('ZX_RULES_B_S'),
'O_TB_' || p_tax)
INTO l_tax_rule_code
FROM DUAL;
INSERT INTO zx_rules_b_tmp
(tax_rule_code,
tax,
tax_regime_code,
service_type_code,
application_id,
recovery_type_code,
priority,
system_default_flag,
effective_from,
effective_to,
enabled_flag,
record_type_code,
det_factor_templ_code,
content_owner_id,
tax_rule_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_login_id,
object_version_number)
SELECT
l_tax_rule_code,
p_tax,
p_tax_regime_code,
'DET_TAXABLE_BASIS',
NULL,
NULL,
1,
'N', -- Bug 4590290
p_effective_from,
p_effective_to,
p_enabled_flag,
'MIGRATED',
'STCC',
p_content_owner_id,
zx_rules_b_s.NEXTVAL,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.conc_login_id,
fnd_global.conc_request_id, --Request Id
fnd_global.prog_appl_id, --Program Application ID
fnd_global.conc_program_id, --Program Id
fnd_global.conc_login_id, --Program Login ID
1
FROM DUAL
WHERE
--Re-runnability
NOT EXISTS (SELECT 1
FROM zx_rules_B
WHERE substrb(tax_rule_code,1,24) = (SELECT DECODE(SIGN(LENGTHB('O_TB_' || p_tax) - 30),
1,
SUBSTRB('O_TB_' || p_tax,1,24),
SUBSTRB('O_TB_' || p_tax,1,24))
FROM DUAL)
AND content_owner_id = p_content_owner_id
AND tax_regime_code = p_tax_regime_code
AND tax = p_tax
AND (effective_from BETWEEN p_effective_from AND NVL(p_effective_to,SYSDATE)
OR
NVL(effective_to,SYSDATE) BETWEEN p_effective_from AND NVL(p_effective_to,SYSDATE)
)
AND enabled_flag = 'Y'
);
INSERT INTO zx_rules_tl (
tax_rule_id,
tax_rule_name,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
language,
source_lang
)
SELECT
tax_rule_id,
CASE WHEN tax_rule_code = UPPER(tax_rule_code)
THEN Initcap(tax_rule_code)
ELSE
tax_rule_code
END,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.conc_login_id,
l.language_code,
userenv('LANG')
FROM fnd_languages l, zx_rules_b rules
WHERE l.installed_flag IN ('I', 'B')
AND rules.record_type_code = 'MIGRATED'
AND rules.tax_rule_code = l_tax_rule_code
AND NOT EXISTS (SELECT NULL
FROM zx_rules_tl t
WHERE t.tax_rule_id = rules.tax_rule_id
AND t.language = l.language_code);
SELECT tax_rule_id
INTO l_tax_rule_id
FROM zx_rules_b
WHERE substrb(tax_rule_code,1,24) = substrb(l_tax_rule_code,1,24)
AND tax_regime_code = p_tax_regime_code
AND tax = p_tax
AND content_owner_id = p_content_owner_id
AND service_type_code = 'DET_TAXABLE_BASIS'
AND enabled_flag = p_enabled_flag
AND (effective_from BETWEEN p_effective_from and nvl(p_effective_to,SYSDATE)
OR NVL(effective_to,sysdate) BETWEEN p_effective_from and nvl(p_effective_to,sysdate));
INSERT INTO zx_process_results
(condition_group_code,
priority,
result_type_code,
tax_status_code,
numeric_result,
alphanumeric_result,
status_result,
rate_result,
legal_message_code,
min_tax_amt,
max_tax_amt,
min_taxable_basis,
max_taxable_basis,
min_tax_rate,
max_tax_rate,
enabled_flag,
allow_exemptions_flag,
allow_exceptions_flag,
record_type_code,
result_api,
result_id,
content_owner_id,
condition_group_id,
tax_rule_id,
condition_set_id,
exception_set_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_login_id,
object_version_number)
SELECT
p_condition_grp_cd,
1,
'CODE',
NULL,
NULL,
p_alphanumeric_result, -- STANDARD_TB_DISCOUNT,STANDARD_QUANTITY or TAX_GROUP_CODE||'_'||TAX_CODE||'_TB'
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
p_enabled_flag, -- also used in insert of zx_rules_b
'N',
'N',
'MIGRATED',
NULL,
zx_process_results_s.NEXTVAL,
p_content_owner_id, -- also used in insert of zx_rules_b also
p_condition_group_id,
l_tax_rule_id, -- zx_rules_b.tax_rule_id(based on rule created above)8429806
p_tax_condition_id, --condition set id
p_tax_exception_id, --exception set id
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.conc_login_id,
fnd_global.conc_request_id, --Request Id
fnd_global.prog_appl_id, --Program Application ID
fnd_global.conc_program_id, --Program Id
fnd_global.conc_login_id, --Program Login ID
1
FROM dual
WHERE
--Re-runnability
NOT EXISTS (SELECT 1
FROM zx_process_results
WHERE zx_process_results.tax_rule_id = l_tax_rule_id --Bug 8429806
AND zx_process_results.content_owner_id = p_content_owner_id
AND zx_process_results.condition_group_code = p_condition_grp_cd
AND zx_process_results.alphanumeric_result = p_alphanumeric_result
);