[Home] [Help]
PACKAGE BODY: APPS.WIP_AUTOSERIALPROC_PRIV
Source
1 PACKAGE BODY wip_autoSerialProc_priv AS
2 /* $Header: wipserpb.pls 120.1 2010/08/03 10:30:22 pfauzdar ship $ */
3
4 ----------------------
5 --forward declarations
6 ----------------------
7 function getTxnType(p_txnActionID IN NUMBER) return NUMBER;
8
9 PROCEDURE deriveSingleItem
10 (p_parentObjID IN NUMBER,
11 p_orgID IN NUMBER,
12 x_compLots IN OUT NOCOPY system.wip_lot_serial_obj_t,
13 x_returnStatus OUT NOCOPY VARCHAR2) IS
14
15 CURSOR c_lotComp(p_parentObjID NUMBER,
16 p_componentID NUMBER,
17 p_orgID NUMBER,
18 p_opSeqNum NUMBER) IS
19
20 SELECT msn.lot_number lot,
21 count(msn.lot_number) lot_qty
22 FROM mtl_object_genealogy mog,
23 mtl_material_transactions mmt,
24 mtl_serial_numbers msn
25 WHERE mog.object_id = msn.gen_object_id
26 AND msn.last_transaction_id = mmt.transaction_id
27 AND mog.end_date_active IS NULL
28 AND mog.parent_object_id = p_parentObjID
29 AND msn.inventory_item_id = p_componentID
30 AND msn.current_organization_id = p_orgID
31 AND mmt.operation_seq_num = p_opSeqNum
32 AND msn.lot_number IS NOT NULL
33 GROUP BY msn.lot_number;
34
35 CURSOR c_serialComp(p_parentObjID NUMBER,
36 p_componentID NUMBER,
37 p_orgID NUMBER,
38 p_opSeqNum NUMBER,
39 p_lotNumber VARCHAR2) IS
40
41 SELECT msn.parent_serial_number parent_serial,
42 msn.serial_number serial
43 FROM mtl_object_genealogy mog,
44 mtl_material_transactions mmt,
45 mtl_serial_numbers msn
46 WHERE mog.object_id = msn.gen_object_id
47 AND msn.last_transaction_id = mmt.transaction_id
48 AND mog.end_date_active IS NULL
49 AND mog.parent_object_id = p_parentObjID
50 AND msn.inventory_item_id = p_componentID
51 AND msn.current_organization_id = p_orgID
52 AND mmt.operation_seq_num = p_opSeqNum
53 AND ((p_lotNumber IS NULL AND msn.lot_number IS NULL)
54 OR
55 (p_lotNumber IS NOT NULL AND msn.lot_number = p_lotNumber))
56 ORDER BY msn.serial_number;
57
58 l_cond BOOLEAN;
59 l_derivedQty NUMBER := 0;
60 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
61 l_curLot VARCHAR2(30) := null;
62 l_errMsg VARCHAR2(240);
63 l_returnStatus VARCHAR2(1);
64 l_item system.wip_component_obj_t;
65 l_lotComp c_lotComp%ROWTYPE;
66 l_params wip_logger.param_tbl_t;
67 l_serialComp c_serialComp%ROWTYPE;
68
69 BEGIN
70 -- Don't need to check the return status because already check in
71 -- deriveSerial()
72 l_cond := x_compLots.getCurrentItem(l_item);
73
74 IF (l_logLevel <= wip_constants.trace_logging) THEN
75 l_params(1).paramName := 'p_parentObjID';
76 l_params(1).paramValue := p_parentObjID;
77 l_params(2).paramName := 'p_orgID';
78 l_params(2).paramValue := p_orgID;
79 l_params(3).paramName := 'x_compLot(cur_item).inventory_item_id';
80 l_params(3).paramValue := l_item.inventory_item_id;
81 l_params(4).paramName := 'x_compLot(cur_item).operation_seq_num';
82 l_params(4).paramValue := l_item.operation_seq_num;
83 l_params(5).paramName := 'x_compLot(cur_item).supply_subinventory';
84 l_params(5).paramValue := l_item.supply_subinventory;
85 l_params(6).paramName := 'x_compLot(cur_item).supply_locator_id';
86 l_params(6).paramValue := l_item.supply_locator_id;
87 l_params(7).paramName := 'x_compLot(cur_item).revision';
88 l_params(7).paramValue := l_item.revision;
89 l_params(8).paramName := 'x_compLot(cur_item).primary_quantity';
90 l_params(8).paramValue := l_item.primary_quantity;
91 l_params(9).paramName := 'x_compLot(cur_item).transaction_action_id';
92 l_params(9).paramValue := l_item.transaction_action_id;
93 wip_logger.entryPoint(
94 p_procName => 'wip_autoSerialProc_priv.deriveSingleItem',
95 p_params => l_params,
96 x_returnStatus => x_returnStatus);
97 END IF;
98
99 -- Under lot and serial control
100 FOR l_lotComp IN c_lotComp
101 (p_parentObjID => p_parentObjID,
102 p_componentID => l_item.inventory_item_id,
103 p_orgID => p_orgID,
104 p_opSeqNum => l_item.operation_seq_num) LOOP
105
106 x_compLots.addLot(p_lotNumber => l_lotComp.lot,
107 p_priQty => l_lotComp.lot_qty,
108 p_attributes => null);
109
110 l_derivedQty := l_derivedQty + l_lotComp.lot_qty;
111
112 IF (l_logLevel <= wip_constants.full_logging) THEN
113 wip_logger.log('Added Lot : ' || l_lotComp.lot, l_returnStatus);
114 wip_logger.log('Added Lot Qty : '|| l_lotComp.lot_qty, l_returnStatus);
115 END IF;
116
117 -- derive lot/serial genealogy
118 FOR l_serialComp IN c_serialComp
119 (p_parentObjID => p_parentObjID,
120 p_componentID => l_item.inventory_item_id,
121 p_orgID => p_orgID,
122 p_opSeqNum => l_item.operation_seq_num,
123 p_lotNumber => l_lotComp.lot) LOOP
124
125 x_compLots.addLotSerial(p_fmSerial => l_serialComp.serial,
126 p_toSerial => l_serialComp.serial,
127 p_parentSerial => l_serialComp.parent_serial,
128 p_priQty => 1,
129 p_attributes => null);
130 IF (l_logLevel <= wip_constants.full_logging) THEN
131 wip_logger.log('Added Serial : ' || l_serialComp.serial,
132 l_returnStatus);
133 END IF;
134 END LOOP; -- l_serialComp IN c_serialComp
135 END LOOP; -- l_lotComp IN c_lotComp
136
137 -- Check whether derived quantity equal to backflush quantity or not
138 -- If not, error out. (for lot and serial control component)
139 IF(l_item.lot_control_code = WIP_CONSTANTS.LOT AND
140 l_item.primary_quantity <> l_derivedQty * -1) THEN
141 wip_logger.log('item : ' || l_item.item_name, l_returnStatus);
142 wip_logger.log('primary_quantity : ' || l_item.primary_quantity,
143 l_returnStatus);
144 wip_logger.log('derived_quantity : ' || l_derivedQty, l_returnStatus);
145 l_errMsg := 'return quantity missmatch';
146 raise fnd_api.g_exc_unexpected_error;
147 END IF;
148
149 -- Serial control only
150 FOR l_serialComp IN c_serialComp
151 (p_parentObjID => p_parentObjID,
152 p_componentID => l_item.inventory_item_id,
153 p_orgID => p_orgID,
154 p_opSeqNum => l_item.operation_seq_num,
155 p_lotNumber => null) LOOP
156
157 x_compLots.addSerial(p_fmSerial => l_serialComp.serial,
158 p_toSerial => l_serialComp.serial,
159 p_parentSerial => l_serialComp.parent_serial,
160 p_priQty => 1,
161 p_attributes => null);
162
163 l_derivedQty := l_derivedQty + 1;
164
165 IF (l_logLevel <= wip_constants.full_logging) THEN
166 wip_logger.log('Added Serial : ' || l_serialComp.serial,
167 l_returnStatus);
168 END IF;
169 END LOOP; -- l_serialComp IN c_serialComp
170
171 -- Check whether derived quantity equal to backflush quantity or not
172 -- If not, error out. (for serial control only component)
173 IF(l_item.lot_control_code = WIP_CONSTANTS.NO_LOT AND
174 l_item.primary_quantity <> l_derivedQty * -1) THEN
175 wip_logger.log('item : ' || l_item.item_name, l_returnStatus);
176 wip_logger.log('primary_quantity : ' || l_item.primary_quantity,
177 l_returnStatus);
178 wip_logger.log('derived_quantity : ' || l_derivedQty, l_returnStatus);
179 l_errMsg := 'return quantity missmatch : ' || l_item.item_name;
180 raise fnd_api.g_exc_unexpected_error;
181 END IF;
182
183 IF (l_logLevel <= wip_constants.trace_logging) THEN
184 wip_logger.exitPoint(
185 p_procName => 'wip_autoSerialProc_priv.deriveSingleItem',
186 p_procReturnStatus => x_returnStatus,
187 p_msg => 'procedure success',
188 x_returnStatus => l_returnStatus); --discard return status
189 END IF;
190
191 EXCEPTION
192 WHEN fnd_api.g_exc_unexpected_error THEN
193 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
194 IF (l_logLevel <= wip_constants.trace_logging) THEN
195 wip_logger.exitPoint(
196 p_procName => 'wip_autoSerialProc_priv.deriveSingleItem',
197 p_procReturnStatus => x_returnStatus,
198 p_msg => l_errMsg,
199 x_returnStatus => l_returnStatus); --discard return status
200 END IF;
201
202 fnd_message.set_name('WIP', 'WIP_RET_QTY_MISSMATCH');
203 fnd_message.set_token('ENTITY1', l_item.item_name);
204 fnd_msg_pub.add;
205
206 WHEN others THEN
207 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
208 IF (l_logLevel <= wip_constants.trace_logging) THEN
209 wip_logger.exitPoint(
210 p_procName => 'wip_autoSerialProc_priv.deriveSingleItem',
211 p_procReturnStatus => x_returnStatus,
212 p_msg => 'unexp error:' || SQLERRM,
213 x_returnStatus => l_returnStatus); --discard return status
214 END IF;
215 END deriveSingleItem;
216
217 PROCEDURE setItemRevision
218 (p_parentObjID IN NUMBER,
219 p_orgID IN NUMBER,
220 p_item IN system.wip_component_obj_t,
221 x_compLots IN OUT NOCOPY system.wip_lot_serial_obj_t,
222 x_returnStatus OUT NOCOPY VARCHAR2) IS
223
224 CURSOR c_revisionComp IS
225
226 SELECT mmt.revision revision
227 FROM mtl_object_genealogy mog,
228 mtl_material_transactions mmt,
229 mtl_serial_numbers msn
230 WHERE mog.object_id = msn.gen_object_id
231 AND msn.last_transaction_id = mmt.transaction_id
232 AND mog.end_date_active IS NULL
233 AND mog.parent_object_id = p_parentObjID
234 AND msn.inventory_item_id = p_item.inventory_item_id
235 AND msn.current_organization_id = p_orgID
236 AND mmt.operation_seq_num = p_item.operation_seq_num;
237
238 l_errMsg VARCHAR2(240);
239 l_returnStatus VARCHAR2(1);
240 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
241 l_params wip_logger.param_tbl_t;
242 l_revisionComp c_revisionComp%ROWTYPE;
243 BEGIN
244
245 IF (l_logLevel <= wip_constants.trace_logging) THEN
246 l_params(1).paramName := 'p_parentObjID';
247 l_params(1).paramValue := p_parentObjID;
248 l_params(2).paramName := 'p_orgID';
249 l_params(2).paramValue := p_orgID;
250 l_params(3).paramName := 'p_item.inventory_item_id';
251 l_params(3).paramValue := p_item.inventory_item_id;
252 l_params(4).paramName := 'p_item.operation_seq_num';
253 l_params(4).paramValue := p_item.operation_seq_num;
254 l_params(5).paramName := 'p_item.supply_subinventory';
255 l_params(5).paramValue := p_item.supply_subinventory;
256 l_params(6).paramName := 'p_item.supply_locator_id';
257 l_params(6).paramValue := p_item.supply_locator_id;
258 l_params(7).paramName := 'p_item.revision';
259 l_params(7).paramValue := p_item.revision;
260 l_params(8).paramName := 'p_item.primary_quantity';
261 l_params(8).paramValue := p_item.primary_quantity;
262 l_params(9).paramName := 'p_item.transaction_action_id';
263 l_params(9).paramValue := p_item.transaction_action_id;
264 wip_logger.entryPoint(
265 p_procName => 'wip_autoSerialProc_priv.setItemRevision',
266 p_params => l_params,
267 x_returnStatus => x_returnStatus);
268 END IF;
269
270 OPEN c_revisionComp;
271 -- Since revision is at the item level, we can just get the revision of
272 -- the first record.
273 FETCH c_revisionComp INTO l_revisionComp;
274
275 IF(c_revisionComp%FOUND AND
276 l_revisionComp.revision IS NOT NULL) THEN
277 -- x_item.revision := l_revisionComp.revision;
278 x_compLots.setRevision(p_revision => l_revisionComp.revision);
279 END IF;
280
281 IF (l_logLevel <= wip_constants.trace_logging) THEN
282 wip_logger.exitPoint(
283 p_procName => 'wip_autoSerialProc_priv.setItemRevision',
284 p_procReturnStatus => x_returnStatus,
285 p_msg => 'procedure success',
286 x_returnStatus => l_returnStatus); --discard return status
287 END IF;
288 CLOSE c_revisionComp;
289 EXCEPTION
290 WHEN others THEN
291 IF(c_revisionComp%ISOPEN) THEN
292 CLOSE c_revisionComp;
293 END IF;
294
295 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
296 IF (l_logLevel <= wip_constants.trace_logging) THEN
297 wip_logger.exitPoint(
298 p_procName => 'wip_autoSerialProc_priv.setItemRevision',
299 p_procReturnStatus => x_returnStatus,
300 p_msg => 'unexp error:' || SQLERRM,
301 x_returnStatus => l_returnStatus); --discard return status
302 END IF;
303 END setItemRevision;
304
305 /*****************************************************************************
309 *
306 * This package will do serial derivation. It will take an object of items and
307 * then derive serials for those items based on the genealogy built for
308 * assembly. Serials can be derived as follows:
310 * Return : A quantity tree is built to query the amount of onhand
311 * serial quantities in the given backflush location.
312 *
313 * Issues : Serial cannot be derived for this transaction type
314 * because no genealogy have been built yet.
315 *
316 * Negative Return/ : Serial cannot be derived for these transaction types
317 * Negative Issue because no genealogy have been built for these txns
318 *
319 * parameters:
320 * x_compLots This parameter contains all the items that need to be
321 * unbackflushed. On output, derived serial/lot are added to
322 * the object appropriately.
323 * p_objectID Object_id of the parent serial(assembly). Used to derive
324 * all the child serial number
325 * p_orgID Organization ID
326 * p_initMsgList Initialize the message list?
327 * x_returnStatus fnd_api.g_ret_sts_success if success without any errors.
328 * Otherwise return fnd_api.g_ret_sts_unexp_error.
329 ****************************************************************************/
330 PROCEDURE deriveSerial(x_compLots IN OUT NOCOPY system.wip_lot_serial_obj_t,
331 p_orgID IN NUMBER,
332 p_objectID IN NUMBER,
333 p_initMsgList IN VARCHAR2,
334 x_returnStatus OUT NOCOPY VARCHAR2) IS
335 l_returnStatus VARCHAR2(1);
336 l_errMsg VARCHAR2(80);
337 l_params wip_logger.param_tbl_t;
338 l_curItem system.wip_component_obj_t;
339 l_txnTypeId NUMBER;
340 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
341 BEGIN
342 IF (l_logLevel <= wip_constants.trace_logging) THEN
343 l_params(1).paramName := 'p_objectID';
344 l_params(1).paramValue := p_objectID;
345 wip_logger.entryPoint(
346 p_procName => 'wip_autoSerialProc_priv.deriveSerial',
347 p_params => l_params,
348 x_returnStatus => x_returnStatus);
349 END IF;
350
351 SAVEPOINT wipbflpb40;
352
353 IF(fnd_api.to_boolean(p_initMsgList)) THEN
354 fnd_msg_pub.initialize;
355 END IF;
356
357 x_compLots.reset;
358 --assume we will be able to derive everything
359 x_returnStatus := fnd_api.g_ret_sts_success;
360
361 LOOP
362 IF(x_compLots.getCurrentItem(l_curItem)) THEN
363 l_txnTypeId := getTxnType(p_txnActionID => l_curItem.transaction_action_id);
364 IF(l_curItem.wip_supply_type NOT IN (WIP_CONSTANTS.PUSH,
365 WIP_CONSTANTS.OP_PULL,
366 WIP_CONSTANTS.ASSY_PULL)
367 OR
368 (l_curItem.serial_number_control_code NOT IN(WIP_CONSTANTS.FULL_SN,
369 WIP_CONSTANTS.DYN_RCV_SN)
370 /* Bug 9907143: added for serial tagging enhancement for SUN */
371 AND
372 inv_cache.get_serial_tagged(p_orgID, l_curItem.inventory_item_id, l_txnTypeId ) = WIP_CONSTANTS.NO_SER_TAGGED
373 )
374 OR
375 l_curItem.transaction_action_id NOT IN(WIP_CONSTANTS.RETCOMP_ACTION)
376 ) THEN
377 GOTO END_OF_LOOP;
378 ELSE
379 -- Instead of defaulting revision to the current revision, we should
380 -- derive revision that got transacted from forward move transaction.
381 setItemRevision(p_parentObjID => p_objectID,
382 p_orgID => p_orgID,
383 p_item => l_curItem,
384 x_compLots => x_compLots,
385 x_returnStatus => l_returnStatus);
386
387 IF(l_returnStatus = fnd_api.g_ret_sts_unexp_error) THEN
388 l_errMsg := 'setItemRevision failed';
389 raise fnd_api.g_exc_unexpected_error;
390 END IF;
391 END IF;
392
393 deriveSingleItem(p_parentObjID => p_objectID,
394 p_orgID => p_orgID,
395 x_compLots => x_compLots,
396 x_returnStatus => l_returnStatus);
397
398 IF(l_returnStatus = fnd_api.g_ret_sts_unexp_error) THEN
399 l_errMsg := 'deriveSingleItem failed';
400 raise fnd_api.g_exc_unexpected_error;
401 END IF;
402
403 END IF; -- x_compLots.getCurrentItem(l_curItem)
404 <<END_OF_LOOP>>
405
406 EXIT WHEN NOT x_compLots.setNextItem;
407 END LOOP;
408
409 IF (l_logLevel <= wip_constants.trace_logging) THEN
410 wip_logger.exitPoint(
411 p_procName => 'wip_autoSerialProc_priv.deriveSerial',
412 p_procReturnStatus => x_returnStatus,
413 p_msg => 'procedure success',
414 x_returnStatus => l_returnStatus);
415 END IF;
416
417 EXCEPTION
418 WHEN fnd_api.g_exc_unexpected_error THEN
419 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
420 IF (l_logLevel <= wip_constants.trace_logging) THEN
421 wip_logger.exitPoint(
422 p_procName => 'wip_autoSerialProc_priv.deriveSerial',
423 p_procReturnStatus => x_returnStatus,
424 p_msg => l_errMsg,
425 x_returnStatus => l_returnStatus); --discard return status
426 END IF;
427 ROLLBACK TO wipbflpb40;
428
429 WHEN others THEN
430 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
431 IF (l_logLevel <= wip_constants.trace_logging) THEN
435 p_msg => 'unexpected error:' || SQLERRM,
432 wip_logger.exitPoint(
433 p_procName => 'wip_autoSerialProc_priv.deriveSerial',
434 p_procReturnStatus => x_returnStatus,
436 x_returnStatus => l_returnStatus); --discard return status
437 END IF;
438 ROLLBACK TO wipbflpb40;
439
440 END deriveSerial;
441
442 function getTxnType(p_txnActionID IN NUMBER) return NUMBER is
443 begin
444 if(p_txnActionID = wip_constants.isscomp_action) then
445 return wip_constants.isscomp_type;
446 elsif(p_txnActionID = wip_constants.retnegc_action) then
447 return wip_constants.retnegc_type;
448 elsif(p_txnActionID = wip_constants.retcomp_action) then
449 return wip_constants.retcomp_type;
450 elsif(p_txnActionID = wip_constants.issnegc_action) then
451 return wip_constants.issnegc_type;
452 end if;
453 return -1; --this procedure only works for component txn types
454 end getTxnType;
455
456 END wip_autoSerialProc_priv;
457