29:
30: ELSIF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
31:
32: make_common_queries('MSN_QUERY');
33: inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
34: inv_mwb_query_manager.execute_query;
35:
36: END IF; -- tree event
37:
30: ELSIF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
31:
32: make_common_queries('MSN_QUERY');
33: inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
34: inv_mwb_query_manager.execute_query;
35:
36: END IF; -- tree event
37:
38: EXCEPTION
61:
62: ELSIF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
63:
64: make_common_queries('MSN');
65: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SERIAL).column_value :=
66: 'msn.serial_number';
67: inv_mwb_query_manager.add_where_clause(
68: 'msn.current_organization_id = :onh_tree_organization_id' ,
69: 'ONHAND'
63:
64: make_common_queries('MSN');
65: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SERIAL).column_value :=
66: 'msn.serial_number';
67: inv_mwb_query_manager.add_where_clause(
68: 'msn.current_organization_id = :onh_tree_organization_id' ,
69: 'ONHAND'
70: );
71: inv_mwb_query_manager.add_bind_variable(
67: inv_mwb_query_manager.add_where_clause(
68: 'msn.current_organization_id = :onh_tree_organization_id' ,
69: 'ONHAND'
70: );
71: inv_mwb_query_manager.add_bind_variable(
72: 'onh_tree_organization_id' ,
73: inv_mwb_globals.g_tree_organization_id
74: );
75: inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
71: inv_mwb_query_manager.add_bind_variable(
72: 'onh_tree_organization_id' ,
73: inv_mwb_globals.g_tree_organization_id
74: );
75: inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
76: inv_mwb_query_manager.execute_query;
77:
78: END IF; -- g_tree_event
79:
72: 'onh_tree_organization_id' ,
73: inv_mwb_globals.g_tree_organization_id
74: );
75: inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
76: inv_mwb_query_manager.execute_query;
77:
78: END IF; -- g_tree_event
79:
80: EXCEPTION
103:
104: ELSIF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
105:
106: make_common_queries('MSN');
107: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SERIAL).column_value :=
108: 'msn.serial_number';
109: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ITEM_ID).column_value :=
110: 'msn.inventory_item_id';
111: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
105:
106: make_common_queries('MSN');
107: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SERIAL).column_value :=
108: 'msn.serial_number';
109: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ITEM_ID).column_value :=
110: 'msn.inventory_item_id';
111: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
112: 'msn.current_subinventory_code';
113: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
107: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SERIAL).column_value :=
108: 'msn.serial_number';
109: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ITEM_ID).column_value :=
110: 'msn.inventory_item_id';
111: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
112: 'msn.current_subinventory_code';
113: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
114: 'msn.current_locator_id';
115: inv_mwb_query_manager.add_where_clause(
109: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ITEM_ID).column_value :=
110: 'msn.inventory_item_id';
111: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
112: 'msn.current_subinventory_code';
113: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
114: 'msn.current_locator_id';
115: inv_mwb_query_manager.add_where_clause(
116: 'msn.current_organization_id = :onh_tree_organization_id' ,
117: 'ONHAND'
111: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
112: 'msn.current_subinventory_code';
113: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
114: 'msn.current_locator_id';
115: inv_mwb_query_manager.add_where_clause(
116: 'msn.current_organization_id = :onh_tree_organization_id' ,
117: 'ONHAND'
118: );
119: inv_mwb_query_manager.add_where_clause(
115: inv_mwb_query_manager.add_where_clause(
116: 'msn.current_organization_id = :onh_tree_organization_id' ,
117: 'ONHAND'
118: );
119: inv_mwb_query_manager.add_where_clause(
120: 'msn.serial_number = :onh_tree_serial_number' ,
121: 'ONHAND'
122: );
123: inv_mwb_query_manager.add_bind_variable(
119: inv_mwb_query_manager.add_where_clause(
120: 'msn.serial_number = :onh_tree_serial_number' ,
121: 'ONHAND'
122: );
123: inv_mwb_query_manager.add_bind_variable(
124: 'onh_tree_organization_id' ,
125: inv_mwb_globals.g_tree_organization_id
126: );
127: inv_mwb_query_manager.add_bind_variable(
123: inv_mwb_query_manager.add_bind_variable(
124: 'onh_tree_organization_id' ,
125: inv_mwb_globals.g_tree_organization_id
126: );
127: inv_mwb_query_manager.add_bind_variable(
128: 'onh_tree_serial_number' ,
129: inv_mwb_globals.g_tree_serial_number
130: );
131: inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
127: inv_mwb_query_manager.add_bind_variable(
128: 'onh_tree_serial_number' ,
129: inv_mwb_globals.g_tree_serial_number
130: );
131: inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
132: inv_mwb_query_manager.execute_query;
133:
134: END IF; -- g_tree_event
135:
128: 'onh_tree_serial_number' ,
129: inv_mwb_globals.g_tree_serial_number
130: );
131: inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
132: inv_mwb_query_manager.execute_query;
133:
134: END IF; -- g_tree_event
135:
136: EXCEPTION
176: WHERE organization_id = inv_mwb_globals.g_tree_organization_id
177: AND inventory_item_id = inv_mwb_globals.g_tree_item_id;
178:
179: make_common_queries('MSN');
180: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SERIAL).column_value :=
181: 'msn.serial_number';
182: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ITEM_ID).column_value :=
183: 'msn.inventory_item_id';
184: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
178:
179: make_common_queries('MSN');
180: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SERIAL).column_value :=
181: 'msn.serial_number';
182: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ITEM_ID).column_value :=
183: 'msn.inventory_item_id';
184: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
185: 'msn.current_subinventory_code';
186: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
180: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SERIAL).column_value :=
181: 'msn.serial_number';
182: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ITEM_ID).column_value :=
183: 'msn.inventory_item_id';
184: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
185: 'msn.current_subinventory_code';
186: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
187: 'msn.current_locator_id';
188: inv_mwb_query_manager.add_where_clause(
182: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ITEM_ID).column_value :=
183: 'msn.inventory_item_id';
184: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
185: 'msn.current_subinventory_code';
186: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
187: 'msn.current_locator_id';
188: inv_mwb_query_manager.add_where_clause(
189: 'msn.current_organization_id = :onh_tree_organization_id' ,
190: 'ONHAND'
184: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
185: 'msn.current_subinventory_code';
186: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
187: 'msn.current_locator_id';
188: inv_mwb_query_manager.add_where_clause(
189: 'msn.current_organization_id = :onh_tree_organization_id' ,
190: 'ONHAND'
191: );
192: inv_mwb_query_manager.add_where_clause(
188: inv_mwb_query_manager.add_where_clause(
189: 'msn.current_organization_id = :onh_tree_organization_id' ,
190: 'ONHAND'
191: );
192: inv_mwb_query_manager.add_where_clause(
193: 'msn.serial_number = :onh_tree_serial_number' ,
194: 'ONHAND'
195: );
196: inv_mwb_query_manager.add_where_clause(
192: inv_mwb_query_manager.add_where_clause(
193: 'msn.serial_number = :onh_tree_serial_number' ,
194: 'ONHAND'
195: );
196: inv_mwb_query_manager.add_where_clause(
197: 'msn.inventory_item_id = :onh_tree_inventory_item_id' ,
198: 'ONHAND'
199: );
200: inv_mwb_query_manager.add_bind_variable(
196: inv_mwb_query_manager.add_where_clause(
197: 'msn.inventory_item_id = :onh_tree_inventory_item_id' ,
198: 'ONHAND'
199: );
200: inv_mwb_query_manager.add_bind_variable(
201: 'onh_tree_organization_id' ,
202: inv_mwb_globals.g_tree_organization_id
203: );
204: inv_mwb_query_manager.add_bind_variable(
200: inv_mwb_query_manager.add_bind_variable(
201: 'onh_tree_organization_id' ,
202: inv_mwb_globals.g_tree_organization_id
203: );
204: inv_mwb_query_manager.add_bind_variable(
205: 'onh_tree_serial_number' ,
206: inv_mwb_globals.g_tree_serial_number
207: );
208: inv_mwb_query_manager.add_bind_variable(
204: inv_mwb_query_manager.add_bind_variable(
205: 'onh_tree_serial_number' ,
206: inv_mwb_globals.g_tree_serial_number
207: );
208: inv_mwb_query_manager.add_bind_variable(
209: 'onh_tree_inventory_item_id' ,
210: inv_mwb_globals.g_tree_item_id
211: );
212:
210: inv_mwb_globals.g_tree_item_id
211: );
212:
213: IF l_rev_control = 1 AND l_lot_control = 2 THEN
214: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
215: 'msn.lot_number';
216: END IF;
217: IF l_rev_control = 2 THEN
218: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
214: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
215: 'msn.lot_number';
216: END IF;
217: IF l_rev_control = 2 THEN
218: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
219: 'msn.revision';
220: END IF;
221: inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
222: inv_mwb_query_manager.execute_query;
217: IF l_rev_control = 2 THEN
218: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
219: 'msn.revision';
220: END IF;
221: inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
222: inv_mwb_query_manager.execute_query;
223:
224: END IF;
225:
218: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
219: 'msn.revision';
220: END IF;
221: inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
222: inv_mwb_query_manager.execute_query;
223:
224: END IF;
225:
226: EXCEPTION
256: WHERE organization_id = inv_mwb_globals.g_tree_organization_id
257: AND inventory_item_id = inv_mwb_globals.g_tree_item_id;
258:
259: make_common_queries('MSN');
260: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SERIAL).column_value :=
261: 'msn.serial_number';
262: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ITEM_ID).column_value :=
263: 'msn.inventory_item_id';
264: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
258:
259: make_common_queries('MSN');
260: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SERIAL).column_value :=
261: 'msn.serial_number';
262: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ITEM_ID).column_value :=
263: 'msn.inventory_item_id';
264: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
265: 'msn.current_subinventory_code';
266: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
260: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SERIAL).column_value :=
261: 'msn.serial_number';
262: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ITEM_ID).column_value :=
263: 'msn.inventory_item_id';
264: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
265: 'msn.current_subinventory_code';
266: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
267: 'msn.current_locator_id';
268: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
262: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ITEM_ID).column_value :=
263: 'msn.inventory_item_id';
264: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
265: 'msn.current_subinventory_code';
266: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
267: 'msn.current_locator_id';
268: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
269: 'msn.revision';
270: inv_mwb_query_manager.add_where_clause(
264: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
265: 'msn.current_subinventory_code';
266: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
267: 'msn.current_locator_id';
268: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
269: 'msn.revision';
270: inv_mwb_query_manager.add_where_clause(
271: 'msn.current_organization_id = :onh_tree_organization_id' ,
272: 'ONHAND'
266: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
267: 'msn.current_locator_id';
268: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
269: 'msn.revision';
270: inv_mwb_query_manager.add_where_clause(
271: 'msn.current_organization_id = :onh_tree_organization_id' ,
272: 'ONHAND'
273: );
274: inv_mwb_query_manager.add_where_clause(
270: inv_mwb_query_manager.add_where_clause(
271: 'msn.current_organization_id = :onh_tree_organization_id' ,
272: 'ONHAND'
273: );
274: inv_mwb_query_manager.add_where_clause(
275: 'msn.serial_number = :onh_tree_serial_number' ,
276: 'ONHAND'
277: );
278: inv_mwb_query_manager.add_where_clause(
274: inv_mwb_query_manager.add_where_clause(
275: 'msn.serial_number = :onh_tree_serial_number' ,
276: 'ONHAND'
277: );
278: inv_mwb_query_manager.add_where_clause(
279: 'msn.inventory_item_id = :onh_tree_inventory_item_id' ,
280: 'ONHAND'
281: );
282: inv_mwb_query_manager.add_where_clause(
278: inv_mwb_query_manager.add_where_clause(
279: 'msn.inventory_item_id = :onh_tree_inventory_item_id' ,
280: 'ONHAND'
281: );
282: inv_mwb_query_manager.add_where_clause(
283: 'msn.revision = :onh_tree_revision' ,
284: 'ONHAND'
285: );
286: inv_mwb_query_manager.add_bind_variable(
282: inv_mwb_query_manager.add_where_clause(
283: 'msn.revision = :onh_tree_revision' ,
284: 'ONHAND'
285: );
286: inv_mwb_query_manager.add_bind_variable(
287: 'onh_tree_organization_id' ,
288: inv_mwb_globals.g_tree_organization_id
289: );
290: inv_mwb_query_manager.add_bind_variable(
286: inv_mwb_query_manager.add_bind_variable(
287: 'onh_tree_organization_id' ,
288: inv_mwb_globals.g_tree_organization_id
289: );
290: inv_mwb_query_manager.add_bind_variable(
291: 'onh_tree_serial_number' ,
292: inv_mwb_globals.g_tree_serial_number
293: );
294: inv_mwb_query_manager.add_bind_variable(
290: inv_mwb_query_manager.add_bind_variable(
291: 'onh_tree_serial_number' ,
292: inv_mwb_globals.g_tree_serial_number
293: );
294: inv_mwb_query_manager.add_bind_variable(
295: 'onh_tree_inventory_item_id' ,
296: inv_mwb_globals.g_tree_item_id
297: );
298: inv_mwb_query_manager.add_bind_variable(
294: inv_mwb_query_manager.add_bind_variable(
295: 'onh_tree_inventory_item_id' ,
296: inv_mwb_globals.g_tree_item_id
297: );
298: inv_mwb_query_manager.add_bind_variable(
299: 'onh_tree_revision' ,
300: inv_mwb_globals.g_tree_rev
301: );
302:
300: inv_mwb_globals.g_tree_rev
301: );
302:
303: IF l_lot_control = 2 THEN
304: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
305: 'msn.lot_number';
306: END IF;
307: inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
308: inv_mwb_query_manager.execute_query;
303: IF l_lot_control = 2 THEN
304: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
305: 'msn.lot_number';
306: END IF;
307: inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
308: inv_mwb_query_manager.execute_query;
309:
310: END IF;
311:
304: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
305: 'msn.lot_number';
306: END IF;
307: inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
308: inv_mwb_query_manager.execute_query;
309:
310: END IF;
311:
312: EXCEPTION
335: WHERE organization_id = inv_mwb_globals.g_tree_organization_id
336: AND inventory_item_id = inv_mwb_globals.g_tree_item_id;
337:
338: make_common_queries('MSN');
339: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SERIAL).column_value :=
340: 'msn.serial_number';
341: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ITEM_ID).column_value :=
342: 'msn.inventory_item_id';
343: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
337:
338: make_common_queries('MSN');
339: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SERIAL).column_value :=
340: 'msn.serial_number';
341: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ITEM_ID).column_value :=
342: 'msn.inventory_item_id';
343: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
344: 'msn.current_subinventory_code';
345: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
339: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SERIAL).column_value :=
340: 'msn.serial_number';
341: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ITEM_ID).column_value :=
342: 'msn.inventory_item_id';
343: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
344: 'msn.current_subinventory_code';
345: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
346: 'msn.current_locator_id';
347: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
341: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ITEM_ID).column_value :=
342: 'msn.inventory_item_id';
343: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
344: 'msn.current_subinventory_code';
345: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
346: 'msn.current_locator_id';
347: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
348: 'msn.lot_number';
349: inv_mwb_query_manager.add_where_clause(
343: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
344: 'msn.current_subinventory_code';
345: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
346: 'msn.current_locator_id';
347: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
348: 'msn.lot_number';
349: inv_mwb_query_manager.add_where_clause(
350: 'msn.current_organization_id = :onh_tree_organization_id' ,
351: 'ONHAND'
345: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
346: 'msn.current_locator_id';
347: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
348: 'msn.lot_number';
349: inv_mwb_query_manager.add_where_clause(
350: 'msn.current_organization_id = :onh_tree_organization_id' ,
351: 'ONHAND'
352: );
353: inv_mwb_query_manager.add_where_clause(
349: inv_mwb_query_manager.add_where_clause(
350: 'msn.current_organization_id = :onh_tree_organization_id' ,
351: 'ONHAND'
352: );
353: inv_mwb_query_manager.add_where_clause(
354: 'msn.serial_number = :onh_tree_serial_number' ,
355: 'ONHAND'
356: );
357: inv_mwb_query_manager.add_where_clause(
353: inv_mwb_query_manager.add_where_clause(
354: 'msn.serial_number = :onh_tree_serial_number' ,
355: 'ONHAND'
356: );
357: inv_mwb_query_manager.add_where_clause(
358: 'msn.inventory_item_id = :onh_tree_inventory_item_id' ,
359: 'ONHAND'
360: );
361: inv_mwb_query_manager.add_bind_variable(
357: inv_mwb_query_manager.add_where_clause(
358: 'msn.inventory_item_id = :onh_tree_inventory_item_id' ,
359: 'ONHAND'
360: );
361: inv_mwb_query_manager.add_bind_variable(
362: 'onh_tree_organization_id' ,
363: inv_mwb_globals.g_tree_organization_id
364: );
365: inv_mwb_query_manager.add_bind_variable(
361: inv_mwb_query_manager.add_bind_variable(
362: 'onh_tree_organization_id' ,
363: inv_mwb_globals.g_tree_organization_id
364: );
365: inv_mwb_query_manager.add_bind_variable(
366: 'onh_tree_serial_number' ,
367: inv_mwb_globals.g_tree_serial_number
368: );
369: inv_mwb_query_manager.add_bind_variable(
365: inv_mwb_query_manager.add_bind_variable(
366: 'onh_tree_serial_number' ,
367: inv_mwb_globals.g_tree_serial_number
368: );
369: inv_mwb_query_manager.add_bind_variable(
370: 'onh_tree_inventory_item_id' ,
371: inv_mwb_globals.g_tree_item_id
372: );
373:
371: inv_mwb_globals.g_tree_item_id
372: );
373:
374: IF l_rev_control = 2 THEN
375: inv_mwb_query_manager.add_where_clause(
376: 'msn.revision = :onh_tree_revision' ,
377: 'ONHAND'
378: );
379: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
375: inv_mwb_query_manager.add_where_clause(
376: 'msn.revision = :onh_tree_revision' ,
377: 'ONHAND'
378: );
379: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
380: 'msn.revision';
381: inv_mwb_query_manager.add_bind_variable(
382: 'onh_tree_revision' ,
383: inv_mwb_globals.g_tree_rev
377: 'ONHAND'
378: );
379: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
380: 'msn.revision';
381: inv_mwb_query_manager.add_bind_variable(
382: 'onh_tree_revision' ,
383: inv_mwb_globals.g_tree_rev
384: );
385: END IF;
382: 'onh_tree_revision' ,
383: inv_mwb_globals.g_tree_rev
384: );
385: END IF;
386: inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
387: inv_mwb_query_manager.execute_query;
388:
389: END IF;
390: EXCEPTION
383: inv_mwb_globals.g_tree_rev
384: );
385: END IF;
386: inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
387: inv_mwb_query_manager.execute_query;
388:
389: END IF;
390: EXCEPTION
391: WHEN no_data_found THEN
395: PROCEDURE make_common_queries(p_flag VARCHAR2) IS
396: BEGIN
397:
398: IF p_flag = 'MSN_QUERY' THEN
399: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ORG_ID).column_value :=
400: 'msn.current_organization_id';
401: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ONHAND).column_value :=
402: 'count(1)';
403: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PACKED).column_value :=
397:
398: IF p_flag = 'MSN_QUERY' THEN
399: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ORG_ID).column_value :=
400: 'msn.current_organization_id';
401: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ONHAND).column_value :=
402: 'count(1)';
403: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PACKED).column_value :=
404: 'sum(decode(msn.lpn_id,NULL,0, 1))';
405: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.UNPACKED).column_value :=
399: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ORG_ID).column_value :=
400: 'msn.current_organization_id';
401: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ONHAND).column_value :=
402: 'count(1)';
403: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PACKED).column_value :=
404: 'sum(decode(msn.lpn_id,NULL,0, 1))';
405: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.UNPACKED).column_value :=
406: 'sum(decode(msn.lpn_id,NULL,1, 0))';
407: inv_mwb_query_manager.add_from_clause('mtl_serial_numbers msn' , 'ONHAND');
401: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ONHAND).column_value :=
402: 'count(1)';
403: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PACKED).column_value :=
404: 'sum(decode(msn.lpn_id,NULL,0, 1))';
405: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.UNPACKED).column_value :=
406: 'sum(decode(msn.lpn_id,NULL,1, 0))';
407: inv_mwb_query_manager.add_from_clause('mtl_serial_numbers msn' , 'ONHAND');
408: inv_mwb_query_manager.add_where_clause('msn.current_status = 3' , 'ONHAND');
409: inv_mwb_query_manager.add_group_clause('msn.current_organization_id' , 'ONHAND');
403: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PACKED).column_value :=
404: 'sum(decode(msn.lpn_id,NULL,0, 1))';
405: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.UNPACKED).column_value :=
406: 'sum(decode(msn.lpn_id,NULL,1, 0))';
407: inv_mwb_query_manager.add_from_clause('mtl_serial_numbers msn' , 'ONHAND');
408: inv_mwb_query_manager.add_where_clause('msn.current_status = 3' , 'ONHAND');
409: inv_mwb_query_manager.add_group_clause('msn.current_organization_id' , 'ONHAND');
410: ELSIF p_flag = 'MSN' THEN
411: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ORG_ID).column_value :=
404: 'sum(decode(msn.lpn_id,NULL,0, 1))';
405: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.UNPACKED).column_value :=
406: 'sum(decode(msn.lpn_id,NULL,1, 0))';
407: inv_mwb_query_manager.add_from_clause('mtl_serial_numbers msn' , 'ONHAND');
408: inv_mwb_query_manager.add_where_clause('msn.current_status = 3' , 'ONHAND');
409: inv_mwb_query_manager.add_group_clause('msn.current_organization_id' , 'ONHAND');
410: ELSIF p_flag = 'MSN' THEN
411: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ORG_ID).column_value :=
412: 'msn.current_organization_id';
405: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.UNPACKED).column_value :=
406: 'sum(decode(msn.lpn_id,NULL,1, 0))';
407: inv_mwb_query_manager.add_from_clause('mtl_serial_numbers msn' , 'ONHAND');
408: inv_mwb_query_manager.add_where_clause('msn.current_status = 3' , 'ONHAND');
409: inv_mwb_query_manager.add_group_clause('msn.current_organization_id' , 'ONHAND');
410: ELSIF p_flag = 'MSN' THEN
411: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ORG_ID).column_value :=
412: 'msn.current_organization_id';
413: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ONHAND).column_value :=
407: inv_mwb_query_manager.add_from_clause('mtl_serial_numbers msn' , 'ONHAND');
408: inv_mwb_query_manager.add_where_clause('msn.current_status = 3' , 'ONHAND');
409: inv_mwb_query_manager.add_group_clause('msn.current_organization_id' , 'ONHAND');
410: ELSIF p_flag = 'MSN' THEN
411: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ORG_ID).column_value :=
412: 'msn.current_organization_id';
413: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ONHAND).column_value :=
414: '1';
415: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PACKED).column_value :=
409: inv_mwb_query_manager.add_group_clause('msn.current_organization_id' , 'ONHAND');
410: ELSIF p_flag = 'MSN' THEN
411: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ORG_ID).column_value :=
412: 'msn.current_organization_id';
413: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ONHAND).column_value :=
414: '1';
415: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PACKED).column_value :=
416: 'decode(msn.lpn_id,NULL,0, 1)';
417: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.UNPACKED).column_value :=
411: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ORG_ID).column_value :=
412: 'msn.current_organization_id';
413: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ONHAND).column_value :=
414: '1';
415: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PACKED).column_value :=
416: 'decode(msn.lpn_id,NULL,0, 1)';
417: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.UNPACKED).column_value :=
418: 'decode(msn.lpn_id,NULL,1, 0)';
419: inv_mwb_query_manager.add_from_clause('mtl_serial_numbers msn' , 'ONHAND');
413: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ONHAND).column_value :=
414: '1';
415: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PACKED).column_value :=
416: 'decode(msn.lpn_id,NULL,0, 1)';
417: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.UNPACKED).column_value :=
418: 'decode(msn.lpn_id,NULL,1, 0)';
419: inv_mwb_query_manager.add_from_clause('mtl_serial_numbers msn' , 'ONHAND');
420: inv_mwb_query_manager.add_where_clause('msn.current_status = 3' , 'ONHAND');
421:
415: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PACKED).column_value :=
416: 'decode(msn.lpn_id,NULL,0, 1)';
417: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.UNPACKED).column_value :=
418: 'decode(msn.lpn_id,NULL,1, 0)';
419: inv_mwb_query_manager.add_from_clause('mtl_serial_numbers msn' , 'ONHAND');
420: inv_mwb_query_manager.add_where_clause('msn.current_status = 3' , 'ONHAND');
421:
422: END IF;
423:
416: 'decode(msn.lpn_id,NULL,0, 1)';
417: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.UNPACKED).column_value :=
418: 'decode(msn.lpn_id,NULL,1, 0)';
419: inv_mwb_query_manager.add_from_clause('mtl_serial_numbers msn' , 'ONHAND');
420: inv_mwb_query_manager.add_where_clause('msn.current_status = 3' , 'ONHAND');
421:
422: END IF;
423:
424: END make_common_queries;