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