[Home] [Help]
PACKAGE BODY: APPS.INV_MWB_LOT_TREE
Source
1 PACKAGE BODY INV_MWB_LOT_TREE AS
2 /* $Header: INVMWLOB.pls 120.12 2011/06/14 09:40:29 nimisra ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'INV_MWB_LOT_TREE';
5 --
6 -- private functions
7 --
8 -- PROCEDURE make_common_queries(p_flag VARCHAR2); -- Bug 6060233
9
10 PROCEDURE root_node_event (
11 x_node_value IN OUT NOCOPY NUMBER
12 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
13 , x_tbl_index IN OUT NOCOPY NUMBER
14 ) IS
15
16 l_procedure_name VARCHAR2(30);
17
18 BEGIN
19
20 l_procedure_name := 'ROOT_NODE_EVENT';
21
22 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_EXPANDED' THEN
23 inv_mwb_tree1.add_orgs(
24 x_node_value
25 , x_node_tbl
26 , x_tbl_index
27 );
28
29 ELSIF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
30
31 IF inv_mwb_globals.g_serial_from IS NOT NULL OR
32 inv_mwb_globals.g_serial_to IS NOT NULL OR
33 inv_mwb_globals.g_status_id IS NOT NULL -- Bug 6060233
34 OR inv_mwb_globals.g_serial_attr_query IS NOT NULL -- Bug 6429880
35 OR inv_mwb_globals.g_unit_number IS NOT NULL -- Bug 9365452
36 THEN
37 make_common_queries('MSN_QUERY');
38 inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
39 ELSE
40 make_common_queries('MOQD');
41 inv_mwb_query_manager.add_qf_where_onhand('ONHAND');
42 END IF;
43 inv_mwb_query_manager.execute_query;
44
45 END IF; -- g_tree_event
46 EXCEPTION
47 WHEN no_data_found THEN
48 NULL;
49 END root_node_event;
50
51 PROCEDURE org_node_event (
52 x_node_value IN OUT NOCOPY NUMBER
53 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
54 , x_tbl_index IN OUT NOCOPY NUMBER
55 ) IS
56 l_procedure_name VARCHAR2(30);
57
58 BEGIN
59
60 l_procedure_name := 'ORG_NODE_EVENT';
61
62 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_EXPANDED' THEN
63
64 inv_mwb_tree1.add_lots(
65 x_node_value
66 , x_node_tbl
67 , x_tbl_index
68 );
69
70 ELSIF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
71
72 IF inv_mwb_globals.g_serial_from IS NOT NULL OR
73 inv_mwb_globals.g_serial_to IS NOT NULL
74 OR inv_mwb_globals.g_status_id IS NOT NULL -- Bug 6060233
75 OR inv_mwb_globals.g_serial_attr_query IS NOT NULL -- Bug 6429880
76 OR inv_mwb_globals.g_unit_number IS NOT NULL -- Bug 9365452
77 THEN
78 make_common_queries('MSN_QUERY');
79 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
80 'msn.lot_number';
81 inv_mwb_query_manager.add_where_clause(
82 'msn.current_organization_id = :onh_tree_organization_id' ,
83 'ONHAND'
84 );
85 inv_mwb_query_manager.add_group_clause(
86 'msn.lot_number' ,
87 'ONHAND'
88 );
89 inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
90 ELSE
91 make_common_queries('MOQD');
92 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
93 'moqd.lot_number';
94 inv_mwb_query_manager.add_where_clause(
95 'moqd.organization_id = :onh_tree_organization_id' ,
96 'ONHAND'
97 );
98 inv_mwb_query_manager.add_group_clause(
99 'moqd.lot_number' ,
100 'ONHAND'
101 );
102 inv_mwb_query_manager.add_qf_where_onhand('ONHAND');
103 END IF; -- serial
104 inv_mwb_query_manager.add_bind_variable(
105 'onh_tree_organization_id',
106 inv_mwb_globals.g_tree_organization_id
107 );
108 inv_mwb_query_manager.execute_query;
109
110 END IF; -- g_tree_event
111
112 EXCEPTION
113 WHEN no_data_found THEN
114 NULL;
115 END org_node_event;
116
117 PROCEDURE lot_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
123 l_procedure_name VARCHAR2(30);
124
125 BEGIN
126
127 l_procedure_name := 'LOT_NODE_EVENT';
128 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_EXPANDED' THEN
129 inv_mwb_tree1.add_items(
130 x_node_value
131 , x_node_tbl
132 , x_tbl_index
133 );
134
135 ELSIF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
136
137 IF inv_mwb_globals.g_serial_from IS NOT NULL OR
138 inv_mwb_globals.g_serial_to IS NOT NULL
139 OR inv_mwb_globals.g_status_id IS NOT NULL -- Bug 6060233
140 OR inv_mwb_globals.g_serial_attr_query IS NOT NULL -- Bug 6429880
141 OR inv_mwb_globals.g_unit_number IS NOT NULL -- Bug 9365452
142 THEN
143 make_common_queries('MSN_QUERY');
144 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
145 'msn.lot_number';
146 inv_mwb_query_manager.add_where_clause(
147 'msn.current_organization_id = :onh_tree_organization_id' ,
148 'ONHAND'
149 );
150 inv_mwb_query_manager.add_where_clause(
151 'msn.lot_number = :onh_tree_lot_number' ,
152 'ONHAND'
153 );
154 inv_mwb_query_manager.add_group_clause(
155 'msn.lot_number' ,
156 'ONHAND'
157 );
158 inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
159 ELSE
160 make_common_queries('MOQD');
161 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
162 'moqd.lot_number';
163 inv_mwb_query_manager.add_where_clause(
164 'moqd.organization_id = :onh_tree_organization_id' ,
165 'ONHAND'
166 );
167 inv_mwb_query_manager.add_where_clause(
168 'moqd.lot_number = :onh_tree_lot_number' ,
169 'ONHAND'
170 );
171 inv_mwb_query_manager.add_group_clause(
172 'moqd.lot_number' ,
173 'ONHAND'
174 );
175
176 inv_mwb_query_manager.add_qf_where_onhand('ONHAND');
177 END IF; -- serial
178 inv_mwb_query_manager.add_bind_variable(
179 'onh_tree_organization_id',
180 inv_mwb_globals.g_tree_organization_id
181 );
182 inv_mwb_query_manager.add_bind_variable(
183 'onh_tree_lot_number',
184 inv_mwb_globals.g_tree_lot_number
185 );
186 inv_mwb_query_manager.execute_query;
187
188 END IF;
189
190 EXCEPTION
191 WHEN no_data_found THEN
192 NULL;
193 END lot_node_event;
194
195 PROCEDURE item_node_event (
196 x_node_value IN OUT NOCOPY NUMBER
197 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
198 , x_tbl_index IN OUT NOCOPY NUMBER
199 ) IS
200 l_procedure_name VARCHAR2(30);
201
202 l_rev_control NUMBER;
203 l_serial_control NUMBER;
204
205 BEGIN
206
207 l_procedure_name := 'ITEM_NODE_EVENT';
208 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_EXPANDED' THEN
209 inv_mwb_tree1.add_revs(
210 x_node_value
211 , x_node_tbl
212 , x_tbl_index
213 );
214
215 IF x_tbl_index = 1 THEN
216 inv_mwb_tree1.add_serials(
217 x_node_value
218 , x_node_tbl
219 , x_tbl_index
220 );
221 END IF;
222
223 ELSIF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
224
225 SELECT revision_qty_control_code,
226 serial_number_control_code
227 INTO l_rev_control,
228 l_serial_control
229 FROM mtl_system_items
230 WHERE organization_id = inv_mwb_globals.g_tree_organization_id
231 AND inventory_item_id = inv_mwb_globals.g_tree_item_id;
232
233 IF NVL(l_rev_control, 1) = 1 AND l_serial_control IN (2,5) THEN
234 make_common_queries('MSN');
235 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
236 'msn.lot_number';
237 inv_mwb_query_manager.add_where_clause(
238 'msn.current_organization_id = :onh_tree_organization_id' ,
239 'ONHAND'
240 );
241 inv_mwb_query_manager.add_where_clause(
242 'msn.lot_number = :onh_tree_lot_number' ,
243 'ONHAND'
244 );
245 inv_mwb_query_manager.add_where_clause(
246 'msn.inventory_item_id = :onh_tree_inventory_item_id' ,
247 'ONHAND'
248 );
249 inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
250
251 ELSE -- item is not serial controlled
252
253 IF inv_mwb_globals.g_serial_from IS NOT NULL OR
254 inv_mwb_globals.g_serial_to IS NOT NULL
255 OR inv_mwb_globals.g_status_id IS NOT NULL -- Bug 6060233
256 OR inv_mwb_globals.g_serial_attr_query IS NOT NULL -- Bug 6429880
257 OR inv_mwb_globals.g_unit_number IS NOT NULL -- Bug 9365452
258 THEN
259 make_common_queries('MSN_QUERY');
260 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
261 'msn.lot_number';
262 IF l_rev_control = 2 THEN
263 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
264 'msn.revision';
265 inv_mwb_query_manager.add_group_clause(
266 'msn.revision' ,
267 'ONHAND'
268 );
269 END IF;
270 inv_mwb_query_manager.add_where_clause(
271 'msn.current_organization_id = :onh_tree_organization_id' ,
272 'ONHAND'
273 );
274 inv_mwb_query_manager.add_where_clause(
275 'msn.lot_number = :onh_tree_lot_number' ,
276 'ONHAND'
277 );
278 inv_mwb_query_manager.add_where_clause(
279 'msn.inventory_item_id = :onh_tree_inventory_item_id' ,
280 'ONHAND'
281 );
282 inv_mwb_query_manager.add_group_clause(
283 'msn.lot_number' ,
284 'ONHAND'
285 );
286 inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
287 ELSE
288 make_common_queries('MOQD');
289 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
290 'moqd.lot_number';
291 IF l_rev_control = 2 THEN
292 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
293 'moqd.revision';
294 inv_mwb_query_manager.add_group_clause(
295 'moqd.revision' ,
296 'ONHAND'
297 );
298 END IF;
299 inv_mwb_query_manager.add_where_clause(
300 'moqd.organization_id = :onh_tree_organization_id' ,
301 'ONHAND'
302 );
303 inv_mwb_query_manager.add_where_clause(
304 'moqd.lot_number = :onh_tree_lot_number' ,
305 'ONHAND'
306 );
307 inv_mwb_query_manager.add_where_clause(
308 'moqd.inventory_item_id = :onh_tree_inventory_item_id' ,
309 'ONHAND'
310 );
311 inv_mwb_query_manager.add_group_clause(
312 'moqd.lot_number' ,
313 'ONHAND'
314 );
315
316 inv_mwb_query_manager.add_qf_where_onhand('ONHAND');
317 END IF; -- serial
318
319 END IF; -- item is not serial controlled
320 inv_mwb_query_manager.add_bind_variable(
321 'onh_tree_organization_id',
322 inv_mwb_globals.g_tree_organization_id
323 );
324 inv_mwb_query_manager.add_bind_variable(
325 'onh_tree_lot_number',
326 inv_mwb_globals.g_tree_lot_number
327 );
328 inv_mwb_query_manager.add_bind_variable(
329 'onh_tree_inventory_item_id',
330 inv_mwb_globals.g_tree_item_id
331 );
332 inv_mwb_query_manager.execute_query;
333
334 END IF; -- g_tree_Event
335
336 EXCEPTION
337 WHEN no_data_found THEN
338 NULL;
339 END item_node_event;
340
341 PROCEDURE rev_node_event (
342 x_node_value IN OUT NOCOPY NUMBER
343 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
344 , x_tbl_index IN OUT NOCOPY NUMBER
345 ) IS
346 l_serial_control NUMBER;
347 l_procedure_name VARCHAR2(30);
348
349 BEGIN
350
351 l_procedure_name := 'REV_NODE_EVENT';
352
353 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_EXPANDED' THEN
354
355 inv_mwb_tree1.add_serials(
356 x_node_value
357 , x_node_tbl
358 , x_tbl_index
359 );
360
361 ELSIF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
362
363 SELECT serial_number_control_code
364 INTO l_serial_control
365 FROM mtl_system_items
366 WHERE organization_id = inv_mwb_globals.g_tree_organization_id
367 AND inventory_item_id = inv_mwb_globals.g_tree_item_id;
368
369 IF l_serial_control in ( 2,5 ) THEN
370 make_common_queries('MSN');
371 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
372 'msn.lot_number';
373 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SERIAL).column_value :=
374 'msn.serial_number';
375 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
376 'msn.revision';
377 inv_mwb_query_manager.add_where_clause(
378 'msn.revision = :onh_tree_revision' ,
379 'ONHAND'
380 );
381 inv_mwb_query_manager.add_where_clause(
382 'msn.current_organization_id = :onh_tree_organization_id' ,
383 'ONHAND'
384 );
385 inv_mwb_query_manager.add_where_clause(
386 'msn.lot_number = :onh_tree_lot_number' ,
387 'ONHAND'
388 );
389 inv_mwb_query_manager.add_where_clause(
390 'msn.inventory_item_id = :onh_tree_inventory_item_id' ,
391 'ONHAND'
392 );
393 inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
394
395 ELSE -- not serial item
396 make_common_queries('MOQD');
397 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
398 'moqd.lot_number';
399 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
400 'moqd.revision';
401 inv_mwb_query_manager.add_where_clause(
402 'moqd.revision = :onh_tree_revision' ,
403 'ONHAND'
404 );
405 inv_mwb_query_manager.add_where_clause(
406 'moqd.organization_id = :onh_tree_organization_id' ,
407 'ONHAND'
408 );
409 inv_mwb_query_manager.add_where_clause(
410 'moqd.lot_number = :onh_tree_lot_number' ,
411 'ONHAND'
412 );
413 inv_mwb_query_manager.add_where_clause(
414 'moqd.inventory_item_id = :onh_tree_inventory_item_id' ,
415 'ONHAND'
416 );
417 inv_mwb_query_manager.add_group_clause(
418 'moqd.lot_number',
419 'ONHAND'
420 );
421 inv_mwb_query_manager.add_group_clause(
422 'moqd.revision',
423 'ONHAND'
424 );
425
426 inv_mwb_query_manager.add_qf_where_onhand('ONHAND');
427 END IF;
428 inv_mwb_query_manager.add_bind_variable(
429 'onh_tree_organization_id',
430 inv_mwb_globals.g_tree_organization_id
431 );
432 inv_mwb_query_manager.add_bind_variable(
433 'onh_tree_lot_number',
434 inv_mwb_globals.g_tree_lot_number
435 );
436 inv_mwb_query_manager.add_bind_variable(
437 'onh_tree_inventory_item_id',
438 inv_mwb_globals.g_tree_item_id
439 );
440 inv_mwb_query_manager.add_bind_variable(
441 'onh_tree_revision',
442 inv_mwb_globals.g_tree_rev
443 );
444 inv_mwb_query_manager.execute_query;
445
446 END IF; -- g_tree
447
448 EXCEPTION
449 WHEN no_data_found THEN
450 NULL;
451 END rev_node_event;
452
453 PROCEDURE serial_node_event (
454 x_node_value IN OUT NOCOPY NUMBER
455 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
456 , x_tbl_index IN OUT NOCOPY NUMBER
457 ) IS
458
459 l_procedure_name VARCHAR2(30);
460 l_rev_control NUMBER;
461
462 BEGIN
463
464 l_procedure_name := 'SERIAL_NODE_EVENT';
465
466 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
467
468 SELECT revision_qty_control_code
469 INTO l_rev_control
470 FROM mtl_system_items
471 WHERE organization_id = inv_mwb_globals.g_tree_organization_id
472 AND inventory_item_id = inv_mwb_globals.g_tree_item_id;
473
474 make_common_queries('MSN');
475 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
476 'msn.lot_number';
477 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SERIAL).column_value :=
478 'msn.serial_number';
479 IF l_rev_control = 2 THEN
480 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
481 'msn.revision';
482 inv_mwb_query_manager.add_where_clause(
483 'msn.revision = :onh_tree_revision' ,
484 'ONHAND'
485 );
486 inv_mwb_query_manager.add_bind_variable(
487 'onh_tree_revision',
488 inv_mwb_globals.g_tree_rev
489 );
490 END IF;
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.lot_number = :onh_tree_lot_number' ,
497 'ONHAND'
498 );
499 inv_mwb_query_manager.add_where_clause(
500 'msn.inventory_item_id = :onh_tree_inventory_item_id' ,
501 'ONHAND'
502 );
503 inv_mwb_query_manager.add_where_clause(
504 'msn.serial_number = :onh_tree_serial_number' ,
505 'ONHAND'
506 );
507 inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
508 inv_mwb_query_manager.add_bind_variable(
509 'onh_tree_organization_id',
510 inv_mwb_globals.g_tree_organization_id
511 );
512 inv_mwb_query_manager.add_bind_variable(
513 'onh_tree_lot_number',
514 inv_mwb_globals.g_tree_lot_number
515 );
516 inv_mwb_query_manager.add_bind_variable(
517 'onh_tree_inventory_item_id',
518 inv_mwb_globals.g_tree_item_id
519 );
520 inv_mwb_query_manager.add_bind_variable(
521 'onh_tree_serial_number',
522 inv_mwb_globals.g_tree_serial_number
523 );
524 inv_mwb_query_manager.execute_query;
525
526 END IF; -- g_tree
527
528 EXCEPTION
529 WHEN no_data_found THEN
530 NULL;
531 END serial_node_event;
532
533 PROCEDURE make_common_queries(p_flag VARCHAR2) IS
534
535 BEGIN
536
537 CASE p_flag
538 WHEN 'MSN' THEN
539 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ITEM_ID).column_value :=
540 'msn.inventory_item_id';
541
542 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ORG_ID).column_value :=
543 'msn.current_organization_id';
544
545 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PRIMARY_UOM_CODE).column_value :=
546 '''Ea''';
547
548 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ONHAND).column_value := 1;
549
550 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PACKED).column_value :=
551 'DECODE(msn.lpn_id, NULL, 0,1)';
552
553 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.UNPACKED).column_value :=
554 'DECODE(msn.lpn_id, NULL, 1,0)';
555
556 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SECONDARY_UOM_CODE).column_value :=
557 'NULL';
558
559 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SECONDARY_PACKED).column_value :=
560 'NULL';
561
562 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SECONDARY_UNPACKED).column_value :=
563 'NULL';
564
565 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SERIAL).column_value :=
566 'msn.serial_number';
567
568 inv_mwb_query_manager.add_from_clause('mtl_serial_numbers msn', 'ONHAND');
569
570 inv_mwb_query_manager.add_where_clause('msn.current_status = 3', 'ONHAND');
571
572 WHEN 'MOQD' THEN
573
574 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ITEM_ID).column_value :=
575 'moqd.inventory_item_id';
576
577 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ORG_ID).column_value :=
578 'moqd.organization_id';
579
580 -- inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PRIMARY_UOM_CODE).column_value :=
581 -- 'moqd.transaction_uom_code';
582
583 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ONHAND).column_value :=
584 'SUM(moqd.primary_transaction_quantity)';
585
586 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PACKED).column_value :=
587 'SUM(DECODE(moqd.containerized_flag, 1, moqd.primary_transaction_quantity, 0))';
588
589 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.UNPACKED).column_value :=
590 'SUM(DECODE(moqd.containerized_flag, 1, 0, moqd.primary_transaction_quantity))';
591
592 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SECONDARY_UOM_CODE).column_value :=
593 'moqd.secondary_uom_code';
594
595 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SECONDARY_ONHAND).column_value :=
596 'SUM(moqd.secondary_transaction_quantity)';
597
598 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SECONDARY_PACKED).column_value :=
599 'SUM(DECODE(moqd.containerized_flag, 1, moqd.secondary_transaction_quantity, 0))';
600
601 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SECONDARY_UNPACKED).column_value :=
602 'SUM(DECODE(moqd.containerized_flag, 1, 0, moqd.secondary_transaction_quantity))';
603
604 inv_mwb_query_manager.add_from_clause('mtl_onhand_quantities_detail moqd', 'ONHAND');
605 inv_mwb_query_manager.add_where_clause('moqd.lot_number is not null', 'ONHAND');
606
607 inv_mwb_query_manager.add_group_clause('moqd.inventory_item_id', 'ONHAND');
608 inv_mwb_query_manager.add_group_clause('moqd.organization_id', 'ONHAND');
609 -- inv_mwb_query_manager.add_group_clause('moqd.transaction_uom_code', 'ONHAND');
610 inv_mwb_query_manager.add_group_clause('moqd.secondary_uom_code', 'ONHAND');
611
612 WHEN 'MSN_QUERY' THEN
613 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ITEM_ID).column_value :=
614 'msn.inventory_item_id';
615 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ORG_ID).column_value :=
616 'msn.current_organization_id';
617 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PRIMARY_UOM_CODE).column_value :=
618 '''Ea''';
619 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ONHAND).column_value :=
620 'count(1)';
621 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PACKED).column_value :=
622 'sum(decode(msn.lpn_id,NULL,0, 1))';
623 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.UNPACKED).column_value :=
624 'sum(decode(msn.lpn_id,NULL,1, 0))';
625 inv_mwb_query_manager.add_from_clause('mtl_serial_numbers msn' , 'ONHAND');
626 inv_mwb_query_manager.add_where_clause('msn.lot_number is not null' , 'ONHAND');
627 inv_mwb_query_manager.add_group_clause('msn.inventory_item_id' , 'ONHAND');
628 inv_mwb_query_manager.add_group_clause('msn.current_organization_id', 'ONHAND');
629 inv_mwb_query_manager.add_group_clause('''Ea''', 'ONHAND');
630 END CASE; -- p_flag
631
632 END make_common_queries;
633 --
634 -- public functions
635 --
636
637 --
638 -- General APPTREE event handler for the EMPLOYEE tab.
639 --
640 PROCEDURE event (
641 x_node_value IN OUT NOCOPY NUMBER
642 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
643 , x_tbl_index IN OUT NOCOPY NUMBER
644 ) IS
645
646 l_procedure_name VARCHAR2(30);
647
648 BEGIN
649
650 l_procedure_name := 'EVENT';
651
652 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_EXPANDED'
653 OR inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
654
655 CASE inv_mwb_globals.g_tree_node_type
656
657 WHEN 'APPTREE_OBJECT_TRUNK' THEN
658 root_node_event (
659 x_node_value
660 , x_node_tbl
661 , x_tbl_index
662 );
663 WHEN 'ORG' THEN
664 org_node_event (
665 x_node_value
666 , x_node_tbl
667 , x_tbl_index
668 );
669 WHEN 'ITEM' THEN
670 item_node_event (
671 x_node_value
672 , x_node_tbl
673 , x_tbl_index
674 );
675 WHEN 'REV' THEN
676 rev_node_event (
677 x_node_value
678 , x_node_tbl
679 , x_tbl_index
680 );
681 WHEN 'LOT' THEN
682 lot_node_event (
683 x_node_value
684 , x_node_tbl
685 , x_tbl_index
686 );
687 WHEN 'SERIAL' THEN
688 serial_node_event (
689 x_node_value
690 , x_node_tbl
691 , x_tbl_index
692 );
693 END CASE;
694
695 END IF; -- g_node_type
696
697 EXCEPTION
698 WHEN no_data_found THEN
699 NULL;
700 END event;
701
702 END INV_MWB_LOT_TREE;
703