1 PACKAGE BODY jai_ar_utils_pkg
2 /* $Header: jai_ar_utils.plb 120.6 2008/01/21 11:25:58 ssumaith 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
23 --------- ---------- -------------------------------------------------------------
24 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
25 as required for CASE COMPLAINCE.
26 */
27
28 PROCEDURE recalculate_tax(transaction_name VARCHAR2,
29 P_tax_category_id NUMBER,
30 p_header_id NUMBER,
31 p_line_id NUMBER,
32 p_assessable_value NUMBER default 0,
33 p_tax_amount IN OUT NOCOPY NUMBER,
34 p_currency_conv_factor NUMBER,
35 p_inventory_item_id NUMBER,
36 p_line_quantity NUMBER,
37 p_uom_code VARCHAR2,
38 p_vendor_id NUMBER,
39 p_currency VARCHAR2,
40 p_creation_date DATE,
41 p_created_by NUMBER,
42 p_last_update_date DATE,
43 p_last_updated_by NUMBER,
44 p_last_update_login NUMBER ,
45 p_vat_assessable_Value NUMBER Default 0
46 )
47 IS
48 TYPE num_tab IS TABLE OF number
49 INDEX BY BINARY_INTEGER;
50 TYPE tax_amt_num_tab IS TABLE OF number
51 INDEX BY BINARY_INTEGER;
52 p1 num_tab;
53 p2 num_tab;
54 p3 num_tab;
55 p4 num_tab;
56 p5 num_tab;
57
58
59 -- Date 01-NOV-2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
60 -- START BUG 5228046
61
62 p6 num_tab;
63 p7 num_tab;
64 p8 num_tab;
65 p9 num_tab;
66 p10 num_tab;
67
68 -- END BUG 5228046
69
70 tax_rate_tab num_tab;
71 tax_type_tab num_tab;
72 tax_amt_tab tax_amt_num_tab;
73 base_tax_amt_tab tax_amt_num_tab;
74 end_date_tab num_tab;
75 rounding_factor_tab num_tab;
76
77
78 /* Added by Ramananda for bug#4407165 */
79 lv_object_name CONSTANT VARCHAR2(61) := 'jai_ar_utils_pkg.recalculate_tax';
80
81 bsln_amt number; -- := p_tax_amount; --Ramananda for File.Sql.35
82 v_conversion_rate number; -- := 0; --Ramananda for File.Sql.35
83 v_currency_conv_factor number; -- := p_currency_conv_factor; --Ramananda for File.Sql.35
84 v_tax_amt number; --SACSETHI for File.Sql.35
85 vamt number; --SACSETHI for File.Sql.35
86 v_amt number;
87 row_count number ; --SACSETHI for File.Sql.35
88
89 counter number;
90 max_iter number ; --SACSETHI for File.Sql.35
91
92
93 CURSOR tax_cur(p_line_id IN Number) IS
94 SELECT a.tax_id, a.tax_line_no lno, b.adhoc_flag, a.base_tax_amount, a.func_tax_amount,
95 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,
96 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,
97 b.tax_rate, b.tax_amount, b.uom_code, b.end_date valid_date, b.rounding_factor,
98 DECODE(rgm_tax_types.regime_Code,jai_constants.vat_regime, 4, /* added by ssumaith - bug# 4245053*/
99 decode(upper(b.tax_type),
100 'EXCISE', 1,
101 'ADDL. EXCISE', 1,
102 'OTHER EXCISE', 1,
103 'CVD', 1,
104 'TDS', 2,
105 -- Modified by Jia Li for Tax Inclusive Computations on 2007/12/11, Begin
106 -- change tax_type_val
107 -------------------------------------------------------------------------
108 'EXCISE_EDUCATION_CESS' , 6,
109 'CVD_EDUCATION_CESS' , 6,
110 'SH_EXCISE_EDUCATION_CESS' , 6,--Added by kundan kumar for bug#5907436
111 'SH_CVD_EDUCATION_CESS' , 6, --Added by kundan kumar for bug#5907436 Added by kunkumar for forward porting
112 -------------------------------------------------------------------------
113 -- Modified by Jia Li for Tax Inclusive Computations on 2007/12/11, End
114 0
115 )
116 )tax_type_val,
117 b.mod_cr_Percentage, b.vendor_id, b.tax_type
118 , b.inclusive_tax_flag -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
119 FROM JAI_AR_TRX_TAX_LINES a, JAI_CMN_TAXES_ALL b ,
120 jai_regime_tax_types_v rgm_tax_types /* added by ssumaith - bug# 4245053*/
121 WHERE a.link_to_cust_trx_line_id = p_line_id
122 AND a.tax_id = b.tax_id
123 AND rgm_tax_types.tax_type (+) = b.tax_type /* added by ssumaith - bug# 4245053*/
124 ORDER BY a.tax_line_no;
125
126 CURSOR uom_class_cur(p_line_uom_code IN varchar2, p_tax_line_uom_code IN varchar2) IS
127 SELECT A.uom_class
128 FROM mtl_units_of_measure A, mtl_units_of_measure B
129 WHERE A.uom_code = p_line_uom_code
130 AND B.uom_code = p_tax_line_uom_code
131 AND A.uom_class = B.uom_class;
132
133 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11, Begin
134 -------------------------------------------------------------------------
135 TYPE char_tab IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
136 tax_amt_rate_tax_tab TAX_AMT_NUM_TAB;
137 tax_amt_non_rate_tab TAX_AMT_NUM_TAB;
138 func_tax_amt_tab TAX_AMT_NUM_TAB;
139 tax_rate_zero_tab NUM_TAB;
140 tax_rate_per_rupee NUM_TAB;
141 tax_target_tab NUM_TAB;
142 inclu_tax_tab CHAR_TAB;
143 ln_assessable_value NUMBER;
144 ln_vat_assessable_value NUMBER;
145 ln_vamt_nr NUMBER(38,10);
146 ln_bsln_amt_nr NUMBER(38,10);
147 ln_v_tax_amt_nr NUMBER(38,10);
148 ln_v_func_tax_amt NUMBER(38,10);
149 ln_exclusive_price NUMBER(38,10);
150 ln_total_non_rate_tax NUMBER(38,10);
151 ln_total_tax_per_rupee NUMBER(38,10);
152 -------------------------------------------------------------------------
153 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11, End
154
155 BEGIN
156
157 /*---------------------------------------------
158
159 1. 2005/03/10 ssumaith - bug# 4245053 - File version 115.1
160
161 Taxes under the vat regime needs to be calculated based on the vat assessable value setup done.
162 In the vendor additional information screen and supplier additional information screen, a place
163 has been given to capture the vat assessable value.
164
165 This needs to be used for the calculation of the taxes registered under vat regime.
166
167 This change has been done by using the view jai_regime_tax_types_v - and outer joining it with the
168 JAI_CMN_TAXES_ALL table based on the tax type column.
169
170 Added a parameter p_vat_assessable_Value NUMBER for this procedure.
171
172 Dependency due to this bug - Huge
173
174 It uses many tables . views and indexes created as part of the VAT base bug - 4245089
175 This patch should always be accompanied by the VAT consolidated patch - 4245089
176
177 2 04-Aug-2005 Bug4535701. Added by Lakshmi Gopalsami
178 Commented the references to WHO columns for
179 global temporary table JAI_AR_TRX_APPS_RELS_T
180
181 Future Dependencies For the release Of this Object:-
182 (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/
183 A datamodel change )
184
185 ----------------------------------------------------------------------------------------------------------------------------------------------------
186 Current Version Current Bug Dependent Files Version Author Date Remarks
187 Of File On Bug/Patchset Dependent On
188 jai_ar_utils_pkg.recalculate_tax_p.sql
189 ----------------------------------------------------------------------------------------------------------------------------------------------------
190 115.1 4245053 IN60106 + ssumaith Service Tax and VAT Infrastructure are created
191 4146708 + based on the bugs - 4146708 and 4545089 respectively.
192 4245089
193 *************************************************************************************************************************/
194
195 bsln_amt := p_tax_amount; --Ramananda for File.Sql.35
196 v_conversion_rate := 0; --Ramananda for File.Sql.35
197 v_currency_conv_factor := p_currency_conv_factor; --Ramananda for File.Sql.35
198 max_iter := 15;
199 v_tax_amt :=0 ;
200 vamt := 0 ;
201 row_count :=0 ;
202
203 ln_vamt_nr := 0; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
204 ln_v_tax_amt_nr := 0; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
205 ln_bsln_amt_nr := 0; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
206
207 FOR rec in tax_cur(p_line_id) LOOP
208 p1(rec.lno) := nvl(rec.p_1,-1);
209 p2(rec.lno) := nvl(rec.p_2,-1);
210 p3(rec.lno) := nvl(rec.p_3,-1);
211 p4(rec.lno) := nvl(rec.p_4,-1);
212 p5(rec.lno) := nvl(rec.p_5,-1);
213
214 -- Date 01-NOV-2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
215 -- START BUG 5228046
216
217 p6(rec.lno) := nvl(rec.p_6,-1);
218 p7(rec.lno) := nvl(rec.p_7,-1);
219 p8(rec.lno) := nvl(rec.p_8,-1);
220 p9(rec.lno) := nvl(rec.p_9,-1);
221 p10(rec.lno) := nvl(rec.p_10,-1);
222
223 -- END BUG 5228046
224
225 tax_rate_tab(rec.lno) := nvl(rec.tax_rate,0);
226 tax_type_tab(rec.lno) := rec.tax_type_val;
227 tax_amt_tab(rec.lno) := 0;
228 base_tax_amt_tab(rec.lno) := 0;
229 rounding_factor_tab(rec.lno) := NVL(rec.rounding_factor, 0);
230
231 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11, Begin
232 -----------------------------------------------------------------------
233 tax_rate_per_rupee(rec.lno) := NVL(rec.tax_rate, 0)/100;
234 ln_total_tax_per_rupee := 0;
235 inclu_tax_tab(rec.lno) := NVL(rec.inclusive_tax_flag, 'N');
236
237 IF rec.tax_rate IS NULL
238 THEN
239 tax_rate_zero_tab(rec.lno) := 0;
240 ELSIF rec.tax_rate = 0
241 THEN
242 tax_rate_zero_tab(rec.lno) := -9999;
243 ELSE
244 tax_rate_zero_tab(rec.lno) := rec.tax_rate;
245 END IF; -- rec.tax_rate is null
246
247 tax_amt_rate_tax_tab(rec.lno) := 0;
248 tax_amt_non_rate_tab(rec.lno) := 0;
249 -----------------------------------------------------------------------
250 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11, End
251
252 IF tax_rate_tab(rec.lno) = 0
253 THEN
254 FOR uom_cls IN uom_class_cur(p_uom_code, rec.uom_code) LOOP
255 INV_CONVERT.inv_um_conversion( p_uom_code, rec.uom_code, p_inventory_item_id, v_conversion_rate);
256 IF nvl(v_conversion_rate, 0) <= 0
257 THEN
258 INV_CONVERT.inv_um_conversion( p_uom_code, rec.uom_code, 0, v_conversion_rate);
259 IF nvl(v_conversion_rate, 0) <= 0
260 THEN
261 v_conversion_rate := 0;
262 END IF;
263 END IF;
264 tax_amt_tab(rec.lno) := ROUND(nvl(rec.tax_amount * v_conversion_rate, 0) * p_line_quantity, NVL(rec.rounding_factor, 0));
265 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11, Begin
266 -----------------------------------------------------------------------
267 tax_amt_non_rate_tab(rec.lno) := NVL(rec.tax_amount * v_conversion_rate, 0) * p_line_quantity;
268 base_tax_amt_tab(rec.lno) := tax_amt_non_rate_tab(rec.lno);
269 -----------------------------------------------------------------------
270 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11, End
271 END LOOP;
272 END IF;
273
274 IF rec.valid_date is NULL or rec.valid_date >= sysdate
275 THEN
276 end_date_tab(rec.lno) := 1;
277 ELSE
278 tax_amt_tab(rec.lno) := 0;
279 end_date_tab(rec.lno) := 0;
280 END IF;
281 row_count := row_count + 1;
282 END LOOP;
283
284 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11, Begin
285 -----------------------------------------------------------------------
286 IF p_assessable_value <> p_tax_amount
287 THEN
288 ln_assessable_value := p_assessable_value;
289 ELSE
290 ln_assessable_value := 1;
291 END IF;
292
293 IF p_vat_assessable_value <> p_tax_amount
294 THEN
295 ln_vat_assessable_value := p_vat_assessable_value;
296 ELSE
297 ln_vat_assessable_value := 1;
298 END IF;
299 -----------------------------------------------------------------------
300 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11, End
301
302 FOR I in 1..row_count LOOP
303 -- Delete by Jia Li for Tax Inclusive Computations on 2007/12/11, Begin
304 -----------------------------------------------------------------------
305 /*
306 IF tax_type_tab(I) = 1
307 THEN
308 bsln_amt := p_assessable_value;
309 ELSIF tax_type_tab(I) = 4 THEN
310 bsln_amt := p_vat_assessable_value;
311 ELSE
312 bsln_amt := p_tax_amount;
313 END IF;
314 */
315 -----------------------------------------------------------------------
316 -- Delete by Jia Li for Tax Inclusive Computations on 2007/12/11, End
317
318 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11, Begin
319 -----------------------------------------------------------------------
320 IF end_date_tab(I) <> 0
321 THEN
322 IF tax_type_tab(I) = 1
323 THEN
324 IF ln_assessable_value = 1
325 THEN
326 bsln_amt := 1;
327 ln_bsln_amt_nr := 0;
328 ELSE
329 bsln_amt := 0;
330 ln_bsln_amt_nr := ln_assessable_value;
331 END IF;
332 ELSIF tax_type_tab(I) = 4
333 THEN
334 IF ln_vat_assessable_value = 1
335 THEN
336 bsln_amt := 1;
337 ln_bsln_amt_nr := 0;
338 ELSE
339 bsln_amt := 0;
340 ln_bsln_amt_nr := ln_vat_assessable_value;
341 END IF;
342 ELSIF tax_type_tab(I) = 6
343 THEN
344 bsln_amt := 0;
345 ln_bsln_amt_nr := 0;
346 ELSE
347 bsln_amt := 1;
348 ln_bsln_amt_nr := 0;
349 END IF; -- tax_type_tab(I) = 1
350 -----------------------------------------------------------------------
351 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11, End
352
353 IF tax_rate_tab(I) <> 0
354 THEN
355 IF p1(I) < I and p1(I) not in (-1,0) THEN
356 vamt := vamt + nvl(tax_amt_tab(p1(I)),0);
357 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
358 ELSIF p1(I) = 0 THEN
359 vamt := vamt + bsln_amt;
360 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
361 END IF;
362
363 IF p2(I) < I and p2(I) not in (-1,0) THEN
364 vamt := vamt + nvl(tax_amt_tab(p2(I)),0);
365 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
366 ELSIF p2(I) = 0 THEN
367 vamt := vamt + bsln_amt;
368 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
369 END IF;
370
371 IF p3(I) < I and p3(I) not in (-1,0) THEN
372 vamt := vamt + nvl(tax_amt_tab(p3(I)),0);
373 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
374 ELSIF p3(I) = 0 THEN
375 vamt := vamt + bsln_amt;
376 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
377 END IF;
378
379 IF p4(I) < I and p4(I) not in (-1,0) THEN
380 vamt := vamt + nvl(tax_amt_tab(p4(I)),0);
381 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
382 ELSIF p4(I) = 0 THEN
383 vamt := vamt + bsln_amt;
384 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
385 END IF;
386
387 IF p5(I) < I and p5(I) not in (-1,0) THEN
388 vamt := vamt + nvl(tax_amt_tab(p5(I)),0);
389 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
390 ELSIF p5(I) = 0 THEN
391 vamt := vamt + bsln_amt;
392 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
393 END IF;
394
395 -- Date 01-NOV-2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
396 -- START BUG 5228046
397 IF p6(I) < I and p6(I) not in (-1,0) THEN
398 vamt := vamt + nvl(tax_amt_tab(p6(I)),0);
399 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
400 ELSIF p6(I) = 0 THEN
401 vamt := vamt + bsln_amt;
402 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
403 END IF;
404
405 IF p7(I) < I and p7(I) not in (-1,0) THEN
406 vamt := vamt + nvl(tax_amt_tab(p7(I)),0);
407 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
408 ELSIF p7(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 p8(I) < I and p8(I) not in (-1,0) THEN
414 vamt := vamt + nvl(tax_amt_tab(p8(I)),0);
415 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
416 ELSIF p8(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 p9(I) < I and p9(I) not in (-1,0) THEN
422 vamt := vamt + nvl(tax_amt_tab(p9(I)),0);
423 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
424 ELSIF p9(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 p10(I) < I and p10(I) not in (-1,0) THEN
430 vamt := vamt + nvl(tax_amt_tab(p10(I)),0);
431 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
432 ELSIF p10(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 -- END BUG 5228046
438
439
440 v_tax_amt := v_tax_amt + (vamt * (tax_rate_tab(I)/100));
441 base_tax_amt_tab(I) := vamt;
442 tax_amt_tab(I) := nvl(tax_amt_tab(I),0) + v_tax_amt;
443
444 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11, Begin
445 -----------------------------------------------------------------------
446 ln_v_tax_amt_nr := ln_v_tax_amt_nr + (ln_vamt_nr * (tax_rate_tab(I)/100));
447 tax_amt_non_rate_tab(I) := NVL(tax_amt_non_rate_tab(I), 0) + ln_v_tax_amt_nr;
448 tax_amt_rate_tax_tab(I) := tax_amt_tab(I);
449 -----------------------------------------------------------------------
450 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11, End
451
452 /*IF end_date_tab(I) = 0
453 THEN
454
455 tax_amt_tab(I) := 0;
456 base_tax_amt_tab(I) := 0;
457 ELSIF end_date_tab(I) = 1 THEN
458 IF tax_type_tab(I) IN (1, 2)
459 THEN
460 v_tax_amt := ROUND(v_tax_amt);
461 END IF;
462 tax_amt_tab(I) := nvl(tax_amt_tab(I),0) + v_tax_amt;
463 END IF;*/
464 vamt := 0;
465 v_tax_amt := 0;
466 ln_vamt_nr := 0; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
467 ln_v_tax_amt_nr := 0; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
468
469 END IF;
470 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11, Begin
471 -----------------------------------------------------------------------
472 ELSE
473 tax_amt_tab(I) := 0;
474 base_tax_amt_tab(I) := 0;
475 END IF; -- end_date_tab(I) <> 0
476 -----------------------------------------------------------------------
477 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11, End
478 END LOOP;
479
480 /*for i in 1 .. row_count loop
481 insert into xc values( ' Tax Amt from I loop in bkend Line ' || to_char( i ) || ' is ' || to_char( tax_amt_tab( I ) ) );
482 end loop;*/
483 FOR I in 1..row_count LOOP
484 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11, Begin
485 -----------------------------------------------------------------------
486 IF end_date_tab(I) <> 0
487 THEN
488 -----------------------------------------------------------------------
489 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11, End
490 IF tax_rate_tab(I) <> 0 THEN
491 IF p1(I) > I THEN
492 vamt := vamt + nvl(tax_amt_tab(p1(I)),0);
493 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
494 END IF;
495 IF p2(I) > I THEN
496 vamt := vamt + nvl(tax_amt_tab(p2(I)),0);
497 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
498 END IF;
499 IF p3(I) > I THEN
500 vamt := vamt + nvl(tax_amt_tab(p3(I)),0);
501 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
502 END IF;
503 IF p4(I) > I THEN
504 vamt := vamt + nvl(tax_amt_tab(p4(I)),0);
505 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
506 END IF;
507 IF p5(I) > I THEN
508 vamt := vamt + nvl(tax_amt_tab(p5(I)),0);
509 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
510 END IF;
511
512 -- Date 01-NOV-2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
513 -- START BUG 5228046
514 IF p6(I) > I THEN
515 vamt := vamt + nvl(tax_amt_tab(p6(I)),0);
516 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
517 END IF;
518 IF p7(I) > I THEN
519 vamt := vamt + nvl(tax_amt_tab(p7(I)),0);
520 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
521 END IF;
522 IF p8(I) > I THEN
523 vamt := vamt + nvl(tax_amt_tab(p8(I)),0);
524 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
525 END IF;
526 IF p9(I) > I THEN
527 vamt := vamt + nvl(tax_amt_tab(p9(I)),0);
528 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
529 END IF;
530 IF p10(I) > I THEN
531 vamt := vamt + nvl(tax_amt_tab(p10(I)),0);
532 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
533 END IF;
534
535 -- END BUG 5228046
536
537
538 v_tax_amt := v_tax_amt + (vamt * (tax_rate_tab(I)/100));
539
540 base_tax_amt_tab(I) := vamt; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
541 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
542
543 IF vamt <> 0 THEN
544 base_tax_amt_tab(I) := base_tax_amt_tab(I) + vamt;
545 END IF;
546
547 tax_amt_tab(I) := nvl(tax_amt_tab(I),0) + v_tax_amt;
548 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11, Begin
549 -----------------------------------------------------------------------
550 tax_amt_non_rate_tab(I) := NVL(tax_amt_non_rate_tab(I), 0) * ln_v_tax_amt_nr;
551 tax_amt_rate_tax_tab(I) := tax_amt_tab(I);
552 ln_vamt_nr := 0;
553 ln_v_tax_amt_nr := 0;
554 -----------------------------------------------------------------------
555 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11, End
556
557 /*IF end_date_tab(I) = 0 THEN
558
559 tax_amt_tab(I) := 0;
560 base_tax_amt_tab(I) := 0;
561 ELSIF end_date_tab(I) = 1 THEN
562 IF tax_type_tab(I) IN (1, 2)
563 THEN
564 v_tax_amt := ROUND(v_tax_amt);
565 END IF;
566 tax_amt_tab(I) := nvl(tax_amt_tab(I),0) + v_tax_amt;
567 END IF;*/
568 vamt := 0;
569 v_tax_amt := 0;
570 END IF;
571
572 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11, Begin
573 -----------------------------------------------------------------------
574 ELSE
575 base_tax_amt_tab(I) := vamt;
576 tax_amt_tab(I) := 0;
577 END IF; -- end_date_tab(I) <> 0
578 -----------------------------------------------------------------------
579 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11, End
580 END LOOP;
581
582
583 /*for i in 1 .. row_count loop
584 insert into xc values( ' Tax Amt from II loop in bkend Line ' || to_char( i ) || ' is ' || to_char( tax_amt_tab( I ) ) );
585 end loop;*/
586 FOR counter IN 1 .. max_iter LOOP
587 vamt := 0;
588 ln_vamt_nr := 0; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/07
589 v_tax_amt := 0;
590
591 FOR i IN 1 .. row_count LOOP
592 --changed > to <> allow -ve tax rate computation - Gaurav 06-dec-99
593 -- Deleted by Jia Li for Tax Inclusive Computations on 2007/12/11, Begin
594 ------------------------------------------------------------------------
595 /*
596 IF tax_rate_tab( i ) <> 0 THEN
597 IF tax_type_tab( I ) = 1 THEN
598 v_amt := p_assessable_value;
599 ELSIF tax_type_tab( I ) = 4 THEN
600 v_amt := p_vat_assessable_value;
601 ELSE
602 IF p_assessable_value IN ( 0, -1 ) OR tax_type_tab( I ) <> 1 THEN
603 v_amt := p_tax_amount;
604 END IF;
605 END IF;
606 */
607 ------------------------------------------------------------------------
608 -- Deleted by Jia Li for Tax Inclusive Computations on 2007/12/11, End
609
610 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11, Begin
611 -----------------------------------------------------------------------
612 IF ( tax_rate_tab(I) <> 0 OR tax_rate_zero_tab(I) = -9999 )
613 AND
614 ( end_date_tab(I) <> 0 )
615 THEN
616 IF tax_type_tab(I) = 1
617 THEN
618 IF ln_assessable_value = 1
619 THEN
620 v_amt := 1;
621 ln_bsln_amt_nr := 0;
622 ELSE
623 v_amt := 0;
624 ln_bsln_amt_nr := ln_assessable_value;
625 END IF;
626 ELSIF tax_type_tab(I) = 4
627 THEN
628 IF ln_vat_assessable_value = 1
629 THEN
630 v_amt := 1;
631 ln_bsln_amt_nr := 0;
632 ELSE
633 v_amt := 0;
634 ln_bsln_amt_nr := ln_vat_assessable_value;
635 END IF;
636 ELSIF tax_type_tab(I) = 6
637 THEN
638 v_amt := 0;
639 ln_bsln_amt_nr := 0;
640 ELSE
641 v_amt := 1;
642 ln_bsln_amt_nr := 0;
643 END IF; -- tax_type_tab(I) = 1
644 -----------------------------------------------------------------------
645 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11, End
646
647 IF p1( i ) <> -1 THEN
648 IF p1( i ) <> 0 THEN
649 vamt := vamt + tax_amt_tab( p1( I ) );
650 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
651 ELSIF p1(i) = 0 THEN
652 vamt := vamt + v_amt;
653 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
654 END IF;
655 END IF;
656 IF p2( i ) <> -1 THEN
657 IF p2( i ) <> 0 THEN
658 vamt := vamt + tax_amt_tab( p2( I ) );
659 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
660 ELSIF p2(i) = 0 THEN
661 vamt := vamt + v_amt;
662 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
663 END IF;
664 END IF;
665 IF p3( i ) <> -1 THEN
666 IF p3( i ) <> 0 THEN
667 vamt := vamt + tax_amt_tab( p3( I ) );
668 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
669 ELSIF p3(i) = 0 THEN
670 vamt := vamt + v_amt;
671 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
672 END IF;
673 END IF;
674 IF p4( i ) <> -1 THEN
675 IF p4( i ) <> 0 THEN
676 vamt := vamt + tax_amt_tab( p4( i ) );
677 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
678 ELSIF p4(i) = 0 THEN
679 vamt := vamt + v_amt;
680 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
681 END IF;
682 END IF;
683
684 IF p5( i ) <> -1 THEN
685 IF p5( i ) <> 0 THEN
686 vamt := vamt + tax_amt_tab( p5( i ) );
687 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
688 ELSIF p5(i) = 0 THEN
689 vamt := vamt + v_amt;
690 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
691 END IF;
692 END IF;
693
694 -- Date 01-NOV-2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
695 -- START BUG 5228046
696
697 IF p6( i ) <> -1 THEN
698 IF p6( i ) <> 0 THEN
699 vamt := vamt + tax_amt_tab( p6( I ) );
700 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
701 ELSIF p6(i) = 0 THEN
702 vamt := vamt + v_amt;
703 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
704 END IF;
705 END IF;
706 IF p7( i ) <> -1 THEN
707 IF p7( i ) <> 0 THEN
708 vamt := vamt + tax_amt_tab( p7( I ) );
709 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
710 ELSIF p7(i) = 0 THEN
711 vamt := vamt + v_amt;
712 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
713 END IF;
714 END IF;
715 IF p8( i ) <> -1 THEN
716 IF p8( i ) <> 0 THEN
717 vamt := vamt + tax_amt_tab( p8( I ) );
718 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
719 ELSIF p8(i) = 0 THEN
720 vamt := vamt + v_amt;
721 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
722 END IF;
723 END IF;
724 IF p9( i ) <> -1 THEN
725 IF p9( i ) <> 0 THEN
726 vamt := vamt + tax_amt_tab( p9( i ) );
727 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
728 ELSIF p9(i) = 0 THEN
729 vamt := vamt + v_amt;
730 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
731 END IF;
732 END IF;
733 IF p10( i ) <> -1 THEN
734 IF p10( i ) <> 0 THEN
735 vamt := vamt + tax_amt_tab( p10( i ) );
736 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
737 ELSIF p10(i) = 0 THEN
738 vamt := vamt + v_amt;
739 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
740 END IF;
741 END IF;
742
743 -- END BUG 5228046
744
745 v_tax_amt := v_tax_amt + ( vamt * ( tax_rate_tab( i )/100));
746 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11, Begin
747 -----------------------------------------------------------------------
748 base_tax_amt_tab(I) := vamt;
749 tax_target_tab(I) := vamt;
750 ln_v_func_tax_amt := v_tax_amt + ( vamt * ( tax_rate_tab(I)/100 ));
751 ln_v_tax_amt_nr := ln_v_tax_amt_nr+( ln_vamt_nr*(tax_rate_tab(i)/100));
752 v_tax_amt := v_tax_amt + ( vamt * ( tax_rate_tab(I)/100 ));
753 ELSIF tax_rate_tab(I) = 0
754 THEN
755 base_tax_amt_tab(I) := tax_amt_tab(I);
756 v_tax_amt := tax_amt_tab(I);
757 tax_target_tab(I) := v_tax_amt;
758 ln_v_tax_amt_nr := tax_amt_non_rate_tab(i);
759 ELSIF end_date_tab(I) = 0
760 THEN
761 tax_amt_tab(I) := 0;
762 base_tax_amt_tab(I) := 0;
763 tax_target_tab(I) := 0;
764 END IF; -- (tax_rate_tab(I) <> 0 OR tax_rate_zero_tab(I) = -9999) AND (end_date_tab(I) <> 0)
765 -----------------------------------------------------------------------
766 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11, End
767
768 -- Deleted by Jia Li for Tax Inclusive Computations on 2007/12/11, Begin
769 -----------------------------------------------------------------------
770 /*
771 ELSE
772 v_tax_amt := tax_amt_tab( i );
773 END IF;
774 */
775 -----------------------------------------------------------------------
776 -- Deleted by Jia Li for Tax Inclusive Computations on 2007/12/11, End
777
778 tax_amt_tab( I ) := NVL( v_tax_amt, 0 );
779 tax_amt_rate_tax_tab(I) := tax_amt_tab(I); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
780 func_tax_amt_tab(I) := NVL(ln_v_func_tax_amt, 0); -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
781 tax_amt_non_rate_tab(I) := ln_v_tax_amt_nr; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
782
783 IF counter = max_iter THEN
784 /*IF tax_type_tab( I ) IN ( 1, 2 ) THEN */
785 tax_amt_tab( I ) := ROUND( tax_amt_tab( I ), rounding_factor_tab(I) );
786 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
787 /*ELSE
788 tax_amt_tab( I ) := ROUND( tax_amt_tab( I ), 2 );
789 END IF;*/
790 END IF;
791
792 vamt := 0;
793 v_amt := 0;
794 v_tax_amt := 0;
795 ln_v_func_tax_amt := 0; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
796
797 IF end_date_tab(I) = 0 THEN
798 tax_amt_tab( i ) := 0;
799 func_tax_amt_tab(I) := 0; -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11
800 END IF;
801 END LOOP;
802 END LOOP;
803
804 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11, Begin
805 -- the following loop calculates the total tax per rupee and total tax thats not dependent on selling price.
806 -- and calculation final tax amount
807 -----------------------------------------------------------------------
808 FOR I IN 1 .. row_count
809 LOOP
810 jai_cmn_utils_pkg.print_log('utils.log','tax_amt_rate_tax_tab(I) = ' || tax_amt_rate_tax_tab(I));
811 jai_cmn_utils_pkg.print_log('utils.log','tax_amt_non_rate_tab(I) = ' || tax_amt_non_rate_tab(I));
812 jai_cmn_utils_pkg.print_log('utils.log','inclu flag = ' || inclu_tax_tab(I));
813
814 IF inclu_tax_tab(I) = 'Y'
815 THEN
816 ln_total_tax_per_rupee := ln_total_tax_per_rupee + NVL(tax_amt_rate_tax_tab(I),0) ;
817 ln_total_non_rate_tax := ln_total_non_rate_tax + NVL(tax_amt_non_rate_tab(I),0);
818 END IF;
819 END LOOP;
820
821 ln_total_tax_per_rupee := ln_total_tax_per_rupee + 1;
822
823 IF ln_total_tax_per_rupee <> 0
824 THEN
825 ln_exclusive_price := (NVL(p_tax_amount,0) - ln_total_non_rate_tax ) / ln_total_tax_per_rupee;
826 END If;
827
828 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 );
829 jai_cmn_utils_pkg.print_log('utils.log','incl sp = ' || p_tax_amount || 'excl price = ' || ln_exclusive_price);
830
831 FOR I IN 1 .. row_count
832 LOOP
833 tax_amt_tab (I) := (tax_amt_rate_tax_tab(I) * ln_exclusive_price ) + tax_amt_non_rate_tab(I);
834 jai_cmn_utils_pkg.print_log('utils.log','in final loop , tax amt is ' ||tax_amt_tab(I));
835 END LOOP;
836 -----------------------------------------------------------------------
837 -- Added by Jia Li for Tax Inclusive Computations on 2007/12/11, End
838
839
840 FOR rec in tax_cur(p_line_id) LOOP
841 IF tax_type_tab(rec.lno) <> 2
842 THEN
843 v_tax_amt := v_tax_amt + nvl(tax_amt_tab(rec.lno),0);
844 END IF;
845
846 IF transaction_name = 'AR_LINES_UPDATE'
847 THEN
848 IF NVL(rec.adhoc_flag,'N') = 'Y' THEN
849 UPDATE JAI_AR_TRX_TAX_LINES
850 SET tax_amount = nvl(rec.tax_amount,0),
851 base_tax_amount = nvl(rec.base_tax_amount,0),
852 func_tax_amount = nvl(rec.func_tax_amount,0),
853 last_update_date = p_last_update_date,
854 last_updated_by = p_last_updated_by,
855 last_update_login = p_last_update_login
856 WHERE link_to_cust_trx_line_id = P_line_id
857 AND tax_line_no = rec.lno;
858 ELSE
859 UPDATE JAI_AR_TRX_TAX_LINES
860 SET tax_amount = nvl(tax_amt_tab(rec.lno),0),
861 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)),
862 func_tax_amount = ROUND(nvl(tax_amt_tab(rec.lno),0) * v_currency_conv_factor, rounding_factor_tab(rec.lno)),
863 last_update_date = p_last_update_date,
864 last_updated_by = p_last_updated_by,
865 last_update_login = p_last_update_login
866 WHERE link_to_cust_trx_line_id = P_line_id
867 AND tax_line_no = rec.lno;
868 END IF;
869 END IF;
870 END LOOP;
871 P_TAX_AMOUNT := nvl(v_tax_amt,0);
872
873
874 /* Added by Ramananda for bug#4407165 */
875 EXCEPTION
876 WHEN OTHERS THEN
877 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
878 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
879 app_exception.raise_exception;
880
881 END recalculate_tax;
882
883 Procedure locator_handler(
884 p_trx_id NUMBER,
885 p_flag VARCHAR2 -- DEFAULT 'Y' -- Use jai_constants.yes in the call of this procedure. Ramananda for for File.Sql.35
886 )
887 IS
888 pragma AUTONOMOUS_TRANSACTION; -- Vijay Shankar for Bug# 3985561
889
890 CURSOR get_temp_count IS
891 SELECT count(*)
892 FROM JAI_AR_TRX_UPDATE_T
893 WHERE trx_id = p_trx_id;
894
895 v_count NUMBER;
896
897 /* Added by Ramananda for bug#4407165 */
898 lv_object_name CONSTANT VARCHAR2(61) := 'jai_ar_utils_pkg.locator_handler';
899
900 BEGIN
901 /*------------------------------------------------------------------------------------------
902 CHANGE HISTORY for FILENAME: jai_om_rg_pkg.sql
903 Slno dd/mm/yyyy Author and Change Details
904 ------------------------------------------------------------------------------------------
905 1. 31/05/2004 Vijay Shankar for Bug# 3985561, Version: 115.1
906 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.
907 To resolve this, procedure is made to execute in AUTONOMOUS_TRANSACTION (Nested TRANSACTION CYCLE with COMMIT) mode
908 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
909 is added that takes care of INSERT/UPDATE/DELETE on the table
910
911 HIGH DEPENDENCY for future bugs
912
913 ------------------------------------------------------------------------------------------ */
914
915 -- 3985561
916 If p_flag = 'N' THEN
917 UPDATE JAI_AR_TRX_UPDATE_T
918 SET modified_flag = p_flag
919 WHERE trx_id = p_trx_id;
920 ELSIF p_flag = 'D' THEN
921 DELETE FROM JAI_AR_TRX_UPDATE_T
922 WHERE trx_id = p_trx_id;
923
924 ELSE
925
926 OPEN get_temp_count;
927 FETCH get_temp_count INTO v_count;
928 CLOSE get_temp_count;
929
930 IF v_count > 0 THEN
931
932 UPDATE JAI_AR_TRX_UPDATE_T
933 SET modified_flag = p_flag
934 WHERE trx_id = p_trx_id;
935
936 ELSIF p_flag <> 'D' THEN
937
938 INSERT INTO JAI_AR_TRX_UPDATE_T(
939 trx_ID, modified_flag,
940 -- added, Harshita for Bug 4866533
941 created_by, creation_date, last_updated_by, last_update_date
942 ) VALUES (
943 p_trx_id, p_flag,
944 -- added, Harshita for Bug 4866533
945 fnd_global.user_id, sysdate, fnd_global.user_id, sysdate
946 );
947
948 END IF;
949
950 END IF;
951
952 COMMIT; -- Vijay Shankar for Bug# 3985561
953
954 /* Added by Ramananda for bug#4407165 */
955 EXCEPTION
956 WHEN OTHERS THEN
957 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
958 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
959 app_exception.raise_exception;
960
961 END locator_handler;
962
963 /*
964 FUNCTION get_register_type(i_item_id NUMBER) RETURN VARCHAR2 IS
965 X_class JAI_OPM_ITM_MASTERS.item_class%type;
966 CURSOR cur IS SELECT item_class
967 FROM JAI_OPM_ITM_MASTERS
968 WHERE ITEM_ID = i_item_id;
969 BEGIN
970 open cur;
971 FETCH cur into X_class;
972 IF substr(X_class,1,2) = 'RM' or substr(X_class,1,2) = 'CC' then
973 RETURN 'A';
974 ELSIF substr(X_class,1,2) = 'CG' THEN
975 RETURN 'C';
976 END IF;
977 END get_register_type;
978 */
979
980 PROCEDURE apps_rel_insert(p_org_id IN NUMBER, p_loc_id IN NUMBER, p_rg_flag IN Varchar2,
981 p_reg_name IN Varchar2 ,p_complete_flag IN Varchar2, p_cretaed_by IN Number,
982 last_updated_by IN Number, p_last_update_login IN Number, p_creation_date IN Date, last_update_date IN Date
983 ) IS
984
985 -- v_paddr v$session.paddr%type;
986
987 /* Added by Ramananda for bug#4407165 */
988 lv_object_name CONSTANT VARCHAR2(61) := 'jai_ar_utils_pkg.apps_rel_insert';
989
990 BEGIN
991
992 /*------------------------------------------------------------------------------------------
993 FILENAME: apps_rel_insert.sql
994
995 CHANGE HISTORY:
996 S.No Date Author and Details
997
998 --------------------------------------------------------------------------------------------*/
999 /*
1000 Select paddr INTO v_paddr From v$session
1001 Where sid = (Select sid From v$mystat Where Rownum = 1);
1002 */--commneted by GSri on 22-jun-01 for tuning
1003
1004 --added by GSri -n 22-jun-01
1005 /*Select paddr into v_paddr
1006 From v$session
1007 Where audsid = userenv('SESSIONID');
1008 */
1009 Insert Into JAI_AR_TRX_APPS_RELS_T(
1010 Organization_ID,Location_ID,RG_Update_Flag,Register_Type,
1011 Once_completed_flag
1012 /* Bug 4535701. Commented by Lakshmi Gopalsami
1013 * As part of global temporary table
1014 * WHO columns has been removed
1015 , paddr,
1016 created_by, last_updated_by, last_update_login, creation_date, last_update_date */
1017 ) Values (
1018 p_org_id,p_loc_id,p_rg_flag,p_reg_name,
1019 p_complete_flag/*, v_paddr,
1020 p_cretaed_by, last_updated_by, p_last_update_login, p_creation_date, last_update_date */
1021 );
1022
1023 COMMIT;
1024
1025
1026 /* Added by Ramananda for bug#4407165 */
1027 EXCEPTION
1028 WHEN OTHERS THEN
1029 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
1030 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
1031 app_exception.raise_exception;
1032
1033 END apps_rel_insert;
1034
1035
1036 END jai_ar_utils_pkg;
[Home] [Help]