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