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