[Home] [Help]
PACKAGE BODY: APPS.INV_MWB_STATUS_TREE
Source
1 PACKAGE BODY INV_MWB_STATUS_TREE AS
2 /* $Header: INVMWSTB.pls 120.11 2008/01/11 09:16:45 musinha ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'INV_MWB_STATUS_TREE';
5
6 --
7 -- private functions
8 --
9 PROCEDURE make_common_queries(p_flag IN VARCHAR2);
10
11 PROCEDURE root_node_event (
12 x_node_value IN OUT NOCOPY NUMBER
13 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
14 , x_tbl_index IN OUT NOCOPY NUMBER
15 ) IS
16
17 l_procedure_name VARCHAR2(30);
18
19 BEGIN
20
21 l_procedure_name := 'ROOT_NODE_EVENT';
22
23 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_EXPANDED' THEN
24 inv_mwb_tree1.add_statuses(
25 x_node_value
26 , x_node_tbl
27 , x_tbl_index
28 );
29 END IF;
30
31 EXCEPTION
32 WHEN no_data_found THEN
33 NULL;
34 END root_node_event;
35
36 PROCEDURE status_node_event (
37 x_node_value IN OUT NOCOPY NUMBER
38 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
39 , x_tbl_index IN OUT NOCOPY NUMBER
40 ) IS
41
42 l_procedure_name VARCHAR2(30);
43
44 BEGIN
45
46 l_procedure_name := 'STATUS_NODE_EVENT';
47
48 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_EXPANDED' THEN
49 inv_mwb_tree1.add_orgs(
50 x_node_value
51 , x_node_tbl
52 , x_tbl_index
53 );
54 END IF;
55
56 EXCEPTION
57 WHEN no_data_found THEN
58 NULL;
59 END status_node_event;
60
61 -- Onhand Material Status support
62 PROCEDURE onhand_node_event (
63 x_node_value IN OUT NOCOPY NUMBER
64 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
65 , x_tbl_index IN OUT NOCOPY NUMBER
66 ) IS
67
68 l_procedure_name VARCHAR2(30);
69
70 BEGIN
71
72 l_procedure_name := 'ONHAND_NODE_EVENT';
73
74 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
75 make_common_queries('MOQD');
76 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ITEM_ID).column_value :=
77 'moqd.inventory_item_id';
78 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.REVISION).column_value :=
79 'moqd.revision';
80 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
81 'moqd.subinventory_code';
82 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
83 'moqd.locator_id';
84 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
85 'moqd.lot_number';
86 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
87 'moqd.lpn_id';
88 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.STATUS_ID).column_value :=
89 'moqd.status_id';
90
91 inv_mwb_query_manager.add_group_clause('moqd.inventory_item_id', 'ONHAND');
92 inv_mwb_query_manager.add_group_clause('moqd.revision', 'ONHAND');
93 inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
94 inv_mwb_query_manager.add_group_clause('moqd.locator_id', 'ONHAND');
95 inv_mwb_query_manager.add_group_clause('moqd.lot_number', 'ONHAND');
96 inv_mwb_query_manager.add_group_clause('moqd.lpn_id', 'ONHAND');
97 inv_mwb_query_manager.add_group_clause('moqd.status_id', 'ONHAND');
98
99 inv_mwb_query_manager.add_qf_where_onhand('ONHAND');
100 inv_mwb_query_manager.add_where_clause(
101 'moqd.status_id = :onh_tree_status_id',
102 'ONHAND'
103 );
104 inv_mwb_query_manager.add_bind_variable(
105 'onh_tree_status_id',
106 inv_mwb_globals.g_tree_st_id
107 );
108 inv_mwb_query_manager.execute_query;
109
110 END IF;
111
112 EXCEPTION
113 WHEN no_data_found THEN
114 NULL;
115 END onhand_node_event;
116
117 PROCEDURE org_node_event (
118 x_node_value IN OUT NOCOPY NUMBER
119 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
120 , x_tbl_index IN OUT NOCOPY NUMBER
121 ) IS
122 l_procedure_name VARCHAR2(30);
123
124 i NUMBER := 1;
125 j number := 1;
126 l_default_status_id NUMBER; -- Onhand Material Status Support
127 BEGIN
128
129 l_procedure_name := 'ORG_NODE_EVENT';
130
131 if (inv_cache.set_org_rec(inv_mwb_globals.g_tree_organization_id)) then
132 l_default_status_id := inv_cache.org_rec.default_status_id;
133 end if;
134
135 inv_mwb_globals.print_msg( g_pkg_name, l_procedure_name, 'default org status id: '||l_default_status_id);
136
137 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_EXPANDED' THEN
138
139 If l_default_status_id is not null then -- Onhand Material Status Support
140 x_node_tbl(i).state := -1;
141 x_node_tbl(i).depth := 1;
142 x_node_tbl(i).label := 'Onhand';
143 x_node_tbl(i).icon := 'affldhdr';
144 x_node_tbl(i).value := 'ONHAND_FOLDER';
145 x_node_tbl(i).type := 'ONHAND_FOLDER';
146 i := i + 1;
147
148 x_node_tbl(i).state := -1;
149 x_node_tbl(i).depth := 1;
150 x_node_tbl(i).label := 'Serials';
151 x_node_tbl(i).icon := 'affldhdr';
152 x_node_tbl(i).value := 'SERIAL_FOLDER';
153 x_node_tbl(i).type := 'SERIAL_FOLDER';
154 else
155 x_node_tbl(i).state := -1;
156 x_node_tbl(i).depth := 1;
157 x_node_tbl(i).label := 'Subinventories';
158 x_node_tbl(i).icon := 'affldhdr';
159 x_node_tbl(i).value := 'SUB_FOLDER';
160 x_node_tbl(i).type := 'SUB_FOLDER';
161 i := i + 1;
162
163 x_node_tbl(i).state := -1;
164 x_node_tbl(i).depth := 1;
165 x_node_tbl(i).label := 'Locators';
166 x_node_tbl(i).icon := 'affldhdr';
167 x_node_tbl(i).value := 'LOC_FOLDER';
168 x_node_tbl(i).type := 'LOC_FOLDER';
169 i := i + 1;
170
171 x_node_tbl(i).state := -1;
172 x_node_tbl(i).depth := 1;
173 x_node_tbl(i).label := 'Lots';
174 x_node_tbl(i).icon := 'affldhdr';
175 x_node_tbl(i).value := 'LOT_FOLDER';
176 x_node_tbl(i).type := 'LOT_FOLDER';
177 i := i + 1;
178
179 x_node_tbl(i).state := -1;
180 x_node_tbl(i).depth := 1;
181 x_node_tbl(i).label := 'Serials';
182 x_node_tbl(i).icon := 'affldhdr';
183 x_node_tbl(i).value := 'SERIAL_FOLDER';
184 x_node_tbl(i).type := 'SERIAL_FOLDER';
185 END IF;
186 ELSIF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
187
188 NULL;
189
190 END IF;
191 EXCEPTION
192 WHEN no_data_found THEN
193 NULL;
194 END org_node_event;
195
196 PROCEDURE sub_folder_node_event (
197 x_node_value IN OUT NOCOPY NUMBER
198 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
199 , x_tbl_index IN OUT NOCOPY NUMBER
200 ) IS
201
202 l_procedure_name VARCHAR2(30);
203
204 BEGIN
205
206 l_procedure_name := 'SUB_FOLDER_NODE_EVENT';
207
208 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_EXPANDED' THEN
209 inv_mwb_tree1.add_subs(
210 x_node_value
211 , x_node_tbl
212 , x_tbl_index
213 );
214
215 ELSIF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
216
217 IF ( inv_mwb_globals.g_serial_from IS NOT NULL AND
218 inv_mwb_globals.g_serial_to IS NOT NULL )
219 OR inv_mwb_globals.g_serial_attr_query IS NOT NULL THEN -- Bug 6429880
220
221 make_common_queries('MSN_QUERY');
222 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
223 'msn.current_subinventory_code';
224
225 inv_mwb_query_manager.add_from_clause('mtl_secondary_inventories msi', 'ONHAND');
226 inv_mwb_query_manager.add_where_clause(
227 'msi.secondary_inventory_name = msn.current_subinventory_code',
228 'ONHAND'
229 );
230 inv_mwb_query_manager.add_where_clause(
231 'msi.organization_id = msn.current_organization_id',
232 'ONHAND'
233 );
234 inv_mwb_query_manager.add_group_clause('msn.current_subinventory_code', 'ONHAND');
235 inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
236
237 ELSE
238 make_common_queries('MOQD');
239 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
240 'moqd.subinventory_code';
241 inv_mwb_query_manager.add_from_clause('mtl_secondary_inventories msi', 'ONHAND');
242 inv_mwb_query_manager.add_where_clause(
243 'msi.secondary_inventory_name = moqd.subinventory_code',
244 'ONHAND'
245 );
246 inv_mwb_query_manager.add_where_clause(
247 'msi.organization_id = moqd.organization_id',
248 'ONHAND'
249 );
250 inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
251 inv_mwb_query_manager.add_qf_where_onhand('ONHAND');
252
253 END IF;
254 inv_mwb_query_manager.add_where_clause(
255 'msi.organization_id = :onh_tree_organization_id',
256 'ONHAND'
257 );
258 inv_mwb_query_manager.add_where_clause(
259 'msi.status_id = :onh_tree_status_id',
260 'ONHAND'
261 );
262 inv_mwb_query_manager.add_bind_variable(
263 'onh_tree_status_id',
264 inv_mwb_globals.g_tree_st_id
265 );
266 inv_mwb_query_manager.add_bind_variable(
267 'onh_tree_organization_id',
268 inv_mwb_globals.g_tree_organization_id
269 );
270 inv_mwb_query_manager.execute_query;
271
272 END IF;
273 EXCEPTION
274 WHEN no_data_found THEN
275 NULL;
276 END sub_folder_node_event;
277
278
279 PROCEDURE loc_folder_node_event (
280 x_node_value IN OUT NOCOPY NUMBER
281 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
282 , x_tbl_index IN OUT NOCOPY NUMBER
283 ) IS
284
285 l_procedure_name VARCHAR2(30);
286
287 BEGIN
288
289 l_procedure_name := 'LOC_FOLDER_NODE_EVENT';
290
291 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_EXPANDED' THEN
292 inv_mwb_tree1.add_locs(
293 x_node_value
294 , x_node_tbl
295 , x_tbl_index
296 );
297
298 ELSIF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
299
300 IF ( inv_mwb_globals.g_serial_from IS NOT NULL AND
301 inv_mwb_globals.g_serial_to IS NOT NULL )
302 OR inv_mwb_globals.g_serial_attr_query IS NOT NULL THEN -- Bug 6429880
303
304 make_common_queries('MSN_QUERY');
305 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
306 'msn.current_locator_id';
307
308 inv_mwb_query_manager.add_from_clause('mtl_item_locations mil', 'ONHAND');
309 inv_mwb_query_manager.add_where_clause(
310 'mil.inventory_location_id = msn.current_locator_id',
311 'ONHAND'
312 );
313 inv_mwb_query_manager.add_where_clause(
314 'mil.subinventory_code = msn.current_subinventory_code',
315 'ONHAND'
316 );
317 inv_mwb_query_manager.add_where_clause(
318 'mil.organization_id = msn.current_organization_id',
319 'ONHAND'
320 );
321 inv_mwb_query_manager.add_group_clause('msn.current_locator_id', 'ONHAND');
322 inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
323
324 ELSE
325 make_common_queries('MOQD');
326 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
327 'moqd.locator_id';
328 inv_mwb_query_manager.add_from_clause('mtl_item_locations mil', 'ONHAND');
329 inv_mwb_query_manager.add_where_clause(
330 'mil.inventory_location_id = moqd.locator_id',
331 'ONHAND'
332 );
333 inv_mwb_query_manager.add_where_clause(
334 'mil.subinventory_code = moqd.subinventory_code',
335 'ONHAND'
336 );
337 inv_mwb_query_manager.add_where_clause(
338 'mil.organization_id = moqd.organization_id',
339 'ONHAND'
340 );
341 inv_mwb_query_manager.add_group_clause('moqd.locator_id', 'ONHAND');
342 inv_mwb_query_manager.add_qf_where_onhand('ONHAND');
343
344 END IF;
345 inv_mwb_query_manager.add_where_clause(
346 'mil.organization_id = :onh_tree_organization_id',
347 'ONHAND'
348 );
349 inv_mwb_query_manager.add_where_clause(
350 'mil.status_id = :onh_tree_status_id',
351 'ONHAND'
352 );
353 inv_mwb_query_manager.add_bind_variable(
354 'onh_tree_status_id',
355 inv_mwb_globals.g_tree_st_id
356 );
357 inv_mwb_query_manager.add_bind_variable(
358 'onh_tree_organization_id',
359 inv_mwb_globals.g_tree_organization_id
360 );
361 inv_mwb_query_manager.execute_query;
362
363 END IF;
364
365 EXCEPTION
366 WHEN no_data_found THEN
367 NULL;
368 END loc_folder_node_event;
369
370 PROCEDURE lot_folder_node_event (
371 x_node_value IN OUT NOCOPY NUMBER
372 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
373 , x_tbl_index IN OUT NOCOPY NUMBER
374 ) IS
375 l_procedure_name VARCHAR2(30);
376
377 BEGIN
378
379 l_procedure_name := 'LOT_FOLDER_NODE_EVENT';
380
381 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_EXPANDED' THEN
382 inv_mwb_tree1.add_lots(
383 x_node_value
384 , x_node_tbl
385 , x_tbl_index
386 );
387
388 ELSIF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
389
390 IF ( inv_mwb_globals.g_serial_from IS NOT NULL AND
391 inv_mwb_globals.g_serial_to IS NOT NULL )
392 OR inv_mwb_globals.g_serial_attr_query IS NOT NULL THEN -- Bug 6429880
393
394 make_common_queries('MSN_QUERY');
395 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
396 'msn.lot_number';
397 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ITEM_ID).column_value :=
398 'msn.inventory_item_id';
399 inv_mwb_query_manager.add_from_clause('mtl_lot_numbers mln', 'ONHAND');
400 inv_mwb_query_manager.add_where_clause(
401 'mln.lot_number = msn.lot_number',
402 'ONHAND'
403 );
404 inv_mwb_query_manager.add_where_clause(
405 'mln.organization_id = msn.current_organization_id',
406 'ONHAND'
407 );
408 inv_mwb_query_manager.add_where_clause(
409 'mln.inventory_item_id = msn.inventory_item_id',
410 'ONHAND'
411 );
412 inv_mwb_query_manager.add_group_clause('msn.lot_number', 'ONHAND');
413 inv_mwb_query_manager.add_group_clause('msn.inventory_item_id', 'ONHAND');
414 inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
415
416 ELSE
417 make_common_queries('MOQD');
418 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
419 'moqd.lot_number';
420 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ITEM_ID).column_value :=
421 'moqd.inventory_item_id';
422 inv_mwb_query_manager.add_from_clause('mtl_lot_numbers mln', 'ONHAND');
423 inv_mwb_query_manager.add_where_clause(
424 'mln.lot_number = moqd.lot_number',
425 'ONHAND'
426 );
427 inv_mwb_query_manager.add_where_clause(
428 'mln.organization_id = moqd.organization_id',
429 'ONHAND'
430 );
431 inv_mwb_query_manager.add_where_clause(
432 'mln.inventory_item_id = moqd.inventory_item_id',
433 'ONHAND'
434 );
435
436 inv_mwb_query_manager.add_group_clause('moqd.lot_number', 'ONHAND');
437 inv_mwb_query_manager.add_group_clause('moqd.inventory_item_id', 'ONHAND');
438 inv_mwb_query_manager.add_qf_where_onhand('ONHAND');
439
440 END IF;
441 inv_mwb_query_manager.add_where_clause(
442 'mln.organization_id = :onh_tree_organization_id',
443 'ONHAND'
444 );
445 inv_mwb_query_manager.add_where_clause(
446 'mln.status_id = :onh_tree_status_id',
447 'ONHAND'
448 );
449 inv_mwb_query_manager.add_bind_variable(
450 'onh_tree_status_id',
451 inv_mwb_globals.g_tree_st_id
452 );
453 inv_mwb_query_manager.add_bind_variable(
454 'onh_tree_organization_id',
455 inv_mwb_globals.g_tree_organization_id
456 );
457 inv_mwb_query_manager.execute_query;
458
459 END IF;
460
461 EXCEPTION
462 WHEN no_data_found THEN
463 NULL;
464 END lot_folder_node_event;
465
466 PROCEDURE serial_folder_node_event (
467 x_node_value IN OUT NOCOPY NUMBER
468 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
469 , x_tbl_index IN OUT NOCOPY NUMBER
470 ) IS
471
472 l_procedure_name VARCHAR2(30);
473
474 BEGIN
475
476 l_procedure_name := 'SERIAL_FOLDER_NODE_EVENT';
477
478 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_EXPANDED' THEN
479 inv_mwb_tree1.add_serials(
480 x_node_value
481 , x_node_tbl
482 , x_tbl_index
483 );
484
485 ELSIF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
486
487 make_common_queries('MSN');
488 inv_mwb_query_manager.add_where_clause(
489 'msn.current_organization_id = :onh_tree_organization_id',
490 'ONHAND'
491 );
492 inv_mwb_query_manager.add_where_clause(
493 'msn.status_id = :onh_tree_status_id',
494 'ONHAND'
495 );
496 inv_mwb_query_manager.add_bind_variable(
497 'onh_tree_status_id',
498 inv_mwb_globals.g_tree_st_id
499 );
500 inv_mwb_query_manager.add_bind_variable(
501 'onh_tree_organization_id',
502 inv_mwb_globals.g_tree_organization_id
503 );
504 inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
505 inv_mwb_query_manager.execute_query;
506
507 END IF; --tree event
508
509 EXCEPTION
510 WHEN no_data_found THEN
511 NULL;
512 END serial_folder_node_event;
513
514 PROCEDURE sub_node_event (
515 x_node_value IN OUT NOCOPY NUMBER
516 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
517 , x_tbl_index IN OUT NOCOPY NUMBER
518 ) IS
519
520 l_procedure_name VARCHAR2(30);
521
522 BEGIN
523
524 l_procedure_name := 'SUB_NODE_EVENT';
525
526 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
527
528 IF ( inv_mwb_globals.g_serial_from IS NOT NULL AND
529 inv_mwb_globals.g_serial_to IS NOT NULL )
530 OR inv_mwb_globals.g_serial_attr_query IS NOT NULL THEN -- Bug 6429880
531
532 make_common_queries('MSN_QUERY');
533 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
534 'msn.current_subinventory_code';
535
536 inv_mwb_query_manager.add_from_clause('mtl_secondary_inventories msi', 'ONHAND');
537 inv_mwb_query_manager.add_where_clause(
538 'msi.secondary_inventory_name = msn.current_subinventory_code',
539 'ONHAND'
540 );
541 inv_mwb_query_manager.add_where_clause(
542 'msi.organization_id = msn.current_organization_id',
543 'ONHAND'
544 );
545 inv_mwb_query_manager.add_group_clause('msn.current_subinventory_code', 'ONHAND');
546 inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
547
548 ELSE
549 make_common_queries('MOQD');
550 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
551 'moqd.subinventory_code';
552 inv_mwb_query_manager.add_from_clause('mtl_secondary_inventories msi', 'ONHAND');
553 inv_mwb_query_manager.add_where_clause(
554 'msi.secondary_inventory_name = moqd.subinventory_code',
555 'ONHAND'
556 );
557 inv_mwb_query_manager.add_where_clause(
558 'msi.organization_id = moqd.organization_id',
559 'ONHAND'
560 );
561 inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
562 inv_mwb_query_manager.add_qf_where_onhand('ONHAND');
563
564 END IF;
565 inv_mwb_query_manager.add_where_clause(
566 'msi.secondary_inventory_name = :onh_tree_subinventory_code',
567 'ONHAND'
568 );
569 inv_mwb_query_manager.add_where_clause(
570 'msi.organization_id = :onh_tree_organization_id',
571 'ONHAND'
572 );
573 inv_mwb_query_manager.add_where_clause(
574 'msi.status_id = :onh_tree_status_id',
575 'ONHAND'
576 );
577 inv_mwb_query_manager.add_bind_variable(
578 'onh_tree_status_id',
579 inv_mwb_globals.g_tree_st_id
580 );
581 inv_mwb_query_manager.add_bind_variable(
582 'onh_tree_organization_id',
583 inv_mwb_globals.g_tree_organization_id
584 );
585 inv_mwb_query_manager.add_bind_variable(
586 'onh_tree_subinventory_code',
587 inv_mwb_globals.g_tree_subinventory_code
588 );
589 inv_mwb_query_manager.execute_query;
590
591 END IF;
592
593 EXCEPTION
594 WHEN no_data_found THEN
595 NULL;
596 END sub_node_event;
597
598 PROCEDURE loc_node_event (
599 x_node_value IN OUT NOCOPY NUMBER
600 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
601 , x_tbl_index IN OUT NOCOPY NUMBER
602 ) IS
603
604 l_procedure_name VARCHAR2(30);
605
606 BEGIN
607
608 l_procedure_name := 'LOC_NODE_EVENT';
609
610 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
611
612 IF ( inv_mwb_globals.g_serial_from IS NOT NULL AND
613 inv_mwb_globals.g_serial_to IS NOT NULL )
614 OR inv_mwb_globals.g_serial_attr_query IS NOT NULL THEN -- Bug 6429880
615
616 make_common_queries('MSN_QUERY');
617 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
618 'msn.current_locator_id';
619
620 inv_mwb_query_manager.add_from_clause('mtl_item_locations mil', 'ONHAND');
621 inv_mwb_query_manager.add_where_clause(
622 'mil.inventory_location_id = msn.current_locator_id',
623 'ONHAND'
624 );
625 inv_mwb_query_manager.add_where_clause(
626 'mil.subinventory_code = msn.current_subinventory_code',
627 'ONHAND'
628 );
629 inv_mwb_query_manager.add_where_clause(
630 'mil.organization_id = msn.current_organization_id',
631 'ONHAND'
632 );
633 inv_mwb_query_manager.add_group_clause('msn.current_locator_id', 'ONHAND');
634 inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
635
636 ELSE
637 make_common_queries('MOQD');
638 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
639 'moqd.locator_id';
640 inv_mwb_query_manager.add_from_clause('mtl_item_locations mil', 'ONHAND');
641 inv_mwb_query_manager.add_where_clause(
642 'mil.inventory_location_id = moqd.locator_id',
643 'ONHAND'
644 );
645 inv_mwb_query_manager.add_where_clause(
646 'mil.subinventory_code = moqd.subinventory_code',
647 'ONHAND'
648 );
649 inv_mwb_query_manager.add_where_clause(
650 'mil.organization_id = moqd.organization_id',
651 'ONHAND'
652 );
653 inv_mwb_query_manager.add_group_clause('moqd.locator_id', 'ONHAND');
654 inv_mwb_query_manager.add_qf_where_onhand('ONHAND');
655
656 END IF;
657 inv_mwb_query_manager.add_where_clause(
658 'mil.inventory_location_id = :onh_tree_locator_id',
659 'ONHAND'
660 );
661 inv_mwb_query_manager.add_where_clause(
662 'mil.organization_id = :onh_tree_organization_id',
663 'ONHAND'
664 );
665 inv_mwb_query_manager.add_where_clause(
666 'mil.status_id = :onh_tree_status_id',
667 'ONHAND'
668 );
669 inv_mwb_query_manager.add_bind_variable(
670 'onh_tree_status_id',
671 inv_mwb_globals.g_tree_st_id
672 );
673 inv_mwb_query_manager.add_bind_variable(
674 'onh_tree_organization_id',
675 inv_mwb_globals.g_tree_organization_id
676 );
677 inv_mwb_query_manager.add_bind_variable(
678 'onh_tree_locator_id',
679 inv_mwb_globals.g_tree_loc_id
680 );
681 inv_mwb_query_manager.execute_query;
682
683 END IF;
684
685 EXCEPTION
686 WHEN no_data_found THEN
687 NULL;
688 END loc_node_event;
689
690 PROCEDURE lot_node_event (
691 x_node_value IN OUT NOCOPY NUMBER
692 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
693 , x_tbl_index IN OUT NOCOPY NUMBER
694 ) IS
695
696 l_procedure_name VARCHAR2(30);
697
698 BEGIN
699
700 l_procedure_name := 'LOT_NODE_EVENT';
701
702 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
703
704 IF ( inv_mwb_globals.g_serial_from IS NOT NULL AND
705 inv_mwb_globals.g_serial_to IS NOT NULL )
706 OR inv_mwb_globals.g_serial_attr_query IS NOT NULL THEN -- Bug 6429880
707
708 make_common_queries('MSN_QUERY');
709 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
710 'msn.lot_number';
711 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ITEM_ID).column_value :=
712 'msn.inventory_item_id';
713
714 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
715 'msn.current_subinventory_code';--VARAJAGO
716
717 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
718 'msn.current_locator_id';--VARAJAGO
719
720 inv_mwb_query_manager.add_from_clause('mtl_lot_numbers mln', 'ONHAND');
721 inv_mwb_query_manager.add_where_clause(
722 'mln.lot_number = msn.lot_number',
723 'ONHAND'
724 );
725 inv_mwb_query_manager.add_where_clause(
726 'mln.organization_id = msn.current_organization_id',
727 'ONHAND'
728 );
729 inv_mwb_query_manager.add_where_clause(
730 'mln.inventory_item_id = msn.inventory_item_id',
731 'ONHAND'
732 );
733 inv_mwb_query_manager.add_group_clause('msn.lot_number', 'ONHAND');
734 inv_mwb_query_manager.add_group_clause('msn.inventory_item_id', 'ONHAND');
735 inv_mwb_query_manager.add_group_clause('msn.current_subinventory_code', 'ONHAND');--VARAJAGO
736 inv_mwb_query_manager.add_group_clause('msn.current_locator_id', 'ONHAND');--VARAJAGO
737
738 inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
739
740 ELSE
741 make_common_queries('MOQD');
742 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
743 'moqd.lot_number';
744 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ITEM_ID).column_value :=
745 'moqd.inventory_item_id';
746
747 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
748 'moqd.subinventory_code';--VARAJAGO
749
750 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
751 'moqd.locator_id';--VARAJAGO
752
753
754 inv_mwb_query_manager.add_from_clause('mtl_lot_numbers mln', 'ONHAND');
755 inv_mwb_query_manager.add_where_clause(
756 'mln.lot_number = moqd.lot_number',
757 'ONHAND'
758 );
759 inv_mwb_query_manager.add_where_clause(
760 'mln.organization_id = moqd.organization_id',
761 'ONHAND'
762 );
763 inv_mwb_query_manager.add_where_clause(
764 'mln.inventory_item_id = moqd.inventory_item_id',
765 'ONHAND'
766 );
767
768 inv_mwb_query_manager.add_group_clause('moqd.lot_number', 'ONHAND');
769 inv_mwb_query_manager.add_group_clause('moqd.inventory_item_id', 'ONHAND');
770
771 inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');--VARAJAGO
772 inv_mwb_query_manager.add_group_clause('moqd.locator_id', 'ONHAND');--VARAJAGO
773
774 inv_mwb_query_manager.add_qf_where_onhand('ONHAND');
775
776 END IF;
777 inv_mwb_query_manager.add_where_clause(
778 'mln.lot_number = :onh_tree_lot_number',
779 'ONHAND'
780 );
781 inv_mwb_query_manager.add_where_clause(
782 'mln.organization_id = :onh_tree_organization_id',
783 'ONHAND'
784 );
785 inv_mwb_query_manager.add_where_clause(
786 'mln.status_id = :onh_tree_status_id',
787 'ONHAND'
788 );
789 inv_mwb_query_manager.add_bind_variable(
790 'onh_tree_status_id',
791 inv_mwb_globals.g_tree_st_id
792 );
793 inv_mwb_query_manager.add_bind_variable(
794 'onh_tree_organization_id',
795 inv_mwb_globals.g_tree_organization_id
796 );
797 inv_mwb_query_manager.add_bind_variable(
798 'onh_tree_lot_number',
799 inv_mwb_globals.g_tree_lot_number
800 );
801 inv_mwb_query_manager.execute_query;
802
803 END IF;
804
805 EXCEPTION
806 WHEN no_data_found THEN
807 NULL;
808 END lot_node_event;
809
810 PROCEDURE serial_node_event (
811 x_node_value IN OUT NOCOPY NUMBER
812 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
813 , x_tbl_index IN OUT NOCOPY NUMBER
814 ) IS
815
816 l_procedure_name VARCHAR2(30);
817
818 BEGIN
819
820 l_procedure_name := 'SERIAL_NODE_EVENT';
821
822 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
823
824 make_common_queries('MSN');
825 inv_mwb_query_manager.add_where_clause(
826 'msn.current_organization_id = :onh_tree_organization_id',
827 'ONHAND'
828 );
829 inv_mwb_query_manager.add_where_clause(
830 'msn.status_id = :onh_tree_status_id',
831 'ONHAND'
832 );
833 inv_mwb_query_manager.add_where_clause(
834 'msn.serial_number = :onh_tree_serial_number',
835 'ONHAND'
836 );
837 inv_mwb_query_manager.add_bind_variable(
838 'onh_tree_status_id',
839 inv_mwb_globals.g_tree_st_id
840 );
841 inv_mwb_query_manager.add_bind_variable(
842 'onh_tree_organization_id',
843 inv_mwb_globals.g_tree_organization_id
844 );
845 inv_mwb_query_manager.add_bind_variable(
846 'onh_tree_serial_number',
847 inv_mwb_globals.g_tree_serial_number
848 );
849 inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
850 inv_mwb_query_manager.execute_query;
851
852 END IF;
853
854 EXCEPTION
855 WHEN no_data_found THEN
856 NULL;
857 END serial_node_event;
858
859 PROCEDURE make_common_queries(p_flag IN VARCHAR2) IS
860 l_procedure_name VARCHAR2(30);
861 BEGIN
862 l_procedure_name := 'MAKE_COMMON_QUERIES';
863
864 CASE p_flag
865 WHEN 'MOQD' THEN
866
867 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ORG_ID).column_value :=
868 'moqd.organization_id';
869
870 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PRIMARY_UOM_CODE).column_value :=
871 'moqd.transaction_uom_code';
872
873 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ONHAND).column_value :=
874 'SUM(moqd.primary_transaction_quantity)';
875
876 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PACKED).column_value :=
877 'SUM(DECODE(moqd.containerized_flag, 1, moqd.primary_transaction_quantity, 0))';
878
879 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.UNPACKED).column_value :=
880 'SUM(DECODE(moqd.containerized_flag, 1, 0, moqd.primary_transaction_quantity))';
881
882 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SECONDARY_UOM_CODE).column_value :=
883 'moqd.secondary_uom_code';
884
885 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SECONDARY_ONHAND).column_value :=
886 'SUM(moqd.secondary_transaction_quantity)';
887
888 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SECONDARY_PACKED).column_value :=
889 'SUM(DECODE(moqd.containerized_flag, 1, moqd.secondary_transaction_quantity, 0))';
890
891 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SECONDARY_UNPACKED).column_value :=
892 'SUM(DECODE(moqd.containerized_flag, 1, 0, moqd.secondary_transaction_quantity))';
893
894 inv_mwb_query_manager.add_from_clause('mtl_onhand_quantities_detail moqd', 'ONHAND');
895
896 inv_mwb_query_manager.add_group_clause('moqd.organization_id', 'ONHAND');
897 inv_mwb_query_manager.add_group_clause('moqd.transaction_uom_code', 'ONHAND');
898 inv_mwb_query_manager.add_group_clause('moqd.secondary_uom_code', 'ONHAND');
899
900 WHEN 'MSN_QUERY' THEN
901
902 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ORG_ID).column_value :=
903 'msn.current_organization_id';
904
905 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PRIMARY_UOM_CODE).column_value :=
906 '''Ea''';
907
908 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ONHAND).column_value := 1;
909
910 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PACKED).column_value :=
911 'SUM(DECODE(msn.lpn_id, NULL, 0,1))';
912
913 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.UNPACKED).column_value :=
914 'SUM(DECODE(msn.lpn_id, NULL, 1,0))';
915
916 inv_mwb_query_manager.add_from_clause('mtl_serial_numbers msn', 'ONHAND');
917
918 inv_mwb_query_manager.add_group_clause('msn.current_organization_id', 'ONHAND');
919 inv_mwb_query_manager.add_group_clause('''Ea''', 'ONHAND');
920
921
922 WHEN 'MSN' THEN
923
924 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ITEM_ID).column_value :=
925 'msn.inventory_item_id';
926
927 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
928 'msn.current_subinventory_code'; --VARAJAGO
929
930 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
931 'msn.current_locator_id'; --VARAJAGO
932
933 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ORG_ID).column_value :=
934 'msn.current_organization_id';
935
936 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PRIMARY_UOM_CODE).column_value :=
937 '''Ea''';
938
939 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ONHAND).column_value := 1;
940
941 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PACKED).column_value :=
942 'DECODE(msn.lpn_id, NULL, 0,1)';
943
944 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.UNPACKED).column_value :=
945 'DECODE(msn.lpn_id, NULL, 1,0)';
946
947 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SERIAL).column_value :=
948 'msn.serial_number';
949
950 inv_mwb_query_manager.add_from_clause('mtl_serial_numbers msn', 'ONHAND');
951
952 END CASE; -- p_flag
953
954 END make_common_queries;
955 --
956 -- public functions
957 --
958
959 PROCEDURE event (
960 x_node_value IN OUT NOCOPY NUMBER
961 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
962 , x_tbl_index IN OUT NOCOPY NUMBER
963 ) IS
964
965 l_procedure_name VARCHAR2(30);
966
967 BEGIN
968
969 l_procedure_name := 'EVENT';
970
971 inv_mwb_globals.print_msg( g_pkg_name, l_procedure_name, 'Entered');
972
973 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_EXPANDED'
974 OR inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
975
976 inv_mwb_globals.print_msg( g_pkg_name, l_procedure_name, 'Tree Node Type: '||inv_mwb_globals.g_tree_node_type);
977
978 CASE inv_mwb_globals.g_tree_node_type
979
980 WHEN 'APPTREE_OBJECT_TRUNK' THEN
981 root_node_event (
982 x_node_value
983 , x_node_tbl
984 , x_tbl_index
985 );
986 WHEN 'ORG' THEN
987 org_node_event (
988 x_node_value
989 , x_node_tbl
990 , x_tbl_index
991 );
992 WHEN 'SUB' THEN
993 sub_node_event (
994 x_node_value
995 , x_node_tbl
996 , x_tbl_index
997 );
998 WHEN 'LOC' THEN
999 loc_node_event (
1000 x_node_value
1001 , x_node_tbl
1002 , x_tbl_index
1003 );
1004 WHEN 'LOT' THEN
1005 lot_node_event (
1006 x_node_value
1007 , x_node_tbl
1008 , x_tbl_index
1009 );
1010 WHEN 'SERIAL' THEN
1011 serial_node_event (
1012 x_node_value
1013 , x_node_tbl
1014 , x_tbl_index
1015 );
1016 WHEN 'SUB_FOLDER' THEN
1017 sub_folder_node_event (
1018 x_node_value
1019 , x_node_tbl
1020 , x_tbl_index
1021 );
1022 WHEN 'LOC_FOLDER' THEN
1023 loc_folder_node_event (
1024 x_node_value
1025 , x_node_tbl
1026 , x_tbl_index
1027 );
1028 WHEN 'LOT_FOLDER' THEN
1029 lot_folder_node_event (
1030 x_node_value
1031 , x_node_tbl
1032 , x_tbl_index
1033 );
1034 WHEN 'SERIAL_FOLDER' THEN
1035 serial_folder_node_event (
1036 x_node_value
1037 , x_node_tbl
1038 , x_tbl_index
1039 );
1040 WHEN 'STATUS' THEN
1041 status_node_event (
1042 x_node_value
1043 , x_node_tbl
1044 , x_tbl_index
1045 );
1046
1047 -- Onhand Material Status support
1048 WHEN 'ONHAND_FOLDER' THEN
1049 onhand_node_event (
1050 x_node_value
1051 , x_node_tbl
1052 , x_tbl_index
1053 );
1054
1055 END CASE;
1056 END IF;
1057 EXCEPTION
1058 WHEN no_data_found THEN
1059 NULL;
1060 END event;
1061
1062 END INV_MWB_STATUS_TREE;