[Home] [Help]
PACKAGE BODY: APPS.CST_INVENTORY_PVT
Source
1 PACKAGE BODY CST_Inventory_PVT AS
2 /* $Header: CSTVIVTB.pls 120.20.12010000.3 2008/10/27 17:40:08 hyu ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CST_Inventory_PVT';
5
6 PROCEDURE log(
7 message IN VARCHAR2,
8 newline IN BOOLEAN DEFAULT TRUE) IS
9 BEGIN
10 IF message = 'NEWLINE' THEN
11 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
12 ELSIF (newline) THEN
13 FND_FILE.put_line(fnd_file.log,message);
14 ELSE
15 FND_FILE.put(fnd_file.log,message);
16 END IF;
17 END log;
18
19 PROCEDURE Populate_ItemList(
20 p_api_version IN NUMBER,
21 p_organization_id IN NUMBER,
22 p_cost_type_id IN NUMBER,
23 p_item_from IN VARCHAR2,
24 p_item_to IN VARCHAR2,
25 p_category_set_id IN NUMBER,
26 p_category_from IN VARCHAR2,
27 p_category_to IN VARCHAR2,
28 p_zero_cost_only IN NUMBER,
29 p_expense_item IN NUMBER,
30 p_cost_enabled_only IN NUMBER,
31 p_one_time_item IN NUMBER,
32 x_return_status OUT NOCOPY VARCHAR2
33 )
34 IS
35 l_api_name CONSTANT VARCHAR2(30) := 'Populate_ItemList';
36 l_api_version CONSTANT NUMBER := 1.0;
37 l_msg_level_threshold NUMBER;
38 l_stmt_num NUMBER := 0;
39 l_def_cost_type_id NUMBER;
40 l_cost_org_id NUMBER;
41 l_primary_cost_method NUMBER;
42 BEGIN
43 -- Standard Start of API savepoint
44 SAVEPOINT Populate_ItemList_PVT;
45
46 -- Check for call compatibility
47 IF NOT FND_API.Compatible_API_Call(
48 p_current_version_number => l_api_version,
49 p_caller_version_number => p_api_version,
50 p_api_name => l_api_name,
51 p_pkg_name => G_PKG_NAME
52 )
53 THEN
54 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
55 END IF;
56
57 -- Check for message level threshold
58 l_msg_level_threshold := FND_PROFILE.Value('FND_AS_MSG_LEVEL_THRESHOLD');
59
60 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH
61 THEN
62 FND_MSG_PUB.Add_Exc_Msg(
63 p_pkg_name => G_PKG_NAME,
64 p_procedure_name => l_api_name,
65 p_error_text => SUBSTR(
66 l_stmt_num||':'||
67 p_organization_id||','||
68 p_cost_type_id||','||
69 p_item_from||','||
70 p_item_to||','||
71 p_category_set_id||','||
72 p_category_from||','||
73 p_category_to||','||
74 p_zero_cost_only||','||
75 p_expense_item||','||
76 p_cost_enabled_only||','||
77 p_one_time_item,
78 1,
79 240
80 )
81 );
82 END IF;
83
84 -- Get the cost organization and the primary cost method
85 l_stmt_num := 10;
86 SELECT cost_organization_id,
87 primary_cost_method
88 INTO l_cost_org_id,
89 l_primary_cost_method
90 FROM mtl_parameters
91 WHERE organization_id = p_organization_id;
92
93 -- Get the default cost type
94 l_stmt_num := 15;
95 SELECT default_cost_type_id
96 INTO l_def_cost_type_id
97 FROM cst_cost_types
98 WHERE cost_type_id = NVL(p_cost_type_id, l_primary_cost_method);
99
100 -- Populate item list
101 -- Key flexfield are not compared at the segment level.
102 -- Doing so would require dynamic SQL, which is hard to tune and
103 -- maintain. Instead, concatenated segment level comparison is
104 -- used. This approach is commonly used across APPS.
105 IF (p_cost_enabled_only = 1) then
106 -- Populate items for Inventory reports
107 -- (All Inv Value, Inv Value, Elem Inv Value, etc.)
108 -- Only costing-enabled items are included.
109
110 l_stmt_num := 17;
111
112 /* Split the query into two for performance gain */
113 IF (p_category_from IS NULL AND p_category_to IS NULL ) THEN
114
115 --BUG#6740678
116 IF l_cost_org_id = p_organization_id AND
117 p_item_from IS NULL AND
118 p_item_to IS NULL AND
119 p_cost_type_id IS NULL AND
120 l_def_cost_type_id = l_primary_cost_method
121 THEN
122
123 INSERT INTO cst_item_list_temp(
124 inventory_item_id,
125 category_id,
126 cost_type_id
127 )
128 SELECT MSI.inventory_item_id,
129 MIC.category_id,
130 CIC.cost_type_id
131 FROM mtl_item_categories MIC,
132 mtl_system_items_kfv MSI,
133 cst_item_costs CIC
134 -- cst_item_costs CIC1,
135 -- cst_item_costs CIC2
136 WHERE MIC.category_set_id = p_category_set_id
137 AND MIC.organization_id = p_organization_id
138 AND MSI.organization_id = p_organization_id
139 AND MSI.inventory_item_id = MIC.inventory_item_id
140 --{
141 -- AND MSI.concatenated_segments
142 -- BETWEEN NVL(p_item_from,MSI.concatenated_segments)
143 -- AND NVL(p_item_to,MSI.concatenated_segments)
144 -- AND CIC1.organization_id (+) = l_cost_org_id
145 -- AND CIC2.organization_id (+) = l_cost_org_id
146 -- AND CIC1.inventory_item_id (+) = MSI.inventory_item_id
147 -- AND CIC2.inventory_item_id (+) = MSI.inventory_item_id
148 -- AND CIC1.cost_type_id (+) = NVL(p_cost_type_id,l_primary_cost_method)
149 -- AND CIC2.cost_type_id (+) = l_def_cost_type_id
150 -- AND CIC.rowid = NVL(CIC1.rowid,CIC2.rowid)
151 AND CIC.organization_id = p_organization_id
152 AND CIC.inventory_item_id = MSI.inventory_item_id
153 AND CIC.cost_type_id = l_primary_cost_method
154 --}
155 AND CIC.inventory_asset_flag =
156 DECODE(p_expense_item,1,CIC.inventory_asset_flag,1)
157 AND NVL(CIC.item_cost,0) = DECODE(p_zero_cost_only,
158 1,0,
159 NVL(CIC.item_cost,0)
160 );
161 ELSE
162 --}
163 log( 'Herve s call2') ;
164
165 INSERT INTO cst_item_list_temp(
166 inventory_item_id,
167 category_id,
168 cost_type_id
169 )
170 SELECT MSI.inventory_item_id,
171 MIC.category_id,
172 CIC.cost_type_id
173 FROM mtl_item_categories MIC,
174 mtl_system_items_kfv MSI,
175 cst_item_costs CIC,
176 cst_item_costs CIC1,
177 cst_item_costs CIC2
178 WHERE MIC.category_set_id = p_category_set_id
179 AND MIC.organization_id = p_organization_id
180 AND MSI.organization_id = p_organization_id
181 AND MSI.inventory_item_id = MIC.inventory_item_id
182 AND MSI.concatenated_segments
183 BETWEEN NVL(p_item_from,MSI.concatenated_segments)
184 AND NVL(p_item_to,MSI.concatenated_segments)
185 AND CIC1.organization_id (+) = l_cost_org_id
186 AND CIC2.organization_id (+) = l_cost_org_id
187 AND CIC1.inventory_item_id (+) = MSI.inventory_item_id
188 AND CIC2.inventory_item_id (+) = MSI.inventory_item_id
189 AND CIC1.cost_type_id (+) = NVL(p_cost_type_id,l_primary_cost_method)
190 AND CIC2.cost_type_id (+) = l_def_cost_type_id
191 AND CIC.rowid = NVL(CIC1.rowid,CIC2.rowid)
192 AND CIC.inventory_asset_flag =
193 DECODE(p_expense_item,1,CIC.inventory_asset_flag,1)
194 AND NVL(CIC.item_cost,0) = DECODE(p_zero_cost_only,
195 1,0,
196 NVL(CIC.item_cost,0)
197 );
198 END IF;
199 --}
200 ELSE
201
202 l_stmt_num := 20;
203
204 --BUG#6740678
205 IF l_cost_org_id = p_organization_id AND
206 p_cost_type_id IS NULL AND
207 l_def_cost_type_id = l_primary_cost_method
208 THEN
209
210 log( 'Herve s call3') ;
211
212
213 INSERT
214 INTO cst_item_list_temp(
215 inventory_item_id,
216 category_id,
217 cost_type_id
218 )
219 SELECT MSI.inventory_item_id,
220 MIC.category_id,
221 CIC.cost_type_id
222 FROM mtl_item_categories MIC,
223 mtl_categories_kfv MC,
224 mtl_system_items_kfv MSI,
225 cst_item_costs CIC
226 -- cst_item_costs CIC1,
227 -- cst_item_costs CIC2
228 WHERE MC.concatenated_segments
229 BETWEEN NVL(p_category_from,MC.concatenated_segments)
230 AND NVL(p_category_to,MC.concatenated_segments)
231 AND MC.structure_id = (SELECT structure_id FROM mtl_category_sets WHERE category_set_id = p_category_set_id)
232 AND MIC.category_id = MC.category_id
233 AND MIC.category_set_id = p_category_set_id
234 AND MIC.organization_id = p_organization_id
235 AND MSI.organization_id = p_organization_id
236 AND MSI.inventory_item_id = MIC.inventory_item_id
237 AND MSI.concatenated_segments
238 BETWEEN NVL(p_item_from,MSI.concatenated_segments)
239 AND NVL(p_item_to,MSI.concatenated_segments)
240 -- The join to CIC implies that the item is
241 -- MSI.costing_enabled
242 --{
243 -- AND CIC1.organization_id (+) = l_cost_org_id
244 -- AND CIC2.organization_id (+) = l_cost_org_id
245 -- AND CIC1.inventory_item_id (+) = MSI.inventory_item_id
246 -- AND CIC2.inventory_item_id (+) = MSI.inventory_item_id
247 -- AND CIC1.cost_type_id (+) = NVL(p_cost_type_id,l_primary_cost_method)
248 -- AND CIC2.cost_type_id (+) = l_def_cost_type_id
249 -- AND CIC.rowid = NVL(CIC1.rowid,CIC2.rowid)
250 AND CIC.organization_id = p_organization_id
251 AND CIC.inventory_item_id = MSI.inventory_item_id
252 AND CIC.cost_type_id = l_primary_cost_method
253 --}
254 AND CIC.inventory_asset_flag =
255 DECODE(p_expense_item,1,CIC.inventory_asset_flag,1)
256 AND NVL(CIC.item_cost,0) = DECODE(p_zero_cost_only,
257 1,0,
258 NVL(CIC.item_cost,0)
259 );
260
261
262 ELSE
263
264 INSERT
265 INTO cst_item_list_temp(
266 inventory_item_id,
267 category_id,
268 cost_type_id
269 )
270 SELECT MSI.inventory_item_id,
271 MIC.category_id,
272 CIC.cost_type_id
273 FROM mtl_item_categories MIC,
274 mtl_categories_kfv MC,
275 mtl_system_items_kfv MSI,
276 cst_item_costs CIC,
277 cst_item_costs CIC1,
278 cst_item_costs CIC2
279 WHERE MC.concatenated_segments
280 BETWEEN NVL(p_category_from,MC.concatenated_segments)
281 AND NVL(p_category_to,MC.concatenated_segments)
282 AND MC.structure_id = (SELECT structure_id FROM mtl_category_sets WHERE category_set_id = p_category_set_id)
283 AND MIC.category_id = MC.category_id
284 AND MIC.category_set_id = p_category_set_id
285 AND MIC.organization_id = p_organization_id
286 AND MSI.organization_id = p_organization_id
287 AND MSI.inventory_item_id = MIC.inventory_item_id
288 AND MSI.concatenated_segments
289 BETWEEN NVL(p_item_from,MSI.concatenated_segments)
290 AND NVL(p_item_to,MSI.concatenated_segments)
291 -- The join to CIC implies that the item is
292 -- MSI.costing_enabled
293 AND CIC1.organization_id (+) = l_cost_org_id
294 AND CIC2.organization_id (+) = l_cost_org_id
295 AND CIC1.inventory_item_id (+) = MSI.inventory_item_id
296 AND CIC2.inventory_item_id (+) = MSI.inventory_item_id
297 AND CIC1.cost_type_id (+) = NVL(p_cost_type_id,l_primary_cost_method)
298 AND CIC2.cost_type_id (+) = l_def_cost_type_id
299 AND CIC.rowid = NVL(CIC1.rowid,CIC2.rowid)
300 AND CIC.inventory_asset_flag =
301 DECODE(p_expense_item,1,CIC.inventory_asset_flag,1)
302 AND NVL(CIC.item_cost,0) = DECODE(p_zero_cost_only,
303 1,0,
304 NVL(CIC.item_cost,0)
305 );
306 END IF;
307
308 END IF;
309
310
311 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
315 p_procedure_name => l_api_name,
312 THEN
313 FND_MSG_PUB.Add_Exc_Msg(
314 p_pkg_name => G_PKG_NAME,
316 p_error_text => l_stmt_num||': Inserted '||SQL%ROWCOUNT||
317 ' items into CILT'||
318 ' - include costing_enabled items only'
319 );
320 END IF;
321
322 ELSE -- p_cost_enabled <> 1
323 -- Receiving reports do not require joining to CIC
324 -- because non-costing-enabled items are not excluded from the report.
325 -- The p_zero_cost_only and p_expense_item parameters are also ignored.
326 l_stmt_num := 30;
327 INSERT
328 INTO cst_item_list_temp(
329 inventory_item_id,
330 category_id,
331 cost_type_id
332 )
333 SELECT MSI.inventory_item_id,
334 MIC.category_id,
335 l_def_cost_type_id
336 FROM mtl_item_categories MIC,
337 mtl_categories_kfv MC,
338 mtl_system_items_kfv MSI
339 WHERE MC.concatenated_segments
340 BETWEEN NVL(p_category_from,MC.concatenated_segments)
341 AND NVL(p_category_to,MC.concatenated_segments)
342 AND MC.structure_id = (SELECT structure_id FROM mtl_category_sets WHERE category_set_id = p_category_set_id)
343 AND MIC.category_id = MC.category_id
344 AND MIC.category_set_id = p_category_set_id
345 AND MIC.organization_id = p_organization_id
346 AND MSI.organization_id = p_organization_id
347 AND MSI.inventory_item_id = MIC.inventory_item_id
348 AND MSI.concatenated_segments
349 BETWEEN NVL(p_item_from,MSI.concatenated_segments)
350 AND NVL(p_item_to,MSI.concatenated_segments);
351
352 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
353 THEN
354 FND_MSG_PUB.Add_Exc_Msg(
355 p_pkg_name => G_PKG_NAME,
356 p_procedure_name => l_api_name,
357 p_error_text => l_stmt_num||': Inserted '||SQL%ROWCOUNT||
358 ' items into CILT'||
359 ' - include non-costing-enabled items'
360 );
361 END IF;
362
363 IF p_one_time_item = 1 THEN
364 INSERT
365 INTO cst_item_list_temp(
366 inventory_item_id,
367 category_id,
368 cost_type_id
369 )
370 VALUES
371 (
372 NULL,
373 NULL,
374 l_def_cost_type_id
375 );
376
377 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
378 THEN
379 FND_MSG_PUB.Add_Exc_Msg(
380 p_pkg_name => G_PKG_NAME,
381 p_procedure_name => l_api_name,
382 p_error_text => l_stmt_num||': Inserted '||SQL%ROWCOUNT||
383 ' items into CILT'||
384 ' - for one-time items'
385 );
386 END IF;
387 END IF;
388
389 END IF; -- end if p_cost_enabled_only = 1
390
391 x_return_status := FND_API.G_RET_STS_SUCCESS;
392
393 EXCEPTION
394 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
395 ROLLBACK TO Populate_ItemList_PVT;
396 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
397 WHEN OTHERS THEN
398 ROLLBACK TO Populate_ItemList_PVT;
399 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
400 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
401 THEN
402 FND_MSG_PUB.Add_Exc_Msg(
403 p_pkg_name => G_PKG_NAME,
404 p_procedure_name => l_api_name,
405 p_error_text => SUBSTR(l_stmt_num||SQLERRM,1,240)
406 );
407 END IF;
408 END Populate_ItemList;
409
410 PROCEDURE Populate_CostGroupList(
411 p_api_version IN NUMBER,
412 p_organization_id IN NUMBER,
413 p_cost_group_from IN VARCHAR2,
414 p_cost_group_to IN VARCHAR2,
415 p_own IN NUMBER,
416 x_return_status OUT NOCOPY VARCHAR2
417 )
418 IS
419 l_api_name CONSTANT VARCHAR2(30) := 'Populate_CostGroupList';
420 l_api_version CONSTANT NUMBER := 1.0;
421 l_msg_level_threshold NUMBER;
422 l_stmt_num NUMBER := 0;
423 BEGIN
424 -- Standard Start of API savepoint
425 SAVEPOINT Populate_CostGroupList_PVT;
426
427 -- Check for call compatibility
428 IF NOT FND_API.Compatible_API_Call(
429 p_current_version_number => l_api_version,
430 p_caller_version_number => p_api_version,
431 p_api_name => l_api_name,
432 p_pkg_name => G_PKG_NAME
433 )
434 THEN
435 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
436 END IF;
437
438 -- Check for message level threshold
439 l_msg_level_threshold := FND_PROFILE.Value('FND_AS_MSG_LEVEL_THRESHOLD');
440
441 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH
442 THEN
443 FND_MSG_PUB.Add_Exc_Msg(
444 p_pkg_name => G_PKG_NAME,
445 p_procedure_name => l_api_name,
446 p_error_text => SUBSTR(
447 l_stmt_num||':'||
448 p_organization_id||','||
452 1,
449 p_cost_group_from||','||
450 p_cost_group_to||','||
451 p_own,
453 240
454 )
455 );
456 END IF;
457
458 -- Populate cost group list for the current organization
459 l_stmt_num := 10;
460 INSERT
461 INTO cst_cg_list_temp(
462 cost_group_id
463 )
464 SELECT CCG.cost_group_id
465 FROM cst_cost_groups CCG,
466 (SELECT organization_id,
467 cost_group_id
468 FROM cst_cost_group_accounts
469 UNION
470 /* This is required for cases where default
471 cost group id is 1 and it doesn't exist
472 in cst_cost_group_accounts */
473 SELECT organization_id,
474 default_cost_group_id cost_group_id
475 FROM mtl_parameters
476 ) CCGA
477 WHERE CCGA.organization_id = p_organization_id
478 AND CCG.cost_group_id = CCGA.cost_group_id
479 AND NVL(CCG.disable_date, sysdate+1) > sysdate
480 AND CCG.cost_group
481 BETWEEN NVL(p_cost_group_from, CCG.cost_group)
482 AND NVL(p_cost_group_to, CCG.cost_group)
483 AND NOT EXISTS( SELECT 'Cost Group already exists'
484 FROM cst_cg_list_temp CGLT
485 where CGLT.cost_group_id = CCG.cost_group_id
486 );
487
488
489 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
490 THEN
491 FND_MSG_PUB.Add_Exc_Msg(
492 p_pkg_name => G_PKG_NAME,
493 p_procedure_name => l_api_name,
494 p_error_text => l_stmt_num||': Inserted '||SQL%ROWCOUNT||
495 ' cost groups from organization '||p_organization_id||
496 ' into CCLT'
497 );
498 END IF;
499
500 -- Populate cost group list for the transfer organizations
501 IF NVL(p_own, -1) <> 1
502 THEN
503 l_stmt_num := 20;
504 INSERT
505 INTO cst_cg_list_temp(
506 cost_group_id
507 )
508 (SELECT DISTINCT
509 CCG.cost_group_id
510 FROM cst_cost_groups CCG,
511 (SELECT organization_id,
512 cost_group_id
513 FROM cst_cost_group_accounts
514 UNION
515 /* This is required for cases where default
516 cost group id is 1 and it doesn't exist
517 in cst_cost_group_accounts */
518 SELECT organization_id,
519 default_cost_group_id cost_group_id
520 FROM mtl_parameters
521 ) CCGA,
522 mtl_interorg_parameters MIP
523 WHERE CCG.cost_group_id = CCGA.cost_group_id
524 AND NVL(CCG.disable_date, sysdate+1) > sysdate
525 AND CCG.cost_group
526 BETWEEN NVL(p_cost_group_from, CCG.cost_group)
527 AND NVL(p_cost_group_to, CCG.cost_group)
528 AND ( ( MIP.from_organization_id = p_organization_id
529 AND MIP.to_organization_id = CCGA.organization_id)
530 OR
531 ( MIP.to_organization_id = p_organization_id
532 AND MIP.from_organization_id = CCGA.organization_id)
533 )
534 MINUS
535 SELECT cost_group_id
536 FROM cst_cg_list_temp);
537
538 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
539 THEN
540 FND_MSG_PUB.Add_Exc_Msg(
541 p_pkg_name => G_PKG_NAME,
542 p_procedure_name => l_api_name,
543 p_error_text => l_stmt_num||': Inserted '||SQL%ROWCOUNT||
544 ' cost groups from other organizations '||
545 ' into CCLT'
546 );
547 END IF;
548 END IF;
549
550 x_return_status := FND_API.G_RET_STS_SUCCESS;
551 EXCEPTION
552 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
553 ROLLBACK TO Populate_CostGroupList_PVT;
554 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
555 WHEN OTHERS THEN
556 ROLLBACK TO Populate_CostGroupList_PVT;
557 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
558 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
559 THEN
560 FND_MSG_PUB.Add_Exc_Msg(
561 p_pkg_name => G_PKG_NAME,
562 p_procedure_name => l_api_name,
563 p_error_text => SUBSTR(l_stmt_num||SQLERRM,1,240)
564 );
565 END IF;
566 END Populate_CostGroupList;
567
568 PROCEDURE Populate_SubinventoryList(
569 p_api_version IN NUMBER,
570 p_organization_id IN NUMBER,
571 p_subinventory_from IN VARCHAR2,
572 p_subinventory_to IN VARCHAR2,
573 p_expense_sub IN NUMBER,
574 x_return_status OUT NOCOPY VARCHAR2
575 )
576 IS
577 l_api_name CONSTANT VARCHAR2(30) := 'Populate_SubinventoryList';
578 l_api_version CONSTANT NUMBER := 1.0;
579 l_msg_level_threshold NUMBER;
580 l_stmt_num NUMBER := 0;
581 BEGIN
582 -- Standard Start of API savepoint
586 IF NOT FND_API.Compatible_API_Call(
583 SAVEPOINT Populate_SubinventoryList_PVT;
584
585 -- Check for call compatibility
587 p_current_version_number => l_api_version,
588 p_caller_version_number => p_api_version,
589 p_api_name => l_api_name,
590 p_pkg_name => G_PKG_NAME
591 )
592 THEN
593 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
594 END IF;
595
596 -- Check for message level threshold
597 l_msg_level_threshold := FND_PROFILE.Value('FND_AS_MSG_LEVEL_THRESHOLD');
598
599 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH
600 THEN
601 FND_MSG_PUB.Add_Exc_Msg(
602 p_pkg_name => G_PKG_NAME,
603 p_procedure_name => l_api_name,
604 p_error_text => SUBSTR(
605 l_stmt_num||':'||
606 p_organization_id||','||
607 p_subinventory_from||','||
608 p_subinventory_to||','||
609 p_expense_sub,
610 1,
611 240
612 )
613 );
614 END IF;
615
616 -- Populate subinventory list
617 l_stmt_num := 10;
618 INSERT
619 INTO cst_sub_list_temp(
620 subinventory_code
621 )
622 SELECT SUB.secondary_inventory_name
623 FROM mtl_secondary_inventories SUB
624 WHERE SUB.organization_id = p_organization_id
625 AND SUB.asset_inventory = DECODE(p_expense_sub,1,SUB.asset_inventory,1)
626 -- Non-quantity tracked subinventories do not appear in MOQ.
627 AND SUB.quantity_tracked = 1
628 AND SUB.secondary_inventory_name
629 BETWEEN NVL(p_subinventory_from, SUB.secondary_inventory_name)
630 AND NVL(p_subinventory_to, SUB.secondary_inventory_name)
631 AND NOT EXISTS ( SELECT 'Subinventory Already Exists'
632 FROM cst_sub_list_temp CSLT
633 where CSLT.subinventory_code = SUB.secondary_inventory_name
634 );
635
636
637 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
638 THEN
639 FND_MSG_PUB.Add_Exc_Msg(
640 p_pkg_name => G_PKG_NAME,
641 p_procedure_name => l_api_name,
642 p_error_text => l_stmt_num||': Inserted '||SQL%ROWCOUNT||
643 ' subinventories into CSLT'
644 );
645 END IF;
646
647 x_return_status := FND_API.G_RET_STS_SUCCESS;
648 EXCEPTION
649 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
650 ROLLBACK TO Populate_SubinventoryList_PVT;
651 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
652 WHEN OTHERS THEN
653 ROLLBACK TO Populate_SubinventoryList_PVT;
654 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
655 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
656 THEN
657 FND_MSG_PUB.Add_Exc_Msg(
658 p_pkg_name => G_PKG_NAME,
659 p_procedure_name => l_api_name,
660 p_error_text => SUBSTR(l_stmt_num||SQLERRM,1,240)
661 );
662 END IF;
663 END Populate_SubinventoryList;
664
665 PROCEDURE Calculate_OnhandQty(
666 p_api_version IN NUMBER,
667 p_organization_id IN NUMBER,
668 p_valuation_date IN DATE,
669 p_qty_by_revision IN NUMBER,
670 p_zero_qty IN NUMBER,
671 p_unvalued_txns IN NUMBER,
672 x_return_status OUT NOCOPY VARCHAR2
673 )
674 IS
675 l_api_name CONSTANT VARCHAR2(30) := 'Calculate_OnhandQty';
676 l_api_version CONSTANT NUMBER := 1.0;
677 l_msg_level_threshold NUMBER;
678 l_stmt_num NUMBER := 0;
679 BEGIN
680 -- Standard Start of API savepoint
681 SAVEPOINT Calculate_OnhandQty_PVT;
682
683 -- Check for call compatibility
684 IF NOT FND_API.Compatible_API_Call(
685 p_current_version_number => l_api_version,
686 p_caller_version_number => p_api_version,
687 p_api_name => l_api_name,
688 p_pkg_name => G_PKG_NAME
689 )
690 THEN
691 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
692 END IF;
693
694 -- Check for message level threshold
695 l_msg_level_threshold := FND_PROFILE.Value('FND_AS_MSG_LEVEL_THRESHOLD');
696
697 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH
698 THEN
699 FND_MSG_PUB.Add_Exc_Msg(
700 p_pkg_name => G_PKG_NAME,
701 p_procedure_name => l_api_name,
702 p_error_text => SUBSTR(
703 l_stmt_num||':'||
704 p_organization_id||','||
705 to_char(p_valuation_date,'DD-MON-YYYY HH24:MI:SS')||','||
706 p_qty_by_revision||','||
707 p_zero_qty||','||
708 p_unvalued_txns,
709 1,
710 240
711 )
712 );
713 END IF;
714
715 -- Calculate Current Onhand Quantity
716 l_stmt_num := 10;
717 INSERT
721 subinventory_code,
718 INTO cst_inv_qty_temp(
719 organization_id,
720 cost_group_id,
722 inventory_item_id,
723 rollback_qty,
724 qty_source,
725 revision,
726 category_id,
727 cost_type_id
728 )
729 SELECT p_organization_id,
730 MOQ.cost_group_id,
731 MOQ.subinventory_code,
732 MOQ.inventory_item_id,
733 SUM(MOQ.transaction_quantity),
734 3, -- CURRENT_ONHAND
735 DECODE(p_qty_by_revision,1,moq.revision,NULL),
736 CILT.category_id,
737 CILT.cost_type_id
738 FROM mtl_onhand_quantities MOQ,
739 cst_item_list_temp CILT,
740 cst_cg_list_temp CCLT,
741 cst_sub_list_temp CSLT
742 WHERE MOQ.organization_id = p_organization_id
743 AND CILT.inventory_item_id = MOQ.inventory_item_id
744 AND CCLT.cost_group_id = MOQ.cost_group_id
745 AND CSLT.subinventory_code = MOQ.subinventory_code
746 GROUP
747 BY MOQ.cost_group_id,
748 MOQ.subinventory_code,
749 MOQ.inventory_item_id,
750 DECODE(p_qty_by_revision,1,moq.revision,NULL),
751 CILT.category_id,
752 CILT.cost_type_id;
753
754 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
755 THEN
756 FND_MSG_PUB.Add_Exc_Msg(
757 p_pkg_name => G_PKG_NAME,
758 p_procedure_name => l_api_name,
759 p_error_text => l_stmt_num||': Calculated '||SQL%ROWCOUNT||
760 ' current onhand quantities'
761 );
762 END IF;
763
764 -- Rollback Uncosted Onhand
765 l_stmt_num := 20;
766 IF NVL(p_unvalued_txns,-1) <> 1
767 THEN
768 INSERT
769 INTO cst_inv_qty_temp(
770 organization_id,
771 cost_group_id,
772 subinventory_code,
773 inventory_item_id,
774 rollback_qty,
775 qty_source,
776 revision,
777 txn_source_type_id,
778 category_id,
779 cost_type_id
780 )
781 SELECT /*+ LEADING (MMT)*/
782 p_organization_id,
783 MMT.cost_group_id,
784 MMT.subinventory_code,
785 MMT.inventory_item_id,
786 -1*SUM(MMT.primary_quantity),
787 -- Sum is used to reduce the number of rows in CIQT
788 4, -- UNCOSTED_ONHAND
789 DECODE(p_qty_by_revision, 1, MMT.revision, NULL),
790 MMT.transaction_source_type_id,
791 CILT.category_id,
792 CILT.cost_type_id
793 FROM mtl_material_transactions MMT,
794 cst_item_list_temp CILT,
795 cst_cg_list_temp CCLT,
796 cst_sub_list_temp CSLT
797 WHERE MMT.organization_id = p_organization_id
798 AND CILT.inventory_item_id = MMT.inventory_item_id
799 AND CCLT.cost_group_id = MMT.cost_group_id
800 AND CSLT.subinventory_code = MMT.subinventory_code
801 AND MMT.costed_flag in ('N','E')
802 -- Ignore consigned transactions
803 AND MMT.organization_id =
804 NVL(MMT.owning_organization_id, MMT.organization_id)
805 AND NVL(MMT.owning_tp_type,2) = 2
806 -- Ignore logical transactions corresponding to drop shipments
807 -- and global procurement transactions
808 AND NVL(MMT.logical_transaction,-1) <> 1
809 -- Ignore WMS/OSFM transactions, cost updates including periodic cost
810 -- updates that do not affect onhand quantity
811 AND MMT.transaction_action_id NOT IN (24,40,41,50,51,52)
812 GROUP
813 BY MMT.cost_group_id,
814 MMT.subinventory_code,
815 MMT.inventory_item_id,
816 DECODE(p_qty_by_revision, 1, MMT.revision, NULL),
817 MMT.transaction_source_type_id,
818 CILT.category_id,
819 CILT.cost_type_id;
820
821 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
822 THEN
823 FND_MSG_PUB.Add_Exc_Msg(
824 p_pkg_name => G_PKG_NAME,
825 p_procedure_name => l_api_name,
826 p_error_text => l_stmt_num||': Calculated '||SQL%ROWCOUNT||
827 ' uncosted onhand quantities'
828 );
829 END IF;
830
831 END IF;
832
833
834 -- Rollback Onhand Quantity to the Valuation Date
835 l_stmt_num := 30;
836 IF p_valuation_date IS NOT NULL
837 THEN
838 INSERT
839 INTO cst_inv_qty_temp
840 ( organization_id,
841 cost_group_id,
842 subinventory_code,
843 inventory_item_id,
844 rollback_qty,
845 qty_source,
846 rollback_value,
847 revision,
848 txn_source_type_id,
852 SELECT p_organization_id,
849 category_id,
850 cost_type_id
851 )
853 MMT.cost_group_id,
854 MMT.subinventory_code,
855 MMT.inventory_item_id,
856 -- There is a bug on Average Cost Update, where primary_quantity
857 -- is populated in addition to quantity_adjusted
858 SUM(-1*DECODE(MMT.transaction_action_id,24,0,MMT.primary_quantity)),
859 -- Sum is used to reduce the number of rows in CIQT
860 5, -- ROLLBACK_ONHAND
861 SUM(
862 DECODE(
863 MMT.transaction_action_id,
864 24, MMT.quantity_adjusted*(MMT.new_cost - MMT.prior_cost),
865 MMT.primary_quantity*MMT.actual_cost - NVL(MMT.variance_amount,0)
866 )
867 ),
868 -- Rollback value is used in the Transaction Value Historical
869 -- Summary - Average Costing report
870 DECODE(p_qty_by_revision, 1, MMT.revision, NULL),
871 MMT.transaction_source_type_id,
872 CILT.category_id,
873 CILT.cost_type_id
874 FROM mtl_material_transactions MMT,
875 cst_item_list_temp CILT
876 WHERE MMT.organization_id = p_organization_id
877 AND CILT.inventory_item_id = MMT.inventory_item_id
878 AND MMT.costed_flag IS NULL
879 AND MMT.transaction_date > p_valuation_date
880 -- Ignore Consigned transactions
881 AND MMT.organization_id = NVL(MMT.owning_organization_id,
882 MMT.organization_id)
883 AND NVL(MMT.owning_tp_type,2) = 2
884 -- Ignore logical transactions corresponding to drop shipments
885 -- and global procurement transactions
886 AND NVL(MMT.logical_transaction,-1) <> 1
887 -- Ignore WMS and OSFM transactions that do not affect onhand
888 -- quantity and inventory valuation
889 AND MMT.transaction_action_id NOT IN (40,41,50,51,52)
890 -- Ignore periodic cost updates
891 AND MMT.transaction_source_type_id <> 14
892 -- The only transactions other than the ones ignored above that
893 -- affect inventory valuation and have null cost_group_id are
894 -- standard cost updates (non-PJM/WMS)
895 AND ( ( MMT.transaction_type_id = 24
896 AND MMT.cost_group_id IS NULL
897 )
898 OR EXISTS (
899 SELECT 1
900 FROM cst_cg_list_temp CCLT
901 WHERE CCLT.cost_group_id = MMT.cost_group_id)
902 )
903 -- The only transactions other than the ones ignored above that
904 -- affect inventory valuation and have null subinventory_code are
905 -- actual cost updates and std cost updates for PJM/WMS orgs
906 AND ( ( MMT.transaction_action_id = 24
907 AND MMT.subinventory_code IS NULL
908 )
909 OR EXISTS (
910 SELECT 1
911 FROM cst_sub_list_temp CSLT
912 WHERE CSLT.subinventory_code = MMT.subinventory_code)
913 )
914 GROUP
915 BY MMT.cost_group_id,
916 MMT.subinventory_code,
917 MMT.inventory_item_id,
918 DECODE(p_qty_by_revision, 1, MMT.revision, NULL),
919 MMT.transaction_source_type_id,
920 CILT.category_id,
921 CILT.cost_type_id;
922
923 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
924 THEN
925 FND_MSG_PUB.Add_Exc_Msg(
926 p_pkg_name => G_PKG_NAME,
927 p_procedure_name => l_api_name,
928 p_error_text => l_stmt_num||': Calculated '||SQL%ROWCOUNT||
929 ' rolled back onhand quantities'
930 );
931 END IF;
932 END IF;
933
934 -- Include zero quantity items
935 IF p_zero_qty = 1
936 THEN
937 l_stmt_num := 40;
938 INSERT
939 INTO cst_inv_qty_temp(
940 organization_id,
941 inventory_item_id,
942 rollback_qty,
943 qty_source,
944 category_id,
945 cost_group_id,
946 cost_type_id
947 )
948 SELECT p_organization_id,
949 TEMP.inventory_item_id,
950 0,
951 3, -- CURRENT_ONHAND
952 TEMP.category_id,
953 MP.default_cost_group_id,
954 TEMP.cost_type_id
955 FROM (
956 SELECT inventory_item_id,
957 category_id,
958 cost_type_id
959 FROM cst_item_list_temp
960 MINUS
961 SELECT DISTINCT
962 inventory_item_id,
963 category_id,
964 cost_type_id
965 FROM cst_inv_qty_temp
966 WHERE organization_id = p_organization_id
967 ) TEMP,
968 mtl_parameters MP
969 WHERE MP.organization_id = p_organization_id;
970
974 p_pkg_name => G_PKG_NAME,
971 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
972 THEN
973 FND_MSG_PUB.Add_Exc_Msg(
975 p_procedure_name => l_api_name,
976 p_error_text => l_stmt_num||': Calculated '||SQL%ROWCOUNT||
977 ' zero quantities'
978 );
979 END IF;
980 END IF;
981
982 x_return_status := FND_API.G_RET_STS_SUCCESS;
983 EXCEPTION
984 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
985 ROLLBACK TO Calculate_OnhandQty_PVT;
986 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
987 WHEN OTHERS THEN
988 ROLLBACK TO Calculate_OnhandQty_PVT;
989 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
990 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
991 THEN
992 FND_MSG_PUB.Add_Exc_Msg(
993 p_pkg_name => G_PKG_NAME,
994 p_procedure_name => l_api_name,
995 p_error_text => SUBSTR(l_stmt_num||SQLERRM,1,240)
996 );
997 END IF;
998 END Calculate_OnhandQty;
999
1000 PROCEDURE Calculate_IntransitQty(
1001 p_api_version IN NUMBER,
1002 p_organization_id IN NUMBER,
1003 p_valuation_date IN DATE,
1004 p_receipt IN NUMBER,
1005 p_shipment IN NUMBER,
1006 p_detail IN NUMBER,
1007 p_own IN NUMBER,
1008 p_unvalued_txns IN NUMBER,
1009 x_return_status OUT NOCOPY VARCHAR2
1010 )
1011 IS
1012 l_api_name CONSTANT VARCHAR2(30) := 'Calculate_IntransitQty';
1013 l_api_version CONSTANT NUMBER := 1.0;
1014 l_msg_level_threshold NUMBER;
1015 --BUG#6109468-FPBUG5606455
1016 l_uncosted_txn_count NUMBER;
1017 l_stmt_num NUMBER := 0;
1018 BEGIN
1019 -- Standard Start of API savepoint
1020 SAVEPOINT Calculate_IntransitQty_PVT;
1021
1022 -- Check for call compatibility
1023 IF NOT FND_API.Compatible_API_Call(
1024 p_current_version_number => l_api_version,
1025 p_caller_version_number => p_api_version,
1026 p_api_name => l_api_name,
1027 p_pkg_name => G_PKG_NAME
1028 )
1029 THEN
1030 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1031 END IF;
1032
1033 -- Check for message level threshold
1034 l_msg_level_threshold := FND_PROFILE.Value('FND_AS_MSG_LEVEL_THRESHOLD');
1035
1036 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH
1037 THEN
1038 FND_MSG_PUB.Add_Exc_Msg(
1039 p_pkg_name => G_PKG_NAME,
1040 p_procedure_name => l_api_name,
1041 p_error_text => SUBSTR(
1042 l_stmt_num||':'||
1043 p_organization_id||','||
1044 to_char(p_valuation_date,'DD-MON-YYYY HH24:MI:SS')||','||
1045 p_receipt||','||
1046 p_shipment||','||
1047 p_detail||','||
1048 p_own||','||
1049 p_unvalued_txns,
1050 1,
1051 240
1052 )
1053 );
1054 END IF;
1055
1056
1057 --BUG#6109468-FOBUG5606455
1058 IF (p_unvalued_txns IS NOT NULL) AND (p_unvalued_txns <> -1) THEN
1059 l_stmt_num := 5;
1060
1061 DECLARE
1062 CURSOR c IS
1063 SELECT /*+ INDEX (MMT MTL_MATERIAL_TRANSACTIONS_N10) */ 1
1064 FROM mtl_material_transactions MMT
1065 WHERE mmt.costed_flag IN ('N','E')
1066 AND ( mmt.organization_id = p_organization_id
1067 OR mmt.transfer_organization_id = p_organization_id)
1068 AND ( mmt.transaction_action_id = 12
1069 OR mmt.transaction_action_id = 21)
1070 AND ROWNUM <2;
1071 BEGIN
1072 OPEN c;
1073 FETCH c INTO l_uncosted_txn_count;
1074 IF c%NOTFOUND THEN
1075 l_uncosted_txn_count := 0;
1076 END IF;
1077 CLOSE c;
1078 END;
1079
1080 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS THEN
1081 FND_MSG_PUB.Add_Exc_Msg(
1082 p_pkg_name => G_PKG_NAME,
1083 p_procedure_name => l_api_name,
1084 p_error_text => l_stmt_num||': Calculated '||l_uncosted_txn_count||
1085 ' uncosted shipment/receipt transactions in the '||
1086 ' current organization' );
1087 END IF;
1088
1089 END IF;
1090
1091
1092
1093 -- Check if the intransit quantity needs to be calculated at the shipment
1094 -- line level OR just the item/from_org/to_org/cost_group combination
1095 IF p_detail = 1
1096 THEN
1097 -- All intransit calculations are for quantities that are related
1098 -- to p_organization_id, but not neccessarily owned by it. This is
1099 -- necessary for the Intransit Valuation Report.
1100
1101 -- Calculate intransit quantity coming into this organization
1102
1103 IF p_receipt = 1
1104 THEN
1105 -- Calculate current intransit quantity coming into this organization
1109 qty_source,
1106 l_stmt_num := 10;
1107 INSERT
1108 INTO cst_inv_qty_temp(
1110 organization_id,
1111 inventory_item_id,
1112 category_id,
1113 revision,
1114 cost_type_id,
1115 cost_group_id,
1116 from_organization_id,
1117 to_organization_id,
1118 rollback_qty,
1119 intransit_inv_account,
1120 shipment_line_id
1121 )
1122 SELECT 6,-- CURRENT_INTRANSIT
1123 MS.intransit_owning_org_id,
1124 ITEMS.inventory_item_id,
1125 ITEMS.category_id,
1126 MS.item_revision,
1127 ITEMS.cost_type_id,
1128 CGS.cost_group_id,
1129 MS.from_organization_id,
1130 MS.to_organization_id,
1131 -- quantity is always expressed in the primary unit of measure
1132 -- of the intransit owning organization
1133 SUM(
1134 DECODE(
1135 MS.intransit_owning_org_id,
1136 MS.from_organization_id,
1137 inv_convert.inv_um_convert(
1138 MS.item_id,NULL,MS.quantity,NULL,NULL,
1139 MS.unit_of_measure,MSI_FROM.primary_unit_of_measure
1140 ),
1141 MS.to_org_primary_quantity
1142 )
1143 ),
1144 NVL(
1145 MMT.intransit_account,
1146 NVL(MIP.intransit_inv_account,MP.intransit_inv_account)
1147 ),
1148 MS.shipment_line_id
1149 FROM mtl_supply MS,
1150 cst_item_list_temp ITEMS,
1151 cst_cg_list_temp CGS,
1152 mtl_parameters MP,
1153 mtl_interorg_parameters MIP,
1154 mtl_material_transactions MMT,
1155 rcv_shipment_lines RSL,
1156 mtl_system_items MSI_FROM
1157 WHERE MS.to_organization_id = p_organization_id
1158 /* AND MS.intransit_owning_org_id = p_organization_id */ /* Bug 5664736 */
1159 AND MS.intransit_owning_org_id = DECODE(NVL(p_own,-1),1,p_organization_id,MS.intransit_owning_org_id)
1160 AND MS.item_id = ITEMS.inventory_item_id
1161 AND MS.supply_type_code IN ('SHIPMENT','RECEIVING')
1162 AND MS.destination_type_code = 'INVENTORY'
1163 AND NVL(MS.cost_group_id,MP.default_cost_group_id) = CGS.cost_group_id
1164 AND MP.organization_id = MS.intransit_owning_org_id
1165 AND RSL.shipment_line_id = MS.shipment_line_id
1166 AND MMT.transaction_id (+) = RSL.mmt_transaction_id
1167 AND MIP.from_organization_id (+) = MS.from_organization_id
1168 AND MIP.to_organization_id (+) = MS.to_organization_id
1169 AND MIP.fob_point (+) =
1170 DECODE(
1171 MS.intransit_owning_org_id,
1172 MS.from_organization_id, 2,
1173 MS.to_organization_id, 1
1174 )
1175 AND MSI_FROM.inventory_item_id = MS.item_id
1176 AND MSI_FROM.organization_id = MS.from_organization_id
1177 GROUP
1178 BY MS.intransit_owning_org_id,
1179 ITEMS.inventory_item_id,
1180 ITEMS.category_id,
1181 MS.item_revision,
1182 ITEMS.cost_type_id,
1183 CGS.cost_group_id,
1184 MS.from_organization_id,
1185 MS.to_organization_id,
1186 NVL(
1187 MMT.intransit_account,
1188 NVL(MIP.intransit_inv_account,MP.intransit_inv_account)
1189 ),
1190 MS.shipment_line_id;
1191
1192 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
1193 THEN
1194 FND_MSG_PUB.Add_Exc_Msg(
1195 p_pkg_name => G_PKG_NAME,
1196 p_procedure_name => l_api_name,
1197 p_error_text => l_stmt_num||': Calculated '||SQL%ROWCOUNT||
1198 ' current intransit quantities coming into the'||
1199 ' current organization'
1200 );
1201 END IF;
1202
1203 -- Calculate uncosted intransit shipment quantities coming into this
1204 -- organization
1205 IF NVL(p_unvalued_txns,-1) <> 1 THEN
1206
1207 IF l_uncosted_txn_count > 0 THEN --BUG6109468-FP5606455
1208
1209 l_stmt_num := 20;
1210 INSERT
1211 INTO cst_inv_qty_temp(
1212 qty_source,
1213 organization_id,
1214 inventory_item_id,
1215 category_id,
1216 revision,
1217 cost_type_id,
1218 cost_group_id,
1219 from_organization_id,
1220 to_organization_id,
1221 rollback_qty,
1222 intransit_inv_account,
1223 shipment_line_id
1224 )
1225 SELECT 7, -- UNCOSTED_INTRANSIT
1226 DECODE(
1227 NVL(MMT.fob_point,MIP.fob_point),
1228 1,MMT.transfer_organization_id,
1229 2,MMT.organization_id
1233 MMT.revision,
1230 ),
1231 ITEMS.inventory_item_id,
1232 ITEMS.category_id,
1234 ITEMS.cost_type_id,
1235 CGS.cost_group_id,
1236 MMT.organization_id,
1237 MMT.transfer_organization_id,
1238 SUM(
1239 DECODE(
1240 NVL(MMT.fob_point,MIP.fob_point),
1241 1,
1242 inv_convert.inv_um_convert(
1243 MMT.inventory_item_id,NULL,MMT.transaction_quantity,
1244 MMT.transaction_uom,MSI_TO.primary_uom_code,NULL,NULL
1245 ),
1246 2,
1247 MMT.primary_quantity
1248 )
1249 ),
1250 NVL(MMT.intransit_account,MIP.intransit_inv_account),
1251 RSL.shipment_line_id
1252 FROM mtl_material_transactions MMT,
1253 cst_item_list_temp ITEMS,
1254 cst_cg_list_temp CGS,
1255 mtl_interorg_parameters MIP,
1256 mtl_system_items MSI_TO,
1257 rcv_shipment_headers RSH,
1258 rcv_shipment_lines RSL
1259 WHERE MMT.transfer_organization_id = p_organization_id
1260 AND MMT.transaction_action_id = 21
1261 AND MMT.costed_flag IN ('N','E')
1262 AND MMT.inventory_item_id = ITEMS.inventory_item_id
1263 AND DECODE(
1264 NVL(MMT.fob_point,MIP.fob_point),
1265 1,MMT.transfer_cost_group_id,
1266 2,MMT.cost_group_id
1267 ) =
1268 CGS.cost_group_id
1269 AND MIP.to_organization_id = MMT.transfer_organization_id
1270 AND MIP.from_organization_id = MMT.organization_id
1271 AND MSI_TO.organization_id = MMT.transfer_organization_id
1272 AND MSI_TO.inventory_item_id = MMT.inventory_item_id
1273 AND RSH.shipment_num = MMT.shipment_number
1274 AND RSL.shipment_header_id = RSH.shipment_header_id
1275 AND RSL.mmt_transaction_id = MMT.transaction_id
1276 GROUP
1277 BY DECODE(
1278 NVL(MMT.fob_point,MIP.fob_point),
1279 1,MMT.transfer_organization_id,
1280 2,MMT.organization_id
1281 ),
1282 ITEMS.inventory_item_id,
1283 ITEMS.category_id,
1284 MMT.revision,
1285 ITEMS.cost_type_id,
1286 CGS.cost_group_id,
1287 MMT.organization_id,
1288 MMT.transfer_organization_id,
1289 NVL(MMT.intransit_account,MIP.intransit_inv_account),
1290 RSL.shipment_line_id;
1291
1292 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
1293 THEN
1294 FND_MSG_PUB.Add_Exc_Msg(
1295 p_pkg_name => G_PKG_NAME,
1296 p_procedure_name => l_api_name,
1297 p_error_text => l_stmt_num||': Calculated '||SQL%ROWCOUNT||
1298 ' uncosted intransit shipment quantities coming'||
1299 ' into the current organization'
1300 );
1301 END IF;
1302
1303 -- Calculate uncosted intransit receipt quantities coming into this
1304 -- organization
1305 l_stmt_num := 30;
1306 INSERT
1307 INTO cst_inv_qty_temp(
1308 qty_source,
1309 organization_id,
1310 inventory_item_id,
1311 category_id,
1312 revision,
1313 cost_type_id,
1314 cost_group_id,
1315 from_organization_id,
1316 to_organization_id,
1317 rollback_qty,
1318 intransit_inv_account,
1319 shipment_line_id
1320 )
1321 SELECT 7, -- UNCOSTED_INTRANSIT
1322 DECODE(
1323 NVL(MMT.fob_point,MIP.fob_point),
1324 1,MMT.organization_id,
1325 2,MMT.transfer_organization_id
1326 ),
1327 ITEMS.inventory_item_id,
1328 ITEMS.category_id,
1329 MMT.revision,
1330 ITEMS.cost_type_id,
1331 CGS.cost_group_id,
1332 MMT.transfer_organization_id,
1333 MMT.organization_id,
1334 SUM(
1335 DECODE(
1336 NVL(MMT.fob_point,MIP.fob_point),
1337 1,
1338 MMT.primary_quantity,
1339 2,
1340 inv_convert.inv_um_convert(
1341 MMT.inventory_item_id,NULL,MMT.transaction_quantity,
1342 MMT.transaction_uom,MSI_FROM.primary_uom_code,NULL,NULL
1343 )
1344 )
1345 ),
1346 NVL(MMT.intransit_account, MIP.intransit_inv_account),
1347 RT.shipment_line_id
1348 FROM mtl_material_transactions MMT,
1349 cst_item_list_temp ITEMS,
1350 cst_cg_list_temp CGS,
1351 mtl_interorg_parameters MIP,
1352 mtl_system_items MSI_FROM,
1356 AND MMT.costed_flag IN ('N', 'E')
1353 rcv_transactions RT
1354 WHERE MMT.organization_id = p_organization_id
1355 AND MMT.transaction_action_id = 12
1357 AND MMT.inventory_item_id = ITEMS.inventory_item_id
1358 AND DECODE(
1359 NVL(MMT.fob_point,MIP.fob_point),
1360 1,MMT.cost_group_id,
1361 2,MMT.transfer_cost_group_id
1362 ) =
1363 CGS.cost_group_id
1364 AND MIP.to_organization_id = MMT.organization_id
1365 AND MIP.from_organization_id = MMT.transfer_organization_id
1366 AND MSI_FROM.organization_id = MMT.transfer_organization_id
1367 AND MSI_FROM.inventory_item_id = MMT.inventory_item_id
1368 AND RT.transaction_id = MMT.rcv_transaction_id
1369 GROUP
1370 BY DECODE(
1371 NVL(MMT.fob_point,MIP.fob_point),
1372 1,MMT.organization_id,
1373 2,MMT.transfer_organization_id
1374 ),
1375 ITEMS.inventory_item_id,
1376 ITEMS.category_id,
1377 MMT.revision,
1378 ITEMS.cost_type_id,
1379 CGS.cost_group_id,
1380 MMT.organization_id,
1381 MMT.transfer_organization_id,
1382 NVL(MMT.intransit_account, MIP.intransit_inv_account),
1383 RT.shipment_line_id;
1384
1385 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
1386 THEN
1387 FND_MSG_PUB.Add_Exc_Msg(
1388 p_pkg_name => G_PKG_NAME,
1389 p_procedure_name => l_api_name,
1390 p_error_text => l_stmt_num||': Calculated '||SQL%ROWCOUNT||
1391 ' uncosted intransit receipt quantities coming'||
1392 ' into the current org'
1393 );
1394 END IF;
1395 END IF; --BUG6109468-FPBUG5606455 --l_uncosted_txn_count>0
1396 END IF; -- NVL(p_unvalued_txns,-1) <> 1
1397
1398 IF p_valuation_date IS NOT NULL
1399 THEN
1400 -- Calculate rollback intransit shipment quantities coming into this
1401 -- organization. The code for this calculation is similar to the one used
1402 -- to calculate uncosted intransit shipment quantities coming into this
1403 -- organization. The only difference is instead of checking for
1404 -- costed_flag in ('N','E'), we check for costed_flag is NULL and
1405 -- transaction_date > p_valuation_date
1406 l_stmt_num := 40;
1407 INSERT
1408 INTO cst_inv_qty_temp(
1409 qty_source,
1410 organization_id,
1411 inventory_item_id,
1412 category_id,
1413 revision,
1414 cost_type_id,
1415 cost_group_id,
1416 from_organization_id,
1417 to_organization_id,
1418 rollback_qty,
1419 intransit_inv_account,
1420 shipment_line_id
1421 )
1422 SELECT 8, -- ROLLBACK_INTRANSIT
1423 DECODE(
1424 NVL(MMT.fob_point,MIP.fob_point),
1425 1,MMT.transfer_organization_id,
1426 2,MMT.organization_id
1427 ),
1428 ITEMS.inventory_item_id,
1429 ITEMS.category_id,
1430 MMT.revision,
1431 ITEMS.cost_type_id,
1432 CGS.cost_group_id,
1433 MMT.organization_id,
1434 MMT.transfer_organization_id,
1435 SUM(
1436 DECODE(
1437 NVL(MMT.fob_point,MIP.fob_point),
1438 1,
1439 inv_convert.inv_um_convert(
1440 MMT.inventory_item_id,NULL,MMT.transaction_quantity,
1441 MMT.transaction_uom,MSI_TO.primary_uom_code,NULL,NULL
1442 ),
1443 2,
1444 MMT.primary_quantity
1445 )
1446 ),
1447 NVL(MMT.intransit_account,MIP.intransit_inv_account),
1448 RSL.shipment_line_id
1449 FROM mtl_material_transactions MMT,
1450 cst_item_list_temp ITEMS,
1451 cst_cg_list_temp CGS,
1452 mtl_interorg_parameters MIP,
1453 mtl_system_items MSI_TO,
1454 rcv_shipment_headers RSH,
1455 rcv_shipment_lines RSL
1456 WHERE MMT.transfer_organization_id = p_organization_id
1457 AND MMT.transaction_action_id = 21
1458 AND MMT.costed_flag IS NULL
1459 AND MMT.transaction_date > p_valuation_date
1460 AND MMT.inventory_item_id = ITEMS.inventory_item_id
1461 AND DECODE(
1462 NVL(MMT.fob_point,MIP.fob_point),
1463 1,MMT.transfer_cost_group_id,
1464 2,MMT.cost_group_id
1465 ) =
1466 CGS.cost_group_id
1467 AND MIP.to_organization_id = MMT.transfer_organization_id
1468 AND MIP.from_organization_id = MMT.organization_id
1472 AND RSL.shipment_header_id = RSH.shipment_header_id
1469 AND MSI_TO.organization_id = MMT.transfer_organization_id
1470 AND MSI_TO.inventory_item_id = MMT.inventory_item_id
1471 AND RSH.shipment_num = MMT.shipment_number
1473 AND RSL.mmt_transaction_id = MMT.transaction_id
1474 GROUP
1475 BY DECODE(
1476 NVL(MMT.fob_point,MIP.fob_point),
1477 1,MMT.transfer_organization_id,
1478 2,MMT.organization_id
1479 ),
1480 ITEMS.inventory_item_id,
1481 ITEMS.category_id,
1482 MMT.revision,
1483 ITEMS.cost_type_id,
1484 CGS.cost_group_id,
1485 MMT.organization_id,
1486 MMT.transfer_organization_id,
1487 NVL(MMT.intransit_account,MIP.intransit_inv_account),
1488 RSL.shipment_line_id;
1489
1490 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
1491 THEN
1492 FND_MSG_PUB.Add_Exc_Msg(
1493 p_pkg_name => G_PKG_NAME,
1494 p_procedure_name => l_api_name,
1495 p_error_text => l_stmt_num||': Calculated '||SQL%ROWCOUNT||
1496 ' rolled back intransit shipment quantities'||
1497 ' coming into the current org'
1498 );
1499 END IF;
1500
1501 -- Calculate rollback intransit receipt quantities coming into this
1502 -- organization. The code for this calculation is similar to the one
1503 -- used to calculate uncosted intransit receipt quantities coming into
1504 -- this organization. The only difference is instead of checking for
1505 -- costed_flag in ('N','E'), we check for costed_flag is NULL and
1506 -- transaction_date > p_valuation_date
1507 l_stmt_num := 50;
1508 INSERT
1509 INTO cst_inv_qty_temp(
1510 qty_source,
1511 organization_id,
1512 inventory_item_id,
1513 category_id,
1514 revision,
1515 cost_type_id,
1516 cost_group_id,
1517 from_organization_id,
1518 to_organization_id,
1519 rollback_qty,
1520 intransit_inv_account,
1521 shipment_line_id
1522 )
1523 SELECT 8, -- ROLLBACK_INTRANSIT
1524 DECODE(
1525 NVL(MMT.fob_point,MIP.fob_point),
1526 1,MMT.organization_id,
1527 2,MMT.transfer_organization_id
1528 ),
1529 ITEMS.inventory_item_id,
1530 ITEMS.category_id,
1531 MMT.revision,
1532 ITEMS.cost_type_id,
1533 CGS.cost_group_id,
1534 MMT.transfer_organization_id,
1535 MMT.organization_id,
1536 SUM(
1537 DECODE(
1538 NVL(MMT.fob_point,MIP.fob_point),
1539 1,
1540 MMT.primary_quantity,
1541 2,
1542 inv_convert.inv_um_convert(
1543 MMT.inventory_item_id,NULL,MMT.transaction_quantity,
1544 MMT.transaction_uom,MSI_FROM.primary_uom_code,NULL,NULL
1545 )
1546 )
1547 ),
1548 NVL(MMT.intransit_account, MIP.intransit_inv_account),
1549 RT.shipment_line_id
1550 FROM mtl_material_transactions MMT,
1551 cst_item_list_temp ITEMS,
1552 cst_cg_list_temp CGS,
1553 mtl_interorg_parameters MIP,
1554 mtl_system_items MSI_FROM,
1555 rcv_transactions RT
1556 WHERE MMT.organization_id = p_organization_id
1557 AND MMT.transaction_action_id = 12
1558 AND MMT.costed_flag IS NULL
1559 AND MMT.transaction_date > p_valuation_date
1560 AND MMT.inventory_item_id = ITEMS.inventory_item_id
1561 AND DECODE(
1562 NVL(MMT.fob_point,MIP.fob_point),
1563 1,MMT.cost_group_id,
1564 2,MMT.transfer_cost_group_id
1565 ) =
1566 CGS.cost_group_id
1567 AND MIP.to_organization_id = MMT.organization_id
1568 AND MIP.from_organization_id = MMT.transfer_organization_id
1569 AND MSI_FROM.organization_id = MMT.transfer_organization_id
1570 AND MSI_FROM.inventory_item_id = MMT.inventory_item_id
1571 AND RT.transaction_id = MMT.rcv_transaction_id
1572 GROUP
1573 BY DECODE(
1574 NVL(MMT.fob_point,MIP.fob_point),
1575 1,MMT.organization_id,
1576 2,MMT.transfer_organization_id
1577 ),
1578 ITEMS.inventory_item_id,
1579 ITEMS.category_id,
1580 MMT.revision,
1581 ITEMS.cost_type_id,
1582 CGS.cost_group_id,
1583 MMT.organization_id,
1584 MMT.transfer_organization_id,
1585 NVL(MMT.intransit_account, MIP.intransit_inv_account),
1586 RT.shipment_line_id;
1590 FND_MSG_PUB.Add_Exc_Msg(
1587
1588 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
1589 THEN
1591 p_pkg_name => G_PKG_NAME,
1592 p_procedure_name => l_api_name,
1593 p_error_text => l_stmt_num||': Calculated '||SQL%ROWCOUNT||
1594 ' rolled back intransit shipment quantities'||
1595 ' coming into the current organization'
1596 );
1597 END IF;
1598 END IF; -- p_valuation_date IS NOT NULL
1599 END IF; -- p_receipt = 1
1600
1601 IF p_shipment = 1 THEN
1602 -- Calculate current intransit quantity going out of this organization
1603 -- The code for this calculation is similar to the one used to calculate
1604 -- current intransit quantities coming into this organization. The only
1605 -- difference is instead of checking for MS.to_organization_id =
1606 -- p_organization_id, we check for MS.from_organization_id =
1607 -- p_organization_id
1608 l_stmt_num := 60;
1609 INSERT
1610 INTO cst_inv_qty_temp(
1611 qty_source,
1612 organization_id,
1613 inventory_item_id,
1614 category_id,
1615 revision,
1616 cost_type_id,
1617 cost_group_id,
1618 from_organization_id,
1619 to_organization_id,
1620 rollback_qty,
1621 intransit_inv_account,
1622 shipment_line_id
1623 )
1624 SELECT 6,-- CURRENT_INTRANSIT
1625 MS.intransit_owning_org_id,
1626 ITEMS.inventory_item_id,
1627 ITEMS.category_id,
1628 MS.item_revision,
1629 ITEMS.cost_type_id,
1630 CGS.cost_group_id,
1631 MS.from_organization_id,
1632 MS.to_organization_id,
1633 SUM(
1634 DECODE(
1635 MS.intransit_owning_org_id,
1636 MS.from_organization_id,
1637 inv_convert.inv_um_convert(
1638 MS.item_id,NULL,MS.quantity,NULL,NULL,
1639 MS.unit_of_measure,MSI_FROM.primary_unit_of_measure
1640 ),
1641 MS.to_org_primary_quantity
1642 )
1643 ),
1644 NVL(
1645 MMT.intransit_account,
1646 NVL(MIP.intransit_inv_account,MP.intransit_inv_account)
1647 ),
1648 MS.shipment_line_id
1649 FROM mtl_supply MS,
1650 cst_item_list_temp ITEMS,
1651 cst_cg_list_temp CGS,
1652 mtl_parameters MP,
1653 mtl_interorg_parameters MIP,
1654 mtl_material_transactions MMT,
1655 rcv_shipment_lines RSL,
1656 mtl_system_items MSI_FROM
1657 WHERE MS.from_organization_id = p_organization_id
1658 /* AND MS.intransit_owning_org_id = p_organization_id */ /* Bug 5664736 */
1659 AND MS.intransit_owning_org_id = DECODE(NVL(p_own,-1),1,p_organization_id,MS.intransit_owning_org_id)
1660 AND MS.item_id = ITEMS.inventory_item_id
1661 AND MS.supply_type_code IN ('SHIPMENT','RECEIVING')
1662 AND MS.destination_type_code = 'INVENTORY'
1663 AND NVL(MS.cost_group_id,MP.default_cost_group_id) = CGS.cost_group_id
1664 AND MP.organization_id = MS.intransit_owning_org_id
1665 AND RSL.shipment_line_id = MS.shipment_line_id
1666 AND MMT.transaction_id (+) = RSL.mmt_transaction_id
1667 AND MIP.from_organization_id (+) = MS.from_organization_id
1668 AND MIP.to_organization_id (+) = MS.to_organization_id
1669 AND MIP.fob_point (+) =
1670 DECODE(
1671 MS.intransit_owning_org_id,
1672 MS.from_organization_id, 2,
1673 MS.to_organization_id, 1)
1674 AND MSI_FROM.inventory_item_id = MS.item_id
1675 AND MSI_FROM.organization_id = MS.from_organization_id
1676 GROUP
1677 BY MS.intransit_owning_org_id,
1678 ITEMS.inventory_item_id,
1679 ITEMS.category_id,
1680 MS.item_revision,
1681 ITEMS.cost_type_id,
1682 CGS.cost_group_id,
1683 MS.from_organization_id,
1684 MS.to_organization_id,
1685 NVL(
1686 MMT.intransit_account,
1687 NVL(MIP.intransit_inv_account,MP.intransit_inv_account)
1688 ),
1689 MS.shipment_line_id;
1690
1691 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
1692 THEN
1693 FND_MSG_PUB.Add_Exc_Msg(
1694 p_pkg_name => G_PKG_NAME,
1695 p_procedure_name => l_api_name,
1696 p_error_text => l_stmt_num||': Calculated '||SQL%ROWCOUNT||
1697 ' current intransit quantities going out of the'||
1698 ' current organization'
1699 );
1700 END IF;
1701
1702 IF NVL(p_unvalued_txns,-1) <> 1 THEN
1703
1704 IF l_uncosted_txn_count > 0 THEN --BUG#6109468-FPBUG5606455
1705
1706 -- Calculate uncosted intransit shipment quantities going out of this
1710 -- MMT.transfer_organization_id = p_organization_id, we check for
1707 -- organization. The code for this calculation is similar to the one used
1708 -- to calculate uncosted intransit shipment quantities coming into this
1709 -- organization. The only difference is instead of checking for
1711 -- MMT.organization_id = p_organization_id
1712 l_stmt_num := 70;
1713 INSERT
1714 INTO cst_inv_qty_temp(
1715 qty_source,
1716 organization_id,
1717 inventory_item_id,
1718 category_id,
1719 revision,
1720 cost_type_id,
1721 cost_group_id,
1722 from_organization_id,
1723 to_organization_id,
1724 rollback_qty,
1725 intransit_inv_account,
1726 shipment_line_id
1727 )
1728 SELECT 7, -- UNCOSTED_INTRANSIT
1729 DECODE(
1730 NVL(MMT.fob_point,MIP.fob_point),
1731 1,MMT.transfer_organization_id,
1732 2,MMT.organization_id
1733 ),
1734 ITEMS.inventory_item_id,
1735 ITEMS.category_id,
1736 MMT.revision,
1737 ITEMS.cost_type_id,
1738 CGS.cost_group_id,
1739 MMT.organization_id,
1740 MMT.transfer_organization_id,
1741 SUM(
1742 DECODE(
1743 NVL(MMT.fob_point,MIP.fob_point),
1744 1,
1745 inv_convert.inv_um_convert(
1746 MMT.inventory_item_id,NULL,MMT.transaction_quantity,
1747 MMT.transaction_uom,MSI_TO.primary_uom_code,NULL,NULL
1748 ),
1749 2,
1750 MMT.primary_quantity
1751 )
1752 ),
1753 NVL(MMT.intransit_account,MIP.intransit_inv_account),
1754 RSL.shipment_line_id
1755 FROM mtl_material_transactions MMT,
1756 cst_item_list_temp ITEMS,
1757 cst_cg_list_temp CGS,
1758 mtl_interorg_parameters MIP,
1759 mtl_system_items MSI_TO,
1760 rcv_shipment_headers RSH,
1761 rcv_shipment_lines RSL
1762 WHERE MMT.organization_id = p_organization_id
1763 AND MMT.transaction_action_id = 21
1764 AND MMT.costed_flag IN ('N','E')
1765 AND MMT.inventory_item_id = ITEMS.inventory_item_id
1766 AND DECODE(
1767 NVL(MMT.fob_point,MIP.fob_point),
1768 1,MMT.transfer_cost_group_id,
1769 2,MMT.cost_group_id
1770 ) =
1771 CGS.cost_group_id
1772 AND MIP.to_organization_id = MMT.transfer_organization_id
1773 AND MIP.from_organization_id = MMT.organization_id
1774 AND MSI_TO.organization_id = MMT.transfer_organization_id
1775 AND MSI_TO.inventory_item_id = MMT.inventory_item_id
1776 AND RSH.shipment_num = MMT.shipment_number
1777 AND RSL.shipment_header_id = RSH.shipment_header_id
1778 AND RSL.mmt_transaction_id = MMT.transaction_id
1779 GROUP
1780 BY DECODE(
1781 NVL(MMT.fob_point,MIP.fob_point),
1782 1,MMT.transfer_organization_id,
1783 2,MMT.organization_id
1784 ),
1785 ITEMS.inventory_item_id,
1786 ITEMS.category_id,
1787 MMT.revision,
1788 ITEMS.cost_type_id,
1789 CGS.cost_group_id,
1790 MMT.organization_id,
1791 MMT.transfer_organization_id,
1792 NVL(MMT.intransit_account,MIP.intransit_inv_account),
1793 RSL.shipment_line_id;
1794
1795 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
1796 THEN
1797 FND_MSG_PUB.Add_Exc_Msg(
1798 p_pkg_name => G_PKG_NAME,
1799 p_procedure_name => l_api_name,
1800 p_error_text => l_stmt_num||': Calculated '||SQL%ROWCOUNT||
1801 ' uncosted intransit shipment quantities going out'||
1802 ' of the current organization'
1803 );
1804 END IF;
1805
1806 -- Calculate uncosted intransit receipt quantities going out of this
1807 -- organization. The code for this calculation is similar to the one used
1808 -- to calculate uncosted intransit receipt quantities going out of this
1809 -- organization. The only difference is instead of checking for
1810 -- MMT.organization_id = p_organization_id, we check for
1811 -- MMT.organization_id = p_organization_id
1812 l_stmt_num := 80;
1813 INSERT
1814 INTO cst_inv_qty_temp(
1815 qty_source,
1816 organization_id,
1817 inventory_item_id,
1818 category_id,
1819 revision,
1820 cost_type_id,
1821 cost_group_id,
1822 from_organization_id,
1823 to_organization_id,
1827 )
1824 rollback_qty,
1825 intransit_inv_account,
1826 shipment_line_id
1828 SELECT 7, -- UNCOSTED_INTRANSIT
1829 DECODE(
1830 NVL(MMT.fob_point,MIP.fob_point),
1831 1,MMT.organization_id,
1832 2,MMT.transfer_organization_id
1833 ),
1834 ITEMS.inventory_item_id,
1835 ITEMS.category_id,
1836 MMT.revision,
1837 ITEMS.cost_type_id,
1838 CGS.cost_group_id,
1839 MMT.transfer_organization_id,
1840 MMT.organization_id,
1841 SUM(
1842 DECODE(
1843 NVL(MMT.fob_point,MIP.fob_point),
1844 1,
1845 MMT.primary_quantity,
1846 2,
1847 inv_convert.inv_um_convert(
1848 MMT.inventory_item_id,NULL,MMT.transaction_quantity,
1849 MMT.transaction_uom,MSI_FROM.primary_uom_code,NULL,NULL
1850 )
1851 )
1852 ),
1853 NVL(MMT.intransit_account, MIP.intransit_inv_account),
1854 RT.shipment_line_id
1855 FROM mtl_material_transactions MMT,
1856 cst_item_list_temp ITEMS,
1857 cst_cg_list_temp CGS,
1858 mtl_interorg_parameters MIP,
1859 mtl_system_items MSI_FROM,
1860 rcv_transactions RT
1861 WHERE MMT.transfer_organization_id = p_organization_id
1862 AND MMT.transaction_action_id = 12
1863 AND MMT.costed_flag IN ('N', 'E')
1864 AND MMT.inventory_item_id = ITEMS.inventory_item_id
1865 AND DECODE(
1866 NVL(MMT.fob_point,MIP.fob_point),
1867 1,MMT.cost_group_id,
1868 2,MMT.transfer_cost_group_id
1869 ) =
1870 CGS.cost_group_id
1871 AND MIP.to_organization_id = MMT.organization_id
1872 AND MIP.from_organization_id = MMT.transfer_organization_id
1873 AND MSI_FROM.organization_id = MMT.transfer_organization_id
1874 AND MSI_FROM.inventory_item_id = MMT.inventory_item_id
1875 AND RT.transaction_id = MMT.rcv_transaction_id
1876 GROUP
1877 BY DECODE(
1878 NVL(MMT.fob_point,MIP.fob_point),
1879 1,MMT.organization_id,
1880 2,MMT.transfer_organization_id
1881 ),
1882 ITEMS.inventory_item_id,
1883 ITEMS.category_id,
1884 MMT.revision,
1885 ITEMS.cost_type_id,
1886 CGS.cost_group_id,
1887 MMT.organization_id,
1888 MMT.transfer_organization_id,
1889 NVL(MMT.intransit_account, MIP.intransit_inv_account),
1890 RT.shipment_line_id;
1891
1892 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
1893 THEN
1894 FND_MSG_PUB.Add_Exc_Msg(
1895 p_pkg_name => G_PKG_NAME,
1896 p_procedure_name => l_api_name,
1897 p_error_text => l_stmt_num||': Calculated '||SQL%ROWCOUNT||
1898 ' uncosted intransit receipt quantities going out'||
1899 ' of the current organization'
1900 );
1901 END IF;
1902 END IF; -- l_uncosted_txn_count >0
1903 END IF; -- NVL(p_unvalued_txns,-1) <> 1
1904
1905 -- Calculate rollback intransit shipment quantities going out of this
1906 -- organization. The code for this calculation is similar to the one
1907 -- used to calculate uncosted intransit shipment quantities going out
1908 -- of this organization. The only difference is instead of checking for
1909 -- costed_flag in ('N','E'), we check for costed_flag is NULL and
1910 -- transaction_date > p_valuation_date
1911 IF p_valuation_date IS NOT NULL
1912 THEN
1913 l_stmt_num := 90;
1914 INSERT
1915 INTO cst_inv_qty_temp(
1916 qty_source,
1917 organization_id,
1918 inventory_item_id,
1919 category_id,
1920 revision,
1921 cost_type_id,
1922 cost_group_id,
1923 from_organization_id,
1924 to_organization_id,
1925 rollback_qty,
1926 intransit_inv_account,
1927 shipment_line_id
1928 )
1929 SELECT 8, -- ROLLBACK_INTRANSIT
1930 DECODE(
1931 NVL(MMT.fob_point,MIP.fob_point),
1932 1,MMT.transfer_organization_id,
1933 2,MMT.organization_id
1934 ),
1935 ITEMS.inventory_item_id,
1936 ITEMS.category_id,
1937 MMT.revision,
1938 ITEMS.cost_type_id,
1939 CGS.cost_group_id,
1940 MMT.organization_id,
1941 MMT.transfer_organization_id,
1942 SUM(
1943 DECODE(
1947 MMT.inventory_item_id,NULL,MMT.transaction_quantity,
1944 NVL(MMT.fob_point,MIP.fob_point),
1945 1,
1946 inv_convert.inv_um_convert(
1948 MMT.transaction_uom,MSI_TO.primary_uom_code,NULL,NULL
1949 ),
1950 2,
1951 MMT.primary_quantity
1952 )
1953 ),
1954 NVL(MMT.intransit_account,MIP.intransit_inv_account),
1955 RSL.shipment_line_id
1956 FROM mtl_material_transactions MMT,
1957 cst_item_list_temp ITEMS,
1958 cst_cg_list_temp CGS,
1959 mtl_interorg_parameters MIP,
1960 mtl_system_items MSI_TO,
1961 rcv_shipment_headers RSH,
1962 rcv_shipment_lines RSL
1963 WHERE MMT.organization_id = p_organization_id
1964 AND MMT.transaction_action_id = 21
1965 AND MMT.costed_flag IS NULL
1966 AND MMT.transaction_date > p_valuation_date
1967 AND MMT.inventory_item_id = ITEMS.inventory_item_id
1968 AND DECODE(
1969 NVL(MMT.fob_point,MIP.fob_point),
1970 1,MMT.transfer_cost_group_id,
1971 2,MMT.cost_group_id
1972 ) =
1973 CGS.cost_group_id
1974 AND MIP.to_organization_id = MMT.transfer_organization_id
1975 AND MIP.from_organization_id = MMT.organization_id
1976 AND MSI_TO.organization_id = MMT.transfer_organization_id
1977 AND MSI_TO.inventory_item_id = MMT.inventory_item_id
1978 AND RSH.shipment_num = MMT.shipment_number
1979 AND RSL.shipment_header_id = RSH.shipment_header_id
1980 AND RSL.mmt_transaction_id = MMT.transaction_id
1981 GROUP
1982 BY DECODE(
1983 NVL(MMT.fob_point,MIP.fob_point),
1984 1,MMT.transfer_organization_id,
1985 2,MMT.organization_id
1986 ),
1987 ITEMS.inventory_item_id,
1988 ITEMS.category_id,
1989 MMT.revision,
1990 ITEMS.cost_type_id,
1991 CGS.cost_group_id,
1992 MMT.organization_id,
1993 MMT.transfer_organization_id,
1994 NVL(MMT.intransit_account,MIP.intransit_inv_account),
1995 RSL.shipment_line_id;
1996
1997 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
1998 THEN
1999 FND_MSG_PUB.Add_Exc_Msg(
2000 p_pkg_name => G_PKG_NAME,
2001 p_procedure_name => l_api_name,
2002 p_error_text => l_stmt_num||': Calculated '||SQL%ROWCOUNT||
2003 ' rolled back intransit shipment quantities'||
2004 ' going out of the current organization'
2005 );
2006 END IF;
2007
2008 -- Calculate rollback intransit receipt quantities going out of this
2009 -- organization. The code for this calculation is similar to the one
2010 -- used to calculate uncosted intransit receipt quantities going out of
2011 -- this organization. The only difference is instead of checking for
2012 -- costed_flag in ('N','E'), we check for costed_flag is NULL and
2013 -- transaction_date > p_valuation_date
2014 l_stmt_num := 100;
2015 INSERT
2016 INTO cst_inv_qty_temp(
2017 qty_source,
2018 organization_id,
2019 inventory_item_id,
2020 category_id,
2021 revision,
2022 cost_type_id,
2023 cost_group_id,
2024 from_organization_id,
2025 to_organization_id,
2026 rollback_qty,
2027 intransit_inv_account,
2028 shipment_line_id
2029 )
2030 SELECT 8, -- ROLLBACK_INTRANSIT
2031 DECODE(
2032 NVL(MMT.fob_point,MIP.fob_point),
2033 1,MMT.organization_id,
2034 2,MMT.transfer_organization_id
2035 ),
2036 ITEMS.inventory_item_id,
2037 ITEMS.category_id,
2038 MMT.revision,
2039 ITEMS.cost_type_id,
2040 CGS.cost_group_id,
2041 MMT.transfer_organization_id,
2042 MMT.organization_id,
2043 SUM(
2044 DECODE(
2045 NVL(MMT.fob_point,MIP.fob_point),
2046 1,
2047 MMT.primary_quantity,
2048 2,
2049 inv_convert.inv_um_convert(
2050 MMT.inventory_item_id,NULL,MMT.transaction_quantity,
2051 MMT.transaction_uom,MSI_FROM.primary_uom_code,NULL,NULL
2052 )
2053 )
2054 ),
2055 NVL(MMT.intransit_account, MIP.intransit_inv_account),
2056 RT.shipment_line_id
2057 FROM mtl_material_transactions MMT,
2058 cst_item_list_temp ITEMS,
2059 cst_cg_list_temp CGS,
2060 mtl_interorg_parameters MIP,
2064 AND MMT.transaction_action_id = 12
2061 mtl_system_items MSI_FROM,
2062 rcv_transactions RT
2063 WHERE MMT.transfer_organization_id = p_organization_id
2065 AND MMT.costed_flag IS NULL
2066 AND MMT.transaction_date > p_valuation_date
2067 AND MMT.inventory_item_id = ITEMS.inventory_item_id
2068 AND DECODE(
2069 NVL(MMT.fob_point,MIP.fob_point),
2070 1,MMT.cost_group_id,
2071 2,MMT.transfer_cost_group_id
2072 ) =
2073 CGS.cost_group_id
2074 AND MIP.to_organization_id = MMT.organization_id
2075 AND MIP.from_organization_id = MMT.transfer_organization_id
2076 AND MSI_FROM.organization_id = MMT.transfer_organization_id
2077 AND MSI_FROM.inventory_item_id = MMT.inventory_item_id
2078 AND RT.transaction_id = MMT.rcv_transaction_id
2079 GROUP
2080 BY DECODE(
2081 NVL(MMT.fob_point,MIP.fob_point),
2082 1,MMT.organization_id,
2083 2,MMT.transfer_organization_id
2084 ),
2085 ITEMS.inventory_item_id,
2086 ITEMS.category_id,
2087 MMT.revision,
2088 ITEMS.cost_type_id,
2089 CGS.cost_group_id,
2090 MMT.organization_id,
2091 MMT.transfer_organization_id,
2092 NVL(MMT.intransit_account, MIP.intransit_inv_account),
2093 RT.shipment_line_id;
2094
2095 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
2096 THEN
2097 FND_MSG_PUB.Add_Exc_Msg(
2098 p_pkg_name => G_PKG_NAME,
2099 p_procedure_name => l_api_name,
2100 p_error_text => l_stmt_num||': Calculated '||SQL%ROWCOUNT||
2101 ' rolled back intransit shipment quantities'||
2102 ' going out of the current organization'
2103 );
2104 END IF;
2105 END IF; -- p_valuation_date IS NOT NULL
2106 END IF; -- p_shipment = 1
2107 ELSE -- p_detail <> 1
2108 -- This calculation is very similar to the shipment line level calculation
2109 -- (stmt 10-100). The difference is that the join to RSL, RSH and RT is
2110 -- avoided when possible, resulting in a better performance
2111
2112 -- All intransit calculations are for quantities that are related
2113 -- to p_organization_id, but not neccessarily owned by it. This is
2114 -- necessary for the Intransit Valuation Report.
2115
2116 -- Calculate intransit quantity coming into this organization
2117 IF p_receipt = 1
2118 THEN
2119 -- Calculate current intransit quantity coming into this organization
2120 l_stmt_num := 110;
2121 INSERT
2122 INTO cst_inv_qty_temp(
2123 qty_source,
2124 organization_id,
2125 inventory_item_id,
2126 category_id,
2127 revision,
2128 cost_type_id,
2129 cost_group_id,
2130 from_organization_id,
2131 to_organization_id,
2132 rollback_qty,
2133 intransit_inv_account
2134 )
2135 SELECT 6,-- CURRENT_INTRANSIT
2136 MS.intransit_owning_org_id,
2137 ITEMS.inventory_item_id,
2138 ITEMS.category_id,
2139 MS.item_revision,
2140 ITEMS.cost_type_id,
2141 CGS.cost_group_id,
2142 MS.from_organization_id,
2143 MS.to_organization_id,
2144 -- quantity is always expressed in the primary unit of measure
2145 -- of the intransit owning organization
2146 SUM(
2147 DECODE(
2148 MS.intransit_owning_org_id,
2149 MS.from_organization_id,
2150 inv_convert.inv_um_convert(
2151 MS.item_id,NULL,MS.quantity,NULL,NULL,
2152 MS.unit_of_measure,MSI_FROM.primary_unit_of_measure
2153 ),
2154 MS.to_org_primary_quantity
2155 )
2156 ),
2157 NVL(
2158 MMT.intransit_account,
2159 NVL(MIP.intransit_inv_account,MP.intransit_inv_account)
2160 )
2161 FROM mtl_supply MS,
2162 cst_item_list_temp ITEMS,
2163 cst_cg_list_temp CGS,
2164 mtl_parameters MP,
2165 mtl_interorg_parameters MIP,
2166 mtl_material_transactions MMT,
2167 rcv_shipment_lines RSL,
2168 mtl_system_items MSI_FROM
2169 WHERE MS.to_organization_id = p_organization_id
2170 /* AND MS.intransit_owning_org_id = p_organization_id */ /* Bug 5664736 */
2171 AND MS.intransit_owning_org_id = DECODE(NVL(p_own,-1),1,p_organization_id,MS.intransit_owning_org_id)
2172 AND MS.item_id = ITEMS.inventory_item_id
2173 AND MS.supply_type_code IN ('SHIPMENT','RECEIVING')
2174 AND MS.destination_type_code = 'INVENTORY'
2175 AND NVL(MS.cost_group_id,MP.default_cost_group_id) = CGS.cost_group_id
2176 AND MP.organization_id = MS.intransit_owning_org_id
2180 AND MIP.to_organization_id (+) = MS.to_organization_id
2177 AND RSL.shipment_line_id = MS.shipment_line_id
2178 AND MMT.transaction_id (+) = RSL.mmt_transaction_id
2179 AND MIP.from_organization_id (+) = MS.from_organization_id
2181 AND MIP.fob_point (+) =
2182 DECODE(
2183 MS.intransit_owning_org_id,
2184 MS.from_organization_id, 2,
2185 MS.to_organization_id, 1
2186 )
2187 AND MSI_FROM.inventory_item_id = MS.item_id
2188 AND MSI_FROM.organization_id = MS.from_organization_id
2189 GROUP
2190 BY MS.intransit_owning_org_id,
2191 ITEMS.inventory_item_id,
2192 ITEMS.category_id,
2193 MS.item_revision,
2194 ITEMS.cost_type_id,
2195 CGS.cost_group_id,
2196 MS.from_organization_id,
2197 MS.to_organization_id,
2198 NVL(
2199 MMT.intransit_account,
2200 NVL(MIP.intransit_inv_account,MP.intransit_inv_account)
2201 );
2202
2203 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
2204 THEN
2205 FND_MSG_PUB.Add_Exc_Msg(
2206 p_pkg_name => G_PKG_NAME,
2207 p_procedure_name => l_api_name,
2208 p_error_text => l_stmt_num||': Calculated '||SQL%ROWCOUNT||
2209 ' current intransit quantities coming into the'||
2210 ' current organization'
2211 );
2212 END IF;
2213
2214 -- Calculate uncosted intransit shipment quantities coming into this
2215 -- organization
2216 IF NVL(p_unvalued_txns,-1) <> 1 THEN
2217 IF l_uncosted_txn_count > 0 THEN --BUG#6109468-FPBUG5606455
2218 l_stmt_num := 120;
2219 INSERT
2220 INTO cst_inv_qty_temp(
2221 qty_source,
2222 organization_id,
2223 inventory_item_id,
2224 category_id,
2225 revision,
2226 cost_type_id,
2227 cost_group_id,
2228 from_organization_id,
2229 to_organization_id,
2230 rollback_qty,
2231 intransit_inv_account
2232 )
2233 SELECT 7, -- UNCOSTED_INTRANSIT
2234 DECODE(
2235 NVL(MMT.fob_point,MIP.fob_point),
2236 1,MMT.transfer_organization_id,
2237 2,MMT.organization_id
2238 ),
2239 ITEMS.inventory_item_id,
2240 ITEMS.category_id,
2241 MMT.revision,
2242 ITEMS.cost_type_id,
2243 CGS.cost_group_id,
2244 MMT.organization_id,
2245 MMT.transfer_organization_id,
2246 SUM(
2247 DECODE(
2248 NVL(MMT.fob_point,MIP.fob_point),
2249 1,
2250 inv_convert.inv_um_convert(
2251 MMT.inventory_item_id,NULL,MMT.transaction_quantity,
2252 MMT.transaction_uom,MSI_TO.primary_uom_code,NULL,NULL
2253 ),
2254 2,
2255 MMT.primary_quantity
2256 )
2257 ),
2258 NVL(MMT.intransit_account,MIP.intransit_inv_account)
2259 FROM mtl_material_transactions MMT,
2260 cst_item_list_temp ITEMS,
2261 cst_cg_list_temp CGS,
2262 mtl_interorg_parameters MIP,
2263 mtl_system_items MSI_TO
2264 WHERE MMT.transfer_organization_id = p_organization_id
2265 AND MMT.transaction_action_id = 21
2266 AND MMT.costed_flag IN ('N','E')
2267 AND MMT.inventory_item_id = ITEMS.inventory_item_id
2268 AND DECODE(
2269 NVL(MMT.fob_point,MIP.fob_point),
2270 1,MMT.transfer_cost_group_id,
2271 2,MMT.cost_group_id
2272 ) =
2273 CGS.cost_group_id
2274 AND MIP.to_organization_id = MMT.transfer_organization_id
2275 AND MIP.from_organization_id = MMT.organization_id
2276 AND MSI_TO.organization_id = MMT.transfer_organization_id
2277 AND MSI_TO.inventory_item_id = MMT.inventory_item_id
2278 GROUP
2279 BY DECODE(
2280 NVL(MMT.fob_point,MIP.fob_point),
2281 1,MMT.transfer_organization_id,
2282 2,MMT.organization_id
2283 ),
2284 ITEMS.inventory_item_id,
2285 ITEMS.category_id,
2286 MMT.revision,
2287 ITEMS.cost_type_id,
2288 CGS.cost_group_id,
2289 MMT.organization_id,
2290 MMT.transfer_organization_id,
2291 NVL(MMT.intransit_account,MIP.intransit_inv_account);
2292
2293 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
2294 THEN
2295 FND_MSG_PUB.Add_Exc_Msg(
2296 p_pkg_name => G_PKG_NAME,
2297 p_procedure_name => l_api_name,
2298 p_error_text => l_stmt_num||': Calculated '||SQL%ROWCOUNT||
2302 END IF;
2299 ' uncosted intransit shipment quantities coming'||
2300 ' into the current organization'
2301 );
2303
2304 -- Calculate uncosted intransit receipt quantities coming into this
2305 -- organization
2306 l_stmt_num := 130;
2307 INSERT
2308 INTO cst_inv_qty_temp(
2309 qty_source,
2310 organization_id,
2311 inventory_item_id,
2312 category_id,
2313 revision,
2314 cost_type_id,
2315 cost_group_id,
2316 from_organization_id,
2317 to_organization_id,
2318 rollback_qty,
2319 intransit_inv_account
2320 )
2321 SELECT 7, -- UNCOSTED_INTRANSIT
2322 DECODE(
2323 NVL(MMT.fob_point,MIP.fob_point),
2324 1,MMT.organization_id,
2325 2,MMT.transfer_organization_id
2326 ),
2327 ITEMS.inventory_item_id,
2328 ITEMS.category_id,
2329 MMT.revision,
2330 ITEMS.cost_type_id,
2331 CGS.cost_group_id,
2332 MMT.transfer_organization_id,
2333 MMT.organization_id,
2334 SUM(
2335 DECODE(
2336 NVL(MMT.fob_point,MIP.fob_point),
2337 1,
2338 MMT.primary_quantity,
2339 2,
2340 inv_convert.inv_um_convert(
2341 MMT.inventory_item_id,NULL,MMT.transaction_quantity,
2342 MMT.transaction_uom,MSI_FROM.primary_uom_code,NULL,NULL
2343 )
2344 )
2345 ),
2346 NVL(MMT.intransit_account, MIP.intransit_inv_account)
2347 FROM mtl_material_transactions MMT,
2348 cst_item_list_temp ITEMS,
2349 cst_cg_list_temp CGS,
2350 mtl_interorg_parameters MIP,
2351 mtl_system_items MSI_FROM
2352 WHERE MMT.organization_id = p_organization_id
2353 AND MMT.transaction_action_id = 12
2354 AND MMT.costed_flag IN ('N', 'E')
2355 AND MMT.inventory_item_id = ITEMS.inventory_item_id
2356 AND DECODE(
2357 NVL(MMT.fob_point,MIP.fob_point),
2358 1,MMT.cost_group_id,
2359 2,MMT.transfer_cost_group_id
2360 ) =
2361 CGS.cost_group_id
2362 AND MIP.to_organization_id = MMT.organization_id
2363 AND MIP.from_organization_id = MMT.transfer_organization_id
2364 AND MSI_FROM.organization_id = MMT.transfer_organization_id
2365 AND MSI_FROM.inventory_item_id = MMT.inventory_item_id
2366 GROUP
2367 BY DECODE(
2368 NVL(MMT.fob_point,MIP.fob_point),
2369 1,MMT.organization_id,
2370 2,MMT.transfer_organization_id
2371 ),
2372 ITEMS.inventory_item_id,
2373 ITEMS.category_id,
2374 MMT.revision,
2375 ITEMS.cost_type_id,
2376 CGS.cost_group_id,
2377 MMT.organization_id,
2378 MMT.transfer_organization_id,
2379 NVL(MMT.intransit_account, MIP.intransit_inv_account);
2380
2381 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
2382 THEN
2383 FND_MSG_PUB.Add_Exc_Msg(
2384 p_pkg_name => G_PKG_NAME,
2385 p_procedure_name => l_api_name,
2386 p_error_text => l_stmt_num||': Calculated '||SQL%ROWCOUNT||
2387 ' uncosted intransit receipt quantities coming'||
2388 ' into the current org'
2389 );
2390 END IF;
2391 END IF; --l_uncosted_txn_count>0
2392 END IF; -- NVL(p_unvalued_txns,-1) <> 1
2393
2394 IF p_valuation_date IS NOT NULL
2395 THEN
2396 -- Calculate rollback intransit shipment quantities coming into this
2397 -- organization. The code for this calculation is similar to the one used
2398 -- to calculate uncosted intransit shipment quantities coming into this
2399 -- organization. The only difference is instead of checking for
2400 -- costed_flag in ('N','E'), we check for costed_flag is NULL and
2401 -- transaction_date > p_valuation_date
2402 l_stmt_num := 140;
2403 INSERT
2404 INTO cst_inv_qty_temp(
2405 qty_source,
2406 organization_id,
2407 inventory_item_id,
2408 category_id,
2409 revision,
2410 cost_type_id,
2411 cost_group_id,
2412 from_organization_id,
2413 to_organization_id,
2414 rollback_qty,
2415 intransit_inv_account
2416 )
2417 SELECT 8, -- ROLLBACK_INTRANSIT
2418 DECODE(
2419 NVL(MMT.fob_point,MIP.fob_point),
2423 ITEMS.inventory_item_id,
2420 1,MMT.transfer_organization_id,
2421 2,MMT.organization_id
2422 ),
2424 ITEMS.category_id,
2425 MMT.revision,
2426 ITEMS.cost_type_id,
2427 CGS.cost_group_id,
2428 MMT.organization_id,
2429 MMT.transfer_organization_id,
2430 SUM(
2431 DECODE(
2432 NVL(MMT.fob_point,MIP.fob_point),
2433 1,
2434 inv_convert.inv_um_convert(
2435 MMT.inventory_item_id,NULL,MMT.transaction_quantity,
2436 MMT.transaction_uom,MSI_TO.primary_uom_code,NULL,NULL
2437 ),
2438 2,
2439 MMT.primary_quantity
2440 )
2441 ),
2442 NVL(MMT.intransit_account,MIP.intransit_inv_account)
2443 FROM mtl_material_transactions MMT,
2444 cst_item_list_temp ITEMS,
2445 cst_cg_list_temp CGS,
2446 mtl_interorg_parameters MIP,
2447 mtl_system_items MSI_TO,
2448 mtl_transaction_types MTT
2449 WHERE MMT.transfer_organization_id = p_organization_id
2450 AND MMT.transaction_action_id = 21
2451 AND MMT.costed_flag IS NULL
2452 AND MMT.transaction_date > p_valuation_date
2453 AND MMT.inventory_item_id = ITEMS.inventory_item_id
2454 AND DECODE(
2455 NVL(MMT.fob_point,MIP.fob_point),
2456 1,MMT.transfer_cost_group_id,
2457 2,MMT.cost_group_id
2458 ) =
2459 CGS.cost_group_id
2460 AND MIP.to_organization_id = MMT.transfer_organization_id
2461 AND MIP.from_organization_id = MMT.organization_id
2462 AND MSI_TO.organization_id = MMT.transfer_organization_id
2463 AND MSI_TO.inventory_item_id = MMT.inventory_item_id
2464 AND MTT.transaction_action_id = MMT.transaction_action_id
2465 AND MTT.transaction_source_type_id = MMT.transaction_source_type_id
2466 AND MTT.transaction_type_id = MMT.transaction_type_id
2467 GROUP
2468 BY DECODE(
2469 NVL(MMT.fob_point,MIP.fob_point),
2470 1,MMT.transfer_organization_id,
2471 2,MMT.organization_id
2472 ),
2473 ITEMS.inventory_item_id,
2474 ITEMS.category_id,
2475 MMT.revision,
2476 ITEMS.cost_type_id,
2477 CGS.cost_group_id,
2478 MMT.organization_id,
2479 MMT.transfer_organization_id,
2480 NVL(MMT.intransit_account,MIP.intransit_inv_account);
2481
2482 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
2483 THEN
2484 FND_MSG_PUB.Add_Exc_Msg(
2485 p_pkg_name => G_PKG_NAME,
2486 p_procedure_name => l_api_name,
2487 p_error_text => l_stmt_num||': Calculated '||SQL%ROWCOUNT||
2488 ' rolled back intransit shipment quantities'||
2489 ' coming into the current org'
2490 );
2491 END IF;
2492
2493 -- Calculate rollback intransit receipt quantities coming into this
2494 -- organization. The code for this calculation is similar to the one
2495 -- used to calculate uncosted intransit receipt quantities coming into
2496 -- this organization. The only difference is instead of checking for
2497 -- costed_flag in ('N','E'), we check for costed_flag is NULL and
2498 -- transaction_date > p_valuation_date
2499 l_stmt_num := 150;
2500 INSERT
2501 INTO cst_inv_qty_temp(
2502 qty_source,
2503 organization_id,
2504 inventory_item_id,
2505 category_id,
2506 revision,
2507 cost_type_id,
2508 cost_group_id,
2509 from_organization_id,
2510 to_organization_id,
2511 rollback_qty,
2512 intransit_inv_account
2513 )
2514 SELECT 8, -- ROLLBACK_INTRANSIT
2515 DECODE(
2516 NVL(MMT.fob_point,MIP.fob_point),
2517 1,MMT.organization_id,
2518 2,MMT.transfer_organization_id
2519 ),
2520 ITEMS.inventory_item_id,
2521 ITEMS.category_id,
2522 MMT.revision,
2523 ITEMS.cost_type_id,
2524 CGS.cost_group_id,
2525 MMT.transfer_organization_id,
2526 MMT.organization_id,
2527 SUM(
2528 DECODE(
2529 NVL(MMT.fob_point,MIP.fob_point),
2530 1,
2531 MMT.primary_quantity,
2532 2,
2533 inv_convert.inv_um_convert(
2534 MMT.inventory_item_id,NULL,MMT.transaction_quantity,
2535 MMT.transaction_uom,MSI_FROM.primary_uom_code,NULL,NULL
2536 )
2537 )
2538 ),
2542 cst_cg_list_temp CGS,
2539 NVL(MMT.intransit_account, MIP.intransit_inv_account)
2540 FROM mtl_material_transactions MMT,
2541 cst_item_list_temp ITEMS,
2543 mtl_interorg_parameters MIP,
2544 mtl_system_items MSI_FROM,
2545 mtl_transaction_types MTT
2546 WHERE MMT.organization_id = p_organization_id
2547 AND MMT.transaction_action_id = 12
2548 AND MMT.costed_flag IS NULL
2549 AND MMT.transaction_date > p_valuation_date
2550 AND MMT.inventory_item_id = ITEMS.inventory_item_id
2551 AND DECODE(
2552 NVL(MMT.fob_point,MIP.fob_point),
2553 1,MMT.cost_group_id,
2554 2,MMT.transfer_cost_group_id
2555 ) =
2556 CGS.cost_group_id
2557 AND MIP.to_organization_id = MMT.organization_id
2558 AND MIP.from_organization_id = MMT.transfer_organization_id
2559 AND MSI_FROM.organization_id = MMT.transfer_organization_id
2560 AND MSI_FROM.inventory_item_id = MMT.inventory_item_id
2561 AND MTT.transaction_action_id = MMT.transaction_action_id
2562 AND MTT.transaction_source_type_id = MMT.transaction_source_type_id
2563 AND MTT.transaction_type_id = MMT.transaction_type_id
2564 GROUP
2565 BY DECODE(
2566 NVL(MMT.fob_point,MIP.fob_point),
2567 1,MMT.organization_id,
2568 2,MMT.transfer_organization_id
2569 ),
2570 ITEMS.inventory_item_id,
2571 ITEMS.category_id,
2572 MMT.revision,
2573 ITEMS.cost_type_id,
2574 CGS.cost_group_id,
2575 MMT.organization_id,
2576 MMT.transfer_organization_id,
2577 NVL(MMT.intransit_account, MIP.intransit_inv_account);
2578
2579 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
2580 THEN
2581 FND_MSG_PUB.Add_Exc_Msg(
2582 p_pkg_name => G_PKG_NAME,
2583 p_procedure_name => l_api_name,
2584 p_error_text => l_stmt_num||': Calculated '||SQL%ROWCOUNT||
2585 ' rolled back intransit shipment quantities'||
2586 ' coming into the current organization'
2587 );
2588 END IF;
2589 END IF; -- p_valuation_date IS NOT NULL
2590 END IF; -- p_receipt = 1
2591
2592 IF p_shipment = 1 THEN
2593 -- Calculate current intransit quantity going out of this organization
2594 -- The code for this calculation is similar to the one used to calculate
2595 -- current intransit quantities coming into this organization. The only
2596 -- difference is instead of checking for MS.to_organization_id =
2597 -- p_organization_id, we check for MS.from_organization_id =
2598 -- p_organization_id
2599 l_stmt_num := 60;
2600 INSERT
2601 INTO cst_inv_qty_temp(
2602 qty_source,
2603 organization_id,
2604 inventory_item_id,
2605 category_id,
2606 revision,
2607 cost_type_id,
2608 cost_group_id,
2609 from_organization_id,
2610 to_organization_id,
2611 rollback_qty,
2612 intransit_inv_account
2613 )
2614 SELECT 6,-- CURRENT_INTRANSIT
2615 MS.intransit_owning_org_id,
2616 ITEMS.inventory_item_id,
2617 ITEMS.category_id,
2618 MS.item_revision,
2619 ITEMS.cost_type_id,
2620 CGS.cost_group_id,
2621 MS.from_organization_id,
2622 MS.to_organization_id,
2623 SUM(
2624 DECODE(
2625 MS.intransit_owning_org_id,
2626 MS.from_organization_id,
2627 inv_convert.inv_um_convert(
2628 MS.item_id,NULL,MS.quantity,NULL,NULL,
2629 MS.unit_of_measure,MSI_FROM.primary_unit_of_measure
2630 ),
2631 MS.to_org_primary_quantity
2632 )
2633 ),
2634 NVL(
2635 MMT.intransit_account,
2636 NVL(MIP.intransit_inv_account,MP.intransit_inv_account)
2637 )
2638 FROM mtl_supply MS,
2639 cst_item_list_temp ITEMS,
2640 cst_cg_list_temp CGS,
2641 mtl_parameters MP,
2642 mtl_interorg_parameters MIP,
2643 mtl_material_transactions MMT,
2644 rcv_shipment_lines RSL,
2645 mtl_system_items MSI_FROM
2646 WHERE MS.from_organization_id = p_organization_id
2647 /* AND MS.intransit_owning_org_id = p_organization_id */ /* Bug 5664736 */
2648 AND MS.intransit_owning_org_id = DECODE(NVL(p_own,-1),1,p_organization_id,MS.intransit_owning_org_id)
2649 AND MS.item_id = ITEMS.inventory_item_id
2650 AND MS.supply_type_code IN ('SHIPMENT','RECEIVING')
2651 AND MS.destination_type_code = 'INVENTORY'
2652 AND NVL(MS.cost_group_id,MP.default_cost_group_id) = CGS.cost_group_id
2653 AND MP.organization_id = MS.intransit_owning_org_id
2657 AND MIP.to_organization_id (+) = MS.to_organization_id
2654 AND RSL.shipment_line_id = MS.shipment_line_id
2655 AND MMT.transaction_id (+) = RSL.mmt_transaction_id
2656 AND MIP.from_organization_id (+) = MS.from_organization_id
2658 AND MIP.fob_point (+) =
2659 DECODE(
2660 MS.intransit_owning_org_id,
2661 MS.from_organization_id, 2,
2662 MS.to_organization_id, 1)
2663 AND MSI_FROM.inventory_item_id = MS.item_id
2664 AND MSI_FROM.organization_id = MS.from_organization_id
2665 GROUP
2666 BY MS.intransit_owning_org_id,
2667 ITEMS.inventory_item_id,
2668 ITEMS.category_id,
2669 MS.item_revision,
2670 ITEMS.cost_type_id,
2671 CGS.cost_group_id,
2672 MS.from_organization_id,
2673 MS.to_organization_id,
2674 NVL(
2675 MMT.intransit_account,
2676 NVL(MIP.intransit_inv_account,MP.intransit_inv_account)
2677 );
2678
2679 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
2680 THEN
2681 FND_MSG_PUB.Add_Exc_Msg(
2682 p_pkg_name => G_PKG_NAME,
2683 p_procedure_name => l_api_name,
2684 p_error_text => l_stmt_num||': Calculated '||SQL%ROWCOUNT||
2685 ' current intransit quantities going out of the'||
2686 ' current organization'
2687 );
2688 END IF;
2689
2690 IF NVL(p_unvalued_txns,-1) <> 1 THEN
2691 -- Calculate uncosted intransit shipment quantities going out of this
2692 -- organization. The code for this calculation is similar to the one used
2693 -- to calculate uncosted intransit shipment quantities coming into this
2694 -- organization. The only difference is instead of checking for
2695 -- MMT.transfer_organization_id = p_organization_id, we check for
2696 -- MMT.organization_id = p_organization_id
2697 IF l_uncosted_txn_count >0 THEN --BUG6109468-FPBUG5606455
2698 l_stmt_num := 70;
2699 INSERT
2700 INTO cst_inv_qty_temp(
2701 qty_source,
2702 organization_id,
2703 inventory_item_id,
2704 category_id,
2705 revision,
2706 cost_type_id,
2707 cost_group_id,
2708 from_organization_id,
2709 to_organization_id,
2710 rollback_qty,
2711 intransit_inv_account
2712 )
2713 SELECT 7, -- UNCOSTED_INTRANSIT
2714 DECODE(
2715 NVL(MMT.fob_point,MIP.fob_point),
2716 1,MMT.transfer_organization_id,
2717 2,MMT.organization_id
2718 ),
2719 ITEMS.inventory_item_id,
2720 ITEMS.category_id,
2721 MMT.revision,
2722 ITEMS.cost_type_id,
2723 CGS.cost_group_id,
2724 MMT.organization_id,
2725 MMT.transfer_organization_id,
2726 SUM(
2727 DECODE(
2728 NVL(MMT.fob_point,MIP.fob_point),
2729 1,
2730 inv_convert.inv_um_convert(
2731 MMT.inventory_item_id,NULL,MMT.transaction_quantity,
2732 MMT.transaction_uom,MSI_TO.primary_uom_code,NULL,NULL
2733 ),
2734 2,
2735 MMT.primary_quantity
2736 )
2737 ),
2738 NVL(MMT.intransit_account,MIP.intransit_inv_account)
2739 FROM mtl_material_transactions MMT,
2740 cst_item_list_temp ITEMS,
2741 cst_cg_list_temp CGS,
2742 mtl_interorg_parameters MIP,
2743 mtl_system_items MSI_TO
2744 WHERE MMT.organization_id = p_organization_id
2745 AND MMT.transaction_action_id = 21
2746 AND MMT.costed_flag IN ('N','E')
2747 AND MMT.inventory_item_id = ITEMS.inventory_item_id
2748 AND DECODE(
2749 NVL(MMT.fob_point,MIP.fob_point),
2750 1,MMT.transfer_cost_group_id,
2751 2,MMT.cost_group_id
2752 ) =
2753 CGS.cost_group_id
2754 AND MIP.to_organization_id = MMT.transfer_organization_id
2755 AND MIP.from_organization_id = MMT.organization_id
2756 AND MSI_TO.organization_id = MMT.transfer_organization_id
2757 AND MSI_TO.inventory_item_id = MMT.inventory_item_id
2758 GROUP
2759 BY DECODE(
2760 NVL(MMT.fob_point,MIP.fob_point),
2761 1,MMT.transfer_organization_id,
2762 2,MMT.organization_id
2763 ),
2764 ITEMS.inventory_item_id,
2765 ITEMS.category_id,
2766 MMT.revision,
2767 ITEMS.cost_type_id,
2768 CGS.cost_group_id,
2769 MMT.organization_id,
2770 MMT.transfer_organization_id,
2771 NVL(MMT.intransit_account,MIP.intransit_inv_account);
2772
2776 p_pkg_name => G_PKG_NAME,
2773 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
2774 THEN
2775 FND_MSG_PUB.Add_Exc_Msg(
2777 p_procedure_name => l_api_name,
2778 p_error_text => l_stmt_num||': Calculated '||SQL%ROWCOUNT||
2779 ' uncosted intransit shipment quantities going out'||
2780 ' of the current organization'
2781 );
2782 END IF;
2783
2784 -- Calculate uncosted intransit receipt quantities going out of this
2785 -- organization. The code for this calculation is similar to the one used
2786 -- to calculate uncosted intransit receipt quantities going out of this
2787 -- organization. The only difference is instead of checking for
2788 -- MMT.organization_id = p_organization_id, we check for
2789 -- MMT.organization_id = p_organization_id
2790 l_stmt_num := 80;
2791 INSERT
2792 INTO cst_inv_qty_temp(
2793 qty_source,
2794 organization_id,
2795 inventory_item_id,
2796 category_id,
2797 revision,
2798 cost_type_id,
2799 cost_group_id,
2800 from_organization_id,
2801 to_organization_id,
2802 rollback_qty,
2803 intransit_inv_account
2804 )
2805 SELECT 7, -- UNCOSTED_INTRANSIT
2806 DECODE(
2807 NVL(MMT.fob_point,MIP.fob_point),
2808 1,MMT.organization_id,
2809 2,MMT.transfer_organization_id
2810 ),
2811 ITEMS.inventory_item_id,
2812 ITEMS.category_id,
2813 MMT.revision,
2814 ITEMS.cost_type_id,
2815 CGS.cost_group_id,
2816 MMT.transfer_organization_id,
2817 MMT.organization_id,
2818 SUM(
2819 DECODE(
2820 NVL(MMT.fob_point,MIP.fob_point),
2821 1,
2822 MMT.primary_quantity,
2823 2,
2824 inv_convert.inv_um_convert(
2825 MMT.inventory_item_id,NULL,MMT.transaction_quantity,
2826 MMT.transaction_uom,MSI_FROM.primary_uom_code,NULL,NULL
2827 )
2828 )
2829 ),
2830 NVL(MMT.intransit_account, MIP.intransit_inv_account)
2831 FROM mtl_material_transactions MMT,
2832 cst_item_list_temp ITEMS,
2833 cst_cg_list_temp CGS,
2834 mtl_interorg_parameters MIP,
2835 mtl_system_items MSI_FROM
2836 WHERE MMT.transfer_organization_id = p_organization_id
2837 AND MMT.transaction_action_id = 12
2838 AND MMT.costed_flag IN ('N', 'E')
2839 AND MMT.inventory_item_id = ITEMS.inventory_item_id
2840 AND DECODE(
2841 NVL(MMT.fob_point,MIP.fob_point),
2842 1,MMT.cost_group_id,
2843 2,MMT.transfer_cost_group_id
2844 ) =
2845 CGS.cost_group_id
2846 AND MIP.to_organization_id = MMT.organization_id
2847 AND MIP.from_organization_id = MMT.transfer_organization_id
2848 AND MSI_FROM.organization_id = MMT.transfer_organization_id
2849 AND MSI_FROM.inventory_item_id = MMT.inventory_item_id
2850 GROUP
2851 BY DECODE(
2852 NVL(MMT.fob_point,MIP.fob_point),
2853 1,MMT.organization_id,
2854 2,MMT.transfer_organization_id
2855 ),
2856 ITEMS.inventory_item_id,
2857 ITEMS.category_id,
2858 MMT.revision,
2859 ITEMS.cost_type_id,
2860 CGS.cost_group_id,
2861 MMT.organization_id,
2862 MMT.transfer_organization_id,
2863 NVL(MMT.intransit_account, MIP.intransit_inv_account);
2864
2865 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
2866 THEN
2867 FND_MSG_PUB.Add_Exc_Msg(
2868 p_pkg_name => G_PKG_NAME,
2869 p_procedure_name => l_api_name,
2870 p_error_text => l_stmt_num||': Calculated '||SQL%ROWCOUNT||
2871 ' uncosted intransit receipt quantities going out'||
2872 ' of the current organization'
2873 );
2874 END IF;
2875 END IF; --l_uncosted_txn_count > 0
2876 END IF; -- NVL(p_unvalued_txns,-1) <> 1
2877
2878 -- Calculate rollback intransit shipment quantities going out of this
2879 -- organization. The code for this calculation is similar to the one
2880 -- used to calculate uncosted intransit shipment quantities going out
2881 -- of this organization. The only difference is instead of checking for
2882 -- costed_flag in ('N','E'), we check for costed_flag is NULL and
2883 -- transaction_date > p_valuation_date
2884 IF p_valuation_date IS NOT NULL
2885 THEN
2886 l_stmt_num := 90;
2887 INSERT
2888 INTO cst_inv_qty_temp(
2889 qty_source,
2893 revision,
2890 organization_id,
2891 inventory_item_id,
2892 category_id,
2894 cost_type_id,
2895 cost_group_id,
2896 from_organization_id,
2897 to_organization_id,
2898 rollback_qty,
2899 intransit_inv_account
2900 )
2901 SELECT 8, -- ROLLBACK_INTRANSIT
2902 DECODE(
2903 NVL(MMT.fob_point,MIP.fob_point),
2904 1,MMT.transfer_organization_id,
2905 2,MMT.organization_id
2906 ),
2907 ITEMS.inventory_item_id,
2908 ITEMS.category_id,
2909 MMT.revision,
2910 ITEMS.cost_type_id,
2911 CGS.cost_group_id,
2912 MMT.organization_id,
2913 MMT.transfer_organization_id,
2914 SUM(
2915 DECODE(
2916 NVL(MMT.fob_point,MIP.fob_point),
2917 1,
2918 inv_convert.inv_um_convert(
2919 MMT.inventory_item_id,NULL,MMT.transaction_quantity,
2920 MMT.transaction_uom,MSI_TO.primary_uom_code,NULL,NULL
2921 ),
2922 2,
2923 MMT.primary_quantity
2924 )
2925 ),
2926 NVL(MMT.intransit_account,MIP.intransit_inv_account)
2927 FROM mtl_material_transactions MMT,
2928 cst_item_list_temp ITEMS,
2929 cst_cg_list_temp CGS,
2930 mtl_interorg_parameters MIP,
2931 mtl_system_items MSI_TO,
2932 mtl_transaction_types MTT
2933 WHERE MMT.organization_id = p_organization_id
2934 AND MMT.transaction_action_id = 21
2935 AND MMT.costed_flag IS NULL
2936 AND MMT.transaction_date > p_valuation_date
2937 AND MMT.inventory_item_id = ITEMS.inventory_item_id
2938 AND DECODE(
2939 NVL(MMT.fob_point,MIP.fob_point),
2940 1,MMT.transfer_cost_group_id,
2941 2,MMT.cost_group_id
2942 ) =
2943 CGS.cost_group_id
2944 AND MIP.to_organization_id = MMT.transfer_organization_id
2945 AND MIP.from_organization_id = MMT.organization_id
2946 AND MSI_TO.organization_id = MMT.transfer_organization_id
2947 AND MSI_TO.inventory_item_id = MMT.inventory_item_id
2948 AND MTT.transaction_action_id = MMT.transaction_action_id
2949 AND MTT.transaction_source_type_id = MMT.transaction_source_type_id
2950 AND MTT.transaction_type_id = MMT.transaction_type_id
2951 GROUP
2952 BY DECODE(
2953 NVL(MMT.fob_point,MIP.fob_point),
2954 1,MMT.transfer_organization_id,
2955 2,MMT.organization_id
2956 ),
2957 ITEMS.inventory_item_id,
2958 ITEMS.category_id,
2959 MMT.revision,
2960 ITEMS.cost_type_id,
2961 CGS.cost_group_id,
2962 MMT.organization_id,
2963 MMT.transfer_organization_id,
2964 NVL(MMT.intransit_account,MIP.intransit_inv_account);
2965
2966 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
2967 THEN
2968 FND_MSG_PUB.Add_Exc_Msg(
2969 p_pkg_name => G_PKG_NAME,
2970 p_procedure_name => l_api_name,
2971 p_error_text => l_stmt_num||': Calculated '||SQL%ROWCOUNT||
2972 ' rolled back intransit shipment quantities'||
2973 ' going out of the current organization'
2974 );
2975 END IF;
2976
2977 -- Calculate rollback intransit receipt quantities going out of this
2978 -- organization. The code for this calculation is similar to the one
2979 -- used to calculate uncosted intransit receipt quantities going out of
2980 -- this organization. The only difference is instead of checking for
2981 -- costed_flag in ('N','E'), we check for costed_flag is NULL and
2982 -- transaction_date > p_valuation_date
2983 l_stmt_num := 200;
2984 INSERT
2985 INTO cst_inv_qty_temp(
2986 qty_source,
2987 organization_id,
2988 inventory_item_id,
2989 category_id,
2990 revision,
2991 cost_type_id,
2992 cost_group_id,
2993 from_organization_id,
2994 to_organization_id,
2995 rollback_qty,
2996 intransit_inv_account
2997 )
2998 SELECT 8, -- ROLLBACK_INTRANSIT
2999 DECODE(
3000 NVL(MMT.fob_point,MIP.fob_point),
3001 1,MMT.organization_id,
3002 2,MMT.transfer_organization_id
3003 ),
3004 ITEMS.inventory_item_id,
3005 ITEMS.category_id,
3006 MMT.revision,
3007 ITEMS.cost_type_id,
3008 CGS.cost_group_id,
3012 DECODE(
3009 MMT.transfer_organization_id,
3010 MMT.organization_id,
3011 SUM(
3013 NVL(MMT.fob_point,MIP.fob_point),
3014 1,
3015 MMT.primary_quantity,
3016 2,
3017 inv_convert.inv_um_convert(
3018 MMT.inventory_item_id,NULL,MMT.transaction_quantity,
3019 MMT.transaction_uom,MSI_FROM.primary_uom_code,NULL,NULL
3020 )
3021 )
3022 ),
3023 NVL(MMT.intransit_account, MIP.intransit_inv_account)
3024 FROM mtl_material_transactions MMT,
3025 cst_item_list_temp ITEMS,
3026 cst_cg_list_temp CGS,
3027 mtl_interorg_parameters MIP,
3028 mtl_system_items MSI_FROM,
3029 mtl_transaction_types MTT
3030 WHERE MMT.transfer_organization_id = p_organization_id
3031 AND MMT.transaction_action_id = 12
3032 AND MMT.costed_flag IS NULL
3033 AND MMT.transaction_date > p_valuation_date
3034 AND MMT.inventory_item_id = ITEMS.inventory_item_id
3035 AND DECODE(
3036 NVL(MMT.fob_point,MIP.fob_point),
3037 1,MMT.cost_group_id,
3038 2,MMT.transfer_cost_group_id
3039 ) =
3040 CGS.cost_group_id
3041 AND MIP.to_organization_id = MMT.organization_id
3042 AND MIP.from_organization_id = MMT.transfer_organization_id
3043 AND MSI_FROM.organization_id = MMT.transfer_organization_id
3044 AND MSI_FROM.inventory_item_id = MMT.inventory_item_id
3045 AND MTT.transaction_action_id = MMT.transaction_action_id
3046 AND MTT.transaction_source_type_id = MMT.transaction_source_type_id
3047 AND MTT.transaction_type_id = MMT.transaction_type_id
3048 GROUP
3049 BY DECODE(
3050 NVL(MMT.fob_point,MIP.fob_point),
3051 1,MMT.organization_id,
3052 2,MMT.transfer_organization_id
3053 ),
3054 ITEMS.inventory_item_id,
3055 ITEMS.category_id,
3056 MMT.revision,
3057 ITEMS.cost_type_id,
3058 CGS.cost_group_id,
3059 MMT.organization_id,
3060 MMT.transfer_organization_id,
3061 NVL(MMT.intransit_account, MIP.intransit_inv_account);
3062
3063 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
3064 THEN
3065 FND_MSG_PUB.Add_Exc_Msg(
3066 p_pkg_name => G_PKG_NAME,
3067 p_procedure_name => l_api_name,
3068 p_error_text => l_stmt_num||': Calculated '||SQL%ROWCOUNT||
3069 ' rolled back intransit shipment quantities'||
3070 ' going out of the current organization'
3071 );
3072 END IF;
3073 END IF; -- p_valuation_date IS NOT NULL
3074 END IF; -- p_shipment = 1
3075 END IF; -- p_detail = 1
3076
3077 x_return_status := FND_API.G_RET_STS_SUCCESS;
3078
3079 EXCEPTION
3080 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3081 ROLLBACK TO Calculate_IntransitQty_PVT;
3082 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3083 WHEN OTHERS THEN
3084 ROLLBACK TO Calculate_IntransitQty_PVT;
3085 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3086 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
3087 THEN
3088 FND_MSG_PUB.Add_Exc_Msg(
3089 p_pkg_name => G_PKG_NAME,
3090 p_procedure_name => l_api_name,
3091 p_error_text => SUBSTR(l_stmt_num||SQLERRM,1,240)
3092 );
3093 END IF;
3094
3095 END Calculate_IntransitQty;
3096
3097 PROCEDURE Calculate_ReceivingQty(
3098 p_api_version IN NUMBER,
3099 p_organization_id IN NUMBER,
3100 p_valuation_date IN DATE,
3101 p_qty_by_revision IN NUMBER,
3102 p_include_period_end IN NUMBER,
3103 x_return_status OUT NOCOPY VARCHAR2
3104 ) IS
3105 l_api_name CONSTANT VARCHAR2(30) := 'Calculate_ReceivingQty';
3106 l_api_version CONSTANT NUMBER := 1.0;
3107 l_msg_level_threshold NUMBER;
3108 l_stmt_num NUMBER := 0;
3109 BEGIN
3110 -- Standard Start of API savepoint
3111 SAVEPOINT Calculate_ReceivingQty_PVT;
3112
3113 -- Check for call compatibility
3114 IF NOT FND_API.Compatible_API_Call(
3115 p_current_version_number => l_api_version,
3116 p_caller_version_number => p_api_version,
3117 p_api_name => l_api_name,
3118 p_pkg_name => G_PKG_NAME
3119 )
3120 THEN
3121 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3122 END IF;
3123
3124 -- Check for message level threshold
3125 l_msg_level_threshold := FND_PROFILE.Value('FND_AS_MSG_LEVEL_THRESHOLD');
3126
3127 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH
3128 THEN
3129 FND_MSG_PUB.Add_Exc_Msg(
3130 p_pkg_name => G_PKG_NAME,
3131 p_procedure_name => l_api_name,
3132 p_error_text => SUBSTR(
3133 l_stmt_num||':'||
3137 p_include_period_end,
3134 p_organization_id||','||
3135 p_valuation_date||','||
3136 p_qty_by_revision||','||
3138 1,
3139 240
3140 )
3141 );
3142 END IF;
3143
3144 -- Calculate current receiving quantity with qty_source = 9
3145 -- Release 12i: Modified to store quantities always by the parent
3146 -- RECEIVE or MATCH transaction. Prior to 12i, quantity was always
3147 -- stored by the rcv_transaction_id stored in MTL_SUPPLY, which was
3148 -- the parent RECEIVE or MATCH with the exception of cases in which
3149 -- there was an Accept/Reject/Transfer transaction.
3150 l_stmt_num := 10;
3151 INSERT
3152 INTO cst_inv_qty_temp(
3153 qty_source,
3154 organization_id,
3155 inventory_item_id,
3156 category_id,
3157 cost_type_id,
3158 rcv_transaction_id,
3159 revision,
3160 rollback_qty
3161 )
3162 SELECT 9, -- RECEIVED
3163 MS.to_organization_id,
3164 CILT.inventory_item_id,
3165 DECODE(MS.item_id, NULL, POL.category_id, CILT.category_id),
3166 CILT.cost_type_id,
3167 DECODE(RT.transaction_type,
3168 'ACCEPT', Get_ParentReceiveTxn(MS.rcv_transaction_id),
3169 'REJECT', Get_ParentReceiveTxn(MS.rcv_transaction_id),
3170 'TRANSFER', Get_ParentReceiveTxn(MS.rcv_transaction_id),
3171 (MS.rcv_transaction_id)),
3172 -- MTL_SUPPLY stores parent Match/Receive except for Accept/Reject/Transfers
3173 DECODE(p_qty_by_revision, 1, POL.item_revision, NULL),
3174 SUM(MS.to_org_primary_quantity) -- sum across po distributions
3175 FROM cst_item_list_temp CILT,
3176 cst_cg_list_temp CCLT,
3177 mtl_supply MS,
3178 rcv_transactions RT,
3179 mtl_parameters MP,
3180 po_lines_all POL,
3181 po_line_locations_all POLL,
3182 pjm_project_parameters PPP
3183 WHERE NVL(CILT.inventory_item_id, -1) = NVL(MS.item_id, -1)
3184 AND MP.organization_id = MS.to_organization_id
3185 AND MS.to_organization_id = p_organization_id
3186 AND NVL(
3187 MS.cost_group_id,
3188 NVL(PPP.costing_group_id,MP.default_cost_group_id)
3189 ) = CCLT.cost_group_id
3190 AND MS.supply_type_code = 'RECEIVING'
3191 AND RT.transaction_id = MS.rcv_transaction_id
3192 -- Joining to MS eliminates consigned and drop ship receipts
3193 AND NVL(RT.consigned_flag, 'N') = 'N' -- eliminate consigned
3194 AND RT.source_document_code = 'PO'
3195 AND POL.po_line_id = RT.po_line_id
3196 AND PPP.project_id (+) = POL.project_id
3197 AND POLL.line_location_id = RT.po_line_location_id
3198 AND POLL.shipment_type <> 'PREPAYMENT'
3199 AND POLL.matching_basis = 'QUANTITY' -- eliminate service line types
3200 AND POLL.accrue_on_receipt_flag = DECODE(p_include_period_end, 1, POLL.accrue_on_receipt_flag, 'Y')
3201 GROUP
3202 BY MS.to_organization_id,
3203 CILT.inventory_item_id,
3204 DECODE(MS.item_id, NULL, POL.category_id, CILT.category_id),
3205 CILT.cost_type_id,
3206 DECODE(RT.transaction_type,
3207 'ACCEPT', Get_ParentReceiveTxn(MS.rcv_transaction_id),
3208 'REJECT', Get_ParentReceiveTxn(MS.rcv_transaction_id),
3209 'TRANSFER', Get_ParentReceiveTxn(MS.rcv_transaction_id),
3210 (MS.rcv_transaction_id)),
3211 DECODE(p_qty_by_revision, 1, POL.item_revision, NULL);
3212
3213 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
3214 THEN
3215 FND_MSG_PUB.Add_Exc_Msg(
3216 p_pkg_name => G_PKG_NAME,
3217 p_procedure_name => l_api_name,
3218 p_error_text => l_stmt_num||': Calculated '||SQL%ROWCOUNT||
3219 ' current receiving quantities'
3220 );
3221 END IF;
3222
3223 -- if p_valuation_date is called with a date in the past,
3224 -- calculate receiving quantity with qty_source = 10
3225 --
3226 -- Method: find all transactions in RT that impact quantity and occurred
3227 -- after the valuation date. Rollback this quantity and insert the sum of
3228 -- the quantity by parent Receive or Match transaction. When summed with
3229 -- the current quantity row with qty_source = 9 inserted in the previous step,
3230 -- this will give the valuation as of the date passed in.
3231
3232 IF (p_valuation_date is not null) THEN
3233 l_stmt_num := 30;
3234 INSERT
3235 INTO cst_inv_qty_temp(
3236 qty_source,
3237 organization_id,
3238 inventory_item_id,
3239 category_id,
3240 cost_type_id,
3241 rcv_transaction_id,
3242 revision,
3243 rollback_qty
3244 )
3245 SELECT 10 qty_source, -- ROLLBACK RECEIVING
3246 RT.organization_id,
3247 CILT.inventory_item_id,
3248 DECODE(POL.item_id, NULL, POL.category_id, CILT.category_id),
3249 CILT.cost_type_id,
3250 DECODE(RT.transaction_type,
3254 DECODE(p_qty_by_revision, 1, POL.item_revision, NULL),
3251 'RECEIVE', RT.transaction_id,
3252 'MATCH', RT.transaction_id,
3253 Get_ParentReceiveTxn(RT.transaction_id)) rcv_transaction_id,
3255 SUM(DECODE(RT.transaction_type,
3256 'RECEIVE', -1 * RT.primary_quantity,
3257 'DELIVER', 1 * RT.primary_quantity,
3258 'RETURN TO RECEIVING', -1 * RT.primary_quantity,
3259 'RETURN TO VENDOR', DECODE(PARENT_RT.transaction_type,
3260 'UNORDERED', 0,
3261 1 * RT.primary_quantity),
3262 'MATCH', -1 * RT.primary_quantity,
3263 'CORRECT', DECODE(PARENT_RT.transaction_type,
3264 'UNORDERED', 0,
3265 'RECEIVE', -1 * RT.primary_quantity,
3266 'DELIVER', 1 * RT.primary_quantity,
3267 'RETURN TO RECEIVING', -1 * RT.primary_quantity,
3268 'RETURN TO VENDOR', DECODE(GRPARENT_RT.transaction_type,
3269 'UNORDERED', 0,
3270 1 * RT.primary_quantity),
3271 'MATCH', -1 * RT.primary_quantity,
3272 0),
3273 0)
3274 ) rollback_qty
3275 FROM cst_item_list_temp CILT,
3276 cst_cg_list_temp CCLT,
3277 rcv_transactions RT,
3278 rcv_transactions PARENT_RT,
3279 rcv_transactions GRPARENT_RT,
3280 mtl_parameters MP,
3281 po_lines_all POL,
3282 po_line_locations_all POLL,
3283 pjm_project_parameters PPP
3284 WHERE NVL(CILT.inventory_item_id, -1) = NVL(POL.item_id, -1)
3285 AND MP.organization_id = RT.organization_id
3286 AND RT.organization_id = p_organization_id
3287 AND NVL(PPP.costing_group_id,MP.default_cost_group_id) = CCLT.cost_group_id
3288 AND NVL(RT.consigned_flag, 'N') = 'N' -- eliminate consigned
3289 AND NVL(RT.dropship_type_code, 3) = 3 -- eliminate drop ship
3290 AND RT.transaction_date > p_valuation_date
3291 AND RT.transaction_type in
3292 ('RECEIVE', 'DELIVER', 'RETURN TO RECEIVING', 'RETURN TO VENDOR', 'CORRECT', 'MATCH')
3293 AND RT.source_document_code = 'PO'
3294 AND DECODE(RT.parent_transaction_id,
3295 -1, NULL,
3296 0, NULL,
3297 RT.parent_transaction_id) = PARENT_RT.transaction_id(+)
3298 AND DECODE(PARENT_RT.parent_transaction_id,
3299 -1, NULL,
3300 0, NULL,
3301 PARENT_RT.parent_transaction_id) = GRPARENT_RT.transaction_id(+)
3302 AND POL.po_line_id = RT.po_line_id
3303 AND PPP.project_id (+) = POL.project_id
3304 AND POLL.line_location_id = RT.po_line_location_id
3305 AND POLL.shipment_type <> 'PREPAYMENT'
3306 AND POLL.matching_basis = 'QUANTITY' -- eliminate service line types
3307 AND POLL.accrue_on_receipt_flag = DECODE(p_include_period_end, 1, POLL.accrue_on_receipt_flag, 'Y')
3308 GROUP
3309 BY RT.organization_id,
3310 CILT.inventory_item_id,
3311 DECODE(POL.item_id, NULL, POL.category_id, CILT.category_id),
3312 CILT.cost_type_id,
3313 DECODE(RT.transaction_type,
3314 'RECEIVE', RT.transaction_id,
3315 'MATCH', RT.transaction_id,
3316 Get_ParentReceiveTxn(RT.transaction_id)),
3317 DECODE(p_qty_by_revision, 1, POL.item_revision, NULL)
3318 HAVING SUM(DECODE(RT.transaction_type,
3319 'RECEIVE', -1 * RT.primary_quantity,
3320 'DELIVER', 1 * RT.primary_quantity,
3321 'RETURN TO RECEIVING', -1 * RT.primary_quantity,
3322 'RETURN TO VENDOR', DECODE(PARENT_RT.transaction_type,
3323 'UNORDERED', 0,
3324 1 * RT.primary_quantity),
3325 'MATCH', -1 * RT.primary_quantity,
3326 'CORRECT', DECODE(PARENT_RT.transaction_type,
3327 'UNORDERED', 0,
3328 'RECEIVE', -1 * RT.primary_quantity,
3329 'DELIVER', 1 * RT.primary_quantity,
3330 'RETURN TO RECEIVING', -1 * RT.primary_quantity,
3331 'RETURN TO VENDOR', DECODE(GRPARENT_RT.transaction_type,
3332 'UNORDERED', 0,
3333 1 * RT.primary_quantity),
3334 'MATCH', -1 * RT.primary_quantity,
3335 0),
3336 0)
3337 ) <> 0;
3338
3339 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
3340 THEN
3341 FND_MSG_PUB.Add_Exc_Msg(
3342 p_pkg_name => G_PKG_NAME,
3343 p_procedure_name => l_api_name,
3347 END IF;
3344 p_error_text => l_stmt_num||': Calculated '||SQL%ROWCOUNT||
3345 ' rollback receiving quantities'
3346 );
3348
3349 END IF; /* end if p_valuation_date is not null */
3350
3351 x_return_status := FND_API.G_RET_STS_SUCCESS;
3352 EXCEPTION
3353 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3354 ROLLBACK TO Calculate_ReceivingQty_PVT;
3355 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3356 WHEN OTHERS THEN
3357 ROLLBACK TO Calculate_ReceivingQty_PVT;
3358 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3359 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
3360 THEN
3361 FND_MSG_PUB.Add_Exc_Msg(
3362 p_pkg_name => G_PKG_NAME,
3363 p_procedure_name => l_api_name,
3364 p_error_text => SUBSTR(l_stmt_num||SQLERRM,1,240)
3365 );
3366 END IF;
3367 END Calculate_ReceivingQty;
3368
3369 FUNCTION Get_ParentReceiveTxn (
3370 p_rcv_transaction_id IN NUMBER
3371 )
3372 RETURN NUMBER
3373 IS
3374 l_parent_transaction_id NUMBER;
3375 BEGIN
3376 SELECT transaction_id
3377 INTO l_parent_transaction_id
3378 FROM (
3379 SELECT RT.transaction_id transaction_id,
3380 RT.parent_transaction_id parent_transaction_id,
3381 RT.transaction_type
3382 FROM rcv_transactions RT
3383 START WITH transaction_id = p_rcv_transaction_id
3384 CONNECT BY transaction_id = PRIOR parent_transaction_id)
3385 WHERE ((transaction_type = 'RECEIVE' and parent_transaction_id=-1)
3386 OR transaction_type = 'MATCH');
3387 return l_parent_transaction_id;
3388 END Get_ParentReceiveTxn;
3389
3390
3391 PROCEDURE Calculate_InventoryCost(
3392 p_api_version IN NUMBER,
3393 p_valuation_date IN DATE,
3394 p_organization_id IN NUMBER,
3395 x_return_status OUT NOCOPY VARCHAR2
3396 )
3397 IS
3398 l_api_name CONSTANT VARCHAR2(30) := 'Calculate_InventoryCost';
3399 l_api_version CONSTANT NUMBER := 1.0;
3400 l_msg_level_threshold NUMBER;
3401 l_stmt_num NUMBER := 0;
3402
3403 l_organization_id NUMBER(15);
3404 l_inventory_item_id NUMBER(15);
3405 l_cost_type_id NUMBER(15);
3406 l_min_cost_update_id NUMBER(15);
3407 l_max_cost_update_id NUMBER(15);
3408 l_rcv_transaction_id NUMBER(15);
3409 l_cost_method NUMBER;
3410 l_receiving_cost NUMBER;
3411
3412 l_msg_count NUMBER;
3413 l_msg_data VARCHAR2(2000);
3414
3415 l_rcv_cost_source NUMBER; -- added in 12i for as of date changes
3416
3417 CURSOR c_standard IS
3418 SELECT DISTINCT
3419 CIQT.organization_id,
3420 CIQT.inventory_item_id,
3421 CIQT.cost_type_id
3422 FROM cst_inv_qty_temp CIQT,
3423 mtl_parameters MP
3424 WHERE CIQT.organization_id = p_organization_id
3425 AND MP.organization_id = p_organization_id
3426 AND MP.primary_cost_method = 1
3427 AND CIQT.qty_source NOT IN (1,2,9,10) -- PRIOR SUMMARY, CURRENT SUMMARY,
3428 -- RECEIVING, PAST RECEIVING
3429 AND CIQT.cost_type_id IS NOT NULL; -- bug 6893581
3430 --{BUG#6631966
3431 -- Commented oout the check on the valuation cost type will be done in the loop
3432 -- AND CIQT.cost_type_id =
3433 -- DECODE(
3434 -- p_valuation_date,
3435 -- NULL,CIQT.cost_type_id,
3436 -- MP.primary_cost_method
3437 -- );
3438 --}
3439 -- Past cost is calculated only for valuation cost type
3440
3441 CURSOR c_receiving IS
3442 SELECT DISTINCT
3443 CIQT.organization_id,
3444 CIQT.inventory_item_id,
3445 CIQT.rcv_transaction_id
3446 FROM cst_inv_qty_temp CIQT
3447 WHERE CIQT.qty_source in (9,10);
3448
3449
3450 --
3451 --{BUG#7484428
3452 --
3453 CURSOR cur_get_new_mcacd_cost(
3454 c_organization_id IN NUMBER,
3455 c_valuation_date IN DATE,
3456 c_inventory_item_id IN NUMBER)
3457 IS
3458 SELECT mcacd_txn,
3459 mmt_txn,
3460 mmt_cost,
3461 mcacd_cost,
3462 TXN_DATE,
3463 prior_COST ,
3464 act,
3465 material_cost,
3466 material_overhead_cost,
3467 resource_cost,
3468 outside_processing_cost,
3469 overhead_cost
3470 FROM
3471 (SELECT MCACD.TRANSACTION_ID mcacd_txn,
3472 MMT.transaction_id mmt_txn,
3473 NVL(MMT.actual_cost,0) mmt_cost,
3474 MMT.transaction_action_id act,
3475 NVL(MMT.prior_COST,0) prior_cost,
3476 SUM(NVL(mcacd.actual_cost,0))
3477 OVER (PARTITION BY MMT.transaction_id) mcacd_cost,
3478 SUM(DECODE(mcacd.cost_element_id,1,NVL(mcacd.actual_cost,0),0))
3482 SUM(DECODE(mcacd.cost_element_id,3,NVL(mcacd.actual_cost,0),0))
3479 OVER (PARTITION BY MMT.transaction_id) material_cost,
3480 SUM(DECODE(mcacd.cost_element_id,2,NVL(mcacd.actual_cost,0),0))
3481 OVER (PARTITION BY MMT.transaction_id) material_overhead_cost,
3483 OVER (PARTITION BY MMT.transaction_id) resource_cost,
3484 SUM(DECODE(mcacd.cost_element_id,4,nvl(mcacd.actual_cost,0),0))
3485 OVER (PARTITION BY MMT.transaction_id) outside_processing_cost,
3486 SUM(DECODE(mcacd.cost_element_id,5,nvl(mcacd.actual_cost,0),0))
3487 OVER (PARTITION BY MMT.transaction_id) overhead_cost,
3488 NVL(MCACD.creation_date,MMT.creation_date) txn_date
3489 FROM MTL_CST_ACTUAL_COST_DETAILS MCACD,
3490 mtl_material_transactions mmt
3491 WHERE MCACD.ORGANIZATION_ID(+) = c_organization_id
3492 AND MCACD.inventory_item_id(+) = c_inventory_item_id
3493 AND MMT.transaction_date > c_valuation_date
3494 AND mmt.transaction_action_id NOT IN (5,30,40,41,42,43,50,51,52,15,22,11,17,10,13,9,14,7,26,36,25,56,57)
3495 AND NOT (mmt.transaction_action_id IN (2,28,55,3) AND mmt.primary_quantity > 0)
3496 --
3497 -- Standard update only originated by standard cost update avoid PAC cost update
3498 --
3499 AND NOT (mmt.transaction_action_id = 24 AND mmt.transaction_source_type_id <> 11)
3500 AND MMT.inventory_item_id = c_inventory_item_id
3501 AND MMT.organization_id = c_organization_id
3502 AND mmt.transaction_id = mcacd.transaction_id (+) )
3503 ORDER BY TXN_DATE asc,
3504 mmt_txn asc;
3505
3506 l_mcacd_txn NUMBER;
3507 l_mmt_txn NUMBER;
3508 l_mmt_cost NUMBER;
3509 l_mcacd_cost NUMBER;
3510 l_txn_date DATE;
3511 l_prior_cost NUMBER;
3512 l_act NUMBER;
3513 l_material_cost NUMBER;
3514 l_material_overhead_cost NUMBER;
3515 l_resource_cost NUMBER;
3516 l_outside_processing_cost NUMBER;
3517 l_overhead_cost NUMBER;
3518 l_exp_item_flag NUMBER;
3519 l_rec cst_inv_cost_temp%ROWTYPE;
3520
3521 PROCEDURE ins_cst_inv_cost_temp(p_rec IN cst_inv_cost_temp%ROWTYPE) IS
3522 BEGIN
3523 INSERT INTO cst_inv_cost_temp(
3524 organization_id,
3525 inventory_item_id,
3526 cost_type_id,
3527 cost_source,
3528 inventory_asset_flag,
3529 item_cost,
3530 material_cost,
3531 material_overhead_cost,
3532 resource_cost,
3533 outside_processing_cost,
3534 overhead_cost
3535 ) VALUES (
3536 p_rec.organization_id,
3537 p_rec.inventory_item_id,
3538 p_rec.cost_type_id,
3539 p_rec.cost_source,
3540 p_rec.inventory_asset_flag,
3541 p_rec.item_cost,
3542 p_rec.material_cost,
3543 p_rec.material_overhead_cost,
3544 p_rec.resource_cost,
3545 p_rec.outside_processing_cost,
3546 p_rec.overhead_cost);
3547 END ins_cst_inv_cost_temp;
3548 --}
3549 BEGIN
3550 -- Standard Start of API savepoint
3551 SAVEPOINT Calculate_InventoryCost_PVT;
3552
3553 -- Check for call compatibility
3554 IF NOT FND_API.Compatible_API_Call(
3555 p_current_version_number => l_api_version,
3556 p_caller_version_number => p_api_version,
3557 p_api_name => l_api_name,
3558 p_pkg_name => G_PKG_NAME
3559 )
3560 THEN
3561 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3562 END IF;
3563
3564 -- Check for message level threshold
3565 l_msg_level_threshold := FND_PROFILE.Value('FND_AS_MSG_LEVEL_THRESHOLD');
3566
3567 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH
3568 THEN
3569 FND_MSG_PUB.Add_Exc_Msg(
3570 p_pkg_name => G_PKG_NAME,
3571 p_procedure_name => l_api_name,
3572 p_error_text => SUBSTR(
3573 l_stmt_num||':'||
3574 to_char(p_valuation_date,'DD-MON-YYYY HH24:MI:SS'),
3575 1,
3576 240
3577 )
3578 );
3579 END IF;
3580
3581 IF p_valuation_date IS NULL
3582 THEN
3583 -- Calculate the costs for CIQT records that belongs to Standard costing
3584 -- organizations
3585 OPEN c_standard;
3586 l_stmt_num := 10;
3587 LOOP
3588 FETCH c_standard
3589 INTO l_organization_id,
3590 l_inventory_item_id,
3591 l_cost_type_id;
3592
3596 INSERT
3593 EXIT
3594 WHEN c_standard%NOTFOUND;
3595
3597 INTO cst_inv_cost_temp(
3598 organization_id,
3599 inventory_item_id,
3600 cost_type_id,
3601 cost_source,
3602 inventory_asset_flag,
3603 item_cost,
3604 material_cost,
3605 material_overhead_cost,
3606 resource_cost,
3607 outside_processing_cost,
3608 overhead_cost
3609 )
3610 SELECT l_organization_id,
3611 l_inventory_item_id,
3612 l_cost_type_id,
3613 1, -- CURRENT
3614 CIC.inventory_asset_flag,
3615 NVL(CIC.item_cost,0),
3616 NVL(CIC.material_cost,0),
3617 NVL(CIC.material_overhead_cost,0),
3618 NVL(CIC.resource_cost,0),
3619 NVL(CIC.outside_processing_cost,0),
3620 NVL(CIC.overhead_cost,0)
3621 FROM mtl_parameters MP,
3622 cst_item_costs CIC
3623 WHERE MP.organization_id = l_organization_id
3624 AND CIC.organization_id = MP.cost_organization_id
3625 AND CIC.inventory_item_id = l_inventory_item_id
3626 AND CIC.cost_type_id = l_cost_type_id;
3627 END LOOP;
3628 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
3629 THEN
3630 FND_MSG_PUB.Add_Exc_Msg(
3631 p_pkg_name => G_PKG_NAME,
3632 p_procedure_name => l_api_name,
3633 p_error_text => l_stmt_num||': Calculated '||c_standard%ROWCOUNT||
3634 ' current standard costs'
3635 );
3636 END IF;
3637 CLOSE c_standard;
3638
3639
3640
3641 -- Calculate the costs for CIQT records that belong to Actual costing
3642 -- organizations
3643 -- Note HYU: For layer the as of date has no effect, always null
3644 --
3645 l_stmt_num := 20;
3646 INSERT
3647 INTO cst_inv_cost_temp(
3648 organization_id,
3649 inventory_item_id,
3650 cost_group_id,
3651 cost_type_id,
3652 cost_source,
3653 inventory_asset_flag,
3654 item_cost,
3655 material_cost,
3656 material_overhead_cost,
3657 resource_cost,
3658 outside_processing_cost,
3659 overhead_cost
3660 )
3661 SELECT CIQT.organization_id,
3662 CIQT.inventory_item_id,
3663 CIQT.cost_group_id,
3664 CIQT.cost_type_id,
3665 1, -- CURRENT
3666 CIC.inventory_asset_flag,
3667 DECODE(
3668 CIQT.cost_type_id,
3669 MP.primary_cost_method,
3670 NVL(CQL.item_cost,0),
3671 NVL(CIC.item_cost,0)
3672 ),
3673 DECODE(
3674 CIQT.cost_type_id,
3675 MP.primary_cost_method,
3676 NVL(CQL.material_cost,0),
3677 NVL(CIC.material_cost,0)
3678 ),
3679 DECODE(
3680 CIQT.cost_type_id,
3681 MP.primary_cost_method,
3682 NVL(CQL.material_overhead_cost,0),
3683 NVL(CIC.material_overhead_cost,0)
3684 ),
3685 DECODE(
3686 CIQT.cost_type_id,
3687 MP.primary_cost_method,
3688 NVL(CQL.resource_cost,0),
3689 NVL(CIC.resource_cost,0)
3690 ),
3691 DECODE(
3692 CIQT.cost_type_id,
3693 MP.primary_cost_method,
3694 NVL(CQL.outside_processing_cost,0),
3695 NVL(CIC.outside_processing_cost,0)
3696 ),
3697 DECODE(
3698 CIQT.cost_type_id,
3699 MP.primary_cost_method,
3700 NVL(CQL.overhead_cost,0),
3701 NVL(CIC.overhead_cost,0)
3702 )
3703 FROM (
3704 SELECT DISTINCT
3705 organization_id,
3706 inventory_item_id,
3707 cost_group_id,
3708 cost_type_id
3709 FROM cst_inv_qty_temp
3710 WHERE qty_source NOT IN (1,2,9,10)
3711 ) CIQT,
3712 cst_quantity_layers CQL,
3713 cst_item_costs CIC,
3714 mtl_parameters MP
3715 WHERE CIC.organization_id = CIQT.organization_id
3716 AND CIC.inventory_item_id = CIQT.inventory_item_id
3717 AND CIC.cost_type_id = CIQT.cost_type_id
3718 AND MP.organization_id = CIQT.organization_id
3719 AND MP.primary_cost_method <> 1
3720 AND CQL.organization_id (+) = CIQT.organization_id
3721 AND CQL.inventory_item_id (+) = CIQT.inventory_item_id
3722 AND CQL.cost_group_id (+) = CIQT.cost_group_id
3723 -- Outer join on CQL to insert zero costs for expense
3724 -- items and asset items that do not have a layer
3725 AND CIQT.organization_id = p_organization_id;
3726
3727 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
3728 THEN
3732 p_error_text => l_stmt_num||': Calculated '||SQL%ROWCOUNT||
3729 FND_MSG_PUB.Add_Exc_Msg(
3730 p_pkg_name => G_PKG_NAME,
3731 p_procedure_name => l_api_name,
3733 ' current actual costs'
3734 );
3735 END IF;
3736
3737 l_stmt_num := 30;
3738 /* Set rcv_cost_source to 3 for Current Receiving Cost */
3739 l_rcv_cost_source := 3;
3740
3741 ELSE /* p_valuation_date is not null: Calculate Past Costs */
3742 OPEN c_standard;
3743 l_stmt_num := 40;
3744
3745 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW
3746 THEN
3747 FND_MSG_PUB.Add_Exc_Msg(
3748 p_pkg_name => G_PKG_NAME,
3749 p_procedure_name => l_api_name,
3750 p_error_text => l_stmt_num||': Calculating '||
3751 'past standard costs'
3752 );
3753 END IF;
3754
3755
3756 LOOP
3757 FETCH c_standard
3758 INTO l_organization_id,
3759 l_inventory_item_id,
3760 l_cost_type_id;
3761
3762 EXIT
3763 WHEN c_standard%NOTFOUND;
3764
3765 l_min_cost_update_id := NULL;
3766 l_max_cost_update_id := NULL;
3767
3768 --{BUG#6631966
3769 IF l_cost_type_id <> 1 THEN
3770
3771 l_stmt_num := 45;
3772
3773 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW
3774 THEN
3775 FND_MSG_PUB.Add_Exc_Msg(
3776 p_pkg_name => G_PKG_NAME,
3777 p_procedure_name => l_api_name,
3778 p_error_text => l_stmt_num||': Item ID '|| l_inventory_item_id ||
3779 ' Cost type '|| l_cost_type_id
3780 );
3781 END IF;
3782
3783 -- As the cost type is not the valuation cost type insert the current cost
3784 INSERT INTO cst_inv_cost_temp
3785 ( organization_id,
3786 inventory_item_id,
3787 cost_type_id,
3788 cost_source,
3789 inventory_asset_flag,
3790 item_cost,
3791 material_cost,
3792 material_overhead_cost,
3793 resource_cost,
3794 outside_processing_cost,
3795 overhead_cost
3796 )
3797 SELECT l_organization_id,
3798 l_inventory_item_id,
3799 l_cost_type_id,
3800 1, -- CURRENT
3801 CIC.inventory_asset_flag,
3802 NVL(CIC.item_cost,0),
3803 NVL(CIC.material_cost,0),
3804 NVL(CIC.material_overhead_cost,0),
3805 NVL(CIC.resource_cost,0),
3806 NVL(CIC.outside_processing_cost,0),
3807 NVL(CIC.overhead_cost,0)
3808 FROM mtl_parameters MP,
3809 cst_item_costs CIC
3810 WHERE MP.organization_id = l_organization_id
3811 AND CIC.organization_id = MP.cost_organization_id
3812 AND CIC.inventory_item_id = l_inventory_item_id
3813 AND CIC.cost_type_id = l_cost_type_id;
3814
3815 ELSE
3816 --BUG#6631966: From this point the current behaviour
3817 -- l_cost_type_id = 1
3818 l_stmt_num := 46;
3819
3820 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW
3821 THEN
3822 FND_MSG_PUB.Add_Exc_Msg(
3823 p_pkg_name => G_PKG_NAME,
3824 p_procedure_name => l_api_name,
3825 p_error_text => l_stmt_num||': Item ID '|| l_inventory_item_id ||
3826 ' Cost type '|| l_cost_type_id
3827 );
3828 END IF;
3829
3830
3831
3832
3833
3834
3835
3836 --{BUG#7484428
3837 SELECT nvl(CIC.inventory_asset_flag,2)
3838 INTO l_exp_item_flag
3839 FROM mtl_parameters MP,
3840 cst_item_costs CIC
3841 WHERE MP.organization_id = l_organization_id
3842 AND CIC.organization_id = MP.cost_organization_id
3843 AND CIC.inventory_item_id = l_inventory_item_id
3844 AND CIC.cost_type_id = 1;
3845
3846 IF(l_exp_item_flag = 2) THEN
3847
3848 l_rec.organization_id := l_organization_id;
3849 l_rec.inventory_item_id := l_inventory_item_id;
3850 l_rec.cost_type_id := 1;
3851 l_rec.cost_source := 2; --PAST
3852 l_rec.inventory_asset_flag := 2;
3853 l_rec.item_cost := 0;
3854 l_rec.material_cost := 0;
3855 l_rec.material_overhead_cost := 0;
3856 l_rec.resource_cost := 0;
3857 l_rec.outside_processing_cost:= 0;
3858 l_rec.overhead_cost := 0;
3859 ins_cst_inv_cost_temp(p_rec => l_rec);
3860
3861 ELSE
3862 OPEN cur_get_new_mcacd_cost(
3863 l_organization_id ,
3864 p_valuation_date ,
3865 l_inventory_item_id );
3866
3867 FETCH cur_get_new_mcacd_cost
3871 l_mcacd_cost ,
3868 INTO l_mcacd_txn ,
3869 l_mmt_txn ,
3870 l_mmt_cost ,
3872 l_txn_date ,
3873 l_prior_cost ,
3874 l_act ,
3875 l_material_cost ,
3876 l_material_overhead_cost ,
3877 l_resource_cost ,
3878 l_outside_processing_cost ,
3879 l_overhead_cost ;
3880
3881
3882 IF (cur_get_new_mcacd_cost%NOTFOUND) THEN
3883 /*No Txn in future use present cost */
3884
3885 INSERT INTO cst_inv_cost_temp(
3886 organization_id,
3887 inventory_item_id,
3888 cost_type_id,
3889 cost_source,
3890 inventory_asset_flag,
3891 item_cost,
3892 material_cost,
3893 material_overhead_cost,
3894 resource_cost,
3895 outside_processing_cost,
3896 overhead_cost
3897 )
3898 SELECT l_organization_id,
3899 l_inventory_item_id,
3900 1,
3901 2, -- PAST
3902 CIC.inventory_asset_flag,
3903 SUM(NVL(CIC.item_cost,0)),
3904 SUM(NVL(CIC.material_cost,0)),
3905 SUM(NVL(CIC.material_overhead_cost,0)),
3906 SUM(NVL(CIC.resource_cost,0)),
3907 SUM(NVL(CIC.outside_processing_cost,0)),
3908 SUM(NVL(CIC.overhead_cost,0))
3909 FROM cst_item_costs CIC,
3910 mtl_parameters MP
3911 WHERE CIC.cost_type_id = 1
3912 AND MP.organization_id = l_organization_id
3913 AND CIC.organization_id = MP.cost_organization_id
3914 AND CIC.inventory_item_id = l_inventory_item_id
3915 GROUP BY CIC.inventory_asset_flag;
3916
3917 ELSE
3918 -- Got transaction use cost from the transaction
3919 l_rec.organization_id := l_organization_id;
3920 l_rec.inventory_item_id := l_inventory_item_id;
3921 l_rec.cost_type_id := 1;
3922 l_rec.cost_source := 2; --PAST
3923 l_rec.inventory_asset_flag := 1; --Standard cost update is only done for asset items
3924
3925 IF( l_mcacd_txn IS NOT null) THEN
3926 l_rec.item_cost := l_mcacd_cost;
3927 l_rec.material_cost := l_material_cost;
3928 l_rec.material_overhead_cost := l_material_overhead_cost;
3929 l_rec.resource_cost := l_resource_cost;
3930 l_rec.outside_processing_cost := l_outside_processing_cost;
3931 l_rec.overhead_cost := l_overhead_cost;
3932
3933 ELSE
3934 IF(l_act = 24) THEN
3935 l_rec.item_cost := l_prior_cost;
3936 l_rec.material_cost := l_prior_cost;
3937 l_rec.material_overhead_cost := 0;
3938 l_rec.resource_cost := 0;
3939 l_rec.outside_processing_cost := 0;
3940 l_rec.overhead_cost := 0;
3941
3942 ELSE
3943 l_rec.item_cost := l_mmt_cost;
3944 l_rec.material_cost := l_mmt_cost;
3945 l_rec.material_overhead_cost := 0;
3946 l_rec.resource_cost := 0;
3947 l_rec.outside_processing_cost:= 0;
3948 l_rec.overhead_cost := 0;
3949 END IF;
3950 END IF;
3951 ins_cst_inv_cost_temp(p_rec => l_rec);
3952
3953 END IF;
3954 CLOSE cur_get_new_mcacd_cost;
3955 END IF;
3956
3957 ----
3958 -- BUG#7484428
3959 -- Item cost will no longer be derived based on standard cost update history
3960 -- but with transaction cost
3961 ----
3962 /*
3963 SELECT MIN(CSC.cost_update_id)
3964 INTO l_min_cost_update_id
3965 FROM cst_standard_costs CSC,
3966 mtl_parameters MP
3967 WHERE MP.organization_id = l_organization_id
3968 AND CSC.organization_id = MP.cost_organization_id
3969 AND CSC.inventory_item_id = l_inventory_item_id
3970 AND CSC.standard_cost_revision_date > p_valuation_date;
3971 -- This logic will only work if the CSC records with
3972 -- standard_cost_revision_date > p_valuation_date have not
3973 -- been purged. Although CSC is populated for cost child
3974 -- organizations, CEC is not. We join through MP to be
3975 -- consistent.
3976
3977 -- Is any cost update performed after the specified date?
3978 IF l_min_cost_update_id IS NOT NULL
3979 THEN
3980 -- Yes. Figure out the prior cost update
3981
3982 SELECT MAX(CSC.cost_update_id)
3983 INTO l_max_cost_update_id
3984 FROM cst_standard_costs CSC,
3985 mtl_parameters MP
3986 WHERE MP.organization_id = l_organization_id
3987 AND CSC.organization_id = MP.cost_organization_id
3991 IF l_max_cost_update_id IS NOT NULL
3988 AND CSC.inventory_item_id = l_inventory_item_id
3989 AND CSC.standard_cost_revision_date <= p_valuation_date;
3990
3992 THEN
3993 -- Use the cost in cst_elemental_costs.
3994 INSERT
3995 INTO cst_inv_cost_temp(
3996 organization_id,
3997 inventory_item_id,
3998 cost_type_id,
3999 cost_source,
4000 inventory_asset_flag,
4001 item_cost,
4002 material_cost,
4003 material_overhead_cost,
4004 resource_cost,
4005 outside_processing_cost,
4006 overhead_cost
4007 )
4008 SELECT l_organization_id,
4009 l_inventory_item_id,
4010 1,
4011 2, -- PAST
4012 1, -- Standard cost update is only done for asset items
4013 SUM(NVL(CEC.standard_cost,0)),
4014 SUM(DECODE(CEC.cost_element_id,1,NVL(CEC.standard_cost,0),0)),
4015 SUM(DECODE(CEC.cost_element_id,2,NVL(CEC.standard_cost,0),0)),
4016 SUM(DECODE(CEC.cost_element_id,3,NVL(CEC.standard_cost,0),0)),
4017 SUM(DECODE(CEC.cost_element_id,4,NVL(CEC.standard_cost,0),0)),
4018 SUM(DECODE(CEC.cost_element_id,5,NVL(CEC.standard_cost,0),0))
4019 FROM cst_elemental_costs CEC,
4020 mtl_parameters MP
4021 WHERE CEC.cost_update_id = l_max_cost_update_id
4022 AND MP.organization_id = l_organization_id
4023 AND CEC.organization_id = MP.cost_organization_id
4024 AND CEC.inventory_item_id = l_inventory_item_id;
4025
4026 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW
4027 THEN
4028 FND_MSG_PUB.Add_Exc_Msg(
4029 p_pkg_name => G_PKG_NAME,
4030 p_procedure_name => l_api_name,
4031 p_error_text => l_stmt_num||': Item ID '||
4032 l_inventory_item_id || ' Cost Update ID '||
4033 l_max_cost_update_id
4034 );
4035 END IF;
4036
4037 ELSE
4038 -- Either the cost history is purged or item cost is zero
4039 INSERT
4040 INTO cst_inv_cost_temp(
4041 organization_id,
4042 inventory_item_id,
4043 cost_type_id,
4044 cost_source,
4045 inventory_asset_flag,
4046 item_cost,
4047 material_cost,
4048 material_overhead_cost,
4049 resource_cost,
4050 outside_processing_cost,
4051 overhead_cost
4052 )
4053 SELECT l_organization_id,
4054 l_inventory_item_id,
4055 1,
4056 2, -- PAST
4057 1, -- Standard cost update is only done for asset items
4058 0,
4059 0,
4060 0,
4061 0,
4062 0,
4063 0
4064 FROM dual;
4065
4066 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW
4067 THEN
4068 FND_MSG_PUB.Add_Exc_Msg(
4069 p_pkg_name => G_PKG_NAME,
4070 p_procedure_name => l_api_name,
4071 p_error_text => l_stmt_num||': Item ID '||l_inventory_item_id||
4072 ' zero cost'
4073 );
4074 END IF;
4075
4076 END IF; --l_max_cost_update_id
4077
4078 ELSE
4079 -- No. Use current cost
4080 INSERT
4081 INTO cst_inv_cost_temp(
4082 organization_id,
4083 inventory_item_id,
4084 cost_type_id,
4085 cost_source,
4086 inventory_asset_flag,
4087 item_cost,
4088 material_cost,
4089 material_overhead_cost,
4090 resource_cost,
4091 outside_processing_cost,
4092 overhead_cost
4093 )
4094 SELECT l_organization_id,
4095 l_inventory_item_id,
4096 1,
4097 2, -- PAST
4098 CIC.inventory_asset_flag,
4099 NVL(CIC.item_cost,0),
4100 NVL(CIC.material_cost,0),
4101 NVL(CIC.material_overhead_cost,0),
4102 NVL(CIC.resource_cost,0),
4103 NVL(CIC.outside_processing_cost,0),
4104 NVL(CIC.overhead_cost,0)
4105 FROM mtl_parameters MP,
4106 cst_item_costs CIC
4107 WHERE MP.organization_id = l_organization_id
4108 AND CIC.organization_id = MP.cost_organization_id
4109 AND CIC.inventory_item_id = l_inventory_item_id
4110 AND CIC.cost_type_id = 1;
4111
4115 p_pkg_name => G_PKG_NAME,
4112 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW
4113 THEN
4114 FND_MSG_PUB.Add_Exc_Msg(
4116 p_procedure_name => l_api_name,
4117 p_error_text => l_stmt_num||': Item ID '||l_inventory_item_id||
4118 ' cost from CIC'
4119 );
4120 END IF;
4121
4122 END IF; --l_min_cost_update_id
4123 */
4124 --
4125 --}BUG#7484428
4126 --
4127 END IF; --
4128 --} BUG#6631966
4129 END LOOP;
4130
4131 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
4132 THEN
4133 FND_MSG_PUB.Add_Exc_Msg(
4134 p_pkg_name => G_PKG_NAME,
4135 p_procedure_name => l_api_name,
4136 p_error_text => l_stmt_num||': Calculated '||c_standard%ROWCOUNT||
4137 ' past standard costs'
4138 );
4139 END IF;
4140 CLOSE c_standard;
4141
4142 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH
4143 THEN
4144 FND_MSG_PUB.Add_Exc_Msg(
4145 p_pkg_name => G_PKG_NAME,
4146 p_procedure_name => l_api_name,
4147 p_error_text => l_stmt_num||': Calculating '||
4148 'past actual costs'
4149 );
4150 END IF;
4151
4152 --Actual Cost processing
4153 l_stmt_num := 50;
4154 INSERT
4155 INTO cst_inv_cost_temp(
4156 organization_id,
4157 inventory_item_id,
4158 cost_group_id,
4159 cost_type_id,
4160 cost_source,
4161 inventory_asset_flag,
4162 item_cost,
4163 material_cost,
4164 material_overhead_cost,
4165 resource_cost,
4166 outside_processing_cost,
4167 overhead_cost
4168 )
4169 SELECT DISTINCT
4170 CIQT.organization_id,
4171 CIQT.inventory_item_id,
4172 CIQT.cost_group_id,
4173 CIQT.cost_type_id,
4174 2, -- PAST
4175 2, -- EXPENSE
4176 0,
4177 0,
4178 0,
4179 0,
4180 0,
4181 0
4182 FROM cst_inv_qty_temp CIQT,
4183 mtl_parameters MP,
4184 cst_item_costs CIC
4185 WHERE MP.organization_id = CIQT.organization_id
4186 AND MP.primary_cost_method <> 1
4187 AND CIC.organization_id = CIQT.organization_id
4188 AND CIC.inventory_item_id = CIQT.inventory_item_id
4189 AND CIC.cost_type_id = CIQT.cost_type_id
4190 AND CIC.inventory_asset_flag <> 1
4191 AND CIQT.qty_source NOT IN (1,2,9,10) -- PRIOR SUMMARY, CURRENT SUMMARY, RECEIVING, PAST RECEIVING
4192 AND CIQT.organization_id = p_organization_id;
4193
4194 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
4195 THEN
4196 FND_MSG_PUB.Add_Exc_Msg(
4197 p_pkg_name => G_PKG_NAME,
4198 p_procedure_name => l_api_name,
4199 p_error_text => l_stmt_num||': Calculated '||SQL%ROWCOUNT||
4200 ' expense items'
4201 );
4202 END IF;
4203
4204 l_stmt_num := 55;
4205 SELECT primary_cost_method
4206 INTO l_cost_method
4207 FROM mtl_parameters
4208 WHERE organization_id = p_organization_id;
4209
4210 l_stmt_num := 60;
4211 INSERT
4212 INTO cst_inv_cost_temp(
4213 organization_id,
4214 inventory_item_id,
4215 cost_group_id,
4216 cost_type_id,
4217 cost_source,
4218 inventory_asset_flag,
4219 item_cost,
4220 material_cost,
4221 material_overhead_cost,
4222 resource_cost,
4223 outside_processing_cost,
4224 overhead_cost
4225 )
4226 SELECT /*+ ORDERED use_nl(CQL,MCACD) use_hash(TFR_TXN,OWN_TXN) */
4227 CIQT.organization_id,
4228 CIQT.inventory_item_id,
4229 CIQT.cost_group_id,
4230 CIQT.primary_cost_method,
4231 2, -- PAST
4232 1, -- ASSET
4233 SUM(NVL(MCACD.prior_cost,0)),
4234 SUM(
4235 DECODE(MCACD.cost_element_id,1,NVL(MCACD.prior_cost,0),0)
4236 ),
4237 SUM(
4238 DECODE(MCACD.cost_element_id,2,NVL(MCACD.prior_cost,0),0)
4239 ),
4240 SUM(
4241 DECODE(MCACD.cost_element_id,3,NVL(MCACD.prior_cost,0),0)
4242 ),
4243 SUM(
4244 DECODE(MCACD.cost_element_id,4,NVL(MCACD.prior_cost,0),0)
4245 ),
4246 SUM(
4247 DECODE(MCACD.cost_element_id,5,NVL(MCACD.prior_cost,0),0)
4248 )
4249 FROM (
4250 SELECT /*+ no_merge */ DISTINCT
4251 CIQT.organization_id,
4252 CIQT.inventory_item_id,
4253 CIQT.cost_group_id,
4254 MP.primary_cost_method
4258 AND MP.primary_cost_method <> 1
4255 FROM cst_inv_qty_temp CIQT,
4256 mtl_parameters MP
4257 WHERE MP.organization_id = CIQT.organization_id
4259 AND CIQT.qty_source NOT IN (1,2,9,10)
4260 -- PRIOR SUMMARY, CURRENT SUMMARY, RECEIVING, PAST RECEIVING
4261 AND NOT EXISTS (
4262 SELECT 1
4263 FROM cst_inv_cost_temp CICT
4264 WHERE CICT.organization_id = CIQT.organization_id
4265 AND CICT.inventory_item_id = CIQT.inventory_item_id
4266 AND CICT.cost_source = 2
4267 )
4268 AND CIQT.organization_id = p_organization_id
4269 ) CIQT,
4270 cst_quantity_layers CQL,
4271 (
4272 SELECT /*+ ORDERED use_hash(MMT,MIP) swap_join_inputs(MIP) */
4273 MIN(MMT.transaction_id) transaction_id,
4274 MMT.transaction_date,
4275 MMT.transfer_organization_id,
4276 MMT.inventory_item_id,
4277 MMT.transfer_cost_group_id,
4278 'N' restrict_mcacd
4279 FROM
4280 (
4281 SELECT /*+ no_merge leading(MIP) use_hash(MMT)*/
4282 MIN(MMT.transaction_date) transaction_date,
4283 MMT.transfer_organization_id,
4284 MMT.inventory_item_id,
4285 MMT.transfer_cost_group_id
4286 FROM mtl_material_transactions MMT,
4287 mtl_interorg_parameters MIP
4288 WHERE MMT.transaction_action_id = 21
4289 AND MIP.from_organization_id = MMT.organization_id
4290 AND MIP.to_organization_id = MMT.transfer_organization_id
4291 AND NVL(MMT.fob_point,MIP.fob_point)=1
4292 AND MMT.costed_flag IS NULL
4293 AND MMT.transaction_date > p_valuation_date
4294 AND MMT.transfer_organization_id = p_organization_id
4295 GROUP
4296 BY MMT.transfer_organization_id,
4297 MMT.inventory_item_id,
4298 MMT.transfer_cost_group_id
4299 ) MINDATE,
4300 mtl_material_transactions MMT,
4301 mtl_interorg_parameters MIP
4302 WHERE MMT.transfer_organization_id = MINDATE.transfer_organization_id
4303 AND MMT.inventory_item_id = MINDATE.inventory_item_id
4304 AND MMT.transfer_cost_group_id = MINDATE.transfer_cost_group_id
4305 AND MMT.transaction_date = MINDATE.transaction_date
4306 AND MMT.transaction_action_id = 21
4307 AND MIP.from_organization_id = MMT.organization_id
4308 AND MIP.to_organization_id = MMT.transfer_organization_id
4309 AND NVL(MMT.fob_point,MIP.fob_point)=1
4310 AND MMT.costed_flag IS NULL
4311 AND MMT.transaction_date > p_valuation_date
4312 AND MMT.transfer_organization_id = p_organization_id
4313 GROUP
4314 BY MMT.transaction_date,
4315 MMT.transfer_organization_id,
4316 MMT.inventory_item_id,
4317 MMT.transfer_cost_group_id
4318 ) TFR_TXN,
4319 (
4320 SELECT /*+ leading(MINDATE) use_nl(MMT) index(MMT,MTL_MATERIAL_TRANSACTIONS_N1) */
4321 MIN(MMT.transaction_id) transaction_id,
4322 MMT.transaction_date,
4323 MMT.organization_id,
4324 MMT.inventory_item_id,
4325 /* In average costing organizations, common issue to WIP
4326 results in reaveraging of the item cost in the transfer
4327 cost group */
4328 DECODE(
4329 l_cost_method,
4330 2,
4331 DECODE(
4332 MMT.transaction_action_id,
4333 1,
4334 DECODE(
4335 MMT.transaction_source_type_id,
4336 5,
4337 NVL(MMT.TRANSFER_COST_GROUP_ID, MMT.cost_group_id),
4338 MMT.cost_group_id
4339 ),
4340 MMT.cost_group_id
4341 ),
4342 MMT.cost_group_id
4343 ) cost_group_id,
4344 /* For common issue to WIP transactions in average costing
4345 organizations, we insert rows both for the cost group
4346 transfer and issue to WIP. We are only interested in the
4347 rows corresponding to the cost group transfer. The
4348 restrict_mcacd flag is used to determine if we need to
4349 check on MCACD.transaction_action_id */
4350 DECODE(
4351 l_cost_method,
4352 2,
4353 DECODE(
4357 MMT.transaction_source_type_id,
4354 MMT.transaction_action_id,
4355 1,
4356 DECODE(
4358 5,
4359 DECODE(
4360 MMT.transfer_cost_group_id,
4361 NULL,
4362 'N',
4363 /* Bug 3500534
4364 It is possible to have normal issue to WIP transactions in
4365 average costing organizations with transfer_cost_group_id
4366 = cost_group_id. Adding the following condition for
4367 cost_group_id ensures such cases are handled as normal issue
4368 to WIP rather than common. */
4369 MMT.cost_group_id,
4370 'N',
4371 'Y'
4372 ),
4373 'N'
4374 ),
4375 'N'
4376 ),
4377 'N'
4378 ) restrict_mcacd
4379 FROM mtl_material_transactions MMT,
4380 (
4381 SELECT /*+ no_merge */ MIN(MMT.transaction_date) transaction_date,
4382 MMT.organization_id,
4383 MMT.inventory_item_id,
4384 DECODE(
4385 l_cost_method,
4386 2,
4387 DECODE(
4388 MMT.transaction_action_id,
4389 1,
4390 DECODE(
4391 MMT.transaction_source_type_id,
4392 5,
4393 NVL(MMT.TRANSFER_COST_GROUP_ID, MMT.cost_group_id),
4394 MMT.cost_group_id
4395 ),
4396 MMT.cost_group_id
4397 ),
4398 MMT.cost_group_id
4399 ) cost_group_id
4400 FROM mtl_material_transactions MMT
4401 WHERE MMT.transaction_action_id <> 30
4402 AND MMT.prior_cost IS NOT NULL
4403 AND MMT.costed_flag IS NULL
4404 -- Ignore consigned transactions
4405 AND MMT.organization_id =
4406 NVL(MMT.owning_organization_id, MMT.organization_id)
4407 AND NVL(MMT.owning_tp_type,2) = 2
4408 AND NVL(MMT.logical_transaction,-1) <> 1
4409 AND MMT.transaction_date > p_valuation_date
4410 AND MMT.organization_id = p_organization_id
4411 GROUP
4412 BY MMT.organization_id,
4413 MMT.inventory_item_id,
4414 DECODE(
4415 l_cost_method,
4416 2,
4417 DECODE(
4418 MMT.transaction_action_id,
4419 1,
4420 DECODE(
4421 MMT.transaction_source_type_id,
4422 5,
4423 NVL(MMT.TRANSFER_COST_GROUP_ID, MMT.cost_group_id),
4424 MMT.cost_group_id
4425 ),
4426 MMT.cost_group_id
4427 ),
4428 MMT.cost_group_id
4429 )
4430 ) MINDATE
4431 WHERE MMT.organization_id = MINDATE.organization_id
4432 AND MMT.inventory_item_id = MINDATE.inventory_item_id
4433 AND DECODE(
4434 l_cost_method,
4435 2,
4436 DECODE(
4437 MMT.transaction_action_id,
4438 1,
4439 DECODE(
4440 MMT.transaction_source_type_id,
4441 5,
4442 NVL(MMT.transfer_cost_group_id, MMT.cost_group_id),
4443 MMT.cost_group_id
4444 ),
4445 MMT.cost_group_id
4446 ),
4447 MMT.cost_group_id
4448 ) = MINDATE.cost_group_id
4449 AND MMT.transaction_date = MINDATE.transaction_date
4450 AND MMT.transaction_action_id <> 30
4451 AND MMT.prior_cost IS NOT NULL
4452 AND MMT.costed_flag IS NULL
4453 -- Ignore consigned transactions
4454 AND MMT.organization_id =
4455 NVL(MMT.owning_organization_id, MMT.organization_id)
4456 AND NVL(MMT.owning_tp_type,2) = 2
4457 AND NVL(MMT.logical_transaction,-1) <> 1
4458 AND MMT.organization_id = p_organization_id
4459 GROUP
4460 BY MMT.transaction_date,
4461 MMT.organization_id,
4462 MMT.inventory_item_id,
4463 DECODE(
4464 l_cost_method,
4465 2,
4466 DECODE(
4467 MMT.transaction_action_id,
4468 1,
4469 DECODE(
4470 MMT.transaction_source_type_id,
4471 5,
4472 NVL(MMT.transfer_cost_group_id, MMT.cost_group_id),
4473 MMT.cost_group_id
4474 ),
4475 MMT.cost_group_id
4476 ),
4477 MMT.cost_group_id
4478 ),
4479 DECODE(
4480 l_cost_method,
4481 2,
4482 DECODE(
4483 MMT.transaction_action_id,
4484 1,
4485 DECODE(
4486 MMT.transaction_source_type_id,
4487 5,
4488 DECODE(
4489 MMT.transfer_cost_group_id,
4490 NULL,
4491 'N',
4492 /* Bug 3500534
4493 It is possible to have normal issue to WIP transactions in
4494 average costing organizations with transfer_cost_group_id
4495 = cost_group_id. Adding the following condition for
4496 cost_group_id ensures such cases are handled as normal issue
4497 to WIP rather than common. */
4498 MMT.cost_group_id,
4499 'N',
4500 'Y'
4501 ),
4502 'N'
4503 ),
4504 'N'
4505 ),
4506 'N'
4507 )
4508 ) OWN_TXN,
4509 mtl_cst_actual_cost_details MCACD
4510 WHERE MCACD.transaction_id = DECODE(
4511 NVL(TFR_TXN.transaction_id,-1),
4512 -1,
4513 OWN_TXN.transaction_id,
4514 DECODE(
4515 NVL(OWN_TXN.transaction_id,-1),
4516 -1,
4517 TFR_TXN.transaction_id,
4518 DECODE(
4519 TFR_TXN.transaction_date,
4520 OWN_TXN.transaction_date,
4521 LEAST(
4522 TFR_TXN.transaction_id,
4523 OWN_TXN.transaction_id
4524 ),
4525 DECODE(
4526 LEAST(
4527 TFR_TXN.transaction_date,
4531 TFR_TXN.transaction_id,
4528 OWN_TXN.transaction_date
4529 ),
4530 TFR_TXN.transaction_date,
4532 OWN_TXN.transaction_id
4533 )
4534 )
4535 )
4536 )
4537 AND MCACD.layer_id = CQL.layer_id
4538 AND MCACD.organization_id = p_organization_id
4539 AND MCACD.transaction_action_id =
4540 DECODE(
4541 MCACD.transaction_id,
4542 OWN_TXN.transaction_id,
4543 DECODE(OWN_TXN.restrict_mcacd, 'Y', 2, MCACD.transaction_action_id),
4544 MCACD.transaction_action_id
4545 )
4546 AND CQL.organization_id = CIQT.organization_id
4547 AND CQL.cost_group_id = CIQT.cost_group_id
4548 AND CQL.inventory_item_id = CIQT.inventory_item_id
4549 AND TFR_TXN.transfer_organization_id (+) = CIQT.organization_id
4550 AND TFR_TXN.transfer_cost_group_id (+) = CIQT.cost_group_id
4551 AND TFR_TXN.inventory_item_id (+) = CIQT.inventory_item_id
4552 AND OWN_TXN.organization_id (+) = CIQT.organization_id
4553 AND OWN_TXN.cost_group_id (+) = CIQT.cost_group_id
4554 AND OWN_TXN.inventory_item_id (+) = CIQT.inventory_item_id
4555 GROUP
4556 BY CIQT.organization_id,
4557 CIQT.inventory_item_id,
4558 CIQT.cost_group_id,
4559 CIQT.primary_cost_method;
4560
4561 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
4562 THEN
4563 FND_MSG_PUB.Add_Exc_Msg(
4564 p_pkg_name => G_PKG_NAME,
4565 p_procedure_name => l_api_name,
4566 p_error_text => l_stmt_num||': Calculated '||SQL%ROWCOUNT||
4567 ' past actual costs from MCACD'
4568 );
4569 END IF;
4570
4571 l_stmt_num := 70;
4572 INSERT
4573 INTO cst_inv_cost_temp(
4574 organization_id,
4575 inventory_item_id,
4576 cost_group_id,
4577 cost_type_id,
4578 cost_source,
4579 inventory_asset_flag,
4580 item_cost,
4581 material_cost,
4582 material_overhead_cost,
4583 resource_cost,
4584 outside_processing_cost,
4585 overhead_cost
4586 )
4587 SELECT CIQT.organization_id,
4588 CIQT.inventory_item_id,
4589 CIQT.cost_group_id,
4590 CIQT.primary_cost_method,
4591 2, -- PAST
4592 1, -- ASSET
4593 NVL(CQL.item_cost,0),
4594 NVL(CQL.material_cost,0),
4595 NVL(CQL.material_overhead_cost,0),
4596 NVL(CQL.resource_cost,0),
4597 NVL(CQL.outside_processing_cost,0),
4598 NVL(CQL.overhead_cost,0)
4599 FROM cst_quantity_layers CQL,
4600 (
4601 SELECT DISTINCT
4602 CIQT.organization_id,
4603 CIQT.inventory_item_id,
4604 CIQT.cost_group_id,
4605 MP.primary_cost_method
4606 FROM cst_inv_qty_temp CIQT,
4607 mtl_parameters MP
4608 WHERE MP.organization_id = CIQT.organization_id
4609 AND MP.primary_cost_method <> 1
4610 AND CIQT.qty_source NOT IN (1,2,9,10)
4611 -- PRIOR SUMMARY, CURRENT SUMMARY, RECEIVING, PAST RECEIVING
4612 AND CIQT.organization_id = p_organization_id
4613 ) CIQT
4614 WHERE CQL.organization_id (+) = CIQT.organization_id
4615 AND CQL.inventory_item_id (+) = CIQT.inventory_item_id
4616 AND CQL.cost_group_id (+) = CIQT.cost_group_id
4617 /* The outer join above is for asset items that do not have a layer */
4618 AND NOT EXISTS (
4619 SELECT 1
4620 FROM cst_inv_cost_temp CICT
4621 WHERE CICT.organization_id = CIQT.organization_id
4622 AND CICT.inventory_item_id = CIQT.inventory_item_id
4623 AND CICT.cost_group_id = CIQT.cost_group_id
4624 AND CICT.cost_source = 2
4625 );
4626
4627 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
4628 THEN
4629 FND_MSG_PUB.Add_Exc_Msg(
4630 p_pkg_name => G_PKG_NAME,
4631 p_procedure_name => l_api_name,
4632 p_error_text => l_stmt_num||': Calculated '||SQL%ROWCOUNT||
4633 ' actual costs from CQL'
4634 );
4635 END IF;
4636
4637 l_stmt_num := 80;
4638 /* Set rcv_cost_source to 4 for Past Receiving Cost */
4639 l_rcv_cost_source := 4;
4640
4641 END IF; /* End if p_valuation_date is null/else */
4642
4643
4644 /* In all cases, regardless of whether p_valuation_date is null or not,
4645 Calculate Receiving Cost. */
4646 l_stmt_num := 90;
4647
4648 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW
4649 THEN
4650 FND_MSG_PUB.Add_Exc_Msg(
4651 p_pkg_name => G_PKG_NAME,
4652 p_procedure_name => l_api_name,
4653 p_error_text => l_stmt_num||': Calculating '||
4654 'receiving cost'
4655 );
4656 END IF;
4657
4658 OPEN c_receiving;
4659 LOOP
4660 FETCH c_receiving
4661 INTO l_organization_id,
4662 l_inventory_item_id,
4663 l_rcv_transaction_id;
4664 EXIT
4665 WHEN c_receiving%NOTFOUND;
4666
4667 RCV_AccrualUtilities_GRP.Get_ReceivingUnitPrice(
4668 p_api_version => 1.0,
4669 p_rcv_transaction_id => l_rcv_transaction_id,
4670 p_valuation_date => p_valuation_date, /* added for 12i: as of date */
4671 x_unit_price => l_receiving_cost,
4672 x_return_status => x_return_status,
4673 x_msg_count => l_msg_count,
4674 x_msg_data => l_msg_data
4675 );
4676
4677 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
4678 THEN
4679 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4680 END IF;
4681
4682 INSERT
4683 INTO cst_inv_cost_temp(
4684 cost_source,
4685 organization_id,
4686 inventory_item_id,
4687 rcv_transaction_id,
4688 item_cost
4689 )
4690 SELECT l_rcv_cost_source,
4691 l_organization_id,
4692 l_inventory_item_id,
4693 l_rcv_transaction_id,
4694 l_receiving_cost
4695 FROM dual;
4696 END LOOP;
4697
4698 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_SUCCESS
4699 THEN
4700 FND_MSG_PUB.Add_Exc_Msg(
4701 p_pkg_name => G_PKG_NAME,
4702 p_procedure_name => l_api_name,
4703 p_error_text => l_stmt_num||': Calculated '||c_receiving%ROWCOUNT||
4704 ' receiving costs'
4705 );
4706 END IF;
4707 CLOSE c_receiving;
4708
4709 x_return_status := FND_API.G_RET_STS_SUCCESS;
4710 EXCEPTION
4711 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4712 ROLLBACK TO Calculate_InventoryCost_PVT;
4713 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4714 WHEN OTHERS THEN
4715 ROLLBACK TO Calculate_InventoryCost_PVT;
4716 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4717 IF l_msg_level_threshold <= FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
4718 THEN
4719 FND_MSG_PUB.Add_Exc_Msg(
4720 p_pkg_name => G_PKG_NAME,
4721 p_procedure_name => l_api_name,
4722 p_error_text => SUBSTR(l_stmt_num||SQLERRM,1,240)
4723 );
4724 END IF;
4725 END Calculate_InventoryCost;
4726
4727 END CST_Inventory_PVT;