1 PACKAGE BODY jai_om_tax_pkg AS
2 /* $Header: jai_om_tax.plb 120.14.12010000.2 2008/10/23 10:47:36 csahoo ship $ */
3
4 /*------------------------------------------------------------------------------------------
5 FILENAME:
6 1 08-Aug-2005 Ramananda for Bug#4540783. File Version 120.2
7 In Procedure recalculate_excise_taxes:
8 Added a new parameter - p_vat_assess_value and value for the same as ln_vat_assessable_value
9 while calling procedure jai_om_tax_pkg.recalculate_oe_taxes
10
11 2.25-Aug-2006 Bug 5490479, Added by aiyer, File version 120.5
12 Issue:-
13 Org_id parameter in all MOAC related Concurrent programs is getting derived from the profile org_id
14 As this parameter is hidden hence not visible to users and so users cannot choose any other org_id from the security profile.
15
16 Fix:-
17 1. The Multi_org_category field for all MOAC related concurrent programs should be set to 'S' (indicating single org reports).
18 This would enable the SRS Operating Unit field. User would then be able to select operating unit values related to the
19 security profile.
20 2. Remove the default value for the parameter p_org_id and make it Required false, Display false. This would ensure that null value gets passed
21 to the called procedures/ reports.
22 3. Change the called procedures/reports. Remove the use of p_org_id and instead derive the org_id using the function mo_global.get_current_org_id
23 This change has been made many procedures and reports.
24 The procedure recalculate_excise_taxes has been changed for the same.
25
26 01/11/2006 SACSETHI for bug 5228046, File version 120.6
27 Forward porting the change in 11i bug 5365523 (Additional CVD Enhancement).
28 This bug has datamodel and spec changes.
29 16/04/2007 KUNKUMAR for bug 5989740 file version 115.11.6107.2 of file ja_in_calc_taxes_ato_p.sql;
30 forward porting to R12
31 17/04/2007 bduvarag for the Bug#5989740, file version 120.8
32 Forward porting the changes done in 11i bug#5907436
33
34 20/05/2007 KUNKUMAR for bugno 5604375, file version 120.9
35 Issue:Interface Trip Stop concurrent was running into a warning when the shipped qty field was updated
36 to less than the original ordered qty given during creation of sales order.
37 Fix: The value of an un-initialized variable was being accessed.
38 Made the appropriate assignment.
39 15/06/2007 bduvarag for the bug#6072461, Forward porting of 11i bug#5183031
40
41 6/18/2007 ssawant for bug 6134057
42 row_count Changed to rec.lno in calculate_ato_taxes procedure.
43
44 12/10/2007 Kevin Cheng Update the logic for inclusive tax calculation
45
46 05-Mar-08 Jia Li Added clause logic for bug# 6846048
47 Issue: When unit selling price is changed because of discounts,
48 inclusive taxes are getting added to the tax amount in the JAI_OM_OE_SO_LINES table.
49 So at ship confirm time, comparing the tax amount in the lines table with the sum of exclusive taxes
50 in the taxes table was not matching and hence the trigger was returning an error.
51 Fix: Ensuring that only exclusive taxes are added to the tax amount in the jai_om_oe_so_lines table.
52
53 22-Oct-2008 CSahoo - bug#4918667, File Version 120.14.12010000.2
54 Issue :- In case of retrobilling functionality, there was a divide by zero error which is caused.
55 The parameter p_line_quantity is being used as a denominator, and in case it is zero
56 it was causing a zero divide error.
57
58 Resolution :- made the change that in case the p_line_quantity is zero , the tax amt is also zero.
59
60 ------------------------------------------------------------------------------------------*/
61
62 PROCEDURE calculate_ato_taxes
63 (
64 transaction_name VARCHAR2,
65 P_tax_category_id NUMBER,
66 p_header_id NUMBER,
67 p_line_id NUMBER,
68 p_assessable_value NUMBER default 0,
69 p_tax_amount IN OUT NOCOPY NUMBER,
70 p_currency_conv_factor NUMBER,
71 p_inventory_item_id NUMBER,
72 p_line_quantity NUMBER,
73 p_quantity NUMBER,
74 p_uom_code VARCHAR2,
75 p_vendor_id NUMBER,
76 p_currency VARCHAR2,
77 p_creation_date DATE,
78 p_created_by NUMBER,
79 p_last_update_date DATE,
80 p_last_updated_by NUMBER,
81 p_last_update_login NUMBER,
82 p_vat_assessable_Value NUMBER DEFAULT 0,
83 p_vat_reversal_price NUMBER DEFAULT 0 /*Bug#6072461, bduvarag*/
84 ) IS
85 TYPE num_tab IS TABLE OF number
86 INDEX BY BINARY_INTEGER;
87 TYPE tax_amt_num_tab IS TABLE OF number
88 INDEX BY BINARY_INTEGER;
89
90 -- Add by Kevin Cheng for inclusive tax Dec 10, 2007
91 ----------------------------------------------------
92 TYPE CHAR_TAB IS TABLE OF VARCHAR2(10)
93 INDEX BY BINARY_INTEGER;
94
95 lt_adhoc_tax_tab CHAR_TAB;
96 lt_inclu_tax_tab CHAR_TAB;
97 lt_tax_rate_tab NUM_TAB;
98 lt_tax_rate_per_rupee NUM_TAB;
99 lt_cumul_tax_rate_per_rupee NUM_TAB;
100 lt_tax_target_tab NUM_TAB;
101 lt_tax_amt_rate_tax_tab TAX_AMT_NUM_TAB;
102 lt_tax_amt_non_rate_tab TAX_AMT_NUM_TAB;
103 lt_func_tax_amt_tab TAX_AMT_NUM_TAB;
104 lv_uom_code VARCHAR2(10) := 'EA';
105 lv_register_code VARCHAR2(20);
106 ln_inventory_item_id NUMBER;
107 ln_exclusive_price NUMBER;
108 ln_total_non_rate_tax NUMBER := 0;
109 ln_total_inclusive_factor NUMBER;
110 ln_bsln_amt_nr NUMBER :=0;
111 ln_tax_amt_nr NUMBER(38,10) := 0;
112 ln_func_tax_amt NUMBER(38,10) := 0;
113 ln_vamt_nr NUMBER(38,10) := 0;
114 ln_excise_jb NUMBER;
115 ln_total_tax_per_rupee NUMBER;
116 ln_assessable_value NUMBER;
117 ln_vat_assessable_value NUMBER;
118 ln_vat_reversal_price NUMBER;
119 ----------------------------------------------------
120 p1 num_tab;
121 p2 num_tab;
122 p3 num_tab;
123 p4 num_tab;
124 p5 num_tab;
125
126 -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
127 -- START BUG 5228046
128
129 p6 num_tab;
130 p7 num_tab;
131 p8 num_tab;
132 p9 num_tab;
133 p10 num_tab;
134
135 -- END BUG 5228046
136 tax_rate_tab num_tab;
137 /*
138 || Aiyer for bug#4691616. Added tax_rate_zero_tab table
139 -------------------------------------------------------------
140 tax_rate(i) tax_rate_tab(i) tax_rate_zero_tab(i)
141 -------------------------------------------------------------
142 NULL 0 0
143 0 0 -9999
144 n (non-zero and not null) n n
145 -------------------------------------------------------------
146 */
147 tax_rate_zero_tab num_tab;
148
149 tax_type_tab num_tab;
150 tax_amt_tab tax_amt_num_tab;
151 base_tax_amt_tab tax_amt_num_tab;
152 end_date_tab num_tab;
153 rounding_factor_tab num_tab;
154 bsln_amt number; -- := p_tax_amount; --Ramananda for File.Sql.35
155 v_conversion_rate number := 0;
156 v_currency_conv_factor number; -- := p_currency_conv_factor; --Ramananda for File.Sql.35
157 v_tax_amt number := 0;
158 vamt number :=0;
159 v_amt number;
160 row_count number := 0;
161 counter number;
162 max_iter number := 10;
163
164 v_rma_ctr Number; -- variable added by sriram - bug # 2740443
165
166 --Comment out by Kevin Cheng for inclusive tax Dec 10, 2007
167 /*CURSOR tax_cur(p_header_id NUMBER, p_line_id NUMBER) IS
168 SELECT c.tax_line_no lno, c.tax_id, c.tax_rate, c.qty_rate, c.uom uom_code, c.func_tax_amount, c.base_tax_amount,
169 c.precedence_1 p_1, c.precedence_2 p_2, c.precedence_3 p_3,c.precedence_4 p_4, c.precedence_5 p_5,
170 c.precedence_6 p_6, c.precedence_7 p_7, c.precedence_8 p_8,c.precedence_9 p_9, c.precedence_10 p_10, -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
171 c.tax_amount, d.tax_type, d.end_date valid_date, d.rounding_factor,
172
173 DECODE(rgm_tax_types.regime_Code,jai_constants.vat_regime, 4, \* added by ssumaith - bug# 4245053*\
174 decode(upper(d.tax_type),
175 'EXCISE', 1,
176 'ADDL. EXCISE', 1,
177 'OTHER EXCISE', 1,
178 'CVD', 1,
179 'TDS', 2,
180 'EXCISE_EDUCATION_CESS', 1,
181 'CVD_EDUCATION_CESS', 1,
182 'SH_EXCISE_EDUCATION_CESS' ,1,--Added by kundan kumar for bug#5907436
183 'SH_CVD_EDUCATION_CESS' , 1, --Added by kundan kumar for bug#5907436
184 'VAT REVERSAL', 5,\*Bug#6072461, bduvarag*\
185 0
186 )
187 )
188 tax_type_val,
189 d.adhoc_flag
190 FROM JAI_OM_OE_SO_TAXES c ,
191 JAI_CMN_TAXES_ALL d ,
192 jai_regime_tax_types_v rgm_tax_types \* added by ssumaith - bug# 4245053*\
193 WHERE c.line_id = p_line_id
194 AND c.header_id = p_header_id
195 AND c.tax_id = d.tax_id
196 AND rgm_tax_types.tax_type (+) = d.tax_type \* added by ssumaith - bug# 4245053*\
197 ORDER BY c.tax_line_no;*/
198
199 --Add by Kevin Cheng for inclusive tax Dec 10, 2007
200 CURSOR tax_cur( p_header_id NUMBER
201 , p_line_id NUMBER
202 )
203 IS
204 SELECT
205 c.tax_line_no lno
206 , c.tax_category_id
207 , c.tax_id
208 , c.tax_rate
209 , c.qty_rate
210 , c.uom uom_code
211 , c.func_tax_amount
212 , c.base_tax_amount
213 , c.precedence_1 p_1
214 , c.precedence_2 p_2
215 , c.precedence_3 p_3
216 , c.precedence_4 p_4
217 , c.precedence_5 p_5
218 , c.precedence_6 p_6
219 , c.precedence_7 p_7
220 , c.precedence_8 p_8
221 , c.precedence_9 p_9
222 , c.precedence_10 p_10 -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
223 , c.tax_amount
224 , d.tax_type
225 , d.end_date valid_date
226 , nvl(d.rounding_factor,0) rounding_factor
227 , DECODE( rgm_tax_types.regime_Code
228 , jai_constants.vat_regime ,4 /* added by ssumaith - bug# 4245053*/
229 , decode( upper(d.tax_type)
230 , 'EXCISE' ,1
231 , 'ADDL. EXCISE' ,1
232 , 'OTHER EXCISE' ,1
233 , jai_constants.tax_type_cvd ,1
234 , jai_constants.tax_type_tds ,2
235 , jai_constants.tax_type_exc_edu_cess ,6
236 , jai_constants.tax_type_cvd_edu_cess ,6
237 , jai_constants.tax_type_sh_exc_edu_cess ,6
238 , jai_constants.tax_type_sh_cvd_edu_cess ,6
239 , 'VAT REVERSAL' ,5/*Bug#6072461, bduvarag*/
240 , 0
241 )
242 ) tax_type_val
243 , d.adhoc_flag
244 , d.vendor_id
245 , d.mod_cr_percentage
246 , d.inclusive_tax_flag
247 FROM
248 Jai_Om_Oe_So_Taxes c
249 , Jai_Cmn_Taxes_All d
250 , Jai_Regime_Tax_Types_V rgm_tax_types /* added by ssumaith - bug# 4245053*/
251 WHERE c.line_id = p_line_id
252 AND c.header_id = p_header_id
253 AND c.tax_id = d.tax_id
254 AND rgm_tax_types.tax_type(+) = d.tax_type /* added by ssumaith - bug# 4245053*/
255 ORDER BY
256 c.tax_line_no;
257
258 -- following cursor added by sriram - bug # 2740443
259
260 CURSOR c_rma_info (v_header_id Number , v_line_id Number) is
261 Select 1
262 from JAI_OM_OE_RMA_LINES
263 where rma_header_id = v_header_id
264 and rma_line_id = v_line_id;
265
266 --Comment out by Kevin Cheng for inclusive tax Dec 10, 2007
267 /*CURSOR c_rma_tax_cur(p_line_id NUMBER) IS
268 SELECT c.tax_line_no lno, c.tax_id, c.tax_rate, c.qty_rate, c.uom uom_code, c.func_tax_amount, c.base_tax_amount,
269 c.precedence_1 p_1, c.precedence_2 p_2, c.precedence_3 p_3,c.precedence_4 p_4, c.precedence_5 p_5,
270 c.precedence_6 p_6, c.precedence_7 p_7, c.precedence_8 p_8,c.precedence_9 p_9, c.precedence_10 p_10, -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
271 c.tax_amount, d.tax_type, d.end_date valid_date, d.rounding_factor,
272 DECODE(rgm_tax_types.regime_Code,jai_constants.vat_regime, 4, \* added by ssumaith - bug# 4245053*\
273 decode(upper(d.tax_type),'EXCISE', 1, 'ADDL. EXCISE', 1, 'OTHER EXCISE', 1, 'CVD',1, 'TDS', 2, 0
274 )
275 ) tax_type_val,
276 d.adhoc_flag
277 FROM JAI_OM_OE_RMA_TAXES c,
278 JAI_CMN_TAXES_ALL d ,
279 jai_regime_tax_types_v rgm_tax_types \* added by ssumaith - bug# 4245053*\
280 WHERE c.rma_line_id = p_line_id
281 AND c.tax_id = d.tax_id
282 AND rgm_tax_types.tax_type(+) = d.tax_type \* added by ssumaith - bug# 4245053*\
283 ORDER BY c.tax_line_no;*/
284
285 -- Add by Kevin Cheng for inclusive tax Dec 10, 2007
286 CURSOR c_rma_tax_cur( p_line_id NUMBER
287 )
288 IS
289 SELECT
290 c.tax_line_no lno
291 , c.tax_id
292 , c.tax_rate
293 , c.qty_rate
294 , c.uom uom_code
295 , c.func_tax_amount
296 , c.base_tax_amount
297 , c.precedence_1 p_1
298 , c.precedence_2 p_2
299 , c.precedence_3 p_3
300 , c.precedence_4 p_4
301 , c.precedence_5 p_5
302 , c.precedence_6 p_6
303 , c.precedence_7 p_7
304 , c.precedence_8 p_8
305 , c.precedence_9 p_9
306 , c.precedence_10 p_10 -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
307 , c.tax_amount
308 , d.tax_type
309 , d.end_date valid_date
310 , nvl(d.rounding_factor,0) rounding_factor
311 , DECODE( rgm_tax_types.regime_Code
312 , jai_constants.vat_regime, 4 /* added by ssumaith - bug# 4245053*/
313 , decode( upper(d.tax_type)
314 , 'EXCISE' ,1
315 , 'ADDL. EXCISE' ,1
316 , 'OTHER EXCISE' ,1
317 , jai_constants.tax_type_cvd ,1
318 , jai_constants.tax_type_tds ,2
319 , jai_constants.tax_type_exc_edu_cess ,6
320 , jai_constants.tax_type_cvd_edu_cess ,6
321 , jai_constants.tax_type_sh_exc_edu_cess ,6
322 , jai_constants.tax_type_sh_cvd_edu_cess ,6
323 , 'VAT REVERSAL' ,5/*Bug#6072461, bduvarag*/
324 , 0
325 )
326 ) tax_type_val
327 , d.adhoc_flag
328 , d.vendor_id
329 , d.mod_cr_percentage
330 , d.inclusive_tax_flag
331 FROM
332 Jai_Om_Oe_Rma_Taxes c
333 , Jai_Cmn_Taxes_All d
334 , Jai_Regime_Tax_Types_V rgm_tax_types /* added by ssumaith - bug# 4245053*/
335 WHERE c.rma_line_id = p_line_id
336 AND c.tax_id = d.tax_id
337 AND rgm_tax_types.tax_type(+) = d.tax_type /* added by ssumaith - bug# 4245053*/
338 ORDER BY
339 c.tax_line_no;
340
341 -- Start of bug 3590208
342 /*****************
343 Code modified by aiyer for the bug 3590208
344 Check whether the excise exemptions exist at the order line level.
345 *****************/
346 CURSOR c_excise_exemption
347 IS
348 SELECT
349 '1'
350 FROM
351 JAI_OM_OE_SO_LINES jsl
352 WHERE
353 jsl.excise_exempt_type IS NOT NULL AND
354 jsl.line_id = p_line_id ;
355
356 lv_excise_exemption_exists VARCHAR2(1);
357
358 -- End of bug 3590208
359
360 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_om_tax_pkg.calculate_ato_taxes';
361 BEGIN
362 /*------------------------------------------------------------------------------------------
363 FILENAME: calculate_ato_taxes_P.sql
364
365
366 CHANGE HISTORY:
367 S.No Date Author and Details
368 1. 2001/11/09 Anuradha Parthasarathy
369 Parameter p_quantity added To handle Adhoc Types of Taxes.
370 2. 2002/01/03 Anuradha Parthasarathy
371 parameter p_quantity used in place of p_line_quantity for correct calculation
372 of taxes for which the tax_rate is null.
373 3. 2003/01/18 Sriram Bug # 2740443 File Version 615.1
374 When RMA was done without reference and mofifiers attached
375 to them, taxes were not getting recalculated.
376 Hence wrote code to get the same done by adding 2 cursors and checking
377 if the current header id and line id combination refers to a sales order
378 or rma order . if it corresponds to an rma order , update the
379 JAI_OM_OE_RMA_TAXES table .
380
381 4. 26-Feb-2004 Aiyer For the Bug #3590208 File Version 619.1
382 Issue:-
383 Excise Duty Recalculation happens for excise exempted tax lines if the order line quantity
384 is changed.
385 Fix:-
386 Code has been added to set the tax_rate, tax_amount and base_tax_amount to 0 when the Order Line has Excise
387 exemptions and tax is of "Excise" type. The cursor c_excise_exemption has been added for the purpose.
388 Dependency Due to this code :-
389 None
390
391 5. 09-Aug-2004 Aiyer - Bug #3802074 File Version 115.2
392 Issue:-
393 Uom based taxes do not get calculated correctly if the qty or price is changed.
394
395 Reason:-
396 --------
397 Previous to this fix the uom currency uom conversion would happen only when the transaction uom code and the tax level uom code where
398 in the same uom class. This was not required.
399 This was happening because the UOM calculation was previously happening only for cases of exact match
400 between transaction uom and setup UOM.
401
402 Fix:-
403 ----
404 Removed the uom_class_cur for loop which used to check for the same uom class match condition. Now the uom conversion happens at all time.
405 Now if an exact match is not found then the conversion rate between the two uom's is determined and tax amounts are calculated for defaultation.
406
407 Dependency Due to This Bug:-
408 ----------------------------
409 The refresh in the India Localization sales order form was not happening properly leading to taxes getting set incorrectly
410 when the tax apply button is clicked (in JAINEORD) post updation of qty in Base apps form. To resolve this the locator logic
411 was removed from the triggers ja_in_oe_order_lines_aiu_trg and ja_in_oe_order_lines_au_trg and a new trigger was created
412 (ja_in_om_locator_aiu_trg). Some fix was also done in JAINEORD.fmb for the same.
413 Hence all these objects should be displatched along with this object, due to functional dependency
414 As this fix is also being taken in the current Due to this the tax was not getting bug
415
416 6. 2005/03/10 ssumaith - bug# 4245053 - File version 115.3
417
418 Taxes under the vat regime needs to be calculated based on the vat assessable value setup done.
419 In the vendor additional information screen and supplier additional information screen, a place
420 has been given to capture the vat assessable value.
421
422 This needs to be used for the calculation of the taxes registered under vat regime.
423
424 This change has been done by using the view jai_regime_tax_types_v - and outer joining it with the
425 JAI_CMN_TAXES_ALL table based on the tax type column.
426
427 A parameter p_vat_assessable_Value NUMBER DEFAULT 0 has been added to the procedure.
428
429 Dependency due to this bug - Huge
430 This patch should always be accompanied by the VAT consolidated patch - 4245089
431
432 7. 08-Jun-2005 Version 116.3 jai_om_tax -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
433 as required for CASE COMPLAINCE.
434
435 8. 13-Jun-2005 File Version: 116.4
436 Ramananda for bug#4428980. Removal of SQL LITERALs is done
437
438 9. 01-Jun-2006 Aiyer for bug# 4691616. File Version 120.3
439 Issue:-
440 UOM based taxes do not get calculated correctly.
441
442 Solution:-
443 Fwd ported the fix for the bug 4729742.
444 Changed the files JAINTAX1.pld, jai_cmn_tax_dflt.plb and jai_om_tax.plb.
445
446 10. 10-Dec-007 Kevin Cheng Update the logic for inclusive tax calculation
447
448 11. 05-Mar-08 Jia Li Added clause logic for bug# 6846048
449 Issue: When unit selling price is changed because of discounts,
450 inclusive taxes are getting added to the tax amount in the JAI_OM_OE_SO_LINES table.
451 So at ship confirm time, comparing the tax amount in the lines table with the sum of exclusive taxes
452 in the taxes table was not matching and hence the trigger was returning an error.
453 Fix: Ensuring that only exclusive taxes are added to the tax amount in the jai_om_oe_so_lines table.
454
455 Future Dependencies For the release Of this Object:-
456 (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/
457 A datamodel change )
458
459 ----------------------------------------------------------------------------------------------------------------------------------------------------
460 Current Version Current Bug Dependent Files Version Author Date Remarks
461 Of File On Bug/Patchset Dependent On
462 calculate_ato_taxes_p.sql
463 ----------------------------------------------------------------------------------------------------------------------------------------------------
464 115.2 3802074 IN60105D2 ja_in_om_locator_aiu_trg.sql 115.1 Aiyer 26-oct-2004 Functional Dependency
465 jai_cmn_utils_pkg.ja_in_set_locator 115.0
466 ja_in_oe_order_lines_aiu_trg 115.5
467 ja_in_oe_order_lines_aiu_trg 115.5
468 JAINEORD.fmb 115.3
469 calculate_ato_taxes_p.sql 115.2 Aiyer
470
471
472 115.3 4245053 IN60106 + ssumaith Service Tax and VAT Infrastructure are created
473 4146708 + based on the bugs - 4146708 and 4545089 respectively.
474 4245089
475
476
477 *************************************************************************************************************************************************************/
478
479 bsln_amt := p_tax_amount; --Ramananda for File.Sql.35
480 v_currency_conv_factor := p_currency_conv_factor; --Ramananda for File.Sql.35
481
482 Open c_rma_info(p_header_id,p_line_id);
483 Fetch c_rma_info into v_rma_ctr;
484 close c_rma_info;
485
486 -- Start of bug 3590208
487 /*****************
488 Code modified by aiyer for the bug 3590208
489 *****************/
490 OPEN c_excise_exemption;
491 FETCH c_excise_exemption INTO lv_excise_exemption_exists;
492 -- End of bug 3590208
493
494 IF NVL(v_rma_ctr,0) = 0 then -- added by sriram - bug # 2740443 abc
495
496 FOR rec in tax_cur(p_header_id, p_line_id)
497 LOOP
498 p1(rec.lno) := nvl(rec.p_1,-1);
499 p2(rec.lno) := nvl(rec.p_2,-1);
500 p3(rec.lno) := nvl(rec.p_3,-1);
501 p4(rec.lno) := nvl(rec.p_4,-1);
502 p5(rec.lno) := nvl(rec.p_5,-1);
503
504 -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
505 -- START BUG 5228046
506
507 p6(rec.lno) := nvl(rec.p_6,-1);
508 p7(rec.lno) := nvl(rec.p_7,-1);
509 p8(rec.lno) := nvl(rec.p_8,-1);/*Added by kunkumar,for bugno5604375,deleted second assignment of p7 */
510 p9(rec.lno) := nvl(rec.p_9,-1);
511 p10(rec.lno) := nvl(rec.p_10,-1);
512
513 -- END BUG 5228046
514
515 tax_rate_tab(rec.lno) := nvl(rec.tax_rate,0);
516
517 /*
518 || The following code added by aiyer for the bug 4691616
519 || Purpose:
520 || rec.tax_rate = 0 means that tax_rate for such a tax line is actually zero (i.e it is not a replacement of null value)
521 || So, when rec.tax_rate = 0, tax_rate_zero_tab is populated with -9999 to identify that this tax_line actually has tax_rate = 0
522 || To calculate the BASE_TAX_AMOUNT of the taxes whose tax_rate is zero
523 */
524
525 IF rec.tax_rate is null THEN
526 /*
527 ||Indicates qty based taxes
528 */
529 tax_rate_zero_tab(rec.lno) := 0; /*row_count Changed to rec.lno by ssawant for bug 6134057*/
530
531 ELSIF rec.tax_rate = 0 THEN
532 /*
533 ||Indicates 0% tax rate becasue a tax can have a rate as 0%.
534 */
535 tax_rate_zero_tab(rec.lno) := -9999; /*row_count Changed to rec.lno by ssawant for bug 6134057*/
536
537 ELSE
538 tax_rate_zero_tab(rec.lno) := rec.tax_rate; /*row_count Changed to rec.lno by ssawant for bug 6134057*/
539
540 END IF;
541
542 tax_type_tab(rec.lno) := rec.tax_type_val; /*row_count Changed to rec.lno by ssawant for bug 6134057*/
543 /*End of bug 4691616 */
544
545 tax_type_tab(rec.lno) := rec.tax_type_val;
546 tax_amt_tab(rec.lno) := 0;
547 base_tax_amt_tab(rec.lno) := 0;
548 rounding_factor_tab(rec.lno) := NVL(rec.rounding_factor, 0);
549
550 -- Add by Kevin Cheng for inclusive tax Dec 10, 2007
551 ----------------------------------------------------
552 lt_tax_rate_per_rupee(rec.lno) := NVL(rec.tax_rate,0)/100;
553 ln_total_tax_per_rupee := 0;
554 lt_inclu_tax_tab(rec.lno) := NVL(rec.inclusive_tax_flag,'N');
555 lt_tax_amt_rate_tax_tab(rec.lno) := 0;
556 lt_tax_amt_non_rate_tab(rec.lno) := 0; -- tax inclusive
557 ----------------------------------------------------
558
559 IF tax_rate_tab(rec.lno) = 0 THEN
560 -- Start of bug 3802074
561 /*
562 Code added by aiyer for the bug 3802074.
563 Removed the uom_class_cur for loop, as it used to check that the uom conversion should happen only when the transaction uom code and the tax uom code
564 are in the same uom class. This was not required.
565 Now the code check whether an exact match exists between the transaction uom and the setup uom.
566 IF an exact match is found then the conversion rate is equal to 1 else the conversion rate between the two uom's would be
567 determined and tax amounts, base_tax_amounts are calculated for defaultation.
568 */
569
570 inv_convert.inv_um_conversion( p_uom_code, rec.uom_code, p_inventory_item_id, v_conversion_rate);
571 IF nvl(v_conversion_rate, 0) <= 0 THEN
572 inv_convert.inv_um_conversion( p_uom_code, rec.uom_code, 0, v_conversion_rate);
573 IF nvl(v_conversion_rate, 0) <= 0 THEN
574 v_conversion_rate := 0;
575 END IF;
576 END IF;
577 --Comment out by Kevin Cheng for inclusive tax Dec 10, 2007
578 /*tax_amt_tab(rec.lno) := ROUND(nvl(rec.qty_rate * v_conversion_rate, 0) * p_quantity, NVL(rec.rounding_factor, 0));*/
579 -- End of bug 3802074
580
581 --Add by Kevin Cheng for inclusive tax Dec 10, 2007
582 ---------------------------------------------------
583 lt_tax_amt_non_rate_tab(rec.lno) := nvl(rec.qty_rate * v_conversion_rate, 0) * p_quantity; -- tax inclusive
584 base_tax_amt_tab(rec.lno) := lt_tax_amt_non_rate_tab(rec.lno); -- tax inclusive
585 ---------------------------------------------------
586
587 IF rec.adhoc_flag = 'Y' THEN
588 --Comment out by Kevin Cheng for inclusive tax Dec 10, 2007
589 /*tax_amt_tab(rec.lno) := ROUND((rec.tax_amount * p_quantity/p_line_quantity), NVL(rec.rounding_factor, 0));*/
590 --Add by Kevin Cheng for inclusive tax Dec 10, 2007
591 -- csahoo - bug#4918667 added the if and the else so that we dont encounter a zero-divide error
592 IF NVL(p_line_quantity,0) <> 0 THEN
593 lt_tax_amt_non_rate_tab(rec.lno) := ROUND((rec.tax_amount * p_quantity/p_line_quantity), NVL(rec.rounding_factor, 0));
594 ELSE
595 lt_tax_amt_non_rate_tab(rec.lno) := 0;
596 END IF;
597 END IF;
598 END IF;
599
600 IF rec.valid_date is NULL or rec.valid_date >= sysdate
601 THEN
602 end_date_tab(rec.lno) := 1;
603 ELSE
604 tax_amt_tab(rec.lno) := 0;
605 end_date_tab(rec.lno) := 0;
606 END IF;
607
608 -- Start of bug 3590208
609 /*****************
610 Code modified by aiyer for the bug 3590208
611 IF the line is excise exempted and the tax is of type Excise then set the tax_rate, tax_amount and base_tax_amount
612 to zero.
613 *****************/
614 IF c_excise_exemption%FOUND AND
615 rec.tax_type_val = 1
616 THEN
617 /* Set tax_rate_tab = 0, tax_amt_tab = 0 and base_tax_amt_tab = 0 */
618 tax_rate_tab(rec.lno) := 0;
619 tax_amt_tab(rec.lno) := 0;
620 base_tax_amt_tab(rec.lno) := 0;
621 lt_tax_amt_non_rate_tab(rec.lno) :=0; -- Add by Kevin Cheng for inclusive tax Dec 10, 2007
622 END IF;
623 -- End of bug 3590208
624
625 row_count := row_count + 1;
626 END LOOP;
627
628 ELSIF v_rma_ctr = 1 then -- else part of abc
629 -- v_rma_ctr value will be 1 if the current header id line id combination corresponds to a return order.
630 -- added by sriram the section from the following elsif to end if- bug # 2740443
631
632 FOR rec in c_rma_tax_cur(p_line_id) LOOP
633 p1(rec.lno) := nvl(rec.p_1,-1);
634 p2(rec.lno) := nvl(rec.p_2,-1);
635 p3(rec.lno) := nvl(rec.p_3,-1);
636 p4(rec.lno) := nvl(rec.p_4,-1);
637 p5(rec.lno) := nvl(rec.p_5,-1);
638
639 -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
640 -- START BUG 5228046
641
642 p6(rec.lno) := nvl(rec.p_6,-1);
643 p7(rec.lno) := nvl(rec.p_7,-1);
644 p8(rec.lno) := nvl(rec.p_8,-1);
645 p9(rec.lno) := nvl(rec.p_9,-1);
646 p10(rec.lno) := nvl(rec.p_10,-1);
647
648 -- END BUG 5228046
649
650
651 tax_rate_tab(rec.lno) := nvl(rec.tax_rate,0);
652 tax_type_tab(rec.lno) := rec.tax_type_val;
653 tax_amt_tab(rec.lno) := 0;
654 base_tax_amt_tab(rec.lno) := 0;
655 rounding_factor_tab(rec.lno) := NVL(rec.rounding_factor, 0);
656
657 -- Add by Kevin Cheng for inclusive tax Dec 10, 2007
658 ----------------------------------------------------
659 lt_tax_rate_per_rupee(rec.lno) := NVL(rec.tax_rate,0)/100;
660 ln_total_tax_per_rupee := 0;
661 lt_inclu_tax_tab(rec.lno) := NVL(rec.inclusive_tax_flag,'N');
662
663 IF rec.tax_rate IS NULL
664 THEN
665 tax_rate_zero_tab(rec.lno) := 0;
666 ELSIF rec.tax_rate = 0
667 THEN --IF rec.tax_rate IS NULL
668 tax_rate_zero_tab(rec.lno) := -9999;
669 ELSE --IF rec.tax_rate IS NULL
670 tax_rate_zero_tab(rec.lno) := rec.tax_rate;
671 END IF; --IF rec.tax_rate IS NULL
672
673 lt_tax_amt_rate_tax_tab(rec.lno) :=0;
674 lt_tax_amt_non_rate_tab(rec.lno) :=0; -- tax inclusive
675 ----------------------------------------------------
676
677 IF tax_rate_tab(rec.lno) = 0 THEN
678 -- Start of bug 3802074
679 /*
680 Code added by aiyer for the bug 3802074
681 Check whether an exact match exists between the transaction uom and the setup uom (obtained through the tax_category list).
682 IF an exact match is found then the conversion rate is equal to 1 else the conversion rate between the two uom's would be
683 determined and tax amounts,base_tax_amounts are calculated for defaultation.
684 */
685
686 inv_convert.inv_um_conversion( p_uom_code, rec.uom_code, p_inventory_item_id, v_conversion_rate);
687 IF nvl(v_conversion_rate, 0) <= 0 THEN
688 INV_CONVERT.inv_um_conversion( p_uom_code, rec.uom_code, 0, v_conversion_rate);
689 IF nvl(v_conversion_rate, 0) <= 0 THEN
690 v_conversion_rate := 0;
691 END IF;
692 END IF;
693 --Comment out by Kevin Cheng for inclusive tax Dec 10, 2007
694 /*tax_amt_tab(rec.lno) := ROUND(nvl(rec.qty_rate * v_conversion_rate, 0) * p_quantity, NVL(rec.rounding_factor, 0));*/
695 -- End of bug 3802074
696
697 -- Add by Kevin Cheng for inclusive tax Dec 10, 2007
698 ----------------------------------------------------
699 lt_tax_amt_non_rate_tab(rec.lno) := nvl(rec.qty_rate * v_conversion_rate, 0) * p_quantity; -- tax inclusive
700 base_tax_amt_tab(rec.lno) := lt_tax_amt_non_rate_tab(rec.lno); -- tax inclusive
701 ----------------------------------------------------
702
703 IF rec.adhoc_flag = 'Y' THEN
704 --Comment out by Kevin Cheng for inclusive tax Dec 10, 2007
705 /*tax_amt_tab(rec.lno) := ROUND((rec.tax_amount * p_quantity/p_line_quantity), NVL(rec.rounding_factor, 0));*/
706 --Add by Kevin Cheng for inclusive tax Dec 10, 2007
707 -- csahoo - bug#4918667 added the if and the else so that we dont encounter a zero-divide error
708 IF NVL(p_line_quantity,0) <> 0 THEN
709 lt_tax_amt_non_rate_tab(rec.lno) := ROUND((rec.tax_amount * p_quantity/p_line_quantity), NVL(rec.rounding_factor, 0));
710 ELSE
711 lt_tax_amt_non_rate_tab(rec.lno) := 0;
712 END IF;
713 END IF;
714 END IF;
715
716 IF rec.valid_date is NULL or rec.valid_date >= sysdate THEN
717 end_date_tab(rec.lno) := 1;
718 ELSE
719 tax_amt_tab(rec.lno) := 0;
720 end_date_tab(rec.lno) := 0;
721 END IF;
722 -- Start of bug 3590208
723 /*****************
724 Code modified by aiyer for the bug 3590208
725 IF the line is excise exempted and the tax is of type Excise then set the tax_rate, tax_amount and base_tax_amount
726 to zero.
727 *****************/
728 IF c_excise_exemption%FOUND AND
729 rec.tax_type_val = 1
730 THEN
731 /* Set tax_rate_tab = 0, tax_amt_tab = 0 and base_tax_amt_tab = 0 */
732 tax_rate_tab(rec.lno) := 0;
733 tax_amt_tab(rec.lno) := 0;
734 base_tax_amt_tab(rec.lno) := 0;
735 lt_tax_amt_non_rate_tab(rec.lno) :=0; -- Add by Kevin Cheng for inclusive tax Dec 10, 2007
736 END IF;
737 -- End of bug 3590208
738 row_count := row_count + 1;
739 END LOOP;
740
741 END IF; -- added by sriram - bug # 2740443. End if of abc
742 -- Start of bug 3590208
743 CLOSE c_excise_exemption ;
744 -- End of bug 3590208
745
746 --Add by Kevin Cheng for inclusive tax Dec 10, 2007
747 ---------------------------------------------------
748 IF p_vat_assessable_value<>p_tax_amount
749 THEN
750 ln_vat_assessable_value:=p_vat_assessable_value;
751 ELSE
752 ln_vat_assessable_value:=1;
753 END IF;
754
755 IF p_assessable_value<>p_tax_amount
756 THEN
757 ln_assessable_value:=p_assessable_value;
758 ELSE
759 ln_assessable_value:=1;
760 END IF;
761
762 IF p_vat_reversal_price<>p_tax_amount
763 THEN
764 ln_vat_reversal_price:=p_vat_reversal_price;
765 ELSE
766 ln_vat_reversal_price:=1;
767 END IF;
768 ---------------------------------------------------
769 --Comment out by Kevin Cheng for inclusive tax Dec 10, 2007
770 /*FOR I in 1..row_count LOOP
771 IF tax_type_tab(I) = 1
772 THEN
773 bsln_amt := p_assessable_value;
774 ELSIF tax_type_tab(I) = 4 THEN
775 bsln_amt := p_vat_assessable_value;
776 ELSIF tax_type_tab(I) = 5 THEN \*bug#6072461, bduvarag*\
777 bsln_amt := p_vat_reversal_price;
778
779 ELSE
780 bsln_amt := p_tax_amount;
781 END IF;
782 IF tax_rate_tab(I) <> 0
783 THEN
784 IF p1(I) < I and p1(I) not in (-1,0)
785 THEN
786 vamt := vamt + nvl(tax_amt_tab(p1(I)),0);
787 ELSIF p1(I) = 0 THEN
788 vamt := vamt + bsln_amt;
789 END IF;
790 IF p2(I) < I and p2(I) not in (-1,0)
791 THEN
792 vamt := vamt + nvl(tax_amt_tab(p2(I)),0);
793 ELSIF p2(I) = 0 THEN
794 vamt := vamt + bsln_amt;
795 END IF;
796 IF p3(I) < I and p3(I) not in (-1,0)
797 THEN
798 vamt := vamt + nvl(tax_amt_tab(p3(I)),0);
799 ELSIF p3(I) = 0 THEN
800 vamt := vamt + bsln_amt;
801 END IF;
802
803
804 IF p4(I) < I and p4(I) not in (-1,0)
805 THEN
806 vamt := vamt + nvl(tax_amt_tab(p4(I)),0);
807 ELSIF p4(I) = 0 THEN
808 vamt := vamt + bsln_amt;
809 END IF;
810 IF p5(I) < I and p5(I) not in (-1,0)
811 THEN
812 vamt := vamt + nvl(tax_amt_tab(p5(I)),0);
813 ELSIF p5(I) = 0 THEN
814 vamt := vamt + bsln_amt;
815 END IF;
816
817 -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
818 -- START BUG 5228046
819
820 IF p6(I) < I and p6(I) not in (-1,0)
821 THEN
822 vamt := vamt + nvl(tax_amt_tab(p6(I)),0);
823 ELSIF p6(I) = 0 THEN
824 vamt := vamt + bsln_amt;
825 END IF;
826 IF p7(I) < I and p7(I) not in (-1,0)
827 THEN
828 vamt := vamt + nvl(tax_amt_tab(p7(I)),0);
829 ELSIF p7(I) = 0 THEN
830 vamt := vamt + bsln_amt;
831 END IF;
832
833 IF p8(I) < I and p8(I) not in (-1,0)
834 THEN
835 vamt := vamt + nvl(tax_amt_tab(p8(I)),0);
836 ELSIF p8(I) = 0 THEN
837 vamt := vamt + bsln_amt;
838 END IF;
839 IF p9(I) < I and p9(I) not in (-1,0)
840 THEN
841 vamt := vamt + nvl(tax_amt_tab(p9(I)),0);
842 ELSIF p9(I) = 0 THEN
843 vamt := vamt + bsln_amt;
844 END IF;
845
846 IF p10(I) < I and p10(I) not in (-1,0)
847 THEN
848 vamt := vamt + nvl(tax_amt_tab(p10(I)),0);
849 ELSIF p10(I) = 0 THEN
850 vamt := vamt + bsln_amt;
851 END IF;
852
853 -- END BUG 5228046
854
855
856
857
858
859
860 v_tax_amt := v_tax_amt + (vamt * (tax_rate_tab(I)/100));
861 base_tax_amt_tab(I) := vamt;
862 tax_amt_tab(I) := nvl(tax_amt_tab(I),0) + v_tax_amt;
863 vamt := 0;
864 v_tax_amt := 0;
865
866 END IF;
867 END LOOP;*/
868 --Add by Kevin Cheng for inclusive tax Dec 10, 2007
869 ----------------------------------------------------
870 FOR I in 1..row_count LOOP
871 IF end_date_tab(I) <> 0 THEN
872 IF tax_type_tab(I) = 1
873 THEN
874 IF ln_assessable_value = 1 THEN
875 bsln_amt := 1;
876 ln_bsln_amt_nr := 0;
877 ELSE
878 bsln_amt := 0;
879 ln_bsln_amt_nr := ln_assessable_value;
880 END IF;
881 ELSIF tax_type_tab(I) = 4 --IF tax_type_tab(I) = 1 THEN
882 THEN
883 IF ln_vat_assessable_value = 1 THEN
884 bsln_amt := 1;
885 ln_bsln_amt_nr := 0;
886 ELSE
887 bsln_amt := 0;
888 ln_bsln_amt_nr := ln_vat_assessable_value;
889 END IF;
890 ELSIF tax_type_tab(I) = 5 --IF tax_type_tab(I) = 1 THEN
891 THEN /*bug#6072461, bduvarag*/
892 IF ln_vat_reversal_price = 1 THEN
893 bsln_amt := 1;
894 ln_bsln_amt_nr := 0;
895 ELSE
896 bsln_amt := 0;
897 ln_bsln_amt_nr := ln_vat_reversal_price;
898 END IF;
899 ELSIF tax_type_tab(I) = 6
900 THEN --IF tax_type_tab(I) = 1 THEN
901 bsln_amt := 0;
902 ln_bsln_amt_nr := 0;
903 ELSE --IF tax_type_tab(I) = 1 THEN
904 bsln_amt := 1;
905 ln_bsln_amt_nr := 0;
906 END IF;
907
908 IF tax_rate_tab(I) <> 0
909 THEN
910 IF p1(I) < I and p1(I) not in (-1,0)
911 THEN
912 vamt := vamt + nvl(tax_amt_tab(p1(I)),0);
913 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P1(I)),0);
914 ELSIF p1(I) = 0 THEN
915 vamt := vamt + bsln_amt;
916 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
917 END IF;
918
919 IF p2(I) < I and p2(I) not in (-1,0)
920 THEN
921 vamt := vamt + nvl(tax_amt_tab(p2(I)),0);
922 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P2(I)),0);
923 ELSIF p2(I) = 0 THEN
924 vamt := vamt + bsln_amt;
925 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
926 END IF;
927
928 IF p3(I) < I and p3(I) not in (-1,0)
929 THEN
930 vamt := vamt + nvl(tax_amt_tab(p3(I)),0);
931 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P3(I)),0);
932 ELSIF p3(I) = 0 THEN
933 vamt := vamt + bsln_amt;
934 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
935 END IF;
936
937
938 IF p4(I) < I and p4(I) not in (-1,0)
939 THEN
940 vamt := vamt + nvl(tax_amt_tab(p4(I)),0);
941 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P4(I)),0);
942 ELSIF p4(I) = 0 THEN
943 vamt := vamt + bsln_amt;
944 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
945 END IF;
946
947 IF p5(I) < I and p5(I) not in (-1,0)
948 THEN
949 vamt := vamt + nvl(tax_amt_tab(p5(I)),0);
950 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P5(I)),0);
951 ELSIF p5(I) = 0 THEN
952 vamt := vamt + bsln_amt;
953 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
954 END IF;
955
956 -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
957 -- START BUG 5228046
958
959 IF p6(I) < I and p6(I) not in (-1,0)
960 THEN
961 vamt := vamt + nvl(tax_amt_tab(p6(I)),0);
962 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P6(I)),0);
963 ELSIF p6(I) = 0 THEN
964 vamt := vamt + bsln_amt;
965 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
966 END IF;
967
968 IF p7(I) < I and p7(I) not in (-1,0)
969 THEN
970 vamt := vamt + nvl(tax_amt_tab(p7(I)),0);
971 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P7(I)),0);
972 ELSIF p7(I) = 0 THEN
973 vamt := vamt + bsln_amt;
974 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
975 END IF;
976
977 IF p8(I) < I and p8(I) not in (-1,0)
978 THEN
979 vamt := vamt + nvl(tax_amt_tab(p8(I)),0);
980 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P8(I)),0);
981 ELSIF p8(I) = 0 THEN
982 vamt := vamt + bsln_amt;
983 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
984 END IF;
985
986 IF p9(I) < I and p9(I) not in (-1,0)
987 THEN
988 vamt := vamt + nvl(tax_amt_tab(p9(I)),0);
989 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P9(I)),0);
990 ELSIF p9(I) = 0 THEN
991 vamt := vamt + bsln_amt;
992 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
993 END IF;
994
995 IF p10(I) < I and p10(I) not in (-1,0)
996 THEN
997 vamt := vamt + nvl(tax_amt_tab(p10(I)),0);
998 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P10(I)),0);
999 ELSIF p10(I) = 0 THEN
1000 vamt := vamt + bsln_amt;
1001 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
1002 END IF;
1003
1004 -- END BUG 5228046
1005
1006 v_tax_amt := v_tax_amt + (vamt * (tax_rate_tab(I)/100));
1007 ln_tax_amt_nr := ln_tax_amt_nr + (ln_vamt_nr * (tax_rate_tab(I)/100));
1008 base_tax_amt_tab(I) := vamt;
1009 tax_amt_tab(I) := nvl(tax_amt_tab(I),0) + v_tax_amt;
1010 lt_tax_amt_non_rate_tab(I) := NVL(lt_tax_amt_non_rate_tab(I),0) + ln_tax_amt_nr; -- tax inclusive
1011 lt_tax_amt_rate_tax_tab(i) := tax_amt_tab(I);
1012
1013 vamt := 0;
1014 v_tax_amt := 0;
1015 ln_tax_amt_nr := 0;
1016 ln_vamt_nr := 0;
1017 END IF; --IF tax_rate_tab(I) <> 0
1018 ELSE --IF end_date_tab(I) <> 0 THEN
1019 tax_amt_tab(I) := 0;
1020 base_tax_amt_tab(I) := 0;
1021 END IF; --IF end_date_tab(I) <> 0 THEN
1022 END LOOP;
1023 ----------------------------------------------------
1024 --Comment out by Kevin Cheng for inclusive tax Dec 10, 2007
1025 /*FOR I in 1..row_count LOOP
1026 IF tax_rate_tab(I) <> 0 THEN
1027 IF p1(I) > I THEN
1028 vamt := vamt + nvl(tax_amt_tab(p1(I)),0);
1029 END IF;
1030 IF p2(I) > I THEN
1031 vamt := vamt + nvl(tax_amt_tab(p2(I)),0);
1032 END IF;
1033 IF p3(I) > I THEN
1034 vamt := vamt + nvl(tax_amt_tab(p3(I)),0);
1035 END IF;
1036 IF p4(I) > I THEN
1037 vamt := vamt + nvl(tax_amt_tab(p4(I)),0);
1038 END IF;
1039 IF p5(I) > I THEN
1040 vamt := vamt + nvl(tax_amt_tab(p5(I)),0);
1041 END IF;
1042
1043 -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
1044 -- START BUG 5228046
1045 IF p6(I) > I THEN
1046 vamt := vamt + nvl(tax_amt_tab(p6(I)),0);
1047 END IF;
1048 IF p7(I) > I THEN
1049 vamt := vamt + nvl(tax_amt_tab(p7(I)),0);
1050 END IF;
1051 IF p8(I) > I THEN
1052 vamt := vamt + nvl(tax_amt_tab(p8(I)),0);
1053 END IF;
1054 IF p9(I) > I THEN
1055 vamt := vamt + nvl(tax_amt_tab(p9(I)),0);
1056 END IF;
1057 IF p10(I) > I THEN
1058 vamt := vamt + nvl(tax_amt_tab(p10(I)),0);
1059 END IF;
1060
1061 -- END BUG 5228046
1062
1063
1064 v_tax_amt := v_tax_amt + (vamt * (tax_rate_tab(I)/100));
1065 IF vamt <> 0 THEN
1066 base_tax_amt_tab(I) := base_tax_amt_tab(I) + vamt;
1067 END IF;
1068 tax_amt_tab(I) := nvl(tax_amt_tab(I),0) + v_tax_amt;
1069
1070 vamt := 0;
1071 v_tax_amt := 0;
1072
1073 END IF;
1074 END LOOP;*/
1075 --Add by Kevin Cheng for inclusive tax Dec 10, 2007
1076 ---------------------------------------------------
1077 FOR I in 1..row_count LOOP
1078 IF end_date_tab( I ) <> 0
1079 THEN
1080 IF tax_rate_tab(I) <> 0 THEN
1081 IF p1(I) > I THEN
1082 vamt := vamt + nvl(tax_amt_tab(p1(I)),0);
1083 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(p1(I)),0); -- tax inclusive
1084 END IF;
1085 IF p2(I) > I THEN
1086 vamt := vamt + nvl(tax_amt_tab(p2(I)),0);
1087 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(p2(I)),0); -- tax inclusive
1088 END IF;
1089 IF p3(I) > I THEN
1090 vamt := vamt + nvl(tax_amt_tab(p3(I)),0);
1091 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(p3(I)),0); -- tax inclusive
1092 END IF;
1093 IF p4(I) > I THEN
1094 vamt := vamt + nvl(tax_amt_tab(p4(I)),0);
1095 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(p4(I)),0); -- tax inclusive
1096 END IF;
1097 IF p5(I) > I THEN
1098 vamt := vamt + nvl(tax_amt_tab(p5(I)),0);
1099 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(p5(I)),0); -- tax inclusive
1100 END IF;
1101
1102 -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
1103 -- START BUG 5228046
1104 IF p6(I) > I THEN
1105 vamt := vamt + nvl(tax_amt_tab(p6(I)),0);
1106 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(p6(I)),0); -- tax inclusive
1107 END IF;
1108 IF p7(I) > I THEN
1109 vamt := vamt + nvl(tax_amt_tab(p7(I)),0);
1110 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(p7(I)),0); -- tax inclusive
1111 END IF;
1112 IF p8(I) > I THEN
1113 vamt := vamt + nvl(tax_amt_tab(p8(I)),0);
1114 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(p8(I)),0); -- tax inclusive
1115 END IF;
1116 IF p9(I) > I THEN
1117 vamt := vamt + nvl(tax_amt_tab(p9(I)),0);
1118 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(p9(I)),0); -- tax inclusive
1119 END IF;
1120 IF p10(I) > I THEN
1121 vamt := vamt + nvl(tax_amt_tab(p10(I)),0);
1122 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(p10(I)),0); -- tax inclusive
1123 END IF;
1124
1125 -- END BUG 5228046
1126 base_tax_amt_tab(I) := vamt;
1127 v_tax_amt := v_tax_amt + (vamt * (tax_rate_tab(I)/100));
1128 ln_tax_amt_nr := ln_tax_amt_nr + (ln_vamt_nr * (tax_rate_tab(I)/100)); -- tax inclusive
1129 IF vamt <> 0 THEN
1130 base_tax_amt_tab(I) := base_tax_amt_tab(I) + vamt;
1131 END IF;
1132 tax_amt_tab(I) := nvl(tax_amt_tab(I),0) + v_tax_amt;
1133 lt_tax_amt_non_rate_tab(I) := NVL(lt_tax_amt_non_rate_tab(I),0) + ln_tax_amt_nr ; -- tax inclusive
1134 lt_tax_amt_rate_tax_tab(i) := tax_amt_tab(I);
1135
1136 vamt := 0;
1137 v_tax_amt := 0;
1138 ln_vamt_nr := 0 ;
1139 ln_tax_amt_nr := 0 ;
1140 END IF;
1141 ELSE --IF end_date_tab( I ) <> 0 THEN
1142 base_tax_amt_tab(I) := vamt;
1143 tax_amt_tab(I) := 0;
1144 END IF; --IF end_date_tab( I ) <> 0 THEN
1145 END LOOP;
1146 ---------------------------------------------------
1147
1148 FOR counter IN 1 .. max_iter LOOP
1149 vamt := 0;
1150 v_tax_amt := 0;
1151 ln_vamt_nr := 0; -- Add by Kevin Cheng for inclusive tax Dec 10, 2007
1152 ln_tax_amt_nr:=0; -- Add by Kevin Cheng for inclusive tax Dec 10, 2007
1153
1154 FOR i IN 1 .. row_count LOOP
1155
1156 /*Commented by aiyer for the bug 4691616
1157 IF tax_rate_tab( i ) <> 0 THEN
1158 */
1159
1160 /*
1161 || start of bug bug#4691616
1162 || IF statement modified by Aiyer for bug#4691616
1163 */
1164
1165 IF ( tax_rate_tab( i ) <> 0 OR tax_rate_zero_tab(I) = -9999 ) AND
1166 end_date_tab( I ) <> 0
1167 THEN
1168 /*
1169 || End of bug 4691616
1170 */
1171 IF tax_type_tab( I ) = 1 THEN
1172 --Comment out by Kevin Cheng for inclusive tax Dec 10, 2007
1173 /*v_amt := p_assessable_value;*/
1174 --Added by Kevin Cheng for inclusive tax Dec 10, 2007
1175 ------------------------------------------------
1176 IF ln_assessable_value =1
1177 THEN
1178 v_amt:=1;
1179 ln_bsln_amt_nr :=0;
1180 ELSE
1181 v_amt :=0;
1182 ln_bsln_amt_nr :=ln_assessable_value;
1183 END IF;
1184 ------------------------------------------------
1185 ELSIF tax_type_tab( I ) = 4 THEN
1186 --Comment out by Kevin Cheng for inclusive tax Dec 10, 2007
1187 /*v_amt := p_vat_assessable_value;*/
1188 --Added by Kevin Cheng for inclusive tax Dec 10, 2007
1189 ------------------------------------------------
1190 IF ln_vat_assessable_value =1
1191 THEN
1192 v_amt:=1;
1193 ln_bsln_amt_nr :=0;
1194 ELSE
1195 v_amt :=0;
1196 ln_bsln_amt_nr :=ln_vat_assessable_value;
1197 END IF;
1198 ------------------------------------------------
1199 ELSIF tax_type_tab( I ) = 5 THEN /*Bug#6072461, bduvarag*/
1200 --Comment out by Kevin Cheng for inclusive tax Dec 10, 2007
1201 /*v_amt := p_vat_reversal_price;*/
1202 --Added by Kevin Cheng for inclusive tax Dec 10, 2007
1203 -------------------------------------
1204 IF ln_vat_reversal_price =1
1205 THEN
1206 v_amt:=1;
1207 ln_bsln_amt_nr :=0;
1208 ELSE
1209 v_amt :=0;
1210 ln_bsln_amt_nr :=ln_vat_reversal_price;
1211 END IF;
1212 ELSIF tax_type_tab(I) = 6 THEN
1213 v_amt:=0;
1214 ln_bsln_amt_nr :=0;
1215 -------------------------------------
1216 ELSE
1217 IF p_assessable_value IN ( 0, -1 ) OR tax_type_tab( I ) <> 1 THEN
1218 /*v_amt := p_tax_amount;*/--Comment out by Kevin Cheng for inclusive tax Dec 10, 2007
1219 v_amt:=1; --Added by Kevin Cheng for inclusive tax Dec 10, 2007
1220 ln_bsln_amt_nr :=0; --Added by Kevin Cheng for inclusive tax Dec 10, 2007
1221 ELSIF p_vat_assessable_value IN ( 0, -1 ) OR tax_type_tab( I ) <> 4 THEN
1222 /*v_amt := p_tax_amount;*/--Comment out by Kevin Cheng for inclusive tax Dec 10, 2007
1223 v_amt:=1; --Added by Kevin Cheng for inclusive tax Dec 10, 2007
1224 ln_bsln_amt_nr :=0; --Added by Kevin Cheng for inclusive tax Dec 10, 2007
1225 ELSIF p_vat_reversal_price IN ( 0, -1 ) OR tax_type_tab( I ) <> 5 THEN /*Bug#6072461, bduvarag*/
1226 /*v_amt := p_tax_amount; */--Comment out by Kevin Cheng for inclusive tax Dec 10, 2007
1227 v_amt:=1; --Added by Kevin Cheng for inclusive tax Dec 10, 2007
1228 ln_bsln_amt_nr :=0; --Added by Kevin Cheng for inclusive tax Dec 10, 2007
1229
1230 END IF;
1231 END IF;
1232
1233 IF p1( i ) <> -1 THEN
1234 IF p1( i ) <> 0 THEN
1235 vamt := vamt + tax_amt_tab( p1( I ) );
1236 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P1(I)),0); --added by Kevin Cheng for inclusive tax Dec 10, 2007
1237 ELSIF p1(i) = 0 THEN
1238 vamt := vamt + v_amt;
1239 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by Kevin Cheng for inclusive tax Dec 10, 2007
1240 END IF;
1241 END IF;
1242
1243 IF p2( i ) <> -1 THEN
1244 IF p2( i ) <> 0 THEN
1245 vamt := vamt + tax_amt_tab( p2( I ) );
1246 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P2(I)),0); --added by Kevin Cheng for inclusive tax Dec 10, 2007
1247 ELSIF p2(i) = 0 THEN
1248 vamt := vamt + v_amt;
1249 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by Kevin Cheng for inclusive tax Dec 10, 2007
1250 END IF;
1251 END IF;
1252
1253 IF p3( i ) <> -1 THEN
1254 IF p3( i ) <> 0 THEN
1255 vamt := vamt + tax_amt_tab( p3( I ) );
1256 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P3(I)),0); --added by Kevin Cheng for inclusive tax Dec 10, 2007
1257 ELSIF p3(i) = 0 THEN
1258 vamt := vamt + v_amt;
1259 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by Kevin Cheng for inclusive tax Dec 10, 2007
1260 END IF;
1261 END IF;
1262
1263 IF p4( i ) <> -1 THEN
1264 IF p4( i ) <> 0 THEN
1265 vamt := vamt + tax_amt_tab( p4( i ) );
1266 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P4(I)),0); --added by Kevin Cheng for inclusive tax Dec 10, 2007
1267 ELSIF p4(i) = 0 THEN
1268 vamt := vamt + v_amt;
1269 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by Kevin Cheng for inclusive tax Dec 10, 2007
1270 END IF;
1271 END IF;
1272
1273 IF p5( i ) <> -1 THEN
1274 IF p5( i ) <> 0 THEN
1275 vamt := vamt + tax_amt_tab( p5( i ) );
1276 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P5(I)),0); --added by Kevin Cheng for inclusive tax Dec 10, 2007
1277 ELSIF p5(i) = 0 THEN
1278 vamt := vamt + v_amt;
1279 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by Kevin Cheng for inclusive tax Dec 10, 2007
1280 END IF;
1281 END IF;
1282
1283 -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
1284 -- START BUG 5228046
1285
1286 IF p6( i ) <> -1 THEN
1287 IF p6( i ) <> 0 THEN
1288 vamt := vamt + tax_amt_tab( p6( I ) );
1289 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P6(I)),0); --added by Kevin Cheng for inclusive tax Dec 10, 2007
1290 ELSIF p6(i) = 0 THEN
1291 vamt := vamt + v_amt;
1292 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by Kevin Cheng for inclusive tax Dec 10, 2007
1293 END IF;
1294 END IF;
1295
1296 IF p7( i ) <> -1 THEN
1297 IF p7( i ) <> 0 THEN
1298 vamt := vamt + tax_amt_tab( p7( I ) );
1299 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P7(I)),0); --added by Kevin Cheng for inclusive tax Dec 10, 2007
1300 ELSIF p7(i) = 0 THEN
1301 vamt := vamt + v_amt;
1302 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by Kevin Cheng for inclusive tax Dec 10, 2007
1303 END IF;
1304 END IF;
1305
1306 IF p8( i ) <> -1 THEN
1307 IF p8( i ) <> 0 THEN
1308 vamt := vamt + tax_amt_tab( p8( I ) );
1309 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P8(I)),0); --added by Kevin Cheng for inclusive tax Dec 10, 2007
1310 ELSIF p8(i) = 0 THEN
1311 vamt := vamt + v_amt;
1312 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by Kevin Cheng for inclusive tax Dec 10, 2007
1313 END IF;
1314 END IF;
1315
1316 IF p9( i ) <> -1 THEN
1317 IF p9( i ) <> 0 THEN
1318 vamt := vamt + tax_amt_tab( p9( i ) );
1319 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P9(I)),0); --added by Kevin Cheng for inclusive tax Dec 10, 2007
1320 ELSIF p9(i) = 0 THEN
1321 vamt := vamt + v_amt;
1322 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by Kevin Cheng for inclusive tax Dec 10, 2007
1323 END IF;
1324 END IF;
1325
1326 IF p10( i ) <> -1 THEN
1327 IF p10( i ) <> 0 THEN
1328 vamt := vamt + tax_amt_tab( p10( i ) );
1329 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P10(I)),0); --added by Kevin Cheng for inclusive tax Dec 10, 2007
1330 ELSIF p10(i) = 0 THEN
1331 vamt := vamt + v_amt;
1332 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by Kevin Cheng for inclusive tax Dec 10, 2007
1333 END IF;
1334 END IF;
1335
1336 -- END BUG 5228046
1337
1338
1339
1340
1341
1342
1343
1344
1345 /*
1346 ||Added by aiyer for the bug 4691616
1347 ||added calculation for base_tax_amt and also changed the else to elsif
1348 */
1349 base_tax_amt_tab(I) := ROUND(vamt, rounding_factor_tab(I) );
1350 -- Add by Kevin Cheng for inclusive tax Dec 10, 2007
1351 ----------------------------------------------------
1352 lt_tax_target_tab(I) := vamt;
1353 ln_tax_amt_nr:=ln_tax_amt_nr+(ln_vamt_nr*(tax_rate_tab(i)/100));
1354 ln_func_tax_amt := v_tax_amt + ( vamt * ( tax_rate_tab( i )/100));
1355 ----------------------------------------------------
1356 v_tax_amt := v_tax_amt + ( vamt * ( tax_rate_tab( i )/100));
1357
1358
1359 ELSIF tax_rate_tab(I) = 0 THEN
1360 /*
1361 || tax_rate_tab(i) will be zero when tax_rate of such a line is null.
1362 || i.e It is UOM based calculation. base_Tax_amount will be same as tax_amount
1363 */
1364 base_tax_amt_tab(I) := tax_amt_tab(i);
1365 v_tax_amt := tax_amt_tab( i );
1366 ln_tax_amt_nr:=lt_tax_amt_non_rate_tab(i); --Add by Kevin Cheng for inclusive tax Jan 08, 2008
1367 lt_tax_target_tab(I):= v_tax_amt; --Add by Kevin Cheng for inclusive tax Dec 10, 2007
1368 /*
1369 ||End of bug 4691616
1370 */
1371 --Add by Kevin Cheng for inclusive tax Dec 10, 2007
1372 ---------------------------------------------------
1373 ELSIF end_date_tab( I ) = 0 THEN
1374 tax_amt_tab(I) := 0;
1375 base_tax_amt_tab(I) := 0;
1376 lt_tax_target_tab(I) := 0;
1377 ---------------------------------------------------
1378 END IF;
1379 --Comment out by Kevin Cheng for inclusive tax Dec 10, 2007
1380 /*tax_amt_tab( I ) := NVL( v_tax_amt, 0 );
1381
1382 IF counter = max_iter THEN
1383 tax_amt_tab( I ) := ROUND( tax_amt_tab( I ), rounding_factor_tab(I) );
1384 END IF;
1385
1386 IF end_date_tab(I) = 0 THEN
1387 tax_amt_tab( i ) := 0;
1388 base_tax_amt_tab(i) := 0;
1389 END IF;*/
1390
1391 --Add by Kevin Cheng for inclusive tax Dec 10, 2007
1392 ---------------------------------------------------
1393 tax_amt_tab( I ) := NVL( v_tax_amt, 0 );
1394 lt_tax_amt_rate_tax_tab(i) := tax_amt_tab(I);
1395 lt_tax_amt_non_rate_tab(I):=ln_tax_amt_nr;
1396 lt_func_tax_amt_tab(I) := NVL(ln_func_tax_amt,0);
1397 IF counter = max_iter THEN
1398 IF end_date_tab(I) = 0 THEN
1399 tax_amt_tab( i ) := 0;
1400 lt_func_tax_amt_tab(i) := 0;
1401 END IF;
1402 END IF;
1403 ---------------------------------------------------
1404
1405 vamt := 0;
1406 v_amt := 0;
1407 v_tax_amt := 0;
1408 ln_func_tax_amt := 0; -- Add by Kevin Cheng for inclusive tax Dec 10, 2007
1409 ln_vamt_nr :=0; -- Add by Kevin Cheng for inclusive tax Dec 10, 2007
1410 ln_tax_amt_nr:=0;-- Add by Kevin Cheng for inclusive tax Dec 10, 2007
1411 END LOOP;
1412 END LOOP;
1413
1414 --Added by Kevin Cheng for inclusive tax Dec 10, 2007
1415 ---------------------------------------------------------------------------------------
1416 FOR I IN 1 .. ROW_COUNT
1417 LOOP
1418 IF lt_inclu_tax_tab(I) = 'Y' THEN
1419 ln_total_tax_per_rupee := ln_total_tax_per_rupee + nvl(lt_tax_amt_rate_tax_tab(I),0) ;
1420 ln_total_non_rate_tax := ln_total_non_rate_tax + nvl(lt_tax_amt_non_rate_tab(I),0);
1421 END IF;
1422 END LOOP;
1423
1424 ln_total_tax_per_rupee := ln_total_tax_per_rupee + 1;
1425
1426 IF ln_total_tax_per_rupee <> 0 THEN
1427 ln_exclusive_price := (NVL(p_tax_amount,0) - ln_total_non_rate_tax ) / ln_total_tax_per_rupee;
1428 END If;
1429
1430 FOR i in 1 .. row_count
1431 LOOP
1432 tax_amt_tab(i) := (lt_tax_amt_rate_tax_tab(I) * ln_exclusive_price ) + lt_tax_amt_non_rate_tab(I);
1433 tax_amt_tab(I) := round(tax_amt_tab(I) ,rounding_factor_tab(I));
1434 END LOOP;
1435 --------------------------------------------------------------------------------------------------------
1436
1437 if NVL(v_rma_ctr,0) = 0 then -- added by sriram - bug # 2740443
1438
1439 FOR rec in tax_cur(p_header_id, p_line_id) LOOP
1440 IF tax_type_tab(rec.lno) <> 2
1441 THEN
1442 IF NVL(rec.inclusive_tax_flag,'N') = 'N' THEN -- Added by Jia for bug# 6846048
1443 v_tax_amt := v_tax_amt + nvl(tax_amt_tab(rec.lno),0);
1444 END IF; -- Added by Jia for bug# 6846048
1445 END IF;
1446
1447 IF transaction_name = 'OE_LINES_UPDATE'
1448 THEN
1449 UPDATE JAI_OM_OE_SO_TAXES
1450 SET tax_amount = nvl(tax_amt_tab(rec.lno),0),
1451 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)),
1452 func_tax_amount = ROUND(nvl(tax_amt_tab(rec.lno),0) * v_currency_conv_factor, rounding_factor_tab(rec.lno) ),
1453 last_update_date = p_last_update_date,
1454 last_updated_by = p_last_updated_by,
1455 last_update_login = p_last_update_login
1456 WHERE line_id = P_line_id
1457 AND header_id = p_header_id
1458 AND tax_line_no = rec.lno;
1459 END IF;
1460 END LOOP;
1461 elsif v_rma_ctr = 1 then
1462 -- added by sriram -following from elsif to end if bug # 2740443
1463 FOR rec in c_rma_tax_cur(p_line_id) LOOP
1464 IF tax_type_tab(rec.lno) <> 2
1465 THEN
1466 v_tax_amt := v_tax_amt + nvl(tax_amt_tab(rec.lno),0);
1467 END IF;
1468
1469 IF transaction_name = 'OE_LINES_UPDATE'
1470 THEN
1471
1472 UPDATE JAI_OM_OE_RMA_TAXES
1473 SET tax_amount = nvl(tax_amt_tab(rec.lno),0),
1474 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)),
1475 func_tax_amount = ROUND(nvl(tax_amt_tab(rec.lno),0) * v_currency_conv_factor, rounding_factor_tab(rec.lno) ),
1476 last_update_date = p_last_update_date,
1477 last_updated_by = p_last_updated_by,
1478 last_update_login = p_last_update_login
1479 WHERE rma_line_id = P_line_id
1480 AND tax_line_no = rec.lno;
1481 END IF;
1482 END LOOP;
1483 end if; -- ends here additions by sriram - bug # 2740443
1484
1485 P_TAX_AMOUNT := nvl(v_tax_amt,0);
1486
1487 EXCEPTION
1488 WHEN OTHERS THEN
1489 p_tax_amount := null;
1490 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
1491 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
1492 app_exception.raise_exception;
1493 END calculate_ato_taxes;
1494
1495
1496 procedure recalculate_oe_taxes(
1497 p_header_id IN NUMBER,
1498 p_line_id IN NUMBER,
1499 p_assessable_value IN NUMBER DEFAULT 0,
1500 p_vat_assess_value IN NUMBER,
1501 p_tax_amount IN OUT NOCOPY NUMBER,
1502 p_inventory_item_id IN NUMBER,
1503 p_line_quantity IN NUMBER,
1504 p_uom_code IN VARCHAR2,
1505 p_currency_conv_factor IN NUMBER,
1506 p_last_updated_date IN DATE,
1507 p_last_updated_by IN NUMBER,
1508 p_last_update_login IN NUMBER
1509 ) IS
1510
1511 -- P_TAX_AMOUNT input parameter will contain the line_tax_amount after successful completion of the procedure which
1512 -- can be used to update the line amount in JAI_OM_OE_SO_LINES
1513
1514 TYPE num_tab IS TABLE OF NUMBER(20,3) INDEX BY BINARY_INTEGER;
1515 TYPE tax_amt_num_tab IS TABLE OF NUMBER(20,3) INDEX BY BINARY_INTEGER;
1516
1517 --Add by Kevin Cheng for inclusive tax Dec 10, 2007
1518 ---------------------------------------------------
1519 TYPE CHAR_TAB IS TABLE OF VARCHAR2(10)
1520 INDEX BY BINARY_INTEGER;
1521
1522 lt_adhoc_tax_tab CHAR_TAB;
1523 lt_inclu_tax_tab CHAR_TAB;
1524 lt_tax_rate_per_rupee NUM_TAB;
1525 lt_cumul_tax_rate_per_rupee NUM_TAB;
1526 lt_tax_rate_zero_tab NUM_TAB;
1527 lt_round_factor_tab NUM_TAB;
1528 lt_tax_amt_rate_tax_tab TAX_AMT_NUM_TAB;
1529 lt_tax_amt_non_rate_tab TAX_AMT_NUM_TAB;
1530 lv_register_code VARCHAR2(20);
1531 ln_exclusive_price NUMBER;
1532 ln_total_non_rate_tax NUMBER := 0;
1533 ln_total_inclusive_factor NUMBER;
1534 ln_bsln_amt_nr NUMBER := 0;
1535 ln_currency_conv_factor NUMBER;
1536 ln_tax_amt_nr NUMBER(38,10) := 0;
1537 ln_vamt_nr NUMBER(38,10) := 0;
1538 ln_excise_jb NUMBER;
1539 ln_total_tax_per_rupee NUMBER;
1540 ln_assessable_value NUMBER;
1541 ln_vat_assessable_value NUMBER;
1542 ---------------------------------------------------
1543
1544 p1 NUM_TAB;
1545 p2 NUM_TAB;
1546 p3 NUM_TAB;
1547 p4 NUM_TAB;
1548 p5 NUM_TAB;
1549
1550 -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
1551 -- START BUG 5228046
1552
1553 p6 NUM_TAB;
1554 p7 NUM_TAB;
1555 p8 NUM_TAB;
1556 p9 NUM_TAB;
1557 p10 NUM_TAB;
1558
1559 -- END BUG 5228046
1560
1561
1562 tax_rate_tab NUM_TAB;
1563 tax_type_tab NUM_TAB;
1564 tax_target_tab NUM_TAB;
1565 tax_amt_tab TAX_AMT_NUM_TAB;
1566 base_tax_amt_tab TAX_AMT_NUM_TAB;
1567 func_tax_amt_tab TAX_AMT_NUM_TAB;
1568 end_date_tab NUM_TAB;
1569
1570 bsln_amt NUMBER; -- := p_tax_amount; --Ramananda for File.Sql.35
1571 v_conversion_rate NUMBER := 0;
1572 v_tax_amt NUMBER(20,3) := 0;
1573 v_func_tax_amt NUMBER(20,3) := 0;
1574 v_rounded_tax NUMBER(20,3) := 0;
1575 vamt NUMBER(20,3) :=0;
1576 v_amt NUMBER;
1577 row_count NUMBER := 1;
1578 counter NUMBER;
1579 max_iter NUMBER := 10;
1580
1581 /* Added by LGOPALSa. Bug 4210102.
1582 * Added Excise education cess in cursor */
1583
1584 /*Added VAT regime in cursor by Ravi for bug#4245365*/
1585 --Comment out by Kevin Cheng for inclusive tax Dec 11, 2007
1586 /*CURSOR c_tax_lines(p_header_id IN NUMBER, p_line_id IN NUMBER) IS
1587 SELECT a.tax_id, a.tax_line_no lno,
1588 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,
1589 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, -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
1590 b.tax_amount, b.tax_rate, b.uom_code, b.end_date valid_date,
1591 a.tax_amount tax_line_amt,
1592 DECODE(aa.regime_code,'VAT',4, decode(upper(b.tax_type),
1593 'EXCISE', 1,
1594 'ADDL. EXCISE', 1,
1595 'OTHER EXCISE', 1,
1596 JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS,1,jai_constants.tax_type_exc_edu_Cess,1, \*Bug 5989740 bduvarag*\
1597 'TDS', 2, 0)) tax_type_val,
1598 b.tax_type,nvl(b.rounding_factor,0) rounding_factor
1599 FROM JAI_OM_OE_SO_TAXES a, JAI_CMN_TAXES_ALL b,jai_regime_tax_types_v aa
1600 WHERE a.header_id = p_header_id
1601 AND a.line_id = p_line_id
1602 AND a.tax_id = b.tax_id
1603 AND aa.tax_type(+) = b.tax_type
1604 ORDER BY a.tax_line_no;*/
1605
1606 -- Add by Kevin Cheng for inclusive tax Dec 10, 2007
1607 CURSOR c_tax_lines( p_header_id IN NUMBER
1608 , p_line_id IN NUMBER
1609 )
1610 IS
1611 SELECT
1612 a.tax_id
1613 , a.tax_category_id
1614 , a.tax_line_no lno
1615 , a.precedence_1 p_1
1616 , a.precedence_2 p_2
1617 , a.precedence_3 p_3
1618 , a.precedence_4 p_4
1619 , a.precedence_5 p_5
1620 , a.precedence_6 p_6
1621 , a.precedence_7 p_7
1622 , a.precedence_8 p_8
1623 , a.precedence_9 p_9
1624 , a.precedence_10 p_10 -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
1625 , b.tax_amount
1626 , b.tax_rate
1627 , b.uom_code
1628 , b.end_date valid_date
1629 , a.tax_amount tax_line_amt
1630 , DECODE( aa.regime_code
1631 , JAI_CONSTANTS.VAT_REGIME
1632 , 4
1633 , decode( upper(b.tax_type)
1634 , 'EXCISE' ,1
1635 , 'ADDL. EXCISE' ,1
1636 , 'OTHER EXCISE' ,1
1637 , jai_constants.tax_type_tds ,2
1638 , jai_constants.tax_type_exc_edu_cess ,6
1639 , jai_constants.tax_type_cvd_edu_cess ,6
1640 , jai_constants.tax_type_sh_exc_edu_cess ,6
1641 , jai_constants.tax_type_sh_cvd_edu_cess ,6
1642 , 0
1643 )
1644 ) tax_type_val
1645 , b.inclusive_tax_flag
1646 , b.mod_cr_percentage
1647 , b.vendor_id
1648 , b.tax_type
1649 , nvl(b.rounding_factor,0) rounding_factor
1650 FROM
1651 Jai_Om_Oe_So_Taxes a
1652 , Jai_Cmn_Taxes_All b
1653 , Jai_Regime_Tax_Types_V aa
1654 WHERE a.header_id = p_header_id
1655 AND a.line_id = p_line_id
1656 AND a.tax_id = b.tax_id
1657 AND aa.tax_type(+) = b.tax_type
1658 ORDER BY
1659 a.tax_line_no;
1660
1661 CURSOR c_uom_class(p_line_uom_code IN VARCHAR2, p_tax_line_uom_code IN VARCHAR2) IS
1662 SELECT A.uom_class
1663 FROM mtl_units_of_measure A, mtl_units_of_measure B
1664 WHERE A.uom_code = p_line_uom_code
1665 AND B.uom_code = p_tax_line_uom_code
1666 AND A.uom_class = B.uom_class;
1667
1668 -- Start of bug 3565499
1669 /*****************
1670 Code modified by aiyer for the bug 3565499
1671 Check whether the excise exemptions exist at the order line level.
1672 *****************/
1673 CURSOR c_excise_exemption
1674 IS
1675 SELECT
1676 '1'
1677 FROM
1678 JAI_OM_OE_SO_LINES jsl
1679 WHERE
1680 jsl.excise_exempt_type IS NOT NULL AND
1681 jsl.line_id = p_line_id ;
1682
1683 lv_excise_exemption_exists VARCHAR2(1);
1684
1685 -- End of bug 3565499
1686 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_om_tax_pkg.recalculate_oe_taxes';
1687 BEGIN
1688 /*-------------------------------------------------------------------------------------------------------------------------
1689 S.No Date(DD/MM/YY) Author and Details of Changes
1690 ---- -------------- -----------------------------
1691 1 31/07/02 Created by Vijay Shankar for Bug# 2485077, 2496481, File Version 615.1
1692 This procedure recalculates the taxes by picking tax lines attached to the sales order line.
1693 If the assessable value of an item is changed, then the excise duty attached to the sales orders
1694 have to be recalculated if they have order lines having assessable value changed. Sales order which
1695 are not ship confirmed( also partially shipped if user specified input says 'Partially Shipped' as 'Y'
1696
1697 3. 20-Feb-2004 Aiyer For the Bug #3565499 File Version 619.1
1698 Issue:-
1699 India Excise Duty Recalculation Concurrent program recalculates the excise duty even for excise exempted Order Lines.
1700 Fix:-
1701 Code has been added to set the tax_rate, tax_amount and base_tax_amount to 0 when Order Like has Excise exemptions and tax is of "Excise" type.
1702 The cursor c_excise_exemption has been added for the purpose.
1703
1704 4. 12-Mar-2005 Bug 4210102. Added by LGOPALSA version 115.1
1705 (1) Added check file syntax
1706 (2) Added NOCOPY for OUT Parameters
1707 (3) Added <> instead of !=
1708 (4) Added Excise education cess type
1709
1710 5. 17/mar-2005 Rchandan for bug#4245365 Version 115.2
1711 Changes made to calculate VAT taxes taking the VAT assessable value as base
1712 New parameter is added for having vat assesable value.
1713
1714 6. 11-Dec-2007 Kevin Cheng Update the logic for inclusive tax calculation
1715 ===============================================================================
1716 Dependencies
1717
1718 Version Dependencies Comments
1719 115.1 IN60106 + 4146708 Service and eduation cess functionality
1720 115.2 4245089 VAT implementation
1721 --------------------------------------------------------------------------------------------------------------------------*/
1722
1723 bsln_amt := p_tax_amount; --Ramananda for File.Sql.35
1724
1725 -- Start of bug 3565499
1726 /*****************
1727 Code modified by aiyer for the bug 3565499
1728 *****************/
1729 OPEN c_excise_exemption;
1730 FETCH c_excise_exemption INTO lv_excise_exemption_exists;
1731 -- End of bug 3565499
1732 FOR rec in c_tax_lines(p_header_id, p_line_id) LOOP
1733
1734 p1(rec.lno) := nvl(rec.p_1,-1);
1735 p2(rec.lno) := nvl(rec.p_2,-1);
1736 p3(rec.lno) := nvl(rec.p_3,-1);
1737 p4(rec.lno) := nvl(rec.p_4,-1);
1738 p5(rec.lno) := nvl(rec.p_5,-1);
1739
1740 -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
1741 -- START BUG 5228046
1742
1743 p6(rec.lno) := nvl(rec.p_6,-1);
1744 p7(rec.lno) := nvl(rec.p_7,-1);
1745 p8(rec.lno) := nvl(rec.p_8,-1);
1746 p9(rec.lno) := nvl(rec.p_9,-1);
1747 p10(rec.lno) := nvl(rec.p_10,-1);
1748
1749 -- END BUG 5228046
1750
1751 tax_rate_tab(rec.lno) := nvl(rec.tax_rate,0);
1752 tax_type_tab(rec.lno) := rec.tax_type_val;
1753 tax_amt_tab(rec.lno) := 0;
1754 base_tax_amt_tab(rec.lno) := 0;
1755
1756 --Add by Kevin Cheng for inclusive tax Dec 11, 2007
1757 ---------------------------------------------------
1758 lt_tax_rate_per_rupee(rec.lno) := NVL(rec.tax_rate,0)/100;
1759 ln_total_tax_per_rupee := 0;
1760 lt_inclu_tax_tab(rec.lno) := NVL(rec.inclusive_tax_flag,'N');
1761 lt_round_factor_tab(rec.lno) := NVL(rec.rounding_factor,0);
1762
1763 IF rec.tax_rate is null THEN
1764 lt_tax_rate_zero_tab(rec.lno) := 0;
1765 ELSIF rec.tax_rate = 0 THEN
1766 lt_tax_rate_zero_tab(rec.lno) := -9999;
1767 ELSE
1768 lt_tax_rate_zero_tab(rec.lno) := rec.tax_rate;
1769 END IF;
1770
1771 lt_tax_amt_rate_tax_tab(rec.lno) := 0;
1772 lt_tax_amt_non_rate_tab(rec.lno) := 0; -- tax inclusive
1773 ---------------------------------------------------
1774
1775 IF tax_rate_tab(rec.lno) = 0 THEN
1776 FOR uom_cls IN c_uom_class(p_uom_code, rec.uom_code) LOOP
1777 INV_CONVERT.inv_um_conversion( p_uom_code, rec.uom_code, p_inventory_item_id, v_conversion_rate);
1778 IF nvl(v_conversion_rate, 0) <= 0 THEN
1779 INV_CONVERT.inv_um_conversion( p_uom_code, rec.uom_code, 0, v_conversion_rate);
1780 IF nvl(v_conversion_rate, 0) <= 0 THEN
1781 v_conversion_rate := 0;
1782 END IF;
1783 END IF;
1784
1785 --Comment out by Kevin Cheng for inclusive tax Dec 11, 2007
1786 /*tax_amt_tab(rec.lno) := (nvl(rec.tax_amount * v_conversion_rate, 0) * p_line_quantity);*/
1787 --Add by Kevin Cheng for inclusive tax Dec 11, 2007
1788 lt_tax_amt_non_rate_tab(rec.lno) := (nvl(rec.tax_amount * v_conversion_rate, 0) * p_line_quantity);
1789
1790 --Comment out by Kevin Cheng for inclusive tax Dec 11, 2007
1791 /*base_tax_amt_tab(rec.lno) := tax_amt_tab(rec.lno);*/
1792 --Add by Kevin Cheng for inclusive tax Dec 11, 2007
1793 base_tax_amt_tab(rec.lno) := lt_tax_amt_non_rate_tab(rec.lno);
1794 END LOOP;
1795
1796 --Comment out by Kevin Cheng for inclusive tax Dec 11, 2007
1797 /*IF tax_amt_tab(rec.lno) = 0 THEN -- this means user has given some adhoc amount
1798 tax_amt_tab(rec.lno) := nvl(rec.tax_line_amt,0);
1799 END IF;*/
1800 --Add by Kevin Cheng for inclusive tax Dec 11, 2007
1801 IF lt_tax_amt_non_rate_tab(rec.lno) = 0 THEN -- this means user has given some adhoc amount
1802 lt_tax_amt_non_rate_tab(rec.lno) := nvl(rec.tax_line_amt,0);
1803 END IF;
1804 END IF;
1805
1806 IF rec.valid_date is NULL or rec.valid_date >= sysdate THEN
1807 end_date_tab(rec.lno) := 1;
1808 ELSE
1809 tax_amt_tab(rec.lno):= 0;
1810 end_date_tab(rec.lno) := 0;
1811 END IF;
1812
1813 -- Start of bug 3565499
1814 /*****************
1815 Code modified by aiyer for the bug 3565499
1816 IF the line is excise exempted and the tax is of type Excise then set the tax_rate, tax_amount and base_tax_amount
1817 to zero.
1818 *****************/
1819 IF c_excise_exemption%FOUND AND
1820 rec.tax_type_val = 1
1821 THEN
1822 /* Set tax_rate_tab = 0, tax_amt_tab = 0 and base_tax_amt_tab = 0 */
1823 tax_rate_tab(rec.lno) := 0;
1824 tax_amt_tab(rec.lno) := 0;
1825 base_tax_amt_tab(rec.lno) := 0;
1826 lt_tax_amt_non_rate_tab(rec.lno) :=0; -- Add by Kevin Cheng for inclusive tax Dec 11, 2007
1827 END IF;
1828 -- End of bug 3565499
1829 row_count := row_count + 1;
1830 END LOOP;
1831 -- Start of bug 3565499
1832 CLOSE c_excise_exemption ;
1833 -- End of bug 3565499
1834
1835 row_count := row_count - 1;
1836
1837 --added by Kevin Cheng for inclusive tax Dec 11, 2007
1838 -------------------------------------------------
1839 IF p_vat_assess_value <> p_tax_amount
1840 THEN
1841 ln_vat_assessable_value := p_vat_assess_value;
1842 ELSE
1843 ln_vat_assessable_value := 1;
1844 END IF;
1845
1846 IF p_assessable_value <> p_tax_amount
1847 THEN
1848 ln_assessable_value := p_assessable_value;
1849 ELSE
1850 ln_assessable_value := 1;
1851 END IF;
1852 ---------------------------------------------------
1853
1854 FOR I in 1..row_count LOOP
1855 IF end_date_tab(I) <> 0 THEN
1856 IF tax_type_tab(I) = 1 THEN
1857 /*bsln_amt := p_assessable_value;*/--Comment out by Kevin Cheng for inclusive tax Dec 11, 2007
1858 --Add by Kevin Cheng for inclusive tax Dec 11, 2007
1859 ---------------------------------------------------
1860 IF ln_assessable_value = 1 THEN
1861 bsln_amt := 1;
1862 ln_bsln_amt_nr := 0;
1863 ELSE
1864 bsln_amt := 0;
1865 ln_bsln_amt_nr := ln_assessable_value;
1866 END IF;
1867 ---------------------------------------------------
1868 ELSIF tax_type_tab(I) = 4 THEN --4245365
1869 /*bsln_amt := p_vat_assess_value;*/--Comment out by Kevin Cheng for inclusive tax Dec 11, 2007
1870 --Add by Kevin Cheng for inclusive tax Dec 11, 2007
1871 ---------------------------------------------------
1872 IF ln_vat_assessable_value = 1 THEN
1873 bsln_amt := 1;
1874 ln_bsln_amt_nr := 0;
1875 ELSE
1876 bsln_amt := 0;
1877 ln_bsln_amt_nr := ln_vat_assessable_value;
1878 END IF;
1879 ELSIF tax_type_tab(I) = 6 THEN --IF tax_type_tab(I) = 1 THEN
1880 bsln_amt := 0;
1881 ln_bsln_amt_nr := 0;
1882 ELSE --IF tax_type_tab(I) = 1 THEN
1883 bsln_amt := 1;
1884 ln_bsln_amt_nr := 0;
1885 ---------------------------------------------------
1886 --Comment out by Kevin Cheng for inclusive tax Dec 11, 2007
1887 /*ELSE
1888 bsln_amt := p_tax_amount;*/
1889 END IF;
1890 IF tax_rate_tab(I) <> 0 THEN
1891
1892 IF p1(I) < I and p1(I) not in (-1,0) THEN
1893 vamt := vamt + nvl(tax_amt_tab(p1(I)),0);
1894 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P1(I)),0); --Add by Kevin Cheng for inclusive tax Dec 11, 2007
1895 ELSIF p1(I) = 0 THEN
1896 vamt := vamt + bsln_amt;
1897 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --Add by Kevin Cheng for inclusive tax Dec 11, 2007
1898 END IF;
1899 IF p2(I) < I and p2(I) not in (-1,0) THEN
1900 vamt := vamt + nvl(tax_amt_tab(p2(I)),0);
1901 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P2(I)),0); --Add by Kevin Cheng for inclusive tax Dec 11, 2007
1902 ELSIF p2(I) = 0 THEN
1903 vamt := vamt + bsln_amt;
1904 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --Add by Kevin Cheng for inclusive tax Dec 11, 2007
1905 END IF;
1906 IF p3(I) < I and p3(I) not in (-1,0) THEN
1907 vamt := vamt + nvl(tax_amt_tab(p3(I)),0);
1908 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P3(I)),0); --Add by Kevin Cheng for inclusive tax Dec 11, 2007
1909 ELSIF p3(I) = 0 THEN
1910 vamt := vamt + bsln_amt;
1911 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --Add by Kevin Cheng for inclusive tax Dec 11, 2007
1912 END IF;
1913 IF p4(I) < I and p4(I) not in (-1,0) THEN
1914 vamt := vamt + nvl(tax_amt_tab(p4(I)),0);
1915 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P4(I)),0); --Add by Kevin Cheng for inclusive tax Dec 11, 2007
1916 ELSIF p4(I) = 0 THEN
1917 vamt := vamt + bsln_amt;
1918 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --Add by Kevin Cheng for inclusive tax Dec 11, 2007
1919 END IF;
1920 IF p5(I) < I and p5(I) not in (-1,0) THEN
1921 vamt := vamt + nvl(tax_amt_tab(p5(I)),0);
1922 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P5(I)),0); --Add by Kevin Cheng for inclusive tax Dec 11, 2007
1923 ELSIF p5(I) = 0 THEN
1924 vamt := vamt + bsln_amt;
1925 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --Add by Kevin Cheng for inclusive tax Dec 11, 2007
1926 END IF;
1927
1928 -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
1929 -- START BUG 5228046
1930
1931 IF p6(I) < I and p6(I) not in (-1,0) THEN
1932 vamt := vamt + nvl(tax_amt_tab(p6(I)),0);
1933 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P6(I)),0); --Add by Kevin Cheng for inclusive tax Dec 11, 2007
1934 ELSIF p6(I) = 0 THEN
1935 vamt := vamt + bsln_amt;
1936 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --Add by Kevin Cheng for inclusive tax Dec 11, 2007
1937 END IF;
1938
1939 IF p7(I) < I and p7(I) not in (-1,0) THEN
1940 vamt := vamt + nvl(tax_amt_tab(p7(I)),0);
1941 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P7(I)),0); --Add by Kevin Cheng for inclusive tax Dec 11, 2007
1942 ELSIF p7(I) = 0 THEN
1943 vamt := vamt + bsln_amt;
1944 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --Add by Kevin Cheng for inclusive tax Dec 11, 2007
1945 END IF;
1946
1947 IF p8(I) < I and p8(I) not in (-1,0) THEN
1948 vamt := vamt + nvl(tax_amt_tab(p8(I)),0);
1949 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P8(I)),0); --Add by Kevin Cheng for inclusive tax Dec 11, 2007
1950 ELSIF p8(I) = 0 THEN
1951 vamt := vamt + bsln_amt;
1952 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --Add by Kevin Cheng for inclusive tax Dec 11, 2007
1953 END IF;
1954
1955 IF p9(I) < I and p9(I) not in (-1,0) THEN
1956 vamt := vamt + nvl(tax_amt_tab(p9(I)),0);
1957 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P9(I)),0); --Add by Kevin Cheng for inclusive tax Dec 11, 2007
1958 ELSIF p9(I) = 0 THEN
1959 vamt := vamt + bsln_amt;
1960 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --Add by Kevin Cheng for inclusive tax Dec 11, 2007
1961 END IF;
1962
1963 IF p10(I) < I and p10(I) not in (-1,0) THEN
1964 vamt := vamt + nvl(tax_amt_tab(p10(I)),0);
1965 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P10(I)),0); --Add by Kevin Cheng for inclusive tax Dec 11, 2007
1966 ELSIF p10(I) = 0 THEN
1967 vamt := vamt + bsln_amt;
1968 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --Add by Kevin Cheng for inclusive tax Dec 11, 2007
1969 END IF;
1970
1971 -- END BUG 5228046
1972
1973 v_tax_amt := v_tax_amt + (vamt * (tax_rate_tab(I)/100));
1974 base_tax_amt_tab(I) := vamt;
1975 tax_amt_tab(I) := nvl(tax_amt_tab(I),0) + v_tax_amt;
1976
1977 --Add by Kevin Cheng for inclusive tax Dec 11, 2007
1978 ---------------------------------------------------
1979 ln_tax_amt_nr := ln_tax_amt_nr + (ln_vamt_nr * (tax_rate_tab(I)/100));
1980 lt_tax_amt_non_rate_tab(I) := NVL(lt_tax_amt_non_rate_tab(I),0) + ln_tax_amt_nr; -- tax inclusive
1981 lt_tax_amt_rate_tax_tab(i) := tax_amt_tab(I);
1982 ln_tax_amt_nr := 0;
1983 ln_vamt_nr := 0;
1984 ---------------------------------------------------
1985
1986 vamt := 0;
1987 v_tax_amt := 0;
1988 END IF;
1989 ELSE
1990 tax_amt_tab(I) := 0;
1991 base_tax_amt_tab(I) := 0;
1992 END IF;
1993 --dbms_output.put_line( '2 tax_amt_tab('||i||') = '||tax_amt_tab(i)
1994 -- ||', base_tax_amt_tab = '||base_tax_amt_tab(i) );
1995 END LOOP;
1996
1997 FOR I in 1..row_count LOOP
1998 IF end_date_tab( I ) <> 0 THEN
1999 IF tax_rate_tab(I) <> 0 THEN
2000 IF p1(I) > I THEN
2001 vamt := vamt + nvl(tax_amt_tab(p1(I)),0);
2002 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(p1(I)),0); --Add by Kevin Cheng for inclusive tax Dec 11, 2007
2003 END IF;
2004 IF p2(I) > I THEN
2005 vamt := vamt + nvl(tax_amt_tab(p2(I)),0);
2006 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(p2(I)),0); --Add by Kevin Cheng for inclusive tax Dec 11, 2007
2007 END IF;
2008 IF p3(I) > I THEN
2009 vamt := vamt + nvl(tax_amt_tab(p3(I)),0);
2010 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(p3(I)),0); --Add by Kevin Cheng for inclusive tax Dec 11, 2007
2011 END IF;
2012 IF p4(I) > I THEN
2013 vamt := vamt + nvl(tax_amt_tab(p4(I)),0);
2014 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(p4(I)),0); --Add by Kevin Cheng for inclusive tax Dec 11, 2007
2015 END IF;
2016 IF p5(I) > I THEN
2017 vamt := vamt + nvl(tax_amt_tab(p5(I)),0);
2018 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(p5(I)),0); --Add by Kevin Cheng for inclusive tax Dec 11, 2007
2019 END IF;
2020
2021
2022 -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
2023 -- START BUG 5228046
2024
2025 IF p6(I) > I THEN
2026 vamt := vamt + nvl(tax_amt_tab(p6(I)),0);
2027 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(p6(I)),0); --Add by Kevin Cheng for inclusive tax Dec 11, 2007
2028 END IF;
2029 IF p7(I) > I THEN
2030 vamt := vamt + nvl(tax_amt_tab(p7(I)),0);
2031 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(p7(I)),0); --Add by Kevin Cheng for inclusive tax Dec 11, 2007
2032 END IF;
2033 IF p8(I) > I THEN
2034 vamt := vamt + nvl(tax_amt_tab(p8(I)),0);
2035 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(p8(I)),0); --Add by Kevin Cheng for inclusive tax Dec 11, 2007
2036 END IF;
2037 IF p9(I) > I THEN
2038 vamt := vamt + nvl(tax_amt_tab(p9(I)),0);
2039 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(p9(I)),0); --Add by Kevin Cheng for inclusive tax Dec 11, 2007
2040 END IF;
2041 IF p10(I) > I THEN
2042 vamt := vamt + nvl(tax_amt_tab(p10(I)),0);
2043 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(p10(I)),0); --Add by Kevin Cheng for inclusive tax Dec 11, 2007
2044 END IF;
2045
2046 -- END BUG 5228046
2047
2048
2049 base_tax_amt_tab(I) := vamt;
2050 v_tax_amt := v_tax_amt + (vamt * (tax_rate_tab(I)/100));
2051 IF vamt <> 0 THEN
2052 base_tax_amt_tab(I) := base_tax_amt_tab(I) + vamt;
2053 END IF;
2054 tax_amt_tab(I) := nvl(tax_amt_tab(I),0) + v_tax_amt;
2055 --Add by Kevin Cheng for inclusive tax Dec 11, 2007
2056 ---------------------------------------------------
2057 ln_tax_amt_nr := ln_tax_amt_nr + (ln_vamt_nr * (tax_rate_tab(I)/100)); -- tax inclusive
2058 lt_tax_amt_non_rate_tab(I) := NVL(lt_tax_amt_non_rate_tab(I),0) + ln_tax_amt_nr ; -- tax inclusive
2059 lt_tax_amt_rate_tax_tab(i) := tax_amt_tab(I);
2060 ln_vamt_nr := 0;
2061 ln_tax_amt_nr := 0;
2062 ---------------------------------------------------
2063 vamt := 0;
2064 v_tax_amt := 0;
2065 END IF;
2066 ELSE
2067 base_tax_amt_tab(I) := vamt;
2068 tax_amt_tab(I) := 0;
2069 END IF;
2070 --dbms_output.put_line( '3 tax_amt_tab('||i||') = '||tax_amt_tab(i)
2071 -- ||', base_tax_amt_tab = '||base_tax_amt_tab(i) );
2072 END LOOP;
2073
2074 FOR counter IN 1 .. max_iter LOOP
2075 vamt := 0;
2076 v_tax_amt := 0;
2077 ln_vamt_nr:= 0; --added by Kevin Cheng for inclusive tax Dec 11, 2007
2078 ln_tax_amt_nr:=0; --added by Kevin Cheng for inclusive tax Dec 11, 2007
2079
2080 FOR i IN 1 .. row_count LOOP
2081 IF tax_rate_tab( i ) <> 0 AND end_date_tab( I ) <> 0 THEN -- modified on 11-10-2k by anuradha.
2082 IF tax_type_tab( I ) = 1 THEN
2083 --Comment out by Kevin Cheng for inclusive tax Dec 11, 2007
2084 /*v_amt := p_assessable_value;*/
2085 --Added by Kevin Cheng for inclusive tax Dec 11, 2007
2086 ------------------------------------------------
2087 IF ln_assessable_value =1
2088 THEN
2089 v_amt:=1;
2090 ln_bsln_amt_nr :=0;
2091 ELSE
2092 v_amt :=0;
2093 ln_bsln_amt_nr :=ln_assessable_value;
2094 END IF;
2095 ------------------------------------------------
2096 ELSIF tax_type_tab(I) = 4 THEN
2097 --Comment out by Kevin Cheng for inclusive tax Dec 11, 2007
2098 /*v_amt := p_vat_assess_value;*/
2099 --Added by Kevin Cheng for inclusive tax Dec 11, 2007
2100 ------------------------------------------------
2101 IF ln_vat_assessable_value =1
2102 THEN
2103 v_amt:= 1;
2104 ln_bsln_amt_nr := 0;
2105 ELSE
2106 v_amt := 0;
2107 ln_bsln_amt_nr := ln_vat_assessable_value;
2108 END IF;
2109 ELSIF tax_type_tab(I) = 6 THEN
2110 v_amt := 0;
2111 ln_bsln_amt_nr := 0;
2112 ------------------------------------------------
2113 ELSE
2114 IF p_assessable_value IN ( 0, -1 ) OR tax_type_tab( I ) <> 1 THEN
2115 --Comment out by Kevin Cheng for inclusive tax Dec 11, 2007
2116 /*v_amt := p_tax_amount;*/
2117 v_amt:=1; --Added by Kevin Cheng for inclusive tax Dec 11, 2007
2118 ln_bsln_amt_nr :=0; --Added by Kevin Cheng for inclusive tax Dec 11, 2007
2119 END IF;
2120 END IF;
2121 IF p1( i ) <> -1 THEN
2122 IF p1( i ) <> 0 THEN
2123 vamt := vamt + tax_amt_tab( p1( I ) );
2124 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P1(I)),0); --added by Kevin Cheng for inclusive tax Dec 11, 2007
2125 ELSIF p1(i) = 0 THEN
2126 vamt := vamt + v_amt;
2127 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by Kevin Cheng for inclusive tax Dec 11, 2007
2128 END IF;
2129 END IF;
2130 IF p2( i ) <> -1 THEN
2131 IF p2( i ) <> 0 THEN
2132 vamt := vamt + tax_amt_tab( p2( I ) );
2133 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P2(I)),0); --added by Kevin Cheng for inclusive tax Dec 11, 2007
2134 ELSIF p2(i) = 0 THEN
2135 vamt := vamt + v_amt;
2136 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by Kevin Cheng for inclusive tax Dec 11, 2007
2137 END IF;
2138 END IF;
2139 IF p3( i ) <> -1 THEN
2140 IF p3( i ) <> 0 THEN
2141 vamt := vamt + tax_amt_tab( p3( I ) );
2142 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P3(I)),0); --added by Kevin Cheng for inclusive tax Dec 11, 2007
2143 ELSIF p3(i) = 0 THEN
2144 vamt := vamt + v_amt;
2145 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by Kevin Cheng for inclusive tax Dec 11, 2007
2146 END IF;
2147 END IF;
2148 IF p4( i ) <> -1 THEN
2149 IF p4( i ) <> 0 THEN
2150 vamt := vamt + tax_amt_tab( p4( i ) );
2151 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P4(I)),0); --added by Kevin Cheng for inclusive tax Dec 11, 2007
2152 ELSIF p4(i) = 0 THEN
2153 vamt := vamt + v_amt;
2154 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by Kevin Cheng for inclusive tax Dec 11, 2007
2155 END IF;
2156 END IF;
2157 IF p5( i ) <> -1 THEN
2158 IF p5( i ) <> 0 THEN
2159 vamt := vamt + tax_amt_tab( p5( i ) );
2160 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P5(I)),0); --added by Kevin Cheng for inclusive tax Dec 11, 2007
2161 ELSIF p5(i) = 0 THEN
2162 vamt := vamt + v_amt;
2163 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by Kevin Cheng for inclusive tax Dec 11, 2007
2164 END IF;
2165 END IF;
2166
2167
2168 -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
2169 -- START BUG 5228046
2170
2171 IF p6( i ) <> -1 THEN
2172 IF p6( i ) <> 0 THEN
2173 vamt := vamt + tax_amt_tab( p6( I ) );
2174 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P6(I)),0); --added by Kevin Cheng for inclusive tax Dec 11, 2007
2175 ELSIF p6(i) = 0 THEN
2176 vamt := vamt + v_amt;
2177 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by Kevin Cheng for inclusive tax Dec 11, 2007
2178 END IF;
2179 END IF;
2180 IF p7( i ) <> -1 THEN
2181 IF p7( i ) <> 0 THEN
2182 vamt := vamt + tax_amt_tab( p7( I ) );
2183 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P7(I)),0); --added by Kevin Cheng for inclusive tax Dec 11, 2007
2184 ELSIF p7(i) = 0 THEN
2185 vamt := vamt + v_amt;
2186 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by Kevin Cheng for inclusive tax Dec 11, 2007
2187 END IF;
2188 END IF;
2189 IF p8( i ) <> -1 THEN
2190 IF p8( i ) <> 0 THEN
2191 vamt := vamt + tax_amt_tab( p8( I ) );
2192 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P8(I)),0); --added by Kevin Cheng for inclusive tax Dec 11, 2007
2193 ELSIF p8(i) = 0 THEN
2194 vamt := vamt + v_amt;
2195 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by Kevin Cheng for inclusive tax Dec 11, 2007
2196 END IF;
2197 END IF;
2198 IF p9( i ) <> -1 THEN
2199 IF p9( i ) <> 0 THEN
2200 vamt := vamt + tax_amt_tab( p9( i ) );
2201 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P9(I)),0); --added by Kevin Cheng for inclusive tax Dec 11, 2007
2202 ELSIF p9(i) = 0 THEN
2203 vamt := vamt + v_amt;
2204 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by Kevin Cheng for inclusive tax Dec 11, 2007
2205 END IF;
2206 END IF;
2207 IF p10( i ) <> -1 THEN
2208 IF p10( i ) <> 0 THEN
2209 vamt := vamt + tax_amt_tab( p10( i ) );
2210 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P10(I)),0); --added by Kevin Cheng for inclusive tax Dec 11, 2007
2211 ELSIF p10(i) = 0 THEN
2212 vamt := vamt + v_amt;
2213 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by Kevin Cheng for inclusive tax Dec 11, 2007
2214 END IF;
2215 END IF;
2216
2217 -- END BUG 5228046
2218
2219 base_tax_amt_tab(I) := vamt;
2220 tax_target_tab(I) := vamt;
2221
2222 v_func_tax_amt := v_tax_amt +( vamt * ( tax_rate_tab( i )/100));
2223 v_tax_amt := v_tax_amt + ( vamt * ( tax_rate_tab( i )/100));
2224 ln_tax_amt_nr:=ln_tax_amt_nr+(ln_vamt_nr*(tax_rate_tab(i)/100));--Add by Kevin Cheng for inclusive tax Jan 08, 2008
2225
2226 ELSIF tax_rate_tab(I) = 0 THEN
2227 base_tax_amt_tab(I) := tax_amt_tab(i);
2228 v_tax_amt := tax_amt_tab( i );
2229 ln_tax_amt_nr:=lt_tax_amt_non_rate_tab(i); --Add by Kevin Cheng for inclusive tax Jan 08, 2008
2230 tax_target_tab(I) := v_tax_amt;
2231 ELSIF end_date_tab( I ) = 0 THEN
2232 tax_amt_tab(I) := 0;
2233 base_tax_amt_tab(I) := 0;
2234 tax_target_tab(I) := 0;
2235 END IF;
2236
2237 tax_amt_tab( I ) := NVL( v_tax_amt, 0 );
2238 lt_tax_amt_non_rate_tab(I):=ln_tax_amt_nr; --Add by Kevin Cheng for inclusive tax Jan 08, 2008
2239 func_tax_amt_tab(I) := NVL(v_func_tax_amt,0);
2240 IF counter = max_iter THEN
2241 IF end_date_tab(I) = 0 THEN
2242 tax_amt_tab( i ) := 0;
2243 func_tax_amt_tab(i) := 0;
2244 END IF;
2245 END IF;
2246 --dbms_output.put_line( '4 tax_amt_tab('||i||') = '||tax_amt_tab(i)
2247 --||', func_tax_amt_tab = '||func_tax_amt_tab(i)
2248 --||', base_tax_amt_tab = '||base_tax_amt_tab(i) );
2249
2250 lt_tax_amt_rate_tax_tab(i) := tax_amt_tab(I); --Add by Kevin Cheng for inclusive tax Dec 11, 2007
2251
2252 vamt := 0;
2253 v_amt := 0;
2254 v_tax_amt := 0;
2255 v_func_tax_amt := 0;
2256 ln_vamt_nr :=0; --added by Kevin Cheng for inclusive tax Dec 11, 2007
2257 ln_tax_amt_nr:=0; --added by Kevin Cheng for inclusive tax Dec 11, 2007
2258 END LOOP;
2259 END LOOP;
2260
2261 --Added by Kevin Cheng for inclusive tax Dec 11, 2007
2262 ---------------------------------------------------------------------------------------
2263 FOR I IN 1 .. ROW_COUNT
2264 LOOP
2265 IF lt_inclu_tax_tab(I) = 'Y'
2266 THEN
2267 ln_total_tax_per_rupee := ln_total_tax_per_rupee + nvl(lt_tax_amt_rate_tax_tab(I),0) ;
2268 ln_total_non_rate_tax := ln_total_non_rate_tax + nvl(lt_tax_amt_non_rate_tab(I),0);
2269 END IF;
2270 END LOOP; --FOR I IN 1 .. ROW_COUNT
2271
2272 ln_total_tax_per_rupee := ln_total_tax_per_rupee + 1;
2273
2274 IF ln_total_tax_per_rupee <> 0
2275 THEN
2276 ln_exclusive_price := (NVL(p_tax_amount,0) - ln_total_non_rate_tax ) / ln_total_tax_per_rupee;
2277 END IF;
2278
2279 FOR i in 1 .. row_count
2280 LOOP
2281 tax_amt_tab (i) := (lt_tax_amt_rate_tax_tab(I) * ln_exclusive_price ) + lt_tax_amt_non_rate_tab(I);
2282 tax_amt_tab(I) := round(tax_amt_tab(I) ,lt_round_factor_tab(I));
2283 END LOOP; --FOR i in 1 .. row_count
2284 --------------------------------------------------------------------------------------------------------
2285
2286 FOR rec in c_tax_lines(p_header_id, p_line_id) LOOP
2287 v_rounded_tax := ROUND(nvl(tax_amt_tab(rec.lno),0), rec.rounding_factor);
2288 IF tax_type_tab(rec.lno) <> 2 THEN
2289 v_tax_amt := v_tax_amt + v_rounded_tax;
2290 END IF;
2291 --dbms_output.put_line( '5 tax_amt_tab('||rec.lno||') = '||tax_amt_tab(rec.lno)
2292 -- ||', func_tax_amt_tab = '||func_tax_amt_tab(rec.lno)
2293 -- ||', base_tax_amt_tab = '||base_tax_amt_tab(rec.lno) );
2294
2295 UPDATE JAI_OM_OE_SO_TAXES
2296 SET tax_amount = v_rounded_tax,
2297 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)),
2298 func_tax_amount = nvl(func_tax_amt_tab(rec.lno),0) * p_currency_conv_factor,
2299 last_update_date= p_last_updated_date,
2300 last_updated_by = p_last_updated_by,
2301 last_update_login = p_last_update_login
2302 WHERE line_id = p_line_id
2303 AND header_id = p_header_id
2304 AND tax_line_no = rec.lno;
2305
2306 END LOOP;
2307
2308 p_tax_amount := nvl(v_tax_amt,0);
2309 EXCEPTION
2310 WHEN OTHERS THEN
2311 p_tax_amount := null;
2312 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
2313 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
2314 app_exception.raise_exception;
2315 END recalculate_oe_taxes;
2316
2317 PROCEDURE recalculate_excise_taxes ( errbuf OUT NOCOPY VARCHAR2
2318 , retcode OUT NOCOPY VARCHAR2
2319 , pn_org_id NUMBER /* This parameter would no more be used after application of the bug 5490479- Aiyer, */
2320 , pn_start_order NUMBER
2321 , pn_end_order NUMBER
2322 , pn_order_type_id NUMBER
2323 , pn_ship_from_org_id NUMBER
2324 )
2325 AS
2326 /*****************************************************************
2327 * Enhancement Bug#2152709, 06/06/2002 Vijay Shankar
2328 * FILENAME: ja_in_assessable_price_change.sql
2329 *
2330 * DESCRIPTION:
2331 * This SQL script is used to Recalculate the Excise Duty if there
2332 * is any change in the asssessable price given in the price list
2333 * attached to the customer
2334 *
2335 * PARAMETERS:
2336 * 1 p_org_id
2337 * 2 p_start_order
2338 * 3 p_end_order
2339 * 4 p_order_type_id
2340 * 5 p_ship_from_org_id
2341 *******************************************************************/
2342
2343 v_header_id oe_order_lines_all.header_id%TYPE;
2344 v_line_id oe_order_lines_all.line_id%TYPE;
2345 v_line_number oe_order_lines_all.line_number%TYPE;
2346 v_shipment_number oe_order_lines_all.shipment_number%TYPE;
2347 v_ship_to_site_use_id oe_order_lines_all.ship_to_ORG_id%TYPE;
2348 v_inventory_item_id oe_order_lines_all.inventory_item_id%TYPE;
2349 v_line_quantity oe_order_lines_all.ordered_quantity%TYPE;
2350 v_uom_code oe_order_lines_all.order_quantity_uom%TYPE;
2351 v_warehouse_id oe_order_lines_all.SHIP_FROM_ORG_ID%TYPE;
2352
2353 v_last_update_date oe_order_lines_all.last_update_date%TYPE;
2354 v_last_updated_by oe_order_lines_all.last_updated_by%TYPE;
2355 v_last_update_login oe_order_lines_all.last_update_login%TYPE;
2356
2357 /* commented by cbabu for Bug#2496481
2358 v_created_by oe_order_lines_all.created_by%TYPE;
2359 v_creation_date oe_order_lines_all.creation_date%TYPE;
2360 v_transaction_name varchar2(30) := 'SO_LINES_UPDATE';
2361 v_original_system_line_ref oe_order_lines_all.ORIG_SYS_LINE_REF%TYPE;
2362 v_original_line_reference oe_order_lines_all.ORIG_SYS_LINE_REF%TYPE;
2363 v_split_from_line_id oe_order_lines_all.split_from_line_id%TYPE;
2364 v_source_document_id oe_order_lines_all.source_document_id%TYPE;
2365 v_source_document_line_id oe_order_lines_all.source_document_line_id%TYPE;
2366 v_source_order_category varchar2(30);
2367 v_source_header_id Number;
2368 v_source_id Number;
2369 */
2370 v_source_document_type_id oe_order_lines_all.source_document_type_id%TYPE;
2371 v_Line_Category_Code oe_order_lines_all.line_category_code%TYPE;
2372 v_reference_line_id oe_order_lines_all.reference_line_id%TYPE; -- used for return lines
2373 v_item_type_code oe_order_lines_all.item_type_code%TYPE;
2374 v_unit_selling_price oe_order_lines_all.unit_selling_price%TYPE;
2375 v_operating_id oe_order_lines_all.org_id%TYPE;
2376 v_old_assessable_value JAI_OM_OE_SO_LINES.assessable_value%TYPE;
2377 v_line_amount NUMBER := 0;
2378
2379 v_original_system_reference VARCHAR2(50);
2380 v_customer_id Number;
2381 v_address_id Number;
2382 v_price_list_id Number;
2383 v_org_id Number;
2384 v_order_number Number;
2385 v_conv_type_code varchar2(30);
2386 v_conv_rate Number;
2387 v_conv_date Date;
2388 v_conv_factor Number;
2389 v_set_of_books_id Number;
2390 v_tax_category_id Number;
2391 v_order_category varchar2(30);
2392 v_assessable_value Number;
2393 v_assessable_amount Number;
2394 v_price_list_uom_code Varchar2(3);
2395 v_converted_rate Number;
2396 v_date_ordered Date;
2397 v_ordered_date Date;
2398
2399 v_assessable_value_date DATE; --Commented for File.sql.35 := SYSDATE; -- cbabu for Bug#2496494
2400
2401 v_line_tax_amount Number := 0;
2402 v_conversion_rate Number := 0;
2403 v_currency_code gl_sets_of_books.currency_code%TYPE;
2404 v_order_source_type varchar2(240);
2405
2406 CURSOR address_cur(p_ship_to_site_use_id IN Number) IS
2407 SELECT nvl(cust_acct_site_id , 0) address_id
2408 FROM hz_cust_site_uses_all A -- Removed ra_site_uses_all for Bug# 4434287
2409 WHERE A.site_use_id = p_ship_to_site_use_id; /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
2410 --WHERE A.site_use_id = NVL(p_ship_to_site_use_id,0);
2411
2412 /* Bug 5243532. Added by Lakshmi Gopalsami
2413 * Removed the cursor set_of_books_cur and implemented using caching logic.
2414 */
2415
2416 /* commented by cbabu for Bug#2496481
2417 CURSOR order_tax_amount_Cur (p_header_id Number, p_line_id Number) IS
2418 Select sum(a.tax_amount)
2419 From JAI_OM_OE_SO_TAXES a, JAI_CMN_TAXES_ALL b
2420 Where a.Header_ID = p_header_id
2421 and a.line_id = p_line_id
2422 and b.tax_id = a.tax_id
2423 and b.tax_type != 'TDS';
2424 */
2425
2426 CURSOR Get_Assessable_Value_Cur(p_customer_id Number,p_address_id Number, p_inventory_item_id Number,
2427 p_uom_code Varchar2, p_ordered_date date ) IS -- p_ordered_date variable name is misleading but the actual value it get is the SYSDATE
2428 SELECT b.operand list_price,
2429 c.product_uom_code list_price_uom_code
2430 FROM JAI_CMN_CUS_ADDRESSES a, QP_LIST_LINES b, qp_pricing_attributes c
2431 WHERE a.customer_id = p_customer_id
2432 AND a.address_id = p_address_id
2433 AND a.price_list_id = b.LIST_header_ID
2434 AND c.list_line_id = b.list_line_id
2435 AND c.product_attr_value = p_inventory_item_id /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
2436 AND trunc(p_ordered_date) BETWEEN trunc(nvl( start_date_active, p_ordered_date)) -- cbabu for Bug#2496494
2437 AND trunc(nvl( end_date_active, SYSDATE));
2438
2439 /*
2440 AND c.product_attr_value = TO_CHAR(p_inventory_item_id) --2001/02/14 Manohar Mishra
2441 AND c.product_uom_code = p_uom_code --2001/10/09 Anuradha Parthasarathy
2442 AND TRUNC(NVL(b.end_date_active,SYSDATE)) >= TRUNC(p_ordered_date);
2443 */
2444
2445 CURSOR get_source_id IS
2446 Select order_source_id
2447 From oe_order_headers_all
2448 Where header_id = v_header_id;
2449
2450 CURSOR Get_Order_Source_Type(P_Source_Document_Type_Id Number) is
2451 Select Name
2452 from OE_ORDER_SOURCES
2453 where Order_Source_Id=P_Source_Document_Type_Id;
2454
2455 ----------------------------------------------------------------------------------
2456 CURSOR line_details1(p_order_number1 IN NUMBER, p_order_number2 IN NUMBER, p_ship_from_org_id IN NUMBER,
2457 p_org_id IN NUMBER, p_order_type_id IN NUMBER) IS
2458 SELECT base.header_id, base.line_id, base.line_number, base.SHIPMENT_NUMBER,
2459 base.ship_to_ORG_id, base.inventory_item_id, base.ordered_quantity, base.order_quantity_uom,
2460 base.SHIP_FROM_ORG_ID, base.creation_date, base.created_by, base.last_update_date,
2461 base.last_updated_by, base.last_update_login, base.ORIG_SYS_LINE_REF,
2462 base.Line_Category_Code, base.reference_line_id, base.item_type_code, base.split_from_line_id,
2463 base.unit_selling_price, base.ORG_ID, base.SOURCE_DOCUMENT_ID, base.SOURCE_DOCUMENT_LINE_ID,
2464 base.SOURCE_DOCUMENT_TYPE_ID, ja.assessable_value,
2465 NVL(head.org_id,0) org_id1, head.SOLD_TO_ORG_ID, head.SOURCE_DOCUMENT_ID hsdi, head.order_number,
2466 head.price_list_id, head.ORDER_CATEGORY_CODE, head.ORIG_SYS_DOCUMENT_REF, head.TRANSACTIONAL_CURR_CODE,
2467 head.conversion_type_code, head.conversion_rate, head.CONVERSION_RATE_DATE, nvl(head.ORDERED_DATE, head.creation_date) ordered_date
2468 FROM oe_order_headers_all head, oe_order_lines_all base, JAI_OM_OE_SO_LINES ja
2469 WHERE head.header_id = base.header_id
2470 and base.line_id = ja.line_id
2471 and base.OPEN_FLAG = 'Y'
2472 and base.line_category_code = 'ORDER'
2473 and base.ship_from_org_id = p_ship_from_org_id
2474 and base.org_id = p_org_id
2475 and head.order_type_id = p_order_type_id
2476 and head.order_number between p_order_number1 and p_order_number2;
2477 --and base.flow_status_code IN ( 'ENTERED', 'BOOKED' )
2478
2479 --vijay shankar some important flags
2480 -- OPEN_FLAG, BOOKED_FLAG, CALCELLED_FLAG, FULFILLED_FLAG, SHIPPABLE_FLAG, SHIP_MODEL_COMPLETE_FLAG,
2481 -- AUTHORIZED_TO_SHIP_FLAG, SHIPPING_INTERFACED_FLAG, CALCULATE_PRICE_FLAG, ORDER_CATEGORY(return,order etc)
2482 -- head.FLOW_STATUS_CODE, line.FLOW_STATUS_CODE, ORDER_CATEGORY_CODE, LINE_CATEGORY
2483
2484 CURSOR get_shipped_line(p_line_id IN NUMBER, p_header_id IN NUMBER ) IS
2485 SELECT order_line_id
2486 FROM JAI_OM_WSH_LINES_ALL
2487 WHERE order_line_id = p_line_id
2488 AND order_header_id = p_header_id;
2489
2490 ii NUMBER; -- := 1; Commented for File.Sql.35
2491 p_start_order NUMBER;
2492 p_end_order NUMBER;
2493 p_org_id NUMBER;
2494 p_order_type_id NUMBER;
2495 p_ship_from_org_id NUMBER;
2496
2497 v_check_line NUMBER;
2498
2499 ln_vat_assessable_value NUMBER;
2500 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_om_tax_pkg.recalculate_excise_taxes';
2501
2502 /* Bug 5243532. Added by Lakshmi Gopalsami
2503 * Defined variable for implementing caching logic.
2504 */
2505 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
2506
2507
2508
2509 BEGIN
2510 /*-------------------------------------------------------------------------------------------------------------------
2511 S.No Date(DD/MM/YYYY) Author and Details of Changes
2512 ---- -------------- -----------------------------
2513 1 05/08/2002 Vijay Shankar for Bug# 2496481
2514 When new taxes are added to order after tax defaultation, then change the assessable price attached
2515 to the item and run the excise duty recalculation Concurrent, the added taxes are not getting calculated
2516 properly. Base bug 2152709 is made obsolete with this patch and this patch becomes a prerequisite
2517 patch for future patches related to this object.
2518
2519 2 05/08/2002 Vijay Shankar for Bug# 2496494
2520 When price list contains more than one price list lines for the same item, which has end date greater than the ordered_date
2521 or end_date is null then more than one price list lines are picked up by the cursor.
2522
2523 3 30/05/2005 Brathod, For Bug# 4400993
2524 Issue:-
2525 ja_in_assessible_price_change.sql was a pl-sql executable script, which needs to be
2526 a procedure inside a package
2527 Fix:-
2528 Script is migrated into a jai_om_tax_pkg as recalculate_excise_tax procedure
2529 with following input parameters.
2530 errbuf, retcode, pn_org_id, pn_start_order
2531 , pn_end_order, pn_order_type_id, pn_ship_from_org_id
2532 In JAINREPC Concurrent Definition execution mentod modified from
2533 SQL*Plus to PL-SQL Stored Procedure
2534
2535 4 26/05/2005 Ramananda for Bug#4540783. File Version 120.2
2536 While running the concurrent - INDIA - EXCISE DUTY RECALCULATION, system is giving error -
2537 "wrong number or types of arguments in call to 'jai_om_tax_pkg.recalculate_oe_taxes'"
2538 Added a new parameter - p_vat_assess_value and value for the same as NULL,
2539 while calling procedure jai_om_tax_pkg.recalculate_oe_taxes
2540
2541 --------------------------------------------------------------------------------------------------------------------*/
2542 v_assessable_value_date := sysdate;
2543 ii := 1;
2544
2545 FND_FILE.PUT_LINE(FND_FILE.LOG,'STARTED the procedure ' );
2546
2547 /* Added by Brathod for Bug# 4400993*/
2548 /*
2549 || Start of bug 5490479
2550 || Added by aiyer for the bug 5490479
2551 || Get the operating unit (org_id)
2552 */
2553 p_org_id := mo_global.get_current_org_id;
2554 fnd_file.put_line(fnd_file.log, 'Operating unit p_org_id is -> '||p_org_id);
2555 /*End of bug 5490479 */
2556
2557 p_start_order := pn_start_order;
2558 p_end_order := pn_end_order ;
2559 p_order_type_id := pn_order_type_id;
2560 p_ship_from_org_id := pn_ship_from_org_id;
2561 /* End of Bug# 4400993 */
2562
2563 -- added by cbabu for Bug#2496481, start
2564 v_last_update_date := SYSDATE;
2565 v_last_updated_by := FND_GLOBAL.USER_ID;
2566 v_last_update_login := FND_GLOBAL.LOGIN_ID;
2567 -- added by cbabu for Bug#2496481, end
2568
2569 FND_FILE.PUT_LINE(FND_FILE.LOG,' Sl. No., Order Number, Header_id , line_id, line_number'
2570 ||', ship_to_site_use_id, inventory_item_id, line_quantity, uom_code'
2571 ||',warehouse_id, Line_Category_Code, reference_line_id, item_type_code'
2572 ||',original_line_reference, unit_selling_price, operating_id, org_id'
2573 ||',SOLD_TO_ORG_ID, hsdi, price_list_id, ORDER_CATEGORY_CODE, ORIG_SYS_DOCUMENT_REF'
2574 ||',TRANSACTIONAL_CURR_CODE, conversion_type_code, conversion_rate'
2575 ||',CONVERSION_RATE_DATE, ORDERED_DATE');
2576
2577 FOR rec IN line_details1( p_start_order, p_end_order,p_ship_from_org_id, p_org_id, p_order_type_id) LOOP
2578 ------------------------------------------------------------------------
2579 FND_FILE.PUT_LINE(FND_FILE.LOG,ii||', ' || rec.order_number||', '||rec.header_id|| ', '||rec.line_id|| ', '||rec.line_number||', '||rec.SHIP_FROM_ORG_ID
2580 ||', '||rec.inventory_item_id|| ', '||rec.ordered_quantity||', '||rec.order_quantity_uom|| ', '||rec.SHIP_FROM_ORG_ID
2581 ||', '||rec.Line_Category_Code||', '||rec.reference_line_id|| ', '||rec.item_type_code
2582 ||', '||rec.ORIG_SYS_LINE_REF||', '||rec.unit_selling_price||', '||rec.org_id
2583 ||', '||rec.org_id1|| ', '||rec.SOLD_TO_ORG_ID||', '||rec.hsdi
2584 ||', '||rec.price_list_id||', '||rec.ORDER_CATEGORY_CODE|| ', '||rec.ORIG_SYS_DOCUMENT_REF||', '||rec.TRANSACTIONAL_CURR_CODE
2585 ||', '||rec.conversion_type_code||', '||rec.conversion_rate||', '||rec.CONVERSION_RATE_DATE||', '||rec.ORDERED_DATE);
2586 ------------------------------------------------------------------------
2587
2588 OPEN get_shipped_line(rec.line_id,rec.header_id);
2589 FETCH get_shipped_line INTO v_check_line;
2590 CLOSE get_shipped_line;
2591
2592 IF v_check_line IS NULL THEN --- zzz
2593
2594 v_header_id := rec.header_id;
2595 v_line_id := rec.line_id;
2596 v_line_number := rec.line_number;
2597 v_shipment_number := rec.shipment_number;
2598 v_ship_to_site_use_id := rec.SHIP_TO_ORG_ID;
2599 v_inventory_item_id := rec.inventory_item_id;
2600 v_line_quantity := rec.ordered_quantity;
2601 v_uom_code := rec.order_quantity_uom;
2602 v_warehouse_id := rec.SHIP_FROM_ORG_ID;
2603
2604 /* commented for cbabu for Bug#2496481
2605 v_creation_date := rec.creation_date;
2606 v_created_by := rec.created_by;
2607 v_last_update_date := rec.last_update_date;
2608 v_last_updated_by := rec.last_updated_by;
2609 v_last_update_login := rec.last_update_login;
2610 v_original_system_line_ref := rec.ORIG_SYS_LINE_REF;
2611 v_original_line_reference := rec.ORIG_SYS_LINE_REF;
2612 v_split_from_line_id := null;
2613 v_source_document_id := null;
2614 v_source_document_line_id := null;
2615 */
2616
2617 v_source_document_type_id := rec.source_document_type_id;
2618 v_Line_Category_Code := rec.Line_Category_Code;
2619 v_reference_line_id := rec.reference_line_id;
2620 v_item_type_code := rec.item_type_code;
2621 v_unit_selling_price := rec.unit_selling_price;
2622 v_operating_id := rec.org_id;
2623 v_old_assessable_value := rec.assessable_value;
2624 v_line_amount := nvl(v_line_quantity,0) * nvl(v_unit_selling_price,0);
2625
2626 FND_FILE.PUT_LINE(FND_FILE.LOG,ii|| ', 1 v_line_amount = ' ||v_line_amount) ;
2627
2628 v_org_id := rec.org_id1;
2629 v_customer_id := rec.sold_to_org_id;
2630
2631 /* commented for cbabu for Bug#2496481
2632 v_source_header_id := rec.hsdi;
2633 */
2634
2635 v_order_number := rec.order_number;
2636 v_price_list_id := rec.price_list_id;
2637 v_order_category := rec.order_category_code;
2638 v_original_system_reference := rec.orig_sys_document_ref;
2639 v_currency_code := rec.transactional_curr_code;
2640 v_conv_type_code := rec.conversion_type_code;
2641 v_conv_rate := rec.conversion_rate;
2642 v_conv_date := rec.conversion_rate_date;
2643 v_date_ordered := rec.ordered_date;
2644
2645 IF v_conv_date IS NULL THEN
2646 v_conv_date := v_date_ordered;
2647 END IF;
2648
2649 Open Get_Order_Source_Type(v_source_document_type_id);
2650 Fetch Get_Order_Source_Type Into v_order_source_type;
2651 Close Get_Order_Source_Type;
2652
2653 IF v_item_type_code = 'STANDARD'
2654 AND (v_reference_line_id IS NOT NULL or v_order_category = 'RETURN')
2655 -- AND nvl(v_source_document_type_id,0) != 2
2656 AND UPPER(v_order_source_type) <> 'COPY'
2657 THEN --- aaa -- if execution enters here, it means it is a return order which need not be considered for recalculation
2658 --return;
2659 null;
2660 FND_FILE.PUT_LINE(FND_FILE.LOG,ii||', 2 , returning v_item_type_code = '||v_item_type_code||', v_order_category = '||v_order_category||', v_reference_line_id = '||v_reference_line_id);
2661 ELSIF v_line_category_code in ('ORDER') THEN --- aaa, compute assessable value
2662
2663 /* Bug 5243532. Added by Lakshmi Gopalsami
2664 * Removed the cursor set_of_books_cur and implemented using caching logic.
2665 */
2666 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
2667 (p_org_id => v_warehouse_id );
2668 v_set_of_books_id := l_func_curr_det.ledger_id;
2669
2670 v_converted_rate := jai_cmn_utils_pkg.currency_conversion (v_set_of_books_id ,
2671 v_currency_code, v_conv_date , v_conv_type_code, v_conv_rate);
2672
2673 OPEN address_cur(v_ship_to_site_use_id);
2674 FETCH address_cur INTO v_address_id;
2675 CLOSE address_cur;
2676
2677 FND_FILE.PUT_LINE(FND_FILE.LOG,ii||' 3 v_address_id = '||v_address_id||', 121 v_customer_id = '||v_customer_id);
2678 -- Fetch Assessable Price List Value for the
2679 -- given Customer and Location Combination
2680 -- OPEN Get_Assessable_Value_Cur(v_customer_id, v_address_id, v_inventory_item_id, v_uom_code, v_date_ordered);
2681 /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980 --added to_char*/
2682 OPEN Get_Assessable_Value_Cur(v_customer_id, v_address_id, to_char(v_inventory_item_id), v_uom_code, v_assessable_value_date); -- cbabu for Bug#2496494
2683 FETCH Get_Assessable_Value_Cur INTO v_assessable_value, v_price_list_uom_code;
2684 CLOSE Get_Assessable_Value_Cur;
2685
2686 IF v_assessable_value IS NULL THEN --5
2687 -- Fetch Assessable Price List Value for the
2688 -- given Customer and NULL LOCATION Combination
2689 -- OPEN Get_Assessable_Value_Cur(v_customer_id, 0, v_inventory_item_id, v_uom_code, v_date_ordered);
2690 /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980 --added to_char*/
2691 OPEN Get_Assessable_Value_Cur(v_customer_id, 0, to_char(v_inventory_item_id), v_uom_code, v_assessable_value_date); -- cbabu for Bug#2496494
2692 FETCH Get_Assessable_Value_Cur INTO v_assessable_value, v_price_list_uom_code;
2693 CLOSE Get_Assessable_Value_Cur;
2694 FND_FILE.PUT_LINE(FND_FILE.LOG,ii||' 4 v_assessable_value = '||v_assessable_value);
2695 END IF; --5
2696
2697 FND_FILE.PUT_LINE(FND_FILE.LOG,ii||' 5 , v_assessable_value = '||v_assessable_value||', v_price_list_uom_code = '||v_price_list_uom_code);
2698 IF NVL(v_assessable_value,0) > 0 THEN --6
2699 -- If still the Assessable Value is available
2700 IF v_price_list_uom_code IS NOT NULL THEN --7
2701 FND_FILE.PUT_LINE(FND_FILE.LOG,ii||' 6 , v_uom_code = '||v_uom_code||', v_inventory_item_id = '||v_inventory_item_id||', v_conversion_rate = '||v_conversion_rate);
2702 INV_CONVERT.inv_um_conversion(v_uom_code, v_price_list_uom_code, v_inventory_item_id, v_conversion_rate);
2703 IF nvl(v_conversion_rate, 0) <= 0 THEN --8
2704 FND_FILE.PUT_LINE(FND_FILE.LOG,ii||' 7 , v_conversion_rate = '||v_conversion_rate);
2705 INV_CONVERT.inv_um_conversion(v_uom_code, v_price_list_uom_code, 0, v_conversion_rate);
2706 FND_FILE.PUT_LINE(FND_FILE.LOG,ii||' 8 , v_conversion_rate = '||v_conversion_rate);
2707 IF nvl(v_conversion_rate, 0) <= 0 THEN --9
2708 v_conversion_rate := 0;
2709 END IF; --9
2710 END IF; --8
2711 END IF; --7
2712
2713 v_assessable_value := NVL(1/v_converted_rate,0) * nvl(v_assessable_value,0) * v_conversion_rate;
2714 v_assessable_amount := nvl(v_assessable_value,0) * v_line_quantity;
2715
2716 FND_FILE.PUT_LINE(FND_FILE.LOG,ii||' 9 v_assessable_amount = '||v_assessable_amount||', v_assessable_value = '||v_assessable_value);
2717 ELSE --6
2718 -- If the assessable value is not available
2719 -- then pick up the Line price for Tax Calculation
2720 v_assessable_value := NVL(v_unit_selling_price,0);
2721 v_assessable_amount := v_line_amount;
2722 FND_FILE.PUT_LINE(FND_FILE.LOG,ii||' 10 v_assessable_amount = '||v_assessable_amount);
2723 END IF; --6
2724
2725 IF v_old_assessable_value <> v_assessable_value THEN ---bbb
2726
2727 /* commented by cbabu for Bug#2496481
2728 Open get_source_id;
2729 Fetch get_source_id into v_source_id;
2730 Close get_source_id;
2731
2732 IF v_line_category_code in ('ORDER') THEN --11 , and V_Order_Source_Type = 'Internal'
2733
2734 FND_FILE.PUT_LINE(FND_FILE.LOG,ii||' 11');
2735 ja_in_tax_pkg.ja_in_cust_default_taxes(
2736 v_warehouse_id, v_customer_id,v_ship_to_site_use_id, v_inventory_item_id,
2737 v_header_id,v_line_id,v_tax_category_id);
2738 IF v_tax_category_id IS NULL THEN
2739 FND_FILE.PUT_LINE(FND_FILE.LOG,ii||' 12');
2740 ja_in_tax_pkg.ja_in_org_default_taxes(v_warehouse_id, v_inventory_item_id, v_tax_category_id);
2741 ELSE --13
2742 v_line_tax_amount := v_line_amount;
2743 FND_FILE.PUT_LINE(FND_FILE.LOG,ii||' 13 = '||v_line_tax_amount);
2744 END IF; --13
2745
2746 ja_in_tax_pkg.ja_in_calc_prec_taxes(
2747 v_transaction_name, v_tax_category_id, v_header_id, v_line_id,
2748 v_assessable_amount, v_line_tax_amount, v_inventory_item_id, v_line_quantity,
2749 v_uom_code, '', '', v_converted_rate,
2750 v_creation_date, v_created_by, v_last_update_date, v_last_updated_by, v_last_update_login);
2751
2752 END IF; --11
2753
2754 OPEN order_tax_amount_Cur(v_header_id, v_LINE_ID);
2755 FETCH order_tax_amount_Cur INTO v_line_tax_amount;
2756 CLOSE order_tax_amount_Cur;
2757 */
2758
2759 -- added by cbabu for Bug#2496481, start
2760 -- v_line_tax_amount is the OUT variable for ja_in_tax_recalc procedure which contains total tax amount for that line
2761 v_line_tax_amount := v_line_amount;
2762 FND_FILE.PUT_LINE(FND_FILE.LOG,ii||' 13 = '||v_line_tax_amount);
2763 /* Added by Brathod for Bug# 4400993 */
2764 ln_vat_assessable_value := jai_general_pkg.ja_in_vat_assessable_value
2765 (
2766 p_party_id => v_customer_id ,
2767 p_party_site_id => v_ship_to_site_use_id ,
2768 p_inventory_item_id => v_inventory_item_id ,
2769 p_uom_code => v_uom_code ,
2770 p_default_price => v_unit_selling_price ,
2771 p_ass_value_date => v_date_ordered ,
2772 p_party_type => 'C'
2773 );
2774
2775 ln_vat_assessable_value := nvl(ln_vat_assessable_value,0) * v_line_quantity;
2776 /* End OF Bug# 4400993 */
2777
2778 jai_om_tax_pkg.recalculate_oe_taxes
2779 (
2780 v_header_id,
2781 v_line_id,
2782 v_assessable_amount,
2783 ln_vat_assessable_value , /* Added by Brathod for Bug# 4400993, Ramananda for Bug#4540783 */
2784 v_line_tax_amount,
2785 v_inventory_item_id,
2786 v_line_quantity,
2787 v_uom_code,
2788 v_converted_rate,
2789 v_last_update_date,
2790 v_last_updated_by,
2791 v_last_update_login
2792 );
2793 -- added by cbabu for Bug#2496481, end
2794
2795 UPDATE JAI_OM_OE_SO_LINES
2796 SET assessable_value = v_assessable_value,
2797 tax_amount = nvl(v_line_tax_amount,0),
2798 line_amount = v_line_amount,
2799 line_tot_amount = v_line_amount + nvl(v_line_tax_amount,0),
2800 last_update_date = v_last_update_date,
2801 last_updated_by = v_last_updated_by,
2802 last_update_login = v_last_update_login
2803 WHERE line_id = v_line_id and header_id = v_header_id;
2804
2805 END IF; ---bbb
2806 END IF; ---aaa
2807
2808 FND_FILE.PUT_LINE(FND_FILE.LOG,ii||' 14');
2809 ------------------------------------------------------------------------
2810 v_original_system_reference := null;
2811 v_customer_id := null;
2812 v_address_id := null;
2813 v_price_list_id := null;
2814 v_org_id := null;
2815 v_order_number := null;
2816 v_conv_type_code := null;
2817 v_conv_rate := null;
2818 v_conv_date := null;
2819 v_conv_factor := null;
2820 v_set_of_books_id := null;
2821 v_tax_category_id := null;
2822 v_order_category := null;
2823 v_line_amount := 0;
2824 /* commented by cbabu for Bug#2496481
2825 v_source_header_id := null;
2826 v_source_order_category := null;
2827 v_source_id := null;
2828 */
2829 v_assessable_value := null;
2830 v_assessable_amount := null;
2831 v_price_list_uom_code := null;
2832 v_converted_rate := null;
2833 v_date_ordered := null;
2834 v_ordered_date := null;
2835
2836 v_line_tax_amount := 0;
2837 v_conversion_rate := 0;
2838 v_currency_code := null;
2839 v_order_source_type := null;
2840 v_source_document_type_id := null;
2841 v_org_id := null;
2842 v_customer_id := null;
2843 v_order_number := null;
2844 v_price_list_id := null;
2845 v_order_category := null;
2846 v_original_system_reference := null;
2847 v_currency_code := null;
2848 v_conv_type_code := null;
2849 v_conv_rate := null;
2850 v_conv_date := null;
2851 v_date_ordered := null;
2852 ------------------------------------------------------------------------
2853
2854 END IF; --- zzz
2855 v_check_line := null;
2856 ii := ii + 1;
2857
2858 END LOOP;
2859 COMMIT;
2860 FND_FILE.PUT_LINE(FND_FILE.LOG,'END of the procedure JA_IN_ASSESSABLE_PRICE_CHANGE');
2861
2862 EXCEPTION
2863 WHEN OTHERS THEN
2864 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
2865 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
2866 app_exception.raise_exception;
2867 END recalculate_excise_taxes;
2868
2869 END jai_om_tax_pkg;
[Home] [Help]