1 PACKAGE BODY eam_material_allocations_pvt AS
2 /* $Header: EAMMTALB.pls 115.1 2003/10/09 03:24:49 dgupta noship $*/
3
4 /*
5 This package manages material allocations in MMTT, MTLT, MSNT along with related
6 interactions with move orders and WIP material requirements. Some of the APIS here
7 are not meant to be called in isolation. For example, reduce_allocation_header
8 is called in conjunction with subsequent calls to APIs as add_serial,
9 remove_serial, update_lot, inv_trx_util_pub.insert_lot_trx. If proper care is
10 not exercised in calling them in conjunction, the state of allocations
11 can become invalid.
12 */
13
14 g_pkg_name CONSTANT VARCHAR2(50):= 'eam_material_allocations_pvt';
15 g_module_name CONSTANT VARCHAR2(60):= 'eam.plsql.' || g_pkg_name;
16
17 --A helper function to fetch necessary information from MMTT for further processing.
18 function get_mmtt_info(
19 p_transaction_temp_id IN NUMBER,
20 x_primary_mmtt_qty OUT NOCOPY NUMBER,
21 x_tx_qty OUT NOCOPY NUMBER,
22 x_move_order_line_id OUT NOCOPY NUMBER
23 ) return boolean IS
24 l_module constant varchar2(200) := g_module_name||'.get_mmtt_info';
25 l_log boolean := ((FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) and
26 FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module));
27 begin
28 --get primary qty from MMTT
29 select primary_quantity, transaction_quantity, move_order_line_id
30 into x_primary_mmtt_qty, x_tx_qty, x_move_order_line_id
31 from mtl_material_transactions_temp
32 where transaction_temp_id = p_transaction_temp_id;
33
34 if (l_log and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) then
35 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
36 'MMTT: x_primary_mmtt_qty=' || x_primary_mmtt_qty|| ',x_tx_qty=' || x_tx_qty
37 || ',x_move_order_line_id=' || x_move_order_line_id);
38 end if;
39 return true;
40 exception
41 when no_data_found then
42 if (l_log and (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) then
43 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, l_module,
44 'Transaction Temp Id '|| p_transaction_temp_id || ' not found');
45 end if;
46 fnd_message.set_name('EAM', 'EAM_ALLOCATION_NOT_FOUND');
47 fnd_msg_pub.add;
48 return false;
49 WHEN OTHERS THEN
50 if (l_log and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) then
51 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
52 'Unhandled exception in get_mmtt_info');
53 end if;
54 return false;
55 end get_mmtt_info;
56
57 /* A private procedure to reduce a move order line. This is useful in cases when
58 after reducing a allocation, we also want to reduce the move order and optionally
59 the WIP requirements for this material
60 */
61 procedure reduce_move_order(
62 p_move_order_line_id in number,
63 p_qty_to_reduce in number,
64 p_reduce_wip_requirements in varchar2
65 ) IS
66 l_module constant varchar2(200) := g_module_name||'.reduce_move_order';
67 l_log boolean := ((FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) and
68 FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module));
69 l_txn_source_id number;
70 l_inventory_item_id number;
71 begin
72 -- Propagate changes to Move Order header,line and WRO if propagate parameter is set
73 -- As per Inventory, if a move order line has no allocations, it should be
74 -- closed (status 5), not cancelled (status 6)
75 update mtl_txn_request_lines
76 set quantity = quantity - p_qty_to_reduce,
77 quantity_detailed = quantity_detailed - p_qty_to_reduce,
78 line_status = decode(quantity,p_qty_to_reduce,5,line_status)
79 where line_id = p_move_order_line_id
80 returning txn_source_id, inventory_item_id
81 into l_txn_source_id, l_inventory_item_id;
82
83 if ((p_reduce_wip_requirements is null) or (p_reduce_wip_requirements= 'Y')) then
84 if (l_log and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) then
85 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
86 'MTRL: l_txn_source_id (wip entity id)=' || l_txn_source_id );
87 end if;
88 update wip_requirement_operations
89 set quantity_allocated = quantity_allocated - p_qty_to_reduce
90 where wip_entity_id = l_txn_source_id
91 and inventory_item_id = l_inventory_item_id;
92 end if;
93 end reduce_move_order;
94
95 /* This procedure deletes a allocation and reduces the move order and WIP material
96 requirements by a corresponding amount. Thus this is to be used when the allocated
97 material is no longer wanted by the requestor.
98 */
99 PROCEDURE delete_allocation(
100 p_init_msg_list IN VARCHAR2,
101 p_commit IN VARCHAR2,
102 p_transaction_temp_id IN NUMBER,
103 x_return_status OUT NOCOPY VARCHAR2,
104 x_msg_count OUT NOCOPY NUMBER,
105 x_msg_data OUT NOCOPY VARCHAR2
106 ) IS
107 l_module constant varchar2(200) := g_module_name||'.delete_allocation';
108 l_log boolean := ((FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) and
109 FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module));
110 l_primary_mmtt_qty number;
111 l_tx_qty number;
112 l_move_order_line_id number;
113 BEGIN
114 SAVEPOINT DELETE_ALLOCATION;
115 IF FND_API.to_Boolean( p_init_msg_list ) THEN
116 FND_MSG_PUB.initialize;
117 END IF;
118 x_return_status := FND_API.G_RET_STS_SUCCESS;
119 x_msg_count := 0;
120
121 if (l_log and (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) then
122 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
123 'Start of '||l_module ||'('||'p_transaction_temp_id='||p_transaction_temp_id||')');
124 end if;
125
126 --API start
127 --get primary qty and other information from MMTT
128 --Later on can reduce dependance on this by optional parameters which give this
129 --information(saves a db hit). If these are not provided, then we cn query up MMTT
130 if (not get_mmtt_info(p_transaction_temp_id,l_primary_mmtt_qty,
131 l_tx_qty, l_move_order_line_id)) then
132 FND_MSG_PUB.Count_And_Get('T', x_msg_count, x_msg_data);
133 return;
134 end if;
135 --Reduce the allocation quantities in Move order headers, lines and in WRO
136 reduce_move_order(l_move_order_line_id, l_primary_mmtt_qty, 'Y');
137 if (l_log and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) then
138 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
139 'Calling inv_trx_util_pub.delete_transaction');
140 end if;
141 --Call the inventory delete API.
142 --This API is similar to the wrapper inv_mo_line_detail_util.delete_allocation
143 inv_trx_util_pub.delete_transaction(
144 x_return_status => x_return_status
145 , x_msg_data => x_msg_data
146 , x_msg_count => x_msg_count
147 , p_transaction_temp_id => p_transaction_temp_id
148 );
149 if (l_log and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) then
150 eam_common_utilities_pvt.log_api_return(l_module,
151 'inv_trx_util_pub.delete_transaction',x_return_status,x_msg_count,x_msg_data);
152 end if;
153 if (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
154 raise fnd_api.g_exc_error;
155 end if;
156 --API end
157
158 IF FND_API.To_Boolean( p_commit ) THEN
159 COMMIT WORK;
160 END IF;
161 x_return_status := FND_API.G_RET_STS_SUCCESS;
162 FND_MSG_PUB.Count_And_Get('T', x_msg_count, x_msg_data);
163 if (l_log and (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) then
164 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module, 'End of ' || l_module);
165 end if;
166
167 EXCEPTION
168 WHEN fnd_api.g_exc_error THEN
169 x_return_status := fnd_api.g_ret_sts_error;
170 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
171 ROLLBACK to delete_allocation;
172 WHEN OTHERS THEN
173 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
174 FND_MSG_PUB.Count_And_Get(
175 p_count => x_msg_count,
176 p_data => x_msg_data);
177 ROLLBACK to delete_allocation;
178 end delete_allocation;
179
180 /* Reduces the allocation header at the MMTT level and reduces the move order
181 and WIP material requirements by a corresponding amount. Thus this is to be used
182 when the requestor wants to reduce his requirement and free up the allocation.
183 For lot and/or serial controlled items, this API is to be followed by subsequent
184 calls to other APIs to adjust the lot and serial allocations.
185 */
186 PROCEDURE reduce_allocation_header(
187 p_init_msg_list IN VARCHAR2,
188 p_commit IN VARCHAR2,
189 p_transaction_temp_id IN NUMBER,
190 p_organization_id IN NUMBER,
191 p_qty_to_reduce IN NUMBER,
192 p_final_qty IN NUMBER,
193 p_delete_remaining IN VARCHAR2,
194 x_new_transaction_temp_id OUT NOCOPY NUMBER,
195 x_return_status OUT NOCOPY VARCHAR2,
196 x_msg_count OUT NOCOPY NUMBER,
197 x_msg_data OUT NOCOPY VARCHAR2
198 ) IS
199 l_module constant varchar2(200) := g_module_name||'.reduce_allocation_header';
200 l_log boolean := ((FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) and
201 FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module));
202 l_qty_to_reduce number;
203 l_final_qty number;
204 l_primary_mmtt_qty number;
205 l_tx_qty number;
206 l_move_order_line_id number;
207 BEGIN
208 SAVEPOINT reduce_allocation_header;
209 x_return_status := FND_API.G_RET_STS_SUCCESS;
210 x_msg_count := 0;
211
212 if (l_log and (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) then
213 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
214 'Start of ' || l_module || '('
215 || 'p_transaction_temp_id='|| p_transaction_temp_id
216 || ',p_qty_to_reduce='|| p_qty_to_reduce || ',p_final_qty='|| p_final_qty
217 || ',p_delete_remaining='|| p_delete_remaining || ')');
218 end if;
219
220 --API start
221 --get primary qty and other information from MMTT
222 if (not get_mmtt_info(p_transaction_temp_id,l_primary_mmtt_qty,
223 l_tx_qty, l_move_order_line_id)) then
224 FND_MSG_PUB.Count_And_Get('T', x_msg_count, x_msg_data);
225 return;
226 end if;
227 --Qty can be entered as either reduction amount (delta) or final reduced amount
228 --Assume that the quantity specified is in terms of primary UOM
229 if (p_qty_to_reduce is not null) then
230 l_final_qty := l_primary_mmtt_qty - p_qty_to_reduce;
231 l_qty_to_reduce := p_qty_to_reduce;
232 else
233 l_final_qty := p_final_qty;
234 l_qty_to_reduce := l_primary_mmtt_qty - p_final_qty;
235 end if;
236 if (l_log and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) then
237 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
238 'Calculated l_final_qty='||l_final_qty||
239 ',l_qty_to_reduce='||l_qty_to_reduce);
240 end if;
241 --Validate final qty and if correct, reduce MMTT
242 if (l_final_qty <= 0) then
243 if (l_log and (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) then
244 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, l_module, 'Cannot reduce qty to below 0');
245 end if;
246 fnd_message.set_name('EAM', 'EAM_NON_POSITIVE_ISSUE_QTY');
247 fnd_msg_pub.add;
248 FND_MSG_PUB.Count_And_Get('T', x_msg_count, x_msg_data);
249 return;
250 --Following elsif (3 lines): not sure whether we should allow delete here
251 --also or only in delete_allocation
252 --elsif (l_final_qty = 0) then
253 -- delete mtl_material_transactions_temp
254 -- where transaction_temp_id = p_transaction_temp_id;
255 else
256 update mtl_material_transactions_temp
257 set primary_quantity = l_final_qty, --following line converts between base to tx uom
258 transaction_quantity = l_final_qty * (l_tx_qty/l_primary_mmtt_qty)
259 where transaction_temp_id = p_transaction_temp_id;
260 end if;
261
262 if (p_delete_remaining is not null and (p_delete_remaining = 'Y')) then
263 --Reduce the allocation quantities in Move order headers, lines and in WRO
264 reduce_move_order(l_move_order_line_id, l_qty_to_reduce, 'Y');
265 elsif (l_qty_to_reduce > 0) then
266 -- save the remainder allocation by splitting current allocation.
267 if (l_log and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) then
268 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
269 'Calling inv_trx_util_pub.copy_insert_line_trx');
270 end if;
271 --call the split API passing in current transaction temp id, split qty
272 inv_trx_util_pub.copy_insert_line_trx(
273 x_return_status => x_return_status
274 , x_msg_data => x_msg_data
275 , x_msg_count => x_msg_count
276 , x_new_txn_temp_id => x_new_transaction_temp_id
277 , p_transaction_temp_id => p_transaction_temp_id
278 , p_organization_id => p_organization_id
279 , p_txn_qty => l_qty_to_reduce
280 , p_primary_qty => l_qty_to_reduce
281 );
282 if (l_log and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) then
283 eam_common_utilities_pvt.log_api_return(l_module,
284 'inv_trx_util_pub.copy_insert_line_trx',x_return_status,x_msg_count,x_msg_data);
285 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
286 'inv_trx_util_pub.copy_insert_line_trx returned '
287 || 'x_new_txn_temp_id='|| x_new_transaction_temp_id);
288 end if;
289 if (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
290 raise fnd_api.g_exc_error;
291 end if;
292 end if;
293 --API end
294
295 IF FND_API.To_Boolean( p_commit ) THEN
296 COMMIT WORK;
297 END IF;
298 x_return_status := FND_API.G_RET_STS_SUCCESS;
299 FND_MSG_PUB.Count_And_Get('T', x_msg_count, x_msg_data);
300 if (l_log and (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) then
301 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module, 'End of ' || l_module);
302 end if;
303
304 EXCEPTION
305 WHEN fnd_api.g_exc_error THEN
306 x_return_status := fnd_api.g_ret_sts_error;
307 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
308 ROLLBACK to reduce_allocation_header;
309 WHEN OTHERS THEN
310 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
311 FND_MSG_PUB.Count_And_Get(
312 p_count => x_msg_count,
313 p_data => x_msg_data);
314 ROLLBACK to reduce_allocation_header;
315 end reduce_allocation_header;
316
317 /* Removes a serial allocation from MSNT and frees up/unmarks the serial in MSN.
318 Optionally if a new allocation temp id is provided, it can split this serial to
319 the new allocation temp id. This API does not adjust the quantities at the MMTT
320 or MTLT level and assumes that they have been adjusted by another API call.
321 Currently this API assumes that the serial numbers have a range of 1 (the
322 pick release process allocates serial numbers individually, not in ranges), this
323 assumption can be relaxed in the future by first splitting the range into
324 individual rows, then deleting just the row for this serial number.
325 */
326 PROCEDURE remove_serial(
327 p_init_msg_list IN VARCHAR2,
328 p_commit IN VARCHAR2,
329 p_transaction_temp_id IN NUMBER,
330 p_serial IN VARCHAR2,
331 p_lot IN VARCHAR2,
332 p_inventory_item_id IN NUMBER,
333 p_new_transaction_temp_id IN NUMBER,
334 x_return_status OUT NOCOPY VARCHAR2,
335 x_msg_count OUT NOCOPY NUMBER,
336 x_msg_data OUT NOCOPY VARCHAR2
337 ) IS
338 l_module constant varchar2(200) := g_module_name||'.remove_serial';
339 l_log boolean := ((FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) and
340 FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module));
341 BEGIN
342 SAVEPOINT remove_serial;
343 x_return_status := FND_API.G_RET_STS_SUCCESS;
344 x_msg_count := 0;
345
346 if (l_log and (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) then
347 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
348 'Start of ' || l_module || '('
349 || 'p_transaction_temp_id='|| p_transaction_temp_id
350 || ',p_serial='|| p_serial || ',p_lot='|| p_lot
351 || ',p_new_transaction_temp_id='|| p_new_transaction_temp_id ||')');
352 end if;
353
354 --API start
355 if (p_new_transaction_temp_id is not null) then
356 --replace the transaction_temp_id for msnt row with this one.
357 UPDATE mtl_serial_numbers_temp
358 SET transaction_temp_id = p_new_transaction_temp_id
359 WHERE transaction_temp_id = p_transaction_temp_id
360 AND fm_serial_number = p_serial;
361 --restamp the group mark id in msn.
362 UPDATE mtl_serial_numbers
363 SET group_mark_id = p_new_transaction_temp_id
364 WHERE group_mark_id = p_transaction_temp_id
365 AND serial_number = p_serial;
366 else
367 --delete current msnt row and then unmark serial
368 DELETE FROM mtl_serial_numbers_temp
369 WHERE transaction_temp_id = p_transaction_temp_id
370 and fm_serial_number = p_serial;
371 --Can also use serial_check.inv_unmark_serial
372 UPDATE mtl_serial_numbers
373 SET line_mark_id = -1
374 , group_mark_id = -1
375 , lot_line_mark_id = -1
376 WHERE group_mark_id = p_transaction_temp_id
377 AND serial_number = p_serial;
378 end if;
379 --API end
380
381 IF FND_API.To_Boolean( p_commit ) THEN
382 COMMIT WORK;
383 END IF;
384 x_return_status := FND_API.G_RET_STS_SUCCESS;
385 FND_MSG_PUB.Count_And_Get('T', x_msg_count, x_msg_data);
386 if (l_log and (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) then
387 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module, 'End of ' || l_module);
388 end if;
389
390 EXCEPTION
391 WHEN OTHERS THEN
392 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
393 FND_MSG_PUB.Count_And_Get(
394 p_count => x_msg_count,
395 p_data => x_msg_data);
396 ROLLBACK to remove_serial;
397 end remove_serial;
398
399 /* Adds a serial allocation to MSNT and reserves/marks the serial in MSN.
400 This API does not adjust the quantities at the MMTT or MTLT level and assumes
401 that they have been adjusted by another API call.
402 */
403 PROCEDURE add_serial(
404 p_init_msg_list IN VARCHAR2,
405 p_commit IN VARCHAR2,
406 p_transaction_temp_id IN NUMBER,
407 p_organization_id IN NUMBER,
408 p_inventory_item_id IN NUMBER,
409 p_serial IN VARCHAR2,
410 x_return_status OUT NOCOPY VARCHAR2,
411 x_msg_count OUT NOCOPY NUMBER,
412 x_msg_data OUT NOCOPY VARCHAR2
413 ) IS
414 l_module constant varchar2(200) := g_module_name||'.add_serial';
415 l_log boolean := ((FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) and
416 FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module));
417 l_insert_count number;
418 l_update_count number;
419 BEGIN
420 SAVEPOINT add_serial;
421 x_return_status := FND_API.G_RET_STS_SUCCESS;
422 x_msg_count := 0;
423
424 if (l_log and (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) then
425 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
426 'Start of ' || l_module || '(' ||
427 'p_transaction_temp_id='|| p_transaction_temp_id ||
428 'p_organization_id='|| p_organization_id ||
429 'p_inventory_item_id='|| p_inventory_item_id ||
430 ',p_serial='|| p_serial ||')');
431 end if;
432
433 --API start
434 --Not using inv_trx_util_pub.insert_ser_trx and serial_check.inv_mark_serial
435 --because of bug 2798128
436 INSERT INTO mtl_serial_numbers_temp(transaction_temp_id
437 , fm_serial_number, to_serial_number, serial_prefix
438 , last_update_date, last_updated_by, creation_date, created_by
439 )
440 VALUES(p_transaction_temp_id,p_serial, p_serial, 1
441 , SYSDATE, fnd_global.user_id, SYSDATE, fnd_global.user_id);
442 l_insert_count := SQL%ROWCOUNT;
443
444 UPDATE mtl_serial_numbers
445 SET group_mark_id = p_transaction_temp_id
446 WHERE serial_number = p_serial
447 AND current_organization_id = p_organization_id
448 AND inventory_item_id = p_inventory_item_id;
449 l_update_count := SQL%ROWCOUNT;
450
451 if (l_log and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) then
452 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
453 l_insert_count|| ' serials inserted into MSNT. '||l_update_count||
454 ' serials marked in MSN');
455 end if;
456 --API end
457
458 IF FND_API.To_Boolean( p_commit ) THEN
459 COMMIT WORK;
460 END IF;
461 x_return_status := FND_API.G_RET_STS_SUCCESS;
462 FND_MSG_PUB.Count_And_Get('T', x_msg_count, x_msg_data);
463 if (l_log and (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) then
464 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module, 'End of ' || l_module);
465 end if;
466
467 EXCEPTION
468 WHEN OTHERS THEN
469 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
470 FND_MSG_PUB.Count_And_Get(
471 p_count => x_msg_count,
472 p_data => x_msg_data);
473 ROLLBACK to add_serial;
474 end add_serial;
475
476 /* This API serves three functions:
477 1) Update lot quantity (if p_lot_quantity is non zero)
478 2) Delete a lot (if p_lot_quantity is zero)
479 3) Split a lot (if p_lot_quantity is less than current qty and new tx temp id is provided)
480 This API does not adjust the quantities at the MMTT or MSNT level and assumes
481 that they have been adjusted by another API call. Future work may need
482 to be done on the uniqueness of temp id, lot_number and qty combination.
483 */
484 PROCEDURE update_lot(
485 p_init_msg_list IN VARCHAR2,
486 p_commit IN VARCHAR2,
487 p_transaction_temp_id IN NUMBER,
488 p_serial_transaction_temp_id IN NUMBER,
489 p_lot IN VARCHAR2,
490 p_lot_quantity IN NUMBER,
491 p_old_lot_quantity IN NUMBER,
492 p_new_transaction_temp_id IN NUMBER,
493 x_ser_trx_id OUT NOCOPY NUMBER,
494 x_return_status OUT NOCOPY VARCHAR2,
495 x_msg_count OUT NOCOPY NUMBER,
496 x_msg_data OUT NOCOPY VARCHAR2
497 ) IS
498 l_module constant varchar2(200) := g_module_name||'.update_lot';
499 l_log boolean := ((FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) and
500 FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module));
501 l_update_count number := 0;
502 l_delete_count number := 0;
503 l_insert_ret_status number := null;
504 l_reduction_qty number := null;
505 BEGIN
506 SAVEPOINT reduce_lot;
507 x_return_status := FND_API.G_RET_STS_SUCCESS;
508 x_msg_count := 0;
509
510 if (l_log and (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) then
511 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
512 'Start of ' || l_module || '('
513 || 'p_transaction_temp_id='|| p_transaction_temp_id
514 || ' p_serial_transaction_temp_id='|| p_serial_transaction_temp_id
515 || ',p_lot='|| p_lot || ',p_lot_quantity='|| p_lot_quantity
516 || ',p_old_lot_quantity='|| p_old_lot_quantity
517 || ',p_new_transaction_temp_id='|| p_new_transaction_temp_id|| ')');
518 end if;
519
520 --API start
521 --update the qty of the current row. If qty = 0, delete this row.
522 if (p_lot_quantity > 0) then
523 if (p_serial_transaction_temp_id is not null) then
524 update mtl_transaction_lots_temp
525 set primary_quantity = p_lot_quantity,
526 transaction_quantity = p_lot_quantity
527 where serial_transaction_temp_id = p_serial_transaction_temp_id
528 and transaction_temp_id = p_transaction_temp_id
529 and lot_number = p_lot
530 and primary_quantity = p_old_lot_quantity;
531 else
532 update mtl_transaction_lots_temp
533 set primary_quantity = p_lot_quantity,
534 transaction_quantity = p_lot_quantity
535 where transaction_temp_id = p_transaction_temp_id
536 and lot_number = p_lot
537 and primary_quantity = p_old_lot_quantity
538 and rownum = 1;--update only one row since there is no PK in this table
539 --and it is discouraged to use rowid: distributed databases
540 --or partitioning may change it within a transaction
541 end if;
542 l_update_count := SQL%ROWCOUNT;
543 if (l_log and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) then
544 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
545 l_update_count || ' row(s) updated in MTLT');
546 end if;
547 l_reduction_qty := (p_old_lot_quantity - p_lot_quantity);
548 if (p_new_transaction_temp_id is not null and (l_reduction_qty >0)) then
549 --split this mtlt row under a new MMTT row with remaining qty
550 if (l_log and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) then
551 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
552 'Calling inv_trx_util_pub.insert_lot_trx('|| 'p_pri_qty=p_trx_qty='|| l_reduction_qty);
553 end if;
554 l_insert_ret_status := inv_trx_util_pub.insert_lot_trx(
555 p_trx_tmp_id => p_new_transaction_temp_id
556 , p_user_id => fnd_global.user_id
557 , p_lot_number => p_lot
558 , p_trx_qty => l_reduction_qty
559 , p_pri_qty => l_reduction_qty
560 , x_proc_msg => x_msg_data
561 , x_ser_trx_id => x_ser_trx_id);
562 if (l_log and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) then
563 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
564 'inv_trx_util_pub.insert_lot_trx returned: Status='||
565 l_insert_ret_status|| ' (0 success, -1 failure)'||
566 ', Return Message='|| x_msg_data|| ', Serial Temp Id='||
567 x_ser_trx_id);
568 end if;
569 end if;
570 elsif (p_lot_quantity = 0) then
571 delete from mtl_transaction_lots_temp
572 where transaction_temp_id = p_transaction_temp_id
573 and lot_number = p_lot
574 and primary_quantity = p_old_lot_quantity
575 and rownum = 1;
576 l_delete_count := SQL%ROWCOUNT;
577 if (l_log and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) then
578 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,
579 l_delete_count || ' row(s) deleted in MTLT');
580 end if;
581 end if;
582 --API end
583
584 IF FND_API.To_Boolean( p_commit ) THEN
585 COMMIT WORK;
586 END IF;
587 x_return_status := FND_API.G_RET_STS_SUCCESS;
588 FND_MSG_PUB.Count_And_Get('T', x_msg_count, x_msg_data);
589 if (l_log and (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) then
590 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module, 'End of ' || l_module);
591 end if;
592
593 EXCEPTION
594 WHEN OTHERS THEN
595 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
596 FND_MSG_PUB.Count_And_Get(
597 p_count => x_msg_count,
598 p_data => x_msg_data);
599 ROLLBACK to reduce_lot;
600 end update_lot;
601
602 /* Marks a lot row in MTLT with a serial transaction temp id so that
603 child records in MSNT can be inserted for this lot.
604 */
605 PROCEDURE mark_lot_with_ser_temp_id(
606 p_init_msg_list IN VARCHAR2,
607 p_commit IN VARCHAR2,
608 p_transaction_temp_id IN NUMBER,
609 p_lot IN VARCHAR2,
610 p_primary_quantity IN NUMBER,
611 x_ser_trx_id OUT NOCOPY NUMBER,
612 x_return_status OUT NOCOPY VARCHAR2,
613 x_msg_count OUT NOCOPY NUMBER,
614 x_msg_data OUT NOCOPY VARCHAR2
615 ) IS
616 l_module constant varchar2(200) := g_module_name||'.mark_lot_with_ser_temp_id';
617 l_log boolean := ((FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) and
618 FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module));
619 l_update_count number := 0;
620 l_ser_trx_id number;
621 BEGIN
622 SAVEPOINT mark_lot_with_ser_temp_id;
623 x_return_status := FND_API.G_RET_STS_ERROR;
624 x_msg_count := 0;
625
626 if (l_log and (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) then
627 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module,
628 'Start of ' || l_module || '(' ||'p_commit=' ||p_commit
629 || 'p_transaction_temp_id='|| p_transaction_temp_id
630 || ',p_lot='|| p_lot || ',p_primary_quantity='|| p_primary_quantity|| ')');
631 end if;
632
633 --API start
634 SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL into l_ser_trx_id from dual;
635 update mtl_transaction_lots_temp
636 set serial_transaction_temp_id = l_ser_trx_id
637 where transaction_temp_id = p_transaction_temp_id
638 and lot_number = p_lot
639 and primary_quantity = p_primary_quantity
640 and rownum = 1;
641 l_update_count := SQL%ROWCOUNT;
642 if (l_update_count = 1) then
643 x_return_status := FND_API.G_RET_STS_SUCCESS;
644 x_ser_trx_id := l_ser_trx_id;
645 end if;
646 if (l_log and (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) then
647 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module, l_update_count ||
648 ' row(s) updated in MTLT with serial_transaction_temp_id: '|| x_ser_trx_id);
649 end if;
650 --API end
651
652 IF FND_API.To_Boolean( p_commit ) THEN
653 COMMIT WORK;
654 END IF;
655 FND_MSG_PUB.Count_And_Get('T', x_msg_count, x_msg_data);
656 if (l_log and (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) then
657 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, l_module, 'End of ' || l_module);
658 end if;
659
660 EXCEPTION
661 WHEN OTHERS THEN
662 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
663 FND_MSG_PUB.Count_And_Get(
664 p_count => x_msg_count,
665 p_data => x_msg_data);
666 ROLLBACK to mark_lot_with_ser_temp_id;
667 end mark_lot_with_ser_temp_id;
668
669 END EAM_MATERIAL_ALLOCATIONS_PVT;
[Home] [Help]