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