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