[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';
SELECT legal_entity_id , internal_organization_id
INTO l_le_id, l_ou_id
FROM ZX_TRX_HEADERS_GT Header
WHERE rownum = 1;
SELECT Count(*)
INTO g_tax_lines_count
FROM zx_import_tax_lines_gt;
UPDATE zx_trx_headers_gt
SET validation_check_flag = 'N'
WHERE trx_id IN (SELECT DISTINCT trx_id FROM zx_validation_errors_gt);
'Updated the validation_check_flag to N in Zx_Trx_Headers_GT for '||to_char(SQL%ROWCOUNT)||' trx(s).');
SELECT Count(*)
INTO l_err_count
FROM zx_validation_errors_gt;
FOR rec IN (SELECT trx_id, message_text FROM ZX_VALIDATION_ERRORS_GT) LOOP
IF (g_level_procedure >= g_current_runtime_level ) THEN
FND_LOG.STRING(g_level_procedure,
'JL.PL/SQL.JL_ZZ_TAX_VALIDATE_PKG.DEFAULT_AND_VALIDATE_TAX_ATTR',
'Trx_ID : '||rec.trx_id||', Error : '||rec.message_text);
SELECT delimiter
FROM zx_fc_types_b
WHERE classification_type_code ='PRODUCT_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,
interface_line_id)
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_business_category||')',
lines_gt.trx_level_type,
lines_gt.interface_line_id
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 tac,
jl_zz_ar_tx_categ tc,
ar_system_parameters 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,
interface_line_id)
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.product_fisc_classification||')',
lines_gt.trx_level_type,
lines_gt.interface_line_id
FROM
zx_transaction_lines_gt lines_gt
WHERE lines_gt.product_fisc_classification is NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM
zx_trx_headers_gt header,
zx_evnt_cls_mappings evntmap,
FND_LOOKUPS LK,
JL_ZZ_AR_TX_FSC_CLS FSC
where
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 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,header.trx_date) <= header.trx_date
and NVL(lk.END_DATE_ACTIVE,header.trx_date) >= header.trx_date
);
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,
interface_line_id)
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_CATEGORY_NOT_EXIST',
l_prd_category_not_exists||'('||lines_gt.product_category||')',
lines_gt.trx_level_type,
lines_gt.interface_line_id
FROM
zx_transaction_lines_gt lines_gt
WHERE lines_gt.product_category IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM
zx_trx_headers_gt header,
zx_evnt_cls_mappings evntmap,
FND_LOOKUPS LK,
JL_ZZ_AR_TX_FSC_CLS FSC
where
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 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 = SUBSTR(lines_gt.product_category,
INSTR(lines_gt.product_category, l_delimiter_prod_cat, 1) +1 )
and fsc.fiscal_classification_code = lk.lookup_code
and fsc.enabled_Flag = 'Y'
and nvl(lk.start_date_active,header.trx_date) <= header.trx_date
and NVL(lk.end_date_active,header.trx_date) >= header.trx_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;
USING (SELECT rates.tax_regime_code tax_regime_code,
rates.tax tax,
TaxLines.trx_id trx_id,
TaxLines.summary_tax_line_number summary_tax_line_number
FROM
ZX_IMPORT_TAX_LINES_GT TaxLines,
ZX_TRX_HEADERS_GT Header,
AR_VAT_TAX rates
WHERE
TaxLines.tax_rate_code = rates.tax_code(+)
AND TaxLines.tax_rate_code IS NOT NULL
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_gt.trx_id = Temp.trx_id AND
TaxLines_gt.summary_tax_line_number = Temp.summary_tax_line_number )
WHEN MATCHED THEN
UPDATE SET
tax_regime_code = nvl(TaxLines_gt.tax_regime_code, Temp.tax_regime_code),
tax = nvl(TaxLines_gt.tax, Temp.tax)
WHEN NOT MATCHED THEN
INSERT(tax) VALUES(NULL);
USING (SELECT rates.tax_regime_code tax_regime_code,
rates.tax tax,
TaxLines.trx_id trx_id,
TaxLines.summary_tax_line_number summary_tax_line_number
FROM
ZX_IMPORT_TAX_LINES_GT TaxLines,
ZX_TRX_HEADERS_GT Header,
AR_VAT_TAX rates
WHERE
TaxLines.tax_rate_id = rates.vat_tax_id(+)
AND TaxLines.tax_rate_id IS NOT NULL
AND (TaxLines.tax_regime_code IS NULL OR TaxLines.tax IS NULL)
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_gt.trx_id = Temp.trx_id AND
TaxLines_gt.summary_tax_line_number = Temp.summary_tax_line_number )
WHEN MATCHED THEN
UPDATE SET
tax_regime_code = nvl(TaxLines_gt.tax_regime_code, Temp.tax_regime_code),
tax = nvl(TaxLines_gt.tax, Temp.tax)
WHEN NOT MATCHED THEN
INSERT(tax) VALUES(NULL);
USING (SELECT Rates.tax_status_code tax_status_code,
TaxLines.trx_id trx_id,
TaxLines.summary_tax_line_number summary_tax_line_number
FROM
ZX_IMPORT_TAX_LINES_GT TaxLines,
AR_VAT_TAX Rates,
ZX_TRX_HEADERS_GT Header
WHERE
Taxlines.tax_regime_Code = Rates.tax_regime_code(+)
AND Taxlines.tax = Rates.tax(+)
AND ((Taxlines.tax_rate_code IS NOT NULL AND Taxlines.tax_rate_code = rates.tax_code)
OR (Taxlines.tax_rate_id IS NOT NULL AND Taxlines.tax_rate_id = rates.vat_tax_id))
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_gt.trx_id = Temp.trx_id AND
TaxLines_gt.summary_tax_line_number = Temp.summary_tax_line_number )
WHEN MATCHED THEN
UPDATE SET
tax_status_code = nvl(TaxLines_gt.tax_status_code, Temp.tax_status_code)
WHEN NOT MATCHED THEN
INSERT(tax) VALUES(NULL);
USING (SELECT Rates.tax_code,
Rates.vat_tax_id,
Rates.tax_rate,
TaxLines.trx_id,
TaxLines.summary_tax_line_number
FROM
AR_VAT_TAX 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 ((Taxlines.tax_rate_code IS NOT NULL AND Taxlines.tax_rate_code = rates.tax_code)
OR (Taxlines.tax_rate_id IS NOT NULL AND Taxlines.tax_rate_id = rates.vat_tax_id))
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 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_gt.trx_id = Temp.trx_id AND
TaxLines_gt.summary_tax_line_number = Temp.summary_tax_line_number )
WHEN MATCHED THEN
UPDATE SET
tax_rate_code = nvl(TaxLines_gt.tax_rate_code,Temp.tax_code),
tax_rate_id = nvl(TaxLines_gt.tax_rate_id,Temp.vat_tax_id),
tax_rate = nvl(TaxLines_gt.tax_rate,Temp.tax_rate)
WHEN NOT MATCHED THEN
INSERT(tax) VALUES(NULL);
USING (SELECT
TaxLines.tax_rate,
TaxLines.tax_amt_included_flag,
TaxLines.trx_id,
Lines.line_amt,
TaxLines.summary_tax_line_number
FROM
ZX_IMPORT_TAX_LINES_GT TaxLines,
ZX_TRX_HEADERS_GT Header,
ZX_TRANSACTION_LINES_GT Lines
WHERE
Taxlines.tax_line_allocation_flag = 'N' AND
TaxLines.tax_amt IS NULL AND
TaxLines.tax_rate IS NOT NULL 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 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.trx_line_id = TaxLines.trx_line_id
) Temp
ON ( TaxLines_gt.trx_id = Temp.trx_id AND
TaxLines_gt.summary_tax_line_number = Temp.summary_tax_line_number )
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,
Lines.trx_line_id,
Lines.trx_level_type
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 AND
Lines.trx_line_id = Temp.trx_line_id AND
Lines.trx_level_type = Temp.trx_level_type)
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,
items.global_attribute2 trx_business_category,
Lines.trx_id,
Lines.trx_line_id,
Lines.trx_level_type
FROM
ZX_TRANSACTION_LINES_GT Lines ,
mtl_system_items items,
ZX_EVNT_CLS_MAPPINGS event
WHERE items.organization_id = lines.Product_org_id
AND items.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 AND
Lines.trx_line_id = Temp.trx_line_id AND
Lines.trx_level_type = Temp.trx_level_type)
WHEN MATCHED THEN
UPDATE SET
trx_business_category = nvl(Lines.trx_business_category,
DECODE(Temp.trx_business_category,NULL,Temp.trx_business_category,
Temp.tax_event_class_code||g_delimiter||Temp.trx_business_category))
WHEN NOT MATCHED THEN
INSERT (LINE_AMT) VALUES(NULL);
USING (SELECT
Event.tax_event_class_code,
Memo.global_attribute2 trx_business_category,
Memo.tax_product_category product_category,
Lines.trx_id,
Lines.trx_line_id,
Lines.trx_level_type
FROM
ZX_TRANSACTION_LINES_GT Lines ,
ar_memo_lines 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 AND
Lines.trx_line_id = Temp.trx_line_id AND
Lines.trx_level_type = Temp.trx_level_type)
WHEN MATCHED THEN
UPDATE SET
trx_business_category = nvl(Lines.trx_business_category,
DECODE(Temp.trx_business_category,NULL,Temp.trx_business_category,
Temp.tax_event_class_code||g_delimiter||Temp.trx_business_category)),
Product_category = nvl(Lines.product_category,
Temp.product_category)
WHEN NOT MATCHED THEN
INSERT (LINE_AMT) VALUES(NULL);
USING (SELECT CTT.global_attribute4 output_tax_classification_code,
H.trx_id
FROM ZX_TRX_HEADERS_GT H,
RA_CUST_TRX_TYPES CTT,
AR_VAT_TAX 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
DECODE(items.global_attribute2, NULL, items.global_attribute2,
zx_global_structures_pkg.trx_line_dist_tbl.tax_event_class_code(p_trx_line_index)
||g_delimiter||items.global_attribute2) trx_business_category
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
DECODE(Memo.global_attribute2, NULL, Memo.global_attribute2,
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 Memo
WHERE memo_line_id = zx_global_structures_pkg.trx_line_dist_tbl.product_id(p_trx_line_index);
INSERT ALL
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,
interface_line_id
)
VALUES(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
NULL,
'ZX_EVNT_CLS_MPG_INVALID',
l_evnt_cls_mpg_invalid,
trx_level_type,
interface_line_id
)
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,
interface_line_id
)
VALUES(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
NULL,
'ZX_EXCHG_INFO_MISSING',
l_exchg_info_missing,
trx_level_type,
interface_line_id
)
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,
interface_line_id
)
VALUES(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
NULL,
'ZX_LINE_CLASS_INVALID',
l_line_class_invalid,
trx_level_type,
interface_line_id
)
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,
interface_line_id
)
VALUES(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
NULL,
'ZX_TRX_LINE_TYPE_INVALID',
l_trx_line_type_invalid,
trx_level_type,
interface_line_id
)
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,
interface_line_id
)
VALUES(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
NULL,
'ZX_LINE_AMT_INCTAX_INVALID',
l_line_amt_incl_tax_invalid,
trx_level_type,
interface_line_id
)
/*
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,
NULL,
'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,
NULL,
'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,
NULL,
'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,
NULL,
'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,
NULL,
'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,
interface_line_id
)
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,
interface_line_id
)
/*
-- Commented the validation as Product Category
-- is a required parameter for Memo Lines
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,
interface_line_id
)
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,
interface_line_id
)
*/
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,
interface_line_id
)
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,
interface_line_id
)
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,
interface_line_id
)
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,
interface_line_id
)
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,
interface_line_id
)
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,
interface_line_id
)
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,
interface_line_id
)
VALUES(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
NULL,
'ZX_TAX_RATE_NOT_EXIST',
l_tax_rate_not_exists ||' (Tax Classification Code = '||
output_tax_classification_code||')',
trx_level_type,
interface_line_id
)
WHEN (TAX_RATE_CODE_NOT_EXISTS = 'N' AND 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,
interface_line_id
)
VALUES(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
NULL,
'ZX_TAX_RATE_NOT_EFFECTIVE',
l_tax_rate_not_effective ||' (Tax Classification Code = '||
output_tax_classification_code||')',
trx_level_type,
interface_line_id
)
WHEN (TAX_RATE_CODE_NOT_EXISTS = 'N' AND 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,
interface_line_id
)
VALUES(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
NULL,
'ZX_TAX_RATE_NOT_ACTIVE',
l_tax_rate_not_active ||' (Tax Classification Code = '||
output_tax_classification_code||')',
trx_level_type,
interface_line_id
)
SELECT
header.application_id,
header.entity_code,
header.event_class_code,
header.trx_id,
lines_gt.trx_line_id,
lines_gt.trx_level_type,
lines_gt.interface_line_id,
-- 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
*/
-- Check for User-Defined Fiscal Classification
CASE WHEN (lines_gt.USER_DEFINED_FISC_CLASS is not null)
THEN 'Y'
ELSE NULL
END USER_DEF_FC_NA_FOR_LTE,
/*
-- Commented the validation as Product Category
-- is populated for Memo Lines
CASE WHEN (lines_gt.PRODUCT_CATEGORY is not null)
THEN 'Y'
ELSE NULL
END PRODUCT_CATEGORY_NA_FOR_LTE,
*/
-- Check for Document Subtype
CASE WHEN (header.DOCUMENT_SUB_TYPE is not null)
THEN 'Y'
ELSE NULL
END DOCUMENT_FC_NA_FOR_LTE,
-- Check for Line Intended Use
CASE WHEN (lines_gt.LINE_INTENDED_USE is not null)
THEN 'Y'
ELSE NULL
END INTENDED_USE_NA_FOR_LTE,
-- Check for Product Type
CASE WHEN (lines_gt.PRODUCT_TYPE is not null)
THEN 'Y'
ELSE NULL
END PRODUCT_TYPE_NA_FOR_LTE,
-- Tax Classification Code
lines_gt.output_tax_classification_code,
-- Check Tax Classification Code exists
CASE WHEN lines_gt.output_tax_classification_code IS NOT NULL
AND NOT EXISTS (SELECT 1
FROM zx_output_classifications_v
WHERE lookup_code = lines_gt.output_tax_classification_code
AND org_id in (header.internal_organization_id, -99))
THEN 'Y'
ELSE NULL
END TAX_RATE_CODE_NOT_EXISTS,
-- Check Tax Classification Code is effective
CASE WHEN lines_gt.output_tax_classification_code IS NOT NULL
AND NOT EXISTS (SELECT 1
FROM zx_output_classifications_v
WHERE lookup_code = lines_gt.output_tax_classification_code
AND org_id in (header.internal_organization_id, -99)
AND header.trx_date BETWEEN start_date_active
AND nvl(end_date_active,header.trx_date))
THEN 'Y'
ELSE NULL
END TAX_RATE_CODE_NOT_EFFECTIVE,
-- Check Tax Classification Code is Active
CASE WHEN lines_gt.output_tax_classification_code IS NOT NULL
AND NOT EXISTS (SELECT 1
FROM zx_output_classifications_v
WHERE lookup_code = lines_gt.output_tax_classification_code
AND org_id in (header.internal_organization_id, -99)
AND enabled_flag = 'Y')
THEN 'Y'
ELSE NULL
END TAX_RATE_CODE_NOT_ACTIVE
FROM
ZX_TRX_HEADERS_GT header,
ZX_EVNT_CLS_MAPPINGS evntmap,
ZX_TRANSACTION_LINES_GT lines_gt,
GL_SETS_OF_BOOKS gsob
WHERE
lines_gt.trx_id = header.trx_id
and gsob.set_of_books_id(+) = header.ledger_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 header.application_id = evntmap.application_id (+)
and header.entity_code = evntmap.entity_code (+)
and header.event_class_code = evntmap.event_class_code(+);
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,
interface_tax_line_id
)
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,
interface_tax_line_id
)
WHEN (REGIME_NOT_EFF_IN_SUBSCR = '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,
interface_tax_line_id
)
VALUES(
application_id,
entity_code,
event_class_code,
trx_id,
trx_line_id,
summary_tax_line_number,
'ZX_REGIME_NOT_EFF_IN_SUBSCR',
l_regime_not_eff_in_subscr,
trx_level_type,
interface_tax_line_id
)
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,
interface_tax_line_id
)
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,
interface_tax_line_id
)
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,
interface_tax_line_id
)
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,
interface_tax_line_id
)
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,
interface_tax_line_id
)
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,
interface_tax_line_id
)
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,
interface_tax_line_id
)
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,
interface_tax_line_id
)
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,
interface_tax_line_id
)
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,
interface_tax_line_id
)
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,
interface_tax_line_id
)
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,
interface_tax_line_id
)
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,
interface_tax_line_id
)
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,
interface_tax_line_id
)
SELECT
header.application_id,
header.entity_code,
header.event_class_code,
header.trx_id,
lines_gt.trx_line_id,
lines_gt.trx_level_type,
taxlines_gt.interface_tax_line_id,
taxlines_gt.summary_tax_line_number,
-- Check for Regime Existence
CASE WHEN taxlines_gt.tax_regime_code IS NOT NULL AND
regime.tax_regime_code IS NULL
THEN 'Y'
ELSE 'N'
END REGIME_NOT_EXISTS,
-- Check for Regime Effectivity in surscription detail table
CASE WHEN taxlines_gt.tax_regime_code IS NOT NULL
AND regime.tax_regime_code IS NOT NULL
THEN
CASE WHEN sd_reg.tax_regime_code IS NULL
THEN 'Y'
ELSE 'N' END
ELSE 'N'
END REGIME_NOT_EFF_IN_SUBSCR,
-- Check for Regime Effectivity
CASE WHEN taxlines_gt.tax_regime_code IS NOT NULL
AND regime.tax_regime_code IS NOT NULL
AND sd_reg.tax_regime_code IS NOT NULL
THEN
CASE WHEN header.trx_date
BETWEEN regime.effective_from
AND NVL(regime.effective_to,header.trx_date)
THEN 'N'
ELSE 'Y' END
ELSE 'N'
END REGIME_NOT_EFFECTIVE,
-- Check for Tax Existence
nvl2(taxlines_gt.tax,
CASE WHEN (sd_tax.tax_regime_code IS NOT NULL AND
tax.tax is not null)
THEN 'N'
ELSE 'Y' END,
'N') TAX_NOT_EXISTS,
-- Check for Tax Live flag
nvl2(taxlines_gt.tax,
CASE WHEN (sd_tax.tax_regime_code IS NOT NULL AND
tax.tax is not NULL )
THEN
CASE WHEN tax.live_for_processing_flag = 'Y'
THEN 'N'
ELSE 'Y' END
ELSE 'N' END,
'N') TAX_NOT_LIVE,
-- Check for Tax Effectivity
nvl2(taxlines_gt.tax,
CASE WHEN (sd_tax.tax_regime_code IS NOT NULL AND
tax.tax is not null)
THEN
CASE WHEN header.trx_date
BETWEEN tax.effective_from AND
NVL(tax.effective_to,header.trx_date)
THEN 'N'
ELSE 'Y' END
ELSE 'N' END ,
'N') TAX_NOT_EFFECTIVE,
-- Check for Status Existence
nvl2(taxlines_gt.tax_status_code,
CASE WHEN(sd_status.tax_regime_code IS NOT NULL AND
status.tax_status_code is not null)
THEN 'N'
ELSE 'Y' END,
'N') TAX_STATUS_NOT_EXISTS,
-- Check for Status Effectivity
nvl2(taxlines_gt.tax_status_code,
CASE WHEN(sd_status.tax_regime_code IS NOT NULL AND
status.tax_status_code IS NOT NULL)
THEN CASE WHEN header.trx_date
BETWEEN status.effective_from AND
nvl(status.effective_to,header.trx_date)
THEN 'N'
ELSE 'Y' END
ELSE 'N' END,
'N') TAX_STATUS_NOT_EFFECTIVE,
-- Check for Tax Jurisdiction
CASE WHEN (taxlines_gt.TAX_JURISDICTION_CODE IS NOT NULL)
THEN 'Y'
ELSE 'N'
END TAX_JUR_CODE_NA_FOR_LTE
FROM
ZX_TRX_HEADERS_GT header,
ZX_REGIMES_B regime,
ZX_TAXES_B tax,
ZX_STATUS_B status,
ZX_TRANSACTION_LINES_GT lines_gt,
ZX_IMPORT_TAX_LINES_GT taxlines_gt,
ZX_SUBSCRIPTION_DETAILS sd_reg,
ZX_SUBSCRIPTION_DETAILS sd_tax,
ZX_SUBSCRIPTION_DETAILS sd_status
WHERE
lines_gt.trx_id = header.trx_id
AND taxlines_gt.trx_id = header.trx_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 = lines_gt.application_id
AND header.entity_code = lines_gt.entity_code
AND header.event_class_code = lines_gt.event_class_code
AND lines_gt.trx_line_id = taxlines_gt.trx_line_id
-- Regime
AND regime.tax_regime_code(+)= taxlines_gt.tax_regime_code
AND regime.tax_regime_code = sd_reg.tax_regime_code (+)
AND sd_reg.first_pty_org_id(+) = g_first_pty_org_id
AND NVL(sd_reg.view_options_code,'NONE') in ('NONE', 'VFC')
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 (header.trx_date between
nvl(sd_reg.effective_from, header.trx_date) AND
nvl(sd_reg.effective_to, header.trx_date)
)
-- Tax
AND tax.tax(+) = taxlines_gt.tax
AND tax.tax_regime_code(+) = taxlines_gt.tax_regime_code
AND tax.tax_regime_code = sd_tax.tax_regime_code (+)
AND (tax.content_owner_id = sd_tax.parent_first_pty_org_id or
sd_tax.parent_first_pty_org_id is null)
AND sd_tax.first_pty_org_id(+) = g_first_pty_org_id
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 (header.trx_date between
nvl(sd_tax.effective_from,header.trx_date) AND
NVL(sd_tax.effective_to,header.trx_date)
)
AND ( nvl(sd_tax.view_options_code,'NONE') in ('NONE', 'VFC')
or
( nvl(sd_tax.view_options_code,'VFR') = 'VFR'
AND not exists
( SELECT 1
FROM zx_taxes_b b
WHERE tax.tax_regime_code = b.tax_regime_code
AND tax.tax = b.tax
AND sd_tax.first_pty_org_id = b.content_owner_id )
)
)
-- Status
AND status.tax_status_code(+) = taxlines_gt.tax_status_code
AND status.tax(+) = taxlines_gt.tax
AND status.tax_regime_code(+) = taxlines_gt.tax_regime_code
AND status.tax_regime_code = sd_status.tax_regime_code (+)
AND (status.content_owner_id = sd_status.parent_first_pty_org_id
or sd_status.parent_first_pty_org_id is null)
AND sd_status.first_pty_org_id(+) = g_first_pty_org_id
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 (header.trx_date between
nvl(sd_status.effective_from,header.trx_date) AND
nvl(sd_status.effective_to,header.trx_date)
)
AND (nvl(sd_status.view_options_code,'NONE') in ('NONE', 'VFC')
or (nvl(sd_status.view_options_code,'VFR') = 'VFR'
AND not exists
(SELECT 1
FROM zx_status_vl b
WHERE b.tax_regime_code = status.tax_regime_code
AND b.tax = status.tax
AND b.tax_status_code = status.tax_status_code
AND b.content_owner_id = sd_status.first_pty_org_id)
)
);
INSERT ALL
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,
interface_tax_line_id
)
VALUES(
application_id,
entity_code,
event_class_code,
trx_id,
NULL,
summary_tax_line_number,
'ZX_TAX_RATE_NOT_EXIST',
l_tax_rate_not_exists ||' (Tax_Rate_Code: '||
tax_rate_code||', Tax_Rate_Id: '||tax_rate_id||')',
NULL,
interface_tax_line_id
)
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,
interface_tax_line_id
)
VALUES(
application_id,
entity_code,
event_class_code,
trx_id,
NULL,
summary_tax_line_number,
'ZX_TAX_RATE_NOT_EFFECTIVE',
l_tax_rate_not_effective ||' (Tax_Rate_Code: '||
tax_rate_code||', Tax_Rate_Id: '||tax_rate_id||')',
NULL,
interface_tax_line_id
)
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,
interface_tax_line_id
)
VALUES(
application_id,
entity_code,
event_class_code,
trx_id,
NULL,
summary_tax_line_number,
'ZX_TAX_RATE_NOT_ACTIVE',
l_tax_rate_not_active ||' (Tax_Rate_Code: '||
tax_rate_code||', Tax_Rate_Id: '||tax_rate_id||')',
NULL,
interface_tax_line_id
)
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,
interface_tax_line_id
)
VALUES(
application_id,
entity_code,
event_class_code,
trx_id,
NULL,
summary_tax_line_number,
'ZX_TAX_RATE_PERCENTAGE_INVALID',
l_tax_rate_percentage_invalid ||' (Tax_Rate_Code: '||
tax_rate_code||', Tax_Rate_Id: '||tax_rate_id||')',
NULL,
interface_tax_line_id
)
SELECT application_id,
entity_code,
event_class_code,
trx_id,
summary_tax_line_number,
interface_tax_line_id,
interface_line_id,
trx_line_id,
trx_level_type,
tax_rate_code,
tax_rate_id,
TAX_RATE_CODE_NOT_EXISTS,
DECODE(TAX_RATE_CODE_NOT_EXISTS,'Y','N',TAX_RATE_CODE_NOT_EFFECTIVE) TAX_RATE_CODE_NOT_EFFECTIVE,
DECODE(TAX_RATE_CODE_NOT_EXISTS,'Y','N',TAX_RATE_CODE_NOT_ACTIVE) TAX_RATE_CODE_NOT_ACTIVE,
DECODE(TAX_RATE_CODE_NOT_EXISTS,'Y','N',TAX_RATE_PERCENTAGE_INVALID) TAX_RATE_PERCENTAGE_INVALID
FROM
(SELECT
header.application_id application_id,
header.entity_code entity_code,
header.event_class_code,
header.trx_id trx_id,
taxlines_gt.summary_tax_line_number summary_tax_line_number,
taxlines_gt.summary_tax_line_number interface_tax_line_id,
lines_gt.trx_line_id interface_line_id,
lines_gt.trx_line_id trx_line_id,
lines_gt.trx_level_type trx_level_type,
taxlines_gt.tax_rate_code,
taxlines_gt.tax_rate_id,
-- Check for Rate Code Existence
nvl2(taxlines_gt.tax_rate_code,
CASE WHEN (sd_rates.tax_regime_code is not null and
rate.tax_rate_code is not NULL )
THEN CASE WHEN taxlines_gt.tax_rate_id IS NOT NULL
AND NOT EXISTS ( SELECT 1 FROM zx_rates_b
WHERE tax_rate_id = taxlines_gt.tax_rate_id)
THEN 'Y'
ELSE 'N' END
ELSE 'Y' END,
'N') TAX_RATE_CODE_NOT_EXISTS,
-- Check for Rate Code Effective
nvl2(taxlines_gt.tax_rate_code,
CASE WHEN rate.effective_to IS NOT NULL AND
(header.trx_date NOT BETWEEN rate.effective_from AND rate.effective_to) AND
NOT EXISTS (SELECT 1 FROM zx_rates_b zrb
WHERE zrb.tax_rate_code = rate.tax_rate_code
AND zrb.tax_regime_code = rate.tax_regime_code
AND zrb.tax = rate.tax
AND zrb.tax_status_code = rate.tax_status_code
AND zrb.content_owner_id = rate.content_owner_id
AND zrb.active_flag = 'Y'
AND zrb.tax_rate_id <> rate.tax_rate_id
AND header.trx_date BETWEEN zrb.effective_from AND
nvl(zrb.effective_to, header.trx_date)
)
THEN 'Y'
ELSE 'N' END,
'N') TAX_RATE_CODE_NOT_EFFECTIVE,
-- Check Rate Code is Active
nvl2(taxlines_gt.tax_rate_code,
CASE WHEN rate.active_flag = 'N' AND
(header.trx_date BETWEEN rate.effective_from AND
nvl(rate.effective_to,header.trx_date)) AND
NOT EXISTS (SELECT 1 FROM zx_rates_b zrb
WHERE zrb.tax_rate_code = rate.tax_rate_code
AND zrb.tax_regime_code = rate.tax_regime_code
AND zrb.tax = rate.tax
AND zrb.tax_status_code = rate.tax_status_code
AND zrb.content_owner_id = rate.content_owner_id
AND zrb.active_flag = 'Y'
AND zrb.tax_rate_id <> rate.tax_rate_id
AND header.trx_date BETWEEN zrb.effective_from AND
nvl(zrb.effective_to, header.trx_date)
)
THEN 'Y'
ELSE 'N' END,
'N') TAX_RATE_CODE_NOT_ACTIVE,
-- Check for Rate Percentage
nvl2(taxlines_gt.tax_rate_code,
CASE WHEN taxlines_gt.tax_rate IS NOT NULL AND
rate.percentage_rate <> taxlines_gt.tax_rate AND
nvl(rate.allow_adhoc_tax_rate_flag,'N') <> 'Y' AND
rate.active_flag = 'Y' AND
(header.trx_date BETWEEN rate.effective_from AND
nvl(rate.effective_to, header.trx_date))
THEN 'Y'
ELSE 'N' END,
'N') TAX_RATE_PERCENTAGE_INVALID
FROM ZX_TRX_HEADERS_GT header,
ZX_RATES_B rate ,
ZX_IMPORT_TAX_LINES_GT taxlines_gt,
ZX_TRANSACTION_LINES_GT lines_gt,
ZX_SUBSCRIPTION_DETAILS sd_rates
WHERE taxlines_gt.trx_id = header.trx_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 (taxlines_gt.tax_rate_code IS NOT NULL OR taxlines_gt.tax_rate_id IS NOT NULL)
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 lines_gt.trx_line_id = taxlines_gt.trx_line_id
AND ((taxlines_gt.tax_rate_code IS NOT NULL AND
rate.tax_rate_code = taxlines_gt.tax_rate_code)
OR
(taxlines_gt.tax_rate_id IS NOT NULL AND
rate.tax_rate_id = taxlines_gt.tax_rate_id))
AND (taxlines_gt.tax_status_code IS NULL OR rate.tax_status_code = taxlines_gt.tax_status_code)
AND (taxlines_gt.tax IS NULL OR rate.tax = taxlines_gt.tax)
AND (taxlines_gt.tax_regime_code IS NULL OR rate.tax_regime_code = taxlines_gt.tax_regime_code)
AND rate.tax_regime_code = sd_rates.tax_regime_code (+)
AND (rate.content_owner_id = sd_rates.parent_first_pty_org_id
OR sd_rates.parent_first_pty_org_id is NULL)
AND sd_rates.first_pty_org_id(+) = g_first_pty_org_id
AND (header.trx_date BETWEEN
nvl(sd_rates.effective_from,header.trx_date) AND
nvl(sd_rates.effective_to,header.trx_date)
)
AND (NVL(sd_rates.view_options_code,'NONE') IN ('NONE', 'VFC')
OR (NVL(sd_rates.view_options_code, 'VFR') = 'VFR'
AND NOT EXISTS
(SELECT 1
FROM zx_rates_b b
WHERE b.tax_regime_code = rate.tax_regime_code
AND b.tax = rate.tax
AND b.tax_status_code = rate.tax_status_code
AND b.tax_rate_code = rate.tax_rate_code
AND b.content_owner_id = sd_rates.first_pty_org_id
)
)
)
);
INSERT ALL
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,
interface_tax_line_id
)
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,
interface_tax_line_id
)
/* 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,
interface_tax_line_id
)
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,
interface_tax_line_id
)
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,
interface_tax_line_id
)
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,
interface_tax_line_id
)
/* 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,
lines_gt.interface_line_id,
taxlines_gt.interface_tax_line_id,
taxlines_gt.summary_tax_line_number,
-- 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 = taxlines_gt.application_id
AND entity_code = taxlines_gt.entity_code
AND event_class_code = taxlines_gt.event_class_code
AND trx_id = taxlines_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 =
taxlines_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 = taxlines_gt.tax_regime_code
AND taxes.tax = taxlines_gt.tax
AND taxes.def_inclusive_tax_flag <> taxlines_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 = taxlines_gt.tax_regime_code
AND zl.tax = taxlines_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 taxlines_gt
WHERE
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 taxlines_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 lines_gt.trx_line_id = taxlines_gt.trx_line_id
AND (taxlines_gt.tax_line_allocation_flag = 'Y'
AND EXISTS
(SELECT 1
FROM zx_trx_tax_link_gt
WHERE application_id = taxlines_gt.application_id
AND entity_code = taxlines_gt.entity_code
AND event_class_code = taxlines_gt.event_class_code
AND trx_id = taxlines_gt.trx_id
AND summary_tax_line_number = taxlines_gt.summary_tax_line_number
AND trx_line_id = lines_gt.trx_line_id
AND trx_level_type = lines_gt.trx_level_type
) OR
(taxlines_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'
)
);