[Home] [Help]
PACKAGE BODY: APPS.JAI_PO_HOOK_PKG
Source
1 PACKAGE BODY JAI_PO_HOOK_PKG AS
2 /* $Header: jai_po_hook_pkg.plb 120.15 2008/01/21 11:27:20 ssumaith noship $ */
3 /*----------------------------------------------------------------------------------------------------------------------
4 CHANGE HISTORY: FILENAME: jai_po_hook_pkg.plb
5 S.No Date Author and Details
6 ------------------------------------------------------------------------------------------------------------------------
7 1 18/04/2007 rchandan for bug#5961325, File Version 115.17
8 Issue: If the ASBN line is in foreign currency and the tax is in INR, the tax calculation
9 is not happening properly.
10 Fix: Modified calc_taxes procedure. Modified the cursor c_asbn_cur to include the currency
11 related fields.
12 Commented the call to ja_in_Calc_tax procedure and called ja_in_po_calc_tax procedure
13 2 16-07-2007 iSupplier forward porting
14 Changed shipment_num to shipment_number
15 excise_inv_num to excise_inv_number in jai_cmn_lines table
16
17 3 20-09-2007 iProcurement 6066485
18 Commented the currency conversion for ln_assessable_value
19
20 4 27-11-2007 Jason Liu
21 Added two functions for iProcurement and iSupplier of Inclusive Tax
22 -------------------------------------------------------------------------------------------------------------------------*/
23
24 PROCEDURE calc_taxes
25 (
26 p_document_type IN VARCHAR2,
27 p_header_id IN NUMBER,
28 p_line_id IN NUMBER,
29 Errbuf OUT NOCOPY VARCHAR2,
30 RetCode OUT NOCOPY VARCHAR2
31 ) IS
32 CURSOR reqn_cur IS
33 SELECT *
34 FROM po_requisition_lines_all
35 WHERE requisition_header_id = p_header_id;
36
37 CURSOR c_uom(pv_unit_of_measure VARCHAR2) IS
38 SELECT uom_code
39 FROM mtl_units_of_measure
40 WHERE unit_of_measure = pv_unit_of_measure;
41
42 CURSOR c_vend_cur(p_sugg_vendor_name IN VARCHAR2) IS
43 SELECT vendor_id
44 FROM po_vendors
45 WHERE vendor_name = p_sugg_vendor_name;
46
47 CURSOR c_vend_site_cur(p_sugg_vendor_loc IN VARCHAR2, p_vendor_id IN NUMBER, p_org_id IN NUMBER) IS
48 SELECT Vendor_Site_Id
49 FROM Po_Vendor_Sites_All A
50 WHERE A.Vendor_Site_Code = p_sugg_vendor_loc
51 AND A.Vendor_Id = p_vendor_id
52 AND NVL(A.Org_Id, 0) = p_org_id;
53
54 CURSOR c_sob(cp_org_id IN NUMBER) IS
55 SELECT set_of_books_id
56 FROM hr_operating_units
57 WHERE organization_id = cp_org_id;
58
59 CURSOR c_hdr_info IS
60 SELECT *
61 FROM Po_Requisition_Headers_V
62 WHERE requisition_header_id = p_header_id;
63
64 CURSOR rcpt_cur IS
65 SELECT *
66 FROM RCV_TRANSACTIONS
67 WHERE shipment_header_id = p_header_id
68 AND shipment_line_id = p_line_id
69 AND transaction_type = 'RECEIVE';
70
71 CURSOR c_rcpt_hdr IS
72 SELECT *
73 FROM RCV_SHIPMENT_HEADERS
74 WHERE shipment_header_id = p_header_id;
75
76 CURSOR c_rcpt_sob(p_inv_orgn_id NUMBER) IS
77 SELECT operating_unit, set_of_books_id
78 FROM org_organization_definitions
79 WHERE organization_id = p_inv_orgn_id;
80
81 CURSOR c_rcpt_line(p_line_id NUMBER) IS
82 SELECT * FROM RCV_SHIPMENT_LINES WHERE shipment_line_id = p_line_id;
83
84 -- pramasub start
85 CURSOR c_asbn_cur(cp_hdr_intf_id IN NUMBER, cp_cmn_line_id IN NUMBER) IS
86 SELECT lines.cmn_line_id,
87 lines.header_interface_id,
88 lines.interface_transaction_id transaction_id,
89 lines.po_unit_price,
90 lines.quantity,
91 lines.item_id,
92 rtxns.unit_of_measure,
93 rtxns.vendor_id,
94 rtxns.vendor_site_id,
95 rtxns.creation_date,
96 rtxns.po_header_id, /*rchandan for 5961325*/
97 poll.price_override,
98 --lines.CURRENCY_CODE, /*rchandan for 5961325*/
99 -- rtxns.CURRENCY_CONVERSION_TYPE, /*rchandan for 5961325*/
100 -- rtxns.CURRENCY_CONVERSION_RATE, /*rchandan for 5961325*/
101 -- rtxns.CURRENCY_CONVERSION_DATE, /*rchandan for 5961325*/
102 poll.org_id /*rchandan for 5961325*/
103 FROM JAI_CMN_LINES lines,
104 RCV_TRANSACTIONS_INTERFACE rtxns,
105 po_line_locations_all poll
106 WHERE lines.interface_transaction_id =
107 rtxns.interface_transaction_id
108 AND lines.HEADER_INTERFACE_ID = rtxns.HEADER_INTERFACE_ID
109 AND lines.po_line_location_id = poll.line_location_id
110 AND lines.HEADER_INTERFACE_ID = cp_hdr_intf_id
111 AND lines.CMN_LINE_ID = cp_cmn_line_id;
112
113 -- pramasub end
114
115 r_rcpt_line c_rcpt_line%ROWTYPE;
116 r_rcpt_hdr c_rcpt_hdr%ROWTYPE;
117 r_doc_hdr c_hdr_info%ROWTYPE;
118 r_rcpt_sob c_rcpt_sob%ROWTYPE;
119 ln_header_id NUMBER;
120 ln_line_id NUMBER;
121 ln_line_amount NUMBER;
122 lv_uom_code VARCHAR2(20);
123 ln_assessable_value NUMBER;
124 ln_vat_assess_value NUMBER;
125 ln_inventory_item_id NUMBER;
126 ln_conv_rate NUMBER;
127 ln_vendor_id NUMBER;
128 ln_Vendor_site_id NUMBER;
129 ln_tax_amount NUMBER;
130 lv_line_currency VARCHAR2(15);
131 lv_rate_type VARCHAR2(30);
132 ln_gl_set_of_bks_id NUMBER;
133 ld_rate_date DATE;
134 lv_hdr_curr VARCHAR2(15);
135 ln_orig_line_amt NUMBER;
136 lv_document_type VARCHAR2(100);
137 ln_transaction_id NUMBER;
138 ln_unit_price NUMBER;
139
140 /*rchandan for 5961325..start*/
141
142 CURSOR c_func_curr(cp_sob_id IN NUMBER) IS
143 SELECT currency_code
144 FROM gl_sets_of_books
145 WHERE set_of_books_id = cp_sob_id;
146
147 CURSOR cur_asbn_hdr(cp_header_id NUMBER) IS
148 SELECT *
149 FROM po_headers_all
150 WHERE po_header_id = cp_header_id;
151
152 asbn_hdr_rec cur_asbn_hdr%ROWTYPE;
153
154
155 ln_set_of_books_id NUMBER;
156 lv_func_curr VARCHAR2(15);
157
158 /*rchandan for 5961325..end*/
159
160 BEGIN
161
162 lv_document_type := p_document_type;
163
164 IF lv_document_type = 'REQUISITION' THEN
165
166 /*
167 || Loop through each of the lines of the header.
168 */
169
170 OPEN c_hdr_info;
171 FETCH c_hdr_info
172 INTO r_doc_hdr;
173 CLOSE c_hdr_info;
174
175 lv_hdr_curr := r_doc_hdr.currency_code;
176
177 FOR r_reqn_cur IN reqn_cur
178 LOOP
179
180 ln_header_id := r_reqn_cur.requisition_header_id;
181 ln_line_id := r_reqn_cur.requisition_line_id;
182 ln_line_amount := NVL(r_reqn_cur.currency_unit_price,
183 r_reqn_cur.unit_price) *
184 r_reqn_cur.quantity;
185 ln_orig_line_amt := NVL(r_reqn_cur.currency_unit_price,
186 r_reqn_cur.unit_price) *
187 r_reqn_cur.quantity;
188 lv_uom_code := r_reqn_cur.unit_meas_lookup_code;
189 ln_vendor_id := r_reqn_cur.vendor_id;
190 ln_vendor_site_id := r_reqn_cur.vendor_site_id;
191
192 OPEN c_uom(r_reqn_cur.unit_meas_lookup_code);
193 FETCH c_uom
194 INTO lv_uom_code;
195 CLOSE c_uom;
196
197 /*
198 || If the vendor Id and Vendor Site id populated in the table are null,
199 || derive the values based on the suggested_Vendor_name and
200 || suggested_vendor_location values.
201 */
202
203 IF ln_vendor_id IS NULL THEN
204 OPEN c_vend_cur(r_reqn_cur.suggested_vendor_name);
205 FETCH c_vend_Cur
206 INTO ln_Vendor_id;
207 CLOSE c_vend_cur;
208 END IF;
209
210 IF ln_vendor_site_id IS NULL THEN
211 OPEN c_vend_site_cur(r_reqn_cur.suggested_vendor_location,
212 ln_Vendor_id,
213 r_reqn_cur.org_id);
214 FETCH c_vend_site_cur
215 INTO ln_Vendor_site_id;
216 CLOSE c_vend_site_cur;
217 END IF;
218
219 lv_line_currency := r_reqn_cur.currency_code;
220 lv_rate_type := r_reqn_cur.rate_type;
221 ld_rate_date := r_reqn_cur.rate_date;
222 ln_conv_rate := r_reqn_cur.rate;
223
224 OPEN c_sob(r_reqn_cur.org_id);
225 FETCH c_sob
226 INTO ln_gl_set_of_bks_id;
227 CLOSE c_sob;
228
229 lv_line_currency := NVL(lv_line_currency, lv_hdr_curr);
230
231 IF NVL(lv_line_currency, '$') = NVL(lv_hdr_curr, '$') THEN
232 ln_conv_rate := 1;
233 ELSE
234 IF lv_rate_type = 'User' THEN
235 ln_conv_rate := ln_conv_rate;
236 ELSE
237 ln_conv_rate := jai_cmn_utils_pkg.currency_conversion(ln_gl_set_of_bks_id,
238 lv_line_currency,
239 ld_rate_date,
240 lv_rate_type,
241 ln_conv_rate);
242 END IF;
243 END IF;
244
245 /*
246 || For each of the line, calculate the Vat assessable value and excise assessable value.
247 */
248
249 ln_vat_assess_value := jai_general_pkg.ja_in_vat_assessable_value(P_PARTY_ID => ln_vendor_id,
250 P_PARTY_SITE_ID => ln_vendor_site_id,
251 P_INVENTORY_ITEM_ID => r_reqn_cur.item_id,
252 P_UOM_CODE => lv_uom_code,
253 P_DEFAULT_PRICE => ln_line_amount,
254 P_ASS_VALUE_DATE => r_reqn_cur.creation_date,
255 P_PARTY_TYPE => 'V');
256
257 ln_assessable_value := jai_cmn_setup_pkg.get_po_assessable_value(P_VENDOR_ID => ln_vendor_id,
258 P_VENDOR_SITE_ID => ln_vendor_site_id,
259 P_INV_ITEM_ID => r_reqn_cur.item_id,
260 P_LINE_UOM => lv_uom_code);
261
262 /*
263 commented the next 2 lines - Internal QA issue
264 */
265 /*ln_assessable_value := NVL(ln_assessable_value, ln_line_amount) *
266 ln_conv_rate;
267 ln_vat_assess_value := ln_vat_assess_value * ln_conv_rate;*/
268
269 /*
270 Call the routine that calculates the tax.
271 */
272 /*
273 ja_in_cal_tax
274 (
275 lv_document_type , -- IN P_TYPE
276 ln_header_id , -- IN P_HEADER_ID
277 ln_line_id , -- IN P_LINE_ID
278 -999 , -- IN P_LINE_LOC_ID
279 r_reqn_cur.quantity , -- IN P_LINE_QUANTITY
280 ln_line_amount , -- IN P_PRICE
281 lv_uom_code , -- IN P_LINE_UOM_CODE
282 ln_line_amount , -- IN OUT P_TAX_AMOUNT
283 ln_assessable_value , -- IN P_ASSESSABLE_VALUE
284 ln_vat_assess_value , -- IN P_VAT_ASSESS_VALUE
285 r_reqn_cur.item_id , -- IN P_ITEM_ID
286 ln_conv_rate -- IN P_CONV_RATE
287 );
288 */
289
290 jai_po_tax_pkg.calc_tax(p_type => lv_document_type,
291 p_header_id => ln_header_id,
292 P_line_id => ln_line_id,
293 p_line_location_id => NULL,
294 p_line_focus_id => NULL,
295 p_line_quantity => r_reqn_cur.quantity,
296 p_base_value => ln_line_amount,
297 p_line_uom_code => lv_uom_code,
298 p_tax_amount => ln_tax_amount,
299 p_assessable_value => ln_assessable_value,
300 p_vat_assess_value => ln_vat_assess_value,
301 p_item_id => r_reqn_cur.item_id,
302 p_conv_rate => ln_conv_rate,
303 p_po_curr => lv_line_currency,
304 p_func_curr => lv_hdr_curr);
305
306 ln_tax_amount := ln_line_amount;
307
308 UPDATE JAI_PO_REQ_LINES
309 SET Last_Update_Date = SYSDATE,
310 tax_amount = ln_tax_amount,
311 total_amount = ln_tax_amount + ln_orig_line_amt,
312 Last_Updated_By = r_reqn_cur.last_updated_by,
313 Last_Update_Login = r_reqn_cur.last_update_login
314 WHERE Requisition_Line_Id = ln_line_id
315 AND Requisition_Header_Id = ln_header_id;
316
317 END LOOP;
318
319 ELSIF lv_document_type = 'RECEIPTS' THEN
320
321 OPEN c_rcpt_hdr;
322 FETCH c_rcpt_hdr
323 INTO r_rcpt_hdr;
324 CLOSE c_rcpt_hdr;
325
326 lv_hdr_curr := r_rcpt_hdr.currency_code;
327
328 FOR r_rcpt_cur IN rcpt_cur
329 LOOP
330
331 OPEN c_rcpt_line(r_rcpt_cur.shipment_line_id);
332 FETCH c_rcpt_line
333 INTO r_rcpt_line;
334 CLOSE c_rcpt_line;
335
336 ln_header_id := r_rcpt_cur.shipment_header_id;
337 ln_line_id := r_rcpt_cur.shipment_line_id;
338 ln_transaction_id := r_rcpt_cur.transaction_id;
339 ln_line_amount := r_rcpt_cur.po_unit_price * r_rcpt_cur.quantity;
340 ln_orig_line_amt := r_rcpt_cur.po_unit_price * r_rcpt_cur.quantity;
341 lv_uom_code := r_rcpt_cur.unit_of_measure;
342 ln_vendor_id := r_rcpt_cur.vendor_id;
343 ln_vendor_site_id := r_rcpt_cur.vendor_site_id;
344
345 lv_line_currency := r_rcpt_cur.currency_code;
346 lv_rate_type := r_rcpt_cur.currency_conversion_type;
347 ld_rate_date := r_rcpt_cur.currency_conversion_date;
348 ln_conv_rate := r_rcpt_cur.currency_conversion_rate;
349
350 OPEN c_rcpt_sob(r_rcpt_cur.organization_id);
351 FETCH c_rcpt_sob
352 INTO r_rcpt_sob;
353 CLOSE c_rcpt_sob;
354
355 OPEN c_uom( r_rcpt_cur.unit_of_measure);
356 FETCH c_uom
357 INTO lv_uom_code;
358 CLOSE c_uom;
359
360 lv_line_currency := NVL(lv_line_currency, lv_hdr_curr);
361
362 IF NVL(lv_line_currency, '$') = NVL(lv_hdr_curr, '$') THEN
363 ln_conv_rate := 1;
364 ELSE
365 IF lv_rate_type = 'User' THEN
366 ln_conv_rate := 1 / ln_conv_rate;
367 ELSE
368 ln_conv_rate := 1 / jai_cmn_utils_pkg.currency_conversion(r_rcpt_sob.set_of_books_id,
369 lv_line_currency,
370 ld_rate_date,
371 lv_rate_type,
372 ln_conv_rate);
373 END IF;
374 END IF;
375
376 /*
377 || For each of the line, calculate the Vat assessable value and excise assessable value.
378 */
379
380 ln_vat_assess_value := jai_general_pkg.ja_in_vat_assessable_value(P_PARTY_ID => ln_vendor_id,
381 P_PARTY_SITE_ID => ln_vendor_site_id,
382 P_INVENTORY_ITEM_ID => r_rcpt_line.item_id,
383 P_UOM_CODE => lv_uom_code,
384 P_DEFAULT_PRICE => ln_line_amount,
385 P_ASS_VALUE_DATE => r_rcpt_cur.creation_date,
386 P_PARTY_TYPE => 'V');
387
388 ln_assessable_value := jai_cmn_setup_pkg.get_po_assessable_value(P_VENDOR_ID => ln_vendor_id,
389 P_VENDOR_SITE_ID => ln_vendor_site_id,
390 P_INV_ITEM_ID => r_rcpt_line.item_id,
391 P_LINE_UOM => lv_uom_code);
392
393 /* This conversion is not required
394 ln_assessable_value := NVL(ln_assessable_value, ln_line_amount) *
395 ln_conv_rate;
396 ln_vat_assess_value := ln_vat_assess_value * ln_conv_rate;
397 */
398 /*
399 Call the routine that calculates the tax.
400 */
401
402 jai_po_tax_pkg.calculate_tax(lv_document_type, -- IN P_TYPE
403 ln_header_id, -- IN P_HEADER_ID
404 ln_line_id, -- IN P_LINE_ID
405 -999, -- IN P_LINE_LOC_ID
406 r_rcpt_cur.quantity, -- IN P_LINE_QUANTITY
407 ln_line_amount, -- IN P_PRICE
408 lv_uom_code, -- IN P_LINE_UOM_CODE
409 ln_line_amount, -- IN OUT P_TAX_AMOUNT
410 ln_assessable_value, -- IN P_ASSESSABLE_VALUE
411 ln_vat_assess_value, -- IN P_VAT_ASSESS_VALUE
412 r_rcpt_line.item_id, -- IN P_ITEM_ID
413 ln_conv_rate -- IN P_CONV_RATE
414 );
415
416 ln_tax_amount := ln_line_amount;
417
418 UPDATE JAI_RCV_LINES
419 SET Last_Update_Date = SYSDATE,
420 tax_amount = ln_tax_amount,
421 Last_Updated_By = r_rcpt_line.last_updated_by,
422 Last_Update_Login = r_rcpt_line.last_update_login
423 WHERE shipment_Line_Id = ln_line_id
424 AND shipment_Header_Id = ln_header_id;
425
426 END LOOP;
427 -- pramasub start
428 ELSIF lv_document_type = 'ASBN' THEN
429
430 /*OPEN c_rcpt_hdr;
431 FETCH c_rcpt_hdr INTO r_rcpt_hdr;
432 CLOSE c_rcpt_hdr;
433
434 lv_hdr_curr := r_rcpt_hdr.currency_code;*/
435
436 FOR r_asbn_cur IN c_asbn_cur(p_header_id, p_line_id)
437 LOOP
438
439 IF nvl(r_asbn_cur.po_unit_price, 0) = 0 THEN
440 ln_unit_price := r_asbn_cur.price_override;
441 ELSE
442 ln_unit_price := r_asbn_cur.po_unit_price;
443 END IF;
444
445 OPEN c_uom(r_asbn_cur.unit_of_measure);
446 FETCH c_uom
447 INTO lv_uom_code;
448 CLOSE c_uom;
449
450 ln_header_id := r_asbn_cur.header_interface_id;
451 ln_line_id := r_asbn_cur.cmn_line_id;
452 ln_transaction_id := r_asbn_cur.transaction_id;
453 ln_line_amount := ln_unit_price * r_asbn_cur.quantity;
454 ln_orig_line_amt := ln_unit_price * r_asbn_cur.quantity;
455 --lv_uom_code := r_asbn_cur.unit_of_measure;
456 ln_vendor_id := r_asbn_cur.vendor_id;
457 ln_vendor_site_id := r_asbn_cur.vendor_site_id;
458
459 /*rchandan for 5961325...start*/
460
461 OPEN cur_asbn_hdr(r_asbn_cur.po_header_id);
462 FETCH cur_asbn_hdr INTO asbn_hdr_rec;
463 CLOSE cur_asbn_hdr;
464
465 lv_line_currency := asbn_hdr_rec.currency_code;
466 lv_rate_type := 'Corporate';
467 ld_rate_date := trunc(sysdate);
468 ln_conv_rate := asbn_hdr_rec.rate;
469
470 OPEN c_sob(r_asbn_cur.org_id);
471 FETCH c_sob INTO ln_set_of_books_id;
472 CLOSE c_sob;
473
474 OPEN c_func_curr(ln_set_of_books_id);
475 FETCH c_func_curr INTO lv_func_curr;
476 CLOSE c_func_curr;
477
478 IF NVL(lv_line_currency,'$') = NVL(lv_func_curr,'$') THEN
479 ln_conv_rate := 1;
480 ELSE
481 IF lv_rate_type = 'User' THEN
482 ln_conv_rate := 1/ln_conv_rate;
483 ELSE
484 ln_conv_rate := 1/jai_cmn_utils_pkg.currency_conversion( ln_set_of_books_id, lv_line_currency, ld_rate_date, lv_rate_type, ln_conv_rate );
485 END IF;
486 END IF;
487
488 /*rchandan for 5961325...end*/
489
490 /*lv_line_currency := r_rcpt_cur.currency_code ;
491 lv_rate_type := r_rcpt_cur.currency_conversion_type;
492 ld_rate_date := r_rcpt_cur.currency_conversion_date;
493 ln_conv_rate := r_rcpt_cur.currency_conversion_rate;
494
495 OPEN c_rcpt_sob (r_rcpt_cur.organization_id);
496 FETCH c_rcpt_sob into r_rcpt_sob;
497 CLOSE c_rcpt_sob;
498
499 lv_line_currency := NVL(lv_line_currency,lv_hdr_curr);
500
501 IF NVL(lv_line_currency,'$') = NVL(lv_hdr_curr,'$') THEN
502 ln_conv_rate := 1;
503 ELSE
504 IF lv_rate_type = 'User' THEN
505 ln_conv_rate := 1/ln_conv_rate;
506 ELSE
507 ln_conv_rate := 1/Ja_Curr_Conv( r_rcpt_sob.set_of_books_id, lv_line_currency, ld_rate_date, lv_rate_type, ln_conv_rate );
508 END IF;
509 END IF;*/
510
511 /*
512 || For each of the line, calculate the Vat assessable value and excise assessable value.
513 */
514
515 ln_vat_assess_value := jai_general_pkg.ja_in_vat_assessable_value(P_PARTY_ID => ln_vendor_id,
516 P_PARTY_SITE_ID => ln_vendor_site_id,
517 P_INVENTORY_ITEM_ID => r_asbn_cur.item_id,
518 P_UOM_CODE => lv_uom_code,
519 P_DEFAULT_PRICE => ln_line_amount,
520 P_ASS_VALUE_DATE => r_asbn_cur.creation_date,
521 P_PARTY_TYPE => 'V');
522
523 ln_assessable_value := jai_cmn_setup_pkg.get_po_assessable_value(P_VENDOR_ID => ln_vendor_id,
524 P_VENDOR_SITE_ID => ln_vendor_site_id,
525 P_INV_ITEM_ID => r_asbn_cur.item_id,
526 P_LINE_UOM => lv_uom_code);
527
528 --ln_assessable_value := NVL(ln_assessable_value ,ln_line_amount) * ln_conv_rate;
529 --ln_vat_assess_value := ln_vat_assess_value * ln_conv_rate;
530 ln_assessable_value := NVL(ln_assessable_value, ln_line_amount) * 1;
531 ln_vat_assess_value := ln_vat_assess_value * 1;
532 /*
533 Call the routine that calculates the tax.
534 */
535 /*
536 ja_in_cal_tax(lv_document_type, -- IN P_TYPE
537 ln_header_id, -- IN P_HEADER_ID
538 ln_line_id, -- IN P_LINE_ID
539 -999, -- IN P_LINE_LOC_ID
540 r_asbn_cur.quantity, -- IN P_LINE_QUANTITY
541 ln_line_amount, -- IN P_PRICE
542 lv_uom_code, -- IN P_LINE_UOM_CODE
543 ln_line_amount, -- IN OUT P_TAX_AMOUNT
544 ln_assessable_value, -- IN P_ASSESSABLE_VALUE
545 ln_vat_assess_value, -- IN P_VAT_ASSESS_VALUE
546 r_asbn_cur.item_id, -- IN P_ITEM_ID
547 --ln_conv_rate -- IN P_CONV_RATE
548 1);*//*commented by rchandan for 5961325 and added call to ja_in_po_calc_tax*/
549
550 jai_po_tax_pkg.calc_tax(p_type => lv_document_type,
551 p_header_id => ln_header_id,
552 P_line_id => ln_line_id,
553 p_line_location_id => NULL,
554 p_line_focus_id => NULL,
555 p_line_quantity => r_asbn_cur.quantity,
556 p_base_value => ln_line_amount,
557 p_line_uom_code => lv_uom_code,
558 p_tax_amount => ln_tax_amount,
559 p_assessable_value => ln_assessable_value,
560 p_vat_assess_value => ln_vat_assess_value,
561 p_item_id => r_asbn_cur.item_id,
562 p_conv_rate => ln_conv_rate,
563 p_po_curr => lv_line_currency,
564 p_func_curr => NULL);
565
566 --ln_tax_amount := ln_line_amount;
567
568 /*UPDATE JAI_RCV_LINES
569 SET Last_Update_Date = sysdate,
570 tax_amount = ln_tax_amount ,
571 Last_Updated_By = r_rcpt_line.last_updated_by,
572 Last_Update_Login = r_rcpt_line.last_update_login
573 WHERE shipment_Line_Id = ln_line_id
574 AND shipment_Header_Id = ln_header_id;*/
575
576 END LOOP;
577 -- pramasub end
578 END IF;
579 errbuf := NULL;
580 RetCode := '0';
581
582 EXCEPTION
583 WHEN OTHERS THEN
584 Errbuf := SQLERRM;
585 RetCode := '2';
586 END calc_taxes;
587
588 PROCEDURE update_rcv_trxs(P_transaction_id IN NUMBER) IS
589 BEGIN
590 UPDATE rcv_transactions
591 SET attribute4 = 'Y', attribute_category = 'India Return to Vendor'
592 WHERE transaction_id = P_transaction_id;
593
594 END update_rcv_trxs;
595
596 PROCEDURE update_cmn_lines
597 (
598 p_shipment_num IN VARCHAR2,
599 p_ex_inv_num IN VARCHAR2,
600 p_ex_inv_date IN date,
601 errbuf OUT NOCOPY VARCHAR2,
602 retcode OUT NOCOPY VARCHAR2
603 ) IS
604 BEGIN
605 UPDATE jai_cmn_lines
606 SET excise_inv_number = p_ex_inv_num, excise_inv_date = p_ex_inv_date
607 WHERE shipment_number = p_shipment_num;
608
609 errbuf := NULL;
610 retcode := '0';
611
612 EXCEPTION
613 WHEN OTHERS THEN
614 errbuf := SQLERRM;
615 retcode := '2';
616 END update_cmn_lines;
617
618 PROCEDURE update_jrcv_flags
619 (
620 P_transaction_id IN NUMBER,
621 p_process_Action VARCHAR2
622 ) IS
623
624 lv_process_flag VARCHAR2(1);
625 BEGIN
626
627 IF p_process_Action = 'Y' THEN
628 lv_process_flag := 'N';
629 ELSIF p_process_Action = 'N' THEN
630 lv_process_flag := 'P';
631 END IF;
632
633 UPDATE JAI_RCV_TRANSACTIONS
634 SET process_status = lv_process_flag,
635 cenvat_rg_status = lv_process_flag,
636 cenvat_rg_message = NULL,
637 process_vat_status = lv_process_flag
638 WHERE transaction_id = P_transaction_id;
639 END update_jrcv_flags;
640
641 PROCEDURE PROCESS_RECEIPT
642 (
643 p_shipment_header_id IN NUMBER,
644 p_transaction_id IN NUMBER DEFAULT NULL,
645 p_process_Action IN VARCHAR2 DEFAULT NULL,
646 errbuf OUT NOCOPY VARCHAR2,
647 retcode OUT NOCOPY VARCHAR2
648 ) IS
649
650 lv_errbuf VARCHAR2(4000);
651 lv_retCode VARCHAR2(100);
652 ln_organization_id NUMBER;
653
654 CURSOR c_rcpt_cur IS
655 SELECT *
656 FROM JAI_RCV_LINES
657 WHERE shipment_header_id = p_shipment_header_id;
658
659 r_rcpt_rec c_rcpt_cur%ROWTYPE;
660 ln_batch_id NUMBER;
661
662 lv_called_from VARCHAR2(30);
663
664 BEGIN
665
666 OPEN c_rcpt_cur;
667 FETCH c_rcpt_cur
668 INTO r_rcpt_rec;
669 CLOSE c_rcpt_cur;
670
671 lv_called_from := 'JAINPORE';
672
673 IF p_process_Action = 'Y'
674 AND p_transaction_id IS NOT NULL THEN
675 lv_called_from := 'RECEIPT_TAX_INSERT_TRG';
676 update_rcv_trxs(p_transaction_id);
677 update_jrcv_flags(p_transaction_id, p_process_Action);
678 ELSIF p_process_Action = 'N'
679 AND p_transaction_id IS NOT NULL THEN
680 update_jrcv_flags(p_transaction_id, p_process_Action);
681 lv_called_from := 'RECEIPT_TAX_INSERT_TRG';
682 END IF;
683
684 jai_rcv_trx_processing_pkg.process_batch(ERRBUF => lv_errbuf,
685 RETCODE => lv_retCode,
686 P_ORGANIZATION_ID => r_rcpt_rec.organization_id,
687 PV_TRANSACTION_FROM => NULL,
688 PV_TRANSACTION_TO => NULL,
689 P_TRANSACTION_TYPE => NULL,
690 P_PARENT_TRX_TYPE => NULL,
691 P_SHIPMENT_HEADER_ID => p_shipment_header_id,
692 P_RECEIPT_NUM => NULL,
693 P_SHIPMENT_LINE_ID => NULL,
694 P_TRANSACTION_ID => p_transaction_id,
695 P_COMMIT_SWITCH => 'Y',
696 P_CALLED_FROM => lv_called_from,
697 P_SIMULATE_FLAG => 'N',
698 P_TRACE_SWITCH => NULL,
699 P_REQUEST_ID => NULL,
700 P_GROUP_ID => NULL);
701
702 IF lv_errbuf IS NOT NULL THEN
703 /* Error Reported */
704 errbuf := lv_errbuf;
705 retCode := lv_retCode;
706 ELSE
707 errbuf := NULL;
708 retCode := '0';
709 END IF;
710
711 EXCEPTION
712 WHEN OTHERS THEN
713 errbuf := SQLERRM;
714 retCode := '2';
715
716 END PROCESS_RECEIPT;
717
718 PROCEDURE populate_cmn_taxes
719 (
720 p_po_header_id IN NUMBER,
721 p_line_location_id IN NUMBER,
722 p_hdr_intf_id IN NUMBER,
723 p_cmn_line_id IN NUMBER,
724 p_quantity IN NUMBER,
725 p_tot_quantity IN NUMBER
726 ) IS
727
728 CURSOR c_tax_codes(cp_tax_id IN NUMBER) IS
729 SELECT * FROM JAI_CMN_TAXES_ALL WHERE tax_id = cp_tax_id;
730
731 CURSOR c_conv_rate_rfq(cp_header_id IN NUMBER) IS
732 SELECT rate, currency_code
733 FROM po_headers_all
734 WHERE po_header_id = cp_header_id;
735
736 r_taxes c_tax_codes%ROWTYPE;
737 ln_conv_rate NUMBER;
738 lv_line_currency VARCHAR2(20);
739 ln_func_Tax_amt NUMBER;
740 ln_rnd_factor NUMBER;
741 ln_tax_Amount NUMBER;
742
743 BEGIN
744 ln_rnd_factor := 0;
745 ln_tax_Amount := -1;
746 OPEN c_conv_rate_rfq(p_po_header_id);
747 FETCH c_conv_rate_rfq
748 INTO ln_conv_rate, lv_line_currency;
749 CLOSE c_conv_rate_rfq;
750
751 IF ln_conv_rate IS NULL THEN
752 ln_conv_rate := 1;
753 END IF;
754
755 FOR j_po_ll_rec IN (SELECT *
756 FROM JAI_PO_TAXES
757 WHERE line_location_id = p_line_location_id)
758 LOOP
759 OPEN c_tax_codes(j_po_ll_rec.TAX_ID);
760 FETCH c_tax_codes
761 INTO r_taxes;
762 CLOSE c_tax_codes;
763
764 IF r_taxes.rounding_factor IS NOT NULL THEN
765 ln_rnd_factor := r_taxes.rounding_factor;
766 END IF;
767 IF p_tot_quantity > 0 THEN
768 ln_tax_Amount := round((p_quantity / p_tot_quantity *
769 j_po_ll_rec.TAX_AMOUNT),
770 ln_rnd_factor);
771 END IF;
772
773 IF NVL(lv_line_currency, '$$$') <> NVL(j_po_ll_rec.CURRENCY, '$$$') THEN
774 ln_func_Tax_amt := j_po_ll_rec.TAX_AMOUNT * ln_conv_rate;
775 END IF;
776
777 INSERT INTO JAI_CMN_DOCUMENT_TAXES
778 (DOC_TAX_ID,
779 TAX_LINE_NO,
780 TAX_ID,
781 TAX_TYPE,
782 CURRENCY_CODE,
783 TAX_RATE,
784 QTY_RATE,
785 UOM,
786 TAX_AMT,
787 FUNC_TAX_AMT,
788 MODVAT_FLAG,
789 TAX_CATEGORY_ID,
790 SOURCE_DOC_TYPE,
791 SOURCE_DOC_ID,
792 SOURCE_DOC_LINE_ID,
793 SOURCE_TABLE_NAME,
794 ADHOC_FLAG,
795 PRECEDENCE_1,
796 PRECEDENCE_2,
797 PRECEDENCE_3,
798 PRECEDENCE_4,
799 PRECEDENCE_5,
800 PRECEDENCE_6,
801 PRECEDENCE_7,
802 PRECEDENCE_8,
803 PRECEDENCE_9,
804 PRECEDENCE_10,
805 CREATION_DATE,
806 CREATED_BY,
807 LAST_UPDATE_DATE,
808 LAST_UPDATED_BY,
809 LAST_UPDATE_LOGIN,
810 VENDOR_ID)
811 VALUES
812 (JAI_CMN_DOCUMENT_TAXES_s.NEXTVAL,
813 j_po_ll_rec.TAX_LINE_NO,
814 j_po_ll_rec.TAX_ID,
815 j_po_ll_rec.TAX_TYPE,
816 j_po_ll_rec.CURRENCY,
817 j_po_ll_rec.TAX_RATE,
818 j_po_ll_rec.QTY_RATE,
819 j_po_ll_rec.UOM,
820 ln_tax_Amount,
821 ln_func_Tax_amt,
822 j_po_ll_rec.MODVAT_FLAG,
823 j_po_ll_rec.TAX_CATEGORY_ID,
824 'ASBN',
825 p_hdr_intf_id,
826 p_cmn_line_id,
827 'JAI_PO_TAXES',
828 r_taxes.adhoc_flag,
829 j_po_ll_rec.PRECEDENCE_1,
830 j_po_ll_rec.PRECEDENCE_2,
831 j_po_ll_rec.PRECEDENCE_3,
832 j_po_ll_rec.PRECEDENCE_4,
833 j_po_ll_rec.PRECEDENCE_5,
834 j_po_ll_rec.PRECEDENCE_6,
835 j_po_ll_rec.PRECEDENCE_7,
836 j_po_ll_rec.PRECEDENCE_8,
837 j_po_ll_rec.PRECEDENCE_9,
838 j_po_ll_rec.PRECEDENCE_10,
839 SYSDATE,
840 fnd_global.user_id,
841 SYSDATE,
842 fnd_global.user_id,
843 fnd_global.login_id,
844 j_po_ll_rec.VENDOR_ID);
845 END LOOP;
846
847 /* errbuf := NULL;
848 RetCode := '0';
849 EXCEPTION
850 WHEN OTHERS THEN
851 Errbuf := sqlerrm;
852 RetCode := '2';*/
853 END populate_cmn_taxes;
854
855 PROCEDURE Populate_cmn_lines
856 (
857 p_hdr_intf_id IN NUMBER,
858 p_invoice_num IN VARCHAR2,
859 p_invoice_date IN DATE,
860 errbuf OUT NOCOPY VARCHAR2,
861 retcode OUT NOCOPY VARCHAR2
862 ) IS
863 CURSOR c_txns_interface(cp_hdr_intf_id IN NUMBER) IS
864 /*select *
865 from rcv_transactions_interface
866 where HEADER_INTERFACE_ID = cp_hdr_intf_id;*/
867 SELECT rtxn.*,
868 (SELECT currency_code
869 FROM po_headers_all
870 WHERE po_header_id = rtxn.po_header_id) CUR_CODE,
871 (SELECT quantity
872 FROM po_line_locations_all
873 WHERE LINE_LOCATION_ID = rtxn.PO_LINE_LOCATION_ID) TOT_QUANTITY
874 FROM rcv_transactions_interface rtxn
875 WHERE rtxn.HEADER_INTERFACE_ID = cp_hdr_intf_id;
876
877 CURSOR c_hdr_interface(cp_hdr_intf_id IN NUMBER) IS
878 SELECT shipment_num
879 FROM rcv_headers_interface
880 WHERE HEADER_INTERFACE_ID = cp_hdr_intf_id;
881
882 CURSOR c_po_hdr_currency(cp_po_header_id IN NUMBER) IS
883 SELECT CURRENCY_CODE
884 FROM PO_HEADERS
885 WHERE PO_HEADER_ID = cp_po_header_id;
886
887 CURSOR c_po_line_loc_details(cp_line_location_id IN NUMBER) IS
888 SELECT *
889 FROM po_line_locations_all
890 WHERE line_location_id = cp_line_location_id;
891
892 r_line_locations c_po_line_loc_details%ROWTYPE;
893
894 --r_txns c_txns_interface%rowtype;
895 r_hdrs c_hdr_interface%ROWTYPE;
896 ln_cmn_line_id NUMBER;
897 lv_ship_num VARCHAR2(30);
898 lv_errbuf VARCHAR2(2000);
899 lv_retcode VARCHAR2(200);
900
901 BEGIN
902 OPEN c_hdr_interface(p_hdr_intf_id);
903 FETCH c_hdr_interface
904 INTO r_hdrs;
905 CLOSE c_hdr_interface;
906
907 lv_ship_num := r_hdrs.shipment_num;
908
909 FOR r_txns IN c_txns_interface(p_hdr_intf_id)
910 LOOP
911 /*ln_cmn_line_id := JAI_CMN_LINES_S.nextval;*/
912 SELECT JAI_CMN_LINES_S.NEXTVAL INTO ln_cmn_line_id FROM DUAL;
913
914 OPEN c_po_line_loc_details(r_txns.po_line_location_id);
915 FETCH c_po_line_loc_details
916 INTO r_line_locations;
917 CLOSE c_po_line_loc_details;
918
919 INSERT INTO JAI_CMN_LINES
920 (CMN_LINE_ID,
921 SHIPMENT_NUMBER,
922 PO_HEADER_ID,
923 PO_LINE_ID,
924 PO_LINE_LOCATION_ID,
925 PO_NUMBER,
926 LINE_NUM,
927 SHIPMENT_LINE_NUM,
928 HEADER_INTERFACE_ID,
929 SHIPMENT_HEADER_ID,
930 INTERFACE_TRANSACTION_ID,
931 SHIPMENT_LINE_ID,
932 ITEM_ID,
933 QUANTITY,
934 EXCISE_INV_NUMBER,
935 EXCISE_INV_DATE,
936 CURRENCY_CODE,
937 PO_UNIT_PRICE)
938 --,
939 -- UOM_CODE)
940 VALUES (ln_cmn_line_id,
941 lv_ship_num,
942 --r_txns.SHIPMENT_NUM,
943 r_txns.PO_HEADER_ID,
944 r_txns.PO_LINE_ID,
945 r_txns.PO_LINE_LOCATION_ID,
946 r_txns.DOCUMENT_NUM,
947 r_txns.DOCUMENT_LINE_NUM,
948 r_txns.DOCUMENT_SHIPMENT_LINE_NUM,
949 r_txns.HEADER_INTERFACE_ID,
950 r_txns.SHIPMENT_HEADER_ID,
951 r_txns.INTERFACE_TRANSACTION_ID,
952 r_txns.SHIPMENT_LINE_ID,
953 r_txns.ITEM_ID,
954 r_txns.QUANTITY,
955 p_invoice_num,
956 p_invoice_date,
957 r_txns.CUR_CODE,
958 nvl(r_txns.PO_UNIT_PRICE, r_line_locations.price_override)); --,
959 -- r_txns.UOM_CODE);
960
961 JAI_PO_HOOK_PKG.populate_cmn_taxes(r_txns.PO_HEADER_ID,
962 r_txns.PO_LINE_LOCATION_ID,
963 r_txns.HEADER_INTERFACE_ID,
964 ln_cmn_line_id,
965 r_txns.QUANTITY,
966 r_txns.TOT_QUANTITY);
967
968 -- following call added by ssumaith - bug#3637364
969 -- it was done so that the taxes are re-calculated after the insert is done
970 JAI_PO_HOOK_PKG.calc_taxes('ASBN',r_txns.HEADER_INTERFACE_ID,ln_cmn_line_id,lv_errbuf,lv_retcode);
971
972 END LOOP;
973 errbuf := NULL;
974 RetCode := '0';
975
976 EXCEPTION
977 WHEN OTHERS THEN
978 Errbuf := SQLERRM;
979 RetCode := '2';
980 END Populate_cmn_lines;
981
982 PROCEDURE POPULATE_CMN_LINES_ON_UPLOAD
983 (
984 p_hdr_intf_id IN NUMBER,
985 errbuf OUT NOCOPY VARCHAR2,
986 retcode OUT NOCOPY VARCHAR2
987 ) IS
988 CURSOR c_hdr_intf(cp_grp_id IN NUMBER) IS
989 select shipment_num, asn_type, header_interface_id
990 from rcv_headers_interface
991 where group_id = cp_grp_id;
992
993 lv_errbuf1 VARCHAR2(2000);
994 lv_retcode1 VARCHAR2(200);
995 lv_errbuf VARCHAR2(2000);
996 lv_retcode VARCHAR2(200);
997 e1 exception;
998 e2 exception;
999 BEGIN
1000 For r_hdr in c_hdr_intf(p_hdr_intf_id)
1001 Loop
1002 JAI_PO_HOOK_PKG.POPULATE_CMN_LINES(r_hdr.header_interface_id,NULL,NULL,lv_errbuf,lv_retcode);
1003 IF lv_retcode <> '0' THEN
1004 raise e1;
1005 END IF;
1006 --IF r_hdr.asn_type = 'ASBN' THEN
1007 JAI_PO_HOOK_PKG.UPDATE_ASBN_MODE(r_hdr.shipment_num,'PENDING',lv_errbuf1,lv_retcode1);
1008 --end if;
1009 IF lv_retcode1 <> '0' THEN
1010 raise e2;
1011 END IF;
1012 End loop;
1013 errbuf := NULL;
1014 RetCode := '0';
1015
1016 EXCEPTION
1017 WHEN e1 THEN
1018 Errbuf := lv_errbuf;
1019 RetCode := '2';
1020 WHEN e2 THEN
1021 Errbuf := lv_errbuf1;
1022 RetCode := '2';
1023 WHEN OTHERS THEN
1024 Errbuf := SQLERRM;
1025 RetCode := '2';
1026 END POPULATE_CMN_LINES_ON_UPLOAD;
1027
1028
1029 PROCEDURE UPDATE_RCV_TXN
1030 (
1031 P_transaction_id IN NUMBER,
1032 p_invoice_num IN VARCHAR2,
1033 P_invoice_date IN VARCHAR2,
1034 errbuf OUT NOCOPY VARCHAR2,
1035 retcode OUT NOCOPY VARCHAR2
1036 ) IS
1037 lv_attr_categ VARCHAR2(30) := 'India Receipt';
1038 BEGIN
1039 UPDATE RCV_TRANSACTIONS_INTERFACE
1040 SET ATTRIBUTE_CATEGORY = lv_attr_categ,
1041 ATTRIBUTE1 = p_invoice_num,
1042 ATTRIBUTE2 = P_invoice_date
1043 WHERE INTERFACE_TRANSACTION_ID = P_transaction_id;
1044
1045 errbuf := NULL;
1046 RetCode := '0';
1047 EXCEPTION
1048 WHEN OTHERS THEN
1049 Errbuf := SQLERRM;
1050 RetCode := '2';
1051 END UPDATE_RCV_TXN;
1052
1053
1054
1055
1056 FUNCTION gettax
1057 (
1058 p_document_type IN VARCHAR2,
1059 p_header_id IN NUMBER,
1060 p_line_id IN NUMBER
1061 ) RETURN NUMBER IS
1062 /* Get the requisition tax amount for each requisition line currency code wise*/
1063 CURSOR c_reqn_tax_amt IS
1064 SELECT rtl.requisition_line_id, rtl.currency, rtl.tax_amount, nvl(tax.rounding_factor, 0) rnd_factor
1065 FROM JAI_PO_REQ_LINE_TAXES rtl, JAI_CMN_TAXES_ALL tax , po_requisition_lines_all porl
1066 WHERE rtl.requisition_header_id = p_header_id
1067 AND (rtl.requisition_line_id = p_line_id OR p_line_id IS NULL)
1068 AND porl.requisition_line_id = rtl.requisition_line_id
1069 AND tax.tax_id = rtl.tax_id
1070 ORDER BY rtl.currency;
1071 /* Get the receipt tax amount for each shipment line currency code wise*/
1072 CURSOR c_rcpt_tax_amt IS
1073 SELECT shipment_line_id, currency, SUM(tax_amount) tax_amount
1074 FROM JAI_RCV_LINE_TAXES
1075 WHERE shipment_header_id = p_header_id
1076 AND (shipment_line_id = p_line_id OR p_line_id IS NULL)
1077 GROUP BY shipment_line_id, currency;
1078 /* Get the conversion rate for the currency code for the requisition line */
1079 CURSOR c_conv_Rate(cp_reqn_line_id NUMBER) IS
1080 SELECT rate, currency_code, cancel_flag, org_id
1081 FROM po_requisition_lines_all
1082 WHERE requisition_line_id = cp_reqn_line_id;
1083 /* Get the conversion rate for the currency code for the receipt shipment line */
1084 CURSOR c_conv_rate_rcpt(cp_shipment_line_id IN NUMBER) IS
1085 SELECT currency_conversion_rate, currency_code
1086 FROM rcv_transactions
1087 WHERE shipment_line_id = cp_shipment_line_id
1088 AND transaction_type = 'RECEIVE';
1089 /* Get the RFQ / PO / BPA tax amount for each line location currency code wise*/
1090 CURSOR c_po_rfq IS
1091 SELECT line_location_id, currency, SUM(tax_amount) tax_amount
1092 FROM JAI_PO_TAXES
1093 WHERE po_header_id = p_header_id
1094 AND (line_location_id = p_line_id OR p_line_id IS NULL)
1095 GROUP BY line_location_id, currency;
1096
1097 /* Get the conversion rate for the currency code for the PO / RFQ */
1098 CURSOR c_conv_rate_rfq(cp_header_id IN NUMBER) IS
1099 SELECT rate, currency_code
1100 FROM po_headers
1101 WHERE po_header_id = cp_header_id;
1102
1103 -- pramasub start
1104 CURSOR c_conv_rate_inv(cp_header_id IN NUMBER, cp_line_id IN NUMBER) IS
1105 SELECT exchange_rate, currency_code
1106 FROM JAI_AP_MATCH_INV_TAXES
1107 WHERE invoice_id = cp_header_id
1108 And parent_invoice_distribution_id = cp_line_id;
1109 -- pramasub end
1110
1111
1112 CURSOR c_inv_amt IS
1113 SELECT SUM(amount)
1114 FROM ap_invoice_distributions_all
1115 WHERE invoice_id = p_header_id
1116 AND line_type_lookup_code = 'MISCELLANEOUS';
1117
1118 CURSOR c_sob_curr(cp_org_id NUMBER) IS
1119 SELECT currency_code
1120 FROM gl_sets_of_booKs
1121 WHERE set_of_books_id IN
1122 (SELECT set_of_books_id
1123 FROM hr_operating_units
1124 WHERE organization_id = cp_org_id);
1125
1126 -- pramasub start
1127 CURSOR c_cmn_doc_taxes(cp_cmn_line_id IN NUMBER, cp_doc_type IN VARCHAR2) IS
1128 SELECT source_doc_line_id, currency_code, SUM(tax_amt) tax_amount
1129 FROM JAI_CMN_DOCUMENT_TAXES
1130 WHERE source_doc_line_id = cp_cmn_line_id
1131 AND source_doc_type = cp_doc_type
1132 GROUP BY source_doc_line_id, currency_code;
1133 -- pramasub end
1134
1135 CURSOR c_conv_rate_asbn(cp_cmn_line_id IN NUMBER) IS
1136 SELECT PHA.rate, PHA.currency_code
1137 FROM PO_HEADERS_ALL PHA
1138 WHERE PHA.PO_HEADER_ID =
1139 (SELECT PO_HEADER_ID
1140 FROM JAI_CMN_LINES
1141 WHERE CMN_LINE_ID = cp_cmn_line_id);
1142
1143 ln_Tax_amt NUMBER;
1144 lv_curr_tax_amt NUMBER;
1145 ln_conv_rate NUMBER;
1146 lv_line_Currency VARCHAR2(15);
1147 lv_cancel_flag VARCHAR2(1);
1148 lv_func_currency VARCHAR2(15);
1149 ln_org_id NUMBER;
1150
1151 BEGIN
1152
1153 ln_tax_amt := 0;
1154 IF p_document_Type = 'REQUISITION' THEN
1155
1156 FOR r_reqn_rec IN c_reqn_tax_amt
1157 LOOP
1158
1159 OPEN c_conv_Rate(r_reqn_rec.requisition_line_id);
1160 FETCH c_conv_Rate
1161 INTO ln_conv_rate, lv_line_Currency, lv_cancel_flag, ln_org_id;
1162 CLOSE c_conv_Rate;
1163
1164 IF ln_org_id IS NOT NULL THEN
1165 /* just fetch it once*/
1166 OPEN c_sob_curr(ln_org_id);
1167 FETCH c_sob_curr
1168 INTO lv_func_currency;
1169 CLOSE c_sob_curr;
1170 END IF;
1171
1172 IF nvl(lv_cancel_flag, '$') <> 'Y' THEN
1173
1174 IF p_line_id IS NOT NULL THEN
1175
1176 /*
1177 || Tax currency not same as line currency means tax currency = func currency
1178 */
1179 IF NVL(r_reqn_rec.currency, '$$$') <>
1180 NVL(lv_line_currency, '$$$') THEN
1181 IF ln_conv_rate IS NULL THEN
1182 ln_conv_rate := 1;
1183 END IF;
1184 ELSE
1185 ln_conv_rate := 1;
1186 END IF;
1187
1188 /*
1189 || being called at line level . need to show tax in line currency.
1190 || if tax amt is in func curr , then the conv rate will be a actual value
1191 || hence divide would cause the tax to show in po currency
1192
1193 || if tax amt is in func curr , then the conv rate = 1
1194 */
1195 ln_tax_amt := nvl(ln_tax_amt, 0) +
1196 round(((r_reqn_rec.tax_amount) / ln_conv_rate),r_reqn_rec.rnd_factor);
1197 ELSE
1198 /*
1199 || being called from header - need to show the tax in func currency.
1200 || tax currency same as func currency
1201 */
1202 IF NVL(r_reqn_rec.currency, '$$$') =
1203 NVL(lv_func_currency, '$$$') THEN
1204 ln_tax_amt := nvl(ln_tax_amt, 0) + (r_reqn_rec.tax_amount);
1205 ELSE
1206 ln_tax_amt := nvl(ln_tax_amt, 0) +
1207 round((r_reqn_rec.tax_amount *nvl( ln_conv_Rate,1)),r_reqn_rec.rnd_factor); -- nvl correction made by pramasub #6066485
1208 END IF;
1209 END IF;
1210
1211 END IF;
1212
1213 END LOOP;
1214
1215 ELSIF p_document_type = 'RECEIPTS' THEN
1216
1217 FOR r_rcpt_rec IN c_rcpt_tax_amt
1218
1219 LOOP
1220 OPEN c_conv_rate_rcpt(r_rcpt_rec.shipment_line_id);
1221 FETCH c_conv_rate_rcpt
1222 INTO ln_conv_rate, lv_line_currency;
1223 CLOSE c_conv_rate_rcpt;
1224
1225 IF NVL(r_rcpt_rec.currency, '$$$') <> NVL(lv_line_currency, '$$$') THEN
1226 IF ln_conv_rate IS NULL THEN
1227 ln_conv_rate := 1;
1228 END IF;
1229 ELSE
1230 ln_conv_rate := 1;
1231 END IF;
1232
1233 ln_tax_amt := nvl(ln_tax_amt, 0) +
1234 ( (r_rcpt_rec.tax_amount) / ln_conv_rate);
1235 END LOOP;
1236
1237 ELSIF p_document_Type IN ('RFQ', 'PO', 'BPA') THEN
1238
1239 OPEN c_conv_rate_rfq(p_header_id);
1240 FETCH c_conv_rate_rfq
1241 INTO ln_conv_rate, lv_line_currency;
1242 CLOSE c_conv_rate_rfq;
1243
1244 FOR r_rfq_po_rec IN c_po_rfq
1245 LOOP
1246 IF NVL(r_rfq_po_rec.currency, '$$$') <>
1247 NVL(lv_line_currency, '$$$') THEN
1248 IF ln_conv_rate IS NULL THEN
1249 ln_conv_rate := 1;
1250 END IF;
1251 ELSE
1252 ln_conv_rate := 1;
1253 END IF;
1254
1255 ln_tax_amt := nvl(ln_tax_amt, 0) +
1256 (ln_conv_rate * (r_rfq_po_rec.tax_amount));
1257
1258 END LOOP;
1259
1260 ELSIF p_document_Type IN ('SHIPMENT', 'RELEASE', 'PO_LINE') THEN
1261
1262 /*
1263 || p_document_type = 'RELEASE' - p_header_id = po_header_id , po_line_id = release_id
1264 || p_document_type = 'SHIPMENT' - p_header_id = po_header_id , po_line_id = p_line_location_id
1265 || p_document_type = 'PO_LINE' - p_header_id = po_header_id , po_line_id = p_line_id
1266 */
1267
1268 OPEN c_conv_rate_rfq(p_header_id);
1269 FETCH c_conv_rate_rfq
1270 INTO ln_conv_rate, lv_line_currency;
1271 CLOSE c_conv_rate_rfq;
1272
1273 IF p_document_Type = 'SHIPMENT' THEN
1274
1275 /*
1276 || We would have got the po_header_id and line_location_id
1277 || we will get the details from the JAI_PO_TAXES table.
1278 */
1279
1280 ln_tax_amt := 0;
1281
1282 FOR r_shipment_rec IN (SELECT currency, SUM(tax_amount) tax_amount
1283 FROM JAI_PO_TAXES
1284 WHERE line_location_id = p_line_id
1285 GROUP BY currency)
1286 LOOP
1287
1288 IF NVL(r_shipment_rec.currency, '$$$') <>
1289 NVL(lv_line_currency, '$$$') THEN
1290 IF ln_conv_rate IS NULL THEN
1291 ln_conv_rate := 1;
1292 END IF;
1293 ELSE
1294 ln_conv_rate := 1;
1295 END IF;
1296
1297 ln_tax_amt := nvl(ln_tax_amt, 0) +
1298 (ln_conv_rate * (r_shipment_rec.tax_amount));
1299
1300 END LOOP;
1301
1302 RETURN(ln_tax_amt);
1303
1304 ELSIF p_document_Type = 'RELEASE' THEN
1305
1306 ln_tax_amt := 0;
1307
1308 /*
1309 || we would have the po_header_id and po_release_id
1310 || from the po_release_id we should get the line_location_id
1311 || for a release the po_line_locations_table will have all the details
1312 */
1313 FOR r_release_rec IN (SELECT currency, SUM(tax_amount) tax_amount
1314 FROM JAI_PO_TAXES a,
1315 po_line_locations_all b
1316 WHERE b.po_header_id = p_header_id
1317 AND b.po_release_id = p_line_id
1318 AND a.po_header_id = b.po_header_id
1319 AND a.po_line_id = b.po_line_id
1320 AND a.line_location_id = b.line_location_id
1321 GROUP BY currency)
1322 LOOP
1323
1324 IF NVL(r_release_rec.currency, '$$$') <>
1325 NVL(lv_line_currency, '$$$') THEN
1326 IF ln_conv_rate IS NULL THEN
1327 ln_conv_rate := 1;
1328 END IF;
1329 ELSE
1330 ln_conv_rate := 1;
1331 END IF;
1332
1333 ln_tax_amt := nvl(ln_tax_amt, 0) +
1334 (ln_conv_rate * (r_release_rec.tax_amount));
1335
1336 END LOOP;
1337
1338 RETURN(ln_tax_amt);
1339
1340 ELSIF p_document_Type = 'PO_LINE' THEN
1341
1342 /*
1343 || we would get the po_header_id and po_line_id
1344 || we would get the tax amounts from the JAI_PO_TAXES table.
1345 */
1346
1347 FOR r_po_line_rec IN (SELECT currency, SUM(tax_amount) tax_amount
1348 FROM JAI_PO_TAXES a
1349 WHERE po_header_id = p_header_id
1350 AND po_line_id = p_line_id
1351 GROUP BY currency)
1352 LOOP
1353
1354 IF NVL(r_po_line_rec.currency, '$$$') <>
1355 NVL(lv_line_currency, '$$$') THEN
1356 IF ln_conv_rate IS NULL THEN
1357 ln_conv_rate := 1;
1358 END IF;
1359 ELSE
1360 ln_conv_rate := 1;
1361 END IF;
1362
1363 ln_tax_amt := nvl(ln_tax_amt, 0) +
1364 (ln_conv_rate * (r_po_line_rec.tax_amount));
1365
1366 END LOOP;
1367
1368 RETURN(ln_tax_amt);
1369
1370 END IF;
1371
1372 ELSIF p_document_Type = 'INVOICE' THEN
1373
1374 ln_tax_amt := 0;
1375 if p_line_id is null then
1376 OPEN c_inv_amt;
1377 FETCH c_inv_amt
1378 INTO ln_tax_amt;
1379 CLOSE c_inv_amt;
1380 Else
1381 For r_ap_in_dist in (SELECT currency_code, SUM(tax_amount) tax_amount
1382 FROM JAI_AP_MATCH_INV_TAXES
1383 where invoice_id = p_header_id
1384 And parent_invoice_distribution_id = p_line_id
1385 GROUP BY currency_code)
1386 Loop
1387 OPEN c_conv_rate_inv(p_header_id, p_line_id);
1388 FETCH c_conv_rate_inv
1389 INTO ln_conv_rate, lv_line_currency;
1390 CLOSE c_conv_rate_inv;
1391
1392 IF NVL(r_ap_in_dist.currency_code, '$$$') <>
1393 NVL(lv_line_currency, '$$$') THEN
1394 IF ln_conv_rate IS NULL THEN
1395 ln_conv_rate := 1;
1396 END IF;
1397 ELSE
1398 ln_conv_rate := 1;
1399 END IF;
1400
1401 ln_tax_amt := nvl(ln_tax_amt, 0) +
1402 ((r_ap_in_dist.tax_amount) / ln_conv_rate );
1403 End loop;
1404 End if;
1405
1406 RETURN(ln_tax_amt);
1407
1408 ELSIF p_document_Type = 'ASBN' THEN
1409
1410 FOR r_cmn_doc_tax IN c_cmn_doc_taxes(p_line_id, p_document_Type)
1411 LOOP
1412 OPEN c_conv_rate_asbn(r_cmn_doc_tax.source_doc_line_id);
1413 FETCH c_conv_rate_asbn
1414 INTO ln_conv_rate, lv_line_currency;
1415 CLOSE c_conv_rate_asbn;
1416
1417 IF NVL(r_cmn_doc_tax.currency_code, '$$$') <>
1418 NVL(lv_line_currency, '$$$') THEN
1419 IF ln_conv_rate IS NULL THEN
1420 ln_conv_rate := 1;
1421 END IF;
1422 ELSE
1423 ln_conv_rate := 1;
1424 END IF;
1425
1426 ln_tax_amt := nvl(ln_tax_amt, 0) +
1427 ( (r_cmn_doc_tax.tax_amount) / ln_conv_rate );
1428
1429 END LOOP;
1430
1431 END IF;
1432
1433 RETURN(ln_tax_amt);
1434 EXCEPTION
1435 WHEN OTHERS THEN
1436 RETURN(-1);
1437 END gettax;
1438
1439 FUNCTION get_taxes_inr
1440 (
1441 p_document_id IN VARCHAR2,
1442 p_header_id IN NUMBER,
1443 p_line_id IN NUMBER
1444 ) RETURN NUMBER IS
1445 /* Get the requisition tax amount for each requisition line currency code wise*/
1446 CURSOR c_reqn_tax_amt IS
1447 SELECT requisition_line_id, currency, SUM(tax_amount) tax_amount
1448 FROM JAI_PO_REQ_LINE_TAXES
1449 WHERE requisition_header_id = p_header_id
1450 AND requisition_line_id = p_line_id
1451 GROUP BY requisition_line_id, currency;
1452 /* Get the conversion rate for the currency code for the requisition line */
1453 CURSOR c_conv_Rate(cp_reqn_line_id NUMBER) IS
1454 SELECT rate, currency_code, cancel_flag, org_id
1455 FROM po_requisition_lines_all
1456 WHERE requisition_line_id = cp_reqn_line_id;
1457
1458 CURSOR c_sob_curr(cp_org_id NUMBER) IS
1459 SELECT currency_code
1460 FROM gl_sets_of_booKs
1461 WHERE set_of_books_id IN
1462 (SELECT set_of_books_id
1463 FROM hr_operating_units
1464 WHERE organization_id = cp_org_id);
1465
1466 ln_tax_amt NUMBER ;
1467 ln_conv_rate NUMBER ;
1468 ln_org_id NUMBER ;
1469 lv_cancel_flag VARCHAR2(16);
1470 lv_func_currency VARCHAR2(16);
1471 lv_line_currency VARCHAR2(16);
1472
1473 BEGIN
1474
1475 ln_tax_amt := 0;
1476
1477 IF p_document_id = 'REQUISITION' THEN
1478 FOR r_reqn_rec IN c_reqn_tax_amt
1479 LOOP
1480
1481 OPEN c_conv_Rate(r_reqn_rec.requisition_line_id);
1482 FETCH c_conv_Rate
1483 INTO ln_conv_rate, lv_line_Currency, lv_cancel_flag, ln_org_id;
1484 CLOSE c_conv_Rate;
1485
1486 IF ln_org_id IS NOT NULL THEN
1487 /* just fetch it once*/
1488 OPEN c_sob_curr(ln_org_id);
1489 FETCH c_sob_curr
1490 INTO lv_func_currency;
1491 CLOSE c_sob_curr;
1492 END IF;
1493
1494 IF nvl(lv_cancel_flag, '$') <> 'Y' THEN
1495
1496 IF NVL(r_reqn_rec.currency, '$$$') =
1497 NVL(lv_func_currency, '$$$') THEN
1498 ln_tax_amt := nvl(ln_tax_amt, 0) + (r_reqn_rec.tax_amount);
1499 ELSE
1500 ln_tax_amt := nvl(ln_tax_amt, 0) +
1501 (r_reqn_rec.tax_amount * ln_conv_Rate);
1502 END IF;
1503 END IF;
1504
1505
1506 END LOOP;
1507 END IF;
1508
1509 RETURN(ln_tax_amt);
1510 EXCEPTION
1511 WHEN OTHERS THEN
1512 RETURN -1;
1513 END get_taxes_inr;
1514
1515 FUNCTION gettax
1516 (
1517 p_document_type IN VARCHAR2,
1518 p_header_id IN NUMBER,
1519 p_line_id IN NUMBER,
1520 p_shipment_line_num IN NUMBER
1521 ) RETURN NUMBER IS
1522 CURSOR c_line_location IS
1523 SELECT line_location_id
1524 FROM po_line_locations_all
1525 WHERE po_line_id = p_line_id
1526 AND shipment_num = p_shipment_line_num;
1527
1528 ln_line_location_id NUMBER;
1529 BEGIN
1530 IF p_shipment_line_num IS NULL THEN
1531 RETURN(-1);
1532 ELSIF p_document_type IS NULL
1533 OR p_header_id IS NULL
1534 OR p_line_id IS NULL THEN
1535 RETURN(-2);
1536 ELSE
1537 OPEN c_line_location;
1538 FETCH c_line_location
1539 INTO ln_line_location_id;
1540 CLOSE c_line_location;
1541
1542 IF ln_line_location_id IS NOT NULL THEN
1543 RETURN(gettax(p_document_type, p_header_id, ln_line_location_id));
1544 ELSE
1545 RETURN(-4);
1546 END IF;
1547 END IF;
1548 EXCEPTION
1549 WHEN OTHERS THEN
1550 RETURN(-8);
1551 END gettax;
1552
1553 FUNCTION get_profile_value(cp_profile_name VARCHAR2) RETURN VARCHAR2 IS
1554 CURSOR c_iproc_profile IS
1555 SELECT fnd_profile.VALUE(cp_profile_name) FROM DUAL;
1556
1557 lv_iproc_profile VARCHAR2(100);
1558
1559 BEGIN
1560
1561 IF cp_profile_name IS NOT NULL THEN
1562 OPEN c_iproc_profile;
1563 FETCH c_iproc_profile
1564 INTO lv_iproc_profile;
1565 CLOSE c_iproc_profile;
1566 END IF;
1567
1568 IF NVL(lv_iproc_profile, '2') = '2' THEN
1569 lv_iproc_profile := 'N';
1570 ELSE
1571 lv_iproc_profile := 'Y';
1572 END IF;
1573
1574 RETURN(lv_iproc_profile);
1575 EXCEPTION
1576 WHEN OTHERS THEN
1577 lv_iproc_profile := 'N';
1578 RETURN(lv_iproc_profile);
1579 END get_profile_value;
1580
1581 PROCEDURE UPDATE_ASBN_MODE
1582 (
1583 p_shipment_num IN VARCHAR2,
1584 p_mode IN VARCHAR2,
1585 errbuf OUT NOCOPY VARCHAR2,
1586 retcode OUT NOCOPY VARCHAR2
1587 ) IS
1588 lv_header_id NUMBER;
1589
1590 BEGIN
1591 SELECT header_interface_id
1592 INTO lv_header_id
1593 FROM rcv_headers_interface
1594 WHERE shipment_num = p_shipment_num;
1595
1596 IF (p_mode = 'PENDING') THEN
1597
1598 UPDATE rcv_headers_interface
1599 SET PROCESSING_STATUS_CODE = 'IL_PENDING'
1600 WHERE header_interface_id = lv_header_id
1601 AND PROCESSING_STATUS_CODE = 'PENDING';
1602
1603 UPDATE RCV_TRANSACTIONS_INTERFACE
1604 SET PROCESSING_STATUS_CODE = 'IL_PENDING'
1605 WHERE header_interface_id = lv_header_id
1606 AND PROCESSING_STATUS_CODE = 'PENDING';
1607
1608 ELSIF p_mode = 'IL_PENDING' THEN
1609
1610 UPDATE rcv_headers_interface
1611 SET PROCESSING_STATUS_CODE = 'PENDING'
1612 WHERE header_interface_id = lv_header_id
1613 AND PROCESSING_STATUS_CODE = 'IL_PENDING';
1614
1615 UPDATE RCV_TRANSACTIONS_INTERFACE
1616 SET PROCESSING_STATUS_CODE = 'PENDING'
1617 WHERE header_interface_id = lv_header_id
1618 AND PROCESSING_STATUS_CODE = 'IL_PENDING';
1619
1620 END IF;
1621
1622 errbuf := NULL;
1623 retcode := '0';
1624
1625 EXCEPTION
1626 WHEN OTHERS THEN
1627 errbuf := SQLERRM;
1628 retcode := '2';
1629 END UPDATE_ASBN_MODE;
1630
1631
1632 /*Function added by srjayara for iSupplier pages*/
1633 FUNCTION gettaxisp
1634 (
1635 p_document_type IN VARCHAR2,
1636 p_header_id IN NUMBER,
1637 p_line_id IN NUMBER,
1638 p_release_id IN NUMBER
1639 ) RETURN NUMBER IS
1640
1641
1642 /* Get the RFQ / PO / BPA tax amount for each line location currency code wise*/
1643 CURSOR c_po_rfq IS
1644 SELECT line_location_id, currency, SUM(tax_amount) tax_amount
1645 FROM JAI_PO_TAXES
1646 WHERE po_header_id = p_header_id
1647 AND (line_location_id = p_line_id OR p_line_id IS NULL)
1648 GROUP BY line_location_id, currency;
1649
1650 -- commented out by pramasub
1651 CURSOR c_po_ppo IS
1652 SELECT a.line_location_id, currency, SUM(tax_amount) tax_amount
1653 FROM JAI_PO_TAXES a,
1654 po_line_locations_all b
1655 WHERE a.po_header_id = p_header_id
1656 AND a.po_header_id = b.po_header_id
1657 AND a.po_line_id = b.po_line_id
1658 AND a.line_location_id = b.line_location_id
1659 and b.po_release_id is null
1660 --AND (line_location_id = :p_line_id OR :p_line_id IS NULL)
1661 GROUP BY a.line_location_id, a.currency;
1662
1663 CURSOR c_po_bpa IS
1664 SELECT a.line_location_id, currency, SUM(tax_amount) tax_amount
1665 FROM JAI_PO_TAXES a,
1666 po_line_locations_all b
1667 WHERE a.po_header_id = p_header_id
1668 AND a.po_header_id = b.po_header_id
1669 AND a.po_line_id = b.po_line_id
1670 AND a.line_location_id = b.line_location_id
1671 and b.po_release_id is null
1672 AND (a.po_line_id = p_line_id OR p_line_id IS NULL)
1673 GROUP BY a.line_location_id, a.currency;
1674
1675 -- pramasub changes end here
1676
1677 /* Get the conversion rate for the currency code for the PO / RFQ */
1678 CURSOR c_conv_rate_rfq(cp_header_id IN NUMBER) IS
1679 SELECT rate, currency_code
1680 FROM po_headers_all
1681 WHERE po_header_id = cp_header_id;
1682
1683 /* Get Invoice amount*/
1684 CURSOR c_inv_amt IS
1685 SELECT SUM(amount)
1686 FROM ap_invoice_distributions_all
1687 WHERE invoice_id = p_header_id
1688 AND line_type_lookup_code = 'MISCELLANEOUS';
1689
1690 /* Get the set of books currency (function currency)*/
1691 CURSOR c_sob_curr(cp_org_id NUMBER) IS
1692 SELECT currency_code
1693 FROM gl_sets_of_booKs
1694 WHERE set_of_books_id IN
1695 (SELECT set_of_books_id
1696 FROM hr_operating_units
1697 WHERE organization_id = cp_org_id);
1698
1699 -- pramasub start /*for asn/asbn*/
1700 CURSOR c_cmn_doc_taxes(cp_cmn_line_id IN NUMBER, cp_doc_type IN VARCHAR2) IS
1701 SELECT SUM(tax_amt) tax_amount, currency_code
1702 FROM JAI_CMN_DOCUMENT_TAXES
1703 WHERE source_doc_line_id = cp_cmn_line_id
1704 AND source_doc_type = cp_doc_type
1705 GROUP BY currency_code;
1706 -- pramasub end
1707
1708 ln_Tax_amt NUMBER;
1709 lv_curr_tax_amt NUMBER;
1710 ln_conv_rate NUMBER;
1711 lv_line_Currency VARCHAR2(15);
1712 lv_cancel_flag VARCHAR2(1);
1713 lv_func_currency VARCHAR2(15);
1714 ln_org_id NUMBER;
1715
1716 BEGIN
1717
1718 ln_tax_amt := 0;
1719
1720 IF p_document_type = 'PO_RECEIPT_ALL' THEN
1721
1722 FOR rec in (select shipment_header_id,shipment_line_id
1723 from rcv_shipment_lines
1724 WHERE po_line_location_id IN (select line_Location_id
1725 from po_line_locations_all
1726 where po_header_id=p_header_id)
1727 AND (po_release_id = p_release_id or p_release_id is null)
1728 )
1729 LOOP
1730 ln_tax_amt := ln_tax_amt + gettax('RECEIPTS',rec.shipment_header_id,rec.shipment_line_id);
1731 END LOOP;
1732 RETURN (ln_tax_amt);
1733 END IF;
1734
1735 IF p_document_type = 'PO_INVOICE_ALL' THEN
1736
1737 FOR rec in (select distinct invoice_id
1738 from ap_invoice_lines_all
1739 where po_line_location_id IN (select line_Location_id
1740 from po_line_locations_all
1741 where po_header_id=p_header_id)
1742 AND (po_release_id = p_release_id or p_release_id is null)
1743 )
1744 LOOP
1745 ln_tax_amt := ln_tax_amt + gettax('INVOICE', rec.invoice_id,null);
1746 END LOOP;
1747 RETURN (ln_tax_amt);
1748 END IF;
1749
1750
1751 IF p_document_type = 'PO_RECEIPT' THEN
1752
1753 FOR rec in (select shipment_header_id,shipment_line_id from rcv_shipment_lines where po_line_location_id = p_line_id)
1754 LOOP
1755 ln_tax_amt := ln_tax_amt + gettax('RECEIPTS',rec.shipment_header_id,rec.shipment_line_id);
1756 END LOOP;
1757 RETURN (ln_tax_amt);
1758 END IF;
1759
1760 IF p_document_type = 'PO_INVOICE' THEN
1761
1762 FOR rec in (select distinct invoice_id from ap_invoice_lines_all where po_line_location_id = p_line_id)
1763 LOOP
1764 ln_tax_amt := ln_tax_amt + gettax('INVOICE', rec.invoice_id,null);
1765 END LOOP;
1766 RETURN (ln_tax_amt);
1767 END IF;
1768
1769 IF p_document_Type = 'GBA_ALL' Then
1770 FOR r_po_rec IN
1771 (SELECT po_header_id FROM po_lines_all WHERE from_header_id = p_header_id)
1772 LOOP
1773 ln_tax_amt := ln_tax_amt + gettaxisp('PO',r_po_rec.po_header_id,null,null);
1774 END LOOP;
1775 RETURN (ln_tax_amt);
1776 END IF;
1777
1778 IF p_document_Type = 'GBA' Then
1779 /*
1780 * This Code is Commented : GBA should display only its taxes
1781 * its should not display taxes of releases
1782 IF p_line_id IS NOT NULL THEN
1783 FOR r_po_rec IN
1784 (SELECT po_header_id, po_line_id FROM po_lines_all WHERE from_header_id = p_header_id AND from_line_id = p_line_id)
1785 LOOP
1786 ln_tax_amt := ln_tax_amt + gettaxisp('PO_LINE',r_po_rec.po_header_id,r_po_rec.po_line_id,null);
1787 END LOOP;
1788 ELSE
1789 FOR r_po_rec IN
1790 (SELECT po_header_id FROM po_lines_all WHERE from_header_id = p_header_id)
1791 LOOP
1792 ln_tax_amt := ln_tax_amt + gettaxisp('PO',r_po_rec.po_header_id,null,null);
1793 END LOOP;
1794 END IF;
1795 */
1796 IF p_line_id IS NOT NULL THEN
1797 FOR r_po_rec IN
1798 (SELECT po_header_id, po_line_id FROM po_lines_all WHERE po_header_id = p_header_id AND po_line_id = p_line_id)
1799 LOOP
1800 ln_tax_amt := ln_tax_amt + gettaxisp('PO_LINE',r_po_rec.po_header_id,r_po_rec.po_line_id,null);
1801 END LOOP;
1802 ELSE
1803 FOR r_po_rec IN
1804 (SELECT po_header_id FROM po_lines_all WHERE po_header_id = p_header_id)
1805 LOOP
1806 ln_tax_amt := ln_tax_amt + gettaxisp('PO',r_po_rec.po_header_id,null,null);
1807 END LOOP;
1808 END IF;
1809 RETURN (ln_tax_amt);
1810 END IF;
1811
1812 IF p_document_Type IN ('RFQ', 'PO') THEN
1813
1814
1815 FOR r_rfq_po_rec IN c_po_rfq
1816 LOOP
1817
1818 OPEN c_conv_rate_rfq(p_header_id);
1819 FETCH c_conv_rate_rfq
1820 INTO ln_conv_rate, lv_line_currency;
1821 CLOSE c_conv_rate_rfq;
1822
1823 IF NVL(r_rfq_po_rec.currency, '$$$') <>
1824 NVL(lv_line_currency, '$$$') THEN
1825 IF ln_conv_rate IS NULL THEN
1826 ln_conv_rate := 1;
1827 END IF;
1828 ELSE
1829 ln_conv_rate := 1;
1830 END IF;
1831
1832 ln_tax_amt := nvl(ln_tax_amt, 0) +
1833 ((r_rfq_po_rec.tax_amount) / ln_conv_rate);
1834
1835 END LOOP;
1836
1837 ELSIF p_document_Type = 'PPO' then
1838 FOR r_rfq_po_rec IN c_po_ppo
1839 LOOP
1840 OPEN c_conv_rate_rfq(p_header_id);
1841 FETCH c_conv_rate_rfq
1842 INTO ln_conv_rate, lv_line_currency;
1843 CLOSE c_conv_rate_rfq;
1844
1845 IF NVL(r_rfq_po_rec.currency, '$$$') <>
1846 NVL(lv_line_currency, '$$$') THEN
1847 IF ln_conv_rate IS NULL THEN
1848 ln_conv_rate := 1;
1849 END IF;
1850 ELSE
1851 ln_conv_rate := 1;
1852 END IF;
1853
1854 ln_tax_amt := nvl(ln_tax_amt, 0) +
1855 ((r_rfq_po_rec.tax_amount) / ln_conv_rate);
1856 END LOOP;
1857
1858 ELSIF p_document_Type = 'BPA' then
1859 FOR r_rfq_po_rec IN c_po_bpa
1860 LOOP
1861 OPEN c_conv_rate_rfq(p_header_id);
1862 FETCH c_conv_rate_rfq
1863 INTO ln_conv_rate, lv_line_currency;
1864 CLOSE c_conv_rate_rfq;
1865
1866 IF NVL(r_rfq_po_rec.currency, '$$$') <>
1867 NVL(lv_line_currency, '$$$') THEN
1868 IF ln_conv_rate IS NULL THEN
1869 ln_conv_rate := 1;
1870 END IF;
1871 ELSE
1872 ln_conv_rate := 1;
1873 END IF;
1874
1875 ln_tax_amt := nvl(ln_tax_amt, 0) +
1876 ((r_rfq_po_rec.tax_amount) / ln_conv_rate);
1877 END LOOP;
1878
1879 ELSIF p_document_Type IN ('SHIPMENT', 'RELEASE', 'PO_LINE', 'RELEASE_TOT') THEN
1880
1881 /*
1882 || p_document_type = 'RELEASE' - p_header_id = po_header_id , po_line_id = p_line_id, po_release_id = p_release_id
1883 || p_document_type = 'SHIPMENT' - p_header_id = po_header_id , po_line_id = p_line_location_id
1884 || p_document_type = 'PO_LINE' - p_header_id = po_header_id , po_line_id = p_line_id
1885 */
1886
1887
1888 IF p_document_Type = 'SHIPMENT' THEN
1889
1890 /*
1891 || We would have got the po_header_id and line_location_id
1892 || we will get the details from the JAI_PO_TAXES table.
1893 */
1894
1895 ln_tax_amt := 0;
1896
1897 FOR r_shipment_rec IN (SELECT currency, SUM(tax_amount) tax_amount
1898 FROM JAI_PO_TAXES
1899 WHERE line_location_id = p_line_id
1900 GROUP BY currency)
1901 LOOP
1902
1903 OPEN c_conv_rate_rfq(p_header_id);
1904 FETCH c_conv_rate_rfq
1905 INTO ln_conv_rate, lv_line_currency;
1906 CLOSE c_conv_rate_rfq;
1907
1908
1909 IF NVL(r_shipment_rec.currency, '$$$') <>
1910 NVL(lv_line_currency, '$$$') THEN
1911 IF ln_conv_rate IS NULL THEN
1912 ln_conv_rate := 1;
1913 END IF;
1914 ELSE
1915 ln_conv_rate := 1;
1916 END IF;
1917
1918 ln_tax_amt := nvl(ln_tax_amt, 0) +
1919 ((r_shipment_rec.tax_amount) / ln_conv_rate);
1920
1921 END LOOP;
1922
1923 RETURN(ln_tax_amt);
1924
1925 ELSIF p_document_Type = 'RELEASE' THEN
1926
1927 ln_tax_amt := 0;
1928
1929 /*
1930 || we would have the po_header_id and po_release_id (and po_line_id -optional)
1931 || from the po_release_id we should get the line_location_id
1932 || for a release the po_line_locations_table will have all the details
1933 */
1934 FOR r_release_rec IN (SELECT currency, SUM(tax_amount) tax_amount
1935 FROM JAI_PO_TAXES a,
1936 po_line_locations_all b
1937 WHERE b.po_header_id = p_header_id
1938 AND b.po_release_id = p_release_id
1939 AND (p_line_id IS NULL OR b.po_line_id = p_line_id)
1940 AND a.po_header_id = b.po_header_id
1941 AND a.po_line_id = b.po_line_id
1942 AND a.line_location_id = b.line_location_id
1943 GROUP BY currency)
1944 LOOP
1945
1946 OPEN c_conv_rate_rfq(p_header_id);
1947 FETCH c_conv_rate_rfq
1948 INTO ln_conv_rate, lv_line_currency;
1949 CLOSE c_conv_rate_rfq;
1950
1951
1952 IF NVL(r_release_rec.currency, '$$$') <>
1953 NVL(lv_line_currency, '$$$') THEN
1954 IF ln_conv_rate IS NULL THEN
1955 ln_conv_rate := 1;
1956 END IF;
1957 ELSE
1958 ln_conv_rate := 1;
1959 END IF;
1960
1961 ln_tax_amt := nvl(ln_tax_amt, 0) +
1962 ((r_release_rec.tax_amount) / ln_conv_rate);
1963
1964 END LOOP;
1965
1966 RETURN(ln_tax_amt);
1967 ELSIF p_document_Type = 'RELEASE_TOT' THEN
1968
1969 ln_tax_amt := 0;
1970
1971 /*
1972 || we would have the po_header_id and po_release_id (and po_line_id -optional)
1973 || from the po_release_id we should get the line_location_id
1974 || for a release the po_line_locations_table will have all the details
1975 */
1976 FOR r_release_rec IN (SELECT currency, SUM(tax_amount) tax_amount
1977 FROM JAI_PO_TAXES a,
1978 po_line_locations_all b,
1979 po_releases_all c -- added by pramasub #6441185
1980 WHERE b.po_header_id = p_header_id
1981 AND b.po_release_id is not null
1982 AND a.po_header_id = b.po_header_id
1983 AND a.po_line_id = b.po_line_id
1984 AND a.line_location_id = b.line_location_id
1985 AND c.po_release_id = b.po_release_id -- added by pramasub #6441185
1986 AND c.approved_flag = 'Y' -- added by pramasub #6441185
1987 GROUP BY currency)
1988 LOOP
1989
1990 OPEN c_conv_rate_rfq(p_header_id);
1991 FETCH c_conv_rate_rfq
1992 INTO ln_conv_rate, lv_line_currency;
1993 CLOSE c_conv_rate_rfq;
1994
1995
1996 IF NVL(r_release_rec.currency, '$$$') <>
1997 NVL(lv_line_currency, '$$$') THEN
1998 IF ln_conv_rate IS NULL THEN
1999 ln_conv_rate := 1;
2000 END IF;
2001 ELSE
2002 ln_conv_rate := 1;
2003 END IF;
2004
2005 ln_tax_amt := nvl(ln_tax_amt, 0) +
2006 ((r_release_rec.tax_amount) / ln_conv_rate);
2007
2008 END LOOP;
2009
2010 RETURN(ln_tax_amt);
2011
2012 ELSIF p_document_Type = 'PO_LINE' THEN
2013
2014 /*
2015 || we would get the po_header_id and po_line_id
2016 || we would get the tax amounts from the JAI_PO_TAXES table.
2017 */
2018
2019 FOR r_po_line_rec IN (SELECT currency, SUM(tax_amount) tax_amount
2020 FROM JAI_PO_TAXES a
2021 WHERE po_header_id = p_header_id
2022 AND po_line_id = p_line_id
2023 GROUP BY currency)
2024 LOOP
2025
2026 OPEN c_conv_rate_rfq(p_header_id);
2027 FETCH c_conv_rate_rfq
2028 INTO ln_conv_rate, lv_line_currency;
2029 CLOSE c_conv_rate_rfq;
2030
2031
2032 IF NVL(r_po_line_rec.currency, '$$$') <>
2033 NVL(lv_line_currency, '$$$') THEN
2034 IF ln_conv_rate IS NULL THEN
2035 ln_conv_rate := 1;
2036 END IF;
2037 ELSE
2038 ln_conv_rate := 1;
2039 END IF;
2040
2041 ln_tax_amt := nvl(ln_tax_amt, 0) +
2042 ((r_po_line_rec.tax_amount) / ln_conv_rate);
2043
2044 END LOOP;
2045
2046 RETURN(ln_tax_amt);
2047
2048 END IF;
2049
2050 ELSIF p_document_Type = 'INVOICE' THEN
2051
2052 OPEN c_inv_amt;
2053 FETCH c_inv_amt
2054 INTO ln_tax_amt;
2055 CLOSE c_inv_amt;
2056
2057 RETURN(ln_tax_amt);
2058
2059 ELSIF p_document_Type = 'ASBN' THEN
2060
2061 FOR r_cmn_doc_tax IN c_cmn_doc_taxes(p_line_id, p_document_Type)
2062 LOOP
2063 ln_tax_amt := r_cmn_doc_tax.tax_amount;
2064 END LOOP;
2065
2066 END IF;
2067
2068 RETURN(ln_tax_amt);
2069 EXCEPTION
2070 WHEN OTHERS THEN
2071 RETURN(-1);
2072 END gettaxisp;
2073
2074 --==========================================================================
2075 -- PROCEDURE NAME:
2076 --
2077 -- Get_InAndEx_Tax_Total Public
2078 --
2079 -- DESCRIPTION:
2080 --
2081 -- to calculate the inclusive and exclusive tax amount
2082 --
2083 -- PARAMETERS:
2084 -- In: pv_document_type document type
2085 -- pn_header_id header id
2086 -- pn_line_id line id
2087 -- pv_inclusive_tax_flag inclusive tax flag
2088 --
2089 -- DESIGN REFERENCES:
2090 -- Inclusive Tax Technical Design.doc
2091 --
2092 -- CHANGE HISTORY:
2093 --
2094 -- 20-NOV-2007 Jason Liu created
2095 FUNCTION Get_InAndEx_Tax_Total
2096 ( pv_document_type IN VARCHAR2
2097 , pn_header_id IN NUMBER
2098 , pn_line_id IN NUMBER
2099 , pv_inclusive_tax_flag IN VARCHAR2
2100 )
2101 RETURN NUMBER
2102 IS
2103
2104 CURSOR sob_curr_csr(pn_org_id NUMBER) IS
2105 SELECT currency_code
2106 FROM gl_sets_of_booKs
2107 WHERE set_of_books_id IN
2108 (SELECT set_of_books_id
2109 FROM hr_operating_units
2110 WHERE organization_id = pn_org_id);
2111
2112 --Get the requisition tax amount for each requisition line currency code
2113 CURSOR reqn_tax_amt_csr IS
2114 SELECT
2115 rtl.requisition_line_id
2116 , rtl.currency
2117 , rtl.tax_amount
2118 , NVL(tax.rounding_factor, 0) rnd_factor
2119 FROM
2120 jai_po_req_line_taxes rtl
2121 , jai_cmn_taxes_all tax
2122 , po_requisition_lines_all prla
2123 WHERE rtl.requisition_header_id = pn_header_id
2124 AND (rtl.requisition_line_id = pn_line_id OR pn_line_id IS NULL)
2125 AND prla.requisition_line_id = rtl.requisition_line_id
2126 AND tax.tax_id = rtl.tax_id
2127 AND NVL(tax.inclusive_tax_flag, 'N') = pv_inclusive_tax_flag
2128 ORDER BY rtl.currency;
2129
2130 --Get the conversion rate for the currency code for the requisition line
2131 CURSOR conv_rate_reqn_csr(cp_reqn_line_id NUMBER) IS
2132 SELECT
2133 rate
2134 , currency_code
2135 , cancel_flag
2136 , org_id
2137 FROM po_requisition_lines_all
2138 WHERE requisition_line_id = cp_reqn_line_id;
2139
2140 --Get the receipt tax amount for each shipment line currency code
2141 CURSOR rcpt_tax_amt_csr IS
2142 SELECT
2143 jrlt.shipment_line_id
2144 , jrlt.currency
2145 , SUM(jrlt.tax_amount) tax_amount
2146 FROM
2147 jai_rcv_line_taxes jrlt
2148 , jai_cmn_taxes_all jcta
2149 WHERE jrlt.shipment_header_id = pn_header_id
2150 AND (jrlt.shipment_line_id = pn_line_id OR pn_line_id IS NULL)
2151 AND jcta.tax_id = jrlt.tax_id
2152 AND NVL(jcta.inclusive_tax_flag, 'N') = pv_inclusive_tax_flag
2153 GROUP BY
2154 jrlt.shipment_line_id
2155 , jrlt.currency;
2156
2157 --Get the conversion rate for the currency code for the receipt shipment line
2158 CURSOR conv_rate_rcpt_csr(pn_shipment_line_id IN NUMBER) IS
2159 SELECT
2160 currency_conversion_rate
2161 , currency_code
2162 FROM rcv_transactions
2163 WHERE shipment_line_id = pn_shipment_line_id
2164 AND transaction_type = 'RECEIVE';
2165
2166 --Get the RFQ / PO / BPA tax amount for each line location currency code
2167 CURSOR po_rfq_tax_amt_csr IS
2168 SELECT
2169 jpt.line_location_id
2170 , jpt.currency
2171 , SUM(jpt.tax_amount) tax_amount
2172 FROM
2173 jai_po_taxes jpt
2174 , jai_cmn_taxes_all jcta
2175 WHERE jpt.po_header_id = pn_header_id
2176 AND (jpt.line_location_id = pn_line_id OR pn_line_id IS NULL)
2177 AND jcta.tax_id = jpt.tax_id
2178 AND NVL(jcta.inclusive_tax_flag, 'N') = pv_inclusive_tax_flag
2179 GROUP BY
2180 jpt.line_location_id
2181 , jpt.currency;
2182
2183 --Get the conversion rate for the currency code for the PO / RFQ / BPA / SHIPMENT/ RELEASE / PO_LINE
2184 CURSOR conv_rate_rfq_csr(pn_csr_header_id IN NUMBER) IS
2185 SELECT
2186 rate
2187 , currency_code
2188 FROM po_headers
2189 WHERE po_header_id = pn_csr_header_id;
2190
2191 --Get the invoice tax amount for each distribution currency code
2192 CURSOR inv_tax_amt_csr IS
2193 SELECT
2194 jamit.currency_code
2195 , SUM(jamit.tax_amount) tax_amount
2196 FROM
2197 jai_ap_match_inv_taxes jamit
2198 , jai_cmn_taxes_all jcta
2199 WHERE jamit.invoice_id = pn_header_id
2200 AND (jamit.parent_invoice_distribution_id = pn_line_id OR pn_line_id IS NULL)
2201 AND jcta.tax_id = jamit.tax_id
2202 AND NVL(jcta.inclusive_tax_flag, 'N') = pv_inclusive_tax_flag
2203 GROUP BY jamit.currency_code;
2204
2205 --Get the conversion rate for the currency code for the INVOICE
2206 CURSOR conv_rate_inv_csr
2207 ( pn_csr_header_id IN NUMBER
2208 , pn_csr_line_id IN NUMBER
2209 )
2210 IS
2211 SELECT
2212 exchange_rate
2213 , currency_code
2214 FROM jai_ap_match_inv_taxes
2215 WHERE invoice_id = pn_csr_header_id
2216 AND parent_invoice_distribution_id = pn_csr_line_id;
2217
2218 -- Get the shipments tax amount
2219 CURSOR shipments_tax_amt_csr IS
2220 SELECT
2221 jpt.currency
2222 , SUM(jpt.tax_amount) tax_amount
2223 FROM
2224 jai_po_taxes jpt
2225 , jai_cmn_taxes_all jcta
2226 WHERE jpt.line_location_id = pn_line_id
2227 AND jcta.tax_id = jpt.tax_id
2228 AND NVL(jcta.inclusive_tax_flag, 'N') = pv_inclusive_tax_flag
2229 GROUP BY jpt.currency;
2230
2231 -- Get the release tax amount
2232 CURSOR release_tax_amt_csr IS
2233 SELECT
2234 jpt.currency
2235 , SUM(jpt.tax_amount) tax_amount
2236 FROM
2237 jai_po_taxes jpt
2238 , po_line_locations_all plla
2239 , jai_cmn_taxes_all jcta
2240 WHERE plla.po_header_id = pn_header_id
2241 AND plla.po_release_id = pn_line_id
2242 AND jpt.po_header_id = plla.po_header_id
2243 AND jpt.po_line_id = plla.po_line_id
2244 AND jpt.line_location_id = plla.line_location_id
2245 AND jcta.tax_id = jpt.tax_id
2246 AND NVL(jcta.inclusive_tax_flag, 'N') = pv_inclusive_tax_flag
2247 GROUP BY jpt.currency;
2248
2249 -- Get the PO_lINE tax amount
2250 CURSOR po_line_tax_amt_csr IS
2251 SELECT
2252 jpt.currency
2253 , SUM(jpt.tax_amount) tax_amount
2254 FROM
2255 jai_po_taxes jpt
2256 , jai_cmn_taxes_all jcta
2257 WHERE po_header_id = pn_header_id
2258 AND po_line_id = pn_line_id
2259 AND jcta.tax_id = jpt.tax_id
2260 AND NVL(jcta.inclusive_tax_flag, 'N') = pv_inclusive_tax_flag
2261 GROUP BY jpt.currency;
2262
2263 -- Get ASBN tax amount
2264 CURSOR asbn_tax_amt_csr
2265 ( cp_cmn_line_id IN NUMBER
2266 , cp_doc_type IN VARCHAR2
2267 )
2268 IS
2269 SELECT
2270 jcdt.source_doc_line_id
2271 , jcdt.currency_code
2272 , SUM(jcdt.tax_amt) tax_amount
2273 FROM
2274 jai_cmn_document_taxes jcdt
2275 , jai_cmn_taxes_all jcta
2276 WHERE jcdt.source_doc_line_id = cp_cmn_line_id
2277 AND jcdt.source_doc_type = cp_doc_type
2278 AND jcta.tax_id = jcdt.tax_id
2279 AND NVL(jcta.inclusive_tax_flag, 'N') = pv_inclusive_tax_flag
2280 GROUP BY
2281 jcdt.source_doc_line_id
2282 , jcdt.currency_code;
2283
2284 --Get the conversion rate for the currency code for the ASBN
2285 CURSOR conv_rate_asbn_csr(cp_cmn_line_id IN NUMBER) IS
2286 SELECT
2287 pha.rate
2288 , pha.currency_code
2289 FROM po_headers_all pha
2290 WHERE pha.po_header_id =
2291 (SELECT po_header_id
2292 FROM jai_cmn_lines
2293 WHERE cmn_line_id = cp_cmn_line_id);
2294
2295
2296 ln_tax_amt NUMBER;
2297 ln_conv_rate NUMBER;
2298 lv_line_currency VARCHAR2(15);
2299 lv_cancel_flag VARCHAR2(1);
2300 lv_func_currency VARCHAR2(15);
2301 ln_org_id NUMBER;
2302 lv_procedure_name VARCHAR2(40):='Get_InAndEx_Tax_Total';
2303 ln_dbg_level NUMBER:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2304 ln_proc_level NUMBER:=FND_LOG.LEVEL_PROCEDURE;
2305 BEGIN
2306 --logging for debug
2307 IF (ln_proc_level >= ln_dbg_level)
2308 THEN
2309 FND_LOG.STRING( ln_proc_level
2310 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.begin'
2311 , 'Enter function'
2312 );
2313 END IF; --l_proc_level>=l_dbg_level
2314
2315 ln_tax_amt := 0;
2316 IF (pv_document_type = 'REQUISITION')
2317 THEN
2318 FOR reqn_tax_amt_rec IN reqn_tax_amt_csr
2319 LOOP
2320 OPEN conv_rate_reqn_csr(reqn_tax_amt_rec.requisition_line_id);
2321 FETCH conv_rate_reqn_csr
2322 INTO
2323 ln_conv_rate
2324 , lv_line_Currency
2325 , lv_cancel_flag
2326 , ln_org_id;
2327 CLOSE conv_rate_reqn_csr;
2328
2329 IF (ln_org_id IS NOT NULL)
2330 THEN
2331 OPEN sob_curr_csr(ln_org_id);
2332 FETCH sob_curr_csr
2333 INTO lv_func_currency;
2334 CLOSE sob_curr_csr;
2335 END IF; --(ln_org_id IS NOT NULL)
2336
2337 IF (NVL(lv_cancel_flag, '$') <> 'Y')
2338 THEN
2339 IF (pn_line_id IS NOT NULL)
2340 THEN
2341 IF (NVL(reqn_tax_amt_rec.currency, '$$$') <> NVL(lv_line_currency, '$$$'))
2342 THEN
2343 IF (ln_conv_rate IS NULL)
2344 THEN
2345 ln_conv_rate := 1;
2346 END IF; --(ln_conv_rate IS NULL)
2347 ELSE
2348 ln_conv_rate := 1;
2349 END IF; --(NVL(reqn_tax_amt_rec.currency, '$$$') <> NVL(lv_line_currency, '$$$'))
2350
2351 ln_tax_amt := NVL(ln_tax_amt, 0) +
2352 ROUND(((reqn_tax_amt_rec.tax_amount) / ln_conv_rate),reqn_tax_amt_rec.rnd_factor);
2353 ELSE
2354 IF (NVL(reqn_tax_amt_rec.currency, '$$$') =
2355 NVL(lv_func_currency, '$$$'))
2356 THEN
2357 ln_tax_amt := NVL(ln_tax_amt, 0) + (reqn_tax_amt_rec.tax_amount);
2358 ELSE
2359 ln_tax_amt := NVL(ln_tax_amt, 0) +
2360 round((reqn_tax_amt_rec.tax_amount * NVL( ln_conv_Rate,1)),reqn_tax_amt_rec.rnd_factor); -- nvl correction made by pramasub #6066485
2361 END IF; --(NVL(reqn_tax_amt_rec.currency, '$$$')=NVL(lv_func_currency, '$$$'))
2362 END IF; --(pn_line_id IS NOT NULL)
2363 END IF; --(NVL(lv_cancel_flag, '$') <> 'Y')
2364 END LOOP; --reqn_tax_amt_rec IN reqn_tax_amt_csr
2365
2366 ELSIF (pv_document_type = 'RECEIPTS')
2367 THEN
2368 FOR r_rcpt_rec IN rcpt_tax_amt_csr
2369 LOOP
2370 OPEN conv_rate_rcpt_csr(r_rcpt_rec.shipment_line_id);
2371 FETCH conv_rate_rcpt_csr
2372 INTO
2373 ln_conv_rate
2374 , lv_line_currency;
2375 CLOSE conv_rate_rcpt_csr;
2376
2377 IF (NVL(r_rcpt_rec.currency, '$$$') <> NVL(lv_line_currency, '$$$'))
2378 THEN
2379 IF (ln_conv_rate IS NULL) THEN
2380 ln_conv_rate := 1;
2381 END IF; --(ln_conv_rate IS NULL)
2382 ELSE
2383 ln_conv_rate := 1;
2384 END IF; --(NVL(r_rcpt_rec.currency, '$$$') <> NVL(lv_line_currency, '$$$'))
2385
2386 ln_tax_amt := NVL(ln_tax_amt, 0) +
2387 ((r_rcpt_rec.tax_amount) / ln_conv_rate);
2388 END LOOP; --(r_rcpt_rec IN rcpt_tax_amt_csr)
2389
2390 ELSIF pv_document_type IN ('RFQ', 'PO', 'BPA')
2391 THEN
2392 OPEN conv_rate_rfq_csr(pn_header_id);
2393 FETCH conv_rate_rfq_csr
2394 INTO
2395 ln_conv_rate
2396 , lv_line_currency;
2397 CLOSE conv_rate_rfq_csr;
2398
2399 FOR po_rfq_rec IN po_rfq_tax_amt_csr
2400 LOOP
2401 IF (NVL(po_rfq_rec.currency, '$$$') <> NVL(lv_line_currency, '$$$'))
2402 THEN
2403 IF (ln_conv_rate IS NULL) THEN
2404 ln_conv_rate := 1;
2405 END IF; --(ln_conv_rate IS NULL)
2406 ELSE
2407 ln_conv_rate := 1;
2408 END IF; --(NVL(po_rfq_rec.currency, '$$$') <> NVL(lv_line_currency, '$$$'))
2409
2410 ln_tax_amt := NVL(ln_tax_amt, 0) +
2411 (ln_conv_rate * (po_rfq_rec.tax_amount));
2412
2413 END LOOP;
2414
2415 ELSIF pv_document_type IN ('SHIPMENT', 'RELEASE', 'PO_LINE')
2416 THEN
2417 OPEN conv_rate_rfq_csr(pn_header_id);
2418 FETCH conv_rate_rfq_csr
2419 INTO
2420 ln_conv_rate
2421 , lv_line_currency;
2422 CLOSE conv_rate_rfq_csr;
2423
2424 IF pv_document_type = 'SHIPMENT'
2425 THEN
2426 ln_tax_amt := 0;
2427
2428 FOR shipments_tax_amt_rec IN shipments_tax_amt_csr
2429 LOOP
2430
2431 IF NVL(shipments_tax_amt_rec.currency, '$$$') <>
2432 NVL(lv_line_currency, '$$$')
2433 THEN
2434 IF ln_conv_rate IS NULL
2435 THEN
2436 ln_conv_rate := 1;
2437 END IF;
2438 ELSE
2439 ln_conv_rate := 1;
2440 END IF;
2441
2442 ln_tax_amt := NVL(ln_tax_amt, 0) +
2443 (ln_conv_rate * (shipments_tax_amt_rec.tax_amount));
2444
2445 END LOOP;
2446
2447 RETURN ln_tax_amt;
2448
2449 ELSIF pv_document_type = 'RELEASE'
2450 THEN
2451
2452 ln_tax_amt := 0;
2453 FOR release_tax_amt_rec IN release_tax_amt_csr
2454 LOOP
2455
2456 IF (NVL(release_tax_amt_rec.currency, '$$$') <> NVL(lv_line_currency, '$$$'))
2457 THEN
2458 IF (ln_conv_rate IS NULL)
2459 THEN
2460 ln_conv_rate := 1;
2461 END IF; --(ln_conv_rate IS NULL)
2462 ELSE
2463 ln_conv_rate := 1;
2464 END IF; --(NVL(release_tax_amt_rec.currency, '$$$') <> NVL(lv_line_currency, '$$$'))
2465
2466 ln_tax_amt := NVL(ln_tax_amt, 0) +
2467 (ln_conv_rate * (release_tax_amt_rec.tax_amount));
2468
2469 END LOOP; --release_tax_amt_rec IN release_tax_amt_csr
2470
2471 RETURN ln_tax_amt;
2472
2473 ELSIF pv_document_type = 'PO_LINE'
2474 THEN
2475 FOR po_line_tax_amt_rec IN po_line_tax_amt_csr
2476 LOOP
2477 IF (NVL(po_line_tax_amt_rec.currency, '$$$') <> NVL(lv_line_currency, '$$$'))
2478 THEN
2479 IF (ln_conv_rate IS NULL)
2480 THEN
2481 ln_conv_rate := 1;
2482 END IF; --(ln_conv_rate IS NULL)
2483 ELSE
2484 ln_conv_rate := 1;
2485 END IF; --(NVL(po_line_tax_amt_rec.currency, '$$$') <> NVL(lv_line_currency, '$$$'))
2486
2487 ln_tax_amt := NVL(ln_tax_amt, 0) +
2488 (ln_conv_rate * (po_line_tax_amt_rec.tax_amount));
2489
2490 END LOOP;
2491
2492 RETURN ln_tax_amt;
2493 END IF;
2494
2495 ELSIF (pv_document_Type = 'INVOICE')
2496 THEN
2497 FOR inv_tax_amt_rec IN inv_tax_amt_csr
2498 LOOP
2499 OPEN conv_rate_inv_csr( pn_header_id
2500 , pn_line_id
2501 );
2502 FETCH conv_rate_inv_csr
2503 INTO
2504 ln_conv_rate
2505 , lv_line_currency;
2506 CLOSE conv_rate_inv_csr;
2507
2508 IF (NVL(inv_tax_amt_rec.currency_code, '$$$') <> NVL(lv_line_currency, '$$$'))
2509 THEN
2510 IF (ln_conv_rate IS NULL)
2511 THEN
2512 ln_conv_rate := 1;
2513 END IF; --(ln_conv_rate IS NULL)
2514 ELSE
2515 ln_conv_rate := 1;
2516 END IF; --(NVL(inv_tax_amt_rec.currency_code, '$$$') <> NVL(lv_line_currency, '$$$'))
2517
2518 ln_tax_amt := NVL(ln_tax_amt, 0) +
2519 ((inv_tax_amt_rec.tax_amount) / ln_conv_rate );
2520 END LOOP; --inv_tax_amt_rec IN inv_tax_amt_csr
2521
2522 RETURN(ln_tax_amt);
2523 ELSIF pv_document_Type = 'ASBN'
2524 THEN
2525 FOR asbn_tax_amt_rec IN asbn_tax_amt_csr(pn_line_id, pv_document_type)
2526 LOOP
2527 OPEN conv_rate_asbn_csr(asbn_tax_amt_rec.source_doc_line_id);
2528 FETCH conv_rate_asbn_csr
2529 INTO
2530 ln_conv_rate
2531 , lv_line_currency;
2532 CLOSE conv_rate_asbn_csr;
2533
2534 IF (NVL(asbn_tax_amt_rec.currency_code, '$$$') <> NVL(lv_line_currency, '$$$'))
2535 THEN
2536 IF (ln_conv_rate IS NULL)
2537 THEN
2538 ln_conv_rate := 1;
2539 END IF; --(ln_conv_rate IS NULL)
2540 ELSE
2541 ln_conv_rate := 1;
2542 END IF; --(NVL(asbn_tax_amt_rec.currency_code, '$$$') <> NVL(lv_line_currency, '$$$'))
2543
2544 ln_tax_amt := NVL(ln_tax_amt, 0) +
2545 ( (asbn_tax_amt_rec.tax_amount) / ln_conv_rate );
2546
2547 END LOOP; --asbn_tax_amt_rec IN asbn_tax_amt_csr(pn_line_id, pv_document_type)
2548 END IF;
2549
2550 --logging for debug
2551 IF (ln_proc_level >= ln_dbg_level)
2552 THEN
2553 FND_LOG.STRING( ln_proc_level
2554 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.begin'
2555 , 'Exit function'
2556 );
2557 END IF; --l_proc_level>=l_dbg_level
2558
2559 RETURN ln_tax_amt;
2560 EXCEPTION
2561 WHEN OTHERS THEN
2562 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2563 THEN
2564 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
2565 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.Other_Exception '
2566 , Sqlcode||Sqlerrm);
2567 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2568 RETURN (-1);
2569 END Get_InAndEx_Tax_Total;
2570
2571 --==========================================================================
2572 -- PROCEDURE NAME:
2573 --
2574 -- Get_Isp_InAndEx_Tax_Total Public
2575 --
2576 -- DESCRIPTION:
2577 --
2578 -- to calculate the inclusive and exclusive tax amount
2579 --
2580 -- PARAMETERS:
2581 -- In: pv_document_type document type
2582 -- pn_header_id header id
2583 -- pn_line_id line id
2584 -- pn_release_id release id
2585 -- pv_inclusive_tax_flag inclusive tax flag
2586 --
2587 -- DESIGN REFERENCES:
2588 -- Inclusive Tax Technical Design.doc
2589 --
2590 -- CHANGE HISTORY:
2591 --
2592 -- 20-NOV-2007 Jason Liu created
2593
2594 FUNCTION Get_Isp_InAndEx_Tax_Total
2595 ( pv_document_type IN VARCHAR2
2596 , pn_header_id IN NUMBER
2597 , pn_line_id IN NUMBER
2598 , pn_release_id IN NUMBER
2599 , pv_inclusive_tax_flag IN VARCHAR2
2600 )
2601 RETURN NUMBER
2602 IS
2603
2604 --Get the RFQ / PO / BPA tax amount for each line location currency code wise
2605 CURSOR po_rfq_tax_amt_csr IS
2606 SELECT
2607 jpt.line_location_id
2608 , jpt.currency
2609 , SUM(jpt.tax_amount) tax_amount
2610 FROM
2611 JAI_PO_TAXES jpt
2612 , jai_cmn_taxes_all jcta
2613 WHERE jpt.po_header_id = pn_header_id
2614 AND (jpt.line_location_id = pn_line_id OR pn_line_id IS NULL)
2615 AND jcta.tax_id = jpt.tax_id
2616 AND NVL(jcta.inclusive_tax_flag, 'N') = pv_inclusive_tax_flag
2617 GROUP BY jpt.line_location_id, jpt.currency;
2618
2619
2620 CURSOR ppo_tax_amt_csr IS
2621 SELECT
2622 jpt.line_location_id
2623 , jpt.currency
2624 , SUM(jpt.tax_amount) tax_amount
2625 FROM
2626 JAI_PO_TAXES jpt
2627 , po_line_locations_all plla
2628 , jai_cmn_taxes_all jcta
2629 WHERE jpt.po_header_id = pn_header_id
2630 AND jpt.po_header_id = plla.po_header_id
2631 AND jpt.po_line_id = plla.po_line_id
2632 AND jpt.line_location_id = plla.line_location_id
2633 AND plla.po_release_id IS NULL
2634 AND jcta.tax_id = jpt.tax_id
2635 AND NVL(jcta.inclusive_tax_flag, 'N') = pv_inclusive_tax_flag
2636 GROUP BY
2637 jpt.line_location_id
2638 , jpt.currency;
2639
2640 CURSOR bpa_tax_amt_csr IS
2641 SELECT
2642 jpt.line_location_id
2643 , jpt.currency
2644 , SUM(jpt.tax_amount) tax_amount
2645 FROM
2646 jai_po_taxes jpt
2647 , po_line_locations_all plla
2648 , jai_cmn_taxes_all jcta
2649 WHERE jpt.po_header_id = pn_header_id
2650 AND jpt.po_header_id = plla.po_header_id
2651 AND jpt.po_line_id = plla.po_line_id
2652 AND jpt.line_location_id = plla.line_location_id
2653 AND plla.po_release_id IS NULL
2654 AND (jpt.po_line_id = pn_line_id OR pn_line_id IS NULL)
2655 AND jcta.tax_id = jpt.tax_id
2656 AND NVL(jcta.inclusive_tax_flag, 'N') = pv_inclusive_tax_flag
2657 GROUP BY
2658 jpt.line_location_id
2659 , jpt.currency;
2660
2661 --Get the conversion rate for the currency code for the PO / RFQ */
2662 CURSOR conv_rate_rfq_csr(pn_header_id IN NUMBER) IS
2663 SELECT
2664 rate
2665 , currency_code
2666 FROM po_headers_all
2667 WHERE po_header_id = pn_header_id;
2668
2669 -- Get the shipments tax amount
2670 CURSOR shipments_tax_amt_csr IS
2671 SELECT
2672 jpt.currency
2673 , SUM(jpt.tax_amount) tax_amount
2674 FROM
2675 jai_po_taxes jpt
2676 , jai_cmn_taxes_all jcta
2677 WHERE jpt.line_location_id = pn_line_id
2678 AND jcta.tax_id = jpt.tax_id
2679 AND NVL(jcta.inclusive_tax_flag, 'N') = pv_inclusive_tax_flag
2680 GROUP BY jpt.currency;
2681
2682 -- Get the release tax amount
2683 CURSOR release_tax_amt_csr IS
2684 SELECT
2685 jpt.currency
2686 , SUM(jpt.tax_amount) tax_amount
2687 FROM
2688 jai_po_taxes jpt
2689 , po_line_locations_all plla
2690 , jai_cmn_taxes_all jcta
2691 WHERE plla.po_header_id = pn_header_id
2692 AND plla.po_release_id = pn_release_id
2693 AND (pn_line_id IS NULL OR plla.po_line_id = pn_line_id)
2694 AND jpt.po_header_id = plla.po_header_id
2695 AND jpt.po_line_id = plla.po_line_id
2696 AND jpt.line_location_id = plla.line_location_id
2697 AND jcta.tax_id = jpt.tax_id
2698 AND NVL(jcta.inclusive_tax_flag, 'N') = pv_inclusive_tax_flag
2699 GROUP BY jpt.currency;
2700
2701 -- Get the PO_lINE tax amount
2702 CURSOR po_line_tax_amt_csr IS
2703 SELECT
2704 jpt.currency
2705 , SUM(jpt.tax_amount) tax_amount
2706 FROM
2707 jai_po_taxes jpt
2708 , jai_cmn_taxes_all jcta
2709 WHERE jpt.po_header_id = pn_header_id
2710 AND jpt.po_line_id = pn_line_id
2711 AND jcta.tax_id = jpt.tax_id
2712 AND NVL(jcta.inclusive_tax_flag, 'N') = pv_inclusive_tax_flag
2713 GROUP BY jpt.currency;
2714
2715 --Get the RELEASE_TOT tax amount
2716 CURSOR release_tot_tax_amt_csr IS
2717 SELECT
2718 jpt.currency
2719 , SUM(jpt.tax_amount) tax_amount
2720 FROM
2721 JAI_PO_TAXES jpt
2722 , po_line_locations_all plla
2723 , po_releases_all pra
2724 , jai_cmn_taxes_all jcta
2725 WHERE plla.po_header_id = pn_header_id
2726 AND plla.po_release_id IS NOT NULL
2727 AND jpt.po_header_id = plla.po_header_id
2728 AND jpt.po_line_id = plla.po_line_id
2729 AND jpt.line_location_id = plla.line_location_id
2730 AND pra.po_release_id = plla.po_release_id
2731 AND pra.approved_flag = 'Y'
2732 AND jcta.tax_id = jpt.tax_id
2733 AND NVL(jcta.inclusive_tax_flag, 'N') = pv_inclusive_tax_flag
2734 GROUP BY jpt.currency;
2735
2736
2737 ln_tax_amt NUMBER;
2738 lv_curr_tax_amt NUMBER;
2739 ln_conv_rate NUMBER;
2740 lv_line_Currency VARCHAR2(15);
2741 lv_cancel_flag VARCHAR2(1);
2742 lv_func_currency VARCHAR2(15);
2743 ln_org_id NUMBER;
2744 lv_procedure_name VARCHAR2(40):='Get_InAndEx_Tax_Total';
2745 ln_dbg_level NUMBER:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2746 ln_proc_level NUMBER:=FND_LOG.LEVEL_PROCEDURE;
2747 BEGIN
2748 --logging for debug
2749 IF (ln_proc_level >= ln_dbg_level)
2750 THEN
2751 FND_LOG.STRING( ln_proc_level
2752 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.begin'
2753 , 'Enter function'
2754 );
2755 END IF; --l_proc_level>=l_dbg_level
2756
2757 ln_tax_amt := 0;
2758
2759 IF (pv_document_type = 'GBA_ALL')
2760 THEN
2761 FOR r_po_rec IN
2762 (SELECT po_header_id
2763 FROM po_lines_all
2764 WHERE from_header_id = pn_header_id)
2765 LOOP
2766 ln_tax_amt := ln_tax_amt + Get_Isp_InAndEx_Tax_Total('PO',r_po_rec.po_header_id,null,null, pv_inclusive_tax_flag);
2767 END LOOP; --r_po_rec IN
2768
2769 RETURN ln_tax_amt;
2770 END IF; --(pv_document_type = 'GBA_ALL')
2771
2772 IF (pv_document_type = 'GBA')
2773 THEN
2774 IF (pn_line_id IS NOT NULL)
2775 THEN
2776 FOR r_po_rec IN (SELECT
2777 po_header_id
2778 , po_line_id
2779 FROM po_lines_all
2780 WHERE po_header_id = pn_header_id
2781 AND po_line_id = pn_line_id)
2782 LOOP
2783 ln_tax_amt := ln_tax_amt + Get_Isp_InAndEx_Tax_Total('PO_LINE',r_po_rec.po_header_id,r_po_rec.po_line_id,null,pv_inclusive_tax_flag);
2784 END LOOP; -- r_po_rec IN
2785 ELSE
2786 FOR r_po_rec IN (SELECT po_header_id
2787 FROM po_lines_all
2788 WHERE po_header_id = pn_header_id)
2789 LOOP
2790 ln_tax_amt := ln_tax_amt + Get_Isp_InAndEx_Tax_Total('PO',r_po_rec.po_header_id,null,null,pv_inclusive_tax_flag);
2791 END LOOP; --r_po_rec IN
2792 END IF; --(pn_line_id IS NOT NULL)
2793 RETURN ln_tax_amt;
2794 END IF; --(pv_document_type = 'GBA')
2795
2796 IF (pv_document_type IN ('RFQ', 'PO'))
2797 THEN
2798 FOR po_rfq_tax_amt_rec IN po_rfq_tax_amt_csr
2799 LOOP
2800 OPEN conv_rate_rfq_csr(pn_header_id);
2801 FETCH conv_rate_rfq_csr
2802 INTO
2803 ln_conv_rate
2804 , lv_line_currency;
2805 CLOSE conv_rate_rfq_csr;
2806
2807 IF (NVL(po_rfq_tax_amt_rec.currency, '$$$') <> NVL(lv_line_currency, '$$$'))
2808 THEN
2809 IF (ln_conv_rate IS NULL)
2810 THEN
2811 ln_conv_rate := 1;
2812 END IF; --(ln_conv_rate IS NULL)
2813 ELSE
2814 ln_conv_rate := 1;
2815 END IF; --(NVL(r_rfq_po_rec.currency, '$$$') <> NVL(lv_line_currency, '$$$'))
2816
2817 ln_tax_amt := NVL(ln_tax_amt, 0) +
2818 ((po_rfq_tax_amt_rec.tax_amount) / ln_conv_rate);
2819
2820 END LOOP; -- po_rfq_tax_amt_rec IN po_rfq_tax_amt_csr
2821
2822 ELSIF pv_document_type = 'PPO'
2823 THEN
2824 FOR ppo_tax_amt_rec IN ppo_tax_amt_csr
2825 LOOP
2826 OPEN conv_rate_rfq_csr(pn_header_id);
2827 FETCH conv_rate_rfq_csr
2828 INTO
2829 ln_conv_rate
2830 , lv_line_currency;
2831 CLOSE conv_rate_rfq_csr;
2832
2833 IF (NVL(ppo_tax_amt_rec.currency, '$$$') <> NVL(lv_line_currency, '$$$'))
2834 THEN
2835 IF (ln_conv_rate IS NULL)
2836 THEN
2837 ln_conv_rate := 1;
2838 END IF; --(ln_conv_rate IS NULL)
2839 ELSE
2840 ln_conv_rate := 1;
2841 END IF; --(NVL(ppo_tax_amt_rec.currency, '$$$') <> NVL(lv_line_currency, '$$$'))
2842
2843 ln_tax_amt := NVL(ln_tax_amt, 0) +
2844 ((ppo_tax_amt_rec.tax_amount) / ln_conv_rate);
2845 END LOOP;
2846
2847 ELSIF pv_document_type = 'BPA'
2848 THEN
2849 FOR bpa_tax_amt_rec IN bpa_tax_amt_csr
2850 LOOP
2851 OPEN conv_rate_rfq_csr(pn_header_id);
2852 FETCH conv_rate_rfq_csr
2853 INTO
2854 ln_conv_rate
2855 , lv_line_currency;
2856 CLOSE conv_rate_rfq_csr;
2857
2858 IF (NVL(bpa_tax_amt_rec.currency, '$$$') <> NVL(lv_line_currency, '$$$'))
2859 THEN
2860 IF (ln_conv_rate IS NULL)
2861 THEN
2862 ln_conv_rate := 1;
2863 END IF; --(ln_conv_rate IS NULL)
2864 ELSE
2865 ln_conv_rate := 1;
2866 END IF; --(NVL(bpa_tax_amt_rec.currency, '$$$') <> NVL(lv_line_currency, '$$$'))
2867
2868 ln_tax_amt := NVL(ln_tax_amt, 0) +
2869 ((bpa_tax_amt_rec.tax_amount) / ln_conv_rate);
2870 END LOOP; --bpa_tax_amt_rec IN bpa_tax_amt_csr
2871
2872 ELSIF pv_document_type IN ('SHIPMENT', 'RELEASE', 'PO_LINE', 'RELEASE_TOT')
2873 THEN
2874 IF (pv_document_type = 'SHIPMENT')
2875 THEN
2876 ln_tax_amt := 0;
2877
2878 FOR shipments_tax_amt_rec IN shipments_tax_amt_csr
2879 LOOP
2880 OPEN conv_rate_rfq_csr(pn_header_id);
2881 FETCH conv_rate_rfq_csr
2882 INTO
2883 ln_conv_rate
2884 , lv_line_currency;
2885 CLOSE conv_rate_rfq_csr;
2886
2887 IF (NVL(shipments_tax_amt_rec.currency, '$$$') <> NVL(lv_line_currency, '$$$'))
2888 THEN
2889 IF (ln_conv_rate IS NULL)
2890 THEN
2891 ln_conv_rate := 1;
2892 END IF; --(ln_conv_rate IS NULL)
2893 ELSE
2894 ln_conv_rate := 1;
2895 END IF; --(NVL(shipments_tax_amt_rec.currency, '$$$') <> NVL(lv_line_currency, '$$$'))
2896
2897 ln_tax_amt := NVL(ln_tax_amt, 0) +
2898 ((shipments_tax_amt_rec.tax_amount) / ln_conv_rate);
2899
2900 END LOOP;
2901
2902 RETURN ln_tax_amt;
2903
2904 ELSIF pv_document_type = 'RELEASE'
2905 THEN
2906 ln_tax_amt := 0;
2907
2908 FOR release_tax_amt_rec IN release_tax_amt_csr
2909 LOOP
2910 OPEN conv_rate_rfq_csr(pn_header_id);
2911 FETCH conv_rate_rfq_csr
2912 INTO
2913 ln_conv_rate
2914 , lv_line_currency;
2915 CLOSE conv_rate_rfq_csr;
2916
2917 IF (NVL(release_tax_amt_rec.currency, '$$$') <> NVL(lv_line_currency, '$$$'))
2918 THEN
2919 IF (ln_conv_rate IS NULL)
2920 THEN
2921 ln_conv_rate := 1;
2922 END IF; --(ln_conv_rate IS NULL)
2923 ELSE
2924 ln_conv_rate := 1;
2925 END IF; --(NVL(release_tax_amt_rec.currency, '$$$') <> NVL(lv_line_currency, '$$$'))
2926
2927 ln_tax_amt := NVL(ln_tax_amt, 0) +
2928 ((release_tax_amt_rec.tax_amount) / ln_conv_rate);
2929
2930 END LOOP;
2931
2932 RETURN(ln_tax_amt);
2933 ELSIF pv_document_type = 'PO_LINE'
2934 THEN
2935 FOR po_line_tax_amt_rec IN po_line_tax_amt_csr
2936 LOOP
2937 OPEN conv_rate_rfq_csr(pn_header_id);
2938 FETCH conv_rate_rfq_csr
2939 INTO
2940 ln_conv_rate
2941 , lv_line_currency;
2942 CLOSE conv_rate_rfq_csr;
2943
2944 IF (NVL(po_line_tax_amt_rec.currency, '$$$') <> NVL(lv_line_currency, '$$$'))
2945 THEN
2946 IF (ln_conv_rate IS NULL)
2947 THEN
2948 ln_conv_rate := 1;
2949 END IF; --(ln_conv_rate IS NULL)
2950 ELSE
2951 ln_conv_rate := 1;
2952 END IF; --(NVL(po_line_tax_amt_rec.currency, '$$$') <> NVL(lv_line_currency, '$$$'))
2953
2954 ln_tax_amt := NVL(ln_tax_amt, 0) +
2955 ((po_line_tax_amt_rec.tax_amount) / ln_conv_rate);
2956
2957 END LOOP;
2958
2959 RETURN ln_tax_amt;
2960
2961 ELSIF pv_document_Type = 'RELEASE_TOT'
2962 THEN
2963 ln_tax_amt := 0;
2964
2965 FOR release_tot_tax_amt_rec IN release_tot_tax_amt_csr
2966 LOOP
2967 OPEN conv_rate_rfq_csr(pn_header_id);
2968 FETCH conv_rate_rfq_csr
2969 INTO
2970 ln_conv_rate
2971 , lv_line_currency;
2972 CLOSE conv_rate_rfq_csr;
2973
2974 IF (NVL(release_tot_tax_amt_rec.currency, '$$$') <> NVL(lv_line_currency, '$$$'))
2975 THEN
2976 IF (ln_conv_rate IS NULL)
2977 THEN
2978 ln_conv_rate := 1;
2979 END IF; --(ln_conv_rate IS NULL)
2980 ELSE
2981 ln_conv_rate := 1;
2982 END IF; --(NVL(release_tot_tax_amt_rec.currency, '$$$') <> NVL(lv_line_currency, '$$$'))
2983
2984 ln_tax_amt := NVL(ln_tax_amt, 0) +
2985 ((release_tot_tax_amt_rec.tax_amount) / ln_conv_rate);
2986
2987 END LOOP;
2988
2989 RETURN ln_tax_amt;
2990 END IF; --(pv_document_type = 'SHIPMENT')
2991 END IF; --(pv_document_type IN ('RFQ', 'PO'))
2992
2993 --logging for debug
2994 IF (ln_proc_level >= ln_dbg_level)
2995 THEN
2996 FND_LOG.STRING( ln_proc_level
2997 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.begin'
2998 , 'Exit function'
2999 );
3000 END IF; --l_proc_level>=l_dbg_level
3001
3002 RETURN ln_tax_amt;
3003 EXCEPTION
3004 WHEN OTHERS THEN
3005 IF(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3006 THEN
3007 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
3008 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.Other_Exception '
3009 , Sqlcode||Sqlerrm);
3010 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3011 RETURN (-1);
3012 END Get_Isp_InAndEx_Tax_Total;
3013
3014 END JAI_PO_HOOK_PKG;