1 PACKAGE BODY inv_cache AS
2 /* $Header: INVCACHB.pls 120.8 2010/08/25 15:52:14 hjogleka ship $ */
3
4 g_om_installed VARCHAR2(10);
5 g_wms_installed_org NUMBER;
6 g_oe_header_id NUMBER;
7
8 FUNCTION set_org_rec
9 (
10 p_organization_id IN NUMBER
11 ) RETURN BOOLEAN IS
12 l_return_val BOOLEAN := FALSE;
13 BEGIN
14 IF org_rec.organization_id = p_organization_id THEN
15 l_return_val := TRUE;
16 ELSE
17 SELECT *
18 INTO org_rec
19 FROM MTL_PARAMETERS
20 WHERE organization_id = p_organization_id;
21 l_return_val := TRUE;
22 END IF;
23 RETURN l_return_val;
24
25 EXCEPTION
26 WHEN NO_DATA_FOUND THEN
27 RETURN l_return_val;
28 WHEN OTHERS THEN
29 RETURN l_return_val;
30 END set_org_rec;
31
32
33 FUNCTION set_org_rec
34 (
35 p_organization_code IN VARCHAR2
36 ) RETURN BOOLEAN IS
37 l_return_val BOOLEAN := FALSE;
38 BEGIN
39 IF org_rec.organization_code = p_organization_code THEN
40 l_return_val := TRUE;
41 ELSE
42 SELECT *
43 INTO org_rec
44 FROM MTL_PARAMETERS
45 WHERE organization_code = p_organization_code;
46 l_return_val := TRUE;
47 END IF;
48 RETURN l_return_val;
49
50 EXCEPTION
51 WHEN NO_DATA_FOUND THEN
52 RETURN l_return_val;
53 WHEN OTHERS THEN
54 RETURN l_return_val;
55 END set_org_rec;
56
57
58 FUNCTION set_item_rec
59 (
60 p_organization_id IN NUMBER,
61 p_item_id IN NUMBER
62 ) RETURN BOOLEAN IS
63 l_return_val BOOLEAN := FALSE;
64 BEGIN
65 IF (item_rec.organization_id = p_organization_id AND
66 item_rec.inventory_item_id = p_item_id) THEN
67 l_return_val := TRUE;
68 ELSE
69 SELECT *
70 INTO item_rec
71 FROM MTL_SYSTEM_ITEMS
72 WHERE organization_id = p_organization_id
73 AND inventory_item_id = p_item_id;
74 l_return_val := TRUE;
75 END IF;
76 RETURN l_return_val;
80 RETURN l_return_val;
77
78 EXCEPTION
79 WHEN NO_DATA_FOUND THEN
81 WHEN OTHERS THEN
82 RETURN l_return_val;
83 END;
84
85
86 FUNCTION set_tosub_rec
87 (
88 p_organization_id IN NUMBER,
89 p_subinventory_code IN VARCHAR2
90 ) RETURN BOOLEAN IS
91 l_return_val BOOLEAN := FALSE;
92 BEGIN
93 IF (tosub_rec.organization_id = p_organization_id AND
94 tosub_rec.secondary_inventory_name = p_subinventory_code) THEN
95 l_return_val := TRUE;
96 ELSE
97 SELECT *
98 INTO tosub_rec
99 FROM MTL_SECONDARY_INVENTORIES
100 WHERE organization_id = p_organization_id
101 AND secondary_inventory_name = p_subinventory_code;
102 l_return_val := TRUE;
103 END IF;
104 RETURN l_return_val;
105
106 EXCEPTION
107 WHEN NO_DATA_FOUND THEN
108 RETURN l_return_val;
109 WHEN OTHERS THEN
110 RETURN l_return_val;
111 END;
112
113
114 FUNCTION set_fromsub_rec
115 (
116 p_organization_id IN NUMBER,
117 p_subinventory_code IN VARCHAR2
118 ) RETURN BOOLEAN IS
119 l_return_val BOOLEAN := FALSE;
120 BEGIN
121 IF (fromsub_rec.organization_id = p_organization_id AND
122 fromsub_rec.secondary_inventory_name = p_subinventory_code) THEN
123 l_return_val := TRUE;
124 ELSE
125 SELECT *
126 INTO fromsub_rec
127 FROM MTL_SECONDARY_INVENTORIES
128 WHERE organization_id = p_organization_id
129 AND secondary_inventory_name = p_subinventory_code;
130 l_return_val := TRUE;
131 END IF;
132 RETURN l_return_val;
133
134 EXCEPTION
135 WHEN NO_DATA_FOUND THEN
136 RETURN l_return_val;
137 WHEN OTHERS THEN
138 RETURN l_return_val;
139 END;
140
141 FUNCTION set_mmtt_rec
142 (
143 p_transaction_temp_id IN NUMBER
144 ) RETURN BOOLEAN IS
145 l_return_val BOOLEAN := FALSE;
146 BEGIN
147 IF (mmtt_rec.transaction_temp_id = p_transaction_temp_id) THEN
148 l_return_val := TRUE;
149 ELSE
150 SELECT *
151 INTO mmtt_rec
152 FROM MTL_MATERIAL_TRANSACTIONS_TEMP
153 WHERE transaction_temp_id = p_transaction_temp_id;
154 l_return_val := TRUE;
155 END IF;
156 RETURN l_return_val;
157
158 EXCEPTION
159 WHEN NO_DATA_FOUND THEN
160 RETURN l_return_val;
161 WHEN OTHERS THEN
162 RETURN l_return_val;
163 END;
164
165 FUNCTION set_mol_rec
166 (
167 p_line_id IN NUMBER
168 ) RETURN BOOLEAN IS
169 l_return_val BOOLEAN := FALSE;
170 BEGIN
171 IF (mol_rec.line_id = p_line_id) THEN
172 l_return_val := TRUE;
173 ELSE
174 SELECT *
175 INTO mol_rec
176 FROM MTL_TXN_REQUEST_LINES
177 WHERE line_id = p_line_id;
178 l_return_val := TRUE;
179 END IF;
180 RETURN l_return_val;
181
182 EXCEPTION
183 WHEN NO_DATA_FOUND THEN
184 RETURN l_return_val;
185 WHEN OTHERS THEN
186 RETURN l_return_val;
187 END;
188
189 FUNCTION set_mtrh_rec
190 (
191 p_header_id IN NUMBER
192 ) RETURN BOOLEAN IS
193 l_return_val BOOLEAN := FALSE;
194 BEGIN
195 IF (mtrh_rec.header_id = p_header_id) THEN
196 l_return_val := TRUE;
197 ELSE
198 SELECT *
199 INTO mtrh_rec
200 FROM MTL_TXN_REQUEST_HEADERS
201 WHERE header_id = p_header_id;
202 l_return_val := TRUE;
203 END IF;
204 RETURN l_return_val;
205
206 EXCEPTION
207 WHEN NO_DATA_FOUND THEN
208 RETURN l_return_val;
209 WHEN OTHERS THEN
210 RETURN l_return_val;
211 END;
212
213 FUNCTION set_wdd_rec
214 (
215 p_move_order_line_id IN NUMBER
216 ) RETURN BOOLEAN IS
217 l_return_val BOOLEAN := FALSE;
218 BEGIN
219 IF (wdd_rec.move_order_line_id = p_move_order_line_id) THEN
220 l_return_val := TRUE;
221 ELSE
222 SELECT *
223 INTO wdd_rec
224 FROM WSH_DELIVERY_DETAILS
225 WHERE move_order_line_id = p_move_order_line_id;
226 --AND NVL(released_status, 'Z') <> 'Y'; --Bug 8642550
227 l_return_val := TRUE;
228 END IF;
229 RETURN l_return_val;
230
231 EXCEPTION
232 WHEN NO_DATA_FOUND THEN
233 RETURN l_return_val;
234 WHEN OTHERS THEN
235 RETURN l_return_val;
236 END;
237
238 FUNCTION set_mso_rec
239 (
240 p_oe_header_id IN NUMBER
241 ) RETURN BOOLEAN IS
242 l_order_source VARCHAR2(1000);
243 l_order_number VARCHAR2(50);
244 l_order_type VARCHAR2(50);
245 l_return_val BOOLEAN := FALSE;
246
247 BEGIN
248 -- Same as inv_sales_order.get_salesorder_for_oeheader(p_oe_header_id);
249 IF (g_oe_header_id = p_oe_header_id) THEN
250 l_return_val := TRUE;
251 ELSE
252 oe_header_util.get_order_info(p_oe_header_id,
253 l_order_number,
254 l_order_type,
255 l_order_source);
256
257 SELECT *
258 INTO mso_rec
259 FROM mtl_sales_orders
260 WHERE segment1 = l_order_number
261 AND segment2 = l_order_type
262 AND segment3 = l_order_source ;
263 g_oe_header_id := p_oe_header_id;
264 l_return_val := TRUE;
265 END IF;
266 RETURN l_return_val;
267
268 EXCEPTION
269 WHEN NO_DATA_FOUND THEN
270 RETURN l_return_val;
271 WHEN OTHERS THEN
275 FUNCTION set_oeh_id
272 RETURN l_return_val;
273 END;
274
276 (
277 p_salesorder_id IN NUMBER
278 ) RETURN BOOLEAN IS
279 l_return_val BOOLEAN := FALSE;
280 BEGIN
281
282 IF mso_rec.sales_order_id = p_salesorder_id THEN
283 l_return_val := TRUE;
284 ELSE
285
286 -- initialize x_oe_order_id to -1, assume default that SO row not created by OOM
287 g_oe_header_id := -1 ;
288
289 -- now check if the SO was created by Oracle Order Management (OOM). If not return (-1)
290 if ( g_om_installed IS NULL ) then
291 g_om_installed := oe_install.get_active_product ;
292 end if;
293 if (g_om_installed <> 'ONT') then -- OOM is not active
294 return TRUE ;
295 end if;
296
297 -- now select segment 2 for the given sales order id
298 SELECT *
299 INTO mso_rec
300 FROM mtl_sales_orders
301 WHERE sales_order_id = p_salesorder_id ;
302
303
304 /*g_oe_header_id := inv_sales_order.get_header_id(to_number(mso_rec.segment1),
305 mso_rec.segment2,
306 mso_rec.segment3);
307 */
308 l_return_val := TRUE;
309 END IF;
310 RETURN l_return_val;
311
312 EXCEPTION
313 WHEN NO_DATA_FOUND THEN
314 RETURN l_return_val;
315 WHEN OTHERS THEN
316 RETURN l_return_val;
317 END;
318
319
320 FUNCTION set_mtt_rec
321 (
322 p_transaction_type_id IN NUMBER
323 ) RETURN BOOLEAN IS
324 l_return_val BOOLEAN := FALSE;
325 BEGIN
326 IF (mtt_rec.transaction_type_id = p_transaction_type_id) THEN
327 l_return_val := TRUE;
328 ELSE
329 SELECT *
330 INTO mtt_rec
331 FROM MTL_TRANSACTION_TYPES
332 WHERE transaction_type_id = p_transaction_type_id;
333 l_return_val := TRUE;
334 END IF;
335 RETURN l_return_val;
336
337 EXCEPTION
338 WHEN NO_DATA_FOUND THEN
339 RETURN l_return_val;
340 WHEN OTHERS THEN
341 RETURN l_return_val;
342 END;
343
344
345 FUNCTION set_wms_installed
346 (
347 p_organization_id IN NUMBER
348 ) RETURN BOOLEAN IS
349 l_return_val BOOLEAN := FALSE;
350 BEGIN
351
352 IF g_wms_installed_org = p_organization_id THEN
353 l_return_val := TRUE;
354 ELSE
355 wms_installed := inv_install.adv_inv_installed(p_organization_id);
356 g_wms_installed_org := p_organization_id;
357 l_return_val := TRUE;
358 END IF;
359 RETURN l_return_val;
360
361 EXCEPTION
362 WHEN NO_DATA_FOUND THEN
363 RETURN l_return_val;
364 WHEN OTHERS THEN
365 RETURN l_return_val;
366 END;
367
368
369 FUNCTION set_pick_release
370 (
371 p_value IN BOOLEAN
372 ) RETURN BOOLEAN IS
373 BEGIN
374
375 is_pickrelease := p_value;
376 RETURN TRUE;
377
378 EXCEPTION
379 WHEN OTHERS THEN
380 RETURN FALSE;
381 END;
382
383 FUNCTION set_to_locator
384 (
385 p_locator_id IN NUMBER
386 ) RETURN BOOLEAN IS
387 BEGIN
388
389 tolocator_id := p_locator_id;
390 RETURN TRUE;
391
392 EXCEPTION
393 WHEN OTHERS THEN
394 RETURN FALSE;
395 END;
396
397 FUNCTION set_to_subinventory
398 (
399 p_subinventory_code IN NUMBER
400 ) RETURN BOOLEAN IS
401 BEGIN
402
403 tosubinventory_code := p_subinventory_code;
404 RETURN TRUE;
405
406 EXCEPTION
407 WHEN OTHERS THEN
408 RETURN False;
409 END;
410
411 --4171297
412 FUNCTION set_oola_rec
413 (
414 p_order_line_id IN NUMBER
415 ) RETURN BOOLEAN IS
416 l_return_val BOOLEAN := FALSE;
417 BEGIN
418 IF (oola_rec.line_id = p_order_line_id) THEN
419 l_return_val := TRUE;
420 ELSE
421 SELECT *
422 INTO oola_rec
423 FROM oe_order_lines_all
424 WHERE line_id = p_order_line_id;
425 l_return_val := TRUE;
426 END IF;
427 RETURN l_return_val;
428
429 EXCEPTION
430 WHEN NO_DATA_FOUND THEN
431 RETURN l_return_val;
432 WHEN OTHERS THEN
433 RETURN l_return_val;
434 END;
435
436
437 -- Bug# 4258360: Added for R12 Crossdock Pegging Project
438 -- Retrieve the picking batch record for a pick release batch
439 FUNCTION set_wpb_rec
440 (p_batch_id IN NUMBER,
441 p_request_number IN VARCHAR2
442 ) RETURN BOOLEAN IS
443 BEGIN
444 IF (p_batch_id IS NULL AND p_request_number IS NULL) THEN
445 -- At least one value must be inputted
446 RETURN FALSE;
447 ELSIF (p_batch_id IS NOT NULL AND wpb_rec.batch_id = p_batch_id) THEN
448 -- Value is already cached
449 RETURN TRUE;
450 ELSIF (p_request_number IS NOT NULL AND wpb_rec.name = p_request_number) THEN
451 -- Value is already cached
452 RETURN TRUE;
453 ELSIF (p_batch_id IS NOT NULL) THEN
454 -- Query and cache the value based on p_batch_id
455 SELECT *
456 INTO wpb_rec
457 FROM wsh_picking_batches
458 WHERE batch_id = p_batch_id;
459 RETURN TRUE;
460 ELSIF (p_request_number IS NOT NULL) THEN
461 -- Query and cache the value based on p_request_number
465 WHERE name = p_request_number;
462 SELECT *
463 INTO wpb_rec
464 FROM wsh_picking_batches
466 RETURN TRUE;
467 ELSE
468 -- Should not reach this condition
469 RETURN FALSE;
470 END IF;
471
472 EXCEPTION
473 WHEN OTHERS THEN
474 RETURN FALSE;
475 END;
476
477 -- Onhand Material Status Support
478 -- Cache locator record.
479 FUNCTION set_loc_rec
480 (
481 p_organization_id IN NUMBER,
482 p_locator_id IN VARCHAR2
483 ) RETURN BOOLEAN IS
484
485 l_return_val BOOLEAN := FALSE;
486 BEGIN
487 IF loc_rec.organization_id = p_organization_id and
488 loc_rec.inventory_location_id = p_locator_id THEN
489 l_return_val := TRUE;
490 ELSE
491 SELECT *
492 INTO loc_rec
493 FROM MTL_ITEM_LOCATIONS
494 WHERE inventory_location_id = p_locator_id
495 AND organization_id = p_organization_id;
496
497 l_return_val := TRUE;
498 END IF;
499
500 RETURN l_return_val;
501
502 EXCEPTION
503 WHEN NO_DATA_FOUND THEN
504 RETURN l_return_val;
505 WHEN OTHERS THEN
506 RETURN l_return_val;
507 END set_loc_rec;
508
509 -- Onhand Material Status Support
510 -- Cache status_id of MOQD for a given SKU.
511 FUNCTION set_moqd_status_rec
512 (
513 p_organization_id IN NUMBER,
514 p_inventory_item_id IN NUMBER,
515 p_sub_code IN VARCHAR2,
516 p_loc_id IN NUMBER,
517 p_lot_number IN VARCHAR2,
518 p_lpn_id IN NUMBER
519 ) RETURN BOOLEAN IS
520
521 l_return_val BOOLEAN := FALSE;
522
523 BEGIN
524 IF moqd_rec.organization_id = p_organization_id and
525 moqd_rec.inventory_item_id = p_inventory_item_id and
526 moqd_rec.subinventory_code = p_sub_code and
527 nvl(moqd_rec.locator_id, -9999) = nvl(p_loc_id, -9999) and
528 nvl(moqd_rec.lot_number, '@@@@') = nvl(p_lot_number, '@@@@') and
529 nvl(moqd_rec.lpn_id, -9999) = nvl(p_lpn_id, -9999) THEN
530
531 l_return_val := TRUE;
532 ELSE
533 SELECT status_id
534 INTO moqd_rec.status_id
535 FROM MTL_ONHAND_QUANTITIES_DETAIL
536 WHERE inventory_item_id = p_inventory_item_id
537 AND organization_id = p_organization_id
538 AND subinventory_code = p_sub_code
539 AND nvl( locator_id, -9999) =nvl(p_loc_id, -9999)
540 AND nvl(lot_number, '@@@@') = nvl(p_lot_number, '@@@@')
541 AND nvl(lpn_id, -9999) = nvl(p_lpn_id, -9999)
542 AND rownum = 1;
543
544 l_return_val := TRUE;
545 END IF;
546
547 RETURN l_return_val;
548
549 EXCEPTION
550 WHEN NO_DATA_FOUND THEN
551 RETURN l_return_val;
552 WHEN OTHERS THEN
553 RETURN l_return_val;
554 END set_moqd_status_rec;
555
556 --8809951 High Volume Projec Phase-2
557 FUNCTION set_pjm_org_parms_rec
558 (
559 p_organization_id IN NUMBER
560 ) RETURN BOOLEAN IS
561
562 l_return_val BOOLEAN := FALSE;
563
564 BEGIN
565 IF pjm_org_parms_rec.organization_id = p_organization_id THEN
566 l_return_val := TRUE;
567 ELSE
568 SELECT allow_cross_proj_issues
569 , allow_cross_unitnum_issues
570 INTO pjm_org_parms_rec.allow_cross_proj_issues,
571 pjm_org_parms_rec.allow_cross_unitnum_issues
572 FROM pjm_org_parameters
573 WHERE organization_id = p_organization_id;
574
575 l_return_val := TRUE;
576 END IF;
577
578 RETURN l_return_val;
579
580 EXCEPTION
581 WHEN NO_DATA_FOUND THEN
582 RETURN l_return_val;
583 WHEN OTHERS THEN
584 RETURN l_return_val;
585 END set_pjm_org_parms_rec;
586
587 /* ==================================================================================*
588 | Procedure : get_client_default_parameters |
589 | Added for LSP Project |
590 | |
591 | Description : For getting the client parameter record , to be used by Shipping |
592 | Team |
593 | Input Parameters: |
594 | p_client_id - The client ID for which the record needs to be passed. |
595 | Output Parameters: |
596 | x_return_status - fnd_api.g_ret_sts_success, if succeeded |
597 | fnd_api.g_ret_sts_error, if error occurred |
598 | x_client_parameters_rec - It returns the record of mtl_client_parameters |
599 | for the passed client_id |
600 | and can be queried in the following format |
601 | Dbms_Output.put_line('Client ID -'||ct_rec.client_rec.client_id) |
602 *================================================================================== */
603
604 PROCEDURE get_client_default_parameters
605 (
606 p_client_id IN MTL_CLIENT_PARAMETERS.CLIENT_ID%TYPE
607 , x_return_status OUT NOCOPY VARCHAR2
608 , x_client_parameters_rec OUT NOCOPY ct_rec_type
609 )
610 AS
611
612 CURSOR client_info(p_client_id NUMBER)
613 IS SELECT * FROM MTL_CLIENT_PARAMETERS
614 WHERE client_id = p_client_id ;
615
616 BEGIN
617 x_return_status := fnd_api.g_ret_sts_success;
618 IF ct_table.EXISTS(p_client_id) THEN
619 x_client_parameters_rec := ct_table(p_client_id);
620 ELSE
621 FOR client_rec_new IN client_info(p_client_id)
622 LOOP
623 ct_table(p_client_id).client_rec := client_rec_new;
624 END LOOP;
625 x_client_parameters_rec := ct_table(p_client_id);
626 END IF;
627
628 EXCEPTION
629 WHEN OTHERS THEN
630 x_return_status := fnd_api.g_ret_sts_error;
631 END get_client_default_parameters;
632
633 /* End of changes for LSP Project */
634
635 --Serial Tagged
636 FUNCTION get_serial_tagged
637 ( p_organization_id IN NUMBER
638 , p_inventory_item_id IN NUMBER
639 , p_transaction_type_id IN NUMBER
640 ) RETURN NUMBER IS
641 x_serial_tag NUMBER := 1;
642 l_hash_value NUMBER;
643 l_txn_type NUMBER;
644 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
645 BEGIN
646 IF p_transaction_type_id = 43 THEN
647 l_txn_type := 35;
648 ELSIF p_transaction_type_id = 94 THEN
649 l_txn_type := 93;
650 ELSIF p_transaction_type_id = 1005 THEN
651 l_txn_type := 36;
652 ELSE
653 l_txn_type := p_transaction_type_id;
654 END IF;
655
656 IF l_debug = 1 THEN
657 inv_log_util.TRACE('p_pxn_type:' || p_transaction_type_id || ' l_txn_type:' || l_txn_type,'INV_CACHE',9);
658 END IF;
659
660
661 l_hash_value := DBMS_UTILITY.get_hash_value
662 ( NAME => p_organization_id ||':'|| p_inventory_item_id ||':'|| l_txn_type
663 , base => 1
664 , hash_size => POWER(2, 25)
665 );
666
667 IF serial_tag_table.exists(l_hash_value) THEN
668 x_serial_tag := serial_tag_table(l_hash_value);
669 IF l_debug = 1 THEN
670 inv_log_util.TRACE('found in cache','INV_CACHE',9);
671 END IF;
672 ELSE
673 IF set_org_rec(p_organization_id) THEN
674 IF org_rec.WSM_ENABLED_FLAG = 'N' -- OSFM
675 AND org_rec.PROCESS_ENABLED_FLAG = 'N' -- OPM
676 AND org_rec.WMS_ENABLED_FLAG = 'N' -- WMS
677 AND org_rec.EAM_ENABLED_FLAG = 'N' -- EAM
678 AND org_rec.PROJECT_REFERENCE_ENABLED = 2 -- PJM
679 THEN
680 IF l_debug = 1 THEN
681 inv_log_util.TRACE('In branch 1','INV_CACHE',9);
682 END IF;
683 BEGIN
684 SELECT 2 INTO x_serial_tag
685 FROM DUAL
686 WHERE EXISTS (SELECT 1 FROM mtl_serial_tagging_assignments
687 WHERE organization_id = p_organization_id
688 AND inventory_item_id = p_inventory_item_id
689 AND transaction_type_id = l_txn_type
690 );
691 EXCEPTION
692 WHEN OTHERS THEN
693 NULL;
694 END;
695 END IF;
696 END IF;
697 IF l_debug = 1 THEN
698 inv_log_util.TRACE('x_serial_tag:' || x_serial_tag,'INV_CACHE',9);
699 END IF;
700 serial_tag_table(l_hash_value) := x_serial_tag;
701
702 END IF;
703 RETURN x_serial_tag;
704
705
706
707
708 EXCEPTION
709 WHEN OTHERS THEN
710 RETURN x_serial_tag;
711 END get_serial_tagged;
712
713 END inv_cache;
[Home] [Help]