1 PACKAGE BODY INV_COST_GROUP_PVT AS
2 /* $Header: INVVDCGB.pls 120.3.12020000.2 2013/02/28 02:49:06 jianpyu 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 l_project_id NUMBER; -- 16054379
408 BEGIN
409
410 --Bug 5214602 : FP of 2879206
411 g_current_txn_temp_id := p_mmtt_rec.transaction_temp_id;
412 /****************Code added*********************/
413
414 SAVEPOINT assign_cost_group;
415
416 IF (p_input_type = G_INPUT_MMTT) THEN
417
418 l_process_txn := 'Y';
419
420 IF (l_debug = 1) THEN
421 print_debug('transaction_header_id : ' || p_mmtt_rec.transaction_header_id);
422 print_debug('transaction_temp_id : ' || p_mmtt_rec.transaction_Temp_id);
423 print_debug('organization_id : ' || p_mmtt_rec.organization_id);
424 print_debug('transaction_action_id : ' || p_mmtt_rec.transaction_action_id);
425 END IF;
426
427 -- If the MMTT line is for a LPN transaction then (in which case
428 -- item_id = -1) we do not process those transactions. In some of
429 -- the transactions the item_id <> -1
430 -- even though they are LPN triggered transactions. We have to
431 -- account FOR them specifically
432 IF (p_mmtt_rec.inventory_item_id = -1) THEN
433
434 x_return_status := fnd_api.g_ret_sts_success;
435 RETURN;
436
437 -- Bug: 4959753: The WIP phantom items are not transactable items
438 -- but are still inserted into MMTT. No onhand or MMT is created for these items
439 -- and Hence no cost group processing is required for these items.
440 -- if the source type is 5 (WIP) and wip_supply_type is 6, we do not
441 -- process such record. Took this condition from INVTXGGB.pls
442 ELSIF ((p_mmtt_rec.TRANSACTION_SOURCE_TYPE_ID = 5) AND
443 (nvl(p_mmtt_rec.OPERATION_SEQ_NUM,1) < 0) AND (nvl(p_mmtt_rec.WIP_SUPPLY_TYPE,0) = 6)) THEN
444 IF (l_debug = 1) THEN
445 print_debug ('Phantom item, Hence skipping processing');
446 END IF;
447 x_return_status := fnd_api.g_ret_sts_success;
448 RETURN;
449
450 -- If the transaction is a logical receipt or a logical delivery
451 -- adjustment, we will not go through the cost group logic. We will
452 -- get the org's default cost group for both the organization
453 -- and the transafer organization.
454 -- IDS: lot serial support
455 ELSIF (p_mmtt_rec.transaction_source_type_id =
456 INV_Globals.G_SOURCETYPE_PURCHASEORDER AND
457 (p_mmtt_rec.transaction_action_id =
458 INV_Globals.G_ACTION_LOGICALRECEIPT OR
459 p_mmtt_rec.transaction_action_id =
460 INV_Globals.g_action_logicaldeladj
461 )
462 ) THEN
463
464 print_debug('Inside get cost group for logical transactions');
465 print_debug('Transaction Action' || p_mmtt_rec.transaction_action_id );
466
467 SELECT default_cost_group_id
468 INTO l_org_cost_group_id
469 FROM mtl_parameters
470 WHERE organization_id = p_mmtt_rec.organization_id;
471
472 print_debug('After selecting the default cost group');
473 print_debug('Cost Group ID' || l_org_cost_group_id);
474
475 IF (p_mmtt_rec.transfer_organization IS NOT NULL) THEN
476 print_debug('Transfer Org. is being populated');
477 SELECT default_cost_group_id
478 INTO l_tfr_org_cost_group_id
479 FROM mtl_parameters
480 WHERE organization_id = p_mmtt_rec.transfer_organization;
481 END IF;
482
483 print_debug('After selecting the default transfer cost group');
484 print_debug('Transfer cost group ID' || l_tfr_org_cost_group_id);
485 print_debug('Return Status before update' ||x_return_status );
486
487 UPDATE mtl_material_transactions_temp
488 SET cost_group_id = l_org_cost_group_id,
489 transfer_cost_group_id = l_tfr_org_cost_group_id
490 WHERE transaction_temp_id = p_mmtt_rec.transaction_temp_id;
491
492 print_debug('Return Status after update' ||x_return_status );
493
494 x_return_status := fnd_api.g_ret_sts_success;
495 RETURN;
496
497 ELSE
498 IF p_mmtt_rec.transaction_action_id IN (INV_Globals.G_Action_IntransitShipment,
499 INV_Globals.G_Action_IntransitReceipt ) THEN
500
501
502 IF l_fob_point IS NULL THEN
503
504 BEGIN
505 SELECT fob_point
506 INTO l_fob_point
507 FROM mtl_interorg_parameters
508 WHERE from_organization_id =
509 Decode(p_mmtt_rec.transaction_action_id,
510 inv_globals.g_action_intransitreceipt,
511 p_mmtt_rec.transfer_organization,
512 p_mmtt_rec.organization_id)
513 AND to_organization_id =
514 Decode(p_mmtt_rec.transaction_action_id,
515 inv_globals.g_action_intransitreceipt,
516 p_mmtt_rec.organization_id,
517 p_mmtt_rec.transfer_organization);
518
519 IF l_fob_point IS NULL THEN
520 IF (l_debug = 1) THEN
521 print_debug ('l_fob_point is null:INV_FOB_NOT_DEFINED');
522 END IF;
523 FND_MESSAGE.SET_NAME('INV', 'INV_FOB_NOT_DEFINED');
524 fnd_message.set_token('ENTITY1',p_mmtt_rec.organization_id );
525 FND_MSG_PUB.ADD;
526 RAISE FND_API.G_EXC_ERROR;
527 END IF;
528 EXCEPTION
529 WHEN NO_DATA_FOUND THEN
530 IF (l_debug = 1) THEN
531 print_debug ('no_data_found:INV_FOB_NOT_DEFINED');
532 END IF;
533 FND_MESSAGE.SET_NAME('INV', 'INV_FOB_NOT_DEFINED');
534 fnd_message.set_token('ENTITY1',p_mmtt_rec.organization_id );
535 FND_MSG_PUB.ADD;
536 RAISE FND_API.G_EXC_ERROR;
537 END;
538 END IF;-- l_fob_point is null
539 END IF;--actions
540
541 IF (l_debug = 1) THEN
542 print_debug('l_fob_point is ' || l_fob_point);
543 END IF;
544 -- l_fob_point = 1 (shipment) = 2 (Receipt)
545
546 IF (l_debug = 1) THEN
547 print_debug('p_mmtt_rec.cost_group_id : ' || p_mmtt_rec.cost_group_id);
548 print_debug('p_mmtt_rec.transfer_cost_group_id : ' || p_mmtt_rec.transfer_cost_group_id);
549 END IF;
550
551
552 IF l_process_txn = 'N' THEN
553 NULL;
554
555 ELSIF p_mmtt_rec.cost_group_id IS NOT NULL THEN
556
557 IF p_mmtt_rec.transaction_action_id IN (INV_Globals.G_Action_Subxfr,INV_Globals.g_action_planxfr,
558 INV_Globals.G_Action_Stgxfr,
559 INV_Globals.g_action_orgxfr,
560 INV_Globals.g_action_ownxfr) THEN
561 IF (p_mmtt_rec.cost_group_id IS NULL OR
562 p_mmtt_rec.transfer_cost_group_id IS NULL) THEN
563
564 l_process_txn := 'Y';
565
566 --Bug 2392914 fix
567 --Most probably we don't need the below code
568 ELSIF p_mmtt_rec.transaction_action_id = INV_Globals.g_action_stgxfr then
569
570 IF (p_mmtt_rec.transfer_to_location IS NOT NULL AND
571 p_mmtt_rec.transfer_to_location > 0 AND
572 p_mmtt_rec.transfer_organization IS NOT NULL AND
573 p_mmtt_rec.transfer_organization > 0) THEN
574
575 BEGIN
576 SELECT project_id INTO l_to_project_id
577 FROM mtl_item_locations
578 WHERE inventory_location_id = p_mmtt_rec.transfer_to_location
579 AND organization_id = p_mmtt_rec.transfer_organization;
580 EXCEPTION
581 WHEN OTHERS THEN
582 IF (l_debug = 1) THEN
583 print_debug('exception in getting to project: ' || Sqlerrm);
584 END IF;
585 RAISE fnd_api.g_exc_unexpected_error;
586 END;
587 ELSE
588 l_to_project_id := NULL;
589 END IF;
590
591 IF l_to_project_id IS NOT NULL THEN
592 IF (l_debug = 1) THEN
593 print_debug('to project is not null');
594 print_debug('setting l_process_txn to N');
595 END IF;
596 --p_mmtt_rec.transfer_cost_group_id := NULL;
597 --Commenting out the above line because we don't
598 --process this RECORD anymore l_process_txn := 'N'
599 --after updating the transfer_cost_group_id as null
600 l_process_txn := 'N';
601
602 BEGIN
603 UPDATE mtl_material_transactions_temp
604 SET transfer_cost_group_id = NULL
605 WHERE
606 transaction_temp_id = p_mmtt_rec.transaction_temp_id;
607 EXCEPTION
608 WHEN OTHERS THEN
609 IF (l_debug = 1) THEN
610 print_debug('exception updating the xfr cost group-null');
611 print_debug('Error :'||Sqlerrm);
612 END IF;
613 END;
614
615 IF (l_debug = 1) THEN
616 print_debug('Setting transfer cost group as null');
617 END IF;
618 END IF;
619
620 IF (l_debug = 1) THEN
621 print_debug('setting l_process_txn to N');
622 END IF;
623 l_process_txn := 'N';
624
625 --Bug 2392914 fix
626
627 ELSE
628 l_process_txn := 'N';
629
630 END IF;
631
632 ELSIF p_mmtt_rec.transaction_action_id = INV_Globals.G_Action_IntransitShipment THEN
633 IF l_fob_point = 1 THEN
634 IF (p_mmtt_rec.cost_group_id IS NULL OR
635 p_mmtt_rec.transfer_cost_group_id IS NULL) THEN
636 l_process_txn := 'Y';
637 ELSE
638 l_process_txn := 'N';
639 END IF;
640 ELSE
641 IF (p_mmtt_rec.cost_group_id IS NULL ) THEN
642 l_process_txn := 'Y';
643 ELSE
644 l_process_txn := 'N';
645 END IF;
646 END IF;
647 ELSIF p_mmtt_rec.transaction_action_id = INV_Globals.G_Action_IntransitReceipt THEN
648 IF l_fob_point = 1 THEN
649 IF (p_mmtt_rec.cost_group_id IS NULL OR
650 p_mmtt_rec.transfer_cost_group_id IS NULL) THEN
651 l_process_txn := 'Y';
652 ELSE
653 l_process_txn := 'N';
654 END IF;
655 ELSE
656 IF (p_mmtt_rec.cost_group_id IS NULL ) THEN
657 l_process_txn := 'Y';
658 ELSE
659 l_process_txn := 'N';
660 END IF;
661 END IF;
662 ELSE -- for all other transaction actions, transfer_cost_group_id will always be null
663 l_process_txn := 'N';
664 END IF;
665 END IF;
666 IF (l_debug = 1) THEN
667 print_debug('l_process_txn.: ' || l_process_txn);
668 END IF;
669
670 IF l_process_txn = 'N' THEN
671 inv_comingling_utils.comingle_check
672 (x_return_status => x_return_status
673 , x_msg_count => x_msg_count
674 , x_msg_data => x_msg_data
675 , x_comingling_occurs => l_comingling_occurs
676 , p_mmtt_rec => p_mmtt_rec);
677
678 IF x_return_status <> fnd_api.g_ret_sts_success THEN
679 RAISE fnd_api.g_exc_unexpected_error;
680 ELSIF l_comingling_occurs = 'Y' THEN
681 IF (l_debug = 1) THEN
682 print_debug('assign_cost_group comingling occurs : ' );
683 END IF;
684 RAISE inv_comingle_error;
685 END IF;
686 END IF;
687 END IF;
688
689 l_line_id := p_mmtt_rec.transaction_temp_id;
690 l_organization_id := p_mmtt_rec.organization_id;
691
692 END IF; -- IF (p_input_type = G_INPUT_MMTT) THEN
693 /****************Code added*********************/
694
695 x_return_status := fnd_api.g_ret_sts_success;
696
697 IF (l_debug = 1) THEN
698 print_debug('in inv_cost_group_pub.assign_cost_group');
699 print_debug('l_line_id : ' || l_line_id);
700 print_debug('l_organization_id : ' || l_organization_id);
701 print_debug('p_input_type : ' || p_input_type);
702 print_debug('l_fob_point : ' || l_fob_point);
703 END IF;
704
705 l_transfer_organization_id:= NULL;
706 l_transaction_action_id := NULL;
707 l_wms_org_flag := FALSE;
708 l_transfer_wms_org_flag := FALSE;
709 l_cost_Group_id := NULL;
710 l_org_cost_Group_id := NULL;
711 l_tfr_org_cost_Group_id := NULL;
712 l_transfer_cost_group_id := NULL;
713 l_subinventory_code := NULL;
714 l_transfer_subinventory := NULL;
715 l_primary_cost_method := NULL;
716 l_tfr_primary_cost_method := NULL;
717
718 l_wms_org_flag := wms_install.check_install
719 (x_return_status => x_return_status,
720 x_msg_count => x_msg_count,
721 x_msg_data => x_msg_data,
722 p_organization_id => l_organization_id);
723 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
724 RAISE FND_API.G_EXC_ERROR;
725 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
726 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
727 END IF;
728
729 IF (p_input_type = G_INPUT_MMTT) THEN
730 l_cost_group_id := p_mmtt_rec.cost_group_id;
731 l_transfer_cost_group_id := p_mmtt_rec.transfer_cost_Group_id;
732 l_subinventory_code := p_mmtt_rec.subinventory_code;
733 l_transfer_subinventory := p_mmtt_rec.transfer_subinventory;
734 l_transaction_action_id := p_mmtt_rec.transaction_action_id;
735 l_transfer_organization_id := p_mmtt_rec.transfer_organization;
736 IF p_mmtt_rec.transaction_action_id IN (INV_Globals.G_Action_Subxfr,INV_Globals.G_Action_Planxfr,
737 INV_Globals.g_action_stgxfr,INV_Globals.g_action_ownxfr) THEN
738 l_transfer_organization_id := p_mmtt_rec.organization_id;
739 END IF;
740
741 IF(p_mmtt_rec.locator_id IS NOT NULL) then
742 BEGIN
743 SELECT project_id INTO l_from_project_id
744 FROM mtl_item_locations
745 WHERE inventory_location_id = p_mmtt_rec.locator_id
746 AND organization_id = p_mmtt_rec.organization_id;
747 EXCEPTION
748 WHEN OTHERS THEN
749 IF (l_debug = 1) THEN
750 print_debug('exception in getting from project: ' || Sqlerrm);
751 END IF;
752 RAISE fnd_api.g_exc_unexpected_error;
753 END;
754 END IF;
755
756 IF(p_mmtt_rec.transfer_to_location IS NOT NULL) then
757
758 BEGIN
759 SELECT project_id INTO l_to_project_id
760 FROM mtl_item_locations
761 WHERE inventory_location_id = p_mmtt_rec.transfer_to_location
762 AND organization_id = l_transfer_organization_id;
763 EXCEPTION
764 WHEN OTHERS THEN
765 IF (l_debug = 1) THEN
766 print_debug('exception in getting to project: ' || Sqlerrm);
767 END IF;
768 RAISE fnd_api.g_exc_unexpected_error;
769 END;
770 END IF;
771
772 IF (l_debug = 1) THEN
773 print_debug('l_from_project_id : ' || l_from_project_id);
774 print_debug('l_to_project_id : ' || l_to_project_id);
775 END IF;
776
777
778 ELSIF (p_input_type = G_INPUT_MOLINE) THEN
779 SELECT from_cost_group_id,
780 to_cost_group_id,
781 from_subinventory_code,
782 to_subinventory_code,
783 from_locator_id,
784 to_locator_id
785 INTO l_cost_Group_id,
786 l_transfer_cost_group_id,
787 l_subinventory_code,
788 l_transfer_subinventory,
789 l_from_locator_id,
790 l_to_locator_id
791 FROM mtl_txn_request_lines
792 WHERE line_id = l_line_id;
793 END IF;
794
795 IF l_transfer_organization_id IS NOT NULL THEN
796 l_transfer_wms_org_flag := wms_install.check_install
797 (x_return_status => x_return_status,
798 x_msg_count => x_msg_count,
799 x_msg_data => x_msg_data,
800 p_organization_id => l_transfer_organization_id);
801 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
802 RAISE FND_API.G_EXC_ERROR;
803 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
804 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
805 END IF;
806
807 SELECT default_cost_group_id,
808 primary_cost_method
809 INTO l_tfr_org_cost_group_id,
810 l_tfr_primary_cost_method
811 FROM mtl_parameters
812 WHERE organization_id = l_transfer_organization_id;
813 IF( l_from_locator_id IS NOT NULL) then
814 BEGIN
815 SELECT project_id INTO l_from_project_id
816 FROM mtl_item_locations
817 WHERE inventory_location_id = l_from_locator_id AND
818 organization_id = l_organization_id;
819 EXCEPTION
820 WHEN OTHERS THEN
821 IF (l_debug = 1) THEN
822 print_debug('exception in getting from project: ' || Sqlerrm);
823 END IF;
824 RAISE fnd_api.g_exc_unexpected_error;
825 END;
826 END IF;
827
828 IF( l_to_locator_id IS NOT NULL) then
829 IF (l_debug = 1) THEN
830 print_debug(' l_transfer_organization_id '||l_transfer_organization_id);
831 END IF;
832 BEGIN
833 SELECT project_id INTO l_to_project_id
834 FROM mtl_item_locations
835 WHERE inventory_location_id = l_to_locator_id AND
836 organization_id = l_transfer_organization_id;
837 EXCEPTION
838 WHEN OTHERS THEN
839 IF (l_debug = 1) THEN
840 print_debug('exception in getting to project: ' || Sqlerrm);
841 END IF;
842 RAISE fnd_api.g_exc_unexpected_error;
843 END;
844 END IF;
845
846 IF (l_debug = 1) THEN
847 print_debug('l_from_project_id : ' || l_from_project_id);
848 print_debug('l_to_project_id : ' || l_to_project_id);
849 END IF;
850
851 END IF;
852 IF (l_debug = 1) THEN
853 print_debug('l_tfr_org_cost_group_id: ' || l_tfr_org_cost_group_id);
854 print_debug('l_tfr_primary_cost_method: ' || l_tfr_primary_cost_method);
855 print_debug('l_transfer_organization_id: ' || l_transfer_organization_id);
856 END IF;
857
858 IF (l_debug = 1) THEN
859 print_debug('l_cost_group_id : ' || l_cost_group_id);
860 print_debug('l_subinventory_code : ' || l_subinventory_code);
861 print_debug('l_transfer_cost_group_id : ' || l_transfer_cost_group_id);
862 print_debug('l_transfer_subinventory : ' || l_transfer_subinventory);
863 END IF;
864
865 SELECT default_cost_group_id,
866 primary_cost_method
867 INTO l_org_cost_group_id,
868 l_primary_cost_method
869 FROM mtl_parameters
870 WHERE organization_id = l_organization_id;
871
872 IF (l_debug = 1) THEN
873 print_debug('l_org_cost_group_id: ' || l_org_cost_group_id);
874 print_debug('l_primary_cost_method: ' || l_primary_cost_method);
875 print_debug('l_organization_id: ' || l_organization_id);
876 END IF;
877
878 IF l_transaction_action_id = inv_globals.g_action_intransitreceipt AND
879 p_mmtt_rec.transfer_cost_group_id IS NULL THEN
880 SELECT cost_group_id
881 INTO l_transfer_cost_group_id
882 FROM rcv_shipment_lines rsl,
883 rcv_transactions rt
884 WHERE rsl.shipment_line_id = rt.shipment_line_id
885 AND rt.transaction_id = p_mmtt_rec.source_line_id;
886 IF (l_debug = 1) THEN
887 print_debug('Intransit transfer cost group ID: ' || l_transfer_cost_group_id);
888 END IF;
889 END IF; -- action = intransit receipt
890
891 IF NOT l_wms_org_flag THEN
892 IF (l_debug = 1) THEN
893 print_debug('l_wms_flag is false ');
894 END IF;
895
896 -- derive cost group from the default cost group in the subinventory
897 IF (l_debug = 1) THEN
898 print_debug('l_transfer_cost_group_id: ' || l_transfer_cost_group_id);
899 END IF;
900 if (l_cost_Group_id is null and l_subinventory_code is not null) then
901 IF (l_debug = 1) THEN
902 print_debug('cost group is null , get the default from sub or org');
903 END IF;
904 IF l_primary_cost_method = 1
905 THEN -- costing method is standard)
906 BEGIN
907 SELECT default_cost_group_id
908 INTO l_cost_group_id
909 FROM mtl_secondary_inventories
910 WHERE secondary_inventory_name = l_subinventory_code
911 AND organization_id = l_organization_id
912 AND default_cost_group_id IS NOT NULL;
913 IF (l_debug = 1) THEN
914 print_debug('l_cost_group of subinventory ' || l_subinventory_code ||
915 ' is ' || l_cost_group_id);
916 END IF;
917 EXCEPTION
918 WHEN no_data_found THEN
919 l_cost_group_id := l_org_cost_group_id;
920 IF (l_debug = 1) THEN
921 print_debug('default cost group of org ' || l_organization_id ||
922 ' is ' || l_cost_group_id);
923 END IF;
924 END;
925 ELSE -- costing method is not standard)
926 l_cost_group_id := l_org_cost_group_id;
927 IF (l_debug = 1) THEN
928 print_debug('non-standard org: default cost group of org ' || l_organization_id ||
929 ' is ' || l_cost_group_id);
930 END IF;
931 END IF;
932 ELSE
933 IF (l_debug = 1) THEN
934 print_debug('l_cost_group_id is not null in mmtt');
935 END IF;
936 END IF;
937 IF (l_debug = 1) THEN
938 print_debug('l_cost_group_id : ' || l_cost_group_id);
939 print_debug('l_transfer_cost_group_id : ' || l_transfer_cost_group_id);
940 END IF;
941 END IF; -- end of not l_wms_org_flag
942
943
944 /* l_transfer_wms_org_flag will be false even when transfer org is null as in sub/stg tfr*/
945 IF (NOT l_wms_org_flag) AND (NOT l_transfer_wms_org_flag) THEN
946 IF (l_debug = 1) THEN
947 print_debug('transfer org is not wms enabled ');
948 END IF;
949
950 -- check if transaction is intransit issue
951 IF l_transaction_Action_id = inv_globals.g_action_intransitshipment THEN
952 IF l_fob_point = 1 THEN -- shipment
953 -- We do not care about the costing method of the org
954 l_transfer_cost_group_id := l_tfr_org_cost_group_id;
955 IF (l_debug = 1) THEN
956 print_debug('default cost group of org ' || p_mmtt_rec.transfer_organization ||
957 ' : ' || l_transfer_cost_group_id);
958 END IF;
959 ELSIF l_fob_point = 2 THEN -- receipt
960 l_transfer_cost_group_id := l_cost_group_id;
961 END IF;
962 END IF;
963
964 IF (l_transfer_cost_Group_id IS NULL AND l_transfer_subinventory IS
965 NOT NULL) AND l_transaction_action_id <> inv_globals.g_action_intransitreceipt THEN
966 IF (l_debug = 1) THEN
967 print_debug('transfer cost group is null , get the default from sub or org');
968 END IF;
969
970 IF l_tfr_primary_cost_method = 1
971 THEN -- costing method is standard)
972 BEGIN
973 select default_cost_group_id
974 into l_transfer_cost_group_id
975 from mtl_secondary_inventories
976 where secondary_inventory_name = l_transfer_subinventory
977 and organization_id = l_transfer_organization_id
978 and default_cost_group_id is not null;
979 IF (l_debug = 1) THEN
980 print_debug('l_transfer_cost_group of sub ' || l_transfer_subinventory ||
981 ' is ' || l_transfer_cost_group_id);
982 END IF;
983 EXCEPTION
984 WHEN no_data_found THEN
985 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
986 l_transfer_cost_group_id := l_tfr_org_cost_group_id;
987 IF (l_debug = 1) THEN
988 print_debug('default cost group of org ' || p_mmtt_rec.transfer_organization ||
989 ' is ' || l_transfer_cost_group_id);
990 END IF;
991 END;
992 ELSE -- costing method is not standard)
993 l_transfer_cost_group_id := l_tfr_org_cost_group_id;
994 IF (l_debug = 1) THEN
995 print_debug('default cost group of org ' || p_mmtt_rec.transfer_organization ||
996 ' : ' || l_transfer_cost_group_id);
997 END IF;
998 END IF;
999 ELSIF l_transfer_cost_Group_id is null AND
1000 l_transfer_subinventory is null AND
1001 l_transaction_Action_id = INV_Globals.G_Action_Orgxfr THEN
1002 /* case where trfr sub is null for a direct org transfer */
1003 l_transfer_cost_group_id := l_tfr_org_cost_group_id;
1004 else
1005 IF (l_debug = 1) THEN
1006 print_debug('l_transfer_cost_group is not null or tfr cost group is not to be populated ');
1007 END IF;
1008 end if;
1009
1010 IF (l_debug = 1) THEN
1011 print_debug('l_cost_group_id : ' || l_cost_group_id);
1012 print_debug('l_transfer_cost_group_id : ' || l_transfer_cost_group_id);
1013 END IF;
1014 IF p_input_type = g_input_mmtt then
1015 IF (l_debug = 1) THEN
1016 print_debug('update the mmtt with cost group');
1017 END IF;
1018 update mtl_material_transactions_temp
1019 set cost_Group_id = l_cost_group_id,
1020 transfer_cost_group_id = Nvl(transfer_cost_group_id, l_transfer_cost_group_id)
1021 where transaction_temp_id = l_line_id;
1022 ELSIF p_input_type = G_INPUT_MOLINE THEN
1023 IF (l_debug = 1) THEN
1024 print_debug('update the mtl_txn_request_lines with cost group ' ||
1025 l_cost_group_id || ' and ' || l_transfer_cost_group_id);
1026 END IF;
1027 update mtl_txn_request_lines
1028 set from_cost_Group_id = l_cost_group_id,
1029 to_cost_group_id = Nvl(to_cost_group_id, l_transfer_cost_group_id)
1030 where line_id = l_line_id;
1031 end if;
1032 END IF; -- end of not l_transfer_wms_org
1033
1034 -- Inventory to WMS transfers and WMS to INV transfers
1035 IF (NOT l_wms_org_flag) AND l_transfer_wms_org_flag THEN
1036 IF (l_debug = 1) THEN
1037 print_debug('INV to WMS transfer');
1038 END IF;
1039 -- check if transaction is intransit issue
1040 IF l_transaction_action_id IN (inv_globals.g_action_intransitshipment,
1041 inv_globals.g_action_orgxfr)
1042 THEN
1043 IF (l_fob_point = 1 AND l_transaction_action_id =
1044 inv_globals.g_action_intransitshipment) -- shipment
1045 OR (l_transaction_action_id = inv_globals.g_action_orgxfr) THEN
1046
1047 -- updating the transfer cost group to null for direct org transfers,
1048 -- if the destination is wms org and dest loc is proj enabled
1049 IF( l_to_project_id IS NOT NULL AND
1050 l_transaction_action_id = inv_globals.g_action_orgxfr) THEN
1051 IF (l_debug = 1) THEN
1052 print_debug('Org transfer to a WMS org ..Dest is project locator');
1053 END IF;
1054 IF p_input_type = G_INPUT_MMTT THEN
1055 IF (l_debug = 1) THEN
1056 print_debug('update the mmtt with transfer cost group null');
1057 END IF;
1058 UPDATE mtl_material_transactions_temp
1059 SET transfer_cost_group_id = NULL
1060 WHERE transaction_temp_id = l_line_id;
1061 ELSIF p_input_type = G_INPUT_MOLINE THEN
1062 IF (l_debug = 1) THEN
1063 print_debug('update the mtl_txn_request_lines with to cost group null');
1064 END IF;
1065 UPDATE mtl_txn_request_lines
1066 SET to_cost_group_id = null
1067 WHERE line_id = l_line_id;
1068 END IF;
1069 ELSE
1070 -- change till here
1071 IF (l_debug = 1) THEN
1072 print_debug('Calling the Rules Engine: ');
1073 END IF;
1074 wms_costgroupengine_pvt.assign_cost_group
1075 (p_api_version => 1.0,
1076 p_init_msg_list => fnd_api.g_false,
1077 p_commit => fnd_api.g_false,
1078 p_validation_level => fnd_api.g_valid_level_full,
1079 x_return_status => x_return_status,
1080 x_msg_count => x_msg_count,
1081 x_msg_data => x_msg_data,
1082 p_line_id => l_line_id,
1083 p_input_type => wms_costgroupengine_pvt.g_input_mmtt);
1084 IF (x_return_status = fnd_api.g_ret_sts_error) THEN
1085 IF (l_debug = 1) THEN
1086 print_debug('return error from wms_costgroupengine_pvt');
1087 END IF;
1088 RAISE fnd_api.g_exc_error;
1089 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error) THEN
1090 IF (l_debug = 1) THEN
1091 print_debug('return unexpected error from wms_costgroupengine_pvt');
1092 END IF;
1093 RAISE fnd_api.g_exc_unexpected_error;
1094 END IF;
1095
1096 IF p_input_type = G_INPUT_MMTT THEN
1097 IF (l_debug = 1) THEN
1098 print_debug('update the mmtt with cost group');
1099 END IF;
1100 UPDATE mtl_material_transactions_temp
1101 SET cost_group_id = l_cost_group_id
1102 WHERE transaction_temp_id = l_line_id;
1103 ELSIF p_input_type = G_INPUT_MOLINE THEN
1104 IF (l_debug = 1) THEN
1105 print_debug('update the mtl_txn_request_lines with cost group ' ||
1106 l_cost_group_id || ' and ' || l_transfer_cost_group_id);
1107 END IF;
1108 UPDATE mtl_txn_request_lines
1109 SET from_cost_group_id = l_cost_group_id
1110 WHERE line_id = l_line_id;
1111 END IF;
1112 END IF;-- for org transfer ,dest locator is project
1113
1114
1115 ELSIF (l_fob_point = 2 AND l_transaction_action_id =
1116 inv_globals.g_action_intransitshipment) THEN -- receipt
1117 IF (l_debug = 1) THEN
1118 print_debug('Setting transfer cost group = cost group');
1119 END IF;
1120 l_transfer_cost_group_id := l_cost_group_id;
1121 IF p_input_type = G_INPUT_MMTT THEN
1122 IF (l_debug = 1) THEN
1123 print_debug('update the mmtt with cost group');
1124 END IF;
1125 UPDATE mtl_material_transactions_temp
1126 SET cost_Group_id = l_cost_group_id,
1127 transfer_cost_group_id = l_transfer_cost_group_id
1128 WHERE transaction_temp_id = l_line_id;
1129 END IF;
1130 END IF;
1131 END IF;
1132
1133
1134
1135 IF l_transaction_action_id IN (inv_globals.g_action_intransitreceipt,
1136 inv_globals.g_action_orgxfr)
1137 THEN
1138 --Newly added code for PJM-WMS
1139 IF( l_to_project_id IS NOT NULL AND
1140 l_transaction_action_id = inv_globals.g_action_orgxfr) THEN
1141 IF (l_debug = 1) THEN
1142 print_debug('Org transfer to a WMS org ..Dest is project locator');
1143 END IF;
1144 --We don't want to fill the transfer cost group with l_transfer_cost_group_id
1145 --if the previous code has stamped it as null for the case of
1146 --org transfer to wms + dest locator is project enabled
1147 IF p_input_type = G_INPUT_MMTT THEN
1148 IF (l_debug = 1) THEN
1149 print_debug('update the mmtt with cost group '|| l_cost_group_id);
1150 END IF;
1151 UPDATE mtl_material_transactions_temp
1152 SET cost_group_id = l_cost_group_id
1153 --transfer_cost_group_id = Nvl(transfer_cost_group_id, l_transfer_cost_group_id)
1154 WHERE transaction_temp_id = l_line_id;
1155 ELSIF p_input_type = G_INPUT_MOLINE THEN
1156 IF (l_debug = 1) THEN
1157 print_debug('update the mtl_txn_request_lines with cost group ' || l_cost_group_id);
1158 END IF;
1159 UPDATE mtl_txn_request_lines
1160 SET from_cost_group_id = l_cost_group_id
1161 --to_cost_group_id = Nvl(to_cost_group_id, l_transfer_cost_group_id)
1162 WHERE line_id = l_line_id;
1163 END IF;
1164 ELSE
1165 --Newly added code for PJM-WMS
1166 IF (l_debug = 1) THEN
1167 print_debug('Receipt side of the interorg transfer...');
1168 END IF;
1169 IF p_input_type = G_INPUT_MMTT THEN
1170 IF (l_debug = 1) THEN
1171 print_debug('update the mmtt with cost group');
1172 END IF;
1173 UPDATE mtl_material_transactions_temp
1174 SET cost_group_id = l_cost_group_id,
1175 transfer_cost_group_id = Nvl(transfer_cost_group_id, l_transfer_cost_group_id)
1176 WHERE transaction_temp_id = l_line_id;
1177 ELSIF p_input_type = G_INPUT_MOLINE THEN
1178 IF (l_debug = 1) THEN
1179 print_debug('update the mtl_txn_request_lines with cost group ' ||
1180 l_cost_group_id || ' and ' || l_transfer_cost_group_id);
1181 END IF;
1182 UPDATE mtl_txn_request_lines
1183 SET from_cost_group_id = l_cost_group_id,
1184 to_cost_group_id = Nvl(to_cost_group_id, l_transfer_cost_group_id)
1185 WHERE line_id = l_line_id;
1186 END IF;
1187 END IF; --Newly added code for PJM-WMS
1188 END IF;
1189
1190
1191 END IF; -- INV --> WMS, WMS --> INV
1192
1193 IF (l_debug = 1) THEN
1194 print_debug('l_org_cost_group_id: ' || l_org_cost_group_id);
1195 print_debug('l_primary_cost_method: ' || l_primary_cost_method);
1196 print_debug('l_organization_id: ' || l_organization_id);
1197 print_debug('l_tfr_org_cost_group_id: ' || l_tfr_org_cost_group_id);
1198 print_debug('l_tfr_primary_cost_method: ' || l_tfr_primary_cost_method);
1199 print_debug('l_transfer_organization_id: ' || l_transfer_organization_id);
1200 END IF;
1201
1202 IF (l_debug = 1) THEN
1203 print_debug('l_cost_group_id : ' || l_cost_group_id);
1204 print_debug('l_subinventory_code : ' || l_subinventory_code);
1205 print_debug('l_transfer_cost_group_id : ' || l_transfer_cost_group_id);
1206 print_debug('l_transfer_subinventory : ' || l_transfer_subinventory);
1207 print_debug('l_transfer_organization : ' || l_transfer_organization_id);
1208 print_debug('l_transaction_action_id : ' || l_transaction_action_id);
1209 END IF;
1210
1211 IF (l_wms_org_flag) THEN
1212 IF (l_debug = 1) THEN
1213 print_Debug('l_wms_org_flag is true');
1214 END IF;
1215
1216 /*** WMS-PJM changes *********/
1217 IF(l_from_project_id IS NOT NULL AND
1218 l_to_project_id IS NOT NULL AND
1219 l_transaction_action_id IN (inv_globals.g_action_subxfr,
1220 inv_globals.g_action_stgxfr)) then
1221
1222 IF (l_debug = 1) THEN
1223 print_debug('Source and destination locators are not project enabled');
1224 print_debug('Stamping null cost groups for source and destination');
1225 END IF;
1226
1227 IF p_input_type = G_INPUT_MMTT THEN
1228 IF (l_debug = 1) THEN
1229 print_debug('update the mmtt with from cost group of null');
1230 print_debug('update the mmtt with tfr cost group of null');
1231 END IF;
1232 UPDATE mtl_material_transactions_temp
1233 SET cost_group_id = NULL,
1234 transfer_cost_group_id = null
1235 WHERE transaction_temp_id = l_line_id;
1236
1237 ELSIF p_input_type = G_INPUT_MOLINE THEN
1238 IF (l_debug = 1) THEN
1239 print_debug('update the mtl_txn_request_lines with from cost group null');
1240 print_debug('update the mtl_txn_request_lines with tfr cost group null');
1241 END IF;
1242
1243 UPDATE mtl_txn_request_lines
1244 SET to_cost_group_id = NULL,
1245 from_cost_group_id = null
1246 WHERE line_id = l_line_id;
1247 END IF;
1248
1249 ELSIF(l_from_project_id IS NOT NULL AND
1250 l_to_project_id IS NULL AND
1251 l_transaction_action_id IN (inv_globals.g_action_subxfr,
1252 inv_globals.g_action_stgxfr)) then
1253
1254 IF (l_debug = 1) THEN
1255 print_debug('Source locator is project enabled');
1256 print_debug('Dest locator is not project enabled');
1257 END IF;
1258
1259 IF (p_input_type = g_input_moline) THEN -- Input type is MMTT
1260 IF (l_debug = 1) THEN
1261 print_debug('before calling wms_costgroupengine_pvt for mtl_txn_request_lines record');
1262 END IF;
1263 wms_costgroupengine_pvt.assign_cost_group
1264 (p_api_version => 1.0,
1265 p_init_msg_list => fnd_api.g_false,
1266 p_commit => fnd_api.g_false,
1267 p_validation_level => fnd_api.g_valid_level_full,
1268 x_return_status => x_return_status,
1269 x_msg_count => x_msg_count,
1270 x_msg_data => x_msg_data,
1271 p_line_id => l_line_id,
1272 p_input_type => wms_costgroupengine_pvt.g_input_mtrl);
1273 IF (x_return_status = fnd_api.g_ret_sts_error) then
1274 IF (l_debug = 1) THEN
1275 print_debug('return error from wms_costgroupengine_pvt');
1276 END IF;
1277 RAISE fnd_api.g_exc_error;
1278 elsif( x_return_status = fnd_api.g_ret_sts_unexp_error) then
1279 IF (l_debug = 1) THEN
1280 print_debug('return unexpected error from wms_costgroupengine_pvt');
1281 END IF;
1282 RAISE fnd_api.g_exc_unexpected_error;
1283 end if;
1284
1285 IF (l_debug = 1) THEN
1286 print_debug('Setting from cost group as null ');
1287 END IF;
1288
1289 UPDATE mtl_txn_request_lines
1290 SET
1291 from_cost_group_id = null
1292 WHERE line_id = l_line_id;
1293
1294 ELSE -- Input type is MMTT
1295 IF (l_debug = 1) THEN
1296 print_debug('Calling Rules Engine : ');
1297 END IF;
1298 wms_costgroupengine_pvt.assign_cost_group
1299 (p_api_version => 1.0,
1300 p_init_msg_list => FND_API.G_FALSE,
1301 p_commit => FND_API.G_FALSE,
1302 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1303 x_return_status => x_return_Status,
1304 x_msg_count => x_msg_count,
1305 x_msg_data => x_msg_data,
1306 p_line_id => l_line_id,
1307 p_input_type => WMS_CostGroupEngine_PVT.G_INPUT_MMTT);
1308
1309 IF (x_return_status = fnd_api.g_ret_sts_error) THEN
1310 IF (l_debug = 1) THEN
1311 print_debug('return error from wms_costgroupengine_pvt');
1312 END IF;
1313 RAISE fnd_api.g_exc_error;
1314 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error) THEN
1315 IF (l_debug = 1) THEN
1316 print_debug('return unexpected error from wms_costgroupengine_pvt');
1317 END IF;
1318 RAISE fnd_api.g_exc_unexpected_error;
1319 END IF;
1320
1321 IF (l_debug = 1) THEN
1322 print_debug('Setting from cost group as null ');
1323 END IF;
1324
1325 UPDATE mtl_material_transactions_temp
1326 SET cost_group_id = NULL
1327 WHERE transaction_temp_id = l_line_id;
1328 end if; -- input type
1329
1330 ELSIF(l_from_project_id IS NOT NULL AND
1331 l_transaction_action_id IN (inv_globals.g_action_receipt,
1332 inv_globals.G_Action_IntransitReceipt,
1333 inv_globals.g_action_AssyComplete )) then
1334
1335 IF (l_debug = 1) THEN
1336 print_debug('Receipt or assy completion transaction');
1337 print_debug('rec locator is project enabled');
1338 END IF;
1339
1340 IF (p_input_type = g_input_moline) THEN -- Input type is MMTT
1341
1342 IF (l_debug = 1) THEN
1343 print_debug('Setting cost group as null ');
1344 END IF;
1345
1346 UPDATE mtl_txn_request_lines
1347 SET
1348 from_cost_group_id = null
1349 WHERE line_id = l_line_id;
1350
1351 ELSE -- Input type is MMTT
1352
1353 IF (l_debug = 1) THEN
1354 print_debug('Setting cost group as null ');
1355 END IF;
1356
1357 UPDATE mtl_material_transactions_temp
1358 SET cost_group_id = NULL
1359 WHERE transaction_temp_id = l_line_id;
1360
1361 end if; -- input type
1362
1363 -- updating the transfer cost group to null for direct org transfers,
1364 -- if the destination is wms org an dest loc is proj enabled
1365 -- Added by cjandhya
1366 ELSIF l_transfer_wms_org_flag
1367 AND l_transaction_action_id = inv_globals.g_action_orgxfr
1368 AND l_to_project_id IS NOT NULL THEN
1369
1370 IF (l_debug = 1) THEN
1371 print_debug('Org transfer WMS to WMS org ..Dest is project locator');
1372 END IF;
1373 IF p_input_type = G_INPUT_MMTT THEN
1374 IF (l_debug = 1) THEN
1375 print_debug('update the mmtt with transfer cost group null');
1376 END IF;
1377 UPDATE mtl_material_transactions_temp
1378 SET transfer_cost_group_id = NULL
1379 WHERE transaction_temp_id = l_line_id;
1380 ELSIF p_input_type = G_INPUT_MOLINE THEN
1381 IF (l_debug = 1) THEN
1382 print_debug('update the mtl_txn_request_lines with transfer cost group null');
1383 END IF;
1384 UPDATE mtl_txn_request_lines
1385 SET to_cost_group_id = null
1386 WHERE line_id = l_line_id;
1387 END IF;
1388 -- Added by cjandhya
1389 -- Start bug 16054379
1390 IF inv_cache.set_org_rec(p_organization_id) THEN
1391 IF (l_debug = 1) THEN
1392 print_debug('project_reference_enabled '||inv_cache.org_rec.project_reference_enabled);
1393 END IF;
1394 IF inv_cache.org_rec.project_reference_enabled =1 THEN
1395 IF (l_debug = 1) THEN
1396 print_debug('Start to calculte transfer cost group id ');
1397 END IF;
1398
1399 BEGIN
1400 SELECT mil.project_id INTO l_project_id
1401 FROM mtl_material_transactions mmt, rcv_shipment_lines rsl, rcv_transactions rt, MTL_ITEM_LOCATIONS mil
1402 WHERE p_mmtt_rec.source_line_id= rt.transaction_id
1403 AND rt.shipment_line_id=rsl.shipment_line_id
1404 AND rsl.mmt_transaction_id=mmt.transaction_id
1405 AND mil.inventory_location_id=mmt.locator_id
1406 AND mmt.organization_id=l_transfer_organization_id;
1407
1408 EXCEPTION
1409 WHEN OTHERS THEN
1410 IF (l_debug = 1) THEN
1411 print_debug('No relevant project for the source subinventory/locator.');
1412 l_project_id := NULL;
1413 END IF;
1414
1415 END;
1416 IF (l_debug = 1) THEN
1417 print_debug('l_project_id ='||l_project_id);
1418
1419 END IF;
1420
1421 IF l_project_id IS NOT NULL THEN
1422 IF (l_debug = 1) THEN
1423 print_debug('before calculate transfer cost group: l_transfer_cost_group_id ='||l_transfer_cost_group_id);
1424 END IF;
1425
1426 begin
1427 select Nvl(pjp.costing_group_id,l_transfer_cost_group_id) INTO l_transfer_cost_group_id
1428 FROM PJM_PROJECT_PARAMETERS pjp
1429 WHERE pjp.project_id=l_project_id
1430 AND pjp.organization_id=l_transfer_organization_id;
1431 exception
1432 when no_data_found then
1433 IF (l_debug = 1) THEN
1434 print_debug('Can not query costing_group_id base on the project Id');
1435 END IF;
1436 end;
1437
1438 IF (l_debug = 1) THEN
1439 print_debug('After calculate transfer cost group: l_transfer_cost_group_id of is ' ||l_transfer_cost_group_id);
1440 END IF;
1441 END IF;
1442
1443 UPDATE mtl_material_transactions_temp
1444 SET transfer_cost_group_id = l_transfer_cost_group_id
1445 WHERE transaction_temp_id = l_line_id;
1446
1447 IF (l_debug = 1) THEN
1448 print_debug('End to calculte transfer cost group id ');
1449 END IF;
1450 END IF;
1451 END IF;
1452 -- End bug 16054379
1453 ELSE
1454 -- Direct org transfer WMS --> INV
1455 IF (NOT l_transfer_wms_org_flag)
1456 AND l_transaction_action_id = inv_globals.g_action_orgxfr THEN
1457 IF l_tfr_primary_cost_method = 1
1458 THEN -- costing method is standard)
1459 BEGIN
1460 SELECT default_cost_group_id
1461 INTO l_transfer_cost_group_id
1462 FROM mtl_secondary_inventories
1463 WHERE secondary_inventory_name = l_transfer_subinventory
1464 AND organization_id = l_transfer_organization_id
1465 AND default_cost_group_id IS NOT NULL;
1466 IF (l_debug = 1) THEN
1467 print_debug('l_cost_group of subinventory ' || l_transfer_subinventory ||
1468 ' is ' || l_transfer_subinventory);
1469 END IF;
1470 EXCEPTION
1471 WHEN no_data_found THEN
1472 l_transfer_cost_group_id := l_tfr_org_cost_group_id;
1473 IF (l_debug = 1) THEN
1474 print_debug('default cost group of org ' || l_transfer_organization_id ||
1475 ' is ' || l_transfer_cost_group_id);
1476 END IF;
1477 END;
1478 ELSE -- costing method is not standard)
1479 l_transfer_cost_group_id := l_tfr_org_cost_group_id;
1480 IF (l_debug = 1) THEN
1481 print_debug('non-standard org: default cost group of org ' || l_transfer_organization_id ||
1482 ' is ' || l_transfer_cost_group_id);
1483 END IF;
1484 END IF;
1485
1486 IF p_input_type = G_INPUT_MMTT THEN
1487 IF (l_debug = 1) THEN
1488 print_debug('update the mmtt with cost group');
1489 END IF;
1490 UPDATE mtl_material_transactions_temp
1491 SET transfer_cost_group_id = l_transfer_cost_group_id
1492 WHERE transaction_temp_id = l_line_id;
1493 ELSIF p_input_type = G_INPUT_MOLINE THEN
1494 IF (l_debug = 1) THEN
1495 print_debug('update the mtl_txn_request_lines with cost group ' ||
1496 l_cost_group_id || ' and ' || l_transfer_cost_group_id);
1497 END IF;
1498 UPDATE mtl_txn_request_lines
1499 SET to_cost_group_id = l_transfer_cost_group_id
1500 WHERE line_id = l_line_id;
1501 END IF;
1502 END IF;
1503
1504 IF (p_input_type = G_INPUT_MMTT) THEN
1505 IF (l_debug = 1) THEN
1506 print_debug('input type is mmtt record');
1507 END IF;
1508
1509
1510 -- All Issue transactions or transfer transactions or transactions
1511 -- whose status is yet to be determined.
1512 IF inv_globals.is_issue_xfr_transaction(l_transaction_action_id)
1513 OR (l_transaction_action_id IN (inv_globals.g_type_cycle_count_adj,
1514 inv_globals.g_type_physical_count_adj,
1515 inv_globals.g_action_deliveryadj))
1516 THEN
1517 IF l_cost_group_id IS NULL OR l_transfer_cost_group_id IS NULL
1518 THEN
1519 IF (l_debug = 1) THEN
1520 print_debug('calling inv_cost_group_update.cost_group_update');
1521 END IF;
1522 inv_cost_group_update.cost_group_update
1523 (p_transaction_rec => p_mmtt_rec,
1524 p_fob_point => l_fob_point,
1525 p_transfer_wms_org => l_transfer_wms_org_flag,
1526 p_tfr_primary_cost_method => l_tfr_primary_cost_method,
1527 p_tfr_org_cost_group_id => l_tfr_org_cost_group_id,
1528 p_from_project_id => l_from_project_id,
1529 p_to_project_id => l_to_project_id,
1530 x_return_status => x_return_status,
1531 x_msg_count => x_msg_count,
1532 x_msg_data => x_msg_data);
1533 if( x_return_status = INV_COST_GROUP_PVT.G_COMINGLE_ERROR ) then
1534 RAISE inv_comingle_error;
1535 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) then
1536 RAISE FND_API.G_EXC_ERROR;
1537 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
1538 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1539 END IF;
1540 END IF;
1541 -- Container transactions
1542 ELSIF (l_transaction_action_id = inv_globals.g_action_containerpack) OR
1543 (l_transaction_action_id = inv_globals.g_action_containerunpack) OR
1544 (l_transaction_action_id = inv_globals.g_action_containersplit) THEN
1545 IF (l_cost_group_id IS NULL AND l_transfer_cost_group_id IS NULL) THEN
1546 IF (l_debug = 1) THEN
1547 print_debug('calling inv_cost_group_update.cost_group_update');
1548 END IF;
1549 inv_cost_group_update.cost_group_update
1550 (p_transaction_rec => p_mmtt_rec,
1551 p_fob_point => l_fob_point,
1552 p_transfer_wms_org => l_transfer_wms_org_flag,
1553 p_tfr_primary_cost_method => l_tfr_primary_cost_method,
1554 p_tfr_org_cost_group_id => l_tfr_org_cost_group_id,
1555 p_from_project_id => l_from_project_id,
1556 p_to_project_id => l_to_project_id,
1557 x_return_status => x_return_status,
1558 x_msg_count => x_msg_count,
1559 x_msg_data => x_msg_data);
1560 if( x_return_status = INV_COST_GROUP_PVT.G_COMINGLE_ERROR ) then
1561 RAISE inv_comingle_error;
1562 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) then
1563 RAISE fnd_api.g_exc_error;
1564 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error) then
1565 RAISE fnd_api.g_exc_unexpected_error;
1566 END IF;
1567 END IF;
1568 ELSE
1569 -- Receipt transactions
1570 IF ((l_transaction_action_id NOT IN (inv_globals.g_action_intransitshipment,
1571 inv_globals.g_action_intransitreceipt))
1572 AND (l_cost_group_id is null AND l_transfer_cost_group_id IS NULL))
1573 OR (l_transaction_action_id = inv_globals.g_action_intransitreceipt AND
1574 l_cost_group_id IS NULL) THEN
1575
1576 IF l_transaction_action_id = inv_globals.g_action_intransitreceipt AND
1577 p_mmtt_rec.transfer_cost_group_id IS NULL THEN
1578 IF (l_debug = 1) THEN
1579 print_debug('update the mmtt with cost group');
1580 END IF;
1581 UPDATE mtl_material_transactions_temp
1582 SET transfer_cost_group_id = l_transfer_cost_group_id
1583 WHERE transaction_temp_id = l_line_id;
1584 END IF;
1585
1586 IF (l_debug = 1) THEN
1587 print_debug('Calling Rules Engine : ');
1588 END IF;
1589 wms_costgroupengine_pvt.assign_cost_group
1590 (p_api_version => 1.0,
1591 p_init_msg_list => FND_API.G_FALSE,
1592 p_commit => FND_API.G_FALSE,
1593 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1594 x_return_status => x_return_Status,
1595 x_msg_count => x_msg_count,
1596 x_msg_data => x_msg_data,
1597 p_line_id => l_line_id,
1598 p_input_type => WMS_CostGroupEngine_PVT.G_INPUT_MMTT);
1599
1600 IF (x_return_status = fnd_api.g_ret_sts_error) THEN
1601 IF (l_debug = 1) THEN
1602 print_debug('return error from wms_costgroupengine_pvt');
1603 END IF;
1604 RAISE fnd_api.g_exc_error;
1605 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error) THEN
1606 IF (l_debug = 1) THEN
1607 print_debug('return unexpected error from wms_costgroupengine_pvt');
1608 END IF;
1609 RAISE fnd_api.g_exc_unexpected_error;
1610 END IF;
1611 END IF;
1612 END IF;
1613 ELSIF (p_input_type = g_input_moline) THEN
1614 IF (l_debug = 1) THEN
1615 print_debug('before calling wms_costgroupengine_pvt for mtl_txn_request_lines record');
1616 END IF;
1617 wms_costgroupengine_pvt.assign_cost_group
1618 (
1619 p_api_version => 1.0,
1620 p_init_msg_list => fnd_api.g_false,
1621 p_commit => fnd_api.g_false,
1622 p_validation_level => fnd_api.g_valid_level_full,
1623 x_return_status => x_return_status,
1624 x_msg_count => x_msg_count,
1625 x_msg_data => x_msg_data,
1626 p_line_id => l_line_id,
1627 p_input_type => wms_costgroupengine_pvt.g_input_mtrl);
1628 IF (x_return_status = fnd_api.g_ret_sts_error) then
1629 IF (l_debug = 1) THEN
1630 print_debug('return error from wms_costgroupengine_pvt');
1631 END IF;
1632 RAISE fnd_api.g_exc_error;
1633 elsif( x_return_status = fnd_api.g_ret_sts_unexp_error) then
1634 IF (l_debug = 1) THEN
1635 print_debug('return unexpected error from wms_costgroupengine_pvt');
1636 END IF;
1637 RAISE fnd_api.g_exc_unexpected_error;
1638 end if;
1639
1640 x_cost_group_id := NULL;
1641 x_transfer_cost_group_id := NULL;
1642 end if; -- input type
1643 END IF;-- from_project or to_project is not null
1644 end if; -- wms org
1645
1646 print_debug('calling comingling for temp_id '||p_mmtt_rec.transaction_temp_id );
1647 IF (p_input_type = G_INPUT_MMTT) THEN
1648 inv_comingling_utils.comingle_check
1649 (x_return_status => x_return_status
1650 , x_msg_count => x_msg_count
1651 , x_msg_data => x_msg_data
1652 , x_comingling_occurs => l_comingling_occurs
1653 , p_transaction_temp_id => p_mmtt_rec.transaction_temp_id);
1654
1655 IF l_comingling_occurs = 'Y' THEN
1656 IF (l_debug = 1) THEN
1657 print_debug('assign_cost_group comingling occurs : ' );
1658 END IF;
1659 RAISE inv_comingle_error;
1660 END IF;
1661
1662 --Bug#6343400.Added code to call Calculate_transfer_cost
1663 IF (l_wms_org_flag) THEN
1664 IF (l_debug = 1) THEN
1665 print_debug('Calling Calculate_transfer_cost : ' );
1666 END IF;
1667
1668 Calculate_transfer_cost
1669 ( p_mmtt_temp_id => p_mmtt_rec.transaction_temp_id
1670 , x_return_status => x_return_status
1671 , x_msg_count => x_msg_count
1672 , x_msg_data => x_msg_data );
1673
1674 IF ( x_return_status <> FND_API.g_ret_sts_success ) THEN
1675 IF (l_debug = 1) THEN
1676 print_debug('Error while executing Calculate_transfer_cost : ' );
1677 END IF;
1678 RAISE FND_API.G_EXC_ERROR;
1679 END IF;
1680 END IF ; --End of Bug#6343400
1681 END IF;
1682
1683 EXCEPTION
1684 WHEN inv_comingle_error THEN
1685 x_return_status := FND_API.G_RET_STS_ERROR;
1686 ROLLBACK TO assign_cost_group;
1687 --Bug 5214602 : FP of 2879206
1688 g_failure_txn_temp_id := g_current_txn_temp_id;
1689 print_debug('Failed Txn Temp Id : ' || g_failure_txn_temp_id );
1690 --Commenting these because this message is getting added
1691 --in INVCOMUB.pls
1692 --fnd_message.set_name('INV', 'INV_COMINGLE_ERROR');
1693 --fnd_msg_pub.add;
1694 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
1695
1696 WHEN FND_API.G_EXC_ERROR THEN
1697 --Bug 5214602 : FP of 2879206
1698 g_failure_txn_temp_id := g_current_txn_temp_id;
1699 print_debug('Failed Txn Temp Id : ' || g_failure_txn_temp_id );
1700 x_return_status := FND_API.G_RET_STS_ERROR;
1701 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
1702
1703 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1704 --Bug 5214602 : FP of 2879206
1705 g_failure_txn_temp_id := g_current_txn_temp_id;
1706 print_debug('Failed Txn Temp Id : ' || g_failure_txn_temp_id );
1707 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1708 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
1709
1710 WHEN OTHERS THEN
1711 --Bug 5214602 : FP of 2879206
1712 g_failure_txn_temp_id := g_current_txn_temp_id;
1713 print_debug('Failed Txn Temp Id : ' || g_failure_txn_temp_id );
1714 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1715 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1716 FND_MSG_PUB.Add_Exc_Msg
1717 ( G_PKG_NAME, 'INV_COST_GROUP_PVT');
1718 end if;
1719 END;
1720
1721 PROCEDURE get_cost_group(x_cost_group_id OUT NOCOPY NUMBER,
1722 x_cost_group OUT NOCOPY VARCHAR2,
1723 x_return_status OUT NOCOPY VARCHAR2,
1724 x_msg_count OUT NOCOPY NUMBER,
1725 x_msg_data OUT NOCOPY VARCHAR2,
1726 p_organization_id IN NUMBER,
1727 p_lpn_id IN NUMBER,
1728 p_inventory_item_id IN NUMBER,
1729 p_revision IN VARCHAR2,
1730 p_subinventory_code IN VARCHAR2,
1731 p_locator_id IN NUMBER,
1732 p_lot_number IN VARCHAR2,
1733 p_serial_number IN VARCHAR2) IS
1734
1735 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1736 BEGIN
1737 IF p_lpn_id IS NULL THEN
1738 IF p_serial_number IS NULL THEN
1739 SELECT ccg.cost_group_id, ccg.cost_group
1740 INTO x_cost_group_id, x_cost_group
1741 FROM cst_cost_groups ccg, mtl_onhand_quantities_detail moq
1742 WHERE ccg.cost_group_id = moq.cost_group_id
1743 AND (moq.lot_number = p_lot_number
1744 OR (p_lot_number IS NULL AND moq.lot_number IS NULL))
1745 AND (moq.revision = p_revision
1746 OR (p_revision IS NULL AND moq.revision IS NULL))
1747 AND moq.inventory_item_id = p_inventory_item_id
1748 AND ( (p_locator_id IS NOT NULL AND moq.locator_id = p_locator_id)
1749 OR (p_locator_id IS NULL AND moq.locator_id IS NULL))
1750 AND moq.subinventory_code = p_subinventory_code
1751 AND moq.organization_id = p_organization_id
1752 /* Bug 4662985 -Modifying the condition to fetch records with containerized_flag as null also
1753 AND moq.containerized_flag = 2 -- Loose Items only */
1754 AND NVL(moq.containerized_flag, 2) = 2 -- Loose Items only
1755 /*End of fix for Bug 4662985 */
1756 GROUP BY ccg.cost_group_id, ccg.cost_group;
1757 ELSE
1758 SELECT ccg.cost_group_id, ccg.cost_group
1759 INTO x_cost_group_id, x_cost_group
1760 FROM cst_cost_groups ccg, mtl_serial_numbers msn
1761 WHERE ccg.cost_group_id = msn.cost_group_id
1762 AND (msn.lot_number = p_lot_number
1763 OR (p_lot_number IS NULL AND msn.lot_number IS NULL))
1764 AND (msn.revision = p_revision
1765 OR (p_revision IS NULL AND msn.revision IS NULL))
1766 AND msn.inventory_item_id = p_inventory_item_id
1767 AND ( (p_locator_id IS NOT NULL AND msn.current_locator_id = p_locator_id)
1768 OR (p_locator_id IS NULL AND msn.current_locator_id IS NULL))
1769 AND msn.current_subinventory_code = p_subinventory_code
1770 AND msn.current_organization_id = p_organization_id
1771 --Bug 4248777- Added the condition to check for the serial number also.
1772 AND msn.serial_number=p_serial_number
1773 AND msn.lpn_id is null --Added this to restrict the query.
1774 --End of fix for Bug 4248777
1775 GROUP BY ccg.cost_group_id, ccg.cost_group;
1776 END IF;
1777 ELSE
1778 IF p_serial_number IS NULL THEN
1779 SELECT ccg.cost_group_id, ccg.cost_group
1780 INTO x_cost_group_id, x_cost_group
1781 FROM cst_cost_groups ccg, wms_lpn_contents wlc,
1782 wms_license_plate_numbers wlpn
1783 WHERE ccg.cost_group_id = wlc.cost_group_id
1784 AND (wlc.lot_number = p_lot_number
1785 OR (p_lot_number IS NULL AND wlc.lot_number IS NULL))
1786 AND (wlc.revision = p_revision
1787 OR (p_revision IS NULL AND wlc.revision IS NULL))
1788 AND wlc.inventory_item_id = p_inventory_item_id
1789 AND wlc.parent_lpn_id = wlpn.lpn_id
1790 AND wlpn.locator_id = p_locator_id
1791 AND wlpn.subinventory_code = p_subinventory_code
1792 AND wlpn.organization_id = p_organization_id
1793 AND wlpn.lpn_id = p_lpn_id
1794 GROUP BY ccg.cost_group_id, ccg.cost_group;
1795 ELSE
1796 SELECT ccg.cost_group_id, ccg.cost_group
1797 INTO x_cost_group_id, x_cost_group
1798 FROM cst_cost_groups ccg, mtl_serial_numbers msn
1799 WHERE ccg.cost_group_id = msn.cost_group_id
1800 AND (msn.lot_number = p_lot_number
1801 OR (p_lot_number IS NULL AND msn.lot_number IS NULL))
1802 AND (msn.revision = p_revision
1803 OR (p_revision IS NULL AND msn.revision IS NULL))
1804 AND msn.lpn_id = p_lpn_id
1805 AND msn.inventory_item_id = p_inventory_item_id
1806 AND msn.current_locator_id = p_locator_id
1807 AND msn.current_subinventory_code = p_subinventory_code
1808 AND msn.current_organization_id = p_organization_id
1809 --Bug 4248777-Added the condition for the serial number also
1810 AND msn.serial_number = p_serial_number
1811 --End of fix for Bug 4248777
1812 GROUP BY ccg.cost_group_id, ccg.cost_group;
1813 END IF;
1814 END IF;
1815
1816 x_return_status := FND_API.g_ret_sts_success;
1817
1818 EXCEPTION
1819 WHEN too_many_rows THEN
1820 FND_MESSAGE.SET_NAME('WMS', 'WMS_COMMINGLE_EXISTS'); /*bug#2795266*/
1821 FND_MSG_PUB.ADD;
1822 x_return_status := FND_API.g_ret_sts_error;
1823 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1824 p_data => x_msg_data);
1825
1826 WHEN NO_DATA_FOUND THEN
1827 x_return_status := FND_API.g_ret_sts_success;
1828 /* BUG 2657862
1829 In this Procedure ,the costgroup is taken from the MOQD
1830 If there is no system quantity ,then this procedure will fails.
1831 So if no record is there ,we need to return sucess and transaction Manager
1832 has to stamp the exact costgroup.
1833 */
1834 x_cost_group_id :=NULL;
1835 x_cost_group :=NULL;
1836
1837 WHEN OTHERS THEN
1838 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1839 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1840 p_data => x_msg_data);
1841 END get_cost_group;
1842
1843
1844 END INV_COST_GROUP_PVT;
[Home] [Help]