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