1 PACKAGE BODY jai_cmn_rcv_matching_pkg AS
2 /* $Header: jai_cmn_rcv_mach.plb 120.36.12020000.4 2013/04/01 04:42:45 mmurtuza ship $ */
3
4 PROCEDURE automatic_match_process(
5 errbuf OUT NOCOPY VARCHAR2,
6 p_created_by IN NUMBER,
7 p_last_update_login IN NUMBER,
8 p_organization_id IN NUMBER,
9 p_customer_id IN NUMBER,
10 p_order_type_id IN NUMBER,
11 p_delivery_id IN NUMBER DEFAULT null,
12 p_delivery_detail_id IN NUMBER DEFAULT null,
13 p_order_header_id IN NUMBER DEFAULT null,
14 p_line_id IN NUMBER DEFAULT null
15 )
16 IS
17 /*------------------------------------------------------------------------------------------------------------------
18 Sl.No. dd/mm/yyyy Author and Details
19 ----- ---------- ----------------
20 1 12/07/2002 Vijay Created this procedure for Bug#2083127
21 This procedure is used to match all the delivery details present in a delivery, with the corresponding receipts
22 present in the register. Receipts are matched based on the inventory_item_id, organization AND location combination.
23 The order of the matching the receipts is FIFO.
24 Parameter for this procedure are
25 errbuf, p_organization_id, p_customer_id, p_order_type_id, p_delivery_id,
26 p_delivery_detail_id, p_order_header_id, p_line_number
27
28 2 13/12/2002 cbabu for Bug# 2689425, FileVersion# 615.2
29 Quantity available is calculated as JAI_CMN_RG_23D_TRXS.quantity_received - quantity that is matched, but it not
30 including the RTV quantity, that is calculating wrongly. Changes made
31 Quantity Available is modified to be calculated as
32 ( JAI_CMN_RG_23D_TRXS.qty_to_adjust - deliver quantity matched with receipt but not ship confirmed )
33
34 3. 08-Jun-2005 Version 116.2 jai_cmn_rcv_mach -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
35 as required for CASE COMPLAINCE.
36
37 4. 13-Jun-2005 File Version: 116.3
38 Ramananda for bug#4428980. Removal of SQL LITERALs is done
39
40 5. 26-FEB-2007 SSAWANT , File version 120.5
41 Forward porting the change in 11.5 bug 5068418 to R12 bug no 5091874.
42
43 Issue :
44 Discount Tax is appearing as zero for order in Ship Confirm Localized screen
45 Cause :
46 An if condition is placed for restricting all tax precedance caluculations for
47 tax_rates <=0. This condition is restricting the calculation of precedance based 'Discount'
48 taxes ( which have tax_rate_tab (i) < 0 ). Thus the tax amount is calculated as zero.
49 The calculation should not be done for Adhoc / "Excise" and "CVD" taxes which have corresponding
50 tax rates defined in matched receipts.
51 Fix :
52 Old Condition : IF tax_rate_tab( i ) > 0 AND End_Date_Tab(I) <> 0 THEN
53 Modified Condition : IF tax_rate_tab( i ) <> 0 AND End_Date_Tab(I) <> 0 AND adhoc_flag_tab(i) = 'N' AND NOT ( tax_rate_tab(i) = -1 AND tax_type_tab(i) IN (1,3) ) THEN
54
55 Dependency Introduced due to this bug:
56 None
57 6. 13/04/2007 bduvarag for the Bug#5989740, file version 120.6
58 Forward porting the changes done in 11i bug#5907436
59
60 7. 26/09/2007 rchandan for bug#6447097. File version 120.10
61 Issue : QA observations of Inter org
62 Fix ; Insert statement into Jai_cmn_match_Taxes did not included the PK
63 match_tax_id.
64 8. 23-apr-2009 vkaranam for bug#8445390,file version 120.18.12010000.2
65 Issue:
66 In Interorg transfer for a trading org ,matching the transaction quantity against the multiple receipts
67 is showing the wrong excise amount.
68 Reason::
69 UPDATE Jai_cmn_document_Taxes
70 SET Tax_Amt = tax_amt_tab(i),
71 Func_Tax_Amt = tax_amt_tab(i) * NVL( p_curr_conv_factor, 1 )
72 WHERE source_doc_line_id = p_ref_line_id
73 AND source_doc_type = 'INTERORG_XFER'
74 AND Tax_Line_No = i;
75 we are updating the tax amount of the last matched receipt line processed.
76
77 Due to this the amount is not getting correctly.
78 Fix:
79 Modified the update stmt as
80 UPDATE Jai_cmn_document_Taxes
81 SET Tax_Amt = nvl(tax_amt,0)+tax_amt_tab(i), --added nvl(tax_amt,0)
82 for bug#8445390
83 Func_Tax_Amt = nvl(Func_Tax_Amt,0)+tax_amt_tab(i) * NVL(
84 p_curr_conv_factor, 1 ) --added nvl(Func_Tax_Amt,0) for bug#8445390
85 WHERE source_doc_line_id = p_ref_line_id
86 AND source_doc_type = 'INTERORG_XFER'
87 AND Tax_Line_No = i;
88
89 9. 11-sep-2009 vkaranam for bug#8887871
90 issue:
91 VAT ASSESSABLE PRICE IS NOT CALCULATED AT SHIPCONFIRM(LOCALIZED) SCREEN
92 fix:
93 Changes are done to calculate the VAT tax based on the line amount.
94
95 10. 18-Jan-2010 CSahoo for bug#9288359, File Version 120.18.12010000.5
96 Issue: NOT ABLE TO MATCH AND PASS ADDITIONAL CVD FROM TRADING ORGANIZATION TO OTHER ORG
97 Fix: Modified the cursor Excise_Duty_Rate_Cur in the procedure om_default_taxes. Added the additional_cvd
98 in the cursor.
99
100 11. 19-Sep-2010 Xiao Lv for GST bug#10043656,
101 Issue: For GST Enhancement.
102 Fix: Add and calculate tax in GST Regime, based on GST Accssable Value.
103 12. 28-Jan-2011 Shujuan Yan bug 10358786
104 Inclusive Tax
105 13. 02-jun-2011 vkaranam for bug#12594676
106 Issue: VAT tax is getting claculated incorrectly for matching the receipt with the partial qty.
107 Fix: ln_vat_assessable_value_tot := ln_vat_assessable_value_tot*nvl(p_line_quantity,0)/v_original_quantity;
108 14. 29-Jun-2011 qiong.liu for bug12687384
109 TAXES ARE COMPUTED AS ZERO IN FULFILLED ORDERS LOCALIZED SCREEN FOR A TRADING
110 15. 04-aug-2011 vkaranam for bug#12819107
111 Issue:
112 Adhoc freight and other type of taxes are calculated as 0 in fulfilled orders localized screen for a trading
113 fix:
114 tax amount logic for adhoc freight and other taxes has been modified.
115 tax_amt_tab(I)=tax_target_tab(I);
116 14 24-JaN-2012 vkaranam for bug#16082873
117 Issue :Taxes with 0% tax rate are calculated in negative values
118 Technical details:
119 Tax_rate_tab for 0% tax rate is assigned the value as -1
120 due to which taxes are calculated in negative figures.
121 fix details:
122 tax_rate_tab shall be assigned as -9999 for which tax_rate is null.
123
124 --------------------------------------------------------------------------------------------------------------------*/
125 -- fields in the view
126 -- organization_id, customer_id, inventory_item_id, picking_line_id,
127 -- subinventory, order_no, order_header_id, line_id, uom,
128 -- release_qty, delivery_id, order_type_id
129 -- ,org_id, location_id
130
131 CURSOR c_match_details IS
132 SELECT customer_id, inventory_item_id,
133 picking_line_id delivery_detail_id,
134 organization_id, subinventory sub_inventory_name, order_no order_number,
135 order_header_id header_id, release_qty requested_quantity, delivery_id,
136 order_type_id, location_id ship_from_location_id, uom requested_quantity_uom
137 FROM JAI_OE_MATCH_LINES_V
138 WHERE organization_id = p_organization_id
139 AND order_type_id = p_order_type_id
140 AND delivery_id = nvl(p_delivery_id, delivery_id)
141 AND picking_line_id = nvl(p_delivery_detail_id, picking_line_id)
142 AND customer_id = nvl(p_customer_id, customer_id)
143 AND order_header_id = nvl(p_order_header_id, order_header_id)
144 AND line_id= nvl(p_line_id, line_id);
145
146 CURSOR c_qty_app_on_receipt( p_receipt_id IN NUMBER ) IS
147 SELECT SUM(receipt_quantity_applied)
148 FROM JAI_CMN_MATCH_RECEIPTS
149 WHERE receipt_id = p_receipt_id
150 AND ship_status IS NULL; -- cbabu for Bug# 2689425
151
152 CURSOR c_qty_matched(p_delivery_detail_id IN NUMBER) IS
153 SELECT SUM(quantity_applied)
154 FROM JAI_CMN_MATCH_RECEIPTS
155 WHERE ref_line_id = p_delivery_detail_id
156 and order_invoice = 'O';
157
158 -- this is commented in this cursor because, the subquery is taken care programmatically and if included in this cursor
159 --then it becomes a performance issue.
160 CURSOR c_receipts_for_item( p_organization_id IN NUMBER,
161 p_location_id IN NUMBER,
162 p_inventory_item_id IN NUMBER) IS
163 SELECT * FROM JAI_CMN_RG_23D_TRXS
164 WHERE organization_id = p_organization_id
165 AND location_id = p_location_id
166 AND inventory_item_id = p_inventory_item_id
167 AND qty_to_adjust > 0
168 ORDER BY register_id;
169
170 v_exist NUMBER;
171 CURSOR c_duplicate_record(p_register_id IN NUMBER, p_delivery_detail_id IN NUMBER) IS
172 SELECT count(receipt_id)
173 FROM JAI_CMN_MATCH_RECEIPTS
174 WHERE receipt_id = p_register_id
175 AND ref_line_id = p_delivery_detail_id
176 AND order_invoice = 'O';
177
178 v_QtyToMatch NUMBER := 0;
179 v_QtyToMatchInRctUOM NUMBER := 0;
180 v_ReceiptQtyAvailable NUMBER := 0;
181 v_RctQtyAvailableInIssueUOM NUMBER := 0;
182 v_QtyAppliedOnReceipt NUMBER := 0;
183 v_UomConversion NUMBER := 0;
184
185 v_DetailsProcessed NUMBER := 0;
186 v_DetailsFetched NUMBER := 0;
187 v_MatchedRcptsForDetail NUMBER := 0;
188
189 v_QtyMatched NUMBER;
190 v_QtyApplied NUMBER;
191 v_QtyAppliedInRcptUom NUMBER;
192
193 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rcv_matching_pkg.automatic_match_process'; /* Added by Ramananda for bug#4407165 */
194
195
196 BEGIN
197
198 errbuf := null;
199
200 FOR match_detail IN c_match_details LOOP
201
202 OPEN c_qty_matched(match_detail.delivery_detail_id) ;
203 FETCH c_qty_matched INTO v_QtyMatched;
204 CLOSE c_qty_matched;
205
206 v_QtyToMatch := match_detail.requested_quantity - nvl(v_QtyMatched,0);
207
208 IF v_QtyToMatch > 0 THEN
209
210 FOR rg23d_entry IN c_receipts_for_item( match_detail.organization_id,
211 match_detail.ship_from_location_id, match_detail.inventory_item_id)
212 LOOP
213
214 OPEN c_qty_app_on_receipt(rg23d_entry.register_id) ;
215 FETCH c_qty_app_on_receipt INTO v_QtyAppliedOnReceipt;
216 CLOSE c_qty_app_on_receipt;
217
218 -- v_ReceiptQtyAvailable := nvl(rg23d_entry.quantity_received,0) - nvl(v_QtyAppliedOnReceipt,0); -- cbabu for Bug# 2689425
219 v_ReceiptQtyAvailable := nvl(rg23d_entry.qty_to_adjust,0) - nvl(v_QtyAppliedOnReceipt,0); -- cbabu for Bug# 2689425
220
221 IF v_ReceiptQtyAvailable > 0 THEN --xyz
222
223 Inv_Convert.Inv_Um_Conversion( rg23d_entry.primary_uom_code,
224 match_detail.requested_quantity_uom, rg23d_entry.Inventory_item_id, v_UomConversion);
225 Inv_Convert.Inv_Um_Conversion( rg23d_entry.primary_uom_code,
226 match_detail.requested_quantity_uom, rg23d_entry.Inventory_item_id, v_UomConversion);
227 IF nvl(v_UomConversion, 0) <= 0 THEN
228 Inv_Convert.Inv_Um_Conversion( rg23d_entry.primary_uom_code,
229 match_detail.requested_quantity_uom, 0, v_UomConversion);
230 END IF;
231 IF nvl(v_UomConversion, 0) <= 0 THEN
232 v_UomConversion := 1;
233 END IF;
234
235 v_RctQtyAvailableInIssueUOM := v_ReceiptQtyAvailable * v_UomConversion;
236 v_QtyToMatchInRctUOM := v_QtyToMatch / v_UomConversion;
237
238 fnd_file.put_line(fnd_file.log, '2 v_QtyToMatch = '||v_QtyToMatch||', v_RctQtyAvailableInIssueUOM = '||v_RctQtyAvailableInIssueUOM);
239 -- Receipt Qty is less than Delivery Detail Qty,
240 -- then loop through Receipts for Matching entire Delivery Detail Qty
241
242 IF v_QtyToMatch > v_RctQtyAvailableInIssueUOM
243 THEN
244 v_QtyApplied := v_RctQtyAvailableInIssueUOM;
245 v_QtyAppliedInRcptUom := v_ReceiptQtyAvailable;
246
247 v_QtyToMatch := v_QtyToMatch - v_RctQtyAvailableInIssueUOM;
248 v_MatchedRcptsForDetail := v_MatchedRcptsForDetail + 1;
249
250 -- Receipt Qty is more than Delivery Detail Qty,
251 -- then match with the corresponding receipt AND come out receipts loop
252 ELSIF v_QtyToMatch <= v_RctQtyAvailableInIssueUOM THEN
253 v_QtyApplied := v_QtyToMatch;
254 v_QtyAppliedInRcptUom := v_QtyToMatchInRctUOM;
255
256 v_QtyToMatch := 0;
257 v_MatchedRcptsForDetail := v_MatchedRcptsForDetail + 1;
258 ELSE
259 errbuf := 'Dont know whats the error. Quantity to match = '||v_QtyToMatch;
260 RAISE_APPLICATION_ERROR(-20120, 'Dont know whats the error. Quantity to match = '||v_QtyToMatch );
261 END IF; --aaa
262
263 Open c_duplicate_record(rg23d_entry.register_id, match_detail.delivery_detail_id);
264 Fetch c_duplicate_record Into v_exist;
265 Close c_duplicate_record;
266 --fnd_file.put_line(fnd_file.log, '2 v_QtyToMatch = '||v_QtyToMatch||', v_RctQtyAvailableInIssueUOM = '||v_RctQtyAvailableInIssueUOM);
267 IF NVL(v_exist,0) <> 1 Then
268 INSERT INTO JAI_CMN_MATCH_RECEIPTS (
269 receipt_id, ref_line_id,
270 subinventory, quantity_applied,
271 issue_uom, receipt_quantity_applied, receipt_quantity_uom,
272 order_invoice, ship_status,
273 creation_date, created_by, last_update_date, last_update_login, last_updated_by)
274 VALUES (
275 rg23d_entry.register_id, match_detail.delivery_detail_id,
276 match_detail.sub_inventory_name, v_QtyApplied,
277 match_detail.requested_quantity_uom, v_QtyAppliedInRcptUom, rg23d_entry.primary_uom_code
278 , 'O', null,
279 sysdate, p_created_by, sysdate, p_last_update_login, p_created_by);
280 ELSE
281 UPDATE JAI_CMN_MATCH_RECEIPTS
282 SET quantity_applied = v_QtyApplied,
283 receipt_quantity_applied = v_QtyAppliedInRcptUom,
284 last_update_login = p_last_update_login,
285 last_update_date = sysdate
286 WHERE receipt_id = rg23d_entry.register_id
287 AND ref_line_id = match_detail.delivery_detail_id
288 AND order_invoice = 'O';
289 END IF;
290
291 IF v_QtyToMatch = 0 THEN
292 EXIT;
293 END IF;
294 END IF; --xyz
295 END LOOP;
296
297 fnd_file.put_line(fnd_file.log, match_detail.delivery_id ||', '||match_detail.delivery_detail_id ||', Matched receipts = '||v_MatchedRcptsForDetail);
298 IF v_QtyToMatch > 0 THEN
299 ROLLBACK;
300 errbuf := 'Enough receipt quantity is not available for the Detail ' || match_detail.delivery_detail_id||', Delivery = '||match_detail.delivery_id;
301 RAISE_APPLICATION_ERROR(-20120, '11 Enough Quantity is not available for the Delivery = '||match_detail.delivery_id||', and Delivery Line = ' || match_detail.delivery_detail_id);
302 END IF;
303
304 IF v_MatchedRcptsForDetail = 0 THEN
305 ROLLBACK;
306 errbuf := 'No Receipts found for the receipt with item id = '|| match_detail.inventory_item_id;
307 RAISE_APPLICATION_ERROR(-20120, '2 No Receipts found for the receipt with item id = '
308 || match_detail.inventory_item_id );
309 END IF;
310
311 v_UomConversion := 0;
312 v_ReceiptQtyAvailable := 0;
313 v_RctQtyAvailableInIssueUOM := 0;
314 v_QtyToMatch := 0;
315 v_QtyToMatchInRctUOM := 0;
316 v_QtyAppliedOnReceipt := 0;
317
318 v_MatchedRcptsForDetail := 0;
319 v_DetailsProcessed := v_DetailsProcessed + 1;
320
321 ELSE -- for v_QtyToMatch <= 0
322 null;
323 END IF;
324
325 v_QtyToMatch := null;
326 v_DetailsFetched := v_DetailsFetched + 1;
327 END LOOP;
328
329
330 IF v_DetailsProcessed = 0 THEN
331 ROLLBACK;
332 IF v_DetailsFetched <> 0 THEN
333 errbuf := 'Details already matched for the given inputs';
334 RAISE_APPLICATION_ERROR(-20120, 'Details already matched for the given inputs');
335 ELSE
336 errbuf := 'No details found for the given inputs';
337 RAISE_APPLICATION_ERROR(-20120, 'No details found for the given inputs');
338 END IF;
339 END IF;
340 COMMIT;
341 errbuf := 'Automatic matching is Successful. Total no of Delivery details processed = ' || v_DetailsProcessed;
342
343 /* Added by Ramananda for bug#4407165 */
344 EXCEPTION
345 WHEN OTHERS THEN
346 errbuf := null;
347 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
348 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
349 app_exception.raise_exception;
350
351 END automatic_match_process;
352
353 /*
354 Commented during the removal of sql literals
355
356 PROCEDURE opm_default_taxes( p_subinventory IN VARCHAR2,
357 p_customer_id IN NUMBER,
358 p_ref_line_id IN NUMBER,
359 p_receipt_id IN NUMBER,
360 p_line_id NUMBER, -- For OE it is Line ID, for AR it is Link to cust trx line id
361 p_line_quantity IN NUMBER,
362 p_curr_conv_factor IN NUMBER,
363 p_order_invoice IN VARCHAR2 ,
364 p_line_no Number ) IS
365
366 ------------------------------------------------------------------------------------------
367 CHANGE HISTORY for FILENAME: ja_in_gem_rg23_d_cal_tax_prc.sql
368 S.No Date Author and Details
369 ------------------------------------------------------------------------------------------
370 1 20/05/2004 Aiyer for Bug#3433634 - 712.1
371 Changed the population of end_date_tab variable based on the JAI_OPM_TAXES.end date
372 column value.
373 Now the code is changed so that the tax amount would be set to zero only in case of records having end dates
374 falling prior to the current date.
375 The tax amount would be calculated for all open end dates (i.e null end dates)
376 and end dates equal to or greater than the current date (sysdate).
377 Dependency Due to this Bug: -
378 None
379
380 2. 01/11/2006 SACSETHI for bug 5228046, File version 120.4
381 1. Forward porting the change in 11i bug 5365523 (Additional CVD Enhancement).
382 This bug has datamodel and spec changes.
383
384 2. Forward porting of 11i bug 5219225 as part of removal of CVD and Additional CVD
385 ------------------------------------------------------------------------------------------
386
387
388 TYPE Num_Tab IS
389 TABLE OF NUMBER(14,3)
390 INDEX BY BINARY_INTEGER;
391 TYPE Tax_Amt_Num_Tab IS
392 TABLE OF NUMBER(14,4)
393 INDEX BY BINARY_INTEGER;
394
395 p1 NUM_TAB ;
396 p2 NUM_TAB ;
397 p3 NUM_TAB ;
398 p4 NUM_TAB ;
399 p5 NUM_TAB ;
400
401 -- Date 01-NOV-2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
402 -- START BUG 5228046
403 p6 NUM_TAB ;
404 p7 NUM_TAB ;
405 p8 NUM_TAB ;
406 p9 NUM_TAB ;
407 p10 NUM_TAB ;
408 -- END BUG 5228046
409
410
411 tax_rate_tab NUM_TAB ;
412 tax_type_tab NUM_TAB ;
413 end_date_tab NUM_TAB ;
414 tax_amt_tab TAX_AMT_NUM_TAB ;
415 tax_target_tab TAX_AMT_NUM_TAB ;
416 rounding_factor_tab TAX_AMT_NUM_TAB ;
417
418 v_exempt_flag VARCHAR2(2); -- := 'N' ; --Ramananda for File.Sql.35
419 v_address_id NUMBER ;
420 i NUMBER ;
421 excise_flag_set BOOLEAN := FALSE ;
422 v_inventory_item_id NUMBER;
423 v_unit_code VARCHAR2(15) ;
424 v_selling_price NUMBER ;
425 v_amt NUMBER ;
426 v_cum_amount NUMBER ;
427 bsln_amt NUMBER ;
428 row_count NUMBER := 0 ;
429 v_tax_amt NUMBER(14,4) := 0 ;
430 vamt NUMBER(14,4) := 0 ;
431 v_conversion_rate NUMBER ;
432 counter NUMBER ;
433 max_iter NUMBER := 10 ;
434 conv_rate NUMBER ;
435 v_count NUMBER := 0 ;
436 v_original_quantity NUMBER := 0 ;
437 v_matched_quantity NUMBER := 0 ;
438 v_excise_duty_rate NUMBER ;
439 TT NUMBER ;
440 div_fac NUMBER ;
441 v_order_um1 VARCHAR2(20) ;
442 v_exchage_rate NUMBER ;
443 v_item_id NUMBER ;
444 v_primary_uom_code VARCHAR2(4) ;
445 v_item_um_f NUMBER ;
446
447
448 CURSOR oe_tax_cur IS ---- OE Side
449 SELECT
450 a.tax_id ,
451 a.tax_line_no lno ,
452 a.precedence_1 p_1 ,
453 a.precedence_2 p_2 ,
454 a.precedence_3 p_3 ,
455 a.precedence_4 p_4 ,
456 a.precedence_5 p_5 ,
457 a.precedence_6 p_6 , -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
458 a.precedence_7 p_7 ,
459 a.precedence_8 p_8 ,
460 a.precedence_9 p_9 ,
461 a.precedence_10 p_10 ,
462 a.qty_rate ,
463 a.tax_rate ,
464 a.tax_amount ,
465 a.uom ,
466 nvl( to_char(b.end_date,'DD-MON-YYYY'),
467 to_char(sysdate,'DD-MON-YYYY')
468 ) valid_date , -- Changed by aiyer for the bug 3433634
469 decode(
470 upper(b.tax_type) ,
471 'EXCISE' ,
472 1 ,
473 'ADDL. EXCISE' ,
474 1 ,
475 'OTHER EXCISE' ,
476 1 ,
477 'CVD' ,
478 1 ,
479 'TDS' ,
480 2 ,
481 0
482 ) tax_type_val ,
483 b.mod_cr_Percentage ,
484 b.tax_type ,
485 NVL( b.rounding_factor, 0 ) rnd
486 FROM
487 JAI_OPM_SO_PICK_TAXES a,
488 JAI_OPM_TAXES b
489 WHERE
490 a.bol_id = p_ref_line_id AND
491 a.bolline_no = p_line_no AND
492 a.tax_id = b.tax_id
493 ORDER BY
494 a.tax_line_no;
495
496
497 CURSOR ar_tax_cur IS ---- AR Side
498 SELECT a.tax_id, a.tax_line_no lno,
499 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,
500 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 )
501 a.qty_rate,
502 a.tax_rate, a.tax_amount, a.uom, b.end_date valid_date,
503 decode(upper(b.tax_type),'EXCISE', 1, 'ADDL. EXCISE', 1, 'OTHER EXCISE', 1, 'CVD',1, 'TDS', 2, 0) tax_type_val,
504 b.mod_cr_Percentage, b.vendor_id, b.tax_type, NVL( b.rounding_factor, 0 ) rnd
505 FROM JAI_AR_TRX_TAX_LINES a, JAI_CMN_TAXES_ALL b
506 WHERE a.link_to_cust_trx_line_id = p_line_id
507 AND a.tax_id = b.tax_id
508 ORDER BY a.tax_line_no;
509
510 CURSOR uom_class_cur( v_unit_code IN VARCHAR2, p_tax_line_uom_code IN VARCHAR2) IS
511 SELECT A.uom_class
512 FROM mtl_units_of_measure A, mtl_units_of_measure B
513 WHERE A.uom_code = v_unit_code
514 AND B.uom_code = p_tax_line_uom_code
515 AND A.uom_class = B.uom_class;
516
517 CURSOR Fetch_Dtls_Cur IS -- OE Side
518 SELECT QUANTITY,price
519 FROM JAI_OPM_SO_PICK_LINES -- JAI_AR_TRX_LINES
520 WHERE Order_id = p_line_id;
521
522 CURSOR Fetch_Dtls1_Cur IS -- AR Side
523 SELECT Quantity, Unit_Selling_Price, Unit_Code, Inventory_Item_Id
524 FROM JAI_AR_TRX_LINES
525 WHERE Customer_Trx_Line_Id = p_line_id;
526
527 CURSOR Fetch_Exempt_Cur( AddressId IN NUMBER ) IS -- OE Side
528 SELECT NVL( Exempt, 'N' )
529 FROM JAI_CMN_CUS_ADDRESSES
530 WHERE Customer_Id = p_customer_id
531 AND Address_Id = AddressId;
532
533 CURSOR Fetch_OE_Address_Cur IS
534 SELECT address_id from ra_site_uses_all where site_use_id=
535 (select Ship_To_Site_Use_Id
536 FROM So_Picking_Lines_All
537 WHERE Picking_Line_Id = p_ref_line_id);
538
539 CURSOR Fetch_AR_Address_Cur IS
540 SELECT Address_id
541 FROM Ra_site_uses_all where site_use_id in
542 (select ship_to_site_use_id from RA_Customer_Trx_All
543 WHERE Customer_trx_Id in (select customer_trx_id from
544 ra_customer_trx_lines_all where customer_trx_line_id = p_ref_line_id));
545
546
547 CURSOR Chk_Rcd_Cur IS
548 SELECT NVL( COUNT( * ), 0 )
549 FROM JAI_CMN_MATCH_TAXES
550 WHERE Ref_Line_Id = p_ref_line_id
551 AND Subinventory = p_subinventory
552 AND Receipt_Id = p_receipt_id
553 AND Order_Invoice = 'O';
554
555 CURSOR Chk_Rcd_AR_Cur IS
556 SELECT NVL( COUNT( * ), 0 )
557 FROM JAI_CMN_MATCH_TAXES
558 WHERE Ref_Line_Id = p_ref_line_id
559 AND Receipt_Id = p_receipt_id
560 AND Order_Invoice = 'I';
561
562 CURSOR Fetch_Totals_Cur( line_no IN NUMBER ) IS
563 SELECT SUM( NVL( Tax_Amount, 0 ) )
564 FROM JAI_CMN_MATCH_TAXES
565 WHERE Subinventory = p_subinventory
566 AND Ref_Line_Id = p_ref_line_id
567 AND Tax_Line_No = line_no;
568
569 CURSOR Fetch_Totals_AR_Cur( line_no IN NUMBER ) IS
570 SELECT SUM( NVL( Tax_Amount, 0 ) )
571 FROM JAI_CMN_MATCH_TAXES
572 WHERE Ref_Line_Id = p_ref_line_id
573 AND Tax_Line_No = line_no;
574
575 CURSOR Fetch_Total_AR_Cur( line_no IN NUMBER ) IS
576 SELECT SUM( NVL( Tax_Amount, 0 ) ) tax_amount, SUM( NVL( Base_Tax_Amount, 0 ) ) base_tax_amount,
577 SUM( NVL( Func_Tax_Amount, 0 ) ) func_tax_amount
578 FROM JAI_CMN_MATCH_TAXES
579 WHERE Ref_Line_Id = p_ref_line_id
580 AND Tax_Line_No = line_no
581 AND Receipt_ID IS NOT NULL;
582
583 -- CURSOR Fetch_Matched_Qty_AR_Cur IS
584 -- SELECT matched_quantity
585 -- FROM JAI_AR_TRX_LINES
586 -- WHERE Customer_Trx_Line_Id = p_ref_line_id;
587
588 CURSOR Excise_Duty_Rate_Cur IS
589 SELECT rate_per_unit ,PRIMARY_UOM_CODE
590 FROM JAI_CMN_RG_23D_TRXS
591 WHERE register_id = p_receipt_id;
592
593 Cursor C_op_dtl is
594 Select order_um1,exchange_rate,item_id
595 From op_ordr_dtl
596 WHERE bol_id = p_ref_line_id
597 and bolline_no = p_line_no;
598
599
600 BEGIN
601
602 v_exempt_flag := jai_constants.no; --Ramananda for File.Sql.35
603
604 IF p_ref_line_id IS NULL THEN
605 RAISE_APPLICATION_ERROR( -20120, 'Ref Line Id cannot be NULL' );
606 END IF;
607
608
609 OPEN Excise_Duty_Rate_Cur;
610 FETCH Excise_Duty_Rate_Cur into v_excise_duty_rate,v_primary_uom_code;
611 CLOSE Excise_Duty_Rate_Cur;
612
613 IF p_order_invoice = 'O' THEN
614 Open c_op_dtl;
615 Fetch c_op_dtl INTO v_order_um1,v_exchage_rate,v_item_id;
616 Close c_op_dtl;
617 if v_order_um1 <> v_primary_uom_code then
618 v_item_um_f := jai_cmn_utils_pkg.opm_uom_version(v_order_um1, v_primary_uom_code, v_item_id);
619 Else
620 v_item_um_f := 1;
621 End if;
622
623
624 OPEN Chk_Rcd_Cur;
625 FETCH Chk_Rcd_Cur INTO v_count;
626 CLOSE Chk_Rcd_Cur;
627
628 OPEN Fetch_Dtls_Cur;
629 FETCH Fetch_Dtls_Cur INTO v_original_quantity,v_selling_price;
630 CLOSE Fetch_Dtls_Cur;
631
632 IF nvl(p_line_quantity,0) <> 0 THEN
633 v_original_quantity := p_line_quantity;
634 END IF;
635
636 -- v_excise_duty_rate := ((nvl(v_excise_duty_rate,0) * v_exchage_rate) * v_original_quantity/v_item_um_f);
637 v_excise_duty_rate := ((nvl(v_excise_duty_rate,0) * 1) * v_original_quantity/v_item_um_f);
638
639
640 OPEN Fetch_OE_Address_Cur;
641 FETCH Fetch_OE_Address_Cur INTO v_address_id;
642 CLOSE Fetch_OE_Address_Cur;
643
644 OPEN Fetch_Exempt_Cur( v_address_id );
645 FETCH Fetch_Exempt_Cur INTO v_exempt_flag;
646 CLOSE Fetch_Exempt_Cur;
647
648 FOR rec in oe_tax_cur LOOP
649 IF v_count = 0 THEN
650 INSERT INTO JAI_CMN_MATCH_TAXES(MATCH_TAX_ID, REF_LINE_ID,
651 SUBINVENTORY,
652 TAX_LINE_NO,
653 PRECEDENCE_1,
654 PRECEDENCE_2,
655 PRECEDENCE_3,
656 PRECEDENCE_4,
657 PRECEDENCE_5,
658 PRECEDENCE_6, -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
659 PRECEDENCE_7,
660 PRECEDENCE_8,
661 PRECEDENCE_9,
662 PRECEDENCE_10,
663 TAX_ID,
664 TAX_RATE,
665 QTY_RATE,
666 UOM,
667 TAX_AMOUNT,
668 BASE_TAX_AMOUNT,
669 FUNC_TAX_AMOUNT,
670 TOTAL_TAX_AMOUNT,
671 CREATION_DATE,
672 CREATED_BY,
673 LAST_UPDATE_DATE,
674 LAST_UPDATE_LOGIN,
675 LAST_UPDATED_BY,
676 RECEIPT_ID,
677 ORDER_INVOICE )
678
679
680 VALUES ( JAI_CMN_MATCH_TAXES_S.nextval, p_ref_line_id,
681 p_subinventory,
682 z.lno,
683 rec.p_1,
684 rec.p_2,
685 rec.p_3,
686 rec.p_4,
687 rec.p_5,
688 rec.p_6, -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
689 rec.p_7,
690 rec.p_8,
691 rec.p_9,
692 rec.p_10,
693 rec.tax_id,
694 rec.tax_rate,
695 rec.qty_rate,
696 rec.uom,
697 0,
698 0,
699 0,
700 0,
701 SYSDATE,
702 UID,
703 SYSDATE,
704 UID,
705 UID,
706 p_receipt_id,
707 p_order_invoice );
708 END IF;
709 Tax_Rate_Tab(rec.lno) := nvl(rec.Tax_Rate,0);
710 IF ( excise_flag_set = FALSE AND rec.tax_type_val = 1 ) OR ( rec.tax_type_val <> 1 ) THEN -- OR rec.tax_type_val <> 1 THEN
711 P1(rec.lno) := nvl(rec.p_1,-1);
712 P2(rec.lno) := nvl(rec.p_2,-1);
713 P3(rec.lno) := nvl(rec.p_3,-1);
714 P4(rec.lno) := nvl(rec.p_4,-1);
715 P5(rec.lno) := nvl(rec.p_5,-1);
716
717 -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
718 -- START BUG 5228046
719 P6(rec.lno) := nvl(rec.p_6,-1);
720 P7(rec.lno) := nvl(rec.p_7,-1);
721 P8(rec.lno) := nvl(rec.p_8,-1);
722 P9(rec.lno) := nvl(rec.p_9,-1);
723 P10(rec.lno) := nvl(rec.p_10,-1);
724 -- END BUG 5228046
725
726
727 IF rec.tax_type_val = 1 THEN
728 tax_rate_tab(rec.lno) := -1;
729 P1(rec.lno) := -1;
730 P2(rec.lno) := -1;
731 P3(rec.lno) := -1;
732 P4(rec.lno) := -1;
733 P5(rec.lno) := -1;
734
735 -- Date 01-NOV-2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
736 -- START BUG 5228046
737 P6(rec.lno) := -1;
738 P7(rec.lno) := -1;
739 P8(rec.lno) := -1;
740 P9(rec.lno) := -1;
741 P10(rec.lno) := -1;
742 -- END BUG 5228046
743 Tax_Amt_Tab(rec.lno) := v_excise_duty_rate;
744 tax_target_tab(rec.lno) := v_excise_duty_rate;
745 END IF;
746 ELSIF excise_flag_set AND rec.tax_type_val = 1 THEN
747 P1(rec.lno) := -1;
748 P2(rec.lno) := -1;
749 P3(rec.lno) := -1;
750 P4(rec.lno) := -1;
751 P5(rec.lno) := -1;
752
753 -- Date 01-NOV-2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
754 -- START BUG 5228046
755 P6(rec.lno) := -1;
756 P7(rec.lno) := -1;
757 P8(rec.lno) := -1;
758 P9(rec.lno) := -1;
759 P10(rec.lno) := -1;
760 -- END BUG 5228046
761 tax_rate_tab(rec.lno) := -1;
762 Tax_Amt_Tab(rec.lno) := 0;
763 tax_target_tab(rec.lno) := 0;
764 END IF;
765
766 IF v_exempt_flag = 'Y' AND rec.tax_type_val = 1 THEN
767 P1(rec.lno) := -1;
768 P2(rec.lno) := -1;
769 P3(rec.lno) := -1;
770 P4(rec.lno) := -1;
771 P5(rec.lno) := -1;
772
773 -- Date 01-NOV-2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
774 -- START BUG 5228046
775
776 P6(rec.lno) := -1;
777 P7(rec.lno) := -1;
778 P8(rec.lno) := -1;
779 P9(rec.lno) := -1;
780 P10(rec.lno) := -1;
781
782 -- END BUG 5228046
783
784 tax_rate_tab(rec.lno) := -1;
785 Tax_Amt_Tab(rec.lno) := 0;
786 tax_target_tab(rec.lno) := 0;
787
788 END IF;
789 Rounding_factor_tab(rec.lno) := rec.rnd;
790 Tax_Type_Tab(rec.lno) := rec.tax_type_val;
791 IF nvl(rec.tax_rate,0) = 0 AND nvl(rec.qty_rate,0) = 0 THEN
792 tax_rate_tab(rec.lno) := -1;
793
794 -- Start of addition by Srihari and Gaurav on 11-JUL-2000
795
796 IF rec.tax_type_val = 1
797 THEN
798 Tax_Amt_Tab(rec.lno) := v_excise_duty_rate;
799 tax_target_tab(rec.lno) := v_excise_duty_rate;
800 ELSE
801
802 -- End of addition by Srihari and Gaurav on 11-JUL-2000
803
804 Tax_Amt_Tab(rec.lno) := rec.tax_amount;
805 tax_target_tab(rec.lno) := rec.tax_amount;
806
807 END IF;
808
809 ELSE
810 IF rec.tax_type_val <> 1 THEN
811 Tax_Amt_Tab(rec.lno) := 0;
812 END IF;
813 END IF;
814
815
816 Code modified by aiyer for the bug 3433634 .
817 Set the end_date_tab to 1 whenever valid_date is null or has an end date greater than or equal to
818 sysdate.
819 Only if the end date is not null and less than sysdate, set the end_date_tab variable to zero.
820
821 IF rec.valid_date >= to_char(sysdate,'DD-MON-YYYY') THEN
822 End_Date_Tab(rec.lno) := 1;
823 ELSE
824 End_Date_Tab(rec.lno) := 0;
825 END IF;
826
827 row_count := row_count + 1;
828
829 IF tax_rate_tab(rec.lno) = 0 THEN
830 FOR uom_cls IN uom_class_cur(v_unit_code, rec.uom) LOOP
831 INV_CONVERT.inv_um_conversion(v_unit_code, rec.uom, v_inventory_item_id, v_conversion_rate);
832 IF nvl(v_conversion_rate, 0) <= 0 THEN
833 INV_CONVERT.inv_um_conversion(v_unit_code, rec.uom, 0, v_conversion_rate);
834 IF nvl(v_conversion_rate, 0) <= 0 THEN
835 v_conversion_rate := 0;
836 END IF;
837 END IF;
838 IF ( excise_flag_set ) AND ( rec.tax_type_val = 1 ) THEN
839 tax_amt_tab(rec.lno) := 0;
840 ELSE
841 tax_amt_tab(rec.lno) := ROUND( nvl(rec.qty_rate * v_conversion_rate, 0) * v_original_quantity, rounding_factor_tab(rec.lno) );
842 END IF;
843 IF v_exempt_flag = 'Y' AND rec.tax_type_val = 1 THEN
844 tax_amt_tab( rec.lno ) := 0;
845 END IF;
846 tax_rate_tab( rec.lno ) := -1;
847 tax_target_tab(rec.lno) := tax_amt_tab( rec.lno );
848 END LOOP;
849 END IF;
850 IF rec.tax_type_val = 1 THEN
851 excise_flag_set := TRUE;
852 END IF;
853 END LOOP;
854
855 END IF;
856
857 bsln_amt := v_selling_price * v_original_quantity;
858 --v_original_quantity;
859 --bsln_amt := 90;
860
861
862 FOR I in 1..row_count
863 LOOP
864 IF p1(I) < I and p1(I) not in (-1,0) then
865 vamt := vamt + nvl(tax_amt_tab(p1(I)),0);
866 ELSIF p1(I) = 0 then
867 vamt := vamt + bsln_amt;
868 END IF;
869 IF p2(I) < I and p2(I) not in (-1,0) then
870 vamt := vamt + nvl(tax_amt_tab(p2(I)),0);
871 ELSIF p2(I) = 0 then
872 vamt := vamt + bsln_amt;
873 END IF;
874 IF p3(I) < I and p3(I) not in (-1,0) then
875 vamt := vamt + nvl(tax_amt_tab(p3(I)),0);
876 ELSIF p3(I) = 0 then
877 vamt := vamt + bsln_amt;
878 END IF;
879 IF p4(I) < I and p4(I) not in (-1,0) then
880 vamt := vamt + nvl(tax_amt_tab(p4(I)),0);
881 ELSIF p4(I) = 0 then
882 vamt := vamt + bsln_amt;
883 END IF;
884 IF p5(I) < I and p5(I) not in (-1,0) then
885 vamt := vamt + nvl(tax_amt_tab(p5(I)),0);
886 ELSIF p5(I) = 0 then
887 vamt := vamt + bsln_amt;
888 END IF;
889
890
891 -- Date 01-NOV-2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
892 -- START BUG 5228046
893
894 IF p6(I) < I and p6(I) not in (-1,0) then
895 vamt := vamt + nvl(tax_amt_tab(p6(I)),0);
896 ELSIF p6(I) = 0 then
897 vamt := vamt + bsln_amt;
898 END IF;
899 IF p7(I) < I and p7(I) not in (-1,0) then
900 vamt := vamt + nvl(tax_amt_tab(p7(I)),0);
901 ELSIF p7(I) = 0 then
902 vamt := vamt + bsln_amt;
903 END IF;
904 IF p8(I) < I and p8(I) not in (-1,0) then
905 vamt := vamt + nvl(tax_amt_tab(p8(I)),0);
906 ELSIF p8(I) = 0 then
907 vamt := vamt + bsln_amt;
908 END IF;
909 IF p9(I) < I and p9(I) not in (-1,0) then
910 vamt := vamt + nvl(tax_amt_tab(p9(I)),0);
911 ELSIF p9(I) = 0 then
912 vamt := vamt + bsln_amt;
913 END IF;
914 IF p10(I) < I and p10(I) not in (-1,0) then
915 vamt := vamt + nvl(tax_amt_tab(p10(I)),0);
916 ELSIF p10(I) = 0 then
917 vamt := vamt + bsln_amt;
918 END IF;
919
920 -- END BUG 5228046
921
922 IF tax_rate_tab(I) <> -1 THEN
923 v_tax_amt := v_tax_amt + (vamt * (tax_rate_tab(I)/100));
924 IF END_date_tab(I) = 0 then
925 tax_amt_tab(I) := 0;
926 ELSIF END_date_tab(I) = 1 then
927 tax_amt_tab(I) := nvl(tax_amt_tab(I),0) + v_tax_amt;
928 END IF;
929 -- tax_amt_tab(I) := nvl(tax_amt_tab(I),0) + v_tax_amt;
930 END IF;
931 vamt := 0;
932 v_tax_amt := 0;
933 END LOOP;
934 FOR I in 1..row_count
935 LOOP
936 IF p1(I) > I then
937 vamt := vamt + nvl(tax_amt_tab(p1(I)),0);
938 END IF;
939 IF p2(I) > I then
940 vamt := vamt + nvl(tax_amt_tab(p2(I)),0);
941 END IF;
942 IF p3(I) > I then
943 vamt := vamt + nvl(tax_amt_tab(p3(I)),0);
944 END IF;
945 IF p4(I) > I then
946 vamt := vamt + nvl(tax_amt_tab(p4(I)),0);
947 END IF;
948 IF p5(I) > I then
949 vamt := vamt + nvl(tax_amt_tab(p5(I)),0);
950 END IF;
951
952 -- Date 01-NOV-2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
953 -- START BUG 5228046
954
955 IF p6(I) > I then
956 vamt := vamt + nvl(tax_amt_tab(p6(I)),0);
957 END IF;
958 IF p7(I) > I then
959 vamt := vamt + nvl(tax_amt_tab(p7(I)),0);
960 END IF;
961 IF p8(I) > I then
962 vamt := vamt + nvl(tax_amt_tab(p8(I)),0);
963 END IF;
964 IF p9(I) > I then
965 vamt := vamt + nvl(tax_amt_tab(p9(I)),0);
966 END IF;
967 IF p10(I) > I then
968 vamt := vamt + nvl(tax_amt_tab(p10(I)),0);
969 END IF;
970
971 -- END BUG 5228046
972
973 IF tax_rate_tab(I) <> -1 THEN
974 v_tax_amt := v_tax_amt + (vamt * (tax_rate_tab(I)/100));
975 IF END_date_tab(I) = 0 then
976 tax_amt_tab(I) := 0;
977 ELSIF END_date_tab(I) = 1 then
978 tax_amt_tab(I) := nvl(tax_amt_tab(I),0) + v_tax_amt;
979 END IF;
980 -- tax_amt_tab(I) := nvl(tax_amt_tab(I),0) + v_tax_amt;
981 END IF;
982 vamt := 0;
983 v_tax_amt := 0;
984 END LOOP;
985
986 FOR counter IN 1 .. max_iter LOOP
987 vamt := 0;
988 v_tax_amt := 0;
989 FOR i IN 1 .. row_count LOOP
990 IF tax_rate_tab( i ) <> 0 AND End_Date_Tab(I) <> 0 AND tax_rate_tab( i ) <> -1 THEN
991 v_amt := bsln_amt;
992
993 IF p1( i ) <> -1 THEN
994 IF p1( i ) <> 0 THEN
995 vamt := vamt + tax_amt_tab( p1( I ) );
996 ELSIF p1(i) = 0 THEN
997 vamt := vamt + v_amt;
998 END IF;
999 END IF;
1000 IF p2( i ) <> -1 THEN
1001 IF p2( i ) <> 0 THEN
1002 vamt := vamt + tax_amt_tab( p2( I ) );
1003 ELSIF p2(i) = 0 THEN
1004 vamt := vamt + v_amt;
1005 END IF;
1006 END IF;
1007 IF p3( i ) <> -1 THEN
1008 IF p3( i ) <> 0 THEN
1009 vamt := vamt + tax_amt_tab( p3( I ) );
1010 ELSIF p3(i) = 0 THEN
1011 vamt := vamt + v_amt;
1012 END IF;
1013 END IF;
1014 IF p4( i ) <> -1 THEN
1015 IF p4( i ) <> 0 THEN
1016 vamt := vamt + tax_amt_tab( p4( i ) );
1017 ELSIF p4(i) = 0 THEN
1018 vamt := vamt + v_amt;
1019 END IF;
1020 END IF;
1021 IF p5( i ) <> -1 THEN
1022 IF p5( i ) <> 0 THEN
1023 vamt := vamt + tax_amt_tab( p5( i ) );
1024 ELSIF p5(i) = 0 THEN
1025 vamt := vamt + v_amt;
1026 END IF;
1027 END IF;
1028
1029
1030 -- Date 01-NOV-2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
1031 -- START BUG 5228046
1032
1033 IF p6( i ) <> -1 THEN
1034 IF p6( i ) <> 0 THEN
1035 vamt := vamt + tax_amt_tab( p6( I ) );
1036 ELSIF p6(i) = 0 THEN
1037 vamt := vamt + v_amt;
1038 END IF;
1039 END IF;
1040 IF p7( i ) <> -1 THEN
1041 IF p7( i ) <> 0 THEN
1042 vamt := vamt + tax_amt_tab( p7( I ) );
1043 ELSIF p7(i) = 0 THEN
1044 vamt := vamt + v_amt;
1045 END IF;
1046 END IF;
1047 IF p8( i ) <> -1 THEN
1048 IF p8( i ) <> 0 THEN
1049 vamt := vamt + tax_amt_tab( p8( I ) );
1050 ELSIF p8(i) = 0 THEN
1051 vamt := vamt + v_amt;
1052 END IF;
1053 END IF;
1054 IF p9( i ) <> -1 THEN
1055 IF p9( i ) <> 0 THEN
1056 vamt := vamt + tax_amt_tab( p9( i ) );
1057 ELSIF p9(i) = 0 THEN
1058 vamt := vamt + v_amt;
1059 END IF;
1060 END IF;
1061 IF p10( i ) <> -1 THEN
1062 IF p10( i ) <> 0 THEN
1063 vamt := vamt + tax_amt_tab( p10( i ) );
1064 ELSIF p10(i) = 0 THEN
1065 vamt := vamt + v_amt;
1066 END IF;
1067 END IF;
1068
1069 -- END BUG 5228046
1070
1071 tax_target_tab(I) := vamt;
1072 IF counter = max_iter THEN
1073 -- v_tax_amt := ROUND( v_tax_amt + ( vamt * ( tax_rate_tab( i )/100)), rounding_factor_tab(I) );
1074 v_tax_amt := ( v_tax_amt + ( vamt * ( tax_rate_tab( i )/100)));
1075
1076
1077 ELSE
1078
1079 v_tax_amt := v_tax_amt + ( vamt * ( tax_rate_tab( i )/100));
1080
1081 END IF;
1082 tax_amt_tab( I ) := NVL( v_tax_amt, 0 );
1083
1084 ELSIF tax_rate_tab( i ) = -1 AND End_Date_Tab(I) <> 0 THEN
1085 NULL;
1086 ELSE
1087 tax_amt_tab(I) := 0;
1088 tax_target_tab(I) := 0;
1089 END IF;
1090
1091 IF counter = max_iter THEN
1092 IF END_date_tab(I) = 0 THEN
1093 tax_amt_tab(I) := 0;
1094 END IF;
1095 END IF;
1096
1097 vamt := 0;
1098 v_amt := 0;
1099 v_tax_amt := 0;
1100 END LOOP;
1101 END LOOP;
1102
1103 FOR i IN 1 .. row_count LOOP
1104 IF p_order_invoice = 'O' THEN
1105 OPEN Fetch_Totals_Cur( i );
1106 FETCH Fetch_Totals_Cur INTO v_cum_amount;
1107 CLOSE Fetch_Totals_Cur;
1108 IF p_line_quantity = 0 THEN
1109 DELETE JAI_CMN_MATCH_TAXES
1110 WHERE Ref_Line_Id = p_ref_line_id
1111 AND nvl(Subinventory,'###') = nvl(p_subinventory,'###')
1112 AND receipt_id = p_receipt_id
1113 AND Tax_Line_No = i;
1114 ELSE
1115 UPDATE JAI_CMN_MATCH_TAXES
1116 SET Tax_Amount = tax_amt_tab(i),
1117 Base_Tax_Amount = tax_target_tab(i),
1118 Func_Tax_Amount = tax_amt_tab(i) ,
1119 --* NVL( p_curr_conv_factor, 1 ),
1120 Total_Tax_Amount = v_cum_amount
1121 WHERE Ref_Line_Id = p_ref_line_id
1122 AND nvl(Subinventory,'###') = nvl(p_subinventory,'###')
1123 AND receipt_id = p_receipt_id
1124 AND Tax_Line_No = i;
1125
1126
1127 update JAI_OPM_SO_PICK_TAXES
1128 Set tax_amount = tax_amt_tab(i)
1129 Where bol_id = p_ref_line_id
1130 and bolline_no = p_line_no
1131 AND Tax_Line_No = i;
1132
1133
1134 END IF;
1135 -- END IF;
1136 -- OPEN Fetch_Matched_Qty_AR_Cur;
1137 -- FETCH Fetch_Matched_Qty_AR_Cur Into v_matched_quantity;
1138 -- CLOSE Fetch_Matched_Qty_AR_Cur;
1139
1140 IF p_line_quantity <> 0 THEN
1141 FOR Rec IN Fetch_Total_AR_Cur( i ) LOOP
1142 UPDATE JAI_AR_TRX_TAX_LINES
1143 SET Tax_Amount = rec.tax_amount,
1144 Base_Tax_Amount = rec.base_tax_amount,
1145 Func_Tax_Amount = rec.func_tax_amount
1146 WHERE link_to_cust_trx_line_id = p_ref_line_id
1147 AND Tax_Line_No = i;
1148 END LOOP;
1149 ELSE
1150 UPDATE JAI_AR_TRX_TAX_LINES
1151 SET Tax_Amount = tax_amt_tab(i),
1152 Base_Tax_Amount = tax_target_tab(i),
1153 Func_Tax_Amount = tax_amt_tab(i) * NVL( p_curr_conv_factor, 1 )
1154 WHERE link_to_cust_trx_line_id = p_ref_line_id
1155 AND Tax_Line_No = i;
1156 END IF;
1157 END IF;
1158 END LOOP;
1159
1160 END opm_default_taxes;
1161 */
1162
1163 PROCEDURE ar_default_taxes( p_ref_line_id IN NUMBER,
1164 p_customer_id IN NUMBER,
1165 p_link_to_cust_trx_line_id NUMBER,
1166 p_curr_conv_factor IN NUMBER,
1167 p_receipt_id IN NUMBER,
1168 p_qty IN NUMBER )
1169
1170 IS
1171
1172 v_qty NUMBER; -- := p_qty; --Ramananda for File.Sql.35
1173 v_n_tax_line_no NUMBER;
1174 v_tax_line_no NUMBER := 0;
1175 v_matched_quantity NUMBER := 0;
1176 v_last_update_date Date; -- := Sysdate; --Ramananda for File.Sql.35
1177 v_shipment_line_id Number; --added by Vijay on 9-Oct-2001 for Tar# 9445972.700
1178
1179 /* CURSOR Fetch_Matched_Qty_AR_Cur IS
1180 SELECT matched_quantity
1181 FROM JAI_AR_TRX_LINES
1182 WHERE Customer_Trx_Line_Id = p_ref_line_id;*/
1183
1184 CURSOR Fetch_AR_Line_Info_Cur IS
1185 SELECT nvl(quantity * nvl(assessable_value, line_amount),0) assessable_value, line_amount, unit_code, inventory_item_id, quantity,
1186 tax_category_id, customer_trx_id, creation_date, created_by, last_updated_by, last_update_login
1187 FROM JAI_AR_TRX_LINES
1188 WHERE Customer_Trx_Line_Id = p_ref_line_id;
1189
1190 CURSOR Chk_New_Added_Tax_Cur IS
1191 SELECT NVL( MAX( Tax_Line_No ), 0 )
1192 FROM JAI_AR_TRX_TAX_LINES
1193 WHERE Link_To_Cust_Trx_Line_Id = p_link_to_cust_trx_line_id;
1194
1195 CURSOR Chk_Tax_Count_Cur IS
1196 SELECT NVL( MAX( Tax_Line_No ), 0 )
1197 FROM JAI_CMN_MATCH_TAXES
1198 WHERE Ref_Line_Id = p_ref_line_id;
1199
1200 CURSOR Fetch_New_Taxes_Cur IS
1201 SELECT *
1202 FROM JAI_AR_TRX_TAX_LINES
1203 WHERE Link_To_Cust_Trx_Line_Id = p_link_to_cust_trx_line_id
1204 AND Tax_Line_No > nvl(v_tax_line_no,0)
1205 ORDER BY Tax_Line_No;
1206
1207 --Start addition by Vijay on 09-Oct-2001 for TAR# 9445972.700
1208 --Added Cursor to fetch taxes from Receipts
1209
1210 -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
1211
1212 CURSOR Get_Receipt_taxes IS SELECT a.tax_id tax_id_po, a.tax_line_no tax_line_no_po, c.tax_id , c.tax_line_no,
1213 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,
1214 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,
1215 c.qty_rate, c.tax_rate, c.tax_amount, c.uom
1216 FROM JAI_PO_TAXES a, JAI_CMN_TAXES_ALL b, JAI_RCV_LINE_TAXES c
1217 WHERE c.tax_line_no > nvl(v_tax_line_no,0)
1218 AND a.tax_id = b.tax_id
1219 AND c.tax_id = b.tax_id
1220 AND (c.shipment_line_id,a.line_location_id) = (SELECT shipment_line_id,po_line_location_id
1221 FROM rcv_transactions
1222 WHERE transaction_id = (select receipt_ref
1223 from JAI_CMN_RG_23D_TRXS
1224 where register_id = p_receipt_id))
1225 ORDER BY c.tax_line_no;
1226 --End addition by Vijay on 09-Oct-2001 for TAR# 9445972.700
1227
1228 BEGIN
1229
1230
1231
1232 /*------------------------------------------------------------------------------------------
1233 FILENAME: Ja_In_Rg23_D_AR_p.sql
1234
1235 CHANGE HISTORY:
1236 S.No Date Author and Details
1237 1. 09/10/2001 A.Vijay Kumar Version#115.0
1238 Added a cursor Get_Receipt_taxes to fetch taxes from Receipts
1239 When Manual Invoice is matched against a Receipt
1240
1241 2. 10/01/2005 brathod for Bug#4111609 Version#115.1
1242 Commented insert into JAI_CMN_MATCH_TAXES
1243 since it is not required
1244 base bug# 4146708 creates objects
1245
1246 --------------------------------------------------------------------------------------------*/
1247 v_qty := p_qty; --Ramananda for File.Sql.35
1248 v_last_update_date := Sysdate; --Ramananda for File.Sql.35
1249
1250 OPEN Chk_New_Added_Tax_Cur;
1251 FETCH Chk_New_Added_Tax_Cur INTO v_n_tax_line_no;
1252 CLOSE Chk_New_Added_Tax_Cur;
1253
1254 OPEN Chk_Tax_Count_Cur;
1255 FETCH Chk_Tax_Count_Cur INTO v_tax_line_no;
1256 CLOSE Chk_Tax_Count_Cur;
1257
1258 --Start addition by Vijay on 09-Oct-2001 for TAR# 9445972.700
1259 /* OPEN Get_shipment_line_id;
1260 FETCH Get_shipment_line_id INTO v_shipment_line_id;
1261 CLOSE Get_Shipment_line_id;*/
1262
1263
1264 -- FOR rec IN Fetch_Rcpt_Dtls_Cur LOOP
1265 -- IF nvl(v_tax_line_no,0) <> v_n_tax_line_no THEN
1266 -- FOR rec1 IN Fetch_New_Taxes_Cur LOOP --commented by Vijay on 09-Oct-2001 for TAR# 9445972.700
1267 FOR rec1 IN Get_Receipt_taxes --Added by Vijay on 09-Oct-2001 for TAR# 9445972.700
1268 LOOP
1269 IF nvl(v_tax_line_no,0) <> rec1.tax_line_no THEN
1270
1271 /* bug# 4111609 insertion not required. So code commented */
1272
1273 /*
1274 INSERT INTO JAI_CMN_MATCH_TAXES(MATCH_TAX_ID, REF_LINE_ID,
1275 SUBINVENTORY,
1276 TAX_LINE_NO,
1277 PRECEDENCE_1,
1278 PRECEDENCE_2,
1279 PRECEDENCE_3,
1280 PRECEDENCE_4,
1281 PRECEDENCE_5,
1282 PRECEDENCE_6, -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
1283 PRECEDENCE_7,
1284 PRECEDENCE_8,
1285 PRECEDENCE_9,
1286 PRECEDENCE_10,
1287 TAX_ID,
1288 TAX_RATE,
1289 QTY_RATE,
1290 UOM,
1291 TAX_AMOUNT,
1292 BASE_TAX_AMOUNT,
1293 FUNC_TAX_AMOUNT,
1294 TOTAL_TAX_AMOUNT,
1295 CREATION_DATE,
1296 CREATED_BY,
1297 LAST_UPDATE_DATE,
1298 LAST_UPDATE_LOGIN,
1299 LAST_UPDATED_BY,
1300 RECEIPT_ID,
1301 ORDER_INVOICE )
1302 VALUES ( JAI_CMN_MATCH_TAXES_S.nextval, p_ref_line_id,
1303 NULL,
1304 rec1.tax_line_no,
1305 rec1.p_1,
1306 rec1.p_2,
1307 rec1.p_3,
1308 rec1.p_4,
1309 rec1.p_5,
1310 rec1.p_6, -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
1311 rec1.p_7,
1312 rec1.p_8,
1313 rec1.p_9,
1314 rec1.p_10,
1315 rec1.tax_id,
1316 rec1.tax_rate,
1317 rec1.qty_rate,
1318 rec1.uom,
1319 0,
1320 0,
1321 0,
1322 0,
1323 SYSDATE,
1324 UID,
1325 SYSDATE,
1326 UID,
1327 UID,
1328 p_receipt_id,
1329 'I' );
1330 */
1331
1332 /* end bug#4111609 */
1333 null;
1334 END IF;
1335 END LOOP;
1336 -- END IF;
1337 /* OPEN Fetch_Matched_Qty_AR_Cur;
1338 FETCH Fetch_Matched_Qty_AR_Cur Into v_matched_quantity;
1339 CLOSE Fetch_Matched_Qty_AR_Cur;
1340 */
1341 -- IF v_matched_quantity <> 0 THEN
1342 if p_qty <> 0 then
1343 jai_cmn_rcv_matching_pkg.om_default_taxes( NULL,
1344 p_customer_id,
1345 p_ref_line_id,
1346 p_receipt_id,
1347 p_link_to_cust_trx_line_id,
1348 p_qty,
1349 p_curr_conv_factor,
1350 'I' );
1351 ELSE
1352 DELETE JAI_CMN_MATCH_TAXES
1353 WHERE Ref_Line_Id = p_ref_line_id
1354 AND receipt_id = p_receipt_id;
1355
1356 FOR Rec IN Fetch_AR_Line_Info_Cur LOOP
1357 jai_ar_utils_pkg.recalculate_tax('AR_LINES_UPDATE' , rec.tax_category_id , rec.customer_trx_id , p_ref_line_id,
1358 rec.assessable_value , rec.line_amount , 1, rec.inventory_item_id ,rec.quantity,
1359 rec.unit_code , NULL , NULL ,rec.creation_date , rec.created_by ,
1360 v_last_update_date , rec.last_updated_by , rec.last_update_login );
1361 END LOOP;
1362 END IF;
1363 --END LOOP;
1364
1365 END ar_default_taxes;
1366
1367 PROCEDURE om_default_taxes(
1368 p_subinventory IN VARCHAR2,
1369 p_customer_id IN NUMBER,
1370 p_ref_line_id IN NUMBER,
1371 p_receipt_id IN NUMBER,
1372 p_line_id NUMBER, -- For OE it is Line ID, for AR it is Link to cust trx line id
1373 p_line_quantity IN NUMBER,
1374 p_curr_conv_factor IN NUMBER,
1375 p_order_invoice IN VARCHAR2
1376 ) IS
1377
1378 TYPE Num_Tab IS TABLE OF NUMBER(25,3) INDEX BY BINARY_INTEGER;
1379 TYPE Tax_Amt_Num_Tab IS TABLE OF NUMBER(25,4) INDEX BY BINARY_INTEGER;
1380 TYPE Flag_Tab IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER; /* Added for bug 5091874 */
1381
1382 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rcv_matching_pkg.om_default_taxes'; /* Added by Ramananda for bug#4407165 */
1383
1384 --Add by Kevin Cheng for inclusive tax Dec 12, 2007
1385 ---------------------------------------------------
1386 TYPE CHAR_TAB IS TABLE OF VARCHAR2(10)
1387 INDEX BY BINARY_INTEGER;
1388
1389 lt_adhoc_tax_tab CHAR_TAB;
1390 lt_inclu_tax_tab CHAR_TAB;
1391 lt_tax_rate_per_rupee NUM_TAB;
1392 lt_cumul_tax_rate_per_rupee NUM_TAB;
1393 lt_tax_rate_zero_tab NUM_TAB;
1394 lt_tax_amt_rate_tax_tab TAX_AMT_NUM_TAB;
1395 lt_tax_amt_non_rate_tab TAX_AMT_NUM_TAB;
1396 lt_base_tax_amt_tab TAX_AMT_NUM_TAB;
1397 base_tax_amount_nr_tab tax_amt_num_tab;--bug#9794835
1398 lt_func_tax_amt_tab TAX_AMT_NUM_TAB;
1399 lv_uom_code VARCHAR2(10) := 'EA';
1400 lv_register_code VARCHAR2(20);
1401 ln_inventory_item_id NUMBER;
1402 ln_exclusive_price NUMBER;
1403 ln_total_non_rate_tax NUMBER := 0;
1404 ln_total_inclusive_factor NUMBER;
1405 ln_bsln_amt_nr NUMBER :=0;
1406 ln_currency_conv_factor NUMBER;
1407 ln_tax_amt_nr NUMBER(38,10) := 0;
1408 ln_func_tax_amt NUMBER(38,10) := 0;
1409 ln_vamt_nr NUMBER(38,10) := 0;
1410 ln_excise_jb NUMBER;
1411 ln_total_tax_per_rupee NUMBER;
1412 ln_assessable_value_tmp NUMBER;
1413 ln_vat_assessable_value_tmp NUMBER;
1414 ln_assessable_value_tmp_tot NUMBER;
1415 ln_vat_assessable_value_tot NUMBER;
1416 ln_vat_reversal_value_tmp NUMBER; -- bug 8887871
1417 ln_vat_reversal_value_tot NUMBER; --bug 8887871
1418 ln_gst_assessable_value_tot NUMBER; --Add by Xiao for GST, refer to bug#10043656
1419 ln_gst_assessable_value_tmp NUMBER; --Add by Xiao for GST, refer to bug#10043656
1420 ln_line_amount NUMBER;
1421 ---------------------------------------------------
1422
1423 -- added by Vijay Shankar for Bug# 3781299
1424 lv_excise_cess_code VARCHAR2(25); -- := 'EXCISE_EDUCATION_CESS'; --Ramananda for File.Sql.35
1425 lv_sh_excise_cess_code VARCHAR2(25) := JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS;/*Bug 5989740 bduvarag*/
1426 lv_cvd_cess_code VARCHAR2(25); -- := 'CVD_EDUCATION_CESS'; --Ramananda for File.Sql.35
1427 lv_sh_cvd_cess_code VARCHAR2(25) := JAI_CONSTANTS.TAX_TYPE_SH_CVD_EDU_CESS;/*Bug 5989740 bduvarag*/
1428 ln_cess_check NUMBER := -1;
1429 ln_sh_cess_check NUMBER := -1;/*Bug 5989740 bduvarag*/
1430 ln_transaction_id NUMBER(15);
1431 ln_amount number; --Added by Shujuan for 10358786 on 28-Jan-2011
1432 p1 num_tab;
1433 p2 num_tab;
1434 p3 num_tab;
1435 p4 num_tab;
1436 p5 num_tab;
1437
1438 -- Date 01-NOV-2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
1439 -- START BUG 5228046
1440
1441 p6 num_tab;
1442 p7 num_tab;
1443 p8 num_tab;
1444 p9 num_tab;
1445 p10 num_tab;
1446
1447 -- END BUG 5228046
1448
1449 tax_rate_tab num_tab;
1450 tax_type_tab num_tab;
1451 end_date_tab num_tab;
1452 tax_amt_tab tax_amt_num_tab;
1453 tax_target_tab tax_amt_num_tab;
1454 rounding_factor_tab tax_amt_num_tab;
1455 adhoc_flag_tab Flag_tab ; /* Added bug 5091874 */
1456
1457 v_exempt_flag VARCHAR2(2); --:= 'N'; --Ramananda for File.Sql.35
1458 v_address_id NUMBER;
1459 i NUMBER;
1460 excise_flag_set BOOLEAN := FALSE;
1461 v_inventory_item_id NUMBER;
1462 v_unit_code VARCHAR2(15);
1463 v_selling_price NUMBER;
1464 v_amt NUMBER;
1465 v_cum_amount NUMBER;
1466 bsln_amt NUMBER;
1467 row_count NUMBER := 0;
1468 v_tax_amt NUMBER(14,4) := 0;
1469 vamt NUMBER(14,4) := 0;
1470 v_conversion_rate NUMBER;
1471 counter NUMBER;
1472 max_iter NUMBER := 10;
1473 conv_rate NUMBER;
1474 v_count NUMBER := 0;
1475 v_original_quantity NUMBER := 0;
1476 v_matched_quantity NUMBER := 0;
1477 v_excise_duty_rate NUMBER;
1478 ln_cess_duty_rate NUMBER := 0;
1479 ln_sh_cess_duty_rate NUMBER := 0;/*Bug 5989740 bduvarag*/
1480 v_e_s varchar2(10);
1481 ln_vat_assessable_value NUMBER ;
1482 ln_gst_assessable_value NUMBER ; --Add by Xiao for GST, refer to bug#10043656
1483
1484 CURSOR oe_tax_cur(p_excise_cess_cnt IN NUMBER , p_sh_excise_cess_cnt IN number ) IS/*Bug 5989740 bduvarag*/ ---- OE Side
1485 SELECT
1486 a.tax_id ,
1487 a.tax_line_no lno ,
1488 a.precedence_1 p_1 ,
1489 a.precedence_2 p_2 ,
1490 a.precedence_3 p_3 ,
1491 a.precedence_4 p_4 ,
1492 a.precedence_5 p_5 ,
1493 a.precedence_6 p_6 , -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
1494 a.precedence_7 p_7 ,
1495 a.precedence_8 p_8 ,
1496 a.precedence_9 p_9 ,
1497 a.precedence_10 p_10 ,
1498 a.qty_rate ,
1499 -- Vijay Shankar for Bug# 3781299
1500 decode(b.tax_type, lv_excise_cess_code, decode(p_excise_cess_cnt, 0, 0, a.tax_rate),
1501 lv_sh_excise_cess_code, decode(p_sh_excise_cess_cnt, 0, 0, a.tax_rate), --Added by sacsethi for Bug no 5907436
1502 a.tax_rate) tax_rate, -- a.tax_rate/*Bug 5989740 bduvarag*/
1503 decode(b.tax_type, lv_excise_cess_code, decode(p_excise_cess_cnt, 0, 0, a.tax_amount),
1504 lv_sh_excise_cess_code, decode(p_sh_excise_cess_cnt, 0, 0, a.tax_amount), --Added by sacsethi for Bug no 5907436
1505 a.tax_amount) tax_amount, -- a.tax_amount ,/*Bug 5989740 bduvarag*/
1506 a.uom ,
1507 b.end_date valid_date ,
1508 DECODE(aa.regime_code, jai_constants.cgst_regime, 7 -- Add by Xiao for GST refer to bug#10043656.
1509 , jai_constants.sgst_regime, 7, -- Add by Xiao for GST refer to bug#10043656.
1510 decode(upper(b.tax_type),'EXCISE', 1,
1511 'ADDL. EXCISE', 1,
1512 'OTHER EXCISE', 1,
1513 'TDS', 2,
1514 lv_excise_cess_code,3, -- bug#4111609 excise cess
1515 lv_cvd_cess_code,3, -- bug#4111609 cvd cess
1516 lv_sh_excise_cess_code,4, --added by sacsethi for budget07 enhancement
1517 lv_sh_cvd_cess_code,4, --added by sacsethi for budget07 enhancement
1518 /* Added for bug#8887871 */
1519 (SELECT jrttv.tax_type
1520 FROM jai_regime_tax_types_v jrttv
1521 WHERE jrttv.tax_type = upper(b.tax_type)
1522 AND jrttv.regime_code = jai_constants.vat_regime), 5,
1523 'VAT REVERSAL', 6, 0)) tax_type_val,
1524 b.mod_cr_Percentage ,
1525 b.vendor_id ,
1526 b.tax_type ,
1527 NVL( b.rounding_factor, 0 ) rnd ,
1528 b.adhoc_flag /* Added bug 5091874 */
1529 , b.inclusive_tax_flag --Add by Kevin Cheng for inclusive tax Dec 12, 2007
1530 FROM
1531 JAI_OM_OE_SO_TAXES a ,
1532 jai_regime_tax_types_v aa, -- Add by Xiao for GST refer to bug#10043656.
1533 JAI_CMN_TAXES_ALL b
1534 WHERE a.line_id = p_line_id
1535 AND a.tax_id = b.tax_id
1536 AND aa.tax_type(+) = b.tax_type -- Add by Xiao for GST refer to bug#10043656.
1537 ORDER BY
1538 a.tax_line_no;
1539
1540 -- the following cursor un-commented by sriram - bug 3179379 - 12/11/2003
1541
1542 CURSOR interorg_xfer_tax_cur IS ---- Interorg XFER bug 6030615
1543 SELECT
1544 a.tax_id ,
1545 a.tax_line_no lno ,
1546 a.precedence_1 p_1 ,
1547 a.precedence_2 p_2 ,
1548 a.precedence_3 p_3 ,
1549 a.precedence_4 p_4 ,
1550 a.precedence_5 p_5 ,
1551 a.precedence_6 p_6 ,
1552 a.precedence_7 p_7 ,
1553 a.precedence_8 p_8 ,
1554 a.precedence_9 p_9 ,
1555 a.precedence_10 p_10 ,
1556 a.qty_rate ,
1557 a.tax_rate tax_rate,
1558 a.tax_amt tax_amount ,
1559 a.tax_amt func_tax_amount,
1560 c.transaction_uom uom,
1561 b.end_date valid_date ,
1562 DECODE(aa.regime_code, jai_constants.cgst_regime, 7 -- Add by Xiao for GST refer to bug#10043656.
1563 , jai_constants.sgst_regime, 7, -- Add by Xiao for GST refer to bug#10043656.
1564 decode(upper(b.tax_type),'EXCISE', 1,
1565 'ADDL. EXCISE', 1,
1566 'OTHER EXCISE', 1,
1567 'TDS', 2,
1568 lv_excise_cess_code,3,
1569 lv_cvd_cess_code,3,
1570 lv_sh_excise_cess_code,4, --added by sacsethi for budget07 enhancement
1571 lv_sh_cvd_cess_code,4, --added by sacsethi for budget07 enhancement
1572 (SELECT jrttv.tax_type
1573 FROM jai_regime_tax_types_v jrttv
1574 WHERE jrttv.tax_type = upper(b.tax_type)
1575 AND jrttv.regime_code = jai_constants.vat_regime), 5,
1576 'VAT REVERSAL', 6, 0)) tax_type_val,
1577 b.mod_cr_Percentage ,
1578 b.vendor_id ,
1579 b.tax_type ,
1580 NVL( b.rounding_factor, 0 ) rnd ,
1581 b.adhoc_flag
1582 , b.inclusive_tax_flag --Add by Kevin for inclusive tax Dec 12, 2007
1583 FROM
1584 jai_cmn_document_taxes a ,
1585 jai_regime_tax_types_v aa , -- Add by Xiao for GST refer to bug#10043656.
1586 jai_cmn_taxes_all b ,
1587 jai_mtl_trxs c /* jai_mtl_trxs_temp is modified as jai_mtl_trxs by Vijay for ReArch. bug#2942973 */
1588 WHERE
1589 a.source_doc_line_id = p_line_id AND
1590 a.tax_id = b.tax_id AND
1591 aa.tax_type(+) = b.tax_type AND -- Add by Xiao for GST refer to bug#10043656.
1592 a.source_doc_type = 'INTERORG_XFER' AND
1593 a.source_doc_line_id = c.transaction_temp_id
1594 ORDER BY
1595 a.tax_line_no;
1596
1597 -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
1598 CURSOR ar_tax_cur(p_excise_cess_cnt IN NUMBER, p_sh_excise_cess_cnt IN NUMBER) IS /*Bug 5989740 bduvarag*/ ---- AR Side
1599 SELECT a.tax_id, a.tax_line_no lno,
1600 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,
1601 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,
1602 a.qty_rate,
1603 -- Vijay Shankar for Bug# 3781299
1604 decode(b.tax_type, lv_excise_cess_code, decode(p_excise_cess_cnt, 0, 0, a.tax_rate),
1605 lv_sh_excise_cess_code, decode(p_sh_excise_cess_cnt, 0, 0, a.tax_rate),
1606 a.tax_rate) tax_rate, -- a.tax_rate
1607 decode(b.tax_type, lv_excise_cess_code, decode(p_excise_cess_cnt, 0, 0, a.tax_amount), /*Bug 5989740 bduvarag*/
1608 lv_sh_excise_cess_code, decode(p_sh_excise_cess_cnt, 0, 0, a.tax_amount), /*Bug 5989740 bduvarag*/
1609 a.tax_amount) tax_amount, -- a.tax_amount ,
1610 a.uom, b.end_date valid_date,
1611 DECODE(aa.regime_code, jai_constants.cgst_regime, 7 --Added by Jia for GST Bug#10043656 on 2010/09/10
1612 , jai_constants.sgst_regime, 7 --Added by Jia for GST Bug#10043656 on 2010/09/10
1613 , decode(upper(b.tax_type),'EXCISE', 1,
1614 'ADDL. EXCISE', 1,
1615 'OTHER EXCISE', 1,
1616 'TDS', 2,
1617 lv_excise_cess_code,3, -- bug#4111609 excise cess
1618 lv_cvd_cess_code,3, -- bug#4111609 cvd cess
1619 lv_sh_excise_cess_code,4, /*Bug 5989740 bduvarag*/
1620 lv_sh_cvd_cess_code,4,
1621 /* Added for bug#8887871 */
1622 (SELECT jrttv.tax_type
1623 FROM jai_regime_tax_types_v jrttv
1624 WHERE jrttv.tax_type = upper(b.tax_type)
1625 AND jrttv.regime_code = jai_constants.vat_regime), 5,
1626 'VAT REVERSAL', 6, 0)) tax_type_val,
1627 b.mod_cr_Percentage, b.vendor_id, b.tax_type, NVL( b.rounding_factor, 0 ) rnd, b.adhoc_flag /* Added for bug 5091874*/
1628 , b.inclusive_tax_flag --Add by Kevin Cheng for inclusive tax Dec 12, 2007
1629 FROM JAI_AR_TRX_TAX_LINES a, JAI_CMN_TAXES_ALL b,
1630 jai_regime_tax_types_v aa -- Add by Xiao for GST refer to bug#10043656.
1631 WHERE a.link_to_cust_trx_line_id = p_line_id
1632 AND a.tax_id = b.tax_id
1633 AND aa.tax_type(+) = b.tax_type -- Add by Xiao for GST refer to bug#10043656.
1634 ORDER BY a.tax_line_no;
1635
1636 CURSOR uom_class_cur( v_unit_code IN VARCHAR2, p_tax_line_uom_code IN VARCHAR2) IS
1637 SELECT A.uom_class
1638 FROM mtl_units_of_measure A, mtl_units_of_measure B
1639 WHERE A.uom_code = v_unit_code
1640 AND B.uom_code = p_tax_line_uom_code
1641 AND A.uom_class = B.uom_class;
1642
1643 CURSOR Fetch_Dtls_Cur IS -- OE Side
1644 SELECT
1645 decode(nvl(quantity,0),0,1,quantity) ,
1646 selling_Price ,
1647 unit_code ,
1648 inventory_item_id
1649 , nvl(line_amount, 0) line_amount --Add by Kevin Cheng for inclusive tax Dec 12, 2007
1650 , nvl(assessable_value,0)*nvl(quantity,0) assessable_value --Add by Kevin Cheng for inclusive tax Dec 12, 2007
1651 , nvl(vat_assessable_value,0) vat_assessable_vale --Add by Kevin Cheng for inclusive tax Dec 12, 2007
1652 , nvl(vat_reversal_price, 0) vat_reversal_price --added for bug#8887871
1653 , nvl(gst_assessable_value,0) gst_assessable_value --Add by Xiao for GST refer to bug#10043656.
1654 FROM
1655 JAI_OM_OE_SO_LINES
1656 WHERE line_id = p_line_id;
1657
1658 CURSOR Fetch_Dtls1_Cur IS -- AR Side
1659 SELECT Quantity, Unit_Selling_Price, Unit_Code, Inventory_Item_Id
1660 , nvl(line_amount, 0) line_amount --Add by Kevin Cheng for inclusive tax Dec 12, 2007
1661 , nvl(assessable_value,0)*nvl(quantity,0) assessable_value --Add by Kevin Cheng for inclusive tax Dec 12, 2007
1662 , nvl(vat_assessable_value,0) vat_assessable_vale --Add by Kevin Cheng for inclusive tax Dec 12, 2007
1663 , nvl(vat_reversal_price, 0) vat_reversal_price --added for bug#8887871
1664 , nvl(gst_assessable_value,0) gst_assessable_value --Add by Xiao for GST refer to bug#10043656.
1665 FROM JAI_AR_TRX_LINES
1666 WHERE Customer_Trx_Line_Id = p_line_id;
1667
1668 CURSOR Fetch_Dtls_xfer_Cur IS -- Inter Org XFER Side 6030615
1669 /* SELECT transaction_Quantity quantity, Selling_Price unit_Selling_price , uom_code Unit_Code, Inventory_Item_Id */
1670 SELECT decode(quantity,0,1,quantity), selling_price unit_Selling_price , transaction_uom Unit_Code, Inventory_Item_Id, vat_assessable_value
1671 , nvl(selling_price,0)*nvl(quantity,0) line_amount --Add by Kevin Cheng for inclusive tax Dec 12, 2007
1672 , nvl(assessable_value,0) assessable_value --Add by Kevin Cheng for inclusive tax Dec 12, 2007
1673 , nvl(gst_assessable_value,0) gst_assessable_value --Add by Xiao for GST refer to bug#10043656.
1674 FROM jai_mtl_trxs /* jai_mtl_trxs_temp is modified as jai_mtl_trxs by Vijay for ReArch. bug#2942973 */
1675 WHERE transaction_temp_id = p_line_id;
1676
1677 CURSOR Fetch_Exempt_Cur( AddressId IN NUMBER ) IS -- OE Side
1678 SELECT NVL( Exempt, 'N' )
1679 FROM JAI_CMN_CUS_ADDRESSES
1680 WHERE Customer_Id = p_customer_id
1681 AND Address_Id = AddressId;
1682
1683 CURSOR Fetch_OE_Address_Cur IS
1684 SELECT cust_acct_site_id address_id
1685 from hz_cust_site_uses_all where site_use_id=
1686 (select SHIP_TO_LOCATION_ID
1687 FROM wsh_delivery_details
1688 WHERE delivery_detail_id = p_ref_line_id);
1689
1690 CURSOR Fetch_AR_Address_Cur IS
1691 SELECT cust_acct_site_id address_id
1692 FROM hz_cust_site_uses_all where site_use_id in
1693 (select ship_to_site_use_id from RA_Customer_Trx_All
1694 WHERE Customer_trx_Id in (select customer_trx_id from
1695 ra_customer_trx_lines_all where customer_trx_line_id = p_ref_line_id));
1696
1697
1698 CURSOR Chk_Rcd_Cur IS
1699 SELECT NVL( COUNT( * ), 0 )
1700 FROM JAI_CMN_MATCH_TAXES
1701 WHERE Ref_Line_Id = p_ref_line_id
1702 AND Subinventory = p_subinventory
1703 AND Receipt_Id = p_receipt_id
1704 AND Order_Invoice = 'O';
1705
1706 CURSOR Chk_Rcd_AR_Cur IS
1707 SELECT NVL( COUNT( * ), 0 )
1708 FROM JAI_CMN_MATCH_TAXES
1709 WHERE Ref_Line_Id = p_ref_line_id
1710 AND Receipt_Id = p_receipt_id
1711 AND Order_Invoice = 'I';
1712
1713 CURSOR Fetch_Totals_Cur( line_no IN NUMBER ) IS
1714 SELECT SUM( NVL( Tax_Amount, 0 ) )
1715 FROM JAI_CMN_MATCH_TAXES
1716 WHERE Subinventory = p_subinventory
1717 AND Ref_Line_Id = p_ref_line_id
1718 AND Tax_Line_No = line_no;
1719
1720 CURSOR Fetch_Totals_AR_Cur( line_no IN NUMBER ) IS
1721 SELECT SUM( NVL( Tax_Amount, 0 ) )
1722 FROM JAI_CMN_MATCH_TAXES
1723 WHERE Ref_Line_Id = p_ref_line_id
1724 AND Tax_Line_No = line_no;
1725
1726 CURSOR Fetch_Total_AR_Cur( line_no IN NUMBER ) IS
1727 SELECT SUM( NVL( Tax_Amount, 0 ) ) tax_amount, SUM( NVL( Base_Tax_Amount, 0 ) ) base_tax_amount,
1728 SUM( NVL( Func_Tax_Amount, 0 ) ) func_tax_amount
1729 FROM JAI_CMN_MATCH_TAXES
1730 WHERE Ref_Line_Id = p_ref_line_id
1731 AND Tax_Line_No = line_no
1732 AND Receipt_ID IS NOT NULL;
1733
1734 CURSOR Excise_Duty_Rate_Cur IS
1735 SELECT --rate_per_unit/*bduvarag for the bug#6038720*/
1736 /* Added the following and commented the above for bug#6022072 */
1737 --added nvl(additional_cvd,0) for bug#9288359
1738 (nvl(duty_amount,0) + nvl(additional_cvd,0)) / decode(DECODE( TRANSACTION_TYPE,
1739 'MCR', oth_receipt_quantity,quantity_received)
1740 ,0,1,DECODE( TRANSACTION_TYPE, 'MCR', oth_receipt_quantity,quantity_received)),
1741 -- added the decode transaction_type for bug8366387
1742 -- ssumaith - removed the + additional_cvd in the above line
1743 receipt_num ,
1744 decode(DECODE( TRANSACTION_TYPE, 'MCR', oth_receipt_quantity,quantity_received) ,0,1,DECODE( TRANSACTION_TYPE, 'MCR', oth_receipt_quantity,quantity_received)) quantity_received -- added the decode transaction_type for bug8366387 ,
1745 --other_tax_credit / decode(quantity_received ,0,1,quantity_received) -- bug#4111609
1746 FROM JAI_CMN_RG_23D_TRXS
1747 WHERE register_id = p_receipt_id;
1748
1749 CURSOR cur_cess_Duty_Rate_Cur IS/*Bug 5989740 bduvarag*/
1750 SELECT tax_type , credit
1751 FROM JAI_CMN_RG_OTHERS
1752 WHERE source_register_id = p_receipt_id and
1753 source_type =3; --in (3,4) ; commented 4 for bug#9406919
1754
1755 ln_quantity_received number ;
1756 ln_cess_duty_amount NUMBER ;/*bduvarag for the bug#6038720*/
1757 ln_sh_cess_duty_amount NUMBER ;/*bduvarag for the bug#6038720*/
1758
1759 -- Start, Vijay Shankar for Bug# 3781299
1760 -- Function added to enhance the product to take care of BUDGET2004 Changes of Match Receipts
1761 FUNCTION excise_cess_check(p_transaction_id IN NUMBER , p_Cess_type_code IN VARCHAR2)/*Bug 5989740 bduvarag*/
1762 RETURN NUMBER IS
1763
1764 CURSOR c_get_order_line_id(cp_delivery_detail_id IN NUMBER) IS
1765 SELECT source_line_id
1766 FROM wsh_delivery_details
1767 WHERE delivery_detail_id = cp_delivery_detail_id;
1768
1769 CURSOR c_oe_excise_cess_cnt(cp_order_line_id IN NUMBER) IS
1770 SELECT count(1)
1771 FROM JAI_OM_OE_SO_TAXES a, JAI_CMN_TAXES_ALL b
1772 WHERE a.line_id = cp_order_line_id
1773 AND a.tax_id = b.tax_id
1774 AND b.tax_type = p_Cess_type_code;/*Bug 5989740 bduvarag*/
1775
1776 CURSOR c_ar_excise_cess_cnt(cp_customer_trx_line_id IN NUMBER) IS
1777 SELECT count(1)
1778 FROM JAI_AR_TRX_TAX_LINES a, JAI_CMN_TAXES_ALL b
1779 WHERE a.link_to_cust_trx_line_id = cp_customer_trx_line_id
1780 AND a.tax_id = b.tax_id
1781 AND b.tax_type = p_Cess_type_code ;/*Bug 5989740 bduvarag*/
1782
1783 CURSOR c_get_shp_line_id(cp_transaction_id IN NUMBER) IS
1784 SELECT shipment_line_id
1785 FROM rcv_transactions
1786 WHERE transaction_id = cp_transaction_id;
1787
1788
1789 CURSOR c_ed_cess_tax_check(cp_shipment_line_id IN NUMBER) IS
1790 SELECT 1
1791 FROM JAI_CMN_RG_23D_TRXS
1792 WHERE register_id = p_transaction_id
1793 AND nvl(other_tax_credit,0) <> 0;
1794
1795 ln_order_line_id NUMBER(15);
1796 lv_shipment_line_id NUMBER(15);
1797 ln_trxn_check NUMBER(5);
1798 ln_check NUMBER(5);
1799 BEGIN
1800
1801
1802
1803 IF p_order_invoice = 'O' THEN
1804 OPEN c_get_order_line_id(p_ref_line_id);
1805 FETCH c_get_order_line_id into ln_order_line_id;
1806 CLOSE c_get_order_line_id;
1807
1808 OPEN c_oe_excise_cess_cnt(ln_order_line_id);
1809 FETCH c_oe_excise_cess_cnt into ln_trxn_check;
1810 CLOSE c_oe_excise_cess_cnt;
1811
1812 ELSIF p_order_invoice = 'I' THEN
1813 OPEN c_ar_excise_cess_cnt(p_ref_line_id);
1814 FETCH c_ar_excise_cess_cnt into ln_trxn_check;
1815 CLOSE c_ar_excise_cess_cnt;
1816
1817 ELSE
1818 NULL;
1819 END IF;
1820
1821 IF ln_trxn_check > 0 AND p_transaction_id IS NOT NULL THEN
1822
1823 OPEN c_get_shp_line_id(p_transaction_id);
1824 FETCH c_get_shp_line_id into lv_shipment_line_id;
1825 CLOSE c_get_shp_line_id;
1826
1827 OPEN c_ed_cess_tax_check(lv_shipment_line_id);
1828 FETCH c_ed_cess_tax_check into ln_check;
1829 CLOSE c_ed_cess_tax_check;
1830
1831 ELSE
1832 ln_check := -1;
1833 END IF;
1834
1835 If ln_check > 0 THEN
1836 ln_check := 1;
1837 END IF;
1838
1839 RETURN ln_check;
1840 END excise_cess_check;
1841 -- End, Vijay Shankar for Bug# 3781299
1842
1843 BEGIN
1844
1845 /*------------------------------------------------------------------------------------------
1846 FILENAME: JA_IN_RG23_D_CAL_TAX_P.sql
1847
1848 CHANGE HISTORY:
1849 S.No Date Author and Details
1850
1851 1. 10/10/2001 A.Vijay Kumar, Subbu
1852 Cursor written to pick taxes from Receipts for Tar# 9445972.700
1853
1854 2. 12/11/2003 SSUMAITH - Bug # 3179379
1855 In the case of a manual ar invoice which is matched against an iso receipt or
1856 manual rg23d receipt, then excise taxes were not getting populated into the
1857 ar invoice from the rg23 receipt.
1858 The reason for this behaviour is that , ther ar_tax_cur was written to work based
1859 on JAI_PO_TAXES and jai_rcv_tax_pkg.default_taxes_onto_line. The problem with cursor
1860 is that , manual rg23d receipts and receipts created by iso transactions are not
1861 picked by this cursor.
1862
1863 Hence, the cursor which was used earlier has been retained , which picks the taxes
1864 from JAI_AR_TRX_TAX_LINES table and then calculate the excise tax bases on
1865 the excise duty per unit and the same is updated back to the
1866 localization AR table.
1867
1868 3. 05/05/2004 Aiyer - Bug # 3611625, FileVersion 619.1
1869 Issue:-
1870 For a trading organization when a delivery is split into two deliveries,
1871 such that the line contains adhoc type of non excise taxes then it is observed that
1872 the tax amounts do not get apportioned based on the quantity applied and the orginal line
1873 quantity.
1874
1875 Reason:-
1876 This was happening because prior to this fix the tax amounts in case of Adhoc non excise
1877 type of tax used to be defaulted from JAI_OM_OE_SO_TAXES .
1878 Now when a delivery detail is split after pick release, then the delivery detail get splits
1879 but not the line.
1880 Hence the tax amount's, which were picked up from the JAI_OM_OE_SO_TAXES did not get apportioned
1881 properly.
1882
1883 Fix:-
1884 In case of Adhoc non excise type of taxes, the tax amounts get apportioned based on the p_line_quantity
1885 and v_original_quantity.
1886
1887 Dependency Due To This Bug:-
1888 None
1889
1890 4 21/07/2004 Vijay Shankar for Budget2004 Bug# 3781299, Version: 115.1
1891 Education Cess has been introduced in Budget 2004, which will be calculated on all excise taxes.
1892 Code is modified to make Education Cess Tax_rate as 0 in MAIN Cursor itself if
1893 JAI_CMN_TAXES_ALL.stform_type='EXCISE - CESS' and 'EXCISE-CESS'/'CVD-CESS' exist in Receipt
1894 Separate function is written to check(1) whether Cess tax exist in sales order/AR Manual Invoice line,
1895 if exists then check(2) for ExciseCess tax is receipts, If exists then return 1 else 0. If any of the
1896 check (1),(2) fails then function returns -1.
1897 Function excise_cess_check is added with this code change. Also Cursors oe_tax_cur and ar_tax_cur are modified
1898 NO DEPENDANCY
1899
1900 5 17/09/2004 Bug#3896539, Version: 115.2
1901 Modified the values assigned to Variables lv_excise_cess_code and lv_cvd_cess_code, so that they dont contain
1902 empty spaces
1903
1904 6 10/01/2005 rallamse bug#4111609, Version 115.3
1905
1906 When Match receipts happens, in addition to excise , education cess also needs to be matched to the sales order / invoice
1907 This has been done by makign code changes at various places in the procedure.
1908
1909 This fix does not introduce dependency on this object , but this patch cannot be sent alone to the CT
1910 because it relies on the alter done and the new tables created as part of the education cess enhancement
1911 bug# 4146708 creates the objects
1912
1913 7 06/08/2005 Aiyer bug#4539813, Version 120.3 (Forward porting fix done in the bug 4284335)
1914 Issue:-
1915 Excise Education cess taxes are not being recalculated correctly in some cases.
1916
1917 Reason:-
1918 This is a enhancement to the existing education cess code
1919 Here if the other_tax_credit in jai_cmn_rg_23d_trxs is <> 0 then the tax_rate and precedences can be assigned as null
1920 and the tax rate need not be considered.
1921 However if the other_tax_credit in jai_cmn_rg_23d_trxs is = 0 then the tax rate needs to be considered for recalculation.
1922
1923 Fix:-
1924 Modified the code to set the tax rate and precedences according to the other_tax_rate column in jai_cmn_rg_23d_trxs based on a IF condition.
1925
1926 Dependency Introduced due to this bug:-
1927 None
1928 8 08/05/2007 Made changes for InterOrg bug 6030615
1929
1930 9 09/10/2007 CSahoo for bug#6487182, File Version 120.12
1931 R12RUP04-ST1: MATCHING RESULTS IN INCORRECT NEGATIVE TAX FOR ZERO RATE TAX CODE
1932 Modified the IF condition to "tax_rate_tab( i ) NOT IN (0,-1)"
1933
1934 10 12/19/2007 Kevin Cheng Update the logic for inclusive tax calculation
1935
1936 11 18-feb-2008 ssumaith - bug#6817615
1937 duty amount field in the jai_cmn_rg23d_trxs table qas already
1938 including additional_cvd. Adding additional_cvd again is causing the problem.
1939
1940 12 03/26/2008 Kevin Cheng bug#6881225
1941 Initialize pl/sql table Tax_Amt_Tab() to 0 in the begining of initialization loop,
1942 in case there is no value for this table. Otherwise, a "no data found" exception will
1943 be thrown out in later calculation loops.
1944
1945 13 03/26/2008 Kevin Cheng bug#6915049
1946 Add statement ln_tax_amt_nr:=lt_tax_amt_non_rate_tab(i) in the ELSE branch for
1947 adhoc and UOM related taxes, so lt_tax_amt_non_rate_tab(i) won't lose its value
1948 in later assignment statement lt_tax_amt_non_rate_tab(i):=ln_tax_amt_nr.
1949
1950 14 04/04/2008 Kevin Cheng bug#6936009
1951 Remove round function for those temporary variables, so the calculation result will
1952 be more pricise, solving the decimal fraction issue.
1953 11 25-feb-2010 vkaranam for bug#9406919
1954 Issue:
1955 Cess/SHE Cess amount is updated wrongly in the ship confirm localized
1956 for trading organization.
1957 Fix:
1958 source_type condition in cur_cess_Duty_Rate_Cur has been changed from
1959 source_type in (3,4) to source_type =3;
1960
1961 12 11-jun-2010 vkaranam for bug#9794835
1962 Issue:
1963 TAX PRECEDENCE IGNORING BASE AMOUNT FOR TRADING SHIPMENT
1964 Analysis:
1965 issue is with the base tax amount calculated incorrectly.
1966 fix:
1967 addded the logic to calculate the base tax amount correctly.
1968 13 24-JUN-2010 vkaranam for bug#9839132
1969 FOREIGN CURRENCY TAXES ARE NOT DISPLAYED CORRECTLY IN SHIP CONFIRM LOCALIZED FORm
1970 Issue:
1971 Issue is happening for the trading organization.
1972 When the sales order is created in Foreign currency and matched against the receipt with INR taxes,
1973 After matching the receipt and delivered ,
1974 Ship confirm localized window shows the excise type of taxes in functional currency i.e INR
1975
1976 Fix:
1977 ---
1978 1.When the order is matched against the receipt,the taxes that flows from
1979 receipt to the sHip confirm localized window should be shown in the document currency
1980 2.Whenever the Order is delivered,
1981 Taxes should be recalculated taking the exchange rate as on Shipment date.
1982 Excise taxes ,functional_tax_amount will be fixed INR from the receipt,
1983 Tax_amount column should change based on the conversion rate.
1984 whereas for other type of taxes func_tax_amount will be recalulated based on the conversion rate.
1985
1986 Changes are done in this file for point #1.
1987 Excise type of taxes should be converted into Document currency when the receipt is matched against the order in trading organization.
1988
1989 Changes are done for P_ORDER_INVOICE='O'.
1990 14 22-nov-2010 vkaranam for bug#10283622
1991 Issue:
1992 For trading organization ,taxes are getting calculated incorrectly when the order is matched against multiple receipts.
1993
1994 issue is that the line amount is used for tax calculation without
1995 proportionating with the qty.
1996
1997 Fix:
1998 Proportioned the line amount with the qty being mathced.
1999
2000 15. 29_mar-2013 mmurtuza for bug 16552731
2001 issue: TRD ORG :ASSESSABLE PRICE FOR VAT TAX NOT GETTING CONSIDERED AT SHIPMENT LEVEL
2002 Fix: For VAT, the tax type tab value compared was 4. It should be 5
2003
2004 --------------------------------------------------------------------------------------------*/
2005
2006 lv_excise_cess_code := 'EXCISE_EDUCATION_CESS'; --Ramananda for File.Sql.35
2007 lv_cvd_cess_code := 'CVD_EDUCATION_CESS'; --Ramananda for File.Sql.35
2008 v_exempt_flag := jai_constants.no; --Ramananda for File.Sql.35
2009
2010 IF p_ref_line_id IS NULL THEN
2011 RAISE_APPLICATION_ERROR( -20120, 'Ref Line Id cannot be NULL' );
2012 END IF;
2013 /*Bug 5989740 bduvarag*/
2014 ln_quantity_received :=0 ;
2015 OPEN Excise_Duty_Rate_Cur;
2016 FETCH Excise_Duty_Rate_Cur into v_excise_duty_rate, ln_transaction_id ,ln_quantity_received ;/*Bug 5989740 bduvarag*/
2017 CLOSE Excise_Duty_Rate_Cur;
2018 /*Bug 5989740 bduvarag*/
2019
2020 for rec in cur_cess_Duty_Rate_Cur
2021 loop /*bduvarag for the bug#6038720 start*/
2022
2023 if rec.tax_type in( jai_constants.tax_type_exc_edu_cess,
2024 jai_constants.tax_type_cvd_edu_cess)
2025 then
2026 ln_cess_duty_amount := NVL(ln_cess_duty_amount,0) + rec.credit ;
2027 elsif rec.tax_type in( jai_constants.tax_type_sh_exc_edu_cess,
2028 jai_constants.tax_type_sh_cvd_edu_cess)
2029 then
2030 ln_sh_cess_duty_amount := NVL(ln_sh_cess_duty_amount,0) + rec.credit ;
2031
2032 /*bduvarag for the bug#6038720 end*/
2033 end if ;
2034 end loop;
2035
2036 ln_cess_duty_rate := ln_cess_duty_amount/ln_quantity_received; --Added for bug#6038720, bduvarag
2037 ln_sh_cess_duty_rate := ln_sh_cess_duty_amount/ln_quantity_received;--Added for bug#6038720, bduvarag
2038
2039
2040 ln_cess_duty_rate := nvl(ln_cess_duty_rate ,0) * nvl(p_line_quantity,0); /* bug#4111609 */
2041 ln_sh_cess_duty_rate := nvl(ln_sh_cess_duty_rate ,0) * nvl(p_line_quantity,0);/*Bug 5989740 bduvarag*/
2042
2043
2044 -- Call to the internal function added to code for BUDGET2004 Changes realted to Match Receipts of Trading Orgn
2045 ln_cess_check := excise_cess_check(p_receipt_id,lv_excise_cess_code);/*Bug 5989740 bduvarag*/
2046
2047 IF p_order_invoice = 'O' THEN
2048
2049 OPEN Chk_Rcd_Cur;
2050 FETCH Chk_Rcd_Cur INTO v_count;
2051 CLOSE Chk_Rcd_Cur;
2052
2053 OPEN Fetch_Dtls_Cur;
2054 FETCH Fetch_Dtls_Cur INTO v_original_quantity, v_selling_price, v_unit_code, v_inventory_item_id
2055 --Add by Kevin Cheng for inclusive tax Dec 19, 2007
2056 ---------------------------------------------------
2057 , ln_line_amount
2058 , ln_assessable_value_tmp_tot
2059 , ln_vat_assessable_value_tot
2060 , ln_vat_reversal_value_tot
2061 , ln_gst_assessable_value_tot --Add by Xiao for GST, refer to bug#10043656
2062 ---------------------------------------------------
2063 ;
2064 CLOSE Fetch_Dtls_Cur;
2065
2066 ln_vat_assessable_value_tot :=ln_vat_assessable_value_tot*nvl(p_line_quantity,0)/nvl(v_original_quantity,1); --bug#12594676
2067 v_excise_duty_rate := nvl(v_excise_duty_rate,0) * nvl(p_line_quantity,0);
2068
2069
2070 OPEN Fetch_OE_Address_Cur;
2071 FETCH Fetch_OE_Address_Cur INTO v_address_id;
2072 CLOSE Fetch_OE_Address_Cur;
2073
2074 OPEN Fetch_Exempt_Cur( v_address_id );
2075 FETCH Fetch_Exempt_Cur INTO v_exempt_flag;
2076 CLOSE Fetch_Exempt_Cur;
2077
2078 FOR rec in oe_tax_cur(ln_cess_check, ln_sh_cess_check) LOOP/*Bug 5989740 bduvarag*/
2079 IF v_count = 0 THEN
2080 INSERT INTO JAI_CMN_MATCH_TAXES(MATCH_TAX_ID, REF_LINE_ID,
2081 SUBINVENTORY,
2082 TAX_LINE_NO,
2083 PRECEDENCE_1,
2084 PRECEDENCE_2,
2085 PRECEDENCE_3,
2086 PRECEDENCE_4,
2087 PRECEDENCE_5,
2088 PRECEDENCE_6, -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
2089 PRECEDENCE_7,
2090 PRECEDENCE_8,
2091 PRECEDENCE_9,
2092 PRECEDENCE_10,
2093 TAX_ID,
2094 TAX_RATE,
2095 QTY_RATE,
2096 UOM,
2097 TAX_AMOUNT,
2098 BASE_TAX_AMOUNT,
2099 FUNC_TAX_AMOUNT,
2100 TOTAL_TAX_AMOUNT,
2101 CREATION_DATE,
2102 CREATED_BY,
2103 LAST_UPDATE_DATE,
2104 LAST_UPDATE_LOGIN,
2105 LAST_UPDATED_BY,
2106 RECEIPT_ID,
2107 ORDER_INVOICE )
2108 VALUES ( JAI_CMN_MATCH_TAXES_S.nextval, p_ref_line_id,
2109 p_subinventory,
2110 rec.lno,
2111 rec.p_1,
2112 rec.p_2,
2113 rec.p_3,
2114 rec.p_4,
2115 rec.p_5,
2116 rec.p_6,
2117 rec.p_7,
2118 rec.p_8,
2119 rec.p_9,
2120 rec.p_10,
2121 rec.tax_id,
2122 rec.tax_rate,
2123 rec.qty_rate,
2124 rec.uom,
2125 0,
2126 0,
2127 0,
2128 0,
2129 SYSDATE,
2130 UID,
2131 SYSDATE,
2132 UID,
2133 UID,
2134 p_receipt_id,
2135 p_order_invoice );
2136 END IF;
2137 --added if condition for bug#16082873
2138 if rec.tax_rate is null
2139 then
2140 tax_rate_tab(rec.lno) :=-9999;
2141 else
2142 Tax_Rate_Tab(rec.lno) := nvl(rec.Tax_Rate,0);
2143 end if;
2144 Adhoc_Flag_Tab(rec.lno) := nvl(rec.adhoc_flag,'N'); /* Added for bug 5091874 */
2145 Tax_Amt_Tab(rec.lno) := 0; --Add by Kevin Cheng for bug#6881225 Mar 26, 2008
2146 IF ( excise_flag_set = FALSE AND rec.tax_type_val = 1 ) OR ( rec.tax_type_val <> 1 ) THEN
2147
2148 P1(rec.lno) := nvl(rec.p_1,-1);
2149 P2(rec.lno) := nvl(rec.p_2,-1);
2150 P3(rec.lno) := nvl(rec.p_3,-1);
2151 P4(rec.lno) := nvl(rec.p_4,-1);
2152 P5(rec.lno) := nvl(rec.p_5,-1);
2153
2154 -- Date 01-NOV-2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
2155 -- START BUG 5228046
2156
2157 P6(rec.lno) := nvl(rec.p_6,-1);
2158 P7(rec.lno) := nvl(rec.p_7,-1);
2159 P8(rec.lno) := nvl(rec.p_8,-1);
2160 P9(rec.lno) := nvl(rec.p_9,-1);
2161 P10(rec.lno) := nvl(rec.p_10,-1);
2162
2163 -- END BUG 5228046
2164
2165
2166 IF rec.tax_type_val = 1 THEN
2167 tax_rate_tab(rec.lno) := -1;
2168
2169 P1(rec.lno) := -1;
2170 P2(rec.lno) := -1;
2171 P3(rec.lno) := -1;
2172 P4(rec.lno) := -1;
2173 P5(rec.lno) := -1;
2174
2175 -- Date 01-NOV-2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
2176 -- START BUG 5228046
2177
2178 P6(rec.lno) := -1;
2179 P7(rec.lno) := -1;
2180 P8(rec.lno) := -1;
2181 P9(rec.lno) := -1;
2182 P10(rec.lno) := -1;
2183
2184 -- END BUG 5228046
2185 Tax_Amt_Tab(rec.lno) := v_excise_duty_rate*(1/p_curr_conv_factor);--9839132
2186 tax_target_tab(rec.lno) := v_excise_duty_rate*(1/p_curr_conv_factor);--9839132
2187 /* Bug#4111609 added code for cess */
2188 ELSIF excise_flag_set AND rec.tax_type_val = 3 then
2189 /*
2190 || Start of bug 4539813
2191 || Code modified by aiyer for the bug 4539813
2192 */
2193 IF nvl(ln_cess_duty_rate,0) <> 0 THEN
2194 /*
2195 ||other_tax_credit in rg23_d is not null
2196 */
2197 tax_rate_tab(rec.lno) := -1;
2198
2199 P1(rec.lno) := -1;
2200 P2(rec.lno) := -1;
2201 P3(rec.lno) := -1;
2202 P4(rec.lno) := -1;
2203 P5(rec.lno) := -1;
2204
2205 -- Date 01-NOV-2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
2206 -- START BUG 5228046
2207 P6(rec.lno) := -1;
2208 P7(rec.lno) := -1;
2209 P8(rec.lno) := -1;
2210 P9(rec.lno) := -1;
2211 P10(rec.lno) := -1;
2212 -- END BUG 5228046
2213
2214
2215 END IF;
2216 /*
2217 ||End of Bug 4539813
2218 */
2219 Tax_Amt_Tab(rec.lno) := ln_cess_duty_rate*(1/p_curr_conv_factor);--9839132;;
2220 tax_target_tab(rec.lno) := ln_cess_duty_rate*(1/p_curr_conv_factor);--9839132;;
2221 /* end bug#4111609 */ /*Bug 5989740 bduvarag start*/
2222 ELSIF excise_flag_set AND rec.tax_type_val = 4 then
2223 IF nvl(ln_sh_cess_duty_rate,0) <> 0 THEN
2224 tax_rate_tab(rec.lno) := -1;
2225 P1(rec.lno) := -1;
2226 P2(rec.lno) := -1;
2227 P3(rec.lno) := -1;
2228 P4(rec.lno) := -1;
2229 P5(rec.lno) := -1;
2230 P6(rec.lno) := -1;
2231 P7(rec.lno) := -1;
2232 P8(rec.lno) := -1;
2233 P9(rec.lno) := -1;
2234 P10(rec.lno) := -1;
2235
2236
2237 END IF;
2238
2239 Tax_Amt_Tab(rec.lno) := ln_sh_cess_duty_rate*(1/p_curr_conv_factor);--9839132;;
2240 tax_target_tab(rec.lno) := ln_sh_cess_duty_rate*(1/p_curr_conv_factor);--9839132;;
2241 /*Bug 5989740 bduvarag end*/
2242 END IF;
2243
2244 ELSIF excise_flag_set AND rec.tax_type_val = 1 THEN
2245 P1(rec.lno) := -1;
2246 P2(rec.lno) := -1;
2247 P3(rec.lno) := -1;
2248 P4(rec.lno) := -1;
2249 P5(rec.lno) := -1;
2250
2251 -- Date 01-NOV-2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
2252 -- START BUG 5228046
2253 P6(rec.lno) := -1;
2254 P7(rec.lno) := -1;
2255 P8(rec.lno) := -1;
2256 P9(rec.lno) := -1;
2257 P10(rec.lno) := -1;
2258
2259 -- END BUG 5228046
2260
2261 tax_rate_tab(rec.lno) := -1;
2262 Tax_Amt_Tab(rec.lno) := 0;
2263 tax_target_tab(rec.lno) := 0;
2264 END IF;
2265
2266 IF v_exempt_flag = 'Y' AND rec.tax_type_val = 1 THEN
2267 P1(rec.lno) := -1;
2268 P2(rec.lno) := -1;
2269 P3(rec.lno) := -1;
2270 P4(rec.lno) := -1;
2271 P5(rec.lno) := -1;
2272
2273 -- Date 01-NOV-2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
2274 -- START BUG 5228046
2275
2276 P6(rec.lno) := -1;
2277 P7(rec.lno) := -1;
2278 P8(rec.lno) := -1;
2279 P9(rec.lno) := -1;
2280 P10(rec.lno) := -1;
2281
2282 -- END BUG 5228046
2283
2284 tax_rate_tab(rec.lno) := -1;
2285 Tax_Amt_Tab(rec.lno) := 0;
2286 tax_target_tab(rec.lno) := 0;
2287
2288 END IF;
2289 Rounding_factor_tab(rec.lno) := rec.rnd;
2290 Tax_Type_Tab(rec.lno) := rec.tax_type_val;
2291
2292 --Add by Kevin Cheng for inclusive tax Dec 12, 2007
2293 ---------------------------------------------------
2294 lt_tax_amt_rate_tax_tab(rec.lno) :=0;
2295 lt_tax_amt_non_rate_tab(rec.lno) :=0; -- tax inclusive
2296 lt_base_tax_amt_tab(rec.lno) := 0;
2297 base_tax_amount_nr_tab(rec.lno):=0;--bug#9794835
2298 ---------------------------------------------------
2299
2300 --IF nvl(rec.tax_rate,0) = 0 AND nvl(rec.qty_rate,0) = 0 THEN
2301 /*commented and added the below if condition for bug#16082873*/
2302 If rec.tax_rate is null and nvl(rec.qty_rate,0)=0 then
2303 tax_rate_tab(rec.lno) := -1;
2304 -- Start of addition by Srihari and Gaurav on 11-JUL-2000
2305 IF rec.tax_type_val = 1 THEN
2306 /*Tax_Amt_Tab(rec.lno) := v_excise_duty_rate;*/--Comment out by Kevin Cheng for inclusive tax 2008/01/14
2307 lt_tax_amt_non_rate_tab(rec.lno) := v_excise_duty_rate*(1/p_curr_conv_factor);--9839132;;; --Add by Kevin Cheng for inclusive tax 2008/01/14
2308 tax_target_tab(rec.lno) := v_excise_duty_rate*(1/p_curr_conv_factor);--9839132;;;
2309 ELSIF rec.tax_type_val = 3 then
2310 /*Tax_Amt_Tab(rec.lno) := ln_cess_duty_rate;*/--Comment out by Kevin Cheng for inclusive tax 2008/01/14
2311 lt_tax_amt_non_rate_tab(rec.lno) := ln_cess_duty_rate*(1/p_curr_conv_factor);--9839132;;; --Add by Kevin Cheng for inclusive tax 2008/01/14
2312 tax_target_tab(rec.lno) := ln_cess_duty_rate*(1/p_curr_conv_factor);--9839132;;;
2313 /*Bug 5989740 bduvarag*/
2314 ELSIF rec.tax_type_val = 4 then
2315 /*Tax_Amt_Tab(rec.lno) := ln_sh_cess_duty_rate;*/--Comment out by Kevin Cheng for inclusive tax 2008/01/14
2316 lt_tax_amt_non_rate_tab(rec.lno) := ln_sh_cess_duty_rate*(1/p_curr_conv_factor);--9839132;;; --Add by Kevin Cheng for inclusive tax 2008/01/14
2317 tax_target_tab(rec.lno) := ln_sh_cess_duty_rate*(1/p_curr_conv_factor);--9839132;;;
2318
2319 ELSE
2320 /*tax_amt_tab(rec.lno) := (p_line_quantity / v_original_quantity) * rec.tax_amount;*/--Comment out by Kevin Cheng for inclusive tax 2008/01/14
2321 lt_tax_amt_non_rate_tab(rec.lno) := (p_line_quantity / v_original_quantity) * rec.tax_amount; --Add by Kevin Cheng for inclusive tax 2008/01/14
2322 tax_target_tab(rec.lno) := (p_line_quantity / v_original_quantity) * rec.tax_amount;
2323 -- End of bug 3611625
2324 END IF;
2325 lt_base_tax_amt_tab(rec.lno) := lt_tax_amt_non_rate_tab(rec.lno); --Add by Kevin Cheng for inclusive tax 2008/01/14
2326 ELSE
2327 IF rec.tax_type_val NOT IN (1,3,4) THEN /*Bug 5989740 bduvarag*/
2328 Tax_Amt_Tab(rec.lno) := 0;
2329 END IF;
2330 END IF;
2331
2332 --Add by Kevin Cheng for inclusive tax Dec 12, 2007
2333 ---------------------------------------------------
2334 lt_tax_rate_per_rupee(rec.lno) := NVL(rec.tax_rate,0)/100;
2335 ln_total_tax_per_rupee := 0;
2336 lt_inclu_tax_tab(rec.lno) := NVL(rec.inclusive_tax_flag,'N');
2337
2338 IF rec.tax_rate is null THEN
2339 lt_tax_rate_zero_tab(rec.lno) := 0;
2340 ELSIF rec.tax_rate = 0 THEN
2341 lt_tax_rate_zero_tab(rec.lno) := -9999;
2342 ELSE
2343 lt_tax_rate_zero_tab(rec.lno) := rec.tax_rate;
2344 END IF;
2345 -----------------------------------------------------
2346
2347 --Comment out by Kevin Cheng for inclusive tax Dec 12, 2007
2348 /*IF rec.Valid_Date is NULL Or rec.Valid_Date >= Sysdate THEN
2349 End_Date_Tab(rec.lno) := 1;
2350 ELSE
2351 End_Date_Tab(rec.lno) := 0;
2352 tax_amt_tab(row_count) := 0; --Add by Kevin Cheng for inclusive tax Dec 12, 2007
2353 END IF;*/
2354
2355 row_count := row_count + 1;
2356 --IF tax_rate_tab(rec.lno) = 0 THEN
2357 /*commented the above if and added the below if condition
2358 for bug#16082873 ,to consider the UOM based taxes*/
2359 if rec.tax_rate is null and nvl(rec.qty_rate,0)<>0 then
2360 FOR uom_cls IN uom_class_cur(v_unit_code, rec.uom) LOOP
2361 INV_CONVERT.inv_um_conversion(v_unit_code, rec.uom, v_inventory_item_id, v_conversion_rate);
2362 IF nvl(v_conversion_rate, 0) <= 0 THEN
2363 INV_CONVERT.inv_um_conversion(v_unit_code, rec.uom, 0, v_conversion_rate);
2364 IF nvl(v_conversion_rate, 0) <= 0 THEN
2365 v_conversion_rate := 0;
2366 END IF;
2367 END IF;
2368 IF ( excise_flag_set ) AND ( rec.tax_type_val = 1 ) THEN
2369 /*tax_amt_tab(rec.lno) := 0;*/--Comment out by Kevin Cheng for inclusive tax Dec 12, 2007
2370 lt_tax_amt_non_rate_tab(rec.lno) := 0; --Add by Kevin Cheng for inclusive tax Dec 12, 2007
2371 ELSIF rec.tax_type_val = 3 then
2372 /*Tax_Amt_Tab(rec.lno) := ln_cess_duty_rate;*/--Comment out by Kevin Cheng for inclusive tax Dec 12, 2007
2373 lt_tax_amt_non_rate_tab(rec.lno) := ln_cess_duty_rate*(1/p_curr_conv_factor);--9839132 --Add by Kevin Cheng for inclusive tax Dec 12, 2007
2374 tax_target_tab(rec.lno) := ln_cess_duty_rate*(1/p_curr_conv_factor);--9839132
2375 /*Bug 5989740 bduvarag*/
2376 ELSIF rec.tax_type_val = 4 then
2377 /*Tax_Amt_Tab(rec.lno) := ln_sh_cess_duty_rate;*/--Comment out by Kevin Cheng for inclusive tax Dec 12, 2007
2378 lt_tax_amt_non_rate_tab(rec.lno) := ln_sh_cess_duty_rate*(1/p_curr_conv_factor);--9839132 --Add by Kevin Cheng for inclusive tax Dec 12, 2007
2379 tax_target_tab(rec.lno) := ln_sh_cess_duty_rate*(1/p_curr_conv_factor);--9839132
2380
2381 ELSE
2382 --Comment out by Kevin Cheng for inclusive tax Dec 12, 2007
2383 /*tax_amt_tab(rec.lno) := ROUND( nvl(rec.qty_rate * v_conversion_rate, 0) * p_line_quantity, rounding_factor_tab(rec.lno) );*/
2384 --Add by Kevin Cheng for inclusive tax Dec 12, 2007
2385 lt_tax_amt_non_rate_tab(rec.lno) := /*ROUND( */nvl(rec.qty_rate * v_conversion_rate, 0) * p_line_quantity/*, rounding_factor_tab(rec.lno) )*/; --Modified by Kevin Cheng for bug#6936009 April 02, 2008
2386 END IF;
2387 IF v_exempt_flag = 'Y' AND rec.tax_type_val = 1 THEN
2388 /*tax_amt_tab( rec.lno ) := 0;*/ --Comment out by Kevin Cheng for inclusive tax Dec 12, 2007
2389 lt_tax_amt_non_rate_tab(rec.lno) := 0; --Add by Kevin Cheng for inclusive tax Dec 12, 2007
2390 END IF;
2391 lt_base_tax_amt_tab(rec.lno) := lt_tax_amt_non_rate_tab(rec.lno); --Add by Kevin Cheng for inclusive tax Dec 12, 2007
2392 tax_rate_tab( rec.lno ) := -1;
2393 tax_target_tab(rec.lno) := tax_amt_tab( rec.lno );
2394 END LOOP;
2395 END IF;
2396
2397 --Add by Kevin Cheng for inclusive tax Dec 12, 2007
2398 ---------------------------------------------------
2399 IF rec.Valid_Date is NULL Or rec.Valid_Date >= SYSDATE
2400 THEN
2401 End_Date_Tab(rec.lno) := 1;
2402 ELSE
2403 End_Date_Tab(rec.lno) := 0;
2404 tax_amt_tab(rec.lno) := 0;
2405 END IF;
2406 ---------------------------------------------------
2407
2408 IF rec.tax_type_val = 1 THEN
2409 excise_flag_set := TRUE;
2410 END IF;
2411 END LOOP;
2412 -----------------------------------------------------------------------------------------------------
2413 ELSIF p_order_invoice = 'I' THEN
2414 OPEN Chk_Rcd_AR_Cur;
2415 FETCH Chk_Rcd_AR_Cur INTO v_count;
2416 CLOSE Chk_Rcd_AR_Cur;
2417
2418 OPEN Fetch_Dtls1_Cur;
2419 FETCH Fetch_Dtls1_Cur INTO v_original_quantity, v_selling_price, v_unit_code, v_inventory_item_id
2420 , ln_line_amount, ln_assessable_value_tmp_tot, ln_vat_assessable_value_tot,ln_vat_reversal_value_tot --Add by Kevin Cheng for inclusive tax Dec 12, 2007
2421 , ln_gst_assessable_value_tot; --Add by Xiao for GST, refer to bug#10043656
2422 CLOSE Fetch_Dtls1_Cur;
2423 ln_vat_assessable_value_tot :=ln_vat_assessable_value_tot*nvl(p_line_quantity,0)/nvl(v_original_quantity,1); --bug#12594676
2424 v_excise_duty_rate := nvl(v_excise_duty_rate,0) * nvl(p_line_quantity,0);
2425
2426 OPEN Fetch_AR_Address_Cur;
2427 FETCH Fetch_AR_Address_Cur INTO v_address_id;
2428 CLOSE Fetch_AR_Address_Cur;
2429
2430 OPEN Fetch_Exempt_Cur( v_address_id );
2431 FETCH Fetch_Exempt_Cur INTO v_exempt_flag;
2432 CLOSE Fetch_Exempt_Cur;
2433
2434
2435
2436 FOR rec in ar_tax_cur(ln_cess_check, ln_sh_cess_check) LOOP/*Bug 5989740 bduvarag*/
2437
2438 IF v_count = 0 THEN
2439
2440 INSERT INTO JAI_CMN_MATCH_TAXES(MATCH_TAX_ID, REF_LINE_ID,
2441 SUBINVENTORY,
2442 TAX_LINE_NO,
2443 PRECEDENCE_1,
2444 PRECEDENCE_2,
2445 PRECEDENCE_3,
2446 PRECEDENCE_4,
2447 PRECEDENCE_5,
2448 PRECEDENCE_6, -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
2449 PRECEDENCE_7,
2450 PRECEDENCE_8,
2451 PRECEDENCE_9,
2452 PRECEDENCE_10,
2453 TAX_ID,
2454 TAX_RATE,
2455 QTY_RATE,
2456 UOM,
2457 TAX_AMOUNT,
2458 BASE_TAX_AMOUNT,
2459 FUNC_TAX_AMOUNT,
2460 TOTAL_TAX_AMOUNT,
2461 CREATION_DATE,
2462 CREATED_BY,
2463 LAST_UPDATE_DATE,
2464 LAST_UPDATE_LOGIN,
2465 LAST_UPDATED_BY,
2466 RECEIPT_ID,
2467 ORDER_INVOICE )
2468 VALUES ( JAI_CMN_MATCH_TAXES_S.nextval, p_ref_line_id,
2469 p_subinventory,
2470 rec.lno,
2471 rec.p_1,
2472 rec.p_2,
2473 rec.p_3,
2474 rec.p_4,
2475 rec.p_5,
2476 rec.p_6, -- Date 01/11/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
2477 rec.p_7,
2478 rec.p_8,
2479 rec.p_9,
2480 rec.p_10,
2481 rec.tax_id,
2482 rec.tax_rate,
2483 rec.qty_rate,
2484 rec.uom,
2485 0,
2486 0,
2487 0,
2488 0,
2489 SYSDATE,
2490 UID,
2491 SYSDATE,
2492 UID,
2493 UID,
2494 p_receipt_id,
2495 p_order_invoice );
2496
2497 END IF;
2498
2499 if rec.tax_rate is null
2500 then
2501 tax_rate_tab(rec.lno):=-9999;
2502 else
2503 Tax_Rate_Tab(rec.lno) := nvl(rec.Tax_Rate,0);
2504 end if; --added if clause for bug#16082873
2505 Adhoc_Flag_Tab(rec.lno) := nvl(rec.adhoc_flag,'N'); /* Added for bug 5091874 */
2506 Tax_Amt_Tab(rec.lno) := 0; --Add by Kevin Cheng for bug#6881225 Mar 28, 2008
2507 if excise_flag_set then
2508 v_e_s := 'Yes';
2509 else
2510 v_e_s := 'NO';
2511 end if;
2512
2513 IF ( excise_flag_set = FALSE AND rec.tax_type_val = 1 ) OR ( rec.tax_type_val <> 1 ) THEN
2514
2515 P1(rec.lno) := nvl(rec.p_1,-1);
2516 P2(rec.lno) := nvl(rec.p_2,-1);
2517 P3(rec.lno) := nvl(rec.p_3,-1);
2518 P4(rec.lno) := nvl(rec.p_4,-1);
2519 P5(rec.lno) := nvl(rec.p_5,-1);
2520
2521
2522 -- Date 01-NOV-2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
2523 -- START BUG 5228046
2524
2525 P6(rec.lno) := nvl(rec.p_6,-1);
2526 P7(rec.lno) := nvl(rec.p_7,-1);
2527 P8(rec.lno) := nvl(rec.p_8,-1);
2528 P9(rec.lno) := nvl(rec.p_9,-1);
2529 P10(rec.lno) := nvl(rec.p_10,-1);
2530 -- END BUG 5228046
2531
2532
2533 IF rec.tax_type_val = 1 THEN
2534
2535 tax_rate_tab(rec.lno) := -1;
2536 P1(rec.lno) := -1;
2537 P2(rec.lno) := -1;
2538 P3(rec.lno) := -1;
2539 P4(rec.lno) := -1;
2540 P5(rec.lno) := -1;
2541
2542 -- Date 01-NOV-2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
2543 -- START BUG 5228046
2544 P6(rec.lno) := -1;
2545 P7(rec.lno) := -1;
2546 P8(rec.lno) := -1;
2547 P9(rec.lno) := -1;
2548 P10(rec.lno) := -1;
2549 -- END BUG 5228046
2550
2551 Tax_Amt_Tab(rec.lno) := v_excise_duty_rate;
2552 tax_target_tab(rec.lno) := v_excise_duty_rate;
2553 /* Bug#4111609 added code for cess */
2554 ELSIF excise_flag_set AND rec.tax_type_val = 3 then
2555 /*
2556 || Start of bug 4539813
2557 || Code modified by aiyer for the bug 4539813
2558 */
2559 IF nvl(ln_cess_duty_rate,0) <> 0 THEN
2560 /*
2561 ||other_tax_credit in jai_cmn_rg_23d_trxs is not null
2562 */
2563 tax_rate_tab(rec.lno) := -1;
2564 P1(rec.lno) := -1;
2565 P2(rec.lno) := -1;
2566 P3(rec.lno) := -1;
2567 P4(rec.lno) := -1;
2568 P5(rec.lno) := -1;
2569
2570 -- Date 01-NOV-2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
2571 -- START BUG 5228046
2572 P6(rec.lno) := -1;
2573 P7(rec.lno) := -1;
2574 P8(rec.lno) := -1;
2575 P9(rec.lno) := -1;
2576 P10(rec.lno) := -1;
2577
2578 -- END BUG 5228046
2579
2580
2581 END IF;
2582 /*
2583 ||End of Bug 4539813
2584 */
2585 Tax_Amt_Tab(rec.lno) := ln_cess_duty_rate;
2586 tax_target_tab(rec.lno) := ln_cess_duty_rate;
2587 /*Bug 5989740 bduvarag start*/
2588 ELSIF excise_flag_set AND rec.tax_type_val = 4 then
2589 IF nvl(ln_sh_cess_duty_rate,0) <> 0 THEN
2590 /*
2591 ||other_tax_credit in ja_in_rg23_d is not null
2592 */
2593 tax_rate_tab(rec.lno) := -1;
2594 P1(rec.lno) := -1;
2595 P2(rec.lno) := -1;
2596 P3(rec.lno) := -1;
2597 P4(rec.lno) := -1;
2598 P5(rec.lno) := -1;
2599 P6(rec.lno) := -1;
2600 P7(rec.lno) := -1;
2601 P8(rec.lno) := -1;
2602 P9(rec.lno) := -1;
2603 P10(rec.lno) := -1;
2604 END IF;
2605 Tax_Amt_Tab(rec.lno) := ln_sh_cess_duty_rate;
2606 tax_target_tab(rec.lno) := ln_sh_cess_duty_rate;
2607 /*Bug 5989740 bduvarag end*/
2608 END IF;
2609 ELSIF excise_flag_set AND rec.tax_type_val = 1 THEN
2610
2611 P1(rec.lno) := -1;
2612 P2(rec.lno) := -1;
2613 P3(rec.lno) := -1;
2614 P4(rec.lno) := -1;
2615 P5(rec.lno) := -1;
2616
2617 -- Date 01-NOV-2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
2618 -- START BUG 5228046
2619
2620 P6(rec.lno) := -1;
2621 P7(rec.lno) := -1;
2622 P8(rec.lno) := -1;
2623 P9(rec.lno) := -1;
2624 P10(rec.lno) := -1;
2625 -- END BUG 5228046
2626
2627
2628 tax_rate_tab(rec.lno) := -1;
2629 Tax_Amt_Tab(rec.lno) := 0;
2630 tax_target_tab(rec.lno) := 0;
2631 END IF;
2632 /* end Bug#4111609 */
2633
2634 IF v_exempt_flag = 'Y' AND rec.tax_type_val = 1 THEN
2635 P1(rec.lno) := -1;
2636 P2(rec.lno) := -1;
2637 P3(rec.lno) := -1;
2638 P4(rec.lno) := -1;
2639 P5(rec.lno) := -1;
2640
2641
2642 -- Date 01-NOV-2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
2643 -- START BUG 5228046
2644 P6(rec.lno) := -1;
2645 P7(rec.lno) := -1;
2646 P8(rec.lno) := -1;
2647 P9(rec.lno) := -1;
2648 P10(rec.lno) := -1;
2649 -- END BUG 5228046
2650
2651
2652 tax_rate_tab(rec.lno) := -1;
2653 Tax_Amt_Tab(rec.lno) := 0;
2654 tax_target_tab(rec.lno) := 0;
2655 END IF;
2656 Rounding_factor_tab(rec.lno) := rec.rnd;
2657 Tax_Type_Tab(rec.lno) := rec.tax_type_val;
2658
2659 --Add by Kevin Cheng for inclusive tax Dec 12, 2007
2660 ---------------------------------------------------
2661 lt_tax_amt_rate_tax_tab(rec.lno) :=0;
2662 lt_tax_amt_non_rate_tab(rec.lno) :=0; -- tax inclusive
2663 lt_base_tax_amt_tab(rec.lno) := 0;
2664 ---------------------------------------------------
2665
2666 --added rec.tax_rate is null for bug#16082873
2667 IF rec.tax_rate is null AND nvl(rec.qty_rate,0) = 0 THEN
2668
2669 tax_rate_tab(rec.lno) := -1;
2670 -- Start of addition by Srihari and Gaurav on 11-JUL-2000
2671 IF rec.tax_type_val = 1 THEN
2672 /*Tax_Amt_Tab(rec.lno) := v_excise_duty_rate;*/--Comment out by Kevin Cheng for inclusive tax 2008/01/15
2673 lt_tax_amt_non_rate_tab(rec.lno) := v_excise_duty_rate;--Add by Kevin Cheng for inclusive tax 2008/01/15
2674 tax_target_tab(rec.lno) := v_excise_duty_rate;
2675 ELSIF rec.tax_type_val = 3 then
2676 /*Tax_Amt_Tab(rec.lno) := ln_cess_duty_rate;*/--Comment out by Kevin Cheng for inclusive tax 2008/01/15
2677 lt_tax_amt_non_rate_tab(rec.lno) := ln_cess_duty_rate;--Add by Kevin Cheng for inclusive tax 2008/01/15
2678 tax_target_tab(rec.lno) := ln_cess_duty_rate;
2679 /*Bug 5989740 bduvarag*/
2680 ELSIF rec.tax_type_val = 4 then
2681 /*Tax_Amt_Tab(rec.lno) := ln_sh_cess_duty_rate;*/--Comment out by Kevin Cheng for inclusive tax 2008/01/15
2682 lt_tax_amt_non_rate_tab(rec.lno) := ln_sh_cess_duty_rate;--Add by Kevin Cheng for inclusive tax 2008/01/15
2683 tax_target_tab(rec.lno) := ln_sh_cess_duty_rate;
2684
2685 ELSE
2686 /*tax_amt_tab(rec.lno) := (p_line_quantity / v_original_quantity) * rec.tax_amount;*/--Comment out by Kevin Cheng for inclusive tax 2008/01/15
2687 lt_tax_amt_non_rate_tab(rec.lno) := (p_line_quantity / v_original_quantity) * rec.tax_amount;--Add by Kevin Cheng for inclusive tax 2008/01/15
2688 tax_target_tab(rec.lno) := (p_line_quantity / v_original_quantity) * rec.tax_amount;
2689 -- End of bug 3611625
2690 END IF;
2691 lt_base_tax_amt_tab(rec.lno) := lt_tax_amt_non_rate_tab(rec.lno); --Add by Kevin Cheng for inclusive tax 2008/01/15
2692 ELSE
2693 IF rec.tax_type_val NOT IN (1,3,4) THEN/*Bug 5989740 bduvarag*/
2694 Tax_Amt_Tab(rec.lno) := 0;
2695 END IF;
2696 END IF;
2697
2698 --Add by Kevin Cheng for inclusive tax Dec 12, 2007
2699 ---------------------------------------------------
2700 lt_tax_rate_per_rupee(rec.lno) := NVL(rec.tax_rate,0)/100;
2701 ln_total_tax_per_rupee := 0;
2702 lt_inclu_tax_tab(rec.lno) := NVL(rec.inclusive_tax_flag,'N');
2703
2704 IF rec.tax_rate is null THEN
2705 lt_tax_rate_zero_tab(rec.lno) := 0;
2706 ELSIF rec.tax_rate = 0 THEN
2707 lt_tax_rate_zero_tab(rec.lno) := -9999;
2708 ELSE
2709 lt_tax_rate_zero_tab(rec.lno) := rec.tax_rate;
2710 END IF;
2711
2712 ---------------------------------------------------
2713 --Comment out by Kevin Cheng for inclusive tax Dec 12, 2007
2714 /*IF rec.Valid_Date is NULL Or rec.Valid_Date >= Sysdate THEN
2715 End_Date_Tab(rec.lno) := 1;
2716 ELSE
2717 End_Date_Tab(rec.lno) := 0;
2718 END IF;*/
2719
2720 row_count := row_count + 1;
2721
2722 --IF tax_rate_tab(rec.lno) = 0 THEN
2723 /*added if for bug#16082873*/
2724 if rec.tax_rate is null and nvl(rec.qty_rate,0)<>0 then
2725
2726
2727 FOR uom_cls IN uom_class_cur(v_unit_code, rec.uom) LOOP
2728 INV_CONVERT.inv_um_conversion(v_unit_code, rec.uom, v_inventory_item_id, v_conversion_rate);
2729 IF nvl(v_conversion_rate, 0) <= 0 THEN
2730 INV_CONVERT.inv_um_conversion(v_unit_code, rec.uom, 0, v_conversion_rate);
2731 IF nvl(v_conversion_rate, 0) <= 0 THEN
2732 v_conversion_rate := 0;
2733 END IF;
2734 END IF;
2735 IF ( excise_flag_set ) AND ( rec.tax_type_val = 1 ) THEN
2736 /*tax_amt_tab(rec.lno) := 0;*/ --Comment out by Kevin Cheng for inclusive tax Dec 12, 2007
2737 lt_tax_amt_non_rate_tab(rec.lno) := 0; --Add by Kevin Cheng for inclusive tax Dec 12, 2007
2738 ELSIF rec.tax_type_val = 3 then
2739 /*Tax_Amt_Tab(rec.lno) := ln_cess_duty_rate;*/ --Comment out by Kevin Cheng for inclusive tax Dec 12, 2007
2740 lt_tax_amt_non_rate_tab(rec.lno) := ln_cess_duty_rate; --Add by Kevin Cheng for inclusive tax Dec 12, 2007
2741 tax_target_tab(rec.lno) := ln_cess_duty_rate;
2742 /*Bug 5989740 bduvarag*/
2743 ELSIF rec.tax_type_val = 4 then
2744 /*Tax_Amt_Tab(rec.lno) := ln_sh_cess_duty_rate;*/ --Comment out by Kevin Cheng for inclusive tax Dec 12, 2007
2745 lt_tax_amt_non_rate_tab(rec.lno) := ln_sh_cess_duty_rate; --Add by Kevin Cheng for inclusive tax Dec 12, 2007
2746 tax_target_tab(rec.lno) := ln_sh_cess_duty_rate;
2747
2748 ELSE
2749 --Comment out by Kevin Cheng for inclusive tax Dec 12, 2007
2750 /*tax_amt_tab(rec.lno) := ROUND( nvl(rec.qty_rate * v_conversion_rate, 0) * p_line_quantity, rounding_factor_tab(rec.lno) );*/
2751 --Add by Kevin Cheng for inclusive tax Dec 12, 2007
2752 lt_tax_amt_non_rate_tab(rec.lno) := /*ROUND( */nvl(rec.qty_rate * v_conversion_rate, 0) * p_line_quantity/*, rounding_factor_tab(rec.lno) )*/; --Modified by Kevin Cheng for bug#6936009 April 02, 2008
2753 END IF;
2754 IF v_exempt_flag = 'Y' AND rec.tax_type_val = 1 THEN
2755 /*tax_amt_tab( rec.lno ) := 0;*/--Comment out by Kevin Cheng for inclusive tax Dec 12, 2007
2756 lt_tax_amt_non_rate_tab(rec.lno) := 0; --Add by Kevin Cheng for inclusive tax Dec 12, 2007
2757 END IF;
2758 lt_base_tax_amt_tab(rec.lno) := lt_tax_amt_non_rate_tab(rec.lno); --Add by Kevin Cheng for inclusive tax Dec 12, 2007
2759 tax_rate_tab( rec.lno ) := -1;
2760 tax_target_tab(rec.lno) := tax_amt_tab( rec.lno );
2761
2762 END LOOP;
2763 END IF;
2764
2765 --Add by Kevin Cheng for inclusive tax Dec 12, 2007
2766 ---------------------------------------------------
2767 IF rec.Valid_Date is NULL Or rec.Valid_Date >= Sysdate THEN
2768 End_Date_Tab(rec.lno) := 1;
2769 ELSE
2770 End_Date_Tab(rec.lno) := 0;
2771 tax_amt_tab(rec.lno) := 0;
2772 END IF;
2773 ---------------------------------------------------
2774
2775 IF rec.tax_type_val = 1 THEN
2776
2777 excise_flag_set := TRUE;
2778 END IF;
2779 END LOOP;
2780
2781 ELSIF p_order_invoice = 'X' THEN -- 'X' = Inter org XFER bug 6030615
2782
2783
2784 OPEN Fetch_Dtls_xfer_Cur;
2785 FETCH Fetch_Dtls_xfer_Cur INTO v_original_quantity, v_selling_price, v_unit_code, v_inventory_item_id , ln_vat_assessable_value
2786 , ln_line_amount, ln_assessable_value_tmp_tot --Add by Kevin Cheng for inclusive tax Dec 12, 2007
2787 , ln_gst_assessable_value --Add by Xiao for GST, refer to bug#10043656
2788 ;
2789 CLOSE Fetch_Dtls_xfer_Cur;
2790 ln_vat_assessable_value_tot := ln_vat_assessable_value; --Add by Kevin Cheng for inclusive tax Dec 12, 2007
2791 ln_vat_assessable_value := /*round(*/nvl(ln_vat_assessable_value,0) / v_original_quantity/*,2)*/ ; /* Added for bug#6374760 */--Modified by Kevin Cheng for bug#6936009 April 02, 2008
2792
2793 --Add by Xiao for GST, refer to bug#10043656, begin
2794 --------------------------------------------------------------------------
2795 ln_gst_assessable_value_tot := ln_gst_assessable_value;
2796 ln_gst_assessable_value := /*round(*/nvl(ln_gst_assessable_value,0) / v_original_quantity/*,2)*/ ;
2797
2798 --------------------------------------------------------------------------
2799 --Add by Xiao for GST, refer to bug#10043656, begin
2800 v_excise_duty_rate := nvl(v_excise_duty_rate,0) * nvl(p_line_quantity,0);
2801
2802
2803
2804 FOR rec in interorg_xfer_tax_cur LOOP
2805
2806
2807
2808 IF v_count = 0 THEN
2809
2810
2811
2812 INSERT INTO Jai_cmn_match_Taxes( match_tax_id, --6447097
2813 REF_LINE_ID,
2814 SUBINVENTORY,
2815 TAX_LINE_NO,
2816 PRECEDENCE_1,
2817 PRECEDENCE_2,
2818 PRECEDENCE_3,
2819 PRECEDENCE_4,
2820 PRECEDENCE_5,
2821 TAX_ID,
2822 TAX_RATE,
2823 QTY_RATE,
2824 UOM,
2825 TAX_AMOUNT,
2826 BASE_TAX_AMOUNT,
2827 FUNC_TAX_AMOUNT,
2828 TOTAL_TAX_AMOUNT,
2829 CREATION_DATE,
2830 CREATED_BY,
2831 LAST_UPDATE_DATE,
2832 LAST_UPDATE_LOGIN,
2833 LAST_UPDATED_BY,
2834 RECEIPT_ID,
2835 ORDER_INVOICE ,
2836 PRECEDENCE_6,
2837 PRECEDENCE_7,
2838 PRECEDENCE_8,
2839 PRECEDENCE_9,
2840 PRECEDENCE_10
2841 )
2842 VALUES ( jai_cmn_match_taxes_s.nextval,--6447097
2843 p_ref_line_id,
2844 p_subinventory,
2845 rec.lno,
2846 rec.p_1,
2847 rec.p_2,
2848 rec.p_3,
2849 rec.p_4,
2850 rec.p_5,
2851 rec.tax_id,
2852 rec.tax_rate,
2853 rec.qty_rate,
2854 rec.uom,
2855 0,
2856 0,
2857 0,
2858 0,
2859 SYSDATE,
2860 FND_GLOBAl.USER_ID,
2861 SYSDATE,
2862 FND_GLOBAL.LOGIN_ID,
2863 FND_GLOBAL.LOGIN_ID,
2864 p_receipt_id,
2865 p_order_invoice,
2866 rec.p_6,
2867 rec.p_7,
2868 rec.p_8,
2869 rec.p_9,
2870 rec.p_10
2871 );
2872 END IF;
2873 --added if condition for bug#16082873
2874 if rec.tax_rate is null then
2875 tax_rate_tab(rec.lno):=-9999;
2876 else
2877 Tax_Rate_Tab(rec.lno) := nvl(rec.Tax_Rate,0);
2878 end if;
2879 Adhoc_Flag_Tab(rec.lno) := nvl(rec.adhoc_flag,'N'); /* Added rallamse bug#5068418 */
2880 Tax_Amt_Tab(rec.lno) := 0; --Add by Kevin Cheng for bug#6881225 Mar 28, 2008
2881 IF ( excise_flag_set = FALSE AND rec.tax_type_val = 1 ) OR ( rec.tax_type_val <> 1 ) THEN
2882
2883 P1(rec.lno) := nvl(rec.p_1,-1);
2884 P2(rec.lno) := nvl(rec.p_2,-1);
2885 P3(rec.lno) := nvl(rec.p_3,-1);
2886 P4(rec.lno) := nvl(rec.p_4,-1);
2887 P5(rec.lno) := nvl(rec.p_5,-1);
2888 P6(rec.lno) := nvl(rec.p_6,-1);
2889 P7(rec.lno) := nvl(rec.p_7,-1);
2890 P8(rec.lno) := nvl(rec.p_8,-1);
2891 P9(rec.lno) := nvl(rec.p_9,-1);
2892 P10(rec.lno) := nvl(rec.p_10,-1);
2893
2894
2895 IF rec.tax_type_val = 1 THEN
2896 tax_rate_tab(rec.lno) := -1;
2897 P1(rec.lno) := -1;
2898 P2(rec.lno) := -1;
2899 P3(rec.lno) := -1;
2900 P4(rec.lno) := -1;
2901 P5(rec.lno) := -1;
2902 P6(rec.lno) := -1;
2903 P7(rec.lno) := -1;
2904 P8(rec.lno) := -1;
2905 P9(rec.lno) := -1;
2906 P10(rec.lno) := -1;
2907 Tax_Amt_Tab(rec.lno) := v_excise_duty_rate;
2908 tax_target_tab(rec.lno) := v_excise_duty_rate;
2909 /* Bug#4111609 added code for cess */
2910
2911
2912
2913 ELSIF excise_flag_set AND rec.tax_type_val = 3 then
2914 /*
2915 || Start of bug 4284335
2916 || Code modified by aiyer for the bug 4284335
2917 */
2918 IF nvl(ln_cess_duty_rate,0) <> 0 THEN
2919 /*
2920 ||other_tax_credit in rg23_d is not null
2921 */
2922 tax_rate_tab(rec.lno) := -1;
2923 P1(rec.lno) := -1;
2924 P2(rec.lno) := -1;
2925 P3(rec.lno) := -1;
2926 P4(rec.lno) := -1;
2927 P5(rec.lno) := -1;
2928 P6(rec.lno) := -1;
2929 P7(rec.lno) := -1;
2930 P8(rec.lno) := -1;
2931 P9(rec.lno) := -1;
2932 P10(rec.lno) := -1;
2933
2934 END IF;
2935 /*
2936 ||End of Bug 4284335
2937 */
2938
2939 Tax_Amt_Tab(rec.lno) := ln_cess_duty_rate;
2940 tax_target_tab(rec.lno) := ln_cess_duty_rate;
2941 /* end bug#4111609 */
2942
2943 -- bug 6470006
2944 ELSIF excise_flag_set AND rec.tax_type_val = 4 then
2945 IF nvl(ln_sh_cess_duty_rate,0) <> 0 THEN
2946 /*
2947 ||other_tax_credit in rg23_d is not null
2948 */
2949 tax_rate_tab(rec.lno) := -1;
2950 P1(rec.lno) := -1;
2951 P2(rec.lno) := -1;
2952 P3(rec.lno) := -1;
2953 P4(rec.lno) := -1;
2954 P5(rec.lno) := -1;
2955 P6(rec.lno) := -1;
2956 P7(rec.lno) := -1;
2957 P8(rec.lno) := -1;
2958 P9(rec.lno) := -1;
2959 P10(rec.lno) := -1;
2960
2961 END IF;
2962 Tax_Amt_Tab(rec.lno) := ln_sh_cess_duty_rate;
2963 tax_target_tab(rec.lno) := ln_sh_cess_duty_rate;
2964 -- end bug 6470006
2965
2966 END IF;
2967
2968 ELSIF excise_flag_set AND rec.tax_type_val = 1 THEN
2969 P1(rec.lno) := -1;
2970 P2(rec.lno) := -1;
2971 P3(rec.lno) := -1;
2972 P4(rec.lno) := -1;
2973 P5(rec.lno) := -1;
2974 P6(rec.lno) := -1;
2975 P7(rec.lno) := -1;
2976 P8(rec.lno) := -1;
2977 P9(rec.lno) := -1;
2978 P10(rec.lno) := -1;
2979 tax_rate_tab(rec.lno) := -1;
2980 Tax_Amt_Tab(rec.lno) := 0;
2981 tax_target_tab(rec.lno) := 0;
2982 END IF;
2983
2984 IF v_exempt_flag = 'Y' AND rec.tax_type_val = 1 THEN
2985 P1(rec.lno) := -1;
2986 P2(rec.lno) := -1;
2987 P3(rec.lno) := -1;
2988 P4(rec.lno) := -1;
2989 P5(rec.lno) := -1;
2990 P6(rec.lno) := -1;
2991 P7(rec.lno) := -1;
2992 P8(rec.lno) := -1;
2993 P9(rec.lno) := -1;
2994 P10(rec.lno) := -1;
2995
2996 tax_rate_tab(rec.lno) := -1;
2997 Tax_Amt_Tab(rec.lno) := 0;
2998 tax_target_tab(rec.lno) := 0;
2999
3000 END IF;
3001 Rounding_factor_tab(rec.lno) := rec.rnd;
3002 Tax_Type_Tab(rec.lno) := rec.tax_type_val;
3003
3004 --Add by Kevin Cheng for inclusive tax Dec 12, 2007
3005 ---------------------------------------------------
3006 lt_tax_amt_rate_tax_tab(rec.lno) :=0;
3007 lt_tax_amt_non_rate_tab(rec.lno) :=0; -- tax inclusive
3008 lt_base_tax_amt_tab(rec.lno) := 0;
3009 ---------------------------------------------------
3010
3011 --IF nvl(rec.tax_rate,0) = 0 AND nvl(rec.qty_rate,0) = 0 THEN
3012 /*added if condition for bug#16082873*/
3013 IF rec.tax_rate is null AND nvl(rec.qty_rate,0) = 0 THEN
3014 tax_rate_tab(rec.lno) := -1;
3015 -- Start of addition by Srihari and Gaurav on 11-JUL-2000
3016 IF rec.tax_type_val = 1 THEN
3017 /*Tax_Amt_Tab(rec.lno) := v_excise_duty_rate;*/ --Comment out by Kevin Cheng for inclusive tax 2008/01/15
3018 lt_tax_amt_non_rate_tab(rec.lno) := v_excise_duty_rate; --Add by Kevin Cheng for inclusive tax 2008/01/15
3019 tax_target_tab(rec.lno) := v_excise_duty_rate;
3020 ELSIF rec.tax_type_val = 3 then
3021 /*Tax_Amt_Tab(rec.lno) := ln_cess_duty_rate;*/ --Comment out by Kevin Cheng for inclusive tax 2008/01/15
3022 lt_tax_amt_non_rate_tab(rec.lno) := ln_cess_duty_rate; --Add by Kevin Cheng for inclusive tax 2008/01/15
3023 tax_target_tab(rec.lno) := ln_cess_duty_rate;
3024 ELSIF rec.tax_type_val = 4 THEN -- bug 6470006
3025 /*Tax_Amt_Tab(rec.lno) := ln_sh_cess_duty_rate;*/ --Comment out by Kevin Cheng for inclusive tax 2008/01/15
3026 lt_tax_amt_non_rate_tab(rec.lno) := ln_sh_cess_duty_rate; --Add by Kevin Cheng for inclusive tax 2008/01/15
3027 tax_target_tab(rec.lno) := ln_sh_cess_duty_rate;
3028 ELSE
3029 /*tax_amt_tab(rec.lno) := (p_line_quantity / v_original_quantity) * rec.tax_amount;*/ --Comment out by Kevin Cheng for inclusive tax 2008/01/15
3030 lt_tax_amt_non_rate_tab(rec.lno) := (p_line_quantity / v_original_quantity) * rec.tax_amount; --Add by Kevin Cheng for inclusive tax 2008/01/15
3031 tax_target_tab(rec.lno) := (p_line_quantity / v_original_quantity) * rec.tax_amount;
3032
3033 -- End of bug 3611625
3034 END IF;
3035 lt_base_tax_amt_tab(rec.lno) := lt_tax_amt_non_rate_tab(rec.lno); --Add by Kevin Cheng for inclusive tax 2008/01/15
3036 ELSE
3037 IF rec.tax_type_val NOT IN (1,3,4) THEN -- bug 6470006
3038 Tax_Amt_Tab(rec.lno) := 0;
3039 END IF;
3040 END IF;
3041
3042
3043 --Add by Kevin Cheng for inclusive tax Dec 12, 2007
3044 ---------------------------------------------------
3045 lt_tax_rate_per_rupee(rec.lno) := NVL(rec.tax_rate,0)/100;
3046 ln_total_tax_per_rupee := 0;
3047 lt_inclu_tax_tab(rec.lno) := NVL(rec.inclusive_tax_flag,'N');
3048
3049 IF rec.tax_rate is null THEN
3050 lt_tax_rate_zero_tab(rec.lno) := 0;
3051 ELSIF rec.tax_rate = 0 THEN
3052 lt_tax_rate_zero_tab(rec.lno) := -9999;
3053 ELSE
3054 lt_tax_rate_zero_tab(rec.lno) := rec.tax_rate;
3055 END IF;
3056 ---------------------------------------------------
3057 --Comment out by Kevin Cheng for inclusive tax Dec 12, 2007
3058 /*IF rec.Valid_Date is NULL Or rec.Valid_Date >= Sysdate THEN
3059 End_Date_Tab(rec.lno) := 1;
3060 ELSE
3061 End_Date_Tab(rec.lno) := 0;
3062 END IF; */
3063
3064 row_count := row_count + 1;
3065 --IF tax_rate_tab(rec.lno) = 0 THEN
3066 --added if condition for bug#16082873
3067 IF rec.tax_rate is null AND nvl(rec.qty_rate,0) <>0 THEN
3068 /*added if condition for bug#16082873*/
3069 FOR uom_cls IN uom_class_cur(v_unit_code, rec.uom) LOOP
3070 INV_CONVERT.inv_um_conversion(v_unit_code, rec.uom, v_inventory_item_id, v_conversion_rate);
3071 IF nvl(v_conversion_rate, 0) <= 0 THEN
3072 INV_CONVERT.inv_um_conversion(v_unit_code, rec.uom, 0, v_conversion_rate);
3073 IF nvl(v_conversion_rate, 0) <= 0 THEN
3074 v_conversion_rate := 0;
3075 END IF;
3076 END IF;
3077 IF ( excise_flag_set ) AND ( rec.tax_type_val = 1 ) THEN
3078 /*tax_amt_tab(rec.lno) := 0;*/ --Comment out by Kevin Cheng for inclusive tax Dec 12, 2007
3079 lt_tax_amt_non_rate_tab(rec.lno) := 0; --Add by Kevin Cheng for inclusive tax Dec 12, 2007
3080 ELSIF rec.tax_type_val = 3 then
3081 /*Tax_Amt_Tab(rec.lno) := ln_cess_duty_rate; */ --Comment out by Kevin Cheng for inclusive tax Dec 12, 2007
3082 lt_tax_amt_non_rate_tab(rec.lno) := ln_cess_duty_rate; --Add by Kevin Cheng for inclusive tax Dec 12, 2007
3083 tax_target_tab(rec.lno) := ln_cess_duty_rate;
3084 ELSIF rec.tax_type_val = 4 then
3085 /*Tax_Amt_Tab(rec.lno) := ln_sh_cess_duty_rate;*/ --Comment out by Kevin Cheng for inclusive tax Dec 12, 2007
3086 lt_tax_amt_non_rate_tab(rec.lno) := ln_sh_cess_duty_rate; --Add by Kevin Cheng for inclusive tax Dec 12, 2007
3087 tax_target_tab(rec.lno) := ln_sh_cess_duty_rate;
3088 ELSE
3089 --Comment out by Kevin Cheng for inclusive tax Dec 12, 2007
3090 /*tax_amt_tab(rec.lno) := ROUND( nvl(rec.qty_rate * v_conversion_rate, 0) * p_line_quantity, rounding_factor_tab(rec.lno) ); */
3091 --Add by Kevin Cheng for inclusive tax Dec 12, 2007
3092 lt_tax_amt_non_rate_tab(rec.lno) := /*ROUND( */nvl(rec.qty_rate * v_conversion_rate, 0) * p_line_quantity/*, rounding_factor_tab(rec.lno) )*/; --Modified by Kevin Cheng for bug#6936009 April 02, 2008
3093 END IF;
3094
3095 IF v_exempt_flag = 'Y' AND rec.tax_type_val = 1 THEN
3096 /*tax_amt_tab( rec.lno ) := 0; */ --Comment out by Kevin Cheng for inclusive tax Dec 12, 2007
3097 lt_tax_amt_non_rate_tab(rec.lno) := 0; --Add by Kevin Cheng for inclusive tax Dec 12, 2007
3098 END IF;
3099 lt_base_tax_amt_tab(rec.lno) := lt_tax_amt_non_rate_tab(rec.lno); --Add by Kevin Cheng for inclusive tax Dec 12, 2007
3100 tax_rate_tab( rec.lno ) := -1;
3101 tax_target_tab(rec.lno) := tax_amt_tab( rec.lno );
3102 END LOOP;
3103 END IF;
3104
3105 --Add by Kevin Cheng for inclusive tax Dec 12, 2007
3106 ---------------------------------------------------
3107 IF rec.Valid_Date is NULL OR rec.Valid_Date >= SYSDATE
3108 THEN
3109 End_Date_Tab(rec.lno) := 1;
3110 ELSE
3111 End_Date_Tab(rec.lno) := 0;
3112 tax_amt_tab(rec.lno) := 0;
3113 END IF;
3114 ---------------------------------------------------
3115
3116 IF rec.tax_type_val = 1 THEN
3117 excise_flag_set := TRUE;
3118 END IF;
3119 END LOOP;
3120 -- ended lines bug 6030615
3121
3122
3123 END IF;
3124 -----------------------------------------------------------------------------------------------------
3125 bsln_amt := v_selling_price* p_line_quantity;
3126
3127 --Add by Kevin Cheng for inclusive tax Dec 12, 2007
3128 ----------------------------------------------------
3129 IF ln_vat_assessable_value_tot <> ln_line_amount
3130 THEN
3131 ln_vat_assessable_value_tmp := ln_vat_assessable_value_tot;
3132 ELSE
3133 ln_vat_assessable_value_tmp := 1;
3134 END IF;
3135
3136 --Add by Xiao for GST, refer to bug#10043656, begin
3137 ----------------------------------------------------
3138 IF ln_gst_assessable_value_tot <> ln_line_amount
3139 THEN
3140 ln_gst_assessable_value_tmp := ln_gst_assessable_value_tot;
3141 ELSE
3142 ln_gst_assessable_value_tmp := 1;
3143 END IF;
3144
3145 ----------------------------------------------------
3146 --Add by Xiao for GST, refer to bug#10043656, end
3147
3148 if ln_assessable_value_tmp_tot <> ln_line_amount
3149 THEN
3150 ln_assessable_value_tmp := ln_assessable_value_tmp_tot;
3151 ELSE
3152 ln_assessable_value_tmp := 1;
3153 END IF;
3154 --start additions for bug#8887871
3155 IF ln_vat_reversal_value_tot <> ln_line_amount
3156 THEN
3157 ln_vat_reversal_value_tmp := ln_vat_reversal_value_tot;
3158 ELSE
3159 ln_vat_reversal_value_tmp := 1;
3160 END IF;
3161
3162 ----------------------------------------------------
3163
3164 FOR I in 1..row_count
3165 LOOP
3166 --Comment out by Kevin Cheng for inclusive tax Dec 12, 2007
3167 /*IF p1(I) < I and p1(I) not in (-1,0) then
3168 vamt := vamt + nvl(tax_amt_tab(p1(I)),0);
3169 ELSIF p1(I) = 0 then
3170 vamt := vamt + bsln_amt;
3171 END IF;
3172 IF p2(I) < I and p2(I) not in (-1,0) then
3173 vamt := vamt + nvl(tax_amt_tab(p2(I)),0);
3174 ELSIF p2(I) = 0 then
3175 vamt := vamt + bsln_amt;
3176 END IF;
3177 IF p3(I) < I and p3(I) not in (-1,0) then
3178 vamt := vamt + nvl(tax_amt_tab(p3(I)),0);
3179 ELSIF p3(I) = 0 then
3180 vamt := vamt + bsln_amt;
3181 END IF;
3182 IF p4(I) < I and p4(I) not in (-1,0) then
3183 vamt := vamt + nvl(tax_amt_tab(p4(I)),0);
3184 ELSIF p4(I) = 0 then
3185 vamt := vamt + bsln_amt;
3186 END IF;
3187 IF p5(I) < I and p5(I) not in (-1,0) then
3188 vamt := vamt + nvl(tax_amt_tab(p5(I)),0);
3189 ELSIF p5(I) = 0 then
3190 vamt := vamt + bsln_amt;
3191 END IF;
3192
3193 -- Date 01-NOV-2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
3194 -- START BUG 5228046
3195
3196 IF p6(I) < I and p6(I) not in (-1,0) then
3197 vamt := vamt + nvl(tax_amt_tab(p6(I)),0);
3198 ELSIF p6(I) = 0 then
3199 vamt := vamt + bsln_amt;
3200 END IF;
3201 IF p7(I) < I and p7(I) not in (-1,0) then
3202 vamt := vamt + nvl(tax_amt_tab(p7(I)),0);
3203 ELSIF p7(I) = 0 then
3204 vamt := vamt + bsln_amt;
3205 END IF;
3206 IF p8(I) < I and p8(I) not in (-1,0) then
3207 vamt := vamt + nvl(tax_amt_tab(p8(I)),0);
3208 ELSIF p8(I) = 0 then
3209 vamt := vamt + bsln_amt;
3210 END IF;
3211 IF p9(I) < I and p9(I) not in (-1,0) then
3212 vamt := vamt + nvl(tax_amt_tab(p9(I)),0);
3213 ELSIF p9(I) = 0 then
3214 vamt := vamt + bsln_amt;
3215 END IF;
3216 IF p10(I) < I and p10(I) not in (-1,0) then
3217 vamt := vamt + nvl(tax_amt_tab(p10(I)),0);
3218 ELSIF p10(I) = 0 then
3219 vamt := vamt + bsln_amt;
3220 END IF;
3221 -- END BUG 5228046
3222
3223
3224 IF tax_rate_tab(I) <> -1 THEN
3225 v_tax_amt := v_tax_amt + (vamt * (tax_rate_tab(I)/100));
3226 IF END_date_tab(I) = 0 then
3227 tax_amt_tab(I) := 0;
3228 ELSIF END_date_tab(I) = 1 then
3229 tax_amt_tab(I) := nvl(tax_amt_tab(I),0) + v_tax_amt;
3230 END IF;
3231 -- tax_amt_tab(I) := nvl(tax_amt_tab(I),0) + v_tax_amt;
3232 END IF;
3233 vamt := 0;
3234 v_tax_amt := 0;*/
3235 --Add by Kevin Cheng for inclusive tax Dec 12, 2007
3236 --------------------------------------------------------------------------------
3237 IF end_date_tab( I ) <> 0
3238 THEN
3239 IF tax_type_tab(I) = 1
3240 THEN
3241 IF ln_assessable_value_tmp = 1 THEN
3242 bsln_amt := 1;
3243 ln_bsln_amt_nr := 0;
3244 ELSE
3245 bsln_amt := 0;
3246 ln_bsln_amt_nr := ln_assessable_value_tmp;
3247 END IF;
3248 ELSIF tax_type_tab(I)=5 --IN (5, 6) bug 8887871
3249 THEN --IF tax_type_tab(I) = 1 THEN
3250 IF ln_vat_assessable_value_tmp = 1 THEN
3251 bsln_amt := 1;
3252 ln_bsln_amt_nr := 0;
3253 ELSE
3254 bsln_amt := 0;
3255 ln_bsln_amt_nr := ln_vat_assessable_value_tmp;
3256 END IF;
3257 --Add by Xiao for GST, refer to bug#10043656, begin
3258 ----------------------------------------------------
3259 ELSIF tax_type_tab(I)=7
3260 THEN
3261 IF ln_gst_assessable_value_tmp = 1 THEN
3262 bsln_amt := 1;
3263 ln_bsln_amt_nr := 0;
3264 ELSE
3265 bsln_amt := 0;
3266 ln_bsln_amt_nr := ln_gst_assessable_value_tmp;
3267 END IF;
3268 ----------------------------------------------------
3269 --Add by Xiao for GST, refer to bug#10043656, end
3270 -- start additions for bug 8887871
3271 ELSIF tax_type_tab(I)=6
3272 THEN --IF tax_type_tab(I) = 1 THEN
3273 IF ln_vat_reversal_value_tmp = 1 THEN
3274 bsln_amt := 1;
3275 ln_bsln_amt_nr := 0;
3276 ELSE
3277 bsln_amt := 0;
3278 ln_bsln_amt_nr := ln_vat_reversal_value_tmp;
3279 END IF;
3280 ELSIF tax_type_tab(I) IN (3, 4)
3281 THEN --IF tax_type_tab(I) = 1 THEN
3282 bsln_amt := 0;
3283 ln_bsln_amt_nr := 0;
3284 ELSE --IF tax_type_tab(I) = 1 THEN
3285 bsln_amt := 1;
3286 ln_bsln_amt_nr := 0;
3287 END IF; --IF tax_type_tab(I) = 1 THEN
3288
3289 IF tax_rate_tab(I) <> 0
3290 THEN
3291 /* Commented out by Shyan for bug 10358786 on 28-Jan-2011 , Begin
3292 IF p1(I) < I and p1(I) not in (-1,0) then
3293 vamt := vamt + nvl(tax_amt_tab(p1(I)),0);
3294 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P1(I)),0);
3295 ELSIF p1(I) = 0 then
3296 vamt := vamt + bsln_amt;
3297 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr;
3298 END IF;
3299 IF p2(I) < I and p2(I) not in (-1,0) then
3300 vamt := vamt + nvl(tax_amt_tab(p2(I)),0);
3301 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P2(I)),0);
3302 ELSIF p2(I) = 0 then
3303 vamt := vamt + bsln_amt;
3304 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr;
3305 END IF;
3306 IF p3(I) < I and p3(I) not in (-1,0) then
3307 vamt := vamt + nvl(tax_amt_tab(p3(I)),0);
3308 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P3(I)),0);
3309 ELSIF p3(I) = 0 then
3310 vamt := vamt + bsln_amt;
3311 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr;
3312 END IF;
3313 IF p4(I) < I and p4(I) not in (-1,0) then
3314 vamt := vamt + nvl(tax_amt_tab(p4(I)),0);
3315 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P4(I)),0);
3316 ELSIF p4(I) = 0 then
3317 vamt := vamt + bsln_amt;
3318 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr;
3319 END IF;
3320 IF p5(I) < I and p5(I) not in (-1,0) then
3321 vamt := vamt + nvl(tax_amt_tab(p5(I)),0);
3322 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P5(I)),0);
3323 ELSIF p5(I) = 0 then
3324 vamt := vamt + bsln_amt;
3325 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr;
3326 END IF;
3327
3328 -- Date 01-NOV-2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
3329 -- START BUG 5228046
3330
3331 IF p6(I) < I and p6(I) not in (-1,0) then
3332 vamt := vamt + nvl(tax_amt_tab(p6(I)),0);
3333 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P6(I)),0);
3334 ELSIF p6(I) = 0 then
3335 vamt := vamt + bsln_amt;
3336 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr;
3337 END IF;
3338 IF p7(I) < I and p7(I) not in (-1,0) then
3339 vamt := vamt + nvl(tax_amt_tab(p7(I)),0);
3340 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P7(I)),0);
3341 ELSIF p7(I) = 0 then
3342 vamt := vamt + bsln_amt;
3343 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr;
3344 END IF;
3345 IF p8(I) < I and p8(I) not in (-1,0) then
3346 vamt := vamt + nvl(tax_amt_tab(p8(I)),0);
3347 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P8(I)),0);
3348 ELSIF p8(I) = 0 then
3349 vamt := vamt + bsln_amt;
3350 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr;
3351 END IF;
3352 IF p9(I) < I and p9(I) not in (-1,0) then
3353 vamt := vamt + nvl(tax_amt_tab(p9(I)),0);
3354 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P9(I)),0);
3355 ELSIF p9(I) = 0 then
3356 vamt := vamt + bsln_amt;
3357 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr;
3358 END IF;
3359 IF p10(I) < I and p10(I) not in (-1,0) then
3360 vamt := vamt + nvl(tax_amt_tab(p10(I)),0);
3361 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P10(I)),0);
3362 ELSIF p10(I) = 0 then
3363 vamt := vamt + bsln_amt;
3364 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr;
3365 END IF;
3366 -- END BUG 5228046
3367 Commented out by Shyan for bug 10358786 on 28-Jan-2011, End*/
3368 --Added by Shujuan for 10358786 on 28-Jan-2011, Begin
3369 -------------------------------------------------------
3370 --Qiong fix bug12687384 2011.06.29 begin
3371 --for P1 to P10 changed : lt_inclu_tax_tab(P1(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P1(I)) IS NOT NULL
3372 --to ( lt_inclu_tax_tab(P1(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P1(I)) IS NOT NULL ) and Adhoc_Flag_Tab(P1(I))='N'
3373 -------------------------------------------------------------------------------------------------------------------
3374 lt_tax_amt_non_rate_tab(I):=0;
3375 IF P1(I) < I AND P1(I) NOT IN (-1, 0) THEN
3376 IF ( lt_inclu_tax_tab(P1(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P1(I)) IS NOT NULL )/* and Adhoc_Flag_Tab(P1(I))='N'*/ then
3377 --Modified by Junjian for bug#14730054 on 23-Oct-2012
3378 vamt := vamt + NVL(tax_amt_tab(P1(I)), 0);
3379 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P1(I)), 0);
3380 END IF;
3381
3382 ELSIF P1(I) = 0 THEN
3383 IF lt_inclu_tax_tab(I) = 'Y' then
3384 vamt := vamt + bsln_amt;
3385 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr;
3386 ELSE
3387 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + ln_line_amount;
3388
3389 END IF;
3390 END IF;
3391 IF p2(I) < I AND p2(I) NOT IN (-1, 0) THEN
3392 IF ( lt_inclu_tax_tab(P2(I)) = 'Y' OR
3393 lt_tax_amt_rate_tax_tab(P2(I)) IS NOT NULL ) /*and Adhoc_Flag_Tab(P2(I))='N'*/ then
3394 --Modified by Junjian for bug#14730054 on 23-Oct-2012
3395 vamt := vamt + NVL(tax_amt_tab(p2(I)), 0);
3396 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P2(I)), 0);
3397 END IF;
3398
3399 ELSIF p2(I) = 0 THEN
3400 IF lt_inclu_tax_tab(I) = 'Y' then
3401 vamt := vamt + bsln_amt;
3402 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
3403 ELSE
3404 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + ln_line_amount;
3405 END IF;
3406
3407 END IF;
3408 IF p3(I) < I AND p3(I) NOT IN (-1, 0) THEN
3409 IF ( lt_inclu_tax_tab(P3(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P3(I)) IS NOT NULL )/* and Adhoc_Flag_Tab(P3(I))='N'*/ then
3410 --Modified by Junjian for bug#14730054 on 23-Oct-2012
3411 vamt := vamt + NVL(tax_amt_tab(p3(I)), 0);
3412 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P3(I)), 0);
3413 END IF;
3414
3415 ELSIF p3(I) = 0 THEN
3416 IF lt_inclu_tax_tab(I) = 'Y' then
3417 vamt := vamt + bsln_amt;
3418 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr;
3419 ELSE
3420 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + ln_line_amount;
3421 END IF;
3422 END IF;
3423
3424 IF p4(I) < I AND p4(I) NOT IN (-1, 0) THEN
3425 IF ( lt_inclu_tax_tab(P4(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P4(I)) IS NOT NULL )/* and Adhoc_Flag_Tab(P4(I))='N'*/ then
3426 --Modified by Junjian for bug#14730054 on 23-Oct-2012
3427 vamt := vamt + NVL(tax_amt_tab(p4(I)), 0);
3428 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P4(I)), 0);
3429 END IF;
3430 ELSIF p4(I) = 0 THEN
3431 IF lt_inclu_tax_tab(I) = 'Y' then
3432 vamt := vamt + bsln_amt;
3433 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr;
3434 ELSE
3435 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + ln_line_amount;
3436 END IF;
3437 END IF;
3438
3439 IF p5(I) < I AND p5(I) NOT IN (-1, 0) THEN
3440 IF ( lt_inclu_tax_tab(P5(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P5(I)) IS NOT NULL ) /*and Adhoc_Flag_Tab(P5(I))='N'*/ then
3441 --Modified by Junjian for bug#14730054 on 23-Oct-2012
3442 vamt := vamt + NVL(tax_amt_tab(p5(I)), 0);
3443 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P5(I)), 0);
3444 END IF;
3445 ELSIF p5(I) = 0 THEN
3446 IF lt_inclu_tax_tab(I) = 'Y' then
3447 vamt := vamt + bsln_amt;
3448 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr;
3449 ELSE
3450 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + ln_line_amount;
3451 END IF;
3452 END IF;
3453 -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
3454 -- start bug 5228046
3455 IF p6(I) < I AND p6(I) NOT IN (-1, 0) THEN
3456 IF ( lt_inclu_tax_tab(P6(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P6(I)) IS NOT NULL ) /*and Adhoc_Flag_Tab(P6(I))='N'*/ then
3457 --Modified by Junjian for bug#14730054 on 23-Oct-2012
3458 vamt := vamt + NVL(tax_amt_tab(p6(I)), 0);
3459 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P6(I)), 0);
3460 END IF;
3461
3462 ELSIF p6(I) = 0 THEN
3463 IF lt_inclu_tax_tab(I) = 'Y' then
3464 vamt := vamt + bsln_amt;
3465 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr;
3466 ELSE
3467 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + ln_line_amount;
3468 END IF;
3469 END IF;
3470
3471 IF p7(I) < I AND p7(I) NOT IN (-1, 0) THEN
3472 IF ( lt_inclu_tax_tab(P7(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P7(I)) IS NOT NULL )/* and Adhoc_Flag_Tab(P7(I))='N'*/ then
3473 --Modified by Junjian for bug#14730054 on 23-Oct-2012
3474 vamt := vamt + NVL(tax_amt_tab(p7(I)), 0);
3475 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P7(I)), 0);
3476 END IF;
3477
3478 ELSIF p7(I) = 0 THEN
3479 IF lt_inclu_tax_tab(I) = 'Y' then
3480 vamt := vamt + bsln_amt;
3481 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr;
3482 ELSE
3483 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + ln_line_amount;
3484 END IF;
3485 END IF;
3486
3487 IF p8(I) < I AND p8(I) NOT IN (-1, 0) THEN
3488 IF ( lt_inclu_tax_tab(P4(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P8(I)) IS NOT NULL )/* and Adhoc_Flag_Tab(P8(I))='N'*/ then
3489 --Modified by Junjian for bug#14730054 on 23-Oct-2012
3490 vamt := vamt + NVL(tax_amt_tab(p8(I)), 0);
3491 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P8(I)), 0);
3492 END IF;
3493
3494 ELSIF p8(I) = 0 THEN
3495 IF lt_inclu_tax_tab(I) = 'Y' then
3496 vamt := vamt + bsln_amt;
3497 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr;
3498 ELSE
3499 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + ln_line_amount;
3500 END IF;
3501 END IF;
3502
3503 IF p9(I) < I AND p9(I) NOT IN (-1, 0) THEN
3504 IF ( lt_inclu_tax_tab(P9(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P9(I)) IS NOT NULL ) /*and Adhoc_Flag_Tab(P9(I))='N'*/ then
3505 --Modified by Junjian for bug#14730054 on 23-Oct-2012
3506 vamt := vamt + NVL(tax_amt_tab(p4(I)), 0);
3507 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P9(I)), 0);
3508 END IF;
3509
3510 ELSIF p9(I) = 0 THEN
3511 IF lt_inclu_tax_tab(I) = 'Y' then
3512 vamt := vamt + bsln_amt;
3513 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr;
3514 ELSE
3515 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + ln_line_amount;
3516 END IF;
3517 END IF;
3518
3519 IF p10(I) < I AND p10(I) NOT IN (-1, 0) THEN
3520 IF ( lt_inclu_tax_tab(P10(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P10(I)) IS NOT NULL ) /*and Adhoc_Flag_Tab(P10(I))='N'*/ then
3521 --Modified by Junjian for bug#14730054 on 23-Oct-2012
3522 vamt := vamt + NVL(tax_amt_tab(p10(I)), 0);
3523 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P10(I)), 0);
3524 END IF;
3525
3526 ELSIF p10(I) = 0 THEN
3527 IF lt_inclu_tax_tab(I) = 'Y' then
3528 vamt := vamt + bsln_amt;
3529 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr;
3530 ELSE
3531 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + ln_line_amount;
3532 END IF;
3533 END IF;
3534 --Qiong fix bug12687384 2011.06.29 end
3535 -------------------------------------------------------------------------------------------------------------------
3536 -- end bug 5228046
3537 -------------------------------------------------------
3538 --Added by Shujuan for 10358786 on 28-Jan-2011, End
3539 IF tax_rate_tab(I) <> -1 THEN
3540 v_tax_amt := v_tax_amt + (vamt * (tax_rate_tab(I)/100));
3541 IF END_date_tab(I) = 0 then
3542 tax_amt_tab(I) := 0;
3543 --Changed by qiong.liu for bug12687384 begin
3544 --------------------------------------------------
3545 --ELSIF END_date_tab(I) = 1 then
3546 ELSIF END_date_tab(I) = 1 and Adhoc_Flag_Tab(I) = 'N' then
3547 --Changed by qiong.liu for bug12687384 end
3548 ---------------------------------------------------
3549 tax_amt_tab(I) := nvl(tax_amt_tab(I),0) + v_tax_amt;
3550 END IF;
3551 -- tax_amt_tab(I) := nvl(tax_amt_tab(I),0) + v_tax_amt;
3552 END IF;
3553 ln_tax_amt_nr := ln_tax_amt_nr + (ln_vamt_nr * (tax_rate_tab(I)/100));
3554 lt_base_tax_amt_tab(I) := vamt;
3555 lt_tax_amt_non_rate_tab(I) := NVL(lt_tax_amt_non_rate_tab(I),0) + ln_tax_amt_nr; -- tax inclusive
3556 --Commented out by Shyan for bug 10358786 on 28-Jan-2011 , Begin
3557 -------------------------------------------------------------------
3558 --lt_tax_amt_rate_tax_tab(I):= tax_amt_tab(I); --added by walton for inclusive tax
3559 -------------------------------------------------------------------
3560 -- Commented out by Shyan for bug 10358786 on 28-Jan-2011 , End
3561
3562 -- Added by Shyan for bug 10358786 on 28-Jan-2011 , Begin
3563 -------------------------------------------------------------------
3564 --Changed by qiong.liu for bug12687384 begin
3565 -------------------------------------------------
3566 --IF lt_inclu_tax_tab(I) = 'Y' then
3567 IF lt_inclu_tax_tab(I) = 'Y' and Adhoc_Flag_Tab(I) = 'N' then
3568 --------------------------------------------------
3569 --Changed by qiong.liu for bug12687384 end
3570 lt_tax_amt_rate_tax_tab(I) := tax_amt_tab(I); --added by walton for inclusive tax
3571 END IF;
3572 -------------------------------------------------------------------
3573 -- Added by Shyan for bug 10358786 on 28-Jan-2011, End
3574 base_tax_amount_nr_tab(I):=ln_vamt_nr;---added for bug#9794835
3575 vamt := 0;
3576 v_tax_amt := 0;
3577 ln_tax_amt_nr := 0;
3578 ln_vamt_nr := 0;
3579 END IF;
3580 --Changed by qiong.liu for bug12687384 begin
3581 -------------------------------------------------
3582 --ELSE
3583 ELSIF Adhoc_Flag_Tab(I)='N' THEN --IF end_date_tab(I) <> 0 THEN
3584 --------------------------------------------------
3585 --Changed by qiong.liu for bug12687384 end
3586 tax_amt_tab(I) := 0;
3587 lt_base_tax_amt_tab(I) := 0;
3588 END IF;
3589 --------------------------------------------------------------------------------
3590 END LOOP;
3591 -----------LOOP2------------------------------------------------------------------
3592 FOR I in 1..row_count
3593 LOOP
3594 --Comment out by Kevin Cheng for inclusive tax Dec 12, 2007
3595 /*IF p1(I) > I then
3596 vamt := vamt + nvl(tax_amt_tab(p1(I)),0);
3597 END IF;
3598 IF p2(I) > I then
3599 vamt := vamt + nvl(tax_amt_tab(p2(I)),0);
3600 END IF;
3601 IF p3(I) > I then
3602 vamt := vamt + nvl(tax_amt_tab(p3(I)),0);
3603 END IF;
3604 IF p4(I) > I then
3605 vamt := vamt + nvl(tax_amt_tab(p4(I)),0);
3606 END IF;
3607 IF p5(I) > I then
3608 vamt := vamt + nvl(tax_amt_tab(p5(I)),0);
3609 END IF;
3610 -- Date 01-NOV-2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
3611 -- START BUG 5228046
3612
3613 IF p6(I) > I then
3614 vamt := vamt + nvl(tax_amt_tab(p6(I)),0);
3615 END IF;
3616 IF p7(I) > I then
3617 vamt := vamt + nvl(tax_amt_tab(p7(I)),0);
3618 END IF;
3619 IF p8(I) > I then
3620 vamt := vamt + nvl(tax_amt_tab(p8(I)),0);
3621 END IF;
3622 IF p9(I) > I then
3623 vamt := vamt + nvl(tax_amt_tab(p9(I)),0);
3624 END IF;
3625 IF p10(I) > I then
3626 vamt := vamt + nvl(tax_amt_tab(p10(I)),0);
3627 END IF;
3628
3629 -- END BUG 5228046
3630
3631
3632 IF tax_rate_tab(I) <> -1 THEN
3633 v_tax_amt := v_tax_amt + (vamt * (tax_rate_tab(I)/100));
3634 IF END_date_tab(I) = 0 then
3635 tax_amt_tab(I) := 0;
3636 ELSIF END_date_tab(I) = 1 then
3637 tax_amt_tab(I) := nvl(tax_amt_tab(I),0) + v_tax_amt;
3638 END IF;
3639 -- tax_amt_tab(I) := nvl(tax_amt_tab(I),0) + v_tax_amt;
3640 END IF;
3641 vamt := 0;
3642 v_tax_amt := 0;*/
3643 --Add by Kevin Cheng for inclusive tax Dec 12, 2007
3644 ----------------------------------------------------------------------
3645 IF end_date_tab( I ) <> 0 THEN
3646 IF tax_rate_tab(I) <> 0 THEN
3647 IF p1(I) > I then
3648 vamt := vamt + nvl(tax_amt_tab(p1(I)),0);
3649 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(p1(I)),0);
3650 END IF;
3651 IF p2(I) > I then
3652 vamt := vamt + nvl(tax_amt_tab(p2(I)),0);
3653 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(p2(I)),0);
3654 END IF;
3655 IF p3(I) > I then
3656 vamt := vamt + nvl(tax_amt_tab(p3(I)),0);
3657 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(p3(I)),0);
3658 END IF;
3659 IF p4(I) > I then
3660 vamt := vamt + nvl(tax_amt_tab(p4(I)),0);
3661 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(p4(I)),0);
3662 END IF;
3663 IF p5(I) > I then
3664 vamt := vamt + nvl(tax_amt_tab(p5(I)),0);
3665 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(p5(I)),0);
3666 END IF;
3667 -- Date 01-NOV-2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
3668 -- START BUG 5228046
3669
3670 IF p6(I) > I then
3671 vamt := vamt + nvl(tax_amt_tab(p6(I)),0);
3672 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(p6(I)),0);
3673 END IF;
3674 IF p7(I) > I then
3675 vamt := vamt + nvl(tax_amt_tab(p7(I)),0);
3676 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(p7(I)),0);
3677 END IF;
3678 IF p8(I) > I then
3679 vamt := vamt + nvl(tax_amt_tab(p8(I)),0);
3680 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(p8(I)),0);
3681 END IF;
3682 IF p9(I) > I then
3683 vamt := vamt + nvl(tax_amt_tab(p9(I)),0);
3684 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(p9(I)),0);
3685 END IF;
3686 IF p10(I) > I then
3687 vamt := vamt + nvl(tax_amt_tab(p10(I)),0);
3688 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(p10(I)),0);
3689 END IF;
3690
3691 -- END BUG 5228046
3692
3693 IF tax_rate_tab(I) <> -1 THEN
3694 v_tax_amt := v_tax_amt + (vamt * (tax_rate_tab(I)/100));
3695 IF END_date_tab(I) = 0 then
3696 tax_amt_tab(I) := 0;
3697 ELSIF END_date_tab(I) = 1 then
3698 tax_amt_tab(I) := nvl(tax_amt_tab(I),0) + v_tax_amt;
3699 END IF;
3700 -- tax_amt_tab(I) := nvl(tax_amt_tab(I),0) + v_tax_amt;
3701 END IF;
3702 lt_base_tax_amt_tab(I) := lt_base_tax_amt_tab(I) + vamt; --9794835
3703 ln_tax_amt_nr := ln_tax_amt_nr + (ln_vamt_nr * (tax_rate_tab(I)/100)); -- tax inclusive
3704 IF vamt <> 0 THEN
3705 lt_base_tax_amt_tab(I) := lt_base_tax_amt_tab(I) + vamt;
3706 END IF;
3707 lt_tax_amt_non_rate_tab(I) := NVL(lt_tax_amt_non_rate_tab(I),0) + ln_tax_amt_nr ; -- tax inclusive
3708 lt_tax_amt_rate_tax_tab(i) := tax_amt_tab(I);
3709 base_tax_amount_nr_tab(I):=ln_vamt_nr;---added for bug#9794835
3710 vamt := 0;
3711 ln_vamt_nr := 0 ;
3712 v_tax_amt := 0;
3713 ln_tax_amt_nr := 0 ;
3714 END IF; --IF tax_rate_tab(I) <> 0 THEN
3715 --Changed by qiong.liu for bug12687384 begin
3716 -------------------------------------------------
3717 --ELSE
3718 ELSIF Adhoc_Flag_Tab(I)='N' THEN --IF end_date_tab(I) <> 0 THEN
3719 --------------------------------------------------
3720 --Changed by qiong.liu for bug12687384 end
3721 lt_base_tax_amt_tab(I) := vamt;
3722 tax_amt_tab(I) := 0;
3723 END IF; --IF end_date_tab( I ) <> 0 THEN
3724 ----------------------------------------------------------------------
3725 END LOOP;
3726
3727 -------------LOOP3------------------------------------------------------
3728 FOR counter IN 1 .. max_iter LOOP
3729 vamt := 0;
3730 v_tax_amt := 0;
3731 ln_vamt_nr := 0; -- Add by Kevin Cheng for inclusive tax Dec 10, 2007
3732 ln_tax_amt_nr:=0; -- Add by Kevin Cheng for inclusive tax Dec 10, 2007
3733 FOR i IN 1 .. row_count LOOP
3734 /*
3735 || rallamse bug#5068418
3736 || The below if condition is for all taxes which comply as :
3737 || tax_rate_tab <> 0 => consider discounts also
3738 || adhoc_flag_tab = 'N' => for non-adhoc taxes only
3739 || tax_rate_tab(i) = -1 and tax_type_tab(i) IN (1,3) => for Excise and CVD considering cess also
3740 */
3741 /*Modified condition "tax_rate_tab( i ) <> 0" to "tax_rate_tab( i ) NOT IN (0,-1)" for bug# 6487182*/
3742 --Comment out by Kevin Cheng for inclusive tax Dec 12, 2007
3743 /*IF tax_rate_tab( i ) NOT IN (0,-1) AND End_Date_Tab(I) <> 0 AND adhoc_flag_tab(i) = 'N' AND NOT ( tax_rate_tab(i) = -1 AND tax_type_tab(i) IN (1,3,4) ) THEN \* Added for bug 5091874 *\\*Bug 5989740 bduvarag*\
3744 v_amt := bsln_amt;
3745 IF p1( i ) <> -1 THEN
3746 IF p1( i ) <> 0 THEN
3747 vamt := vamt + tax_amt_tab( p1( I ) );
3748 ELSIF p1(i) = 0 THEN
3749 vamt := vamt + v_amt;
3750 END IF;
3751 END IF;
3752 IF p2( i ) <> -1 THEN
3753 IF p2( i ) <> 0 THEN
3754 vamt := vamt + tax_amt_tab( p2( I ) );
3755 ELSIF p2(i) = 0 THEN
3756 vamt := vamt + v_amt;
3757 END IF;
3758 END IF;
3759 IF p3( i ) <> -1 THEN
3760 IF p3( i ) <> 0 THEN
3761 vamt := vamt + tax_amt_tab( p3( I ) );
3762 ELSIF p3(i) = 0 THEN
3763 vamt := vamt + v_amt;
3764 END IF;
3765 END IF;
3766 IF p4( i ) <> -1 THEN
3767 IF p4( i ) <> 0 THEN
3768 vamt := vamt + tax_amt_tab( p4( i ) );
3769 ELSIF p4(i) = 0 THEN
3770 vamt := vamt + v_amt;
3771 END IF;
3772 END IF;
3773 IF p5( i ) <> -1 THEN
3774 IF p5( i ) <> 0 THEN
3775 vamt := vamt + tax_amt_tab( p5( i ) );
3776 ELSIF p5(i) = 0 THEN
3777 vamt := vamt + v_amt;
3778 END IF;
3779 END IF;
3780
3781 -- Date 01-NOV-2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
3782 -- START BUG 5228046
3783
3784 IF p6( i ) <> -1 THEN
3785 IF p6( i ) <> 0 THEN
3786 vamt := vamt + tax_amt_tab( p6( I ) );
3787 ELSIF p6(i) = 0 THEN
3788 vamt := vamt + v_amt;
3789 END IF;
3790 END IF;
3791 IF p7( i ) <> -1 THEN
3792 IF p7( i ) <> 0 THEN
3793 vamt := vamt + tax_amt_tab( p7( I ) );
3794 ELSIF p7(i) = 0 THEN
3795 vamt := vamt + v_amt;
3796 END IF;
3797 END IF;
3798 IF p8( i ) <> -1 THEN
3799 IF p8( i ) <> 0 THEN
3800 vamt := vamt + tax_amt_tab( p8( I ) );
3801 ELSIF p8(i) = 0 THEN
3802 vamt := vamt + v_amt;
3803 END IF;
3804 END IF;
3805 IF p9( i ) <> -1 THEN
3806 IF p9( i ) <> 0 THEN
3807 vamt := vamt + tax_amt_tab( p9( i ) );
3808 ELSIF p9(i) = 0 THEN
3809 vamt := vamt + v_amt;
3810 END IF;
3811 END IF;
3812 IF p10( i ) <> -1 THEN
3813 IF p10( i ) <> 0 THEN
3814 vamt := vamt + tax_amt_tab( p10( i ) );
3815 ELSIF p10(i) = 0 THEN
3816 vamt := vamt + v_amt;
3817 END IF;
3818 END IF;
3819 -- END BUG 5228046
3820
3821
3822
3823 tax_target_tab(I) := vamt;
3824 IF counter = max_iter THEN
3825 v_tax_amt := ROUND( v_tax_amt + ( vamt * ( tax_rate_tab( i )/100)), rounding_factor_tab(I) );
3826 ELSE
3827 v_tax_amt := v_tax_amt + ( vamt * ( tax_rate_tab( i )/100));
3828 END IF;
3829 tax_amt_tab( I ) := NVL( v_tax_amt, 0 );
3830 ELSIF tax_rate_tab( i ) = -1 AND End_Date_Tab(I) <> 0 THEN
3831 NULL;
3832 ELSE
3833 tax_amt_tab(I) := 0;
3834 tax_target_tab(I) := 0;
3835 END IF;*/
3836 --Add by Kevin Cheng for inclusive tax Dec 12, 2007
3837 -----------------------------------------------------------------------
3838 IF ( tax_rate_tab( i ) <> 0 OR lt_tax_rate_zero_tab(I) = -9999 ) AND
3839 end_date_tab( I ) <> 0
3840 THEN
3841 IF tax_type_tab( I ) = 1
3842 THEN -- tax inclusive
3843 IF ln_assessable_value_tmp = 1 THEN
3844 v_amt := 1;
3845 ln_bsln_amt_nr :=0;
3846 ELSE
3847 v_amt :=0;
3848 ln_bsln_amt_nr :=ln_assessable_value_tmp;
3849 END IF;
3850 ELSIF tax_type_tab(I)=5 --IN (5, 6) bug 8887871
3851 THEN --IF tax_type_tab(I) = 1 THEN
3852 IF ln_vat_assessable_value_tmp = 1 THEN
3853 -- bsln_amt := 1; commented for bug#9794835
3854 v_amt := 1; --added for bug#9794835
3855 ln_bsln_amt_nr := 0;
3856 ELSE
3857 --bsln_amt := 0; commented for bug#9794835
3858 v_amt := 0; --added for bug#9794835
3859 ln_bsln_amt_nr := ln_vat_assessable_value_tmp;
3860 END IF;
3861 --Add by Xiao for GST, refer to bug#10043656, begin
3862 --------------------------------------------------------------------------------
3863 ELSIF tax_type_tab(I)=7
3864 THEN
3865 IF ln_gst_assessable_value_tmp = 1 THEN
3866 bsln_amt := 1;
3867 ln_bsln_amt_nr := 0;
3868 ELSE
3869 bsln_amt := 0;
3870 ln_bsln_amt_nr := ln_gst_assessable_value_tmp;
3871 END IF;
3872 --------------------------------------------------------------------------------
3873 --Add by Xiao for GST, refer to bug#10043656, end
3874 -- start additions for bug 8887871
3875 ELSIF tax_type_tab(I)=6
3876 THEN --IF tax_type_tab(I) = 1 THEN
3877 IF ln_vat_reversal_value_tmp = 1 THEN
3878 -- bsln_amt := 1; commented for bug#9794835
3879 v_amt := 1; --added for bug#9794835
3880 ln_bsln_amt_nr := 0;
3881 ELSE
3882 --bsln_amt := 0; commented for bug#9794835
3883 v_amt := 0; --added for bug#9794835
3884 ln_bsln_amt_nr := ln_vat_reversal_value_tmp;
3885 END IF;
3886 ELSIF tax_type_tab(I) IN (3, 4) THEN --IF tax_type_tab( I ) = 1 THEN
3887 v_amt := 0;
3888 ln_bsln_amt_nr := 0;
3889 ELSE --IF tax_type_tab( I ) = 1 THEN
3890 v_amt := 1;
3891 ln_bsln_amt_nr := 0;
3892 END IF; --IF tax_type_tab( I ) = 1 THEN
3893
3894 IF tax_rate_tab( i ) NOT IN (0,-1)
3895 AND End_Date_Tab(I) <> 0
3896 AND adhoc_flag_tab(i) = 'N'
3897 --AND NOT ( tax_rate_tab(i) = -1 AND tax_type_tab(i) IN (1,3,4) ) commented out by qiong for Credit Check
3898 THEN /* Added for bug 5091874 *//*Bug 5989740 bduvarag*/
3899 /*v_amt := bsln_amt;*/--Comment out by Kevin Cheng for inclusive tax Jan 15, 2008
3900 /* Commented out by Shyan for bug 10358786 on 28-Jan-2011 , Begin
3901 --------------------------------------------------------------------
3902 IF p1( i ) <> -1 THEN
3903 IF p1( i ) <> 0 THEN
3904 vamt := vamt + tax_amt_tab( p1( I ) );
3905 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P1(I)),0);
3906 ELSIF p1(i) = 0 THEN
3907 vamt := vamt + v_amt;
3908 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
3909 END IF;
3910 END IF;
3911 IF p2( i ) <> -1 THEN
3912 IF p2( i ) <> 0 THEN
3913 vamt := vamt + tax_amt_tab( p2( I ) );
3914 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P2(I)),0);
3915 ELSIF p2(i) = 0 THEN
3916 vamt := vamt + v_amt;
3917 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
3918 END IF;
3919 END IF;
3920 IF p3( i ) <> -1 THEN
3921 IF p3( i ) <> 0 THEN
3922 vamt := vamt + tax_amt_tab( p3( I ) );
3923 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P3(I)),0);
3924 ELSIF p3(i) = 0 THEN
3925 vamt := vamt + v_amt;
3926 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
3927 END IF;
3928 END IF;
3929 IF p4( i ) <> -1 THEN
3930 IF p4( i ) <> 0 THEN
3931 vamt := vamt + tax_amt_tab( p4( i ) );
3932 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P4(I)),0);
3933 ELSIF p4(i) = 0 THEN
3934 vamt := vamt + v_amt;
3935 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
3936 END IF;
3937 END IF;
3938 IF p5( i ) <> -1 THEN
3939 IF p5( i ) <> 0 THEN
3940 vamt := vamt + tax_amt_tab( p5( i ) );
3941 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P5(I)),0);
3942 ELSIF p5(i) = 0 THEN
3943 vamt := vamt + v_amt;
3944 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
3945 END IF;
3946 END IF;
3947
3948 -- Date 01-NOV-2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
3949 -- START BUG 5228046
3950
3951 IF p6( i ) <> -1 THEN
3952 IF p6( i ) <> 0 THEN
3953 vamt := vamt + tax_amt_tab( p6( I ) );
3954 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P6(I)),0);
3955 ELSIF p6(i) = 0 THEN
3956 vamt := vamt + v_amt;
3957 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
3958 END IF;
3959 END IF;
3960 IF p7( i ) <> -1 THEN
3961 IF p7( i ) <> 0 THEN
3962 vamt := vamt + tax_amt_tab( p7( I ) );
3963 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P7(I)),0);
3964 ELSIF p7(i) = 0 THEN
3965 vamt := vamt + v_amt;
3966 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
3967 END IF;
3968 END IF;
3969 IF p8( i ) <> -1 THEN
3970 IF p8( i ) <> 0 THEN
3971 vamt := vamt + tax_amt_tab( p8( I ) );
3972 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P8(I)),0);
3973 ELSIF p8(i) = 0 THEN
3974 vamt := vamt + v_amt;
3975 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
3976 END IF;
3977 END IF;
3978 IF p9( i ) <> -1 THEN
3979 IF p9( i ) <> 0 THEN
3980 vamt := vamt + tax_amt_tab( p9( i ) );
3981 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P9(I)),0);
3982 ELSIF p9(i) = 0 THEN
3983 vamt := vamt + v_amt;
3984 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
3985 END IF;
3986 END IF;
3987 IF p10( i ) <> -1 THEN
3988 IF p10( i ) <> 0 THEN
3989 vamt := vamt + tax_amt_tab( p10( i ) );
3990 ln_vamt_nr:=ln_vamt_nr+NVL(lt_tax_amt_non_rate_tab(P10(I)),0);
3991 ELSIF p10(i) = 0 THEN
3992 vamt := vamt + v_amt;
3993 ln_vamt_nr:=ln_vamt_nr+ln_bsln_amt_nr;
3994 END IF;
3995 END IF;
3996 -- END BUG 5228046
3997 ----------------------------------------------------------------
3998 Commented out by Shyan for bug 10358786 on 28-Jan-2011, End */
3999
4000 -- Added by shujuan for bug 10358786 on 28-Jan-2011, Begin
4001 -----------------------------------------------------------
4002 ln_vamt_nr :=0;
4003 lt_tax_amt_non_rate_tab(I):=0;
4004 --Qiong fix bug12687384 2011.06.29 begin
4005 --for P1 to P10 changed : lt_inclu_tax_tab(P1(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P1(I)) IS NOT NULL
4006 --to ( lt_inclu_tax_tab(P1(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P1(I)) IS NOT NULL ) and Adhoc_Flag_Tab(P1(I))='N'
4007 -------------------------------------------------------------------------------------------------------------------
4008 IF P1(i) <> -1 THEN
4009 IF P1(i) <> 0 THEN
4010 IF ( lt_inclu_tax_tab(P1(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P1(I)) IS NOT NULL )/* and Adhoc_Flag_Tab(P1(I))='N'*/ then
4011 --Modified by Junjian for bug#14730054 on 23-Oct-2012
4012 vamt:= vamt + tax_amt_tab(P1(I));
4013 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P1(I)), 0); --added by walton for inclusive tax
4014 END IF;
4015 ELSIF P1(i) = 0 THEN
4016 IF lt_inclu_tax_tab(I) = 'Y' then
4017 vamt := vamt + bsln_amt;
4018 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr;
4019 ELSE
4020 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + ln_line_amount;
4021 END IF;
4022 END IF;
4023 END IF;
4024
4025 IF p2(i) <> -1 THEN
4026 IF p2(i) <> 0 THEN
4027 IF ( lt_inclu_tax_tab(P2(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P2(I)) IS NOT NULL ) /* and Adhoc_Flag_Tab(P2(I))='N' */then
4028 --Modified by Junjian for bug#14730054 on 23-Oct-2012
4029 vamt := vamt + tax_amt_tab(p2(I));
4030 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P2(I)), 0); --added by walton for inclusive tax
4031 END IF;
4032 ELSIF p2(i) = 0 THEN
4033 IF lt_inclu_tax_tab(I) = 'Y' then
4034 vamt := vamt + v_amt;
4035 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
4036 ELSE
4037 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + ln_line_amount;
4038 END IF;
4039 END IF;
4040 END IF;
4041
4042 IF p3(i) <> -1 THEN
4043 IF p3(i) <> 0 THEN
4044 IF ( lt_inclu_tax_tab(P3(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P3(I)) IS NOT NULL )/* and Adhoc_Flag_Tab(P3(I))='N'*/ then
4045 --Modified by Junjian for bug#14730054 on 23-Oct-2012
4046 vamt := vamt + tax_amt_tab(p3(I));
4047 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P3(I)), 0); --added by walton for inclusive tax
4048 END IF;
4049 ELSIF p3(i) = 0 THEN
4050 IF lt_inclu_tax_tab(I) = 'Y' then
4051 vamt := vamt + v_amt;
4052 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
4053 ELSE
4054 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + ln_line_amount;
4055 END IF;
4056 END IF;
4057 END IF;
4058
4059 IF p4(i) <> -1 THEN
4060 IF p4(i) <> 0 THEN
4061 IF ( lt_inclu_tax_tab(P4(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P4(I)) IS NOT NULL ) /* and Adhoc_Flag_Tab(P4(I))='N' */then
4062 --Modified by Junjian for bug#14730054 on 23-Oct-2012
4063 vamt := vamt + tax_amt_tab(p4(I));
4064 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P4(I)), 0); --added by walton for inclusive tax
4065 END IF;
4066 ELSIF p4(i) = 0 THEN
4067 IF lt_inclu_tax_tab(I) = 'Y' then
4068 vamt := vamt + v_amt;
4069 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
4070 ELSE
4071 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + ln_line_amount;
4072 END IF;
4073 END IF;
4074 END IF;
4075
4076 IF p5(i) <> -1 THEN
4077 IF p5(i) <> 0 THEN
4078 IF ( lt_inclu_tax_tab(P5(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P5(I)) IS NOT NULL )/* and Adhoc_Flag_Tab(P5(I))='N'*/ then
4079 --Modified by Junjian for bug#14730054 on 23-Oct-2012
4080 vamt := vamt + tax_amt_tab(p5(I));
4081 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P5(I)), 0); --added by walton for inclusive tax
4082 END IF;
4083 ELSIF p5(i) = 0 THEN
4084 IF lt_inclu_tax_tab(I) = 'Y' then
4085 vamt := vamt + v_amt;
4086 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
4087 ELSE
4088 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + ln_line_amount;
4089 END IF;
4090 END IF;
4091 END IF;
4092
4093 -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
4094 -- start bug 5228046
4095 IF p6(i) <> -1 THEN
4096 IF p6(i) <> 0 THEN
4097 IF ( lt_inclu_tax_tab(P6(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P6(I)) IS NOT NULL ) /*and Adhoc_Flag_Tab(P6(I))='N'*/ then
4098 --Modified by Junjian for bug#14730054 on 23-Oct-2012
4099 vamt := vamt + tax_amt_tab(p6(I));
4100 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P6(I)), 0); --added by walton for inclusive tax
4101 END IF;
4102 ELSIF p6(i) = 0 THEN
4103 IF lt_inclu_tax_tab(I) = 'Y' then
4104 vamt := vamt + v_amt;
4105 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
4106 ELSE
4107 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + ln_line_amount;
4108 END IF;
4109 END IF;
4110 END IF;
4111 IF p7(i) <> -1 THEN
4112 IF p7(i) <> 0 THEN
4113 IF ( lt_inclu_tax_tab(P7(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P7(I)) IS NOT NULL ) /*and Adhoc_Flag_Tab(P7(I))='N'*/ then
4114 --Modified by Junjian for bug#14730054 on 23-Oct-2012
4115 vamt := vamt + tax_amt_tab(p7(I));
4116 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P7(I)), 0); --added by walton for inclusive tax
4117 END IF;
4118 ELSIF p7(i) = 0 THEN
4119 IF lt_inclu_tax_tab(I) = 'Y' then
4120 vamt := vamt + v_amt;
4121 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
4122 ELSE
4123 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + ln_line_amount;
4124 END IF;
4125 END IF;
4126 END IF;
4127
4128 IF p8(i) <> -1 THEN
4129 IF p8(i) <> 0 THEN
4130 IF ( lt_inclu_tax_tab(P8(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P8(I)) IS NOT NULL )/* and Adhoc_Flag_Tab(P8(I))='N'*/ then
4131 --Modified by Junjian for bug#14730054 on 23-Oct-2012
4132 vamt := vamt + tax_amt_tab(p8(I));
4133 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P8(I)), 0); --added by walton for inclusive tax
4134 END IF;
4135 ELSIF p8(i) = 0 THEN
4136 IF lt_inclu_tax_tab(I) = 'Y' then
4137 vamt := vamt + v_amt;
4138 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
4139 ELSE
4140 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + ln_line_amount;
4141 END IF;
4142 END IF;
4143 END IF;
4144
4145 IF p9(i) <> -1 THEN
4146 IF p9(i) <> 0 THEN
4147 IF ( lt_inclu_tax_tab(P9(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P9(I)) IS NOT NULL )/* and Adhoc_Flag_Tab(P9(I))='N'*/ then
4148 --Modified by Junjian for bug#14730054 on 23-Oct-2012
4149 vamt := vamt + tax_amt_tab(p9(I));
4150 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P9(I)), 0); --added by walton for inclusive tax
4151 END IF;
4152 ELSIF p9(i) = 0 THEN
4153 IF lt_inclu_tax_tab(I) = 'Y' then
4154 vamt := vamt + v_amt;
4155 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
4156 ELSE
4157 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + ln_line_amount;
4158 END IF;
4159 END IF;
4160 END IF;
4161
4162 IF p10(i) <> -1 THEN
4163 IF p10(i) <> 0 THEN
4164 IF ( lt_inclu_tax_tab(P10(I)) = 'Y' OR lt_tax_amt_rate_tax_tab(P10(I)) IS NOT NULL )/* and Adhoc_Flag_Tab(P10(I))='N'*/ then
4165 --Modified by Junjian for bug#14730054 on 23-Oct-2012
4166 vamt := vamt + tax_amt_tab(p10(I));
4167 ln_vamt_nr := ln_vamt_nr + NVL(lt_tax_amt_non_rate_tab(P10(I)), 0); --added by walton for inclusive tax
4168 END IF;
4169 ELSIF p10(i) = 0 THEN
4170 IF lt_inclu_tax_tab(I) = 'Y' then
4171 vamt := vamt + v_amt;
4172 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr; --added by walton for inclusive tax
4173 ELSE
4174 ln_vamt_nr := ln_vamt_nr + ln_bsln_amt_nr + ln_line_amount;
4175 END IF;
4176 END IF;
4177 END IF;
4178 --Qiong fix bug12687384 2011.06.29 end
4179 -------------------------------------------
4180 -- end bug 5228046
4181 -----------------------------------------------------------
4182 -- Added by shujuan for bug 10358786 on 28-Jan-2011, End
4183 lt_base_tax_amt_tab(I) := vamt;
4184 tax_target_tab(I) := vamt;
4185 ln_func_tax_amt := v_tax_amt + ( vamt * ( tax_rate_tab( i )/100));
4186 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
4187 --Comment out by Kevin Cheng for bug#6936009 April 02, 2008
4188 /*IF counter = max_iter THEN
4189 v_tax_amt := ROUND( v_tax_amt + ( vamt * ( tax_rate_tab( i )/100)), rounding_factor_tab(I) );
4190 ELSE*/
4191 v_tax_amt := v_tax_amt + ( vamt * ( tax_rate_tab( i )/100));
4192 /*END IF;*/--Comment out by Kevin Cheng for bug#6936009 April 02, 2008
4193 tax_amt_tab( I ) := NVL( v_tax_amt, 0 );
4194 ELSIF tax_rate_tab( i ) = -1 AND End_Date_Tab(I) <> 0 THEN
4195 --NULL; --Comment out by Kevin Cheng for bug#6915049 Mar 26, 2008
4196 ln_tax_amt_nr:=lt_tax_amt_non_rate_tab(i); --Add by Kevin Cheng for bug#6915049 Mar 26, 2008
4197 ELSE
4198 tax_amt_tab(I) := 0;
4199 tax_target_tab(I) := 0;
4200 END IF;
4201 ELSIF tax_rate_tab(I) = 0 THEN --IF ( tax_rate_tab( i ) <> 0 OR tax_rate_zero_tab(I) = -9999) AND end_date_tab( I ) <> 0 THEN
4202 lt_base_tax_amt_tab(I) := tax_amt_tab(i);
4203 v_tax_amt := tax_amt_tab( i );
4204 ln_tax_amt_nr:=lt_tax_amt_non_rate_tab(i);
4205 tax_target_tab(I) := v_tax_amt;
4206 ELSIF end_date_tab( I ) = 0 THEN --IF ( tax_rate_tab( i ) <> 0 OR tax_rate_zero_tab(I) = -9999) AND end_date_tab( I ) <> 0 THEN
4207 tax_amt_tab(I) := 0;
4208 lt_base_tax_amt_tab(I) := 0;
4209 tax_target_tab(I) := 0;
4210 END IF; --IF ( tax_rate_tab( i ) <> 0 OR tax_rate_zero_tab(I) = -9999) AND end_date_tab( I ) <> 0 THEN
4211
4212 --Add by qiong.liu for bug12687384 begin
4213 ----------------------------------------
4214 IF Adhoc_Flag_Tab(I)='N' THEN
4215 tax_amt_tab( I ) := NVL( v_tax_amt, 0 );
4216 END IF;
4217 --Add by qiong.liu for bug12687384 end
4218 -----------------------------------------
4219 -- Commented out by Shyan for bug 10358786 on 28-Jan-2011 , Begin
4220 -------------------------------------------------------------------
4221 -- lt_tax_amt_rate_tax_tab(i) := tax_amt_tab(I);
4222 -------------------------------------------------------------------
4223 -- Commented out by Shyan for bug 10358786 on 28-Jan-2011 , End
4224
4225 -- Added by Shyan for bug 10358786 on 28-Jan-2011 , Begin
4226 -------------------------------------------------------------------
4227 --Changed by qiong.liu for bug12687384 begin
4228 -----------------------------------------
4229 IF lt_inclu_tax_tab(I) = 'Y' and Adhoc_Flag_Tab(I)='N' THEN
4230 -----------------------------------------
4231 --Changed by qiong.liu for bug12687384 end
4232 lt_tax_amt_rate_tax_tab(I) := tax_amt_tab(I);
4233 END IF;
4234 -------------------------------------------------------------------
4235 -- Added by Shyan for bug 10358786 on 28-Jan-2011 , End
4236
4237 lt_tax_amt_non_rate_tab(I) := ln_tax_amt_nr;
4238 lt_func_tax_amt_tab(I) := NVL(ln_func_tax_amt,0);
4239 base_tax_amount_nr_tab(I):=ln_vamt_nr;---added for bug#9794835
4240 -----------------------------------------------------------------------
4241 IF counter = max_iter THEN
4242 IF END_date_tab(I) = 0 THEN
4243 tax_amt_tab(I) := 0;
4244 lt_func_tax_amt_tab(i) := 0; --Add by Kevin Cheng for inclusive tax Dec 12, 2007
4245 END IF;
4246 END IF;
4247
4248 vamt := 0;
4249 v_amt := 0;
4250 v_tax_amt := 0;
4251 ln_func_tax_amt := 0; -- Add by Kevin Cheng for inclusive tax Dec 10, 2007
4252 ln_vamt_nr :=0; -- Add by Kevin Cheng for inclusive tax Dec 10, 2007
4253 ln_tax_amt_nr:=0;-- Add by Kevin Cheng for inclusive tax Dec 10, 2007
4254 END LOOP;
4255 END LOOP;
4256
4257 --Added by Kevin Cheng for inclusive tax Dec 13, 2007
4258 ---------------------------------------------------------------------------------------
4259 FOR I IN 1 .. ROW_COUNT
4260 LOOP
4261 IF lt_inclu_tax_tab(I) = 'Y' THEN
4262 ln_total_tax_per_rupee := ln_total_tax_per_rupee + nvl(lt_tax_amt_rate_tax_tab(I),0) ;
4263 ln_total_non_rate_tax := ln_total_non_rate_tax + nvl(lt_tax_amt_non_rate_tab(I),0);
4264 END IF;
4265 END LOOP; --FOR I IN 1 .. ROW_COUNT
4266
4267 ln_total_tax_per_rupee := ln_total_tax_per_rupee + 1;
4268
4269 IF ln_total_tax_per_rupee <> 0
4270 THEN
4271 ln_line_amount :=nvl(ln_line_amount,0)*p_line_quantity/v_original_quantity;--10283622
4272 ln_exclusive_price := (NVL(ln_line_amount,0) - ln_total_non_rate_tax ) / ln_total_tax_per_rupee;
4273 END IF;
4274
4275 FOR i in 1 .. row_count
4276 LOOP
4277 -- insert into jai_debug values('base_tax_amount_nr_tab(I) ',base_tax_amount_nr_tab(I));9794835
4278 -- Commented out by Shyan for bug 10358786 on 28-Jan-2011 , Begin
4279 ------------------------------------------------------------------
4280 --tax_amt_tab (i) := (lt_tax_amt_rate_tax_tab(I) * ln_exclusive_price ) + lt_tax_amt_non_rate_tab(I);
4281 ------------------------------------------------------------------
4282 -- Commented out by Shyan for bug 10358786 on 28-Jan-2011 , End
4283
4284 -- Added by Shyan for bug 10358786 on 28-Jan-2011 , Begin
4285 ------------------------------------------------------------------
4286 --Added by qiong.liu for bug12687384 2011.06.29 begin
4287 -----------------------------------------------------
4288 IF Adhoc_Flag_Tab(I)='N' THEN
4289 --Added by qiong.liu for bug12687384 2011.06.29 end
4290 -----------------------------------------------------
4291 tax_amt_tab (i):=0;
4292 IF lt_inclu_tax_tab(I) = 'Y' THEN
4293 IF (tax_type_tab(I) = 1 and ln_assessable_value_tmp>1) OR
4294 (tax_type_tab(I) = 5 and ln_vat_assessable_value_tmp> 1) OR --Changed tax_type_tab to 5 from 4 by mmurtuza for bug 16552731
4295 (tax_type_tab(I) = 7 and ln_gst_assessable_value_tmp >1) THEN
4296
4297 IF tax_type_tab(I) = 1 and ln_assessable_value_tmp >1 Then
4298 ln_amount:= ln_assessable_value_tmp;
4299 ELSIF tax_type_tab(I) = 5 and ln_vat_assessable_value_tmp >1 Then --Changed tax_type_tab to 5 from 4 by mmurtuza for bug 16552731
4300 ln_amount:= ln_vat_assessable_value_tmp;
4301 ELSIF tax_type_tab(I) = 7 and ln_gst_assessable_value_tmp >1 Then
4302 ln_amount:= ln_gst_assessable_value_tmp;
4303 END IF;
4304
4305 IF P1(I) < I AND p1(I) NOT IN (-1, 0) then
4306 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P1(i))* tax_rate_tab(i)/100;
4307 ELSIF P1(I)=0 Then
4308 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
4309 END IF;
4310
4311 IF P2(I) < I AND p2(I) NOT IN (-1, 0) then
4312 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P2(i))* tax_rate_tab(i)/100;
4313 ELSIF P2(I)=0 Then
4314 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
4315 END IF;
4316
4317 IF P3(I) < I AND p3(I) NOT IN (-1, 0) then
4318 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P3(i))* tax_rate_tab(i)/100;
4319 ELSIF P3(I)=0 Then
4320 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
4321 END IF;
4322
4323 IF P4(I) < I AND p4(I) NOT IN (-1, 0) then
4324 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P4(i))* tax_rate_tab(i)/100;
4325 ELSIF P4(I)=0 Then
4326 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
4327 END IF;
4328
4329 IF P5(I) < I AND p5(I) NOT IN (-1, 0) then
4330 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P5(i))* tax_rate_tab(i)/100;
4331 ELSIF P5(I)=0 Then
4332 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
4333 END IF;
4334
4335 IF P6(I) < I AND p6(I) NOT IN (-1, 0) then
4336 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P6(i))* tax_rate_tab(i)/100;
4337 ELSIF P6(I)=0 Then
4338 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
4339 END IF;
4340
4341 IF P7(I) < I AND p7(I) NOT IN (-1, 0) then
4342 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P7(i))* tax_rate_tab(i)/100;
4343 ELSIF P7(I)=0 Then
4344 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
4345 END IF;
4346
4347 IF P8(I) < I AND p8(I) NOT IN (-1, 0) then
4348 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P8(i))* tax_rate_tab(i)/100;
4349 ELSIF P8(I)=0 Then
4350 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
4351 END IF;
4352
4353 IF P9(I) < I AND p9(I) NOT IN (-1, 0) then
4354 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P9(i))* tax_rate_tab(i)/100;
4355 ELSIF P9(I)=0 Then
4356 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
4357 END IF;
4358
4359 IF P10(I) < I AND p10(I) NOT IN (-1, 0) then
4360 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P10(i))* tax_rate_tab(i)/100;
4361 ELSIF P10(I)=0 Then
4362 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
4363 END IF;
4364 ELSE
4365 tax_amt_tab (i) := (lt_tax_amt_rate_tax_tab(I) * ln_exclusive_price ) + lt_tax_amt_non_rate_tab(I);
4366 END IF;
4367 ELSE
4368 IF (tax_type_tab(I) = 1 and ln_assessable_value_tmp >1) OR
4369 (tax_type_tab(I) = 5 and ln_vat_assessable_value_tmp > 1) OR --Changed tax_type_tab to 5 from 4 by mmurtuza for bug 16552731
4370 (tax_type_tab(I) = 7 and ln_gst_assessable_value_tmp >1) THEN
4371
4372 IF tax_type_tab(I) = 1 and ln_assessable_value_tmp >1 Then
4373 ln_amount:= ln_assessable_value_tmp;
4374 ELSIF tax_type_tab(I) = 5 and ln_vat_assessable_value_tmp >1 Then --Changed tax_type_tab to 5 from 4 by mmurtuza for bug 16552731
4375 ln_amount:= ln_vat_assessable_value_tmp;
4376 ELSIF tax_type_tab(I) = 7 and ln_gst_assessable_value_tmp >1 Then
4377 ln_amount:= ln_gst_assessable_value_tmp;
4378 END IF;
4379 ELSE ln_amount:= NVL(ln_line_amount,0);
4380 END IF;
4381 IF P1(I) < I AND p1(I) NOT IN (-1, 0) then
4382 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P1(i))* tax_rate_tab(i)/100;
4383 ELSIF P1(I)=0 Then
4384 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
4385 END IF;
4386
4387 IF P2(I) < I AND p2(I) NOT IN (-1, 0) then
4388 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P2(i))* tax_rate_tab(i)/100;
4389 ELSIF P2(I)=0 Then
4390 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
4391 END IF;
4392
4393 IF P3(I) < I AND p3(I) NOT IN (-1, 0) then
4394 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P3(i))* tax_rate_tab(i)/100;
4395 ELSIF P3(I)=0 Then
4396 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
4397 END IF;
4398
4399 IF P4(I) < I AND p4(I) NOT IN (-1, 0) then
4400 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P4(i))* tax_rate_tab(i)/100;
4401 ELSIF P4(I)=0 Then
4402 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
4403 END IF;
4404
4405 IF P5(I) < I AND p5(I) NOT IN (-1, 0) then
4406 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P5(i))* tax_rate_tab(i)/100;
4407 ELSIF P5(I)=0 Then
4408 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
4409 END IF;
4410
4411 IF P6(I) < I AND p6(I) NOT IN (-1, 0) then
4412 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P6(i))* tax_rate_tab(i)/100;
4413 ELSIF P6(I)=0 Then
4414 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
4415 END IF;
4416
4417 IF P7(I) < I AND p7(I) NOT IN (-1, 0) then
4418 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P7(i))* tax_rate_tab(i)/100;
4419 ELSIF P7(I)=0 Then
4420 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
4421 END IF;
4422
4423 IF P8(I) < I AND p8(I) NOT IN (-1, 0) then
4424 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P8(i))* tax_rate_tab(i)/100;
4425 ELSIF P8(I)=0 Then
4426 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
4427 END IF;
4428
4429 IF P9(I) < I AND p9(I) NOT IN (-1, 0) then
4430 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P9(i))* tax_rate_tab(i)/100;
4431 ELSIF P9(I)=0 Then
4432 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
4433 END IF;
4434
4435 IF P10(I) < I AND p10(I) NOT IN (-1, 0) then
4436 tax_amt_tab(i) := tax_amt_tab(i) + tax_amt_tab(P10(i))* tax_rate_tab(i)/100;
4437 ELSIF P10(I)=0 Then
4438 tax_amt_tab(i) := tax_amt_tab(i) + ln_amount * tax_rate_tab(i)/100;
4439 END IF;
4440 END IF;
4441 ------------------------------------------------------------------
4442 -- Added by Shyan for bug 10358786 on 28-Jan-2011 , End
4443 /*start additions by vkaranam for bug#12819107*
4444 adhoc taxes which are not excise and vat*/
4445 else /*adhoc_flag='y'*/
4446
4447 if tax_type_tab(I) =0
4448 then
4449 tax_amt_tab (i) := tax_target_tab(i);
4450 end if;
4451 /*end additions by vkaranam for bug#12819107*
4452 adhoc taxes which are not excise and vat*/
4453
4454 END IF;--END Adhoc_Flag_Tab(I)='N'
4455 tax_amt_tab(I) := round(tax_amt_tab(I) ,rounding_factor_tab(I));
4456 lt_base_tax_amt_tab(I):= ln_exclusive_price * lt_base_tax_amt_tab(I)
4457 + base_tax_amount_nr_tab(I); --bug#9794853
4458 END LOOP; --FOR i in 1 .. row_count
4459 --------------------------------------------------------------------------------------------------------
4460
4461 FOR i IN 1 .. row_count LOOP
4462 IF p_order_invoice = 'O' THEN
4463 OPEN Fetch_Totals_Cur( i );
4464 FETCH Fetch_Totals_Cur INTO v_cum_amount;
4465 CLOSE Fetch_Totals_Cur;
4466 IF p_line_quantity = 0 THEN
4467 DELETE JAI_CMN_MATCH_TAXES
4468 WHERE Ref_Line_Id = p_ref_line_id
4469 AND nvl(Subinventory,'###') = nvl(p_subinventory,'###')
4470 AND receipt_id = p_receipt_id
4471 AND Tax_Line_No = i;
4472 ELSE
4473 -- insert into jai_debug values('before update base_tax_amount_nr_tab(I) ',base_tax_amount_nr_tab(I)); 9794835
4474 UPDATE JAI_CMN_MATCH_TAXES
4475 SET Tax_Amount = tax_amt_tab(i),
4476 -- Base_Tax_Amount = tax_target_tab(i),
4477 Base_Tax_Amount = lt_base_tax_amt_tab(i) --bug#9794853
4478 ,Func_Tax_Amount = tax_amt_tab(i) * NVL( p_curr_conv_factor, 1 ),
4479 Total_Tax_Amount = v_cum_amount
4480 WHERE Ref_Line_Id = p_ref_line_id
4481 AND nvl(Subinventory,'###') = nvl(p_subinventory,'###')
4482 AND receipt_id = p_receipt_id
4483 AND Tax_Line_No = i;
4484 END IF;
4485 ELSIF p_order_invoice = 'I' THEN
4486 OPEN Fetch_Totals_AR_Cur( i );
4487 FETCH Fetch_Totals_AR_Cur INTO v_cum_amount;
4488 CLOSE Fetch_Totals_AR_Cur;
4489 IF p_line_quantity = 0 THEN
4490 DELETE JAI_CMN_MATCH_TAXES
4491 WHERE Ref_Line_Id = p_ref_line_id
4492 AND nvl(Subinventory,'###') = nvl(p_subinventory,'###')
4493 AND receipt_id = p_receipt_id
4494 AND Tax_Line_No = i;
4495 ELSE
4496 UPDATE JAI_CMN_MATCH_TAXES
4497 SET Tax_Amount = tax_amt_tab(i),
4498 -- Base_Tax_Amount = tax_target_tab(i),
4499 Base_Tax_Amount = lt_base_tax_amt_tab(i) --bug#9794853
4500 ,Func_Tax_Amount = tax_amt_tab(i) * NVL( p_curr_conv_factor, 1 ),
4501 Total_Tax_Amount = v_cum_amount
4502 WHERE Ref_Line_Id = p_ref_line_id
4503 AND nvl(Subinventory,'###') = nvl(p_subinventory,'###')
4504 AND receipt_id = p_receipt_id
4505 AND Tax_Line_No = i;
4506 END IF;
4507
4508 IF p_line_quantity <> 0 THEN
4509 FOR Rec IN Fetch_Total_AR_Cur( i ) LOOP
4510 UPDATE JAI_AR_TRX_TAX_LINES
4511 SET Tax_Amount = rec.tax_amount,
4512 Base_Tax_Amount = rec.base_tax_amount,
4513 Func_Tax_Amount = rec.func_tax_amount
4514 WHERE link_to_cust_trx_line_id = p_ref_line_id
4515 AND Tax_Line_No = i;
4516 END LOOP;
4517 ELSE
4518 UPDATE JAI_AR_TRX_TAX_LINES
4519 SET Tax_Amount = tax_amt_tab(i),
4520 -- Base_Tax_Amount = tax_target_tab(i),
4521 Base_Tax_Amount = lt_base_tax_amt_tab(i) --bug#9794853
4522 ,Func_Tax_Amount = tax_amt_tab(i) * NVL( p_curr_conv_factor, 1 )
4523 WHERE link_to_cust_trx_line_id = p_ref_line_id
4524 AND Tax_Line_No = i;
4525 END IF;
4526
4527 ELSIF p_order_invoice = 'X' THEN -- Interorg bug 6030615
4528
4529 UPDATE Jai_cmn_document_Taxes
4530 SET Tax_Amt = nvl(tax_amt,0)+tax_amt_tab(i), --added nvl(tax_amt,0) for bug#8445390
4531 Func_Tax_Amt = nvl(Func_Tax_Amt,0)+tax_amt_tab(i) * NVL( p_curr_conv_factor, 1 ) --added nvl(Func_Tax_Amt,0) for bug#8445390
4532 WHERE source_doc_line_id = p_ref_line_id
4533 AND source_doc_type = 'INTERORG_XFER'
4534 AND Tax_Line_No = i;
4535
4536 UPDATE Jai_cmn_match_Taxes
4537 SET Tax_Amount = tax_amt_tab(i),
4538 -- Base_Tax_Amount = tax_target_tab(i),
4539 Base_Tax_Amount = lt_base_tax_amt_tab(i) --bug#9794853
4540 ,Func_Tax_Amount = tax_amt_tab(i) * NVL( p_curr_conv_factor, 1 )
4541 WHERE Ref_Line_Id = p_ref_line_id
4542 AND receipt_id = p_receipt_id
4543 AND Tax_Line_No = i;
4544 -- Interorg bug ended 6030615
4545 END IF;
4546 END LOOP;
4547 --Added by Qiong for credit check begin
4548 -------------------------------------------------
4549 IF p_order_invoice = 'O' THEN
4550 jai_credit_check_pkg.process_trading_tax_amount(
4551 pn_so_line_id => p_line_id
4552 , pn_receipt_id => p_receipt_id
4553 , pn_ref_line_id => p_ref_line_id
4554 , pv_subinventory => p_subinventory
4555 );
4556 END IF;
4557 --------------------------------------------------
4558 --Added by Qiong for credit check end
4559
4560 /* Added by Ramananda for bug#4407165 */
4561 EXCEPTION
4562 WHEN OTHERS THEN
4563 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
4564 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
4565 app_exception.raise_exception;
4566
4567 END om_default_taxes;
4568
4569
4570 END jai_cmn_rcv_matching_pkg ;
[Home] [Help]