1 PACKAGE BODY inv_cache AS
2 /* $Header: INVCACHB.pls 120.2 2007/12/26 18:43:56 musinha 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;
77
78 EXCEPTION
79 WHEN NO_DATA_FOUND THEN
80 RETURN l_return_val;
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 l_return_val := TRUE;
227 END IF;
228 RETURN l_return_val;
229
230 EXCEPTION
231 WHEN NO_DATA_FOUND THEN
232 RETURN l_return_val;
233 WHEN OTHERS THEN
234 RETURN l_return_val;
235 END;
236
237 FUNCTION set_mso_rec
238 (
239 p_oe_header_id IN NUMBER
240 ) RETURN BOOLEAN IS
241 l_order_source VARCHAR2(1000);
242 l_order_number VARCHAR2(50);
243 l_order_type VARCHAR2(50);
244 l_return_val BOOLEAN := FALSE;
245
246 BEGIN
247 -- Same as inv_sales_order.get_salesorder_for_oeheader(p_oe_header_id);
248 IF (g_oe_header_id = p_oe_header_id) THEN
249 l_return_val := TRUE;
250 ELSE
251 oe_header_util.get_order_info(p_oe_header_id,
252 l_order_number,
253 l_order_type,
254 l_order_source);
255
256 SELECT *
257 INTO mso_rec
258 FROM mtl_sales_orders
259 WHERE segment1 = l_order_number
260 AND segment2 = l_order_type
261 AND segment3 = l_order_source ;
262 g_oe_header_id := p_oe_header_id;
263 l_return_val := TRUE;
264 END IF;
265 RETURN l_return_val;
266
267 EXCEPTION
268 WHEN NO_DATA_FOUND THEN
269 RETURN l_return_val;
270 WHEN OTHERS THEN
271 RETURN l_return_val;
272 END;
273
274 FUNCTION set_oeh_id
275 (
276 p_salesorder_id IN NUMBER
277 ) RETURN BOOLEAN IS
278 l_return_val BOOLEAN := FALSE;
279 BEGIN
280
281 IF mso_rec.sales_order_id = p_salesorder_id THEN
282 l_return_val := TRUE;
283 ELSE
284
285 -- initialize x_oe_order_id to -1, assume default that SO row not created by OOM
286 g_oe_header_id := -1 ;
287
288 -- now check if the SO was created by Oracle Order Management (OOM). If not return (-1)
289 if ( g_om_installed IS NULL ) then
290 g_om_installed := oe_install.get_active_product ;
291 end if;
292 if (g_om_installed <> 'ONT') then -- OOM is not active
293 return TRUE ;
294 end if;
295
296 -- now select segment 2 for the given sales order id
297 SELECT *
298 INTO mso_rec
299 FROM mtl_sales_orders
300 WHERE sales_order_id = p_salesorder_id ;
301
302
303 /*g_oe_header_id := inv_sales_order.get_header_id(to_number(mso_rec.segment1),
304 mso_rec.segment2,
305 mso_rec.segment3);
306 */
307 l_return_val := TRUE;
308 END IF;
309 RETURN l_return_val;
310
311 EXCEPTION
312 WHEN NO_DATA_FOUND THEN
313 RETURN l_return_val;
314 WHEN OTHERS THEN
315 RETURN l_return_val;
316 END;
317
318
319 FUNCTION set_mtt_rec
320 (
321 p_transaction_type_id IN NUMBER
322 ) RETURN BOOLEAN IS
323 l_return_val BOOLEAN := FALSE;
324 BEGIN
325 IF (mtt_rec.transaction_type_id = p_transaction_type_id) THEN
326 l_return_val := TRUE;
327 ELSE
328 SELECT *
329 INTO mtt_rec
330 FROM MTL_TRANSACTION_TYPES
331 WHERE transaction_type_id = p_transaction_type_id;
332 l_return_val := TRUE;
333 END IF;
334 RETURN l_return_val;
335
336 EXCEPTION
337 WHEN NO_DATA_FOUND THEN
338 RETURN l_return_val;
339 WHEN OTHERS THEN
340 RETURN l_return_val;
341 END;
342
343
344 FUNCTION set_wms_installed
345 (
346 p_organization_id IN NUMBER
347 ) RETURN BOOLEAN IS
348 l_return_val BOOLEAN := FALSE;
349 BEGIN
350
351 IF g_wms_installed_org = p_organization_id THEN
352 l_return_val := TRUE;
353 ELSE
354 wms_installed := inv_install.adv_inv_installed(p_organization_id);
355 g_wms_installed_org := p_organization_id;
356 l_return_val := TRUE;
357 END IF;
358 RETURN l_return_val;
359
360 EXCEPTION
361 WHEN NO_DATA_FOUND THEN
362 RETURN l_return_val;
363 WHEN OTHERS THEN
364 RETURN l_return_val;
365 END;
366
367
368 FUNCTION set_pick_release
369 (
370 p_value IN BOOLEAN
371 ) RETURN BOOLEAN IS
372 BEGIN
373
374 is_pickrelease := p_value;
375 RETURN TRUE;
376
377 EXCEPTION
378 WHEN OTHERS THEN
379 RETURN FALSE;
380 END;
381
382 FUNCTION set_to_locator
383 (
384 p_locator_id IN NUMBER
385 ) RETURN BOOLEAN IS
386 BEGIN
387
388 tolocator_id := p_locator_id;
389 RETURN TRUE;
390
391 EXCEPTION
392 WHEN OTHERS THEN
393 RETURN FALSE;
394 END;
395
396 FUNCTION set_to_subinventory
397 (
398 p_subinventory_code IN NUMBER
399 ) RETURN BOOLEAN IS
400 BEGIN
401
402 tosubinventory_code := p_subinventory_code;
403 RETURN TRUE;
404
405 EXCEPTION
406 WHEN OTHERS THEN
407 RETURN False;
408 END;
409
410 --4171297
411 FUNCTION set_oola_rec
412 (
413 p_order_line_id IN NUMBER
414 ) RETURN BOOLEAN IS
415 l_return_val BOOLEAN := FALSE;
416 BEGIN
417 IF (oola_rec.line_id = p_order_line_id) THEN
418 l_return_val := TRUE;
419 ELSE
420 SELECT *
421 INTO oola_rec
422 FROM oe_order_lines_all
423 WHERE line_id = p_order_line_id;
424 l_return_val := TRUE;
425 END IF;
426 RETURN l_return_val;
427
428 EXCEPTION
429 WHEN NO_DATA_FOUND THEN
430 RETURN l_return_val;
431 WHEN OTHERS THEN
432 RETURN l_return_val;
433 END;
434
435
436 -- Bug# 4258360: Added for R12 Crossdock Pegging Project
437 -- Retrieve the picking batch record for a pick release batch
438 FUNCTION set_wpb_rec
439 (p_batch_id IN NUMBER,
440 p_request_number IN VARCHAR2
441 ) RETURN BOOLEAN IS
442 BEGIN
443 IF (p_batch_id IS NULL AND p_request_number IS NULL) THEN
444 -- At least one value must be inputted
445 RETURN FALSE;
446 ELSIF (p_batch_id IS NOT NULL AND wpb_rec.batch_id = p_batch_id) THEN
447 -- Value is already cached
448 RETURN TRUE;
449 ELSIF (p_request_number IS NOT NULL AND wpb_rec.name = p_request_number) THEN
450 -- Value is already cached
451 RETURN TRUE;
452 ELSIF (p_batch_id IS NOT NULL) THEN
453 -- Query and cache the value based on p_batch_id
454 SELECT *
455 INTO wpb_rec
456 FROM wsh_picking_batches
457 WHERE batch_id = p_batch_id;
458 RETURN TRUE;
459 ELSIF (p_request_number IS NOT NULL) THEN
460 -- Query and cache the value based on p_request_number
461 SELECT *
462 INTO wpb_rec
463 FROM wsh_picking_batches
464 WHERE name = p_request_number;
465 RETURN TRUE;
466 ELSE
467 -- Should not reach this condition
468 RETURN FALSE;
469 END IF;
470
471 EXCEPTION
472 WHEN OTHERS THEN
473 RETURN FALSE;
474 END;
475
476 -- Onhand Material Status Support
477 -- Cache locator record.
478 FUNCTION set_loc_rec
479 (
480 p_organization_id IN NUMBER,
481 p_locator_id IN VARCHAR2
482 ) RETURN BOOLEAN IS
483
484 l_return_val BOOLEAN := FALSE;
485 BEGIN
486 IF loc_rec.organization_id = p_organization_id and
487 loc_rec.inventory_location_id = p_locator_id THEN
488 l_return_val := TRUE;
489 ELSE
490 SELECT *
491 INTO loc_rec
492 FROM MTL_ITEM_LOCATIONS
493 WHERE inventory_location_id = p_locator_id
494 AND organization_id = p_organization_id;
495
496 l_return_val := TRUE;
497 END IF;
498
499 RETURN l_return_val;
500
501 EXCEPTION
502 WHEN NO_DATA_FOUND THEN
503 RETURN l_return_val;
504 WHEN OTHERS THEN
505 RETURN l_return_val;
506 END set_loc_rec;
507
508 -- Onhand Material Status Support
509 -- Cache status_id of MOQD for a given SKU.
510 FUNCTION set_moqd_status_rec
511 (
512 p_organization_id IN NUMBER,
513 p_inventory_item_id IN NUMBER,
514 p_sub_code IN VARCHAR2,
515 p_loc_id IN NUMBER,
516 p_lot_number IN VARCHAR2,
517 p_lpn_id IN NUMBER
518 ) RETURN BOOLEAN IS
519
520 l_return_val BOOLEAN := FALSE;
521
522 BEGIN
523 IF moqd_rec.organization_id = p_organization_id and
524 moqd_rec.inventory_item_id = p_inventory_item_id and
525 moqd_rec.subinventory_code = p_sub_code and
526 nvl(moqd_rec.locator_id, -9999) = nvl(p_loc_id, -9999) and
527 nvl(moqd_rec.lot_number, '@@@@') = nvl(p_lot_number, '@@@@') and
528 nvl(moqd_rec.lpn_id, -9999) = nvl(p_lpn_id, -9999) THEN
529
530 l_return_val := TRUE;
531 ELSE
532 SELECT status_id
533 INTO moqd_rec.status_id
534 FROM MTL_ONHAND_QUANTITIES_DETAIL
535 WHERE inventory_item_id = p_inventory_item_id
536 AND organization_id = p_organization_id
537 AND subinventory_code = p_sub_code
538 AND nvl( locator_id, -9999) =nvl(p_loc_id, -9999)
539 AND nvl(lot_number, '@@@@') = nvl(p_lot_number, '@@@@')
540 AND nvl(lpn_id, -9999) = nvl(p_lpn_id, -9999)
541 AND rownum = 1;
542
543 l_return_val := TRUE;
544 END IF;
545
546 RETURN l_return_val;
547
548 EXCEPTION
549 WHEN NO_DATA_FOUND THEN
550 RETURN l_return_val;
551 WHEN OTHERS THEN
552 RETURN l_return_val;
553 END set_moqd_status_rec;
554
555
556
557 END inv_cache;
[Home] [Help]