[Home] [Help]
PACKAGE BODY: APPS.INV_COST_GROUP_UPDATE
Source
1 PACKAGE BODY inv_cost_group_update AS
2 /* $Header: INVCGUPB.pls 120.6.12010000.4 2009/07/21 23:25:20 mchemban ship $*/
3 g_pkg_name varchar2(100) := 'INV_COST_GROUP_UPDATE';
4
5 g_corrupt_cg_error VARCHAR2(1) := 'C';
6
7 CURSOR cur_mtlt (p_transaction_temp_id NUMBER) IS
8 SELECT mtlt.ROWID mtlt_rowid,
9 mtlt.*
10 FROM mtl_transaction_lots_temp mtlt
11 WHERE mtlt.transaction_temp_id = p_transaction_temp_id;
12
13 CURSOR cur_msnt(cp_transaction_temp_id NUMBER) IS
14 SELECT msnt.* ,
15 msnt.ROWID msnt_rowid
16 FROM mtl_serial_numbers_temp msnt
17 WHERE transaction_temp_id = cp_transaction_temp_id;
18
19 CURSOR cur_msn(cp_fm_serial_number VARCHAR2,
20 cp_to_serial_number VARCHAR2,
21 cp_inventory_item_id NUMBER,
22 cp_organization_id NUMBER,
23 cp_prefix VARCHAR2,
24 cp_length NUMBER)
25 IS
26 SELECT cost_group_id,
27 serial_number
28 FROM mtl_serial_numbers
29 WHERE serial_number
30 BETWEEN cp_fm_serial_number AND Nvl(cp_to_serial_number, cp_fm_serial_number)
31 AND Length(serial_number)=cp_length
32 AND serial_number LIKE (cp_prefix||'%')
33 AND inventory_item_id = cp_inventory_item_id
34 AND current_organization_id = cp_organization_id;
35
36 procedure print_debug(p_message in VARCHAR2) IS
37
38 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
39 begin
40 IF (l_debug = 1) THEN
41 inv_log_util.trace(p_message, 'INV_COST_GROUP_UPDATE', 9);
42 END IF;
43 end;
44
45 PROCEDURE proc_update_mmtt(p_transaction_temp_id IN NUMBER,
46 p_transfer_wms_org IN BOOLEAN,
47 p_fob_point IN NUMBER,
48 p_tfr_primary_cost_method IN NUMBER,
49 p_tfr_org_cost_group_id IN NUMBER,
50 p_transaction_action_id IN NUMBER,
51 p_transfer_organization IN NUMBER := NULL,
52 p_transfer_subinventory IN VARCHAR2,
53 p_cost_group_id IN NUMBER,
54 p_transfer_cost_group_id IN NUMBER,
55 p_primary_quantity IN NUMBER := NULL,
56 p_transaction_quantity IN NUMBER := NULL,
57 p_from_project_id IN NUMBER := NULL,
58 p_to_project_id IN NUMBER := NULL,
59 x_return_status OUT NOCOPY VARCHAR2)
60 IS
61 l_transfer_cost_group_id NUMBER := NULL;
62 x_valid VARCHAR2(1) := 'Y';
63
64 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
65 BEGIN
66 IF (l_debug = 1) THEN
67 print_debug('in proc_update_mmtt .. p_transaction_temp_id : '|| p_transaction_temp_id );
68 print_debug('in proc_update_mmtt .. p_cost_group_id : '|| p_cost_group_id );
69 print_debug('in proc_update_mmtt .. p_transaction_action_id : '|| p_transaction_action_id );
70 print_debug('in proc_update_mmtt .. p_transfer_organization : '|| p_transfer_organization );
71 END IF;
72 x_return_status := fnd_api.g_ret_sts_success;
73
74 IF p_transaction_action_id IN (inv_globals.g_action_subxfr,
75 inv_globals.g_action_stgxfr,
76 inv_globals.g_action_ownxfr)
77 -- Subtransfer, staging transfer
78 THEN
79 l_transfer_cost_group_id := p_cost_group_id;
80 ELSE
81 l_transfer_cost_group_id := p_transfer_cost_group_id;
82 END IF;
83
84 IF p_transaction_Action_id = inv_globals.g_action_intransitshipment THEN
85 IF NOT p_transfer_wms_org AND p_fob_point = 1 THEN -- shipment
86 -- We don't care about the costing method of the org
87 l_transfer_cost_group_id := p_tfr_org_cost_group_id;
88 IF (l_debug = 1) THEN
89 print_debug('default cost group of org ' || p_transfer_organization || ' : ' || l_transfer_cost_group_id);
90 END IF;
91 ELSIF p_fob_point = 2 THEN -- receipt
92 l_transfer_cost_group_id := p_cost_group_id;
93 END IF;
94 END IF;
95
96 IF(p_from_project_id IS NULL AND
97 p_to_project_id IS NOT NULL AND
98 p_transaction_action_id IN (inv_globals.g_action_subxfr,
99 inv_globals.g_action_stgxfr,
100 inv_globals.G_Action_Receipt)) then
101
102 IF (l_debug = 1) THEN
103 print_debug('updating the transfer_cost_group to null as the dest'|| 'locator is proj enabled');
104 END IF;
105 l_transfer_cost_group_id := NULL;
106
107 END IF;
108
109
110
111 IF (l_debug = 1) THEN
112 print_debug('proc_update_mmtt .. l_transfer_cost_group_id: ' ||
113 l_transfer_cost_group_id || ':' );
114 END IF;
115 UPDATE mtl_material_transactions_temp
116 SET cost_group_id = Nvl(p_cost_group_id, cost_group_id),
117 transfer_cost_group_id = Nvl(l_transfer_cost_group_id, transfer_cost_group_id),
118 primary_quantity = Nvl(p_primary_quantity, primary_quantity),
119 transaction_quantity = Nvl(p_transaction_quantity, transaction_quantity)
120 WHERE transaction_temp_id = p_transaction_temp_id;
121 IF (SQL%NOTFOUND )THEN
122 x_return_status := FND_API.G_RET_STS_ERROR ;
123 fnd_message.set_name('INV', 'INV_UPDATE_ERROR');
124 fnd_message.set_token('ENTITY1', 'mtl_material_transactions_temp');
125 -- MESSAGE_TEXT = "Error Updating ENTITY1 "
126 fnd_msg_pub.add;
127 IF (l_debug = 1) THEN
128 print_debug('proc_update_mmtt .. nodatafound OTHERS : ' );
129 END IF;
130 END IF;
131
132 EXCEPTION
133 WHEN FND_API.G_EXC_ERROR THEN
134 x_return_status := FND_API.G_RET_STS_ERROR ;
135 IF (l_debug = 1) THEN
136 print_debug('proc_update_mmtt .. EXCEP G_EXC_ERROR : ' );
137 END IF;
138 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
139 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
140 IF (l_debug = 1) THEN
141 print_debug('proc_update_mmtt .. EXCEP G_EXC_UNEXPECTED_ERROR : ' );
142 END IF;
143 WHEN OTHERS THEN
144 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
145 IF (l_debug = 1) THEN
146 print_debug('proc_update_mmtt .. UNEXCEP OTHERS : ' );
147 END IF;
148 END proc_update_mmtt ;
149
150 PROCEDURE proc_update_msnt(p_rowid IN ROWID,
151 p_new_transaction_temp_id IN NUMBER,
152 p_from_serial_number IN VARCHAR2,
153 p_to_serial_number IN VARCHAR2,
154 x_return_status OUT NOCOPY VARCHAR2)
155 IS
156 x_valid VARCHAR2(1) := 'Y';
157 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
158 BEGIN
159 IF (l_debug = 1) THEN
160 print_debug('in proc_update_msnt .. p_rowid: '|| p_rowid );
161 print_debug('in proc_update_msnt .. p_new_transaction_temp_id: '|| p_new_transaction_temp_id );
162 print_debug('in proc_update_msnt .. p_from_serial_number: '|| p_from_serial_number);
163 print_debug('in proc_update_msnt .. p_to_serial_number: '|| p_to_serial_number);
164 END IF;
165
166 x_return_status := fnd_api.g_ret_sts_success;
167
168 UPDATE mtl_serial_numbers_temp
169 SET
170 transaction_temp_id = p_new_transaction_temp_id,
171 fm_serial_number = p_from_serial_number,
172 to_serial_number = p_to_serial_number
173 WHERE ROWID = p_rowid;
174
175 IF (SQL%NOTFOUND) THEN
176 x_return_status := FND_API.G_RET_STS_ERROR ;
177 fnd_message.set_name('INV', 'INV_UPDATE_ERROR');
178 fnd_message.set_token('ENTITY1', 'MTL_SERIAL_NUMBERS_TEMP');
179 -- MESSAGE_TEXT = "Error Updating ENTITY1 "
180 fnd_msg_pub.add;
181 IF (l_debug = 1) THEN
182 print_debug('proc_update_msnt .. nodatafound OTHERS : ');
183 END IF;
184 END IF;
185
186 EXCEPTION
187 WHEN OTHERS THEN
188 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
189 IF (l_debug = 1) THEN
190 print_debug('proc_update_msnt .. UNEXCEP OTHERS : ');
191 END IF;
192 END proc_update_msnt;
193
194 PROCEDURE proc_update_mtlt(p_rowid IN ROWID,
195 p_new_transaction_temp_id IN NUMBER,
196 p_lot_number IN VARCHAR2,
197 p_primary_quantity IN NUMBER,
198 p_transaction_quantity IN NUMBER,
199 p_new_serial_trx_temp_id IN NUMBER,
200 x_return_status OUT NOCOPY VARCHAR2)
201 IS
202 x_valid VARCHAR2(1) := 'Y';
203 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
204 BEGIN
205 IF (l_debug = 1) THEN
206 print_debug('in proc_update_mtlt .. p_rowid: '|| p_rowid);
207 print_debug('in proc_update_mtlt .. p_new_transaction_temp_id: '|| p_new_transaction_temp_id );
208 print_debug('in proc_update_mtlt .. p_lot_number: '|| p_lot_number);
209 print_debug('in proc_update_mtlt .. p_primary_quantity: '|| p_primary_quantity);
210 print_debug('in proc_update_mtlt .. p_transaction_quantity: '|| p_transaction_quantity);
211 print_debug('in proc_update_mtlt .. p_new_serial_trx_temp_id: '|| p_new_serial_trx_temp_id);
212 END IF;
213
214 x_return_status := fnd_api.g_ret_sts_success;
215
216 UPDATE mtl_transaction_lots_temp
217 SET
218 transaction_temp_id = p_new_transaction_temp_id,
219 lot_number = Nvl(p_lot_number, lot_number),
220 primary_quantity = Nvl(p_primary_quantity, primary_quantity),
221 transaction_quantity = Nvl(p_transaction_quantity, transaction_quantity),
222 serial_transaction_temp_id = p_new_serial_trx_temp_id
223 WHERE ROWID = p_rowid;
224
225 IF (SQL%NOTFOUND) THEN
226 x_return_status := FND_API.G_RET_STS_ERROR ;
227 fnd_message.set_name('INV', 'INV_UPDATE_ERROR');
228 fnd_message.set_token('ENTITY1', 'MTL_LOT_NUMBERS_TEMP');
229 -- MESSAGE_TEXT = "Error Updating ENTITY1 "
230 fnd_msg_pub.add;
231 IF (l_debug = 1) THEN
232 print_debug('proc_update_mtlt .. nodatafound OTHERS : ');
233 END IF;
234 END IF;
235
236 EXCEPTION
237 WHEN OTHERS THEN
238 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
239 IF (l_debug = 1) THEN
240 print_debug('proc_update_mtlt .. UNEXCEP OTHERS : ');
241 END IF;
242 END proc_update_mtlt;
243
244 FUNCTION onhand_quantity_exists(p_inventory_item_id IN NUMBER,
245 p_revision IN VARCHAR2,
246 p_organization_id IN NUMBER,
247 p_subinventory_code IN VARCHAR2,
248 p_locator_id IN NUMBER,
249 p_lot_number IN VARCHAR2,
250 p_serial_number IN VARCHAR2,
251 p_lpn_id IN NUMBER)
252 RETURN BOOLEAN
253 IS
254 l_onhand NUMBER := 0;
255 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
256 BEGIN
257 BEGIN
258 IF p_lpn_id IS NULL THEN
259 IF p_serial_number IS NULL THEN
260 SELECT 1 INTO l_onhand FROM dual
261 WHERE exists
262 (SELECT organization_id
263 FROM mtl_onhand_quantities_detail moq
264 WHERE (moq.lot_number = p_lot_number
265 OR (p_lot_number IS NULL AND moq.lot_number IS NULL))
266 AND (moq.revision = p_revision
267 OR (p_revision IS NULL AND moq.revision IS NULL))
268 AND moq.inventory_item_id = p_inventory_item_id
269 AND Nvl(moq.locator_id, -1) = Nvl(p_locator_id, -1)
270 AND moq.subinventory_code = p_subinventory_code
271 AND moq.organization_id = p_organization_id
272 AND Nvl(moq.containerized_flag, 2) = 2); -- Loose Items only
273
274 ELSE
275 SELECT 1 INTO l_onhand FROM dual
276 WHERE exists
277 (SELECT current_organization_id
278 FROM mtl_serial_numbers msn
279 WHERE (msn.lot_number = p_lot_number
280 OR (p_lot_number IS NULL AND msn.lot_number IS NULL))
281 AND (msn.revision = p_revision
282 OR (p_revision IS NULL AND msn.revision IS NULL))
283 AND msn.inventory_item_id = p_inventory_item_id
284 AND Nvl(msn.current_locator_id, -1) = Nvl(p_locator_id, -1)
285 AND msn.current_subinventory_code = p_subinventory_code
286 AND msn.lpn_id IS NULL
287 AND msn.current_status = 3
288 AND msn.serial_number = p_serial_number
289 AND msn.current_organization_id = p_organization_id);
290 END IF;
291 ELSE
292 IF p_serial_number IS NULL THEN
293 SELECT 1 INTO l_onhand FROM dual
294 WHERE exists
295 (SELECT wlpn.organization_id
296 FROM wms_lpn_contents wlc, wms_license_plate_numbers wlpn
297 WHERE (wlc.lot_number = p_lot_number
298 OR (p_lot_number IS NULL AND wlc.lot_number IS NULL))
299 AND (wlc.revision = p_revision
300 OR (p_revision IS NULL AND wlc.revision IS NULL))
301 AND wlc.inventory_item_id = p_inventory_item_id
302 AND Nvl(wlpn.locator_id, -1) = Nvl(p_locator_id, -1)
303 AND wlpn.subinventory_code = p_subinventory_code
304 AND wlpn.lpn_context IN (1,11) -- onhand, picked
305 AND wlc.parent_lpn_id = wlpn.lpn_id
306 AND wlc.organization_id = p_organization_id
307 AND wlc.parent_lpn_id = p_lpn_id);
308 ELSE
309 SELECT 1 INTO l_onhand FROM dual
310 WHERE exists
311 (SELECT current_organization_id
312 FROM mtl_serial_numbers msn
313 WHERE (msn.lot_number = p_lot_number
314 OR (p_lot_number IS NULL AND msn.lot_number IS NULL))
315 AND (msn.revision = p_revision
316 OR (p_revision IS NULL AND msn.revision IS NULL))
317 AND msn.lpn_id = p_lpn_id
318 AND msn.current_status = 3
319 AND msn.inventory_item_id = p_inventory_item_id
320 AND Nvl(msn.current_locator_id, -1) = Nvl(p_locator_id, -1)
321 AND msn.current_subinventory_code = p_subinventory_code
322 AND msn.serial_number = p_serial_number
323 AND msn.current_organization_id = p_organization_id);
324 END IF;
325 END IF;
326
327 EXCEPTION
328 WHEN no_data_found THEN
329 l_onhand := 0;
330 WHEN OTHERS THEN
331 RAISE;
332 END;
333 IF l_onhand = 0 THEN
334 RETURN FALSE;
335 ELSIF l_onhand = 1 THEN
336 RETURN TRUE;
337 END IF;
338 END onhand_quantity_exists;
339
340 function valid_cost_group(p_cost_group_id IN NUMBER,
341 p_organization_id IN NUMBER)
342 RETURN boolean
343 IS
344 l_valid VARCHAR2(1) := NULL;
345 BEGIN
346 l_valid := 'N';
347
348 BEGIN
349 SELECT 'Y' INTO l_valid FROM dual
350 WHERE
351 EXISTS
352 (SELECT ccgA.cost_group_id FROM
353 cst_cost_group_accounts CCGA
354 WHERE
355 ccga.cost_group_id = p_cost_group_id
356 AND ccga.organization_id = p_organization_id);
357 EXCEPTION
358 WHEN no_data_found THEN
359 l_valid := 'N';
360 END;
361
362 IF l_valid = 'Y' THEN
363 print_debug('cost group is valid');
364 RETURN TRUE;
365 ELSE
366 RETURN FALSE;
367 END IF;
368
369 EXCEPTION
370 WHEN OTHERS THEN
371 print_debug('exception in valid_cost_group');
372 RETURN FALSE;
373 END valid_cost_group;
374
375 -- Returns the default cost group from the organization or the subinventory
376 -- depending on whether the organization is standard costed or average costed
377 PROCEDURE proc_get_default_costgroup(p_organization_id IN NUMBER,
378 p_inventory_item_id IN NUMBER,
379 p_subinventory_code IN VARCHAR2,
380 p_locator_id IN NUMBER,
381 p_revision IN VARCHAR2,
382 p_lot_number IN VARCHAR2,
383 p_serial_number IN VARCHAR2,
384 p_lpn_id IN NUMBER,
385 p_transaction_action_id IN NUMBER,
386 p_is_backflush_txn IN BOOLEAN,
387 x_cost_group_id OUT NOCOPY NUMBER,
388 x_return_status OUT NOCOPY VARCHAR2)
389 IS
390 l_primary_cost_method NUMBER;
391 l_negative_balances_allowed NUMBER;
392 l_override_neg_for_backflush NUMBER := 0;
393 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
394
395 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
396 l_msg_data VARCHAR2(255) := NULL;
397 l_msg_count NUMBER;
398 l_cost_group_id NUMBER;
399
400 BEGIN
401 x_return_status := fnd_api.g_ret_sts_success;
402
403 SELECT primary_cost_method, negative_inv_receipt_code
404 INTO l_primary_cost_method, l_negative_balances_allowed
405 FROM mtl_parameters
406 WHERE organization_id = p_organization_id;
407
408 IF (l_debug = 1) THEN
409 print_debug('proc_get_default_costgroup.. l_negative_balances_allowed ' || l_negative_balances_allowed);
410 END IF;
411
412 IF p_is_backflush_txn = TRUE THEN
413 l_override_neg_for_backflush := fnd_profile.value('INV_OVERRIDE_NEG_FOR_BACKFLUSH');
414 END IF;
415
416 IF (l_debug = 1) THEN
417 print_debug('proc_get_default_costgroup.. l_override_neg_for_backflush ' || l_override_neg_for_backflush);
418 END IF;
419
420 IF l_negative_balances_allowed = 1 -- Negative balances are allowed
421 OR l_override_neg_for_backflush = 1 -- Negative balances are allowed for backflush
422 THEN
423
424 BEGIN
425 --2690948 Bug fix
426 IF (l_debug = 1) THEN
427 print_debug('calling inv_user_cost_group.get_cg_for_neg_onhand');
428 END IF;
429 inv_user_cost_group.get_cg_for_neg_onhand
430 (x_return_status => l_return_status,
431 x_msg_count => l_msg_count,
432 x_msg_data => l_msg_data,
433 x_cost_group_id => l_cost_group_id,
434 p_organization_id => p_organization_id,
435 p_inventory_item_id => p_inventory_item_id,
436 p_subinventory_code => p_subinventory_code,
437 p_locator_id => p_locator_id,
438 p_revision => p_revision,
439 p_lot_number => p_lot_number,
440 p_serial_number => p_serial_number,
441 p_transaction_action_id => p_transaction_action_id);
442 EXCEPTION
443 WHEN OTHERS THEN
444 IF (l_debug = 1) THEN
445 print_debug('Exception raised from inv_user_cost_group.get_cg_for_neg_onhand');
446 print_debug(Sqlerrm);
447 END IF;
448 END;
449
450 IF (l_debug = 1) THEN
451 print_debug('get_cg_for_neg_onhand ret l_return_status '||l_return_status);
452 print_debug('get_cg_for_neg_onhand ret l_cost_group_id '||l_cost_group_id);
453 END IF;
454
455 IF l_return_status <> fnd_api.g_ret_sts_success THEN
456 IF (l_debug = 1) THEN
457 print_debug('get_cg_for_neg_onhand ret l_msg_data '||l_msg_data);
458 END IF;
459 RAISE FND_API.G_EXC_ERROR;
460
461 ELSIF (l_return_status = fnd_api.g_ret_sts_success) AND
462 (l_cost_group_id IS NOT NULL) THEN
463
464 IF valid_cost_group(p_cost_group_id => l_cost_group_id,
465 p_organization_id => p_organization_id) THEN
466 x_cost_group_id := l_cost_group_id;
467 ELSE
468 IF (l_debug = 1) THEN
469 print_debug('Invalid cost group returned from inv_user_cost_group.get_cg_for_neg_onhand');
470 END IF;
471 RAISE FND_API.G_EXC_ERROR;
472 END IF;
473 --2690948 Bug fix
474
475 ELSE
476
477 IF l_primary_cost_method = 1 THEN -- Standard costed org
478 BEGIN
479 SELECT default_cost_group_id
480 INTO x_cost_group_id
481 FROM mtl_secondary_inventories
482 WHERE organization_id = p_organization_id
483 AND secondary_inventory_name = p_subinventory_code;
484 IF (l_debug = 1) THEN
485 print_debug('proc_get_default_costgroup.. default_sub_cost_group: ' || x_cost_group_id);
486 END IF;
487 EXCEPTION
488 WHEN no_data_found THEN
489 SELECT default_cost_group_id
490 INTO x_cost_group_id
491 FROM mtl_parameters
492 WHERE organization_id = p_organization_id;
493 IF (l_debug = 1) THEN
494 print_debug('proc_get_default_costgroup.. default_org_cost_group: ' || x_cost_group_id);
495 END IF;
496 END;
497 ELSE
498 SELECT default_cost_group_id
499 INTO x_cost_group_id
500 FROM mtl_parameters
501 WHERE organization_id = p_organization_id;
502 IF (l_debug = 1) THEN
503 print_debug('proc_get_default_costgroup.. default_org_cost_group: ' || x_cost_group_id);
504 END IF;
505 END IF;
506 END IF;
507 ELSIF l_negative_balances_allowed = 2 THEN -- Negative balances are not allowed
508 x_return_status := FND_API.G_RET_STS_ERROR ;
509 fnd_message.set_name('INV', 'INV_ZERO_ONHAND');
510 fnd_msg_pub.add;
511 END IF;
512 EXCEPTION
513 WHEN no_data_found THEN
514 x_return_status := FND_API.G_RET_STS_ERROR ;
515 fnd_message.set_name('INV', 'INV_ZERO_ONHAND');
516 fnd_msg_pub.add;
517 IF (l_debug = 1) THEN
518 print_debug('proc_get_default_costgroup .. no_data_found' );
519 END IF;
520
521 END proc_get_default_costgroup;
522
523 -- Tries to assign the cost group for the material if the material is
524 -- present in MTL_MATERIAL_TRANSACTIONS_TEMP as pending transactions.
525 PROCEDURE proc_get_pending_costgroup(p_organization_id IN NUMBER,
526 p_inventory_item_id IN NUMBER,
527 p_subinventory_code IN VARCHAR2,
528 p_locator_id IN NUMBER,
529 p_revision IN VARCHAR2,
530 p_lot_number IN VARCHAR2,
531 p_serial_number IN VARCHAR2,
532 p_lpn_id IN NUMBER,
533 p_transaction_action_id IN NUMBER,
534 x_cost_group_id OUT NOCOPY NUMBER,
535 x_return_status OUT NOCOPY VARCHAR2)
536 IS
537 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
538 BEGIN
539 IF (l_debug = 1) THEN
540 print_debug('In proc_get_pending_costgroup... ');
541 END IF;
542 x_return_status := fnd_api.g_ret_sts_success;
543
544 IF p_serial_number IS NULL THEN
545 IF (l_debug = 1) THEN
546 print_debug('In proc_get_pending_costgroup...No Control ');
547 END IF;
548 IF p_lot_number IS NULL THEN
549 -- No control
550 IF (l_debug = 1) THEN
551 print_debug('In proc_get_pending_costgroup...No Control ');
552 END IF;
553
554 Select cost_group_id INTO x_cost_group_id FROM (
555 SELECT mmtt.cost_group_id
556 FROM mtl_material_transactions_temp mmtt
557 WHERE mmtt.transfer_organization = p_organization_id
558 AND mmtt.transfer_subinventory = p_subinventory_code
559 AND Nvl(mmtt.transfer_to_location, -1) = Nvl(p_locator_id, -1)
560 AND mmtt.inventory_item_id = p_inventory_item_id
561 AND Nvl(mmtt.lpn_id, -1) = Nvl(p_lpn_id, -1)
562 AND mmtt.transaction_action_id IN (inv_globals.g_action_subxfr,
563 inv_globals.g_action_orgxfr,
564 inv_globals.g_action_stgxfr)
565
566 UNION
567
568 -- No control
569
570 SELECT mmtt.cost_group_id
571 FROM mtl_material_transactions_temp mmtt
572 WHERE mmtt.organization_id = p_organization_id
573 AND mmtt.subinventory_code = p_subinventory_code
574 AND Nvl(mmtt.locator_id, -1) = Nvl(p_locator_id, -1)
575 AND mmtt.inventory_item_id = p_inventory_item_id
576 AND Nvl(mmtt.lpn_id, -1) = Nvl(p_lpn_id, -1)
577 AND mmtt.transaction_action_id IN (inv_globals.g_action_cyclecountadj,
578 inv_globals.g_action_physicalcountadj,
579 inv_globals.g_action_intransitreceipt,
580 inv_globals.g_action_receipt,
581 inv_globals.g_action_assycomplete,
582 /*3199679inv_globals.g_action_assyreturn,*/
583 inv_globals.g_action_inv_lot_split,
584 inv_globals.g_action_inv_lot_merge,
585 inv_globals.g_action_inv_lot_translate))
586 WHERE ROWNUM = 1;
587
588 ELSE
589 IF (l_debug = 1) THEN
590 print_debug('In proc_get_pending_costgroup... Lot Control');
591 END IF;
592 -- Lot control
593 Select cost_group_id INTO x_cost_group_id FROM (
594 SELECT mmtt.cost_group_id
595 FROM mtl_material_transactions_temp mmtt,
596 mtl_transaction_lots_temp mtlt
597 WHERE mmtt.transfer_organization = p_organization_id
598 AND mmtt.transfer_subinventory = p_subinventory_code
599 AND Nvl(mmtt.transfer_to_location, -1) = Nvl(p_locator_id, -1)
600 AND mmtt.inventory_item_id = p_inventory_item_id
601 AND Nvl(mmtt.lpn_id, -1) = Nvl(p_lpn_id, -1)
602 AND mmtt.transaction_action_id IN (inv_globals.g_action_subxfr,
603 inv_globals.g_action_orgxfr,
604 inv_globals.g_action_stgxfr)
605 AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
606 AND mtlt.lot_number = p_lot_number
607
608 UNION
609
610 SELECT mmtt.cost_group_id
611 FROM mtl_material_transactions_temp mmtt,
612 mtl_transaction_lots_temp mtlt
613 WHERE mmtt.organization_id = p_organization_id
614 AND mmtt.subinventory_code = p_subinventory_code
615 AND Nvl(mmtt.locator_id, -1) = Nvl(p_locator_id, -1)
616 AND mmtt.inventory_item_id = p_inventory_item_id
617 AND Nvl(mmtt.lpn_id, -1) = Nvl(p_lpn_id, -1)
618 AND mmtt.transaction_action_id IN (inv_globals.g_action_cyclecountadj,
619 inv_globals.g_action_physicalcountadj,
620 inv_globals.g_action_intransitreceipt,
621 inv_globals.g_action_receipt,
622 inv_globals.g_action_assycomplete,
623 /*3199679 inv_globals.g_action_assyreturn,*/
624 inv_globals.g_action_inv_lot_split,
625 inv_globals.g_action_inv_lot_merge,
626 inv_globals.g_action_inv_lot_translate)
627 AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
628 AND mtlt.lot_number = p_lot_number)
629 WHERE ROWNUM = 1;
630
631 END IF;
632
633 ELSE
634 IF (l_debug = 1) THEN
635 print_debug('In proc_get_pending_costgroup...No ControlSerial Control ');
636 END IF;
637
638 -- Serial control
639 IF p_lot_number IS NULL THEN
640 IF (l_debug = 1) THEN
641 print_debug('In proc_get_pending_costgroup...Serial Control ');
642 END IF;
643 Select cost_group_id INTO x_cost_group_id FROM (
644 SELECT mmtt.cost_group_id
645 FROM mtl_material_transactions_temp mmtt,
646 mtl_serial_numbers_temp msnt
647 WHERE mmtt.transfer_organization = p_organization_id
648 AND mmtt.transfer_subinventory = p_subinventory_code
649 AND Nvl(mmtt.transfer_to_location, -1) = Nvl(p_locator_id, -1)
650 AND mmtt.inventory_item_id = p_inventory_item_id
651 AND Nvl(mmtt.lpn_id, -1) = Nvl(p_lpn_id, -1)
652 AND mmtt.transaction_action_id IN (inv_globals.g_action_subxfr,
653 inv_globals.g_action_orgxfr,
654 inv_globals.g_action_stgxfr)
655 AND mmtt.transaction_temp_id = msnt.transaction_temp_id
656 AND msnt.fm_serial_number <= p_serial_number
657 AND msnt.to_serial_number >= p_serial_number
658
659 UNION
660
661 SELECT mmtt.cost_group_id
662 FROM mtl_material_transactions_temp mmtt,
663 mtl_serial_numbers_temp msnt
664 WHERE mmtt.organization_id = p_organization_id
665 AND mmtt.subinventory_code = p_subinventory_code
666 AND Nvl(mmtt.locator_id, -1) = Nvl(p_locator_id, -1)
667 AND mmtt.inventory_item_id = p_inventory_item_id
668 AND Nvl(mmtt.lpn_id, -1) = Nvl(p_lpn_id, -1)
669 AND mmtt.transaction_action_id IN (inv_globals.g_action_cyclecountadj,
670 inv_globals.g_action_physicalcountadj,
671 inv_globals.g_action_intransitreceipt,
672 inv_globals.g_action_receipt,
673 inv_globals.g_action_assycomplete,
674 /*3199679inv_globals.g_action_assyreturn,*/
675 inv_globals.g_action_inv_lot_split,
676 inv_globals.g_action_inv_lot_merge,
677 inv_globals.g_action_inv_lot_translate)
678 AND mmtt.transaction_temp_id = msnt.transaction_temp_id
679 AND msnt.fm_serial_number <= p_serial_number
680 AND msnt.to_serial_number >= p_serial_number)
681 WHERE ROWNUM = 1;
682
683 ELSE
684 IF (l_debug = 1) THEN
685 print_debug('In proc_get_pending_costgroup...BOTh control ');
686 END IF;
687 -- Lot and serial control
688 Select cost_group_id INTO x_cost_group_id FROM (
689 SELECT mmtt.cost_group_id
690 FROM mtl_material_transactions_temp mmtt,
691 mtl_transaction_lots_temp mtlt,
692 mtl_serial_numbers_temp msnt
693 WHERE mmtt.transfer_organization = p_organization_id
694 AND mmtt.transfer_subinventory = p_subinventory_code
695 AND Nvl(mmtt.transfer_to_location, -1) = Nvl(p_locator_id, -1)
696 AND mmtt.inventory_item_id = p_inventory_item_id
697 AND Nvl(mmtt.lpn_id, -1) = Nvl(p_lpn_id, -1)
698 AND mmtt.transaction_action_id IN (inv_globals.g_action_subxfr,
699 inv_globals.g_action_orgxfr,
700 inv_globals.g_action_stgxfr)
701 AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
702 AND mtlt.lot_number = p_lot_number
703 AND mtlt.transaction_temp_id = msnt.transaction_temp_id
704 AND msnt.fm_serial_number <= p_serial_number
705 AND msnt.to_serial_number >= p_serial_number
706
707 UNION
708
709 SELECT mmtt.cost_group_id
710 FROM mtl_material_transactions_temp mmtt,
711 mtl_transaction_lots_temp mtlt,
712 mtl_serial_numbers_temp msnt
713 WHERE mmtt.organization_id = p_organization_id
714 AND mmtt.subinventory_code = p_subinventory_code
715 AND Nvl(mmtt.locator_id, -1) = Nvl(p_locator_id, -1)
716 AND mmtt.inventory_item_id = p_inventory_item_id
717 AND Nvl(mmtt.lpn_id, -1) = Nvl(p_lpn_id, -1)
718 AND mmtt.transaction_action_id IN (inv_globals.g_action_cyclecountadj,
719 inv_globals.g_action_physicalcountadj,
720 inv_globals.g_action_intransitreceipt,
721 inv_globals.g_action_receipt,
722 inv_globals.g_action_assycomplete,
723 /*3199679inv_globals.g_action_assyreturn,*/
724 inv_globals.g_action_inv_lot_split,
725 inv_globals.g_action_inv_lot_merge,
726 inv_globals.g_action_inv_lot_translate)
727 AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
728 AND mtlt.lot_number = p_lot_number
729 AND mtlt.transaction_temp_id = msnt.transaction_temp_id
730 AND msnt.fm_serial_number <= p_serial_number
731 AND msnt.to_serial_number >= p_serial_number)
732 WHERE ROWNUM = 1;
733
734 END IF;
735
736 END IF;
737 /* BUG 3777187
738 --Bug 2844271 fix
739 IF x_cost_group_id IS NULL
740 OR x_cost_group_id = 0 THEN
741 IF (l_debug = 1) THEN
742 print_debug('proc_get_pendingcostgroup...cg null for pending txn '||
743 'org '||p_organization_id||
744 'item '||p_inventory_item_id||
745 'sub '||p_subinventory_code||
746 'loc '||p_locator_id||
747 'rev '||p_revision||
748 'lot '||p_lot_number||
749 'ser '||p_serial_number);
750
751 print_debug('proc_get_pendingcostgroup...returning failure with CG:'||x_cost_group_id);
752 END IF;
753 fnd_message.set_name('INV','INV_PENDING_CG_NULL');
754 fnd_message.set_token('ORG',p_organization_id);
755 fnd_message.set_token('ITEM',p_inventory_item_id);
756 fnd_msg_pub.add;
757 x_return_status := g_corrupt_cg_error;
758 --Bug 2844271 fix
759 ELSE */
760 /*BUG 3777187 Pending transactions may not have cost_group_id always so x_cost_group_id might be null. In this case we need to
761 call the proc_get_default_costgroup api so that to get cost_group from sub/org or from user defined api */
762 IF x_cost_group_id IS NULL
763 OR x_cost_group_id = 0 THEN
764 IF (l_debug = 1) THEN
765 print_debug('proc_get_pendingcostgroup...cg is null or cg is zero for pending txn :x_cost_group_id' || x_cost_group_id);
766 print_debug('returning error to call proc_get_default_costgroup() api..');
767 END IF;
768 x_return_status := FND_API.G_RET_STS_ERROR ; --8715706
769 ELSE
770 IF (l_debug = 1) THEN
771 print_debug('proc_get_pendingcostgroup... Returning success with CG:'|| x_cost_group_id);
772 END IF;
773 x_return_status := FND_API.g_ret_sts_success;
774 END IF;
775
776 EXCEPTION
777 WHEN no_data_found THEN
778 x_return_status := FND_API.G_RET_STS_ERROR ;
779 fnd_message.set_name('INV', 'INV_ZERO_ONHAND');
780 fnd_msg_pub.add;
781 IF (l_debug = 1) THEN
782 print_debug('proc_get_pending_costgroup .. no_data_found' );
783 END IF;
784 WHEN OTHERS THEN
785 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
786 IF (l_debug = 1) THEN
787 print_debug('proc_get_pending_costgroup .. UNEXCEP OTHERS : ' );
788 END IF;
789 END proc_get_pending_costgroup;
790
791 -- Gets the current cost group for the material given parameters
792 -- First checks the mtl_onhand_quantities for onhand inventory and then the
793 -- mtl_material_transactions_temp for any pending transactions.
794 PROCEDURE proc_get_costgroup(p_organization_id IN NUMBER,
795 p_inventory_item_id IN NUMBER,
796 p_subinventory_code IN VARCHAR2,
797 p_locator_id IN NUMBER,
798 p_revision IN VARCHAR2,
799 p_lot_number IN VARCHAR2,
800 p_serial_number IN VARCHAR2,
801 p_containerized_flag IN NUMBER,
802 p_lpn_id IN NUMBER,
803 p_transaction_action_id IN NUMBER,
804 x_cost_group_id OUT NOCOPY NUMBER,
805 x_return_status OUT NOCOPY VARCHAR2)
806 IS
807 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
808 BEGIN
809 IF (l_debug = 1) THEN
810 print_debug( 'In proc_get_costgroup... ');
811 print_debug('p_organization_id'||p_organization_id);
812 print_debug('p_inventory_item_id'||p_inventory_item_id);
813 print_debug('p_subinventory_code'||p_subinventory_code);
814 print_debug('p_locator_id'||p_locator_id);
815 print_debug('p_revision'||p_revision);
816 print_debug('p_lot_number'||p_lot_number);
817 print_debug('p_serial_number'||p_serial_number);
818 print_debug('p_containerized_flag'||p_containerized_flag);
819 print_debug('p_lpn_id'||p_lpn_id);
820 print_debug('p_transaction_action_id'||p_transaction_action_id);
821 END IF;
822
823 x_return_status := fnd_api.g_ret_sts_success;
824 IF p_lpn_id IS NULL THEN
825 IF p_serial_number IS NULL THEN
826 SELECT moq.cost_group_id -- Loose material, no serial control
827 INTO x_cost_group_id
828 FROM mtl_onhand_quantities_detail moq
829 WHERE (moq.lot_number = p_lot_number
830 OR (p_lot_number IS NULL AND moq.lot_number IS NULL))
831 AND (moq.revision = p_revision
832 OR (p_revision IS NULL AND moq.revision IS NULL))
833 AND moq.inventory_item_id = p_inventory_item_id
834 AND nvl(moq.locator_id, -1) = Nvl(p_locator_id, -1)
835 AND moq.subinventory_code = p_subinventory_code
836 AND moq.organization_id = p_organization_id
837 AND Nvl(moq.containerized_flag, 2) = 2 -- Loose Items only
838 AND ROWNUM = 1;
839 ELSE
840 SELECT msn.cost_group_id -- Loose material, serial control
841 INTO x_cost_group_id
842 FROM mtl_serial_numbers msn
843 WHERE (msn.lot_number = p_lot_number
844 OR (p_lot_number IS NULL AND msn.lot_number IS NULL))
845 AND (msn.revision = p_revision
846 OR (p_revision IS NULL AND msn.revision IS NULL))
847 AND msn.inventory_item_id = p_inventory_item_id
848 AND Nvl(msn.current_locator_id, -1) = Nvl(p_locator_id, -1)
849 AND msn.current_subinventory_code = p_subinventory_code
850 AND msn.current_status = 3
851 AND msn.serial_number = p_serial_number
852 AND msn.current_organization_id = p_organization_id
853 AND ROWNUM = 1;
854 END IF;
855 ELSE
856 IF p_serial_number IS NULL THEN
857 -- Packed material, no serial control
858 SELECT cost_group_id INTO x_cost_group_id
859 FROM (
860 SELECT wlc.cost_group_id
861 FROM wms_lpn_contents wlc,
862 wms_license_plate_numbers wlpn
863 WHERE (wlc.lot_number = p_lot_number
864 OR (p_lot_number IS NULL AND wlc.lot_number IS NULL))
865 AND (wlc.revision = p_revision
866 OR (p_revision IS NULL AND wlc.revision IS NULL))
867 AND wlc.inventory_item_id = p_inventory_item_id
868 AND wlc.parent_lpn_id = wlpn.lpn_id
869 -- Bug 2393441 - During ship confirmation, an LPN may
870 -- have blank sub and loc if some lines belonging to the LPN
871 -- are shipped out. To prevent the API from erroring out the
872 -- following checks are commented out
873 -- AND Nvl(wlpn.locator_id, -1) = nvl(p_locator_id, -1)
874 -- AND wlpn.subinventory_code = p_subinventory_code
875 AND wlpn.organization_id = p_organization_id
876 AND wlpn.lpn_id = p_lpn_id
877 AND ROWNUM = 1
878 UNION --Bug#6133411.Added the UNION and outer SELECT as well.
879 SELECT moq.cost_group_id
880 FROM mtl_onhand_quantities_detail moq
881 WHERE (moq.lot_number = p_lot_number
882 OR (p_lot_number IS NULL AND moq.lot_number IS NULL))
883 AND (moq.revision = p_revision
884 OR (p_revision IS NULL AND moq.revision IS NULL))
885 AND moq.inventory_item_id = p_inventory_item_id
886 AND moq.locator_id = p_locator_id
887 AND moq.subinventory_code = p_subinventory_code
888 AND moq.organization_id = p_organization_id
889 AND moq.containerized_flag = 1
890 AND moq.lpn_id = p_lpn_id
891 AND ROWNUM < 2 )
892 WHERE ROWNUM < 2 ;
893 ELSE
894 SELECT msn.cost_group_id -- Packed material, serial control
895 INTO x_cost_group_id
896 FROM mtl_serial_numbers msn
897 WHERE (msn.lot_number = p_lot_number
898 OR (p_lot_number IS NULL AND msn.lot_number IS NULL))
899 AND (msn.revision = p_revision
900 OR (p_revision IS NULL AND msn.revision IS NULL))
901 AND msn.lpn_id = p_lpn_id
902 AND msn.current_status = 3
903 AND msn.inventory_item_id = p_inventory_item_id
904 AND Nvl(msn.current_locator_id, -1) = Nvl(p_locator_id, -1)
905 AND msn.current_subinventory_code = p_subinventory_code
906 AND msn.serial_number = p_serial_number
907 AND msn.current_organization_id = p_organization_id
908 AND ROWNUM = 1;
909 END IF;
910 END IF;
911
912 --Bug 2844271 fix
913 IF x_cost_group_id IS NULL
914 OR x_cost_group_id <= 0 THEN
915 IF (l_debug = 1) THEN
916 print_debug('proc_get_costgroup...onhand cg null or 0 for '||
917 'org '||p_organization_id||
918 'item '||p_inventory_item_id||
919 'sub '||p_subinventory_code||
920 'loc '||p_locator_id||
921 'rev '||p_revision||
922 'lot '||p_lot_number||
923 'ser '||p_serial_number);
924
925 print_debug('proc_get_costgroup...returning failure with CG:'||x_cost_group_id);
926 END IF;
927 fnd_message.set_name('INV','INV_ONHAND_CG_NULL');
928 fnd_message.set_token('ORG',p_organization_id);
929 fnd_message.set_token('ITEM',p_inventory_item_id);
930 fnd_msg_pub.add;
931 x_return_status := g_corrupt_cg_error;
932 --Bug 2844271 fix
933 ELSE
934 IF (l_debug = 1) THEN
935 print_debug('proc_get_costgroup... Returning success with CG:'|| x_cost_group_id);
936 END IF;
937 x_return_status := FND_API.g_ret_sts_success;
938 END IF;
939
940 EXCEPTION
941 WHEN FND_API.G_EXC_ERROR THEN
942 IF (l_debug = 1) THEN
943 print_debug('proc_get_costgroup .. EXCEP G_EXC_ERROR : ' );
944 END IF;
945 x_return_status := FND_API.G_RET_STS_ERROR ;
946 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
947 IF (l_debug = 1) THEN
948 print_debug('proc_get_costgroup .. EXCEP G_EXC_UNEXPECTED_ERROR : ' );
949 END IF;
950 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
951 WHEN no_data_found THEN
952 IF (l_debug = 1) THEN
953 print_debug('proc_get_costgroup .. No data found in MOQ, checking MMTT' );
954 END IF;
955 -- Check if there is any material in the pending transactions
956 proc_get_pending_costgroup(p_organization_id => p_organization_id,
957 p_inventory_item_id => p_inventory_item_id,
958 p_subinventory_code => p_subinventory_code,
959 p_locator_id => p_locator_id,
960 p_revision => p_revision,
961 p_lot_number => p_lot_number,
962 p_serial_number => p_serial_number,
963 p_lpn_id => p_lpn_id,
964 p_transaction_action_id => p_transaction_action_id,
965 x_cost_group_id => x_cost_group_id,
966 x_return_status => x_return_status);
967 WHEN OTHERS THEN
968 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
969 IF (l_debug = 1) THEN
970 print_debug('proc_get_costgroup .. UNEXCEP OTHERS : ' );
971 END IF;
972
973 END proc_get_costgroup;
974
975 -- Gets the current cost group for the material given parameters
976 -- First checks the mtl_onhand_quantities for onhand inventory and then the
977 -- mtl_material_transactions_temp for any pending transactions. If no
978 -- entries are found there then it checks if negative onhand balances are
979 -- allowed. If negative balances are allowed then it assigns the default
980 -- cost group of the subinventory or the organization.
981 PROCEDURE proc_determine_costgroup(p_organization_id IN NUMBER,
982 p_inventory_item_id IN NUMBER,
983 p_subinventory_code IN VARCHAR2,
984 p_locator_id IN NUMBER,
985 p_revision IN VARCHAR2,
986 p_lot_number IN VARCHAR2,
987 p_serial_number IN VARCHAR2,
988 p_containerized_flag IN NUMBER,
989 p_lpn_id IN NUMBER,
990 p_transaction_action_id IN NUMBER,
991 p_is_backflush_txn IN BOOLEAN,
992 x_cost_group_id OUT NOCOPY NUMBER,
993 x_return_status OUT NOCOPY VARCHAR2)
994 IS
995 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
996 BEGIN
997 proc_get_costgroup(p_organization_id => p_organization_id,
998 p_inventory_item_id => p_inventory_item_id,
999 p_subinventory_code => p_subinventory_code,
1000 p_locator_id => p_locator_id,
1001 p_revision => p_revision,
1002 p_lot_number => p_lot_number,
1003 p_serial_number => p_serial_number,
1004 p_containerized_flag => p_containerized_flag,
1005 p_lpn_id => p_lpn_id,
1006 p_transaction_action_id => p_transaction_action_id,
1007 x_cost_group_id => x_cost_group_id,
1008 x_return_status => x_return_status);
1009 IF (l_debug = 1) THEN
1010 print_debug('proc_get_costgroup returned ' || x_return_status);
1011 END IF;
1012
1013 IF x_return_status = g_corrupt_cg_error THEN
1014 x_return_status := fnd_api.g_ret_sts_error;
1015 ELSIF x_return_status <> fnd_api.g_ret_sts_success THEN
1016 proc_get_default_costgroup(p_organization_id => p_organization_id,
1017 p_inventory_item_id => p_inventory_item_id,
1018 p_subinventory_code => p_subinventory_code,
1019 p_locator_id => p_locator_id,
1020 p_revision => p_revision,
1021 p_lot_number => p_lot_number,
1022 p_serial_number => p_serial_number,
1023 p_lpn_id => p_lpn_id,
1024 p_transaction_action_id => p_transaction_action_id,
1025 p_is_backflush_txn => p_is_backflush_txn,
1026 x_cost_group_id => x_cost_group_id,
1027 x_return_status => x_return_status);
1028 IF (l_debug = 1) THEN
1029 print_debug('proc_get_default_costgroup returned ' || x_return_status);
1030 END IF;
1031 END IF;
1032
1033 END;
1034
1035 PROCEDURE proc_insert_msnt(p_msnt_rec IN cur_msnt%ROWTYPE,
1036 p_from_serial_number IN VARCHAR2,
1037 p_to_serial_number IN VARCHAR2,
1038 p_new_txn_temp_id IN NUMBER,
1039 x_return_status OUT NOCOPY VARCHAR2)
1040 IS
1041 l_api_name CONSTANT VARCHAR2(100) := 'proc_insert_msnt';
1042 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1043 BEGIN
1044 x_return_status := fnd_api.g_ret_sts_success;
1045 SAVEPOINT sp_proc_insert_msnt;
1046 IF (l_debug = 1) THEN
1047 print_debug('proc_insert_msnt.. FSN: ' || p_from_serial_number);
1048 print_debug('proc_insert_msnt.. TSN: ' || p_to_serial_number);
1049 print_debug('proc_insert_msnt.. Txn temp id: ' || p_new_txn_temp_id);
1050 END IF;
1051
1052 INSERT INTO mtl_serial_numbers_temp
1053 (TRANSACTION_TEMP_ID
1054 ,LAST_UPDATE_DATE
1055 ,LAST_UPDATED_BY
1056 ,CREATION_DATE
1057 ,CREATED_BY
1058 ,LAST_UPDATE_LOGIN
1059 ,REQUEST_ID
1060 ,PROGRAM_APPLICATION_ID
1061 ,PROGRAM_ID
1062 ,PROGRAM_UPDATE_DATE
1063 ,VENDOR_SERIAL_NUMBER
1064 ,VENDOR_LOT_NUMBER
1065 ,FM_SERIAL_NUMBER
1066 ,TO_SERIAL_NUMBER
1067 ,SERIAL_PREFIX
1068 ,ERROR_CODE
1069 ,PARENT_SERIAL_NUMBER
1070 ,GROUP_HEADER_ID
1071 ,END_ITEM_UNIT_NUMBER
1072 ,SERIAL_ATTRIBUTE_CATEGORY
1073 ,TERRITORY_CODE
1074 ,ORIGINATION_DATE
1075 ,C_ATTRIBUTE1
1076 ,C_ATTRIBUTE2
1077 ,C_ATTRIBUTE3
1078 ,C_ATTRIBUTE4
1079 ,C_ATTRIBUTE5
1080 ,C_ATTRIBUTE6
1081 ,C_ATTRIBUTE7
1082 ,C_ATTRIBUTE8
1083 ,C_ATTRIBUTE9
1084 ,C_ATTRIBUTE10
1085 ,C_ATTRIBUTE11
1086 ,C_ATTRIBUTE12
1087 ,C_ATTRIBUTE13
1088 ,C_ATTRIBUTE14
1089 ,C_ATTRIBUTE15
1090 ,C_ATTRIBUTE16
1091 ,C_ATTRIBUTE17
1092 ,C_ATTRIBUTE18
1093 ,C_ATTRIBUTE19
1094 ,C_ATTRIBUTE20
1095 ,D_ATTRIBUTE1
1096 ,D_ATTRIBUTE2
1097 ,D_ATTRIBUTE3
1098 ,D_ATTRIBUTE4
1099 ,D_ATTRIBUTE5
1100 ,D_ATTRIBUTE6
1101 ,D_ATTRIBUTE7
1102 ,D_ATTRIBUTE8
1103 ,D_ATTRIBUTE9
1104 ,D_ATTRIBUTE10
1105 ,N_ATTRIBUTE1
1106 ,N_ATTRIBUTE2
1107 ,N_ATTRIBUTE3
1108 ,N_ATTRIBUTE4
1109 ,N_ATTRIBUTE5
1110 ,N_ATTRIBUTE6
1111 ,N_ATTRIBUTE7
1112 ,N_ATTRIBUTE8
1113 ,N_ATTRIBUTE9
1114 ,N_ATTRIBUTE10
1115 ,STATUS_ID
1116 ,TIME_SINCE_NEW
1117 ,CYCLES_SINCE_NEW
1118 ,TIME_SINCE_OVERHAUL
1119 ,CYCLES_SINCE_OVERHAUL
1120 ,TIME_SINCE_REPAIR
1121 ,CYCLES_SINCE_REPAIR
1122 ,TIME_SINCE_VISIT
1123 ,CYCLES_SINCE_VISIT
1124 ,TIME_SINCE_MARK
1125 ,CYCLES_SINCE_MARK
1126 ,NUMBER_OF_REPAIRS
1127 ,OBJECT_TYPE2 -- R12 Genealogy Enhancements
1128 ,OBJECT_NUMBER2 -- R12 Genealogy Enhancements
1129 ,PARENT_OBJECT_TYPE -- R12 Genealogy Enhancements
1130 ,PARENT_OBJECT_ID -- R12 Genealogy Enhancements
1131 ,PARENT_OBJECT_NUMBER -- R12 Genealogy Enhancements
1132 ,PARENT_ITEM_ID -- R12 Genealogy Enhancements
1133 ,PARENT_OBJECT_TYPE2 -- R12 Genealogy Enhancements
1134 ,PARENT_OBJECT_ID2 -- R12 Genealogy Enhancements
1135 ,PARENT_OBJECT_NUMBER2) -- R12 Genealogy Enhancements
1136 VALUES (p_new_txn_temp_id
1137 ,p_msnt_rec.LAST_UPDATE_DATE
1138 ,p_msnt_rec.LAST_UPDATED_BY
1139 ,p_msnt_rec.CREATION_DATE
1140 ,p_msnt_rec.CREATED_BY
1141 ,p_msnt_rec.LAST_UPDATE_LOGIN
1142 ,p_msnt_rec.REQUEST_ID
1143 ,p_msnt_rec.PROGRAM_APPLICATION_ID
1144 ,p_msnt_rec.PROGRAM_ID
1145 ,p_msnt_rec.PROGRAM_UPDATE_DATE
1146 ,p_msnt_rec.VENDOR_SERIAL_NUMBER
1147 ,p_msnt_rec.VENDOR_LOT_NUMBER
1148 ,p_from_serial_number
1149 ,p_to_serial_number
1150 ,p_msnt_rec.SERIAL_PREFIX
1151 ,p_msnt_rec.ERROR_CODE
1152 ,p_msnt_rec.PARENT_SERIAL_NUMBER
1153 ,p_msnt_rec.GROUP_HEADER_ID
1154 ,p_msnt_rec.END_ITEM_UNIT_NUMBER
1155 ,p_msnt_rec.SERIAL_ATTRIBUTE_CATEGORY
1156 ,p_msnt_rec.TERRITORY_CODE
1157 ,p_msnt_rec.ORIGINATION_DATE
1158 ,p_msnt_rec.C_ATTRIBUTE1
1159 ,p_msnt_rec.C_ATTRIBUTE2
1160 ,p_msnt_rec.C_ATTRIBUTE3
1161 ,p_msnt_rec.C_ATTRIBUTE4
1162 ,p_msnt_rec.C_ATTRIBUTE5
1163 ,p_msnt_rec.C_ATTRIBUTE6
1164 ,p_msnt_rec.C_ATTRIBUTE7
1165 ,p_msnt_rec.C_ATTRIBUTE8
1166 ,p_msnt_rec.C_ATTRIBUTE9
1167 ,p_msnt_rec.C_ATTRIBUTE10
1168 ,p_msnt_rec.C_ATTRIBUTE11
1169 ,p_msnt_rec.C_ATTRIBUTE12
1170 ,p_msnt_rec.C_ATTRIBUTE13
1171 ,p_msnt_rec.C_ATTRIBUTE14
1172 ,p_msnt_rec.C_ATTRIBUTE15
1173 ,p_msnt_rec.C_ATTRIBUTE16
1174 ,p_msnt_rec.C_ATTRIBUTE17
1175 ,p_msnt_rec.C_ATTRIBUTE18
1176 ,p_msnt_rec.C_ATTRIBUTE19
1177 ,p_msnt_rec.C_ATTRIBUTE20
1178 ,p_msnt_rec.D_ATTRIBUTE1
1179 ,p_msnt_rec.D_ATTRIBUTE2
1180 ,p_msnt_rec.D_ATTRIBUTE3
1181 ,p_msnt_rec.D_ATTRIBUTE4
1182 ,p_msnt_rec.D_ATTRIBUTE5
1183 ,p_msnt_rec.D_ATTRIBUTE6
1184 ,p_msnt_rec.D_ATTRIBUTE7
1185 ,p_msnt_rec.D_ATTRIBUTE8
1186 ,p_msnt_rec.D_ATTRIBUTE9
1187 ,p_msnt_rec.D_ATTRIBUTE10
1188 ,p_msnt_rec.N_ATTRIBUTE1
1189 ,p_msnt_rec.N_ATTRIBUTE2
1190 ,p_msnt_rec.N_ATTRIBUTE3
1191 ,p_msnt_rec.N_ATTRIBUTE4
1192 ,p_msnt_rec.N_ATTRIBUTE5
1193 ,p_msnt_rec.N_ATTRIBUTE6
1194 ,p_msnt_rec.N_ATTRIBUTE7
1195 ,p_msnt_rec.N_ATTRIBUTE8
1196 ,p_msnt_rec.N_ATTRIBUTE9
1197 ,p_msnt_rec.N_ATTRIBUTE10
1198 ,p_msnt_rec.STATUS_ID
1199 ,p_msnt_rec.TIME_SINCE_NEW
1200 ,p_msnt_rec.CYCLES_SINCE_NEW
1201 ,p_msnt_rec.TIME_SINCE_OVERHAUL
1202 ,p_msnt_rec.CYCLES_SINCE_OVERHAUL
1203 ,p_msnt_rec.TIME_SINCE_REPAIR
1204 ,p_msnt_rec.CYCLES_SINCE_REPAIR
1205 ,p_msnt_rec.TIME_SINCE_VISIT
1206 ,p_msnt_rec.CYCLES_SINCE_VISIT
1207 ,p_msnt_rec.TIME_SINCE_MARK
1208 ,p_msnt_rec.CYCLES_SINCE_MARK
1209 ,p_msnt_rec.number_of_repairs
1210 ,p_msnt_rec.OBJECT_TYPE2 -- R12 Genealogy Enhancements
1211 ,p_msnt_rec.OBJECT_NUMBER2 -- R12 Genealogy Enhancements
1212 ,p_msnt_rec.PARENT_OBJECT_TYPE -- R12 Genealogy Enhancements
1213 ,p_msnt_rec.PARENT_OBJECT_ID -- R12 Genealogy Enhancements
1214 ,p_msnt_rec.PARENT_OBJECT_NUMBER -- R12 Genealogy Enhancements
1215 ,p_msnt_rec.PARENT_ITEM_ID -- R12 Genealogy Enhancements
1216 ,p_msnt_rec.PARENT_OBJECT_TYPE2 -- R12 Genealogy Enhancements
1217 ,p_msnt_rec.PARENT_OBJECT_ID2 -- R12 Genealogy Enhancements
1218 ,p_msnt_rec.PARENT_OBJECT_NUMBER2); -- R12 Genealogy Enhancements
1219 EXCEPTION
1220 WHEN OTHERS THEN
1221 IF (l_debug = 1) THEN
1222 print_debug( 'proc_insert_msnt .. EXCEP others : ' );
1223 END IF;
1224 ROLLBACK TO sp_proc_insert_msnt;
1225 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1226 END proc_insert_msnt;
1227 --
1228 PROCEDURE proc_insert_mtlt (p_mtlt_rec IN cur_mtlt%ROWTYPE,
1229 p_new_txn_temp_id IN NUMBER,
1230 p_prim_qty IN NUMBER,
1231 p_txn_qty IN NUMBER,
1232 p_new_serial_trx_temp_id IN NUMBER,
1233 x_return_status OUT NOCOPY VARCHAR2)
1234 IS
1235 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1236 BEGIN
1237 x_return_status := fnd_api.g_ret_sts_success;
1238 SAVEPOINT sp_proc_insert_mtlt;
1239
1240 IF (l_debug = 1) THEN
1241 print_debug( 'proc_insert_mtlt ..p_new_txn_temp_id :'|| p_new_txn_temp_id );
1242 print_debug( 'proc_insert_mtlt ..p_prim_qty'|| p_prim_qty );
1243 print_debug( 'proc_insert_mtlt ..Lot : '|| p_mtlt_rec.lot_number );
1244 print_debug( 'proc_insert_mtlt ..p_new_serial_trx_temp_id: '|| p_new_serial_trx_temp_id );
1245 print_debug( 'proc_insert_mtlt ..RowId : '|| p_mtlt_rec.mtlt_RowId );
1246 END IF;
1247
1248 INSERT INTO mtl_transaction_lots_temp
1249 ( TRANSACTION_TEMP_ID
1250 ,LAST_UPDATE_DATE
1251 ,LAST_UPDATED_BY
1252 ,CREATION_DATE
1253 ,CREATED_BY
1254 ,LAST_UPDATE_LOGIN
1255 ,REQUEST_ID
1256 ,PROGRAM_APPLICATION_ID
1257 ,PROGRAM_ID
1258 ,PROGRAM_UPDATE_DATE
1259 ,TRANSACTION_QUANTITY
1260 ,PRIMARY_QUANTITY
1261 ,LOT_NUMBER
1262 ,LOT_EXPIRATION_DATE
1263 ,ERROR_CODE
1264 ,SERIAL_TRANSACTION_TEMP_ID
1265 ,GROUP_HEADER_ID
1266 ,PUT_AWAY_RULE_ID
1267 ,PICK_RULE_ID
1268 ,DESCRIPTION
1269 ,VENDOR_ID
1270 ,SUPPLIER_LOT_NUMBER
1271 ,TERRITORY_CODE
1272 ,ORIGINATION_DATE
1273 ,DATE_CODE
1274 ,GRADE_CODE
1275 ,CHANGE_DATE
1276 ,MATURITY_DATE
1277 ,STATUS_ID
1278 ,RETEST_DATE
1279 ,AGE
1280 ,ITEM_SIZE
1281 ,COLOR
1282 ,VOLUME
1283 ,VOLUME_UOM
1284 ,PLACE_OF_ORIGIN
1285 ,BEST_BY_DATE
1286 ,LENGTH
1287 ,LENGTH_UOM
1288 ,RECYCLED_CONTENT
1289 ,THICKNESS
1290 ,THICKNESS_UOM
1291 ,WIDTH
1292 ,WIDTH_UOM
1293 ,CURL_WRINKLE_FOLD
1294 ,LOT_ATTRIBUTE_CATEGORY
1295 ,C_ATTRIBUTE1
1296 ,C_ATTRIBUTE2
1297 ,C_ATTRIBUTE3
1298 ,C_ATTRIBUTE4
1299 ,C_ATTRIBUTE5
1300 ,C_ATTRIBUTE6
1301 ,C_ATTRIBUTE7
1302 ,C_ATTRIBUTE8
1303 ,C_ATTRIBUTE9
1304 ,C_ATTRIBUTE10
1305 ,C_ATTRIBUTE11
1306 ,C_ATTRIBUTE12
1307 ,C_ATTRIBUTE13
1308 ,C_ATTRIBUTE14
1309 ,C_ATTRIBUTE15
1310 ,C_ATTRIBUTE16
1311 ,C_ATTRIBUTE17
1312 ,C_ATTRIBUTE18
1313 ,C_ATTRIBUTE19
1314 ,C_ATTRIBUTE20
1315 ,D_ATTRIBUTE1
1316 ,D_ATTRIBUTE2
1317 ,D_ATTRIBUTE3
1318 ,D_ATTRIBUTE4
1319 ,D_ATTRIBUTE5
1320 ,D_ATTRIBUTE6
1321 ,D_ATTRIBUTE7
1322 ,D_ATTRIBUTE8
1323 ,D_ATTRIBUTE9
1324 ,D_ATTRIBUTE10
1325 ,N_ATTRIBUTE1
1326 ,N_ATTRIBUTE2
1327 ,N_ATTRIBUTE3
1328 ,N_ATTRIBUTE4
1329 ,N_ATTRIBUTE5
1330 ,N_ATTRIBUTE6
1331 ,N_ATTRIBUTE7
1332 ,N_ATTRIBUTE8
1333 ,N_ATTRIBUTE9
1334 ,N_ATTRIBUTE10
1335 ,vendor_name
1336 ,PARENT_OBJECT_TYPE -- R12 Genealogy Enhancements
1337 ,PARENT_OBJECT_ID -- R12 Genealogy Enhancements
1338 ,PARENT_OBJECT_NUMBER -- R12 Genealogy Enhancements
1339 ,PARENT_ITEM_ID -- R12 Genealogy Enhancements
1340 ,PARENT_OBJECT_TYPE2 -- R12 Genealogy Enhancements
1341 ,PARENT_OBJECT_ID2 -- R12 Genealogy Enhancements
1342 ,PARENT_OBJECT_NUMBER2) -- R12 Genealogy Enhancements
1343 VALUES (p_new_txn_temp_id
1344 ,p_mtlt_rec.LAST_UPDATE_DATE
1345 ,p_mtlt_rec.LAST_UPDATED_BY
1346 ,p_mtlt_rec.CREATION_DATE
1347 ,p_mtlt_rec.CREATED_BY
1348 ,p_mtlt_rec.LAST_UPDATE_LOGIN
1349 ,p_mtlt_rec.REQUEST_ID
1350 ,p_mtlt_rec.PROGRAM_APPLICATION_ID
1351 ,p_mtlt_rec.PROGRAM_ID
1352 ,p_mtlt_rec.PROGRAM_UPDATE_DATE
1353 ,p_txn_qty
1354 ,p_prim_qty
1355 ,p_mtlt_rec.LOT_NUMBER
1356 ,p_mtlt_rec.LOT_EXPIRATION_DATE
1357 ,p_mtlt_rec.ERROR_CODE
1358 ,p_new_serial_trx_temp_id
1359 ,p_mtlt_rec.GROUP_HEADER_ID
1360 ,p_mtlt_rec.PUT_AWAY_RULE_ID
1361 ,p_mtlt_rec.PICK_RULE_ID
1362 ,p_mtlt_rec.DESCRIPTION
1363 ,p_mtlt_rec.VENDOR_ID
1364 ,p_mtlt_rec.SUPPLIER_LOT_NUMBER
1365 ,p_mtlt_rec.TERRITORY_CODE
1366 ,p_mtlt_rec.ORIGINATION_DATE
1367 ,p_mtlt_rec.DATE_CODE
1368 ,p_mtlt_rec.GRADE_CODE
1369 ,p_mtlt_rec.CHANGE_DATE
1370 ,p_mtlt_rec.MATURITY_DATE
1371 ,p_mtlt_rec.STATUS_ID
1372 ,p_mtlt_rec.RETEST_DATE
1373 ,p_mtlt_rec.AGE
1374 ,p_mtlt_rec.ITEM_SIZE
1375 ,p_mtlt_rec.COLOR
1376 ,p_mtlt_rec.VOLUME
1377 ,p_mtlt_rec.VOLUME_UOM
1378 ,p_mtlt_rec.PLACE_OF_ORIGIN
1379 ,p_mtlt_rec.BEST_BY_DATE
1380 ,p_mtlt_rec.LENGTH
1381 ,p_mtlt_rec.LENGTH_UOM
1382 ,p_mtlt_rec.RECYCLED_CONTENT
1383 ,p_mtlt_rec.THICKNESS
1384 ,p_mtlt_rec.THICKNESS_UOM
1385 ,p_mtlt_rec.WIDTH
1386 ,p_mtlt_rec.WIDTH_UOM
1387 ,p_mtlt_rec.CURL_WRINKLE_FOLD
1388 ,p_mtlt_rec.LOT_ATTRIBUTE_CATEGORY
1389 ,p_mtlt_rec.C_ATTRIBUTE1
1390 ,p_mtlt_rec.C_ATTRIBUTE2
1391 ,p_mtlt_rec.C_ATTRIBUTE3
1392 ,p_mtlt_rec.C_ATTRIBUTE4
1393 ,p_mtlt_rec.C_ATTRIBUTE5
1394 ,p_mtlt_rec.C_ATTRIBUTE6
1395 ,p_mtlt_rec.C_ATTRIBUTE7
1396 ,p_mtlt_rec.C_ATTRIBUTE8
1397 ,p_mtlt_rec.C_ATTRIBUTE9
1398 ,p_mtlt_rec.C_ATTRIBUTE10
1399 ,p_mtlt_rec.C_ATTRIBUTE11
1400 ,p_mtlt_rec.C_ATTRIBUTE12
1401 ,p_mtlt_rec.C_ATTRIBUTE13
1402 ,p_mtlt_rec.C_ATTRIBUTE14
1403 ,p_mtlt_rec.C_ATTRIBUTE15
1404 ,p_mtlt_rec.C_ATTRIBUTE16
1405 ,p_mtlt_rec.C_ATTRIBUTE17
1406 ,p_mtlt_rec.C_ATTRIBUTE18
1407 ,p_mtlt_rec.C_ATTRIBUTE19
1408 ,p_mtlt_rec.C_ATTRIBUTE20
1409 ,p_mtlt_rec.D_ATTRIBUTE1
1410 ,p_mtlt_rec.D_ATTRIBUTE2
1411 ,p_mtlt_rec.D_ATTRIBUTE3
1412 ,p_mtlt_rec.D_ATTRIBUTE4
1413 ,p_mtlt_rec.D_ATTRIBUTE5
1414 ,p_mtlt_rec.D_ATTRIBUTE6
1415 ,p_mtlt_rec.D_ATTRIBUTE7
1416 ,p_mtlt_rec.D_ATTRIBUTE8
1417 ,p_mtlt_rec.D_ATTRIBUTE9
1418 ,p_mtlt_rec.D_ATTRIBUTE10
1419 ,p_mtlt_rec.N_ATTRIBUTE1
1420 ,p_mtlt_rec.N_ATTRIBUTE2
1421 ,p_mtlt_rec.N_ATTRIBUTE3
1422 ,p_mtlt_rec.N_ATTRIBUTE4
1423 ,p_mtlt_rec.N_ATTRIBUTE5
1424 ,p_mtlt_rec.N_ATTRIBUTE6
1425 ,p_mtlt_rec.N_ATTRIBUTE7
1426 ,p_mtlt_rec.N_ATTRIBUTE8
1427 ,p_mtlt_rec.N_ATTRIBUTE9
1428 ,p_mtlt_rec.N_ATTRIBUTE10
1429 ,p_mtlt_rec.vendor_name
1430 ,p_mtlt_rec.PARENT_OBJECT_TYPE -- R12 Genealogy Enhancements
1431 ,p_mtlt_rec.PARENT_OBJECT_ID -- R12 Genealogy Enhancements
1432 ,p_mtlt_rec.PARENT_OBJECT_NUMBER -- R12 Genealogy Enhancements
1433 ,p_mtlt_rec.PARENT_ITEM_ID -- R12 Genealogy Enhancements
1434 ,p_mtlt_rec.PARENT_OBJECT_TYPE2 -- R12 Genealogy Enhancements
1435 ,p_mtlt_rec.PARENT_OBJECT_ID2 -- R12 Genealogy Enhancements
1436 ,p_mtlt_rec.PARENT_OBJECT_NUMBER2); -- R12 Genealogy Enhancements
1437 EXCEPTION
1438 WHEN OTHERS THEN
1439 IF (l_debug = 1) THEN
1440 print_debug( 'proc_insert_mtlt .. EXCEP others : ' );
1441 END IF;
1442 ROLLBACK TO sp_proc_insert_mtlt;
1443 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1444 END proc_insert_mtlt;
1445 --
1446 PROCEDURE proc_insert_mmtt(p_mmtt_rec IN mtl_material_transactions_temp%ROWTYPE,
1447 p_transfer_wms_org IN BOOLEAN,
1448 p_fob_point IN NUMBER,
1449 p_tfr_primary_cost_method IN NUMBER,
1450 p_tfr_org_cost_group_id IN NUMBER,
1451 p_cost_group_id IN NUMBER,
1452 p_transfer_cost_group_id IN NUMBER,
1453 p_prim_qty IN NUMBER,
1454 p_txn_qty IN NUMBER,
1455 p_new_txn_temp_id IN NUMBER,
1456 p_from_project_id IN NUMBER,
1457 p_to_project_id IN NUMBER,
1458 x_return_status OUT NOCOPY VARCHAR2)
1459 IS
1460 l_transfer_cost_group_id NUMBER := NULL;
1461 x_valid VARCHAR2(1) := 'Y';
1462 l_comingling_occurs VARCHAR2(1) := 'N';
1463 l_msg_data VARCHAR2(255) := NULL;
1464 l_msg_count NUMBER := NULL;
1465
1466 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1467 BEGIN
1468 x_return_status := fnd_api.g_ret_sts_success;
1469 IF (l_debug = 1) THEN
1470 print_debug( 'Begin proc_insert_mmtt: action_id: ' || p_mmtt_rec.transaction_action_id );
1471 END IF;
1472 SAVEPOINT sp_proc_insert_mmtt;
1473 IF p_mmtt_rec.transaction_action_id IN (inv_globals.G_Action_Subxfr,
1474 inv_globals.g_action_stgxfr,
1475 inv_globals.g_action_ownxfr)
1476 -- Subtransfer, staging transfer
1477 THEN
1478 l_transfer_cost_group_id := p_cost_group_id;
1479 ELSE
1480 l_transfer_cost_group_id := p_transfer_cost_group_id;
1481 END IF;
1482
1483 IF p_mmtt_rec.transaction_Action_id = inv_globals.g_action_intransitshipment THEN
1484 IF NOT p_transfer_wms_org AND p_fob_point = 1 THEN -- shipment
1485 -- We don't care about the costing method of the org
1486 l_transfer_cost_group_id := p_tfr_org_cost_group_id;
1487 IF (l_debug = 1) THEN
1488 print_debug('default cost group of org ' || p_mmtt_rec.transfer_organization ||
1489 ' : ' || l_transfer_cost_group_id);
1490 END IF;
1491 ELSIF p_fob_point = 2 THEN -- receipt
1492 l_transfer_cost_group_id := p_cost_group_id;
1493 END IF;
1494 END IF;
1495
1496 IF(p_from_project_id IS NULL AND
1497 p_to_project_id IS NOT NULL AND
1498 p_mmtt_rec.transaction_action_id IN (inv_globals.g_action_subxfr,
1499 inv_globals.g_action_stgxfr,
1500 inv_globals.G_Action_Receipt)) then
1501
1502 IF (l_debug = 1) THEN
1503 print_debug('inserting the transfer_cost_group to null as the dest'||
1504 'locator is proj enabled');
1505 END IF;
1506 l_transfer_cost_group_id := NULL;
1507
1508 END IF;
1509
1510 IF (l_debug = 1) THEN
1511 print_debug( 'Begin proc_insert_mmtt: l_Transfer_cost_group_id: ' || l_Transfer_cost_group_id);
1512 END IF;
1513 INSERT INTO
1514 mtl_material_transactions_temp
1515 (
1516 TRANSACTION_HEADER_ID ,
1517 TRANSACTION_TEMP_ID ,
1518 SOURCE_CODE ,
1519 SOURCE_LINE_ID ,
1520 TRANSACTION_MODE ,
1521 LOCK_FLAG ,
1522 LAST_UPDATE_DATE ,
1523 LAST_UPDATED_BY ,
1524 CREATION_DATE ,
1525 CREATED_BY ,
1526 LAST_UPDATE_LOGIN ,
1527 REQUEST_ID ,
1528 PROGRAM_APPLICATION_ID ,
1529 PROGRAM_ID ,
1530 PROGRAM_UPDATE_DATE ,
1531 INVENTORY_ITEM_ID ,
1532 REVISION ,
1533 ORGANIZATION_ID ,
1534 SUBINVENTORY_CODE ,
1535 LOCATOR_ID ,
1536 TRANSACTION_QUANTITY ,
1537 PRIMARY_QUANTITY ,
1538 TRANSACTION_UOM ,
1539 TRANSACTION_COST ,
1540 TRANSACTION_TYPE_ID ,
1541 TRANSACTION_ACTION_ID ,
1542 TRANSACTION_SOURCE_TYPE_ID ,
1543 TRANSACTION_SOURCE_ID ,
1544 TRANSACTION_SOURCE_NAME ,
1545 TRANSACTION_DATE ,
1546 ACCT_PERIOD_ID ,
1547 DISTRIBUTION_ACCOUNT_ID ,
1548 TRANSACTION_REFERENCE ,
1549 REQUISITION_LINE_ID ,
1550 REQUISITION_DISTRIBUTION_ID ,
1551 REASON_ID ,
1552 LOT_NUMBER ,
1553 LOT_EXPIRATION_DATE ,
1554 SERIAL_NUMBER ,
1555 RECEIVING_DOCUMENT ,
1556 DEMAND_ID ,
1557 RCV_TRANSACTION_ID ,
1558 MOVE_TRANSACTION_ID ,
1559 COMPLETION_TRANSACTION_ID ,
1560 WIP_ENTITY_TYPE ,
1561 SCHEDULE_ID ,
1562 REPETITIVE_LINE_ID ,
1563 EMPLOYEE_CODE ,
1564 PRIMARY_SWITCH ,
1565 SCHEDULE_UPDATE_CODE ,
1566 SETUP_TEARDOWN_CODE ,
1567 ITEM_ORDERING ,
1568 NEGATIVE_REQ_FLAG ,
1569 OPERATION_SEQ_NUM ,
1570 PICKING_LINE_ID ,
1571 TRX_SOURCE_LINE_ID ,
1572 TRX_SOURCE_DELIVERY_ID ,
1573 PHYSICAL_ADJUSTMENT_ID ,
1574 CYCLE_COUNT_ID ,
1575 RMA_LINE_ID ,
1576 CUSTOMER_SHIP_ID ,
1577 CURRENCY_CODE ,
1578 CURRENCY_CONVERSION_RATE ,
1579 CURRENCY_CONVERSION_TYPE ,
1580 CURRENCY_CONVERSION_DATE ,
1581 USSGL_TRANSACTION_CODE ,
1582 VENDOR_LOT_NUMBER ,
1583 ENCUMBRANCE_ACCOUNT ,
1584 ENCUMBRANCE_AMOUNT ,
1585 SHIP_TO_LOCATION ,
1586 SHIPMENT_NUMBER ,
1587 TRANSFER_COST ,
1588 TRANSPORTATION_COST ,
1589 TRANSPORTATION_ACCOUNT ,
1590 FREIGHT_CODE ,
1591 CONTAINERS ,
1592 WAYBILL_AIRBILL ,
1593 EXPECTED_ARRIVAL_DATE ,
1594 TRANSFER_SUBINVENTORY ,
1595 TRANSFER_ORGANIZATION ,
1596 TRANSFER_TO_LOCATION ,
1597 NEW_AVERAGE_COST ,
1598 VALUE_CHANGE ,
1599 PERCENTAGE_CHANGE ,
1600 MATERIAL_ALLOCATION_TEMP_ID ,
1601 DEMAND_SOURCE_HEADER_ID ,
1602 DEMAND_SOURCE_LINE ,
1603 DEMAND_SOURCE_DELIVERY ,
1604 ITEM_SEGMENTS ,
1605 ITEM_DESCRIPTION ,
1606 ITEM_TRX_ENABLED_FLAG ,
1607 ITEM_LOCATION_CONTROL_CODE ,
1608 ITEM_RESTRICT_SUBINV_CODE ,
1609 ITEM_RESTRICT_LOCATORS_CODE ,
1610 ITEM_REVISION_QTY_CONTROL_CODE ,
1611 ITEM_PRIMARY_UOM_CODE ,
1612 ITEM_UOM_CLASS ,
1613 ITEM_SHELF_LIFE_CODE ,
1614 ITEM_SHELF_LIFE_DAYS ,
1615 ITEM_LOT_CONTROL_CODE ,
1616 ITEM_SERIAL_CONTROL_CODE ,
1617 ITEM_INVENTORY_ASSET_FLAG ,
1618 ALLOWED_UNITS_LOOKUP_CODE ,
1619 DEPARTMENT_ID ,
1620 DEPARTMENT_CODE ,
1621 WIP_SUPPLY_TYPE ,
1622 SUPPLY_SUBINVENTORY ,
1623 SUPPLY_LOCATOR_ID ,
1624 VALID_SUBINVENTORY_FLAG ,
1625 VALID_LOCATOR_FLAG ,
1626 LOCATOR_SEGMENTS ,
1627 CURRENT_LOCATOR_CONTROL_CODE ,
1628 NUMBER_OF_LOTS_ENTERED ,
1629 WIP_COMMIT_FLAG ,
1630 NEXT_LOT_NUMBER ,
1631 LOT_ALPHA_PREFIX ,
1632 NEXT_SERIAL_NUMBER ,
1633 SERIAL_ALPHA_PREFIX ,
1634 SHIPPABLE_FLAG ,
1635 POSTING_FLAG ,
1636 REQUIRED_FLAG ,
1637 PROCESS_FLAG ,
1638 ERROR_CODE ,
1639 ERROR_EXPLANATION ,
1640 ATTRIBUTE_CATEGORY ,
1641 ATTRIBUTE1 ,
1642 ATTRIBUTE2 ,
1643 ATTRIBUTE3 ,
1644 ATTRIBUTE4 ,
1645 ATTRIBUTE5 ,
1646 ATTRIBUTE6 ,
1647 ATTRIBUTE7 ,
1648 ATTRIBUTE8 ,
1649 ATTRIBUTE9 ,
1650 ATTRIBUTE10 ,
1651 ATTRIBUTE11 ,
1652 ATTRIBUTE12 ,
1653 ATTRIBUTE13 ,
1654 ATTRIBUTE14 ,
1655 ATTRIBUTE15 ,
1656 MOVEMENT_ID ,
1657 RESERVATION_QUANTITY ,
1658 SHIPPED_QUANTITY ,
1659 TRANSACTION_LINE_NUMBER ,
1660 TASK_ID ,
1661 TO_TASK_ID ,
1662 SOURCE_TASK_ID ,
1663 PROJECT_ID ,
1664 SOURCE_PROJECT_ID ,
1665 PA_EXPENDITURE_ORG_ID ,
1666 TO_PROJECT_ID ,
1667 EXPENDITURE_TYPE ,
1668 FINAL_COMPLETION_FLAG ,
1669 TRANSFER_PERCENTAGE ,
1670 TRANSACTION_SEQUENCE_ID ,
1671 MATERIAL_ACCOUNT ,
1672 MATERIAL_OVERHEAD_ACCOUNT ,
1673 RESOURCE_ACCOUNT ,
1674 OUTSIDE_PROCESSING_ACCOUNT ,
1675 OVERHEAD_ACCOUNT ,
1676 FLOW_SCHEDULE ,
1677 COST_GROUP_ID ,
1678 TRANSFER_COST_GROUP_ID ,
1679 DEMAND_CLASS ,
1680 QA_COLLECTION_ID ,
1681 KANBAN_CARD_ID ,
1682 OVERCOMPLETION_TRANSACTION_QTY ,
1683 OVERCOMPLETION_PRIMARY_QTY ,
1684 OVERCOMPLETION_TRANSACTION_ID ,
1685 END_ITEM_UNIT_NUMBER ,
1686 SCHEDULED_PAYBACK_DATE ,
1687 LINE_TYPE_CODE ,
1688 PARENT_TRANSACTION_TEMP_ID ,
1689 PUT_AWAY_STRATEGY_ID ,
1690 PUT_AWAY_RULE_ID ,
1691 PICK_STRATEGY_ID ,
1692 PICK_RULE_ID ,
1693 MOVE_ORDER_LINE_ID ,
1694 TASK_GROUP_ID ,
1695 PICK_SLIP_NUMBER ,
1696 RESERVATION_ID ,
1697 COMMON_BOM_SEQ_ID ,
1698 COMMON_ROUTING_SEQ_ID ,
1699 ORG_COST_GROUP_ID ,
1700 COST_TYPE_ID ,
1701 TRANSACTION_STATUS ,
1702 STANDARD_OPERATION_ID ,
1703 TASK_PRIORITY ,
1704 WMS_TASK_TYPE ,
1705 PARENT_LINE_ID ,
1706 LPN_ID ,
1707 TRANSFER_LPN_ID ,
1708 WMS_TASK_STATUS ,
1709 CONTENT_LPN_ID ,
1710 CONTAINER_ITEM_ID ,
1711 CARTONIZATION_ID ,
1712 PICK_SLIP_DATE ,
1713 REBUILD_ITEM_ID ,
1714 REBUILD_SERIAL_NUMBER ,
1715 REBUILD_ACTIVITY_ID ,
1716 REBUILD_JOB_NAME ,
1717 ORGANIZATION_TYPE ,
1718 TRANSFER_ORGANIZATION_TYPE ,
1719 OWNING_ORGANIZATION_ID ,
1720 OWNING_TP_TYPE ,
1721 XFR_OWNING_ORGANIZATION_ID ,
1722 TRANSFER_OWNING_TP_TYPE ,
1723 PLANNING_ORGANIZATION_ID ,
1724 PLANNING_TP_TYPE ,
1725 XFR_PLANNING_ORGANIZATION_ID ,
1726 TRANSFER_PLANNING_TP_TYPE ,
1727 SECONDARY_UOM_CODE ,
1728 SECONDARY_TRANSACTION_QUANTITY ,
1729 TRANSACTION_BATCH_ID ,
1730 TRANSACTION_BATCH_SEQ ,
1731 ALLOCATED_LPN_ID ,
1732 SCHEDULE_NUMBER ,
1733 SCHEDULED_FLAG ,
1734 CLASS_CODE ,
1735 SCHEDULE_GROUP ,
1736 BUILD_SEQUENCE ,
1737 BOM_REVISION ,
1738 ROUTING_REVISION ,
1739 BOM_REVISION_DATE ,
1740 ROUTING_REVISION_DATE ,
1741 ALTERNATE_BOM_DESIGNATOR ,
1742 ALTERNATE_ROUTING_DESIGNATOR ,
1743 OPERATION_PLAN_ID ,
1744 fob_point ,
1745 intransit_account ,
1746 relieve_reservations_flag , /*** {{ R12 Enhanced reservations code changes ***/
1747 relieve_high_level_rsv_flag /*** {{ R12 Enhanced reservations code changes ***/
1748 )
1749 values
1750 (p_mmtt_rec.TRANSACTION_HEADER_ID ,
1751 p_new_txn_temp_id ,
1752 p_mmtt_rec.SOURCE_CODE ,
1753 p_mmtt_rec.SOURCE_LINE_ID ,
1754 p_mmtt_rec.TRANSACTION_MODE ,
1755 p_mmtt_rec.LOCK_FLAG ,
1756 p_mmtt_rec.LAST_UPDATE_DATE ,
1757 p_mmtt_rec.LAST_UPDATED_BY ,
1758 p_mmtt_rec.CREATION_DATE ,
1759 p_mmtt_rec.CREATED_BY ,
1760 p_mmtt_rec.LAST_UPDATE_LOGIN ,
1761 p_mmtt_rec.REQUEST_ID ,
1762 p_mmtt_rec.PROGRAM_APPLICATION_ID ,
1763 p_mmtt_rec.PROGRAM_ID ,
1764 p_mmtt_rec.PROGRAM_UPDATE_DATE ,
1765 p_mmtt_rec.INVENTORY_ITEM_ID ,
1766 p_mmtt_rec.REVISION ,
1767 p_mmtt_rec.ORGANIZATION_ID ,
1768 p_mmtt_rec.SUBINVENTORY_CODE ,
1769 p_mmtt_rec.LOCATOR_ID ,
1770 p_txn_qty ,
1771 p_prim_qty ,
1772 p_mmtt_rec.TRANSACTION_UOM ,
1773 p_mmtt_rec.TRANSACTION_COST ,
1774 p_mmtt_rec.TRANSACTION_TYPE_ID ,
1775 p_mmtt_rec.TRANSACTION_ACTION_ID ,
1776 p_mmtt_rec.TRANSACTION_SOURCE_TYPE_ID ,
1777 p_mmtt_rec.TRANSACTION_SOURCE_ID ,
1778 p_mmtt_rec.TRANSACTION_SOURCE_NAME ,
1779 p_mmtt_rec.TRANSACTION_DATE ,
1780 p_mmtt_rec.ACCT_PERIOD_ID ,
1781 p_mmtt_rec.DISTRIBUTION_ACCOUNT_ID ,
1782 p_mmtt_rec.TRANSACTION_REFERENCE ,
1783 p_mmtt_rec.REQUISITION_LINE_ID ,
1784 p_mmtt_rec.REQUISITION_DISTRIBUTION_ID ,
1785 p_mmtt_rec.REASON_ID ,
1786 p_mmtt_rec.LOT_NUMBER ,
1787 p_mmtt_rec.LOT_EXPIRATION_DATE ,
1788 p_mmtt_rec.SERIAL_NUMBER ,
1789 p_mmtt_rec.RECEIVING_DOCUMENT ,
1790 p_mmtt_rec.DEMAND_ID ,
1791 p_mmtt_rec.RCV_TRANSACTION_ID ,
1792 p_mmtt_rec.MOVE_TRANSACTION_ID ,
1793 p_mmtt_rec.COMPLETION_TRANSACTION_ID ,
1794 p_mmtt_rec.WIP_ENTITY_TYPE ,
1795 p_mmtt_rec.SCHEDULE_ID ,
1796 p_mmtt_rec.REPETITIVE_LINE_ID ,
1797 p_mmtt_rec.EMPLOYEE_CODE ,
1798 p_mmtt_rec.PRIMARY_SWITCH ,
1799 p_mmtt_rec.SCHEDULE_UPDATE_CODE ,
1800 p_mmtt_rec.SETUP_TEARDOWN_CODE ,
1801 p_mmtt_rec.ITEM_ORDERING ,
1802 p_mmtt_rec.NEGATIVE_REQ_FLAG ,
1803 p_mmtt_rec.OPERATION_SEQ_NUM ,
1804 p_mmtt_rec.PICKING_LINE_ID ,
1805 p_mmtt_rec.TRX_SOURCE_LINE_ID ,
1806 p_mmtt_rec.TRX_SOURCE_DELIVERY_ID ,
1807 p_mmtt_rec.PHYSICAL_ADJUSTMENT_ID ,
1808 p_mmtt_rec.CYCLE_COUNT_ID ,
1809 p_mmtt_rec.RMA_LINE_ID ,
1810 p_mmtt_rec.CUSTOMER_SHIP_ID ,
1811 p_mmtt_rec.CURRENCY_CODE ,
1812 p_mmtt_rec.CURRENCY_CONVERSION_RATE ,
1813 p_mmtt_rec.CURRENCY_CONVERSION_TYPE ,
1814 p_mmtt_rec.CURRENCY_CONVERSION_DATE ,
1815 p_mmtt_rec.USSGL_TRANSACTION_CODE ,
1816 p_mmtt_rec.VENDOR_LOT_NUMBER ,
1817 p_mmtt_rec.ENCUMBRANCE_ACCOUNT ,
1818 p_mmtt_rec.ENCUMBRANCE_AMOUNT ,
1819 p_mmtt_rec.SHIP_TO_LOCATION ,
1820 p_mmtt_rec.SHIPMENT_NUMBER ,
1821 p_mmtt_rec.TRANSFER_COST ,
1822 p_mmtt_rec.TRANSPORTATION_COST ,
1823 p_mmtt_rec.TRANSPORTATION_ACCOUNT ,
1824 p_mmtt_rec.FREIGHT_CODE ,
1825 p_mmtt_rec.CONTAINERS ,
1826 p_mmtt_rec.WAYBILL_AIRBILL ,
1827 p_mmtt_rec.EXPECTED_ARRIVAL_DATE ,
1828 p_mmtt_rec.TRANSFER_SUBINVENTORY ,
1829 p_mmtt_rec.TRANSFER_ORGANIZATION ,
1830 p_mmtt_rec.TRANSFER_TO_LOCATION ,
1831 p_mmtt_rec.NEW_AVERAGE_COST ,
1832 p_mmtt_rec.VALUE_CHANGE ,
1833 p_mmtt_rec.PERCENTAGE_CHANGE ,
1834 p_mmtt_rec.MATERIAL_ALLOCATION_TEMP_ID ,
1835 p_mmtt_rec.DEMAND_SOURCE_HEADER_ID ,
1836 p_mmtt_rec.DEMAND_SOURCE_LINE ,
1837 p_mmtt_rec.DEMAND_SOURCE_DELIVERY ,
1838 p_mmtt_rec.ITEM_SEGMENTS ,
1839 p_mmtt_rec.ITEM_DESCRIPTION ,
1840 p_mmtt_rec.ITEM_TRX_ENABLED_FLAG ,
1841 p_mmtt_rec.ITEM_LOCATION_CONTROL_CODE ,
1842 p_mmtt_rec.ITEM_RESTRICT_SUBINV_CODE ,
1843 p_mmtt_rec.ITEM_RESTRICT_LOCATORS_CODE ,
1844 p_mmtt_rec.ITEM_REVISION_QTY_CONTROL_CODE ,
1845 p_mmtt_rec.ITEM_PRIMARY_UOM_CODE ,
1846 p_mmtt_rec.ITEM_UOM_CLASS ,
1847 p_mmtt_rec.ITEM_SHELF_LIFE_CODE ,
1848 p_mmtt_rec.ITEM_SHELF_LIFE_DAYS ,
1849 p_mmtt_rec.ITEM_LOT_CONTROL_CODE ,
1850 p_mmtt_rec.ITEM_SERIAL_CONTROL_CODE ,
1851 p_mmtt_rec.ITEM_INVENTORY_ASSET_FLAG ,
1852 p_mmtt_rec.ALLOWED_UNITS_LOOKUP_CODE ,
1853 p_mmtt_rec.DEPARTMENT_ID ,
1854 p_mmtt_rec.DEPARTMENT_CODE ,
1855 p_mmtt_rec.WIP_SUPPLY_TYPE ,
1856 p_mmtt_rec.SUPPLY_SUBINVENTORY ,
1857 p_mmtt_rec.SUPPLY_LOCATOR_ID ,
1858 p_mmtt_rec.VALID_SUBINVENTORY_FLAG ,
1859 p_mmtt_rec.VALID_LOCATOR_FLAG ,
1860 p_mmtt_rec.LOCATOR_SEGMENTS ,
1861 p_mmtt_rec.CURRENT_LOCATOR_CONTROL_CODE ,
1862 p_mmtt_rec.NUMBER_OF_LOTS_ENTERED ,
1863 p_mmtt_rec.WIP_COMMIT_FLAG ,
1864 p_mmtt_rec.NEXT_LOT_NUMBER ,
1865 p_mmtt_rec.LOT_ALPHA_PREFIX ,
1866 p_mmtt_rec.NEXT_SERIAL_NUMBER ,
1867 p_mmtt_rec.SERIAL_ALPHA_PREFIX ,
1868 p_mmtt_rec.SHIPPABLE_FLAG ,
1869 p_mmtt_rec.POSTING_FLAG ,
1870 p_mmtt_rec.REQUIRED_FLAG ,
1871 p_mmtt_rec.PROCESS_FLAG ,
1872 p_mmtt_rec.ERROR_CODE ,
1873 p_mmtt_rec.ERROR_EXPLANATION ,
1874 p_mmtt_rec.ATTRIBUTE_CATEGORY ,
1875 p_mmtt_rec.ATTRIBUTE1 ,
1876 p_mmtt_rec.ATTRIBUTE2 ,
1877 p_mmtt_rec.ATTRIBUTE3 ,
1878 p_mmtt_rec.ATTRIBUTE4 ,
1879 p_mmtt_rec.ATTRIBUTE5 ,
1880 p_mmtt_rec.ATTRIBUTE6 ,
1881 p_mmtt_rec.ATTRIBUTE7 ,
1882 p_mmtt_rec.ATTRIBUTE8 ,
1883 p_mmtt_rec.ATTRIBUTE9 ,
1884 p_mmtt_rec.ATTRIBUTE10 ,
1885 p_mmtt_rec.ATTRIBUTE11 ,
1886 p_mmtt_rec.ATTRIBUTE12 ,
1887 p_mmtt_rec.ATTRIBUTE13 ,
1888 p_mmtt_rec.ATTRIBUTE14 ,
1889 p_mmtt_rec.ATTRIBUTE15 ,
1890 p_mmtt_rec.MOVEMENT_ID ,
1891 p_mmtt_rec.RESERVATION_QUANTITY ,
1892 p_mmtt_rec.SHIPPED_QUANTITY ,
1893 p_mmtt_rec.TRANSACTION_LINE_NUMBER ,
1894 p_mmtt_rec.TASK_ID ,
1895 p_mmtt_rec.TO_TASK_ID ,
1896 p_mmtt_rec.SOURCE_TASK_ID ,
1897 p_mmtt_rec.PROJECT_ID ,
1898 p_mmtt_rec.SOURCE_PROJECT_ID ,
1899 p_mmtt_rec.PA_EXPENDITURE_ORG_ID ,
1900 p_mmtt_rec.TO_PROJECT_ID ,
1901 p_mmtt_rec.EXPENDITURE_TYPE ,
1902 p_mmtt_rec.FINAL_COMPLETION_FLAG ,
1903 p_mmtt_rec.TRANSFER_PERCENTAGE ,
1904 p_mmtt_rec.TRANSACTION_SEQUENCE_ID ,
1905 p_mmtt_rec.MATERIAL_ACCOUNT ,
1906 p_mmtt_rec.MATERIAL_OVERHEAD_ACCOUNT ,
1907 p_mmtt_rec.RESOURCE_ACCOUNT ,
1908 p_mmtt_rec.OUTSIDE_PROCESSING_ACCOUNT ,
1909 p_mmtt_rec.OVERHEAD_ACCOUNT ,
1910 p_mmtt_rec.FLOW_SCHEDULE ,
1911 p_cost_group_id ,
1912 l_transfer_cost_group_id ,
1913 p_mmtt_rec.DEMAND_CLASS ,
1914 p_mmtt_rec.QA_COLLECTION_ID ,
1915 p_mmtt_rec.KANBAN_CARD_ID ,
1916 p_mmtt_rec.OVERCOMPLETION_TRANSACTION_QTY ,
1917 p_mmtt_rec.OVERCOMPLETION_PRIMARY_QTY ,
1918 p_mmtt_rec.OVERCOMPLETION_TRANSACTION_ID ,
1919 p_mmtt_rec.END_ITEM_UNIT_NUMBER ,
1920 p_mmtt_rec.SCHEDULED_PAYBACK_DATE ,
1921 p_mmtt_rec.LINE_TYPE_CODE ,
1922 p_mmtt_rec.PARENT_TRANSACTION_TEMP_ID ,
1923 p_mmtt_rec.PUT_AWAY_STRATEGY_ID ,
1924 p_mmtt_rec.PUT_AWAY_RULE_ID ,
1925 p_mmtt_rec.PICK_STRATEGY_ID ,
1926 p_mmtt_rec.PICK_RULE_ID ,
1927 p_mmtt_rec.MOVE_ORDER_LINE_ID ,
1928 p_mmtt_rec.TASK_GROUP_ID ,
1929 p_mmtt_rec.PICK_SLIP_NUMBER ,
1930 p_mmtt_rec.RESERVATION_ID ,
1931 p_mmtt_rec.COMMON_BOM_SEQ_ID ,
1932 p_mmtt_rec.COMMON_ROUTING_SEQ_ID ,
1933 p_mmtt_rec.ORG_COST_GROUP_ID ,
1934 p_mmtt_rec.COST_TYPE_ID ,
1935 p_mmtt_rec.TRANSACTION_STATUS ,
1936 p_mmtt_rec.STANDARD_OPERATION_ID ,
1937 p_mmtt_rec.TASK_PRIORITY ,
1938 p_mmtt_rec.WMS_TASK_TYPE ,
1939 p_mmtt_rec.PARENT_LINE_ID ,
1940 p_mmtt_rec.LPN_ID ,
1941 p_mmtt_rec.TRANSFER_LPN_ID ,
1942 p_mmtt_rec.WMS_TASK_STATUS ,
1943 p_mmtt_rec.CONTENT_LPN_ID ,
1944 p_mmtt_rec.CONTAINER_ITEM_ID ,
1945 p_mmtt_rec.CARTONIZATION_ID ,
1946 p_mmtt_rec.PICK_SLIP_DATE ,
1947 p_mmtt_rec.REBUILD_ITEM_ID ,
1948 p_mmtt_rec.REBUILD_SERIAL_NUMBER ,
1949 p_mmtt_rec.REBUILD_ACTIVITY_ID ,
1950 p_mmtt_rec.REBUILD_JOB_NAME ,
1951 p_mmtt_rec.ORGANIZATION_TYPE ,
1952 p_mmtt_rec.TRANSFER_ORGANIZATION_TYPE ,
1953 p_mmtt_rec.OWNING_ORGANIZATION_ID ,
1954 p_mmtt_rec.OWNING_TP_TYPE ,
1955 p_mmtt_rec.XFR_OWNING_ORGANIZATION_ID ,
1956 p_mmtt_rec.TRANSFER_OWNING_TP_TYPE ,
1957 p_mmtt_rec.PLANNING_ORGANIZATION_ID ,
1958 p_mmtt_rec.PLANNING_TP_TYPE ,
1959 p_mmtt_rec.XFR_PLANNING_ORGANIZATION_ID ,
1960 p_mmtt_rec.TRANSFER_PLANNING_TP_TYPE ,
1961 p_mmtt_rec.SECONDARY_UOM_CODE ,
1962 p_mmtt_rec.SECONDARY_TRANSACTION_QUANTITY ,
1963 p_mmtt_rec.TRANSACTION_BATCH_ID ,
1964 p_mmtt_rec.TRANSACTION_BATCH_SEQ ,
1965 p_mmtt_rec.ALLOCATED_LPN_ID ,
1966 p_mmtt_rec.SCHEDULE_NUMBER ,
1967 p_mmtt_rec.SCHEDULED_FLAG ,
1968 p_mmtt_rec.CLASS_CODE ,
1969 p_mmtt_rec.SCHEDULE_GROUP ,
1970 p_mmtt_rec.BUILD_SEQUENCE ,
1971 p_mmtt_rec.BOM_REVISION ,
1972 p_mmtt_rec.ROUTING_REVISION ,
1973 p_mmtt_rec.BOM_REVISION_DATE ,
1974 p_mmtt_rec.ROUTING_REVISION_DATE ,
1975 p_mmtt_rec.ALTERNATE_BOM_DESIGNATOR ,
1976 p_mmtt_rec.ALTERNATE_ROUTING_DESIGNATOR ,
1977 p_mmtt_rec.OPERATION_PLAN_ID ,
1978 p_mmtt_rec.fob_point ,
1979 p_mmtt_rec.intransit_account ,
1980 p_mmtt_rec.relieve_reservations_flag , /*** {{ R12 Enhanced reservations code changes ***/
1981 p_mmtt_rec.relieve_high_level_rsv_flag /*** {{ R12 Enhanced reservations code changes ***/
1982 ) ;
1983
1984 inv_comingling_utils.comingle_check
1985 (x_return_status => x_return_status
1986 , x_msg_count => L_msg_count
1987 , x_msg_data => L_msg_data
1988 , x_comingling_occurs => l_comingling_occurs
1989 , p_transaction_temp_id => p_mmtt_rec.transaction_temp_id);
1990
1991 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1992 RAISE fnd_api.g_exc_unexpected_error;
1993 ELSIF l_comingling_occurs = 'Y' THEN
1994 IF (l_debug = 1) THEN
1995 print_debug('proc_insert_mmtt .. comigling occurs : ' );
1996 END IF;
1997 --Commenting these because this message is getting added
1998 --in INVCOMUB.pls
1999 --fnd_message.set_name('INV', 'INV_COMINGLE_ERROR');
2000 --fnd_msg_pub.add;
2001 x_return_status := inv_cost_group_pvt.g_comingle_error;
2002 END IF;
2003
2004 EXCEPTION
2005 WHEN FND_API.G_EXC_ERROR THEN
2006 x_return_status := FND_API.G_RET_STS_ERROR ;
2007 IF (l_debug = 1) THEN
2008 print_debug('proc_insert_mmtt .. EXCEP G_EXC_ERROR : ' );
2009 END IF;
2010 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2011 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2012 IF (l_debug = 1) THEN
2013 print_debug('proc_insert_mmtt .. EXCEP G_EXC_UNEXPECTED_ERROR : ' );
2014 END IF;
2015 WHEN OTHERS THEN
2016 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2017 IF (l_debug = 1) THEN
2018 print_debug('proc_insert_mmtt .. EXCEP others: ' || SQLERRM(SQLCODE) );
2019 END IF;
2020 END proc_insert_mmtt;
2021 --
2022
2023 PROCEDURE proc_process_nocontrol
2024 (p_mmtt_rec IN mtl_material_transactions_temp%ROWTYPE,
2025 p_fob_point IN NUMBER,
2026 p_transfer_wms_org IN BOOLEAN,
2027 p_tfr_primary_cost_method IN NUMBER,
2028 p_tfr_org_cost_group_id IN NUMBER,
2029 p_from_project_id IN NUMBER,
2030 p_to_project_id IN NUMBER,
2031 x_return_status OUT NOCOPY VARCHAR2,
2032 x_msg_count OUT NOCOPY NUMBER,
2033 x_msg_data OUT NOCOPY VARCHAR2)
2034 IS
2035 l_cost_group_id NUMBER := -99999;
2036 l_lpn_id NUMBER := p_mmtt_rec.lpn_id;
2037 l_onhand_exists BOOLEAN := TRUE;
2038 l_is_backflush_txn BOOLEAN := FALSE;
2039 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2040 BEGIN
2041 x_return_status := fnd_api.g_ret_sts_success;
2042 IF (l_debug = 1) THEN
2043 print_debug( 'in proc_process_nocontrol p_mmtt_rec.transaction_temp_id:' || p_mmtt_rec.transaction_temp_id || ':');
2044 print_debug( '1: '||p_mmtt_rec.organization_id || ':');
2045 print_debug( '2: '||p_mmtt_rec.inventory_item_id || ':');
2046 print_debug( '3: '||p_mmtt_rec.subinventory_code || ':');
2047 print_debug( '4: '||p_mmtt_rec.locator_id || ':');
2048 print_debug( '5: '||p_mmtt_rec.revision || ':');
2049 END IF;
2050
2051 IF p_mmtt_rec.transaction_action_id IN (inv_globals.g_type_cycle_count_adj,
2052 inv_globals.g_type_physical_count_adj,
2053 inv_globals.g_action_deliveryadj)
2054 THEN
2055 IF p_mmtt_rec.transaction_action_id = inv_globals.g_type_physical_count_adj THEN
2056 IF p_mmtt_rec.lpn_id IS NOT NULL THEN
2057 l_lpn_id := p_mmtt_rec.lpn_id;
2058 ELSIF p_mmtt_rec.content_lpn_id IS NOT NULL THEN
2059 l_lpn_id := p_mmtt_rec.content_lpn_id;
2060 ELSIF p_mmtt_rec.transfer_lpn_id IS NOT NULL THEN
2061 l_lpn_id := p_mmtt_rec.transfer_lpn_id;
2062 END IF;
2063 ELSIF p_mmtt_rec.transaction_action_id = inv_globals.g_action_deliveryadj THEN
2064 l_lpn_id := p_mmtt_rec.transfer_lpn_id;
2065 ELSIF p_mmtt_rec.transaction_action_id = inv_globals.g_type_cycle_count_adj THEN
2066 l_lpn_id := p_mmtt_rec.transfer_lpn_id;
2067 END IF;
2068
2069 l_onhand_exists :=
2070 onhand_quantity_exists
2071 (p_inventory_item_id => p_mmtt_rec.inventory_item_id,
2072 p_revision => p_mmtt_rec.revision,
2073 p_organization_id => p_mmtt_rec.organization_id,
2074 p_subinventory_code => p_mmtt_rec.subinventory_code,
2075 p_locator_id => p_mmtt_rec.locator_id,
2076 p_lot_number => NULL,
2077 p_serial_number => NULL,
2078 p_lpn_id => l_lpn_id);
2079 IF NOT l_onhand_exists THEN
2080 IF (l_debug = 1) THEN
2081 print_debug('Treating this as as receipt transaction...: ');
2082 print_debug('Getting transfer cost group id from rules engine...: ');
2083 END IF;
2084 wms_costgroupengine_pvt.assign_cost_group
2085 (p_api_version => 1.0,
2086 p_init_msg_list => FND_API.G_FALSE,
2087 p_commit => FND_API.G_FALSE,
2088 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2089 x_return_status => x_return_Status,
2090 x_msg_count => x_msg_count,
2091 x_msg_data => x_msg_data,
2092 p_line_id => p_mmtt_rec.transaction_temp_id,
2093 p_input_type => wms_costgroupengine_pvt.g_input_mmtt);
2094
2095 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2096 IF (l_debug = 1) THEN
2097 print_debug('return error from wms_costgroupengine_pvt');
2098 END IF;
2099 RAISE FND_API.G_EXC_ERROR;
2100 ELSIF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2101 IF (l_debug = 1) THEN
2102 print_debug('return unexpected error from wms_costgroupengine_pvt');
2103 END IF;
2104 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2105 END IF;
2106
2107 END IF;
2108 ELSIF (p_mmtt_rec.transaction_source_type_id = inv_globals.g_sourcetype_salesorder
2109 OR p_mmtt_rec.transaction_source_type_id = inv_globals.g_sourcetype_intorder)
2110 AND (p_mmtt_rec.transaction_action_id = inv_globals.g_action_issue
2111 OR p_mmtt_rec.transaction_action_id = inv_globals.g_action_subxfr
2112 OR p_mmtt_rec.transaction_action_id = inv_globals.g_action_orgxfr
2113 OR p_mmtt_rec.transaction_action_id = inv_globals.g_action_intransitshipment)
2114 THEN -- For a ship confirm transaction, get the cost group from the content lpn ID
2115 l_lpn_id := p_mmtt_rec.content_lpn_id;
2116 /* Bug 4628878: For staging transfers, when whole LPN is being transfered, cost group should
2117 * be obtained from content_lpn_id */
2118
2119 /*8650417 Added the following IF to handle Bill only WF from OM*/
2120 IF (l_lpn_id IS NULL AND p_mmtt_rec.transaction_action_id = inv_globals.g_action_issue) THEN
2121 l_lpn_id := p_mmtt_rec.lpn_id;
2122 END IF;
2123
2124 ELSIF (
2125 ((p_mmtt_rec.transaction_source_type_id = inv_globals.g_sourcetype_salesorder
2126 OR p_mmtt_rec.transaction_source_type_id = inv_globals.g_sourcetype_intorder)
2127 AND (p_mmtt_rec.transaction_action_id = inv_globals.g_action_stgxfr))
2128 OR /*Bug 6499833:For move order sub transfers,trying to get the costgroup from content_lpn_id.*/
2129 (( p_mmtt_rec.transaction_source_type_id = inv_globals.g_sourcetype_moveorder)
2130 AND (p_mmtt_rec.transaction_action_id = inv_globals.g_action_subxfr))
2131 )
2132 -- AND (p_mmtt_rec.lpn_id IS NULL) Bug#6770593
2133 AND (p_mmtt_rec.content_lpn_id IS NOT NULL)
2134 AND (p_mmtt_rec.inventory_item_id <> -1)
2135 THEN -- For a staging transfer transaction with content_lpn_id, get the cost group from the content lpn ID
2136 l_lpn_id := p_mmtt_rec.content_lpn_id;
2137 END IF;
2138 IF (l_debug = 1) THEN
2139 print_debug('l_lpn_id is set to: '||l_lpn_id);
2140 END IF;
2141
2142 IF l_onhand_exists THEN
2143
2144 IF p_mmtt_rec.move_transaction_id IS NOT NULL OR
2145 p_mmtt_rec.completion_transaction_id IS NOT NULL THEN
2146 l_is_backflush_txn := TRUE;
2147 END IF;
2148
2149 proc_determine_costgroup(p_organization_id => p_mmtt_rec.organization_id,
2150 p_inventory_item_id => p_mmtt_rec.inventory_item_id,
2151 p_subinventory_code => p_mmtt_rec.subinventory_code,
2152 p_locator_id => p_mmtt_rec.locator_id,
2153 p_revision => p_mmtt_rec.revision,
2154 p_lot_number => NULL,
2155 p_serial_number => NULL,
2156 p_containerized_flag => 2, -- we need unpacked material from moq
2157 p_lpn_id => l_lpn_id,
2158 p_transaction_action_id => p_mmtt_rec.transaction_action_id,
2159 p_is_backflush_txn => l_is_backflush_txn,
2160 x_cost_group_id => l_cost_group_id,
2161 x_return_status => x_return_status);
2162
2163 IF (l_debug = 1) THEN
2164 print_debug('proc_determine_costgroup return : ' || x_return_status);
2165 print_debug('proc_determine_costgroup cg : ' || l_cost_group_id);
2166 END IF;
2167 IF (x_return_status = fnd_api.g_ret_sts_error)
2168 THEN
2169 RAISE fnd_api.g_exc_error ;
2170 END IF;
2171
2172 IF (x_return_status = fnd_api.g_ret_sts_unexp_error)
2173 THEN
2174 RAISE fnd_api.g_exc_unexpected_error ;
2175 END IF;
2176
2177 IF (l_debug = 1) THEN
2178 print_debug('call :proc_update_mmtt :p_transaction_temp_id: ' || p_mmtt_rec.transaction_temp_id);
2179 print_debug('call :proc_update_mmtt :l_cost_group_id: ' || l_cost_group_id);
2180 END IF;
2181
2182 proc_update_mmtt(p_transaction_temp_id => p_mmtt_rec.transaction_temp_id,
2183 p_transfer_wms_org => p_transfer_wms_org,
2184 p_fob_point => p_fob_point,
2185 p_tfr_primary_cost_method => p_tfr_primary_cost_method,
2186 p_tfr_org_cost_group_id => p_tfr_org_cost_group_id,
2187 p_transaction_action_id => p_mmtt_rec.transaction_action_id,
2188 p_transfer_organization => p_mmtt_rec.transfer_organization,
2189 p_transfer_subinventory => p_mmtt_rec.transfer_subinventory,
2190 p_cost_group_id => l_cost_group_id,
2191 p_transfer_cost_group_id => NULL,
2192 p_primary_quantity => NULL,
2193 p_transaction_quantity => NULL,
2194 p_from_project_id => p_from_project_id,
2195 p_to_project_id => p_to_project_id,
2196 x_return_status => x_return_status);
2197
2198 IF (l_debug = 1) THEN
2199 print_debug('proc_update_mmtt return : ' || x_return_status);
2200 END IF;
2201 IF (x_return_status = fnd_api.g_ret_sts_error)
2202 THEN
2203 RAISE fnd_api.g_exc_error ;
2204 END IF;
2205
2206 IF (x_return_status = fnd_api.g_ret_sts_unexp_error)
2207 THEN
2208 RAISE fnd_api.g_exc_unexpected_error ;
2209 END IF;
2210 END IF; -- Onhand exists
2211
2212 EXCEPTION
2213 WHEN FND_API.G_EXC_ERROR THEN
2214 x_return_status := FND_API.G_RET_STS_ERROR ;
2215 IF (l_debug = 1) THEN
2216 print_debug('no process control .. EXCEP G_EXC_ERROR : ' );
2217 END IF;
2218 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2219 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2220 IF (l_debug = 1) THEN
2221 print_debug('no process control .. EXCEP G_EXC_UNEXPECTED_ERROR : ' );
2222 END IF;
2223 WHEN OTHERS THEN
2224 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2225 IF (l_debug = 1) THEN
2226 print_debug('no process control .. EXCEP OTHERS : ' );
2227 END IF;
2228 END proc_process_nocontrol;
2229
2230
2231 -- Processes lot controlled items. This involves splitting
2232 -- MMTT and updating MTLT lines so that each row corresponds to a unique cost
2233 -- group.
2234 PROCEDURE proc_process_lots
2235 (p_mmtt_rec IN mtl_material_transactions_temp%ROWTYPE,
2236 p_fob_point IN NUMBER,
2237 p_transfer_wms_org IN BOOLEAN,
2238 p_tfr_primary_cost_method IN NUMBER,
2239 p_tfr_org_cost_group_id IN NUMBER,
2240 p_from_project_id IN NUMBER,
2241 p_to_project_id IN NUMBER,
2242 x_return_status OUT NOCOPY VARCHAR2,
2243 x_msg_count OUT NOCOPY NUMBER,
2244 x_msg_data OUT NOCOPY VARCHAR2)
2245 IS
2246 l_api_name CONSTANT VARCHAR2(100) := 'proc_process_lots';
2247 l_transaction_temp_id NUMBER := NULL;
2248 i INTEGER;
2249 j INTEGER;
2250 l_transaction_quantity NUMBER;
2251 l_quantity_sign NUMBER;
2252 l_cost_group_id NUMBER;
2253
2254 -- For putting records in MTLT and MSNT tables
2255 TYPE lots_record IS RECORD
2256 (mtlt_rowid ROWID,
2257 cost_group_id NUMBER);
2258 TYPE lots_table IS TABLE OF lots_record INDEX BY BINARY_INTEGER;
2259 l_lots_table lots_table;
2260 l_lti INTEGER := 0;
2261
2262 TYPE cg_quantity_record IS RECORD
2263 (new_transaction_temp_id NUMBER,
2264 primary_quantity NUMBER,
2265 transaction_quantity NUMBER,
2266 update_mmtt BOOLEAN);
2267 TYPE cg_quantity_table IS TABLE OF cg_quantity_record INDEX BY BINARY_INTEGER;
2268 l_cg_quantity_table cg_quantity_table;
2269
2270 rec_mtlt cur_mtlt%ROWTYPE;
2271
2272 l_onhand_exists BOOLEAN;
2273 l_is_backflush_txn BOOLEAN := FALSE;
2274 l_lpn_id NUMBER := p_mmtt_rec.lpn_id;
2275
2276 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2277 BEGIN
2278 x_return_status := fnd_api.g_ret_sts_success;
2279 SAVEPOINT sp_proc_process_lots;
2280
2281 IF (l_debug = 1) THEN
2282 print_debug('IN proc_process_lots.. ');
2283 END IF;
2284
2285 OPEN cur_mtlt(p_mmtt_rec.transaction_temp_id);
2286
2287 FETCH cur_mtlt INTO rec_mtlt;
2288
2289 WHILE cur_mtlt%found LOOP
2290 IF (l_debug = 1) THEN
2291 print_debug('Within cur_mtlt loop... ' || rec_mtlt.lot_number);
2292 END IF;
2293
2294 l_onhand_exists := TRUE;
2295
2296 -- Adjustment transactions
2297 IF p_mmtt_rec.transaction_action_id IN (inv_globals.g_type_cycle_count_adj,
2298 inv_globals.g_type_physical_count_adj,
2299 inv_globals.g_action_deliveryadj)
2300 THEN
2301 IF p_mmtt_rec.transaction_action_id = inv_globals.g_type_physical_count_adj THEN
2302 IF p_mmtt_rec.lpn_id IS NOT NULL THEN
2303 l_lpn_id := p_mmtt_rec.lpn_id;
2304 ELSIF p_mmtt_rec.content_lpn_id IS NOT NULL THEN
2305 l_lpn_id := p_mmtt_rec.content_lpn_id;
2306 ELSIF p_mmtt_rec.transfer_lpn_id IS NOT NULL THEN
2307 l_lpn_id := p_mmtt_rec.transfer_lpn_id;
2308 END IF;
2309 ELSIF p_mmtt_rec.transaction_action_id = inv_globals.g_action_deliveryadj THEN
2310 l_lpn_id := p_mmtt_rec.transfer_lpn_id;
2311 ELSIF p_mmtt_rec.transaction_action_id = inv_globals.g_type_cycle_count_adj THEN
2312 l_lpn_id := p_mmtt_rec.transfer_lpn_id;
2313 END IF;
2314
2315 l_onhand_exists :=
2316 onhand_quantity_exists
2317 (p_inventory_item_id => p_mmtt_rec.inventory_item_id,
2318 p_revision => p_mmtt_rec.revision,
2319 p_organization_id => p_mmtt_rec.organization_id,
2320 p_subinventory_code => p_mmtt_rec.subinventory_code,
2321 p_locator_id => p_mmtt_rec.locator_id,
2322 p_lot_number => rec_mtlt.lot_number,
2323 p_serial_number => NULL,
2324 p_lpn_id => l_lpn_id);
2325 IF NOT l_onhand_exists THEN
2326 IF (l_debug = 1) THEN
2327 print_debug('Treating this as as receipt transaction...: ');
2328 print_debug('Getting transfer cost group id from rules engine...: ');
2329 END IF;
2330
2331 wms_costgroupengine_pvt.assign_cost_group
2332 (p_api_version => 1.0,
2333 p_init_msg_list => FND_API.G_FALSE,
2334 p_commit => FND_API.G_FALSE,
2335 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2336 x_return_status => x_return_Status,
2337 x_msg_count => x_msg_count,
2338 x_msg_data => x_msg_data,
2339 p_line_id => p_mmtt_rec.transaction_temp_id,
2340 p_input_type => wms_costgroupengine_pvt.g_input_mmtt);
2341
2342 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2343 IF (l_debug = 1) THEN
2344 print_debug('return error from wms_costgroupengine_pvt');
2345 END IF;
2346 RAISE FND_API.G_EXC_ERROR;
2347 ELSIF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2348 IF (l_debug = 1) THEN
2349 print_debug('return unexpected error from wms_costgroupengine_pvt');
2350 END IF;
2351 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2352 END IF;
2353 END IF;
2354 ELSIF (p_mmtt_rec.transaction_source_type_id = inv_globals.g_sourcetype_salesorder
2355 OR p_mmtt_rec.transaction_source_type_id = inv_globals.g_sourcetype_intorder)
2356 AND (p_mmtt_rec.transaction_action_id = inv_globals.g_action_issue
2357 OR p_mmtt_rec.transaction_action_id = inv_globals.g_action_subxfr
2358 OR p_mmtt_rec.transaction_action_id = inv_globals.g_action_orgxfr
2359 OR p_mmtt_rec.transaction_action_id = inv_globals.g_action_intransitshipment)
2360 THEN -- For a ship confirm transaction, get the cost group from the content lpn ID
2361 l_lpn_id := p_mmtt_rec.content_lpn_id;
2362 /* Bug 4628878: For staging transfers, when whole LPN is being transfered, cost group should
2363 * be obtained from content_lpn_id */
2364
2365 /*8650417 Added the following IF to handle Bill only WF from OM*/
2366 IF (l_lpn_id IS NULL AND p_mmtt_rec.transaction_action_id = inv_globals.g_action_issue) THEN
2367 l_lpn_id := p_mmtt_rec.lpn_id;
2368 END IF;
2369
2370 ELSIF (
2371 ((p_mmtt_rec.transaction_source_type_id = inv_globals.g_sourcetype_salesorder
2372 OR p_mmtt_rec.transaction_source_type_id = inv_globals.g_sourcetype_intorder)
2373 AND (p_mmtt_rec.transaction_action_id = inv_globals.g_action_stgxfr))
2374 OR/*Bug 6499833:For move order sub transfers,trying to get the costgroup from content_lpn_id.*/
2375 (( p_mmtt_rec.transaction_source_type_id = inv_globals.g_sourcetype_moveorder)
2376 AND (p_mmtt_rec.transaction_action_id = inv_globals.g_action_subxfr))
2377 )
2378 -- AND (p_mmtt_rec.lpn_id IS NULL) Bug#6770593
2379 AND (p_mmtt_rec.content_lpn_id IS NOT NULL)
2380 AND (p_mmtt_rec.inventory_item_id <> -1)
2381 THEN -- For a staging transfer transaction with content_lpn_id, get the cost group from the content lpn ID
2382 l_lpn_id := p_mmtt_rec.content_lpn_id;
2383 END IF;
2384 IF (l_debug = 1) THEN
2385 print_debug('l_lpn_id is set to: '||l_lpn_id);
2386 END IF;
2387
2388 IF l_onhand_exists THEN
2389 l_lti := l_lti + 1;
2390 IF (l_debug = 1) THEN
2391 print_debug('trx_id: ' || p_mmtt_rec.transaction_temp_id);
2392 print_debug('Row ID: ' || rec_mtlt.mtlt_rowid);
2393 END IF;
2394
2395 l_lots_table(l_lti).mtlt_rowid := rec_mtlt.mtlt_rowid;
2396
2397 IF p_mmtt_rec.move_transaction_id IS NOT NULL OR
2398 p_mmtt_rec.completion_transaction_id IS NOT NULL THEN
2399 l_is_backflush_txn := TRUE;
2400 END IF;
2401
2402 -- Get the cost group for this MTLT record
2403 proc_determine_costgroup(p_organization_id => p_mmtt_rec.organization_id,
2404 p_inventory_item_id => p_mmtt_rec.inventory_item_id,
2405 p_subinventory_code => p_mmtt_rec.subinventory_code,
2406 p_locator_id => p_mmtt_rec.locator_id,
2407 p_revision => p_mmtt_rec.revision,
2408 p_lot_number => rec_mtlt.lot_number,
2409 p_serial_number => NULL,
2410 p_containerized_flag => 2, -- we need unpacked material from moq
2411 p_lpn_id => l_lpn_id,
2412 p_transaction_action_id => p_mmtt_rec.transaction_action_id,
2413 p_is_backflush_txn => l_is_backflush_txn,
2414 x_cost_group_id => l_cost_group_id,
2415 x_return_status => x_return_status);
2416
2417
2418 l_lots_table(l_lti).cost_group_id := l_cost_group_id;
2419
2420 IF x_return_status = fnd_api.g_ret_sts_error THEN
2421 RAISE fnd_api.g_exc_error ;
2422 END IF;
2423
2424 IF l_cg_quantity_table.exists(l_cost_group_id) THEN
2425 l_cg_quantity_table(l_cost_group_id).primary_quantity :=
2426 l_cg_quantity_table(l_cost_group_id).primary_quantity +
2427 Abs(rec_mtlt.primary_quantity);
2428
2429 l_cg_quantity_table(l_cost_group_id).transaction_quantity :=
2430 l_cg_quantity_table(l_cost_group_id).transaction_quantity +
2431 Abs(rec_mtlt.transaction_quantity);
2432 ELSE
2433 IF l_cg_quantity_table.COUNT = 0 THEN
2434 -- If the table is empty then the existing
2435 -- transaction_temp_id should be used as the
2436 -- new_transaction_temp_id also
2437 l_cg_quantity_table(l_cost_group_id).new_transaction_temp_id := p_mmtt_rec.transaction_temp_id;
2438 l_cg_quantity_table(l_cost_group_id).update_mmtt := TRUE;
2439 ELSE
2440 -- otherwise generate a new_transaction_temp_id
2441 SELECT mtl_material_transactions_s.NEXTVAL
2442 INTO l_transaction_temp_id
2443 FROM dual;
2444 l_cg_quantity_table(l_cost_group_id).new_transaction_temp_id := l_transaction_temp_id;
2445 l_cg_quantity_table(l_cost_group_id).update_mmtt := FALSE;
2446 END IF;
2447 l_cg_quantity_table(l_cost_group_id).primary_quantity := Abs(rec_mtlt.primary_quantity);
2448 l_cg_quantity_table(l_cost_group_id).transaction_quantity := Abs(rec_mtlt.transaction_quantity);
2449 END IF;
2450 END IF; -- If onhand exists
2451
2452 FETCH cur_mtlt INTO rec_mtlt;
2453 END LOOP;
2454
2455 CLOSE cur_mtlt;
2456
2457 -- Insert or update the records in l_cg_quantity_table into MMTT
2458 IF (l_debug = 1) THEN
2459 print_debug('count: ' || l_cg_quantity_table.count);
2460 END IF;
2461
2462 IF l_cg_quantity_table.COUNT > 0 THEN
2463 IF (l_debug = 1) THEN
2464 print_debug('proc_process_lots..Inserting records INTO MMTT ');
2465 END IF;
2466 i := l_cg_quantity_table.first;
2467
2468 IF p_mmtt_rec.transaction_quantity >= 0 THEN
2469 l_quantity_sign := 1;
2470 ELSE
2471 l_quantity_sign := -1;
2472 END IF;
2473
2474 LOOP
2475 l_cg_quantity_table(i).primary_quantity :=
2476 l_cg_quantity_table(i).primary_quantity * l_quantity_sign;
2477
2478 l_cg_quantity_table(i).transaction_quantity :=
2479 l_cg_quantity_table(i).transaction_quantity * l_quantity_sign;
2480
2481 IF (l_debug = 1) THEN
2482 print_debug('Primary qty: ' ||
2483 l_cg_quantity_table(i).primary_quantity);
2484 print_debug('qty sign: ' || l_quantity_sign);
2485 END IF;
2486
2487 IF l_cg_quantity_table(i).update_mmtt = FALSE THEN
2488 proc_insert_mmtt(p_mmtt_rec,
2489 p_transfer_wms_org,
2490 p_fob_point,
2491 p_tfr_primary_cost_method,
2492 p_tfr_org_cost_group_id,
2493 i, -- Remember that i is also the cost_group_id of the record
2494 NULL,
2495 l_cg_quantity_table(i).primary_quantity,
2496 l_cg_quantity_table(i).transaction_quantity,
2497 l_cg_quantity_table(i).new_transaction_temp_id,
2498 p_from_project_id,
2499 p_to_project_id,
2500 x_return_status);
2501
2502 IF (l_debug = 1) THEN
2503 print_debug('proc_insert_mmtt return : ' || x_return_status);
2504 END IF;
2505 ELSE
2506 proc_update_mmtt(p_mmtt_rec.transaction_temp_id,
2507 p_transfer_wms_org,
2508 p_fob_point,
2509 p_tfr_primary_cost_method,
2510 p_tfr_org_cost_group_id,
2511 p_mmtt_rec.transaction_action_id,
2512 p_mmtt_rec.transfer_organization,
2513 p_mmtt_rec.transfer_subinventory,
2514 i, -- Remember that i is also the cost_group_id of the record
2515 NULL,
2516 l_cg_quantity_table(i).primary_quantity,
2517 l_cg_quantity_table(i).transaction_quantity,
2518 p_from_project_id,
2519 p_to_project_id,
2520 x_return_status);
2521
2522 IF (l_debug = 1) THEN
2523 print_debug('proc_update_mmtt return : ' || x_return_status);
2524 END IF;
2525 END IF;
2526
2527 IF (x_return_status = fnd_api.g_ret_sts_error)
2528 THEN
2529 RAISE fnd_api.g_exc_error;
2530 END IF;
2531
2532 IF (x_return_status = fnd_api.g_ret_sts_unexp_error)
2533 THEN
2534 RAISE fnd_api.g_exc_unexpected_error;
2535 END IF;
2536
2537 EXIT WHEN i = l_cg_quantity_table.last;
2538 i := l_cg_quantity_table.next(i);
2539 END LOOP;
2540
2541 -- Update the records in l_lots_table into MTLT and MSNT
2542 IF (l_debug = 1) THEN
2543 print_debug('IN proc_process_lots..updating records INTO MTLT');
2544 END IF;
2545 FOR i IN 1..l_lots_table.COUNT LOOP
2546 -- Update the MTLT records
2547 IF (l_debug = 1) THEN
2548 print_debug('updating MTLT ');
2549 END IF;
2550 proc_update_mtlt(l_lots_table(i).mtlt_rowid,
2551 l_cg_quantity_table(l_lots_table(i).cost_group_id).new_transaction_temp_id,
2552 NULL,
2553 NULL,
2554 NULL,
2555 NULL,
2556 x_return_status);
2557
2558 IF (l_debug = 1) THEN
2559 print_debug('proc_update_mtlt return : ' || x_return_status);
2560 END IF;
2561
2562 IF (x_return_status = fnd_api.g_ret_sts_error)
2563 THEN
2564 RAISE fnd_api.g_exc_error ;
2565 END IF;
2566
2567 IF (x_return_status = fnd_api.g_ret_sts_unexp_error)
2568 THEN
2569 RAISE fnd_api.g_exc_unexpected_error ;
2570 END IF;
2571 END LOOP;
2572
2573 END IF;
2574 EXCEPTION
2575 WHEN FND_API.G_EXC_ERROR THEN
2576 IF (l_debug = 1) THEN
2577 print_debug('proc_process_lots .. EXCEP G_EXC_ERROR : ' );
2578 END IF;
2579 x_return_status := FND_API.G_RET_STS_ERROR ;
2580 IF cur_mtlt%isopen THEN
2581 CLOSE cur_mtlt;
2582 END IF;
2583 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2584 IF (l_debug = 1) THEN
2585 print_debug('proc_process_lots .. EXCEP G_EXC_UNEXPECTED_ERROR : ' );
2586 END IF;
2587 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2588 IF cur_mtlt%isopen THEN
2589 CLOSE cur_mtlt;
2590 END IF;
2591 WHEN OTHERS THEN
2592 IF (l_debug = 1) THEN
2593 print_debug('proc_process_lots .. EXCEP OTHERS : ' || SQLERRM(SQLCODE));
2594 END IF;
2595 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2596 IF cur_mtlt%isopen THEN
2597 CLOSE cur_mtlt;
2598 END IF;
2599 END proc_process_lots;
2600
2601
2602 -- Processes the lot and serial controlled items. This involves splitting
2603 -- MMTT, MTLT and MSNT lines so that each row corresponds to a unique cost
2604 -- group.
2605
2606 PROCEDURE proc_process_serials
2607 (p_mmtt_rec IN mtl_material_transactions_temp%ROWTYPE,
2608 p_fob_point IN NUMBER,
2609 p_transfer_wms_org IN BOOLEAN,
2610 p_tfr_primary_cost_method IN NUMBER,
2611 p_tfr_org_cost_group_id IN NUMBER,
2612 p_from_project_id IN NUMBER,
2613 p_to_project_id IN NUMBER,
2614 x_return_status OUT NOCOPY VARCHAR2,
2615 x_msg_count OUT NOCOPY NUMBER,
2616 x_msg_data OUT NOCOPY VARCHAR2)
2617 IS
2618 l_api_name CONSTANT VARCHAR2(100) := 'proc_process_serial';
2619 l_transaction_temp_id NUMBER := NULL;
2620 i INTEGER;
2621 l_transaction_quantity NUMBER;
2622 l_quantity_sign NUMBER;
2623 l_cost_group_id NUMBER;
2624
2625 -- For putting records in MSNT tables
2626 TYPE serial_record IS RECORD
2627 (from_serial_number mtl_serial_numbers.serial_number%TYPE,
2628 to_serial_number mtl_serial_numbers.serial_number%TYPE,
2629 cost_group_id NUMBER,
2630 update_msnt BOOLEAN);
2631 TYPE serial_table IS TABLE OF serial_record INDEX BY BINARY_INTEGER;
2632 l_serial_table serial_table;
2633 l_sti INTEGER := 1;
2634
2635 TYPE cg_quantity_record IS RECORD
2636 (new_transaction_temp_id NUMBER,
2637 quantity NUMBER,
2638 update_mmtt BOOLEAN);
2639 TYPE cg_quantity_table IS TABLE OF cg_quantity_record INDEX BY BINARY_INTEGER;
2640 l_cg_quantity_table cg_quantity_table;
2641
2642 TYPE msnt_rowid_table IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
2643 l_msnt_rowid_table msnt_rowid_table;
2644
2645 TYPE msnt_table IS TABLE OF cur_msnt%ROWTYPE INDEX BY BINARY_INTEGER;
2646 l_msnt_table msnt_table;
2647
2648 rec_msnt cur_msnt%ROWTYPE;
2649
2650 l_onhand_exists BOOLEAN;
2651 l_lpn_id NUMBER := p_mmtt_rec.lpn_id;
2652 --Bug 3686015 fix
2653 l_temp_prefix VARCHAR2(30):=NULL;
2654 l_from_ser_number NUMBER := NULL;
2655 l_fm_ser_length NUMBER := NULL;
2656 l_to_temp_prefix VARCHAR2(30) := NULL;
2657 l_to_ser_number NUMBER := NULL;
2658 --Bug 3686015 fix
2659 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2660 BEGIN
2661 x_return_status := fnd_api.g_ret_sts_success;
2662 SAVEPOINT sp_proc_process_serial;
2663
2664 IF (l_debug = 1) THEN
2665 print_debug('IN proc_process_serial.. ');
2666 END IF;
2667
2668 OPEN cur_msnt(p_mmtt_rec.transaction_temp_id);
2669
2670 FETCH cur_msnt INTO rec_msnt;
2671
2672 -- If the serial control is dynamic entry at sales order issue check if
2673 -- there are any records in MSNT. If there are no records there treat the
2674 -- item as a no control item.
2675 IF p_mmtt_rec.item_serial_control_code = 6 THEN
2676 CLOSE cur_msnt;
2677 proc_process_nocontrol
2678 (p_mmtt_rec => p_mmtt_rec,
2679 p_fob_point => p_fob_point,
2680 p_transfer_wms_org => p_transfer_wms_org,
2681 p_tfr_primary_cost_method => p_tfr_primary_cost_method,
2682 p_tfr_org_cost_group_id => p_tfr_org_cost_group_id,
2683 p_from_project_id => p_from_project_id,
2684 p_to_project_id => p_to_project_id,
2685 x_return_status => x_return_status,
2686 x_msg_count => x_msg_count,
2687 x_msg_data => x_msg_data);
2688 RETURN;
2689 END IF;
2690
2691
2692 IF p_mmtt_rec.item_serial_control_code <> 6 AND cur_msnt%notfound THEN
2693 IF (l_debug = 1) THEN
2694 print_debug('Serial Code is not 6 but does not have any corresponding
2695 records IN MSNT');
2696 END IF;
2697 fnd_message.set_name('INV', 'Cannot find the serial number
2698 for the transaction being processed');
2699 fnd_msg_pub.add;
2700 RAISE FND_API.G_EXC_ERROR;
2701 END IF;
2702
2703 WHILE cur_msnt%found LOOP
2704 IF (l_debug = 1) THEN
2705 print_debug('trx_id: ' || p_mmtt_rec.transaction_temp_id);
2706 END IF;
2707
2708 i := 1;
2709 IF (l_debug = 1) THEN
2710 print_debug('FSN: ' || rec_msnt.fm_serial_number);
2711 print_debug('TSN: ' || rec_msnt.to_serial_number);
2712 print_debug('ORG: ' || p_mmtt_rec.organization_id);
2713 print_debug('ITEM: ' || p_mmtt_rec.inventory_item_id);
2714 END IF;
2715
2716 --Bug 3686015
2717 inv_validate.number_from_sequence(rec_msnt.fm_serial_number, l_temp_prefix, l_from_ser_number);
2718 l_fm_ser_length := Length(rec_msnt.fm_serial_number);
2719
2720 IF (l_debug = 1) THEN
2721 print_debug('FSNPREFIX: ' || l_temp_prefix);
2722 print_debug('FSNNUMERIC: ' || l_from_ser_number);
2723 print_debug('FSNLENGTH: ' || l_fm_ser_length);
2724 END IF;
2725 IF (rec_msnt.to_serial_number IS NOT NULL) AND
2726 (rec_msnt.to_serial_number <> rec_msnt.fm_serial_number) THEN
2727 IF Length(rec_msnt.to_serial_number)<>l_fm_ser_length THEN
2728 IF (l_debug = 1) THEN
2729 print_debug('ERROR: Length of FSN diff from TSN');
2730 END IF;
2731 fnd_message.set_name('INV', 'INV_FROM_TO_SER_DIFF_LENGTH');
2732 fnd_message.set_token('FM_SER_NUM',rec_msnt.fm_serial_number);
2733 fnd_message.set_token('TO_SER_NUM', rec_msnt.to_serial_number);
2734 fnd_msg_pub.add;
2735 RAISE fnd_api.g_exc_error;
2736 END IF;
2737
2738 -- get the number part of the to serial
2739 inv_validate.number_from_sequence(rec_msnt.to_serial_number, l_to_temp_prefix, l_to_ser_number);
2740
2741 IF (l_debug = 1) THEN
2742 print_debug('TSNPREFIX: ' || l_to_temp_prefix);
2743 print_debug('TSNNUMERIC: ' || l_to_ser_number);
2744 END IF;
2745
2746 IF (l_temp_prefix IS NOT NULL) AND (l_to_temp_prefix IS NOT NULL) AND
2747 (l_to_temp_prefix <> l_temp_prefix) THEN
2748 IF (l_debug = 1) THEN
2749 print_debug('ERROR: From serial prefix different from to serial prefix');
2750 END IF;
2751 fnd_message.set_name('INV', 'INV_FROM_TO_SER_DIFF_PFX');
2752 fnd_message.set_token('FM_SER_NUM',rec_msnt.fm_serial_number);
2753 fnd_message.set_token('TO_SER_NUM', rec_msnt.to_serial_number);
2754 fnd_msg_pub.add;
2755 RAISE fnd_api.g_exc_error;
2756 END IF;
2757
2758 END IF;
2759 --Bug 3686015
2760
2761
2762 FOR rec_msn IN cur_msn(rec_msnt.fm_serial_number,
2763 rec_msnt.to_serial_number,
2764 p_mmtt_rec.inventory_item_id,
2765 p_mmtt_rec.organization_id,
2766 l_temp_prefix,
2767 l_fm_ser_length)
2768 LOOP
2769 IF (l_debug = 1) THEN
2770 print_debug('In MSN cursor...SN: ' || rec_msn.serial_number);
2771 END IF;
2772 l_onhand_exists := TRUE;
2773
2774 -- Adjustment transactions
2775 IF p_mmtt_rec.transaction_action_id IN (inv_globals.g_type_cycle_count_adj,
2776 inv_globals.g_type_physical_count_adj,
2777 inv_globals.g_action_deliveryadj)
2778 THEN
2779
2780 IF p_mmtt_rec.transaction_action_id = inv_globals.g_type_physical_count_adj THEN
2781 IF p_mmtt_rec.lpn_id IS NOT NULL THEN
2782 l_lpn_id := p_mmtt_rec.lpn_id;
2783 ELSIF p_mmtt_rec.content_lpn_id IS NOT NULL THEN
2784 l_lpn_id := p_mmtt_rec.content_lpn_id;
2785 ELSIF p_mmtt_rec.transfer_lpn_id IS NOT NULL THEN
2786 l_lpn_id := p_mmtt_rec.transfer_lpn_id;
2787 END IF;
2788 ELSIF p_mmtt_rec.transaction_action_id = inv_globals.g_action_deliveryadj THEN
2789 l_lpn_id := p_mmtt_rec.transfer_lpn_id;
2790 ELSIF p_mmtt_rec.transaction_action_id = inv_globals.g_type_cycle_count_adj THEN
2791 l_lpn_id := p_mmtt_rec.transfer_lpn_id;
2792 END IF;
2793
2794 l_onhand_exists :=
2795 onhand_quantity_exists
2796 (p_inventory_item_id => p_mmtt_rec.inventory_item_id,
2797 p_revision => p_mmtt_rec.revision,
2798 p_organization_id => p_mmtt_rec.organization_id,
2799 p_subinventory_code => p_mmtt_rec.subinventory_code,
2800 p_locator_id => p_mmtt_rec.locator_id,
2801 p_lot_number => NULL,
2802 p_serial_number => rec_msn.serial_number,
2803 p_lpn_id => l_lpn_id);
2804 IF NOT l_onhand_exists THEN
2805 IF (l_debug = 1) THEN
2806 print_debug('Treating this as as receipt transaction...: ');
2807 print_debug('Getting transfer cost group id from rules engine...: ');
2808 END IF;
2809 wms_costgroupengine_pvt.assign_cost_group
2810 (p_api_version => 1.0,
2811 p_init_msg_list => FND_API.G_FALSE,
2812 p_commit => FND_API.G_FALSE,
2813 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2814 x_return_status => x_return_Status,
2815 x_msg_count => x_msg_count,
2816 x_msg_data => x_msg_data,
2817 p_line_id => p_mmtt_rec.transaction_temp_id,
2818 p_input_type => wms_costgroupengine_pvt.g_input_mmtt);
2819
2820 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2821 IF (l_debug = 1) THEN
2822 print_debug('return error from wms_costgroupengine_pvt');
2823 END IF;
2824 RAISE FND_API.G_EXC_ERROR;
2825 ELSIF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2826 IF (l_debug = 1) THEN
2827 print_debug('return unexpected error from wms_costgroupengine_pvt');
2828 END IF;
2829 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2830 END IF;
2831
2832 END IF;
2833 ELSIF (p_mmtt_rec.transaction_source_type_id = inv_globals.g_sourcetype_salesorder
2834 OR p_mmtt_rec.transaction_source_type_id = inv_globals.g_sourcetype_intorder)
2835 AND (p_mmtt_rec.transaction_action_id = inv_globals.g_action_issue
2836 OR p_mmtt_rec.transaction_action_id = inv_globals.g_action_subxfr
2837 OR p_mmtt_rec.transaction_action_id = inv_globals.g_action_orgxfr
2838 OR p_mmtt_rec.transaction_action_id = inv_globals.g_action_intransitshipment)
2839 THEN -- For a ship confirm transaction, get the cost group from the content lpn ID
2840 l_lpn_id := p_mmtt_rec.content_lpn_id;
2841 /* Bug 4628878: For staging transfers, when whole LPN is being transfered, cost group should
2842 * be obtained from content_lpn_id */
2843 ELSIF (
2844 ((p_mmtt_rec.transaction_source_type_id = inv_globals.g_sourcetype_salesorder
2845 OR p_mmtt_rec.transaction_source_type_id = inv_globals.g_sourcetype_intorder)
2846 AND (p_mmtt_rec.transaction_action_id = inv_globals.g_action_stgxfr))
2847 OR/*Bug 6499833:For move order sub transfers,trying to get the costgroup from content_lpn_id.*/
2848 (( p_mmtt_rec.transaction_source_type_id = inv_globals.g_sourcetype_moveorder)
2849 AND (p_mmtt_rec.transaction_action_id = inv_globals.g_action_subxfr))
2850 )
2851 -- AND (p_mmtt_rec.lpn_id IS NULL) Bug#6770593
2852 AND (p_mmtt_rec.content_lpn_id IS NOT NULL)
2853 AND (p_mmtt_rec.inventory_item_id <> -1)
2854 THEN -- For a staging transfer transaction with content_lpn_id, get the cost group from the content lpn ID
2855 l_lpn_id := p_mmtt_rec.content_lpn_id;
2856 END IF;
2857 IF (l_debug = 1) THEN
2858 print_debug('l_lpn_id is set to: '||l_lpn_id);
2859 END IF;
2860
2861 IF l_onhand_exists THEN
2862
2863 IF i=1 THEN --When the l_serial_table is empty
2864 l_serial_table(l_sti).from_serial_number := rec_msn.serial_number;
2865 l_serial_table(l_sti).to_serial_number := rec_msn.serial_number;
2866 l_serial_table(l_sti).cost_group_id := rec_msn.cost_group_id;
2867
2868 l_serial_table(l_sti).update_msnt := TRUE;
2869 l_msnt_rowid_table(l_sti) := rec_msnt.msnt_rowid;
2870
2871 l_msnt_table(l_sti) := rec_msnt;
2872
2873 l_sti := l_sti + 1;
2874 i := i + 1;
2875 ELSIF i<>1 THEN -- When there are records in l_serial_table
2876 -- If the Cost Group ID of this record is the same as that of
2877 -- the previous record then extend the serial number range of
2878 -- the previous record otherwise insert a new record
2879 IF rec_msn.cost_group_id = l_serial_table(l_sti-1).cost_group_id THEN
2880 l_serial_table(l_sti-1).to_serial_number := rec_msn.serial_number;
2881 ELSE
2882 l_serial_table(l_sti).from_serial_number := rec_msn.serial_number;
2883 l_serial_table(l_sti).to_serial_number := rec_msn.serial_number;
2884 l_serial_table(l_sti).cost_group_id := rec_msn.cost_group_id;
2885
2886 l_serial_table(l_sti).update_msnt := FALSE;
2887
2888 l_msnt_table(l_sti) := rec_msnt;
2889
2890 l_sti := l_sti + 1;
2891 END IF;
2892 END IF;
2893
2894 IF rec_msn.cost_group_id IS NULL THEN
2895 proc_get_pending_costgroup(p_organization_id => p_mmtt_rec.organization_id,
2896 p_inventory_item_id => p_mmtt_rec.inventory_item_id,
2897 p_subinventory_code => p_mmtt_rec.subinventory_code,
2898 p_locator_id => p_mmtt_rec.locator_id,
2899 p_revision => p_mmtt_rec.revision,
2900 p_lot_number => p_mmtt_rec.lot_number,
2901 p_serial_number => rec_msn.serial_number,
2902 p_lpn_id => p_mmtt_rec.lpn_id,
2903 p_transaction_action_id => p_mmtt_rec.transaction_action_id,
2904 x_cost_group_id => l_cost_group_id,
2905 x_return_status => x_return_status);
2906 rec_msn.cost_group_id := l_cost_group_id;
2907 IF x_return_status = fnd_api.g_ret_sts_error THEN
2908 RAISE fnd_api.g_exc_error;
2909 END IF;
2910 END IF;
2911
2912 IF l_cg_quantity_table.exists(rec_msn.cost_group_id) THEN
2913 l_cg_quantity_table(rec_msn.cost_group_id).quantity :=
2914 l_cg_quantity_table(rec_msn.cost_group_id).quantity + 1;
2915 ELSE
2916 IF l_cg_quantity_table.COUNT = 0 THEN
2917 -- If the table is empty then the existing
2918 -- transaction_temp_id should be used as the
2919 -- new_transaction_temp_id also
2920 l_cg_quantity_table(rec_msn.cost_group_id).new_transaction_temp_id := p_mmtt_rec.transaction_temp_id;
2921 l_cg_quantity_table(rec_msn.cost_group_id).update_mmtt := TRUE;
2922 ELSE
2923 -- otherwise generate a new_transaction_temp_id
2924 SELECT mtl_material_transactions_s.NEXTVAL
2925 INTO l_transaction_temp_id
2926 FROM dual;
2927 IF (l_debug = 1) THEN
2928 print_debug('l_transaction_temp_id: ' || l_transaction_temp_id);
2929 END IF;
2930 l_cg_quantity_table(rec_msn.cost_group_id).new_transaction_temp_id := l_transaction_temp_id;
2931 l_cg_quantity_table(rec_msn.cost_group_id).update_mmtt := FALSE;
2932 END IF;
2933 l_cg_quantity_table(rec_msn.cost_group_id).quantity := 1;
2934 END IF;
2935 END IF;
2936 END LOOP;
2937
2938 FETCH cur_msnt INTO rec_msnt;
2939 END LOOP;
2940
2941 CLOSE cur_msnt;
2942
2943 -- Insert or update the records in l_cg_quantity_table into MMTT
2944 IF (l_debug = 1) THEN
2945 print_debug('proc_process_serial..Inserting records INTO MMTT ');
2946 print_debug('count: ' || l_cg_quantity_table.count);
2947 END IF;
2948 IF l_cg_quantity_table.COUNT > 0 THEN
2949 i := l_cg_quantity_table.first;
2950 IF p_mmtt_rec.transaction_quantity >= 0 THEN
2951 l_quantity_sign := 1;
2952 ELSE
2953 l_quantity_sign := -1;
2954 END IF;
2955
2956 LOOP
2957 l_transaction_quantity := inv_convert.inv_um_convert
2958 (p_mmtt_rec.inventory_item_id,
2959 5,
2960 l_cg_quantity_table(i).quantity,
2961 p_mmtt_rec.item_primary_uom_code,
2962 p_mmtt_rec.transaction_uom,
2963 NULL,
2964 NULL);
2965
2966 l_transaction_quantity := l_transaction_quantity * l_quantity_sign;
2967 IF (l_debug = 1) THEN
2968 print_debug('qty: ' || l_transaction_quantity);
2969 print_debug('qty sign: ' || l_quantity_sign);
2970 END IF;
2971 IF l_cg_quantity_table(i).update_mmtt = FALSE THEN
2972 IF (l_debug = 1) THEN
2973 print_debug('trx_temp_id: ' || l_cg_quantity_table(i).new_transaction_temp_id);
2974 END IF;
2975
2976 proc_insert_mmtt(p_mmtt_rec,
2977 p_transfer_wms_org,
2978 p_fob_point,
2979 p_tfr_primary_cost_method,
2980 p_tfr_org_cost_group_id,
2981 i, -- Remember that i is also the cost_group_id of the record
2982 NULL,
2983 l_cg_quantity_table(i).quantity * l_quantity_sign,
2984 l_transaction_quantity,
2985 l_cg_quantity_table(i).new_transaction_temp_id,
2986 p_from_project_id,
2987 p_to_project_id,
2988 x_return_status);
2989
2990 IF (l_debug = 1) THEN
2991 print_debug('proc_insert_mmtt return : ' || x_return_status);
2992 END IF;
2993 ELSE
2994 proc_update_mmtt(p_mmtt_rec.transaction_temp_id,
2995 p_transfer_wms_org,
2996 p_fob_point,
2997 p_tfr_primary_cost_method,
2998 p_tfr_org_cost_group_id,
2999 p_mmtt_rec.transaction_action_id,
3000 p_mmtt_rec.transfer_organization,
3001 p_mmtt_rec.transfer_subinventory,
3002 i, -- Remember that i is also the cost_group_id of the record
3003 NULL,
3004 l_cg_quantity_table(i).quantity * l_quantity_sign,
3005 l_transaction_quantity,
3006 p_from_project_id,
3007 p_to_project_id,
3008 x_return_status);
3009
3010 IF (l_debug = 1) THEN
3011 print_debug('proc_update_mmtt return : ' || x_return_status);
3012 END IF;
3013 END IF;
3014 IF (x_return_status = fnd_api.g_ret_sts_error)
3015 THEN
3016 RAISE fnd_api.g_exc_error ;
3017 END IF;
3018
3019 IF (x_return_status = fnd_api.g_ret_sts_unexp_error)
3020 THEN
3021 RAISE fnd_api.g_exc_unexpected_error ;
3022 END IF;
3023
3024 EXIT WHEN i = l_cg_quantity_table.last;
3025 i := l_cg_quantity_table.next(i);
3026 END LOOP;
3027
3028 -- Insert or update the records in l_serial_table into MSNT
3029 IF (l_debug = 1) THEN
3030 print_debug('IN proc_process_serial..Inserting records INTO MSNT');
3031 END IF;
3032 FOR i IN 1..l_serial_table.COUNT LOOP
3033 IF (l_debug = 1) THEN
3034 print_debug('cg_id: ' || l_serial_table(i).cost_group_id);
3035 print_debug('FSN: ' || l_serial_table(i).from_serial_number);
3036 print_debug('TSN: ' || l_serial_table(i).to_serial_number);
3037 print_debug('txn tmp id: ' || l_cg_quantity_table(l_serial_table(i).cost_group_id).new_transaction_temp_id);
3038 END IF;
3039
3040 IF l_serial_table(i).update_msnt = TRUE THEN -- Update the MSNT records
3041 IF (l_debug = 1) THEN
3042 print_debug('updating MSNT ');
3043 print_debug('row_id: ' || l_msnt_rowid_table(i));
3044 END IF;
3045 proc_update_msnt(l_msnt_rowid_table(i),
3046 l_cg_quantity_table(l_serial_table(i).cost_group_id).new_transaction_temp_id,
3047 l_serial_table(i).from_serial_number,
3048 l_serial_table(i).to_serial_number,
3049 x_return_status);
3050
3051 IF (l_debug = 1) THEN
3052 print_debug('proc_update_msnt return : ' || x_return_status);
3053 END IF;
3054 ELSE -- Insert into MSNT to create new records
3055 IF (l_debug = 1) THEN
3056 print_debug('inserting into MSNT ');
3057 END IF;
3058 proc_insert_msnt(l_msnt_table(i),
3059 l_serial_table(i).from_serial_number,
3060 l_serial_table(i).to_serial_number,
3061 l_cg_quantity_table(l_serial_table(i).cost_group_id).new_transaction_temp_id,
3062 x_return_status);
3063
3064 IF (l_debug = 1) THEN
3065 print_debug('proc_insert_msnt return : ' || x_return_status);
3066 END IF;
3067 END IF;
3068
3069 IF (x_return_status = fnd_api.g_ret_sts_error)
3070 THEN
3071 RAISE fnd_api.g_exc_error ;
3072 END IF;
3073
3074 IF (x_return_status = fnd_api.g_ret_sts_unexp_error)
3075 THEN
3076 RAISE fnd_api.g_exc_unexpected_error ;
3077 END IF;
3078
3079 END LOOP;
3080 END IF;
3081
3082 EXCEPTION
3083 WHEN FND_API.G_EXC_ERROR THEN
3084 IF (l_debug = 1) THEN
3085 print_debug('proc_process_serial .. EXCEP G_EXC_ERROR : ' );
3086 END IF;
3087 x_return_status := FND_API.G_RET_STS_ERROR ;
3088 IF cur_msnt%isopen THEN
3089 CLOSE cur_msnt;
3090 END IF;
3091 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3092 IF (l_debug = 1) THEN
3093 print_debug('proc_process_serial .. EXCEP G_EXC_UNEXPECTED_ERROR : ' );
3094 END IF;
3095 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3096 IF cur_msnt%isopen THEN
3097 CLOSE cur_msnt;
3098 END IF;
3099 WHEN OTHERS THEN
3100 IF (l_debug = 1) THEN
3101 print_debug('proc_process_serial .. EXCEP OTHERS:' || SQLERRM(SQLCODE) );
3102 END IF;
3103 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3104 IF cur_msnt%isopen THEN
3105 CLOSE cur_msnt;
3106 END IF;
3107 END proc_process_serials;
3108
3109 -- Processes the lot and serial controlled items. This involves splitting
3110 -- MMTT, MTLT and MSNT lines so that each row corresponds to a unique cost
3111 -- group.
3112 PROCEDURE proc_process_lot_serial
3113 (p_mmtt_rec IN mtl_material_transactions_temp%ROWTYPE,
3114 p_fob_point IN NUMBER,
3115 p_transfer_wms_org IN BOOLEAN,
3116 p_tfr_primary_cost_method IN NUMBER,
3117 p_tfr_org_cost_group_id IN NUMBER,
3118 p_from_project_id IN NUMBER,
3119 p_to_project_id IN NUMBER,
3120 x_return_status OUT NOCOPY VARCHAR2,
3121 x_msg_count OUT NOCOPY NUMBER,
3122 x_msg_data OUT NOCOPY VARCHAR2)
3123 IS
3124 l_api_name CONSTANT VARCHAR2(100) := 'proc_process_lot_serial';
3125 l_transaction_temp_id NUMBER := NULL;
3126 i INTEGER;
3127 j INTEGER;
3128 l_transaction_quantity NUMBER;
3129 l_quantity_sign NUMBER;
3130 l_cost_group_id NUMBER;
3131 l_ser_trx_tmp_id NUMBER; -- bug 1936698
3132
3133 --Bug 3390284 Changed the logic in in this procedure to prevent the
3134 --splitting of mtlt if not necessary.
3135 --1. Changed the existing structure 'lot_serial_record', holding
3136 --lot/serial information to 'serial_record'. The structure 'serial_record'
3137 --now holds information about records IN msnt only
3138 --2.Added New record type lot_record, holds information about a row in mtlt
3139 --3.lot_cg_quantity record is indexed by cost_group and holds the quantity
3140 --against that cost_group, also holds the serial_transaction_temp_id
3141 --This is cleared before processing each mtlt record, built while
3142 --processing that record, and after processing the mtlt record completely,
3143 --the information in this table is copied to to lot_table
3144
3145 -- For putting records in MTLT and MSNT tables
3146 TYPE serial_record IS RECORD
3147 (from_serial_number mtl_serial_numbers.serial_number%TYPE,
3148 to_serial_number mtl_serial_numbers.serial_number%TYPE,
3149 --lot_number mtl_serial_numbers.lot_number%TYPE,
3150 quantity NUMBER,
3151 cost_group_id NUMBER,
3152 new_serial_transaction_temp_id NUMBER,
3153 --update_mtlt BOOLEAN,
3154 update_msnt BOOLEAN);
3155
3156 TYPE serial_table IS TABLE OF serial_record INDEX BY BINARY_INTEGER;
3157 l_serial_table serial_table;
3158 l_sti INTEGER := 1;
3159
3160 TYPE cg_quantity_record IS RECORD
3161 (new_transaction_temp_id NUMBER,
3162 quantity NUMBER,
3163 update_mmtt BOOLEAN);
3164 TYPE cg_quantity_table IS TABLE OF cg_quantity_record INDEX BY BINARY_INTEGER;
3165 l_cg_quantity_table cg_quantity_table;
3166
3167 --Bug 3390284
3168 TYPE lot_cg_qty_record IS RECORD
3169 (quantity NUMBER,
3170 serial_transaction_temp_id NUMBER);
3171
3172 TYPE lot_cg_qty_table_tp IS TABLE OF lot_cg_qty_record INDEX BY BINARY_INTEGER;
3173 lot_cg_qty_table lot_cg_qty_table_tp;
3174
3175 TYPE lot_record IS RECORD
3176 (mtlt_rowid ROWID,
3177 lot_number mtl_lot_numbers.lot_number%TYPE,
3178 quantity NUMBER,
3179 serial_transaction_temp_id NUMBER,
3180 cost_group_id NUMBER,
3181 update_mtlt boolean);
3182
3183
3184 TYPE lot_table_tp IS TABLE OF lot_record INDEX BY BINARY_INTEGER;
3185 l_lot_table lot_table_tp;
3186 l_lti INTEGER := 0;
3187 --Bug 3390284
3188
3189 TYPE mtlt_rowid_table IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
3190 l_mtlt_rowid_table mtlt_rowid_table;
3191
3192 TYPE msnt_rowid_table IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
3193 l_msnt_rowid_table msnt_rowid_table;
3194
3195 TYPE msnt_table IS TABLE OF cur_msnt%ROWTYPE INDEX BY BINARY_INTEGER;
3196 l_msnt_table msnt_table;
3197
3198 TYPE mtlt_table IS TABLE OF cur_mtlt%ROWTYPE INDEX BY BINARY_INTEGER;
3199 l_mtlt_table mtlt_table;
3200
3201 rec_mtlt cur_mtlt%ROWTYPE;
3202 rec_msnt cur_msnt%ROWTYPE;
3203
3204 call_lot_control BOOLEAN := FALSE;
3205
3206 l_onhand_exists BOOLEAN;
3207 l_lpn_id NUMBER := p_mmtt_rec.lpn_id;
3208 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3209 l_last NUMBER;
3210 lot_cgi NUMBER;
3211
3212 --Bug 3686015 fix
3213 l_temp_prefix VARCHAR2(30):=NULL;
3214 l_from_ser_number NUMBER := NULL;
3215 l_fm_ser_length NUMBER := NULL;
3216 l_to_temp_prefix VARCHAR2(30) := NULL;
3217 l_to_ser_number NUMBER := NULL;
3218 --Bug 3686015 fix
3219 BEGIN
3220 x_return_status := fnd_api.g_ret_sts_success;
3221 SAVEPOINT sp_proc_process_lot_serial;
3222
3223 IF (l_debug = 1) THEN
3224 print_debug('IN proc_process_lot_serial.. ');
3225 END IF;
3226 --3390284
3227 l_lot_table.DELETE;
3228 --3390284
3229 OPEN cur_mtlt(p_mmtt_rec.transaction_temp_id);
3230
3231 FETCH cur_mtlt INTO rec_mtlt;
3232
3233 WHILE cur_mtlt%found LOOP
3234 IF (l_debug = 1) THEN
3235 print_debug('trx_id: ' || p_mmtt_rec.transaction_temp_id);
3236 END IF;
3237 j := 1;
3238
3239 --3390284
3240 lot_cg_qty_table.DELETE;
3241 --3390284
3242
3243 OPEN cur_msnt(rec_mtlt.serial_transaction_temp_id);
3244
3245 FETCH cur_msnt INTO rec_msnt;
3246
3247 -- If the serial control is dynamic entry at sales order issue check if
3248 -- there are any records in MSNT. If there are no records there treat the
3249 -- item as a lot control item.
3250 IF p_mmtt_rec.item_serial_control_code = 6 AND cur_msnt%notfound THEN
3251 CLOSE cur_msnt;
3252 call_lot_control := TRUE;
3253 EXIT;
3254 ELSE
3255 WHILE cur_msnt%found LOOP
3256 /* Bug 2424354: The variable i has to be reset to 1 for each record from MSNT
3257 rather than for each record from MTLT */
3258 i := 1;
3259 IF (l_debug = 1) THEN
3260 print_debug('ser_trx_id: ' || rec_mtlt.serial_transaction_temp_id);
3261 print_debug('FSN: ' || rec_msnt.fm_serial_number);
3262 print_debug('TSN: ' || rec_msnt.to_serial_number);
3263 print_debug('ORG: ' || p_mmtt_rec.organization_id );
3264 print_debug('ITEM: ' || p_mmtt_rec.inventory_item_id);
3265 END IF;
3266
3267 --Bug 3686015
3268 inv_validate.number_from_sequence(rec_msnt.fm_serial_number, l_temp_prefix, l_from_ser_number);
3269 l_fm_ser_length := Length(rec_msnt.fm_serial_number);
3270
3271 IF (l_debug = 1) THEN
3272 print_debug('FSNPREFIX: ' || l_temp_prefix);
3273 print_debug('FSNNUMERIC: ' || l_from_ser_number);
3274 print_debug('FSNLENGTH: ' || l_fm_ser_length);
3275 END IF;
3276
3277 IF (rec_msnt.to_serial_number IS NOT NULL) AND
3278 (rec_msnt.to_serial_number <> rec_msnt.fm_serial_number) THEN
3279 IF Length(rec_msnt.to_serial_number)<>l_fm_ser_length THEN
3280 IF (l_debug = 1) THEN
3281 print_debug('ERROR: Length of FSN diff from TSN');
3282 END IF;
3283 fnd_message.set_name('INV', 'INV_FROM_TO_SER_DIFF_LENGTH');
3284 fnd_message.set_token('FM_SER_NUM',rec_msnt.fm_serial_number);
3285 fnd_message.set_token('TO_SER_NUM', rec_msnt.to_serial_number);
3286 fnd_msg_pub.add;
3287 RAISE fnd_api.g_exc_error;
3288 END IF;
3289
3290 -- get the number part of the to serial
3291 inv_validate.number_from_sequence(rec_msnt.to_serial_number, l_to_temp_prefix, l_to_ser_number);
3292
3293 IF (l_debug = 1) THEN
3294 print_debug('TSNPREFIX: ' || l_to_temp_prefix);
3295 print_debug('TSNNUMERIC: ' || l_to_ser_number);
3296 END IF;
3297
3298 IF (l_temp_prefix IS NOT NULL) AND (l_to_temp_prefix IS NOT NULL) AND
3299 (l_to_temp_prefix <> l_temp_prefix) THEN
3300 IF (l_debug = 1) THEN
3301 print_debug('ERROR: From serial prefix different from to serial prefix');
3302 END IF;
3303 fnd_message.set_name('INV', 'INV_FROM_TO_SER_DIFF_PFX');
3304 fnd_message.set_token('FM_SER_NUM',rec_msnt.fm_serial_number);
3305 fnd_message.set_token('TO_SER_NUM', rec_msnt.to_serial_number);
3306 fnd_msg_pub.add;
3307 RAISE fnd_api.g_exc_error;
3308 END IF;
3309
3310 END IF;
3311 --Bug 3686015
3312
3313
3314
3315
3316 FOR rec_msn IN cur_msn(rec_msnt.fm_serial_number,
3317 rec_msnt.to_serial_number,
3318 p_mmtt_rec.inventory_item_id,
3319 p_mmtt_rec.organization_id,
3320 l_temp_prefix,
3321 l_fm_ser_length)
3322 LOOP
3323 IF (l_debug = 1) THEN
3324 print_debug('In MSN cursor');
3325 END IF;
3326 l_onhand_exists := TRUE;
3327
3328 -- Adjustment transactions
3329 IF p_mmtt_rec.transaction_action_id IN (inv_globals.g_type_cycle_count_adj,
3330 inv_globals.g_type_physical_count_adj,
3331 inv_globals.g_action_deliveryadj)
3332 THEN
3333 IF p_mmtt_rec.transaction_action_id = inv_globals.g_type_physical_count_adj THEN
3334 IF p_mmtt_rec.lpn_id IS NOT NULL THEN
3335 l_lpn_id := p_mmtt_rec.lpn_id;
3336 ELSIF p_mmtt_rec.content_lpn_id IS NOT NULL THEN
3337 l_lpn_id := p_mmtt_rec.content_lpn_id;
3338 ELSIF p_mmtt_rec.transfer_lpn_id IS NOT NULL THEN
3339 l_lpn_id := p_mmtt_rec.transfer_lpn_id;
3340 END IF;
3341 ELSIF p_mmtt_rec.transaction_action_id = inv_globals.g_action_deliveryadj THEN
3342 l_lpn_id := p_mmtt_rec.transfer_lpn_id;
3343 ELSIF p_mmtt_rec.transaction_action_id = inv_globals.g_type_cycle_count_adj THEN
3344 l_lpn_id := p_mmtt_rec.transfer_lpn_id;
3345 END IF;
3346
3347 l_onhand_exists :=
3348 onhand_quantity_exists
3349 (p_inventory_item_id => p_mmtt_rec.inventory_item_id,
3350 p_revision => p_mmtt_rec.revision,
3351 p_organization_id => p_mmtt_rec.organization_id,
3352 p_subinventory_code => p_mmtt_rec.subinventory_code,
3353 p_locator_id => p_mmtt_rec.locator_id,
3354 p_lot_number => rec_mtlt.lot_number,
3355 p_serial_number => rec_msn.serial_number,
3356 p_lpn_id => l_lpn_id);
3357 IF NOT l_onhand_exists THEN
3358 IF (l_debug = 1) THEN
3359 print_debug('Treating this as as receipt transaction...: ');
3360 print_debug('Getting transfer cost group id from rules engine...: ');
3361 END IF;
3362 wms_costgroupengine_pvt.assign_cost_group
3363 (p_api_version => 1.0,
3364 p_init_msg_list => FND_API.G_FALSE,
3365 p_commit => FND_API.G_FALSE,
3366 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3367 x_return_status => x_return_Status,
3368 x_msg_count => x_msg_count,
3369 x_msg_data => x_msg_data,
3370 p_line_id => p_mmtt_rec.transaction_temp_id,
3371 p_input_type => wms_costgroupengine_pvt.g_input_mmtt);
3372
3373 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
3374 IF (l_debug = 1) THEN
3375 print_debug('return error from wms_costgroupengine_pvt');
3376 END IF;
3377 RAISE FND_API.G_EXC_ERROR;
3378 ELSIF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3379 IF (l_debug = 1) THEN
3380 print_debug('return unexpected error from wms_costgroupengine_pvt');
3381 END IF;
3382 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3383 END IF;
3384
3385 END IF;
3386 ELSIF (p_mmtt_rec.transaction_source_type_id = inv_globals.g_sourcetype_salesorder
3387 OR p_mmtt_rec.transaction_source_type_id = inv_globals.g_sourcetype_intorder)
3388 AND (p_mmtt_rec.transaction_action_id = inv_globals.g_action_issue
3389 OR p_mmtt_rec.transaction_action_id = inv_globals.g_action_subxfr
3390 OR p_mmtt_rec.transaction_action_id = inv_globals.g_action_orgxfr
3391 OR p_mmtt_rec.transaction_action_id = inv_globals.g_action_intransitshipment)
3392 THEN -- For a ship confirm transaction, get the cost group from the content lpn ID
3393 l_lpn_id := p_mmtt_rec.content_lpn_id;
3394
3395 --Bug 2631651 fix. For sales order issue transactions,if the serial control is set at
3396 --sales order issue msn wouldn't have cost group id
3397 --stamped so we have to get the cost group from onhand,
3398 --considering the item as a non serial controlled item
3399 --(by passing null for p_serial_number parameter)
3400
3401 IF p_mmtt_rec.item_serial_control_code = 6 AND rec_msn.cost_group_id IS NULL THEN
3402
3403 proc_determine_costgroup(p_organization_id => p_mmtt_rec.organization_id,
3404 p_inventory_item_id => p_mmtt_rec.inventory_item_id,
3405 p_subinventory_code => p_mmtt_rec.subinventory_code,
3406 p_locator_id => p_mmtt_rec.locator_id,
3407 p_revision => p_mmtt_rec.revision,
3408 p_lot_number => rec_mtlt.lot_number,
3409 p_serial_number => NULL,
3410 p_containerized_flag => 2, -- param is ignored by the PROCEDURE
3411 p_lpn_id => l_lpn_id,
3412 p_transaction_action_id => p_mmtt_rec.transaction_action_id,
3413 p_is_backflush_txn => FALSE,
3414 x_cost_group_id => l_cost_group_id,
3415 x_return_status => x_return_status);
3416
3417 rec_msn.cost_group_id := l_cost_group_id;
3418
3419 END IF;
3420 --Bug 2631651 fix
3421 /* Bug 4628878: For staging transfers, when whole LPN is being transfered, cost group should
3422 * be obtained from content_lpn_id */
3423 ELSIF (
3424 ((p_mmtt_rec.transaction_source_type_id = inv_globals.g_sourcetype_salesorder
3425 OR p_mmtt_rec.transaction_source_type_id = inv_globals.g_sourcetype_intorder)
3426 AND (p_mmtt_rec.transaction_action_id = inv_globals.g_action_stgxfr))
3427 OR/*Bug 6499833:For move order sub transfers,trying to get the costgroup from content_lpn_id.*/
3428 (( p_mmtt_rec.transaction_source_type_id = inv_globals.g_sourcetype_moveorder)
3429 AND (p_mmtt_rec.transaction_action_id = inv_globals.g_action_subxfr))
3430 )
3431 -- AND (p_mmtt_rec.lpn_id IS NULL) Bug#6770593
3432 AND (p_mmtt_rec.content_lpn_id IS NOT NULL)
3433 AND (p_mmtt_rec.inventory_item_id <> -1)
3434 THEN -- For a staging transfer transaction with content_lpn_id, get the cost group from the content lpn ID
3435 l_lpn_id := p_mmtt_rec.content_lpn_id;
3436
3437 --Bug 2631651 fix. For sales order issue transactions,if the serial control is set at
3438 --sales order issue msn wouldn't have cost group id
3439 --stamped so we have to get the cost group from onhand,
3440 --considering the item as a non serial controlled item
3441 --(by passing null for p_serial_number parameter)
3442
3443 IF p_mmtt_rec.item_serial_control_code = 6 AND rec_msn.cost_group_id IS NULL THEN
3444
3445 proc_determine_costgroup(p_organization_id => p_mmtt_rec.organization_id,
3446 p_inventory_item_id => p_mmtt_rec.inventory_item_id,
3447 p_subinventory_code => p_mmtt_rec.subinventory_code,
3448 p_locator_id => p_mmtt_rec.locator_id,
3449 p_revision => p_mmtt_rec.revision,
3450 p_lot_number => rec_mtlt.lot_number,
3451 p_serial_number => NULL,
3452 p_containerized_flag => 2, -- param is ignored by the PROCEDURE
3453 p_lpn_id => l_lpn_id,
3454 p_transaction_action_id => p_mmtt_rec.transaction_action_id,
3455 p_is_backflush_txn => FALSE,
3456 x_cost_group_id => l_cost_group_id,
3457 x_return_status => x_return_status);
3458
3459 rec_msn.cost_group_id := l_cost_group_id;
3460
3461 END IF;
3462 --Bug 2631651 fix
3463
3464 END IF;
3465
3466 IF l_onhand_exists THEN
3467
3468 IF i=1 THEN --When the l_serial_table is empty
3469 l_serial_table(l_sti).from_serial_number := rec_msn.serial_number;
3470 l_serial_table(l_sti).to_serial_number := rec_msn.serial_number;
3471 --l_serial_table(l_sti).lot_number := rec_mtlt.lot_number;
3472 l_serial_table(l_sti).cost_group_id := rec_msn.cost_group_id;
3473 l_serial_table(l_sti).quantity := 1;
3474
3475 IF lot_cg_qty_table.exists(rec_msn.cost_group_id) then
3476 l_serial_table(l_sti).new_serial_transaction_temp_id
3477 := lot_cg_qty_table(rec_msn.cost_group_id).serial_transaction_temp_id;
3478 --Bug 3390284
3479 lot_cg_qty_table(rec_msn.cost_group_id).quantity := lot_cg_qty_table(rec_msn.cost_group_id).quantity + 1;
3480 --Bug 3390284
3481 ELSE
3482 SELECT mtl_material_transactions_s.NEXTVAL
3483 INTO l_transaction_temp_id
3484 FROM dual;
3485
3486 l_serial_table(l_sti).new_serial_transaction_temp_id:= l_transaction_temp_id;
3487
3488 --Bug 3390284
3489 lot_cg_qty_table(rec_msn.cost_group_id).quantity := 1;
3490 lot_cg_qty_table(rec_msn.cost_group_id).serial_transaction_temp_id := l_transaction_temp_id;
3491 --Bug 3390284
3492 END IF;
3493
3494 l_serial_table(l_sti).update_msnt := TRUE;
3495 l_msnt_rowid_table(l_sti) := rec_msnt.msnt_rowid;
3496
3497 IF j = 1 THEN
3498 --l_serial_table(l_sti).update_mtlt := TRUE;
3499 l_mtlt_rowid_table(l_sti) := rec_mtlt.mtlt_rowid;
3500 j := j + 1;
3501 ELSE
3502 --l_serial_table(l_sti).update_mtlt := FALSE;
3503 NULL;
3504 END IF;
3505
3506 l_msnt_table(l_sti) := rec_msnt;
3507 --l_mtlt_table(l_sti) := rec_mtlt;
3508
3509 l_sti := l_sti + 1;
3510 i := i + 1;
3511 ELSIF i<>1 THEN -- When there are records in l_serial_table
3512 -- If the Cost Group ID of this record is the same as that of
3513 -- the previous record then extend the serial number range of
3514 -- the previous record otherwise insert a new record
3515 IF rec_msn.cost_group_id = l_serial_table(l_sti-1).cost_group_id THEN
3516 l_serial_table(l_sti-1).to_serial_number := rec_msn.serial_number;
3517 l_serial_table(l_sti-1).quantity := l_serial_table(l_sti-1).quantity + 1;
3518
3519 --3390284
3520 IF lot_cg_qty_table.exists(rec_msn.cost_group_id) then
3521 lot_cg_qty_table(rec_msn.cost_group_id).quantity :=
3522 lot_cg_qty_table(rec_msn.cost_group_id).quantity + 1 ;
3523 ELSE
3524 RAISE fnd_api.g_exc_error ;
3525 END IF;
3526 --Bug 3390284
3527 ELSE
3528 l_serial_table(l_sti).from_serial_number := rec_msn.serial_number;
3529 l_serial_table(l_sti).to_serial_number := rec_msn.serial_number;
3530 --l_serial_table(l_sti).lot_number := rec_mtlt.lot_number;
3531 l_serial_table(l_sti).cost_group_id := rec_msn.cost_group_id;
3532 l_serial_table(l_sti).quantity := 1;
3533
3534 SELECT mtl_material_transactions_s.NEXTVAL
3535 INTO l_transaction_temp_id
3536 FROM dual;
3537
3538 --3390284
3539 IF lot_cg_qty_table.exists(rec_msn.cost_group_id) then
3540 lot_cg_qty_table(rec_msn.cost_group_id).quantity :=
3541 lot_cg_qty_table(rec_msn.cost_group_id).quantity + 1 ;
3542 l_transaction_temp_id := lot_cg_qty_table(rec_msn.cost_group_id).serial_transaction_temp_id;
3543 ELSE
3544 lot_cg_qty_table(rec_msn.cost_group_id).quantity := 1;
3545 lot_cg_qty_table(rec_msn.cost_group_id).serial_transaction_temp_id := l_transaction_temp_id;
3546 END IF;
3547 --Bug 3390284
3548
3549 l_serial_table(l_sti).new_serial_transaction_temp_id:= l_transaction_temp_id;
3550 l_serial_table(l_sti).update_msnt := FALSE;
3551 -- l_serial_table(l_sti).update_mtlt := FALSE;
3552
3553 l_msnt_table(l_sti) := rec_msnt;
3554 --l_mtlt_table(l_sti) := rec_mtlt;
3555
3556 l_sti := l_sti + 1;
3557 END IF;
3558 END IF;
3559
3560
3561 IF rec_msn.cost_group_id IS NULL THEN
3562 proc_get_pending_costgroup(p_organization_id => p_mmtt_rec.organization_id,
3563 p_inventory_item_id => p_mmtt_rec.inventory_item_id,
3564 p_subinventory_code => p_mmtt_rec.subinventory_code,
3565 p_locator_id => p_mmtt_rec.locator_id,
3566 p_revision => p_mmtt_rec.revision,
3567 p_lot_number => p_mmtt_rec.lot_number,
3568 p_serial_number => rec_msn.serial_number,
3569 p_lpn_id => p_mmtt_rec.lpn_id,
3570 p_transaction_action_id => p_mmtt_rec.transaction_action_id,
3571 x_cost_group_id => l_cost_group_id,
3572 x_return_status => x_return_status);
3573 rec_msn.cost_group_id := l_cost_group_id;
3574 IF x_return_status = fnd_api.g_ret_sts_error THEN
3575 RAISE fnd_api.g_exc_error ;
3576 END IF;
3577 END IF;
3578
3579 IF l_cg_quantity_table.exists(rec_msn.cost_group_id) THEN
3580 l_cg_quantity_table(rec_msn.cost_group_id).quantity :=
3581 l_cg_quantity_table(rec_msn.cost_group_id).quantity + 1;
3582 ELSE
3583 IF l_cg_quantity_table.COUNT = 0 THEN
3584 -- If the table is empty then the existing
3585 -- transaction_temp_id should be used as the
3586 -- new_transaction_temp_id also
3587 l_cg_quantity_table(rec_msn.cost_group_id).new_transaction_temp_id := p_mmtt_rec.transaction_temp_id;
3588 l_cg_quantity_table(rec_msn.cost_group_id).update_mmtt := TRUE;
3589 ELSE
3590 -- otherwise generate a new_transaction_temp_id
3591 SELECT mtl_material_transactions_s.NEXTVAL
3592 INTO l_transaction_temp_id
3593 FROM dual;
3594 l_cg_quantity_table(rec_msn.cost_group_id).new_transaction_temp_id := l_transaction_temp_id;
3595 l_cg_quantity_table(rec_msn.cost_group_id).update_mmtt := FALSE;
3596 END IF;
3597 l_cg_quantity_table(rec_msn.cost_group_id).quantity := 1;
3598 END IF;
3599 END IF;
3600 END LOOP;
3601 FETCH cur_msnt INTO rec_msnt;
3602 END LOOP;
3603 END IF;
3604 IF (l_debug = 1) THEN
3605 print_debug('Closing MSNT cursor... ');
3606 END IF;
3607 CLOSE cur_msnt;
3608
3609 --Bug3390284
3610 --Copying information to l_lot_table
3611 IF lot_cg_qty_table.COUNT > 0 THEN
3612 l_last := lot_cg_qty_table.last;
3613 lot_cgi := lot_cg_qty_table.first;
3614 LOOP
3615 l_lti := l_lti + 1;
3616 l_lot_table(l_lti).mtlt_rowid := rec_mtlt.mtlt_ROWID;
3617 l_lot_table(l_lti).lot_number := rec_mtlt.lot_number;
3618 l_mtlt_table(l_lti) := rec_mtlt;
3619 l_lot_table(l_lti).quantity := lot_cg_qty_table(lot_cgi).quantity;
3620 l_lot_table(l_lti).serial_transaction_temp_id := lot_cg_qty_table(lot_cgi).serial_transaction_temp_id;
3621 l_lot_table(l_lti).cost_group_id := lot_cgi;
3622 IF lot_cgi = lot_cg_qty_table.first THEN
3623 l_lot_table(l_lti).update_mtlt := TRUE;
3624 ELSE
3625 l_lot_table(l_lti).update_mtlt := FALSE;
3626 END IF;
3627
3628 EXIT WHEN (l_last = lot_cgi);
3629
3630 lot_cgi := lot_cg_qty_table.next(lot_cgi);
3631
3632 END LOOP;
3633 END IF;
3634 --Bug3390284
3635
3636 FETCH cur_mtlt INTO rec_mtlt;
3637 END LOOP;
3638 IF (l_debug = 1) THEN
3639 print_debug('Closing MTLT cursor... ');
3640 END IF;
3641 CLOSE cur_mtlt;
3642
3643 -- If the item is to be treated as a lot control item then
3644 IF call_lot_control = TRUE THEN
3645 proc_process_lots
3646 (p_mmtt_rec => p_mmtt_rec,
3647 p_fob_point => p_fob_point,
3648 p_transfer_wms_org => p_transfer_wms_org,
3649 p_tfr_primary_cost_method => p_tfr_primary_cost_method,
3650 p_tfr_org_cost_group_id => p_tfr_org_cost_group_id,
3651 p_from_project_id => p_from_project_id,
3652 p_to_project_id => p_to_project_id,
3653 x_return_status => x_return_status,
3654 x_msg_count => x_msg_count,
3655 x_msg_data => x_msg_data);
3656 RETURN;
3657 END IF;
3658
3659 -- Insert or update the records in l_cg_quantity_table into MMTT
3660 IF (l_debug = 1) THEN
3661 print_debug('count: ' || l_cg_quantity_table.count);
3662 END IF;
3663
3664 IF l_cg_quantity_table.COUNT > 0 THEN
3665 IF (l_debug = 1) THEN
3666 print_debug('proc_process_lot_serial..Inserting records INTO MMTT ');
3667 END IF;
3668 i := l_cg_quantity_table.first;
3669 IF p_mmtt_rec.transaction_quantity >= 0 THEN
3670 l_quantity_sign := 1;
3671 ELSE
3672 l_quantity_sign := -1;
3673 END IF;
3674
3675 IF (l_debug = 1) THEN
3676 print_debug('Primary UOM: ' || p_mmtt_rec.item_primary_uom_code);
3677 print_debug('Txn UOM: ' || p_mmtt_rec.transaction_uom);
3678 print_debug('Qty: ' || l_cg_quantity_table(i).quantity);
3679 END IF;
3680 LOOP
3681 l_transaction_quantity := inv_convert.inv_um_convert
3682 (p_mmtt_rec.inventory_item_id,
3683 5,
3684 l_cg_quantity_table(i).quantity,
3685 p_mmtt_rec.item_primary_uom_code,
3686 p_mmtt_rec.transaction_uom,
3687 NULL,
3688 NULL);
3689 l_transaction_quantity := l_transaction_quantity * l_quantity_sign;
3690 IF (l_debug = 1) THEN
3691 print_debug('qty: ' || l_transaction_quantity);
3692 print_debug('qty sign: ' || l_quantity_sign);
3693 END IF;
3694 IF l_cg_quantity_table(i).update_mmtt = FALSE THEN
3695 proc_insert_mmtt(p_mmtt_rec,
3696 p_transfer_wms_org,
3697 p_fob_point,
3698 p_tfr_primary_cost_method,
3699 p_tfr_org_cost_group_id,
3700 i, -- Remember that i is also the cost_group_id of the record
3701 NULL,
3702 l_cg_quantity_table(i).quantity * l_quantity_sign,
3703 l_transaction_quantity,
3704 l_cg_quantity_table(i).new_transaction_temp_id,
3705 p_from_project_id,
3706 p_to_project_id,
3707 x_return_status);
3708
3709 IF (l_debug = 1) THEN
3710 print_debug('proc_insert_mmtt return : ' || x_return_status);
3711 END IF;
3712 ELSE
3713 proc_update_mmtt(p_mmtt_rec.transaction_temp_id,
3714 p_transfer_wms_org,
3715 p_fob_point,
3716 p_tfr_primary_cost_method,
3717 p_tfr_org_cost_group_id,
3718 p_mmtt_rec.transaction_action_id,
3719 p_mmtt_rec.transfer_organization,
3720 p_mmtt_rec.transfer_subinventory,
3721 i, -- Remember that i is also the cost_group_id of the record
3722 NULL,
3723 l_cg_quantity_table(i).quantity * l_quantity_sign,
3724 l_transaction_quantity,
3725 p_from_project_id,
3726 p_to_project_id,
3727 x_return_status);
3728
3729 IF (l_debug = 1) THEN
3730 print_debug('proc_update_mmtt return : ' || x_return_status);
3731 END IF;
3732 END IF;
3733
3734 IF (x_return_status = fnd_api.g_ret_sts_error)
3735 THEN
3736 RAISE fnd_api.g_exc_error;
3737 END IF;
3738
3739 IF (x_return_status = fnd_api.g_ret_sts_unexp_error)
3740 THEN
3741 RAISE fnd_api.g_exc_unexpected_error;
3742 END IF;
3743
3744 EXIT WHEN i = l_cg_quantity_table.last;
3745 i := l_cg_quantity_table.next(i);
3746 END LOOP;
3747
3748 -- Insert or update the records in l_lot_serial_table into MTLT and MSNT
3749 IF (l_debug = 1) THEN
3750 print_debug('IN proc_process_lot_serial..Inserting records INTO MTLT');
3751 END IF;
3752
3753 --3390284 Now inserting records into mtlt with the information from the lot
3754 --table
3755
3756 FOR lot_i IN 1..l_lot_table.COUNT LOOP
3757 l_transaction_quantity := inv_convert.inv_um_convert
3758 (p_mmtt_rec.inventory_item_id,
3759 5,
3760 l_lot_table(lot_i).quantity,
3761 p_mmtt_rec.item_primary_uom_code,
3762 p_mmtt_rec.transaction_uom,
3763 NULL,
3764 NULL);
3765 IF l_lot_table(lot_i).update_mtlt THEN
3766 proc_update_mtlt(l_lot_table(lot_i).mtlt_rowid,
3767 l_cg_quantity_table(l_lot_table(lot_i).cost_group_id).new_transaction_temp_id,
3768 l_lot_table(lot_i).lot_number,
3769 l_lot_table(lot_i).quantity,
3770 l_transaction_quantity,
3771 l_lot_table(lot_i).serial_transaction_temp_id,
3772 x_return_status);
3773
3774 ELSE
3775 proc_insert_mtlt(l_mtlt_table(lot_i),
3776 l_cg_quantity_table(l_lot_table(lot_i).cost_group_id).new_transaction_temp_id,
3777 l_lot_table(lot_i).quantity,
3778 l_transaction_quantity,
3779 l_lot_table(lot_i).serial_transaction_temp_id,
3780 x_return_status);
3781 END IF;
3782
3783 IF (x_return_status = fnd_api.g_ret_sts_error)
3784 THEN
3785 RAISE fnd_api.g_exc_error ;
3786 END IF;
3787
3788 IF (x_return_status = fnd_api.g_ret_sts_unexp_error)
3789 THEN
3790 RAISE fnd_api.g_exc_unexpected_error ;
3791 END IF;
3792
3793 END LOOP;
3794
3795 --3390284
3796
3797
3798 IF (l_debug = 1) THEN
3799 print_debug('IN proc_process_lot_serial..Inserting records INTO MSNT');
3800 END IF;
3801
3802 FOR i IN 1..l_serial_table.COUNT LOOP
3803
3804
3805 IF l_serial_table(i).update_msnt = TRUE THEN -- Update the MSNT records
3806 IF (l_debug = 1) THEN
3807 print_debug('updating MSNT ');
3808 END IF;
3809 proc_update_msnt(l_msnt_rowid_table(i),
3810 l_serial_table(i).new_serial_transaction_temp_id,
3811 l_serial_table(i).from_serial_number,
3812 l_serial_table(i).to_serial_number,
3813 x_return_status);
3814
3815 IF (l_debug = 1) THEN
3816 print_debug('proc_update_msnt return : ' || x_return_status);
3817 END IF;
3818 ELSE -- Insert into MSNT to create new records
3819 IF (l_debug = 1) THEN
3820 print_debug('inserting into MSNT ');
3821 END IF;
3822 proc_insert_msnt(l_msnt_table(i),
3823 l_serial_table(i).from_serial_number,
3824 l_serial_table(i).to_serial_number,
3825 l_serial_table(i).new_serial_transaction_temp_id,
3826 x_return_status);
3827
3828 IF (l_debug = 1) THEN
3829 print_debug('proc_insert_msnt return : ' || x_return_status);
3830 END IF;
3831 END IF;
3832
3833 IF (x_return_status = fnd_api.g_ret_sts_error)
3834 THEN
3835 RAISE fnd_api.g_exc_error ;
3836 END IF;
3837
3838 IF (x_return_status = fnd_api.g_ret_sts_unexp_error)
3839 THEN
3840 RAISE fnd_api.g_exc_unexpected_error ;
3841 END IF;
3842
3843 END LOOP;
3844 END IF;
3845 EXCEPTION
3846 WHEN FND_API.G_EXC_ERROR THEN
3847 IF (l_debug = 1) THEN
3848 print_debug('proc_process_lot_serial .. EXCEP G_EXC_ERROR : ' );
3849 END IF;
3850 x_return_status := FND_API.G_RET_STS_ERROR ;
3851 IF cur_msnt%isopen THEN
3852 CLOSE cur_msnt;
3853 END IF;
3854 IF cur_mtlt%isopen THEN
3855 CLOSE cur_mtlt;
3856 END IF;
3857 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3858 IF (l_debug = 1) THEN
3859 print_debug('proc_process_lot_serial .. EXCEP G_EXC_UNEXPECTED_ERROR : ' );
3860 END IF;
3861 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3862 IF cur_msnt%isopen THEN
3863 CLOSE cur_msnt;
3864 END IF;
3865 IF cur_mtlt%isopen THEN
3866 CLOSE cur_mtlt;
3867 END IF;
3868 WHEN OTHERS THEN
3869 IF (l_debug = 1) THEN
3870 print_debug('proc_process_lot_serial .. EXCEP OTHERS : ' || SQLERRM(SQLCODE));
3871 END IF;
3872 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3873 IF cur_msnt%isopen THEN
3874 CLOSE cur_msnt;
3875 END IF;
3876 IF cur_mtlt%isopen THEN
3877 CLOSE cur_mtlt;
3878 END IF;
3879 END proc_process_lot_serial;
3880
3881 -- Main Logic
3882
3883 PROCEDURE cost_group_update
3884 (p_transaction_rec IN mtl_material_transactions_temp%ROWTYPE,
3885 p_fob_point IN mtl_interorg_parameters.fob_point%TYPE DEFAULT NULL,
3886 p_transfer_wms_org IN BOOLEAN DEFAULT TRUE,
3887 p_tfr_primary_cost_method IN NUMBER,
3888 p_tfr_org_cost_group_id IN NUMBER,
3889 p_from_project_id IN NUMBER DEFAULT NULL,
3890 p_to_project_id IN NUMBER DEFAULT NULL,
3891 x_return_status OUT NOCOPY VARCHAR2,
3892 x_msg_count OUT NOCOPY NUMBER,
3893 x_msg_data OUT NOCOPY VARCHAR2)
3894 IS
3895 l_api_name CONSTANT VARCHAR2(50) := 'cost_group_update';
3896 l_txn_temp_id NUMBER := 0;
3897 l_prev_rowid ROWID := NULL;
3898 l_prev_org_id mtl_parameters.organization_id%TYPE := NULL;
3899 l_primary_qty NUMBER := NULL;
3900 l_is_lot_control BOOLEAN := NULL;
3901 l_is_serial_control BOOLEAN := NULL;
3902 l_cost_group_id NUMBER := 0;
3903 l_mmtt_rec mtl_material_transactions_temp%ROWTYPE;
3904 l_lpn_id NUMBER := p_transaction_rec.lpn_id;
3905 l_onhand_exists BOOLEAN := TRUE;
3906 l_is_backflush_txn BOOLEAN := FALSE;
3907 l_lot_number VARCHAR2(80); --- BUG#4291891 Joe DiIorio 04/08/2005
3908 l_transfer_cost_group_id NUMBER;
3909 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3910 BEGIN
3911 -- Standard Start of API savepoint
3912
3913 SAVEPOINT API_updatecostgroups;
3914 x_return_status := fnd_api.g_ret_sts_success;
3915
3916 IF (l_debug = 1) THEN
3917 print_debug('Beginning get_cost_group.... with temp_id: ' ||
3918 l_mmtt_rec.transaction_temp_id);
3919 END IF;
3920
3921 l_mmtt_rec := p_transaction_rec;
3922
3923 -- If this is a DirectOrg or an IntransitShipment Transaction and
3924 -- the transfer CostGroupId is NULL, then run the RulesEngine
3925 -- to pick the appropriate CostGroupId for the transfer side
3926 l_cost_group_id := l_mmtt_rec.cost_group_id;
3927 l_transfer_cost_group_id := l_mmtt_rec.transfer_cost_group_id;
3928
3929 IF l_transfer_cost_group_id IS NULL AND p_transfer_wms_org AND
3930 ((l_mmtt_rec.transaction_action_id = inv_globals.g_action_orgxfr) OR
3931 (l_mmtt_rec.transaction_action_id = inv_globals.g_action_intransitshipment
3932 AND p_fob_point = 1)
3933 OR (l_mmtt_rec.transaction_action_id = inv_globals.g_action_intransitreceipt)
3934 )
3935 THEN
3936 IF (l_debug = 1) THEN
3937 print_debug('Getting transfer cost group id from rules engine...: ');
3938 END IF;
3939 wms_costgroupengine_pvt.assign_cost_group
3940 (p_api_version => 1.0,
3941 p_init_msg_list => FND_API.G_FALSE,
3942 p_commit => FND_API.G_FALSE,
3943 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3944 x_return_status => x_return_Status,
3945 x_msg_count => x_msg_count,
3946 x_msg_data => x_msg_data,
3947 p_line_id => l_mmtt_rec.transaction_temp_id,
3948 p_input_type => wms_costgroupengine_pvt.g_input_mmtt);
3949
3950 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
3951 IF (l_debug = 1) THEN
3952 print_debug('return error from wms_costgroupengine_pvt');
3953 END IF;
3954 RAISE FND_API.G_EXC_ERROR;
3955 ELSIF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3956 IF (l_debug = 1) THEN
3957 print_debug('return unexpected error from wms_costgroupengine_pvt');
3958 END IF;
3959 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3960 END IF;
3961
3962 -- Requery MMTT to get the fresh records
3963 SELECT *
3964 INTO l_mmtt_rec
3965 FROM mtl_material_transactions_temp
3966 WHERE transaction_temp_id = l_mmtt_rec.transaction_temp_id;
3967
3968 IF (l_debug = 1) THEN
3969 print_debug('CG from RULES engine: ' || l_mmtt_rec.cost_group_id);
3970 print_debug('TCG from RULES engine: ' || l_mmtt_rec.transfer_cost_group_id);
3971 END IF;
3972 END IF;
3973
3974 -- If transaction is not lpn triggered...
3975 IF l_mmtt_rec.inventory_item_id <> -1 THEN
3976 SELECT lot_control_code, serial_number_control_code, primary_uom_code
3977 INTO l_mmtt_rec.item_lot_control_code,
3978 l_mmtt_rec.item_serial_control_code, l_mmtt_rec.item_primary_uom_code
3979 FROM mtl_system_items
3980 WHERE organization_id = l_mmtt_rec.organization_id
3981 AND inventory_item_id = l_mmtt_rec.inventory_item_id;
3982 ELSE
3983 RETURN;
3984 END IF;
3985
3986 IF inv_globals.is_issue_xfr_transaction(l_mmtt_rec.transaction_action_id) THEN
3987 IF l_mmtt_rec.transfer_cost_group_id IS NOT NULL
3988 AND l_mmtt_rec.cost_group_id IS NOT NULL THEN
3989 RETURN;
3990 ELSIF l_mmtt_rec.cost_group_id IS NOT NULL AND
3991 l_mmtt_rec.transfer_cost_group_id IS NULL THEN
3992 proc_update_mmtt(l_mmtt_rec.transaction_temp_id,
3993 p_transfer_wms_org,
3994 p_fob_point,
3995 p_tfr_primary_cost_method,
3996 p_tfr_org_cost_group_id,
3997 l_mmtt_rec.transaction_action_id,
3998 l_mmtt_rec.transfer_organization,
3999 l_mmtt_rec.transfer_subinventory,
4000 l_cost_group_id,
4001 NULL,
4002 NULL,
4003 NULL,
4004 p_from_project_id,
4005 p_to_project_id,
4006 x_return_status);
4007 IF (l_debug = 1) THEN
4008 print_debug('proc_update_mmtt : ' || x_return_status);
4009 END IF;
4010 IF (x_return_status = fnd_api.g_ret_sts_error)
4011 THEN
4012 RAISE fnd_api.g_exc_error ;
4013 END IF;
4014
4015 IF (x_return_status = fnd_api.g_ret_sts_unexp_error)
4016 THEN
4017 RAISE fnd_api.g_exc_unexpected_error ;
4018 END IF;
4019 RETURN;
4020 END IF;
4021
4022 END IF;
4023
4024 IF (l_debug = 1) THEN
4025 print_debug('Org: ' || l_mmtt_rec.organization_id);
4026 print_debug('Item: ' || l_mmtt_rec.inventory_item_id);
4027 print_debug('Lot control code: ' || l_mmtt_rec.item_lot_control_code);
4028 print_debug('Serial control code: ' || l_mmtt_rec.item_serial_control_code);
4029 print_debug('Action ID: ' || l_mmtt_rec.transaction_action_id);
4030 print_debug('Primary UOM: ' || l_mmtt_rec.item_primary_uom_code);
4031 print_debug('Txn UOM: ' || l_mmtt_rec.transaction_uom);
4032 print_debug('Qty: ' || l_mmtt_rec.transaction_quantity);
4033 END IF;
4034
4035 l_txn_temp_id := l_mmtt_rec.transaction_header_id;
4036 IF (l_debug = 1) THEN
4037 print_debug('header_id:'||l_txn_temp_id || ':');
4038 END IF;
4039
4040 IF l_mmtt_rec.item_lot_control_code = 2
4041 THEN
4042 l_is_lot_control := TRUE;
4043 ELSE
4044 l_is_lot_control := FALSE;
4045 END IF;
4046
4047 IF l_mmtt_rec.item_serial_control_code = 1
4048 THEN
4049 IF (l_debug = 1) THEN
4050 print_debug('l_is_serial_control: FALSE: ' || l_mmtt_rec.item_serial_control_code);
4051 END IF;
4052 l_is_serial_control := FALSE;
4053 ELSE
4054 IF (l_debug = 1) THEN
4055 print_debug('l_is_serial_control: TRUE: ' || l_mmtt_rec.item_serial_control_code);
4056 END IF;
4057 l_is_serial_control := TRUE;
4058 END IF;
4059
4060 -- only one serial/lot number for the line
4061 IF (l_mmtt_rec.serial_number IS NOT NULL) OR
4062 (l_mmtt_rec.lot_number IS NOT NULL) THEN
4063
4064 l_lot_number := l_mmtt_rec.lot_number;
4065 -- Check if there is any MTLT record corresponding to this MMTT, use
4066 -- that to get the value of the lot number
4067 IF l_mmtt_rec.lot_number IS NULL THEN
4068 BEGIN
4069 SELECT lot_number
4070 INTO l_lot_number
4071 FROM mtl_transaction_lots_temp
4072 WHERE transaction_temp_id = l_mmtt_rec.transaction_temp_id;
4073 IF (l_debug = 1) THEN
4074 print_debug('Lot number from MTLT: ' || l_lot_number);
4075 END IF;
4076 EXCEPTION
4077 WHEN no_data_found THEN
4078 IF (l_debug = 1) THEN
4079 print_debug('No MTLT found: ' || l_lot_number);
4080 END IF;
4081 l_lot_number := NULL;
4082 WHEN OTHERS THEN
4083 IF (l_debug = 1) THEN
4084 print_debug('Unexpected error:' || Sqlerrm);
4085 END IF;
4086 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4087 END;
4088 END IF;
4089
4090 IF l_mmtt_rec.transaction_action_id IN (inv_globals.g_type_cycle_count_adj,
4091 inv_globals.g_type_physical_count_adj,
4092 inv_globals.g_action_deliveryadj)
4093 THEN
4094 IF l_mmtt_rec.transaction_action_id IN (inv_globals.g_type_physical_count_adj,
4095 inv_globals.g_action_deliveryadj) THEN
4096 IF l_mmtt_rec.lpn_id IS NOT NULL THEN
4097 l_lpn_id := l_mmtt_rec.lpn_id;
4098 ELSIF l_mmtt_rec.content_lpn_id IS NOT NULL THEN
4099 l_lpn_id := l_mmtt_rec.content_lpn_id;
4100 ELSIF l_mmtt_rec.transfer_lpn_id IS NOT NULL THEN
4101 l_lpn_id := l_mmtt_rec.transfer_lpn_id;
4102 END IF;
4103 ELSIF l_mmtt_rec.transaction_action_id = inv_globals.g_type_cycle_count_adj
4104 THEN
4105 l_lpn_id := l_mmtt_rec.transfer_lpn_id;
4106 END IF;
4107
4108 l_onhand_exists :=
4109 onhand_quantity_exists
4110 (p_inventory_item_id => l_mmtt_rec.inventory_item_id,
4111 p_revision => l_mmtt_rec.revision,
4112 p_organization_id => l_mmtt_rec.organization_id,
4113 p_subinventory_code => l_mmtt_rec.subinventory_code,
4114 p_locator_id => l_mmtt_rec.locator_id,
4115 p_lot_number => l_lot_number,
4116 p_serial_number => l_mmtt_rec.serial_number,
4117 p_lpn_id => l_lpn_id);
4118 IF NOT l_onhand_exists THEN
4119 IF (l_debug = 1) THEN
4120 print_debug('Treating this as as receipt transaction...: ');
4121 print_debug('Getting transfer cost group id from rules engine...: ');
4122 END IF;
4123 wms_costgroupengine_pvt.assign_cost_group
4124 (p_api_version => 1.0,
4125 p_init_msg_list => FND_API.G_FALSE,
4126 p_commit => FND_API.G_FALSE,
4127 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4128 x_return_status => x_return_Status,
4129 x_msg_count => x_msg_count,
4130 x_msg_data => x_msg_data,
4131 p_line_id => l_mmtt_rec.transaction_temp_id,
4132 p_input_type => wms_costgroupengine_pvt.g_input_mmtt);
4133
4134 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
4135 IF (l_debug = 1) THEN
4136 print_debug('return error from wms_costgroupengine_pvt');
4137 END IF;
4138 RAISE FND_API.G_EXC_ERROR;
4139 ELSIF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
4140 IF (l_debug = 1) THEN
4141 print_debug('return unexpected error from wms_costgroupengine_pvt');
4142 END IF;
4143 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4144 END IF;
4145
4146 END IF;
4147 END IF;
4148
4149 IF l_onhand_exists THEN
4150 IF (l_debug = 1) THEN
4151 print_debug('proc_determine_costgroup: ');
4152 END IF;
4153
4154 IF l_mmtt_rec.move_transaction_id IS NOT NULL OR
4155 l_mmtt_rec.completion_transaction_id IS NOT NULL THEN
4156 l_is_backflush_txn := TRUE;
4157 END IF;
4158
4159 proc_determine_costgroup(p_organization_id => l_mmtt_rec.organization_id,
4160 p_inventory_item_id => l_mmtt_rec.inventory_item_id,
4161 p_subinventory_code => l_mmtt_rec.subinventory_code,
4162 p_locator_id => l_mmtt_rec.locator_id,
4163 p_revision => l_mmtt_rec.revision,
4164 p_lot_number => l_lot_number,
4165 p_serial_number => l_mmtt_rec.serial_number,
4166 p_containerized_flag => 2, -- we need unpacked material from moq
4167 p_lpn_id => l_lpn_id,
4168 p_transaction_action_id => l_mmtt_rec.transaction_action_id,
4169 p_is_backflush_txn => l_is_backflush_txn,
4170 x_cost_group_id => l_cost_group_id,
4171 x_return_status => x_return_status);
4172
4173 IF (l_debug = 1) THEN
4174 print_debug('proc_determine_costgroup return : ' || x_return_status);
4175 print_debug('cost_group_id: ' || l_cost_group_id);
4176 END IF;
4177
4178 IF (x_return_status = fnd_api.g_ret_sts_error)
4179 THEN
4180 RAISE fnd_api.g_exc_error ;
4181 END IF;
4182
4183 IF (x_return_status = fnd_api.g_ret_sts_unexp_error)
4184 THEN
4185 RAISE fnd_api.g_exc_unexpected_error ;
4186 END IF;
4187
4188 proc_update_mmtt(l_mmtt_rec.transaction_temp_id,
4189 p_transfer_wms_org,
4190 p_fob_point,
4191 p_tfr_primary_cost_method,
4192 p_tfr_org_cost_group_id,
4193 l_mmtt_rec.transaction_action_id,
4194 l_mmtt_rec.transfer_organization,
4195 l_mmtt_rec.transfer_subinventory,
4196 l_cost_group_id,
4197 NULL,
4198 NULL,
4199 NULL,
4200 p_from_project_id,
4201 p_to_project_id,
4202 x_return_status);
4203 IF (l_debug = 1) THEN
4204 print_debug('proc_update_mmtt : ' || x_return_status);
4205 END IF;
4206 IF (x_return_status = fnd_api.g_ret_sts_error)
4207 THEN
4208 RAISE fnd_api.g_exc_error ;
4209 END IF;
4210
4211 IF (x_return_status = fnd_api.g_ret_sts_unexp_error)
4212 THEN
4213 RAISE fnd_api.g_exc_unexpected_error ;
4214 END IF;
4215 END IF; -- If onhand exists
4216 ELSE
4217 IF l_is_lot_control THEN
4218 IF l_is_serial_control THEN
4219 -- Lot and serial controlled
4220 IF (l_debug = 1) THEN
4221 print_debug('proc_process_lot_serial: ');
4222 END IF;
4223 proc_process_lot_serial
4224 (p_mmtt_rec => l_mmtt_rec,
4225 p_fob_point => p_fob_point,
4226 p_transfer_wms_org => p_transfer_wms_org,
4227 p_tfr_primary_cost_method => p_tfr_primary_cost_method,
4228 p_tfr_org_cost_group_id => p_tfr_org_cost_group_id,
4229 p_from_project_id => p_from_project_id,
4230 p_to_project_id => p_to_project_id,
4231 x_return_status => x_return_status,
4232 x_msg_count => x_msg_count,
4233 x_msg_data => x_msg_data);
4234 IF (l_debug = 1) THEN
4235 print_debug('proc_process_lot_serial: x_return_status: ' || x_return_status);
4236 END IF;
4237 IF (x_return_status = fnd_api.g_ret_sts_error)
4238 THEN
4239 RAISE fnd_api.g_exc_error ;
4240 END IF;
4241
4242 IF (x_return_status = fnd_api.g_ret_sts_unexp_error)
4243 THEN
4244 RAISE fnd_api.g_exc_unexpected_error ;
4245 END IF;
4246 ELSE
4247 -- Lot controlled
4248 proc_process_lots
4249 (p_mmtt_rec => l_mmtt_rec,
4250 p_fob_point => p_fob_point,
4251 p_transfer_wms_org => p_transfer_wms_org,
4252 p_tfr_primary_cost_method => p_tfr_primary_cost_method,
4253 p_tfr_org_cost_group_id => p_tfr_org_cost_group_id,
4254 p_from_project_id => p_from_project_id,
4255 p_to_project_id => p_to_project_id,
4256 x_return_status => x_return_status,
4257 x_msg_count => x_msg_count,
4258 x_msg_data => x_msg_data);
4259
4260 IF (l_debug = 1) THEN
4261 print_debug('proc_process_lots return: ' || x_return_status);
4262 END IF;
4263 IF (x_return_status = fnd_api.g_ret_sts_error)
4264 THEN
4265 RAISE fnd_api.g_exc_error ;
4266 END IF;
4267
4268 IF (x_return_status = fnd_api.g_ret_sts_unexp_error)
4269 THEN
4270 RAISE fnd_api.g_exc_unexpected_error ;
4271 END IF;
4272 END IF;
4273 ELSE
4274 IF l_is_serial_control THEN
4275 -- Serial control
4276 IF (l_debug = 1) THEN
4277 print_debug('is_serial_control: ' );
4278 END IF;
4279 -- Mrana inv_cost_group_update.proc_process_serials
4280 proc_process_serials
4281 (p_mmtt_rec => l_mmtt_rec,
4282 p_fob_point => p_fob_point,
4283 p_transfer_wms_org => p_transfer_wms_org,
4284 p_tfr_primary_cost_method => p_tfr_primary_cost_method,
4285 p_tfr_org_cost_group_id => p_tfr_org_cost_group_id,
4286 p_from_project_id => p_from_project_id,
4287 p_to_project_id => p_to_project_id,
4288 x_return_status => x_return_status,
4289 x_msg_count => x_msg_count,
4290 x_msg_data => x_msg_data);
4291
4292 IF (l_debug = 1) THEN
4293 print_debug('proc_process_serials return: ' || x_return_status);
4294 END IF;
4295 IF (x_return_status = fnd_api.g_ret_sts_error)
4296 THEN
4297 RAISE fnd_api.g_exc_error ;
4298 END IF;
4299
4300 IF (x_return_status = fnd_api.g_ret_sts_unexp_error)
4301 THEN
4302 RAISE fnd_api.g_exc_unexpected_error ;
4303 END IF;
4304 ELSE
4305 -- No control
4306 IF (l_debug = 1) THEN
4307 print_debug('Nocontrol: Call proc_process_nocontrol: Trx temp id:'
4308 || l_mmtt_rec.transaction_temp_id);
4309 END IF;
4310 -- process mmtt
4311 proc_process_nocontrol
4312 (p_mmtt_rec => l_mmtt_rec,
4313 p_fob_point => p_fob_point,
4314 p_transfer_wms_org => p_transfer_wms_org,
4315 p_tfr_org_cost_group_id => p_tfr_org_cost_group_id,
4316 p_tfr_primary_cost_method => p_tfr_primary_cost_method,
4317 p_from_project_id => p_from_project_id,
4318 p_to_project_id => p_to_project_id,
4319 x_return_status => x_return_status,
4320 x_msg_count => x_msg_count,
4321 x_msg_data => x_msg_data);
4322 IF (l_debug = 1) THEN
4323 print_debug('proc_process_nocontrol return : ' || x_return_status);
4324 END IF;
4325 IF (x_return_status = fnd_api.g_ret_sts_error)
4326 THEN
4327 RAISE fnd_api.g_exc_error ;
4328 END IF;
4329
4330 IF (x_return_status = fnd_api.g_ret_sts_unexp_error)
4331 THEN
4332 RAISE fnd_api.g_exc_unexpected_error ;
4333 END IF;
4334 IF (l_debug = 1) THEN
4335 print_debug('after call to nocontrol');
4336 END IF;
4337 END IF;
4338 END IF;
4339 END IF;
4340 IF (l_debug = 1) THEN
4341 print_debug('Processing Over... ' || x_return_status );
4342 END IF;
4343
4344 EXCEPTION
4345 WHEN FND_API.G_EXC_ERROR THEN
4346 IF (l_debug = 1) THEN
4347 print_debug('inv_cost_group_update .. EXCEP G_EXC_ERROR : ' );
4348 END IF;
4349 ROLLBACK TO API_updatecostgroups;
4350 x_return_status := FND_API.G_RET_STS_ERROR ;
4351 FND_MSG_PUB.Count_And_Get
4352 (p_encoded => FND_API.G_FALSE,
4353 p_count => x_msg_count,
4354 p_data => x_msg_data);
4355 IF (l_debug = 1) THEN
4356 print_debug(' over ');
4357 END IF;
4358 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4359 IF (l_debug = 1) THEN
4360 print_debug('inv_cost_group_update .. EXCEP G_EXC_UNEXPECTED_ERROR : ' );
4361 END IF;
4362 ROLLBACK TO API_updatecostgroups;
4363 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4364 FND_MSG_PUB.Count_And_Get
4365 (p_encoded => FND_API.G_FALSE,
4366 p_count => x_msg_count,
4367 p_data => x_msg_data);
4368 IF (l_debug = 1) THEN
4369 print_debug(' over ' );
4370 END IF;
4371 WHEN OTHERS THEN
4372 IF (l_debug = 1) THEN
4373 print_debug('inv_cost_group_update .. EXCEP G_EXC_UNEXPECTED_ERROR : ' );
4374 END IF;
4375 ROLLBACK TO API_updatecostgroups;
4376 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4377 IF fnd_msg_pub.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4378 THEN
4379 FND_MSG_PUB.Add_Exc_Msg (g_pkg_name,
4380 l_api_name );
4381 END IF;
4382 FND_MSG_PUB.Count_And_Get
4383 (p_encoded => FND_API.G_FALSE,
4384 p_count => x_msg_count,
4385 p_data => x_msg_data);
4386 IF (l_debug = 1) THEN
4387 print_debug(' over ');
4388 END IF;
4389 END cost_group_update;
4390
4391 END inv_cost_group_update;