[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT delimiter
FROM zx_fc_types_b
WHERE classification_type_code ='TRX_BUSINESS_CATEGORY';
INSERT ALL
INTO ZX_VALIDATION_ERRORS_GT(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
message_name,
message_text,
trx_level_type)
SELECT
lines_gt.application_id,
lines_gt.entity_code,
lines_gt.event_class_code,
lines_gt.trx_id,
lines_gt.trx_line_id,
'ZX_TRX_BIZ_FC_CODE_NOT_EXIST',
l_trx_biz_fc_code_not_exists,
lines_gt.trx_level_type
FROM zx_transaction_lines_gt lines_gt
WHERE lines_gt.trx_business_category is NOT NULL
AND NOT EXISTS
(SELECT 1
FROM
zx_evnt_cls_mappings evntmap,
jl_zz_ar_tx_att_cls_all tac,
jl_zz_ar_tx_categ_all tc,
ar_system_parameters_all asp
where
lines_gt.application_id = evntmap.application_id
and lines_gt.entity_code = evntmap.entity_code
and lines_gt.event_class_code = evntmap.event_class_code
and tac.TAX_ATTR_CLASS_TYPE = 'TRANSACTION_CLASS'
and tac.TAX_ATTR_CLASS_CODE = SUBSTR(lines_gt.trx_business_category,
INSTR(lines_gt.trx_business_category, g_delimiter, -1) +1 )
and tac.tax_category_id = tc.tax_category_id
and tc.tax_rule_set = asp.global_attribute13
and tac.enabled_flag = 'Y'
and tac.org_id = asp.org_id
and tc.org_id = asp.org_id);
INSERT ALL
INTO ZX_VALIDATION_ERRORS_GT(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
message_name,
message_text,
trx_level_type)
SELECT
lines_gt.application_id,
lines_gt.entity_code,
lines_gt.event_class_code,
lines_gt.trx_id,
lines_gt.trx_line_id,
'ZX_PRODUCT_FC_CODE_NOT_EXIST',
l_prd_fc_code_not_exists,
lines_gt.trx_level_type
FROM
zx_transaction_lines_gt lines_gt
WHERE lines_gt.product_fisc_classification is NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM
zx_evnt_cls_mappings evntmap,
FND_LOOKUPS LK,
JL_ZZ_AR_TX_FSC_CLS FSC
where
lines_gt.application_id = evntmap.application_id
and lines_gt.entity_code = evntmap.entity_code
and lines_gt.event_class_code = evntmap.event_class_code
and lk.lookup_type = 'JLZZ_AR_TX_FISCAL_CLASS_CODE'
and lk.enabled_flag = 'Y'
and lk.lookup_code = lines_gt.product_fisc_classification
and FSC.FISCAL_CLASSIFICATION_CODE = lk.LOOKUP_CODE
and fsc.enabled_Flag = 'Y'
and nvl(lk.start_date_active,lines_gt.trx_line_date) <= lines_gt.trx_line_date
and NVL(lk.END_DATE_ACTIVE,lines_gt.trx_line_date) >= lines_gt.trx_line_date
);
UPDATE ZX_TRX_HEADERS_GT Header
SET default_taxation_country =
(SELECT
decode(syspa.global_attribute13,
'ARGENTINA', 'AR',
'COLOMBIA', 'CO',
'BRAZIL', 'BR',
NULL)
FROM ar_system_parameters_all syspa
WHERE org_id = Header.internal_organization_id
AND global_attribute_category like 'JL%')
WHERE Header.default_taxation_country is NULL;
select count(*) into l_tax_lines_count
from zx_import_tax_lines_gt;
USING (SELECT rates.tax_regime_code tax_regime_code,
rates.tax tax,
TaxLines.trx_id trx_id
FROM
ZX_IMPORT_TAX_LINES_GT TaxLines,
ZX_TRX_HEADERS_GT Header,
AR_VAT_TAX_ALL_B rates
WHERE
TaxLines.tax_rate_code = rates.tax_code(+)
AND Header.trx_date between nvl(rates.start_date,Header.trx_date)
and nvl(rates.end_date,Header.trx_date)
AND TaxLines.application_id = Header.application_id
AND TaxLines.entity_code = Header.entity_code
AND TaxLines.event_class_code = Header.event_class_code
AND TaxLines.trx_id = Header.trx_id
) Temp
ON ( TaxLines.trx_id = Temp.trx_id)
WHEN MATCHED THEN
UPDATE SET
tax_regime_code = nvl(TaxLines.tax_regime_code,
Temp.tax_regime_code),
tax = nvl(TaxLines.tax,Temp.tax)
WHEN NOT MATCHED THEN
INSERT(tax) VALUES(NULL);
USING (SELECT Status.tax_status_code tax_status_code,
TaxLines.trx_id trx_id
FROM
ZX_IMPORT_TAX_LINES_GT TaxLines,
ZX_STATUS_B Status,
ZX_TRX_HEADERS_GT Header
WHERE
Status.tax_regime_code = TaxLines.tax_regime_code
AND Status.tax = TaxLines.tax
AND Status.default_status_flag = 'Y'
AND Header.trx_date between Status.default_flg_effective_from
AND nvl(Status.default_flg_effective_to, Header.trx_date)
AND TaxLines.application_id = Header.application_id
AND TaxLines.entity_code = Header.entity_code
AND TaxLines.event_class_code = Header.event_class_code
AND TaxLines.trx_id = Header.trx_id
) Temp
ON ( TaxLines.trx_id = Temp.trx_id)
WHEN MATCHED THEN
UPDATE SET
tax_status_code = nvl(TaxLines.tax_status_code,
Temp.tax_status_code)
WHEN NOT MATCHED THEN
INSERT(tax) VALUES(NULL);
/* Replaced update statement with Merge statement
UPDATE ZX_IMPORT_TAX_LINES_GT TaxLines
SET tax_status_code =
(SELECT tax_status_code
FROM
ZX_STATUS_B Status,
ZX_TRX_HEADERS_GT Header
WHERE
Status.tax_regime_code = TaxLines.tax_regime_code
AND Status.tax = TaxLines.tax
AND Status.default_status_flag = 'Y'
AND Header.trx_date between Status.default_flg_effective_from
AND nvl(Status.default_flg_effective_to, Header.trx_date)
AND TaxLines.application_id = Header.application_id
AND TaxLines.entity_code = Header.entity_code
AND TaxLines.event_class_code = Header.event_class_code
AND TaxLines.trx_id = Header.trx_id
)
WHERE tax_status_code is NULL;
USING (SELECT Rates.tax_code ,
Rates.vat_tax_id ,
Rates.tax_rate ,
TaxLines.trx_id
FROM
AR_VAT_TAX_ALL_B Rates,
ZX_IMPORT_TAX_LINES_GT TaxLines,
ZX_TRX_HEADERS_GT Header
WHERE
Taxlines.tax_regime_Code = Rates.tax_regime_code
AND Taxlines.tax = Rates.tax
AND Taxlines.tax_status_code = Rates.tax_status_code
AND Rates.enabled_flag = 'Y'
AND Header.trx_date between nvl(Rates.start_date,Header.trx_date)
AND nvl(Rates.end_date, Header.trx_date)
-- AND Header.trx_date between
-- nvl(Rates.default_flg_effective_from,Header.trx_date)
-- AND nvl(Rates.default_flg_effective_to, Header.trx_date)
AND TaxLines.application_id = Header.application_id
AND TaxLines.entity_code = Header.entity_code
AND TaxLines.event_class_code = Header.event_class_code
AND TaxLines.trx_id = Header.trx_id
) Temp
ON ( TaxLines.trx_id = Temp.trx_id
)
WHEN MATCHED THEN
UPDATE SET
tax_rate_code = nvl(TaxLines.tax_rate_code,Temp.tax_code),
tax_rate_id = nvl(TaxLines.tax_rate_id,Temp.vat_tax_id),
tax_rate = nvl(TaxLines.tax_rate,Temp.tax_rate)
WHEN NOT MATCHED THEN
INSERT(tax) VALUES(NULL);
USING (SELECT
TaxLines.tax_rate,
TaxLines.tax_amt_included_flag,
TaxLink.line_amt,
TaxLines.trx_id
FROM
ZX_IMPORT_TAX_LINES_GT TaxLines,
ZX_TRX_TAX_LINK_GT TaxLink
WHERE
Taxlines.TAX_LINE_ALLOCATION_FLAG = 'Y' AND
TaxLines.tax_amt is NULL AND
TaxLines.tax_rate is not NULL AND
TaxLines.application_id = taxLink.application_id AND
TaxLines.entity_code = taxLink.entity_code AND
TaxLines.event_class_code = taxLink.event_class_code AND
TaxLines.summary_tax_line_number = taxLink.summary_tax_line_number AND
TaxLines.trx_id = TaxLink.trx_id
) Temp
ON(
TaxLines1.trx_id = Temp.trx_id
)
WHEN MATCHED THEN
UPDATE SET
tax_amt = CASE WHEN (temp.tax_amt_included_flag <> 'Y')
THEN (temp.tax_rate / 100 ) * temp.line_amt
WHEN (temp.tax_rate = 0 )
THEN 0
ELSE temp.tax_rate * temp.line_amt / ( 100 + temp.tax_rate )
END
WHEN NOT MATCHED THEN
INSERT(tax) VALUES(NULL);
SELECT line_level_action
INTO l_line_level_action
FROM ZX_TRANSACTION_LINES_GT
WHERE rownum = 1;
UPDATE ZX_TRANSACTION_LINES_GT L
SET (L.product_fisc_classification,
L.trx_business_category,
L.product_category,
L.output_tax_classification_code ) =
(SELECT D.product_fisc_classification,
D.trx_business_category,
D.product_category,
D.output_tax_classification_code
FROM ZX_LINES_DET_FACTORS D
WHERE D.event_class_code = L.source_event_class_code
AND D.application_id = L.source_application_id
AND D.entity_code = L.source_entity_code
AND D.trx_id = L.source_trx_id
AND D.trx_line_id = L.source_line_id
AND D.trx_level_type = L.source_trx_level_type )
WHERE L.source_trx_id IS NOT NULL
AND L.line_level_action = 'COPY_AND_CREATE';
USING (SELECT
fc.classification_code product_fisc_class,
Lines.trx_id
FROM
zx_fc_product_fiscal_v fc,
mtl_item_categories mic,
zx_transaction_lines_gt lines ,
zx_trx_headers_gt header
WHERE
((fc.country_code = Header.default_taxation_country
AND fc.country_code in ('AR', 'BR', 'CO'))
or
fc.country_code is NULL
)
AND Lines.application_id = Header.application_id
AND Lines.entity_code = Header.entity_code
AND Lines.event_class_code = Header.event_class_code
AND Lines.trx_id = Header.trx_id
AND Lines.product_org_id is NOT NULL
AND Lines.product_id = mic.inventory_item_id
AND mic.organization_id = Lines.Product_org_id
AND mic.category_id = fc.category_id
AND mic.category_set_id = fc.category_set_id
-- AND fc.structure_name = 'Fiscal Classification' -- Commented for Bug#7125709
AND fc.structure_code = 'FISCAL_CLASSIFICATION' -- Added as a fix for Bug#7125709
AND EXISTS
(SELECT 1
FROM JL_ZZ_AR_TX_FSC_CLS
WHERE fiscal_classification_code = fc.classification_code
AND enabled_flag = 'Y')
) Temp
ON ( Lines.trx_id = Temp.trx_id)
WHEN MATCHED THEN
UPDATE SET
product_fisc_classification = nvl(Lines.product_fisc_classification,
Temp.product_fisc_class)
WHEN NOT MATCHED THEN
INSERT (LINE_AMT) VALUES(NULL);
USING (SELECT
Event.tax_event_class_code ||g_delimiter||global_attribute2 trx_business_category,
Lines.trx_id
FROM
ZX_TRANSACTION_LINES_GT Lines ,
mtl_system_items items,
ZX_EVNT_CLS_MAPPINGS event
WHERE organization_id = l_organization_id
AND inventory_item_id = lines.product_id
AND lines.product_org_id is not NULL
AND Lines.application_id = Event.application_id
AND Lines.entity_code = Event.entity_code
AND Lines.event_class_code = Event.event_class_code
)Temp
ON ( Lines.trx_id = Temp.trx_id)
WHEN MATCHED THEN
UPDATE SET
trx_business_category = nvl(Lines.trx_business_category,
Temp.trx_business_category)
WHEN NOT MATCHED THEN
INSERT (LINE_AMT) VALUES(NULL);
USING (SELECT
Event.tax_event_class_code ||g_delimiter||Memo.global_attribute2 trx_business_category,
Memo.tax_product_category product_category,
Lines.trx_id
FROM
ZX_TRANSACTION_LINES_GT Lines ,
ar_memo_lines_all_b Memo,
ZX_EVNT_CLS_MAPPINGS event
WHERE Memo.memo_line_id = lines.product_id
AND lines.product_org_id is NULL
AND Lines.application_id = Event.application_id
AND Lines.entity_code = Event.entity_code
AND Lines.event_class_code = Event.event_class_code
)Temp
ON ( Lines.trx_id = Temp.trx_id)
WHEN MATCHED THEN
UPDATE SET
trx_business_category = nvl(Lines.trx_business_category,
Temp.trx_business_category) ,
Product_category = nvl(Lines.product_category,
Temp.product_category)
WHEN NOT MATCHED THEN
INSERT (LINE_AMT) VALUES(NULL);
SELECT l_event_class_code || g_delimiter || global_attribute2
INTO l_trx_biz_categ
FROM mtl_system_items
WHERE organization_id = l_organization_id
AND inventory_item_id = l_inv_item_id;
SELECT l_event_class_code || g_delimiter || global_attribute2
INTO l_trx_biz_categ
FROM ar_memo_lines
WHERE memo_line_id = l_memo_line_id;
USING (SELECT CTT.global_attribute4 output_tax_classification_code,
H.trx_id
FROM ZX_TRX_HEADERS_GT H,
RA_CUST_TRX_TYPES_ALL CTT,
AR_VAT_TAX_ALL_B VT
WHERE CTT.cust_trx_type_id = H.receivables_trx_type_id
AND CTT.org_id = VT.org_id
AND CTT.org_id = H.internal_organization_id
AND CTT.global_attribute4 = VT.tax_code
AND VT.set_of_books_id = H.ledger_id
AND H.trx_date between VT.start_date
and NVL(VT.end_date, H.trx_date)
AND NVL(VT.enabled_flag,'Y') = 'Y'
AND NVL(VT.tax_class,'O') = 'O'
)Temp
ON ( Lines.trx_id = Temp.trx_id)
WHEN MATCHED THEN
UPDATE SET
output_tax_classification_code = NVL(Lines.output_tax_classification_code,
Temp.output_tax_classification_code)
WHEN NOT MATCHED THEN
INSERT (output_tax_classification_code) VALUES(NULL);
SELECT delimiter
FROM zx_fc_types_b
WHERE classification_type_code ='TRX_BUSINESS_CATEGORY';
SELECT
zx_global_structures_pkg.trx_line_dist_tbl.tax_event_class_code(p_trx_line_index)
||g_delimiter||items.global_attribute2
INTO
l_trx_business_category
FROM
mtl_system_items items
WHERE organization_id = l_organization_id
AND inventory_item_id = zx_global_structures_pkg.trx_line_dist_tbl.product_id(p_trx_line_index);
SELECT
fc.classification_code
INTO
l_product_fisc_class
FROM
zx_fc_product_fiscal_v fc,
mtl_item_categories mic
WHERE
((fc.country_code =
zx_global_structures_pkg.trx_line_dist_tbl.default_taxation_country(p_trx_line_index)
AND fc.country_code in ('AR', 'BR', 'CO'))
or
fc.country_code is NULL
)
AND zx_global_structures_pkg.trx_line_dist_tbl.product_id(p_trx_line_index)
= mic.inventory_item_id
AND mic.organization_id = l_organization_id
AND mic.category_id = fc.category_id
AND mic.category_set_id = fc.category_set_id
-- AND fc.structure_name = 'Fiscal Classification' -- Commented for Bug#7125709
AND fc.structure_code = 'FISCAL_CLASSIFICATION' -- Added as a fix for Bug#7125709
AND EXISTS
(SELECT 1
FROM JL_ZZ_AR_TX_FSC_CLS
WHERE fiscal_classification_code = fc.classification_code
AND enabled_flag = 'Y')
AND rownum = 1; -- Bug 5701599
SELECT
zx_global_structures_pkg.trx_line_dist_tbl.tax_event_class_code(p_trx_line_index)
||g_delimiter||Memo.global_attribute2 trx_business_category,
Memo.tax_product_category product_category
INTO
l_trx_business_category,
l_product_category
FROM
ar_memo_lines_all_b Memo
WHERE memo_line_id = zx_global_structures_pkg.trx_line_dist_tbl.product_id(p_trx_line_index);
INSERT ALL
WHEN (REGIME_NOT_EXISTS = 'Y') THEN
INTO ZX_VALIDATION_ERRORS_GT(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
message_name,
message_text,
trx_level_type
)
VALUES(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
'ZX_REGIME_NOT_EXIST',
l_regime_not_exists,
trx_level_type
)
WHEN (REGIME_NOT_EFFECTIVE = 'Y') THEN
INTO ZX_VALIDATION_ERRORS_GT(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
message_name,
message_text,
trx_level_type
)
VALUES(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
'ZX_REGIME_NOT_EFFECTIVE',
l_regime_not_effective,
trx_level_type
)
WHEN (TAX_NOT_EXISTS = 'Y') THEN
INTO ZX_VALIDATION_ERRORS_GT(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
message_name,
message_text,
trx_level_type
)
VALUES(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
'ZX_TAX_NOT_EXIST',
l_tax_not_exists,
trx_level_type
)
WHEN (TAX_NOT_LIVE = 'Y') THEN
INTO ZX_VALIDATION_ERRORS_GT(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
message_name,
message_text,
trx_level_type
)
VALUES(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
'ZX_TAX_NOT_LIVE',
l_tax_not_live,
trx_level_type
)
WHEN (TAX_NOT_EFFECTIVE = 'Y') THEN
INTO ZX_VALIDATION_ERRORS_GT(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
message_name,
message_text,
trx_level_type
)
VALUES(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
'ZX_TAX_NOT_EFFECTIVE',
l_tax_not_effective,
trx_level_type
)
WHEN (TAX_STATUS_NOT_EXISTS = 'Y') THEN
INTO ZX_VALIDATION_ERRORS_GT(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
message_name,
message_text,
trx_level_type
)
VALUES(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
'ZX_TAX_STATUS_NOT_EXIST',
l_tax_status_not_exists,
trx_level_type
)
WHEN (TAX_STATUS_NOT_EFFECTIVE = 'Y') THEN
INTO ZX_VALIDATION_ERRORS_GT(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
message_name,
message_text,
trx_level_type
)
VALUES(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
'ZX_TAX_STATUS_NOT_EFFECTIVE',
l_tax_status_not_effective,
trx_level_type
)
WHEN (TAX_RATE_NOT_EXISTS = 'Y') THEN
INTO ZX_VALIDATION_ERRORS_GT(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
message_name,
message_text,
trx_level_type
)
VALUES(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
'ZX_TAX_RATE_NOT_EXIST',
l_tax_rate_not_exists,
trx_level_type
)
WHEN (TAX_RATE_NOT_EFFECTIVE = 'Y') THEN
INTO ZX_VALIDATION_ERRORS_GT(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
message_name,
message_text,
trx_level_type
)
VALUES(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
'ZX_TAX_RATE_NOT_EFFECTIVE',
l_tax_rate_not_effective,
trx_level_type
)
WHEN (TAX_RATE_NOT_ACTIVE = 'Y') THEN
INTO ZX_VALIDATION_ERRORS_GT(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
message_name,
message_text,
trx_level_type
)
VALUES(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
'ZX_TAX_RATE_NOT_ACTIVE',
l_tax_rate_not_active,
trx_level_type
)
WHEN (TAX_RATE_CODE_NOT_EXISTS = 'Y') THEN
INTO ZX_VALIDATION_ERRORS_GT(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
message_name,
message_text,
trx_level_type
)
VALUES(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
'ZX_TAX_RATE_NOT_EXIST',
l_tax_rate_not_exists,
trx_level_type
)
WHEN (TAX_RATE_CODE_NOT_EFFECTIVE = 'Y') THEN
INTO ZX_VALIDATION_ERRORS_GT(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
message_name,
message_text,
trx_level_type
)
VALUES(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
'ZX_TAX_RATE_NOT_EFFECTIVE',
l_tax_rate_not_effective,
trx_level_type
)
WHEN (TAX_RATE_CODE_NOT_ACTIVE = 'Y') THEN
INTO ZX_VALIDATION_ERRORS_GT(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
message_name,
message_text,
trx_level_type
)
VALUES(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
'ZX_TAX_RATE_NOT_ACTIVE',
l_tax_rate_not_active,
trx_level_type
)
WHEN (TAX_RATE_PERCENTAGE_INVALID = 'Y') THEN
INTO ZX_VALIDATION_ERRORS_GT(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
message_name,
message_text,
trx_level_type
)
VALUES(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
'ZX_TAX_RATE_PERCENTAGE_INVALID',
l_tax_rate_percentage_invalid,
trx_level_type
)
WHEN (ZX_EVNT_CLS_MPG_INVALID = 'Y') THEN
INTO ZX_VALIDATION_ERRORS_GT(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
message_name,
message_text,
trx_level_type
)
VALUES(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
'ZX_EVNT_CLS_MPG_INVALID',
l_evnt_cls_mpg_invalid,
trx_level_type
)
WHEN (ZX_EXCHG_INFO_MISSING = 'Y') THEN
INTO ZX_VALIDATION_ERRORS_GT(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
message_name,
message_text,
trx_level_type
)
VALUES(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
'ZX_EXCHG_INFO_MISSING',
l_exchg_info_missing,
trx_level_type
)
WHEN (ZX_LINE_CLASS_INVALID = 'Y') THEN
INTO ZX_VALIDATION_ERRORS_GT(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
message_name,
message_text,
trx_level_type
)
VALUES(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
'ZX_LINE_CLASS_INVALID',
l_line_class_invalid,
trx_level_type
)
WHEN (ZX_TRX_LINE_TYPE_INVALID = 'Y') THEN
INTO ZX_VALIDATION_ERRORS_GT(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
message_name,
message_text,
trx_level_type
)
VALUES(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
'ZX_TRX_LINE_TYPE_INVALID',
l_trx_line_type_invalid,
trx_level_type
)
WHEN (ZX_LINE_AMT_INCL_TAX_INVALID = 'Y') THEN
INTO ZX_VALIDATION_ERRORS_GT(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
message_name,
message_text,
trx_level_type
)
VALUES(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
'ZX_LINE_AMT_INCTAX_INVALID',
l_line_amt_incl_tax_invalid,
trx_level_type
)
/*
WHEN (SHIP_TO_PARTY_NOT_EXISTS = 'Y') THEN
INTO ZX_VALIDATION_ERRORS_GT(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
message_name,
message_text,
trx_level_type
)
VALUES(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
'ZX_SHIP_TO_PARTY_NOT_EXIST',
l_ship_to_party_not_exists,
trx_level_type
)
WHEN (BILL_TO_PARTY_NOT_EXISTS = 'Y') THEN
INTO ZX_VALIDATION_ERRORS_GT(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
message_name,
message_text,
trx_level_type
)
VALUES(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
'ZX_BILTO_PARTY_NOT_EXIST',
l_bill_to_party_not_exists,
trx_level_type
)
WHEN (SHIPTO_PARTY_SITE_NOT_EXISTS = 'Y') THEN
INTO ZX_VALIDATION_ERRORS_GT(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
message_name,
message_text,
trx_level_type
)
VALUES(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
'ZX_SHIPTO_PARTY_SITE_NOT_EXIST',
l_shipto_party_site_not_exists,
trx_level_type
)
WHEN (SHIPFROM_PARTY_SITE_NOT_EXISTS = 'Y') THEN
INTO ZX_VALIDATION_ERRORS_GT(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
message_name,
message_text,
trx_level_type
)
VALUES(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
'ZX_SHIPFROM_PARTY_SITE_NOT_EXIST',
l_shipfrm_party_site_not_exits,
trx_level_type
)
WHEN (BILLTO_PARTY_SITE_NOT_EXISTS = 'Y') THEN
INTO ZX_VALIDATION_ERRORS_GT(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
message_name,
message_text,
trx_level_type
)
VALUES(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
'ZX_BILLTO_PARTY_SITE_NOT_EXIST',
l_billto_party_site_not_exists,
trx_level_type
)
*/
WHEN (USER_DEF_FC_NA_FOR_LTE = 'Y') THEN
INTO ZX_VALIDATION_ERRORS_GT(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
message_name,
message_text,
trx_level_type
)
VALUES(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
NULL,
'ZX_USER_DEF_FC_NA_FOR_LTE',
l_user_def_fc_na_for_lte,
trx_level_type
)
WHEN (PRODUCT_CATEGORY_NA_FOR_LTE = 'Y') THEN
INTO ZX_VALIDATION_ERRORS_GT(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
message_name,
message_text,
trx_level_type
)
VALUES(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
NULL,
'ZX_PRODUCT_CATEGORY_NA_FOR_LTE',
l_product_category_na_for_lte,
trx_level_type
)
WHEN (DOCUMENT_FC_NA_FOR_LTE = 'Y') THEN
INTO ZX_VALIDATION_ERRORS_GT(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
message_name,
message_text,
trx_level_type
)
VALUES(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
NULL,
'ZX_DOCUMENT_FC_NA_FOR_LTE',
l_document_fc_na_for_lte,
trx_level_type
)
WHEN (INTENDED_USE_NA_FOR_LTE = 'Y') THEN
INTO ZX_VALIDATION_ERRORS_GT(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
message_name,
message_text,
trx_level_type
)
VALUES(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
NULL,
'ZX_INTENDED_USE_NA_FOR_LTE',
l_indended_use_na_for_lte,
trx_level_type
)
WHEN (PRODUCT_TYPE_NA_FOR_LTE = 'Y') THEN
INTO ZX_VALIDATION_ERRORS_GT(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
message_name,
message_text,
trx_level_type
)
VALUES(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
NULL,
'ZX_PRODUCT_TYPE_NA_FOR_LTE',
l_product_type_na_for_lte,
trx_level_type
)
WHEN (TAX_JUR_CODE_NA_FOR_LTE = 'Y') THEN
INTO ZX_VALIDATION_ERRORS_GT(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
message_name,
message_text,
trx_level_type
)
VALUES(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
'ZX_TAX_JUR_CODE_NA_FOR_LTE',
l_tax_jur_code_na_for_lte,
trx_level_type
)
SELECT
header.application_id,
header.entity_code,
header.event_class_code,
header.trx_id,
lines_gt.trx_line_id,
taxlines_gt.summary_tax_line_number,
lines_gt.trx_level_type,
-- Check for Regime Existence
nvl2( regime.tax_regime_code,
'N','Y'
) REGIME_NOT_EXISTS,
-- Check for Regime Effectivity
nvl2(regime.tax_regime_code,
CASE WHEN header.trx_date BETWEEN
regime.effective_from AND
nvl(regime.effective_to, header.trx_date)
THEN 'N'
ELSE 'Y' END,
NULL
) REGIME_NOT_EFFECTIVE,
-- Check for Tax Existence
CASE WHEN (tax.tax_regime_code = regime.tax_regime_code AND
tax.tax is not null)
THEN NULL
ELSE 'Y'
END TAX_NOT_EXISTS,
-- Check for Tax Live flag
nvl2(CASE WHEN (tax.tax_regime_code=regime.tax_regime_code AND
tax.tax is not null)
THEN 'Y'
ELSE NULL END,
CASE WHEN tax.live_for_processing_flag = 'Y'
THEN 'N'
ELSE 'Y' END,
NULL
) TAX_NOT_LIVE,
-- Check for Tax Effectivity
nvl2(CASE WHEN (tax.tax_regime_code=regime.tax_regime_code AND
tax.tax is not null)
THEN 'Y'
ELSE NULL END,
CASE WHEN header.trx_date BETWEEN
tax.effective_from AND
nvl(tax.effective_to, header.trx_date)
THEN 'N'
ELSE 'Y' END,
NULL
) TAX_NOT_EFFECTIVE,
-- Check for Status Existence
CASE WHEN(status.tax_regime_code = regime.tax_regime_code AND
status.tax = tax.tax AND
status.tax_status_code is not null)
THEN NULL
ELSE 'Y'
END TAX_STATUS_NOT_EXISTS,
-- Check for Status Effectivity
nvl2(CASE WHEN(status.tax_regime_code = regime.tax_regime_code
AND status.tax = tax.tax AND
status.tax_status_code is not null)
THEN 'Y'
ELSE NULL
END ,
CASE WHEN header.trx_date BETWEEN
status.effective_from AND
nvl(status.effective_to, header.trx_date)
THEN 'N'
ELSE 'Y' END,
NULL
) TAX_STATUS_NOT_EFFECTIVE,
-- Check for Rate Id Existence
nvl2(rate.vat_tax_id,'N','Y') TAX_RATE_NOT_EXISTS,
-- Check for Rate Id Date Effectivity
nvl2(rate.vat_tax_id,
CASE WHEN header.trx_date BETWEEN
rate.start_date AND
nvl(rate.end_date, header.trx_date)
THEN 'N'
ELSE 'Y' END,
NULL
) TAX_RATE_NOT_EFFECTIVE,
-- Check Rate Id is Active
nvl2(rate.vat_tax_id,
CASE WHEN rate.enabled_flag = 'Y'
THEN 'N'
ELSE 'Y' END,
NULL
) TAX_RATE_NOT_ACTIVE,
-- Check for Rate Code Existence
CASE WHEN (rate.tax_regime_code = regime.tax_regime_code AND
rate.tax = tax.tax AND
rate.tax_status_code = status.tax_status_code AND
rate.tax_code is not null)
THEN NULL
ELSE 'Y'
END TAX_RATE_CODE_NOT_EXISTS,
-- Check for Rate Code Effectivity
nvl2(CASE WHEN (rate.tax_regime_code = regime.tax_regime_code
AND rate.tax = tax.tax AND
rate.tax_status_code = status.tax_status_code
AND rate.tax_code is not null)
THEN 'Y'
ELSE NULL
END,
CASE WHEN header.trx_date BETWEEN
rate.start_date AND
nvl(rate.end_date, header.trx_date)
THEN 'N'
ELSE 'Y' END,
NULL
) TAX_RATE_CODE_NOT_EFFECTIVE,
-- Check Rate Code is Active
nvl2(CASE WHEN (rate.tax_regime_code = regime.tax_regime_code
AND rate.tax = tax.tax AND
rate.tax_status_code = status.tax_status_code
AND rate.tax_code is not null)
THEN 'Y'
ELSE NULL
END,
CASE WHEN rate.enabled_flag = 'Y'
THEN 'N'
ELSE 'Y' END,
NULL
) TAX_RATE_CODE_NOT_ACTIVE,
-- Check for Rate Percentage
CASE WHEN (rate.tax_regime_code = regime.tax_regime_code
AND rate.tax = tax.tax AND
rate.tax_status_code = status.tax_status_code
AND rate.tax_code is not null AND
rate.tax_rate <> taxlines_gt.tax_rate
AND rate.validate_flag <> 'Y' AND
header.trx_date BETWEEN
rate.start_date AND
nvl(rate.end_date, header.trx_date))
THEN 'Y'
ELSE NULL
END TAX_RATE_PERCENTAGE_INVALID,
-- Check for Event Class Existence
CASE WHEN (evntmap.application_id is not null AND
evntmap.entity_code is not null AND
evntmap.event_class_code is not null)
THEN NULL
ELSE 'Y'
END ZX_EVNT_CLS_MPG_INVALID,
-- Check for existence of Exchange information
CASE WHEN (header.ledger_id = gsob.set_of_books_id AND
gsob.currency_code <> header.trx_currency_code AND
header.currency_conversion_rate is NULL AND
header.currency_conversion_date is NULL AND
header.currency_conversion_type is NULL
)
THEN 'Y'
ELSE 'N' --Note the change of yes, no value
END ZX_EXCHG_INFO_MISSING,
-- Check for Validity of Transaction line class
nvl2(lines_gt.line_class,
CASE WHEN (NOT EXISTS
(SELECT 1 FROM FND_LOOKUPS lkp
WHERE lines_gt.line_class = lkp.lookup_code
AND lkp.lookup_type = 'ZX_LINE_CLASS'))
THEN 'Y'
ELSE NULL
END,
NULL
) ZX_LINE_CLASS_INVALID,
-- Check for Validity of transaction line type
CASE WHEN (lines_gt.trx_line_type NOT IN('ITEM','FREIGHT',
'MISC'))
THEN 'Y'
ELSE NULL
END ZX_TRX_LINE_TYPE_INVALID,
-- Check for Validity of Line amount includes tax flag
CASE WHEN (lines_gt.line_amt_includes_tax_flag
NOT IN ('A','N','S'))
THEN 'Y'
ELSE NULL
END ZX_LINE_AMT_INCL_TAX_INVALID,
/* need to add party types for O2C
-- Check for SHIP_TO_PARTY_ID
nvl2(lines_gt.SHIP_TO_PARTY_ID,
CASE WHEN (NOT EXISTS
(SELECT 1 FROM zx_party_tax_profile
WHERE party_id =
lines_gt.SHIP_TO_PARTY_ID
AND party_type_code = 'CUSTOMER'))
THEN 'Y'
ELSE NULL END,
NULL) SHIP_TO_PARTY_NOT_EXISTS,
-- Check for BILL_TO_PARTY_ID
nvl2(lines_gt.BILL_TO_PARTY_ID,
CASE WHEN (NOT EXISTS
(SELECT 1 FROM zx_party_tax_profile
WHERE party_id =
lines_gt.BILL_TO_PARTY_ID
AND party_type_code = 'CUSTOMER'))
THEN 'Y'
ELSE NULL END,
NULL) BILL_TO_PARTY_NOT_EXISTS,
-- Check for SHIP_TO_PARTY_SITE_ID
nvl2(lines_gt.SHIP_TO_PARTY_SITE_ID,
CASE WHEN (NOT EXISTS
(SELECT 1 FROM zx_party_tax_profile
WHERE party_id =
lines_gt.SHIP_TO_PARTY_SITE_ID
AND party_type_code = 'CUSTOMER_SITE'))
THEN 'Y'
ELSE NULL END,
NULL) SHIPTO_PARTY_SITE_NOT_EXISTS,
-- Check for SHIP_FROM_PARTY_SITE_ID
nvl2(lines_gt.SHIP_FROM_PARTY_SITE_ID,
CASE WHEN (NOT EXISTS
(SELECT 1 FROM zx_party_tax_profile
WHERE party_id =
lines_gt.SHIP_FROM_PARTY_SITE_ID
AND party_type_code = 'LEGAL_ESTABLISHMENT'))
THEN 'Y'
ELSE NULL END,
NULL) SHIPFROM_PARTY_SITE_NOT_EXISTS,
-- Check for BILL_TO_PARTY_SITE_ID
nvl2(lines_gt.BILL_TO_PARTY_SITE_ID,
CASE WHEN (NOT EXISTS
(SELECT 1 FROM zx_party_tax_profile
WHERE party_id =
lines_gt.BILL_TO_PARTY_SITE_ID
AND party_type_code = 'CUSTOMER_SITE'))
THEN 'Y'
ELSE NULL END,
NULL) BILLTO_PARTY_SITE_NOT_EXISTS
*/
CASE WHEN (lines_gt.USER_DEFINED_FISC_CLASS is not null)
THEN 'Y'
ELSE NULL
END USER_DEF_FC_NA_FOR_LTE,
CASE WHEN (lines_gt.PRODUCT_CATEGORY is not null)
THEN 'Y'
ELSE NULL
END PRODUCT_CATEGORY_NA_FOR_LTE,
CASE WHEN (header.DOCUMENT_SUB_TYPE is not null)
THEN 'Y'
ELSE NULL
END DOCUMENT_FC_NA_FOR_LTE,
CASE WHEN (lines_gt.LINE_INTENDED_USE is not null)
THEN 'Y'
ELSE NULL
END INTENDED_USE_NA_FOR_LTE,
CASE WHEN (lines_gt.PRODUCT_TYPE is not null)
THEN 'Y'
ELSE NULL
END PRODUCT_TYPE_NA_FOR_LTE,
CASE WHEN (taxlines_gt.TAX_JURISDICTION_CODE is not null)
THEN 'Y'
ELSE NULL
END TAX_JUR_CODE_NA_FOR_LTE
FROM
ZX_TRX_HEADERS_GT header,
ZX_EVNT_CLS_MAPPINGS evntmap,
ZX_REGIMES_B regime ,
ZX_TAXES_B tax ,
ZX_STATUS_B status ,
AR_VAT_TAX_ALL_B rate ,
-- zx_import_tax_lines_gt temp_gt,
ZX_TRANSACTION_LINES_GT lines_gt,
ZX_IMPORT_TAX_LINES_GT taxlines_gt,
GL_SETS_OF_BOOKS gsob
WHERE
lines_gt.trx_id = header.trx_id
and taxlines_gt.trx_id = header.trx_id
and gsob.set_of_books_id(+) = header.ledger_id
and taxlines_gt.application_id = Header.application_id
and taxlines_gt.entity_code = Header.entity_code
and taxlines_gt.event_class_code
= Header.event_class_code
and header.application_id = evntmap.application_id (+)
and header.entity_code = evntmap.entity_code (+)
and header.event_class_code = evntmap.event_class_code(+)
and regime.tax_regime_code(+)= taxlines_gt.tax_regime_code
and (header.trx_date BETWEEN
nvl(regime.effective_from,header.trx_date) AND
nvl(regime.effective_to, header.trx_date)
OR
regime.effective_from = (select min(effective_from)
from ZX_REGIMES_B
where tax_regime_code =
regime.tax_regime_code)
)
and tax.tax(+) = taxlines_gt.tax
and (header.trx_date BETWEEN
nvl(tax.effective_from,header.trx_date) AND
nvl(tax.effective_to, header.trx_date)
OR
tax.effective_from = (select min(effective_from)
from ZX_TAXES_B
where tax = tax.tax)
)
and status.tax_status_code(+)= taxlines_gt.tax_status_code
and (header.trx_date BETWEEN
nvl(status.effective_from,header.trx_date) AND
nvl(status.effective_to, header.trx_date)
OR
status.effective_from = (select min(effective_from)
from ZX_status_B
where tax_status_code =
status.tax_status_code)
)
and rate.vat_tax_id(+) = taxlines_gt.tax_rate_id
and rate.tax_code (+) = taxlines_gt.tax_rate_code
and (header.trx_date BETWEEN
nvl(rate.start_date,header.trx_date) AND
nvl(rate.end_date, header.trx_date)
);
INSERT ALL
WHEN (SAMETAX_MULTIALLOC_TO_SAMELN = 'Y') THEN
INTO zx_validation_errors_gt(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
message_name,
message_text,
trx_level_type
)
VALUES (
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
'ZX_TAX_MULTIALLOC_TO_SAMELN',
l_tax_multialloc_to_sameln,
trx_level_type
)
WHEN (SAMESUMTX_MULTIALLOC_TO_SAMELN = 'Y') THEN
INTO zx_validation_errors_gt(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
message_name,
message_text,
trx_level_type
)
VALUES (
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
'ZX_IMPTAX_MULTIALLOC_TO_SAMELN',
l_imptax_multialloc_to_sameln,
trx_level_type
)
/* bug 3698554 */
WHEN (TAX_INCL_FLAG_MISMATCH = 'Y' ) THEN
INTO zx_validation_errors_gt(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
message_name,
message_text,
trx_level_type
)
VALUES (
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
'ZX_TAX_INCL_FLAG_MISMATCH',
l_tax_incl_flag_mismatch,
trx_level_type
)
WHEN (IMP_TAX_MISSING_IN_ADJUSTED_TO = 'Y') THEN
INTO zx_validation_errors_gt(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
message_name,
message_text,
trx_level_type
)
VALUES (
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
'IMP_TAX_MISSING_IN_ADJUSTED_TO',
l_imp_tax_missing_in_adjust_to,
trx_level_type
)
/* end bug 3698554 */
SELECT
header.application_id,
header.entity_code,
header.event_class_code,
header.trx_id,
lines_gt.trx_line_id,
lines_gt.trx_level_type,
imptaxes_gt.summary_tax_line_number,
-- The same tax regime and tax cannot be allocated to the same
-- transaction line multi times
--
CASE
WHEN EXISTS
(SELECT 1
FROM zx_import_tax_lines_gt imptaxes_gt1
WHERE imptaxes_gt1.application_id= imptaxes_gt.application_id
AND imptaxes_gt1.entity_code = imptaxes_gt.entity_code
AND imptaxes_gt1.event_class_code = imptaxes_gt.event_class_code
AND imptaxes_gt1.trx_id = imptaxes_gt.trx_id
AND imptaxes_gt1.summary_tax_line_number <>
imptaxes_gt.summary_tax_line_number
AND imptaxes_gt1.tax_regime_code = imptaxes_gt.tax_regime_code
AND imptaxes_gt1.tax = imptaxes_gt.tax
AND (imptaxes_gt1.tax_line_allocation_flag = 'Y'
AND EXISTS
(SELECT 1
FROM zx_trx_tax_link_gt
WHERE application_id = imptaxes_gt1.application_id
AND entity_code = imptaxes_gt1.entity_code
AND event_class_code = imptaxes_gt1.event_class_code
AND trx_id = imptaxes_gt1.trx_id
AND summary_tax_line_number =
imptaxes_gt1.summary_tax_line_number
AND trx_line_id = lines_gt.trx_line_id
AND trx_level_type = lines_gt.trx_level_type
)
) OR
(imptaxes_gt1.tax_line_allocation_flag = 'N'
AND lines_gt.applied_from_application_id IS NULL
AND lines_gt.adjusted_doc_application_id IS NULL
AND lines_gt.applied_to_application_id IS NULL
AND lines_gt.line_level_action = 'CREATE_WITH_TAX'
)
)
THEN
'Y'
ELSE
'N'
END SAMETAX_MULTIALLOC_TO_SAMELN,
-- The same summary tax line cannot be allocated to the same transaction
-- line multi times
--
CASE
WHEN
(SELECT COUNT(*)
FROM zx_trx_tax_link_gt
WHERE application_id = imptaxes_gt.application_id
AND entity_code = imptaxes_gt.entity_code
AND event_class_code = imptaxes_gt.event_class_code
AND trx_id = imptaxes_gt.trx_id
AND trx_line_id = lines_gt.trx_line_id
AND trx_level_type = lines_gt.trx_level_type
AND summary_tax_line_number =
imptaxes_gt.summary_tax_line_number
) > 1
THEN
'Y'
ELSE
'N'
END SAMESUMTX_MULTIALLOC_TO_SAMELN,
/* bug 3698554 */
-- If the imported tax line has inclusive_flag = 'N' but the tax
-- is defined as inclusive in ZX_TAXES and allow inclusive override is N
-- or vice versa, then raise error
CASE
WHEN EXISTS
(
SELECT 1
FROM zx_taxes_b taxes
WHERE taxes.tax_regime_code = imptaxes_gt.tax_regime_code
AND taxes.tax = imptaxes_gt.tax
AND taxes.def_inclusive_tax_flag <> imptaxes_gt.tax_amt_included_flag
AND taxes.tax_inclusive_override_flag = 'N'
)
THEN
'Y'
ELSE
'N'
END TAX_INCL_FLAG_MISMATCH,
/* end bug 3698554 */
CASE
WHEN lines_gt.adjusted_doc_application_id IS NOT NULL
AND NOT EXISTS
(SELECT 1
FROM zx_lines zl
WHERE zl.application_id = lines_gt.adjusted_doc_application_id
AND zl.entity_code = lines_gt.adjusted_doc_entity_code
AND zl.event_class_code = lines_gt.adjusted_doc_event_class_code
AND zl.trx_id = lines_gt.adjusted_doc_trx_id
AND zl.trx_line_id = lines_gt.adjusted_doc_line_id
AND zl.trx_level_type = lines_gt.adjusted_doc_trx_level_type
AND zl.tax_regime_code = imptaxes_gt.tax_regime_code
AND zl.tax = imptaxes_gt.tax
)
THEN
'Y'
ELSE
'N'
END IMP_TAX_MISSING_IN_ADJUSTED_TO
/* end bug 3676878 */
FROM
zx_trx_headers_gt header,
zx_transaction_lines_gt lines_gt,
zx_import_tax_lines_gt imptaxes_gt
WHERE
imptaxes_gt.application_id = header.application_id
AND imptaxes_gt.entity_code = header.entity_code
AND imptaxes_gt.event_class_code = header.event_class_code
AND imptaxes_gt.trx_id = header.trx_id
AND lines_gt.application_id = header.application_id
AND lines_gt.entity_code = header.entity_code
AND lines_gt.event_class_code = header.event_class_code
AND lines_gt.trx_id = header.trx_id
AND (imptaxes_gt.tax_line_allocation_flag = 'Y'
AND EXISTS
(SELECT 1
FROM zx_trx_tax_link_gt
WHERE application_id = imptaxes_gt.application_id
AND entity_code = imptaxes_gt.entity_code
AND event_class_code = imptaxes_gt.event_class_code
AND trx_id = imptaxes_gt.trx_id
AND summary_tax_line_number = imptaxes_gt.summary_tax_line_number
AND trx_line_id = lines_gt.trx_line_id
AND trx_level_type = lines_gt.trx_level_type
) OR
(imptaxes_gt.tax_line_allocation_flag = 'N'
AND lines_gt.applied_from_application_id IS NULL
AND lines_gt.adjusted_doc_application_id IS NULL
AND lines_gt.applied_to_application_id IS NULL
AND lines_gt.line_level_action = 'CREATE_WITH_TAX'
)
);