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