[Home] [Help]
PACKAGE BODY: APPS.INV_MWB_CONTROLLER
Source
1 PACKAGE BODY INV_MWB_CONTROLLER AS
2 /* $Header: INVMWCTB.pls 120.25 2010/12/08 11:52:59 ksaripal ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'INV_MWB_CONTROLLER';
5
6 /*
7 * Forward declarations of functions
8 */
9 PROCEDURE initialize;
10
11 PROCEDURE PROCESS_QUERY(
12 p_organization_id IN NUMBER
13 , p_organization_code IN VARCHAR2
14 , p_locator_name IN VARCHAR2
15 , p_item_name IN VARCHAR2
16 , p_cost_group IN VARCHAR2
17 , p_project_number IN VARCHAR2
18 , p_task_number IN VARCHAR2
19 , p_owning_party IN VARCHAR2
20 , p_planning_party IN VARCHAR2
21 , p_lpn_state IN VARCHAR2
22 , p_status IN VARCHAR2
23 , p_subinventory_code IN VARCHAR2
24 , p_locator_id IN NUMBER
25 , p_client_code IN VARCHAR2 -- ER(9158529 client)
26 , p_inventory_item_id IN NUMBER
27 , p_revision IN VARCHAR2
28 , p_status_id IN NUMBER
29 , p_cost_group_id IN NUMBER
30 , p_category_set_id IN NUMBER -- ER(9158529)
31 , p_category_id IN NUMBER -- ER(9158529)
32 , p_lpn_from IN VARCHAR2
33 , p_lpn_from_id IN NUMBER
34 , p_lpn_to IN VARCHAR2
35 , p_lpn_to_id IN NUMBER
36 , p_lot_from IN VARCHAR2
37 , p_lot_to IN VARCHAR2
38 , p_supplier_lot_from IN VARCHAR2 -- Bug 8396954
39 , p_supplier_lot_to IN VARCHAR2 -- Bug 8396954
40 , p_serial_from IN VARCHAR2
41 , p_serial_to IN VARCHAR2
42 , p_workbench_vb IN NUMBER
43 , p_prepacked IN VARCHAR2
44 , p_project_id IN NUMBER
45 , p_task_id IN NUMBER
46 , p_unit_number IN VARCHAR2 -- Bug 9486070
47 , p_planning_org IN VARCHAR2
48 , p_owning_org IN VARCHAR2
49 , p_po_header_id IN NUMBER
50 , p_po_release_id IN NUMBER
51 , p_shipment_header_id_asn IN NUMBER
52 , p_shipment_header_id_interorg IN NUMBER
53 , p_req_header_id IN NUMBER
54 , p_vendor_id IN NUMBER
55 , p_vendor_site_id IN NUMBER
56 , p_source_org_id IN NUMBER
57 , p_chk_inbound IN NUMBER
58 , p_chk_receiving IN NUMBER
59 , p_chk_onhand IN NUMBER
60 , p_include_po_without_asn IN NUMBER
61 , p_expected_from_date IN DATE
62 , p_expected_to_date IN DATE
63 , p_internal_order_id IN NUMBER
64 , p_vendor_item IN VARCHAR2
65 , p_grade_from IN VARCHAR2
66 , p_planning_query_mode IN NUMBER
67 , p_owning_query_mode IN NUMBER
68 , p_wms_enabled_flag IN NUMBER
69 , p_subinventory_type IN NUMBER
70 , p_rcv_query_mode IN NUMBER
71 , p_detailed IN NUMBER
72 , p_item_description IN VARCHAR2
73 , p_qty_from IN NUMBER
74 , p_qty_to IN NUMBER
75 , p_view_by IN VARCHAR2
76 , p_locator_control_code IN NUMBER
77 , p_wms_installed_flag IN NUMBER
78 , p_check IN NUMBER
79 , p_serial_attr_query IN VARCHAR2 -- Bug 6429880
80 , p_lot_attr_query IN VARCHAR2 -- Bug 7566588
81 , p_responsibility_id IN NUMBER
82 , p_resp_application_id IN NUMBER
83 , p_is_projects_enabled_org IN NUMBER
84 ,p_expired_lots IN VARCHAR2
85 ,p_expiration_date IN DATE
86 ,p_parent_lot IN VARCHAR2 -- BUG 7556505
87 ,p_shipment_header_id IN NUMBER -- Bug 6633612
88 ) IS
89
90 l_procedure_name VARCHAR2(30);
91
92 BEGIN
93
94 l_procedure_name := 'PROCESS_QUERY';
95
96 inv_mwb_globals.print_msg(g_pkg_name, l_procedure_name, 'Entered' );
97
98 inv_mwb_globals.g_organization_id := p_organization_id;
99 inv_mwb_globals.g_subinventory_code := p_subinventory_code;
100 inv_mwb_globals.g_locator_id := p_locator_id;
101 inv_mwb_globals.g_client_code := p_client_code; -- ER(9158529 client)
102 inv_mwb_globals.g_inventory_item_id := p_inventory_item_id;
103 inv_mwb_globals.g_revision := p_revision;
104 inv_mwb_globals.g_status_id := p_status_id;
105 inv_mwb_globals.g_cost_group_id := p_cost_group_id;
106 inv_mwb_globals.g_category_set_id := p_category_set_id; -- ER(9158529)
107 inv_mwb_globals.g_category_id := p_category_id; -- ER(9158529)
108 inv_mwb_globals.g_lpn_from := p_lpn_from;
109 inv_mwb_globals.g_lpn_from_id := p_lpn_from_id;
110 inv_mwb_globals.g_lpn_to := p_lpn_to;
111 inv_mwb_globals.g_lpn_to_id := p_lpn_to_id;
112 inv_mwb_globals.g_lot_from := p_lot_from;
113 inv_mwb_globals.g_lot_to := p_lot_to;
114 inv_mwb_globals.g_supplier_lot_from := p_supplier_lot_from; -- Bug 8396954
115 inv_mwb_globals.g_supplier_lot_to := p_supplier_lot_to; -- Bug 8396954
116 inv_mwb_globals.g_serial_from := p_serial_from;
117 inv_mwb_globals.g_serial_to := p_serial_to;
118 inv_mwb_globals.g_workbench_vb := p_workbench_vb;
119 inv_mwb_globals.g_prepacked := p_prepacked;
120 inv_mwb_globals.g_project_id := p_project_id;
121 inv_mwb_globals.g_task_id := p_task_id;
122 inv_mwb_globals.g_unit_number := p_unit_number;
123 inv_mwb_globals.g_planning_org := p_planning_org;
124 inv_mwb_globals.g_owning_org := p_owning_org;
125 inv_mwb_globals.g_po_header_id := p_po_header_id;
126 inv_mwb_globals.g_po_release_id := p_po_release_id;
127 inv_mwb_globals.g_shipment_header_id_asn := p_shipment_header_id_asn;
128 inv_mwb_globals.g_shipment_header_id_interorg := p_shipment_header_id_interorg;
129 inv_mwb_globals.g_req_header_id := p_req_header_id;
130 inv_mwb_globals.g_vendor_id := p_vendor_id;
131 inv_mwb_globals.g_vendor_site_id := p_vendor_site_id;
132 inv_mwb_globals.g_source_org_id := p_source_org_id;
133 inv_mwb_globals.g_chk_inbound := p_chk_inbound;
134 inv_mwb_globals.g_chk_receiving := p_chk_receiving;
135 inv_mwb_globals.g_chk_onhand := p_chk_onhand;
136 inv_mwb_globals.g_include_po_without_asn := p_include_po_without_asn;
137 inv_mwb_globals.g_expected_from_date := p_expected_from_date;
138 inv_mwb_globals.g_expected_to_date := p_expected_to_date;
139 inv_mwb_globals.g_internal_order_id := p_internal_order_id;
140 inv_mwb_globals.g_vendor_item := p_vendor_item;
141 inv_mwb_globals.g_grade_from_code := p_grade_from;
142 inv_mwb_globals.g_planning_query_mode := p_planning_query_mode;
143 inv_mwb_globals.g_owning_qry_mode := p_owning_query_mode;
144 inv_mwb_globals.g_wms_enabled_flag := p_wms_enabled_flag;
145 inv_mwb_globals.g_sub_type := p_subinventory_type;
146 inv_mwb_globals.g_rcv_query_mode := p_rcv_query_mode;
147 inv_mwb_globals.g_detailed := p_detailed;
148 inv_mwb_globals.g_item_description := p_item_description;
149 inv_mwb_globals.g_qty_from := p_qty_from;
150 inv_mwb_globals.g_qty_to := p_qty_to;
151 inv_mwb_globals.g_view_by := p_view_by;
152 inv_mwb_globals.g_locator_control_code := p_locator_control_code;
153 inv_mwb_globals.g_wms_installed_flag := p_wms_installed_flag;
154 inv_mwb_globals.g_check := p_check;
155 inv_mwb_globals.g_serial_attr_query := p_serial_attr_query; -- Bug 6429880
156 inv_mwb_globals.g_lot_attr_query := p_lot_attr_query; -- Bug 7566588
157 inv_mwb_globals.g_responsibility_id := p_responsibility_id;
158 inv_mwb_globals.g_resp_application_id := p_resp_application_id;
159 inv_mwb_globals.g_is_projects_enabled_org := p_is_projects_enabled_org;
160 inv_mwb_globals.g_organization_code := p_organization_code;
161 inv_mwb_globals.g_locator_name := p_locator_name;
162 inv_mwb_globals.g_item_name := p_item_name;
163 inv_mwb_globals.g_cost_group := p_cost_group;
164 inv_mwb_globals.g_project_number := p_project_number;
165 inv_mwb_globals.g_task_number := p_task_number;
166 inv_mwb_globals.g_owning_party := p_owning_party;
167 inv_mwb_globals.g_planning_party := p_planning_party;
168 inv_mwb_globals.g_lpn_state := p_lpn_state;
169 inv_mwb_globals.g_status := p_status;
170 --KMOTUPAL ME # 3922793
171 inv_mwb_globals.g_expired_lots := p_expired_lots;
172 inv_mwb_globals.g_expiration_date := p_expiration_date;
173 inv_mwb_globals.g_parent_lot := p_parent_lot; -- BUG 7556505
174 inv_mwb_globals.g_shipment_header_id := p_shipment_header_id; -- Bug 6633612
175
176
177 IF (inv_mwb_globals.g_chk_onhand = 1
178 AND inv_mwb_globals.g_chk_receiving = 1)
179 OR (inv_mwb_globals.g_chk_onhand = 1
180 AND inv_mwb_globals.g_chk_inbound = 1)
181 OR (inv_mwb_globals.g_chk_receiving = 1
182 AND inv_mwb_globals.g_chk_inbound = 1)
183 THEN
184 inv_mwb_globals.g_multiple_loc_selected := 'TRUE';
185 ELSE
186 inv_mwb_globals.g_multiple_loc_selected := 'FALSE';
187 END IF;
188
189
190 inv_mwb_globals.print_parameters;
191
192 END PROCESS_QUERY;
193
194 PROCEDURE SET_TREE_GLOBALS(
195 p_tree_organization_id IN NUMBER
196 , p_view_by IN VARCHAR2
197 , p_tree_subinventory_code IN VARCHAR2
198 , p_tree_loc_id IN NUMBER
199 , p_tree_item_id IN NUMBER
200 , p_tree_plpn_id IN NUMBER
201 , p_tree_mat_loc_id IN NUMBER
202 , p_tree_doc_type_id IN NUMBER
203 , p_tree_doc_num IN VARCHAR2
204 , p_tree_doc_header_id IN NUMBER
205 , p_tree_st_id IN NUMBER
206 , p_tree_rev IN VARCHAR2
207 , p_tree_cg_id IN VARCHAR2
208 , p_tree_grade IN VARCHAR2
209 , p_tree_lot IN VARCHAR2
210 , p_tree_serial IN VARCHAR2
211 , p_tree_node_type IN VARCHAR2
212 , p_tree_node_value IN VARCHAR2
213 , p_tree_node_state IN NUMBER
214 , p_tree_node_high_value IN NUMBER
215 , p_tree_node_low_value IN NUMBER
216 , p_tree_serial_attr_query IN VARCHAR2 -- Bug 6429880
217 , p_tree_lot_attr_query IN VARCHAR2 -- Bug 7566588
218 , p_tree_event IN VARCHAR2
219 , p_tree_attribute_qf_lot IN VARCHAR2
220 , p_attribute_qf_serial IN VARCHAR2
221 , p_query_lot_attr IN VARCHAR2
222 , p_query_serial_attr IN VARCHAR2
223 , p_is_containerized IN NUMBER
224 ) IS
225
226 l_procedure_name CONSTANT VARCHAR2(30) := 'SET_TREE_GLOBALS';
227
228 BEGIN
229
230 inv_mwb_globals.print_msg(g_pkg_name, l_procedure_name, 'Entered' );
231
232 inv_mwb_globals.g_tree_organization_id := p_tree_organization_id;
236 inv_mwb_globals.g_tree_parent_lpn_id := p_tree_plpn_id;
233 inv_mwb_globals.g_tree_loc_id := p_tree_loc_id;
234 inv_mwb_globals.g_tree_subinventory_code := p_tree_subinventory_code;
235 inv_mwb_globals.g_tree_item_id := p_tree_item_id;
237 inv_mwb_globals.g_tree_mat_loc_id := p_tree_mat_loc_id;
238 inv_mwb_globals.g_tree_doc_type_id := p_tree_doc_type_id;
239 inv_mwb_globals.g_tree_doc_num := p_tree_doc_num;
240 inv_mwb_globals.g_tree_doc_header_id := p_tree_doc_header_id;
241 inv_mwb_globals.g_tree_st_id := p_tree_st_id;
242 inv_mwb_globals.g_tree_rev := p_tree_rev;
243 inv_mwb_globals.g_tree_cg_id := p_tree_cg_id;
244 inv_mwb_globals.g_tree_grade_code := p_tree_grade;
245 inv_mwb_globals.g_tree_lot_number := p_tree_lot;
246 inv_mwb_globals.g_tree_serial_number := p_tree_serial;
247 inv_mwb_globals.g_tree_node_type := p_tree_node_type;
248 inv_mwb_globals.g_tree_node_state := p_tree_node_state;
249 inv_mwb_globals.g_tree_node_high_value := p_tree_node_high_value;
250 inv_mwb_globals.g_tree_node_low_value := p_tree_node_low_value;
251 inv_mwb_globals.g_tree_node_value := p_tree_node_value;
252 inv_mwb_globals.g_view_by := p_view_by;
253 inv_mwb_globals.g_tree_event := p_tree_event;
254 inv_mwb_globals.g_tree_serial_attr_query := p_tree_serial_attr_query; -- Bug 6429880
255 inv_mwb_globals.g_tree_lot_attr_query := p_tree_lot_attr_query; -- Bug 7566588
256 inv_mwb_globals.g_tree_attribute_qf_lot := p_tree_attribute_qf_lot;
257 inv_mwb_globals.g_tree_attribute_qf_serial := p_attribute_qf_serial;
258 inv_mwb_globals.g_tree_query_lot_attr := p_query_lot_attr;
259 inv_mwb_globals.g_tree_query_serial_attr := p_query_serial_attr;
260 inv_mwb_globals.g_containerized := p_is_containerized;
261 -- Bug 9865190 Start
262 IF (NVL(p_tree_organization_id, inv_mwb_globals.g_organization_id) IS NOT NULL) THEN
263 SELECT DECODE(wms_enabled_flag, 'Y', 1, 0)
264 INTO inv_mwb_globals.g_wms_enabled_flag
265 FROM mtl_parameters
266 WHERE organization_id = NVL(p_tree_organization_id, inv_mwb_globals.g_organization_id);
267 END IF;
268 -- Bug 9865190 End
269 /*
270 * Temp changes for testing
271 */
272 inv_mwb_globals.print_parameters;
273
274
275 END SET_TREE_GLOBALS;
276
277
278 PROCEDURE EVENT (
279 x_node_value IN OUT NOCOPY NUMBER
280 , x_node_tbl IN OUT NOCOPY fnd_apptree.node_tbl_type
281 , x_tbl_index IN OUT NOCOPY NUMBER
282 ) IS
283
284 l_procedure_name CONSTANT VARCHAR2(30) := 'EVENT';
285 BEGIN
286
287 inv_mwb_globals.print_msg( g_pkg_name, l_procedure_name, 'Entered' );
288
289 inv_mwb_globals.print_msg( g_pkg_name, l_procedure_name, inv_mwb_globals.g_view_by );
290
291 /*
292 * Initialize engine
293 * -- Truncate table
294 * -- Initialize/reset the query engine..
295 */
296 inv_mwb_globals.g_revision_controlled := 0;
297 inv_mwb_globals.g_lot_controlled := 0;
298 inv_mwb_globals.g_locator_controlled := 0;
299 inv_mwb_globals.g_inserted_under_org := 'N';
300
301 IF inv_mwb_globals.g_tree_event = 'TREE_NODE_SELECTED' THEN
302 initialize;
303 END IF;
304
305 CASE inv_mwb_globals.g_view_by
306
307 WHEN 'LOCATION' THEN
308 inv_mwb_location_tree.event(
309 x_node_value
310 ,x_node_tbl
311 ,x_tbl_index
312 );
313 WHEN 'ITEM' THEN
314 inv_mwb_item_tree.event(
315 x_node_value
316 ,x_node_tbl
317 ,x_tbl_index
318 );
319 WHEN 'COST_GROUP' THEN
320 inv_mwb_cost_group_tree.event(
321 x_node_value
322 ,x_node_tbl
323 ,x_tbl_index
324 );
325 WHEN 'STATUS' THEN
326 inv_mwb_status_tree.event(
327 x_node_value
328 ,x_node_tbl
329 ,x_tbl_index
330 );
331 WHEN 'LPN' THEN
332 inv_mwb_lpn_tree.event(
333 x_node_value
334 ,x_node_tbl
335 ,x_tbl_index
336 );
337
338 WHEN 'SERIAL' THEN
339 inv_mwb_serial_tree.event(
340 x_node_value
341 ,x_node_tbl
342 ,x_tbl_index
343 );
344 WHEN 'LOT' THEN
345 inv_mwb_lot_tree.event(
346 x_node_value
347 ,x_node_tbl
348 ,x_tbl_index
349 );
350
351 WHEN 'GRADE' THEN
352 inv_mwb_grade_tree.event(
353 x_node_value
354 ,x_node_tbl
355 ,x_tbl_index
356 );
357 END CASE;
358
359 EXCEPTION
360 WHEN OTHERS THEN
361 inv_mwb_globals.print_msg(
365 );
362 g_pkg_name,
363 l_procedure_name,
364 DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
366 inv_mwb_globals.print_msg(
367 g_pkg_name,
368 l_procedure_name,
369 DBMS_UTILITY.FORMAT_ERROR_STACK
370 );
371 RAISE;
372
373 END EVENT;
374
375 PROCEDURE initialize IS
376 BEGIN
377
378 DELETE FROM mtl_mwb_gtmp;
379 inv_mwb_query_manager.initialize_union_query;
380 inv_mwb_query_manager.initialize_onhand_query;
381 inv_mwb_query_manager.initialize_inbound_query;
382 inv_mwb_query_manager.initialize_receiving_query;
383
384 inv_mwb_query_manager.initialize_onhand_1_query;
385 inv_mwb_query_manager.initialize_inbound_1_query;
386 inv_mwb_query_manager.initialize_receiving_1_query;
387
388 END initialize;
389
390
391 FUNCTION GET_LAST_QUERY RETURN LONG
392 IS
393 l_procedure_name CONSTANT VARCHAR2(30) := 'GET_LAST_QUERY';
394 BEGIN
395 inv_mwb_globals.print_msg( g_pkg_name, l_procedure_name, 'Entered');
396 return inv_mwb_globals.g_last_query;
397 END;
398
399 END INV_MWB_CONTROLLER;