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