DBA Data[Home] [Help]

PACKAGE BODY: APPS.JL_ZZ_TAX_VALIDATE_PKG

Source


1 package BODY jl_zz_tax_validate_pkg as
2 /* $Header: jlzzdefvalpkgb.pls 120.25.12020000.2 2012/12/20 07:21:26 ssohal ship $ */
3 
4   procedure  validate_pfc_tbc (x_return_status OUT NOCOPY VARCHAR2);
5 
6   g_current_runtime_level NUMBER;
7   g_level_statement       NUMBER;
8   g_level_procedure       NUMBER;
9   g_level_event           NUMBER;
10   g_level_exception       NUMBER;
11   g_level_unexpected      NUMBER;
12 
13   g_first_pty_org_id      NUMBER;  -- Added for Bug#7530930
14 
15   g_delimiter             zx_fc_types_b.delimiter%type;
16 
17   g_tax_lines_count       NUMBER;
18 
19   l_regime_not_exists                     varchar2(2000);
20   l_regime_not_effective                  varchar2(2000);
21   l_regime_not_eff_in_subscr              varchar2(2000);
22   l_tax_not_exists                        varchar2(2000);
23   l_tax_not_live                          varchar2(2000);
24   l_tax_not_effective                     varchar2(2000);
25   l_tax_status_not_exists                 varchar2(2000);
26   l_tax_status_not_effective              varchar2(2000);
27   l_tax_rate_not_exists                   varchar2(2000);
28   l_tax_rate_not_effective                varchar2(2000);
29   l_tax_rate_not_active                   varchar2(2000);
30 --l_tax_rate_code_not_exists              varchar2(2000);
31 --l_tax_rate_code_not_effective           varchar2(2000);
32 --l_tax_rate_code_not_active              varchar2(2000);
33   l_tax_rate_percentage_invalid           varchar2(2000);
34   l_evnt_cls_mpg_invalid                  varchar2(2000);
35   l_exchg_info_missing                    varchar2(2000);
36   l_line_class_invalid                    varchar2(2000);
37   l_trx_line_type_invalid                 varchar2(2000);
38   l_line_amt_incl_tax_invalid             varchar2(2000);
39   l_trx_biz_fc_code_not_exists            varchar2(2000);
40   l_trx_biz_fc_code_not_effect            varchar2(2000);
41   l_prd_fc_code_not_exists                varchar2(2000);
42   l_prd_category_not_exists               varchar2(2000);
43   l_ship_to_party_not_exists              varchar2(2000);
44   l_ship_frm_party_not_exits              varchar2(2000);
45   l_bill_to_party_not_exists              varchar2(2000);
46   l_shipto_party_site_not_exists          varchar2(2000);
47   l_billto_party_site_not_exists          varchar2(2000);
48   l_billfrm_party_site_not_exist          varchar2(2000);
49   l_tax_multialloc_to_sameln              varchar2(2000);
50   l_imptax_multialloc_to_sameln           varchar2(2000);
51   l_tax_incl_flag_mismatch                varchar2(2000);
52   l_imp_tax_missing_in_adjust_to          varchar2(2000);
53 --l_product_category_na_for_lte           varchar2(2000);
54   l_user_def_fc_na_for_lte                varchar2(2000);
55   l_document_fc_na_for_lte                varchar2(2000);
56   l_indended_use_na_for_lte               varchar2(2000);
57   l_product_type_na_for_lte               varchar2(2000);
58   l_tax_jur_code_na_for_lte               varchar2(2000);
59 
60 PROCEDURE default_and_validate_tax_attr(
61                   p_api_version      IN            NUMBER,
62                   p_init_msg_list    IN            VARCHAR2,
63                   p_commit           IN            VARCHAR2,
64                   p_validation_level IN            VARCHAR2,
65                   x_return_status       OUT NOCOPY VARCHAR2,
66                   x_msg_count           OUT NOCOPY NUMBER,
67                   x_msg_data            OUT NOCOPY VARCHAR2) IS
68 
69   CURSOR c_delimiter IS
70   SELECT delimiter
71   FROM   zx_fc_types_b
72   WHERE  classification_type_code ='TRX_BUSINESS_CATEGORY';
73 
74   -- Added for Bug#7530930
75   l_le_id  NUMBER;
76   l_ou_id  NUMBER;
77   l_return_status VARCHAR2(1);
78   l_err_count NUMBER := 0;
79 BEGIN
80 
81 /* It is assumed that TSRM will set the security context before calling this API
82    So the same logic is not coded here
83 */
84      x_return_status := FND_API.G_RET_STS_SUCCESS;
85      g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
86      IF (g_level_procedure >= g_current_runtime_level ) THEN
87         FND_LOG.STRING(g_level_procedure,
88                        'JL.PL/SQL.JL_ZZ_TAX_VALIDATE_PKG.DEFAULT_AND_VALIDATE_TAX_ATTR',
89                        'JL_ZZ_TAX_VALIDATE_PKG.DEFAULT_AND_VALIDATE_TAX_ATTR(+)');
90      END IF;
91 
92      OPEN c_delimiter;
93      FETCH c_delimiter INTO g_delimiter;
94      CLOSE c_delimiter;
95 
96      -- Start : Code added to get the First-Party-Org-Id  -- bug#7530930
97      BEGIN
98          SELECT legal_entity_id , internal_organization_id
99          INTO   l_le_id, l_ou_id
100          FROM   ZX_TRX_HEADERS_GT Header
101          WHERE  rownum = 1;
102      EXCEPTION
103          WHEN NO_DATA_FOUND THEN
104              IF (g_level_exception >= g_current_runtime_level ) THEN
105                  FND_LOG.STRING(g_level_exception,
106                         'JL.PL/SQL.JL_ZZ_TAX_VALIDATE_PKG.DEFAULT_AND_VALIDATE_TAX_ATTR',
107                         'First Party Org Id : Not able to fetch OU and LE');
108              END IF;
109          app_exception.raise_exception;
110      END;
111 
112      IF ( g_level_statement >= g_current_runtime_level) THEN
113        FND_LOG.STRING(g_level_statement,'JL.PL/SQL.JL_ZZ_TAX_VALIDATE_PKG.DEFAULT_AND_VALIDATE_TAX_ATTR',
114        'Call ZX_TCM_PTP_PKG.GET_TAX_SUBSCRIBER() with OU: '||TO_CHAR(l_ou_id)||' and LE: '||TO_CHAR(l_le_id));
115      END IF;
116 
117      ZX_TCM_PTP_PKG.GET_TAX_SUBSCRIBER(l_le_id,
118                                        l_ou_id,
119                                        g_first_pty_org_id,
120                                        l_return_status);
121 
122      IF ( g_level_statement >= g_current_runtime_level) THEN
123        FND_LOG.STRING(g_level_statement,'JL.PL/SQL.JL_ZZ_TAX_VALIDATE_PKG.DEFAULT_AND_VALIDATE_TAX_ATTR',
124        'G_FIRST_PTY_ORG_ID: '||TO_CHAR(g_first_pty_org_id));
125      END IF;
126 
127      IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
128        IF (g_level_exception >= g_current_runtime_level ) THEN
129            FND_LOG.STRING(g_level_exception,
130                    'JL.PL/SQL.JL_ZZ_TAX_VALIDATE_PKG.DEFAULT_AND_VALIDATE_TAX_ATTR',
131                     'Get Tax Subscriber : Returned Error Status');
132        END IF;
133      END IF;
134      -- End : Code added to get the First-Party-Org-Id -- Bug#7530930
135 
136      -- Get the count of manual tax lines in global variable
137      SELECT Count(*)
138      INTO   g_tax_lines_count
139      FROM   zx_import_tax_lines_gt;
140 
141      validate_pfc_tbc  (x_return_status);
142 
143      default_tax_attr  (x_return_status);
144 
145      validate_tax_attr (x_return_status);
146 
147      -- Update the validation_check_flag to N for problematic trxs
148      -- so that these trxs should not be picked up for tax processing
149      UPDATE zx_trx_headers_gt
150      SET    validation_check_flag = 'N'
151      WHERE  trx_id IN (SELECT DISTINCT trx_id FROM zx_validation_errors_gt);
152 
153      IF ( SQL%ROWCOUNT > 0 ) THEN
154        g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
155        IF (g_level_statement >= g_current_runtime_level) THEN
156            FND_LOG.STRING(g_level_statement,'ZX_VALIDATE_API_PKG.VALIDATE_TAX_ATTR',
157            'Updated the validation_check_flag to N in Zx_Trx_Headers_GT for '||to_char(SQL%ROWCOUNT)||' trx(s).');
158        END IF;
159      END IF ;
160 
161      -- Printing Error Messages in Zx_Validation_Errors_Gt
162      SELECT Count(*)
163      INTO   l_err_count
164      FROM   zx_validation_errors_gt;
165 
166      IF l_err_count > 0 THEN
167        IF (g_level_procedure >= g_current_runtime_level ) THEN
168           FND_LOG.STRING(g_level_procedure,
169                          'JL.PL/SQL.JL_ZZ_TAX_VALIDATE_PKG.DEFAULT_AND_VALIDATE_TAX_ATTR',
170                          'Error Message Count : '||l_err_count);
171        END IF;
172        FOR rec IN (SELECT trx_id, message_text FROM ZX_VALIDATION_ERRORS_GT) LOOP
173          IF (g_level_procedure >= g_current_runtime_level ) THEN
174            FND_LOG.STRING(g_level_procedure,
175                           'JL.PL/SQL.JL_ZZ_TAX_VALIDATE_PKG.DEFAULT_AND_VALIDATE_TAX_ATTR',
176                           'Trx_ID : '||rec.trx_id||', Error : '||rec.message_text);
177          END IF;
178        END LOOP;
179      END IF;
180 
181      IF (g_level_procedure >= g_current_runtime_level ) THEN
182         FND_LOG.STRING(g_level_procedure,
183                        'JL.PL/SQL.JL_ZZ_TAX_VALIDATE_PKG.DEFAULT_AND_VALIDATE_TAX_ATTR',
184                        'RETURN_STATUS : '||x_return_status);
185         FND_LOG.STRING(g_level_procedure,
186                        'JL.PL/SQL.JL_ZZ_TAX_VALIDATE_PKG.DEFAULT_AND_VALIDATE_TAX_ATTR',
187                        'JL_ZZ_TAX_VALIDATE_PKG.DEFAULT_AND_VALIDATE_TAX_ATTR(-)');
188      END IF;
189 
190   EXCEPTION
191          WHEN OTHERS THEN
192               IF (g_level_unexpected >= g_current_runtime_level ) THEN
193                   FND_LOG.STRING(g_level_unexpected,
194                                  'JL_ZZ_TAX_VALIDATE_PKG.DEFAULT_AND_VALIDATE_TAX_ATTR',
195                                  sqlerrm);
196               END IF;
197               x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
198               app_exception.raise_exception;
199 
200 END default_and_validate_tax_attr;
201 
202 -- Validations for Product Fiscal Classification code and
203 -- Transaction Business Category (done before defaulting, as we would not need
204 -- to validate the defaulted values). This is done in a separate query as we
205 -- do not need to join to other unnecessary tables.
206 
207 Procedure VALIDATE_PFC_TBC( x_return_status       OUT NOCOPY VARCHAR2) IS
208 
209   CURSOR c_delimiter_prod_cat IS
210    SELECT delimiter
211    FROM   zx_fc_types_b
212    WHERE  classification_type_code ='PRODUCT_CATEGORY';
213 
214   l_delimiter_prod_cat   zx_fc_types_b.delimiter%type;
215 
216 BEGIN
217 
218     x_return_status := FND_API.G_RET_STS_SUCCESS;
219     IF (g_level_procedure >= g_current_runtime_level ) THEN
220        FND_LOG.STRING(g_level_procedure,
221                       'JL.PL/SQL.JL_ZZ_TAX_VALIDATE_PKG.VALIDATE_PFC_TBC',
222                       'JL_ZZ_TAX_VALIDATE_PKG.VALIDATE_PFC_TBC(+)');
223     END IF;
224 
225     OPEN c_delimiter_prod_cat;
226     FETCH c_delimiter_prod_cat INTO l_delimiter_prod_cat;
227     CLOSE c_delimiter_prod_cat;
228 
229     INSERT ALL
230     INTO ZX_VALIDATION_ERRORS_GT(
231                 application_id,
232                 entity_code,
233                 event_class_code,
234                 trx_id,
235                 trx_line_id,
236                 message_name,
237                 message_text,
238                 trx_level_type,
239                 interface_line_id)
240      SELECT
241                 lines_gt.application_id,
242                 lines_gt.entity_code,
243                 lines_gt.event_class_code,
244                 lines_gt.trx_id,
245                 lines_gt.trx_line_id,
246                 'ZX_TRX_BIZ_FC_CODE_NOT_EXIST',
247                 l_trx_biz_fc_code_not_exists||'('||lines_gt.trx_business_category||')',
248                 lines_gt.trx_level_type,
249                 lines_gt.interface_line_id
250      FROM       zx_transaction_lines_gt       lines_gt
251      WHERE      lines_gt.trx_business_category is NOT NULL
252      AND        NOT EXISTS
253                 (SELECT 1
254                  FROM
255                       zx_evnt_cls_mappings    evntmap,
256                       jl_zz_ar_tx_att_cls     tac,
257                       jl_zz_ar_tx_categ       tc,
258                       ar_system_parameters    asp
259                where
260                       lines_gt.application_id    = evntmap.application_id
261                  and  lines_gt.entity_code    = evntmap.entity_code
262                  and  lines_gt.event_class_code  = evntmap.event_class_code
263                  and  tac.TAX_ATTR_CLASS_TYPE = 'TRANSACTION_CLASS'
264                  and  tac.TAX_ATTR_CLASS_CODE =  SUBSTR(lines_gt.trx_business_category,
265                             INSTR(lines_gt.trx_business_category, g_delimiter, 1) +1 )
266                  and  tac.tax_category_id = tc.tax_category_id
267                  and  tc.tax_rule_set = asp.global_attribute13
268                  and  tac.enabled_flag = 'Y'
269                  and  tac.org_id = asp.org_id
270                  and  tc.org_id = asp.org_id);
271 
272     IF (g_level_procedure >= g_current_runtime_level ) THEN
273       FND_LOG.STRING(g_level_procedure,
274                      'JL.PL/SQL.JL_ZZ_TAX_VALIDATE_PKG.VALIDATE_PFC_TBC',
275                      'Transaction Business Category Validation Errors: '|| To_Char(SQL%ROWCOUNT) );
276     END IF;
277 
278     INSERT ALL
279     INTO ZX_VALIDATION_ERRORS_GT(
280                 application_id,
281                 entity_code,
282                 event_class_code,
283                 trx_id,
284                 trx_line_id,
285                 message_name,
286                 message_text,
287                 trx_level_type,
288                 interface_line_id)
289      SELECT
290                 lines_gt.application_id,
291                 lines_gt.entity_code,
292                 lines_gt.event_class_code,
293                 lines_gt.trx_id,
294                 lines_gt.trx_line_id,
295                 'ZX_PRODUCT_FC_CODE_NOT_EXIST',
296                 l_prd_fc_code_not_exists||'('||lines_gt.product_fisc_classification||')',
297                 lines_gt.trx_level_type,
298                 lines_gt.interface_line_id
299      FROM
300                 zx_transaction_lines_gt       lines_gt
301      WHERE      lines_gt.product_fisc_classification is NOT NULL
302         AND     NOT EXISTS
303                 (
304                 SELECT 1
305                 FROM
306                       zx_trx_headers_gt             header,
307                       zx_evnt_cls_mappings          evntmap,
308                       FND_LOOKUPS                   LK,
309                       JL_ZZ_AR_TX_FSC_CLS           FSC
310                 where
311                       lines_gt.application_id    = header.application_id
312                   and lines_gt.entity_code       = header.entity_code
313                   and lines_gt.event_class_code  = header.event_class_code
314                   and lines_gt.trx_id            = header.trx_id
315                   and lines_gt.application_id    = evntmap.application_id
316                   and lines_gt.entity_code       = evntmap.entity_code
317                   and lines_gt.event_class_code  = evntmap.event_class_code
318                   and lk.lookup_type = 'JLZZ_AR_TX_FISCAL_CLASS_CODE'
319                   and lk.enabled_flag = 'Y'
320                   and lk.lookup_code =  lines_gt.product_fisc_classification
321                   and FSC.FISCAL_CLASSIFICATION_CODE = lk.LOOKUP_CODE
322                   and fsc.enabled_Flag = 'Y'
323                   and nvl(lk.start_date_active,header.trx_date) <= header.trx_date
324                   and NVL(lk.END_DATE_ACTIVE,header.trx_date) >= header.trx_date
325                   );
326 
327     IF (g_level_procedure >= g_current_runtime_level ) THEN
328       FND_LOG.STRING(g_level_procedure,
329                      'JL.PL/SQL.JL_ZZ_TAX_VALIDATE_PKG.VALIDATE_PFC_TBC',
330                      'Product Fiscal Classification Validation Errors: '|| To_Char(SQL%ROWCOUNT) );
331     END IF;
332 
333     INSERT ALL
334     INTO ZX_VALIDATION_ERRORS_GT(
335                 application_id,
336                 entity_code,
337                 event_class_code,
338                 trx_id,
339                 trx_line_id,
340                 message_name,
341                 message_text,
342                 trx_level_type,
343                 interface_line_id)
344      SELECT
345                 lines_gt.application_id,
346                 lines_gt.entity_code,
347                 lines_gt.event_class_code,
348                 lines_gt.trx_id,
349                 lines_gt.trx_line_id,
350                 'ZX_PRODUCT_CATEGORY_NOT_EXIST',
351                 l_prd_category_not_exists||'('||lines_gt.product_category||')',
352                 lines_gt.trx_level_type,
353                 lines_gt.interface_line_id
354      FROM
355                 zx_transaction_lines_gt       lines_gt
356      WHERE      lines_gt.product_category IS NOT NULL
357        AND      NOT EXISTS
358                 (
359                 SELECT 1
360                 FROM
361                       zx_trx_headers_gt             header,
362                       zx_evnt_cls_mappings          evntmap,
363                       FND_LOOKUPS                   LK,
364                       JL_ZZ_AR_TX_FSC_CLS           FSC
365                 where
366                       lines_gt.application_id    = header.application_id
367                   and lines_gt.entity_code       = header.entity_code
368                   and lines_gt.event_class_code  = header.event_class_code
369                   and lines_gt.trx_id            = header.trx_id
370                   and lines_gt.application_id    = evntmap.application_id
371                   and lines_gt.entity_code       = evntmap.entity_code
372                   and lines_gt.event_class_code  = evntmap.event_class_code
373                   and lk.lookup_type = 'JLZZ_AR_TX_FISCAL_CLASS_CODE'
374                   and lk.enabled_flag = 'Y'
375                   and lk.lookup_code = SUBSTR(lines_gt.product_category,
376                             INSTR(lines_gt.product_category, l_delimiter_prod_cat, 1) +1 )
377                   and fsc.fiscal_classification_code = lk.lookup_code
378                   and fsc.enabled_Flag = 'Y'
379                   and nvl(lk.start_date_active,header.trx_date) <= header.trx_date
380                   and NVL(lk.end_date_active,header.trx_date) >= header.trx_date
381                   );
382 
383     IF (g_level_procedure >= g_current_runtime_level ) THEN
384       FND_LOG.STRING(g_level_procedure,
385                      'JL.PL/SQL.JL_ZZ_TAX_VALIDATE_PKG.VALIDATE_PFC_TBC',
386                      'Product Category Validation Errors: '|| To_Char(SQL%ROWCOUNT) );
387     END IF;
388 
389     IF (g_level_procedure >= g_current_runtime_level ) THEN
390        FND_LOG.STRING(g_level_procedure,
391                       'JL.PL/SQL.JL_ZZ_TAX_VALIDATE_PKG.VALIDATE_PFC_TBC',
392                       'JL_ZZ_TAX_VALIDATE_PKG.VALIDATE_PFC_TBC(-)');
393     END IF;
394 
395   EXCEPTION
396          WHEN OTHERS THEN
397               IF (g_level_unexpected >= g_current_runtime_level ) THEN
398                   FND_LOG.STRING(g_level_unexpected,
399                                  'JL_ZZ_TAX_VALIDATE_PKG.VALIDATE_PFC_TBC',
400                                  sqlerrm);
401               END IF;
402               x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
403               app_exception.raise_exception;
404 
405 END VALIDATE_PFC_TBC;
406 
407 
408 PROCEDURE default_tax_attr (x_return_status OUT NOCOPY VARCHAR2) IS
409 
410   l_line_level_action  ZX_TRANSACTION_LINES_GT.line_level_action%type;
411   l_source_trx_id      ZX_TRANSACTION_LINES_GT.source_trx_id%type;
412 
413 
414 BEGIN
415 
416     x_return_status := FND_API.G_RET_STS_SUCCESS;
417     g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
418     IF (g_level_procedure >= g_current_runtime_level ) THEN
419        FND_LOG.STRING(g_level_procedure,
420                       'JL.PL/SQL.JL_ZZ_TAX_VALIDATE_PKG.DEFAULT_TAX_ATTR',
421                       'JL_ZZ_TAX_VALIDATE_PKG.DEFAULT_TAX_ATTR(+)');
422     END IF;
423 
424     -- Defaulting the taxation country
425     -- Bugfix 3971179
426     UPDATE ZX_TRX_HEADERS_GT Header
427        SET default_taxation_country =
428             (SELECT
429                  decode(syspa.global_attribute13,
430                             'ARGENTINA', 'AR',
431                             'COLOMBIA',  'CO',
432                             'BRAZIL',    'BR',
433                              NULL)
434              FROM ar_system_parameters_all syspa
435              WHERE  org_id = Header.internal_organization_id
436                AND  global_attribute_category like 'JL%')
437     WHERE Header.default_taxation_country is NULL;
438 
439  -- default the tax attributes only if there is at least on tax line being imported
440  IF nvl(g_tax_lines_count,0) > 0 THEN
441 
442     --Defaulting for Tax Regime Code and Tax on imported tax lines
443 
444     --In case of LTE/O2C, the tax lines imoprted are detail tax lines and
445     --will always have trx_line_id information
446 
447     IF (g_level_statement >= g_current_runtime_level ) THEN
448         FND_LOG.STRING(g_level_statement,
449                 'JL.PL/SQL.JL_ZZ_TAX_VALIDATE_PKG.DEFAULT_TAX_ATTR',
450                 'JL_ZZ_TAX_VALIDATE_PKG.DEFAULT_TAX_ATTR: Defaulting for Tax Regime Code and Tax');
451     END IF;
452 
453     -- Defaulting the Tax_regime_code and Tax if tax_rate_code is passed
454     MERGE INTO ZX_IMPORT_TAX_LINES_GT  TaxLines_gt
455     USING   (SELECT rates.tax_regime_code  tax_regime_code,
456                     rates.tax              tax,
457                     TaxLines.trx_id        trx_id,
458                     TaxLines.summary_tax_line_number  summary_tax_line_number
459              FROM
460                     ZX_IMPORT_TAX_LINES_GT TaxLines,
461                     ZX_TRX_HEADERS_GT Header,
462                     AR_VAT_TAX rates
463              WHERE
464                  TaxLines.tax_rate_code    = rates.tax_code(+)
465              AND TaxLines.tax_rate_code IS NOT NULL
466              AND Header.trx_date between nvl(rates.start_date,Header.trx_date)
467                                     and  nvl(rates.end_date,Header.trx_date)
468              AND TaxLines.application_id   = Header.application_id
469              AND TaxLines.entity_code      = Header.entity_code
470              AND TaxLines.event_class_code = Header.event_class_code
471              AND TaxLines.trx_id = Header.trx_id
472                ) Temp
473     ON        (  TaxLines_gt.trx_id = Temp.trx_id AND
474                  TaxLines_gt.summary_tax_line_number = Temp.summary_tax_line_number )
475     WHEN MATCHED THEN
476         UPDATE SET
477           tax_regime_code = nvl(TaxLines_gt.tax_regime_code, Temp.tax_regime_code),
478           tax             = nvl(TaxLines_gt.tax, Temp.tax)
479     WHEN NOT MATCHED THEN
480         INSERT(tax) VALUES(NULL);
481 
482     IF (g_level_statement >= g_current_runtime_level ) THEN
483         FND_LOG.STRING(g_level_statement,
484                 'JL.PL/SQL.JL_ZZ_TAX_VALIDATE_PKG.DEFAULT_TAX_ATTR',
485                 'DML performed on rows(1):'||SQL%ROWCOUNT);
486     END IF;
487 
488     -- Defaulting the Tax_regime_code and Tax if tax_rate_id is passed
489     MERGE INTO ZX_IMPORT_TAX_LINES_GT  TaxLines_gt
490     USING   (SELECT rates.tax_regime_code  tax_regime_code,
491                     rates.tax              tax,
492                     TaxLines.trx_id        trx_id,
493                     TaxLines.summary_tax_line_number  summary_tax_line_number
494              FROM
495                     ZX_IMPORT_TAX_LINES_GT TaxLines,
496                     ZX_TRX_HEADERS_GT Header,
497                     AR_VAT_TAX rates
498              WHERE
499                  TaxLines.tax_rate_id    = rates.vat_tax_id(+)
500              AND TaxLines.tax_rate_id IS NOT NULL
501              AND (TaxLines.tax_regime_code IS NULL OR TaxLines.tax IS NULL)
502              AND Header.trx_date between nvl(rates.start_date,Header.trx_date)
503                                     and  nvl(rates.end_date,Header.trx_date)
504              AND TaxLines.application_id   = Header.application_id
505              AND TaxLines.entity_code      = Header.entity_code
506              AND TaxLines.event_class_code = Header.event_class_code
507              AND TaxLines.trx_id = Header.trx_id
508                ) Temp
509     ON        (  TaxLines_gt.trx_id = Temp.trx_id AND
510                  TaxLines_gt.summary_tax_line_number = Temp.summary_tax_line_number )
511     WHEN MATCHED THEN
512         UPDATE SET
513           tax_regime_code = nvl(TaxLines_gt.tax_regime_code, Temp.tax_regime_code),
514           tax             = nvl(TaxLines_gt.tax, Temp.tax)
515     WHEN NOT MATCHED THEN
516         INSERT(tax) VALUES(NULL);
517 
518     IF (g_level_statement >= g_current_runtime_level ) THEN
519         FND_LOG.STRING(g_level_statement,
520                 'JL.PL/SQL.JL_ZZ_TAX_VALIDATE_PKG.DEFAULT_TAX_ATTR',
521                 'DML performed on rows(2):'||SQL%ROWCOUNT);
522     END IF;
523 
524     --Defaulting for Tax Status Code on imported tax lines
525     IF (g_level_statement >= g_current_runtime_level ) THEN
526         FND_LOG.STRING(g_level_statement,
527                 'JL.PL/SQL.JL_ZZ_TAX_VALIDATE_PKG.DEFAULT_TAX_ATTR',
528                 'JL_ZZ_TAX_VALIDATE_PKG.DEFAULT_TAX_ATTR: Defaulting for Tax Status Code');
529     END IF;
530 
531     MERGE INTO ZX_IMPORT_TAX_LINES_GT  TaxLines_gt
532     USING   (SELECT Rates.tax_status_code  tax_status_code,
533                     TaxLines.trx_id        trx_id,
534                     TaxLines.summary_tax_line_number  summary_tax_line_number
535              FROM
536                     ZX_IMPORT_TAX_LINES_GT TaxLines,
537                     AR_VAT_TAX Rates,
538                     ZX_TRX_HEADERS_GT Header
539              WHERE
540                     Taxlines.tax_regime_Code = Rates.tax_regime_code(+)
541              AND    Taxlines.tax             = Rates.tax(+)
542              AND    ((Taxlines.tax_rate_code IS NOT NULL AND Taxlines.tax_rate_code = rates.tax_code)
543                     OR (Taxlines.tax_rate_id IS NOT NULL AND Taxlines.tax_rate_id = rates.vat_tax_id))
544              AND    Header.trx_date BETWEEN nvl(Rates.start_date,Header.trx_date)
545                                     AND  nvl(Rates.end_date,Header.trx_date)
546              AND    TaxLines.application_id = Header.application_id
547              AND    TaxLines.entity_code = Header.entity_code
548              AND    TaxLines.event_class_code = Header.event_class_code
549              AND    TaxLines.trx_id = Header.trx_id
550                ) Temp
551     ON        (  TaxLines_gt.trx_id = Temp.trx_id AND
552                  TaxLines_gt.summary_tax_line_number = Temp.summary_tax_line_number )
553     WHEN MATCHED THEN
554         UPDATE SET
555             tax_status_code = nvl(TaxLines_gt.tax_status_code, Temp.tax_status_code)
556     WHEN NOT MATCHED THEN
557         INSERT(tax) VALUES(NULL);
558 
559     IF (g_level_statement >= g_current_runtime_level ) THEN
560         FND_LOG.STRING(g_level_statement,
561                 'JL.PL/SQL.JL_ZZ_TAX_VALIDATE_PKG.DEFAULT_TAX_ATTR',
562                 'DML performed on rows:'||SQL%ROWCOUNT);
563     END IF;
564 
565     -- Defaulting for Tax Rate Code, Tax Rate Id, Percentage Rate on imported tax lines
566     IF (g_level_statement >= g_current_runtime_level ) THEN
567         FND_LOG.STRING(g_level_statement,
568                 'JL.PL/SQL.JL_ZZ_TAX_VALIDATE_PKG.DEFAULT_TAX_ATTR',
569                 'JL_ZZ_TAX_VALIDATE_PKG.DEFAULT_TAX_ATTR: Defaulting for Tax Rate Code, Tax Rate Id, Percentage Rate');
570     END IF;
571 
572     MERGE INTO ZX_IMPORT_TAX_LINES_GT  TaxLines_gt
573     USING  (SELECT Rates.tax_code,
574                Rates.vat_tax_id,
575                Rates.tax_rate,
576                TaxLines.trx_id,
577                TaxLines.summary_tax_line_number
578        FROM
579                AR_VAT_TAX Rates,
580                ZX_IMPORT_TAX_LINES_GT  TaxLines,
581                ZX_TRX_HEADERS_GT Header
582        WHERE
583            Taxlines.tax_regime_Code = Rates.tax_regime_code(+)
584        AND Taxlines.tax             = Rates.tax(+)
585        AND Taxlines.tax_status_code = Rates.tax_status_code(+)
586        AND ((Taxlines.tax_rate_code IS NOT NULL AND Taxlines.tax_rate_code = rates.tax_code)
587            OR (Taxlines.tax_rate_id IS NOT NULL AND Taxlines.tax_rate_id = rates.vat_tax_id))
588        AND Rates.enabled_flag  = 'Y'
589        AND Header.trx_date BETWEEN nvl(Rates.start_date,Header.trx_date)
590                            AND nvl(Rates.end_date, Header.trx_date)
591        AND TaxLines.application_id   = Header.application_id
592        AND TaxLines.entity_code      = Header.entity_code
593        AND TaxLines.event_class_code = Header.event_class_code
594        AND TaxLines.trx_id = Header.trx_id
595        ) Temp
596     ON ( TaxLines_gt.trx_id = Temp.trx_id AND
597          TaxLines_gt.summary_tax_line_number = Temp.summary_tax_line_number )
598     WHEN MATCHED THEN
599     	 UPDATE SET
600     	 tax_rate_code = nvl(TaxLines_gt.tax_rate_code,Temp.tax_code),
601     	 tax_rate_id   = nvl(TaxLines_gt.tax_rate_id,Temp.vat_tax_id),
602     	 tax_rate      = nvl(TaxLines_gt.tax_rate,Temp.tax_rate)
603     WHEN NOT MATCHED THEN
604                          INSERT(tax) VALUES(NULL);
605 
606     IF (g_level_statement >= g_current_runtime_level ) THEN
607         FND_LOG.STRING(g_level_statement,
608                 'JL.PL/SQL.JL_ZZ_TAX_VALIDATE_PKG.DEFAULT_TAX_ATTR',
609                 'DML performed on rows:'||SQL%ROWCOUNT);
610     END IF;
611 
612    --  Default tax amount if it is NULL and tax rate is specified
613    MERGE INTO ZX_IMPORT_TAX_LINES_GT  TaxLines_gt
614    USING  (SELECT
615              TaxLines.tax_rate,
616              TaxLines.tax_amt_included_flag,
617              TaxLines.trx_id,
618              Lines.line_amt,
619              TaxLines.summary_tax_line_number
620    FROM
621           ZX_IMPORT_TAX_LINES_GT TaxLines,
622           ZX_TRX_HEADERS_GT Header,
623           ZX_TRANSACTION_LINES_GT Lines
624    WHERE
625       Taxlines.tax_line_allocation_flag  = 'N' AND
626       TaxLines.tax_amt IS NULL AND
627       TaxLines.tax_rate IS NOT NULL AND
628       TaxLines.application_id  = Header.application_id AND
629       TaxLines.entity_code  = Header.entity_code AND
630       TaxLines.event_class_code  = Header.event_class_code AND
631       TaxLines.trx_id = Header.trx_id AND
632       Lines.application_id = Header.application_id AND
633       Lines.entity_code = Header.entity_code AND
634       Lines.event_class_code = Header.event_class_code AND
635       Lines.trx_id = Header.trx_id AND
636       Lines.trx_line_id = TaxLines.trx_line_id
637      ) Temp
638    ON ( TaxLines_gt.trx_id = Temp.trx_id AND
639         TaxLines_gt.summary_tax_line_number = Temp.summary_tax_line_number )
640        WHEN MATCHED THEN
641                  UPDATE SET
642                     tax_amt = CASE WHEN (temp.tax_amt_included_flag  <> 'Y')
643                                      THEN  (temp.tax_rate / 100 ) * temp.line_amt
644                                    WHEN (temp.tax_rate = 0 )
645                                      THEN  0
646                                    ELSE temp.tax_rate * temp.line_amt / ( 100 + temp.tax_rate )
647                                    END
648        WHEN NOT MATCHED THEN
649                      INSERT(tax) VALUES(NULL);
650 
651  END IF; -- nvl(g_tax_lines_count,0) > 0
652 
653 /* Defaulting for Transaction Business Category, Product Category  and
654    Product Fiscal Classification on transaction lines */
655 
656    -- In case where the line is not a memo line, default the Transaction Business
657    -- Category and Product Fiscal Classification from mtl_system_items / mtl_item_categories.
658    -- If the line is a memo line, then populate Transaction Business Category and
659    -- Product Category from ar_memo_lines.
660 
661    IF (g_level_statement >= g_current_runtime_level ) THEN
662         FND_LOG.STRING(g_level_statement,
663                 'JL.PL/SQL.JL_ZZ_TAX_VALIDATE_PKG.DEFAULT_TAX_ATTR',
664                 'JL_ZZ_TAX_VALIDATE_PKG.DEFAULT_TAX_ATTR:
665         Defaulting for Transaction Business Category and Product Category,
666         Product Fiscal Classification');
667    END IF;
668 
669    -- Bug#5639478-
670 
671    SELECT line_level_action
672      INTO l_line_level_action
673      FROM ZX_TRANSACTION_LINES_GT
674      WHERE rownum             = 1;
675 
676   IF l_line_level_action = 'COPY_AND_CREATE' THEN
677 
678      UPDATE ZX_TRANSACTION_LINES_GT L
679      SET (L.product_fisc_classification,
680           L.trx_business_category,
681           L.product_category,
682           L.output_tax_classification_code ) =
683           (SELECT D.product_fisc_classification,
684                   D.trx_business_category,
685                   D.product_category,
686                   D.output_tax_classification_code
687              FROM ZX_LINES_DET_FACTORS D
688             WHERE D.event_class_code = L.source_event_class_code
689               AND D.application_id   = L.source_application_id
690               AND D.entity_code      = L.source_entity_code
691               AND D.trx_id           = L.source_trx_id
692               AND D.trx_line_id      = L.source_line_id
693               AND D.trx_level_type   = L.source_trx_level_type )
694      WHERE L.source_trx_id IS NOT NULL
695        AND L.line_level_action = 'COPY_AND_CREATE';
696   ELSE
697      -- keep current logic
698 
699 
700    MERGE INTO  ZX_TRANSACTION_LINES_GT Lines
701    USING (SELECT
702             fc.classification_code  product_fisc_class,
703             Lines.trx_id,
704             Lines.trx_line_id,
705             Lines.trx_level_type
706         FROM
707             zx_fc_product_fiscal_v     fc,
708             mtl_item_categories        mic,
709             zx_transaction_lines_gt    lines ,
710             zx_trx_headers_gt          header
711         WHERE
712            ((fc.country_code    = Header.default_taxation_country
713             AND fc.country_code in ('AR', 'BR', 'CO'))
714             or
715             fc.country_code is NULL
716             )
717         AND Lines.application_id    = Header.application_id
718         AND Lines.entity_code       = Header.entity_code
719         AND Lines.event_class_code  = Header.event_class_code
720         AND Lines.trx_id = Header.trx_id
721         AND Lines.product_org_id is NOT NULL
722         AND Lines.product_id = mic.inventory_item_id
723         AND mic.organization_id  = Lines.Product_org_id
724         AND mic.category_id = fc.category_id
725         AND mic.category_set_id = fc.category_set_id
726      -- AND fc.structure_name = 'Fiscal Classification'  -- Commented for Bug#7125709
727         AND fc.structure_code = 'FISCAL_CLASSIFICATION'  -- Added as a fix for Bug#7125709
728         AND EXISTS
729                (SELECT 1
730                  FROM  JL_ZZ_AR_TX_FSC_CLS
731                  WHERE fiscal_classification_code = fc.classification_code
732                    AND enabled_flag = 'Y')
733         ) Temp
734    ON   ( Lines.trx_id = Temp.trx_id AND
735           Lines.trx_line_id = Temp.trx_line_id AND
736           Lines.trx_level_type = Temp.trx_level_type)
737    WHEN MATCHED THEN
738          UPDATE SET
739          product_fisc_classification = nvl(Lines.product_fisc_classification,
740                                            Temp.product_fisc_class)
741    WHEN NOT MATCHED THEN
742                       INSERT  (LINE_AMT) VALUES(NULL);
743 
744 
745 
746    MERGE INTO  ZX_TRANSACTION_LINES_GT Lines
747    USING (SELECT
748              Event.tax_event_class_code,
749              items.global_attribute2 trx_business_category,
750              Lines.trx_id,
751              Lines.trx_line_id,
752              Lines.trx_level_type
753         FROM
754               ZX_TRANSACTION_LINES_GT Lines ,
755               mtl_system_items        items,
756               ZX_EVNT_CLS_MAPPINGS    event
757         WHERE items.organization_id =  lines.Product_org_id
758         AND   items.inventory_item_id = lines.product_id
759         AND   lines.product_org_id is not NULL
760         AND Lines.application_id    = Event.application_id
761         AND Lines.entity_code       = Event.entity_code
762         AND Lines.event_class_code  = Event.event_class_code
763         )Temp
764    ON   ( Lines.trx_id = Temp.trx_id AND
765           Lines.trx_line_id = Temp.trx_line_id AND
766           Lines.trx_level_type = Temp.trx_level_type)
767    WHEN MATCHED THEN
768      UPDATE SET
769      trx_business_category     = nvl(Lines.trx_business_category,
770                                      DECODE(Temp.trx_business_category,NULL,Temp.trx_business_category,
771                                             Temp.tax_event_class_code||g_delimiter||Temp.trx_business_category))
772    WHEN NOT MATCHED THEN
773                   INSERT  (LINE_AMT) VALUES(NULL);
774 
775    -- In case where the product type is 'MEMO', default the Transaction Business Category
776    -- and Product Category from ar_memo_lines.
777 
778    MERGE INTO  ZX_TRANSACTION_LINES_GT Lines
779    USING (SELECT
780              Event.tax_event_class_code,
781              Memo.global_attribute2       trx_business_category,
782              Memo.tax_product_category    product_category,
783              Lines.trx_id,
784              Lines.trx_line_id,
785              Lines.trx_level_type
786         FROM
787               ZX_TRANSACTION_LINES_GT Lines ,
788               ar_memo_lines           Memo,
789               ZX_EVNT_CLS_MAPPINGS    event
790         WHERE Memo.memo_line_id = lines.product_id
791         AND   lines.product_org_id is NULL
792         AND   Lines.application_id    = Event.application_id
793         AND   Lines.entity_code       = Event.entity_code
794         AND   Lines.event_class_code  = Event.event_class_code
795         )Temp
796    ON   ( Lines.trx_id = Temp.trx_id AND
797           Lines.trx_line_id = Temp.trx_line_id AND
798           Lines.trx_level_type = Temp.trx_level_type)
799    WHEN MATCHED THEN
800      UPDATE SET
801      trx_business_category     = nvl(Lines.trx_business_category,
802                                      DECODE(Temp.trx_business_category,NULL,Temp.trx_business_category,
803                                             Temp.tax_event_class_code||g_delimiter||Temp.trx_business_category)),
804      Product_category          = nvl(Lines.product_category,
805                                      Temp.product_category)
806    WHEN NOT MATCHED THEN
807                   INSERT  (LINE_AMT) VALUES(NULL);
808 
809 
810    -- bug#5696143- populate output_tax_classification_code
811 
812    MERGE INTO  ZX_TRANSACTION_LINES_GT Lines
813    USING (SELECT CTT.global_attribute4  output_tax_classification_code,
814                  H.trx_id
815           FROM   ZX_TRX_HEADERS_GT H,
816                  RA_CUST_TRX_TYPES CTT,
817                  AR_VAT_TAX VT
818           WHERE  CTT.cust_trx_type_id =  H.receivables_trx_type_id
819           AND    CTT.org_id = VT.org_id
820           AND    CTT.org_id = H.internal_organization_id
821           AND    CTT.global_attribute4 = VT.tax_code
822           AND    VT.set_of_books_id = H.ledger_id
823           AND    H.trx_date between VT.start_date
824                             and     NVL(VT.end_date, H.trx_date)
825           AND    NVL(VT.enabled_flag,'Y') = 'Y'
826           AND    NVL(VT.tax_class,'O') = 'O'
827         )Temp
828    ON   ( Lines.trx_id = Temp.trx_id)
829    WHEN MATCHED THEN
830      UPDATE SET
831        output_tax_classification_code = NVL(Lines.output_tax_classification_code,
832                                             Temp.output_tax_classification_code)
833    WHEN NOT MATCHED THEN
834                   INSERT  (output_tax_classification_code) VALUES(NULL);
835 
836 
837  END IF;
838 
839     IF (g_level_procedure >= g_current_runtime_level ) THEN
840        FND_LOG.STRING(g_level_procedure,
841                       'JL.PL/SQL.JL_ZZ_TAX_VALIDATE_PKG.DEFAULT_TAX_ATTR',
842                       'JL_ZZ_TAX_VALIDATE_PKG.DEFAULT_TAX_ATTR(-)');
843     END IF;
844 
845   EXCEPTION
846        WHEN OTHERS THEN
847             IF (g_level_unexpected >= g_current_runtime_level ) THEN
848                FND_LOG.STRING(g_level_unexpected,
849                               'JL_ZZ_TAX_VALIDATE_PKG.DEFAULT_TAX_ATTR',
850                                sqlerrm);
851             END IF;
852             x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
853             app_exception.raise_exception;
854 
855 END default_tax_attr;
856 
857 /* default ax attributes API for line level calls */
858 PROCEDURE default_tax_attr  (p_trx_line_index      IN  NUMBER,
859                              x_return_status       OUT NOCOPY VARCHAR2) is
860 
861   l_organization_id  hr_organization_units.organization_id%type;
862   l_product_fisc_class zx_lines_det_factors.product_fisc_classification%type;
863   l_product_category   zx_lines_det_factors.product_category%type;
864   l_trx_business_category zx_lines_det_factors.trx_business_category%type;
865 
866   CURSOR c_delimiter IS
867   SELECT delimiter
868   FROM   zx_fc_types_b
869   WHERE  classification_type_code ='TRX_BUSINESS_CATEGORY';
870 
871 BEGIN
872 
873     x_return_status := FND_API.G_RET_STS_SUCCESS;
874     g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
875     IF (g_level_procedure >= g_current_runtime_level ) THEN
876        FND_LOG.STRING(g_level_procedure,
877                       'JL.PL/SQL.JL_ZZ_TAX_VALIDATE_PKG.DEFAULT_TAX_ATTR',
878                       'JL_ZZ_TAX_VALIDATE_PKG.DEFAULT_TAX_ATTR(+)');
879     END IF;
880 
881     -- Defaulting the taxation country
882 /* -- Commented out the logic that raises error when default taxation country is not available.
883    -- Instead, the calling API will verify that default_taxation_country is available before
884    -- calling this API.
885 
886    IF zx_global_structures_pkg.trx_line_dist_tbl.default_taxation_country(p_trx_line_index) is NULL then
887     -- Check with TSRM that default_taxation_country is always populated;
888 
889            IF (g_level_unexpected >= g_current_runtime_level ) THEN
890                FND_LOG.STRING(g_level_unexpected,
891                               'JL_ZZ_TAX_VALIDATE_PKG.DEFAULT_TAX_ATTR',
892                                'Default taxation country is not available');
893             END IF;
894             x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
895             app_exception.raise_exception;
896    End If;
897 */
898 
899 /* Defaulting for Transaction Business Category, Product Category  and
900    Product Fiscal Classification on transaction lines */
901 
902    -- In case where the line is not a memo line, default the Transaction Business
903    -- Category and Product Fiscal Classification from mtl_system_items / mtl_item_categories.
904    -- If the line is a memo line, then populate Transaction Business Category and
905    -- Product Category from ar_memo_lines.
906 
907    l_organization_id := zx_global_structures_pkg.trx_line_dist_tbl.product_org_id(p_trx_line_index);
908 
909    IF (g_level_statement >= g_current_runtime_level ) THEN
910         FND_LOG.STRING(g_level_statement,
911                 'JL.PL/SQL.JL_ZZ_TAX_VALIDATE_PKG.DEFAULT_TAX_ATTR',
912                 'JL_ZZ_TAX_VALIDATE_PKG.DEFAULT_TAX_ATTR:
913         Defaulting for Transaction Business Category and Product Category,
914         Product Fiscal Classification');
915    END IF;
916 
917   IF zx_global_structures_pkg.trx_line_dist_tbl.product_id(p_trx_line_index) IS NOT NULL
918   AND zx_global_structures_pkg.trx_line_dist_tbl.product_org_id(p_trx_line_index) is NOT NULL THEN
919 
920     -- It is an inveontory item; Populate product_fisc_classification and trx_business_category
921     -- from mtl_system_items.
922      If zx_global_structures_pkg.trx_line_dist_tbl.trx_business_category(p_trx_line_index) is NULL then
923 
924         IF g_delimiter is NULL then
925                OPEN c_delimiter;
926                FETCH c_delimiter INTO g_delimiter;
927                CLOSE c_delimiter;
928         END IF;
929 
930         IF (g_level_statement >= g_current_runtime_level ) THEN
931             FND_LOG.STRING(g_level_statement,
932                 'JL.PL/SQL.JL_ZZ_TAX_VALIDATE_PKG.DEFAULT_TAX_ATTR',
933                 'Transaction Business Category is NULL. Defaulting Transaction Business Category'||
934                 ' Tax Event Class Code = '||zx_global_structures_pkg.trx_line_dist_tbl.tax_event_class_code(p_trx_line_index)||
935                 ' Delimiter = '||g_delimiter);
936         END IF;
937 
938 
939         SELECT
940              DECODE(items.global_attribute2, NULL, items.global_attribute2,
941                     zx_global_structures_pkg.trx_line_dist_tbl.tax_event_class_code(p_trx_line_index)
942                     ||g_delimiter||items.global_attribute2) trx_business_category
943         INTO
944              l_trx_business_category
945         FROM
946               mtl_system_items   items
947         WHERE organization_id =  l_organization_id
948         AND   inventory_item_id = zx_global_structures_pkg.trx_line_dist_tbl.product_id(p_trx_line_index);
949 
950 
951         IF (g_level_statement >= g_current_runtime_level ) THEN
952             FND_LOG.STRING(g_level_statement,
953                 'JL.PL/SQL.JL_ZZ_TAX_VALIDATE_PKG.DEFAULT_TAX_ATTR',
954                 'After defaulting: l_trx_business_category = '||l_trx_business_category);
955         END IF;
956 
957        zx_global_structures_pkg.trx_line_dist_tbl.trx_business_category(p_trx_line_index) :=
958                       nvl(zx_global_structures_pkg.trx_line_dist_tbl.trx_business_category(p_trx_line_index),
959                                        l_trx_business_category);
960      End If;
961 
962     Begin
963       If zx_global_structures_pkg.trx_line_dist_tbl.product_fisc_classification(p_trx_line_index) is NULL then
964 
965 
966          IF (g_level_statement >= g_current_runtime_level ) THEN
967             FND_LOG.STRING(g_level_statement,
968                 'JL.PL/SQL.JL_ZZ_TAX_VALIDATE_PKG.DEFAULT_TAX_ATTR',
969                 'Country code: '||
970                 zx_global_structures_pkg.trx_line_dist_tbl.default_taxation_country(p_trx_line_index));
971         END IF;
972 
973 
974         SELECT
975             fc.classification_code
976         INTO
977             l_product_fisc_class
978         FROM
979             zx_fc_product_fiscal_v     fc,
980             mtl_item_categories      mic
981         WHERE
982             ((fc.country_code    =
983                zx_global_structures_pkg.trx_line_dist_tbl.default_taxation_country(p_trx_line_index)
984                AND fc.country_code in ('AR', 'BR', 'CO'))
985               or
986               fc.country_code is NULL
987              )
988         AND zx_global_structures_pkg.trx_line_dist_tbl.product_id(p_trx_line_index)
989                                      = mic.inventory_item_id
990         AND mic.organization_id  = l_organization_id
991         AND mic.category_id = fc.category_id
992         AND mic.category_set_id = fc.category_set_id
993      -- AND fc.structure_name = 'Fiscal Classification'  -- Commented for Bug#7125709
994         AND fc.structure_code = 'FISCAL_CLASSIFICATION'  -- Added as a fix for Bug#7125709
995         AND EXISTS
996                (SELECT 1
997                  FROM  JL_ZZ_AR_TX_FSC_CLS
998                  WHERE fiscal_classification_code = fc.classification_code
999                    AND enabled_flag = 'Y')
1000         AND rownum = 1;   -- Bug 5701599
1001 
1002          zx_global_structures_pkg.trx_line_dist_tbl.product_fisc_classification(p_trx_line_index) :=
1003                        nvl(zx_global_structures_pkg.trx_line_dist_tbl.product_fisc_classification(p_trx_line_index),
1004                                            l_product_fisc_class);
1005        End If;
1006      Exception
1007         when no_data_found then
1008             IF (g_level_exception >= g_current_runtime_level ) THEN
1009                FND_LOG.STRING(g_level_exception,
1010                               'JL_ZZ_TAX_VALIDATE_PKG.DEFAULT_TAX_ATTR',
1011                                'Unable to default Product Fiscal Classification which is mandatory for LTE');
1012             END IF;
1013             x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1014             -- app_exception.raise_exception;
1015      End;
1016 
1017   ELSIF zx_global_structures_pkg.trx_line_dist_tbl.product_id(p_trx_line_index) IS NOT NULL
1018   AND zx_global_structures_pkg.trx_line_dist_tbl.product_org_id(p_trx_line_index) is NULL THEN
1019 
1020    -- In case where the line is a memo line, default the Transaction Business Category
1021    -- and Product Category from ar_memo_lines.
1022 
1023      IF (zx_global_structures_pkg.trx_line_dist_tbl.product_fisc_classification(p_trx_line_index) IS NULL
1024      OR zx_global_structures_pkg.trx_line_dist_tbl.trx_business_category(p_trx_line_index) IS NULL)
1025      then
1026 
1027           Begin
1028 
1029              IF g_delimiter is NULL then
1030                OPEN c_delimiter;
1031                FETCH c_delimiter INTO g_delimiter;
1032                CLOSE c_delimiter;
1033              END IF;
1034 
1035 
1036              SELECT
1037                   DECODE(Memo.global_attribute2, NULL, Memo.global_attribute2,
1038                          zx_global_structures_pkg.trx_line_dist_tbl.tax_event_class_code(p_trx_line_index)
1039                          ||g_delimiter||Memo.global_attribute2) trx_business_category,
1040                   Memo.tax_product_category                     product_category
1041              INTO
1042                   l_trx_business_category,
1043                   l_product_category
1044              FROM
1045                    ar_memo_lines  Memo
1046              WHERE memo_line_id = zx_global_structures_pkg.trx_line_dist_tbl.product_id(p_trx_line_index);
1047 
1048              zx_global_structures_pkg.trx_line_dist_tbl.trx_business_category(p_trx_line_index) :=
1049                            nvl(zx_global_structures_pkg.trx_line_dist_tbl.trx_business_category(p_trx_line_index),
1050                                             l_trx_business_category);
1051 
1052              zx_global_structures_pkg.trx_line_dist_tbl.product_category(p_trx_line_index) :=
1053                            nvl(zx_global_structures_pkg.trx_line_dist_tbl.product_category(p_trx_line_index),
1054                                             l_product_category);
1055 
1056             IF (g_level_statement >= g_current_runtime_level ) THEN
1057                  FND_LOG.STRING(g_level_statement,
1058                 'JL.PL/SQL.JL_ZZ_TAX_VALIDATE_PKG.DEFAULT_TAX_ATTR',
1059                 ' l_trx_business_category = '||l_trx_business_category||
1060                 ' l_product_category = '||l_product_category);
1061             END IF;
1062 
1063           Exception
1064              when no_data_found then
1065                  IF (g_level_exception >= g_current_runtime_level ) THEN
1066                     FND_LOG.STRING(g_level_exception,
1067                                    'JL_ZZ_TAX_VALIDATE_PKG.DEFAULT_TAX_ATTR',
1068                                     'Unable to default Product Fiscal Classification ot Trx Business Category'||
1069                                     ' which is mandatory for LTE');
1070                  END IF;
1071                  x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1072                  app_exception.raise_exception;
1073           End;
1074         End If;
1075   END IF;
1076 
1077 
1078   IF (g_level_procedure >= g_current_runtime_level ) THEN
1079        FND_LOG.STRING(g_level_procedure,
1080                       'JL.PL/SQL.JL_ZZ_TAX_VALIDATE_PKG.DEFAULT_TAX_ATTR',
1081                       'JL_ZZ_TAX_VALIDATE_PKG.DEFAULT_TAX_ATTR(-)');
1082   END IF;
1083 
1084 EXCEPTION
1085        WHEN OTHERS THEN
1086             IF (g_level_unexpected >= g_current_runtime_level ) THEN
1087                FND_LOG.STRING(g_level_unexpected,
1088                               'JL_ZZ_TAX_VALIDATE_PKG.DEFAULT_TAX_ATTR',
1089                                sqlerrm);
1090             END IF;
1091             x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1092             app_exception.raise_exception;
1093 
1094 END default_tax_attr;
1095 
1096 
1097 
1098 PROCEDURE validate_tax_attr (x_return_status OUT NOCOPY VARCHAR2) IS
1099 
1100 BEGIN
1101 
1102    x_return_status := FND_API.G_RET_STS_SUCCESS;
1103    g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1104    IF (g_level_procedure >= g_current_runtime_level ) THEN
1105       FND_LOG.STRING(g_level_procedure,
1106                      'JL.PL/SQL.JL_ZZ_TAX_VALIDATE_PKG.VALIDATE_TAX_ATTR',
1107                      'JL_ZZ_TAX_VALIDATE_PKG.VALIDATE_TAX_ATTR(+)');
1108    END IF;
1109 
1110    IF (g_level_procedure >= g_current_runtime_level ) THEN
1111       FND_LOG.STRING(g_level_procedure,
1112                      'JL.PL/SQL.JL_ZZ_TAX_VALIDATE_PKG.VALIDATE_TAX_ATTR',
1113                      'Running Line Level Validations...');
1114    END IF;
1115 
1116         INSERT ALL
1117         WHEN (ZX_EVNT_CLS_MPG_INVALID = 'Y')  THEN
1118 
1119                         INTO ZX_VALIDATION_ERRORS_GT(
1120                                 application_id,
1121                                 entity_code,
1122                                 event_class_code,
1123                                 trx_id,
1124                                 trx_line_id,
1125                                 summary_tax_line_number,
1126                                 message_name,
1130                                 )
1127                                 message_text,
1128                                 trx_level_type,
1129                                 interface_line_id
1131                         VALUES(
1132                                 application_id,
1133                                 entity_code,
1134                                 event_class_code,
1135                                 trx_id,
1136                                 trx_line_id,
1137                                 NULL,
1138                                 'ZX_EVNT_CLS_MPG_INVALID',
1139                                 l_evnt_cls_mpg_invalid,
1140                                 trx_level_type,
1141                                 interface_line_id
1142                                  )
1143 
1144         WHEN (ZX_EXCHG_INFO_MISSING = 'Y')  THEN
1145 
1146                         INTO ZX_VALIDATION_ERRORS_GT(
1147                                 application_id,
1148                                 entity_code,
1149                                 event_class_code,
1150                                 trx_id,
1151                                 trx_line_id,
1152                                 summary_tax_line_number,
1153                                 message_name,
1154                                 message_text,
1155                                 trx_level_type,
1156                                 interface_line_id
1157                                 )
1158                         VALUES(
1159                                 application_id,
1160                                 entity_code,
1161                                 event_class_code,
1162                                 trx_id,
1163                                 trx_line_id,
1164                                 NULL,
1165                                 'ZX_EXCHG_INFO_MISSING',
1166                                 l_exchg_info_missing,
1167                                 trx_level_type,
1168                                 interface_line_id
1169                                  )
1170 
1171 
1172         WHEN (ZX_LINE_CLASS_INVALID = 'Y')  THEN
1173 
1174                         INTO ZX_VALIDATION_ERRORS_GT(
1175                                 application_id,
1176                                 entity_code,
1177                                 event_class_code,
1178                                 trx_id,
1179                                 trx_line_id,
1180                                 summary_tax_line_number,
1181                                 message_name,
1182                                 message_text,
1183                                 trx_level_type,
1184                                 interface_line_id
1185                                 )
1186                         VALUES(
1187                                 application_id,
1188                                 entity_code,
1189                                 event_class_code,
1190                                 trx_id,
1191                                 trx_line_id,
1192                                 NULL,
1193                                 'ZX_LINE_CLASS_INVALID',
1194                                 l_line_class_invalid,
1195                                 trx_level_type,
1196                                 interface_line_id
1197                                  )
1198 
1199         WHEN (ZX_TRX_LINE_TYPE_INVALID = 'Y')  THEN
1200 
1201                         INTO ZX_VALIDATION_ERRORS_GT(
1202                                 application_id,
1203                                 entity_code,
1204                                 event_class_code,
1205                                 trx_id,
1206                                 trx_line_id,
1207                                 summary_tax_line_number,
1208                                 message_name,
1209                                 message_text,
1210                                 trx_level_type,
1211                                 interface_line_id
1212                                 )
1213                         VALUES(
1214                                 application_id,
1215                                 entity_code,
1216                                 event_class_code,
1217                                 trx_id,
1218                                 trx_line_id,
1219                                 NULL,
1220                                 'ZX_TRX_LINE_TYPE_INVALID',
1221                                 l_trx_line_type_invalid,
1222                                 trx_level_type,
1223                                 interface_line_id
1224                                  )
1225 
1226         WHEN (ZX_LINE_AMT_INCL_TAX_INVALID = 'Y')  THEN
1227 
1228                         INTO ZX_VALIDATION_ERRORS_GT(
1229                                 application_id,
1230                                 entity_code,
1231                                 event_class_code,
1232                                 trx_id,
1233                                 trx_line_id,
1234                                 summary_tax_line_number,
1235                                 message_name,
1236                                 message_text,
1237                                 trx_level_type,
1238                                 interface_line_id
1239                                 )
1240                         VALUES(
1241                                 application_id,
1242                                 entity_code,
1243                                 event_class_code,
1244                                 trx_id,
1245                                 trx_line_id,
1246                                 NULL,
1247                                 'ZX_LINE_AMT_INCTAX_INVALID',
1248                                 l_line_amt_incl_tax_invalid,
1249                                 trx_level_type,
1250                                 interface_line_id
1251                                  )
1252 
1253         /*
1254         WHEN (SHIP_TO_PARTY_NOT_EXISTS = 'Y')  THEN
1255 
1256                                 INTO ZX_VALIDATION_ERRORS_GT(
1257                                         application_id,
1258                                         entity_code,
1259                                         event_class_code,
1260                                         trx_id,
1261                                         trx_line_id,
1262                                         summary_tax_line_number,
1263                                         message_name,
1264                                         message_text,
1265                                         trx_level_type
1266                                         )
1267                                 VALUES(
1268                                         application_id,
1269                                         entity_code,
1270                                         event_class_code,
1271                                         trx_id,
1272                                         trx_line_id,
1273                                         NULL,
1274                                         'ZX_SHIP_TO_PARTY_NOT_EXIST',
1275                                         l_ship_to_party_not_exists,
1276                                         trx_level_type
1277                                  )
1278 
1279 
1280         WHEN (BILL_TO_PARTY_NOT_EXISTS = 'Y')  THEN
1281 
1282                                 INTO ZX_VALIDATION_ERRORS_GT(
1283                                         application_id,
1284                                         entity_code,
1285                                         event_class_code,
1286                                         trx_id,
1287                                         trx_line_id,
1288                                         summary_tax_line_number,
1289                                         message_name,
1290                                         message_text,
1291                                         trx_level_type
1292                                         )
1293                                 VALUES(
1294                                         application_id,
1295                                         entity_code,
1296                                         event_class_code,
1297                                         trx_id,
1298                                         trx_line_id,
1299                                         NULL,
1300                                         'ZX_BILTO_PARTY_NOT_EXIST',
1301                                         l_bill_to_party_not_exists,
1302                                         trx_level_type
1303                                  )
1304 
1305 
1306         WHEN (SHIPTO_PARTY_SITE_NOT_EXISTS = 'Y')  THEN
1307 
1308                                 INTO ZX_VALIDATION_ERRORS_GT(
1309                                         application_id,
1310                                         entity_code,
1311                                         event_class_code,
1312                                         trx_id,
1313                                         trx_line_id,
1314                                         summary_tax_line_number,
1315                                         message_name,
1316                                         message_text,
1317                                         trx_level_type
1318                                         )
1319                                 VALUES(
1320                                         application_id,
1321                                         entity_code,
1322                                         event_class_code,
1323                                         trx_id,
1324                                         trx_line_id,
1325                                         NULL,
1326                                         'ZX_SHIPTO_PARTY_SITE_NOT_EXIST',
1327                                         l_shipto_party_site_not_exists,
1328                                         trx_level_type
1329                                  )
1330 
1331         WHEN (SHIPFROM_PARTY_SITE_NOT_EXISTS = 'Y')  THEN
1332 
1333                                 INTO ZX_VALIDATION_ERRORS_GT(
1334                                         application_id,
1335                                         entity_code,
1336                                         event_class_code,
1337                                         trx_id,
1338                                         trx_line_id,
1339                                         summary_tax_line_number,
1340                                         message_name,
1341                                         message_text,
1342                                         trx_level_type
1343                                         )
1344                                 VALUES(
1345                                         application_id,
1346                                         entity_code,
1347                                         event_class_code,
1348                                         trx_id,
1349                                         trx_line_id,
1350                                         NULL,
1351                                         'ZX_SHIPFROM_PARTY_SITE_NOT_EXIST',
1352                                         l_shipfrm_party_site_not_exits,
1353                                         trx_level_type
1354                                  )
1355 
1356         WHEN (BILLTO_PARTY_SITE_NOT_EXISTS = 'Y')  THEN
1357 
1358                                 INTO ZX_VALIDATION_ERRORS_GT(
1359                                         application_id,
1360                                         entity_code,
1361                                         event_class_code,
1362                                         trx_id,
1363                                         trx_line_id,
1364                                         summary_tax_line_number,
1365                                         message_name,
1366                                         message_text,
1367                                         trx_level_type
1368                                         )
1369                                 VALUES(
1370                                         application_id,
1371                                         entity_code,
1372                                         event_class_code,
1373                                         trx_id,
1374                                         trx_line_id,
1375                                         NULL,
1376                                         'ZX_BILLTO_PARTY_SITE_NOT_EXIST',
1377                                         l_billto_party_site_not_exists,
1378                                         trx_level_type
1379                                  )
1380 
1381            */
1382 
1383            WHEN (USER_DEF_FC_NA_FOR_LTE = 'Y') THEN
1384                 INTO ZX_VALIDATION_ERRORS_GT(
1385                         application_id,
1386                         entity_code,
1387                         event_class_code,
1388                         trx_id,
1389                         trx_line_id,
1390                         summary_tax_line_number,
1391                         message_name,
1392                         message_text,
1393                         trx_level_type,
1394                         interface_line_id
1395                         )
1396                 VALUES(
1397                         application_id,
1398                         entity_code,
1399                         event_class_code,
1400                         trx_id,
1401                         trx_line_id,
1402                         NULL,
1403                         'ZX_USER_DEF_FC_NA_FOR_LTE',
1404                         l_user_def_fc_na_for_lte,
1405                         trx_level_type,
1406                         interface_line_id
1407                          )
1408 
1409            /*
1410            -- Commented the validation as Product Category
1411            -- is a required parameter for Memo Lines
1412            WHEN (PRODUCT_CATEGORY_NA_FOR_LTE = 'Y') THEN
1413                 INTO ZX_VALIDATION_ERRORS_GT(
1414                         application_id,
1415                         entity_code,
1416                         event_class_code,
1417                         trx_id,
1418                         trx_line_id,
1419                         summary_tax_line_number,
1420                         message_name,
1421                         message_text,
1422                         trx_level_type,
1423                         interface_line_id
1424                         )
1425                 VALUES(
1426                         application_id,
1427                         entity_code,
1428                         event_class_code,
1429                         trx_id,
1430                         trx_line_id,
1431                         NULL,
1432                         'ZX_PRODUCT_CATEGORY_NA_FOR_LTE',
1433                         l_product_category_na_for_lte,
1434                         trx_level_type,
1435                         interface_line_id
1436                          )
1437             */
1438 
1439               WHEN (DOCUMENT_FC_NA_FOR_LTE = 'Y') THEN
1440                 INTO ZX_VALIDATION_ERRORS_GT(
1441                         application_id,
1442                         entity_code,
1443                         event_class_code,
1444                         trx_id,
1445                         trx_line_id,
1446                         summary_tax_line_number,
1447                         message_name,
1448                         message_text,
1449                         trx_level_type,
1450                         interface_line_id
1451                         )
1452                 VALUES(
1453                         application_id,
1454                         entity_code,
1455                         event_class_code,
1456                         trx_id,
1457                         trx_line_id,
1458                         NULL,
1459                         'ZX_DOCUMENT_FC_NA_FOR_LTE',
1460                         l_document_fc_na_for_lte,
1461                         trx_level_type,
1462                         interface_line_id
1463                          )
1464 
1465               WHEN (INTENDED_USE_NA_FOR_LTE = 'Y') THEN
1466                 INTO ZX_VALIDATION_ERRORS_GT(
1467                         application_id,
1468                         entity_code,
1469                         event_class_code,
1470                         trx_id,
1471                         trx_line_id,
1472                         summary_tax_line_number,
1473                         message_name,
1474                         message_text,
1475                         trx_level_type,
1476                         interface_line_id
1477                         )
1478                 VALUES(
1479                         application_id,
1480                         entity_code,
1481                         event_class_code,
1482                         trx_id,
1483                         trx_line_id,
1484                         NULL,
1485                         'ZX_INTENDED_USE_NA_FOR_LTE',
1486                         l_indended_use_na_for_lte,
1487                         trx_level_type,
1488                         interface_line_id
1489                          )
1490 
1491               WHEN (PRODUCT_TYPE_NA_FOR_LTE = 'Y') THEN
1492                 INTO ZX_VALIDATION_ERRORS_GT(
1493                         application_id,
1494                         entity_code,
1495                         event_class_code,
1496                         trx_id,
1497                         trx_line_id,
1498                         summary_tax_line_number,
1499                         message_name,
1500                         message_text,
1501                         trx_level_type,
1502                         interface_line_id
1503                         )
1504                 VALUES(
1505                         application_id,
1506                         entity_code,
1507                         event_class_code,
1508                         trx_id,
1509                         trx_line_id,
1510                         NULL,
1511                         'ZX_PRODUCT_TYPE_NA_FOR_LTE',
1512                         l_product_type_na_for_lte,
1513                         trx_level_type,
1514                         interface_line_id
1515                          )
1516 
1517               WHEN (TAX_RATE_CODE_NOT_EXISTS = 'Y')  THEN
1518                 INTO ZX_VALIDATION_ERRORS_GT(
1519                         application_id,
1520                         entity_code,
1521                         event_class_code,
1522                         trx_id,
1523                         trx_line_id,
1524                         summary_tax_line_number,
1525                         message_name,
1526                         message_text,
1527                         trx_level_type,
1528                         interface_line_id
1529                         )
1530                 VALUES(
1531                         application_id,
1532                         entity_code,
1533                         event_class_code,
1534                         trx_id,
1535                         trx_line_id,
1536                         NULL,
1537                         'ZX_TAX_RATE_NOT_EXIST',
1538                         l_tax_rate_not_exists ||' (Tax Classification Code = '||
1539                               output_tax_classification_code||')',
1540                         trx_level_type,
1541                         interface_line_id
1542                         )
1543 
1544               WHEN (TAX_RATE_CODE_NOT_EXISTS = 'N' AND TAX_RATE_CODE_NOT_EFFECTIVE = 'Y')  THEN
1545                 INTO ZX_VALIDATION_ERRORS_GT(
1546                         application_id,
1547                         entity_code,
1548                         event_class_code,
1549                         trx_id,
1550                         trx_line_id,
1551                         summary_tax_line_number,
1552                         message_name,
1553                         message_text,
1554                         trx_level_type,
1555                         interface_line_id
1556                         )
1557                 VALUES(
1558                         application_id,
1559                         entity_code,
1560                         event_class_code,
1561                         trx_id,
1562                         trx_line_id,
1563                         NULL,
1564                         'ZX_TAX_RATE_NOT_EFFECTIVE',
1565                         l_tax_rate_not_effective ||' (Tax Classification Code = '||
1566                               output_tax_classification_code||')',
1567                         trx_level_type,
1568                         interface_line_id
1569                         )
1570 
1571               WHEN (TAX_RATE_CODE_NOT_EXISTS = 'N' AND TAX_RATE_CODE_NOT_ACTIVE = 'Y')  THEN
1572                 INTO ZX_VALIDATION_ERRORS_GT(
1573                         application_id,
1574                         entity_code,
1575                         event_class_code,
1576                         trx_id,
1577                         trx_line_id,
1578                         summary_tax_line_number,
1579                         message_name,
1580                         message_text,
1581                         trx_level_type,
1582                         interface_line_id
1583                         )
1584                 VALUES(
1585                         application_id,
1586                         entity_code,
1587                         event_class_code,
1588                         trx_id,
1589                         trx_line_id,
1590                         NULL,
1591                         'ZX_TAX_RATE_NOT_ACTIVE',
1592                         l_tax_rate_not_active ||' (Tax Classification Code = '||
1593                               output_tax_classification_code||')',
1594                         trx_level_type,
1595                         interface_line_id
1596                         )
1597 
1598               SELECT
1599                 header.application_id,
1600                 header.entity_code,
1601                 header.event_class_code,
1602                 header.trx_id,
1603                 lines_gt.trx_line_id,
1604                 lines_gt.trx_level_type,
1605                 lines_gt.interface_line_id,
1606                 -- Check for Event Class Existence
1607                 CASE WHEN (evntmap.application_id is not null AND
1608                            evntmap.entity_code is not null AND
1609                            evntmap.event_class_code is not null)
1610                      THEN  NULL
1611                      ELSE  'Y'
1612                  END ZX_EVNT_CLS_MPG_INVALID,
1613 
1614 
1615                 -- Check for existence of Exchange information
1616                 CASE WHEN (header.ledger_id    = gsob.set_of_books_id AND
1617                            gsob.currency_code <> header.trx_currency_code AND
1618                            header.currency_conversion_rate is NULL AND
1619                            header.currency_conversion_date is NULL AND
1620                            header.currency_conversion_type is NULL
1621                            )
1622                      THEN 'Y'
1623                      ELSE 'N' --Note the change of yes, no value
1624                  END ZX_EXCHG_INFO_MISSING,
1625 
1626                 -- Check for Validity of Transaction line class
1627                 nvl2(lines_gt.line_class,
1628                      CASE WHEN (NOT EXISTS
1629                                 (SELECT 1 FROM FND_LOOKUPS lkp
1630                                  WHERE lines_gt.line_class = lkp.lookup_code
1631                                  AND lkp.lookup_type = 'ZX_LINE_CLASS'))
1632                           THEN 'Y'
1633                           ELSE NULL
1634                      END,
1635                      NULL
1636                     ) ZX_LINE_CLASS_INVALID,
1637 
1638                 -- Check for Validity of transaction line type
1639                 CASE WHEN (lines_gt.trx_line_type NOT IN('ITEM','FREIGHT',
1640                                                          'MISC'))
1641                      THEN 'Y'
1642                      ELSE NULL
1643                 END  ZX_TRX_LINE_TYPE_INVALID,
1644 
1645                 -- Check for Validity of Line amount includes tax flag
1646                 CASE WHEN (lines_gt.line_amt_includes_tax_flag
1647                            NOT IN ('A','N','S'))
1648                      THEN 'Y'
1649                      ELSE  NULL
1650                 END  ZX_LINE_AMT_INCL_TAX_INVALID,
1651 
1652 
1653                 /* need to add party types for O2C
1654 
1655                 -- Check for SHIP_TO_PARTY_ID
1656                 nvl2(lines_gt.SHIP_TO_PARTY_ID,
1657                      CASE WHEN (NOT EXISTS
1658                                 (SELECT 1 FROM zx_party_tax_profile
1659                                  WHERE party_id =
1660                                        lines_gt.SHIP_TO_PARTY_ID
1661                                  AND  party_type_code = 'CUSTOMER'))
1662                            THEN 'Y'
1663                            ELSE NULL END,
1664                       NULL) SHIP_TO_PARTY_NOT_EXISTS,
1665 
1666 
1667                 -- Check for BILL_TO_PARTY_ID
1668                 nvl2(lines_gt.BILL_TO_PARTY_ID,
1669                      CASE WHEN (NOT EXISTS
1670                                 (SELECT 1 FROM zx_party_tax_profile
1671                                  WHERE party_id =
1672                                        lines_gt.BILL_TO_PARTY_ID
1673                                  AND  party_type_code = 'CUSTOMER'))
1674                            THEN 'Y'
1675                            ELSE NULL END,
1676                       NULL) BILL_TO_PARTY_NOT_EXISTS,
1677 
1678 
1679                 -- Check for SHIP_TO_PARTY_SITE_ID
1680                 nvl2(lines_gt.SHIP_TO_PARTY_SITE_ID,
1681                      CASE WHEN (NOT EXISTS
1682                                 (SELECT 1 FROM zx_party_tax_profile
1683                                  WHERE party_id =
1684                                        lines_gt.SHIP_TO_PARTY_SITE_ID
1685                                  AND  party_type_code = 'CUSTOMER_SITE'))
1686                            THEN 'Y'
1687                            ELSE NULL END,
1688                       NULL) SHIPTO_PARTY_SITE_NOT_EXISTS,
1689 
1690                 -- Check for SHIP_FROM_PARTY_SITE_ID
1691                 nvl2(lines_gt.SHIP_FROM_PARTY_SITE_ID,
1692                      CASE WHEN (NOT EXISTS
1693                                 (SELECT 1 FROM zx_party_tax_profile
1694                                  WHERE party_id =
1695                                        lines_gt.SHIP_FROM_PARTY_SITE_ID
1696                                  AND  party_type_code = 'LEGAL_ESTABLISHMENT'))
1697                            THEN 'Y'
1698                            ELSE NULL END,
1699                       NULL) SHIPFROM_PARTY_SITE_NOT_EXISTS,
1700 
1701                 -- Check for BILL_TO_PARTY_SITE_ID
1702                 nvl2(lines_gt.BILL_TO_PARTY_SITE_ID,
1703                      CASE WHEN (NOT EXISTS
1704                                 (SELECT 1 FROM zx_party_tax_profile
1705                                  WHERE party_id =
1706                                        lines_gt.BILL_TO_PARTY_SITE_ID
1707                                  AND  party_type_code = 'CUSTOMER_SITE'))
1708                            THEN 'Y'
1709                            ELSE NULL END,
1710                       NULL) BILLTO_PARTY_SITE_NOT_EXISTS
1711                 */
1712 
1713                 -- Check for User-Defined Fiscal Classification
1714                   CASE WHEN (lines_gt.USER_DEFINED_FISC_CLASS is not null)
1715                         THEN 'Y'
1716                         ELSE NULL
1717                   END USER_DEF_FC_NA_FOR_LTE,
1718 
1719                 /*
1720                 -- Commented the validation as Product Category
1721                 -- is populated for Memo Lines
1722                   CASE WHEN (lines_gt.PRODUCT_CATEGORY is not null)
1723                         THEN 'Y'
1724                         ELSE NULL
1725                   END PRODUCT_CATEGORY_NA_FOR_LTE,
1726                 */
1727 
1728                 -- Check for Document Subtype
1729                   CASE WHEN (header.DOCUMENT_SUB_TYPE is not null)
1730                         THEN 'Y'
1731                         ELSE NULL
1732                   END DOCUMENT_FC_NA_FOR_LTE,
1733 
1734                 -- Check for Line Intended Use
1735                   CASE WHEN (lines_gt.LINE_INTENDED_USE is not null)
1736                         THEN 'Y'
1737                         ELSE NULL
1738                   END INTENDED_USE_NA_FOR_LTE,
1739 
1740                 -- Check for Product Type
1741                   CASE WHEN (lines_gt.PRODUCT_TYPE is not null)
1742                         THEN 'Y'
1743                         ELSE NULL
1744                   END PRODUCT_TYPE_NA_FOR_LTE,
1745 
1746                   -- Tax Classification Code
1747                   lines_gt.output_tax_classification_code,
1748 
1749                   -- Check Tax Classification Code exists
1750                   CASE WHEN lines_gt.output_tax_classification_code IS NOT NULL
1751                         AND NOT EXISTS (SELECT 1
1752                                         FROM zx_output_classifications_v
1753                                         WHERE lookup_code = lines_gt.output_tax_classification_code
1754                                         AND org_id in (header.internal_organization_id, -99))
1755                         THEN 'Y'
1756                         ELSE NULL
1757                   END TAX_RATE_CODE_NOT_EXISTS,
1758 
1759                   -- Check Tax Classification Code is effective
1760                   CASE WHEN lines_gt.output_tax_classification_code IS NOT NULL
1761                        AND NOT EXISTS (SELECT 1
1762                                         FROM zx_output_classifications_v
1763                                         WHERE lookup_code = lines_gt.output_tax_classification_code
1764                                         AND org_id in (header.internal_organization_id, -99)
1765                                         AND header.trx_date BETWEEN start_date_active
1766                                             AND nvl(end_date_active,header.trx_date))
1767                        THEN 'Y'
1768                        ELSE NULL
1769                   END TAX_RATE_CODE_NOT_EFFECTIVE,
1770 
1771                   -- Check Tax Classification Code is Active
1772                   CASE WHEN lines_gt.output_tax_classification_code IS NOT NULL
1773                        AND NOT EXISTS (SELECT 1
1774                                         FROM zx_output_classifications_v
1775                                         WHERE lookup_code = lines_gt.output_tax_classification_code
1776                                         AND org_id in (header.internal_organization_id, -99)
1777                                         AND enabled_flag = 'Y')
1778                        THEN 'Y'
1779                        ELSE NULL
1780                   END TAX_RATE_CODE_NOT_ACTIVE
1781 
1782               FROM
1783                   ZX_TRX_HEADERS_GT             header,
1784                   ZX_EVNT_CLS_MAPPINGS          evntmap,
1785                   ZX_TRANSACTION_LINES_GT       lines_gt,
1786                   GL_SETS_OF_BOOKS              gsob
1787 
1788               WHERE
1789                     lines_gt.trx_id = header.trx_id
1790                 and gsob.set_of_books_id(+)   = header.ledger_id
1791                 and lines_gt.application_id   = Header.application_id
1792                 and lines_gt.entity_code      = Header.entity_code
1793                 and lines_gt.event_class_code = Header.event_class_code
1794                 and header.application_id     = evntmap.application_id (+)
1795                 and header.entity_code        = evntmap.entity_code (+)
1796                 and header.event_class_code   = evntmap.event_class_code(+);
1797 
1798    IF (g_level_procedure >= g_current_runtime_level ) THEN
1799       FND_LOG.STRING(g_level_procedure,
1800                      'JL.PL/SQL.JL_ZZ_TAX_VALIDATE_PKG.VALIDATE_TAX_ATTR',
1801                      'Line Level Validation Errors : '|| To_Char(SQL%ROWCOUNT) );
1802    END IF;
1803 
1804    -- Run Tax Line Level Validation for manual tax lines (if any)
1805    IF nvl(g_tax_lines_count,0) > 0 THEN
1806 
1807       IF (g_level_procedure >= g_current_runtime_level ) THEN
1808          FND_LOG.STRING(g_level_procedure,
1809                         'JL.PL/SQL.JL_ZZ_TAX_VALIDATE_PKG.VALIDATE_TAX_ATTR',
1810                         'Running Manual Tax Line Level Validations...');
1811       END IF;
1812 
1813         INSERT ALL
1814         WHEN (REGIME_NOT_EXISTS = 'Y') THEN
1815                 INTO ZX_VALIDATION_ERRORS_GT(
1816                         application_id,
1817                         entity_code,
1818                         event_class_code,
1819                         trx_id,
1820                         trx_line_id,
1821                         summary_tax_line_number,
1822                         message_name,
1823                         message_text,
1824                         trx_level_type,
1825                         interface_tax_line_id
1826                         )
1827                 VALUES(
1828                         application_id,
1829                         entity_code,
1830                         event_class_code,
1831                         trx_id,
1832                         trx_line_id,
1833                         summary_tax_line_number,
1834                         'ZX_REGIME_NOT_EXIST',
1835                         l_regime_not_exists,
1836                         trx_level_type,
1837                         interface_tax_line_id
1838                          )
1839 
1840         WHEN (REGIME_NOT_EFF_IN_SUBSCR = 'Y') THEN
1841                 INTO ZX_VALIDATION_ERRORS_GT(
1842                         application_id,
1843                         entity_code,
1844                         event_class_code,
1845                         trx_id,
1846                         trx_line_id,
1847                         summary_tax_line_number,
1848                         message_name,
1849                         message_text,
1850                         trx_level_type,
1851                         interface_tax_line_id
1852                         )
1853                 VALUES(
1854                         application_id,
1855                         entity_code,
1856                         event_class_code,
1857                         trx_id,
1858                         trx_line_id,
1859                         summary_tax_line_number,
1860                         'ZX_REGIME_NOT_EFF_IN_SUBSCR',
1861                         l_regime_not_eff_in_subscr,
1862                         trx_level_type,
1863                         interface_tax_line_id
1864                          )
1865         WHEN (REGIME_NOT_EFFECTIVE = 'Y')  THEN
1866                 INTO ZX_VALIDATION_ERRORS_GT(
1867                         application_id,
1868                         entity_code,
1869                         event_class_code,
1870                         trx_id,
1871                         trx_line_id,
1872                         summary_tax_line_number,
1873                         message_name,
1874                         message_text,
1875                         trx_level_type,
1876                         interface_tax_line_id
1877                         )
1878                 VALUES(
1879                         application_id,
1880                         entity_code,
1881                         event_class_code,
1882                         trx_id,
1883                         trx_line_id,
1884                         summary_tax_line_number,
1885                         'ZX_REGIME_NOT_EFFECTIVE',
1886                         l_regime_not_effective,
1887                         trx_level_type,
1888                         interface_tax_line_id
1889                          )
1890 
1891         WHEN (TAX_NOT_EXISTS = 'Y')  THEN
1892                         INTO ZX_VALIDATION_ERRORS_GT(
1893                                 application_id,
1894                                 entity_code,
1895                                 event_class_code,
1896                                 trx_id,
1897                                 trx_line_id,
1898                                 summary_tax_line_number,
1899                                 message_name,
1900                                 message_text,
1901                                 trx_level_type,
1902                                 interface_tax_line_id
1903                                 )
1904                         VALUES(
1905                                 application_id,
1906                                 entity_code,
1907                                 event_class_code,
1908                                 trx_id,
1909                                 trx_line_id,
1910                                 summary_tax_line_number,
1911                                 'ZX_TAX_NOT_EXIST',
1912                                 l_tax_not_exists,
1913                                 trx_level_type,
1914                                 interface_tax_line_id
1915                                  )
1916 
1917         WHEN (TAX_NOT_LIVE = 'Y')  THEN
1918                         INTO ZX_VALIDATION_ERRORS_GT(
1919                                 application_id,
1920                                 entity_code,
1921                                 event_class_code,
1922                                 trx_id,
1923                                 trx_line_id,
1924                                 summary_tax_line_number,
1925                                 message_name,
1926                                 message_text,
1927                                 trx_level_type,
1928                                 interface_tax_line_id
1929                                 )
1930                         VALUES(
1931                                 application_id,
1932                                 entity_code,
1933                                 event_class_code,
1934                                 trx_id,
1935                                 trx_line_id,
1936                                 summary_tax_line_number,
1937                                 'ZX_TAX_NOT_LIVE',
1938                                 l_tax_not_live,
1939                                 trx_level_type,
1940                                 interface_tax_line_id
1941                                  )
1942 
1943         WHEN (TAX_NOT_EFFECTIVE = 'Y')  THEN
1944                         INTO ZX_VALIDATION_ERRORS_GT(
1945                                 application_id,
1946                                 entity_code,
1947                                 event_class_code,
1948                                 trx_id,
1949                                 trx_line_id,
1950                                 summary_tax_line_number,
1951                                 message_name,
1952                                 message_text,
1953                                 trx_level_type,
1954                                 interface_tax_line_id
1955                                 )
1956                         VALUES(
1957                                 application_id,
1958                                 entity_code,
1959                                 event_class_code,
1960                                 trx_id,
1961                                 trx_line_id,
1962                                 summary_tax_line_number,
1963                                 'ZX_TAX_NOT_EFFECTIVE',
1964                                 l_tax_not_effective,
1965                                 trx_level_type,
1966                                 interface_tax_line_id
1967                                  )
1968 
1969         WHEN (TAX_STATUS_NOT_EXISTS = 'Y')  THEN
1970                         INTO ZX_VALIDATION_ERRORS_GT(
1971                                 application_id,
1972                                 entity_code,
1973                                 event_class_code,
1974                                 trx_id,
1975                                 trx_line_id,
1976                                 summary_tax_line_number,
1977                                 message_name,
1978                                 message_text,
1979                                 trx_level_type,
1980                                 interface_tax_line_id
1981                                 )
1982                         VALUES(
1983                                 application_id,
1984                                 entity_code,
1985                                 event_class_code,
1986                                 trx_id,
1987                                 trx_line_id,
1988                                 summary_tax_line_number,
1989                                 'ZX_TAX_STATUS_NOT_EXIST',
1990                                 l_tax_status_not_exists,
1991                                 trx_level_type,
1992                                 interface_tax_line_id
1993                                  )
1994 
1995         WHEN (TAX_STATUS_NOT_EFFECTIVE = 'Y')  THEN
1996                         INTO ZX_VALIDATION_ERRORS_GT(
1997                                 application_id,
1998                                 entity_code,
1999                                 event_class_code,
2000                                 trx_id,
2001                                 trx_line_id,
2002                                 summary_tax_line_number,
2003                                 message_name,
2004                                 message_text,
2005                                 trx_level_type,
2006                                 interface_tax_line_id
2007                                 )
2008                         VALUES(
2009                                 application_id,
2010                                 entity_code,
2011                                 event_class_code,
2012                                 trx_id,
2013                                 trx_line_id,
2014                                 summary_tax_line_number,
2015                                 'ZX_TAX_STATUS_NOT_EFFECTIVE',
2016                                 l_tax_status_not_effective,
2017                                 trx_level_type,
2018                                 interface_tax_line_id
2019                                  )
2020 
2021         WHEN (TAX_JUR_CODE_NA_FOR_LTE = 'Y') THEN
2022                         INTO ZX_VALIDATION_ERRORS_GT(
2023                                 application_id,
2024                                 entity_code,
2025                                 event_class_code,
2026                                 trx_id,
2027                                 trx_line_id,
2028                                 summary_tax_line_number,
2029                                 message_name,
2030                                 message_text,
2031                                 trx_level_type,
2032                                 interface_tax_line_id
2033                                 )
2034                         VALUES(
2035                                 application_id,
2036                                 entity_code,
2037                                 event_class_code,
2038                                 trx_id,
2039                                 trx_line_id,
2040                                 summary_tax_line_number,
2041                                 'ZX_TAX_JUR_CODE_NA_FOR_LTE',
2042                                 l_tax_jur_code_na_for_lte,
2043                                 trx_level_type,
2044                                 interface_tax_line_id
2045                                 )
2046 
2047                 SELECT
2048                 header.application_id,
2049                 header.entity_code,
2050                 header.event_class_code,
2051                 header.trx_id,
2052                 lines_gt.trx_line_id,
2053                 lines_gt.trx_level_type,
2054                 taxlines_gt.interface_tax_line_id,
2055                 taxlines_gt.summary_tax_line_number,
2056                 -- Check for Regime Existence
2057                 CASE WHEN taxlines_gt.tax_regime_code IS NOT NULL AND
2058                           regime.tax_regime_code IS NULL
2059                      THEN 'Y'
2060                      ELSE 'N'
2061                 END  REGIME_NOT_EXISTS,
2062 
2063                 -- Check for Regime Effectivity in surscription detail table
2064                 CASE WHEN taxlines_gt.tax_regime_code IS NOT NULL
2065                           AND regime.tax_regime_code IS NOT NULL
2066                      THEN
2067                      CASE WHEN sd_reg.tax_regime_code IS NULL
2068                           THEN 'Y'
2069                           ELSE 'N' END
2070                      ELSE 'N'
2071                 END REGIME_NOT_EFF_IN_SUBSCR,
2072 
2073                 -- Check for Regime Effectivity
2074                 CASE WHEN taxlines_gt.tax_regime_code IS NOT NULL
2075                           AND regime.tax_regime_code IS NOT NULL
2076                           AND sd_reg.tax_regime_code IS NOT NULL
2077                      THEN
2078                      CASE WHEN header.trx_date
2079                                BETWEEN regime.effective_from
2080                                AND NVL(regime.effective_to,header.trx_date)
2081                           THEN 'N'
2082                           ELSE 'Y' END
2083                      ELSE 'N'
2084                 END REGIME_NOT_EFFECTIVE,
2085 
2086                 -- Check for Tax Existence
2087                 nvl2(taxlines_gt.tax,
2088                      CASE WHEN (sd_tax.tax_regime_code IS NOT NULL AND
2089                                 tax.tax is not null)
2090                           THEN 'N'
2091                           ELSE 'Y' END,
2092                      'N') TAX_NOT_EXISTS,
2093 
2094                 -- Check for Tax Live flag
2095                 nvl2(taxlines_gt.tax,
2096                      CASE WHEN (sd_tax.tax_regime_code IS NOT NULL AND
2097                                 tax.tax is not NULL )
2098                           THEN
2099                           CASE WHEN tax.live_for_processing_flag = 'Y'
2100                                THEN 'N'
2101                                ELSE 'Y' END
2102                           ELSE 'N' END,
2103                      'N') TAX_NOT_LIVE,
2104 
2105                 -- Check for Tax Effectivity
2106                 nvl2(taxlines_gt.tax,
2107                      CASE WHEN (sd_tax.tax_regime_code IS NOT NULL AND
2108                                 tax.tax is not null)
2109                           THEN
2110                           CASE WHEN header.trx_date
2111                                     BETWEEN tax.effective_from AND
2112                                     NVL(tax.effective_to,header.trx_date)
2113                                THEN 'N'
2114                                ELSE 'Y' END
2115                           ELSE 'N' END ,
2116                      'N')  TAX_NOT_EFFECTIVE,
2117 
2118                 -- Check for Status Existence
2119                 nvl2(taxlines_gt.tax_status_code,
2120                      CASE WHEN(sd_status.tax_regime_code IS NOT NULL AND
2121                                status.tax_status_code is not null)
2122                           THEN 'N'
2123                           ELSE 'Y' END,
2124                      'N') TAX_STATUS_NOT_EXISTS,
2125 
2126                 -- Check for Status Effectivity
2127                 nvl2(taxlines_gt.tax_status_code,
2128                      CASE WHEN(sd_status.tax_regime_code IS NOT NULL AND
2129                                status.tax_status_code IS NOT NULL)
2130                           THEN  CASE WHEN header.trx_date
2131                                      BETWEEN status.effective_from AND
2132                                      nvl(status.effective_to,header.trx_date)
2133                                 THEN 'N'
2134                                 ELSE 'Y' END
2135                           ELSE 'N' END,
2136                      'N') TAX_STATUS_NOT_EFFECTIVE,
2137 
2138                 -- Check for Tax Jurisdiction
2139                 CASE WHEN (taxlines_gt.TAX_JURISDICTION_CODE IS NOT NULL)
2140                      THEN 'Y'
2141                      ELSE 'N'
2142                 END TAX_JUR_CODE_NA_FOR_LTE
2143 
2144               FROM
2145                      ZX_TRX_HEADERS_GT             header,
2146                      ZX_REGIMES_B                  regime,
2147                      ZX_TAXES_B                    tax,
2148                      ZX_STATUS_B                   status,
2149                      ZX_TRANSACTION_LINES_GT       lines_gt,
2150                      ZX_IMPORT_TAX_LINES_GT        taxlines_gt,
2151                      ZX_SUBSCRIPTION_DETAILS       sd_reg,
2152                      ZX_SUBSCRIPTION_DETAILS       sd_tax,
2153                      ZX_SUBSCRIPTION_DETAILS       sd_status
2154 
2155               WHERE
2156                     lines_gt.trx_id = header.trx_id
2157                 AND taxlines_gt.trx_id  = header.trx_id
2158                 AND taxlines_gt.application_id = Header.application_id
2159                 AND taxlines_gt.entity_code    = Header.entity_code
2160                 AND taxlines_gt.event_class_code
2161                                              = Header.event_class_code
2162                 AND header.application_id    = lines_gt.application_id
2163                 AND header.entity_code       = lines_gt.entity_code
2164                 AND header.event_class_code  = lines_gt.event_class_code
2165                 AND lines_gt.trx_line_id     = taxlines_gt.trx_line_id
2166                -- Regime
2167                 AND regime.tax_regime_code(+)= taxlines_gt.tax_regime_code
2168                 AND regime.tax_regime_code = sd_reg.tax_regime_code (+)
2169                 AND sd_reg.first_pty_org_id(+) = g_first_pty_org_id
2170                 AND NVL(sd_reg.view_options_code,'NONE') in ('NONE', 'VFC')
2171                 AND (header.trx_date BETWEEN
2172                      nvl(regime.effective_from,header.trx_date) AND
2173                      nvl(regime.effective_to, header.trx_date)
2174                      OR
2175                      regime.effective_from = (select min(effective_from)
2176                                               from zx_regimes_b
2177                                               where tax_regime_code =
2178                                               regime.tax_regime_code)
2179                      )
2180                 AND (header.trx_date between
2181                       nvl(sd_reg.effective_from, header.trx_date) AND
2182                       nvl(sd_reg.effective_to, header.trx_date)
2183                      )
2184                 -- Tax
2185                 AND tax.tax(+) = taxlines_gt.tax
2186                 AND tax.tax_regime_code(+) = taxlines_gt.tax_regime_code
2187                 AND tax.tax_regime_code = sd_tax.tax_regime_code (+)
2188                 AND (tax.content_owner_id = sd_tax.parent_first_pty_org_id  or
2189                      sd_tax.parent_first_pty_org_id is null)
2190                 AND sd_tax.first_pty_org_id(+) = g_first_pty_org_id
2191                 AND (header.trx_date BETWEEN
2192                      nvl(tax.effective_from,header.trx_date) AND
2193                      nvl(tax.effective_to, header.trx_date)
2194                      OR
2195                      tax.effective_from = (select min(effective_from)
2196                                            from zx_taxes_b
2197                                            where tax = tax.tax)
2198                     )
2199                 AND (header.trx_date between
2200                      nvl(sd_tax.effective_from,header.trx_date) AND
2201                      NVL(sd_tax.effective_to,header.trx_date)
2202                     )
2203                 AND ( nvl(sd_tax.view_options_code,'NONE') in ('NONE', 'VFC')
2204                       or
2205                       ( nvl(sd_tax.view_options_code,'VFR') = 'VFR'
2206                         AND not exists
2207                         ( SELECT 1
2208                           FROM zx_taxes_b b
2209                           WHERE tax.tax_regime_code = b.tax_regime_code
2210                           AND tax.tax = b.tax
2211                           AND sd_tax.first_pty_org_id = b.content_owner_id )
2212                       )
2213                     )
2214                 -- Status
2215                 AND status.tax_status_code(+) = taxlines_gt.tax_status_code
2216                 AND status.tax(+) = taxlines_gt.tax
2217                 AND status.tax_regime_code(+) = taxlines_gt.tax_regime_code
2218                 AND status.tax_regime_code = sd_status.tax_regime_code (+)
2219                 AND (status.content_owner_id = sd_status.parent_first_pty_org_id
2220                      or sd_status.parent_first_pty_org_id is null)
2221                 AND sd_status.first_pty_org_id(+) = g_first_pty_org_id
2222                 AND (header.trx_date BETWEEN
2223                      nvl(status.effective_from,header.trx_date) AND
2224                      nvl(status.effective_to, header.trx_date)
2225                      OR
2226                      status.effective_from = (select min(effective_from)
2227                                               from zx_status_b
2228                                               where tax_status_code =
2229                                               status.tax_status_code)
2230                     )
2231                 AND (header.trx_date between
2232                      nvl(sd_status.effective_from,header.trx_date) AND
2233                      nvl(sd_status.effective_to,header.trx_date)
2234                     )
2235                 AND (nvl(sd_status.view_options_code,'NONE') in ('NONE', 'VFC')
2236                      or (nvl(sd_status.view_options_code,'VFR') = 'VFR'
2237                          AND not exists
2238                          (SELECT 1
2239                             FROM zx_status_vl b
2240                            WHERE b.tax_regime_code = status.tax_regime_code
2241                              AND b.tax = status.tax
2242                              AND b.tax_status_code = status.tax_status_code
2243                              AND b.content_owner_id = sd_status.first_pty_org_id)
2244                         )
2245                     );
2246 
2247       IF (g_level_procedure >= g_current_runtime_level ) THEN
2248          FND_LOG.STRING(g_level_procedure,
2249                         'JL.PL/SQL.JL_ZZ_TAX_VALIDATE_PKG.VALIDATE_TAX_ATTR',
2250                         'Regime, Tax and Status Validation Errors : '|| To_Char(SQL%ROWCOUNT) );
2251       END IF;
2252 
2253         INSERT ALL
2254         WHEN (TAX_RATE_CODE_NOT_EXISTS = 'Y')  THEN
2255                         INTO ZX_VALIDATION_ERRORS_GT(
2256                                 application_id,
2257                                 entity_code,
2258                                 event_class_code,
2259                                 trx_id,
2260                                 trx_line_id,
2261                                 summary_tax_line_number,
2262                                 message_name,
2263                                 message_text,
2264                                 trx_level_type,
2265                                 interface_tax_line_id
2266                                 )
2267                         VALUES(
2268                                 application_id,
2269                                 entity_code,
2270                                 event_class_code,
2271                                 trx_id,
2272                                 NULL,
2273                                 summary_tax_line_number,
2274                                 'ZX_TAX_RATE_NOT_EXIST',
2275                                 l_tax_rate_not_exists ||' (Tax_Rate_Code: '||
2276                                          tax_rate_code||', Tax_Rate_Id: '||tax_rate_id||')',
2277                                 NULL,
2278                                 interface_tax_line_id
2279                                  )
2280 
2281         WHEN (TAX_RATE_CODE_NOT_EFFECTIVE = 'Y')  THEN
2282                         INTO ZX_VALIDATION_ERRORS_GT(
2283                                 application_id,
2284                                 entity_code,
2285                                 event_class_code,
2286                                 trx_id,
2287                                 trx_line_id,
2288                                 summary_tax_line_number,
2289                                 message_name,
2290                                 message_text,
2291                                 trx_level_type,
2292                                 interface_tax_line_id
2293                                 )
2294                         VALUES(
2295                                 application_id,
2296                                 entity_code,
2297                                 event_class_code,
2298                                 trx_id,
2299                                 NULL,
2300                                 summary_tax_line_number,
2301                                 'ZX_TAX_RATE_NOT_EFFECTIVE',
2302                                 l_tax_rate_not_effective ||' (Tax_Rate_Code: '||
2303                                             tax_rate_code||', Tax_Rate_Id: '||tax_rate_id||')',
2304                                 NULL,
2305                                 interface_tax_line_id
2306                                 )
2307 
2308         WHEN (TAX_RATE_CODE_NOT_ACTIVE = 'Y')  THEN
2309                         INTO ZX_VALIDATION_ERRORS_GT(
2310                                 application_id,
2311                                 entity_code,
2312                                 event_class_code,
2313                                 trx_id,
2314                                 trx_line_id,
2315                                 summary_tax_line_number,
2316                                 message_name,
2317                                 message_text,
2318                                 trx_level_type,
2319                                 interface_tax_line_id
2320                                 )
2321                         VALUES(
2322                                 application_id,
2323                                 entity_code,
2324                                 event_class_code,
2325                                 trx_id,
2326                                 NULL,
2327                                 summary_tax_line_number,
2328                                 'ZX_TAX_RATE_NOT_ACTIVE',
2329                                 l_tax_rate_not_active ||' (Tax_Rate_Code: '||
2330                                          tax_rate_code||', Tax_Rate_Id: '||tax_rate_id||')',
2331                                 NULL,
2332                                 interface_tax_line_id
2333                                  )
2334 
2335         WHEN (TAX_RATE_PERCENTAGE_INVALID = 'Y')  THEN
2336                         INTO ZX_VALIDATION_ERRORS_GT(
2337                                 application_id,
2338                                 entity_code,
2339                                 event_class_code,
2340                                 trx_id,
2341                                 trx_line_id,
2342                                 summary_tax_line_number,
2343                                 message_name,
2344                                 message_text,
2345                                 trx_level_type,
2346                                 interface_tax_line_id
2347                                 )
2348                         VALUES(
2349                                 application_id,
2350                                 entity_code,
2351                                 event_class_code,
2352                                 trx_id,
2353                                 NULL,
2354                                 summary_tax_line_number,
2355                                 'ZX_TAX_RATE_PERCENTAGE_INVALID',
2356                                 l_tax_rate_percentage_invalid ||' (Tax_Rate_Code: '||
2357                                                  tax_rate_code||', Tax_Rate_Id: '||tax_rate_id||')',
2358                                 NULL,
2359                                 interface_tax_line_id
2360                                  )
2361         SELECT  application_id,
2362                 entity_code,
2363                 event_class_code,
2364                 trx_id,
2365                 summary_tax_line_number,
2366                 interface_tax_line_id,
2367                 interface_line_id,
2368                 trx_line_id,
2369                 trx_level_type,
2370                 tax_rate_code,
2371                 tax_rate_id,
2372                 TAX_RATE_CODE_NOT_EXISTS,
2373                 DECODE(TAX_RATE_CODE_NOT_EXISTS,'Y','N',TAX_RATE_CODE_NOT_EFFECTIVE) TAX_RATE_CODE_NOT_EFFECTIVE,
2374                 DECODE(TAX_RATE_CODE_NOT_EXISTS,'Y','N',TAX_RATE_CODE_NOT_ACTIVE) TAX_RATE_CODE_NOT_ACTIVE,
2375                 DECODE(TAX_RATE_CODE_NOT_EXISTS,'Y','N',TAX_RATE_PERCENTAGE_INVALID) TAX_RATE_PERCENTAGE_INVALID
2376           FROM
2377               (SELECT
2378                   header.application_id application_id,
2379                   header.entity_code entity_code,
2380                   header.event_class_code,
2381                   header.trx_id trx_id,
2382                   taxlines_gt.summary_tax_line_number summary_tax_line_number,
2383                   taxlines_gt.summary_tax_line_number interface_tax_line_id,
2384                   lines_gt.trx_line_id     interface_line_id,
2385                   lines_gt.trx_line_id     trx_line_id,
2386                   lines_gt.trx_level_type  trx_level_type,
2387                   taxlines_gt.tax_rate_code,
2388                   taxlines_gt.tax_rate_id,
2389                   -- Check for Rate Code Existence
2390                   nvl2(taxlines_gt.tax_rate_code,
2391                        CASE WHEN (sd_rates.tax_regime_code is not null and
2392                                   rate.tax_rate_code is not NULL )
2393                              THEN CASE WHEN taxlines_gt.tax_rate_id IS NOT NULL
2394                                             AND NOT EXISTS ( SELECT 1 FROM zx_rates_b
2395                                                               WHERE tax_rate_id = taxlines_gt.tax_rate_id)
2396                                        THEN 'Y'
2397                                        ELSE 'N' END
2398                              ELSE 'Y' END,
2399                        'N') TAX_RATE_CODE_NOT_EXISTS,
2400                   -- Check for Rate Code Effective
2401                   nvl2(taxlines_gt.tax_rate_code,
2402                        CASE WHEN rate.effective_to IS NOT NULL AND
2403                                  (header.trx_date NOT BETWEEN rate.effective_from AND rate.effective_to) AND
2404                                  NOT EXISTS (SELECT 1 FROM zx_rates_b zrb
2405                                               WHERE zrb.tax_rate_code = rate.tax_rate_code
2406                                                 AND zrb.tax_regime_code = rate.tax_regime_code
2407                                                 AND zrb.tax = rate.tax
2408                                                 AND zrb.tax_status_code = rate.tax_status_code
2409                                                 AND zrb.content_owner_id = rate.content_owner_id
2410                                                 AND zrb.active_flag = 'Y'
2411                                                 AND zrb.tax_rate_id <> rate.tax_rate_id
2412                                                 AND header.trx_date BETWEEN zrb.effective_from AND
2413                                                         nvl(zrb.effective_to, header.trx_date)
2414                                             )
2415                             THEN 'Y'
2416                             ELSE 'N' END,
2417                        'N') TAX_RATE_CODE_NOT_EFFECTIVE,
2418                   -- Check Rate Code is Active
2419                   nvl2(taxlines_gt.tax_rate_code,
2420                        CASE WHEN rate.active_flag = 'N' AND
2421                                  (header.trx_date BETWEEN rate.effective_from AND
2422                                       nvl(rate.effective_to,header.trx_date)) AND
2423                                  NOT EXISTS (SELECT 1 FROM zx_rates_b zrb
2424                                               WHERE zrb.tax_rate_code = rate.tax_rate_code
2425                                                 AND zrb.tax_regime_code = rate.tax_regime_code
2426                                                 AND zrb.tax = rate.tax
2427                                                 AND zrb.tax_status_code = rate.tax_status_code
2428                                                 AND zrb.content_owner_id = rate.content_owner_id
2429                                                 AND zrb.active_flag = 'Y'
2430                                                 AND zrb.tax_rate_id <> rate.tax_rate_id
2431                                                 AND header.trx_date BETWEEN zrb.effective_from AND
2432                                                         nvl(zrb.effective_to, header.trx_date)
2433                                             )
2434                             THEN 'Y'
2435                             ELSE 'N' END,
2436                        'N') TAX_RATE_CODE_NOT_ACTIVE,
2437                   -- Check for Rate Percentage
2438                   nvl2(taxlines_gt.tax_rate_code,
2439                        CASE WHEN taxlines_gt.tax_rate IS NOT NULL AND
2440                                  rate.percentage_rate <> taxlines_gt.tax_rate AND
2441                                  nvl(rate.allow_adhoc_tax_rate_flag,'N') <> 'Y' AND
2442                                  rate.active_flag = 'Y' AND
2443                                  (header.trx_date BETWEEN rate.effective_from AND
2444                                       nvl(rate.effective_to, header.trx_date))
2445                             THEN 'Y'
2446                             ELSE 'N' END,
2447                        'N') TAX_RATE_PERCENTAGE_INVALID
2448                  FROM ZX_TRX_HEADERS_GT header,
2449                       ZX_RATES_B rate ,
2450                       ZX_IMPORT_TAX_LINES_GT taxlines_gt,
2451                       ZX_TRANSACTION_LINES_GT lines_gt,
2452                       ZX_SUBSCRIPTION_DETAILS sd_rates
2453                 WHERE taxlines_gt.trx_id = header.trx_id
2454                   AND taxlines_gt.application_id = Header.application_id
2455                   AND taxlines_gt.entity_code = Header.entity_code
2456                   AND taxlines_gt.event_class_code = Header.event_class_code
2457                   AND (taxlines_gt.tax_rate_code IS NOT NULL OR taxlines_gt.tax_rate_id IS NOT NULL)
2458                   AND lines_gt.application_id = header.application_id
2459                   AND lines_gt.entity_code = header.entity_code
2460                   AND lines_gt.event_class_code = header.event_class_code
2461                   AND lines_gt.trx_id = header.trx_id
2462                   AND lines_gt.trx_line_id = taxlines_gt.trx_line_id
2463                   AND ((taxlines_gt.tax_rate_code IS NOT NULL AND
2464                         rate.tax_rate_code = taxlines_gt.tax_rate_code)
2465                       OR
2466                        (taxlines_gt.tax_rate_id IS NOT NULL AND
2467                         rate.tax_rate_id = taxlines_gt.tax_rate_id))
2468                   AND (taxlines_gt.tax_status_code IS NULL OR rate.tax_status_code = taxlines_gt.tax_status_code)
2469                   AND (taxlines_gt.tax IS NULL OR rate.tax = taxlines_gt.tax)
2470                   AND (taxlines_gt.tax_regime_code IS NULL OR rate.tax_regime_code = taxlines_gt.tax_regime_code)
2471                   AND rate.tax_regime_code = sd_rates.tax_regime_code (+)
2472                   AND (rate.content_owner_id = sd_rates.parent_first_pty_org_id
2473                        OR sd_rates.parent_first_pty_org_id is NULL)
2474                   AND sd_rates.first_pty_org_id(+) = g_first_pty_org_id
2475                   AND (header.trx_date BETWEEN
2476                               nvl(sd_rates.effective_from,header.trx_date) AND
2477                               nvl(sd_rates.effective_to,header.trx_date)
2478                       )
2479                   AND (NVL(sd_rates.view_options_code,'NONE') IN ('NONE', 'VFC')
2480                        OR (NVL(sd_rates.view_options_code, 'VFR') = 'VFR'
2481                            AND NOT EXISTS
2482                            (SELECT 1
2483                               FROM zx_rates_b b
2484                              WHERE b.tax_regime_code = rate.tax_regime_code
2485                                AND b.tax = rate.tax
2486                                AND b.tax_status_code = rate.tax_status_code
2487                                AND b.tax_rate_code = rate.tax_rate_code
2488                                AND b.content_owner_id = sd_rates.first_pty_org_id
2489                            )
2490                           )
2491                       )
2492               );
2493 
2494       IF (g_level_procedure >= g_current_runtime_level ) THEN
2495          FND_LOG.STRING(g_level_procedure,
2496                         'JL.PL/SQL.JL_ZZ_TAX_VALIDATE_PKG.VALIDATE_TAX_ATTR',
2497                         'Tax Rate Validation Errors : '|| To_Char(SQL%ROWCOUNT) );
2498       END IF;
2499 
2500        INSERT ALL
2501             WHEN (SAMESUMTX_MULTIALLOC_TO_SAMELN = 'Y') THEN
2502               INTO zx_validation_errors_gt(
2503                    application_id,
2504                    entity_code,
2505                    event_class_code,
2506                    trx_id,
2507                    trx_line_id,
2508                    summary_tax_line_number,
2509                    message_name,
2510                    message_text,
2511                    trx_level_type,
2512                    interface_tax_line_id
2513                     )
2514               VALUES (
2515                    application_id,
2516                    entity_code,
2517                    event_class_code,
2518                    trx_id,
2519                    trx_line_id,
2520                    summary_tax_line_number,
2521                    'ZX_IMPTAX_MULTIALLOC_TO_SAMELN',
2522                    l_imptax_multialloc_to_sameln,
2523                    trx_level_type,
2524                    interface_tax_line_id
2525                     )
2526 
2527            /* bug 3698554 */
2528 
2529             WHEN (TAX_INCL_FLAG_MISMATCH = 'Y' ) THEN
2530               INTO zx_validation_errors_gt(
2531                    application_id,
2532                    entity_code,
2533                    event_class_code,
2534                    trx_id,
2535                    trx_line_id,
2536                    summary_tax_line_number,
2537                    message_name,
2538                    message_text,
2539                    trx_level_type,
2540                    interface_tax_line_id
2541                     )
2542               VALUES (
2543                    application_id,
2544                    entity_code,
2545                    event_class_code,
2546                    trx_id,
2547                    trx_line_id,
2548                    summary_tax_line_number,
2549                    'ZX_TAX_INCL_FLAG_MISMATCH',
2550                    l_tax_incl_flag_mismatch,
2551                    trx_level_type,
2552                    interface_tax_line_id
2553                   )
2554 
2555             WHEN (IMP_TAX_MISSING_IN_ADJUSTED_TO = 'Y') THEN
2556               INTO zx_validation_errors_gt(
2557                    application_id,
2558                    entity_code,
2559                    event_class_code,
2560                    trx_id,
2561                    trx_line_id,
2562                    summary_tax_line_number,
2563                    message_name,
2564                    message_text,
2565                    trx_level_type,
2566                    interface_tax_line_id
2567                     )
2568             VALUES (
2569                    application_id,
2570                    entity_code,
2571                    event_class_code,
2572                    trx_id,
2573                    trx_line_id,
2574                    summary_tax_line_number,
2575                    'IMP_TAX_MISSING_IN_ADJUSTED_TO',
2576                    l_imp_tax_missing_in_adjust_to,
2577                    trx_level_type,
2578                    interface_tax_line_id
2579                    )
2580 
2581             /* end bug 3698554 */
2582 
2583             SELECT
2584                    header.application_id,
2585                    header.entity_code,
2586                    header.event_class_code,
2587                    header.trx_id,
2588                    lines_gt.trx_line_id,
2589                    lines_gt.trx_level_type,
2590                    lines_gt.interface_line_id,
2591                    taxlines_gt.interface_tax_line_id,
2592                    taxlines_gt.summary_tax_line_number,
2593                    -- The same summary tax line cannot be allocated to the same transaction
2594                    -- line multi times
2595                    --
2596                    CASE
2597                      WHEN
2598                       (SELECT COUNT(*)
2599                          FROM zx_trx_tax_link_gt
2600                         WHERE application_id = taxlines_gt.application_id
2601                           AND entity_code = taxlines_gt.entity_code
2602                           AND event_class_code = taxlines_gt.event_class_code
2603                           AND trx_id = taxlines_gt.trx_id
2604                           AND trx_line_id = lines_gt.trx_line_id
2605                           AND trx_level_type = lines_gt.trx_level_type
2606                           AND summary_tax_line_number =
2607                                                   taxlines_gt.summary_tax_line_number
2608                       ) > 1
2609                      THEN
2610                          'Y'
2611                      ELSE
2612                          'N'
2613                    END SAMESUMTX_MULTIALLOC_TO_SAMELN,
2614 
2615                   /* bug 3698554 */
2616 
2617                   -- If the imported tax line has inclusive_flag = 'N' but the tax
2618                   -- is defined as inclusive in ZX_TAXES and allow inclusive override is N
2619                   -- or vice versa, then raise error
2620                   CASE
2621                      WHEN EXISTS
2622                           (
2623                           SELECT 1
2624                             FROM zx_taxes_b taxes
2625                            WHERE taxes.tax_regime_code = taxlines_gt.tax_regime_code
2626                              AND taxes.tax = taxlines_gt.tax
2627                              AND taxes.def_inclusive_tax_flag <> taxlines_gt.tax_amt_included_flag
2628                              AND taxes.tax_inclusive_override_flag = 'N'
2629                           )
2630                   THEN
2631                            'Y'
2632                      ELSE
2633                           'N'
2634                    END TAX_INCL_FLAG_MISMATCH,
2635                    /* end bug 3698554  */
2636 
2637                    CASE
2638                      WHEN lines_gt.adjusted_doc_application_id IS NOT NULL
2639                       AND NOT EXISTS
2640                           (SELECT 1
2641                              FROM zx_lines zl
2642                             WHERE zl.application_id = lines_gt.adjusted_doc_application_id
2643                               AND zl.entity_code = lines_gt.adjusted_doc_entity_code
2644                               AND zl.event_class_code = lines_gt.adjusted_doc_event_class_code
2645                               AND zl.trx_id = lines_gt.adjusted_doc_trx_id
2646                               AND zl.trx_line_id = lines_gt.adjusted_doc_line_id
2647                               AND zl.trx_level_type = lines_gt.adjusted_doc_trx_level_type
2648                               AND zl.tax_regime_code = taxlines_gt.tax_regime_code
2649                               AND zl.tax = taxlines_gt.tax
2650                           )
2651                      THEN
2652                          'Y'
2653                      ELSE
2654                          'N'
2655                    END IMP_TAX_MISSING_IN_ADJUSTED_TO
2656                    /* end bug 3676878  */
2657                FROM
2658                    zx_trx_headers_gt         header,
2659                    zx_transaction_lines_gt   lines_gt,
2660                    zx_import_tax_lines_gt    taxlines_gt
2661                WHERE
2662                    taxlines_gt.application_id = header.application_id
2663                AND taxlines_gt.entity_code = header.entity_code
2664                AND taxlines_gt.event_class_code = header.event_class_code
2665                AND taxlines_gt.trx_id = header.trx_id
2666                AND lines_gt.application_id = header.application_id
2667                AND lines_gt.entity_code = header.entity_code
2668                AND lines_gt.event_class_code = header.event_class_code
2669                AND lines_gt.trx_id = header.trx_id
2670                AND lines_gt.trx_line_id = taxlines_gt.trx_line_id
2671                AND (taxlines_gt.tax_line_allocation_flag = 'Y'
2672                     AND EXISTS
2673                     (SELECT 1
2674                        FROM zx_trx_tax_link_gt
2675                       WHERE application_id = taxlines_gt.application_id
2676                         AND entity_code = taxlines_gt.entity_code
2677                         AND event_class_code = taxlines_gt.event_class_code
2678                         AND trx_id = taxlines_gt.trx_id
2679                         AND summary_tax_line_number = taxlines_gt.summary_tax_line_number
2680                         AND trx_line_id = lines_gt.trx_line_id
2681                         AND trx_level_type = lines_gt.trx_level_type
2682                     ) OR
2683                     (taxlines_gt.tax_line_allocation_flag = 'N'
2684                      AND lines_gt.applied_from_application_id IS NULL
2685                      AND lines_gt.adjusted_doc_application_id IS NULL
2686                      AND lines_gt.applied_to_application_id IS NULL
2687                      AND lines_gt.line_level_action = 'CREATE_WITH_TAX'
2688                     )
2689                    );
2690 
2691       IF (g_level_procedure >= g_current_runtime_level ) THEN
2692          FND_LOG.STRING(g_level_procedure,
2693                         'JL.PL/SQL.JL_ZZ_TAX_VALIDATE_PKG.VALIDATE_TAX_ATTR',
2694                         'Tax Allocation Validation Errors : '|| To_Char(SQL%ROWCOUNT) );
2695       END IF;
2696 
2697    END IF; -- IF g_tax_lines_count > 0
2698 
2699 
2700   EXCEPTION
2701          WHEN OTHERS THEN
2702               IF (g_level_unexpected >= g_current_runtime_level ) THEN
2703                   FND_LOG.STRING(g_level_unexpected,
2704                                  'JL_ZZ_TAX_VALIDATE_PKG.VALIDATE_TAX_ATTR',
2705                                  sqlerrm);
2706               END IF;
2707               x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2708               app_exception.raise_exception;
2709 
2710 END validate_tax_attr;
2711 
2712 --Constructor
2713 BEGIN
2714 
2715   g_level_statement       := FND_LOG.LEVEL_STATEMENT;
2716   g_level_procedure       := FND_LOG.LEVEL_PROCEDURE;
2717   g_level_event           := FND_LOG.LEVEL_EVENT;
2718   g_level_exception       := FND_LOG.LEVEL_EXCEPTION;
2719   g_level_unexpected      := FND_LOG.LEVEL_UNEXPECTED;
2720   l_regime_not_exists           :=fnd_message.get_string('ZX','ZX_REGIME_NOT_EXIST' );
2721   l_regime_not_effective        :=fnd_message.get_string('ZX','ZX_REGIME_NOT_EFFECTIVE' );
2722   l_regime_not_eff_in_subscr    :=fnd_message.get_string('ZX','ZX_REGIME_NOT_EFF_IN_SUBSCR' );
2723   l_tax_not_exists              :=fnd_message.get_string('ZX','ZX_TAX_NOT_EXIST' );
2724   l_tax_not_live                :=fnd_message.get_string('ZX','ZX_TAX_NOT_LIVE' );
2725   l_tax_not_effective           :=fnd_message.get_string('ZX','ZX_TAX_NOT_EFFECTIVE' );
2726   l_tax_status_not_exists       :=fnd_message.get_string('ZX','ZX_TAX_STATUS_NOT_EXIST' );
2727   l_tax_status_not_effective    :=fnd_message.get_string('ZX','ZX_TAX_STATUS_NOT_EFFECTIVE' );
2728   l_tax_rate_not_exists         :=fnd_message.get_string('ZX','ZX_TAX_RATE_NOT_EXIST' );
2729   l_tax_rate_not_effective      :=fnd_message.get_string('ZX','ZX_TAX_RATE_NOT_EFFECTIVE' );
2730   l_tax_rate_not_active         :=fnd_message.get_string('ZX','ZX_TAX_RATE_NOT_ACTIVE' );
2731   l_tax_rate_percentage_invalid :=fnd_message.get_string('ZX','ZX_TAX_RATE_PERCENTAGE_INVALID' );
2732   l_evnt_cls_mpg_invalid        :=fnd_message.get_string('ZX','ZX_EVNT_CLS_MPG_INVALID' );
2733   l_exchg_info_missing          :=fnd_message.get_string('ZX','ZX_EXCHG_INFO_MISSING' );
2734   l_line_class_invalid          :=fnd_message.get_string('ZX','ZX_LINE_CLASS_INVALID' );
2735   l_trx_line_type_invalid       :=fnd_message.get_string('ZX','ZX_TRX_LINE_TYPE_INVALID' );
2736   l_line_amt_incl_tax_invalid   :=fnd_message.get_string('ZX','ZX_LINE_AMT_INCTAX_INVALID' );
2737   l_trx_biz_fc_code_not_exists  :=fnd_message.get_string('ZX','ZX_TRX_BIZ_FC_CODE_NOT_EXIST' );
2738   l_trx_biz_fc_code_not_effect  :=fnd_message.get_string('ZX','ZX_TRX_BIZ_FC_CODE_NOT_EFFECT' );
2739   l_prd_fc_code_not_exists      :=fnd_message.get_string('ZX','ZX_PRODUCT_FC_CODE_NOT_EXIST' );
2740   l_prd_category_not_exists     :=fnd_message.get_string('ZX','ZX_PRODUCT_CATEGORY_NOT_EXIST' );
2741   l_ship_to_party_not_exists    :=fnd_message.get_string('ZX','ZX_SHIP_TO_PARTY_NOT_EXIST' );
2742   l_ship_frm_party_not_exits    :=fnd_message.get_string('ZX','ZX_SHIP_FROM_PARTY_NOT_EXIST' );
2743   l_bill_to_party_not_exists    :=fnd_message.get_string('ZX','ZX_BILTO_PARTY_NOT_EXIST' );
2744   l_shipto_party_site_not_exists:=fnd_message.get_string('ZX','ZX_SHIPTO_PARTY_SITE_NOT_EXIST' );
2745   l_billto_party_site_not_exists:=fnd_message.get_string('ZX','ZX_BILLTO_PARTY_SITE_NOT_EXIST' );
2746   l_billfrm_party_site_not_exist:=fnd_message.get_string('ZX','ZX_BILLFROM_PARTYSITE_NOTEXIST' );
2747   l_tax_multialloc_to_sameln    :=fnd_message.get_string('ZX','ZX_TAX_MULTIALLOC_TO_SAMELN' );
2748   l_imptax_multialloc_to_sameln :=fnd_message.get_string('ZX','ZX_IMPTAX_MULTIALLOC_TO_SAMELN' );
2749 --l_tax_only_ln_w_null_tax_amt  :=fnd_message.get_string('ZX','ZX_TAX_ONLY_LN_W_NULL_TAX_AMT' );
2750   l_tax_incl_flag_mismatch      :=fnd_message.get_string('ZX','ZX_TAX_INCL_FLAG_MISMATCH' );
2751 --l_product_category_na_for_lte :=fnd_message.get_string('ZX','ZX_PRODUCT_CATEGORY_NA_FOR_LTE' );
2752   l_user_def_fc_na_for_lte      :=fnd_message.get_string('ZX','ZX_USER_DEF_FC_NA_FOR_LTE' );
2753   l_document_fc_na_for_lte      :=fnd_message.get_string('ZX','ZX_DOCUMENT_FC_NA_FOR_LTE' );
2754   l_indended_use_na_for_lte     :=fnd_message.get_string('ZX','ZX_INTENDED_USE_NA_FOR_LTE' );
2755   l_product_type_na_for_lte     :=fnd_message.get_string('ZX','ZX_PRODUCT_TYPE_NA_FOR_LTE' );
2756   l_tax_jur_code_na_for_lte     :=fnd_message.get_string('ZX','ZX_TAX_JUR_CODE_NA_FOR_LTE' );
2757 
2758 END jl_zz_tax_validate_pkg;