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