[Home] [Help]
PACKAGE BODY: APPS.WMA_INV_WRAPPERS
Source
1 PACKAGE BODY wma_inv_wrappers AS
2 /* $Header: wmainvwb.pls 115.12 2003/09/08 22:11:58 rlohani ship $ */
3
4 type tree_tbl_t is table of NUMBER index by binary_integer;
5
6 g_tree_tbl tree_tbl_t;
7 g_lotTree_tbl tree_tbl_t;
8
9 PROCEDURE validateLot(p_inventory_item_id IN NUMBER,
10 p_organization_id IN NUMBER,
11 p_lot_number IN VARCHAR2,
12 x_lot_exp OUT NOCOPY DATE,
13 x_return_status OUT NOCOPY VARCHAR2,
14 x_err_msg OUT NOCOPY VARCHAR2) IS
15 l_msg_count NUMBER;
16 BEGIN
17 WMS_WIP_INTEGRATION.perform_lot_validations(p_item_id => p_inventory_item_id,
18 p_org_id => p_organization_id,
19 p_lot_number => p_lot_number,
20 x_return_status => x_return_status,
21 x_msg_count => l_msg_count,
22 x_msg_data => x_err_msg);
23 if(l_msg_count > 1) then
24 inv_mobile_helper_functions.get_stacked_messages(x_err_msg);
25 end if;
26
27 if(x_return_status = FND_API.G_RET_STS_SUCCESS) then --lot exists in mtl_lot_numbers
28 select expiration_date
29 into x_lot_exp
30 from mtl_lot_numbers
31 where lot_number = p_lot_number
32 and inventory_item_id = p_inventory_item_id
33 and organization_id = p_organization_id;
34 end if;
35 EXCEPTION
36 when others then
37 x_return_status := fnd_api.g_ret_sts_unexp_error;
38 END validateLot;
39
40 PROCEDURE insertLot(p_header_id IN NUMBER,
41 p_lot_number IN VARCHAR2,
42 x_return_status OUT NOCOPY VARCHAR2,
43 x_err_msg OUT NOCOPY VARCHAR2) IS
44 l_msg_count NUMBER;
45 BEGIN
46 WMS_WIP_INTEGRATION.insert_lot(p_header_id => p_header_id,
47 p_lot_number => p_lot_number,
48 x_return_status => x_return_status,
49 x_msg_count => l_msg_count,
50 x_msg_data => x_err_msg);
51
52 if(l_msg_count > 1) then
53 inv_mobile_helper_functions.get_stacked_messages(x_err_msg);
54 end if;
55 EXCEPTION
56 when others then
57 x_return_status := fnd_api.g_ret_sts_unexp_error;
58 end insertLot;
59
60 PROCEDURE createLots(p_header_id IN NUMBER,
61 x_err_msg OUT NOCOPY VARCHAR2,
62 x_return_status OUT NOCOPY VARCHAR2) IS
63 cursor lots(v_header_id in NUMBER) IS
64 select lot_number
65 from wip_lpn_completions_lots
66 where header_id = v_header_id;
67
68 l_msg_count NUMBER;
69
70 BEGIN
71 x_return_status := fnd_api.G_RET_STS_SUCCESS;
72 SAVEPOINT preProc;
73 FOR lots_rec in lots(p_header_id) LOOP
74 wms_wip_integration.insert_lot(p_header_id, lots_rec.lot_number, x_return_status, l_msg_count, x_err_msg);
75 if(x_return_status = fnd_api.g_ret_sts_unexp_error) then
76 ROLLBACK TO SAVEPOINT preProc;
77 exit;
78 end if;
79 end LOOP;
80 if(l_msg_count > 1) then
81 inv_mobile_helper_functions.get_stacked_messages(x_err_msg);
82 end if;
83 EXCEPTION
84 when others then
85 x_return_status := fnd_api.g_ret_sts_unexp_error;
86 END createLots;
87
88 PROCEDURE updateSerials(p_header_id IN NUMBER,
89 x_err_msg OUT NOCOPY VARCHAR2,
90 x_return_status OUT NOCOPY VARCHAR2) IS
91 cursor serials(v_header_id NUMBER) IS
92 select fm_serial_number
93 from wip_lpn_completions_serials
94 where header_id = v_header_id;
95
96 l_msg_count NUMBER;
97
98 BEGIN
99 x_return_status := fnd_api.G_RET_STS_SUCCESS;
100 SAVEPOINT preProc;
101 FOR serials_rec in serials(p_header_id) LOOP
102 wms_wip_integration.update_serial(p_header_id => p_header_id,
103 p_serial_number => serials_rec.fm_serial_number,
104 x_return_status => x_return_status,
105 x_msg_count => l_msg_count,
106 x_msg_data => x_err_msg);
107 if(x_return_status <> fnd_api.g_ret_sts_success) then
108 ROLLBACK TO SAVEPOINT preProc;
109 if(l_msg_count > 1) then
110 inv_mobile_helper_functions.get_stacked_messages(x_err_msg);
111 end if;
112 exit;
113 end if;
114 end loop;
115 EXCEPTION
116 when others then
117 x_return_status := fnd_api.g_ret_sts_unexp_error;
118 END updateSerials;
119
120 PROCEDURE updateLSAttributes(p_header_id IN NUMBER,
121 x_return_status OUT NOCOPY VARCHAR2,
122 x_err_msg OUT NOCOPY VARCHAR2) IS BEGIN
123 x_return_status := fnd_api.G_RET_STS_SUCCESS;
124 createLots(p_header_id, x_err_msg, x_return_status);
125 if(x_return_status = fnd_api.G_RET_STS_SUCCESS) then
126 updateSerials(p_header_id, x_err_msg, x_return_status);
127 end if;
128 EXCEPTION
129 when others then
130 x_return_status := fnd_api.g_ret_sts_unexp_error;
131 end updateLSAttributes;
132
133 PROCEDURE backflush(p_header_id IN NUMBER,
134 x_return_status OUT NOCOPY VARCHAR2,
135 x_err_msg OUT NOCOPY VARCHAR2) IS
136 l_msg_count NUMBER;
137
138 BEGIN
139 wms_wip_integration.backflush(p_header_id => p_header_id,
140 x_return_status => x_return_status,
141 x_msg_count => l_msg_count,
142 x_msg_data => x_err_msg);
143
144 if(l_msg_count > 1) then
145 inv_mobile_helper_functions.get_stacked_messages(x_err_msg);
146 end if;
147 EXCEPTION
148 when others then
149 x_return_status := fnd_api.g_ret_sts_unexp_error;
150 END backflush;
151
152 PROCEDURE createLpn(p_api_version IN NUMBER,
153 p_commit IN VARCHAR2,
154 p_lpn IN VARCHAR2,
155 p_organization_id IN NUMBER,
156 p_source IN NUMBER,
157 p_source_type_id IN NUMBER,
158 x_return_status OUT NOCOPY VARCHAR2,
159 x_err_msg OUT NOCOPY VARCHAR2,
160 x_lpn_id OUT NOCOPY VARCHAR2) IS
161
162 l_msg_count NUMBER;
163
164 BEGIN
165 wms_container_pub.create_lpn (p_api_version => p_api_version,
166 p_commit => p_commit,
167 p_lpn => p_lpn,
168 p_organization_id => p_organization_id,
169 p_source => p_source,
170 p_source_type_id => p_source_type_id,
171 x_return_status => x_return_status,
172 x_msg_count => l_msg_count,
173 x_msg_data => x_err_msg,
174 x_lpn_id => x_lpn_id);
175
176 if(l_msg_count > 1) then
177 inv_mobile_helper_functions.get_stacked_messages(x_err_msg);
178 end if;
179 EXCEPTION
180 when others then
181 x_return_status := fnd_api.g_ret_sts_unexp_error;
182 END createLpn;
183
184 PROCEDURE packLpnContainer(p_api_version IN NUMBER,
185 p_commit IN VARCHAR2,
186 p_lpn_id IN NUMBER,
187 p_content_item_id IN NUMBER,
188 p_revision IN VARCHAR2,
189 p_lot_number IN VARCHAR2,
190 p_from_serial_number IN VARCHAR2,
191 p_to_serial_number IN VARCHAR2,
192 p_quantity IN NUMBER,
193 p_organization_id IN NUMBER,
194 p_source_type_id IN NUMBER,
195 p_uom IN VARCHAR2,
196 x_return_status OUT NOCOPY VARCHAR2,
197 x_err_msg OUT NOCOPY VARCHAR2) IS
198
199 l_msg_count NUMBER;
200
201 BEGIN
202 wms_container_pub.packunpack_container (p_api_version => p_api_version,
203 p_commit => p_commit,
204 p_lpn_id => p_lpn_id,
205 p_content_item_id => p_content_item_id,
206 p_revision => p_revision,
207 p_lot_number => p_lot_number,
208 p_from_serial_number => p_from_serial_number,
209 p_to_serial_number => p_to_serial_number,
210 p_quantity => p_quantity,
211 p_organization_id => p_organization_id,
212 p_operation => 1,
213 p_source_type_id => p_source_type_id,
214 p_uom => p_uom,
215 x_return_status => x_return_status,
216 x_msg_count => l_msg_count,
217 x_msg_data => x_err_msg);
218
219 if(l_msg_count > 1) then
220 inv_mobile_helper_functions.get_stacked_messages(x_err_msg);
221 end if;
222 EXCEPTION
223 when others then
224 x_return_status := fnd_api.g_ret_sts_unexp_error;
225 END packLpnContainer;
226
227 PROCEDURE packSerials(p_api_version IN NUMBER,
228 p_commit IN VARCHAR2,
229 p_lpn_id IN NUMBER,
230 p_content_item_id IN NUMBER,
231 p_revision IN VARCHAR2,
232 p_lot_number IN VARCHAR2,
233 p_from_serial_number IN VARCHAR2,
234 p_to_serial_number IN VARCHAR2,
235 p_quantity IN NUMBER,
236 p_organization_id IN NUMBER,
237 p_source_type_id IN NUMBER,
238 p_uom IN VARCHAR2,
239 x_return_status OUT NOCOPY VARCHAR2,
240 x_err_msg OUT NOCOPY VARCHAR2) IS
241
242 l_msg_count NUMBER;
243
244 BEGIN
245 wms_container_pub.pack_prepack_container (p_api_version => p_api_version,
246 p_commit => p_commit,
247 p_lpn_id => p_lpn_id,
248 p_content_item_id => p_content_item_id,
249 p_revision => p_revision,
250 p_lot_number => p_lot_number,
251 p_from_serial_number => p_from_serial_number,
252 p_to_serial_number => p_to_serial_number,
253 p_quantity => p_quantity,
254 p_organization_id => p_organization_id,
255 p_operation => 1,
256 p_source_type_id => p_source_type_id,
257 p_uom => p_uom,
258 x_return_status => x_return_status,
259 x_msg_count => l_msg_count,
260 x_msg_data => x_err_msg);
261
262 if(l_msg_count > 1) then
263 inv_mobile_helper_functions.get_stacked_messages(x_err_msg);
264 end if;
265 EXCEPTION
266 when others then
267 x_return_status := fnd_api.g_ret_sts_unexp_error;
268 END packSerials;
269
270 PROCEDURE createMO(p_organization_id IN NUMBER,
271 p_inventory_item_id IN NUMBER,
272 p_quantity IN NUMBER,
273 p_uom IN VARCHAR2,
274 p_lpn_id IN NUMBER,
275 p_reference_id IN NUMBER,
276 p_lot_number IN VARCHAR2,
277 p_revision IN VARCHAR2,
278 p_transaction_source_id IN NUMBER,
279 p_transaction_type_id IN NUMBER,
280 p_transaction_source_type_id IN NUMBER,
281 p_wms_process_flag IN NUMBER,
282 p_project_id IN NUMBER,
283 p_task_id IN NUMBER,
284 p_header_id IN OUT NOCOPY NUMBER,
285 x_line_id OUT NOCOPY NUMBER,
286 x_return_status OUT NOCOPY VARCHAR2,
287 x_err_msg OUT NOCOPY VARCHAR2) IS
288
289 l_msg_count NUMBER;
290
291 BEGIN
292 wms_task_dispatch_gen.create_mo (p_org_id => p_organization_id,
293 p_inventory_item_id => p_inventory_item_id,
294 p_qty => p_quantity,
295 p_uom => p_uom,
296 p_lpn => p_lpn_id,
297 p_reference_id => p_reference_id,
298 p_lot_number => p_lot_number,
299 p_revision => p_revision,
300 p_header_id => p_header_id,
301 p_project_id => p_project_id,
302 p_task_id => p_task_id,
303 x_line_id => x_line_id,
304 p_txn_source_id => p_transaction_source_id,
305 p_transaction_type_id => p_transaction_type_id,
306 p_transaction_source_type_id => p_transaction_source_type_id,
307 p_wms_process_flag => p_wms_process_flag,
308 x_return_status => x_return_status,
309 x_msg_count => l_msg_count,
310 x_msg_data => x_err_msg);
311 if(l_msg_count > 1) then
312 inv_mobile_helper_functions.get_stacked_messages(x_err_msg);
313 end if;
314 EXCEPTION
315 when others then
316 x_return_status := fnd_api.g_ret_sts_unexp_error;
317 END createMO;
318
319 PROCEDURE OkMOLines(p_lpn_id IN NUMBER,
320 x_return_status OUT NOCOPY VARCHAR2,
321 x_err_msg OUT NOCOPY VARCHAR2) IS
322
323 l_msg_count NUMBER;
324
325 BEGIN
326 wms_wip_integration.update_mo_line(p_lpn_id => p_lpn_id,
327 p_wms_process_flag => 1,
328 x_return_status => x_return_status,
329 x_msg_count => l_msg_count,
330 x_msg_data => x_err_msg);
331 if(l_msg_count > 1) then
332 inv_mobile_helper_functions.get_stacked_messages(x_err_msg);
333 end if;
334 EXCEPTION
335 when others then
336 x_return_status := fnd_api.g_ret_sts_unexp_error;
337 END OkMOLines;
338
339 PROCEDURE updateLpnContext(p_api_version IN NUMBER,
340 p_init_msg_list IN VARCHAR2,
341 p_commit IN VARCHAR2,
342 p_lpn_id IN NUMBER,
343 p_lpn_context IN NUMBER,
344 x_return_status OUT NOCOPY VARCHAR2,
345 x_err_msg OUT NOCOPY VARCHAR2) IS
346 lpnRec WMS_CONTAINER_PUB.LPN;
347 l_msg_count NUMBER;
348
349 BEGIN
350 lpnRec.lpn_id := p_lpn_id;
351 lpnRec.lpn_context := p_lpn_context;
352
353 wms_container_pub.Modify_LPN(p_api_version => p_api_version,
354 p_init_msg_list => p_init_msg_list,
355 p_commit => p_commit,
356 x_return_status => x_return_status,
357 x_msg_count => l_msg_count,
358 x_msg_data => x_err_msg,
359 p_lpn => lpnRec);
360 if(l_msg_count > 1) then
361 inv_mobile_helper_functions.get_stacked_messages(x_err_msg);
362 end if;
363 EXCEPTION
364 when others then
365 x_return_status := fnd_api.g_ret_sts_unexp_error;
366 END updateLpnContext;
367
368 PROCEDURE transferReservation(p_header_id IN NUMBER, --the header_id to the wlc table
369 p_subinventory_code IN VARCHAR2,
370 p_locator_id IN NUMBER,
371 p_primary_quantity IN NUMBER,
372 p_lpn_id IN NUMBER,
373 p_lot_number IN VARCHAR2,
374 x_return_status OUT NOCOPY VARCHAR2,
375 x_msg_count OUT NOCOPY NUMBER,
376 x_err_msg OUT NOCOPY VARCHAR2) IS
377 l_orgID NUMBER;
378 l_wipID NUMBER;
379 l_itemID NUMBER;
380 l_entityType NUMBER;
381 l_ctoItemCount NUMBER;
382 l_soExistsFlag NUMBER;
383 l_dummy VARCHAR2(1);
384 l_params wip_logger.param_tbl_t;
385 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
386 BEGIN
387 if(l_logLevel <= wip_constants.trace_logging) then
388 l_params(1).paramName := 'p_header_id';
389 l_params(1).paramValue := p_header_id;
390 l_params(2).paramName := 'p_subinventory_code';
391 l_params(2).paramValue := p_subinventory_code;
392 l_params(3).paramName := 'p_locator_id';
393 l_params(3).paramValue := p_locator_id;
394 l_params(4).paramName := 'p_primary_quantity';
395 l_params(4).paramValue := p_primary_quantity;
396 l_params(5).paramName := 'p_lpn_id';
397 l_params(5).paramValue := p_lpn_id;
398 l_params(6).paramName := 'p_lot_number';
399 l_params(6).paramValue := p_lot_number;
400
401 wip_logger.entryPoint(p_procName => 'wma_inv_wrappers.transferReservation',
402 p_params => l_params,
403 x_returnStatus => l_dummy);
404 end if;
405 update wip_lpn_completions
406 set subinventory_code = p_subinventory_code,
407 locator_id = p_locator_id
408 where header_id = p_header_id
409 returning wip_entity_id, organization_id, inventory_item_id, wip_entity_type
410 into l_wipID, l_orgID, l_itemID, l_entityType;
411
412 if(l_entityType = wip_constants.discrete) then
413 if(l_logLevel <= wip_constants.full_logging) then
414 wip_logger.log('discrete', l_dummy);
415 end if;
416 wip_so_reservations.allocate_completion_to_so(p_organization_id => l_orgID,
417 p_wip_entity_id => l_wipID,
418 p_inventory_item_id => l_itemID,
419 p_transaction_header_id => p_header_id,
420 p_table_type => 'WLC',
421 p_lpn_id => p_lpn_id,
422 p_primary_quantity => p_primary_quantity,
423 p_lot_number => p_lot_number,
424 x_return_status => x_return_status,
425 x_msg_count => x_msg_count,
426 x_msg_data => x_err_msg);
427 elsif(l_entityType = wip_constants.flow) then
428 if(l_logLevel <= wip_constants.full_logging) then
429 wip_logger.log('flow', l_dummy);
430 end if;
431 /* commented out as Sales Order will be entered through UI
432 --only transfer reservations for CTO items
433 select count(*)
434 into l_ctoItemCount
435 from mtl_system_items
436 where inventory_item_id = l_itemID
437 and organization_id = l_orgID
438 and build_in_wip_flag = 'Y'
439 and base_item_id is not null
440 and bom_item_type = wip_constants.standard_type
441 and replenish_to_order_flag = 'Y';
442 if(l_ctoItemCount > 0) then
443 */
444 select count(*)
445 into l_soExistsFlag
446 from wip_lpn_completions
447 where header_id = p_header_id
448 and demand_source_header_id is not null
449 and demand_source_line is not null;
450 if(l_soExistsFlag > 0) then
451 if(l_logLevel <= wip_constants.full_logging) then
452 wip_logger.log('found cto item', l_dummy);
453 end if;
454 wip_so_reservations.complete_flow_sched_to_so(p_header_id => p_header_id,
455 p_lpn_id => p_lpn_id,
456 p_primary_quantity => p_primary_quantity,
457 p_lot_number => p_lot_number,
458 x_return_status => x_return_status,
459 x_msg_count => x_msg_count,
460 x_msg_data => x_err_msg);
461 end if;
462 end if;
463 if(l_logLevel <= wip_constants.trace_logging) then
464 wip_logger.exitPoint(p_procName => 'wma_inv_wrappers.transferReservation',
465 p_procReturnStatus => x_return_status,
466 p_msg => 'success',
467 x_returnStatus => l_dummy);
468 end if;
469 exception
470 when others then
471 if(l_logLevel <= wip_constants.trace_logging) then
472 wip_logger.exitPoint(p_procName => 'wma_inv_wrappers.transferReservation',
473 p_procReturnStatus => x_return_status,
474 p_msg => 'error:' || x_err_msg,
475 x_returnStatus => l_dummy);
476 end if;
477 x_return_status := FND_API.G_RET_STS_ERROR;
478 wip_logger.log('unhandled exception ' || SQLERRM, l_dummy);
479 fnd_message.set_name('WIP', 'GENERIC_ERROR');
480 fnd_message.set_token('FUNCTION', 'wmainvwb.transferReservation');
481 fnd_message.set_token('ERROR', SQLERRM);
482 fnd_msg_pub.add;
483 fnd_msg_pub.count_and_get(p_count => x_msg_count,
484 p_data => x_err_msg);
485 end transferReservation;
486
487 PROCEDURE clearQtyTrees(x_return_status OUT NOCOPY VARCHAR2,
488 x_err_msg OUT NOCOPY VARCHAR2) is
489 begin
490 x_return_status := fnd_api.g_ret_sts_success;
491 inv_quantity_tree_pub.clear_quantity_cache;
492 exception
493 when others then
494 x_return_status := fnd_api.g_ret_sts_unexp_error;
495 fnd_msg_pub.add_exc_msg(p_pkg_name => 'wma_inv_wrappers',
496 p_procedure_name => 'clearQtyTrees',
497 p_error_text => SQLERRM);
498 end clearQtyTrees;
499 END wma_inv_wrappers;