[Home] [Help]
PACKAGE BODY: APPS.INV_MWB_LPN_TREE
Source
1 PACKAGE BODY INV_MWB_LPN_TREE AS
2 /* $Header: INVMWLPB.pls 120.14 2008/01/30 09:46:43 ammathew ship $ */
3 --
4 -- private functions
5 --
6 g_pkg_name CONSTANT VARCHAR2(30) := 'INV_MWB_LPN_TREE';
7
8 PROCEDURE make_common_query_onhand (p_flag VARCHAR2);
9 PROCEDURE make_common_query_receiving (p_flag VARCHAR2);
10 PROCEDURE make_common_query_lpn;
11
12 -- PROCEDURE make_common_query_lpn(p_flag VARCHAR2);
13
14 PROCEDURE root_node_event (
15 x_node_value IN OUT NOCOPY NUMBER
16 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
17 , x_tbl_index IN OUT NOCOPY NUMBER
18 ) IS
19 l_procedure_name VARCHAR2(30);
20 BEGIN
21
22 l_procedure_name := 'ROOT_NODE_EVENT';
23
24 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_EXPANDED' THEN
25 inv_mwb_tree1.add_orgs (
26 x_node_value
27 , x_node_tbl
28 , x_tbl_index
29 );
30
31 ELSIF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
32
33 -- If all material locations are unchecked and the view by is LPN
34 -- Using ONHAND but it does not query onhand material location.
35 IF inv_mwb_globals.g_chk_onhand = 0
36 AND inv_mwb_globals.g_chk_receiving = 0
37 AND inv_mwb_globals.g_chk_inbound = 0 THEN
38 make_common_query_lpn;
39 inv_mwb_query_manager.add_qf_where_lpn_node('ONHAND');
40 inv_mwb_query_manager.execute_query;
41 RETURN;
42 END IF;
43
44 IF inv_mwb_globals.g_chk_onhand = 1 THEN
45 IF inv_mwb_globals.g_serial_from IS NOT NULL OR
46 inv_mwb_globals.g_serial_to IS NOT NULL
47 OR inv_mwb_globals.g_serial_attr_query IS NOT NULL THEN -- Bug 6429880
48 make_common_query_onhand('MSN_QUERY');
49 inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
50 ELSE
51 make_common_query_onhand('MOQD');
52 inv_mwb_query_manager.add_qf_where_onhand('ONHAND');
53 END IF;
54 END IF;
55
56 IF inv_mwb_globals.g_chk_receiving = 1 THEN
57 IF inv_mwb_globals.g_serial_from IS NOT NULL OR
58 inv_mwb_globals.g_serial_to IS NOT NULL THEN
59 make_common_query_receiving('MSN_QUERY');
60 inv_mwb_query_manager.add_qf_where_receiving('MSN_RECEIVING');
61 ELSE
62 make_common_query_receiving('RECEIVING');
63 inv_mwb_query_manager.add_qf_where_receiving('RECEIVING');
64 END IF;
65 END IF;
66
67 inv_mwb_query_manager.execute_query;
68 END IF;
69 EXCEPTION
70 WHEN NO_DATA_FOUND THEN
71 NULL;
72 END root_node_event;
73
74 PROCEDURE org_node_event (
75 x_node_value IN OUT NOCOPY NUMBER
76 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
77 , x_tbl_index IN OUT NOCOPY NUMBER
78 ) IS
79 l_procedure_name VARCHAR(30);
80 TYPE tab IS TABLE OF varchar2(100) index by binary_integer;
81 mtl_loc_type tab;
82 BEGIN
83 l_procedure_name := 'ORG_NODE_EVENT';
84 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_EXPANDED' THEN
85
86 SELECT meaning
87 BULK COLLECT INTO mtl_loc_type
88 FROM mfg_lookups
89 WHERE lookup_type = 'MTL_LOCATION_TYPES'
90 ORDER BY lookup_code;
91
92 inv_mwb_globals.print_msg( g_pkg_name, l_procedure_name, 'Selected all the document types' );
93
94 IF inv_mwb_globals.g_chk_onhand = 0 AND
95 inv_mwb_globals.g_chk_receiving = 0 AND
96 inv_mwb_globals.g_chk_inbound = 0 THEN
97
98 inv_mwb_tree1.add_lpns (
99 x_node_value
100 , x_node_tbl
101 , x_tbl_index
102 );
103
104 RETURN;
105 END IF;
106
107 IF inv_mwb_globals.g_chk_onhand = 1
108 THEN
109 x_node_tbl(x_tbl_index).state := -1;
110 x_node_tbl(x_tbl_index).DEPTH := 1;
111 x_node_tbl(x_tbl_index).label := mtl_loc_type(1);
112 x_node_tbl(x_tbl_index).icon := 'tree_workflowpackage';
113 x_node_tbl(x_tbl_index).VALUE := 1;
114 x_node_tbl(x_tbl_index).TYPE := 'MATLOC';
115 x_tbl_index := x_tbl_index + 1;
116 END IF;
117
118 IF NVL(inv_mwb_globals.g_chk_receiving, 1) = 1
119 THEN
120 x_node_tbl(x_tbl_index).state := -1;
121 x_node_tbl(x_tbl_index).DEPTH := 1;
122 x_node_tbl(x_tbl_index).label := mtl_loc_type(2);
123 x_node_tbl(x_tbl_index).icon := 'tree_workflowpackage';
124 x_node_tbl(x_tbl_index).VALUE := 2;
125 x_node_tbl(x_tbl_index).TYPE := 'MATLOC';
126 x_tbl_index := x_tbl_index + 1;
127 END IF;
128
129
130 ELSIF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
131
132 -- If all material locations are unchecked and the view by is LPN
133 -- Using ONHAND but it does not query onhand material location.
134
135 IF inv_mwb_globals.g_chk_onhand = 0
136 AND inv_mwb_globals.g_chk_receiving = 0
137 AND inv_mwb_globals.g_chk_inbound = 0
138 AND inv_mwb_globals.g_view_by = 'LPN' THEN
139 make_common_query_lpn;
140 inv_mwb_query_manager.add_qf_where_lpn_node('ONHAND');
141 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
142 'wlpn.subinventory_code';
143 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
144 'wlpn.locator_id';
145 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
146 'wlpn.lpn_id';
147 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
148 'wlc.cost_group_id';
149
150 inv_mwb_query_manager.add_group_clause('wlpn.subinventory_code','ONHAND');
151 inv_mwb_query_manager.add_group_clause('wlpn.locator_id','ONHAND');
152 inv_mwb_query_manager.add_group_clause('wlpn.lpn_id','ONHAND');
153 inv_mwb_query_manager.add_group_clause('wlc.cost_group_id','ONHAND');
154
155 inv_mwb_query_manager.execute_query;
156 RETURN;
157 END IF;
158
159 IF inv_mwb_globals.g_chk_onhand = 1 THEN
160 IF inv_mwb_globals.g_serial_from IS NOT NULL OR
161 inv_mwb_globals.g_serial_to IS NOT NULL THEN
162 make_common_query_receiving('RECEIVING');
163 inv_mwb_query_manager.add_qf_where_receiving('RECEIVING');
164
165 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
166 'msn.current_subinventory_code';
167 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
168 'msn.current_locator_id';
169 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
170 'msn.lpn_id';
171
172 inv_mwb_query_manager.add_group_clause('msn.current_subinventory_code','ONHAND');
173 inv_mwb_query_manager.add_group_clause('msn.current_locator_id','ONHAND');
174 inv_mwb_query_manager.add_group_clause('msn.lpn_id','ONHAND');
175 inv_mwb_query_manager.add_group_clause('msn.cost_group_id','ONHAND');
176 ELSE
177 make_common_query_onhand('MOQD');
178 inv_mwb_query_manager.add_qf_where_onhand('ONHAND');
179 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
180 'moqd.subinventory_code';
181 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
182 'moqd.locator_id';
183 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
184 'moqd.lpn_id';
185 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
186 'moqd.cost_group_id';
187
188 inv_mwb_query_manager.add_group_clause('moqd.subinventory_code','ONHAND');
189 inv_mwb_query_manager.add_group_clause('moqd.locator_id','ONHAND');
190 inv_mwb_query_manager.add_group_clause('moqd.lpn_id','ONHAND');
191 inv_mwb_query_manager.add_group_clause('moqd.cost_group_id','ONHAND');
192
193 END IF;
194 END IF;
195
196 IF inv_mwb_globals.g_chk_receiving = 1 THEN
197 IF inv_mwb_globals.g_serial_from IS NOT NULL OR
198 inv_mwb_globals.g_serial_to IS NOT NULL THEN
199 make_common_query_receiving('MSN_QUERY');
200 inv_mwb_query_manager.add_qf_where_receiving('MSN_RECEIVING');
201 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
202 'msn.current_subinventory_code';
203 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
204 'msn.current_locator_id';
205 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.LPN_ID).column_value :=
206 'msn.lpn_id';
207
208 inv_mwb_query_manager.add_group_clause('msn.current_subinventory_code','RECEIVING');
209 inv_mwb_query_manager.add_group_clause('msn.current_locator_id','RECEIVING');
210 inv_mwb_query_manager.add_group_clause('msn.lpn_id','RECEIVING');
211
212 ELSE
213 make_common_query_receiving('RECEIVING');
214 inv_mwb_query_manager.add_qf_where_receiving('RECEIVING');
215 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
216 'rs.to_subinventory';
217 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
218 'rs.to_locator_id';
219 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.LPN_ID).column_value :=
220 'rs.lpn_id';
221
222 inv_mwb_query_manager.add_group_clause('rs.to_subinventory','RECEIVING');
223 inv_mwb_query_manager.add_group_clause('rs.to_locator_id','RECEIVING');
224 inv_mwb_query_manager.add_group_clause('rs.lpn_id','RECEIVING');
225 END IF;
226 END IF;
227
228 inv_mwb_query_manager.execute_query;
229 END IF; -- Node selected
230 EXCEPTION
231 WHEN NO_DATA_FOUND THEN
232 NULL;
233 END org_node_event;
234
235
236 PROCEDURE mat_loc_node_event (
237 x_node_value IN OUT NOCOPY NUMBER
238 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
239 , x_tbl_index IN OUT NOCOPY NUMBER
240 ) IS
241 l_procedure_name VARCHAR(30);
242 BEGIN
243 l_procedure_name := 'MAT_LOC_NODE_EVENT';
244 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_EXPANDED' THEN
245
246 inv_mwb_tree1.add_lpns (
247 x_node_value
248 , x_node_tbl
249 , x_tbl_index
250 );
251
252 ELSIF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
253
254 -- If all material locations are unchecked and the view by is LPN
255 -- Using ONHAND but it does not query onhand material location.
256 IF inv_mwb_globals.g_chk_onhand = 0
257 AND inv_mwb_globals.g_chk_receiving = 0
258 AND inv_mwb_globals.g_chk_inbound = 0
259 AND inv_mwb_globals.g_view_by = 'LPN' THEN
260 make_common_query_lpn;
261 inv_mwb_query_manager.add_qf_where_lpn_node('ONHAND');
262 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
263 'wlpn.subinventory_code';
264 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
265 'wlpn.locator_id';
266 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
267 'wlpn.lpn_id';
268 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
269 'wlc.cost_group_id';
270
271 inv_mwb_query_manager.add_group_clause('wlpn.subinventory_code','ONHAND');
272 inv_mwb_query_manager.add_group_clause('wlpn.locator_id','ONHAND');
273 inv_mwb_query_manager.add_group_clause('wlpn.lpn_id','ONHAND');
274 inv_mwb_query_manager.add_group_clause('wlc.cost_group_id','ONHAND');
275
276 inv_mwb_query_manager.execute_query;
277 RETURN;
278 END IF;
279
280 IF inv_mwb_globals.g_tree_mat_loc_id = 1 THEN
281 IF inv_mwb_globals.g_serial_from IS NOT NULL OR
282 inv_mwb_globals.g_serial_to IS NOT NULL
283 OR inv_mwb_globals.g_serial_attr_query IS NOT NULL THEN -- Bug 6429880
284 make_common_query_onhand('MSN_QUERY');
285 inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
286 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
287 'msn.current_subinventory_code';
288 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
289 'msn.current_locator_id';
290 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
291 'msn.lpn_id';
292 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
293 'msn.cost_group_id';
294
295 inv_mwb_query_manager.add_group_clause('msn.current_subinventory_code','ONHAND');
296 inv_mwb_query_manager.add_group_clause('msn.current_locator_id','ONHAND');
297 inv_mwb_query_manager.add_group_clause('msn.lpn_id','ONHAND');
298 inv_mwb_query_manager.add_group_clause('msn.cost_group_id','ONHAND');
299 ELSE
300 make_common_query_onhand('MOQD');
301 inv_mwb_query_manager.add_qf_where_onhand('ONHAND');
302 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
303 'moqd.subinventory_code';
304 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
305 'moqd.locator_id';
306 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
307 'moqd.lpn_id';
308 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
309 'moqd.cost_group_id';
310
311 inv_mwb_query_manager.add_group_clause('moqd.subinventory_code','ONHAND');
312 inv_mwb_query_manager.add_group_clause('moqd.locator_id','ONHAND');
313 inv_mwb_query_manager.add_group_clause('moqd.lpn_id','ONHAND');
314 inv_mwb_query_manager.add_group_clause('moqd.cost_group_id','ONHAND');
315
316 END IF;
317 END IF;
318
319 IF inv_mwb_globals.g_tree_mat_loc_id = 2 THEN
320 IF inv_mwb_globals.g_serial_from IS NOT NULL OR
321 inv_mwb_globals.g_serial_to IS NOT NULL THEN
322 make_common_query_receiving('MSN_QUERY');
323 inv_mwb_query_manager.add_qf_where_receiving('MSN_RECEIVING');
324 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
325 'msn.current_subinventory_code';
326 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
327 'msn.current_locator_id';
328 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.LPN_ID).column_value :=
329 'msn.lpn_id';
330
331 inv_mwb_query_manager.add_group_clause('msn.current_subinventory_code','RECEIVING');
332 inv_mwb_query_manager.add_group_clause('msn.current_locator_id','RECEIVING');
333 inv_mwb_query_manager.add_group_clause('msn.lpn_id','RECEIVING');
334
335 ELSE
336 make_common_query_receiving('RECEIVING');
337 inv_mwb_query_manager.add_qf_where_receiving('RECEIVING');
338 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
339 'rs.to_subinventory';
340 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
341 'rs.to_locator_id';
342 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.LPN_ID).column_value :=
343 'rs.lpn_id';
344
345 inv_mwb_query_manager.add_group_clause('rs.to_subinventory','RECEIVING');
346 inv_mwb_query_manager.add_group_clause('rs.to_locator_id','RECEIVING');
347 inv_mwb_query_manager.add_group_clause('rs.lpn_id','RECEIVING');
348 END IF;
349 END IF;
350
351 inv_mwb_query_manager.execute_query;
352 END IF; --Tree node selected
353 END mat_loc_node_event;
354
355 PROCEDURE lpn_node_event (
356 x_node_value IN OUT NOCOPY NUMBER
357 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
358 , x_tbl_index IN OUT NOCOPY NUMBER
359 ) IS
360 l_procedure_name VARCHAR2(30);
361 BEGIN
362
363 l_procedure_name := 'LPN_NODE_EVENT';
364 inv_mwb_globals.print_msg( g_pkg_name, l_procedure_name, 'Entered' );
365
366 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_EXPANDED' THEN
367 inv_mwb_tree1.add_lpns (
368 x_node_value
369 , x_node_tbl
370 , x_tbl_index
371 );
372
373 inv_mwb_tree1.add_items (
374 x_node_value
375 , x_node_tbl
376 , x_tbl_index
377 );
378
379 ELSIF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
380
381 -- If all material locations are unchecked and the view by is LPN
382 -- Using ONHAND but it does not query onhand material location.
383 IF inv_mwb_globals.g_chk_onhand = 0
384 AND inv_mwb_globals.g_chk_receiving = 0
385 AND inv_mwb_globals.g_chk_inbound = 0
386 AND inv_mwb_globals.g_view_by = 'LPN' THEN
387
388 inv_mwb_query_manager.make_nested_lpn_onhand_query;
389
390 make_common_query_lpn;
391 inv_mwb_query_manager.add_qf_where_lpn_node('ONHAND');
392 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
393 'wlpn.subinventory_code';
394 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
395 'wlpn.locator_id';
396 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
397 'wlpn.lpn_id';
398 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
399 'wlc.cost_group_id';
400
401 inv_mwb_query_manager.add_group_clause('wlpn.subinventory_code','ONHAND');
402 inv_mwb_query_manager.add_group_clause('wlpn.locator_id','ONHAND');
403 inv_mwb_query_manager.add_group_clause('wlpn.lpn_id','ONHAND');
404 inv_mwb_query_manager.add_group_clause('wlc.cost_group_id','ONHAND');
405
406 inv_mwb_query_manager.add_where_clause('wlpn.lpn_id = :onh_lpn_id', 'ONHAND');
407 inv_mwb_query_manager.add_bind_variable('onh_lpn_id', inv_mwb_globals.g_tree_parent_lpn_id);
408 inv_mwb_query_manager.execute_query;
409 RETURN;
410 END IF;
411
412 -- inv_mwb_query_manager.make_nested_lpn_onhand_query;
413
414 IF inv_mwb_globals.g_tree_mat_loc_id = 1 THEN
415 IF inv_mwb_globals.g_serial_from IS NOT NULL OR
416 inv_mwb_globals.g_serial_to IS NOT NULL
417 OR inv_mwb_globals.g_serial_attr_query IS NOT NULL THEN -- Bug 6429880
418
419 inv_mwb_query_manager.make_nested_lpn_onhand_query;
420
421 make_common_query_onhand('MSN_QUERY');
422 inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
423 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
424 'msn.current_subinventory_code';
425 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
426 'msn.current_locator_id';
427 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
428 'msn.lpn_id';
429 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
430 'msn.cost_group_id';
431
432 inv_mwb_query_manager.add_group_clause('msn.current_subinventory_code','ONHAND');
433 inv_mwb_query_manager.add_group_clause('msn.current_locator_id','ONHAND');
434 inv_mwb_query_manager.add_group_clause('msn.lpn_id','ONHAND');
435 inv_mwb_query_manager.add_group_clause('msn.cost_group_id','ONHAND');
436
437 inv_mwb_query_manager.add_where_clause('msn.lpn_id = :onh_lpn_id', 'ONHAND');
438 inv_mwb_query_manager.add_bind_variable('onh_lpn_id', inv_mwb_globals.g_tree_parent_lpn_id);
439
440 ELSE
441
442 inv_mwb_query_manager.make_nested_lpn_onhand_query;
443 make_common_query_onhand('MOQD');
444 inv_mwb_query_manager.add_qf_where_onhand('ONHAND');
445 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
446 'moqd.subinventory_code';
447 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
448 'moqd.locator_id';
449 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
450 'moqd.lpn_id';
451 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
452 'moqd.cost_group_id';
453
454 inv_mwb_query_manager.add_group_clause('moqd.subinventory_code','ONHAND');
455 inv_mwb_query_manager.add_group_clause('moqd.locator_id','ONHAND');
456 inv_mwb_query_manager.add_group_clause('moqd.lpn_id','ONHAND');
457 inv_mwb_query_manager.add_group_clause('moqd.cost_group_id','ONHAND');
458
459 inv_mwb_query_manager.add_where_clause('moqd.lpn_id = :onh_lpn_id', 'ONHAND');
460 inv_mwb_query_manager.add_bind_variable('onh_lpn_id', inv_mwb_globals.g_tree_parent_lpn_id);
461
462 END IF;
463 END IF;
464
465 IF inv_mwb_globals.g_tree_mat_loc_id = 2 THEN
466
467 inv_mwb_query_manager.make_nested_lpn_rcv_query;
468
469 IF inv_mwb_globals.g_serial_from IS NOT NULL OR
470 inv_mwb_globals.g_serial_to IS NOT NULL THEN
471 make_common_query_receiving('MSN_QUERY');
472 inv_mwb_query_manager.add_qf_where_receiving('MSN_RECEIVING');
473 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
474 'msn.current_subinventory_code';
475 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
476 'msn.current_locator_id';
477 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.LPN_ID).column_value :=
478 'msn.lpn_id';
479
480 inv_mwb_query_manager.add_group_clause('msn.current_subinventory_code','RECEIVING');
481 inv_mwb_query_manager.add_group_clause('msn.current_locator_id','RECEIVING');
482 inv_mwb_query_manager.add_group_clause('msn.lpn_id','RECEIVING');
483
484 inv_mwb_query_manager.add_where_clause('msn.lpn_id = :rcv_lpn_id', 'RECEIVING');
485 inv_mwb_query_manager.add_bind_variable('rcv_lpn_id', inv_mwb_globals.g_tree_parent_lpn_id);
486
487 ELSE
488
489 make_common_query_receiving('RECEIVING');
490 inv_mwb_query_manager.add_qf_where_receiving('RECEIVING');
491 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
492 'rs.to_subinventory';
493 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
494 'rs.to_locator_id';
495 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.LPN_ID).column_value :=
496 'rs.lpn_id';
497
498 inv_mwb_query_manager.add_group_clause('rs.to_subinventory','RECEIVING');
499 inv_mwb_query_manager.add_group_clause('rs.to_locator_id','RECEIVING');
500 inv_mwb_query_manager.add_group_clause('rs.lpn_id','RECEIVING');
501
502 inv_mwb_query_manager.add_where_clause('rs.lpn_id = :rcv_lpn_id', 'RECEIVING');
503 inv_mwb_query_manager.add_bind_variable('rcv_lpn_id', inv_mwb_globals.g_tree_parent_lpn_id);
504
505 END IF;
506 END IF;
507 inv_mwb_query_manager.execute_query;
508 END IF; -- Node selected
509 EXCEPTION
510 WHEN NO_DATA_FOUND THEN
511 NULL;
512 END lpn_node_event;
513
514 PROCEDURE item_node_event (
515 x_node_value IN OUT NOCOPY NUMBER
516 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
517 , x_tbl_index IN OUT NOCOPY NUMBER
518 ) IS
519
520 l_rev_control NUMBER;
521 l_lot_control NUMBER;
522 l_serial_control NUMBER;
523 l_procedure_name VARCHAR2(30);
524
525 /* LPN Status Project */
526 l_lot_controlled NUMBER := 0;
527 l_serial_controlled NUMBER := 0;
528 l_default_status_id NUMBER;
529 l_status_id NUMBER;
530
531 BEGIN
532
533 l_procedure_name := 'ITEM_NODE_EVENT';
534
535 inv_mwb_globals.print_msg( g_pkg_name, l_procedure_name, 'Item Node Event-Entered' );
536
537 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_EXPANDED' THEN
538 inv_mwb_tree1.add_revs (
539 x_node_value
540 , x_node_tbl
541 , x_tbl_index
542 );
543
544 IF x_tbl_index = 1 THEN
545 inv_mwb_tree1.add_lots (
546 x_node_value
547 , x_node_tbl
548 , x_tbl_index
549 );
550
551
552 IF x_tbl_index = 1 THEN
553 IF NVL(inv_mwb_globals.g_prepacked,-99) <> 10 THEN
554 inv_mwb_tree1.add_serials (
555 x_node_value
556 , x_node_tbl
557 , x_tbl_index
558 );
559
560
561 END IF;
562 END IF;
563 END IF;
564
565
566 ELSIF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
567
568 SELECT revision_qty_control_code,
569 lot_control_code,
570 serial_number_control_code
571 INTO l_rev_control,
572 l_lot_control,
573 l_serial_control
574 FROM mtl_system_items
575 WHERE inventory_item_id = inv_mwb_globals.g_tree_item_id
576 AND organization_id = inv_mwb_globals.g_organization_id;
577
578 -- If all material locations are unchecked and the view by is LPN
579 -- Using ONHAND but it does not query onhand material location.
580 IF inv_mwb_globals.g_chk_onhand = 0
581 AND inv_mwb_globals.g_chk_receiving = 0
582 AND inv_mwb_globals.g_chk_inbound = 0
583 AND inv_mwb_globals.g_view_by = 'LPN' THEN
584 make_common_query_lpn;
585 inv_mwb_query_manager.add_qf_where_lpn_node('ONHAND');
586 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
587 'wlpn.subinventory_code';
588 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
589 'wlpn.locator_id';
590 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
591 'wlpn.lpn_id';
592 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
593 'wlc.cost_group_id';
594 IF l_rev_control = 2 THEN
595 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.REVISION).column_value :=
596 'wlc.revision';
597 inv_mwb_query_manager.add_group_clause('wlc.revision','ONHAND');
598 ELSE
599 IF l_lot_control = 2 THEN
600 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
601 'wlc.lot_number';
602 inv_mwb_query_manager.add_group_clause('wlc.lot_number','ONHAND');
603 ELSIF l_serial_control IN (2, 5) THEN
604 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SERIAL).column_value :=
605 'msn.serial_number';
606 inv_mwb_query_manager.add_group_clause('msn.serial_number','ONHAND');
607 END IF;
608 END IF;
609
610 /*LPN Status Project */
611 IF (inv_cache.set_org_rec(inv_mwb_globals.g_tree_organization_id)) THEN
612 l_default_status_id := inv_cache.org_rec.default_status_id;
613 END IF;
614
615 IF inv_cache.set_item_rec(inv_mwb_globals.g_tree_organization_id, inv_mwb_globals.g_tree_item_id) THEN
616
617 IF (inv_cache.item_rec.serial_number_control_code in (2,5)) THEN
618 l_serial_controlled := 1; -- Item is serial controlled
619 END IF;
620
621 IF (inv_cache.item_rec.lot_control_code <> 1) THEN
622 l_lot_controlled := 1; -- Item is lot controlled
623 END IF;
624
625 END IF;
626
627 IF (l_default_status_id IS NOT NULL AND l_serial_controlled = 0 AND l_lot_controlled = 0) THEN
628
629 l_status_id := INV_MATERIAL_STATUS_GRP.get_default_status(p_organization_id => inv_mwb_globals.g_organization_id,
630 p_inventory_item_id=>inv_mwb_globals.g_tree_item_id ,
631 p_sub_code=>inv_mwb_globals.g_tree_subinventory_code,
632 p_loc_id=>inv_mwb_globals.g_tree_loc_id,
633 p_lot_number=>inv_mwb_globals.g_tree_lot_number,
634 p_lpn_id=>inv_mwb_globals.g_tree_parent_lpn_id);
635
636 inv_mwb_globals.print_msg( g_pkg_name, l_procedure_name, 'Value of l_status_id:'|| l_status_id );
637
638 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.STATUS_ID).column_value :=
639 l_status_id;
640 inv_mwb_query_manager.add_group_clause(l_status_id, 'ONHAND');
641
642 END IF;
643 /* End of fix for LPN Status Project */
644
645 inv_mwb_query_manager.add_group_clause('wlpn.subinventory_code','ONHAND');
646 inv_mwb_query_manager.add_group_clause('wlpn.locator_id','ONHAND');
647 inv_mwb_query_manager.add_group_clause('wlpn.lpn_id','ONHAND');
648 inv_mwb_query_manager.add_group_clause('wlc.cost_group_id','ONHAND');
649
650 inv_mwb_query_manager.add_where_clause('wlpn.lpn_id = :onh_lpn_id', 'ONHAND');
651 inv_mwb_query_manager.add_bind_variable('onh_lpn_id', inv_mwb_globals.g_tree_parent_lpn_id);
652
653 inv_mwb_query_manager.add_where_clause('wlc.inventory_item_id = :onh_inventory_item_id', 'ONHAND');
654 inv_mwb_query_manager.add_bind_variable('onh_inventory_item_id', inv_mwb_globals.g_tree_item_id);
655
656 inv_mwb_query_manager.execute_query;
657 RETURN;
658 END IF;
659
660
661
662 IF inv_mwb_globals.g_tree_mat_loc_id = 1 THEN
663 IF (inv_mwb_globals.g_serial_from IS NOT NULL OR
664 inv_mwb_globals.g_serial_to IS NOT NULL)
665 OR (NVL(l_rev_control, 1) = 1
666 AND NVL(l_lot_control, 1) = 1
667 AND l_serial_control IN ( 2,5 ))
668 OR inv_mwb_globals.g_serial_attr_query IS NOT NULL THEN -- Bug 6429880
669
670 make_common_query_onhand('MSN');
671 inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
672
673 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
674 'msn.current_subinventory_code';
675 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
676 'msn.current_locator_id';
677 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
678 'msn.lpn_id';
679 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
680 'msn.cost_group_id';
681
682 IF l_rev_control = 2 THEN
683 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.REVISION).column_value :=
684 'msn.revision';
685 inv_mwb_query_manager.add_group_clause('msn.revision','ONHAND');
686 ELSE
687 IF l_lot_control = 2 THEN
688 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
689 'msn.lot_number';
690 inv_mwb_query_manager.add_group_clause('msn.lot_number','ONHAND');
691 ELSIF l_serial_control IN (2, 5) THEN
692 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SERIAL).column_value :=
693 'msn.serial_number';
694 inv_mwb_query_manager.add_group_clause('msn.serial_number','ONHAND');
695 END IF;
696 END IF;
697
698 inv_mwb_query_manager.add_group_clause('msn.current_subinventory_code','ONHAND');
699 inv_mwb_query_manager.add_group_clause('msn.current_locator_id','ONHAND');
700 inv_mwb_query_manager.add_group_clause('msn.lpn_id','ONHAND');
701 inv_mwb_query_manager.add_group_clause('msn.cost_group_id','ONHAND');
702
703 inv_mwb_query_manager.add_where_clause('msn.lpn_id = :onh_lpn_id', 'ONHAND');
704 inv_mwb_query_manager.add_bind_variable('onh_lpn_id', inv_mwb_globals.g_tree_parent_lpn_id);
705 inv_mwb_query_manager.add_where_clause('msn.inventory_item_id = :onh_inventory_item_id', 'ONHAND');
706 inv_mwb_query_manager.add_bind_variable('onh_inventory_item_id', inv_mwb_globals.g_tree_item_id);
707
708 ELSE
709 make_common_query_onhand('MOQD');
710 inv_mwb_query_manager.add_qf_where_onhand('ONHAND');
711 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
712 'moqd.subinventory_code';
713 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
714 'moqd.locator_id';
715 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
716 'moqd.lpn_id';
717 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
718 'moqd.cost_group_id';
719
720 /* LPN Status Support */
721 -- For serial controlled items, the status_id will be populated in post_query of IMVMWQMB.
722
723 inv_mwb_globals.print_msg( g_pkg_name, l_procedure_name, 'LPN Status check');
724
725 IF (inv_cache.set_org_rec(inv_mwb_globals.g_tree_organization_id)) THEN
726 l_default_status_id := inv_cache.org_rec.default_status_id;
727 END IF;
728
729 IF inv_cache.set_item_rec(inv_mwb_globals.g_tree_organization_id, inv_mwb_globals.g_tree_item_id) THEN
730 IF (inv_cache.item_rec.serial_number_control_code in (2,5)) THEN
731 l_serial_controlled := 1; -- Item is serial controlled
732 END IF;
733
734 IF (inv_cache.item_rec.lot_control_code <> 1) then
735 l_lot_controlled := 1; -- Item is lot controlled
736 END IF;
737 END IF;
738
739 IF (l_default_status_id IS NOT NULL AND l_serial_controlled = 0 and l_lot_controlled = 0) THEN
740 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.STATUS_ID).column_value :=
741 'moqd.status_id';
742 inv_mwb_query_manager.add_group_clause('moqd.status_id', 'ONHAND');
743 END IF;
744
745 /* LPN Status Support */
746
747 IF l_rev_control = 2 THEN
748 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.REVISION).column_value :=
749 'moqd.revision';
750 inv_mwb_query_manager.add_group_clause('moqd.revision','ONHAND');
751 ELSIF l_lot_control = 2 THEN
752 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
753 'moqd.lot_number';
754 inv_mwb_query_manager.add_group_clause('moqd.lot_number','ONHAND');
755 END IF;
756
757 inv_mwb_query_manager.add_group_clause('moqd.subinventory_code','ONHAND');
758 inv_mwb_query_manager.add_group_clause('moqd.locator_id','ONHAND');
759 inv_mwb_query_manager.add_group_clause('moqd.lpn_id','ONHAND');
760 inv_mwb_query_manager.add_group_clause('moqd.cost_group_id','ONHAND');
761
762 inv_mwb_query_manager.add_where_clause('moqd.lpn_id = :onh_lpn_id', 'ONHAND');
763 inv_mwb_query_manager.add_bind_variable('onh_lpn_id', inv_mwb_globals.g_tree_parent_lpn_id);
764 inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = :onh_inventory_item_id', 'ONHAND');
765 inv_mwb_query_manager.add_bind_variable('onh_inventory_item_id', inv_mwb_globals.g_tree_item_id);
766
767 END IF;
768 END IF;
769
770 IF inv_mwb_globals.g_tree_mat_loc_id = 2 THEN
771 IF inv_mwb_globals.g_serial_from IS NOT NULL OR
772 inv_mwb_globals.g_serial_to IS NOT NULL
773 OR (NVL(l_rev_control, 1) = 1
774 AND NVL(l_lot_control, 1) = 1
775 AND l_serial_control IN ( 2,5 )) THEN
776
777 make_common_query_receiving('MSN_QUERY');
778 inv_mwb_query_manager.add_qf_where_receiving('MSN_RECEIVING');
779 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
780 'msn.current_subinventory_code';
781 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
782 'msn.current_locator_id';
783 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.LPN_ID).column_value :=
784 'msn.lpn_id';
785
786 inv_mwb_query_manager.add_group_clause('msn.current_subinventory_code','RECEIVING');
787 inv_mwb_query_manager.add_group_clause('msn.current_locator_id','RECEIVING');
788 inv_mwb_query_manager.add_group_clause('msn.lpn_id','RECEIVING');
789
790 IF l_rev_control = 2 THEN
791 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.REVISION).column_value :=
792 'msn.revision';
793 inv_mwb_query_manager.add_group_clause('msn.revision','RECEIVING');
794 ELSE
795 IF l_lot_control = 2 THEN
796 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
797 'msn.lot_number';
798 inv_mwb_query_manager.add_group_clause('msn.lot_number','RECEIVING');
799 ELSIF l_serial_control IN (2, 5) THEN
800 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SERIAL).column_value :=
801 'msn.serial_number';
802 inv_mwb_query_manager.add_group_clause('msn.serial_number','RECEIVING');
803 END IF;
804 END IF;
805
806 inv_mwb_query_manager.add_where_clause('msn.lpn_id = :rcv_lpn_id', 'RECEIVING');
807 inv_mwb_query_manager.add_bind_variable('rcv_lpn_id', inv_mwb_globals.g_tree_parent_lpn_id);
808 inv_mwb_query_manager.add_where_clause('msn.inventory_item_id = :onh_inventory_item_id', 'RECEIVING');
809 inv_mwb_query_manager.add_bind_variable('onh_inventory_item_id', inv_mwb_globals.g_tree_item_id);
810
811
812 ELSE
813 make_common_query_receiving('RECEIVING');
814 inv_mwb_query_manager.add_qf_where_receiving('RECEIVING');
815 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
816 'rs.to_subinventory';
817 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
818 'rs.to_locator_id';
819 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.LPN_ID).column_value :=
820 'rs.lpn_id';
821
822 inv_mwb_query_manager.add_group_clause('rs.to_subinventory','RECEIVING');
823 inv_mwb_query_manager.add_group_clause('rs.to_locator_id','RECEIVING');
824 inv_mwb_query_manager.add_group_clause('rs.lpn_id','RECEIVING');
825
826 IF l_rev_control = 2 THEN
827 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.REVISION).column_value :=
828 'rs.item_revision';
829 inv_mwb_query_manager.add_group_clause('rs.item_revision','RECEIVING');
830 ELSIF l_lot_control = 2 THEN
831 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
832 'rls.lot_num';
833 inv_mwb_query_manager.add_group_clause('rls.lot_num','RECEIVING');
834 END IF;
835
836 inv_mwb_query_manager.add_where_clause('rs.lpn_id = :rcv_lpn_id', 'RECEIVING');
837 inv_mwb_query_manager.add_bind_variable('rcv_lpn_id', inv_mwb_globals.g_tree_parent_lpn_id);
838 inv_mwb_query_manager.add_where_clause('rs.item_id = :onh_inventory_item_id', 'RECEIVING');
839 inv_mwb_query_manager.add_bind_variable('onh_inventory_item_id', inv_mwb_globals.g_tree_item_id);
840
841 END IF;
842 END IF;
843
844 inv_mwb_query_manager.execute_query;
845 END IF;
846 EXCEPTION
847 WHEN NO_DATA_FOUND THEN
848 NULL;
849 END item_node_event;
850
851 PROCEDURE rev_node_event (
852 x_node_value IN OUT NOCOPY NUMBER
853 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
854 , x_tbl_index IN OUT NOCOPY NUMBER
855 ) IS
856 l_lot_control NUMBER;
857 l_serial_control NUMBER;
858 l_procedure_name VARCHAR2(30);
859
860 /* LPN Status Project */
861 l_lot_controlled NUMBER := 0;
862 l_serial_controlled NUMBER := 0;
863 l_default_status_id NUMBER;
864 l_status_id NUMBER;
865
866 BEGIN
867
868 l_procedure_name := 'REV_NODE_EVENT';
869
870 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_EXPANDED' THEN
871 inv_mwb_tree1.add_lots (
872 x_node_value
873 , x_node_tbl
874 , x_tbl_index
875 );
876
877 IF x_tbl_index = 1 THEN
878 inv_mwb_tree1.add_serials (
879 x_node_value
880 , x_node_tbl
881 , x_tbl_index
882 );
883
884 END IF;
885
886 ELSIF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
887
888 SELECT lot_control_code,
889 serial_number_control_code
890 INTO l_lot_control,
891 l_serial_control
892 FROM mtl_system_items
893 WHERE inventory_item_id = inv_mwb_globals.g_tree_item_id
894 AND organization_id = inv_mwb_globals.g_organization_id;
895
896 -- If all material locations are unchecked and the view by is LPN
897 -- Using ONHAND but it does not query onhand material location.
898 IF inv_mwb_globals.g_chk_onhand = 0
899 AND inv_mwb_globals.g_chk_receiving = 0
900 AND inv_mwb_globals.g_chk_inbound = 0
901 AND inv_mwb_globals.g_view_by = 'LPN' THEN
902 make_common_query_lpn;
903 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
904 'wlpn.subinventory_code';
905 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
906 'wlpn.locator_id';
907 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
908 'wlpn.lpn_id';
909 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
910 'wlc.cost_group_id';
911 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.REVISION).column_value :=
912 'wlc.revision';
913 inv_mwb_query_manager.add_group_clause('wlc.revision','ONHAND');
914
915 IF l_lot_control = 2 THEN
916 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
917 'wlc.lot_number';
918 inv_mwb_query_manager.add_group_clause('wlc.lot_number','ONHAND');
919 ELSIF l_serial_control IN (2, 5) THEN
920 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SERIAL).column_value :=
921 'msn.serial_number';
922 inv_mwb_query_manager.add_group_clause('msn.serial_number','ONHAND');
923 END IF;
924
925 inv_mwb_query_manager.add_group_clause('wlpn.subinventory_code','ONHAND');
926 inv_mwb_query_manager.add_group_clause('wlpn.locator_id','ONHAND');
927 inv_mwb_query_manager.add_group_clause('wlpn.lpn_id','ONHAND');
928 inv_mwb_query_manager.add_group_clause('wlc.cost_group_id','ONHAND');
929
930 /*LPN Status Project */
931 IF (inv_cache.set_org_rec(inv_mwb_globals.g_tree_organization_id)) THEN
932 l_default_status_id := inv_cache.org_rec.default_status_id;
933 END IF;
934
935 IF inv_cache.set_item_rec(inv_mwb_globals.g_tree_organization_id, inv_mwb_globals.g_tree_item_id) THEN
936
937 IF (inv_cache.item_rec.serial_number_control_code in (2,5)) THEN
938 l_serial_controlled := 1; -- Item is serial controlled
939 END IF;
940
941 IF (inv_cache.item_rec.lot_control_code <> 1) THEN
942 l_lot_controlled := 1; -- Item is lot controlled
943 END IF;
944
945 END IF;
946
947 IF (l_default_status_id IS NOT NULL AND l_serial_controlled = 0 AND l_lot_controlled = 0) THEN
948
949 l_status_id := INV_MATERIAL_STATUS_GRP.get_default_status(p_organization_id => inv_mwb_globals.g_organization_id,
950 p_inventory_item_id=>inv_mwb_globals.g_tree_item_id ,
951 p_sub_code=>inv_mwb_globals.g_tree_subinventory_code,
952 p_loc_id=>inv_mwb_globals.g_tree_loc_id,
953 p_lot_number=>inv_mwb_globals.g_tree_lot_number,
954 p_lpn_id=>inv_mwb_globals.g_tree_parent_lpn_id);
955
956 inv_mwb_globals.print_msg( g_pkg_name, l_procedure_name, 'Value of l_status_id:'|| l_status_id );
957
958 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.STATUS_ID).column_value :=
959 l_status_id;
960 inv_mwb_query_manager.add_group_clause(l_status_id, 'ONHAND');
961
962 END IF;
963 /* End of fix for LPN Status Project */
964
965 inv_mwb_query_manager.add_where_clause('wlc.revision = :onh_revision', 'ONHAND');
966 inv_mwb_query_manager.add_bind_variable('onh_revision', inv_mwb_globals.g_tree_rev);
967
968
969 inv_mwb_query_manager.add_where_clause('wlpn.lpn_id = :onh_lpn_id', 'ONHAND');
970 inv_mwb_query_manager.add_bind_variable('onh_lpn_id', inv_mwb_globals.g_tree_parent_lpn_id);
971 inv_mwb_query_manager.add_qf_where_lpn_node('ONHAND');
972 inv_mwb_query_manager.execute_query;
973 RETURN;
974 END IF;
975
976 IF inv_mwb_globals.g_tree_mat_loc_id = 1 THEN
977 IF (inv_mwb_globals.g_serial_from IS NOT NULL OR
978 inv_mwb_globals.g_serial_to IS NOT NULL)
979 OR (NVL(l_lot_control, 1) = 1
980 AND l_serial_control IN ( 2,5 ))
981 OR inv_mwb_globals.g_serial_attr_query IS NOT NULL THEN -- Bug 6429880
982 make_common_query_onhand('MSN');
983 inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
984
985 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
986 'msn.current_subinventory_code';
987 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
988 'msn.current_locator_id';
989 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
990 'msn.lpn_id';
991 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
992 'msn.cost_group_id';
993 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.REVISION).column_value :=
994 'msn.revision';
995 inv_mwb_query_manager.add_group_clause('msn.revision','ONHAND');
996
997 IF l_lot_control = 2 THEN
998 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
999 'msn.lot_number';
1000 inv_mwb_query_manager.add_group_clause('msn.lot_number','ONHAND');
1001 ELSIF l_serial_control IN (2, 5) THEN
1002 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SERIAL).column_value :=
1003 'msn.serial_number';
1004 inv_mwb_query_manager.add_group_clause('msn.serial_number','ONHAND');
1005 END IF;
1006
1007 inv_mwb_query_manager.add_group_clause('msn.current_subinventory_code','ONHAND');
1008 inv_mwb_query_manager.add_group_clause('msn.current_locator_id','ONHAND');
1009 inv_mwb_query_manager.add_group_clause('msn.lpn_id','ONHAND');
1010 inv_mwb_query_manager.add_group_clause('msn.cost_group_id','ONHAND');
1011
1012 inv_mwb_query_manager.add_where_clause('msn.lpn_id = :onh_lpn_id', 'ONHAND');
1013 inv_mwb_query_manager.add_bind_variable('onh_lpn_id', inv_mwb_globals.g_tree_parent_lpn_id);
1014 inv_mwb_query_manager.add_where_clause('msn.inventory_item_id = :onh_inventory_item_id', 'ONHAND');
1015 inv_mwb_query_manager.add_bind_variable('onh_inventory_item_id', inv_mwb_globals.g_tree_item_id);
1016 inv_mwb_query_manager.add_where_clause('msn.revision = :onh_revision', 'ONHAND');
1017 inv_mwb_query_manager.add_bind_variable('onh_revision', inv_mwb_globals.g_tree_rev);
1018
1019
1020 ELSE
1021 make_common_query_onhand('MOQD');
1022 inv_mwb_query_manager.add_qf_where_onhand('ONHAND');
1023 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
1024 'moqd.subinventory_code';
1025 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
1026 'moqd.locator_id';
1027 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
1028 'moqd.lpn_id';
1029 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
1030 'moqd.cost_group_id';
1031 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.REVISION).column_value :=
1032 'moqd.revision';
1033 inv_mwb_query_manager.add_group_clause('moqd.revision','ONHAND');
1034
1035 IF l_lot_control = 2 THEN
1036 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
1037 'moqd.lot_number';
1038 inv_mwb_query_manager.add_group_clause('moqd.lot_number','ONHAND');
1039 END IF;
1040
1041 inv_mwb_query_manager.add_group_clause('moqd.subinventory_code','ONHAND');
1042 inv_mwb_query_manager.add_group_clause('moqd.locator_id','ONHAND');
1043 inv_mwb_query_manager.add_group_clause('moqd.lpn_id','ONHAND');
1044 inv_mwb_query_manager.add_group_clause('moqd.cost_group_id','ONHAND');
1045
1046 /* LPN Status Project */
1047 -- For serial controlled items, the status_id will be populated in post_query of IMVMWQMB.
1048 IF (inv_cache.set_org_rec(inv_mwb_globals.g_tree_organization_id)) THEN
1049 l_default_status_id := inv_cache.org_rec.default_status_id;
1050 END IF;
1051
1052 IF inv_cache.set_item_rec(inv_mwb_globals.g_tree_organization_id, inv_mwb_globals.g_tree_item_id) THEN
1053 IF (inv_cache.item_rec.serial_number_control_code in (2,5)) THEN
1054 l_serial_controlled := 1; -- Item is serial controlled
1055 END IF;
1056
1057 IF (inv_cache.item_rec.lot_control_code <> 1) THEN
1058 l_lot_controlled := 1; -- Item is lot controlled
1059 END IF;
1060 END IF;
1061
1062 IF (l_default_status_id IS NOT NULL AND l_serial_controlled = 0 AND l_lot_controlled = 0) THEN
1063 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.STATUS_ID).column_value :=
1064 'moqd.status_id';
1065 inv_mwb_query_manager.add_group_clause('moqd.status_id', 'ONHAND');
1066 END IF;
1067
1068 /* LPN Status Project */
1069
1070 inv_mwb_query_manager.add_where_clause('moqd.lpn_id = :onh_lpn_id', 'ONHAND');
1071 inv_mwb_query_manager.add_bind_variable('onh_lpn_id', inv_mwb_globals.g_tree_parent_lpn_id);
1072 inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = :onh_inventory_item_id', 'ONHAND');
1073 inv_mwb_query_manager.add_bind_variable('onh_inventory_item_id', inv_mwb_globals.g_tree_item_id);
1074 inv_mwb_query_manager.add_where_clause('moqd.revision = :onh_revision', 'ONHAND');
1075 inv_mwb_query_manager.add_bind_variable('onh_revision', inv_mwb_globals.g_tree_rev);
1076
1077 END IF;
1078 END IF;
1079
1080 IF inv_mwb_globals.g_tree_mat_loc_id = 2 THEN
1081 IF inv_mwb_globals.g_serial_from IS NOT NULL OR
1082 inv_mwb_globals.g_serial_to IS NOT NULL
1083 OR (NVL(l_lot_control, 1) = 1
1084 AND l_serial_control IN ( 2,5 )) THEN
1085
1086 make_common_query_receiving('MSN_QUERY');
1087 inv_mwb_query_manager.add_qf_where_receiving('MSN_RECEIVING');
1088 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
1089 'msn.current_subinventory_code';
1090 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
1091 'msn.current_locator_id';
1092 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.LPN_ID).column_value :=
1093 'msn.lpn_id';
1094
1095 inv_mwb_query_manager.add_group_clause('msn.current_subinventory_code','RECEIVING');
1096 inv_mwb_query_manager.add_group_clause('msn.current_locator_id','RECEIVING');
1097 inv_mwb_query_manager.add_group_clause('msn.lpn_id','RECEIVING');
1098
1099 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.REVISION).column_value :=
1100 'msn.revision';
1101 inv_mwb_query_manager.add_group_clause('msn.revision','RECEIVING');
1102
1103 IF l_lot_control = 2 THEN
1104 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
1105 'msn.lot_number';
1106 inv_mwb_query_manager.add_group_clause('msn.lot_number','RECEIVING');
1107 ELSIF l_serial_control IN (2, 5) THEN
1108 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SERIAL).column_value :=
1109 'msn.serial_number';
1110 inv_mwb_query_manager.add_group_clause('msn.serial_number','RECEIVING');
1111 END IF;
1112
1113 inv_mwb_query_manager.add_where_clause('msn.lpn_id = :rcv_lpn_id', 'RECEIVING');
1114 inv_mwb_query_manager.add_bind_variable('rcv_lpn_id', inv_mwb_globals.g_tree_parent_lpn_id);
1115 inv_mwb_query_manager.add_where_clause('msn.revision = :onh_revision', 'RECEIVING');
1116 inv_mwb_query_manager.add_bind_variable('onh_revision', inv_mwb_globals.g_tree_rev);
1117 inv_mwb_query_manager.add_where_clause('msn.inventory_item_id = :onh_inventory_item_id', 'RECEIVING');
1118 inv_mwb_query_manager.add_bind_variable('onh_inventory_item_id', inv_mwb_globals.g_tree_item_id);
1119
1120 ELSE
1121
1122 make_common_query_receiving('RECEIVING');
1123 inv_mwb_query_manager.add_qf_where_receiving('RECEIVING');
1124 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
1125 'rs.to_subinventory';
1126 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
1127 'rs.to_locator_id';
1128 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.LPN_ID).column_value :=
1129 'rs.lpn_id';
1130
1131 inv_mwb_query_manager.add_group_clause('rs.to_subinventory','RECEIVING');
1132 inv_mwb_query_manager.add_group_clause('rs.to_locator_id','RECEIVING');
1133 inv_mwb_query_manager.add_group_clause('rs.lpn_id','RECEIVING');
1134
1135 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.REVISION).column_value :=
1136 'rs.item_revision';
1137 inv_mwb_query_manager.add_group_clause('rs.item_revision','RECEIVING');
1138
1139 IF l_lot_control = 2 THEN
1140 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
1141 'rls.lot_num';
1142 inv_mwb_query_manager.add_group_clause('rls.lot_num','RECEIVING');
1143 END IF;
1144
1145 inv_mwb_query_manager.add_where_clause('rs.lpn_id = :rcv_lpn_id', 'RECEIVING');
1146 inv_mwb_query_manager.add_bind_variable('rcv_lpn_id', inv_mwb_globals.g_tree_parent_lpn_id);
1147 inv_mwb_query_manager.add_where_clause('rs.item_revision = :onh_revision', 'RECEIVING');
1148 inv_mwb_query_manager.add_bind_variable('onh_revision', inv_mwb_globals.g_tree_rev);
1149 inv_mwb_query_manager.add_where_clause('rs.item_id = :onh_inventory_item_id', 'RECEIVING');
1150 inv_mwb_query_manager.add_bind_variable('onh_inventory_item_id', inv_mwb_globals.g_tree_item_id);
1151
1152 END IF;
1153 END IF;
1154 inv_mwb_query_manager.execute_query;
1155 END IF;
1156 EXCEPTION
1157 WHEN NO_DATA_FOUND THEN
1158 NULL;
1159 END rev_node_event;
1160
1161 PROCEDURE lot_node_event (
1162 x_node_value IN OUT NOCOPY NUMBER
1163 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
1164 , x_tbl_index IN OUT NOCOPY NUMBER
1165 ) IS
1166
1167 l_serial_control NUMBER;
1168 l_procedure_name VARCHAR2(30);
1169
1170 /* LPN Status Project */
1171 l_serial_controlled NUMBER := 0;
1172 l_default_status_id NUMBER;
1173 l_status_id NUMBER;
1174
1175 BEGIN
1176
1177 l_procedure_name := 'LOT_NODE_EVENT';
1178
1179 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_EXPANDED' THEN
1180 inv_mwb_tree1.add_serials (
1181 x_node_value
1182 , x_node_tbl
1183 , x_tbl_index);
1184
1185 ELSIF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
1186
1187 SELECT serial_number_control_code
1188 INTO l_serial_control
1189 FROM mtl_system_items
1190 WHERE inventory_item_id = inv_mwb_globals.g_tree_item_id
1191 AND organization_id = inv_mwb_globals.g_organization_id;
1192
1193 IF inv_mwb_globals.g_chk_onhand = 0
1194 AND inv_mwb_globals.g_chk_receiving = 0
1195 AND inv_mwb_globals.g_chk_inbound = 0 THEN
1196 make_common_query_lpn;
1197 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
1198 'wlpn.subinventory_code';
1199 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
1200 'wlpn.locator_id';
1201 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
1202 'wlpn.lpn_id';
1203 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
1204 'wlc.cost_group_id';
1205 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.REVISION).column_value :=
1206 'wlc.revision';
1207 inv_mwb_query_manager.add_group_clause('wlc.revision','ONHAND');
1208
1209 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
1210 'wlc.lot_number';
1211 inv_mwb_query_manager.add_group_clause('wlc.lot_number','ONHAND');
1212
1213 IF l_serial_control IN (2, 5) THEN
1214 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SERIAL).column_value :=
1215 'msn.serial_number';
1216 inv_mwb_query_manager.add_group_clause('msn.serial_number','ONHAND');
1217 END IF;
1218
1219 inv_mwb_query_manager.add_group_clause('wlpn.subinventory_code','ONHAND');
1220 inv_mwb_query_manager.add_group_clause('wlpn.locator_id','ONHAND');
1221 inv_mwb_query_manager.add_group_clause('wlpn.lpn_id','ONHAND');
1222 inv_mwb_query_manager.add_group_clause('wlc.cost_group_id','ONHAND');
1223
1224 /* LPN Status Project */
1225 -- For serial controlled items, the status_id will be populated in post_query of IMVMWQMB.
1226
1227 inv_mwb_globals.print_msg(g_pkg_name, l_procedure_name, 'org id' ||inv_mwb_globals.g_tree_organization_id );
1228
1229 IF(inv_cache.set_org_rec(inv_mwb_globals.g_tree_organization_id)) THEN
1230 l_default_status_id := inv_cache.org_rec.default_status_id;
1231 END IF;
1232
1233 inv_mwb_globals.print_msg(g_pkg_name, l_procedure_name, 'org status id' || l_default_status_id);
1234
1235 IF inv_cache.set_item_rec(inv_mwb_globals.g_tree_organization_id, inv_mwb_globals.g_tree_item_id) THEN
1236 IF (inv_cache.item_rec.serial_number_control_code in (2,5)) THEN
1237 l_serial_controlled := 1; -- Item is serial controlled
1238 END IF;
1239 END IF;
1240
1241 IF (l_default_status_id is not null and l_serial_controlled = 0) THEN
1242
1243 l_status_id := INV_MATERIAL_STATUS_GRP.get_default_status(p_organization_id => inv_mwb_globals.g_organization_id,
1244 p_inventory_item_id=>inv_mwb_globals.g_tree_item_id ,
1245 p_sub_code=>inv_mwb_globals.g_tree_subinventory_code,
1246 p_loc_id=>inv_mwb_globals.g_tree_loc_id,
1247 p_lot_number=>inv_mwb_globals.g_tree_lot_number,
1248 p_lpn_id=>inv_mwb_globals.g_tree_parent_lpn_id);
1249
1250 inv_mwb_globals.print_msg( g_pkg_name, l_procedure_name, 'Value of l_status_id:'|| l_status_id );
1251
1252 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.STATUS_ID).column_value :=
1253 l_status_id;
1254 inv_mwb_query_manager.add_group_clause(l_status_id, 'ONHAND');
1255
1256 END IF;
1257 /* End of fix for LPN Status Project */
1258
1259 inv_mwb_query_manager.add_where_clause('wlpn.lpn_id = :onh_lpn_id', 'ONHAND');
1260 inv_mwb_query_manager.add_where_clause('wlc.lot_number = :onh_lot_number', 'ONHAND');
1261 inv_mwb_query_manager.add_bind_variable('onh_lot_number', inv_mwb_globals.g_tree_lot_number);
1262 inv_mwb_query_manager.add_bind_variable('onh_lpn_id', inv_mwb_globals.g_tree_parent_lpn_id);
1263 inv_mwb_query_manager.add_qf_where_lpn_node('ONHAND');
1264 inv_mwb_query_manager.execute_query;
1265 RETURN;
1266 END IF;
1267
1268 IF inv_mwb_globals.g_tree_mat_loc_id = 1 THEN
1269 IF (inv_mwb_globals.g_serial_from IS NOT NULL OR
1270 inv_mwb_globals.g_serial_to IS NOT NULL)
1271 OR l_serial_control IN ( 2,5 )
1272 OR inv_mwb_globals.g_serial_attr_query IS NOT NULL THEN -- Bug 6429880
1273 make_common_query_onhand('MSN');
1274 inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
1275
1276 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
1277 'msn.current_subinventory_code';
1278 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
1279 'msn.current_locator_id';
1280 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
1281 'msn.lpn_id';
1282 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
1283 'msn.cost_group_id';
1284 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.REVISION).column_value :=
1285 'msn.revision';
1286 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
1287 'msn.lot_number';
1288
1289
1290 IF l_serial_control IN (2, 5) THEN
1291 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SERIAL).column_value :=
1292 'msn.serial_number';
1293 inv_mwb_query_manager.add_group_clause('msn.serial_number','ONHAND');
1294 END IF;
1295
1296 inv_mwb_query_manager.add_group_clause('msn.current_subinventory_code','ONHAND');
1297 inv_mwb_query_manager.add_group_clause('msn.current_locator_id','ONHAND');
1298 inv_mwb_query_manager.add_group_clause('msn.lpn_id','ONHAND');
1299 inv_mwb_query_manager.add_group_clause('msn.cost_group_id','ONHAND');
1300 inv_mwb_query_manager.add_group_clause('msn.revision','ONHAND');
1301 inv_mwb_query_manager.add_group_clause('msn.lot_number','ONHAND');
1302
1303 inv_mwb_query_manager.add_where_clause('msn.lpn_id = :onh_lpn_id', 'ONHAND');
1304 inv_mwb_query_manager.add_bind_variable('onh_lpn_id', inv_mwb_globals.g_tree_parent_lpn_id);
1305 inv_mwb_query_manager.add_where_clause('msn.inventory_item_id = :onh_inventory_item_id', 'ONHAND');
1306 inv_mwb_query_manager.add_bind_variable('onh_inventory_item_id', inv_mwb_globals.g_tree_item_id);
1307
1308 IF inv_mwb_globals.g_tree_rev IS NOT NULL THEN
1309 inv_mwb_query_manager.add_where_clause('msn.revision = :onh_revision', 'ONHAND');
1310 inv_mwb_query_manager.add_bind_variable('onh_revision', inv_mwb_globals.g_tree_rev);
1311 END IF;
1312 ELSE
1313 make_common_query_onhand('MOQD');
1314 inv_mwb_query_manager.add_qf_where_onhand('ONHAND');
1315 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
1316 'moqd.subinventory_code';
1317 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
1318 'moqd.locator_id';
1319 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
1320 'moqd.lpn_id';
1321 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
1322 'moqd.cost_group_id';
1323 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.REVISION).column_value :=
1324 'moqd.revision';
1325 inv_mwb_query_manager.add_group_clause('moqd.revision','ONHAND');
1326
1327 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
1328 'moqd.lot_number';
1329 inv_mwb_query_manager.add_group_clause('moqd.lot_number','ONHAND');
1330
1331 inv_mwb_query_manager.add_group_clause('moqd.subinventory_code','ONHAND');
1332 inv_mwb_query_manager.add_group_clause('moqd.locator_id','ONHAND');
1333 inv_mwb_query_manager.add_group_clause('moqd.lpn_id','ONHAND');
1334 inv_mwb_query_manager.add_group_clause('moqd.cost_group_id','ONHAND');
1335
1336 /* LPN Status Project */
1337 -- For serial controlled items, the status_id will be populated in post_query of IMVMWQMB.
1338
1339 inv_mwb_globals.print_msg(g_pkg_name, l_procedure_name, 'org id' ||inv_mwb_globals.g_tree_organization_id );
1340
1341 IF(inv_cache.set_org_rec(inv_mwb_globals.g_tree_organization_id)) THEN
1342 l_default_status_id := inv_cache.org_rec.default_status_id;
1343 END IF;
1344
1345 inv_mwb_globals.print_msg(g_pkg_name, l_procedure_name, 'org status id' || l_default_status_id);
1346
1347 IF inv_cache.set_item_rec(inv_mwb_globals.g_tree_organization_id, inv_mwb_globals.g_tree_item_id) THEN
1348 IF (inv_cache.item_rec.serial_number_control_code in (2,5)) THEN
1349 l_serial_controlled := 1; -- Item is serial controlled
1350 END IF;
1351 END IF;
1352
1353 inv_mwb_globals.print_msg(g_pkg_name, l_procedure_name, 'serial controlled' || l_serial_controlled);
1354
1355 IF (l_default_status_id is not null and l_serial_controlled = 0) THEN
1356 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.STATUS_ID).column_value :=
1357 'moqd.status_id';
1358 inv_mwb_query_manager.add_group_clause('moqd.status_id', 'ONHAND');
1359 END IF;
1360 /* LPN Status Project */
1361
1362 inv_mwb_query_manager.add_where_clause('moqd.lpn_id = :onh_lpn_id', 'ONHAND');
1363 inv_mwb_query_manager.add_bind_variable('onh_lpn_id', inv_mwb_globals.g_tree_parent_lpn_id);
1364 inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = :onh_inventory_item_id', 'ONHAND');
1365 inv_mwb_query_manager.add_bind_variable('onh_inventory_item_id', inv_mwb_globals.g_tree_item_id);
1366 inv_mwb_query_manager.add_where_clause('moqd.lot_number = :onh_lot_number', 'ONHAND');
1367 inv_mwb_query_manager.add_bind_variable('onh_lot_number', inv_mwb_globals.g_tree_lot_number);
1368 IF inv_mwb_globals.g_tree_rev IS NOT NULL THEN
1369 inv_mwb_query_manager.add_where_clause('moqd.revision = :onh_revision', 'ONHAND');
1370 inv_mwb_query_manager.add_bind_variable('onh_revision', inv_mwb_globals.g_tree_rev);
1371 END IF;
1372 END IF;
1373 END IF;
1374
1375 IF inv_mwb_globals.g_tree_mat_loc_id = 2 THEN
1376 IF (inv_mwb_globals.g_serial_from IS NOT NULL OR
1377 inv_mwb_globals.g_serial_to IS NOT NULL)
1378 OR l_serial_control IN ( 2,5 ) THEN
1379
1380 make_common_query_receiving('MSN_QUERY');
1381 inv_mwb_query_manager.add_qf_where_receiving('MSN_RECEIVING');
1382 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
1383 'msn.current_subinventory_code';
1384 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
1385 'msn.current_locator_id';
1386 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.LPN_ID).column_value :=
1387 'msn.lpn_id';
1388
1389 inv_mwb_query_manager.add_group_clause('msn.current_subinventory_code','RECEIVING');
1390 inv_mwb_query_manager.add_group_clause('msn.current_locator_id','RECEIVING');
1391 inv_mwb_query_manager.add_group_clause('msn.lpn_id','RECEIVING');
1392
1393 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.REVISION).column_value :=
1394 'msn.revision';
1395 inv_mwb_query_manager.add_group_clause('msn.revision','RECEIVING');
1396
1397 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
1398 'msn.lot_number';
1399 inv_mwb_query_manager.add_group_clause('msn.lot_number','RECEIVING');
1400
1401 IF l_serial_control IN (2, 5) THEN
1402 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SERIAL).column_value :=
1403 'msn.serial_number';
1404 inv_mwb_query_manager.add_group_clause('msn.serial_number','RECEIVING');
1405 END IF;
1406
1407 inv_mwb_query_manager.add_where_clause('msn.lpn_id = :rcv_lpn_id', 'RECEIVING');
1408 inv_mwb_query_manager.add_bind_variable('rcv_lpn_id', inv_mwb_globals.g_tree_parent_lpn_id);
1409 inv_mwb_query_manager.add_where_clause('msn.lot_number = :onh_lot_number', 'RECEIVING');
1410 inv_mwb_query_manager.add_bind_variable('onh_lot_number', inv_mwb_globals.g_tree_lot_number);
1411
1412 IF inv_mwb_globals.g_tree_rev IS NOT NULL THEN
1413 inv_mwb_query_manager.add_where_clause('msn.revision = :onh_revision', 'RECEIVING');
1414 inv_mwb_query_manager.add_bind_variable('onh_revision', inv_mwb_globals.g_tree_rev);
1415 END IF;
1416 inv_mwb_query_manager.add_where_clause('msn.inventory_item_id = :onh_inventory_item_id', 'RECEIVING');
1417 inv_mwb_query_manager.add_bind_variable('onh_inventory_item_id', inv_mwb_globals.g_tree_item_id);
1418
1419 ELSE
1420
1421 make_common_query_receiving('RECEIVING');
1422 inv_mwb_query_manager.add_qf_where_receiving('RECEIVING');
1423 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
1424 'rs.to_subinventory';
1425 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
1426 'rs.to_locator_id';
1427 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.LPN_ID).column_value :=
1428 'rs.lpn_id';
1429
1430 inv_mwb_query_manager.add_group_clause('rs.to_subinventory','RECEIVING');
1431 inv_mwb_query_manager.add_group_clause('rs.to_locator_id','RECEIVING');
1432 inv_mwb_query_manager.add_group_clause('rs.lpn_id','RECEIVING');
1433
1434 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.REVISION).column_value :=
1435 'rs.item_revision';
1436 inv_mwb_query_manager.add_group_clause('rs.item_revision','RECEIVING');
1437
1438
1439 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
1440 'rls.lot_num';
1441 inv_mwb_query_manager.add_group_clause('rls.lot_num','RECEIVING');
1442
1443
1444 inv_mwb_query_manager.add_where_clause('rs.lpn_id = :rcv_lpn_id', 'RECEIVING');
1445 inv_mwb_query_manager.add_bind_variable('rcv_lpn_id', inv_mwb_globals.g_tree_parent_lpn_id);
1446 IF inv_mwb_globals.g_tree_rev IS NOT NULL THEN
1447 inv_mwb_query_manager.add_where_clause('rs.item_revision = :onh_revision', 'RECEIVING');
1448 inv_mwb_query_manager.add_bind_variable('onh_revision', inv_mwb_globals.g_tree_rev);
1449 END IF;
1450 inv_mwb_query_manager.add_where_clause('rls.lot_number = :onh_lot_number', 'RECEIVING');
1451 inv_mwb_query_manager.add_bind_variable('onh_lot_number', inv_mwb_globals.g_tree_lot_number);
1452
1453 inv_mwb_query_manager.add_where_clause('rs.item_id = :onh_inventory_item_id', 'RECEIVING');
1454 inv_mwb_query_manager.add_bind_variable('onh_inventory_item_id', inv_mwb_globals.g_tree_item_id);
1455
1456 END IF;
1457 END IF;
1458
1459 inv_mwb_query_manager.execute_query;
1460 END IF;
1461
1462 EXCEPTION
1463 WHEN NO_DATA_FOUND THEN
1464 NULL;
1465 END lot_node_event;
1466
1467 PROCEDURE serial_node_event (
1468 x_node_value IN OUT NOCOPY NUMBER
1469 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
1470 , x_tbl_index IN OUT NOCOPY NUMBER
1471 ) IS
1472 l_procedure_name VARCHAR2(30);
1473 BEGIN
1474
1475 l_procedure_name := 'SERIAL_NODE_EVENT';
1476
1477 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
1478 IF inv_mwb_globals.g_chk_onhand = 0
1479 AND inv_mwb_globals.g_chk_receiving = 0
1480 AND inv_mwb_globals.g_chk_inbound = 0
1481 AND inv_mwb_globals.g_view_by = 'LPN' THEN
1482 make_common_query_lpn;
1483 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
1484 'wlpn.subinventory_code';
1485 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
1486 'wlpn.locator_id';
1487 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
1488 'wlpn.lpn_id';
1489 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
1490 'wlc.cost_group_id';
1491 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.REVISION).column_value :=
1492 'wlc.revision';
1493 inv_mwb_query_manager.add_group_clause('wlc.revision','ONHAND');
1494
1495 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
1496 'wlc.lot_number';
1497 inv_mwb_query_manager.add_group_clause('wlc.lot_number','ONHAND');
1498
1499 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SERIAL).column_value :=
1500 'msn.serial_number';
1501 inv_mwb_query_manager.add_group_clause('msn.serial_number','ONHAND');
1502
1503 inv_mwb_query_manager.add_group_clause('wlpn.subinventory_code','ONHAND');
1504 inv_mwb_query_manager.add_group_clause('wlpn.locator_id','ONHAND');
1505 inv_mwb_query_manager.add_group_clause('wlpn.lpn_id','ONHAND');
1506 inv_mwb_query_manager.add_group_clause('wlc.cost_group_id','ONHAND');
1507
1508 inv_mwb_query_manager.add_where_clause('wlpn.lpn_id = :onh_lpn_id', 'ONHAND');
1509 inv_mwb_query_manager.add_where_clause('msn.serial_number = :onh_serial_number', 'ONHAND');
1510 inv_mwb_query_manager.add_bind_variable('onh_serial_number', inv_mwb_globals. g_tree_serial_number);
1511 inv_mwb_query_manager.add_bind_variable('onh_lpn_id', inv_mwb_globals.g_tree_parent_lpn_id);
1512 inv_mwb_query_manager.add_qf_where_lpn_node('ONHAND');
1513 inv_mwb_query_manager.execute_query;
1514 RETURN;
1515 END IF;
1516
1517 IF inv_mwb_globals.g_tree_mat_loc_id = 1 THEN
1518 make_common_query_onhand('MSN');
1519 inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
1520
1521 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
1522 'msn.current_subinventory_code';
1523 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
1524 'msn.current_locator_id';
1525 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
1526 'msn.lpn_id';
1527 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
1528 'msn.cost_group_id';
1529 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.REVISION).column_value :=
1530 'msn.revision';
1531 inv_mwb_query_manager.add_group_clause('msn.revision','ONHAND');
1532
1533 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
1534 'msn.lot_number';
1535 inv_mwb_query_manager.add_group_clause('msn.lot_number','ONHAND');
1536
1537 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SERIAL).column_value :=
1538 'msn.serial_number';
1539 inv_mwb_query_manager.add_group_clause('msn.serial_number','ONHAND');
1540
1541 inv_mwb_query_manager.add_group_clause('msn.current_subinventory_code','ONHAND');
1542 inv_mwb_query_manager.add_group_clause('msn.current_locator_id','ONHAND');
1543 inv_mwb_query_manager.add_group_clause('msn.lpn_id','ONHAND');
1544 inv_mwb_query_manager.add_group_clause('msn.cost_group_id','ONHAND');
1545
1546 inv_mwb_query_manager.add_where_clause('msn.lpn_id = :onh_lpn_id', 'ONHAND');
1547 inv_mwb_query_manager.add_bind_variable('onh_lpn_id', inv_mwb_globals.g_tree_parent_lpn_id);
1548 inv_mwb_query_manager.add_where_clause('msn.inventory_item_id = :onh_inventory_item_id', 'ONHAND');
1549 inv_mwb_query_manager.add_bind_variable('onh_inventory_item_id', inv_mwb_globals.g_tree_item_id);
1550 inv_mwb_query_manager.add_where_clause('msn.serial_number = :onh_serial_number', 'ONHAND');
1551 inv_mwb_query_manager.add_bind_variable('onh_serial_number', inv_mwb_globals. g_tree_serial_number);
1552
1553 IF inv_mwb_globals.g_tree_rev IS NOT NULL THEN
1554 inv_mwb_query_manager.add_where_clause('msn.revision = :onh_revision', 'ONHAND');
1555 inv_mwb_query_manager.add_bind_variable('onh_revision', inv_mwb_globals.g_tree_rev);
1556 END IF;
1557 IF inv_mwb_globals.g_tree_lot_number IS NOT NULL THEN
1558 inv_mwb_query_manager.add_where_clause('msn.lot_number = :onh_lot_number', 'ONHAND');
1559 inv_mwb_query_manager.add_bind_variable('onh_lot_number', inv_mwb_globals.g_tree_lot_number);
1560 END IF;
1561 END IF;
1562
1563 IF inv_mwb_globals.g_tree_mat_loc_id = 2 THEN
1564 make_common_query_receiving('MSN_QUERY');
1565 inv_mwb_query_manager.add_qf_where_receiving('MSN_RECEIVING');
1566
1567 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
1568 'msn.current_subinventory_code';
1569 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
1570 'msn.current_locator_id';
1571 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.LPN_ID).column_value :=
1572 'msn.lpn_id';
1573 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.CG_ID).column_value :=
1574 'msn.cost_group_id';
1575 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.REVISION).column_value :=
1576 'msn.revision';
1577 inv_mwb_query_manager.add_group_clause('msn.revision','RECEIVING');
1578
1579 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.LOT).column_value :=
1580 'msn.lot_number';
1581 inv_mwb_query_manager.add_group_clause('msn.lot_number','RECEIVING');
1582
1583 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SERIAL).column_value :=
1584 'msn.serial_number';
1585 inv_mwb_query_manager.add_group_clause('msn.serial_number','RECEIVING');
1586
1587 inv_mwb_query_manager.add_group_clause('msn.current_subinventory_code','RECEIVING');
1588 inv_mwb_query_manager.add_group_clause('msn.current_locator_id','RECEIVING');
1589 inv_mwb_query_manager.add_group_clause('msn.lpn_id','RECEIVING');
1590 inv_mwb_query_manager.add_group_clause('msn.cost_group_id','RECEIVING');
1591
1592 inv_mwb_query_manager.add_where_clause('msn.lpn_id = :onh_lpn_id', 'RECEIVING');
1593 inv_mwb_query_manager.add_bind_variable('onh_lpn_id', inv_mwb_globals.g_tree_parent_lpn_id);
1594 inv_mwb_query_manager.add_where_clause('msn.inventory_item_id = :onh_inventory_item_id', 'RECEIVING');
1595 inv_mwb_query_manager.add_bind_variable('onh_inventory_item_id', inv_mwb_globals.g_tree_item_id);
1596 inv_mwb_query_manager.add_where_clause('msn.serial_number = :onh_serial_number', 'RECEIVING');
1597 inv_mwb_query_manager.add_bind_variable('onh_serial_number', inv_mwb_globals. g_tree_serial_number);
1598
1599 IF inv_mwb_globals.g_tree_rev IS NOT NULL THEN
1600 inv_mwb_query_manager.add_where_clause('msn.revision = :onh_revision', 'RECEIVING');
1601 inv_mwb_query_manager.add_bind_variable('onh_revision', inv_mwb_globals.g_tree_rev);
1602 END IF;
1603 IF inv_mwb_globals.g_tree_lot_number IS NOT NULL THEN
1604 inv_mwb_query_manager.add_where_clause('msn.lot_number = :onh_lot_number', 'RECEIVING');
1605 inv_mwb_query_manager.add_bind_variable('onh_lot_number', inv_mwb_globals.g_tree_lot_number);
1606 END IF;
1607 END IF;
1608
1609 inv_mwb_query_manager.execute_query;
1610
1611 END IF;
1612 EXCEPTION
1613 WHEN NO_DATA_FOUND THEN
1614 NULL;
1615 END serial_node_event;
1616
1617 --
1618 -- public functions
1619 --
1620
1621 --
1622 -- General APPTREE event handler for the EMPLOYEE tab.
1623 --
1624
1625
1626 PROCEDURE make_common_query_onhand(p_flag VARCHAR2) IS
1627 BEGIN
1628 IF(inv_mwb_globals.g_chk_onhand = 1) THEN
1629 CASE p_flag
1630 WHEN 'MSN' THEN
1631 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ITEM_ID).column_value :=
1632 'msn.inventory_item_id';
1633
1634 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ORG_ID).column_value :=
1635 'msn.current_organization_id';
1636
1637 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PRIMARY_UOM_CODE).column_value :=
1638 '''Ea''';
1639
1640 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ONHAND).column_value := 1;
1641
1642 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PACKED).column_value := 1;
1643
1644
1645 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.UNPACKED).column_value := 0;
1646
1647 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SECONDARY_UOM_CODE).column_value :=
1648 'NULL';
1649
1650 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SECONDARY_PACKED).column_value :=
1651 'NULL';
1652
1653 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SECONDARY_UNPACKED).column_value :=
1654 'NULL';
1655
1656 -- inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SERIAL).column_value :=
1657 --- 'msn.serial_number';
1658
1659 inv_mwb_query_manager.add_from_clause('mtl_serial_numbers msn', 'ONHAND');
1660
1661 inv_mwb_query_manager.add_group_clause('msn.inventory_item_id', 'ONHAND');
1662 inv_mwb_query_manager.add_group_clause('msn.current_organization_id', 'ONHAND');
1663 inv_mwb_query_manager.add_group_clause('''Ea''', 'ONHAND');
1664 -- inv_mwb_query_manager.add_group_clause('msn.serial_number', 'ONHAND');
1665
1666 inv_mwb_query_manager.add_where_clause('msn.current_status = 3', 'ONHAND');
1667 inv_mwb_query_manager.add_where_clause('msn.lpn_id IS NOT NULL', 'ONHAND');
1668
1669 WHEN 'MOQD' THEN
1670
1671 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ITEM_ID).column_value :=
1672 'moqd.inventory_item_id';
1673
1674 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ORG_ID).column_value :=
1675 'moqd.organization_id';
1676
1677 -- inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PRIMARY_UOM_CODE).column_value :=
1678 -- 'moqd.transaction_uom_code';
1679
1680 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ONHAND).column_value :=
1681 'SUM(moqd.primary_transaction_quantity)';
1682
1683 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PACKED).column_value :=
1684 'SUM(moqd.primary_transaction_quantity)';
1685
1686 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.UNPACKED).column_value := 0;
1687
1688 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SECONDARY_UOM_CODE).column_value :=
1689 'moqd.secondary_uom_code';
1690
1691 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SECONDARY_ONHAND).column_value :=
1692 'SUM(moqd.secondary_transaction_quantity)';
1693
1694 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SECONDARY_PACKED).column_value :=
1695 'SUM(moqd.secondary_transaction_quantity)';
1696
1697 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SECONDARY_UNPACKED).column_value := 0;
1698
1699
1700 inv_mwb_query_manager.add_from_clause('mtl_onhand_quantities_detail moqd', 'ONHAND');
1701
1702 inv_mwb_query_manager.add_where_clause('moqd.lpn_id IS NOT NULL', 'ONHAND');
1703 inv_mwb_query_manager.add_group_clause('moqd.inventory_item_id', 'ONHAND');
1704 inv_mwb_query_manager.add_group_clause('moqd.organization_id', 'ONHAND');
1705 -- inv_mwb_query_manager.add_group_clause('moqd.transaction_uom_code', 'ONHAND');
1706 inv_mwb_query_manager.add_group_clause('moqd.secondary_uom_code', 'ONHAND');
1707
1708 WHEN 'MSN_QUERY' THEN
1709 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ONHAND).column_value :=
1710 'count(1)';
1711 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PACKED).column_value :=
1712 'count(1)';
1713 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.UNPACKED).column_value := 0;
1714
1715 inv_mwb_query_manager.add_group_clause('msn.inventory_item_id' , 'ONHAND');
1716 inv_mwb_query_manager.add_group_clause('msn.current_organization_id', 'ONHAND');
1717 inv_mwb_query_manager.add_group_clause('''Ea''', 'ONHAND');
1718
1719 inv_mwb_query_manager.add_from_clause('mtl_serial_numbers msn', 'ONHAND');
1720 inv_mwb_query_manager.add_where_clause('msn.current_status = 3', 'ONHAND');
1721 inv_mwb_query_manager.add_where_clause('msn.lpn_id IS NOT NULL', 'ONHAND');
1722 END CASE; -- p_flag
1723 END IF;
1724 END;
1725
1726 PROCEDURE make_common_query_receiving(p_flag VARCHAR2) IS
1727 l_procedure_name VARCHAR2(30);
1728 BEGIN
1729 l_procedure_name := 'MAKE_COMMON_QUERY_RECEIVING';
1730 inv_mwb_globals.print_msg(g_pkg_name, l_procedure_name, 'Entered');
1731 inv_mwb_globals.print_msg(g_pkg_name, l_procedure_name, 'p_flag : ' || p_flag);
1732 IF(inv_mwb_globals.g_chk_receiving = 1) THEN
1733 IF p_flag = 'RECEIVING' THEN
1734 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.ORG_ID).column_value :=
1735 'rs.to_organization_id';
1736 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.ITEM_ID).column_value :=
1737 'rs.item_id';
1738 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.RECEIVING).column_value :=
1739 'SUM(rs.to_org_primary_quantity)';
1740
1741 inv_mwb_query_manager.add_group_clause('rs.to_organization_id','RECEIVING');
1742 inv_mwb_query_manager.add_group_clause('rs.item_id','RECEIVING');
1743 inv_mwb_query_manager.add_where_clause('rs.lpn_id IS NOT NULL', 'RECEIVING');
1744
1745
1746 IF inv_mwb_globals.g_multiple_loc_selected = 'FALSE'
1747 OR inv_mwb_globals.g_tree_node_type <> 'APPTREE_OBJECT_TRUNK' THEN
1748 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.UNPACKED).column_value := 0;
1749 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.PACKED).column_value :=
1750 'SUM(rs.to_org_primary_quantity)';
1751 END IF;
1752
1753 ELSIF p_flag = 'RCV_TREE_LPN' THEN
1754
1755 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.ORG_ID).column_value :=
1756 'wlpn.organization_id';
1757 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.ITEM_ID).column_value :=
1758 'wlc.inventory_item_id';
1759 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.PRIMARY_UOM_CODE).column_value :=
1760 'wlc.uom_code';
1761 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.RECEIVING).column_value :=
1762 'SUM(wlc.primary_quantity)';--bug 4761399
1763
1764 inv_mwb_query_manager.add_group_clause('wlpn.organization_id','RECEIVING');
1765 inv_mwb_query_manager.add_group_clause('wlc.inventory_item_id','RECEIVING');
1766 inv_mwb_query_manager.add_group_clause('wlc.uom_code','RECEIVING');
1767
1768 IF inv_mwb_globals.g_multiple_loc_selected = 'FALSE'
1769 OR inv_mwb_globals.g_tree_node_type <> 'APPTREE_OBJECT_TRUNK' THEN
1770 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SECONDARY_UOM_CODE).column_value :=
1771 'wlc.secondary_uom_code';
1772 inv_mwb_query_manager.add_group_clause('wlc.secondary_uom_code','RECEIVING');
1773 END IF;
1774
1775 ELSIF p_flag = 'MSN' THEN
1776
1777 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.ITEM_ID).column_value :=
1778 'msn.inventory_item_id';
1779 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.ORG_ID).column_value :=
1780 'msn.current_organization_id';
1781 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.PRIMARY_UOM_CODE).column_value :=
1782 '''Ea''';
1783 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.receiving).column_value := 1;
1784
1785 inv_mwb_query_manager.add_group_clause('msn.current_organization_id', 'RECEIVING');
1786 inv_mwb_query_manager.add_group_clause('msn.inventory_item_id', 'RECEIVING');
1787 inv_mwb_query_manager.add_group_clause('''Ea''', 'RECEIVING');
1788
1789 IF inv_mwb_globals.g_multiple_loc_selected = 'FALSE'
1790 OR inv_mwb_globals.g_tree_node_type <> 'APPTREE_OBJECT_TRUNK' THEN
1791 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.PACKED).column_value := 1;
1792 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.UNPACKED).column_value := 0;
1793 END IF;
1794
1795 ELSIF p_flag = 'MSN_QUERY' THEN
1796 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.ITEM_ID).column_value :=
1797 'msn.inventory_item_id';
1798 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.ORG_ID).column_value :=
1799 'msn.current_organization_id';
1800 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.PRIMARY_UOM_CODE).column_value :=
1801 '''Ea''';
1802 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.receiving).column_value :=
1803 'count(1)';
1804 inv_mwb_query_manager.add_group_clause('msn.inventory_item_id' , 'RECEIVING');
1805 inv_mwb_query_manager.add_group_clause('msn.current_organization_id', 'RECEIVING');
1806 inv_mwb_query_manager.add_group_clause('''Ea''', 'RECEIVING');
1807
1808 IF inv_mwb_globals.g_multiple_loc_selected = 'FALSE'
1809 OR inv_mwb_globals.g_tree_node_type <> 'APPTREE_OBJECT_TRUNK' THEN
1810 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.PACKED).column_value :=
1811 'count(1)';
1812 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.UNPACKED).column_value := 0;
1813 END IF;
1814 END IF;
1815 END IF; -- End if for receiving
1816 END make_common_query_receiving;
1817
1818
1819 PROCEDURE make_common_query_lpn
1820 IS
1821 BEGIN
1822 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ORG_ID).column_value :=
1823 'wlpn.organization_id';
1824 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ITEM_ID).column_value :=
1825 'wlc.inventory_item_id';
1826 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PRIMARY_UOM_CODE).column_value :=
1827 'wlc.uom_code';
1828 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SECONDARY_UOM_CODE).column_value :=
1829 'wlc.secondary_uom_code';
1830 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ONHAND).column_value :=
1831 'SUM(wlc.primary_quantity)'; --bug 4761399
1832
1833 -- IF inv_mwb_globals.g_prepacked <> 12 THEN -- For All states chosen dont add this where clause. -- Bug : 6023196
1834 IF inv_mwb_globals.g_prepacked <> 999 THEN -- For All states chosen dont add this where clause. -- Bug : 6023196
1835 inv_mwb_query_manager.add_where_clause('wlpn.lpn_context = :onh_lpn_context', 'ONHAND');
1836 inv_mwb_query_manager.add_bind_variable('onh_lpn_context', inv_mwb_globals.g_prepacked);
1837 END IF;
1838
1839 inv_mwb_query_manager.add_group_clause('wlpn.organization_id','ONHAND');
1840 inv_mwb_query_manager.add_group_clause('wlc.inventory_item_id','ONHAND');
1841 inv_mwb_query_manager.add_group_clause('wlc.uom_code','ONHAND');
1842 inv_mwb_query_manager.add_group_clause('wlc.secondary_uom_code','ONHAND');
1843 END;
1844
1845 PROCEDURE event (
1846 x_node_value IN OUT NOCOPY NUMBER
1847 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
1848 , x_tbl_index IN OUT NOCOPY NUMBER
1849 ) IS
1850 l_procedure_name VARCHAR2(30);
1851 BEGIN
1852
1853 l_procedure_name := 'EVENT';
1854 x_tbl_index := 1;
1855 x_node_value := 1;
1856
1857 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_EXPANDED'
1858 OR inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
1859
1860 CASE inv_mwb_globals.g_tree_node_type
1861 WHEN 'APPTREE_OBJECT_TRUNK' THEN
1862 root_node_event (
1863 x_node_value
1864 , x_node_tbl
1865 , x_tbl_index
1866 );
1867 WHEN 'ORG' THEN
1868 org_node_event (
1869 x_node_value
1870 , x_node_tbl
1871 , x_tbl_index
1872 );
1873 WHEN 'ITEM' THEN
1874 item_node_event (
1875 x_node_value
1876 , x_node_tbl
1877 , x_tbl_index
1878 );
1879
1880 WHEN 'MATLOC' THEN
1881 mat_loc_node_event (
1882 x_node_value
1883 , x_node_tbl
1884 , x_tbl_index
1885 );
1886
1887 WHEN 'REV' THEN
1888 rev_node_event (
1889 x_node_value
1890 , x_node_tbl
1891 , x_tbl_index
1892 );
1893
1894 WHEN 'LPN' THEN
1895 lpn_node_event (
1896 x_node_value
1897 , x_node_tbl
1898 , x_tbl_index
1899 );
1900
1901 WHEN 'LOT' THEN
1902 lot_node_event (
1903 x_node_value
1904 , x_node_tbl
1905 , x_tbl_index
1906 );
1907 WHEN 'SERIAL' THEN
1908 serial_node_event (
1909 x_node_value
1910 , x_node_tbl
1911 , x_tbl_index
1912 );
1913
1914 END CASE;
1915 END IF;
1916
1917 EXCEPTION
1918 WHEN NO_DATA_FOUND THEN
1919 NULL;
1920 END event;
1921
1922 END INV_MWB_LPN_TREE;