[Home] [Help]
PACKAGE BODY: APPS.INV_MWB_STATUS_TREE
Source
1 PACKAGE BODY INV_MWB_STATUS_TREE AS
2 /* $Header: INVMWSTB.pls 120.12 2010/12/08 14:09:06 ksaripal 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 -- Bug 6429880
220 OR inv_mwb_globals.g_unit_number IS NOT NULL -- Bug 9486070
221 THEN
222 make_common_queries('MSN_QUERY');
223 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
224 'msn.current_subinventory_code';
225
226 inv_mwb_query_manager.add_from_clause('mtl_secondary_inventories msi', 'ONHAND');
227 inv_mwb_query_manager.add_where_clause(
228 'msi.secondary_inventory_name = msn.current_subinventory_code',
229 'ONHAND'
230 );
231 inv_mwb_query_manager.add_where_clause(
232 'msi.organization_id = msn.current_organization_id',
233 'ONHAND'
234 );
235 inv_mwb_query_manager.add_group_clause('msn.current_subinventory_code', 'ONHAND');
236 inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
237
238 ELSE
239 make_common_queries('MOQD');
240 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
241 'moqd.subinventory_code';
242 inv_mwb_query_manager.add_from_clause('mtl_secondary_inventories msi', 'ONHAND');
243 inv_mwb_query_manager.add_where_clause(
244 'msi.secondary_inventory_name = moqd.subinventory_code',
245 'ONHAND'
246 );
247 inv_mwb_query_manager.add_where_clause(
248 'msi.organization_id = moqd.organization_id',
249 'ONHAND'
250 );
251 inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
252 inv_mwb_query_manager.add_qf_where_onhand('ONHAND');
253
254 END IF;
255 inv_mwb_query_manager.add_where_clause(
256 'msi.organization_id = :onh_tree_organization_id',
257 'ONHAND'
258 );
259 inv_mwb_query_manager.add_where_clause(
260 'msi.status_id = :onh_tree_status_id',
261 'ONHAND'
262 );
263 inv_mwb_query_manager.add_bind_variable(
264 'onh_tree_status_id',
265 inv_mwb_globals.g_tree_st_id
266 );
267 inv_mwb_query_manager.add_bind_variable(
268 'onh_tree_organization_id',
269 inv_mwb_globals.g_tree_organization_id
270 );
271 inv_mwb_query_manager.execute_query;
272
273 END IF;
274 EXCEPTION
275 WHEN no_data_found THEN
276 NULL;
277 END sub_folder_node_event;
278
279
280 PROCEDURE loc_folder_node_event (
281 x_node_value IN OUT NOCOPY NUMBER
282 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
283 , x_tbl_index IN OUT NOCOPY NUMBER
284 ) IS
285
286 l_procedure_name VARCHAR2(30);
287
288 BEGIN
289
290 l_procedure_name := 'LOC_FOLDER_NODE_EVENT';
291
292 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_EXPANDED' THEN
293 inv_mwb_tree1.add_locs(
294 x_node_value
295 , x_node_tbl
296 , x_tbl_index
297 );
298
299 ELSIF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
300
301 IF ( inv_mwb_globals.g_serial_from IS NOT NULL AND
302 inv_mwb_globals.g_serial_to IS NOT NULL )
303 OR inv_mwb_globals.g_serial_attr_query IS NOT NULL -- Bug 6429880
304 OR inv_mwb_globals.g_unit_number IS NOT NULL -- Bug 9486070
305 THEN
306 make_common_queries('MSN_QUERY');
307 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
308 'msn.current_locator_id';
309
310 inv_mwb_query_manager.add_from_clause('mtl_item_locations mil', 'ONHAND');
311 inv_mwb_query_manager.add_where_clause(
312 'mil.inventory_location_id = msn.current_locator_id',
313 'ONHAND'
314 );
315 inv_mwb_query_manager.add_where_clause(
316 'mil.subinventory_code = msn.current_subinventory_code',
317 'ONHAND'
318 );
319 inv_mwb_query_manager.add_where_clause(
320 'mil.organization_id = msn.current_organization_id',
321 'ONHAND'
322 );
323 inv_mwb_query_manager.add_group_clause('msn.current_locator_id', 'ONHAND');
324 inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
325
326 ELSE
327 make_common_queries('MOQD');
328 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
329 'moqd.locator_id';
330 inv_mwb_query_manager.add_from_clause('mtl_item_locations mil', 'ONHAND');
331 inv_mwb_query_manager.add_where_clause(
332 'mil.inventory_location_id = moqd.locator_id',
333 'ONHAND'
334 );
335 inv_mwb_query_manager.add_where_clause(
336 'mil.subinventory_code = moqd.subinventory_code',
337 'ONHAND'
338 );
339 inv_mwb_query_manager.add_where_clause(
340 'mil.organization_id = moqd.organization_id',
341 'ONHAND'
342 );
343 inv_mwb_query_manager.add_group_clause('moqd.locator_id', 'ONHAND');
344 inv_mwb_query_manager.add_qf_where_onhand('ONHAND');
345
346 END IF;
347 inv_mwb_query_manager.add_where_clause(
348 'mil.organization_id = :onh_tree_organization_id',
349 'ONHAND'
350 );
351 inv_mwb_query_manager.add_where_clause(
352 'mil.status_id = :onh_tree_status_id',
353 'ONHAND'
354 );
355 inv_mwb_query_manager.add_bind_variable(
356 'onh_tree_status_id',
357 inv_mwb_globals.g_tree_st_id
358 );
359 inv_mwb_query_manager.add_bind_variable(
360 'onh_tree_organization_id',
361 inv_mwb_globals.g_tree_organization_id
362 );
363 inv_mwb_query_manager.execute_query;
364
365 END IF;
366
367 EXCEPTION
368 WHEN no_data_found THEN
369 NULL;
370 END loc_folder_node_event;
371
372 PROCEDURE lot_folder_node_event (
373 x_node_value IN OUT NOCOPY NUMBER
374 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
375 , x_tbl_index IN OUT NOCOPY NUMBER
376 ) IS
377 l_procedure_name VARCHAR2(30);
378
379 BEGIN
380
381 l_procedure_name := 'LOT_FOLDER_NODE_EVENT';
382
383 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_EXPANDED' THEN
384 inv_mwb_tree1.add_lots(
385 x_node_value
386 , x_node_tbl
387 , x_tbl_index
388 );
389
390 ELSIF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
391
392 IF ( inv_mwb_globals.g_serial_from IS NOT NULL AND
393 inv_mwb_globals.g_serial_to IS NOT NULL )
394 OR inv_mwb_globals.g_serial_attr_query IS NOT NULL -- Bug 6429880
395 OR inv_mwb_globals.g_unit_number IS NOT NULL -- Bug 9486070
396 THEN
397 make_common_queries('MSN_QUERY');
398 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
399 'msn.lot_number';
400 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ITEM_ID).column_value :=
401 'msn.inventory_item_id';
402 inv_mwb_query_manager.add_from_clause('mtl_lot_numbers mln', 'ONHAND');
403 inv_mwb_query_manager.add_where_clause(
404 'mln.lot_number = msn.lot_number',
405 'ONHAND'
406 );
407 inv_mwb_query_manager.add_where_clause(
408 'mln.organization_id = msn.current_organization_id',
409 'ONHAND'
410 );
411 inv_mwb_query_manager.add_where_clause(
412 'mln.inventory_item_id = msn.inventory_item_id',
413 'ONHAND'
414 );
415 inv_mwb_query_manager.add_group_clause('msn.lot_number', 'ONHAND');
416 inv_mwb_query_manager.add_group_clause('msn.inventory_item_id', 'ONHAND');
417 inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
418
419 ELSE
420 make_common_queries('MOQD');
421 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
422 'moqd.lot_number';
423 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ITEM_ID).column_value :=
424 'moqd.inventory_item_id';
425 inv_mwb_query_manager.add_from_clause('mtl_lot_numbers mln', 'ONHAND');
426 inv_mwb_query_manager.add_where_clause(
427 'mln.lot_number = moqd.lot_number',
428 'ONHAND'
429 );
430 inv_mwb_query_manager.add_where_clause(
431 'mln.organization_id = moqd.organization_id',
432 'ONHAND'
433 );
434 inv_mwb_query_manager.add_where_clause(
435 'mln.inventory_item_id = moqd.inventory_item_id',
436 'ONHAND'
437 );
438
439 inv_mwb_query_manager.add_group_clause('moqd.lot_number', 'ONHAND');
440 inv_mwb_query_manager.add_group_clause('moqd.inventory_item_id', 'ONHAND');
441 inv_mwb_query_manager.add_qf_where_onhand('ONHAND');
442
443 END IF;
444 inv_mwb_query_manager.add_where_clause(
445 'mln.organization_id = :onh_tree_organization_id',
446 'ONHAND'
447 );
448 inv_mwb_query_manager.add_where_clause(
449 'mln.status_id = :onh_tree_status_id',
450 'ONHAND'
451 );
452 inv_mwb_query_manager.add_bind_variable(
453 'onh_tree_status_id',
454 inv_mwb_globals.g_tree_st_id
455 );
456 inv_mwb_query_manager.add_bind_variable(
457 'onh_tree_organization_id',
458 inv_mwb_globals.g_tree_organization_id
459 );
460 inv_mwb_query_manager.execute_query;
461
462 END IF;
463
464 EXCEPTION
465 WHEN no_data_found THEN
466 NULL;
467 END lot_folder_node_event;
468
469 PROCEDURE serial_folder_node_event (
470 x_node_value IN OUT NOCOPY NUMBER
471 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
472 , x_tbl_index IN OUT NOCOPY NUMBER
473 ) IS
474
475 l_procedure_name VARCHAR2(30);
476
477 BEGIN
478
479 l_procedure_name := 'SERIAL_FOLDER_NODE_EVENT';
480
481 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_EXPANDED' THEN
482 inv_mwb_tree1.add_serials(
483 x_node_value
484 , x_node_tbl
485 , x_tbl_index
486 );
487
488 ELSIF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
489
490 make_common_queries('MSN');
491 inv_mwb_query_manager.add_where_clause(
492 'msn.current_organization_id = :onh_tree_organization_id',
493 'ONHAND'
494 );
495 inv_mwb_query_manager.add_where_clause(
496 'msn.status_id = :onh_tree_status_id',
497 'ONHAND'
498 );
499 inv_mwb_query_manager.add_bind_variable(
500 'onh_tree_status_id',
501 inv_mwb_globals.g_tree_st_id
502 );
503 inv_mwb_query_manager.add_bind_variable(
504 'onh_tree_organization_id',
505 inv_mwb_globals.g_tree_organization_id
506 );
507 inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
508 inv_mwb_query_manager.execute_query;
509
510 END IF; --tree event
511
512 EXCEPTION
513 WHEN no_data_found THEN
514 NULL;
515 END serial_folder_node_event;
516
517 PROCEDURE sub_node_event (
518 x_node_value IN OUT NOCOPY NUMBER
519 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
520 , x_tbl_index IN OUT NOCOPY NUMBER
521 ) IS
522
523 l_procedure_name VARCHAR2(30);
524
525 BEGIN
526
527 l_procedure_name := 'SUB_NODE_EVENT';
528
529 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
530
531 IF ( inv_mwb_globals.g_serial_from IS NOT NULL AND
532 inv_mwb_globals.g_serial_to IS NOT NULL )
533 OR inv_mwb_globals.g_serial_attr_query IS NOT NULL -- Bug 6429880
534 OR inv_mwb_globals.g_unit_number IS NOT NULL -- Bug 9486070
535 THEN
536
537 make_common_queries('MSN_QUERY');
538 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
539 'msn.current_subinventory_code';
540
541 inv_mwb_query_manager.add_from_clause('mtl_secondary_inventories msi', 'ONHAND');
542 inv_mwb_query_manager.add_where_clause(
543 'msi.secondary_inventory_name = msn.current_subinventory_code',
544 'ONHAND'
545 );
546 inv_mwb_query_manager.add_where_clause(
547 'msi.organization_id = msn.current_organization_id',
548 'ONHAND'
549 );
550 inv_mwb_query_manager.add_group_clause('msn.current_subinventory_code', 'ONHAND');
551 inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
552
553 ELSE
554 make_common_queries('MOQD');
555 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
556 'moqd.subinventory_code';
557 inv_mwb_query_manager.add_from_clause('mtl_secondary_inventories msi', 'ONHAND');
558 inv_mwb_query_manager.add_where_clause(
559 'msi.secondary_inventory_name = moqd.subinventory_code',
560 'ONHAND'
561 );
562 inv_mwb_query_manager.add_where_clause(
563 'msi.organization_id = moqd.organization_id',
564 'ONHAND'
565 );
566 inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
567 inv_mwb_query_manager.add_qf_where_onhand('ONHAND');
568
569 END IF;
570 inv_mwb_query_manager.add_where_clause(
571 'msi.secondary_inventory_name = :onh_tree_subinventory_code',
572 'ONHAND'
573 );
574 inv_mwb_query_manager.add_where_clause(
575 'msi.organization_id = :onh_tree_organization_id',
576 'ONHAND'
577 );
578 inv_mwb_query_manager.add_where_clause(
579 'msi.status_id = :onh_tree_status_id',
580 'ONHAND'
581 );
582 inv_mwb_query_manager.add_bind_variable(
583 'onh_tree_status_id',
584 inv_mwb_globals.g_tree_st_id
585 );
586 inv_mwb_query_manager.add_bind_variable(
587 'onh_tree_organization_id',
588 inv_mwb_globals.g_tree_organization_id
589 );
590 inv_mwb_query_manager.add_bind_variable(
591 'onh_tree_subinventory_code',
592 inv_mwb_globals.g_tree_subinventory_code
593 );
594 inv_mwb_query_manager.execute_query;
595
596 END IF;
597
598 EXCEPTION
599 WHEN no_data_found THEN
600 NULL;
601 END sub_node_event;
602
603 PROCEDURE loc_node_event (
604 x_node_value IN OUT NOCOPY NUMBER
605 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
606 , x_tbl_index IN OUT NOCOPY NUMBER
607 ) IS
608
609 l_procedure_name VARCHAR2(30);
610
611 BEGIN
612
613 l_procedure_name := 'LOC_NODE_EVENT';
614
615 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
616
617 IF ( inv_mwb_globals.g_serial_from IS NOT NULL AND
618 inv_mwb_globals.g_serial_to IS NOT NULL )
619 OR inv_mwb_globals.g_serial_attr_query IS NOT NULL -- Bug 6429880
620 OR inv_mwb_globals.g_unit_number IS NOT NULL -- Bug 9486070
621 THEN
622
623 make_common_queries('MSN_QUERY');
624 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
625 'msn.current_locator_id';
626
627 inv_mwb_query_manager.add_from_clause('mtl_item_locations mil', 'ONHAND');
628 inv_mwb_query_manager.add_where_clause(
629 'mil.inventory_location_id = msn.current_locator_id',
630 'ONHAND'
631 );
632 inv_mwb_query_manager.add_where_clause(
633 'mil.subinventory_code = msn.current_subinventory_code',
634 'ONHAND'
635 );
636 inv_mwb_query_manager.add_where_clause(
637 'mil.organization_id = msn.current_organization_id',
638 'ONHAND'
639 );
640 inv_mwb_query_manager.add_group_clause('msn.current_locator_id', 'ONHAND');
641 inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
642
643 ELSE
644 make_common_queries('MOQD');
645 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
646 'moqd.locator_id';
647 inv_mwb_query_manager.add_from_clause('mtl_item_locations mil', 'ONHAND');
648 inv_mwb_query_manager.add_where_clause(
649 'mil.inventory_location_id = moqd.locator_id',
650 'ONHAND'
651 );
652 inv_mwb_query_manager.add_where_clause(
653 'mil.subinventory_code = moqd.subinventory_code',
654 'ONHAND'
655 );
656 inv_mwb_query_manager.add_where_clause(
657 'mil.organization_id = moqd.organization_id',
658 'ONHAND'
659 );
660 inv_mwb_query_manager.add_group_clause('moqd.locator_id', 'ONHAND');
661 inv_mwb_query_manager.add_qf_where_onhand('ONHAND');
662
663 END IF;
664 inv_mwb_query_manager.add_where_clause(
665 'mil.inventory_location_id = :onh_tree_locator_id',
666 'ONHAND'
667 );
668 inv_mwb_query_manager.add_where_clause(
669 'mil.organization_id = :onh_tree_organization_id',
670 'ONHAND'
671 );
672 inv_mwb_query_manager.add_where_clause(
673 'mil.status_id = :onh_tree_status_id',
674 'ONHAND'
675 );
676 inv_mwb_query_manager.add_bind_variable(
677 'onh_tree_status_id',
678 inv_mwb_globals.g_tree_st_id
679 );
680 inv_mwb_query_manager.add_bind_variable(
681 'onh_tree_organization_id',
682 inv_mwb_globals.g_tree_organization_id
683 );
684 inv_mwb_query_manager.add_bind_variable(
685 'onh_tree_locator_id',
686 inv_mwb_globals.g_tree_loc_id
687 );
688 inv_mwb_query_manager.execute_query;
689
690 END IF;
691
692 EXCEPTION
693 WHEN no_data_found THEN
694 NULL;
695 END loc_node_event;
696
697 PROCEDURE lot_node_event (
698 x_node_value IN OUT NOCOPY NUMBER
699 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
700 , x_tbl_index IN OUT NOCOPY NUMBER
701 ) IS
702
703 l_procedure_name VARCHAR2(30);
704
705 BEGIN
706
707 l_procedure_name := 'LOT_NODE_EVENT';
708
709 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
710
711 IF ( inv_mwb_globals.g_serial_from IS NOT NULL AND
712 inv_mwb_globals.g_serial_to IS NOT NULL )
713 OR inv_mwb_globals.g_serial_attr_query IS NOT NULL -- Bug 6429880
714 OR inv_mwb_globals.g_unit_number IS NOT NULL -- Bug 9486070
715 THEN
716
717 make_common_queries('MSN_QUERY');
718 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
719 'msn.lot_number';
720 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ITEM_ID).column_value :=
721 'msn.inventory_item_id';
722
723 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
724 'msn.current_subinventory_code';--VARAJAGO
725
726 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
727 'msn.current_locator_id';--VARAJAGO
728
729 inv_mwb_query_manager.add_from_clause('mtl_lot_numbers mln', 'ONHAND');
730 inv_mwb_query_manager.add_where_clause(
731 'mln.lot_number = msn.lot_number',
732 'ONHAND'
733 );
734 inv_mwb_query_manager.add_where_clause(
735 'mln.organization_id = msn.current_organization_id',
736 'ONHAND'
737 );
738 inv_mwb_query_manager.add_where_clause(
739 'mln.inventory_item_id = msn.inventory_item_id',
740 'ONHAND'
741 );
742 inv_mwb_query_manager.add_group_clause('msn.lot_number', 'ONHAND');
743 inv_mwb_query_manager.add_group_clause('msn.inventory_item_id', 'ONHAND');
744 inv_mwb_query_manager.add_group_clause('msn.current_subinventory_code', 'ONHAND');--VARAJAGO
745 inv_mwb_query_manager.add_group_clause('msn.current_locator_id', 'ONHAND');--VARAJAGO
746
747 inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
748
749 ELSE
750 make_common_queries('MOQD');
751 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
752 'moqd.lot_number';
753 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ITEM_ID).column_value :=
754 'moqd.inventory_item_id';
755
756 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
757 'moqd.subinventory_code';--VARAJAGO
758
759 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
760 'moqd.locator_id';--VARAJAGO
761
762
763 inv_mwb_query_manager.add_from_clause('mtl_lot_numbers mln', 'ONHAND');
764 inv_mwb_query_manager.add_where_clause(
765 'mln.lot_number = moqd.lot_number',
766 'ONHAND'
767 );
768 inv_mwb_query_manager.add_where_clause(
769 'mln.organization_id = moqd.organization_id',
770 'ONHAND'
771 );
772 inv_mwb_query_manager.add_where_clause(
773 'mln.inventory_item_id = moqd.inventory_item_id',
774 'ONHAND'
775 );
776
777 inv_mwb_query_manager.add_group_clause('moqd.lot_number', 'ONHAND');
778 inv_mwb_query_manager.add_group_clause('moqd.inventory_item_id', 'ONHAND');
779
780 inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');--VARAJAGO
781 inv_mwb_query_manager.add_group_clause('moqd.locator_id', 'ONHAND');--VARAJAGO
782
783 inv_mwb_query_manager.add_qf_where_onhand('ONHAND');
784
785 END IF;
786 inv_mwb_query_manager.add_where_clause(
787 'mln.lot_number = :onh_tree_lot_number',
788 'ONHAND'
789 );
790 inv_mwb_query_manager.add_where_clause(
791 'mln.organization_id = :onh_tree_organization_id',
792 'ONHAND'
793 );
794 inv_mwb_query_manager.add_where_clause(
795 'mln.status_id = :onh_tree_status_id',
796 'ONHAND'
797 );
798 inv_mwb_query_manager.add_bind_variable(
799 'onh_tree_status_id',
800 inv_mwb_globals.g_tree_st_id
801 );
802 inv_mwb_query_manager.add_bind_variable(
803 'onh_tree_organization_id',
804 inv_mwb_globals.g_tree_organization_id
805 );
806 inv_mwb_query_manager.add_bind_variable(
807 'onh_tree_lot_number',
808 inv_mwb_globals.g_tree_lot_number
809 );
810 inv_mwb_query_manager.execute_query;
811
812 END IF;
813
814 EXCEPTION
815 WHEN no_data_found THEN
816 NULL;
817 END lot_node_event;
818
819 PROCEDURE serial_node_event (
820 x_node_value IN OUT NOCOPY NUMBER
821 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
822 , x_tbl_index IN OUT NOCOPY NUMBER
823 ) IS
824
825 l_procedure_name VARCHAR2(30);
826
827 BEGIN
828
829 l_procedure_name := 'SERIAL_NODE_EVENT';
830
831 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
832
833 make_common_queries('MSN');
834 inv_mwb_query_manager.add_where_clause(
835 'msn.current_organization_id = :onh_tree_organization_id',
836 'ONHAND'
837 );
838 inv_mwb_query_manager.add_where_clause(
839 'msn.status_id = :onh_tree_status_id',
840 'ONHAND'
841 );
842 inv_mwb_query_manager.add_where_clause(
843 'msn.serial_number = :onh_tree_serial_number',
844 'ONHAND'
845 );
846 inv_mwb_query_manager.add_bind_variable(
847 'onh_tree_status_id',
848 inv_mwb_globals.g_tree_st_id
849 );
850 inv_mwb_query_manager.add_bind_variable(
851 'onh_tree_organization_id',
852 inv_mwb_globals.g_tree_organization_id
853 );
854 inv_mwb_query_manager.add_bind_variable(
855 'onh_tree_serial_number',
856 inv_mwb_globals.g_tree_serial_number
857 );
858 inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
859 inv_mwb_query_manager.execute_query;
860
861 END IF;
862
863 EXCEPTION
864 WHEN no_data_found THEN
865 NULL;
866 END serial_node_event;
867
868 PROCEDURE make_common_queries(p_flag IN VARCHAR2) IS
869 l_procedure_name VARCHAR2(30);
870 BEGIN
871 l_procedure_name := 'MAKE_COMMON_QUERIES';
872
873 CASE p_flag
874 WHEN 'MOQD' THEN
875
876 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ORG_ID).column_value :=
877 'moqd.organization_id';
878
879 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PRIMARY_UOM_CODE).column_value :=
880 'moqd.transaction_uom_code';
881
882 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ONHAND).column_value :=
883 'SUM(moqd.primary_transaction_quantity)';
884
885 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PACKED).column_value :=
886 'SUM(DECODE(moqd.containerized_flag, 1, moqd.primary_transaction_quantity, 0))';
887
888 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.UNPACKED).column_value :=
889 'SUM(DECODE(moqd.containerized_flag, 1, 0, moqd.primary_transaction_quantity))';
890
891 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SECONDARY_UOM_CODE).column_value :=
892 'moqd.secondary_uom_code';
893
894 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SECONDARY_ONHAND).column_value :=
895 'SUM(moqd.secondary_transaction_quantity)';
896
897 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SECONDARY_PACKED).column_value :=
898 'SUM(DECODE(moqd.containerized_flag, 1, moqd.secondary_transaction_quantity, 0))';
899
900 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SECONDARY_UNPACKED).column_value :=
901 'SUM(DECODE(moqd.containerized_flag, 1, 0, moqd.secondary_transaction_quantity))';
902
903 inv_mwb_query_manager.add_from_clause('mtl_onhand_quantities_detail moqd', 'ONHAND');
904
905 inv_mwb_query_manager.add_group_clause('moqd.organization_id', 'ONHAND');
906 inv_mwb_query_manager.add_group_clause('moqd.transaction_uom_code', 'ONHAND');
907 inv_mwb_query_manager.add_group_clause('moqd.secondary_uom_code', 'ONHAND');
908
909 WHEN 'MSN_QUERY' THEN
910
911 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ORG_ID).column_value :=
912 'msn.current_organization_id';
913
914 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PRIMARY_UOM_CODE).column_value :=
915 '''Ea''';
916
917 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ONHAND).column_value := 1;
918
919 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PACKED).column_value :=
920 'SUM(DECODE(msn.lpn_id, NULL, 0,1))';
921
922 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.UNPACKED).column_value :=
923 'SUM(DECODE(msn.lpn_id, NULL, 1,0))';
924
925 inv_mwb_query_manager.add_from_clause('mtl_serial_numbers msn', 'ONHAND');
926
927 inv_mwb_query_manager.add_group_clause('msn.current_organization_id', 'ONHAND');
928 inv_mwb_query_manager.add_group_clause('''Ea''', 'ONHAND');
929
930
931 WHEN 'MSN' THEN
932
933 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ITEM_ID).column_value :=
934 'msn.inventory_item_id';
935
936 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
937 'msn.current_subinventory_code'; --VARAJAGO
938
939 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
940 'msn.current_locator_id'; --VARAJAGO
941
942 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ORG_ID).column_value :=
943 'msn.current_organization_id';
944
945 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PRIMARY_UOM_CODE).column_value :=
946 '''Ea''';
947
948 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ONHAND).column_value := 1;
949
950 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PACKED).column_value :=
951 'DECODE(msn.lpn_id, NULL, 0,1)';
952
953 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.UNPACKED).column_value :=
954 'DECODE(msn.lpn_id, NULL, 1,0)';
955
956 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SERIAL).column_value :=
957 'msn.serial_number';
958
959 inv_mwb_query_manager.add_from_clause('mtl_serial_numbers msn', 'ONHAND');
960
961 END CASE; -- p_flag
962
963 END make_common_queries;
964 --
965 -- public functions
966 --
967
968 PROCEDURE event (
969 x_node_value IN OUT NOCOPY NUMBER
970 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
971 , x_tbl_index IN OUT NOCOPY NUMBER
972 ) IS
973
974 l_procedure_name VARCHAR2(30);
975
976 BEGIN
977
978 l_procedure_name := 'EVENT';
979
980 inv_mwb_globals.print_msg( g_pkg_name, l_procedure_name, 'Entered');
981
982 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_EXPANDED'
983 OR inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
984
985 inv_mwb_globals.print_msg( g_pkg_name, l_procedure_name, 'Tree Node Type: '||inv_mwb_globals.g_tree_node_type);
986
987 CASE inv_mwb_globals.g_tree_node_type
988
989 WHEN 'APPTREE_OBJECT_TRUNK' THEN
990 root_node_event (
991 x_node_value
992 , x_node_tbl
993 , x_tbl_index
994 );
995 WHEN 'ORG' THEN
996 org_node_event (
997 x_node_value
998 , x_node_tbl
999 , x_tbl_index
1000 );
1001 WHEN 'SUB' THEN
1002 sub_node_event (
1003 x_node_value
1004 , x_node_tbl
1005 , x_tbl_index
1006 );
1007 WHEN 'LOC' THEN
1008 loc_node_event (
1009 x_node_value
1010 , x_node_tbl
1011 , x_tbl_index
1012 );
1013 WHEN 'LOT' THEN
1014 lot_node_event (
1015 x_node_value
1016 , x_node_tbl
1017 , x_tbl_index
1018 );
1019 WHEN 'SERIAL' THEN
1020 serial_node_event (
1021 x_node_value
1022 , x_node_tbl
1023 , x_tbl_index
1024 );
1025 WHEN 'SUB_FOLDER' THEN
1026 sub_folder_node_event (
1027 x_node_value
1028 , x_node_tbl
1029 , x_tbl_index
1030 );
1031 WHEN 'LOC_FOLDER' THEN
1032 loc_folder_node_event (
1033 x_node_value
1034 , x_node_tbl
1035 , x_tbl_index
1036 );
1037 WHEN 'LOT_FOLDER' THEN
1038 lot_folder_node_event (
1039 x_node_value
1040 , x_node_tbl
1041 , x_tbl_index
1042 );
1043 WHEN 'SERIAL_FOLDER' THEN
1044 serial_folder_node_event (
1045 x_node_value
1046 , x_node_tbl
1047 , x_tbl_index
1048 );
1049 WHEN 'STATUS' THEN
1050 status_node_event (
1051 x_node_value
1052 , x_node_tbl
1053 , x_tbl_index
1054 );
1055
1056 -- Onhand Material Status support
1057 WHEN 'ONHAND_FOLDER' THEN
1058 onhand_node_event (
1059 x_node_value
1060 , x_node_tbl
1061 , x_tbl_index
1062 );
1063
1064 END CASE;
1065 END IF;
1066 EXCEPTION
1067 WHEN no_data_found THEN
1068 NULL;
1069 END event;
1070
1071 END INV_MWB_STATUS_TREE;