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