[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.2 2008/07/29 13:39:17 ptkumar 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 x_return_status := FND_API.G_RET_STS_ERROR ;
768 END IF;
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 ELSIF (
2119 ((p_mmtt_rec.transaction_source_type_id = inv_globals.g_sourcetype_salesorder
2120 OR p_mmtt_rec.transaction_source_type_id = inv_globals.g_sourcetype_intorder)
2121 AND (p_mmtt_rec.transaction_action_id = inv_globals.g_action_stgxfr))
2122 OR /*Bug 6499833:For move order sub transfers,trying to get the costgroup from content_lpn_id.*/
2123 (( p_mmtt_rec.transaction_source_type_id = inv_globals.g_sourcetype_moveorder)
2124 AND (p_mmtt_rec.transaction_action_id = inv_globals.g_action_subxfr))
2125 )
2126 -- AND (p_mmtt_rec.lpn_id IS NULL) Bug#6770593
2127 AND (p_mmtt_rec.content_lpn_id IS NOT NULL)
2128 AND (p_mmtt_rec.inventory_item_id <> -1)
2129 THEN -- For a staging transfer transaction with content_lpn_id, get the cost group from the content lpn ID
2130 l_lpn_id := p_mmtt_rec.content_lpn_id;
2131 END IF;
2132 IF (l_debug = 1) THEN
2133 print_debug('l_lpn_id is set to: '||l_lpn_id);
2134 END IF;
2135
2136 IF l_onhand_exists THEN
2137
2138 IF p_mmtt_rec.move_transaction_id IS NOT NULL OR
2139 p_mmtt_rec.completion_transaction_id IS NOT NULL THEN
2140 l_is_backflush_txn := TRUE;
2141 END IF;
2142
2143 proc_determine_costgroup(p_organization_id => p_mmtt_rec.organization_id,
2144 p_inventory_item_id => p_mmtt_rec.inventory_item_id,
2145 p_subinventory_code => p_mmtt_rec.subinventory_code,
2146 p_locator_id => p_mmtt_rec.locator_id,
2147 p_revision => p_mmtt_rec.revision,
2148 p_lot_number => NULL,
2149 p_serial_number => NULL,
2150 p_containerized_flag => 2, -- we need unpacked material from moq
2151 p_lpn_id => l_lpn_id,
2152 p_transaction_action_id => p_mmtt_rec.transaction_action_id,
2153 p_is_backflush_txn => l_is_backflush_txn,
2154 x_cost_group_id => l_cost_group_id,
2155 x_return_status => x_return_status);
2156
2157 IF (l_debug = 1) THEN
2158 print_debug('proc_determine_costgroup return : ' || x_return_status);
2159 print_debug('proc_determine_costgroup cg : ' || l_cost_group_id);
2160 END IF;
2161 IF (x_return_status = fnd_api.g_ret_sts_error)
2162 THEN
2163 RAISE fnd_api.g_exc_error ;
2164 END IF;
2165
2166 IF (x_return_status = fnd_api.g_ret_sts_unexp_error)
2167 THEN
2168 RAISE fnd_api.g_exc_unexpected_error ;
2169 END IF;
2170
2171 IF (l_debug = 1) THEN
2172 print_debug('call :proc_update_mmtt :p_transaction_temp_id: ' || p_mmtt_rec.transaction_temp_id);
2173 print_debug('call :proc_update_mmtt :l_cost_group_id: ' || l_cost_group_id);
2174 END IF;
2175
2176 proc_update_mmtt(p_transaction_temp_id => p_mmtt_rec.transaction_temp_id,
2177 p_transfer_wms_org => p_transfer_wms_org,
2178 p_fob_point => p_fob_point,
2179 p_tfr_primary_cost_method => p_tfr_primary_cost_method,
2180 p_tfr_org_cost_group_id => p_tfr_org_cost_group_id,
2181 p_transaction_action_id => p_mmtt_rec.transaction_action_id,
2182 p_transfer_organization => p_mmtt_rec.transfer_organization,
2183 p_transfer_subinventory => p_mmtt_rec.transfer_subinventory,
2184 p_cost_group_id => l_cost_group_id,
2185 p_transfer_cost_group_id => NULL,
2186 p_primary_quantity => NULL,
2187 p_transaction_quantity => NULL,
2188 p_from_project_id => p_from_project_id,
2189 p_to_project_id => p_to_project_id,
2190 x_return_status => x_return_status);
2191
2192 IF (l_debug = 1) THEN
2193 print_debug('proc_update_mmtt return : ' || x_return_status);
2194 END IF;
2195 IF (x_return_status = fnd_api.g_ret_sts_error)
2196 THEN
2197 RAISE fnd_api.g_exc_error ;
2198 END IF;
2199
2200 IF (x_return_status = fnd_api.g_ret_sts_unexp_error)
2201 THEN
2202 RAISE fnd_api.g_exc_unexpected_error ;
2203 END IF;
2204 END IF; -- Onhand exists
2205
2206 EXCEPTION
2207 WHEN FND_API.G_EXC_ERROR THEN
2208 x_return_status := FND_API.G_RET_STS_ERROR ;
2209 IF (l_debug = 1) THEN
2210 print_debug('no process control .. EXCEP G_EXC_ERROR : ' );
2211 END IF;
2212 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2213 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2214 IF (l_debug = 1) THEN
2215 print_debug('no process control .. EXCEP G_EXC_UNEXPECTED_ERROR : ' );
2216 END IF;
2217 WHEN OTHERS THEN
2218 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2219 IF (l_debug = 1) THEN
2220 print_debug('no process control .. EXCEP OTHERS : ' );
2221 END IF;
2222 END proc_process_nocontrol;
2223
2224
2225 -- Processes lot controlled items. This involves splitting
2226 -- MMTT and updating MTLT lines so that each row corresponds to a unique cost
2227 -- group.
2228 PROCEDURE proc_process_lots
2229 (p_mmtt_rec IN mtl_material_transactions_temp%ROWTYPE,
2230 p_fob_point IN NUMBER,
2231 p_transfer_wms_org IN BOOLEAN,
2232 p_tfr_primary_cost_method IN NUMBER,
2233 p_tfr_org_cost_group_id IN NUMBER,
2234 p_from_project_id IN NUMBER,
2235 p_to_project_id IN NUMBER,
2236 x_return_status OUT NOCOPY VARCHAR2,
2237 x_msg_count OUT NOCOPY NUMBER,
2238 x_msg_data OUT NOCOPY VARCHAR2)
2239 IS
2240 l_api_name CONSTANT VARCHAR2(100) := 'proc_process_lots';
2241 l_transaction_temp_id NUMBER := NULL;
2242 i INTEGER;
2243 j INTEGER;
2244 l_transaction_quantity NUMBER;
2245 l_quantity_sign NUMBER;
2246 l_cost_group_id NUMBER;
2247
2248 -- For putting records in MTLT and MSNT tables
2249 TYPE lots_record IS RECORD
2250 (mtlt_rowid ROWID,
2251 cost_group_id NUMBER);
2252 TYPE lots_table IS TABLE OF lots_record INDEX BY BINARY_INTEGER;
2253 l_lots_table lots_table;
2254 l_lti INTEGER := 0;
2255
2256 TYPE cg_quantity_record IS RECORD
2257 (new_transaction_temp_id NUMBER,
2258 primary_quantity NUMBER,
2259 transaction_quantity NUMBER,
2260 update_mmtt BOOLEAN);
2261 TYPE cg_quantity_table IS TABLE OF cg_quantity_record INDEX BY BINARY_INTEGER;
2262 l_cg_quantity_table cg_quantity_table;
2263
2264 rec_mtlt cur_mtlt%ROWTYPE;
2265
2266 l_onhand_exists BOOLEAN;
2267 l_is_backflush_txn BOOLEAN := FALSE;
2268 l_lpn_id NUMBER := p_mmtt_rec.lpn_id;
2269
2270 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2271 BEGIN
2272 x_return_status := fnd_api.g_ret_sts_success;
2273 SAVEPOINT sp_proc_process_lots;
2274
2275 IF (l_debug = 1) THEN
2276 print_debug('IN proc_process_lots.. ');
2277 END IF;
2278
2279 OPEN cur_mtlt(p_mmtt_rec.transaction_temp_id);
2280
2281 FETCH cur_mtlt INTO rec_mtlt;
2282
2283 WHILE cur_mtlt%found LOOP
2284 IF (l_debug = 1) THEN
2285 print_debug('Within cur_mtlt loop... ' || rec_mtlt.lot_number);
2286 END IF;
2287
2288 l_onhand_exists := TRUE;
2289
2290 -- Adjustment transactions
2291 IF p_mmtt_rec.transaction_action_id IN (inv_globals.g_type_cycle_count_adj,
2292 inv_globals.g_type_physical_count_adj,
2293 inv_globals.g_action_deliveryadj)
2294 THEN
2295 IF p_mmtt_rec.transaction_action_id = inv_globals.g_type_physical_count_adj THEN
2296 IF p_mmtt_rec.lpn_id IS NOT NULL THEN
2297 l_lpn_id := p_mmtt_rec.lpn_id;
2298 ELSIF p_mmtt_rec.content_lpn_id IS NOT NULL THEN
2299 l_lpn_id := p_mmtt_rec.content_lpn_id;
2300 ELSIF p_mmtt_rec.transfer_lpn_id IS NOT NULL THEN
2301 l_lpn_id := p_mmtt_rec.transfer_lpn_id;
2302 END IF;
2303 ELSIF p_mmtt_rec.transaction_action_id = inv_globals.g_action_deliveryadj THEN
2304 l_lpn_id := p_mmtt_rec.transfer_lpn_id;
2305 ELSIF p_mmtt_rec.transaction_action_id = inv_globals.g_type_cycle_count_adj THEN
2306 l_lpn_id := p_mmtt_rec.transfer_lpn_id;
2307 END IF;
2308
2309 l_onhand_exists :=
2310 onhand_quantity_exists
2311 (p_inventory_item_id => p_mmtt_rec.inventory_item_id,
2312 p_revision => p_mmtt_rec.revision,
2313 p_organization_id => p_mmtt_rec.organization_id,
2314 p_subinventory_code => p_mmtt_rec.subinventory_code,
2315 p_locator_id => p_mmtt_rec.locator_id,
2316 p_lot_number => rec_mtlt.lot_number,
2317 p_serial_number => NULL,
2318 p_lpn_id => l_lpn_id);
2319 IF NOT l_onhand_exists THEN
2320 IF (l_debug = 1) THEN
2321 print_debug('Treating this as as receipt transaction...: ');
2322 print_debug('Getting transfer cost group id from rules engine...: ');
2323 END IF;
2324
2325 wms_costgroupengine_pvt.assign_cost_group
2326 (p_api_version => 1.0,
2327 p_init_msg_list => FND_API.G_FALSE,
2328 p_commit => FND_API.G_FALSE,
2329 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2330 x_return_status => x_return_Status,
2331 x_msg_count => x_msg_count,
2332 x_msg_data => x_msg_data,
2333 p_line_id => p_mmtt_rec.transaction_temp_id,
2334 p_input_type => wms_costgroupengine_pvt.g_input_mmtt);
2335
2336 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2337 IF (l_debug = 1) THEN
2338 print_debug('return error from wms_costgroupengine_pvt');
2339 END IF;
2340 RAISE FND_API.G_EXC_ERROR;
2341 ELSIF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2342 IF (l_debug = 1) THEN
2343 print_debug('return unexpected error from wms_costgroupengine_pvt');
2344 END IF;
2345 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2346 END IF;
2347 END IF;
2348 ELSIF (p_mmtt_rec.transaction_source_type_id = inv_globals.g_sourcetype_salesorder
2349 OR p_mmtt_rec.transaction_source_type_id = inv_globals.g_sourcetype_intorder)
2350 AND (p_mmtt_rec.transaction_action_id = inv_globals.g_action_issue
2351 OR p_mmtt_rec.transaction_action_id = inv_globals.g_action_subxfr
2352 OR p_mmtt_rec.transaction_action_id = inv_globals.g_action_orgxfr
2353 OR p_mmtt_rec.transaction_action_id = inv_globals.g_action_intransitshipment)
2354 THEN -- For a ship confirm transaction, get the cost group from the content lpn ID
2355 l_lpn_id := p_mmtt_rec.content_lpn_id;
2356 /* Bug 4628878: For staging transfers, when whole LPN is being transfered, cost group should
2357 * be obtained from content_lpn_id */
2358 ELSIF (
2359 ((p_mmtt_rec.transaction_source_type_id = inv_globals.g_sourcetype_salesorder
2360 OR p_mmtt_rec.transaction_source_type_id = inv_globals.g_sourcetype_intorder)
2361 AND (p_mmtt_rec.transaction_action_id = inv_globals.g_action_stgxfr))
2362 OR/*Bug 6499833:For move order sub transfers,trying to get the costgroup from content_lpn_id.*/
2363 (( p_mmtt_rec.transaction_source_type_id = inv_globals.g_sourcetype_moveorder)
2364 AND (p_mmtt_rec.transaction_action_id = inv_globals.g_action_subxfr))
2365 )
2366 -- AND (p_mmtt_rec.lpn_id IS NULL) Bug#6770593
2367 AND (p_mmtt_rec.content_lpn_id IS NOT NULL)
2368 AND (p_mmtt_rec.inventory_item_id <> -1)
2369 THEN -- For a staging transfer transaction with content_lpn_id, get the cost group from the content lpn ID
2370 l_lpn_id := p_mmtt_rec.content_lpn_id;
2371 END IF;
2372 IF (l_debug = 1) THEN
2373 print_debug('l_lpn_id is set to: '||l_lpn_id);
2374 END IF;
2375
2376 IF l_onhand_exists THEN
2377 l_lti := l_lti + 1;
2378 IF (l_debug = 1) THEN
2379 print_debug('trx_id: ' || p_mmtt_rec.transaction_temp_id);
2380 print_debug('Row ID: ' || rec_mtlt.mtlt_rowid);
2381 END IF;
2382
2383 l_lots_table(l_lti).mtlt_rowid := rec_mtlt.mtlt_rowid;
2384
2385 IF p_mmtt_rec.move_transaction_id IS NOT NULL OR
2386 p_mmtt_rec.completion_transaction_id IS NOT NULL THEN
2387 l_is_backflush_txn := TRUE;
2388 END IF;
2389
2390 -- Get the cost group for this MTLT record
2391 proc_determine_costgroup(p_organization_id => p_mmtt_rec.organization_id,
2392 p_inventory_item_id => p_mmtt_rec.inventory_item_id,
2393 p_subinventory_code => p_mmtt_rec.subinventory_code,
2394 p_locator_id => p_mmtt_rec.locator_id,
2395 p_revision => p_mmtt_rec.revision,
2396 p_lot_number => rec_mtlt.lot_number,
2397 p_serial_number => NULL,
2398 p_containerized_flag => 2, -- we need unpacked material from moq
2399 p_lpn_id => l_lpn_id,
2400 p_transaction_action_id => p_mmtt_rec.transaction_action_id,
2401 p_is_backflush_txn => l_is_backflush_txn,
2402 x_cost_group_id => l_cost_group_id,
2403 x_return_status => x_return_status);
2404
2405
2406 l_lots_table(l_lti).cost_group_id := l_cost_group_id;
2407
2408 IF x_return_status = fnd_api.g_ret_sts_error THEN
2409 RAISE fnd_api.g_exc_error ;
2410 END IF;
2411
2412 IF l_cg_quantity_table.exists(l_cost_group_id) THEN
2413 l_cg_quantity_table(l_cost_group_id).primary_quantity :=
2414 l_cg_quantity_table(l_cost_group_id).primary_quantity +
2415 Abs(rec_mtlt.primary_quantity);
2416
2417 l_cg_quantity_table(l_cost_group_id).transaction_quantity :=
2418 l_cg_quantity_table(l_cost_group_id).transaction_quantity +
2419 Abs(rec_mtlt.transaction_quantity);
2420 ELSE
2421 IF l_cg_quantity_table.COUNT = 0 THEN
2422 -- If the table is empty then the existing
2423 -- transaction_temp_id should be used as the
2424 -- new_transaction_temp_id also
2425 l_cg_quantity_table(l_cost_group_id).new_transaction_temp_id := p_mmtt_rec.transaction_temp_id;
2426 l_cg_quantity_table(l_cost_group_id).update_mmtt := TRUE;
2427 ELSE
2428 -- otherwise generate a new_transaction_temp_id
2429 SELECT mtl_material_transactions_s.NEXTVAL
2430 INTO l_transaction_temp_id
2431 FROM dual;
2432 l_cg_quantity_table(l_cost_group_id).new_transaction_temp_id := l_transaction_temp_id;
2433 l_cg_quantity_table(l_cost_group_id).update_mmtt := FALSE;
2434 END IF;
2435 l_cg_quantity_table(l_cost_group_id).primary_quantity := Abs(rec_mtlt.primary_quantity);
2436 l_cg_quantity_table(l_cost_group_id).transaction_quantity := Abs(rec_mtlt.transaction_quantity);
2437 END IF;
2438 END IF; -- If onhand exists
2439
2440 FETCH cur_mtlt INTO rec_mtlt;
2441 END LOOP;
2442
2443 CLOSE cur_mtlt;
2444
2445 -- Insert or update the records in l_cg_quantity_table into MMTT
2446 IF (l_debug = 1) THEN
2447 print_debug('count: ' || l_cg_quantity_table.count);
2448 END IF;
2449
2450 IF l_cg_quantity_table.COUNT > 0 THEN
2451 IF (l_debug = 1) THEN
2452 print_debug('proc_process_lots..Inserting records INTO MMTT ');
2453 END IF;
2454 i := l_cg_quantity_table.first;
2455
2456 IF p_mmtt_rec.transaction_quantity >= 0 THEN
2457 l_quantity_sign := 1;
2458 ELSE
2459 l_quantity_sign := -1;
2460 END IF;
2461
2462 LOOP
2463 l_cg_quantity_table(i).primary_quantity :=
2464 l_cg_quantity_table(i).primary_quantity * l_quantity_sign;
2465
2466 l_cg_quantity_table(i).transaction_quantity :=
2467 l_cg_quantity_table(i).transaction_quantity * l_quantity_sign;
2468
2469 IF (l_debug = 1) THEN
2470 print_debug('Primary qty: ' ||
2471 l_cg_quantity_table(i).primary_quantity);
2472 print_debug('qty sign: ' || l_quantity_sign);
2473 END IF;
2474
2475 IF l_cg_quantity_table(i).update_mmtt = FALSE THEN
2476 proc_insert_mmtt(p_mmtt_rec,
2477 p_transfer_wms_org,
2478 p_fob_point,
2479 p_tfr_primary_cost_method,
2480 p_tfr_org_cost_group_id,
2481 i, -- Remember that i is also the cost_group_id of the record
2482 NULL,
2483 l_cg_quantity_table(i).primary_quantity,
2484 l_cg_quantity_table(i).transaction_quantity,
2485 l_cg_quantity_table(i).new_transaction_temp_id,
2486 p_from_project_id,
2487 p_to_project_id,
2488 x_return_status);
2489
2490 IF (l_debug = 1) THEN
2491 print_debug('proc_insert_mmtt return : ' || x_return_status);
2492 END IF;
2493 ELSE
2494 proc_update_mmtt(p_mmtt_rec.transaction_temp_id,
2495 p_transfer_wms_org,
2496 p_fob_point,
2497 p_tfr_primary_cost_method,
2498 p_tfr_org_cost_group_id,
2499 p_mmtt_rec.transaction_action_id,
2500 p_mmtt_rec.transfer_organization,
2501 p_mmtt_rec.transfer_subinventory,
2502 i, -- Remember that i is also the cost_group_id of the record
2503 NULL,
2504 l_cg_quantity_table(i).primary_quantity,
2505 l_cg_quantity_table(i).transaction_quantity,
2506 p_from_project_id,
2507 p_to_project_id,
2508 x_return_status);
2509
2510 IF (l_debug = 1) THEN
2511 print_debug('proc_update_mmtt return : ' || x_return_status);
2512 END IF;
2513 END IF;
2514
2515 IF (x_return_status = fnd_api.g_ret_sts_error)
2516 THEN
2517 RAISE fnd_api.g_exc_error;
2518 END IF;
2519
2520 IF (x_return_status = fnd_api.g_ret_sts_unexp_error)
2521 THEN
2522 RAISE fnd_api.g_exc_unexpected_error;
2523 END IF;
2524
2525 EXIT WHEN i = l_cg_quantity_table.last;
2526 i := l_cg_quantity_table.next(i);
2527 END LOOP;
2528
2529 -- Update the records in l_lots_table into MTLT and MSNT
2530 IF (l_debug = 1) THEN
2531 print_debug('IN proc_process_lots..updating records INTO MTLT');
2532 END IF;
2533 FOR i IN 1..l_lots_table.COUNT LOOP
2534 -- Update the MTLT records
2535 IF (l_debug = 1) THEN
2536 print_debug('updating MTLT ');
2537 END IF;
2538 proc_update_mtlt(l_lots_table(i).mtlt_rowid,
2539 l_cg_quantity_table(l_lots_table(i).cost_group_id).new_transaction_temp_id,
2540 NULL,
2541 NULL,
2542 NULL,
2543 NULL,
2544 x_return_status);
2545
2546 IF (l_debug = 1) THEN
2547 print_debug('proc_update_mtlt return : ' || x_return_status);
2548 END IF;
2549
2550 IF (x_return_status = fnd_api.g_ret_sts_error)
2551 THEN
2552 RAISE fnd_api.g_exc_error ;
2553 END IF;
2554
2555 IF (x_return_status = fnd_api.g_ret_sts_unexp_error)
2556 THEN
2557 RAISE fnd_api.g_exc_unexpected_error ;
2558 END IF;
2559 END LOOP;
2560
2561 END IF;
2562 EXCEPTION
2563 WHEN FND_API.G_EXC_ERROR THEN
2564 IF (l_debug = 1) THEN
2565 print_debug('proc_process_lots .. EXCEP G_EXC_ERROR : ' );
2566 END IF;
2567 x_return_status := FND_API.G_RET_STS_ERROR ;
2568 IF cur_mtlt%isopen THEN
2569 CLOSE cur_mtlt;
2570 END IF;
2571 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2572 IF (l_debug = 1) THEN
2573 print_debug('proc_process_lots .. EXCEP G_EXC_UNEXPECTED_ERROR : ' );
2574 END IF;
2575 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2576 IF cur_mtlt%isopen THEN
2577 CLOSE cur_mtlt;
2578 END IF;
2579 WHEN OTHERS THEN
2580 IF (l_debug = 1) THEN
2581 print_debug('proc_process_lots .. EXCEP OTHERS : ' || SQLERRM(SQLCODE));
2582 END IF;
2583 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2584 IF cur_mtlt%isopen THEN
2585 CLOSE cur_mtlt;
2586 END IF;
2587 END proc_process_lots;
2588
2589
2590 -- Processes the lot and serial controlled items. This involves splitting
2591 -- MMTT, MTLT and MSNT lines so that each row corresponds to a unique cost
2592 -- group.
2593
2594 PROCEDURE proc_process_serials
2595 (p_mmtt_rec IN mtl_material_transactions_temp%ROWTYPE,
2596 p_fob_point IN NUMBER,
2597 p_transfer_wms_org IN BOOLEAN,
2598 p_tfr_primary_cost_method IN NUMBER,
2599 p_tfr_org_cost_group_id IN NUMBER,
2600 p_from_project_id IN NUMBER,
2601 p_to_project_id IN NUMBER,
2602 x_return_status OUT NOCOPY VARCHAR2,
2603 x_msg_count OUT NOCOPY NUMBER,
2604 x_msg_data OUT NOCOPY VARCHAR2)
2605 IS
2606 l_api_name CONSTANT VARCHAR2(100) := 'proc_process_serial';
2607 l_transaction_temp_id NUMBER := NULL;
2608 i INTEGER;
2609 l_transaction_quantity NUMBER;
2610 l_quantity_sign NUMBER;
2611 l_cost_group_id NUMBER;
2612
2613 -- For putting records in MSNT tables
2614 TYPE serial_record IS RECORD
2615 (from_serial_number mtl_serial_numbers.serial_number%TYPE,
2616 to_serial_number mtl_serial_numbers.serial_number%TYPE,
2617 cost_group_id NUMBER,
2618 update_msnt BOOLEAN);
2619 TYPE serial_table IS TABLE OF serial_record INDEX BY BINARY_INTEGER;
2620 l_serial_table serial_table;
2621 l_sti INTEGER := 1;
2622
2623 TYPE cg_quantity_record IS RECORD
2624 (new_transaction_temp_id NUMBER,
2625 quantity NUMBER,
2626 update_mmtt BOOLEAN);
2627 TYPE cg_quantity_table IS TABLE OF cg_quantity_record INDEX BY BINARY_INTEGER;
2628 l_cg_quantity_table cg_quantity_table;
2629
2630 TYPE msnt_rowid_table IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
2631 l_msnt_rowid_table msnt_rowid_table;
2632
2633 TYPE msnt_table IS TABLE OF cur_msnt%ROWTYPE INDEX BY BINARY_INTEGER;
2634 l_msnt_table msnt_table;
2635
2636 rec_msnt cur_msnt%ROWTYPE;
2637
2638 l_onhand_exists BOOLEAN;
2639 l_lpn_id NUMBER := p_mmtt_rec.lpn_id;
2640 --Bug 3686015 fix
2641 l_temp_prefix VARCHAR2(30):=NULL;
2642 l_from_ser_number NUMBER := NULL;
2643 l_fm_ser_length NUMBER := NULL;
2644 l_to_temp_prefix VARCHAR2(30) := NULL;
2645 l_to_ser_number NUMBER := NULL;
2646 --Bug 3686015 fix
2647 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2648 BEGIN
2649 x_return_status := fnd_api.g_ret_sts_success;
2650 SAVEPOINT sp_proc_process_serial;
2651
2652 IF (l_debug = 1) THEN
2653 print_debug('IN proc_process_serial.. ');
2654 END IF;
2655
2656 OPEN cur_msnt(p_mmtt_rec.transaction_temp_id);
2657
2658 FETCH cur_msnt INTO rec_msnt;
2659
2660 -- If the serial control is dynamic entry at sales order issue check if
2661 -- there are any records in MSNT. If there are no records there treat the
2662 -- item as a no control item.
2663 IF p_mmtt_rec.item_serial_control_code = 6 THEN
2664 CLOSE cur_msnt;
2665 proc_process_nocontrol
2666 (p_mmtt_rec => p_mmtt_rec,
2667 p_fob_point => p_fob_point,
2668 p_transfer_wms_org => p_transfer_wms_org,
2669 p_tfr_primary_cost_method => p_tfr_primary_cost_method,
2670 p_tfr_org_cost_group_id => p_tfr_org_cost_group_id,
2671 p_from_project_id => p_from_project_id,
2672 p_to_project_id => p_to_project_id,
2673 x_return_status => x_return_status,
2674 x_msg_count => x_msg_count,
2675 x_msg_data => x_msg_data);
2676 RETURN;
2677 END IF;
2678
2679
2680 IF p_mmtt_rec.item_serial_control_code <> 6 AND cur_msnt%notfound THEN
2681 IF (l_debug = 1) THEN
2682 print_debug('Serial Code is not 6 but does not have any corresponding
2683 records IN MSNT');
2684 END IF;
2685 fnd_message.set_name('INV', 'Cannot find the serial number
2686 for the transaction being processed');
2687 fnd_msg_pub.add;
2688 RAISE FND_API.G_EXC_ERROR;
2689 END IF;
2690
2691 WHILE cur_msnt%found LOOP
2692 IF (l_debug = 1) THEN
2693 print_debug('trx_id: ' || p_mmtt_rec.transaction_temp_id);
2694 END IF;
2695
2696 i := 1;
2697 IF (l_debug = 1) THEN
2698 print_debug('FSN: ' || rec_msnt.fm_serial_number);
2699 print_debug('TSN: ' || rec_msnt.to_serial_number);
2700 print_debug('ORG: ' || p_mmtt_rec.organization_id);
2701 print_debug('ITEM: ' || p_mmtt_rec.inventory_item_id);
2702 END IF;
2703
2704 --Bug 3686015
2705 inv_validate.number_from_sequence(rec_msnt.fm_serial_number, l_temp_prefix, l_from_ser_number);
2706 l_fm_ser_length := Length(rec_msnt.fm_serial_number);
2707
2708 IF (l_debug = 1) THEN
2709 print_debug('FSNPREFIX: ' || l_temp_prefix);
2710 print_debug('FSNNUMERIC: ' || l_from_ser_number);
2711 print_debug('FSNLENGTH: ' || l_fm_ser_length);
2712 END IF;
2713 IF (rec_msnt.to_serial_number IS NOT NULL) AND
2714 (rec_msnt.to_serial_number <> rec_msnt.fm_serial_number) THEN
2715 IF Length(rec_msnt.to_serial_number)<>l_fm_ser_length THEN
2716 IF (l_debug = 1) THEN
2717 print_debug('ERROR: Length of FSN diff from TSN');
2718 END IF;
2719 fnd_message.set_name('INV', 'INV_FROM_TO_SER_DIFF_LENGTH');
2720 fnd_message.set_token('FM_SER_NUM',rec_msnt.fm_serial_number);
2721 fnd_message.set_token('TO_SER_NUM', rec_msnt.to_serial_number);
2722 fnd_msg_pub.add;
2723 RAISE fnd_api.g_exc_error;
2724 END IF;
2725
2726 -- get the number part of the to serial
2727 inv_validate.number_from_sequence(rec_msnt.to_serial_number, l_to_temp_prefix, l_to_ser_number);
2728
2729 IF (l_debug = 1) THEN
2730 print_debug('TSNPREFIX: ' || l_to_temp_prefix);
2731 print_debug('TSNNUMERIC: ' || l_to_ser_number);
2732 END IF;
2733
2734 IF (l_temp_prefix IS NOT NULL) AND (l_to_temp_prefix IS NOT NULL) AND
2735 (l_to_temp_prefix <> l_temp_prefix) THEN
2736 IF (l_debug = 1) THEN
2737 print_debug('ERROR: From serial prefix different from to serial prefix');
2738 END IF;
2739 fnd_message.set_name('INV', 'INV_FROM_TO_SER_DIFF_PFX');
2740 fnd_message.set_token('FM_SER_NUM',rec_msnt.fm_serial_number);
2741 fnd_message.set_token('TO_SER_NUM', rec_msnt.to_serial_number);
2742 fnd_msg_pub.add;
2743 RAISE fnd_api.g_exc_error;
2744 END IF;
2745
2746 END IF;
2747 --Bug 3686015
2748
2749
2750 FOR rec_msn IN cur_msn(rec_msnt.fm_serial_number,
2751 rec_msnt.to_serial_number,
2752 p_mmtt_rec.inventory_item_id,
2753 p_mmtt_rec.organization_id,
2754 l_temp_prefix,
2755 l_fm_ser_length)
2756 LOOP
2757 IF (l_debug = 1) THEN
2758 print_debug('In MSN cursor...SN: ' || rec_msn.serial_number);
2759 END IF;
2760 l_onhand_exists := TRUE;
2761
2762 -- Adjustment transactions
2763 IF p_mmtt_rec.transaction_action_id IN (inv_globals.g_type_cycle_count_adj,
2764 inv_globals.g_type_physical_count_adj,
2765 inv_globals.g_action_deliveryadj)
2766 THEN
2767
2768 IF p_mmtt_rec.transaction_action_id = inv_globals.g_type_physical_count_adj THEN
2769 IF p_mmtt_rec.lpn_id IS NOT NULL THEN
2770 l_lpn_id := p_mmtt_rec.lpn_id;
2771 ELSIF p_mmtt_rec.content_lpn_id IS NOT NULL THEN
2772 l_lpn_id := p_mmtt_rec.content_lpn_id;
2773 ELSIF p_mmtt_rec.transfer_lpn_id IS NOT NULL THEN
2774 l_lpn_id := p_mmtt_rec.transfer_lpn_id;
2775 END IF;
2776 ELSIF p_mmtt_rec.transaction_action_id = inv_globals.g_action_deliveryadj THEN
2777 l_lpn_id := p_mmtt_rec.transfer_lpn_id;
2778 ELSIF p_mmtt_rec.transaction_action_id = inv_globals.g_type_cycle_count_adj THEN
2779 l_lpn_id := p_mmtt_rec.transfer_lpn_id;
2780 END IF;
2781
2782 l_onhand_exists :=
2783 onhand_quantity_exists
2784 (p_inventory_item_id => p_mmtt_rec.inventory_item_id,
2785 p_revision => p_mmtt_rec.revision,
2786 p_organization_id => p_mmtt_rec.organization_id,
2787 p_subinventory_code => p_mmtt_rec.subinventory_code,
2788 p_locator_id => p_mmtt_rec.locator_id,
2789 p_lot_number => NULL,
2790 p_serial_number => rec_msn.serial_number,
2791 p_lpn_id => l_lpn_id);
2792 IF NOT l_onhand_exists THEN
2793 IF (l_debug = 1) THEN
2794 print_debug('Treating this as as receipt transaction...: ');
2795 print_debug('Getting transfer cost group id from rules engine...: ');
2796 END IF;
2797 wms_costgroupengine_pvt.assign_cost_group
2798 (p_api_version => 1.0,
2799 p_init_msg_list => FND_API.G_FALSE,
2800 p_commit => FND_API.G_FALSE,
2801 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2802 x_return_status => x_return_Status,
2803 x_msg_count => x_msg_count,
2804 x_msg_data => x_msg_data,
2805 p_line_id => p_mmtt_rec.transaction_temp_id,
2806 p_input_type => wms_costgroupengine_pvt.g_input_mmtt);
2807
2808 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2809 IF (l_debug = 1) THEN
2810 print_debug('return error from wms_costgroupengine_pvt');
2811 END IF;
2812 RAISE FND_API.G_EXC_ERROR;
2813 ELSIF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2814 IF (l_debug = 1) THEN
2815 print_debug('return unexpected error from wms_costgroupengine_pvt');
2816 END IF;
2817 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2818 END IF;
2819
2820 END IF;
2821 ELSIF (p_mmtt_rec.transaction_source_type_id = inv_globals.g_sourcetype_salesorder
2822 OR p_mmtt_rec.transaction_source_type_id = inv_globals.g_sourcetype_intorder)
2823 AND (p_mmtt_rec.transaction_action_id = inv_globals.g_action_issue
2824 OR p_mmtt_rec.transaction_action_id = inv_globals.g_action_subxfr
2825 OR p_mmtt_rec.transaction_action_id = inv_globals.g_action_orgxfr
2826 OR p_mmtt_rec.transaction_action_id = inv_globals.g_action_intransitshipment)
2827 THEN -- For a ship confirm transaction, get the cost group from the content lpn ID
2828 l_lpn_id := p_mmtt_rec.content_lpn_id;
2829 /* Bug 4628878: For staging transfers, when whole LPN is being transfered, cost group should
2830 * be obtained from content_lpn_id */
2831 ELSIF (
2832 ((p_mmtt_rec.transaction_source_type_id = inv_globals.g_sourcetype_salesorder
2833 OR p_mmtt_rec.transaction_source_type_id = inv_globals.g_sourcetype_intorder)
2834 AND (p_mmtt_rec.transaction_action_id = inv_globals.g_action_stgxfr))
2835 OR/*Bug 6499833:For move order sub transfers,trying to get the costgroup from content_lpn_id.*/
2836 (( p_mmtt_rec.transaction_source_type_id = inv_globals.g_sourcetype_moveorder)
2837 AND (p_mmtt_rec.transaction_action_id = inv_globals.g_action_subxfr))
2838 )
2839 -- AND (p_mmtt_rec.lpn_id IS NULL) Bug#6770593
2840 AND (p_mmtt_rec.content_lpn_id IS NOT NULL)
2841 AND (p_mmtt_rec.inventory_item_id <> -1)
2842 THEN -- For a staging transfer transaction with content_lpn_id, get the cost group from the content lpn ID
2843 l_lpn_id := p_mmtt_rec.content_lpn_id;
2844 END IF;
2845 IF (l_debug = 1) THEN
2846 print_debug('l_lpn_id is set to: '||l_lpn_id);
2847 END IF;
2848
2849 IF l_onhand_exists THEN
2850
2851 IF i=1 THEN --When the l_serial_table is empty
2852 l_serial_table(l_sti).from_serial_number := rec_msn.serial_number;
2853 l_serial_table(l_sti).to_serial_number := rec_msn.serial_number;
2854 l_serial_table(l_sti).cost_group_id := rec_msn.cost_group_id;
2855
2856 l_serial_table(l_sti).update_msnt := TRUE;
2857 l_msnt_rowid_table(l_sti) := rec_msnt.msnt_rowid;
2858
2859 l_msnt_table(l_sti) := rec_msnt;
2860
2861 l_sti := l_sti + 1;
2862 i := i + 1;
2863 ELSIF i<>1 THEN -- When there are records in l_serial_table
2864 -- If the Cost Group ID of this record is the same as that of
2865 -- the previous record then extend the serial number range of
2866 -- the previous record otherwise insert a new record
2867 IF rec_msn.cost_group_id = l_serial_table(l_sti-1).cost_group_id THEN
2868 l_serial_table(l_sti-1).to_serial_number := rec_msn.serial_number;
2869 ELSE
2870 l_serial_table(l_sti).from_serial_number := rec_msn.serial_number;
2871 l_serial_table(l_sti).to_serial_number := rec_msn.serial_number;
2872 l_serial_table(l_sti).cost_group_id := rec_msn.cost_group_id;
2873
2874 l_serial_table(l_sti).update_msnt := FALSE;
2875
2876 l_msnt_table(l_sti) := rec_msnt;
2877
2878 l_sti := l_sti + 1;
2879 END IF;
2880 END IF;
2881
2882 IF rec_msn.cost_group_id IS NULL THEN
2883 proc_get_pending_costgroup(p_organization_id => p_mmtt_rec.organization_id,
2884 p_inventory_item_id => p_mmtt_rec.inventory_item_id,
2885 p_subinventory_code => p_mmtt_rec.subinventory_code,
2886 p_locator_id => p_mmtt_rec.locator_id,
2887 p_revision => p_mmtt_rec.revision,
2888 p_lot_number => p_mmtt_rec.lot_number,
2889 p_serial_number => rec_msn.serial_number,
2890 p_lpn_id => p_mmtt_rec.lpn_id,
2891 p_transaction_action_id => p_mmtt_rec.transaction_action_id,
2892 x_cost_group_id => l_cost_group_id,
2893 x_return_status => x_return_status);
2894 rec_msn.cost_group_id := l_cost_group_id;
2895 IF x_return_status = fnd_api.g_ret_sts_error THEN
2896 RAISE fnd_api.g_exc_error;
2897 END IF;
2898 END IF;
2899
2900 IF l_cg_quantity_table.exists(rec_msn.cost_group_id) THEN
2901 l_cg_quantity_table(rec_msn.cost_group_id).quantity :=
2902 l_cg_quantity_table(rec_msn.cost_group_id).quantity + 1;
2903 ELSE
2904 IF l_cg_quantity_table.COUNT = 0 THEN
2905 -- If the table is empty then the existing
2906 -- transaction_temp_id should be used as the
2907 -- new_transaction_temp_id also
2908 l_cg_quantity_table(rec_msn.cost_group_id).new_transaction_temp_id := p_mmtt_rec.transaction_temp_id;
2909 l_cg_quantity_table(rec_msn.cost_group_id).update_mmtt := TRUE;
2910 ELSE
2911 -- otherwise generate a new_transaction_temp_id
2912 SELECT mtl_material_transactions_s.NEXTVAL
2913 INTO l_transaction_temp_id
2914 FROM dual;
2915 IF (l_debug = 1) THEN
2916 print_debug('l_transaction_temp_id: ' || l_transaction_temp_id);
2917 END IF;
2918 l_cg_quantity_table(rec_msn.cost_group_id).new_transaction_temp_id := l_transaction_temp_id;
2919 l_cg_quantity_table(rec_msn.cost_group_id).update_mmtt := FALSE;
2920 END IF;
2921 l_cg_quantity_table(rec_msn.cost_group_id).quantity := 1;
2922 END IF;
2923 END IF;
2924 END LOOP;
2925
2926 FETCH cur_msnt INTO rec_msnt;
2927 END LOOP;
2928
2929 CLOSE cur_msnt;
2930
2931 -- Insert or update the records in l_cg_quantity_table into MMTT
2932 IF (l_debug = 1) THEN
2933 print_debug('proc_process_serial..Inserting records INTO MMTT ');
2934 print_debug('count: ' || l_cg_quantity_table.count);
2935 END IF;
2936 IF l_cg_quantity_table.COUNT > 0 THEN
2937 i := l_cg_quantity_table.first;
2938 IF p_mmtt_rec.transaction_quantity >= 0 THEN
2939 l_quantity_sign := 1;
2940 ELSE
2941 l_quantity_sign := -1;
2942 END IF;
2943
2944 LOOP
2945 l_transaction_quantity := inv_convert.inv_um_convert
2946 (p_mmtt_rec.inventory_item_id,
2947 5,
2948 l_cg_quantity_table(i).quantity,
2949 p_mmtt_rec.item_primary_uom_code,
2950 p_mmtt_rec.transaction_uom,
2951 NULL,
2952 NULL);
2953
2954 l_transaction_quantity := l_transaction_quantity * l_quantity_sign;
2955 IF (l_debug = 1) THEN
2956 print_debug('qty: ' || l_transaction_quantity);
2957 print_debug('qty sign: ' || l_quantity_sign);
2958 END IF;
2959 IF l_cg_quantity_table(i).update_mmtt = FALSE THEN
2960 IF (l_debug = 1) THEN
2961 print_debug('trx_temp_id: ' || l_cg_quantity_table(i).new_transaction_temp_id);
2962 END IF;
2963
2964 proc_insert_mmtt(p_mmtt_rec,
2965 p_transfer_wms_org,
2966 p_fob_point,
2967 p_tfr_primary_cost_method,
2968 p_tfr_org_cost_group_id,
2969 i, -- Remember that i is also the cost_group_id of the record
2970 NULL,
2971 l_cg_quantity_table(i).quantity * l_quantity_sign,
2972 l_transaction_quantity,
2973 l_cg_quantity_table(i).new_transaction_temp_id,
2974 p_from_project_id,
2975 p_to_project_id,
2976 x_return_status);
2977
2978 IF (l_debug = 1) THEN
2979 print_debug('proc_insert_mmtt return : ' || x_return_status);
2980 END IF;
2981 ELSE
2982 proc_update_mmtt(p_mmtt_rec.transaction_temp_id,
2983 p_transfer_wms_org,
2984 p_fob_point,
2985 p_tfr_primary_cost_method,
2986 p_tfr_org_cost_group_id,
2987 p_mmtt_rec.transaction_action_id,
2988 p_mmtt_rec.transfer_organization,
2989 p_mmtt_rec.transfer_subinventory,
2990 i, -- Remember that i is also the cost_group_id of the record
2991 NULL,
2992 l_cg_quantity_table(i).quantity * l_quantity_sign,
2993 l_transaction_quantity,
2994 p_from_project_id,
2995 p_to_project_id,
2996 x_return_status);
2997
2998 IF (l_debug = 1) THEN
2999 print_debug('proc_update_mmtt return : ' || x_return_status);
3000 END IF;
3001 END IF;
3002 IF (x_return_status = fnd_api.g_ret_sts_error)
3003 THEN
3004 RAISE fnd_api.g_exc_error ;
3005 END IF;
3006
3007 IF (x_return_status = fnd_api.g_ret_sts_unexp_error)
3008 THEN
3009 RAISE fnd_api.g_exc_unexpected_error ;
3010 END IF;
3011
3012 EXIT WHEN i = l_cg_quantity_table.last;
3013 i := l_cg_quantity_table.next(i);
3014 END LOOP;
3015
3016 -- Insert or update the records in l_serial_table into MSNT
3017 IF (l_debug = 1) THEN
3018 print_debug('IN proc_process_serial..Inserting records INTO MSNT');
3019 END IF;
3020 FOR i IN 1..l_serial_table.COUNT LOOP
3021 IF (l_debug = 1) THEN
3022 print_debug('cg_id: ' || l_serial_table(i).cost_group_id);
3023 print_debug('FSN: ' || l_serial_table(i).from_serial_number);
3024 print_debug('TSN: ' || l_serial_table(i).to_serial_number);
3025 print_debug('txn tmp id: ' || l_cg_quantity_table(l_serial_table(i).cost_group_id).new_transaction_temp_id);
3026 END IF;
3027
3028 IF l_serial_table(i).update_msnt = TRUE THEN -- Update the MSNT records
3029 IF (l_debug = 1) THEN
3030 print_debug('updating MSNT ');
3031 print_debug('row_id: ' || l_msnt_rowid_table(i));
3032 END IF;
3033 proc_update_msnt(l_msnt_rowid_table(i),
3034 l_cg_quantity_table(l_serial_table(i).cost_group_id).new_transaction_temp_id,
3035 l_serial_table(i).from_serial_number,
3036 l_serial_table(i).to_serial_number,
3037 x_return_status);
3038
3039 IF (l_debug = 1) THEN
3040 print_debug('proc_update_msnt return : ' || x_return_status);
3041 END IF;
3042 ELSE -- Insert into MSNT to create new records
3043 IF (l_debug = 1) THEN
3044 print_debug('inserting into MSNT ');
3045 END IF;
3046 proc_insert_msnt(l_msnt_table(i),
3047 l_serial_table(i).from_serial_number,
3048 l_serial_table(i).to_serial_number,
3049 l_cg_quantity_table(l_serial_table(i).cost_group_id).new_transaction_temp_id,
3050 x_return_status);
3051
3052 IF (l_debug = 1) THEN
3053 print_debug('proc_insert_msnt return : ' || x_return_status);
3054 END IF;
3055 END IF;
3056
3057 IF (x_return_status = fnd_api.g_ret_sts_error)
3058 THEN
3059 RAISE fnd_api.g_exc_error ;
3060 END IF;
3061
3062 IF (x_return_status = fnd_api.g_ret_sts_unexp_error)
3063 THEN
3064 RAISE fnd_api.g_exc_unexpected_error ;
3065 END IF;
3066
3067 END LOOP;
3068 END IF;
3069
3070 EXCEPTION
3071 WHEN FND_API.G_EXC_ERROR THEN
3072 IF (l_debug = 1) THEN
3073 print_debug('proc_process_serial .. EXCEP G_EXC_ERROR : ' );
3074 END IF;
3075 x_return_status := FND_API.G_RET_STS_ERROR ;
3076 IF cur_msnt%isopen THEN
3077 CLOSE cur_msnt;
3078 END IF;
3079 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3080 IF (l_debug = 1) THEN
3081 print_debug('proc_process_serial .. EXCEP G_EXC_UNEXPECTED_ERROR : ' );
3082 END IF;
3083 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3084 IF cur_msnt%isopen THEN
3085 CLOSE cur_msnt;
3086 END IF;
3087 WHEN OTHERS THEN
3088 IF (l_debug = 1) THEN
3089 print_debug('proc_process_serial .. EXCEP OTHERS:' || SQLERRM(SQLCODE) );
3090 END IF;
3091 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3092 IF cur_msnt%isopen THEN
3093 CLOSE cur_msnt;
3094 END IF;
3095 END proc_process_serials;
3096
3097 -- Processes the lot and serial controlled items. This involves splitting
3098 -- MMTT, MTLT and MSNT lines so that each row corresponds to a unique cost
3099 -- group.
3100 PROCEDURE proc_process_lot_serial
3101 (p_mmtt_rec IN mtl_material_transactions_temp%ROWTYPE,
3102 p_fob_point IN NUMBER,
3103 p_transfer_wms_org IN BOOLEAN,
3104 p_tfr_primary_cost_method IN NUMBER,
3105 p_tfr_org_cost_group_id IN NUMBER,
3106 p_from_project_id IN NUMBER,
3107 p_to_project_id IN NUMBER,
3108 x_return_status OUT NOCOPY VARCHAR2,
3109 x_msg_count OUT NOCOPY NUMBER,
3110 x_msg_data OUT NOCOPY VARCHAR2)
3111 IS
3112 l_api_name CONSTANT VARCHAR2(100) := 'proc_process_lot_serial';
3113 l_transaction_temp_id NUMBER := NULL;
3114 i INTEGER;
3115 j INTEGER;
3116 l_transaction_quantity NUMBER;
3117 l_quantity_sign NUMBER;
3118 l_cost_group_id NUMBER;
3119 l_ser_trx_tmp_id NUMBER; -- bug 1936698
3120
3121 --Bug 3390284 Changed the logic in in this procedure to prevent the
3122 --splitting of mtlt if not necessary.
3123 --1. Changed the existing structure 'lot_serial_record', holding
3124 --lot/serial information to 'serial_record'. The structure 'serial_record'
3125 --now holds information about records IN msnt only
3126 --2.Added New record type lot_record, holds information about a row in mtlt
3127 --3.lot_cg_quantity record is indexed by cost_group and holds the quantity
3128 --against that cost_group, also holds the serial_transaction_temp_id
3129 --This is cleared before processing each mtlt record, built while
3130 --processing that record, and after processing the mtlt record completely,
3131 --the information in this table is copied to to lot_table
3132
3133 -- For putting records in MTLT and MSNT tables
3134 TYPE serial_record IS RECORD
3135 (from_serial_number mtl_serial_numbers.serial_number%TYPE,
3136 to_serial_number mtl_serial_numbers.serial_number%TYPE,
3137 --lot_number mtl_serial_numbers.lot_number%TYPE,
3138 quantity NUMBER,
3139 cost_group_id NUMBER,
3140 new_serial_transaction_temp_id NUMBER,
3141 --update_mtlt BOOLEAN,
3142 update_msnt BOOLEAN);
3143
3144 TYPE serial_table IS TABLE OF serial_record INDEX BY BINARY_INTEGER;
3145 l_serial_table serial_table;
3146 l_sti INTEGER := 1;
3147
3148 TYPE cg_quantity_record IS RECORD
3149 (new_transaction_temp_id NUMBER,
3150 quantity NUMBER,
3151 update_mmtt BOOLEAN);
3152 TYPE cg_quantity_table IS TABLE OF cg_quantity_record INDEX BY BINARY_INTEGER;
3153 l_cg_quantity_table cg_quantity_table;
3154
3155 --Bug 3390284
3156 TYPE lot_cg_qty_record IS RECORD
3157 (quantity NUMBER,
3158 serial_transaction_temp_id NUMBER);
3159
3160 TYPE lot_cg_qty_table_tp IS TABLE OF lot_cg_qty_record INDEX BY BINARY_INTEGER;
3161 lot_cg_qty_table lot_cg_qty_table_tp;
3162
3163 TYPE lot_record IS RECORD
3164 (mtlt_rowid ROWID,
3165 lot_number mtl_lot_numbers.lot_number%TYPE,
3166 quantity NUMBER,
3167 serial_transaction_temp_id NUMBER,
3168 cost_group_id NUMBER,
3169 update_mtlt boolean);
3170
3171
3172 TYPE lot_table_tp IS TABLE OF lot_record INDEX BY BINARY_INTEGER;
3173 l_lot_table lot_table_tp;
3174 l_lti INTEGER := 0;
3175 --Bug 3390284
3176
3177 TYPE mtlt_rowid_table IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
3178 l_mtlt_rowid_table mtlt_rowid_table;
3179
3180 TYPE msnt_rowid_table IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
3181 l_msnt_rowid_table msnt_rowid_table;
3182
3183 TYPE msnt_table IS TABLE OF cur_msnt%ROWTYPE INDEX BY BINARY_INTEGER;
3184 l_msnt_table msnt_table;
3185
3186 TYPE mtlt_table IS TABLE OF cur_mtlt%ROWTYPE INDEX BY BINARY_INTEGER;
3187 l_mtlt_table mtlt_table;
3188
3189 rec_mtlt cur_mtlt%ROWTYPE;
3190 rec_msnt cur_msnt%ROWTYPE;
3191
3192 call_lot_control BOOLEAN := FALSE;
3193
3194 l_onhand_exists BOOLEAN;
3195 l_lpn_id NUMBER := p_mmtt_rec.lpn_id;
3196 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3197 l_last NUMBER;
3198 lot_cgi NUMBER;
3199
3200 --Bug 3686015 fix
3201 l_temp_prefix VARCHAR2(30):=NULL;
3202 l_from_ser_number NUMBER := NULL;
3203 l_fm_ser_length NUMBER := NULL;
3204 l_to_temp_prefix VARCHAR2(30) := NULL;
3205 l_to_ser_number NUMBER := NULL;
3206 --Bug 3686015 fix
3207 BEGIN
3208 x_return_status := fnd_api.g_ret_sts_success;
3209 SAVEPOINT sp_proc_process_lot_serial;
3210
3211 IF (l_debug = 1) THEN
3212 print_debug('IN proc_process_lot_serial.. ');
3213 END IF;
3214 --3390284
3215 l_lot_table.DELETE;
3216 --3390284
3217 OPEN cur_mtlt(p_mmtt_rec.transaction_temp_id);
3218
3219 FETCH cur_mtlt INTO rec_mtlt;
3220
3221 WHILE cur_mtlt%found LOOP
3222 IF (l_debug = 1) THEN
3223 print_debug('trx_id: ' || p_mmtt_rec.transaction_temp_id);
3224 END IF;
3225 j := 1;
3226
3227 --3390284
3228 lot_cg_qty_table.DELETE;
3229 --3390284
3230
3231 OPEN cur_msnt(rec_mtlt.serial_transaction_temp_id);
3232
3233 FETCH cur_msnt INTO rec_msnt;
3234
3235 -- If the serial control is dynamic entry at sales order issue check if
3236 -- there are any records in MSNT. If there are no records there treat the
3237 -- item as a lot control item.
3238 IF p_mmtt_rec.item_serial_control_code = 6 AND cur_msnt%notfound THEN
3239 CLOSE cur_msnt;
3240 call_lot_control := TRUE;
3241 EXIT;
3242 ELSE
3243 WHILE cur_msnt%found LOOP
3244 /* Bug 2424354: The variable i has to be reset to 1 for each record from MSNT
3245 rather than for each record from MTLT */
3246 i := 1;
3247 IF (l_debug = 1) THEN
3248 print_debug('ser_trx_id: ' || rec_mtlt.serial_transaction_temp_id);
3249 print_debug('FSN: ' || rec_msnt.fm_serial_number);
3250 print_debug('TSN: ' || rec_msnt.to_serial_number);
3251 print_debug('ORG: ' || p_mmtt_rec.organization_id );
3252 print_debug('ITEM: ' || p_mmtt_rec.inventory_item_id);
3253 END IF;
3254
3255 --Bug 3686015
3256 inv_validate.number_from_sequence(rec_msnt.fm_serial_number, l_temp_prefix, l_from_ser_number);
3257 l_fm_ser_length := Length(rec_msnt.fm_serial_number);
3258
3259 IF (l_debug = 1) THEN
3260 print_debug('FSNPREFIX: ' || l_temp_prefix);
3261 print_debug('FSNNUMERIC: ' || l_from_ser_number);
3262 print_debug('FSNLENGTH: ' || l_fm_ser_length);
3263 END IF;
3264
3265 IF (rec_msnt.to_serial_number IS NOT NULL) AND
3266 (rec_msnt.to_serial_number <> rec_msnt.fm_serial_number) THEN
3267 IF Length(rec_msnt.to_serial_number)<>l_fm_ser_length THEN
3268 IF (l_debug = 1) THEN
3269 print_debug('ERROR: Length of FSN diff from TSN');
3270 END IF;
3271 fnd_message.set_name('INV', 'INV_FROM_TO_SER_DIFF_LENGTH');
3272 fnd_message.set_token('FM_SER_NUM',rec_msnt.fm_serial_number);
3273 fnd_message.set_token('TO_SER_NUM', rec_msnt.to_serial_number);
3274 fnd_msg_pub.add;
3275 RAISE fnd_api.g_exc_error;
3276 END IF;
3277
3278 -- get the number part of the to serial
3279 inv_validate.number_from_sequence(rec_msnt.to_serial_number, l_to_temp_prefix, l_to_ser_number);
3280
3281 IF (l_debug = 1) THEN
3282 print_debug('TSNPREFIX: ' || l_to_temp_prefix);
3283 print_debug('TSNNUMERIC: ' || l_to_ser_number);
3284 END IF;
3285
3286 IF (l_temp_prefix IS NOT NULL) AND (l_to_temp_prefix IS NOT NULL) AND
3287 (l_to_temp_prefix <> l_temp_prefix) THEN
3288 IF (l_debug = 1) THEN
3289 print_debug('ERROR: From serial prefix different from to serial prefix');
3290 END IF;
3291 fnd_message.set_name('INV', 'INV_FROM_TO_SER_DIFF_PFX');
3292 fnd_message.set_token('FM_SER_NUM',rec_msnt.fm_serial_number);
3293 fnd_message.set_token('TO_SER_NUM', rec_msnt.to_serial_number);
3294 fnd_msg_pub.add;
3295 RAISE fnd_api.g_exc_error;
3296 END IF;
3297
3298 END IF;
3299 --Bug 3686015
3300
3301
3302
3303
3304 FOR rec_msn IN cur_msn(rec_msnt.fm_serial_number,
3305 rec_msnt.to_serial_number,
3306 p_mmtt_rec.inventory_item_id,
3307 p_mmtt_rec.organization_id,
3308 l_temp_prefix,
3309 l_fm_ser_length)
3310 LOOP
3311 IF (l_debug = 1) THEN
3312 print_debug('In MSN cursor');
3313 END IF;
3314 l_onhand_exists := TRUE;
3315
3316 -- Adjustment transactions
3317 IF p_mmtt_rec.transaction_action_id IN (inv_globals.g_type_cycle_count_adj,
3318 inv_globals.g_type_physical_count_adj,
3319 inv_globals.g_action_deliveryadj)
3320 THEN
3321 IF p_mmtt_rec.transaction_action_id = inv_globals.g_type_physical_count_adj THEN
3322 IF p_mmtt_rec.lpn_id IS NOT NULL THEN
3323 l_lpn_id := p_mmtt_rec.lpn_id;
3324 ELSIF p_mmtt_rec.content_lpn_id IS NOT NULL THEN
3325 l_lpn_id := p_mmtt_rec.content_lpn_id;
3326 ELSIF p_mmtt_rec.transfer_lpn_id IS NOT NULL THEN
3327 l_lpn_id := p_mmtt_rec.transfer_lpn_id;
3328 END IF;
3329 ELSIF p_mmtt_rec.transaction_action_id = inv_globals.g_action_deliveryadj THEN
3330 l_lpn_id := p_mmtt_rec.transfer_lpn_id;
3331 ELSIF p_mmtt_rec.transaction_action_id = inv_globals.g_type_cycle_count_adj THEN
3332 l_lpn_id := p_mmtt_rec.transfer_lpn_id;
3333 END IF;
3334
3335 l_onhand_exists :=
3336 onhand_quantity_exists
3337 (p_inventory_item_id => p_mmtt_rec.inventory_item_id,
3338 p_revision => p_mmtt_rec.revision,
3339 p_organization_id => p_mmtt_rec.organization_id,
3340 p_subinventory_code => p_mmtt_rec.subinventory_code,
3341 p_locator_id => p_mmtt_rec.locator_id,
3342 p_lot_number => rec_mtlt.lot_number,
3343 p_serial_number => rec_msn.serial_number,
3344 p_lpn_id => l_lpn_id);
3345 IF NOT l_onhand_exists THEN
3346 IF (l_debug = 1) THEN
3347 print_debug('Treating this as as receipt transaction...: ');
3348 print_debug('Getting transfer cost group id from rules engine...: ');
3349 END IF;
3350 wms_costgroupengine_pvt.assign_cost_group
3351 (p_api_version => 1.0,
3352 p_init_msg_list => FND_API.G_FALSE,
3353 p_commit => FND_API.G_FALSE,
3354 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3355 x_return_status => x_return_Status,
3356 x_msg_count => x_msg_count,
3357 x_msg_data => x_msg_data,
3358 p_line_id => p_mmtt_rec.transaction_temp_id,
3359 p_input_type => wms_costgroupengine_pvt.g_input_mmtt);
3360
3361 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
3362 IF (l_debug = 1) THEN
3363 print_debug('return error from wms_costgroupengine_pvt');
3364 END IF;
3365 RAISE FND_API.G_EXC_ERROR;
3366 ELSIF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3367 IF (l_debug = 1) THEN
3368 print_debug('return unexpected error from wms_costgroupengine_pvt');
3369 END IF;
3370 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3371 END IF;
3372
3373 END IF;
3374 ELSIF (p_mmtt_rec.transaction_source_type_id = inv_globals.g_sourcetype_salesorder
3375 OR p_mmtt_rec.transaction_source_type_id = inv_globals.g_sourcetype_intorder)
3376 AND (p_mmtt_rec.transaction_action_id = inv_globals.g_action_issue
3377 OR p_mmtt_rec.transaction_action_id = inv_globals.g_action_subxfr
3378 OR p_mmtt_rec.transaction_action_id = inv_globals.g_action_orgxfr
3379 OR p_mmtt_rec.transaction_action_id = inv_globals.g_action_intransitshipment)
3380 THEN -- For a ship confirm transaction, get the cost group from the content lpn ID
3381 l_lpn_id := p_mmtt_rec.content_lpn_id;
3382
3383 --Bug 2631651 fix. For sales order issue transactions,if the serial control is set at
3384 --sales order issue msn wouldn't have cost group id
3385 --stamped so we have to get the cost group from onhand,
3386 --considering the item as a non serial controlled item
3387 --(by passing null for p_serial_number parameter)
3388
3389 IF p_mmtt_rec.item_serial_control_code = 6 AND rec_msn.cost_group_id IS NULL THEN
3390
3391 proc_determine_costgroup(p_organization_id => p_mmtt_rec.organization_id,
3392 p_inventory_item_id => p_mmtt_rec.inventory_item_id,
3393 p_subinventory_code => p_mmtt_rec.subinventory_code,
3394 p_locator_id => p_mmtt_rec.locator_id,
3395 p_revision => p_mmtt_rec.revision,
3396 p_lot_number => rec_mtlt.lot_number,
3397 p_serial_number => NULL,
3398 p_containerized_flag => 2, -- param is ignored by the PROCEDURE
3399 p_lpn_id => l_lpn_id,
3400 p_transaction_action_id => p_mmtt_rec.transaction_action_id,
3401 p_is_backflush_txn => FALSE,
3402 x_cost_group_id => l_cost_group_id,
3403 x_return_status => x_return_status);
3404
3405 rec_msn.cost_group_id := l_cost_group_id;
3406
3407 END IF;
3408 --Bug 2631651 fix
3409 /* Bug 4628878: For staging transfers, when whole LPN is being transfered, cost group should
3410 * be obtained from content_lpn_id */
3411 ELSIF (
3412 ((p_mmtt_rec.transaction_source_type_id = inv_globals.g_sourcetype_salesorder
3413 OR p_mmtt_rec.transaction_source_type_id = inv_globals.g_sourcetype_intorder)
3414 AND (p_mmtt_rec.transaction_action_id = inv_globals.g_action_stgxfr))
3415 OR/*Bug 6499833:For move order sub transfers,trying to get the costgroup from content_lpn_id.*/
3416 (( p_mmtt_rec.transaction_source_type_id = inv_globals.g_sourcetype_moveorder)
3417 AND (p_mmtt_rec.transaction_action_id = inv_globals.g_action_subxfr))
3418 )
3419 -- AND (p_mmtt_rec.lpn_id IS NULL) Bug#6770593
3420 AND (p_mmtt_rec.content_lpn_id IS NOT NULL)
3421 AND (p_mmtt_rec.inventory_item_id <> -1)
3422 THEN -- For a staging transfer transaction with content_lpn_id, get the cost group from the content lpn ID
3423 l_lpn_id := p_mmtt_rec.content_lpn_id;
3424
3425 --Bug 2631651 fix. For sales order issue transactions,if the serial control is set at
3426 --sales order issue msn wouldn't have cost group id
3427 --stamped so we have to get the cost group from onhand,
3428 --considering the item as a non serial controlled item
3429 --(by passing null for p_serial_number parameter)
3430
3431 IF p_mmtt_rec.item_serial_control_code = 6 AND rec_msn.cost_group_id IS NULL THEN
3432
3433 proc_determine_costgroup(p_organization_id => p_mmtt_rec.organization_id,
3434 p_inventory_item_id => p_mmtt_rec.inventory_item_id,
3435 p_subinventory_code => p_mmtt_rec.subinventory_code,
3436 p_locator_id => p_mmtt_rec.locator_id,
3437 p_revision => p_mmtt_rec.revision,
3438 p_lot_number => rec_mtlt.lot_number,
3439 p_serial_number => NULL,
3440 p_containerized_flag => 2, -- param is ignored by the PROCEDURE
3441 p_lpn_id => l_lpn_id,
3442 p_transaction_action_id => p_mmtt_rec.transaction_action_id,
3443 p_is_backflush_txn => FALSE,
3444 x_cost_group_id => l_cost_group_id,
3445 x_return_status => x_return_status);
3446
3447 rec_msn.cost_group_id := l_cost_group_id;
3448
3449 END IF;
3450 --Bug 2631651 fix
3451
3452 END IF;
3453
3454 IF l_onhand_exists THEN
3455
3456 IF i=1 THEN --When the l_serial_table is empty
3457 l_serial_table(l_sti).from_serial_number := rec_msn.serial_number;
3458 l_serial_table(l_sti).to_serial_number := rec_msn.serial_number;
3459 --l_serial_table(l_sti).lot_number := rec_mtlt.lot_number;
3460 l_serial_table(l_sti).cost_group_id := rec_msn.cost_group_id;
3461 l_serial_table(l_sti).quantity := 1;
3462
3463 IF lot_cg_qty_table.exists(rec_msn.cost_group_id) then
3464 l_serial_table(l_sti).new_serial_transaction_temp_id
3465 := lot_cg_qty_table(rec_msn.cost_group_id).serial_transaction_temp_id;
3466 --Bug 3390284
3467 lot_cg_qty_table(rec_msn.cost_group_id).quantity := lot_cg_qty_table(rec_msn.cost_group_id).quantity + 1;
3468 --Bug 3390284
3469 ELSE
3470 SELECT mtl_material_transactions_s.NEXTVAL
3471 INTO l_transaction_temp_id
3472 FROM dual;
3473
3474 l_serial_table(l_sti).new_serial_transaction_temp_id:= l_transaction_temp_id;
3475
3476 --Bug 3390284
3477 lot_cg_qty_table(rec_msn.cost_group_id).quantity := 1;
3478 lot_cg_qty_table(rec_msn.cost_group_id).serial_transaction_temp_id := l_transaction_temp_id;
3479 --Bug 3390284
3480 END IF;
3481
3482 l_serial_table(l_sti).update_msnt := TRUE;
3483 l_msnt_rowid_table(l_sti) := rec_msnt.msnt_rowid;
3484
3485 IF j = 1 THEN
3486 --l_serial_table(l_sti).update_mtlt := TRUE;
3487 l_mtlt_rowid_table(l_sti) := rec_mtlt.mtlt_rowid;
3488 j := j + 1;
3489 ELSE
3490 --l_serial_table(l_sti).update_mtlt := FALSE;
3491 NULL;
3492 END IF;
3493
3494 l_msnt_table(l_sti) := rec_msnt;
3495 --l_mtlt_table(l_sti) := rec_mtlt;
3496
3497 l_sti := l_sti + 1;
3498 i := i + 1;
3499 ELSIF i<>1 THEN -- When there are records in l_serial_table
3500 -- If the Cost Group ID of this record is the same as that of
3501 -- the previous record then extend the serial number range of
3502 -- the previous record otherwise insert a new record
3503 IF rec_msn.cost_group_id = l_serial_table(l_sti-1).cost_group_id THEN
3504 l_serial_table(l_sti-1).to_serial_number := rec_msn.serial_number;
3505 l_serial_table(l_sti-1).quantity := l_serial_table(l_sti-1).quantity + 1;
3506
3507 --3390284
3508 IF lot_cg_qty_table.exists(rec_msn.cost_group_id) then
3509 lot_cg_qty_table(rec_msn.cost_group_id).quantity :=
3510 lot_cg_qty_table(rec_msn.cost_group_id).quantity + 1 ;
3511 ELSE
3512 RAISE fnd_api.g_exc_error ;
3513 END IF;
3514 --Bug 3390284
3515 ELSE
3516 l_serial_table(l_sti).from_serial_number := rec_msn.serial_number;
3517 l_serial_table(l_sti).to_serial_number := rec_msn.serial_number;
3518 --l_serial_table(l_sti).lot_number := rec_mtlt.lot_number;
3519 l_serial_table(l_sti).cost_group_id := rec_msn.cost_group_id;
3520 l_serial_table(l_sti).quantity := 1;
3521
3522 SELECT mtl_material_transactions_s.NEXTVAL
3523 INTO l_transaction_temp_id
3524 FROM dual;
3525
3526 --3390284
3527 IF lot_cg_qty_table.exists(rec_msn.cost_group_id) then
3528 lot_cg_qty_table(rec_msn.cost_group_id).quantity :=
3529 lot_cg_qty_table(rec_msn.cost_group_id).quantity + 1 ;
3530 l_transaction_temp_id := lot_cg_qty_table(rec_msn.cost_group_id).serial_transaction_temp_id;
3531 ELSE
3532 lot_cg_qty_table(rec_msn.cost_group_id).quantity := 1;
3533 lot_cg_qty_table(rec_msn.cost_group_id).serial_transaction_temp_id := l_transaction_temp_id;
3534 END IF;
3535 --Bug 3390284
3536
3537 l_serial_table(l_sti).new_serial_transaction_temp_id:= l_transaction_temp_id;
3538 l_serial_table(l_sti).update_msnt := FALSE;
3539 -- l_serial_table(l_sti).update_mtlt := FALSE;
3540
3541 l_msnt_table(l_sti) := rec_msnt;
3542 --l_mtlt_table(l_sti) := rec_mtlt;
3543
3544 l_sti := l_sti + 1;
3545 END IF;
3546 END IF;
3547
3548
3549 IF rec_msn.cost_group_id IS NULL THEN
3550 proc_get_pending_costgroup(p_organization_id => p_mmtt_rec.organization_id,
3551 p_inventory_item_id => p_mmtt_rec.inventory_item_id,
3552 p_subinventory_code => p_mmtt_rec.subinventory_code,
3553 p_locator_id => p_mmtt_rec.locator_id,
3554 p_revision => p_mmtt_rec.revision,
3555 p_lot_number => p_mmtt_rec.lot_number,
3556 p_serial_number => rec_msn.serial_number,
3557 p_lpn_id => p_mmtt_rec.lpn_id,
3558 p_transaction_action_id => p_mmtt_rec.transaction_action_id,
3559 x_cost_group_id => l_cost_group_id,
3560 x_return_status => x_return_status);
3561 rec_msn.cost_group_id := l_cost_group_id;
3562 IF x_return_status = fnd_api.g_ret_sts_error THEN
3563 RAISE fnd_api.g_exc_error ;
3564 END IF;
3565 END IF;
3566
3567 IF l_cg_quantity_table.exists(rec_msn.cost_group_id) THEN
3568 l_cg_quantity_table(rec_msn.cost_group_id).quantity :=
3569 l_cg_quantity_table(rec_msn.cost_group_id).quantity + 1;
3570 ELSE
3571 IF l_cg_quantity_table.COUNT = 0 THEN
3572 -- If the table is empty then the existing
3573 -- transaction_temp_id should be used as the
3574 -- new_transaction_temp_id also
3575 l_cg_quantity_table(rec_msn.cost_group_id).new_transaction_temp_id := p_mmtt_rec.transaction_temp_id;
3576 l_cg_quantity_table(rec_msn.cost_group_id).update_mmtt := TRUE;
3577 ELSE
3578 -- otherwise generate a new_transaction_temp_id
3579 SELECT mtl_material_transactions_s.NEXTVAL
3580 INTO l_transaction_temp_id
3581 FROM dual;
3582 l_cg_quantity_table(rec_msn.cost_group_id).new_transaction_temp_id := l_transaction_temp_id;
3583 l_cg_quantity_table(rec_msn.cost_group_id).update_mmtt := FALSE;
3584 END IF;
3585 l_cg_quantity_table(rec_msn.cost_group_id).quantity := 1;
3586 END IF;
3587 END IF;
3588 END LOOP;
3589 FETCH cur_msnt INTO rec_msnt;
3590 END LOOP;
3591 END IF;
3592 IF (l_debug = 1) THEN
3593 print_debug('Closing MSNT cursor... ');
3594 END IF;
3595 CLOSE cur_msnt;
3596
3597 --Bug3390284
3598 --Copying information to l_lot_table
3599 IF lot_cg_qty_table.COUNT > 0 THEN
3600 l_last := lot_cg_qty_table.last;
3601 lot_cgi := lot_cg_qty_table.first;
3602 LOOP
3603 l_lti := l_lti + 1;
3604 l_lot_table(l_lti).mtlt_rowid := rec_mtlt.mtlt_ROWID;
3605 l_lot_table(l_lti).lot_number := rec_mtlt.lot_number;
3606 l_mtlt_table(l_lti) := rec_mtlt;
3607 l_lot_table(l_lti).quantity := lot_cg_qty_table(lot_cgi).quantity;
3608 l_lot_table(l_lti).serial_transaction_temp_id := lot_cg_qty_table(lot_cgi).serial_transaction_temp_id;
3609 l_lot_table(l_lti).cost_group_id := lot_cgi;
3610 IF lot_cgi = lot_cg_qty_table.first THEN
3611 l_lot_table(l_lti).update_mtlt := TRUE;
3612 ELSE
3613 l_lot_table(l_lti).update_mtlt := FALSE;
3614 END IF;
3615
3616 EXIT WHEN (l_last = lot_cgi);
3617
3618 lot_cgi := lot_cg_qty_table.next(lot_cgi);
3619
3620 END LOOP;
3621 END IF;
3622 --Bug3390284
3623
3624 FETCH cur_mtlt INTO rec_mtlt;
3625 END LOOP;
3626 IF (l_debug = 1) THEN
3627 print_debug('Closing MTLT cursor... ');
3628 END IF;
3629 CLOSE cur_mtlt;
3630
3631 -- If the item is to be treated as a lot control item then
3632 IF call_lot_control = TRUE THEN
3633 proc_process_lots
3634 (p_mmtt_rec => p_mmtt_rec,
3635 p_fob_point => p_fob_point,
3636 p_transfer_wms_org => p_transfer_wms_org,
3637 p_tfr_primary_cost_method => p_tfr_primary_cost_method,
3638 p_tfr_org_cost_group_id => p_tfr_org_cost_group_id,
3639 p_from_project_id => p_from_project_id,
3640 p_to_project_id => p_to_project_id,
3641 x_return_status => x_return_status,
3642 x_msg_count => x_msg_count,
3643 x_msg_data => x_msg_data);
3644 RETURN;
3645 END IF;
3646
3647 -- Insert or update the records in l_cg_quantity_table into MMTT
3648 IF (l_debug = 1) THEN
3649 print_debug('count: ' || l_cg_quantity_table.count);
3650 END IF;
3651
3652 IF l_cg_quantity_table.COUNT > 0 THEN
3653 IF (l_debug = 1) THEN
3654 print_debug('proc_process_lot_serial..Inserting records INTO MMTT ');
3655 END IF;
3656 i := l_cg_quantity_table.first;
3657 IF p_mmtt_rec.transaction_quantity >= 0 THEN
3658 l_quantity_sign := 1;
3659 ELSE
3660 l_quantity_sign := -1;
3661 END IF;
3662
3663 IF (l_debug = 1) THEN
3664 print_debug('Primary UOM: ' || p_mmtt_rec.item_primary_uom_code);
3665 print_debug('Txn UOM: ' || p_mmtt_rec.transaction_uom);
3666 print_debug('Qty: ' || l_cg_quantity_table(i).quantity);
3667 END IF;
3668 LOOP
3669 l_transaction_quantity := inv_convert.inv_um_convert
3670 (p_mmtt_rec.inventory_item_id,
3671 5,
3672 l_cg_quantity_table(i).quantity,
3673 p_mmtt_rec.item_primary_uom_code,
3674 p_mmtt_rec.transaction_uom,
3675 NULL,
3676 NULL);
3677 l_transaction_quantity := l_transaction_quantity * l_quantity_sign;
3678 IF (l_debug = 1) THEN
3679 print_debug('qty: ' || l_transaction_quantity);
3680 print_debug('qty sign: ' || l_quantity_sign);
3681 END IF;
3682 IF l_cg_quantity_table(i).update_mmtt = FALSE THEN
3683 proc_insert_mmtt(p_mmtt_rec,
3684 p_transfer_wms_org,
3685 p_fob_point,
3686 p_tfr_primary_cost_method,
3687 p_tfr_org_cost_group_id,
3688 i, -- Remember that i is also the cost_group_id of the record
3689 NULL,
3690 l_cg_quantity_table(i).quantity * l_quantity_sign,
3691 l_transaction_quantity,
3692 l_cg_quantity_table(i).new_transaction_temp_id,
3693 p_from_project_id,
3694 p_to_project_id,
3695 x_return_status);
3696
3697 IF (l_debug = 1) THEN
3698 print_debug('proc_insert_mmtt return : ' || x_return_status);
3699 END IF;
3700 ELSE
3701 proc_update_mmtt(p_mmtt_rec.transaction_temp_id,
3702 p_transfer_wms_org,
3703 p_fob_point,
3704 p_tfr_primary_cost_method,
3705 p_tfr_org_cost_group_id,
3706 p_mmtt_rec.transaction_action_id,
3707 p_mmtt_rec.transfer_organization,
3708 p_mmtt_rec.transfer_subinventory,
3709 i, -- Remember that i is also the cost_group_id of the record
3710 NULL,
3711 l_cg_quantity_table(i).quantity * l_quantity_sign,
3712 l_transaction_quantity,
3713 p_from_project_id,
3714 p_to_project_id,
3715 x_return_status);
3716
3717 IF (l_debug = 1) THEN
3718 print_debug('proc_update_mmtt return : ' || x_return_status);
3719 END IF;
3720 END IF;
3721
3722 IF (x_return_status = fnd_api.g_ret_sts_error)
3723 THEN
3724 RAISE fnd_api.g_exc_error;
3725 END IF;
3726
3727 IF (x_return_status = fnd_api.g_ret_sts_unexp_error)
3728 THEN
3729 RAISE fnd_api.g_exc_unexpected_error;
3730 END IF;
3731
3732 EXIT WHEN i = l_cg_quantity_table.last;
3733 i := l_cg_quantity_table.next(i);
3734 END LOOP;
3735
3736 -- Insert or update the records in l_lot_serial_table into MTLT and MSNT
3737 IF (l_debug = 1) THEN
3738 print_debug('IN proc_process_lot_serial..Inserting records INTO MTLT');
3739 END IF;
3740
3741 --3390284 Now inserting records into mtlt with the information from the lot
3742 --table
3743
3744 FOR lot_i IN 1..l_lot_table.COUNT LOOP
3745 l_transaction_quantity := inv_convert.inv_um_convert
3746 (p_mmtt_rec.inventory_item_id,
3747 5,
3748 l_lot_table(lot_i).quantity,
3749 p_mmtt_rec.item_primary_uom_code,
3750 p_mmtt_rec.transaction_uom,
3751 NULL,
3752 NULL);
3753 IF l_lot_table(lot_i).update_mtlt THEN
3754 proc_update_mtlt(l_lot_table(lot_i).mtlt_rowid,
3755 l_cg_quantity_table(l_lot_table(lot_i).cost_group_id).new_transaction_temp_id,
3756 l_lot_table(lot_i).lot_number,
3757 l_lot_table(lot_i).quantity,
3758 l_transaction_quantity,
3759 l_lot_table(lot_i).serial_transaction_temp_id,
3760 x_return_status);
3761
3762 ELSE
3763 proc_insert_mtlt(l_mtlt_table(lot_i),
3764 l_cg_quantity_table(l_lot_table(lot_i).cost_group_id).new_transaction_temp_id,
3765 l_lot_table(lot_i).quantity,
3766 l_transaction_quantity,
3767 l_lot_table(lot_i).serial_transaction_temp_id,
3768 x_return_status);
3769 END IF;
3770
3771 IF (x_return_status = fnd_api.g_ret_sts_error)
3772 THEN
3773 RAISE fnd_api.g_exc_error ;
3774 END IF;
3775
3776 IF (x_return_status = fnd_api.g_ret_sts_unexp_error)
3777 THEN
3778 RAISE fnd_api.g_exc_unexpected_error ;
3779 END IF;
3780
3781 END LOOP;
3782
3783 --3390284
3784
3785
3786 IF (l_debug = 1) THEN
3787 print_debug('IN proc_process_lot_serial..Inserting records INTO MSNT');
3788 END IF;
3789
3790 FOR i IN 1..l_serial_table.COUNT LOOP
3791
3792
3793 IF l_serial_table(i).update_msnt = TRUE THEN -- Update the MSNT records
3794 IF (l_debug = 1) THEN
3795 print_debug('updating MSNT ');
3796 END IF;
3797 proc_update_msnt(l_msnt_rowid_table(i),
3798 l_serial_table(i).new_serial_transaction_temp_id,
3799 l_serial_table(i).from_serial_number,
3800 l_serial_table(i).to_serial_number,
3801 x_return_status);
3802
3803 IF (l_debug = 1) THEN
3804 print_debug('proc_update_msnt return : ' || x_return_status);
3805 END IF;
3806 ELSE -- Insert into MSNT to create new records
3807 IF (l_debug = 1) THEN
3808 print_debug('inserting into MSNT ');
3809 END IF;
3810 proc_insert_msnt(l_msnt_table(i),
3811 l_serial_table(i).from_serial_number,
3812 l_serial_table(i).to_serial_number,
3813 l_serial_table(i).new_serial_transaction_temp_id,
3814 x_return_status);
3815
3816 IF (l_debug = 1) THEN
3817 print_debug('proc_insert_msnt return : ' || x_return_status);
3818 END IF;
3819 END IF;
3820
3821 IF (x_return_status = fnd_api.g_ret_sts_error)
3822 THEN
3823 RAISE fnd_api.g_exc_error ;
3824 END IF;
3825
3826 IF (x_return_status = fnd_api.g_ret_sts_unexp_error)
3827 THEN
3828 RAISE fnd_api.g_exc_unexpected_error ;
3829 END IF;
3830
3831 END LOOP;
3832 END IF;
3833 EXCEPTION
3834 WHEN FND_API.G_EXC_ERROR THEN
3835 IF (l_debug = 1) THEN
3836 print_debug('proc_process_lot_serial .. EXCEP G_EXC_ERROR : ' );
3837 END IF;
3838 x_return_status := FND_API.G_RET_STS_ERROR ;
3839 IF cur_msnt%isopen THEN
3840 CLOSE cur_msnt;
3841 END IF;
3842 IF cur_mtlt%isopen THEN
3843 CLOSE cur_mtlt;
3844 END IF;
3845 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3846 IF (l_debug = 1) THEN
3847 print_debug('proc_process_lot_serial .. EXCEP G_EXC_UNEXPECTED_ERROR : ' );
3848 END IF;
3849 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3850 IF cur_msnt%isopen THEN
3851 CLOSE cur_msnt;
3852 END IF;
3853 IF cur_mtlt%isopen THEN
3854 CLOSE cur_mtlt;
3855 END IF;
3856 WHEN OTHERS THEN
3857 IF (l_debug = 1) THEN
3858 print_debug('proc_process_lot_serial .. EXCEP OTHERS : ' || SQLERRM(SQLCODE));
3859 END IF;
3860 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3861 IF cur_msnt%isopen THEN
3862 CLOSE cur_msnt;
3863 END IF;
3864 IF cur_mtlt%isopen THEN
3865 CLOSE cur_mtlt;
3866 END IF;
3867 END proc_process_lot_serial;
3868
3869 -- Main Logic
3870
3871 PROCEDURE cost_group_update
3872 (p_transaction_rec IN mtl_material_transactions_temp%ROWTYPE,
3873 p_fob_point IN mtl_interorg_parameters.fob_point%TYPE DEFAULT NULL,
3874 p_transfer_wms_org IN BOOLEAN DEFAULT TRUE,
3875 p_tfr_primary_cost_method IN NUMBER,
3876 p_tfr_org_cost_group_id IN NUMBER,
3877 p_from_project_id IN NUMBER DEFAULT NULL,
3878 p_to_project_id IN NUMBER DEFAULT NULL,
3879 x_return_status OUT NOCOPY VARCHAR2,
3880 x_msg_count OUT NOCOPY NUMBER,
3881 x_msg_data OUT NOCOPY VARCHAR2)
3882 IS
3883 l_api_name CONSTANT VARCHAR2(50) := 'cost_group_update';
3884 l_txn_temp_id NUMBER := 0;
3885 l_prev_rowid ROWID := NULL;
3886 l_prev_org_id mtl_parameters.organization_id%TYPE := NULL;
3887 l_primary_qty NUMBER := NULL;
3888 l_is_lot_control BOOLEAN := NULL;
3889 l_is_serial_control BOOLEAN := NULL;
3890 l_cost_group_id NUMBER := 0;
3891 l_mmtt_rec mtl_material_transactions_temp%ROWTYPE;
3892 l_lpn_id NUMBER := p_transaction_rec.lpn_id;
3893 l_onhand_exists BOOLEAN := TRUE;
3894 l_is_backflush_txn BOOLEAN := FALSE;
3895 l_lot_number VARCHAR2(80); --- BUG#4291891 Joe DiIorio 04/08/2005
3896 l_transfer_cost_group_id NUMBER;
3897 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3898 BEGIN
3899 -- Standard Start of API savepoint
3900
3901 SAVEPOINT API_updatecostgroups;
3902 x_return_status := fnd_api.g_ret_sts_success;
3903
3904 IF (l_debug = 1) THEN
3905 print_debug('Beginning get_cost_group.... with temp_id: ' ||
3906 l_mmtt_rec.transaction_temp_id);
3907 END IF;
3908
3909 l_mmtt_rec := p_transaction_rec;
3910
3911 -- If this is a DirectOrg or an IntransitShipment Transaction and
3912 -- the transfer CostGroupId is NULL, then run the RulesEngine
3913 -- to pick the appropriate CostGroupId for the transfer side
3914 l_cost_group_id := l_mmtt_rec.cost_group_id;
3915 l_transfer_cost_group_id := l_mmtt_rec.transfer_cost_group_id;
3916
3917 IF l_transfer_cost_group_id IS NULL AND p_transfer_wms_org AND
3918 ((l_mmtt_rec.transaction_action_id = inv_globals.g_action_orgxfr) OR
3919 (l_mmtt_rec.transaction_action_id = inv_globals.g_action_intransitshipment
3920 AND p_fob_point = 1)
3921 OR (l_mmtt_rec.transaction_action_id = inv_globals.g_action_intransitreceipt)
3922 )
3923 THEN
3924 IF (l_debug = 1) THEN
3925 print_debug('Getting transfer cost group id from rules engine...: ');
3926 END IF;
3927 wms_costgroupengine_pvt.assign_cost_group
3928 (p_api_version => 1.0,
3929 p_init_msg_list => FND_API.G_FALSE,
3930 p_commit => FND_API.G_FALSE,
3931 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3932 x_return_status => x_return_Status,
3933 x_msg_count => x_msg_count,
3934 x_msg_data => x_msg_data,
3935 p_line_id => l_mmtt_rec.transaction_temp_id,
3936 p_input_type => wms_costgroupengine_pvt.g_input_mmtt);
3937
3938 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
3939 IF (l_debug = 1) THEN
3940 print_debug('return error from wms_costgroupengine_pvt');
3941 END IF;
3942 RAISE FND_API.G_EXC_ERROR;
3943 ELSIF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3944 IF (l_debug = 1) THEN
3945 print_debug('return unexpected error from wms_costgroupengine_pvt');
3946 END IF;
3947 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3948 END IF;
3949
3950 -- Requery MMTT to get the fresh records
3951 SELECT *
3952 INTO l_mmtt_rec
3953 FROM mtl_material_transactions_temp
3954 WHERE transaction_temp_id = l_mmtt_rec.transaction_temp_id;
3955
3956 IF (l_debug = 1) THEN
3957 print_debug('CG from RULES engine: ' || l_mmtt_rec.cost_group_id);
3958 print_debug('TCG from RULES engine: ' || l_mmtt_rec.transfer_cost_group_id);
3959 END IF;
3960 END IF;
3961
3962 -- If transaction is not lpn triggered...
3963 IF l_mmtt_rec.inventory_item_id <> -1 THEN
3964 SELECT lot_control_code, serial_number_control_code, primary_uom_code
3965 INTO l_mmtt_rec.item_lot_control_code,
3966 l_mmtt_rec.item_serial_control_code, l_mmtt_rec.item_primary_uom_code
3967 FROM mtl_system_items
3968 WHERE organization_id = l_mmtt_rec.organization_id
3969 AND inventory_item_id = l_mmtt_rec.inventory_item_id;
3970 ELSE
3971 RETURN;
3972 END IF;
3973
3974 IF inv_globals.is_issue_xfr_transaction(l_mmtt_rec.transaction_action_id) THEN
3975 IF l_mmtt_rec.transfer_cost_group_id IS NOT NULL
3976 AND l_mmtt_rec.cost_group_id IS NOT NULL THEN
3977 RETURN;
3978 ELSIF l_mmtt_rec.cost_group_id IS NOT NULL AND
3979 l_mmtt_rec.transfer_cost_group_id IS NULL THEN
3980 proc_update_mmtt(l_mmtt_rec.transaction_temp_id,
3981 p_transfer_wms_org,
3982 p_fob_point,
3983 p_tfr_primary_cost_method,
3984 p_tfr_org_cost_group_id,
3985 l_mmtt_rec.transaction_action_id,
3986 l_mmtt_rec.transfer_organization,
3987 l_mmtt_rec.transfer_subinventory,
3988 l_cost_group_id,
3989 NULL,
3990 NULL,
3991 NULL,
3992 p_from_project_id,
3993 p_to_project_id,
3994 x_return_status);
3995 IF (l_debug = 1) THEN
3996 print_debug('proc_update_mmtt : ' || x_return_status);
3997 END IF;
3998 IF (x_return_status = fnd_api.g_ret_sts_error)
3999 THEN
4000 RAISE fnd_api.g_exc_error ;
4001 END IF;
4002
4003 IF (x_return_status = fnd_api.g_ret_sts_unexp_error)
4004 THEN
4005 RAISE fnd_api.g_exc_unexpected_error ;
4006 END IF;
4007 RETURN;
4008 END IF;
4009
4010 END IF;
4011
4012 IF (l_debug = 1) THEN
4013 print_debug('Org: ' || l_mmtt_rec.organization_id);
4014 print_debug('Item: ' || l_mmtt_rec.inventory_item_id);
4015 print_debug('Lot control code: ' || l_mmtt_rec.item_lot_control_code);
4016 print_debug('Serial control code: ' || l_mmtt_rec.item_serial_control_code);
4017 print_debug('Action ID: ' || l_mmtt_rec.transaction_action_id);
4018 print_debug('Primary UOM: ' || l_mmtt_rec.item_primary_uom_code);
4019 print_debug('Txn UOM: ' || l_mmtt_rec.transaction_uom);
4020 print_debug('Qty: ' || l_mmtt_rec.transaction_quantity);
4021 END IF;
4022
4023 l_txn_temp_id := l_mmtt_rec.transaction_header_id;
4024 IF (l_debug = 1) THEN
4025 print_debug('header_id:'||l_txn_temp_id || ':');
4026 END IF;
4027
4028 IF l_mmtt_rec.item_lot_control_code = 2
4029 THEN
4030 l_is_lot_control := TRUE;
4031 ELSE
4032 l_is_lot_control := FALSE;
4033 END IF;
4034
4035 IF l_mmtt_rec.item_serial_control_code = 1
4036 THEN
4037 IF (l_debug = 1) THEN
4038 print_debug('l_is_serial_control: FALSE: ' || l_mmtt_rec.item_serial_control_code);
4039 END IF;
4040 l_is_serial_control := FALSE;
4041 ELSE
4042 IF (l_debug = 1) THEN
4043 print_debug('l_is_serial_control: TRUE: ' || l_mmtt_rec.item_serial_control_code);
4044 END IF;
4045 l_is_serial_control := TRUE;
4046 END IF;
4047
4048 -- only one serial/lot number for the line
4049 IF (l_mmtt_rec.serial_number IS NOT NULL) OR
4050 (l_mmtt_rec.lot_number IS NOT NULL) THEN
4051
4052 l_lot_number := l_mmtt_rec.lot_number;
4053 -- Check if there is any MTLT record corresponding to this MMTT, use
4054 -- that to get the value of the lot number
4055 IF l_mmtt_rec.lot_number IS NULL THEN
4056 BEGIN
4057 SELECT lot_number
4058 INTO l_lot_number
4059 FROM mtl_transaction_lots_temp
4060 WHERE transaction_temp_id = l_mmtt_rec.transaction_temp_id;
4061 IF (l_debug = 1) THEN
4062 print_debug('Lot number from MTLT: ' || l_lot_number);
4063 END IF;
4064 EXCEPTION
4065 WHEN no_data_found THEN
4066 IF (l_debug = 1) THEN
4067 print_debug('No MTLT found: ' || l_lot_number);
4068 END IF;
4069 l_lot_number := NULL;
4070 WHEN OTHERS THEN
4071 IF (l_debug = 1) THEN
4072 print_debug('Unexpected error:' || Sqlerrm);
4073 END IF;
4074 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4075 END;
4076 END IF;
4077
4078 IF l_mmtt_rec.transaction_action_id IN (inv_globals.g_type_cycle_count_adj,
4079 inv_globals.g_type_physical_count_adj,
4080 inv_globals.g_action_deliveryadj)
4081 THEN
4082 IF l_mmtt_rec.transaction_action_id IN (inv_globals.g_type_physical_count_adj,
4083 inv_globals.g_action_deliveryadj) THEN
4084 IF l_mmtt_rec.lpn_id IS NOT NULL THEN
4085 l_lpn_id := l_mmtt_rec.lpn_id;
4086 ELSIF l_mmtt_rec.content_lpn_id IS NOT NULL THEN
4087 l_lpn_id := l_mmtt_rec.content_lpn_id;
4088 ELSIF l_mmtt_rec.transfer_lpn_id IS NOT NULL THEN
4089 l_lpn_id := l_mmtt_rec.transfer_lpn_id;
4090 END IF;
4091 ELSIF l_mmtt_rec.transaction_action_id = inv_globals.g_type_cycle_count_adj
4092 THEN
4093 l_lpn_id := l_mmtt_rec.transfer_lpn_id;
4094 END IF;
4095
4096 l_onhand_exists :=
4097 onhand_quantity_exists
4098 (p_inventory_item_id => l_mmtt_rec.inventory_item_id,
4099 p_revision => l_mmtt_rec.revision,
4100 p_organization_id => l_mmtt_rec.organization_id,
4101 p_subinventory_code => l_mmtt_rec.subinventory_code,
4102 p_locator_id => l_mmtt_rec.locator_id,
4103 p_lot_number => l_lot_number,
4104 p_serial_number => l_mmtt_rec.serial_number,
4105 p_lpn_id => l_lpn_id);
4106 IF NOT l_onhand_exists THEN
4107 IF (l_debug = 1) THEN
4108 print_debug('Treating this as as receipt transaction...: ');
4109 print_debug('Getting transfer cost group id from rules engine...: ');
4110 END IF;
4111 wms_costgroupengine_pvt.assign_cost_group
4112 (p_api_version => 1.0,
4113 p_init_msg_list => FND_API.G_FALSE,
4114 p_commit => FND_API.G_FALSE,
4115 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4116 x_return_status => x_return_Status,
4117 x_msg_count => x_msg_count,
4118 x_msg_data => x_msg_data,
4119 p_line_id => l_mmtt_rec.transaction_temp_id,
4120 p_input_type => wms_costgroupengine_pvt.g_input_mmtt);
4121
4122 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
4123 IF (l_debug = 1) THEN
4124 print_debug('return error from wms_costgroupengine_pvt');
4125 END IF;
4126 RAISE FND_API.G_EXC_ERROR;
4127 ELSIF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
4128 IF (l_debug = 1) THEN
4129 print_debug('return unexpected error from wms_costgroupengine_pvt');
4130 END IF;
4131 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4132 END IF;
4133
4134 END IF;
4135 END IF;
4136
4137 IF l_onhand_exists THEN
4138 IF (l_debug = 1) THEN
4139 print_debug('proc_determine_costgroup: ');
4140 END IF;
4141
4142 IF l_mmtt_rec.move_transaction_id IS NOT NULL OR
4143 l_mmtt_rec.completion_transaction_id IS NOT NULL THEN
4144 l_is_backflush_txn := TRUE;
4145 END IF;
4146
4147 proc_determine_costgroup(p_organization_id => l_mmtt_rec.organization_id,
4148 p_inventory_item_id => l_mmtt_rec.inventory_item_id,
4149 p_subinventory_code => l_mmtt_rec.subinventory_code,
4150 p_locator_id => l_mmtt_rec.locator_id,
4151 p_revision => l_mmtt_rec.revision,
4152 p_lot_number => l_lot_number,
4153 p_serial_number => l_mmtt_rec.serial_number,
4154 p_containerized_flag => 2, -- we need unpacked material from moq
4155 p_lpn_id => l_lpn_id,
4156 p_transaction_action_id => l_mmtt_rec.transaction_action_id,
4157 p_is_backflush_txn => l_is_backflush_txn,
4158 x_cost_group_id => l_cost_group_id,
4159 x_return_status => x_return_status);
4160
4161 IF (l_debug = 1) THEN
4162 print_debug('proc_determine_costgroup return : ' || x_return_status);
4163 print_debug('cost_group_id: ' || l_cost_group_id);
4164 END IF;
4165
4166 IF (x_return_status = fnd_api.g_ret_sts_error)
4167 THEN
4168 RAISE fnd_api.g_exc_error ;
4169 END IF;
4170
4171 IF (x_return_status = fnd_api.g_ret_sts_unexp_error)
4172 THEN
4173 RAISE fnd_api.g_exc_unexpected_error ;
4174 END IF;
4175
4176 proc_update_mmtt(l_mmtt_rec.transaction_temp_id,
4177 p_transfer_wms_org,
4178 p_fob_point,
4179 p_tfr_primary_cost_method,
4180 p_tfr_org_cost_group_id,
4181 l_mmtt_rec.transaction_action_id,
4182 l_mmtt_rec.transfer_organization,
4183 l_mmtt_rec.transfer_subinventory,
4184 l_cost_group_id,
4185 NULL,
4186 NULL,
4187 NULL,
4188 p_from_project_id,
4189 p_to_project_id,
4190 x_return_status);
4191 IF (l_debug = 1) THEN
4192 print_debug('proc_update_mmtt : ' || x_return_status);
4193 END IF;
4194 IF (x_return_status = fnd_api.g_ret_sts_error)
4195 THEN
4196 RAISE fnd_api.g_exc_error ;
4197 END IF;
4198
4199 IF (x_return_status = fnd_api.g_ret_sts_unexp_error)
4200 THEN
4201 RAISE fnd_api.g_exc_unexpected_error ;
4202 END IF;
4203 END IF; -- If onhand exists
4204 ELSE
4205 IF l_is_lot_control THEN
4206 IF l_is_serial_control THEN
4207 -- Lot and serial controlled
4208 IF (l_debug = 1) THEN
4209 print_debug('proc_process_lot_serial: ');
4210 END IF;
4211 proc_process_lot_serial
4212 (p_mmtt_rec => l_mmtt_rec,
4213 p_fob_point => p_fob_point,
4214 p_transfer_wms_org => p_transfer_wms_org,
4215 p_tfr_primary_cost_method => p_tfr_primary_cost_method,
4216 p_tfr_org_cost_group_id => p_tfr_org_cost_group_id,
4217 p_from_project_id => p_from_project_id,
4218 p_to_project_id => p_to_project_id,
4219 x_return_status => x_return_status,
4220 x_msg_count => x_msg_count,
4221 x_msg_data => x_msg_data);
4222 IF (l_debug = 1) THEN
4223 print_debug('proc_process_lot_serial: x_return_status: ' || x_return_status);
4224 END IF;
4225 IF (x_return_status = fnd_api.g_ret_sts_error)
4226 THEN
4227 RAISE fnd_api.g_exc_error ;
4228 END IF;
4229
4230 IF (x_return_status = fnd_api.g_ret_sts_unexp_error)
4231 THEN
4232 RAISE fnd_api.g_exc_unexpected_error ;
4233 END IF;
4234 ELSE
4235 -- Lot controlled
4236 proc_process_lots
4237 (p_mmtt_rec => l_mmtt_rec,
4238 p_fob_point => p_fob_point,
4239 p_transfer_wms_org => p_transfer_wms_org,
4240 p_tfr_primary_cost_method => p_tfr_primary_cost_method,
4241 p_tfr_org_cost_group_id => p_tfr_org_cost_group_id,
4242 p_from_project_id => p_from_project_id,
4243 p_to_project_id => p_to_project_id,
4244 x_return_status => x_return_status,
4245 x_msg_count => x_msg_count,
4246 x_msg_data => x_msg_data);
4247
4248 IF (l_debug = 1) THEN
4249 print_debug('proc_process_lots return: ' || x_return_status);
4250 END IF;
4251 IF (x_return_status = fnd_api.g_ret_sts_error)
4252 THEN
4253 RAISE fnd_api.g_exc_error ;
4254 END IF;
4255
4256 IF (x_return_status = fnd_api.g_ret_sts_unexp_error)
4257 THEN
4258 RAISE fnd_api.g_exc_unexpected_error ;
4259 END IF;
4260 END IF;
4261 ELSE
4262 IF l_is_serial_control THEN
4263 -- Serial control
4264 IF (l_debug = 1) THEN
4265 print_debug('is_serial_control: ' );
4266 END IF;
4267 -- Mrana inv_cost_group_update.proc_process_serials
4268 proc_process_serials
4269 (p_mmtt_rec => l_mmtt_rec,
4270 p_fob_point => p_fob_point,
4271 p_transfer_wms_org => p_transfer_wms_org,
4272 p_tfr_primary_cost_method => p_tfr_primary_cost_method,
4273 p_tfr_org_cost_group_id => p_tfr_org_cost_group_id,
4274 p_from_project_id => p_from_project_id,
4275 p_to_project_id => p_to_project_id,
4276 x_return_status => x_return_status,
4277 x_msg_count => x_msg_count,
4278 x_msg_data => x_msg_data);
4279
4280 IF (l_debug = 1) THEN
4281 print_debug('proc_process_serials return: ' || x_return_status);
4282 END IF;
4283 IF (x_return_status = fnd_api.g_ret_sts_error)
4284 THEN
4285 RAISE fnd_api.g_exc_error ;
4286 END IF;
4287
4288 IF (x_return_status = fnd_api.g_ret_sts_unexp_error)
4289 THEN
4290 RAISE fnd_api.g_exc_unexpected_error ;
4291 END IF;
4292 ELSE
4293 -- No control
4294 IF (l_debug = 1) THEN
4295 print_debug('Nocontrol: Call proc_process_nocontrol: Trx temp id:'
4296 || l_mmtt_rec.transaction_temp_id);
4297 END IF;
4298 -- process mmtt
4299 proc_process_nocontrol
4300 (p_mmtt_rec => l_mmtt_rec,
4301 p_fob_point => p_fob_point,
4302 p_transfer_wms_org => p_transfer_wms_org,
4303 p_tfr_org_cost_group_id => p_tfr_org_cost_group_id,
4304 p_tfr_primary_cost_method => p_tfr_primary_cost_method,
4305 p_from_project_id => p_from_project_id,
4306 p_to_project_id => p_to_project_id,
4307 x_return_status => x_return_status,
4308 x_msg_count => x_msg_count,
4309 x_msg_data => x_msg_data);
4310 IF (l_debug = 1) THEN
4311 print_debug('proc_process_nocontrol return : ' || x_return_status);
4312 END IF;
4313 IF (x_return_status = fnd_api.g_ret_sts_error)
4314 THEN
4315 RAISE fnd_api.g_exc_error ;
4316 END IF;
4317
4318 IF (x_return_status = fnd_api.g_ret_sts_unexp_error)
4319 THEN
4320 RAISE fnd_api.g_exc_unexpected_error ;
4321 END IF;
4322 IF (l_debug = 1) THEN
4323 print_debug('after call to nocontrol');
4324 END IF;
4325 END IF;
4326 END IF;
4327 END IF;
4328 IF (l_debug = 1) THEN
4329 print_debug('Processing Over... ' || x_return_status );
4330 END IF;
4331
4332 EXCEPTION
4333 WHEN FND_API.G_EXC_ERROR THEN
4334 IF (l_debug = 1) THEN
4335 print_debug('inv_cost_group_update .. EXCEP G_EXC_ERROR : ' );
4336 END IF;
4337 ROLLBACK TO API_updatecostgroups;
4338 x_return_status := FND_API.G_RET_STS_ERROR ;
4339 FND_MSG_PUB.Count_And_Get
4340 (p_encoded => FND_API.G_FALSE,
4341 p_count => x_msg_count,
4342 p_data => x_msg_data);
4343 IF (l_debug = 1) THEN
4344 print_debug(' over ');
4345 END IF;
4346 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4347 IF (l_debug = 1) THEN
4348 print_debug('inv_cost_group_update .. EXCEP G_EXC_UNEXPECTED_ERROR : ' );
4349 END IF;
4350 ROLLBACK TO API_updatecostgroups;
4351 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4352 FND_MSG_PUB.Count_And_Get
4353 (p_encoded => FND_API.G_FALSE,
4354 p_count => x_msg_count,
4355 p_data => x_msg_data);
4356 IF (l_debug = 1) THEN
4357 print_debug(' over ' );
4358 END IF;
4359 WHEN OTHERS THEN
4360 IF (l_debug = 1) THEN
4361 print_debug('inv_cost_group_update .. EXCEP G_EXC_UNEXPECTED_ERROR : ' );
4362 END IF;
4363 ROLLBACK TO API_updatecostgroups;
4364 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4365 IF fnd_msg_pub.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4366 THEN
4367 FND_MSG_PUB.Add_Exc_Msg (g_pkg_name,
4368 l_api_name );
4369 END IF;
4370 FND_MSG_PUB.Count_And_Get
4371 (p_encoded => FND_API.G_FALSE,
4372 p_count => x_msg_count,
4373 p_data => x_msg_data);
4374 IF (l_debug = 1) THEN
4375 print_debug(' over ');
4376 END IF;
4377 END cost_group_update;
4378
4379 END inv_cost_group_update;