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