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