33: inv_mwb_globals.g_serial_to IS NOT NULL
34: OR inv_mwb_globals.g_status_id IS NOT NULL -- Bug 6060233
35: OR inv_mwb_globals.g_serial_attr_query IS NOT NULL THEN -- Bug 6429880
36: make_common_queries('MSN_QUERY');
37: inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
38: ELSE
39: make_common_queries('MOQD');
40: inv_mwb_query_manager.add_qf_where_onhand('ONHAND');
41: END IF;
36: make_common_queries('MSN_QUERY');
37: inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
38: ELSE
39: make_common_queries('MOQD');
40: inv_mwb_query_manager.add_qf_where_onhand('ONHAND');
41: END IF;
42: inv_mwb_query_manager.execute_query;
43:
44: END IF; -- tree event
38: ELSE
39: make_common_queries('MOQD');
40: inv_mwb_query_manager.add_qf_where_onhand('ONHAND');
41: END IF;
42: inv_mwb_query_manager.execute_query;
43:
44: END IF; -- tree event
45:
46: EXCEPTION
72: inv_mwb_globals.g_serial_to IS NOT NULL
73: OR inv_mwb_globals.g_status_id IS NOT NULL -- Bug 6060233
74: OR inv_mwb_globals.g_serial_attr_query IS NOT NULL THEN -- Bug 6429880
75: make_common_queries('MSN_QUERY');
76: inv_mwb_query_manager.add_where_clause(
77: 'msn.inventory_item_id = :onh_tree_inventory_item_id' ,
78: 'ONHAND'
79: );
80: inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
76: inv_mwb_query_manager.add_where_clause(
77: 'msn.inventory_item_id = :onh_tree_inventory_item_id' ,
78: 'ONHAND'
79: );
80: inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
81: ELSE
82: make_common_queries('MOQD');
83: inv_mwb_query_manager.add_where_clause(
84: 'moqd.inventory_item_id = :onh_tree_inventory_item_id' ,
79: );
80: inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
81: ELSE
82: make_common_queries('MOQD');
83: inv_mwb_query_manager.add_where_clause(
84: 'moqd.inventory_item_id = :onh_tree_inventory_item_id' ,
85: 'ONHAND'
86: );
87:
84: 'moqd.inventory_item_id = :onh_tree_inventory_item_id' ,
85: 'ONHAND'
86: );
87:
88: inv_mwb_query_manager.add_qf_where_onhand('ONHAND');
89: END IF; -- serial
90: inv_mwb_query_manager.add_bind_variable(
91: 'onh_tree_inventory_item_id',
92: inv_mwb_globals.g_tree_item_id
86: );
87:
88: inv_mwb_query_manager.add_qf_where_onhand('ONHAND');
89: END IF; -- serial
90: inv_mwb_query_manager.add_bind_variable(
91: 'onh_tree_inventory_item_id',
92: inv_mwb_globals.g_tree_item_id
93: );
94: inv_mwb_query_manager.execute_query;
90: inv_mwb_query_manager.add_bind_variable(
91: 'onh_tree_inventory_item_id',
92: inv_mwb_globals.g_tree_item_id
93: );
94: inv_mwb_query_manager.execute_query;
95: END IF; -- onhand
96:
97: END IF; -- node seleted
98: EXCEPTION
154: IF NVL(l_rev_control, 1) = 1 AND NVL(l_lot_control, 1) = 1
155: AND l_serial_control IN ( 2,5 ) THEN
156:
157: make_common_queries('MSN');
158: inv_mwb_query_manager.add_where_clause(
159: 'msn.inventory_item_id = :onh_tree_inventory_item_id' ,
160: 'ONHAND'
161: );
162: inv_mwb_query_manager.add_where_clause(
158: inv_mwb_query_manager.add_where_clause(
159: 'msn.inventory_item_id = :onh_tree_inventory_item_id' ,
160: 'ONHAND'
161: );
162: inv_mwb_query_manager.add_where_clause(
163: 'msn.current_organization_id = :onh_tree_organization_id' ,
164: 'ONHAND'
165: );
166: inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
162: inv_mwb_query_manager.add_where_clause(
163: 'msn.current_organization_id = :onh_tree_organization_id' ,
164: 'ONHAND'
165: );
166: inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
167: ELSE -- only serial controlled
168: IF inv_mwb_globals.g_serial_from IS NOT NULL OR
169: inv_mwb_globals.g_serial_to IS NOT NULL
170: OR inv_mwb_globals.g_status_id IS NOT NULL -- Bug 6060233
170: OR inv_mwb_globals.g_status_id IS NOT NULL -- Bug 6060233
171: OR inv_mwb_globals.g_serial_attr_query IS NOT NULL THEN -- Bug 6429880
172: make_common_queries('MSN_QUERY');
173: IF l_rev_control = 2 THEN
174: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
175: 'msn.revision';
176: inv_mwb_query_manager.add_group_clause('msn.revision', 'ONHAND');
177: END IF;
178: IF NVL(l_rev_control, 1) = 1 AND l_lot_control = 2 THEN
172: make_common_queries('MSN_QUERY');
173: IF l_rev_control = 2 THEN
174: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
175: 'msn.revision';
176: inv_mwb_query_manager.add_group_clause('msn.revision', 'ONHAND');
177: END IF;
178: IF NVL(l_rev_control, 1) = 1 AND l_lot_control = 2 THEN
179: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
180: 'msn.lot_number';
175: 'msn.revision';
176: inv_mwb_query_manager.add_group_clause('msn.revision', 'ONHAND');
177: END IF;
178: IF NVL(l_rev_control, 1) = 1 AND l_lot_control = 2 THEN
179: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
180: 'msn.lot_number';
181: inv_mwb_query_manager.add_group_clause('msn.lot_number', 'ONHAND');
182: END IF;
183: inv_mwb_query_manager.add_where_clause(
177: END IF;
178: IF NVL(l_rev_control, 1) = 1 AND l_lot_control = 2 THEN
179: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
180: 'msn.lot_number';
181: inv_mwb_query_manager.add_group_clause('msn.lot_number', 'ONHAND');
182: END IF;
183: inv_mwb_query_manager.add_where_clause(
184: 'msn.inventory_item_id = :onh_tree_inventory_item_id' ,
185: 'ONHAND'
179: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
180: 'msn.lot_number';
181: inv_mwb_query_manager.add_group_clause('msn.lot_number', 'ONHAND');
182: END IF;
183: inv_mwb_query_manager.add_where_clause(
184: 'msn.inventory_item_id = :onh_tree_inventory_item_id' ,
185: 'ONHAND'
186: );
187: inv_mwb_query_manager.add_where_clause(
183: inv_mwb_query_manager.add_where_clause(
184: 'msn.inventory_item_id = :onh_tree_inventory_item_id' ,
185: 'ONHAND'
186: );
187: inv_mwb_query_manager.add_where_clause(
188: 'msn.current_organization_id = :onh_tree_organization_id' ,
189: 'ONHAND'
190: );
191: inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
187: inv_mwb_query_manager.add_where_clause(
188: 'msn.current_organization_id = :onh_tree_organization_id' ,
189: 'ONHAND'
190: );
191: inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
192: ELSE -- serial entered in qf
193: make_common_queries('MOQD');
194: inv_mwb_query_manager.add_where_clause(
195: 'moqd.inventory_item_id = :onh_tree_inventory_item_id' ,
190: );
191: inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
192: ELSE -- serial entered in qf
193: make_common_queries('MOQD');
194: inv_mwb_query_manager.add_where_clause(
195: 'moqd.inventory_item_id = :onh_tree_inventory_item_id' ,
196: 'ONHAND'
197: );
198: inv_mwb_query_manager.add_where_clause(
194: inv_mwb_query_manager.add_where_clause(
195: 'moqd.inventory_item_id = :onh_tree_inventory_item_id' ,
196: 'ONHAND'
197: );
198: inv_mwb_query_manager.add_where_clause(
199: 'moqd.organization_id = :onh_tree_organization_id' ,
200: 'ONHAND'
201: );
202: IF l_rev_control = 2 THEN
199: 'moqd.organization_id = :onh_tree_organization_id' ,
200: 'ONHAND'
201: );
202: IF l_rev_control = 2 THEN
203: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
204: 'moqd.revision';
205: inv_mwb_query_manager.add_group_clause('moqd.revision', 'ONHAND');
206: END IF;
207: IF NVL(l_rev_control, 1) = 1 AND l_lot_control = 2 THEN
201: );
202: IF l_rev_control = 2 THEN
203: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
204: 'moqd.revision';
205: inv_mwb_query_manager.add_group_clause('moqd.revision', 'ONHAND');
206: END IF;
207: IF NVL(l_rev_control, 1) = 1 AND l_lot_control = 2 THEN
208: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
209: 'moqd.lot_number';
204: 'moqd.revision';
205: inv_mwb_query_manager.add_group_clause('moqd.revision', 'ONHAND');
206: END IF;
207: IF NVL(l_rev_control, 1) = 1 AND l_lot_control = 2 THEN
208: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
209: 'moqd.lot_number';
210: inv_mwb_query_manager.add_group_clause('moqd.lot_number', 'ONHAND');
211: END IF;
212:
206: END IF;
207: IF NVL(l_rev_control, 1) = 1 AND l_lot_control = 2 THEN
208: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
209: 'moqd.lot_number';
210: inv_mwb_query_manager.add_group_clause('moqd.lot_number', 'ONHAND');
211: END IF;
212:
213: inv_mwb_query_manager.add_qf_where_onhand('ONHAND');
214: END IF; -- serial in query find
209: 'moqd.lot_number';
210: inv_mwb_query_manager.add_group_clause('moqd.lot_number', 'ONHAND');
211: END IF;
212:
213: inv_mwb_query_manager.add_qf_where_onhand('ONHAND');
214: END IF; -- serial in query find
215: END IF; -- only serial controlled
216: inv_mwb_query_manager.add_bind_variable(
217: 'onh_tree_organization_id',
212:
213: inv_mwb_query_manager.add_qf_where_onhand('ONHAND');
214: END IF; -- serial in query find
215: END IF; -- only serial controlled
216: inv_mwb_query_manager.add_bind_variable(
217: 'onh_tree_organization_id',
218: inv_mwb_globals.g_tree_organization_id
219: );
220: inv_mwb_query_manager.add_bind_variable(
216: inv_mwb_query_manager.add_bind_variable(
217: 'onh_tree_organization_id',
218: inv_mwb_globals.g_tree_organization_id
219: );
220: inv_mwb_query_manager.add_bind_variable(
221: 'onh_tree_inventory_item_id',
222: inv_mwb_globals.g_tree_item_id
223: );
224: END IF; -- onhand check
221: 'onh_tree_inventory_item_id',
222: inv_mwb_globals.g_tree_item_id
223: );
224: END IF; -- onhand check
225: inv_mwb_query_manager.execute_query;
226:
227: END IF; -- tree event
228:
229: EXCEPTION
273: IF inv_mwb_globals.g_chk_onhand = 1 THEN
274:
275: IF l_lot_control = 1 AND l_serial_control IN ( 2,5 ) THEN
276: make_common_queries('MSN');
277: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
278: 'msn.revision';
279: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SERIAL).column_value :=
280: 'msn.serial_number';
281: inv_mwb_query_manager.add_where_clause('msn.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
275: IF l_lot_control = 1 AND l_serial_control IN ( 2,5 ) THEN
276: make_common_queries('MSN');
277: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
278: 'msn.revision';
279: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SERIAL).column_value :=
280: 'msn.serial_number';
281: inv_mwb_query_manager.add_where_clause('msn.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
282: inv_mwb_query_manager.add_where_clause('msn.organization_id = :onh_tree_organization_id' ,'ONHAND');
283: inv_mwb_query_manager.add_where_clause('msn.revision = :onh_tree_revision' ,'ONHAND');
277: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
278: 'msn.revision';
279: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SERIAL).column_value :=
280: 'msn.serial_number';
281: inv_mwb_query_manager.add_where_clause('msn.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
282: inv_mwb_query_manager.add_where_clause('msn.organization_id = :onh_tree_organization_id' ,'ONHAND');
283: inv_mwb_query_manager.add_where_clause('msn.revision = :onh_tree_revision' ,'ONHAND');
284: inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
285: ELSE -- l_serial_control
278: 'msn.revision';
279: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SERIAL).column_value :=
280: 'msn.serial_number';
281: inv_mwb_query_manager.add_where_clause('msn.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
282: inv_mwb_query_manager.add_where_clause('msn.organization_id = :onh_tree_organization_id' ,'ONHAND');
283: inv_mwb_query_manager.add_where_clause('msn.revision = :onh_tree_revision' ,'ONHAND');
284: inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
285: ELSE -- l_serial_control
286: IF inv_mwb_globals.g_serial_from IS NOT NULL OR
279: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SERIAL).column_value :=
280: 'msn.serial_number';
281: inv_mwb_query_manager.add_where_clause('msn.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
282: inv_mwb_query_manager.add_where_clause('msn.organization_id = :onh_tree_organization_id' ,'ONHAND');
283: inv_mwb_query_manager.add_where_clause('msn.revision = :onh_tree_revision' ,'ONHAND');
284: inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
285: ELSE -- l_serial_control
286: IF inv_mwb_globals.g_serial_from IS NOT NULL OR
287: inv_mwb_globals.g_serial_to IS NOT NULL
280: 'msn.serial_number';
281: inv_mwb_query_manager.add_where_clause('msn.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
282: inv_mwb_query_manager.add_where_clause('msn.organization_id = :onh_tree_organization_id' ,'ONHAND');
283: inv_mwb_query_manager.add_where_clause('msn.revision = :onh_tree_revision' ,'ONHAND');
284: inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
285: ELSE -- l_serial_control
286: IF inv_mwb_globals.g_serial_from IS NOT NULL OR
287: inv_mwb_globals.g_serial_to IS NOT NULL
288: OR inv_mwb_globals.g_status_id IS NOT NULL -- Bug 6060233
287: inv_mwb_globals.g_serial_to IS NOT NULL
288: OR inv_mwb_globals.g_status_id IS NOT NULL -- Bug 6060233
289: OR inv_mwb_globals.g_serial_attr_query IS NOT NULL THEN -- Bug 6429880
290: make_common_queries('MSN_QUERY');
291: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
292: 'msn.revision';
293: inv_mwb_query_manager.add_group_clause('msn.revision', 'ONHAND'); -- Bug 6060233
294: IF l_lot_control = 2 THEN
295: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
289: OR inv_mwb_globals.g_serial_attr_query IS NOT NULL THEN -- Bug 6429880
290: make_common_queries('MSN_QUERY');
291: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
292: 'msn.revision';
293: inv_mwb_query_manager.add_group_clause('msn.revision', 'ONHAND'); -- Bug 6060233
294: IF l_lot_control = 2 THEN
295: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
296: 'msn.lot_number';
297: inv_mwb_query_manager.add_group_clause('msn.lot_number', 'ONHAND');
291: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
292: 'msn.revision';
293: inv_mwb_query_manager.add_group_clause('msn.revision', 'ONHAND'); -- Bug 6060233
294: IF l_lot_control = 2 THEN
295: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
296: 'msn.lot_number';
297: inv_mwb_query_manager.add_group_clause('msn.lot_number', 'ONHAND');
298: END IF;
299: inv_mwb_query_manager.add_where_clause(
293: inv_mwb_query_manager.add_group_clause('msn.revision', 'ONHAND'); -- Bug 6060233
294: IF l_lot_control = 2 THEN
295: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
296: 'msn.lot_number';
297: inv_mwb_query_manager.add_group_clause('msn.lot_number', 'ONHAND');
298: END IF;
299: inv_mwb_query_manager.add_where_clause(
300: 'msn.inventory_item_id = :onh_tree_inventory_item_id' ,
301: 'ONHAND'
295: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
296: 'msn.lot_number';
297: inv_mwb_query_manager.add_group_clause('msn.lot_number', 'ONHAND');
298: END IF;
299: inv_mwb_query_manager.add_where_clause(
300: 'msn.inventory_item_id = :onh_tree_inventory_item_id' ,
301: 'ONHAND'
302: );
303: inv_mwb_query_manager.add_where_clause(
299: inv_mwb_query_manager.add_where_clause(
300: 'msn.inventory_item_id = :onh_tree_inventory_item_id' ,
301: 'ONHAND'
302: );
303: inv_mwb_query_manager.add_where_clause(
304: 'msn.current_organization_id = :onh_tree_organization_id' ,
305: 'ONHAND'
306: );
307: inv_mwb_query_manager.add_where_clause(
303: inv_mwb_query_manager.add_where_clause(
304: 'msn.current_organization_id = :onh_tree_organization_id' ,
305: 'ONHAND'
306: );
307: inv_mwb_query_manager.add_where_clause(
308: 'msn.revision = :onh_tree_revision' ,
309: 'ONHAND'
310: );
311: inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
307: inv_mwb_query_manager.add_where_clause(
308: 'msn.revision = :onh_tree_revision' ,
309: 'ONHAND'
310: );
311: inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
312: ELSE -- serial entered in qf
313: make_common_queries('MOQD');
314: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
315: 'moqd.revision';
310: );
311: inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
312: ELSE -- serial entered in qf
313: make_common_queries('MOQD');
314: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
315: 'moqd.revision';
316: inv_mwb_query_manager.add_group_clause('moqd.revision', 'ONHAND');
317: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
318: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id' ,'ONHAND');
312: ELSE -- serial entered in qf
313: make_common_queries('MOQD');
314: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
315: 'moqd.revision';
316: inv_mwb_query_manager.add_group_clause('moqd.revision', 'ONHAND');
317: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
318: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id' ,'ONHAND');
319: inv_mwb_query_manager.add_where_clause('moqd.revision = :onh_tree_revision' ,'ONHAND');
320: IF l_lot_control = 2 THEN
313: make_common_queries('MOQD');
314: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
315: 'moqd.revision';
316: inv_mwb_query_manager.add_group_clause('moqd.revision', 'ONHAND');
317: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
318: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id' ,'ONHAND');
319: inv_mwb_query_manager.add_where_clause('moqd.revision = :onh_tree_revision' ,'ONHAND');
320: IF l_lot_control = 2 THEN
321: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
314: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
315: 'moqd.revision';
316: inv_mwb_query_manager.add_group_clause('moqd.revision', 'ONHAND');
317: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
318: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id' ,'ONHAND');
319: inv_mwb_query_manager.add_where_clause('moqd.revision = :onh_tree_revision' ,'ONHAND');
320: IF l_lot_control = 2 THEN
321: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
322: 'moqd.lot_number';
315: 'moqd.revision';
316: inv_mwb_query_manager.add_group_clause('moqd.revision', 'ONHAND');
317: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
318: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id' ,'ONHAND');
319: inv_mwb_query_manager.add_where_clause('moqd.revision = :onh_tree_revision' ,'ONHAND');
320: IF l_lot_control = 2 THEN
321: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
322: 'moqd.lot_number';
323: inv_mwb_query_manager.add_group_clause('moqd.lot_number', 'ONHAND');
317: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
318: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id' ,'ONHAND');
319: inv_mwb_query_manager.add_where_clause('moqd.revision = :onh_tree_revision' ,'ONHAND');
320: IF l_lot_control = 2 THEN
321: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
322: 'moqd.lot_number';
323: inv_mwb_query_manager.add_group_clause('moqd.lot_number', 'ONHAND');
324: END IF;
325:
319: inv_mwb_query_manager.add_where_clause('moqd.revision = :onh_tree_revision' ,'ONHAND');
320: IF l_lot_control = 2 THEN
321: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
322: 'moqd.lot_number';
323: inv_mwb_query_manager.add_group_clause('moqd.lot_number', 'ONHAND');
324: END IF;
325:
326: inv_mwb_query_manager.add_qf_where_onhand('ONHAND');
327: END IF; -- serial entered in qf
322: 'moqd.lot_number';
323: inv_mwb_query_manager.add_group_clause('moqd.lot_number', 'ONHAND');
324: END IF;
325:
326: inv_mwb_query_manager.add_qf_where_onhand('ONHAND');
327: END IF; -- serial entered in qf
328: END IF; -- l_serial_control
329: inv_mwb_query_manager.add_bind_variable(
330: 'onh_tree_organization_id',
325:
326: inv_mwb_query_manager.add_qf_where_onhand('ONHAND');
327: END IF; -- serial entered in qf
328: END IF; -- l_serial_control
329: inv_mwb_query_manager.add_bind_variable(
330: 'onh_tree_organization_id',
331: inv_mwb_globals.g_tree_organization_id
332: );
333: inv_mwb_query_manager.add_bind_variable(
329: inv_mwb_query_manager.add_bind_variable(
330: 'onh_tree_organization_id',
331: inv_mwb_globals.g_tree_organization_id
332: );
333: inv_mwb_query_manager.add_bind_variable(
334: 'onh_tree_inventory_item_id',
335: inv_mwb_globals.g_tree_item_id
336: );
337: inv_mwb_query_manager.add_bind_variable(
333: inv_mwb_query_manager.add_bind_variable(
334: 'onh_tree_inventory_item_id',
335: inv_mwb_globals.g_tree_item_id
336: );
337: inv_mwb_query_manager.add_bind_variable(
338: 'onh_tree_revision',
339: inv_mwb_globals.g_tree_rev
340: );
341: END IF; --chk_onhand
338: 'onh_tree_revision',
339: inv_mwb_globals.g_tree_rev
340: );
341: END IF; --chk_onhand
342: inv_mwb_query_manager.execute_query;
343: END IF;
344: EXCEPTION
345: WHEN no_data_found THEN
346: NULL;
380: IF inv_mwb_globals.g_chk_onhand = 1 THEN
381:
382: IF l_serial_control IN ( 2,5 ) THEN
383: make_common_queries('MSN');
384: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
385: 'msn.lot_number';
386: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SERIAL).column_value :=
387: 'msn.serial_number';
388: inv_mwb_query_manager.add_where_clause('msn.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
382: IF l_serial_control IN ( 2,5 ) THEN
383: make_common_queries('MSN');
384: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
385: 'msn.lot_number';
386: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SERIAL).column_value :=
387: 'msn.serial_number';
388: inv_mwb_query_manager.add_where_clause('msn.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
389: inv_mwb_query_manager.add_where_clause('msn.current_organization_id = :onh_tree_organization_id' ,'ONHAND');
390: inv_mwb_query_manager.add_where_clause('msn.lot_number = :onh_tree_lot_number' ,'ONHAND');
384: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
385: 'msn.lot_number';
386: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SERIAL).column_value :=
387: 'msn.serial_number';
388: inv_mwb_query_manager.add_where_clause('msn.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
389: inv_mwb_query_manager.add_where_clause('msn.current_organization_id = :onh_tree_organization_id' ,'ONHAND');
390: inv_mwb_query_manager.add_where_clause('msn.lot_number = :onh_tree_lot_number' ,'ONHAND');
391: IF l_rev_control = 2 THEN
392: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
385: 'msn.lot_number';
386: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SERIAL).column_value :=
387: 'msn.serial_number';
388: inv_mwb_query_manager.add_where_clause('msn.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
389: inv_mwb_query_manager.add_where_clause('msn.current_organization_id = :onh_tree_organization_id' ,'ONHAND');
390: inv_mwb_query_manager.add_where_clause('msn.lot_number = :onh_tree_lot_number' ,'ONHAND');
391: IF l_rev_control = 2 THEN
392: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
393: 'msn.revision';
386: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SERIAL).column_value :=
387: 'msn.serial_number';
388: inv_mwb_query_manager.add_where_clause('msn.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
389: inv_mwb_query_manager.add_where_clause('msn.current_organization_id = :onh_tree_organization_id' ,'ONHAND');
390: inv_mwb_query_manager.add_where_clause('msn.lot_number = :onh_tree_lot_number' ,'ONHAND');
391: IF l_rev_control = 2 THEN
392: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
393: 'msn.revision';
394: inv_mwb_query_manager.add_where_clause('msn.revision = :onh_tree_revision' ,'ONHAND');
388: inv_mwb_query_manager.add_where_clause('msn.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
389: inv_mwb_query_manager.add_where_clause('msn.current_organization_id = :onh_tree_organization_id' ,'ONHAND');
390: inv_mwb_query_manager.add_where_clause('msn.lot_number = :onh_tree_lot_number' ,'ONHAND');
391: IF l_rev_control = 2 THEN
392: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
393: 'msn.revision';
394: inv_mwb_query_manager.add_where_clause('msn.revision = :onh_tree_revision' ,'ONHAND');
395: END IF;
396: inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
390: inv_mwb_query_manager.add_where_clause('msn.lot_number = :onh_tree_lot_number' ,'ONHAND');
391: IF l_rev_control = 2 THEN
392: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
393: 'msn.revision';
394: inv_mwb_query_manager.add_where_clause('msn.revision = :onh_tree_revision' ,'ONHAND');
395: END IF;
396: inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
397: ELSE -- l_serial_control
398: IF inv_mwb_globals.g_serial_from IS NOT NULL OR
392: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
393: 'msn.revision';
394: inv_mwb_query_manager.add_where_clause('msn.revision = :onh_tree_revision' ,'ONHAND');
395: END IF;
396: inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
397: ELSE -- l_serial_control
398: IF inv_mwb_globals.g_serial_from IS NOT NULL OR
399: inv_mwb_globals.g_serial_to IS NOT NULL
400: OR inv_mwb_globals.g_status_id IS NOT NULL -- Bug 6060233
400: OR inv_mwb_globals.g_status_id IS NOT NULL -- Bug 6060233
401: OR inv_mwb_globals.g_serial_attr_query IS NOT NULL THEN -- Bug 6429880
402: make_common_queries('MSN_QUERY');
403: IF l_rev_control = 2 THEN
404: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
405: 'msn.revision';
406: inv_mwb_query_manager.add_where_clause('msn.revision = :onh_tree_revision' ,'ONHAND');
407: inv_mwb_query_manager.add_group_clause('msn.revision', 'ONHAND');
408: END IF;
402: make_common_queries('MSN_QUERY');
403: IF l_rev_control = 2 THEN
404: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
405: 'msn.revision';
406: inv_mwb_query_manager.add_where_clause('msn.revision = :onh_tree_revision' ,'ONHAND');
407: inv_mwb_query_manager.add_group_clause('msn.revision', 'ONHAND');
408: END IF;
409: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
410: 'msn.lot_number';
403: IF l_rev_control = 2 THEN
404: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
405: 'msn.revision';
406: inv_mwb_query_manager.add_where_clause('msn.revision = :onh_tree_revision' ,'ONHAND');
407: inv_mwb_query_manager.add_group_clause('msn.revision', 'ONHAND');
408: END IF;
409: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
410: 'msn.lot_number';
411: inv_mwb_query_manager.add_group_clause('msn.lot_number', 'ONHAND');
405: 'msn.revision';
406: inv_mwb_query_manager.add_where_clause('msn.revision = :onh_tree_revision' ,'ONHAND');
407: inv_mwb_query_manager.add_group_clause('msn.revision', 'ONHAND');
408: END IF;
409: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
410: 'msn.lot_number';
411: inv_mwb_query_manager.add_group_clause('msn.lot_number', 'ONHAND');
412: inv_mwb_query_manager.add_where_clause(
413: 'msn.inventory_item_id = :onh_tree_inventory_item_id' ,
407: inv_mwb_query_manager.add_group_clause('msn.revision', 'ONHAND');
408: END IF;
409: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
410: 'msn.lot_number';
411: inv_mwb_query_manager.add_group_clause('msn.lot_number', 'ONHAND');
412: inv_mwb_query_manager.add_where_clause(
413: 'msn.inventory_item_id = :onh_tree_inventory_item_id' ,
414: 'ONHAND'
415: );
408: END IF;
409: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
410: 'msn.lot_number';
411: inv_mwb_query_manager.add_group_clause('msn.lot_number', 'ONHAND');
412: inv_mwb_query_manager.add_where_clause(
413: 'msn.inventory_item_id = :onh_tree_inventory_item_id' ,
414: 'ONHAND'
415: );
416: inv_mwb_query_manager.add_where_clause(
412: inv_mwb_query_manager.add_where_clause(
413: 'msn.inventory_item_id = :onh_tree_inventory_item_id' ,
414: 'ONHAND'
415: );
416: inv_mwb_query_manager.add_where_clause(
417: 'msn.current_organization_id = :onh_tree_organization_id' ,
418: 'ONHAND'
419: );
420: inv_mwb_query_manager.add_where_clause(
416: inv_mwb_query_manager.add_where_clause(
417: 'msn.current_organization_id = :onh_tree_organization_id' ,
418: 'ONHAND'
419: );
420: inv_mwb_query_manager.add_where_clause(
421: 'msn.lot_number = :onh_tree_lot_number' ,
422: 'ONHAND'
423: );
424: inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
420: inv_mwb_query_manager.add_where_clause(
421: 'msn.lot_number = :onh_tree_lot_number' ,
422: 'ONHAND'
423: );
424: inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
425: ELSE -- serial entered in qf
426: make_common_queries('MOQD');
427: IF l_rev_control = 2 THEN
428: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
424: inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
425: ELSE -- serial entered in qf
426: make_common_queries('MOQD');
427: IF l_rev_control = 2 THEN
428: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
429: 'moqd.revision';
430: inv_mwb_query_manager.add_where_clause('moqd.revision = :onh_tree_revision' ,'ONHAND');
431: inv_mwb_query_manager.add_group_clause('moqd.revision','ONHAND');
432: END IF;
426: make_common_queries('MOQD');
427: IF l_rev_control = 2 THEN
428: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
429: 'moqd.revision';
430: inv_mwb_query_manager.add_where_clause('moqd.revision = :onh_tree_revision' ,'ONHAND');
431: inv_mwb_query_manager.add_group_clause('moqd.revision','ONHAND');
432: END IF;
433: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
434: 'moqd.lot_number';
427: IF l_rev_control = 2 THEN
428: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
429: 'moqd.revision';
430: inv_mwb_query_manager.add_where_clause('moqd.revision = :onh_tree_revision' ,'ONHAND');
431: inv_mwb_query_manager.add_group_clause('moqd.revision','ONHAND');
432: END IF;
433: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
434: 'moqd.lot_number';
435: inv_mwb_query_manager.add_group_clause('moqd.lot_number','ONHAND');
429: 'moqd.revision';
430: inv_mwb_query_manager.add_where_clause('moqd.revision = :onh_tree_revision' ,'ONHAND');
431: inv_mwb_query_manager.add_group_clause('moqd.revision','ONHAND');
432: END IF;
433: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
434: 'moqd.lot_number';
435: inv_mwb_query_manager.add_group_clause('moqd.lot_number','ONHAND');
436:
437: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
431: inv_mwb_query_manager.add_group_clause('moqd.revision','ONHAND');
432: END IF;
433: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
434: 'moqd.lot_number';
435: inv_mwb_query_manager.add_group_clause('moqd.lot_number','ONHAND');
436:
437: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
438: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id' ,'ONHAND');
439: inv_mwb_query_manager.add_where_clause('moqd.lot_number = :onh_tree_lot_number' ,'ONHAND');
433: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
434: 'moqd.lot_number';
435: inv_mwb_query_manager.add_group_clause('moqd.lot_number','ONHAND');
436:
437: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
438: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id' ,'ONHAND');
439: inv_mwb_query_manager.add_where_clause('moqd.lot_number = :onh_tree_lot_number' ,'ONHAND');
440:
441: inv_mwb_query_manager.add_qf_where_onhand('ONHAND');
434: 'moqd.lot_number';
435: inv_mwb_query_manager.add_group_clause('moqd.lot_number','ONHAND');
436:
437: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
438: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id' ,'ONHAND');
439: inv_mwb_query_manager.add_where_clause('moqd.lot_number = :onh_tree_lot_number' ,'ONHAND');
440:
441: inv_mwb_query_manager.add_qf_where_onhand('ONHAND');
442: END IF; -- serial entered in qf
435: inv_mwb_query_manager.add_group_clause('moqd.lot_number','ONHAND');
436:
437: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
438: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id' ,'ONHAND');
439: inv_mwb_query_manager.add_where_clause('moqd.lot_number = :onh_tree_lot_number' ,'ONHAND');
440:
441: inv_mwb_query_manager.add_qf_where_onhand('ONHAND');
442: END IF; -- serial entered in qf
443: END IF; -- l_serial_control
437: inv_mwb_query_manager.add_where_clause('moqd.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
438: inv_mwb_query_manager.add_where_clause('moqd.organization_id = :onh_tree_organization_id' ,'ONHAND');
439: inv_mwb_query_manager.add_where_clause('moqd.lot_number = :onh_tree_lot_number' ,'ONHAND');
440:
441: inv_mwb_query_manager.add_qf_where_onhand('ONHAND');
442: END IF; -- serial entered in qf
443: END IF; -- l_serial_control
444: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
445: inv_mwb_query_manager.add_bind_variable('onh_tree_inventory_item_id', inv_mwb_globals.g_tree_item_id);
440:
441: inv_mwb_query_manager.add_qf_where_onhand('ONHAND');
442: END IF; -- serial entered in qf
443: END IF; -- l_serial_control
444: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
445: inv_mwb_query_manager.add_bind_variable('onh_tree_inventory_item_id', inv_mwb_globals.g_tree_item_id);
446: inv_mwb_query_manager.add_bind_variable('onh_tree_lot_number', inv_mwb_globals.g_tree_lot_number);
447: IF l_rev_control = 2 THEN
448: inv_mwb_query_manager.add_bind_variable('onh_tree_revision', inv_mwb_globals.g_tree_rev);
441: inv_mwb_query_manager.add_qf_where_onhand('ONHAND');
442: END IF; -- serial entered in qf
443: END IF; -- l_serial_control
444: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
445: inv_mwb_query_manager.add_bind_variable('onh_tree_inventory_item_id', inv_mwb_globals.g_tree_item_id);
446: inv_mwb_query_manager.add_bind_variable('onh_tree_lot_number', inv_mwb_globals.g_tree_lot_number);
447: IF l_rev_control = 2 THEN
448: inv_mwb_query_manager.add_bind_variable('onh_tree_revision', inv_mwb_globals.g_tree_rev);
449: END IF;
442: END IF; -- serial entered in qf
443: END IF; -- l_serial_control
444: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
445: inv_mwb_query_manager.add_bind_variable('onh_tree_inventory_item_id', inv_mwb_globals.g_tree_item_id);
446: inv_mwb_query_manager.add_bind_variable('onh_tree_lot_number', inv_mwb_globals.g_tree_lot_number);
447: IF l_rev_control = 2 THEN
448: inv_mwb_query_manager.add_bind_variable('onh_tree_revision', inv_mwb_globals.g_tree_rev);
449: END IF;
450: END IF; --chk_onhand
444: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
445: inv_mwb_query_manager.add_bind_variable('onh_tree_inventory_item_id', inv_mwb_globals.g_tree_item_id);
446: inv_mwb_query_manager.add_bind_variable('onh_tree_lot_number', inv_mwb_globals.g_tree_lot_number);
447: IF l_rev_control = 2 THEN
448: inv_mwb_query_manager.add_bind_variable('onh_tree_revision', inv_mwb_globals.g_tree_rev);
449: END IF;
450: END IF; --chk_onhand
451: inv_mwb_query_manager.execute_query;
452:
447: IF l_rev_control = 2 THEN
448: inv_mwb_query_manager.add_bind_variable('onh_tree_revision', inv_mwb_globals.g_tree_rev);
449: END IF;
450: END IF; --chk_onhand
451: inv_mwb_query_manager.execute_query;
452:
453: END IF; -- tree event
454: EXCEPTION
455: WHEN no_data_found THEN
479: AND organization_id = inv_mwb_globals.g_tree_organization_id;
480:
481: IF inv_mwb_globals.g_chk_onhand = 1 THEN
482: make_common_queries('MSN');
483: inv_mwb_query_manager.add_where_clause('msn.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
484: inv_mwb_query_manager.add_where_clause('msn.current_organization_id = :onh_tree_organization_id' ,'ONHAND');
485: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
486: inv_mwb_query_manager.add_bind_variable('onh_tree_inventory_item_id', inv_mwb_globals.g_tree_item_id);
487: IF l_rev_control = 2 THEN
480:
481: IF inv_mwb_globals.g_chk_onhand = 1 THEN
482: make_common_queries('MSN');
483: inv_mwb_query_manager.add_where_clause('msn.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
484: inv_mwb_query_manager.add_where_clause('msn.current_organization_id = :onh_tree_organization_id' ,'ONHAND');
485: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
486: inv_mwb_query_manager.add_bind_variable('onh_tree_inventory_item_id', inv_mwb_globals.g_tree_item_id);
487: IF l_rev_control = 2 THEN
488: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
481: IF inv_mwb_globals.g_chk_onhand = 1 THEN
482: make_common_queries('MSN');
483: inv_mwb_query_manager.add_where_clause('msn.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
484: inv_mwb_query_manager.add_where_clause('msn.current_organization_id = :onh_tree_organization_id' ,'ONHAND');
485: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
486: inv_mwb_query_manager.add_bind_variable('onh_tree_inventory_item_id', inv_mwb_globals.g_tree_item_id);
487: IF l_rev_control = 2 THEN
488: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
489: 'msn.revision';
482: make_common_queries('MSN');
483: inv_mwb_query_manager.add_where_clause('msn.inventory_item_id = :onh_tree_inventory_item_id' ,'ONHAND');
484: inv_mwb_query_manager.add_where_clause('msn.current_organization_id = :onh_tree_organization_id' ,'ONHAND');
485: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
486: inv_mwb_query_manager.add_bind_variable('onh_tree_inventory_item_id', inv_mwb_globals.g_tree_item_id);
487: IF l_rev_control = 2 THEN
488: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
489: 'msn.revision';
490: inv_mwb_query_manager.add_where_clause('msn.revision = :onh_tree_revision' ,'ONHAND');
484: inv_mwb_query_manager.add_where_clause('msn.current_organization_id = :onh_tree_organization_id' ,'ONHAND');
485: inv_mwb_query_manager.add_bind_variable('onh_tree_organization_id', inv_mwb_globals.g_tree_organization_id);
486: inv_mwb_query_manager.add_bind_variable('onh_tree_inventory_item_id', inv_mwb_globals.g_tree_item_id);
487: IF l_rev_control = 2 THEN
488: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
489: 'msn.revision';
490: inv_mwb_query_manager.add_where_clause('msn.revision = :onh_tree_revision' ,'ONHAND');
491: inv_mwb_query_manager.add_bind_variable('onh_tree_revision', inv_mwb_globals.g_tree_rev);
492: END IF;
486: inv_mwb_query_manager.add_bind_variable('onh_tree_inventory_item_id', inv_mwb_globals.g_tree_item_id);
487: IF l_rev_control = 2 THEN
488: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
489: 'msn.revision';
490: inv_mwb_query_manager.add_where_clause('msn.revision = :onh_tree_revision' ,'ONHAND');
491: inv_mwb_query_manager.add_bind_variable('onh_tree_revision', inv_mwb_globals.g_tree_rev);
492: END IF;
493: IF l_lot_control = 2 THEN
494: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
487: IF l_rev_control = 2 THEN
488: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
489: 'msn.revision';
490: inv_mwb_query_manager.add_where_clause('msn.revision = :onh_tree_revision' ,'ONHAND');
491: inv_mwb_query_manager.add_bind_variable('onh_tree_revision', inv_mwb_globals.g_tree_rev);
492: END IF;
493: IF l_lot_control = 2 THEN
494: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
495: 'msn.lot_number';
490: inv_mwb_query_manager.add_where_clause('msn.revision = :onh_tree_revision' ,'ONHAND');
491: inv_mwb_query_manager.add_bind_variable('onh_tree_revision', inv_mwb_globals.g_tree_rev);
492: END IF;
493: IF l_lot_control = 2 THEN
494: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
495: 'msn.lot_number';
496: inv_mwb_query_manager.add_where_clause('msn.lot_number = :onh_lot_number' ,'ONHAND');
497: inv_mwb_query_manager.add_bind_variable('onh_lot_number', inv_mwb_globals.g_tree_lot_number);
498: END IF;
492: END IF;
493: IF l_lot_control = 2 THEN
494: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
495: 'msn.lot_number';
496: inv_mwb_query_manager.add_where_clause('msn.lot_number = :onh_lot_number' ,'ONHAND');
497: inv_mwb_query_manager.add_bind_variable('onh_lot_number', inv_mwb_globals.g_tree_lot_number);
498: END IF;
499: inv_mwb_query_manager.add_where_clause('msn.serial_number = :onh_serial_number' ,'ONHAND');
500: inv_mwb_query_manager.add_where_clause('msn.current_status = 3' ,'ONHAND');
493: IF l_lot_control = 2 THEN
494: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
495: 'msn.lot_number';
496: inv_mwb_query_manager.add_where_clause('msn.lot_number = :onh_lot_number' ,'ONHAND');
497: inv_mwb_query_manager.add_bind_variable('onh_lot_number', inv_mwb_globals.g_tree_lot_number);
498: END IF;
499: inv_mwb_query_manager.add_where_clause('msn.serial_number = :onh_serial_number' ,'ONHAND');
500: inv_mwb_query_manager.add_where_clause('msn.current_status = 3' ,'ONHAND');
501: inv_mwb_query_manager.add_bind_variable('onh_serial_number', inv_mwb_globals.g_tree_serial_number);
495: 'msn.lot_number';
496: inv_mwb_query_manager.add_where_clause('msn.lot_number = :onh_lot_number' ,'ONHAND');
497: inv_mwb_query_manager.add_bind_variable('onh_lot_number', inv_mwb_globals.g_tree_lot_number);
498: END IF;
499: inv_mwb_query_manager.add_where_clause('msn.serial_number = :onh_serial_number' ,'ONHAND');
500: inv_mwb_query_manager.add_where_clause('msn.current_status = 3' ,'ONHAND');
501: inv_mwb_query_manager.add_bind_variable('onh_serial_number', inv_mwb_globals.g_tree_serial_number);
502:
503: END IF;
496: inv_mwb_query_manager.add_where_clause('msn.lot_number = :onh_lot_number' ,'ONHAND');
497: inv_mwb_query_manager.add_bind_variable('onh_lot_number', inv_mwb_globals.g_tree_lot_number);
498: END IF;
499: inv_mwb_query_manager.add_where_clause('msn.serial_number = :onh_serial_number' ,'ONHAND');
500: inv_mwb_query_manager.add_where_clause('msn.current_status = 3' ,'ONHAND');
501: inv_mwb_query_manager.add_bind_variable('onh_serial_number', inv_mwb_globals.g_tree_serial_number);
502:
503: END IF;
504: inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
497: inv_mwb_query_manager.add_bind_variable('onh_lot_number', inv_mwb_globals.g_tree_lot_number);
498: END IF;
499: inv_mwb_query_manager.add_where_clause('msn.serial_number = :onh_serial_number' ,'ONHAND');
500: inv_mwb_query_manager.add_where_clause('msn.current_status = 3' ,'ONHAND');
501: inv_mwb_query_manager.add_bind_variable('onh_serial_number', inv_mwb_globals.g_tree_serial_number);
502:
503: END IF;
504: inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
505: inv_mwb_query_manager.execute_query;
500: inv_mwb_query_manager.add_where_clause('msn.current_status = 3' ,'ONHAND');
501: inv_mwb_query_manager.add_bind_variable('onh_serial_number', inv_mwb_globals.g_tree_serial_number);
502:
503: END IF;
504: inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
505: inv_mwb_query_manager.execute_query;
506: END IF;
507:
508: EXCEPTION
501: inv_mwb_query_manager.add_bind_variable('onh_serial_number', inv_mwb_globals.g_tree_serial_number);
502:
503: END IF;
504: inv_mwb_query_manager.add_qf_where_onhand('ONHAND_MSN');
505: inv_mwb_query_manager.execute_query;
506: END IF;
507:
508: EXCEPTION
509: WHEN no_data_found THEN
516: IF(inv_mwb_globals.g_chk_onhand = 1) THEN
517:
518: CASE p_flag
519: WHEN 'MSN' THEN
520: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ITEM_ID).column_value :=
521: 'msn.inventory_item_id';
522:
523: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ORG_ID).column_value :=
524: 'msn.current_organization_id';
519: WHEN 'MSN' THEN
520: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ITEM_ID).column_value :=
521: 'msn.inventory_item_id';
522:
523: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ORG_ID).column_value :=
524: 'msn.current_organization_id';
525:
526: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PRIMARY_UOM_CODE).column_value :=
527: '''Ea''';
522:
523: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ORG_ID).column_value :=
524: 'msn.current_organization_id';
525:
526: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PRIMARY_UOM_CODE).column_value :=
527: '''Ea''';
528:
529: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ONHAND).column_value := 1;
530:
525:
526: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PRIMARY_UOM_CODE).column_value :=
527: '''Ea''';
528:
529: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ONHAND).column_value := 1;
530:
531: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PACKED).column_value :=
532: 'DECODE(msn.lpn_id, NULL, 0,1)';
533:
527: '''Ea''';
528:
529: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ONHAND).column_value := 1;
530:
531: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PACKED).column_value :=
532: 'DECODE(msn.lpn_id, NULL, 0,1)';
533:
534: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.UNPACKED).column_value :=
535: 'DECODE(msn.lpn_id, NULL, 1,0)';
530:
531: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PACKED).column_value :=
532: 'DECODE(msn.lpn_id, NULL, 0,1)';
533:
534: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.UNPACKED).column_value :=
535: 'DECODE(msn.lpn_id, NULL, 1,0)';
536:
537: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SECONDARY_UOM_CODE).column_value :=
538: 'NULL';
533:
534: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.UNPACKED).column_value :=
535: 'DECODE(msn.lpn_id, NULL, 1,0)';
536:
537: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SECONDARY_UOM_CODE).column_value :=
538: 'NULL';
539:
540: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SECONDARY_PACKED).column_value :=
541: 'NULL';
536:
537: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SECONDARY_UOM_CODE).column_value :=
538: 'NULL';
539:
540: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SECONDARY_PACKED).column_value :=
541: 'NULL';
542:
543: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SECONDARY_UNPACKED).column_value :=
544: 'NULL';
539:
540: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SECONDARY_PACKED).column_value :=
541: 'NULL';
542:
543: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SECONDARY_UNPACKED).column_value :=
544: 'NULL';
545:
546: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SERIAL).column_value :=
547: 'msn.serial_number';
542:
543: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SECONDARY_UNPACKED).column_value :=
544: 'NULL';
545:
546: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SERIAL).column_value :=
547: 'msn.serial_number';
548:
549: inv_mwb_query_manager.add_from_clause('mtl_serial_numbers msn', 'ONHAND');
550:
545:
546: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SERIAL).column_value :=
547: 'msn.serial_number';
548:
549: inv_mwb_query_manager.add_from_clause('mtl_serial_numbers msn', 'ONHAND');
550:
551: inv_mwb_query_manager.add_where_clause('msn.current_status = 3', 'ONHAND');
552:
553: WHEN 'MOQD' THEN
547: 'msn.serial_number';
548:
549: inv_mwb_query_manager.add_from_clause('mtl_serial_numbers msn', 'ONHAND');
550:
551: inv_mwb_query_manager.add_where_clause('msn.current_status = 3', 'ONHAND');
552:
553: WHEN 'MOQD' THEN
554:
555: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ITEM_ID).column_value :=
551: inv_mwb_query_manager.add_where_clause('msn.current_status = 3', 'ONHAND');
552:
553: WHEN 'MOQD' THEN
554:
555: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ITEM_ID).column_value :=
556: 'moqd.inventory_item_id';
557:
558: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ORG_ID).column_value :=
559: 'moqd.organization_id';
554:
555: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ITEM_ID).column_value :=
556: 'moqd.inventory_item_id';
557:
558: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ORG_ID).column_value :=
559: 'moqd.organization_id';
560:
561: -- inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PRIMARY_UOM_CODE).column_value :=
562: -- 'moqd.transaction_uom_code';
557:
558: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ORG_ID).column_value :=
559: 'moqd.organization_id';
560:
561: -- inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PRIMARY_UOM_CODE).column_value :=
562: -- 'moqd.transaction_uom_code';
563:
564: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ONHAND).column_value :=
565: 'SUM(moqd.primary_transaction_quantity)';
560:
561: -- inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PRIMARY_UOM_CODE).column_value :=
562: -- 'moqd.transaction_uom_code';
563:
564: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ONHAND).column_value :=
565: 'SUM(moqd.primary_transaction_quantity)';
566:
567: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PACKED).column_value :=
568: 'SUM(DECODE(moqd.containerized_flag, 1, moqd.primary_transaction_quantity, 0))';
563:
564: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ONHAND).column_value :=
565: 'SUM(moqd.primary_transaction_quantity)';
566:
567: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PACKED).column_value :=
568: 'SUM(DECODE(moqd.containerized_flag, 1, moqd.primary_transaction_quantity, 0))';
569:
570: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.UNPACKED).column_value :=
571: 'SUM(DECODE(moqd.containerized_flag, 1, 0, moqd.primary_transaction_quantity))';
566:
567: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PACKED).column_value :=
568: 'SUM(DECODE(moqd.containerized_flag, 1, moqd.primary_transaction_quantity, 0))';
569:
570: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.UNPACKED).column_value :=
571: 'SUM(DECODE(moqd.containerized_flag, 1, 0, moqd.primary_transaction_quantity))';
572:
573: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SECONDARY_UOM_CODE).column_value :=
574: 'moqd.secondary_uom_code';
569:
570: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.UNPACKED).column_value :=
571: 'SUM(DECODE(moqd.containerized_flag, 1, 0, moqd.primary_transaction_quantity))';
572:
573: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SECONDARY_UOM_CODE).column_value :=
574: 'moqd.secondary_uom_code';
575:
576: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SECONDARY_ONHAND).column_value :=
577: 'SUM(moqd.secondary_transaction_quantity)';
572:
573: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SECONDARY_UOM_CODE).column_value :=
574: 'moqd.secondary_uom_code';
575:
576: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SECONDARY_ONHAND).column_value :=
577: 'SUM(moqd.secondary_transaction_quantity)';
578:
579: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SECONDARY_PACKED).column_value :=
580: 'SUM(DECODE(moqd.containerized_flag, 1, moqd.secondary_transaction_quantity, 0))';
575:
576: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SECONDARY_ONHAND).column_value :=
577: 'SUM(moqd.secondary_transaction_quantity)';
578:
579: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SECONDARY_PACKED).column_value :=
580: 'SUM(DECODE(moqd.containerized_flag, 1, moqd.secondary_transaction_quantity, 0))';
581:
582: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SECONDARY_UNPACKED).column_value :=
583: 'SUM(DECODE(moqd.containerized_flag, 1, 0, moqd.secondary_transaction_quantity))';
578:
579: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SECONDARY_PACKED).column_value :=
580: 'SUM(DECODE(moqd.containerized_flag, 1, moqd.secondary_transaction_quantity, 0))';
581:
582: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SECONDARY_UNPACKED).column_value :=
583: 'SUM(DECODE(moqd.containerized_flag, 1, 0, moqd.secondary_transaction_quantity))';
584:
585: inv_mwb_query_manager.add_from_clause('mtl_onhand_quantities_detail moqd', 'ONHAND');
586:
581:
582: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SECONDARY_UNPACKED).column_value :=
583: 'SUM(DECODE(moqd.containerized_flag, 1, 0, moqd.secondary_transaction_quantity))';
584:
585: inv_mwb_query_manager.add_from_clause('mtl_onhand_quantities_detail moqd', 'ONHAND');
586:
587: inv_mwb_query_manager.add_group_clause('moqd.inventory_item_id', 'ONHAND');
588: inv_mwb_query_manager.add_group_clause('moqd.organization_id', 'ONHAND');
589: -- inv_mwb_query_manager.add_group_clause('moqd.transaction_uom_code', 'ONHAND');
583: 'SUM(DECODE(moqd.containerized_flag, 1, 0, moqd.secondary_transaction_quantity))';
584:
585: inv_mwb_query_manager.add_from_clause('mtl_onhand_quantities_detail moqd', 'ONHAND');
586:
587: inv_mwb_query_manager.add_group_clause('moqd.inventory_item_id', 'ONHAND');
588: inv_mwb_query_manager.add_group_clause('moqd.organization_id', 'ONHAND');
589: -- inv_mwb_query_manager.add_group_clause('moqd.transaction_uom_code', 'ONHAND');
590: inv_mwb_query_manager.add_group_clause('moqd.secondary_uom_code', 'ONHAND');
591:
584:
585: inv_mwb_query_manager.add_from_clause('mtl_onhand_quantities_detail moqd', 'ONHAND');
586:
587: inv_mwb_query_manager.add_group_clause('moqd.inventory_item_id', 'ONHAND');
588: inv_mwb_query_manager.add_group_clause('moqd.organization_id', 'ONHAND');
589: -- inv_mwb_query_manager.add_group_clause('moqd.transaction_uom_code', 'ONHAND');
590: inv_mwb_query_manager.add_group_clause('moqd.secondary_uom_code', 'ONHAND');
591:
592: WHEN 'MSN_QUERY' THEN
585: inv_mwb_query_manager.add_from_clause('mtl_onhand_quantities_detail moqd', 'ONHAND');
586:
587: inv_mwb_query_manager.add_group_clause('moqd.inventory_item_id', 'ONHAND');
588: inv_mwb_query_manager.add_group_clause('moqd.organization_id', 'ONHAND');
589: -- inv_mwb_query_manager.add_group_clause('moqd.transaction_uom_code', 'ONHAND');
590: inv_mwb_query_manager.add_group_clause('moqd.secondary_uom_code', 'ONHAND');
591:
592: WHEN 'MSN_QUERY' THEN
593: -- Bug 6060233
586:
587: inv_mwb_query_manager.add_group_clause('moqd.inventory_item_id', 'ONHAND');
588: inv_mwb_query_manager.add_group_clause('moqd.organization_id', 'ONHAND');
589: -- inv_mwb_query_manager.add_group_clause('moqd.transaction_uom_code', 'ONHAND');
590: inv_mwb_query_manager.add_group_clause('moqd.secondary_uom_code', 'ONHAND');
591:
592: WHEN 'MSN_QUERY' THEN
593: -- Bug 6060233
594: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ITEM_ID).column_value :=
590: inv_mwb_query_manager.add_group_clause('moqd.secondary_uom_code', 'ONHAND');
591:
592: WHEN 'MSN_QUERY' THEN
593: -- Bug 6060233
594: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ITEM_ID).column_value :=
595: 'msn.inventory_item_id';
596:
597: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ORG_ID).column_value :=
598: 'msn.current_organization_id';
593: -- Bug 6060233
594: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ITEM_ID).column_value :=
595: 'msn.inventory_item_id';
596:
597: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ORG_ID).column_value :=
598: 'msn.current_organization_id';
599:
600: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PRIMARY_UOM_CODE).column_value :=
601: '''Ea''';
596:
597: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ORG_ID).column_value :=
598: 'msn.current_organization_id';
599:
600: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PRIMARY_UOM_CODE).column_value :=
601: '''Ea''';
602: -- End Bug 6060233
603: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ONHAND).column_value :=
604: 'count(1)';
599:
600: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PRIMARY_UOM_CODE).column_value :=
601: '''Ea''';
602: -- End Bug 6060233
603: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ONHAND).column_value :=
604: 'count(1)';
605: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PACKED).column_value :=
606: 'count(decode(msn.lpn_id,NULL,0, 1))';
607: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.UNPACKED).column_value :=
601: '''Ea''';
602: -- End Bug 6060233
603: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ONHAND).column_value :=
604: 'count(1)';
605: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PACKED).column_value :=
606: 'count(decode(msn.lpn_id,NULL,0, 1))';
607: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.UNPACKED).column_value :=
608: 'count(decode(msn.lpn_id,NULL,1, 0))';
609: inv_mwb_query_manager.add_group_clause('msn.inventory_item_id' , 'ONHAND');
603: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.ONHAND).column_value :=
604: 'count(1)';
605: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PACKED).column_value :=
606: 'count(decode(msn.lpn_id,NULL,0, 1))';
607: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.UNPACKED).column_value :=
608: 'count(decode(msn.lpn_id,NULL,1, 0))';
609: inv_mwb_query_manager.add_group_clause('msn.inventory_item_id' , 'ONHAND');
610: inv_mwb_query_manager.add_group_clause('msn.current_organization_id', 'ONHAND');
611: inv_mwb_query_manager.add_group_clause('''Ea''', 'ONHAND');
605: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.PACKED).column_value :=
606: 'count(decode(msn.lpn_id,NULL,0, 1))';
607: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.UNPACKED).column_value :=
608: 'count(decode(msn.lpn_id,NULL,1, 0))';
609: inv_mwb_query_manager.add_group_clause('msn.inventory_item_id' , 'ONHAND');
610: inv_mwb_query_manager.add_group_clause('msn.current_organization_id', 'ONHAND');
611: inv_mwb_query_manager.add_group_clause('''Ea''', 'ONHAND');
612:
613: inv_mwb_query_manager.add_from_clause('mtl_serial_numbers msn', 'ONHAND');
606: 'count(decode(msn.lpn_id,NULL,0, 1))';
607: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.UNPACKED).column_value :=
608: 'count(decode(msn.lpn_id,NULL,1, 0))';
609: inv_mwb_query_manager.add_group_clause('msn.inventory_item_id' , 'ONHAND');
610: inv_mwb_query_manager.add_group_clause('msn.current_organization_id', 'ONHAND');
611: inv_mwb_query_manager.add_group_clause('''Ea''', 'ONHAND');
612:
613: inv_mwb_query_manager.add_from_clause('mtl_serial_numbers msn', 'ONHAND');
614: inv_mwb_query_manager.add_where_clause('msn.current_status = 3', 'ONHAND');
607: inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.UNPACKED).column_value :=
608: 'count(decode(msn.lpn_id,NULL,1, 0))';
609: inv_mwb_query_manager.add_group_clause('msn.inventory_item_id' , 'ONHAND');
610: inv_mwb_query_manager.add_group_clause('msn.current_organization_id', 'ONHAND');
611: inv_mwb_query_manager.add_group_clause('''Ea''', 'ONHAND');
612:
613: inv_mwb_query_manager.add_from_clause('mtl_serial_numbers msn', 'ONHAND');
614: inv_mwb_query_manager.add_where_clause('msn.current_status = 3', 'ONHAND');
615: END CASE; -- p_flag
609: inv_mwb_query_manager.add_group_clause('msn.inventory_item_id' , 'ONHAND');
610: inv_mwb_query_manager.add_group_clause('msn.current_organization_id', 'ONHAND');
611: inv_mwb_query_manager.add_group_clause('''Ea''', 'ONHAND');
612:
613: inv_mwb_query_manager.add_from_clause('mtl_serial_numbers msn', 'ONHAND');
614: inv_mwb_query_manager.add_where_clause('msn.current_status = 3', 'ONHAND');
615: END CASE; -- p_flag
616:
617: END IF; -- End if for onhand
610: inv_mwb_query_manager.add_group_clause('msn.current_organization_id', 'ONHAND');
611: inv_mwb_query_manager.add_group_clause('''Ea''', 'ONHAND');
612:
613: inv_mwb_query_manager.add_from_clause('mtl_serial_numbers msn', 'ONHAND');
614: inv_mwb_query_manager.add_where_clause('msn.current_status = 3', 'ONHAND');
615: END CASE; -- p_flag
616:
617: END IF; -- End if for onhand
618: