[Home] [Help]
PACKAGE BODY: APPS.JAI_CMN_TAX_DEFAULTATION_PKG
Source
1 PACKAGE BODY jai_cmn_tax_defaultation_pkg AS
2 /* $Header: jai_cmn_tax_dflt.plb 120.13.12010000.2 2008/11/21 12:11:01 jmeena ship $ */
3
4
5 PROCEDURE ja_in_cust_default_taxes (
6 p_org_id NUMBER,
7 p_customer_id NUMBER,
8 p_ship_to_site_use_id NUMBER,
9 p_inventory_item_id IN NUMBER,
10 p_header_id NUMBER,
11 p_line_id NUMBER,
12 p_tax_category_id IN OUT NOCOPY NUMBER
13 )
14 IS
15 v_address_id NUMBER;
16 v_tax_category_list VARCHAR2(30);
17 v_tax_category_id NUMBER;
18
19 /* Added by Ramananda for bug#4407165 */
20 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_tax_defaultation_pkg.ja_in_cust_default_taxes';
21
22 -- to get address_id
23 CURSOR address_cur(p_ship_to_site_use_id IN NUMBER) IS
24 SELECT cust_acct_site_id address_id
25 FROM hz_cust_site_uses_all A -- Removed ra_site_uses_all from Bug# 4434287
26 WHERE A.site_use_id = p_ship_to_site_use_id;
27
28 -- to get tax_category_list
29 CURSOR tax_catg_list_cur(p_customer_id IN NUMBER, p_address_id IN NUMBER DEFAULT 0) IS
30 SELECT tax_category_list
31 FROM JAI_CMN_CUS_ADDRESSES a
32 WHERE A.customer_id = p_customer_id
33 AND A.address_id = p_address_id;
34
35 -- to get tax_category_id
36 CURSOR tax_catg_id_cur(p_tax_category_list IN VARCHAR2, p_inventory_item_id IN NUMBER) IS
37 SELECT tax_category_id
38 FROM JAI_INV_ITM_TAXCTG_DTLS a
39 WHERE a.tax_category_list = p_tax_category_list
40 AND a.inventory_item_id = p_inventory_item_id;
41
42 BEGIN
43
44 OPEN address_cur(p_ship_to_site_use_id);
45 FETCH address_cur INTO v_address_id;
46 CLOSE address_cur;
47
48 IF p_customer_id IS NOT NULL AND v_address_id IS NOT NULL THEN
49 OPEN tax_catg_list_cur(p_customer_id , v_address_id);
50 FETCH tax_catg_list_cur INTO v_tax_category_list;
51 CLOSE tax_catg_list_cur;
52 END IF;
53
54 IF v_tax_category_list IS NULL THEN
55 OPEN tax_catg_list_cur(p_customer_id,0);
56 FETCH tax_catg_list_cur INTO v_tax_category_list;
57 CLOSE tax_catg_list_cur;
58 END IF;
59
60 IF v_tax_category_list IS NOT NULL THEN
61 OPEN tax_catg_id_cur(v_tax_category_list, p_inventory_item_id);
62 FETCH tax_catg_id_cur INTO v_tax_category_id;
63 CLOSE tax_catg_id_cur;
64 -- ELSE -- redundant
65 -- ja_in_org_default_taxes(p_org_id, p_inventory_item_id, v_tax_category_id);
66 END IF;
67
68 IF v_tax_category_id IS NULL THEN
69
70 /* redundant code
71 OPEN tax_catg_list_cur(p_customer_id,0);
72 FETCH tax_catg_list_cur INTO v_tax_category_list;
73 CLOSE tax_catg_list_cur;
74
75 IF v_tax_category_list IS NOT NULL THEN
76 OPEN tax_catg_id_cur(v_tax_category_list ,p_inventory_item_id );
77 FETCH tax_catg_id_cur INTO v_tax_category_id;
78 CLOSE tax_catg_id_cur;
79 -- ELSE -- redundant
80 -- ja_in_org_default_taxes(p_org_id, p_inventory_item_id, v_tax_category_id);
81 END IF;
82
83 IF v_tax_category_id IS NULL THEN
84 ja_in_org_default_taxes(p_org_id, p_inventory_item_id, v_tax_category_id);
85 END IF;
86 */
87 ja_in_org_default_taxes(p_org_id, p_inventory_item_id, v_tax_category_id);
88
89 END IF;
90
91 p_tax_category_id := v_tax_category_id;
92
93
94 /* Added by Ramananda for bug#4407165 */
95 EXCEPTION
96 WHEN OTHERS THEN
97 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
98 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
99 app_exception.raise_exception;
100
101 END ja_in_cust_default_taxes;
102
103 /***********************************************************************************************************************/
104 PROCEDURE ja_in_vendor_default_taxes(
105 p_org_id NUMBER,
106 p_vendor_id NUMBER,
107 p_vendor_site_id NUMBER,
108 p_inventory_item_id IN NUMBER,
109 p_header_id NUMBER,
110 p_line_id NUMBER,
111 p_tax_category_id IN OUT NOCOPY NUMBER
112 ) IS
113
114 v_tax_category_list VARCHAR2(30);
115 v_tax_category_id NUMBER;
116
117 /* Added by Ramananda for bug#4407165 */
118 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_tax_defaultation_pkg.ja_in_vendor_default_taxes';
119
120 -- to get tax_category_list
121 CURSOR tax_catg_list_cur(p_vendor_id IN NUMBER, p_vendor_site_id IN NUMBER DEFAULT 0) IS
122 SELECT tax_category_list
123 FROM JAI_CMN_VENDOR_SITES A
124 WHERE a.vendor_id = p_vendor_id
125 AND a.vendor_site_id = p_vendor_site_id;
126
127 -- to get tax_category_id
128 CURSOR tax_catg_id_cur(p_tax_category_list IN VARCHAR2, p_inventory_item_id IN NUMBER) IS
129 SELECT tax_category_id
130 FROM JAI_INV_ITM_TAXCTG_DTLS a
131 WHERE a.tax_category_list = p_tax_category_list
132 AND a.inventory_item_id = p_inventory_item_id;
133
134 BEGIN
135
136 IF p_vendor_id IS NOT NULL AND p_vendor_site_id IS NOT NULL THEN
137 OPEN tax_catg_list_cur(p_vendor_id, p_vendor_site_id);
138 FETCH tax_catg_list_cur INTO v_tax_category_list;
139 CLOSE tax_catg_list_cur;
140 END IF;
141
142 IF v_tax_category_list IS NULL THEN
143 OPEN tax_catg_list_cur(p_vendor_id,0);
144 FETCH tax_catg_list_cur INTO v_tax_category_list;
145 CLOSE tax_catg_list_cur;
146 END IF;
147
148 IF v_tax_category_list IS NOT NULL THEN
149 OPEN tax_catg_id_cur(v_tax_category_list ,p_inventory_item_id );
150 FETCH tax_catg_id_cur INTO v_tax_category_id;
151 CLOSE tax_catg_id_cur;
152 -- ELSE redundant code
153 -- ja_in_org_default_taxes(p_org_id, p_inventory_item_id, v_tax_category_id);
154 END IF;
155
156 IF NVL(v_tax_category_id,0) = 0 THEN
157
158 /* REDUNDANT CODE
159 OPEN tax_catg_list_cur(p_vendor_id,0);
160 FETCH tax_catg_list_cur INTO v_tax_category_list;
161 CLOSE tax_catg_list_cur;
162
163 IF v_tax_category_list IS NOT NULL THEN
164 OPEN tax_catg_id_cur(v_tax_category_list ,p_inventory_item_id );
165 FETCH tax_catg_id_cur INTO v_tax_category_id;
166 CLOSE tax_catg_id_cur;
167 ELSE
168 ja_in_org_default_taxes(p_org_id, p_inventory_item_id, v_tax_category_id);
169 END IF;
170
171 IF NVL(v_tax_category_id,0) = 0 THEN
172 ja_in_org_default_taxes(p_org_id, p_inventory_item_id, v_tax_category_id);
173 END IF;
174 */
175
176 ja_in_org_default_taxes(p_org_id, p_inventory_item_id, v_tax_category_id);
177
178 END IF;
179
180 p_tax_category_id := v_tax_category_id;
181
182 /* Added by Ramananda for bug#4407165 */
183 EXCEPTION
184 WHEN OTHERS THEN
185 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
186 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
187 app_exception.raise_exception;
188
189 END ja_in_vendor_default_taxes;
190
191 ---------------******************JA_IN_ORG_DEFAULT_TAXES******************---------------------
192
193 PROCEDURE ja_in_org_default_taxes(
194 p_org_id NUMBER,
195 p_inventory_item_id IN NUMBER,
196 p_tax_category_id IN OUT NOCOPY NUMBER
197 ) IS
198 /*------------------------------------------------------------------------------------------
199 CHANGE HISTORY:
200
201 Sl. YYYY/MM/DD Author and Details
202 ------------------------------------------------------------------------------------------
203 1 2004/09/22 Aiyer for bug#3792765. Version#115.2
204 Issue
205 Warehouse ID is not currently being allowed to be left null from the base apps sales order. When placing a order from 2
206 different manufacturing organizations, it is required that customer temporarily leaves the warehouseid as Null and then
207 updates the same before pick release. However this is currently not allowed by localization even though base
208 apps allows this feature.
209
210 Reason:-
211 The trigger ja_in_oe_order_lines_aiu_trg raises an error of warehouse not found when the value of warehouse_id goes as
212 null from the form.
213
214 Solution:-
215 Removed this part from the trigger ja_in_oe_order_lines_aiu_trg. The procedure jai_cmn_tax_defaultation_pkg.ja_in_org_default_taxes has been modified
216 for the same.
217 During tax defaultation, if the tax category list is not found in the customer/customer site level then it is being picked up from the
218 item class level. Now in cases where the warehouseid is left blank in the base apps sales order form, the tax category id from the
219 master organization set for the default operating unit is picked up for further processing
220
221 Dependency Due to this Bug:-
222 Functional dependency with the trigger ja_in_oe_order_lines_aiu_trg.sql version 115.4
223
224 2 31/10/2006 SACSETHI for bug 5228046, File version 120.3
225 1. Forward porting the change in 11i bug 5365523 (Additional CVD Enhancement).
226 This bug has datamodel and spec changes.
227
228 2. Forward porting of bug 5219225
229
230 3 25/04/2007 cbabu for BUG#6012570 (5876390 )version = 120.5 (115.29 )
231 FP: Project Billing
232 4 05/06/2007 bduvarag for the bug#6081966 and 5989740, File version 120.8
233 forward porting the 11i bugs 6074792 and 5907436
234
235 5. 01-08-2007 rchandan for bug#6030615 , Version 120.10
236 Issue : Inter org Forward porting
237 ----------------------------------------------------------------------------------------------------------------------------------------------------*/
238 /* Added by Ramananda for bug#4407165 */
239 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_tax_defaultation_pkg.ja_in_org_default_taxes';
240
241
242 --Code Added on 18-SEP-2000, Srihari and Gsrinivas
243 v_operating_unit NUMBER;
244 /*
245 || Start of bug 3792765
246 || Cursor added by aiyer to get the operating unit and master organization id in case the warehouse id is passed
247 || as null from the Base apps sales order form.
248 */
249 CURSOR cur_get_master_org_id
250 IS
251 SELECT
252 org_id operating_unit,
253 master_organization_id
254 FROM
255 oe_system_parameters ;
256 /*
257 || End of bug 3792765
258 */
259
260
261 CURSOR operating_unit_cur(c_org_id NUMBER) IS
262 SELECT operating_unit
263 FROM org_organization_definitions
264 WHERE organization_id = NVL(c_org_id, 0);
265
266 -- to get tax_category_id from Item class
267 CURSOR tax_catg_id_cur(v_org_id IN NUMBER, v_inventory_item_id IN NUMBER, v_operating_unit NUMBER) IS
268 SELECT b.tax_category_id
269 FROM JAI_INV_ITM_SETUPS a , JAI_CMN_TAX_CTGS_ALL b -- redundant, org_organization_definitions c
270 WHERE a.item_class = b.item_class_cd
271 AND a.inventory_item_id = v_inventory_item_id
272 AND a.organization_id = v_org_id
273 AND b.org_id = v_operating_unit;
274 --End of Addition , Srihari and Gsrinivas
275
276 rec_cur_get_master_org_id CUR_GET_MASTER_ORG_ID%ROWTYPE;
277 BEGIN
278 /*
279 || Start of bug 3792765
280 || IF the warehouse id i.e the invemtory organization id is null from the SAles order base apps form
281 || then in that case get the operating unit and master organization id from the oe_system_parameters table
282 */
283
284 IF p_org_id IS NULL THEN
285 OPEN cur_get_master_org_id;
286 FETCH cur_get_master_org_id INTO rec_cur_get_master_org_id ;
287 CLOSE cur_get_master_org_id ;
288 v_operating_unit := rec_cur_get_master_org_id.operating_unit;
289 ELSE
290 /*
291 || End of bug 3792765
292 */
293
294 OPEN operating_unit_cur(p_org_id);
295 FETCH operating_unit_cur INTO v_operating_unit;
296 CLOSE operating_unit_cur;
297 END IF;
298
299 v_operating_unit := NVL(v_operating_unit, 0);
300
301 OPEN tax_catg_id_cur( nvl(p_org_id,rec_cur_get_master_org_id.master_organization_id) , p_inventory_item_id , v_operating_unit);
302 FETCH tax_catg_id_cur INTO p_tax_category_id;
303 CLOSE tax_catg_id_cur;
304
305 /* Added by Ramananda for bug#4407165 */
306 EXCEPTION
307 WHEN OTHERS THEN
308 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
309 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
310 app_exception.raise_exception;
311
312 END ja_in_org_default_taxes;
313
314 /*****************************JA_IN_CALC_PREC_TAXES********************************/
315
316 PROCEDURE ja_in_calc_prec_taxes(
317 transaction_name VARCHAR2,
318 p_tax_category_id NUMBER,
319 p_header_id NUMBER,
320 p_line_id NUMBER,
321 p_assessable_value NUMBER DEFAULT 0,
322 p_tax_amount IN OUT NOCOPY NUMBER,
323 p_inventory_item_id NUMBER,
324 p_line_quantity NUMBER,
325 p_uom_code VARCHAR2,
326 p_vendor_id NUMBER,
327 p_currency VARCHAR2,
328 p_currency_conv_factor NUMBER,
329 p_creation_date DATE,
330 p_created_by NUMBER,
331 p_last_update_date DATE,
332 p_last_updated_by NUMBER,
333 p_last_update_login NUMBER,
334 p_operation_flag NUMBER DEFAULT NULL , -- for CRM this is used to hold aso_shipments.shipment_id
335 p_vat_assessable_value NUMBER DEFAULT 0
336 /** bgowrava for forward porting bug#5631784,Following parameters are added for TCS enh.*/
337 , p_thhold_cat_base_tax_typ JAI_CMN_TAXES_ALL.tax_type%type default null -- tax type to be considered as base when calculating threshold taxes
338 , p_threshold_tax_cat_id JAI_AP_TDS_THHOLD_TAXES.tax_category_id%type default null
339 , p_source_trx_type jai_cmn_document_taxes.source_doc_type%type default null
340 , p_source_table_name jai_cmn_document_taxes.source_table_name%type default null
341 , p_action varchar2 default null
342 /** End bug 5631784 */
343 , pv_retroprice_changed IN VARCHAR2 DEFAULT 'N' --Added by Kevin Cheng for Retroactive Price 2008/01/13
344 ) IS
345 --TYPE num_tab IS TABLE OF NUMBER(30,3) INDEX BY BINARY_INTEGER; -- sriram - bug # 2812781 was 14 eaerler changed to 30
346 --TYPE tax_amt_num_tab IS TABLE OF NUMBER(30,3) INDEX BY BINARY_INTEGER; -- sriram - bug # 2812781 was 14 eaerler changed to 30
347
348
349 -- Date 02/11/2006 Bug 5228046 added by SACSETHI
350
351 TYPE num_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
352 TYPE tax_amt_num_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
353 TYPE tax_adhoc_flag_tab IS TABLE OF VARCHAR2(2) INDEX BY BINARY_INTEGER; /* rchandan bug#6030615 */
354
355 --added by Walton for inclusive tax 07-Dev-07
356 ---------------------------------------------
357 TYPE char_tab IS TABLE OF VARCHAR2(10)
358 INDEX BY BINARY_INTEGER;
359
360 lt_adhoc_tax_tab CHAR_TAB;
361 lt_inclusive_tax_tab CHAR_TAB;
362 ln_exclusive_price NUMBER;
363 lt_tax_rate_per_rupee NUM_TAB;
364 lt_cumul_tax_rate_per_rupee NUM_TAB;
365 ln_total_non_rate_tax NUMBER :=0;
366 ln_total_inclusive_factor NUMBER;
367 lt_tax_amt_rate_tax_tab TAX_AMT_NUM_TAB;
368 lt_tax_amt_non_rate_tab TAX_AMT_NUM_TAB;
369 ln_bsln_amt_nr NUMBER :=0;
370 ln_tax_amt_nr NUMBER(38,10) :=0;
371 ln_vamt_nr NUMBER(38,10) :=0;
372 ln_total_tax_per_rupee NUMBER;
373 ln_assessable_value NUMBER;
374 ln_vat_assessable_value NUMBER;
375 -----------------------------------------------
376
377 /* Added by Ramananda for bug#4407165 */
378 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_tax_defaultation_pkg.ja_in_calc_prec_taxes';
379
380 p1 NUM_TAB;
381 p2 NUM_TAB;
382 p3 NUM_TAB;
383 p4 NUM_TAB;
384 p5 NUM_TAB;
385
386 -- Date 31/10/2006 Bug 5228046 added by SACSETHI
387 -- start bug 5228046
388 p6 NUM_TAB;
389 p7 NUM_TAB;
390 p8 NUM_TAB;
391 p9 NUM_TAB;
392 p10 NUM_TAB;
393 -- end bug 5228046
394
395 tax_rate_tab NUM_TAB;
396 /*
397 || Aiyer for the fwd ported bug#4691616 . Added tax_rate_zero_tab table
398 -------------------------------------------------------------
399 tax_rate(i) tax_rate_tab(i) tax_rate_zero_tab(i)
400 -------------------------------------------------------------
401 NULL 0 0
402 0 0 -9999
403 n (non-zero and not null) n n
404 -------------------------------------------------------------
405 */
406 tax_rate_zero_tab NUM_TAB;
407
408 tax_type_tab NUM_TAB;
409 tax_target_tab NUM_TAB;
410 tax_amt_tab TAX_AMT_NUM_TAB;
411 round_factor_tab TAX_AMT_NUM_TAB; --added by csahoo for bug#6077133
412 base_tax_amt_tab TAX_AMT_NUM_TAB;
413 func_tax_amt_tab TAX_AMT_NUM_TAB;
414 adhoc_flag_tab TAX_ADHOC_FLAG_TAB ; /* rchandan bug#6030615 */
415 end_date_tab NUM_TAB;
416
417 bsln_amt NUMBER; -- := p_tax_amount; --Ramananda for File.Sql.35
418
419 v_conversion_rate NUMBER; -- := 0; --Ramananda for File.Sql.35
420 v_currency_conv_factor NUMBER; -- := p_currency_conv_factor; --Ramananda for File.Sql.35
421
422
423 -- Date 01/11/2006 Bug 5228046 added by SACSETHI
424
425 -- v_tax_amt NUMBER(25,3) := 0; -- cbabu for EnhancementBug# 2427465
426 -- v_func_tax_amt NUMBER(25,3) := 0;
427 -- vamt NUMBER(25,3) := 0;
428
429 v_tax_amt NUMBER := 0;
430 v_func_tax_amt NUMBER := 0;
431 vamt NUMBER := 0;
432
433 v_amt NUMBER;
434 row_count NUMBER := 1;
435 counter NUMBER;
436 max_iter NUMBER := 10;
437 v_excise_jb NUMBER;
438
439 v_line_focus_id_holder JAI_PO_LINE_LOCATIONS.line_focus_id%TYPE; -- cbabu for EnhancementBug# 2427465
440
441
442 /** bgowrava, Begin forward porting bug#5631784 */
443 refc_tax_cur ref_cur_typ;
444 rec tax_rec_typ;
445 type tax_table_typ is
446 table of tax_rec_typ index by binary_integer;
447 lt_tax_table tax_table_typ;
448
449 ln_max_tax_line number;
450 ln_max_rgm_tax_line number;
451 ln_base number ;
452 ln_dup_tax_exists number;
453
454 v_modvat_flag varchar2(1); -- moved from prev location to here
455 lv_recalculation_sql varchar2 (4000);
456 ln_exists number (2);
457
458 /*
459 || Cursor will check if the given tax_categories have common taxes
460 */
461 cursor c_chk_tax_duplication
462 is
463 select 1
464 from JAI_CMN_TAX_CTG_LINES
465 where tax_category_id in (p_tax_category_id, nvl(p_threshold_tax_cat_id,-1))
466 group by tax_id
467 having count(tax_id) > 1;
468
469 cursor c_get_max_tax_line
470 is
471 select max(line_no) max_tax_line
472 from JAI_CMN_TAX_CTG_LINES cat
473 where cat.tax_category_id = p_tax_category_id;
474
475 cursor c_get_max_rgm_tax_line
476 is
477 select max(line_no) max_rgm_tax_line
478 from JAI_CMN_TAX_CTG_LINES cat, JAI_CMN_TAXES_ALL taxes
479 where cat.tax_category_id = p_tax_category_id
480 and taxes.tax_id = cat.tax_id
481 and taxes.tax_type = p_thhold_cat_base_tax_typ;
482
483 /** End Bug#5631784*/
484
485
486 CURSOR tax_cur(p_tax_category_id IN NUMBER) IS
487 SELECT a.tax_category_id, a.tax_id, a.line_no lno,
488 a.precedence_1 p_1, a.precedence_2 p_2,
489 a.precedence_3 p_3, a.precedence_4 p_4,
490 a.precedence_5 p_5, a.precedence_6 p_6, -- Date 31/10/2006 Bug 5228046 added by SACSETHI
491 a.precedence_7 p_7, a.precedence_8 p_8,
492 a.precedence_9 p_9, a.precedence_10 p_10,
493 b.tax_rate, b.tax_amount, b.uom_code, b.end_date valid_date,
494 DECODE(rgm_tax_types.regime_Code,jai_constants.vat_regime, 4, /* added by ssumaith - bug# 4245053*/
495 DECODE(UPPER(b.tax_type),
496 'EXCISE', 1,
497 'ADDL. EXCISE', 1,
498 'OTHER EXCISE', 1,
499 'TDS', 2,
500 'EXCISE_EDUCATION_CESS' ,1,
501 'CVD_EDUCATION_CESS' ,1,
502 0
503 )
504 ) tax_type_val,
505 b.mod_cr_percentage, b.vendor_id, b.tax_type,nvl(b.rounding_factor,0) rounding_factor
506 , inclusive_tax_flag --added by walton for inclusive tax 08-Dev-07
507 FROM JAI_CMN_TAX_CTG_LINES a, JAI_CMN_TAXES_ALL b ,
508 jai_regime_tax_types_v rgm_tax_types /* added by ssumaith - bug# 4245053*/
509 WHERE a.tax_category_id = p_tax_category_id
510 AND rgm_tax_types.tax_type (+) = b.tax_type /* added by ssumaith - bug# 4245053*/
511 AND a.tax_id = b.tax_id
512 -- AND (b.end_date >= sysdate OR b.end_date IS NULL)
513 ORDER BY A.line_no;
514
515 CURSOR uom_class_cur(p_line_uom_code IN VARCHAR2, p_tax_line_uom_code IN VARCHAR2) IS
516 SELECT a.uom_class
517 FROM mtl_units_of_measure A, mtl_units_of_measure B
518 WHERE a.uom_code = p_line_uom_code
519 AND b.uom_code = p_tax_line_uom_code
520 AND a.uom_class = b.uom_class;
521
522 --2001/03/30 Manohar Mishra
523 /*Start of Addition*/
524 v_organization_id NUMBER;
525 v_location_id NUMBER;
526 v_batch_source_id NUMBER;
527 v_register_code JAI_OM_OE_BOND_REG_HDRS.register_code%TYPE;
528
529 CURSOR get_header_info_cur IS
530 SELECT organization_id, location_id, batch_source_id
531 FROM JAI_AR_TRXS
532 WHERE customer_trx_id = p_header_id;
533
534 CURSOR get_register_code_cur(p_organization_id NUMBER, p_location_id NUMBER, p_batch_source_id NUMBER) IS
535 SELECT register_code
536 FROM JAI_OM_OE_BOND_REG_HDRS
537 WHERE organization_id = p_organization_id
538 AND location_id = p_location_id
539 AND register_id IN (SELECT register_id
540 FROM JAI_OM_OE_BOND_REG_DTLS
541 WHERE order_type_id = p_batch_source_id
542 AND order_flag = 'N');
543
544 v_debug boolean := true; -- Vijay Shankar for Bug# 2837970
545 v_line_num number:=1 ;--added by rchandan for bug#6030615
546 ln_reg_id number;
547
548 /*End of Addition*/
549 uom_cls UOM_CLASS_CUR%ROWTYPE;
550
551 lv_tax_remain_flag VARCHAR2(1); --Added by Kevin Cheng for Retroactive Price 2008/01/13
552 lv_transaction_name VARCHAR2(30); --Added by Kevin Cheng for Retroactive Price 2008/01/13
553 lv_start NUMBER; --Added by Kevin Cheng for Retroactive Price 2008/01/13
554 lv_line_loc_id NUMBER; --Added by Kevin Cheng for Retroactive Price 2008/01/13
555 lv_process_flag VARCHAR2(10); --Added by Kevin Cheng for Retroactive Price 2008/01/13
556 lv_process_message VARCHAR2(2000); --Added by Kevin Cheng for Retroactive Price 2008/01/13
557
558 BEGIN
559 /*************************************************************************************************************************
560 /*----------------------------------------------------------------------------------------------------------------------
561 CHANGE HISTORY: Procedure ja_in_calc_prec_taxes
562 S.No Date Author and Details
563 ------------------------------------------------------------------------------------------------------------------------
564
565 1. 12/07/2003 Aiyer - Bug #3749294 File Version 115.1
566 Issue:-
567 Uom based taxes do not get calculated correctly if the transaction UOM is different from the
568 UOM setup in the tax definitions India Localization (JAI_CMN_TAXES_ALL table).
569
570 Reason:-
571 --------
572 This was happening because the UOM calculation was previously happening only for cases of exact match
573 between transaction uom and setup UOM.
574
575 Fix:-
576 ----
577 Modified the procedure ja_in_calc_prec_taxes.The exact match condition was removed. Now if an exact match
578 is not found then the conversion rate between the two uom's is determined and tax amounts are calculated for
579 defaultation.
580
581 Dependency Due to This Bug:-
582 ----------------------------
583 None
584
585 2. 27/01/2005 ssumaith - bug#4136981 - Version 115.2
586
587 In case of Bond Register scenario , in addition to excise taxes , Excise Cess (EXCISE_EDUCATION_CESS,CVD_EDUCATION_CESS)
588 should also not flow to AR
589
590 3. 2005/03/10 ssumaith - bug# 4245053 - File version 115.3
591
592 Taxes under the vat regime needs to be calculated based on the vat assessable value setup done.
593 In the vendor additional information screen and supplier additional information screen, a place
594 has been given to capture the vat assessable value.
595
596 This needs to be used for the calculation of the taxes registered under vat regime.
597
598 This change has been done by using the view jai_regime_tax_types_v - and outer joining it with the
599 JAI_CMN_TAXES_ALL table based on the tax type column.
600
601 Parameter - p_vat_assessable_value NUMBER DEFAULT 0 has been added
602
603 Dependency due to this bug - Huge
604 This patch should always be accompanied by the VAT consolidated patch - 4245089
605
606
607 4. 01-Jun-2006 Aiyer for bug# 4691616. File Version 120.2
608 Issue:-
609 UOM based taxes do not get calculated correctly.
610
611 Solution:-
612 Fwd ported the fix for the bug 4729742.
613 Changed the files JAINTAX1.pld, jai_cmn_tax_dflt.plb and jai_om_tax.plb.
614
615 5. 24-Jan-2007 bgowrava for forward porting bug#5631784, File Version 120.3 - TCS Enhancement
616
617 Issue:
618 1. As a part of TCS enh. there was a requirement to default taxes using the tax category attached to
619 threshold setup for TCS regime in the India-Threshold setup UI.
620 2. Package should provide an API for inserting taxes in to new table jai_cmn_document_taxes
621
622 Fix:
623 To support above functionalities the following approach is used.
624 1. New parameters are added to this procedure to get the tax category defined for threshold limit.
625 (Please refer the procedure signature)
626 2. Whenever p_threshold_tax_cat_id is not NULL then it means taxes from two categories needs to be merged.
627 one using p_tax_category_id and other is p_threshold_tax_cat_id
628 3. current driving cursor (tax_cur) is modified to handle multiple tax categories.
629 3.1 For all the tax lines defined in the p_tax_category_id there is no change
630 3.2 For all the tax lines defined in the p_threshold_tax_cat_id, line_no will be changed
631 to ln_max_tax_line + line_no where ln_max_tax_line is the maximum of line numbers for
632 tax lines defined in p_tax_category_id
633 3.3 All the precedences defined in p_threshold_tax_cat_id will be changed as following
634 - If precedence refers to base precedence (i.e. 0) it will be changed to ln_max_rgm_tax_line
635 where ln_max_rgm_tax_line is maximum of the line numbers of taxes having
636 tax_type = p_thhold_cat_base_tax_typ (i.e. tax type to be considered as a base tax
637 when calculating threshold taxes defined using p_threshold_tax_cat_id)
638 - All other precedences will be changed to precedence_N + ln_max_tax_line
639
640
641 6 04/june/2007 ssumaith - bug#6109941 - review comments for TCS .
642 TCS enhancement forward porting has some minor issues that were resolved.
643
644 7. 05-Jun-2007 CSahoo for bug#6077133, File version- 120.7
645 Issue: The Taxes at header and the Line level does not
646 tally for the Manually created AR Transaction.
647 Fix: added a rounding factor round_factor_tab.
648
649 8. 16-Oct-2007 CSahoo for bug#6498072, File Version 120.12
650 R12RUP04-ST1: TCS TAXES ARE WRONG ON ADDING SURCHARGE
651 On creating a sales order and after delivery the taxes are taken only for a Single Quantity which is wrong.
652 so made changes in the code so that the taxes are taken for the whole quantity
653 9 01-Dev-2007 Walton for Inclusive Tax
654
655 10. 20-Nov-2008 JMEENA for bug#6488296( FP of 6475430)
656 Added OR condition in procedure ja_in_calc_prec_taxes as we are passing p_action null in case of 'CASH' Receipt.
657
658 Future Dependencies For the release Of this Object:-
659 (Please add a row in the section below only if your bug introduces a dependency due to spec change/ A new call to a object/
660 A datamodel change )
661
662 ----------------------------------------------------------------------------------------------------------------------------------------------------
663 Current Version Current Bug Dependent Files Version Author Date Remarks
664 Of File On Bug/Patchset Dependent On
665 jai_cmn_tax_defaultation_pkg.sql
666 ----------------------------------------------------------------------------------------------------------------------------------------------------
667 115.1 2977185 IN60105D2 None -- Aiyer 13/07/2004 Row introduces to start dependency tracking
668
669 115.3 4245053 IN60106 + ssumaith Service Tax and VAT Infrastructure are created
670 4146708 + based on the bugs - 4146708 and 4545089 respectively.
671 4245089
672 *************************************************************************************************************************/
673 --Added by Kevin Cheng for Retroactive Price 2008/01/13
674 --=====================================================
675 IF pv_retroprice_changed = 'N'
676 THEN
677 --=====================================================
678 --Ramananda for File.Sql.35
679 bsln_amt := p_tax_amount ;
680 v_conversion_rate := 0;
681 v_currency_conv_factor := p_currency_conv_factor;
682 ln_base := 0 ;
683
684 IF transaction_name <> 'CRM_QUOTE' THEN -- Vijay Shankar for Bug# 2837970
685 IF v_debug THEN fnd_file.put_line(fnd_file.log, ' transaction_name -> '||transaction_name); END IF;
686
687 --2001/03/30 Manohar Mishra
688 /*Start of Addition*/
689 OPEN get_header_info_cur;
690 FETCH get_header_info_cur INTO v_organization_id, v_location_id, v_batch_source_id;
691 CLOSE get_header_info_cur;
692
693 OPEN get_register_code_cur(v_organization_id, v_location_id, v_batch_source_id);
694 FETCH get_register_code_cur INTO v_register_code;
695 CLOSE get_register_code_cur;
696
697 -- Vijay Shankar for Bug# 2837970
698 ELSE -- this should get executed when tax defaultation is for CRM_QUOTE
699 v_register_code := null;
700 END IF;
701 --2001/03/30 Manohar Mishra
702
703 /*End of Addition*/
704
705
706 /** bgowrava for forward porting bug#5631784*/
707 if p_tax_category_id is null
708 and (p_threshold_tax_cat_id is null or p_threshold_tax_cat_id <0) then
709 /** Both driving parameter tax_category_id and threshol_tax_category_id are invalid hence no need to do anything */
710 return;
711 end if;
712
713 if nvl(p_action, jai_constants.default_taxes) = jai_constants.default_taxes then
714 /** Assign tax defaultation cursor object to refc_tax_cur reference by using. Call to get_tax_cat_taxes_cur will return
715 a reference cursor */
716
717 if p_threshold_tax_cat_id is not null and p_threshold_tax_cat_id > 0 then
718 /*
719 || Cursor to check if same taxes exists in both tax categories
720 */
721 ln_dup_tax_exists := null;
722 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Open/Close/Fetch cursor c_chk_tax_duplication');*/ --commented by bgowrava for bug#5631784
723 open c_chk_tax_duplication;
724 fetch c_chk_tax_duplication into ln_dup_tax_exists;
725 close c_chk_tax_duplication;
726
727 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'ln_dup_tax_exists='||ln_dup_tax_exists);*/ --commented by bgowrava for bug#5631784
728
729 if ln_dup_tax_exists is not null
730 or (nvl(p_threshold_tax_cat_id,-1) = p_tax_category_id)
731 then
732
733 fnd_message.set_name('JA', 'JAI_DUP_TAX_IN_TAX_CAT');
734 app_exception.raise_exception ;
735
736 end if;
737
738 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Open/Close/Fetch cursor c_get_max_tax_line');*/ --commented by bgowrava for bug#5631784
739 open c_get_max_tax_line;
740 fetch c_get_max_tax_line into ln_max_tax_line;
741 close c_get_max_tax_line ;
742
743 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Open/Close/Fetch cursor c_get_max_rgm_tax_line');*/ --commented by bgowrava for bug#5631784
744 open c_get_max_rgm_tax_line;
745 fetch c_get_max_rgm_tax_line into ln_max_rgm_tax_line;
746 close c_get_max_rgm_tax_line ;
747
748 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'ln_max_tax_line='||ln_max_tax_line||', ln_max_rgm_tax_line='||ln_max_rgm_tax_line);*/ --commented by bgowrava for bug#5631784
749
750 end if;
751
752 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Opening ref cursor');*/ --commented by bgowrava for bug#5631784
753 get_tax_cat_taxes_cur ( p_tax_category_id => p_tax_category_id
754 , p_threshold_tax_cat_id => p_threshold_tax_cat_id
755 , p_max_tax_line => ln_max_tax_line
756 , p_max_rgm_tax_line => ln_max_rgm_tax_line
757 , p_refc_tax_cat_taxes_cur => refc_tax_cur
758 );
759
760 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Cursor is opened');*/ --commented by bgowrava for bug#5631784
761
762 elsif p_action = jai_constants.recalculate_taxes then
763 /**
764 Following is a dynamic sql string which can be modifed as per requirement
765
766 The sql has four place holders defined as below
767 $$EXTRA_SELECT_COLUMN_LIST$$ - Use this place holder to select additional columns in the sql.
768 You must also change corrosponding fetch statements and the record being used for fetch.
769 SELECT statement above should also be changed to include the newly added columns
770 as they are sharing a common cursor and fetch record.
771
772 $$TAX_SOURCE_TABLE$$ - At runtime this placeholder must be replaced with name of
773 source table to be used for recalculation
774 $$SOURCE_TABLE_FILTER$$ - At runtime, this place holder must represent a boolean condition
775 which can filter required rows from the source table
776 for recalculation. It must be the first condition and should never
777 start with either AND or OR
778 $$ADDITIONAL_WHERE_CLAUSE$$ - Replace the placeholder with additional conditions if any.
779 The condition must start with either AND or OR keyword
780 $$ADDITIONAL_ORDER_BY$$ - Replace the placeholder with list of columns and order sequence, if required.
781 Column list must start with comma (,)
782 If any of this placeholder is not required to be used it must be replaced with a null value as below
783 replace ( lv_recalculation_sql
784 , '$$EXTRA_SELECT_COLUMN_LIST$$'
785 , ''
786 );
787 */
788 lv_recalculation_sql :=
789 ' select a.tax_id
790 , a.tax_line_no lno
791 , a.precedence_1 p_1
792 , a.precedence_2 p_2
793 , a.precedence_3 p_3
794 , a.precedence_4 p_4
795 , a.precedence_5 p_5
796 , a.precedence_6 p_6
797 , a.precedence_7 p_7
798 , a.precedence_8 p_8
799 , a.precedence_9 p_9
800 , a.precedence_10 p_10
801 , a.tax_rate
802 , a.tax_amount
803 , b.uom_code
804 , b.end_date valid_date
805 , DECODE(rttv.regime_code, '''||jai_constants.vat_regime||''', 4, /* added by ssumaith - bug# 4245053*/
806 DECODE(UPPER(b.tax_type), ''EXCISE'' , 1
807 , ''ADDL. EXCISE'', 1
808 , ''OTHER EXCISE'', 1
809 , ''TDS'' , 2
810 , ''EXCISE_EDUCATION_CESS'',6 --modified by walton for inclusive tax
811 , '''||JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS||''' , 6 /*bduvarag for the bug#5989740*/ --modified by walton for inclusive tax
812 , ''CVD_EDUCATION_CESS'' ,6 --modified by walton for inclusive tax
813 , '''||JAI_CONSTANTS.TAX_TYPE_SH_CVD_EDU_CESS||''' , 6 /*bduvarag for the bug#5989740*/ --modified by walton for inclusive tax
814 , 0
815 )
816 ) tax_type_val
817 , b.mod_cr_percentage
818 , b.vendor_id
819 , b.tax_type
820 , nvl(b.rounding_factor,0) rounding_factor
821 , b.adhoc_flag
822 , b.inclusive_tax_flag --added by walton for inclusive tax on 08-Dev-07
823 $$EXTRA_SELECT_COLUMN_LIST$$
824 from $$TAX_SOURCE_TABLE$$ a
825 , JAI_CMN_TAXES_ALL b
826 , jai_regime_tax_types_v rttv
827 where $$SOURCE_TABLE_FILTER$$
828 and rttv.tax_type (+) = b.tax_type
829 and a.tax_id = b.tax_id $$ADDITIONAL_WHERE_CLAUSE$$
830 order by a.tax_line_no $$ADDITIONAL_ORDER_BY$$';
831
832
833 /** No extra columns required. Dummy column (NULL) tax_category_id needs to be added in the last as same record (rec) is being used
834 when fetching the cursor. If there is a need to override this default behaviour then please replace these place holder with
835 desired strings which can be evaluated at runtime by sql-engine
836 */
837 lv_recalculation_sql :=
838 replace ( lv_recalculation_sql
839 , '$$EXTRA_SELECT_COLUMN_LIST$$'
840 , ',null tax_category_id'
841 );
842
843 /** No additional filtering required */
844 lv_recalculation_sql :=
845 replace ( lv_recalculation_sql
846 , '$$ADDITIONAL_WHERE_CLAUSE$$'
847 , ''
848 );
849
850 /** No additional sorting required */
851 lv_recalculation_sql :=
852 replace ( lv_recalculation_sql
853 , '$$ADDITIONAL_ORDER_BY$$'
854 , ''
855 );
856
857 if upper(p_source_trx_type) = jai_constants.source_ttype_delivery then
858
859 /** replace the correct tax source table */
860 lv_recalculation_sql :=
861 replace ( lv_recalculation_sql
862 , '$$TAX_SOURCE_TABLE$$'
863 , 'JAI_OM_WSH_LINE_TAXES'
864 );
865 /** replace join condition */
866 lv_recalculation_sql :=
867 replace ( lv_recalculation_sql
868 , '$$SOURCE_TABLE_FILTER$$'
869 , 'a.delivery_detail_id = ' || p_line_id
870 );
871 elsif upper(p_source_trx_type) = jai_constants.bill_only_invoice then
872
873 /** For bill_only_invoice tax source table is ja_in_ra_cust_trx_tax_lines*/
874 lv_recalculation_sql :=
875 replace ( lv_recalculation_sql
876 , '$$TAX_SOURCE_TABLE$$'
877 , 'JAI_AR_TRX_TAX_LINES'
878 );
879 /** replace join condition */
880 lv_recalculation_sql :=
881 replace ( lv_recalculation_sql
882 , '$$SOURCE_TABLE_FILTER$$'
883 , 'a.link_to_cust_trx_line_id = ' || p_line_id
884 );
885 /*
886 elsif upper(pv_tax_source_table) = '<some tax table>' then
887 ...
888 ...
889 */
890
891 -- Date 24-Apr-2007 Added by SACSETHI for bug 6012570 (5876390)
892 -- in This , Recalculation will be happen in Draft invoice
893 ---------------------------------------------------------
894 elsif upper(p_source_trx_type) = jai_constants.PA_DRAFT_INVOICE then
895
896 lv_recalculation_sql :=
897 replace ( lv_recalculation_sql
898 , 'a.tax_amount'
899 , 'a.tax_amt'
900 );
901
902 lv_recalculation_sql :=
903 replace ( lv_recalculation_sql
904 , '$$TAX_SOURCE_TABLE$$'
905 , 'JAI_CMN_DOCUMENT_TAXES'
906 );
907 /** replace join condition */
908 lv_recalculation_sql :=
909 replace ( lv_recalculation_sql
910 , '$$SOURCE_TABLE_FILTER$$'
911 , 'a.SOURCE_DOC_LINE_ID = ' || p_line_id || ' and SOURCE_DOC_TYPE ='''|| jai_constants.PA_DRAFT_INVOICE || ''''
912 );
913
914 -- Added by Jason Liu for standalone invoice on 2007/08/23
915 ----------------------------------------------------------------------
916 ELSIF upper(p_source_trx_type) =
917 jai_constants.G_AP_STANDALONE_INVOICE
918 THEN
919
920 lv_recalculation_sql :=
921 REPLACE( lv_recalculation_sql
922 , 'a.tax_amount'
923 , 'a.tax_amt'
924 );
925
926 lv_recalculation_sql :=
927 REPLACE( lv_recalculation_sql
928 , '$$TAX_SOURCE_TABLE$$'
929 , 'JAI_CMN_DOCUMENT_TAXES'
930 );
931 -- replace join condition
932 lv_recalculation_sql :=
933 REPLACE( lv_recalculation_sql
934 , '$$SOURCE_TABLE_FILTER$$'
935 , 'a.SOURCE_DOC_LINE_ID = ' || p_line_id ||
936 ' and SOURCE_DOC_TYPE ='''||
937 jai_constants.G_AP_STANDALONE_INVOICE || ''''
938 );
939 ----------------------------------------------------------------------
940 end if; /*pv_tax_source_table*/
941
942 /**
943 When control comes here, a valid sql statement hold by variable lv_recalculate_sql
944 must be ready to execute.
945
946 open a dynamic select statement using OPEN-FOR statement
947 */
948 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'TAX RECALCULATION SQL STATEMENT');
949 jai_cmn_debug_contexts_pkg.print (ln_reg_id, lv_recalculation_sql);
950 jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Opening ref cursor with dynamic sql'); */--commented by bgowrava for bug#5631784
951
952 open refc_tax_cur for lv_recalculation_sql;
953
954 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Cursor is opened with lv_recalculation_sql');*/ --commented by bgowrava for bug#5631784
955
956 end if; /** RECALCULATE */
957
958 /** Clear the tax table */
959 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Before fetching cursor rows and starting loop');*/ --commented by bgowrava for bug#5631784
960 lt_tax_table.delete;
961
962 loop
963 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'In Loop, row_count='||row_count||', lt_tax_table.count='||lt_tax_table.count);*/ --commented by bgowrava for bug#5631784
964 fetch refc_tax_cur into rec;
965 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Post Fetch refc_tax_cur, rec.tax_id='||rec.tax_id||', rec.tax_rate='||rec.tax_rate);*/ --commented by bgowrava for bug#5631784
966 exit when refc_tax_cur%notfound;
967
968 /** End of bug 5631784*/
969
970
971
972 /** Add current record in the lt_tax_table for future use at the time of either UPDATE or INSERT into the tables*/
973
974 --FOR rec IN tax_cur(p_tax_category_id) LOOP
975 lt_tax_table(lt_tax_table.count+1) := rec;
976 p1(row_count) := nvl(rec.p_1,-1);
977 p2(row_count) := nvl(rec.p_2,-1);
978 p3(row_count) := nvl(rec.p_3,-1);
979 p4(row_count) := nvl(rec.p_4,-1);
980 p5(row_count) := nvl(rec.p_5,-1);
981 p6(row_count) := nvl(rec.p_6,-1); -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
982 p7(row_count) := nvl(rec.p_7,-1);
983 p8(row_count) := nvl(rec.p_8,-1);
984 p9(row_count) := nvl(rec.p_9,-1);
985 p10(row_count) := nvl(rec.p_10,-1);
986 tax_rate_tab(row_count) := NVL(rec.tax_rate,0);
987
988 --added by walton for inclusive tax on 08-Dev-07
989 -----------------------------------------------------------------
990 lt_tax_rate_per_rupee(row_count):=NVL(rec.tax_rate,0)/100;
991 ln_total_tax_per_rupee:=0;
992 lt_inclusive_tax_tab(row_count):=NVL(rec.inclusive_tax_flag,'N');
993 lt_tax_amt_rate_tax_tab(row_count):=0;
994 lt_tax_amt_non_rate_tab(row_count):=0;
995 ------------------------------------------------------------------
996
997 /*
998 || The following code added by aiyer for the bug 4691616
999 || Purpose:
1000 || rec.tax_rate = 0 means that tax_rate for such a tax line is actually zero (i.e it is not a replacement of null value)
1001 || So, when rec.tax_rate = 0, tax_rate_zero_tab is populated with -9999 to identify that this tax_line actually has tax_rate = 0
1002 || To calculate the BASE_TAX_AMOUNT of the taxes whose tax_rate is zero
1003 */
1004
1005 IF rec.tax_rate is null THEN
1006 /*
1007 ||Indicates qty based taxes
1008 */
1009 tax_rate_zero_tab(row_count) := 0;
1010
1011 ELSIF rec.tax_rate = 0 THEN
1012 /*
1013 ||Indicates 0% tax rate becasue a tax can have a rate as 0%.
1014 */
1015 tax_rate_zero_tab(row_count) := -9999;
1016
1017 ELSE
1018 tax_rate_zero_tab(row_count) := rec.tax_rate;
1019
1020 END IF;
1021
1022 tax_type_tab(row_count) := rec.tax_type_val;
1023 /*End of bug 4691616 */
1024 -- tax_amt_tab(row_count) := 0;
1025 /*added for bug#6498072, start*/
1026 IF p_action = jai_constants.recalculate_taxes AND --recalculate_taxes
1027 NVL(rec.adhoc_flag,'N') = 'Y' --adhoc_flag='Y'
1028 THEN
1029 tax_amt_tab(row_count) := nvl(rec.tax_amount,0) ;
1030 ELSE
1031 tax_amt_tab(row_count) := 0;
1032 END IF ;
1033 /*bug#6498072, end*/
1034 round_factor_tab(row_count):=rec.rounding_factor; --added by csahoo for bug#6077133
1035 base_tax_amt_tab(row_count) := 0;
1036 adhoc_flag_tab(row_count):=rec.adhoc_flag ; /* rchandan bug#6030615 */
1037
1038 IF tax_rate_tab(row_count) = 0
1039 AND rec.uom_code is not null --added by csahoo for bug#6498072
1040 THEN
1041 -- Start of bug 3749294
1042 /*
1043 Code added by aiyer for the bug 3749294
1044 Check whether an exact match exists between the transaction uom and the setup uom (obtained through the tax_category list).
1045 IF an exact match is found then the conversion rate is equal to 1 else the conversion rate between the two uom's would be
1046 determined and tax amounts,base_tax_amounts are calculated for defaultation.
1047 */
1048 Inv_Convert.inv_um_conversion( p_uom_code, rec.uom_code, p_inventory_item_id, v_conversion_rate);
1049 IF NVL(v_conversion_rate, 0) <= 0 THEN
1050 -- pramasub start FP
1051 /*4281841 ..rchandan..start*/
1052 OPEN uom_class_cur(p_uom_code, rec.uom_code);
1053 FETCH uom_class_cur INTO uom_cls;
1054 IF uom_class_cur%FOUND THEN
1055 Inv_Convert.inv_um_conversion( p_uom_code, rec.uom_code, 0, v_conversion_rate);
1056 ELSE
1057 v_conversion_rate := 0;
1058 END IF;
1059 CLOSE uom_class_cur;
1060 /*4281841 ..rchandan..end*/
1061 -- pramasub end FP
1062 --Inv_Convert.inv_um_conversion( p_uom_code, rec.uom_code, 0, v_conversion_rate); commented by pramasub
1063 IF NVL(v_conversion_rate, 0) <= 0 THEN
1064
1065 /* for cash receipt there will be no lines. sacsethi for 6012570 (5876390) */
1066 if (p_uom_code is null and p_inventory_item_id is null )
1067 and p_source_trx_type = jai_constants.ar_cash
1068 then --
1069 v_conversion_rate := 1;
1070
1071 /*
1072 Date 22-feb-2007 Added by SACSETHI for bug 6012570 (5876390)
1073 in This , Recalculation will be happen in Draft invoice
1074 */
1075 elsif (p_uom_code is null and p_inventory_item_id is null )
1076 and p_source_trx_type in (jai_constants.pa_draft_invoice
1077 ,jai_constants.G_AP_STANDALONE_INVOICE)
1078 then
1079 v_conversion_rate := 0;
1080
1081 else
1082 v_conversion_rate := 0;
1083 end if;
1084
1085 END IF;
1086 END IF;
1087 --tax_amt_tab(rec.lno) := nvl(rec.tax_amount * v_conversion_rate, 0) * p_line_quantity; -- cbabu for EnhancementBug# 2427465, compact code
1088 /*added for bug#6498072, start*/
1089 IF p_action = jai_constants.recalculate_taxes THEN
1090 /*tax_amt_tab(rec.lno) := nvl(rec.tax_amount * v_conversion_rate, 0) ;*/ --commented out by walton for inclusive tax
1091 lt_tax_amt_non_rate_tab(rec.lno):=NVL(rec.tax_amount * v_conversion_rate, 0); --added by walton for inclusive tax
1092 ELSE
1093 /*tax_amt_tab(rec.lno) := nvl(rec.tax_amount * v_conversion_rate, 0) * p_line_quantity ;*/ --commented out by walton for inclusive tax
1094 --added by walton for inclusive tax
1095 lt_tax_amt_non_rate_tab(rec.lno):=NVL(rec.tax_amount * v_conversion_rate, 0) * p_line_quantity;
1096 END IF ;
1097 /*added for bug#6498072, end*/
1098 /*commented out by walton for inclusive tax
1099 tax_amt_tab(rec.lno):=round(tax_amt_tab(rec.lno),round_factor_tab(rec.lno)); --added by csahoo for bug#6077133
1100 base_tax_amt_tab(rec.lno) := tax_amt_tab(rec.lno);
1101 */
1102 base_tax_amt_tab(rec.lno) := lt_tax_amt_non_rate_tab(rec.lno); --added by walton for inclusive tax
1103 -- End of bug 3749294
1104 END IF;
1105
1106 IF rec.valid_date IS NULL OR rec.valid_date >= SYSDATE THEN
1107 end_date_tab(row_count) := 1;
1108 ELSE
1109 tax_amt_tab(row_count) := 0;
1110 end_date_tab(row_count) := 0;
1111 END IF;
1112 row_count := row_count + 1;
1113 END LOOP;
1114
1115 row_count := row_count - 1;
1116
1117 --added by walton for inclusive tax 08-Dev-07
1118 -------------------------------------------------
1119 IF p_vat_assessable_value<>p_tax_amount
1120 THEN
1121 ln_vat_assessable_value:=p_vat_assessable_value;
1122 ELSE
1123 ln_vat_assessable_value:=1;
1124 END IF; --End p_vat_assessable_value<>p_tax_amount
1125
1126 IF p_assessable_value<>p_tax_amount
1127 THEN
1128 ln_assessable_value:=p_assessable_value;
1129 ELSE
1130 ln_assessable_value:=1;
1131 END IF; --End p_assessable_value<>p_tax_amount
1132 ---------------------------------------------------
1133
1134 FOR I IN 1..row_count LOOP
1135 IF end_date_tab(I) <> 0 THEN
1136 IF tax_type_tab(I) = 1 THEN
1137 --Added by walton for inclusive tax on 08-Dec-07
1138 ------------------------------------------------
1139 IF ln_assessable_value =1
1140 THEN
1141 bsln_amt:=1;
1142 ln_bsln_amt_nr :=0;
1143 ELSE
1144 bsln_amt :=0;
1145 ln_bsln_amt_nr :=ln_assessable_value;
1146 END IF;
1147 ------------------------------------------------
1148 /*bsln_amt := p_assessable_value;*/ --commented out by walton for inclusive tax
1149 ELSIF tax_type_tab(I) = 4 THEN
1150 --Added by walton for inclusive tax on 08-Dec-07
1151 ------------------------------------------------
1152 IF ln_vat_assessable_value =1
1153 THEN
1154 bsln_amt:=1;
1155 ln_bsln_amt_nr :=0;
1156 ELSE
1157 bsln_amt :=0;
1158 ln_bsln_amt_nr :=ln_vat_assessable_value;
1159 END IF;
1160 ------------------------------------------------
1161 /*bsln_amt := p_vat_assessable_value;*/ --commented out by walton for inclusive tax
1162
1163 --Added by walton for inclusive tax
1164 -------------------------------------
1165 ELSIF tax_type_tab(I) = 6 THEN
1166 bsln_amt:=0;
1167 ln_bsln_amt_nr :=0;
1168 -------------------------------------
1169 ELSE
1170 bsln_amt:=1; --Added by walton for inclusive tax
1171 ln_bsln_amt_nr :=0; --Added by walton for inclusive tax
1172 /*bsln_amt := p_tax_amount;*/ --commented out by walton for inclusive tax
1173 END IF;
1174
1175 IF tax_rate_tab(I) <> 0 THEN
1176 IF P1(I) < I AND P1(I) NOT IN (-1,0) THEN
1177 vamt := vamt + NVL(tax_amt_tab(P1(I)),0);
1178 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P1(I)),0); --added by walton for inclusive tax
1179 ELSIF P1(I) = 0 THEN
1180 vamt := vamt + bsln_amt;
1181 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by walton for inclusive tax
1182 END IF;
1183 IF p2(I) < I AND p2(I) NOT IN (-1,0) THEN
1184 vamt := vamt + NVL(tax_amt_tab(p2(I)),0);
1185 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P2(I)),0); --added by walton for inclusive tax
1186 ELSIF p2(I) = 0 THEN
1187 vamt := vamt + bsln_amt;
1188 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by walton for inclusive tax
1189 END IF;
1190 IF p3(I) < I AND p3(I) NOT IN (-1,0) THEN
1191 vamt := vamt + NVL(tax_amt_tab(p3(I)),0);
1192 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P3(I)),0); --added by walton for inclusive tax
1193 ELSIF p3(I) = 0 THEN
1194 vamt := vamt + bsln_amt;
1195 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by walton for inclusive tax
1196 END IF;
1197 IF p4(I) < I AND p4(I) NOT IN (-1,0) THEN
1198 vamt := vamt + NVL(tax_amt_tab(p4(I)),0);
1199 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P4(I)),0); --added by walton for inclusive tax
1200 ELSIF p4(I) = 0 THEN
1201 vamt := vamt + bsln_amt;
1202 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by walton for inclusive tax
1203 END IF;
1204 IF p5(I) < I AND p5(I) NOT IN (-1,0) THEN
1205 vamt := vamt + NVL(tax_amt_tab(p5(I)),0);
1206 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P5(I)),0); --added by walton for inclusive tax
1207 ELSIF p5(I) = 0 THEN
1208 vamt := vamt + bsln_amt;
1209 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by walton for inclusive tax
1210 END IF;
1211 -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
1212 -- start bug 5228046
1213 IF P6(I) < I AND P6(I) NOT IN (-1,0) THEN
1214 vamt := vamt + NVL(tax_amt_tab(P6(I)),0);
1215 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P6(I)),0); --added by walton for inclusive tax
1216 ELSIF P6(I) = 0 THEN
1217 vamt := vamt + bsln_amt;
1218 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by walton for inclusive tax
1219 END IF;
1220 IF p7(I) < I AND p7(I) NOT IN (-1,0) THEN
1221 vamt := vamt + NVL(tax_amt_tab(p7(I)),0);
1222 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P7(I)),0); --added by walton for inclusive tax
1223 ELSIF p7(I) = 0 THEN
1224 vamt := vamt + bsln_amt;
1225 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by walton for inclusive tax
1226 END IF;
1227 IF p8(I) < I AND p8(I) NOT IN (-1,0) THEN
1228 vamt := vamt + NVL(tax_amt_tab(p8(I)),0);
1229 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P8(I)),0); --added by walton for inclusive tax
1230 ELSIF p8(I) = 0 THEN
1231 vamt := vamt + bsln_amt;
1232 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by walton for inclusive tax
1233 END IF;
1234 IF p9(I) < I AND p9(I) NOT IN (-1,0) THEN
1235 vamt := vamt + NVL(tax_amt_tab(p9(I)),0);
1236 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P9(I)),0); --added by walton for inclusive tax
1237 ELSIF p9(I) = 0 THEN
1238 vamt := vamt + bsln_amt;
1239 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by walton for inclusive tax
1240 END IF;
1241 IF p10(I) < I AND p10(I) NOT IN (-1,0) THEN
1242 vamt := vamt + NVL(tax_amt_tab(p10(I)),0);
1243 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P10(I)),0); --added by walton for inclusive tax
1244 ELSIF p10(I) = 0 THEN
1245 vamt := vamt + bsln_amt;
1246 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by walton for inclusive tax
1247 END IF;
1248 -- end bug 5228046
1249 v_tax_amt := v_tax_amt + (vamt * (tax_rate_tab(I)/100));
1250 ln_tax_amt_nr:=ln_tax_amt_nr+(ln_vamt_nr*(tax_rate_tab(I)/100)); --added by walton for inclusive tax
1251 base_tax_amt_tab(I) := vamt;
1252 tax_amt_tab(I) := NVL(tax_amt_tab(I),0) + v_tax_amt;
1253 lt_tax_amt_non_rate_tab(I):=NVL(lt_tax_amt_non_rate_tab(I),0)+ln_tax_amt_nr; --added by walton for inclusive tax
1254 lt_tax_amt_rate_tax_tab(I):= tax_amt_tab(I); --added by walton for inclusive tax
1255 /*tax_amt_tab(I) := round(tax_amt_tab(I) ,round_factor_tab(I)); --added by csahoo for bug#6077133*/ --commented by walton for inclusive tax
1256 vamt := 0;
1257 v_tax_amt := 0;
1258 ln_tax_amt_nr:=0; --added by walton for inclusive tax
1259 ln_vamt_nr:=0; --added by walton for inclusive tax
1260 END IF;
1261
1262 ELSE
1263
1264 tax_amt_tab(I) := 0;
1265 base_tax_amt_tab(I) := 0;
1266
1267 END IF;
1268
1269 END LOOP;
1270
1271 FOR I IN 1..row_count LOOP
1272 IF end_date_tab( I ) <> 0 THEN
1273 IF tax_rate_tab(I) <> 0 THEN
1274 IF P1(I) > I THEN
1275 vamt := vamt + NVL(tax_amt_tab(P1(I)),0);
1276 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P1(I)),0); --added by walton for inclusive tax
1277 END IF;
1278 IF p2(I) > I THEN
1279 vamt := vamt + NVL(tax_amt_tab(p2(I)),0);
1280 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P2(I)),0); --added by walton for inclusive tax
1281 END IF;
1282 IF p3(I) > I THEN
1283 vamt := vamt + NVL(tax_amt_tab(p3(I)),0);
1284 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P3(I)),0); --added by walton for inclusive tax
1285 END IF;
1286 IF p4(I) > I THEN
1287 vamt := vamt + NVL(tax_amt_tab(p4(I)),0);
1288 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P4(I)),0); --added by walton for inclusive tax
1289 END IF;
1290 IF p5(I) > I THEN
1291 vamt := vamt + NVL(tax_amt_tab(p5(I)),0);
1292 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P5(I)),0); --added by walton for inclusive tax
1293 END IF;
1294 -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
1295 -- start bug 5228046
1296 IF P6(I) > I THEN
1297 vamt := vamt + NVL(tax_amt_tab(P6(I)),0);
1298 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P6(I)),0); --added by walton for inclusive tax
1299 END IF;
1300 IF p7(I) > I THEN
1301 vamt := vamt + NVL(tax_amt_tab(p7(I)),0);
1302 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P7(I)),0); --added by walton for inclusive tax
1303 END IF;
1304 IF p8(I) > I THEN
1305 vamt := vamt + NVL(tax_amt_tab(p8(I)),0);
1306 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P8(I)),0); --added by walton for inclusive tax
1307 END IF;
1308 IF p9(I) > I THEN
1309 vamt := vamt + NVL(tax_amt_tab(p9(I)),0);
1310 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P9(I)),0); --added by walton for inclusive tax
1311 END IF;
1312 IF p10(I) > I THEN
1313 vamt := vamt + NVL(tax_amt_tab(p10(I)),0);
1314 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P10(I)),0); --added by walton for inclusive tax
1315 END IF;
1316 -- end bug 5228046
1317 base_tax_amt_tab(I) := vamt;
1318 v_tax_amt := v_tax_amt + (vamt * (tax_rate_tab(I)/100));
1319 ln_tax_amt_nr:=ln_tax_amt_nr+(ln_vamt_nr * (tax_rate_tab(I)/100)); --added by walton for inclusive tax
1320 IF vamt <> 0 THEN
1321 base_tax_amt_tab(I) := base_tax_amt_tab(I) + vamt;
1322 END IF;
1323 tax_amt_tab(I) := NVL(tax_amt_tab(I),0) + v_tax_amt;
1324 lt_tax_amt_non_rate_tab(I):=NVL(lt_tax_amt_non_rate_tab(I),0)+ln_tax_amt_nr; --added by walton for inclusive tax
1325 lt_tax_amt_rate_tax_tab(I):= tax_amt_tab(I); --added by walton for inclusive tax
1326 vamt := 0;
1327 v_tax_amt := 0;
1328 ln_vamt_nr :=0; --added by walton for inclusive tax
1329 ln_tax_amt_nr :=0; --added by walton for inclusive tax
1330 END IF;
1331
1332 ELSE
1333
1334 base_tax_amt_tab(I) := vamt;
1335 tax_amt_tab(I) := 0;
1336 END IF;
1337
1338 END LOOP;
1339
1340 FOR counter IN 1 .. max_iter LOOP
1341 vamt := 0;
1342 v_tax_amt := 0;
1343 ln_vamt_nr:= 0; --added by walton for inclusive tax
1344 ln_tax_amt_nr:=0; --added by walton for inclusive tax
1345
1346 FOR i IN 1 .. row_count LOOP
1347
1348 /*
1349 || Modified by aiyer for the fwd porting bug 4691616.
1350 || The following if clause will restrict the taxes whose tax_rate is null
1351 || i.e when tax_rate is null, tax_rate_tab(i) is 0.
1352 */
1353 IF ( tax_rate_tab( i ) <> 0 OR
1354 tax_rate_zero_tab(I) = -9999
1355 ) AND
1356 end_date_tab( I ) <> 0
1357 THEN
1358
1359 IF tax_type_tab( I ) = 1 THEN
1360 --Added by walton for inclusive tax on 08-Dec-07
1361 ------------------------------------------------
1362 IF ln_assessable_value =1
1363 THEN
1364 v_amt:=1;
1365 ln_bsln_amt_nr :=0;
1366 ELSE
1367 v_amt :=0;
1368 ln_bsln_amt_nr :=ln_assessable_value;
1369 END IF;
1370 ------------------------------------------------
1371 /*v_amt := p_assessable_value;*/ --commented out by walton for inclusive tax
1372 ELSIF tax_type_tab(I) = 4 THEN
1373 --Added by walton for inclusive tax on 08-Dec-07
1374 ------------------------------------------------
1375 IF ln_vat_assessable_value =1
1376 THEN
1377 v_amt:=1;
1378 ln_bsln_amt_nr :=0;
1379 ELSE
1380 v_amt :=0;
1381 ln_bsln_amt_nr :=ln_vat_assessable_value;
1382 END IF;
1383 ------------------------------------------------
1384 /*v_amt := p_vat_assessable_value;*/ --commented out by walton for inclusive tax
1385
1386 --Added by walton for inclusive tax
1387 -------------------------------------
1388 ELSIF tax_type_tab(I) = 6 THEN
1389 v_amt:=0;
1390 ln_bsln_amt_nr :=0;
1391 -------------------------------------
1392 ELSE
1393 IF p_assessable_value IN ( 0, -1 ) OR tax_type_tab( I ) <> 1 THEN
1394 /* v_amt := p_tax_amount;*/
1395 v_amt:=1; --Added by walton for inclusive tax
1396 ln_bsln_amt_nr :=0; --Added by walton for inclusive tax
1397 ELSIF p_vat_assessable_value IN ( 0, -1 ) OR tax_type_tab( I ) <> 4 THEN
1398 /* v_amt := p_tax_amount;*/
1399 v_amt:=1; --Added by walton for inclusive tax
1400 ln_bsln_amt_nr :=0; --Added by walton for inclusive tax
1401 END IF;
1402 END IF;
1403
1404 IF P1( i ) <> -1 THEN
1405 IF P1( i ) <> 0 THEN
1406 vamt := vamt + tax_amt_tab( P1( I ) );
1407 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P1(I)),0); --added by walton for inclusive tax
1408 ELSIF P1(i) = 0 THEN
1409 vamt := vamt + v_amt;
1410 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by walton for inclusive tax
1411 END IF;
1412 END IF;
1413
1414 IF p2( i ) <> -1 THEN
1415 IF p2( i ) <> 0 THEN
1416 vamt := vamt + tax_amt_tab( p2( I ) );
1417 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P2(I)),0); --added by walton for inclusive tax
1418 ELSIF p2(i) = 0 THEN
1419 vamt := vamt + v_amt;
1420 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by walton for inclusive tax
1421 END IF;
1422 END IF;
1423 IF p3( i ) <> -1 THEN
1424 IF p3( i ) <> 0 THEN
1425 vamt := vamt + tax_amt_tab( p3( I ) );
1426 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P3(I)),0); --added by walton for inclusive tax
1427 ELSIF p3(i) = 0 THEN
1428 vamt := vamt + v_amt;
1429 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by walton for inclusive tax
1430 END IF;
1431 END IF;
1432
1433 IF p4( i ) <> -1 THEN
1434 IF p4( i ) <> 0 THEN
1435 vamt := vamt + tax_amt_tab( p4( i ) );
1436 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P4(I)),0); --added by walton for inclusive tax
1437 ELSIF p4(i) = 0 THEN
1438 vamt := vamt + v_amt;
1439 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by walton for inclusive tax
1440 END IF;
1441 END IF;
1442
1443 IF p5( i ) <> -1 THEN
1444 IF p5( i ) <> 0 THEN
1445 vamt := vamt + tax_amt_tab( p5( i ) );
1446 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P5(I)),0); --added by walton for inclusive tax
1447 ELSIF p5(i) = 0 THEN
1448 vamt := vamt + v_amt;
1449 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by walton for inclusive tax
1450 END IF;
1451 END IF;
1452
1453 -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
1454 -- start bug 5228046
1455 IF P6( i ) <> -1 THEN
1456 IF P6( i ) <> 0 THEN
1457 vamt := vamt + tax_amt_tab( P6( I ) );
1458 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P6(I)),0); --added by walton for inclusive tax
1459 ELSIF P6(i) = 0 THEN
1460 vamt := vamt + v_amt;
1461 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by walton for inclusive tax
1462 END IF;
1463 END IF;
1464
1465 IF p7( i ) <> -1 THEN
1466 IF p7( i ) <> 0 THEN
1467 vamt := vamt + tax_amt_tab( p7( I ) );
1468 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P7(I)),0); --added by walton for inclusive tax
1469 ELSIF p7(i) = 0 THEN
1470 vamt := vamt + v_amt;
1471 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by walton for inclusive tax
1472 END IF;
1473 END IF;
1474 IF p8( i ) <> -1 THEN
1475 IF p8( i ) <> 0 THEN
1476 vamt := vamt + tax_amt_tab( p8( I ) );
1477 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P8(I)),0); --added by walton for inclusive tax
1478 ELSIF p8(i) = 0 THEN
1479 vamt := vamt + v_amt;
1480 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by walton for inclusive tax
1481 END IF;
1482 END IF;
1483
1484 IF p9( i ) <> -1 THEN
1485 IF p9( i ) <> 0 THEN
1486 vamt := vamt + tax_amt_tab( p9( i ) );
1487 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P9(I)),0); --added by walton for inclusive tax
1488 ELSIF p9(i) = 0 THEN
1489 vamt := vamt + v_amt;
1490 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by walton for inclusive tax
1491 END IF;
1492 END IF;
1493
1494 IF p10( i ) <> -1 THEN
1495 IF p10( i ) <> 0 THEN
1496 vamt := vamt + tax_amt_tab( p10( i ) );
1497 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P10(I)),0); --added by walton for inclusive tax
1498 ELSIF p10(i) = 0 THEN
1499 vamt := vamt + v_amt;
1500 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by walton for inclusive tax
1501 END IF;
1502 END IF;
1503
1504 -- end bug 5228046
1505
1506
1507 base_tax_amt_tab(I) := vamt;
1508 tax_target_tab(I) := vamt;
1509
1510 --------------------------------------------------------------------------------------
1511 /*Change History: jai_cmn_tax_defaultation_pkg
1512 Last Modified By Jagdish Bhosle. 2001/04/05
1513 The follow check will ensure that for Bond reg. Txns
1514 excise duty will not be added to original Line amount. */
1515 --------------------------------------------------------------------------------------
1516 IF (v_register_code='BOND_REG') THEN --- Added By Jagdish 2001/04/05
1517 IF counter = max_iter AND tax_type_tab( I ) NOT IN ( 1, 2 ) THEN
1518 v_func_tax_amt := v_tax_amt + ( vamt * ( tax_rate_tab( i )/100));
1519 v_tax_amt := v_tax_amt + ( vamt * ( tax_rate_tab( i )/100));
1520 ln_tax_amt_nr:=ln_tax_amt_nr+(ln_vamt_nr*(tax_rate_tab(i)/100)); --added by walton for inclusive
1521 END IF;
1522
1523 ELSE
1524 v_func_tax_amt := v_tax_amt + ( vamt * ( tax_rate_tab( i )/100));
1525 v_tax_amt := v_tax_amt + ( vamt * ( tax_rate_tab( i )/100));
1526 ln_tax_amt_nr:=ln_tax_amt_nr+(ln_vamt_nr*(tax_rate_tab(i)/100)); --added by walton for inclusive
1527 END IF; -- End of Addition Jagdish 2001/04/05
1528
1529 ELSIF tax_rate_tab(I) = 0 THEN
1530 base_tax_amt_tab(I) := tax_amt_tab(i);
1531 v_tax_amt := tax_amt_tab( i );
1532 ln_tax_amt_nr:=lt_tax_amt_non_rate_tab(i);
1533 tax_target_tab(I) := v_tax_amt;
1534 ELSIF end_date_tab( I ) = 0 THEN
1535 tax_amt_tab(I) := 0;
1536 base_tax_amt_tab(I) := 0;
1537 tax_target_tab(I) := 0;
1538 END IF;
1539
1540 tax_amt_tab( I ) := NVL( v_tax_amt, 0 );
1541 lt_tax_amt_rate_tax_tab(I) := tax_amt_tab(I); --added by walton for inclusive tax
1542 lt_tax_amt_non_rate_tab(I):=ln_tax_amt_nr; --added by walton for inclusive tax
1543 /*tax_amt_tab(I) := round(tax_amt_tab(I) ,round_factor_tab(I)); --added by csahoo for bug#6077133*/--commented by walton for inclusive tax
1544 func_tax_amt_tab(I) := NVL(v_func_tax_amt,0);
1545
1546 IF counter = max_iter THEN
1547 IF end_date_tab(I) = 0 THEN
1548 tax_amt_tab( i ) := 0;
1549 func_tax_amt_tab(i) := 0;
1550 END IF;
1551 END IF;
1552
1553 vamt := 0;
1554 v_amt := 0;
1555 v_tax_amt := 0;
1556 v_func_tax_amt := 0;
1557 ln_vamt_nr :=0; --added by walton for inclusive tax
1558 ln_tax_amt_nr:=0; --added by walton for inclusive tax
1559
1560 END LOOP;
1561
1562 END LOOP;
1563
1564 --Added by walton for inclusive tax
1565 ---------------------------------------------------------------------------------------
1566 FOR I IN 1 .. ROW_COUNT --Compute Factor
1567 LOOP
1568 IF lt_inclusive_tax_tab(I) = 'Y'
1569 THEN
1570 ln_total_tax_per_rupee := ln_total_tax_per_rupee + nvl(lt_tax_amt_rate_tax_tab(I),0) ;
1571 ln_total_non_rate_tax := ln_total_non_rate_tax + nvl(lt_tax_amt_non_rate_tab(I),0);
1572 END IF;
1573 END LOOP; --End Compute Factor
1574
1575 ln_total_tax_per_rupee := ln_total_tax_per_rupee + 1;
1576
1577 IF ln_total_tax_per_rupee <> 0
1578 THEN
1579 ln_exclusive_price := (NVL(p_tax_amount,0) - ln_total_non_rate_tax ) / ln_total_tax_per_rupee;
1580 END If;
1581
1582 FOR i in 1 .. row_count --Compute Tax Amount
1583 Loop
1584 tax_amt_tab (i) := (lt_tax_amt_rate_tax_tab(I) * ln_exclusive_price ) + lt_tax_amt_non_rate_tab(I);
1585 tax_amt_tab(I) := round(tax_amt_tab(I) ,round_factor_tab(I));
1586 END LOOP; --End Compute Tax Amount
1587 --------------------------------------------------------------------------------------------------------
1588
1589 --Added by Kevin Cheng for Retroactive Price 2008/01/13
1590 --===========================================================================================================
1591 ELSIF pv_retroprice_changed = 'Y'
1592 THEN
1593
1594 --Ramananda for File.Sql.35
1595 bsln_amt := p_tax_amount ;
1596 v_conversion_rate := 0;
1597 v_currency_conv_factor := p_currency_conv_factor;
1598 ln_base := 0 ;
1599
1600 IF transaction_name <> 'CRM_QUOTE' THEN -- Vijay Shankar for Bug# 2837970
1601 IF v_debug THEN fnd_file.put_line(fnd_file.log, ' transaction_name -> '||transaction_name); END IF;
1602
1603 --2001/03/30 Manohar Mishra
1604 /*Start of Addition*/
1605 OPEN get_header_info_cur;
1606 FETCH get_header_info_cur INTO v_organization_id, v_location_id, v_batch_source_id;
1607 CLOSE get_header_info_cur;
1608
1609 OPEN get_register_code_cur(v_organization_id, v_location_id, v_batch_source_id);
1610 FETCH get_register_code_cur INTO v_register_code;
1611 CLOSE get_register_code_cur;
1612
1613 -- Vijay Shankar for Bug# 2837970
1614 ELSE -- this should get executed when tax defaultation is for CRM_QUOTE
1615 v_register_code := null;
1616 END IF;
1617 --2001/03/30 Manohar Mishra
1618
1619 /*End of Addition*/
1620
1621
1622 /** bgowrava for forward porting bug#5631784*/
1623 if p_tax_category_id is null
1624 and (p_threshold_tax_cat_id is null or p_threshold_tax_cat_id <0) then
1625 /** Both driving parameter tax_category_id and threshol_tax_category_id are invalid hence no need to do anything */
1626 return;
1627 end if;
1628
1629 if nvl(p_action, jai_constants.default_taxes) = jai_constants.default_taxes then
1630 /** Assign tax defaultation cursor object to refc_tax_cur reference by using. Call to get_tax_cat_taxes_cur will return
1631 a reference cursor */
1632
1633 if p_threshold_tax_cat_id is not null and p_threshold_tax_cat_id > 0 then
1634 /*
1635 || Cursor to check if same taxes exists in both tax categories
1636 */
1637 ln_dup_tax_exists := null;
1638 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Open/Close/Fetch cursor c_chk_tax_duplication');*/ --commented by bgowrava for bug#5631784
1639 open c_chk_tax_duplication;
1640 fetch c_chk_tax_duplication into ln_dup_tax_exists;
1641 close c_chk_tax_duplication;
1642
1643 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'ln_dup_tax_exists='||ln_dup_tax_exists);*/ --commented by bgowrava for bug#5631784
1644
1645 if ln_dup_tax_exists is not null
1646 or (nvl(p_threshold_tax_cat_id,-1) = p_tax_category_id)
1647 then
1648
1649 fnd_message.set_name('JA', 'JAI_DUP_TAX_IN_TAX_CAT');
1650 app_exception.raise_exception ;
1651
1652 end if;
1653
1654 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Open/Close/Fetch cursor c_get_max_tax_line');*/ --commented by bgowrava for bug#5631784
1655 open c_get_max_tax_line;
1656 fetch c_get_max_tax_line into ln_max_tax_line;
1657 close c_get_max_tax_line ;
1658
1659 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Open/Close/Fetch cursor c_get_max_rgm_tax_line');*/ --commented by bgowrava for bug#5631784
1660 open c_get_max_rgm_tax_line;
1661 fetch c_get_max_rgm_tax_line into ln_max_rgm_tax_line;
1662 close c_get_max_rgm_tax_line ;
1663
1664 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'ln_max_tax_line='||ln_max_tax_line||', ln_max_rgm_tax_line='||ln_max_rgm_tax_line);*/ --commented by bgowrava for bug#5631784
1665
1666 end if;
1667
1668 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Opening ref cursor');*/ --commented by bgowrava for bug#5631784
1669 get_tax_cat_taxes_cur ( p_tax_category_id => p_tax_category_id
1670 , p_threshold_tax_cat_id => p_threshold_tax_cat_id
1671 , p_max_tax_line => ln_max_tax_line
1672 , p_max_rgm_tax_line => ln_max_rgm_tax_line
1673 , p_refc_tax_cat_taxes_cur => refc_tax_cur
1674 , pv_retroprice_changed => pv_retroprice_changed --Added by Kevin Cheng for Retroactive Price 2008/01/14
1675 );
1676
1677 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Cursor is opened');*/ --commented by bgowrava for bug#5631784
1678
1679 elsif p_action = jai_constants.recalculate_taxes then
1680 /**
1681 Following is a dynamic sql string which can be modifed as per requirement
1682
1683 The sql has four place holders defined as below
1684 $$EXTRA_SELECT_COLUMN_LIST$$ - Use this place holder to select additional columns in the sql.
1685 You must also change corrosponding fetch statements and the record being used for fetch.
1686 SELECT statement above should also be changed to include the newly added columns
1687 as they are sharing a common cursor and fetch record.
1688
1689 $$TAX_SOURCE_TABLE$$ - At runtime this placeholder must be replaced with name of
1690 source table to be used for recalculation
1691 $$SOURCE_TABLE_FILTER$$ - At runtime, this place holder must represent a boolean condition
1692 which can filter required rows from the source table
1693 for recalculation. It must be the first condition and should never
1694 start with either AND or OR
1695 $$ADDITIONAL_WHERE_CLAUSE$$ - Replace the placeholder with additional conditions if any.
1696 The condition must start with either AND or OR keyword
1697 $$ADDITIONAL_ORDER_BY$$ - Replace the placeholder with list of columns and order sequence, if required.
1698 Column list must start with comma (,)
1699 If any of this placeholder is not required to be used it must be replaced with a null value as below
1700 replace ( lv_recalculation_sql
1701 , '$$EXTRA_SELECT_COLUMN_LIST$$'
1702 , ''
1703 );
1704 */
1705 lv_recalculation_sql :=
1706 ' select a.tax_id
1707 , a.tax_line_no lno
1708 , a.precedence_1 p_1
1709 , a.precedence_2 p_2
1710 , a.precedence_3 p_3
1711 , a.precedence_4 p_4
1712 , a.precedence_5 p_5
1713 , a.precedence_6 p_6
1714 , a.precedence_7 p_7
1715 , a.precedence_8 p_8
1716 , a.precedence_9 p_9
1717 , a.precedence_10 p_10
1718 , a.tax_rate
1719 , a.tax_amount
1720 , b.uom_code
1721 , b.end_date valid_date
1722 , DECODE(rttv.regime_code, '''||jai_constants.vat_regime||''', 4, /* added by ssumaith - bug# 4245053*/
1723 DECODE(UPPER(b.tax_type), ''EXCISE'' , 1
1724 , ''ADDL. EXCISE'', 1
1725 , ''OTHER EXCISE'', 1
1726 , ''TDS'' , 2
1727 , ''EXCISE_EDUCATION_CESS'',1
1728 , '''||JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS||''' , 1 /*bduvarag for the bug#5989740*/
1729 , ''CVD_EDUCATION_CESS'' ,1
1730 , '''||JAI_CONSTANTS.TAX_TYPE_SH_CVD_EDU_CESS||''' , 1 /*bduvarag for the bug#5989740*/
1731 , 0
1732 )
1733 ) tax_type_val
1734 , b.mod_cr_percentage
1735 , b.vendor_id
1736 , b.tax_type
1737 , nvl(b.rounding_factor,0) rounding_factor
1738 , b.adhoc_flag
1739 $$EXTRA_SELECT_COLUMN_LIST$$
1740 from $$TAX_SOURCE_TABLE$$ a
1741 , JAI_CMN_TAXES_ALL b
1742 , jai_regime_tax_types_v rttv
1743 where $$SOURCE_TABLE_FILTER$$
1744 and rttv.tax_type (+) = b.tax_type
1745 and a.tax_id = b.tax_id $$ADDITIONAL_WHERE_CLAUSE$$
1746 order by a.tax_line_no $$ADDITIONAL_ORDER_BY$$';
1747
1748
1749 /** No extra columns required. Dummy column (NULL) tax_category_id needs to be added in the last as same record (rec) is being used
1750 when fetching the cursor. If there is a need to override this default behaviour then please replace these place holder with
1751 desired strings which can be evaluated at runtime by sql-engine
1752 */
1753 lv_recalculation_sql :=
1754 replace ( lv_recalculation_sql
1755 , '$$EXTRA_SELECT_COLUMN_LIST$$'
1756 , ',null tax_category_id'
1757 );
1758
1759 /** No additional filtering required */
1760 lv_recalculation_sql :=
1761 replace ( lv_recalculation_sql
1762 , '$$ADDITIONAL_WHERE_CLAUSE$$'
1763 , ''
1764 );
1765
1766 /** No additional sorting required */
1767 lv_recalculation_sql :=
1768 replace ( lv_recalculation_sql
1769 , '$$ADDITIONAL_ORDER_BY$$'
1770 , ''
1771 );
1772
1773 if upper(p_source_trx_type) = jai_constants.source_ttype_delivery then
1774
1775 /** replace the correct tax source table */
1776 lv_recalculation_sql :=
1777 replace ( lv_recalculation_sql
1778 , '$$TAX_SOURCE_TABLE$$'
1779 , 'JAI_OM_WSH_LINE_TAXES'
1780 );
1781 /** replace join condition */
1782 lv_recalculation_sql :=
1783 replace ( lv_recalculation_sql
1784 , '$$SOURCE_TABLE_FILTER$$'
1785 , 'a.delivery_detail_id = ' || p_line_id
1786 );
1787 elsif upper(p_source_trx_type) = jai_constants.bill_only_invoice then
1788
1789 /** For bill_only_invoice tax source table is ja_in_ra_cust_trx_tax_lines*/
1790 lv_recalculation_sql :=
1791 replace ( lv_recalculation_sql
1792 , '$$TAX_SOURCE_TABLE$$'
1793 , 'JAI_AR_TRX_TAX_LINES'
1794 );
1795 /** replace join condition */
1796 lv_recalculation_sql :=
1797 replace ( lv_recalculation_sql
1798 , '$$SOURCE_TABLE_FILTER$$'
1799 , 'a.link_to_cust_trx_line_id = ' || p_line_id
1800 );
1801 /*
1802 elsif upper(pv_tax_source_table) = '<some tax table>' then
1803 ...
1804 ...
1805 */
1806
1807 -- Date 24-Apr-2007 Added by SACSETHI for bug 6012570 (5876390)
1808 -- in This , Recalculation will be happen in Draft invoice
1809 ---------------------------------------------------------
1810 elsif upper(p_source_trx_type) = jai_constants.PA_DRAFT_INVOICE then
1811
1812 lv_recalculation_sql :=
1813 replace ( lv_recalculation_sql
1814 , 'a.tax_amount'
1815 , 'a.tax_amt'
1816 );
1817
1818 lv_recalculation_sql :=
1819 replace ( lv_recalculation_sql
1820 , '$$TAX_SOURCE_TABLE$$'
1821 , 'JAI_CMN_DOCUMENT_TAXES'
1822 );
1823 /** replace join condition */
1824 lv_recalculation_sql :=
1825 replace ( lv_recalculation_sql
1826 , '$$SOURCE_TABLE_FILTER$$'
1827 , 'a.SOURCE_DOC_LINE_ID = ' || p_line_id || ' and SOURCE_DOC_TYPE ='''|| jai_constants.PA_DRAFT_INVOICE || ''''
1828 );
1829
1830 -- Added by Jason Liu for standalone invoice on 2007/08/23
1831 ----------------------------------------------------------------------
1832 ELSIF upper(p_source_trx_type) =
1833 jai_constants.G_AP_STANDALONE_INVOICE
1834 THEN
1835
1836 lv_recalculation_sql :=
1837 REPLACE( lv_recalculation_sql
1838 , 'a.tax_amount'
1839 , 'a.tax_amt'
1840 );
1841
1842 lv_recalculation_sql :=
1843 REPLACE( lv_recalculation_sql
1844 , '$$TAX_SOURCE_TABLE$$'
1845 , 'JAI_CMN_DOCUMENT_TAXES'
1846 );
1847 -- replace join condition
1848 lv_recalculation_sql :=
1849 REPLACE( lv_recalculation_sql
1850 , '$$SOURCE_TABLE_FILTER$$'
1851 , 'a.SOURCE_DOC_LINE_ID = ' || p_line_id ||
1852 ' and SOURCE_DOC_TYPE ='''||
1853 jai_constants.G_AP_STANDALONE_INVOICE || ''''
1854 );
1855 ----------------------------------------------------------------------
1856 end if; /*pv_tax_source_table*/
1857
1858 /**
1859 When control comes here, a valid sql statement hold by variable lv_recalculate_sql
1860 must be ready to execute.
1861
1862 open a dynamic select statement using OPEN-FOR statement
1863 */
1864 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'TAX RECALCULATION SQL STATEMENT');
1865 jai_cmn_debug_contexts_pkg.print (ln_reg_id, lv_recalculation_sql);
1866 jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Opening ref cursor with dynamic sql'); */--commented by bgowrava for bug#5631784
1867
1868 open refc_tax_cur for lv_recalculation_sql;
1869
1870 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Cursor is opened with lv_recalculation_sql');*/ --commented by bgowrava for bug#5631784
1871
1872 end if; /** RECALCULATE */
1873
1874 /** Clear the tax table */
1875 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Before fetching cursor rows and starting loop');*/ --commented by bgowrava for bug#5631784
1876 lt_tax_table.delete;
1877
1878 loop
1879 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'In Loop, row_count='||row_count||', lt_tax_table.count='||lt_tax_table.count);*/ --commented by bgowrava for bug#5631784
1880 fetch refc_tax_cur into rec;
1881 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Post Fetch refc_tax_cur, rec.tax_id='||rec.tax_id||', rec.tax_rate='||rec.tax_rate);*/ --commented by bgowrava for bug#5631784
1882 exit when refc_tax_cur%notfound;
1883
1884 /** End of bug 5631784*/
1885
1886
1887
1888 /** Add current record in the lt_tax_table for future use at the time of either UPDATE or INSERT into the tables*/
1889
1890 --FOR rec IN tax_cur(p_tax_category_id) LOOP
1891 lt_tax_table(lt_tax_table.count+1) := rec;
1892 p1(row_count) := nvl(rec.p_1,-1);
1893 p2(row_count) := nvl(rec.p_2,-1);
1894 p3(row_count) := nvl(rec.p_3,-1);
1895 p4(row_count) := nvl(rec.p_4,-1);
1896 p5(row_count) := nvl(rec.p_5,-1);
1897 p6(row_count) := nvl(rec.p_6,-1); -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
1898 p7(row_count) := nvl(rec.p_7,-1);
1899 p8(row_count) := nvl(rec.p_8,-1);
1900 p9(row_count) := nvl(rec.p_9,-1);
1901 p10(row_count) := nvl(rec.p_10,-1);
1902 tax_rate_tab(row_count) := NVL(rec.tax_rate,0);
1903
1904
1905 /*
1906 || The following code added by aiyer for the bug 4691616
1907 || Purpose:
1908 || rec.tax_rate = 0 means that tax_rate for such a tax line is actually zero (i.e it is not a replacement of null value)
1909 || So, when rec.tax_rate = 0, tax_rate_zero_tab is populated with -9999 to identify that this tax_line actually has tax_rate = 0
1910 || To calculate the BASE_TAX_AMOUNT of the taxes whose tax_rate is zero
1911 */
1912
1913 IF rec.tax_rate is null THEN
1914 /*
1915 ||Indicates qty based taxes
1916 */
1917 tax_rate_zero_tab(row_count) := 0;
1918
1919 ELSIF rec.tax_rate = 0 THEN
1920 /*
1921 ||Indicates 0% tax rate becasue a tax can have a rate as 0%.
1922 */
1923 tax_rate_zero_tab(row_count) := -9999;
1924
1925 ELSE
1926 tax_rate_zero_tab(row_count) := rec.tax_rate;
1927
1928 END IF;
1929
1930 tax_type_tab(row_count) := rec.tax_type_val;
1931 /*End of bug 4691616 */
1932 -- tax_amt_tab(row_count) := 0;
1933 /*added for bug#6498072, start*/
1934 --Comment out by Kevin Cheng
1935 /*IF p_action = jai_constants.recalculate_taxes AND --recalculate_taxes
1936 NVL(rec.adhoc_flag,'N') = 'Y' --adhoc_flag='Y'
1937 THEN
1938 tax_amt_tab(row_count) := nvl(rec.tax_amount,0) ;
1939 ELSE
1940 tax_amt_tab(row_count) := 0;
1941 END IF ;*/
1942 /*bug#6498072, end*/
1943
1944 --Added by Kevin Cheng -- for remain unchanged taxes
1945 --1, Ad hoc taxes
1946 --2, UOM based taxes
1947 --3, Assessable value base taxes (Excise/VAT)
1948 --4, Third party taxes
1949 --=================================================================================
1950 IF NVL(rec.adhoc_flag,'N') = 'Y' --Ad hoc
1951 THEN
1952 lv_tax_remain_flag := 'Y';
1953 ELSIF tax_rate_tab(row_count) = 0 AND rec.uom_code IS NOT NULL --UOM based
1954 THEN
1955 lv_tax_remain_flag := 'Y';
1956 ELSIF rec.tax_type_val = 1 AND p_assessable_value <> p_tax_amount --Excise assessable value based
1957 THEN
1958 lv_tax_remain_flag := 'Y';
1959 ELSIF rec.tax_type_val = 4 AND p_vat_assessable_value <> p_tax_amount --VAT assessable value based
1960 THEN
1961 lv_tax_remain_flag := 'Y';
1962 ELSIF rec.vendor_id <> p_vendor_id --Third party
1963 THEN
1964 lv_tax_remain_flag := 'Y';
1965 ELSE
1966 lv_tax_remain_flag := 'N';
1967 END IF;
1968
1969 IF lv_tax_remain_flag = 'Y'
1970 THEN
1971 --Get line location id from transaction_name
1972 IF SUBSTR( transaction_name, 1, 1 ) = 'R' THEN
1973 lv_transaction_name := 'RFQ';
1974 lv_start := 4;
1975 ELSIF SUBSTR( transaction_name, 1, 1 ) = 'S' THEN
1976 lv_transaction_name := 'RFQ';
1977 lv_start := 10;
1978 ELSIF SUBSTR( transaction_name, 1, 1 ) = 'Q' THEN
1979 lv_transaction_name := 'QUOTATION';
1980 lv_start := 10;
1981 ELSIF SUBSTR( transaction_name, 1, 1 ) = 'B' AND SUBSTR( transaction_name, 1, 8 ) <> 'BLANKETR' THEN
1982 lv_transaction_name := 'BLANKET';
1983 lv_start := 8;
1984 ELSIF SUBSTR( transaction_name, 1, 8 ) = 'BLANKETR' THEN
1985 lv_transaction_name := 'RFQ';
1986 lv_start := 9;
1987 ELSIF SUBSTR( transaction_name, 1, 1 ) = 'O' THEN
1988 lv_transaction_name := 'OTHERS';
1989 lv_start := 7;
1990 END IF;
1991
1992 lv_line_loc_id := TO_NUMBER( SUBSTR( transaction_name, lv_start, LENGTH( transaction_name )-( lv_start + 1 )));
1993 SELECT
1994 original_tax_amount
1995 INTO
1996 tax_amt_tab(row_count)
1997 FROM
1998 Jai_Retro_Tax_Changes jrtc
1999 WHERE jrtc.tax_id = rec.tax_id
2000 AND jrtc.line_change_id = (SELECT
2001 line_change_id
2002 FROM
2003 Jai_Retro_Line_Changes jrlc
2004 WHERE jrlc.line_location_id = lv_line_loc_id
2005 AND jrlc.doc_type IN ( 'RELEASE'
2006 , 'RECEIPT'
2007 , 'STANDARD PO'
2008 )
2009 AND jrlc.doc_version_number = (SELECT
2010 MAX(jrlc1.doc_version_number)
2011 FROM
2012 Jai_Retro_Line_Changes jrlc1
2013 WHERE jrlc1.line_location_id = lv_line_loc_id
2014 AND jrlc1.doc_type IN ( 'RELEASE'
2015 , 'RECEIPT'
2016 , 'STANDARD PO'
2017 )
2018 )
2019 );
2020
2021 tax_rate_tab(row_count) := 0;
2022 tax_rate_zero_tab(row_count) := 0;
2023 adhoc_flag_tab(row_count) := 'Y';
2024
2025 ELSIF lv_tax_remain_flag = 'N'
2026 THEN
2027 tax_amt_tab(row_count) := 0;
2028 adhoc_flag_tab(row_count):= rec.adhoc_flag ; /* rchandan bug#6030615 */
2029 END IF;
2030 --=================================================================================
2031
2032 round_factor_tab(row_count):=rec.rounding_factor; --added by csahoo for bug#6077133
2033 base_tax_amt_tab(row_count) := 0;
2034 --Comment out by Kevin Cheng
2035 --adhoc_flag_tab(row_count):=rec.adhoc_flag ; /* rchandan bug#6030615 */
2036
2037 --Comment out by Kevin Cheng
2038 /*IF tax_rate_tab(row_count) = 0
2039 AND rec.uom_code is not null --added by csahoo for bug#6498072
2040 THEN
2041 -- Start of bug 3749294
2042 \*
2043 Code added by aiyer for the bug 3749294
2044 Check whether an exact match exists between the transaction uom and the setup uom (obtained through the tax_category list).
2045 IF an exact match is found then the conversion rate is equal to 1 else the conversion rate between the two uom's would be
2046 determined and tax amounts,base_tax_amounts are calculated for defaultation.
2047 *\
2048 Inv_Convert.inv_um_conversion( p_uom_code, rec.uom_code, p_inventory_item_id, v_conversion_rate);
2049 IF NVL(v_conversion_rate, 0) <= 0 THEN
2050 -- pramasub start FP
2051 \*4281841 ..rchandan..start*\
2052 OPEN uom_class_cur(p_uom_code, rec.uom_code);
2053 FETCH uom_class_cur INTO uom_cls;
2054 IF uom_class_cur%FOUND THEN
2055 Inv_Convert.inv_um_conversion( p_uom_code, rec.uom_code, 0, v_conversion_rate);
2056 ELSE
2057 v_conversion_rate := 0;
2058 END IF;
2059 CLOSE uom_class_cur;
2060 \*4281841 ..rchandan..end*\
2061 -- pramasub end FP
2062 --Inv_Convert.inv_um_conversion( p_uom_code, rec.uom_code, 0, v_conversion_rate); commented by pramasub
2063 IF NVL(v_conversion_rate, 0) <= 0 THEN
2064
2065 \* for cash receipt there will be no lines. sacsethi for 6012570 (5876390) *\
2066 if (p_uom_code is null and p_inventory_item_id is null )
2067 and p_source_trx_type = jai_constants.ar_cash
2068 then --
2069 v_conversion_rate := 1;
2070
2071 \*
2072 Date 22-feb-2007 Added by SACSETHI for bug 6012570 (5876390)
2073 in This , Recalculation will be happen in Draft invoice
2074 *\
2075 elsif (p_uom_code is null and p_inventory_item_id is null )
2076 and p_source_trx_type= jai_constants.pa_draft_invoice
2077 then
2078 v_conversion_rate := 0;
2079
2080 else
2081 v_conversion_rate := 0;
2082 end if;
2083
2084 END IF;
2085 END IF;
2086 --tax_amt_tab(rec.lno) := nvl(rec.tax_amount * v_conversion_rate, 0) * p_line_quantity; -- cbabu for EnhancementBug# 2427465, compact code
2087 \*added for bug#6498072, start*\
2088 IF p_action = jai_constants.recalculate_taxes THEN
2089 tax_amt_tab(rec.lno) := nvl(rec.tax_amount * v_conversion_rate, 0) ;
2090 ELSE
2091 tax_amt_tab(rec.lno) := nvl(rec.tax_amount * v_conversion_rate, 0) * p_line_quantity ;
2092 END IF ;
2093 \*added for bug#6498072, end*\
2094 tax_amt_tab(rec.lno):=round(tax_amt_tab(rec.lno),round_factor_tab(rec.lno)); --added by csahoo for bug#6077133
2095 base_tax_amt_tab(rec.lno) := tax_amt_tab(rec.lno);
2096 -- End of bug 3749294
2097 END IF;*/
2098
2099 IF rec.valid_date IS NULL OR rec.valid_date >= SYSDATE THEN
2100 end_date_tab(row_count) := 1;
2101 ELSE
2102 tax_amt_tab(row_count) := 0;
2103 end_date_tab(row_count) := 0;
2104 END IF;
2105 row_count := row_count + 1;
2106 END LOOP;
2107
2108 row_count := row_count - 1;
2109
2110 FOR I IN 1..row_count LOOP
2111 IF end_date_tab(I) <> 0 THEN
2112 IF tax_type_tab(I) = 1 THEN
2113 bsln_amt := p_assessable_value;
2114 ELSIF tax_type_tab(I) = 4 THEN
2115 bsln_amt := p_vat_assessable_value;
2116 ELSE
2117 bsln_amt := p_tax_amount;
2118 END IF;
2119
2120 IF tax_rate_tab(I) <> 0 THEN
2121 IF P1(I) < I AND P1(I) NOT IN (-1,0) THEN
2122 vamt := vamt + NVL(tax_amt_tab(P1(I)),0);
2123 ELSIF P1(I) = 0 THEN
2124 vamt := vamt + bsln_amt;
2125 END IF;
2126 IF p2(I) < I AND p2(I) NOT IN (-1,0) THEN
2127 vamt := vamt + NVL(tax_amt_tab(p2(I)),0);
2128 ELSIF p2(I) = 0 THEN
2129 vamt := vamt + bsln_amt;
2130 END IF;
2131 IF p3(I) < I AND p3(I) NOT IN (-1,0) THEN
2132 vamt := vamt + NVL(tax_amt_tab(p3(I)),0);
2133 ELSIF p3(I) = 0 THEN
2134 vamt := vamt + bsln_amt;
2135 END IF;
2136 IF p4(I) < I AND p4(I) NOT IN (-1,0) THEN
2137 vamt := vamt + NVL(tax_amt_tab(p4(I)),0);
2138 ELSIF p4(I) = 0 THEN
2139 vamt := vamt + bsln_amt;
2140 END IF;
2141 IF p5(I) < I AND p5(I) NOT IN (-1,0) THEN
2142 vamt := vamt + NVL(tax_amt_tab(p5(I)),0);
2143 ELSIF p5(I) = 0 THEN
2144 vamt := vamt + bsln_amt;
2145 END IF;
2146 -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
2147 -- start bug 5228046
2148 IF P6(I) < I AND P6(I) NOT IN (-1,0) THEN
2149 vamt := vamt + NVL(tax_amt_tab(P6(I)),0);
2150 ELSIF P6(I) = 0 THEN
2151 vamt := vamt + bsln_amt;
2152 END IF;
2153 IF p7(I) < I AND p7(I) NOT IN (-1,0) THEN
2154 vamt := vamt + NVL(tax_amt_tab(p7(I)),0);
2155 ELSIF p7(I) = 0 THEN
2156 vamt := vamt + bsln_amt;
2157 END IF;
2158 IF p8(I) < I AND p8(I) NOT IN (-1,0) THEN
2159 vamt := vamt + NVL(tax_amt_tab(p8(I)),0);
2160 ELSIF p8(I) = 0 THEN
2161 vamt := vamt + bsln_amt;
2162 END IF;
2163 IF p9(I) < I AND p9(I) NOT IN (-1,0) THEN
2164 vamt := vamt + NVL(tax_amt_tab(p9(I)),0);
2165 ELSIF p9(I) = 0 THEN
2166 vamt := vamt + bsln_amt;
2167 END IF;
2168 IF p10(I) < I AND p10(I) NOT IN (-1,0) THEN
2169 vamt := vamt + NVL(tax_amt_tab(p10(I)),0);
2170 ELSIF p10(I) = 0 THEN
2171 vamt := vamt + bsln_amt;
2172 END IF;
2173 -- end bug 5228046
2174 v_tax_amt := v_tax_amt + (vamt * (tax_rate_tab(I)/100));
2175 base_tax_amt_tab(I) := vamt;
2176 tax_amt_tab(I) := NVL(tax_amt_tab(I),0) + v_tax_amt;
2177 tax_amt_tab(I) := round(tax_amt_tab(I) ,round_factor_tab(I)); --added by csahoo for bug#6077133
2178 vamt := 0;
2179 v_tax_amt := 0;
2180 END IF;
2181
2182 ELSE
2183
2184 tax_amt_tab(I) := 0;
2185 base_tax_amt_tab(I) := 0;
2186
2187 END IF;
2188
2189 END LOOP;
2190
2191 FOR I IN 1..row_count LOOP
2192 IF end_date_tab( I ) <> 0 THEN
2193 IF tax_rate_tab(I) <> 0 THEN
2194 IF P1(I) > I THEN
2195 vamt := vamt + NVL(tax_amt_tab(P1(I)),0);
2196 END IF;
2197 IF p2(I) > I THEN
2198 vamt := vamt + NVL(tax_amt_tab(p2(I)),0);
2199 END IF;
2200 IF p3(I) > I THEN
2201 vamt := vamt + NVL(tax_amt_tab(p3(I)),0);
2202 END IF;
2203 IF p4(I) > I THEN
2204 vamt := vamt + NVL(tax_amt_tab(p4(I)),0);
2205 END IF;
2206 IF p5(I) > I THEN
2207 vamt := vamt + NVL(tax_amt_tab(p5(I)),0);
2208 END IF;
2209 -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
2210 -- start bug 5228046
2211 IF P6(I) > I THEN
2212 vamt := vamt + NVL(tax_amt_tab(P6(I)),0);
2213 END IF;
2214 IF p7(I) > I THEN
2215 vamt := vamt + NVL(tax_amt_tab(p7(I)),0);
2216 END IF;
2217 IF p8(I) > I THEN
2218 vamt := vamt + NVL(tax_amt_tab(p8(I)),0);
2219 END IF;
2220 IF p9(I) > I THEN
2221 vamt := vamt + NVL(tax_amt_tab(p9(I)),0);
2222 END IF;
2223 IF p10(I) > I THEN
2224 vamt := vamt + NVL(tax_amt_tab(p10(I)),0);
2225 END IF;
2226 -- end bug 5228046
2227 base_tax_amt_tab(I) := vamt;
2228 v_tax_amt := v_tax_amt + (vamt * (tax_rate_tab(I)/100));
2229 IF vamt <> 0 THEN
2230 base_tax_amt_tab(I) := base_tax_amt_tab(I) + vamt;
2231 END IF;
2232 tax_amt_tab(I) := NVL(tax_amt_tab(I),0) + v_tax_amt;
2233 tax_amt_tab(I) := round(tax_amt_tab(I) ,round_factor_tab(I)); --added by csahoo for bug#6077133
2234 vamt := 0;
2235 v_tax_amt := 0;
2236 END IF;
2237
2238 ELSE
2239
2240 base_tax_amt_tab(I) := vamt;
2241 tax_amt_tab(I) := 0;
2242 END IF;
2243
2244 END LOOP;
2245
2246 FOR counter IN 1 .. max_iter LOOP
2247 vamt := 0;
2248 v_tax_amt := 0;
2249
2250 FOR i IN 1 .. row_count LOOP
2251
2252 /*
2253 || Modified by aiyer for the fwd porting bug 4691616.
2254 || The following if clause will restrict the taxes whose tax_rate is null
2255 || i.e when tax_rate is null, tax_rate_tab(i) is 0.
2256 */
2257 IF ( tax_rate_tab( i ) <> 0 OR
2258 tax_rate_zero_tab(I) = -9999
2259 ) AND
2260 end_date_tab( I ) <> 0
2261 THEN
2262
2263 IF tax_type_tab( I ) = 1 THEN
2264 v_amt := p_assessable_value;
2265 ELSIF tax_type_tab(I) = 4 THEN
2266 v_amt := p_vat_assessable_value;
2267 ELSE
2268 IF p_assessable_value IN ( 0, -1 ) OR tax_type_tab( I ) <> 1 THEN
2269 v_amt := p_tax_amount;
2270 ELSIF p_vat_assessable_value IN ( 0, -1 ) OR tax_type_tab( I ) <> 4 THEN
2271 v_amt := p_tax_amount;
2272 END IF;
2273 END IF;
2274
2275 IF P1( i ) <> -1 THEN
2276 IF P1( i ) <> 0 THEN
2277 vamt := vamt + tax_amt_tab( P1( I ) );
2278 ELSIF P1(i) = 0 THEN
2279 vamt := vamt + v_amt;
2280 END IF;
2281 END IF;
2282
2283 IF p2( i ) <> -1 THEN
2284 IF p2( i ) <> 0 THEN
2285 vamt := vamt + tax_amt_tab( p2( I ) );
2286 ELSIF p2(i) = 0 THEN
2287 vamt := vamt + v_amt;
2288 END IF;
2289 END IF;
2290 IF p3( i ) <> -1 THEN
2291 IF p3( i ) <> 0 THEN
2292 vamt := vamt + tax_amt_tab( p3( I ) );
2293 ELSIF p3(i) = 0 THEN
2294 vamt := vamt + v_amt;
2295 END IF;
2296 END IF;
2297
2298 IF p4( i ) <> -1 THEN
2299 IF p4( i ) <> 0 THEN
2300 vamt := vamt + tax_amt_tab( p4( i ) );
2301 ELSIF p4(i) = 0 THEN
2302 vamt := vamt + v_amt;
2303 END IF;
2304 END IF;
2305
2306 IF p5( i ) <> -1 THEN
2307 IF p5( i ) <> 0 THEN
2308 vamt := vamt + tax_amt_tab( p5( i ) );
2309 ELSIF p5(i) = 0 THEN
2310 vamt := vamt + v_amt;
2311 END IF;
2312 END IF;
2313
2314 -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
2315 -- start bug 5228046
2316 IF P6( i ) <> -1 THEN
2317 IF P6( i ) <> 0 THEN
2318 vamt := vamt + tax_amt_tab( P6( I ) );
2319 ELSIF P6(i) = 0 THEN
2320 vamt := vamt + v_amt;
2321 END IF;
2322 END IF;
2323
2324 IF p7( i ) <> -1 THEN
2325 IF p7( i ) <> 0 THEN
2326 vamt := vamt + tax_amt_tab( p7( I ) );
2327 ELSIF p7(i) = 0 THEN
2328 vamt := vamt + v_amt;
2329 END IF;
2330 END IF;
2331 IF p8( i ) <> -1 THEN
2332 IF p8( i ) <> 0 THEN
2333 vamt := vamt + tax_amt_tab( p8( I ) );
2334 ELSIF p8(i) = 0 THEN
2335 vamt := vamt + v_amt;
2336 END IF;
2337 END IF;
2338
2339 IF p9( i ) <> -1 THEN
2340 IF p9( i ) <> 0 THEN
2341 vamt := vamt + tax_amt_tab( p9( i ) );
2342 ELSIF p9(i) = 0 THEN
2343 vamt := vamt + v_amt;
2344 END IF;
2345 END IF;
2346
2347 IF p10( i ) <> -1 THEN
2348 IF p10( i ) <> 0 THEN
2349 vamt := vamt + tax_amt_tab( p10( i ) );
2350 ELSIF p10(i) = 0 THEN
2351 vamt := vamt + v_amt;
2352 END IF;
2353 END IF;
2354
2355 -- end bug 5228046
2356
2357
2358 base_tax_amt_tab(I) := vamt;
2359 tax_target_tab(I) := vamt;
2360
2361 --------------------------------------------------------------------------------------
2362 /*Change History: jai_cmn_tax_defaultation_pkg
2363 Last Modified By Jagdish Bhosle. 2001/04/05
2364 The follow check will ensure that for Bond reg. Txns
2365 excise duty will not be added to original Line amount. */
2366 --------------------------------------------------------------------------------------
2367 IF (v_register_code='BOND_REG') THEN --- Added By Jagdish 2001/04/05
2368 IF counter = max_iter AND tax_type_tab( I ) NOT IN ( 1, 2) THEN
2369 v_func_tax_amt := v_tax_amt + ( vamt * ( tax_rate_tab( i )/100));
2370 v_tax_amt := v_tax_amt + ( vamt * ( tax_rate_tab( i )/100));
2371 END IF;
2372
2373 ELSE
2374 v_func_tax_amt := v_tax_amt + ( vamt * ( tax_rate_tab( i )/100));
2375 v_tax_amt := v_tax_amt + ( vamt * ( tax_rate_tab( i )/100));
2376 END IF; -- End of Addition Jagdish 2001/04/05
2377
2378 ELSIF tax_rate_tab(I) = 0 THEN
2379 base_tax_amt_tab(I) := tax_amt_tab(i);
2380 v_tax_amt := tax_amt_tab( i );
2381 tax_target_tab(I) := v_tax_amt;
2382 ELSIF end_date_tab( I ) = 0 THEN
2383 tax_amt_tab(I) := 0;
2384 base_tax_amt_tab(I) := 0;
2385 tax_target_tab(I) := 0;
2386 END IF;
2387
2388 tax_amt_tab( I ) := NVL( v_tax_amt, 0 );
2389 tax_amt_tab(I) := round(tax_amt_tab(I) ,round_factor_tab(I)); --added by csahoo for bug#6077133
2390 func_tax_amt_tab(I) := NVL(v_func_tax_amt,0);
2391
2392 IF counter = max_iter THEN
2393 IF end_date_tab(I) = 0 THEN
2394 tax_amt_tab( i ) := 0;
2395 func_tax_amt_tab(i) := 0;
2396 END IF;
2397 END IF;
2398
2399 vamt := 0;
2400 v_amt := 0;
2401 v_tax_amt := 0;
2402 v_func_tax_amt := 0;
2403
2404 END LOOP;
2405
2406 END LOOP;
2407
2408 END IF;
2409 --===========================================================================================================
2410
2411 row_count := 1;
2412
2413 -- this is the place where you hv to add your insert statements for transaction specific tables
2414 rec := null ; -- added by ssumaith - code review comments for TCS buG#6109941
2415 FOR i in 1.. lt_tax_table.count LOOP
2416 rec := lt_tax_table(i);
2417 -- ends additions by ssumaith - code review comments for TCS bug# 6109941
2418 IF tax_type_tab(row_count) <> 2 THEN
2419 v_tax_amt := v_tax_amt + NVL(tax_amt_tab(row_count),0);
2420 END IF;
2421
2422
2423 /** bgowrava for forward porting bug# 5631784 */
2424 if rec.mod_cr_percentage is not null and rec.mod_cr_percentage > 0 then
2425 v_modvat_flag := 'Y';
2426 elsif rec.mod_cr_percentage is null then
2427 v_modvat_flag := 'N';
2428 end if;
2429 /*end bug# 5631784 */
2430
2431 if v_debug then fnd_file.put_line(fnd_file.log, 'Before tr_name -> '||transaction_name); end if;
2432
2433 -- Vijay Shankar for Bug# 2837970
2434 IF transaction_name = 'CRM_QUOTE' THEN
2435
2436 if v_debug then fnd_file.put_line(fnd_file.log, 'Before insert into of tr_name -> '||transaction_name); end if;
2437
2438 INSERT INTO JAI_CRM_QUOTE_TAXES(quote_line_id, quote_header_id, shipment_id, tax_line_no,
2439 precedence_1, precedence_2, precedence_3, precedence_4, precedence_5,
2440 precedence_6, precedence_7, precedence_8, precedence_9, precedence_10, -- Date 31/10/2006 Bug 5228046 added by SACSETHI
2441 tax_id, tax_amount,
2442 base_tax_amount,
2443 func_tax_amount,
2444 creation_date, created_by, last_update_date,
2445 last_updated_by, last_update_login)
2446 VALUES ( p_line_id, p_header_id, p_operation_flag, row_count,
2447 rec.p_1, rec.p_2, rec.p_3, rec.p_4, rec.p_5,
2448 rec.p_6, rec.p_7, rec.p_8, rec.p_9, rec.p_10,
2449 rec.tax_id, ROUND(nvl(tax_amt_tab(row_count),0),REC.ROUNDING_FACTOR),
2450 decode(nvl(base_tax_amt_tab(row_count), 0), 0, nvl(tax_amt_tab(row_count),0), nvl(base_tax_amt_tab(row_count), 0)),
2451 (nvl(func_tax_amt_tab(row_count),0) * v_currency_conv_factor),
2452 nvl(p_creation_date, SYSDATE), nvl(p_created_by,1), nvl(p_last_update_date,SYSDATE) ,
2453 nvl(p_last_updated_by,1), nvl(p_last_update_login,1) );
2454
2455 -- end, Vijay Shankar for Bug# 2837970
2456 ELSIF transaction_name = 'SALES_ORDER' THEN
2457 INSERT INTO JAI_OM_OE_SO_TAXES(
2458 line_id, header_id, tax_line_no,
2459 precedence_1, precedence_2, precedence_3, precedence_4, precedence_5,
2460 precedence_6, precedence_7, precedence_8, precedence_9, precedence_10, -- Date 31/10/2006 Bug 5228046 added by SACSETHI
2461 tax_id, tax_rate, qty_rate, uom,
2462 tax_amount,
2463 base_tax_amount,
2464 func_tax_amount,
2465 creation_date, created_by, last_update_date,
2466 last_updated_by, last_update_login,
2467 tax_category_id -- cbabu for EnhancementBug# 2427465
2468 ) VALUES (
2469 p_line_id, p_header_id, row_count,
2470 rec.p_1, rec.p_2, rec.p_3, rec.p_4, rec.p_5,
2471 rec.p_6, rec.p_7, rec.p_8, rec.p_9, rec.p_10,
2472 rec.tax_id, rec.tax_rate, rec.tax_amount, rec.uom_code,
2473 ROUND(NVL(tax_amt_tab(row_count),0),REC.ROUNDING_FACTOR),
2474 DECODE(NVL(base_tax_amt_tab(row_count), 0), 0, NVL(tax_amt_tab(row_count),0), NVL(base_tax_amt_tab(row_count), 0)),
2475 (NVL(func_tax_amt_tab(row_count),0) * v_currency_conv_factor),
2476 p_creation_date, p_created_by, p_last_update_date,
2477 p_last_updated_by, p_last_update_login,
2478 p_tax_category_id -- cbabu for EnhancementBug# 2427465
2479 );
2480
2481 ELSIF transaction_name='INTERORG_XFER' THEN
2482 /*added by rchandan for bug#6030615*/
2483 BEGIN
2484 DECLARE
2485 v_modvat_flag VARCHAR2(1);
2486 BEGIN
2487 IF rec.mod_cr_percentage IS NOT NULL AND rec.mod_cr_percentage > 0 THEN
2488 v_modvat_flag := 'Y';
2489 ELSIF rec.mod_cr_percentage IS NULL THEN
2490 v_modvat_flag := 'N';
2491 END IF;
2492 -- bug 6436825
2493 IF REC.TAX_TYPE NOT IN ('Service', JAI_CONSTANTS.TAX_TYPE_SH_SERVICE_EDU_CESS,
2494 JAI_CONSTANTS.TAX_TYPE_SERVICE_EDU_CESS,
2495 JAI_CONSTANTS.TAX_TYPE_SH_CUSTOMS_EDU_CESS,
2496 JAI_CONSTANTS.TAX_TYPE_CUSTOMS_EDU_CESS,
2497 'CVD_EDUCATION_CESS', JAI_CONSTANTS.TAX_TYPE_SH_CVD_EDU_CESS,
2498 'Customs', 'CVD', 'ADDITIONAL_CVD' ,'TDS' , 'Modvat Recovery') THEN
2499
2500 INSERT INTO jai_cmn_document_taxes(
2501 DOC_TAX_ID,
2502 tax_line_no,
2503 tax_id ,
2504 tax_type,
2505 currency_code ,
2506 tax_rate ,
2507 qty_rate ,
2508 uom ,
2509 tax_amt ,
2510 func_tax_amt,
2511 modvat_flag,
2512 tax_category_id,
2513 source_doc_type ,
2514 source_doc_id ,
2515 source_doc_line_id ,
2516 source_table_name ,
2517 TAX_MODIFIED_BY ,
2518 adhoc_flag ,
2519 precedence_1 ,
2520 precedence_2 ,
2521 precedence_3 ,
2522 precedence_4 ,
2523 precedence_5 ,
2524 precedence_6 ,
2525 precedence_7 ,
2526 precedence_8 ,
2527 precedence_9 ,
2528 precedence_10 ,
2529 creation_date ,
2530 created_by ,
2531 last_update_date ,
2532 last_updated_by ,
2533 last_update_login )
2534 VALUES (
2535 jai_cmn_document_taxes_s.nextval ,
2536 v_line_num,
2537 rec.tax_id,
2538 rec.tax_type,
2539 p_currency,
2540 rec.tax_rate,
2541 rec.tax_amount,
2542 rec.uom_code,
2543 round(nvl(tax_amt_tab(row_count),0),rec.rounding_factor),
2544 round(NVL(func_tax_amt_tab(row_count),0) * v_currency_conv_factor,rec.rounding_factor),
2545 v_modvat_flag,
2546 p_tax_category_id ,
2547 'INTERORG_XFER',
2548 p_header_id,
2549 p_line_id,
2550 'MTL_MATERIAL_TRANSACTIONS_TEMP',
2551 NULL,
2552 rec.adhoc_flag,
2553 rec.p_1,
2554 rec.p_2,
2555 rec.p_3,
2556 rec.p_4,
2557 rec.p_5,
2558 rec.p_6,
2559 rec.p_7,
2560 rec.p_8,
2561 rec.p_9,
2562 rec.p_10,
2563 p_creation_date,
2564 p_created_by,
2565 p_last_update_date,
2566 p_last_updated_by,
2567 p_last_update_login
2568 );
2569 v_line_num:=nvl(v_line_num,1)+1;
2570 END IF;
2571 END;
2572 END;
2573
2574 ELSIF transaction_name = 'RMA_LEGACY_INSERT' THEN
2575
2576 -- This elsif added by Aparajita on 31-may-2002 for bug 2381492
2577 INSERT INTO JAI_OM_OE_RMA_TAXES (
2578 rma_line_id, tax_line_no,
2579 precedence_1, precedence_2, precedence_3, precedence_4,precedence_5,
2580 precedence_6, precedence_7, precedence_8, precedence_9,precedence_10, -- Date 31/10/2006 Bug 5228046 added by SACSETHI
2581 tax_id, tax_rate,
2582 qty_rate, uom, tax_amount,
2583 base_tax_amount,
2584 func_tax_amount, creation_date, created_by,
2585 last_update_date, last_updated_by, last_update_login
2586 ) VALUES (
2587 p_line_id, row_count,
2588 rec.p_1,rec.p_2, rec.p_3,rec.p_4, rec.p_5,
2589 rec.p_6,rec.p_7, rec.p_8,rec.p_9, rec.p_10,
2590 rec.tax_id, rec.tax_rate,
2591 rec.tax_amount, rec.uom_code, ROUND(nvl(tax_amt_tab(row_count),0), rec.rounding_factor),
2592 DECODE( NVL(base_tax_amt_tab(row_count), 0), 0, NVL(tax_amt_tab(row_count),0), NVL(base_tax_amt_tab(row_count), 0) ),
2593 nvl(func_tax_amt_tab(row_count),0) * v_currency_conv_factor, p_creation_date, p_created_by,
2594 p_last_update_date, p_last_updated_by, p_last_update_login
2595 );
2596
2597 ELSIF transaction_name = 'SO_LINES_UPDATE' THEN
2598
2599 UPDATE JAI_OM_OE_SO_TAXES
2600 SET tax_amount = ROUND(NVL(tax_amt_tab(row_count),0), REC.ROUNDING_FACTOR),
2601 base_tax_amount = DECODE(NVL(base_tax_amt_tab(row_count), 0), 0, NVL(tax_amt_tab(row_count),0), NVL(base_tax_amt_tab(row_count), 0)),
2602 func_tax_amount = NVL(func_tax_amt_tab(row_count),0) * v_currency_conv_factor,
2603 last_update_date = p_last_update_date,
2604 last_updated_by = p_last_updated_by,
2605 last_update_login = p_last_update_login
2606 WHERE line_id = P_line_id
2607 AND header_id = p_header_id
2608 AND tax_line_no = row_count;
2609
2610 ELSIF transaction_name = 'AR_LINES' THEN
2611
2612 --2001/03/30 Manohar Mishra
2613 -- Added the following IF condition
2614 --if (v_register_code='BOND_REG') --and (rec.tax_type_val<>1))
2615 --then
2616 --if (rec.tax_type_val<>1) then
2617
2618 INSERT INTO JAI_AR_TRX_TAX_LINES(
2619 customer_trx_line_id, link_to_cust_trx_line_id, tax_line_no,
2620 precedence_1, precedence_2, precedence_3, precedence_4, precedence_5,
2621 precedence_6, precedence_7, precedence_8, precedence_9, precedence_10, -- Date 31/10/2006 Bug 5228046 added by SACSETHI
2622 tax_id, tax_rate, qty_rate, uom,
2623 tax_amount,
2624 base_tax_amount,
2625 func_tax_amount,
2626 creation_date, created_by, last_update_date,
2627 last_updated_by, last_update_login
2628 ) VALUES(
2629 ra_customer_trx_lines_s.NEXTVAL, p_line_id, row_count,
2630 rec.p_1, rec.p_2, rec.p_3, rec.p_4, rec.p_5,
2631 rec.p_6, rec.p_7, rec.p_8, rec.p_9, rec.p_10,
2632 rec.tax_id, rec.tax_rate, rec.tax_amount, rec.uom_code,
2633 ROUND(NVL(tax_amt_tab(row_count), 0), REC.ROUNDING_FACTOR),
2634 DECODE(NVL(base_tax_amt_tab(row_count), 0), 0, NVL(tax_amt_tab(row_count),0), NVL(base_tax_amt_tab(row_count), 0)),
2635 NVL(func_tax_amt_tab(row_count),0) * v_currency_conv_factor,
2636 p_creation_date, p_created_by, p_last_update_date,
2637 p_last_updated_by, p_last_update_login
2638 );
2639
2640 /*
2641 end if;
2642 else
2643 INSERT INTO JAI_AR_TRX_TAX_LINES(customer_trx_line_id,
2644 link_to_cust_trx_line_id,
2645 tax_line_no,
2646 precedence_1,precedence_2, precedence_3, precedence_4,precedence_5,
2647 precedence_6,precedence_7, precedence_8, precedence_9,precedence_10, -- Date 31/10/2006 Bug 5228046 added by SACSETHI
2648 tax_id, tax_rate, qty_rate, uom,
2649 tax_amount,
2650 base_tax_amount,
2651 func_tax_amount,
2652 creation_date, created_by, last_update_date,
2653 last_updated_by, last_update_login)
2654 VALUES(ra_customer_trx_lines_s.nextval, p_line_id, row_count,
2655 rec.p_1, rec.p_2, rec.p_3, rec.p_4, rec.p_5,
2656 rec.p_6, rec.p_7, rec.p_8, rec.p_9, rec.p_10,
2657 rec.tax_id, rec.tax_rate, rec.tax_amount, rec.uom_code,
2658 ROUND(nvl(tax_amt_tab(row_count), 0), REC.ROUNDING_FACTOR),
2659 decode(nvl(base_tax_amt_tab(row_count), 0), 0, nvl(tax_amt_tab(row_count),0), nvl(base_tax_amt_tab(row_count), 0)),
2660 (nvl(func_tax_amt_tab(row_count),0) * v_currency_conv_factor),
2661 p_creation_date, p_created_by, p_last_update_date,
2662 p_last_updated_by, p_last_update_login);
2663 end if;
2664 */
2665
2666 ELSIF transaction_name = 'AR_LINES_UPDATE' THEN
2667
2668 --2001/03/30 Manohar Mishra
2669 -- Added the following IF condition
2670 --if ((v_register_code<>'BOND_REG') and (rec.tax_type_val<>1)) then
2671 UPDATE JAI_AR_TRX_TAX_LINES
2672 SET tax_amount = ROUND(NVL(tax_amt_tab(row_count), 0), REC.ROUNDING_FACTOR),
2673 base_tax_amount = DECODE(NVL(base_tax_amt_tab(row_count), 0), 0, NVL(tax_amt_tab(row_count),0), NVL(base_tax_amt_tab(row_count), 0)),
2674 func_tax_amount = NVL(func_tax_amt_tab(row_count),0) * v_currency_conv_factor,
2675 last_update_date = p_last_update_date,
2676 last_updated_by = p_last_updated_by,
2677 last_update_login = p_last_update_login
2678 WHERE link_to_cust_trx_line_id = P_line_id
2679 AND tax_line_no = row_count;
2680
2681 --end if;
2682
2683 ELSIF SUBSTR( transaction_name, 1, 3 ) = 'RFQ' OR
2684 SUBSTR( transaction_name, 1, 9 ) = 'QUOTATION' OR
2685 SUBSTR( transaction_name, 1, 7 ) = 'BLANKET' OR
2686 SUBSTR( transaction_name, 1, 8 ) = 'BLANKETR' OR
2687 SUBSTR( transaction_name, 1, 6 ) = 'OTHERS' OR
2688 SUBSTR( transaction_name, 1, 9 ) = 'SCHEDULED' THEN
2689
2690 /*
2691 Since there is no provision of line location id as one of the parameter,
2692 Line location Id is passed in place of line id, Line Id is concatinated
2693 to transaction name.
2694 If proportioning has to be done, then operation variable takes value U
2695 else takes I. This is also concatenated with transaction name with a preceding $.
2696 */
2697
2698 BEGIN -- MBEGIN
2699
2700 DECLARE -- NDECLARE
2701
2702 v_seq_val NUMBER;
2703 v_modvat_flag VARCHAR2(1);
2704 v_vendor_id NUMBER;
2705 v_vendor1_id NUMBER;
2706 v_vendor2_id NUMBER;
2707 v_currency VARCHAR2(15);
2708 v_transaction_name VARCHAR2(100);
2709 v_mod_cr NUMBER;
2710 v_tax_type VARCHAR2(30);
2711 v_start NUMBER;
2712 v_line_id NUMBER;
2713 operation VARCHAR2(2);
2714
2715 CURSOR fetch_mod_cr_cur( taxid IN NUMBER ) IS
2716 SELECT Tax_Type, Mod_Cr_Percentage, Vendor_Id
2717 FROM JAI_CMN_TAXES_ALL
2718 WHERE Tax_Id = taxid;
2719
2720 CURSOR fetch_vendor2_cur IS
2721 SELECT vendor_id
2722 FROM JAI_PO_REQ_LINE_TAXES
2723 WHERE Requisition_Line_Id = ( SELECT Requisition_Line_Id
2724 FROM Po_Requisition_Lines_All
2725 WHERE Line_Location_Id = p_line_id );
2726
2727 CURSOR fetch_focus_id IS
2728 SELECT Line_Focus_Id
2729 FROM JAI_PO_LINE_LOCATIONS
2730 WHERE Po_Line_Id = p_line_id
2731 AND Po_Header_Id = p_header_id
2732 AND Line_Location_Id IS NULL;
2733
2734 CURSOR fetch_focus1_id( line_id IN NUMBER ) IS
2735 SELECT Line_Focus_Id
2736 FROM JAI_PO_LINE_LOCATIONS
2737 WHERE Po_Line_Id = p_line_id
2738 AND Po_Header_Id = p_header_id
2739 AND Line_Location_Id = line_id;
2740
2741 BEGIN -- NBEGIN
2742
2743 IF SUBSTR( transaction_name, 1, 1 ) = 'R' THEN
2744 v_transaction_name := 'RFQ';
2745 v_start := 4;
2746 ELSIF SUBSTR( transaction_name, 1, 1 ) = 'S' THEN
2747 v_transaction_name := 'RFQ';
2748 v_start := 10;
2749 ELSIF SUBSTR( transaction_name, 1, 1 ) = 'Q' THEN
2750 v_transaction_name := 'QUOTATION';
2751 v_start := 10;
2752 ELSIF SUBSTR( transaction_name, 1, 1 ) = 'B' AND SUBSTR( transaction_name, 1, 8 ) <> 'BLANKETR' THEN
2753 v_transaction_name := 'BLANKET';
2754 v_start := 8;
2755 ELSIF SUBSTR( transaction_name, 1, 8 ) = 'BLANKETR' THEN
2756 v_transaction_name := 'RFQ';
2757 v_start := 9;
2758 ELSIF SUBSTR( transaction_name, 1, 1 ) = 'O' THEN
2759 v_transaction_name := 'OTHERS';
2760 v_start := 7;
2761 END IF;
2762
2763 operation := SUBSTR( transaction_name, INSTR(transaction_name, '$' )+1, 1 );
2764 v_line_id := TO_NUMBER( SUBSTR( transaction_name, v_start, LENGTH( transaction_name )-( v_start + 1 )));
2765
2766 IF NVL( v_line_id, 0 ) = 0 THEN
2767
2768 OPEN Fetch_Focus_Id;
2769 FETCH Fetch_Focus_Id INTO v_seq_val;
2770 CLOSE Fetch_Focus_Id;
2771
2772 v_line_focus_id_holder := v_seq_val; -- cbabu for EnhancementBug# 2427465
2773
2774 ELSE
2775 OPEN Fetch_Focus1_Id( v_line_id );
2776 FETCH Fetch_Focus1_Id INTO v_seq_val;
2777 CLOSE Fetch_Focus1_Id;
2778
2779 v_line_focus_id_holder := v_seq_val; -- cbabu for EnhancementBug# 2427465
2780
2781 END IF;
2782
2783 OPEN Fetch_Mod_Cr_Cur( rec.tax_id );
2784 FETCH Fetch_Mod_Cr_Cur INTO v_tax_type, v_mod_cr, v_vendor1_id;
2785 CLOSE Fetch_Mod_Cr_Cur;
2786
2787 IF rec.mod_cr_percentage IS NOT NULL AND rec.mod_cr_percentage > 0 THEN
2788 v_modvat_flag := 'Y';
2789 ELSIF rec.mod_cr_percentage IS NULL THEN
2790 v_modvat_flag := 'N';
2791 END IF;
2792
2793 IF v_transaction_name IN ( 'OTHERS', 'QUOTATION', 'BLANKET' ) THEN
2794 IF upper(rec.tax_type) IN ( 'CVD',
2795 jai_constants.tax_type_add_cvd , -- Date 31/10/2006 Bug 5228046 added by SACSETHI
2796 'CUSTOMS' ,
2797 JAI_CONSTANTS.TAX_TYPE_SH_CVD_EDU_CESS,/*bduvarag for the bug#5989740*/
2798 jai_constants.tax_type_cvd_edu_cess ,
2799 JAI_CONSTANTS.TAX_TYPE_SH_CUSTOMS_EDU_CESS,/*bduvarag for the bug#5989740*/
2800 jai_constants.tax_type_customs_edu_cess
2801 ) THEN
2802 v_vendor_id := NULL;
2803 ELSIF UPPER( rec.tax_type ) LIKE UPPER( '%EXCISE%' ) THEN
2804 v_vendor_id := p_vendor_id;
2805 ELSE
2806 v_vendor_id := NVL( v_vendor1_id, p_vendor_id );
2807 END IF;
2808 END IF;
2809
2810 IF rec.tax_type = 'TDS' THEN
2811 v_vendor_id := v_vendor1_id;
2812 END IF;
2813
2814 IF operation = 'I' THEN
2815 IF p_operation_flag <> -1 THEN
2816 INSERT INTO JAI_PO_TAXES(
2817 line_focus_id, line_location_id, po_line_id, po_header_id,
2818 tax_line_no,
2819 precedence_1, precedence_2, precedence_3, precedence_4, precedence_5,
2820 precedence_6, precedence_7, precedence_8, precedence_9, precedence_10, -- Date 31/10/2006 Bug 5228046 added by SACSETHI
2821 tax_id, tax_type, tax_rate, qty_rate, uom, tax_amount, tax_target_amount,
2822 currency, modvat_flag, vendor_id,
2823 creation_date, created_by,
2824 last_update_date, last_updated_by, last_update_login,
2825 tax_category_id -- cbabu for EnhancementBug# 2427465
2826 ) VALUES (
2827 v_seq_val, v_line_id, p_line_id, p_header_id,
2828 row_count,
2829 rec.p_1, rec.p_2,rec.p_3, rec.p_4, rec.p_5,
2830 rec.p_6, rec.p_7,rec.p_8, rec.p_9, rec.p_10,
2831 rec.tax_id, rec.tax_type, rec.tax_rate, rec.tax_amount, rec.uom_code,
2832 ROUND(NVL(tax_amt_tab(row_count),0), REC.ROUNDING_FACTOR),
2833 DECODE(NVL(base_tax_amt_tab(row_count), 0), 0, NVL(tax_amt_tab(row_count),0),
2834 NVL(base_tax_amt_tab(row_count), 0)) * p_currency_conv_factor ,
2835 p_currency, v_modvat_flag, v_vendor_id,
2836 p_creation_date, p_created_by,
2837 p_last_update_date, p_last_updated_by, p_last_update_login,
2838 p_tax_category_id -- cbabu for EnhancementBug# 2427465
2839 );
2840
2841 --Added by Kevin Cheng for Retroactive Price 2008/01/13
2842 --=====================================================
2843 IF pv_retroprice_changed = 'Y'
2844 THEN
2845 JAI_RETRO_PRC_PKG.Update_Price_Changes( pn_tax_amt => ROUND(NVL(tax_amt_tab(row_count),0), REC.ROUNDING_FACTOR)
2846 , pn_line_no => row_count
2847 , pn_line_loc_id => v_line_id
2848 , pv_process_flag => lv_process_flag
2849 , pv_process_message => lv_process_message
2850 );
2851
2852 IF lv_process_flag IN ('EE', 'UE')
2853 THEN
2854 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
2855 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG','JAI_CMN_TAX_DEFAULTATION_PKG.JA_IN_CALC_PREC_TAXES.Err:'||lv_process_message);
2856 app_exception.raise_exception;
2857 END IF;
2858 END IF;
2859 --=====================================================
2860
2861 ELSE
2862
2863 INSERT INTO JAI_PO_TAXES(
2864 Line_Focus_Id, Line_Location_Id, Po_Line_Id, Po_Header_Id,
2865 Tax_Line_No,
2866 Precedence_1, Precedence_2, Precedence_3, Precedence_4, Precedence_5,
2867 Precedence_6, Precedence_7, Precedence_8, Precedence_9, Precedence_10, -- Date 31/10/2006 Bug 5228046 added by SACSETHI
2868 Tax_Id, Tax_Type, Tax_Rate, Qty_Rate, UOM, Tax_Amount,
2869 Tax_Target_Amount,
2870 Currency, Modvat_Flag, Vendor_Id,
2871 Creation_Date, Created_By,
2872 Last_Update_Date, Last_Updated_By, Last_Update_Login,
2873 tax_category_id -- cbabu for EnhancementBug# 2427465
2874 ) VALUES (
2875 v_seq_val, NULL, p_line_id, p_header_id,
2876 row_count,
2877 rec.p_1, rec.p_2, rec.p_3, rec.p_4, rec.p_5,
2878 rec.p_6, rec.p_7, rec.p_8, rec.p_9, rec.p_10,
2879 rec.tax_id, rec.tax_type, rec.tax_rate, rec.tax_amount, rec.uom_code, NULL,
2880 DECODE( NVL(base_tax_amt_tab(row_count), 0), 0, NVL(tax_amt_tab(row_count),0),
2881 NVL(base_tax_amt_tab(row_count), 0)) * p_currency_conv_factor ,
2882 p_currency, v_modvat_flag, v_vendor_id,
2883 p_creation_date, p_created_by,
2884 p_last_update_date, p_last_updated_by, p_last_update_login,
2885 p_tax_category_id -- cbabu for EnhancementBug# 2427465
2886 );
2887 END IF;
2888
2889 ELSIF operation = 'U' THEN
2890
2891 IF v_line_id IS NOT NULL THEN
2892 UPDATE JAI_PO_TAXES
2893 SET Tax_Amount = ROUND(NVL( tax_amt_tab(row_count), 0 ),REC.ROUNDING_FACTOR),
2894 tax_target_amount = DECODE(NVL(base_tax_amt_tab(row_count), 0), 0, NVL(tax_amt_tab(row_count),0),
2895 NVL(base_tax_amt_tab(row_count), 0)) * p_currency_conv_factor ,
2896 last_updated_by = p_last_updated_by,
2897 last_update_date = p_last_update_date,
2898 last_update_login = p_last_update_login
2899 WHERE po_line_id = p_line_id
2900 AND line_location_id = v_line_id;
2901 ELSE
2902 UPDATE JAI_PO_TAXES
2903 SET Tax_Amount = ROUND(NVL( tax_amt_tab(row_count), 0 ), REC.ROUNDING_FACTOR),
2904 tax_target_amount = DECODE(NVL(base_tax_amt_tab(row_count), 0), 0, NVL(tax_amt_tab(row_count),0),
2905 NVL(base_tax_amt_tab(row_count), 0)) * p_currency_conv_factor ,
2906 last_updated_by = p_last_updated_by,
2907 last_update_date = p_last_update_date,
2908 last_update_login = p_last_update_login
2909 WHERE Po_Line_Id = p_line_id
2910 AND Line_Location_Id IS NULL;
2911 END IF;
2912
2913 END IF;
2914 v_vendor_id := NULL;
2915
2916 END; -- NDECLARE, NBEGIN
2917
2918 END; -- MBEGIN -- transaction_name = RFQ/QUOTATION/PURCHASE ORDER/RELEASES
2919
2920 ELSIF transaction_name = 'PO_REQN' THEN
2921
2922 BEGIN
2923
2924 DECLARE
2925
2926 v_modvat_flag VARCHAR2(1);
2927 v_vendor_id NUMBER;
2928 v_currency VARCHAR2(15);
2929
2930 BEGIN
2931
2932 IF rec.mod_cr_percentage IS NOT NULL AND rec.mod_cr_percentage > 0 THEN
2933 v_modvat_flag := 'Y';
2934 ELSIF rec.mod_cr_percentage IS NULL THEN
2935 v_modvat_flag := 'N';
2936 END IF;
2937 IF rec.tax_type = 'TDS' THEN
2938 v_vendor_id := rec.vendor_id;
2939 ELSIF UPPER(rec.tax_type) IN ('CVD',
2940 jai_constants.tax_type_add_cvd , -- Date 31/10/2006 Bug 5228046 added by SACSETHI
2941 'CUSTOMS',
2942 JAI_CONSTANTS.TAX_TYPE_SH_CVD_EDU_CESS,/*bduvarag for the bug#5989740*/
2943 jai_constants.tax_type_cvd_edu_cess ,
2944 JAI_CONSTANTS.TAX_TYPE_SH_CUSTOMS_EDU_CESS,/*bduvarag for the bug#5989740*/
2945 jai_constants.tax_type_customs_edu_cess
2946 ) THEN
2947 v_vendor_id := NULL;
2948 ELSIF UPPER( rec.tax_type ) LIKE '%EXCISE%' THEN
2949 v_vendor_id := p_vendor_id;
2950 ELSE
2951 v_vendor_id := NVL( rec.vendor_id, p_vendor_id );
2952 END IF;
2953
2954 IF p_currency IS NOT NULL THEN
2955 v_currency := p_currency;
2956 END IF;
2957 IF NVL( p_operation_flag, 0 ) < 0 AND ( UPPER( rec.tax_type ) LIKE '%EXCISE%' OR UPPER(rec.tax_type)
2958 NOT IN ( 'CVD',
2959 jai_constants.tax_type_add_cvd, -- Date 31/10/2006 Bug 5228046 added by SACSETHI
2960 'CUSTOMS',
2961 JAI_CONSTANTS.TAX_TYPE_SH_CVD_EDU_CESS,/*bduvarag for the bug#5989740*/
2962 jai_constants.tax_type_cvd_edu_cess ,
2963 JAI_CONSTANTS.TAX_TYPE_SH_CUSTOMS_EDU_CESS,/*bduvarag for the bug#5989740*/
2964 jai_constants.tax_type_customs_edu_cess
2965 ) )
2966 THEN /* Indiactes an Internal Requisition */
2967 v_vendor_id := p_operation_flag;
2968 END IF;
2969
2970 INSERT INTO JAI_PO_REQ_LINE_TAXES(
2971 requisition_line_id, requisition_header_id, tax_line_no,
2972 precedence_1, precedence_2, precedence_3, precedence_4, precedence_5,
2973 precedence_6, precedence_7, precedence_8, precedence_9, precedence_10, -- Date 31/10/2006 Bug 5228046 added by SACSETHI
2974 tax_id, tax_rate, qty_rate, uom, tax_amount, Tax_Target_Amount,
2975 tax_type, modvat_flag, vendor_id, currency,
2976 creation_date, created_by, last_update_date,
2977 last_updated_by, last_update_login,
2978 tax_category_id -- cbabu for EnhancementBug# 2427465
2979 ) VALUES (
2980 p_line_id, p_header_id, row_count,
2981 rec.p_1, rec.p_2, rec.p_3,rec.p_4, rec.p_5,
2982 rec.p_6, rec.p_7, rec.p_8,rec.p_9, rec.p_10,
2983 rec.tax_id, rec.tax_rate, rec.tax_amount, rec.uom_code,
2984 ROUND( NVL(tax_amt_tab(row_count),0), rec.rounding_factor), -- v_currency_conv_factor ),
2985 NVL( base_tax_amt_tab(row_count), 0) * ( v_currency_conv_factor), rec.tax_type, v_modvat_flag, v_vendor_id, v_currency,
2986 p_creation_date, p_created_by, p_last_update_date,
2987 p_last_updated_by, p_last_update_login,
2988 p_tax_category_id -- cbabu for EnhancementBug# 2427465
2989 );
2990
2991 END;
2992
2993 END;
2994
2995
2996
2997
2998 /* bgowrava for forward porting bug#5631784 */
2999 /* Added OR condition as we are passing p_action as null in case of 'CASH' Receipt. by JMEENA */
3000 elsif (p_source_trx_type in (
3001 jai_constants.pa_draft_invoice /* 6012570 (5876390) */
3002 , jai_constants.G_AP_STANDALONE_INVOICE
3003 -- Added by Jason Liu on 2007/08/23
3004 )
3005 and p_action = jai_constants.default_taxes )
3006 OR (p_source_trx_type = jai_constants.ar_cash)
3007 then
3008 -- Added by Jason Liu for standalone invoice on 2007/08/23
3009 ----------------------------------------------------------------------
3010 IF p_source_trx_type = jai_constants.G_AP_STANDALONE_INVOICE
3011 AND rec.tax_type IN ( jai_constants.tax_type_value_added
3012 , jai_constants.tax_type_sales
3013 , jai_constants.tax_type_cst
3014 , jai_constants.tax_type_other)
3015 THEN
3016 v_modvat_flag := 'N';
3017 END IF; --p_source_trx_type = jai_constants.AP_STANDALONE_INVOICE
3018 ----------------------------------------------------------------------
3019
3020 /*
3021 || When currency conversion rate is null it means the transaction is in the INR only
3022 */
3023 if v_currency_conv_factor is null then
3024 v_currency_conv_factor := 1;
3025 end if;
3026
3027 /* jai_cmn_debug_contexts_pkg.print
3028 (ln_reg_id
3029 ,'Values before insert into jai_cmn_document_taxes' ||chr(10)
3030 || ',tax_line_no -> '||row_count ||CHR(10)
3031 || ',tax_id -> '||rec.tax_id ||CHR(10)
3032 || ',tax_type -> '||rec.tax_type ||CHR(10)
3033 || ',currency_code -> '||p_currency ||CHR(10)
3034 || ',tax_rate -> '||rec.tax_rate ||CHR(10)
3035 || ',qty_rate -> '||rec.tax_amount ||CHR(10)
3036 || ',uom -> '||rec.uom_code ||CHR(10)
3037 || ',tax_amt -> '||round( nvl(tax_amt_tab(row_count),0) , rec.rounding_factor ) ||CHR(10)
3038 || ',func_tax_amt -> '||nvl(func_tax_amt_tab(row_count),0) * v_currency_conv_factor ||CHR(10)
3039 || ',modvat_flag -> '||v_modvat_flag ||CHR(10)
3040 || ',adhoc_flag -> '||rec.adhoc_flag ||CHR(10)
3041 || ',tax_category_id -> '||rec.tax_category_id ||CHR(10)
3042 || ',source_doc_type -> '||p_source_trx_type ||CHR(10)
3043 || ',source_doc_id -> '||p_header_id ||CHR(10)
3044 || ',source_doc_line_id -> '||p_line_id ||CHR(10)
3045 || ',source_table_name -> '||p_source_table_name ||CHR(10)
3046 || ',tax_modified_by -> '||jai_constants.tax_modified_by_system ||CHR(10)
3047 || ',precedence_1 -> '||rec.p_1 ||CHR(10)
3048 || ',precedence_2 -> '||rec.p_2 ||CHR(10)
3049 || ',precedence_3 -> '||rec.p_3 ||CHR(10)
3050 || ',precedence_4 -> '||rec.p_4 ||CHR(10)
3051 || ',precedence_5 -> '||rec.p_5 ||CHR(10)
3052 || ',precedence_6 -> '||rec.p_6 ||CHR(10)
3053 || ',precedence_7 -> '||rec.p_7 ||CHR(10)
3054 || ',precedence_8 -> '||rec.p_8 ||CHR(10)
3055 || ',precedence_9 -> '||rec.p_9 ||CHR(10)
3056 || ',precedence_10 -> '||rec.p_10 ||CHR(10)
3057 || ',creation_date -> '||p_creation_date ||CHR(10)
3058 || ',created_by -> '||p_created_by ||CHR(10)
3059 || ',last_update_date -> '||p_last_update_date ||CHR(10)
3060 || ',last_updated_by -> '||p_last_updated_by ||CHR(10)
3061 || ',last_update_login -> '||p_last_update_login ||CHR(10)
3062 ); */ --commented by bgowrava for bug#5631784
3063 -- Added by Eric Ma for standalone invoice on 2007/09/27
3064 ----------------------------------------------------------------------
3065 IF (p_source_trx_type = jai_constants.G_AP_STANDALONE_INVOICE )
3066 THEN
3067 INSERT INTO jai_cmn_document_taxes
3068 ( doc_tax_id
3069 , tax_line_no
3070 , tax_id
3071 , tax_type
3072 , currency_code
3073 , tax_rate
3074 , qty_rate
3075 , uom
3076 , tax_amt
3077 , func_tax_amt
3078 , modvat_flag
3079 , adhoc_flag
3080 , tax_category_id
3081 , source_doc_type
3082 , source_doc_id
3083 , source_doc_line_id
3084 , source_doc_parent_line_no --added by Eric Ma,Sep 27,2007
3085 , source_table_name
3086 , tax_modified_by
3087 , precedence_1
3088 , precedence_2
3089 , precedence_3
3090 , precedence_4
3091 , precedence_5
3092 , precedence_6
3093 , precedence_7
3094 , precedence_8
3095 , precedence_9
3096 , precedence_10
3097 , creation_date
3098 , created_by
3099 , last_update_date
3100 , last_updated_by
3101 , last_update_login
3102 )
3103 VALUES
3104 ( jai_cmn_document_taxes_s.nextval -- doc_tax_id
3105 , row_count -- tax_line_no
3106 , rec.tax_id -- tax_id
3107 , rec.tax_type -- tax_type
3108 , p_currency -- currency
3109 , rec.tax_rate -- tax_rate
3110 , rec.tax_amount -- qty_rate
3111 , rec.uom_code -- uom
3112 , round( nvl(tax_amt_tab(row_count),0) -- tax_amount
3113 , rec.rounding_factor
3114 )
3115 , nvl(func_tax_amt_tab(row_count),0)
3116 * v_currency_conv_factor -- func_tax_amount
3117 , v_modvat_flag -- modvat_flag
3118 , rec.adhoc_flag -- adhoc_flag
3119 , rec.tax_category_id -- tax_category_id
3120 , p_source_trx_type -- source_doc_type
3121 , p_header_id -- source_doc_id
3122 , p_line_id -- source_doc_line_id
3123 , p_line_id -- source_doc_parent_line_no,added by Eric Ma
3124 , p_source_table_name -- source_table_name
3125 , jai_constants.tax_modified_by_system
3126 --tax_modified_by(SYSTEM=system defaulted, MANUAL=User Modified)
3127 , rec.p_1 -- precedence_1
3128 , rec.p_2 -- precedence_2
3129 , rec.p_3 -- precedence_3
3130 , rec.p_4 -- precedence_4
3131 , rec.p_5 -- precedence_5
3132 , rec.p_6 -- precedence_6
3133 , rec.p_7 -- precedence_7
3134 , rec.p_8 -- precedence_8
3135 , rec.p_9 -- precedence_9
3136 , rec.p_10 -- precedence_10
3137 , p_creation_date -- creation_date
3138 , p_created_by -- created_by
3139 , p_last_update_date -- last_update_date
3140 , p_last_updated_by -- last_updated_by
3141 , p_last_update_login -- last_update_login
3142 );
3143 ELSE --(p_source_trx_type <>jai_constants.G_AP_STANDALONE_INVOICE );
3144 ------------------------------------------------------------------
3145 insert into jai_cmn_document_taxes
3146 ( doc_tax_id
3147 , tax_line_no
3148 , tax_id
3149 , tax_type
3150 , currency_code
3151 , tax_rate
3152 , qty_rate
3153 , uom
3154 , tax_amt
3155 , func_tax_amt
3156 , modvat_flag
3157 , adhoc_flag
3158 , tax_category_id
3159 , source_doc_type
3160 , source_doc_id
3161 , source_doc_line_id
3162 , source_table_name
3163 , tax_modified_by
3164 , precedence_1
3165 , precedence_2
3166 , precedence_3
3167 , precedence_4
3168 , precedence_5
3169 , precedence_6
3170 , precedence_7
3171 , precedence_8
3172 , precedence_9
3173 , precedence_10
3174 , creation_date
3175 , created_by
3176 , last_update_date
3177 , last_updated_by
3178 , last_update_login
3179 )
3180 values
3181 (
3182 jai_cmn_document_taxes_s.nextval -- doc_tax_id
3183 , row_count -- tax_line_no
3184 , rec.tax_id -- tax_id
3185 , rec.tax_type -- tax_type
3186 , p_currency -- currency
3187 , rec.tax_rate -- tax_rate
3188 , rec.tax_amount -- qty_rate
3189 , rec.uom_code -- uom
3190 , round( nvl(tax_amt_tab(row_count),0) -- tax_amount
3191 , rec.rounding_factor
3192 )
3193 , nvl(func_tax_amt_tab(row_count),0)
3194 * v_currency_conv_factor -- func_tax_amount
3195 , v_modvat_flag -- modvat_flag
3196 , rec.adhoc_flag -- adhoc_flag
3197 , rec.tax_category_id -- tax_category_id
3198 , p_source_trx_type -- source_doc_type
3199 , p_header_id -- source_doc_id
3200 , p_line_id -- source_doc_line_id
3201 , p_source_table_name -- source_table_name
3202 , jai_constants.tax_modified_by_system -- tax_modified_by (SYSTEM=system defaulted, MANUAL=User Modified)
3203 , rec.p_1 -- precedence_1
3204 , rec.p_2 -- precedence_2
3205 , rec.p_3 -- precedence_3
3206 , rec.p_4 -- precedence_4
3207 , rec.p_5 -- precedence_5
3208 , rec.p_6 -- precedence_6
3209 , rec.p_7 -- precedence_7
3210 , rec.p_8 -- precedence_8
3211 , rec.p_9 -- precedence_9
3212 , rec.p_10 -- precedence_10
3213 , p_creation_date -- creation_date
3214 , p_created_by -- created_by
3215 , p_last_update_date -- last_update_date
3216 , p_last_updated_by -- last_updated_by
3217 , p_last_update_login -- last_update_login
3218 );
3219 END IF; --(p_source_trx_type = jai_constants.G_AP_STANDALONE_INVOICE )
3220 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Record inserted into jai_cmn_document_taxes');*/ --commented by bgowrava for bug#5631784
3221
3222 /* Date 22-feb-2007 Added by SACSETHI for bug 6012570 (5876390)
3223 in This , Recalculation will be happen in Draft invoice */
3224 elsif
3225 p_source_trx_type = jai_constants.pa_draft_invoice
3226 and p_action = jai_constants.recalculate_taxes
3227 then
3228
3229 update jai_cmn_document_taxes
3230 set tax_amt = tax_amt_tab(row_count)
3231 ,func_tax_amt = nvl(func_tax_amt_tab(row_count),0) * nvl(v_currency_conv_factor ,1)
3232 ,last_update_date = p_last_update_date
3233 ,last_updated_by = p_last_updated_by
3234 ,last_update_login = p_last_update_login
3235 where source_doc_line_id = p_line_id
3236 and tax_id = rec.tax_id
3237 and source_doc_type = jai_constants.pa_draft_invoice;
3238
3239 elsif p_source_trx_type = jai_constants.source_ttype_delivery
3240 then
3241
3242 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id
3243 ,'Value of variables used for updating ja_in_so_picking_tax_lines'||chr(10)
3244 ||'tax_amount = '|| tax_amt_tab(row_count) ||chr(10)
3245 ||'func_tax_amount = '||nvl(func_tax_amt_tab(row_count),0) * v_currency_conv_factor ||chr(10)
3246 ||'base_tax_amount = '||round( nvl(tax_amt_tab(row_count),0), rec.rounding_factor) ||chr(10)
3247 ||'tax_id = '||rec.tax_id
3248 ); */ --commented by bgowrava for bug#5631784
3249
3250 if p_action = jai_constants.recalculate_taxes then
3251
3252 update JAI_OM_WSH_LINE_TAXES
3253 set tax_amount = tax_amt_tab(row_count)
3254 ,func_tax_amount = nvl(func_tax_amt_tab(row_count),0) * nvl(v_currency_conv_factor ,1)
3255 ,base_tax_amount = round( nvl(tax_amt_tab(row_count),0), rec.rounding_factor)
3256 ,last_update_date = p_last_update_date
3257 ,last_updated_by = p_last_updated_by
3258 ,last_update_login = p_last_update_login
3259 where delivery_detail_id = p_line_id
3260 and tax_id = rec.tax_id;
3261
3262 end if;
3263
3264 elsif p_source_trx_type = jai_constants.bill_only_invoice then
3265
3266 /*
3267 || When currency conversion rate is null it means the transaction is in the INR only
3268 */
3269 if v_currency_conv_factor is null then
3270 v_currency_conv_factor := 1;
3271 end if;
3272
3273 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id
3274 ,'Value of variables used for updating JA_IN_RA_CUST_TRX_TAX_LINES'||chr(10)
3275 ||'tax_amount = '|| tax_amt_tab(row_count) ||chr(10)
3276 ||'func_tax_amount = '||nvl(func_tax_amt_tab(row_count),0) * v_currency_conv_factor ||chr(10)
3277 ||'base_tax_amount = '||round( nvl(tax_amt_tab(row_count),0), rec.rounding_factor) ||chr(10)
3278 ||'tax_id = '||rec.tax_id
3279 ); */ --commented by bgowrava for bug#5631784
3280
3281 if p_action = jai_constants.recalculate_taxes then
3282
3283 update JAI_AR_TRX_TAX_LINES
3284 set tax_amount = tax_amt_tab(row_count)
3285 ,func_tax_amount = nvl(func_tax_amt_tab(row_count),0) * nvl(v_currency_conv_factor ,1)
3286 ,base_tax_amount = round( nvl(tax_amt_tab(row_count),0), rec.rounding_factor)
3287 ,last_update_date = p_last_update_date
3288 ,last_updated_by = p_last_updated_by
3289 ,last_update_login = p_last_update_login
3290 where link_to_cust_trx_line_id = p_line_id
3291 and tax_id = rec.tax_id;
3292
3293 end if;
3294
3295 /** End Bug 5631784 */
3296
3297
3298
3299 END IF; -- p_transaction_type
3300 row_count := row_count + 1;
3301
3302 END LOOP;
3303
3304 -- Start, cbabu for EnhancementBug# 2427465
3305 IF SUBSTR( transaction_name, 1, 3 ) = 'RFQ' OR
3306 SUBSTR( transaction_name, 1, 9 ) = 'QUOTATION' OR
3307 SUBSTR( transaction_name, 1, 7 ) = 'BLANKET' OR
3308 SUBSTR( transaction_name, 1, 8 ) = 'BLANKETR' OR
3309 SUBSTR( transaction_name, 1, 6 ) = 'OTHERS' OR
3310 SUBSTR( transaction_name, 1, 9 ) = 'SCHEDULED'
3311 THEN
3312
3313 BEGIN
3314 UPDATE JAI_PO_LINE_LOCATIONS
3315 SET tax_category_id = p_tax_category_id
3316 WHERE line_focus_id = v_line_focus_id_holder;
3317 EXCEPTION
3318 WHEN OTHERS THEN
3319 RAISE_APPLICATION_ERROR( -20101, '1 Exception raised in jai_cmn_tax_defaultation_pkg:JIPLL '||SQLERRM, TRUE);
3320 END;
3321
3322 ELSIF transaction_name = 'PO_REQN' THEN
3323
3324 BEGIN
3325 UPDATE JAI_PO_REQ_LINES
3326 SET tax_category_id = p_tax_category_id
3327 WHERE requisition_line_id = p_line_id;
3328 EXCEPTION
3329 WHEN OTHERS THEN
3330 RAISE_APPLICATION_ERROR( -20101, '2 Exception raised in jai_cmn_tax_defaultation_pkg:JIRL '||SQLERRM, TRUE);
3331 END;
3332
3333
3334 /* End, cbabu for EnhancementBug# 2427465 */
3335
3336 END IF;
3337
3338 p_tax_amount := nvl(v_tax_amt,0);
3339
3340 /* Added by Ramananda for bug#4407165 */
3341 EXCEPTION
3342 WHEN OTHERS THEN
3343 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
3344 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
3345 app_exception.raise_exception;
3346
3347
3348 END ja_in_calc_prec_taxes;
3349
3350 /* Added by bgowrava for Forward porting bug#5631784 */
3351
3352 /*------------------------------------------------------------------------------------------------------------*/
3353 procedure get_tax_cat_taxes_cur (p_tax_category_id number
3354 ,p_threshold_tax_cat_id number default null
3355 ,p_max_tax_line number default 0
3356 ,p_max_rgm_tax_line number default 0
3357 ,p_base number default 0
3358 ,p_refc_tax_cat_taxes_cur out nocopy ref_cur_typ
3359 , pv_retroprice_changed IN VARCHAR2 DEFAULT 'N' --Added by Kevin Cheng for Retroactive Price 2008/01/13
3360 )
3361 /***************************************************************************************************************
3362 ||Purpose :-
3363 || 1. Whenever p_threshold_tax_cat_id is not NULL then it means taxes from two categories needs to be merged.
3364 || one using p_tax_category_id and other is p_threshold_tax_cat_id
3365 || 2. current driving cursor (tax_cur) is modified to handle multiple tax categories.
3366 || 2.1 For all the tax lines defined in the p_tax_category_id there is no change
3367 || 2.2 For all the tax lines defined in the p_threshold_tax_cat_id, line_no will be changed
3368 || to p_max_tax_line + line_no where p_max_tax_line is the maximum of line numbers for
3369 || tax lines defined in p_tax_category_id
3370 || 2.3 All the precedences defined in p_threshold_tax_cat_id will be changed as following
3371 || - If precedence refers to base precedence (i.e. 0) it will be changed to p_max_rgm_tax_line
3372 || where p_max_rgm_tax_line is maximum of the line numbers of taxes having
3373 || tax_type = p_thhold_cat_base_tax_typ (i.e. tax type to be considered as a base tax
3374 || when calculating threshold taxes defined using p_threshold_tax_cat_id)
3375 || - All other precedences will be changed to precedence_N + p_max_tax_line
3376 ***************************************************************************************************************/
3377
3378 is
3379 ref_tax_cur ref_cur_typ;
3380 begin
3381 /*
3382 ||
3383 */
3384
3385 --Added by Kevin Cheng for Retroactive Price 2008/01/13
3386 --=====================================================
3387 IF pv_retroprice_changed = 'N'
3388 THEN
3389 --=====================================================
3390 open ref_tax_cur
3391 for select a.tax_id
3392 , decode (a.tax_category_id, p_tax_category_id, a.line_no
3393 , (p_max_tax_line + a.line_no)
3394 ) lno
3395 , decode (a.tax_category_id, p_tax_category_id, a.precedence_1
3396 , decode (a.precedence_1, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_1))
3397 ) p_1
3398 , decode (a.tax_category_id, p_tax_category_id, a.precedence_2
3399 , decode (a.precedence_2, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_2))
3400 ) p_2
3401 , decode (a.tax_category_id, p_tax_category_id, a.precedence_3
3402 , decode (a.precedence_3, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_3))
3403 ) p_3
3404 , decode (a.tax_category_id, p_tax_category_id, a.precedence_4
3405 , decode (a.precedence_4, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_4))
3406 ) p_4
3407 , decode (a.tax_category_id, p_tax_category_id, a.precedence_5
3408 , decode (a.precedence_5, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_5))
3409 ) p_5
3410 /* Bug 5094130. Added by Lakshmi Gopalsami Included precedences 6 to 10*/
3411 , decode (a.tax_category_id, p_tax_category_id, a.precedence_6
3412 , decode (a.precedence_6, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_6))
3413 ) p_6
3414 , decode (a.tax_category_id, p_tax_category_id, a.precedence_7
3415 , decode (a.precedence_7, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_7))
3416 ) p_7
3417 , decode (a.tax_category_id, p_tax_category_id, a.precedence_8
3418 , decode (a.precedence_8, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_8))
3419 ) p_8
3420 , decode (a.tax_category_id, p_tax_category_id, a.precedence_9
3421 , decode (a.precedence_9, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_9))
3422 ) p_9
3423 , decode (a.tax_category_id, p_tax_category_id, a.precedence_10
3424 , decode (a.precedence_10, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_10))
3425 ) p_10
3426 , b.tax_rate
3427 , b.tax_amount
3428 , b.uom_code
3429 , b.end_date valid_date
3430 , DECODE(rgm_tax_types.regime_Code,jai_constants.vat_regime, 4, /* added by ssumaith - bug# 4245053*/
3431 DECODE(UPPER(b.tax_type),
3432 'EXCISE', 1,
3433 'ADDL. EXCISE', 1,
3434 'OTHER EXCISE', 1,
3435 'TDS', 2,
3436 'EXCISE_EDUCATION_CESS' ,6, --modified by walton for inclusive tax
3437 JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS , 6 , /*bduvarag for the bug#5989740*/ --modified by walton for inclusive tax
3438 'CVD_EDUCATION_CESS' ,6, --modified by walton for inclusive tax
3439 JAI_CONSTANTS.TAX_TYPE_SH_CVD_EDU_CESS, 6 , /*bduvarag for the bug#5989740*/--modified by walton for inclusive tax
3440 0
3441 )
3442 ) tax_type_val
3443 , b.mod_cr_percentage
3444 , b.vendor_id
3445 , b.tax_type
3446 , nvl(b.rounding_factor,0) rounding_factor
3447 , b.adhoc_flag
3448 , a.tax_category_id
3449 , b.inclusive_tax_flag --added by walton for inclusive tax on 08-Dev-07
3450 from JAI_CMN_TAX_CTG_LINES a
3451 , JAI_CMN_TAXES_ALL b
3452 , jai_regime_tax_types_v rgm_tax_types /* added by ssumaith - bug# 4245053*/
3453 where a.tax_category_id in (p_tax_category_id, nvl(p_threshold_tax_cat_id,-1))
3454 and rgm_tax_types.tax_type (+) = b.tax_type /* added by ssumaith - bug# 4245053*/
3455 and a.tax_id = b.tax_id
3456 order by decode (a.tax_category_id, p_tax_category_id, a.line_no
3457 , (p_max_tax_line + a.line_no)
3458 );
3459 --Added by Kevin Cheng for Retroactive Price 2008/01/13
3460 --=====================================================
3461 ELSIF pv_retroprice_changed = 'Y'
3462 THEN
3463 open ref_tax_cur
3464 for select a.tax_id
3465 , decode (a.tax_category_id, p_tax_category_id, a.line_no
3466 , (p_max_tax_line + a.line_no)
3467 ) lno
3468 , decode (a.tax_category_id, p_tax_category_id, a.precedence_1
3469 , decode (a.precedence_1, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_1))
3470 ) p_1
3471 , decode (a.tax_category_id, p_tax_category_id, a.precedence_2
3472 , decode (a.precedence_2, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_2))
3473 ) p_2
3474 , decode (a.tax_category_id, p_tax_category_id, a.precedence_3
3475 , decode (a.precedence_3, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_3))
3476 ) p_3
3477 , decode (a.tax_category_id, p_tax_category_id, a.precedence_4
3478 , decode (a.precedence_4, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_4))
3479 ) p_4
3480 , decode (a.tax_category_id, p_tax_category_id, a.precedence_5
3481 , decode (a.precedence_5, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_5))
3482 ) p_5
3483 /* Bug 5094130. Added by Lakshmi Gopalsami Included precedences 6 to 10*/
3484 , decode (a.tax_category_id, p_tax_category_id, a.precedence_6
3485 , decode (a.precedence_6, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_6))
3486 ) p_6
3487 , decode (a.tax_category_id, p_tax_category_id, a.precedence_7
3488 , decode (a.precedence_7, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_7))
3489 ) p_7
3490 , decode (a.tax_category_id, p_tax_category_id, a.precedence_8
3491 , decode (a.precedence_8, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_8))
3492 ) p_8
3493 , decode (a.tax_category_id, p_tax_category_id, a.precedence_9
3494 , decode (a.precedence_9, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_9))
3495 ) p_9
3496 , decode (a.tax_category_id, p_tax_category_id, a.precedence_10
3497 , decode (a.precedence_10, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_10))
3498 ) p_10
3499 , b.tax_rate
3500 , b.tax_amount
3501 , b.uom_code
3502 , b.end_date valid_date
3503 , DECODE(rgm_tax_types.regime_Code,jai_constants.vat_regime, 4, /* added by ssumaith - bug# 4245053*/
3504 DECODE(UPPER(b.tax_type),
3505 'EXCISE', 1,
3506 'ADDL. EXCISE', 1,
3507 'OTHER EXCISE', 1,
3508 'TDS', 2,
3509 'EXCISE_EDUCATION_CESS' ,1,
3510 JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS , 1 , /*bduvarag for the bug#5989740*/
3511 'CVD_EDUCATION_CESS' ,1,
3512 JAI_CONSTANTS.TAX_TYPE_SH_CVD_EDU_CESS, 1 , /*bduvarag for the bug#5989740*/
3513 0
3514 )
3515 ) tax_type_val
3516 , b.mod_cr_percentage
3517 , b.vendor_id
3518 , b.tax_type
3519 , nvl(b.rounding_factor,0) rounding_factor
3520 , b.adhoc_flag
3521 , a.tax_category_id
3522 , b.inclusive_tax_flag --Add by Kevin Cheng
3523 from JAI_CMN_TAX_CTG_LINES a
3524 , JAI_CMN_TAXES_ALL b
3525 , jai_regime_tax_types_v rgm_tax_types /* added by ssumaith - bug# 4245053*/
3526 where a.tax_category_id in (p_tax_category_id, nvl(p_threshold_tax_cat_id,-1))
3527 and rgm_tax_types.tax_type (+) = b.tax_type /* added by ssumaith - bug# 4245053*/
3528 and a.tax_id = b.tax_id
3529 order by decode (a.tax_category_id, p_tax_category_id, a.line_no
3530 , (p_max_tax_line + a.line_no)
3531 );
3532 END IF;
3533 --=====================================================
3534 p_refc_tax_cat_taxes_cur := ref_tax_cur;
3535 end get_tax_cat_taxes_cur;
3536 /* end of bug#5631784 */
3537
3538 END jai_cmn_tax_defaultation_pkg;