[Home] [Help]
PACKAGE BODY: APPS.INV_MWB_LOCATION_TREE
Source
1 PACKAGE BODY inv_mwb_location_tree AS
2 /* $Header: INVMWLEB.pls 120.67.12020000.3 2012/09/07 05:58:19 srsomasu ship $ */
3
4 g_pkg_name VARCHAR2(30) := 'INV_MWB_LOCATION_TREE';
5
6 -- PROCEDURE make_common_query_onhand(p_flag VARCHAR2); -- Bug 6060233 : Putting the declaration in the package spec.
7 PROCEDURE make_common_query_receiving(p_flag VARCHAR2);
8 PROCEDURE make_common_query_inbound(p_flag VARCHAR2);
9
10 PROCEDURE root_node_event (
11 x_node_value IN OUT NOCOPY NUMBER
12 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
13 , x_tbl_index IN OUT NOCOPY NUMBER
14 ) IS
15 i NUMBER;
16 j NUMBER;
17 l_procedure_name VARCHAR2(30);
18
19 BEGIN
20
21 l_procedure_name := 'ROOT_NODE_EVENT';
22 inv_mwb_globals.print_msg( g_pkg_name, l_procedure_name, 'Entered' );
23
24 i := x_tbl_index;
25 j := x_node_value;
26
27
28 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_EXPANDED' THEN
29
30 inv_mwb_tree1.add_orgs(
31 x_node_value
32 , x_node_tbl
33 , x_tbl_index
34 );
35
36 ELSIF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
37
38 IF( inv_mwb_globals.g_chk_onhand = 1) THEN
39
40 IF inv_mwb_globals.g_serial_from IS NOT NULL
41 OR inv_mwb_globals.g_serial_to IS NOT NULL
42 OR inv_mwb_globals.g_status_id IS NOT NULL
43 OR inv_mwb_globals.g_serial_attr_query IS NOT NULL -- Bug 6429880
44 OR inv_mwb_globals.g_unit_number IS NOT NULL -- Bug 9486070
45 THEN
46 make_common_query_onhand('MSN_QUERY');
47
48 IF inv_mwb_globals.g_detailed = 1 THEN
49 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
50 'msn.current_subinventory_code';
51 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
52 'msn.current_locator_id';
53 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.REVISION).column_value :=
54 'msn.revision';
55
56 inv_mwb_query_manager.add_group_clause('msn.current_subinventory_code', 'ONHAND');
57 inv_mwb_query_manager.add_group_clause('msn.current_locator_id', 'ONHAND');
58 inv_mwb_query_manager.add_group_clause('msn.revision', 'ONHAND');
59 END IF;
60 inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
61 ELSE
62 make_common_query_onhand('MOQD');
63 IF inv_mwb_globals.g_detailed = 1 THEN
64 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
65 'moqd.subinventory_code';
66 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
67 'moqd.locator_id';
68 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.REVISION).column_value :=
69 'moqd.revision';
70
71 inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
72 inv_mwb_query_manager.add_group_clause('moqd.locator_id', 'ONHAND');
73 inv_mwb_query_manager.add_group_clause('moqd.revision', 'ONHAND');
74 END IF;
75 inv_mwb_query_manager.add_qf_where_onhand('ONHAND');
76 END IF;
77
78 END IF;
79
80 IF( inv_mwb_globals.g_chk_inbound = 1) THEN
81 inv_mwb_query_manager.add_qf_where_inbound('INBOUND');
82 make_common_query_inbound('INBOUND');
83 END IF;
84
85 IF( inv_mwb_globals.g_chk_receiving = 1) THEN
86 IF inv_mwb_globals.g_serial_from IS NOT NULL OR
87 inv_mwb_globals.g_serial_to IS NOT NULL THEN
88 make_common_query_receiving('MSN_QUERY');
89 inv_mwb_query_manager.add_qf_where_receiving('MSN');
90 ELSE
91 make_common_query_receiving('RECEIVING');
92 IF inv_mwb_globals.g_detailed = 1 THEN
93 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
94 'rs.to_subinventory';
95 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
96 'rs.to_locator_id';
97 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.REVISION).column_value :=
98 'rs.item_revision';
99
100 inv_mwb_query_manager.add_group_clause('rs.to_subinventory', 'RECEIVING');
101 inv_mwb_query_manager.add_group_clause('rs.to_locator_id', 'RECEIVING');
102 inv_mwb_query_manager.add_group_clause('rs.item_revision', 'RECEIVING');
103 END IF;
104 inv_mwb_query_manager.add_qf_where_receiving('RECEIVING');
105 END IF;
106 END IF;
107 inv_mwb_globals.print_msg( g_pkg_name, l_procedure_name, 'Going to call execute_query');
108 inv_mwb_query_manager.execute_query;
109 END IF;
110
111 EXCEPTION
112 WHEN no_data_found THEN
113 NULL;
114 END root_node_event;
115
116
117 PROCEDURE org_node_event(
118 x_node_value IN OUT NOCOPY NUMBER
119 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
120 , x_tbl_index IN OUT NOCOPY NUMBER
121 )
122 IS
123 i NUMBER := 1;
124 j NUMBER := 1;
125 TYPE tab IS TABLE OF varchar2(100) index by binary_integer;
126 mtl_loc_type tab;
127 str_query varchar2(4000);
128 l_procedure_name VARCHAR2(30);
129 BEGIN
130
131 l_procedure_name := 'ORG_NODE_EVENT';
132 inv_mwb_globals.print_msg( g_pkg_name, l_procedure_name, 'Entered' );
133
134 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_EXPANDED' THEN
135
136 SELECT meaning
137 BULK COLLECT INTO mtl_loc_type
138 FROM mfg_lookups
139 WHERE lookup_type = 'MTL_LOCATION_TYPES'
140 ORDER BY lookup_code;
141
142 inv_mwb_globals.print_msg( g_pkg_name, l_procedure_name, 'Selected all the document types' );
143
144 IF inv_mwb_globals.g_chk_onhand = 1
145 THEN
146 x_node_tbl(i).state := -1;
147 x_node_tbl(i).DEPTH := 1;
148 x_node_tbl(i).label := mtl_loc_type(1);
149 x_node_tbl(i).icon := 'tree_workflowpackage';
150 x_node_tbl(i).VALUE := 1;
151 x_node_tbl(i).TYPE := 'MATLOC';
152 i := i + 1;
153 END IF;
154
155 IF NVL(inv_mwb_globals.g_chk_receiving, 1) = 1
156 THEN
157 x_node_tbl(i).state := -1;
158 x_node_tbl(i).DEPTH := 1;
159 x_node_tbl(i).label := mtl_loc_type(2);
160 x_node_tbl(i).icon := 'tree_workflowpackage';
161 x_node_tbl(i).VALUE := 2;
162 x_node_tbl(i).TYPE := 'MATLOC';
163 i := i + 1;
164 END IF;
165
166 IF inv_mwb_globals.g_chk_inbound = 1
167 THEN
168 x_node_tbl(i).state := -1;
169 x_node_tbl(i).DEPTH := 1;
170 x_node_tbl(i).label := mtl_loc_type(3);
171 x_node_tbl(i).icon := 'tree_workflowpackage';
172 x_node_tbl(i).VALUE := 3;
173 x_node_tbl(i).TYPE := 'MATLOC';
174 i := i + 1;
175 END IF;
176
177
178 ELSIF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
179 inv_mwb_globals.print_msg( g_pkg_name, l_procedure_name, 'Tree Node Selected' );
180
181 IF(inv_mwb_globals.g_chk_onhand = 1) THEN
182 IF (inv_mwb_globals.g_serial_from IS NOT NULL ---serials entered in qf
183 OR inv_mwb_globals.g_serial_to IS NOT NULL)
184 OR inv_mwb_globals.g_status_id IS NOT NULL -- Bug 6060233
185 OR inv_mwb_globals.g_serial_attr_query IS NOT NULL -- Bug 6429880
186 OR inv_mwb_globals.g_unit_number IS NOT NULL -- Bug 9486070
187 THEN
188 make_common_query_onhand('MSN_QUERY');
189 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
190 'msn.current_subinventory_code';
191 inv_mwb_query_manager.add_group_clause('msn.current_subinventory_code', 'ONHAND');
192
193 inv_mwb_query_manager.add_where_clause('msn.current_organization_id = :onh_tree_organization_id', 'ONHAND');
194 inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
195 inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
196 ELSE
197 make_common_query_onhand('MOQD');
198 inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id', 'ONHAND');
199 inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
200
201 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
202 'moqd.subinventory_code';
203 inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
204 inv_mwb_query_manager.add_qf_where_onhand('ONHAND');
205 END IF;
206 END IF;
207
208 IF(inv_mwb_globals.g_chk_receiving = 1) THEN
209 IF inv_mwb_globals.g_serial_from IS NOT NULL OR
210 inv_mwb_globals.g_serial_to IS NOT NULL THEN
211 make_common_query_receiving('MSN_QUERY');
212 inv_mwb_query_manager.add_qf_where_receiving('MSN');
213 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
214 'rs.to_subinventory';
215 inv_mwb_query_manager.add_group_clause('rs.to_subinventory', 'RECEIVING');
216 inv_mwb_query_manager.add_where_clause('rs.to_organization_id = :rcv_tree_organization_id', 'RECEIVING');
217 inv_mwb_query_manager.add_bind_variable('rcv_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
218 ELSE
219 make_common_query_receiving('RECEIVING');
220 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
221 'rs.to_subinventory';
222 inv_mwb_query_manager.add_group_clause('rs.to_subinventory', 'RECEIVING');
223 inv_mwb_query_manager.add_where_clause('rs.to_organization_id = :rcv_tree_organization_id', 'RECEIVING');
224 inv_mwb_query_manager.add_bind_variable('rcv_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
225 inv_mwb_query_manager.add_qf_where_receiving('RECEIVING');
226 END IF;
227 END IF;
228
229 IF(inv_mwb_globals.g_chk_inbound = 1) THEN
230 make_common_query_inbound('INBOUND');
231 inv_mwb_query_manager.add_where_clause('ms.to_organization_id = :inb_tree_organization_id', 'INBOUND');
232 inv_mwb_query_manager.add_bind_variable('inb_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
233 inv_mwb_query_manager.add_qf_where_inbound('INBOUND');
234 END IF;
235 inv_mwb_query_manager.execute_query;
236 END IF; -- tree event
237 EXCEPTION
238 WHEN no_data_found THEN
239 NULL;
240 END org_node_event;
241
242
243 PROCEDURE sub_node_event(
244 x_node_value IN OUT NOCOPY NUMBER
245 ,x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
246 ,x_tbl_index IN OUT NOCOPY NUMBER
247 )
248 IS
249
250 is_locator_controlled BOOLEAN;
251 loc_type NUMBER;
252 str_query VARCHAR2(4000);
253 l_procedure_name VARCHAR2(30);
254
255 BEGIN
256
257 x_tbl_index := 1;
258 x_node_value := 1;
259 is_locator_controlled := FALSE;
260 l_procedure_name := 'SUB_NODE_EVENT';
261
262
263 IF (inv_mwb_globals.g_locator_control_code = 2
264 OR inv_mwb_globals.g_locator_control_code = 3) THEN
265 is_locator_controlled := TRUE;
266 ELSIF (inv_mwb_globals.g_locator_control_code = 1) THEN
267 is_locator_controlled := FALSE;
268 ELSIF (inv_mwb_globals.g_locator_control_code = 4)
269 AND inv_mwb_globals.g_tree_organization_id IS NOT NULL
270 AND inv_mwb_globals.g_tree_subinventory_code IS NOT NULL THEN
271
272 SELECT locator_type
273 INTO loc_type
274 FROM mtl_secondary_inventories
275 WHERE secondary_inventory_name = inv_mwb_globals.g_tree_subinventory_code
276 AND organization_id = inv_mwb_globals.g_tree_organization_id;
277
278 IF loc_type = 1 THEN
279 is_locator_controlled := FALSE;
280 ELSE
281 is_locator_controlled := TRUE;
282 END IF;
283 END IF;
284
285 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_EXPANDED' THEN
286 IF (is_locator_controlled = TRUE) THEN
287
288 inv_mwb_tree1.add_locs(
289 x_node_value
290 , x_node_tbl
291 , x_tbl_index
292 );
293 END IF;
294
295 -- If the given subinventory is not locator controlled then add items
296 -- directly under subinventory, else if the locator is determined at item level
297 -- add both items and subinventories.
298
299 IF is_locator_controlled = FALSE
300 OR inv_mwb_globals.g_locator_control_code = 5
301 OR loc_type = 5
302 THEN
303 IF inv_mwb_globals.g_lpn_from IS NULL
304 AND inv_mwb_globals.g_lpn_to IS NULL THEN
305
306 inv_mwb_globals.g_containerized := 1;
307 inv_mwb_globals.g_locator_controlled := 1;
308
309 inv_mwb_tree1.add_items(
310 x_node_value
311 , x_node_tbl
312 , x_tbl_index
313 );
314 END IF;
315 END IF;
316
317 ELSIF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
318
319 -- IF(inv_mwb_globals.g_chk_onhand = 1) THEN
320 IF(inv_mwb_globals.g_tree_mat_loc_id = 1) THEN
321 IF inv_mwb_globals.g_serial_from IS NOT NULL ---serials entered in qf
322 OR inv_mwb_globals.g_serial_to IS NOT NULL
323 OR inv_mwb_globals.g_status_id IS NOT NULL
324 OR inv_mwb_globals.g_serial_attr_query IS NOT NULL -- Bug 6429880
325 OR inv_mwb_globals.g_unit_number IS NOT NULL -- Bug 9486070
326 THEN
327 make_common_query_onhand('MSN_QUERY');
328 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
329 'msn.current_subinventory_code';
330 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
331 'msn.current_locator_id';
332
333 inv_mwb_query_manager.add_group_clause('msn.current_subinventory_code', 'ONHAND');
334 inv_mwb_query_manager.add_group_clause('msn.current_locator_id', 'ONHAND');
335
336 inv_mwb_query_manager.add_where_clause('msn.current_organization_id = :onh_tree_organization_id', 'ONHAND');
337 inv_mwb_query_manager.add_where_clause('msn.current_subinventory_code = :onh_tree_subinventory_code', 'ONHAND');
338
339 inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
340 inv_mwb_query_manager.add_bind_variable('onh_tree_subinventory_code', inv_mwb_globals.g_tree_subinventory_code);
341
342 inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
343 ELSE
344 make_common_query_onhand('MOQD');
345 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
346 'moqd.subinventory_code';
347 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
348 'moqd.locator_id';
349
350 inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
351 inv_mwb_query_manager.add_group_clause('moqd.locator_id', 'ONHAND');
352
353 inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id', 'ONHAND');
354 inv_mwb_query_manager.add_where_clause('moqd.subinventory_code = :onh_tree_subinventory_code', 'ONHAND');
355
356 inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
357 inv_mwb_query_manager.add_bind_variable('onh_tree_subinventory_code', inv_mwb_globals.g_tree_subinventory_code);
358
359 inv_mwb_query_manager.add_qf_where_onhand('ONHAND');
360 END IF;
361
362 END IF;
363
364 IF (inv_mwb_globals.g_tree_mat_loc_id = 2) THEN -- Receiving node chosen
365 IF inv_mwb_globals.g_serial_from IS NOT NULL OR
366 inv_mwb_globals.g_serial_to IS NOT NULL THEN
367 make_common_query_receiving('MSN_QUERY');
368 inv_mwb_query_manager.add_qf_where_receiving('MSN');
369 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
370 'rs.to_subinventory';
371 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
372 'rs.to_locator_id';
373
374 inv_mwb_query_manager.add_group_clause('rs.to_subinventory', 'RECEIVING');
375 inv_mwb_query_manager.add_group_clause('rs.to_locator_id', 'RECEIVING');
376 inv_mwb_query_manager.add_where_clause('rs.to_organization_id = :rcv_tree_organization_id', 'RECEIVING');
377 inv_mwb_query_manager.add_where_clause('rs.to_subinventory = :rcv_tree_subinventory_code', 'RECEIVING');
378
379 inv_mwb_query_manager.add_bind_variable('rcv_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
380 inv_mwb_query_manager.add_bind_variable('rcv_tree_subinventory_code', inv_mwb_globals.g_tree_subinventory_code);
381 ELSE
382 make_common_query_receiving('RECEIVING');
383 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
384 'rs.to_subinventory';
385 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
386 'rs.to_locator_id';
387
388 inv_mwb_query_manager.add_group_clause('rs.to_subinventory', 'RECEIVING');
389 inv_mwb_query_manager.add_group_clause('rs.to_locator_id', 'RECEIVING');
390
391 inv_mwb_query_manager.add_where_clause('rs.to_organization_id = :rcv_tree_organization_id', 'RECEIVING');
392 inv_mwb_query_manager.add_where_clause('rs.to_subinventory = :rcv_tree_subinventory_code', 'RECEIVING');
393
394 inv_mwb_query_manager.add_bind_variable('rcv_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
395 inv_mwb_query_manager.add_bind_variable('rcv_tree_subinventory_code', inv_mwb_globals.g_tree_subinventory_code);
396
397 inv_mwb_query_manager.add_qf_where_receiving('RECEIVING');
398 END IF;
399 END IF;
400 inv_mwb_query_manager.execute_query;
401 END IF; -- node selected
402
403 EXCEPTION
404 WHEN no_data_found THEN
405 NULL;
406 END sub_node_event;
407
408
409 PROCEDURE loc_node_event(
410 x_node_value IN OUT NOCOPY NUMBER
411 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
412 , x_tbl_index IN OUT NOCOPY NUMBER
413 )
414 IS
415 i NUMBER := 1;
416 j NUMBER := 1;
417 str_query varchar2(4000);
418 l_procedure_name VARCHAR2(30);
419
420 BEGIN
421 l_procedure_name := 'LOC_NODE_EVENT';
422 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_EXPANDED' THEN
423 inv_mwb_tree1.add_lpns(
424 x_node_value
425 , x_node_tbl
426 , x_tbl_index
427 );
428
429 IF inv_mwb_globals.g_lpn_from IS NULL
430 AND inv_mwb_globals.g_lpn_to IS NULL THEN
431
432 inv_mwb_globals.g_locator_controlled := 2;
433 inv_mwb_globals.g_containerized := 1;
434
435 inv_mwb_tree1.add_items(
436 x_node_value
437 , x_node_tbl
438 , x_tbl_index
439 );
440 END IF;
441
442 ELSIF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
443
444 IF (inv_mwb_globals.g_tree_mat_loc_id = 1) THEN -- Onhand node chosen
445 IF inv_mwb_globals.g_serial_from IS NOT NULL ---serials entered in qf
446 OR inv_mwb_globals.g_serial_to IS NOT NULL
447 OR inv_mwb_globals.g_status_id IS NOT NULL
448 OR inv_mwb_globals.g_serial_attr_query IS NOT NULL -- Bug 6429880
449 OR inv_mwb_globals.g_unit_number IS NOT NULL -- Bug 9486070
450 THEN
451 make_common_query_onhand('MSN_QUERY');
452
453 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
454 'msn.current_subinventory_code';
455 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
456 'msn.current_locator_id';
457 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
458 'msn.cost_group_id';
459
460 inv_mwb_query_manager.add_group_clause('msn.current_subinventory_code', 'ONHAND');
461 inv_mwb_query_manager.add_group_clause('msn.current_locator_id', 'ONHAND');
462 inv_mwb_query_manager.add_group_clause('msn.cost_group_id', 'ONHAND');
463
464 inv_mwb_query_manager.add_where_clause('msn.current_organization_id = :onh_tree_organization_id', 'ONHAND');
465 inv_mwb_query_manager.add_where_clause('msn.current_subinventory_code = :onh_tree_subinventory_code', 'ONHAND');
466 inv_mwb_query_manager.add_where_clause('msn.current_locator_id = :onh_tree_loc_id', 'ONHAND');
467
468 inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
469 inv_mwb_query_manager.add_bind_variable('onh_tree_subinventory_code', inv_mwb_globals.g_tree_subinventory_code);
470 inv_mwb_query_manager.add_bind_variable('onh_tree_loc_id', inv_mwb_globals.g_tree_loc_id);
471
472 inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
473 ELSE
474 make_common_query_onhand('MOQD');
475 inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id', 'ONHAND');
476 inv_mwb_query_manager.add_where_clause('moqd.subinventory_code = :onh_tree_subinventory_code', 'ONHAND');
477 inv_mwb_query_manager.add_where_clause('moqd.locator_id = :onh_tree_loc_id', 'ONHAND');
478
479 inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
480 inv_mwb_query_manager.add_bind_variable('onh_tree_subinventory_code', inv_mwb_globals.g_tree_subinventory_code);
481 inv_mwb_query_manager.add_bind_variable('onh_tree_loc_id', inv_mwb_globals.g_tree_loc_id);
482
483 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
484 'moqd.subinventory_code';
485 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
486 'moqd.locator_id';
487 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
488 'moqd.lpn_id';
489 --Bug 8840288 Added LotNumber
490 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
491 'moqd.lot_number';
492 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
493 'moqd.cost_group_id';
494
495 inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
496 inv_mwb_query_manager.add_group_clause('moqd.locator_id', 'ONHAND');
497 inv_mwb_query_manager.add_group_clause('moqd.lpn_id', 'ONHAND');
498 --Bug 8840288 Added LotNumber
499 inv_mwb_query_manager.add_group_clause('moqd.lot_number', 'ONHAND');
500 inv_mwb_query_manager.add_group_clause('moqd.cost_group_id', 'ONHAND');
501
502 inv_mwb_query_manager.add_qf_where_onhand('ONHAND');
503 END IF; -- Serial Entered
504 END IF; -- ONHAND
505
506
507 IF (inv_mwb_globals.g_tree_mat_loc_id = 2) THEN -- Receiving node chosen
508 IF inv_mwb_globals.g_serial_from IS NOT NULL OR
509 inv_mwb_globals.g_serial_to IS NOT NULL THEN
510 make_common_query_receiving('MSN_QUERY');
511 inv_mwb_query_manager.add_qf_where_receiving('MSN');
512 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
513 'rs.to_subinventory';
514 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
515 'rs.to_locator_id';
516
517 inv_mwb_query_manager.add_group_clause('rs.to_subinventory', 'RECEIVING');
518 inv_mwb_query_manager.add_group_clause('rs.to_locator_id', 'RECEIVING');
519 inv_mwb_query_manager.add_where_clause('rs.to_organization_id = :rcv_tree_organization_id', 'RECEIVING');
520 inv_mwb_query_manager.add_where_clause('rs.to_subinventory = :rcv_tree_subinventory_code', 'RECEIVING');
521 inv_mwb_query_manager.add_where_clause('rs.to_locator_id = :rcv_tree_locator', 'RECEIVING');
522
523 inv_mwb_query_manager.add_bind_variable('rcv_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
524 inv_mwb_query_manager.add_bind_variable('rcv_tree_subinventory_code', inv_mwb_globals.g_tree_subinventory_code);
525 inv_mwb_query_manager.add_bind_variable('rcv_tree_locator', inv_mwb_globals.g_tree_loc_id);
526 ELSE
527 make_common_query_receiving('RECEIVING');
528 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
529 'rs.to_subinventory';
530 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
531 'rs.to_locator_id';
532
533 inv_mwb_query_manager.add_group_clause('rs.to_subinventory', 'RECEIVING');
534 inv_mwb_query_manager.add_group_clause('rs.to_locator_id', 'RECEIVING');
535
536 inv_mwb_query_manager.add_where_clause('rs.to_organization_id = :rcv_tree_organization_id', 'RECEIVING');
537 inv_mwb_query_manager.add_where_clause('rs.to_subinventory = :rcv_tree_subinventory_code', 'RECEIVING');
538 inv_mwb_query_manager.add_where_clause('rs.to_locator_id = :rcv_tree_locator', 'RECEIVING');
539
540 inv_mwb_query_manager.add_bind_variable('rcv_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
541 inv_mwb_query_manager.add_bind_variable('rcv_tree_subinventory_code', inv_mwb_globals.g_tree_subinventory_code);
542 inv_mwb_query_manager.add_bind_variable('rcv_tree_locator', inv_mwb_globals.g_tree_loc_id);
543
544 inv_mwb_query_manager.add_qf_where_receiving('RECEIVING');
545 END IF;
546 END IF;
547 inv_mwb_query_manager.execute_query;
548 END IF; -- node selected
549 EXCEPTION
550 WHEN no_data_found THEN
551 NULL;
552 END loc_node_event;
553
554 PROCEDURE lpn_node_event(
555 x_node_value IN OUT NOCOPY NUMBER
556 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
557 , x_tbl_index IN OUT NOCOPY NUMBER
558 )
559 IS
560 str_query varchar2(4000);
561 l_procedure_name VARCHAR2(30);
562 l_req_header_id NUMBER;
563 l_lpn_id NUMBER;
564
565 l_rev_control NUMBER;
566 l_lot_control NUMBER;
567 l_serial_control NUMBER;
568 l_return_val BOOLEAN := FALSE; -- bug 8920503
569
570 BEGIN
571 l_procedure_name := 'LPN_NODE_EVENT';
572 inv_mwb_globals.print_msg(g_pkg_name, l_procedure_name, 'Entered');
573
574 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_EXPANDED' THEN
575 inv_mwb_globals.print_msg(g_pkg_name, l_procedure_name, 'Gonig to add LPN');
576
577 IF inv_mwb_globals.g_tree_loc_id IS NULL THEN
578 inv_mwb_globals.g_locator_controlled := 1;
579 END IF;
580 inv_mwb_globals.g_containerized := 2;
581
582 inv_mwb_tree1.add_lpns(
583 x_node_value
584 , x_node_tbl
585 , x_tbl_index
586 );
587 inv_mwb_globals.print_msg(g_pkg_name, l_procedure_name, 'Gonig to add ITEM');
588 inv_mwb_tree1.add_items(
589 x_node_value
590 , x_node_tbl
591 , x_tbl_index
592 );
593
594 ELSIF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
595
596 --START Bug7462485
597 IF (inv_mwb_globals.g_inventory_item_id IS NOT NULL) THEN --bug 8920503
598
599 l_return_val := inv_cache.set_item_rec(inv_mwb_globals.g_tree_organization_id, inv_mwb_globals.g_inventory_item_id);
600 IF l_return_val THEN
601
602 l_lot_control := inv_cache.item_rec.lot_control_code;
603 l_rev_control := inv_cache.item_rec.revision_qty_control_code;
604 l_serial_control:= inv_cache.item_rec.serial_number_control_code;
605
606 END IF;
607 END IF;
608
609 --End Bug7462485
610
611
612 IF (inv_mwb_globals.g_tree_mat_loc_id = 1) THEN -- Onhand node chosen
613
614 inv_mwb_query_manager.make_nested_lpn_onhand_query;
615
616 -- Start Bug7462485 Added code to take care of all the flavors of items.
617
618 IF (inv_mwb_globals.g_serial_from IS NOT NULL
619 OR inv_mwb_globals.g_serial_to IS NOT NULL)
620 OR (NVL(l_rev_control, 1) = 1 AND NVL(l_lot_control, 1) = 1
621 AND l_serial_control IN ( 2,5 ))
622 OR inv_mwb_globals.g_status_id IS NOT NULL
623 OR inv_mwb_globals.g_serial_attr_query IS NOT NULL THEN
624
625 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
626 'msn.current_subinventory_code';
627 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
628 'msn.current_locator_id';
629 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
630 'msn.lpn_id';
631 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
632 'msn.cost_group_id';
633
634
635 inv_mwb_query_manager.add_group_clause('msn.current_subinventory_code', 'ONHAND');
636 inv_mwb_query_manager.add_group_clause('msn.current_locator_id', 'ONHAND');
637 inv_mwb_query_manager.add_group_clause('msn.lpn_id', 'ONHAND');
638 inv_mwb_query_manager.add_group_clause('msn.cost_group_id', 'ONHAND');
639
640 inv_mwb_query_manager.add_where_clause(
641 'msn.lpn_id = :onh_tree_plpn_id' ,
642 'ONHAND'
643 );
644
645 IF inv_mwb_globals.g_tree_loc_id IS NOT NULL THEN
646 inv_mwb_query_manager.add_where_clause(
647 'msn.current_locator_id = :onh_tree_loc_id' ,
648 'ONHAND'
649 );
650 ELSE
651 inv_mwb_query_manager.add_where_clause(
652 'msn.current_locator_id IS NULL' ,
653 'ONHAND'
654 );
655 END IF;
656
657 IF inv_mwb_globals.g_tree_subinventory_code IS NOT NULL THEN
658 inv_mwb_query_manager.add_where_clause(
659 'msn.current_subinventory_code = :onh_tree_sub_code' ,
660 'ONHAND'
661 );
662 END IF;
663 ELSE
664
665 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
666 'moqd.cost_group_id';
667 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
668 'moqd.subinventory_code';
669 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
670 'moqd.locator_id';
671 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
672 'moqd.lpn_id';
673
674 inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
675 inv_mwb_query_manager.add_group_clause('moqd.locator_id', 'ONHAND');
676 inv_mwb_query_manager.add_group_clause('moqd.lpn_id', 'ONHAND');
677 inv_mwb_query_manager.add_group_clause('moqd.cost_group_id', 'ONHAND');
678
679 inv_mwb_query_manager.add_where_clause(
680 'moqd.lpn_id = :onh_tree_plpn_id' ,
681 'ONHAND'
682 );
683
684 IF inv_mwb_globals.g_tree_loc_id IS NOT NULL THEN
685 inv_mwb_query_manager.add_where_clause(
686 'moqd.locator_id = :onh_tree_loc_id' ,
687 'ONHAND'
688 );
689 ELSE
690 inv_mwb_query_manager.add_where_clause(
691 'moqd.locator_id IS NULL' ,
692 'ONHAND'
693 );
694 END IF;
695
696 IF inv_mwb_globals.g_tree_subinventory_code IS NOT NULL THEN
697 inv_mwb_query_manager.add_where_clause(
698 'moqd.subinventory_code = :onh_tree_sub_code' ,
699 'ONHAND'
700 );
701 END IF;
702 END IF;
703
704 IF inv_mwb_globals.g_tree_parent_lpn_id IS NOT NULL THEN
705 inv_mwb_query_manager.add_bind_variable(
706 'onh_tree_plpn_id',
707 inv_mwb_globals.g_tree_parent_lpn_id
708 );
709 END IF;
710
711 IF inv_mwb_globals.g_tree_loc_id IS NOT NULL THEN
712 inv_mwb_query_manager.add_bind_variable(
713 'onh_tree_loc_id',
714 inv_mwb_globals.g_tree_loc_id
715 );
716 END IF;
717
718 IF inv_mwb_globals.g_tree_subinventory_code IS NOT NULL THEN
719 inv_mwb_query_manager.add_bind_variable(
720 'onh_tree_sub_code',
721 inv_mwb_globals.g_tree_subinventory_code
722 );
723 END IF;
724
725 IF NVL(l_rev_control, 1) = 1 AND NVL(l_lot_control, 1) = 1
726 AND l_serial_control IN ( 2,5 ) THEN
727
728 make_common_query_onhand('MSN_QUERY');
729 IF (inv_mwb_globals.g_inventory_item_id IS NOT NULL) THEN
730 inv_mwb_query_manager.add_where_clause(
731 'msn.inventory_item_id = :onh_tree_inventory_item_id' ,
732 'ONHAND'
733 );
734 END IF;
735 inv_mwb_query_manager.add_where_clause(
736 'msn.current_organization_id = :onh_tree_organization_id' ,
737 'ONHAND'
738 );
739 inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
740
741 ELSE -- only serial controlled
742 IF inv_mwb_globals.g_serial_from IS NOT NULL OR
743 inv_mwb_globals.g_serial_to IS NOT NULL
744 OR inv_mwb_globals.g_serial_attr_query IS NOT NULL -- Bug 6429880
745 OR inv_mwb_globals.g_unit_number IS NOT NULL -- Bug 9486070
746 THEN
747 make_common_query_onhand('MSN_QUERY');
748 IF l_rev_control = 2 THEN
749 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
750 'msn.revision';
751 inv_mwb_query_manager.add_group_clause('msn.revision', 'ONHAND');
752 END IF;
753
754 IF NVL(l_rev_control, 1) = 1 AND l_lot_control = 2 THEN
755 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
756 'msn.lot_number';
757 inv_mwb_query_manager.add_group_clause('msn.lot_number', 'ONHAND');
758 END IF;
759 IF (inv_mwb_globals.g_inventory_item_id IS NOT NULL) THEN
760 inv_mwb_query_manager.add_where_clause(
761 'msn.inventory_item_id = :onh_tree_inventory_item_id' ,
762 'ONHAND'
763 );
764 END IF;
765 inv_mwb_query_manager.add_where_clause(
766 'msn.current_organization_id = :onh_tree_organization_id' ,
767 'ONHAND'
768 );
769 inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
770 ELSE -- serial entered in qf
771 make_common_query_onhand('MOQD');
772
773 IF (inv_mwb_globals.g_inventory_item_id IS NOT NULL) THEN
774 inv_mwb_query_manager.add_where_clause(
775 'moqd.inventory_item_id = :onh_tree_inventory_item_id' ,
776 'ONHAND'
777 );
778 END IF;
779 inv_mwb_query_manager.add_where_clause(
780 'moqd.organization_id = :onh_tree_organization_id' ,
781 'ONHAND'
782 );
783
784 -- Bug 8601470
785 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
786 'moqd.revision';
787 inv_mwb_query_manager.add_group_clause('moqd.revision', 'ONHAND');
788
789
790 -- Bug 8601470
791 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
792 'moqd.lot_number';
793 inv_mwb_query_manager.add_group_clause('moqd.lot_number', 'ONHAND');
794
795 inv_mwb_query_manager.add_qf_where_onhand('ONHAND');
796 END IF; -- serial in query find
797 END IF; -- only serial controlled
798 inv_mwb_query_manager.add_bind_variable(
799 'onh_tree_organization_id',
800 inv_mwb_globals.g_tree_organization_id
801 );
802 IF (inv_mwb_globals.g_inventory_item_id IS NOT NULL) THEN
803 inv_mwb_query_manager.add_bind_variable(
804 'onh_tree_inventory_item_id',
805 inv_mwb_globals.g_inventory_item_id
806 );
807 ENd IF;
808 END IF; --ONHAND
809
810 -- End Bug7462485
811
812 IF (inv_mwb_globals.g_tree_mat_loc_id = 3) THEN -- Inbound node chosen
813 inv_mwb_query_manager.make_nested_lpn_inbound_query;
814 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.OWNING_ORG_ID).column_value :=
815 'ms.intransit_owning_org_id';
816 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.EXPECTED_RECEIPT_DATE).column_value :=
817 'ms.expected_delivery_date';
818 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.FROM_ORG_ID).column_value :=
819 'ms.from_organization_id';
820 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.TO_ORG_ID).column_value :=
821 'ms.to_organization_id'; -- Bug 11716231
822 inv_mwb_query_manager.add_group_clause('ms.intransit_owning_org_id', 'INBOUND');
823 inv_mwb_query_manager.add_group_clause('ms.expected_delivery_date', 'INBOUND');
824 inv_mwb_query_manager.add_group_clause('ms.from_organization_id', 'INBOUND');
825 inv_mwb_query_manager.add_group_clause('ms.to_organization_id', 'INBOUND'); -- Bug 11716231
826
827 CASE inv_mwb_globals.g_tree_doc_type_id
828 WHEN 1 THEN
829 null;
830 WHEN 2 THEN
831 make_common_query_inbound('INBOUND');
832 inv_mwb_query_manager.add_qf_where_inbound('INBOUND');
833 inv_mwb_globals.print_msg(g_pkg_name, l_procedure_name, 'For item under Requisition');
834 inv_mwb_globals.print_msg(g_pkg_name, l_procedure_name, 'Under Req item selecetd');
835 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.REQ_HEADER_ID).column_value :=
836 'ms.req_header_id';
837 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.DOCUMENT_LINE_NUMBER).column_value :=
838 'ms.req_line_id';
839 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.LPN_ID).column_value :=
840 'rsl.asn_lpn_id';
841
842 inv_mwb_query_manager.add_group_clause('ms.req_header_id', 'INBOUND');
843 inv_mwb_query_manager.add_group_clause('ms.req_line_id', 'INBOUND');
844 inv_mwb_query_manager.add_group_clause('rsl.asn_lpn_id', 'INBOUND');
845 inv_mwb_query_manager.add_where_clause('ms.supply_type_code IN (''REQ'',''SHIPMENT'')', 'INBOUND');
846 inv_mwb_query_manager.add_where_clause('ms.req_header_id = :req_header_id', 'INBOUND');
847 inv_mwb_query_manager.add_where_clause('ms.to_organization_id = :inb_tree_organization_id', 'INBOUND');
848 inv_mwb_query_manager.add_where_clause('ms.item_id = :item_id', 'INBOUND');
849 inv_mwb_query_manager.add_bind_variable('req_header_id', inv_mwb_globals.g_tree_doc_header_id);
850 inv_mwb_query_manager.add_bind_variable('item_id', inv_mwb_globals.g_tree_item_id);
851 inv_mwb_query_manager.add_bind_variable('inb_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
852 WHEN 3 THEN
853 inv_mwb_globals.print_msg(g_pkg_name, l_procedure_name, 'Making query for internal intransit');
854 make_common_query_inbound('INBOUND');
855 inv_mwb_query_manager.add_qf_where_inbound('INBOUND');
856 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.SHIPMENT_HEADER_ID_ASN).column_value :=
857 'ms.shipment_header_id';
858 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.DOCUMENT_LINE_NUMBER).column_value :=
859 'ms.shipment_line_id';
860 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.LPN_ID).column_value :=
861 'rsl.asn_lpn_id';
862
863 inv_mwb_query_manager.add_group_clause('rsl.asn_lpn_id', 'INBOUND');
864 inv_mwb_query_manager.add_group_clause('ms.shipment_header_id', 'INBOUND');
865 inv_mwb_query_manager.add_group_clause('ms.shipment_line_id', 'INBOUND');
866
867 inv_mwb_query_manager.add_where_clause('ms.supply_type_code = ''SHIPMENT''', 'INBOUND');
868 inv_mwb_query_manager.add_where_clause('rsh.ASN_TYPE IS NULL', 'INBOUND');
869 inv_mwb_query_manager.add_where_clause('rsh.shipment_num = :shipment_num', 'INBOUND');
870 inv_mwb_query_manager.add_where_clause('rsl.asn_lpn_id = :asn_lpn_id', 'INBOUND');
871 inv_mwb_query_manager.add_bind_variable('asn_lpn_id',inv_mwb_globals.g_tree_parent_lpn_id);
872
873 inv_mwb_query_manager.add_bind_variable('shipment_num', inv_mwb_globals.g_tree_doc_num);
874
875 WHEN 4 THEN
876 make_common_query_inbound('INBOUND');
877 inv_mwb_query_manager.add_qf_where_inbound('INBOUND');
878 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.SHIPMENT_HEADER_ID_ASN).column_value :=
879 'ms.shipment_header_id';
880 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.DOCUMENT_LINE_NUMBER).column_value :=
881 'ms.shipment_line_id';
882 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.LPN_ID).column_value :=
883 'rsl.asn_lpn_id';
884 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.VENDOR_ID).column_value :=
885 'rsh.vendor_id';
886 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.VENDOR_SITE_ID).column_value :=
887 'rsh.vendor_site_id';
888
889 inv_mwb_query_manager.add_group_clause('rsh.vendor_id', 'INBOUND');
890 inv_mwb_query_manager.add_group_clause('rsh.vendor_site_id', 'INBOUND');
891 inv_mwb_query_manager.add_group_clause('ms.shipment_header_id', 'INBOUND');
892 inv_mwb_query_manager.add_group_clause('ms.shipment_line_id', 'INBOUND');
893 inv_mwb_query_manager.add_group_clause('rsl.asn_lpn_id', 'INBOUND');
894
895 inv_mwb_query_manager.add_where_clause('ms.supply_type_code = ''SHIPMENT''', 'INBOUND');
896 inv_mwb_query_manager.add_where_clause('rsh.ASN_TYPE IS NOT NULL', 'INBOUND');
897 inv_mwb_query_manager.add_where_clause('rsh.RECEIPT_SOURCE_CODE = ''VENDOR''', 'INBOUND');
898 inv_mwb_query_manager.add_where_clause('rsh.shipment_num = :shipment_num', 'INBOUND');
899 inv_mwb_query_manager.add_where_clause('rsl.asn_lpn_id = :asn_lpn_id', 'INBOUND');
900 inv_mwb_query_manager.add_bind_variable('shipment_num', inv_mwb_globals.g_tree_doc_num);
901 inv_mwb_query_manager.add_bind_variable('asn_lpn_id',inv_mwb_globals.g_tree_parent_lpn_id);
902 END CASE;
903 END IF;
904
905
906 IF (inv_mwb_globals.g_tree_mat_loc_id = 2) THEN -- Receiving node chosen
907 inv_mwb_query_manager.make_nested_lpn_rcv_query;
908 IF inv_mwb_globals.g_serial_from IS NOT NULL OR
909 inv_mwb_globals.g_serial_to IS NOT NULL THEN
910 make_common_query_receiving('MSN_QUERY');
911 inv_mwb_query_manager.add_qf_where_receiving('MSN');
912 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
913 'rs.to_subinventory';
914 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
915 'rs.to_locator_id';
916 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.LPN_ID).column_value :=
917 'rs.lpn_id';
918
919 inv_mwb_query_manager.add_group_clause('rs.to_subinventory', 'RECEIVING');
920 inv_mwb_query_manager.add_group_clause('rs.to_locator_id', 'RECEIVING');
921 inv_mwb_query_manager.add_group_clause('rs.lpn_id', 'RECEIVING');
922
923 inv_mwb_query_manager.add_where_clause('rs.to_organization_id = :rcv_tree_organization_id', 'RECEIVING');
924 inv_mwb_query_manager.add_where_clause('rs.lpn_id = :rcv_lpn_id', 'RECEIVING');
925 inv_mwb_query_manager.add_bind_variable('rcv_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
926 inv_mwb_query_manager.add_bind_variable('rcv_lpn_id', inv_mwb_globals.g_tree_parent_lpn_id);
927
928 IF inv_mwb_globals.g_tree_subinventory_code IS NOT NULL THEN
929 inv_mwb_query_manager.add_where_clause('rs.to_subinventory = :rcv_tree_subinventory_code', 'RECEIVING');
930 inv_mwb_query_manager.add_bind_variable('rcv_tree_subinventory_code', inv_mwb_globals.g_tree_subinventory_code);
931 END IF;
932
933 IF inv_mwb_globals.g_tree_loc_id IS NOT NULL THEN
934 inv_mwb_query_manager.add_where_clause('rs.to_locator_id = :rcv_tree_locator', 'RECEIVING');
935 inv_mwb_query_manager.add_bind_variable('rcv_tree_locator', inv_mwb_globals.g_tree_loc_id);
936 END IF;
937
938 ELSE
939 make_common_query_receiving('RCV_TREE_LPN');
940 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
941 'wlpn.subinventory_code';
942 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
943 'wlpn.locator_id';
944 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.LPN_ID).column_value :=
945 'wlpn.lpn_id';
946
947 inv_mwb_query_manager.add_group_clause('wlpn.subinventory_code', 'RECEIVING');
948 inv_mwb_query_manager.add_group_clause('wlpn.locator_id', 'RECEIVING');
949 inv_mwb_query_manager.add_group_clause('wlpn.lpn_id', 'RECEIVING');
950
951 IF inv_mwb_globals.g_tree_organization_id IS NOT NULL THEN
952 inv_mwb_query_manager.add_where_clause('wlpn.organization_id = :rcv_tree_organization_id', 'RECEIVING');
953 inv_mwb_query_manager.add_bind_variable('rcv_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
954 END IF;
955
956 IF inv_mwb_globals.g_tree_subinventory_code IS NOT NULL THEN
957 inv_mwb_query_manager.add_where_clause('wlpn.subinventory_code = :rcv_tree_subinventory_code', 'RECEIVING');
958 inv_mwb_query_manager.add_bind_variable('rcv_tree_subinventory_code', inv_mwb_globals.g_tree_subinventory_code);
959 END IF;
960
961 IF inv_mwb_globals.g_tree_loc_id IS NOT NULL THEN
962 inv_mwb_query_manager.add_where_clause('wlpn.locator_id = :rcv_tree_locator_id', 'RECEIVING');
963 inv_mwb_query_manager.add_bind_variable('rcv_tree_locator_id', inv_mwb_globals.g_tree_loc_id);
964 END IF;
965
966 IF inv_mwb_globals.g_tree_parent_lpn_id IS NOT NULL THEN
967 inv_mwb_query_manager.add_where_clause('wlpn.lpn_id = :rcv_tree_lpn_id', 'RECEIVING');
968 inv_mwb_query_manager.add_bind_variable('rcv_tree_lpn_id', inv_mwb_globals.g_tree_parent_lpn_id);
969 END IF;
970 inv_mwb_query_manager.add_qf_where_lpn_node('RECEIVING');
971 END IF;
972 END IF;
973 inv_mwb_query_manager.execute_query;
974 END IF;
975 inv_mwb_globals.print_msg(g_pkg_name, l_procedure_name, ' Leaving lpn_node_event');
976
977 EXCEPTION
978 WHEN no_data_found THEN
979 NULL;
980 END lpn_node_event;
981
982
983 PROCEDURE item_node_event(
984 x_node_value IN OUT NOCOPY NUMBER
985 ,x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
986 ,x_tbl_index IN OUT NOCOPY NUMBER
987 ) IS
988
989 from_receiving NUMBER;
990 loc_control NUMBER;
991 rev_control NUMBER;
992 lot_control NUMBER;
993 containerized NUMBER;
994 prepacked NUMBER;
995 serial_control NUMBER;
996 select_lot NUMBER;
997 select_serial NUMBER;
998 select_grade NUMBER;
999 str_query VARCHAR2(4000);
1000 l_procedure_name VARCHAR2(30);
1001 l_po_header_id NUMBER;
1002 l_req_header_id NUMBER;
1003 l_shipment_header_id NUMBER;
1004 l_rev_control NUMBER;
1005 l_lot_control NUMBER;
1006 l_serial_control NUMBER;
1007 l_lot_controlled NUMBER := 0; -- Onhand Material Status Support
1008 l_serial_controlled NUMBER := 0; -- Onhand Material Status Support
1009 l_default_status_id NUMBER; -- Onhand Material Status Support
1010
1011 BEGIN
1012 l_procedure_name := 'ITEM_NODE_EVENT';
1013 inv_mwb_globals.print_msg(g_pkg_name, l_procedure_name, 'Entered');
1014
1015 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_EXPANDED' THEN
1016
1017 IF inv_mwb_globals.g_tree_loc_id IS NULL THEN
1018 loc_control := 1;
1019 ELSE
1020 loc_control := 2;
1021 END IF;
1022
1023 IF inv_mwb_globals.g_tree_parent_lpn_id IS NULL THEN
1024 containerized := 1;
1025 ELSE
1026 containerized := 2;
1027 END IF;
1028
1029 IF containerized =1 THEN
1030 prepacked := 1;
1031 ELSE
1032 IF inv_mwb_globals.g_sub_type = 2 THEN
1033 prepacked := 1;
1034 ELSE
1035 prepacked := NULL;
1036 END IF;
1037 END IF;
1038
1039 IF NVL(inv_mwb_globals.g_tree_doc_type_id,-99) <> 1 THEN
1040
1041 inv_mwb_globals.g_locator_controlled := loc_control;
1042 inv_mwb_globals.g_containerized := containerized;
1043
1044 inv_mwb_tree1.add_revs(
1045 x_node_value
1046 , x_node_tbl
1047 , x_tbl_index
1048 );
1049
1050 IF x_tbl_index = 1 THEN
1051
1052 inv_mwb_globals.g_revision_controlled := 1;
1053 inv_mwb_globals.g_locator_controlled := loc_control;
1054 inv_mwb_globals.g_containerized := containerized;
1055
1056 inv_mwb_tree1.add_lots(
1057 x_node_value
1058 , x_node_tbl
1059 , x_tbl_index
1060 );
1061
1062 IF x_tbl_index = 1 THEN
1063
1064 inv_mwb_globals.g_revision_controlled := 1;
1065 inv_mwb_globals.g_locator_controlled := loc_control;
1066 inv_mwb_globals.g_containerized := containerized;
1067 inv_mwb_globals.g_lot_controlled := 1;
1068
1069 inv_mwb_tree1.add_serials(
1070 x_node_value
1071 , x_node_tbl
1072 , x_tbl_index
1073 );
1074 END IF;
1075 END IF;
1076 END IF;
1077 ELSIF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
1078
1079 SELECT revision_qty_control_code,
1080 lot_control_code,
1081 serial_number_control_code
1082 INTO l_rev_control,
1083 l_lot_control,
1084 l_serial_control
1085 FROM mtl_system_items
1086 WHERE inventory_item_id = inv_mwb_globals.g_tree_item_id
1087 AND organization_id = inv_mwb_globals.g_tree_organization_id;
1088
1089 IF inv_mwb_globals.g_tree_mat_loc_id = 1 THEN
1090
1091 IF (inv_mwb_globals.g_serial_from IS NOT NULL
1092 OR inv_mwb_globals.g_serial_to IS NOT NULL)
1093 OR (NVL(l_rev_control, 1) = 1 AND NVL(l_lot_control, 1) = 1
1094 AND l_serial_control IN ( 2,5 ))
1095 OR inv_mwb_globals.g_status_id IS NOT NULL
1096 OR inv_mwb_globals.g_serial_attr_query IS NOT NULL THEN -- Bug 6429880
1097 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
1098 'msn.current_subinventory_code';
1099 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
1100 'msn.current_locator_id';
1101 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
1102 'msn.lpn_id';
1103 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
1104 'msn.cost_group_id';
1105
1106 inv_mwb_query_manager.add_group_clause('msn.current_subinventory_code', 'ONHAND');
1107 inv_mwb_query_manager.add_group_clause('msn.current_locator_id', 'ONHAND');
1108 inv_mwb_query_manager.add_group_clause('msn.lpn_id', 'ONHAND');
1109 inv_mwb_query_manager.add_group_clause('msn.cost_group_id', 'ONHAND');
1110
1111 IF inv_mwb_globals.g_tree_parent_lpn_id IS NOT NULL THEN
1112 inv_mwb_query_manager.add_where_clause(
1113 'msn.lpn_id = :onh_tree_lpn_id' ,
1114 'ONHAND'
1115 );
1116 ELSE
1117 inv_mwb_query_manager.add_where_clause(
1118 'msn.lpn_id IS NULL' ,
1119 'ONHAND'
1120 );
1121 END IF;
1122
1123 IF inv_mwb_globals.g_tree_loc_id IS NOT NULL THEN
1124 inv_mwb_query_manager.add_where_clause(
1125 'msn.current_locator_id = :onh_tree_loc_id' ,
1126 'ONHAND'
1127 );
1128 ELSE
1129 inv_mwb_query_manager.add_where_clause(
1130 'msn.current_locator_id IS NULL' ,
1131 'ONHAND'
1132 );
1133 END IF;
1134
1135 IF inv_mwb_globals.g_tree_subinventory_code IS NOT NULL THEN
1136 inv_mwb_query_manager.add_where_clause(
1137 'msn.current_subinventory_code = :onh_tree_sub_code' ,
1138 'ONHAND'
1139 );
1140 END IF;
1141 ELSE
1142 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
1143 'moqd.cost_group_id';
1144 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
1145 'moqd.subinventory_code';
1146 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
1147 'moqd.locator_id';
1148 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
1149 'moqd.lpn_id';
1150
1151 inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
1152 inv_mwb_query_manager.add_group_clause('moqd.locator_id', 'ONHAND');
1153 inv_mwb_query_manager.add_group_clause('moqd.lpn_id', 'ONHAND');
1154 inv_mwb_query_manager.add_group_clause('moqd.cost_group_id', 'ONHAND');
1155
1156 -- Onhand Material Status Support
1157 -- For serial controlled items, the status_id will be populated in post_query of IMVMWQMB.
1158 if (inv_cache.set_org_rec(inv_mwb_globals.g_tree_organization_id)) then
1159 l_default_status_id := inv_cache.org_rec.default_status_id;
1160 end if;
1161
1162 if inv_cache.set_item_rec(inv_mwb_globals.g_tree_organization_id, inv_mwb_globals.g_tree_item_id) then
1163 if (inv_cache.item_rec.serial_number_control_code in (2,5)) then
1164 l_serial_controlled := 1; -- Item is serial controlled
1165 end if;
1166
1167 if (inv_cache.item_rec.lot_control_code <> 1) then
1168 l_lot_controlled := 1; -- Item is lot controlled
1169 end if;
1170 end if;
1171
1172 if (l_default_status_id is not null and l_serial_controlled = 0 and l_lot_controlled = 0) then
1173 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.STATUS_ID).column_value :=
1174 'moqd.status_id';
1175 inv_mwb_query_manager.add_group_clause('moqd.status_id', 'ONHAND');
1176 end if;
1177 -- End Onhand Material Status Support
1178
1179 IF inv_mwb_globals.g_tree_parent_lpn_id IS NOT NULL THEN
1180 inv_mwb_query_manager.add_where_clause(
1181 'moqd.lpn_id = :onh_tree_lpn_id' ,
1182 'ONHAND'
1183 );
1184 ELSE
1185 inv_mwb_query_manager.add_where_clause(
1186 'moqd.lpn_id IS NULL' ,
1187 'ONHAND'
1188 );
1189 END IF;
1190
1191 IF inv_mwb_globals.g_tree_loc_id IS NOT NULL THEN
1192 inv_mwb_query_manager.add_where_clause(
1193 'moqd.locator_id = :onh_tree_loc_id' ,
1194 'ONHAND'
1195 );
1196 ELSE
1197 inv_mwb_query_manager.add_where_clause(
1198 'moqd.locator_id IS NULL' ,
1199 'ONHAND'
1200 );
1201 END IF;
1202
1203 IF inv_mwb_globals.g_tree_subinventory_code IS NOT NULL THEN
1204 inv_mwb_query_manager.add_where_clause(
1205 'moqd.subinventory_code = :onh_tree_sub_code' ,
1206 'ONHAND'
1207 );
1208 END IF;
1209 END IF;
1210
1211 IF inv_mwb_globals.g_tree_parent_lpn_id IS NOT NULL THEN
1212 inv_mwb_query_manager.add_bind_variable(
1213 'onh_tree_lpn_id',
1214 inv_mwb_globals.g_tree_parent_lpn_id
1215 );
1216 END IF;
1217
1218 IF inv_mwb_globals.g_tree_loc_id IS NOT NULL THEN
1219 inv_mwb_query_manager.add_bind_variable(
1220 'onh_tree_loc_id',
1221 inv_mwb_globals.g_tree_loc_id
1222 );
1223 END IF;
1224
1225 IF inv_mwb_globals.g_tree_subinventory_code IS NOT NULL THEN
1226 inv_mwb_query_manager.add_bind_variable(
1227 'onh_tree_sub_code',
1228 inv_mwb_globals.g_tree_subinventory_code
1229 );
1230 END IF;
1231
1232 IF NVL(l_rev_control, 1) = 1 AND NVL(l_lot_control, 1) = 1
1233 AND l_serial_control IN ( 2,5 ) THEN
1234
1235 make_common_query_onhand('MSN');
1236 inv_mwb_query_manager.add_where_clause(
1237 'msn.inventory_item_id = :onh_tree_inventory_item_id' ,
1238 'ONHAND'
1239 );
1240 inv_mwb_query_manager.add_where_clause(
1241 'msn.current_organization_id = :onh_tree_organization_id' ,
1242 'ONHAND'
1243 );
1244 inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
1245
1246 ELSE -- only serial controlled
1247 IF inv_mwb_globals.g_serial_from IS NOT NULL OR
1248 inv_mwb_globals.g_serial_to IS NOT NULL
1249 OR inv_mwb_globals.g_status_id IS NOT NULL -- Bug 6060233
1250 OR inv_mwb_globals.g_serial_attr_query IS NOT NULL -- Bug 6429880
1251 OR inv_mwb_globals.g_unit_number IS NOT NULL -- Bug 9486070
1252 THEN
1253 make_common_query_onhand('MSN_QUERY');
1254 IF l_rev_control = 2 THEN
1255 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
1256 'msn.revision';
1257 inv_mwb_query_manager.add_group_clause('msn.revision', 'ONHAND');
1258 END IF;
1259
1260 IF NVL(l_rev_control, 1) = 1 AND l_lot_control = 2 THEN
1261 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
1262 'msn.lot_number';
1263 inv_mwb_query_manager.add_group_clause('msn.lot_number', 'ONHAND');
1264 END IF;
1265 inv_mwb_query_manager.add_where_clause(
1266 'msn.inventory_item_id = :onh_tree_inventory_item_id' ,
1267 'ONHAND'
1268 );
1269 inv_mwb_query_manager.add_where_clause(
1270 'msn.current_organization_id = :onh_tree_organization_id' ,
1271 'ONHAND'
1272 );
1273 inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
1274 ELSE -- serial entered in qf
1275 make_common_query_onhand('MOQD');
1276 inv_mwb_query_manager.add_where_clause(
1277 'moqd.inventory_item_id = :onh_tree_inventory_item_id' ,
1278 'ONHAND'
1279 );
1280 inv_mwb_query_manager.add_where_clause(
1281 'moqd.organization_id = :onh_tree_organization_id' ,
1282 'ONHAND'
1283 );
1284
1285 IF l_rev_control = 2 THEN
1286 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
1287 'moqd.revision';
1288 inv_mwb_query_manager.add_group_clause('moqd.revision', 'ONHAND');
1289 END IF;
1290
1291 IF NVL(l_rev_control, 1) = 1 AND l_lot_control = 2 THEN
1292 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
1293 'moqd.lot_number';
1294 inv_mwb_query_manager.add_group_clause('moqd.lot_number', 'ONHAND');
1295 END IF;
1296 inv_mwb_query_manager.add_qf_where_onhand('ONHAND');
1297 END IF; -- serial in query find
1298 END IF; -- only serial controlled
1299 inv_mwb_query_manager.add_bind_variable(
1300 'onh_tree_organization_id',
1301 inv_mwb_globals.g_tree_organization_id
1302 );
1303 inv_mwb_query_manager.add_bind_variable(
1304 'onh_tree_inventory_item_id',
1305 inv_mwb_globals.g_tree_item_id
1306 );
1307
1308 ELSIF (inv_mwb_globals.g_tree_mat_loc_id = 3) THEN -- INBOUND NODE CHOSEN
1309
1310 make_common_query_inbound('INBOUND');
1311 inv_mwb_query_manager.add_qf_where_inbound('INBOUND');
1312
1313 IF inv_mwb_globals.g_tree_doc_type_id IN (3,4) THEN
1314 IF l_rev_control = 2 THEN
1315 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.REVISION).column_value :=
1316 'ms.item_revision';
1317 inv_mwb_query_manager.add_group_clause('ms.item_revision', 'INBOUND');
1318 ELSE
1319 IF l_lot_control = 2 THEN
1320 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.LOT).column_value :=
1321 'rls.lot_num';
1322 -- for bug 8420783
1323 -- for bug 8414727
1324 -- here only for serial control code 2, because for serial control code 2, the query sql will join the rcv_serials_supply
1325 -- so need to add lot number condition to where_clause.
1326 IF (inv_mwb_globals.g_serial_from IS NOT NULl or inv_mwb_globals.g_serial_to is NOT NULL)
1327 or (l_serial_control = 2) THEN
1328 inv_mwb_query_manager.add_where_clause('rss.lot_num = rls.lot_num', 'INBOUND');
1329 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.INBOUND).column_value :=
1330 'count(1)';
1331 ELSE
1332 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.INBOUND).column_value :=
1333 'sum(rls.quantity)';
1334 END IF ;
1335 -- end of bug 8414727
1336 -- end of bug 8420783
1337 inv_mwb_query_manager.add_group_clause('rls.lot_num', 'INBOUND');
1338 ELSIF l_serial_control = 2 THEN
1339 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.SERIAL).column_value :=
1340 'rss.serial_num';
1341 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.INBOUND).column_value := 1;
1342 inv_mwb_query_manager.add_group_clause('rss.serial_num', 'INBOUND');
1343 END IF;
1344 END IF;
1345 END IF;
1346
1347 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.OWNING_ORG_ID).column_value :=
1348 'ms.intransit_owning_org_id';
1349 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.EXPECTED_RECEIPT_DATE).column_value :=
1350 'ms.expected_delivery_date';
1351 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.FROM_ORG_ID).column_value :=
1352 'ms.from_organization_id';
1353 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.TO_ORG_ID).column_value :=
1354 'ms.to_organization_id'; -- Bug 11716231
1355
1356 inv_mwb_query_manager.add_group_clause('ms.intransit_owning_org_id', 'INBOUND');
1357 inv_mwb_query_manager.add_group_clause('ms.expected_delivery_date', 'INBOUND');
1358 inv_mwb_query_manager.add_group_clause('ms.from_organization_id', 'INBOUND');
1359 inv_mwb_query_manager.add_group_clause('ms.to_organization_id', 'INBOUND'); -- Bug 11716231
1360
1361 CASE inv_mwb_globals.g_tree_doc_type_id
1362 WHEN 1 THEN
1363 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.DOCUMENT_NUMBER).column_value :=
1364 inv_mwb_globals.g_tree_node_value;
1365 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.PO_HEADER_ID).column_value :=
1366 'ms.po_header_id';
1367 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.DOCUMENT_LINE_NUMBER).column_value :=
1368 'ms.po_line_id';
1369 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.REVISION).column_value :=
1370 'ms.item_revision';
1371 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.PO_RELEASE_ID).column_value :=
1372 'ms.po_release_id';
1373 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.RELEASE_NUMBER).column_value :=
1374 'prl.release_num'; -- Bug 11932315
1375 inv_mwb_query_manager.add_from_clause('po_releases_all prl','INBOUND'); -- Bug 11932315
1376 inv_mwb_query_manager.add_where_clause('ms.po_release_id = prl.po_release_id', 'INBOUND'); -- Bug 11932315
1377
1378 inv_mwb_query_manager.add_group_clause('ms.po_release_id', 'INBOUND');
1379 inv_mwb_query_manager.add_group_clause('prl.release_num', 'INBOUND'); -- Bug 11932315
1380 inv_mwb_query_manager.add_group_clause('ms.po_header_id', 'INBOUND');
1381 inv_mwb_query_manager.add_group_clause('ms.po_line_id', 'INBOUND');
1382 inv_mwb_query_manager.add_group_clause('ms.item_revision', 'INBOUND');
1383
1384 inv_mwb_query_manager.add_where_clause('ms.supply_type_code = ''PO''', 'INBOUND');
1385 inv_mwb_query_manager.add_where_clause('ms.to_organization_id = :inb_tree_organization_id', 'INBOUND');
1386 inv_mwb_query_manager.add_where_clause('ms.po_header_id = :po_header_id', 'INBOUND');
1387 inv_mwb_query_manager.add_where_clause('ms.item_id = :item_id', 'INBOUND');
1388 inv_mwb_query_manager.add_bind_variable('inb_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
1389 inv_mwb_query_manager.add_bind_variable('po_header_id', inv_mwb_globals.g_tree_doc_header_id);
1390 inv_mwb_query_manager.add_bind_variable('item_id', inv_mwb_globals.g_tree_item_id);
1391 -- inv_mwb_query_manager.execute_query;
1392
1393 WHEN 2 THEN
1394
1395 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.REQ_HEADER_ID).column_value :=
1396 'ms.req_header_id';
1397 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.DOCUMENT_LINE_NUMBER).column_value :=
1398 'ms.req_line_id';
1399
1400 inv_mwb_query_manager.add_group_clause('ms.req_header_id', 'INBOUND');
1401 inv_mwb_query_manager.add_group_clause('ms.req_line_id', 'INBOUND');
1402 inv_mwb_query_manager.add_where_clause('ms.supply_type_code IN (''REQ'',''SHIPMENT'')', 'INBOUND');
1403 inv_mwb_query_manager.add_where_clause('ms.req_header_id = :req_header_id', 'INBOUND');
1404 inv_mwb_query_manager.add_where_clause('ms.to_organization_id = :inb_tree_organization_id', 'INBOUND');
1405 inv_mwb_query_manager.add_where_clause('ms.item_id = :item_id', 'INBOUND');
1406 inv_mwb_query_manager.add_bind_variable('req_header_id', inv_mwb_globals.g_tree_doc_header_id);
1407 inv_mwb_query_manager.add_bind_variable('item_id', inv_mwb_globals.g_tree_item_id);
1408 inv_mwb_query_manager.add_bind_variable('inb_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
1409
1410 WHEN 3 THEN
1411
1412 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.SHIPMENT_HEADER_ID_ASN).column_value :=
1413 'ms.shipment_header_id';
1414 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.DOCUMENT_LINE_NUMBER).column_value :=
1415 'ms.shipment_line_id';
1416 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.SHIPPED_DATE).column_value :=
1417 'rsh.shipped_date';
1418 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.OWNING_ORG_ID).column_value :=
1419 'ms.intransit_owning_org_id';
1420
1421 inv_mwb_query_manager.add_group_clause('ms.shipment_header_id', 'INBOUND');
1422 inv_mwb_query_manager.add_group_clause('ms.shipment_line_id', 'INBOUND');
1423 inv_mwb_query_manager.add_group_clause('ms.intransit_owning_org_id', 'INBOUND');
1424 inv_mwb_query_manager.add_group_clause('rsh.shipped_date', 'INBOUND');
1425 inv_mwb_query_manager.add_where_clause('ms.supply_type_code = ''SHIPMENT''', 'INBOUND');
1426 inv_mwb_query_manager.add_where_clause('rsh.ASN_TYPE IS NULL', 'INBOUND');
1427 inv_mwb_query_manager.add_where_clause('rsh.shipment_num = :shipment_num', 'INBOUND');
1428 inv_mwb_query_manager.add_where_clause('ms.item_id = :item_id', 'INBOUND');
1429 inv_mwb_query_manager.add_bind_variable('shipment_num', inv_mwb_globals.g_tree_doc_num);
1430 inv_mwb_query_manager.add_bind_variable('item_id',inv_mwb_globals.g_tree_item_id);
1431
1432 IF inv_mwb_globals.g_tree_parent_lpn_id IS NOT NULL THEN
1433 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.LPN_ID).column_value :=
1434 'rsl.asn_lpn_id';
1435 inv_mwb_query_manager.add_where_clause('rsl.asn_lpn_id = :inb_tree_plpn_id', 'INBOUND');
1436 inv_mwb_query_manager.add_bind_variable('inb_tree_plpn_id', inv_mwb_globals.g_tree_parent_lpn_id);
1437 inv_mwb_query_manager.add_group_clause('rsl.asn_lpn_id', 'INBOUND');
1438 ELSE
1439 inv_mwb_query_manager.add_where_clause('rsl.asn_lpn_id IS NULL', 'INBOUND');
1440 END IF;
1441
1442 WHEN 4 THEN
1443
1444 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.SHIPMENT_HEADER_ID_ASN).column_value :=
1445 'ms.shipment_header_id';
1446 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.DOCUMENT_LINE_NUMBER).column_value :=
1447 'ms.shipment_line_id';
1448 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.VENDOR_ID).column_value :=
1449 'rsh.vendor_id';
1450 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.VENDOR_SITE_ID).column_value :=
1451 'rsh.vendor_site_id';
1452 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.SHIPPED_DATE).column_value :=
1453 'rsh.shipped_date';
1454
1455 inv_mwb_query_manager.add_group_clause('rsh.vendor_id', 'INBOUND');
1456 inv_mwb_query_manager.add_group_clause('rsh.vendor_site_id', 'INBOUND');
1457 inv_mwb_query_manager.add_group_clause('ms.shipment_header_id', 'INBOUND');
1458 inv_mwb_query_manager.add_group_clause('ms.shipment_line_id', 'INBOUND');
1459 inv_mwb_query_manager.add_group_clause('rsh.shipped_date', 'INBOUND');
1460
1461 inv_mwb_query_manager.add_where_clause('ms.supply_type_code = ''SHIPMENT''', 'INBOUND');
1462 inv_mwb_query_manager.add_where_clause('rsh.ASN_TYPE IS NOT NULL', 'INBOUND');
1463 inv_mwb_query_manager.add_where_clause('rsh.RECEIPT_SOURCE_CODE = ''VENDOR''', 'INBOUND');
1464 inv_mwb_query_manager.add_where_clause('rsh.shipment_num = :shipment_num', 'INBOUND');
1465 inv_mwb_query_manager.add_where_clause('ms.item_id = :item_id', 'INBOUND');
1466 inv_mwb_query_manager.add_bind_variable('shipment_num', inv_mwb_globals.g_tree_doc_num);
1467 inv_mwb_query_manager.add_bind_variable('item_id',inv_mwb_globals.g_tree_item_id);
1468
1469 IF inv_mwb_globals.g_tree_parent_lpn_id IS NOT NULL THEN
1470 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.LPN_ID).column_value :=
1471 'rsl.asn_lpn_id';
1472 inv_mwb_query_manager.add_where_clause('rsl.asn_lpn_id = :inb_tree_plpn_id', 'INBOUND');
1473 inv_mwb_query_manager.add_bind_variable('inb_tree_plpn_id', inv_mwb_globals.g_tree_parent_lpn_id);
1474 inv_mwb_query_manager.add_group_clause('rsl.asn_lpn_id', 'INBOUND');
1475 ELSE
1476 inv_mwb_query_manager.add_where_clause('rsl.asn_lpn_id IS NULL', 'INBOUND');
1477 END IF;
1478
1479 END CASE;
1480
1481
1482 ELSIF (inv_mwb_globals.g_tree_mat_loc_id = 2) THEN -- Receiving node chosen
1483
1484 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
1485 'rs.to_subinventory';
1486 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
1487 'rs.to_locator_id';
1488 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.LPN_ID).column_value :=
1489 'rs.lpn_id';
1490
1491 inv_mwb_query_manager.add_group_clause('rs.to_subinventory', 'RECEIVING');
1492 inv_mwb_query_manager.add_group_clause('rs.to_locator_id', 'RECEIVING');
1493 inv_mwb_query_manager.add_group_clause('rs.lpn_id', 'RECEIVING');
1494
1495 IF inv_mwb_globals.g_tree_parent_lpn_id IS NOT NULL THEN
1496 inv_mwb_query_manager.add_where_clause('rs.lpn_id = :rcv_tree_plpn_id' ,'RECEIVING');
1497 inv_mwb_query_manager.add_bind_variable('rcv_tree_plpn_id', inv_mwb_globals.g_tree_parent_lpn_id);
1498 ELSE
1499 inv_mwb_query_manager.add_where_clause('rs.lpn_id IS NULL', 'RECEIVING');
1500 END IF;
1501
1502 IF inv_mwb_globals.g_tree_loc_id IS NOT NULL THEN
1503 inv_mwb_query_manager.add_where_clause('rs.to_locator_id = :rcv_tree_loc_id' ,'RECEIVING');
1504 inv_mwb_query_manager.add_bind_variable('rcv_tree_loc_id', inv_mwb_globals.g_tree_loc_id);
1505 ELSE
1506 inv_mwb_query_manager.add_where_clause('rs.to_locator_id IS NULL' ,'RECEIVING');
1507 END IF;
1508
1509 IF inv_mwb_globals.g_tree_subinventory_code IS NOT NULL THEN
1510 inv_mwb_query_manager.add_where_clause('rs.to_subinventory = :rcv_tree_subinventory_code', 'RECEIVING');
1511 inv_mwb_query_manager.add_bind_variable('rcv_tree_subinventory_code', inv_mwb_globals.g_tree_subinventory_code);
1512 ELSE
1513 inv_mwb_query_manager.add_where_clause('rs.to_subinventory IS NULL', 'RECEIVING');
1514 END IF;
1515
1516 inv_mwb_query_manager.add_where_clause('rs.to_organization_id = :rcv_tree_organization_id', 'RECEIVING');
1517 inv_mwb_query_manager.add_where_clause('rs.item_id = :rcv_tree_item_id', 'RECEIVING');
1518
1519 inv_mwb_query_manager.add_bind_variable('rcv_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
1520 inv_mwb_query_manager.add_bind_variable('rcv_tree_item_id', inv_mwb_globals.g_tree_item_id);
1521
1522
1523 IF (inv_mwb_globals.g_serial_from IS NOT NULL OR inv_mwb_globals.g_serial_to IS NOT NULL)
1524 OR l_serial_control IN ( 2,5 ) THEN
1525 inv_mwb_query_manager.add_qf_where_receiving('MSN');
1526 make_common_query_receiving('MSN');
1527 IF l_rev_control = 2 THEN
1528 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.REVISION).column_value :=
1529 'rs.item_revision';
1530 inv_mwb_query_manager.add_group_clause('rs.item_revision', 'RECEIVING');
1531 ELSIF l_lot_control = 2 THEN
1532 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.LOT).column_value :=
1533 'rss.lot_num';
1534 inv_mwb_query_manager.add_group_clause('rss.lot_num', 'RECEIVING');
1535 ELSE
1536 inv_mwb_globals.print_msg(g_pkg_name, l_procedure_name, 'Adding Serial');
1537 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SERIAL).column_value :=
1538 'rss.serial_num';
1539 inv_mwb_query_manager.add_group_clause('rss.serial_num', 'RECEIVING');
1540 END IF;
1541 ELSIF l_serial_control NOT IN (2,5) THEN
1542 inv_mwb_query_manager.add_qf_where_receiving('RECEIVING');
1543 make_common_query_receiving('RECEIVING');
1544 IF l_rev_control = 2 THEN
1545 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.REVISION).column_value :=
1546 'rs.item_revision';
1547 inv_mwb_query_manager.add_group_clause('rs.item_revision', 'RECEIVING');
1548
1549 -- bug 13812686 -- At item level, the quantities are getting doubled. Below fix for prim qty
1550 -- are already done through bug 9865190 in inv_mwb_lpn_tree file. Adding the same here along with
1551 --sec qty assignments
1552 IF l_lot_control = 2 THEN
1553 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.LOT).column_value :=
1554 'rls.lot_num';
1555 inv_mwb_query_manager.add_group_clause('rls.lot_num', 'RECEIVING');
1556 IF inv_mwb_globals.g_wms_enabled_flag = 1 THEN --Adding this flag for Bug 14595973
1557 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.RECEIVING).column_value :=
1558 'SUM(rls.primary_quantity)';
1559 inv_mwb_query_manager.add_where_clause('rls.supply_type_code = ''RECEIVING''', 'RECEIVING');
1560 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.UNPACKED).column_value :=
1561 'SUM(DECODE (rs.lpn_id, null, rls.primary_quantity, 0))';
1562 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.PACKED).column_value :=
1563 'SUM(DECODE (rs.lpn_id, null, 0, rls.primary_quantity))';
1564
1565 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SECONDARY_RECEIVING).column_value :=
1566 'SUM(rls.secondary_quantity)';
1567 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SECONDARY_UNPACKED).column_value :=
1568 'SUM(DECODE (rs.lpn_id, null, rls.secondary_quantity, 0))';
1569 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SECONDARY_PACKED).column_value :=
1570 'SUM(DECODE (rs.lpn_id, null, 0, rls.secondary_quantity))';
1571 END IF;
1572 END IF;
1573 -- bug 13812686
1574
1575 ELSIF l_lot_control = 2 THEN
1576 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.LOT).column_value :=
1577 'rls.lot_num';
1578 inv_mwb_query_manager.add_group_clause('rls.lot_num', 'RECEIVING');
1579 -- Bug 9865190 Start
1580 IF inv_mwb_globals.g_wms_enabled_flag = 1 THEN
1581 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.RECEIVING).column_value :=
1582 'SUM(rls.primary_quantity)';
1583 inv_mwb_query_manager.add_where_clause('rls.supply_type_code = ''RECEIVING''', 'RECEIVING');
1584 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.UNPACKED).column_value :=
1585 'SUM(DECODE (rs.lpn_id, null, rls.primary_quantity, 0))';
1586 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.PACKED).column_value :=
1587 'SUM(DECODE (rs.lpn_id, null, 0, rls.primary_quantity))';
1588 --adding for bug 13812686 START
1589 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SECONDARY_RECEIVING).column_value :=
1590 'SUM(rls.secondary_quantity)';
1591 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SECONDARY_UNPACKED).column_value :=
1592 'SUM(DECODE (rs.lpn_id, null, rls.secondary_quantity, 0))';
1593 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SECONDARY_PACKED).column_value :=
1594 'SUM(DECODE (rs.lpn_id, null, 0, rls.secondary_quantity))';
1595 --adding for bug 13812686 END
1596 END IF;
1597 -- Bug 9865190 End
1598 END IF;
1599 END IF;
1600 END IF;
1601 inv_mwb_query_manager.execute_query;
1602 END IF; -- node selected
1603 EXCEPTION
1604 WHEN NO_DATA_FOUND THEN
1605 null;
1606 END item_node_event;
1607
1608
1609 PROCEDURE rev_node_event(
1610 x_node_value IN OUT NOCOPY NUMBER
1611 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
1612 , x_tbl_index IN OUT NOCOPY NUMBER
1613 )
1614 IS
1615 loc_control NUMBER;
1616 l_lot_control NUMBER;
1617 l_serial_control NUMBER;
1618 containerized NUMBER;
1619 select_serial NUMBER := 0;
1620 select_grade NUMBER := 0; -- NSRIVAST, INVCONV
1621 str_query VARCHAR2(4000);
1622 l_procedure_name VARCHAR2(30);
1623 l_po_header_id NUMBER;
1624 l_req_header_id NUMBER;
1625 l_lot_controlled NUMBER := 0; -- Onhand Material Status Support
1626 l_serial_controlled NUMBER := 0; -- Onhand Material Status Support
1627 l_default_status_id NUMBER; -- Onhand Material Status Support
1628
1629 BEGIN
1630
1631 /*Bug3457132-Introduced a new parameter CHECK which is set to 'Y' when
1632 there is an item-node-event and inv_mwb_globals.g_tree_rev-node-event*/
1633
1634 -- copy('Y','PARAMETER.CHECK');
1635 l_procedure_name := 'REV_NODE_EVENT';
1636 inv_mwb_globals.print_msg(g_pkg_name, l_procedure_name, 'Entered');
1637 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_EXPANDED' THEN
1638
1639 IF inv_mwb_globals.g_tree_loc_id IS NULL THEN
1640 loc_control := 1;
1641 ELSE
1642 loc_control := 2;
1643 END IF;
1644
1645 IF inv_mwb_globals.g_tree_parent_lpn_id IS NULL THEN
1646 containerized := 1;
1647 ELSE
1648 containerized := 2;
1649 END IF;
1650
1651 IF containerized =1 THEN
1652 inv_mwb_globals.g_prepacked := 1;
1653 ELSE
1654 IF inv_mwb_globals.g_sub_type = 2 THEN
1655 inv_mwb_globals.g_prepacked := 1;
1656 ELSE
1657 inv_mwb_globals.g_prepacked := NULL;
1658 END IF;
1659 END IF;
1660
1661 inv_mwb_globals.g_locator_controlled := loc_control;
1662 inv_mwb_globals.g_containerized := containerized;
1663
1664 inv_mwb_tree1.add_lots(
1665 x_node_value
1666 , x_node_tbl
1667 , x_tbl_index
1668 );
1669
1670 IF x_tbl_index = 1 THEN
1671
1672 inv_mwb_globals.g_locator_controlled := loc_control;
1673 inv_mwb_globals.g_containerized := containerized;
1674 inv_mwb_globals.g_lot_controlled := 1;
1675
1676 inv_mwb_tree1.add_serials(
1677 x_node_value
1678 , x_node_tbl
1679 , x_tbl_index
1680 );
1681 END IF;
1682
1683 ELSIF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
1684 SELECT lot_control_code
1685 , serial_number_control_code
1686 INTO l_lot_control
1687 , l_serial_control
1688 FROM mtl_system_items
1689 WHERE inventory_item_id = inv_mwb_globals.g_tree_item_id
1690 AND organization_id = inv_mwb_globals.g_tree_organization_id;
1691
1692 IF inv_mwb_globals.g_tree_loc_id IS NULL THEN
1693 loc_control := 1;
1694 ELSE
1695 loc_control := 2;
1696 END IF;
1697
1698 IF (inv_mwb_globals.g_tree_mat_loc_id = 1) THEN -- Onhand node chosen
1699 --Serial Controlled
1700 IF (inv_mwb_globals.g_serial_from IS NOT NULL OR inv_mwb_globals.g_serial_to IS NOT NULL)
1701 OR l_serial_control IN ( 2,5 )
1702 OR inv_mwb_globals.g_status_id IS NOT NULL
1703 OR inv_mwb_globals.g_serial_attr_query IS NOT NULL -- Bug 6429880
1704 OR inv_mwb_globals.g_unit_number IS NOT NULL -- Bug 9486070
1705 THEN
1706 inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
1707 make_common_query_onhand('MSN_QUERY');
1708
1709 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
1710 'msn.current_subinventory_code';
1711 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
1712 'msn.current_locator_id';
1713 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
1714 'msn.lpn_id';
1715 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.REVISION).column_value :=
1716 'msn.revision';
1717 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
1718 'msn.cost_group_id';
1719
1720 inv_mwb_query_manager.add_group_clause('msn.current_subinventory_code', 'ONHAND');
1721 inv_mwb_query_manager.add_group_clause('msn.current_locator_id', 'ONHAND');
1722 inv_mwb_query_manager.add_group_clause('msn.lpn_id', 'ONHAND');
1723 inv_mwb_query_manager.add_group_clause('msn.revision', 'ONHAND');
1724 inv_mwb_query_manager.add_group_clause('msn.cost_group_id', 'ONHAND');
1725
1726 IF l_lot_control = 2 THEN
1727 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
1728 'msn.lot_number';
1729 inv_mwb_query_manager.add_group_clause('msn.lot_number', 'ONHAND');
1730 ELSE
1731 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SERIAL).column_value :=
1732 'msn.serial_number';
1733 inv_mwb_query_manager.add_group_clause('msn.serial_number', 'ONHAND');
1734 END IF;
1735
1736 inv_mwb_query_manager.add_where_clause('msn.current_organization_id = :onh_tree_organization_id' ,'ONHAND');
1737 inv_mwb_query_manager.add_where_clause('msn.current_subinventory_code = :onh_tree_subinventory_code', 'ONHAND');
1738 inv_mwb_query_manager.add_where_clause('msn.inventory_item_id = :onh_tree_item_id', 'ONHAND');
1739
1740 inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
1741 inv_mwb_query_manager.add_bind_variable('onh_tree_subinventory_code', inv_mwb_globals.g_tree_subinventory_code);
1742 inv_mwb_query_manager.add_bind_variable('onh_tree_item_id', inv_mwb_globals.g_tree_item_id);
1743
1744 IF inv_mwb_globals.g_tree_parent_lpn_id IS NOT NULL THEN
1745 inv_mwb_query_manager.add_where_clause('msn.lpn_id = :onh_tree_plpn_id', 'ONHAND');
1746 inv_mwb_query_manager.add_bind_variable('onh_tree_plpn_id', inv_mwb_globals.g_tree_parent_lpn_id);
1747 ELSE
1748 inv_mwb_query_manager.add_where_clause('msn.lpn_id IS NULL', 'ONHAND');
1749 END IF;
1750
1751 IF inv_mwb_globals.g_tree_loc_id IS NOT NULL THEN
1752 inv_mwb_query_manager.add_where_clause('msn.current_locator_id = :onh_tree_loc_id', 'ONHAND');
1753 inv_mwb_query_manager.add_bind_variable('onh_tree_loc_id', inv_mwb_globals.g_tree_loc_id);
1754 ELSE
1755 inv_mwb_query_manager.add_where_clause('msn.current_locator_id IS NULL' ,'ONHAND');
1756 END IF;
1757
1758 inv_mwb_query_manager.add_where_clause('msn.revision = :onh_tree_revision' ,'ONHAND');
1759 inv_mwb_query_manager.add_bind_variable('onh_tree_revision', inv_mwb_globals.g_tree_rev);
1760
1761 ELSIF l_serial_control NOT IN (2,5) THEN
1762
1763 make_common_query_onhand('MOQD');
1764 inv_mwb_query_manager.add_qf_where_onhand('ONHAND');
1765
1766 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
1767 'moqd.subinventory_code';
1768 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
1769 'moqd.locator_id';
1770 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
1771 'moqd.lot_number';
1772 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
1773 'moqd.cost_group_id';
1774 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
1775 'moqd.lpn_id';
1776 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.REVISION).column_value :=
1777 'moqd.revision';
1778
1779 inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
1780 inv_mwb_query_manager.add_group_clause('moqd.locator_id', 'ONHAND');
1781 inv_mwb_query_manager.add_group_clause('moqd.lot_number', 'ONHAND');
1782 inv_mwb_query_manager.add_group_clause('moqd.cost_group_id', 'ONHAND');
1783 inv_mwb_query_manager.add_group_clause('moqd.lpn_id', 'ONHAND');
1784 inv_mwb_query_manager.add_group_clause('moqd.revision', 'ONHAND');
1785
1786 -- Onhand Material Status Support
1787 -- For serial controlled items, the status_id will be populated in post_query of IMVMWQMB.
1788 if (inv_cache.set_org_rec(inv_mwb_globals.g_tree_organization_id)) then
1789 l_default_status_id := inv_cache.org_rec.default_status_id;
1790 end if;
1791
1792 if inv_cache.set_item_rec(inv_mwb_globals.g_tree_organization_id, inv_mwb_globals.g_tree_item_id) then
1793 if (inv_cache.item_rec.serial_number_control_code in (2,5)) then
1794 l_serial_controlled := 1; -- Item is serial controlled
1795 end if;
1796
1797 if (inv_cache.item_rec.lot_control_code <> 1) then
1798 l_lot_controlled := 1; -- Item is lot controlled
1799 end if;
1800 end if;
1801
1802 if (l_default_status_id is not null and l_serial_controlled = 0 and l_lot_controlled = 0) then
1803 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.STATUS_ID).column_value :=
1804 'moqd.status_id';
1805 inv_mwb_query_manager.add_group_clause('moqd.status_id', 'ONHAND');
1806 end if;
1807 -- End Onhand Material Status Support
1808
1809 IF inv_mwb_globals.g_tree_parent_lpn_id IS NOT NULL THEN
1810 inv_mwb_query_manager.add_where_clause('moqd.lpn_id = :onh_tree_plpn_id', 'ONHAND');
1811 inv_mwb_query_manager.add_bind_variable('onh_tree_plpn_id', inv_mwb_globals.g_tree_parent_lpn_id);
1812 ELSE
1813 inv_mwb_query_manager.add_where_clause('moqd.lpn_id IS NULL', 'ONHAND');
1814 END IF;
1815
1816 IF inv_mwb_globals.g_tree_loc_id IS NOT NULL THEN
1817 inv_mwb_query_manager.add_where_clause('moqd.locator_id = :onh_tree_loc_id', 'ONHAND');
1818 inv_mwb_query_manager.add_bind_variable('onh_tree_loc_id', inv_mwb_globals.g_tree_loc_id);
1819 ELSE
1820 inv_mwb_query_manager.add_where_clause('moqd.locator_id IS NULL', 'ONHAND');
1821 END IF;
1822
1823 inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id' ,'ONHAND');
1824 inv_mwb_query_manager.add_where_clause('moqd.subinventory_code = :onh_tree_subinventory_code', 'ONHAND');
1825 inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = :onh_tree_item_id' ,'ONHAND');
1826 inv_mwb_query_manager.add_where_clause('moqd.revision = :onh_tree_revision' ,'ONHAND');
1827
1828 inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
1829 inv_mwb_query_manager.add_bind_variable('onh_tree_subinventory_code', inv_mwb_globals.g_tree_subinventory_code);
1830 inv_mwb_query_manager.add_bind_variable('onh_tree_item_id', inv_mwb_globals.g_tree_item_id);
1831 inv_mwb_query_manager.add_bind_variable('onh_tree_revision', inv_mwb_globals.g_tree_rev);
1832
1833 END IF;
1834 END IF;
1835
1836
1837
1838 IF inv_mwb_globals.g_tree_mat_loc_id = 3 THEN
1839
1840 CASE inv_mwb_globals.g_tree_doc_type_id
1841 WHEN 1 THEN
1842 make_common_query_inbound('INBOUND');
1843 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.DOCUMENT_NUMBER).column_value :=
1844 inv_mwb_globals.g_tree_node_value;
1845 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.PO_HEADER_ID).column_value :=
1846 'ms.po_header_id';
1847 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.DOCUMENT_LINE_NUMBER).column_value :=
1848 'ms.po_line_id';
1849 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.REVISION).column_value :=
1850 'ms.item_revision';
1851 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.PO_HEADER_ID).column_value :=
1852 'ms.po_release_id';
1853 inv_mwb_query_manager.add_group_clause('ms.po_release_id', 'INBOUND');
1854 inv_mwb_query_manager.add_group_clause('ms.po_header_id', 'INBOUND');
1855 inv_mwb_query_manager.add_group_clause('ms.po_line_id', 'INBOUND');
1856 inv_mwb_query_manager.add_group_clause('ms.item_revision', 'INBOUND');
1857
1858 inv_mwb_query_manager.add_where_clause('ms.supply_type_code = ''PO''', 'INBOUND');
1859 inv_mwb_query_manager.add_where_clause('ms.to_organization_id = :inb_tree_organization_id', 'INBOUND');
1860 inv_mwb_query_manager.add_where_clause('ms.po_header_id = :po_header_id', 'INBOUND');
1861 inv_mwb_query_manager.add_where_clause('ms.item_id = :item_id', 'INBOUND');
1862 inv_mwb_query_manager.add_where_clause('ms.item_revision = :item_revision', 'INBOUND');
1863 inv_mwb_query_manager.add_bind_variable('inb_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
1864 inv_mwb_query_manager.add_bind_variable('po_header_id', inv_mwb_globals.g_tree_doc_header_id);
1865 inv_mwb_query_manager.add_bind_variable('item_id', inv_mwb_globals.g_tree_item_id);
1866 inv_mwb_query_manager.add_bind_variable('item_revision', inv_mwb_globals.g_tree_rev);
1867 inv_mwb_query_manager.execute_query;
1868 return;
1869 WHEN 2 THEN
1870 make_common_query_inbound('INBOUND');
1871 inv_mwb_query_manager.add_qf_where_inbound('INBOUND');
1872 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.REQ_HEADER_ID).column_value :=
1873 'ms.req_header_id';
1874 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.DOCUMENT_LINE_NUMBER).column_value :=
1875 'ms.req_line_id';
1876 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.LOT).column_value :=
1877 'rls.lot_num';
1878 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.LOT_EXPIRY_DATE).column_value :=
1879 'rls.expiration_date';
1880
1881 inv_mwb_query_manager.add_group_clause('ms.req_header_id', 'INBOUND');
1882 inv_mwb_query_manager.add_group_clause('ms.req_line_id', 'INBOUND');
1883 inv_mwb_query_manager.add_group_clause('rls.lot_num', 'INBOUND');
1884 inv_mwb_query_manager.add_group_clause('rls.expiration_date', 'INBOUND');
1885 inv_mwb_query_manager.add_where_clause('ms.supply_type_code IN (''REQ'',''SHIPMENT'')', 'INBOUND');
1886 inv_mwb_query_manager.add_where_clause('ms.req_header_id = :req_header_id', 'INBOUND');
1887 inv_mwb_query_manager.add_where_clause('ms.item_id = :item_id', 'INBOUND');
1888 inv_mwb_query_manager.add_bind_variable('req_header_id', inv_mwb_globals.g_tree_doc_header_id);
1889 inv_mwb_query_manager.add_bind_variable('item_id', inv_mwb_globals.g_tree_item_id);
1890
1891 WHEN 3 THEN
1892
1893 make_common_query_inbound('INBOUND');
1894 inv_mwb_query_manager.add_qf_where_inbound('INBOUND');
1895
1896 IF l_lot_control = 2 THEN
1897 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.LOT).column_value :=
1898 'rls.lot_num';
1899 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.LOT_EXPIRY_DATE).column_value :=
1900 'rls.expiration_date';
1901 inv_mwb_query_manager.add_group_clause('rls.lot_num', 'INBOUND');
1902 inv_mwb_query_manager.add_group_clause('rls.expiration_date', 'INBOUND');
1903 ELSIF l_serial_control = 2 THEN
1904 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.SERIAL).column_value :=
1905 'rss.serial_num';
1906 inv_mwb_query_manager.add_group_clause('rss.serial_num', 'INBOUND');
1907 END IF;
1908
1909 IF inv_mwb_globals.g_tree_parent_lpn_id IS NOT NULL THEN
1910 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.LPN_ID).column_value :=
1911 'rsl.asn_lpn_id';
1912 inv_mwb_query_manager.add_where_clause('rsl.asn_lpn_id = :asn_lpn_id', 'INBOUND');
1913 inv_mwb_query_manager.add_bind_variable('asn_lpn_id',inv_mwb_globals.g_tree_parent_lpn_id);
1914 inv_mwb_query_manager.add_group_clause('rsl.asn_lpn_id', 'INBOUND');
1915 END IF;
1916
1917 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.SHIPMENT_HEADER_ID_ASN).column_value :=
1918 'ms.shipment_header_id';
1919 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.DOCUMENT_LINE_NUMBER).column_value :=
1920 'ms.shipment_line_id';
1921 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.SHIPPED_DATE).column_value :=
1922 'rsh.shipped_date';
1923 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.OWNING_ORG_ID).column_value :=
1924 'ms.intransit_owning_org_id';
1925 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.REVISION).column_value :=
1926 'ms.item_revision';
1927
1928 inv_mwb_query_manager.add_group_clause('ms.shipment_header_id', 'INBOUND');
1929 inv_mwb_query_manager.add_group_clause('ms.shipment_line_id', 'INBOUND');
1930 inv_mwb_query_manager.add_group_clause('ms.intransit_owning_org_id', 'INBOUND');
1931 inv_mwb_query_manager.add_group_clause('rsh.shipped_date', 'INBOUND');
1932 inv_mwb_query_manager.add_group_clause('ms.item_revision', 'INBOUND');
1933 inv_mwb_query_manager.add_where_clause('rsh.shipment_header_id = :shipment_header_id', 'INBOUND');
1934 inv_mwb_query_manager.add_where_clause('ms.item_id = :item_id', 'INBOUND');
1935 inv_mwb_query_manager.add_bind_variable('shipment_header_id', inv_mwb_globals.g_tree_doc_header_id);
1936 inv_mwb_query_manager.add_bind_variable('item_id',inv_mwb_globals.g_tree_item_id);
1937
1938 WHEN 4 THEN
1939 make_common_query_inbound('INBOUND');
1940 inv_mwb_query_manager.add_qf_where_inbound('INBOUND');
1941
1942 IF l_lot_control = 2 THEN
1943 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.LOT).column_value :=
1944 'rls.lot_num';
1945 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.LOT_EXPIRY_DATE).column_value :=
1946 'rls.expiration_date';
1947 inv_mwb_query_manager.add_group_clause('rls.lot_num', 'INBOUND');
1948 inv_mwb_query_manager.add_group_clause('rls.expiration_date', 'INBOUND');
1949 ELSIF l_serial_control = 2 THEN
1950 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.SERIAL).column_value :=
1951 'rss.serial_num';
1952 inv_mwb_query_manager.add_group_clause('rss.serial_num', 'INBOUND');
1953 END IF;
1954
1955 IF inv_mwb_globals.g_tree_parent_lpn_id IS NOT NULL THEN
1956 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.LPN_ID).column_value :=
1957 'rsl.asn_lpn_id';
1958 inv_mwb_query_manager.add_where_clause('rsl.asn_lpn_id = :asn_lpn_id', 'INBOUND');
1959 inv_mwb_query_manager.add_bind_variable('asn_lpn_id',inv_mwb_globals.g_tree_parent_lpn_id);
1960 inv_mwb_query_manager.add_group_clause('rsl.asn_lpn_id', 'INBOUND');
1961 END IF;
1962
1963 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.SHIPMENT_HEADER_ID_ASN).column_value :=
1964 'ms.shipment_header_id';
1965 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.DOCUMENT_LINE_NUMBER).column_value :=
1966 'ms.shipment_line_id';
1967 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.SHIPPED_DATE).column_value :=
1968 'rsh.shipped_date';
1969 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.OWNING_ORG_ID).column_value :=
1970 'ms.intransit_owning_org_id';
1971 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.REVISION).column_value :=
1972 'ms.item_revision';
1973
1974 inv_mwb_query_manager.add_group_clause('ms.shipment_header_id', 'INBOUND');
1975 inv_mwb_query_manager.add_group_clause('ms.shipment_line_id', 'INBOUND');
1976 inv_mwb_query_manager.add_group_clause('ms.intransit_owning_org_id', 'INBOUND');
1977 inv_mwb_query_manager.add_group_clause('rsh.shipped_date', 'INBOUND');
1978 inv_mwb_query_manager.add_group_clause('ms.item_revision', 'INBOUND');
1979
1980 inv_mwb_query_manager.add_where_clause('rsh.shipment_header_id = :shipment_header_id', 'INBOUND');
1981 inv_mwb_query_manager.add_where_clause('ms.item_id = :item_id', 'INBOUND');
1982 inv_mwb_query_manager.add_bind_variable('shipment_header_id', inv_mwb_globals.g_tree_doc_header_id);
1983 inv_mwb_query_manager.add_bind_variable('item_id',inv_mwb_globals.g_tree_item_id);
1984 END CASE;
1985 END IF;
1986
1987 IF (inv_mwb_globals.g_tree_mat_loc_id = 2) THEN -- Receiving node chosen
1988 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
1989 'rs.to_subinventory';
1990 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
1991 'rs.to_locator_id';
1992 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.LPN_ID).column_value :=
1993 'rs.lpn_id';
1994 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.REVISION).column_value :=
1995 'rs.item_revision';
1996
1997 inv_mwb_query_manager.add_group_clause('rs.to_subinventory', 'RECEIVING');
1998 inv_mwb_query_manager.add_group_clause('rs.to_locator_id', 'RECEIVING');
1999 inv_mwb_query_manager.add_group_clause('rs.lpn_id', 'RECEIVING');
2000 inv_mwb_query_manager.add_group_clause('rs.item_revision', 'RECEIVING');
2001
2002 IF inv_mwb_globals.g_tree_parent_lpn_id IS NOT NULL THEN
2003 inv_mwb_query_manager.add_where_clause('rs.lpn_id = :rcv_tree_plpn_id' ,'RECEIVING');
2004 inv_mwb_query_manager.add_bind_variable('rcv_tree_plpn_id', inv_mwb_globals.g_tree_parent_lpn_id);
2005 END IF;
2006 IF inv_mwb_globals.g_tree_loc_id IS NOT NULL THEN
2007 inv_mwb_query_manager.add_where_clause('rs.to_locator_id = :rcv_tree_loc_id' ,'RECEIVING');
2008 inv_mwb_query_manager.add_bind_variable('rcv_tree_loc_id', inv_mwb_globals.g_tree_loc_id);
2009 END IF;
2010 IF inv_mwb_globals.g_tree_subinventory_code IS NOT NULL THEN
2011 inv_mwb_query_manager.add_where_clause('rs.to_subinventory = :rcv_tree_subinventory_code', 'RECEIVING');
2012 inv_mwb_query_manager.add_bind_variable('rcv_tree_subinventory_code', inv_mwb_globals.g_tree_subinventory_code);
2013 END IF;
2014
2015 inv_mwb_query_manager.add_where_clause('rs.to_organization_id = :rcv_tree_organization_id', 'RECEIVING');
2016 inv_mwb_query_manager.add_where_clause('rs.item_id = :rcv_tree_item_id', 'RECEIVING');
2017 inv_mwb_query_manager.add_where_clause('rs.item_revision = :rcv_tree_rev', 'RECEIVING');
2018
2019 inv_mwb_query_manager.add_bind_variable('rcv_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
2020 inv_mwb_query_manager.add_bind_variable('rcv_tree_item_id', inv_mwb_globals.g_tree_item_id);
2021 inv_mwb_query_manager.add_bind_variable('rcv_tree_rev', inv_mwb_globals.g_tree_rev);
2022
2023 --Serial Controlled
2024 IF (inv_mwb_globals.g_serial_from IS NOT NULL OR inv_mwb_globals.g_serial_to IS NOT NULL)
2025 OR l_serial_control IN ( 2,5 ) THEN
2026 inv_mwb_query_manager.add_qf_where_receiving('MSN');
2027 make_common_query_receiving('MSN');
2028 IF l_lot_control = 2 THEN
2029 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.LOT).column_value :=
2030 'rss.lot_num';
2031 inv_mwb_query_manager.add_group_clause('rss.lot_num', 'RECEIVING');
2032 ELSE
2033 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SERIAL).column_value :=
2034 'rss.serial_num';
2035 inv_mwb_query_manager.add_group_clause('rss.serial_num', 'RECEIVING');
2036 END IF;
2037 ELSIF l_serial_control NOT IN (2,5) THEN
2038 inv_mwb_query_manager.add_qf_where_receiving('RECEIVING');
2039 make_common_query_receiving('RECEIVING');
2040 IF l_lot_control = 2 THEN
2041 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.LOT).column_value :=
2042 'rls.lot_num';
2043 inv_mwb_query_manager.add_group_clause('rls.lot_num', 'RECEIVING');
2044 -- Bug 9865190 Start
2045 IF inv_mwb_globals.g_wms_enabled_flag = 1 THEN
2046 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.RECEIVING).column_value :=
2047 'SUM(rls.primary_quantity)';
2048 inv_mwb_query_manager.add_where_clause('rls.supply_type_code = ''RECEIVING''', 'RECEIVING');
2049 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.UNPACKED).column_value :=
2050 'SUM(DECODE (rs.lpn_id, null, rls.primary_quantity, 0))';
2051 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.PACKED).column_value :=
2052 'SUM(DECODE (rs.lpn_id, null, 0, rls.primary_quantity))';
2053
2054 --SR adding for Bug 13812686 START
2055 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SECONDARY_RECEIVING).column_value :=
2056 'SUM(rls.secondary_quantity)';
2057 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SECONDARY_UNPACKED).column_value :=
2058 'SUM(DECODE (rs.lpn_id, null, rls.secondary_quantity, 0))';
2059 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SECONDARY_PACKED).column_value :=
2060 'SUM(DECODE (rs.lpn_id, null, 0, rls.secondary_quantity))';
2061 --SR adding for Bug 13812686 END
2062 END IF;
2063 -- Bug 9865190 End
2064 END IF;
2065 END IF;
2066 END IF;
2067 END IF;
2068 inv_mwb_query_manager.execute_query;
2069 END rev_node_event;
2070
2071 PROCEDURE lot_node_event(
2072 x_node_value IN OUT NOCOPY NUMBER
2073 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
2074 , x_tbl_index IN OUT NOCOPY NUMBER
2075 )
2076 IS
2077 loc_control NUMBER;
2078 rev_control NUMBER;
2079 serial_control NUMBER;
2080 containerized NUMBER;
2081 select_serial NUMBER := 0;
2082 str_query VARCHAR2(4000);
2083 l_procedure_name VARCHAR2(30);
2084 l_req_header_id NUMBER;
2085 l_serial_controlled NUMBER := 0; -- Onhand Material Status Support
2086 l_default_status_id NUMBER; -- Onhand Material Status Support
2087
2088 BEGIN
2089 l_procedure_name := 'LOT_NODE_EVENT';
2090 inv_mwb_globals.print_msg(g_pkg_name, l_procedure_name, 'Entered');
2091 inv_mwb_globals.print_msg(g_pkg_name, l_procedure_name, inv_mwb_globals.g_tree_node_value);
2092 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_EXPANDED' THEN
2093 IF inv_mwb_globals.g_tree_loc_id IS NULL THEN
2094 loc_control := 1;
2095 ELSE
2096 loc_control := 2;
2097 END IF;
2098
2099 IF inv_mwb_globals.g_tree_rev IS NULL THEN
2100 rev_control := 1;
2101 ELSE
2102 rev_control := 2;
2103 END IF;
2104
2105 IF inv_mwb_globals.g_tree_parent_lpn_id IS NULL THEN
2106 containerized := 1;
2107 ELSE
2108 containerized := 2;
2109 END IF;
2110
2111 IF containerized =1 THEN
2112 inv_mwb_globals.g_prepacked := 1;
2113 ELSE
2114 IF inv_mwb_globals.g_sub_type = 2 THEN
2115 inv_mwb_globals.g_prepacked := 1;
2116 ELSE
2117 inv_mwb_globals.g_prepacked := NULL;
2118 END IF;
2119 END IF;
2120
2121 inv_mwb_globals.g_locator_controlled := loc_control;
2122 inv_mwb_globals.g_containerized := containerized;
2123
2124 inv_mwb_tree1.add_serials(
2125 x_node_value
2126 , x_node_tbl
2127 , x_tbl_index
2128 );
2129
2130
2131 ELSIF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
2132 SELECT serial_number_control_code
2133 INTO serial_control
2134 FROM mtl_system_items
2135 WHERE organization_id = inv_mwb_globals.g_tree_organization_id
2136 AND inventory_item_id = inv_mwb_globals.g_tree_item_id;
2137
2138 IF (inv_mwb_globals.g_tree_mat_loc_id = 1) THEN
2139 IF serial_control IN(2, 5)
2140 OR inv_mwb_globals.g_status_id IS NOT NULL
2141 OR inv_mwb_globals.g_unit_number IS NOT NULL -- Bug 9486070
2142 THEN
2143 make_common_query_onhand('MSN_QUERY');
2144 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.REVISION).column_value :=
2145 'msn.revision';
2146 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SERIAL).column_value :=
2147 'msn.serial_number';
2148 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
2149 'msn.current_subinventory_code';
2150 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
2151 'msn.current_locator_id';
2152 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
2153 'msn.lpn_id';
2154 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
2155 'msn.cost_group_id';
2156 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
2157 'msn.lot_number';
2158
2159 inv_mwb_query_manager.add_group_clause('msn.revision', 'ONHAND');
2160 inv_mwb_query_manager.add_group_clause('msn.serial_number', 'ONHAND');
2161 inv_mwb_query_manager.add_group_clause('msn.current_subinventory_code', 'ONHAND');
2162 inv_mwb_query_manager.add_group_clause('msn.current_locator_id', 'ONHAND');
2163 inv_mwb_query_manager.add_group_clause('msn.lpn_id', 'ONHAND');
2164 inv_mwb_query_manager.add_group_clause('msn.cost_group_id', 'ONHAND');
2165 inv_mwb_query_manager.add_group_clause('msn.lot_number', 'ONHAND');
2166
2167 IF inv_mwb_globals.g_tree_parent_lpn_id IS NOT NULL THEN
2168 inv_mwb_query_manager.add_where_clause('msn.lpn_id = :onh_tree_plpn_id', 'ONHAND');
2169 inv_mwb_query_manager.add_bind_variable('onh_tree_plpn_id', inv_mwb_globals.g_tree_parent_lpn_id);
2170 ELSE
2171 inv_mwb_query_manager.add_where_clause('msn.lpn_id IS NULL', 'ONHAND');
2172 END IF;
2173
2174 IF inv_mwb_globals.g_tree_rev IS NOT NULL THEN
2175 inv_mwb_query_manager.add_where_clause('msn.revision = :onh_tree_revision' ,'ONHAND');
2176 inv_mwb_query_manager.add_bind_variable('onh_tree_revision', inv_mwb_globals.g_tree_rev);
2177 END IF;
2178
2179 IF inv_mwb_globals.g_tree_loc_id IS NOT NULL THEN
2180 inv_mwb_query_manager.add_where_clause('msn.current_locator_id = :onh_tree_loc_id', 'ONHAND');
2181 inv_mwb_query_manager.add_bind_variable('onh_tree_loc_id', inv_mwb_globals.g_tree_loc_id);
2182 ELSE
2183 inv_mwb_query_manager.add_where_clause('msn.current_locator_id IS NULL' ,'ONHAND');
2184 END IF;
2185
2186 inv_mwb_query_manager.add_where_clause('msn.current_organization_id = :onh_tree_organization_id' ,'ONHAND');
2187 inv_mwb_query_manager.add_where_clause('msn.current_subinventory_code = :onh_tree_subinventory_code', 'ONHAND');
2188 inv_mwb_query_manager.add_where_clause('msn.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
2189 inv_mwb_query_manager.add_where_clause('msn.lot_number = :onh_tree_lot_num' ,'ONHAND');
2190
2191
2192 inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
2193 inv_mwb_query_manager.add_bind_variable('onh_tree_subinventory_code', inv_mwb_globals.g_tree_subinventory_code);
2194 inv_mwb_query_manager.add_bind_variable('onh_tree_inventory_item_id', inv_mwb_globals.g_tree_item_id);
2195 inv_mwb_query_manager.add_bind_variable('onh_tree_lot_num', inv_mwb_globals.g_tree_node_value);
2196
2197 inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
2198 ELSE
2199 make_common_query_onhand('MOQD');
2200 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.REVISION).column_value :=
2201 'moqd.revision';
2202 -- inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SERIAL).column_value :=
2203 -- 'moqd.serial_number';
2204 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
2205 'moqd.subinventory_code';
2206 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
2207 'moqd.locator_id';
2208 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
2209 'moqd.lpn_id';
2210 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
2211 'moqd.cost_group_id';
2212 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
2213 'moqd.lot_number';
2214
2215
2216 inv_mwb_query_manager.add_group_clause('moqd.revision', 'ONHAND');
2217 -- inv_mwb_query_manager.add_group_clause('moqd.serial_number', 'ONHAND');
2218 inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
2219 inv_mwb_query_manager.add_group_clause('moqd.locator_id', 'ONHAND');
2220 inv_mwb_query_manager.add_group_clause('moqd.lpn_id', 'ONHAND');
2221 inv_mwb_query_manager.add_group_clause('moqd.cost_group_id', 'ONHAND');
2222 inv_mwb_query_manager.add_group_clause('moqd.lot_number', 'ONHAND');
2223
2224 -- Onhand Material Status Support
2225 -- For serial controlled items, the status_id will be populated in post_query of IMVMWQMB.
2226
2227 inv_mwb_globals.print_msg(g_pkg_name, l_procedure_name, 'org id' ||inv_mwb_globals.g_tree_organization_id );
2228
2229 if (inv_cache.set_org_rec(inv_mwb_globals.g_tree_organization_id)) then
2230 l_default_status_id := inv_cache.org_rec.default_status_id;
2231 end if;
2232
2233 inv_mwb_globals.print_msg(g_pkg_name, l_procedure_name, 'org status id' || l_default_status_id);
2234
2235 if inv_cache.set_item_rec(inv_mwb_globals.g_tree_organization_id, inv_mwb_globals.g_tree_item_id) then
2236 if (inv_cache.item_rec.serial_number_control_code in (2,5)) then
2237 l_serial_controlled := 1; -- Item is serial controlled
2238 end if;
2239 end if;
2240
2241 inv_mwb_globals.print_msg(g_pkg_name, l_procedure_name, 'serial controlled' || l_serial_controlled);
2242
2243 if (l_default_status_id is not null and l_serial_controlled = 0) then
2244 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.STATUS_ID).column_value :=
2245 'moqd.status_id';
2246 inv_mwb_query_manager.add_group_clause('moqd.status_id', 'ONHAND');
2247 end if;
2248 -- End Onhand Material Status Support
2249
2250 IF inv_mwb_globals.g_tree_rev IS NOT NULL THEN
2251 inv_mwb_query_manager.add_where_clause('moqd.revision = :onh_tree_revision' ,'ONHAND');
2252 inv_mwb_query_manager.add_bind_variable('onh_tree_revision', inv_mwb_globals.g_tree_rev);
2253 END IF;
2254
2255 IF inv_mwb_globals.g_tree_parent_lpn_id IS NOT NULL THEN
2256 inv_mwb_query_manager.add_where_clause('moqd.lpn_id = :onh_tree_plpn_id', 'ONHAND');
2257 inv_mwb_query_manager.add_bind_variable('onh_tree_plpn_id', inv_mwb_globals.g_tree_parent_lpn_id);
2258 ELSE
2259 inv_mwb_query_manager.add_where_clause('moqd.lpn_id IS NULL', 'ONHAND');
2260 END IF;
2261
2262 IF inv_mwb_globals.g_tree_loc_id IS NOT NULL THEN
2263 inv_mwb_query_manager.add_where_clause('moqd.locator_id = :onh_tree_loc_id', 'ONHAND');
2264 inv_mwb_query_manager.add_bind_variable('onh_tree_loc_id', inv_mwb_globals.g_tree_loc_id);
2265 ELSE
2266 inv_mwb_query_manager.add_where_clause('moqd.locator_id IS NULL', 'ONHAND');
2267 END IF;
2268
2269 inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id' ,'ONHAND');
2270 inv_mwb_query_manager.add_where_clause('moqd.subinventory_code = :onh_tree_subinventory_code', 'ONHAND');
2271 inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
2272 inv_mwb_query_manager.add_where_clause('moqd.lot_number = :onh_tree_lot_num' ,'ONHAND');
2273
2274 inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
2275 inv_mwb_query_manager.add_bind_variable('onh_tree_subinventory_code', inv_mwb_globals.g_tree_subinventory_code);
2276 inv_mwb_query_manager.add_bind_variable('onh_tree_inventory_item_id', inv_mwb_globals.g_tree_item_id);
2277 inv_mwb_query_manager.add_bind_variable('onh_tree_lot_num', inv_mwb_globals.g_tree_node_value);
2278
2279 inv_mwb_query_manager.add_qf_where_onhand('ONHAND');
2280 END IF;
2281 END IF; --onhand
2282
2283 IF (inv_mwb_globals.g_tree_mat_loc_id = 3) THEN
2284
2285
2286 CASE inv_mwb_globals.g_tree_doc_type_id
2287 WHEN 1 THEN
2288 null;
2289 WHEN 2 THEN
2290 inv_mwb_query_manager.add_qf_where_inbound('INBOUND');
2291 inv_mwb_globals.print_msg(g_pkg_name, l_procedure_name, 'For item under Requisition');
2292 inv_mwb_globals.print_msg(g_pkg_name, l_procedure_name, 'Under Req item selecetd');
2293 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.REQ_HEADER_ID).column_value :=
2294 'ms.req_header_id';
2295 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.DOCUMENT_LINE_NUMBER).column_value :=
2296 'ms.req_line_id';
2297 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.LOT).column_value :=
2298 'rls.lot_num';
2299 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.LOT_EXPIRY_DATE).column_value :=
2300 'rls.expiration_date';
2301
2302 inv_mwb_query_manager.add_group_clause('ms.req_header_id', 'INBOUND');
2303 inv_mwb_query_manager.add_group_clause('ms.req_line_id', 'INBOUND');
2304 inv_mwb_query_manager.add_group_clause('rls.lot_num', 'INBOUND');
2305 inv_mwb_query_manager.add_group_clause('rls.expiration_date', 'INBOUND');
2306 inv_mwb_query_manager.add_where_clause('ms.supply_type_code IN (''REQ'',''SHIPMENT'')', 'INBOUND');
2307 inv_mwb_query_manager.add_where_clause('ms.req_header_id = :req_header_id', 'INBOUND');
2308 inv_mwb_query_manager.add_where_clause('ms.to_organization_id = :inb_tree_organization_id', 'INBOUND');
2309 inv_mwb_query_manager.add_where_clause('ms.item_id = :item_id', 'INBOUND');
2310 IF inv_mwb_globals.g_tree_lot_number IS NOT NULL THEN
2311 inv_mwb_query_manager.add_where_clause('rls.lot_num = :lot_num', 'INBOUND');
2312 inv_mwb_query_manager.add_bind_variable('lot_num',inv_mwb_globals.g_tree_lot_number);
2313 END IF;
2314
2315 inv_mwb_query_manager.add_bind_variable('req_header_id', inv_mwb_globals.g_tree_doc_header_id);
2316 inv_mwb_query_manager.add_bind_variable('item_id', inv_mwb_globals.g_tree_item_id);
2317 inv_mwb_query_manager.add_bind_variable('inb_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
2318 WHEN 3 THEN
2319
2320 make_common_query_inbound('INBOUND');
2321 inv_mwb_query_manager.add_from_clause('mtl_supply ms ','INBOUND');
2322 inv_mwb_query_manager.add_from_clause('rcv_shipment_lines rsl ','INBOUND');
2323 inv_mwb_query_manager.add_from_clause('rcv_lots_supply rls ','INBOUND');
2324 inv_mwb_query_manager.add_where_clause('ms.shipment_line_id = rsl.shipment_line_id', 'INBOUND');
2325 inv_mwb_query_manager.add_where_clause('ms.shipment_line_id = rls.shipment_line_id', 'INBOUND');
2326
2327 -- for bug 8420783
2328 -- for the bug 8414727
2329 IF (inv_mwb_globals.g_serial_from IS NOT NULl or inv_mwb_globals.g_serial_to is NOT NULL) THEN
2330 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.INBOUND).column_value :=
2331 'count(1)';
2332 ELSE
2333 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.INBOUND).column_value :=
2334 'sum(rls.quantity)';
2335 END IF ;
2336
2337 IF serial_control = 2 or serial_control = 5 THEN
2338
2339 inv_mwb_query_manager.add_from_clause('rcv_serials_supply rss ','INBOUND');
2340 inv_mwb_query_manager.add_where_clause('rss.shipment_line_id = rsl.shipment_line_id', 'INBOUND');
2341
2342 inv_mwb_query_manager.add_where_clause('rss.lot_num = rls.lot_num', 'INBOUND');
2343 -- end of bug 8414727
2344 -- end of bug 8420783
2345
2346 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.SERIAL).column_value :=
2347 'rss.serial_num';
2348 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.INBOUND).column_value := 1;
2349 inv_mwb_query_manager.add_group_clause('rss.serial_num', 'INBOUND');
2350
2351 IF NVL(inv_mwb_globals.g_serial_from, -1) = NVL(inv_mwb_globals.g_serial_to,-2) THEN
2352 inv_mwb_query_manager.add_where_clause('rss.serial_num = :serial_num', 'INBOUND');
2353 inv_mwb_query_manager.add_bind_variable('serial_num',inv_mwb_globals.g_serial_from);
2354 ELSE
2355 IF inv_mwb_globals.g_serial_from IS NOT NULL THEN
2356 inv_mwb_query_manager.add_where_clause('rss.serial_num >= :serial_from', 'INBOUND');
2357 inv_mwb_query_manager.add_bind_variable('serial_from',inv_mwb_globals.g_serial_from);
2358 END IF;
2359 IF inv_mwb_globals.g_serial_to IS NOT NULL THEN
2360 -- for bug 8420783
2361 -- for bug 8414727
2362 inv_mwb_query_manager.add_where_clause('rss.serial_num <= :serial_to', 'INBOUND');
2363 -- end of bug 8414727
2364 -- end of bug 8420783
2365 inv_mwb_query_manager.add_bind_variable('serial_to',inv_mwb_globals.g_serial_to);
2366 END IF;
2367 END IF;
2368 END IF;
2369
2370 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.REVISION).column_value :=
2371 'ms.item_revision';
2372 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.SHIPMENT_HEADER_ID_ASN).column_value :=
2373 'ms.shipment_header_id';
2374 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.DOCUMENT_LINE_NUMBER).column_value :=
2375 'ms.shipment_line_id';
2376 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.LOT).column_value :=
2377 'rls.lot_num';
2378 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.LOT_EXPIRY_DATE).column_value :=
2379 'rls.expiration_date';
2380 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.SHIPPED_DATE).column_value :=
2381 'ms.receipt_date';
2382 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.OWNING_ORG_ID).column_value :=
2383 'ms.intransit_owning_org_id';
2384
2385 inv_mwb_query_manager.add_group_clause('ms.shipment_header_id', 'INBOUND');
2386 inv_mwb_query_manager.add_group_clause('ms.shipment_line_id', 'INBOUND');
2387 inv_mwb_query_manager.add_group_clause('ms.intransit_owning_org_id', 'INBOUND');
2388 inv_mwb_query_manager.add_group_clause('rls.lot_num', 'INBOUND');
2389 inv_mwb_query_manager.add_group_clause('rls.expiration_date', 'INBOUND');
2390 inv_mwb_query_manager.add_group_clause('ms.receipt_date', 'INBOUND');
2391 inv_mwb_query_manager.add_group_clause('ms.item_revision', 'INBOUND');
2392
2393 IF inv_mwb_globals.g_tree_parent_lpn_id IS NOT NULL THEN
2394 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.LPN_ID).column_value :=
2395 'rsl.asn_lpn_id';
2396 inv_mwb_query_manager.add_where_clause('rsl.asn_lpn_id = :asn_lpn_id', 'INBOUND');
2397 inv_mwb_query_manager.add_bind_variable('asn_lpn_id',inv_mwb_globals.g_tree_parent_lpn_id);
2398 inv_mwb_query_manager.add_group_clause('rsl.asn_lpn_id', 'INBOUND');
2399 END IF;
2400
2401 IF inv_mwb_globals.g_tree_lot_number IS NOT NULL THEN
2402 inv_mwb_query_manager.add_where_clause('rls.lot_num = :lot_num', 'INBOUND');
2403 inv_mwb_query_manager.add_bind_variable('lot_num',inv_mwb_globals.g_tree_lot_number);
2404 END IF;
2405
2406 IF inv_mwb_globals.g_tree_rev IS NOT NULL THEN
2407 inv_mwb_query_manager.add_where_clause('ms.item_revision = :inb_tree_rev', 'INBOUND');
2408 inv_mwb_query_manager.add_bind_variable('inb_tree_rev',inv_mwb_globals.g_tree_rev);
2409 END IF;
2410
2411 inv_mwb_query_manager.add_where_clause('rsl.shipment_header_id = :shipment_header_id', 'INBOUND');
2412 inv_mwb_query_manager.add_bind_variable('shipment_header_id', inv_mwb_globals.g_tree_doc_header_id);
2413
2414 WHEN 4 THEN
2415 make_common_query_inbound('INBOUND');
2416 inv_mwb_query_manager.add_from_clause('mtl_supply ms ','INBOUND');
2417 inv_mwb_query_manager.add_from_clause('rcv_shipment_lines rsl ','INBOUND');
2418 inv_mwb_query_manager.add_from_clause('rcv_lots_supply rls ','INBOUND');
2419 inv_mwb_query_manager.add_where_clause('ms.shipment_line_id = rsl.shipment_line_id', 'INBOUND');
2420 inv_mwb_query_manager.add_where_clause('ms.shipment_line_id = rls.shipment_line_id', 'INBOUND');
2421
2422 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.INBOUND).column_value :=
2423 'sum(rls.quantity)';
2424
2425 IF serial_control = 2
2426 OR inv_mwb_globals.g_serial_from IS NOT NULL
2427 OR inv_mwb_globals.g_serial_from IS NOT NULL THEN
2428 inv_mwb_query_manager.add_from_clause('rcv_serials_supply rss ','INBOUND');
2429 inv_mwb_query_manager.add_where_clause('rss.shipment_line_id = rsl.shipment_line_id', 'INBOUND');
2430
2431 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.SERIAL).column_value :=
2432 'rss.serial_num';
2433 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.INBOUND).column_value := 1;
2434
2435 inv_mwb_query_manager.add_group_clause('rss.serial_num', 'INBOUND');
2436 END IF;
2437
2438 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.REVISION).column_value :=
2439 'ms.item_revision';
2440 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.SHIPMENT_HEADER_ID_ASN).column_value :=
2441 'ms.shipment_header_id';
2442 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.DOCUMENT_LINE_NUMBER).column_value :=
2443 'ms.shipment_line_id';
2444 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.LOT).column_value :=
2445 'rls.lot_num';
2446 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.LOT_EXPIRY_DATE).column_value :=
2447 'rls.expiration_date';
2448 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.SHIPPED_DATE).column_value :=
2449 'ms.receipt_date';
2450 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.OWNING_ORG_ID).column_value :=
2451 'ms.intransit_owning_org_id';
2452
2453 inv_mwb_query_manager.add_group_clause('ms.shipment_header_id', 'INBOUND');
2454 inv_mwb_query_manager.add_group_clause('ms.shipment_line_id', 'INBOUND');
2455 inv_mwb_query_manager.add_group_clause('ms.intransit_owning_org_id', 'INBOUND');
2456 inv_mwb_query_manager.add_group_clause('rls.lot_num', 'INBOUND');
2457 inv_mwb_query_manager.add_group_clause('rls.expiration_date', 'INBOUND');
2458 inv_mwb_query_manager.add_group_clause('ms.receipt_date', 'INBOUND');
2459 inv_mwb_query_manager.add_group_clause('ms.item_revision', 'INBOUND');
2460
2461 IF inv_mwb_globals.g_tree_parent_lpn_id IS NOT NULL THEN
2462 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.LPN_ID).column_value :=
2463 'rsl.asn_lpn_id';
2464 inv_mwb_query_manager.add_where_clause('rsl.asn_lpn_id = :asn_lpn_id', 'INBOUND');
2465 inv_mwb_query_manager.add_bind_variable('asn_lpn_id',inv_mwb_globals.g_tree_parent_lpn_id);
2466 inv_mwb_query_manager.add_group_clause('rsl.asn_lpn_id', 'INBOUND');
2467 END IF;
2468
2469 IF inv_mwb_globals.g_tree_lot_number IS NOT NULL THEN
2470 inv_mwb_query_manager.add_where_clause('rls.lot_num = :lot_num', 'INBOUND');
2471 inv_mwb_query_manager.add_bind_variable('lot_num',inv_mwb_globals.g_tree_lot_number);
2472 END IF;
2473
2474 IF inv_mwb_globals.g_tree_rev IS NOT NULL THEN
2475 inv_mwb_query_manager.add_where_clause('ms.item_revision = :inb_tree_rev', 'INBOUND');
2476 inv_mwb_query_manager.add_bind_variable('inb_tree_rev',inv_mwb_globals.g_tree_rev);
2477 END IF;
2478
2479 inv_mwb_query_manager.add_where_clause('rsl.shipment_header_id = :shipment_header_id', 'INBOUND');
2480 inv_mwb_query_manager.add_bind_variable('shipment_header_id', inv_mwb_globals.g_tree_doc_header_id);
2481
2482 END CASE;
2483 END IF;
2484
2485 IF inv_mwb_globals.g_tree_mat_loc_id = 2 THEN
2486
2487 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
2488 'rs.to_subinventory';
2489 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
2490 'rs.to_locator_id';
2491 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.LPN_ID).column_value :=
2492 'rs.lpn_id';
2493 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.REVISION).column_value :=
2494 'rs.item_revision';
2495
2496 inv_mwb_query_manager.add_group_clause('rs.to_subinventory', 'RECEIVING');
2497 inv_mwb_query_manager.add_group_clause('rs.to_locator_id', 'RECEIVING');
2498 inv_mwb_query_manager.add_group_clause('rs.lpn_id', 'RECEIVING');
2499 inv_mwb_query_manager.add_group_clause('rs.item_revision', 'RECEIVING');
2500
2501
2502 IF inv_mwb_globals.g_tree_parent_lpn_id IS NOT NULL THEN
2503 inv_mwb_query_manager.add_where_clause('rs.lpn_id = :rcv_tree_plpn_id' ,'RECEIVING');
2504 inv_mwb_query_manager.add_bind_variable('rcv_tree_plpn_id', inv_mwb_globals.g_tree_parent_lpn_id);
2505 END IF;
2506 IF inv_mwb_globals.g_tree_loc_id IS NOT NULL THEN
2507 inv_mwb_query_manager.add_where_clause('rs.to_locator_id = :rcv_tree_loc_id' ,'RECEIVING');
2508 inv_mwb_query_manager.add_bind_variable('rcv_tree_loc_id', inv_mwb_globals.g_tree_loc_id);
2509 END IF;
2510 IF inv_mwb_globals.g_tree_subinventory_code IS NOT NULL THEN
2511 inv_mwb_query_manager.add_where_clause('rs.to_subinventory = :rcv_tree_subinventory_code', 'RECEIVING');
2512 inv_mwb_query_manager.add_bind_variable('rcv_tree_subinventory_code', inv_mwb_globals.g_tree_subinventory_code);
2513 END IF;
2514 IF inv_mwb_globals.g_tree_rev IS NOT NULL THEN
2515 inv_mwb_query_manager.add_where_clause('rs.item_revision = :rcv_tree_rev', 'RECEIVING');
2516 inv_mwb_query_manager.add_bind_variable('rcv_tree_rev', inv_mwb_globals.g_tree_rev);
2517 END IF;
2518
2519 inv_mwb_query_manager.add_where_clause('rs.to_organization_id = :rcv_tree_organization_id', 'RECEIVING');
2520 inv_mwb_query_manager.add_where_clause('rs.item_id = :rcv_tree_item_id', 'RECEIVING');
2521
2522 inv_mwb_query_manager.add_bind_variable('rcv_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
2523 inv_mwb_query_manager.add_bind_variable('rcv_tree_item_id', inv_mwb_globals.g_tree_item_id);
2524
2525 --Serial Controlled
2526 IF (inv_mwb_globals.g_serial_from IS NOT NULL OR inv_mwb_globals.g_serial_to IS NOT NULL)
2527 OR serial_control IN ( 2,5 ) THEN
2528 inv_mwb_query_manager.add_qf_where_receiving('MSN');
2529 make_common_query_receiving('MSN');
2530 inv_mwb_query_manager.add_where_clause('rss.lot_num = :rcv_lot_num', 'RECEIVING');
2531 inv_mwb_query_manager.add_bind_variable('rcv_lot_num', inv_mwb_globals.g_tree_lot_number);
2532
2533 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.LOT).column_value :=
2534 'rss.lot_num';
2535 inv_mwb_query_manager.add_group_clause('rss.lot_num', 'RECEIVING');
2536
2537 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SERIAL).column_value :=
2538 'rss.serial_num';
2539 inv_mwb_query_manager.add_group_clause('rss.serial_num', 'RECEIVING');
2540 ELSIF serial_control NOT IN (2,5) THEN
2541 inv_mwb_query_manager.add_qf_where_receiving('RECEIVING');
2542 make_common_query_receiving('RECEIVING');
2543 inv_mwb_query_manager.add_where_clause('rls.lot_num = :rcv_lot_num', 'RECEIVING');
2544 inv_mwb_query_manager.add_bind_variable('rcv_lot_num', inv_mwb_globals.g_tree_lot_number);
2545 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.LOT).column_value :=
2546 'rls.lot_num';
2547 inv_mwb_query_manager.add_group_clause('rls.lot_num', 'RECEIVING');
2548 -- Bug 9865190 Start
2549 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.RECEIVING).column_value :=
2550 'SUM(rls.primary_quantity)';
2551 inv_mwb_query_manager.add_where_clause('rls.supply_type_code = ''RECEIVING''', 'RECEIVING');
2552 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.UNPACKED).column_value :=
2553 'SUM(DECODE (rs.lpn_id, null, rls.primary_quantity, 0))';
2554 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.PACKED).column_value :=
2555 'SUM(DECODE (rs.lpn_id, null, 0, rls.primary_quantity))';
2556 --added for bug 13812686 START
2557 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SECONDARY_RECEIVING).column_value :=
2558 'SUM(rls.secondary_quantity)';
2559 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SECONDARY_UNPACKED).column_value :=
2560 'SUM(DECODE (rs.lpn_id, null, rls.secondary_quantity, 0))';
2561 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SECONDARY_PACKED).column_value :=
2562 'SUM(DECODE (rs.lpn_id, null, 0, rls.secondary_quantity))';
2563
2564 --added for bug 13812686 END
2565 -- Bug 9865190 End
2566 END IF;
2567 END IF;
2568 inv_mwb_query_manager.execute_query; -- Bug 6060233
2569 END IF; -- node selected
2570 EXCEPTION
2571 WHEN no_data_found THEN
2572 NULL;
2573 END lot_node_event;
2574
2575 PROCEDURE serial_node_event(
2576 x_node_value IN OUT NOCOPY NUMBER
2577 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
2578 , x_tbl_index IN OUT NOCOPY NUMBER
2579 )
2580 IS
2581 serial mtl_serial_numbers.serial_number%TYPE;
2582 loc_control NUMBER;
2583 serial_control NUMBER;
2584 from_receiving NUMBER;
2585 str_query VARCHAR2(4000);
2586 l_procedure_name VARCHAR2(30);
2587 l_req_header_id NUMBER;
2588 BEGIN
2589 l_procedure_name := 'SERIAL_NODE_EVENT';
2590 inv_mwb_globals.print_msg(g_pkg_name, l_procedure_name, 'Entered');
2591 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
2592 IF inv_mwb_globals.g_tree_mat_loc_id = 1 THEN
2593 make_common_query_onhand('MSN');
2594 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.REVISION).column_value :=
2595 'msn.revision';
2596 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
2597 'msn.current_locator_id';
2598 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
2599 'msn.lpn_id';
2600 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
2601 'msn.lot_number';
2602 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SERIAL).column_value :=
2603 'msn.serial_number';
2604 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
2605 'msn.current_subinventory_code';
2606 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
2607 'msn.cost_group_id';
2608
2609
2610 inv_mwb_query_manager.add_group_clause('msn.revision', 'ONHAND');
2611 inv_mwb_query_manager.add_group_clause('msn.current_locator_id', 'ONHAND');
2612 inv_mwb_query_manager.add_group_clause('msn.lpn_id', 'ONHAND');
2613 inv_mwb_query_manager.add_group_clause('msn.lot_number', 'ONHAND');
2614 inv_mwb_query_manager.add_group_clause('msn.serial_number', 'ONHAND');
2615 inv_mwb_query_manager.add_group_clause('msn.current_subinventory_code', 'ONHAND');
2616 inv_mwb_query_manager.add_group_clause('msn.cost_group_id', 'ONHAND');
2617 inv_mwb_query_manager.add_group_clause('msn.inventory_item_id', 'ONHAND');
2618 inv_mwb_query_manager.add_group_clause('msn.current_organization_id', 'ONHAND');
2619
2620
2621 IF inv_mwb_globals.g_tree_loc_id IS NOT NULL THEN
2622 inv_mwb_query_manager.add_where_clause('msn.current_locator_id = :onh_tree_loc_id', 'ONHAND');
2623 inv_mwb_query_manager.add_bind_variable('onh_tree_loc_id', inv_mwb_globals.g_tree_loc_id);
2624 END IF;
2625
2626 IF inv_mwb_globals.g_tree_parent_lpn_id IS NOT NULL THEN
2627 inv_mwb_query_manager.add_where_clause('msn.lpn_id = :onh_tree_plpn_id', 'ONHAND');
2628 inv_mwb_query_manager.add_bind_variable('onh_tree_plpn_id', inv_mwb_globals.g_tree_parent_lpn_id);
2629 END IF;
2630
2631 IF inv_mwb_globals.g_tree_rev IS NOT NULL THEN
2632 inv_mwb_query_manager.add_where_clause('msn.revision = :onh_tree_revision' ,'ONHAND');
2633 inv_mwb_query_manager.add_bind_variable('onh_tree_revision', inv_mwb_globals.g_tree_rev);
2634 END IF;
2635
2636 IF inv_mwb_globals.g_tree_lot_number IS NOT NULL THEN
2637 inv_mwb_query_manager.add_where_clause('msn.lot_number = :onh_tree_lot_number' ,'ONHAND');
2638 inv_mwb_query_manager.add_bind_variable('onh_tree_lot_number', inv_mwb_globals.g_tree_lot_number);
2639 END IF;
2640
2641 inv_mwb_query_manager.add_where_clause('msn.current_organization_id = :onh_tree_organization_id' ,'ONHAND');
2642 inv_mwb_query_manager.add_where_clause('msn.current_subinventory_code = :onh_tree_subinventory_code', 'ONHAND');
2643 inv_mwb_query_manager.add_where_clause('msn.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
2644 inv_mwb_query_manager.add_where_clause('msn.serial_number = :onh_tree_serial_number' ,'ONHAND');
2645
2646 inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
2647 inv_mwb_query_manager.add_bind_variable('onh_tree_subinventory_code', inv_mwb_globals.g_tree_subinventory_code);
2648 inv_mwb_query_manager.add_bind_variable('onh_tree_inventory_item_id', inv_mwb_globals.g_tree_item_id);
2649 inv_mwb_query_manager.add_bind_variable('onh_tree_serial_number', inv_mwb_globals.g_tree_node_value);
2650
2651 inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
2652
2653 END IF;
2654
2655 IF (inv_mwb_globals.g_tree_mat_loc_id = 3) THEN
2656
2657 make_common_query_inbound('INBOUND');
2658 inv_mwb_query_manager.add_from_clause('mtl_supply ms ','INBOUND');
2659 inv_mwb_query_manager.add_from_clause('rcv_shipment_lines rsl ','INBOUND');
2660 inv_mwb_query_manager.add_from_clause('rcv_serials_supply rss ','INBOUND');
2661 inv_mwb_query_manager.add_where_clause('rss.shipment_line_id = rsl.shipment_line_id', 'INBOUND');
2662 inv_mwb_query_manager.add_where_clause('ms.shipment_line_id = rsl.shipment_line_id', 'INBOUND');
2663
2664 IF inv_mwb_globals.g_tree_lot_number IS NOT NULL THEN
2665 inv_mwb_query_manager.add_from_clause('rcv_lots_supply rls ','INBOUND');
2666 inv_mwb_query_manager.add_where_clause('ms.shipment_line_id = rls.shipment_line_id', 'INBOUND');
2667
2668 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.LOT).column_value :=
2669 'rls.lot_num';
2670 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.LOT_EXPIRY_DATE).column_value :=
2671 'rls.expiration_date';
2672 inv_mwb_query_manager.add_group_clause('rls.lot_num', 'INBOUND');
2673 inv_mwb_query_manager.add_group_clause('rls.expiration_date', 'INBOUND');
2674
2675 inv_mwb_query_manager.add_where_clause('rls.lot_num = :lot_num', 'INBOUND');
2676 inv_mwb_query_manager.add_bind_variable('lot_num',inv_mwb_globals.g_tree_lot_number);
2677 END IF;
2678
2679
2680 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.SERIAL).column_value :=
2681 'rss.serial_num';
2682 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.SHIPMENT_HEADER_ID_ASN).column_value :=
2683 'ms.shipment_header_id';
2684 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.DOCUMENT_LINE_NUMBER).column_value :=
2685 'ms.shipment_line_id';
2686 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.SHIPPED_DATE).column_value :=
2687 'ms.receipt_date';
2688 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.OWNING_ORG_ID).column_value :=
2689 'ms.intransit_owning_org_id';
2690 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.INBOUND).column_value := 1;
2691 inv_mwb_query_manager.add_group_clause('ms.shipment_header_id', 'INBOUND');
2692 inv_mwb_query_manager.add_group_clause('ms.shipment_line_id', 'INBOUND');
2693 inv_mwb_query_manager.add_group_clause('ms.intransit_owning_org_id', 'INBOUND');
2694 inv_mwb_query_manager.add_group_clause('ms.receipt_date', 'INBOUND');
2695 inv_mwb_query_manager.add_group_clause('rss.serial_num', 'INBOUND');
2696
2697 IF inv_mwb_globals.g_tree_parent_lpn_id IS NOT NULL THEN
2698 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.LPN_ID).column_value :=
2699 'rsl.asn_lpn_id';
2700 inv_mwb_query_manager.add_where_clause('rsl.asn_lpn_id = :asn_lpn_id', 'INBOUND');
2701 inv_mwb_query_manager.add_bind_variable('asn_lpn_id',inv_mwb_globals.g_tree_parent_lpn_id);
2702 inv_mwb_query_manager.add_group_clause('rsl.asn_lpn_id', 'INBOUND');
2703 END IF;
2704
2705 IF inv_mwb_globals.g_tree_rev IS NOT NULL THEN
2706 inv_mwb_query_manager.add_where_clause('ms.item_revision = :inb_tree_rev', 'INBOUND');
2707 inv_mwb_query_manager.add_bind_variable('inb_tree_rev',inv_mwb_globals.g_tree_rev);
2708 END IF;
2709
2710 inv_mwb_query_manager.add_where_clause('rsl.shipment_header_id = :shipment_header_id', 'INBOUND');
2711 inv_mwb_query_manager.add_bind_variable('shipment_header_id', inv_mwb_globals.g_tree_doc_header_id);
2712
2713 inv_mwb_query_manager.add_where_clause('rss.serial_num = :serial' ,'INBOUND');
2714 inv_mwb_query_manager.add_bind_variable('serial', inv_mwb_globals.g_tree_serial_number);
2715
2716 END IF;
2717
2718 IF inv_mwb_globals.g_tree_mat_loc_id = 2 THEN
2719
2720 inv_mwb_query_manager.add_qf_where_receiving('MSN');
2721 make_common_query_receiving('MSN');
2722
2723 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
2724 'rs.to_subinventory';
2725 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
2726 'rs.to_locator_id';
2727 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.LPN_ID).column_value :=
2728 'rs.lpn_id';
2729 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.REVISION).column_value :=
2730 'rs.item_revision';
2731 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.LOT).column_value :=
2732 'rss.lot_num';
2733 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SERIAL).column_value :=
2734 'rss.serial_num';
2735
2736 inv_mwb_query_manager.add_group_clause('rs.to_subinventory', 'RECEIVING');
2737 inv_mwb_query_manager.add_group_clause('rs.to_locator_id', 'RECEIVING');
2738 inv_mwb_query_manager.add_group_clause('rs.lpn_id', 'RECEIVING');
2739 inv_mwb_query_manager.add_group_clause('rs.item_revision', 'RECEIVING');
2740 inv_mwb_query_manager.add_group_clause('rss.lot_num', 'RECEIVING');
2741 inv_mwb_query_manager.add_group_clause('rss.serial_num', 'RECEIVING');
2742
2743
2744 IF inv_mwb_globals.g_tree_parent_lpn_id IS NOT NULL THEN
2745 inv_mwb_query_manager.add_where_clause('rs.lpn_id = :rcv_tree_plpn_id' ,'RECEIVING');
2746 inv_mwb_query_manager.add_bind_variable('rcv_tree_plpn_id', inv_mwb_globals.g_tree_parent_lpn_id);
2747 END IF;
2748 IF inv_mwb_globals.g_tree_loc_id IS NOT NULL THEN
2749 inv_mwb_query_manager.add_where_clause('rs.to_locator_id = :rcv_tree_loc_id' ,'RECEIVING');
2750 inv_mwb_query_manager.add_bind_variable('rcv_tree_loc_id', inv_mwb_globals.g_tree_loc_id);
2751 END IF;
2752 IF inv_mwb_globals.g_tree_subinventory_code IS NOT NULL THEN
2753 inv_mwb_query_manager.add_where_clause('rs.to_subinventory = :rcv_tree_subinventory_code', 'RECEIVING');
2754 inv_mwb_query_manager.add_bind_variable('rcv_tree_subinventory_code', inv_mwb_globals.g_tree_subinventory_code);
2755 END IF;
2756
2757 IF inv_mwb_globals.g_tree_rev IS NOT NULL THEN
2758 inv_mwb_query_manager.add_where_clause('rs.item_revision = :rcv_tree_rev', 'RECEIVING');
2759 inv_mwb_query_manager.add_bind_variable('rcv_tree_rev', inv_mwb_globals.g_tree_rev);
2760 END IF;
2761
2762 IF inv_mwb_globals.g_tree_lot_number IS NOT NULL THEN
2763 inv_mwb_query_manager.add_where_clause('rss.lot_num = :rcv_lot_num', 'RECEIVING');
2764 inv_mwb_query_manager.add_bind_variable('rcv_lot_num', inv_mwb_globals.g_tree_lot_number);
2765 END IF;
2766
2767 inv_mwb_query_manager.add_where_clause('rs.to_organization_id = :rcv_tree_organization_id', 'RECEIVING');
2768 inv_mwb_query_manager.add_where_clause('rs.item_id = :rcv_tree_item_id', 'RECEIVING');
2769 inv_mwb_query_manager.add_where_clause('rss.serial_num = :rcv_tree_serial_num', 'RECEIVING');
2770
2771 inv_mwb_query_manager.add_bind_variable('rcv_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
2772 inv_mwb_query_manager.add_bind_variable('rcv_tree_item_id', inv_mwb_globals.g_tree_item_id);
2773 inv_mwb_query_manager.add_bind_variable('rcv_tree_serial_num', inv_mwb_globals.g_tree_serial_number);
2774
2775 END IF;
2776 inv_mwb_query_manager.execute_query;
2777 END IF;
2778 EXCEPTION
2779 WHEN no_data_found THEN
2780 NULL;
2781 END serial_node_event;
2782
2783 --
2784 -- public functions
2785 --
2786
2787
2788 PROCEDURE matloc_node_event(
2789 x_node_value IN OUT NOCOPY NUMBER
2790 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
2791 , x_tbl_index IN OUT NOCOPY NUMBER
2792 )
2793 IS
2794 i NUMBER := 1;
2795 j NUMBER := 1;
2796 query_str VARCHAR2(4000);
2797
2798 grade_f mtl_material_status_history.grade_code%TYPE ; -- NSRIVAST, INVCONV
2799 l_procedure_name VARCHAR2(30);
2800
2801 TYPE lookup_meaning_table IS TABLE OF mfg_lookups.meaning%TYPE
2802 INDEX BY BINARY_INTEGER;
2803
2804 document_type_meaning lookup_meaning_table;
2805 ctr_lookup_code number := 1;
2806 BEGIN
2807 l_procedure_name := 'MATLOC_NODE_EVENT';
2808 inv_mwb_globals.print_msg(g_pkg_name, l_procedure_name, 'Entered');
2809 SELECT meaning
2810 BULK COLLECT INTO document_type_meaning
2811 FROM mfg_lookups
2812 WHERE lookup_type = 'MTL_DOCUMENT_TYPES'
2813 ORDER BY lookup_code;
2814
2815 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_EXPANDED' THEN
2816 IF inv_mwb_globals.g_tree_mat_loc_id = 1
2817 OR inv_mwb_globals.g_tree_mat_loc_id = 2
2818 THEN
2819 inv_mwb_tree1.add_subs(
2820 x_node_value
2821 , x_node_tbl
2822 , x_tbl_index
2823 );
2824
2825 IF inv_mwb_globals.g_tree_mat_loc_id = 2
2826 THEN
2827
2828 inv_mwb_globals.g_locator_controlled := 2;
2829
2830 inv_mwb_tree1.add_lpns(
2831 x_node_value
2832 , x_node_tbl
2833 , x_tbl_index
2834 );
2835
2836 IF inv_mwb_globals.g_lpn_from IS NULL
2837 AND inv_mwb_globals.g_lpn_to IS NULL THEN
2838
2839 inv_mwb_globals.g_containerized := 1;
2840 inv_mwb_globals.g_locator_controlled := 2;
2841 IF inv_mwb_globals.g_tree_node_type = 'MATLOC' THEN
2842 inv_mwb_globals.g_inserted_under_org := 'Y';
2843 ELSE
2844 inv_mwb_globals.g_inserted_under_org := 'N';
2845 END IF;
2846
2847 inv_mwb_tree1.add_items(
2848 x_node_value
2849 , x_node_tbl
2850 , x_tbl_index
2851 );
2852
2853 END IF;
2854 END IF;
2855 ELSIF inv_mwb_globals.g_tree_mat_loc_id = 3 then
2856 IF inv_mwb_globals.g_shipment_header_id_interorg IS NOT NULL
2857 OR inv_mwb_globals.g_req_header_id IS NOT NULL
2858 OR inv_mwb_globals.g_shipment_header_id_asn IS NOT NULL
2859 OR inv_mwb_globals.g_po_header_id IS NOT NULL
2860 THEN
2861 ctr_lookup_code := 1;
2862
2863 IF inv_mwb_globals.g_po_header_id IS NOT NULL
2864 THEN
2865 /* add PO as the document type */
2866 x_node_tbl (i).state := -1;
2867 x_node_tbl (i).DEPTH := 1;
2868 x_node_tbl (i).label := document_type_meaning(1);
2869 x_node_tbl (i).icon := 'tree_account';
2870 x_node_tbl (i).VALUE := 1;
2871 x_node_tbl (i).TYPE := 'DOCTYPE';
2872 i := i + 1;
2873 END IF;
2874
2875 IF inv_mwb_globals.g_req_header_id IS NOT NULL
2876 THEN
2877 x_node_tbl (i).state := -1;
2878 x_node_tbl (i).DEPTH := 1;
2879 x_node_tbl (i).label := document_type_meaning (2);
2880 x_node_tbl (i).icon := 'tree_account';
2881 x_node_tbl (i).VALUE := 2;
2882 x_node_tbl (i).TYPE := 'DOCTYPE';
2883 i := i + 1;
2884 END IF;
2885
2886 IF inv_mwb_globals.g_shipment_header_id_interorg IS NOT NULL
2887 THEN
2888 x_node_tbl (i).state := -1;
2889 x_node_tbl (i).DEPTH := 1;
2890 x_node_tbl (i).label := document_type_meaning (3);
2891 x_node_tbl (i).icon := 'tree_account';
2892 x_node_tbl (i).VALUE := 3;
2893 x_node_tbl (i).TYPE := 'DOCTYPE';
2894 i := i + 1;
2895 END IF;
2896
2897 IF inv_mwb_globals.g_shipment_header_id_asn IS NOT NULL
2898 THEN
2899 /* add ASN as the document type */
2900 x_node_tbl (i).state := -1;
2901 x_node_tbl (i).DEPTH := 1;
2902 x_node_tbl (i).label := document_type_meaning (4);
2903 x_node_tbl (i).icon := 'tree_account';
2904 x_node_tbl (i).VALUE := 4;
2905 x_node_tbl (i).TYPE := 'DOCTYPE';
2906 i := i + 1;
2907 END IF;
2908 ELSE
2909 FOR i IN 1 .. document_type_meaning .COUNT
2910 LOOP
2911 x_node_tbl (i).state := -1;
2912 x_node_tbl (i).DEPTH := 1;
2913 x_node_tbl (i).label := document_type_meaning (i);
2914 x_node_tbl (i).icon := 'tree_account';
2915 x_node_tbl (i).VALUE := i;
2916 x_node_tbl (i).TYPE := 'DOCTYPE';
2917 END LOOP;
2918 END IF;
2919 END IF;
2920
2921 ELSIF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
2922 inv_mwb_globals.print_msg( g_pkg_name, l_procedure_name, 'Tree Node Selected' );
2923 CASE inv_mwb_globals.g_tree_mat_loc_id
2924 WHEN 1 THEN
2925 IF inv_mwb_globals.g_serial_from IS NOT NULL
2926 OR inv_mwb_globals.g_serial_to IS NOT NULL
2927 OR inv_mwb_globals.g_status_id IS NOT NULL -- Bug 6060233
2928 OR inv_mwb_globals.g_serial_attr_query IS NOT NULL -- Bug 6429880
2929 OR inv_mwb_globals.g_unit_number IS NOT NULL -- Bug 9486070
2930 THEN
2931 make_common_query_onhand('MSN_QUERY');
2932 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
2933 'msn.current_subinventory_code';
2934 inv_mwb_query_manager.add_group_clause('msn.current_subinventory_code', 'ONHAND');
2935
2936 inv_mwb_query_manager.add_where_clause('msn.current_organization_id = :onh_tree_organization_id', 'ONHAND');
2937 inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
2938 inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
2939 ELSE
2940 make_common_query_onhand('MOQD');
2941 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
2942 'moqd.subinventory_code';
2943 inv_mwb_query_manager.add_group_clause('moqd.subinventory_code', 'ONHAND');
2944
2945 inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id', 'ONHAND');
2946 inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
2947 inv_mwb_query_manager.add_qf_where_onhand('ONHAND');
2948 END IF;
2949 WHEN 2 THEN
2950 make_common_query_receiving('RECEIVING');
2951 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
2952 'rs.to_subinventory';
2953 inv_mwb_query_manager.add_group_clause('rs.to_subinventory', 'RECEIVING');
2954 inv_mwb_query_manager.add_where_clause('rs.to_organization_id = :rcv_tree_organization_id', 'RECEIVING');
2955 inv_mwb_query_manager.add_bind_variable('rcv_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
2956 inv_mwb_query_manager.add_qf_where_receiving('RECEIVING');
2957 WHEN 3 THEN
2958 make_common_query_inbound('INBOUND');
2959 inv_mwb_globals.print_msg( g_pkg_name, l_procedure_name, 'Going to add po_header_id in select');
2960 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.PO_HEADER_ID).column_value :=
2961 'ms.po_header_id';
2962 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.REQ_HEADER_ID).column_value :=
2963 'ms.req_header_id';
2964 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.SHIPMENT_HEADER_ID_ASN).column_value :=
2965 'ms.shipment_header_id';
2966 inv_mwb_query_manager.add_group_clause('ms.po_header_id', 'INBOUND');
2967 inv_mwb_query_manager.add_group_clause('ms.req_header_id', 'INBOUND');
2968 inv_mwb_query_manager.add_group_clause('ms.shipment_header_id', 'INBOUND');
2969 inv_mwb_query_manager.add_where_clause('ms.to_organization_id = :inb_tree_organization_id', 'INBOUND');
2970 inv_mwb_query_manager.add_bind_variable('inb_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
2971 inv_mwb_query_manager.add_qf_where_inbound('INBOUND');
2972 END CASE;
2973 inv_mwb_globals.print_msg( g_pkg_name, l_procedure_name, 'Going to build and execute the query');
2974 inv_mwb_query_manager.execute_query;
2975 END IF;
2976 EXCEPTION
2977 WHEN no_data_found THEN
2978 NULL;
2979 END matloc_node_event;
2980
2981
2982 PROCEDURE doc_type_node_event(
2983 x_node_value IN OUT NOCOPY NUMBER
2984 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
2985 , x_tbl_index IN OUT NOCOPY NUMBER
2986 )
2987 IS
2988 i NUMBER := 1;
2989 j NUMBER := 1;
2990 l_procedure_name VARCHAR2(30);
2991 BEGIN
2992 l_procedure_name := 'DOC_TYPE_NODE_EVENT';
2993 inv_mwb_globals.print_msg(g_pkg_name, l_procedure_name, 'Entered');
2994 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_EXPANDED' THEN
2995 inv_mwb_tree1.add_document_numbers(
2996 x_node_value
2997 , x_node_tbl
2998 , x_tbl_index
2999 );
3000 ELSIF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
3001 make_common_query_inbound('INBOUND');
3002 inv_mwb_query_manager.add_qf_where_inbound('INBOUND');
3003
3004 CASE inv_mwb_globals.g_tree_doc_type_id
3005 WHEN 1 THEN
3006 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.PO_HEADER_ID).column_value :=
3007 'ms.po_header_id';
3008 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.RELEASE_NUMBER).column_value :=
3009 'prl.release_num'; -- Bug 11694520
3010 inv_mwb_query_manager.add_from_clause('po_releases_all prl','INBOUND'); -- Bug 11694520
3011 inv_mwb_query_manager.add_group_clause('prl.release_num', 'INBOUND'); -- Bug 11694520
3012 inv_mwb_query_manager.add_group_clause('ms.po_header_id', 'INBOUND');
3013 inv_mwb_query_manager.add_where_clause('ms.supply_type_code = ''PO''', 'INBOUND');
3014 inv_mwb_query_manager.add_where_clause('ms.po_release_id = prl.po_release_id', 'INBOUND'); -- Bug 11694520
3015 inv_mwb_query_manager.add_where_clause('ms.po_header_id IS NOT NULL', 'INBOUND');
3016 inv_mwb_query_manager.add_where_clause('ms.to_organization_id = :inb_tree_organization_id', 'INBOUND');
3017 inv_mwb_query_manager.add_bind_variable('inb_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
3018 WHEN 2 THEN
3019 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.REQ_HEADER_ID).column_value :=
3020 'ms.req_header_id';
3021 /* Bug 11716231 */
3022 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.OWNING_ORG_ID).column_value :=
3023 'ms.intransit_owning_org_id';
3024 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.EXPECTED_RECEIPT_DATE).column_value :=
3025 'ms.expected_delivery_date';
3026 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.FROM_ORG_ID).column_value :=
3027 'ms.from_organization_id';
3028 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.TO_ORG_ID).column_value :=
3029 'ms.to_organization_id';
3030
3031 inv_mwb_query_manager.add_group_clause('ms.intransit_owning_org_id', 'INBOUND');
3032 inv_mwb_query_manager.add_group_clause('ms.expected_delivery_date', 'INBOUND');
3033 inv_mwb_query_manager.add_group_clause('ms.from_organization_id', 'INBOUND');
3034 inv_mwb_query_manager.add_group_clause('ms.to_organization_id', 'INBOUND');
3035 /* Bug 11716231 */
3036 inv_mwb_query_manager.add_group_clause('ms.req_header_id', 'INBOUND');
3037 inv_mwb_query_manager.add_where_clause('ms.supply_type_code IN (''REQ'',''SHIPMENT'')', 'INBOUND');
3038 inv_mwb_query_manager.add_where_clause('ms.req_header_id is not null', 'INBOUND');
3039 inv_mwb_query_manager.add_where_clause('ms.to_organization_id = :inb_tree_organization_id', 'INBOUND');
3040 inv_mwb_query_manager.add_bind_variable('inb_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
3041 WHEN 3 THEN
3042 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.SHIPMENT_HEADER_ID_ASN).column_value :=
3043 'ms.shipment_header_id';
3044 inv_mwb_query_manager.add_group_clause('ms.shipment_header_id', 'INBOUND');
3045 inv_mwb_query_manager.add_where_clause('ms.supply_type_code = ''SHIPMENT''', 'INBOUND');
3046 inv_mwb_query_manager.add_where_clause('ms.req_header_id IS NULL', 'INBOUND');
3047 inv_mwb_query_manager.add_where_clause('rsh.ASN_TYPE IS NULL', 'INBOUND');
3048 inv_mwb_query_manager.add_where_clause('rsh.RECEIPT_SOURCE_CODE = ''INVENTORY''', 'INBOUND');
3049 WHEN 4 THEN
3050 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.SHIPMENT_HEADER_ID_ASN).column_value :=
3051 'ms.shipment_header_id';
3052 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.VENDOR_ID).column_value :=
3053 'rsh.vendor_id';
3054 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.VENDOR_SITE_ID).column_value :=
3055 'rsh.vendor_site_id';
3056 inv_mwb_query_manager.add_group_clause('rsh.vendor_id', 'INBOUND');
3057 inv_mwb_query_manager.add_group_clause('rsh.vendor_site_id', 'INBOUND');
3058 inv_mwb_query_manager.add_group_clause('ms.shipment_header_id', 'INBOUND');
3059 inv_mwb_query_manager.add_where_clause('ms.supply_type_code = ''SHIPMENT''', 'INBOUND');
3060 inv_mwb_query_manager.add_where_clause('rsh.ASN_TYPE IS NOT NULL', 'INBOUND');
3061 inv_mwb_query_manager.add_where_clause('rsh.RECEIPT_SOURCE_CODE = ''VENDOR''', 'INBOUND');
3062 END CASE;
3063 inv_mwb_query_manager.execute_query;
3064 END IF;
3065 END doc_type_node_event;
3066
3067 PROCEDURE doc_num_node_event(
3068 x_node_value IN OUT NOCOPY NUMBER
3069 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
3070 , x_tbl_index IN OUT NOCOPY NUMBER
3071 )
3072 IS
3073 l_procedure_name VARCHAR2(30);
3074 l_po_header_id NUMBER;
3075 l_req_header_id NUMBER;
3076 l_shipment_header_id NUMBER;
3077 BEGIN
3078 l_procedure_name := 'DOC_NUM_NODE_EVENT';
3079 inv_mwb_globals.print_msg(g_pkg_name, l_procedure_name, 'Entered');
3080 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_EXPANDED' THEN
3081 IF (inv_mwb_globals.g_tree_doc_type_id <> 1) THEN
3082 inv_mwb_tree1.add_lpns(
3083 x_node_value
3084 , x_node_tbl
3085 , x_tbl_index
3086 );
3087 END IF;
3088
3089 IF inv_mwb_globals.g_lpn_from IS NULL
3090 AND inv_mwb_globals.g_lpn_to IS NULL THEN
3091 inv_mwb_tree1.add_items(
3092 x_node_value
3093 , x_node_tbl
3094 , x_tbl_index
3095 );
3096 END IF;
3097
3098 ELSIF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
3099
3100 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.OWNING_ORG_ID).column_value :=
3101 'ms.intransit_owning_org_id';
3102 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.EXPECTED_RECEIPT_DATE).column_value :=
3103 'ms.expected_delivery_date';
3104 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.FROM_ORG_ID).column_value :=
3105 'ms.from_organization_id';
3106 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.TO_ORG_ID).column_value :=
3107 'ms.to_organization_id'; -- Bug 11716231
3108 inv_mwb_query_manager.add_group_clause('ms.intransit_owning_org_id', 'INBOUND');
3109 inv_mwb_query_manager.add_group_clause('ms.expected_delivery_date', 'INBOUND');
3110 inv_mwb_query_manager.add_group_clause('ms.from_organization_id', 'INBOUND');
3111 inv_mwb_query_manager.add_group_clause('ms.to_organization_id', 'INBOUND'); -- Bug 11716231
3112 make_common_query_inbound('INBOUND');
3113 inv_mwb_query_manager.add_qf_where_inbound('INBOUND');
3114
3115 CASE inv_mwb_globals.g_tree_doc_type_id
3116 WHEN 1 THEN
3117 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.DOCUMENT_NUMBER).column_value :=
3118 inv_mwb_globals.g_tree_node_value;
3119 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.PO_HEADER_ID).column_value :=
3120 'ms.po_header_id';
3121 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.DOCUMENT_LINE_NUMBER).column_value :=
3122 'ms.po_line_id';
3123 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.PO_RELEASE_ID).column_value :=
3124 'ms.po_release_id';
3125 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.RELEASE_NUMBER).column_value :=
3126 'prl.release_num'; -- Bug 11932315
3127 inv_mwb_query_manager.add_from_clause('po_releases_all prl','INBOUND'); -- Bug 11932315
3128 inv_mwb_query_manager.add_where_clause('ms.po_release_id = prl.po_release_id', 'INBOUND'); -- Bug 11932315
3129
3130 inv_mwb_query_manager.add_group_clause('ms.po_release_id', 'INBOUND');
3131 inv_mwb_query_manager.add_group_clause('prl.release_num', 'INBOUND'); -- Bug 11932315
3132 inv_mwb_query_manager.add_group_clause('ms.po_header_id', 'INBOUND');
3133 inv_mwb_query_manager.add_group_clause('ms.po_line_id', 'INBOUND');
3134
3135 inv_mwb_query_manager.add_where_clause('ms.supply_type_code = ''PO''', 'INBOUND');
3136 inv_mwb_query_manager.add_where_clause('ms.to_organization_id = :inb_tree_organization_id', 'INBOUND');
3137 inv_mwb_query_manager.add_where_clause('ms.po_header_id = :po_header_id', 'INBOUND');
3138 inv_mwb_query_manager.add_bind_variable('inb_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
3139 inv_mwb_query_manager.add_bind_variable('po_header_id', inv_mwb_globals.g_tree_node_value);
3140
3141 WHEN 2 THEN
3142 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.REQ_HEADER_ID).column_value :=
3143 'ms.req_header_id';
3144 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.DOCUMENT_LINE_NUMBER).column_value :=
3145 'ms.req_line_id';
3146
3147 inv_mwb_query_manager.add_group_clause('ms.req_header_id', 'INBOUND');
3148 inv_mwb_query_manager.add_group_clause('ms.req_line_id', 'INBOUND');
3149 inv_mwb_query_manager.add_where_clause('ms.supply_type_code IN (''REQ'',''SHIPMENT'')', 'INBOUND');
3150 inv_mwb_query_manager.add_where_clause('ms.req_header_id = :req_header_id', 'INBOUND');
3151 inv_mwb_query_manager.add_where_clause('ms.to_organization_id = :inb_tree_organization_id', 'INBOUND');
3152 inv_mwb_query_manager.add_bind_variable('req_header_id', inv_mwb_globals.g_tree_node_value);
3153 inv_mwb_query_manager.add_bind_variable('inb_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
3154 WHEN 3 THEN
3155 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.SHIPMENT_HEADER_ID_ASN).column_value :=
3156 'ms.shipment_header_id';
3157 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.DOCUMENT_LINE_NUMBER).column_value :=
3158 'ms.shipment_line_id';
3159 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.LPN_ID).column_value :=
3160 'rsl.asn_lpn_id';
3161 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.OWNING_ORG_ID).column_value :=
3162 'ms.intransit_owning_org_id';
3163 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.SHIPPED_DATE).column_value :=
3164 'rsh.shipped_date';
3165
3166 inv_mwb_query_manager.add_group_clause('ms.shipment_header_id', 'INBOUND');
3167 inv_mwb_query_manager.add_group_clause('ms.shipment_line_id', 'INBOUND');
3168 inv_mwb_query_manager.add_group_clause('ms.intransit_owning_org_id', 'INBOUND');
3169 inv_mwb_query_manager.add_group_clause('rsl.asn_lpn_id', 'INBOUND');
3170 inv_mwb_query_manager.add_group_clause('rsh.shipped_date', 'INBOUND');
3171
3172 inv_mwb_query_manager.add_where_clause('ms.supply_type_code = ''SHIPMENT''', 'INBOUND');
3173 inv_mwb_query_manager.add_where_clause('rsh.ASN_TYPE IS NULL', 'INBOUND');
3174 inv_mwb_query_manager.add_where_clause('ms.shipment_header_id = :inb_shipment_header_id', 'INBOUND');
3175 inv_mwb_query_manager.add_bind_variable('inb_shipment_header_id', inv_mwb_globals.g_tree_doc_header_id);
3176
3177 WHEN 4 THEN
3178 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.SHIPMENT_HEADER_ID_ASN).column_value :=
3179 'ms.shipment_header_id';
3180 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.DOCUMENT_LINE_NUMBER).column_value :=
3181 'ms.shipment_line_id';
3182 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.VENDOR_ID).column_value :=
3183 'rsh.vendor_id';
3184 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.VENDOR_SITE_ID).column_value :=
3185 'rsh.vendor_site_id';
3186 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.SHIPPED_DATE).column_value :=
3187 'rsh.shipped_date';
3188 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.LPN_ID).column_value :=
3189 'rsl.asn_lpn_id';
3190
3191 inv_mwb_query_manager.add_group_clause('rsh.vendor_id', 'INBOUND');
3192 inv_mwb_query_manager.add_group_clause('rsh.vendor_site_id', 'INBOUND');
3193 inv_mwb_query_manager.add_group_clause('ms.shipment_header_id', 'INBOUND');
3194 inv_mwb_query_manager.add_group_clause('ms.shipment_line_id', 'INBOUND');
3195 inv_mwb_query_manager.add_group_clause('rsh.shipped_date', 'INBOUND');
3196 inv_mwb_query_manager.add_group_clause('rsl.asn_lpn_id', 'INBOUND');
3197
3198 inv_mwb_query_manager.add_where_clause('ms.supply_type_code = ''SHIPMENT''', 'INBOUND');
3199 inv_mwb_query_manager.add_where_clause('rsh.ASN_TYPE IS NOT NULL', 'INBOUND');
3200 inv_mwb_query_manager.add_where_clause('rsh.RECEIPT_SOURCE_CODE = ''VENDOR''', 'INBOUND');
3201 inv_mwb_query_manager.add_where_clause('ms.shipment_header_id = :shipment_num', 'INBOUND');
3202 inv_mwb_query_manager.add_bind_variable('shipment_num', inv_mwb_globals.g_tree_node_value);
3203
3204 END CASE;
3205 inv_mwb_query_manager.execute_query;
3206 END IF; -- Tree node selected /expanded.
3207 END doc_num_node_event;
3208
3209 PROCEDURE make_common_query_onhand(p_flag VARCHAR2) IS
3210 l_procedure_name VARCHAR2(30);
3211 BEGIN
3212 l_procedure_name := 'MAKE_COMMON_QUERY_ONHAND';
3213 inv_mwb_globals.print_msg(g_pkg_name, l_procedure_name, 'Entered');
3214 IF(inv_mwb_globals.g_chk_onhand = 1) THEN
3215 CASE p_flag
3216 WHEN 'MSN' THEN
3217 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ITEM_ID).column_value :=
3218 'msn.inventory_item_id';
3219
3220 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ORG_ID).column_value :=
3221 'msn.current_organization_id';
3222
3223 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PRIMARY_UOM_CODE).column_value :=
3224 '''Ea''';
3225
3226 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ONHAND).column_value := 1;
3227
3228 inv_mwb_query_manager.add_from_clause('mtl_serial_numbers msn', 'ONHAND');
3229
3230 inv_mwb_query_manager.add_where_clause('msn.current_status = 3', 'ONHAND');
3231 inv_mwb_query_manager.add_group_clause('msn.current_organization_id', 'ONHAND');
3232 inv_mwb_query_manager.add_group_clause('msn.inventory_item_id', 'ONHAND');
3233 inv_mwb_query_manager.add_group_clause('''Ea''', 'ONHAND');
3234
3235 IF inv_mwb_globals.g_multiple_loc_selected = 'FALSE'
3236 OR inv_mwb_globals.g_tree_node_type <> 'APPTREE_OBJECT_TRUNK' THEN
3237
3238 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PACKED).column_value :=
3239 'DECODE(msn.lpn_id, NULL, 0,1)';
3240
3241 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.UNPACKED).column_value :=
3242 'DECODE(msn.lpn_id, NULL, 1,0)';
3243
3244 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SECONDARY_UOM_CODE).column_value :=
3245 'NULL';
3246
3247 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SECONDARY_PACKED).column_value :=
3248 'NULL';
3249
3250 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SECONDARY_UNPACKED).column_value :=
3251 'NULL';
3252
3253 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SERIAL).column_value :=
3254 'msn.serial_number';
3255
3256 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.owning_organization_id).column_value :=
3257 'msn.owning_organization_id';
3258
3259 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.planning_organization_id).column_value :=
3260 'msn.planning_organization_id';
3261
3262 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.owning_tp_type).column_value :=
3263 'msn.owning_tp_type';
3264
3265 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.planning_tp_type).column_value :=
3266 'msn.planning_tp_type';
3267
3268 inv_mwb_query_manager.add_group_clause('msn.serial_number', 'ONHAND');
3269 inv_mwb_query_manager.add_group_clause('msn.owning_organization_id', 'ONHAND');
3270 inv_mwb_query_manager.add_group_clause('msn.planning_organization_id', 'ONHAND');
3271 inv_mwb_query_manager.add_group_clause('msn.owning_tp_type', 'ONHAND');
3272 inv_mwb_query_manager.add_group_clause('msn.planning_tp_type', 'ONHAND');
3273
3274 END IF;
3275
3276
3277 WHEN 'MOQD' THEN
3278
3279 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ITEM_ID).column_value :=
3280 'moqd.inventory_item_id';
3281
3282 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ORG_ID).column_value :=
3283 'moqd.organization_id';
3284
3285 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ONHAND).column_value :=
3286 'SUM(moqd.primary_transaction_quantity)';
3287
3288 inv_mwb_query_manager.add_from_clause('mtl_onhand_quantities_detail moqd', 'ONHAND');
3289
3290 inv_mwb_query_manager.add_group_clause('moqd.organization_id', 'ONHAND');
3291 inv_mwb_query_manager.add_group_clause('moqd.inventory_item_id', 'ONHAND');
3292
3293 IF inv_mwb_globals.g_multiple_loc_selected = 'FALSE'
3294 OR inv_mwb_globals.g_tree_node_type <> 'APPTREE_OBJECT_TRUNK' THEN
3295
3296 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PACKED).column_value :=
3297 'SUM(DECODE(moqd.containerized_flag, 1, moqd.primary_transaction_quantity, 0))';
3298
3299 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.UNPACKED).column_value :=
3300 'SUM(DECODE(moqd.containerized_flag, 1, 0, moqd.primary_transaction_quantity))';
3301
3302 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SECONDARY_UOM_CODE).column_value :=
3303 'moqd.secondary_uom_code';
3304
3305 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.owning_organization_id).column_value :=
3306 'moqd.owning_organization_id';
3307
3308 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.planning_organization_id).column_value :=
3309 'moqd.planning_organization_id';
3310
3311 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.owning_tp_type).column_value :=
3312 'moqd.owning_tp_type';
3313
3314 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.planning_tp_type).column_value :=
3315 'moqd.planning_tp_type';
3316
3317 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SECONDARY_ONHAND).column_value :=
3318 'SUM(moqd.secondary_transaction_quantity)';
3319
3320 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SECONDARY_PACKED).column_value :=
3321 'SUM(DECODE(moqd.containerized_flag, 1, moqd.secondary_transaction_quantity, 0))';
3322
3323 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SECONDARY_UNPACKED).column_value :=
3324 'SUM(DECODE(moqd.containerized_flag, 1, 0, moqd.secondary_transaction_quantity))';
3325
3326 inv_mwb_query_manager.add_group_clause('moqd.secondary_uom_code', 'ONHAND');
3327 inv_mwb_query_manager.add_group_clause('moqd.owning_organization_id', 'ONHAND');
3328 inv_mwb_query_manager.add_group_clause('moqd.planning_organization_id', 'ONHAND');
3329 inv_mwb_query_manager.add_group_clause('moqd.owning_tp_type', 'ONHAND');
3330 inv_mwb_query_manager.add_group_clause('moqd.planning_tp_type', 'ONHAND');
3331
3332 END IF;
3333
3334 WHEN 'MSN_QUERY' THEN
3335
3336
3337 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ITEM_ID).column_value :=
3338 'msn.inventory_item_id';
3339
3340 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ORG_ID).column_value :=
3341 'msn.current_organization_id';
3342
3343 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PRIMARY_UOM_CODE).column_value :=
3344 '''Ea''';
3345
3346 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ONHAND).column_value :=
3347 'count(1)';
3348
3349 inv_mwb_query_manager.add_group_clause('msn.inventory_item_id' , 'ONHAND');
3350 inv_mwb_query_manager.add_group_clause('msn.current_organization_id', 'ONHAND');
3351 inv_mwb_query_manager.add_group_clause('''Ea''', 'ONHAND');
3352
3353 inv_mwb_query_manager.add_from_clause('mtl_serial_numbers msn', 'ONHAND');
3354 inv_mwb_query_manager.add_where_clause('msn.current_status = 3', 'ONHAND');
3355
3356 IF inv_mwb_globals.g_multiple_loc_selected = 'FALSE'
3357 OR inv_mwb_globals.g_tree_node_type <> 'APPTREE_OBJECT_TRUNK' THEN
3358 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PACKED).column_value :=
3359 'sum(decode(msn.lpn_id,NULL,0, 1))';
3360 inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.UNPACKED).column_value :=
3361 'sum(decode(msn.lpn_id,NULL,1, 0))';
3362 END IF;
3363
3364 END CASE; -- p_flag
3365 END IF; -- End if for onhand
3366 END make_common_query_onhand;
3367
3368 PROCEDURE make_common_query_inbound(p_flag VARCHAR2) IS
3369 l_procedure_name VARCHAR2(30);
3370 l_lot_control NUMBER;
3371 BEGIN
3372 l_procedure_name := 'MAKE_COMMON_QUERY_INBOUND';
3373 inv_mwb_globals.print_msg(g_pkg_name, l_procedure_name, 'Entered');
3374 IF(inv_mwb_globals.g_chk_inbound = 1 AND p_flag = 'INBOUND') THEN
3375
3376 IF inv_mwb_globals.g_tree_item_id IS NOT NULL THEN
3377 SELECT lot_control_code
3378 INTO l_lot_control
3379 FROM mtl_system_items
3380 WHERE inventory_item_id = inv_mwb_globals.g_tree_item_id
3381 AND organization_id = inv_mwb_globals.g_tree_organization_id;
3382 END IF;
3383
3384 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.ITEM_ID).column_value :=
3385 'ms.item_id';
3386 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.ORG_ID).column_value :=
3387 'ms.to_organization_id';
3388 -- for bug 8420731
3389 -- for bug 8420783
3390 -- for bug 8414727
3391 IF (inv_mwb_globals.g_serial_from IS NOT NULl or inv_mwb_globals.g_serial_to is NOT NULL) THEN
3392 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.INBOUND).column_value :=
3393 'COUNT(1)';
3394 ELSIF(inv_mwb_globals.g_lot_from IS NOT NULl or inv_mwb_globals.g_lot_to is NOT NULL ) THEN
3395 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.INBOUND).column_value :=
3396 'SUM(rls.quantity)';
3397 ELSE
3398 inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.INBOUND).column_value :=
3399 'SUM(ms.to_org_primary_quantity)';
3400 END IF;
3401 -- end of bug 8414727
3402 -- end of bug 8420783
3403 -- end of bug 8420731
3404 --bug 4761399
3405 -- inv_mwb_query_manager.g_inbound_select(inv_mwb_query_manager.CG_ID).column_value :=
3406 -- 'ms.cost_group_id';
3407
3408 inv_mwb_query_manager.add_group_clause('ms.item_id','INBOUND');
3409 inv_mwb_query_manager.add_group_clause('ms.to_organization_id','INBOUND');
3410 -- inv_mwb_query_manager.add_group_clause('ms.cost_group_id', 'INBOUND');
3411 inv_mwb_query_manager.add_where_clause('ms.destination_type_code = ''INVENTORY''', 'INBOUND');
3412 inv_mwb_query_manager.add_where_clause('ms.supply_type_code <> ''RECEIVING''', 'INBOUND');
3413
3414 END IF;
3415 END make_common_query_inbound;
3416
3417 PROCEDURE make_common_query_receiving(p_flag VARCHAR2) IS
3418 l_procedure_name VARCHAR2(30);
3419 BEGIN
3420 l_procedure_name := 'MAKE_COMMON_QUERY_RECEIVING';
3421 inv_mwb_globals.print_msg(g_pkg_name, l_procedure_name, 'Entered');
3422 inv_mwb_globals.print_msg(g_pkg_name, l_procedure_name, 'p_flag : ' || p_flag);
3423 IF(inv_mwb_globals.g_chk_receiving = 1) THEN
3424 IF p_flag = 'RECEIVING' THEN
3425 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.ORG_ID).column_value :=
3426 'rs.to_organization_id';
3427 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.ITEM_ID).column_value :=
3428 'rs.item_id';
3429 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.RECEIVING).column_value :=
3430 'SUM(rs.to_org_primary_quantity)';
3431 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SECONDARY_RECEIVING).column_value :=
3432 'SUM(rs.secondary_quantity)'; -- 8687440
3433
3434 inv_mwb_query_manager.add_group_clause('rs.to_organization_id','RECEIVING');
3435 inv_mwb_query_manager.add_group_clause('rs.item_id','RECEIVING');
3436
3437 IF inv_mwb_globals.g_multiple_loc_selected = 'FALSE'
3438 OR inv_mwb_globals.g_tree_node_type <> 'APPTREE_OBJECT_TRUNK' THEN
3439 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.UNPACKED).column_value :=
3440 'SUM(DECODE (rs.lpn_id, null, rs.to_org_primary_quantity, 0))';
3441 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.PACKED).column_value :=
3442 'SUM(DECODE (rs.lpn_id, null, 0, rs.to_org_primary_quantity))';
3443 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SECONDARY_UNPACKED).column_value :=
3444 'SUM(DECODE (rs.lpn_id, null, rs.secondary_quantity, 0))'; -- 8687440
3445 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SECONDARY_PACKED).column_value :=
3446 'SUM(DECODE (rs.lpn_id, null, 0, rs.secondary_quantity))'; -- 8687440
3447 END IF;
3448
3449 ELSIF p_flag = 'RCV_TREE_LPN' THEN
3450 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.ORG_ID).column_value :=
3451 'wlpn.organization_id';
3452 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.ITEM_ID).column_value :=
3453 'wlc.inventory_item_id';
3454 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.PRIMARY_UOM_CODE).column_value :=
3455 'wlc.uom_code';
3456 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.RECEIVING).column_value :=
3457 'SUM(wlc.primary_quantity)';--bug 4761399
3458
3459 inv_mwb_query_manager.add_group_clause('wlpn.organization_id','RECEIVING');
3460 inv_mwb_query_manager.add_group_clause('wlc.inventory_item_id','RECEIVING');
3461 inv_mwb_query_manager.add_group_clause('wlc.uom_code','RECEIVING');
3462
3463 IF inv_mwb_globals.g_multiple_loc_selected = 'FALSE'
3464 OR inv_mwb_globals.g_tree_node_type <> 'APPTREE_OBJECT_TRUNK' THEN
3465 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.SECONDARY_UOM_CODE).column_value :=
3466 'wlc.secondary_uom_code';
3467 inv_mwb_query_manager.add_group_clause('wlc.secondary_uom_code','RECEIVING');
3468 END IF;
3469
3470 ELSIF p_flag = 'MSN' THEN
3471
3472 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.ITEM_ID).column_value :=
3473 'rs.item_id';
3474 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.ORG_ID).column_value :=
3475 'rs.to_organization_id';
3476 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.PRIMARY_UOM_CODE).column_value :=
3477 '''Ea''';
3478 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.receiving).column_value :=
3479 'count(1)';
3480
3481 inv_mwb_query_manager.add_group_clause('rs.to_organization_id', 'RECEIVING');
3482 inv_mwb_query_manager.add_group_clause('rs.item_id', 'RECEIVING');
3483 inv_mwb_query_manager.add_group_clause('''Ea''', 'RECEIVING');
3484
3485 IF inv_mwb_globals.g_multiple_loc_selected = 'FALSE'
3486 OR inv_mwb_globals.g_tree_node_type <> 'APPTREE_OBJECT_TRUNK' THEN
3487 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.PACKED).column_value :=
3488 'SUM(DECODE(rs.lpn_id, NULL, 0,1))'; -- Bug 9865190 added SUM fnc
3489 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.UNPACKED).column_value :=
3490 'SUM(DECODE(rs.lpn_id, NULL, 1,0))'; -- Bug 9865190 added SUM fnc
3491 END IF;
3492
3493 ELSIF p_flag = 'MSN_QUERY' THEN
3494 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.ITEM_ID).column_value :=
3495 'rs.item_id';
3496 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.ORG_ID).column_value :=
3497 'rs.to_organization_id';
3498 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.PRIMARY_UOM_CODE).column_value :=
3499 '''Ea''';
3500 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.receiving).column_value :=
3501 'count(1)';
3502 inv_mwb_query_manager.add_group_clause('rs.item_id' , 'RECEIVING');
3503 inv_mwb_query_manager.add_group_clause('rs.to_organization_id', 'RECEIVING');
3504 inv_mwb_query_manager.add_group_clause('''Ea''', 'RECEIVING');
3505
3506 IF inv_mwb_globals.g_multiple_loc_selected = 'FALSE'
3507 OR inv_mwb_globals.g_tree_node_type <> 'APPTREE_OBJECT_TRUNK' THEN
3508 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.PACKED).column_value :=
3509 'sum(decode(rs.lpn_id,NULL,0, 1))';
3510 inv_mwb_query_manager.g_receiving_select(inv_mwb_query_manager.UNPACKED).column_value :=
3511 'sum(decode(rs.lpn_id,NULL,1, 0))';
3512 END IF;
3513 END IF;
3514 END IF; -- End if for receiving
3515 END make_common_query_receiving;
3516
3517 PROCEDURE event (
3518 x_node_value IN OUT NOCOPY NUMBER
3519 ,x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
3520 ,x_tbl_index IN OUT NOCOPY NUMBER
3521 ) IS
3522
3523 l_procedure_name VARCHAR2(30);
3524
3525 BEGIN
3526
3527 l_procedure_name := 'EVENT';
3528 inv_mwb_globals.print_msg(g_pkg_name, l_procedure_name, 'Entered EVENT with node_type :'||inv_mwb_globals.g_tree_node_type);
3529
3530 IF inv_mwb_globals.g_tree_node_type = 'MATLOC' THEN
3531 inv_mwb_globals.g_inserted_under_org := 'Y';
3532 ELSE
3533 inv_mwb_globals.g_inserted_under_org := 'N';
3534 END IF;
3535
3536 CASE inv_mwb_globals.g_tree_node_type
3537
3538 WHEN 'APPTREE_OBJECT_TRUNK' THEN
3539
3540 root_node_event(
3541 x_node_value
3542 ,x_node_tbl
3543 ,x_tbl_index
3544 );
3545
3546 WHEN 'ORG' THEN
3547
3548 org_node_event(
3549 x_node_value
3550 ,x_node_tbl
3551 ,x_tbl_index
3552 );
3553
3554 WHEN 'MATLOC' THEN
3555
3556 matloc_node_event(
3557 x_node_value
3558 ,x_node_tbl
3559 ,x_tbl_index
3560 );
3561
3562 WHEN 'DOCTYPE' THEN
3563
3564 doc_type_node_event(
3565 x_node_value
3566 ,x_node_tbl
3567 ,x_tbl_index
3568 );
3569
3570 WHEN 'DOCNUM' THEN
3571
3572 doc_num_node_event(
3573 x_node_value
3574 ,x_node_tbl
3575 ,x_tbl_index
3576 );
3577
3578 WHEN 'SUB' THEN
3579
3580 sub_node_event(
3581 x_node_value
3582 ,x_node_tbl
3583 ,x_tbl_index
3584 );
3585
3586 WHEN 'LOC' THEN
3587
3588 loc_node_event(
3589 x_node_value
3590 ,x_node_tbl
3591 ,x_tbl_index
3592 );
3593
3594 WHEN 'ITEM' THEN
3595
3596 item_node_event(
3597 x_node_value
3598 ,x_node_tbl
3599 ,x_tbl_index
3600 );
3601
3602 WHEN 'REV' THEN
3603
3604 IF NVL(inv_mwb_globals.g_tree_doc_type_id,-99) <> 1 THEN
3605 rev_node_event(
3606 x_node_value
3607 ,x_node_tbl
3608 ,x_tbl_index
3609 );
3610 END IF;
3611
3612 WHEN 'LPN' THEN
3613
3614 IF NVL(inv_mwb_globals.g_tree_doc_type_id,-99) <> 1 THEN
3615 lpn_node_event(
3616 x_node_value
3617 ,x_node_tbl
3618 ,x_tbl_index
3619 );
3620 END IF;
3621
3622 WHEN 'LOT' THEN
3623
3624 IF NVL(inv_mwb_globals.g_tree_doc_type_id,-99) <> 1 THEN
3625 lot_node_event(
3626 x_node_value
3627 ,x_node_tbl
3628 ,x_tbl_index
3629 );
3630 END IF;
3631
3632 WHEN 'SERIAL' THEN
3633
3634 IF NVL(inv_mwb_globals.g_tree_doc_type_id,-99) <> 1 THEN
3635 serial_node_event(
3636 x_node_value
3637 ,x_node_tbl
3638 ,x_tbl_index
3639 );
3640 END IF;
3641
3642 END CASE;
3643
3644 END event;
3645
3646 END inv_mwb_location_tree;