1 PACKAGE BODY jai_om_tax_pkg AS
2 /* $Header: jai_om_tax.plb 120.26.12020000.6 2013/02/28 09:40:05 mmurtuza 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 March 01. 2010 Bug 9382657
61 Base Tax Amount is not calculated correctly.
62 Calculated by multiplying the line amount with effective rate of the precedences
63 base_tax_amt_tab(I) := ln_exclusive_price*base_tax_amt_tab(I) + base_tax_amount_nr_tab(I)
64
65 31-Mar-2010 Bug 9327049
66 Issue - Could not create receipt for RMA for some transactions.
67 Cause - Problematic code in procedure calculate_ato_taxes. The
68 pl sql array tax_rate_zero_tab is not not populated for RMA,
69 but is being used for comparison in tax calculation. This raises
70 a No Data Found error.
71 Fix - Added logic to populate the tax_rate_zero_tab array in case of
72 RMA also.
73
74 07-May-2010 Bug 9674771
75 Issue - Interface trip stop errors out for partial shipment.
76 Cause - Similar to that of bug 9327049. Here, the base_tax_amount_nr_tab table is not initialized
77 for zero rate taxes.
78 Fix - Added code to initialize the pl sql table for zero rate tax lines also.
79
80 20 Feb2012 mmurtuza for bug13473763
81 Description: WRONG TAX CALCULATION ON SALES ORDER WHEN TAXES APPLIED THROUGH ITEM CATEGORY.
82 Fix: Modified the procedure calculate_ato_taxes to perform calculation of inclusive and exclsuive taxes.
83 (Ported from ja_in_calc_prec_taxes procedure)
84
85 28-Aug-2012 mmurtuza for bug 14031594
86 Description: TAXES CALCULATION RESULT IS WRONG FOR IL RMA WHEN THERE IS ADHOC TAX INVOLVED.
87 Fix: Added code in calculate_ato_taxes procedure to calcualte tax for RMA when tax type is adhoc.
88
89 ------------------------------------------------------------------------------------------*/
90
91 PROCEDURE calculate_ato_taxes
92 (
93 transaction_name VARCHAR2,
94 P_tax_category_id NUMBER,
95 p_header_id NUMBER,
96 p_line_id NUMBER,
97 p_assessable_value NUMBER default 0,
98 p_tax_amount IN OUT NOCOPY NUMBER,
99 p_currency_conv_factor NUMBER,
100 p_inventory_item_id NUMBER,
101 p_line_quantity NUMBER,
102 p_quantity NUMBER,
103 p_uom_code VARCHAR2,
104 p_vendor_id NUMBER,
105 p_currency VARCHAR2,
106 p_creation_date DATE,
107 p_created_by NUMBER,
108 p_last_update_date DATE,
109 p_last_updated_by NUMBER,
110 p_last_update_login NUMBER,
111 p_vat_assessable_Value NUMBER DEFAULT 0,
112 p_vat_reversal_price NUMBER DEFAULT 0 /*Bug#6072461, bduvarag*/
113 --,pn_gst_assessable_Value NUMBER DEFAULT 0 -- Added by zhiwei for bug10043656 GST enhancement 2010/09/14 /*Commented by mmurtuza for removing GST Changes*/
114 ) IS
115 TYPE num_tab IS TABLE OF number
116 INDEX BY BINARY_INTEGER;
117 TYPE tax_amt_num_tab IS TABLE OF number
118 INDEX BY BINARY_INTEGER;
119
120 -- Add by Kevin Cheng for inclusive tax Dec 10, 2007
121 ----------------------------------------------------
122 TYPE CHAR_TAB IS TABLE OF VARCHAR2(10)
123 INDEX BY BINARY_INTEGER;
124
125 lt_adhoc_tax_tab CHAR_TAB;
126 lt_inclu_tax_tab CHAR_TAB;
127 lt_tax_rate_tab NUM_TAB;
128 lt_tax_rate_per_rupee NUM_TAB;
129 lt_cumul_tax_rate_per_rupee NUM_TAB;
130 lt_tax_target_tab NUM_TAB;
131 lt_tax_amt_rate_tax_tab TAX_AMT_NUM_TAB;
132 lt_tax_amt_non_rate_tab TAX_AMT_NUM_TAB;
133 lt_func_tax_amt_tab TAX_AMT_NUM_TAB;
134 lv_uom_code VARCHAR2(10) := 'EA';
135 lv_register_code VARCHAR2(20);
136 ln_inventory_item_id NUMBER;
137 ln_exclusive_price NUMBER;
138 ln_total_non_rate_tax NUMBER := 0;
139 ln_total_inclusive_factor NUMBER;
140 ln_bsln_amt_nr NUMBER :=0;
141 ln_tax_amt_nr NUMBER(38,10) := 0;
142 ln_func_tax_amt NUMBER(38,10) := 0;
143 ln_vamt_nr NUMBER(38,10) := 0;
144 ln_excise_jb NUMBER;
145 ln_total_tax_per_rupee NUMBER;
146 ln_assessable_value NUMBER;
147 ln_vat_assessable_value NUMBER;
148 ln_vat_reversal_price NUMBER;
149 -- ln_gst_assessable_value NUMBER; -- Added by zhiwei for GST 2010/09/14 /*Commented by mmurtuza for removing GST Changes*/
150 ----------------------------------------------------
151 p1 num_tab;
152 p2 num_tab;
153 p3 num_tab;
154 p4 num_tab;
155 p5 num_tab;
156
157 -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
158 -- START BUG 5228046
159
160 p6 num_tab;
161 p7 num_tab;
162 p8 num_tab;
163 p9 num_tab;
164 p10 num_tab;
165
166 -- END BUG 5228046
167 tax_rate_tab num_tab;
168 /*
169 || Aiyer for bug#4691616. Added tax_rate_zero_tab table
170 -------------------------------------------------------------
171 tax_rate(i) tax_rate_tab(i) tax_rate_zero_tab(i)
172 -------------------------------------------------------------
173 NULL 0 0
174 0 0 -9999
175 n (non-zero and not null) n n
176 -------------------------------------------------------------
177 */
178 tax_rate_zero_tab num_tab;
179
180 tax_type_tab num_tab;
181 qty_rate_tab NUM_TAB; /*By mmurtuza for Bug 14358278*/
182 tax_amt_tab tax_amt_num_tab;
183 base_tax_amt_tab tax_amt_num_tab;
184 base_tax_amount_nr_tab tax_amt_num_tab; /*Bug 9382657*/
185 end_date_tab num_tab;
186 rounding_factor_tab num_tab;
187 bsln_amt number; -- := p_tax_amount; --Ramananda for File.Sql.35
188 v_conversion_rate number := 0;
189 v_currency_conv_factor number; -- := p_currency_conv_factor; --Ramananda for File.Sql.35
190 v_tax_amt number := 0;
191 vamt number :=0;
192 v_amt number;
193 row_count number := 0;
194 counter number;
195 max_iter number := 10;
196
197 v_rma_ctr Number; -- variable added by sriram - bug # 2740443
198 ln_amount NUMBER; --mmurtuza for bug 13473763
199
200 --Comment out by Kevin Cheng for inclusive tax Dec 10, 2007
201 /*CURSOR tax_cur(p_header_id NUMBER, p_line_id NUMBER) IS
202 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,
203 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,
204 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 )
205 c.tax_amount, d.tax_type, d.end_date valid_date, d.rounding_factor,
206
207 DECODE(rgm_tax_types.regime_Code,jai_constants.vat_regime, 4, \* added by ssumaith - bug# 4245053*\
208 decode(upper(d.tax_type),
209 'EXCISE', 1,
210 'ADDL. EXCISE', 1,
211 'OTHER EXCISE', 1,
212 'CVD', 1,
213 'TDS', 2,
214 'EXCISE_EDUCATION_CESS', 1,
215 'CVD_EDUCATION_CESS', 1,
216 'SH_EXCISE_EDUCATION_CESS' ,1,--Added by kundan kumar for bug#5907436
217 'SH_CVD_EDUCATION_CESS' , 1, --Added by kundan kumar for bug#5907436
218 'VAT REVERSAL', 5,\*Bug#6072461, bduvarag*\
219 0
220 )
221 )
222 tax_type_val,
223 d.adhoc_flag
224 FROM JAI_OM_OE_SO_TAXES c ,
225 JAI_CMN_TAXES_ALL d ,
226 jai_regime_tax_types_v rgm_tax_types \* added by ssumaith - bug# 4245053*\
227 WHERE c.line_id = p_line_id
228 AND c.header_id = p_header_id
229 AND c.tax_id = d.tax_id
230 AND rgm_tax_types.tax_type (+) = d.tax_type \* added by ssumaith - bug# 4245053*\
231 ORDER BY c.tax_line_no;*/
232
233 --Add by Kevin Cheng for inclusive tax Dec 10, 2007
234 CURSOR tax_cur( p_header_id NUMBER
235 , p_line_id NUMBER
236 )
237 IS
238 SELECT
239 c.tax_line_no lno
240 , c.tax_category_id
241 , c.tax_id
242 , c.tax_rate
243 , c.qty_rate
244 , c.uom uom_code
245 , c.func_tax_amount
246 , c.base_tax_amount
247 , c.precedence_1 p_1
248 , c.precedence_2 p_2
249 , c.precedence_3 p_3
250 , c.precedence_4 p_4
251 , c.precedence_5 p_5
252 , c.precedence_6 p_6
253 , c.precedence_7 p_7
254 , c.precedence_8 p_8
255 , c.precedence_9 p_9
256 , c.precedence_10 p_10 -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
257 , c.tax_amount
258 , d.tax_type
259 , d.end_date valid_date
260 , nvl(d.rounding_factor,0) rounding_factor
261 , DECODE( rgm_tax_types.regime_Code
262 , jai_constants.vat_regime ,4 /* added by ssumaith - bug# 4245053*/
263 --, jai_constants.cgst_regime ,7 --Added by zhiwei for bug10043656 GST enhancement 2010/09/14 /*Commented by mmurtuza for removing GST Changes*/
264 --, jai_constants.sgst_regime ,7 --Added by zhiwei for bug10043656 GST enhancement 2010/09/14 /*Commented by mmurtuza for removing GST Changes*/
265 , decode( upper(d.tax_type)
266 , 'EXCISE' ,1
267 , 'ADDL. EXCISE' ,1
268 , 'OTHER EXCISE' ,1
269 , jai_constants.tax_type_cvd ,1
270 , jai_constants.tax_type_tds ,2
271 , jai_constants.tax_type_exc_edu_cess ,6
272 , jai_constants.tax_type_cvd_edu_cess ,6
273 , jai_constants.tax_type_sh_exc_edu_cess ,6
274 , jai_constants.tax_type_sh_cvd_edu_cess ,6
275 , 'VAT REVERSAL' ,5/*Bug#6072461, bduvarag*/
276 , 0
277 )
278 ) tax_type_val
279 , d.adhoc_flag
280 , d.vendor_id
281 , d.mod_cr_percentage
282 , d.inclusive_tax_flag
283 FROM
284 Jai_Om_Oe_So_Taxes c
285 , Jai_Cmn_Taxes_All d
286 , Jai_Regime_Tax_Types_V rgm_tax_types /* added by ssumaith - bug# 4245053*/
287 WHERE c.line_id = p_line_id
288 AND c.header_id = p_header_id
289 AND c.tax_id = d.tax_id
290 AND rgm_tax_types.tax_type(+) = d.tax_type /* added by ssumaith - bug# 4245053*/
291 ORDER BY
292 c.tax_line_no;
293
294 -- following cursor added by sriram - bug # 2740443
295
296 CURSOR c_rma_info (v_header_id Number , v_line_id Number) is
297 Select 1
298 from JAI_OM_OE_RMA_LINES
299 where rma_header_id = v_header_id
300 and rma_line_id = v_line_id;
301
302 --Comment out by Kevin Cheng for inclusive tax Dec 10, 2007
303 /*CURSOR c_rma_tax_cur(p_line_id NUMBER) IS
304 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,
305 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,
306 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 )
307 c.tax_amount, d.tax_type, d.end_date valid_date, d.rounding_factor,
308 DECODE(rgm_tax_types.regime_Code,jai_constants.vat_regime, 4, \* added by ssumaith - bug# 4245053*\
309 decode(upper(d.tax_type),'EXCISE', 1, 'ADDL. EXCISE', 1, 'OTHER EXCISE', 1, 'CVD',1, 'TDS', 2, 0
310 )
311 ) tax_type_val,
312 d.adhoc_flag
313 FROM JAI_OM_OE_RMA_TAXES c,
314 JAI_CMN_TAXES_ALL d ,
315 jai_regime_tax_types_v rgm_tax_types \* added by ssumaith - bug# 4245053*\
316 WHERE c.rma_line_id = p_line_id
317 AND c.tax_id = d.tax_id
318 AND rgm_tax_types.tax_type(+) = d.tax_type \* added by ssumaith - bug# 4245053*\
319 ORDER BY c.tax_line_no;*/
320
321 -- Add by Kevin Cheng for inclusive tax Dec 10, 2007
322 CURSOR c_rma_tax_cur( p_line_id NUMBER
323 )
324 IS
325 SELECT
326 c.tax_line_no lno
327 , c.tax_id
328 , c.tax_rate
329 , c.qty_rate
330 , c.uom uom_code
331 , c.func_tax_amount
332 , c.base_tax_amount
333 , c.precedence_1 p_1
334 , c.precedence_2 p_2
335 , c.precedence_3 p_3
336 , c.precedence_4 p_4
337 , c.precedence_5 p_5
338 , c.precedence_6 p_6
339 , c.precedence_7 p_7
340 , c.precedence_8 p_8
341 , c.precedence_9 p_9
342 , c.precedence_10 p_10 -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
343 , c.tax_amount
344 , d.tax_type
345 , d.end_date valid_date
346 , nvl(d.rounding_factor,0) rounding_factor
347 , DECODE( rgm_tax_types.regime_Code
348 , jai_constants.vat_regime, 4 /* added by ssumaith - bug# 4245053*/
349 --, jai_constants.cgst_regime ,7 --Added by zhiwei for bug10043656 GST enhancement 2010/09/14 /*Commented by mmurtuza for removing GST Changes*/
350 --, jai_constants.sgst_regime ,7 --Added by zhiwei for bug10043656 GST enhancement 2010/09/14 /*Commented by mmurtuza for removing GST Changes*/
351 , decode( upper(d.tax_type)
352 , 'EXCISE' ,1
353 , 'ADDL. EXCISE' ,1
354 , 'OTHER EXCISE' ,1
355 , jai_constants.tax_type_cvd ,1
356 , jai_constants.tax_type_tds ,2
357 , jai_constants.tax_type_exc_edu_cess ,6
358 , jai_constants.tax_type_cvd_edu_cess ,6
359 , jai_constants.tax_type_sh_exc_edu_cess ,6
360 , jai_constants.tax_type_sh_cvd_edu_cess ,6
361 , 'VAT REVERSAL' ,5/*Bug#6072461, bduvarag*/
362 , 0
363 )
364 ) tax_type_val
365 , d.adhoc_flag
366 , d.vendor_id
367 , d.mod_cr_percentage
368 , d.inclusive_tax_flag
369 FROM
370 Jai_Om_Oe_Rma_Taxes c
371 , Jai_Cmn_Taxes_All d
372 , Jai_Regime_Tax_Types_V rgm_tax_types /* added by ssumaith - bug# 4245053*/
373 WHERE c.rma_line_id = p_line_id
374 AND c.tax_id = d.tax_id
375 AND rgm_tax_types.tax_type(+) = d.tax_type /* added by ssumaith - bug# 4245053*/
376 ORDER BY
377 c.tax_line_no;
378
379 -- Start of bug 3590208
380 /*****************
381 Code modified by aiyer for the bug 3590208
382 Check whether the excise exemptions exist at the order line level.
383 *****************/
384 CURSOR c_excise_exemption
385 IS
386 SELECT
387 '1'
388 FROM
389 JAI_OM_OE_SO_LINES jsl
390 WHERE
391 jsl.excise_exempt_type IS NOT NULL AND
392 jsl.line_id = p_line_id ;
393
394 lv_excise_exemption_exists VARCHAR2(1);
395
396 -- End of bug 3590208
397
398 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_om_tax_pkg.calculate_ato_taxes';
399
400 /* Start Additions by mmurtuza for bug 14031594*/
401 CURSOR get_adhoc_flag(pn_tax_id NUMBER) IS
402 SELECT adhoc_flag
403 FROM jai_cmn_taxes_all
404 WHERE tax_id = pn_tax_id;
405
406 lv_adhoc_flag VARCHAR2(1);
407 /* End Additions by mmurtuza for bug 14031594*/
408
409 /*Start additions by mmurtuza for bug 14559561*/
410
411 CURSOR c_get_vat_exempt_flag(cp_line_id number) IS
412 select vat_exemption_flag from jai_om_oe_so_lines
413 where line_id = cp_line_id;
414 lv_vat_exemption_flag char(1) := 'N';
415
416 cursor c_get_base_line_id(cp_line_id oe_order_lines_all.line_id%type)
417 is
418 select wdd.source_line_id from wsh_delivery_details wdd, jai_om_oe_rma_lines joorl
419 where wdd.delivery_detail_id = joorl.delivery_detail_id
420 and joorl.rma_line_id = cp_line_id;
421 lv_reference_Line_id oe_order_lines_all.Reference_Line_id%type;
422
423 cursor c_excise_exemption_rma(cp_line_id number) IS
424 select excise_exempt_type from jai_om_oe_so_lines
425 where excise_exempt_type IS NOT NULL AND
426 line_id = cp_line_id;
427
428 /*End additions by mmurtuza for bug 14559561*/
429
430 BEGIN
431 /*------------------------------------------------------------------------------------------
432 FILENAME: calculate_ato_taxes_P.sql
433
434
435 CHANGE HISTORY:
436 S.No Date Author and Details
437 1. 2001/11/09 Anuradha Parthasarathy
438 Parameter p_quantity added To handle Adhoc Types of Taxes.
439 2. 2002/01/03 Anuradha Parthasarathy
440 parameter p_quantity used in place of p_line_quantity for correct calculation
441 of taxes for which the tax_rate is null.
442 3. 2003/01/18 Sriram Bug # 2740443 File Version 615.1
443 When RMA was done without reference and mofifiers attached
444 to them, taxes were not getting recalculated.
445 Hence wrote code to get the same done by adding 2 cursors and checking
446 if the current header id and line id combination refers to a sales order
447 or rma order . if it corresponds to an rma order , update the
448 JAI_OM_OE_RMA_TAXES table .
449
450 4. 26-Feb-2004 Aiyer For the Bug #3590208 File Version 619.1
451 Issue:-
452 Excise Duty Recalculation happens for excise exempted tax lines if the order line quantity
453 is changed.
454 Fix:-
455 Code has been added to set the tax_rate, tax_amount and base_tax_amount to 0 when the Order Line has Excise
456 exemptions and tax is of "Excise" type. The cursor c_excise_exemption has been added for the purpose.
457 Dependency Due to this code :-
458 None
459
460 5. 09-Aug-2004 Aiyer - Bug #3802074 File Version 115.2
461 Issue:-
462 Uom based taxes do not get calculated correctly if the qty or price is changed.
463
464 Reason:-
465 --------
466 Previous to this fix the uom currency uom conversion would happen only when the transaction uom code and the tax level uom code where
467 in the same uom class. This was not required.
468 This was happening because the UOM calculation was previously happening only for cases of exact match
469 between transaction uom and setup UOM.
470
471 Fix:-
472 ----
473 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.
474 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.
475
476 Dependency Due to This Bug:-
477 ----------------------------
478 The refresh in the India Localization sales order form was not happening properly leading to taxes getting set incorrectly
479 when the tax apply button is clicked (in JAINEORD) post updation of qty in Base apps form. To resolve this the locator logic
480 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
481 (ja_in_om_locator_aiu_trg). Some fix was also done in JAINEORD.fmb for the same.
482 Hence all these objects should be displatched along with this object, due to functional dependency
483 As this fix is also being taken in the current Due to this the tax was not getting bug
484
485 6. 2005/03/10 ssumaith - bug# 4245053 - File version 115.3
486
487 Taxes under the vat regime needs to be calculated based on the vat assessable value setup done.
488 In the vendor additional information screen and supplier additional information screen, a place
489 has been given to capture the vat assessable value.
490
491 This needs to be used for the calculation of the taxes registered under vat regime.
492
493 This change has been done by using the view jai_regime_tax_types_v - and outer joining it with the
494 JAI_CMN_TAXES_ALL table based on the tax type column.
495
496 A parameter p_vat_assessable_Value NUMBER DEFAULT 0 has been added to the procedure.
497
498 Dependency due to this bug - Huge
499 This patch should always be accompanied by the VAT consolidated patch - 4245089
500
501 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
502 as required for CASE COMPLAINCE.
503
504 8. 13-Jun-2005 File Version: 116.4
505 Ramananda for bug#4428980. Removal of SQL LITERALs is done
506
507 9. 01-Jun-2006 Aiyer for bug# 4691616. File Version 120.3
508 Issue:-
509 UOM based taxes do not get calculated correctly.
510
511 Solution:-
512 Fwd ported the fix for the bug 4729742.
513 Changed the files JAINTAX1.pld, jai_cmn_tax_dflt.plb and jai_om_tax.plb.
514
515 10. 10-Dec-007 Kevin Cheng Update the logic for inclusive tax calculation
516
517 11. 05-Mar-08 Jia Li Added clause logic for bug# 6846048
518 Issue: When unit selling price is changed because of discounts,
519 inclusive taxes are getting added to the tax amount in the JAI_OM_OE_SO_LINES table.
520 So at ship confirm time, comparing the tax amount in the lines table with the sum of exclusive taxes
521 in the taxes table was not matching and hence the trigger was returning an error.
522 Fix: Ensuring that only exclusive taxes are added to the tax amount in the jai_om_oe_so_lines table.
523
524 12 22-sep-2010 vkaranam for bug#10114559
525 Issue:
526 TAXES ARE NOT CALCULATED AUTOMATICALLY ON A SPLIT ORDER
527 Reason:
528 Cess and shecess taxe are caluclated as 0.
529 This is due to the base_tax_amt before the tax claculation was 0 due to the rounding function.
530 Fix: -- removed round function for base_tax_amount calculation in calculate_ato_taxes procedure.
531
532 13. 12-Jun-2012 mmurtuza for bug 14140327
533 Issue: EXCISE DUTY IS WRONGLY CALCULATING FOR ISO ORDERS when line is splitted
534 Description: Added condition while calcualting ln_vamt_nr. If ln_bsln_amt_nr = 0, only then p_tax_amount is added
535
536 14. 31-Jul-2012 mmurtuza for bug 14358278
537 Issue: RATE PER QUANTITY TAX AMT POPULATED AS ZERO IN SALES ORDER LOCALIZED SCREEN.
538 Fix: Added a block in calculate_ato_taxes to check if tax_amount for that particular tax is defined or not and assigned the value of
539 tax_amount * qunatity to tax_amt_tab(i)
540
541 15. 27-Sep-2012 mmurtuza for bug 14559561
542 Issue: RMA COPIED ORDER IS INCORRECTLY CALCULATING TAXES
543 Fix: Added cursors c_get_vat_exempt_flag, c_get_base_line_id, c_excise_exemption_rma to check if the base ordre of RMA is excise / vat
544 exempted. Also added code to ensure that vat taxes remain zero for vat exempted sales order even if sales order is updated
545
546 16. 28-feb-2013 mmurtuza for bug 16389308
547 Issue: TAXES ARE NOT GETTING APPLIED PROPERLY WHEN THE SALE ORDER LINE IS SPLITTED
548 Fix: Moved the code to calcualte adhoc taxes in the bgining of procedure arther than calcaulting at the end.
549
550 Future Dependencies For the release Of this Object:-
551 (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/
552 A datamodel change )
553
554 ----------------------------------------------------------------------------------------------------------------------------------------------------
555 Current Version Current Bug Dependent Files Version Author Date Remarks
556 Of File On Bug/Patchset Dependent On
557 calculate_ato_taxes_p.sql
558 ----------------------------------------------------------------------------------------------------------------------------------------------------
559 115.2 3802074 IN60105D2 ja_in_om_locator_aiu_trg.sql 115.1 Aiyer 26-oct-2004 Functional Dependency
560 jai_cmn_utils_pkg.ja_in_set_locator 115.0
561 ja_in_oe_order_lines_aiu_trg 115.5
562 ja_in_oe_order_lines_aiu_trg 115.5
563 JAINEORD.fmb 115.3
564 calculate_ato_taxes_p.sql 115.2 Aiyer
565
566
567 115.3 4245053 IN60106 + ssumaith Service Tax and VAT Infrastructure are created
568 4146708 + based on the bugs - 4146708 and 4545089 respectively.
569 4245089
570
571
572 *************************************************************************************************************************************************************/
573
574 bsln_amt := p_tax_amount; --Ramananda for File.Sql.35
575 v_currency_conv_factor := p_currency_conv_factor; --Ramananda for File.Sql.35
576
577 Open c_rma_info(p_header_id,p_line_id);
578 Fetch c_rma_info into v_rma_ctr;
579 close c_rma_info;
580
581 -- Start of bug 3590208
582 /*****************
583 Code modified by aiyer for the bug 3590208
584 *****************/
585 OPEN c_excise_exemption;
586 FETCH c_excise_exemption INTO lv_excise_exemption_exists;
587 -- End of bug 3590208
588
589 IF NVL(v_rma_ctr,0) = 0 then -- added by sriram - bug # 2740443 abc
590
591 FOR rec in tax_cur(p_header_id, p_line_id)
592 LOOP
593 p1(rec.lno) := nvl(rec.p_1,-1);
594 p2(rec.lno) := nvl(rec.p_2,-1);
595 p3(rec.lno) := nvl(rec.p_3,-1);
596 p4(rec.lno) := nvl(rec.p_4,-1);
597 p5(rec.lno) := nvl(rec.p_5,-1);
598
599 -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
600 -- START BUG 5228046
601
602 p6(rec.lno) := nvl(rec.p_6,-1);
603 p7(rec.lno) := nvl(rec.p_7,-1);
604 p8(rec.lno) := nvl(rec.p_8,-1);/*Added by kunkumar,for bugno5604375,deleted second assignment of p7 */
605 p9(rec.lno) := nvl(rec.p_9,-1);
606 p10(rec.lno) := nvl(rec.p_10,-1);
607
608 -- END BUG 5228046
609
610 lt_adhoc_tax_tab(rec.lno) := nvl(rec.adhoc_flag, 'N'); -- by mmurtuza for bug 16389308
611
612 tax_rate_tab(rec.lno) := nvl(rec.tax_rate,0);
613
614 /*
615 || The following code added by aiyer for the bug 4691616
616 || Purpose:
617 || 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)
618 || 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
619 || To calculate the BASE_TAX_AMOUNT of the taxes whose tax_rate is zero
620 */
621
622 IF rec.tax_rate is null THEN
623 /*
624 ||Indicates qty based taxes
625 */
626 tax_rate_zero_tab(rec.lno) := 0; /*row_count Changed to rec.lno by ssawant for bug 6134057*/
627
628 ELSIF rec.tax_rate = 0 THEN
629 /*
630 ||Indicates 0% tax rate becasue a tax can have a rate as 0%.
631 */
632 tax_rate_zero_tab(rec.lno) := -9999; /*row_count Changed to rec.lno by ssawant for bug 6134057*/
633
634 ELSE
635 tax_rate_zero_tab(rec.lno) := rec.tax_rate; /*row_count Changed to rec.lno by ssawant for bug 6134057*/
636
637 END IF;
638
639 tax_type_tab(rec.lno) := rec.tax_type_val; /*row_count Changed to rec.lno by ssawant for bug 6134057*/
640 /*End of bug 4691616 */
641
642 tax_type_tab(rec.lno) := rec.tax_type_val;
643 qty_rate_tab(rec.lno) := rec.qty_rate; /*By mmurtuza for Bug 14358278*/
644 tax_amt_tab(rec.lno) := 0;
645 base_tax_amt_tab(rec.lno) := 0;
646 rounding_factor_tab(rec.lno) := NVL(rec.rounding_factor, 0);
647
648 -- Add by Kevin Cheng for inclusive tax Dec 10, 2007
649 ----------------------------------------------------
650 lt_tax_rate_per_rupee(rec.lno) := NVL(rec.tax_rate,0)/100;
651 ln_total_tax_per_rupee := 0;
652 lt_inclu_tax_tab(rec.lno) := NVL(rec.inclusive_tax_flag,'N');
653 lt_tax_amt_rate_tax_tab(rec.lno) := 0;
654 lt_tax_amt_non_rate_tab(rec.lno) := 0; -- tax inclusive
655 ----------------------------------------------------
656
657 IF tax_rate_tab(rec.lno) = 0 THEN
658 -- Start of bug 3802074
659 /*
660 Code added by aiyer for the bug 3802074.
661 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
662 are in the same uom class. This was not required.
663 Now the code check whether an exact match exists between the transaction uom and the setup uom.
664 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
665 determined and tax amounts, base_tax_amounts are calculated for defaultation.
666 */
667
668 inv_convert.inv_um_conversion( p_uom_code, rec.uom_code, p_inventory_item_id, v_conversion_rate);
669 IF nvl(v_conversion_rate, 0) <= 0 THEN
670 inv_convert.inv_um_conversion( p_uom_code, rec.uom_code, 0, v_conversion_rate);
671 IF nvl(v_conversion_rate, 0) <= 0 THEN
672 v_conversion_rate := 0;
673 END IF;
674 END IF;
675 --Comment out by Kevin Cheng for inclusive tax Dec 10, 2007
676 /*tax_amt_tab(rec.lno) := ROUND(nvl(rec.qty_rate * v_conversion_rate, 0) * p_quantity, NVL(rec.rounding_factor, 0));*/
677 -- End of bug 3802074
678
679 --Add by Kevin Cheng for inclusive tax Dec 10, 2007
680 ---------------------------------------------------
681 lt_tax_amt_non_rate_tab(rec.lno) := nvl(rec.qty_rate * v_conversion_rate, 0) * p_quantity; -- tax inclusive
682 base_tax_amt_tab(rec.lno) := lt_tax_amt_non_rate_tab(rec.lno); -- tax inclusive
683 ---------------------------------------------------
684
685 IF rec.adhoc_flag = 'Y' THEN
686 --Comment out by Kevin Cheng for inclusive tax Dec 10, 2007
687 /*tax_amt_tab(rec.lno) := ROUND((rec.tax_amount * p_quantity/p_line_quantity), NVL(rec.rounding_factor, 0));*/
688 --Add by Kevin Cheng for inclusive tax Dec 10, 2007
689 -- csahoo - bug#4918667 added the if and the else so that we dont encounter a zero-divide error
690 IF NVL(p_line_quantity,0) <> 0 THEN
691 lt_tax_amt_non_rate_tab(rec.lno) := ROUND((rec.tax_amount * p_quantity/p_line_quantity), NVL(rec.rounding_factor, 0));
692 ELSE
693 lt_tax_amt_non_rate_tab(rec.lno) := 0;
694 END IF;
695 END IF;
696 END IF;
697
698 IF rec.valid_date is NULL or rec.valid_date >= sysdate
699 THEN
700 end_date_tab(rec.lno) := 1;
701 ELSE
702 tax_amt_tab(rec.lno) := 0;
703 end_date_tab(rec.lno) := 0;
704 END IF;
705
706 -- Start of bug 3590208
707 /*****************
708 Code modified by aiyer for the bug 3590208
709 IF the line is excise exempted and the tax is of type Excise then set the tax_rate, tax_amount and base_tax_amount
710 to zero.
711 *****************/
712 IF c_excise_exemption%FOUND AND
713 rec.tax_type_val = 1
714 THEN
715 /* Set tax_rate_tab = 0, tax_amt_tab = 0 and base_tax_amt_tab = 0 */
716 tax_rate_tab(rec.lno) := 0;
717 tax_amt_tab(rec.lno) := 0;
718 base_tax_amt_tab(rec.lno) := 0;
719 lt_tax_amt_non_rate_tab(rec.lno) :=0; -- Add by Kevin Cheng for inclusive tax Dec 10, 2007
720
721 /*Start additions by mmurtuza for bug 14559561*/
722 ELSIF rec.tax_type_val = 4 then
723 open c_get_vat_exempt_flag(p_line_id);
724 fetch c_get_vat_exempt_flag into lv_vat_exemption_flag;
725 close c_get_vat_exempt_flag;
726 IF nvl(lv_vat_exemption_flag, 'N') = 'Y' then
727 tax_rate_tab(rec.lno) := 0;
728 tax_amt_tab(rec.lno) := 0;
729 base_tax_amt_tab(rec.lno) := 0;
730 lt_tax_amt_non_rate_tab(rec.lno) :=0;
731 end if;
732 /*End additions by mmurtuza for bug 14559561*/
733 END IF;
734 -- End of bug 3590208
735
736
737 row_count := row_count + 1;
738 END LOOP;
739
740 ELSIF v_rma_ctr = 1 then -- else part of abc
741 -- v_rma_ctr value will be 1 if the current header id line id combination corresponds to a return order.
742 -- added by sriram the section from the following elsif to end if- bug # 2740443
743
744 FOR rec in c_rma_tax_cur(p_line_id) LOOP
745 p1(rec.lno) := nvl(rec.p_1,-1);
746 p2(rec.lno) := nvl(rec.p_2,-1);
747 p3(rec.lno) := nvl(rec.p_3,-1);
748 p4(rec.lno) := nvl(rec.p_4,-1);
749 p5(rec.lno) := nvl(rec.p_5,-1);
750
751 -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
752 -- START BUG 5228046
753
754 p6(rec.lno) := nvl(rec.p_6,-1);
755 p7(rec.lno) := nvl(rec.p_7,-1);
756 p8(rec.lno) := nvl(rec.p_8,-1);
757 p9(rec.lno) := nvl(rec.p_9,-1);
758 p10(rec.lno) := nvl(rec.p_10,-1);
759
760 -- END BUG 5228046
761
762 lt_adhoc_tax_tab(rec.lno) := nvl(rec.adhoc_flag, 'N'); -- by mmurtuza for bug 16389308
763
764
765 tax_rate_tab(rec.lno) := nvl(rec.tax_rate,0);
766 tax_type_tab(rec.lno) := rec.tax_type_val;
767 tax_amt_tab(rec.lno) := 0;
768 base_tax_amt_tab(rec.lno) := 0;
769 rounding_factor_tab(rec.lno) := NVL(rec.rounding_factor, 0);
770 qty_rate_tab(rec.lno) := rec.qty_rate; /*By mmurtuza for bug 14031594*/
771
772 /*bug 9327049*/
773 IF rec.tax_rate is null THEN
774 /*
775 ||Indicates qty based taxes
776 */
777 tax_rate_zero_tab(rec.lno) := 0; /*row_count Changed to rec.lno by ssawant for bug 6134057*/
778
779 ELSIF rec.tax_rate = 0 THEN
780 /*
781 ||Indicates 0% tax rate becasue a tax can have a rate as 0%.
782 */
783 tax_rate_zero_tab(rec.lno) := -9999; /*row_count Changed to rec.lno by ssawant for bug 6134057*/
784
785 ELSE
786 tax_rate_zero_tab(rec.lno) := rec.tax_rate; /*row_count Changed to rec.lno by ssawant for bug 6134057*/
787
788 END IF;
789 /*end bug 9327049*/
790
791 -- Add by Kevin Cheng for inclusive tax Dec 10, 2007
792 ----------------------------------------------------
793 lt_tax_rate_per_rupee(rec.lno) := NVL(rec.tax_rate,0)/100;
794 ln_total_tax_per_rupee := 0;
795 lt_inclu_tax_tab(rec.lno) := NVL(rec.inclusive_tax_flag,'N');
796
797 IF rec.tax_rate IS NULL
798 THEN
799 tax_rate_zero_tab(rec.lno) := 0;
800 ELSIF rec.tax_rate = 0
801 THEN --IF rec.tax_rate IS NULL
802 tax_rate_zero_tab(rec.lno) := -9999;
803 ELSE --IF rec.tax_rate IS NULL
804 tax_rate_zero_tab(rec.lno) := rec.tax_rate;
805 END IF; --IF rec.tax_rate IS NULL
806
807 lt_tax_amt_rate_tax_tab(rec.lno) :=0;
808 lt_tax_amt_non_rate_tab(rec.lno) :=0; -- tax inclusive
809 ----------------------------------------------------
810
811 IF tax_rate_tab(rec.lno) = 0 THEN
812 -- Start of bug 3802074
813 /*
814 Code added by aiyer for the bug 3802074
815 Check whether an exact match exists between the transaction uom and the setup uom (obtained through the tax_category list).
816 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
817 determined and tax amounts,base_tax_amounts are calculated for defaultation.
818 */
819
820 inv_convert.inv_um_conversion( p_uom_code, rec.uom_code, p_inventory_item_id, v_conversion_rate);
821 IF nvl(v_conversion_rate, 0) <= 0 THEN
822 INV_CONVERT.inv_um_conversion( p_uom_code, rec.uom_code, 0, v_conversion_rate);
823 IF nvl(v_conversion_rate, 0) <= 0 THEN
824 v_conversion_rate := 0;
825 END IF;
826 END IF;
827 --Comment out by Kevin Cheng for inclusive tax Dec 10, 2007
828 /*tax_amt_tab(rec.lno) := ROUND(nvl(rec.qty_rate * v_conversion_rate, 0) * p_quantity, NVL(rec.rounding_factor, 0));*/
829 -- End of bug 3802074
830
831 -- Add by Kevin Cheng for inclusive tax Dec 10, 2007
832 ----------------------------------------------------
833 lt_tax_amt_non_rate_tab(rec.lno) := nvl(rec.qty_rate * v_conversion_rate, 0) * p_quantity; -- tax inclusive
834 base_tax_amt_tab(rec.lno) := lt_tax_amt_non_rate_tab(rec.lno); -- tax inclusive
835 ----------------------------------------------------
836
837 IF rec.adhoc_flag = 'Y' THEN
838 --Comment out by Kevin Cheng for inclusive tax Dec 10, 2007
839 /*tax_amt_tab(rec.lno) := ROUND((rec.tax_amount * p_quantity/p_line_quantity), NVL(rec.rounding_factor, 0));*/
840 --Add by Kevin Cheng for inclusive tax Dec 10, 2007
841 -- csahoo - bug#4918667 added the if and the else so that we dont encounter a zero-divide error
842 IF NVL(p_line_quantity,0) <> 0 THEN
843 lt_tax_amt_non_rate_tab(rec.lno) := ROUND((rec.tax_amount * p_quantity/p_line_quantity), NVL(rec.rounding_factor, 0));
844 ELSE
845 lt_tax_amt_non_rate_tab(rec.lno) := 0;
846 END IF;
847 END IF;
848 END IF;
849
850 IF rec.valid_date is NULL or rec.valid_date >= sysdate THEN
851 end_date_tab(rec.lno) := 1;
852 ELSE
853 tax_amt_tab(rec.lno) := 0;
854 end_date_tab(rec.lno) := 0;
855 END IF;
856 -- Start of bug 3590208
857 /*****************
858 Code modified by aiyer for the bug 3590208
859 IF the line is excise exempted and the tax is of type Excise then set the tax_rate, tax_amount and base_tax_amount
860 to zero.
861 *****************/
862
863 /*Start commenting by mmurtuza for bug 14559561
864
865 IF c_excise_exemption%FOUND AND
866 rec.tax_type_val = 1
867 THEN
868 /* Set tax_rate_tab = 0, tax_amt_tab = 0 and base_tax_amt_tab = 0 *
869 tax_rate_tab(rec.lno) := 0;
870 tax_amt_tab(rec.lno) := 0;
871 base_tax_amt_tab(rec.lno) := 0;
872 lt_tax_amt_non_rate_tab(rec.lno) :=0; -- Add by Kevin Cheng for inclusive tax Dec 10, 2007
873 END IF;
874 -- End of bug 3590208
875
876 /*End commenting by mmurtuza for bug 14559561*/
877
878 /*Start additions by mmurtuza for bug 14559561*/
879
880 open c_get_base_line_id(p_line_id);
881 fetch c_get_base_line_id into lv_reference_Line_id;
882 close c_get_base_line_id;
883
884 IF rec.tax_type_val = 1 then
885 open c_excise_exemption_rma(lv_reference_Line_id);
886 IF c_excise_exemption_rma%FOUND then
887 tax_rate_tab(rec.lno) := 0;
888 tax_amt_tab(rec.lno) := 0;
889 base_tax_amt_tab(rec.lno) := 0;
890 lt_tax_amt_non_rate_tab(rec.lno) :=0;
891 END IF;
892 close c_excise_exemption_rma;
893 ELSIF rec.tax_type_val = 4 then
894 open c_get_vat_exempt_flag(lv_reference_Line_id);
895 fetch c_get_vat_exempt_flag into lv_vat_exemption_flag;
896 close c_get_vat_exempt_flag;
897 IF nvl(lv_vat_exemption_flag, 'N') = 'Y' then
898 tax_rate_tab(rec.lno) := 0;
899 tax_amt_tab(rec.lno) := 0;
900 base_tax_amt_tab(rec.lno) := 0;
901 lt_tax_amt_non_rate_tab(rec.lno) :=0;
902 end if;
903 END IF;
904 /*End additions by mmurtuza for bug 14559561*/
905
906 row_count := row_count + 1;
907 END LOOP;
908
909 END IF; -- added by sriram - bug # 2740443. End if of abc
910 -- Start of bug 3590208
911 CLOSE c_excise_exemption ;
912 -- End of bug 3590208
913
914 --Add by Kevin Cheng for inclusive tax Dec 10, 2007
915 ---------------------------------------------------
916 IF p_vat_assessable_value<>p_tax_amount
917 THEN
918 ln_vat_assessable_value:=p_vat_assessable_value;
919 ELSE
920 ln_vat_assessable_value:=1;
921 END IF;
922
923 --Added by zhiwei for bug10043656 GST enhancement 2010/09/14 begin
924 ---------------------------------------------------
925 /*Start Commenting by mmurtuza for removing GST Changes*/
926 /*IF pn_gst_assessable_value<>p_tax_amount
927 THEN
928 ln_gst_assessable_value:=pn_gst_assessable_value;
929 ELSE
930 ln_gst_assessable_value:=1;
931 END IF;*/
932 /*End Commenting by mmurtuza for removing GST Changes*/
933 ---------------------------------------------------
934 --Added by zhiwei for bug10043656 GST enhancement 2010/09/14 end
935
936 IF p_assessable_value<>p_tax_amount
937 THEN
938 ln_assessable_value:=p_assessable_value;
939 ELSE
940 ln_assessable_value:=1;
941 END IF;
942
943 IF p_vat_reversal_price<>p_tax_amount
944 THEN
945 ln_vat_reversal_price:=p_vat_reversal_price;
946 ELSE
947 ln_vat_reversal_price:=1;
948 END IF;
949 ---------------------------------------------------
950 --Comment out by Kevin Cheng for inclusive tax Dec 10, 2007
951 /*FOR I in 1..row_count LOOP
952 IF tax_type_tab(I) = 1
953 THEN
954 bsln_amt := p_assessable_value;
955 ELSIF tax_type_tab(I) = 4 THEN
956 bsln_amt := p_vat_assessable_value;
957 ELSIF tax_type_tab(I) = 7 THEN --Added by zhiwei for bug10043656 GST enhancement 2010/09/14
958 bsln_amt := pn_gst_assessable_value; --Added by zhiwei for bug10043656 GST enhancement 2010/09/14
959 ELSIF tax_type_tab(I) = 5 THEN \*bug#6072461, bduvarag*\
960 bsln_amt := p_vat_reversal_price;
961
962 ELSE
963 bsln_amt := p_tax_amount;
964 END IF;
965 IF tax_rate_tab(I) <> 0
966 THEN
967 IF p1(I) < I and p1(I) not in (-1,0)
968 THEN
969 vamt := vamt + nvl(tax_amt_tab(p1(I)),0);
970 ELSIF p1(I) = 0 THEN
971 vamt := vamt + bsln_amt;
972 END IF;
973 IF p2(I) < I and p2(I) not in (-1,0)
974 THEN
975 vamt := vamt + nvl(tax_amt_tab(p2(I)),0);
976 ELSIF p2(I) = 0 THEN
977 vamt := vamt + bsln_amt;
978 END IF;
979 IF p3(I) < I and p3(I) not in (-1,0)
980 THEN
981 vamt := vamt + nvl(tax_amt_tab(p3(I)),0);
982 ELSIF p3(I) = 0 THEN
983 vamt := vamt + bsln_amt;
984 END IF;
985
986
987 IF p4(I) < I and p4(I) not in (-1,0)
988 THEN
989 vamt := vamt + nvl(tax_amt_tab(p4(I)),0);
990 ELSIF p4(I) = 0 THEN
991 vamt := vamt + bsln_amt;
992 END IF;
993 IF p5(I) < I and p5(I) not in (-1,0)
994 THEN
995 vamt := vamt + nvl(tax_amt_tab(p5(I)),0);
996 ELSIF p5(I) = 0 THEN
997 vamt := vamt + bsln_amt;
998 END IF;
999
1000 -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
1001 -- START BUG 5228046
1002
1003 IF p6(I) < I and p6(I) not in (-1,0)
1004 THEN
1005 vamt := vamt + nvl(tax_amt_tab(p6(I)),0);
1006 ELSIF p6(I) = 0 THEN
1007 vamt := vamt + bsln_amt;
1008 END IF;
1009 IF p7(I) < I and p7(I) not in (-1,0)
1010 THEN
1011 vamt := vamt + nvl(tax_amt_tab(p7(I)),0);
1012 ELSIF p7(I) = 0 THEN
1013 vamt := vamt + bsln_amt;
1014 END IF;
1015
1016 IF p8(I) < I and p8(I) not in (-1,0)
1017 THEN
1018 vamt := vamt + nvl(tax_amt_tab(p8(I)),0);
1019 ELSIF p8(I) = 0 THEN
1020 vamt := vamt + bsln_amt;
1021 END IF;
1022 IF p9(I) < I and p9(I) not in (-1,0)
1023 THEN
1024 vamt := vamt + nvl(tax_amt_tab(p9(I)),0);
1025 ELSIF p9(I) = 0 THEN
1026 vamt := vamt + bsln_amt;
1027 END IF;
1028
1029 IF p10(I) < I and p10(I) not in (-1,0)
1030 THEN
1031 vamt := vamt + nvl(tax_amt_tab(p10(I)),0);
1032 ELSIF p10(I) = 0 THEN
1033 vamt := vamt + bsln_amt;
1034 END IF;
1035
1036 -- END BUG 5228046
1037
1038
1039
1040
1041
1042
1043 v_tax_amt := v_tax_amt + (vamt * (tax_rate_tab(I)/100));
1044 base_tax_amt_tab(I) := vamt;
1045 tax_amt_tab(I) := nvl(tax_amt_tab(I),0) + v_tax_amt;
1046 vamt := 0;
1047 v_tax_amt := 0;
1048
1049 END IF;
1050 END LOOP;*/
1051 --Add by Kevin Cheng for inclusive tax Dec 10, 2007
1052 ----------------------------------------------------
1053 FOR I in 1..row_count LOOP
1054 IF end_date_tab(I) <> 0 THEN
1055 IF tax_type_tab(I) = 1
1056 THEN
1057 IF ln_assessable_value = 1 THEN
1058 bsln_amt := 1;
1059 ln_bsln_amt_nr := 0;
1060 ELSE
1061 bsln_amt := 0;
1062 ln_bsln_amt_nr := ln_assessable_value;
1063 END IF;
1064 ELSIF tax_type_tab(I) = 4 --IF tax_type_tab(I) = 1 THEN
1065 THEN
1066 IF ln_vat_assessable_value = 1 THEN
1067 bsln_amt := 1;
1068 ln_bsln_amt_nr := 0;
1069 ELSE
1070 bsln_amt := 0;
1071 ln_bsln_amt_nr := ln_vat_assessable_value;
1072 END IF;
1073
1074 --Added by zhiwei for bug10043656 GST enhancement 2010/09/14 begin
1075 ---------------------------------------------------
1076 /*Start Commenting by mmurtuza for removing GST Changes*/
1077 /*ELSIF tax_type_tab( I ) = 7 THEN
1078 IF ln_gst_assessable_value =1 THEN
1079 bsln_amt:=1;
1080 ln_bsln_amt_nr :=0;
1081 ELSE
1082 bsln_amt :=0;
1083 ln_bsln_amt_nr :=ln_gst_assessable_value;
1084 END IF;*/
1085 /*End Commenting by mmurtuza for removing GST Changes*/
1086 ---------------------------------------------------
1087 --Added by zhiwei for bug10043656 GST enhancement 2010/09/14 end
1088
1089
1090 ELSIF tax_type_tab(I) = 5 --IF tax_type_tab(I) = 1 THEN
1091 THEN /*bug#6072461, bduvarag*/
1092 IF ln_vat_reversal_price = 1 THEN
1093 bsln_amt := 1;
1094 ln_bsln_amt_nr := 0;
1095 ELSE
1096 bsln_amt := 0;
1097 ln_bsln_amt_nr := ln_vat_reversal_price;
1098 END IF;
1099 ELSIF tax_type_tab(I) = 6
1100 THEN --IF tax_type_tab(I) = 1 THEN
1101 bsln_amt := 0;
1102 ln_bsln_amt_nr := 0;
1103 ELSE --IF tax_type_tab(I) = 1 THEN
1104 bsln_amt := 1;
1105 ln_bsln_amt_nr := 0;
1106 END IF;
1107
1108 IF tax_rate_tab(I) <> 0
1109 THEN
1110 lt_tax_amt_non_rate_tab(I):=0; --mmurtuza for bug 13473763
1111 IF p1(I) < I and p1(I) not in (-1,0)
1112 THEN
1113 IF lt_inclu_tax_tab(P1(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P1(I)) IS NOT NULL then --mmurtuza for bug 13473763
1114 vamt := vamt + nvl(tax_amt_tab(p1(I)),0);
1115 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P1(I)),0);
1116 end if; --mmurtuza for bug 13473763
1117 ELSIF p1(I) = 0 THEN
1118 IF lt_inclu_tax_tab(I) = 'Y' then -- 13473763
1119 vamt := vamt + bsln_amt;
1120 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
1121 else
1122 if (ln_bsln_amt_nr = 0) then /*14140327*/
1123 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount; -- 13473763
1124 else
1125 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; /*14140327*/
1126 end if;
1127 END IF;
1128 END IF;
1129
1130 IF p2(I) < I and p2(I) not in (-1,0)
1131 THEN
1132 IF lt_inclu_tax_tab(P2(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P2(I)) IS NOT NULL then --mmurtuza for bug 13473763
1133 vamt := vamt + nvl(tax_amt_tab(p2(I)),0);
1134 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P2(I)),0);
1135 end if; --mmurtuza for bug 13473763
1136 ELSIF p2(I) = 0 THEN
1137 IF lt_inclu_tax_tab(I) = 'Y' then -- 13473763
1138 vamt := vamt + bsln_amt;
1139 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
1140 else
1141 if (ln_bsln_amt_nr = 0) then /*14140327*/
1142 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount; -- 13473763
1143 else
1144 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; /*14140327*/
1145 end if;
1146 END IF;
1147 END IF;
1148
1149 IF p3(I) < I and p3(I) not in (-1,0)
1150 THEN
1151 IF lt_inclu_tax_tab(P3(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P3(I)) IS NOT NULL then --mmurtuza for bug 13473763
1152 vamt := vamt + nvl(tax_amt_tab(p3(I)),0);
1153 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P3(I)),0);
1154 end if; --mmurtuza for bug 13473763
1155 ELSIF p3(I) = 0 THEN
1156 IF lt_inclu_tax_tab(I) = 'Y' then -- 13473763
1157 vamt := vamt + bsln_amt;
1158 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
1159 else
1160 if (ln_bsln_amt_nr = 0) then /*14140327*/
1161 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount; -- 13473763
1162 else
1163 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; /*14140327*/
1164 end if;
1165 END IF;
1166 END IF;
1167
1168
1169 IF p4(I) < I and p4(I) not in (-1,0)
1170 THEN
1171 IF lt_inclu_tax_tab(P4(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P4(I)) IS NOT NULL then --mmurtuza for bug 13473763
1172 vamt := vamt + nvl(tax_amt_tab(p4(I)),0);
1173 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P4(I)),0);
1174 end if; --mmurtuza for bug 13473763
1175 ELSIF p4(I) = 0 THEN
1176 IF lt_inclu_tax_tab(I) = 'Y' then -- 13473763
1177 vamt := vamt + bsln_amt;
1178 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
1179 else
1180 if (ln_bsln_amt_nr = 0) then /*14140327*/
1181 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount; -- 13473763
1182 else
1183 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; /*14140327*/
1184 end if;
1185 END IF;
1186 END IF;
1187
1188 IF p5(I) < I and p5(I) not in (-1,0)
1189 THEN
1190 IF lt_inclu_tax_tab(P5(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P5(I)) IS NOT NULL then --mmurtuza for bug 13473763
1191 vamt := vamt + nvl(tax_amt_tab(p5(I)),0);
1192 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P5(I)),0);
1193 end if; --mmurtuza for bug 13473763
1194 ELSIF p5(I) = 0 THEN
1195 IF lt_inclu_tax_tab(I) = 'Y' then -- 13473763
1196 vamt := vamt + bsln_amt;
1197 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
1198 else
1199 if (ln_bsln_amt_nr = 0) then /*14140327*/
1200 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount; -- 13473763
1201 else
1202 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; /*14140327*/
1203 end if;
1204 END IF;
1205 END IF;
1206
1207 -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
1208 -- START BUG 5228046
1209
1210 IF p6(I) < I and p6(I) not in (-1,0)
1211 THEN
1212 IF lt_inclu_tax_tab(P6(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P6(I)) IS NOT NULL then --mmurtuza for bug 13473763
1213 vamt := vamt + nvl(tax_amt_tab(p6(I)),0);
1214 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P6(I)),0);
1215 end if; --mmurtuza for bug 13473763
1216 ELSIF p6(I) = 0 THEN
1217 IF lt_inclu_tax_tab(I) = 'Y' then -- 13473763
1218 vamt := vamt + bsln_amt;
1219 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
1220 else
1221 if (ln_bsln_amt_nr = 0) then /*14140327*/
1222 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount; -- 13473763
1223 else
1224 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; /*14140327*/
1225 end if;
1226 END IF;
1227 END IF;
1228
1229 IF p7(I) < I and p7(I) not in (-1,0)
1230 THEN
1231 IF lt_inclu_tax_tab(P7(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P7(I)) IS NOT NULL then --mmurtuza for bug 13473763
1232 vamt := vamt + nvl(tax_amt_tab(p7(I)),0);
1233 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P7(I)),0);
1234 end if; --mmurtuza for bug 13473763
1235 ELSIF p7(I) = 0 THEN
1236 IF lt_inclu_tax_tab(I) = 'Y' then -- 13473763
1237 vamt := vamt + bsln_amt;
1238 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
1239 else
1240 if (ln_bsln_amt_nr = 0) then /*14140327*/
1241 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount; -- 13473763
1242 else
1243 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; /*14140327*/
1244 end if;
1245 END IF;
1246 END IF;
1247
1248 IF p8(I) < I and p8(I) not in (-1,0)
1249 THEN
1250 IF lt_inclu_tax_tab(P8(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P8(I)) IS NOT NULL then --mmurtuza for bug 13473763
1251 vamt := vamt + nvl(tax_amt_tab(p8(I)),0);
1252 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P8(I)),0);
1253 end if; --mmurtuza for bug 13473763
1254 ELSIF p8(I) = 0 THEN
1255 IF lt_inclu_tax_tab(I) = 'Y' then -- 13473763
1256 vamt := vamt + bsln_amt;
1257 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
1258 else
1259 if (ln_bsln_amt_nr = 0) then /*14140327*/
1260 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount; -- 13473763
1261 else
1262 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; /*14140327*/
1263 end if;
1264 END IF;
1265 END IF;
1266
1267 IF p9(I) < I and p9(I) not in (-1,0)
1268 THEN
1269 IF lt_inclu_tax_tab(P9(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P9(I)) IS NOT NULL then --mmurtuza for bug 13473763
1270 vamt := vamt + nvl(tax_amt_tab(p9(I)),0);
1271 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P9(I)),0);
1272 end if; --mmurtuza for bug 13473763
1273 ELSIF p9(I) = 0 THEN
1274 IF lt_inclu_tax_tab(I) = 'Y' then -- 13473763
1275 vamt := vamt + bsln_amt;
1276 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
1277 else
1278 if (ln_bsln_amt_nr = 0) then /*14140327*/
1279 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount; -- 13473763
1280 else
1281 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; /*14140327*/
1282 end if;
1283 END IF;
1284 END IF;
1285
1286 IF p10(I) < I and p10(I) not in (-1,0)
1287 THEN
1288 IF lt_inclu_tax_tab(P10(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P10(I)) IS NOT NULL then --mmurtuza for bug 13473763
1289 vamt := vamt + nvl(tax_amt_tab(p10(I)),0);
1290 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P10(I)),0);
1291 end if; --mmurtuza for bug 13473763
1292 ELSIF p10(I) = 0 THEN
1293 IF lt_inclu_tax_tab(I) = 'Y' then -- 13473763
1294 vamt := vamt + bsln_amt;
1295 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
1296 else
1297 if (ln_bsln_amt_nr = 0) then /*14140327*/
1298 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount; -- 13473763
1299 else
1300 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; /*14140327*/
1301 end if;
1302 END IF;
1303 END IF;
1304
1305 -- END BUG 5228046
1306
1307 v_tax_amt := v_tax_amt + (vamt * (tax_rate_tab(I)/100));
1308 ln_tax_amt_nr := ln_tax_amt_nr + (ln_vamt_nr * (tax_rate_tab(I)/100));
1309 base_tax_amt_tab(I) := vamt;
1310 base_tax_amount_nr_tab(I):=ln_vamt_nr; /*Bug 9382657*/
1311 tax_amt_tab(I) := nvl(tax_amt_tab(I),0) + v_tax_amt;
1312 lt_tax_amt_non_rate_tab(I) := NVL(lt_tax_amt_non_rate_tab(I),0) + ln_tax_amt_nr; -- tax inclusive
1313 -- lt_tax_amt_rate_tax_tab(i) := tax_amt_tab(I); --mmurtuza for bug 13473763
1314
1315 IF lt_inclu_tax_tab(I) = 'Y' then
1316 lt_tax_amt_rate_tax_tab(I) := tax_amt_tab(I); --mmurtuza for bug 13473763
1317 END IF;
1318
1319 vamt := 0;
1320 v_tax_amt := 0;
1321 ln_tax_amt_nr := 0;
1322 ln_vamt_nr := 0;
1323 ELSE /*bug 9674771 - populate base_tax_amount_nr_tab for zero rate taxes also*/
1324 base_tax_amount_nr_tab(I) := 0;
1325 END IF; --IF tax_rate_tab(I) <> 0
1326 ELSE --IF end_date_tab(I) <> 0 THEN
1327 tax_amt_tab(I) := 0;
1328 base_tax_amt_tab(I) := 0;
1329 base_tax_amount_nr_tab(I):=0; /*Bug 9382657*/
1330 END IF; --IF end_date_tab(I) <> 0 THEN
1331 END LOOP;
1332 ----------------------------------------------------
1333 --Comment out by Kevin Cheng for inclusive tax Dec 10, 2007
1334 /*FOR I in 1..row_count LOOP
1335 IF tax_rate_tab(I) <> 0 THEN
1336 IF p1(I) > I THEN
1337 vamt := vamt + nvl(tax_amt_tab(p1(I)),0);
1338 END IF;
1339 IF p2(I) > I THEN
1340 vamt := vamt + nvl(tax_amt_tab(p2(I)),0);
1341 END IF;
1342 IF p3(I) > I THEN
1343 vamt := vamt + nvl(tax_amt_tab(p3(I)),0);
1344 END IF;
1345 IF p4(I) > I THEN
1346 vamt := vamt + nvl(tax_amt_tab(p4(I)),0);
1347 END IF;
1348 IF p5(I) > I THEN
1349 vamt := vamt + nvl(tax_amt_tab(p5(I)),0);
1350 END IF;
1351
1352 -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
1353 -- START BUG 5228046
1354 IF p6(I) > I THEN
1355 vamt := vamt + nvl(tax_amt_tab(p6(I)),0);
1356 END IF;
1357 IF p7(I) > I THEN
1358 vamt := vamt + nvl(tax_amt_tab(p7(I)),0);
1359 END IF;
1360 IF p8(I) > I THEN
1361 vamt := vamt + nvl(tax_amt_tab(p8(I)),0);
1362 END IF;
1363 IF p9(I) > I THEN
1364 vamt := vamt + nvl(tax_amt_tab(p9(I)),0);
1365 END IF;
1366 IF p10(I) > I THEN
1367 vamt := vamt + nvl(tax_amt_tab(p10(I)),0);
1368 END IF;
1369
1370 -- END BUG 5228046
1371
1372
1373 v_tax_amt := v_tax_amt + (vamt * (tax_rate_tab(I)/100));
1374 IF vamt <> 0 THEN
1375 base_tax_amt_tab(I) := base_tax_amt_tab(I) + vamt;
1376 END IF;
1377 tax_amt_tab(I) := nvl(tax_amt_tab(I),0) + v_tax_amt;
1378
1379 vamt := 0;
1380 v_tax_amt := 0;
1381
1382 END IF;
1383 END LOOP;*/
1384 --Add by Kevin Cheng for inclusive tax Dec 10, 2007
1385 ---------------------------------------------------
1386 FOR I in 1..row_count LOOP
1387 IF end_date_tab( I ) <> 0
1388 THEN
1389 IF tax_rate_tab(I) <> 0 THEN
1390 IF p1(I) > I THEN
1391 vamt := vamt + nvl(tax_amt_tab(p1(I)),0);
1392 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(p1(I)),0); -- tax inclusive
1393 END IF;
1394 IF p2(I) > I THEN
1395 vamt := vamt + nvl(tax_amt_tab(p2(I)),0);
1396 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(p2(I)),0); -- tax inclusive
1397 END IF;
1398 IF p3(I) > I THEN
1399 vamt := vamt + nvl(tax_amt_tab(p3(I)),0);
1400 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(p3(I)),0); -- tax inclusive
1401 END IF;
1402 IF p4(I) > I THEN
1403 vamt := vamt + nvl(tax_amt_tab(p4(I)),0);
1404 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(p4(I)),0); -- tax inclusive
1405 END IF;
1406 IF p5(I) > I THEN
1407 vamt := vamt + nvl(tax_amt_tab(p5(I)),0);
1408 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(p5(I)),0); -- tax inclusive
1409 END IF;
1410
1411 -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
1412 -- START BUG 5228046
1413 IF p6(I) > I THEN
1414 vamt := vamt + nvl(tax_amt_tab(p6(I)),0);
1415 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(p6(I)),0); -- tax inclusive
1416 END IF;
1417 IF p7(I) > I THEN
1418 vamt := vamt + nvl(tax_amt_tab(p7(I)),0);
1419 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(p7(I)),0); -- tax inclusive
1420 END IF;
1421 IF p8(I) > I THEN
1422 vamt := vamt + nvl(tax_amt_tab(p8(I)),0);
1423 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(p8(I)),0); -- tax inclusive
1424 END IF;
1425 IF p9(I) > I THEN
1426 vamt := vamt + nvl(tax_amt_tab(p9(I)),0);
1427 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(p9(I)),0); -- tax inclusive
1428 END IF;
1429 IF p10(I) > I THEN
1430 vamt := vamt + nvl(tax_amt_tab(p10(I)),0);
1431 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(p10(I)),0); -- tax inclusive
1432 END IF;
1433
1434 -- END BUG 5228046
1435 base_tax_amt_tab(I) := vamt;
1436 base_tax_amount_nr_tab(I):=ln_vamt_nr; /*9382657*/
1437 v_tax_amt := v_tax_amt + (vamt * (tax_rate_tab(I)/100));
1438 ln_tax_amt_nr := ln_tax_amt_nr + (ln_vamt_nr * (tax_rate_tab(I)/100)); -- tax inclusive
1439 IF vamt <> 0 THEN
1440 base_tax_amt_tab(I) := base_tax_amt_tab(I) + vamt;
1441 END IF;
1442 tax_amt_tab(I) := nvl(tax_amt_tab(I),0) + v_tax_amt;
1443 lt_tax_amt_non_rate_tab(I) := NVL(lt_tax_amt_non_rate_tab(I),0) + ln_tax_amt_nr ; -- tax inclusive
1444 lt_tax_amt_rate_tax_tab(i) := tax_amt_tab(I);
1445
1446 vamt := 0;
1447 v_tax_amt := 0;
1448 ln_vamt_nr := 0 ;
1449 ln_tax_amt_nr := 0 ;
1450 END IF;
1451 ELSE --IF end_date_tab( I ) <> 0 THEN
1452 base_tax_amt_tab(I) := vamt;
1453 base_tax_amount_nr_tab(I):=ln_vamt_nr; /*9382657*/
1454 tax_amt_tab(I) := 0;
1455 END IF; --IF end_date_tab( I ) <> 0 THEN
1456 END LOOP;
1457 ---------------------------------------------------
1458
1459 FOR counter IN 1 .. max_iter LOOP
1460 vamt := 0;
1461 v_tax_amt := 0;
1462 ln_vamt_nr := 0; -- Add by Kevin Cheng for inclusive tax Dec 10, 2007
1463 ln_tax_amt_nr:=0; -- Add by Kevin Cheng for inclusive tax Dec 10, 2007
1464
1465 FOR i IN 1 .. row_count LOOP
1466
1467 /*Commented by aiyer for the bug 4691616
1468 IF tax_rate_tab( i ) <> 0 THEN
1469 */
1470
1471 /*
1472 || start of bug bug#4691616
1473 || IF statement modified by Aiyer for bug#4691616
1474 */
1475
1476 IF ( tax_rate_tab( i ) <> 0 OR tax_rate_zero_tab(I) = -9999 ) AND
1477 end_date_tab( I ) <> 0
1478 THEN
1479 /*
1480 || End of bug 4691616
1481 */
1482 IF tax_type_tab( I ) = 1 THEN
1483 --Comment out by Kevin Cheng for inclusive tax Dec 10, 2007
1484 /*v_amt := p_assessable_value;*/
1485 --Added by Kevin Cheng for inclusive tax Dec 10, 2007
1486 ------------------------------------------------
1487 IF ln_assessable_value =1
1488 THEN
1489 v_amt:=1;
1490 ln_bsln_amt_nr :=0;
1491 ELSE
1492 v_amt :=0;
1493 ln_bsln_amt_nr :=ln_assessable_value;
1494 END IF;
1495 ------------------------------------------------
1496 ELSIF tax_type_tab( I ) = 4 THEN
1497 --Comment out by Kevin Cheng for inclusive tax Dec 10, 2007
1498 /*v_amt := p_vat_assessable_value;*/
1499 --Added by Kevin Cheng for inclusive tax Dec 10, 2007
1500 ------------------------------------------------
1501 IF ln_vat_assessable_value =1
1502 THEN
1503 v_amt:=1;
1504 ln_bsln_amt_nr :=0;
1505 ELSE
1506 v_amt :=0;
1507 ln_bsln_amt_nr :=ln_vat_assessable_value;
1508 END IF;
1509
1510 --Added by zhiwei for bug10043656 GST enhancement 2010/09/14 begin
1511 ---------------------------------------------------
1512 /*Start Commenting by mmurtuza for removing GST Changes*/
1513 /*ELSIF tax_type_tab( I ) = 7 THEN
1514 IF ln_gst_assessable_value =1
1515 THEN
1516 v_amt:=1;
1517 ln_bsln_amt_nr :=0;
1518 ELSE
1519 v_amt :=0;
1520 ln_bsln_amt_nr :=ln_gst_assessable_value;
1521 END IF;*/
1522 /*End Commenting by mmurtuza for removing GST Changes*/
1523 ---------------------------------------------------
1524 --Added by zhiwei for bug10043656 GST enhancement 2010/09/14 end
1525
1526
1527
1528 ------------------------------------------------
1529 ELSIF tax_type_tab( I ) = 5 THEN /*Bug#6072461, bduvarag*/
1530 --Comment out by Kevin Cheng for inclusive tax Dec 10, 2007
1531 /*v_amt := p_vat_reversal_price;*/
1532 --Added by Kevin Cheng for inclusive tax Dec 10, 2007
1533 -------------------------------------
1534 IF ln_vat_reversal_price =1
1535 THEN
1536 v_amt:=1;
1537 ln_bsln_amt_nr :=0;
1538 ELSE
1539 v_amt :=0;
1540 ln_bsln_amt_nr :=ln_vat_reversal_price;
1541 END IF;
1542 ELSIF tax_type_tab(I) = 6 THEN
1543 v_amt:=0;
1544 ln_bsln_amt_nr :=0;
1545 -------------------------------------
1546 ELSE
1547 IF p_assessable_value IN ( 0, -1 ) OR tax_type_tab( I ) <> 1 THEN
1548 /*v_amt := p_tax_amount;*/--Comment out by Kevin Cheng for inclusive tax Dec 10, 2007
1549 v_amt:=1; --Added by Kevin Cheng for inclusive tax Dec 10, 2007
1550 ln_bsln_amt_nr :=0; --Added by Kevin Cheng for inclusive tax Dec 10, 2007
1551 ELSIF p_vat_assessable_value IN ( 0, -1 ) OR tax_type_tab( I ) <> 4 THEN
1552 /*v_amt := p_tax_amount;*/--Comment out by Kevin Cheng for inclusive tax Dec 10, 2007
1553 v_amt:=1; --Added by Kevin Cheng for inclusive tax Dec 10, 2007
1554 ln_bsln_amt_nr :=0; --Added by Kevin Cheng for inclusive tax Dec 10, 2007
1555 ELSIF p_vat_reversal_price IN ( 0, -1 ) OR tax_type_tab( I ) <> 5 THEN /*Bug#6072461, bduvarag*/
1556 /*v_amt := p_tax_amount; */--Comment out by Kevin Cheng for inclusive tax Dec 10, 2007
1557 v_amt:=1; --Added by Kevin Cheng for inclusive tax Dec 10, 2007
1558 ln_bsln_amt_nr :=0; --Added by Kevin Cheng for inclusive tax Dec 10, 2007
1559
1560 --Added by zhiwei for bug10043656 GST enhancement 2010/09/14 begin
1561 ---------------------------------------------------
1562 /*Start Commenting by mmurtuza for removing GST Changes*/
1563 /*ELSIF pn_gst_assessable_value IN ( 0, -1 ) OR tax_type_tab( I ) <> 7 THEN
1564 v_amt:=1; --Added by Kevin Cheng for inclusive tax Dec 10, 2007
1565 ln_bsln_amt_nr :=0; */ --Added by Kevin Cheng for inclusive tax Dec 10, 2007
1566 /*End Commenting by mmurtuza for removing GST Changes*/
1567 ---------------------------------------------------
1568 --Added by zhiwei for bug10043656 GST enhancement 2010/09/14 end
1569
1570 END IF;
1571 END IF;
1572
1573 IF p1( i ) <> -1 THEN
1574 IF p1( i ) <> 0 THEN
1575 IF lt_inclu_tax_tab(P1(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P1(I)) IS NOT NULL then -- bug 13473763
1576 vamt := vamt + tax_amt_tab( p1( I ) );
1577 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
1578 end if; --bug 13473763
1579 ELSIF p1(i) = 0 THEN
1580 IF lt_inclu_tax_tab(I) = 'Y' then -- bug 13473763
1581 vamt := vamt + v_amt;
1582 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by Kevin Cheng for inclusive tax Dec 10, 2007
1583 else
1584 if (ln_bsln_amt_nr = 0) then /*14140327*/
1585 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount; -- 13473763
1586 else
1587 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; /*14140327*/
1588 end if;
1589 END IF;
1590 END IF;
1591 END IF;
1592
1593 IF p2( i ) <> -1 THEN
1594 IF p2( i ) <> 0 THEN
1595 IF lt_inclu_tax_tab(P2(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P2(I)) IS NOT NULL then -- bug 13473763
1596 vamt := vamt + tax_amt_tab( p2( I ) );
1597 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
1598 end if; --bug 13473763
1599 ELSIF p2(i) = 0 THEN
1600 IF lt_inclu_tax_tab(I) = 'Y' then -- bug 13473763
1601 vamt := vamt + v_amt;
1602 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by Kevin Cheng for inclusive tax Dec 10, 2007
1603 else
1604 if (ln_bsln_amt_nr = 0) then /*14140327*/
1605 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount; -- 13473763
1606 else
1607 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; /*14140327*/
1608 end if;
1609 END IF;
1610 END IF;
1611 END IF;
1612
1613 IF p3( i ) <> -1 THEN
1614 IF p3( i ) <> 0 THEN
1615 IF lt_inclu_tax_tab(P3(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P3(I)) IS NOT NULL then -- bug 13473763
1616 vamt := vamt + tax_amt_tab( p3( I ) );
1617 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
1618 end if; --bug 13473763
1619 ELSIF p3(i) = 0 THEN
1620 IF lt_inclu_tax_tab(I) = 'Y' then -- bug 13473763
1621 vamt := vamt + v_amt;
1622 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by Kevin Cheng for inclusive tax Dec 10, 2007
1623 else
1624 if (ln_bsln_amt_nr = 0) then /*14140327*/
1625 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount; -- 13473763
1626 else
1627 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; /*14140327*/
1628 end if;
1629 END IF;
1630 END IF;
1631 END IF;
1632
1633 IF p4( i ) <> -1 THEN
1634 IF p4( i ) <> 0 THEN
1635 IF lt_inclu_tax_tab(P4(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P4(I)) IS NOT NULL then -- bug 13473763
1636 vamt := vamt + tax_amt_tab( p4( i ) );
1637 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
1638 end if; --bug 13473763
1639 ELSIF p4(i) = 0 THEN
1640 IF lt_inclu_tax_tab(I) = 'Y' then -- bug 13473763
1641 vamt := vamt + v_amt;
1642 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by Kevin Cheng for inclusive tax Dec 10, 2007
1643 else
1644 if (ln_bsln_amt_nr = 0) then /*14140327*/
1645 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount; -- 13473763
1646 else
1647 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; /*14140327*/
1648 end if;
1649 END IF;
1650 END IF;
1651 END IF;
1652
1653 IF p5( i ) <> -1 THEN
1654 IF p5( i ) <> 0 THEN
1655 IF lt_inclu_tax_tab(P5(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P5(I)) IS NOT NULL then -- bug 13473763
1656 vamt := vamt + tax_amt_tab( p5( i ) );
1657 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
1658 end if; --bug 13473763
1659 ELSIF p5(i) = 0 THEN
1660 IF lt_inclu_tax_tab(I) = 'Y' then -- bug 13473763
1661 vamt := vamt + v_amt;
1662 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by Kevin Cheng for inclusive tax Dec 10, 2007
1663 else
1664 if (ln_bsln_amt_nr = 0) then /*14140327*/
1665 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount; -- 13473763
1666 else
1667 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; /*14140327*/
1668 end if;
1669 END IF;
1670 END IF;
1671 END IF;
1672
1673 -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
1674 -- START BUG 5228046
1675
1676 IF p6( i ) <> -1 THEN
1677 IF p6( i ) <> 0 THEN
1678 IF lt_inclu_tax_tab(P6(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P6(I)) IS NOT NULL then -- bug 13473763
1679 vamt := vamt + tax_amt_tab( p6( I ) );
1680 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
1681 end if; --bug 13473763
1682 ELSIF p6(i) = 0 THEN
1683 IF lt_inclu_tax_tab(I) = 'Y' then -- bug 13473763
1684 vamt := vamt + v_amt;
1685 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by Kevin Cheng for inclusive tax Dec 10, 2007
1686 else
1687 if (ln_bsln_amt_nr = 0) then /*14140327*/
1688 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount; -- 13473763
1689 else
1690 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; /*14140327*/
1691 end if;
1692 END IF;
1693 END IF;
1694 END IF;
1695
1696 IF p7( i ) <> -1 THEN
1697 IF p7( i ) <> 0 THEN
1698 IF lt_inclu_tax_tab(P7(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P7(I)) IS NOT NULL then -- bug 13473763
1699 vamt := vamt + tax_amt_tab( p7( I ) );
1700 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
1701 end if; --bug 13473763
1702 ELSIF p7(i) = 0 THEN
1703 IF lt_inclu_tax_tab(I) = 'Y' then -- bug 13473763
1704 vamt := vamt + v_amt;
1705 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by Kevin Cheng for inclusive tax Dec 10, 2007
1706 else
1707 if (ln_bsln_amt_nr = 0) then /*14140327*/
1708 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount; -- 13473763
1709 else
1710 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; /*14140327*/
1711 end if;
1712 END IF;
1713 END IF;
1714 END IF;
1715
1716 IF p8( i ) <> -1 THEN
1717 IF p8( i ) <> 0 THEN
1718 IF lt_inclu_tax_tab(P8(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P8(I)) IS NOT NULL then -- bug 13473763
1719 vamt := vamt + tax_amt_tab( p8( I ) );
1720 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
1721 end if; --bug 13473763
1722 ELSIF p8(i) = 0 THEN
1723 IF lt_inclu_tax_tab(I) = 'Y' then -- bug 13473763
1724 vamt := vamt + v_amt;
1725 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by Kevin Cheng for inclusive tax Dec 10, 2007
1726 else
1727 if (ln_bsln_amt_nr = 0) then /*14140327*/
1728 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount; -- 13473763
1729 else
1730 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; /*14140327*/
1731 end if;
1732 END IF;
1733 END IF;
1734 END IF;
1735
1736 IF p9( i ) <> -1 THEN
1737 IF p9( i ) <> 0 THEN
1738 IF lt_inclu_tax_tab(P9(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P9(I)) IS NOT NULL then -- bug 13473763
1739 vamt := vamt + tax_amt_tab( p9( i ) );
1740 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
1741 end if; --bug 13473763
1742 ELSIF p9(i) = 0 THEN
1743 IF lt_inclu_tax_tab(I) = 'Y' then -- bug 13473763
1744 vamt := vamt + v_amt;
1745 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by Kevin Cheng for inclusive tax Dec 10, 2007
1746 else
1747 if (ln_bsln_amt_nr = 0) then /*14140327*/
1748 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount; -- 13473763
1749 else
1750 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; /*14140327*/
1751 end if;
1752 END IF;
1753 END IF;
1754 END IF;
1755
1756 IF p10( i ) <> -1 THEN
1757 IF p10( i ) <> 0 THEN
1758 IF lt_inclu_tax_tab(P10(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P10(I)) IS NOT NULL then -- bug 13473763
1759 vamt := vamt + tax_amt_tab( p10( i ) );
1760 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
1761 end if; --bug 13473763
1762 ELSIF p10(i) = 0 THEN
1763 IF lt_inclu_tax_tab(I) = 'Y' then -- bug 13473763
1764 vamt := vamt + v_amt;
1765 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by Kevin Cheng for inclusive tax Dec 10, 2007
1766 else
1767 if (ln_bsln_amt_nr = 0) then /*14140327*/
1768 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount; -- 13473763
1769 else
1770 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; /*14140327*/
1771 end if;
1772 END IF;
1773 END IF;
1774 END IF;
1775
1776 -- END BUG 5228046
1777
1778
1779
1780
1781
1782
1783
1784
1785 /*
1786 ||Added by aiyer for the bug 4691616
1787 ||added calculation for base_tax_amt and also changed the else to elsif
1788 */
1789 /*removed the round function for bug#10114559
1790 base_tax_amt_tab(I) := ROUND(vamt, rounding_factor_tab(I) );
1791 base_tax_amount_nr_tab(I):=ROUND(ln_vamt_nr, rounding_factor_tab(I) ); /*9382657*
1792 */
1793 base_tax_amt_tab(I) :=vamt; -- 10114559
1794 base_tax_amount_nr_tab(I):=ln_vamt_nr;--10114559
1795
1796
1797 -- Add by Kevin Cheng for inclusive tax Dec 10, 2007
1798 ----------------------------------------------------
1799 lt_tax_target_tab(I) := vamt;
1800 ln_tax_amt_nr:=ln_tax_amt_nr+(ln_vamt_nr*(tax_rate_tab(i)/100));
1801 ln_func_tax_amt := v_tax_amt + ( vamt * ( tax_rate_tab( i )/100));
1802 ----------------------------------------------------
1803 v_tax_amt := v_tax_amt + ( vamt * ( tax_rate_tab( i )/100));
1804
1805
1806 ELSIF tax_rate_tab(I) = 0 THEN
1807 /*
1808 || tax_rate_tab(i) will be zero when tax_rate of such a line is null.
1809 || i.e It is UOM based calculation. base_Tax_amount will be same as tax_amount
1810 */
1811 base_tax_amt_tab(I) := tax_amt_tab(i);
1812 v_tax_amt := tax_amt_tab( i );
1813 ln_tax_amt_nr:=lt_tax_amt_non_rate_tab(i); --Add by Kevin Cheng for inclusive tax Jan 08, 2008
1814 lt_tax_target_tab(I):= v_tax_amt; --Add by Kevin Cheng for inclusive tax Dec 10, 2007
1815 /*
1816 ||End of bug 4691616
1817 */
1818 --Add by Kevin Cheng for inclusive tax Dec 10, 2007
1819 ---------------------------------------------------
1820 ELSIF end_date_tab( I ) = 0 THEN
1821 tax_amt_tab(I) := 0;
1822 base_tax_amt_tab(I) := 0;
1823 base_tax_amount_nr_tab(I):=0; /*9382657*/
1824 lt_tax_target_tab(I) := 0;
1825 ---------------------------------------------------
1826 END IF;
1827 --Comment out by Kevin Cheng for inclusive tax Dec 10, 2007
1828 /*tax_amt_tab( I ) := NVL( v_tax_amt, 0 );
1829
1830 IF counter = max_iter THEN
1831 tax_amt_tab( I ) := ROUND( tax_amt_tab( I ), rounding_factor_tab(I) );
1832 END IF;
1833
1834 IF end_date_tab(I) = 0 THEN
1835 tax_amt_tab( i ) := 0;
1836 base_tax_amt_tab(i) := 0;
1837 END IF;*/
1838
1839 --Add by Kevin Cheng for inclusive tax Dec 10, 2007
1840 ---------------------------------------------------
1841 tax_amt_tab( I ) := NVL( v_tax_amt, 0 );
1842 lt_tax_amt_rate_tax_tab(i) := tax_amt_tab(I);
1843 lt_tax_amt_non_rate_tab(I):=ln_tax_amt_nr;
1844 lt_func_tax_amt_tab(I) := NVL(ln_func_tax_amt,0);
1845 IF counter = max_iter THEN
1846 IF end_date_tab(I) = 0 THEN
1847 tax_amt_tab( i ) := 0;
1848 lt_func_tax_amt_tab(i) := 0;
1849 END IF;
1850 END IF;
1851 ---------------------------------------------------
1852
1853 vamt := 0;
1854 v_amt := 0;
1855 v_tax_amt := 0;
1856 ln_func_tax_amt := 0; -- Add by Kevin Cheng for inclusive tax Dec 10, 2007
1857 ln_vamt_nr :=0; -- Add by Kevin Cheng for inclusive tax Dec 10, 2007
1858 ln_tax_amt_nr:=0;-- Add by Kevin Cheng for inclusive tax Dec 10, 2007
1859 END LOOP;
1860 END LOOP;
1861
1862 --Added by Kevin Cheng for inclusive tax Dec 10, 2007
1863 ---------------------------------------------------------------------------------------
1864 FOR I IN 1 .. ROW_COUNT
1865 LOOP
1866 IF lt_inclu_tax_tab(I) = 'Y' THEN
1867 ln_total_tax_per_rupee := ln_total_tax_per_rupee + nvl(lt_tax_amt_rate_tax_tab(I),0) ;
1868 ln_total_non_rate_tax := ln_total_non_rate_tax + nvl(lt_tax_amt_non_rate_tab(I),0);
1869 END IF;
1870 END LOOP;
1871
1872 ln_total_tax_per_rupee := ln_total_tax_per_rupee + 1;
1873
1874 IF ln_total_tax_per_rupee <> 0 THEN
1875 ln_exclusive_price := (NVL(p_tax_amount,0) - ln_total_non_rate_tax ) / ln_total_tax_per_rupee;
1876 END If;
1877
1878 /* Start Additions by mmurtuza for bug 16389308*/
1879 if NVL(v_rma_ctr,0) = 0 then
1880
1881 FOR rec in tax_cur(p_header_id, p_line_id) LOOP
1882
1883
1884 OPEN get_adhoc_flag(rec.tax_id);
1885
1886 FETCH get_adhoc_flag INTO lv_adhoc_flag;
1887 CLOSE get_adhoc_flag;
1888
1889 IF lv_adhoc_flag = 'Y' THEN
1890 IF nvl(p_line_quantity, 0) <> 0 THEN
1891 tax_amt_tab(rec.lno) := ROUND((rec.tax_amount * p_quantity/p_line_quantity), NVL(rec.rounding_factor, 0));
1892 END IF;
1893 END IF;
1894
1895
1896
1897
1898 END LOOP;
1899 elsif v_rma_ctr = 1 then
1900 FOR rec in c_rma_tax_cur(p_line_id) LOOP
1901
1902 OPEN get_adhoc_flag(rec.tax_id);
1903 FETCH get_adhoc_flag INTO lv_adhoc_flag;
1904 CLOSE get_adhoc_flag;
1905
1906 IF lv_adhoc_flag = 'Y' THEN
1907 IF nvl(p_line_quantity, 0) <> 0 THEN
1908 tax_amt_tab(rec.lno) := ROUND((rec.tax_amount * p_quantity/p_line_quantity), NVL(rec.rounding_factor, 0));
1909 END IF;
1910 END IF;
1911
1912 END LOOP;
1913 end if;
1914
1915 /* End Additions by mmurtuza for bug 16389308*/
1916
1917 FOR i in 1 .. row_count
1918 LOOP
1919 -- tax_amt_tab(i) := (lt_tax_amt_rate_tax_tab(I) * ln_exclusive_price ) + lt_tax_amt_non_rate_tab(I); --mmurtuza for bug 13473763
1920 -- tax_amt_tab(I) := round(tax_amt_tab(I) ,rounding_factor_tab(I)); --mmurtuza for bug 13473763
1921 /*Bug 9382657 - Multiply Line Amount with the Rate of the precedences*/
1922 -- base_tax_amt_tab(I):= ln_exclusive_price * base_tax_amt_tab(I) + base_tax_amount_nr_tab(I); --mmurtuza for bug 13473763
1923
1924 /*Start Additions by mmurtuza for bug 13473763 */
1925
1926 if (lt_adhoc_tax_tab(i) = 'N') then --Added if condition by mmurtuza for bug 16389308
1927 tax_amt_tab (i):=0;
1928 end if;
1929
1930 IF lt_inclu_tax_tab(I) = 'Y' THEN
1931 IF (tax_type_tab(I) = 1 and ln_assessable_value>1) OR
1932 (tax_type_tab(I) = 4 and ln_vat_assessable_value> 1) THEN
1933
1934 IF tax_type_tab(I) = 1 and ln_assessable_value >1 Then
1935 ln_amount:= ln_assessable_value;
1936 ELSIF tax_type_tab(I) = 4 and ln_vat_assessable_value >1 Then
1937 ln_amount:= ln_vat_assessable_value;
1938 END IF;
1939
1940 IF P1(I) < I AND p1(I) NOT IN (-1, 0) then
1941 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P1(i))* tax_rate_tab(i)/100;
1942 ELSIF P1(I)=0 Then
1943 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
1944 END IF;
1945
1946 IF P2(I) < I AND p2(I) NOT IN (-1, 0) then
1947 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P2(i))* tax_rate_tab(i)/100;
1948 ELSIF P2(I)=0 Then
1949 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
1950 END IF;
1951
1952 IF P3(I) < I AND p3(I) NOT IN (-1, 0) then
1953 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P3(i))* tax_rate_tab(i)/100;
1954 ELSIF P3(I)=0 Then
1955 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
1956 END IF;
1957
1958 IF P4(I) < I AND p4(I) NOT IN (-1, 0) then
1959 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P4(i))* tax_rate_tab(i)/100;
1960 ELSIF P4(I)=0 Then
1961 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
1962 END IF;
1963
1964 IF P5(I) < I AND p5(I) NOT IN (-1, 0) then
1965 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P5(i))* tax_rate_tab(i)/100;
1966 ELSIF P5(I)=0 Then
1967 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
1968 END IF;
1969
1970 IF P6(I) < I AND p6(I) NOT IN (-1, 0) then
1971 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P6(i))* tax_rate_tab(i)/100;
1972 ELSIF P6(I)=0 Then
1973 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
1974 END IF;
1975
1976 IF P7(I) < I AND p7(I) NOT IN (-1, 0) then
1977 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P7(i))* tax_rate_tab(i)/100;
1978 ELSIF P7(I)=0 Then
1979 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
1980 END IF;
1981
1982 IF P8(I) < I AND p8(I) NOT IN (-1, 0) then
1983 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P8(i))* tax_rate_tab(i)/100;
1984 ELSIF P8(I)=0 Then
1985 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
1986 END IF;
1987
1988 IF P9(I) < I AND p9(I) NOT IN (-1, 0) then
1989 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P9(i))* tax_rate_tab(i)/100;
1990 ELSIF P9(I)=0 Then
1991 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
1992 END IF;
1993
1994 IF P10(I) < I AND p10(I) NOT IN (-1, 0) then
1995 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P10(i))* tax_rate_tab(i)/100;
1996 ELSIF P10(I)=0 Then
1997 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
1998 END IF;
1999 ELSE
2000 tax_amt_tab (i) := (lt_tax_amt_rate_tax_tab(I) * ln_exclusive_price ) + lt_tax_amt_non_rate_tab(I);
2001 END IF;
2002 ELSE
2003 IF (tax_type_tab(I) = 1 and ln_assessable_value >1) OR
2004 (tax_type_tab(I) = 4 and ln_vat_assessable_value > 1) THEN
2005
2006 IF tax_type_tab(I) = 1 and ln_assessable_value >1 Then
2007 ln_amount:= ln_assessable_value;
2008 ELSIF tax_type_tab(I) = 4 and ln_vat_assessable_value >1 Then
2009 ln_amount:= ln_vat_assessable_value;
2010 END IF;
2011 ELSE ln_amount:= p_tax_amount;
2012 END IF;
2013 IF P1(I) < I AND p1(I) NOT IN (-1, 0) then
2014 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P1(i))* tax_rate_tab(i)/100;
2015 ELSIF P1(I)=0 Then
2016 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
2017 END IF;
2018
2019 IF P2(I) < I AND p2(I) NOT IN (-1, 0) then
2020 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P2(i))* tax_rate_tab(i)/100;
2021 ELSIF P2(I)=0 Then
2022 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
2023 END IF;
2024
2025 IF P3(I) < I AND p3(I) NOT IN (-1, 0) then
2026 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P3(i))* tax_rate_tab(i)/100;
2027 ELSIF P3(I)=0 Then
2028 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
2029 END IF;
2030
2031 IF P4(I) < I AND p4(I) NOT IN (-1, 0) then
2032 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P4(i))* tax_rate_tab(i)/100;
2033 ELSIF P4(I)=0 Then
2034 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
2035 END IF;
2036
2037 IF P5(I) < I AND p5(I) NOT IN (-1, 0) then
2038 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P5(i))* tax_rate_tab(i)/100;
2039 ELSIF P5(I)=0 Then
2040 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
2041 END IF;
2042
2043 IF P6(I) < I AND p6(I) NOT IN (-1, 0) then
2044 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P6(i))* tax_rate_tab(i)/100;
2045 ELSIF P6(I)=0 Then
2046 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
2047 END IF;
2048
2049 IF P7(I) < I AND p7(I) NOT IN (-1, 0) then
2050 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P7(i))* tax_rate_tab(i)/100;
2051 ELSIF P7(I)=0 Then
2052 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
2053 END IF;
2054
2055 IF P8(I) < I AND p8(I) NOT IN (-1, 0) then
2056 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P8(i))* tax_rate_tab(i)/100;
2057 ELSIF P8(I)=0 Then
2058 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
2059 END IF;
2060
2061 IF P9(I) < I AND p9(I) NOT IN (-1, 0) then
2062 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P9(i))* tax_rate_tab(i)/100;
2063 ELSIF P9(I)=0 Then
2064 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
2065 END IF;
2066
2067 IF P10(I) < I AND p10(I) NOT IN (-1, 0) then
2068 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P10(i))* tax_rate_tab(i)/100;
2069 ELSIF P10(I)=0 Then
2070 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
2071 END IF;
2072 END IF;
2073
2074 /*Bug 14358278 -- Start Additions by mmurtuza*/
2075 if (nvl(qty_rate_tab(i),0) <> 0) then
2076 tax_amt_tab(I) := qty_rate_tab(i) * p_quantity;
2077 end if;
2078 /*Bug 14358278 -- End Additions by mmurtuza*/
2079
2080 ------------------------------------------------------------------
2081 -- Added by Shyan for bug 10358786 on 28-Jan-2011 , End
2082 lt_func_tax_amt_tab(i):=tax_amt_tab (i); --Added by walton for bug#9288016
2083 tax_amt_tab(I) := round(tax_amt_tab(I) ,rounding_factor_tab(I));
2084 base_tax_amt_tab(I):= ln_exclusive_price * base_tax_amt_tab(I)
2085 + base_tax_amount_nr_tab(I); --added by Xiao Lv for bug#8789761 on 30-Oct-09
2086
2087 /*End Additions by mmurtuza for bug 13473763*/
2088
2089 /*Bug 14358278 -- Start Additions by mmurtuza*/
2090 if (nvl(qty_rate_tab(i),0) <> 0) then
2091 base_tax_amt_tab(I):= tax_amt_tab(I);
2092 end if;
2093 /*Bug 14358278 -- End Additions by mmurtuza*/
2094
2095 END LOOP;
2096 --------------------------------------------------------------------------------------------------------
2097
2098 if NVL(v_rma_ctr,0) = 0 then -- added by sriram - bug # 2740443
2099
2100 FOR rec in tax_cur(p_header_id, p_line_id) LOOP
2101 IF tax_type_tab(rec.lno) <> 2
2102 THEN
2103 IF NVL(rec.inclusive_tax_flag,'N') = 'N' THEN -- Added by Jia for bug# 6846048
2104 v_tax_amt := v_tax_amt + nvl(tax_amt_tab(rec.lno),0);
2105 END IF; -- Added by Jia for bug# 6846048
2106 END IF;
2107
2108 /* Start Additions by mmurtuza for bug 14031594*/
2109
2110 /*Start commenting by mmurtuza for bug 16389308. Moved in the intial place of this procedure*/
2111
2112 /*OPEN get_adhoc_flag(rec.tax_id);
2113
2114 FETCH get_adhoc_flag INTO lv_adhoc_flag;
2115 CLOSE get_adhoc_flag;
2116
2117 IF lv_adhoc_flag = 'Y' THEN
2118 IF nvl(p_line_quantity, 0) <> 0 THEN
2119 tax_amt_tab(rec.lno) := ROUND((rec.tax_amount * p_quantity/p_line_quantity), NVL(rec.rounding_factor, 0));
2120 END IF;
2121 END IF;*/
2122
2123 /*End commenting by mmurtuza for bug 16389308*/
2124
2125 /* End Additions by mmurtuza for bug 14031594*/
2126
2127 IF transaction_name = 'OE_LINES_UPDATE'
2128 THEN
2129 UPDATE JAI_OM_OE_SO_TAXES
2130 SET tax_amount = nvl(tax_amt_tab(rec.lno),0),
2131 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)),
2132 func_tax_amount = ROUND(nvl(tax_amt_tab(rec.lno),0) * v_currency_conv_factor, rounding_factor_tab(rec.lno) ),
2133 last_update_date = p_last_update_date,
2134 last_updated_by = p_last_updated_by,
2135 last_update_login = p_last_update_login
2136 WHERE line_id = P_line_id
2137 AND header_id = p_header_id
2138 AND tax_line_no = rec.lno;
2139 END IF;
2140 END LOOP;
2141 elsif v_rma_ctr = 1 then
2142 -- added by sriram -following from elsif to end if bug # 2740443
2143 FOR rec in c_rma_tax_cur(p_line_id) LOOP
2144 IF tax_type_tab(rec.lno) <> 2
2145 THEN
2146 v_tax_amt := v_tax_amt + nvl(tax_amt_tab(rec.lno),0);
2147 END IF;
2148
2149 /* Start Additions by mmurtuza for bug 14031594*/
2150
2151 /*Start commenting by mmurtuza for bug 16389308. Moved in the intial place of this procedure*/
2152
2153 /*OPEN get_adhoc_flag(rec.tax_id);
2154 FETCH get_adhoc_flag INTO lv_adhoc_flag;
2155 CLOSE get_adhoc_flag;
2156
2157 IF lv_adhoc_flag = 'Y' THEN
2158 IF nvl(p_line_quantity, 0) <> 0 THEN
2159 tax_amt_tab(rec.lno) := ROUND((rec.tax_amount * p_quantity/p_line_quantity), NVL(rec.rounding_factor, 0));
2160 END IF;
2161 END IF;*/
2162
2163 /*End commenting by mmurtuza for bug 16389308*/
2164
2165 /* End Additions by mmurtuza for bug 14031594*/
2166
2167 IF transaction_name = 'OE_LINES_UPDATE'
2168 THEN
2169
2170 UPDATE JAI_OM_OE_RMA_TAXES
2171 SET tax_amount = nvl(tax_amt_tab(rec.lno),0),
2172 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)),
2173 func_tax_amount = ROUND(nvl(tax_amt_tab(rec.lno),0) * v_currency_conv_factor, rounding_factor_tab(rec.lno) ),
2174 last_update_date = p_last_update_date,
2175 last_updated_by = p_last_updated_by,
2176 last_update_login = p_last_update_login
2177 WHERE rma_line_id = P_line_id
2178 AND tax_line_no = rec.lno;
2179 END IF;
2180 END LOOP;
2181 end if; -- ends here additions by sriram - bug # 2740443
2182
2183 P_TAX_AMOUNT := nvl(v_tax_amt,0);
2184
2185 EXCEPTION
2186 WHEN OTHERS THEN
2187 p_tax_amount := null;
2188 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
2189 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
2190 app_exception.raise_exception;
2191 END calculate_ato_taxes;
2192
2193
2194 procedure recalculate_oe_taxes(
2195 p_header_id IN NUMBER,
2196 p_line_id IN NUMBER,
2197 p_assessable_value IN NUMBER DEFAULT 0,
2198 p_vat_assess_value IN NUMBER,
2199 p_tax_amount IN OUT NOCOPY NUMBER,
2200 p_inventory_item_id IN NUMBER,
2201 p_line_quantity IN NUMBER,
2202 p_uom_code IN VARCHAR2,
2203 p_currency_conv_factor IN NUMBER,
2204 p_last_updated_date IN DATE,
2205 p_last_updated_by IN NUMBER,
2206 p_last_update_login IN NUMBER
2207 --,pn_gst_assess_value IN NUMBER --Added by zhiwei for bug10043656 GST enhancement 2010/09/14 /*Commented by mmurtuza for removing GST Changes*/
2208 ) IS
2209
2210 -- P_TAX_AMOUNT input parameter will contain the line_tax_amount after successful completion of the procedure which
2211 -- can be used to update the line amount in JAI_OM_OE_SO_LINES
2212
2213 TYPE num_tab IS TABLE OF NUMBER(20,3) INDEX BY BINARY_INTEGER;
2214 TYPE tax_amt_num_tab IS TABLE OF NUMBER(20,3) INDEX BY BINARY_INTEGER;
2215
2216 --Add by Kevin Cheng for inclusive tax Dec 10, 2007
2217 ---------------------------------------------------
2218 TYPE CHAR_TAB IS TABLE OF VARCHAR2(10)
2219 INDEX BY BINARY_INTEGER;
2220
2221 lt_adhoc_tax_tab CHAR_TAB;
2222 lt_inclu_tax_tab CHAR_TAB;
2223 lt_tax_rate_per_rupee NUM_TAB;
2224 lt_cumul_tax_rate_per_rupee NUM_TAB;
2225 lt_tax_rate_zero_tab NUM_TAB;
2226 lt_round_factor_tab NUM_TAB;
2227 lt_tax_amt_rate_tax_tab TAX_AMT_NUM_TAB;
2228 lt_tax_amt_non_rate_tab TAX_AMT_NUM_TAB;
2229 lv_register_code VARCHAR2(20);
2230 ln_exclusive_price NUMBER;
2231 ln_total_non_rate_tax NUMBER := 0;
2232 ln_total_inclusive_factor NUMBER;
2233 ln_bsln_amt_nr NUMBER := 0;
2234 ln_currency_conv_factor NUMBER;
2235 ln_tax_amt_nr NUMBER(38,10) := 0;
2236 ln_vamt_nr NUMBER(38,10) := 0;
2237 ln_excise_jb NUMBER;
2238 ln_total_tax_per_rupee NUMBER;
2239 ln_assessable_value NUMBER;
2240 ln_vat_assessable_value NUMBER;
2241 --ln_gst_assessable_value NUMBER; -- Added by zhiwei for bug10043656 GST enhancement 2010/09/14 /*Commented by mmurtuza for removing GST Changes*/
2242
2243 ---------------------------------------------------
2244
2245 p1 NUM_TAB;
2246 p2 NUM_TAB;
2247 p3 NUM_TAB;
2248 p4 NUM_TAB;
2249 p5 NUM_TAB;
2250
2251 -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
2252 -- START BUG 5228046
2253
2254 p6 NUM_TAB;
2255 p7 NUM_TAB;
2256 p8 NUM_TAB;
2257 p9 NUM_TAB;
2258 p10 NUM_TAB;
2259
2260 -- END BUG 5228046
2261
2262
2263 tax_rate_tab NUM_TAB;
2264 tax_type_tab NUM_TAB;
2265 tax_target_tab NUM_TAB;
2266 tax_amt_tab TAX_AMT_NUM_TAB;
2267 base_tax_amt_tab TAX_AMT_NUM_TAB;
2268 base_tax_amount_nr_tab tax_amt_num_tab; /*Bug 9382657*/
2269 func_tax_amt_tab TAX_AMT_NUM_TAB;
2270 end_date_tab NUM_TAB;
2271
2272 bsln_amt NUMBER; -- := p_tax_amount; --Ramananda for File.Sql.35
2273 v_conversion_rate NUMBER := 0;
2274 v_tax_amt NUMBER(20,3) := 0;
2275 v_func_tax_amt NUMBER(20,3) := 0;
2276 v_rounded_tax NUMBER(20,3) := 0;
2277 vamt NUMBER(20,3) :=0;
2278 v_amt NUMBER;
2279 row_count NUMBER := 1;
2280 counter NUMBER;
2281 max_iter NUMBER := 10;
2282 ln_amount NUMBER; --mmurtuza for bug 13819477
2283
2284 /* Added by LGOPALSa. Bug 4210102.
2285 * Added Excise education cess in cursor */
2286
2287 /*Added VAT regime in cursor by Ravi for bug#4245365*/
2288 --Comment out by Kevin Cheng for inclusive tax Dec 11, 2007
2289 /*CURSOR c_tax_lines(p_header_id IN NUMBER, p_line_id IN NUMBER) IS
2290 SELECT a.tax_id, a.tax_line_no lno,
2291 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,
2292 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 )
2293 b.tax_amount, b.tax_rate, b.uom_code, b.end_date valid_date,
2294 a.tax_amount tax_line_amt,
2295 DECODE(aa.regime_code,'VAT',4, decode(upper(b.tax_type),
2296 'EXCISE', 1,
2297 'ADDL. EXCISE', 1,
2298 'OTHER EXCISE', 1,
2299 JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS,1,jai_constants.tax_type_exc_edu_Cess,1, \*Bug 5989740 bduvarag*\
2300 'TDS', 2, 0)) tax_type_val,
2301 b.tax_type,nvl(b.rounding_factor,0) rounding_factor
2302 FROM JAI_OM_OE_SO_TAXES a, JAI_CMN_TAXES_ALL b,jai_regime_tax_types_v aa
2303 WHERE a.header_id = p_header_id
2304 AND a.line_id = p_line_id
2305 AND a.tax_id = b.tax_id
2306 AND aa.tax_type(+) = b.tax_type
2307 ORDER BY a.tax_line_no;*/
2308
2309 -- Add by Kevin Cheng for inclusive tax Dec 10, 2007
2310 CURSOR c_tax_lines( p_header_id IN NUMBER
2311 , p_line_id IN NUMBER
2312 )
2313 IS
2314 SELECT
2315 a.tax_id
2316 , a.tax_category_id
2317 , a.tax_line_no lno
2318 , a.precedence_1 p_1
2319 , a.precedence_2 p_2
2320 , a.precedence_3 p_3
2321 , a.precedence_4 p_4
2322 , a.precedence_5 p_5
2323 , a.precedence_6 p_6
2324 , a.precedence_7 p_7
2325 , a.precedence_8 p_8
2326 , a.precedence_9 p_9
2327 , a.precedence_10 p_10 -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
2328 , b.tax_amount
2329 , b.tax_rate
2330 , b.uom_code
2331 , b.end_date valid_date
2332 , a.tax_amount tax_line_amt
2333 , DECODE( aa.regime_code
2334 , JAI_CONSTANTS.VAT_REGIME
2335 , 4
2336 --, JAI_CONSTANTS.cgst_regime --Added by zhiwei for bug10043656 GST enhancement 2010/09/14 /*Commented by mmurtuza for removing GST Changes*/
2337 --, 7
2338 --, JAI_CONSTANTS.sgst_regime --Added by zhiwei for bug10043656 GST enhancement 2010/09/14 /*Commented by mmurtuza for removing GST Changes*/
2339 --, 7
2340 , decode( upper(b.tax_type)
2341 , 'EXCISE' ,1
2342 , 'ADDL. EXCISE' ,1
2343 , 'OTHER EXCISE' ,1
2344 , jai_constants.tax_type_tds ,2
2345 , jai_constants.tax_type_exc_edu_cess ,6
2346 , jai_constants.tax_type_cvd_edu_cess ,6
2347 , jai_constants.tax_type_sh_exc_edu_cess ,6
2348 , jai_constants.tax_type_sh_cvd_edu_cess ,6
2349 , 0
2350 )
2351 ) tax_type_val
2352 , b.inclusive_tax_flag
2353 , b.mod_cr_percentage
2354 , b.vendor_id
2355 , b.tax_type
2356 , nvl(b.rounding_factor,0) rounding_factor
2357 FROM
2358 Jai_Om_Oe_So_Taxes a
2359 , Jai_Cmn_Taxes_All b
2360 , Jai_Regime_Tax_Types_V aa
2361 WHERE a.header_id = p_header_id
2362 AND a.line_id = p_line_id
2363 AND a.tax_id = b.tax_id
2364 AND aa.tax_type(+) = b.tax_type
2365 ORDER BY
2366 a.tax_line_no;
2367
2368 CURSOR c_uom_class(p_line_uom_code IN VARCHAR2, p_tax_line_uom_code IN VARCHAR2) IS
2369 SELECT A.uom_class
2370 FROM mtl_units_of_measure A, mtl_units_of_measure B
2371 WHERE A.uom_code = p_line_uom_code
2372 AND B.uom_code = p_tax_line_uom_code
2373 AND A.uom_class = B.uom_class;
2374
2375 -- Start of bug 3565499
2376 /*****************
2377 Code modified by aiyer for the bug 3565499
2378 Check whether the excise exemptions exist at the order line level.
2379 *****************/
2380 CURSOR c_excise_exemption
2381 IS
2382 SELECT
2383 '1'
2384 FROM
2385 JAI_OM_OE_SO_LINES jsl
2386 WHERE
2387 jsl.excise_exempt_type IS NOT NULL AND
2388 jsl.line_id = p_line_id ;
2389
2390 lv_excise_exemption_exists VARCHAR2(1);
2391
2392 -- End of bug 3565499
2393 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_om_tax_pkg.recalculate_oe_taxes';
2394 BEGIN
2395 /*-------------------------------------------------------------------------------------------------------------------------
2396 S.No Date(DD/MM/YY) Author and Details of Changes
2397 ---- -------------- -----------------------------
2398 1 31/07/02 Created by Vijay Shankar for Bug# 2485077, 2496481, File Version 615.1
2399 This procedure recalculates the taxes by picking tax lines attached to the sales order line.
2400 If the assessable value of an item is changed, then the excise duty attached to the sales orders
2401 have to be recalculated if they have order lines having assessable value changed. Sales order which
2402 are not ship confirmed( also partially shipped if user specified input says 'Partially Shipped' as 'Y'
2403
2404 3. 20-Feb-2004 Aiyer For the Bug #3565499 File Version 619.1
2405 Issue:-
2406 India Excise Duty Recalculation Concurrent program recalculates the excise duty even for excise exempted Order Lines.
2407 Fix:-
2408 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.
2409 The cursor c_excise_exemption has been added for the purpose.
2410
2411 4. 12-Mar-2005 Bug 4210102. Added by LGOPALSA version 115.1
2412 (1) Added check file syntax
2413 (2) Added NOCOPY for OUT Parameters
2414 (3) Added <> instead of !=
2415 (4) Added Excise education cess type
2416
2417 5. 17/mar-2005 Rchandan for bug#4245365 Version 115.2
2418 Changes made to calculate VAT taxes taking the VAT assessable value as base
2419 New parameter is added for having vat assesable value.
2420
2421 6. 11-Dec-2007 Kevin Cheng Update the logic for inclusive tax calculation
2422
2423 7. 03-Mar-2011 Abezgam Bug: 11779012
2424 Description: 'India Excise Duty Recalculation' is not updating the pending sales order
2425 transactions with the latest Excise Assessable value as per the price list.
2426 Fix: Initialized the parameter base_tax_amount_nr_tab in the procedure recalculate_oe_taxes
2427
2428 8. 09-Mar-2012 mmurtuza for bug 13819477
2429 Description: IL12.1.1 INDIA MASS TAX RECALCULATION CONCURRENT CALCULATES INCORRECT TAXES.
2430 Fix: Modified the procedure recalculate_oe_taxes to perform calculation of taxes correctly.
2431 (Ported from ja_in_calc_prec_taxes procedure)
2432 ===============================================================================
2433 Dependencies
2434
2435 Version Dependencies Comments
2436 115.1 IN60106 + 4146708 Service and eduation cess functionality
2437 115.2 4245089 VAT implementation
2438 --------------------------------------------------------------------------------------------------------------------------*/
2439
2440 bsln_amt := p_tax_amount; --Ramananda for File.Sql.35
2441
2442 -- Start of bug 3565499
2443 /*****************
2444 Code modified by aiyer for the bug 3565499
2445 *****************/
2446 OPEN c_excise_exemption;
2447 FETCH c_excise_exemption INTO lv_excise_exemption_exists;
2448 -- End of bug 3565499
2449 FOR rec in c_tax_lines(p_header_id, p_line_id) LOOP
2450
2451 p1(rec.lno) := nvl(rec.p_1,-1);
2452 p2(rec.lno) := nvl(rec.p_2,-1);
2453 p3(rec.lno) := nvl(rec.p_3,-1);
2454 p4(rec.lno) := nvl(rec.p_4,-1);
2455 p5(rec.lno) := nvl(rec.p_5,-1);
2456
2457 -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
2458 -- START BUG 5228046
2459
2460 p6(rec.lno) := nvl(rec.p_6,-1);
2461 p7(rec.lno) := nvl(rec.p_7,-1);
2462 p8(rec.lno) := nvl(rec.p_8,-1);
2463 p9(rec.lno) := nvl(rec.p_9,-1);
2464 p10(rec.lno) := nvl(rec.p_10,-1);
2465
2466 -- END BUG 5228046
2467
2468 tax_rate_tab(rec.lno) := nvl(rec.tax_rate,0);
2469 tax_type_tab(rec.lno) := rec.tax_type_val;
2470 tax_amt_tab(rec.lno) := 0;
2471 base_tax_amt_tab(rec.lno) := 0;
2472 base_tax_amount_nr_tab(rec.lno) := 0; /*added by abezgam for bug 11779012*/
2473
2474 --Add by Kevin Cheng for inclusive tax Dec 11, 2007
2475 ---------------------------------------------------
2476 lt_tax_rate_per_rupee(rec.lno) := NVL(rec.tax_rate,0)/100;
2477 ln_total_tax_per_rupee := 0;
2478 lt_inclu_tax_tab(rec.lno) := NVL(rec.inclusive_tax_flag,'N');
2479 lt_round_factor_tab(rec.lno) := NVL(rec.rounding_factor,0);
2480
2481 IF rec.tax_rate is null THEN
2482 lt_tax_rate_zero_tab(rec.lno) := 0;
2483 ELSIF rec.tax_rate = 0 THEN
2484 lt_tax_rate_zero_tab(rec.lno) := -9999;
2485 ELSE
2486 lt_tax_rate_zero_tab(rec.lno) := rec.tax_rate;
2487 END IF;
2488
2489 lt_tax_amt_rate_tax_tab(rec.lno) := 0;
2490 lt_tax_amt_non_rate_tab(rec.lno) := 0; -- tax inclusive
2491 ---------------------------------------------------
2492
2493 IF tax_rate_tab(rec.lno) = 0 THEN
2494 FOR uom_cls IN c_uom_class(p_uom_code, rec.uom_code) LOOP
2495 INV_CONVERT.inv_um_conversion( p_uom_code, rec.uom_code, p_inventory_item_id, v_conversion_rate);
2496 IF nvl(v_conversion_rate, 0) <= 0 THEN
2497 INV_CONVERT.inv_um_conversion( p_uom_code, rec.uom_code, 0, v_conversion_rate);
2498 IF nvl(v_conversion_rate, 0) <= 0 THEN
2499 v_conversion_rate := 0;
2500 END IF;
2501 END IF;
2502
2503 --Comment out by Kevin Cheng for inclusive tax Dec 11, 2007
2504 /*tax_amt_tab(rec.lno) := (nvl(rec.tax_amount * v_conversion_rate, 0) * p_line_quantity);*/
2505 --Add by Kevin Cheng for inclusive tax Dec 11, 2007
2506 lt_tax_amt_non_rate_tab(rec.lno) := (nvl(rec.tax_amount * v_conversion_rate, 0) * p_line_quantity);
2507
2508 --Comment out by Kevin Cheng for inclusive tax Dec 11, 2007
2509 /*base_tax_amt_tab(rec.lno) := tax_amt_tab(rec.lno);*/
2510 --Add by Kevin Cheng for inclusive tax Dec 11, 2007
2511 base_tax_amt_tab(rec.lno) := lt_tax_amt_non_rate_tab(rec.lno);
2512 END LOOP;
2513
2514 --Comment out by Kevin Cheng for inclusive tax Dec 11, 2007
2515 /*IF tax_amt_tab(rec.lno) = 0 THEN -- this means user has given some adhoc amount
2516 tax_amt_tab(rec.lno) := nvl(rec.tax_line_amt,0);
2517 END IF;*/
2518 --Add by Kevin Cheng for inclusive tax Dec 11, 2007
2519 IF lt_tax_amt_non_rate_tab(rec.lno) = 0 THEN -- this means user has given some adhoc amount
2520 lt_tax_amt_non_rate_tab(rec.lno) := nvl(rec.tax_line_amt,0);
2521 END IF;
2522 END IF;
2523
2524 IF rec.valid_date is NULL or rec.valid_date >= sysdate THEN
2525 end_date_tab(rec.lno) := 1;
2526 ELSE
2527 tax_amt_tab(rec.lno):= 0;
2528 end_date_tab(rec.lno) := 0;
2529 END IF;
2530
2531 -- Start of bug 3565499
2532 /*****************
2533 Code modified by aiyer for the bug 3565499
2534 IF the line is excise exempted and the tax is of type Excise then set the tax_rate, tax_amount and base_tax_amount
2535 to zero.
2536 *****************/
2537 IF c_excise_exemption%FOUND AND
2538 rec.tax_type_val = 1
2539 THEN
2540 /* Set tax_rate_tab = 0, tax_amt_tab = 0 and base_tax_amt_tab = 0 */
2541 tax_rate_tab(rec.lno) := 0;
2542 tax_amt_tab(rec.lno) := 0;
2543 base_tax_amt_tab(rec.lno) := 0;
2544 lt_tax_amt_non_rate_tab(rec.lno) :=0; -- Add by Kevin Cheng for inclusive tax Dec 11, 2007
2545 END IF;
2546 -- End of bug 3565499
2547 row_count := row_count + 1;
2548 END LOOP;
2549 -- Start of bug 3565499
2550 CLOSE c_excise_exemption ;
2551 -- End of bug 3565499
2552
2553 row_count := row_count - 1;
2554
2555 --added by Kevin Cheng for inclusive tax Dec 11, 2007
2556 -------------------------------------------------
2557 IF p_vat_assess_value <> p_tax_amount
2558 THEN
2559 ln_vat_assessable_value := p_vat_assess_value;
2560 ELSE
2561 ln_vat_assessable_value := 1;
2562 END IF;
2563
2564 IF p_assessable_value <> p_tax_amount
2565 THEN
2566 ln_assessable_value := p_assessable_value;
2567 ELSE
2568 ln_assessable_value := 1;
2569 END IF;
2570 ---------------------------------------------------
2571
2572 --Added by zhiwei for bug10043656 GST enhancement 2010/09/14 begin
2573 -------------------------------------------------
2574 /*Start Commenting by mmurtuza for removing GST Changes*/
2575 /*IF pn_gst_assess_value <> p_tax_amount
2576 THEN
2577 ln_gst_assessable_value := pn_gst_assess_value;
2578 ELSE
2579 ln_gst_assessable_value := 1;
2580 END IF;*/
2581 /*End Commenting by mmurtuza for removing GST Changes*/
2582 -------------------------------------------------
2583 --Added by zhiwei for bug10043656 GST enhancement 2010/09/14 end
2584
2585
2586 FOR I in 1..row_count LOOP
2587 IF end_date_tab(I) <> 0 THEN
2588 IF tax_type_tab(I) = 1 THEN
2589 /*bsln_amt := p_assessable_value;*/--Comment out by Kevin Cheng for inclusive tax Dec 11, 2007
2590 --Add by Kevin Cheng for inclusive tax Dec 11, 2007
2591 ---------------------------------------------------
2592 IF ln_assessable_value = 1 THEN
2593 bsln_amt := 1;
2594 ln_bsln_amt_nr := 0;
2595 ELSE
2596 bsln_amt := 0;
2597 ln_bsln_amt_nr := ln_assessable_value;
2598 END IF;
2599 ---------------------------------------------------
2600 ELSIF tax_type_tab(I) = 4 THEN --4245365
2601 /*bsln_amt := p_vat_assess_value;*/--Comment out by Kevin Cheng for inclusive tax Dec 11, 2007
2602 --Add by Kevin Cheng for inclusive tax Dec 11, 2007
2603 ---------------------------------------------------
2604 IF ln_vat_assessable_value = 1 THEN
2605 bsln_amt := 1;
2606 ln_bsln_amt_nr := 0;
2607 ELSE
2608 bsln_amt := 0;
2609 ln_bsln_amt_nr := ln_vat_assessable_value;
2610 END IF;
2611
2612 --Added by zhiwei for bug10043656 GST enhancement 2010/09/14 begin
2613 ---------------------------------------------------
2614 /*Start Commenting by mmurtuza for removing GST Changes*/
2615 /*ELSIF tax_type_tab(I) = 7 THEN
2616 IF ln_gst_assessable_value = 1 THEN
2617 bsln_amt := 1;
2618 ln_bsln_amt_nr := 0;
2619 ELSE
2620 bsln_amt := 0;
2621 ln_bsln_amt_nr := ln_gst_assessable_value;
2622 END IF;*/
2623 /*End Commenting by mmurtuza for removing GST Changes*/
2624 ---------------------------------------------------
2625 --Added by zhiwei for bug10043656 GST enhancement 2010/09/14 end
2626
2627 ELSIF tax_type_tab(I) = 6 THEN --IF tax_type_tab(I) = 1 THEN
2628 bsln_amt := 0;
2629 ln_bsln_amt_nr := 0;
2630 ELSE --IF tax_type_tab(I) = 1 THEN
2631 bsln_amt := 1;
2632 ln_bsln_amt_nr := 0;
2633 ---------------------------------------------------
2634 --Comment out by Kevin Cheng for inclusive tax Dec 11, 2007
2635 /*ELSE
2636 bsln_amt := p_tax_amount;*/
2637 END IF;
2638 IF tax_rate_tab(I) <> 0 THEN
2639
2640 lt_tax_amt_non_rate_tab(I):=0; --mmurtuza for bug 13819477
2641 IF p1(I) < I and p1(I) not in (-1,0) THEN
2642 IF lt_inclu_tax_tab(P1(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P1(I)) IS NOT NULL then --mmurtuza for bug 13819477
2643 vamt := vamt + nvl(tax_amt_tab(p1(I)),0);
2644 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
2645 end if; --mmurtuza for bug 13819477
2646 ELSIF p1(I) = 0 THEN
2647 IF lt_inclu_tax_tab(I) = 'Y' then --bug 13819477
2648 vamt := vamt + bsln_amt;
2649 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --Add by Kevin Cheng for inclusive tax Dec 11, 2007
2650 else
2651 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount; -- bug 13819477
2652 END IF;
2653 END IF;
2654 IF p2(I) < I and p2(I) not in (-1,0) THEN
2655 IF lt_inclu_tax_tab(P2(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P2(I)) IS NOT NULL then --mmurtuza for bug 13819477
2656 vamt := vamt + nvl(tax_amt_tab(p2(I)),0);
2657 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
2658 end if; --mmurtuza for bug 13819477
2659 ELSIF p2(I) = 0 THEN
2660 IF lt_inclu_tax_tab(I) = 'Y' then -- bug 13819477
2661 vamt := vamt + bsln_amt;
2662 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --Add by Kevin Cheng for inclusive tax Dec 11, 2007
2663 else
2664 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount; -- bug 13819477
2665 END IF;
2666 END IF;
2667 IF p3(I) < I and p3(I) not in (-1,0) THEN
2668 IF lt_inclu_tax_tab(P3(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P3(I)) IS NOT NULL then --mmurtuza for bug 13819477
2669 vamt := vamt + nvl(tax_amt_tab(p3(I)),0);
2670 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
2671 end if; --mmurtuza for bug 13819477
2672 ELSIF p3(I) = 0 THEN
2673 IF lt_inclu_tax_tab(I) = 'Y' then -- bug 13819477
2674 vamt := vamt + bsln_amt;
2675 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --Add by Kevin Cheng for inclusive tax Dec 11, 2007
2676 else
2677 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount; -- bug 13819477
2678 END IF;
2679 END IF;
2680 IF p4(I) < I and p4(I) not in (-1,0) THEN
2681 IF lt_inclu_tax_tab(P4(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P4(I)) IS NOT NULL then --mmurtuza for bug 13819477
2682 vamt := vamt + nvl(tax_amt_tab(p4(I)),0);
2683 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
2684 end if; --mmurtuza for bug 13819477
2685 ELSIF p4(I) = 0 THEN
2686 IF lt_inclu_tax_tab(I) = 'Y' then -- bug 13819477
2687 vamt := vamt + bsln_amt;
2688 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --Add by Kevin Cheng for inclusive tax Dec 11, 2007
2689 else
2690 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount; -- bug 13819477
2691 END IF;
2692 END IF;
2693 IF p5(I) < I and p5(I) not in (-1,0) THEN
2694 IF lt_inclu_tax_tab(P5(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P5(I)) IS NOT NULL then --mmurtuza for bug 13819477
2695 vamt := vamt + nvl(tax_amt_tab(p5(I)),0);
2696 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
2697 end if; --mmurtuza for bug 13819477
2698 ELSIF p5(I) = 0 THEN
2699 IF lt_inclu_tax_tab(I) = 'Y' then -- bug 13819477
2700 vamt := vamt + bsln_amt;
2701 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --Add by Kevin Cheng for inclusive tax Dec 11, 2007
2702 else
2703 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount; -- bug 13819477
2704 END IF;
2705 END IF;
2706
2707 -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
2708 -- START BUG 5228046
2709
2710 IF p6(I) < I and p6(I) not in (-1,0) THEN
2711 IF lt_inclu_tax_tab(P6(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P6(I)) IS NOT NULL then --mmurtuza for bug 13819477
2712 vamt := vamt + nvl(tax_amt_tab(p6(I)),0);
2713 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
2714 end if; --mmurtuza for bug 13819477
2715 ELSIF p6(I) = 0 THEN
2716 IF lt_inclu_tax_tab(I) = 'Y' then -- bug 13819477
2717 vamt := vamt + bsln_amt;
2718 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --Add by Kevin Cheng for inclusive tax Dec 11, 2007
2719 else
2720 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount; -- bug 13819477
2721 END IF;
2722 END IF;
2723
2724 IF p7(I) < I and p7(I) not in (-1,0) THEN
2725 IF lt_inclu_tax_tab(P7(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P7(I)) IS NOT NULL then --mmurtuza for bug 13819477
2726 vamt := vamt + nvl(tax_amt_tab(p7(I)),0);
2727 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
2728 end if; --mmurtuza for bug 13819477
2729 ELSIF p7(I) = 0 THEN
2730 IF lt_inclu_tax_tab(I) = 'Y' then -- bug 13819477
2731 vamt := vamt + bsln_amt;
2732 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --Add by Kevin Cheng for inclusive tax Dec 11, 2007
2733 else
2734 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount; -- bug 13819477
2735 END IF;
2736 END IF;
2737
2738 IF p8(I) < I and p8(I) not in (-1,0) THEN
2739 IF lt_inclu_tax_tab(P8(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P8(I)) IS NOT NULL then --mmurtuza for bug 13819477
2740 vamt := vamt + nvl(tax_amt_tab(p8(I)),0);
2741 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
2742 end if; --mmurtuza for bug 13819477
2743 ELSIF p8(I) = 0 THEN
2744 IF lt_inclu_tax_tab(I) = 'Y' then -- bug 13819477
2745 vamt := vamt + bsln_amt;
2746 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --Add by Kevin Cheng for inclusive tax Dec 11, 2007
2747 else
2748 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount; -- bug 13819477
2749 END IF;
2750 END IF;
2751
2752 IF p9(I) < I and p9(I) not in (-1,0) THEN
2753 IF lt_inclu_tax_tab(P9(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P9(I)) IS NOT NULL then --mmurtuza for bug 13819477
2754 vamt := vamt + nvl(tax_amt_tab(p9(I)),0);
2755 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
2756 end if; --mmurtuza for bug 13819477
2757 ELSIF p9(I) = 0 THEN
2758 IF lt_inclu_tax_tab(I) = 'Y' then -- bug 13819477
2759 vamt := vamt + bsln_amt;
2760 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --Add by Kevin Cheng for inclusive tax Dec 11, 2007
2761 else
2762 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount; -- bug 13819477
2763 END IF;
2764 END IF;
2765
2766 IF p10(I) < I and p10(I) not in (-1,0) THEN
2767 IF lt_inclu_tax_tab(P10(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P10(I)) IS NOT NULL then --mmurtuza for bug 13819477
2768 vamt := vamt + nvl(tax_amt_tab(p10(I)),0);
2769 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
2770 end if; --mmurtuza for bug 13819477
2771 ELSIF p10(I) = 0 THEN
2772 IF lt_inclu_tax_tab(I) = 'Y' then -- bug 13819477
2773 vamt := vamt + bsln_amt;
2774 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --Add by Kevin Cheng for inclusive tax Dec 11, 2007
2775 else
2776 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount; -- bug 13819477
2777 END IF;
2778 END IF;
2779
2780 -- END BUG 5228046
2781
2782 v_tax_amt := v_tax_amt + (vamt * (tax_rate_tab(I)/100));
2783 base_tax_amt_tab(I) := vamt;
2784 base_tax_amount_nr_tab(I) := ln_vamt_nr; /*9382657*/
2785 tax_amt_tab(I) := nvl(tax_amt_tab(I),0) + v_tax_amt;
2786
2787 --Add by Kevin Cheng for inclusive tax Dec 11, 2007
2788 ---------------------------------------------------
2789 ln_tax_amt_nr := ln_tax_amt_nr + (ln_vamt_nr * (tax_rate_tab(I)/100));
2790 lt_tax_amt_non_rate_tab(I) := NVL(lt_tax_amt_non_rate_tab(I),0) + ln_tax_amt_nr; -- tax inclusive
2791 --lt_tax_amt_rate_tax_tab(i) := tax_amt_tab(I);
2792 IF lt_inclu_tax_tab(I) = 'Y' then
2793 lt_tax_amt_rate_tax_tab(I) := tax_amt_tab(I); --mmurtuza for bug 13473763
2794 END IF;
2795 ln_tax_amt_nr := 0;
2796 ln_vamt_nr := 0;
2797 ---------------------------------------------------
2798
2799 vamt := 0;
2800 v_tax_amt := 0;
2801 END IF;
2802 ELSE
2803 tax_amt_tab(I) := 0;
2804 base_tax_amt_tab(I) := 0;
2805 base_tax_amount_nr_tab(I) := 0; /*9382657*/
2806 END IF;
2807 --dbms_output.put_line( '2 tax_amt_tab('||i||') = '||tax_amt_tab(i)
2808 -- ||', base_tax_amt_tab = '||base_tax_amt_tab(i) );
2809 END LOOP;
2810
2811 FOR I in 1..row_count LOOP
2812 IF end_date_tab( I ) <> 0 THEN
2813 IF tax_rate_tab(I) <> 0 THEN
2814 IF p1(I) > I THEN
2815 vamt := vamt + nvl(tax_amt_tab(p1(I)),0);
2816 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
2817 END IF;
2818 IF p2(I) > I THEN
2819 vamt := vamt + nvl(tax_amt_tab(p2(I)),0);
2820 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
2821 END IF;
2822 IF p3(I) > I THEN
2823 vamt := vamt + nvl(tax_amt_tab(p3(I)),0);
2824 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
2825 END IF;
2826 IF p4(I) > I THEN
2827 vamt := vamt + nvl(tax_amt_tab(p4(I)),0);
2828 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
2829 END IF;
2830 IF p5(I) > I THEN
2831 vamt := vamt + nvl(tax_amt_tab(p5(I)),0);
2832 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
2833 END IF;
2834
2835
2836 -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
2837 -- START BUG 5228046
2838
2839 IF p6(I) > I THEN
2840 vamt := vamt + nvl(tax_amt_tab(p6(I)),0);
2841 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
2842 END IF;
2843 IF p7(I) > I THEN
2844 vamt := vamt + nvl(tax_amt_tab(p7(I)),0);
2845 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
2846 END IF;
2847 IF p8(I) > I THEN
2848 vamt := vamt + nvl(tax_amt_tab(p8(I)),0);
2849 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
2850 END IF;
2851 IF p9(I) > I THEN
2852 vamt := vamt + nvl(tax_amt_tab(p9(I)),0);
2853 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
2854 END IF;
2855 IF p10(I) > I THEN
2856 vamt := vamt + nvl(tax_amt_tab(p10(I)),0);
2857 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
2858 END IF;
2859
2860 -- END BUG 5228046
2861
2862
2863 base_tax_amt_tab(I) := vamt;
2864 base_tax_amount_nr_tab(I) := ln_vamt_nr; /*9382657*/
2865 v_tax_amt := v_tax_amt + (vamt * (tax_rate_tab(I)/100));
2866 IF vamt <> 0 THEN
2867 base_tax_amt_tab(I) := base_tax_amt_tab(I) + vamt;
2868 END IF;
2869 tax_amt_tab(I) := nvl(tax_amt_tab(I),0) + v_tax_amt;
2870 --Add by Kevin Cheng for inclusive tax Dec 11, 2007
2871 ---------------------------------------------------
2872 ln_tax_amt_nr := ln_tax_amt_nr + (ln_vamt_nr * (tax_rate_tab(I)/100)); -- tax inclusive
2873 lt_tax_amt_non_rate_tab(I) := NVL(lt_tax_amt_non_rate_tab(I),0) + ln_tax_amt_nr ; -- tax inclusive
2874 lt_tax_amt_rate_tax_tab(i) := tax_amt_tab(I);
2875 ln_vamt_nr := 0;
2876 ln_tax_amt_nr := 0;
2877 ---------------------------------------------------
2878 vamt := 0;
2879 v_tax_amt := 0;
2880 END IF;
2881 ELSE
2882 base_tax_amt_tab(I) := vamt;
2883 base_tax_amount_nr_tab(I) := ln_vamt_nr; /*9382657*/
2884 tax_amt_tab(I) := 0;
2885 END IF;
2886 --dbms_output.put_line( '3 tax_amt_tab('||i||') = '||tax_amt_tab(i)
2887 -- ||', base_tax_amt_tab = '||base_tax_amt_tab(i) );
2888 END LOOP;
2889
2890 FOR counter IN 1 .. max_iter LOOP
2891 vamt := 0;
2892 v_tax_amt := 0;
2893 ln_vamt_nr:= 0; --added by Kevin Cheng for inclusive tax Dec 11, 2007
2894 ln_tax_amt_nr:=0; --added by Kevin Cheng for inclusive tax Dec 11, 2007
2895
2896 FOR i IN 1 .. row_count LOOP
2897 IF tax_rate_tab( i ) <> 0 AND end_date_tab( I ) <> 0 THEN -- modified on 11-10-2k by anuradha.
2898 IF tax_type_tab( I ) = 1 THEN
2899 --Comment out by Kevin Cheng for inclusive tax Dec 11, 2007
2900 /*v_amt := p_assessable_value;*/
2901 --Added by Kevin Cheng for inclusive tax Dec 11, 2007
2902 ------------------------------------------------
2903 IF ln_assessable_value =1
2904 THEN
2905 v_amt:=1;
2906 ln_bsln_amt_nr :=0;
2907 ELSE
2908 v_amt :=0;
2909 ln_bsln_amt_nr :=ln_assessable_value;
2910 END IF;
2911 ------------------------------------------------
2912 ELSIF tax_type_tab(I) = 4 THEN
2913 --Comment out by Kevin Cheng for inclusive tax Dec 11, 2007
2914 /*v_amt := p_vat_assess_value;*/
2915 --Added by Kevin Cheng for inclusive tax Dec 11, 2007
2916 ------------------------------------------------
2917 IF ln_vat_assessable_value =1
2918 THEN
2919 v_amt:= 1;
2920 ln_bsln_amt_nr := 0;
2921 ELSE
2922 v_amt := 0;
2923 ln_bsln_amt_nr := ln_vat_assessable_value;
2924 END IF;
2925
2926 --Added by zhiwei for bug10043656 GST enhancement 2010/09/14 begin
2927 -------------------------------------------------
2928 /*Start Commenting by mmurtuza for removing GST Changes*/
2929 /*ELSIF tax_type_tab(I) = 7 THEN
2930 IF ln_gst_assessable_value =1
2931 THEN
2932 v_amt:= 1;
2933 ln_bsln_amt_nr := 0;
2934 ELSE
2935 v_amt := 0;
2936 ln_bsln_amt_nr := ln_gst_assessable_value;
2937 END IF;*/
2938 /*End Commenting by mmurtuza for removing GST Changes*/
2939 -------------------------------------------------
2940 --Added by zhiwei for bug10043656 GST enhancement 2010/09/14 end
2941
2942 ELSIF tax_type_tab(I) = 6 THEN
2943 v_amt := 0;
2944 ln_bsln_amt_nr := 0;
2945 ------------------------------------------------
2946 ELSE
2947 IF p_assessable_value IN ( 0, -1 ) OR tax_type_tab( I ) <> 1 THEN
2948 --Comment out by Kevin Cheng for inclusive tax Dec 11, 2007
2949 /*v_amt := p_tax_amount;*/
2950 v_amt:=1; --Added by Kevin Cheng for inclusive tax Dec 11, 2007
2951 ln_bsln_amt_nr :=0; --Added by Kevin Cheng for inclusive tax Dec 11, 2007
2952 END IF;
2953 END IF;
2954 IF p1( i ) <> -1 THEN
2955 IF p1( i ) <> 0 THEN
2956 IF lt_inclu_tax_tab(P1(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P1(I)) IS NOT NULL then -- bug 13819477
2957 vamt := vamt + tax_amt_tab( p1( I ) );
2958 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
2959 end if; --bug 13819477
2960 ELSIF p1(i) = 0 THEN
2961 IF lt_inclu_tax_tab(I) = 'Y' then -- bug 13819477
2962 vamt := vamt + v_amt;
2963 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by Kevin Cheng for inclusive tax Dec 11, 2007
2964 else
2965 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount; --bug 13819477
2966 END IF;
2967 END IF;
2968 END IF;
2969 IF p2( i ) <> -1 THEN
2970 IF p2( i ) <> 0 THEN
2971 IF lt_inclu_tax_tab(P2(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P2(I)) IS NOT NULL then -- bug 13819477
2972 vamt := vamt + tax_amt_tab( p2( I ) );
2973 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
2974 end if; --bug 13819477
2975 ELSIF p2(i) = 0 THEN
2976 IF lt_inclu_tax_tab(I) = 'Y' then -- bug 13819477
2977 vamt := vamt + v_amt;
2978 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by Kevin Cheng for inclusive tax Dec 11, 2007
2979 else
2980 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount; --bug 13819477
2981 END IF;
2982 END IF;
2983 END IF;
2984 IF p3( i ) <> -1 THEN
2985 IF p3( i ) <> 0 THEN
2986 IF lt_inclu_tax_tab(P3(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P3(I)) IS NOT NULL then -- bug 13819477
2987 vamt := vamt + tax_amt_tab( p3( I ) );
2988 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
2989 end if; --bug 13819477
2990 ELSIF p3(i) = 0 THEN
2991 IF lt_inclu_tax_tab(I) = 'Y' then -- bug 13819477
2992 vamt := vamt + v_amt;
2993 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by Kevin Cheng for inclusive tax Dec 11, 2007
2994 else
2995 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount; --bug 13819477
2996 END IF;
2997 END IF;
2998 END IF;
2999 IF p4( i ) <> -1 THEN
3000 IF p4( i ) <> 0 THEN
3001 IF lt_inclu_tax_tab(P4(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P4(I)) IS NOT NULL then -- bug 13819477
3002 vamt := vamt + tax_amt_tab( p4( i ) );
3003 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
3004 end if; --bug 13819477
3005 ELSIF p4(i) = 0 THEN
3006 IF lt_inclu_tax_tab(I) = 'Y' then -- bug 13819477
3007 vamt := vamt + v_amt;
3008 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by Kevin Cheng for inclusive tax Dec 11, 2007
3009 else
3010 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount; --bug 13819477
3011 END IF;
3012 END IF;
3013 END IF;
3014 IF p5( i ) <> -1 THEN
3015 IF p5( i ) <> 0 THEN
3016 IF lt_inclu_tax_tab(P5(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P5(I)) IS NOT NULL then -- bug 13819477
3017 vamt := vamt + tax_amt_tab( p5( i ) );
3018 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
3019 end if; --bug 13819477
3020 ELSIF p5(i) = 0 THEN
3021 IF lt_inclu_tax_tab(I) = 'Y' then -- bug 13819477
3022 vamt := vamt + v_amt;
3023 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by Kevin Cheng for inclusive tax Dec 11, 2007
3024 else
3025 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount; --bug 13819477
3026 END IF;
3027 END IF;
3028 END IF;
3029
3030
3031 -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
3032 -- START BUG 5228046
3033
3034 IF p6( i ) <> -1 THEN
3035 IF p6( i ) <> 0 THEN
3036 IF lt_inclu_tax_tab(P6(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P6(I)) IS NOT NULL then -- bug 13819477
3037 vamt := vamt + tax_amt_tab( p6( I ) );
3038 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
3039 end if; --bug 13819477
3040 ELSIF p6(i) = 0 THEN
3041 IF lt_inclu_tax_tab(I) = 'Y' then -- bug 13819477
3042 vamt := vamt + v_amt;
3043 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by Kevin Cheng for inclusive tax Dec 11, 2007
3044 else
3045 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount; --bug 13819477
3046 END IF;
3047 END IF;
3048 END IF;
3049 IF p7( i ) <> -1 THEN
3050 IF p7( i ) <> 0 THEN
3051 IF lt_inclu_tax_tab(P7(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P7(I)) IS NOT NULL then -- bug 13819477
3052 vamt := vamt + tax_amt_tab( p7( I ) );
3053 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
3054 end if; --bug 13819477
3055 ELSIF p7(i) = 0 THEN
3056 IF lt_inclu_tax_tab(I) = 'Y' then -- bug 13819477
3057 vamt := vamt + v_amt;
3058 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by Kevin Cheng for inclusive tax Dec 11, 2007
3059 else
3060 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount; --bug 13819477
3061 END IF;
3062 END IF;
3063 END IF;
3064 IF p8( i ) <> -1 THEN
3065 IF p8( i ) <> 0 THEN
3066 IF lt_inclu_tax_tab(P8(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P8(I)) IS NOT NULL then -- bug 13819477
3067 vamt := vamt + tax_amt_tab( p8( I ) );
3068 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
3069 end if; --bug 13819477
3070 ELSIF p8(i) = 0 THEN
3071 IF lt_inclu_tax_tab(I) = 'Y' then -- bug 13819477
3072 vamt := vamt + v_amt;
3073 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by Kevin Cheng for inclusive tax Dec 11, 2007
3074 else
3075 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount; --bug 13819477
3076 END IF;
3077 END IF;
3078 END IF;
3079 IF p9( i ) <> -1 THEN
3080 IF p9( i ) <> 0 THEN
3081 IF lt_inclu_tax_tab(P9(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P9(I)) IS NOT NULL then -- bug 13819477
3082 vamt := vamt + tax_amt_tab( p9( i ) );
3083 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
3084 end if; --bug 13819477
3085 ELSIF p9(i) = 0 THEN
3086 IF lt_inclu_tax_tab(I) = 'Y' then -- bug 13819477
3087 vamt := vamt + v_amt;
3088 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by Kevin Cheng for inclusive tax Dec 11, 2007
3089 else
3090 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount; --bug 13819477
3091 END IF;
3092 END IF;
3093 END IF;
3094 IF p10( i ) <> -1 THEN
3095 IF p10( i ) <> 0 THEN
3096 IF lt_inclu_tax_tab(P10(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P10(I)) IS NOT NULL then -- bug 13819477
3097 vamt := vamt + tax_amt_tab( p10( i ) );
3098 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
3099 end if; --bug 13819477
3100 ELSIF p10(i) = 0 THEN
3101 IF lt_inclu_tax_tab(I) = 'Y' then -- bug 13819477
3102 vamt := vamt + v_amt;
3103 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr; --added by Kevin Cheng for inclusive tax Dec 11, 2007
3104 else
3105 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + p_tax_amount; --bug 13819477
3106 END IF;
3107 END IF;
3108 END IF;
3109
3110 -- END BUG 5228046
3111
3112 base_tax_amt_tab(I) := vamt;
3113 base_tax_amount_nr_tab(I) := ln_vamt_nr; /*9382657*/
3114 tax_target_tab(I) := vamt;
3115
3116 v_func_tax_amt := v_tax_amt +( vamt * ( tax_rate_tab( i )/100));
3117 v_tax_amt := v_tax_amt + ( vamt * ( tax_rate_tab( i )/100));
3118 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
3119
3120 ELSIF tax_rate_tab(I) = 0 THEN
3121 base_tax_amt_tab(I) := tax_amt_tab(i);
3122 v_tax_amt := tax_amt_tab( i );
3123 ln_tax_amt_nr:=lt_tax_amt_non_rate_tab(i); --Add by Kevin Cheng for inclusive tax Jan 08, 2008
3124 tax_target_tab(I) := v_tax_amt;
3125 ELSIF end_date_tab( I ) = 0 THEN
3126 tax_amt_tab(I) := 0;
3127 base_tax_amt_tab(I) := 0;
3128 base_tax_amount_nr_tab(I) := 0; /*9382657*/
3129 tax_target_tab(I) := 0;
3130 END IF;
3131
3132 tax_amt_tab( I ) := NVL( v_tax_amt, 0 );
3133 lt_tax_amt_non_rate_tab(I):=ln_tax_amt_nr; --Add by Kevin Cheng for inclusive tax Jan 08, 2008
3134 func_tax_amt_tab(I) := NVL(v_func_tax_amt,0);
3135 IF counter = max_iter THEN
3136 IF end_date_tab(I) = 0 THEN
3137 tax_amt_tab( i ) := 0;
3138 func_tax_amt_tab(i) := 0;
3139 END IF;
3140 END IF;
3141 --dbms_output.put_line( '4 tax_amt_tab('||i||') = '||tax_amt_tab(i)
3142 --||', func_tax_amt_tab = '||func_tax_amt_tab(i)
3143 --||', base_tax_amt_tab = '||base_tax_amt_tab(i) );
3144
3145 lt_tax_amt_rate_tax_tab(i) := tax_amt_tab(I); --Add by Kevin Cheng for inclusive tax Dec 11, 2007
3146
3147 vamt := 0;
3148 v_amt := 0;
3149 v_tax_amt := 0;
3150 v_func_tax_amt := 0;
3151 ln_vamt_nr :=0; --added by Kevin Cheng for inclusive tax Dec 11, 2007
3152 ln_tax_amt_nr:=0; --added by Kevin Cheng for inclusive tax Dec 11, 2007
3153 END LOOP;
3154 END LOOP;
3155
3156 --Added by Kevin Cheng for inclusive tax Dec 11, 2007
3157 ---------------------------------------------------------------------------------------
3158 FOR I IN 1 .. ROW_COUNT
3159 LOOP
3160 IF lt_inclu_tax_tab(I) = 'Y'
3161 THEN
3162 ln_total_tax_per_rupee := ln_total_tax_per_rupee + nvl(lt_tax_amt_rate_tax_tab(I),0) ;
3163 ln_total_non_rate_tax := ln_total_non_rate_tax + nvl(lt_tax_amt_non_rate_tab(I),0);
3164 END IF;
3165 END LOOP; --FOR I IN 1 .. ROW_COUNT
3166
3167 ln_total_tax_per_rupee := ln_total_tax_per_rupee + 1;
3168
3169 IF ln_total_tax_per_rupee <> 0
3170 THEN
3171 ln_exclusive_price := (NVL(p_tax_amount,0) - ln_total_non_rate_tax ) / ln_total_tax_per_rupee;
3172 END IF;
3173
3174 FOR i in 1 .. row_count
3175 LOOP
3176 --tax_amt_tab (i) := (lt_tax_amt_rate_tax_tab(I) * ln_exclusive_price ) + lt_tax_amt_non_rate_tab(I); --mmurtuza for bug 13819477
3177 --tax_amt_tab(I) := round(tax_amt_tab(I) ,lt_round_factor_tab(I)); --mmurtuza for bug 13819477
3178 /*Bug 9382657 - Multiply Line Amount with the Rate of the precedences*/
3179 --base_tax_amt_tab(I):= ln_exclusive_price * base_tax_amt_tab(I) + base_tax_amount_nr_tab(I); /*Bug 9382657*/ --mmurtuza for bug 13819477
3180
3181 /*Start Additions by mmurtuza for bug 13819477 */
3182 tax_amt_tab (i):=0;
3183 IF lt_inclu_tax_tab(I) = 'Y' THEN
3184 IF (tax_type_tab(I) = 1 and ln_assessable_value>1) OR
3185 (tax_type_tab(I) = 4 and ln_vat_assessable_value> 1) THEN
3186
3187 IF tax_type_tab(I) = 1 and ln_assessable_value >1 Then
3188 ln_amount:= ln_assessable_value;
3189 ELSIF tax_type_tab(I) = 4 and ln_vat_assessable_value >1 Then
3190 ln_amount:= ln_vat_assessable_value;
3191 END IF;
3192
3193 IF P1(I) < I AND p1(I) NOT IN (-1, 0) then
3194 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P1(i))* tax_rate_tab(i)/100;
3195 ELSIF P1(I)=0 Then
3196 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3197 END IF;
3198
3199 IF P2(I) < I AND p2(I) NOT IN (-1, 0) then
3200 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P2(i))* tax_rate_tab(i)/100;
3201 ELSIF P2(I)=0 Then
3202 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3203 END IF;
3204
3205 IF P3(I) < I AND p3(I) NOT IN (-1, 0) then
3206 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P3(i))* tax_rate_tab(i)/100;
3207 ELSIF P3(I)=0 Then
3208 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3209 END IF;
3210
3211 IF P4(I) < I AND p4(I) NOT IN (-1, 0) then
3212 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P4(i))* tax_rate_tab(i)/100;
3213 ELSIF P4(I)=0 Then
3214 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3215 END IF;
3216
3217 IF P5(I) < I AND p5(I) NOT IN (-1, 0) then
3218 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P5(i))* tax_rate_tab(i)/100;
3219 ELSIF P5(I)=0 Then
3220 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3221 END IF;
3222
3223 IF P6(I) < I AND p6(I) NOT IN (-1, 0) then
3224 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P6(i))* tax_rate_tab(i)/100;
3225 ELSIF P6(I)=0 Then
3226 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3227 END IF;
3228
3229 IF P7(I) < I AND p7(I) NOT IN (-1, 0) then
3230 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P7(i))* tax_rate_tab(i)/100;
3231 ELSIF P7(I)=0 Then
3232 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3233 END IF;
3234
3235 IF P8(I) < I AND p8(I) NOT IN (-1, 0) then
3236 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P8(i))* tax_rate_tab(i)/100;
3237 ELSIF P8(I)=0 Then
3238 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3239 END IF;
3240
3241 IF P9(I) < I AND p9(I) NOT IN (-1, 0) then
3242 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P9(i))* tax_rate_tab(i)/100;
3243 ELSIF P9(I)=0 Then
3244 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3245 END IF;
3246
3247 IF P10(I) < I AND p10(I) NOT IN (-1, 0) then
3248 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P10(i))* tax_rate_tab(i)/100;
3249 ELSIF P10(I)=0 Then
3250 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3251 END IF;
3252 ELSE
3253 tax_amt_tab (i) := (lt_tax_amt_rate_tax_tab(I) * ln_exclusive_price ) + lt_tax_amt_non_rate_tab(I);
3254 END IF;
3255 ELSE
3256 IF (tax_type_tab(I) = 1 and ln_assessable_value >1) OR
3257 (tax_type_tab(I) = 4 and ln_vat_assessable_value > 1) THEN
3258
3259 IF tax_type_tab(I) = 1 and ln_assessable_value >1 Then
3260 ln_amount:= ln_assessable_value;
3261 ELSIF tax_type_tab(I) = 4 and ln_vat_assessable_value >1 Then
3262 ln_amount:= ln_vat_assessable_value;
3263 END IF;
3264 ELSE ln_amount:= p_tax_amount;
3265 END IF;
3266 IF P1(I) < I AND p1(I) NOT IN (-1, 0) then
3267 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P1(i))* tax_rate_tab(i)/100;
3268 ELSIF P1(I)=0 Then
3269 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3270 END IF;
3271
3272 IF P2(I) < I AND p2(I) NOT IN (-1, 0) then
3273 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P2(i))* tax_rate_tab(i)/100;
3274 ELSIF P2(I)=0 Then
3275 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3276 END IF;
3277
3278 IF P3(I) < I AND p3(I) NOT IN (-1, 0) then
3279 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P3(i))* tax_rate_tab(i)/100;
3280 ELSIF P3(I)=0 Then
3281 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3282 END IF;
3283
3284 IF P4(I) < I AND p4(I) NOT IN (-1, 0) then
3285 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P4(i))* tax_rate_tab(i)/100;
3286 ELSIF P4(I)=0 Then
3287 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3288 END IF;
3289
3290 IF P5(I) < I AND p5(I) NOT IN (-1, 0) then
3291 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P5(i))* tax_rate_tab(i)/100;
3292 ELSIF P5(I)=0 Then
3293 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3294 END IF;
3295
3296 IF P6(I) < I AND p6(I) NOT IN (-1, 0) then
3297 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P6(i))* tax_rate_tab(i)/100;
3298 ELSIF P6(I)=0 Then
3299 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3300 END IF;
3301
3302 IF P7(I) < I AND p7(I) NOT IN (-1, 0) then
3303 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P7(i))* tax_rate_tab(i)/100;
3304 ELSIF P7(I)=0 Then
3305 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3306 END IF;
3307
3308 IF P8(I) < I AND p8(I) NOT IN (-1, 0) then
3309 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P8(i))* tax_rate_tab(i)/100;
3310 ELSIF P8(I)=0 Then
3311 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3312 END IF;
3313
3314 IF P9(I) < I AND p9(I) NOT IN (-1, 0) then
3315 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P9(i))* tax_rate_tab(i)/100;
3316 ELSIF P9(I)=0 Then
3317 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3318 END IF;
3319
3320 IF P10(I) < I AND p10(I) NOT IN (-1, 0) then
3321 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P10(i))* tax_rate_tab(i)/100;
3322 ELSIF P10(I)=0 Then
3323 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
3324 END IF;
3325 END IF;
3326 ------------------------------------------------------------------
3327 -- Added by Shyan for bug 10358786 on 28-Jan-2011 , End
3328 func_tax_amt_tab(i):=tax_amt_tab (i); --Added by walton for bug#9288016
3329 tax_amt_tab(I) := round(tax_amt_tab(I) ,lt_round_factor_tab(I));
3330 base_tax_amt_tab(I):= ln_exclusive_price * base_tax_amt_tab(I)
3331 + base_tax_amount_nr_tab(I); --added by Xiao Lv for bug#8789761 on 30-Oct-09
3332
3333 /*End Additions by mmurtuza for bug 13819477*/
3334
3335 END LOOP; --FOR i in 1 .. row_count
3336 --------------------------------------------------------------------------------------------------------
3337
3338 FOR rec in c_tax_lines(p_header_id, p_line_id) LOOP
3339 v_rounded_tax := ROUND(nvl(tax_amt_tab(rec.lno),0), rec.rounding_factor);
3340 IF tax_type_tab(rec.lno) <> 2 THEN
3341 v_tax_amt := v_tax_amt + v_rounded_tax;
3342 END IF;
3343 --dbms_output.put_line( '5 tax_amt_tab('||rec.lno||') = '||tax_amt_tab(rec.lno)
3344 -- ||', func_tax_amt_tab = '||func_tax_amt_tab(rec.lno)
3345 -- ||', base_tax_amt_tab = '||base_tax_amt_tab(rec.lno) );
3346
3347 UPDATE JAI_OM_OE_SO_TAXES
3348 SET tax_amount = v_rounded_tax,
3349 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)),
3350 func_tax_amount = nvl(func_tax_amt_tab(rec.lno),0) * p_currency_conv_factor,
3351 last_update_date= p_last_updated_date,
3352 last_updated_by = p_last_updated_by,
3353 last_update_login = p_last_update_login
3354 WHERE line_id = p_line_id
3355 AND header_id = p_header_id
3356 AND tax_line_no = rec.lno;
3357
3358 END LOOP;
3359
3360 p_tax_amount := nvl(v_tax_amt,0);
3361 EXCEPTION
3362 WHEN OTHERS THEN
3363 p_tax_amount := null;
3364 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
3365 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
3366 app_exception.raise_exception;
3367 END recalculate_oe_taxes;
3368
3369 PROCEDURE recalculate_excise_taxes ( errbuf OUT NOCOPY VARCHAR2
3370 , retcode OUT NOCOPY VARCHAR2
3371 , pn_org_id NUMBER /* This parameter would no more be used after application of the bug 5490479- Aiyer, */
3372 , pn_start_order NUMBER
3373 , pn_end_order NUMBER
3374 , pn_order_type_id NUMBER
3375 , pn_ship_from_org_id NUMBER
3376 )
3377 AS
3378 /*****************************************************************
3379 * Enhancement Bug#2152709, 06/06/2002 Vijay Shankar
3380 * FILENAME: ja_in_assessable_price_change.sql
3381 *
3382 * DESCRIPTION:
3383 * This SQL script is used to Recalculate the Excise Duty if there
3384 * is any change in the asssessable price given in the price list
3385 * attached to the customer
3386 *
3387 * PARAMETERS:
3388 * 1 p_org_id
3389 * 2 p_start_order
3390 * 3 p_end_order
3391 * 4 p_order_type_id
3392 * 5 p_ship_from_org_id
3393 *******************************************************************/
3394
3395 v_header_id oe_order_lines_all.header_id%TYPE;
3396 v_line_id oe_order_lines_all.line_id%TYPE;
3397 v_line_number oe_order_lines_all.line_number%TYPE;
3398 v_shipment_number oe_order_lines_all.shipment_number%TYPE;
3399 v_ship_to_site_use_id oe_order_lines_all.ship_to_ORG_id%TYPE;
3400 v_inventory_item_id oe_order_lines_all.inventory_item_id%TYPE;
3401 v_line_quantity oe_order_lines_all.ordered_quantity%TYPE;
3402 v_uom_code oe_order_lines_all.order_quantity_uom%TYPE;
3403 v_warehouse_id oe_order_lines_all.SHIP_FROM_ORG_ID%TYPE;
3404
3405 v_last_update_date oe_order_lines_all.last_update_date%TYPE;
3406 v_last_updated_by oe_order_lines_all.last_updated_by%TYPE;
3407 v_last_update_login oe_order_lines_all.last_update_login%TYPE;
3408
3409 /* commented by cbabu for Bug#2496481
3410 v_created_by oe_order_lines_all.created_by%TYPE;
3411 v_creation_date oe_order_lines_all.creation_date%TYPE;
3412 v_transaction_name varchar2(30) := 'SO_LINES_UPDATE';
3413 v_original_system_line_ref oe_order_lines_all.ORIG_SYS_LINE_REF%TYPE;
3414 v_original_line_reference oe_order_lines_all.ORIG_SYS_LINE_REF%TYPE;
3415 v_split_from_line_id oe_order_lines_all.split_from_line_id%TYPE;
3416 v_source_document_id oe_order_lines_all.source_document_id%TYPE;
3417 v_source_document_line_id oe_order_lines_all.source_document_line_id%TYPE;
3418 v_source_order_category varchar2(30);
3419 v_source_header_id Number;
3420 v_source_id Number;
3421 */
3422 v_source_document_type_id oe_order_lines_all.source_document_type_id%TYPE;
3423 v_Line_Category_Code oe_order_lines_all.line_category_code%TYPE;
3424 v_reference_line_id oe_order_lines_all.reference_line_id%TYPE; -- used for return lines
3425 v_item_type_code oe_order_lines_all.item_type_code%TYPE;
3426 v_unit_selling_price oe_order_lines_all.unit_selling_price%TYPE;
3427 v_operating_id oe_order_lines_all.org_id%TYPE;
3428 v_old_assessable_value JAI_OM_OE_SO_LINES.assessable_value%TYPE;
3429 v_line_amount NUMBER := 0;
3430
3431 v_original_system_reference VARCHAR2(50);
3432 v_customer_id Number;
3433 v_address_id Number;
3434 v_price_list_id Number;
3435 v_org_id Number;
3436 v_order_number Number;
3437 v_conv_type_code varchar2(30);
3438 v_conv_rate Number;
3439 v_conv_date Date;
3440 v_conv_factor Number;
3441 v_set_of_books_id Number;
3442 v_tax_category_id Number;
3443 v_order_category varchar2(30);
3444 v_assessable_value Number;
3445 v_assessable_amount Number;
3446 v_price_list_uom_code Varchar2(3);
3447 v_converted_rate Number;
3448 v_date_ordered Date;
3449 v_ordered_date Date;
3450
3451 v_assessable_value_date DATE; --Commented for File.sql.35 := SYSDATE; -- cbabu for Bug#2496494
3452
3453 v_line_tax_amount Number := 0;
3454 v_conversion_rate Number := 0;
3455 v_currency_code gl_sets_of_books.currency_code%TYPE;
3456 v_order_source_type varchar2(240);
3457
3458 CURSOR address_cur(p_ship_to_site_use_id IN Number) IS
3459 SELECT nvl(cust_acct_site_id , 0) address_id
3460 FROM hz_cust_site_uses_all A -- Removed ra_site_uses_all for Bug# 4434287
3461 WHERE A.site_use_id = p_ship_to_site_use_id; /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
3462 --WHERE A.site_use_id = NVL(p_ship_to_site_use_id,0);
3463
3464 /* Bug 5243532. Added by Lakshmi Gopalsami
3465 * Removed the cursor set_of_books_cur and implemented using caching logic.
3466 */
3467
3468 /* commented by cbabu for Bug#2496481
3469 CURSOR order_tax_amount_Cur (p_header_id Number, p_line_id Number) IS
3470 Select sum(a.tax_amount)
3471 From JAI_OM_OE_SO_TAXES a, JAI_CMN_TAXES_ALL b
3472 Where a.Header_ID = p_header_id
3473 and a.line_id = p_line_id
3474 and b.tax_id = a.tax_id
3475 and b.tax_type != 'TDS';
3476 */
3477
3478 CURSOR Get_Assessable_Value_Cur(p_customer_id Number,p_address_id Number, p_inventory_item_id Number,
3479 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
3480 SELECT b.operand list_price,
3481 c.product_uom_code list_price_uom_code
3482 FROM JAI_CMN_CUS_ADDRESSES a, QP_LIST_LINES b, qp_pricing_attributes c
3483 WHERE a.customer_id = p_customer_id
3484 AND a.address_id = p_address_id
3485 AND a.price_list_id = b.LIST_header_ID
3486 AND c.list_line_id = b.list_line_id
3487 AND c.product_attr_value = p_inventory_item_id /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
3488 AND trunc(p_ordered_date) BETWEEN trunc(nvl( start_date_active, p_ordered_date)) -- cbabu for Bug#2496494
3489 AND trunc(nvl( end_date_active, SYSDATE));
3490
3491 /*
3492 AND c.product_attr_value = TO_CHAR(p_inventory_item_id) --2001/02/14 Manohar Mishra
3493 AND c.product_uom_code = p_uom_code --2001/10/09 Anuradha Parthasarathy
3494 AND TRUNC(NVL(b.end_date_active,SYSDATE)) >= TRUNC(p_ordered_date);
3495 */
3496
3497 CURSOR get_source_id IS
3498 Select order_source_id
3499 From oe_order_headers_all
3500 Where header_id = v_header_id;
3501
3502 CURSOR Get_Order_Source_Type(P_Source_Document_Type_Id Number) is
3503 Select Name
3504 from OE_ORDER_SOURCES
3505 where Order_Source_Id=P_Source_Document_Type_Id;
3506
3507 ----------------------------------------------------------------------------------
3508 CURSOR line_details1(p_order_number1 IN NUMBER, p_order_number2 IN NUMBER, p_ship_from_org_id IN NUMBER,
3509 p_org_id IN NUMBER, p_order_type_id IN NUMBER) IS
3510 SELECT base.header_id, base.line_id, base.line_number, base.SHIPMENT_NUMBER,
3511 base.ship_to_ORG_id, base.inventory_item_id, base.ordered_quantity, base.order_quantity_uom,
3512 base.SHIP_FROM_ORG_ID, base.creation_date, base.created_by, base.last_update_date,
3513 base.last_updated_by, base.last_update_login, base.ORIG_SYS_LINE_REF,
3514 base.Line_Category_Code, base.reference_line_id, base.item_type_code, base.split_from_line_id,
3515 base.unit_selling_price, base.ORG_ID, base.SOURCE_DOCUMENT_ID, base.SOURCE_DOCUMENT_LINE_ID,
3516 base.SOURCE_DOCUMENT_TYPE_ID, ja.assessable_value,
3517 NVL(head.org_id,0) org_id1, head.SOLD_TO_ORG_ID, head.SOURCE_DOCUMENT_ID hsdi, head.order_number,
3518 head.price_list_id, head.ORDER_CATEGORY_CODE, head.ORIG_SYS_DOCUMENT_REF, head.TRANSACTIONAL_CURR_CODE,
3519 head.conversion_type_code, head.conversion_rate, head.CONVERSION_RATE_DATE, nvl(head.ORDERED_DATE, head.creation_date) ordered_date
3520 FROM oe_order_headers_all head, oe_order_lines_all base, JAI_OM_OE_SO_LINES ja
3521 WHERE head.header_id = base.header_id
3522 and base.line_id = ja.line_id
3523 and base.OPEN_FLAG = 'Y'
3524 and base.line_category_code = 'ORDER'
3525 and base.ship_from_org_id = p_ship_from_org_id
3526 and base.org_id = p_org_id
3527 and head.order_type_id = p_order_type_id
3528 and head.order_number between p_order_number1 and p_order_number2;
3529 --and base.flow_status_code IN ( 'ENTERED', 'BOOKED' )
3530
3531 --vijay shankar some important flags
3532 -- OPEN_FLAG, BOOKED_FLAG, CALCELLED_FLAG, FULFILLED_FLAG, SHIPPABLE_FLAG, SHIP_MODEL_COMPLETE_FLAG,
3533 -- AUTHORIZED_TO_SHIP_FLAG, SHIPPING_INTERFACED_FLAG, CALCULATE_PRICE_FLAG, ORDER_CATEGORY(return,order etc)
3534 -- head.FLOW_STATUS_CODE, line.FLOW_STATUS_CODE, ORDER_CATEGORY_CODE, LINE_CATEGORY
3535
3536 CURSOR get_shipped_line(p_line_id IN NUMBER, p_header_id IN NUMBER ) IS
3537 SELECT order_line_id
3538 FROM JAI_OM_WSH_LINES_ALL
3539 WHERE order_line_id = p_line_id
3540 AND order_header_id = p_header_id;
3541
3542 ii NUMBER; -- := 1; Commented for File.Sql.35
3543 p_start_order NUMBER;
3544 p_end_order NUMBER;
3545 p_org_id NUMBER;
3546 p_order_type_id NUMBER;
3547 p_ship_from_org_id NUMBER;
3548
3549 v_check_line NUMBER;
3550
3551 ln_vat_assessable_value NUMBER;
3552 --ln_gst_assessable_value NUMBER; --Added by zhiwei for bug10043656 GST enhancement 2010/09/14 /*Commented by mmurtuza for removing GST Changes*/
3553
3554 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_om_tax_pkg.recalculate_excise_taxes';
3555
3556 /* Bug 5243532. Added by Lakshmi Gopalsami
3557 * Defined variable for implementing caching logic.
3558 */
3559 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
3560
3561
3562
3563 BEGIN
3564 /*-------------------------------------------------------------------------------------------------------------------
3565 S.No Date(DD/MM/YYYY) Author and Details of Changes
3566 ---- -------------- -----------------------------
3567 1 05/08/2002 Vijay Shankar for Bug# 2496481
3568 When new taxes are added to order after tax defaultation, then change the assessable price attached
3569 to the item and run the excise duty recalculation Concurrent, the added taxes are not getting calculated
3570 properly. Base bug 2152709 is made obsolete with this patch and this patch becomes a prerequisite
3571 patch for future patches related to this object.
3572
3573 2 05/08/2002 Vijay Shankar for Bug# 2496494
3574 When price list contains more than one price list lines for the same item, which has end date greater than the ordered_date
3575 or end_date is null then more than one price list lines are picked up by the cursor.
3576
3577 3 30/05/2005 Brathod, For Bug# 4400993
3578 Issue:-
3579 ja_in_assessible_price_change.sql was a pl-sql executable script, which needs to be
3580 a procedure inside a package
3581 Fix:-
3582 Script is migrated into a jai_om_tax_pkg as recalculate_excise_tax procedure
3583 with following input parameters.
3584 errbuf, retcode, pn_org_id, pn_start_order
3585 , pn_end_order, pn_order_type_id, pn_ship_from_org_id
3586 In JAINREPC Concurrent Definition execution mentod modified from
3587 SQL*Plus to PL-SQL Stored Procedure
3588
3589 4 26/05/2005 Ramananda for Bug#4540783. File Version 120.2
3590 While running the concurrent - INDIA - EXCISE DUTY RECALCULATION, system is giving error -
3591 "wrong number or types of arguments in call to 'jai_om_tax_pkg.recalculate_oe_taxes'"
3592 Added a new parameter - p_vat_assess_value and value for the same as NULL,
3593 while calling procedure jai_om_tax_pkg.recalculate_oe_taxes
3594
3595 --------------------------------------------------------------------------------------------------------------------*/
3596 v_assessable_value_date := sysdate;
3597 ii := 1;
3598
3599 FND_FILE.PUT_LINE(FND_FILE.LOG,'STARTED the procedure ' );
3600
3601 /* Added by Brathod for Bug# 4400993*/
3602 /*
3603 || Start of bug 5490479
3604 || Added by aiyer for the bug 5490479
3605 || Get the operating unit (org_id)
3606 */
3607 p_org_id := mo_global.get_current_org_id;
3608 fnd_file.put_line(fnd_file.log, 'Operating unit p_org_id is -> '||p_org_id);
3609 /*End of bug 5490479 */
3610
3611 p_start_order := pn_start_order;
3612 p_end_order := pn_end_order ;
3613 p_order_type_id := pn_order_type_id;
3614 p_ship_from_org_id := pn_ship_from_org_id;
3615 /* End of Bug# 4400993 */
3616
3617 -- added by cbabu for Bug#2496481, start
3618 v_last_update_date := SYSDATE;
3619 v_last_updated_by := FND_GLOBAL.USER_ID;
3620 v_last_update_login := FND_GLOBAL.LOGIN_ID;
3621 -- added by cbabu for Bug#2496481, end
3622
3623 FND_FILE.PUT_LINE(FND_FILE.LOG,' Sl. No., Order Number, Header_id , line_id, line_number'
3624 ||', ship_to_site_use_id, inventory_item_id, line_quantity, uom_code'
3625 ||',warehouse_id, Line_Category_Code, reference_line_id, item_type_code'
3626 ||',original_line_reference, unit_selling_price, operating_id, org_id'
3627 ||',SOLD_TO_ORG_ID, hsdi, price_list_id, ORDER_CATEGORY_CODE, ORIG_SYS_DOCUMENT_REF'
3628 ||',TRANSACTIONAL_CURR_CODE, conversion_type_code, conversion_rate'
3629 ||',CONVERSION_RATE_DATE, ORDERED_DATE');
3630
3631 FOR rec IN line_details1( p_start_order, p_end_order,p_ship_from_org_id, p_org_id, p_order_type_id) LOOP
3632 ------------------------------------------------------------------------
3633 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
3634 ||', '||rec.inventory_item_id|| ', '||rec.ordered_quantity||', '||rec.order_quantity_uom|| ', '||rec.SHIP_FROM_ORG_ID
3635 ||', '||rec.Line_Category_Code||', '||rec.reference_line_id|| ', '||rec.item_type_code
3636 ||', '||rec.ORIG_SYS_LINE_REF||', '||rec.unit_selling_price||', '||rec.org_id
3637 ||', '||rec.org_id1|| ', '||rec.SOLD_TO_ORG_ID||', '||rec.hsdi
3638 ||', '||rec.price_list_id||', '||rec.ORDER_CATEGORY_CODE|| ', '||rec.ORIG_SYS_DOCUMENT_REF||', '||rec.TRANSACTIONAL_CURR_CODE
3639 ||', '||rec.conversion_type_code||', '||rec.conversion_rate||', '||rec.CONVERSION_RATE_DATE||', '||rec.ORDERED_DATE);
3640 ------------------------------------------------------------------------
3641
3642 OPEN get_shipped_line(rec.line_id,rec.header_id);
3643 FETCH get_shipped_line INTO v_check_line;
3644 CLOSE get_shipped_line;
3645
3646 IF v_check_line IS NULL THEN --- zzz
3647
3648 v_header_id := rec.header_id;
3649 v_line_id := rec.line_id;
3650 v_line_number := rec.line_number;
3651 v_shipment_number := rec.shipment_number;
3652 v_ship_to_site_use_id := rec.SHIP_TO_ORG_ID;
3653 v_inventory_item_id := rec.inventory_item_id;
3654 v_line_quantity := rec.ordered_quantity;
3655 v_uom_code := rec.order_quantity_uom;
3656 v_warehouse_id := rec.SHIP_FROM_ORG_ID;
3657
3658 /* commented for cbabu for Bug#2496481
3659 v_creation_date := rec.creation_date;
3660 v_created_by := rec.created_by;
3661 v_last_update_date := rec.last_update_date;
3662 v_last_updated_by := rec.last_updated_by;
3663 v_last_update_login := rec.last_update_login;
3664 v_original_system_line_ref := rec.ORIG_SYS_LINE_REF;
3665 v_original_line_reference := rec.ORIG_SYS_LINE_REF;
3666 v_split_from_line_id := null;
3667 v_source_document_id := null;
3668 v_source_document_line_id := null;
3669 */
3670
3671 v_source_document_type_id := rec.source_document_type_id;
3672 v_Line_Category_Code := rec.Line_Category_Code;
3673 v_reference_line_id := rec.reference_line_id;
3674 v_item_type_code := rec.item_type_code;
3675 v_unit_selling_price := rec.unit_selling_price;
3676 v_operating_id := rec.org_id;
3677 v_old_assessable_value := rec.assessable_value;
3678 v_line_amount := nvl(v_line_quantity,0) * nvl(v_unit_selling_price,0);
3679
3680 FND_FILE.PUT_LINE(FND_FILE.LOG,ii|| ', 1 v_line_amount = ' ||v_line_amount) ;
3681
3682 v_org_id := rec.org_id1;
3683 v_customer_id := rec.sold_to_org_id;
3684
3685 /* commented for cbabu for Bug#2496481
3686 v_source_header_id := rec.hsdi;
3687 */
3688
3689 v_order_number := rec.order_number;
3690 v_price_list_id := rec.price_list_id;
3691 v_order_category := rec.order_category_code;
3692 v_original_system_reference := rec.orig_sys_document_ref;
3693 v_currency_code := rec.transactional_curr_code;
3694 v_conv_type_code := rec.conversion_type_code;
3695 v_conv_rate := rec.conversion_rate;
3696 v_conv_date := rec.conversion_rate_date;
3697 v_date_ordered := rec.ordered_date;
3698
3699 IF v_conv_date IS NULL THEN
3700 v_conv_date := v_date_ordered;
3701 END IF;
3702
3703 Open Get_Order_Source_Type(v_source_document_type_id);
3704 Fetch Get_Order_Source_Type Into v_order_source_type;
3705 Close Get_Order_Source_Type;
3706
3707 IF v_item_type_code = 'STANDARD'
3708 AND (v_reference_line_id IS NOT NULL or v_order_category = 'RETURN')
3709 -- AND nvl(v_source_document_type_id,0) != 2
3710 AND UPPER(v_order_source_type) <> 'COPY'
3711 THEN --- aaa -- if execution enters here, it means it is a return order which need not be considered for recalculation
3712 --return;
3713 null;
3714 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);
3715 ELSIF v_line_category_code in ('ORDER') THEN --- aaa, compute assessable value
3716
3717 /* Bug 5243532. Added by Lakshmi Gopalsami
3718 * Removed the cursor set_of_books_cur and implemented using caching logic.
3719 */
3720 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
3721 (p_org_id => v_warehouse_id );
3722 v_set_of_books_id := l_func_curr_det.ledger_id;
3723
3724 v_converted_rate := jai_cmn_utils_pkg.currency_conversion (v_set_of_books_id ,
3725 v_currency_code, v_conv_date , v_conv_type_code, v_conv_rate);
3726
3727 OPEN address_cur(v_ship_to_site_use_id);
3728 FETCH address_cur INTO v_address_id;
3729 CLOSE address_cur;
3730
3731 FND_FILE.PUT_LINE(FND_FILE.LOG,ii||' 3 v_address_id = '||v_address_id||', 121 v_customer_id = '||v_customer_id);
3732 -- Fetch Assessable Price List Value for the
3733 -- given Customer and Location Combination
3734 -- OPEN Get_Assessable_Value_Cur(v_customer_id, v_address_id, v_inventory_item_id, v_uom_code, v_date_ordered);
3735 /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980 --added to_char*/
3736 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
3737 FETCH Get_Assessable_Value_Cur INTO v_assessable_value, v_price_list_uom_code;
3738 CLOSE Get_Assessable_Value_Cur;
3739
3740 IF v_assessable_value IS NULL THEN --5
3741 -- Fetch Assessable Price List Value for the
3742 -- given Customer and NULL LOCATION Combination
3743 -- OPEN Get_Assessable_Value_Cur(v_customer_id, 0, v_inventory_item_id, v_uom_code, v_date_ordered);
3744 /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980 --added to_char*/
3745 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
3746 FETCH Get_Assessable_Value_Cur INTO v_assessable_value, v_price_list_uom_code;
3747 CLOSE Get_Assessable_Value_Cur;
3748 FND_FILE.PUT_LINE(FND_FILE.LOG,ii||' 4 v_assessable_value = '||v_assessable_value);
3749 END IF; --5
3750
3751 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);
3752 IF NVL(v_assessable_value,0) > 0 THEN --6
3753 -- If still the Assessable Value is available
3754 IF v_price_list_uom_code IS NOT NULL THEN --7
3755 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);
3756 INV_CONVERT.inv_um_conversion(v_uom_code, v_price_list_uom_code, v_inventory_item_id, v_conversion_rate);
3757 IF nvl(v_conversion_rate, 0) <= 0 THEN --8
3758 FND_FILE.PUT_LINE(FND_FILE.LOG,ii||' 7 , v_conversion_rate = '||v_conversion_rate);
3759 INV_CONVERT.inv_um_conversion(v_uom_code, v_price_list_uom_code, 0, v_conversion_rate);
3760 FND_FILE.PUT_LINE(FND_FILE.LOG,ii||' 8 , v_conversion_rate = '||v_conversion_rate);
3761 IF nvl(v_conversion_rate, 0) <= 0 THEN --9
3762 v_conversion_rate := 0;
3763 END IF; --9
3764 END IF; --8
3765 END IF; --7
3766
3767 v_assessable_value := NVL(1/v_converted_rate,0) * nvl(v_assessable_value,0) * v_conversion_rate;
3768 v_assessable_amount := nvl(v_assessable_value,0) * v_line_quantity;
3769
3770 FND_FILE.PUT_LINE(FND_FILE.LOG,ii||' 9 v_assessable_amount = '||v_assessable_amount||', v_assessable_value = '||v_assessable_value);
3771 ELSE --6
3772 -- If the assessable value is not available
3773 -- then pick up the Line price for Tax Calculation
3774 v_assessable_value := NVL(v_unit_selling_price,0);
3775 v_assessable_amount := v_line_amount;
3776 FND_FILE.PUT_LINE(FND_FILE.LOG,ii||' 10 v_assessable_amount = '||v_assessable_amount);
3777 END IF; --6
3778
3779 IF v_old_assessable_value <> v_assessable_value THEN ---bbb
3780
3781 /* commented by cbabu for Bug#2496481
3782 Open get_source_id;
3783 Fetch get_source_id into v_source_id;
3784 Close get_source_id;
3785
3786 IF v_line_category_code in ('ORDER') THEN --11 , and V_Order_Source_Type = 'Internal'
3787
3788 FND_FILE.PUT_LINE(FND_FILE.LOG,ii||' 11');
3789 ja_in_tax_pkg.ja_in_cust_default_taxes(
3790 v_warehouse_id, v_customer_id,v_ship_to_site_use_id, v_inventory_item_id,
3791 v_header_id,v_line_id,v_tax_category_id);
3792 IF v_tax_category_id IS NULL THEN
3793 FND_FILE.PUT_LINE(FND_FILE.LOG,ii||' 12');
3794 ja_in_tax_pkg.ja_in_org_default_taxes(v_warehouse_id, v_inventory_item_id, v_tax_category_id);
3795 ELSE --13
3796 v_line_tax_amount := v_line_amount;
3797 FND_FILE.PUT_LINE(FND_FILE.LOG,ii||' 13 = '||v_line_tax_amount);
3798 END IF; --13
3799
3800 ja_in_tax_pkg.ja_in_calc_prec_taxes(
3801 v_transaction_name, v_tax_category_id, v_header_id, v_line_id,
3802 v_assessable_amount, v_line_tax_amount, v_inventory_item_id, v_line_quantity,
3803 v_uom_code, '', '', v_converted_rate,
3804 v_creation_date, v_created_by, v_last_update_date, v_last_updated_by, v_last_update_login);
3805
3806 END IF; --11
3807
3808 OPEN order_tax_amount_Cur(v_header_id, v_LINE_ID);
3809 FETCH order_tax_amount_Cur INTO v_line_tax_amount;
3810 CLOSE order_tax_amount_Cur;
3811 */
3812
3813 -- added by cbabu for Bug#2496481, start
3814 -- v_line_tax_amount is the OUT variable for ja_in_tax_recalc procedure which contains total tax amount for that line
3815 v_line_tax_amount := v_line_amount;
3816 FND_FILE.PUT_LINE(FND_FILE.LOG,ii||' 13 = '||v_line_tax_amount);
3817 /* Added by Brathod for Bug# 4400993 */
3818 ln_vat_assessable_value := jai_general_pkg.ja_in_vat_assessable_value
3819 (
3820 p_party_id => v_customer_id ,
3821 p_party_site_id => v_ship_to_site_use_id ,
3822 p_inventory_item_id => v_inventory_item_id ,
3823 p_uom_code => v_uom_code ,
3824 p_default_price => v_unit_selling_price ,
3825 p_ass_value_date => v_date_ordered ,
3826 p_party_type => 'C'
3827 );
3828
3829 ln_vat_assessable_value := nvl(ln_vat_assessable_value,0) * v_line_quantity;
3830 /* End OF Bug# 4400993 */
3831
3832
3833 --Added by zhiwei for bug10043656 GST enhancement 2010/09/14 begin
3834 ---------------------------------------------------
3835 /*Start Commenting by mmurtuza for removing GST Changes*/
3836 /*ln_gst_assessable_value := JAI_GST_GENERAL_PKG.GET_GST_ASSESSABLE_VALUE
3837 (
3838 p_party_id => v_customer_id ,
3839 p_party_site_id => v_ship_to_site_use_id ,
3840 p_inventory_item_id => v_inventory_item_id ,
3841 p_uom_code => v_uom_code ,
3842 p_default_price => v_unit_selling_price ,
3843 p_ass_value_date => v_date_ordered ,
3844 p_party_type => 'C'
3845 );
3846
3847 ln_gst_assessable_value := nvl(ln_gst_assessable_value,0) * v_line_quantity;*/
3848 /*End Commenting by mmurtuza for removing GST Changes*/
3849 ---------------------------------------------------
3850 --Added by zhiwei for bug10043656 GST enhancement 2010/09/14 end
3851
3852 jai_om_tax_pkg.recalculate_oe_taxes
3853 (
3854 v_header_id,
3855 v_line_id,
3856 v_assessable_amount,
3857 ln_vat_assessable_value , /* Added by Brathod for Bug# 4400993, Ramananda for Bug#4540783 */
3858 v_line_tax_amount,
3859 v_inventory_item_id,
3860 v_line_quantity,
3861 v_uom_code,
3862 v_converted_rate,
3863 v_last_update_date,
3864 v_last_updated_by,
3865 v_last_update_login
3866 --,ln_gst_assessable_value --Added by zhiwei for bug10043656 GST enhancement 2010/09/14 /*Commented by mmurtuza for removing GST Changes*/
3867 );
3868 -- added by cbabu for Bug#2496481, end
3869
3870 UPDATE JAI_OM_OE_SO_LINES
3871 SET assessable_value = v_assessable_value,
3872 tax_amount = nvl(v_line_tax_amount,0),
3873 line_amount = v_line_amount,
3874 line_tot_amount = v_line_amount + nvl(v_line_tax_amount,0),
3875 last_update_date = v_last_update_date,
3876 last_updated_by = v_last_updated_by,
3877 last_update_login = v_last_update_login
3878 WHERE line_id = v_line_id and header_id = v_header_id;
3879
3880 END IF; ---bbb
3881 END IF; ---aaa
3882
3883 FND_FILE.PUT_LINE(FND_FILE.LOG,ii||' 14');
3884 ------------------------------------------------------------------------
3885 v_original_system_reference := null;
3886 v_customer_id := null;
3887 v_address_id := null;
3888 v_price_list_id := null;
3889 v_org_id := null;
3890 v_order_number := null;
3891 v_conv_type_code := null;
3892 v_conv_rate := null;
3893 v_conv_date := null;
3894 v_conv_factor := null;
3895 v_set_of_books_id := null;
3896 v_tax_category_id := null;
3897 v_order_category := null;
3898 v_line_amount := 0;
3899 /* commented by cbabu for Bug#2496481
3900 v_source_header_id := null;
3901 v_source_order_category := null;
3902 v_source_id := null;
3903 */
3904 v_assessable_value := null;
3905 v_assessable_amount := null;
3906 v_price_list_uom_code := null;
3907 v_converted_rate := null;
3908 v_date_ordered := null;
3909 v_ordered_date := null;
3910
3911 v_line_tax_amount := 0;
3912 v_conversion_rate := 0;
3913 v_currency_code := null;
3914 v_order_source_type := null;
3915 v_source_document_type_id := null;
3916 v_org_id := null;
3917 v_customer_id := null;
3918 v_order_number := null;
3919 v_price_list_id := null;
3920 v_order_category := null;
3921 v_original_system_reference := null;
3922 v_currency_code := null;
3923 v_conv_type_code := null;
3924 v_conv_rate := null;
3925 v_conv_date := null;
3926 v_date_ordered := null;
3927 ------------------------------------------------------------------------
3928
3929 END IF; --- zzz
3930 v_check_line := null;
3931 ii := ii + 1;
3932
3933 END LOOP;
3934 COMMIT;
3935 FND_FILE.PUT_LINE(FND_FILE.LOG,'END of the procedure JA_IN_ASSESSABLE_PRICE_CHANGE');
3936
3937 EXCEPTION
3938 WHEN OTHERS THEN
3939 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
3940 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
3941 app_exception.raise_exception;
3942 END recalculate_excise_taxes;
3943
3944 END jai_om_tax_pkg;
[Home] [Help]