[Home] [Help]
PACKAGE BODY: APPS.JAI_PO_CMN_PKG
Source
1 PACKAGE BODY jai_po_cmn_pkg AS
2 /* $Header: jai_po_cmn.plb 120.7.12010000.2 2009/03/03 12:47:39 mbremkum ship $ */
3 v_conv_rate NUMBER;
4 v_inv_org_id NUMBER;
5
6 /* --------------------------------------------------------------------------------------
7 Filename:
8
9 Change History:
10
11 Date Bug Remarks
12 --------- ---------- -------------------------------------------------------------
13 08-Jun-2005 Version 116.2 jai_po_cmn -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
14 as required for CASE COMPLAINCE.
15
16 13-Jun-2005 File Version: 116.3
17 Ramananda for bug#4428980. Removal of SQL LITERALs is done
18
19 06-Jul-2005 rallamse for bug# PADDR Elimination
20 1. Removed the procedures query_locator_for_release, query_locator_for_hdr and query_locator_for_line
21 from both specification and body.
22
23 15-feb-2007 vkaranam for Bug #4601436,File Version 120.4
24 1.Forward porting the change sin 11i bug 4562363(autocreate req to spo fails due to localization trigger)
25
26 16-Jan-2008 Kevin Cheng for Retroactive Price Enhancement
27 Add parameter for process_release_shipment procedure and procedures called in it.
28
29 --------------------------------------------------------------------------------------*/
30
31 /* PROCEDURE insert_accrual_reconcile
32 (p_transaction_id number,
33 p_po_line_location_id number,
34 p_po_distribution_id number,
35 p_shipment_line_id number,
36 p_organization_id number,
37 p_transaction_date date,
38 p_transaction_amount number,
39 p_accrual_account_id number
40 )
41 IS
42
43 v_operating_unit org_organization_definitions.operating_unit % type;
44 v_line_num po_lines_all.line_num % type;
45 v_item_id rcv_shipment_lines.item_id % type;
46 v_vendor_name po_vendors.vendor_name % type;
47 v_receipt_num rcv_shipment_headers.receipt_num % type;
48 v_po_num po_headers_all.segment1 % type;
49 v_unit_price number;
50 v_primary_uom mtl_system_items.primary_unit_of_measure % type;
51
52 CURSOR org_cur IS
53 SELECT operating_unit
54 FROM org_organization_definitions
55 WHERE organization_id = p_organization_id;
56
57 CURSOR rcv_cur IS
58 SELECT source_document_code,
59 unit_of_measure,
60 shipment_header_id,
61 po_header_id,
62 po_line_id,
63 po_unit_price,
64 requisition_line_id,
65 vendor_id,
66 quantity
67 FROM rcv_transactions
68 WHERE transaction_id = p_transaction_id;
69
70 v_rcv_rec rcv_cur % ROWTYPE;
71
72 CURSOR line_cur IS
73 SELECT line_num
74 FROM po_lines_all
75 WHERE po_line_id = v_rcv_rec.po_line_id;
76
77 CURSOR ven_cur IS
78 SELECT vendor_name
79 FROM po_vendors
80 WHERE vendor_id = v_rcv_rec.vendor_id;
81
82 Cursor ship_cur IS
83 SELECT item_id
84 FROM rcv_shipment_lines
85 WHERE shipment_line_id = p_shipment_line_id;
86
87 CURSOR head_rec IS
88 SELECT receipt_num
89 FROM rcv_shipment_headers
90 WHERE shipment_header_id = v_rcv_rec.shipment_header_id;
91
92 CURSOR po_cur IS
93 SELECT segment1
94 FROM po_headers_all
95 WHERE po_header_id = v_rcv_rec.po_header_id;
96
97 CURSOR uom_cur IS
98 SELECT primary_unit_of_measure
99 FROM mtl_system_items
100 WHERE organization_id = p_organization_id
101 AND inventory_item_id = v_item_id;
102
103 lv_accrual_code po_accrual_reconcile_temp_all.accrual_code%type ;
104 lv_po_trans_type po_accrual_reconcile_temp_all.po_transaction_type%type ;
105 lv_destination_type_code po_accrual_reconcile_temp_all.destination_type_code%type ;
106 BEGIN
107
108 OPEN rcv_cur;
109 FETCH rcv_cur INTO v_rcv_rec;
110 CLOSE rcv_cur;
111
112 OPEN org_cur;
113 FETCH org_cur INTO v_operating_unit;
114 CLOSE org_cur;
115
116 OPEN ship_cur;
117 FETCH ship_cur INTO v_item_id;
118 CLOSE ship_cur;
119
120 OPEN head_rec;
121 FETCH head_rec INTO v_receipt_num;
122 CLOSE head_rec;
123
124 OPEN uom_cur;
125 FETCH uom_cur INTO v_primary_uom;
126 CLOSE uom_cur;
127
128 IF v_rcv_rec.source_document_code = 'PO'
129 THEN
130
131 OPEN line_cur;
132 FETCH line_cur INTO v_line_num;
133 CLOSE line_cur;
134
135 OPEN ven_cur;
136 FETCH ven_cur INTO v_vendor_name;
137 CLOSE ven_cur;
138
139 OPEN po_cur;
140 FETCH po_cur INTO v_po_num;
141 CLOSE po_cur;
142
143 v_unit_price := v_rcv_rec.po_unit_price;
144
145 ELSIF v_rcv_rec.source_document_code = 'INVENTORY'
146 THEN
147 For price_rec IN (SELECT list_price_per_unit price
148 FROM mtl_system_items
149 WHERE inventory_item_id = v_item_id
150 AND organization_id = p_organization_id)
151 LOOP
152 v_unit_price := price_rec.price;
153 END LOOP;
154 ELSIF v_rcv_rec.source_document_code = 'REQ'
155 THEN
156 For price_rec IN (SELECT unit_price
157 FROM po_requisition_lines_all
158 WHERE requisition_line_id = v_rcv_rec.requisition_line_id)
159 LOOP
160 v_unit_price := price_rec.unit_price;
161 END LOOP;
162 END IF;
163
164 lv_accrual_code := 'Receive' ;
165 lv_po_trans_type := 'RECEIVE' ;
166 lv_destination_type_code := 'INVENTORY';
167
168 INSERT INTO po_accrual_reconcile_temp_all
169 (transaction_date,
170 inventory_item_id,
171 transaction_quantity,
172 po_header_id,
173 po_line_num,
174 po_line_id,
175 vendor_name,
176 transaction_organization_id,
177 vendor_id,
178 item_master_organization_id,
179 accrual_account_id,
180 accrual_code,
181 po_transaction_type,
182 receipt_num,
183 po_transaction_id,
184 po_unit_of_measure,
185 primary_unit_of_measure,
186 net_po_line_quantity,
187 po_num,
188 po_distribution_id,
189 transaction_unit_price,
190 avg_receipt_price,
191 transaction_amount,
192 transaction_source_code,
193 write_off_flag,
194 destination_type_code,
195 net_po_line_amount,
196 aging_date,
197 org_id,
198 line_location_id)
199 VALUES (p_transaction_date,
200 v_item_id,
201 -v_rcv_rec.quantity,
202 v_rcv_rec.po_header_id,
203 v_line_num,
204 v_rcv_rec.po_line_id,
205 v_vendor_name,
206 p_organization_id,
207 v_rcv_rec.vendor_id,
208 v_operating_unit,
209 p_accrual_account_id,
210 lv_accrual_code, --'Receive',
211 lv_po_trans_type, --'RECEIVE', -- Modified by Ramananda for removal of SQL LITERALs :bug#4428980
212 v_receipt_num,
213 p_transaction_id,
214 v_rcv_rec.unit_of_measure,
215 v_primary_uom,
216 - (v_rcv_rec.quantity * 2),
217 v_po_num,
218 p_po_distribution_id,
219 v_unit_price,
220 -(v_unit_price / 2),
221 -p_transaction_amount,
222 v_rcv_rec.source_document_code,
223 'N',
224 lv_destination_type_code, --'INVENTORY',
225 -p_transaction_amount,
226 p_transaction_date,
227 v_operating_unit,
228 p_po_line_location_id);
229 END insert_accrual_reconcile;
230 */
231
232 /*------------------------------------------------------------------------------------------------------------*/
233 PROCEDURE insert_line
234 ( v_code IN VARCHAR2,
235 v_line_loc_id IN NUMBER,
236 v_po_hdr_id IN NUMBER,
237 v_po_line_id IN NUMBER,
238 v_cre_dt IN DATE,
239 v_cre_by IN NUMBER,
240 v_last_upd_dt IN DATE,
241 v_last_upd_by IN NUMBER,
242 v_last_upd_login IN NUMBER,
243 flag IN VARCHAR2,
244 v_service_type_code IN VARCHAR2 DEFAULT NULL)
245 IS
246
247 v_seq_val NUMBER;
248 v_tax_amt NUMBER;
249 v_total_amt NUMBER;
250
251 ------------------------------>
252
253 CURSOR Fetch_Focus_Id IS SELECT JAI_PO_LINE_LOCATIONS_S.NEXTVAL
254 FROM Dual;
255
256 ------------------------------>
257 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_po_cmn_pkg.insert_line';
258 BEGIN
259
260 OPEN Fetch_Focus_Id;
261 FETCH Fetch_Focus_Id INTO v_seq_val;
262 CLOSE Fetch_Focus_Id;
263
264 IF v_code IN ( 'CATALOG', 'BLANKET' ) THEN
265 v_tax_amt := NULL;
266 v_total_amt := NULL;
267 ELSE
268 v_tax_amt := 0; -- Init first to 0
269 v_total_amt := 0; -- ------"--------
270 END IF;
271
272 IF flag = 'I' THEN
273
274 INSERT INTO JAI_PO_LINE_LOCATIONS( Line_Focus_Id, Line_Location_Id, Po_Line_Id, Po_Header_Id,
275 Tax_Modified_Flag, Tax_Amount, Total_Amount,
276 Creation_Date, Created_By, Last_Update_Date, Last_Updated_By,
277 Last_Update_Login,Service_type_code )
278 VALUES
279 ( v_seq_val, v_line_loc_id , v_po_line_id, v_po_hdr_id,
280 'N', v_tax_amt, v_total_amt,
281 v_cre_dt, v_cre_by, v_last_upd_dt, v_last_upd_by,
282 v_last_upd_login,v_service_type_code );
283
284 ELSIF flag = 'U' THEN
285
286 UPDATE JAI_PO_LINE_LOCATIONS
287 SET Tax_Modified_flag = 'N',
288 Tax_Amount = 0,
289 Total_Amount = 0,
290 Last_Update_Date = v_last_upd_dt,
291 Last_Updated_By = v_last_upd_by,
292 Last_Update_Login = v_last_upd_login
293 WHERE Line_Location_Id = v_line_Loc_id AND
294 Po_Line_Id = v_po_line_id AND
295 Po_Header_Id = v_po_hdr_id;
296
297 END IF;
298 EXCEPTION
299 WHEN OTHERS THEN
300 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
301 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
302 app_exception.raise_exception;
303 END insert_line;
304 /*------------------------------------------------------------------------------------------------------------*/
305 FUNCTION Ja_In_Po_Get_Func_Curr( p_po_header_id IN NUMBER ) RETURN VARCHAR2
306
307 IS
308
309 v_set_of_books_id NUMBER;
310 v_func_curr Gl_Sets_Of_Books.Currency_Code%TYPE;
311 v_location_id NUMBER;
312
313 CURSOR Get_Inv_Org_Id_Cur IS SELECT Inventory_Organization_Id
314 FROM Hr_Locations
315 WHERE Location_Id = v_location_id;
316 /* Bug 5243532. Added by Lakshmi Gopalsami
317 * Removed the cursors Get_Set_Of_Book_Cur and
318 * Get_Func_Curr_Cur and implemented caching logic
319 * for getting SOB and SOB curr.
320 */
321
322 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_po_cmn_pkg.ja_in_po_get_func_curr';
323
324 /* Bug 5243532. Added by Lakshmi Gopalsami
325 * Defined variable for implementing caching logic.
326 */
327 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
328 /*------------------------------------------------------------------------------------------------------------*/
329 FUNCTION Ja_In_Po_Get_Loc_Id( p_po_header_id IN NUMBER ) RETURN NUMBER IS
330
331 CURSOR Fetch_Location_Id_Cur IS SELECT Ship_To_Location_Id
332 FROM Po_Headers_All
333 WHERE Po_Header_Id = p_po_header_id;
334
335 v_location_id NUMBER;
336
337 BEGIN
338
339 OPEN Fetch_Location_Id_Cur;
340 FETCH Fetch_Location_Id_Cur INTO v_location_id;
341 CLOSE Fetch_Location_Id_Cur;
342
343 RETURN( v_location_id );
344
345
346 END Ja_In_Po_Get_Loc_Id;
347
348 BEGIN
349
350 v_location_id := ja_in_po_get_loc_id( p_po_header_id );
351
352 OPEN Get_Inv_Org_Id_Cur;
353 FETCH Get_Inv_Org_Id_Cur INTO v_inv_org_id;
354 CLOSE Get_Inv_Org_Id_Cur;
355
356 IF v_inv_org_id IS NULL THEN --added bby vkaranam for Bug#4601436
357 RAISE_APPLICATION_ERROR(-20121,'No Inventory Org is associated to the Location with Id:'||v_location_id);
358 END IF ;
359
360 /* Bug 5243532. Added by Lakshmi Gopalsami
361 * Removed cursors Get_Set_Of_Book_Cur and Get_Func_Curr_Cur
362 * and implemented caching logic to get the sob and sob currency.
363 */
364 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
365 (p_org_id => v_inv_org_id );
366 v_set_of_books_id := l_func_curr_det.ledger_id;
367 v_func_curr := l_func_curr_det.currency_code;
368
369 RETURN( v_func_curr );
370
371 EXCEPTION
372 WHEN OTHERS THEN
373 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
374 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
375 app_exception.raise_exception;
376 END Ja_In_Po_Get_Func_Curr;
377
378 /*------------------------------------------------------------------------------------------------------------*/
379 FUNCTION Ja_In_Po_Assessable_Val_Conv( p_po_header_id IN NUMBER,
380 p_assessable_val IN NUMBER,
381 p_func_curr IN VARCHAR2,
382 p_doc_curr IN VARCHAR2,
383 /* Bug 5096787. Added by Lakshmi Gopalsami */
384 p_rate IN NUMBER DEFAULT NULL,
385 p_rate_date IN DATE DEFAULT NULL,
386 p_rate_type IN VARCHAR2 DEFAULT NULL
387 )
388 RETURN NUMBER IS
389
390 v_rate_type VARCHAR2(30);
391 v_rate_date DATE;
392 v_rate NUMBER;
393 v_bookid NUMBER;
394 v_org_id NUMBER;
395
396 /* Bug 5243532. Added by Lakshmi Gopalsami
397 * Removed cursor Fetch_SET_Of_Books_Id_Cur
398 * and used caching logic for getting SOB
399 */
400
401 CURSOR Fetch_Curr_Details_Cur IS
402 SELECT Rate, Rate_Date, Rate_Type
403 FROM Po_Headers_All
404 WHERE Po_Header_Id = p_po_header_id;
405
406 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_po_cmn_pkg.ja_in_po_assessable_val_conv';
407
408 /* Bug 5243532. Added by Lakshmi Gopalsami
409 * Defined variable for implementing caching logic.
410 */
411 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
412
413 FUNCTION Ja_In_Po_Get_Org_Id RETURN NUMBER IS
414
415 CURSOR Fetch_Org_Id_Cur IS SELECT NVL( Org_Id, -99 )
416 FROM Po_Headers_All
417 WHERE Po_Header_Id = p_po_header_id;
418
419 v_org_id NUMBER;
420
421 BEGIN
422 OPEN Fetch_Org_Id_Cur;
423 FETCH Fetch_Org_Id_Cur INTO v_org_id;
424 CLOSE Fetch_Org_Id_Cur;
425
426 RETURN( v_org_id );
427 END Ja_In_Po_Get_Org_Id;
428
429 BEGIN
430 IF p_func_curr <> p_doc_curr OR p_doc_curr IS NOT NULL THEN
431 OPEN Fetch_Curr_Details_Cur;
432 FETCH Fetch_Curr_Details_Cur INTO v_rate, v_rate_date, v_rate_type;
433 CLOSE Fetch_Curr_Details_Cur;
434 IF v_rate_type = 'User' THEN
435 v_conv_rate := 1/v_rate;
436 ELSE
437 /* Bug 5243532. Added by Lakshmi Gopalsami
438 * Removed cursor Fetch_SET_Of_Books_Id_Cur
439 * and used caching logic for getting SOB
440 */
441 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
442 (p_org_id => v_inv_org_id );
443 v_bookid := l_func_curr_det.ledger_id;
444 v_conv_rate := jai_cmn_utils_pkg.currency_conversion
445 ( v_bookid, p_doc_curr, v_rate_date, v_rate_type, 1 );
446 v_conv_rate := 1/v_conv_rate;
447 END IF;
448 ELSE
449 v_conv_rate := 1;
450 END IF;
451 RETURN( NVL( p_assessable_val * v_conv_rate, 0 ) );
452 EXCEPTION
453 WHEN OTHERS THEN
454 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
455 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
456 app_exception.raise_exception;
457 END Ja_In_Po_Assessable_Val_Conv;
458 /*------------------------------------------------------------------------------------------------------------*/
459 PROCEDURE Ja_In_Po_Func_Curr( p_po_header_id IN NUMBER,
460 p_assessable_val IN OUT NOCOPY NUMBER,
461 p_doc_curr IN VARCHAR2,
462 p_conv_rate IN OUT NOCOPY NUMBER,
463 /* Bug 5096787. Added by Lakshmi Gopalsami */
464 p_rate IN NUMBER DEFAULT NULL,
465 p_rate_date IN DATE DEFAULT NULL,
466 p_rate_type IN VARCHAR2 DEFAULT NULL
467 ) IS
468 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_po_cmn_pkg.ja_in_po_func_curr';
469 BEGIN
470
471 p_assessable_val := jai_po_cmn_pkg.ja_in_po_assessable_val_conv
472 ( p_po_header_id, p_assessable_val,
473 jai_po_cmn_pkg.ja_in_po_get_func_curr( p_po_header_id ),
474 p_doc_curr
475 );
476 p_conv_rate := v_conv_rate;
477 EXCEPTION
478 WHEN OTHERS THEN
479 p_assessable_val := null;
480 p_conv_rate := null;
481 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
482 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
483 app_exception.raise_exception;
484 END Ja_In_Po_Func_Curr;
485
486 /*------------------------------------------------------------------------------------------------------------*/
487 PROCEDURE locate_source_line
488 (
489 p_header_id IN NUMBER,
490 p_line_num IN NUMBER,
491 p_line_quantity IN NUMBER,
492 p_po_line_id OUT NOCOPY NUMBER,
493 p_line_location_id OUT NOCOPY NUMBER,
494 p_line_id NUMBER DEFAULT NULL
495 ) IS
496 -- This procedure is used to find out the line_location_id from which the taxes should be defaulted in case
497 -- there are multiple price break lines for the specified p_line_id or p_line_num of p_header_id
498
499 i NUMBER := 1;
500 v_po_line_id NUMBER;
501 v_cum_flag Po_Lines_All.Price_Break_Lookup_Code % TYPE;
502 v_quantity NUMBER; -- := p_line_quantity; --Ramananda for File.Sql.35
503 v_qty NUMBER;
504 v_count NUMBER;
505
506 TYPE v_Llid_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
507 TYPE v_Qty_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
508
509 Llid_tab v_Llid_tab;
510 Qty_tab v_Qty_Tab;
511
512 CURSOR Fetch_Qty_Cur IS
513 SELECT Quantity, Line_Location_Id
514 FROM Po_Line_Locations_All
515 WHERE Po_Header_Id = p_header_id
516 AND Po_Line_Id = v_po_line_id
517 and SYSDATE between nvl(start_date, SYSDATE) and nvl(end_date, SYSDATE) -- cbabu for Bug# 2740918
518 ORDER BY Quantity;
519
520 CURSOR Fetch_Line_Id_Cur IS
521 SELECT Po_Line_Id, NVL( Price_Break_Lookup_Code, 'NC' ) Price_Break_Lookup_Code
522 FROM Po_Lines_All
523 WHERE Po_Header_Id = p_header_id
524 AND Line_Num = p_line_num;
525
526 CURSOR Fetch_Line1_Id_Cur IS
527 SELECT NVL( Price_Break_Lookup_Code, 'NC' ) Price_Break_Lookup_Code
528 FROM Po_Lines_All
529 WHERE Po_Line_Id = p_line_id;
530
531 CURSOR Fetch_Cum_Qty_Cur IS
532 SELECT SUM( Quantity )
533 FROM Po_Line_Locations_All
534 WHERE Po_Line_Id = v_po_line_id;
535
536 CURSOR Chk_Line_Loc_Cur IS
537 SELECT COUNT( * )
538 FROM Po_Line_Locations_All
539 WHERE Po_Line_Id = v_po_line_id;
540
541 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_po_cmn_pkg.locate_source_line';
542
543 BEGIN
544
545 /*----------------------------------------------------------------------------------------------------------
546 CHANGE HISTORY for ja_in_locate_line_p.sql
547 S.No DD/MM/YYYY Author and Details
548 ------------------------------------------------------------------------------------------------------------
549 1 06/02/2003 Vijay Shankar for Bug# 2740918, Version# 615.1
550 When multiple price break lines are there for quotation with different effectivity dates, then taxes
551 are not picked up properly, this is fixed by adding a condition in the where clause
552 ----------------------------------------------------------------------------------------------------------*/
553 v_quantity := p_line_quantity; --Ramananda for File.Sql.35
554
555 IF NVL( p_line_id, -999 ) = -999 THEN
556 OPEN Fetch_Line_Id_Cur;
557 FETCH Fetch_Line_Id_Cur INTO v_po_line_id, v_cum_flag;
558 CLOSE Fetch_Line_Id_Cur;
559 ELSE
560 OPEN Fetch_Line1_Id_Cur;
561 FETCH Fetch_Line1_Id_Cur INTO v_cum_flag;
562 CLOSE Fetch_Line1_Id_Cur;
563
564 v_po_line_id := p_line_id;
565 END IF;
566
567 p_po_line_id := v_po_line_id;
568
569 OPEN Fetch_Cum_Qty_Cur;
570 FETCH Fetch_Cum_Qty_Cur INTO v_qty;
571 CLOSE Fetch_Cum_Qty_Cur;
572
573 IF v_cum_flag = 'CUMULATIVE' THEN
574 v_quantity := v_qty;
575 ELSE
576 v_quantity := p_line_quantity;
577 END IF;
578
579 OPEN Chk_Line_Loc_Cur;
580 FETCH Chk_Line_Loc_Cur INTO v_count;
581 CLOSE Chk_Line_Loc_Cur;
582
583 IF v_count = 0 THEN
584 p_line_location_id := -999;
585 ELSE
586 FOR rec IN Fetch_Qty_Cur LOOP
587 Llid_tab( i ) := NVL( rec.Line_Location_Id, -99 );
588 Qty_Tab( i ) := rec.quantity;
589 i := i + 1;
590 END LOOP;
591 i := i - 1;
592
593 IF Qty_tab( 1 ) > v_quantity THEN
594 p_line_location_id := -999;
595 ELSE
596 FOR j IN 1 .. i-1 LOOP
597 IF v_quantity >= Qty_tab( j ) AND v_quantity < Qty_tab( j + 1 ) THEN
598 p_line_location_id := Llid_tab( j );
599 END IF;
600 END LOOP;
601 IF v_quantity >= Qty_tab( i ) THEN
602 p_line_location_id := Llid_tab( i );
603 END IF;
604 END IF;
605 END IF;
606 EXCEPTION
607 WHEN OTHERS THEN
608 p_po_line_id := null;
609 p_line_location_id:= null;
610 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
611 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
612 app_exception.raise_exception;
613 END locate_source_line;
614 /*------------------------------------------------------------------------------------------------------------*/
615 PROCEDURE process_release_shipment
616 (
617 v_shipment_type IN VARCHAR2,
618 v_src_ship_id IN NUMBER,
619 v_line_loc_id IN NUMBER,
620 v_po_line_id IN NUMBER,
621 v_po_hdr_id IN NUMBER,
622 v_qty IN NUMBER,
623 v_po_rel_id IN NUMBER,
624 v_cre_dt IN DATE,
625 v_cre_by IN NUMBER,
626 v_last_upd_dt IN DATE,
627 v_last_upd_by IN NUMBER,
628 v_last_upd_login IN NUMBER,
629 flag IN VARCHAR2 DEFAULT NULL
630 ,pv_retroprice_changed IN VARCHAR2 DEFAULT 'N' --Added by Kevin Cheng for Retroactive Price 2008/01/11
631 )
632 IS
633
634 req_id NUMBER;
635 result BOOLEAN;
636 v_tax_amt NUMBER;
637 v_total_amt NUMBER;
638 v_seq_val NUMBER;
639 --Added by kunkumar for forward porting to R12 Start
640 v_vendor_id number;
641 v_vendor_site_id number;
642 v_service_type_code varchar2(30);
643
644
645 cursor fetch_vendor_id_cur IS
646 select vendor_id,vendor_site_id
647 from po_headers_all
648 where po_header_id=v_po_hdr_id;
649
650 --Added by kunkumar End
651
652 v_cum_flag VARCHAR2(50);
653
654 ------------------------------>
655
656 /*Bug 8303124 - Added precedence 6 to 10*/
657
658 CURSOR fetch_taxes_cur IS
659 SELECT line_location_id, tax_line_no, po_line_id, po_header_id,
660 precedence_1, precedence_2, precedence_3, precedence_4, precedence_5,
661 precedence_6, precedence_7, precedence_8, precedence_9, precedence_10,
662 tax_id, currency, tax_rate, qty_rate, uom, tax_amount, tax_type,
663 vendor_id, modvat_flag, tax_target_amount,
664 tax_category_id -- cbabu for EnhancementBug# 2427465
665 FROM JAI_PO_TAXES
666 WHERE line_location_id = v_src_ship_id
667 AND po_line_id = v_po_line_id;
668
669 ------------------------------>
670
671
672 CURSOR Fetch_Focus_Id IS SELECT JAI_PO_LINE_LOCATIONS_S.NEXTVAL
673 FROM Dual;
674
675 ------------------------------>
676
677 /* Fetch Cumulative pricing flag */
678
679 CURSOR Fetch_Cum_Pr_Cur IS SELECT Price_Break_Lookup_Code
680 FROM Po_Lines_All
681 WHERE Po_Line_Id = v_po_line_id;
682
683
684 ------------------------------>
685 --Start, cbabu for EnhancementBug# 2427465
686 v_tax_category_id_holder JAI_PO_LINE_LOCATIONS.tax_category_id%TYPE;
687 CURSOR c_get_tax_category_id( p_line_location_id IN NUMBER) IS
688 SELECT tax_category_id
689 FROM JAI_PO_LINE_LOCATIONS
690 WHERE line_location_id = p_line_location_id;
691 -- End, cbabu for EnhancementBug# 2427465
692
693 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_po_cmn_pkg.process_release_shipment';
694
695 BEGIN
696 /*-----------------------------------------------------------------------------------------------------------------------
697 CHANGE HISTORY: FILENAME: ja_in_po_releases_p.sql
698 S.No Date Author and Details
699 -------------------------------------------------------------------------------------------------------------------------
700 1 06/12/2002 cbabu for EnhancementBug# 2427465, FileVersion# 615.1
701 tax_category_id column is populated into PO and SO localization tables, which will be used to
702 identify from which tax_category the taxes are defaulted. Also the tax_category_id populated into
703 the tax table will be useful to identify whether the tax is a defaulted or a manual tax.
704 2 21/12/2004 avallabh for bug#4070899, file version 115.1
705 When submitting request for JAINPOCR, the description is being displayed without an "India - " prefix.
706 Changed the FND_REQUEST.submit_request procedure and attached the prefix "India - ".
707
708 3 15/01/2008 Kevin Cheng for Retroactive Price Enhancement
709 Add parameter pv_retroprice_changed to indicate whether it is called by retroactive price concurrent;
710 Add parameter to procedures called here;
711 -------------------------------------------------------------------------------------------------------------------------*/
712
713 OPEN Fetch_Focus_Id;
714 FETCH Fetch_Focus_Id INTO v_seq_val;
715 CLOSE Fetch_Focus_Id;
716
717 OPEN Fetch_Cum_Pr_Cur;
718 FETCH Fetch_Cum_Pr_Cur INTO v_cum_flag;
719 CLOSE Fetch_Cum_Pr_Cur;
720
721 -- cbabu for EnhancementBug# 2427465
722 OPEN c_get_tax_category_id(v_src_ship_id);
723 FETCH c_get_tax_category_id INTO v_tax_category_id_holder;
724 CLOSE c_get_tax_category_id;
725 open fetch_vendor_id_cur;
726 fetch fetch_vendor_id_cur into v_vendor_id, v_vendor_site_id;
727 close fetch_vendor_id_cur;
728
729 v_service_type_code :=jai_ar_rctla_trigger_pkg.get_service_type(v_vendor_id,v_vendor_site_id,'V');
730
731
732 IF flag <> 'U' THEN
733
734 INSERT INTO JAI_PO_LINE_LOCATIONS(
735 line_focus_id, line_location_id, po_line_id, po_header_id,
736 tax_modified_flag, tax_amount, total_amount,
737 creation_date, created_by, last_update_date, last_updated_by, last_update_login,
738 tax_category_id -- cbabu for EnhancementBug# 2427465
739 ,service_type_code ) VALUES (
740 v_seq_val, v_line_loc_id, v_po_line_id, v_po_hdr_id,
741 'N', v_tax_amt, v_total_amt,
742 v_cre_dt, v_cre_by, v_last_upd_dt, v_last_upd_by, v_last_upd_login,
743 v_tax_category_id_holder -- cbabu for EnhancementBug# 2427465
744 , v_service_type_code );
745 END IF;
746
747 IF v_shipment_type = 'SCHEDULED' THEN
748 FOR Tax_Rec IN Fetch_Taxes_Cur LOOP
749
750 /*Bug 8303124 - Added precedence 6 to 10*/
751
752 INSERT INTO JAI_PO_REQUEST_T(
753 Line_Focus_Id, Line_Location_Id, Tax_Line_No,
754 Po_Line_Id, Po_Header_Id, Precedence_1,
755 Precedence_2, Precedence_3, Precedence_4,
756 Precedence_5, Precedence_6,
757 Precedence_7, Precedence_8, Precedence_9,
758 Precedence_10,
759 Tax_Id, Currency,
760 Tax_Rate, Qty_Rate, UOM,
761 Tax_Amount, Tax_Type, Modvat_Flag,
762 Vendor_Id, Tax_Target_Amount, Creation_Date,
763 Created_By, Last_Update_Date, Last_Updated_By, Last_Update_Login,
764 tax_category_id -- cbabu for EnhancementBug# 2427465
765 ) VALUES (
766 v_seq_val, v_line_loc_id, Tax_Rec.Tax_Line_No,
767 v_Po_Line_Id, v_Po_Hdr_Id, Tax_Rec.Precedence_1,
768 Tax_Rec.Precedence_2, Tax_Rec.Precedence_3, Tax_Rec.Precedence_4,
769 Tax_Rec.Precedence_5, Tax_Rec.Precedence_6,
770 Tax_Rec.Precedence_7, Tax_Rec.Precedence_8, Tax_Rec.Precedence_9,
771 Tax_Rec.Precedence_10,
772 Tax_Rec.Tax_Id, Tax_Rec.Currency,
773 Tax_Rec.Tax_Rate, Tax_Rec.Qty_Rate, Tax_Rec.UOM,
774 Tax_Rec.Tax_Amount, Tax_Rec.Tax_Type, Tax_Rec.Modvat_Flag,
775 Tax_Rec.Vendor_Id, Tax_Rec.Tax_Target_Amount, v_cre_dt,
776 v_cre_by, v_last_upd_dt, v_last_upd_by, v_last_upd_login,
777 tax_rec.tax_category_id -- cbabu for EnhancementBug# 2427465
778 );
779
780 END LOOP;
781
782 -- Run concurrent request.
783
784 /* Changed the submit_request calls below and prefixed with "India - " for bug #4070899 */
785
786 result := Fnd_Request.Set_Mode( TRUE );
787 req_id := Fnd_Request.Submit_Request( 'JA', 'JAINPOCR', 'India - Concurrent request for ' || v_shipment_type || ' Release',
788 SYSDATE, FALSE,
789 v_seq_val, v_qty, NULL, NULL, v_src_ship_id,
790 v_shipment_type, NULL,
791 v_cre_dt, v_cre_by, v_last_upd_dt, v_last_upd_by, v_last_upd_login );
792
793
794 ELSIF v_shipment_type = 'BLANKET' THEN
795
796 -- Run concurrent request.
797
798 result := Fnd_Request.Set_Mode( TRUE );
799 req_id := Fnd_Request.Submit_Request( 'JA', 'JAINPOCR', 'India - Concurrent request for ' || v_shipment_type || ' Release',
800 SYSDATE, FALSE,
801 v_seq_val, v_qty, v_po_hdr_id, v_po_line_id, v_line_loc_id,
802 v_shipment_type, v_cum_flag,
803 v_cre_dt, v_cre_by, v_last_upd_dt, v_last_upd_by, v_last_upd_login
804 ,pv_retroprice_changed --Added by Kevin Cheng for Retroactive Price 2008/01/11
805 );
806
807 END IF;
808
809 EXCEPTION
810 WHEN OTHERS THEN
811 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
812 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
813 app_exception.raise_exception;
814 END process_release_shipment;
815
816 /*------------------------------------------------------------------------------------------------------------*/
817 PROCEDURE get_functional_curr
818 ( v_ship_to_loc_id IN NUMBER, v_po_org_id IN NUMBER, v_inv_org_id IN NUMBER,
819 v_doc_curr IN VARCHAR2, v_assessable_value IN OUT NOCOPY NUMBER,
820 v_rate IN OUT NOCOPY NUMBER, v_rate_type IN VARCHAR2, v_rate_date IN DATE,
821 v_func_currency IN OUT NOCOPY VARCHAR2
822 )
823 IS
824
825 v_set_of_books_id NUMBER;
826 v_func_curr Gl_Sets_Of_Books.Currency_Code%TYPE;
827 v_location_id NUMBER;
828
829 v_bookid NUMBER;
830 v_org_id NUMBER;
831
832 conv_rate NUMBER;
833
834 CURSOR Get_Inv_Org_Id_Cur IS SELECT Inventory_Organization_Id
835 FROM Hr_Locations
836 WHERE Location_Id = v_location_id;
837 /* Bug 5243532. Added by Lakshmi Gopalsami
838 * Removed cursors Get_Set_Of_Book_Cur and Get_Func_Curr_Cur
839 * and implemented caching logic
840 * Removed the unused cursor Fetch_SET_Of_Books_Id_Cur
841 */
842
843 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_po_cmn_pkg.get_functional_curr';
844 /* Bug 5243532. Added by Lakshmi Gopalsami
845 * Defined variable for implementing caching logic.
846 */
847 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
848 BEGIN
849 /* Bug 5243532. Added by Lakshmi Gopalsami
850 * Removed cursors Get_Set_Of_Book_Cur and Get_Func_Curr_Cur
851 * and implemented caching logic
852 */
853 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
854 (p_org_id => v_inv_org_id );
855 v_set_of_books_id := l_func_curr_det.ledger_id;
856 v_func_curr := l_func_curr_det.currency_code;
857
858 IF v_func_curr <> v_doc_curr OR v_doc_curr IS NOT NULL THEN
859
860 IF v_rate_type = 'User' THEN
861 conv_rate := 1/v_rate;
862 ELSE
863 /* Bug 5243532. Added by Lakshmi Gopalsami
864 * Removed the commented code
865 */
866 conv_rate := jai_cmn_utils_pkg.currency_conversion
867 ( v_set_of_books_id,
868 v_doc_curr,
869 v_rate_date,
870 v_rate_type,
871 1
872 );
873 conv_rate := 1/conv_rate;
874 END IF;
875 ELSE
876 conv_rate := 1;
877 END IF;
878
879 v_assessable_value := NVL( v_assessable_value * conv_rate, 0 );
880 v_func_currency := v_func_curr;
881 v_rate := conv_rate;
882 EXCEPTION
883 WHEN OTHERS THEN
884 v_assessable_value := null;
885 v_rate := null;
886 v_func_currency:= null;
887 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
888 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
889 app_exception.raise_exception;
890 END get_functional_curr;
891
892 /*------------------------------------------------------------------------------------------------------------*/
893
894 END jai_po_cmn_pkg;