1 PACKAGE BODY jai_cmn_tax_defaultation_pkg AS
2 /* $Header: jai_cmn_tax_dflt.plb 120.38.12020000.6 2013/03/19 03:04:10 qimeng ship $ */
3
4 /*Bug 8371741 - Start*/
5
6 PROCEDURE get_created_from
7 (
8 p_header_id IN NUMBER,
9 p_created_from OUT NOCOPY VARCHAR2
10 ) IS
11 --PRAGMA AUTONOMOUS_TRANSACTION;
12 BEGIN
13 select jat.created_from into p_created_from
14 from JAI_AR_TRXS jat
15 where jat.customer_trx_id = p_header_id;
16 END;
17
18 /*Bug 8371741 - End*/
19
20 /*
21 Bug 8241905 - Added the procedure to get the quantity from the parent requistion
22 line to split the taxes in the child lines
26 (
23 Used Autonomous transaction as po_requistion_lines_all needs to be queried
24 */
25 PROCEDURE ja_in_po_get_reqline_p
27 p_req_line_id IN NUMBER,
28 p_prev_quantity OUT NOCOPY NUMBER
29 ) IS
30 PRAGMA AUTONOMOUS_TRANSACTION;
31 BEGIN
32 select quantity into p_prev_quantity
33 from po_requisition_lines_all
34 where requisition_line_id = p_req_line_id;
35 END;
36
37 PROCEDURE ja_in_cust_default_taxes (
38 p_org_id NUMBER,
39 p_customer_id NUMBER,
40 p_ship_to_site_use_id NUMBER,
41 p_inventory_item_id IN NUMBER,
42 p_header_id NUMBER,
43 p_line_id NUMBER,
44 p_tax_category_id IN OUT NOCOPY NUMBER
45 )
46 IS
47 v_address_id NUMBER;
48 v_tax_category_list VARCHAR2(30);
49 v_tax_category_id NUMBER;
50
51 /* Added by Ramananda for bug#4407165 */
52 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_tax_defaultation_pkg.ja_in_cust_default_taxes';
53
54 -- to get address_id
55 CURSOR address_cur(p_ship_to_site_use_id IN NUMBER) IS
56 SELECT cust_acct_site_id address_id
57 FROM hz_cust_site_uses_all A -- Removed ra_site_uses_all from Bug# 4434287
58 WHERE A.site_use_id = p_ship_to_site_use_id;
59
60 -- to get tax_category_list
61 CURSOR tax_catg_list_cur(p_customer_id IN NUMBER, p_address_id IN NUMBER DEFAULT 0) IS
62 SELECT tax_category_list
63 FROM JAI_CMN_CUS_ADDRESSES a
64 WHERE A.customer_id = p_customer_id
65 AND A.address_id = p_address_id;
66
67 -- to get tax_category_id
68 CURSOR tax_catg_id_cur(p_tax_category_list IN VARCHAR2, p_inventory_item_id IN NUMBER) IS
69 SELECT tax_category_id
70 FROM JAI_INV_ITM_TAXCTG_DTLS a
71 WHERE a.tax_category_list = p_tax_category_list
72 AND a.inventory_item_id = p_inventory_item_id;
73
74 BEGIN
75
76 OPEN address_cur(p_ship_to_site_use_id);
77 FETCH address_cur INTO v_address_id;
78 CLOSE address_cur;
79
80 IF p_customer_id IS NOT NULL AND v_address_id IS NOT NULL THEN
81 OPEN tax_catg_list_cur(p_customer_id , v_address_id);
82 FETCH tax_catg_list_cur INTO v_tax_category_list;
83 CLOSE tax_catg_list_cur;
84 END IF;
85
86 IF v_tax_category_list IS NULL THEN
87 OPEN tax_catg_list_cur(p_customer_id,0);
88 FETCH tax_catg_list_cur INTO v_tax_category_list;
89 CLOSE tax_catg_list_cur;
90 END IF;
91
92 IF v_tax_category_list IS NOT NULL THEN
93 OPEN tax_catg_id_cur(v_tax_category_list, p_inventory_item_id);
94 FETCH tax_catg_id_cur INTO v_tax_category_id;
95 CLOSE tax_catg_id_cur;
96 -- ELSE -- redundant
97 -- ja_in_org_default_taxes(p_org_id, p_inventory_item_id, v_tax_category_id);
98 END IF;
99
100 IF v_tax_category_id IS NULL THEN
101
102 /* redundant code
103 OPEN tax_catg_list_cur(p_customer_id,0);
104 FETCH tax_catg_list_cur INTO v_tax_category_list;
105 CLOSE tax_catg_list_cur;
106
107 IF v_tax_category_list IS NOT NULL THEN
108 OPEN tax_catg_id_cur(v_tax_category_list ,p_inventory_item_id );
109 FETCH tax_catg_id_cur INTO v_tax_category_id;
110 CLOSE tax_catg_id_cur;
111 -- ELSE -- redundant
112 -- ja_in_org_default_taxes(p_org_id, p_inventory_item_id, v_tax_category_id);
113 END IF;
114
115 IF v_tax_category_id IS NULL THEN
116 ja_in_org_default_taxes(p_org_id, p_inventory_item_id, v_tax_category_id);
117 END IF;
118 */
119 ja_in_org_default_taxes(p_org_id, p_inventory_item_id, v_tax_category_id);
120
121 END IF;
122
123 p_tax_category_id := v_tax_category_id;
124
125
126 /* Added by Ramananda for bug#4407165 */
127 EXCEPTION
128 WHEN OTHERS THEN
129 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
130 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
131 app_exception.raise_exception;
132
133 END ja_in_cust_default_taxes;
134
135 /***********************************************************************************************************************/
136 PROCEDURE ja_in_vendor_default_taxes(
137 p_org_id NUMBER,
138 p_vendor_id NUMBER,
139 p_vendor_site_id NUMBER,
140 p_inventory_item_id IN NUMBER,
141 p_header_id NUMBER,
142 p_line_id NUMBER,
143 p_tax_category_id IN OUT NOCOPY NUMBER
144 ) IS
145
146 v_tax_category_list VARCHAR2(30);
147 v_tax_category_id NUMBER;
148
149 /* Added by Ramananda for bug#4407165 */
150 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_tax_defaultation_pkg.ja_in_vendor_default_taxes';
151
152 -- to get tax_category_list
153 CURSOR tax_catg_list_cur(p_vendor_id IN NUMBER, p_vendor_site_id IN NUMBER DEFAULT 0) IS
154 SELECT tax_category_list
155 FROM JAI_CMN_VENDOR_SITES A
156 WHERE a.vendor_id = p_vendor_id
157 AND a.vendor_site_id = p_vendor_site_id;
158
159 -- to get tax_category_id
160 CURSOR tax_catg_id_cur(p_tax_category_list IN VARCHAR2, p_inventory_item_id IN NUMBER) IS
161 SELECT tax_category_id
162 FROM JAI_INV_ITM_TAXCTG_DTLS a
163 WHERE a.tax_category_list = p_tax_category_list
164 AND a.inventory_item_id = p_inventory_item_id;
165
166 BEGIN
167
168 IF p_vendor_id IS NOT NULL AND p_vendor_site_id IS NOT NULL THEN
169 OPEN tax_catg_list_cur(p_vendor_id, p_vendor_site_id);
170 FETCH tax_catg_list_cur INTO v_tax_category_list;
174 IF v_tax_category_list IS NULL THEN
171 CLOSE tax_catg_list_cur;
172 END IF;
173
175 OPEN tax_catg_list_cur(p_vendor_id,0);
176 FETCH tax_catg_list_cur INTO v_tax_category_list;
177 CLOSE tax_catg_list_cur;
178 END IF;
179
180 IF v_tax_category_list IS NOT NULL THEN
181 OPEN tax_catg_id_cur(v_tax_category_list ,p_inventory_item_id );
182 FETCH tax_catg_id_cur INTO v_tax_category_id;
183 CLOSE tax_catg_id_cur;
184 -- ELSE redundant code
185 -- ja_in_org_default_taxes(p_org_id, p_inventory_item_id, v_tax_category_id);
186 END IF;
187
188 IF NVL(v_tax_category_id,0) = 0 THEN
189
190 /* REDUNDANT CODE
191 OPEN tax_catg_list_cur(p_vendor_id,0);
192 FETCH tax_catg_list_cur INTO v_tax_category_list;
193 CLOSE tax_catg_list_cur;
194
195 IF v_tax_category_list IS NOT NULL THEN
196 OPEN tax_catg_id_cur(v_tax_category_list ,p_inventory_item_id );
197 FETCH tax_catg_id_cur INTO v_tax_category_id;
198 CLOSE tax_catg_id_cur;
199 ELSE
200 ja_in_org_default_taxes(p_org_id, p_inventory_item_id, v_tax_category_id);
201 END IF;
202
203 IF NVL(v_tax_category_id,0) = 0 THEN
204 ja_in_org_default_taxes(p_org_id, p_inventory_item_id, v_tax_category_id);
205 END IF;
206 */
207
208 ja_in_org_default_taxes(p_org_id, p_inventory_item_id, v_tax_category_id);
209
210 END IF;
211
212 p_tax_category_id := v_tax_category_id;
213
214 /* Added by Ramananda for bug#4407165 */
215 EXCEPTION
216 WHEN OTHERS THEN
217 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
218 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
219 app_exception.raise_exception;
220
221 END ja_in_vendor_default_taxes;
222
223 ---------------******************JA_IN_ORG_DEFAULT_TAXES******************---------------------
224
225 PROCEDURE ja_in_org_default_taxes(
226 p_org_id NUMBER,
227 p_inventory_item_id IN NUMBER,
228 p_tax_category_id IN OUT NOCOPY NUMBER
229 ) IS
230 /*------------------------------------------------------------------------------------------
231 CHANGE HISTORY:
232
233 Sl. YYYY/MM/DD Author and Details
234 ------------------------------------------------------------------------------------------
235 1 2004/09/22 Aiyer for bug#3792765. Version#115.2
236 Issue
237 Warehouse ID is not currently being allowed to be left null from the base apps sales order. When placing a order from 2
238 different manufacturing organizations, it is required that customer temporarily leaves the warehouseid as Null and then
239 updates the same before pick release. However this is currently not allowed by localization even though base
240 apps allows this feature.
241
242 Reason:-
243 The trigger ja_in_oe_order_lines_aiu_trg raises an error of warehouse not found when the value of warehouse_id goes as
244 null from the form.
245
246 Solution:-
247 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
248 for the same.
249 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
250 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
251 master organization set for the default operating unit is picked up for further processing
252
253 Dependency Due to this Bug:-
254 Functional dependency with the trigger ja_in_oe_order_lines_aiu_trg.sql version 115.4
255
256 2 31/10/2006 SACSETHI for bug 5228046, File version 120.3
257 1. Forward porting the change in 11i bug 5365523 (Additional CVD Enhancement).
258 This bug has datamodel and spec changes.
259
260 2. Forward porting of bug 5219225
261
262 3 25/04/2007 cbabu for BUG#6012570 (5876390 )version = 120.5 (115.29 )
263 FP: Project Billing
264 4 05/06/2007 bduvarag for the bug#6081966 and 5989740, File version 120.8
265 forward porting the 11i bugs 6074792 and 5907436
266
267 5. 01-08-2007 rchandan for bug#6030615 , Version 120.10
268 Issue : Inter org Forward porting
269 ----------------------------------------------------------------------------------------------------------------------------------------------------*/
270 /* Added by Ramananda for bug#4407165 */
271 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_tax_defaultation_pkg.ja_in_org_default_taxes';
272
273
274 --Code Added on 18-SEP-2000, Srihari and Gsrinivas
275 v_operating_unit NUMBER;
276 /*
277 || Start of bug 3792765
278 || Cursor added by aiyer to get the operating unit and master organization id in case the warehouse id is passed
279 || as null from the Base apps sales order form.
280 */
281 CURSOR cur_get_master_org_id
282 IS
283 SELECT
284 org_id operating_unit,
285 master_organization_id
286 FROM
287 oe_system_parameters ;
288 /*
289 || End of bug 3792765
290 */
291
292
293 CURSOR operating_unit_cur(c_org_id NUMBER) IS
294 SELECT operating_unit
295 FROM org_organization_definitions
296 WHERE organization_id = NVL(c_org_id, 0);
297
298 -- to get tax_category_id from Item class
299 CURSOR tax_catg_id_cur(v_org_id IN NUMBER, v_inventory_item_id IN NUMBER, v_operating_unit NUMBER) IS
303 AND a.inventory_item_id = v_inventory_item_id
300 SELECT b.tax_category_id
301 FROM JAI_INV_ITM_SETUPS a , JAI_CMN_TAX_CTGS_ALL b -- redundant, org_organization_definitions c
302 WHERE a.item_class = b.item_class_cd
304 AND a.organization_id = v_org_id
305 AND b.org_id = v_operating_unit;
306 --End of Addition , Srihari and Gsrinivas
307
308 rec_cur_get_master_org_id CUR_GET_MASTER_ORG_ID%ROWTYPE;
309 BEGIN
310 /*
311 || Start of bug 3792765
312 || IF the warehouse id i.e the invemtory organization id is null from the SAles order base apps form
313 || then in that case get the operating unit and master organization id from the oe_system_parameters table
314 */
315
316 IF p_org_id IS NULL THEN
317 OPEN cur_get_master_org_id;
318 FETCH cur_get_master_org_id INTO rec_cur_get_master_org_id ;
319 CLOSE cur_get_master_org_id ;
320 v_operating_unit := rec_cur_get_master_org_id.operating_unit;
321 ELSE
322 /*
323 || End of bug 3792765
324 */
325
326 OPEN operating_unit_cur(p_org_id);
327 FETCH operating_unit_cur INTO v_operating_unit;
328 CLOSE operating_unit_cur;
329 END IF;
330
331 v_operating_unit := NVL(v_operating_unit, 0);
332
333 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);
334 FETCH tax_catg_id_cur INTO p_tax_category_id;
335 CLOSE tax_catg_id_cur;
336
337 /* Added by Ramananda for bug#4407165 */
338 EXCEPTION
339 WHEN OTHERS THEN
340 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
341 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
342 app_exception.raise_exception;
343
344 END ja_in_org_default_taxes;
345
346 /*****************************JA_IN_CALC_PREC_TAXES********************************/
347
348 PROCEDURE ja_in_calc_prec_taxes(
349 transaction_name VARCHAR2,
350 p_tax_category_id NUMBER,
351 p_header_id NUMBER,
352 p_line_id NUMBER,
353 p_assessable_value NUMBER DEFAULT 0,
354 p_tax_amount IN OUT NOCOPY NUMBER,
355 p_inventory_item_id NUMBER,
356 p_line_quantity NUMBER,
357 p_uom_code VARCHAR2,
358 p_vendor_id NUMBER,
359 p_currency VARCHAR2,
360 p_currency_conv_factor NUMBER,
361 p_creation_date DATE,
362 p_created_by NUMBER,
363 p_last_update_date DATE,
364 p_last_updated_by NUMBER,
365 p_last_update_login NUMBER,
366 p_operation_flag NUMBER DEFAULT NULL , -- for CRM this is used to hold aso_shipments.shipment_id
367 p_vat_assessable_value NUMBER DEFAULT 0
368 /** bgowrava for forward porting bug#5631784,Following parameters are added for TCS enh.*/
369 , 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
370 , p_threshold_tax_cat_id JAI_AP_TDS_THHOLD_TAXES.tax_category_id%type default null
371 , p_source_trx_type jai_cmn_document_taxes.source_doc_type%type default null
372 , p_source_table_name jai_cmn_document_taxes.source_table_name%type default null
373 , p_action varchar2 default null
374 /** End bug 5631784 */
375 , pv_retroprice_changed IN VARCHAR2 DEFAULT 'N' --Added by Kevin Cheng for Retroactive Price 2008/01/13
376 , p_modified_by_agent_flag po_requisition_lines_all.modified_by_agent_flag%type default NULL /*Added for Bug 8241905*/
377 , p_parent_req_line_id po_requisition_lines_all.parent_req_line_id%type default NULL /*Added for Bug 8241905*/
378 , p_max_tax_line NUMBER DEFAULT 0 /*Added for Bug 8371741*/
379 , p_max_rgm_tax_line NUMBER DEFAULT 0 /*Added for Bug 8371741*/
380 --, pn_gst_assessable_value NUMBER DEFAULT 0 -- Added by Jia for GST Bug#10043656 on 2010/09/10
381 , pn_customs_assessable_value NUMBER DEFAULT 0 --Added by Bo Li for Bug#11684111 BOE Ehancement
382 ) IS
383 --TYPE num_tab IS TABLE OF NUMBER(30,3) INDEX BY BINARY_INTEGER; -- sriram - bug # 2812781 was 14 eaerler changed to 30
384 --TYPE tax_amt_num_tab IS TABLE OF NUMBER(30,3) INDEX BY BINARY_INTEGER; -- sriram - bug # 2812781 was 14 eaerler changed to 30
385
386
387 -- Date 02/11/2006 Bug 5228046 added by SACSETHI
388
389 TYPE num_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
390 TYPE tax_amt_num_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
391 TYPE tax_adhoc_flag_tab IS TABLE OF VARCHAR2(2) INDEX BY BINARY_INTEGER; /* rchandan bug#6030615 */
392
393 --added by Walton for inclusive tax 07-Dev-07
394 ---------------------------------------------
395 TYPE char_tab IS TABLE OF VARCHAR2(10)
396 INDEX BY BINARY_INTEGER;
397
398 lt_adhoc_tax_tab CHAR_TAB;
399 lt_inclusive_tax_tab CHAR_TAB;
400 ln_exclusive_price NUMBER;
401 lt_tax_rate_per_rupee NUM_TAB;
402 lt_cumul_tax_rate_per_rupee NUM_TAB;
403 ln_total_non_rate_tax NUMBER :=0;
404 ln_total_inclusive_factor NUMBER;
405 lt_tax_amt_rate_tax_tab TAX_AMT_NUM_TAB;
406 lt_tax_amt_non_rate_tab TAX_AMT_NUM_TAB;
407 ln_bsln_amt_nr NUMBER :=0;
408 ln_tax_amt_nr NUMBER(38,10) :=0;
409 ln_vamt_nr NUMBER(38,10) :=0;
410 ln_total_tax_per_rupee NUMBER;
411 ln_assessable_value NUMBER;
412 ln_vat_assessable_value NUMBER;
413 -----------------------------------------------
414 --ln_gst_assessable_value NUMBER; -- Added by Jia for GST Bug#10043656 on 2010/09/10
415 base_tax_amount_nr_tab tax_amt_num_tab; --added by Xiao Lv for bug#8789761
419 /* Added by Ramananda for bug#4407165 */
416 ln_amount NUMBER; --Added shujuan for bug
417
418 ln_customs_assessable_value NUMBER; --Added by Bo Li for Bug#11684111 BOE Ehancement
420 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_tax_defaultation_pkg.ja_in_calc_prec_taxes';
421
422 p1 NUM_TAB;
423 p2 NUM_TAB;
424 p3 NUM_TAB;
425 p4 NUM_TAB;
426 p5 NUM_TAB;
427
428 -- Date 31/10/2006 Bug 5228046 added by SACSETHI
429 -- start bug 5228046
430 p6 NUM_TAB;
431 p7 NUM_TAB;
432 p8 NUM_TAB;
433 p9 NUM_TAB;
434 p10 NUM_TAB;
435 line_no_tab NUM_TAB; --added for bug#9214366
436 -- end bug 5228046
437
438 tax_rate_tab NUM_TAB;
439 /*
440 || Aiyer for the fwd ported bug#4691616 . Added tax_rate_zero_tab table
441 -------------------------------------------------------------
442 tax_rate(i) tax_rate_tab(i) tax_rate_zero_tab(i)
443 -------------------------------------------------------------
444 NULL 0 0
445 0 0 -9999
446 n (non-zero and not null) n n
447 -------------------------------------------------------------
448 */
449 tax_rate_zero_tab NUM_TAB;
450
451 tax_type_tab NUM_TAB;
452 qty_rate_tab NUM_TAB; /*By mmurtuza for Bug 14358278*/
453 tax_target_tab NUM_TAB;
454 tax_amt_tab TAX_AMT_NUM_TAB;
455 round_factor_tab TAX_AMT_NUM_TAB; --added by csahoo for bug#6077133
456 base_tax_amt_tab TAX_AMT_NUM_TAB;
457 func_tax_amt_tab TAX_AMT_NUM_TAB;
458 adhoc_flag_tab TAX_ADHOC_FLAG_TAB ; /* rchandan bug#6030615 */
459 end_date_tab NUM_TAB;
460
461 bsln_amt NUMBER; -- := p_tax_amount; --Ramananda for File.Sql.35
462
463 v_conversion_rate NUMBER; -- := 0; --Ramananda for File.Sql.35
464 v_currency_conv_factor NUMBER; -- := p_currency_conv_factor; --Ramananda for File.Sql.35
465
466
467 -- Date 01/11/2006 Bug 5228046 added by SACSETHI
468
469 -- v_tax_amt NUMBER(25,3) := 0; -- cbabu for EnhancementBug# 2427465
470 -- v_func_tax_amt NUMBER(25,3) := 0;
471 -- vamt NUMBER(25,3) := 0;
472
473 v_tax_amt NUMBER := 0;
474 v_func_tax_amt NUMBER := 0;
475 vamt NUMBER := 0;
476
477 v_amt NUMBER;
478 row_count NUMBER := 1;
479 counter NUMBER;
480 max_iter NUMBER := 10;
481 v_excise_jb NUMBER;
482
483 v_line_focus_id_holder JAI_PO_LINE_LOCATIONS.line_focus_id%TYPE; -- cbabu for EnhancementBug# 2427465
484
485
486 /** bgowrava, Begin forward porting bug#5631784 */
487 refc_tax_cur ref_cur_typ;
488 rec tax_rec_typ;
489 type tax_table_typ is
490 table of tax_rec_typ index by binary_integer;
491 lt_tax_table tax_table_typ;
492
493 ln_max_tax_line number;
494 ln_max_rgm_tax_line number;
495 ln_base number ;
496 ln_dup_tax_exists number;
497
498 v_modvat_flag varchar2(1); -- moved from prev location to here
499 lv_recalculation_sql varchar2 (4000);
500 ln_exists number (2);
501
502 ln_prev_quantity NUMBER; /*Bug 8241905*/
503 ln_tax_modified_flag VARCHAR2(1); /*Bug 8241905*/
504 ln_rounding_factor NUMBER; /*Bug 8241905*/
505 l_created_from VARCHAR2(10); /*Bug 8371741*/
506
507 /*
508 || Cursor will check if the given tax_categories have common taxes
509 */
510 cursor c_chk_tax_duplication
511 is
512 select 1
513 from JAI_CMN_TAX_CTG_LINES
514 where tax_category_id in (p_tax_category_id, nvl(p_threshold_tax_cat_id,-1))
515 group by tax_id
516 having count(tax_id) > 1;
517
518 cursor c_get_max_tax_line
519 is
520 select max(line_no) max_tax_line
521 from JAI_CMN_TAX_CTG_LINES cat
522 where cat.tax_category_id = p_tax_category_id;
523
524 cursor c_get_max_rgm_tax_line
525 is
526 select max(line_no) max_rgm_tax_line
527 from JAI_CMN_TAX_CTG_LINES cat, JAI_CMN_TAXES_ALL taxes
528 where cat.tax_category_id = p_tax_category_id
529 and taxes.tax_id = cat.tax_id
530 and taxes.tax_type = p_thhold_cat_base_tax_typ;
531
532 /** End Bug#5631784*/
533
534
535 CURSOR tax_cur(p_tax_category_id IN NUMBER) IS
536 SELECT a.tax_category_id, a.tax_id, a.line_no lno,
537 a.precedence_1 p_1, a.precedence_2 p_2,
538 a.precedence_3 p_3, a.precedence_4 p_4,
539 a.precedence_5 p_5, a.precedence_6 p_6, -- Date 31/10/2006 Bug 5228046 added by SACSETHI
540 a.precedence_7 p_7, a.precedence_8 p_8,
541 a.precedence_9 p_9, a.precedence_10 p_10,
542 b.tax_rate, b.tax_amount, b.uom_code, b.end_date valid_date,
543 DECODE(rgm_tax_types.regime_Code,jai_constants.vat_regime, 4, /* added by ssumaith - bug# 4245053*/
544 /*jai_constants.cgst_regime, 7, --Added by Jia for GST Bug#10043656 on 2010/09/10
545 jai_constants.sgst_regime, 7, --Added by Jia for GST Bug#10043656 on 2010/09/10*/
549 'ADDL. EXCISE', 1,
546 jai_constants.customs_regime, 8, --Added by Bo Li for Bug#11684111 BOE Ehancement
547 DECODE(UPPER(b.tax_type),
548 'EXCISE', 1,
550 'OTHER EXCISE', 1,
551 'TDS', 2,
552 'EXCISE_EDUCATION_CESS' ,1,
553 'CVD_EDUCATION_CESS' ,1,
554 0
555 )
556 ) tax_type_val,
557 b.mod_cr_percentage, b.vendor_id, b.tax_type,nvl(b.rounding_factor,0) rounding_factor
558 , inclusive_tax_flag --added by walton for inclusive tax 08-Dev-07
559 FROM JAI_CMN_TAX_CTG_LINES a, JAI_CMN_TAXES_ALL b ,
560 jai_regime_tax_types_v rgm_tax_types /* added by ssumaith - bug# 4245053*/
561 WHERE a.tax_category_id = p_tax_category_id
562 AND rgm_tax_types.tax_type (+) = b.tax_type /* added by ssumaith - bug# 4245053*/
563 AND a.tax_id = b.tax_id
564 -- AND (b.end_date >= sysdate OR b.end_date IS NULL)
565 ORDER BY A.line_no;
566
567 CURSOR uom_class_cur(p_line_uom_code IN VARCHAR2, p_tax_line_uom_code IN VARCHAR2) IS
568 SELECT a.uom_class
569 FROM mtl_units_of_measure A, mtl_units_of_measure B
570 WHERE a.uom_code = p_line_uom_code
571 AND b.uom_code = p_tax_line_uom_code
572 AND a.uom_class = b.uom_class;
573
574 --2001/03/30 Manohar Mishra
575 /*Start of Addition*/
576 v_organization_id NUMBER;
577 v_location_id NUMBER;
578 v_batch_source_id NUMBER;
579 v_register_code JAI_OM_OE_BOND_REG_HDRS.register_code%TYPE;
580
581 CURSOR get_header_info_cur IS
582 SELECT organization_id, location_id, batch_source_id
583 FROM JAI_AR_TRXS
584 WHERE customer_trx_id = p_header_id;
585
586 CURSOR get_register_code_cur(p_organization_id NUMBER, p_location_id NUMBER, p_batch_source_id NUMBER) IS
587 SELECT register_code
588 FROM JAI_OM_OE_BOND_REG_HDRS
589 WHERE organization_id = p_organization_id
590 AND location_id = p_location_id
591 AND register_id IN (SELECT register_id
592 FROM JAI_OM_OE_BOND_REG_DTLS
593 WHERE order_type_id = p_batch_source_id
594 AND order_flag = 'N');
595
596 v_debug boolean := true; -- Vijay Shankar for Bug# 2837970
597 v_line_num number:=1 ;--added by rchandan for bug#6030615
598 ln_reg_id number;
599
600 /*End of Addition*/
601 uom_cls UOM_CLASS_CUR%ROWTYPE;
602
603 lv_tax_remain_flag VARCHAR2(1); --Added by Kevin Cheng for Retroactive Price 2008/01/13
604 lv_transaction_name VARCHAR2(30); --Added by Kevin Cheng for Retroactive Price 2008/01/13
605 lv_start NUMBER; --Added by Kevin Cheng for Retroactive Price 2008/01/13
606 lv_line_loc_id NUMBER; --Added by Kevin Cheng for Retroactive Price 2008/01/13
607 lv_process_flag VARCHAR2(10); --Added by Kevin Cheng for Retroactive Price 2008/01/13
608 lv_process_message VARCHAR2(2000); --Added by Kevin Cheng for Retroactive Price 2008/01/13
609
610 lv_tax_type varchar2(30); --Add by Qiong Liu for bug#11684111 BOE Ehancement
611 lv_boe_flag VARCHAR2(1);-- Add by Wenqiong for bug#12611347
612
613 BEGIN
614 /*************************************************************************************************************************
615 /*----------------------------------------------------------------------------------------------------------------------
616 CHANGE HISTORY: Procedure ja_in_calc_prec_taxes
617 S.No Date Author and Details
618 ------------------------------------------------------------------------------------------------------------------------
619
620 1. 12/07/2003 Aiyer - Bug #3749294 File Version 115.1
621 Issue:-
622 Uom based taxes do not get calculated correctly if the transaction UOM is different from the
623 UOM setup in the tax definitions India Localization (JAI_CMN_TAXES_ALL table).
624
625 Reason:-
626 --------
627 This was happening because the UOM calculation was previously happening only for cases of exact match
628 between transaction uom and setup UOM.
629
630 Fix:-
631 ----
632 Modified the procedure ja_in_calc_prec_taxes.The exact match condition was removed. Now if an exact match
633 is not found then the conversion rate between the two uom's is determined and tax amounts are calculated for
634 defaultation.
635
636 Dependency Due to This Bug:-
637 ----------------------------
638 None
639
640 2. 27/01/2005 ssumaith - bug#4136981 - Version 115.2
641
642 In case of Bond Register scenario , in addition to excise taxes , Excise Cess (EXCISE_EDUCATION_CESS,CVD_EDUCATION_CESS)
643 should also not flow to AR
644
645 3. 2005/03/10 ssumaith - bug# 4245053 - File version 115.3
646
647 Taxes under the vat regime needs to be calculated based on the vat assessable value setup done.
648 In the vendor additional information screen and supplier additional information screen, a place
649 has been given to capture the vat assessable value.
650
651 This needs to be used for the calculation of the taxes registered under vat regime.
652
653 This change has been done by using the view jai_regime_tax_types_v - and outer joining it with the
657
654 JAI_CMN_TAXES_ALL table based on the tax type column.
655
656 Parameter - p_vat_assessable_value NUMBER DEFAULT 0 has been added
658 Dependency due to this bug - Huge
659 This patch should always be accompanied by the VAT consolidated patch - 4245089
660
661
662 4. 01-Jun-2006 Aiyer for bug# 4691616. File Version 120.2
663 Issue:-
664 UOM based taxes do not get calculated correctly.
665
666 Solution:-
667 Fwd ported the fix for the bug 4729742.
668 Changed the files JAINTAX1.pld, jai_cmn_tax_dflt.plb and jai_om_tax.plb.
669
670 5. 24-Jan-2007 bgowrava for forward porting bug#5631784, File Version 120.3 - TCS Enhancement
671
672 Issue:
673 1. As a part of TCS enh. there was a requirement to default taxes using the tax category attached to
674 threshold setup for TCS regime in the India-Threshold setup UI.
675 2. Package should provide an API for inserting taxes in to new table jai_cmn_document_taxes
676
677 Fix:
678 To support above functionalities the following approach is used.
679 1. New parameters are added to this procedure to get the tax category defined for threshold limit.
680 (Please refer the procedure signature)
681 2. Whenever p_threshold_tax_cat_id is not NULL then it means taxes from two categories needs to be merged.
682 one using p_tax_category_id and other is p_threshold_tax_cat_id
683 3. current driving cursor (tax_cur) is modified to handle multiple tax categories.
684 3.1 For all the tax lines defined in the p_tax_category_id there is no change
685 3.2 For all the tax lines defined in the p_threshold_tax_cat_id, line_no will be changed
686 to ln_max_tax_line + line_no where ln_max_tax_line is the maximum of line numbers for
687 tax lines defined in p_tax_category_id
688 3.3 All the precedences defined in p_threshold_tax_cat_id will be changed as following
689 - If precedence refers to base precedence (i.e. 0) it will be changed to ln_max_rgm_tax_line
690 where ln_max_rgm_tax_line is maximum of the line numbers of taxes having
691 tax_type = p_thhold_cat_base_tax_typ (i.e. tax type to be considered as a base tax
692 when calculating threshold taxes defined using p_threshold_tax_cat_id)
693 - All other precedences will be changed to precedence_N + ln_max_tax_line
694
695
696 6 04/june/2007 ssumaith - bug#6109941 - review comments for TCS .
697 TCS enhancement forward porting has some minor issues that were resolved.
698
699 7. 05-Jun-2007 CSahoo for bug#6077133, File version- 120.7
700 Issue: The Taxes at header and the Line level does not
701 tally for the Manually created AR Transaction.
702 Fix: added a rounding factor round_factor_tab.
703
704 8. 16-Oct-2007 CSahoo for bug#6498072, File Version 120.12
705 R12RUP04-ST1: TCS TAXES ARE WRONG ON ADDING SURCHARGE
706 On creating a sales order and after delivery the taxes are taken only for a Single Quantity which is wrong.
707 so made changes in the code so that the taxes are taken for the whole quantity
708 9 01-Dev-2007 Walton for Inclusive Tax
709
710 10. 20-Nov-2008 JMEENA for bug#6488296( FP of 6475430)
711 Added OR condition in procedure ja_in_calc_prec_taxes as we are passing p_action null in case of 'CASH' Receipt.
712
713 11. 14-Sep-2009 JMEENA for bug#8905076
714 Modified the update statement of table JAI_OM_WSH_LINE_TAXES and used base_tax_amt_tab instead of
715 tax_amt_tab to update the column base_tax_amount.
716
717 12. 4-Nov-2009 Xiao Lv for bug#8789761
718 Added variable base_tax_amount_nr_tab with type of tax_amt_num_tab to calculate base tax amount.
719 base_tax_amt_tab(I) := ln_exclusive_price*base_tax_amt_tab(I) + base_tax_amount_nr_tab.
720
721 13. 22-Dec-2009 CSahoo for bug#9214366, File Version 120.13.12010000.9
722 Issue: UNABLE TO SAVE AR TRANSACTION WITH VAT + TCS OR CST +TCS TYPE OF TAXES
723 Fix: Modified the code in the procedure ja_in_calc_prec_taxes. Added a new table type variable line_no_tab
724 to store the line number of the tax lines. Further added a code to initialize the tax_amt_tab table
725 for surcharge taxes.
726 14. 19-Mar-2010 Walton for bug#9288016, File Version 120.13.12010000.10
727 Issue: Function tax amount column is not populated correctly
728 Fix: in the old code, function tax amount only cover rate amount, when the taxes is computed based on assessable
729 value, non-rate amount is not getting summed, so the fix is to re-assign function base amount once tax amount
730 is computed.
731 15. 19-Mar-2010 JMEENA for bug#9489492
732 Modified the dynamic query lv_recalculation_sql and changed the order of columns $$EXTRA_SELECT_COLUMN_LIST$$ and inclusive_tax_flag
733 as inclusive_tax_flag is last column in the record type.
734
735 16 10-JUN-2010 Bo Li for Bug#9780751
736 Issue - Round factor of vat tax is 0. But the accounting entry amount inserted into gl_interface table has 2 bit demical.
740 Issue : EXCISE CESS AND SHECESS TAXES NOT GETTING CALCULATED CORRECTLY ON ORDER SPLIT
737 Fix - Set the same round factor to the functional tax amount which is used in the gl_interface table
738
739 17. 27-aug-2010 vkaranam for bug#9692478
741 Issue is due to the base tax amount not calculated correctly.
742 Fix:
743 base_tax_amount_nr_tab has been initalized correctly .
744
745 18. 10-Sep-2010 Jia for GST Bug#10043656.
746
747 19. 11-Mar-2011 Bo Li for BOE Enhancement bug#11684111
748
749 20. 16-Jan-2012 Qinglei for Bug#13547239
750 Issue: FUNCTIONAL TAX AMOUNT IN INDIA LOCALIZATION TAXES UI IS INACCURATE
751 Fix: Rounded the functional tax amount before insert into jai_cmn_document_taxes.
752
753 21. 07-Jun-2012 mmurtuza for bug 14591926
754 Issue: TAX CALCUALTION IS GOING WRONG WITH INCLUSIVE TAXES
755 Fix: While calculating the tax amount for exclusive taxes, added condition to check if the corresponding tax line no of precedence of
756 exclusive tax is inclusive.
757
758 22. 27-Sep-2012 mmurtuza for bug 14675629
759 Issue: IN SHIP CONFIRM LOCALIZED FORM, WRONG TCS TAX AMOUNTS GETTING DEFAULTED
760 Fix: Added b.tax_amount in lv_recalculation_sql and assigned it to qty_rate_tab(row_count)
761
762 23. 17-Jan-2012 mmurtuza for bug 16172861
763 Issue: INDIA LOC:INCLUSIVE CST TAX AMOUNT CALCULATED WRONGLY IN MANY SALES ORDER
764 Fix: Added condition while calcualting ln_vamt_nr. If ln_bsln_amt_nr = 0, only then p_tax_amount is added
765
766 Future Dependencies For the release Of this Object:-
767 (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/
768 A datamodel change )
769
770 ----------------------------------------------------------------------------------------------------------------------------------------------------
771 Current Version Current Bug Dependent Files Version Author Date Remarks
772 Of File On Bug/Patchset Dependent On
773 jai_cmn_tax_defaultation_pkg.sql
774 ----------------------------------------------------------------------------------------------------------------------------------------------------
775 115.1 2977185 IN60105D2 None -- Aiyer 13/07/2004 Row introduces to start dependency tracking
776
777 115.3 4245053 IN60106 + ssumaith Service Tax and VAT Infrastructure are created
778 4146708 + based on the bugs - 4146708 and 4545089 respectively.
779 4245089
780 *************************************************************************************************************************/
781 --Added by Kevin Cheng for Retroactive Price 2008/01/13
782 --=====================================================
783 IF pv_retroprice_changed = 'N'
784 THEN
785 --=====================================================
786 --Ramananda for File.Sql.35
787 bsln_amt := p_tax_amount ;
788 v_conversion_rate := 0;
789 v_currency_conv_factor := p_currency_conv_factor;
790 ln_base := 0 ;
791
792 IF transaction_name <> 'CRM_QUOTE' THEN -- Vijay Shankar for Bug# 2837970
793 IF v_debug THEN fnd_file.put_line(fnd_file.log, ' transaction_name -> '||transaction_name); END IF;
794
795 --2001/03/30 Manohar Mishra
796 /*Start of Addition*/
797 OPEN get_header_info_cur;
798 FETCH get_header_info_cur INTO v_organization_id, v_location_id, v_batch_source_id;
799 CLOSE get_header_info_cur;
800
801 OPEN get_register_code_cur(v_organization_id, v_location_id, v_batch_source_id);
802 FETCH get_register_code_cur INTO v_register_code;
803 CLOSE get_register_code_cur;
804
805 -- Vijay Shankar for Bug# 2837970
806 ELSE -- this should get executed when tax defaultation is for CRM_QUOTE
807 v_register_code := null;
808 END IF;
809 --2001/03/30 Manohar Mishra
810
811 /*End of Addition*/
812
813 /*Bug 8241905 - Start*/
814 /*
815 Fetch Tax modified flag of the parent line to check if taxes defaulted based on
816 Tax Category attached to Vendor or Item have been modified. If 'Y' then the new
817 taxes need to be used to calculate taxes of child lines else the normal defaultation
818 logic can be used
819 */
820 BEGIN
821 select tax_modified_flag into ln_tax_modified_flag
822 from JAI_PO_REQ_LINES
823 where requisition_line_id = p_parent_req_line_id;
824 EXCEPTION
825 WHEN NO_DATA_FOUND THEN
826 ln_tax_modified_flag := 'N';
827 END;
828
829 /*
830 The line is split using Tools->Modify option in AutoCreate when p_parent_req_line_id is NOT NULL
831 p_modified_by_agent_flag is set to 'Y' if it is parent line
832 */
833 if (transaction_name = 'PO_REQN' and (p_tax_category_id is null or ln_tax_modified_flag = 'Y')
834 and p_modified_by_agent_flag is NULL and p_parent_req_line_id is NOT NULL) then
835
836 for c_req_tax_lines in (select * from JAI_PO_REQ_LINE_TAXES
837 where requisition_line_id = p_parent_req_line_id) loop
838
839 /*Fetch quantity of parent line to populate taxes in child lines*/
840 ja_in_po_get_reqline_p(p_parent_req_line_id, ln_prev_quantity);
841
842 select rounding_factor into ln_rounding_factor
843 from JAI_CMN_TAXES_ALL
847 insert into JAI_PO_REQ_LINE_TAXES(
844 where tax_id = c_req_tax_lines.tax_id;
845
846 /*Insert Tax Lines for child lines based on Tax lines of the parent*/
848 requisition_line_id, requisition_header_id, tax_line_no,
849 precedence_1, precedence_2, precedence_3,
850 precedence_4, precedence_5,
851 precedence_6, precedence_7, precedence_8,
852 precedence_9, precedence_10,
853 tax_id, tax_rate, qty_rate, uom, tax_amount, Tax_Target_Amount,
854 tax_type, modvat_flag, vendor_id, currency,
855 creation_date, created_by, last_update_date,
856 last_updated_by, last_update_login,
857 tax_category_id
858 ) VALUES (
859 p_line_id, c_req_tax_lines.requisition_header_id, c_req_tax_lines.tax_line_no,
860 c_req_tax_lines.precedence_1, c_req_tax_lines.precedence_2, c_req_tax_lines.precedence_3,
861 c_req_tax_lines.precedence_4, c_req_tax_lines.precedence_5,
862 c_req_tax_lines.precedence_6, c_req_tax_lines.precedence_7, c_req_tax_lines.precedence_8,
863 c_req_tax_lines.precedence_9, c_req_tax_lines.precedence_10,
864 c_req_tax_lines.tax_id, c_req_tax_lines.tax_rate, c_req_tax_lines.qty_rate,
865 c_req_tax_lines.uom,
866 round((c_req_tax_lines.tax_amount * p_line_quantity)/ln_prev_quantity, ln_rounding_factor),
867 round((c_req_tax_lines.Tax_Target_Amount * p_line_quantity)/ln_prev_quantity, ln_rounding_factor),
868 c_req_tax_lines.tax_type, c_req_tax_lines.modvat_flag, c_req_tax_lines.vendor_id,
869 c_req_tax_lines.currency,
870 c_req_tax_lines.creation_date, c_req_tax_lines.created_by, c_req_tax_lines.last_update_date,
871 c_req_tax_lines.last_updated_by, c_req_tax_lines.last_update_login,
872 c_req_tax_lines.tax_category_id
873 );
874
875 end loop;
876 end if;
877
878 /*Bug 8241905 - End*/
879
880
881 /** bgowrava for forward porting bug#5631784*/
882 if ((p_tax_category_id is null
883 and (p_threshold_tax_cat_id is null or p_threshold_tax_cat_id <0)) or ln_tax_modified_flag = 'Y') then
884 /*
885 Bug 8241905 - Added ln_tax_modified_flag = 'Y' to prevent taxes defaulting in child lines from parent tax_category_id
886 when the taxes are modified in the parent line. The modified taxes need to used to calculate
887 taxes in child lines
888 */
889 /** Both driving parameter tax_category_id and threshol_tax_category_id are invalid hence no need to do anything */
890 return;
891 end if;
892
893 if nvl(p_action, jai_constants.default_taxes) = jai_constants.default_taxes then
894 /** Assign tax defaultation cursor object to refc_tax_cur reference by using. Call to get_tax_cat_taxes_cur will return
895 a reference cursor */
896
897 if p_threshold_tax_cat_id is not null and p_threshold_tax_cat_id > 0 then
898 /*
899 || Cursor to check if same taxes exists in both tax categories
900 */
901 ln_dup_tax_exists := null;
902 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Open/Close/Fetch cursor c_chk_tax_duplication');*/ --commented by bgowrava for bug#5631784
903 open c_chk_tax_duplication;
904 fetch c_chk_tax_duplication into ln_dup_tax_exists;
905 close c_chk_tax_duplication;
906
907 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'ln_dup_tax_exists='||ln_dup_tax_exists);*/ --commented by bgowrava for bug#5631784
908
909 if ln_dup_tax_exists is not null
910 or (nvl(p_threshold_tax_cat_id,-1) = p_tax_category_id)
911 then
912
913 fnd_message.set_name('JA', 'JAI_DUP_TAX_IN_TAX_CAT');
914 app_exception.raise_exception ;
915
916 end if;
917
918 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Open/Close/Fetch cursor c_get_max_tax_line');*/ --commented by bgowrava for bug#5631784
919 open c_get_max_tax_line;
920 fetch c_get_max_tax_line into ln_max_tax_line;
921 close c_get_max_tax_line ;
922
923 /* 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
924 open c_get_max_rgm_tax_line;
925 fetch c_get_max_rgm_tax_line into ln_max_rgm_tax_line;
926 close c_get_max_rgm_tax_line ;
927
928 /* 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
929
930 end if;
931 /*Bug 8371741 - Start*/
932 if ln_max_rgm_tax_line IS NULL then
933 ln_max_rgm_tax_line := p_max_rgm_tax_line;
934 ln_max_tax_line := p_max_tax_line;
935 end if;
936 /*Bug 8371741 - End*/
937
938 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Opening ref cursor');*/ --commented by bgowrava for bug#5631784
939 get_tax_cat_taxes_cur ( p_tax_category_id => p_tax_category_id
940 , p_threshold_tax_cat_id => p_threshold_tax_cat_id
941 , p_max_tax_line => ln_max_tax_line
942 , p_max_rgm_tax_line => ln_max_rgm_tax_line
946 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Cursor is opened');*/ --commented by bgowrava for bug#5631784
943 , p_refc_tax_cat_taxes_cur => refc_tax_cur
944 );
945
947
948 elsif p_action = jai_constants.recalculate_taxes then
949 /**
950 Following is a dynamic sql string which can be modifed as per requirement
951
952 The sql has four place holders defined as below
953 $$EXTRA_SELECT_COLUMN_LIST$$ - Use this place holder to select additional columns in the sql.
954 You must also change corrosponding fetch statements and the record being used for fetch.
955 SELECT statement above should also be changed to include the newly added columns
956 as they are sharing a common cursor and fetch record.
957
958 $$TAX_SOURCE_TABLE$$ - At runtime this placeholder must be replaced with name of
959 source table to be used for recalculation
960 $$SOURCE_TABLE_FILTER$$ - At runtime, this place holder must represent a boolean condition
961 which can filter required rows from the source table
962 for recalculation. It must be the first condition and should never
963 start with either AND or OR
964 $$ADDITIONAL_WHERE_CLAUSE$$ - Replace the placeholder with additional conditions if any.
965 The condition must start with either AND or OR keyword
966 $$ADDITIONAL_ORDER_BY$$ - Replace the placeholder with list of columns and order sequence, if required.
967 Column list must start with comma (,)
968 If any of this placeholder is not required to be used it must be replaced with a null value as below
969 replace ( lv_recalculation_sql
970 , '$$EXTRA_SELECT_COLUMN_LIST$$'
971 , ''
972 );
973 */
974 lv_recalculation_sql :=
975 ' select a.tax_id
976 , a.tax_line_no lno
977 , a.precedence_1 p_1
978 , a.precedence_2 p_2
979 , a.precedence_3 p_3
980 , a.precedence_4 p_4
981 , a.precedence_5 p_5
982 , a.precedence_6 p_6
983 , a.precedence_7 p_7
984 , a.precedence_8 p_8
985 , a.precedence_9 p_9
986 , a.precedence_10 p_10
987 , a.tax_rate
988 , a.tax_amount
989 , b.tax_amount qty_rate /*Added b.tax_amount qty_rate by murtuza for bug 14675629*/
990 , b.uom_code
991 , b.end_date valid_date
992 , DECODE(rttv.regime_code, '''||jai_constants.vat_regime||''', 4, /* added by ssumaith - bug# 4245053*/
993 /*'''||jai_constants.cgst_regime||''', 7, *//* Added by Jia for GST Bug#10043656 on 2010/09/10 */
994 /*'''||jai_constants.sgst_regime||''', 7, *//* Added by Jia for GST Bug#10043656 on 2010/09/10 */
995 '''||jai_constants.customs_regime||''', 8, /* Added by Bo Li for Bug#11684111 BOE Ehancement */
996 DECODE(UPPER(b.tax_type), ''EXCISE'' , 1
997 , ''ADDL. EXCISE'', 1
998 , ''OTHER EXCISE'', 1
999 , ''TDS'' , 2
1000 , ''EXCISE_EDUCATION_CESS'',6 --modified by walton for inclusive tax
1001 , '''||JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS||''' , 6 /*bduvarag for the bug#5989740*/ --modified by walton for inclusive tax
1002 , ''CVD_EDUCATION_CESS'' ,6 --modified by walton for inclusive tax
1003 , '''||JAI_CONSTANTS.TAX_TYPE_SH_CVD_EDU_CESS||''' , 6 /*bduvarag for the bug#5989740*/ --modified by walton for inclusive tax
1004 , 0
1005 )
1006 ) tax_type_val
1007 , b.mod_cr_percentage
1008 , b.vendor_id
1009 , b.tax_type
1010 , nvl(b.rounding_factor,0) rounding_factor
1011 , b.adhoc_flag
1012 $$EXTRA_SELECT_COLUMN_LIST$$
1013 ,b.inclusive_tax_flag --added by walton for inclusive tax on 08-Dev-07,--Added inclusive_tax_flag in end as it is last column in record type. by JMEENA for bug#9489492
1014 from $$TAX_SOURCE_TABLE$$ a
1015 , JAI_CMN_TAXES_ALL b
1016 , jai_regime_tax_types_v rttv
1017 where $$SOURCE_TABLE_FILTER$$
1018 and rttv.tax_type (+) = b.tax_type
1019 and a.tax_id = b.tax_id $$ADDITIONAL_WHERE_CLAUSE$$
1020 order by a.tax_line_no $$ADDITIONAL_ORDER_BY$$';
1021
1022
1023 /** No extra columns required. Dummy column (NULL) tax_category_id needs to be added in the last as same record (rec) is being used
1024 when fetching the cursor. If there is a need to override this default behaviour then please replace these place holder with
1025 desired strings which can be evaluated at runtime by sql-engine
1029 , '$$EXTRA_SELECT_COLUMN_LIST$$'
1026 */
1027 lv_recalculation_sql :=
1028 replace ( lv_recalculation_sql
1030 , ',null tax_category_id'
1031 );
1032
1033 /** No additional filtering required */
1034 lv_recalculation_sql :=
1035 replace ( lv_recalculation_sql
1036 , '$$ADDITIONAL_WHERE_CLAUSE$$'
1037 , ''
1038 );
1039
1040 /** No additional sorting required */
1041 lv_recalculation_sql :=
1042 replace ( lv_recalculation_sql
1043 , '$$ADDITIONAL_ORDER_BY$$'
1044 , ''
1045 );
1046
1047 if upper(p_source_trx_type) = jai_constants.source_ttype_delivery then
1048
1049 /** replace the correct tax source table */
1050 lv_recalculation_sql :=
1051 replace ( lv_recalculation_sql
1052 , '$$TAX_SOURCE_TABLE$$'
1053 , 'JAI_OM_WSH_LINE_TAXES'
1054 );
1055 /** replace join condition */
1056 lv_recalculation_sql :=
1057 replace ( lv_recalculation_sql
1058 , '$$SOURCE_TABLE_FILTER$$'
1059 , 'a.delivery_detail_id = ' || p_line_id
1060 );
1061 elsif upper(p_source_trx_type) = jai_constants.bill_only_invoice then
1062
1063 /** For bill_only_invoice tax source table is ja_in_ra_cust_trx_tax_lines*/
1064 lv_recalculation_sql :=
1065 replace ( lv_recalculation_sql
1066 , '$$TAX_SOURCE_TABLE$$'
1067 , 'JAI_AR_TRX_TAX_LINES'
1068 );
1069 /** replace join condition */
1070 lv_recalculation_sql :=
1071 replace ( lv_recalculation_sql
1072 , '$$SOURCE_TABLE_FILTER$$'
1073 , 'a.link_to_cust_trx_line_id = ' || p_line_id
1074 );
1075 /*
1076 elsif upper(pv_tax_source_table) = '<some tax table>' then
1077 ...
1078 ...
1079 */
1080
1081 -- Date 24-Apr-2007 Added by SACSETHI for bug 6012570 (5876390)
1082 -- in This , Recalculation will be happen in Draft invoice
1083 ---------------------------------------------------------
1084 elsif upper(p_source_trx_type) = jai_constants.PA_DRAFT_INVOICE then
1085
1086 lv_recalculation_sql :=
1087 replace ( lv_recalculation_sql
1088 , 'a.tax_amount'
1089 , 'a.tax_amt'
1090 );
1091
1092 lv_recalculation_sql :=
1093 replace ( lv_recalculation_sql
1094 , '$$TAX_SOURCE_TABLE$$'
1095 , 'JAI_CMN_DOCUMENT_TAXES'
1096 );
1097 /** replace join condition */
1098 lv_recalculation_sql :=
1099 replace ( lv_recalculation_sql
1100 , '$$SOURCE_TABLE_FILTER$$'
1101 , 'a.SOURCE_DOC_LINE_ID = ' || p_line_id || ' and SOURCE_DOC_TYPE ='''|| jai_constants.PA_DRAFT_INVOICE || ''''
1102 );
1103
1104 -- Added by Jason Liu for standalone invoice on 2007/08/23
1105 ----------------------------------------------------------------------
1106 ELSIF upper(p_source_trx_type) =
1107 jai_constants.G_AP_STANDALONE_INVOICE
1108 THEN
1109
1110 lv_recalculation_sql :=
1111 REPLACE( lv_recalculation_sql
1112 , 'a.tax_amount'
1113 , 'a.tax_amt'
1114 );
1115
1116 lv_recalculation_sql :=
1117 REPLACE( lv_recalculation_sql
1118 , '$$TAX_SOURCE_TABLE$$'
1119 , 'JAI_CMN_DOCUMENT_TAXES'
1120 );
1121 -- replace join condition
1122 lv_recalculation_sql :=
1123 REPLACE( lv_recalculation_sql
1124 , '$$SOURCE_TABLE_FILTER$$'
1125 , 'a.SOURCE_DOC_LINE_ID = ' || p_line_id ||
1126 ' and SOURCE_DOC_TYPE ='''||
1127 jai_constants.G_AP_STANDALONE_INVOICE || ''''
1128 );
1129
1130 -- Added by Zhiwei Hou for Bug#13359892 DTC enhancement on 2011/11/28 begin
1131 ----------------------------------------------------------------------
1132 ELSIF upper(p_source_trx_type) = 'TDS INVOICE'
1133 THEN
1134
1135 lv_recalculation_sql :=
1136 replace ( lv_recalculation_sql
1137 , 'a.tax_id'
1138 , 'a.actual_tax_id'
1139 );
1140
1141 lv_recalculation_sql :=
1142 replace ( lv_recalculation_sql
1143 , 'a.tax_rate'
1144 , 'b.tax_rate'
1145 );
1146
1147 lv_recalculation_sql :=
1148 replace ( lv_recalculation_sql
1149 , 'nvl(b.rounding_factor,0) rounding_factor'
1150 , 'nvl(b.rounding_factor,99) rounding_factor'
1151 );
1152
1153
1154
1155 lv_recalculation_sql :=
1156 replace ( lv_recalculation_sql
1157 , '$$TAX_SOURCE_TABLE$$'
1158 , 'JAI_AP_TDS_INV_TAXES'
1159 );
1160
1161 lv_recalculation_sql :=
1162 replace ( lv_recalculation_sql
1163 , '$$SOURCE_TABLE_FILTER$$'
1167 ----------------------------------------------------------------------
1164 , ' a.invoice_id=' || p_header_id || ' and a.INVOICE_DISTRIBUTION_ID = ' || p_line_id
1165 );
1166
1168 --Added by Zhiwei Hou for Bug#13359892 DTC enhancement on 2011/11/28 end
1169
1170 ----------------------------------------------------------------------
1171 --Added by Bo Li for bug#11684111 BOE Enhancement Begin
1172 --------------------------------------------------------
1173 ELSIF transaction_name = 'BOE_INVOICE_UPDATE'
1174 THEN
1175 lv_recalculation_sql :=
1176 replace ( lv_recalculation_sql
1177 , '$$TAX_SOURCE_TABLE$$'
1178 , 'jai_boe_detail_taxes'
1179 );
1180
1181
1182 lv_recalculation_sql :=
1183 replace ( lv_recalculation_sql
1184 , '$$SOURCE_TABLE_FILTER$$'
1185 , 'a.boe_detail_id = '||p_line_id
1186 );
1187 ---------------------------------------
1188 --Added by Bo Li for bug#11684111 BOE Enhancement End
1189 end if; /*pv_tax_source_table*/
1190
1191 /**
1192 When control comes here, a valid sql statement hold by variable lv_recalculate_sql
1193 must be ready to execute.
1194
1195 open a dynamic select statement using OPEN-FOR statement
1196 */
1197 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'TAX RECALCULATION SQL STATEMENT');
1198 jai_cmn_debug_contexts_pkg.print (ln_reg_id, lv_recalculation_sql);
1199 jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Opening ref cursor with dynamic sql'); */--commented by bgowrava for bug#5631784
1200
1201 open refc_tax_cur for lv_recalculation_sql;
1202
1203 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Cursor is opened with lv_recalculation_sql');*/ --commented by bgowrava for bug#5631784
1204
1205 end if; /** RECALCULATE */
1206
1207 /** Clear the tax table */
1208 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Before fetching cursor rows and starting loop');*/ --commented by bgowrava for bug#5631784
1209 lt_tax_table.delete;
1210
1211 loop
1212 /* 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
1213 fetch refc_tax_cur into rec;
1214 /* 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
1215 exit when refc_tax_cur%notfound;
1216
1217 /** End of bug 5631784*/
1218
1219
1220
1221 /** Add current record in the lt_tax_table for future use at the time of either UPDATE or INSERT into the tables*/
1222
1223 --FOR rec IN tax_cur(p_tax_category_id) LOOP
1224 lt_tax_table(lt_tax_table.count+1) := rec;
1225 p1(row_count) := nvl(rec.p_1,-1);
1226 p2(row_count) := nvl(rec.p_2,-1);
1227 p3(row_count) := nvl(rec.p_3,-1);
1228 p4(row_count) := nvl(rec.p_4,-1);
1229 p5(row_count) := nvl(rec.p_5,-1);
1230 p6(row_count) := nvl(rec.p_6,-1); -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
1231 p7(row_count) := nvl(rec.p_7,-1);
1232 p8(row_count) := nvl(rec.p_8,-1);
1233 p9(row_count) := nvl(rec.p_9,-1);
1234 p10(row_count) := nvl(rec.p_10,-1);
1235 tax_rate_tab(row_count) := NVL(rec.tax_rate,0);
1236 line_no_tab(row_count) := rec.lno; --added for bug#9214366
1237
1238 --added by walton for inclusive tax on 08-Dev-07
1239 -----------------------------------------------------------------
1240 lt_tax_rate_per_rupee(row_count):=NVL(rec.tax_rate,0)/100;
1241 ln_total_tax_per_rupee:=0;
1242 lt_inclusive_tax_tab(row_count):=NVL(rec.inclusive_tax_flag,'N');
1243 lt_tax_amt_rate_tax_tab(row_count):=0;
1244 lt_tax_amt_non_rate_tab(row_count):=0;
1245 ------------------------------------------------------------------
1246
1247 /*
1248 || The following code added by aiyer for the bug 4691616
1249 || Purpose:
1250 || 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)
1251 || 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
1252 || To calculate the BASE_TAX_AMOUNT of the taxes whose tax_rate is zero
1253 */
1254
1255 IF rec.tax_rate is null THEN
1256 /*
1257 ||Indicates qty based taxes
1258 */
1259 tax_rate_zero_tab(row_count) := 0;
1260
1261 ELSIF rec.tax_rate = 0 THEN
1262 /*
1263 ||Indicates 0% tax rate becasue a tax can have a rate as 0%.
1264 */
1265 tax_rate_zero_tab(row_count) := -9999;
1266
1267 ELSE
1268 tax_rate_zero_tab(row_count) := rec.tax_rate;
1269
1270 END IF;
1271
1272 tax_type_tab(row_count) := rec.tax_type_val;
1273 qty_rate_tab(row_count) := rec.qty_rate; /*Added by mmurtuza for bug 14675629*/
1274 /*End of bug 4691616 */
1275 -- tax_amt_tab(row_count) := 0;
1276 /*added for bug#6498072, start*/
1277 IF p_action = jai_constants.recalculate_taxes AND --recalculate_taxes
1278 NVL(rec.adhoc_flag,'N') = 'Y' --adhoc_flag='Y'
1279 THEN
1280 tax_amt_tab(row_count) := nvl(rec.tax_amount,0) ;
1281 ELSE
1282 tax_amt_tab(row_count) := 0;
1283 END IF ;
1284 /*bug#6498072, end*/
1288
1285 round_factor_tab(row_count):=rec.rounding_factor; --added by csahoo for bug#6077133
1286 base_tax_amt_tab(row_count) := 0;
1287 adhoc_flag_tab(row_count):=rec.adhoc_flag ; /* rchandan bug#6030615 */
1289 IF tax_rate_tab(row_count) = 0
1290 AND rec.uom_code is not null --added by csahoo for bug#6498072
1291 THEN
1292 -- Start of bug 3749294
1293 /*
1294 Code added by aiyer for the bug 3749294
1295 Check whether an exact match exists between the transaction uom and the setup uom (obtained through the tax_category list).
1296 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
1297 determined and tax amounts,base_tax_amounts are calculated for defaultation.
1298 */
1299 Inv_Convert.inv_um_conversion( p_uom_code, rec.uom_code, p_inventory_item_id, v_conversion_rate);
1300 IF NVL(v_conversion_rate, 0) <= 0 THEN
1301 -- pramasub start FP
1302 /*4281841 ..rchandan..start*/
1303 OPEN uom_class_cur(p_uom_code, rec.uom_code);
1304 FETCH uom_class_cur INTO uom_cls;
1305 IF uom_class_cur%FOUND THEN
1306 Inv_Convert.inv_um_conversion( p_uom_code, rec.uom_code, 0, v_conversion_rate);
1307 ELSE
1308 v_conversion_rate := 0;
1309 END IF;
1310 CLOSE uom_class_cur;
1311 /*4281841 ..rchandan..end*/
1312 -- pramasub end FP
1313 --Inv_Convert.inv_um_conversion( p_uom_code, rec.uom_code, 0, v_conversion_rate); commented by pramasub
1314 IF NVL(v_conversion_rate, 0) <= 0 THEN
1315
1316 /* for cash receipt there will be no lines. sacsethi for 6012570 (5876390) */
1317 if (p_uom_code is null and p_inventory_item_id is null )
1318 and p_source_trx_type = jai_constants.ar_cash
1319 then --
1320 v_conversion_rate := 1;
1321
1322 /*
1323 Date 22-feb-2007 Added by SACSETHI for bug 6012570 (5876390)
1324 in This , Recalculation will be happen in Draft invoice
1325 */
1326 elsif (p_uom_code is null and p_inventory_item_id is null )
1327 and p_source_trx_type in (jai_constants.pa_draft_invoice
1328 ,jai_constants.G_AP_STANDALONE_INVOICE)
1329 then
1330 v_conversion_rate := 0;
1331
1332 else
1333 v_conversion_rate := 0;
1334 end if;
1335
1336 END IF;
1337 END IF;
1338 --tax_amt_tab(rec.lno) := nvl(rec.tax_amount * v_conversion_rate, 0) * p_line_quantity; -- cbabu for EnhancementBug# 2427465, compact code
1339 /*added for bug#6498072, start*/
1340 IF p_action = jai_constants.recalculate_taxes THEN
1341 /*tax_amt_tab(rec.lno) := nvl(rec.tax_amount * v_conversion_rate, 0) ;*/ --commented out by walton for inclusive tax
1342 lt_tax_amt_non_rate_tab(rec.lno):=NVL(rec.tax_amount * v_conversion_rate, 0); --added by walton for inclusive tax
1343 ELSE
1344 /*tax_amt_tab(rec.lno) := nvl(rec.tax_amount * v_conversion_rate, 0) * p_line_quantity ;*/ --commented out by walton for inclusive tax
1345 --added by walton for inclusive tax
1346 lt_tax_amt_non_rate_tab(rec.lno):=NVL(rec.tax_amount * v_conversion_rate, 0) * p_line_quantity;
1347 END IF ;
1348 /*added for bug#6498072, end*/
1349 /*commented out by walton for inclusive tax
1350 tax_amt_tab(rec.lno):=round(tax_amt_tab(rec.lno),round_factor_tab(rec.lno)); --added by csahoo for bug#6077133
1351 base_tax_amt_tab(rec.lno) := tax_amt_tab(rec.lno);
1352 */
1353 base_tax_amt_tab(rec.lno) := lt_tax_amt_non_rate_tab(rec.lno); --added by walton for inclusive tax
1354 -- End of bug 3749294
1355 END IF;
1356
1357 IF rec.valid_date IS NULL OR rec.valid_date >= SYSDATE THEN
1358 end_date_tab(row_count) := 1;
1359 ELSE
1360 tax_amt_tab(row_count) := 0;
1361 end_date_tab(row_count) := 0;
1362 END IF;
1363 row_count := row_count + 1;
1364
1365 --added for bug#9214366, start
1366 IF p_action = jai_constants.default_taxes
1367 and p_thhold_cat_base_tax_typ = jai_constants.tax_type_tcs
1368 THEN
1369 tax_amt_tab(rec.lno) := 0;
1370 END IF;
1371 --bug#9214366, end
1372
1373 END LOOP;
1374
1375 row_count := row_count - 1;
1376
1377 --added by walton for inclusive tax 08-Dev-07
1378 -------------------------------------------------
1379 IF p_vat_assessable_value<>p_tax_amount
1380 THEN
1381 ln_vat_assessable_value:=p_vat_assessable_value;
1382 ELSE
1383 ln_vat_assessable_value:=1;
1384 END IF; --End p_vat_assessable_value<>p_tax_amount
1385
1386 IF p_assessable_value<>p_tax_amount
1387 THEN
1388 ln_assessable_value:=p_assessable_value;
1389 ELSE
1390 ln_assessable_value:=1;
1391 END IF; --End p_assessable_value<>p_tax_amount
1392 ---------------------------------------------------
1393
1394 -- Added by Jia for GST Bug#10043656 2010/09/10, Begin
1395 ---------------------------------------------
1396 /*
1397 IF pn_gst_assessable_value <> p_tax_amount
1398 THEN
1399 ln_gst_assessable_value := pn_gst_assessable_value;
1400 ELSE
1401 ln_gst_assessable_value := 1;
1402 END IF; --End pn_gst_assessable_value <> p_tax_amount
1403 */
1404 ---------------------------------------------
1405 -- Added by Jia for GST Bug#10043656 2010/09/10, End
1406
1407 -- Added by Bo Li for Bug#11684111 BOE Ehancement, Begin
1408 ---------------------------------------------
1409 IF pn_customs_assessable_value <> p_tax_amount
1410 THEN
1411 ln_customs_assessable_value := pn_customs_assessable_value;
1412 ELSE
1413 ln_customs_assessable_value := 1;
1414 END IF; --End pn_gst_assessable_value <> p_tax_amount
1415 ---------------------------------------------
1416 -- Added by Bo Li for Bug#11684111 BOE Ehancement, End
1417
1418 FOR I IN 1..row_count LOOP
1419 IF end_date_tab(I) <> 0 THEN
1420 IF tax_type_tab(I) = 1 THEN
1421 --Added by walton for inclusive tax on 08-Dec-07
1422 ------------------------------------------------
1423 IF ln_assessable_value =1
1424 THEN
1425 bsln_amt:=1;
1426 ln_bsln_amt_nr :=0;
1427 ELSE
1428 bsln_amt :=0;
1429 ln_bsln_amt_nr :=ln_assessable_value;
1430 END IF;
1431 ------------------------------------------------
1432 /*bsln_amt := p_assessable_value;*/ --commented out by walton for inclusive tax
1433 ELSIF tax_type_tab(I) = 4 THEN
1434 --Added by walton for inclusive tax on 08-Dec-07
1435 ------------------------------------------------
1436 IF ln_vat_assessable_value =1
1437 THEN
1438 bsln_amt:=1;
1439 ln_bsln_amt_nr :=0;
1440 ELSE
1441 bsln_amt :=0;
1442 ln_bsln_amt_nr :=ln_vat_assessable_value;
1443 END IF;
1444 ------------------------------------------------
1445 /*bsln_amt := p_vat_assessable_value;*/ --commented out by walton for inclusive tax
1446
1447 --Added by Jia for GST Bug#10043656 on 2010/09/10, Begin
1448 ------------------------------------------------
1449 /*
1450 ELSIF tax_type_tab(I) = 7 THEN
1451 IF ln_gst_assessable_value = 1
1452 THEN
1453 bsln_amt := 1;
1454 ln_bsln_amt_nr := 0;
1455 ELSE
1456 bsln_amt := 0;
1457 ln_bsln_amt_nr := ln_gst_assessable_value;
1458 END IF;
1459 */
1460 ------------------------------------------------
1461 --Added by Jia for GST Bug#10043656 on 2010/09/10, End
1462
1463 --Added by Bo Li for Bug#11684111 BOE Ehancement, Begin
1464 ------------------------------------------------
1465 ELSIF tax_type_tab(I) = 8 THEN
1466 IF ln_customs_assessable_value = 1
1467 THEN
1468 bsln_amt := 1;
1469 ln_bsln_amt_nr := 0;
1470 ELSE
1471 bsln_amt := 0;
1472 ln_bsln_amt_nr := ln_customs_assessable_value;
1473 END IF;
1474 ------------------------------------------------
1475 --Added by Bo Li for Bug#11684111 BOE Ehancement, End
1476
1477 --Added by walton for inclusive tax
1478 -------------------------------------
1479 ELSIF tax_type_tab(I) = 6 THEN
1480 bsln_amt:=0;
1484 bsln_amt:=1; --Added by walton for inclusive tax
1481 ln_bsln_amt_nr :=0;
1482 -------------------------------------
1483 ELSE
1485 ln_bsln_amt_nr :=0; --Added by walton for inclusive tax
1486 /*bsln_amt := p_tax_amount;*/ --commented out by walton for inclusive tax
1487 END IF;
1488 IF tax_rate_tab(I) <> 0 THEN
1489 /* Commented out by Shyan for bug 10358786 on 28-Jan-2011 , Begin
1490 IF P1(I) < line_no_tab(I) AND P1(I) NOT IN (-1,0) THEN --replaced I by line_no_tab(I) for bug#9214366
1491 vamt := vamt + NVL(tax_amt_tab(P1(I)),0);
1492 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P1(I)),0); --added by walton for inclusive tax
1493 ELSIF P1(I) = 0 THEN
1494 vamt := vamt + bsln_amt;
1495 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by walton for inclusive tax
1496 END IF;
1497 IF p2(I) < line_no_tab(I) AND p2(I) NOT IN (-1,0) THEN --replaced I by line_no_tab(I) for bug#9214366
1498 vamt := vamt + NVL(tax_amt_tab(p2(I)),0);
1499 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P2(I)),0); --added by walton for inclusive tax
1500 ELSIF p2(I) = 0 THEN
1501 vamt := vamt + bsln_amt;
1502 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by walton for inclusive tax
1503 END IF;
1504 IF p3(I) < line_no_tab(I) AND p3(I) NOT IN (-1,0) THEN --replaced I by line_no_tab(I) for bug#9214366
1505 vamt := vamt + NVL(tax_amt_tab(p3(I)),0);
1506 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P3(I)),0); --added by walton for inclusive tax
1507 ELSIF p3(I) = 0 THEN
1508 vamt := vamt + bsln_amt;
1509 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by walton for inclusive tax
1510 END IF;
1511 IF p4(I) < line_no_tab(I) AND p4(I) NOT IN (-1,0) THEN --replaced I by line_no_tab(I) for bug#9214366
1512 vamt := vamt + NVL(tax_amt_tab(p4(I)),0);
1513 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P4(I)),0); --added by walton for inclusive tax
1514 ELSIF p4(I) = 0 THEN
1515 vamt := vamt + bsln_amt;
1516 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by walton for inclusive tax
1517 END IF;
1518 IF p5(I) < line_no_tab(I) AND p5(I) NOT IN (-1,0) THEN --replaced I by line_no_tab(I) for bug#9214366
1519 vamt := vamt + NVL(tax_amt_tab(p5(I)),0);
1520 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P5(I)),0); --added by walton for inclusive tax
1521 ELSIF p5(I) = 0 THEN
1522 vamt := vamt + bsln_amt;
1523 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by walton for inclusive tax
1524 END IF;
1525 -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
1526 -- start bug 5228046
1527 IF P6(I) < line_no_tab(I) AND P6(I) NOT IN (-1,0) THEN --replaced I by line_no_tab(I) for bug#9214366
1528 vamt := vamt + NVL(tax_amt_tab(P6(I)),0);
1529 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P6(I)),0); --added by walton for inclusive tax
1530 ELSIF P6(I) = 0 THEN
1531 vamt := vamt + bsln_amt;
1532 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by walton for inclusive tax
1533 END IF;
1534 IF p7(I) < line_no_tab(I) AND p7(I) NOT IN (-1,0) THEN --replaced I by line_no_tab(I) for bug#9214366
1535 vamt := vamt + NVL(tax_amt_tab(p7(I)),0);
1536 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P7(I)),0); --added by walton for inclusive tax
1537 ELSIF p7(I) = 0 THEN
1538 vamt := vamt + bsln_amt;
1539 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by walton for inclusive tax
1540 END IF;
1541 IF p8(I) < line_no_tab(I) AND p8(I) NOT IN (-1,0) THEN --replaced I by line_no_tab(I) for bug#9214366
1542 vamt := vamt + NVL(tax_amt_tab(p8(I)),0);
1543 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P8(I)),0); --added by walton for inclusive tax
1544 ELSIF p8(I) = 0 THEN
1545 vamt := vamt + bsln_amt;
1546 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by walton for inclusive tax
1547 END IF;
1548 IF p9(I) < line_no_tab(I) AND p9(I) NOT IN (-1,0) THEN --replaced I by line_no_tab(I) for bug#9214366
1549 vamt := vamt + NVL(tax_amt_tab(p9(I)),0);
1550 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P9(I)),0); --added by walton for inclusive tax
1551 ELSIF p9(I) = 0 THEN
1552 vamt := vamt + bsln_amt;
1553 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by walton for inclusive tax
1554 END IF;
1555 IF p10(I) < line_no_tab(I) AND p10(I) NOT IN (-1,0) THEN --replaced I by line_no_tab(I) for bug#9214366
1556 vamt := vamt + NVL(tax_amt_tab(p10(I)),0);
1557 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P10(I)),0); --added by walton for inclusive tax
1558 ELSIF p10(I) = 0 THEN
1559 vamt := vamt + bsln_amt;
1560 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by walton for inclusive tax
1561 END IF;
1562 -- end bug 5228046
1563 Commented out by Shyan for bug 10358786 on 28-Jan-2011, End*/
1564
1565 --Added by Shujuan for 10358786 on 28-Jan-2011, Begin
1566 -------------------------------------------------------
1567
1568 lt_tax_amt_non_rate_tab(I):=0;
1569 IF P1(I) < line_no_tab(I) AND P1(I) NOT IN (-1, 0) THEN --replaced I by line_no_tab(I) for bug#9214366
1570 IF lt_inclusive_tax_tab(P1(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P1(I)) IS NOT NULL then
1571 vamt := vamt + NVL(tax_amt_tab(P1(I)), 0);
1572 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P1(I)), 0); --added by walton for inclusive tax
1573 END IF;
1574
1575 ELSIF P1(I) = 0 THEN
1576 IF lt_inclusive_tax_tab(I) = 'Y' then
1577 vamt := vamt + bsln_amt;
1578 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
1579 ELSE
1580 if (ln_bsln_amt_nr = 0) then /*16172861*/
1581 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
1582
1583 else
1584 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; /*16172861*/
1585 end if;
1586
1587 END IF;
1588 END IF;
1589 IF p2(I) < line_no_tab(I) AND p2(I) NOT IN (-1, 0) THEN --replaced I by line_no_tab(I) for bug#9214366
1590 IF lt_inclusive_tax_tab(P2(I)) = 'Y' OR
1591 lt_tax_amt_rate_tax_tab(P2(I)) IS NOT NULL then
1592 vamt := vamt + NVL(tax_amt_tab(p2(I)), 0);
1593 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P2(I)), 0); --added by walton for inclusive tax
1594 END IF;
1595
1596 ELSIF p2(I) = 0 THEN
1597 IF lt_inclusive_tax_tab(I) = 'Y' then
1598 vamt := vamt + bsln_amt;
1599 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
1600 ELSE
1601 if (ln_bsln_amt_nr = 0) then /*16172861*/
1602 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
1603
1604 else
1605 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; /*16172861*/
1606 end if;
1607 END IF;
1608
1609 END IF;
1610 IF p3(I) < line_no_tab(I) AND p3(I) NOT IN (-1, 0) THEN --replaced I by line_no_tab(I) for bug#9214366
1611 IF lt_inclusive_tax_tab(P3(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P3(I)) IS NOT NULL then
1612 vamt := vamt + NVL(tax_amt_tab(p3(I)), 0);
1613 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P3(I)), 0); --added by walton for inclusive tax
1614 END IF;
1615
1616 ELSIF p3(I) = 0 THEN
1617 IF lt_inclusive_tax_tab(I) = 'Y' then
1618 vamt := vamt + bsln_amt;
1619 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
1620 ELSE
1621 if (ln_bsln_amt_nr = 0) then /*16172861*/
1622 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
1623
1624 else
1625 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; /*16172861*/
1626 end if;
1627 END IF;
1628 END IF;
1629
1630 IF p4(I) < line_no_tab(I) AND p4(I) NOT IN (-1, 0) THEN --replaced I by line_no_tab(I) for bug#9214366
1631 IF lt_inclusive_tax_tab(P4(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P4(I)) IS NOT NULL then
1632 vamt := vamt + NVL(tax_amt_tab(p4(I)), 0);
1633 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P4(I)), 0); --added by walton for inclusive tax
1634 END IF;
1635 ELSIF p4(I) = 0 THEN
1636 IF lt_inclusive_tax_tab(I) = 'Y' then
1637 vamt := vamt + bsln_amt;
1638 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
1639 ELSE
1640 if (ln_bsln_amt_nr = 0) then /*16172861*/
1641 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
1642
1643 else
1644 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; /*16172861*/
1645 end if;
1646 END IF;
1647 END IF;
1648
1649 IF p5(I) < line_no_tab(I) AND p5(I) NOT IN (-1, 0) THEN --replaced I by line_no_tab(I) for bug#9214366
1650 IF lt_inclusive_tax_tab(P5(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P5(I)) IS NOT NULL then
1651 vamt := vamt + NVL(tax_amt_tab(p5(I)), 0);
1652 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P5(I)), 0); --added by walton for inclusive tax
1653 END IF;
1654 ELSIF p5(I) = 0 THEN
1655 IF lt_inclusive_tax_tab(I) = 'Y' then
1656 vamt := vamt + bsln_amt;
1657 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
1658 ELSE
1659 if (ln_bsln_amt_nr = 0) then /*16172861*/
1660 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
1661
1662 else
1666 END IF;
1663 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; /*16172861*/
1664 end if;
1665 END IF;
1667 -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
1668 -- start bug 5228046
1669 IF p6(I) < line_no_tab(I) AND p6(I) NOT IN (-1, 0) THEN --replaced I by line_no_tab(I) for bug#9214366
1670 IF lt_inclusive_tax_tab(P6(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P6(I)) IS NOT NULL then
1671 vamt := vamt + NVL(tax_amt_tab(p6(I)), 0);
1672 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P6(I)), 0); --added by walton for inclusive tax
1673 END IF;
1674
1675 ELSIF p6(I) = 0 THEN
1676 IF lt_inclusive_tax_tab(I) = 'Y' then
1677 vamt := vamt + bsln_amt;
1678 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
1679 ELSE
1680 if (ln_bsln_amt_nr = 0) then /*16172861*/
1681 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
1682
1683 else
1684 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; /*16172861*/
1685 end if;
1686 END IF;
1687 END IF;
1688
1689 IF p7(I) < line_no_tab(I) AND p7(I) NOT IN (-1, 0) THEN --replaced I by line_no_tab(I) for bug#9214366
1690 IF lt_inclusive_tax_tab(P7(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P7(I)) IS NOT NULL then
1691 vamt := vamt + NVL(tax_amt_tab(p7(I)), 0);
1692 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P7(I)), 0); --added by walton for inclusive tax
1693 END IF;
1694
1695 ELSIF p7(I) = 0 THEN
1696 IF lt_inclusive_tax_tab(I) = 'Y' then
1697 vamt := vamt + bsln_amt;
1698 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
1699 ELSE
1700 if (ln_bsln_amt_nr = 0) then /*16172861*/
1701 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
1702
1703 else
1704 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; /*16172861*/
1705 end if;
1706 END IF;
1707 END IF;
1708
1709 IF p8(I) < line_no_tab(I) AND p8(I) NOT IN (-1, 0) THEN --replaced I by line_no_tab(I) for bug#9214366
1710 IF lt_inclusive_tax_tab(P4(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P8(I)) IS NOT NULL then
1711 vamt := vamt + NVL(tax_amt_tab(p8(I)), 0);
1712 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P8(I)), 0); --added by walton for inclusive tax
1713 END IF;
1714
1715 ELSIF p8(I) = 0 THEN
1716 IF lt_inclusive_tax_tab(I) = 'Y' then
1717 vamt := vamt + bsln_amt;
1718 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
1719 ELSE
1720 if (ln_bsln_amt_nr = 0) then /*16172861*/
1721 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
1722
1723 else
1724 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; /*16172861*/
1725 end if;
1726 END IF;
1727 END IF;
1728
1729 IF p9(I) < line_no_tab(I) AND p9(I) NOT IN (-1, 0) THEN --replaced I by line_no_tab(I) for bug#9214366
1730 IF lt_inclusive_tax_tab(P9(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P9(I)) IS NOT NULL then
1731 vamt := vamt + NVL(tax_amt_tab(p4(I)), 0);
1732 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P9(I)), 0); --added by walton for inclusive tax
1733 END IF;
1734
1735 ELSIF p9(I) = 0 THEN
1736 IF lt_inclusive_tax_tab(I) = 'Y' then
1737 vamt := vamt + bsln_amt;
1738 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
1739 ELSE
1740 if (ln_bsln_amt_nr = 0) then /*16172861*/
1741 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
1742
1743 else
1744 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; /*16172861*/
1745 end if;
1746 END IF;
1747 END IF;
1748
1749 IF p10(I) < line_no_tab(I) AND p10(I) NOT IN (-1, 0) THEN --replaced I by line_no_tab(I) for bug#9214366
1750 IF lt_inclusive_tax_tab(P10(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P10(I)) IS NOT NULL then
1751 vamt := vamt + NVL(tax_amt_tab(p10(I)), 0);
1752 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P10(I)), 0); --added by walton for inclusive tax
1753 END IF;
1754
1755 ELSIF p10(I) = 0 THEN
1756 IF lt_inclusive_tax_tab(I) = 'Y' then
1757 vamt := vamt + bsln_amt;
1758 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
1759 ELSE
1760 if (ln_bsln_amt_nr = 0) then /*16172861*/
1761 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
1762
1763 else
1764 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; /*16172861*/
1765 end if;
1766 END IF;
1767 END IF;
1768 -- end bug 5228046
1769 -------------------------------------------------------
1770 --Added by Shujuan for 10358786 on 28-Jan-2011, End
1771
1772 v_tax_amt := v_tax_amt + (vamt * (tax_rate_tab(I)/100));
1773 ln_tax_amt_nr:=ln_tax_amt_nr+(ln_vamt_nr*(tax_rate_tab(I)/100)); --added by walton for inclusive tax
1774 base_tax_amt_tab(I) := vamt;
1775 tax_amt_tab(I) := NVL(tax_amt_tab(I),0) + v_tax_amt;
1776 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
1777 --Commented out by Shyan for bug 10358786 on 28-Jan-2011 , Begin
1781 -- Commented out by Shyan for bug 10358786 on 28-Jan-2011 , End
1778 -------------------------------------------------------------------
1779 --lt_tax_amt_rate_tax_tab(I):= tax_amt_tab(I); --added by walton for inclusive tax
1780 -------------------------------------------------------------------
1782
1783 -- Added by Shyan for bug 10358786 on 28-Jan-2011 , Begin
1784 -------------------------------------------------------------------
1785 IF lt_inclusive_tax_tab(I) = 'Y' then
1786 lt_tax_amt_rate_tax_tab(I) := tax_amt_tab(I); --added by walton for inclusive tax
1787 END IF;
1788 -------------------------------------------------------------------
1789 -- Added by Shyan for bug 10358786 on 28-Jan-2011, End
1790 /*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
1791 base_tax_amount_nr_tab(I):=ln_vamt_nr; --added by Xiao Lv for bug#8789761 on 30-Oct-09
1792 vamt := 0;
1793 v_tax_amt := 0;
1794 ln_tax_amt_nr:=0; --added by walton for inclusive tax
1795 ln_vamt_nr:=0; --added by walton for inclusive tax
1796 END IF;
1797
1798 ELSE
1799
1800 tax_amt_tab(I) := 0;
1801 base_tax_amt_tab(I) := 0;
1802 base_tax_amount_nr_tab(I):=0;--9692478
1803
1804
1805 END IF;
1806
1807 END LOOP;
1808
1809 FOR I IN 1..row_count LOOP
1810 IF end_date_tab( I ) <> 0 THEN
1811 IF tax_rate_tab(I) <> 0 THEN
1812 IF P1(I) > line_no_tab(I) THEN --replaced I by line_no_tab(I) for bug#9214366
1813 vamt := vamt + NVL(tax_amt_tab(P1(I)),0);
1814 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P1(I)),0); --added by walton for inclusive tax
1815 END IF;
1816 IF p2(I) > line_no_tab(I) THEN --replaced I by line_no_tab(I) for bug#9214366
1817 vamt := vamt + NVL(tax_amt_tab(p2(I)),0);
1818 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P2(I)),0); --added by walton for inclusive tax
1819 END IF;
1820 IF p3(I) > line_no_tab(I) THEN --replaced I by line_no_tab(I) for bug#9214366
1821 vamt := vamt + NVL(tax_amt_tab(p3(I)),0);
1822 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P3(I)),0); --added by walton for inclusive tax
1823 END IF;
1824 IF p4(I) > line_no_tab(I) THEN --replaced I by line_no_tab(I) for bug#9214366
1825 vamt := vamt + NVL(tax_amt_tab(p4(I)),0);
1826 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P4(I)),0); --added by walton for inclusive tax
1827 END IF;
1828 IF p5(I) > line_no_tab(I) THEN --replaced I by line_no_tab(I) for bug#9214366
1829 vamt := vamt + NVL(tax_amt_tab(p5(I)),0);
1830 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P5(I)),0); --added by walton for inclusive tax
1831 END IF;
1832 -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
1833 -- start bug 5228046
1834 IF P6(I) > line_no_tab(I) THEN --replaced I by line_no_tab(I) for bug#9214366
1835 vamt := vamt + NVL(tax_amt_tab(P6(I)),0);
1836 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P6(I)),0); --added by walton for inclusive tax
1837 END IF;
1838 IF p7(I) > line_no_tab(I) THEN --replaced I by line_no_tab(I) for bug#9214366
1839 vamt := vamt + NVL(tax_amt_tab(p7(I)),0);
1840 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P7(I)),0); --added by walton for inclusive tax
1841 END IF;
1842 IF p8(I) > line_no_tab(I) THEN --replaced I by line_no_tab(I) for bug#9214366
1843 vamt := vamt + NVL(tax_amt_tab(p8(I)),0);
1844 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P8(I)),0); --added by walton for inclusive tax
1845 END IF;
1846 IF p9(I) > line_no_tab(I) THEN --replaced I by line_no_tab(I) for bug#9214366
1847 vamt := vamt + NVL(tax_amt_tab(p9(I)),0);
1848 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P9(I)),0); --added by walton for inclusive tax
1849 END IF;
1850 IF p10(I) > line_no_tab(I) THEN --replaced I by line_no_tab(I) for bug#9214366
1851 vamt := vamt + NVL(tax_amt_tab(p10(I)),0);
1852 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P10(I)),0); --added by walton for inclusive tax
1853 END IF;
1854
1855 -- end bug 5228046
1856 base_tax_amt_tab(I) := vamt;
1857 base_tax_amount_nr_tab(I):=ln_vamt_nr; /*9692478*/
1858
1859 v_tax_amt := v_tax_amt + (vamt * (tax_rate_tab(I)/100));
1860 ln_tax_amt_nr:=ln_tax_amt_nr+(ln_vamt_nr * (tax_rate_tab(I)/100)); --added by walton for inclusive tax
1861 IF vamt <> 0 THEN
1862 base_tax_amt_tab(I) := base_tax_amt_tab(I) + vamt;
1863 END IF;
1864 tax_amt_tab(I) := NVL(tax_amt_tab(I),0) + v_tax_amt;
1865 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
1866 lt_tax_amt_rate_tax_tab(I):= tax_amt_tab(I); --added by walton for inclusive tax
1867 base_tax_amount_nr_tab(I):=ln_vamt_nr; --added by Xiao Lv for bug#8789761 on 30-Oct-09
1868 vamt := 0;
1869 v_tax_amt := 0;
1870 ln_vamt_nr :=0; --added by walton for inclusive tax
1871 ln_tax_amt_nr :=0; --added by walton for inclusive tax
1872 END IF;
1873
1874 ELSE
1875
1876 base_tax_amt_tab(I) := vamt;
1877 base_tax_amount_nr_tab(I):=ln_vamt_nr; /*9692478*/
1878
1879 tax_amt_tab(I) := 0;
1880 END IF;
1881
1882 END LOOP;
1883
1884
1885 FOR counter IN 1 .. max_iter LOOP
1886 vamt := 0;
1887 v_tax_amt := 0;
1888 ln_vamt_nr:= 0; --added by walton for inclusive tax
1889 ln_tax_amt_nr:=0; --added by walton for inclusive tax
1890
1891 FOR i IN 1 .. row_count LOOP
1892
1893 /*
1894 || Modified by aiyer for the fwd porting bug 4691616.
1895 || The following if clause will restrict the taxes whose tax_rate is null
1896 || i.e when tax_rate is null, tax_rate_tab(i) is 0.
1897 */
1898
1899 IF ( tax_rate_tab( i ) <> 0 OR
1900 tax_rate_zero_tab(I) = -9999
1901 ) AND
1902 end_date_tab( I ) <> 0
1903 THEN
1904
1905 IF tax_type_tab( I ) = 1 THEN
1906 --Added by walton for inclusive tax on 08-Dec-07
1907 ------------------------------------------------
1908 IF ln_assessable_value =1
1909 THEN
1910 v_amt:=1;
1911 ln_bsln_amt_nr :=0;
1912 ELSE
1913 v_amt :=0;
1914 ln_bsln_amt_nr :=ln_assessable_value;
1915 END IF;
1916 ------------------------------------------------
1917 /*v_amt := p_assessable_value;*/ --commented out by walton for inclusive tax
1918 ELSIF tax_type_tab(I) = 4 THEN
1919 --Added by walton for inclusive tax on 08-Dec-07
1920 ------------------------------------------------
1921 IF ln_vat_assessable_value =1
1922 THEN
1923 v_amt:=1;
1924 ln_bsln_amt_nr :=0;
1925 ELSE
1926 v_amt :=0;
1927 ln_bsln_amt_nr :=ln_vat_assessable_value;
1928 END IF;
1929 ------------------------------------------------
1930 /*v_amt := p_vat_assessable_value;*/ --commented out by walton for inclusive tax
1931
1932 --Added by Jia for GST Bug#10043656 on 2010/09/10, Begin
1933 ------------------------------------------------
1934 /*
1935 ELSIF tax_type_tab(I) = 7 THEN
1936 IF ln_gst_assessable_value = 1
1937 THEN
1938 v_amt := 1;
1939 ln_bsln_amt_nr := 0;
1940 ELSE
1941 v_amt := 0;
1942 ln_bsln_amt_nr := ln_gst_assessable_value;
1943 END IF;
1944 */
1945 ------------------------------------------------
1946 --Added by Jia for GST Bug#10043656 on 2010/09/10, End
1947
1948 --Added by Bo Li for Bug#11684111 BOE Ehancement , Begin
1949 ------------------------------------------------
1950 ELSIF tax_type_tab(I) = 8 THEN
1951 IF ln_customs_assessable_value = 1
1952 THEN
1953 v_amt := 1;
1954 ln_bsln_amt_nr := 0;
1955 ELSE
1956 v_amt := 0;
1957 ln_bsln_amt_nr := ln_customs_assessable_value;
1958 END IF;
1959 ------------------------------------------------
1960 --Added by Bo Li for Bug#11684111 BOE Ehancement , End
1961
1962 --Added by walton for inclusive tax
1963 -------------------------------------
1964 ELSIF tax_type_tab(I) = 6 THEN
1965 v_amt:=0;
1966 ln_bsln_amt_nr :=0;
1967 -------------------------------------
1968 ELSE
1969 IF p_assessable_value IN ( 0, -1 ) OR tax_type_tab( I ) <> 1 THEN
1970 /* v_amt := p_tax_amount;*/
1971 v_amt:=1; --Added by walton for inclusive tax
1972 ln_bsln_amt_nr :=0; --Added by walton for inclusive tax
1973 ELSIF p_vat_assessable_value IN ( 0, -1 ) OR tax_type_tab( I ) <> 4 THEN
1974 /* v_amt := p_tax_amount;*/
1975 v_amt:=1; --Added by walton for inclusive tax
1976 ln_bsln_amt_nr :=0; --Added by walton for inclusive tax
1977 --Added by Jia for GST Bug#10043656 on 2010/09/10, Begin
1978 ------------------------------------------------
1979 /*
1980 ELSIF pn_gst_assessable_value IN ( 0, -1 ) OR tax_type_tab( I ) <> 7 THEN
1981 v_amt := 1;
1982 ln_bsln_amt_nr := 0;
1983 */
1984 ------------------------------------------------
1985 --Added by Jia for GST Bug#10043656 on 2010/09/10, End
1986 --Added by Bo Li for Bug#11684111 BOE Ehancement , Begin
1987 ------------------------------------------------
1988 ELSIF pn_customs_assessable_value IN ( 0, -1 ) OR tax_type_tab( I ) <> 8 THEN
1989 v_amt := 1;
1990 ln_bsln_amt_nr := 0;
1991 ------------------------------------------------
1992 --Added by Bo Li for Bug#11684111 BOE Ehancement, End
1993 END IF;
1994 END IF;
1995 /* Commented out by Shyan for bug 10358786 on 28-Jan-2011 , Begin
1996 --------------------------------------------------------------------
1997 IF P1( i ) <> -1 THEN
1998 IF P1( i ) <> 0 THEN
1999 vamt := vamt + tax_amt_tab( P1( I ) );
2000 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P1(I)),0); --added by walton for inclusive tax
2001 ELSIF P1(i) = 0 THEN
2002 vamt := vamt + v_amt;
2003 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by walton for inclusive tax
2004 END IF;
2005 END IF;
2006
2007 IF p2( i ) <> -1 THEN
2008 IF p2( i ) <> 0 THEN
2009 vamt := vamt + tax_amt_tab( p2( I ) );
2010 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P2(I)),0); --added by walton for inclusive tax
2011 ELSIF p2(i) = 0 THEN
2012 vamt := vamt + v_amt;
2013 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by walton for inclusive tax
2014 END IF;
2015 END IF;
2016 IF p3( i ) <> -1 THEN
2017 IF p3( i ) <> 0 THEN
2018 vamt := vamt + tax_amt_tab( p3( I ) );
2019 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P3(I)),0); --added by walton for inclusive tax
2020 ELSIF p3(i) = 0 THEN
2021 vamt := vamt + v_amt;
2022 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by walton for inclusive tax
2023 END IF;
2024 END IF;
2025
2026 IF p4( i ) <> -1 THEN
2027 IF p4( i ) <> 0 THEN
2028 vamt := vamt + tax_amt_tab( p4( i ) );
2029 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P4(I)),0); --added by walton for inclusive tax
2030 ELSIF p4(i) = 0 THEN
2031 vamt := vamt + v_amt;
2032 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by walton for inclusive tax
2033 END IF;
2034 END IF;
2035
2036 IF p5( i ) <> -1 THEN
2037 IF p5( i ) <> 0 THEN
2038 vamt := vamt + tax_amt_tab( p5( i ) );
2039 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P5(I)),0); --added by walton for inclusive tax
2040 ELSIF p5(i) = 0 THEN
2041 vamt := vamt + v_amt;
2042 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by walton for inclusive tax
2043 END IF;
2044 END IF;
2045
2046 -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
2047 -- start bug 5228046
2048 IF P6( i ) <> -1 THEN
2049 IF P6( i ) <> 0 THEN
2050 vamt := vamt + tax_amt_tab( P6( I ) );
2051 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P6(I)),0); --added by walton for inclusive tax
2052 ELSIF P6(i) = 0 THEN
2053 vamt := vamt + v_amt;
2054 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by walton for inclusive tax
2055 END IF;
2056 END IF;
2057
2058 IF p7( i ) <> -1 THEN
2059 IF p7( i ) <> 0 THEN
2060 vamt := vamt + tax_amt_tab( p7( I ) );
2061 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P7(I)),0); --added by walton for inclusive tax
2062 ELSIF p7(i) = 0 THEN
2063 vamt := vamt + v_amt;
2064 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by walton for inclusive tax
2065 END IF;
2066 END IF;
2067 IF p8( i ) <> -1 THEN
2068 IF p8( i ) <> 0 THEN
2069 vamt := vamt + tax_amt_tab( p8( I ) );
2070 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P8(I)),0); --added by walton for inclusive tax
2071 ELSIF p8(i) = 0 THEN
2072 vamt := vamt + v_amt;
2073 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by walton for inclusive tax
2077 IF p9( i ) <> -1 THEN
2074 END IF;
2075 END IF;
2076
2078 IF p9( i ) <> 0 THEN
2079 vamt := vamt + tax_amt_tab( p9( i ) );
2080 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P9(I)),0); --added by walton for inclusive tax
2081 ELSIF p9(i) = 0 THEN
2082 vamt := vamt + v_amt;
2083 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by walton for inclusive tax
2084 END IF;
2085 END IF;
2086
2087 IF p10( i ) <> -1 THEN
2088 IF p10( i ) <> 0 THEN
2089 vamt := vamt + tax_amt_tab( p10( i ) );
2090 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P10(I)),0); --added by walton for inclusive tax
2091 ELSIF p10(i) = 0 THEN
2092 vamt := vamt + v_amt;
2093 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by walton for inclusive tax
2094 END IF;
2095 END IF;
2096 -- end bug 5228046
2097 ----------------------------------------------------------------
2098 Commented out by Shyan for bug 10358786 on 28-Jan-2011, End */
2099
2100 -- Added by shujuan for bug 10358786 on 28-Jan-2011, Begin
2101 -----------------------------------------------------------
2102 ln_vamt_nr :=0;
2103 lt_tax_amt_non_rate_tab(I):=0;
2104 IF P1(i) <> -1 THEN
2105 IF P1(i) <> 0 THEN
2106 IF lt_inclusive_tax_tab(P1(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P1(I)) IS NOT NULL then
2107 vamt:= vamt + tax_amt_tab(P1(I));
2108 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P1(I)), 0); --added by walton for inclusive tax
2109 END IF;
2110 ELSIF P1(i) = 0 THEN
2111 IF lt_inclusive_tax_tab(I) = 'Y' then
2112 vamt := vamt + v_amt;
2113 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr;
2114 ELSE
2115 if (ln_bsln_amt_nr = 0) then /*16172861*/
2116 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
2117 else
2118 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; /*16172861*/
2119 end if;
2120 END IF;
2121 END IF;
2122 END IF;
2123
2124 IF p2(i) <> -1 THEN
2125 IF p2(i) <> 0 THEN
2126 IF lt_inclusive_tax_tab(P2(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P2(I)) IS NOT NULL then
2127 vamt := vamt + tax_amt_tab(p2(I));
2128 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P2(I)), 0); --added by walton for inclusive tax
2129 END IF;
2130 ELSIF p2(i) = 0 THEN
2131 IF lt_inclusive_tax_tab(I) = 'Y' then
2132 vamt := vamt + v_amt;
2133 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
2134 ELSE
2135 if (ln_bsln_amt_nr = 0) then /*16172861*/
2136 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
2137 else
2138 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; /*16172861*/
2139 end if;
2140 END IF;
2141 END IF;
2142 END IF;
2143
2144 IF p3(i) <> -1 THEN
2145 IF p3(i) <> 0 THEN
2146 IF lt_inclusive_tax_tab(P3(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P3(I)) IS NOT NULL then
2147 vamt := vamt + tax_amt_tab(p3(I));
2148 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P3(I)), 0); --added by walton for inclusive tax
2149 END IF;
2150 ELSIF p3(i) = 0 THEN
2151 IF lt_inclusive_tax_tab(I) = 'Y' then
2152 vamt := vamt + v_amt;
2153 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
2154
2155 ELSE
2156 if (ln_bsln_amt_nr = 0) then /*16172861*/
2157 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
2158 else
2159 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; /*16172861*/
2160 end if;
2161 END IF;
2162 END IF;
2163 END IF;
2164
2165 IF p4(i) <> -1 THEN
2166 IF p4(i) <> 0 THEN
2167 IF lt_inclusive_tax_tab(P4(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P4(I)) IS NOT NULL then
2168 vamt := vamt + tax_amt_tab(p4(I));
2169 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P4(I)), 0); --added by walton for inclusive tax
2170 END IF;
2171 ELSIF p4(i) = 0 THEN
2172 IF lt_inclusive_tax_tab(I) = 'Y' then
2173 vamt := vamt + v_amt;
2174 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
2175 ELSE
2176 if (ln_bsln_amt_nr = 0) then /*16172861*/
2177 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
2178 else
2179 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; /*16172861*/
2180 end if;
2181 END IF;
2182 END IF;
2183 END IF;
2184
2185 IF p5(i) <> -1 THEN
2186 IF p5(i) <> 0 THEN
2187 IF lt_inclusive_tax_tab(P5(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P5(I)) IS NOT NULL then
2188 vamt := vamt + tax_amt_tab(p5(I));
2189 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P5(I)), 0); --added by walton for inclusive tax
2190 END IF;
2191 ELSIF p5(i) = 0 THEN
2192 IF lt_inclusive_tax_tab(I) = 'Y' then
2193 vamt := vamt + v_amt;
2194 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
2195 ELSE
2199 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; /*16172861*/
2196 if (ln_bsln_amt_nr = 0) then /*16172861*/
2197 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
2198 else
2200 end if;
2201 END IF;
2202 END IF;
2203 END IF;
2204
2205 -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
2206 -- start bug 5228046
2207 IF p6(i) <> -1 THEN
2208 IF p6(i) <> 0 THEN
2209 IF lt_inclusive_tax_tab(P6(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P6(I)) IS NOT NULL then
2210 vamt := vamt + tax_amt_tab(p6(I));
2211 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P6(I)), 0); --added by walton for inclusive tax
2212 END IF;
2213 ELSIF p6(i) = 0 THEN
2214 IF lt_inclusive_tax_tab(I) = 'Y' then
2215 vamt := vamt + v_amt;
2216 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
2217 ELSE
2218 if (ln_bsln_amt_nr = 0) then /*16172861*/
2219 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
2220 else
2221 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; /*16172861*/
2222 end if;
2223 END IF;
2224 END IF;
2225 END IF;
2226 IF p7(i) <> -1 THEN
2227 IF p7(i) <> 0 THEN
2228 IF lt_inclusive_tax_tab(P7(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P7(I)) IS NOT NULL then
2229 vamt := vamt + tax_amt_tab(p7(I));
2230 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P7(I)), 0); --added by walton for inclusive tax
2231 END IF;
2232 ELSIF p7(i) = 0 THEN
2233 IF lt_inclusive_tax_tab(I) = 'Y' then
2234 vamt := vamt + v_amt;
2235 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
2236 ELSE
2237 if (ln_bsln_amt_nr = 0) then /*16172861*/
2238 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
2239 else
2240 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; /*16172861*/
2241 end if;
2242 END IF;
2243 END IF;
2244 END IF;
2245
2246 IF p8(i) <> -1 THEN
2247 IF p8(i) <> 0 THEN
2248 IF lt_inclusive_tax_tab(P8(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P8(I)) IS NOT NULL then
2249 vamt := vamt + tax_amt_tab(p8(I));
2250 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P8(I)), 0); --added by walton for inclusive tax
2251 END IF;
2252 ELSIF p8(i) = 0 THEN
2253 IF lt_inclusive_tax_tab(I) = 'Y' then
2254 vamt := vamt + v_amt;
2255 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
2256 ELSE
2257 if (ln_bsln_amt_nr = 0) then /*16172861*/
2258 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
2259 else
2260 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; /*16172861*/
2261 end if;
2262 END IF;
2263 END IF;
2264 END IF;
2265
2266 IF p9(i) <> -1 THEN
2267 IF p9(i) <> 0 THEN
2268 IF lt_inclusive_tax_tab(P9(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P9(I)) IS NOT NULL then
2269 vamt := vamt + tax_amt_tab(p9(I));
2270 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P9(I)), 0); --added by walton for inclusive tax
2271 END IF;
2272 ELSIF p9(i) = 0 THEN
2273 IF lt_inclusive_tax_tab(I) = 'Y' then
2274 vamt := vamt + v_amt;
2275 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
2276 ELSE
2277 if (ln_bsln_amt_nr = 0) then /*16172861*/
2278 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
2279 else
2280 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; /*16172861*/
2281 end if;
2282 END IF;
2283 END IF;
2284 END IF;
2285
2286 IF p10(i) <> -1 THEN
2287 IF p10(i) <> 0 THEN
2288 IF lt_inclusive_tax_tab(P10(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P10(I)) IS NOT NULL then
2289 vamt := vamt + tax_amt_tab(p10(I));
2290 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P10(I)), 0); --added by walton for inclusive tax
2291 END IF;
2292 ELSIF p10(i) = 0 THEN
2293 IF lt_inclusive_tax_tab(I) = 'Y' then
2294 vamt := vamt + v_amt;
2295 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
2296 ELSE
2297 if (ln_bsln_amt_nr = 0) then /*16172861*/
2298 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
2299 else
2300 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; /*16172861*/
2301 end if;
2302 END IF;
2303 END IF;
2304 END IF;
2305 -- end bug 5228046
2306 -----------------------------------------------------------
2307 -- Added by shujuan for bug 10358786 on 28-Jan-2011, End
2308
2309 base_tax_amt_tab(I) := vamt;
2310 tax_target_tab(I) := vamt;
2311 base_tax_amount_nr_tab(I):=ln_vamt_nr;/*9692478*/
2312
2313
2314 --------------------------------------------------------------------------------------
2315 /*Change History: jai_cmn_tax_defaultation_pkg
2316 Last Modified By Jagdish Bhosle. 2001/04/05
2320 IF (v_register_code='BOND_REG') THEN --- Added By Jagdish 2001/04/05
2317 The follow check will ensure that for Bond reg. Txns
2318 excise duty will not be added to original Line amount. */
2319 --------------------------------------------------------------------------------------
2321 IF counter = max_iter AND tax_type_tab( I ) NOT IN ( 1, 2 ) THEN
2322 v_func_tax_amt := v_tax_amt + ( vamt * ( tax_rate_tab( i )/100));
2323 v_tax_amt := v_tax_amt + ( vamt * ( tax_rate_tab( i )/100));
2324 ln_tax_amt_nr:=ln_tax_amt_nr+(ln_vamt_nr*(tax_rate_tab(i)/100)); --added by walton for inclusive
2325 END IF;
2326
2327 ELSE
2328 v_func_tax_amt := v_tax_amt + ( vamt * ( tax_rate_tab( i )/100));
2329 v_tax_amt := v_tax_amt + ( vamt * ( tax_rate_tab( i )/100));
2330 ln_tax_amt_nr:=ln_tax_amt_nr+(ln_vamt_nr*(tax_rate_tab(i)/100)); --added by walton for inclusive
2331 END IF; -- End of Addition Jagdish 2001/04/05
2332
2333 ELSIF tax_rate_tab(I) = 0 THEN
2334 base_tax_amt_tab(I) := tax_amt_tab(i);
2335 v_tax_amt := tax_amt_tab( i );
2336 ln_tax_amt_nr:=lt_tax_amt_non_rate_tab(i);
2337 tax_target_tab(I) := v_tax_amt;
2338 ELSIF end_date_tab( I ) = 0 THEN
2339 tax_amt_tab(I) := 0;
2340 base_tax_amt_tab(I) := 0;
2341 base_tax_amount_nr_tab(I):=0; /*9692478*/
2342
2343 tax_target_tab(I) := 0;
2344 END IF;
2345
2346 tax_amt_tab( I ) := NVL( v_tax_amt, 0 );
2347
2348
2349 -- Commented out by Shyan for bug 10358786 on 28-Jan-2011 , Begin
2350 -------------------------------------------------------------------
2351 --lt_tax_amt_rate_tax_tab(I) := tax_amt_tab(I); --added by walton for inclusive tax
2352 -------------------------------------------------------------------
2353 -- Commented out by Shyan for bug 10358786 on 28-Jan-2011 , End
2354
2355 -- Added by Shyan for bug 10358786 on 28-Jan-2011 , Begin
2356 -------------------------------------------------------------------
2357 IF lt_inclusive_tax_tab(I) = 'Y' THEN
2358 lt_tax_amt_rate_tax_tab(I) := tax_amt_tab(I); --added by walton for inclusive tax
2359 END IF;
2360 -------------------------------------------------------------------
2361 -- Added by Shyan for bug 10358786 on 28-Jan-2011 , End
2362 lt_tax_amt_non_rate_tab(I):=ln_tax_amt_nr; --added by walton for inclusive tax
2363 /*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
2364 --func_tax_amt_tab(I) := NVL(v_func_tax_amt,0); --Commented by walton for bug#9288016
2365 base_tax_amount_nr_tab(I):=ln_vamt_nr; --added by Xiao Lv for bug#8789761 on 30-Oct-09
2366
2367 IF counter = max_iter THEN
2368 IF end_date_tab(I) = 0 THEN
2369 tax_amt_tab( i ) := 0;
2370 func_tax_amt_tab(i) := 0;
2371 END IF;
2372 END IF;
2373
2374 vamt := 0;
2375 v_amt := 0;
2376 v_tax_amt := 0;
2377 v_func_tax_amt := 0;
2378 ln_vamt_nr :=0; --added by walton for inclusive tax
2379 ln_tax_amt_nr:=0; --added by walton for inclusive tax
2380
2381 END LOOP;
2382
2383 END LOOP;
2384
2385 --Added by walton for inclusive tax
2386 ---------------------------------------------------------------------------------------
2387 FOR I IN 1 .. ROW_COUNT --Compute Factor
2388 LOOP
2389 IF lt_inclusive_tax_tab(I) = 'Y'
2390 THEN
2391 ln_total_tax_per_rupee := ln_total_tax_per_rupee + nvl(lt_tax_amt_rate_tax_tab(I),0) ;
2392 ln_total_non_rate_tax := ln_total_non_rate_tax + nvl(lt_tax_amt_non_rate_tab(I),0);
2393 END IF;
2394 END LOOP; --End Compute Factor
2395
2396 ln_total_tax_per_rupee := ln_total_tax_per_rupee + 1;
2397
2398 IF ln_total_tax_per_rupee <> 0
2399 THEN
2400 ln_exclusive_price := (NVL(p_tax_amount,0) - ln_total_non_rate_tax ) / ln_total_tax_per_rupee;
2401 END If;
2402 FOR i in 1 .. row_count --Compute Tax Amount
2403 Loop
2404 -- Commented out by Shyan for bug 10358786 on 28-Jan-2011 , Begin
2405 ------------------------------------------------------------------
2406 --tax_amt_tab (i) := (lt_tax_amt_rate_tax_tab(I) * ln_exclusive_price ) + lt_tax_amt_non_rate_tab(I);
2407 ------------------------------------------------------------------
2408 -- Commented out by Shyan for bug 10358786 on 28-Jan-2011 , End
2409
2410 -- Added by Shyan for bug 10358786 on 28-Jan-2011 , Begin
2411 ------------------------------------------------------------------
2412 tax_amt_tab (i):=0;
2413 IF lt_inclusive_tax_tab(I) = 'Y' THEN
2414 IF (tax_type_tab(I) = 1 and ln_assessable_value>1) OR
2415 (tax_type_tab(I) = 4 and ln_vat_assessable_value> 1) OR
2416 /*(tax_type_tab(I) = 7 and ln_gst_assessable_value >1)
2417 OR*/ (tax_type_tab(I) = 8 and ln_customs_assessable_value >1) --Added by Bo Li for bug#11684111 BOE Enhancement
2418 THEN
2419
2420 IF tax_type_tab(I) = 1 and ln_assessable_value >1 Then
2421 ln_amount:= ln_assessable_value;
2422 ELSIF tax_type_tab(I) = 4 and ln_vat_assessable_value >1 Then
2423 ln_amount:= ln_vat_assessable_value;
2424 /*ELSIF tax_type_tab(I) = 7 and ln_gst_assessable_value >1 Then
2425 ln_amount:= ln_gst_assessable_value;*/
2426 ELSIF tax_type_tab(I) = 8 and ln_customs_assessable_value >1 Then --Added by Bo Li for bug#11684111 BOE Enhancement
2430 IF P1(I) < line_no_tab(I) AND p1(I) NOT IN (-1, 0) then
2427 ln_amount:= ln_customs_assessable_value;--Added by Bo Li for bug#11684111 BOE Enhancement
2428 END IF;
2429
2431 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P1(i))* tax_rate_tab(i)/100;
2432 ELSIF P1(I)=0 Then
2433 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
2434 END IF;
2435
2436 IF P2(I) < line_no_tab(I) AND p2(I) NOT IN (-1, 0) then
2437 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P2(i))* tax_rate_tab(i)/100;
2438 ELSIF P2(I)=0 Then
2439 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
2440 END IF;
2441
2442 IF P3(I) < line_no_tab(I) AND p3(I) NOT IN (-1, 0) then
2443 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P3(i))* tax_rate_tab(i)/100;
2444 ELSIF P3(I)=0 Then
2445 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
2446 END IF;
2447
2448 IF P4(I) < line_no_tab(I) AND p4(I) NOT IN (-1, 0) then
2449 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P4(i))* tax_rate_tab(i)/100;
2450 ELSIF P4(I)=0 Then
2451 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
2452 END IF;
2453
2454 IF P5(I) < line_no_tab(I) AND p5(I) NOT IN (-1, 0) then
2455 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P5(i))* tax_rate_tab(i)/100;
2456 ELSIF P5(I)=0 Then
2457 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
2458 END IF;
2459
2460 IF P6(I) < line_no_tab(I) AND p6(I) NOT IN (-1, 0) then
2461 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P6(i))* tax_rate_tab(i)/100;
2462 ELSIF P6(I)=0 Then
2463 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
2464 END IF;
2465
2466 IF P7(I) < line_no_tab(I) AND p7(I) NOT IN (-1, 0) then
2467 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P7(i))* tax_rate_tab(i)/100;
2468 ELSIF P7(I)=0 Then
2469 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
2470 END IF;
2471
2472 IF P8(I) < line_no_tab(I) AND p8(I) NOT IN (-1, 0) then
2473 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P8(i))* tax_rate_tab(i)/100;
2474 ELSIF P8(I)=0 Then
2475 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
2476 END IF;
2477
2478 IF P9(I) < line_no_tab(I) AND p9(I) NOT IN (-1, 0) then
2479 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P9(i))* tax_rate_tab(i)/100;
2480 ELSIF P9(I)=0 Then
2481 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
2482 END IF;
2483
2484 IF P10(I) < line_no_tab(I) AND p10(I) NOT IN (-1, 0) then
2485 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P10(i))* tax_rate_tab(i)/100;
2486 ELSIF P10(I)=0 Then
2487 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
2488 END IF;
2489 ELSE
2490 tax_amt_tab (i) := (lt_tax_amt_rate_tax_tab(I) * ln_exclusive_price ) + lt_tax_amt_non_rate_tab(I);
2491 END IF;
2492 ELSE
2493 IF (tax_type_tab(I) = 1 and ln_assessable_value >1) OR
2494 (tax_type_tab(I) = 4 and ln_vat_assessable_value > 1) OR
2495 /*(tax_type_tab(I) = 7 and ln_gst_assessable_value >1)
2496 OR*/ (tax_type_tab(I) = 8 and ln_customs_assessable_value >1) --Added by Bo Li for bug#11684111 BOE Enhancement
2497 THEN
2498
2499 IF tax_type_tab(I) = 1 and ln_assessable_value >1 Then
2500 ln_amount:= ln_assessable_value;
2501 ELSIF tax_type_tab(I) = 4 and ln_vat_assessable_value >1 Then
2502 ln_amount:= ln_vat_assessable_value;
2503 /*ELSIF tax_type_tab(I) = 7 and ln_gst_assessable_value >1 Then
2504 ln_amount:= ln_gst_assessable_value;*/
2505 ELSIF tax_type_tab(I) = 8 and ln_customs_assessable_value >1 Then--Added by Bo Li for bug#11684111 BOE Enhancement
2506 ln_amount:= ln_customs_assessable_value;--Added by Bo Li for bug#11684111 BOE Enhancement
2507 END IF;
2508 ELSE ln_amount:= p_tax_amount;
2509 END IF;
2510 IF P1(I) < line_no_tab(I) AND p1(I) NOT IN (-1, 0) then
2511 if (NVL(lt_inclusive_tax_tab(P1(i)), 'N') = 'N') then --added by mmurtuza for bug 14591926
2512 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P1(i))* tax_rate_tab(i)/100;
2513 end if;
2514 ELSIF P1(I)=0 Then
2515 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
2516 END IF;
2517
2518 IF P2(I) < line_no_tab(I) AND p2(I) NOT IN (-1, 0) then
2519 if (NVL(lt_inclusive_tax_tab(P2(i)), 'N') = 'N') then --added by mmurtuza for bug 14591926
2520 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P2(i))* tax_rate_tab(i)/100;
2521 end if;
2522 ELSIF P2(I)=0 Then
2523 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
2524 END IF;
2525
2526 IF P3(I) < line_no_tab(I) AND p3(I) NOT IN (-1, 0) then
2527 if (NVL(lt_inclusive_tax_tab(P3(i)), 'N') = 'N') then --added by mmurtuza for bug 14591926
2528 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P3(i))* tax_rate_tab(i)/100;
2529 end if;
2530 ELSIF P3(I)=0 Then
2531 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
2532 END IF;
2533
2534 IF P4(I) < line_no_tab(I) AND p4(I) NOT IN (-1, 0) then
2538 ELSIF P4(I)=0 Then
2535 if (NVL(lt_inclusive_tax_tab(P4(i)), 'N') = 'N') then --added by mmurtuza for bug 14591926
2536 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P4(i))* tax_rate_tab(i)/100;
2537 end if;
2539 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
2540 END IF;
2541
2542 IF P5(I) < line_no_tab(I) AND p5(I) NOT IN (-1, 0) then
2543 if (NVL(lt_inclusive_tax_tab(P5(i)), 'N') = 'N') then --added by mmurtuza for bug 14591926
2544 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P5(i))* tax_rate_tab(i)/100;
2545 end if;
2546 ELSIF P5(I)=0 Then
2547 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
2548 END IF;
2549
2550 IF P6(I) < line_no_tab(I) AND p6(I) NOT IN (-1, 0) then
2551 if (NVL(lt_inclusive_tax_tab(P6(i)), 'N') = 'N') then --added by mmurtuza for bug 14591926
2552 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P6(i))* tax_rate_tab(i)/100;
2553 end if;
2554 ELSIF P6(I)=0 Then
2555 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
2556 END IF;
2557
2558 IF P7(I) < line_no_tab(I) AND p7(I) NOT IN (-1, 0) then
2559 if (NVL(lt_inclusive_tax_tab(P7(i)), 'N') = 'N') then --added by mmurtuza for bug 14591926
2560 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P7(i))* tax_rate_tab(i)/100;
2561 end if;
2562 ELSIF P7(I)=0 Then
2563 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
2564 END IF;
2565
2566 IF P8(I) < line_no_tab(I) AND p8(I) NOT IN (-1, 0) then
2567 if (NVL(lt_inclusive_tax_tab(P8(i)), 'N') = 'N') then --added by mmurtuza for bug 14591926
2568 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P8(i))* tax_rate_tab(i)/100;
2569 end if;
2570 ELSIF P8(I)=0 Then
2571 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
2572 END IF;
2573
2574 IF P9(I) < line_no_tab(I) AND p9(I) NOT IN (-1, 0) then
2575 if (NVL(lt_inclusive_tax_tab(P9(i)), 'N') = 'N') then --added by mmurtuza for bug 14591926
2576 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P9(i))* tax_rate_tab(i)/100;
2577 end if;
2578 ELSIF P9(I)=0 Then
2579 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
2580 END IF;
2581
2582 IF P10(I) < line_no_tab(I) AND p10(I) NOT IN (-1, 0) then
2583 if (NVL(lt_inclusive_tax_tab(P10(i)), 'N') = 'N') then --added by mmurtuza for bug 14591926
2584 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P10(i))* tax_rate_tab(i)/100;
2585 end if;
2586 ELSIF P10(I)=0 Then
2587 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
2588 END IF;
2589 END IF;
2590 ------------------------------------------------------------------
2591 -- Added by Shyan for bug 10358786 on 28-Jan-2011 , End
2592 func_tax_amt_tab(i):=tax_amt_tab (i); --Added by walton for bug#9288016
2593 tax_amt_tab(I) := round(tax_amt_tab(I) ,round_factor_tab(I));
2594 base_tax_amt_tab(I):= ln_exclusive_price * base_tax_amt_tab(I)
2595 + base_tax_amount_nr_tab(I); --added by Xiao Lv for bug#8789761 on 30-Oct-09
2596 END LOOP; --End Compute Tax Amount
2597 --------------------------------------------------------------------------------------------------------
2598
2599 --Added by Kevin Cheng for Retroactive Price 2008/01/13
2600 --===========================================================================================================
2601 ELSIF pv_retroprice_changed = 'Y'
2602 THEN
2603
2604 --Ramananda for File.Sql.35
2605 bsln_amt := p_tax_amount ;
2606 v_conversion_rate := 0;
2607 v_currency_conv_factor := p_currency_conv_factor;
2608 ln_base := 0 ;
2609
2610 IF transaction_name <> 'CRM_QUOTE' THEN -- Vijay Shankar for Bug# 2837970
2611 IF v_debug THEN fnd_file.put_line(fnd_file.log, ' transaction_name -> '||transaction_name); END IF;
2612
2613 --2001/03/30 Manohar Mishra
2614 /*Start of Addition*/
2615 OPEN get_header_info_cur;
2616 FETCH get_header_info_cur INTO v_organization_id, v_location_id, v_batch_source_id;
2617 CLOSE get_header_info_cur;
2618
2619 OPEN get_register_code_cur(v_organization_id, v_location_id, v_batch_source_id);
2620 FETCH get_register_code_cur INTO v_register_code;
2621 CLOSE get_register_code_cur;
2622
2623 -- Vijay Shankar for Bug# 2837970
2624 ELSE -- this should get executed when tax defaultation is for CRM_QUOTE
2625 v_register_code := null;
2626 END IF;
2627 --2001/03/30 Manohar Mishra
2628
2629 /*End of Addition*/
2630
2631
2632 /** bgowrava for forward porting bug#5631784*/
2633 if p_tax_category_id is null
2634 and (p_threshold_tax_cat_id is null or p_threshold_tax_cat_id <0) then
2635 /** Both driving parameter tax_category_id and threshol_tax_category_id are invalid hence no need to do anything */
2636 return;
2637 end if;
2638
2639 if nvl(p_action, jai_constants.default_taxes) = jai_constants.default_taxes then
2640 /** Assign tax defaultation cursor object to refc_tax_cur reference by using. Call to get_tax_cat_taxes_cur will return
2641 a reference cursor */
2642
2643 if p_threshold_tax_cat_id is not null and p_threshold_tax_cat_id > 0 then
2644 /*
2645 || Cursor to check if same taxes exists in both tax categories
2646 */
2647 ln_dup_tax_exists := null;
2648 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Open/Close/Fetch cursor c_chk_tax_duplication');*/ --commented by bgowrava for bug#5631784
2649 open c_chk_tax_duplication;
2650 fetch c_chk_tax_duplication into ln_dup_tax_exists;
2651 close c_chk_tax_duplication;
2652
2653 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'ln_dup_tax_exists='||ln_dup_tax_exists);*/ --commented by bgowrava for bug#5631784
2654
2655 if ln_dup_tax_exists is not null
2656 or (nvl(p_threshold_tax_cat_id,-1) = p_tax_category_id)
2657 then
2658
2659 fnd_message.set_name('JA', 'JAI_DUP_TAX_IN_TAX_CAT');
2660 app_exception.raise_exception ;
2661
2662 end if;
2663
2664 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Open/Close/Fetch cursor c_get_max_tax_line');*/ --commented by bgowrava for bug#5631784
2665 open c_get_max_tax_line;
2666 fetch c_get_max_tax_line into ln_max_tax_line;
2667 close c_get_max_tax_line ;
2668
2669 /* 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
2670 open c_get_max_rgm_tax_line;
2671 fetch c_get_max_rgm_tax_line into ln_max_rgm_tax_line;
2672 close c_get_max_rgm_tax_line ;
2673
2674 /* 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
2675
2676 end if;
2677
2678 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Opening ref cursor');*/ --commented by bgowrava for bug#5631784
2679 get_tax_cat_taxes_cur ( p_tax_category_id => p_tax_category_id
2680 , p_threshold_tax_cat_id => p_threshold_tax_cat_id
2681 , p_max_tax_line => ln_max_tax_line
2682 , p_max_rgm_tax_line => ln_max_rgm_tax_line
2683 , p_refc_tax_cat_taxes_cur => refc_tax_cur
2684 , pv_retroprice_changed => pv_retroprice_changed --Added by Kevin Cheng for Retroactive Price 2008/01/14
2685 );
2686
2687 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Cursor is opened');*/ --commented by bgowrava for bug#5631784
2688
2689 elsif p_action = jai_constants.recalculate_taxes then
2690 /**
2691 Following is a dynamic sql string which can be modifed as per requirement
2692
2693 The sql has four place holders defined as below
2694 $$EXTRA_SELECT_COLUMN_LIST$$ - Use this place holder to select additional columns in the sql.
2695 You must also change corrosponding fetch statements and the record being used for fetch.
2696 SELECT statement above should also be changed to include the newly added columns
2697 as they are sharing a common cursor and fetch record.
2698
2699 $$TAX_SOURCE_TABLE$$ - At runtime this placeholder must be replaced with name of
2700 source table to be used for recalculation
2701 $$SOURCE_TABLE_FILTER$$ - At runtime, this place holder must represent a boolean condition
2702 which can filter required rows from the source table
2703 for recalculation. It must be the first condition and should never
2704 start with either AND or OR
2705 $$ADDITIONAL_WHERE_CLAUSE$$ - Replace the placeholder with additional conditions if any.
2706 The condition must start with either AND or OR keyword
2707 $$ADDITIONAL_ORDER_BY$$ - Replace the placeholder with list of columns and order sequence, if required.
2708 Column list must start with comma (,)
2709 If any of this placeholder is not required to be used it must be replaced with a null value as below
2710 replace ( lv_recalculation_sql
2711 , '$$EXTRA_SELECT_COLUMN_LIST$$'
2712 , ''
2713 );
2714 */
2715 lv_recalculation_sql :=
2716 ' select a.tax_id
2717 , a.tax_line_no lno
2718 , a.precedence_1 p_1
2719 , a.precedence_2 p_2
2720 , a.precedence_3 p_3
2721 , a.precedence_4 p_4
2722 , a.precedence_5 p_5
2723 , a.precedence_6 p_6
2724 , a.precedence_7 p_7
2725 , a.precedence_8 p_8
2726 , a.precedence_9 p_9
2727 , a.precedence_10 p_10
2728 , a.tax_rate
2729 , a.tax_amount
2730 , b.uom_code
2731 , b.end_date valid_date
2732 , DECODE(rttv.regime_code, '''||jai_constants.vat_regime||''', 4, /* added by ssumaith - bug# 4245053*/
2733 /*'''||jai_constants.cgst_regime||''', 7, *//* Added by Jia for GST Bug#10043656 on 2010/09/10 */
2734 /*'''||jai_constants.sgst_regime||''', 7, *//* Added by Jia for GST Bug#10043656 on 2010/09/10 */
2735 '''||jai_constants.customs_regime||''', 8, /* Added by Bo Li for Bug#11684111 BOE Ehancement */
2736 DECODE(UPPER(b.tax_type), ''EXCISE'' , 1
2737 , ''ADDL. EXCISE'', 1
2738 , ''OTHER EXCISE'', 1
2739 , ''TDS'' , 2
2740 , ''EXCISE_EDUCATION_CESS'',1
2741 , '''||JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS||''' , 1 /*bduvarag for the bug#5989740*/
2742 , ''CVD_EDUCATION_CESS'' ,1
2743 , '''||JAI_CONSTANTS.TAX_TYPE_SH_CVD_EDU_CESS||''' , 1 /*bduvarag for the bug#5989740*/
2744 , 0
2745 )
2746 ) tax_type_val
2747 , b.mod_cr_percentage
2748 , b.vendor_id
2749 , b.tax_type
2750 , nvl(b.rounding_factor,0) rounding_factor
2751 , b.adhoc_flag
2752 $$EXTRA_SELECT_COLUMN_LIST$$
2753 from $$TAX_SOURCE_TABLE$$ a
2754 , JAI_CMN_TAXES_ALL b
2755 , jai_regime_tax_types_v rttv
2756 where $$SOURCE_TABLE_FILTER$$
2757 and rttv.tax_type (+) = b.tax_type
2758 and a.tax_id = b.tax_id $$ADDITIONAL_WHERE_CLAUSE$$
2759 order by a.tax_line_no $$ADDITIONAL_ORDER_BY$$';
2760
2761
2762 /** No extra columns required. Dummy column (NULL) tax_category_id needs to be added in the last as same record (rec) is being used
2763 when fetching the cursor. If there is a need to override this default behaviour then please replace these place holder with
2764 desired strings which can be evaluated at runtime by sql-engine
2765 */
2766 lv_recalculation_sql :=
2767 replace ( lv_recalculation_sql
2768 , '$$EXTRA_SELECT_COLUMN_LIST$$'
2769 , ',null tax_category_id'
2770 );
2771
2772 /** No additional filtering required */
2773 lv_recalculation_sql :=
2774 replace ( lv_recalculation_sql
2775 , '$$ADDITIONAL_WHERE_CLAUSE$$'
2776 , ''
2777 );
2778
2779 /** No additional sorting required */
2780 lv_recalculation_sql :=
2781 replace ( lv_recalculation_sql
2782 , '$$ADDITIONAL_ORDER_BY$$'
2783 , ''
2784 );
2785
2786 if upper(p_source_trx_type) = jai_constants.source_ttype_delivery then
2787
2788 /** replace the correct tax source table */
2789 lv_recalculation_sql :=
2790 replace ( lv_recalculation_sql
2791 , '$$TAX_SOURCE_TABLE$$'
2792 , 'JAI_OM_WSH_LINE_TAXES'
2793 );
2794 /** replace join condition */
2795 lv_recalculation_sql :=
2796 replace ( lv_recalculation_sql
2797 , '$$SOURCE_TABLE_FILTER$$'
2798 , 'a.delivery_detail_id = ' || p_line_id
2799 );
2800 elsif upper(p_source_trx_type) = jai_constants.bill_only_invoice then
2801
2802 /** For bill_only_invoice tax source table is ja_in_ra_cust_trx_tax_lines*/
2803 lv_recalculation_sql :=
2804 replace ( lv_recalculation_sql
2805 , '$$TAX_SOURCE_TABLE$$'
2806 , 'JAI_AR_TRX_TAX_LINES'
2807 );
2808 /** replace join condition */
2809 lv_recalculation_sql :=
2810 replace ( lv_recalculation_sql
2811 , '$$SOURCE_TABLE_FILTER$$'
2812 , 'a.link_to_cust_trx_line_id = ' || p_line_id
2813 );
2814 /*
2815 elsif upper(pv_tax_source_table) = '<some tax table>' then
2816 ...
2817 ...
2818 */
2819
2820 -- Date 24-Apr-2007 Added by SACSETHI for bug 6012570 (5876390)
2821 -- in This , Recalculation will be happen in Draft invoice
2822 ---------------------------------------------------------
2823 elsif upper(p_source_trx_type) = jai_constants.PA_DRAFT_INVOICE then
2824
2825 lv_recalculation_sql :=
2826 replace ( lv_recalculation_sql
2827 , 'a.tax_amount'
2828 , 'a.tax_amt'
2829 );
2830
2831 lv_recalculation_sql :=
2832 replace ( lv_recalculation_sql
2833 , '$$TAX_SOURCE_TABLE$$'
2834 , 'JAI_CMN_DOCUMENT_TAXES'
2835 );
2836 /** replace join condition */
2837 lv_recalculation_sql :=
2838 replace ( lv_recalculation_sql
2839 , '$$SOURCE_TABLE_FILTER$$'
2840 , 'a.SOURCE_DOC_LINE_ID = ' || p_line_id || ' and SOURCE_DOC_TYPE ='''|| jai_constants.PA_DRAFT_INVOICE || ''''
2841 );
2842
2843 -- Added by Jason Liu for standalone invoice on 2007/08/23
2844 ----------------------------------------------------------------------
2845 ELSIF upper(p_source_trx_type) =
2846 jai_constants.G_AP_STANDALONE_INVOICE
2847 THEN
2848
2849 lv_recalculation_sql :=
2850 REPLACE( lv_recalculation_sql
2851 , 'a.tax_amount'
2852 , 'a.tax_amt'
2853 );
2854
2855 lv_recalculation_sql :=
2856 REPLACE( lv_recalculation_sql
2857 , '$$TAX_SOURCE_TABLE$$'
2858 , 'JAI_CMN_DOCUMENT_TAXES'
2859 );
2860 -- replace join condition
2861 lv_recalculation_sql :=
2862 REPLACE( lv_recalculation_sql
2863 , '$$SOURCE_TABLE_FILTER$$'
2864 , 'a.SOURCE_DOC_LINE_ID = ' || p_line_id ||
2865 ' and SOURCE_DOC_TYPE ='''||
2866 jai_constants.G_AP_STANDALONE_INVOICE || ''''
2867 );
2868 ----------------------------------------------------------------------
2869 end if; /*pv_tax_source_table*/
2870
2871 /**
2872 When control comes here, a valid sql statement hold by variable lv_recalculate_sql
2873 must be ready to execute.
2874
2875 open a dynamic select statement using OPEN-FOR statement
2876 */
2877 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'TAX RECALCULATION SQL STATEMENT');
2878 jai_cmn_debug_contexts_pkg.print (ln_reg_id, lv_recalculation_sql);
2879 jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Opening ref cursor with dynamic sql'); */--commented by bgowrava for bug#5631784
2880
2881 open refc_tax_cur for lv_recalculation_sql;
2882
2883 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Cursor is opened with lv_recalculation_sql');*/ --commented by bgowrava for bug#5631784
2884
2885 end if; /** RECALCULATE */
2886
2887 /** Clear the tax table */
2888 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Before fetching cursor rows and starting loop');*/ --commented by bgowrava for bug#5631784
2889 lt_tax_table.delete;
2890
2891 loop
2892 /* 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
2893 fetch refc_tax_cur into rec;
2894 /* 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
2895 exit when refc_tax_cur%notfound;
2896
2897 /** End of bug 5631784*/
2898
2899
2900
2901 /** Add current record in the lt_tax_table for future use at the time of either UPDATE or INSERT into the tables*/
2902
2903 --FOR rec IN tax_cur(p_tax_category_id) LOOP
2904 lt_tax_table(lt_tax_table.count+1) := rec;
2905 p1(row_count) := nvl(rec.p_1,-1);
2906 p2(row_count) := nvl(rec.p_2,-1);
2907 p3(row_count) := nvl(rec.p_3,-1);
2908 p4(row_count) := nvl(rec.p_4,-1);
2909 p5(row_count) := nvl(rec.p_5,-1);
2910 p6(row_count) := nvl(rec.p_6,-1); -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
2911 p7(row_count) := nvl(rec.p_7,-1);
2912 p8(row_count) := nvl(rec.p_8,-1);
2913 p9(row_count) := nvl(rec.p_9,-1);
2914 p10(row_count) := nvl(rec.p_10,-1);
2915 tax_rate_tab(row_count) := NVL(rec.tax_rate,0);
2916
2917
2918 /*
2919 || The following code added by aiyer for the bug 4691616
2920 || Purpose:
2921 || 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)
2922 || 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
2923 || To calculate the BASE_TAX_AMOUNT of the taxes whose tax_rate is zero
2924 */
2925
2926 IF rec.tax_rate is null THEN
2927 /*
2928 ||Indicates qty based taxes
2929 */
2930 tax_rate_zero_tab(row_count) := 0;
2931
2932 ELSIF rec.tax_rate = 0 THEN
2933 /*
2934 ||Indicates 0% tax rate becasue a tax can have a rate as 0%.
2935 */
2936 tax_rate_zero_tab(row_count) := -9999;
2937
2938 ELSE
2939 tax_rate_zero_tab(row_count) := rec.tax_rate;
2940
2941 END IF;
2942
2943 tax_type_tab(row_count) := rec.tax_type_val;
2944 /*End of bug 4691616 */
2945 -- tax_amt_tab(row_count) := 0;
2946 /*added for bug#6498072, start*/
2947 --Comment out by Kevin Cheng
2948 /*IF p_action = jai_constants.recalculate_taxes AND --recalculate_taxes
2949 NVL(rec.adhoc_flag,'N') = 'Y' --adhoc_flag='Y'
2950 THEN
2951 tax_amt_tab(row_count) := nvl(rec.tax_amount,0) ;
2952 ELSE
2956
2953 tax_amt_tab(row_count) := 0;
2954 END IF ;*/
2955 /*bug#6498072, end*/
2957 --Added by Kevin Cheng -- for remain unchanged taxes
2958 --1, Ad hoc taxes
2959 --2, UOM based taxes
2960 --3, Assessable value base taxes (Excise/VAT)
2961 --4, Third party taxes
2962 --=================================================================================
2963 IF NVL(rec.adhoc_flag,'N') = 'Y' --Ad hoc
2964 THEN
2965 lv_tax_remain_flag := 'Y';
2966 ELSIF tax_rate_tab(row_count) = 0 AND rec.uom_code IS NOT NULL --UOM based
2967 THEN
2968 lv_tax_remain_flag := 'Y';
2969 ELSIF rec.tax_type_val = 1 AND p_assessable_value <> p_tax_amount --Excise assessable value based
2970 THEN
2971 lv_tax_remain_flag := 'Y';
2972 ELSIF rec.tax_type_val = 4 AND p_vat_assessable_value <> p_tax_amount --VAT assessable value based
2973 THEN
2974 lv_tax_remain_flag := 'Y';
2975 -- Added by Jia for GST Bug#10043656 on 2010/09/10, Begin
2976 --------------------------------------------------------------------------------------------------
2977 /*
2978 ELSIF rec.tax_type_val = 7 AND pn_gst_assessable_value <> p_tax_amount --GST assessable value based
2979 THEN
2980 lv_tax_remain_flag := 'Y';
2981 */
2982 --------------------------------------------------------------------------------------------------
2983 -- Added by Jia for GST Bug#10043656 on 2010/09/10, End
2984 -- Added by Bo Li for Bug#11684111 BOE Ehancement , Begin
2985 --------------------------------------------------------------------------------------------------
2986 ELSIF rec.tax_type_val = 8 AND pn_customs_assessable_value <> p_tax_amount --GST assessable value based
2987 THEN
2988 lv_tax_remain_flag := 'Y';
2989 --------------------------------------------------------------------------------------------------
2990 -- Added by Bo Li for Bug#11684111 BOE Ehancement , End
2991 ELSIF rec.vendor_id <> p_vendor_id --Third party
2992 THEN
2993 lv_tax_remain_flag := 'Y';
2994 ELSE
2995 lv_tax_remain_flag := 'N';
2996 END IF;
2997
2998 IF lv_tax_remain_flag = 'Y'
2999 THEN
3000 --Get line location id from transaction_name
3001 IF SUBSTR( transaction_name, 1, 1 ) = 'R' THEN
3002 lv_transaction_name := 'RFQ';
3003 lv_start := 4;
3004 ELSIF SUBSTR( transaction_name, 1, 1 ) = 'S' THEN
3005 lv_transaction_name := 'RFQ';
3006 lv_start := 10;
3007 ELSIF SUBSTR( transaction_name, 1, 1 ) = 'Q' THEN
3008 lv_transaction_name := 'QUOTATION';
3009 lv_start := 10;
3010 ELSIF SUBSTR( transaction_name, 1, 1 ) = 'B' AND SUBSTR( transaction_name, 1, 8 ) <> 'BLANKETR' THEN
3011 lv_transaction_name := 'BLANKET';
3012 lv_start := 8;
3013 ELSIF SUBSTR( transaction_name, 1, 8 ) = 'BLANKETR' THEN
3014 lv_transaction_name := 'RFQ';
3015 lv_start := 9;
3016 ELSIF SUBSTR( transaction_name, 1, 1 ) = 'O' THEN
3017 lv_transaction_name := 'OTHERS';
3018 lv_start := 7;
3019 END IF;
3020
3021 lv_line_loc_id := TO_NUMBER( SUBSTR( transaction_name, lv_start, LENGTH( transaction_name )-( lv_start + 1 )));
3022 SELECT
3023 original_tax_amount
3024 INTO
3025 tax_amt_tab(row_count)
3026 FROM
3027 Jai_Retro_Tax_Changes jrtc
3028 WHERE jrtc.tax_id = rec.tax_id
3029 AND jrtc.line_change_id = (SELECT
3030 line_change_id
3031 FROM
3032 Jai_Retro_Line_Changes jrlc
3033 WHERE jrlc.line_location_id = lv_line_loc_id
3034 AND jrlc.doc_type IN ( 'RELEASE'
3035 , 'RECEIPT'
3036 , 'STANDARD PO'
3037 )
3038 AND jrlc.doc_version_number = (SELECT
3039 MAX(jrlc1.doc_version_number)
3040 FROM
3041 Jai_Retro_Line_Changes jrlc1
3042 WHERE jrlc1.line_location_id = lv_line_loc_id
3043 AND jrlc1.doc_type IN ( 'RELEASE'
3044 , 'RECEIPT'
3045 , 'STANDARD PO'
3046 )
3047 )
3048 );
3049
3050 tax_rate_tab(row_count) := 0;
3051 tax_rate_zero_tab(row_count) := 0;
3052 adhoc_flag_tab(row_count) := 'Y';
3053
3054 ELSIF lv_tax_remain_flag = 'N'
3055 THEN
3056 tax_amt_tab(row_count) := 0;
3057 adhoc_flag_tab(row_count):= rec.adhoc_flag ; /* rchandan bug#6030615 */
3058 END IF;
3059 --=================================================================================
3060
3061 round_factor_tab(row_count):=rec.rounding_factor; --added by csahoo for bug#6077133
3062 base_tax_amt_tab(row_count) := 0;
3063 --Comment out by Kevin Cheng
3064 --adhoc_flag_tab(row_count):=rec.adhoc_flag ; /* rchandan bug#6030615 */
3065
3066 --Comment out by Kevin Cheng
3067 /*IF tax_rate_tab(row_count) = 0
3068 AND rec.uom_code is not null --added by csahoo for bug#6498072
3069 THEN
3070 -- Start of bug 3749294
3071 \*
3072 Code added by aiyer for the bug 3749294
3076 *\
3073 Check whether an exact match exists between the transaction uom and the setup uom (obtained through the tax_category list).
3074 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
3075 determined and tax amounts,base_tax_amounts are calculated for defaultation.
3077 Inv_Convert.inv_um_conversion( p_uom_code, rec.uom_code, p_inventory_item_id, v_conversion_rate);
3078 IF NVL(v_conversion_rate, 0) <= 0 THEN
3079 -- pramasub start FP
3080 \*4281841 ..rchandan..start*\
3081 OPEN uom_class_cur(p_uom_code, rec.uom_code);
3082 FETCH uom_class_cur INTO uom_cls;
3083 IF uom_class_cur%FOUND THEN
3084 Inv_Convert.inv_um_conversion( p_uom_code, rec.uom_code, 0, v_conversion_rate);
3085 ELSE
3086 v_conversion_rate := 0;
3087 END IF;
3088 CLOSE uom_class_cur;
3089 \*4281841 ..rchandan..end*\
3090 -- pramasub end FP
3091 --Inv_Convert.inv_um_conversion( p_uom_code, rec.uom_code, 0, v_conversion_rate); commented by pramasub
3092 IF NVL(v_conversion_rate, 0) <= 0 THEN
3093
3094 \* for cash receipt there will be no lines. sacsethi for 6012570 (5876390) *\
3095 if (p_uom_code is null and p_inventory_item_id is null )
3096 and p_source_trx_type = jai_constants.ar_cash
3097 then --
3098 v_conversion_rate := 1;
3099
3100 \*
3101 Date 22-feb-2007 Added by SACSETHI for bug 6012570 (5876390)
3102 in This , Recalculation will be happen in Draft invoice
3103 *\
3104 elsif (p_uom_code is null and p_inventory_item_id is null )
3105 and p_source_trx_type= jai_constants.pa_draft_invoice
3106 then
3107 v_conversion_rate := 0;
3108
3109 else
3110 v_conversion_rate := 0;
3111 end if;
3112
3113 END IF;
3114 END IF;
3115 --tax_amt_tab(rec.lno) := nvl(rec.tax_amount * v_conversion_rate, 0) * p_line_quantity; -- cbabu for EnhancementBug# 2427465, compact code
3116 \*added for bug#6498072, start*\
3117 IF p_action = jai_constants.recalculate_taxes THEN
3118 tax_amt_tab(rec.lno) := nvl(rec.tax_amount * v_conversion_rate, 0) ;
3119 ELSE
3120 tax_amt_tab(rec.lno) := nvl(rec.tax_amount * v_conversion_rate, 0) * p_line_quantity ;
3121 END IF ;
3122 \*added for bug#6498072, end*\
3123 tax_amt_tab(rec.lno):=round(tax_amt_tab(rec.lno),round_factor_tab(rec.lno)); --added by csahoo for bug#6077133
3124 base_tax_amt_tab(rec.lno) := tax_amt_tab(rec.lno);
3125 -- End of bug 3749294
3126 END IF;*/
3127
3128 IF rec.valid_date IS NULL OR rec.valid_date >= SYSDATE THEN
3129 end_date_tab(row_count) := 1;
3130 ELSE
3131 tax_amt_tab(row_count) := 0;
3132 end_date_tab(row_count) := 0;
3133 END IF;
3134 row_count := row_count + 1;
3135 END LOOP;
3136
3137 row_count := row_count - 1;
3138
3139 FOR I IN 1..row_count LOOP
3140 IF end_date_tab(I) <> 0 THEN
3141 IF tax_type_tab(I) = 1 THEN
3142 bsln_amt := p_assessable_value;
3143 ELSIF tax_type_tab(I) = 4 THEN
3144 bsln_amt := p_vat_assessable_value;
3145 /*ELSIF tax_type_tab(I) = 7 THEN --Added by Jia for GST Bug#10043656 on 2010/09/10
3146 bsln_amt := pn_gst_assessable_value; --Added by Jia for GST Bug#10043656 on 2010/09/10
3147 */
3148 ELSIF tax_type_tab(I) = 8 THEN --Added by Bo Li for Bug#11684111 BOE Ehancement
3149 bsln_amt := pn_customs_assessable_value; --Added by Bo Li for Bug#11684111 BOE Ehancement
3150 ELSE
3151 bsln_amt := p_tax_amount;
3152 END IF;
3153
3154 IF tax_rate_tab(I) <> 0 THEN
3155
3156 IF P1(I) < I AND P1(I) NOT IN (-1,0) THEN
3157 vamt := vamt + NVL(tax_amt_tab(P1(I)),0);
3158 ELSIF P1(I) = 0 THEN
3159 vamt := vamt + bsln_amt;
3160 END IF;
3161 IF p2(I) < I AND p2(I) NOT IN (-1,0) THEN
3162 vamt := vamt + NVL(tax_amt_tab(p2(I)),0);
3163 ELSIF p2(I) = 0 THEN
3164 vamt := vamt + bsln_amt;
3165 END IF;
3166 IF p3(I) < I AND p3(I) NOT IN (-1,0) THEN
3167 vamt := vamt + NVL(tax_amt_tab(p3(I)),0);
3168 ELSIF p3(I) = 0 THEN
3169 vamt := vamt + bsln_amt;
3170 END IF;
3171 IF p4(I) < I AND p4(I) NOT IN (-1,0) THEN
3172 vamt := vamt + NVL(tax_amt_tab(p4(I)),0);
3173 ELSIF p4(I) = 0 THEN
3174 vamt := vamt + bsln_amt;
3175 END IF;
3176 IF p5(I) < I AND p5(I) NOT IN (-1,0) THEN
3177 vamt := vamt + NVL(tax_amt_tab(p5(I)),0);
3178 ELSIF p5(I) = 0 THEN
3179 vamt := vamt + bsln_amt;
3180 END IF;
3181 -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
3182 -- start bug 5228046
3183 IF P6(I) < I AND P6(I) NOT IN (-1,0) THEN
3184 vamt := vamt + NVL(tax_amt_tab(P6(I)),0);
3185 ELSIF P6(I) = 0 THEN
3186 vamt := vamt + bsln_amt;
3187 END IF;
3188 IF p7(I) < I AND p7(I) NOT IN (-1,0) THEN
3189 vamt := vamt + NVL(tax_amt_tab(p7(I)),0);
3190 ELSIF p7(I) = 0 THEN
3194 vamt := vamt + NVL(tax_amt_tab(p8(I)),0);
3191 vamt := vamt + bsln_amt;
3192 END IF;
3193 IF p8(I) < I AND p8(I) NOT IN (-1,0) THEN
3195 ELSIF p8(I) = 0 THEN
3196 vamt := vamt + bsln_amt;
3197 END IF;
3198 IF p9(I) < I AND p9(I) NOT IN (-1,0) THEN
3199 vamt := vamt + NVL(tax_amt_tab(p9(I)),0);
3200 ELSIF p9(I) = 0 THEN
3201 vamt := vamt + bsln_amt;
3202 END IF;
3203 IF p10(I) < I AND p10(I) NOT IN (-1,0) THEN
3204 vamt := vamt + NVL(tax_amt_tab(p10(I)),0);
3205 ELSIF p10(I) = 0 THEN
3206 vamt := vamt + bsln_amt;
3207 END IF;
3208 -- end bug 5228046
3209
3210 v_tax_amt := v_tax_amt + (vamt * (tax_rate_tab(I)/100));
3211 base_tax_amt_tab(I) := vamt;
3212 tax_amt_tab(I) := NVL(tax_amt_tab(I),0) + v_tax_amt;
3213 tax_amt_tab(I) := round(tax_amt_tab(I) ,round_factor_tab(I)); --added by csahoo for bug#6077133
3214 vamt := 0;
3215 v_tax_amt := 0;
3216 END IF;
3217
3218 ELSE
3219
3220 tax_amt_tab(I) := 0;
3221 base_tax_amt_tab(I) := 0;
3222
3223 END IF;
3224
3225 END LOOP;
3226
3227 FOR I IN 1..row_count LOOP
3228 IF end_date_tab( I ) <> 0 THEN
3229 IF tax_rate_tab(I) <> 0 THEN
3230 IF P1(I) > I THEN
3231 vamt := vamt + NVL(tax_amt_tab(P1(I)),0);
3232 END IF;
3233 IF p2(I) > I THEN
3234 vamt := vamt + NVL(tax_amt_tab(p2(I)),0);
3235 END IF;
3236 IF p3(I) > I THEN
3237 vamt := vamt + NVL(tax_amt_tab(p3(I)),0);
3238 END IF;
3239 IF p4(I) > I THEN
3240 vamt := vamt + NVL(tax_amt_tab(p4(I)),0);
3241 END IF;
3242 IF p5(I) > I THEN
3243 vamt := vamt + NVL(tax_amt_tab(p5(I)),0);
3244 END IF;
3245 -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
3246 -- start bug 5228046
3247 IF P6(I) > I THEN
3248 vamt := vamt + NVL(tax_amt_tab(P6(I)),0);
3249 END IF;
3250 IF p7(I) > I THEN
3251 vamt := vamt + NVL(tax_amt_tab(p7(I)),0);
3252 END IF;
3253 IF p8(I) > I THEN
3254 vamt := vamt + NVL(tax_amt_tab(p8(I)),0);
3255 END IF;
3256 IF p9(I) > I THEN
3257 vamt := vamt + NVL(tax_amt_tab(p9(I)),0);
3258 END IF;
3259 IF p10(I) > I THEN
3260 vamt := vamt + NVL(tax_amt_tab(p10(I)),0);
3261 END IF;
3262 -- end bug 5228046
3263 base_tax_amt_tab(I) := vamt;
3264 v_tax_amt := v_tax_amt + (vamt * (tax_rate_tab(I)/100));
3265 IF vamt <> 0 THEN
3266 base_tax_amt_tab(I) := base_tax_amt_tab(I) + vamt;
3267 END IF;
3268 tax_amt_tab(I) := NVL(tax_amt_tab(I),0) + v_tax_amt;
3269 tax_amt_tab(I) := round(tax_amt_tab(I) ,round_factor_tab(I)); --added by csahoo for bug#6077133
3270 vamt := 0;
3271 v_tax_amt := 0;
3272 END IF;
3273
3274 ELSE
3275
3276 base_tax_amt_tab(I) := vamt;
3277 tax_amt_tab(I) := 0;
3278 END IF;
3279
3280 END LOOP;
3281
3282 FOR counter IN 1 .. max_iter LOOP
3283 vamt := 0;
3284 v_tax_amt := 0;
3285
3286 FOR i IN 1 .. row_count LOOP
3287
3288 /*
3289 || Modified by aiyer for the fwd porting bug 4691616.
3290 || The following if clause will restrict the taxes whose tax_rate is null
3291 || i.e when tax_rate is null, tax_rate_tab(i) is 0.
3292 */
3293 IF ( tax_rate_tab( i ) <> 0 OR
3294 tax_rate_zero_tab(I) = -9999
3295 ) AND
3296 end_date_tab( I ) <> 0
3297 THEN
3298
3299 IF tax_type_tab( I ) = 1 THEN
3300 v_amt := p_assessable_value;
3301 ELSIF tax_type_tab(I) = 4 THEN
3302 v_amt := p_vat_assessable_value;
3303 /*ELSIF tax_type_tab(I) = 7 THEN -- Added by Jia for GST Bug#10043656 on 2010/09/10
3304 v_amt := pn_gst_assessable_value; -- Added by Jia for GST Bug#10043656 on 2010/09/10
3305 */
3306 ELSIF tax_type_tab(I) = 8 THEN --Added by Bo Li for Bug#11684111 BOE Ehancement
3307 v_amt := pn_customs_assessable_value; --Added by Bo Li for Bug#11684111 BOE Ehancement
3308 ELSE
3309 IF p_assessable_value IN ( 0, -1 ) OR tax_type_tab( I ) <> 1 THEN
3310 v_amt := p_tax_amount;
3311 ELSIF p_vat_assessable_value IN ( 0, -1 ) OR tax_type_tab( I ) <> 4 THEN
3312 v_amt := p_tax_amount;
3313 /*ELSIF pn_gst_assessable_value IN ( 0, -1 ) OR tax_type_tab( I ) <> 7 THEN -- Added by Jia for GST Bug#10043656 on 2010/09/10
3314 v_amt := p_tax_amount; -- Added by Jia for GST Bug#10043656 on 2010/09/10
3315 */
3316 ELSIF pn_customs_assessable_value IN ( 0, -1 ) OR tax_type_tab( I ) <> 8 THEN --Added by Bo Li for Bug#11684111 BOE Ehancement
3317 v_amt := p_tax_amount; --Added by Bo Li for Bug#11684111 BOE Ehancement
3318 END IF;
3319 END IF;
3320
3324 ELSIF P1(i) = 0 THEN
3321 IF P1( i ) <> -1 THEN
3322 IF P1( i ) <> 0 THEN
3323 vamt := vamt + tax_amt_tab( P1( I ) );
3325 vamt := vamt + v_amt;
3326 END IF;
3327 END IF;
3328
3329 IF p2( i ) <> -1 THEN
3330 IF p2( i ) <> 0 THEN
3331 vamt := vamt + tax_amt_tab( p2( I ) );
3332 ELSIF p2(i) = 0 THEN
3333 vamt := vamt + v_amt;
3334 END IF;
3335 END IF;
3336 IF p3( i ) <> -1 THEN
3337 IF p3( i ) <> 0 THEN
3338 vamt := vamt + tax_amt_tab( p3( I ) );
3339 ELSIF p3(i) = 0 THEN
3340 vamt := vamt + v_amt;
3341 END IF;
3342 END IF;
3343
3344 IF p4( i ) <> -1 THEN
3345 IF p4( i ) <> 0 THEN
3346 vamt := vamt + tax_amt_tab( p4( i ) );
3347 ELSIF p4(i) = 0 THEN
3348 vamt := vamt + v_amt;
3349 END IF;
3350 END IF;
3351
3352 IF p5( i ) <> -1 THEN
3353 IF p5( i ) <> 0 THEN
3354 vamt := vamt + tax_amt_tab( p5( i ) );
3355 ELSIF p5(i) = 0 THEN
3356 vamt := vamt + v_amt;
3357 END IF;
3358 END IF;
3359
3360 -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
3361 -- start bug 5228046
3362 IF P6( i ) <> -1 THEN
3363 IF P6( i ) <> 0 THEN
3364 vamt := vamt + tax_amt_tab( P6( I ) );
3365 ELSIF P6(i) = 0 THEN
3366 vamt := vamt + v_amt;
3367 END IF;
3368 END IF;
3369
3370 IF p7( i ) <> -1 THEN
3371 IF p7( i ) <> 0 THEN
3372 vamt := vamt + tax_amt_tab( p7( I ) );
3373 ELSIF p7(i) = 0 THEN
3374 vamt := vamt + v_amt;
3375 END IF;
3376 END IF;
3377 IF p8( i ) <> -1 THEN
3378 IF p8( i ) <> 0 THEN
3379 vamt := vamt + tax_amt_tab( p8( I ) );
3380 ELSIF p8(i) = 0 THEN
3381 vamt := vamt + v_amt;
3382 END IF;
3383 END IF;
3384
3385 IF p9( i ) <> -1 THEN
3386 IF p9( i ) <> 0 THEN
3387 vamt := vamt + tax_amt_tab( p9( i ) );
3388 ELSIF p9(i) = 0 THEN
3389 vamt := vamt + v_amt;
3390 END IF;
3391 END IF;
3392
3393 IF p10( i ) <> -1 THEN
3394 IF p10( i ) <> 0 THEN
3395 vamt := vamt + tax_amt_tab( p10( i ) );
3396 ELSIF p10(i) = 0 THEN
3397 vamt := vamt + v_amt;
3398 END IF;
3399 END IF;
3400
3401 -- end bug 5228046
3402
3403
3404 base_tax_amt_tab(I) := vamt;
3405 tax_target_tab(I) := vamt;
3406
3407 --------------------------------------------------------------------------------------
3408 /*Change History: jai_cmn_tax_defaultation_pkg
3409 Last Modified By Jagdish Bhosle. 2001/04/05
3410 The follow check will ensure that for Bond reg. Txns
3411 excise duty will not be added to original Line amount. */
3412 --------------------------------------------------------------------------------------
3413 IF (v_register_code='BOND_REG') THEN --- Added By Jagdish 2001/04/05
3414 IF counter = max_iter AND tax_type_tab( I ) NOT IN ( 1, 2) THEN
3415 v_func_tax_amt := v_tax_amt + ( vamt * ( tax_rate_tab( i )/100));
3416 v_tax_amt := v_tax_amt + ( vamt * ( tax_rate_tab( i )/100));
3417 END IF;
3418
3419 ELSE
3420 v_func_tax_amt := v_tax_amt + ( vamt * ( tax_rate_tab( i )/100));
3421 v_tax_amt := v_tax_amt + ( vamt * ( tax_rate_tab( i )/100));
3422 END IF; -- End of Addition Jagdish 2001/04/05
3423
3424 ELSIF tax_rate_tab(I) = 0 THEN
3425 base_tax_amt_tab(I) := tax_amt_tab(i);
3426 v_tax_amt := tax_amt_tab( i );
3427 tax_target_tab(I) := v_tax_amt;
3428 ELSIF end_date_tab( I ) = 0 THEN
3429 tax_amt_tab(I) := 0;
3430 base_tax_amt_tab(I) := 0;
3431 tax_target_tab(I) := 0;
3432 END IF;
3433
3434 tax_amt_tab( I ) := NVL( v_tax_amt, 0 );
3435 tax_amt_tab(I) := round(tax_amt_tab(I) ,round_factor_tab(I)); --added by csahoo for bug#6077133
3436 func_tax_amt_tab(I) := NVL(v_func_tax_amt,0);
3437
3438 IF counter = max_iter THEN
3439 IF end_date_tab(I) = 0 THEN
3440 tax_amt_tab( i ) := 0;
3441 func_tax_amt_tab(i) := 0;
3442 END IF;
3443 END IF;
3444
3445 vamt := 0;
3446 v_amt := 0;
3447 v_tax_amt := 0;
3448 v_func_tax_amt := 0;
3449
3450 END LOOP;
3451
3452 END LOOP;
3453
3454 END IF;
3455 --===========================================================================================================
3456
3457 row_count := 1;
3458
3459 -- this is the place where you hv to add your insert statements for transaction specific tables
3460 rec := null ; -- added by ssumaith - code review comments for TCS buG#6109941
3461 FOR i in 1.. lt_tax_table.count LOOP
3462 rec := lt_tax_table(i);
3463 -- ends additions by ssumaith - code review comments for TCS bug# 6109941
3467
3464 IF tax_type_tab(row_count) <> 2 THEN
3465 v_tax_amt := v_tax_amt + NVL(tax_amt_tab(row_count),0);
3466 END IF;
3468
3469 /** bgowrava for forward porting bug# 5631784 */
3470 if rec.mod_cr_percentage is not null and rec.mod_cr_percentage > 0 then
3471 v_modvat_flag := 'Y';
3472 elsif rec.mod_cr_percentage is null then
3473 v_modvat_flag := 'N';
3474 end if;
3475 /*end bug# 5631784 */
3476
3477 if v_debug then fnd_file.put_line(fnd_file.log, 'Before tr_name -> '||transaction_name); end if;
3478
3479 -- Vijay Shankar for Bug# 2837970
3480 IF transaction_name = 'CRM_QUOTE' THEN
3481
3482 if v_debug then fnd_file.put_line(fnd_file.log, 'Before insert into of tr_name -> '||transaction_name); end if;
3483
3484 INSERT INTO JAI_CRM_QUOTE_TAXES(quote_line_id, quote_header_id, shipment_id, tax_line_no,
3485 precedence_1, precedence_2, precedence_3, precedence_4, precedence_5,
3486 precedence_6, precedence_7, precedence_8, precedence_9, precedence_10, -- Date 31/10/2006 Bug 5228046 added by SACSETHI
3487 tax_id, tax_amount,
3488 base_tax_amount,
3489 func_tax_amount,
3490 creation_date, created_by, last_update_date,
3491 last_updated_by, last_update_login)
3492 VALUES ( p_line_id, p_header_id, p_operation_flag, row_count,
3493 rec.p_1, rec.p_2, rec.p_3, rec.p_4, rec.p_5,
3494 rec.p_6, rec.p_7, rec.p_8, rec.p_9, rec.p_10,
3495 rec.tax_id, ROUND(nvl(tax_amt_tab(row_count),0),REC.ROUNDING_FACTOR),
3496 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)),
3497 (nvl(func_tax_amt_tab(row_count),0) * v_currency_conv_factor),
3498 nvl(p_creation_date, SYSDATE), nvl(p_created_by,1), nvl(p_last_update_date,SYSDATE) ,
3499 nvl(p_last_updated_by,1), nvl(p_last_update_login,1) );
3500
3501 -- end, Vijay Shankar for Bug# 2837970
3502 ELSIF transaction_name = 'SALES_ORDER' THEN
3503 INSERT INTO JAI_OM_OE_SO_TAXES(
3504 line_id, header_id, tax_line_no,
3505 precedence_1, precedence_2, precedence_3, precedence_4, precedence_5,
3506 precedence_6, precedence_7, precedence_8, precedence_9, precedence_10, -- Date 31/10/2006 Bug 5228046 added by SACSETHI
3507 tax_id, tax_rate, qty_rate, uom,
3508 tax_amount,
3509 base_tax_amount,
3510 func_tax_amount,
3511 creation_date, created_by, last_update_date,
3512 last_updated_by, last_update_login,
3513 tax_category_id -- cbabu for EnhancementBug# 2427465
3514 ) VALUES (
3515 p_line_id, p_header_id, row_count,
3516 rec.p_1, rec.p_2, rec.p_3, rec.p_4, rec.p_5,
3517 rec.p_6, rec.p_7, rec.p_8, rec.p_9, rec.p_10,
3518 rec.tax_id, rec.tax_rate, rec.tax_amount, rec.uom_code,
3519 ROUND(NVL(tax_amt_tab(row_count),0),REC.ROUNDING_FACTOR),
3520 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)),
3521 (NVL(func_tax_amt_tab(row_count),0) * v_currency_conv_factor),
3522 p_creation_date, p_created_by, p_last_update_date,
3523 p_last_updated_by, p_last_update_login,
3524 p_tax_category_id -- cbabu for EnhancementBug# 2427465
3525 );
3526
3527 ELSIF transaction_name='INTERORG_XFER' THEN
3528 /*added by rchandan for bug#6030615*/
3529 BEGIN
3530 DECLARE
3531 v_modvat_flag VARCHAR2(1);
3532 BEGIN
3533 IF rec.mod_cr_percentage IS NOT NULL AND rec.mod_cr_percentage > 0 THEN
3534 v_modvat_flag := 'Y';
3535 ELSIF rec.mod_cr_percentage IS NULL THEN
3536 v_modvat_flag := 'N';
3537 END IF;
3538 -- bug 6436825
3539 IF REC.TAX_TYPE NOT IN ('Service', JAI_CONSTANTS.TAX_TYPE_SH_SERVICE_EDU_CESS,
3540 JAI_CONSTANTS.TAX_TYPE_SERVICE_EDU_CESS,
3541 JAI_CONSTANTS.TAX_TYPE_SH_CUSTOMS_EDU_CESS,
3542 JAI_CONSTANTS.TAX_TYPE_CUSTOMS_EDU_CESS,
3543 'CVD_EDUCATION_CESS', JAI_CONSTANTS.TAX_TYPE_SH_CVD_EDU_CESS,
3544 'Customs', 'CVD', 'ADDITIONAL_CVD' ,'TDS' , 'Modvat Recovery') THEN
3545
3546 INSERT INTO jai_cmn_document_taxes(
3547 DOC_TAX_ID,
3548 tax_line_no,
3549 tax_id ,
3550 tax_type,
3551 currency_code ,
3552 tax_rate ,
3553 qty_rate ,
3554 uom ,
3555 tax_amt ,
3556 func_tax_amt,
3557 modvat_flag,
3558 tax_category_id,
3559 source_doc_type ,
3560 source_doc_id ,
3561 source_doc_line_id ,
3562 source_table_name ,
3563 TAX_MODIFIED_BY ,
3564 adhoc_flag ,
3565 precedence_1 ,
3566 precedence_2 ,
3567 precedence_3 ,
3568 precedence_4 ,
3569 precedence_5 ,
3570 precedence_6 ,
3574 precedence_10 ,
3571 precedence_7 ,
3572 precedence_8 ,
3573 precedence_9 ,
3575 creation_date ,
3576 created_by ,
3577 last_update_date ,
3578 last_updated_by ,
3579 last_update_login )
3580 VALUES (
3581 jai_cmn_document_taxes_s.nextval ,
3582 v_line_num,
3583 rec.tax_id,
3584 rec.tax_type,
3585 p_currency,
3586 rec.tax_rate,
3587 rec.tax_amount,
3588 rec.uom_code,
3589 round(nvl(tax_amt_tab(row_count),0),rec.rounding_factor),
3590 round(NVL(func_tax_amt_tab(row_count),0) * v_currency_conv_factor,rec.rounding_factor),
3591 v_modvat_flag,
3592 p_tax_category_id ,
3593 'INTERORG_XFER',
3594 p_header_id,
3595 p_line_id,
3596 'MTL_MATERIAL_TRANSACTIONS_TEMP',
3597 NULL,
3598 rec.adhoc_flag,
3599 rec.p_1,
3600 rec.p_2,
3601 rec.p_3,
3602 rec.p_4,
3603 rec.p_5,
3604 rec.p_6,
3605 rec.p_7,
3606 rec.p_8,
3607 rec.p_9,
3608 rec.p_10,
3609 p_creation_date,
3610 p_created_by,
3611 p_last_update_date,
3612 p_last_updated_by,
3613 p_last_update_login
3614 );
3615 v_line_num:=nvl(v_line_num,1)+1;
3616 END IF;
3617 END;
3618 END;
3619
3620 ELSIF transaction_name = 'RMA_LEGACY_INSERT' THEN
3621
3622 -- This elsif added by Aparajita on 31-may-2002 for bug 2381492
3623 INSERT INTO JAI_OM_OE_RMA_TAXES (
3624 rma_line_id, tax_line_no,
3625 precedence_1, precedence_2, precedence_3, precedence_4,precedence_5,
3626 precedence_6, precedence_7, precedence_8, precedence_9,precedence_10, -- Date 31/10/2006 Bug 5228046 added by SACSETHI
3627 tax_id, tax_rate,
3628 qty_rate, uom, tax_amount,
3629 base_tax_amount,
3630 func_tax_amount, creation_date, created_by,
3631 last_update_date, last_updated_by, last_update_login
3632 ) VALUES (
3633 p_line_id, row_count,
3634 rec.p_1,rec.p_2, rec.p_3,rec.p_4, rec.p_5,
3635 rec.p_6,rec.p_7, rec.p_8,rec.p_9, rec.p_10,
3636 rec.tax_id, rec.tax_rate,
3637 rec.tax_amount, rec.uom_code, ROUND(nvl(tax_amt_tab(row_count),0), rec.rounding_factor),
3638 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) ),
3639 nvl(func_tax_amt_tab(row_count),0) * v_currency_conv_factor, p_creation_date, p_created_by,
3640 p_last_update_date, p_last_updated_by, p_last_update_login
3641 );
3642
3643 ELSIF transaction_name = 'SO_LINES_UPDATE' THEN
3644
3645 UPDATE JAI_OM_OE_SO_TAXES
3646 SET tax_amount = ROUND(NVL(tax_amt_tab(row_count),0), REC.ROUNDING_FACTOR),
3647 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)),
3648 func_tax_amount = NVL(func_tax_amt_tab(row_count),0) * v_currency_conv_factor,
3649 last_update_date = p_last_update_date,
3650 last_updated_by = p_last_updated_by,
3651 last_update_login = p_last_update_login
3652 WHERE line_id = P_line_id
3653 AND header_id = p_header_id
3654 AND tax_line_no = row_count;
3655
3656 --Added by Bo Li for bug#11684111 BOE Ehancement Begin
3657 -----------------------------------------------------------------------------------------------
3658 ELSIF transaction_name = 'BOE_INVOICE' THEN
3659
3660 --Add by Qiong Liu for bug#11684111 BOE Ehancement Begin
3661 ------------------------------
3662 BEGIN
3663 SELECT tax_type
3664 INTO lv_tax_type
3665 FROM jai_cmn_taxes_all
3666 WHERE tax_id = rec.tax_id;
3667 EXCEPTION
3668 WHEN OTHERS THEN
3669 lv_tax_type := NULL;
3670 END;
3671 ------------------------------
3672 --Add by Qiong Liu for bug#11684111 BOE Ehancement End
3673 -- Add by Wenqiong for bug#12611347 begin
3674 IF lv_tax_type IN (jai_constants.tax_type_add_cvd,
3675 jai_constants.tax_type_customs_edu_cess,
3676 jai_constants.tax_type_sh_customs_edu_cess,
3677 jai_constants.tax_type_cvd,
3678 jai_constants.tax_type_cvd_edu_cess,
3679 jai_constants.tax_type_sh_cvd_edu_cess,
3680 jai_constants.tax_type_customs,
3681 jai_constants.tax_type_boe_surcharge_duty,
3685 jai_constants.tax_type_boe_other4,
3682 jai_constants.tax_type_boe_other1,
3683 jai_constants.tax_type_boe_other2,
3684 jai_constants.tax_type_boe_other3,
3686 jai_constants.tax_type_boe_other5,
3687 jai_constants.tax_type_boe_add_customs) THEN
3688 lv_boe_flag := 'Y';
3689 ELSE
3690 lv_boe_flag := 'N';
3691 END IF;
3692 -- Add by Wenqiong for bug#12611347 end.
3693 INSERT INTO jai_boe_detail_taxes
3694 (boe_detail_tax_id,
3695 boe_detail_id,
3696 tax_line_no,
3697 precedence_1,
3698 precedence_2,
3699 precedence_3,
3700 precedence_4,
3701 precedence_5,
3702 precedence_6,
3703 precedence_7,
3704 precedence_8,
3705 precedence_9,
3706 precedence_10,
3707 tax_id,
3708 tax_type,-- Add by Qiong Liu for bug#11684111 BOE Ehancement
3709 boe_flag,-- Add by Wenqiong for bug#12611347
3710 tax_rate,
3711 qty_rate,
3712 uom,
3713 tax_amount,
3714 base_tax_amount,
3715 func_tax_amount,
3716 creation_date,
3717 created_by,
3718 last_update_date,
3719 last_updated_by,
3720 last_update_login,
3721 tax_category_id -- cbabu for EnhancementBug# 2427465
3722 )
3723 VALUES
3724 (jai_boe_detail_taxes_s.NEXTVAL,
3725 p_line_id,
3726 row_count,
3727 rec.p_1,
3728 rec.p_2,
3729 rec.p_3,
3730 rec.p_4,
3731 rec.p_5,
3732 rec.p_6,
3733 rec.p_7,
3734 rec.p_8,
3735 rec.p_9,
3736 rec.p_10,
3737 rec.tax_id,
3738 lv_tax_type,-- Add by Qiong Liu for bug#11684111 BOE Ehancement
3739 lv_boe_flag,-- Add by Wenqiong for bug#12611347
3740 rec.tax_rate,
3741 rec.tax_amount,
3742 rec.uom_code,
3743 round(nvl(tax_amt_tab(row_count), 0), rec.rounding_factor),
3744 decode(nvl(base_tax_amt_tab(row_count), 0),
3745 0,
3746 nvl(tax_amt_tab(row_count), 0),
3747 nvl(base_tax_amt_tab(row_count), 0)),
3748 (nvl(func_tax_amt_tab(row_count), 0) * v_currency_conv_factor),
3749 p_creation_date,
3750 p_created_by,
3751 p_last_update_date,
3752 p_last_updated_by,
3753 p_last_update_login,
3754 p_tax_category_id -- cbabu for EnhancementBug# 2427465
3755 );
3756
3757 ELSIF transaction_name = 'BOE_INVOICE_UPDATE' THEN
3758
3759 UPDATE jai_boe_detail_taxes
3760 SET tax_amount = round(nvl(tax_amt_tab(row_count), 0),
3761 rec.rounding_factor),
3762 base_tax_amount = decode(nvl(base_tax_amt_tab(row_count), 0),
3763 0,
3764 nvl(tax_amt_tab(row_count), 0),
3765 nvl(base_tax_amt_tab(row_count), 0)),
3766 func_tax_amount = nvl(func_tax_amt_tab(row_count), 0) *
3767 v_currency_conv_factor,
3768 last_update_date = p_last_update_date,
3769 last_updated_by = p_last_updated_by,
3770 last_update_login = p_last_update_login
3771 WHERE boe_detail_id = p_line_id
3772 AND tax_line_no = row_count;
3773 ---------------------------------------------------------------
3774 --Added by Bo Li for bug#11684111 BOE Ehancement End
3775
3776 ELSIF transaction_name = 'AR_LINES' THEN
3777
3778 --2001/03/30 Manohar Mishra
3779 -- Added the following IF condition
3780 --if (v_register_code='BOND_REG') --and (rec.tax_type_val<>1))
3781 --then
3782 --if (rec.tax_type_val<>1) then
3783
3784 /*Bug 8371741 - Start*/
3785
3786 get_created_from(p_header_id, l_created_from);
3787
3788 if (l_created_from = 'ARXTWMAI' and p_max_rgm_tax_line>0) then
3789
3790 INSERT INTO JAI_AR_TRX_TAX_LINES(
3791 customer_trx_line_id, link_to_cust_trx_line_id, tax_line_no,
3792 precedence_1, precedence_2, precedence_3, precedence_4, precedence_5,
3793 precedence_6, precedence_7, precedence_8, precedence_9, precedence_10, -- Date 31/10/2006 Bug 5228046 added by SACSETHI
3794 tax_id, tax_rate, qty_rate, uom,
3795 tax_amount,
3796 base_tax_amount,
3797 func_tax_amount,
3798 creation_date, created_by, last_update_date,
3799 last_updated_by, last_update_login
3800 ) VALUES(
3801 ra_customer_trx_lines_s.NEXTVAL, p_line_id, rec.lno,
3802 rec.p_1, rec.p_2, rec.p_3, rec.p_4, rec.p_5,
3803 rec.p_6, rec.p_7, rec.p_8, rec.p_9, rec.p_10,
3804 rec.tax_id, rec.tax_rate, rec.tax_amount, rec.uom_code,
3805 ROUND(NVL(tax_amt_tab(row_count), 0), REC.ROUNDING_FACTOR),
3806 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)),
3807 ROUND(NVL(func_tax_amt_tab(row_count),0), REC.ROUNDING_FACTOR) * v_currency_conv_factor, --Modified by Bo Li for Bug#9780751 on 11-JUN-2010
3808 p_creation_date, p_created_by, p_last_update_date,
3809 p_last_updated_by, p_last_update_login
3813
3810 );
3811
3812 else
3814 INSERT INTO JAI_AR_TRX_TAX_LINES(
3815 customer_trx_line_id, link_to_cust_trx_line_id, tax_line_no,
3816 precedence_1, precedence_2, precedence_3, precedence_4, precedence_5,
3817 precedence_6, precedence_7, precedence_8, precedence_9, precedence_10, -- Date 31/10/2006 Bug 5228046 added by SACSETHI
3818 tax_id, tax_rate, qty_rate, uom,
3819 tax_amount,
3820 base_tax_amount,
3821 func_tax_amount,
3822 creation_date, created_by, last_update_date,
3823 last_updated_by, last_update_login
3824 ) VALUES(
3825 ra_customer_trx_lines_s.NEXTVAL, p_line_id, row_count,
3826 rec.p_1, rec.p_2, rec.p_3, rec.p_4, rec.p_5,
3827 rec.p_6, rec.p_7, rec.p_8, rec.p_9, rec.p_10,
3828 rec.tax_id, rec.tax_rate, rec.tax_amount, rec.uom_code,
3829 ROUND(NVL(tax_amt_tab(row_count), 0), REC.ROUNDING_FACTOR),
3830 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)),
3831 ROUND(NVL(func_tax_amt_tab(row_count),0), REC.ROUNDING_FACTOR) * v_currency_conv_factor, --Modified by Bo Li for Bug#9780751 on 11-JUN-2010D:\Workplace\Source\BUG\bug9780751\jai_cmn_tax_dflt.plb
3832 p_creation_date, p_created_by, p_last_update_date,
3833 p_last_updated_by, p_last_update_login
3834 );
3835
3836 end if;
3837
3838 /*Bug 8371741 - End*/
3839
3840 /*
3841 end if;
3842 else
3843 INSERT INTO JAI_AR_TRX_TAX_LINES(customer_trx_line_id,
3844 link_to_cust_trx_line_id,
3845 tax_line_no,
3846 precedence_1,precedence_2, precedence_3, precedence_4,precedence_5,
3847 precedence_6,precedence_7, precedence_8, precedence_9,precedence_10, -- Date 31/10/2006 Bug 5228046 added by SACSETHI
3848 tax_id, tax_rate, qty_rate, uom,
3849 tax_amount,
3850 base_tax_amount,
3851 func_tax_amount,
3852 creation_date, created_by, last_update_date,
3853 last_updated_by, last_update_login)
3854 VALUES(ra_customer_trx_lines_s.nextval, p_line_id, row_count,
3855 rec.p_1, rec.p_2, rec.p_3, rec.p_4, rec.p_5,
3856 rec.p_6, rec.p_7, rec.p_8, rec.p_9, rec.p_10,
3857 rec.tax_id, rec.tax_rate, rec.tax_amount, rec.uom_code,
3858 ROUND(nvl(tax_amt_tab(row_count), 0), REC.ROUNDING_FACTOR),
3859 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)),
3860 (nvl(func_tax_amt_tab(row_count),0) * v_currency_conv_factor),
3861 p_creation_date, p_created_by, p_last_update_date,
3862 p_last_updated_by, p_last_update_login);
3863 end if;
3864 */
3865
3866 ELSIF transaction_name = 'AR_LINES_UPDATE' THEN
3867
3868 --2001/03/30 Manohar Mishra
3869 -- Added the following IF condition
3870 --if ((v_register_code<>'BOND_REG') and (rec.tax_type_val<>1)) then
3871 UPDATE JAI_AR_TRX_TAX_LINES
3872 SET tax_amount = ROUND(NVL(tax_amt_tab(row_count), 0), REC.ROUNDING_FACTOR),
3873 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)),
3874 func_tax_amount = NVL(func_tax_amt_tab(row_count),0) * v_currency_conv_factor,
3875 last_update_date = p_last_update_date,
3876 last_updated_by = p_last_updated_by,
3877 last_update_login = p_last_update_login
3878 WHERE link_to_cust_trx_line_id = P_line_id
3879 AND tax_line_no = row_count;
3880
3881 --end if;
3882
3883 ELSIF SUBSTR( transaction_name, 1, 3 ) = 'RFQ' OR
3884 SUBSTR( transaction_name, 1, 9 ) = 'QUOTATION' OR
3885 SUBSTR( transaction_name, 1, 7 ) = 'BLANKET' OR
3886 SUBSTR( transaction_name, 1, 8 ) = 'BLANKETR' OR
3887 SUBSTR( transaction_name, 1, 6 ) = 'OTHERS' OR
3888 SUBSTR( transaction_name, 1, 9 ) = 'SCHEDULED' THEN
3889
3890 /*
3891 Since there is no provision of line location id as one of the parameter,
3892 Line location Id is passed in place of line id, Line Id is concatinated
3893 to transaction name.
3894 If proportioning has to be done, then operation variable takes value U
3895 else takes I. This is also concatenated with transaction name with a preceding $.
3896 */
3897
3898 BEGIN -- MBEGIN
3899
3900 DECLARE -- NDECLARE
3901
3902 v_seq_val NUMBER;
3903 v_modvat_flag VARCHAR2(1);
3904 v_vendor_id NUMBER;
3905 v_vendor1_id NUMBER;
3906 v_vendor2_id NUMBER;
3907 v_currency VARCHAR2(15);
3908 v_transaction_name VARCHAR2(100);
3909 v_mod_cr NUMBER;
3910 v_tax_type VARCHAR2(30);
3911 v_start NUMBER;
3912 v_line_id NUMBER;
3913 operation VARCHAR2(2);
3914
3915 CURSOR fetch_mod_cr_cur( taxid IN NUMBER ) IS
3916 SELECT Tax_Type, Mod_Cr_Percentage, Vendor_Id
3917 FROM JAI_CMN_TAXES_ALL
3918 WHERE Tax_Id = taxid;
3919
3920 CURSOR fetch_vendor2_cur IS
3921 SELECT vendor_id
3922 FROM JAI_PO_REQ_LINE_TAXES
3926
3923 WHERE Requisition_Line_Id = ( SELECT Requisition_Line_Id
3924 FROM Po_Requisition_Lines_All
3925 WHERE Line_Location_Id = p_line_id );
3927 CURSOR fetch_focus_id IS
3928 SELECT Line_Focus_Id
3929 FROM JAI_PO_LINE_LOCATIONS
3930 WHERE Po_Line_Id = p_line_id
3931 AND Po_Header_Id = p_header_id
3932 AND Line_Location_Id IS NULL;
3933
3934 CURSOR fetch_focus1_id( line_id IN NUMBER ) IS
3935 SELECT Line_Focus_Id
3936 FROM JAI_PO_LINE_LOCATIONS
3937 WHERE Po_Line_Id = p_line_id
3938 AND Po_Header_Id = p_header_id
3939 AND Line_Location_Id = line_id;
3940
3941 BEGIN -- NBEGIN
3942
3943 IF SUBSTR( transaction_name, 1, 1 ) = 'R' THEN
3944 v_transaction_name := 'RFQ';
3945 v_start := 4;
3946 ELSIF SUBSTR( transaction_name, 1, 1 ) = 'S' THEN
3947 v_transaction_name := 'RFQ';
3948 v_start := 10;
3949 ELSIF SUBSTR( transaction_name, 1, 1 ) = 'Q' THEN
3950 v_transaction_name := 'QUOTATION';
3951 v_start := 10;
3952 ELSIF SUBSTR( transaction_name, 1, 1 ) = 'B' AND SUBSTR( transaction_name, 1, 8 ) <> 'BLANKETR' THEN
3953 v_transaction_name := 'BLANKET';
3954 v_start := 8;
3955 ELSIF SUBSTR( transaction_name, 1, 8 ) = 'BLANKETR' THEN
3956 v_transaction_name := 'RFQ';
3957 v_start := 9;
3958 ELSIF SUBSTR( transaction_name, 1, 1 ) = 'O' THEN
3959 v_transaction_name := 'OTHERS';
3960 v_start := 7;
3961 END IF;
3962
3963 operation := SUBSTR( transaction_name, INSTR(transaction_name, '$' )+1, 1 );
3964 v_line_id := TO_NUMBER( SUBSTR( transaction_name, v_start, LENGTH( transaction_name )-( v_start + 1 )));
3965
3966 IF NVL( v_line_id, 0 ) = 0 THEN
3967
3968 OPEN Fetch_Focus_Id;
3969 FETCH Fetch_Focus_Id INTO v_seq_val;
3970 CLOSE Fetch_Focus_Id;
3971
3972 v_line_focus_id_holder := v_seq_val; -- cbabu for EnhancementBug# 2427465
3973
3974 ELSE
3975 OPEN Fetch_Focus1_Id( v_line_id );
3976 FETCH Fetch_Focus1_Id INTO v_seq_val;
3977 CLOSE Fetch_Focus1_Id;
3978
3979 v_line_focus_id_holder := v_seq_val; -- cbabu for EnhancementBug# 2427465
3980
3981 END IF;
3982
3983 OPEN Fetch_Mod_Cr_Cur( rec.tax_id );
3984 FETCH Fetch_Mod_Cr_Cur INTO v_tax_type, v_mod_cr, v_vendor1_id;
3985 CLOSE Fetch_Mod_Cr_Cur;
3986
3987 IF rec.mod_cr_percentage IS NOT NULL AND rec.mod_cr_percentage > 0 THEN
3988 v_modvat_flag := 'Y';
3989 ELSIF rec.mod_cr_percentage IS NULL THEN
3990 v_modvat_flag := 'N';
3991 END IF;
3992
3993 IF v_transaction_name IN ( 'OTHERS', 'QUOTATION', 'BLANKET' ) THEN
3994 IF upper(rec.tax_type) IN ( 'CVD',
3995 jai_constants.tax_type_add_cvd , -- Date 31/10/2006 Bug 5228046 added by SACSETHI
3996 'CUSTOMS' ,
3997 JAI_CONSTANTS.TAX_TYPE_SH_CVD_EDU_CESS,/*bduvarag for the bug#5989740*/
3998 jai_constants.tax_type_cvd_edu_cess ,
3999 JAI_CONSTANTS.TAX_TYPE_SH_CUSTOMS_EDU_CESS,/*bduvarag for the bug#5989740*/
4000 jai_constants.tax_type_customs_edu_cess
4001 ) THEN
4002 v_vendor_id := NULL;
4003 ELSIF UPPER( rec.tax_type ) LIKE UPPER( '%EXCISE%' ) THEN
4004 v_vendor_id := p_vendor_id;
4005 ELSE
4006 v_vendor_id := NVL( v_vendor1_id, p_vendor_id );
4007 END IF;
4008 END IF;
4009
4010 IF rec.tax_type = 'TDS' THEN
4011 v_vendor_id := v_vendor1_id;
4012 END IF;
4013
4014 IF operation = 'I' THEN
4015 IF p_operation_flag <> -1 THEN
4016 INSERT INTO JAI_PO_TAXES(
4017 line_focus_id, line_location_id, po_line_id, po_header_id,
4018 tax_line_no,
4019 precedence_1, precedence_2, precedence_3, precedence_4, precedence_5,
4020 precedence_6, precedence_7, precedence_8, precedence_9, precedence_10, -- Date 31/10/2006 Bug 5228046 added by SACSETHI
4021 tax_id, tax_type, tax_rate, qty_rate, uom, tax_amount, tax_target_amount,
4022 currency, modvat_flag, vendor_id,
4023 creation_date, created_by,
4024 last_update_date, last_updated_by, last_update_login,
4025 tax_category_id -- cbabu for EnhancementBug# 2427465
4026 ) VALUES (
4027 v_seq_val, v_line_id, p_line_id, p_header_id,
4028 row_count,
4029 rec.p_1, rec.p_2,rec.p_3, rec.p_4, rec.p_5,
4030 rec.p_6, rec.p_7,rec.p_8, rec.p_9, rec.p_10,
4031 rec.tax_id, rec.tax_type, rec.tax_rate, rec.tax_amount, rec.uom_code,
4032 ROUND(NVL(tax_amt_tab(row_count),0), REC.ROUNDING_FACTOR),
4036 p_creation_date, p_created_by,
4033 DECODE(NVL(base_tax_amt_tab(row_count), 0), 0, NVL(tax_amt_tab(row_count),0),
4034 NVL(base_tax_amt_tab(row_count), 0)) * p_currency_conv_factor ,
4035 p_currency, v_modvat_flag, v_vendor_id,
4037 p_last_update_date, p_last_updated_by, p_last_update_login,
4038 p_tax_category_id -- cbabu for EnhancementBug# 2427465
4039 );
4040
4041 --Added by Kevin Cheng for Retroactive Price 2008/01/13
4042 --=====================================================
4043 IF pv_retroprice_changed = 'Y'
4044 THEN
4045 JAI_RETRO_PRC_PKG.Update_Price_Changes( pn_tax_amt => ROUND(NVL(tax_amt_tab(row_count),0), REC.ROUNDING_FACTOR)
4046 , pn_line_no => row_count
4047 , pn_line_loc_id => v_line_id
4048 , pv_process_flag => lv_process_flag
4049 , pv_process_message => lv_process_message
4050 );
4051
4052 IF lv_process_flag IN ('EE', 'UE')
4053 THEN
4054 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
4055 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG','JAI_CMN_TAX_DEFAULTATION_PKG.JA_IN_CALC_PREC_TAXES.Err:'||lv_process_message);
4056 app_exception.raise_exception;
4057 END IF;
4058 END IF;
4059 --=====================================================
4060
4061 ELSE
4062
4063 INSERT INTO JAI_PO_TAXES(
4064 Line_Focus_Id, Line_Location_Id, Po_Line_Id, Po_Header_Id,
4065 Tax_Line_No,
4066 Precedence_1, Precedence_2, Precedence_3, Precedence_4, Precedence_5,
4067 Precedence_6, Precedence_7, Precedence_8, Precedence_9, Precedence_10, -- Date 31/10/2006 Bug 5228046 added by SACSETHI
4068 Tax_Id, Tax_Type, Tax_Rate, Qty_Rate, UOM, Tax_Amount,
4069 Tax_Target_Amount,
4070 Currency, Modvat_Flag, Vendor_Id,
4071 Creation_Date, Created_By,
4072 Last_Update_Date, Last_Updated_By, Last_Update_Login,
4073 tax_category_id -- cbabu for EnhancementBug# 2427465
4074 ) VALUES (
4075 v_seq_val, NULL, p_line_id, p_header_id,
4076 row_count,
4077 rec.p_1, rec.p_2, rec.p_3, rec.p_4, rec.p_5,
4078 rec.p_6, rec.p_7, rec.p_8, rec.p_9, rec.p_10,
4079 rec.tax_id, rec.tax_type, rec.tax_rate, rec.tax_amount, rec.uom_code, NULL,
4080 DECODE( NVL(base_tax_amt_tab(row_count), 0), 0, NVL(tax_amt_tab(row_count),0),
4081 NVL(base_tax_amt_tab(row_count), 0)) * p_currency_conv_factor ,
4082 p_currency, v_modvat_flag, v_vendor_id,
4083 p_creation_date, p_created_by,
4084 p_last_update_date, p_last_updated_by, p_last_update_login,
4085 p_tax_category_id -- cbabu for EnhancementBug# 2427465
4086 );
4087 END IF;
4088
4089 ELSIF operation = 'U' THEN
4090
4091 IF v_line_id IS NOT NULL THEN
4092 UPDATE JAI_PO_TAXES
4093 SET Tax_Amount = ROUND(NVL( tax_amt_tab(row_count), 0 ),REC.ROUNDING_FACTOR),
4094 tax_target_amount = DECODE(NVL(base_tax_amt_tab(row_count), 0), 0, NVL(tax_amt_tab(row_count),0),
4095 NVL(base_tax_amt_tab(row_count), 0)) * p_currency_conv_factor ,
4096 last_updated_by = p_last_updated_by,
4097 last_update_date = p_last_update_date,
4098 last_update_login = p_last_update_login
4099 WHERE po_line_id = p_line_id
4100 AND line_location_id = v_line_id;
4101 ELSE
4102 UPDATE JAI_PO_TAXES
4103 SET Tax_Amount = ROUND(NVL( tax_amt_tab(row_count), 0 ), REC.ROUNDING_FACTOR),
4104 tax_target_amount = DECODE(NVL(base_tax_amt_tab(row_count), 0), 0, NVL(tax_amt_tab(row_count),0),
4105 NVL(base_tax_amt_tab(row_count), 0)) * p_currency_conv_factor ,
4106 last_updated_by = p_last_updated_by,
4107 last_update_date = p_last_update_date,
4108 last_update_login = p_last_update_login
4109 WHERE Po_Line_Id = p_line_id
4110 AND Line_Location_Id IS NULL;
4111 END IF;
4112
4113 END IF;
4114 v_vendor_id := NULL;
4115
4116 END; -- NDECLARE, NBEGIN
4117
4118 END; -- MBEGIN -- transaction_name = RFQ/QUOTATION/PURCHASE ORDER/RELEASES
4119
4120 ELSIF transaction_name = 'PO_REQN' THEN
4121
4122 BEGIN
4123
4124 DECLARE
4125
4126 v_modvat_flag VARCHAR2(1);
4127 v_vendor_id NUMBER;
4128 v_currency VARCHAR2(15);
4129
4130 BEGIN
4131
4132 IF rec.mod_cr_percentage IS NOT NULL AND rec.mod_cr_percentage > 0 THEN
4133 v_modvat_flag := 'Y';
4137 IF rec.tax_type = 'TDS' THEN
4134 ELSIF rec.mod_cr_percentage IS NULL THEN
4135 v_modvat_flag := 'N';
4136 END IF;
4138 v_vendor_id := rec.vendor_id;
4139 ELSIF UPPER(rec.tax_type) IN ('CVD',
4140 jai_constants.tax_type_add_cvd , -- Date 31/10/2006 Bug 5228046 added by SACSETHI
4141 'CUSTOMS',
4142 JAI_CONSTANTS.TAX_TYPE_SH_CVD_EDU_CESS,/*bduvarag for the bug#5989740*/
4143 jai_constants.tax_type_cvd_edu_cess ,
4144 JAI_CONSTANTS.TAX_TYPE_SH_CUSTOMS_EDU_CESS,/*bduvarag for the bug#5989740*/
4145 jai_constants.tax_type_customs_edu_cess
4146 ) THEN
4147 v_vendor_id := NULL;
4148 ELSIF UPPER( rec.tax_type ) LIKE '%EXCISE%' THEN
4149 v_vendor_id := p_vendor_id;
4150 ELSE
4151 v_vendor_id := NVL( rec.vendor_id, p_vendor_id );
4152 END IF;
4153
4154 IF p_currency IS NOT NULL THEN
4155 v_currency := p_currency;
4156 END IF;
4157 IF NVL( p_operation_flag, 0 ) < 0 AND ( UPPER( rec.tax_type ) LIKE '%EXCISE%' OR UPPER(rec.tax_type)
4158 NOT IN ( 'CVD',
4159 jai_constants.tax_type_add_cvd, -- Date 31/10/2006 Bug 5228046 added by SACSETHI
4160 'CUSTOMS',
4161 JAI_CONSTANTS.TAX_TYPE_SH_CVD_EDU_CESS,/*bduvarag for the bug#5989740*/
4162 jai_constants.tax_type_cvd_edu_cess ,
4163 JAI_CONSTANTS.TAX_TYPE_SH_CUSTOMS_EDU_CESS,/*bduvarag for the bug#5989740*/
4164 jai_constants.tax_type_customs_edu_cess
4165 ) )
4166 THEN /* Indiactes an Internal Requisition */
4167 v_vendor_id := p_operation_flag;
4168 END IF;
4169
4170 INSERT INTO JAI_PO_REQ_LINE_TAXES(
4171 requisition_line_id, requisition_header_id, tax_line_no,
4172 precedence_1, precedence_2, precedence_3, precedence_4, precedence_5,
4173 precedence_6, precedence_7, precedence_8, precedence_9, precedence_10, -- Date 31/10/2006 Bug 5228046 added by SACSETHI
4174 tax_id, tax_rate, qty_rate, uom, tax_amount, Tax_Target_Amount,
4175 tax_type, modvat_flag, vendor_id, currency,
4176 creation_date, created_by, last_update_date,
4177 last_updated_by, last_update_login,
4178 tax_category_id -- cbabu for EnhancementBug# 2427465
4179 ) VALUES (
4180 p_line_id, p_header_id, row_count,
4181 rec.p_1, rec.p_2, rec.p_3,rec.p_4, rec.p_5,
4182 rec.p_6, rec.p_7, rec.p_8,rec.p_9, rec.p_10,
4183 rec.tax_id, rec.tax_rate, rec.tax_amount, rec.uom_code,
4184 ROUND( NVL(tax_amt_tab(row_count),0), rec.rounding_factor), -- v_currency_conv_factor ),
4185 NVL( base_tax_amt_tab(row_count), 0) * ( v_currency_conv_factor), rec.tax_type, v_modvat_flag, v_vendor_id, v_currency,
4186 p_creation_date, p_created_by, p_last_update_date,
4187 p_last_updated_by, p_last_update_login,
4188 p_tax_category_id -- cbabu for EnhancementBug# 2427465
4189 );
4190
4191 END;
4192
4193 END;
4194
4195
4196
4197
4198 /* bgowrava for forward porting bug#5631784 */
4199 /* Added OR condition as we are passing p_action as null in case of 'CASH' Receipt. by JMEENA */
4200 elsif (p_source_trx_type in (
4201 jai_constants.pa_draft_invoice /* 6012570 (5876390) */
4202 , jai_constants.G_AP_STANDALONE_INVOICE
4203 -- Added by Jason Liu on 2007/08/23
4204 )
4205 and p_action = jai_constants.default_taxes )
4206 OR (p_source_trx_type = jai_constants.ar_cash)
4207 then
4208 -- Added by Jason Liu for standalone invoice on 2007/08/23
4209 ----------------------------------------------------------------------
4210 IF p_source_trx_type = jai_constants.G_AP_STANDALONE_INVOICE
4211 AND rec.tax_type IN ( jai_constants.tax_type_value_added
4212 , jai_constants.tax_type_sales
4213 , jai_constants.tax_type_cst
4214 , jai_constants.tax_type_other)
4215 THEN
4216 v_modvat_flag := 'N';
4217 END IF; --p_source_trx_type = jai_constants.AP_STANDALONE_INVOICE
4218 ----------------------------------------------------------------------
4219
4220 /*
4221 || When currency conversion rate is null it means the transaction is in the INR only
4222 */
4223 if v_currency_conv_factor is null then
4224 v_currency_conv_factor := 1;
4225 end if;
4226
4227 /* jai_cmn_debug_contexts_pkg.print
4228 (ln_reg_id
4229 ,'Values before insert into jai_cmn_document_taxes' ||chr(10)
4230 || ',tax_line_no -> '||row_count ||CHR(10)
4231 || ',tax_id -> '||rec.tax_id ||CHR(10)
4232 || ',tax_type -> '||rec.tax_type ||CHR(10)
4233 || ',currency_code -> '||p_currency ||CHR(10)
4234 || ',tax_rate -> '||rec.tax_rate ||CHR(10)
4235 || ',qty_rate -> '||rec.tax_amount ||CHR(10)
4239 || ',modvat_flag -> '||v_modvat_flag ||CHR(10)
4236 || ',uom -> '||rec.uom_code ||CHR(10)
4237 || ',tax_amt -> '||round( nvl(tax_amt_tab(row_count),0) , rec.rounding_factor ) ||CHR(10)
4238 || ',func_tax_amt -> '||nvl(func_tax_amt_tab(row_count),0) * v_currency_conv_factor ||CHR(10)
4240 || ',adhoc_flag -> '||rec.adhoc_flag ||CHR(10)
4241 || ',tax_category_id -> '||rec.tax_category_id ||CHR(10)
4242 || ',source_doc_type -> '||p_source_trx_type ||CHR(10)
4243 || ',source_doc_id -> '||p_header_id ||CHR(10)
4244 || ',source_doc_line_id -> '||p_line_id ||CHR(10)
4245 || ',source_table_name -> '||p_source_table_name ||CHR(10)
4246 || ',tax_modified_by -> '||jai_constants.tax_modified_by_system ||CHR(10)
4247 || ',precedence_1 -> '||rec.p_1 ||CHR(10)
4248 || ',precedence_2 -> '||rec.p_2 ||CHR(10)
4249 || ',precedence_3 -> '||rec.p_3 ||CHR(10)
4250 || ',precedence_4 -> '||rec.p_4 ||CHR(10)
4251 || ',precedence_5 -> '||rec.p_5 ||CHR(10)
4252 || ',precedence_6 -> '||rec.p_6 ||CHR(10)
4253 || ',precedence_7 -> '||rec.p_7 ||CHR(10)
4254 || ',precedence_8 -> '||rec.p_8 ||CHR(10)
4255 || ',precedence_9 -> '||rec.p_9 ||CHR(10)
4256 || ',precedence_10 -> '||rec.p_10 ||CHR(10)
4257 || ',creation_date -> '||p_creation_date ||CHR(10)
4258 || ',created_by -> '||p_created_by ||CHR(10)
4259 || ',last_update_date -> '||p_last_update_date ||CHR(10)
4260 || ',last_updated_by -> '||p_last_updated_by ||CHR(10)
4261 || ',last_update_login -> '||p_last_update_login ||CHR(10)
4262 ); */ --commented by bgowrava for bug#5631784
4263 -- Added by Eric Ma for standalone invoice on 2007/09/27
4264 ----------------------------------------------------------------------
4265 IF (p_source_trx_type = jai_constants.G_AP_STANDALONE_INVOICE )
4266 THEN
4267 INSERT INTO jai_cmn_document_taxes
4268 ( doc_tax_id
4269 , tax_line_no
4270 , tax_id
4271 , tax_type
4272 , currency_code
4273 , tax_rate
4274 , qty_rate
4275 , uom
4276 , tax_amt
4277 , func_tax_amt
4278 , modvat_flag
4279 , adhoc_flag
4280 , tax_category_id
4281 , source_doc_type
4282 , source_doc_id
4283 , source_doc_line_id
4284 , source_doc_parent_line_no --added by Eric Ma,Sep 27,2007
4285 , source_table_name
4286 , tax_modified_by
4287 , precedence_1
4288 , precedence_2
4289 , precedence_3
4290 , precedence_4
4291 , precedence_5
4292 , precedence_6
4293 , precedence_7
4294 , precedence_8
4295 , precedence_9
4296 , precedence_10
4297 , creation_date
4298 , created_by
4299 , last_update_date
4300 , last_updated_by
4301 , last_update_login
4302 )
4303 VALUES
4304 ( jai_cmn_document_taxes_s.nextval -- doc_tax_id
4305 , row_count -- tax_line_no
4306 , rec.tax_id -- tax_id
4307 , rec.tax_type -- tax_type
4308 , p_currency -- currency
4309 , rec.tax_rate -- tax_rate
4310 , rec.tax_amount -- qty_rate
4311 , rec.uom_code -- uom
4312 , round( nvl(tax_amt_tab(row_count),0) -- tax_amount
4313 , rec.rounding_factor
4314 )
4315 , nvl(func_tax_amt_tab(row_count),0)
4316 * v_currency_conv_factor -- func_tax_amount
4317 , v_modvat_flag -- modvat_flag
4318 , rec.adhoc_flag -- adhoc_flag
4319 , rec.tax_category_id -- tax_category_id
4320 , p_source_trx_type -- source_doc_type
4321 , p_header_id -- source_doc_id
4322 , p_line_id -- source_doc_line_id
4323 , p_line_id -- source_doc_parent_line_no,added by Eric Ma
4327 , rec.p_1 -- precedence_1
4324 , p_source_table_name -- source_table_name
4325 , jai_constants.tax_modified_by_system
4326 --tax_modified_by(SYSTEM=system defaulted, MANUAL=User Modified)
4328 , rec.p_2 -- precedence_2
4329 , rec.p_3 -- precedence_3
4330 , rec.p_4 -- precedence_4
4331 , rec.p_5 -- precedence_5
4332 , rec.p_6 -- precedence_6
4333 , rec.p_7 -- precedence_7
4334 , rec.p_8 -- precedence_8
4335 , rec.p_9 -- precedence_9
4336 , rec.p_10 -- precedence_10
4337 , p_creation_date -- creation_date
4338 , p_created_by -- created_by
4339 , p_last_update_date -- last_update_date
4340 , p_last_updated_by -- last_updated_by
4341 , p_last_update_login -- last_update_login
4342 );
4343 ELSE --(p_source_trx_type <>jai_constants.G_AP_STANDALONE_INVOICE );
4344 ------------------------------------------------------------------
4345 insert into jai_cmn_document_taxes
4346 ( doc_tax_id
4347 , tax_line_no
4348 , tax_id
4349 , tax_type
4350 , currency_code
4351 , tax_rate
4352 , qty_rate
4353 , uom
4354 , tax_amt
4355 , func_tax_amt
4356 , modvat_flag
4357 , adhoc_flag
4358 , tax_category_id
4359 , source_doc_type
4360 , source_doc_id
4361 , source_doc_line_id
4362 , source_table_name
4363 , tax_modified_by
4364 , precedence_1
4365 , precedence_2
4366 , precedence_3
4367 , precedence_4
4368 , precedence_5
4369 , precedence_6
4370 , precedence_7
4371 , precedence_8
4372 , precedence_9
4373 , precedence_10
4374 , creation_date
4375 , created_by
4376 , last_update_date
4377 , last_updated_by
4378 , last_update_login
4379 )
4380 values
4381 (
4382 jai_cmn_document_taxes_s.nextval -- doc_tax_id
4383 , row_count -- tax_line_no
4384 , rec.tax_id -- tax_id
4385 , rec.tax_type -- tax_type
4386 , p_currency -- currency
4387 , rec.tax_rate -- tax_rate
4388 , rec.tax_amount -- qty_rate
4389 , rec.uom_code -- uom
4390 , round( nvl(tax_amt_tab(row_count),0) -- tax_amount
4391 , rec.rounding_factor
4392 )
4393 /* , nvl(func_tax_amt_tab(row_count),0)
4394 * v_currency_conv_factor*/
4395 , round(round(nvl(func_tax_amt_tab(row_count),0)
4396 ,rec.rounding_factor) * v_currency_conv_factor,
4397 rec.rounding_factor) -- func_tax_amount
4398 --Modified by Qinglei on 16-Jan-2012 for bug#13547239
4399 , v_modvat_flag -- modvat_flag
4400 , rec.adhoc_flag -- adhoc_flag
4401 , rec.tax_category_id -- tax_category_id
4402 , p_source_trx_type -- source_doc_type
4403 , p_header_id -- source_doc_id
4404 , p_line_id -- source_doc_line_id
4405 , p_source_table_name -- source_table_name
4406 , jai_constants.tax_modified_by_system -- tax_modified_by (SYSTEM=system defaulted, MANUAL=User Modified)
4407 , rec.p_1 -- precedence_1
4408 , rec.p_2 -- precedence_2
4409 , rec.p_3 -- precedence_3
4410 , rec.p_4 -- precedence_4
4411 , rec.p_5 -- precedence_5
4412 , rec.p_6 -- precedence_6
4413 , rec.p_7 -- precedence_7
4414 , rec.p_8 -- precedence_8
4415 , rec.p_9 -- precedence_9
4416 , rec.p_10 -- precedence_10
4420 , p_last_updated_by -- last_updated_by
4417 , p_creation_date -- creation_date
4418 , p_created_by -- created_by
4419 , p_last_update_date -- last_update_date
4421 , p_last_update_login -- last_update_login
4422 );
4423 END IF; --(p_source_trx_type = jai_constants.G_AP_STANDALONE_INVOICE )
4424 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Record inserted into jai_cmn_document_taxes');*/ --commented by bgowrava for bug#5631784
4425
4426 /* Date 22-feb-2007 Added by SACSETHI for bug 6012570 (5876390)
4427 in This , Recalculation will be happen in Draft invoice */
4428 elsif
4429 p_source_trx_type = jai_constants.pa_draft_invoice
4430 and p_action = jai_constants.recalculate_taxes
4431 then
4432
4433 update jai_cmn_document_taxes
4434 set tax_amt = tax_amt_tab(row_count)
4435 ,func_tax_amt = nvl(func_tax_amt_tab(row_count),0) * nvl(v_currency_conv_factor ,1)
4436 ,last_update_date = p_last_update_date
4437 ,last_updated_by = p_last_updated_by
4438 ,last_update_login = p_last_update_login
4439 where source_doc_line_id = p_line_id
4440 and tax_id = rec.tax_id
4441 and source_doc_type = jai_constants.pa_draft_invoice;
4442
4443 elsif p_source_trx_type = jai_constants.source_ttype_delivery
4444 then
4445
4446 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id
4447 ,'Value of variables used for updating ja_in_so_picking_tax_lines'||chr(10)
4448 ||'tax_amount = '|| tax_amt_tab(row_count) ||chr(10)
4449 ||'func_tax_amount = '||nvl(func_tax_amt_tab(row_count),0) * v_currency_conv_factor ||chr(10)
4450 ||'base_tax_amount = '||round( nvl(tax_amt_tab(row_count),0), rec.rounding_factor) ||chr(10)
4451 ||'tax_id = '||rec.tax_id
4452 ); */ --commented by bgowrava for bug#5631784
4453
4454 if p_action = jai_constants.recalculate_taxes then
4455 --Used base_tax_amt_tab instead of tax_amt_tab to update column base_tax_amount for bug#8905076 by JMEENA
4456 update JAI_OM_WSH_LINE_TAXES
4457 set tax_amount = tax_amt_tab(row_count)
4458 ,func_tax_amount = nvl(func_tax_amt_tab(row_count),0) * nvl(v_currency_conv_factor ,1)
4459 ,base_tax_amount = round( nvl(base_tax_amt_tab(row_count),0), rec.rounding_factor)
4460 ,last_update_date = p_last_update_date
4461 ,last_updated_by = p_last_updated_by
4462 ,last_update_login = p_last_update_login
4463 where delivery_detail_id = p_line_id
4464 and tax_id = rec.tax_id;
4465
4466 end if;
4467
4468 --Added by Zhiwei Hou for Bug#13359892 DTC enhancement on 20111128 begin
4469 --------------------------------------------------------------------------------
4470 elsif
4471 p_source_trx_type = 'TDS INVOICE'--jai_constants.pa_draft_invoice
4472 and p_action = jai_constants.recalculate_taxes
4473 then
4474
4475 if v_currency_conv_factor is null then
4476 v_currency_conv_factor := 1;
4477 end if;
4478
4479
4480 update JAI_AP_TDS_INV_TAXES
4481 set TAX_AMOUNT = tax_amt_tab(row_count)
4482 ,calc_tax_amount = tax_amt_tab(row_count)
4483 ,base_tax_amount = round(nvl(tax_amt_tab(row_count),0)/nvl(v_currency_conv_factor ,1),2) --Added By zhiwei hou for Bug#13787158 on 20120305
4484 ,func_tax_amount = tax_amt_tab(row_count) --Added By zhiwei hou for Bug#13787158 on 20120305
4485 --,func_tax_amount = nvl(func_tax_amt_tab(row_count),0) * nvl(v_currency_conv_factor ,1) --Commented By zhiwei hou for Bug#13787158 on 20120305
4486 ,last_update_date = p_last_update_date
4487 ,last_updated_by = p_last_updated_by
4488 ,last_update_login = p_last_update_login
4489 where invoice_id = p_header_id
4490 and invoice_distribution_id = p_line_id
4491 and actual_tax_id = rec.tax_id;
4492
4493
4494 --------------------------------------------------------------------------------
4495 --Added by Zhiwei Hou for Bug#13359892 DTC enhancement on 20111128 end
4496
4497 elsif p_source_trx_type = jai_constants.bill_only_invoice then
4498
4499 /*
4500 || When currency conversion rate is null it means the transaction is in the INR only
4501 */
4502 if v_currency_conv_factor is null then
4503 v_currency_conv_factor := 1;
4504 end if;
4505
4506 /* jai_cmn_debug_contexts_pkg.print (ln_reg_id
4507 ,'Value of variables used for updating JA_IN_RA_CUST_TRX_TAX_LINES'||chr(10)
4508 ||'tax_amount = '|| tax_amt_tab(row_count) ||chr(10)
4509 ||'func_tax_amount = '||nvl(func_tax_amt_tab(row_count),0) * v_currency_conv_factor ||chr(10)
4510 ||'base_tax_amount = '||round( nvl(tax_amt_tab(row_count),0), rec.rounding_factor) ||chr(10)
4511 ||'tax_id = '||rec.tax_id
4512 ); */ --commented by bgowrava for bug#5631784
4513
4514 if p_action = jai_constants.recalculate_taxes then
4515
4519 ,base_tax_amount = round( nvl(tax_amt_tab(row_count),0), rec.rounding_factor)
4516 update JAI_AR_TRX_TAX_LINES
4517 set tax_amount = tax_amt_tab(row_count)
4518 ,func_tax_amount = nvl(func_tax_amt_tab(row_count),0) * nvl(v_currency_conv_factor ,1)
4520 ,last_update_date = p_last_update_date
4521 ,last_updated_by = p_last_updated_by
4522 ,last_update_login = p_last_update_login
4523 where link_to_cust_trx_line_id = p_line_id
4524 and tax_id = rec.tax_id;
4525
4526 end if;
4527
4528 /** End Bug 5631784 */
4529
4530
4531
4532 END IF; -- p_transaction_type
4533 row_count := row_count + 1;
4534
4535 END LOOP;
4536
4537 -- Start, cbabu for EnhancementBug# 2427465
4538 IF SUBSTR( transaction_name, 1, 3 ) = 'RFQ' OR
4539 SUBSTR( transaction_name, 1, 9 ) = 'QUOTATION' OR
4540 SUBSTR( transaction_name, 1, 7 ) = 'BLANKET' OR
4541 SUBSTR( transaction_name, 1, 8 ) = 'BLANKETR' OR
4542 SUBSTR( transaction_name, 1, 6 ) = 'OTHERS' OR
4543 SUBSTR( transaction_name, 1, 9 ) = 'SCHEDULED'
4544 THEN
4545
4546 BEGIN
4547 UPDATE JAI_PO_LINE_LOCATIONS
4548 SET tax_category_id = p_tax_category_id
4549 WHERE line_focus_id = v_line_focus_id_holder;
4550 EXCEPTION
4551 WHEN OTHERS THEN
4552 RAISE_APPLICATION_ERROR( -20101, '1 Exception raised in jai_cmn_tax_defaultation_pkg:JIPLL '||SQLERRM, TRUE);
4553 END;
4554
4555 ELSIF transaction_name = 'PO_REQN' THEN
4556
4557 BEGIN
4558 UPDATE JAI_PO_REQ_LINES
4559 SET tax_category_id = p_tax_category_id
4560 WHERE requisition_line_id = p_line_id;
4561 EXCEPTION
4562 WHEN OTHERS THEN
4563 RAISE_APPLICATION_ERROR( -20101, '2 Exception raised in jai_cmn_tax_defaultation_pkg:JIRL '||SQLERRM, TRUE);
4564 END;
4565
4566
4567 /* End, cbabu for EnhancementBug# 2427465 */
4568
4569 END IF;
4570
4571 p_tax_amount := nvl(v_tax_amt,0);
4572
4573 /* Added by Ramananda for bug#4407165 */
4574 EXCEPTION
4575 WHEN OTHERS THEN
4576 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
4577 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
4578 app_exception.raise_exception;
4579
4580
4581 END ja_in_calc_prec_taxes;
4582
4583 /* Added by bgowrava for Forward porting bug#5631784 */
4584
4585 /*------------------------------------------------------------------------------------------------------------*/
4586 procedure get_tax_cat_taxes_cur (p_tax_category_id number
4587 ,p_threshold_tax_cat_id number default null
4588 ,p_max_tax_line number default 0
4589 ,p_max_rgm_tax_line number default 0
4590 ,p_base number default 0
4591 ,p_refc_tax_cat_taxes_cur out nocopy ref_cur_typ
4592 , pv_retroprice_changed IN VARCHAR2 DEFAULT 'N' --Added by Kevin Cheng for Retroactive Price 2008/01/13
4593 )
4594 /***************************************************************************************************************
4595 ||Purpose :-
4596 || 1. Whenever p_threshold_tax_cat_id is not NULL then it means taxes from two categories needs to be merged.
4597 || one using p_tax_category_id and other is p_threshold_tax_cat_id
4598 || 2. current driving cursor (tax_cur) is modified to handle multiple tax categories.
4599 || 2.1 For all the tax lines defined in the p_tax_category_id there is no change
4600 || 2.2 For all the tax lines defined in the p_threshold_tax_cat_id, line_no will be changed
4601 || to p_max_tax_line + line_no where p_max_tax_line is the maximum of line numbers for
4602 || tax lines defined in p_tax_category_id
4603 || 2.3 All the precedences defined in p_threshold_tax_cat_id will be changed as following
4604 || - If precedence refers to base precedence (i.e. 0) it will be changed to p_max_rgm_tax_line
4605 || where p_max_rgm_tax_line is maximum of the line numbers of taxes having
4606 || tax_type = p_thhold_cat_base_tax_typ (i.e. tax type to be considered as a base tax
4607 || when calculating threshold taxes defined using p_threshold_tax_cat_id)
4608 || - All other precedences will be changed to precedence_N + p_max_tax_line
4609 ***************************************************************************************************************/
4610
4611 is
4612 ref_tax_cur ref_cur_typ;
4613 begin
4614 /*
4615 ||
4616 */
4617
4618 --Added by Kevin Cheng for Retroactive Price 2008/01/13
4619 --=====================================================
4620 IF pv_retroprice_changed = 'N'
4621 THEN
4622 --=====================================================
4623 open ref_tax_cur
4624 for select a.tax_id
4625 , decode (a.tax_category_id, p_tax_category_id, a.line_no
4626 , (p_max_tax_line + a.line_no)
4627 ) lno
4628 , decode (a.tax_category_id, p_tax_category_id, a.precedence_1
4629 , decode (a.precedence_1, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_1))
4630 ) p_1
4631 , decode (a.tax_category_id, p_tax_category_id, a.precedence_2
4635 , decode (a.precedence_3, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_3))
4632 , decode (a.precedence_2, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_2))
4633 ) p_2
4634 , decode (a.tax_category_id, p_tax_category_id, a.precedence_3
4636 ) p_3
4637 , decode (a.tax_category_id, p_tax_category_id, a.precedence_4
4638 , decode (a.precedence_4, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_4))
4639 ) p_4
4640 , decode (a.tax_category_id, p_tax_category_id, a.precedence_5
4641 , decode (a.precedence_5, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_5))
4642 ) p_5
4643 /* Bug 5094130. Added by Lakshmi Gopalsami Included precedences 6 to 10*/
4644 , decode (a.tax_category_id, p_tax_category_id, a.precedence_6
4645 , decode (a.precedence_6, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_6))
4646 ) p_6
4647 , decode (a.tax_category_id, p_tax_category_id, a.precedence_7
4648 , decode (a.precedence_7, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_7))
4649 ) p_7
4650 , decode (a.tax_category_id, p_tax_category_id, a.precedence_8
4651 , decode (a.precedence_8, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_8))
4652 ) p_8
4653 , decode (a.tax_category_id, p_tax_category_id, a.precedence_9
4654 , decode (a.precedence_9, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_9))
4655 ) p_9
4656 , decode (a.tax_category_id, p_tax_category_id, a.precedence_10
4657 , decode (a.precedence_10, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_10))
4658 ) p_10
4659 , b.tax_rate
4660 , b.tax_amount
4661 , b.tax_amount qty_rate /*Added b.tax_amount qty_rate by murtuza for bug 14675629*/
4662 , b.uom_code
4663 , b.end_date valid_date
4664 , DECODE(rgm_tax_types.regime_Code,jai_constants.vat_regime, 4, /* added by ssumaith - bug# 4245053*/
4665 /*jai_constants.cgst_regime, 7,*/ /* Added by Jia for GST Bug#10043656 on 2010/09/10 */
4666 /*jai_constants.sgst_regime, 7, *//* Added by Jia for GST Bug#10043656 on 2010/09/10 */
4667 jai_constants.customs_regime, 8, /* Added by Bo Li for Bug#11684111 BOE Ehancement */
4668 DECODE(UPPER(b.tax_type),
4669 'EXCISE', 1,
4670 'ADDL. EXCISE', 1,
4671 'OTHER EXCISE', 1,
4672 'TDS', 2,
4673 'EXCISE_EDUCATION_CESS' ,6, --modified by walton for inclusive tax
4674 JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS , 6 , /*bduvarag for the bug#5989740*/ --modified by walton for inclusive tax
4675 'CVD_EDUCATION_CESS' ,6, --modified by walton for inclusive tax
4676 JAI_CONSTANTS.TAX_TYPE_SH_CVD_EDU_CESS, 6 , /*bduvarag for the bug#5989740*/--modified by walton for inclusive tax
4677 0
4678 )
4679 ) tax_type_val
4680 , b.mod_cr_percentage
4681 , b.vendor_id
4682 , b.tax_type
4683 , nvl(b.rounding_factor,0) rounding_factor
4684 , b.adhoc_flag
4685 , a.tax_category_id
4686 , b.inclusive_tax_flag --added by walton for inclusive tax on 08-Dev-07
4687 from JAI_CMN_TAX_CTG_LINES a
4688 , JAI_CMN_TAXES_ALL b
4689 , jai_regime_tax_types_v rgm_tax_types /* added by ssumaith - bug# 4245053*/
4690 where a.tax_category_id in (p_tax_category_id, nvl(p_threshold_tax_cat_id,-1))
4691 and rgm_tax_types.tax_type (+) = b.tax_type /* added by ssumaith - bug# 4245053*/
4692 and a.tax_id = b.tax_id
4693 order by decode (a.tax_category_id, p_tax_category_id, a.line_no
4694 , (p_max_tax_line + a.line_no)
4695 );
4696 --Added by Kevin Cheng for Retroactive Price 2008/01/13
4697 --=====================================================
4698 ELSIF pv_retroprice_changed = 'Y'
4699 THEN
4700 open ref_tax_cur
4701 for select a.tax_id
4702 , decode (a.tax_category_id, p_tax_category_id, a.line_no
4703 , (p_max_tax_line + a.line_no)
4704 ) lno
4705 , decode (a.tax_category_id, p_tax_category_id, a.precedence_1
4706 , decode (a.precedence_1, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_1))
4707 ) p_1
4708 , decode (a.tax_category_id, p_tax_category_id, a.precedence_2
4709 , decode (a.precedence_2, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_2))
4710 ) p_2
4711 , decode (a.tax_category_id, p_tax_category_id, a.precedence_3
4712 , decode (a.precedence_3, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_3))
4713 ) p_3
4714 , decode (a.tax_category_id, p_tax_category_id, a.precedence_4
4715 , decode (a.precedence_4, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_4))
4716 ) p_4
4717 , decode (a.tax_category_id, p_tax_category_id, a.precedence_5
4718 , decode (a.precedence_5, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_5))
4719 ) p_5
4720 /* Bug 5094130. Added by Lakshmi Gopalsami Included precedences 6 to 10*/
4721 , decode (a.tax_category_id, p_tax_category_id, a.precedence_6
4722 , decode (a.precedence_6, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_6))
4723 ) p_6
4724 , decode (a.tax_category_id, p_tax_category_id, a.precedence_7
4725 , decode (a.precedence_7, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_7))
4726 ) p_7
4727 , decode (a.tax_category_id, p_tax_category_id, a.precedence_8
4728 , decode (a.precedence_8, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_8))
4729 ) p_8
4730 , decode (a.tax_category_id, p_tax_category_id, a.precedence_9
4731 , decode (a.precedence_9, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_9))
4732 ) p_9
4733 , decode (a.tax_category_id, p_tax_category_id, a.precedence_10
4734 , decode (a.precedence_10, p_base, p_max_rgm_tax_line, (p_max_tax_line + a.precedence_10))
4735 ) p_10
4736 , b.tax_rate
4737 , b.tax_amount
4738 , b.uom_code
4739 , b.end_date valid_date
4740 , DECODE(rgm_tax_types.regime_Code,jai_constants.vat_regime, 4, /* added by ssumaith - bug# 4245053*/
4741 /*jai_constants.cgst_regime, 7, *//* Added by Jia for GST Bug#10043656 on 2010/09/10 */
4742 /*jai_constants.sgst_regime, 7, *//* Added by Jia for GST Bug#10043656 on 2010/09/10 */
4743 jai_constants.customs_regime, 8, /* Added by Bo Li for Bug#11684111 BOE Ehancement */
4744 DECODE(UPPER(b.tax_type),
4745 'EXCISE', 1,
4746 'ADDL. EXCISE', 1,
4747 'OTHER EXCISE', 1,
4748 'TDS', 2,
4749 'EXCISE_EDUCATION_CESS' ,1,
4750 JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS , 1 , /*bduvarag for the bug#5989740*/
4751 'CVD_EDUCATION_CESS' ,1,
4752 JAI_CONSTANTS.TAX_TYPE_SH_CVD_EDU_CESS, 1 , /*bduvarag for the bug#5989740*/
4753 0
4754 )
4755 ) tax_type_val
4756 , b.mod_cr_percentage
4757 , b.vendor_id
4758 , b.tax_type
4759 , nvl(b.rounding_factor,0) rounding_factor
4760 , b.adhoc_flag
4761 , a.tax_category_id
4762 , b.inclusive_tax_flag --Add by Kevin Cheng
4763 from JAI_CMN_TAX_CTG_LINES a
4764 , JAI_CMN_TAXES_ALL b
4765 , jai_regime_tax_types_v rgm_tax_types /* added by ssumaith - bug# 4245053*/
4766 where a.tax_category_id in (p_tax_category_id, nvl(p_threshold_tax_cat_id,-1))
4767 and rgm_tax_types.tax_type (+) = b.tax_type /* added by ssumaith - bug# 4245053*/
4768 and a.tax_id = b.tax_id
4769 order by decode (a.tax_category_id, p_tax_category_id, a.line_no
4770 , (p_max_tax_line + a.line_no)
4771 );
4772 END IF;
4773 --=====================================================
4774 p_refc_tax_cat_taxes_cur := ref_tax_cur;
4775 end get_tax_cat_taxes_cur;
4776 /* end of bug#5631784 */
4777
4778 END jai_cmn_tax_defaultation_pkg;
[Home] [Help]