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