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:
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
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
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
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:
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');
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:
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
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';
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';
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:
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');
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
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');
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';
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
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
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
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;
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);
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);
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);
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
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)
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
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');
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 :=
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';
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');
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');
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');
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 :=
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';
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';
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:
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');
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
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;
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)
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
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
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
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
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;
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
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
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;
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:
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
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
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
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
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
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');
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 :=
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';
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 :=
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
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');
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;
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:
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');
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 :=
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';
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 :=
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';
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;
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;
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
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;
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:
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
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
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
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');
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:
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 :=
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';
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');
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
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 :=
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';
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 :=
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');
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 :=
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';
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');
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 :=
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';
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');
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;
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;
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:
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;
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;
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:
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(
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
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
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;
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
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:
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');
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:
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 :=
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:
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');
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
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');
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:
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 :=
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';
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 :=
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 :=
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');
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 :=
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';
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');
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');
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');
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';
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
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');
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:
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:
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');
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 :=
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';
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');
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);
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:
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');
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:
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');
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:
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');
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:
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');
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:
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');
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');
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;
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;
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
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
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;
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;
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:
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
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
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(
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
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
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:
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(
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
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
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:
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
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
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
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
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 :=
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';
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: );
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: );
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: );
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
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:
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: );
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: );
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: );
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: );
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
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: );
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
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: );
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
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');
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
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 :=
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';
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
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:
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:
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');
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 :=
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';
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:
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
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:
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
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:
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 :=
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 :=
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';
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);
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;
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 :=
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';
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);
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 :=
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');
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:
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');
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:
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');
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:
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)
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
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
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;
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');
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;
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
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;
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;
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
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;
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;
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;
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
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
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(
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
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
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;
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
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;
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
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:
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');
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:
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
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');
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);
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');
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:
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');
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:
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');
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
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:
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');
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:
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');
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:
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:
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;
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;
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:
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');
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;
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';
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;
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;
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
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');
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:
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');
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);
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 :=
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');
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');
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);
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 :=
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:
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
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';
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
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);
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
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);
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:
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');
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
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)
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
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
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;
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
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;
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;
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;
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;
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
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;
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;
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;
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
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
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)
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
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 :=
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 :=
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');
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:
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:
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');
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');
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:
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:
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');
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
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');
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;
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);
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
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;
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:
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');
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');
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:
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');
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:
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:
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');
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;
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
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
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');
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 :=
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';
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:
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);
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:
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');
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');
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
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);
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
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);
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;
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:
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);
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
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:
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');
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:
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);
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');
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:
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 :=
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);
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
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:
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');
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:
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);
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:
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 :=
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
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);
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
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);
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
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);
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:
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');
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)
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
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');
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');
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;
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 :=
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';
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 :=
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:
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');
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:
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');
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:
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');
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:
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');
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:
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');
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:
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;
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 :=
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 :=
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);
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
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:
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);
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:
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
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:
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
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);
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
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);
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:
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');
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:
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');
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:
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');
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;
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;
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;
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'
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(
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
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
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:
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
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;
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
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
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';
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;
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:
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
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
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
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;
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:
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
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;
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);
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);
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;
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
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
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
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');
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 :=
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';
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');
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');
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';
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;
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
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
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
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
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 :=
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');
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');
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
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
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
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
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
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 :=
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 :=
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';
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 :=
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';
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';
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 :=
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';
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.
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 :=
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';
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)';
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:
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))';
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:
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 :=
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))';
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
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
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
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 :=
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';
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 :=
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';
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 :=
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 :=
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))';
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');
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;
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 :=
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)';
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 :=
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))';
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
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;
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:
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:
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(
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
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
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
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