1 PACKAGE BODY jai_ar_utils_pkg
2 /* $Header: jai_ar_utils.plb 120.15 2011/12/19 09:24:10 qimeng ship $ */
3 AS
4
5 /* --------------------------------------------------------------------------------------
6 Filename:
7
8 Change History:
9
10 Date Bug Remarks
11
12 07/12/2005 4866533 Hjujjuru File version 120.3
13 added the who columns in the insert into tables JAI_AP_ETDS_REQUESTS and JAI_AP_ETDS_T.
14 Dependencies Due to this bug:-
15 None
16
17 01/11/2006 SACSETHI for bug 5228046, File version 120.4
18 Forward porting the change in 11i bug 5365523 (Additional CVD Enhancement).
19 This bug has datamodel and spec changes.
20 16/04/2007 KUNKUMAR for bugno 5989740
21 Forward porting to R12 from 11i version 115.3.6107.2
22 4/11/2009 walton for bug no 9080017
23 Issue: base tax amount is not correct in IL table after tax calcualtion
24 Anaysis: base_tax_amt_tab(i) is hoding rate instead of amount as part of inclusive ER due to new arithmetic
25 Fix: re-compute base_tax_amt_tab(i) after tax calculation, formula is:
26 base_tax_amt_tab(I):=ln_exclusive_price*base_tax_amt_tab(I)+base_tax_amount_nr_tab.
27 and fix a existing tax re-calculation issue that tax amount is zero after saved changes on AR transaction line
28
29 03/12/2009 Modified by Xiao for bug#9109910
30 Issue: tax_amount is Zero after Trx Line amount changed.
31 Analysis: for adhoc tax, tax_amount is updated as JAI_CMN_TAXES_ALL.tax_amount, which is always Zero
32 when adhoc tax is defined in table JAI_CMN_TAXES_ALL.
33 Fix: Modified cursor tax_cur, get JAI_AR_TRX_TAX_LINES.tax_amount instead of JAI_CMN_TAXES_ALL.tax_amount
34 to update tax_amount.
35
36 02-Sep-2010 Bug 10043656 Modified by Peng Zheng
37 New Tax Category Default logic for GST
38 28-Jan-2011 bug 10358786 shujuan Yan Modified
39 Inclusive Tax
40 --------- ---------- -------------------------------------------------------------
41 08-Jun-2005 Version 116.2 jai_ar_utils -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
42 as required for CASE COMPLAINCE.
43 */
44
45 PROCEDURE recalculate_tax(transaction_name VARCHAR2,
46 P_tax_category_id NUMBER,
47 p_header_id NUMBER,
48 p_line_id NUMBER,
49 p_assessable_value NUMBER default 0,
50 p_tax_amount IN OUT NOCOPY NUMBER,
51 p_currency_conv_factor NUMBER,
52 p_inventory_item_id NUMBER,
53 p_line_quantity NUMBER,
54 p_uom_code VARCHAR2,
55 p_vendor_id NUMBER,
56 p_currency VARCHAR2,
57 p_creation_date DATE,
58 p_created_by NUMBER,
59 p_last_update_date DATE,
60 p_last_updated_by NUMBER,
61 p_last_update_login NUMBER ,
62 p_vat_assessable_Value NUMBER Default 0,
63 pn_gst_assessable_Value NUMBER Default 0--added by peng.zheng for bug 10043656
64 )
65 IS
66 TYPE num_tab IS TABLE OF number
67 INDEX BY BINARY_INTEGER;
68 TYPE tax_amt_num_tab IS TABLE OF number
69 INDEX BY BINARY_INTEGER;
70 p1 num_tab;
71 p2 num_tab;
72 p3 num_tab;
73 p4 num_tab;
74 p5 num_tab;
75
76
77 -- Date 01-NOV-2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
78 -- START BUG 5228046
79
80 p6 num_tab;
81 p7 num_tab;
82 p8 num_tab;
83 p9 num_tab;
84 p10 num_tab;
85
86 -- END BUG 5228046
87
88 tax_rate_tab num_tab;
89 tax_type_tab num_tab;
90 tax_amt_tab tax_amt_num_tab;
91 base_tax_amt_tab tax_amt_num_tab;
92 base_tax_amount_nr_tab tax_amt_num_tab; --Added by walton for bug#9080017
93 end_date_tab num_tab;
94 rounding_factor_tab num_tab;
95
96
97 /* Added by Ramananda for bug#4407165 */
98 lv_object_name CONSTANT VARCHAR2(61) := 'jai_ar_utils_pkg.recalculate_tax';
99
100 bsln_amt number; -- := p_tax_amount; --Ramananda for File.Sql.35
101 v_conversion_rate number; -- := 0; --Ramananda for File.Sql.35
102 v_currency_conv_factor number; -- := p_currency_conv_factor; --Ramananda for File.Sql.35
103 v_tax_amt number; --SACSETHI for File.Sql.35
104 vamt number; --SACSETHI for File.Sql.35
105 v_amt number;
106 row_count number ; --SACSETHI for File.Sql.35
107
108 counter number;
109 max_iter number ; --SACSETHI for File.Sql.35
110 ln_amount number; -- Added by Shujuan for 10358786 on 28-Jan-2011
111
112
113 CURSOR tax_cur(p_line_id IN Number) IS
114 SELECT a.tax_id, a.tax_line_no lno, b.adhoc_flag, a.base_tax_amount, a.func_tax_amount,
115 a.precedence_1 p_1, a.precedence_2 p_2, a.precedence_3 p_3, a.precedence_4 p_4, a.precedence_5 p_5,
116 a.precedence_6 p_6, a.precedence_7 p_7, a.precedence_8 p_8, a.precedence_9 p_9, a.precedence_10 p_10,
117 b.tax_rate,
118 a.tax_amount, --Modified by Xiao for bug#9109910 on 3-Dec-09, /*b.tax_amount, */
119 b.uom_code, b.end_date valid_date, b.rounding_factor,
120 DECODE(rgm_tax_types.regime_Code,jai_constants.vat_regime, 4, /* added by ssumaith - bug# 4245053*/
121 jai_constants.cgst_regime, 7, /*Added by Peng Zheng for GST Bug#10043656*/
122 jai_constants.sgst_regime, 7, /*Added by Peng Zheng for GST Bug#10043656*/
123 decode(upper(b.tax_type),
124 'EXCISE', 1,
125 'ADDL. EXCISE', 1,
126 'OTHER EXCISE', 1,
127 'CVD', 1,
128 'TDS', 2,
129 -- Modified by Jia Li for Tax Inclusive Computations on 2007/12/11, Begin
130 -- change tax_type_val
131 -------------------------------------------------------------------------
132 'EXCISE_EDUCATION_CESS' , 6,
133 'CVD_EDUCATION_CESS' , 6,
134 'SH_EXCISE_EDUCATION_CESS' , 6,--Added by kundan kumar for bug#5907436
135 'SH_CVD_EDUCATION_CESS' , 6, --Added by kundan kumar for bug#5907436 Added by kunkumar for forward porting
136 -------------------------------------------------------------------------
137 -- Modified by Jia Li for Tax Inclusive Computations on 2007/12/11, End
138 0
139 )
140 )tax_type_val,
141 b.mod_cr_Percentage, b.vendor_id, b.tax_type
142 , b.inclusive_tax_flag -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
143 FROM JAI_AR_TRX_TAX_LINES a, JAI_CMN_TAXES_ALL b ,
144 jai_regime_tax_types_v rgm_tax_types /* added by ssumaith - bug# 4245053*/
145 WHERE a.link_to_cust_trx_line_id = p_line_id
146 AND a.tax_id = b.tax_id
147 AND rgm_tax_types.tax_type (+) = b.tax_type /* added by ssumaith - bug# 4245053*/
148 ORDER BY a.tax_line_no;
149
150 CURSOR uom_class_cur(p_line_uom_code IN varchar2, p_tax_line_uom_code IN varchar2) IS
151 SELECT A.uom_class
152 FROM mtl_units_of_measure A, mtl_units_of_measure B
153 WHERE A.uom_code = p_line_uom_code
154 AND B.uom_code = p_tax_line_uom_code
155 AND A.uom_class = B.uom_class;
156
157 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11, Begin
158 -------------------------------------------------------------------------
159 TYPE char_tab IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
160 tax_amt_rate_tax_tab TAX_AMT_NUM_TAB;
161 tax_amt_non_rate_tab TAX_AMT_NUM_TAB;
162 func_tax_amt_tab TAX_AMT_NUM_TAB;
163 tax_rate_zero_tab NUM_TAB;
164 tax_rate_per_rupee NUM_TAB;
165 tax_target_tab NUM_TAB;
166 inclu_tax_tab CHAR_TAB;
167 ln_assessable_value NUMBER;
168 ln_vat_assessable_value NUMBER;
169 ln_gst_assessable_value NUMBER;/*Added by Peng Zheng for GST Bug10043656*/
170 ln_vamt_nr NUMBER(38,10);
171 ln_bsln_amt_nr NUMBER(38,10);
172 ln_v_tax_amt_nr NUMBER(38,10);
173 ln_v_func_tax_amt NUMBER(38,10);
174 --:=0 added by peng.zheng for bug fixing, without default value, NULL will be used to calculate, which could cause bug
175 ln_exclusive_price NUMBER(38,10) := 0;
176 ln_total_non_rate_tax NUMBER(38,10) := 0;
177 --:=0 added by, ends
178 ln_total_tax_per_rupee NUMBER(38,10);
179 -------------------------------------------------------------------------
180 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11, End
181
182 BEGIN
183
184 /*---------------------------------------------
185
186 1. 2005/03/10 ssumaith - bug# 4245053 - File version 115.1
187
188 Taxes under the vat regime needs to be calculated based on the vat assessable value setup done.
189 In the vendor additional information screen and supplier additional information screen, a place
190 has been given to capture the vat assessable value.
191
192 This needs to be used for the calculation of the taxes registered under vat regime.
193
194 This change has been done by using the view jai_regime_tax_types_v - and outer joining it with the
195 JAI_CMN_TAXES_ALL table based on the tax type column.
196
197 Added a parameter p_vat_assessable_Value NUMBER for this procedure.
198
199 Dependency due to this bug - Huge
200
201 It uses many tables . views and indexes created as part of the VAT base bug - 4245089
202 This patch should always be accompanied by the VAT consolidated patch - 4245089
203
204 2 04-Aug-2005 Bug4535701. Added by Lakshmi Gopalsami
205 Commented the references to WHO columns for
206 global temporary table JAI_AR_TRX_APPS_RELS_T
207
208 Future Dependencies For the release Of this Object:-
209 (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/
210 A datamodel change )
211
212 ----------------------------------------------------------------------------------------------------------------------------------------------------
213 Current Version Current Bug Dependent Files Version Author Date Remarks
214 Of File On Bug/Patchset Dependent On
215 jai_ar_utils_pkg.recalculate_tax_p.sql
216 ----------------------------------------------------------------------------------------------------------------------------------------------------
217 115.1 4245053 IN60106 + ssumaith Service Tax and VAT Infrastructure are created
218 4146708 + based on the bugs - 4146708 and 4545089 respectively.
219 4245089
220 *************************************************************************************************************************/
221
222 bsln_amt := p_tax_amount; --Ramananda for File.Sql.35
223 v_conversion_rate := 0; --Ramananda for File.Sql.35
224 v_currency_conv_factor := p_currency_conv_factor; --Ramananda for File.Sql.35
225 max_iter := 15;
226 v_tax_amt :=0 ;
227 vamt := 0 ;
228 row_count :=0 ;
229
230 ln_vamt_nr := 0; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
231 ln_v_tax_amt_nr := 0; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
232 ln_bsln_amt_nr := 0; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
233
234 FOR rec in tax_cur(p_line_id) LOOP
235 p1(rec.lno) := nvl(rec.p_1,-1);
236 p2(rec.lno) := nvl(rec.p_2,-1);
237 p3(rec.lno) := nvl(rec.p_3,-1);
238 p4(rec.lno) := nvl(rec.p_4,-1);
239 p5(rec.lno) := nvl(rec.p_5,-1);
240
241 -- Date 01-NOV-2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
242 -- START BUG 5228046
243
244 p6(rec.lno) := nvl(rec.p_6,-1);
245 p7(rec.lno) := nvl(rec.p_7,-1);
246 p8(rec.lno) := nvl(rec.p_8,-1);
247 p9(rec.lno) := nvl(rec.p_9,-1);
248 p10(rec.lno) := nvl(rec.p_10,-1);
249
250 -- END BUG 5228046
251
252 tax_rate_tab(rec.lno) := nvl(rec.tax_rate,0);
253 tax_type_tab(rec.lno) := rec.tax_type_val;
254 tax_amt_tab(rec.lno) := 0;
255 base_tax_amt_tab(rec.lno) := 0;
256 rounding_factor_tab(rec.lno) := NVL(rec.rounding_factor, 0);
257
258 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11, Begin
259 -----------------------------------------------------------------------
260 tax_rate_per_rupee(rec.lno) := NVL(rec.tax_rate, 0)/100;
261 ln_total_tax_per_rupee := 0;
262 inclu_tax_tab(rec.lno) := NVL(rec.inclusive_tax_flag, 'N');
263
264 IF rec.tax_rate IS NULL
265 THEN
266 tax_rate_zero_tab(rec.lno) := 0;
267 ELSIF rec.tax_rate = 0
268 THEN
269 tax_rate_zero_tab(rec.lno) := -9999;
270 ELSE
271 tax_rate_zero_tab(rec.lno) := rec.tax_rate;
272 END IF; -- rec.tax_rate is null
273
274 tax_amt_rate_tax_tab(rec.lno) := 0;
275 tax_amt_non_rate_tab(rec.lno) := 0;
276 -----------------------------------------------------------------------
277 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11, End
278
279 IF tax_rate_tab(rec.lno) = 0
280 THEN
281 FOR uom_cls IN uom_class_cur(p_uom_code, rec.uom_code) LOOP
282 INV_CONVERT.inv_um_conversion( p_uom_code, rec.uom_code, p_inventory_item_id, v_conversion_rate);
283 IF nvl(v_conversion_rate, 0) <= 0
284 THEN
285 INV_CONVERT.inv_um_conversion( p_uom_code, rec.uom_code, 0, v_conversion_rate);
286 IF nvl(v_conversion_rate, 0) <= 0
287 THEN
288 v_conversion_rate := 0;
289 END IF;
290 END IF;
291 tax_amt_tab(rec.lno) := ROUND(nvl(rec.tax_amount * v_conversion_rate, 0) * p_line_quantity, NVL(rec.rounding_factor, 0));
292 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11, Begin
293 -----------------------------------------------------------------------
294 tax_amt_non_rate_tab(rec.lno) := NVL(rec.tax_amount * v_conversion_rate, 0) * p_line_quantity;
295 base_tax_amt_tab(rec.lno) := tax_amt_non_rate_tab(rec.lno);
296 -----------------------------------------------------------------------
297 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11, End
298 END LOOP;
299 END IF;
300
301 IF rec.valid_date is NULL or rec.valid_date >= sysdate
302 THEN
303 end_date_tab(rec.lno) := 1;
304 ELSE
305 tax_amt_tab(rec.lno) := 0;
306 end_date_tab(rec.lno) := 0;
307 END IF;
308 row_count := row_count + 1;
309 END LOOP;
310
311 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11, Begin
312 -----------------------------------------------------------------------
313 IF p_assessable_value <> p_tax_amount
314 THEN
315 ln_assessable_value := p_assessable_value;
316 ELSE
317 ln_assessable_value := 1;
318 END IF;
319
320 IF p_vat_assessable_value <> p_tax_amount
321 THEN
322 ln_vat_assessable_value := p_vat_assessable_value;
323 ELSE
324 ln_vat_assessable_value := 1;
325 END IF;
326
327 --added by peng.zheng for bug 10043656, begins
328 IF pn_gst_assessable_Value <> p_tax_amount
329 THEN
330 ln_gst_assessable_value := pn_gst_assessable_Value;
331 ELSE
332 ln_gst_assessable_value := 1;
333 END IF;
334 --added by peng.zheng for bug 10043656, ends
335
336 -----------------------------------------------------------------------
337 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11, End
338
339 FOR I in 1..row_count LOOP
340 -- Delete by Jia Li for Tax Inclusive Computations on 2007/12/11, Begin
341 -----------------------------------------------------------------------
342 /*
343 IF tax_type_tab(I) = 1
344 THEN
345 bsln_amt := p_assessable_value;
346 ELSIF tax_type_tab(I) = 4 THEN
347 bsln_amt := p_vat_assessable_value;
348 ELSE
349 bsln_amt := p_tax_amount;
350 END IF;
351 */
352 -----------------------------------------------------------------------
353 -- Delete by Jia Li for Tax Inclusive Computations on 2007/12/11, End
354
355 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11, Begin
356 -----------------------------------------------------------------------
357 IF end_date_tab(I) <> 0
358 THEN
359 IF tax_type_tab(I) = 1
360 THEN
361 IF ln_assessable_value = 1
362 THEN
363 bsln_amt := 1;
364 ln_bsln_amt_nr := 0;
365 ELSE
366 bsln_amt := 0;
367 ln_bsln_amt_nr := ln_assessable_value;
368 END IF;
369 ELSIF tax_type_tab(I) = 4
370 THEN
371 IF ln_vat_assessable_value = 1
372 THEN
373 bsln_amt := 1;
374 ln_bsln_amt_nr := 0;
375 ELSE
376 bsln_amt := 0;
377 ln_bsln_amt_nr := ln_vat_assessable_value;
378 END IF;
379 --added by peng.zheng for bug 10043656, begins
380 ELSIF tax_type_tab(I) = 7
381 THEN
382 IF ln_gst_assessable_value = 1
383 THEN
384 bsln_amt := 1;
385 ln_bsln_amt_nr := 0;
386 ELSE
387 bsln_amt := 0;
388 ln_bsln_amt_nr := ln_gst_assessable_value;
389 END IF;
390 --added by peng.zheng for bug 10043656, ends
391 ELSIF tax_type_tab(I) = 6
392 THEN
393 bsln_amt := 0;
394 ln_bsln_amt_nr := 0;
395 ELSE
396 bsln_amt := 1;
397 ln_bsln_amt_nr := 0;
398 END IF; -- tax_type_tab(I) = 1
399 -----------------------------------------------------------------------
400 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11, End
401
402 IF tax_rate_tab(I) <> 0
403 THEN
404 /* Commented out by Shyan for bug 10358786 on 28-Jan-2011 , Begin
405 IF p1(I) < I and p1(I) not in (-1,0) THEN
406 vamt := vamt + nvl(tax_amt_tab(p1(I)),0);
407 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(p1(I)), 0); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
408 ELSIF p1(I) = 0 THEN
409 vamt := vamt + bsln_amt;
410 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
411 END IF;
412
413 IF p2(I) < I and p2(I) not in (-1,0) THEN
414 vamt := vamt + nvl(tax_amt_tab(p2(I)),0);
415 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(p2(I)), 0); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
416 ELSIF p2(I) = 0 THEN
417 vamt := vamt + bsln_amt;
418 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
419 END IF;
420
421 IF p3(I) < I and p3(I) not in (-1,0) THEN
422 vamt := vamt + nvl(tax_amt_tab(p3(I)),0);
423 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(p3(I)), 0); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
424 ELSIF p3(I) = 0 THEN
425 vamt := vamt + bsln_amt;
426 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
427 END IF;
428
429 IF p4(I) < I and p4(I) not in (-1,0) THEN
430 vamt := vamt + nvl(tax_amt_tab(p4(I)),0);
431 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(p4(I)), 0); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
432 ELSIF p4(I) = 0 THEN
433 vamt := vamt + bsln_amt;
434 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
435 END IF;
436
437 IF p5(I) < I and p5(I) not in (-1,0) THEN
438 vamt := vamt + nvl(tax_amt_tab(p5(I)),0);
439 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(p5(I)), 0); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
440 ELSIF p5(I) = 0 THEN
441 vamt := vamt + bsln_amt;
442 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
443 END IF;
444
445 -- Date 01-NOV-2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
446 -- START BUG 5228046
447 IF p6(I) < I and p6(I) not in (-1,0) THEN
448 vamt := vamt + nvl(tax_amt_tab(p6(I)),0);
449 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(p6(I)), 0); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
450 ELSIF p6(I) = 0 THEN
451 vamt := vamt + bsln_amt;
452 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
453 END IF;
454
455 IF p7(I) < I and p7(I) not in (-1,0) THEN
456 vamt := vamt + nvl(tax_amt_tab(p7(I)),0);
457 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(p7(I)), 0); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
458 ELSIF p7(I) = 0 THEN
459 vamt := vamt + bsln_amt;
460 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
461 END IF;
462
463 IF p8(I) < I and p8(I) not in (-1,0) THEN
464 vamt := vamt + nvl(tax_amt_tab(p8(I)),0);
465 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(p8(I)), 0); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
466 ELSIF p8(I) = 0 THEN
467 vamt := vamt + bsln_amt;
468 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
469 END IF;
470
471 IF p9(I) < I and p9(I) not in (-1,0) THEN
472 vamt := vamt + nvl(tax_amt_tab(p9(I)),0);
473 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(p9(I)), 0); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
474 ELSIF p9(I) = 0 THEN
475 vamt := vamt + bsln_amt;
476 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
477 END IF;
478
479 IF p10(I) < I and p10(I) not in (-1,0) THEN
480 vamt := vamt + nvl(tax_amt_tab(p10(I)),0);
481 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(p10(I)), 0); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
482 ELSIF p10(I) = 0 THEN
483 vamt := vamt + bsln_amt;
484 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
485 END IF;
486
487 -- END BUG 5228046
488 Commented out by Shyan for bug 10358786 on 28-Jan-2011, End*/
489 --Added by Shujuan for 10358786 on 28-Jan-2011, Begin
490 -------------------------------------------------------
491 tax_amt_non_rate_tab(I):=0;
492 IF P1(I) < I AND P1(I) NOT IN (-1, 0) THEN --replaced I by line_no_tab(I) for bug#9214366
493 IF inclu_tax_tab(P1(I)) = 'Y' OR tax_amt_rate_tax_tab(P1(I)) IS NOT NULL then
494 vamt := vamt + NVL(tax_amt_tab(P1(I)), 0);
495 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(P1(I)), 0); --added by walton for inclusive tax
496 END IF;
497
498 ELSIF P1(I) = 0 THEN
499 IF inclu_tax_tab(I) = 'Y' then
500 vamt := vamt + bsln_amt;
501 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
502 ELSE
503 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
504
505 END IF;
506 END IF;
507 IF p2(I) < I AND p2(I) NOT IN (-1, 0) THEN --replaced I by line_no_tab(I) for bug#9214366
508 IF inclu_tax_tab(P2(I)) = 'Y' OR
509 tax_amt_rate_tax_tab(P2(I)) IS NOT NULL then
510 vamt := vamt + NVL(tax_amt_tab(p2(I)), 0);
511 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(P2(I)), 0); --added by walton for inclusive tax
512 END IF;
513
514 ELSIF p2(I) = 0 THEN
515 IF inclu_tax_tab(I) = 'Y' then
516 vamt := vamt + bsln_amt;
517 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
518 ELSE
519 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
520 END IF;
521
522 END IF;
523 IF p3(I) < I AND p3(I) NOT IN (-1, 0) THEN --replaced I by line_no_tab(I) for bug#9214366
524 IF inclu_tax_tab(P3(I)) = 'Y' OR tax_amt_rate_tax_tab(P3(I)) IS NOT NULL then
525 vamt := vamt + NVL(tax_amt_tab(p3(I)), 0);
526 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(P3(I)), 0); --added by walton for inclusive tax
527 END IF;
528
529 ELSIF p3(I) = 0 THEN
530 IF inclu_tax_tab(I) = 'Y' then
531 vamt := vamt + bsln_amt;
532 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
533 ELSE
534 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
535 END IF;
536 END IF;
537
538 IF p4(I) < I AND p4(I) NOT IN (-1, 0) THEN --replaced I by line_no_tab(I) for bug#9214366
539 IF inclu_tax_tab(P4(I)) = 'Y' OR tax_amt_rate_tax_tab(P4(I)) IS NOT NULL then
540 vamt := vamt + NVL(tax_amt_tab(p4(I)), 0);
541 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(P4(I)), 0); --added by walton for inclusive tax
542 END IF;
543 ELSIF p4(I) = 0 THEN
544 IF inclu_tax_tab(I) = 'Y' then
545 vamt := vamt + bsln_amt;
546 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
547 ELSE
548 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
549 END IF;
550 END IF;
551
552 IF p5(I) < I AND p5(I) NOT IN (-1, 0) THEN --replaced I by line_no_tab(I) for bug#9214366
553 IF inclu_tax_tab(P5(I)) = 'Y' OR tax_amt_rate_tax_tab(P5(I)) IS NOT NULL then
554 vamt := vamt + NVL(tax_amt_tab(p5(I)), 0);
555 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(P5(I)), 0); --added by walton for inclusive tax
556 END IF;
557 ELSIF p5(I) = 0 THEN
558 IF inclu_tax_tab(I) = 'Y' then
559 vamt := vamt + bsln_amt;
560 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
561 ELSE
562 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
563 END IF;
564 END IF;
565 -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
566 -- start bug 5228046
567 IF p6(I) < I AND p6(I) NOT IN (-1, 0) THEN --replaced I by line_no_tab(I) for bug#9214366
568 IF inclu_tax_tab(P6(I)) = 'Y' OR tax_amt_rate_tax_tab(P6(I)) IS NOT NULL then
569 vamt := vamt + NVL(tax_amt_tab(p6(I)), 0);
570 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(P6(I)), 0); --added by walton for inclusive tax
571 END IF;
572
573 ELSIF p6(I) = 0 THEN
574 IF inclu_tax_tab(I) = 'Y' then
575 vamt := vamt + bsln_amt;
576 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
577 ELSE
578 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
579 END IF;
580 END IF;
581
582 IF p7(I) < I AND p7(I) NOT IN (-1, 0) THEN --replaced I by line_no_tab(I) for bug#9214366
583 IF inclu_tax_tab(P7(I)) = 'Y' OR tax_amt_rate_tax_tab(P7(I)) IS NOT NULL then
584 vamt := vamt + NVL(tax_amt_tab(p7(I)), 0);
585 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(P7(I)), 0); --added by walton for inclusive tax
586 END IF;
587
588 ELSIF p7(I) = 0 THEN
589 IF inclu_tax_tab(I) = 'Y' then
590 vamt := vamt + bsln_amt;
591 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
592 ELSE
593 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
594 END IF;
595 END IF;
596
597 IF p8(I) < I AND p8(I) NOT IN (-1, 0) THEN --replaced I by line_no_tab(I) for bug#9214366
598 IF inclu_tax_tab(P4(I)) = 'Y' OR tax_amt_rate_tax_tab(P8(I)) IS NOT NULL then
599 vamt := vamt + NVL(tax_amt_tab(p8(I)), 0);
600 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(P8(I)), 0); --added by walton for inclusive tax
601 END IF;
602
603 ELSIF p8(I) = 0 THEN
604 IF inclu_tax_tab(I) = 'Y' then
605 vamt := vamt + bsln_amt;
606 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
607 ELSE
608 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
609 END IF;
610 END IF;
611
612 IF p9(I) <I AND p9(I) NOT IN (-1, 0) THEN --replaced I by line_no_tab(I) for bug#9214366
613 IF inclu_tax_tab(P9(I)) = 'Y' OR tax_amt_rate_tax_tab(P9(I)) IS NOT NULL then
614 vamt := vamt + NVL(tax_amt_tab(p4(I)), 0);
615 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(P9(I)), 0); --added by walton for inclusive tax
616 END IF;
617
618 ELSIF p9(I) = 0 THEN
619 IF inclu_tax_tab(I) = 'Y' then
620 vamt := vamt + bsln_amt;
621 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
622 ELSE
623 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
624 END IF;
625 END IF;
626
627 IF p10(I) < I AND p10(I) NOT IN (-1, 0) THEN --replaced I by line_no_tab(I) for bug#9214366
628 IF inclu_tax_tab(P10(I)) = 'Y' OR tax_amt_rate_tax_tab(P10(I)) IS NOT NULL then
629 vamt := vamt + NVL(tax_amt_tab(p10(I)), 0);
630 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(P10(I)), 0); --added by walton for inclusive tax
631 END IF;
632
633 ELSIF p10(I) = 0 THEN
634 IF inclu_tax_tab(I) = 'Y' then
635 vamt := vamt + bsln_amt;
636 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
637 ELSE
638 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
639 END IF;
640 END IF;
641 -- end bug 5228046
642 -------------------------------------------------------
643 --Added by Shujuan for 10358786 on 28-Jan-2011, End
644 v_tax_amt := v_tax_amt + (vamt * (tax_rate_tab(I)/100));
645 base_tax_amt_tab(I) := vamt;
646 tax_amt_tab(I) := nvl(tax_amt_tab(I),0) + v_tax_amt;
647
648 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11, Begin
649 -----------------------------------------------------------------------
650 ln_v_tax_amt_nr := ln_v_tax_amt_nr + (ln_vamt_nr * (tax_rate_tab(I)/100));
651 tax_amt_non_rate_tab(I) := NVL(tax_amt_non_rate_tab(I), 0) + ln_v_tax_amt_nr;
652 --Commented out by Shyan for bug 10358786 on 28-Jan-2011 , Begin
653 -------------------------------------------------------------------
654 --tax_amt_rate_tax_tab(I):= tax_amt_tab(I);
655 -------------------------------------------------------------------
656 -- Commented out by Shyan for bug 10358786 on 28-Jan-2011 , End
657
658 -- Added by Shyan for bug 10358786 on 28-Jan-2011 , Begin
659 -------------------------------------------------------------------
660 IF inclu_tax_tab(I) = 'Y' then
661 tax_amt_rate_tax_tab(I) := tax_amt_tab(I);
662 END IF;
663 -------------------------------------------------------------------
664 -- Added by Shyan for bug 10358786 on 28-Jan-2011, End
665 -----------------------------------------------------------------------
666 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11, End
667 base_tax_amount_nr_tab(i):=ln_vamt_nr; --Added by walton for bug#9080017
668
669 /*IF end_date_tab(I) = 0
670 THEN
671
672 tax_amt_tab(I) := 0;
673 base_tax_amt_tab(I) := 0;
674 ELSIF end_date_tab(I) = 1 THEN
675 IF tax_type_tab(I) IN (1, 2)
676 THEN
677 v_tax_amt := ROUND(v_tax_amt);
678 END IF;
679 tax_amt_tab(I) := nvl(tax_amt_tab(I),0) + v_tax_amt;
680 END IF;*/
681 vamt := 0;
682 v_tax_amt := 0;
683 ln_vamt_nr := 0; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
684 ln_v_tax_amt_nr := 0; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
685
686 END IF;
687 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11, Begin
688 -----------------------------------------------------------------------
689 ELSE
690 tax_amt_tab(I) := 0;
691 base_tax_amt_tab(I) := 0;
692 END IF; -- end_date_tab(I) <> 0
693 -----------------------------------------------------------------------
694 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11, End
695 END LOOP;
696
697 /*for i in 1 .. row_count loop
698 insert into xc values( ' Tax Amt from I loop in bkend Line ' || to_char( i ) || ' is ' || to_char( tax_amt_tab( I ) ) );
699 end loop;*/
700 FOR I in 1..row_count LOOP
701 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11, Begin
702 -----------------------------------------------------------------------
703 IF end_date_tab(I) <> 0
704 THEN
705 -----------------------------------------------------------------------
706 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11, End
707 IF tax_rate_tab(I) <> 0 THEN
708 IF p1(I) > I THEN
709 vamt := vamt + nvl(tax_amt_tab(p1(I)),0);
710 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(p1(I)), 0); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
711 END IF;
712 IF p2(I) > I THEN
713 vamt := vamt + nvl(tax_amt_tab(p2(I)),0);
714 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(p2(I)), 0); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
715 END IF;
716 IF p3(I) > I THEN
717 vamt := vamt + nvl(tax_amt_tab(p3(I)),0);
718 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(p3(I)), 0); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
719 END IF;
720 IF p4(I) > I THEN
721 vamt := vamt + nvl(tax_amt_tab(p4(I)),0);
722 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(p4(I)), 0); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
723 END IF;
724 IF p5(I) > I THEN
725 vamt := vamt + nvl(tax_amt_tab(p5(I)),0);
726 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(p5(I)), 0); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
727 END IF;
728
729 -- Date 01-NOV-2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
730 -- START BUG 5228046
731 IF p6(I) > I THEN
732 vamt := vamt + nvl(tax_amt_tab(p6(I)),0);
733 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(p6(I)), 0); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
734 END IF;
735 IF p7(I) > I THEN
736 vamt := vamt + nvl(tax_amt_tab(p7(I)),0);
737 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(p7(I)), 0); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
738 END IF;
739 IF p8(I) > I THEN
740 vamt := vamt + nvl(tax_amt_tab(p8(I)),0);
741 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(p8(I)), 0); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
742 END IF;
743 IF p9(I) > I THEN
744 vamt := vamt + nvl(tax_amt_tab(p9(I)),0);
745 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(p9(I)), 0); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
746 END IF;
747 IF p10(I) > I THEN
748 vamt := vamt + nvl(tax_amt_tab(p10(I)),0);
749 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(p10(I)), 0); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
750 END IF;
751
752 -- END BUG 5228046
753
754
755 v_tax_amt := v_tax_amt + (vamt * (tax_rate_tab(I)/100));
756
757 base_tax_amt_tab(I) := vamt; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
758 ln_v_tax_amt_nr := ln_v_tax_amt_nr + (ln_vamt_nr * (tax_rate_tab(I)/100)); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
759 base_tax_amount_nr_tab(i):=ln_vamt_nr; --Added by walton for bug#9080017
760
761 IF vamt <> 0 THEN
762 base_tax_amt_tab(I) := base_tax_amt_tab(I) + vamt;
763 END IF;
764
765 tax_amt_tab(I) := nvl(tax_amt_tab(I),0) + v_tax_amt;
766 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11, Begin
767 -----------------------------------------------------------------------
768 tax_amt_non_rate_tab(I) := NVL(tax_amt_non_rate_tab(I), 0) + ln_v_tax_amt_nr; --modified by walton for bug9080017
769 tax_amt_rate_tax_tab(I) := tax_amt_tab(I);
770 ln_vamt_nr := 0;
771 ln_v_tax_amt_nr := 0;
772 -----------------------------------------------------------------------
773 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11, End
774
775 /*IF end_date_tab(I) = 0 THEN
776
777 tax_amt_tab(I) := 0;
778 base_tax_amt_tab(I) := 0;
779 ELSIF end_date_tab(I) = 1 THEN
780 IF tax_type_tab(I) IN (1, 2)
781 THEN
782 v_tax_amt := ROUND(v_tax_amt);
783 END IF;
784 tax_amt_tab(I) := nvl(tax_amt_tab(I),0) + v_tax_amt;
785 END IF;*/
786 vamt := 0;
787 v_tax_amt := 0;
788 END IF;
789
790 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11, Begin
791 -----------------------------------------------------------------------
792 ELSE
793 base_tax_amt_tab(I) := vamt;
794 tax_amt_tab(I) := 0;
795 END IF; -- end_date_tab(I) <> 0
796 -----------------------------------------------------------------------
797 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11, End
798 END LOOP;
799
800
801 /*for i in 1 .. row_count loop
802 insert into xc values( ' Tax Amt from II loop in bkend Line ' || to_char( i ) || ' is ' || to_char( tax_amt_tab( I ) ) );
803 end loop;*/
804 FOR counter IN 1 .. max_iter LOOP
805 vamt := 0;
806 ln_vamt_nr := 0; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
807 v_tax_amt := 0;
808 ln_v_tax_amt_nr:=0; --Added by walton for bug #9080017
809
810 FOR i IN 1 .. row_count LOOP
811 --changed > to <> allow -ve tax rate computation - Gaurav 06-dec-99
812 -- Deleted by Jia Li for Tax Inclusive Computations on 2007/12/11, Begin
813 ------------------------------------------------------------------------
814 /*
815 IF tax_rate_tab( i ) <> 0 THEN
816 IF tax_type_tab( I ) = 1 THEN
817 v_amt := p_assessable_value;
818 ELSIF tax_type_tab( I ) = 4 THEN
819 v_amt := p_vat_assessable_value;
820 ELSE
821 IF p_assessable_value IN ( 0, -1 ) OR tax_type_tab( I ) <> 1 THEN
822 v_amt := p_tax_amount;
823 END IF;
824 END IF;
825 */
826 ------------------------------------------------------------------------
827 -- Deleted by Jia Li for Tax Inclusive Computations on 2007/12/11, End
828
829 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11, Begin
830 -----------------------------------------------------------------------
831 IF ( tax_rate_tab(I) <> 0 OR tax_rate_zero_tab(I) = -9999 )
832 AND
833 ( end_date_tab(I) <> 0 )
834 THEN
835 IF tax_type_tab(I) = 1
836 THEN
837 IF ln_assessable_value = 1
838 THEN
839 v_amt := 1;
840 ln_bsln_amt_nr := 0;
841 ELSE
842 v_amt := 0;
843 ln_bsln_amt_nr := ln_assessable_value;
844 END IF;
845 ELSIF tax_type_tab(I) = 4
846 THEN
847 IF ln_vat_assessable_value = 1
848 THEN
849 v_amt := 1;
850 ln_bsln_amt_nr := 0;
851 ELSE
852 v_amt := 0;
853 ln_bsln_amt_nr := ln_vat_assessable_value;
854 END IF;
855 --added by peng.zheng for bug 10043656, begins
856 ELSIF tax_type_tab(I) = 7
857 THEN
858 IF ln_gst_assessable_value = 1
859 THEN
860 v_amt := 1;
861 ln_bsln_amt_nr := 0;
862 ELSE
863 v_amt := 0;
864 ln_bsln_amt_nr := ln_gst_assessable_value;
865 END IF;
866 --added by peng.zheng for bug 10043656, ends
867 ELSIF tax_type_tab(I) = 6
868 THEN
869 v_amt := 0;
870 ln_bsln_amt_nr := 0;
871 ELSE
872 v_amt := 1;
873 ln_bsln_amt_nr := 0;
874 END IF; -- tax_type_tab(I) = 1
875 -----------------------------------------------------------------------
876 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11, End
877 /* Commented out by Shyan for bug 10358786 on 28-Jan-2011 , Begin
878 --------------------------------------------------------------------
879 IF p1( i ) <> -1 THEN
880 IF p1( i ) <> 0 THEN
881 vamt := vamt + tax_amt_tab( p1( I ) );
882 ln_vamt_nr := ln_vamt_nr + tax_amt_non_rate_tab(p1(I)); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
883 ELSIF p1(i) = 0 THEN
884 vamt := vamt + v_amt;
885 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
886 END IF;
887 END IF;
888 IF p2( i ) <> -1 THEN
889 IF p2( i ) <> 0 THEN
890 vamt := vamt + tax_amt_tab( p2( I ) );
891 ln_vamt_nr := ln_vamt_nr + tax_amt_non_rate_tab(p2(I)); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
892 ELSIF p2(i) = 0 THEN
893 vamt := vamt + v_amt;
894 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
895 END IF;
896 END IF;
897 IF p3( i ) <> -1 THEN
898 IF p3( i ) <> 0 THEN
899 vamt := vamt + tax_amt_tab( p3( I ) );
900 ln_vamt_nr := ln_vamt_nr + tax_amt_non_rate_tab(p3(I)); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
901 ELSIF p3(i) = 0 THEN
902 vamt := vamt + v_amt;
903 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
904 END IF;
905 END IF;
906 IF p4( i ) <> -1 THEN
907 IF p4( i ) <> 0 THEN
908 vamt := vamt + tax_amt_tab( p4( i ) );
909 ln_vamt_nr := ln_vamt_nr + tax_amt_non_rate_tab(p4(I)); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
910 ELSIF p4(i) = 0 THEN
911 vamt := vamt + v_amt;
912 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
913 END IF;
914 END IF;
915
916 IF p5( i ) <> -1 THEN
917 IF p5( i ) <> 0 THEN
918 vamt := vamt + tax_amt_tab( p5( i ) );
919 ln_vamt_nr := ln_vamt_nr + tax_amt_non_rate_tab(p5(I)); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
920 ELSIF p5(i) = 0 THEN
921 vamt := vamt + v_amt;
922 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
923 END IF;
924 END IF;
925
926 -- Date 01-NOV-2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
927 -- START BUG 5228046
928
929 IF p6( i ) <> -1 THEN
930 IF p6( i ) <> 0 THEN
931 vamt := vamt + tax_amt_tab( p6( I ) );
932 ln_vamt_nr := ln_vamt_nr + tax_amt_non_rate_tab(p6(I)); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
933 ELSIF p6(i) = 0 THEN
934 vamt := vamt + v_amt;
935 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
936 END IF;
937 END IF;
938 IF p7( i ) <> -1 THEN
939 IF p7( i ) <> 0 THEN
940 vamt := vamt + tax_amt_tab( p7( I ) );
941 ln_vamt_nr := ln_vamt_nr + tax_amt_non_rate_tab(p7(I)); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
942 ELSIF p7(i) = 0 THEN
943 vamt := vamt + v_amt;
944 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
945 END IF;
946 END IF;
947 IF p8( i ) <> -1 THEN
948 IF p8( i ) <> 0 THEN
949 vamt := vamt + tax_amt_tab( p8( I ) );
950 ln_vamt_nr := ln_vamt_nr + tax_amt_non_rate_tab(p8(I)); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
951 ELSIF p8(i) = 0 THEN
952 vamt := vamt + v_amt;
953 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
954 END IF;
955 END IF;
956 IF p9( i ) <> -1 THEN
957 IF p9( i ) <> 0 THEN
958 vamt := vamt + tax_amt_tab( p9( i ) );
959 ln_vamt_nr := ln_vamt_nr + tax_amt_non_rate_tab(p9(I)); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
960 ELSIF p9(i) = 0 THEN
961 vamt := vamt + v_amt;
962 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
963 END IF;
964 END IF;
965 IF p10( i ) <> -1 THEN
966 IF p10( i ) <> 0 THEN
967 vamt := vamt + tax_amt_tab( p10( i ) );
968 ln_vamt_nr := ln_vamt_nr + tax_amt_non_rate_tab(p10(I)); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
969 ELSIF p10(i) = 0 THEN
970 vamt := vamt + v_amt;
971 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
972 END IF;
973 END IF;
974
975 -- END BUG 5228046
976 ----------------------------------------------------------------
977 Commented out by Shyan for bug 10358786 on 28-Jan-2011, End */
978
979 -- Added by shujuan for bug 10358786 on 28-Jan-2011, Begin
980 -----------------------------------------------------------
981 ln_vamt_nr :=0;
982 tax_amt_non_rate_tab(I):=0;
983 IF P1(i) <> -1 THEN
984 IF P1(i) <> 0 THEN
985 IF inclu_tax_tab(P1(I)) = 'Y' OR tax_amt_rate_tax_tab(P1(I)) IS NOT NULL then
986 vamt:= vamt + tax_amt_tab(P1(I));
987 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(P1(I)), 0); --added by walton for inclusive tax
988 END IF;
989 ELSIF P1(i) = 0 THEN
990 IF inclu_tax_tab(I) = 'Y' then
991 vamt := vamt + bsln_amt;
992 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr;
993 ELSE
994 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
995 END IF;
996 END IF;
997 END IF;
998
999 IF p2(i) <> -1 THEN
1000 IF p2(i) <> 0 THEN
1001 IF inclu_tax_tab(P2(I)) = 'Y' OR tax_amt_rate_tax_tab(P2(I)) IS NOT NULL then
1002 vamt := vamt + tax_amt_tab(p2(I));
1003 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(P2(I)), 0); --added by walton for inclusive tax
1004 END IF;
1005 ELSIF p2(i) = 0 THEN
1006 IF inclu_tax_tab(I) = 'Y' then
1007 vamt := vamt + v_amt;
1008 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
1009 ELSE
1010 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
1011 END IF;
1012 END IF;
1013 END IF;
1014
1015 IF p3(i) <> -1 THEN
1016 IF p3(i) <> 0 THEN
1017 IF inclu_tax_tab(P3(I)) = 'Y' OR tax_amt_rate_tax_tab(P3(I)) IS NOT NULL then
1018 vamt := vamt + tax_amt_tab(p3(I));
1019 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(P3(I)), 0); --added by walton for inclusive tax
1020 END IF;
1021 ELSIF p3(i) = 0 THEN
1022 IF inclu_tax_tab(I) = 'Y' then
1023 vamt := vamt + v_amt;
1024 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
1025 ELSE
1026 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
1027 END IF;
1028 END IF;
1029 END IF;
1030
1031 IF p4(i) <> -1 THEN
1032 IF p4(i) <> 0 THEN
1033 IF inclu_tax_tab(P4(I)) = 'Y' OR tax_amt_rate_tax_tab(P4(I)) IS NOT NULL then
1034 vamt := vamt + tax_amt_tab(p4(I));
1035 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(P4(I)), 0); --added by walton for inclusive tax
1036 END IF;
1037 ELSIF p4(i) = 0 THEN
1038 IF inclu_tax_tab(I) = 'Y' then
1039 vamt := vamt + v_amt;
1040 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
1041 ELSE
1042 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
1043 END IF;
1044 END IF;
1045 END IF;
1046
1047 IF p5(i) <> -1 THEN
1048 IF p5(i) <> 0 THEN
1049 IF inclu_tax_tab(P5(I)) = 'Y' OR tax_amt_rate_tax_tab(P5(I)) IS NOT NULL then
1050 vamt := vamt + tax_amt_tab(p5(I));
1051 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(P5(I)), 0); --added by walton for inclusive tax
1052 END IF;
1053 ELSIF p5(i) = 0 THEN
1054 IF inclu_tax_tab(I) = 'Y' then
1055 vamt := vamt + v_amt;
1056 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
1057 ELSE
1058 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
1059 END IF;
1060 END IF;
1061 END IF;
1062
1063 -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
1064 -- start bug 5228046
1065 IF p6(i) <> -1 THEN
1066 IF p6(i) <> 0 THEN
1067 IF inclu_tax_tab(P6(I)) = 'Y' OR tax_amt_rate_tax_tab(P6(I)) IS NOT NULL then
1068 vamt := vamt + tax_amt_tab(p6(I));
1069 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(P6(I)), 0); --added by walton for inclusive tax
1070 END IF;
1071 ELSIF p6(i) = 0 THEN
1072 IF inclu_tax_tab(I) = 'Y' then
1073 vamt := vamt + v_amt;
1074 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
1075 ELSE
1076 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
1077 END IF;
1078 END IF;
1079 END IF;
1080 IF p7(i) <> -1 THEN
1081 IF p7(i) <> 0 THEN
1082 IF inclu_tax_tab(P7(I)) = 'Y' OR tax_amt_rate_tax_tab(P7(I)) IS NOT NULL then
1083 vamt := vamt + tax_amt_tab(p7(I));
1084 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(P7(I)), 0); --added by walton for inclusive tax
1085 END IF;
1086 ELSIF p7(i) = 0 THEN
1087 IF inclu_tax_tab(I) = 'Y' then
1088 vamt := vamt + v_amt;
1089 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
1090 ELSE
1091 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
1092 END IF;
1093 END IF;
1094 END IF;
1095
1096 IF p8(i) <> -1 THEN
1097 IF p8(i) <> 0 THEN
1098 IF inclu_tax_tab(P8(I)) = 'Y' OR tax_amt_rate_tax_tab(P8(I)) IS NOT NULL then
1099 vamt := vamt + tax_amt_tab(p8(I));
1100 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(P8(I)), 0); --added by walton for inclusive tax
1101 END IF;
1102 ELSIF p8(i) = 0 THEN
1103 IF inclu_tax_tab(I) = 'Y' then
1104 vamt := vamt + v_amt;
1105 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
1106 ELSE
1107 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
1108 END IF;
1109 END IF;
1110 END IF;
1111
1112 IF p9(i) <> -1 THEN
1113 IF p9(i) <> 0 THEN
1114 IF inclu_tax_tab(P9(I)) = 'Y' OR tax_amt_rate_tax_tab(P9(I)) IS NOT NULL then
1115 vamt := vamt + tax_amt_tab(p9(I));
1116 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(P9(I)), 0); --added by walton for inclusive tax
1117 END IF;
1118 ELSIF p9(i) = 0 THEN
1119 IF inclu_tax_tab(I) = 'Y' then
1120 vamt := vamt + v_amt;
1121 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
1122 ELSE
1123 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
1124 END IF;
1125 END IF;
1126 END IF;
1127
1128 IF p10(i) <> -1 THEN
1129 IF p10(i) <> 0 THEN
1130 IF inclu_tax_tab(P10(I)) = 'Y' OR tax_amt_rate_tax_tab(P10(I)) IS NOT NULL then
1131 vamt := vamt + tax_amt_tab(p10(I));
1132 ln_vamt_nr := ln_vamt_nr + NVL(tax_amt_non_rate_tab(P10(I)), 0); --added by walton for inclusive tax
1133 END IF;
1134 ELSIF p10(i) = 0 THEN
1135 IF inclu_tax_tab(I) = 'Y' then
1136 vamt := vamt + v_amt;
1137 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
1138 ELSE
1139 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount;
1140 END IF;
1141 END IF;
1142 END IF;
1143 -- end bug 5228046
1144 -----------------------------------------------------------
1145 -- Added by shujuan for bug 10358786 on 28-Jan-2011, End
1146 v_tax_amt := v_tax_amt + ( vamt * ( tax_rate_tab( i )/100));
1147 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11, Begin
1148 -----------------------------------------------------------------------
1149 base_tax_amt_tab(I) := vamt;
1150 tax_target_tab(I) := vamt;
1151 ln_v_func_tax_amt := v_tax_amt + ( vamt * ( tax_rate_tab(I)/100 ));
1152 ln_v_tax_amt_nr := ln_v_tax_amt_nr+( ln_vamt_nr*(tax_rate_tab(i)/100));
1153 --v_tax_amt := v_tax_amt + ( vamt * ( tax_rate_tab(I)/100 )); --Commented by walton for bug #9080017
1154 ELSIF tax_rate_tab(I) = 0
1155 THEN
1156 base_tax_amt_tab(I) := tax_amt_tab(I);
1157 v_tax_amt := tax_amt_tab(I);
1158 tax_target_tab(I) := v_tax_amt;
1159 ln_v_tax_amt_nr := tax_amt_non_rate_tab(i);
1160 ELSIF end_date_tab(I) = 0
1161 THEN
1162 tax_amt_tab(I) := 0;
1163 base_tax_amt_tab(I) := 0;
1164 tax_target_tab(I) := 0;
1165 END IF; -- (tax_rate_tab(I) <> 0 OR tax_rate_zero_tab(I) = -9999) AND (end_date_tab(I) <> 0)
1166 -----------------------------------------------------------------------
1167 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11, End
1168
1169 -- Deleted by Jia Li for Tax Inclusive Computations on 2007/12/11, Begin
1170 -----------------------------------------------------------------------
1171 /*
1172 ELSE
1173 v_tax_amt := tax_amt_tab( i );
1174 END IF;
1175 */
1176 -----------------------------------------------------------------------
1177 -- Deleted by Jia Li for Tax Inclusive Computations on 2007/12/11, End
1178
1179 tax_amt_tab( I ) := NVL( v_tax_amt, 0 );
1180 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07, Begin
1181 -----------------------------------------------------------------------
1182 -- Commented out by Shyan for bug 10358786 on 28-Jan-2011 , Begin
1183 -------------------------------------------------------------------
1184 --tax_amt_rate_tax_tab(I) := tax_amt_tab(I); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
1185 -------------------------------------------------------------------
1186 -- Commented out by Shyan for bug 10358786 on 28-Jan-2011 , End
1187
1188 -- Added by Shyan for bug 10358786 on 28-Jan-2011 , Begin
1189 -------------------------------------------------------------------
1190 IF inclu_tax_tab(I) = 'Y' THEN
1191 tax_amt_rate_tax_tab(I) := tax_amt_tab(I);
1192 END IF;
1193 -------------------------------------------------------------------
1194 -- Added by Shyan for bug 10358786 on 28-Jan-2011 , End
1195
1196 func_tax_amt_tab(I) := NVL(ln_v_func_tax_amt, 0); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
1197 tax_amt_non_rate_tab(I) := ln_v_tax_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
1198 base_tax_amount_nr_tab(i):=ln_vamt_nr; --Added by walton for bug#9080017
1199
1200 IF counter = max_iter THEN
1201 /*IF tax_type_tab( I ) IN ( 1, 2 ) THEN */
1202 tax_amt_tab( I ) := ROUND( tax_amt_tab( I ), rounding_factor_tab(I) );
1203 func_tax_amt_tab(I) := ROUND( func_tax_amt_tab( I ), rounding_factor_tab(I)); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
1204 /*ELSE
1205 tax_amt_tab( I ) := ROUND( tax_amt_tab( I ), 2 );
1206 END IF;*/
1207 END IF;
1208
1209 vamt := 0;
1210 v_amt := 0;
1211 v_tax_amt := 0;
1212 ln_v_func_tax_amt := 0; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
1213 ln_v_tax_amt_nr:=0; --Added by walton for bug #9080017
1214 ln_vamt_nr:=0; --Added by walton for bug #9080017
1215
1216 IF end_date_tab(I) = 0 THEN
1217 tax_amt_tab( i ) := 0;
1218 func_tax_amt_tab(I) := 0; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
1219 END IF;
1220 END LOOP;
1221 END LOOP;
1222
1223 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11, Begin
1224 -- the following loop calculates the total tax per rupee and total tax thats not dependent on selling price.
1225 -- and calculation final tax amount
1226 -----------------------------------------------------------------------
1227 FOR I IN 1 .. row_count
1228 LOOP
1229 jai_cmn_utils_pkg.print_log('utils.log','tax_amt_rate_tax_tab(I) = ' || tax_amt_rate_tax_tab(I));
1230 jai_cmn_utils_pkg.print_log('utils.log','tax_amt_non_rate_tab(I) = ' || tax_amt_non_rate_tab(I));
1231 jai_cmn_utils_pkg.print_log('utils.log','inclu flag = ' || inclu_tax_tab(I));
1232
1233 IF inclu_tax_tab(I) = 'Y'
1234 THEN
1235 ln_total_tax_per_rupee := ln_total_tax_per_rupee + NVL(tax_amt_rate_tax_tab(I),0) ;
1236 ln_total_non_rate_tax := nvl(ln_total_non_rate_tax,0) + NVL(tax_amt_non_rate_tab(I),0); --modified by walton for bug#9080017
1237 END IF;
1238 END LOOP;
1239
1240 ln_total_tax_per_rupee := ln_total_tax_per_rupee + 1;
1241
1242 IF ln_total_tax_per_rupee <> 0
1243 THEN
1244 ln_exclusive_price := (NVL(p_tax_amount,0) - ln_total_non_rate_tax) / ln_total_tax_per_rupee;
1245 END If;
1246
1247 jai_cmn_utils_pkg.print_log('utils.log','tot tax per rupee = ' || ln_total_tax_per_rupee || ' totl non tax = ' || ln_total_non_rate_tax );
1248 jai_cmn_utils_pkg.print_log('utils.log','incl sp = ' || p_tax_amount || 'excl price = ' || ln_exclusive_price);
1249
1250 FOR I IN 1 .. row_count
1251 LOOP
1252 -- Commented out by Shyan for bug 10358786 on 28-Jan-2011 , Begin
1253 ------------------------------------------------------------------
1254 --tax_amt_tab (I) := (tax_amt_rate_tax_tab(I) * ln_exclusive_price ) + tax_amt_non_rate_tab(I);
1255 ------------------------------------------------------------------
1256 -- Commented out by Shyan for bug 10358786 on 28-Jan-2011 , End
1257
1258 -- Added by Shyan for bug 10358786 on 28-Jan-2011 , Begin
1259 ------------------------------------------------------------------
1260 tax_amt_tab (i):=0;
1261 IF inclu_tax_tab(I) = 'Y' THEN
1262 IF (tax_type_tab(I) = 1 and ln_assessable_value>1) OR
1263 (tax_type_tab(I) = 4 and ln_vat_assessable_value> 1) OR
1264 (tax_type_tab(I) = 7 and ln_gst_assessable_value >1) THEN
1265
1266 IF tax_type_tab(I) = 1 and ln_assessable_value >1 Then
1267 ln_amount:= ln_assessable_value;
1268 ELSIF tax_type_tab(I) = 4 and ln_vat_assessable_value >1 Then
1269 ln_amount:= ln_vat_assessable_value;
1270 ELSIF tax_type_tab(I) = 7 and ln_gst_assessable_value >1 Then
1271 ln_amount:= ln_gst_assessable_value;
1272 END IF;
1273
1274 IF P1(I) < I AND p1(I) NOT IN (-1, 0) then
1275 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P1(i))* tax_rate_tab(i)/100;
1276 ELSIF P1(I)=0 Then
1277 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
1278 END IF;
1279
1280 IF P2(I) < I AND p2(I) NOT IN (-1, 0) then
1281 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P2(i))* tax_rate_tab(i)/100;
1282 ELSIF P2(I)=0 Then
1283 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
1284 END IF;
1285
1286 IF P3(I) < I AND p3(I) NOT IN (-1, 0) then
1287 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P3(i))* tax_rate_tab(i)/100;
1288 ELSIF P3(I)=0 Then
1289 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
1290 END IF;
1291
1292 IF P4(I) < I AND p4(I) NOT IN (-1, 0) then
1293 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P4(i))* tax_rate_tab(i)/100;
1294 ELSIF P4(I)=0 Then
1295 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
1296 END IF;
1297
1298 IF P5(I) < I AND p5(I) NOT IN (-1, 0) then
1299 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P5(i))* tax_rate_tab(i)/100;
1300 ELSIF P5(I)=0 Then
1301 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
1302 END IF;
1303
1304 IF P6(I) < I AND p6(I) NOT IN (-1, 0) then
1305 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P6(i))* tax_rate_tab(i)/100;
1306 ELSIF P6(I)=0 Then
1307 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
1308 END IF;
1309
1310 IF P7(I) < I AND p7(I) NOT IN (-1, 0) then
1311 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P7(i))* tax_rate_tab(i)/100;
1312 ELSIF P7(I)=0 Then
1313 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
1314 END IF;
1315
1316 IF P8(I) < I AND p8(I) NOT IN (-1, 0) then
1317 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P8(i))* tax_rate_tab(i)/100;
1318 ELSIF P8(I)=0 Then
1319 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
1320 END IF;
1321
1322 IF P9(I) < I AND p9(I) NOT IN (-1, 0) then
1323 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P9(i))* tax_rate_tab(i)/100;
1324 ELSIF P9(I)=0 Then
1325 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
1326 END IF;
1327
1328 IF P10(I) < I AND p10(I) NOT IN (-1, 0) then
1329 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P10(i))* tax_rate_tab(i)/100;
1330 ELSIF P10(I)=0 Then
1331 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
1332 END IF;
1333 ELSE
1334 tax_amt_tab (i) := (tax_amt_rate_tax_tab(I) * ln_exclusive_price ) + tax_amt_non_rate_tab(I);
1335 END IF;
1336 ELSE
1337 IF (tax_type_tab(I) = 1 and ln_assessable_value>1) OR
1338 (tax_type_tab(I) = 4 and ln_vat_assessable_value> 1) OR
1339 (tax_type_tab(I) = 7 and ln_gst_assessable_value >1) THEN
1340
1341 IF tax_type_tab(I) = 1 and ln_assessable_value >1 Then
1342 ln_amount:= ln_assessable_value;
1343 ELSIF tax_type_tab(I) = 4 and ln_vat_assessable_value >1 Then
1344 ln_amount:= ln_vat_assessable_value;
1345 ELSIF tax_type_tab(I) = 7 and ln_gst_assessable_value >1 Then
1346 ln_amount:= ln_gst_assessable_value;
1347
1348 END IF;
1349 ELSE ln_amount:= p_tax_amount;
1350 END IF;
1351 IF P1(I) <I AND p1(I) NOT IN (-1, 0) then
1352 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P1(i))* tax_rate_tab(i)/100;
1353 ELSIF P1(I)=0 Then
1354 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
1355 END IF;
1356
1357 IF P2(I) < I AND p2(I) NOT IN (-1, 0) then
1358 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P2(i))* tax_rate_tab(i)/100;
1359 ELSIF P2(I)=0 Then
1360 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
1361 END IF;
1362
1363 IF P3(I) < I AND p3(I) NOT IN (-1, 0) then
1364 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P3(i))* tax_rate_tab(i)/100;
1365 ELSIF P3(I)=0 Then
1366 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
1367 END IF;
1368
1369 IF P4(I) < I AND p4(I) NOT IN (-1, 0) then
1370 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P4(i))* tax_rate_tab(i)/100;
1371 ELSIF P4(I)=0 Then
1372 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
1373 END IF;
1374
1375 IF P5(I) < I AND p5(I) NOT IN (-1, 0) then
1376 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P5(i))* tax_rate_tab(i)/100;
1377 ELSIF P5(I)=0 Then
1378 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
1379 END IF;
1380
1381 IF P6(I) < I AND p6(I) NOT IN (-1, 0) then
1382 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P6(i))* tax_rate_tab(i)/100;
1383 ELSIF P6(I)=0 Then
1384 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
1385 END IF;
1386
1387 IF P7(I) < I AND p7(I) NOT IN (-1, 0) then
1388 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P7(i))* tax_rate_tab(i)/100;
1389 ELSIF P7(I)=0 Then
1390 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
1391 END IF;
1392
1393 IF P8(I) < I AND p8(I) NOT IN (-1, 0) then
1394 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P8(i))* tax_rate_tab(i)/100;
1395 ELSIF P8(I)=0 Then
1396 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
1397 END IF;
1398
1399 IF P9(I) < I AND p9(I) NOT IN (-1, 0) then
1400 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P9(i))* tax_rate_tab(i)/100;
1401 ELSIF P9(I)=0 Then
1402 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
1403 END IF;
1404
1405 IF P10(I) < I AND p10(I) NOT IN (-1, 0) then
1406 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P10(i))* tax_rate_tab(i)/100;
1407 ELSIF P10(I)=0 Then
1408 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
1409 END IF;
1410 END IF;
1411 ------------------------------------------------------------------
1412 -- Added by Shyan for bug 10358786 on 28-Jan-2011 , End
1413
1414 base_tax_amt_tab(I):=ln_exclusive_price*base_tax_amt_tab(I)+base_tax_amount_nr_tab(i); --Added by walton for bug#9080017
1415 jai_cmn_utils_pkg.print_log('utils.log','in final loop , tax amt is ' ||tax_amt_tab(I));
1416 END LOOP;
1417 -----------------------------------------------------------------------
1418 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11, End
1419
1420
1421 FOR rec in tax_cur(p_line_id) LOOP
1422 IF tax_type_tab(rec.lno) <> 2
1423 THEN
1424 v_tax_amt := v_tax_amt + nvl(tax_amt_tab(rec.lno),0);
1425 END IF;
1426
1427 IF transaction_name = 'AR_LINES_UPDATE'
1428 THEN
1429 IF NVL(rec.adhoc_flag,'N') = 'Y' THEN
1430 UPDATE JAI_AR_TRX_TAX_LINES
1431 SET tax_amount = nvl(rec.tax_amount,0),
1432 base_tax_amount = nvl(rec.base_tax_amount,0),
1433 func_tax_amount = nvl(rec.func_tax_amount,0),
1434 last_update_date = p_last_update_date,
1435 last_updated_by = p_last_updated_by,
1436 last_update_login = p_last_update_login
1437 WHERE link_to_cust_trx_line_id = P_line_id
1438 AND tax_line_no = rec.lno;
1439 ELSE
1440 UPDATE JAI_AR_TRX_TAX_LINES
1441 SET tax_amount = nvl(tax_amt_tab(rec.lno),0),
1442 base_tax_amount = decode(nvl(base_tax_amt_tab(rec.lno), 0), 0, nvl(tax_amt_tab(rec.lno),0), nvl(base_tax_amt_tab(rec.lno), 0)),
1443 func_tax_amount = ROUND(nvl(tax_amt_tab(rec.lno),0) * v_currency_conv_factor, rounding_factor_tab(rec.lno)),
1444 last_update_date = p_last_update_date,
1445 last_updated_by = p_last_updated_by,
1446 last_update_login = p_last_update_login
1447 WHERE link_to_cust_trx_line_id = P_line_id
1448 AND tax_line_no = rec.lno;
1449 END IF;
1450 END IF;
1451 END LOOP;
1452 P_TAX_AMOUNT := nvl(v_tax_amt,0);
1453
1454
1455 /* Added by Ramananda for bug#4407165 */
1456 EXCEPTION
1457 WHEN OTHERS THEN
1458 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
1459 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
1460 app_exception.raise_exception;
1461
1462 END recalculate_tax;
1463
1464 Procedure locator_handler(
1465 p_trx_id NUMBER,
1466 p_flag VARCHAR2 -- DEFAULT 'Y' -- Use jai_constants.yes in the call of this procedure. Ramananda for for File.Sql.35
1467 )
1468 IS
1469 pragma AUTONOMOUS_TRANSACTION; -- Vijay Shankar for Bug# 3985561
1470
1471 CURSOR get_temp_count IS
1472 SELECT count(*)
1473 FROM JAI_AR_TRX_UPDATE_T
1474 WHERE trx_id = p_trx_id;
1475
1476 v_count NUMBER;
1477
1478 /* Added by Ramananda for bug#4407165 */
1479 lv_object_name CONSTANT VARCHAR2(61) := 'jai_ar_utils_pkg.locator_handler';
1480
1481 BEGIN
1482 /*------------------------------------------------------------------------------------------
1483 CHANGE HISTORY for FILENAME: jai_om_rg_pkg.sql
1484 Slno dd/mm/yyyy Author and Change Details
1485 ------------------------------------------------------------------------------------------
1486 1. 31/05/2004 Vijay Shankar for Bug# 3985561, Version: 115.1
1487 Deadlocks being caused due to JAI_AR_TRX_UPDATE_T table as the same row of table is being updated from different forms and triggers.
1488 To resolve this, procedure is made to execute in AUTONOMOUS_TRANSACTION (Nested TRANSACTION CYCLE with COMMIT) mode
1489 This whole procedure is modified as an Table Handler for JAI_AR_TRX_UPDATE_T table. for this purpose a new parameter named p_flag
1490 is added that takes care of INSERT/UPDATE/DELETE on the table
1491
1492 HIGH DEPENDENCY for future bugs
1493
1494 ------------------------------------------------------------------------------------------ */
1495
1496 -- 3985561
1497 If p_flag = 'N' THEN
1498 UPDATE JAI_AR_TRX_UPDATE_T
1499 SET modified_flag = p_flag
1500 WHERE trx_id = p_trx_id;
1501 ELSIF p_flag = 'D' THEN
1502 DELETE FROM JAI_AR_TRX_UPDATE_T
1503 WHERE trx_id = p_trx_id;
1504
1505 ELSE
1506
1507 OPEN get_temp_count;
1508 FETCH get_temp_count INTO v_count;
1509 CLOSE get_temp_count;
1510
1511 IF v_count > 0 THEN
1512
1513 UPDATE JAI_AR_TRX_UPDATE_T
1514 SET modified_flag = p_flag
1515 WHERE trx_id = p_trx_id;
1516
1517 ELSIF p_flag <> 'D' THEN
1518
1519 INSERT INTO JAI_AR_TRX_UPDATE_T(
1520 trx_ID, modified_flag,
1521 -- added, Harshita for Bug 4866533
1522 created_by, creation_date, last_updated_by, last_update_date
1523 ) VALUES (
1524 p_trx_id, p_flag,
1525 -- added, Harshita for Bug 4866533
1526 fnd_global.user_id, sysdate, fnd_global.user_id, sysdate
1527 );
1528
1529 END IF;
1530
1531 END IF;
1532
1533 COMMIT; -- Vijay Shankar for Bug# 3985561
1534
1535 /* Added by Ramananda for bug#4407165 */
1536 EXCEPTION
1537 WHEN OTHERS THEN
1538 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
1539 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
1540 app_exception.raise_exception;
1541
1542 END locator_handler;
1543
1544 /*
1545 FUNCTION get_register_type(i_item_id NUMBER) RETURN VARCHAR2 IS
1546 X_class JAI_OPM_ITM_MASTERS.item_class%type;
1547 CURSOR cur IS SELECT item_class
1548 FROM JAI_OPM_ITM_MASTERS
1549 WHERE ITEM_ID = i_item_id;
1550 BEGIN
1551 open cur;
1552 FETCH cur into X_class;
1553 IF substr(X_class,1,2) = 'RM' or substr(X_class,1,2) = 'CC' then
1554 RETURN 'A';
1555 ELSIF substr(X_class,1,2) = 'CG' THEN
1556 RETURN 'C';
1557 END IF;
1558 END get_register_type;
1559 */
1560
1561 PROCEDURE apps_rel_insert(p_org_id IN NUMBER, p_loc_id IN NUMBER, p_rg_flag IN Varchar2,
1562 p_reg_name IN Varchar2 ,p_complete_flag IN Varchar2, p_cretaed_by IN Number,
1563 last_updated_by IN Number, p_last_update_login IN Number, p_creation_date IN Date, last_update_date IN Date
1564 ) IS
1565
1566 -- v_paddr v$session.paddr%type;
1567
1568 /* Added by Ramananda for bug#4407165 */
1569 lv_object_name CONSTANT VARCHAR2(61) := 'jai_ar_utils_pkg.apps_rel_insert';
1570
1571 BEGIN
1572
1573 /*------------------------------------------------------------------------------------------
1574 FILENAME: apps_rel_insert.sql
1575
1576 CHANGE HISTORY:
1577 S.No Date Author and Details
1578
1579 --------------------------------------------------------------------------------------------*/
1580 /*
1581 Select paddr INTO v_paddr From v$session
1582 Where sid = (Select sid From v$mystat Where Rownum = 1);
1583 */--commneted by GSri on 22-jun-01 for tuning
1584
1585 --added by GSri -n 22-jun-01
1586 /*Select paddr into v_paddr
1587 From v$session
1588 Where audsid = userenv('SESSIONID');
1589 */
1590 Insert Into JAI_AR_TRX_APPS_RELS_T(
1591 Organization_ID,Location_ID,RG_Update_Flag,Register_Type,
1592 Once_completed_flag
1593 /* Bug 4535701. Commented by Lakshmi Gopalsami
1594 * As part of global temporary table
1595 * WHO columns has been removed
1596 , paddr,
1597 created_by, last_updated_by, last_update_login, creation_date, last_update_date */
1598 ) Values (
1599 p_org_id,p_loc_id,p_rg_flag,p_reg_name,
1600 p_complete_flag/*, v_paddr,
1601 p_cretaed_by, last_updated_by, p_last_update_login, p_creation_date, last_update_date */
1602 );
1603
1604 COMMIT;
1605
1606
1607 /* Added by Ramananda for bug#4407165 */
1608 EXCEPTION
1609 WHEN OTHERS THEN
1610 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
1611 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
1612 app_exception.raise_exception;
1613
1614 END apps_rel_insert;
1615
1616 /*Added by Qinglei for the Advanced Receipt for Service Tax enhancement Bug 13361952 Begin*/
1617 FUNCTION get_tcs_flag RETURN NUMBER
1618 IS
1619 CURSOR cur_get_tcs_flag IS
1620 SELECT COUNT(*)
1621 FROM JAI_RGM_REGISTRATIONS a,
1622 JAI_RGM_DEFINITIONS b
1623 WHERE a.attribute_code = 'AR_RECEIPT_CLASS'
1624 AND a.registration_type = 'OTHERS'
1625 AND a.attribute_type_code = 'OTHERS'
1626 AND a.regime_id = b.regime_id
1627 AND b.regime_code IN( 'TCS','SERVICE') ;
1628 ln_jai_tcs_flag NUMBER;
1629 BEGIN
1630 OPEN cur_get_tcs_flag;
1631 FETCH cur_get_tcs_flag INTO ln_jai_tcs_flag;
1632 CLOSE cur_get_tcs_flag;
1633
1634 RETURN ln_jai_tcs_flag;
1635 END get_tcs_flag;
1636
1637 FUNCTION get_receipt_class_flag(pn_receipt_method_id NUMBER) RETURN NUMBER
1638 IS
1639 CURSOR c_receipt_class(cp_receipt_method_id NUMBER)
1640 IS
1641 SELECT '1' flag
1642 FROM ar_receipt_classes a,
1643 ar_receipt_methods b
1644 WHERE a.receipt_class_id = b.receipt_class_id
1645 AND a.name IN(SELECT a.attribute_value receipt_class
1646 FROM JAI_RGM_REGISTRATIONS a,
1647 JAI_RGM_DEFINITIONS b
1648 WHERE a.attribute_code = 'AR_RECEIPT_CLASS'
1649 AND a.registration_type = 'OTHERS'
1650 AND a.attribute_type_code = 'OTHERS'
1651 AND a.regime_id = b.regime_id
1652 AND b.regime_code IN ('TCS','SERVICE'))
1653 AND b.receipt_method_id = cp_receipt_method_id;
1654 ln_receipt_class_flag NUMBER;
1655 BEGIN
1656 OPEN c_receipt_class(pn_receipt_method_id);
1657 FETCH c_receipt_class INTO ln_receipt_class_flag;
1658 CLOSE c_receipt_class;
1659 RETURN ln_receipt_class_flag;
1660 END get_receipt_class_flag;
1661
1662 FUNCTION is_receipt_taxes_exists(pn_cash_receipt_id NUMBER) RETURN NUMBER
1663 IS
1664 CURSOR c_receipt_taxes_exists(cp_cash_receipt_id NUMBER)
1665 IS
1666 SELECT '1' flag
1667 FROM jai_cmn_document_taxes
1668 WHERE source_doc_id = cp_cash_receipt_id
1669 AND source_doc_type = 'CASH';
1670 ln_receipt_taxes_flag NUMBER;
1671 BEGIN
1672 OPEN c_receipt_taxes_exists(pn_cash_receipt_id);
1673 FETCH c_receipt_taxes_exists INTO ln_receipt_taxes_flag;
1674 CLOSE c_receipt_taxes_exists;
1675 RETURN ln_receipt_taxes_flag;
1676 END is_receipt_taxes_exists;
1677 /*Added by Qinglei for the Advanced Receipt for Service Tax enhancement Bug 13361952 End*/
1678 END jai_ar_utils_pkg;
[Home] [Help]