1 PACKAGE BODY INV_COST_GROUP_PVT AS
2 /* $Header: INVVDCGB.pls 120.3 2007/08/19 00:31:22 mchemban ship $ */
3
4
5 is_debug BOOLEAN := TRUE;
6
7 --Bug 5214608 ( FP of 2879206 ) constant identifying the current transaction getting processed
8 g_current_txn_temp_id NUMBER := NULL;
9
10 INV_COMINGLE_ERROR Exception;
11
12
13 -- Start of Comments
14 -- API name Assign_Cost_Group
15 -- Type Public
16 -- Function
17 --
18 -- Pre-reqs
19 --
20 -- Parameters
21 --
22 -- Version Current version = 1.0
23 -- Initial version = 1.0
24 --
25 -- End of Comments
26
27 PROCEDURE print_debug(p_message IN VARCHAR2) IS
28 --Bug 3559334 fix. Need not call the fnd_api again here since
29 --this procedure is invoked only if the Inv:Debug Trace is enabled
30 --l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
31 BEGIN
32
33 IF (is_debug = TRUE) THEN
34 --IF (l_debug = 1) THEN
35 inv_log_util.trace(p_message, 'INV_COST_GROUP_PVT', 9);
36 --END IF;
37 END IF;
38 END;
39
40 -- added the average_cost_var_account as a parameter
41
42 PROCEDURE get_default_cost_group(x_return_status OUT NOCOPY VARCHAR2,
43 x_msg_count OUT NOCOPY NUMBER,
44 x_msg_data OUT NOCOPY VARCHAR2,
45 x_cost_group_id OUT NOCOPY NUMBER,
46 p_material_account IN NUMBER,
47 p_material_overhead_account IN NUMBER,
48 p_resource_account IN NUMBER,
49 p_overhead_account IN NUMBER,
50 p_outside_processing_account IN NUMBER,
51 p_expense_account IN NUMBER,
52 p_encumbrance_account IN NUMBER,
53 p_average_cost_var_account IN NUMBER DEFAULT NULL,
54 p_organization_id IN NUMBER,
55 p_cost_group IN VARCHAR2 DEFAULT NULL)
56 IS
57 l_cost_group_id_tbl cstpcgut.cost_group_tbl;
58 l_count number;
59 l_found boolean := FALSE;
60 l_cost_group varchar2(10);
61
62 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
63 BEGIN
64
65 cstpcgut.get_cost_group
66 (
67 x_return_status => x_return_status
68 , x_msg_count => x_msg_count
69 , x_msg_data => x_msg_data
70 , x_cost_group_id_tbl => l_cost_group_id_tbl
71 , x_count => l_count
72 , p_material_account => p_material_account
73 , p_material_overhead_account => p_material_overhead_account
74 , p_resource_account => p_resource_account
75 , p_overhead_account => p_overhead_account
76 , p_outside_processing_account => p_outside_processing_account
77 , p_expense_account => p_expense_account
78 , p_encumbrance_account => p_encumbrance_account
79 , p_average_cost_var_account => p_average_cost_var_account
80 , p_organization_id => p_organization_id
81 , p_cost_group_type_id => 3);
82
83 if (l_count > 0) then
84 for i in 1..l_count
85 loop
86 select cost_group
87 into l_cost_group
88 from cst_cost_groups
89 where cost_group_id = l_cost_group_id_tbl(i);
90
91 if l_cost_group = p_cost_group then
92 x_cost_group_id := l_cost_group_id_tbl(i);
93 l_found := TRUE;
94 exit;
95 end if;
96 end loop;
97 end if;
98
99 if NOT(l_found) then
100 cstpcgut.create_cost_group
101 (
102 x_return_status => x_return_status
103 , x_msg_count => x_msg_count
104 , x_msg_data => x_msg_data
105 , x_cost_group_id => x_cost_group_id
106 , p_cost_group => p_cost_group
107 , p_material_account => p_material_account
108 , p_material_overhead_account => p_material_overhead_account
109 , p_resource_account => p_resource_account
110 , p_overhead_account => p_overhead_account
111 , p_outside_processing_account => p_outside_processing_account
112 , p_expense_account => p_expense_account
113 , p_encumbrance_account => p_encumbrance_account
114 , p_organization_id => p_organization_id
115 , p_average_cost_var_account => p_average_cost_var_account
116 , p_cost_group_type_id => 3);
117 end if;
118
119 EXCEPTION
120 WHEN FND_API.G_EXC_ERROR THEN
121 x_return_status := FND_API.G_RET_STS_ERROR;
122 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
123 p_data => x_msg_data);
124
125 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
126 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
127 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
128 p_data => x_msg_data);
129
130 WHEN OTHERS THEN
131 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
132 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
133 FND_MSG_PUB.Add_Exc_Msg
134 ( G_PKG_NAME, 'INV_COST_GROUP_PUB');
135 END IF;
136
137 END get_default_cost_group;
138
139 --Bug#6343400.Added the following procedure
140 PROCEDURE Calculate_Transfer_Cost
141 (
142 p_mmtt_temp_id IN NUMBER
143 , x_return_status OUT NOCOPY VARCHAR2
144 , x_msg_count OUT NOCOPY NUMBER
145 , x_msg_data OUT NOCOPY VARCHAR2
146 ) IS
147 l_interorg_xfer_code mtl_interorg_parameters.matl_interorg_transfer_code%type;
148 l_interorg_charge_prct mtl_interorg_parameters.interorg_trnsfr_charge_percent%type;
149 l_transfer_cost NUMBER ;
150 l_item_cost NUMBER := 0 ;
151 l_item_id NUMBER ;
152 l_org_id NUMBER ;
153 l_xfer_org_id NUMBER ;
154 l_cg_id NUMBER ;
155 l_pri_qty NUMBER ;
156 l_trx_action_id NUMBER ;
157 l_primary_cost_method NUMBER := 1 ;
158 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
159 BEGIN
160 SAVEPOINT Calculate_Transfer_Cost_SP;
161 x_return_status := fnd_api.g_ret_sts_success;
162
163 IF ( l_debug = 1) THEN
164 print_debug('Calculate_Transfer_Cost : Entered with temp id:' ||p_mmtt_temp_id );
165 END IF;
166
167 SELECT inventory_item_id, organization_id,transfer_organization,cost_group_id, nvl(transfer_cost,0),
168 abs(primary_quantity), transaction_action_id
169 INTO l_item_id,l_org_id,l_xfer_org_id, l_cg_id,l_transfer_cost,l_pri_qty, l_trx_action_id
170 FROM Mtl_Material_Transactions_Temp
171 WHERE transaction_temp_id = p_mmtt_temp_id;
172
173 IF ( l_debug = 1) THEN
174 print_debug('item id:'||l_item_id||'org id:'|| l_org_id ||', xfer org id :'|| l_xfer_org_id ||',CG:'||l_cg_id||',xfrcost:'||l_transfer_cost);
175 END IF;
176
177 SELECT NVL(primary_cost_method,1) INTO l_primary_cost_method
178 FROM MTL_PARAMETERS
179 WHERE organization_id = l_org_id ;
180
181 IF (l_primary_cost_method = 1 ) THEN
182 IF (l_debug = 1) THEN
183 print_debug('Calculate_Transfer_Cost :This org uses primary cost method..so exiting' );
184 END IF;
185 RETURN ;
186 END IF;
187
188 IF (l_trx_action_id not in ( INV_GLOBALS.G_ACTION_ORGXFR , INV_GLOBALS.G_ACTION_INTRANSITSHIPMENT ) ) THEN
189 IF (l_debug = 1) THEN
190 print_debug('Calculate_Transfer_Cost :This is not an org xfer, so no need of calculating transfer cost..exiting' );
191 END IF;
192 RETURN ;
193 END IF;
194
195 SELECT NVL(matl_interorg_transfer_code,1) , interorg_trnsfr_charge_percent
196 INTO l_interorg_xfer_code , l_interorg_charge_prct
197 FROM mtl_interorg_parameters
198 WHERE from_organization_id = l_org_id
199 AND to_organization_id = l_xfer_org_id ;
200
201 IF (l_interorg_xfer_code NOT IN (4, 3 ) ) THEN
202 IF (l_debug = 1) THEN
203 print_debug('Calculate_Transfer_Cost :matl_interorg_transfer_code ='|| l_interorg_xfer_code||' , so exiting' );
204 END IF;
205 RETURN ;
206 END IF;
207
208 IF (l_interorg_xfer_code = 3 ) THEN
209 l_interorg_charge_prct := l_transfer_cost * 100 / l_pri_qty ;
210 END IF;
211
212 IF (l_debug = 1) THEN
213 print_debug('Calculate_Transfer_Cost : mtl_interorg_transfer_code : ' ||l_interorg_xfer_code|| ',interorg_trnsfr_charge_percent:'||l_interorg_charge_prct );
214 END IF;
215
216 SELECT NVL(ccicv.item_cost, 0) INTO l_item_cost
217 FROM cst_cg_item_costs_view ccicv
218 WHERE ccicv.inventory_item_id= l_item_id
219 AND ccicv.organization_id= l_org_id
220 AND ccicv.cost_group_id = l_cg_id ;
221
222 l_transfer_cost := l_interorg_charge_prct / 100 * l_item_cost * l_pri_qty ;
223
224 IF (l_debug = 1) THEN
225 print_debug('Calculate_Transfer_Cost : item_cost :'|| l_item_cost || ',transfer_cost :'||l_transfer_cost);
226 END IF;
227
228 UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP
229 SET TRANSFER_COST = l_transfer_cost
230 WHERE TRANSACTION_TEMP_ID = p_mmtt_temp_id;
231
232 IF (l_debug = 1) THEN
233 print_debug('Calculate_Transfer_Cost : Updated MMTT with transfer cost ..Exiting API.');
234 END IF;
235
236 EXCEPTION
237 WHEN NO_DATA_FOUND THEN
238 ROLLBACK TO Calculate_Transfer_Cost_SP;
239 IF (l_debug = 1) THEN
240 print_debug('Calculate_Transfer_Cost :No Data found !!!! ');
241 END IF;
242 x_return_status := FND_API.G_RET_STS_ERROR;
243
244 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
245 p_data => x_msg_data);
246
247 WHEN OTHERS THEN
248 ROLLBACK TO Calculate_Transfer_Cost_SP;
249 IF (l_debug = 1) THEN
250 print_debug('Calculate_Transfer_Cost :Others Exception !!!! ');
251 END IF;
252 x_return_status := FND_API.G_RET_STS_ERROR;
253
254 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
255 p_data => x_msg_data);
256 END Calculate_Transfer_Cost;
257
258
259 PROCEDURE Assign_Cost_Group
260 (
261 p_api_version_number IN NUMBER
262 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
263 , p_commit IN VARCHAR2 := FND_API.G_FALSE
264 , x_return_status OUT NOCOPY VARCHAR2
265 , x_msg_count OUT NOCOPY NUMBER
266 , x_msg_data OUT NOCOPY VARCHAR2
267 , p_transaction_header_id IN NUMBER
268 )
269 IS
270 l_transaction_header_id NUMBER := p_transaction_header_id;
271 l_organization_id NUMBER;
272 l_cost_Group_id NUMBER;
273 l_transfer_cost_Group_id NUMBER;
274 l_process_txn VARCHAR2(1) := 'Y';
275 l_fob_point mtl_interorg_parameters.fob_point%TYPE;
276 l_to_project_id NUMBER := NULL;
277 l_comingling_occurs VARCHAR2(1) := 'N';
278 cursor trx_cursor is
279 select mmtt.*
280 from mtl_material_transactions_temp mmtt
281 WHERE
282 transaction_header_id = p_transaction_header_id
283 AND PROCESS_FLAG = 'Y'
284 AND NVL(TRANSACTION_STATUS,1) <> 2 ; /* 2STEP */
285 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
286 BEGIN
287
288 SAVEPOINT assign_cost_group_hdr;
289
290 g_failure_txn_temp_id := NULL; -- Bug 5214602
291
292 x_return_status := fnd_api.g_ret_sts_success;
293 IF (l_debug = 1) THEN
294 print_debug('In assign cost group pass with header id ' || l_transaction_header_id);
295 END IF;
296
297 -- l_process_txn := 'Y'; -- Process the transaction otherwise skip it, since it already has
298 -- Cost group populated.
299 -- Moved the assignment inside the for loop for bug 2233573
300
301
302 -- Update all the rows which have null transaction_temp_id for this
303 -- transaction_header_id
304 UPDATE mtl_material_transactions_temp
305 SET transaction_temp_id = mtl_material_transactions_s.NEXTVAL
306 WHERE transaction_header_id = p_transaction_header_id
307 AND transaction_temp_id IS NULL;
308
309
310
311 FOR rec_trx_cursor IN trx_cursor
312 LOOP
313 g_current_txn_temp_id := rec_trx_cursor.transaction_temp_id; --5214602 : FP of Bug 2879206
314 inv_cost_group_pvt.assign_cost_group
315 (x_return_status => x_return_status,
316 x_msg_data => x_msg_data,
317 x_msg_count => x_msg_count,
318 p_mmtt_rec => rec_trx_cursor,
319 p_fob_point => null,
320 p_line_id => rec_trx_cursor.transaction_temp_id,
321 p_organization_id => rec_trx_cursor.organization_id,
322 p_input_type => INV_COST_GROUP_PUB.G_INPUT_MMTT,
323 x_cost_group_id => l_cost_group_id,
324 x_transfer_cost_group_id => l_transfer_cost_Group_id);
325
326 if( x_return_status = INV_COST_GROUP_PVT.G_COMINGLE_ERROR ) then
327 RAISE inv_comingle_error;
328 elsif( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) then
329 raise FND_API.G_EXC_UNEXPECTED_ERROR;
330 elsif( x_return_status = FND_API.G_RET_STS_ERROR ) then
331 raise FND_API.G_EXC_ERROR;
332 end if;
333 END LOOP;
334
335 EXCEPTION
336 WHEN inv_comingle_error THEN
337 ROLLBACK TO assign_cost_group_hdr;
338 x_return_status := FND_API.G_RET_STS_ERROR;
339 --Bug 5214602 : FP of 2879206
340 g_failure_txn_temp_id := g_current_txn_temp_id;
341 print_debug('Failed Txn Temp Id : ' || g_failure_txn_temp_id );
342 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
343 WHEN FND_API.G_EXC_ERROR THEN
344 ROLLBACK TO assign_cost_group_hdr;
345 x_return_status := FND_API.G_RET_STS_ERROR;
346 --Bug 5214602 : FP of 2879206
347 g_failure_txn_temp_id := g_current_txn_temp_id;
348 print_debug('Failed Txn Temp Id : ' || g_failure_txn_temp_id );
349 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
350 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
351 ROLLBACK TO assign_cost_group_hdr;
352 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
353 --Bug 5214602 : FP of 2879206
354 g_failure_txn_temp_id := g_current_txn_temp_id;
355 print_debug('Failed Txn Temp Id : ' || g_failure_txn_temp_id );
356 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
357
358 WHEN OTHERS THEN
359 ROLLBACK TO assign_cost_group_hdr;
360 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
361 --Bug 5214602 : FP of 2879206
362 g_failure_txn_temp_id := g_current_txn_temp_id;
363 print_debug('Failed Txn Temp Id : ' || g_failure_txn_temp_id );
364 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
365 FND_MSG_PUB.Add_Exc_Msg
366 ( G_PKG_NAME, 'INV_COST_GROUP_PUB');
367 end if;
368 END;
369
370 PROCEDURE Assign_Cost_Group
371 (
372 x_return_status OUT NOCOPY VARCHAR2
373 ,x_msg_count OUT NOCOPY NUMBER
374 ,x_msg_data OUT NOCOPY VARCHAR2
375 ,p_organization_id IN NUMBER
376 ,p_mmtt_rec IN mtl_material_transactions_temp%ROWTYPE DEFAULT NULL
377 ,p_fob_point IN mtl_interorg_parameters.fob_point%TYPE DEFAULT NULL
378 ,p_line_id IN NUMBER
379 ,p_input_type IN VARCHAR2
380 ,x_cost_group_id OUT NOCOPY NUMBER
381 ,x_transfer_cost_group_id OUT NOCOPY NUMBER
382 ) IS
383 l_organization_id NUMBER := p_organization_id;
384 l_transfer_organization_id NUMBER;
385 l_transaction_action_id NUMBER;
386 l_line_id NUMBER := p_line_id;
387 l_wms_org_flag boolean;
388 l_transfer_wms_org_flag boolean;
389 l_cost_Group_id NUMBER;
390 l_org_cost_Group_id NUMBER;
391 l_tfr_org_cost_Group_id NUMBER;
392 l_transfer_cost_group_id NUMBER;
393 l_subinventory_code VARCHAR2(10);
394 l_transfer_subinventory VARCHAR2(10);
395 l_primary_cost_method NUMBER;
396 l_tfr_primary_cost_method NUMBER;
397 l_from_project_id NUMBER := null;
398 l_to_project_id NUMBER := null;
399 l_from_locator_id NUMBER := null;
400 l_to_locator_id NUMBER := null;
401
402 l_process_txn VARCHAR2(1) := 'Y';
403 l_comingling_occurs VARCHAR2(1) := 'N';
404 l_fob_point mtl_interorg_parameters.fob_point%TYPE := p_mmtt_rec.fob_point;
405 --assign mmttvalue to variable FOB_POINT Changes
406 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
407 BEGIN
408
409 --Bug 5214602 : FP of 2879206
410 g_current_txn_temp_id := p_mmtt_rec.transaction_temp_id;
411 /****************Code added*********************/
412
413 SAVEPOINT assign_cost_group;
414
415 IF (p_input_type = G_INPUT_MMTT) THEN
416
417 l_process_txn := 'Y';
418
419 IF (l_debug = 1) THEN
420 print_debug('transaction_header_id : ' || p_mmtt_rec.transaction_header_id);
421 print_debug('transaction_temp_id : ' || p_mmtt_rec.transaction_Temp_id);
422 print_debug('organization_id : ' || p_mmtt_rec.organization_id);
423 print_debug('transaction_action_id : ' || p_mmtt_rec.transaction_action_id);
424 END IF;
425
426 -- If the MMTT line is for a LPN transaction then (in which case
427 -- item_id = -1) we do not process those transactions. In some of
428 -- the transactions the item_id <> -1
429 -- even though they are LPN triggered transactions. We have to
430 -- account FOR them specifically
431 IF (p_mmtt_rec.inventory_item_id = -1) THEN
432
433 x_return_status := fnd_api.g_ret_sts_success;
434 RETURN;
435
436 -- Bug: 4959753: The WIP phantom items are not transactable items
437 -- but are still inserted into MMTT. No onhand or MMT is created for these items
438 -- and Hence no cost group processing is required for these items.
439 -- if the source type is 5 (WIP) and wip_supply_type is 6, we do not
440 -- process such record. Took this condition from INVTXGGB.pls
441 ELSIF ((p_mmtt_rec.TRANSACTION_SOURCE_TYPE_ID = 5) AND
442 (nvl(p_mmtt_rec.OPERATION_SEQ_NUM,1) < 0) AND (nvl(p_mmtt_rec.WIP_SUPPLY_TYPE,0) = 6)) THEN
443 IF (l_debug = 1) THEN
444 print_debug ('Phantom item, Hence skipping processing');
445 END IF;
446 x_return_status := fnd_api.g_ret_sts_success;
447 RETURN;
448
449 -- If the transaction is a logical receipt or a logical delivery
450 -- adjustment, we will not go through the cost group logic. We will
451 -- get the org's default cost group for both the organization
452 -- and the transafer organization.
453 -- IDS: lot serial support
454 ELSIF (p_mmtt_rec.transaction_source_type_id =
455 INV_Globals.G_SOURCETYPE_PURCHASEORDER AND
456 (p_mmtt_rec.transaction_action_id =
457 INV_Globals.G_ACTION_LOGICALRECEIPT OR
458 p_mmtt_rec.transaction_action_id =
459 INV_Globals.g_action_logicaldeladj
460 )
461 ) THEN
462
463 print_debug('Inside get cost group for logical transactions');
464 print_debug('Transaction Action' || p_mmtt_rec.transaction_action_id );
465
466 SELECT default_cost_group_id
467 INTO l_org_cost_group_id
468 FROM mtl_parameters
469 WHERE organization_id = p_mmtt_rec.organization_id;
470
471 print_debug('After selecting the default cost group');
472 print_debug('Cost Group ID' || l_org_cost_group_id);
473
474 IF (p_mmtt_rec.transfer_organization IS NOT NULL) THEN
475 print_debug('Transfer Org. is being populated');
476 SELECT default_cost_group_id
477 INTO l_tfr_org_cost_group_id
478 FROM mtl_parameters
479 WHERE organization_id = p_mmtt_rec.transfer_organization;
480 END IF;
481
482 print_debug('After selecting the default transfer cost group');
483 print_debug('Transfer cost group ID' || l_tfr_org_cost_group_id);
484 print_debug('Return Status before update' ||x_return_status );
485
486 UPDATE mtl_material_transactions_temp
487 SET cost_group_id = l_org_cost_group_id,
488 transfer_cost_group_id = l_tfr_org_cost_group_id
489 WHERE transaction_temp_id = p_mmtt_rec.transaction_temp_id;
490
491 print_debug('Return Status after update' ||x_return_status );
492
493 x_return_status := fnd_api.g_ret_sts_success;
494 RETURN;
495
496 ELSE
497 IF p_mmtt_rec.transaction_action_id IN (INV_Globals.G_Action_IntransitShipment,
498 INV_Globals.G_Action_IntransitReceipt ) THEN
499
500
501 IF l_fob_point IS NULL THEN
502
503 BEGIN
504 SELECT fob_point
505 INTO l_fob_point
506 FROM mtl_interorg_parameters
507 WHERE from_organization_id =
508 Decode(p_mmtt_rec.transaction_action_id,
509 inv_globals.g_action_intransitreceipt,
510 p_mmtt_rec.transfer_organization,
511 p_mmtt_rec.organization_id)
512 AND to_organization_id =
513 Decode(p_mmtt_rec.transaction_action_id,
514 inv_globals.g_action_intransitreceipt,
515 p_mmtt_rec.organization_id,
516 p_mmtt_rec.transfer_organization);
517
518 IF l_fob_point IS NULL THEN
519 IF (l_debug = 1) THEN
520 print_debug ('l_fob_point is null:INV_FOB_NOT_DEFINED');
521 END IF;
522 FND_MESSAGE.SET_NAME('INV', 'INV_FOB_NOT_DEFINED');
523 fnd_message.set_token('ENTITY1',p_mmtt_rec.organization_id );
524 FND_MSG_PUB.ADD;
525 RAISE FND_API.G_EXC_ERROR;
526 END IF;
527 EXCEPTION
528 WHEN NO_DATA_FOUND THEN
529 IF (l_debug = 1) THEN
530 print_debug ('no_data_found:INV_FOB_NOT_DEFINED');
531 END IF;
532 FND_MESSAGE.SET_NAME('INV', 'INV_FOB_NOT_DEFINED');
533 fnd_message.set_token('ENTITY1',p_mmtt_rec.organization_id );
534 FND_MSG_PUB.ADD;
535 RAISE FND_API.G_EXC_ERROR;
536 END;
537 END IF;-- l_fob_point is null
538 END IF;--actions
539
540 IF (l_debug = 1) THEN
541 print_debug('l_fob_point is ' || l_fob_point);
542 END IF;
543 -- l_fob_point = 1 (shipment) = 2 (Receipt)
544
545 IF (l_debug = 1) THEN
546 print_debug('p_mmtt_rec.cost_group_id : ' || p_mmtt_rec.cost_group_id);
547 print_debug('p_mmtt_rec.transfer_cost_group_id : ' || p_mmtt_rec.transfer_cost_group_id);
548 END IF;
549
550
551 IF l_process_txn = 'N' THEN
552 NULL;
553
554 ELSIF p_mmtt_rec.cost_group_id IS NOT NULL THEN
555
556 IF p_mmtt_rec.transaction_action_id IN (INV_Globals.G_Action_Subxfr,INV_Globals.g_action_planxfr,
557 INV_Globals.G_Action_Stgxfr,
558 INV_Globals.g_action_orgxfr,
559 INV_Globals.g_action_ownxfr) THEN
560 IF (p_mmtt_rec.cost_group_id IS NULL OR
561 p_mmtt_rec.transfer_cost_group_id IS NULL) THEN
562
563 l_process_txn := 'Y';
564
565 --Bug 2392914 fix
566 --Most probably we don't need the below code
567 ELSIF p_mmtt_rec.transaction_action_id = INV_Globals.g_action_stgxfr then
568
569 IF (p_mmtt_rec.transfer_to_location IS NOT NULL AND
570 p_mmtt_rec.transfer_to_location > 0 AND
571 p_mmtt_rec.transfer_organization IS NOT NULL AND
572 p_mmtt_rec.transfer_organization > 0) THEN
573
574 BEGIN
575 SELECT project_id INTO l_to_project_id
576 FROM mtl_item_locations
577 WHERE inventory_location_id = p_mmtt_rec.transfer_to_location
578 AND organization_id = p_mmtt_rec.transfer_organization;
579 EXCEPTION
580 WHEN OTHERS THEN
581 IF (l_debug = 1) THEN
582 print_debug('exception in getting to project: ' || Sqlerrm);
583 END IF;
584 RAISE fnd_api.g_exc_unexpected_error;
585 END;
586 ELSE
587 l_to_project_id := NULL;
588 END IF;
589
590 IF l_to_project_id IS NOT NULL THEN
591 IF (l_debug = 1) THEN
592 print_debug('to project is not null');
593 print_debug('setting l_process_txn to N');
594 END IF;
595 --p_mmtt_rec.transfer_cost_group_id := NULL;
596 --Commenting out the above line because we don't
597 --process this RECORD anymore l_process_txn := 'N'
598 --after updating the transfer_cost_group_id as null
599 l_process_txn := 'N';
600
601 BEGIN
602 UPDATE mtl_material_transactions_temp
603 SET transfer_cost_group_id = NULL
604 WHERE
605 transaction_temp_id = p_mmtt_rec.transaction_temp_id;
606 EXCEPTION
607 WHEN OTHERS THEN
608 IF (l_debug = 1) THEN
609 print_debug('exception updating the xfr cost group-null');
610 print_debug('Error :'||Sqlerrm);
611 END IF;
612 END;
613
614 IF (l_debug = 1) THEN
615 print_debug('Setting transfer cost group as null');
616 END IF;
617 END IF;
618
619 IF (l_debug = 1) THEN
620 print_debug('setting l_process_txn to N');
621 END IF;
622 l_process_txn := 'N';
623
624 --Bug 2392914 fix
625
626 ELSE
627 l_process_txn := 'N';
628
629 END IF;
630
631 ELSIF p_mmtt_rec.transaction_action_id = INV_Globals.G_Action_IntransitShipment THEN
632 IF l_fob_point = 1 THEN
633 IF (p_mmtt_rec.cost_group_id IS NULL OR
634 p_mmtt_rec.transfer_cost_group_id IS NULL) THEN
635 l_process_txn := 'Y';
636 ELSE
637 l_process_txn := 'N';
638 END IF;
639 ELSE
640 IF (p_mmtt_rec.cost_group_id IS NULL ) THEN
641 l_process_txn := 'Y';
642 ELSE
643 l_process_txn := 'N';
644 END IF;
645 END IF;
646 ELSIF p_mmtt_rec.transaction_action_id = INV_Globals.G_Action_IntransitReceipt THEN
647 IF l_fob_point = 1 THEN
648 IF (p_mmtt_rec.cost_group_id IS NULL OR
649 p_mmtt_rec.transfer_cost_group_id IS NULL) THEN
650 l_process_txn := 'Y';
651 ELSE
652 l_process_txn := 'N';
653 END IF;
654 ELSE
655 IF (p_mmtt_rec.cost_group_id IS NULL ) THEN
656 l_process_txn := 'Y';
657 ELSE
658 l_process_txn := 'N';
659 END IF;
660 END IF;
661 ELSE -- for all other transaction actions, transfer_cost_group_id will always be null
662 l_process_txn := 'N';
663 END IF;
664 END IF;
665 IF (l_debug = 1) THEN
666 print_debug('l_process_txn.: ' || l_process_txn);
667 END IF;
668
669 IF l_process_txn = 'N' THEN
670 inv_comingling_utils.comingle_check
671 (x_return_status => x_return_status
672 , x_msg_count => x_msg_count
673 , x_msg_data => x_msg_data
674 , x_comingling_occurs => l_comingling_occurs
675 , p_mmtt_rec => p_mmtt_rec);
676
677 IF x_return_status <> fnd_api.g_ret_sts_success THEN
678 RAISE fnd_api.g_exc_unexpected_error;
679 ELSIF l_comingling_occurs = 'Y' THEN
680 IF (l_debug = 1) THEN
681 print_debug('assign_cost_group comingling occurs : ' );
682 END IF;
683 RAISE inv_comingle_error;
684 END IF;
685 END IF;
686 END IF;
687
688 l_line_id := p_mmtt_rec.transaction_temp_id;
689 l_organization_id := p_mmtt_rec.organization_id;
690
691 END IF; -- IF (p_input_type = G_INPUT_MMTT) THEN
692 /****************Code added*********************/
693
694 x_return_status := fnd_api.g_ret_sts_success;
695
696 IF (l_debug = 1) THEN
697 print_debug('in inv_cost_group_pub.assign_cost_group');
698 print_debug('l_line_id : ' || l_line_id);
699 print_debug('l_organization_id : ' || l_organization_id);
700 print_debug('p_input_type : ' || p_input_type);
701 print_debug('l_fob_point : ' || l_fob_point);
702 END IF;
703
704 l_transfer_organization_id:= NULL;
705 l_transaction_action_id := NULL;
706 l_wms_org_flag := FALSE;
707 l_transfer_wms_org_flag := FALSE;
708 l_cost_Group_id := NULL;
709 l_org_cost_Group_id := NULL;
710 l_tfr_org_cost_Group_id := NULL;
711 l_transfer_cost_group_id := NULL;
712 l_subinventory_code := NULL;
713 l_transfer_subinventory := NULL;
714 l_primary_cost_method := NULL;
715 l_tfr_primary_cost_method := NULL;
716
717 l_wms_org_flag := wms_install.check_install
718 (x_return_status => x_return_status,
719 x_msg_count => x_msg_count,
720 x_msg_data => x_msg_data,
721 p_organization_id => l_organization_id);
722 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
723 RAISE FND_API.G_EXC_ERROR;
724 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
725 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
726 END IF;
727
728 IF (p_input_type = G_INPUT_MMTT) THEN
729 l_cost_group_id := p_mmtt_rec.cost_group_id;
730 l_transfer_cost_group_id := p_mmtt_rec.transfer_cost_Group_id;
731 l_subinventory_code := p_mmtt_rec.subinventory_code;
732 l_transfer_subinventory := p_mmtt_rec.transfer_subinventory;
733 l_transaction_action_id := p_mmtt_rec.transaction_action_id;
734 l_transfer_organization_id := p_mmtt_rec.transfer_organization;
735 IF p_mmtt_rec.transaction_action_id IN (INV_Globals.G_Action_Subxfr,INV_Globals.G_Action_Planxfr,
736 INV_Globals.g_action_stgxfr,INV_Globals.g_action_ownxfr) THEN
737 l_transfer_organization_id := p_mmtt_rec.organization_id;
738 END IF;
739
740 IF(p_mmtt_rec.locator_id IS NOT NULL) then
741 BEGIN
742 SELECT project_id INTO l_from_project_id
743 FROM mtl_item_locations
744 WHERE inventory_location_id = p_mmtt_rec.locator_id
745 AND organization_id = p_mmtt_rec.organization_id;
746 EXCEPTION
747 WHEN OTHERS THEN
748 IF (l_debug = 1) THEN
749 print_debug('exception in getting from project: ' || Sqlerrm);
750 END IF;
751 RAISE fnd_api.g_exc_unexpected_error;
752 END;
753 END IF;
754
755 IF(p_mmtt_rec.transfer_to_location IS NOT NULL) then
756
757 BEGIN
758 SELECT project_id INTO l_to_project_id
759 FROM mtl_item_locations
760 WHERE inventory_location_id = p_mmtt_rec.transfer_to_location
761 AND organization_id = l_transfer_organization_id;
762 EXCEPTION
763 WHEN OTHERS THEN
764 IF (l_debug = 1) THEN
765 print_debug('exception in getting to project: ' || Sqlerrm);
766 END IF;
767 RAISE fnd_api.g_exc_unexpected_error;
768 END;
769 END IF;
770
771 IF (l_debug = 1) THEN
772 print_debug('l_from_project_id : ' || l_from_project_id);
773 print_debug('l_to_project_id : ' || l_to_project_id);
774 END IF;
775
776
777 ELSIF (p_input_type = G_INPUT_MOLINE) THEN
778 SELECT from_cost_group_id,
779 to_cost_group_id,
780 from_subinventory_code,
781 to_subinventory_code,
782 from_locator_id,
783 to_locator_id
784 INTO l_cost_Group_id,
785 l_transfer_cost_group_id,
786 l_subinventory_code,
787 l_transfer_subinventory,
788 l_from_locator_id,
789 l_to_locator_id
790 FROM mtl_txn_request_lines
791 WHERE line_id = l_line_id;
792 END IF;
793
794 IF l_transfer_organization_id IS NOT NULL THEN
795 l_transfer_wms_org_flag := wms_install.check_install
796 (x_return_status => x_return_status,
797 x_msg_count => x_msg_count,
798 x_msg_data => x_msg_data,
799 p_organization_id => l_transfer_organization_id);
800 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
801 RAISE FND_API.G_EXC_ERROR;
802 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
803 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
804 END IF;
805
806 SELECT default_cost_group_id,
807 primary_cost_method
808 INTO l_tfr_org_cost_group_id,
809 l_tfr_primary_cost_method
810 FROM mtl_parameters
811 WHERE organization_id = l_transfer_organization_id;
812 IF( l_from_locator_id IS NOT NULL) then
813 BEGIN
814 SELECT project_id INTO l_from_project_id
815 FROM mtl_item_locations
816 WHERE inventory_location_id = l_from_locator_id AND
817 organization_id = l_organization_id;
818 EXCEPTION
819 WHEN OTHERS THEN
820 IF (l_debug = 1) THEN
821 print_debug('exception in getting from project: ' || Sqlerrm);
822 END IF;
823 RAISE fnd_api.g_exc_unexpected_error;
824 END;
825 END IF;
826
827 IF( l_to_locator_id IS NOT NULL) then
828 IF (l_debug = 1) THEN
829 print_debug(' l_transfer_organization_id '||l_transfer_organization_id);
830 END IF;
831 BEGIN
832 SELECT project_id INTO l_to_project_id
833 FROM mtl_item_locations
834 WHERE inventory_location_id = l_to_locator_id AND
835 organization_id = l_transfer_organization_id;
836 EXCEPTION
837 WHEN OTHERS THEN
838 IF (l_debug = 1) THEN
839 print_debug('exception in getting to project: ' || Sqlerrm);
840 END IF;
841 RAISE fnd_api.g_exc_unexpected_error;
842 END;
843 END IF;
844
845 IF (l_debug = 1) THEN
846 print_debug('l_from_project_id : ' || l_from_project_id);
847 print_debug('l_to_project_id : ' || l_to_project_id);
848 END IF;
849
850 END IF;
851 IF (l_debug = 1) THEN
852 print_debug('l_tfr_org_cost_group_id: ' || l_tfr_org_cost_group_id);
853 print_debug('l_tfr_primary_cost_method: ' || l_tfr_primary_cost_method);
854 print_debug('l_transfer_organization_id: ' || l_transfer_organization_id);
855 END IF;
856
857 IF (l_debug = 1) THEN
858 print_debug('l_cost_group_id : ' || l_cost_group_id);
859 print_debug('l_subinventory_code : ' || l_subinventory_code);
860 print_debug('l_transfer_cost_group_id : ' || l_transfer_cost_group_id);
861 print_debug('l_transfer_subinventory : ' || l_transfer_subinventory);
862 END IF;
863
864 SELECT default_cost_group_id,
865 primary_cost_method
866 INTO l_org_cost_group_id,
867 l_primary_cost_method
868 FROM mtl_parameters
869 WHERE organization_id = l_organization_id;
870
871 IF (l_debug = 1) THEN
872 print_debug('l_org_cost_group_id: ' || l_org_cost_group_id);
873 print_debug('l_primary_cost_method: ' || l_primary_cost_method);
874 print_debug('l_organization_id: ' || l_organization_id);
875 END IF;
876
877 IF l_transaction_action_id = inv_globals.g_action_intransitreceipt AND
878 p_mmtt_rec.transfer_cost_group_id IS NULL THEN
879 SELECT cost_group_id
880 INTO l_transfer_cost_group_id
881 FROM rcv_shipment_lines rsl,
882 rcv_transactions rt
883 WHERE rsl.shipment_line_id = rt.shipment_line_id
884 AND rt.transaction_id = p_mmtt_rec.source_line_id;
885 IF (l_debug = 1) THEN
886 print_debug('Intransit transfer cost group ID: ' || l_transfer_cost_group_id);
887 END IF;
888 END IF; -- action = intransit receipt
889
890 IF NOT l_wms_org_flag THEN
891 IF (l_debug = 1) THEN
892 print_debug('l_wms_flag is false ');
893 END IF;
894
895 -- derive cost group from the default cost group in the subinventory
896 IF (l_debug = 1) THEN
897 print_debug('l_transfer_cost_group_id: ' || l_transfer_cost_group_id);
898 END IF;
899 if (l_cost_Group_id is null and l_subinventory_code is not null) then
900 IF (l_debug = 1) THEN
901 print_debug('cost group is null , get the default from sub or org');
902 END IF;
903 IF l_primary_cost_method = 1
904 THEN -- costing method is standard)
905 BEGIN
906 SELECT default_cost_group_id
907 INTO l_cost_group_id
908 FROM mtl_secondary_inventories
909 WHERE secondary_inventory_name = l_subinventory_code
910 AND organization_id = l_organization_id
911 AND default_cost_group_id IS NOT NULL;
912 IF (l_debug = 1) THEN
913 print_debug('l_cost_group of subinventory ' || l_subinventory_code ||
914 ' is ' || l_cost_group_id);
915 END IF;
916 EXCEPTION
917 WHEN no_data_found THEN
918 l_cost_group_id := l_org_cost_group_id;
919 IF (l_debug = 1) THEN
920 print_debug('default cost group of org ' || l_organization_id ||
921 ' is ' || l_cost_group_id);
922 END IF;
923 END;
924 ELSE -- costing method is not standard)
925 l_cost_group_id := l_org_cost_group_id;
926 IF (l_debug = 1) THEN
927 print_debug('non-standard org: default cost group of org ' || l_organization_id ||
928 ' is ' || l_cost_group_id);
929 END IF;
930 END IF;
931 ELSE
932 IF (l_debug = 1) THEN
933 print_debug('l_cost_group_id is not null in mmtt');
934 END IF;
935 END IF;
936 IF (l_debug = 1) THEN
937 print_debug('l_cost_group_id : ' || l_cost_group_id);
938 print_debug('l_transfer_cost_group_id : ' || l_transfer_cost_group_id);
939 END IF;
940 END IF; -- end of not l_wms_org_flag
941
942
943 /* l_transfer_wms_org_flag will be false even when transfer org is null as in sub/stg tfr*/
944 IF (NOT l_wms_org_flag) AND (NOT l_transfer_wms_org_flag) THEN
945 IF (l_debug = 1) THEN
946 print_debug('transfer org is not wms enabled ');
947 END IF;
948
949 -- check if transaction is intransit issue
950 IF l_transaction_Action_id = inv_globals.g_action_intransitshipment THEN
951 IF l_fob_point = 1 THEN -- shipment
952 -- We do not care about the costing method of the org
953 l_transfer_cost_group_id := l_tfr_org_cost_group_id;
954 IF (l_debug = 1) THEN
955 print_debug('default cost group of org ' || p_mmtt_rec.transfer_organization ||
956 ' : ' || l_transfer_cost_group_id);
957 END IF;
958 ELSIF l_fob_point = 2 THEN -- receipt
959 l_transfer_cost_group_id := l_cost_group_id;
960 END IF;
961 END IF;
962
963 IF (l_transfer_cost_Group_id IS NULL AND l_transfer_subinventory IS
964 NOT NULL) AND l_transaction_action_id <> inv_globals.g_action_intransitreceipt THEN
965 IF (l_debug = 1) THEN
966 print_debug('transfer cost group is null , get the default from sub or org');
967 END IF;
968
969 IF l_tfr_primary_cost_method = 1
970 THEN -- costing method is standard)
971 BEGIN
972 select default_cost_group_id
973 into l_transfer_cost_group_id
974 from mtl_secondary_inventories
975 where secondary_inventory_name = l_transfer_subinventory
976 and organization_id = l_transfer_organization_id
977 and default_cost_group_id is not null;
978 IF (l_debug = 1) THEN
979 print_debug('l_transfer_cost_group of sub ' || l_transfer_subinventory ||
980 ' is ' || l_transfer_cost_group_id);
981 END IF;
982 EXCEPTION
983 WHEN no_data_found THEN
984 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
985 l_transfer_cost_group_id := l_tfr_org_cost_group_id;
986 IF (l_debug = 1) THEN
987 print_debug('default cost group of org ' || p_mmtt_rec.transfer_organization ||
988 ' is ' || l_transfer_cost_group_id);
989 END IF;
990 END;
991 ELSE -- costing method is not standard)
992 l_transfer_cost_group_id := l_tfr_org_cost_group_id;
993 IF (l_debug = 1) THEN
994 print_debug('default cost group of org ' || p_mmtt_rec.transfer_organization ||
995 ' : ' || l_transfer_cost_group_id);
996 END IF;
997 END IF;
998 ELSIF l_transfer_cost_Group_id is null AND
999 l_transfer_subinventory is null AND
1000 l_transaction_Action_id = INV_Globals.G_Action_Orgxfr THEN
1001 /* case where trfr sub is null for a direct org transfer */
1002 l_transfer_cost_group_id := l_tfr_org_cost_group_id;
1003 else
1004 IF (l_debug = 1) THEN
1005 print_debug('l_transfer_cost_group is not null or tfr cost group is not to be populated ');
1006 END IF;
1007 end if;
1008
1009 IF (l_debug = 1) THEN
1010 print_debug('l_cost_group_id : ' || l_cost_group_id);
1011 print_debug('l_transfer_cost_group_id : ' || l_transfer_cost_group_id);
1012 END IF;
1013 IF p_input_type = g_input_mmtt then
1014 IF (l_debug = 1) THEN
1015 print_debug('update the mmtt with cost group');
1016 END IF;
1017 update mtl_material_transactions_temp
1018 set cost_Group_id = l_cost_group_id,
1019 transfer_cost_group_id = Nvl(transfer_cost_group_id, l_transfer_cost_group_id)
1020 where transaction_temp_id = l_line_id;
1021 ELSIF p_input_type = G_INPUT_MOLINE THEN
1022 IF (l_debug = 1) THEN
1023 print_debug('update the mtl_txn_request_lines with cost group ' ||
1024 l_cost_group_id || ' and ' || l_transfer_cost_group_id);
1025 END IF;
1026 update mtl_txn_request_lines
1027 set from_cost_Group_id = l_cost_group_id,
1028 to_cost_group_id = Nvl(to_cost_group_id, l_transfer_cost_group_id)
1029 where line_id = l_line_id;
1030 end if;
1031 END IF; -- end of not l_transfer_wms_org
1032
1033 -- Inventory to WMS transfers and WMS to INV transfers
1034 IF (NOT l_wms_org_flag) AND l_transfer_wms_org_flag THEN
1035 IF (l_debug = 1) THEN
1036 print_debug('INV to WMS transfer');
1037 END IF;
1038 -- check if transaction is intransit issue
1039 IF l_transaction_action_id IN (inv_globals.g_action_intransitshipment,
1040 inv_globals.g_action_orgxfr)
1041 THEN
1042 IF (l_fob_point = 1 AND l_transaction_action_id =
1043 inv_globals.g_action_intransitshipment) -- shipment
1044 OR (l_transaction_action_id = inv_globals.g_action_orgxfr) THEN
1045
1046 -- updating the transfer cost group to null for direct org transfers,
1047 -- if the destination is wms org and dest loc is proj enabled
1048 IF( l_to_project_id IS NOT NULL AND
1049 l_transaction_action_id = inv_globals.g_action_orgxfr) THEN
1050 IF (l_debug = 1) THEN
1051 print_debug('Org transfer to a WMS org ..Dest is project locator');
1052 END IF;
1053 IF p_input_type = G_INPUT_MMTT THEN
1054 IF (l_debug = 1) THEN
1055 print_debug('update the mmtt with transfer cost group null');
1056 END IF;
1057 UPDATE mtl_material_transactions_temp
1058 SET transfer_cost_group_id = NULL
1059 WHERE transaction_temp_id = l_line_id;
1060 ELSIF p_input_type = G_INPUT_MOLINE THEN
1061 IF (l_debug = 1) THEN
1062 print_debug('update the mtl_txn_request_lines with to cost group null');
1063 END IF;
1064 UPDATE mtl_txn_request_lines
1065 SET to_cost_group_id = null
1066 WHERE line_id = l_line_id;
1067 END IF;
1068 ELSE
1069 -- change till here
1070 IF (l_debug = 1) THEN
1071 print_debug('Calling the Rules Engine: ');
1072 END IF;
1073 wms_costgroupengine_pvt.assign_cost_group
1074 (p_api_version => 1.0,
1075 p_init_msg_list => fnd_api.g_false,
1076 p_commit => fnd_api.g_false,
1077 p_validation_level => fnd_api.g_valid_level_full,
1078 x_return_status => x_return_status,
1079 x_msg_count => x_msg_count,
1080 x_msg_data => x_msg_data,
1081 p_line_id => l_line_id,
1082 p_input_type => wms_costgroupengine_pvt.g_input_mmtt);
1083 IF (x_return_status = fnd_api.g_ret_sts_error) THEN
1084 IF (l_debug = 1) THEN
1085 print_debug('return error from wms_costgroupengine_pvt');
1086 END IF;
1087 RAISE fnd_api.g_exc_error;
1088 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error) THEN
1089 IF (l_debug = 1) THEN
1090 print_debug('return unexpected error from wms_costgroupengine_pvt');
1091 END IF;
1092 RAISE fnd_api.g_exc_unexpected_error;
1093 END IF;
1094
1095 IF p_input_type = G_INPUT_MMTT THEN
1096 IF (l_debug = 1) THEN
1097 print_debug('update the mmtt with cost group');
1098 END IF;
1099 UPDATE mtl_material_transactions_temp
1100 SET cost_group_id = l_cost_group_id
1101 WHERE transaction_temp_id = l_line_id;
1102 ELSIF p_input_type = G_INPUT_MOLINE THEN
1103 IF (l_debug = 1) THEN
1104 print_debug('update the mtl_txn_request_lines with cost group ' ||
1105 l_cost_group_id || ' and ' || l_transfer_cost_group_id);
1106 END IF;
1107 UPDATE mtl_txn_request_lines
1108 SET from_cost_group_id = l_cost_group_id
1109 WHERE line_id = l_line_id;
1110 END IF;
1111 END IF;-- for org transfer ,dest locator is project
1112
1113
1114 ELSIF (l_fob_point = 2 AND l_transaction_action_id =
1115 inv_globals.g_action_intransitshipment) THEN -- receipt
1116 IF (l_debug = 1) THEN
1117 print_debug('Setting transfer cost group = cost group');
1118 END IF;
1119 l_transfer_cost_group_id := l_cost_group_id;
1120 IF p_input_type = G_INPUT_MMTT THEN
1121 IF (l_debug = 1) THEN
1122 print_debug('update the mmtt with cost group');
1123 END IF;
1124 UPDATE mtl_material_transactions_temp
1125 SET cost_Group_id = l_cost_group_id,
1126 transfer_cost_group_id = l_transfer_cost_group_id
1127 WHERE transaction_temp_id = l_line_id;
1128 END IF;
1129 END IF;
1130 END IF;
1131
1132
1133
1134 IF l_transaction_action_id IN (inv_globals.g_action_intransitreceipt,
1135 inv_globals.g_action_orgxfr)
1136 THEN
1137 --Newly added code for PJM-WMS
1138 IF( l_to_project_id IS NOT NULL AND
1139 l_transaction_action_id = inv_globals.g_action_orgxfr) THEN
1140 IF (l_debug = 1) THEN
1141 print_debug('Org transfer to a WMS org ..Dest is project locator');
1142 END IF;
1143 --We don't want to fill the transfer cost group with l_transfer_cost_group_id
1144 --if the previous code has stamped it as null for the case of
1145 --org transfer to wms + dest locator is project enabled
1146 IF p_input_type = G_INPUT_MMTT THEN
1147 IF (l_debug = 1) THEN
1148 print_debug('update the mmtt with cost group '|| l_cost_group_id);
1149 END IF;
1150 UPDATE mtl_material_transactions_temp
1151 SET cost_group_id = l_cost_group_id
1152 --transfer_cost_group_id = Nvl(transfer_cost_group_id, l_transfer_cost_group_id)
1153 WHERE transaction_temp_id = l_line_id;
1154 ELSIF p_input_type = G_INPUT_MOLINE THEN
1155 IF (l_debug = 1) THEN
1156 print_debug('update the mtl_txn_request_lines with cost group ' || l_cost_group_id);
1157 END IF;
1158 UPDATE mtl_txn_request_lines
1159 SET from_cost_group_id = l_cost_group_id
1160 --to_cost_group_id = Nvl(to_cost_group_id, l_transfer_cost_group_id)
1161 WHERE line_id = l_line_id;
1162 END IF;
1163 ELSE
1164 --Newly added code for PJM-WMS
1165 IF (l_debug = 1) THEN
1166 print_debug('Receipt side of the interorg transfer...');
1167 END IF;
1168 IF p_input_type = G_INPUT_MMTT THEN
1169 IF (l_debug = 1) THEN
1170 print_debug('update the mmtt with cost group');
1171 END IF;
1172 UPDATE mtl_material_transactions_temp
1173 SET cost_group_id = l_cost_group_id,
1174 transfer_cost_group_id = Nvl(transfer_cost_group_id, l_transfer_cost_group_id)
1175 WHERE transaction_temp_id = l_line_id;
1176 ELSIF p_input_type = G_INPUT_MOLINE THEN
1177 IF (l_debug = 1) THEN
1178 print_debug('update the mtl_txn_request_lines with cost group ' ||
1179 l_cost_group_id || ' and ' || l_transfer_cost_group_id);
1180 END IF;
1181 UPDATE mtl_txn_request_lines
1182 SET from_cost_group_id = l_cost_group_id,
1183 to_cost_group_id = Nvl(to_cost_group_id, l_transfer_cost_group_id)
1184 WHERE line_id = l_line_id;
1185 END IF;
1186 END IF; --Newly added code for PJM-WMS
1187 END IF;
1188
1189
1190 END IF; -- INV --> WMS, WMS --> INV
1191
1192 IF (l_debug = 1) THEN
1193 print_debug('l_org_cost_group_id: ' || l_org_cost_group_id);
1194 print_debug('l_primary_cost_method: ' || l_primary_cost_method);
1195 print_debug('l_organization_id: ' || l_organization_id);
1196 print_debug('l_tfr_org_cost_group_id: ' || l_tfr_org_cost_group_id);
1197 print_debug('l_tfr_primary_cost_method: ' || l_tfr_primary_cost_method);
1198 print_debug('l_transfer_organization_id: ' || l_transfer_organization_id);
1199 END IF;
1200
1201 IF (l_debug = 1) THEN
1202 print_debug('l_cost_group_id : ' || l_cost_group_id);
1203 print_debug('l_subinventory_code : ' || l_subinventory_code);
1204 print_debug('l_transfer_cost_group_id : ' || l_transfer_cost_group_id);
1205 print_debug('l_transfer_subinventory : ' || l_transfer_subinventory);
1206 print_debug('l_transfer_organization : ' || l_transfer_organization_id);
1207 print_debug('l_transaction_action_id : ' || l_transaction_action_id);
1208 END IF;
1209
1210 IF (l_wms_org_flag) THEN
1211 IF (l_debug = 1) THEN
1212 print_Debug('l_wms_org_flag is true');
1213 END IF;
1214
1215 /*** WMS-PJM changes *********/
1216 IF(l_from_project_id IS NOT NULL AND
1217 l_to_project_id IS NOT NULL AND
1218 l_transaction_action_id IN (inv_globals.g_action_subxfr,
1219 inv_globals.g_action_stgxfr)) then
1220
1221 IF (l_debug = 1) THEN
1222 print_debug('Source and destination locators are not project enabled');
1223 print_debug('Stamping null cost groups for source and destination');
1224 END IF;
1225
1226 IF p_input_type = G_INPUT_MMTT THEN
1227 IF (l_debug = 1) THEN
1228 print_debug('update the mmtt with from cost group of null');
1229 print_debug('update the mmtt with tfr cost group of null');
1230 END IF;
1231 UPDATE mtl_material_transactions_temp
1232 SET cost_group_id = NULL,
1233 transfer_cost_group_id = null
1234 WHERE transaction_temp_id = l_line_id;
1235
1236 ELSIF p_input_type = G_INPUT_MOLINE THEN
1237 IF (l_debug = 1) THEN
1238 print_debug('update the mtl_txn_request_lines with from cost group null');
1239 print_debug('update the mtl_txn_request_lines with tfr cost group null');
1240 END IF;
1241
1242 UPDATE mtl_txn_request_lines
1243 SET to_cost_group_id = NULL,
1244 from_cost_group_id = null
1245 WHERE line_id = l_line_id;
1246 END IF;
1247
1248 ELSIF(l_from_project_id IS NOT NULL AND
1249 l_to_project_id IS NULL AND
1250 l_transaction_action_id IN (inv_globals.g_action_subxfr,
1251 inv_globals.g_action_stgxfr)) then
1252
1253 IF (l_debug = 1) THEN
1254 print_debug('Source locator is project enabled');
1255 print_debug('Dest locator is not project enabled');
1256 END IF;
1257
1258 IF (p_input_type = g_input_moline) THEN -- Input type is MMTT
1259 IF (l_debug = 1) THEN
1260 print_debug('before calling wms_costgroupengine_pvt for mtl_txn_request_lines record');
1261 END IF;
1262 wms_costgroupengine_pvt.assign_cost_group
1263 (p_api_version => 1.0,
1264 p_init_msg_list => fnd_api.g_false,
1265 p_commit => fnd_api.g_false,
1266 p_validation_level => fnd_api.g_valid_level_full,
1267 x_return_status => x_return_status,
1268 x_msg_count => x_msg_count,
1269 x_msg_data => x_msg_data,
1270 p_line_id => l_line_id,
1271 p_input_type => wms_costgroupengine_pvt.g_input_mtrl);
1272 IF (x_return_status = fnd_api.g_ret_sts_error) then
1273 IF (l_debug = 1) THEN
1274 print_debug('return error from wms_costgroupengine_pvt');
1275 END IF;
1276 RAISE fnd_api.g_exc_error;
1277 elsif( x_return_status = fnd_api.g_ret_sts_unexp_error) then
1278 IF (l_debug = 1) THEN
1279 print_debug('return unexpected error from wms_costgroupengine_pvt');
1280 END IF;
1281 RAISE fnd_api.g_exc_unexpected_error;
1282 end if;
1283
1284 IF (l_debug = 1) THEN
1285 print_debug('Setting from cost group as null ');
1286 END IF;
1287
1288 UPDATE mtl_txn_request_lines
1289 SET
1290 from_cost_group_id = null
1291 WHERE line_id = l_line_id;
1292
1293 ELSE -- Input type is MMTT
1294 IF (l_debug = 1) THEN
1295 print_debug('Calling Rules Engine : ');
1296 END IF;
1297 wms_costgroupengine_pvt.assign_cost_group
1298 (p_api_version => 1.0,
1299 p_init_msg_list => FND_API.G_FALSE,
1300 p_commit => FND_API.G_FALSE,
1301 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1302 x_return_status => x_return_Status,
1303 x_msg_count => x_msg_count,
1304 x_msg_data => x_msg_data,
1305 p_line_id => l_line_id,
1306 p_input_type => WMS_CostGroupEngine_PVT.G_INPUT_MMTT);
1307
1308 IF (x_return_status = fnd_api.g_ret_sts_error) THEN
1309 IF (l_debug = 1) THEN
1310 print_debug('return error from wms_costgroupengine_pvt');
1311 END IF;
1312 RAISE fnd_api.g_exc_error;
1313 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error) THEN
1314 IF (l_debug = 1) THEN
1315 print_debug('return unexpected error from wms_costgroupengine_pvt');
1316 END IF;
1317 RAISE fnd_api.g_exc_unexpected_error;
1318 END IF;
1319
1320 IF (l_debug = 1) THEN
1321 print_debug('Setting from cost group as null ');
1322 END IF;
1323
1324 UPDATE mtl_material_transactions_temp
1325 SET cost_group_id = NULL
1326 WHERE transaction_temp_id = l_line_id;
1327 end if; -- input type
1328
1329 ELSIF(l_from_project_id IS NOT NULL AND
1330 l_transaction_action_id IN (inv_globals.g_action_receipt,
1331 inv_globals.G_Action_IntransitReceipt,
1332 inv_globals.g_action_AssyComplete )) then
1333
1334 IF (l_debug = 1) THEN
1335 print_debug('Receipt or assy completion transaction');
1336 print_debug('rec locator is project enabled');
1337 END IF;
1338
1339 IF (p_input_type = g_input_moline) THEN -- Input type is MMTT
1340
1341 IF (l_debug = 1) THEN
1342 print_debug('Setting cost group as null ');
1343 END IF;
1344
1345 UPDATE mtl_txn_request_lines
1346 SET
1347 from_cost_group_id = null
1348 WHERE line_id = l_line_id;
1349
1350 ELSE -- Input type is MMTT
1351
1352 IF (l_debug = 1) THEN
1353 print_debug('Setting cost group as null ');
1354 END IF;
1355
1356 UPDATE mtl_material_transactions_temp
1357 SET cost_group_id = NULL
1358 WHERE transaction_temp_id = l_line_id;
1359 end if; -- input type
1360
1361 -- updating the transfer cost group to null for direct org transfers,
1362 -- if the destination is wms org an dest loc is proj enabled
1363 -- Added by cjandhya
1364 ELSIF l_transfer_wms_org_flag
1365 AND l_transaction_action_id = inv_globals.g_action_orgxfr
1366 AND l_to_project_id IS NOT NULL THEN
1367
1368 IF (l_debug = 1) THEN
1369 print_debug('Org transfer WMS to WMS org ..Dest is project locator');
1370 END IF;
1371 IF p_input_type = G_INPUT_MMTT THEN
1372 IF (l_debug = 1) THEN
1373 print_debug('update the mmtt with transfer cost group null');
1374 END IF;
1375 UPDATE mtl_material_transactions_temp
1376 SET transfer_cost_group_id = NULL
1377 WHERE transaction_temp_id = l_line_id;
1378 ELSIF p_input_type = G_INPUT_MOLINE THEN
1379 IF (l_debug = 1) THEN
1380 print_debug('update the mtl_txn_request_lines with transfer cost group null');
1381 END IF;
1382 UPDATE mtl_txn_request_lines
1383 SET to_cost_group_id = null
1384 WHERE line_id = l_line_id;
1385 END IF;
1386 -- Added by cjandhya
1387
1388 ELSE
1389 -- Direct org transfer WMS --> INV
1390 IF (NOT l_transfer_wms_org_flag)
1391 AND l_transaction_action_id = inv_globals.g_action_orgxfr THEN
1392 IF l_tfr_primary_cost_method = 1
1393 THEN -- costing method is standard)
1394 BEGIN
1395 SELECT default_cost_group_id
1396 INTO l_transfer_cost_group_id
1397 FROM mtl_secondary_inventories
1398 WHERE secondary_inventory_name = l_transfer_subinventory
1399 AND organization_id = l_transfer_organization_id
1400 AND default_cost_group_id IS NOT NULL;
1401 IF (l_debug = 1) THEN
1402 print_debug('l_cost_group of subinventory ' || l_transfer_subinventory ||
1403 ' is ' || l_transfer_subinventory);
1404 END IF;
1405 EXCEPTION
1406 WHEN no_data_found THEN
1407 l_transfer_cost_group_id := l_tfr_org_cost_group_id;
1408 IF (l_debug = 1) THEN
1409 print_debug('default cost group of org ' || l_transfer_organization_id ||
1410 ' is ' || l_transfer_cost_group_id);
1411 END IF;
1412 END;
1413 ELSE -- costing method is not standard)
1414 l_transfer_cost_group_id := l_tfr_org_cost_group_id;
1415 IF (l_debug = 1) THEN
1416 print_debug('non-standard org: default cost group of org ' || l_transfer_organization_id ||
1417 ' is ' || l_transfer_cost_group_id);
1418 END IF;
1419 END IF;
1420
1421 IF p_input_type = G_INPUT_MMTT THEN
1422 IF (l_debug = 1) THEN
1423 print_debug('update the mmtt with cost group');
1424 END IF;
1425 UPDATE mtl_material_transactions_temp
1426 SET transfer_cost_group_id = l_transfer_cost_group_id
1427 WHERE transaction_temp_id = l_line_id;
1428 ELSIF p_input_type = G_INPUT_MOLINE THEN
1429 IF (l_debug = 1) THEN
1430 print_debug('update the mtl_txn_request_lines with cost group ' ||
1431 l_cost_group_id || ' and ' || l_transfer_cost_group_id);
1432 END IF;
1433 UPDATE mtl_txn_request_lines
1434 SET to_cost_group_id = l_transfer_cost_group_id
1435 WHERE line_id = l_line_id;
1436 END IF;
1437 END IF;
1438
1439 IF (p_input_type = G_INPUT_MMTT) THEN
1440 IF (l_debug = 1) THEN
1441 print_debug('input type is mmtt record');
1442 END IF;
1443
1444
1445 -- All Issue transactions or transfer transactions or transactions
1446 -- whose status is yet to be determined.
1447 IF inv_globals.is_issue_xfr_transaction(l_transaction_action_id)
1448 OR (l_transaction_action_id IN (inv_globals.g_type_cycle_count_adj,
1449 inv_globals.g_type_physical_count_adj,
1450 inv_globals.g_action_deliveryadj))
1451 THEN
1452 IF l_cost_group_id IS NULL OR l_transfer_cost_group_id IS NULL
1453 THEN
1454 IF (l_debug = 1) THEN
1455 print_debug('calling inv_cost_group_update.cost_group_update');
1456 END IF;
1457 inv_cost_group_update.cost_group_update
1458 (p_transaction_rec => p_mmtt_rec,
1459 p_fob_point => l_fob_point,
1460 p_transfer_wms_org => l_transfer_wms_org_flag,
1461 p_tfr_primary_cost_method => l_tfr_primary_cost_method,
1462 p_tfr_org_cost_group_id => l_tfr_org_cost_group_id,
1463 p_from_project_id => l_from_project_id,
1464 p_to_project_id => l_to_project_id,
1465 x_return_status => x_return_status,
1466 x_msg_count => x_msg_count,
1467 x_msg_data => x_msg_data);
1468 if( x_return_status = INV_COST_GROUP_PVT.G_COMINGLE_ERROR ) then
1469 RAISE inv_comingle_error;
1470 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) then
1471 RAISE FND_API.G_EXC_ERROR;
1472 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
1473 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1474 END IF;
1475 END IF;
1476 -- Container transactions
1477 ELSIF (l_transaction_action_id = inv_globals.g_action_containerpack) OR
1478 (l_transaction_action_id = inv_globals.g_action_containerunpack) OR
1479 (l_transaction_action_id = inv_globals.g_action_containersplit) THEN
1480 IF (l_cost_group_id IS NULL AND l_transfer_cost_group_id IS NULL) THEN
1481 IF (l_debug = 1) THEN
1482 print_debug('calling inv_cost_group_update.cost_group_update');
1483 END IF;
1484 inv_cost_group_update.cost_group_update
1485 (p_transaction_rec => p_mmtt_rec,
1486 p_fob_point => l_fob_point,
1487 p_transfer_wms_org => l_transfer_wms_org_flag,
1488 p_tfr_primary_cost_method => l_tfr_primary_cost_method,
1489 p_tfr_org_cost_group_id => l_tfr_org_cost_group_id,
1490 p_from_project_id => l_from_project_id,
1491 p_to_project_id => l_to_project_id,
1492 x_return_status => x_return_status,
1493 x_msg_count => x_msg_count,
1494 x_msg_data => x_msg_data);
1495 if( x_return_status = INV_COST_GROUP_PVT.G_COMINGLE_ERROR ) then
1496 RAISE inv_comingle_error;
1497 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) then
1498 RAISE fnd_api.g_exc_error;
1499 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error) then
1500 RAISE fnd_api.g_exc_unexpected_error;
1501 END IF;
1502 END IF;
1503 ELSE
1504 -- Receipt transactions
1505 IF ((l_transaction_action_id NOT IN (inv_globals.g_action_intransitshipment,
1506 inv_globals.g_action_intransitreceipt))
1507 AND (l_cost_group_id is null AND l_transfer_cost_group_id IS NULL))
1508 OR (l_transaction_action_id = inv_globals.g_action_intransitreceipt AND
1509 l_cost_group_id IS NULL) THEN
1510
1511 IF l_transaction_action_id = inv_globals.g_action_intransitreceipt AND
1512 p_mmtt_rec.transfer_cost_group_id IS NULL THEN
1513 IF (l_debug = 1) THEN
1514 print_debug('update the mmtt with cost group');
1515 END IF;
1516 UPDATE mtl_material_transactions_temp
1517 SET transfer_cost_group_id = l_transfer_cost_group_id
1518 WHERE transaction_temp_id = l_line_id;
1519 END IF;
1520
1521 IF (l_debug = 1) THEN
1522 print_debug('Calling Rules Engine : ');
1523 END IF;
1524 wms_costgroupengine_pvt.assign_cost_group
1525 (p_api_version => 1.0,
1526 p_init_msg_list => FND_API.G_FALSE,
1527 p_commit => FND_API.G_FALSE,
1528 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1529 x_return_status => x_return_Status,
1530 x_msg_count => x_msg_count,
1531 x_msg_data => x_msg_data,
1532 p_line_id => l_line_id,
1533 p_input_type => WMS_CostGroupEngine_PVT.G_INPUT_MMTT);
1534
1535 IF (x_return_status = fnd_api.g_ret_sts_error) THEN
1536 IF (l_debug = 1) THEN
1537 print_debug('return error from wms_costgroupengine_pvt');
1538 END IF;
1539 RAISE fnd_api.g_exc_error;
1540 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error) THEN
1541 IF (l_debug = 1) THEN
1542 print_debug('return unexpected error from wms_costgroupengine_pvt');
1543 END IF;
1544 RAISE fnd_api.g_exc_unexpected_error;
1545 END IF;
1546 END IF;
1547 END IF;
1548 ELSIF (p_input_type = g_input_moline) THEN
1549 IF (l_debug = 1) THEN
1550 print_debug('before calling wms_costgroupengine_pvt for mtl_txn_request_lines record');
1551 END IF;
1552 wms_costgroupengine_pvt.assign_cost_group
1553 (
1554 p_api_version => 1.0,
1555 p_init_msg_list => fnd_api.g_false,
1556 p_commit => fnd_api.g_false,
1557 p_validation_level => fnd_api.g_valid_level_full,
1558 x_return_status => x_return_status,
1559 x_msg_count => x_msg_count,
1560 x_msg_data => x_msg_data,
1561 p_line_id => l_line_id,
1562 p_input_type => wms_costgroupengine_pvt.g_input_mtrl);
1563 IF (x_return_status = fnd_api.g_ret_sts_error) then
1564 IF (l_debug = 1) THEN
1565 print_debug('return error from wms_costgroupengine_pvt');
1566 END IF;
1567 RAISE fnd_api.g_exc_error;
1568 elsif( x_return_status = fnd_api.g_ret_sts_unexp_error) then
1569 IF (l_debug = 1) THEN
1570 print_debug('return unexpected error from wms_costgroupengine_pvt');
1571 END IF;
1572 RAISE fnd_api.g_exc_unexpected_error;
1573 end if;
1574
1575 x_cost_group_id := NULL;
1576 x_transfer_cost_group_id := NULL;
1577 end if; -- input type
1578 END IF;-- from_project or to_project is not null
1579 end if; -- wms org
1580
1581 print_debug('calling comingling for temp_id '||p_mmtt_rec.transaction_temp_id );
1582 IF (p_input_type = G_INPUT_MMTT) THEN
1583 inv_comingling_utils.comingle_check
1584 (x_return_status => x_return_status
1585 , x_msg_count => x_msg_count
1586 , x_msg_data => x_msg_data
1587 , x_comingling_occurs => l_comingling_occurs
1588 , p_transaction_temp_id => p_mmtt_rec.transaction_temp_id);
1589
1590 IF l_comingling_occurs = 'Y' THEN
1591 IF (l_debug = 1) THEN
1592 print_debug('assign_cost_group comingling occurs : ' );
1593 END IF;
1594 RAISE inv_comingle_error;
1595 END IF;
1596
1597 --Bug#6343400.Added code to call Calculate_transfer_cost
1598 IF (l_wms_org_flag) THEN
1599 IF (l_debug = 1) THEN
1600 print_debug('Calling Calculate_transfer_cost : ' );
1601 END IF;
1602
1603 Calculate_transfer_cost
1604 ( p_mmtt_temp_id => p_mmtt_rec.transaction_temp_id
1605 , x_return_status => x_return_status
1606 , x_msg_count => x_msg_count
1607 , x_msg_data => x_msg_data );
1608
1609 IF ( x_return_status <> FND_API.g_ret_sts_success ) THEN
1610 IF (l_debug = 1) THEN
1611 print_debug('Error while executing Calculate_transfer_cost : ' );
1612 END IF;
1613 RAISE FND_API.G_EXC_ERROR;
1614 END IF;
1615 END IF ; --End of Bug#6343400
1616 END IF;
1617
1618 EXCEPTION
1619 WHEN inv_comingle_error THEN
1620 x_return_status := FND_API.G_RET_STS_ERROR;
1621 ROLLBACK TO assign_cost_group;
1622 --Bug 5214602 : FP of 2879206
1623 g_failure_txn_temp_id := g_current_txn_temp_id;
1624 print_debug('Failed Txn Temp Id : ' || g_failure_txn_temp_id );
1625 --Commenting these because this message is getting added
1626 --in INVCOMUB.pls
1627 --fnd_message.set_name('INV', 'INV_COMINGLE_ERROR');
1628 --fnd_msg_pub.add;
1629 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
1630
1631 WHEN FND_API.G_EXC_ERROR THEN
1632 --Bug 5214602 : FP of 2879206
1633 g_failure_txn_temp_id := g_current_txn_temp_id;
1634 print_debug('Failed Txn Temp Id : ' || g_failure_txn_temp_id );
1635 x_return_status := FND_API.G_RET_STS_ERROR;
1636 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
1637
1638 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1639 --Bug 5214602 : FP of 2879206
1640 g_failure_txn_temp_id := g_current_txn_temp_id;
1641 print_debug('Failed Txn Temp Id : ' || g_failure_txn_temp_id );
1642 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1643 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
1644
1645 WHEN OTHERS THEN
1646 --Bug 5214602 : FP of 2879206
1647 g_failure_txn_temp_id := g_current_txn_temp_id;
1648 print_debug('Failed Txn Temp Id : ' || g_failure_txn_temp_id );
1649 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1650 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1651 FND_MSG_PUB.Add_Exc_Msg
1652 ( G_PKG_NAME, 'INV_COST_GROUP_PVT');
1653 end if;
1654 END;
1655
1656 PROCEDURE get_cost_group(x_cost_group_id OUT NOCOPY NUMBER,
1657 x_cost_group OUT NOCOPY VARCHAR2,
1658 x_return_status OUT NOCOPY VARCHAR2,
1659 x_msg_count OUT NOCOPY NUMBER,
1660 x_msg_data OUT NOCOPY VARCHAR2,
1661 p_organization_id IN NUMBER,
1662 p_lpn_id IN NUMBER,
1663 p_inventory_item_id IN NUMBER,
1664 p_revision IN VARCHAR2,
1665 p_subinventory_code IN VARCHAR2,
1666 p_locator_id IN NUMBER,
1667 p_lot_number IN VARCHAR2,
1668 p_serial_number IN VARCHAR2) IS
1669
1670 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1671 BEGIN
1672 IF p_lpn_id IS NULL THEN
1673 IF p_serial_number IS NULL THEN
1674 SELECT ccg.cost_group_id, ccg.cost_group
1675 INTO x_cost_group_id, x_cost_group
1676 FROM cst_cost_groups ccg, mtl_onhand_quantities_detail moq
1677 WHERE ccg.cost_group_id = moq.cost_group_id
1678 AND (moq.lot_number = p_lot_number
1679 OR (p_lot_number IS NULL AND moq.lot_number IS NULL))
1680 AND (moq.revision = p_revision
1681 OR (p_revision IS NULL AND moq.revision IS NULL))
1682 AND moq.inventory_item_id = p_inventory_item_id
1683 AND ( (p_locator_id IS NOT NULL AND moq.locator_id = p_locator_id)
1684 OR (p_locator_id IS NULL AND moq.locator_id IS NULL))
1685 AND moq.subinventory_code = p_subinventory_code
1686 AND moq.organization_id = p_organization_id
1687 /* Bug 4662985 -Modifying the condition to fetch records with containerized_flag as null also
1688 AND moq.containerized_flag = 2 -- Loose Items only */
1689 AND NVL(moq.containerized_flag, 2) = 2 -- Loose Items only
1690 /*End of fix for Bug 4662985 */
1691 GROUP BY ccg.cost_group_id, ccg.cost_group;
1692 ELSE
1693 SELECT ccg.cost_group_id, ccg.cost_group
1694 INTO x_cost_group_id, x_cost_group
1695 FROM cst_cost_groups ccg, mtl_serial_numbers msn
1696 WHERE ccg.cost_group_id = msn.cost_group_id
1697 AND (msn.lot_number = p_lot_number
1698 OR (p_lot_number IS NULL AND msn.lot_number IS NULL))
1699 AND (msn.revision = p_revision
1700 OR (p_revision IS NULL AND msn.revision IS NULL))
1701 AND msn.inventory_item_id = p_inventory_item_id
1702 AND ( (p_locator_id IS NOT NULL AND msn.current_locator_id = p_locator_id)
1703 OR (p_locator_id IS NULL AND msn.current_locator_id IS NULL))
1704 AND msn.current_subinventory_code = p_subinventory_code
1705 AND msn.current_organization_id = p_organization_id
1706 --Bug 4248777- Added the condition to check for the serial number also.
1707 AND msn.serial_number=p_serial_number
1708 AND msn.lpn_id is null --Added this to restrict the query.
1709 --End of fix for Bug 4248777
1710 GROUP BY ccg.cost_group_id, ccg.cost_group;
1711 END IF;
1712 ELSE
1713 IF p_serial_number IS NULL THEN
1714 SELECT ccg.cost_group_id, ccg.cost_group
1715 INTO x_cost_group_id, x_cost_group
1716 FROM cst_cost_groups ccg, wms_lpn_contents wlc,
1717 wms_license_plate_numbers wlpn
1718 WHERE ccg.cost_group_id = wlc.cost_group_id
1719 AND (wlc.lot_number = p_lot_number
1720 OR (p_lot_number IS NULL AND wlc.lot_number IS NULL))
1721 AND (wlc.revision = p_revision
1722 OR (p_revision IS NULL AND wlc.revision IS NULL))
1723 AND wlc.inventory_item_id = p_inventory_item_id
1724 AND wlc.parent_lpn_id = wlpn.lpn_id
1725 AND wlpn.locator_id = p_locator_id
1726 AND wlpn.subinventory_code = p_subinventory_code
1727 AND wlpn.organization_id = p_organization_id
1728 AND wlpn.lpn_id = p_lpn_id
1729 GROUP BY ccg.cost_group_id, ccg.cost_group;
1730 ELSE
1731 SELECT ccg.cost_group_id, ccg.cost_group
1732 INTO x_cost_group_id, x_cost_group
1733 FROM cst_cost_groups ccg, mtl_serial_numbers msn
1734 WHERE ccg.cost_group_id = msn.cost_group_id
1735 AND (msn.lot_number = p_lot_number
1736 OR (p_lot_number IS NULL AND msn.lot_number IS NULL))
1737 AND (msn.revision = p_revision
1738 OR (p_revision IS NULL AND msn.revision IS NULL))
1739 AND msn.lpn_id = p_lpn_id
1740 AND msn.inventory_item_id = p_inventory_item_id
1741 AND msn.current_locator_id = p_locator_id
1742 AND msn.current_subinventory_code = p_subinventory_code
1743 AND msn.current_organization_id = p_organization_id
1744 --Bug 4248777-Added the condition for the serial number also
1745 AND msn.serial_number = p_serial_number
1746 --End of fix for Bug 4248777
1747 GROUP BY ccg.cost_group_id, ccg.cost_group;
1748 END IF;
1749 END IF;
1750
1751 x_return_status := FND_API.g_ret_sts_success;
1752
1753 EXCEPTION
1754 WHEN too_many_rows THEN
1755 FND_MESSAGE.SET_NAME('WMS', 'WMS_COMMINGLE_EXISTS'); /*bug#2795266*/
1756 FND_MSG_PUB.ADD;
1757 x_return_status := FND_API.g_ret_sts_error;
1758 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1759 p_data => x_msg_data);
1760
1761 WHEN NO_DATA_FOUND THEN
1762 x_return_status := FND_API.g_ret_sts_success;
1763 /* BUG 2657862
1764 In this Procedure ,the costgroup is taken from the MOQD
1765 If there is no system quantity ,then this procedure will fails.
1766 So if no record is there ,we need to return sucess and transaction Manager
1767 has to stamp the exact costgroup.
1768 */
1769 x_cost_group_id :=NULL;
1770 x_cost_group :=NULL;
1771
1772 WHEN OTHERS THEN
1773 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1774 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1775 p_data => x_msg_data);
1776 END get_cost_group;
1777
1778
1779 END INV_COST_GROUP_PVT;
[Home] [Help]