[Home] [Help]
PACKAGE BODY: APPS.WIP_AUTOLOTPROC_PRIV
Source
1 package body wip_autoLotProc_priv as
2 /* $Header: wiplotpb.pls 120.3 2007/09/17 21:14:49 kboonyap ship $ */
3
4
5 -----------------------
6 --private package types
7 -----------------------
8
9 type num_tbl_t is table of number;
10 type char_tbl_t is table of varchar2(30);
11 type big_char_tbl_t is table of varchar2(2000);
12
13 type itemInfo_recTbl_t is record(txnID num_tbl_t, --either temp_id or interface_id
14 opSeqNum num_tbl_t,
15 itemID num_tbl_t,
16 itemName big_char_tbl_t,
17 priQty num_tbl_t,
18 lotPriQty num_tbl_t,
19 txnQty num_tbl_t,
20 priUomCode char_tbl_t,
21 supplySubinv char_tbl_t,
22 supplyLocID num_tbl_t,
23 -- wipSupplyType num_tbl_t,
24 txnActionID num_tbl_t,
25 txnsEnabledFlag char_tbl_t,
26 serialControlCode num_tbl_t,
27 lotControlCode num_tbl_t,
28 revision char_tbl_t,
29 movTxnID num_tbl_t,
30 cplTxnID num_tbl_t);
31
32
33 type itemInfo_rec_t is record(txnID NUMBER, --either temp_id or interface_id
34 opSeqNum NUMBER,
35 itemID NUMBER,
36 itemName VARCHAR2(2000),
37 priQty NUMBER,
38 lotPriQty NUMBER,
39 txnQty NUMBER,
40 priUomCode VARCHAR2(3),
41 supplySubinv VARCHAR2(30),
42 supplyLocID NUMBER,
43 wipSupplyType NUMBER,
44 txnActionID NUMBER,
45 txnsEnabledFlag VARCHAR2(1),
46 serialControlCode NUMBER,
47 lotControlCode NUMBER,
48 revision VARCHAR2(3));
49
50 ----------------------
51 --forward declarations
52 ----------------------
53
54 function worstReturnStatus(p_status1 VARCHAR2, p_status2 VARCHAR2) return VARCHAR2;
55 function getTxnType(p_txnActionID IN NUMBER) return NUMBER;
56
57 --checks to see if there are any serial requirements unfulfilled
58 procedure checkSerial(p_txnTmpID IN NUMBER,
59 p_txnIntID IN NUMBER,
60 p_itemID IN NUMBER,
61 p_itemName IN VARCHAR2,
62 p_orgID IN NUMBER,
63 p_revision IN VARCHAR2,
64 p_subinv IN VARCHAR2,
65 p_locID IN NUMBER,
66 p_qty IN NUMBER,
67 p_txnActionID IN NUMBER,
68 p_serControlCode IN NUMBER,
69 x_serialReturnStatus OUT NOCOPY VARCHAR2,
70 x_returnStatus OUT NOCOPY VARCHAR2);
71
72 --checks to see if there are enough serial numbers to fulfill open requirements
73 procedure checkSerialQuantity(p_itemID IN NUMBER,
74 p_itemName IN VARCHAR2,
75 p_orgID IN NUMBER,
76 p_qty IN NUMBER,
77 p_txnActionID IN NUMBER,
78 p_serControlCode IN NUMBER,
79 x_returnStatus OUT NOCOPY VARCHAR2);
80
81 procedure deriveSingleItem(p_orgID IN NUMBER,
82 p_wipEntityID IN NUMBER,
83 p_entryType IN NUMBER,
84 p_treeMode IN NUMBER,
85 p_treeSrcName IN VARCHAR2,
86 x_treeID IN OUT NOCOPY NUMBER, --the qty tree id if one was built
87 x_compLots IN OUT NOCOPY system.wip_lot_serial_obj_t,
88 x_returnStatus OUT NOCOPY VARCHAR2);
89
90 function findTxnAction(p_isForwardTxn IN VARCHAR2,
91 p_qty IN NUMBER) return number;
92
93 /* Fix for Bug#4956543. Added following lot_selected procedure
94 This procedure will return Lot Quantity populated by the system for a
95 particular Lot. Since Quantity Tree is not considering MTI records for
96 Quantity calculation, we need to look into interface tables
97 */
98 procedure lot_selected (
99 p_organization_id NUMBER,
100 p_inventory_item_id NUMBER,
101 p_sub_code VARCHAR2,
102 p_locator_id NUMBER,
103 p_lot_number VARCHAR2,
104 p_lot_qty_selected OUT NOCOPY NUMBER,
105 x_returnStatus OUT NOCOPY VARCHAR2);
106
107
108
109 ---------------------------
110 --public/private procedures
111 ---------------------------
112 procedure deriveLots(x_compLots IN OUT NOCOPY system.wip_lot_serial_obj_t,
113 p_orgID IN NUMBER,
114 p_wipEntityID IN NUMBER,
115 p_initMsgList IN VARCHAR2,
116 p_endDebug IN VARCHAR2,
117 p_destroyTrees IN VARCHAR2,
118 p_treeMode IN NUMBER,
119 p_treeSrcName IN VARCHAR2,
120 x_returnStatus OUT NOCOPY VARCHAR2) is
121 l_index NUMBER;
122 l_returnStatus VARCHAR2(1);
123 l_msgCount NUMBER;
124 l_msgData VARCHAR2(240);
125 l_numLotsDerived NUMBER;
126 l_curItem system.wip_component_obj_t;
127 l_treeID NUMBER;
128 l_prevItem NUMBER := -1;
129 l_lotTbl system.wip_txn_lot_tbl_t;
130 l_params wip_logger.param_tbl_t;
131 l_errMsg VARCHAR2(80);
132 l_entryType NUMBER;
133 l_logLevel NUMBER := to_number(fnd_log.g_current_runtime_level);
134 begin
135 x_returnStatus := fnd_api.g_ret_sts_success;
136 if (l_logLevel <= wip_constants.trace_logging) then
137 l_params(1).paramName := 'p_orgID';
138 l_params(1).paramValue := p_orgID;
139 l_params(2).paramName := 'p_wipEntityID';
140 l_params(2).paramValue := p_wipEntityID;
141 wip_logger.entryPoint(p_procName => 'wip_autoLotProc_priv.deriveLots',
142 p_params => l_params,
143 x_returnStatus => x_returnStatus);
144 end if;
145
146 if(fnd_api.to_boolean(p_initMsgList)) then
147 fnd_msg_pub.initialize;
148 end if;
149
150 select backflush_lot_entry_type
151 into l_entryType
152 from wip_parameters
153 where organization_id = p_orgID;
154
155
156 x_compLots.reset;
157 x_returnStatus := fnd_api.g_ret_sts_success; --assume we will be able to derive everything
158 loop
159 if(x_compLots.getCurrentItem(l_curItem)) then
160 if(l_curItem.wip_supply_type not in (wip_constants.push, wip_constants.op_pull, wip_constants.assy_pull)) then
161 goto END_OF_LOOP;
162 end if;
163
164 if(l_treeID is not null
165 and l_prevItem <> l_curItem.inventory_item_id) then
166 --if destroy trees is true, free the tree. otherwise, we just
167 --need to reset the l_treeID variable
168 if(fnd_api.to_boolean(p_destroyTrees)) then
169 inv_quantity_tree_pvt.free_tree(p_api_version_number => 1.0,
170 p_init_msg_lst => fnd_api.g_false,
171 p_tree_id => l_treeID,
172 x_return_status => l_returnStatus,
173 x_msg_count => l_msgCount,
174 x_msg_data => l_msgData);
175 end if;
176 l_treeID := null; --reset in out parameter
177 end if;
178
179 deriveSingleItem(p_orgID => p_orgID,
180 p_wipEntityID => p_wipEntityID,
181 p_entryType => l_entryType,
182 p_treeMode => p_treeMode,
183 p_treeSrcName => p_treeSrcName,
184 x_treeID => l_treeID,
185 x_compLots => x_compLots,
186 x_returnStatus => l_returnStatus);
187 if(l_returnStatus = fnd_api.g_ret_sts_unexp_error) then
188 l_errMsg := 'deriveSingleItem failed';
189 raise fnd_api.g_exc_unexpected_error;
190 elsif(l_returnStatus = fnd_api.g_ret_sts_error) then
191 x_returnStatus := fnd_api.g_ret_sts_error;
192 end if;
193
194 --set up data for the next iteration of the loop
195 l_prevItem := l_curItem.inventory_item_id;
196 end if;
197 <<END_OF_LOOP>>
198 exit when not x_compLots.setNextItem;
199 end loop;
200
201 --destroy the last tree if the user has not requested it persist
202 if(l_treeID is not null and
203 fnd_api.to_boolean(p_destroyTrees)) then
204 inv_quantity_tree_pvt.free_tree(p_api_version_number => 1.0,
205 p_init_msg_lst => fnd_api.g_false,
206 p_tree_id => l_treeID,
207 x_return_status => l_returnStatus,
208 x_msg_count => l_msgCount,
209 x_msg_data => l_msgData);
210 l_treeID := null;
211 end if;
212
213 if (l_logLevel <= wip_constants.trace_logging) then
214 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.deriveLots',
215 p_procReturnStatus => x_returnStatus,
216 p_msg => 'procedure success',
217 x_returnStatus => l_returnStatus); --discard logging return status
218 end if;
219 if(fnd_api.to_boolean(p_endDebug)) then
220 wip_logger.cleanup(x_returnStatus => l_returnStatus);
221 end if;
222 exception
223 when fnd_api.g_exc_unexpected_error then
224 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
225 if (l_logLevel <= wip_constants.trace_logging) then
226 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.deriveLots',
227 p_procReturnStatus => x_returnStatus,
228 p_msg => l_errMsg,
229 x_returnStatus => l_returnStatus); --discard logging return status
230 end if;
231 if(fnd_api.to_boolean(p_endDebug)) then
232 wip_logger.cleanup(x_returnStatus => l_returnStatus);
233 end if;
234 when others then
235 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
236 if (l_logLevel <= wip_constants.trace_logging) then
237 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.deriveLots',
238 p_procReturnStatus => x_returnStatus,
239 p_msg => 'unexpected error:' || SQLERRM,
240 x_returnStatus => l_returnStatus); --discard logging return status
241 end if;
242 fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_autoLotProc_priv',
243 p_procedure_name => 'deriveLots',
244 p_error_text => SQLERRM);
245 if(fnd_api.to_boolean(p_endDebug)) then
246 wip_logger.cleanup(x_returnStatus => l_returnStatus);
247 end if;
248 end deriveLots;
249 /*
250 procedure deriveLotsFromMMTT(p_orgID IN NUMBER,
251 p_wipEntityID IN NUMBER,
252 p_txnHdrID IN NUMBER,
253 p_cplTxnID IN NUMBER,
254 p_movTxnID IN NUMBER,
255 p_initMsgList IN VARCHAR2,
256 p_endDebug IN VARCHAR2,
257 x_returnStatus OUT NOCOPY VARCHAR2) is
258 l_itemRec itemInfo_rec_t;
259 l_index NUMBER := 1;
260 l_compObj system.wip_lot_serial_obj_t;
261 l_item system.wip_component_obj_t;
262 l_lot system.wip_txn_lot_obj_t;
263 l_lotReturnStatus VARCHAR2(1);
264 l_serialReturnStatus VARCHAR2(1);
265 l_tempReturnStatus VARCHAR2(1);
266 l_errMsg VARCHAR2(80);
267 l_params wip_logger.param_tbl_t;
268 l_returnStatus VARCHAR2(1);
269 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
270 cursor c_cplItems return itemInfo_rec_t is
271 select mmtt.transaction_temp_id,
272 mmtt.operation_seq_num,
273 mmtt.inventory_item_id,
274 msi.concatenated_segments,
275 mmtt.primary_quantity * -1,
276 sum(mtlt.primary_quantity),
277 mmtt.transaction_quantity * -1,
278 msi.primary_uom_code,
279 mmtt.subinventory_code,
280 mmtt.locator_id,
281 mmtt.wip_supply_type,
282 mmtt.transaction_action_id,
283 msi.mtl_transactions_enabled_flag,
284 msi.serial_number_control_code,
285 msi.lot_control_code,
286 mmtt.revision
287 from mtl_material_transactions_temp mmtt,
288 mtl_system_items_kfv msi,
289 mtl_transaction_lots_temp mtlt
290 where mmtt.completion_transaction_id = p_cplTxnID
291 and mmtt.transaction_action_id in (wip_constants.isscomp_action, wip_constants.retcomp_action,
292 wip_constants.issnegc_action, wip_constants.retnegc_action)
293 and mmtt.inventory_item_id = msi.inventory_item_id
294 and mmtt.organization_id = msi.organization_id
295 and mmtt.transaction_temp_id = mtlt.transaction_temp_id (+)
296 group by mmtt.transaction_temp_id,
297 mmtt.operation_seq_num,
298 mmtt.inventory_item_id,
299 msi.concatenated_segments,
300 mmtt.primary_quantity * -1,
301 mmtt.transaction_quantity * -1,
302 msi.primary_uom_code,
303 mmtt.subinventory_code,
304 mmtt.locator_id,
305 mmtt.wip_supply_type,
306 mmtt.transaction_action_id,
307 msi.mtl_transactions_enabled_flag,
308 msi.serial_number_control_code,
309 msi.lot_control_code,
310 mmtt.revision
311 order by mmtt.inventory_item_id, mmtt.transaction_temp_id;
312
313 cursor c_movItems return itemInfo_rec_t is
314 select mmtt.transaction_temp_id,
315 mmtt.operation_seq_num,
316 mmtt.inventory_item_id,
317 msi.concatenated_segments,
318 mmtt.primary_quantity * -1,
319 sum(mtlt.primary_quantity),
320 mmtt.transaction_quantity * -1,
321 msi.primary_uom_code,
322 mmtt.subinventory_code,
323 mmtt.locator_id,
324 mmtt.wip_supply_type,
325 mmtt.transaction_action_id,
326 msi.mtl_transactions_enabled_flag,
327 msi.serial_number_control_code,
328 msi.lot_control_code,
329 mmtt.revision
330 from mtl_material_transactions_temp mmtt,
331 mtl_system_items_kfv msi,
332 mtl_transaction_lots_temp mtlt
333 where mmtt.move_transaction_id = p_movTxnID
334 and mmtt.transaction_action_id in (wip_constants.isscomp_action, wip_constants.retcomp_action,
338 and mmtt.transaction_temp_id = mtlt.transaction_temp_id (+)
335 wip_constants.issnegc_action, wip_constants.retnegc_action)
336 and mmtt.inventory_item_id = msi.inventory_item_id
337 and mmtt.organization_id = msi.organization_id
339 group by mmtt.transaction_temp_id,
340 mmtt.operation_seq_num,
341 mmtt.inventory_item_id,
342 msi.concatenated_segments,
343 mmtt.primary_quantity * -1,
344 mmtt.transaction_quantity * -1,
345 msi.primary_uom_code,
346 mmtt.subinventory_code,
347 mmtt.locator_id,
348 mmtt.wip_supply_type,
349 mmtt.transaction_action_id,
350 msi.mtl_transactions_enabled_flag,
351 msi.serial_number_control_code,
352 msi.lot_control_code,
353 mmtt.revision
354 order by mmtt.inventory_item_id, mmtt.transaction_temp_id;
355 begin
356 savepoint wiplotpb_10;
357 x_returnStatus := fnd_api.g_ret_sts_success;
358
359 if (l_logLevel <= wip_constants.trace_logging) then
360 l_params(1).paramName := 'p_cplTxnID';
361 l_params(1).paramValue := p_cplTxnID;
362 l_params(2).paramName := 'p_movTxnID';
363 l_params(2).paramValue := p_movTxnID;
364 l_params(3).paramName := 'p_orgID';
365 l_params(3).paramValue := p_orgID;
366 l_params(4).paramName := 'p_wipEntityID';
367 l_params(4).paramValue := p_wipEntityID;
368 wip_logger.entryPoint(p_procName => 'wip_autoLotProc_priv.deriveLotsFromMMTT',
369 p_params => l_params,
370 x_returnStatus => x_returnStatus);
371 end if;
372
373 if(fnd_api.to_boolean(p_initMsgList)) then
374 fnd_msg_pub.initialize;
375 end if;
376
377 if(p_cplTxnID is not null) then
378 open c_cplItems;
379 else
380 open c_movItems;
381 end if;
382 l_compObj := system.wip_lot_serial_obj_t(null,null,null,null,null,null);
383 l_compObj.initialize;
384 loop
385 if(p_cplTxnID is not null) then
386 fetch c_cplItems into l_itemRec;
387 if(c_cplItems%NOTFOUND) then
388 close c_cplItems;
389 exit;
390 end if;
391 else
392 fetch c_movItems into l_itemRec;
393 if(c_movItems%NOTFOUND) then
394 close c_movItems;
395 exit;
396 end if;
397 end if;
398
399 if (l_logLevel <= wip_constants.full_logging) then
400 wip_logger.log('priQty:' || l_itemRec.priQty || '; lot quantity:' || l_itemRec.lotPriQty, l_returnStatus);
401 end if;
402 if(abs(l_itemRec.priQty) > nvl(l_itemRec.lotPriQty, 0)) then
403 if (l_logLevel <= wip_constants.full_logging) then
404 wip_logger.log('at item ' || l_itemRec.itemName,l_returnStatus);
405 end if;
406 if(l_itemRec.lotControlCode = wip_constants.lot) then
407 if (l_logLevel <= wip_constants.full_logging) then
408 wip_logger.log('adding item ' || l_itemRec.itemName || ';' || l_itemRec.itemID, l_returnStatus);
409 end if;
410 l_compObj.addItem(p_opSeqNum => l_itemRec.opSeqNum,
411 p_itemID => l_itemRec.itemID,
412 p_itemName => l_itemRec.itemName,
413 p_priQty => l_itemRec.priQty - sign(l_itemRec.priQty) * nvl(l_itemRec.lotPriQty, 0),
414 p_priUomCode => l_itemRec.priUomCode,
415 p_supplySubinv => l_itemRec.supplySubinv,
416 p_supplyLocID => l_itemRec.supplyLocID,
417 p_wipSupplyType => l_itemRec.wipSupplyType,
418 p_mtlTxnsEnabledFlag => l_itemRec.txnsEnabledFlag,
419 p_revision => l_itemRec.revision,
420 p_txnActionID => l_itemRec.txnActionID,
421 p_lotControlCode => l_itemRec.lotControlCode,
422 p_serialControlCode => l_itemRec.serialControlCode,
423 p_genericID => l_itemRec.txnID);
424 elsif(l_itemRec.serialControlCode in (wip_constants.full_sn, wip_constants.dyn_rcv_sn)) then
425 --see if we've derived the entire serial quantity
426 checkSerial(p_txnTmpID => l_itemRec.txnID,
427 p_txnIntID => null, --since using temp table
428 p_qty => abs(l_itemRec.priQty),
429 p_itemID => l_itemRec.itemID,
430 p_itemName => l_itemRec.itemName,
431 p_orgID => p_orgID,
432 p_revision => l_itemRec.revision,
433 p_subinv => l_itemRec.supplySubinv,
434 p_locID => l_itemRec.supplyLocID,
435 p_txnActionID => l_itemRec.txnActionID,
436 p_serControlCode => l_itemRec.serialControlCode,
437 x_serialReturnStatus => l_tempReturnStatus,
438 x_returnStatus => x_returnStatus);
442 end if;
439 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
440 l_errMsg := 'check serial failed';
441 raise fnd_api.g_exc_unexpected_error;
443 l_serialReturnStatus := worstReturnStatus(l_serialReturnStatus, l_tempReturnStatus);
444 end if;
445 end if;
446
447 end loop;
448 deriveLots(x_compLots => l_compObj,
449 p_orgID => p_orgID,
450 p_wipEntityID => p_wipEntityID,
451 p_initMsgList => fnd_api.g_false,
452 p_endDebug => fnd_api.g_false,
453 p_destroyTrees => fnd_api.g_true,
454 p_treeMode => inv_quantity_tree_pvt.g_reservation_mode,
455 p_treeSrcName => null,
456 x_returnStatus => l_lotReturnStatus);
457
458 x_returnStatus := worstReturnStatus(l_serialReturnStatus, l_lotReturnStatus);
459 if(x_returnStatus = fnd_api.g_ret_sts_unexp_error) then
460 l_errMsg := 'derive lots failed';
461 raise fnd_api.g_exc_unexpected_error;
462 end if;
463 --otherwise we at least derived some lot info
464 if(p_cplTxnID is not null) then
465 open c_cplItems;
466 else
467 open c_movItems;
468 end if;
469
470 l_compObj.reset;
471 --2nd pass: update all the mmtt rows with lot info
472 loop
473 <<START_OF_OUTER_LOOP>>
474 if(p_cplTxnID is not null) then
475 fetch c_cplItems into l_itemRec;
476 if(c_cplItems%NOTFOUND) then
477 close c_cplItems;
478 exit;
479 end if;
480 else
481 fetch c_movItems into l_itemRec;
482 if(c_movItems%NOTFOUND) then
483 close c_movItems;
484 exit;
485 end if;
486 end if;
487
488 if (l_logLevel <= wip_constants.full_logging) then
489 wip_logger.log('start outer loop for item' || l_itemRec.itemID, l_returnStatus);
490 end if;
491
492 if(l_itemRec.lotControlCode <> wip_constants.lot) then
493 goto START_OF_OUTER_LOOP; --skip this item if it's not lot controlled
494 end if;
495
496 loop
497 if (l_logLevel <= wip_constants.full_logging) then
498 wip_logger.log('start inner loop1',l_returnStatus);
499 end if;
500 if(l_compObj.setNextItem) then
501 if(not l_compObj.getCurrentItem(l_item)) then
502 l_errMsg := 'object error';
503 raise fnd_api.g_exc_unexpected_error;
504 end if;
505 if (l_logLevel <= wip_constants.full_logging) then
506 wip_logger.log('found item: ' || l_item.inventory_item_id, l_returnStatus);
507 end if;
508
509 if(l_item.inventory_item_id = l_itemRec.itemID and
510 l_item.supply_subinventory = l_itemRec.supplySubinv and
511 nvl(l_item.supply_locator_id, -1) = nvl(l_itemRec.supplyLocID, -1) and
512 l_item.operation_seq_num = l_itemRec.opSeqNum and
513 l_item.primary_quantity = l_itemRec.priQty) then
514 if (l_logLevel <= wip_constants.full_logging) then
515 wip_logger.log('item: ' || l_item.inventory_item_id || ' matches cursor item', l_returnStatus);
516 end if;
517 exit; --found an item to match the cursor
518 end if;
519 else
520 if (l_logLevel <= wip_constants.full_logging) then
521 wip_logger.log('ran out of items', l_returnStatus);
522 end if;
523 goto END_OF_OUTER_LOOP; --must exit inner and outer loop!
524 end if;
525 end loop;
526
527
528 while(l_compObj.getNextLot(l_lot)) loop
529 if (l_logLevel <= wip_constants.full_logging) then
530 wip_logger.log('start inner loop2', l_returnStatus);
531 end if;
532 insert into mtl_transaction_lots_temp
533 (transaction_temp_id,
534 last_update_date,
535 last_updated_by,
536 creation_date,
537 created_by,
538 last_update_login,
539 request_id,
540 program_application_id,
541 program_id,
542 program_update_date,
543 transaction_quantity,
544 primary_quantity,
545 lot_number)
546 values
547 (l_itemRec.txnID,
548 sysdate,
549 fnd_global.user_id,
550 sysdate,
551 fnd_global.user_id,
552 fnd_global.login_id,
553 fnd_global.conc_request_id,
554 fnd_global.prog_appl_id,
555 fnd_global.conc_program_id,
556 sysdate,
557 abs(round(l_lot.primary_quantity * (l_itemRec.txnQty/
558 l_itemRec.priQty), wip_constants.inv_max_precision)),
559 abs(round(l_lot.primary_quantity, wip_constants.inv_max_precision)),
560 l_lot.lot_number);
561 end loop;
562 if (l_logLevel <= wip_constants.full_logging) then
563 wip_logger.log('end outer loop', l_returnStatus);
564 end if;
565 end loop;
566 <<END_OF_OUTER_LOOP>>
567 if(c_cplItems%ISOPEN) then
568 close c_cplItems;
569 elsif(c_movItems%ISOPEN) then
570 close c_movItems;
571 end if;
572
573 --return status has already been set at this point
577 p_msg => 'procedure success',
574 if (l_logLevel <= wip_constants.trace_logging) then
575 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.deriveLotsFromMMTT',
576 p_procReturnStatus => x_returnStatus,
578 x_returnStatus => l_returnStatus); --discard logging return status
579 end if;
580 if(fnd_api.to_boolean(p_endDebug)) then
581 wip_logger.cleanup(x_returnStatus => l_returnStatus);
582 end if;
583 exception
584 when fnd_api.g_exc_unexpected_error then
585 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
586 rollback to wiplotpb_10;
587 if (l_logLevel <= wip_constants.trace_logging) then
588 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.deriveLotsFromMMTT',
589 p_procReturnStatus => x_returnStatus,
590 p_msg => l_errMsg,
591 x_returnStatus => l_returnStatus); --discard logging return status
592 end if;
593 if(fnd_api.to_boolean(p_endDebug)) then
594 wip_logger.cleanup(x_returnStatus => l_returnStatus);
595 end if;
596 when others then
597 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
598 rollback to wiplotpb_10;
599 fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_autoLotProc_priv',
600 p_procedure_name => 'deriveLotsFromMMTT',
601 p_error_text => SQLERRM);
602 if (l_logLevel <= wip_constants.trace_logging) then
603 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.deriveLotsFromMMTT',
604 p_procReturnStatus => x_returnStatus,
605 p_msg => 'unexpected error:' || SQLERRM,
606 x_returnStatus => l_returnStatus); --discard logging return status
607 end if;
608 if(fnd_api.to_boolean(p_endDebug)) then
609 wip_logger.cleanup(x_returnStatus => l_returnStatus);
610 end if;
611 end deriveLotsFromMMTT;
612 */
613
614 procedure deriveLotsFromMTI(p_orgID IN NUMBER,
615 p_wipEntityID IN NUMBER, --populate for returns
616 p_txnHdrID IN NUMBER,
617 p_cplTxnID IN NUMBER := null,
618 p_movTxnID IN NUMBER := null,
619 p_childMovTxnID IN NUMBER := null,
620 p_initMsgList IN VARCHAR2,
621 p_endDebug IN VARCHAR2,
622 x_returnStatus OUT NOCOPY VARCHAR2) is
623 l_itemRec itemInfo_rec_t;
624 l_index NUMBER := 1;
625 l_compObj system.wip_lot_serial_obj_t;
626 l_item system.wip_component_obj_t;
627 l_lot system.wip_txn_lot_obj_t;
628 l_lotReturnStatus VARCHAR2(1);
629 l_serialReturnStatus VARCHAR2(1);
630 l_tempReturnStatus VARCHAR2(1);
631 l_errMsg VARCHAR2(80);
632 l_params wip_logger.param_tbl_t;
633 l_returnStatus VARCHAR2(1);
634 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
635 l_itemRecTbl itemInfo_recTbl_t;
636 l_supType NUMBER;
637
638 cursor c_allItems is
639 select mti.transaction_interface_id,
640 mti.operation_seq_num,
641 mti.inventory_item_id,
642 msi.concatenated_segments,
643 mti.primary_quantity * -1,
644 sum(mtli.primary_quantity),
645 mti.transaction_quantity * -1,
646 msi.primary_uom_code,
647 mti.subinventory_code,
648 mti.locator_id,
649 -- null,--mti.wip_supply_type,
650 mti.transaction_action_id,
651 msi.mtl_transactions_enabled_flag,
652 msi.serial_number_control_code,
653 msi.lot_control_code,
654 mti.revision,
655 mti.move_transaction_id,
656 mti.completion_transaction_id
657 from mtl_transactions_interface mti,
658 mtl_system_items_kfv msi,
659 mtl_transaction_lots_interface mtli
660 where mti.transaction_header_id = p_txnHdrID
661 and mti.transaction_action_id in (wip_constants.isscomp_action, wip_constants.retcomp_action,
662 wip_constants.issnegc_action, wip_constants.retnegc_action)
663 and mti.inventory_item_id = msi.inventory_item_id
664 and mti.organization_id = msi.organization_id
665 and mti.transaction_interface_id = mtli.transaction_interface_id (+)
666 group by mti.transaction_interface_id,
667 mti.operation_seq_num,
668 mti.inventory_item_id,
669 msi.concatenated_segments,
670 mti.primary_quantity * -1,
671 mti.transaction_quantity * -1,
672 msi.primary_uom_code,
673 mti.subinventory_code,
674 mti.locator_id,
675 -- null,--mti.wip_supply_type,
676 mti.transaction_action_id,
677 msi.mtl_transactions_enabled_flag,
678 msi.serial_number_control_code,
679 msi.lot_control_code,
680 mti.revision,
681 mti.move_transaction_id,
682 mti.completion_transaction_id
683 order by mti.inventory_item_id, mti.transaction_interface_id;
684
685 --backflush items
686 cursor c_bflItems is
690 msi.concatenated_segments,
687 select mti.transaction_interface_id,
688 mti.operation_seq_num,
689 mti.inventory_item_id,
691 mti.primary_quantity * -1,
692 sum(mtli.primary_quantity),
693 mti.transaction_quantity * -1,
694 msi.primary_uom_code,
695 mti.subinventory_code,
696 mti.locator_id,
697 -- null,--mti.wip_supply_type,
698 mti.transaction_action_id,
699 msi.mtl_transactions_enabled_flag,
700 msi.serial_number_control_code,
701 msi.lot_control_code,
702 mti.revision,
703 mti.move_transaction_id,
704 mti.completion_transaction_id
705 from mtl_transactions_interface mti,
706 mtl_system_items_kfv msi,
707 mtl_transaction_lots_interface mtli
708 where mti.transaction_header_id = p_txnHdrID
709 and ( mti.completion_transaction_id = p_cplTxnID
710 or mti.move_transaction_id in (p_movTxnID, p_childMovTxnID))
711 and mti.transaction_action_id in (wip_constants.isscomp_action, wip_constants.retcomp_action,
712 wip_constants.issnegc_action, wip_constants.retnegc_action)
713 and mti.inventory_item_id = msi.inventory_item_id
714 and mti.organization_id = msi.organization_id
715 and mti.transaction_interface_id = mtli.transaction_interface_id (+)
716 group by mti.transaction_interface_id,
717 mti.operation_seq_num,
718 mti.inventory_item_id,
719 msi.concatenated_segments,
720 mti.primary_quantity * -1,
721 mti.transaction_quantity * -1,
722 msi.primary_uom_code,
723 mti.subinventory_code,
724 mti.locator_id,
725 -- null,--mti.wip_supply_type,
726 mti.transaction_action_id,
727 msi.mtl_transactions_enabled_flag,
728 msi.serial_number_control_code,
729 msi.lot_control_code,
730 mti.revision,
731 mti.move_transaction_id,
732 mti.completion_transaction_id
733 order by mti.inventory_item_id, mti.transaction_interface_id;
734
735 /* no move transaction id in MTI so don''t support for now
736 cursor c_movItems return itemInfo_rec_t is
737 select mti.transaction_temp_id,
738 mti.operation_seq_num,
739 mti.inventory_item_id,
740 msi.concatenated_segments,
741 mti.primary_quantity * -1,
742 sum(mtli.primary_quantity),
743 mti.transaction_quantity * -1,
744 msi.primary_uom_code,
745 mti.subinventory_code,
746 mti.locator_id,
747 mti.wip_supply_type,
748 msi.mtl_transactions_enabled_flag,
749 msi.serial_number_control_code,
750 msi.lot_control_code,
751 mti.revision
752 from mtl_material_transactions_temp mti,
753 mtl_system_items_kfv msi,
754 mtl_transaction_lots_temp mtlt
755 where mti.move_transaction_id = p_movTxnID
756 and mti.transaction_action_id in (wip_constants.isscomp_action, wip_constants.retcomp_action,
757 wip_constants.issnegc_action, wip_constants.retnegc_action)
758 and mti.inventory_item_id = msi.inventory_item_id
759 and mti.organization_id = msi.organization_id
760 and mti.transaction_interface_id = mtlt.transaction_interface_id (+)
761 group by mti.transaction_temp_id,
762 mti.operation_seq_num,
763 mti.inventory_item_id,
764 msi.concatenated_segments,
765 mti.primary_quantity * -1,
766 mti.transaction_quantity * -1,
767 msi.primary_uom_code,
768 mti.subinventory_code,
769 mti.locator_id,
770 mti.wip_supply_type,
771 msi.mtl_transactions_enabled_flag,
772 msi.serial_number_control_code,
773 msi.lot_control_code,
774 mti.revision
775 order by mti.inventory_item_id, mti.transaction_temp_id;
776 */
777 begin
778 x_returnStatus := fnd_api.g_ret_sts_success;
779 savepoint wiplotpb_10;
780
781 if (l_logLevel <= wip_constants.trace_logging) then
782 l_params(1).paramName := 'p_txnHdrID';
783 l_params(1).paramValue := p_txnHdrID;
784 l_params(2).paramName := 'p_cplTxnID';
785 l_params(2).paramValue := p_cplTxnID;
786 l_params(3).paramName := 'p_movTxnID';
787 l_params(3).paramValue := p_movTxnID;
788 l_params(4).paramName := 'p_orgID';
789 l_params(4).paramValue := p_orgID;
790 l_params(5).paramName := 'p_wipEntityID';
791 l_params(5).paramValue := p_wipEntityID;
792 wip_logger.entryPoint(p_procName => 'wip_autoLotProc_priv.deriveLotsFromMTI',
793 p_params => l_params,
794 x_returnStatus => x_returnStatus);
795 end if;
796
797 if(fnd_api.to_boolean(p_initMsgList)) then
798 fnd_msg_pub.initialize;
799 end if;
800
801 l_compObj := system.wip_lot_serial_obj_t(null,null,null,null,null,null);
802 l_compObj.initialize;
803
804 if(p_cplTxnID is null and p_movTxnID is null) then
805 open c_allItems;
806 fetch c_allItems
810 l_itemRecTbl.itemName,
807 bulk collect into l_itemRecTbl.txnID,
808 l_itemRecTbl.opSeqNum,
809 l_itemRecTbl.itemID,
811 l_itemRecTbl.priQty,
812 l_itemRecTbl.lotPriQty,
813 l_itemRecTbl.txnQty,
814 l_itemRecTbl.priUomCode,
815 l_itemRecTbl.supplySubinv,
816 l_itemRecTbl.supplyLocID,
817 -- l_itemRecTbl.wipSupplyType,
818 l_itemRecTbl.txnActionID,
819 l_itemRecTbl.txnsEnabledFlag,
820 l_itemRecTbl.serialControlCode,
821 l_itemRecTbl.lotControlCode,
822 l_itemRecTbl.revision,
823 l_itemRecTbl.movTxnID,
824 l_itemRecTbl.cplTxnID;
825
826 close c_allItems;
827 else
828 open c_bflItems;
829 fetch c_bflItems
830 bulk collect into l_itemRecTbl.txnID,
831 l_itemRecTbl.opSeqNum,
832 l_itemRecTbl.itemID,
833 l_itemRecTbl.itemName,
834 l_itemRecTbl.priQty,
835 l_itemRecTbl.lotPriQty,
836 l_itemRecTbl.txnQty,
837 l_itemRecTbl.priUomCode,
838 l_itemRecTbl.supplySubinv,
839 l_itemRecTbl.supplyLocID,
840 -- l_itemRecTbl.wipSupplyType,
841 l_itemRecTbl.txnActionID,
842 l_itemRecTbl.txnsEnabledFlag,
843 l_itemRecTbl.serialControlCode,
844 l_itemRecTbl.lotControlCode,
845 l_itemRecTbl.revision,
846 l_itemRecTbl.movTxnID,
847 l_itemRecTbl.cplTxnID;
848
849 close c_bflItems;
850 end if;
851
852 for i in 1..l_itemRecTbl.itemID.count loop
853 <<START_OF_OUTER_LOOP>>
854 if (l_logLevel <= wip_constants.full_logging) then
855 wip_logger.log('priQty:' || l_itemRecTbl.priQty(i) || '; lot quantity:' || l_itemRecTbl.lotPriQty(i), l_returnStatus);
856 end if;
857
858 if(abs(l_itemRecTbl.priQty(i)) > abs(nvl(l_itemRecTbl.lotPriQty(i), 0))) then
859 if (l_logLevel <= wip_constants.full_logging) then
860 wip_logger.log('adding item ' || l_itemRecTbl.itemName(i) || ';' || l_itemRecTbl.itemID(i), l_returnStatus);
861 end if;
862
863 if(l_itemRecTbl.lotControlCode(i) = wip_constants.lot) then
864 if(l_itemRecTbl.movTxnID(i) is not null) then
865 l_supType := wip_constants.op_pull;
866 elsif(l_itemRecTbl.cplTxnID(i) is not null) then
867 l_supType := wip_constants.assy_pull;
868 else
869 l_supType := wip_constants.push;
870 end if;
871
872 l_compObj.addItem(p_opSeqNum => l_itemRecTbl.opSeqNum(i),
873 p_itemID => l_itemRecTbl.itemID(i),
874 p_itemName => l_itemRecTbl.itemName(i),
875 p_priQty => l_itemRecTbl.priQty(i) - sign(l_itemRecTbl.priQty(i)) * nvl(l_itemRecTbl.lotPriQty(i), 0),
876 p_priUomCode => l_itemRecTbl.priUomCode(i),
877 p_supplySubinv => l_itemRecTbl.supplySubinv(i),
878 p_supplyLocID => l_itemRecTbl.supplyLocID(i),
879 p_wipSupplyType => l_supType,
880 p_txnActionID => l_itemRecTbl.txnActionID(i),
881 p_mtlTxnsEnabledFlag => l_itemRecTbl.txnsEnabledFlag(i),
882 p_revision => l_itemRecTbl.revision(i),
883 p_lotControlCode => l_itemRecTbl.lotControlCode(i),
884 p_serialControlCode => l_itemRecTbl.serialControlCode(i),
885 p_genericID => l_itemRecTbl.txnID(i));
886 elsif(l_itemRecTbl.serialControlCode(i) in (wip_constants.full_sn, wip_constants.dyn_rcv_sn)) then
887 --see if we've derived the entire serial quantity
888 checkSerial(p_txnTmpID => null, --since using interface table
889 p_txnIntID => l_itemRecTbl.txnID(i),
890 p_qty => abs(l_itemRecTbl.priQty(i)),
891 p_itemID => l_itemRecTbl.itemID(i),
892 p_itemName => l_itemRecTbl.itemName(i),
893 p_orgID => p_orgID,
894 p_revision => l_itemRecTbl.revision(i),
895 p_subinv => l_itemRecTbl.supplySubinv(i),
896 p_locID => l_itemRecTbl.supplyLocID(i),
897 p_txnActionID => l_itemRecTbl.txnActionID(i),
898 p_serControlCode => l_itemRecTbl.serialControlCode(i),
899 x_serialReturnStatus => l_tempReturnStatus,
900 x_returnStatus => x_returnStatus);
901 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
902 l_errMsg := 'check serial failed';
903 raise fnd_api.g_exc_unexpected_error;
904 end if;
905 l_serialReturnStatus := worstReturnStatus(l_serialReturnStatus, l_tempReturnStatus);
906 end if;
907 end if;
908 end loop;
909
910 deriveLots(x_compLots => l_compObj,
914 p_endDebug => fnd_api.g_false,
911 p_orgID => p_orgID,
912 p_wipEntityID => p_wipEntityID,
913 p_initMsgList => fnd_api.g_false,
915 p_destroyTrees => fnd_api.g_true,
916 p_treeMode => inv_quantity_tree_pvt.g_reservation_mode,
917 p_treeSrcName => null,
918 x_returnStatus => l_lotReturnStatus);
919
920 x_returnStatus := worstReturnStatus(l_serialReturnStatus, l_lotReturnStatus);
921 if(x_returnStatus = fnd_api.g_ret_sts_unexp_error) then
922 l_errMsg := 'derive lots failed';
923 raise fnd_api.g_exc_unexpected_error;
924 end if;
925
926 --otherwise we at least derived some lot info
927 l_compObj.reset;
928 --2nd pass: update all the mti rows with lot info
929 for i in 1..l_itemRecTbl.txnID.count loop
930 if (l_logLevel <= wip_constants.full_logging) then
931 wip_logger.log('start outer loop for item' || l_itemRecTbl.itemID(i), l_returnStatus);
932 end if;
933
934 if(l_itemRecTbl.lotControlCode(i) <> wip_constants.lot) then
935 goto END_OF_OUTER_LOOP;
936 end if;
937
938 loop
939 if (l_logLevel <= wip_constants.full_logging) then
940 wip_logger.log('start inner loop1', l_returnStatus);
941 end if;
942 if(l_compObj.setNextItem) then
943 if(not l_compObj.getCurrentItem(l_item)) then
944 l_errMsg := 'object error';
945 raise fnd_api.g_exc_unexpected_error;
946 end if;
947 if (l_logLevel <= wip_constants.full_logging) then
948 wip_logger.log('found item: ' || l_item.inventory_item_id, l_returnStatus);
949 end if;
950
951 if(l_item.inventory_item_id = l_itemRecTbl.itemID(i) and
952 l_item.supply_subinventory = l_itemRecTbl.supplySubinv(i) and
953 nvl(l_item.supply_locator_id, -1) = nvl(l_itemRecTbl.supplyLocID(i), -1) and
954 l_item.operation_seq_num = l_itemRecTbl.opSeqNum(i) and
955 l_item.primary_quantity = l_itemRecTbl.priQty(i)) then
956 if (l_logLevel <= wip_constants.full_logging) then
957 wip_logger.log('item: ' || l_item.inventory_item_id || ' matches cursor item', l_returnStatus);
958 end if;
959 exit; --found an item to match the cursor
960 end if;
961 else
962 if (l_logLevel <= wip_constants.full_logging) then
963 wip_logger.log('ran out of items', l_returnStatus);
964 end if;
965 goto END_OF_OUTER_LOOP; --must exit inner and outer loop!
966 end if;
967 end loop;
968
969
970 while(l_compObj.getNextLot(l_lot)) loop
971 if (l_logLevel <= wip_constants.full_logging) then
972 wip_logger.log('start inner loop2', l_returnStatus);
973 end if;
974 insert into mtl_transaction_lots_interface
975 (transaction_interface_id,
976 last_update_date,
977 last_updated_by,
978 creation_date,
979 created_by,
980 last_update_login,
981 request_id,
982 program_application_id,
983 program_id,
984 program_update_date,
985 transaction_quantity,
986 primary_quantity,
987 lot_number)
988 values
989 (l_itemRecTbl.txnID(i),
990 sysdate,
991 fnd_global.user_id,
992 sysdate,
993 fnd_global.user_id,
994 fnd_global.login_id,
995 fnd_global.conc_request_id,
996 fnd_global.prog_appl_id,
997 fnd_global.conc_program_id,
998 sysdate,
999 abs(round(l_lot.primary_quantity * (l_itemRecTbl.txnQty(i)/
1000 l_itemRecTbl.priQty(i)), wip_constants.inv_max_precision)),
1001 abs(round(l_lot.primary_quantity, wip_constants.inv_max_precision)),
1002 l_lot.lot_number);
1003 end loop;
1004 <<END_OF_OUTER_LOOP>>
1005 if (l_logLevel <= wip_constants.full_logging) then
1006 wip_logger.log('end outer loop', l_returnStatus);
1007 end if;
1008 end loop;
1009
1010 --return status has already been set at this point
1011 if (l_logLevel <= wip_constants.trace_logging) then
1012 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.deriveLotsFromMTI',
1013 p_procReturnStatus => x_returnStatus,
1014 p_msg => 'procedure success',
1015 x_returnStatus => l_returnStatus); --discard logging return status
1016 end if;
1017 if(fnd_api.to_boolean(p_endDebug)) then
1018 wip_logger.cleanup(x_returnStatus => l_returnStatus);
1019 end if;
1020 exception
1021 when fnd_api.g_exc_unexpected_error then
1022 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1023 rollback to wiplotpb_10;
1024 if (l_logLevel <= wip_constants.trace_logging) then
1025 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.deriveLotsFromMTI',
1026 p_procReturnStatus => x_returnStatus,
1027 p_msg => l_errMsg,
1028 x_returnStatus => l_returnStatus); --discard logging return status
1029 end if;
1030 if(fnd_api.to_boolean(p_endDebug)) then
1031 wip_logger.cleanup(x_returnStatus => l_returnStatus);
1035 rollback to wiplotpb_10;
1032 end if;
1033 when others then
1034 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1036 fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_autoLotProc_priv',
1037 p_procedure_name => 'deriveLotsFromMTI',
1038 p_error_text => SQLERRM);
1039 if (l_logLevel <= wip_constants.trace_logging) then
1040 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.deriveLotsFromMTI',
1041 p_procReturnStatus => x_returnStatus,
1042 p_msg => 'unexpected error:' || SQLERRM,
1043 x_returnStatus => l_returnStatus); --discard logging return status
1044 end if;
1045 if(fnd_api.to_boolean(p_endDebug)) then
1046 wip_logger.cleanup(x_returnStatus => l_returnStatus);
1047 end if;
1048 end deriveLotsFromMTI;
1049
1050
1051 --derive lots procedure for return txns
1052 procedure deriveTxnLots(p_orgID IN NUMBER,
1053 p_wipEntityID IN NUMBER, --only needed for returns and neg returns
1054 p_txnActionID IN NUMBER,
1055 p_entryType IN NUMBER,
1056 x_compLots IN OUT NOCOPY system.wip_lot_serial_obj_t,
1057 x_returnStatus OUT NOCOPY VARCHAR2) is
1058
1059 cursor c_retTxnBasedLots(v_itemID NUMBER) is
1060 select tln.lot_number,
1061 max(mln.expiration_date),
1062 abs(round(sum(tln.primary_quantity), wip_constants.max_displayed_precision))
1063 from mtl_transaction_lot_numbers tln,
1064 mtl_material_transactions mmt,
1065 mtl_lot_numbers mln
1066 where tln.organization_id = p_orgID
1067 and tln.transaction_source_id = p_wipEntityID
1068 and tln.transaction_source_type_id = 5
1069 and tln.inventory_item_id = v_itemID
1070 and tln.organization_id = mln.organization_id
1071 and tln.inventory_item_id = mln.inventory_item_id
1072 and tln.lot_number = mln.lot_number
1073 and nvl(mln.expiration_date, sysdate + 1) > sysdate
1074 and mmt.transaction_id = tln.transaction_id
1075 and mmt.transaction_action_id in (wip_constants.isscomp_action, wip_constants.retcomp_action)
1076 group by tln.lot_number
1077 having sign(round(sum(tln.primary_quantity), wip_constants.max_displayed_precision)) < 0 --more issued quantity than returned
1078 order by max(sign(round(tln.primary_quantity, wip_constants.max_displayed_precision))), --give priority to lots that have ret txns
1079 max(tln.transaction_date) desc, --then sort by most recent txn date
1080 tln.lot_number desc; --finally sort by lot number, descending b/c issues are ascending
1081
1082 cursor c_negRetTxnBasedLots(v_itemID NUMBER) is
1083 select tln.lot_number,
1084 max(mln.expiration_date),
1085 abs(round(sum(tln.primary_quantity), wip_constants.max_displayed_precision))
1086 from mtl_transaction_lot_numbers tln,
1087 mtl_material_transactions mmt,
1088 mtl_lot_numbers mln
1089 where tln.organization_id = p_orgID
1090 and tln.transaction_source_id = p_wipEntityID
1091 and tln.transaction_source_type_id = 5
1092 and tln.inventory_item_id = v_itemID
1093 and tln.organization_id = mln.organization_id
1094 and tln.inventory_item_id = mln.inventory_item_id
1095 and tln.lot_number = mln.lot_number
1096 and nvl(mln.expiration_date, sysdate + 1) > sysdate
1097 and mmt.transaction_id = tln.transaction_id
1098 and mmt.transaction_action_id in (wip_constants.issnegc_action, wip_constants.retnegc_action)
1099 group by tln.lot_number
1100 having sign(round(sum(tln.primary_quantity), wip_constants.max_displayed_precision)) > 0 --more neg issues than neg returns
1101 order by max(sign(round(tln.primary_quantity, wip_constants.max_displayed_precision))), --give priority to lots that have ret txns
1102 max(tln.transaction_date) desc, --then sort by most recent txn date
1103 tln.lot_number desc; --finally sort by lot number, descending b/c issues are ascending
1104
1105 l_item system.wip_component_obj_t;
1106 l_rmnQty NUMBER;
1107 /* ER 4378835: Increased length of l_lotNumber from 30 to 80 to support OPM Lot-model changes */
1108 l_lotNumber VARCHAR2(80);
1109 l_expDate DATE;
1110 l_lotQty NUMBER;
1111 l_cond boolean;
1112 l_params wip_logger.param_tbl_t;
1113 l_errMsg VARCHAR2(80);
1114 l_returnStatus VARCHAR2(1);
1115 l_enabled VARCHAR2(1);
1116 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
1117 begin
1118 x_returnStatus := fnd_api.g_ret_sts_success;
1119 l_cond := x_compLots.getCurrentItem(l_item);
1120
1121 if (l_logLevel <= wip_constants.trace_logging) then
1122 l_params(1).paramName := 'p_orgID';
1123 l_params(1).paramValue := p_orgID;
1124 l_params(2).paramName := 'p_wipEntityID';
1125 l_params(2).paramValue := p_wipEntityID;
1126 l_params(3).paramName := 'p_txnActionID';
1127 l_params(3).paramValue := p_txnActionID;
1128 if(l_cond) then
1129 l_params(4).paramName := 'x_compLot(cur_item).inventory_item_id';
1133 l_params(6).paramName := 'x_compLot(cur_item).supply_locator_id';
1130 l_params(4).paramValue := l_item.inventory_item_id;
1131 l_params(5).paramName := 'x_compLot(cur_item).supply_subinventory';
1132 l_params(5).paramValue := l_item.supply_subinventory;
1134 l_params(6).paramValue := l_item.supply_locator_id;
1135 l_params(7).paramName := 'x_compLot(cur_item).revision';
1136 l_params(7).paramValue := l_item.revision;
1137 l_params(8).paramName := 'x_compLot(cur_item).primary_quantity';
1138 l_params(8).paramValue := l_item.primary_quantity;
1139 end if;
1140 wip_logger.entryPoint(p_procName => 'wip_autoLotProc_priv.deriveTxnLots',
1141 p_params => l_params,
1142 x_returnStatus => x_returnStatus);
1143 end if;
1144
1145 if (not l_cond) then
1146 l_errMsg := 'current item not set';
1147 raise fnd_api.g_exc_unexpected_error;
1148 end if;
1149
1150 l_rmnQty := abs(l_item.primary_quantity);
1151
1152 if(p_txnActionID = wip_constants.retcomp_action) then
1153 open c_retTxnBasedLots(v_itemID => l_item.inventory_item_id);
1154 else
1155 open c_negRetTxnBasedLots(v_itemID => l_item.inventory_item_id);
1156 end if;
1157
1158 loop
1159 if(p_txnActionID = wip_constants.retcomp_action) then
1160 fetch c_retTxnBasedLots into l_lotNumber, l_expDate, l_lotQty;
1161 exit when c_retTxnBasedLots%NOTFOUND;
1162 else
1163 fetch c_negRetTxnBasedLots into l_lotNumber, l_expDate, l_lotQty;
1164 exit when c_negRetTxnBasedLots%NOTFOUND;
1165 end if;
1166
1167 if (l_logLevel <= wip_constants.full_logging) then
1168 wip_logger.log('lot: ' || l_lotNumber || '; qty: ' || l_lotQty, l_returnStatus);
1169 end if;
1170 l_enabled := wip_utilities.is_status_applicable(p_trx_type_id => getTxnType(l_item.transaction_action_id),
1171 p_organization_id => p_orgID,
1172 p_inventory_item_id => l_item.inventory_item_id,
1173 p_sub_code => l_item.supply_subinventory,
1174 p_locator_id => l_item.supply_locator_id,
1175 p_lot_number => l_lotNumber,
1176 p_object_type => 'O');
1177 if(l_enabled <> 'Y') then
1178 if (l_logLevel <= wip_constants.full_logging) then
1179 wip_logger.log('lot is not enabled', l_returnStatus);
1180 end if;
1181 goto END_OF_LOOP;
1182 end if;
1183 --processing here is slightly different than deriveIssueLots b/c sign of quantities could be either + or -
1184 if(l_lotQty >= l_rmnQty) then --lot has more than we need. only fill in the remaining qty
1185 x_compLots.addLot(p_lotNumber => l_lotNumber,
1186 p_priQty => l_rmnQty,
1187 p_attributes => null);
1188 l_rmnQty := 0;
1189 exit;
1190 else --exhaust all remaining qty in the lot
1191 x_compLots.addLot(p_lotNumber => l_lotNumber,
1192 p_priQty => l_lotQty,
1193 p_attributes => null);
1194 l_rmnQty := l_rmnQty - l_lotQty;
1195 end if;
1196 <<END_OF_LOOP>>
1197 null;
1198 end loop;
1199
1200 if(c_retTxnBasedLots%ISOPEN) then
1201 close c_retTxnBasedLots;
1202 elsif(c_retTxnBasedLots%ISOPEN) then
1203 close c_negRetTxnBasedLots;
1204 end if;
1205
1206 if(l_rmnQty <> 0) then
1207 l_errMsg := 'could not derive all qty. ' || l_rmnQty || ' remaining.';
1208 raise fnd_api.g_exc_error;
1209 end if;
1210
1211 if (l_logLevel <= wip_constants.trace_logging) then
1212 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.deriveTxnLots',
1213 p_procReturnStatus => x_returnStatus,
1214 p_msg => 'procedure success',
1215 x_returnStatus => l_returnStatus); --discard logging return status
1216 end if;
1217
1218 exception
1219 when fnd_api.g_exc_error then
1220 x_returnStatus:= fnd_api.g_ret_sts_error; --let caller know item was not fully derived
1221 if (l_logLevel <= wip_constants.trace_logging) then
1222 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.deriveTxnLots',
1223 p_procReturnStatus => x_returnStatus,
1224 p_msg => l_errMsg,
1225 x_returnStatus => l_returnStatus); --discard logging return status
1226 end if;
1227 when fnd_api.g_exc_unexpected_error then
1228 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1229 if (l_logLevel <= wip_constants.trace_logging) then
1230 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.deriveTxnLots',
1231 p_procReturnStatus => x_returnStatus,
1232 p_msg => 'error: ' || l_errMsg,
1233 x_returnStatus => l_returnStatus); --discard logging return status
1234 end if;
1235 when others then
1236 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1237 if(c_retTxnBasedLots%ISOPEN) then
1238 close c_retTxnBasedLots;
1239 elsif(c_retTxnBasedLots%ISOPEN) then
1240 close c_negRetTxnBasedLots;
1241 end if;
1242 fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_autoLotProc_priv',
1246 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.deriveTxnLots',
1243 p_procedure_name => 'deriveTxnLots',
1244 p_error_text => SQLERRM);
1245 if (l_logLevel <= wip_constants.trace_logging) then
1247 p_procReturnStatus => x_returnStatus,
1248 p_msg => 'error: ' || SQLERRM,
1249 x_returnStatus => l_returnStatus); --discard logging return status
1250 end if;
1251 end deriveTxnLots;
1252
1253 --derive lots procedure for issue txns
1254 procedure deriveIssueLots(p_orgID IN NUMBER,
1255 p_wipentityID IN NUMBER,
1256 p_entryType IN NUMBER,
1257 p_treeMode IN NUMBER,
1258 p_treeSrcName IN VARCHAR2,
1259 x_treeID IN OUT NOCOPY NUMBER,
1260 x_compLots IN OUT NOCOPY system.wip_lot_serial_obj_t,
1261 x_returnStatus OUT NOCOPY VARCHAR2) is
1262 l_rmnQty NUMBER;
1263 l_item system.wip_component_obj_t;
1264 l_treeID NUMBER;
1265 /* ER 4378835: Increased length of l_lotNumber from 30 to 80 to support OPM Lot-model changes */
1266 l_lotNumber VARCHAR2(80);
1267 l_returnStatus VARCHAR2(1);
1268 l_msgData VARCHAR2(240);
1269 l_errMsg VARCHAR2(240);
1270 l_expDate DATE;
1271 l_msgCount NUMBER;
1272 l_qtyOnHand NUMBER;
1273 l_rsvableQtyOnHand NUMBER;
1274 l_qtyRsved NUMBER;
1275 l_qtySuggested NUMBER;
1276 l_qtyAvailToRsv NUMBER;
1277 l_qtyAvailToTxt NUMBER;
1278 l_qtyOnHand2 NUMBER;
1279 l_rsvableQtyOnHand2 NUMBER;
1280 l_qtyRsved2 NUMBER;
1281 l_qtySuggested2 NUMBER;
1282 l_qtyAvailToRsv2 NUMBER;
1283 l_qtyAvailToTxt2 NUMBER;
1284 l_params wip_logger.param_tbl_t;
1285 l_cond boolean;
1286 l_enabled VARCHAR2(1);
1287 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
1288 /* Added for Wilson Greatbatch Enhancement */
1289 l_alt_lot_selection_method NUMBER ;
1290 l_flag NUMBER ;
1291 l_lot_qty_selected NUMBER ; /* Fix for Bug#4956543 */
1292
1293 cursor c_receiptOrderedLots(v_itemID NUMBER,
1294 v_supplySubinv VARCHAR2,
1295 v_supplyLocID NUMBER,
1296 v_revision VARCHAR2) is
1297 select moq.lot_number,
1298 min(mln.expiration_date)
1299 from mtl_lot_numbers mln,
1300 mtl_onhand_quantities_detail moq
1301 where moq.inventory_item_id = v_itemID
1302 and moq.organization_id = p_orgID
1303 and moq.subinventory_code = v_supplySubinv
1304 and nvl(moq.locator_id, -1) = nvl(v_supplyLocID, -1)
1305 and nvl(moq.revision, 'NONE') = nvl(v_revision, 'NONE')
1306 and mln.lot_number = moq.lot_number
1307 and mln.inventory_item_id = moq.inventory_item_id
1308 and mln.organization_id = moq.organization_id
1309 and nvl(mln.expiration_date, sysdate + 1) > sysdate
1310 group by moq.lot_number
1311 order by min(moq.date_received), moq.lot_number;
1312
1313 cursor c_expDateOrderedLots(v_itemID NUMBER,
1314 v_supplySubinv VARCHAR2,
1315 v_supplyLocID NUMBER,
1316 v_revision VARCHAR2) is
1317 select moq.lot_number,
1318 min(mln.expiration_date)
1319 from mtl_lot_numbers mln,
1320 mtl_onhand_quantities_detail moq
1321 where moq.inventory_item_id = v_itemID
1322 and moq.organization_id = p_orgID
1323 and moq.subinventory_code = v_supplySubinv
1324 and nvl(moq.locator_id, -1) = nvl(v_supplyLocID, -1)
1325 and nvl(moq.revision, 'NONE') = nvl(v_revision, 'NONE')
1326 and mln.lot_number = moq.lot_number
1327 and mln.inventory_item_id = moq.inventory_item_id
1328 and mln.organization_id = moq.organization_id
1329 and nvl(mln.expiration_date, sysdate + 1) > sysdate
1330 group by moq.lot_number
1331 order by min(mln.expiration_date),
1332 min(moq.date_received),
1333 moq.lot_number;
1334
1335 /* Added for Wilson Greatbatch Enhancement */
1336
1337 cursor c_TxnHistoryOrderedLots(v_itemID NUMBER,
1338 v_supplySubinv VARCHAR2,
1339 v_supplyLocID NUMBER,
1340 v_revision VARCHAR2) is
1341 select tln.lot_number
1342 from mtl_transaction_lot_numbers tln ,
1343 mtl_lot_numbers mln ,
1344 mtl_onhand_quantities_detail moq
1345 where tln.transaction_date =
1346 ( select max(transaction_date)
1347 from mtl_material_transactions
1348 where organization_id = p_OrgID
1349 and transaction_source_id =p_wipEntityID
1350 and transaction_source_type_id = 5
1351 and inventory_item_id = v_ItemId
1352 and ( MOVE_TRANSACTION_ID IS NOT NULL or
1353 COMPLETION_TRANSACTION_ID IS NOT NULL )
1354 )
1355 and tln.organization_id = moq.organization_id
1356 and tln.inventory_item_id = moq.inventory_item_id
1357 and tln.lot_number = moq.lot_number
1361 and nvl(moq.revision, 'NONE') = nvl(v_revision, 'NONE')
1358 and tln.lot_number = mln.lot_number
1359 and moq.subinventory_code = v_supplySubinv
1360 and nvl(moq.locator_id, -1) = nvl(v_supplyLocID, -1)
1362 and nvl(mln.expiration_date, sysdate + 1) > sysdate
1363 group by tln.lot_number
1364 order by tln.lot_number ;
1365 begin
1366 x_returnStatus := fnd_api.g_ret_sts_success;
1367 l_cond := x_compLots.getCurrentItem(l_item);
1368
1369 if (l_logLevel <= wip_constants.trace_logging) then
1370 l_params(1).paramName := 'p_orgID';
1371 l_params(1).paramValue := p_orgID;
1372 l_params(2).paramName := 'p_treeMode';
1373 l_params(2).paramValue := p_treeMode;
1374 l_params(3).paramName := 'p_treeSrcName';
1375 l_params(3).paramValue := p_treeSrcName;
1376 l_params(4).paramName := 'x_treeID';
1377 l_params(5).paramValue := x_treeID;
1378 if(l_cond) then
1379
1380 l_params(4).paramName := 'x_compLot(cur_item).inventory_item_id';
1381 l_params(4).paramValue := l_item.inventory_item_id;
1382 l_params(5).paramName := 'x_compLot(cur_item).supply_subinventory';
1383 l_params(5).paramValue := l_item.supply_subinventory;
1384 l_params(6).paramName := 'x_compLot(cur_item).supply_locator_id';
1385 l_params(6).paramValue := l_item.supply_locator_id;
1386 l_params(7).paramName := 'x_compLot(cur_item).revision';
1387 l_params(7).paramValue := l_item.revision;
1388 l_params(8).paramName := 'x_compLot(cur_item).primary_quantity';
1389 l_params(8).paramValue := l_item.primary_quantity;
1390 end if;
1391 wip_logger.entryPoint(p_procName => 'wip_autoLotProc_priv.deriveIssueLots',
1392 p_params => l_params,
1393 x_returnStatus => x_returnStatus);
1394 end if;
1395
1396 if(l_cond) then
1397 l_rmnQty := l_item.primary_quantity;
1398 else
1399 l_rmnQty := 0;
1400 end if;
1401
1402 if(x_treeID is null) then
1403 inv_quantity_tree_pvt.create_tree(p_api_version_number => 1.0,
1404 p_init_msg_lst => fnd_api.g_false,
1405 p_organization_id => p_orgID,
1406 p_inventory_item_id => l_item.inventory_item_id,
1407 p_tree_mode => p_treeMode,
1408 p_is_revision_control => (l_item.revision is not null),
1409 p_is_lot_control => true,
1410 p_is_serial_control => false,
1411 p_asset_sub_only => false,
1412 p_include_suggestion => false,
1413 p_demand_source_type_id => 5, --wip...set to match INVTTMTX form's trees
1414 p_demand_source_header_id => -1, --set to match INVTTMTX form's trees
1415 p_demand_source_line_id => null, --set to match INVTTMTX form's trees
1416 p_demand_source_name => p_treeSrcName,
1417 p_demand_source_delivery => null,
1418 p_lot_expiration_date => null,
1419 x_return_status => x_returnStatus,
1420 x_msg_count => l_msgCount,
1421 x_msg_data => l_msgData,
1422 x_tree_id => x_treeID);
1423 end if;
1424
1425 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
1426 l_errMsg := 'tree creation failed';
1427 raise fnd_api.g_exc_unexpected_error;
1428 end if;
1429
1430 if(p_entryType in (wip_constants.recdate_full, wip_constants.recdate_exc)) then
1431 open c_receiptOrderedLots(v_itemID => l_item.inventory_item_id,
1432 v_supplySubinv => l_item.supply_subinventory,
1433 v_supplyLociD => l_item.supply_locator_id,
1434 v_revision => l_item.revision);
1435 elsif(p_entryType in (wip_constants.expdate_full, wip_constants.expdate_exc)) then
1436 open c_expDateOrderedLots(v_itemID => l_item.inventory_item_id,
1437 v_supplySubinv => l_item.supply_subinventory,
1438 v_supplyLociD => l_item.supply_locator_id,
1439 v_revision => l_item.revision);
1440 /* Added for Wilson Greatbatch Enhancement */
1441 elsif(p_entryType in (wip_constants.txnHistory_full, wip_constants.txnHistory_exc)) then
1442 open c_TxnHistoryOrderedLots(v_itemID => l_item.inventory_item_id,
1443 v_supplySubinv => l_item.supply_subinventory,
1444 v_supplyLociD => l_item.supply_locator_id,
1445 v_revision => l_item.revision);
1446 select alternate_lot_selection_method
1447 into l_alt_lot_selection_method
1448 from wip_parameters
1449 where organization_id = p_orgID ;
1450 l_flag := 0 ;
1451 if (l_alt_lot_selection_method in (wip_constants.recdate_full,wip_constants.recdate_exc)) then
1452 open c_receiptOrderedLots(v_itemID => l_item.inventory_item_id,
1453 v_supplySubinv => l_item.supply_subinventory,
1454 v_supplyLociD => l_item.supply_locator_id,
1458 v_supplySubinv => l_item.supply_subinventory,
1455 v_revision => l_item.revision);
1456 elsif (l_alt_lot_selection_method in (wip_constants.expdate_full,wip_constants.expdate_exc)) then
1457 open c_expDateOrderedLots(v_itemID => l_item.inventory_item_id,
1459 v_supplyLociD => l_item.supply_locator_id,
1460 v_revision => l_item.revision);
1461 end if ;
1462 /* End of addition for Wilson Greatbatch Enhancement */
1463 else
1464 l_errMsg := 'manual entry';
1465 raise fnd_api.g_exc_error; --manual selection.
1466 end if;
1467
1468 loop
1469 if(p_entryType in (wip_constants.recdate_full, wip_constants.recdate_exc)) then
1470 fetch c_receiptOrderedLots into l_lotNumber, l_expDate;
1471 exit when c_receiptOrderedLots%NOTFOUND;
1472 elsif(p_entryType in (wip_constants.expdate_full, wip_constants.expdate_exc)) then
1473 fetch c_expDateOrderedLots into l_lotNumber, l_expDate;
1474 exit when c_expDateOrderedLots%NOTFOUND;
1475 /* Added for Wilson Greatbatch Enhancement */
1476 elsif(p_entryType in (wip_constants.txnHistory_full, wip_constants.txnHistory_exc)) then
1477 if ( l_flag = 0 ) then
1478 fetch c_txnHistoryOrderedLots into l_lotNumber;
1479 end if ;
1480 if ( (c_txnHistoryOrderedLots%ROWCOUNT = 0) OR ( c_txnHistoryOrderedLots%NOTFOUND AND ( l_rmnQty <> 0)) ) then
1481 l_flag := 1;
1482 if(l_alt_lot_selection_method in (wip_constants.recdate_full, wip_constants.recdate_exc)) then
1483 fetch c_receiptOrderedLots into l_lotNumber, l_expDate;
1484 exit when c_receiptOrderedLots%NOTFOUND;
1485 elsif(l_alt_lot_selection_method in (wip_constants.expdate_full, wip_constants.expdate_exc)) then
1486 fetch c_expDateOrderedLots into l_lotNumber, l_expDate;
1487 exit when c_expDateOrderedLots%NOTFOUND;
1488 /*Fix for bug 4090078 */
1489 else
1490 exit when c_txnHistoryOrderedLots%NOTFOUND;
1491 end if; -- end if for l_alt_lot_selection
1492 else -- else condition if c_TxnHistoryordered fetches rows
1493 exit when c_txnHistoryOrderedLots%NOTFOUND;
1494 end if; -- end if for row count
1495 /* End of addition for Wilson Greatbatch Enhancement */
1496 end if; -- end if for p_entrytype
1497
1498 l_enabled := wip_utilities.is_status_applicable(p_trx_type_id => getTxnType(l_item.transaction_action_id),
1499 p_organization_id => p_orgID,
1500 p_inventory_item_id => l_item.inventory_item_id,
1501 p_sub_code => l_item.supply_subinventory,
1502 p_locator_id => l_item.supply_locator_id,
1503 p_lot_number => l_lotNumber,
1504 p_object_type => 'O');
1505 --if this lot is not enabled, skip it.
1506 if(l_enabled <> 'Y') then
1507 goto END_OF_LOOP;
1508 end if;
1509
1510 inv_quantity_tree_pvt.query_tree(p_api_version_number => 1.0,
1511 p_init_msg_lst => fnd_api.g_false,
1512 p_tree_id => x_treeID,
1513 p_revision => l_item.revision,
1514 p_lot_number => l_lotNumber,
1515 p_subinventory_code => l_item.supply_subinventory,
1516 p_locator_id => l_item.supply_locator_id,
1517 p_transfer_subinventory_code => null,
1518 p_cost_group_id => null,
1519 p_lpn_id => null,
1520 p_transfer_locator_id => null,
1521 x_return_status => x_returnStatus,
1522 x_msg_count => l_msgCount,
1523 x_msg_data => l_msgData,
1524 x_qoh => l_qtyOnHand,
1525 x_rqoh => l_rsvableQtyOnHand,
1526 x_qr => l_qtyRsved,
1527 x_qs => l_qtySuggested,
1528 x_att => l_qtyAvailToTxt,
1529 x_atr => l_qtyAvailToRsv);
1530
1531
1532 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
1533 l_errMsg := 'qty tree query failed';
1534 raise fnd_api.g_exc_unexpected_error;
1535 end if;
1536
1537 -- Check if Lot is already entered into MTLI and populate into l_lot_qty_selected
1538 -- If Lot Number is not found then l_lot_qty_selected will be populated as zero Qty.
1539
1540 /* Fix for Bug#4956543 */
1541
1542 lot_selected ( p_organization_id => p_orgID,
1543 p_inventory_item_id => l_item.inventory_item_id,
1544 p_sub_code => l_item.supply_subinventory,
1545 p_locator_id => l_item.supply_locator_id,
1546 p_lot_number => l_lotNumber,
1547 p_lot_qty_selected => l_lot_qty_selected,
1548 x_returnStatus => x_returnStatus ) ;
1549
1553 if (l_lot_qty_selected > 0 ) then
1550 /* Begin Bug#4956543. l_qtyAvailToTxt is updated if Lot is already selected */
1551
1552 if ((l_qtyAvailToTxt > 0) and (l_qtyAvailToTxt - l_lot_qty_selected ) > 0) then
1554 wip_logger.log ('Changing l_qtyAvailToTxt', l_returnStatus ) ;
1555 l_qtyAvailToTxt := l_qtyAvailToTxt - l_lot_qty_selected ;
1556 end if ;
1557
1558 /* End Bug#4956543 */
1559
1560 if (l_logLevel <= wip_constants.full_logging) then
1561 wip_logger.log('adding lot ' || l_lotNumber || 'w/qty ' || least(l_rmnQty, l_qtyAvailToTxt), l_returnStatus);
1562 wip_logger.log('qty avail to txt:' || l_qtyAvailToTxt, l_returnStatus);
1563 wip_logger.log('l_rmnQty:' || l_rmnQty, l_returnStatus);
1564 end if;
1565
1566 x_compLots.addLot(p_lotNumber => l_lotNumber,
1567 p_priQty => least(l_rmnQty, l_qtyAvailToTxt),
1568 p_attributes => null);
1569
1570
1571 inv_quantity_tree_pvt.update_quantities(p_api_version_number => 1.0,
1572 p_init_msg_lst => fnd_api.g_false,
1573 p_tree_id => x_treeID,
1574 p_revision => l_item.revision,
1575 p_lot_number => l_lotNumber,
1576 p_subinventory_code => l_item.supply_subinventory,
1577 p_locator_id => l_item.supply_locator_id,
1578 p_primary_quantity => -1 * least(l_rmnQty, l_qtyAvailToTxt),
1579 p_quantity_type => 1, --pending txn
1580 p_transfer_subinventory_code => null,
1581 p_cost_group_id => null,
1582 p_containerized => inv_quantity_tree_pvt.g_containerized_false,
1583 p_lpn_id => null,
1584 p_transfer_locator_id => null,
1585 x_return_status => x_returnStatus,
1586 x_msg_count => l_msgCount,
1587 x_msg_data => l_msgData,
1588 x_qoh => l_qtyOnHand2,
1589 x_rqoh => l_rsvableQtyOnHand2,
1590 x_qr => l_qtyRsved2,
1591 x_qs => l_qtySuggested2,
1592 x_att => l_qtyAvailToTxt2,
1593 x_atr => l_qtyAvailToRsv2);
1594 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
1595 l_errMsg := 'qty tree update failed';
1596 raise fnd_api.g_exc_unexpected_error;
1597 end if;
1598
1599 if (l_logLevel <= wip_constants.full_logging) then
1600 wip_logger.log('updating treeID' || x_treeID, l_returnStatus);
1601 wip_logger.log(' item=' || l_item.inventory_item_id, l_returnStatus);
1602 wip_logger.log(' lot=' || l_lotNumber, l_returnStatus);
1603 wip_logger.log(' qty=' || -1 * least(l_rmnQty, l_qtyAvailToTxt), l_returnStatus);
1604 end if;
1605 l_rmnQty := l_rmnQty - least(l_rmnQty, l_qtyAvailToTxt);
1606 if(l_rmnQty = 0) then
1607 exit;
1608 end if;
1609 end if;
1610
1611 <<END_OF_LOOP>>
1612 null;
1613 end loop;
1614
1615 if(c_receiptOrderedLots%ISOPEN) then
1616 close c_receiptOrderedLots;
1617 elsif(c_expDateOrderedLots%ISOPEN) then
1618 close c_expDateOrderedLots;
1619 end if;
1620 /* Added for Wilson Greatbatch Enhancement */
1621 if(c_TxnHistoryOrderedLots%ISOPEN) then
1622 close c_TxnHistoryOrderedLots;
1623 end if;
1624
1625 if(l_rmnQty > 0) then
1626 l_errMsg := 'could not derive all qty. ' || l_rmnQty || ' remaining.';
1627 raise fnd_api.g_exc_error;
1628 end if;
1629
1630 if (l_logLevel <= wip_constants.trace_logging) then
1631 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.deriveIssueLots',
1632 p_procReturnStatus => x_returnStatus,
1633 p_msg => 'procedure success',
1634 x_returnStatus => l_returnStatus); --discard logging return status
1635 end if;
1636
1637 exception
1638 when fnd_api.g_exc_error then
1639 x_returnStatus:= fnd_api.g_ret_sts_error; --let caller know item was not fully derived
1640 if (l_logLevel <= wip_constants.trace_logging) then
1641 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.deriveIssueLots',
1642 p_procReturnStatus => x_returnStatus,
1643 p_msg => l_errMsg,
1644 x_returnStatus => l_returnStatus); --discard logging return status
1645 end if;
1646 when fnd_api.g_exc_unexpected_error then
1647 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1648 if(c_receiptOrderedLots%ISOPEN) then
1649 close c_receiptOrderedLots;
1650 elsif(c_expDateOrderedLots%ISOPEN) then
1651 close c_expDateOrderedLots;
1652 end if;
1653 if (l_logLevel <= wip_constants.trace_logging) then
1657 x_returnStatus => l_returnStatus); --discard logging return status
1654 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.deriveIssueLots',
1655 p_procReturnStatus => x_returnStatus,
1656 p_msg => 'error: ' || l_errMsg,
1658 end if;
1659 when others then
1660 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1661 if(c_receiptOrderedLots%ISOPEN) then
1662 close c_receiptOrderedLots;
1663 elsif(c_expDateOrderedLots%ISOPEN) then
1664 close c_expDateOrderedLots;
1665 end if;
1666 fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_autoLotProc_priv',
1667 p_procedure_name => 'deriveIssueLots',
1668 p_error_text => SQLERRM);
1669 if (l_logLevel <= wip_constants.trace_logging) then
1670 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.deriveIssueLots',
1671 p_procReturnStatus => x_returnStatus,
1672 p_msg => 'error: ' || SQLERRM,
1673 x_returnStatus => l_returnStatus); --discard logging return status
1674 end if;
1675
1676 end deriveIssueLots;
1677
1678 procedure deriveSingleItem(p_orgID IN NUMBER,
1679 p_wipEntityID IN NUMBER, --only needed for returns and neg returns
1680 p_entryType IN NUMBER,
1681 p_treeMode IN NUMBER,
1682 p_treeSrcName IN VARCHAR2,
1683 x_treeID IN OUT NOCOPY NUMBER, --the qty tree id if one was built
1684 x_compLots IN OUT NOCOPY system.wip_lot_serial_obj_t,
1685 x_returnStatus OUT NOCOPY VARCHAR2) is
1686 l_lotControlCode NUMBER;
1687 l_serialControlCode NUMBER;
1688 l_errMsg VARCHAR2(80);
1689 l_params wip_logger.param_tbl_t;
1690 l_returnStatus VARCHAR2(1);
1691 l_item system.wip_component_obj_t;
1692 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
1693 begin
1694
1695 x_returnStatus := fnd_api.g_ret_sts_success;
1696 if (l_logLevel <= wip_constants.trace_logging) then
1697 l_params(1).paramName := 'p_orgID';
1698 l_params(1).paramValue := p_orgID;
1699 l_params(2).paramName := 'p_wipEntityID';
1700 l_params(2).paramValue := p_wipEntityID;
1701 l_params(3).paramName := 'p_treeMode';
1702 l_params(3).paramValue := p_treeMode;
1703 l_params(4).paramName := 'p_treeSrcName';
1704 l_params(4).paramValue := p_treeSrcName;
1705 l_params(5).paramName := 'x_treeID';
1706 l_params(5).paramValue := x_treeID;
1707
1708 wip_logger.entryPoint(p_procName => 'wip_autoLotProc_priv.deriveSingleItem',
1709 p_params => l_params,
1710 x_returnStatus => x_returnStatus);
1711 end if;
1712
1713 savepoint wipbflpb40;
1714
1715 if(not x_compLots.getCurrentItem(l_item)) then
1716 l_errMsg := 'unable to get current item';
1717 raise fnd_api.g_exc_unexpected_error;
1718 end if;
1719
1720 select lot_control_code, serial_number_control_code
1721 into l_lotControlCode, l_serialControlCode
1722 from mtl_system_items
1723 where inventory_item_id = l_item.inventory_item_id
1724 and organization_id = p_orgID;
1725
1726 --if under serial control, we can not derive lots
1727 if(l_serialControlCode in (wip_constants.full_sn, wip_constants.dyn_rcv_sn)) then
1728 checkSerialQuantity(p_itemID => l_item.inventory_item_id,
1729 p_itemName => l_item.item_name,
1730 p_orgID => p_orgID,
1731 p_qty => abs(l_item.primary_quantity),
1732 p_txnActionID => l_item.transaction_action_id,
1733 p_serControlCode => l_serialControlCode,
1734 x_returnStatus => x_returnStatus);
1735 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
1736 l_errMsg := 'not enough serial #s';
1737 raise fnd_api.g_exc_unexpected_error;
1738 else
1739 l_errMsg := 'item under serial control';
1740 raise fnd_api.g_exc_error;
1741 end if;
1742 end if;
1743
1744 --if uncontrolled, return success (no derivation necessary)
1745 if(l_lotControlCode = wip_constants.no_lot) then
1746 x_returnStatus := fnd_api.g_ret_sts_success;
1747 if (l_logLevel <= wip_constants.trace_logging) then
1748 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.deriveSingleItem',
1749 p_procReturnStatus => x_returnStatus,
1750 p_msg => 'procedure success (no derivation necessary)',
1751 x_returnStatus => l_returnStatus); --discard logging return status
1752 end if;
1753 return;
1754 end if;
1755
1756
1757 --else under lot control only
1758
1759 --cannot derive lots for any other statuses besides these 3
1760 if(l_item.transaction_action_id not in (wip_constants.isscomp_action,
1761 wip_constants.retcomp_action,
1762 wip_constants.retnegc_action)) then
1763 l_errMsg := 'non-derivable txn action:' || l_item.transaction_action_id;
1764 raise fnd_api.g_exc_error;
1765 end if;
1766
1767
1771 p_wipEntityID => p_wipEntityID,
1768 --for issues, check out all the lots in the specified location
1769 if(l_item.transaction_action_id = wip_constants.isscomp_action) then
1770 deriveIssueLots(p_orgID => p_orgID,
1772 p_entryType => p_entryType,
1773 p_treeMode => p_treeMode,
1774 p_treeSrcName => p_treeSrcName,
1775 x_treeID => x_treeID,
1776 x_compLots => x_compLots,
1777 x_returnStatus => x_returnStatus);
1778
1779 --for returns, look at the past issue transactions and try to return those lots
1780 else
1781 deriveTxnLots(p_orgID => p_orgID,
1782 p_wipEntityID => p_wipEntityID,
1783 p_txnActionID => l_item.transaction_action_id,
1784 p_entryType => p_entryType,
1785 x_compLots => x_compLots,
1786 x_returnStatus => x_returnStatus);
1787 end if;
1788 if(x_returnStatus = fnd_api.g_ret_sts_unexp_error) then
1789 raise fnd_api.g_exc_unexpected_error;
1790 end if;
1791
1792 if (l_logLevel <= wip_constants.trace_logging) then
1793 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.deriveSingleItem',
1794 p_procReturnStatus => x_returnStatus,
1795 p_msg => 'procedure success',
1796 x_returnStatus => l_returnStatus); --discard logging return status
1797 end if;
1798
1799 exception
1800 when fnd_api.g_exc_error then
1801 x_returnStatus := fnd_api.g_ret_sts_error;
1802 if (l_logLevel <= wip_constants.trace_logging) then
1803 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.deriveSingleItem',
1804 p_procReturnStatus => x_returnStatus,
1805 p_msg => 'error' || l_errMsg,
1806 x_returnStatus => l_returnStatus); --discard logging return status
1807 end if;
1808 rollback to wipbflpb40;
1809 when fnd_api.g_exc_unexpected_error then
1810 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1811 if (l_logLevel <= wip_constants.trace_logging) then
1812 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.deriveSingleItem',
1813 p_procReturnStatus => x_returnStatus,
1814 p_msg => 'unexp error raised:',
1815 x_returnStatus => l_returnStatus); --discard logging return status
1816 end if;
1817 rollback to wipbflpb40;
1818 when others then
1819 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1820 fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_autoLotProc_priv',
1821 p_procedure_name => 'deriveSingleItem',
1822 p_error_text => SQLERRM);
1823 if (l_logLevel <= wip_constants.trace_logging) then
1824 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.deriveSingleItem',
1825 p_procReturnStatus => x_returnStatus,
1826 p_msg => 'unexp error:' || SQLERRM,
1827 x_returnStatus => l_returnStatus); --discard logging return status
1828 end if;
1829 rollback to wipbflpb40;
1830 end deriveSingleItem;
1831
1832
1833 function findTxnAction(p_isForwardTxn in VARCHAR2,
1834 p_qty in NUMBER) return number is
1835 begin
1836 if(fnd_api.to_boolean(p_isForwardTxn)) then
1837 if(p_qty > 0) then
1838 return wip_constants.isscomp_action;
1839 else
1840 return wip_constants.issnegc_action;
1841 end if;
1842 else
1843 if(p_qty < 0) then
1844 return wip_constants.retcomp_action;
1845 else
1846 return wip_constants.retnegc_action;
1847 end if;
1848 end if;
1849 end findTxnAction;
1850
1851 procedure checkSerialQuantity(p_itemID IN NUMBER,
1852 p_itemName IN VARCHAR2,
1853 p_orgID IN NUMBER,
1854 p_qty IN NUMBER,
1855 p_txnActionID IN NUMBER,
1856 p_serControlCode IN NUMBER,
1857 x_returnStatus OUT NOCOPY VARCHAR2) IS
1858 l_serCount NUMBER;
1859 l_params wip_logger.param_tbl_t;
1860 l_returnStatus VARCHAR2(1);
1861 l_errMsg VARCHAR2(80);
1862 l_txnTypeID NUMBER;
1863 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
1864 begin
1865
1866 x_returnStatus := fnd_api.g_ret_sts_success;
1867 if (l_logLevel <= wip_constants.trace_logging) then
1868 l_params(1).paramName := 'p_itemID';
1869 l_params(1).paramValue := p_itemID;
1870 l_params(2).paramName := 'p_orgID';
1871 l_params(2).paramValue := p_orgID;
1872 l_params(3).paramName := 'p_qty';
1873 l_params(3).paramValue := p_qty;
1874 l_params(4).paramName := 'p_txnActionID';
1875 l_params(4).paramValue := p_txnActionID;
1876 wip_logger.entryPoint(p_procName => 'wip_autoLotProc_priv.checkSerialQuantity',
1877 p_params => l_params,
1878 x_returnStatus => x_returnStatus);
1879 end if;
1880
1881 if (l_logLevel <= wip_constants.full_logging) then
1882 wip_logger.log('qty:' || p_qty, l_returnStatus);
1886 l_errMsg := 'serial requirement not a whole #';
1883 wip_logger.log('round(qty)' || round(p_qty), l_returnStatus);
1884 end if;
1885 if(p_qty <> round(p_qty)) then --serial requirements must be whole numbers
1887 fnd_message.set_name('WIP', 'COMP_INVALID_SER_QTY');
1888 fnd_message.set_token('ITEM', p_itemName);
1889 fnd_msg_pub.add;
1890 raise fnd_api.g_exc_unexpected_error;
1891 end if;
1892
1893 l_txnTypeID := getTxnType(p_txnActionID);
1894
1895 if(p_txnActionID in (wip_constants.isscomp_action, wip_constants.retnegc_action)) then
1896 select nvl(max(count(*)), 0)
1897 into l_serCount
1898 from mtl_serial_numbers
1899 where current_organization_id = p_orgID
1900 and inventory_item_id = p_itemID
1901 and current_status = 3
1902 and (group_mark_id = -1 OR group_mark_id is null)
1903 and lpn_id is null
1904 and (wip_utilities.is_status_applicable(/*p_trx_status_enabled => */ null,
1905 /*p_trx_type_id => */ l_txnTypeID,
1906 /*p_lot_status_enabled => */ null,
1907 /*p_serial_status_enabled => */ null,
1908 /*p_organization_id => */ current_organization_id,
1909 /*p_inventory_item_id => */ inventory_item_id,
1910 /*p_sub_code => */ current_subinventory_code,
1911 /*p_locator_id => */ current_locator_id,
1912 /*p_lot_number => */ lot_number,
1913 /*p_serial_number => */ serial_number,
1914 /*p_object_type => */ 'S') = 'Y')
1915 group by current_subinventory_code, current_locator_id, revision;
1916
1917 elsif(p_txnActionID = wip_constants.retcomp_action) then
1918 select nvl(max(count(*)), 0)
1919 into l_serCount
1920 from mtl_serial_numbers
1921 where current_organization_id = p_orgID
1922 and inventory_item_id = p_itemID
1923 and current_status = 4
1924 and (group_mark_id = -1 OR group_mark_id is null)
1925 and (wip_utilities.is_status_applicable(/*p_trx_status_enabled => */ null,
1926 /*p_trx_type_id => */ l_txnTypeID,
1927 /*p_lot_status_enabled => */ null,
1928 /*p_serial_status_enabled => */ null,
1929 /*p_organization_id => */ current_organization_id,
1930 /*p_inventory_item_id => */ inventory_item_id,
1931 /*p_sub_code => */ current_subinventory_code,
1932 /*p_locator_id => */ current_locator_id,
1933 /*p_lot_number => */ lot_number,
1934 /*p_serial_number => */ serial_number,
1935 /*p_object_type => */ 'S') = 'Y')
1936 group by revision;
1937
1938 elsif(p_txnActionID = wip_constants.issnegc_action) then
1939 if(p_serControlCode = wip_constants.dyn_rcv_sn) then
1940 x_returnStatus := fnd_api.g_ret_sts_success;
1941 if (l_logLevel <= wip_constants.trace_logging) then
1942 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.checkSerialQuantity',
1943 p_procReturnStatus => x_returnStatus,
1944 p_msg => 'procedure success (neg issue and serial is dyn at recpt)',
1945 x_returnStatus => l_returnStatus);
1946 end if;
1947 return;
1948 else
1949 select count(*)
1950 into l_serCount
1951 from mtl_serial_numbers
1952 where current_organization_id = p_orgID
1953 and inventory_item_id = p_itemID
1954 and current_status in (1, 6)
1955 and (group_mark_id = -1 OR group_mark_id is null)
1956 and (wip_utilities.is_status_applicable(/*p_trx_status_enabled => */ null,
1957 /*p_trx_type_id => */ l_txnTypeID,
1958 /*p_lot_status_enabled => */ null,
1959 /*p_serial_status_enabled => */ null,
1960 /*p_organization_id => */ current_organization_id,
1961 /*p_inventory_item_id => */ inventory_item_id,
1962 /*p_sub_code => */ current_subinventory_code,
1963 /*p_locator_id => */ current_locator_id,
1964 /*p_lot_number => */ lot_number,
1965 /*p_serial_number => */ serial_number,
1966 /*p_object_type => */ 'S') = 'Y');
1967
1968 end if;
1969 end if;
1970 if (l_logLevel <= wip_constants.full_logging) then
1971 wip_logger.log('serial count is ' || l_serCount, l_returnStatus);
1975 fnd_message.set_token('ITEM', p_itemName);
1972 end if;
1973 if(l_serCount < p_qty) then
1974 fnd_message.set_name('WIP', 'NO_COMP_SERIAL_NUMBERS');
1976 fnd_msg_pub.add;
1977 l_errMsg := 'error: not enough serials available';
1978 raise fnd_api.g_exc_unexpected_error;
1979 else
1980 x_returnStatus := fnd_api.g_ret_sts_success;
1981 end if;
1982 if (l_logLevel <= wip_constants.trace_logging) then
1983 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.checkSerialQuantity',
1984 p_procReturnStatus => x_returnStatus,
1985 p_msg => 'procedure success',
1986 x_returnStatus => l_returnStatus);
1987 end if;
1988 exception
1989 when fnd_api.g_exc_unexpected_error then
1990 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1991 if (l_logLevel <= wip_constants.trace_logging) then
1992 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.checkSerialQuantity',
1993 p_procReturnStatus => x_returnStatus,
1994 p_msg => l_errMsg,
1995 x_returnStatus => l_returnStatus);
1996 end if;
1997 when others then
1998 fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_autoLotProc_priv',
1999 p_procedure_name => 'checkSerialQuantity',
2000 p_error_text => SQLERRM);
2001 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2002 if (l_logLevel <= wip_constants.trace_logging) then
2003 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.checkSerialQuantity',
2004 p_procReturnStatus => x_returnStatus,
2005 p_msg => 'unexp error ' || SQLERRM,
2006 x_returnStatus => l_returnStatus);
2007 end if;
2008 end checkSerialQuantity;
2009
2010 procedure checkSerial(p_txnTmpID IN NUMBER,
2011 p_txnIntID IN NUMBER,
2012 p_itemID IN NUMBER,
2013 p_itemName IN VARCHAR2,
2014 p_orgID IN NUMBER,
2015 p_revision IN VARCHAR2,
2016 p_subinv IN VARCHAR2,
2017 p_locID IN NUMBER,
2018 p_qty IN NUMBER,
2019 p_txnActionID IN NUMBER,
2020 p_serControlCode IN NUMBER,
2021 x_serialReturnStatus OUT NOCOPY VARCHAR2,
2022 x_returnStatus OUT NOCOPY VARCHAR2) IS
2023 l_serQty NUMBER;
2024 l_totalQty NUMBER := 0;
2025 /* ER 4378835: Increased length of lot variables from 30 to 80 to support OPM Lot-model changes */
2026 l_prefix VARCHAR2(80);
2027 l_fmNumber VARCHAR2(80);
2028 l_toNumber VARCHAR2(80);
2029 l_errCode NUMBER;
2030 l_errMsg VARCHAR2(80);
2031 l_returnStatus VARCHAR2(1);
2032 l_params wip_logger.param_tbl_t;
2033 l_fmSerial VARCHAR2(30);
2034 l_toSerial VARCHAR2(30);
2035 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
2036 cursor c_tmpSerials is
2037 select fm_serial_number fmSerial,
2038 to_serial_number toSerial
2039 from mtl_serial_numbers_temp
2040 where transaction_temp_id = p_txnTmpID;
2041
2042 cursor c_intSerials is
2043 select fm_serial_number fmSerial,
2044 to_serial_number toSerial
2045 from mtl_serial_numbers_interface
2046 where transaction_interface_id = p_txnIntID;
2047 begin
2048 x_returnStatus := fnd_api.g_ret_sts_success;
2049 if (l_logLevel <= wip_constants.trace_logging) then
2050 l_params(1).paramName := 'p_txnTmpID';
2051 l_params(1).paramValue := p_txnTmpID;
2052 l_params(2).paramName := 'p_txnIntID';
2053 l_params(2).paramValue := p_txnIntID;
2054 l_params(3).paramName := 'p_itemID';
2055 l_params(3).paramValue := p_itemID;
2056 l_params(4).paramName := 'p_itemName';
2057 l_params(4).paramValue := p_itemName;
2058 l_params(5).paramName := 'p_orgID';
2059 l_params(5).paramValue := p_orgID;
2060 l_params(6).paramName := 'p_revision';
2061 l_params(6).paramValue := p_revision;
2062 l_params(7).paramName := 'p_subinv';
2063 l_params(7).paramValue := p_subinv;
2064 l_params(8).paramName := 'p_locID';
2065 l_params(8).paramValue := p_locID;
2066 l_params(9).paramName := 'p_qty';
2067 l_params(9).paramValue := p_qty;
2068 l_params(10).paramName := 'p_txnActionID';
2069 l_params(10).paramValue := p_txnActionID;
2070 l_params(11).paramName := 'p_serControlCode';
2071 l_params(11).paramValue := p_serControlCode;
2072 wip_logger.entryPoint(p_procName => 'wip_autoLotProc_priv.checkSerial',
2073 p_params => l_params,
2074 x_returnStatus => x_returnStatus);
2075 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
2076 raise fnd_api.g_exc_unexpected_error;
2077 end if;
2078 end if;
2079
2080 if(p_txnTmpID is not null) then
2081 open c_tmpSerials;
2082 else
2083 open c_intSerials;
2084 end if;
2085
2086 loop
2087 if(p_txnTmpID is not null) then
2088 fetch c_tmpSerials into l_fmSerial, l_toSerial;
2089 exit when c_tmpSerials%NOTFOUND;
2090 else
2091 fetch c_intSerials into l_fmSerial, l_toSerial;
2095 p_to_serial_number => l_toSerial,
2092 exit when c_intSerials%NOTFOUND;
2093 end if;
2094 if(MTL_Serial_Check.inv_serial_info(p_from_serial_number => l_fmSerial,
2096 x_prefix => l_prefix,
2097 x_quantity => l_serQty,
2098 x_from_number => l_fmNumber,
2099 x_to_number => l_toNumber,
2100 x_errorcode => l_errCode)) then
2101 l_totalQty := l_totalQty + l_serQty;
2102 else
2103 l_errMsg := 'mtl_serial_check.inv_serial_info returned false';
2104 raise fnd_api.g_exc_unexpected_error;
2105 end if;
2106 end loop;
2107
2108 if(c_tmpSerials%ISOPEN) then
2109 close c_tmpSerials;
2110 elsif(c_intSerials%ISOPEN) then
2111 close c_intSerials;
2112 end if;
2113
2114 if(l_totalQty <> abs(p_qty)) then
2115 checkSerialQuantity(p_itemID => p_itemID,
2116 p_itemName => p_itemName,
2117 p_orgID => p_orgID,
2118 p_qty => abs(p_qty),
2119 p_txnActionID => p_txnActionID,
2120 p_serControlCode => p_serControlCode,
2121 x_returnStatus => x_serialReturnStatus);
2122 if(x_serialReturnStatus = fnd_api.g_ret_sts_success) then --enough serial numbers exist to complete this transaction
2123 raise fnd_api.g_exc_error;
2124 else
2125 raise fnd_api.g_exc_unexpected_error;
2126 end if;
2127
2128 end if;
2129
2130 if (l_logLevel <= wip_constants.trace_logging) then
2131 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.checkSerial',
2132 p_procReturnStatus => x_returnStatus,
2133 p_msg => 'procedure success',
2134 x_returnStatus => l_returnStatus);
2135 end if;
2136 exception
2137 when fnd_api.g_exc_error then
2138 x_serialReturnStatus := fnd_api.g_ret_sts_error;
2139 x_returnStatus := fnd_api.g_ret_sts_success;
2140 if (l_logLevel <= wip_constants.trace_logging) then
2141 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.checkSerial',
2142 p_procReturnStatus => x_returnStatus,
2143 p_msg => 'insufficient serial qty. only found ' || l_totalQty,
2144 x_returnStatus => l_returnStatus);
2145 end if;
2146 when fnd_api.g_exc_unexpected_error then
2147 x_serialReturnStatus := fnd_api.g_ret_sts_unexp_error;
2148 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2149 if(c_tmpSerials%ISOPEN) then
2150 close c_tmpSerials;
2151 elsif(c_intSerials%ISOPEN) then
2152 close c_intSerials;
2153 end if;
2154 if (l_logLevel <= wip_constants.trace_logging) then
2155 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.checkSerial',
2156 p_procReturnStatus => x_returnStatus,
2157 p_msg => l_errMsg,
2158 x_returnStatus => l_returnStatus);
2159 end if;
2160 when others then
2161 x_serialReturnStatus := fnd_api.g_ret_sts_unexp_error;
2162 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2163 if(c_tmpSerials%ISOPEN) then
2164 close c_tmpSerials;
2165 elsif(c_intSerials%ISOPEN) then
2166 close c_intSerials;
2167 end if;
2168 fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_autoLotProc_priv',
2169 p_procedure_name => 'checkSerial',
2170 p_error_text => SQLERRM);
2171 if (l_logLevel <= wip_constants.trace_logging) then
2172 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.checkSerial',
2173 p_procReturnStatus => x_returnStatus,
2174 p_msg => 'unexp error ' || SQLERRM,
2175 x_returnStatus => l_returnStatus);
2176 end if;
2177 end checkSerial;
2178
2179 function worstReturnStatus(p_status1 VARCHAR2, p_status2 VARCHAR2) return VARCHAR2 is
2180 begin
2181 if(p_status1 = fnd_api.g_ret_sts_unexp_error or
2182 p_status2 = fnd_api.g_ret_sts_unexp_error) then
2183 return fnd_api.g_ret_sts_unexp_error;
2184 elsif(p_status1 = fnd_api.g_ret_sts_error or
2185 p_status2 = fnd_api.g_ret_sts_error) then
2186 return fnd_api.g_ret_sts_error;
2187 else
2188 return fnd_api.g_ret_sts_success;
2189 end if;
2190 end worstReturnStatus;
2191
2192 function getTxnType(p_txnActionID IN NUMBER) return NUMBER is
2193 begin
2194 if(p_txnActionID = wip_constants.isscomp_action) then
2195 return wip_constants.isscomp_type;
2196 elsif(p_txnActionID = wip_constants.retnegc_action) then
2197 return wip_constants.retnegc_type;
2198 elsif(p_txnActionID = wip_constants.retcomp_action) then
2199 return wip_constants.retcomp_type;
2200 elsif(p_txnActionID = wip_constants.issnegc_action) then
2201 return wip_constants.issnegc_type;
2202 end if;
2203 return -1; --this procedure only works for component txn types
2204 end getTxnType;
2205
2206
2207 /* Fix for Bug#4737216 . Added following procedure */
2208 procedure lot_selected (
2209 p_organization_id NUMBER,
2210 p_inventory_item_id NUMBER,
2211 p_sub_code VARCHAR2,
2212 p_locator_id NUMBER,
2213 p_lot_number VARCHAR2,
2214 p_lot_qty_selected OUT NOCOPY NUMBER,
2215 x_returnStatus OUT NOCOPY VARCHAR2) is
2216 l_qty NUMBER ;
2217 l_returnStatus varchar2(1) ;
2218 l_params wip_logger.param_tbl_t;
2219 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
2220 begin
2221
2222 l_qty := 0 ;
2223
2224 x_returnStatus := fnd_api.g_ret_sts_success;
2225
2226 if (l_logLevel <= wip_constants.trace_logging) then
2227 l_params(1).paramName := 'p_organization_id';
2228 l_params(1).paramValue := p_organization_id;
2229 l_params(2).paramName := 'p_inventory_item_id';
2230 l_params(2).paramValue := p_inventory_item_id;
2231 l_params(3).paramName := 'p_sub_code';
2232 l_params(3).paramValue := p_sub_code;
2233 l_params(4).paramName := 'p_locator_id';
2234 l_params(4).paramValue := p_locator_id;
2235 l_params(5).paramName := 'p_lot_number';
2236 l_params(5).paramValue := p_lot_number;
2237
2238 wip_logger.entryPoint(p_procName => 'wip_autoLotProc_priv.lot_selected',
2239 p_params => l_params,
2240 x_returnStatus => x_returnStatus);
2241 end if ;
2242
2243 begin
2244
2245 select sum(abs(nvl(transaction_quantity, 0)))
2246 into l_qty
2247 from mtl_transaction_lots_interface
2248 where transaction_interface_id in
2249 (select transaction_interface_id
2250 from mtl_transactions_interface
2251 where inventory_item_id = p_inventory_item_id
2252 and organization_id = p_organization_id
2253 and subinventory_code = p_sub_code
2254 and nvl(locator_id, -1) = nvl(p_locator_id, -1))
2255 and lot_number = p_lot_number ;
2256
2257 exception
2258 when others then
2259 wip_logger.log( 'In exception Lots entered **** ' , l_returnStatus) ;
2260 l_qty := 0 ;
2261 end ;
2262
2263 p_lot_qty_selected := nvl(l_qty, 0) ;
2264
2265 wip_logger.log( 'Lot Qty Selected ' || p_lot_qty_selected || ' for Lot ' || p_lot_number, l_returnStatus) ;
2266
2267 if (l_logLevel <= wip_constants.trace_logging) then
2268 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.lot_selected',
2269 p_procReturnStatus => x_returnStatus,
2270 p_msg => 'procedure success',
2271 x_returnStatus => l_returnStatus); --discard logging return status
2272 end if;
2273
2274 end lot_selected ;
2275
2276 PROCEDURE deriveSingleItemFromMOG
2277 (p_parentObjID IN NUMBER,
2278 p_orgID IN NUMBER,
2279 p_item IN system.wip_component_obj_t,
2280 x_compLots IN OUT NOCOPY system.wip_lot_serial_obj_t,
2281 x_returnStatus OUT NOCOPY VARCHAR2) IS
2282
2283 CURSOR c_lotComp IS
2284
2285 SELECT mtln.lot_number lot,
2286 mtln.primary_quantity * -1 lot_qty
2287 FROM mtl_object_genealogy mog,
2288 mtl_material_transactions mmt,
2289 mtl_transaction_lot_numbers mtln,
2290 mtl_lot_numbers mln
2291 WHERE mog.object_id = mln.gen_object_id
2292 AND mog.end_date_active IS NULL
2293 AND mog.parent_object_id = p_parentObjID
2294 AND mtln.inventory_item_id = p_item.inventory_item_id
2295 AND mtln.organization_id = p_orgID
2296 AND mtln.organization_id = mln.organization_id
2297 AND mtln.inventory_item_id = mln.inventory_item_id
2298 AND mtln.lot_number = mln.lot_number
2299 AND nvl(mln.expiration_date, sysdate + 1) > sysdate
2300 AND mmt.transaction_id = mog.origin_txn_id
2301 AND mmt.transaction_id = mtln.transaction_id
2302 AND mmt.transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
2303 WIP_CONSTANTS.RETCOMP_ACTION)
2304 AND mmt.operation_seq_num = p_item.operation_seq_num;
2305
2306 l_derivedQty NUMBER := 0;
2307 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
2308 l_errMsg VARCHAR2(240);
2309 l_returnStatus VARCHAR2(1);
2310 l_lotComp c_lotComp%ROWTYPE;
2311 l_params wip_logger.param_tbl_t;
2312
2313 BEGIN
2314 -- Don't need to check the return status because already check in
2315 -- deriveSerial()
2316
2317 IF (l_logLevel <= wip_constants.trace_logging) THEN
2318 l_params(1).paramName := 'p_parentObjID';
2319 l_params(1).paramValue := p_parentObjID;
2320 l_params(2).paramName := 'p_orgID';
2321 l_params(2).paramValue := p_orgID;
2322 l_params(3).paramName := 'p_item.inventory_item_id';
2323 l_params(3).paramValue := p_item.inventory_item_id;
2324 l_params(4).paramName := 'p_item.operation_seq_num';
2325 l_params(4).paramValue := p_item.operation_seq_num;
2326 l_params(5).paramName := 'p_item.supply_subinventory';
2327 l_params(5).paramValue := p_item.supply_subinventory;
2328 l_params(6).paramName := 'p_item.supply_locator_id';
2329 l_params(6).paramValue := p_item.supply_locator_id;
2330 l_params(7).paramName := 'p_item.revision';
2331 l_params(7).paramValue := p_item.revision;
2332 l_params(8).paramName := 'p_item.primary_quantity';
2333 l_params(8).paramValue := p_item.primary_quantity;
2334 l_params(9).paramName := 'p_item.transaction_action_id';
2335 l_params(9).paramValue := p_item.transaction_action_id;
2336 wip_logger.entryPoint(
2337 p_procName => 'wip_autoLotProc_priv.deriveSingleItemFromMOG',
2338 p_params => l_params,
2339 x_returnStatus => x_returnStatus);
2340 END IF;
2341 -- Under lot control only
2342 FOR l_lotComp IN c_lotComp LOOP
2343
2344 x_compLots.addLot(p_lotNumber => l_lotComp.lot,
2345 p_priQty => l_lotComp.lot_qty,
2346 p_attributes => null);
2347
2348 l_derivedQty := l_derivedQty + l_lotComp.lot_qty;
2349
2350 IF (l_logLevel <= wip_constants.full_logging) THEN
2351 wip_logger.log('Added Lot : ' || l_lotComp.lot, l_returnStatus);
2352 wip_logger.log('Added Lot Qty : '|| l_lotComp.lot_qty, l_returnStatus);
2353 END IF;
2354 END LOOP; -- l_lotComp IN c_lotComp
2355 -- Check whether derived quantity equal to backflush quantity or not
2356 -- If not, error out.
2357 IF(p_item.lot_control_code = WIP_CONSTANTS.LOT AND
2358 p_item.primary_quantity <> l_derivedQty * -1) THEN
2359 wip_logger.log('item : ' || p_item.item_name, l_returnStatus);
2360 wip_logger.log('primary_quantity : ' || p_item.primary_quantity,
2361 l_returnStatus);
2362 wip_logger.log('derived_quantity : ' || l_derivedQty, l_returnStatus);
2363 l_errMsg := 'return quantity missmatch';
2364 raise fnd_api.g_exc_unexpected_error;
2365 END IF;
2366
2367 IF (l_logLevel <= wip_constants.trace_logging) THEN
2368 wip_logger.exitPoint(
2369 p_procName => 'wip_autoLotProc_priv.deriveSingleItemFromMOG',
2370 p_procReturnStatus => x_returnStatus,
2371 p_msg => 'procedure success',
2372 x_returnStatus => l_returnStatus); --discard return status
2373 END IF;
2374
2375 EXCEPTION
2376 WHEN fnd_api.g_exc_unexpected_error THEN
2377 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2378 IF (l_logLevel <= wip_constants.trace_logging) THEN
2379 wip_logger.exitPoint(
2380 p_procName => 'wip_autoLotProc_priv.deriveSingleItemFromMOG',
2381 p_procReturnStatus => x_returnStatus,
2382 p_msg => l_errMsg,
2383 x_returnStatus => l_returnStatus); --discard return status
2384 END IF;
2385
2386 fnd_message.set_name('WIP', 'WIP_RET_QTY_MISSMATCH');
2387 fnd_message.set_token('ENTITY1', p_item.item_name);
2388 fnd_msg_pub.add;
2389
2390 WHEN others THEN
2391 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2392 IF (l_logLevel <= wip_constants.trace_logging) THEN
2393 wip_logger.exitPoint(
2394 p_procName => 'wip_autoLotProc_priv.deriveSingleItemFromMOG',
2395 p_procReturnStatus => x_returnStatus,
2396 p_msg => 'unexp error:' || SQLERRM,
2397 x_returnStatus => l_returnStatus); --discard return status
2398 END IF;
2399 END deriveSingleItemFromMOG;
2400
2401 PROCEDURE setItemRevision
2402 (p_parentObjID IN NUMBER,
2403 p_orgID IN NUMBER,
2404 p_item IN system.wip_component_obj_t,
2405 x_compLots IN OUT NOCOPY system.wip_lot_serial_obj_t,
2406 x_returnStatus OUT NOCOPY VARCHAR2) IS
2407
2408 CURSOR c_revisionComp IS
2409
2410 SELECT mmt.revision revision
2411 FROM mtl_object_genealogy mog,
2412 mtl_material_transactions mmt,
2413 mtl_transaction_lot_numbers mtln,
2414 mtl_lot_numbers mln
2415 WHERE mog.object_id = mln.gen_object_id
2416 AND mog.end_date_active IS NULL
2417 AND mog.parent_object_id = p_parentObjID
2418 AND mtln.inventory_item_id = p_item.inventory_item_id
2419 AND mtln.organization_id = p_orgID
2420 AND mtln.organization_id = mln.organization_id
2421 AND mtln.inventory_item_id = mln.inventory_item_id
2422 AND mtln.lot_number = mln.lot_number
2423 AND nvl(mln.expiration_date, sysdate + 1) > sysdate
2424 AND mmt.transaction_id = mog.origin_txn_id
2425 AND mmt.transaction_id = mtln.transaction_id
2426 AND mmt.transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
2427 WIP_CONSTANTS.RETCOMP_ACTION)
2428 AND mmt.operation_seq_num = p_item.operation_seq_num;
2429
2430 l_errMsg VARCHAR2(240);
2431 l_returnStatus VARCHAR2(1);
2432 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
2433 l_params wip_logger.param_tbl_t;
2434 l_revisionComp c_revisionComp%ROWTYPE;
2435 BEGIN
2436
2437 IF (l_logLevel <= wip_constants.trace_logging) THEN
2438 l_params(1).paramName := 'p_parentObjID';
2439 l_params(1).paramValue := p_parentObjID;
2440 l_params(2).paramName := 'p_orgID';
2441 l_params(2).paramValue := p_orgID;
2442 l_params(3).paramName := 'p_item.inventory_item_id';
2443 l_params(3).paramValue := p_item.inventory_item_id;
2444 l_params(4).paramName := 'p_item.operation_seq_num';
2445 l_params(4).paramValue := p_item.operation_seq_num;
2446 l_params(5).paramName := 'p_item.supply_subinventory';
2447 l_params(5).paramValue := p_item.supply_subinventory;
2448 l_params(6).paramName := 'p_item.supply_locator_id';
2449 l_params(6).paramValue := p_item.supply_locator_id;
2450 l_params(7).paramName := 'p_item.revision';
2451 l_params(7).paramValue := p_item.revision;
2452 l_params(8).paramName := 'p_item.primary_quantity';
2453 l_params(8).paramValue := p_item.primary_quantity;
2454 l_params(9).paramName := 'p_item.transaction_action_id';
2455 l_params(9).paramValue := p_item.transaction_action_id;
2456 wip_logger.entryPoint(
2457 p_procName => 'wip_autoLotProc_priv.setItemRevision',
2458 p_params => l_params,
2459 x_returnStatus => x_returnStatus);
2460 END IF;
2461
2462 OPEN c_revisionComp;
2463 -- Since revision is at the item level, we can just get the revision of
2464 -- the first record.
2465 FETCH c_revisionComp INTO l_revisionComp;
2466
2467 IF(c_revisionComp%FOUND AND
2468 l_revisionComp.revision IS NOT NULL) THEN
2469 x_compLots.setRevision(p_revision => l_revisionComp.revision);
2470 END IF;
2471
2472 IF (l_logLevel <= wip_constants.trace_logging) THEN
2473 wip_logger.exitPoint(
2474 p_procName => 'wip_autoLotProc_priv.setItemRevision',
2475 p_procReturnStatus => x_returnStatus,
2476 p_msg => 'procedure success',
2477 x_returnStatus => l_returnStatus); --discard return status
2478 END IF;
2479 CLOSE c_revisionComp;
2480 EXCEPTION
2481 WHEN others THEN
2482 IF(c_revisionComp%ISOPEN) THEN
2483 CLOSE c_revisionComp;
2484 END IF;
2485
2486 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2487 IF (l_logLevel <= wip_constants.trace_logging) THEN
2488 wip_logger.exitPoint(
2489 p_procName => 'wip_autoLotProc_priv.setItemRevision',
2490 p_procReturnStatus => x_returnStatus,
2491 p_msg => 'unexp error:' || SQLERRM,
2492 x_returnStatus => l_returnStatus); --discard return status
2493 END IF;
2494 END setItemRevision;
2495
2496
2497 PROCEDURE deriveLotsFromMOG(
2498 x_compLots IN OUT NOCOPY system.wip_lot_serial_obj_t,
2499 p_orgID IN NUMBER,
2500 p_objectID IN NUMBER,
2501 p_initMsgList IN VARCHAR2,
2502 x_returnStatus OUT NOCOPY VARCHAR2) IS
2503
2504 l_returnStatus VARCHAR2(1);
2505 l_errMsg VARCHAR2(80);
2506 l_params wip_logger.param_tbl_t;
2507 l_curItem system.wip_component_obj_t;
2508 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
2509 BEGIN
2510 IF (l_logLevel <= wip_constants.trace_logging) THEN
2511 l_params(1).paramName := 'p_orgID';
2512 l_params(1).paramValue := p_orgID;
2513 l_params(2).paramName := 'p_objectID';
2514 l_params(2).paramValue := p_objectID;
2515
2516 wip_logger.entryPoint(
2517 p_procName => 'wip_autoLotProc_priv.deriveLotsFromMOG',
2518 p_params => l_params,
2519 x_returnStatus => x_returnStatus);
2520 END IF;
2521
2522 SAVEPOINT s_deriveLotsFromMOG;
2523
2524 IF(fnd_api.to_boolean(p_initMsgList)) THEN
2525 fnd_msg_pub.initialize;
2526 END IF;
2527
2528 x_compLots.reset;
2529 --assume we will be able to derive everything
2530 x_returnStatus := fnd_api.g_ret_sts_success;
2531
2532 LOOP
2533 IF(x_compLots.getCurrentItem(l_curItem)) THEN
2534 IF(l_curItem.wip_supply_type NOT IN (WIP_CONSTANTS.PUSH,
2535 WIP_CONSTANTS.OP_PULL,
2536 WIP_CONSTANTS.ASSY_PULL)
2537 OR
2538 l_curItem.lot_control_code = WIP_CONSTANTS.NO_LOT
2539 OR
2540 l_curItem.serial_number_control_code IN(WIP_CONSTANTS.FULL_SN,
2541 WIP_CONSTANTS.DYN_RCV_SN)
2542 OR
2543 l_curItem.transaction_action_id <> WIP_CONSTANTS.RETCOMP_ACTION
2544 ) THEN
2545 GOTO END_OF_LOOP;
2546 ELSE
2547 -- Instead of defaulting revision to the current revision, we should
2548 -- derive revision that got transacted from forward move transaction.
2549 setItemRevision(p_parentObjID => p_objectID,
2550 p_orgID => p_orgID,
2551 p_item => l_curItem,
2552 x_compLots => x_compLots,
2553 x_returnStatus => l_returnStatus);
2554
2555 IF(l_returnStatus = fnd_api.g_ret_sts_unexp_error) THEN
2556 l_errMsg := 'setItemRevision failed';
2557 raise fnd_api.g_exc_unexpected_error;
2558 END IF;
2559 END IF;
2560
2561 deriveSingleItemFromMOG(p_parentObjID => p_objectID,
2562 p_orgID => p_orgID,
2563 p_item => l_curItem,
2564 x_compLots => x_compLots,
2565 x_returnStatus => l_returnStatus);
2566
2567 IF(l_returnStatus = fnd_api.g_ret_sts_unexp_error) THEN
2568 l_errMsg := 'deriveSingleItemFromMOG failed';
2569 raise fnd_api.g_exc_unexpected_error;
2570 END IF;
2571
2572 END IF; -- x_compLots.getCurrentItem(l_curItem)
2573 <<END_OF_LOOP>>
2574
2575 EXIT WHEN NOT x_compLots.setNextItem;
2576 END LOOP;
2577
2578 IF (l_logLevel <= wip_constants.trace_logging) THEN
2579 wip_logger.exitPoint(
2580 p_procName => 'wip_autoLotProc_priv.deriveLotsFromMOG',
2581 p_procReturnStatus => x_returnStatus,
2582 p_msg => 'procedure success',
2583 x_returnStatus => l_returnStatus);
2584 END IF;
2585
2586 EXCEPTION
2587 WHEN fnd_api.g_exc_unexpected_error THEN
2588 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2589 IF (l_logLevel <= wip_constants.trace_logging) THEN
2590 wip_logger.exitPoint(
2591 p_procName => 'wip_autoLotProc_priv.deriveLotsFromMOG',
2592 p_procReturnStatus => x_returnStatus,
2593 p_msg => l_errMsg,
2594 x_returnStatus => l_returnStatus); --discard return status
2595 END IF;
2596 ROLLBACK TO s_deriveLotsFromMOG;
2597
2598 WHEN others THEN
2599 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2600 IF (l_logLevel <= wip_constants.trace_logging) THEN
2601 wip_logger.exitPoint(
2602 p_procName => 'wip_autoLotProc_priv.deriveLotsFromMOG',
2603 p_procReturnStatus => x_returnStatus,
2604 p_msg => 'unexpected error:' || SQLERRM,
2605 x_returnStatus => l_returnStatus); --discard return status
2606 END IF;
2607 ROLLBACK TO s_deriveLotsFromMOG;
2608
2609 END deriveLotsFromMOG;
2610
2611 end wip_autoLotProc_priv;