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