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