[Home] [Help]
PACKAGE BODY: APPS.INV_MWB_SERIAL_TREE
Source
1 PACKAGE BODY INV_MWB_SERIAL_TREE AS
2 /* $Header: INVMWSEB.pls 120.3 2005/07/18 10:59:48 rsagar noship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'INV_MWB_SERIAL_TREE';
5
6 --
7 -- private functions
8 --
9 PROCEDURE make_common_queries(p_flag VARCHAR2);
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
17 l_procedure_name VARCHAR2(30);
18
19 BEGIN
20
21 l_procedure_name := 'ROOT_NODE_EVENT';
22
23 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_EXPANDED' THEN
24 inv_mwb_tree1.add_orgs(
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 make_common_queries('MSN_QUERY');
33 inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
34 inv_mwb_query_manager.execute_query;
35
36 END IF; -- tree event
37
38 EXCEPTION
39 WHEN no_data_found THEN
40 NULL;
41 END root_node_event;
42
43 PROCEDURE org_node_event (
44 x_node_value IN OUT NOCOPY NUMBER
45 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
46 , x_tbl_index IN OUT NOCOPY NUMBER
47 ) IS
48
49 l_procedure_name VARCHAR2(30);
50
51 BEGIN
52
53 l_procedure_name := 'ORG_NODE_EVENT';
54
55 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_EXPANDED' THEN
56 inv_mwb_tree1.add_serials(
57 x_node_value
58 , x_node_tbl
59 , x_tbl_index
60 );
61
62 ELSIF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
63
64 make_common_queries('MSN');
65 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SERIAL).column_value :=
66 'msn.serial_number';
67 inv_mwb_query_manager.add_where_clause(
68 'msn.current_organization_id = :onh_tree_organization_id' ,
69 'ONHAND'
70 );
71 inv_mwb_query_manager.add_bind_variable(
72 'onh_tree_organization_id' ,
73 inv_mwb_globals.g_tree_organization_id
74 );
75 inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
76 inv_mwb_query_manager.execute_query;
77
78 END IF; -- g_tree_event
79
80 EXCEPTION
81 WHEN no_data_found THEN
82 NULL;
83 END org_node_event;
84
85 PROCEDURE serial_node_event (
86 x_node_value IN OUT NOCOPY NUMBER
87 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
88 , x_tbl_index IN OUT NOCOPY NUMBER
89 ) IS
90
91 l_procedure_name VARCHAR2(30);
92
93 BEGIN
94
95 l_procedure_name := 'SERIAL_NODE_EVENT';
96
97 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_EXPANDED' THEN
98 inv_mwb_tree1.add_items(
99 x_node_value
100 , x_node_tbl
101 , x_tbl_index
102 );
103
104 ELSIF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
105
106 make_common_queries('MSN');
107 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SERIAL).column_value :=
108 'msn.serial_number';
109 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ITEM_ID).column_value :=
110 'msn.inventory_item_id';
111 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
112 'msn.current_subinventory_code';
113 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
114 'msn.current_locator_id';
115 inv_mwb_query_manager.add_where_clause(
116 'msn.current_organization_id = :onh_tree_organization_id' ,
117 'ONHAND'
118 );
119 inv_mwb_query_manager.add_where_clause(
120 'msn.serial_number = :onh_tree_serial_number' ,
121 'ONHAND'
122 );
123 inv_mwb_query_manager.add_bind_variable(
124 'onh_tree_organization_id' ,
125 inv_mwb_globals.g_tree_organization_id
126 );
127 inv_mwb_query_manager.add_bind_variable(
128 'onh_tree_serial_number' ,
129 inv_mwb_globals.g_tree_serial_number
130 );
131 inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
132 inv_mwb_query_manager.execute_query;
133
134 END IF; -- g_tree_event
135
136 EXCEPTION
137 WHEN no_data_found THEN
138 NULL;
139 END serial_node_event;
140
141 PROCEDURE item_node_event (
142 x_node_value IN OUT NOCOPY NUMBER
143 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
144 , x_tbl_index IN OUT NOCOPY NUMBER
145 ) IS
146 l_rev_control NUMBER;
147 l_lot_control NUMBER;
148 l_procedure_name VARCHAR2(30);
149
150 BEGIN
151
152 l_procedure_name := 'ITEM_NODE_EVENT';
153
154 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_EXPANDED' THEN
155 inv_mwb_tree1.add_revs(
156 x_node_value
157 , x_node_tbl
158 , x_tbl_index
159 );
160
161 IF x_tbl_index = 1 THEN
162 inv_mwb_tree1.add_lots(
163 x_node_value
164 , x_node_tbl
165 , x_tbl_index
166 );
167 END IF;
168
169 ELSIF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
170
171 SELECT revision_qty_control_code,
172 lot_control_code
173 INTO l_rev_control,
174 l_lot_control
175 FROM mtl_system_items
176 WHERE organization_id = inv_mwb_globals.g_tree_organization_id
177 AND inventory_item_id = inv_mwb_globals.g_tree_item_id;
178
179 make_common_queries('MSN');
180 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SERIAL).column_value :=
181 'msn.serial_number';
182 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ITEM_ID).column_value :=
183 'msn.inventory_item_id';
184 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
185 'msn.current_subinventory_code';
186 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
187 'msn.current_locator_id';
188 inv_mwb_query_manager.add_where_clause(
189 'msn.current_organization_id = :onh_tree_organization_id' ,
190 'ONHAND'
191 );
192 inv_mwb_query_manager.add_where_clause(
193 'msn.serial_number = :onh_tree_serial_number' ,
194 'ONHAND'
195 );
196 inv_mwb_query_manager.add_where_clause(
197 'msn.inventory_item_id = :onh_tree_inventory_item_id' ,
198 'ONHAND'
199 );
200 inv_mwb_query_manager.add_bind_variable(
201 'onh_tree_organization_id' ,
202 inv_mwb_globals.g_tree_organization_id
203 );
204 inv_mwb_query_manager.add_bind_variable(
205 'onh_tree_serial_number' ,
206 inv_mwb_globals.g_tree_serial_number
207 );
208 inv_mwb_query_manager.add_bind_variable(
209 'onh_tree_inventory_item_id' ,
210 inv_mwb_globals.g_tree_item_id
211 );
212
213 IF l_rev_control = 1 AND l_lot_control = 2 THEN
214 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
215 'msn.lot_number';
216 END IF;
217 IF l_rev_control = 2 THEN
218 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
219 'msn.revision';
220 END IF;
221 inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
222 inv_mwb_query_manager.execute_query;
223
224 END IF;
225
226 EXCEPTION
227 WHEN no_data_found THEN
228 NULL;
229 END item_node_event;
230
231 PROCEDURE rev_node_event (
232 x_node_value IN OUT NOCOPY NUMBER
233 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
234 , x_tbl_index IN OUT NOCOPY NUMBER
235 ) IS
236
237 l_lot_control NUMBER;
238 l_procedure_name VARCHAR2(30);
239
240 BEGIN
241
242 l_procedure_name := 'REV_NODE_EVENT';
243
244 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_EXPANDED' THEN
245 inv_mwb_tree1.add_lots(
246 x_node_value
247 , x_node_tbl
248 , x_tbl_index
249 );
250
251 ELSIF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
252
253 SELECT lot_control_code
254 INTO l_lot_control
255 FROM mtl_system_items
256 WHERE organization_id = inv_mwb_globals.g_tree_organization_id
257 AND inventory_item_id = inv_mwb_globals.g_tree_item_id;
258
259 make_common_queries('MSN');
260 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SERIAL).column_value :=
261 'msn.serial_number';
262 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ITEM_ID).column_value :=
263 'msn.inventory_item_id';
264 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
265 'msn.current_subinventory_code';
266 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
267 'msn.current_locator_id';
268 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
269 'msn.revision';
270 inv_mwb_query_manager.add_where_clause(
271 'msn.current_organization_id = :onh_tree_organization_id' ,
272 'ONHAND'
273 );
274 inv_mwb_query_manager.add_where_clause(
275 'msn.serial_number = :onh_tree_serial_number' ,
276 'ONHAND'
277 );
278 inv_mwb_query_manager.add_where_clause(
279 'msn.inventory_item_id = :onh_tree_inventory_item_id' ,
280 'ONHAND'
281 );
282 inv_mwb_query_manager.add_where_clause(
283 'msn.revision = :onh_tree_revision' ,
284 'ONHAND'
285 );
286 inv_mwb_query_manager.add_bind_variable(
287 'onh_tree_organization_id' ,
288 inv_mwb_globals.g_tree_organization_id
289 );
290 inv_mwb_query_manager.add_bind_variable(
291 'onh_tree_serial_number' ,
292 inv_mwb_globals.g_tree_serial_number
293 );
294 inv_mwb_query_manager.add_bind_variable(
295 'onh_tree_inventory_item_id' ,
296 inv_mwb_globals.g_tree_item_id
297 );
298 inv_mwb_query_manager.add_bind_variable(
299 'onh_tree_revision' ,
300 inv_mwb_globals.g_tree_rev
301 );
302
303 IF l_lot_control = 2 THEN
304 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
305 'msn.lot_number';
306 END IF;
307 inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
308 inv_mwb_query_manager.execute_query;
309
310 END IF;
311
312 EXCEPTION
313 WHEN no_data_found THEN
314 NULL;
315 END rev_node_event;
316
317 PROCEDURE lot_node_event (
318 x_node_value IN OUT NOCOPY NUMBER
319 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
320 , x_tbl_index IN OUT NOCOPY NUMBER
321 ) IS
322
323 l_rev_control NUMBER;
324 l_procedure_name VARCHAR2(30);
325
326 BEGIN
327
328 l_procedure_name := 'LOT_NODE_EVENT';
329
330 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
331
332 SELECT revision_qty_control_code
333 INTO l_rev_control
334 FROM mtl_system_items
335 WHERE organization_id = inv_mwb_globals.g_tree_organization_id
336 AND inventory_item_id = inv_mwb_globals.g_tree_item_id;
337
338 make_common_queries('MSN');
339 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SERIAL).column_value :=
340 'msn.serial_number';
341 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ITEM_ID).column_value :=
342 'msn.inventory_item_id';
343 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
344 'msn.current_subinventory_code';
345 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
346 'msn.current_locator_id';
347 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
348 'msn.lot_number';
349 inv_mwb_query_manager.add_where_clause(
350 'msn.current_organization_id = :onh_tree_organization_id' ,
351 'ONHAND'
352 );
353 inv_mwb_query_manager.add_where_clause(
354 'msn.serial_number = :onh_tree_serial_number' ,
355 'ONHAND'
356 );
357 inv_mwb_query_manager.add_where_clause(
358 'msn.inventory_item_id = :onh_tree_inventory_item_id' ,
359 'ONHAND'
360 );
361 inv_mwb_query_manager.add_bind_variable(
362 'onh_tree_organization_id' ,
363 inv_mwb_globals.g_tree_organization_id
364 );
365 inv_mwb_query_manager.add_bind_variable(
366 'onh_tree_serial_number' ,
367 inv_mwb_globals.g_tree_serial_number
368 );
369 inv_mwb_query_manager.add_bind_variable(
370 'onh_tree_inventory_item_id' ,
371 inv_mwb_globals.g_tree_item_id
372 );
373
374 IF l_rev_control = 2 THEN
375 inv_mwb_query_manager.add_where_clause(
376 'msn.revision = :onh_tree_revision' ,
377 'ONHAND'
378 );
379 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
380 'msn.revision';
381 inv_mwb_query_manager.add_bind_variable(
382 'onh_tree_revision' ,
383 inv_mwb_globals.g_tree_rev
387 inv_mwb_query_manager.execute_query;
384 );
385 END IF;
386 inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
388
389 END IF;
390 EXCEPTION
391 WHEN no_data_found THEN
392 NULL;
393 END lot_node_event;
394
395 PROCEDURE make_common_queries(p_flag VARCHAR2) IS
396 BEGIN
397
398 IF p_flag = 'MSN_QUERY' THEN
399 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ORG_ID).column_value :=
400 'msn.current_organization_id';
401 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ONHAND).column_value :=
402 'count(1)';
403 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PACKED).column_value :=
404 'sum(decode(msn.lpn_id,NULL,0, 1))';
405 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.UNPACKED).column_value :=
406 'sum(decode(msn.lpn_id,NULL,1, 0))';
407 inv_mwb_query_manager.add_from_clause('mtl_serial_numbers msn' , 'ONHAND');
408 inv_mwb_query_manager.add_where_clause('msn.current_status = 3' , 'ONHAND');
409 inv_mwb_query_manager.add_group_clause('msn.current_organization_id' , 'ONHAND');
410 ELSIF p_flag = 'MSN' THEN
411 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ORG_ID).column_value :=
412 'msn.current_organization_id';
413 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ONHAND).column_value :=
414 '1';
415 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PACKED).column_value :=
416 'decode(msn.lpn_id,NULL,0, 1)';
417 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.UNPACKED).column_value :=
418 'decode(msn.lpn_id,NULL,1, 0)';
419 inv_mwb_query_manager.add_from_clause('mtl_serial_numbers msn' , 'ONHAND');
420 inv_mwb_query_manager.add_where_clause('msn.current_status = 3' , 'ONHAND');
421
422 END IF;
423
424 END make_common_queries;
425
426 --
427 -- public functions
428 --
429
430 --
431 -- General APPTREE event handler for the EMPLOYEE tab.
432 --
433 PROCEDURE event (
434 x_node_value IN OUT NOCOPY NUMBER
435 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
436 , x_tbl_index IN OUT NOCOPY NUMBER
437 ) IS
438
439 l_procedure_name VARCHAR2(30);
440
441 BEGIN
442
443 l_procedure_name := 'EVENT';
444
445 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_EXPANDED'
446 OR inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
447
448 CASE inv_mwb_globals.g_tree_node_type
449
450 WHEN 'APPTREE_OBJECT_TRUNK' THEN
451 root_node_event (
452 x_node_value
453 , x_node_tbl
454 , x_tbl_index
455 );
456 WHEN 'ORG' THEN
457 org_node_event (
458 x_node_value
459 , x_node_tbl
460 , x_tbl_index
461 );
462 WHEN 'ITEM' THEN
463 item_node_event (
464 x_node_value
465 , x_node_tbl
466 , x_tbl_index
467 );
468 WHEN 'REV' THEN
469 rev_node_event (
470 x_node_value
471 , x_node_tbl
472 , x_tbl_index
473 );
474 WHEN 'LOT' THEN
475 lot_node_event (
476 x_node_value
477 , x_node_tbl
478 , x_tbl_index
479 );
480 WHEN 'SERIAL' THEN
481 serial_node_event (
482 x_node_value
483 , x_node_tbl
484 , x_tbl_index
485 );
486 END CASE;
487
488 END IF; -- node type
489
490 EXCEPTION
491 WHEN no_data_found THEN
492 NULL;
493 END event;
494
495 END INV_MWB_SERIAL_TREE;