[Home] [Help]
PACKAGE BODY: APPS.WIP_AUTOLOTPROC_PRIV
Source
1 package body wip_autoLotProc_priv as
2 /* $Header: wiplotpb.pls 120.6.12020000.2 2012/07/18 18:11:24 ankohli 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,
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
338 and mmtt.transaction_temp_id = mtlt.transaction_temp_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);
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;
442 end if;
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
574 if (l_logLevel <= wip_constants.trace_logging) then
575 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.deriveLotsFromMMTT',
576 p_procReturnStatus => x_returnStatus,
577 p_msg => 'procedure success',
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 l_serialTaggedCode NUMBER;
638
639 cursor c_allItems is
640 select mti.transaction_interface_id,
641 mti.operation_seq_num,
642 mti.inventory_item_id,
643 msi.concatenated_segments,
644 mti.primary_quantity * -1,
645 sum(mtli.primary_quantity),
646 mti.transaction_quantity * -1,
647 msi.primary_uom_code,
648 mti.subinventory_code,
649 mti.locator_id,
650 -- null,--mti.wip_supply_type,
651 mti.transaction_action_id,
652 msi.mtl_transactions_enabled_flag,
653 msi.serial_number_control_code,
654 msi.lot_control_code,
655 mti.revision,
656 mti.move_transaction_id,
657 mti.completion_transaction_id
658 from mtl_transactions_interface mti,
659 mtl_system_items_kfv msi,
660 mtl_transaction_lots_interface mtli
661 where mti.transaction_header_id = p_txnHdrID
662 and mti.transaction_action_id in (wip_constants.isscomp_action, wip_constants.retcomp_action,
663 wip_constants.issnegc_action, wip_constants.retnegc_action)
664 and mti.inventory_item_id = msi.inventory_item_id
665 and mti.organization_id = msi.organization_id
666 and mti.transaction_interface_id = mtli.transaction_interface_id (+)
667 group by mti.transaction_interface_id,
668 mti.operation_seq_num,
669 mti.inventory_item_id,
670 msi.concatenated_segments,
671 mti.primary_quantity * -1,
672 mti.transaction_quantity * -1,
673 msi.primary_uom_code,
674 mti.subinventory_code,
675 mti.locator_id,
676 -- null,--mti.wip_supply_type,
677 mti.transaction_action_id,
678 msi.mtl_transactions_enabled_flag,
679 msi.serial_number_control_code,
680 msi.lot_control_code,
681 mti.revision,
682 mti.move_transaction_id,
683 mti.completion_transaction_id
684 order by mti.inventory_item_id, mti.transaction_interface_id;
685
686 --backflush items
687 cursor c_bflItems is
688 select mti.transaction_interface_id,
689 mti.operation_seq_num,
690 mti.inventory_item_id,
691 msi.concatenated_segments,
692 mti.primary_quantity * -1,
693 sum(mtli.primary_quantity),
694 mti.transaction_quantity * -1,
695 msi.primary_uom_code,
696 mti.subinventory_code,
697 mti.locator_id,
698 -- null,--mti.wip_supply_type,
699 mti.transaction_action_id,
700 msi.mtl_transactions_enabled_flag,
701 msi.serial_number_control_code,
702 msi.lot_control_code,
703 mti.revision,
704 mti.move_transaction_id,
705 mti.completion_transaction_id
706 from mtl_transactions_interface mti,
707 mtl_system_items_kfv msi,
708 mtl_transaction_lots_interface mtli
709 where mti.transaction_header_id = p_txnHdrID
710 and ( mti.completion_transaction_id = p_cplTxnID
711 or mti.move_transaction_id in (p_movTxnID, p_childMovTxnID))
712 and mti.transaction_action_id in (wip_constants.isscomp_action, wip_constants.retcomp_action,
713 wip_constants.issnegc_action, wip_constants.retnegc_action)
714 and mti.inventory_item_id = msi.inventory_item_id
715 and mti.organization_id = msi.organization_id
716 and mti.transaction_interface_id = mtli.transaction_interface_id (+)
717 group by mti.transaction_interface_id,
718 mti.operation_seq_num,
719 mti.inventory_item_id,
720 msi.concatenated_segments,
721 mti.primary_quantity * -1,
722 mti.transaction_quantity * -1,
723 msi.primary_uom_code,
724 mti.subinventory_code,
725 mti.locator_id,
726 -- null,--mti.wip_supply_type,
727 mti.transaction_action_id,
728 msi.mtl_transactions_enabled_flag,
729 msi.serial_number_control_code,
730 msi.lot_control_code,
731 mti.revision,
732 mti.move_transaction_id,
733 mti.completion_transaction_id
734 order by mti.inventory_item_id, mti.transaction_interface_id;
735
736 /* no move transaction id in MTI so don''t support for now
737 cursor c_movItems return itemInfo_rec_t is
738 select mti.transaction_temp_id,
739 mti.operation_seq_num,
740 mti.inventory_item_id,
741 msi.concatenated_segments,
742 mti.primary_quantity * -1,
743 sum(mtli.primary_quantity),
744 mti.transaction_quantity * -1,
745 msi.primary_uom_code,
746 mti.subinventory_code,
747 mti.locator_id,
748 mti.wip_supply_type,
749 msi.mtl_transactions_enabled_flag,
750 msi.serial_number_control_code,
751 msi.lot_control_code,
752 mti.revision
753 from mtl_material_transactions_temp mti,
754 mtl_system_items_kfv msi,
755 mtl_transaction_lots_temp mtlt
756 where mti.move_transaction_id = p_movTxnID
757 and mti.transaction_action_id in (wip_constants.isscomp_action, wip_constants.retcomp_action,
758 wip_constants.issnegc_action, wip_constants.retnegc_action)
759 and mti.inventory_item_id = msi.inventory_item_id
760 and mti.organization_id = msi.organization_id
761 and mti.transaction_interface_id = mtlt.transaction_interface_id (+)
762 group by mti.transaction_temp_id,
763 mti.operation_seq_num,
764 mti.inventory_item_id,
765 msi.concatenated_segments,
766 mti.primary_quantity * -1,
767 mti.transaction_quantity * -1,
768 msi.primary_uom_code,
769 mti.subinventory_code,
770 mti.locator_id,
771 mti.wip_supply_type,
772 msi.mtl_transactions_enabled_flag,
773 msi.serial_number_control_code,
774 msi.lot_control_code,
775 mti.revision
776 order by mti.inventory_item_id, mti.transaction_temp_id;
777 */
778 begin
779 x_returnStatus := fnd_api.g_ret_sts_success;
780 savepoint wiplotpb_10;
781
782 if (l_logLevel <= wip_constants.trace_logging) then
783 l_params(1).paramName := 'p_txnHdrID';
784 l_params(1).paramValue := p_txnHdrID;
785 l_params(2).paramName := 'p_cplTxnID';
786 l_params(2).paramValue := p_cplTxnID;
787 l_params(3).paramName := 'p_movTxnID';
788 l_params(3).paramValue := p_movTxnID;
789 l_params(4).paramName := 'p_orgID';
790 l_params(4).paramValue := p_orgID;
791 l_params(5).paramName := 'p_wipEntityID';
792 l_params(5).paramValue := p_wipEntityID;
793 wip_logger.entryPoint(p_procName => 'wip_autoLotProc_priv.deriveLotsFromMTI',
794 p_params => l_params,
795 x_returnStatus => x_returnStatus);
796 end if;
797
798 if(fnd_api.to_boolean(p_initMsgList)) then
799 fnd_msg_pub.initialize;
800 end if;
801
802 l_compObj := system.wip_lot_serial_obj_t(null,null,null,null,null,null);
803 l_compObj.initialize;
804
805 if(p_cplTxnID is null and p_movTxnID is null) then
806 open c_allItems;
807 fetch c_allItems
808 bulk collect into l_itemRecTbl.txnID,
809 l_itemRecTbl.opSeqNum,
810 l_itemRecTbl.itemID,
811 l_itemRecTbl.itemName,
812 l_itemRecTbl.priQty,
813 l_itemRecTbl.lotPriQty,
814 l_itemRecTbl.txnQty,
815 l_itemRecTbl.priUomCode,
816 l_itemRecTbl.supplySubinv,
817 l_itemRecTbl.supplyLocID,
818 -- l_itemRecTbl.wipSupplyType,
819 l_itemRecTbl.txnActionID,
820 l_itemRecTbl.txnsEnabledFlag,
821 l_itemRecTbl.serialControlCode,
822 l_itemRecTbl.lotControlCode,
823 l_itemRecTbl.revision,
824 l_itemRecTbl.movTxnID,
825 l_itemRecTbl.cplTxnID;
826
827 close c_allItems;
828 else
829 open c_bflItems;
830 fetch c_bflItems
831 bulk collect into l_itemRecTbl.txnID,
832 l_itemRecTbl.opSeqNum,
833 l_itemRecTbl.itemID,
834 l_itemRecTbl.itemName,
835 l_itemRecTbl.priQty,
836 l_itemRecTbl.lotPriQty,
837 l_itemRecTbl.txnQty,
838 l_itemRecTbl.priUomCode,
839 l_itemRecTbl.supplySubinv,
840 l_itemRecTbl.supplyLocID,
841 -- l_itemRecTbl.wipSupplyType,
842 l_itemRecTbl.txnActionID,
843 l_itemRecTbl.txnsEnabledFlag,
844 l_itemRecTbl.serialControlCode,
845 l_itemRecTbl.lotControlCode,
846 l_itemRecTbl.revision,
847 l_itemRecTbl.movTxnID,
848 l_itemRecTbl.cplTxnID;
849
850 close c_bflItems;
851 end if;
852
853 for i in 1..l_itemRecTbl.itemID.count loop
854 <<START_OF_OUTER_LOOP>>
855 if (l_logLevel <= wip_constants.full_logging) then
856 wip_logger.log('priQty:' || l_itemRecTbl.priQty(i) || '; lot quantity:' || l_itemRecTbl.lotPriQty(i), l_returnStatus);
857 end if;
858
859 if(abs(l_itemRecTbl.priQty(i)) > abs(nvl(l_itemRecTbl.lotPriQty(i), 0))) then
860 if (l_logLevel <= wip_constants.full_logging) then
861 wip_logger.log('adding item ' || l_itemRecTbl.itemName(i) || ';' || l_itemRecTbl.itemID(i), l_returnStatus);
862 end if;
863
864 l_serialTaggedCode := inv_cache.get_serial_tagged(p_orgID, l_itemRecTbl.itemID(i), getTxnType(l_itemRecTbl.txnActionID(i)));
865
866 if(l_itemRecTbl.lotControlCode(i) = wip_constants.lot) then
867 if(l_itemRecTbl.movTxnID(i) is not null) then
868 l_supType := wip_constants.op_pull;
869 elsif(l_itemRecTbl.cplTxnID(i) is not null) then
870 l_supType := wip_constants.assy_pull;
871 else
872 l_supType := wip_constants.push;
873 end if;
874
875 l_compObj.addItem(p_opSeqNum => l_itemRecTbl.opSeqNum(i),
876 p_itemID => l_itemRecTbl.itemID(i),
877 p_itemName => l_itemRecTbl.itemName(i),
878 p_priQty => l_itemRecTbl.priQty(i) - sign(l_itemRecTbl.priQty(i)) * nvl(l_itemRecTbl.lotPriQty(i), 0),
879 p_priUomCode => l_itemRecTbl.priUomCode(i),
880 p_supplySubinv => l_itemRecTbl.supplySubinv(i),
881 p_supplyLocID => l_itemRecTbl.supplyLocID(i),
882 p_wipSupplyType => l_supType,
883 p_txnActionID => l_itemRecTbl.txnActionID(i),
884 p_mtlTxnsEnabledFlag => l_itemRecTbl.txnsEnabledFlag(i),
885 p_revision => l_itemRecTbl.revision(i),
886 p_lotControlCode => l_itemRecTbl.lotControlCode(i),
887 p_serialControlCode => l_itemRecTbl.serialControlCode(i),
888 p_genericID => l_itemRecTbl.txnID(i));
889 elsif(l_itemRecTbl.serialControlCode(i) in (wip_constants.full_sn, wip_constants.dyn_rcv_sn)) then
890 --see if we've derived the entire serial quantity
891 checkSerial(p_txnTmpID => null, --since using interface table
892 p_txnIntID => l_itemRecTbl.txnID(i),
893 p_qty => abs(l_itemRecTbl.priQty(i)),
894 p_itemID => l_itemRecTbl.itemID(i),
895 p_itemName => l_itemRecTbl.itemName(i),
896 p_orgID => p_orgID,
897 p_revision => l_itemRecTbl.revision(i),
898 p_subinv => l_itemRecTbl.supplySubinv(i),
899 p_locID => l_itemRecTbl.supplyLocID(i),
900 p_txnActionID => l_itemRecTbl.txnActionID(i),
901 p_serControlCode => l_itemRecTbl.serialControlCode(i),
902 x_serialReturnStatus => l_tempReturnStatus,
903 x_returnStatus => x_returnStatus);
904 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
905 l_errMsg := 'check serial failed';
906 raise fnd_api.g_exc_unexpected_error;
907 end if;
908 l_serialReturnStatus := worstReturnStatus(l_serialReturnStatus, l_tempReturnStatus);
909 elsif (l_serialTaggedCode = WIP_CONSTANTS.SER_TAGGED) then -- bug fix 10053619
910 l_errMsg := 'item under serial control';
911 l_serialReturnStatus := fnd_api.g_ret_sts_error;
912 end if;
913 end if;
914 end loop;
915
916 deriveLots(x_compLots => l_compObj,
917 p_orgID => p_orgID,
918 p_wipEntityID => p_wipEntityID,
919 p_initMsgList => fnd_api.g_false,
920 p_endDebug => fnd_api.g_false,
921 p_destroyTrees => fnd_api.g_true,
922 p_treeMode => inv_quantity_tree_pvt.g_reservation_mode,
923 p_treeSrcName => null,
924 x_returnStatus => l_lotReturnStatus);
925
926 x_returnStatus := worstReturnStatus(l_serialReturnStatus, l_lotReturnStatus);
927 if(x_returnStatus = fnd_api.g_ret_sts_unexp_error) then
928 l_errMsg := 'derive lots failed';
929 raise fnd_api.g_exc_unexpected_error;
930 end if;
931
932 --otherwise we at least derived some lot info
933 l_compObj.reset;
934 --2nd pass: update all the mti rows with lot info
935 for i in 1..l_itemRecTbl.txnID.count loop
936 if (l_logLevel <= wip_constants.full_logging) then
937 wip_logger.log('start outer loop for item' || l_itemRecTbl.itemID(i), l_returnStatus);
938 end if;
939
940 if(l_itemRecTbl.lotControlCode(i) <> wip_constants.lot) then
941 goto END_OF_OUTER_LOOP;
942 end if;
943
944 loop
945 if (l_logLevel <= wip_constants.full_logging) then
946 wip_logger.log('start inner loop1', l_returnStatus);
947 end if;
948 if(l_compObj.setNextItem) then
949 if(not l_compObj.getCurrentItem(l_item)) then
950 l_errMsg := 'object error';
951 raise fnd_api.g_exc_unexpected_error;
952 end if;
953 if (l_logLevel <= wip_constants.full_logging) then
954 wip_logger.log('found item: ' || l_item.inventory_item_id, l_returnStatus);
955 end if;
956
957 if(l_item.inventory_item_id = l_itemRecTbl.itemID(i) and
958 l_item.supply_subinventory = l_itemRecTbl.supplySubinv(i) and
959 nvl(l_item.supply_locator_id, -1) = nvl(l_itemRecTbl.supplyLocID(i), -1) and
960 l_item.operation_seq_num = l_itemRecTbl.opSeqNum(i) and
961 l_item.primary_quantity = l_itemRecTbl.priQty(i)) then
962 if (l_logLevel <= wip_constants.full_logging) then
963 wip_logger.log('item: ' || l_item.inventory_item_id || ' matches cursor item', l_returnStatus);
964 end if;
965 exit; --found an item to match the cursor
966 end if;
967 else
968 if (l_logLevel <= wip_constants.full_logging) then
969 wip_logger.log('ran out of items', l_returnStatus);
970 end if;
971 goto END_OF_OUTER_LOOP; --must exit inner and outer loop!
972 end if;
973 end loop;
974
975
976 while(l_compObj.getNextLot(l_lot)) loop
977 if (l_logLevel <= wip_constants.full_logging) then
978 wip_logger.log('start inner loop2', l_returnStatus);
979 end if;
980 insert into mtl_transaction_lots_interface
981 (transaction_interface_id,
982 last_update_date,
983 last_updated_by,
984 creation_date,
985 created_by,
986 last_update_login,
987 request_id,
988 program_application_id,
989 program_id,
990 program_update_date,
991 transaction_quantity,
992 primary_quantity,
993 lot_number)
994 values
995 (l_itemRecTbl.txnID(i),
996 sysdate,
997 fnd_global.user_id,
998 sysdate,
999 fnd_global.user_id,
1000 fnd_global.login_id,
1001 fnd_global.conc_request_id,
1002 fnd_global.prog_appl_id,
1003 fnd_global.conc_program_id,
1004 sysdate,
1005 abs(round(l_lot.primary_quantity * (l_itemRecTbl.txnQty(i)/
1006 l_itemRecTbl.priQty(i)), wip_constants.inv_max_precision)),
1007 abs(round(l_lot.primary_quantity, wip_constants.inv_max_precision)),
1008 l_lot.lot_number);
1009 end loop;
1010 <<END_OF_OUTER_LOOP>>
1011 if (l_logLevel <= wip_constants.full_logging) then
1012 wip_logger.log('end outer loop', l_returnStatus);
1013 end if;
1014 end loop;
1015
1016 --return status has already been set at this point
1017 if (l_logLevel <= wip_constants.trace_logging) then
1018 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.deriveLotsFromMTI',
1019 p_procReturnStatus => x_returnStatus,
1020 p_msg => 'procedure success',
1021 x_returnStatus => l_returnStatus); --discard logging return status
1022 end if;
1023 if(fnd_api.to_boolean(p_endDebug)) then
1024 wip_logger.cleanup(x_returnStatus => l_returnStatus);
1025 end if;
1026 exception
1027 when fnd_api.g_exc_unexpected_error then
1028 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1029 rollback to wiplotpb_10;
1030 if (l_logLevel <= wip_constants.trace_logging) then
1031 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.deriveLotsFromMTI',
1032 p_procReturnStatus => x_returnStatus,
1033 p_msg => l_errMsg,
1034 x_returnStatus => l_returnStatus); --discard logging return status
1035 end if;
1036 if(fnd_api.to_boolean(p_endDebug)) then
1037 wip_logger.cleanup(x_returnStatus => l_returnStatus);
1038 end if;
1039 when others then
1040 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1041 rollback to wiplotpb_10;
1042 fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_autoLotProc_priv',
1043 p_procedure_name => 'deriveLotsFromMTI',
1044 p_error_text => SQLERRM);
1045 if (l_logLevel <= wip_constants.trace_logging) then
1046 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.deriveLotsFromMTI',
1047 p_procReturnStatus => x_returnStatus,
1048 p_msg => 'unexpected error:' || SQLERRM,
1049 x_returnStatus => l_returnStatus); --discard logging return status
1050 end if;
1051 if(fnd_api.to_boolean(p_endDebug)) then
1052 wip_logger.cleanup(x_returnStatus => l_returnStatus);
1053 end if;
1054 end deriveLotsFromMTI;
1055
1056
1057 --derive lots procedure for return txns
1058 procedure deriveTxnLots(p_orgID IN NUMBER,
1059 p_wipEntityID IN NUMBER, --only needed for returns and neg returns
1060 p_txnActionID IN NUMBER,
1061 p_entryType IN NUMBER,
1062 x_compLots IN OUT NOCOPY system.wip_lot_serial_obj_t,
1063 x_returnStatus OUT NOCOPY VARCHAR2) is
1064
1065 cursor c_retTxnBasedLots(v_itemID NUMBER) is
1066 select tln.lot_number,
1067 max(mln.expiration_date),
1068 abs(round(sum(tln.primary_quantity), wip_constants.max_displayed_precision))
1069 from mtl_transaction_lot_numbers tln,
1070 mtl_material_transactions mmt,
1071 mtl_lot_numbers mln
1072 where tln.organization_id = p_orgID
1073 and tln.transaction_source_id = p_wipEntityID
1074 and tln.transaction_source_type_id = 5
1075 and tln.inventory_item_id = v_itemID
1076 and tln.organization_id = mln.organization_id
1077 and tln.inventory_item_id = mln.inventory_item_id
1078 and tln.lot_number = mln.lot_number
1079 and nvl(mln.expiration_date, sysdate + 1) > sysdate
1080 and mmt.transaction_id = tln.transaction_id
1081 and mmt.transaction_action_id in (wip_constants.isscomp_action, wip_constants.retcomp_action)
1082 group by tln.lot_number
1083 having sign(round(sum(tln.primary_quantity), wip_constants.max_displayed_precision)) < 0 --more issued quantity than returned
1084 order by max(sign(round(tln.primary_quantity, wip_constants.max_displayed_precision))), --give priority to lots that have ret txns
1085 max(tln.transaction_date) desc, --then sort by most recent txn date
1086 tln.lot_number desc; --finally sort by lot number, descending b/c issues are ascending
1087
1088 cursor c_negRetTxnBasedLots(v_itemID NUMBER) is
1089 select tln.lot_number,
1090 max(mln.expiration_date),
1091 abs(round(sum(tln.primary_quantity), wip_constants.max_displayed_precision))
1092 from mtl_transaction_lot_numbers tln,
1093 mtl_material_transactions mmt,
1094 mtl_lot_numbers mln
1095 where tln.organization_id = p_orgID
1096 and tln.transaction_source_id = p_wipEntityID
1097 and tln.transaction_source_type_id = 5
1098 and tln.inventory_item_id = v_itemID
1099 and tln.organization_id = mln.organization_id
1100 and tln.inventory_item_id = mln.inventory_item_id
1101 and tln.lot_number = mln.lot_number
1102 and nvl(mln.expiration_date, sysdate + 1) > sysdate
1103 and mmt.transaction_id = tln.transaction_id
1104 and mmt.transaction_action_id in (wip_constants.issnegc_action, wip_constants.retnegc_action)
1105 group by tln.lot_number
1106 having sign(round(sum(tln.primary_quantity), wip_constants.max_displayed_precision)) > 0 --more neg issues than neg returns
1107 order by max(sign(round(tln.primary_quantity, wip_constants.max_displayed_precision))), --give priority to lots that have ret txns
1108 max(tln.transaction_date) desc, --then sort by most recent txn date
1109 tln.lot_number desc; --finally sort by lot number, descending b/c issues are ascending
1110
1111 l_item system.wip_component_obj_t;
1112 l_rmnQty NUMBER;
1113 /* ER 4378835: Increased length of l_lotNumber from 30 to 80 to support OPM Lot-model changes */
1114 l_lotNumber VARCHAR2(80);
1115 l_expDate DATE;
1116 l_lotQty NUMBER;
1117 l_cond boolean;
1118 l_params wip_logger.param_tbl_t;
1119 l_errMsg VARCHAR2(80);
1120 l_returnStatus VARCHAR2(1);
1121 l_enabled VARCHAR2(1);
1122 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
1123 begin
1124 x_returnStatus := fnd_api.g_ret_sts_success;
1125 l_cond := x_compLots.getCurrentItem(l_item);
1126
1127 if (l_logLevel <= wip_constants.trace_logging) then
1128 l_params(1).paramName := 'p_orgID';
1129 l_params(1).paramValue := p_orgID;
1130 l_params(2).paramName := 'p_wipEntityID';
1131 l_params(2).paramValue := p_wipEntityID;
1132 l_params(3).paramName := 'p_txnActionID';
1133 l_params(3).paramValue := p_txnActionID;
1134 if(l_cond) then
1135 l_params(4).paramName := 'x_compLot(cur_item).inventory_item_id';
1136 l_params(4).paramValue := l_item.inventory_item_id;
1137 l_params(5).paramName := 'x_compLot(cur_item).supply_subinventory';
1138 l_params(5).paramValue := l_item.supply_subinventory;
1139 l_params(6).paramName := 'x_compLot(cur_item).supply_locator_id';
1140 l_params(6).paramValue := l_item.supply_locator_id;
1141 l_params(7).paramName := 'x_compLot(cur_item).revision';
1142 l_params(7).paramValue := l_item.revision;
1143 l_params(8).paramName := 'x_compLot(cur_item).primary_quantity';
1144 l_params(8).paramValue := l_item.primary_quantity;
1145 end if;
1146 wip_logger.entryPoint(p_procName => 'wip_autoLotProc_priv.deriveTxnLots',
1147 p_params => l_params,
1148 x_returnStatus => x_returnStatus);
1149 end if;
1150
1151 if (not l_cond) then
1152 l_errMsg := 'current item not set';
1153 raise fnd_api.g_exc_unexpected_error;
1154 end if;
1155
1156 l_rmnQty := abs(l_item.primary_quantity);
1157
1158 if(p_txnActionID = wip_constants.retcomp_action) then
1159 open c_retTxnBasedLots(v_itemID => l_item.inventory_item_id);
1160 else
1161 open c_negRetTxnBasedLots(v_itemID => l_item.inventory_item_id);
1162 end if;
1163
1164 loop
1165 if(p_txnActionID = wip_constants.retcomp_action) then
1166 fetch c_retTxnBasedLots into l_lotNumber, l_expDate, l_lotQty;
1167 exit when c_retTxnBasedLots%NOTFOUND;
1168 else
1169 fetch c_negRetTxnBasedLots into l_lotNumber, l_expDate, l_lotQty;
1170 exit when c_negRetTxnBasedLots%NOTFOUND;
1171 end if;
1172
1173 if (l_logLevel <= wip_constants.full_logging) then
1174 wip_logger.log('lot: ' || l_lotNumber || '; qty: ' || l_lotQty, l_returnStatus);
1175 end if;
1176 l_enabled := wip_utilities.is_status_applicable(p_trx_type_id => getTxnType(l_item.transaction_action_id),
1177 p_organization_id => p_orgID,
1178 p_inventory_item_id => l_item.inventory_item_id,
1179 p_sub_code => l_item.supply_subinventory,
1180 p_locator_id => l_item.supply_locator_id,
1181 p_lot_number => l_lotNumber,
1182 p_object_type => 'O');
1183 if(l_enabled <> 'Y') then
1184 if (l_logLevel <= wip_constants.full_logging) then
1185 wip_logger.log('lot is not enabled', l_returnStatus);
1186 end if;
1187 goto END_OF_LOOP;
1188 end if;
1189 --processing here is slightly different than deriveIssueLots b/c sign of quantities could be either + or -
1190 if(l_lotQty >= l_rmnQty) then --lot has more than we need. only fill in the remaining qty
1191 x_compLots.addLot(p_lotNumber => l_lotNumber,
1192 p_priQty => l_rmnQty,
1193 p_attributes => null);
1194 l_rmnQty := 0;
1195 exit;
1196 else --exhaust all remaining qty in the lot
1197 x_compLots.addLot(p_lotNumber => l_lotNumber,
1198 p_priQty => l_lotQty,
1199 p_attributes => null);
1200 l_rmnQty := l_rmnQty - l_lotQty;
1201 end if;
1202 <<END_OF_LOOP>>
1203 null;
1204 end loop;
1205
1206 if(c_retTxnBasedLots%ISOPEN) then
1207 close c_retTxnBasedLots;
1208 elsif(c_retTxnBasedLots%ISOPEN) then
1209 close c_negRetTxnBasedLots;
1210 end if;
1211
1212 if(l_rmnQty <> 0) then
1213 l_errMsg := 'could not derive all qty. ' || l_rmnQty || ' remaining.';
1214 raise fnd_api.g_exc_error;
1215 end if;
1216
1217 if (l_logLevel <= wip_constants.trace_logging) then
1218 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.deriveTxnLots',
1219 p_procReturnStatus => x_returnStatus,
1220 p_msg => 'procedure success',
1221 x_returnStatus => l_returnStatus); --discard logging return status
1222 end if;
1223
1224 exception
1225 when fnd_api.g_exc_error then
1226 x_returnStatus:= fnd_api.g_ret_sts_error; --let caller know item was not fully derived
1227 if (l_logLevel <= wip_constants.trace_logging) then
1228 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.deriveTxnLots',
1229 p_procReturnStatus => x_returnStatus,
1230 p_msg => l_errMsg,
1231 x_returnStatus => l_returnStatus); --discard logging return status
1232 end if;
1233 when fnd_api.g_exc_unexpected_error then
1234 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1235 if (l_logLevel <= wip_constants.trace_logging) then
1236 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.deriveTxnLots',
1237 p_procReturnStatus => x_returnStatus,
1238 p_msg => 'error: ' || l_errMsg,
1239 x_returnStatus => l_returnStatus); --discard logging return status
1240 end if;
1241 when others then
1242 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1243 if(c_retTxnBasedLots%ISOPEN) then
1244 close c_retTxnBasedLots;
1245 elsif(c_retTxnBasedLots%ISOPEN) then
1246 close c_negRetTxnBasedLots;
1247 end if;
1248 fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_autoLotProc_priv',
1249 p_procedure_name => 'deriveTxnLots',
1250 p_error_text => SQLERRM);
1251 if (l_logLevel <= wip_constants.trace_logging) then
1252 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.deriveTxnLots',
1253 p_procReturnStatus => x_returnStatus,
1254 p_msg => 'error: ' || SQLERRM,
1255 x_returnStatus => l_returnStatus); --discard logging return status
1256 end if;
1257 end deriveTxnLots;
1258
1259 --derive lots procedure for issue txns
1260 procedure deriveIssueLots(p_orgID IN NUMBER,
1261 p_wipentityID IN NUMBER,
1262 p_entryType IN NUMBER,
1263 p_treeMode IN NUMBER,
1264 p_treeSrcName IN VARCHAR2,
1265 x_treeID IN OUT NOCOPY NUMBER,
1266 x_compLots IN OUT NOCOPY system.wip_lot_serial_obj_t,
1267 x_returnStatus OUT NOCOPY VARCHAR2) is
1268 l_rmnQty NUMBER;
1269 l_item system.wip_component_obj_t;
1270 l_treeID NUMBER;
1271 /* ER 4378835: Increased length of l_lotNumber from 30 to 80 to support OPM Lot-model changes */
1272 l_lotNumber VARCHAR2(80);
1273 l_returnStatus VARCHAR2(1);
1274 l_msgData VARCHAR2(240);
1275 l_errMsg VARCHAR2(240);
1276 l_expDate DATE;
1277 l_msgCount NUMBER;
1278 l_qtyOnHand NUMBER;
1279 l_rsvableQtyOnHand NUMBER;
1280 l_qtyRsved NUMBER;
1281 l_qtySuggested NUMBER;
1282 l_qtyAvailToRsv NUMBER;
1283 l_qtyAvailToTxt NUMBER;
1284 l_qtyOnHand2 NUMBER;
1285 l_rsvableQtyOnHand2 NUMBER;
1286 l_qtyRsved2 NUMBER;
1287 l_qtySuggested2 NUMBER;
1288 l_qtyAvailToRsv2 NUMBER;
1289 l_qtyAvailToTxt2 NUMBER;
1290 l_params wip_logger.param_tbl_t;
1291 l_cond boolean;
1292 l_enabled VARCHAR2(1);
1293 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
1294 /* Added for Wilson Greatbatch Enhancement */
1295 l_alt_lot_selection_method NUMBER ;
1296 l_flag NUMBER ;
1297 l_lot_qty_selected NUMBER ; /* Fix for Bug#4956543 */
1298 l_custom_lot_number VARCHAR2(80);
1299 l_custom_returnStatus VARCHAR2(1);
1300 l_custom_returnMessage VARCHAR2(240);
1301 l_demand_source_header_id Number := 0;
1302 cursor c_receiptOrderedLots(v_itemID NUMBER,
1303 v_supplySubinv VARCHAR2,
1304 v_supplyLocID NUMBER,
1305 v_revision VARCHAR2) is
1306 select moq.lot_number,
1307 min(mln.expiration_date)
1308 from mtl_lot_numbers mln,
1309 mtl_onhand_quantities_detail moq
1310 where moq.inventory_item_id = v_itemID
1311 and moq.organization_id = p_orgID
1312 and moq.subinventory_code = v_supplySubinv
1313 and nvl(moq.locator_id, -1) = nvl(v_supplyLocID, -1)
1314 and nvl(moq.revision, 'NONE') = nvl(v_revision, 'NONE')
1315 and mln.lot_number = moq.lot_number
1316 and mln.inventory_item_id = moq.inventory_item_id
1317 and mln.organization_id = moq.organization_id
1318 and nvl(mln.expiration_date, sysdate + 1) > sysdate
1319 group by moq.lot_number
1320 order by min(moq.date_received), moq.lot_number;
1321
1322 cursor c_expDateOrderedLots(v_itemID NUMBER,
1323 v_supplySubinv VARCHAR2,
1324 v_supplyLocID NUMBER,
1325 v_revision VARCHAR2) is
1326 select moq.lot_number,
1327 min(mln.expiration_date)
1328 from mtl_lot_numbers mln,
1329 mtl_onhand_quantities_detail moq
1330 where moq.inventory_item_id = v_itemID
1331 and moq.organization_id = p_orgID
1332 and moq.subinventory_code = v_supplySubinv
1333 and nvl(moq.locator_id, -1) = nvl(v_supplyLocID, -1)
1334 and nvl(moq.revision, 'NONE') = nvl(v_revision, 'NONE')
1335 and mln.lot_number = moq.lot_number
1336 and mln.inventory_item_id = moq.inventory_item_id
1337 and mln.organization_id = moq.organization_id
1338 and nvl(mln.expiration_date, sysdate + 1) > sysdate
1339 group by moq.lot_number
1340 order by min(mln.expiration_date),
1341 min(moq.date_received),
1342 moq.lot_number;
1343
1344 /* Added for Wilson Greatbatch Enhancement */
1345
1346 cursor c_TxnHistoryOrderedLots(v_itemID NUMBER,
1347 v_supplySubinv VARCHAR2,
1348 v_supplyLocID NUMBER,
1349 v_revision VARCHAR2) is
1350 select tln.lot_number
1351 from mtl_transaction_lot_numbers tln ,
1352 mtl_lot_numbers mln ,
1353 mtl_onhand_quantities_detail moq
1354 where tln.transaction_date =
1355 ( select max(transaction_date)
1356 from mtl_material_transactions
1357 where organization_id = p_OrgID
1358 and transaction_source_id =p_wipEntityID
1359 and transaction_source_type_id = 5
1360 and inventory_item_id = v_ItemId
1361 and ( MOVE_TRANSACTION_ID IS NOT NULL or
1362 COMPLETION_TRANSACTION_ID IS NOT NULL )
1363 )
1364 and tln.organization_id = moq.organization_id
1365 and tln.inventory_item_id = moq.inventory_item_id
1366 and tln.lot_number = moq.lot_number
1367 and tln.lot_number = mln.lot_number
1368 and moq.subinventory_code = v_supplySubinv
1369 and nvl(moq.locator_id, -1) = nvl(v_supplyLocID, -1)
1370 and nvl(moq.revision, 'NONE') = nvl(v_revision, 'NONE')
1371 and nvl(mln.expiration_date, sysdate + 1) > sysdate
1372 group by tln.lot_number
1373 order by tln.lot_number ;
1374 begin
1375 x_returnStatus := fnd_api.g_ret_sts_success;
1376 l_cond := x_compLots.getCurrentItem(l_item);
1377
1378 if (l_logLevel <= wip_constants.trace_logging) then
1379 l_params(1).paramName := 'p_orgID';
1380 l_params(1).paramValue := p_orgID;
1381 l_params(2).paramName := 'p_treeMode';
1382 l_params(2).paramValue := p_treeMode;
1383 l_params(3).paramName := 'p_treeSrcName';
1384 l_params(3).paramValue := p_treeSrcName;
1385 l_params(4).paramName := 'x_treeID';
1386 l_params(5).paramValue := x_treeID;
1387 if(l_cond) then
1388
1389 l_params(4).paramName := 'x_compLot(cur_item).inventory_item_id';
1390 l_params(4).paramValue := l_item.inventory_item_id;
1391 l_params(5).paramName := 'x_compLot(cur_item).supply_subinventory';
1392 l_params(5).paramValue := l_item.supply_subinventory;
1393 l_params(6).paramName := 'x_compLot(cur_item).supply_locator_id';
1394 l_params(6).paramValue := l_item.supply_locator_id;
1395 l_params(7).paramName := 'x_compLot(cur_item).revision';
1396 l_params(7).paramValue := l_item.revision;
1397 l_params(8).paramName := 'x_compLot(cur_item).primary_quantity';
1398 l_params(8).paramValue := l_item.primary_quantity;
1399 end if;
1400 wip_logger.entryPoint(p_procName => 'wip_autoLotProc_priv.deriveIssueLots',
1401 p_params => l_params,
1402 x_returnStatus => x_returnStatus);
1403 end if;
1404
1405 /*Calling MES Hook for Lot Derivation for Express Move*/
1406 WIP_WS_CUSTOM.LOT_HOOK(
1407 org_id => p_orgID,
1408 wip_entity_id => p_wipentityID,
1409 operation_seq_num => l_item.operation_seq_num,
1410 repetitive_schedule_id => null,
1411 inventory_item_id => l_item.inventory_item_id,
1412 supply_subinv => l_item.supply_subinventory,
1413 supply_locator_id => l_item.supply_locator_id,
1414 revision => l_item.revision,
1415 primary_qty => l_item.primary_quantity,
1416 lot_number => l_custom_lot_number,
1417 return_status => l_custom_returnStatus, --Values 'S': Success and 'E': Error
1418 return_message => l_custom_returnMessage);
1419 --Lot Hook can return 3 cases.
1420 if(l_custom_returnStatus = 'S' and l_custom_lot_number is not null) then
1421 --Case 1: Success and a Lot Number. Carry out the transaction with value provided by the hook
1422 if (l_logLevel <= wip_constants.full_logging) then
1423 wip_logger.log('Custom Hook returned Lot Number: ' || l_custom_lot_number, l_returnStatus );
1424 wip_logger.log('adding lot ' || l_custom_lot_number || 'w/qty ' || l_item.primary_quantity, l_returnStatus);
1425 end if;
1426
1427 x_compLots.addLot(p_lotNumber => l_custom_lot_number,
1428 p_priQty => l_item.primary_quantity,
1429 p_attributes => null);
1430 x_returnStatus := l_returnStatus;
1431 return;
1432 elsif(l_custom_returnStatus <> 'S') then
1433 --Case 2: Error. In this case we fail the transaction
1434 if (l_logLevel <= wip_constants.full_logging) then
1435 wip_logger.log('Custom Hook errored out and hence no value is stamped for lot number. If express move it will eventually fail since required lot number not provided.', l_returnStatus);
1436 end if;
1437 return;
1438 else
1439 --Case 3: Success and no Lot number. In this case proceed with Lot Derivation logic
1440 if (l_logLevel <= wip_constants.full_logging) then
1441 wip_logger.log('Custom Hook successfully returned, but NO value returned. This means hook has nothing to return in this case. Thus lot derivation logic will proceed to derive the lot', l_returnStatus);
1442 end if;
1443 end if;
1444
1445 if(l_cond) then
1446 l_rmnQty := l_item.primary_quantity;
1447 else
1448 l_rmnQty := 0;
1449 end if;
1450
1451 if(x_treeID is null) then
1452
1453 begin
1454 select wip_entity_id
1455 into l_demand_source_header_id
1456 from wip_flow_schedules
1457 where wip_entity_id = p_wipentityID
1458 and organization_id = p_orgID
1459 and scheduled_flag = 1;
1460 exception
1461 when others then
1462 l_demand_source_header_id := 0;
1463 end;
1464
1465 inv_quantity_tree_pvt.create_tree(p_api_version_number => 1.0,
1466 p_init_msg_lst => fnd_api.g_false,
1467 p_organization_id => p_orgID,
1468 p_inventory_item_id => l_item.inventory_item_id,
1469 p_tree_mode => p_treeMode,
1470 p_is_revision_control => (l_item.revision is not null),
1471 p_is_lot_control => true,
1472 p_is_serial_control => false,
1473 p_asset_sub_only => false,
1474 p_include_suggestion => false,
1475 p_demand_source_type_id => 5, --wip...set to match INVTTMTX form's trees
1476 p_demand_source_header_id => l_demand_source_header_id, --set to match INVTTMTX form's trees Change value from -1 to 0 for fix a bug 7561942
1477 p_demand_source_line_id => null, --set to match INVTTMTX form's trees
1478 p_demand_source_name => p_treeSrcName,
1479 p_demand_source_delivery => null,
1480 p_lot_expiration_date => null,
1481 x_return_status => x_returnStatus,
1482 x_msg_count => l_msgCount,
1483 x_msg_data => l_msgData,
1484 x_tree_id => x_treeID);
1485 end if;
1486
1487 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
1488 l_errMsg := 'tree creation failed';
1489 raise fnd_api.g_exc_unexpected_error;
1490 end if;
1491
1492 if(p_entryType in (wip_constants.recdate_full, wip_constants.recdate_exc)) then
1493 open c_receiptOrderedLots(v_itemID => l_item.inventory_item_id,
1494 v_supplySubinv => l_item.supply_subinventory,
1495 v_supplyLociD => l_item.supply_locator_id,
1496 v_revision => l_item.revision);
1497 elsif(p_entryType in (wip_constants.expdate_full, wip_constants.expdate_exc)) then
1498 open c_expDateOrderedLots(v_itemID => l_item.inventory_item_id,
1499 v_supplySubinv => l_item.supply_subinventory,
1500 v_supplyLociD => l_item.supply_locator_id,
1501 v_revision => l_item.revision);
1502 /* Added for Wilson Greatbatch Enhancement */
1503 elsif(p_entryType in (wip_constants.txnHistory_full, wip_constants.txnHistory_exc)) then
1504 open c_TxnHistoryOrderedLots(v_itemID => l_item.inventory_item_id,
1505 v_supplySubinv => l_item.supply_subinventory,
1506 v_supplyLociD => l_item.supply_locator_id,
1507 v_revision => l_item.revision);
1508 select alternate_lot_selection_method
1509 into l_alt_lot_selection_method
1510 from wip_parameters
1511 where organization_id = p_orgID ;
1512 l_flag := 0 ;
1513 if (l_alt_lot_selection_method in (wip_constants.recdate_full,wip_constants.recdate_exc)) then
1514 open c_receiptOrderedLots(v_itemID => l_item.inventory_item_id,
1515 v_supplySubinv => l_item.supply_subinventory,
1516 v_supplyLociD => l_item.supply_locator_id,
1517 v_revision => l_item.revision);
1518 elsif (l_alt_lot_selection_method in (wip_constants.expdate_full,wip_constants.expdate_exc)) then
1519 open c_expDateOrderedLots(v_itemID => l_item.inventory_item_id,
1520 v_supplySubinv => l_item.supply_subinventory,
1521 v_supplyLociD => l_item.supply_locator_id,
1522 v_revision => l_item.revision);
1523 end if ;
1524 /* End of addition for Wilson Greatbatch Enhancement */
1525 else
1526 l_errMsg := 'manual entry';
1527 raise fnd_api.g_exc_error; --manual selection.
1528 end if;
1529
1530 loop
1531 if(p_entryType in (wip_constants.recdate_full, wip_constants.recdate_exc)) then
1532 fetch c_receiptOrderedLots into l_lotNumber, l_expDate;
1533 exit when c_receiptOrderedLots%NOTFOUND;
1534 elsif(p_entryType in (wip_constants.expdate_full, wip_constants.expdate_exc)) then
1535 fetch c_expDateOrderedLots into l_lotNumber, l_expDate;
1536 exit when c_expDateOrderedLots%NOTFOUND;
1537 /* Added for Wilson Greatbatch Enhancement */
1538 elsif(p_entryType in (wip_constants.txnHistory_full, wip_constants.txnHistory_exc)) then
1539 if ( l_flag = 0 ) then
1540 fetch c_txnHistoryOrderedLots into l_lotNumber;
1541 end if ;
1542 if ( (c_txnHistoryOrderedLots%ROWCOUNT = 0) OR ( c_txnHistoryOrderedLots%NOTFOUND AND ( l_rmnQty <> 0)) ) then
1543 l_flag := 1;
1544 if(l_alt_lot_selection_method in (wip_constants.recdate_full, wip_constants.recdate_exc)) then
1545 fetch c_receiptOrderedLots into l_lotNumber, l_expDate;
1546 exit when c_receiptOrderedLots%NOTFOUND;
1547 elsif(l_alt_lot_selection_method in (wip_constants.expdate_full, wip_constants.expdate_exc)) then
1548 fetch c_expDateOrderedLots into l_lotNumber, l_expDate;
1549 exit when c_expDateOrderedLots%NOTFOUND;
1550 /*Fix for bug 4090078 */
1551 else
1552 exit when c_txnHistoryOrderedLots%NOTFOUND;
1553 end if; -- end if for l_alt_lot_selection
1554 else -- else condition if c_TxnHistoryordered fetches rows
1555 exit when c_txnHistoryOrderedLots%NOTFOUND;
1556 end if; -- end if for row count
1557 /* End of addition for Wilson Greatbatch Enhancement */
1558 end if; -- end if for p_entrytype
1559
1560 l_enabled := wip_utilities.is_status_applicable(p_trx_type_id => getTxnType(l_item.transaction_action_id),
1561 p_organization_id => p_orgID,
1562 p_inventory_item_id => l_item.inventory_item_id,
1563 p_sub_code => l_item.supply_subinventory,
1564 p_locator_id => l_item.supply_locator_id,
1565 p_lot_number => l_lotNumber,
1566 p_object_type => 'O');
1567 --if this lot is not enabled, skip it.
1568 if(l_enabled <> 'Y') then
1569 goto END_OF_LOOP;
1570 end if;
1571
1572 inv_quantity_tree_pvt.query_tree(p_api_version_number => 1.0,
1573 p_init_msg_lst => fnd_api.g_false,
1574 p_tree_id => x_treeID,
1575 p_revision => l_item.revision,
1576 p_lot_number => l_lotNumber,
1577 p_subinventory_code => l_item.supply_subinventory,
1578 p_locator_id => l_item.supply_locator_id,
1579 p_transfer_subinventory_code => null,
1580 p_cost_group_id => null,
1581 p_lpn_id => null,
1582 p_transfer_locator_id => null,
1583 x_return_status => x_returnStatus,
1584 x_msg_count => l_msgCount,
1585 x_msg_data => l_msgData,
1586 x_qoh => l_qtyOnHand,
1587 x_rqoh => l_rsvableQtyOnHand,
1588 x_qr => l_qtyRsved,
1589 x_qs => l_qtySuggested,
1590 x_att => l_qtyAvailToTxt,
1591 x_atr => l_qtyAvailToRsv);
1592
1593
1594 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
1595 l_errMsg := 'qty tree query failed';
1596 raise fnd_api.g_exc_unexpected_error;
1597 end if;
1598
1599 -- Check if Lot is already entered into MTLI and populate into l_lot_qty_selected
1600 -- If Lot Number is not found then l_lot_qty_selected will be populated as zero Qty.
1601
1602 /* Fix for Bug#4956543 */
1603
1604 lot_selected ( p_organization_id => p_orgID,
1605 p_inventory_item_id => l_item.inventory_item_id,
1606 p_sub_code => l_item.supply_subinventory,
1607 p_locator_id => l_item.supply_locator_id,
1608 p_lot_number => l_lotNumber,
1609 p_lot_qty_selected => l_lot_qty_selected,
1610 x_returnStatus => x_returnStatus ) ;
1611
1612 /* Begin Bug#4956543. l_qtyAvailToTxt is updated if Lot is already selected */
1613
1614 if ((l_qtyAvailToTxt > 0) and (l_qtyAvailToTxt - l_lot_qty_selected ) > 0) then
1615 if (l_lot_qty_selected > 0 ) then
1616 wip_logger.log ('Changing l_qtyAvailToTxt', l_returnStatus ) ;
1617 l_qtyAvailToTxt := l_qtyAvailToTxt - l_lot_qty_selected ;
1618 end if ;
1619
1620 /* End Bug#4956543 */
1621
1622 if (l_logLevel <= wip_constants.full_logging) then
1623 wip_logger.log('adding lot ' || l_lotNumber || 'w/qty ' || least(l_rmnQty, l_qtyAvailToTxt), l_returnStatus);
1624 wip_logger.log('qty avail to txt:' || l_qtyAvailToTxt, l_returnStatus);
1625 wip_logger.log('l_rmnQty:' || l_rmnQty, l_returnStatus);
1626 end if;
1627
1628 x_compLots.addLot(p_lotNumber => l_lotNumber,
1629 p_priQty => least(l_rmnQty, l_qtyAvailToTxt),
1630 p_attributes => null);
1631
1632
1633 inv_quantity_tree_pvt.update_quantities(p_api_version_number => 1.0,
1634 p_init_msg_lst => fnd_api.g_false,
1635 p_tree_id => x_treeID,
1636 p_revision => l_item.revision,
1637 p_lot_number => l_lotNumber,
1638 p_subinventory_code => l_item.supply_subinventory,
1639 p_locator_id => l_item.supply_locator_id,
1640 p_primary_quantity => -1 * least(l_rmnQty, l_qtyAvailToTxt),
1641 p_quantity_type => 1, --pending txn
1642 p_transfer_subinventory_code => null,
1643 p_cost_group_id => null,
1644 p_containerized => inv_quantity_tree_pvt.g_containerized_false,
1645 p_lpn_id => null,
1646 p_transfer_locator_id => null,
1647 x_return_status => x_returnStatus,
1648 x_msg_count => l_msgCount,
1649 x_msg_data => l_msgData,
1650 x_qoh => l_qtyOnHand2,
1651 x_rqoh => l_rsvableQtyOnHand2,
1652 x_qr => l_qtyRsved2,
1653 x_qs => l_qtySuggested2,
1654 x_att => l_qtyAvailToTxt2,
1655 x_atr => l_qtyAvailToRsv2);
1656 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
1657 l_errMsg := 'qty tree update failed';
1658 raise fnd_api.g_exc_unexpected_error;
1659 end if;
1660
1661 if (l_logLevel <= wip_constants.full_logging) then
1662 wip_logger.log('updating treeID' || x_treeID, l_returnStatus);
1663 wip_logger.log(' item=' || l_item.inventory_item_id, l_returnStatus);
1664 wip_logger.log(' lot=' || l_lotNumber, l_returnStatus);
1665 wip_logger.log(' qty=' || -1 * least(l_rmnQty, l_qtyAvailToTxt), l_returnStatus);
1666 end if;
1667 l_rmnQty := l_rmnQty - least(l_rmnQty, l_qtyAvailToTxt);
1668 if(l_rmnQty = 0) then
1669 exit;
1670 end if;
1671 end if;
1672
1673 <<END_OF_LOOP>>
1674 null;
1675 end loop;
1676
1677 if(c_receiptOrderedLots%ISOPEN) then
1678 close c_receiptOrderedLots;
1679 elsif(c_expDateOrderedLots%ISOPEN) then
1680 close c_expDateOrderedLots;
1681 end if;
1682 /* Added for Wilson Greatbatch Enhancement */
1683 if(c_TxnHistoryOrderedLots%ISOPEN) then
1684 close c_TxnHistoryOrderedLots;
1685 end if;
1686
1687 if(l_rmnQty > 0) then
1688 l_errMsg := 'could not derive all qty. ' || l_rmnQty || ' remaining.';
1689 raise fnd_api.g_exc_error;
1690 end if;
1691
1692 if (l_logLevel <= wip_constants.trace_logging) then
1693 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.deriveIssueLots',
1694 p_procReturnStatus => x_returnStatus,
1695 p_msg => 'procedure success',
1696 x_returnStatus => l_returnStatus); --discard logging return status
1697 end if;
1698
1699 exception
1700 when fnd_api.g_exc_error then
1701 x_returnStatus:= fnd_api.g_ret_sts_error; --let caller know item was not fully derived
1702 if (l_logLevel <= wip_constants.trace_logging) then
1703 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.deriveIssueLots',
1704 p_procReturnStatus => x_returnStatus,
1705 p_msg => l_errMsg,
1706 x_returnStatus => l_returnStatus); --discard logging return status
1707 end if;
1708 when fnd_api.g_exc_unexpected_error then
1709 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1710 if(c_receiptOrderedLots%ISOPEN) then
1711 close c_receiptOrderedLots;
1712 elsif(c_expDateOrderedLots%ISOPEN) then
1713 close c_expDateOrderedLots;
1714 end if;
1715 if (l_logLevel <= wip_constants.trace_logging) then
1716 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.deriveIssueLots',
1717 p_procReturnStatus => x_returnStatus,
1718 p_msg => 'error: ' || l_errMsg,
1719 x_returnStatus => l_returnStatus); --discard logging return status
1720 end if;
1721 when others then
1722 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1723 if(c_receiptOrderedLots%ISOPEN) then
1724 close c_receiptOrderedLots;
1725 elsif(c_expDateOrderedLots%ISOPEN) then
1726 close c_expDateOrderedLots;
1727 end if;
1728 fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_autoLotProc_priv',
1729 p_procedure_name => 'deriveIssueLots',
1730 p_error_text => SQLERRM);
1731 if (l_logLevel <= wip_constants.trace_logging) then
1732 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.deriveIssueLots',
1733 p_procReturnStatus => x_returnStatus,
1734 p_msg => 'error: ' || SQLERRM,
1735 x_returnStatus => l_returnStatus); --discard logging return status
1736 end if;
1737
1738 end deriveIssueLots;
1739
1740 procedure deriveSingleItem(p_orgID IN NUMBER,
1741 p_wipEntityID IN NUMBER, --only needed for returns and neg returns
1742 p_entryType IN NUMBER,
1743 p_treeMode IN NUMBER,
1744 p_treeSrcName IN VARCHAR2,
1745 x_treeID IN OUT NOCOPY NUMBER, --the qty tree id if one was built
1746 x_compLots IN OUT NOCOPY system.wip_lot_serial_obj_t,
1747 x_returnStatus OUT NOCOPY VARCHAR2) is
1748 l_lotControlCode NUMBER;
1749 l_serialControlCode NUMBER;
1750 l_serialTaggedCode NUMBER; -- Bug 9907143: added for serial tagging enhancement for SUN
1751 l_TxnTypeID NUMBER;
1752 l_errMsg VARCHAR2(80);
1753 l_params wip_logger.param_tbl_t;
1754 l_returnStatus VARCHAR2(1);
1755 l_item system.wip_component_obj_t;
1756 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
1757 begin
1758
1759 x_returnStatus := fnd_api.g_ret_sts_success;
1760 if (l_logLevel <= wip_constants.trace_logging) then
1761 l_params(1).paramName := 'p_orgID';
1762 l_params(1).paramValue := p_orgID;
1763 l_params(2).paramName := 'p_wipEntityID';
1764 l_params(2).paramValue := p_wipEntityID;
1765 l_params(3).paramName := 'p_treeMode';
1766 l_params(3).paramValue := p_treeMode;
1767 l_params(4).paramName := 'p_treeSrcName';
1768 l_params(4).paramValue := p_treeSrcName;
1769 l_params(5).paramName := 'x_treeID';
1770 l_params(5).paramValue := x_treeID;
1771
1772 wip_logger.entryPoint(p_procName => 'wip_autoLotProc_priv.deriveSingleItem',
1773 p_params => l_params,
1774 x_returnStatus => x_returnStatus);
1775 end if;
1776
1777 savepoint wipbflpb40;
1778
1779 if(not x_compLots.getCurrentItem(l_item)) then
1780 l_errMsg := 'unable to get current item';
1781 raise fnd_api.g_exc_unexpected_error;
1782 end if;
1783
1784 l_TxnTypeID := getTxnType(l_item.transaction_action_id);
1785 select lot_control_code, serial_number_control_code,
1786 inv_cache.get_serial_tagged(p_orgID, -- Bug 9907143: added for serial tagging enhancement for SUN
1787 l_item.inventory_item_id,
1788 l_TxnTypeID) serial_tagged_code
1789 into l_lotControlCode, l_serialControlCode, l_serialTaggedCode
1790 from mtl_system_items
1791 where inventory_item_id = l_item.inventory_item_id
1792 and organization_id = p_orgID;
1793
1794 --if under serial control, we can not derive lots
1795 if(l_serialControlCode in (wip_constants.full_sn, wip_constants.dyn_rcv_sn)) then
1796 checkSerialQuantity(p_itemID => l_item.inventory_item_id,
1797 p_itemName => l_item.item_name,
1798 p_orgID => p_orgID,
1799 p_qty => abs(l_item.primary_quantity),
1800 p_txnActionID => l_item.transaction_action_id,
1801 p_serControlCode => l_serialControlCode,
1802 x_returnStatus => x_returnStatus);
1803 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
1804 l_errMsg := 'not enough serial #s';
1805 raise fnd_api.g_exc_unexpected_error;
1806 else
1807 l_errMsg := 'item under serial control';
1808 raise fnd_api.g_exc_error;
1809 end if;
1810 elsif (l_serialTaggedCode = WIP_CONSTANTS.SER_TAGGED) then -- Bug 9907143: added for serial tagging enhancement for SUN
1811 l_errMsg := 'item under serial control';
1812 raise fnd_api.g_exc_error;
1813 end if;
1814
1815 --if uncontrolled, return success (no derivation necessary)
1816 if(l_lotControlCode = wip_constants.no_lot) then
1817 x_returnStatus := fnd_api.g_ret_sts_success;
1818 if (l_logLevel <= wip_constants.trace_logging) then
1819 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.deriveSingleItem',
1820 p_procReturnStatus => x_returnStatus,
1821 p_msg => 'procedure success (no derivation necessary)',
1822 x_returnStatus => l_returnStatus); --discard logging return status
1823 end if;
1824 return;
1825 end if;
1826
1827
1828 --else under lot control only
1829
1830 --cannot derive lots for any other statuses besides these 3
1831 if(l_item.transaction_action_id not in (wip_constants.isscomp_action,
1832 wip_constants.retcomp_action,
1833 wip_constants.retnegc_action)) then
1834 l_errMsg := 'non-derivable txn action:' || l_item.transaction_action_id;
1835 raise fnd_api.g_exc_error;
1836 end if;
1837
1838
1839 --for issues, check out all the lots in the specified location
1840 if(l_item.transaction_action_id = wip_constants.isscomp_action) then
1841 deriveIssueLots(p_orgID => p_orgID,
1842 p_wipEntityID => p_wipEntityID,
1843 p_entryType => p_entryType,
1844 p_treeMode => p_treeMode,
1845 p_treeSrcName => p_treeSrcName,
1846 x_treeID => x_treeID,
1847 x_compLots => x_compLots,
1848 x_returnStatus => x_returnStatus);
1849
1850 --for returns, look at the past issue transactions and try to return those lots
1851 else
1852 deriveTxnLots(p_orgID => p_orgID,
1853 p_wipEntityID => p_wipEntityID,
1854 p_txnActionID => l_item.transaction_action_id,
1855 p_entryType => p_entryType,
1856 x_compLots => x_compLots,
1857 x_returnStatus => x_returnStatus);
1858 end if;
1859 if(x_returnStatus = fnd_api.g_ret_sts_unexp_error) then
1860 raise fnd_api.g_exc_unexpected_error;
1861 end if;
1862
1863 if (l_logLevel <= wip_constants.trace_logging) then
1864 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.deriveSingleItem',
1865 p_procReturnStatus => x_returnStatus,
1866 p_msg => 'procedure success',
1867 x_returnStatus => l_returnStatus); --discard logging return status
1868 end if;
1869
1870 exception
1871 when fnd_api.g_exc_error then
1872 x_returnStatus := fnd_api.g_ret_sts_error;
1873 if (l_logLevel <= wip_constants.trace_logging) then
1874 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.deriveSingleItem',
1875 p_procReturnStatus => x_returnStatus,
1876 p_msg => 'error' || l_errMsg,
1877 x_returnStatus => l_returnStatus); --discard logging return status
1878 end if;
1879 rollback to wipbflpb40;
1880 when fnd_api.g_exc_unexpected_error then
1881 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1882 if (l_logLevel <= wip_constants.trace_logging) then
1883 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.deriveSingleItem',
1884 p_procReturnStatus => x_returnStatus,
1885 p_msg => 'unexp error raised:',
1886 x_returnStatus => l_returnStatus); --discard logging return status
1887 end if;
1888 rollback to wipbflpb40;
1889 when others then
1890 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
1891 fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_autoLotProc_priv',
1892 p_procedure_name => 'deriveSingleItem',
1893 p_error_text => SQLERRM);
1894 if (l_logLevel <= wip_constants.trace_logging) then
1895 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.deriveSingleItem',
1896 p_procReturnStatus => x_returnStatus,
1897 p_msg => 'unexp error:' || SQLERRM,
1898 x_returnStatus => l_returnStatus); --discard logging return status
1899 end if;
1900 rollback to wipbflpb40;
1901 end deriveSingleItem;
1902
1903
1904 function findTxnAction(p_isForwardTxn in VARCHAR2,
1905 p_qty in NUMBER) return number is
1906 begin
1907 if(fnd_api.to_boolean(p_isForwardTxn)) then
1908 if(p_qty > 0) then
1909 return wip_constants.isscomp_action;
1910 else
1911 return wip_constants.issnegc_action;
1912 end if;
1913 else
1914 if(p_qty < 0) then
1915 return wip_constants.retcomp_action;
1916 else
1917 return wip_constants.retnegc_action;
1918 end if;
1919 end if;
1920 end findTxnAction;
1921
1922 procedure checkSerialQuantity(p_itemID IN NUMBER,
1923 p_itemName IN VARCHAR2,
1924 p_orgID IN NUMBER,
1925 p_qty IN NUMBER,
1926 p_txnActionID IN NUMBER,
1927 p_serControlCode IN NUMBER,
1928 x_returnStatus OUT NOCOPY VARCHAR2) IS
1929 l_serCount NUMBER;
1930 l_params wip_logger.param_tbl_t;
1931 l_returnStatus VARCHAR2(1);
1932 l_errMsg VARCHAR2(80);
1933 l_txnTypeID NUMBER;
1934 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
1935 begin
1936
1937 x_returnStatus := fnd_api.g_ret_sts_success;
1938 if (l_logLevel <= wip_constants.trace_logging) then
1939 l_params(1).paramName := 'p_itemID';
1940 l_params(1).paramValue := p_itemID;
1941 l_params(2).paramName := 'p_orgID';
1942 l_params(2).paramValue := p_orgID;
1943 l_params(3).paramName := 'p_qty';
1944 l_params(3).paramValue := p_qty;
1945 l_params(4).paramName := 'p_txnActionID';
1946 l_params(4).paramValue := p_txnActionID;
1947 wip_logger.entryPoint(p_procName => 'wip_autoLotProc_priv.checkSerialQuantity',
1948 p_params => l_params,
1949 x_returnStatus => x_returnStatus);
1950 end if;
1951
1952 if (l_logLevel <= wip_constants.full_logging) then
1953 wip_logger.log('qty:' || p_qty, l_returnStatus);
1954 wip_logger.log('round(qty)' || round(p_qty), l_returnStatus);
1955 end if;
1956 if(p_qty <> round(p_qty)) then --serial requirements must be whole numbers
1957 l_errMsg := 'serial requirement not a whole #';
1958 fnd_message.set_name('WIP', 'COMP_INVALID_SER_QTY');
1959 fnd_message.set_token('ITEM', p_itemName);
1960 fnd_msg_pub.add;
1961 raise fnd_api.g_exc_unexpected_error;
1962 end if;
1963
1964 l_txnTypeID := getTxnType(p_txnActionID);
1965
1966 if(p_txnActionID in (wip_constants.isscomp_action, wip_constants.retnegc_action)) then
1967 select nvl(max(count(*)), 0)
1968 into l_serCount
1969 from mtl_serial_numbers
1970 where current_organization_id = p_orgID
1971 and inventory_item_id = p_itemID
1972 and current_status = 3
1973 and (group_mark_id = -1 OR group_mark_id is null)
1974 and lpn_id is null
1975 and (wip_utilities.is_status_applicable(/*p_trx_status_enabled => */ null,
1976 /*p_trx_type_id => */ l_txnTypeID,
1977 /*p_lot_status_enabled => */ null,
1978 /*p_serial_status_enabled => */ null,
1979 /*p_organization_id => */ current_organization_id,
1980 /*p_inventory_item_id => */ inventory_item_id,
1981 /*p_sub_code => */ current_subinventory_code,
1982 /*p_locator_id => */ current_locator_id,
1983 /*p_lot_number => */ lot_number,
1984 /*p_serial_number => */ serial_number,
1985 /*p_object_type => */ 'S') = 'Y')
1986 group by current_subinventory_code, current_locator_id, revision;
1987
1988 elsif(p_txnActionID = wip_constants.retcomp_action) then
1989 select nvl(max(count(*)), 0)
1990 into l_serCount
1991 from mtl_serial_numbers
1992 where current_organization_id = p_orgID
1993 and inventory_item_id = p_itemID
1994 and current_status = 4
1995 and (group_mark_id = -1 OR group_mark_id is null)
1996 and (wip_utilities.is_status_applicable(/*p_trx_status_enabled => */ null,
1997 /*p_trx_type_id => */ l_txnTypeID,
1998 /*p_lot_status_enabled => */ null,
1999 /*p_serial_status_enabled => */ null,
2000 /*p_organization_id => */ current_organization_id,
2001 /*p_inventory_item_id => */ inventory_item_id,
2002 /*p_sub_code => */ current_subinventory_code,
2003 /*p_locator_id => */ current_locator_id,
2004 /*p_lot_number => */ lot_number,
2005 /*p_serial_number => */ serial_number,
2006 /*p_object_type => */ 'S') = 'Y')
2007 group by revision;
2008
2009 elsif(p_txnActionID = wip_constants.issnegc_action) then
2010 if(p_serControlCode = wip_constants.dyn_rcv_sn) then
2011 x_returnStatus := fnd_api.g_ret_sts_success;
2012 if (l_logLevel <= wip_constants.trace_logging) then
2013 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.checkSerialQuantity',
2014 p_procReturnStatus => x_returnStatus,
2015 p_msg => 'procedure success (neg issue and serial is dyn at recpt)',
2016 x_returnStatus => l_returnStatus);
2017 end if;
2018 return;
2019 else
2020 select count(*)
2021 into l_serCount
2022 from mtl_serial_numbers
2023 where current_organization_id = p_orgID
2024 and inventory_item_id = p_itemID
2025 and current_status in (1, 6)
2026 and (group_mark_id = -1 OR group_mark_id is null)
2027 and (wip_utilities.is_status_applicable(/*p_trx_status_enabled => */ null,
2028 /*p_trx_type_id => */ l_txnTypeID,
2029 /*p_lot_status_enabled => */ null,
2030 /*p_serial_status_enabled => */ null,
2031 /*p_organization_id => */ current_organization_id,
2032 /*p_inventory_item_id => */ inventory_item_id,
2033 /*p_sub_code => */ current_subinventory_code,
2034 /*p_locator_id => */ current_locator_id,
2035 /*p_lot_number => */ lot_number,
2036 /*p_serial_number => */ serial_number,
2037 /*p_object_type => */ 'S') = 'Y');
2038
2039 end if;
2040 end if;
2041 if (l_logLevel <= wip_constants.full_logging) then
2042 wip_logger.log('serial count is ' || l_serCount, l_returnStatus);
2043 end if;
2044 if(l_serCount < p_qty) then
2045 fnd_message.set_name('WIP', 'NO_COMP_SERIAL_NUMBERS');
2046 fnd_message.set_token('ITEM', p_itemName);
2047 fnd_msg_pub.add;
2048 l_errMsg := 'error: not enough serials available';
2049 raise fnd_api.g_exc_unexpected_error;
2050 else
2051 x_returnStatus := fnd_api.g_ret_sts_success;
2052 end if;
2053 if (l_logLevel <= wip_constants.trace_logging) then
2054 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.checkSerialQuantity',
2055 p_procReturnStatus => x_returnStatus,
2056 p_msg => 'procedure success',
2057 x_returnStatus => l_returnStatus);
2058 end if;
2059 exception
2060 when fnd_api.g_exc_unexpected_error then
2061 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2062 if (l_logLevel <= wip_constants.trace_logging) then
2063 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.checkSerialQuantity',
2064 p_procReturnStatus => x_returnStatus,
2065 p_msg => l_errMsg,
2066 x_returnStatus => l_returnStatus);
2067 end if;
2068 when others then
2069 fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_autoLotProc_priv',
2070 p_procedure_name => 'checkSerialQuantity',
2071 p_error_text => SQLERRM);
2072 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2073 if (l_logLevel <= wip_constants.trace_logging) then
2074 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.checkSerialQuantity',
2075 p_procReturnStatus => x_returnStatus,
2076 p_msg => 'unexp error ' || SQLERRM,
2077 x_returnStatus => l_returnStatus);
2078 end if;
2079 end checkSerialQuantity;
2080
2081 procedure checkSerial(p_txnTmpID IN NUMBER,
2082 p_txnIntID IN NUMBER,
2083 p_itemID IN NUMBER,
2084 p_itemName IN VARCHAR2,
2085 p_orgID IN NUMBER,
2086 p_revision IN VARCHAR2,
2087 p_subinv IN VARCHAR2,
2088 p_locID IN NUMBER,
2089 p_qty IN NUMBER,
2090 p_txnActionID IN NUMBER,
2091 p_serControlCode IN NUMBER,
2092 x_serialReturnStatus OUT NOCOPY VARCHAR2,
2093 x_returnStatus OUT NOCOPY VARCHAR2) IS
2094 l_serQty NUMBER;
2095 l_totalQty NUMBER := 0;
2096 /* ER 4378835: Increased length of lot variables from 30 to 80 to support OPM Lot-model changes */
2097 l_prefix VARCHAR2(80);
2098 l_fmNumber VARCHAR2(80);
2099 l_toNumber VARCHAR2(80);
2100 l_errCode NUMBER;
2101 l_errMsg VARCHAR2(80);
2102 l_returnStatus VARCHAR2(1);
2103 l_params wip_logger.param_tbl_t;
2104 l_fmSerial VARCHAR2(30);
2105 l_toSerial VARCHAR2(30);
2106 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
2107 cursor c_tmpSerials is
2108 select fm_serial_number fmSerial,
2109 to_serial_number toSerial
2110 from mtl_serial_numbers_temp
2111 where transaction_temp_id = p_txnTmpID;
2112
2113 cursor c_intSerials is
2114 select fm_serial_number fmSerial,
2115 to_serial_number toSerial
2116 from mtl_serial_numbers_interface
2117 where transaction_interface_id = p_txnIntID;
2118 begin
2119 x_returnStatus := fnd_api.g_ret_sts_success;
2120 if (l_logLevel <= wip_constants.trace_logging) then
2121 l_params(1).paramName := 'p_txnTmpID';
2122 l_params(1).paramValue := p_txnTmpID;
2123 l_params(2).paramName := 'p_txnIntID';
2124 l_params(2).paramValue := p_txnIntID;
2125 l_params(3).paramName := 'p_itemID';
2126 l_params(3).paramValue := p_itemID;
2127 l_params(4).paramName := 'p_itemName';
2128 l_params(4).paramValue := p_itemName;
2129 l_params(5).paramName := 'p_orgID';
2130 l_params(5).paramValue := p_orgID;
2131 l_params(6).paramName := 'p_revision';
2132 l_params(6).paramValue := p_revision;
2133 l_params(7).paramName := 'p_subinv';
2134 l_params(7).paramValue := p_subinv;
2135 l_params(8).paramName := 'p_locID';
2136 l_params(8).paramValue := p_locID;
2137 l_params(9).paramName := 'p_qty';
2138 l_params(9).paramValue := p_qty;
2139 l_params(10).paramName := 'p_txnActionID';
2140 l_params(10).paramValue := p_txnActionID;
2141 l_params(11).paramName := 'p_serControlCode';
2142 l_params(11).paramValue := p_serControlCode;
2143 wip_logger.entryPoint(p_procName => 'wip_autoLotProc_priv.checkSerial',
2144 p_params => l_params,
2145 x_returnStatus => x_returnStatus);
2146 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
2147 raise fnd_api.g_exc_unexpected_error;
2148 end if;
2149 end if;
2150
2151 if(p_txnTmpID is not null) then
2152 open c_tmpSerials;
2153 else
2154 open c_intSerials;
2155 end if;
2156
2157 loop
2158 if(p_txnTmpID is not null) then
2159 fetch c_tmpSerials into l_fmSerial, l_toSerial;
2160 exit when c_tmpSerials%NOTFOUND;
2161 else
2162 fetch c_intSerials into l_fmSerial, l_toSerial;
2163 exit when c_intSerials%NOTFOUND;
2164 end if;
2165 if(MTL_Serial_Check.inv_serial_info(p_from_serial_number => l_fmSerial,
2166 p_to_serial_number => l_toSerial,
2167 x_prefix => l_prefix,
2168 x_quantity => l_serQty,
2169 x_from_number => l_fmNumber,
2170 x_to_number => l_toNumber,
2171 x_errorcode => l_errCode)) then
2172 l_totalQty := l_totalQty + l_serQty;
2173 else
2174 l_errMsg := 'mtl_serial_check.inv_serial_info returned false';
2175 raise fnd_api.g_exc_unexpected_error;
2176 end if;
2177 end loop;
2178
2179 if(c_tmpSerials%ISOPEN) then
2180 close c_tmpSerials;
2181 elsif(c_intSerials%ISOPEN) then
2182 close c_intSerials;
2183 end if;
2184
2185 if(l_totalQty <> abs(p_qty)) then
2186 checkSerialQuantity(p_itemID => p_itemID,
2187 p_itemName => p_itemName,
2188 p_orgID => p_orgID,
2189 p_qty => abs(p_qty),
2190 p_txnActionID => p_txnActionID,
2191 p_serControlCode => p_serControlCode,
2192 x_returnStatus => x_serialReturnStatus);
2193 if(x_serialReturnStatus = fnd_api.g_ret_sts_success) then --enough serial numbers exist to complete this transaction
2194 raise fnd_api.g_exc_error;
2195 else
2196 raise fnd_api.g_exc_unexpected_error;
2197 end if;
2198
2199 end if;
2200
2201 if (l_logLevel <= wip_constants.trace_logging) then
2202 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.checkSerial',
2203 p_procReturnStatus => x_returnStatus,
2204 p_msg => 'procedure success',
2205 x_returnStatus => l_returnStatus);
2206 end if;
2207 exception
2208 when fnd_api.g_exc_error then
2209 x_serialReturnStatus := fnd_api.g_ret_sts_error;
2210 x_returnStatus := fnd_api.g_ret_sts_success;
2211 if (l_logLevel <= wip_constants.trace_logging) then
2212 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.checkSerial',
2213 p_procReturnStatus => x_returnStatus,
2214 p_msg => 'insufficient serial qty. only found ' || l_totalQty,
2215 x_returnStatus => l_returnStatus);
2216 end if;
2217 when fnd_api.g_exc_unexpected_error then
2218 x_serialReturnStatus := fnd_api.g_ret_sts_unexp_error;
2219 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2220 if(c_tmpSerials%ISOPEN) then
2221 close c_tmpSerials;
2222 elsif(c_intSerials%ISOPEN) then
2223 close c_intSerials;
2224 end if;
2225 if (l_logLevel <= wip_constants.trace_logging) then
2226 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.checkSerial',
2227 p_procReturnStatus => x_returnStatus,
2228 p_msg => l_errMsg,
2229 x_returnStatus => l_returnStatus);
2230 end if;
2231 when others then
2232 x_serialReturnStatus := fnd_api.g_ret_sts_unexp_error;
2233 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2234 if(c_tmpSerials%ISOPEN) then
2235 close c_tmpSerials;
2236 elsif(c_intSerials%ISOPEN) then
2237 close c_intSerials;
2238 end if;
2239 fnd_msg_pub.add_exc_msg(p_pkg_name => 'wip_autoLotProc_priv',
2240 p_procedure_name => 'checkSerial',
2241 p_error_text => SQLERRM);
2242 if (l_logLevel <= wip_constants.trace_logging) then
2243 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.checkSerial',
2244 p_procReturnStatus => x_returnStatus,
2245 p_msg => 'unexp error ' || SQLERRM,
2246 x_returnStatus => l_returnStatus);
2247 end if;
2248 end checkSerial;
2249
2250 function worstReturnStatus(p_status1 VARCHAR2, p_status2 VARCHAR2) return VARCHAR2 is
2251 begin
2252 if(p_status1 = fnd_api.g_ret_sts_unexp_error or
2253 p_status2 = fnd_api.g_ret_sts_unexp_error) then
2254 return fnd_api.g_ret_sts_unexp_error;
2255 elsif(p_status1 = fnd_api.g_ret_sts_error or
2256 p_status2 = fnd_api.g_ret_sts_error) then
2257 return fnd_api.g_ret_sts_error;
2258 else
2259 return fnd_api.g_ret_sts_success;
2260 end if;
2261 end worstReturnStatus;
2262
2263 function getTxnType(p_txnActionID IN NUMBER) return NUMBER is
2264 begin
2265 if(p_txnActionID = wip_constants.isscomp_action) then
2266 return wip_constants.isscomp_type;
2267 elsif(p_txnActionID = wip_constants.retnegc_action) then
2268 return wip_constants.retnegc_type;
2269 elsif(p_txnActionID = wip_constants.retcomp_action) then
2270 return wip_constants.retcomp_type;
2271 elsif(p_txnActionID = wip_constants.issnegc_action) then
2272 return wip_constants.issnegc_type;
2273 end if;
2274 return -1; --this procedure only works for component txn types
2275 end getTxnType;
2276
2277
2278 /* Fix for Bug#4737216 . Added following procedure */
2279 procedure lot_selected (
2280 p_organization_id NUMBER,
2281 p_inventory_item_id NUMBER,
2282 p_sub_code VARCHAR2,
2283 p_locator_id NUMBER,
2284 p_lot_number VARCHAR2,
2285 p_lot_qty_selected OUT NOCOPY NUMBER,
2286 x_returnStatus OUT NOCOPY VARCHAR2) is
2287 l_qty NUMBER ;
2288 l_returnStatus varchar2(1) ;
2289 l_params wip_logger.param_tbl_t;
2290 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
2291 begin
2292
2293 l_qty := 0 ;
2294
2295 x_returnStatus := fnd_api.g_ret_sts_success;
2296
2297 if (l_logLevel <= wip_constants.trace_logging) then
2298 l_params(1).paramName := 'p_organization_id';
2299 l_params(1).paramValue := p_organization_id;
2300 l_params(2).paramName := 'p_inventory_item_id';
2301 l_params(2).paramValue := p_inventory_item_id;
2302 l_params(3).paramName := 'p_sub_code';
2303 l_params(3).paramValue := p_sub_code;
2304 l_params(4).paramName := 'p_locator_id';
2305 l_params(4).paramValue := p_locator_id;
2306 l_params(5).paramName := 'p_lot_number';
2307 l_params(5).paramValue := p_lot_number;
2308
2309 wip_logger.entryPoint(p_procName => 'wip_autoLotProc_priv.lot_selected',
2310 p_params => l_params,
2311 x_returnStatus => x_returnStatus);
2312 end if ;
2313
2314 begin
2315
2316 select sum(abs(nvl(transaction_quantity, 0)))
2317 into l_qty
2318 from mtl_transaction_lots_interface
2319 where transaction_interface_id in
2320 (select transaction_interface_id
2321 from mtl_transactions_interface
2322 where inventory_item_id = p_inventory_item_id
2323 and organization_id = p_organization_id
2324 and subinventory_code = p_sub_code
2325 and nvl(locator_id, -1) = nvl(p_locator_id, -1))
2326 and lot_number = p_lot_number ;
2327
2328 exception
2329 when others then
2330 wip_logger.log( 'In exception Lots entered **** ' , l_returnStatus) ;
2331 l_qty := 0 ;
2332 end ;
2333
2334 p_lot_qty_selected := nvl(l_qty, 0) ;
2335
2336 wip_logger.log( 'Lot Qty Selected ' || p_lot_qty_selected || ' for Lot ' || p_lot_number, l_returnStatus) ;
2337
2338 if (l_logLevel <= wip_constants.trace_logging) then
2339 wip_logger.exitPoint(p_procName => 'wip_autoLotProc_priv.lot_selected',
2340 p_procReturnStatus => x_returnStatus,
2341 p_msg => 'procedure success',
2342 x_returnStatus => l_returnStatus); --discard logging return status
2343 end if;
2344
2345 end lot_selected ;
2346
2347 PROCEDURE deriveSingleItemFromMOG
2348 (p_parentObjID IN NUMBER,
2349 p_orgID IN NUMBER,
2350 p_item IN system.wip_component_obj_t,
2351 x_compLots IN OUT NOCOPY system.wip_lot_serial_obj_t,
2352 x_returnStatus OUT NOCOPY VARCHAR2) IS
2353
2354 CURSOR c_lotComp IS
2355
2356 SELECT mtln.lot_number lot,
2357 mtln.primary_quantity * -1 lot_qty
2358 FROM mtl_object_genealogy mog,
2359 mtl_material_transactions mmt,
2360 mtl_transaction_lot_numbers mtln,
2361 mtl_lot_numbers mln
2362 WHERE mog.object_id = mln.gen_object_id
2363 AND mog.end_date_active IS NULL
2364 AND mog.parent_object_id = p_parentObjID
2365 AND mtln.inventory_item_id = p_item.inventory_item_id
2366 AND mtln.organization_id = p_orgID
2367 AND mtln.organization_id = mln.organization_id
2368 AND mtln.inventory_item_id = mln.inventory_item_id
2369 AND mtln.lot_number = mln.lot_number
2370 AND nvl(mln.expiration_date, sysdate + 1) > sysdate
2371 AND mmt.transaction_id = mog.origin_txn_id
2372 AND mmt.transaction_id = mtln.transaction_id
2373 AND mmt.transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
2374 WIP_CONSTANTS.RETCOMP_ACTION)
2375 AND mmt.operation_seq_num = p_item.operation_seq_num;
2376
2377 l_derivedQty NUMBER := 0;
2378 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
2379 l_errMsg VARCHAR2(240);
2380 l_returnStatus VARCHAR2(1);
2381 l_lotComp c_lotComp%ROWTYPE;
2382 l_params wip_logger.param_tbl_t;
2383
2384 BEGIN
2385 -- Don't need to check the return status because already check in
2386 -- deriveSerial()
2387
2388 IF (l_logLevel <= wip_constants.trace_logging) THEN
2389 l_params(1).paramName := 'p_parentObjID';
2390 l_params(1).paramValue := p_parentObjID;
2391 l_params(2).paramName := 'p_orgID';
2392 l_params(2).paramValue := p_orgID;
2393 l_params(3).paramName := 'p_item.inventory_item_id';
2394 l_params(3).paramValue := p_item.inventory_item_id;
2395 l_params(4).paramName := 'p_item.operation_seq_num';
2396 l_params(4).paramValue := p_item.operation_seq_num;
2397 l_params(5).paramName := 'p_item.supply_subinventory';
2398 l_params(5).paramValue := p_item.supply_subinventory;
2399 l_params(6).paramName := 'p_item.supply_locator_id';
2400 l_params(6).paramValue := p_item.supply_locator_id;
2401 l_params(7).paramName := 'p_item.revision';
2402 l_params(7).paramValue := p_item.revision;
2403 l_params(8).paramName := 'p_item.primary_quantity';
2404 l_params(8).paramValue := p_item.primary_quantity;
2405 l_params(9).paramName := 'p_item.transaction_action_id';
2406 l_params(9).paramValue := p_item.transaction_action_id;
2407 wip_logger.entryPoint(
2408 p_procName => 'wip_autoLotProc_priv.deriveSingleItemFromMOG',
2409 p_params => l_params,
2410 x_returnStatus => x_returnStatus);
2411 END IF;
2412 -- Under lot control only
2413 FOR l_lotComp IN c_lotComp LOOP
2414
2415 x_compLots.addLot(p_lotNumber => l_lotComp.lot,
2416 p_priQty => l_lotComp.lot_qty,
2417 p_attributes => null);
2418
2419 l_derivedQty := l_derivedQty + l_lotComp.lot_qty;
2420
2421 IF (l_logLevel <= wip_constants.full_logging) THEN
2422 wip_logger.log('Added Lot : ' || l_lotComp.lot, l_returnStatus);
2423 wip_logger.log('Added Lot Qty : '|| l_lotComp.lot_qty, l_returnStatus);
2424 END IF;
2425 END LOOP; -- l_lotComp IN c_lotComp
2426 -- Check whether derived quantity equal to backflush quantity or not
2427 -- If not, error out.
2428 IF(p_item.lot_control_code = WIP_CONSTANTS.LOT AND
2429 p_item.primary_quantity <> l_derivedQty * -1) THEN
2430 wip_logger.log('item : ' || p_item.item_name, l_returnStatus);
2431 wip_logger.log('primary_quantity : ' || p_item.primary_quantity,
2432 l_returnStatus);
2433 wip_logger.log('derived_quantity : ' || l_derivedQty, l_returnStatus);
2434 l_errMsg := 'return quantity missmatch';
2435 raise fnd_api.g_exc_unexpected_error;
2436 END IF;
2437
2438 IF (l_logLevel <= wip_constants.trace_logging) THEN
2439 wip_logger.exitPoint(
2440 p_procName => 'wip_autoLotProc_priv.deriveSingleItemFromMOG',
2441 p_procReturnStatus => x_returnStatus,
2442 p_msg => 'procedure success',
2443 x_returnStatus => l_returnStatus); --discard return status
2444 END IF;
2445
2446 EXCEPTION
2447 WHEN fnd_api.g_exc_unexpected_error THEN
2448 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2449 IF (l_logLevel <= wip_constants.trace_logging) THEN
2450 wip_logger.exitPoint(
2451 p_procName => 'wip_autoLotProc_priv.deriveSingleItemFromMOG',
2452 p_procReturnStatus => x_returnStatus,
2453 p_msg => l_errMsg,
2454 x_returnStatus => l_returnStatus); --discard return status
2455 END IF;
2456
2457 fnd_message.set_name('WIP', 'WIP_RET_QTY_MISSMATCH');
2458 fnd_message.set_token('ENTITY1', p_item.item_name);
2459 fnd_msg_pub.add;
2460
2461 WHEN others THEN
2462 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2463 IF (l_logLevel <= wip_constants.trace_logging) THEN
2464 wip_logger.exitPoint(
2465 p_procName => 'wip_autoLotProc_priv.deriveSingleItemFromMOG',
2466 p_procReturnStatus => x_returnStatus,
2467 p_msg => 'unexp error:' || SQLERRM,
2468 x_returnStatus => l_returnStatus); --discard return status
2469 END IF;
2470 END deriveSingleItemFromMOG;
2471
2472 PROCEDURE setItemRevision
2473 (p_parentObjID IN NUMBER,
2474 p_orgID IN NUMBER,
2475 p_item IN system.wip_component_obj_t,
2476 x_compLots IN OUT NOCOPY system.wip_lot_serial_obj_t,
2477 x_returnStatus OUT NOCOPY VARCHAR2) IS
2478
2479 CURSOR c_revisionComp IS
2480
2481 SELECT mmt.revision revision
2482 FROM mtl_object_genealogy mog,
2483 mtl_material_transactions mmt,
2484 mtl_transaction_lot_numbers mtln,
2485 mtl_lot_numbers mln
2486 WHERE mog.object_id = mln.gen_object_id
2487 AND mog.end_date_active IS NULL
2488 AND mog.parent_object_id = p_parentObjID
2489 AND mtln.inventory_item_id = p_item.inventory_item_id
2490 AND mtln.organization_id = p_orgID
2491 AND mtln.organization_id = mln.organization_id
2492 AND mtln.inventory_item_id = mln.inventory_item_id
2493 AND mtln.lot_number = mln.lot_number
2494 AND nvl(mln.expiration_date, sysdate + 1) > sysdate
2495 AND mmt.transaction_id = mog.origin_txn_id
2496 AND mmt.transaction_id = mtln.transaction_id
2497 AND mmt.transaction_action_id IN (WIP_CONSTANTS.ISSCOMP_ACTION,
2498 WIP_CONSTANTS.RETCOMP_ACTION)
2499 AND mmt.operation_seq_num = p_item.operation_seq_num;
2500
2501 l_errMsg VARCHAR2(240);
2502 l_returnStatus VARCHAR2(1);
2503 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
2504 l_params wip_logger.param_tbl_t;
2505 l_revisionComp c_revisionComp%ROWTYPE;
2506 BEGIN
2507
2508 IF (l_logLevel <= wip_constants.trace_logging) THEN
2509 l_params(1).paramName := 'p_parentObjID';
2510 l_params(1).paramValue := p_parentObjID;
2511 l_params(2).paramName := 'p_orgID';
2512 l_params(2).paramValue := p_orgID;
2513 l_params(3).paramName := 'p_item.inventory_item_id';
2514 l_params(3).paramValue := p_item.inventory_item_id;
2515 l_params(4).paramName := 'p_item.operation_seq_num';
2516 l_params(4).paramValue := p_item.operation_seq_num;
2517 l_params(5).paramName := 'p_item.supply_subinventory';
2518 l_params(5).paramValue := p_item.supply_subinventory;
2519 l_params(6).paramName := 'p_item.supply_locator_id';
2520 l_params(6).paramValue := p_item.supply_locator_id;
2521 l_params(7).paramName := 'p_item.revision';
2522 l_params(7).paramValue := p_item.revision;
2523 l_params(8).paramName := 'p_item.primary_quantity';
2524 l_params(8).paramValue := p_item.primary_quantity;
2525 l_params(9).paramName := 'p_item.transaction_action_id';
2526 l_params(9).paramValue := p_item.transaction_action_id;
2527 wip_logger.entryPoint(
2528 p_procName => 'wip_autoLotProc_priv.setItemRevision',
2529 p_params => l_params,
2530 x_returnStatus => x_returnStatus);
2531 END IF;
2532
2533 OPEN c_revisionComp;
2534 -- Since revision is at the item level, we can just get the revision of
2535 -- the first record.
2536 FETCH c_revisionComp INTO l_revisionComp;
2537
2538 IF(c_revisionComp%FOUND AND
2539 l_revisionComp.revision IS NOT NULL) THEN
2540 x_compLots.setRevision(p_revision => l_revisionComp.revision);
2541 END IF;
2542
2543 IF (l_logLevel <= wip_constants.trace_logging) THEN
2544 wip_logger.exitPoint(
2545 p_procName => 'wip_autoLotProc_priv.setItemRevision',
2546 p_procReturnStatus => x_returnStatus,
2547 p_msg => 'procedure success',
2548 x_returnStatus => l_returnStatus); --discard return status
2549 END IF;
2550 CLOSE c_revisionComp;
2551 EXCEPTION
2552 WHEN others THEN
2553 IF(c_revisionComp%ISOPEN) THEN
2554 CLOSE c_revisionComp;
2555 END IF;
2556
2557 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2558 IF (l_logLevel <= wip_constants.trace_logging) THEN
2559 wip_logger.exitPoint(
2560 p_procName => 'wip_autoLotProc_priv.setItemRevision',
2561 p_procReturnStatus => x_returnStatus,
2562 p_msg => 'unexp error:' || SQLERRM,
2563 x_returnStatus => l_returnStatus); --discard return status
2564 END IF;
2565 END setItemRevision;
2566
2567
2568 PROCEDURE deriveLotsFromMOG(
2569 x_compLots IN OUT NOCOPY system.wip_lot_serial_obj_t,
2570 p_orgID IN NUMBER,
2571 p_objectID IN NUMBER,
2572 p_initMsgList IN VARCHAR2,
2573 x_returnStatus OUT NOCOPY VARCHAR2) IS
2574
2575 l_returnStatus VARCHAR2(1);
2576 l_errMsg VARCHAR2(80);
2577 l_params wip_logger.param_tbl_t;
2578 l_curItem system.wip_component_obj_t;
2579 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
2580 BEGIN
2581 IF (l_logLevel <= wip_constants.trace_logging) THEN
2582 l_params(1).paramName := 'p_orgID';
2583 l_params(1).paramValue := p_orgID;
2584 l_params(2).paramName := 'p_objectID';
2585 l_params(2).paramValue := p_objectID;
2586
2587 wip_logger.entryPoint(
2588 p_procName => 'wip_autoLotProc_priv.deriveLotsFromMOG',
2589 p_params => l_params,
2590 x_returnStatus => x_returnStatus);
2591 END IF;
2592
2593 SAVEPOINT s_deriveLotsFromMOG;
2594
2595 IF(fnd_api.to_boolean(p_initMsgList)) THEN
2596 fnd_msg_pub.initialize;
2597 END IF;
2598
2599 x_compLots.reset;
2600 --assume we will be able to derive everything
2601 x_returnStatus := fnd_api.g_ret_sts_success;
2602
2603 LOOP
2604 IF(x_compLots.getCurrentItem(l_curItem)) THEN
2605 IF(l_curItem.wip_supply_type NOT IN (WIP_CONSTANTS.PUSH,
2606 WIP_CONSTANTS.OP_PULL,
2607 WIP_CONSTANTS.ASSY_PULL)
2608 OR
2609 l_curItem.lot_control_code = WIP_CONSTANTS.NO_LOT
2610 OR
2611 l_curItem.serial_number_control_code IN(WIP_CONSTANTS.FULL_SN,
2612 WIP_CONSTANTS.DYN_RCV_SN)
2613 OR
2614 l_curItem.transaction_action_id <> WIP_CONSTANTS.RETCOMP_ACTION
2615 ) THEN
2616 GOTO END_OF_LOOP;
2617 ELSE
2618 -- Instead of defaulting revision to the current revision, we should
2619 -- derive revision that got transacted from forward move transaction.
2620 setItemRevision(p_parentObjID => p_objectID,
2621 p_orgID => p_orgID,
2622 p_item => l_curItem,
2623 x_compLots => x_compLots,
2624 x_returnStatus => l_returnStatus);
2625
2626 IF(l_returnStatus = fnd_api.g_ret_sts_unexp_error) THEN
2627 l_errMsg := 'setItemRevision failed';
2628 raise fnd_api.g_exc_unexpected_error;
2629 END IF;
2630 END IF;
2631
2632 deriveSingleItemFromMOG(p_parentObjID => p_objectID,
2633 p_orgID => p_orgID,
2634 p_item => l_curItem,
2635 x_compLots => x_compLots,
2636 x_returnStatus => l_returnStatus);
2637
2638 IF(l_returnStatus = fnd_api.g_ret_sts_unexp_error) THEN
2639 l_errMsg := 'deriveSingleItemFromMOG failed';
2640 raise fnd_api.g_exc_unexpected_error;
2641 END IF;
2642
2643 END IF; -- x_compLots.getCurrentItem(l_curItem)
2644 <<END_OF_LOOP>>
2645
2646 EXIT WHEN NOT x_compLots.setNextItem;
2647 END LOOP;
2648
2649 IF (l_logLevel <= wip_constants.trace_logging) THEN
2650 wip_logger.exitPoint(
2651 p_procName => 'wip_autoLotProc_priv.deriveLotsFromMOG',
2652 p_procReturnStatus => x_returnStatus,
2653 p_msg => 'procedure success',
2654 x_returnStatus => l_returnStatus);
2655 END IF;
2656
2657 EXCEPTION
2658 WHEN fnd_api.g_exc_unexpected_error THEN
2659 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2660 IF (l_logLevel <= wip_constants.trace_logging) THEN
2661 wip_logger.exitPoint(
2662 p_procName => 'wip_autoLotProc_priv.deriveLotsFromMOG',
2663 p_procReturnStatus => x_returnStatus,
2664 p_msg => l_errMsg,
2665 x_returnStatus => l_returnStatus); --discard return status
2666 END IF;
2667 ROLLBACK TO s_deriveLotsFromMOG;
2668
2669 WHEN others THEN
2670 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
2671 IF (l_logLevel <= wip_constants.trace_logging) THEN
2672 wip_logger.exitPoint(
2673 p_procName => 'wip_autoLotProc_priv.deriveLotsFromMOG',
2674 p_procReturnStatus => x_returnStatus,
2675 p_msg => 'unexpected error:' || SQLERRM,
2676 x_returnStatus => l_returnStatus); --discard return status
2677 END IF;
2678 ROLLBACK TO s_deriveLotsFromMOG;
2679
2680 END deriveLotsFromMOG;
2681
2682 end wip_autoLotProc_priv;