[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;