[Home] [Help]
PACKAGE BODY: APPS.JL_ZZ_TAX
Source
1 PACKAGE BODY JL_ZZ_TAX AS
2 /* $Header: jlzzrtxb.pls 120.27.12020000.3 2012/07/27 09:30:20 ssanka ship $ */
3
4 --PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('TAX_DEBUG_FLAG'), 'N');
5 -- Bugfix# 3259701
6 --PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
7
8 g_current_runtime_level NUMBER;
9 g_level_statement CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
10 g_level_procedure CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
11 g_level_event CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
12 g_level_exception CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
13 g_level_unexpected CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
14
15 PROCEDURE get_rule_legal_message (
16 p_tax_category_id IN NUMBER,
17 p_cust_trx_type_id IN NUMBER,
18 p_ship_to_site_use_id IN NUMBER,
19 p_bill_to_site_use_id IN NUMBER,
20 p_inventory_item_id IN NUMBER,
21 p_group_tax_id IN NUMBER,
22 p_memo_line_id IN NUMBER,
23 p_ship_to_customer_id IN NUMBER,
24 p_bill_to_customer_id IN NUMBER,
25 p_trx_date IN DATE,
26 p_application IN VARCHAR2,
27 p_ship_from_warehouse_id IN NUMBER,
28 p_fiscal_classification_code IN VARCHAR2,
29 p_inventory_organization_id IN NUMBER,
30 p_location_structure_id IN NUMBER,
31 p_location_segment_num IN NUMBER,
32 p_set_of_books_id IN NUMBER,
33 p_transaction_nature IN VARCHAR2,
34 p_base_amount IN NUMBER,
35 p_establishment_type IN VARCHAR2,
36 p_contributor_type IN VARCHAR2,
37 p_warehouse_location_id IN NUMBER,
38 p_transaction_nature_class IN VARCHAR2,
39 p_use_legal_message IN VARCHAR2,
40 p_base_rate IN NUMBER,
41 p_legal_message_exception IN VARCHAR2,
42 o_rule_id IN OUT NOCOPY NUMBER,
43 o_legal_message8 IN OUT NOCOPY VARCHAR2,
44 o_legal_message9 IN OUT NOCOPY VARCHAR2) IS
45
46 v_rule_id NUMBER;
47 v_tax_code VARCHAR2(50);
48 v_base_rate NUMBER;
49 v_rule_data_id NUMBER;
50 o_tax_code VARCHAR2(50);
51 o_base_rate NUMBER;
52 o_rule_data_id NUMBER;
53
54
55 BEGIN
56 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
57
58 IF (g_level_statement >= g_current_runtime_level) THEN
59 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_rule_legal_message()+');
60 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_rule_legal_message: ' || '-P- Tax category passed: '||to_char(p_tax_category_id));
61 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_rule_legal_message: ' || '-P- Tax base rate passed: '||to_char(p_base_rate));
62 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_rule_legal_message: ' || '-P- Tax base amount passed: '||to_char(p_base_amount));
63 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_rule_legal_message: ' || '-P- Tax group passed: '||to_char(p_group_tax_id));
64 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_rule_legal_message: ' || '-P- Transaction Type: '||to_char(p_cust_trx_type_id));
65 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_rule_legal_message: ' || '-P- Inventory Item Id: '||to_char(p_inventory_item_id));
66 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_rule_legal_message: ' || '-P- Memo Line Id: '||to_char(p_memo_line_id));
67 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_rule_legal_message: ' || '-P- Ship To Cust Id: '||to_char(p_ship_to_customer_id));
68 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_rule_legal_message: ' || '-P- Bill To Cust Id: '||to_char(p_bill_to_customer_id));
69 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_rule_legal_message: ' || '-P- Application: '||p_application);
70 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_rule_legal_message: ' || '-P- Ship From Warehouse Id: '||
71 to_char(p_ship_from_warehouse_id));
72 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_rule_legal_message: ' || '-P- Inventory Organization Id: '||
73 to_char(p_inventory_organization_id));
74 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_rule_legal_message: ' || '-P- Fiscal Classification Code: '||
75 p_fiscal_classification_code);
76 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_rule_legal_message: ' || '-P- Warehouse Location Id: '||
77 to_char(p_warehouse_location_id));
78 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_rule_legal_message: ' || '-P- Location Structure Id: '||
79 to_char(p_location_structure_id));
80 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_rule_legal_message: ' || '-P- Location Segment Number: '||p_location_segment_num);
81 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_rule_legal_message: ' || '-P- Trx Nature: '|| p_transaction_nature);
82 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_rule_legal_message: ' || '-P- Trx Nature Class: '||p_transaction_nature_class);
83 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','-P- Trx date: ' ||to_char(p_trx_date,'DD-MM-YYYY'));
84 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_rule_legal_message: ' || '-P- Ship to Site: '||to_char(p_ship_to_site_use_id));
85 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_rule_legal_message: ' || '-P- Bill to site: '||to_char(p_bill_to_site_use_id) );
86 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_rule_legal_message: ' || '-P- Establishment Type: '|| p_establishment_type);
87 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_rule_legal_message: ' || '-P- Contributor Type: '|| p_contributor_type);
88 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_rule_legal_message: ' || '-P- Set Of Books Id: '||to_char(p_set_of_books_id));
89 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_rule_legal_message: ' || '-P- Use Legal Message: '|| p_use_legal_message);
90 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_rule_legal_message: ' || '-P- Legal Message Exception: '|| p_legal_message_exception);
91 END IF;
92
93 get_category_tax_rule ( p_tax_category_id,
94 p_cust_trx_type_id,
95 p_ship_to_site_use_id,
96 p_bill_to_site_use_id,
97 p_inventory_item_id,
98 p_group_tax_id,
99 p_memo_line_id,
100 p_ship_to_customer_id,
101 p_bill_to_customer_id,
102 p_trx_date,
103 p_application,
104 p_ship_from_warehouse_id,
105 'RATE',
106 p_fiscal_classification_code,
107 p_inventory_organization_id,
108 p_location_structure_id,
109 p_location_segment_num,
110 p_set_of_books_id,
111 p_transaction_nature,
112 p_base_amount,
113 p_establishment_type,
114 p_contributor_type,
115 p_warehouse_location_id,
116 p_transaction_nature_class,
117 o_tax_code,
118 o_base_rate,
119 o_rule_data_id,
120 o_rule_id);
121
122 IF (g_level_statement >= g_current_runtime_level) THEN
123 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_rule_legal_message: ' || '-- Rule Id: '||to_char(o_rule_id));
124 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_rule_legal_message: ' || '-- Rule Data Id: '||to_char(o_rule_data_id));
125 END IF;
126
127 IF p_use_legal_message = 'Y' AND p_application = 'AR' THEN
128
129 IF ((p_base_rate IS NOT NULL) AND (p_base_rate <= 0)) THEN
130
131 get_category_tax_rule (
132 p_tax_category_id,
133 p_cust_trx_type_id,
134 p_ship_to_site_use_id,
135 p_bill_to_site_use_id,
136 p_inventory_item_id,
137 p_group_tax_id,
138 p_memo_line_id,
139 p_ship_to_customer_id,
140 p_bill_to_customer_id,
141 p_trx_date,
142 p_application,
143 p_ship_from_warehouse_id,
144 'BASE',
145 p_fiscal_classification_code,
146 p_inventory_organization_id,
147 p_location_structure_id,
148 p_location_segment_num,
149 p_set_of_books_id,
150 p_transaction_nature,
151 p_base_amount,
152 p_establishment_type,
153 p_contributor_type,
154 p_warehouse_location_id,
155 p_transaction_nature_class,
156 v_tax_code,
157 v_base_rate,
158 v_rule_data_id,
159 v_rule_id);
160
161 IF (g_level_statement >= g_current_runtime_level) THEN
162 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_rule_legal_message: ' || '-- Rule Id: '||to_char(v_rule_id));
163 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_rule_legal_message: ' || '-- Rule Data Id: '||to_char(v_rule_data_id));
164 END IF;
165
166 o_legal_message8 := get_legal_message (v_rule_id,
167 v_rule_data_id,
168 'BASE_AMOUNT_REDUCTION',
169 p_ship_from_warehouse_id);
170
171 IF (g_level_statement >= g_current_runtime_level) THEN
172 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_rule_legal_message: ' || '-- Legal Message8: '|| o_legal_message8);
173 END IF;
174
175 END IF;
176
177 IF p_legal_message_exception IS NOT NULL AND
178 (p_legal_message_exception <> 'BASE_AMOUNT_REDUCTION')
179 THEN
180 o_legal_message9 := get_legal_message ( o_rule_id,
181 o_rule_data_id,
182 p_legal_message_exception,
183 p_ship_from_warehouse_id);
184
185 IF (g_level_statement >= g_current_runtime_level) THEN
186 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_rule_legal_message: ' || '-- Legal Message9: '|| o_legal_message9);
187 END IF;
188
189 END IF;
190
191 END IF;
192
193 IF (g_level_statement >= g_current_runtime_level) THEN
194 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_rule_legal_message()-');
195 END IF;
196 END get_rule_legal_message;
197
198 --========================================================================
199 -- PUBLIC PROCEDURE
200 -- get_rule_info
201 --
202 -- DESCRIPTION
203 -- The procedure returns the tax code for a given rule
204 -- Each rule specifies a search for the tax code in a determined table.
205 -- For the new tables each row has a tax code associated to a tax
206 -- category. For the core product tables, the tax code can be TAX_TYPE =
207 -- 'TAX_GROUP' or 'VAT'. If the tax type is 'TAX_GROUP' the function
208 -- returns the tax code associated to the tax category passed to the
209 -- function. If the tax category is not defined in this tax group, the
210 -- function continues to search for the tax code in the other rules. If
211 -- the tax type is 'VAT' the tax code is considered to be associated to
212 -- the tax category of the tax code.
213 --
214 -- PARAMETERS
215 -- p_rule Rule to be checked
216 -- o_tax_code Tax code corresponding to the rule
217 --
218 -- RETURNS
219 -- tax code
220 --
221 -- CALLED FROM
222 -- jl_zz_tax.get_category_tax_code
223 -- jl_zz_tax.get_tax_base_rate
224 -- jl_zz_tax.get_category_tax_rule
225 --
226 -- HISTORY
227 --========================================================================
228
229 PROCEDURE get_rule_info(
230 p_rule IN VARCHAR2,
231 p_fiscal_classification_code IN VARCHAR2,
232 p_tax_category_id IN NUMBER,
233 p_trx_date IN DATE,
234 p_ship_to_site_use_id IN NUMBER,
235 p_bill_to_site_use_id IN NUMBER,
236 p_inventory_item_id IN NUMBER,
237 p_ship_from_warehouse_id IN NUMBER,
238 p_group_tax_id IN NUMBER,
239 p_contributor_type IN VARCHAR2,
240 p_transaction_nature IN VARCHAR2,
241 p_establishment_type IN VARCHAR2,
242 p_transaction_nature_class IN VARCHAR2,
243 p_inventory_organization_id IN NUMBER,
244 p_ship_to_customer_id IN NUMBER,
245 p_bill_to_customer_id IN NUMBER,
246 p_warehouse_location_id IN NUMBER,
247 p_memo_line_id IN NUMBER,
248 p_base_amount IN NUMBER,
249 p_application IN VARCHAR2,
250 o_tax_code IN OUT NOCOPY VARCHAR2,
251 o_base_rate IN OUT NOCOPY NUMBER,
252 o_rule_data_id IN OUT NOCOPY NUMBER ) IS
253
254 -- Bugfix 1388703
255 CURSOR c_category IS
256 SELECT cat.tax_code,
257 NULL,
258 NULL
259 FROM jl_zz_ar_tx_cat_dtl cat
260 WHERE cat.tax_category_id = p_tax_category_id
261 AND p_trx_date <= cat.end_date_active
262 AND p_trx_date >= NVL(cat.start_date_active,p_trx_date)
263 UNION
264 SELECT cat.tax_code,
265 NULL,
266 NULL
267 FROM jl_zz_ar_tx_categ cat
268 WHERE cat.tax_category_id = p_tax_category_id
269 AND p_trx_date <= cat.end_date_active
270 AND p_trx_date >= NVL(cat.start_date_active,p_trx_date);
271
272 -- Bugfix 1388703
273 CURSOR c_item IS
274 SELECT si.tax_code,
275 NULL,
276 si.inventory_item_id
277 FROM mtl_system_items si
278 WHERE si.inventory_item_id = p_inventory_item_id
279 AND si.organization_id = p_inventory_organization_id
280 AND exists (select 1
281 from ar_vat_tax vt
282 WHERE vt.tax_code = si.tax_code
283 AND vt.tax_type = 'VAT'
284 AND decode(ltrim(vt.global_attribute1, '0123456789'),
285 null, to_number(vt.global_attribute1), null) =
286 p_tax_category_id
287 AND nvl(vt.enabled_flag,'Y') = 'Y'
288 AND nvl(vt.tax_class,'O') = 'O'
289 AND p_trx_date >= vt.start_date
290 AND p_trx_date <= nvl(vt.end_date,p_trx_date))
291 UNION
292 SELECT tg.tax_code,
293 NULL,
294 si.inventory_item_id
295 FROM mtl_system_items si
296 ,ar_vat_tax vt
297 ,jl_zz_ar_tx_groups tg
298 ,ar_vat_tax vt1
299 WHERE si.inventory_item_id = p_inventory_item_id
300 AND si.organization_id = p_inventory_organization_id
301 AND si.tax_code = vt.tax_code
302 AND vt.tax_type = 'TAX_GROUP'
303 AND p_trx_date >= vt1.start_date
304 AND p_trx_date <= nvl(vt1.end_date,p_trx_date)
305 AND tg.group_tax_id = vt.vat_tax_id
306 AND nvl(vt1.enabled_flag,'Y') = 'Y'
307 AND nvl(vt1.tax_class,'O') = 'O'
308 AND tg.tax_category_id = p_tax_category_id
309 AND tg.contributor_type = p_contributor_type
310 AND tg.establishment_type = p_establishment_type
311 AND tg.transaction_nature = p_transaction_nature
312 AND tg.tax_code = vt1.tax_code
313 AND p_trx_date <= tg.end_date_active
314 AND p_trx_date >= NVL(tg.start_date_active, p_trx_date);
315
316
317 -- Bugfix 1388703
318 CURSOR c_customer IS
319 SELECT c.tax_code,
320 NULL,
321 c.cust_account_id
322 FROM hz_cust_accounts c
323 WHERE c.cust_account_id = NVL(p_ship_to_customer_id,
324 p_bill_to_customer_id)
325 AND exists (select 1
326 from ar_vat_tax vt
327 WHERE c.tax_code = vt.tax_code
328 AND vt.tax_type = 'VAT'
329 AND decode(ltrim(vt.global_attribute1,
330 '0123456789'),
331 null,
332 to_number(vt.global_attribute1),null) =
333 p_tax_category_id
334 AND p_trx_date >= vt.start_date
335 AND p_trx_date <= nvl(vt.end_date,p_trx_date)
336 AND nvl(vt.enabled_flag,'Y') = 'Y'
337 AND nvl(vt.tax_class,'O') = 'O'
338 )
339 UNION
340 SELECT tg.tax_code,
341 NULL,
342 c.cust_account_id
343 FROM hz_cust_accounts c
344 ,ar_vat_tax vt
345 ,jl_zz_ar_tx_groups tg
346 ,ar_vat_tax vt1
347 WHERE c.cust_account_id = NVL(p_ship_to_customer_id,
348 p_bill_to_customer_id)
349 AND c.tax_code = vt.tax_code
350 AND vt.tax_type = 'TAX_GROUP'
351 AND tg.tax_category_id = p_tax_category_id
352 AND tg.tax_code = vt1.tax_code
353 AND p_trx_date >= vt1.start_date
354 AND p_trx_date <= nvl(vt1.end_date,p_trx_date)
355 AND nvl(vt1.enabled_flag,'Y') = 'Y'
356 AND nvl(vt1.tax_class,'O') = 'O'
357 AND tg.group_tax_id = vt.vat_tax_id
358 AND tg.contributor_type = p_contributor_type
359 AND tg.transaction_nature = p_transaction_nature
360 AND tg.establishment_type = p_establishment_type
361 AND p_trx_date <= tg.end_date_active
362 AND p_trx_date >= NVL(tg.start_date_active, p_trx_date);
363
364 -- Bugfix 1388703
365 CURSOR c_bill_to IS
366 SELECT su.tax_code,
367 NULL,
368 su.site_use_id
369 FROM hz_cust_site_uses su
370 WHERE su.site_use_id = p_bill_to_site_use_id
371 AND exists (select 1
372 from ar_vat_tax vt
373 WHERE su.tax_code = vt.tax_code
374 AND vt.tax_type = 'VAT'
375 AND decode(ltrim(vt.global_attribute1,
376 '0123456789'),
377 null,
378 to_number(vt.global_attribute1),null) = p_tax_category_id
379 AND p_trx_date >= vt.start_date
380 AND p_trx_date <= nvl(vt.end_date,p_trx_date)
381 AND nvl(vt.enabled_flag,'Y') = 'Y'
382 AND nvl(vt.tax_class,'O') = 'O'
383 )
384 UNION
385 SELECT tg.tax_code,
386 NULL,
387 su.site_use_id
388 FROM hz_cust_site_uses su
389 ,ar_vat_tax vt
390 ,jl_zz_ar_tx_groups tg
391 ,ar_vat_tax vt1
392 WHERE su.site_use_id = p_bill_to_site_use_id
393 AND su.tax_code = vt.tax_code
394 AND vt.tax_type = 'TAX_GROUP'
395 AND tg.group_tax_id = vt.vat_tax_id
396 AND tg.tax_category_id = p_tax_category_id
397 AND tg.contributor_type = p_contributor_type
398 AND tg.transaction_nature = p_transaction_nature
399 AND tg.establishment_type = p_establishment_type
400 AND tg.tax_code = vt1.tax_code
401 AND p_trx_date >= vt1.start_date
402 AND p_trx_date <= nvl(vt1.end_date,p_trx_date)
403 AND nvl(vt1.enabled_flag,'Y') = 'Y'
404 AND nvl(vt1.tax_class,'O') = 'O'
405 AND p_trx_date <= tg.end_date_active
406 AND p_trx_date >= NVL(tg.start_date_active, p_trx_date);
407
408 -- Bugfix 1388703
409 CURSOR c_ship_to IS
410 SELECT su.tax_code,
411 NULL,
412 su.site_use_id
413 FROM hz_cust_site_uses su
414 WHERE su.site_use_id = p_ship_to_site_use_id
415 AND exists (select 1
416 from ar_vat_tax vt
417 WHERE su.tax_code = vt.tax_code
418 AND vt.tax_type = 'VAT'
419 AND decode(ltrim(vt.global_attribute1,
420 '0123456789'),null,
421 to_number(vt.global_attribute1),null)
422 = p_tax_category_id
423 AND p_trx_date >= vt.start_date
424 AND p_trx_date <= nvl(vt.end_date,p_trx_date)
425 AND nvl(vt.enabled_flag,'Y') = 'Y'
426 AND nvl(vt.tax_class,'O') = 'O'
427 )
428 UNION
429 SELECT tg.tax_code,
430 NULL,
431 su.site_use_id
432 FROM hz_cust_site_uses su
433 ,ar_vat_tax vt
434 ,jl_zz_ar_tx_groups tg
435 ,ar_vat_tax vt1
436 WHERE su.site_use_id = p_ship_to_site_use_id
437 AND su.tax_code = vt.tax_code
438 AND vt.tax_type = 'TAX_GROUP'
439 AND tg.group_tax_id = vt.vat_tax_id
440 AND tg.tax_category_id = p_tax_category_id
441 AND tg.contributor_type = p_contributor_type
442 AND tg.transaction_nature = p_transaction_nature
443 AND tg.establishment_type = p_establishment_type
444 AND tg.tax_code = vt1.tax_code
445 AND p_trx_date >= vt1.start_date
446 AND p_trx_date <= nvl(vt1.end_date,p_trx_date)
447 AND nvl(vt1.enabled_flag,'Y') = 'Y'
448 AND nvl(vt1.tax_class,'O') = 'O'
449 AND p_trx_date <= tg.end_date_active
450 AND p_trx_date >= NVL(tg.start_date_active, p_trx_date);
451
452 -- Bugfix 1388703
453 CURSOR c_organization IS
454 SELECT hrl.global_attribute6 tax_code,
455 NULL,
456 p_ship_from_warehouse_id
457 FROM hr_locations_all hrl
458 WHERE hrl.location_id = p_warehouse_location_id
459 AND exists (select 1
460 from ar_vat_tax vt
461 WHERE hrl.global_attribute6 = vt.tax_code
462 AND vt.tax_type = 'VAT'
463 AND decode(ltrim(vt.global_attribute1,'0123456789'),
464 null, to_number(vt.global_attribute1),
465 null) = p_tax_category_id
466 AND p_trx_date >= vt.start_date
467 AND p_trx_date <= nvl(vt.end_date,p_trx_date)
468 AND nvl(vt.enabled_flag,'Y') = 'Y'
469 AND nvl(vt.tax_class,'O') = 'O'
470 )
471 UNION
472 SELECT tg.tax_code tax_code,
473 NULL,
474 p_ship_from_warehouse_id
475 FROM jl_zz_ar_tx_groups tg
476 WHERE tg.tax_category_id = p_tax_category_id
477 AND tg.contributor_type = p_contributor_type
478 AND tg.transaction_nature = p_transaction_nature
479 AND tg.establishment_type = p_establishment_type
480 AND p_trx_date <= tg.end_date_active
481 AND p_trx_date >= NVL(tg.start_date_active, p_trx_date)
482 AND exists (select 1
483 from ar_vat_tax vt1,
484 ar_vat_tax vt,
485 hr_locations_all hrl
486 WHERE hrl.location_id = p_warehouse_location_id
487 AND hrl.global_attribute6 = vt.tax_code
488 AND vt.tax_type = 'TAX_GROUP'
489 AND tg.group_tax_id = vt.vat_tax_id
490 AND tg.tax_code = vt1.tax_code
491 AND nvl(vt1.enabled_flag,'Y') = 'Y'
492 AND nvl(vt1.tax_class,'O') = 'O'
493 AND p_trx_date >= vt1.start_date
494 AND p_trx_date <= NVL(vt1.end_date, p_trx_date)
495 );
496
497 -- Bugfix 1388703
498 CURSOR c_sysparam IS
499 SELECT sp.tax_classification_code,
500 NULL,
501 NULL
502 FROM zx_product_options sp
503 ,ar_vat_tax vt
504 WHERE sp.application_id = 222
505 AND sp.org_id = vt.org_id
506 AND sp.tax_classification_code = vt.tax_code
507 AND vt.tax_type = 'VAT'
508 AND decode(ltrim(vt.global_attribute1, '0123456789'), null,
509 to_number(vt.global_attribute1), null) = p_tax_category_id
510 AND p_trx_date >= vt.start_date
511 AND p_trx_date <= nvl(vt.end_date,p_trx_date)
512 AND nvl(vt.enabled_flag,'Y') = 'Y'
513 AND nvl(vt.tax_class,'O') = 'O'
514 UNION
515 SELECT tg.tax_code,
516 NULL,
517 NULL
518 FROM jl_zz_ar_tx_groups tg
519 WHERE tg.tax_category_id = p_tax_category_id
520 AND tg.contributor_type = p_contributor_type
521 AND tg.transaction_nature = p_transaction_nature
522 AND tg.establishment_type = p_establishment_type
523 AND p_trx_date <= tg.end_date_active
524 AND p_trx_date >= NVL(tg.start_date_active, p_trx_date)
525 AND exists (select 1
526 from ar_vat_tax vt1
527 ,ar_vat_tax vt
528 ,zx_product_options sp
529 WHERE sp.tax_classification_code = vt.tax_code
530 AND sp.application_id = 222
531 AND sp.org_id = vt.org_id
532 AND vt.tax_type = 'TAX_GROUP'
533 AND tg.group_tax_id = vt.vat_tax_id
534 AND tg.tax_code = vt1.tax_code
535 AND nvl(vt1.enabled_flag,'Y') = 'Y'
536 AND nvl(vt1.tax_class,'O') = 'O'
537 AND p_trx_date >= vt1.start_date
538 AND p_trx_date <= NVL(vt1.end_date,p_trx_date)
539 );
540
541 -- Bugfix 1388703
542 CURSOR c_memo_line IS
543 SELECT ml.tax_code,
544 NULL,
545 ml.memo_line_id
546 FROM ar_memo_lines ml
547 WHERE ml.memo_line_id = p_memo_line_id
548 AND exists (select 1
549 from ar_vat_tax vt
550 WHERE ml.tax_code = vt.tax_code
551 AND vt.tax_type = 'VAT'
552 AND decode(ltrim(vt.global_attribute1,
553 '0123456789'), null,
554 to_number(vt.global_attribute1), null)
555 = p_tax_category_id
556 AND p_trx_date >= vt.start_date
557 AND p_trx_date <= nvl(vt.end_date,p_trx_date)
558 AND nvl(vt.enabled_flag,'Y') = 'Y'
559 AND nvl(vt.tax_class,'O') = 'O'
560 )
561 UNION
562 SELECT tg.tax_code,
563 NULL,
564 ml.memo_line_id
565 FROM ar_memo_lines ml
566 ,ar_vat_tax vt
567 ,jl_zz_ar_tx_groups tg
568 ,ar_vat_tax vt1
569 WHERE memo_line_id = p_memo_line_id
570 AND ml.tax_code = vt.tax_code
571 AND vt.tax_type = 'TAX_GROUP'
572 AND tg.group_tax_id = vt.vat_tax_id
573 AND tg.tax_category_id = p_tax_category_id
574 AND tg.contributor_type = p_contributor_type
575 AND tg.transaction_nature = p_transaction_nature
576 AND tg.establishment_type = p_establishment_type
577 AND tg.tax_code = vt1.tax_code
578 AND p_trx_date >= vt1.start_date
579 AND p_trx_date <= nvl(vt1.end_date,p_trx_date)
580 AND nvl(vt1.enabled_flag,'Y') = 'Y'
581 AND nvl(vt1.tax_class,'O') = 'O'
582 AND p_trx_date <= tg.end_date_active
583 AND p_trx_date >= NVL(tg.start_date_active, p_trx_date);
584
585 -- Bugfix 1388703
586 CURSOR c_tax_schedule IS
587 SELECT tax_code,
588 NULL,
589 NULL
590 FROM jl_zz_ar_tx_schedules
591 WHERE tax_category_id = p_tax_category_id
592 AND p_base_amount BETWEEN min_taxable_basis
593 AND max_taxable_basis
594 AND p_trx_date <= end_date_active
595 AND p_trx_date >= NVL(start_date_active,p_trx_date);
596
597 -- Bugfix 1388703
598 CURSOR c_fiscal_classif IS
599 SELECT fc.tax_code,
600 fc.base_rate,
601 fc.fsc_cls_id
602 FROM jl_zz_ar_tx_fsc_cls fc
603 WHERE fc.fiscal_classification_code = p_fiscal_classification_code
604 AND fc.tax_category_id = p_tax_category_id
605 AND fc.enabled_flag = 'Y'
606 AND p_trx_date <= fc.end_date_active
607 AND p_trx_date >= NVL(fc.start_date_active,p_trx_date);
608
609 -- Bugfix 1388703
610 -- Geography uptake
611 -- bug 10367703 performance changes to the query
612 CURSOR c_location (cp_location_id IN NUMBER
613 ,cp_tax_category_id IN NUMBER
614 ,cp_trx_date IN DATE
615 ,cp_site_use_id IN NUMBER
616 ) IS
617 SELECT /*+ leading (hrl) */
618 loc.tax_code,
619 loc.base_rate,
620 loc.locn_id
621 FROM jl_zz_ar_tx_locn loc
622 ,hr_locations_all hrl
623 WHERE hrl.location_id = cp_location_id
624 AND loc.ship_from_code = hrl.REGION_2
625 AND loc.tax_category_id = cp_tax_category_id
626 AND cp_trx_date <= loc.end_date_active
627 AND cp_trx_date >= NVL(loc.start_date_active, cp_trx_date)
628 AND exists
629 (select /*+ FIRST_ROWS(1) leading (su) */ 1
630 from hz_cust_site_uses su
631 ,hz_cust_acct_sites ad
632 ,hz_party_sites p
633 ,hz_locations lc
634 ,hz_geographies lv
635 ,ar_system_parameters sp
636 WHERE su.site_use_id = cp_site_use_id
637 AND ad.cust_acct_site_id = su.cust_acct_site_id
638 AND p.party_site_id = ad.party_site_id
639 AND lc.location_id = p.location_id
640 AND lv.geography_id = loc.ship_to_segment_id
641 AND lv.geography_type = sp.global_attribute9
642 AND UPPER(lv.geography_name) =
643 UPPER(decode(sp.global_attribute9,'STATE',lc.state,lc.province))
644 AND rownum = 1
645 );
646
647 -- geography related changes..
648 -- Bugfix 1388703
649 CURSOR c_exc_fiscal_classif IS
650 SELECT exc.tax_code,
651 exc.base_rate,
652 exc.exc_fsc_id
653 FROM jl_zz_ar_tx_exc_fsc exc
654 WHERE exc.fiscal_classification_code = p_fiscal_classification_code
655 AND exc.tax_category_id = p_tax_category_id
656 AND p_trx_date <= exc.end_date_active
657 AND p_trx_date >= NVL(exc.start_date_active, p_trx_date)
658 AND exists (select 1
659 from hz_geographies lv
660 ,hz_cust_acct_sites ad
661 ,hz_cust_site_uses su
662 ,hz_party_sites p
663 ,hz_locations loc
664 ,ar_system_parameters sp
665 ,hr_locations_all hrl
666 WHERE hrl.location_id = p_warehouse_location_id
667 AND exc.ship_from_code = hrl.REGION_2
668 AND exc.ship_to_segment_id = lv.geography_id
669 AND lv.geography_type = sp.global_attribute9
670 AND ad.party_site_id=p.party_site_id
671 AND p.location_id=loc.location_id
672 AND UPPER(lv.geography_name) = UPPER(decode(sp.global_attribute9,
673 'STATE', loc.state, loc.province))
674 AND su.cust_acct_site_id = ad.cust_acct_site_id
675 AND su.site_use_id = NVL(p_ship_to_site_use_id, p_bill_to_site_use_id));
676
677 -- geography changes
678 -- Bugfix 1388703
679 CURSOR c_exc_item IS
680 SELECT exi.tax_code,
681 exi.base_rate,
682 exi.exc_itm_id
683 FROM jl_zz_ar_tx_exc_itm exi
684 WHERE exi.inventory_item_id = p_inventory_item_id
685 AND exi.organization_id = p_ship_from_warehouse_id
686 AND exi.tax_category_id = p_tax_category_id
687 AND p_trx_date <= exi.end_date_active
688 AND p_trx_date >= NVL(exi.start_date_active, p_trx_date)
689 AND exists (select 1
690 from hz_geographies lv
691 ,hz_cust_acct_sites ad
692 ,hz_cust_site_uses su
693 ,hz_party_sites p
694 ,hz_locations loc
695 ,ar_system_parameters sp
696 ,hr_locations_all hrl
697 WHERE hrl.location_id = p_warehouse_location_id
698 AND exi.ship_from_code = hrl.REGION_2
699 AND exi.ship_to_segment_id = lv.geography_id
700 AND lv.geography_type = sp.global_attribute9
701 AND ad.party_site_id =p.party_site_id
702 AND p.location_id=loc.location_id
703 AND UPPER(lv.geography_name) = decode(sp.global_attribute9,
704 'STATE', loc.state,loc.province)
705 AND su.cust_acct_site_id = ad.cust_acct_site_id
706 AND su.site_use_id = NVL(p_ship_to_site_use_id,p_bill_to_site_use_id));
707
708 -- Bugfix 1388703
709 CURSOR c_exc_tax_group IS
710 SELECT gt.tax_code,
711 gt.base_rate,
712 gt.tax_group_record_id
713 FROM jl_zz_ar_tx_groups gt
714 WHERE gt.group_tax_id = p_group_tax_id
715 AND gt.tax_category_id = p_tax_category_id
716 AND gt.contributor_type = p_contributor_type
717 AND gt.transaction_nature = p_transaction_nature
718 AND gt.establishment_type = p_establishment_type
719 AND p_trx_date <= gt.end_date_active
720 AND p_trx_date >= NVL(gt.start_date_active, p_trx_date);
721
722 -- Bugfix 1388703
723 CURSOR c_exc_trx_nature IS
724 SELECT tnr.tax_code,
725 tnr.base_rate,
726 tnr.txn_nature_id
727 FROM jl_zz_ar_tx_nat_rat tnr,
728 jl_zz_ar_tx_att_val tcav,
729 jl_zz_ar_tx_categ tc,
730 jl_zz_ar_tx_cat_att tca,
731 jl_zz_ar_tx_att_cls tcac
732 WHERE tcac.tax_attr_class_code = p_transaction_nature_class
733 AND tcac.tax_category_id = p_tax_category_id
734 AND tcac.tax_attr_class_type = 'TRANSACTION_CLASS'
735 AND tcac.enabled_flag = 'Y'
736 AND tca.tax_attribute_type = 'TRANSACTION_ATTRIBUTE'
737 AND tca.tax_attribute_name = tcac.tax_attribute_name
738 AND tc.tax_category_id = tcac.tax_category_id
739 AND p_trx_date <= tc.end_date_active
740 AND p_trx_date >= NVL(tc.start_date_active,p_trx_date)
741 AND tcav.tax_category_id = tc.tax_category_id
742 AND tcav.tax_attribute_type = 'TRANSACTION_ATTRIBUTE'
743 AND tcav.tax_attribute_name = tcac.tax_attribute_name
744 AND tcav.tax_attribute_value = tcac.tax_attribute_value
745 AND tnr.tax_categ_attr_val_id = tcav.tax_categ_attr_val_id
746 AND p_trx_date <= tnr.end_date_active
747 AND p_trx_date >= NVL(tnr.start_date_active,p_trx_date)
748 ORDER BY tca.priority_number;
749
750 -- Bugfix 1388703
751 CURSOR c_cust_exc IS
752 SELECT tec.tax_code,
753 tec.base_rate,
754 tec.exc_cus_id
755 FROM jl_zz_ar_tx_exc_cus tec,
756 hz_cust_site_uses su
757 WHERE tec.tax_category_id = p_tax_category_id
758 AND p_trx_date <= tec.end_date_active
759 AND p_trx_date >= NVL(tec.start_date_active,p_trx_date)
760 AND su.site_use_id = NVL(p_ship_to_site_use_id,p_bill_to_site_use_id)
761 AND su.cust_acct_site_id = tec.address_id;
762
763
764 BEGIN
765
766 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
767
768 IF (g_level_statement >= g_current_runtime_level) then
769 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','Get_rule_info(+)');
770 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','p_rule = '||p_rule);
771 END IF;
772
773 o_base_rate := to_number(NULL);
774 o_tax_code := NULL;
775 o_rule_data_id := to_number(NULL);
776
777 IF p_rule = 'GET_TAX_CATEGORY_TX_CODE' THEN
778 OPEN c_category;
779 FETCH c_category INTO o_tax_code, o_base_rate, o_rule_data_id;
780 CLOSE c_category;
781
782 ELSIF p_rule = 'GET_ITEM_TX_CODE' THEN
783 OPEN c_item;
784 FETCH c_item INTO o_tax_code, o_base_rate, o_rule_data_id;
785 CLOSE c_item;
786
787 ELSIF p_rule = 'GET_CUSTOMER_TX_CODE' THEN
788 OPEN c_customer;
789 FETCH c_customer INTO o_tax_code, o_base_rate, o_rule_data_id;
790 CLOSE c_customer;
791
792 ELSIF p_rule = 'GET_BILL_TO_TX_CODE' THEN
793 OPEN c_bill_to;
794 FETCH c_bill_to INTO o_tax_code, o_base_rate, o_rule_data_id;
795 CLOSE c_bill_to;
796
797 ELSIF p_rule = 'GET_SHIP_TO_TX_CODE' THEN
798 OPEN c_ship_to;
799 FETCH c_ship_to INTO o_tax_code, o_base_rate, o_rule_data_id;
800 CLOSE c_ship_to;
801
802 ELSIF p_rule = 'GET_ORGANIZATION_TX_CODE' THEN
803 OPEN c_organization;
804 FETCH c_organization INTO o_tax_code, o_base_rate, o_rule_data_id;
805 CLOSE c_organization;
806
807 ELSIF p_rule = 'GET_SYS_OPTIONS_TX_CODE' THEN
808 OPEN c_sysparam;
809 FETCH c_sysparam INTO o_tax_code, o_base_rate, o_rule_data_id;
810 CLOSE c_sysparam;
811
812 ELSIF p_rule = 'GET_MEMO_LINE_TX_CODE' THEN
813 IF (p_application = 'AR') THEN
814 OPEN c_memo_line;
815 FETCH c_memo_line INTO o_tax_code, o_base_rate, o_rule_data_id;
816 CLOSE c_memo_line;
817 END IF;
818
819 ELSIF p_rule = 'GET_TX_SCH_TX_CODE' THEN
820 OPEN c_tax_schedule;
821 FETCH c_tax_schedule INTO o_tax_code, o_base_rate, o_rule_data_id;
822 CLOSE c_tax_schedule;
823
824 ELSIF p_rule = 'GET_FISC_CLAS_TX_CODE' THEN
825 OPEN c_fiscal_classif;
826 FETCH c_fiscal_classif INTO o_tax_code, o_base_rate, o_rule_data_id;
827 CLOSE c_fiscal_classif;
828
829 ELSIF p_rule = 'GET_LOCATION_TX_CODE' THEN
830 OPEN c_location (cp_location_id => p_warehouse_location_id
831 ,cp_tax_category_id => p_tax_category_id
832 ,cp_trx_date => p_trx_date
833 ,cp_site_use_id => NVL(p_ship_to_site_use_id, p_bill_to_site_use_id)
834 );
835 FETCH c_location INTO o_tax_code, o_base_rate, o_rule_data_id;
836 CLOSE c_location;
837
838 ELSIF p_rule = 'GET_EXC_FISC_CLAS_TX_CODE' THEN
839 OPEN c_exc_fiscal_classif;
840 FETCH c_exc_fiscal_classif INTO o_tax_code, o_base_rate, o_rule_data_id;
841 CLOSE c_exc_fiscal_classif;
842
843 ELSIF p_rule = 'GET_EXC_ITEM_TX_CODE' THEN
844 OPEN c_exc_item;
845 FETCH c_exc_item INTO o_tax_code, o_base_rate, o_rule_data_id;
846 CLOSE c_exc_item;
847
848 ELSIF p_rule = 'GET_LATIN_TX_GRP_TX_CODE' THEN
849 OPEN c_exc_tax_group;
850 FETCH c_exc_tax_group INTO o_tax_code, o_base_rate, o_rule_data_id;
851 CLOSE c_exc_tax_group;
852
853 ELSIF p_rule = 'GET_TRX_NATURE_TX_CODE' THEN
854 OPEN c_exc_trx_nature;
855 FETCH c_exc_trx_nature INTO o_tax_code, o_base_rate, o_rule_data_id;
856 CLOSE c_exc_trx_nature ;
857
858 ELSIF p_rule = 'GET_CUST_EXC_TX_CODE' THEN
859 OPEN c_cust_exc;
860 FETCH c_cust_exc INTO o_tax_code, o_base_rate, o_rule_data_id;
861 CLOSE c_cust_exc;
862
863 END IF;
864
865 IF (g_level_statement >= g_current_runtime_level) then
866 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','o_tax_code = '||o_tax_code);
867 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','o_base_rate = '||o_base_rate);
868 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','o_rule_data_id = '||o_rule_data_id);
869 END IF;
870
871 EXCEPTION
872 WHEN TOO_MANY_ROWS THEN
873 fnd_message.set_name('AR', 'GENERIC_MESSAGE');
874 fnd_message.set_token('GENERIC_TEXT',
875 'EXCEPTION(TOO_MANY_ROWS): jl_zz_tax.get_rule_info');
876 IF (g_level_unexpected >= g_current_runtime_level) THEN
877 FND_LOG.STRING(g_level_unexpected,'ZX.PLSQL.JL_ZZ_TAX','EXCEPTION(TOO_MANY_ROWS): jl_zz_tax.get_rule_info');
878 END IF;
879 END get_rule_info;
880
881 --========================================================================
882 -- PUBLIC FUNCTION
883 -- get_category_tax_code
884 --
885 -- DESCRIPTION
886 -- This routine searches for a tax code according to the tax rate rules
887 -- for the level = 'RATE'.
888 -- The tax code is searched following the priorities defined in the
889 -- rules form for the specific tax category + contributor type +
890 -- transaction type. If there is no specific tax category + contributor
891 -- type + transaction type combination, the routine considers contributor
892 -- type = 'DEFAULT' and transaction type = the one defined in system options
893 -- Each rule specifies a search for the tax code in a determined table.
894 -- For the new tables each row has a tax code associated to a tax
895 -- category. For the core product tables, the tax code can be TAX_TYPE =
896 -- 'TAX_GROUP' or 'VAT'. If the tax type is 'TAX_GROUP' the function
897 -- returns the tax code associated to the tax category passed to the
898 -- function. If the tax category is not defined in this tax group, the
899 -- function continues to search for the tax code in the other rules. If
900 -- the tax type is 'VAT' the tax code is considered to be associated to
901 -- the tax category of the tax code.
902 --
903 -- PARAMETERS
904 -- The argument p_warehouse_id is for future purposes
905 --
906 -- RETURNS
907 -- tax_code
908 --
909 -- CALLED FROM
910 -- latin tax views
911 --
912 -- HISTORY
913 --========================================================================
914
915 FUNCTION get_category_tax_code (
916 p_tax_category_id IN NUMBER,
917 p_cust_trx_type_id IN NUMBER,
918 p_ship_to_site_use_id IN NUMBER,
919 p_bill_to_site_use_id IN NUMBER,
920 p_inventory_item_id IN NUMBER,
921 p_group_tax_id IN NUMBER,
922 p_memo_line_id IN NUMBER,
923 p_ship_to_customer_id IN NUMBER,
924 p_bill_to_customer_id IN NUMBER,
925 p_trx_date IN DATE,
926 p_application IN VARCHAR2,
927 p_warehouse_id IN NUMBER,
928 p_level IN VARCHAR2,
929 p_fiscal_classification_code IN VARCHAR2,
930 p_inventory_organization_id IN NUMBER,
931 p_location_structure_id IN NUMBER,
932 p_location_segment_num IN NUMBER,
933 p_set_of_books_id IN NUMBER,
934 p_transaction_nature IN VARCHAR2,
935 p_base_amount IN NUMBER,
936 p_establishment_type IN VARCHAR2,
937 p_contributor_type IN VARCHAR2,
938 p_warehouse_location_id IN NUMBER,
939 p_transaction_nature_class IN VARCHAR2
940 ) return VARCHAR2 IS
941
942 v_tax_code VARCHAR2(50) := NULL;
943
944 -- Bugfix 1388703
945 CURSOR c_rule IS
946 SELECT tr.rule,
947 tr.rule_id rule_id
948 FROM jl_zz_ar_tx_rules tr
949 WHERE tr.tax_rule_level = p_level
950 AND tr.tax_category_id = p_tax_category_id
951 AND nvl(tr.contributor_type,'~') = p_contributor_type
952 AND tr.cust_trx_type_id = p_cust_trx_type_id
953 ORDER BY tr.priority,
954 tr.rule;
955
956 c_rule_rec c_rule%ROWTYPE;
957
958 -- Bugfix 1388703
959 CURSOR c_rule_default IS
960 SELECT tr.rule rule,
961 tr.rule_id rule_id
962 FROM jl_zz_ar_tx_rules tr,
963 ar_system_parameters sp
964 WHERE tr.tax_rule_level = p_level
965 AND tr.tax_category_id = p_tax_category_id
966 AND tr.contributor_type = 'DEFAULT'
967 AND tr.cust_trx_type_id =
968 decode(ltrim(sp.global_attribute15, '0123456789'),
969 null, to_number(sp.global_attribute15), null)
970 ORDER BY tr.priority,
971 tr.rule;
972
973 c_rule_default_rec c_rule_default%ROWTYPE;
974
975 l_base_rate NUMBER;
976 l_rule_data_id NUMBER;
977
978
979 BEGIN
980 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
981
982 IF (g_level_statement >= g_current_runtime_level) THEN
983 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','jl_zz_tx.get_category_tax_code()+');
984 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_category_tax_code: ' || '-P- Tax Category: '||to_char(p_tax_category_id));
985 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_category_tax_code: ' || '-P- Transaction Type: '||to_char(p_cust_trx_type_id));
986 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_category_tax_code: ' || '-P- Ship to Site Use: '||to_char(p_ship_to_site_use_id));
987 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_category_tax_code: ' || '-P- Bill to Site Use: '||to_char(p_bill_to_site_use_id));
988 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_category_tax_code: ' || '-P- Inventory Item id: '||to_char(p_inventory_item_id));
989 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_category_tax_code: ' || '-P- Tax Group Id: '||to_char(p_group_tax_id));
990 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_category_tax_code: ' || '-P- Memo Line Id: '||to_char(p_memo_line_id));
991 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_category_tax_code: ' || '-P- Ship To Customer Id: '||to_char(p_ship_to_customer_id));
992 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_category_tax_code: ' || '-P- Bill To Customer Id: '||to_char(p_bill_to_customer_id));
993 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','-P- Transaction Date: '||to_char(p_trx_date,'DD-MM-YYYY'));
994 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_category_tax_code: ' || '-P- Application: '||p_application);
995 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_category_tax_code: ' || '-P- Warehouse Id: '||to_char(p_warehouse_id));
996 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_category_tax_code: ' || '-P- Rule Level: '||p_level);
997 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_category_tax_code: ' || '-P- Fiscal Classification Code: '||p_fiscal_classification_code);
998 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_category_tax_code: ' || '-P- Inventory Organization Id: '||to_char(p_inventory_organization_id));
999 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_category_tax_code: ' || '-P- Location Structure Id: '||to_char(p_location_structure_id));
1000 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_category_tax_code: ' || '-P- Location Segment Number: '||to_char(p_location_segment_num));
1001 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_category_tax_code: ' || '-P- Set Of books Id: '||to_char(p_set_of_books_id));
1002 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_category_tax_code: ' || '-P- Transaction Nature: '||p_transaction_nature);
1003 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_category_tax_code: ' || '-P- Base Amount: '||to_char(p_base_amount));
1004 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_category_tax_code: ' || '-P- Establishment Type: '||p_establishment_type);
1005 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_category_tax_code: ' || '-P- Contributor Type: '||p_contributor_type);
1006 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_category_tax_code: ' || '-P- Warehouse Location Id: '||to_char(p_warehouse_location_id));
1007 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_category_tax_code: ' || '-P- Transaction Nature Class: '||p_transaction_nature_class);
1008 END IF;
1009
1010 -- search tax rule to be applied
1011 FOR c_rule_rec in c_rule LOOP
1012
1013 l_base_rate := to_number(NULL);
1014 v_tax_code := NULL;
1015 l_rule_data_id := to_number(NULL);
1016
1017 IF (g_level_statement >= g_current_runtime_level) THEN
1018 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_category_tax_code: ' || '-- Processing Rule: '||c_rule_rec.rule);
1019 END IF;
1020
1021 get_rule_info(
1022 c_rule_rec.rule,
1023 p_fiscal_classification_code,
1024 p_tax_category_id,
1025 p_trx_date,
1026 p_ship_to_site_use_id,
1027 p_bill_to_site_use_id,
1028 p_inventory_item_id,
1029 p_warehouse_id,
1030 p_group_tax_id,
1031 p_contributor_type,
1032 p_transaction_nature,
1033 p_establishment_type,
1034 p_transaction_nature_class,
1035 p_inventory_organization_id,
1036 p_ship_to_customer_id,
1037 p_bill_to_customer_id,
1038 p_warehouse_location_id,
1039 p_memo_line_id,
1040 p_base_amount,
1041 p_application,
1042 v_tax_code,
1043 l_base_rate,
1044 l_rule_data_id);
1045
1046 IF (g_level_statement >= g_current_runtime_level) THEN
1047 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_category_tax_code: ' || '-- Tax Code: '||v_tax_code);
1048 END IF;
1049
1050 IF v_tax_code IS NOT NULL
1051 THEN
1052 EXIT;
1053 END IF;
1054 END LOOP;
1055
1056 IF v_tax_code IS NULL
1057 THEN
1058 FOR c_rule_default_rec in c_rule_default LOOP
1059
1060 IF (g_level_statement >= g_current_runtime_level) THEN
1061 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_category_tax_code: ' || '-- Procesing Default Tax Rule: '||c_rule_default_rec.rule);
1062 END IF;
1063
1064 l_base_rate := to_number(NULL);
1065 v_tax_code := NULL;
1066 l_rule_data_id := to_number(NULL);
1067
1068 get_rule_info(
1069 c_rule_default_rec.rule,
1070 p_fiscal_classification_code,
1071 p_tax_category_id,
1072 p_trx_date,
1073 p_ship_to_site_use_id,
1074 p_bill_to_site_use_id,
1075 p_inventory_item_id,
1076 p_warehouse_id,
1077 p_group_tax_id,
1078 p_contributor_type,
1079 p_transaction_nature,
1080 p_establishment_type,
1081 p_transaction_nature_class,
1082 p_inventory_organization_id,
1083 p_ship_to_customer_id,
1084 p_bill_to_customer_id,
1085 p_warehouse_location_id,
1086 p_memo_line_id,
1087 p_base_amount,
1088 p_application,
1089 v_tax_code,
1090 l_base_rate,
1091 l_rule_data_id);
1092
1093 IF (g_level_statement >= g_current_runtime_level) THEN
1094 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_category_tax_code: ' || '-- Tax Code: '||v_tax_code);
1095 END IF;
1096
1097 IF v_tax_code IS NOT NULL
1098 THEN
1099 EXIT;
1100 END IF;
1101 END LOOP;
1102 END IF;
1103
1104 IF v_tax_code IS NULL
1105 THEN
1106 -- as the function cannot raise an this message is passed to the
1107 -- user as a 'not valid tax code' within tax engine
1108 v_tax_code := 'NO_VALID_TAX_CODE';
1109 END IF;
1110
1111 IF (g_level_statement >= g_current_runtime_level) THEN
1112 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','jl_zz_tax.get_category_tax_code()-');
1113 END IF;
1114
1115 RETURN (v_tax_code);
1116
1117 END get_category_tax_code;
1118
1119 --========================================================================
1120 -- PUBLIC FUNCTION
1121 -- get_category_tax_rule
1122 --
1123 -- DESCRIPTION
1124 -- Function returns the rule_id to be used to get the Legal Message
1125 -- The rule_id is related to the rule that originated the tax code for
1126 -- the transaction line
1127 --
1128 -- RETURNS
1129 -- rule_id
1130 --
1131 -- CALLED FROM
1132 -- jl_zz_tax.calculate
1133 --
1134 -- HISTORY
1135 --========================================================================
1136
1137 PROCEDURE get_category_tax_rule (
1138 p_tax_category_id IN NUMBER,
1139 p_cust_trx_type_id IN NUMBER,
1140 p_ship_to_site_use_id IN NUMBER,
1141 p_bill_to_site_use_id IN NUMBER,
1142 p_inventory_item_id IN NUMBER,
1143 p_group_tax_id IN NUMBER,
1144 p_memo_line_id IN NUMBER,
1145 p_ship_to_customer_id IN NUMBER,
1146 p_bill_to_customer_id IN NUMBER,
1147 p_trx_date IN DATE,
1148 p_application IN VARCHAR2,
1149 p_warehouse_id IN NUMBER,
1150 p_level IN VARCHAR2,
1151 p_fiscal_classification_code IN VARCHAR2,
1152 p_inventory_organization_id IN NUMBER,
1153 p_location_structure_id IN NUMBER,
1154 p_location_segment_num IN NUMBER,
1155 p_set_of_books_id IN NUMBER,
1156 p_transaction_nature IN VARCHAR2,
1157 p_base_amount IN NUMBER,
1158 p_establishment_type IN VARCHAR2,
1159 p_contributor_type IN VARCHAR2,
1160 p_warehouse_location_id IN NUMBER,
1161 p_transaction_nature_class IN VARCHAR2,
1162 o_tax_code IN OUT NOCOPY VARCHAR2,
1163 o_base_rate IN OUT NOCOPY NUMBER,
1164 o_rule_data_id IN OUT NOCOPY NUMBER,
1165 o_rule_id IN OUT NOCOPY NUMBER
1166 ) IS
1167
1168 v_tax_code VARCHAR2(50) := NULL;
1169
1170 -- Bugfix 1388703
1171 CURSOR c_rule IS
1172 SELECT tr.rule,
1173 tr.rule_id rule_id
1174 FROM jl_zz_ar_tx_rules tr
1175 WHERE tr.tax_rule_level = p_level
1176 AND tr.tax_category_id = p_tax_category_id
1177 AND nvl(tr.contributor_type,'~') = p_contributor_type
1178 AND tr.cust_trx_type_id = p_cust_trx_type_id
1179 ORDER BY tr.priority,
1180 tr.rule;
1181
1182 c_rule_rec c_rule%ROWTYPE;
1183
1184 -- Bugfix 1388703
1185 CURSOR c_rule_default IS
1186 SELECT tr.rule rule,
1187 tr.rule_id rule_id
1188 FROM jl_zz_ar_tx_rules tr,
1189 ar_system_parameters sp
1190 WHERE tr.tax_rule_level = p_level
1191 AND tr.tax_category_id = p_tax_category_id
1192 AND tr.contributor_type = 'DEFAULT'
1193 AND tr.cust_trx_type_id =
1194 decode(ltrim(sp.global_attribute15, '0123456789'), null,
1195 to_number(sp.global_attribute15), null)
1196 ORDER BY tr.priority,
1197 tr.rule;
1198
1199 c_rule_default_rec c_rule_default%ROWTYPE;
1200
1201 l_base_rate NUMBER;
1202 l_rule_data_id NUMBER;
1203 l_rule_id NUMBER;
1204
1205
1206 BEGIN
1207 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1208
1209 IF (g_level_statement >= g_current_runtime_level) THEN
1210 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_category_tax_rule()+');
1211 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_category_tax_rule: ' || '-P- Tax category passed: '||to_char(p_tax_category_id));
1212 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_category_tax_rule: ' || '-P- Transaction Type: '||to_char(p_cust_trx_type_id));
1213 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_category_tax_rule: ' || '-P- Ship to Site: '||to_char(p_ship_to_site_use_id));
1214 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_category_tax_rule: ' || '-P- Bill to site: '||to_char(p_bill_to_site_use_id) );
1215 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_category_tax_rule: ' || '-P- Inventory Item Id: '||to_char(p_inventory_item_id));
1216 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_category_tax_rule: ' || '-P- Tax group passed: '||to_char(p_group_tax_id));
1217 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_category_tax_rule: ' || '-P- Memo Line Id: '||to_char(p_memo_line_id));
1218 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_category_tax_rule: ' || '-P- Ship To Cust Id: '||to_char(p_ship_to_customer_id));
1219 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_category_tax_rule: ' || '-P- Bill To Cust Id: '||to_char(p_bill_to_customer_id));
1220 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','-P- Trx date: ' ||to_char(p_trx_date,'DD-MM-YYYY'));
1221 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_category_tax_rule: ' || '-P- Application: '||p_application);
1222 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_category_tax_rule: ' || '-P- Warehouse Id: '|| to_char(p_warehouse_id));
1223 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_category_tax_rule: ' || '-P- Rule Level: '||p_level);
1224 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_category_tax_rule: ' || '-P- Fiscal Classification Code: '||
1225 p_fiscal_classification_code);
1226 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_category_tax_rule: ' || '-P- Inventory Organization Id: '||
1227 to_char(p_inventory_organization_id));
1228 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_category_tax_rule: ' || '-P- Location Structure Id: '||
1229 to_char(p_location_structure_id));
1230 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_category_tax_rule: ' || '-P- Location Segment Number: '||p_location_segment_num);
1231 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_category_tax_rule: ' || '-P- Set Of Books Id: '||to_char(p_set_of_books_id));
1232 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_category_tax_rule: ' || '-P- Trx Nature: '|| p_transaction_nature);
1233 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_category_tax_rule: ' || '-P- Tax base amount passed: '||to_char(p_base_amount));
1234 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_category_tax_rule: ' || '-P- Establishment Type: '|| p_establishment_type);
1235 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_category_tax_rule: ' || '-P- Contributor Type: '|| p_contributor_type);
1236 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_category_tax_rule: ' || '-P- Warehouse Location Id: '||
1237 to_char(p_warehouse_location_id));
1238 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_category_tax_rule: ' || '-P- Trx Nature Class: '||p_transaction_nature_class);
1239 END IF;
1240
1241 -- search tax rule to be applied
1242 l_rule_id := to_number(NULL);
1243
1244 FOR c_rule_rec in c_rule LOOP
1245
1246 l_base_rate := to_number(NULL);
1247 v_tax_code := NULL;
1248 l_rule_data_id := to_number(NULL);
1249
1250 get_rule_info(
1251 c_rule_rec.rule,
1252 p_fiscal_classification_code,
1253 p_tax_category_id,
1254 p_trx_date,
1255 p_ship_to_site_use_id,
1256 p_bill_to_site_use_id,
1257 p_inventory_item_id,
1258 p_warehouse_id,
1259 p_group_tax_id,
1260 p_contributor_type,
1261 p_transaction_nature,
1262 p_establishment_type,
1263 p_transaction_nature_class,
1264 p_inventory_organization_id,
1265 p_ship_to_customer_id,
1266 p_bill_to_customer_id,
1267 p_warehouse_location_id,
1268 p_memo_line_id,
1269 p_base_amount,
1270 p_application,
1271 v_tax_code,
1272 l_base_rate,
1273 l_rule_data_id);
1274
1275 IF v_tax_code IS NOT NULL
1276 THEN
1277 IF (g_level_statement >= g_current_runtime_level) THEN
1278 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','-- Return from get_category_tax_rule: '||
1279 to_char(c_rule_rec.rule_id));
1280 END IF;
1281 l_rule_id := c_rule_rec.rule_id;
1282 EXIT;
1283 END IF;
1284 END LOOP;
1285
1286 IF v_tax_code IS NULL
1287 THEN
1288 FOR c_rule_default_rec in c_rule_default LOOP
1289
1290 l_base_rate := to_number(NULL);
1291 v_tax_code := NULL;
1292 l_rule_data_id := to_number(NULL);
1293
1294 get_rule_info(
1295 c_rule_default_rec.rule,
1296 p_fiscal_classification_code,
1297 p_tax_category_id,
1298 p_trx_date,
1299 p_ship_to_site_use_id,
1300 p_bill_to_site_use_id,
1301 p_inventory_item_id,
1302 p_warehouse_id,
1303 p_group_tax_id,
1304 p_contributor_type,
1305 p_transaction_nature,
1306 p_establishment_type,
1307 p_transaction_nature_class,
1308 p_inventory_organization_id,
1309 p_ship_to_customer_id,
1310 p_bill_to_customer_id,
1311 p_warehouse_location_id,
1312 p_memo_line_id,
1313 p_base_amount,
1314 p_application,
1315 v_tax_code,
1316 l_base_rate,
1317 l_rule_data_id);
1318
1319 IF v_tax_code IS NOT NULL
1320 THEN
1321 IF (g_level_statement >= g_current_runtime_level) THEN
1322 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','-- Return from get_category_tax_rule: '||
1323 to_char(c_rule_default_rec.rule_id));
1324 END IF;
1325 l_rule_id := c_rule_default_rec.rule_id;
1326 EXIT;
1327 END IF;
1328 END LOOP;
1329 END IF;
1330
1331 o_rule_id := l_rule_id;
1332 o_tax_code := v_tax_code;
1333 o_base_rate := l_base_rate;
1334 o_rule_data_id := l_rule_data_id;
1335
1336 IF (g_level_statement >= g_current_runtime_level) THEN
1337 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_category_tax_rule: ' || '-O- Rule Id: '|| to_char(o_rule_id));
1338 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_category_tax_rule: ' || '-O- Tax Code: '|| o_tax_code);
1339 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_category_tax_rule: ' || '-O- Base Rate: '|| to_char(o_base_rate));
1340 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_category_tax_rule: ' || '-O- Rule Data Id: '|| to_char(o_rule_data_id));
1341 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_category_tax_rule()-');
1342 END IF;
1343
1344 EXCEPTION
1345 WHEN NO_DATA_FOUND THEN
1346 fnd_message.set_name('AR', 'GENERIC_MESSAGE');
1347 fnd_message.set_token('GENERIC_TEXT',
1348 'No valid tax code found: jl_zz_tax.get_category_tax_rule');
1349 IF (g_level_unexpected >= g_current_runtime_level) THEN
1350 FND_LOG.STRING(g_level_unexpected,'ZX.PLSQL.JL_ZZ_TAX','EXCEPTION(NO_DATA_FOUND): jl_zz_tax.get_category_tax_rule');
1351 END IF;
1352 ZX_TDS_CALC_SERVICES_PUB_PKG.g_msg_context_info_rec.trx_line_id :=
1353 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.customer_trx_line_id;
1354
1355 ZX_TDS_CALC_SERVICES_PUB_PKG.g_msg_context_info_rec.trx_level_type :=
1356 'LINE';
1357
1358 ZX_API_PUB.add_msg(
1359 ZX_TDS_CALC_SERVICES_PUB_PKG.g_msg_context_info_rec);
1360
1361
1362 END get_category_tax_rule;
1363
1364
1365 --========================================================================
1366 -- This function is a wrapper for the procedure get_rule_info. The
1367 -- function is called from the Tax Engine views. The function gets the
1368 -- base rate tax code to modify the extended amount. For Brazilian
1369 -- Taxes you can define a kind of exemption (to increase or decrease
1370 -- the tax base amount) to be applied to the base amount before you
1371 -- apply the tax rate. To increase the base rate you should define a
1372 -- positive value to the tax code and to decrease the base rate you
1373 -- should define a negative value to the tax code. The base rate tax
1374 -- code is defined by the tax rules priorities. Level (Base). If there
1375 -- is not a base rate the function returns NULL and the calculation
1376 -- procedure uses the normal invoice line or sales order line amounts.
1377 -- 19-SEP-98 Harsh Takle Added following parameters
1378 -- p_establishment_type
1379 -- p_contributor_type
1380 -- p_warehouse_location_id
1381 -- p_transaction_nature_class
1382 --========================================================================
1383
1384 FUNCTION get_tax_base_rate (
1385 p_tax_category_id IN NUMBER,
1386 p_cust_trx_type_id IN NUMBER,
1387 p_ship_to_site_use_id IN NUMBER,
1388 p_bill_to_site_use_id IN NUMBER,
1389 p_inventory_item_id IN NUMBER,
1390 p_group_tax_id IN NUMBER,
1391 p_memo_line_id IN NUMBER,
1392 p_ship_to_customer_id IN NUMBER,
1393 p_bill_to_customer_id IN NUMBER,
1394 p_trx_date IN DATE,
1395 p_application IN VARCHAR2,
1396 p_warehouse_id IN NUMBER,
1397 p_level IN VARCHAR2,
1398 p_fiscal_classification_code IN VARCHAR2,
1399 p_inventory_organization_id IN NUMBER,
1400 p_location_structure_id IN NUMBER,
1401 p_location_segment_num IN NUMBER,
1402 p_transaction_nature IN VARCHAR2,
1403 p_establishment_type IN VARCHAR2,
1404 p_contributor_type IN VARCHAR2,
1405 p_warehouse_location_id IN NUMBER,
1406 p_transaction_nature_class IN VARCHAR2
1407 ) return NUMBER IS
1408
1409 v_base_rate NUMBER := NULL;
1410
1411 -- Bugfix 1388703
1412 CURSOR c_rule IS
1413 SELECT tr.rule,
1414 tr.rule_id rule_id
1415 FROM jl_zz_ar_tx_rules tr
1416 WHERE tr.tax_rule_level = p_level
1417 AND tr.tax_category_id = p_tax_category_id
1418 AND NVL(tr.contributor_type,'~') = p_contributor_type
1419 AND tr.cust_trx_type_id = p_cust_trx_type_id
1420 ORDER BY tr.priority,
1421 tr.rule;
1422
1423 c_rule_rec c_rule%ROWTYPE;
1424
1425 -- Bugfix 1388703
1426 CURSOR c_rule_default IS
1427 SELECT tr.rule rule,
1428 tr.rule_id rule_id
1429 FROM jl_zz_ar_tx_rules tr,
1430 ar_system_parameters sp
1431 WHERE tr.tax_rule_level = p_level
1432 AND tr.tax_category_id = p_tax_category_id
1433 AND tr.contributor_type = 'DEFAULT'
1434 AND tr.cust_trx_type_id =
1435 decode(ltrim(sp.global_attribute15, '0123456789'),
1436 null, to_number(sp.global_attribute15), null)
1437 ORDER BY tr.priority,
1438 tr.rule;
1439
1440 c_rule_default_rec c_rule_default%ROWTYPE;
1441
1442 l_tax_code ar_vat_tax.tax_code%type;
1443 l_rule_data_id NUMBER;
1444
1445
1446 BEGIN
1447 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1448
1449 -- search tax rule to be applied
1450 FOR c_rule_rec in c_rule LOOP
1451
1452 v_base_rate := to_number(NULL);
1453 l_tax_code := NULL;
1454 l_rule_data_id := to_number(NULL);
1455
1456 get_rule_info(
1457 c_rule_rec.rule,
1458 p_fiscal_classification_code,
1459 p_tax_category_id,
1460 p_trx_date,
1461 p_ship_to_site_use_id,
1462 p_bill_to_site_use_id,
1463 p_inventory_item_id,
1464 p_warehouse_id,
1465 p_group_tax_id,
1466 p_contributor_type,
1467 p_transaction_nature,
1468 p_establishment_type,
1469 p_transaction_nature_class,
1470 p_inventory_organization_id,
1471 p_ship_to_customer_id,
1472 p_bill_to_customer_id,
1473 p_warehouse_location_id,
1474 p_memo_line_id,
1475 0,
1476 p_application,
1477 l_tax_code,
1478 v_base_rate,
1479 l_rule_data_id);
1480
1481 IF v_base_rate IS NOT NULL
1482 THEN
1483 EXIT;
1484 END IF;
1485 END LOOP;
1486
1487 IF v_base_rate IS NULL
1488 THEN
1489 FOR c_rule_default_rec in c_rule_default LOOP
1490
1491 get_rule_info(
1492 c_rule_default_rec.rule,
1493 p_fiscal_classification_code,
1494 p_tax_category_id,
1495 p_trx_date,
1496 p_ship_to_site_use_id,
1497 p_bill_to_site_use_id,
1498 p_inventory_item_id,
1499 p_warehouse_id,
1500 p_group_tax_id,
1501 p_contributor_type,
1502 p_transaction_nature,
1503 p_establishment_type,
1504 p_transaction_nature_class,
1505 p_inventory_organization_id,
1506 p_ship_to_customer_id,
1507 p_bill_to_customer_id,
1508 p_warehouse_location_id,
1509 p_memo_line_id,
1510 0,
1511 p_application,
1512 l_tax_code,
1513 v_base_rate,
1514 l_rule_data_id);
1515
1516 IF v_base_rate IS NOT NULL
1517 THEN
1518 EXIT;
1519 END IF;
1520 END LOOP;
1521 END IF;
1522
1523 RETURN(v_base_rate);
1524
1525 END get_tax_base_rate;
1526
1527 -- Following procedure is created on 19-SEP-98 by Harsh Takle
1528 -- Procedure will return Applicable prior base and charged tax amount
1529 -- for current tax line
1530 -- If grouping attribute is 'DOCUMENT' then it will sum up all previous
1531 -- tax lines base amount and tax amount from PL/SQL table for current tax
1532 -- category, irrespective of their grouping attribute value, otherwise it will
1533 -- return base amount and charged tax amount for current tax category and
1534 -- current grouping attribute value
1535 -- This procedure is called from get_prior_base procedure
1536
1537 PROCEDURE get_prior_base_curr_doc (p_tax_category_id IN NUMBER,
1538 p_grp_attr_name IN VARCHAR2,
1539 p_grp_attr_value IN VARCHAR2,
1540 p_appl_prior_base IN OUT NOCOPY NUMBER,
1541 p_charged_tax_amount IN OUT NOCOPY NUMBER,
1542 p_calculated_tax_amount IN OUT NOCOPY NUMBER) IS
1543
1544 l_counter NUMBER;
1545
1546
1547 BEGIN
1548 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1549
1550 IF (g_level_statement >= g_current_runtime_level) THEN
1551 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','jl_zz_tax.get_prior_base_curr_doc()+');
1552 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_prior_base_curr_doc: ' || '-P- Tax category: '||to_char(p_tax_category_id));
1553 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_prior_base_curr_doc: ' || '-P- Grouping Attribute Name: '||p_grp_attr_name);
1554 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_prior_base_curr_doc: ' || '-P- Grouping Attribute Value: ' ||p_grp_attr_value);
1555 END IF;
1556
1557 p_appl_prior_base := 0;
1558 p_charged_tax_amount := 0;
1559 p_calculated_tax_amount := 0;
1560 l_counter := g_rel_tax_line_amounts.COUNT;
1561
1562 IF (g_level_statement >= g_current_runtime_level) THEN
1563 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_prior_base_curr_doc: ' || '-- Rel tax Amounts PLSQL Table counter: '|| to_char(l_counter));
1564 END IF;
1565
1566 IF p_grp_attr_name = 'DOCUMENT' THEN
1567 LOOP
1568 IF l_counter = 0 THEN
1569 exit;
1570 END IF;
1571 IF p_tax_category_id = g_rel_tax_line_amounts(l_counter).TaxCateg THEN
1572 p_appl_prior_base := p_appl_prior_base +
1573 g_rel_tax_line_amounts(l_counter).ApplPriorBase;
1574 p_charged_tax_amount := p_charged_tax_amount +
1575 g_rel_tax_line_amounts(l_counter).ChargedTax;
1576 p_calculated_tax_amount := p_calculated_tax_amount +
1577 g_rel_tax_line_amounts(l_counter).CalcltdTax;
1578 END IF;
1579
1580 IF (g_level_statement >= g_current_runtime_level) THEN
1581 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_prior_base_curr_doc: ' || '-- Rel Tax category: '||
1582 to_char(g_rel_tax_line_amounts(l_counter).TaxCateg));
1583 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_prior_base_curr_doc: ' || '-- Applicable Prior Base: '||to_char(p_appl_prior_base));
1584 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_prior_base_curr_doc: ' || '-- Charged tax Amount: '||to_char(p_charged_tax_amount));
1585 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_prior_base_curr_doc: ' || '-- Calculated tax Amount: '||
1586 to_char(p_calculated_tax_amount));
1587 END IF;
1588 l_counter := l_counter - 1;
1589 END LOOP;
1590 ELSE
1591 LOOP
1592 IF l_counter = 0 THEN
1593 EXIT;
1594 END IF;
1595 IF p_tax_category_id = g_rel_tax_line_amounts(l_counter).TaxCateg AND
1596 p_grp_attr_name = g_rel_tax_line_amounts(l_counter).GrpAttrname AND
1597 p_grp_attr_value = g_rel_tax_line_amounts(l_counter).GrpAttrvalue THEN
1598 p_appl_prior_base := p_appl_prior_base +
1599 g_rel_tax_line_amounts(l_counter).ApplPriorBase;
1600 p_charged_tax_amount := p_charged_tax_amount +
1601 g_rel_tax_line_amounts(l_counter).ChargedTax;
1602 p_calculated_tax_amount := p_calculated_tax_amount +
1603 g_rel_tax_line_amounts(l_counter).CalcltdTax;
1604 IF (g_level_statement >= g_current_runtime_level) THEN
1605 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_prior_base_curr_doc: ' || '-- Applicable Prior Base: '||
1606 to_char(p_appl_prior_base));
1607 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_prior_base_curr_doc: ' || '-- Charged tax Amount: ' ||
1608 to_char(p_charged_tax_amount));
1609 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_prior_base_curr_doc: ' || '-- Calculated tax Amount: ' ||
1610 to_char(p_calculated_tax_amount));
1611 END IF;
1612 END IF;
1613
1614 IF (g_level_statement >= g_current_runtime_level) THEN
1615 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_prior_base_curr_doc: ' || '-- Rel Tax category: '||
1616 to_char(g_rel_tax_line_amounts(l_counter).TaxCateg));
1617 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_prior_base_curr_doc: ' || '-- Rel Grp attribute Name: '||
1618 g_rel_tax_line_amounts(l_counter).GrpAttrname);
1619 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_prior_base_curr_doc: ' || '-- Rel Grp attribute Value: '||
1620 g_rel_tax_line_amounts(l_counter).GrpAttrvalue);
1621 END IF;
1622
1623 l_counter := l_counter - 1;
1624 END LOOP;
1625 END IF;
1626
1627 IF (g_level_statement >= g_current_runtime_level) THEN
1628 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_prior_base_curr_doc: ' || '-O- Applicable Prior Base: '||to_char(p_appl_prior_base));
1629 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_prior_base_curr_doc: ' || '-O- Charged tax Amount: ' ||to_char(p_charged_tax_amount));
1630 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_prior_base_curr_doc: ' || '-O- Calculated tax Amount: ' ||
1631 to_char(p_calculated_tax_amount));
1632 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','jl_zz_tax.get_prior_base_curr_doc()-');
1633 END IF;
1634
1635 END get_prior_base_curr_doc;
1636
1637 -- Following procedure is created on 19-SEP-98 by Harsh Takle
1638 -- Procedure will return applicable prior base amount and charged tax amount
1639 -- Thie procedure will call get_prior_base_curr_doc procedure to get these
1640 -- amounts.
1641 -- Procedure is called from calculate_tax_amount procedure.
1642
1643 PROCEDURE get_prior_base(p_operation_level IN VARCHAR2,
1644 p_tax_category_id IN NUMBER,
1645 p_grp_attr_name IN VARCHAR2,
1646 p_grp_attr_value IN VARCHAR2,
1647 p_appl_prior_base IN OUT NOCOPY NUMBER,
1648 p_charged_tax_amount IN OUT NOCOPY NUMBER,
1649 p_calculated_tax_amount IN OUT NOCOPY NUMBER) IS
1650
1651
1652
1653 BEGIN
1654 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1655
1656 IF (g_level_statement >= g_current_runtime_level) THEN
1657 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','jl_zz_tax.get_prior_base()+');
1658 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_prior_base: ' || '-P- Operation Level: '|| p_operation_level);
1659 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_prior_base: ' || '-P- Tax category: '||to_char(p_tax_category_id));
1660 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_prior_base: ' || '-P- Grouping Attribute Name: '||p_grp_attr_name);
1661 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_prior_base: ' || '-P- Grouping Attribute Value: ' ||p_grp_attr_value);
1662 END IF;
1663
1664 IF p_operation_level = 'OPERATION' THEN
1665 get_prior_base_curr_doc(p_tax_category_id,p_grp_attr_name,
1666 p_grp_attr_value,p_appl_prior_base,
1667 p_charged_tax_amount,p_calculated_tax_amount);
1668 ELSIF p_operation_level = 'DOCUMENT' THEN
1669 IF g_first_tax_line = TRUE THEN
1670 p_appl_prior_base := 0;
1671 p_charged_tax_amount := 0;
1672 p_calculated_tax_amount := 0;
1673 ELSE
1674 get_prior_base_curr_doc(p_tax_category_id,p_grp_attr_name,
1675 p_grp_attr_value,p_appl_prior_base,
1676 p_charged_tax_amount,p_calculated_tax_amount);
1677 END IF;
1678 END IF;
1679
1680 IF (g_level_statement >= g_current_runtime_level) THEN
1681 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_prior_base: ' || '-O- Applicable Prior Base: '||to_char(p_appl_prior_base));
1682 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_prior_base: ' || '-O- Charged tax Amount: ' ||to_char(p_charged_tax_amount));
1683 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_prior_base: ' || '-O- Calculated tax Amount: ' ||
1684 to_char(p_calculated_tax_amount));
1685 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','jl_zz_tax.get_prior_base()-');
1686 END IF;
1687
1688 END get_prior_base;
1689
1690 -- Following function is created on 19-SEP-98 by Harsh Takle
1691 -- Function will check whether related tax line category of type DOCUMENT
1692 -- is included in cuurent tax line tax group. If it is included then function
1693 -- will return Included otherwise return an Error
1694 -- This function is called from validate_current_tax_line function.
1695
1696 -- Bugfix 1388703
1697 FUNCTION validate_rel_tax_line_category(p_tax_group IN NUMBER,
1698 p_tax_category_id IN NUMBER,
1699 p_trx_date IN DATE,
1700 p_site_use_id IN NUMBER,
1701 p_organization_class IN VARCHAR2,
1702 p_contributor_class IN VARCHAR2,
1703 p_transaction_nature_class IN VARCHAR2)
1704 RETURN VARCHAR2 IS
1705
1706 l_return_code VARCHAR2(20);
1707 l_curr_nature_class_value VARCHAR2(30);
1708 l_establishment_type VARCHAR2(30);
1709 l_contributor_type VARCHAR2(30);
1710 l_dummy NUMBER;
1711
1712
1713
1714 BEGIN
1715 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1716
1717 IF (g_level_statement >= g_current_runtime_level) THEN
1718 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','jl_zz_tax.validate_rel_tax_line_category()+');
1719 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','validate_rel_tax_line_category: ' || '-P- Organization Class: '||p_organization_class);
1720 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','validate_rel_tax_line_category: ' || '-P- Contributor Class: '||p_contributor_class);
1721 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','validate_rel_tax_line_category: ' || '-P- Trx Nature Class: '||p_transaction_nature_class);
1722 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','validate_rel_tax_line_category: ' || '-P- Tax Group: ' ||to_char(p_tax_group));
1723 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','validate_rel_tax_line_category: ' || '-P- Tax category: '||to_char(p_tax_category_id));
1724 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','validate_rel_tax_line_category: ' || '-P- Site use id : '||to_char(p_site_use_id));
1725 END IF;
1726
1727 l_curr_nature_class_value := NULL;
1728 BEGIN
1729 -- Bugfix 1388703
1730 SELECT ac.tax_attribute_value
1731 INTO l_curr_nature_class_value
1732 FROM jl_zz_ar_tx_att_cls ac
1733 WHERE ac.tax_attr_class_type = 'TRANSACTION_CLASS'
1734 AND ac.tax_attr_class_code = p_transaction_nature_class
1735 AND ac.tax_category_id = p_tax_category_id
1736 AND ac.tax_attribute_type = 'TRANSACTION_ATTRIBUTE'
1737 AND ac.enabled_flag = 'Y'
1738 AND exists (select 1
1739 from jl_zz_ar_tx_cat_att ca
1740 WHERE ca.tax_category_id = ac.tax_category_id
1741 AND ca.tax_attribute_type = 'TRANSACTION_ATTRIBUTE'
1742 AND ca.tax_attribute_name = ac.tax_attribute_name);
1743
1744 EXCEPTION
1745 WHEN OTHERS THEN
1746 l_curr_nature_class_value := NULL;
1747 END;
1748
1749 IF (g_level_statement >= g_current_runtime_level) THEN
1750 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','validate_rel_tax_line_category: ' || '-- Current Nature Class value: '||l_curr_nature_class_value);
1751 END IF;
1752
1753 l_establishment_type := NULL;
1754 l_contributor_type := NULL;
1755
1756 BEGIN
1757 -- Bugfix 1783986
1758 IF NVL(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf4,'N') = 'Y' THEN
1759 select ota.tax_attribute_value,
1760 cta.tax_attribute_value
1761 into l_establishment_type,
1762 l_contributor_type
1763 FROM jl_zz_ar_tx_cus_cls cta,
1764 hz_cust_site_uses rsu,
1765 jl_zz_ar_tx_att_cls ota,
1766 jl_zz_ar_tx_categ tc
1767 WHERE tc.tax_category_id = p_tax_category_id
1768 AND tc.threshold_check_grp_by = 'DOCUMENT'
1769 AND ota.tax_attr_class_type = 'ORGANIZATION_CLASS'
1770 AND ota.tax_category_id = tc.tax_category_id
1771 AND ota.tax_attribute_type = 'ORGANIZATION_ATTRIBUTE'
1772 AND ota.tax_attribute_name = tc.org_tax_attribute
1773 AND ota.tax_attr_class_code = p_organization_class
1774 AND rsu.site_use_id =
1775 decode(tc.tax_category_id, null, 0, p_site_use_id)
1776 AND cta.address_id = rsu.cust_acct_site_id
1777 AND cta.tax_category_id = tc.tax_category_id
1778 AND cta.tax_attribute_name = tc.cus_tax_attribute
1779 AND cta.tax_attr_class_code = p_contributor_class
1780 AND cta.enabled_flag = 'Y';
1781 ELSE
1782 select ota.tax_attribute_value,
1783 cta.tax_attribute_value
1784 into l_establishment_type,
1785 l_contributor_type
1786 FROM jl_zz_ar_tx_att_cls cta,
1787 jl_zz_ar_tx_att_cls ota,
1788 jl_zz_ar_tx_categ tc
1789 WHERE tc.tax_category_id = p_tax_category_id
1790 AND tc.threshold_check_grp_by = 'DOCUMENT'
1791 AND ota.tax_attr_class_type = 'ORGANIZATION_CLASS'
1792 AND ota.tax_category_id = tc.tax_category_id
1793 AND ota.tax_attribute_type = 'ORGANIZATION_ATTRIBUTE'
1794 AND ota.tax_attribute_name = tc.org_tax_attribute
1795 AND ota.tax_attr_class_code = p_organization_class
1796 AND cta.tax_attr_class_type = 'CONTRIBUTOR_CLASS'
1797 AND cta.tax_category_id = tc.tax_category_id
1798 AND cta.tax_attribute_type = 'CONTRIBUTOR_ATTRIBUTE'
1799 AND cta.tax_attribute_name = tc.cus_tax_attribute
1800 AND cta.tax_attr_class_code = p_contributor_class
1801 AND cta.enabled_flag = 'Y';
1802 END IF;
1803
1804 EXCEPTION
1805 WHEN OTHERS THEN
1806 l_establishment_type := NULL;
1807 l_contributor_type := NULL;
1808 END;
1809
1810 IF (g_level_statement >= g_current_runtime_level) THEN
1811 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','validate_rel_tax_line_category: ' || '-- Establishment Type: ' || l_establishment_type);
1812 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','validate_rel_tax_line_category: ' || '-- Contributor Type: ' || l_contributor_type);
1813 END IF;
1814
1815 l_dummy := 0;
1816 BEGIN
1817 SELECT 1
1818 INTO l_dummy
1819 FROM jl_zz_ar_tx_groups tg
1820 WHERE tg.group_tax_id = p_tax_group
1821 AND tg.tax_category_id = p_tax_category_id
1822 AND tg.transaction_nature = l_curr_nature_class_value
1823 AND tg.establishment_type = l_establishment_type
1824 AND tg.contributor_type = l_contributor_type
1825 AND p_trx_date <= tg.end_date_active
1826 AND p_trx_date >= NVL(tg.start_date_active, p_trx_date);
1827
1828 EXCEPTION
1829 WHEN OTHERS THEN
1830 l_dummy := 0;
1831 END;
1832
1833 IF (g_level_statement >= g_current_runtime_level) THEN
1834 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','validate_rel_tax_line_category: ' || '-- Include Status: ' || to_char(l_dummy));
1835 END IF;
1836 IF l_dummy = 1 THEN
1837 l_return_code := 'INCLUDED';
1838 ELSE
1839 l_return_code := 'NOT INCLUDED';
1840 END IF;
1841
1842 IF (g_level_statement >= g_current_runtime_level) THEN
1843 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','validate_rel_tax_line_category: ' || '-O- return_code: ' || l_return_code);
1844 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','jl_zz_tax.validate_rel_tax_line_category()-');
1845 END IF;
1846
1847 RETURN l_return_code;
1848 END validate_rel_tax_line_category;
1849
1850 -- Following function is created on 19-SEP-98 by Harsh Takle
1851 -- Function calls validate_rel_tax_line_category function to validate related
1852 -- tax line category of type DOCUMENT
1853 -- Function will also check whether current tax line category of DOCUMENT is
1854 -- included in all related invoice lines
1855 -- Function is called from calculate_tax_amount procedure.
1856
1857 -- Bugfix 1388703
1858 FUNCTION validate_current_tax_line (
1859 p_rel_customer_trx_id IN NUMBER,
1860 p_customer_trx_id IN NUMBER,
1861 p_customer_trx_line_number IN NUMBER,
1862 p_operation_level IN VARCHAR2,
1863 p_tax_group IN NUMBER,
1864 p_tax_category_id IN NUMBER,
1865 p_grp_attr_name IN VARCHAR2,
1866 p_trx_date IN DATE,
1867 p_site_use_id IN NUMBER,
1868 p_organization_class IN VARCHAR2,
1869 p_contributor_class IN VARCHAR2,
1870 p_transaction_nature_class IN VARCHAR2)
1871 RETURN VARCHAR2 IS
1872
1873 l_return_code VARCHAR2(20);
1874 l_counter BINARY_INTEGER;
1875 l_include_status VARCHAR2(20);
1876 l_prev_inv_line_number NUMBER(15);
1877 l_tax_category_match_flag VARCHAR2(1);
1878 l_max_table_entries NUMBER;
1879 l_prev_header_id NUMBER;
1880 l_rel_trx_categ_ctr NUMBER;
1881 l_tax_category_id NUMBER;
1882
1883
1884
1885 BEGIN
1886 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1887
1888 IF (g_level_statement >= g_current_runtime_level) THEN
1889 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','jl_zz_tax.validate_current_tax_line()+');
1890 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','validate_current_tax_line: ' || '-P- Customer Trx Id: ' ||to_char(p_customer_trx_id));
1891 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','validate_current_tax_line: ' || '-P- Rel Customer Trx Id: '||
1892 to_char(p_rel_customer_trx_id));
1893 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','validate_current_tax_line: ' || '-P- Customer trx line number: '||
1894 to_char(p_customer_trx_line_number));
1895 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','validate_current_tax_line: ' || '-P- Operation Level: '||p_operation_level);
1896 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','validate_current_tax_line: ' || '-P- Tax Group: ' ||to_char(p_tax_group));
1897 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','validate_current_tax_line: ' || '-P- Tax category: '||to_char(p_tax_category_id));
1898 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','validate_current_tax_line: ' || '-P- Grouping Attribute Name: '||p_grp_attr_name);
1899 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','validate_current_tax_line: ' || '-P- Trx Nature Class: '||p_transaction_nature_class);
1900 END IF;
1901
1902 l_return_code := 'SUCCESS';
1903 IF g_first_tax_line = TRUE THEN
1904 IF p_operation_level = 'DOCUMENT' THEN
1905 RETURN l_return_code;
1906 END IF;
1907 END IF;
1908
1909 l_counter := 1;
1910 l_prev_inv_line_number := Null;
1911 l_tax_category_match_flag := Null;
1912 l_max_table_entries := g_rel_trx_categ.COUNT;
1913
1914 IF (g_level_statement >= g_current_runtime_level) THEN
1915 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','validate_current_tax_line: ' || '-- Rel lines table entries: '||to_char(l_max_table_entries));
1916 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','validate_current_tax_line: ' || '-- Tax line amounts table entries: '||
1917 to_char(g_rel_tax_line_amounts.COUNT));
1918 END IF;
1919 IF l_max_table_entries = 0 AND p_grp_attr_name = 'DOCUMENT' AND
1920 g_rel_tax_line_amounts.COUNT > 0 AND
1921 (p_customer_trx_id <> NVL(g_prev_header_id,p_customer_trx_id) OR
1922 p_customer_trx_line_number <>
1923 NVL(g_prev_cust_trx_line_number,p_customer_trx_line_number)) THEN
1924 l_return_code := 'ERROR';
1925 RETURN l_return_code;
1926 END IF;
1927
1928
1929 IF g_rel_trx_categ.FIRST is not null THEN
1930
1931 l_tax_category_id := g_rel_trx_categ.FIRST;
1932
1933 LOOP
1934 IF p_tax_group IS NULL THEN
1935 IF (g_level_statement >= g_current_runtime_level) THEN
1936 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','validate_current_tax_line: ' || '-- Current tax category: '||
1937 to_char(p_tax_category_id));
1938 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','validate_current_tax_line: ' || '-- Related tax category: '||
1939 to_char(l_tax_category_id));
1940 END IF;
1941 IF NOT g_rel_trx_categ.EXISTS(p_tax_category_id) THEN
1942 l_return_code := 'ERROR';
1943 EXIT;
1944 END IF;
1945 ELSE
1946 -- Tax Category from PL/SQL table g_rel_trx_categ is of type DOCUMENT
1947 -- So check whether it is included in current tax line tax group
1948 -- Bugfix 1388703
1949 l_include_status := validate_rel_tax_line_category(p_tax_group,
1950 l_tax_category_id,
1951 p_trx_date,
1952 p_site_use_id,
1953 p_organization_class,
1954 p_contributor_class,
1955 p_transaction_nature_class);
1956 IF l_include_status = 'NOT_INCLUDED' THEN
1957 l_return_code := 'ERROR';
1958 EXIT;
1959 END IF;
1960 END IF;
1961
1962 IF (g_level_statement >= g_current_runtime_level) THEN
1963 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','validate_current_tax_line: ' || '-- Rel tax lines table Tax category: '||
1964 to_char(l_tax_category_id));
1965 END IF;
1966
1967 IF l_tax_category_id = g_rel_trx_categ.LAST THEN
1968 EXIT;
1969 END IF;
1970 l_tax_category_id := g_rel_trx_categ.NEXT(l_tax_category_id);
1971 END LOOP;
1972 END IF;
1973
1974 IF l_return_code = 'ERROR' THEN
1975 IF (g_level_statement >= g_current_runtime_level) THEN
1976 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','validate_current_tax_line: ' || '-O- Return Code: '||l_return_code);
1977 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','jl_zz_tax.validate_current_tax_line()-');
1978 END IF;
1979
1980 RETURN l_return_code;
1981 END IF;
1982
1983 IF p_grp_attr_name = 'DOCUMENT' AND
1984 (g_first_processed_invoice_line <> p_customer_trx_line_number) THEN
1985
1986 IF g_rel_trx_categ.EXISTS(p_tax_category_id) THEN
1987 l_return_code := 'SUCCESS';
1988 ELSE
1989 l_return_code := 'ERROR';
1990 END IF;
1991
1992 END IF;
1993
1994 IF (g_level_statement >= g_current_runtime_level) THEN
1995 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','validate_current_tax_line: ' || '-- Return Code: '||l_return_code);
1996 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','validate_current_tax_line: ' || '-O- Return Code: '||l_return_code);
1997 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','jl_zz_tax.validate_current_tax_line()-');
1998 END IF;
1999
2000 RETURN l_return_code;
2001 END validate_current_tax_line;
2002
2003 -- Following prcedure is created on 19-SEP-98 by Harsh Takle
2004 -- Procedure will return minimum thresholds for tax rate, tax amount and
2005 -- taxable basis for current tax category and/or current tax group
2006 -- Function is called from calculate_tax_amount procedure.
2007
2008 PROCEDURE get_minimum_thresholds (p_tax_group_id IN NUMBER,
2009 p_tax_category_id IN NUMBER,
2010 p_trx_date IN DATE,
2011 p_establishment_type IN VARCHAR2,
2012 p_contributor_type IN VARCHAR2,
2013 p_transaction_nature IN VARCHAR2,
2014 p_transaction_nature_class IN VARCHAR2,
2015 p_rule_code IN VARCHAR2,
2016 p_min_tax_rate IN OUT NOCOPY NUMBER,
2017 p_min_tax_amount IN OUT NOCOPY NUMBER,
2018 p_min_taxable_basis IN OUT NOCOPY NUMBER) IS
2019
2020 l_use_tx_categ_thresholds VARCHAR2(10);
2021 l_nat_min_taxable_base NUMBER;
2022 l_nat_min_tax_amount NUMBER;
2023 l_nat_min_tax_rate NUMBER;
2024
2025 -- Bugfix 1388703
2026 CURSOR trx_nature IS
2027 SELECT tcav.tax_categ_attr_val_id tax_categ_attr_val_id
2028 FROM jl_zz_ar_tx_att_val tcav,
2029 jl_zz_ar_tx_cat_att tca,
2030 jl_zz_ar_tx_att_cls tcac
2031 WHERE tcav.tax_attribute_type = 'TRANSACTION_ATTRIBUTE'
2032 AND tcav.tax_category_id = p_tax_category_id
2033 AND tcac.tax_attr_class_code = p_transaction_nature_class
2034 AND tcac.tax_category_id = tcav.tax_category_id
2035 AND tcac.tax_attr_class_type = 'TRANSACTION_CLASS'
2036 AND tcac.enabled_flag = 'Y'
2037 AND tca.tax_attribute_type = 'TRANSACTION_ATTRIBUTE'
2038 AND tca.tax_attribute_name = tcac.tax_attribute_name
2039 AND tca.tax_attribute_name = tcav.tax_attribute_name
2040 AND tcac.tax_attribute_value = tcav.tax_attribute_value
2041 ORDER BY tca.priority_number;
2042
2043
2044
2045 BEGIN
2046 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2047
2048 IF (g_level_statement >= g_current_runtime_level) THEN
2049 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','jl_zz_tax.get_minimum_thresholds()+');
2050 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_minimum_thresholds: ' || '-P- Tax Group: '||to_char(p_tax_group_id));
2051 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_minimum_thresholds: ' || '-P- Tax category: '||to_char(p_tax_category_id));
2052 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_minimum_thresholds: ' || '-P- Establishment Type: '||p_establishment_type);
2053 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_minimum_thresholds: ' || '-P- Contributor Type: '||p_contributor_type);
2054 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_minimum_thresholds: ' || '-P- Transaction Nature: '||p_transaction_nature);
2055 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_minimum_thresholds: ' || '-P- Transaction Nature Class: '||p_transaction_nature_class);
2056 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_minimum_thresholds: ' || '-P- Rule Code: '||p_rule_code);
2057 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','-P- Trx Date: '||to_char(p_trx_date,'DD-MM-YYYY'));
2058 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_minimum_thresholds: ' || '-P- Minimum Tax Rate: '||to_char(p_min_tax_rate));
2059 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_minimum_thresholds: ' || '-P- Minimum Tax Amount: '||to_char(p_min_tax_amount));
2060 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_minimum_thresholds: ' || '-P- Minimum Taxable basis: '||to_char(p_min_taxable_basis));
2061 END IF;
2062
2063 p_min_tax_rate := NULL;
2064 p_min_tax_amount := NULL;
2065 p_min_taxable_basis := NULL;
2066 l_use_tx_categ_thresholds := NULL;
2067
2068 BEGIN
2069 SELECT min_percentage,
2070 min_amount,
2071 min_taxable_basis,
2072 use_tx_categ_thresholds
2073 INTO p_min_tax_rate,
2074 p_min_tax_amount,
2075 p_min_taxable_basis,
2076 l_use_tx_categ_thresholds
2077 FROM jl_zz_ar_tx_groups
2078 WHERE group_tax_id = p_tax_group_id
2079 AND tax_category_id = p_tax_category_id
2080 AND establishment_type = p_establishment_type
2081 AND contributor_type = p_contributor_type
2082 AND transaction_nature = p_transaction_nature
2083 AND p_trx_date BETWEEN NVL(start_date_active,p_trx_date)
2084 AND end_date_active;
2085 EXCEPTION
2086 WHEN NO_DATA_FOUND THEN
2087 p_min_tax_rate := NULL;
2088 p_min_tax_amount := NULL;
2089 p_min_taxable_basis := NULL;
2090 l_use_tx_categ_thresholds := NULL;
2091 END;
2092 IF NVL(p_min_tax_rate,0) = 0 AND l_use_tx_categ_thresholds = 'Y' THEN
2093 BEGIN
2094 SELECT min_percentage
2095 INTO p_min_tax_rate
2096 FROM jl_zz_ar_tx_cat_dtl
2097 WHERE tax_category_id = p_tax_category_id
2098 AND p_trx_date BETWEEN NVL(start_date_active,p_trx_date)
2099 AND end_date_active;
2100 EXCEPTION
2101 WHEN NO_DATA_FOUND THEN
2102 BEGIN
2103 SELECT min_percentage
2104 INTO p_min_tax_rate
2105 FROM jl_zz_ar_tx_categ
2106 WHERE tax_category_id = p_tax_category_id
2107 AND p_trx_date BETWEEN NVL(start_date_active,p_trx_date)
2108 AND end_date_active;
2109 EXCEPTION
2110 WHEN NO_DATA_FOUND THEN
2111 p_min_tax_rate := 0;
2112 END;
2113 END;
2114 END IF;
2115 IF NVL(p_min_tax_amount,0) = 0 AND l_use_tx_categ_thresholds = 'Y' THEN
2116 BEGIN
2117 SELECT min_amount
2118 INTO p_min_tax_amount
2119 FROM jl_zz_ar_tx_cat_dtl
2120 WHERE tax_category_id = p_tax_category_id
2121 AND p_trx_date BETWEEN NVL(start_date_active,p_trx_date)
2122 AND end_date_active;
2123 EXCEPTION
2124 WHEN NO_DATA_FOUND THEN
2125 BEGIN
2126 SELECT min_amount
2127 INTO p_min_tax_amount
2128 FROM jl_zz_ar_tx_categ
2129 WHERE tax_category_id = p_tax_category_id
2130 AND p_trx_date BETWEEN NVL(start_date_active,p_trx_date)
2131 AND end_date_active;
2132 EXCEPTION
2133 WHEN NO_DATA_FOUND THEN
2134 p_min_tax_amount := 0;
2135 END;
2136 END;
2137 END IF;
2138 IF NVL(p_min_taxable_basis,0) = 0 AND l_use_tx_categ_thresholds = 'Y' THEN
2139 BEGIN
2140 SELECT min_taxable_basis
2141 INTO p_min_taxable_basis
2142 FROM jl_zz_ar_tx_cat_dtl
2143 WHERE tax_category_id = p_tax_category_id
2144 AND p_trx_date BETWEEN NVL(start_date_active,p_trx_date)
2145 AND end_date_active;
2146 EXCEPTION
2147 WHEN NO_DATA_FOUND THEN
2148 BEGIN
2149 SELECT min_taxable_basis
2150 INTO p_min_taxable_basis
2151 FROM jl_zz_ar_tx_categ
2152 WHERE tax_category_id = p_tax_category_id
2153 AND p_trx_date BETWEEN NVL(start_date_active,p_trx_date)
2154 AND end_date_active;
2155 EXCEPTION
2156 WHEN NO_DATA_FOUND THEN
2157 p_min_taxable_basis := 0;
2158 END;
2159 END;
2160 END IF;
2161
2162 IF p_rule_code = 'GET_TRX_NATURE_TX_CODE' THEN
2163 FOR trx_nature_rec IN trx_nature
2164 LOOP
2165 l_nat_min_tax_rate := NULL;
2166 l_nat_min_tax_amount := NULL;
2167 l_nat_min_taxable_base := NULL;
2168 BEGIN
2169 SELECT min_percentage,
2170 min_amount,
2171 min_taxable_basis
2172 INTO l_nat_min_tax_rate,
2173 l_nat_min_tax_amount,
2174 l_nat_min_taxable_base
2175 FROM jl_zz_ar_tx_nat_rat
2176 WHERE tax_categ_attr_val_id = trx_nature_rec.tax_categ_attr_val_id
2177 AND p_trx_date BETWEEN NVL(start_date_active,p_trx_date)
2178 AND end_date_active;
2179 EXCEPTION
2180 WHEN OTHERS THEN
2181 l_nat_min_tax_rate := NULL;
2182 l_nat_min_tax_amount := NULL;
2183 l_nat_min_taxable_base := NULL;
2184 END;
2185 IF l_nat_min_tax_rate IS NOT NULL OR
2186 l_nat_min_tax_amount IS NOT NULL OR
2187 l_nat_min_taxable_base IS NOT NULL
2188 THEN
2189 p_min_tax_rate := NVL(l_nat_min_tax_rate,p_min_tax_rate);
2190 p_min_tax_amount := NVL(l_nat_min_tax_amount,p_min_tax_amount);
2191 p_min_taxable_basis := NVL(l_nat_min_taxable_base,p_min_taxable_basis);
2192 EXIT;
2193 END IF;
2194 END LOOP;
2195 END IF;
2196
2197 IF (g_level_statement >= g_current_runtime_level) THEN
2198 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_minimum_thresholds: ' || '-O- Minimum Tax Rate: '||to_char(p_min_tax_rate));
2199 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_minimum_thresholds: ' || '-O- Minimum Tax Amount: '||to_char(p_min_tax_amount));
2200 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_minimum_thresholds: ' || '-O- Minimum Taxable basis: '||to_char(p_min_taxable_basis));
2201 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','jl_zz_tax.get_minimum_thresholds()-');
2202 END IF;
2203
2204 end get_minimum_thresholds;
2205
2206 -- Bugfix 1388703
2207 -- Following procedure is created on 19-SEP-98 by Harsh Takle
2208 -- Procedure will populate following PL/SQL tables
2209 -- Table g_all_tax_grp will be populated with all tax groups and associated
2210 -- tax categories of type DOCUMENT and determining attribute values
2211 -- Function is called from calculate_tax_amount procedure.
2212 -- 22-FEB-99 Changed cursor to consider
2213 -- Credit transactions of related transactions
2214 -- Related transactions of Main transaction for current credit
2215 -- Transaction
2216 -- Credit transactions of main transaction for current credit
2217 -- Transaction
2218 -- Credit Transactions of other related transaction for
2219 -- current related transaction
2220 -- Credit Transactions of main transaction for
2221 -- current related transaction
2222 PROCEDURE populate_plsql_tables (p_rel_cust_trx_id IN NUMBER,
2223 p_prev_cust_trx_id IN NUMBER,
2224 p_curr_cust_trx_id IN NUMBER,
2225 p_trx_date IN DATE) IS
2226
2227 l_rel_tax_lines_ctr BINARY_INTEGER;
2228 l_rel_tax_line_amount_ctr BINARY_INTEGER;
2229 l_count NUMBER;
2230 l_rel_trx_categ_ctr BINARY_INTEGER;
2231
2232 -- Bugfix 1388703
2233 CURSOR prev_tax_lines IS
2234 --Main Transaction of current credit transaction
2235 SELECT tc1.tax_category_id tax_category_id
2236 ,tc1.threshold_check_grp_by grp_attr_name
2237 ,tcl1.tax_attribute_value grp_attr_value
2238 ,nvl(rlt1.global_attribute11,0) appl_prior_base
2239 ,nvl(rlt1.global_attribute19,0) charged_tax
2240 ,nvl(rlt1.global_attribute20,0) calculated_tax
2241 ,rl1.customer_trx_line_id inv_line_number
2242 ,r1.customer_trx_id header_trx_id
2243 FROM ra_customer_trx r1
2244 ,ra_customer_trx_lines rl1
2245 ,ra_customer_trx_lines rlt1
2246 ,ar_vat_tax v1
2247 ,jl_zz_ar_tx_att_cls tcl1
2248 ,jl_zz_ar_tx_categ tc1
2249 WHERE r1.customer_trx_id = rl1.customer_trx_id
2250 AND r1.customer_trx_id = p_prev_cust_trx_id
2251 AND rl1.line_type = 'LINE'
2252 AND r1.customer_trx_id = rlt1.customer_trx_id
2253 AND rl1.customer_trx_line_id = rlt1.link_to_cust_trx_line_id
2254 AND rlt1.line_type= 'TAX'
2255 AND rlt1.vat_tax_id = v1.vat_tax_id
2256 AND p_trx_date >= nvl(v1.start_date,to_date( '01011900', 'ddmmyyyy'))
2257 AND p_trx_date <= nvl(v1.end_date, p_trx_date)
2258 AND v1.tax_type = 'VAT'
2259 AND v1.global_attribute1 = tc1.tax_category_id
2260 AND rl1.global_attribute3 = tcl1.tax_attr_class_code
2261 AND tcl1.tax_category_id = tc1.tax_category_id
2262 AND tcl1.tax_attr_class_type = 'TRANSACTION_CLASS'
2263 AND tc1.txn_tax_attribute = tcl1.tax_attribute_name
2264 AND p_trx_date <= tc1.end_date_active
2265 AND p_trx_date >= NVL(tc1.start_date_active,p_trx_date)
2266 UNION --Related transactions of main transaction for current credit
2267 --Transaction
2268 SELECT tc1.tax_category_id tax_category_id
2269 ,tc1.threshold_check_grp_by grp_attr_name
2270 ,tcl1.tax_attribute_value grp_attr_value
2271 ,nvl(rlt1.global_attribute11,0) appl_prior_base
2272 ,nvl(rlt1.global_attribute19,0) charged_tax
2273 ,nvl(rlt1.global_attribute20,0) calculated_tax
2274 ,rl1.customer_trx_line_id inv_line_number
2275 ,r1.customer_trx_id header_trx_id
2276 FROM ra_customer_trx r1
2277 ,ra_customer_trx_lines rl1
2278 ,ra_customer_trx_lines rlt1
2279 ,ar_vat_tax v1
2280 ,jl_zz_ar_tx_att_cls tcl1
2281 ,jl_zz_ar_tx_categ tc1
2282 WHERE r1.customer_trx_id = rl1.customer_trx_id
2283 AND r1.related_customer_trx_id = p_prev_cust_trx_id
2284 AND rl1.line_type = 'LINE'
2285 AND r1.customer_trx_id = rlt1.customer_trx_id
2286 AND rl1.customer_trx_line_id = rlt1.link_to_cust_trx_line_id
2287 AND rlt1.line_type= 'TAX'
2288 AND rlt1.vat_tax_id = v1.vat_tax_id
2289 AND p_trx_date >= nvl(v1.start_date,to_date( '01011900', 'ddmmyyyy'))
2290 AND p_trx_date <= nvl(v1.end_date, p_trx_date)
2291 AND v1.tax_type = 'VAT'
2292 AND v1.global_attribute1 = tc1.tax_category_id
2293 AND rl1.global_attribute3 = tcl1.tax_attr_class_code
2294 AND tcl1.tax_category_id = tc1.tax_category_id
2295 AND tcl1.tax_attr_class_type = 'TRANSACTION_CLASS'
2296 AND tc1.txn_tax_attribute = tcl1.tax_attribute_name
2297 AND p_trx_date <= tc1.end_date_active
2298 AND p_trx_date >= NVL(tc1.start_date_active,p_trx_date)
2299 UNION --Credit transactions of main transaction for current credit
2300 --Transaction
2301 SELECT tc1.tax_category_id tax_category_id
2302 ,tc1.threshold_check_grp_by grp_attr_name
2303 ,tcl1.tax_attribute_value grp_attr_value
2304 ,nvl(rlt1.global_attribute11,0) appl_prior_base
2305 ,nvl(rlt1.global_attribute19,0) charged_tax
2306 ,nvl(rlt1.global_attribute20,0) calculated_tax
2307 ,rl1.customer_trx_line_id inv_line_number
2308 ,r1.customer_trx_id header_trx_id
2309 FROM ra_customer_trx r1
2310 ,ra_customer_trx_lines rl1
2311 ,ra_customer_trx_lines rlt1
2312 ,ar_vat_tax v1
2313 ,jl_zz_ar_tx_att_cls tcl1
2314 ,jl_zz_ar_tx_categ tc1
2315 WHERE r1.customer_trx_id = rl1.customer_trx_id
2316 AND r1.previous_customer_trx_id = p_prev_cust_trx_id
2317 AND r1.customer_trx_id <> p_curr_cust_trx_id
2318 AND rl1.line_type = 'LINE'
2319 AND r1.customer_trx_id = rlt1.customer_trx_id
2320 AND rl1.customer_trx_line_id = rlt1.link_to_cust_trx_line_id
2321 AND rlt1.line_type= 'TAX'
2322 AND rlt1.vat_tax_id = v1.vat_tax_id
2323 AND p_trx_date >= nvl(v1.start_date,to_date( '01011900', 'ddmmyyyy'))
2324 AND p_trx_date <= nvl(v1.end_date, p_trx_date)
2325 AND v1.tax_type = 'VAT'
2326 AND v1.global_attribute1 = tc1.tax_category_id
2327 AND rl1.global_attribute3 = tcl1.tax_attr_class_code
2328 AND tcl1.tax_category_id = tc1.tax_category_id
2329 AND tcl1.tax_attr_class_type = 'TRANSACTION_CLASS'
2330 AND tc1.txn_tax_attribute = tcl1.tax_attribute_name
2331 AND p_trx_date <= tc1.end_date_active
2332 AND p_trx_date >= NVL(tc1.start_date_active,p_trx_date)
2333 UNION --Credit transactions of related transactions
2334 SELECT tc1.tax_category_id tax_category_id
2335 ,tc1.threshold_check_grp_by grp_attr_name
2336 ,tcl1.tax_attribute_value grp_attr_value
2337 ,nvl(rlt1.global_attribute11,0) appl_prior_base
2338 ,nvl(rlt1.global_attribute19,0) charged_tax
2339 ,nvl(rlt1.global_attribute20,0) calculated_tax
2340 ,rl1.customer_trx_line_id inv_line_number
2341 ,r1.customer_trx_id header_trx_id
2342 FROM ra_customer_trx r1
2343 ,ra_customer_trx r2
2344 ,ra_customer_trx_lines rl1
2345 ,ra_customer_trx_lines rlt1
2346 ,ar_vat_tax v1
2347 ,jl_zz_ar_tx_att_cls tcl1
2348 ,jl_zz_ar_tx_categ tc1
2349 WHERE r2.related_customer_trx_id = p_prev_cust_trx_id
2350 AND r2.customer_trx_id = r1.previous_customer_trx_id
2351 AND r1.customer_trx_id = rl1.customer_trx_id
2352 AND rl1.line_type = 'LINE'
2353 AND r1.customer_trx_id = rlt1.customer_trx_id
2354 AND rl1.customer_trx_line_id = rlt1.link_to_cust_trx_line_id
2355 AND rlt1.line_type= 'TAX'
2356 AND rlt1.vat_tax_id = v1.vat_tax_id
2357 AND p_trx_date >= nvl(v1.start_date,to_date( '01011900', 'ddmmyyyy'))
2358 AND p_trx_date <= nvl(v1.end_date, p_trx_date)
2359 AND v1.tax_type = 'VAT'
2360 AND v1.global_attribute1 = tc1.tax_category_id
2361 AND rl1.global_attribute3 = tcl1.tax_attr_class_code
2362 AND tcl1.tax_category_id = tc1.tax_category_id
2363 AND tcl1.tax_attr_class_type = 'TRANSACTION_CLASS'
2364 AND tc1.txn_tax_attribute = tcl1.tax_attribute_name
2365 AND p_trx_date <= tc1.end_date_active
2366 AND p_trx_date >= NVL(tc1.start_date_active,p_trx_date)
2367 ORDER BY 7, 6, 1;
2368
2369 -- Bugfix 1388703
2370 CURSOR rel_tax_lines IS
2371 -- Main transaction of current related transaction
2372 SELECT tc1.tax_category_id tax_category_id
2373 ,tc1.threshold_check_grp_by grp_attr_name
2374 ,tcl1.tax_attribute_value grp_attr_value
2375 ,nvl(rlt1.taxable_amount,0) appl_prior_base
2376 ,fnd_number.canonical_to_number(nvl(rlt1.global_attribute19,0))
2377 charged_tax
2378 ,fnd_number.canonical_to_number(nvl(rlt1.global_attribute20,0))
2379 calculated_tax
2380 ,rl1.customer_trx_line_id inv_line_number
2381 ,r1.customer_trx_id header_trx_id
2382 FROM ra_customer_trx r1
2383 ,ra_customer_trx_lines rl1
2384 ,ra_customer_trx_lines rlt1
2385 ,ar_vat_tax v1
2386 ,jl_zz_ar_tx_att_cls tcl1
2387 ,jl_zz_ar_tx_categ tc1
2388 WHERE r1.customer_trx_id = rl1.customer_trx_id
2389 AND r1.customer_trx_id = p_rel_cust_trx_id
2390 AND rl1.line_type = 'LINE'
2391 AND r1.customer_trx_id = rlt1.customer_trx_id
2392 AND rl1.customer_trx_line_id = rlt1.link_to_cust_trx_line_id
2393 AND rlt1.line_type= 'TAX'
2394 AND rlt1.vat_tax_id = v1.vat_tax_id
2395 AND v1.tax_type = 'VAT'
2396 AND v1.global_attribute1 = tc1.tax_category_id
2397 AND rl1.global_attribute3 = tcl1.tax_attr_class_code
2398 AND tcl1.tax_category_id = tc1.tax_category_id
2399 AND tcl1.tax_attr_class_type = 'TRANSACTION_CLASS'
2400 AND tc1.txn_tax_attribute = tcl1.tax_attribute_name
2401 AND p_trx_date <= tc1.end_date_active
2402 AND p_trx_date >= NVL(tc1.start_date_active,p_trx_date)
2403 UNION --Related Transactions of main transaction of
2404 --current related transaction
2405 SELECT tc1.tax_category_id tax_category_id
2406 ,tc1.threshold_check_grp_by grp_attr_name
2407 ,tcl1.tax_attribute_value grp_attr_value
2408 ,nvl(rlt1.taxable_amount,0) appl_prior_base
2409 ,fnd_number.canonical_to_number(nvl(rlt1.global_attribute19,0))
2410 charged_tax
2411 ,fnd_number.canonical_to_number(nvl(rlt1.global_attribute20,0))
2412 calculated_tax
2413 ,rl1.customer_trx_line_id inv_line_number
2414 ,r1.customer_trx_id header_trx_id
2415 FROM ra_customer_trx r1
2416 ,ra_customer_trx_lines rl1
2417 ,ra_customer_trx_lines rlt1
2418 ,ar_vat_tax v1
2419 ,jl_zz_ar_tx_att_cls tcl1
2420 ,jl_zz_ar_tx_categ tc1
2421 WHERE r1.customer_trx_id = rl1.customer_trx_id
2422 AND r1.related_customer_trx_id = p_rel_cust_trx_id
2423 AND rl1.line_type = 'LINE'
2424 AND r1.customer_trx_id = rlt1.customer_trx_id
2425 AND rl1.customer_trx_line_id = rlt1.link_to_cust_trx_line_id
2426 AND rlt1.line_type= 'TAX'
2427 AND rlt1.vat_tax_id = v1.vat_tax_id
2428 AND v1.tax_type = 'VAT'
2429 AND v1.global_attribute1 = tc1.tax_category_id
2430 AND rl1.global_attribute3 = tcl1.tax_attr_class_code
2431 AND tcl1.tax_category_id = tc1.tax_category_id
2432 AND tcl1.tax_attr_class_type = 'TRANSACTION_CLASS'
2433 AND tc1.txn_tax_attribute = tcl1.tax_attribute_name
2434 AND p_trx_date <= tc1.end_date_active
2435 AND p_trx_date >= NVL(tc1.start_date_active,p_trx_date)
2436 UNION --Credit Transactions of main transaction for
2437 -- current related transaction
2438 SELECT tc1.tax_category_id tax_category_id
2439 ,tc1.threshold_check_grp_by grp_attr_name
2440 ,tcl1.tax_attribute_value grp_attr_value
2441 ,nvl(rlt1.taxable_amount,0) appl_prior_base
2442 ,fnd_number.canonical_to_number(nvl(rlt1.global_attribute19,0))
2443 charged_tax
2444 ,fnd_number.canonical_to_number(nvl(rlt1.global_attribute20,0))
2445 calculated_tax
2446 ,rl1.customer_trx_line_id inv_line_number
2447 ,r1.customer_trx_id header_trx_id
2448 FROM ra_customer_trx r1
2449 ,ra_customer_trx_lines rl1
2450 ,ra_customer_trx_lines rlt1
2451 ,ar_vat_tax v1
2452 ,jl_zz_ar_tx_att_cls tcl1
2453 ,jl_zz_ar_tx_categ tc1
2454 WHERE r1.customer_trx_id = rl1.customer_trx_id
2455 AND r1.previous_customer_trx_id = p_rel_cust_trx_id
2456 AND rl1.line_type = 'LINE'
2457 AND r1.customer_trx_id = rlt1.customer_trx_id
2458 AND rl1.customer_trx_line_id = rlt1.link_to_cust_trx_line_id
2459 AND rlt1.line_type= 'TAX'
2460 AND rlt1.vat_tax_id = v1.vat_tax_id
2461 AND v1.tax_type = 'VAT'
2462 AND v1.global_attribute1 = tc1.tax_category_id
2463 AND rl1.global_attribute3 = tcl1.tax_attr_class_code
2464 AND tcl1.tax_category_id = tc1.tax_category_id
2465 AND tcl1.tax_attr_class_type = 'TRANSACTION_CLASS'
2466 AND tc1.txn_tax_attribute = tcl1.tax_attribute_name
2467 AND p_trx_date <= tc1.end_date_active
2468 AND p_trx_date >= NVL(tc1.start_date_active,p_trx_date)
2469 UNION -- Credit Transactions of other related transaction for
2470 -- current related transactions
2471 SELECT tc1.tax_category_id tax_category_id
2472 ,tc1.threshold_check_grp_by grp_attr_name
2473 ,tcl1.tax_attribute_value grp_attr_value
2474 ,nvl(rlt1.taxable_amount,0) appl_prior_base
2475 ,fnd_number.canonical_to_number(nvl(rlt1.global_attribute19,0))
2476 charged_tax
2477 ,fnd_number.canonical_to_number(nvl(rlt1.global_attribute20,0))
2478 calculated_tax
2479 ,rl1.customer_trx_line_id inv_line_number
2480 ,r1.customer_trx_id header_trx_id
2481 FROM ra_customer_trx r1
2482 ,ra_customer_trx r2
2483 ,ra_customer_trx_lines rl1
2484 ,ra_customer_trx_lines rlt1
2485 ,ar_vat_tax v1
2486 ,jl_zz_ar_tx_att_cls tcl1
2487 ,jl_zz_ar_tx_categ tc1
2488 WHERE r2.related_customer_trx_id = p_rel_cust_trx_id
2489 AND r2.customer_trx_id = r1.previous_customer_trx_id
2490 AND r1.customer_trx_id = rl1.customer_trx_id
2491 AND rl1.line_type = 'LINE'
2492 AND r1.customer_trx_id = rlt1.customer_trx_id
2493 AND rl1.customer_trx_line_id = rlt1.link_to_cust_trx_line_id
2494 AND rlt1.line_type= 'TAX'
2495 AND rlt1.vat_tax_id = v1.vat_tax_id
2496 AND v1.tax_type = 'VAT'
2497 AND v1.global_attribute1 = tc1.tax_category_id
2498 AND rl1.global_attribute3 = tcl1.tax_attr_class_code
2499 AND tcl1.tax_category_id = tc1.tax_category_id
2500 AND tcl1.tax_attr_class_type = 'TRANSACTION_CLASS'
2501 AND tc1.txn_tax_attribute = tcl1.tax_attribute_name
2502 AND p_trx_date <= tc1.end_date_active
2503 AND p_trx_date >= NVL(tc1.start_date_active,p_trx_date)
2504 ORDER BY 7, 6, 1;
2505
2506 -- Bugfix 1388703
2507 CURSOR curr_tax_lines IS
2508 SELECT tc1.tax_category_id tax_category_id
2509 ,tc1.threshold_check_grp_by grp_attr_name
2510 ,tcl1.tax_attribute_value grp_attr_value
2511 ,nvl(rlt1.global_attribute11,0) appl_prior_base
2512 ,nvl(rlt1.global_attribute19,0) charged_tax
2513 ,nvl(rlt1.global_attribute20,0) calculated_tax
2514 ,rl1.customer_trx_line_id inv_line_number
2515 ,r1.customer_trx_id header_trx_id
2516 FROM ra_customer_trx r1
2517 ,ra_customer_trx_lines rl1
2518 ,ra_customer_trx_lines rlt1
2519 ,ar_vat_tax v1
2520 ,jl_zz_ar_tx_att_cls tcl1
2521 ,jl_zz_ar_tx_categ tc1
2522 WHERE r1.customer_trx_id = rl1.customer_trx_id
2523 AND r1.customer_trx_id = p_curr_cust_trx_id
2524 AND rl1.line_type = 'LINE'
2525 AND r1.customer_trx_id = rlt1.customer_trx_id
2526 AND rl1.customer_trx_line_id = rlt1.link_to_cust_trx_line_id
2527 AND rlt1.line_type= 'TAX'
2528 AND rlt1.vat_tax_id = v1.vat_tax_id
2529 AND p_trx_date >= nvl(v1.start_date,to_date( '01011900', 'ddmmyyyy'))
2530 AND p_trx_date <= nvl(v1.end_date,p_trx_date)
2531 AND v1.tax_type = 'VAT'
2532 AND v1.global_attribute1 = tc1.tax_category_id
2533 AND rl1.global_attribute3 = tcl1.tax_attr_class_code
2534 AND tcl1.tax_category_id = tc1.tax_category_id
2535 AND tcl1.tax_attr_class_type = 'TRANSACTION_CLASS'
2536 AND tc1.txn_tax_attribute = tcl1.tax_attribute_name
2537 AND p_trx_date <= tc1.end_date_active
2538 AND p_trx_date >= NVL(tc1.start_date_active,p_trx_date)
2539 ORDER BY 7, 6, 1;
2540
2541 cursor rel_trx_categ is
2542 SELECT tc1.tax_category_id tax_category_id
2543 FROM ra_customer_trx_lines rlt1
2544 ,ar_vat_tax v1
2545 ,jl_zz_ar_tx_categ tc1
2546 WHERE rlt1.customer_trx_id = p_rel_cust_trx_id
2547 AND rlt1.line_type= 'TAX'
2548 AND rlt1.vat_tax_id = v1.vat_tax_id
2549 AND v1.tax_type = 'VAT'
2550 AND v1.global_attribute1 = tc1.tax_category_id
2551 AND tc1.threshold_check_grp_by = 'DOCUMENT';
2552
2553 cursor cur_trx_categ is
2554 SELECT tc1.tax_category_id tax_category_id
2555 FROM ra_customer_trx_lines rlt1
2556 ,ar_vat_tax v1
2557 ,jl_zz_ar_tx_categ tc1
2558 WHERE rlt1.customer_trx_id = p_curr_cust_trx_id
2559 AND rlt1.line_type= 'TAX'
2560 AND rlt1.vat_tax_id = v1.vat_tax_id
2561 AND v1.tax_type = 'VAT'
2562 AND v1.global_attribute1 = tc1.tax_category_id
2563 AND tc1.threshold_check_grp_by = 'DOCUMENT';
2564
2565
2566
2567 l_jlzz_ar_tx_use_whole_operatn ar_system_parameters_all.global_attribute19%type;
2568
2569 BEGIN
2570 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2571
2572 IF (g_level_statement >= g_current_runtime_level) THEN
2573 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','jl_zz_tax.populate_plsql_tables()+');
2574 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','populate_plsql_tables: ' || '-P- Related Customer Trx Id: '||to_char(p_rel_cust_trx_id));
2575 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','populate_plsql_tables: ' || '-P- Previous Customer Trx Id: '||to_char(p_prev_cust_trx_id));
2576 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','populate_plsql_tables: ' || '-P- Current Customer Trx Id: '||to_char(p_curr_cust_trx_id));
2577 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','-P- Trx Date: '||to_char(p_trx_date,'DD-MM-YYYY'));
2578 END IF;
2579
2580 g_rel_tax_line_amounts.DELETE;
2581 g_rel_trx_categ.DELETE;
2582
2583 l_jlzz_ar_tx_use_whole_operatn :=
2584 JL_ZZ_SYS_OPTIONS_PKG.get_ar_tx_use_whole_operation(mo_global.get_current_org_id);
2585
2586 IF NVL(l_jlzz_ar_tx_use_whole_operatn,'N') = 'Y' THEN
2587
2588 IF (g_level_statement >= g_current_runtime_level) THEN
2589 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','populate_plsql_tables: ' || '-- Use Whole Operation ');
2590 END IF;
2591
2592 l_rel_tax_lines_ctr := 1;
2593 l_rel_tax_line_amount_ctr := 1;
2594 l_rel_trx_categ_ctr := 1;
2595
2596 IF p_rel_cust_trx_id IS NOT NULL THEN
2597
2598 FOR rel_tax_lines_rec IN rel_tax_lines
2599 LOOP
2600
2601 IF (g_level_statement >= g_current_runtime_level) THEN
2602 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','populate_plsql_tables: ' || '-- Rel tax line categ: ' ||
2603 to_char(rel_tax_lines_rec.tax_category_id));
2604 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','populate_plsql_tables: ' || '-- Rel tax line grouping attribute name: ' ||
2605 rel_tax_lines_rec.grp_attr_name);
2606 END IF;
2607
2608 g_rel_tax_line_amounts(l_rel_tax_line_amount_ctr).TaxCateg :=
2609 rel_tax_lines_rec.tax_category_id;
2610 g_rel_tax_line_amounts(l_rel_tax_line_amount_ctr).GrpAttrName :=
2611 rel_tax_lines_rec.grp_attr_name;
2612 g_rel_tax_line_amounts(l_rel_tax_line_amount_ctr).GrpAttrValue :=
2613 rel_tax_lines_rec.grp_attr_value;
2614 g_rel_tax_line_amounts(l_rel_tax_line_amount_ctr).ApplPriorBase :=
2615 rel_tax_lines_rec.appl_prior_base;
2616 g_rel_tax_line_amounts(l_rel_tax_line_amount_ctr).ChargedTax :=
2617 rel_tax_lines_rec.charged_tax;
2618 g_rel_tax_line_amounts(l_rel_tax_line_amount_ctr).CalcltdTax :=
2619 rel_tax_lines_rec.calculated_tax;
2620
2621 l_rel_tax_line_amount_ctr := l_rel_tax_line_amount_ctr + 1;
2622 g_prev_cust_trx_line_number := rel_tax_lines_rec.inv_line_number;
2623 g_prev_invoice_line_number := rel_tax_lines_rec.inv_line_number;
2624 g_prev_header_id := rel_tax_lines_rec.header_trx_id;
2625
2626 END LOOP;
2627
2628 FOR rel_trx_categ_rec IN rel_trx_categ
2629 LOOP
2630
2631 IF (g_level_statement >= g_current_runtime_level) THEN
2632 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','populate_plsql_tables: ' || '-- Rel tax line categ: ' ||
2633 to_char(rel_trx_categ_rec.tax_category_id));
2634 END IF;
2635
2636 g_rel_trx_categ(rel_trx_categ_rec.tax_category_id).ExistFlag := 'Y';
2637
2638 END LOOP;
2639
2640 END IF;
2641
2642 IF p_prev_cust_trx_id IS NOT NULL THEN
2643
2644 FOR prev_tax_lines_rec IN prev_tax_lines
2645 LOOP
2646
2647 IF (g_level_statement >= g_current_runtime_level) THEN
2648 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','populate_plsql_tables: ' || '-- Prev tax line categ: ' ||
2649 to_char(prev_tax_lines_rec.tax_category_id));
2650 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','populate_plsql_tables: ' || '-- Prev tax line grouping attribute name: ' ||
2651 prev_tax_lines_rec.grp_attr_name);
2652 END IF;
2653
2654 g_rel_tax_line_amounts(l_rel_tax_line_amount_ctr).TaxCateg :=
2655 prev_tax_lines_rec.tax_category_id;
2656 g_rel_tax_line_amounts(l_rel_tax_line_amount_ctr).GrpAttrName :=
2657 prev_tax_lines_rec.grp_attr_name;
2658 g_rel_tax_line_amounts(l_rel_tax_line_amount_ctr).GrpAttrValue :=
2659 prev_tax_lines_rec.grp_attr_value;
2660 g_rel_tax_line_amounts(l_rel_tax_line_amount_ctr).ApplPriorBase :=
2661 prev_tax_lines_rec.appl_prior_base;
2662 g_rel_tax_line_amounts(l_rel_tax_line_amount_ctr).ChargedTax :=
2663 prev_tax_lines_rec.charged_tax;
2664 g_rel_tax_line_amounts(l_rel_tax_line_amount_ctr).CalcltdTax :=
2665 prev_tax_lines_rec.calculated_tax;
2666
2667 l_rel_tax_line_amount_ctr := l_rel_tax_line_amount_ctr + 1;
2668 g_prev_cust_trx_line_number := prev_tax_lines_rec.inv_line_number;
2669 g_prev_invoice_line_number := prev_tax_lines_rec.inv_line_number;
2670 g_prev_header_id := prev_tax_lines_rec.header_trx_id;
2671
2672 END LOOP;
2673 END IF;
2674
2675 IF (g_level_statement >= g_current_runtime_level) THEN
2676 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','-- Related Tax lines total entries: ' ||
2677 to_char( l_rel_tax_lines_ctr - 1));
2678 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','-- Related Tax line amounts total entries: ' ||
2679 to_char( l_rel_tax_line_amount_ctr - 1));
2680 END IF;
2681
2682 FOR curr_tax_lines_rec IN curr_tax_lines
2683 LOOP
2684
2685 IF (g_level_statement >= g_current_runtime_level) THEN
2686 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','populate_plsql_tables: ' || '-- Curr tax line categ: ' ||
2687 to_char(curr_tax_lines_rec.tax_category_id));
2688 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','populate_plsql_tables: ' || '-- Curr tax line grouping attribute name: ' ||
2689 curr_tax_lines_rec.grp_attr_name);
2690 END IF;
2691
2692 g_rel_tax_line_amounts(l_rel_tax_line_amount_ctr).TaxCateg :=
2693 curr_tax_lines_rec.tax_category_id;
2694 g_rel_tax_line_amounts(l_rel_tax_line_amount_ctr).GrpAttrName :=
2695 curr_tax_lines_rec.grp_attr_name;
2696 g_rel_tax_line_amounts(l_rel_tax_line_amount_ctr).GrpAttrValue :=
2697 curr_tax_lines_rec.grp_attr_value;
2698 g_rel_tax_line_amounts(l_rel_tax_line_amount_ctr).ApplPriorBase :=
2699 curr_tax_lines_rec.appl_prior_base;
2700 g_rel_tax_line_amounts(l_rel_tax_line_amount_ctr).ChargedTax :=
2701 curr_tax_lines_rec.charged_tax;
2702 g_rel_tax_line_amounts(l_rel_tax_line_amount_ctr).CalcltdTax :=
2703 curr_tax_lines_rec.calculated_tax;
2704
2705 l_rel_tax_line_amount_ctr := l_rel_tax_line_amount_ctr + 1;
2706 g_prev_cust_trx_line_number := curr_tax_lines_rec.inv_line_number;
2707 g_prev_invoice_line_number := curr_tax_lines_rec.inv_line_number;
2708 g_prev_header_id := curr_tax_lines_rec.header_trx_id;
2709
2710 END LOOP;
2711
2712 FOR cur_trx_categ_rec IN cur_trx_categ
2713 LOOP
2714
2715 IF (g_level_statement >= g_current_runtime_level) THEN
2716 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','populate_plsql_tables: ' || '-- cur tax line categ: ' ||
2717 to_char(cur_trx_categ_rec.tax_category_id));
2718 END IF;
2719
2720 g_rel_trx_categ(cur_trx_categ_rec.tax_category_id).ExistFlag := 'Y';
2721
2722 END LOOP;
2723
2724 IF (g_level_statement >= g_current_runtime_level) THEN
2725 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','-- Related Tax lines total entries: ' ||
2726 to_char( l_rel_trx_categ_ctr - 1));
2727 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','-- Related Tax line amounts total entries: ' ||
2728 to_char( l_rel_tax_line_amount_ctr - 1));
2729 END IF;
2730
2731 END IF;
2732
2733 IF (g_level_statement >= g_current_runtime_level) THEN
2734 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','jl_zz_tax.populate_plsql_tables()-');
2735 END IF;
2736
2737 END populate_plsql_tables;
2738
2739 FUNCTION get_functional_curr_amount(p_amount IN NUMBER,
2740 p_exchange_rate IN NUMBER) RETURN NUMBER IS
2741 l_functional_amount NUMBER;
2742 BEGIN
2743
2744 l_functional_amount := ZX_PRODUCT_INTEGRATION_PKG.tax_curr_round (
2745 p_amount * p_exchange_rate,
2746 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.trx_currency_code,
2747 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.precision,
2748 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.minimum_accountable_unit,
2749 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_rounding_rule,
2750 'Y');
2751
2752 RETURN l_functional_amount;
2753
2754 END get_functional_curr_amount;
2755
2756 --========================================================================
2757 -- PRIVATE PROCEDURE
2758 -- calculate_tax_amount
2759 --
2760 -- DESCRIPTION
2761 -- The procedure calculates the tax amount from a given tax rate.
2762 --
2763 -- CALLED FROM
2764 -- jl_zz_tax.calculate_latin_tax
2765 --
2766 -- HISTORY
2767 -- 25-SEP-98 Harsh Takle Created
2768 --
2769 --========================================================================
2770 -- Bugfix 1388703
2771 PROCEDURE calculate_tax_amount (p_transaction_nature IN VARCHAR2,
2772 p_transaction_nature_class IN VARCHAR2,
2773 p_organization_class IN VARCHAR2,
2774 p_base_amount IN NUMBER,
2775 p_tax_group IN NUMBER,
2776 p_tax_category_id IN NUMBER,
2777 p_trx_date IN DATE,
2778 p_rule_id IN NUMBER,
2779 p_ship_to_site_use_id IN NUMBER,
2780 p_bill_to_site_use_id IN NUMBER,
2781 p_establishment_type IN VARCHAR2,
2782 p_contributor_type IN VARCHAR2,
2783 p_customer_trx_id IN NUMBER,
2784 p_customer_trx_line_id IN NUMBER,
2785 p_related_customer_trx_id IN NUMBER,
2786 p_previous_customer_trx_id IN NUMBER,
2787 p_location_id IN NUMBER,
2788 p_contributor_class IN VARCHAR2,
2789 p_set_of_books_id IN NUMBER,
2790 p_latin_return_code IN OUT NOCOPY VARCHAR,
2791 p_tax_amount IN OUT NOCOPY NUMBER,
2792 p_tax_rate IN OUT NOCOPY NUMBER,
2793 p_taxable_basis IN OUT NOCOPY VARCHAR2,
2794 p_calculated_tax_amount IN OUT NOCOPY NUMBER,
2795 p_exchange_rate IN NUMBER) IS
2796
2797 l_rel_tax_line_amt_ctr BINARY_INTEGER;
2798 l_rel_tax_lines_ctr BINARY_INTEGER;
2799 l_rule_code jl_zz_ar_tx_rules.rule%type;
2800 l_tax_code ar_vat_tax.tax_code%type;
2801 l_grp_attr_name VARCHAR2(30);
2802 l_grp_attr_val VARCHAR2(30);
2803 l_operation_level VARCHAR2(30);
2804 l_func_curr_taxable_base NUMBER;
2805 l_min_taxable_base NUMBER;
2806 l_min_tax_amount NUMBER;
2807 l_min_tax_rate NUMBER;
2808 l_applicable_prior_base NUMBER;
2809 l_tot_calculated_tax_amt NUMBER;
2810 l_taxable_base NUMBER;
2811 l_sch_tax_rate NUMBER;
2812 l_sch_taxable_basis VARCHAR2(30);
2813 l_sch_tax_amount NUMBER;
2814 l_charged_tax_amount NUMBER;
2815 l_return_status VARCHAR2(20);
2816 l_error_message VARCHAR2(250);
2817 ERROR_FROM_FUNCTION EXCEPTION;
2818 err_num NUMBER;
2819 err_msg NUMBER;
2820 l_tax_category_match_flag VARCHAR2(1);
2821 l_rel_trx_categ_ctr NUMBER;
2822
2823 CURSOR tax_schedule IS
2824 SELECT tax_code,
2825 min_taxable_basis,
2826 max_taxable_basis
2827 FROM jl_zz_ar_tx_schedules
2828 WHERE tax_category_id = p_tax_category_id
2829 AND p_trx_date BETWEEN NVL(start_date_active,p_trx_date)
2830 AND end_date_active;
2831
2832
2833
2834 BEGIN
2835 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2836 IF (g_level_statement >= g_current_runtime_level) THEN
2837 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','jl_zz_tax.calculate_tax_amount()+');
2838 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-P- Trx Nature: '|| p_transaction_nature);
2839 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-P- Trx Nature Class: '||p_transaction_nature_class);
2840 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-P- Organization Class: '||p_organization_class);
2841 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-P- Base Amount: ' ||to_char(p_base_amount));
2842 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-P- Tax Group: ' ||to_char(p_tax_group));
2843 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-P- Tax category: '||to_char(p_tax_category_id));
2844 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','-P- Trx date: ' ||to_char(p_trx_date,'DD-MM-YYYY'));
2845 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-P- Rule Id: ' ||to_char(p_rule_id));
2846 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-P- Ship to Site: '||to_char(p_ship_to_site_use_id));
2847 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-P- Bill to site: '||to_char(p_bill_to_site_use_id) );
2848 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-P- Establishment Type: '|| p_establishment_type);
2849 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-P- Contributor Type: '|| p_contributor_type);
2850 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-P- Customer Trx Id: '||to_char(p_customer_trx_id));
2851 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-P- Customer Trx Line Id: '||to_char(p_customer_trx_line_id));
2852 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-P- Related customer Trx Id: '||
2853 to_char(p_related_customer_trx_id));
2854 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-P- Previous Customer Trx Id: '||
2855 to_char(p_previous_customer_trx_id));
2856 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-P- Location Id: '||to_char(p_location_id));
2857 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-P- Contributor Class: '||p_contributor_class);
2858 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-P- Set Of Books Id: '||to_char(p_set_of_books_id));
2859 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-P- Calculated Tax Amount: '||
2860 to_char(p_calculated_tax_amount));
2861 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-P- Tax Amount: ' ||to_char(p_tax_amount));
2862 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-P- Tax Rate: ' ||to_char(p_tax_rate));
2863 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-P- Exchange Rate: ' ||to_char(p_exchange_rate));
2864 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-P- Last Header Id: '||to_char(g_prev_header_id));
2865 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-P- Prev invoice line number: '||
2866 to_char(g_prev_invoice_line_number));
2867 END IF;
2868
2869 g_first_tax_line := FALSE;
2870 p_latin_return_code := 'SUCCESS';
2871 IF p_customer_trx_id <> NVL(g_prev_header_id,0) OR
2872 g_prev_invoice_line_number IS NULL OR
2873 p_customer_trx_line_id < g_prev_invoice_line_number OR
2874 (g_first_processed_invoice_line = p_customer_trx_line_id AND
2875 g_first_processed_category_id = p_tax_category_id) THEN
2876
2877 g_first_tax_line := TRUE;
2878
2879 populate_plsql_tables(p_related_customer_trx_id,
2880 p_previous_customer_trx_id,
2881 p_customer_trx_id,
2882 p_trx_date);
2883
2884 g_first_processed_invoice_line := p_customer_trx_line_id;
2885 g_first_processed_category_id := p_tax_category_id;
2886
2887 IF (g_level_statement >= g_current_runtime_level) THEN
2888 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-- Last customer trx line number: '||
2889 to_char(g_prev_cust_trx_line_number));
2890 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-- Last invoice line number: ' ||
2891 to_char(g_prev_invoice_line_number));
2892 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-- Last header id: '||to_char(g_prev_header_id));
2893 END IF;
2894 END IF;
2895
2896 IF (g_level_statement >= g_current_runtime_level) THEN
2897 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-- First Processed line: '||
2898 to_char(g_first_processed_invoice_line));
2899 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-- First Processed category id: '||
2900 to_char(g_first_processed_category_id));
2901 END IF;
2902
2903 l_rule_code := NULL;
2904 -- Bugfix 1388703
2905 BEGIN
2906 SELECT rule
2907 INTO l_rule_code
2908 FROM jl_zz_ar_tx_rules
2909 WHERE rule_id = p_rule_id;
2910 EXCEPTION
2911 WHEN OTHERS THEN
2912 l_rule_code := NULL;
2913 END;
2914
2915 IF (g_level_statement >= g_current_runtime_level) THEN
2916 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-- Rule code: '||l_rule_code);
2917 END IF;
2918
2919 get_minimum_thresholds(p_tax_group, p_tax_category_id, p_trx_date,
2920 p_establishment_type, p_contributor_type,
2921 p_transaction_nature,
2922 p_transaction_nature_class,
2923 l_rule_code,
2924 l_min_tax_rate, l_min_tax_amount, l_min_taxable_base);
2925
2926 IF (g_level_statement >= g_current_runtime_level) THEN
2927 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-- Minimum Tax rate: '||to_char(l_min_tax_rate));
2928 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-- Minimum Tax Amount: '||to_char(l_min_tax_amount));
2929 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-- Minimum Taxable base: '||to_char(l_min_taxable_base));
2930 END IF;
2931
2932 l_grp_attr_name := Null;
2933 l_operation_level := Null;
2934 BEGIN
2935 SELECT threshold_check_grp_by,
2936 threshold_check_level
2937 INTO l_grp_attr_name,
2938 l_operation_level
2939 FROM jl_zz_ar_tx_categ
2940 WHERE tax_category_id = p_tax_category_id
2941 AND p_trx_date BETWEEN NVL(start_date_active,p_trx_date)
2942 AND end_date_active;
2943 EXCEPTION
2944 WHEN OTHERS THEN
2945 If (g_level_statement >= g_current_runtime_level) then
2946 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','Exception when getting threshold_check_level: '||SQLCODE||SQLERRM);
2947 END IF;
2948 l_grp_attr_name := Null;
2949 l_operation_level := Null;
2950 --++ nipatel added for LTE healthcheck testing
2951 -- l_grp_attr_name := 'LINE';
2952 -- l_operation_level := 'LINE';
2953 END;
2954
2955 IF (g_level_statement >= g_current_runtime_level) THEN
2956 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-- Grouping Attr Name: '||l_grp_attr_name);
2957 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-- Operation Level: '||l_operation_level);
2958 END IF;
2959
2960 IF NVL(l_operation_level,'LINE') <> 'LINE' AND p_taxable_basis = 'QUANTITY' THEN
2961 BEGIN
2962 select taxable_basis
2963 into p_taxable_basis
2964 from ar_vat_tax vt
2965 where decode(ltrim(vt.global_attribute1, '0123456789')
2966 , null, to_number(vt.global_attribute1)
2967 , null) = p_tax_category_id
2968 AND nvl(vt.enabled_flag,'Y') = 'Y'
2969 AND nvl(vt.tax_class,'O') = 'O'
2970 AND taxable_basis <> 'QUANTITY'
2971 AND p_trx_date >= vt.start_date
2972 AND p_trx_date <= nvl(vt.end_date,p_trx_date)
2973 AND rownum = 1;
2974
2975 fnd_message.set_name('AR', 'GENERIC_MESSAGE');
2976 fnd_message.set_token('GENERIC_TEXT', 'Could not expand tax group successfully');
2977 app_exception.raise_exception;
2978 EXCEPTION
2979 WHEN NO_DATA_FOUND THEN
2980 p_taxable_basis := 'QUANTITY';
2981 WHEN OTHERS THEN
2982 app_exception.raise_exception;
2983 END;
2984 END IF;
2985
2986 IF l_grp_attr_name = 'LINE' THEN
2987 l_grp_attr_val := 'LINE';
2988 ELSIF l_grp_attr_name = 'DOCUMENT' THEN
2989 l_grp_attr_val := 'DOCUMENT';
2990 ELSE
2991 BEGIN
2992 SELECT tax_attribute_value
2993 INTO l_grp_attr_val
2994 FROM jl_zz_ar_tx_att_cls ac
2995 WHERE tax_attr_class_type = 'TRANSACTION_CLASS'
2996 AND tax_attr_class_code = p_transaction_nature_class
2997 AND enabled_flag = 'Y'
2998 AND tax_category_id = p_tax_category_id
2999 AND tax_attribute_type = 'TRANSACTION_ATTRIBUTE'
3000 AND tax_attribute_name = l_grp_attr_name;
3001 EXCEPTION
3002 WHEN OTHERS THEN
3003 l_grp_attr_val := NULL;
3004 END;
3005 END IF;
3006
3007 IF (g_level_statement >= g_current_runtime_level) THEN
3008 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-- Grouping attribute Value: '||l_grp_attr_val);
3009 END IF;
3010
3011 l_return_status := validate_current_tax_line(
3012 p_related_customer_trx_id,
3013 p_customer_trx_id,
3014 p_customer_trx_line_id,
3015 l_operation_level,
3016 p_tax_group,
3017 p_tax_category_id,
3018 l_grp_attr_name,
3019 p_trx_date,
3020 nvl(p_ship_to_site_use_id,p_bill_to_site_use_id),
3021 p_organization_class,
3022 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf6,
3023 p_transaction_nature_class);
3024
3025 IF (g_level_statement >= g_current_runtime_level) THEN
3026 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-- validate current tax line return status: '||
3027 l_return_status);
3028 END IF;
3029
3030 IF l_return_status = 'ERROR' THEN
3031 RAISE ERROR_FROM_FUNCTION;
3032 END IF;
3033
3034 IF (g_level_statement >= g_current_runtime_level) THEN
3035 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','l_operation_level = '||l_operation_level);
3036 END IF;
3037
3038 IF l_operation_level = 'LINE' THEN
3039 l_applicable_prior_base := 0;
3040 l_charged_tax_amount := 0;
3041 l_tot_calculated_tax_amt := 0;
3042 ELSE
3043 get_prior_base(l_operation_level, p_tax_category_id,
3044 l_grp_attr_name, l_grp_attr_val,
3045 l_applicable_prior_base, l_charged_tax_amount,
3046 l_tot_calculated_tax_amt);
3047 END IF;
3048
3049 IF (g_level_statement >= g_current_runtime_level) THEN
3050 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-- Appl Prior Base: '||to_char(l_applicable_prior_base));
3051 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-- Charged Tax Amount: '||to_char(l_charged_tax_amount));
3052 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-- Total Calculated Tax Amount: '||
3053 to_char(l_tot_calculated_tax_amt));
3054 END IF;
3055 IF (g_level_statement >= g_current_runtime_level) THEN
3056 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.trx_currency_code = '||
3057 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.trx_currency_code);
3058 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.precision = '||
3059 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.precision);
3060 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.minimum_accountable_unit = '||
3061 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.minimum_accountable_unit);
3062 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_rounding_rule ='||
3063 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_rounding_rule);
3064 END IF;
3065
3066 IF p_taxable_basis = 'QUANTITY' THEN
3067 p_calculated_tax_amount := ZX_PRODUCT_INTEGRATION_PKG.tax_curr_round (
3068 p_base_amount * p_tax_rate,
3069 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.trx_currency_code,
3070 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.precision,
3071 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.minimum_accountable_unit,
3072 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_rounding_rule,
3073 'Y');
3074 ELSE
3075 p_calculated_tax_amount := ZX_PRODUCT_INTEGRATION_PKG.tax_curr_round (
3076 p_base_amount * (p_tax_rate / 100),
3077 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.trx_currency_code,
3078 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.precision,
3079 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.minimum_accountable_unit,
3080 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_rounding_rule,
3081 'Y');
3082 END IF;
3083
3084 p_tax_amount := 0;
3085
3086 IF (g_level_statement >= g_current_runtime_level) THEN
3087 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-- Current Calculated Tax Amount (rounded): '||
3088 to_char(p_calculated_tax_amount));
3089 END IF;
3090
3091 IF get_functional_curr_amount(abs(l_applicable_prior_base + p_base_amount),
3092 p_exchange_rate) >=
3093 nvl(l_min_taxable_base,0) THEN
3094 l_taxable_base := l_applicable_prior_base + p_base_amount;
3095
3096 IF (g_level_statement >= g_current_runtime_level) THEN
3097 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-- Taxable base: ' ||to_char(l_taxable_base));
3098 END IF;
3099
3100 l_func_curr_taxable_base :=
3101 get_functional_curr_amount(l_taxable_base,p_exchange_rate);
3102
3103 IF (g_level_statement >= g_current_runtime_level) THEN
3104 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-- Taxable base in functional currency: '||
3105 to_char(l_func_curr_taxable_base));
3106 END IF;
3107
3108 l_sch_tax_amount := 0;
3109 IF l_rule_code = 'GET_TX_SCH_TX_CODE' THEN
3110 p_tax_rate := 0;
3111 l_sch_tax_rate := NULL;
3112 l_sch_taxable_basis := NULL;
3113 FOR tax_schedule_rec IN tax_schedule
3114 LOOP
3115 l_sch_tax_rate := NULL;
3116 l_sch_taxable_basis := NULL;
3117 BEGIN
3118 SELECT tax_rate,taxable_basis
3119 INTO l_sch_tax_rate,l_sch_taxable_basis
3120 FROM ar_vat_tax
3121 WHERE tax_code = tax_schedule_rec.tax_code
3122 AND set_of_books_id = p_set_of_books_id
3123 AND start_date <= p_trx_date
3124 AND NVL(end_date,p_trx_date) >= p_trx_date
3125 AND nvl(enabled_flag,'Y') = 'Y'
3126 AND nvl(tax_class,'O') = 'O';
3127 EXCEPTION
3128 WHEN OTHERS THEN
3129 l_sch_tax_rate := 0;
3130 END;
3131 IF l_func_curr_taxable_base
3132 BETWEEN tax_schedule_rec.min_taxable_basis
3133 AND tax_schedule_rec.max_taxable_basis THEN
3134 p_tax_rate := l_sch_tax_rate;
3135 IF l_sch_taxable_basis = 'QUANTITY' THEN
3136 l_sch_tax_amount := l_sch_tax_amount
3137 + ((l_func_curr_taxable_base - tax_schedule_rec.min_taxable_basis) * l_sch_tax_rate);
3138 ELSE
3139 l_sch_tax_amount := l_sch_tax_amount + ((l_func_curr_taxable_base -
3140 tax_schedule_rec.min_taxable_basis) * (l_sch_tax_rate/100));
3141 END IF;
3142 ELSIF l_func_curr_taxable_base > tax_schedule_rec.max_taxable_basis
3143 THEN
3144 IF l_sch_taxable_basis = 'QUANTITY' THEN
3145 l_sch_tax_amount := l_sch_tax_amount +
3146 ((tax_schedule_rec.max_taxable_basis - tax_schedule_rec.min_taxable_basis) * l_sch_tax_rate);
3147 ELSE
3148 l_sch_tax_amount := l_sch_tax_amount +
3149 ((tax_schedule_rec.max_taxable_basis -
3150 tax_schedule_rec.min_taxable_basis) *
3151 (l_sch_tax_rate/100));
3152 END IF;
3153 END IF;
3154 IF (g_level_statement >= g_current_runtime_level) THEN
3155 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-- Tax Schedule Tax Rate: '||to_char(l_sch_tax_rate));
3156 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-- Tax Schedule Tax Amount: '||
3157 to_char(l_sch_tax_amount));
3158 END IF;
3159 END LOOP;
3160
3161 IF (g_level_statement >= g_current_runtime_level) THEN
3162 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-- Tax Schedule Tax Rate: '||to_char(p_tax_rate));
3163 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-- Tax Schedule Tax Amount in functional currency: '||
3164 to_char(l_sch_tax_amount));
3165 END IF;
3166
3167 p_calculated_tax_amount :=
3168 ZX_PRODUCT_INTEGRATION_PKG.tax_curr_round (
3169 (l_sch_tax_amount / p_exchange_rate),
3170 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.trx_currency_code,
3171 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.precision,
3172 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.minimum_accountable_unit,
3173 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_rounding_rule,
3174 'Y');
3175 END IF;
3176
3177 IF (g_level_statement >= g_current_runtime_level) THEN
3178 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-- Tax Rate after checking SCH method: '||
3179 to_char(p_tax_rate));
3180 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-- Taxable base: ' ||to_char(l_taxable_base));
3181 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-- Calculated Tax Amount (rounded): ' ||
3182 to_char(p_calculated_tax_amount));
3183 END IF;
3184
3185 IF l_min_tax_rate IS NOT NULL AND p_tax_rate < l_min_tax_rate THEN
3186 IF (g_level_statement >= g_current_runtime_level) THEN
3187 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-- Reverse charged tax : '||
3188 to_char(l_charged_tax_amount));
3189 END IF;
3190 p_tax_amount := l_charged_tax_amount * -1;
3191 ELSE
3192 IF get_functional_curr_amount(
3193 abs(p_calculated_tax_amount+l_tot_calculated_tax_amt),
3194 p_exchange_rate) >= nvl(l_min_tax_amount,0) THEN
3195
3196 IF (g_level_statement >= g_current_runtime_level) THEN
3197 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-- Calculated Tax Amount is greater than minimum'||
3198 ' tax amount');
3199 END IF;
3200
3201 p_tax_amount := p_calculated_tax_amount + l_tot_calculated_tax_amt
3202 - l_charged_tax_amount;
3203 ELSE
3204 IF (g_level_statement >= g_current_runtime_level) THEN
3205 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-- Reverse charged tax : '||
3206 to_char(l_charged_tax_amount));
3207 END IF;
3208 p_tax_amount := l_charged_tax_amount * -1;
3209 END IF;
3210 END IF;
3211
3212 ELSE
3213 IF (g_level_statement >= g_current_runtime_level) THEN
3214 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-- Reverse charged tax : '||
3215 to_char(l_charged_tax_amount));
3216 END IF;
3217 p_tax_amount := l_charged_tax_amount * -1;
3218 END IF;
3219
3220 IF l_rule_code = 'GET_TX_SCH_TX_CODE' THEN
3221 p_calculated_tax_amount := p_tax_amount;
3222 END IF;
3223
3224 IF (g_level_statement >= g_current_runtime_level) THEN
3225 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-- New Tax Amount: '||p_tax_amount);
3226 END IF;
3227
3228 IF NVL(l_operation_level,'LINE') <> 'LINE' THEN
3229 l_rel_tax_line_amt_ctr := g_rel_tax_line_amounts.COUNT + 1;
3230
3231 IF (g_level_statement >= g_current_runtime_level) THEN
3232 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-- Insert PLSQL rel tax line amounts table entry: '||
3233 to_char(l_rel_tax_line_amt_ctr));
3234 END IF;
3235
3236 g_rel_tax_line_amounts(l_rel_tax_line_amt_ctr).ApplPriorBase := p_base_amount;
3237 g_rel_tax_line_amounts(l_rel_tax_line_amt_ctr).ChargedTax := p_tax_amount;
3238 g_rel_tax_line_amounts(l_rel_tax_line_amt_ctr).CalcltdTax :=
3239 p_calculated_tax_amount;
3240 g_rel_tax_line_amounts(l_rel_tax_line_amt_ctr).TaxCateg := p_tax_category_id;
3241 g_rel_tax_line_amounts(l_rel_tax_line_amt_ctr).GrpAttrName := l_grp_attr_name;
3242 g_rel_tax_line_amounts(l_rel_tax_line_amt_ctr).GrpAttrValue := l_grp_attr_val;
3243
3244 IF l_grp_attr_name = 'DOCUMENT' THEN
3245
3246 g_rel_trx_categ(p_tax_category_id).ExistFlag := 'Y';
3247
3248 END IF;
3249 END IF;
3250
3251 IF g_prev_cust_trx_line_number <> p_customer_trx_line_id AND
3252 g_prev_invoice_line_number <> p_customer_trx_line_id AND
3253 g_prev_invoice_line_number IS NOT NULL THEN
3254
3255 g_prev_cust_trx_line_number := g_prev_invoice_line_number;
3256 END IF;
3257
3258 g_prev_header_id := p_customer_trx_id;
3259 g_prev_invoice_line_number := p_customer_trx_line_id;
3260
3261 IF (g_level_statement >= g_current_runtime_level) THEN
3262 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-- Last customer trx line number: '||
3263 to_char(g_prev_cust_trx_line_number));
3264 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-- Last invoice line number: ' ||
3265 to_char(g_prev_invoice_line_number));
3266 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-- Last header id: '||to_char(g_prev_header_id));
3267
3268 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-- p_tax_amount(out): '||p_tax_amount);
3269 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-- p_tax_rate(out): '||p_tax_rate);
3270 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_tax_amount: ' || '-- p_calculated_tax_amount(out): '||p_calculated_tax_amount);
3271
3272 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','jl_zz_tax.calculate_tax_amount()-');
3273
3274
3275
3276 END IF;
3277
3278 EXCEPTION
3279 WHEN ERROR_FROM_FUNCTION THEN
3280 FND_MESSAGE.SET_NAME('JL','JL_ZZ_AR_TX_INVALID_TAX_GROUP');
3281 p_latin_return_code := FND_MESSAGE.GET;
3282 ZX_TDS_CALC_SERVICES_PUB_PKG.g_msg_context_info_rec.trx_line_id :=
3283 p_customer_trx_line_id;
3284
3285 ZX_TDS_CALC_SERVICES_PUB_PKG.g_msg_context_info_rec.trx_level_type :=
3286 'LINE';
3287
3288 ZX_API_PUB.add_msg(
3289 ZX_TDS_CALC_SERVICES_PUB_PKG.g_msg_context_info_rec);
3290
3291
3292 WHEN OTHERS THEN
3293 err_num := SQLCODE;
3294 p_latin_return_code := SUBSTR(SQLERRM,1,100);
3295
3296 END calculate_tax_amount;
3297
3298 --========================================================================
3299 -- PRIVATE PROCEDURE
3300 -- calculate_latin_tax
3301 --
3302 -- DESCRIPTION
3303 -- This routine calculates the tax amount for the transaction line or
3304 -- sales order line that is recorded in the global structure
3305 -- ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec
3306 --
3307 -- PARAMETERS
3308 -- p_tax_category_id
3309 -- p_tax_rate
3310 -- p_base_rate
3311 -- p_base_amount (with NULL)
3312 --
3313 -- RETURNS
3314 -- p_base_amount
3315 -- o_aux_tax_amount
3316 -- o_latin_return_code
3317 --
3318 -- CALLED FROM
3319 -- jl_zz_tax.calculate
3320 --
3321 -- HISTORY
3322 -- 09-JAN-98 A.Chiromatzo bug #608814 replaced the occurrences of
3323 -- extended_amount to entered_amount (tax_info_rec)
3324 --
3325 --========================================================================
3326 PROCEDURE calculate_latin_tax (p_tax_category_id IN NUMBER,
3327 p_rule_id IN NUMBER,
3328 p_group_tax_id IN NUMBER,
3329 p_trx_date IN DATE,
3330 p_contributor_type IN VARCHAR2,
3331 p_transaction_nature IN VARCHAR2,
3332 p_establishment_type IN VARCHAR2,
3333 p_trx_type_id IN NUMBER,
3334 p_ship_to_site_use_id IN NUMBER,
3335 p_bill_to_site_use_id IN NUMBER,
3336 p_inventory_item_id IN NUMBER,
3337 p_memo_line_id IN NUMBER,
3338 p_ship_to_cust_id IN NUMBER,
3339 p_bill_to_cust_id IN NUMBER,
3340 p_application IN VARCHAR2,
3341 p_ship_from_warehouse_id IN NUMBER,
3342 p_fiscal_classification_code IN VARCHAR2,
3343 p_warehouse_location_id IN NUMBER,
3344 p_transaction_nature_class IN VARCHAR2,
3345 p_set_of_books_id IN NUMBER,
3346 p_location_structure_id IN NUMBER,
3347 p_location_segment_num IN VARCHAR2,
3348 p_entered_amount IN NUMBER,
3349 p_customer_trx_id IN NUMBER,
3350 p_customer_trx_line_id IN NUMBER,
3351 p_related_customer_trx_id IN NUMBER,
3352 p_previous_customer_trx_id IN NUMBER,
3353 p_contributor_class IN VARCHAR2,
3354 p_organization_class IN VARCHAR2,
3355 p_tax_rate IN OUT NOCOPY NUMBER,
3356 p_taxable_basis IN OUT NOCOPY VARCHAR2,
3357 p_base_rate IN NUMBER,
3358 p_base_amount IN OUT NOCOPY NUMBER,
3359 o_tax_amount IN OUT NOCOPY NUMBER,
3360 o_latin_return_code IN OUT NOCOPY VARCHAR,
3361 p_calculated_tax_amount IN OUT NOCOPY NUMBER,
3362 p_exchange_rate IN NUMBER) IS
3363
3364 v_tributary_substitution VARCHAR2(1);
3365 v_aux_transaction_nature VARCHAR2(30);
3366 v_aux_establishment_type VARCHAR2(30);
3367 v_aux_contributor_type VARCHAR2(30);
3368 l_rule_id NUMBER;
3369 l_rule_data_id NUMBER;
3370 l_tax_code VARCHAR(50);
3371 v_aux_tax_code VARCHAR(50);
3372 v_aux_latin_return_code VARCHAR2(30);
3373 v_calculate_return_code VARCHAR2(2000);
3374 v_tax_category_to_reduce_id NUMBER;
3375 v_aux_rule_id NUMBER;
3376 v_aux_rule_data_id NUMBER;
3377 v_aux_tax_rate NUMBER;
3378
3379 v_aux_taxable_basis VARCHAR2(30);
3380 l_entered_amount NUMBER;
3381
3382 v_aux_base_rate NUMBER;
3383 v_aux_base_amount NUMBER;
3384 v_aux_tax_amount NUMBER := NULL;
3385 v_aux_calculated_tax_amount NUMBER := NULL;
3386 v_location_structure_id NUMBER;
3387 v_location_segment_num NUMBER;
3388 v_set_of_books_id NUMBER;
3389 err_num NUMBER;
3390 err_msg NUMBER;
3391 ERROR_FROM_FUNCTION EXCEPTION;
3392 ERROR_FROM_CAL_TAX_AMT EXCEPTION;
3393
3394
3395
3396 BEGIN
3397 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3398 IF (g_level_statement >= g_current_runtime_level) THEN
3399 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','jl_zz_tax.calculate_latin_tax()+');
3400 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || '-P- Tax category passed: '||to_char(p_tax_category_id));
3401 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || '-P- Tax rate passed: '||to_char(p_tax_rate));
3402 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || '-P- Rule Id: '||to_char(p_rule_id));
3403 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || '-P- Tax base rate passed: '||to_char(p_base_rate));
3404 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || '-P- Tax base amount passed: '||to_char(p_base_amount));
3405 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || '-P- Tax amount passed: '||to_char(o_tax_amount));
3406 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || '-P- Tax group passed: '||to_char(p_group_tax_id));
3407 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || '-P- Transaction Type: '||to_char(p_trx_type_id));
3408 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || '-P- Inventory Item Id: '||to_char(p_inventory_item_id));
3409 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || '-P- Memo Line Id: '||to_char(p_memo_line_id));
3410 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || '-P- Ship To Cust Id: '||to_char(p_ship_to_cust_id));
3411 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || '-P- Bill To Cust Id: '||to_char(p_bill_to_cust_id));
3412 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || '-P- Application: '||p_application);
3413 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || '-P- Ship From Warehouse Id: '||
3414 to_char(p_ship_from_warehouse_id));
3415 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || '-P- Fiscal Classification Code: '||
3416 p_fiscal_classification_code);
3417 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || '-P- Warehouse Location Id: '||
3418 to_char(p_warehouse_location_id));
3419 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || '-P- Location Structure Id: '||
3420 to_char(p_location_structure_id));
3421 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || '-P- Location Segment Number: '||p_location_segment_num);
3422 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || '-P- Entered Amount: '||to_char(p_entered_amount));
3423 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || '-P- Trx Nature: '|| p_transaction_nature);
3424 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || '-P- Trx Nature Class: '||p_transaction_nature_class);
3425 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','-P- Trx date: ' ||to_char(p_trx_date,'DD-MM-YYYY'));
3426 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || '-P- Rule Id: ' ||to_char(p_rule_id));
3427 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || '-P- Ship to Site: '||to_char(p_ship_to_site_use_id));
3428 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || '-P- Bill to site: '||to_char(p_bill_to_site_use_id) );
3429 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || '-P- Establishment Type: '|| p_establishment_type);
3430 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || '-P- Contributor Type: '|| p_contributor_type);
3431 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || '-P- Customer Trx Id: '||to_char(p_customer_trx_id));
3432 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || '-P- Customer Trx Line Id: '||to_char(p_customer_trx_line_id));
3433 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || '-P- Related customer Trx Id: '||
3434 to_char(p_related_customer_trx_id));
3435 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || '-P- Previous Customer Trx Id: '||
3436 to_char(p_previous_customer_trx_id));
3437 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || '-P- Contributor Class: '||p_contributor_class);
3438 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || '-P- Organization Class: '||p_organization_class);
3439 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || '-P- Set Of Books Id: '||to_char(p_set_of_books_id));
3440 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || '-P- Exchange Rate: '||to_char(p_exchange_rate));
3441 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || '-P- Calculated Tax Amount: '||
3442 to_char(p_calculated_tax_amount));
3443 END IF;
3444
3445 -- Check if the tax code passed in the line is not a group
3446 IF p_group_tax_id IS NULL THEN
3447 -- get attributes from the Latin tax category to check
3448 -- tax_categ_to_reduce_id and Tributary Substitution
3449 -- Bugfix 1388703
3450 SELECT tc.tributary_substitution,
3451 tc.tax_categ_to_reduce_id,
3452 sp.location_structure_id,
3453 decode(ltrim(sp.global_attribute10, '0123456789'),
3454 null, to_number(sp.global_attribute10), null),
3455 sp.set_of_books_id
3456 INTO v_tributary_substitution,
3457 v_tax_category_to_reduce_id,
3458 v_location_structure_id,
3459 v_location_segment_num,
3460 v_set_of_books_id
3461 FROM jl_zz_ar_tx_categ tc,
3462 ar_system_parameters_all sp
3463 WHERE tc.tax_category_id = p_tax_category_id
3464 AND p_trx_date <= tc.end_date_active
3465 AND p_trx_date >= NVL(tc.start_date_active, p_trx_date)
3466 AND nvl(tc.org_id,-99) = nvl(sp.org_id,-99)
3467 AND tc.org_id = zx_product_integration_pkg.sysinfo.sysparam.org_id;
3468
3469 -- Tax code passed in the line is a group
3470 ELSE
3471 -- get attributes from the Latin tax group to check
3472 -- tax_category_to_reduce_id and Tributary Substitution
3473 -- Bugfix 1388703
3474 SELECT tg.tributary_substitution,
3475 tg.tax_category_to_reduce_id,
3476 sp.location_structure_id,
3477 decode(ltrim(sp.global_attribute10, '0123456789'),
3478 null, to_number(sp.global_attribute10), null),
3479 sp.set_of_books_id
3480 INTO v_tributary_substitution,
3481 v_tax_category_to_reduce_id,
3482 v_location_structure_id,
3483 v_location_segment_num,
3484 v_set_of_books_id
3485 FROM jl_zz_ar_tx_groups tg,
3486 ar_system_parameters_all sp
3487 WHERE tg.tax_category_id = p_tax_category_id
3488 AND tg.group_tax_id = p_group_tax_id
3489 AND tg.contributor_type = p_contributor_type
3490 AND tg.transaction_nature = p_transaction_nature
3491 AND tg.establishment_type = p_establishment_type
3492 AND p_trx_date <= tg.end_date_active
3493 AND p_trx_date >= NVL(tg.start_date_active, p_trx_date)
3494 AND nvl(tg.org_id,-99) = nvl(sp.org_id,-99)
3495 AND tg.org_id = zx_product_integration_pkg.sysinfo.sysparam.org_id;
3496
3497 END IF;
3498
3499 IF (g_level_statement >= g_current_runtime_level) THEN
3500 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || '-- Tributary Substitution: '|| v_tributary_substitution);
3501 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || '-- Auxiliary Set of Books Id: '||
3502 to_char(v_set_of_books_id));
3503 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || '-- Auxiliary Location Structure Id: '||
3504 to_char(v_location_structure_id));
3505 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || '-- Auxiliary Location Segment Number: '||
3506 to_char(v_location_segment_num));
3507 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || '-- Auxiliary tax category: '||
3508 to_char(v_tax_category_to_reduce_id));
3509 END IF;
3510
3511 -- check need to calculate the auxiliary tax category before
3512 IF v_tax_category_to_reduce_id IS NOT NULL THEN
3513
3514 v_aux_establishment_type := NULL;
3515 begin
3516 -- Bugfix 1388703
3517 select ac.tax_attribute_value
3518 into v_aux_establishment_type
3519 from jl_zz_ar_tx_att_cls ac
3520 where ac.tax_attr_class_code = p_organization_class
3521 and ac.tax_category_id = v_tax_category_to_reduce_id
3522 and ac.tax_attribute_type = 'ORGANIZATION_ATTRIBUTE'
3523 and ac.tax_attr_class_type = 'ORGANIZATION_CLASS'
3524 and ac.enabled_flag = 'Y'
3525 and exists (select 1
3526 from jl_zz_ar_tx_categ cat
3527 where cat.tax_category_id = ac.tax_category_id
3528 and ac.tax_attribute_name = cat.org_tax_attribute
3529 and p_trx_date <= cat.end_date_active
3530 and p_trx_date >= NVL(cat.start_date_active, p_trx_date));
3531 exception
3532 when others then
3533 v_aux_establishment_type := NULL;
3534 end;
3535
3536 IF (g_level_statement >= g_current_runtime_level) THEN
3537 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || '-- Auxiliary establishment type: '||
3538 v_aux_establishment_type);
3539 END IF;
3540
3541 v_aux_transaction_nature := NULL;
3542 begin
3543 -- Bugfix 1388703
3544 select ac.tax_attribute_value
3545 into v_aux_transaction_nature
3546 from jl_zz_ar_tx_att_cls ac
3547 where ac.tax_attr_class_code = p_transaction_nature_class
3548 and ac.tax_category_id = v_tax_category_to_reduce_id
3549 and ac.tax_attribute_type = 'TRANSACTION_ATTRIBUTE'
3550 and ac.tax_attr_class_type = 'TRANSACTION_CLASS'
3551 and ac.enabled_flag = 'Y'
3552 and exists (select 1
3553 from jl_zz_ar_tx_categ cat
3554 where cat.tax_category_id = ac.tax_category_id
3555 and ac.tax_attribute_name = cat.txn_tax_attribute
3556 and p_trx_date <= cat.end_date_active
3557 and p_trx_date >= NVL(cat.start_date_active, p_trx_date));
3558 exception
3559 when others then
3560 v_aux_transaction_nature := NULL;
3561 end;
3562
3563 IF (g_level_statement >= g_current_runtime_level) THEN
3564 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || '-- Auxiliary transaction nature: '||
3565 v_aux_transaction_nature);
3566 END IF;
3567
3568 v_aux_contributor_type := NULL;
3569 begin
3570 -- Bugfix 1388703
3571
3572 -- Bugfix 1783986. Added if and else conditions
3573 IF NVL(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf4,'N') = 'Y' THEN
3574 select ac.tax_attribute_value
3575 into v_aux_contributor_type
3576 from jl_zz_ar_tx_cus_cls ac,
3577 hz_cust_site_uses su,
3578 jl_zz_ar_tx_categ cat
3579 where ac.tax_attr_class_code = p_contributor_class
3580 and ac.tax_category_id = v_tax_category_to_reduce_id
3581 and ac.enabled_flag = 'Y'
3582 and cat.tax_category_id = ac.tax_category_id
3583 and ac.tax_attribute_name = cat.cus_tax_attribute
3584 and su.cust_acct_site_id = ac.address_id
3585 and su.site_use_id =
3586 NVL(p_ship_to_site_use_id, p_bill_to_site_use_id)
3587 and p_trx_date <= cat.end_date_active
3588 and p_trx_date >= NVL(cat.start_date_active, p_trx_date);
3589 ELSE
3590 select ac.tax_attribute_value
3591 into v_aux_contributor_type
3592 from jl_zz_ar_tx_att_cls ac,
3593 jl_zz_ar_tx_categ cat
3594 where ac.tax_attr_class_code = p_contributor_class
3595 and ac.tax_category_id = v_tax_category_to_reduce_id
3596 and ac.enabled_flag = 'Y'
3597 and ac.tax_attr_class_type = 'CONTRIBUTOR_CLASS'
3598 and cat.tax_category_id = ac.tax_category_id
3599 and ac.tax_attribute_type = 'CONTRIBUTOR_ATTRIBUTE'
3600 and ac.tax_attribute_name = cat.cus_tax_attribute
3601 and p_trx_date <= cat.end_date_active
3602 and p_trx_date >= NVL(cat.start_date_active, p_trx_date);
3603 END IF;
3604
3605 exception
3606 when others then
3607 v_aux_contributor_type := NULL;
3608 end;
3609
3610 IF (g_level_statement >= g_current_runtime_level) THEN
3611 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || '-- Auxiliary contributor type: '||v_aux_contributor_type);
3612 END IF;
3613
3614 -- get tax code, base rate, tax rate for the auxiliary tax category
3615
3616 v_aux_rule_data_id := NULL;
3617 v_aux_rule_id := NULL;
3618 v_aux_tax_code := NULL;
3619 IF (g_level_statement >= g_current_runtime_level) then
3620 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','Calling get_category_tax_rule');
3621 END If;
3622
3623 get_category_tax_rule (v_tax_category_to_reduce_id,
3624 p_trx_type_id,
3625 p_ship_to_site_use_id,
3626 p_bill_to_site_use_id,
3627 p_inventory_item_id,
3628 p_group_tax_id,
3629 p_memo_line_id,
3630 p_ship_to_cust_id,
3631 p_bill_to_cust_id,
3632 p_trx_date,
3633 p_application,
3634 p_ship_from_warehouse_id,
3635 'RATE',
3636 p_fiscal_classification_code,
3637 p_ship_from_warehouse_id,
3638 v_location_structure_id,
3639 v_location_segment_num,
3640 v_set_of_books_id,
3641 v_aux_transaction_nature,
3642 p_base_amount,
3643 v_aux_establishment_type,
3644 v_aux_contributor_type,
3645 p_warehouse_location_id,
3646 p_transaction_nature_class,
3647 v_aux_tax_code,
3648 v_aux_base_rate,
3649 v_aux_rule_data_id,
3650 v_aux_rule_id);
3651
3652 IF v_aux_tax_code IS NULL THEN
3653 v_aux_tax_code := 'NO_VALID_TAX_CODE';
3654 RAISE ERROR_FROM_FUNCTION;
3655 ELSE
3656 SELECT vt.tax_rate,vt.taxable_basis
3657 INTO v_aux_tax_rate,v_aux_taxable_basis
3658 FROM ar_vat_tax vt
3659 WHERE vt.tax_code = v_aux_tax_code
3660 AND vt.set_of_books_id = v_set_of_books_id
3661 AND vt.start_date <= trunc(p_trx_date)
3662 AND nvl(vt.end_date, trunc(p_trx_date)) >= trunc(p_trx_date)
3663 AND nvl(vt.enabled_flag,'Y') = 'Y'
3664 AND nvl(vt.tax_class,'O') = 'O';
3665 END IF;
3666
3667 IF (g_level_statement >= g_current_runtime_level) THEN
3668 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || '-- Auxiliary tax code: '||v_aux_tax_code);
3669 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || '-- Auxiliary rule id: '||to_char(v_aux_rule_id));
3670 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || '-- Auxiliary tax rate: '||to_char(v_aux_tax_rate));
3671 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || '-- Auxiliary taxable basis: '||to_char(v_aux_taxable_basis));
3672 END IF;
3673
3674 IF v_aux_taxable_basis = 'QUANTITY' THEN
3675 l_entered_amount := ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.taxed_quantity;
3676 ELSE
3677 l_entered_amount := ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.entered_amount;
3678 END IF;
3679
3680 v_aux_base_rate := NULL;
3681
3682 IF (g_level_statement >= g_current_runtime_level) then
3683 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','Calling get_category_tax_rule for v_tax_category_to_reduce_id:'
3684 ||v_tax_category_to_reduce_id);
3685 END IF;
3686 get_category_tax_rule (v_tax_category_to_reduce_id,
3687 p_trx_type_id,
3688 p_ship_to_site_use_id,
3689 p_bill_to_site_use_id,
3690 p_inventory_item_id,
3691 p_group_tax_id,
3692 p_memo_line_id,
3693 p_ship_to_cust_id,
3694 p_bill_to_cust_id,
3695 p_trx_date,
3696 p_application,
3697 p_ship_from_warehouse_id,
3698 'BASE',
3699 p_fiscal_classification_code,
3700 p_ship_from_warehouse_id,
3701 v_location_structure_id,
3702 v_location_segment_num,
3703 v_set_of_books_id,
3704 v_aux_transaction_nature,
3705 p_base_amount,
3706 v_aux_establishment_type,
3707 v_aux_contributor_type,
3708 p_warehouse_location_id,
3709 p_transaction_nature_class,
3710 l_tax_code,
3711 v_aux_base_rate,
3712 l_rule_data_id,
3713 l_rule_id);
3714
3715 IF l_tax_code IS NULL THEN
3716 RAISE ERROR_FROM_FUNCTION;
3717 end if;
3718
3719 IF (g_level_statement >= g_current_runtime_level) THEN
3720 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || '-- Auxiliary base rate: '||to_char(v_aux_base_rate));
3721 END IF;
3722
3723 -- calculate the auxiliary tax category
3724 calculate_latin_tax (v_tax_category_to_reduce_id,
3725 v_aux_rule_id,
3726 p_group_tax_id,
3727 p_trx_date,
3728 v_aux_contributor_type,
3729 v_aux_transaction_nature,
3730 v_aux_establishment_type,
3731 p_trx_type_id,
3732 p_ship_to_site_use_id,
3733 p_bill_to_site_use_id,
3734 p_inventory_item_id,
3735 p_memo_line_id,
3736 p_ship_to_cust_id,
3737 p_bill_to_cust_id,
3738 p_application,
3739 p_ship_from_warehouse_id,
3740 p_fiscal_classification_code,
3741 p_warehouse_location_id,
3742 p_transaction_nature_class,
3743 p_set_of_books_id,
3744 p_location_structure_id,
3745 p_location_segment_num,
3746 l_entered_amount,
3747 p_customer_trx_id,
3748 p_customer_trx_line_id,
3749 p_related_customer_trx_id,
3750 p_previous_customer_trx_id,
3751 p_contributor_class,
3752 p_organization_class,
3753 v_aux_tax_rate,
3754 v_aux_taxable_basis,
3755 v_aux_base_rate,
3756 v_aux_base_amount,
3757 v_aux_tax_amount,
3758 v_aux_latin_return_code,
3759 v_aux_calculated_tax_amount,
3760 p_exchange_rate);
3761
3762 IF v_aux_latin_return_code <> ZX_PRODUCT_INTEGRATION_PKG.TAX_SUCCESS THEN
3763 RAISE ERROR_FROM_FUNCTION;
3764 END IF;
3765
3766 -- add the tax amount to the base amount if needed
3767 IF v_tributary_substitution = 'N' THEN
3768 p_base_amount := v_aux_tax_amount;
3769 ELSE
3770 -- base amount used in the tax category to compound base
3771 p_base_amount := v_aux_base_amount - p_entered_amount;
3772 END IF;
3773
3774 p_calculated_tax_amount := v_aux_calculated_tax_amount;
3775
3776 IF (g_level_statement >= g_current_runtime_level) THEN
3777 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || '-- Auxiliary tributary substitution: '||
3778 v_tributary_substitution);
3779 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || '-- Auxiliary base amount: '||to_char(p_base_amount));
3780 END IF;
3781
3782 END IF; -- end of steps for the auxiliary tax category
3783
3784 -- get base amount
3785 IF (g_level_statement >= g_current_runtime_level) THEN
3786 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || '-- Base amount before apply base rate: '||
3787 to_char(p_base_amount));
3788 END IF;
3789
3790 -- CR 3571797. Assign Unrounded Taxable Amount in tax_info_rec for E-Business Tax
3791 -- requirement on LTE Tax Lines to simplify migration process of LTE.
3792 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.unrounded_taxable_amt := (nvl(p_base_amount,0) + p_entered_amount)
3793 * (1 + (nvl(p_base_rate,0)/100));
3794
3795 p_base_amount := ZX_PRODUCT_INTEGRATION_PKG.tax_curr_round (
3796 (nvl(p_base_amount,0) + p_entered_amount)
3797 * (1 + (nvl(p_base_rate,0)/100)),
3798 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.trx_currency_code,
3799 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.precision,
3800 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.minimum_accountable_unit,
3801 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_rounding_rule,
3802 'Y');
3803
3804 IF (g_level_statement >= g_current_runtime_level) THEN
3805 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || '-- Base amount after apply base rate (rounded): '||
3806 to_char(p_base_amount));
3807 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || '-- Call calculate_tax_amount - Regular');
3808 END IF;
3809 -- get tax amount
3810 calculate_tax_amount (p_transaction_nature,
3811 p_transaction_nature_class,
3812 p_organization_class,
3813 p_base_amount,
3814 p_group_tax_id,
3815 p_tax_category_id,
3816 p_trx_date,
3817 p_rule_id,
3818 p_ship_to_site_use_id,
3819 p_bill_to_site_use_id,
3820 p_establishment_type,
3821 p_contributor_type,
3822 p_customer_trx_id,
3823 p_customer_trx_line_id,
3824 p_related_customer_trx_id,
3825 p_previous_customer_trx_id,
3826 p_warehouse_location_id,
3827 p_contributor_class,
3828 p_set_of_books_id,
3829 v_calculate_return_code,
3830 o_tax_amount,
3831 p_tax_rate,
3832 p_taxable_basis,
3833 p_calculated_tax_amount,
3834 p_exchange_rate);
3835
3836 IF v_calculate_return_code <> 'SUCCESS' THEN
3837 RAISE ERROR_FROM_CAL_TAX_AMT;
3838 END IF;
3839
3840 IF (g_level_statement >= g_current_runtime_level) THEN
3841 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || '-- After call calculate_tax_amount'||
3842 'o_tax_amount = '||o_tax_amount);
3843 END IF;
3844
3845 -- get reduced tax amount for tributary substitution
3846 IF v_tributary_substitution = 'Y' THEN
3847 o_tax_amount := o_tax_amount - v_aux_tax_amount;
3848 IF (g_level_statement >= g_current_runtime_level) THEN
3849 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || '-- Tax category tributary substitution tax amount: '||
3850 to_char(o_tax_amount));
3851 END IF;
3852 END IF;
3853
3854 -- return success
3855 o_latin_return_code := ZX_PRODUCT_INTEGRATION_PKG.TAX_SUCCESS;
3856
3857 IF (g_level_statement >= g_current_runtime_level) THEN
3858 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','jl_zz_tax.calculate_latin_tax()-');
3859 END IF;
3860
3861 EXCEPTION
3862 WHEN ERROR_FROM_CAL_TAX_AMT THEN
3863 IF (g_level_statement >= g_current_runtime_level) THEN
3864 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || 'EXCEPTION(ERROR_FROM_FUNCTION): '||
3865 'jl_zz_tax.calculate_latin_tax ' ||v_calculate_return_code);
3866 END IF;
3867 fnd_message.set_name('AR', 'GENERIC_MESSAGE');
3868 fnd_message.set_token('GENERIC_TEXT',
3869 'EXCEPTION(ERROR_FROM_FUNCTION): jl_zz_tax.calculate_latin_tax '||
3870 v_calculate_return_code);
3871 o_latin_return_code := ZX_PRODUCT_INTEGRATION_PKG.TAX_RC_OERR;
3872 ZX_TDS_CALC_SERVICES_PUB_PKG.g_msg_context_info_rec.trx_line_id :=
3873 p_customer_trx_line_id;
3874
3875 ZX_TDS_CALC_SERVICES_PUB_PKG.g_msg_context_info_rec.trx_level_type :=
3876 'LINE';
3877
3878 ZX_API_PUB.add_msg(
3879 ZX_TDS_CALC_SERVICES_PUB_PKG.g_msg_context_info_rec);
3880
3881
3882
3883 WHEN ERROR_FROM_FUNCTION THEN
3884 fnd_message.set_name('AR', 'GENERIC_MESSAGE');
3885 fnd_message.set_token('GENERIC_TEXT',
3886 'EXCEPTION(ERROR_FROM_FUNCTION): jl_zz_tax.calculate_latin_tax');
3887 o_latin_return_code := ZX_PRODUCT_INTEGRATION_PKG.TAX_RC_OERR;
3888 ZX_TDS_CALC_SERVICES_PUB_PKG.g_msg_context_info_rec.trx_line_id :=
3889 p_customer_trx_line_id;
3890
3891 ZX_TDS_CALC_SERVICES_PUB_PKG.g_msg_context_info_rec.trx_level_type :=
3892 'LINE';
3893
3894 ZX_API_PUB.add_msg(
3895 ZX_TDS_CALC_SERVICES_PUB_PKG.g_msg_context_info_rec);
3896
3897 IF (g_level_statement >= g_current_runtime_level) THEN
3898 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || 'EXCEPTION(ERROR_FROM_FUNCTION): '||
3899 'jl_zz_tax.calculate_latin_tax');
3900 END IF;
3901
3902 WHEN NO_DATA_FOUND THEN
3903 fnd_message.set_name('AR', 'GENERIC_MESSAGE');
3904 fnd_message.set_token('GENERIC_TEXT',
3905 'EXCEPTION(NO_DATA_FOUND): jl_zz_tax.calculate_latin_tax');
3906 o_latin_return_code := ZX_PRODUCT_INTEGRATION_PKG.TAX_RC_OERR;
3907 IF (g_level_statement >= g_current_runtime_level) THEN
3908 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || 'EXCEPTION(NO_DATA_FOUND): '||
3909 'jl_zz_tax.calculate_latin_tax');
3910 END IF;
3911
3912 WHEN TOO_MANY_ROWS THEN
3913 fnd_message.set_name('AR', 'GENERIC_MESSAGE');
3914 fnd_message.set_token('GENERIC_TEXT',
3915 'EXCEPTION(TOO_MANY_ROWS): jl_zz_tax.calculate_latin_tax');
3916 o_latin_return_code := ZX_PRODUCT_INTEGRATION_PKG.TAX_RC_OERR;
3917 IF (g_level_statement >= g_current_runtime_level) THEN
3918 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','calculate_latin_tax: ' || 'EXCEPTION(TOO_MANY_ROWS): '||
3919 'jl_zz_tax.calculate_latin_tax');
3920 END IF;
3921
3922 WHEN OTHERS THEN
3923 err_num := SQLCODE;
3924 err_msg := SUBSTR(SQLERRM,1,100);
3925 fnd_message.set_name('AR', 'GENERIC_MESSAGE');
3926 fnd_message.set_token('GENERIC_TEXT',
3927 'EXCEPTION(OTHERS): jl_zz_tax.calculate_latin_tax '||
3928 to_char(err_num)||' '||err_msg);
3929 o_latin_return_code := ZX_PRODUCT_INTEGRATION_PKG.TAX_RC_OERR;
3930 IF (g_level_statement >= g_current_runtime_level) THEN
3931 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','EXCEPTION(OTHERS): jl_zz_tax.calculate_latin_tax '||
3932 to_char(err_num)||' '||err_msg);
3933 END IF;
3934
3935 END calculate_latin_tax;
3936
3937
3938 --========================================================================
3939 -- PUBLIC FUNCTION
3940 -- calculate
3941 --
3942 -- DESCRIPTION
3943 -- This routine calculates the tax amount for the transaction line or
3944 -- sales order line that is recorded in the global structure
3945 -- ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec
3946 --
3947 -- PARAMETERS
3948 -- tax_info_rec
3949 --
3950 -- RETURNS
3951 -- tax_info_rec updated with tax_rate, amount, global_attributes and
3952 -- other information
3953 --
3954 -- CALLED FROM
3955 -- ZX_PRODUCT_INTEGRATION_PKG.calculate
3956 --
3957 -- HISTORY
3958 -- 02-OCT-97 A. Chiromatzo Return values for global flexfield just
3959 -- for AR
3960 -- 09-SEP-97 A. Chiromatzo in calculate function, first check
3961 -- if ZX_PRODUCT_INTEGRATION_PKG.TAX_INFO_REC.TAX_AMOUNT is not null, if
3962 -- it's not null then does not perform anything as it
3963 -- does not use the audit trail, avoiding error
3964 -- during delete
3965 -- 20-NOV-97 A.Chiromatzo debug messages
3966 -- 22-APR-98 I. William bug #660025 reset the value of global_attribute8
3967 -- and global_attribute9 to null in the beginning
3968 -- of the calculate_latin_tax procedure. Also,
3969 -- for base amount reduction, <= 0 condition
3970 -- replaced with < 0.
3971 -- 08-FEB-99 H. Takle Used ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern7 to determine
3972 -- the calling view name. Changed for bug number
3973 -- 807588. Following is the mapping for usern7.
3974 -- 1 TAX_LINES_DELETE_VBR
3975 -- 2 TAX_LINES_CREATE_VBR
3976 -- 3 TAX_LINES_INVOICE_IMPORT_VBR
3977 -- 4 TAX_LINES_RECURR_INVOICE_VBR
3978 -- 5 TAX_ADJUSTMENTS_VBR
3979 -- 6 SO_TAX_LINES_CREDIT_CHECK_VBR
3980 -- 7 SO_TAX_LINES_SUMMARY_VBR
3981 -- 8 TAX_LINES_RMA_IMPORT_VBR
3982 --========================================================================
3983
3984 --Bug fix 2367111
3985 FUNCTION calculate
3986 (p_org_id IN NUMBER) RETURN VARCHAR2 IS
3987
3988 err_num NUMBER;
3989 err_msg NUMBER;
3990 v_rule_id NUMBER;
3991 v_tax_amount NUMBER;
3992 l_entered_amount NUMBER;
3993 v_base_amount NUMBER := NULL;
3994 v_calculated_tax_amount NUMBER := NULL;
3995 l_vat_tax_id NUMBER;
3996 v_use_legal_message VARCHAR2(1);
3997 l_application VARCHAR2(2);
3998 v_latin_tax_return_code VARCHAR2(30);
3999 l_organization_class VARCHAR2(30);
4000 v_legal_message_exception VARCHAR2(30);
4001 l_legal_message8 VARCHAR2(150);
4002 l_legal_message9 VARCHAR2(150);
4003 l_exchange_rate NUMBER;
4004 ERROR_FROM_FUNCTION EXCEPTION;
4005 l_org_id NUMBER;
4006 l_country_code varchar2(2);
4007
4008 -- bug#6834705
4009 l_trx_type_id RA_CUSTOMER_TRX_ALL.CUST_TRX_TYPE_ID%TYPE;
4010 l_cust_trx_type_id RA_CUSTOMER_TRX_ALL.CUST_TRX_TYPE_ID%TYPE;
4011
4012
4013 CURSOR get_orig_trx_type_id_c
4014 (c_org_id RA_CUSTOMER_TRX_ALL.org_id%TYPE,
4015 c_customer_trx_id RA_CUSTOMER_TRX_ALL.customer_trx_id%TYPE)
4016 IS
4017 SELECT cust_trx_type_id
4018 FROM RA_CUSTOMER_TRX_ALL
4019 WHERE org_id = c_org_id
4020 AND customer_trx_id = c_customer_trx_id ;
4021
4022 BEGIN
4023 v_use_legal_message := 'N';
4024
4025 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
4026 IF p_org_id IS NULL THEN
4027 l_org_id := TO_NUMBER(FND_PROFILE.VALUE('ORG_ID'));
4028 ELSE
4029 l_org_id := p_org_id;
4030 END IF;
4031
4032 IF (g_level_statement >= g_current_runtime_level) THEN
4033 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','jl_zz_tax.calculate()+');
4034 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','-P- Tax category tax amount: '||
4035 to_char(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_amount));
4036 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','-P- Tax category tax code: '||ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_code);
4037 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','-P- Tax category tax rate: '||ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_rate);
4038 END IF;
4039
4040 v_latin_tax_return_code := ZX_PRODUCT_INTEGRATION_PKG.TAX_SUCCESS;
4041
4042 -- Following code added for the bug number 1019748++
4043
4044 IF ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern7 IN (1,2,3,4,5,8) THEN
4045 l_application := 'AR';
4046 ELSIF ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern7 IN (6,7) THEN
4047 l_application := 'OE';
4048 END IF;
4049
4050 -- Above code added for the bug number 1019748--
4051 IF (g_level_statement >= g_current_runtime_level) THEN
4052 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','-- Application: '||l_application);
4053 END IF;
4054 -- Following code added for the bug number 807588++
4055
4056 IF ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern7 = 1 THEN
4057 -- Set following variable to populate_plsql_tables to execute before
4058 -- the first tax line processing
4059 g_prev_invoice_line_number := NULL;
4060 END IF;
4061
4062 -- Above code added for the bug number 807588--
4063
4064 -- bug#6834705
4065 -- if the invoice is credit memo, need to get
4066 -- cust_trx_type_id from the original invoice
4067 -- in order to get the correct rule defined from
4068 -- jl_zz_ar_tx_rules table
4069 --
4070
4071 IF ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.credit_memo_flag = TRUE THEN
4072 OPEN get_orig_trx_type_id_c
4073 (l_org_id,
4074 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.previous_customer_trx_id);
4075 FETCH get_orig_trx_type_id_c INTO l_cust_trx_type_id;
4076 CLOSE get_orig_trx_type_id_c;
4077
4078 l_trx_type_id := l_cust_trx_type_id;
4079 ELSE
4080 -- not credit memo
4081 l_trx_type_id := ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.trx_type_id;
4082 END IF;
4083
4084
4085 -- The view did not get the default tax code
4086 IF ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_code IS NULL THEN
4087 -- Raise the message populated in get_category_tax_code
4088 v_latin_tax_return_code := ZX_PRODUCT_INTEGRATION_PKG.TAX_RC_OERR;
4089
4090 ELSIF ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_amount IS NULL THEN
4091 SELECT NVL(vt.amount_includes_tax_flag,'N'),
4092 sp.global_attribute14,
4093 vt.global_attribute3,
4094 decode(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.trx_currency_code,
4095 ZX_PRODUCT_INTEGRATION_PKG.sysinfo.base_currency_code, 1,
4096 decode(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.trx_exchange_rate,
4097 NULL, 1,
4098 0, 1,
4099 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.trx_exchange_rate))
4100 INTO ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.amount_includes_tax_flag,
4101 v_use_legal_message,
4102 v_legal_message_exception,
4103 l_exchange_rate
4104 FROM ar_vat_tax vt,
4105 ar_system_parameters_all sp
4106 WHERE vt.vat_tax_id = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.vat_tax_id
4107 AND nvl(vt.org_id,-99) = nvl(sp.org_id,-99);
4108
4109 --BugFix 2180174 commented the Following IF condition.
4110 /*
4111 IF ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern2 IS NULL AND
4112 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.amount_includes_tax_flag <> 'N' THEN
4113 IF (g_level_statement >= g_current_runtime_level) THEN
4114 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','-- Amount Includes flag should be N');
4115 END IF;
4116 fnd_message.set_name('AR', 'GENERIC_MESSAGE');
4117 fnd_message.set_token('GENERIC_TEXT',
4118 'Amount Includes flag should be N');
4119 app_exception.raise_exception;
4120 END IF;
4121 */
4122 IF (g_level_statement >= g_current_runtime_level) THEN
4123
4124 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','-- Use Legal Message: '||v_use_legal_message);
4125 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','-- Exchange Rate: '||to_char(l_exchange_rate));
4126 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','-- Amount Includes tax flag: '||
4127 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.amount_includes_tax_flag);
4128 END IF;
4129
4130 SELECT NVL(global_attribute1,'DEFAULT')
4131 INTO l_organization_class
4132 FROM hr_locations_all
4133 where location_id = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern4;
4134
4135 IF (g_level_statement >= g_current_runtime_level) THEN
4136 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','-- Organization Class: '||l_organization_class);
4137 END IF;
4138
4139 IF ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.taxable_basis = 'QUANTITY' THEN
4140 l_entered_amount := ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.taxed_quantity;
4141 ELSE
4142 l_entered_amount := ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.entered_amount;
4143 END IF;
4144
4145 IF ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern2 is NOT NULL THEN
4146 IF (g_level_statement >= g_current_runtime_level) THEN
4147
4148 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','-- Trx Type Id: '||
4149 to_char(l_trx_type_id));
4150 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','-- Tax Category Id: '||
4151 to_char(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern1));
4152 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','-- Contributor Type: '||ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf8);
4153 END IF;
4154
4155 l_legal_message8 := NULL;
4156 l_legal_message9 := NULL;
4157
4158 -- Added following 2 lines to take care of bug# 660025
4159 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.global_attribute8 := null;
4160 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.global_attribute9 := null;
4161
4162 IF ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern3 IS NULL THEN
4163 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern3 :=
4164 JL_ZZ_TAX.GET_TAX_BASE_RATE (
4165 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern1,
4166 l_trx_type_id,
4167 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.ship_to_site_use_id,
4168 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.bill_to_site_use_id,
4169 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.inventory_item_id,
4170 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern2,
4171 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.memo_line_id,
4172 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.ship_to_cust_id,
4173 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.bill_to_cust_id,
4174 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.trx_date,
4175 l_application,
4176 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.ship_from_warehouse_id,
4177 'BASE',
4178 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf2,
4179 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.ship_from_warehouse_id,
4180 ZX_PRODUCT_INTEGRATION_PKG.sysinfo.sysparam.location_structure_id,
4181 ZX_PRODUCT_INTEGRATION_PKG.sysinfo.sysparam.global_attribute10,
4182 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf3,
4183 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf5,
4184 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf8,
4185 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern4,
4186 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf9 );
4187 END IF;
4188
4189 IF (g_level_statement >= g_current_runtime_level ) THEN
4190 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','-- Base Rate: '
4191 || to_char(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern3));
4192 END IF;
4193
4194 get_rule_legal_message (ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern1,
4195 --ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.trx_type_id,
4196 l_trx_type_id,
4197 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.ship_to_site_use_id,
4198 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.bill_to_site_use_id,
4199 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.inventory_item_id,
4200 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern2,
4201 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.memo_line_id,
4202 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.ship_to_cust_id,
4203 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.bill_to_cust_id,
4204 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.trx_date,
4205 l_application,
4206 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.ship_from_warehouse_id,
4207 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf2,
4208 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.ship_from_warehouse_id,
4209 ZX_PRODUCT_INTEGRATION_PKG.sysinfo.sysparam.location_structure_id,
4210 to_number(ZX_PRODUCT_INTEGRATION_PKG.sysinfo.sysparam.global_attribute10),
4211 ZX_PRODUCT_INTEGRATION_PKG.sysinfo.sysparam.set_of_books_id,
4212 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf3,
4213 l_entered_amount * (1 + (nvl(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern3,0)/100)),
4214 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf5,
4215 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf8,
4216 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern4,
4217 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf9,
4218 v_use_legal_message,
4219 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern3,
4220 v_legal_message_exception,
4221 v_rule_id,
4222 l_legal_message8,
4223 l_legal_message9);
4224
4225 IF (g_level_statement >= g_current_runtime_level) THEN
4226 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','-- Rule Id: '|| to_char(v_rule_id));
4227 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','-- Legal Message 1: '|| l_legal_message8);
4228 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','-- Legal Message 2: '|| l_legal_message9);
4229 END IF;
4230
4231 IF l_legal_message8 = 'ERROR' OR l_legal_message9 = 'ERROR' THEN
4232 JL_ZZ_TAX_INTEGRATION_PKG.g_jl_exception_type := 'E';
4233 RETURN(ZX_PRODUCT_INTEGRATION_PKG.TAX_RC_OERR);
4234 ELSE
4235 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.global_attribute8 := l_legal_message8;
4236 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.global_attribute9 := l_legal_message9;
4237 END IF;
4238 END IF;
4239
4240
4241 calculate_latin_tax (ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern1,
4242 v_rule_id,
4243 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern2,
4244 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.trx_date,
4245 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf8,
4246 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf3,
4247 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf5,
4248 l_trx_type_id, --ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.trx_type_id,
4249 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.ship_to_site_use_id,
4250 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.bill_to_site_use_id,
4251 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.inventory_item_id,
4252 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.memo_line_id,
4253 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.ship_to_cust_id,
4254 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.bill_to_cust_id,
4255 l_application,
4256 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.ship_from_warehouse_id,
4257 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf2,
4258 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern4,
4259 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf9,
4260 ZX_PRODUCT_INTEGRATION_PKG.sysinfo.sysparam.set_of_books_id,
4261 ZX_PRODUCT_INTEGRATION_PKG.sysinfo.sysparam.location_structure_id,
4262 ZX_PRODUCT_INTEGRATION_PKG.sysinfo.sysparam.global_attribute10,
4263 l_entered_amount,
4264 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.customer_trx_id,
4265 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.customer_trx_line_id,
4266 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern5,
4267 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.previous_customer_trx_id,
4268 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf6,
4269 l_organization_class,
4270 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_rate,
4271 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.taxable_basis,
4272 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern3,
4273 v_base_amount,
4274 v_tax_amount,
4275 v_latin_tax_return_code,
4276 v_calculated_tax_amount,
4277 l_exchange_rate);
4278
4279 IF v_latin_tax_return_code <> ZX_PRODUCT_INTEGRATION_PKG.TAX_SUCCESS THEN
4280 RAISE ERROR_FROM_FUNCTION;
4281 END IF;
4282
4283 IF (g_level_statement >= g_current_runtime_level) THEN
4284 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','-- Base amount (rounded): ' || to_char(v_base_amount));
4285 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','-- Tax amount: '|| to_char(v_tax_amount));
4286 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','-- Calculated Tax amount: '||
4287 to_char(v_calculated_tax_amount));
4288 END IF;
4289
4290 -- CR 3571797. Assign Unrounded Tax Amount to tax_info_rec for E-Business Tax
4291 -- requirement on LTE Tax Lines to simplify migration process of LTE.
4292 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.unrounded_tax_amt := v_tax_amount;
4293
4294 -- associate values in tax_info_rec and in global_attributes
4295 -- original rounding procedure
4296 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_amount := ZX_PRODUCT_INTEGRATION_PKG.tax_curr_round
4297 (v_tax_amount,
4298 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.trx_currency_code,
4299 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.precision,
4300 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.minimum_accountable_unit,
4301 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_rounding_rule,
4302 'Y');
4303
4304 IF (g_level_statement >= g_current_runtime_level) THEN
4305 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','-- Tax category tax amount after rounding: '||
4306 to_char(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_amount));
4307 END IF;
4308 -- set the global flexfield context according to product 'AR'
4309 IF l_application = 'AR' THEN
4310 l_country_code := JG_ZZ_SHARED_PKG.GET_COUNTRY(l_org_id, null);
4311
4312 IF (g_level_statement >= g_current_runtime_level) THEN
4313 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','-- l_country_code: '||l_country_code);
4314 END IF;
4315
4316 IF l_country_code = 'BR' THEN --Bug 2367111
4317 --IF fnd_profile.value_wnps('JGZZ_COUNTRY_CODE') = 'BR' THEN
4318 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.global_attribute_category :=
4319 'JL.BR.ARXTWMAI.Additional Info';
4320 ELSIF l_country_code IN ('AR','CO') THEN --Bug 2367111
4321 --ELSIF fnd_profile.value_wnps('JGZZ_COUNTRY_CODE') IN ('AR','CO') THEN
4322 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.global_attribute_category :=
4323 'J'||'L.'||JG_ZZ_SHARED_PKG.GET_COUNTRY(l_org_id, null)|| --Bug 2367111
4324 --'J'||'L.'||fnd_profile.value_wnps('JGZZ_COUNTRY_CODE')||
4325 '.ARXTWMAI.LINES';
4326 END IF;
4327
4328 -- set the default global attributes for the tax lines
4329 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.global_attribute2 := ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf2;
4330 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.global_attribute3 := ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.userf9;
4331
4332 -- Bugfix 1062149 fnd_number.number_to_canonical added in following lines
4333 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.global_attribute11 :=
4334 fnd_number.number_to_canonical (v_base_amount);
4335 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.global_attribute12 :=
4336 fnd_number.number_to_canonical(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern3);
4337
4338 -- Bugfix 1062149 fnd_number.number_to_canonical added in following lines
4339 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.global_attribute19 :=
4340 fnd_number.number_to_canonical(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_amount);
4341 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.global_attribute20 :=
4342 fnd_number.number_to_canonical( v_calculated_tax_amount);
4343
4344 -- Added following 1 line to take care of bug# 787259
4345 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.taxable_amount := v_base_amount;
4346 END IF;
4347
4348 END IF;
4349
4350 -- Following code added for Bug Number 803394++
4351
4352 IF ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern5 IS NOT NULL THEN
4353 l_vat_tax_id := NULL;
4354 BEGIN
4355 SELECT vat_tax_id
4356 INTO l_vat_tax_id
4357 FROM ar_vat_tax
4358 WHERE tax_code = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_code
4359 AND set_of_books_id = ZX_PRODUCT_INTEGRATION_PKG.sysinfo.sysparam.set_of_books_id
4360 AND tax_type = 'VAT'
4361 AND to_date(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern6,'YYYYMMDD') >= start_date
4362 AND to_date(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern6,'YYYYMMDD') <=
4363 nvl(end_date,to_date(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern6,'YYYYMMDD'))
4364 AND nvl(enabled_flag,'Y') = 'Y'
4365 AND nvl(tax_class,'O') = 'O';
4366 EXCEPTION
4367 WHEN OTHERS THEN
4368 l_vat_tax_id := NULL;
4369 END;
4370 IF (g_level_statement >= g_current_runtime_level) THEN
4371 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','-- Original Vat Tax Id '||
4372 to_char(ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.vat_tax_id));
4373 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','-- New Vat Tax Id '||to_char(l_vat_tax_id));
4374 END IF;
4375 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.vat_tax_id :=
4376 NVL(l_vat_tax_id,ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.vat_tax_id);
4377 END IF;
4378
4379 -- 11.6 Bug 3571797. Following code would populate missing columns of Tax Lines
4380 -- that are required for E-Business Tax solution and simplifies migration of LTE
4381 -- tax lines.
4382 --IF ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_regime_code IS NULL THEN
4383
4384 IF (g_level_statement >= g_current_runtime_level) THEN
4385 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','-- populating tix information for tax_regime_code: '||
4386 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_regime_code);
4387 END IF;
4388
4389 BEGIN
4390 SELECT rate.tax_rate_id,
4391 rate.tax_rate_code,
4392 regime.tax_regime_id,
4393 regime.tax_regime_code,
4394 tax.tax_id,
4395 tax.tax,
4396 status.tax_status_id,
4397 status.tax_status_code
4398 INTO ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_rate_id,
4399 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_rate_code,
4400 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_regime_id,
4401 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_regime_code,
4402 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_id,
4403 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax,
4404 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_status_id,
4405 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_status_code
4406 FROM zx_rates_b rate,
4407 zx_regimes_b regime,
4408 zx_taxes_b tax,
4409 zx_status_b status
4410 WHERE rate.tax_rate_id = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.vat_tax_id
4411 and rate.tax_regime_code = regime.tax_regime_code
4412 and rate.tax = tax.tax
4413 and tax.tax_regime_code = rate.tax_regime_code
4414 and tax.content_owner_id = rate.content_owner_id
4415 and rate.tax_status_code = status.tax_status_code
4416 and status.tax_regime_code = rate.tax_regime_code
4417 and status.tax = rate.tax
4418 and status.content_owner_id = rate.content_owner_id;
4419
4420 IF (g_level_statement >= g_current_runtime_level) THEN
4421 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','-- EBTAX- Tax Rate Code: ' ||
4422 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_rate_code);
4423 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','-- EBTAX- Tax Regime Code: ' ||
4424 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_regime_code);
4425 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','-- EBTAX- Tax: ' ||
4426 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax);
4427 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','-- EBTAX- Tax Status: ' ||
4428 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_status_code);
4429 END IF;
4430
4431 EXCEPTION
4432 WHEN NO_DATA_FOUND THEN
4433
4434 SELECT rate.tax_rate_id,
4435 rate.tax_rate_code,
4436 rate.tax_regime_code,
4437 rate.tax,
4438 rate.tax_status_code
4439 INTO ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_rate_id,
4440 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_rate_code,
4441 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_regime_code,
4442 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax,
4443 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_status_code
4444 FROM zx_rates_b rate
4445 WHERE rate.tax_rate_id = ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.vat_tax_id;
4446
4447 WHEN OTHERS THEN
4448 IF (g_level_statement >= g_current_runtime_level) THEN
4449 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','EXCEPTION(EBTAX): jl_zz_tax.calculate '||
4450 SQLCODE||' '||SQLERRM);
4451 END IF;
4452
4453 END;
4454 --END IF; -- End Bug 3571797
4455
4456 -- Populate the eBTax related columns here:
4457 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.legal_justification_text1 :=
4458 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.GLOBAL_ATTRIBUTE8;
4459 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.legal_justification_text2 :=
4460 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.GLOBAL_ATTRIBUTE9;
4461 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.legal_justification_text3 :=
4462 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.GLOBAL_ATTRIBUTE10;
4463 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_base_modifier_rate :=
4464 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.usern3;
4465 -- same as ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.GLOBAL_ATTRIBUTE12;
4466 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.cal_tax_amt := v_calculated_tax_amount;
4467 -- Same as ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.GLOBAL_ATTRIBUTE20;
4468 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.unrounded_taxable_amt :=
4469 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.taxable_Amount;
4470 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_date :=
4471 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.trx_date;
4472 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_determine_date :=
4473 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.trx_date;
4474 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.tax_point_date :=
4475 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.trx_date;
4476
4477 -- Code added for Bug Number 803394--
4478
4479 IF (g_level_statement >= g_current_runtime_level) THEN
4480 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','jl_zz_tax.calculate()-');
4481 END IF;
4482 RETURN (v_latin_tax_return_code);
4483
4484 EXCEPTION
4485 WHEN ERROR_FROM_FUNCTION THEN
4486 fnd_message.set_name('AR', 'GENERIC_MESSAGE');
4487 fnd_message.set_token('GENERIC_TEXT',
4488 'EXCEPTION(ERROR_FROM_FUNCTION): jl_zz_tax.calculate');
4489 IF (g_level_unexpected >= g_current_runtime_level) THEN
4490 FND_LOG.STRING(g_level_unexpected,'ZX.PLSQL.JL_ZZ_TAX','EXCEPTION(ERROR_FROM_FUNCTION): jl_zz_tax.calculate');
4491 END IF;
4492 ZX_TDS_CALC_SERVICES_PUB_PKG.g_msg_context_info_rec.trx_line_id :=
4493 ZX_PRODUCT_INTEGRATION_PKG.tax_info_rec.customer_trx_line_id;
4494
4495 ZX_TDS_CALC_SERVICES_PUB_PKG.g_msg_context_info_rec.trx_level_type :=
4496 'LINE';
4497
4498 ZX_API_PUB.add_msg(
4499 ZX_TDS_CALC_SERVICES_PUB_PKG.g_msg_context_info_rec);
4500
4501
4502 RETURN (ZX_PRODUCT_INTEGRATION_PKG.TAX_RC_OERR);
4503
4504 WHEN NO_DATA_FOUND THEN
4505 fnd_message.set_name('AR', 'GENERIC_MESSAGE');
4506 fnd_message.set_token('GENERIC_TEXT',
4507 'EXCEPTION(NO_DATA_FOUND): jl_zz_tax.calculate');
4508 IF (g_level_unexpected >= g_current_runtime_level) THEN
4509 FND_LOG.STRING(g_level_unexpected,'ZX.PLSQL.JL_ZZ_TAX','EXCEPTION(NO_DATA_FOUND): jl_zz_tax.calculate');
4510 END IF;
4511 RETURN (ZX_PRODUCT_INTEGRATION_PKG.TAX_RC_OERR);
4512
4513 WHEN TOO_MANY_ROWS THEN
4514 fnd_message.set_name('AR', 'GENERIC_MESSAGE');
4515 fnd_message.set_token('GENERIC_TEXT',
4516 'EXCEPTION(TOO_MANY_ROWS): jl_zz_tax.calculate');
4517 IF (g_level_unexpected >= g_current_runtime_level) THEN
4518 FND_LOG.STRING(g_level_unexpected,'ZX.PLSQL.JL_ZZ_TAX','EXCEPTION(TOO_MANY_ROWS): jl_zz_tax.calculate');
4519 END IF;
4520 RETURN (ZX_PRODUCT_INTEGRATION_PKG.TAX_RC_OERR);
4521
4522 WHEN OTHERS THEN
4523 err_num := SQLCODE;
4524 err_msg := SUBSTR(SQLERRM,1,100);
4525 fnd_message.set_name('AR', 'GENERIC_MESSAGE');
4526 fnd_message.set_token('GENERIC_TEXT',
4527 'EXCEPTION(OTHERS): jl_zz_tax.calculate '||
4528 to_char(err_num)||' '
4529 ||err_msg);
4530 IF (g_level_unexpected >= g_current_runtime_level) THEN
4531 FND_LOG.STRING(g_level_unexpected,'ZX.PLSQL.JL_ZZ_TAX','EXCEPTION(OTHERS): jl_zz_tax.calculate '||
4532 SQLCODE||' ; '||SQLERRM);
4533 END IF;
4534 RETURN (ZX_PRODUCT_INTEGRATION_PKG.TAX_RC_OERR);
4535
4536 END calculate;
4537
4538 FUNCTION get_legal_message (
4539 p_rule_id IN NUMBER,
4540 p_rule_data_id IN NUMBER,
4541 p_legal_message_exception IN VARCHAR2,
4542 p_ship_from_warehouse_id IN NUMBER)
4543 RETURN VARCHAR2 IS
4544
4545 v_message_text VARCHAR2(150) := NULL;
4546 v_rule jl_zz_ar_tx_rules.rule%type;
4547
4548
4549
4550 BEGIN
4551 g_current_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
4552 IF (g_level_statement >= g_current_runtime_level) THEN
4553 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','jl_zz_tax.get_legal_message()+');
4554 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_legal_message: ' || '-P- Rule Id: '||to_char(p_rule_id));
4555 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_legal_message: ' || '-P- Rule Data Id: '||to_char(p_rule_data_id));
4556 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_legal_message: ' || '-P- Exception Code: '||p_legal_message_exception);
4557 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_legal_message: ' || '-P- Ship from warehouse Id: '||
4558 to_char(p_ship_from_warehouse_id));
4559 END IF;
4560
4561 SELECT r.rule
4562 INTO v_rule
4563 FROM jl_zz_ar_tx_rules r
4564 WHERE r.rule_id = p_rule_id;
4565
4566 IF (g_level_statement >= g_current_runtime_level) THEN
4567 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_legal_message: ' || '-- Rule Code: '||v_rule);
4568 END IF;
4569
4570 IF v_rule IN ('GET_ITEM_TX_CODE', 'GET_MEMO_LINE_TX_CODE',
4571 'GET_CUSTOMER_TX_CODE', 'GET_BILL_TO_TX_CODE',
4572 'GET_SHIP_TO_TX_CODE', 'GET_ORGANIZATION_TX_CODE',
4573 'GET_FISC_CLAS_TX_CODE', 'GET_LOCATION_TX_CODE',
4574 'GET_CUST_EXC_TX_CODE', 'GET_TRX_NATURE_TX_CODE',
4575 'GET_LATIN_TX_GRP_TX_CODE', 'GET_EXC_FISC_CLAS_TX_CODE',
4576 'GET_EXC_ITEM_TX_CODE')
4577 THEN
4578
4579 IF v_rule IN ('GET_ITEM_TX_CODE', 'GET_EXC_ITEM_TX_CODE')
4580 THEN
4581 SELECT substr(st.text,1,150)
4582 INTO v_message_text
4583 FROM jl_zz_ar_tx_lgl_msg lm,
4584 ar_standard_text_vl st
4585 WHERE st.standard_text_id = lm.message_id
4586 AND lm.rule_data_id = p_rule_data_id
4587 AND lm.inventory_organization_id = p_ship_from_warehouse_id
4588 AND lm.rule_id = p_rule_id
4589 AND lm.exception_code = p_legal_message_exception;
4590 ELSE
4591 SELECT substr(st.text,1,150)
4592 INTO v_message_text
4593 FROM jl_zz_ar_tx_lgl_msg lm,
4594 ar_standard_text_vl st
4595 WHERE st.standard_text_id = lm.message_id
4596 AND lm.rule_data_id = p_rule_data_id
4597 AND lm.rule_id = p_rule_id
4598 AND lm.exception_code = p_legal_message_exception;
4599 END IF;
4600
4601 END IF;
4602
4603 IF (g_level_statement >= g_current_runtime_level) THEN
4604 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','get_legal_message: ' || '-O- Legal Message: '||v_message_text);
4605 FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_TAX','jl_zz_tax.get_legal_message()-');
4606 END IF;
4607
4608 RETURN(v_message_text);
4609
4610 EXCEPTION
4611 WHEN NO_DATA_FOUND THEN
4612 fnd_message.set_name('JL', 'JL_LEGAL_MESSAGE_NOT_FOUND');
4613 fnd_message.set_token('RULE_CODE',v_rule);
4614 fnd_message.set_token('EXCEPTION_CODE',p_legal_message_exception);
4615 RETURN ('ERROR');
4616
4617 END get_legal_message;
4618
4619
4620 END JL_ZZ_TAX;