[Home] [Help]
PACKAGE BODY: APPS.CST_PAC_ITERATION_PROCESS_PVT
Source
1 PACKAGE BODY CST_PAC_ITERATION_PROCESS_PVT AS
2 -- $Header: CSTVIIPB.pls 120.62.12020000.3 2012/10/15 23:24:03 fayang ship $
3
4 -- API Name : Iteration_Process
5 -- Type : Private
6 -- Function :
7 -- Pre-reqs : None
8 -- Parameters :
9 -- IN : p_init_msg_list IN VARCHAR2
10 -- p_validation_level IN NUMBER
11 -- p_legal_entity_id IN NUMBER
12 -- p_cost_type_id IN NUMBER
13 -- p_cost_method IN NUMBER
14 -- p_period_id IN NUMBER
15 -- p_start_date IN DATE
16 -- p_end_date IN DATE
17 -- p_inventory_item_id IN NUMBER
18 -- p_inventory_item_number IN VARCHAR2(1025)
19 -- p_tolerance IN NUMBER
20 -- p_iteration_num IN NUMBER
21 -- p_run_options IN NUMBER
22 -- p_user_id IN NUMBER
23 -- p_login_id IN NUMBER
24 -- p_req_id IN NUMBER
25 -- p_prg_id IN NUMBER
26 -- p_prg_appid IN NUMBER
27 -- OUT : x_return_status OUT VARCHAR2(1)
28 -- x_msg_count OUT NUMBER
29 -- x_msg_data OUT VARCHAR2(2000)
30 -- Version : Current Version : 1.0
31 -- Initial version 1.0
32 -- Notes :
33 -- +========================================================================+
34
35 -- +========================================================================+
36 -- PRIVATE CONSTANTS AND VARIABLES
37 -- +========================================================================+
38 G_MODULE_HEAD CONSTANT VARCHAR2(50) := 'cst.plsql.' || G_PKG_NAME || '.';
39
40 -- +========================================================================+
41 -- PROCEDURES AND FUNCTIONS OF ITERATION PROCESS
42 -- +========================================================================+
43
44 -- +========================================================================+
45 -- FUNCTION: Check_Cst_Group Local Utility
46 -- PARAMETERS:
47 -- p_cost_group_id user input
48 -- COMMENT:
49 -- Take p_cost_group_id and look in the PL/SQL table l_cst_group_tbl.
50 -- A return value 'Y' means that the cost group id belongs to user entered
51 -- legal entity and therefore its a valid cost group.
52 -- A return value 'N' means that the cost group is not valid since it is not
53 -- belong to Legal Entity
54 -- USAGE: This function is used within the SQL
55 -- PRE-COND: none
56 -- EXCEPTIONS: none
57 -- +========================================================================+
58 FUNCTION Check_Cst_Group
59 ( p_cost_group_id IN NUMBER
60 )
61 RETURN VARCHAR2
62 IS
63 l_cost_group_id_idx BINARY_INTEGER;
64 l_return VARCHAR2(1) := 'N';
65
66 BEGIN
67 l_cost_group_id_idx := p_cost_group_id;
68 IF CST_PAC_ITERATION_PROCESS_PVT.G_CST_GROUP_TBL.EXISTS(l_cost_group_id_idx)
69 THEN
70 -- valid Cost Group in Legal Entity
71 l_return := 'Y';
72 ELSE
73 -- not a valid Cost Group
74 l_return := 'N';
75 END IF;
76
77 RETURN(l_return);
78
79 END; -- Check_Cst_Group
80
81 -- +========================================================================+
82 -- FUNCTION: Get_Master_Org Local Utility
83 -- PARAMETERS:
84 -- p_cost_group_id IN NUMBER Cost Group Id
85 -- COMMENT:
86 -- Get Item Master Organization of the Cost Group
87 -- USAGE: This procedure is invoked by Compute_iterative_pwac_cost for
88 -- each optimal cost group of the item
89 -- PRE-COND: none
90 -- EXCEPTIONS: none
91 -- +========================================================================+
92 FUNCTION Get_Master_Org
93 ( p_cost_group_id IN NUMBER
94 )
95 RETURN NUMBER
96 IS
97 l_cost_group_id_idx BINARY_INTEGER;
98 l_master_org_id NUMBER;
99
100 BEGIN
101 l_cost_group_id_idx := p_cost_group_id;
102 l_master_org_id :=
103 CST_PAC_ITERATION_PROCESS_PVT.G_CST_GROUP_TBL(l_cost_group_id_idx).master_organization_id;
104
105 RETURN l_master_org_id;
106
107 END Get_Master_Org;
108
109
110 -- +========================================================================+
111 -- FUNCTION: Get_Cost_Group Local Utility
112 -- PARAMETERS:
113 -- p_organization_id IN NUMBER
114 -- COMMENT:
115 -- Get Cost Group of the corresponding p_organization_id
116 -- USAGE: This function is used in the sql cursor
117 -- PRE-COND: none
118 -- EXCEPTIONS: none
119 -- +========================================================================+
120 FUNCTION Get_Cost_Group
121 ( p_organization_id IN NUMBER
122 )
123 RETURN NUMBER
124 IS
125 l_routine CONSTANT VARCHAR2(30) := 'get_cost_group';
126 l_organization_id_idx BINARY_INTEGER;
127 l_cost_group_id NUMBER;
128
129 BEGIN
130 l_organization_id_idx := p_organization_id;
131 IF CST_PAC_ITERATION_PROCESS_PVT.G_CST_GROUP_ORG_TBL.EXISTS(l_organization_id_idx) THEN
132 l_cost_group_id :=
133 CST_PAC_ITERATION_PROCESS_PVT.G_CST_GROUP_ORG_TBL(l_organization_id_idx).cost_group_id;
134 ELSE
135 l_cost_group_id := -99;
136 END IF;
137
138 RETURN l_cost_group_id;
139
140 END Get_Cost_Group;
141
142
143 -- +========================================================================+
144 -- FUNCTION: Check_Cst_Group_Org Local Utility
145 -- PARAMETERS:
146 -- p_organization_id
147 -- COMMENT:
148 -- Take p_organization_id and look in the PL/SQL table l_cst_group_org_tbl.
149 -- A return value 'Y' means that the organization id belongs to one of the
150 -- valid cost group in legal entity
151 -- A return value 'N' means that the organization id is NOT belong to
152 -- valid cost group
153 -- USAGE: This function is used within the SQL
154 -- PRE-COND: none
155 -- EXCEPTIONS: none
156 -- +========================================================================+
157 FUNCTION Check_Cst_Group_Org
158 ( p_organization_id IN NUMBER
159 )
160 RETURN VARCHAR2
161 IS
162 l_organization_id_idx BINARY_INTEGER;
163 l_return VARCHAR2(1) := 'N';
164
165 BEGIN
166 l_organization_id_idx := p_organization_id;
167 IF CST_PAC_ITERATION_PROCESS_PVT.G_CST_GROUP_ORG_TBL.EXISTS(l_organization_id_idx)
168 THEN
169 -- Organization exists in one of the valid cost group
170 l_return := 'Y';
171 ELSE
172 -- not a valid organization
173 l_return := 'N';
174 END IF;
175
176 RETURN(l_return);
177
178 END Check_Cst_Group_Org ;
179
180 -- +========================================================================+
181 -- FUNCTION: Get_Previous_Iteration_Count Local Utility
182 -- PARAMETERS:
183 -- p_pac_period_id NUMBER PAC Period Id
184 -- COMMENT:
185 -- This is to get the Previous iteration count if any
186 -- iteration count initialized to 0
187 -- After first iteration process, iteration count will be set 1
188 -- For the remaining iteration process, it will be the maximum
189 -- iteration_num which is equal to the number of iterations user specified
190 -- in the previous iteration process
191 -- This procedure will retrieve the maximum iteration count of the
192 -- current BOM level
193 -- USAGE: This function is used in Iteration_Process
194 -- PRE-COND: none
195 -- EXCEPTIONS: none
196 -- +========================================================================+
197 FUNCTION Get_Previous_Iteration_Count
198 ( p_period_id IN NUMBER
199 , p_inventory_item_id IN NUMBER
200 )
201 RETURN NUMBER
202 IS
203
204 -- Cursor to get maximum iteration number of the current BOM level
205 -- interorg item
206 -- For current BOM level item, iteration_count will be 0 for the very
207 -- first iteration and the count will be > 0 for the consecutive
208 -- iterations
209 CURSOR max_iteration_num_cur(c_period_id NUMBER
210 ,c_inventory_item_id NUMBER
211 )
212 IS
213 SELECT
214 MAX(iteration_count)
215 FROM
216 CST_PAC_INTORG_ITMS_TEMP
217 WHERE pac_period_id = c_period_id
218 AND inventory_item_id = c_inventory_item_id
219 AND tolerance_flag = 'N';
220
221 l_max_iteration_num NUMBER;
222 l_prev_iteration_count NUMBER := 0;
223
224 BEGIN
225 -- Get maximum iteration number
226 OPEN max_iteration_num_cur(p_period_id
227 ,p_inventory_item_id
228 );
229 FETCH max_iteration_num_cur
230 INTO l_max_iteration_num;
231
232 IF max_iteration_num_cur%FOUND THEN
233 l_prev_iteration_count := l_max_iteration_num;
234 ELSE
235 l_prev_iteration_count := -99;
236 END IF;
237
238 CLOSE max_iteration_num_cur;
239
240 RETURN l_prev_iteration_count;
241
242 END; -- Get_Previous_Iteration_Count
243
244 -- +========================================================================+
245 -- PROCEDURE: Get_Correspond_Pmac_Cost Local Utility
246 -- PARAMETERS:
247 -- p_cost_group_id Cost Group Id
248 -- p_cost_type_id Cost Type Id
249 -- p_opp_transaction_id Corresponding Transaction Id
250 -- p_period_id PAC Period Id
251 -- p_organization_id Organization of Cost owned transaction id
252 -- p_opp_organization_id Corresponding organization Id
253 -- p_transaction_id Cost owned Transaction Id
254 -- p_transaction_action_id Direct-interorg,intransit shipment/receipt
255 -- p_group_num Group Number
256 -- x_correspond_pmac_cost PMAC Cost of the corresponding cost group
257 -- COMMENT:
258 -- This procedure is to get the PMAC Cost of corresponding transaction id and
259 -- organization id from the same temporary table
260 -- Corresponding transaction id is the cost derived transaction. Hence,
261 -- the actual cost is the pmac cost of the corresponding cost group
262 --
263 -- USAGE:
264 -- This procedure is used to compute the transfer cost as a %age of the
265 -- pmac cost of the corresponding cost group
266 -- PRE-COND: none
267 -- EXCEPTIONS: none
268 -- +========================================================================+
269 PROCEDURE Get_Correspond_Pmac_Cost
270 ( p_cost_group_id IN NUMBER
271 , p_cost_type_id IN NUMBER
272 , p_opp_transaction_id IN NUMBER
273 , p_period_id IN NUMBER
274 , p_organization_id IN NUMBER
275 , p_opp_organization_id IN NUMBER
276 , p_transaction_id IN NUMBER
277 , p_transaction_action_id IN NUMBER
278 , p_group_num IN NUMBER
279 , x_correspond_pmac_cost OUT NOCOPY NUMBER
280 )
281 IS
282
283 l_routine CONSTANT VARCHAR2(30) := 'Get_Correspond_Pmac_Cost';
284
285 -- cursor to get actual cost from MTL_PAC_ACT_CST_DTL_TEMP
286 CURSOR new_pwac_cost_cur( c_transaction_id NUMBER
287 , c_period_id NUMBER
288 , c_cost_type_id NUMBER
289 , c_cost_group_id NUMBER
290 )
291 IS
292 SELECT
293 SUM(actual_cost)
294 FROM
295 mtl_pac_act_cst_dtl_temp
296 WHERE cost_group_id = c_cost_group_id
297 AND cost_type_id = c_cost_type_id
298 AND pac_period_id <= c_period_id
299 AND transaction_id = c_transaction_id;
300
301 l_correspond_org_id NUMBER;
302 l_correspond_pmac_cost NUMBER := 0;
303 l_cost_group_id NUMBER;
304
305 new_cost_direct_excep EXCEPTION;
306 new_cost_intransit_excep EXCEPTION;
307
308 BEGIN
309
310
311 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
312 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
313 , G_MODULE_HEAD || l_routine ||'.begin'
314 , l_routine || '<'
315 );
316 END IF;
317
318 -- Get New Cost for Direct Interorg Receipt
319 IF p_transaction_action_id = 3 THEN
320 l_cost_group_id := get_cost_group(p_opp_organization_id);
321
322 OPEN new_pwac_cost_cur(p_opp_transaction_id
323 ,p_period_id
324 ,p_cost_type_id
325 ,l_cost_group_id
326 );
327
328 FETCH new_pwac_cost_cur
329 INTO l_correspond_pmac_cost;
330
331
332 CLOSE new_pwac_cost_cur;
333
334 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
335 FND_LOG.string(FND_LOG.LEVEL_EVENT
336 , G_MODULE_HEAD || l_routine ||'.Directnewcost'
337 , 'Correspond Txn Id:' || p_opp_transaction_id ||
338 ' Correspond organization Id:' || p_opp_organization_id
339 || ' New Cost:' || l_correspond_pmac_cost
340 );
341 END IF;
342
343 -- intransit interorg transactions
344 ELSIF (p_transaction_action_id = 21 OR p_transaction_action_id = 12) THEN
345
346 -- Set Corresponding Organization Id
347 -- intransit receipt - 12 group 1 transactions
348 -- Transfer organization id is the corresponding organization
349 -- intransit shipment - 21 group 1 transactions
350 -- Organization id is the corresponding organization
351 -- intransit receipt - 12 group 2 transactions
352 -- Organization id is the corresponding organization
353 -- intransit shipment - 21 group 2 transactions
354 -- Transfer organization id is the corresponding organization
358 l_correspond_org_id := p_organization_id;
355 IF (p_transaction_action_id = 12 AND p_group_num = 1) THEN
356 l_correspond_org_id := p_opp_organization_id;
357 ELSIF (p_transaction_action_id = 21 AND p_group_num = 1) THEN
359 ELSIF (p_transaction_action_id = 12 AND p_group_num = 2) THEN
360 l_correspond_org_id := p_organization_id;
361 ELSIF (p_transaction_action_id = 21 AND p_group_num = 2) THEN
362 l_correspond_org_id := p_opp_organization_id;
363 END IF;
364
365 -- p_opp_transaction_id is same as p_transaction_id
366 -- eg: TX1' = TX1
367 -- note: transfer_transaction_id not available for intransit txns
368 l_cost_group_id := get_cost_group(l_correspond_org_id);
369
370 OPEN new_pwac_cost_cur(p_transaction_id
371 ,p_period_id
372 ,p_cost_type_id
373 ,l_cost_group_id
374 );
375 FETCH new_pwac_cost_cur
376 INTO l_correspond_pmac_cost;
377
378 CLOSE new_pwac_cost_cur;
379
380 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
381 FND_LOG.string(FND_LOG.LEVEL_EVENT
382 , G_MODULE_HEAD || l_routine ||'.Intransitnewcost'
383 , 'Correspond Txn Id:' || p_opp_transaction_id ||
384 ' Correspond organization Id:' || l_correspond_org_id ||
385 ' New Cost:' || l_correspond_pmac_cost
386 );
387 END IF;
388
389 END IF; -- interorg transaction check
390
391 x_correspond_pmac_cost := l_correspond_pmac_cost;
392
393 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
394 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
395 , G_MODULE_HEAD || l_routine ||'.end'
396 , l_routine || '>'
397 );
398 END IF;
399
400 END Get_Correspond_Pmac_Cost;
401
402 -- +========================================================================+
403 -- PROCEDURE: Get_Correspond_Pwac_New_Cost Local Utility
404 -- PARAMETERS:
405 -- p_cost_group_id Cost Group Id
406 -- p_opp_transaction_id Corresponding Transaction Id
407 -- p_period_id PAC Period Id
408 -- p_organization_id Organization of Cost owned transaction id
409 -- p_opp_organization_id Corresponding organization Id
410 -- p_transaction_id Cost owned Transaction Id
411 -- p_transaction_action_id Direct-interorg,intransit shipment/receipt
412 -- p_cost_element_id Cost Element Id
413 -- p_level_type Level Type
414 -- p_group_num Group Number
415 -- x_new_correspond_cost New Cost of corresponding txn
416 -- COMMENT:
417 -- This procedure is to get the New Cost of corresponding transaction id and
418 -- organization id from the same temporary table for the cost_element_id
419 -- and level_type
420 --
421 -- USAGE:
422 -- This procedure is used in compute_iterative_pwac_cost during
423 -- consecutive iterations
424 -- PRE-COND: none
425 -- EXCEPTIONS: none
426 -- +========================================================================+
427 PROCEDURE Get_Correspond_Pwac_New_Cost
428 ( p_cost_group_id IN NUMBER
429 , p_cost_type_id IN NUMBER
430 , p_opp_transaction_id IN NUMBER
431 , p_period_id IN NUMBER
432 , p_organization_id IN NUMBER
433 , p_opp_organization_id IN NUMBER
434 , p_transaction_id IN NUMBER
435 , p_transaction_action_id IN NUMBER
436 , p_cost_element_id IN NUMBER
437 , p_level_type IN NUMBER
438 , p_group_num IN NUMBER
439 , x_new_correspond_cost OUT NOCOPY NUMBER
440 )
441 IS
442
443 l_routine CONSTANT VARCHAR2(30) := 'Get_Correspond_Pwac_New_Cost';
444
445 -- cursor to get actual cost from MTL_PAC_ACT_CST_DTL_TEMP
446 CURSOR new_pwac_cost_cur( c_transaction_id NUMBER
447 , c_period_id NUMBER
448 , c_cost_type_id NUMBER
449 , c_cost_group_id NUMBER
450 , c_cost_element_id NUMBER
451 , c_level_type NUMBER
452 )
453 IS
454 SELECT
455 actual_cost
456 FROM
457 mtl_pac_act_cst_dtl_temp
458 WHERE cost_group_id = c_cost_group_id
459 AND pac_period_id <= c_period_id
460 AND cost_type_id = c_cost_type_id
461 AND transaction_id = c_transaction_id
462 AND cost_element_id = c_cost_element_id
463 AND level_type = c_level_type;
464
465 l_correspond_org_id NUMBER;
466 l_new_correspond_cost NUMBER := 0;
467 l_cost_group_id NUMBER;
468
469 new_cost_direct_excep EXCEPTION;
470 new_cost_intransit_excep EXCEPTION;
471
472 BEGIN
473
474 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
475 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
476 , G_MODULE_HEAD || l_routine ||'.begin'
477 , l_routine || '<'
478 );
479 END IF;
480
481 -- Get New Cost for Direct Interorg Receipt
482 IF p_transaction_action_id = 3 THEN
483 l_cost_group_id := get_cost_group(p_opp_organization_id);
484
485 OPEN new_pwac_cost_cur(p_opp_transaction_id
486 ,p_period_id
487 ,p_cost_type_id
488 ,l_cost_group_id
489 ,p_cost_element_id
490 ,p_level_type
491 );
492
493 FETCH new_pwac_cost_cur
494 INTO l_new_correspond_cost;
495
496 CLOSE new_pwac_cost_cur;
497
501 , 'Correspond Txn Id:' || p_opp_transaction_id ||
498 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
499 FND_LOG.string(FND_LOG.LEVEL_EVENT
500 , G_MODULE_HEAD || l_routine ||'.Directnewcost'
502 ' Correspond organization Id:' || p_opp_organization_id || ' Cost element Id:' || p_cost_element_id ||
503 ' Level Type:' || p_level_type || ' New Cost:' || l_new_correspond_cost
504 );
505 END IF;
506
507 -- intransit interorg transactions
508 ELSIF (p_transaction_action_id = 21 OR p_transaction_action_id = 12) THEN
509
510 -- Set Corresponding Organization Id
511 -- intransit receipt - 12 group 1 transactions
512 -- Transfer organization id is the corresponding organization
513 -- intransit shipment - 21 group 1 transactions
514 -- Organization id is the corresponding organization
515 -- intransit receipt - 12 group 2 transactions
516 -- Organization id is the corresponding organization
517 -- intransit shipment - 21 group 2 transactions
518 -- Transfer organization id is the corresponding organization
519 IF (p_transaction_action_id = 12 AND p_group_num = 1) THEN
520 l_correspond_org_id := p_opp_organization_id;
521 ELSIF (p_transaction_action_id = 21 AND p_group_num = 1) THEN
522 l_correspond_org_id := p_organization_id;
523 ELSIF (p_transaction_action_id = 12 AND p_group_num = 2) THEN
524 l_correspond_org_id := p_organization_id;
525 ELSIF (p_transaction_action_id = 21 AND p_group_num = 2) THEN
526 l_correspond_org_id := p_opp_organization_id;
527 END IF;
528
529 -- p_opp_transaction_id is same as p_transaction_id
530 -- eg: TX1' = TX1
531 -- note: transfer_transaction_id not available for intransit txns
532 l_cost_group_id := get_cost_group(l_correspond_org_id);
533 OPEN new_pwac_cost_cur(p_transaction_id
534 ,p_period_id
535 ,p_cost_type_id
536 ,l_cost_group_id
537 ,p_cost_element_id
538 ,p_level_type
539 );
540 FETCH new_pwac_cost_cur
541 INTO l_new_correspond_cost;
542
543 -- IF new_cost_cur%NOTFOUND THEN
544 -- RAISE new_cost_intransit_excep;
545 -- END IF;
546
547 CLOSE new_pwac_cost_cur;
548
549 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
550 FND_LOG.string(FND_LOG.LEVEL_EVENT
551 , G_MODULE_HEAD || l_routine ||'.Intransitnewcost'
552 , 'Correspond Txn Id:' || p_opp_transaction_id ||
553 ' Correspond organization Id:' || l_correspond_org_id || ' Cost element Id:' || p_cost_element_id ||
554 ' Level Type:' || p_level_type || ' New Cost:' ||
555 l_new_correspond_cost
556 );
557 END IF;
558
559 END IF; -- interorg transaction check
560
561 x_new_correspond_cost := l_new_correspond_cost;
562
563 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
564 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
565 , G_MODULE_HEAD || l_routine ||'.end'
566 , l_routine || '>'
567 );
568 END IF;
569 END Get_Correspond_Pwac_New_Cost;
570
571 -- +========================================================================+
572 -- PROCEDURE: Get_Correspond_Actual_Cost
573 -- PARAMETERS:
574 -- p_period_id IN NUMBER
575 -- p_cost_type_id IN NUMBER
576 -- p_transaction_id IN NUMBER
577 -- p_transaction_action_id IN NUMBER
578 -- p_organization_id IN NUMBER Organization Id
579 -- p_opp_organization_id IN NUMBER Transfer Org Id
580 -- p_opp_transaction_id IN NUMBER Corresponding txn id
581 -- p_cost_element_id IN NUMBER Cost element id
582 -- p_level_type IN NUMBER Level type
583 -- p_group_num IN NUMBER Group Number
584 -- x_correspond_actual_cost OUT NOCOPY NUMBER Correspond actual cost
585 -- x_correspond_txn_flag OUT NOCOPY VARCHAR2
586 -- COMMENT:
587 -- This procedure is to retrieve actual cost of the corresponding
588 -- shipment PAC transaction with cost element and level type
589 -- If the corresponding transaction exists, x_correspond_txn_flag set
590 -- to 'Y'. Otherwise, flag is set to 'N'
591 -- USAGE: This procedure is invoked by verify_tolerance_of_item
592 -- This procedure is also used in balance_pac_txn inorder to
593 -- determine whether the corresponding pac transaction exists
594 -- x_correspond_txn_flag is used by blaance_pac_txn
595 -- PRE-COND: none
596 -- EXCEPTIONS: none
597 -- +========================================================================+
598 PROCEDURE Get_Correspond_Actual_Cost
599 ( p_period_id IN NUMBER
600 , p_cost_type_id IN NUMBER
601 , p_transaction_id IN NUMBER
602 , p_transaction_action_id IN NUMBER
603 , p_organization_id IN NUMBER
604 , p_opp_organization_id IN NUMBER
605 , p_opp_transaction_id IN NUMBER
606 , p_cost_element_id IN NUMBER
607 , p_level_type IN NUMBER
608 , p_group_num IN NUMBER
609 , x_correspond_actual_cost OUT NOCOPY NUMBER
610 , x_correspond_txn_flag OUT NOCOPY VARCHAR2
611 )
612 IS
613
614 l_routine CONSTANT VARCHAR2(30) := 'Get_correspond_actual_cost';
615
616 -- Retrieve corresponding actual cost for the direct interorg shipment
617 CURSOR direct_actual_cost_cur(c_period_id NUMBER
618 ,c_cost_type_id NUMBER
619 ,c_cost_group_id NUMBER
623 )
620 ,c_opp_transaction_id NUMBER
621 ,c_cost_element_id NUMBER
622 ,c_level_type NUMBER
624 IS
625 SELECT
626 actual_cost
627 , new_cost
628 FROM
629 mtl_pac_act_cst_dtl_temp
630 WHERE cost_group_id = c_cost_group_id
631 AND pac_period_id <= c_period_id
632 AND cost_type_id = c_cost_type_id
633 AND transaction_id = c_opp_transaction_id
634 AND cost_element_id = c_cost_element_id
635 AND level_type = c_level_type;
636
637
638 -- Retrieve corresponding actual cost for intransit transactions
639 CURSOR intransit_actual_cost_cur(c_period_id NUMBER
640 ,c_cost_type_id NUMBER
641 ,c_transaction_id NUMBER
642 ,c_cost_group_id NUMBER
643 ,c_cost_element_id NUMBER
644 ,c_level_type NUMBER
645 )
646 IS
647 SELECT
648 actual_cost
649 FROM
650 mtl_pac_act_cst_dtl_temp
651 WHERE cost_group_id = c_cost_group_id
652 AND pac_period_id <= c_period_id
653 AND cost_type_id = c_cost_type_id
654 AND transaction_id = c_transaction_id
655 AND cost_element_id = c_cost_element_id
656 AND level_type = c_level_type;
657
658 l_correspond_org_id NUMBER;
659 l_correspond_actual_cost NUMBER := 0;
660 l_correspond_txn_flag VARCHAR2(1);
661 l_new_cost NUMBER := 0;
662 l_cost_group_id NUMBER;
663
664 BEGIN
665
666 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
667 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
668 , G_MODULE_HEAD || l_routine ||'.begin'
669 , l_routine || '<'
670 );
671 END IF;
672
673 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
674 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
675 , G_MODULE_HEAD || l_routine ||'.txnid'
676 , 'Transaction ID:' || p_transaction_id
677 );
678 END IF;
679
680 -- ==================================================================
681 -- Check for Transaction Action id
682 -- ==================================================================
683 -- Is it a Direct interorg transaction
684 IF p_transaction_action_id = 3 THEN
685 -- Get actual cost of corresponding direct interorg shipment
686 l_cost_group_id := get_cost_group(p_opp_organization_id);
687 OPEN direct_actual_cost_cur(p_period_id
688 ,p_cost_type_id
689 ,l_cost_group_id
690 ,p_opp_transaction_id
691 ,p_cost_element_id
692 ,p_level_type
693 );
694 FETCH direct_actual_cost_cur
695 INTO l_correspond_actual_cost
696 ,l_new_cost;
697
698 IF direct_actual_cost_cur%FOUND THEN
699 l_correspond_txn_flag := 'Y';
700 ELSE
701 -- Check whether corresponding Cost Group exists
702 IF Get_Cost_Group(p_opp_organization_id) = -99 THEN
703 -- corresponding cost group not found
704 l_correspond_txn_flag := 'C';
705 ELSE
706 -- corresponding cost group exists, but txn not exists
707 l_correspond_txn_flag := 'N';
708 END IF;
709
710 END IF;
711
712 CLOSE direct_actual_cost_cur;
713
714
715 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
716 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
717 , G_MODULE_HEAD || l_routine ||'.Direct_txn'
718 , 'Correspond Txn Info: Organization Id:' ||
719 p_opp_organization_id || ' Transaction Id:' ||
720 p_opp_transaction_id ||
721 ' Cost element Id:' || p_cost_element_id ||
722 ' Level Type:' || p_level_type ||
723 ' Correspond Actual Cost:' || l_correspond_actual_cost
724 );
725 END IF;
726
727 -- intransit interorg transactions
728 ELSIF (p_transaction_action_id = 12 OR p_transaction_action_id = 21 ) THEN
729 -- Set Corresponding Organization Id
730 -- intransit receipt - 12 group 1 transactions
731 -- Transfer organization id is the corresponding organization
732 -- intransit shipment - 21 group 1 transactions
733 -- Organization id is the corresponding organization
734 -- intransit receipt - 12 group 2 transactions
735 -- Organization id is the corresponding organization
736 -- intransit shipment - 21 group 2 transactions
737 -- Transfer organization id is the corresponding organization
738 IF (p_transaction_action_id = 12 AND p_group_num = 1) THEN
739 l_correspond_org_id := p_opp_organization_id;
740 ELSIF (p_transaction_action_id = 21 AND p_group_num = 1) THEN
741 l_correspond_org_id := p_organization_id;
742 ELSIF (p_transaction_action_id = 12 AND p_group_num = 2) THEN
743 l_correspond_org_id := p_organization_id;
744 ELSIF (p_transaction_action_id = 21 AND p_group_num = 2) THEN
745 l_correspond_org_id := p_opp_organization_id;
746 END IF;
747
748 l_cost_group_id := get_cost_group(l_correspond_org_id);
749 OPEN intransit_actual_cost_cur(p_period_id
750 ,p_cost_type_id
751 ,p_transaction_id
752 ,l_cost_group_id
753 ,p_cost_element_id
754 ,p_level_type
758 INTO l_correspond_actual_cost;
755 );
756
757 FETCH intransit_actual_cost_cur
759
760 IF intransit_actual_cost_cur%FOUND THEN
761 l_correspond_txn_flag := 'Y';
762 ELSE
763 -- Check whether corresponding Cost Group exists
764 IF l_cost_group_id = -99 THEN
765 -- corresponding cost group not found
766 l_correspond_txn_flag := 'C';
767 ELSE
768 -- corresponding cost group exists, but txn not exists
769 l_correspond_txn_flag := 'N';
770 END IF;
771 END IF;
772
773 CLOSE intransit_actual_cost_cur;
774
775 IF l_correspond_txn_flag = 'C' THEN
776 -- Display a message that not cost group found
777 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
778 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION
779 , G_MODULE_HEAD || l_routine ||'.nocg'
780 , 'No Cost Group found'
781 );
782 END IF;
783 END IF;
784
785 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
786 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
787 , G_MODULE_HEAD || l_routine ||'.Direct_txn'
788 , 'Correspond Txn Info: Organization Id:' ||
789 l_correspond_org_id || ' Transaction Id:' ||
790 p_transaction_id ||
791 ' Cost element Id:' || p_cost_element_id ||
792 ' Level Type:' || p_level_type ||
793 ' Correspond Actual Cost:' || l_correspond_actual_cost
794 );
795 END IF;
796
797
798 END IF; -- interorg transactions check
799
800 x_correspond_actual_cost := l_correspond_actual_cost;
801 x_correspond_txn_flag := l_correspond_txn_flag;
802
803 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
804 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
805 , G_MODULE_HEAD || l_routine ||'.end'
806 , l_routine || '>'
807 );
808 END IF;
809
810 EXCEPTION
811 WHEN FND_API.G_EXC_ERROR THEN
812 RAISE FND_API.G_EXC_ERROR;
813 WHEN OTHERS THEN
814 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
815 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION
816 , G_MODULE_HEAD || l_routine ||'.others_exc'
817 , 'txn_id '|| p_transaction_id || 'Opp Txn_id '||p_opp_transaction_id || SQLCODE || substr(SQLERRM, 1,200)
818 );
819 END IF;
820 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
821 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
822 FND_MESSAGE.set_token('MESSAGE', 'txn_id '|| p_transaction_id || 'Opp Txn_id '||p_opp_transaction_id || '('||SQLCODE||') '||SQLERRM);
823 FND_MSG_PUB.Add;
824 RAISE FND_API.G_EXC_ERROR;
825
826 END; -- Get correspond actual cost
827
828 -- +========================================================================+
829 -- PROCEDURE: Balance_Pac_Txn Local Utility
830 -- PARAMETERS:
831 -- p_period_id PAC Period Id
832 -- p_inventory_item_id Inventory Item Id
833 -- p_cost_type_id Cost type of Legal Entity
834 -- COMMENT:
835 -- This procedure creates or deletes pac transactions inorder to balance
836 -- with corresponding interorg transactions
837 -- USAGE:
838 -- This procedure is invoked through api: iteration_process
839 -- after the very iteration (default current behavior)
840 -- It is invoked only once after the first iteration
841 -- PRE-COND: none
842 -- EXCEPTIONS: none
843 -- +========================================================================+
844 PROCEDURE Balance_Pac_Txn
845 ( p_period_id IN NUMBER
846 , p_inventory_item_id IN NUMBER
847 , p_cost_type_id IN NUMBER
848 )
849 IS
850
851 l_routine CONSTANT VARCHAR2(30) := 'balance_pac_txn';
852
853 -- Cursor to obtain PAC transaction cost element and level type
854 CURSOR pac_bal_txn_cursor(c_cost_group_id NUMBER
855 ,c_period_id NUMBER
856 ,c_transaction_id NUMBER
857 ,c_inventory_item_id NUMBER
858 )
859 IS
860 SELECT
861 cost_layer_id
862 , cost_element_id
863 , level_type
864 , actual_cost
865 FROM
866 mtl_pac_act_cst_dtl_temp
867 WHERE pac_period_id = c_period_id
868 AND cost_group_id = c_cost_group_id
869 AND transaction_id = c_transaction_id
870 AND inventory_item_id = c_inventory_item_id
871 ORDER BY
872 cost_element_id
873 , level_type
874 , transaction_id
875 FOR UPDATE;
876
877 -- Cursor to obtain interorg transactions of Cost Groups with interorg receipts
878 -- and interorg shipments.
879 -- Bug#9828840 fix: Perform balance pac txn only for those cost groups having
880 -- both interorg receipts and interorg shipments. Exclude the cost group having
881 -- only interorg receipt since optimal sequence 3 cost groups are processed
882 -- at the end of iteration process.
883
884 CURSOR pac_interorg_txns_cur(c_period_id NUMBER
885 , c_inventory_item_id NUMBER
886 )
887 IS
888 SELECT
889 ccit.transaction_id transaction_id
890 , ccit.transaction_action_id transaction_action_id
891 , ccit.organization_id organization_id
892 , nvl(ccit.transfer_organization_id,-1) transfer_organization_id
893 , ccit.transfer_transaction_id transfer_transaction_id
894 , ccit.cost_group_id cost_group_id
895 , ccit.txn_type txn_type
896 FROM
897 CST_PAC_INTERORG_TXNS_TMP ccit, cst_pac_intorg_itms_temp cpiit
901 AND cpiit.cost_group_id = ccit.cost_group_id
898 WHERE ccit.inventory_item_id = c_inventory_item_id
899 AND ccit.pac_period_id = c_period_id
900 AND cpiit.inventory_item_id = ccit.inventory_item_id
902 AND cpiit.pac_period_id = ccit.pac_period_id
903 AND cpiit.interorg_receipt_flag = 'Y'
904 AND cpiit.interorg_shipment_flag = 'Y'
905 ORDER BY ccit.cost_group_id, ccit.txn_type, ccit.transaction_id;
906
907 TYPE pac_interorg_txns_tab IS TABLE OF pac_interorg_txns_cur%rowtype INDEX BY BINARY_INTEGER;
908 l_pac_interorg_txns_tab pac_interorg_txns_tab;
909 l_empty_pac_interorg_txns_tab pac_interorg_txns_tab;
910
911 l_loop_count NUMBER := 0;
912 l_batch_size NUMBER := 200;
913
914 -- Cursor to obtain cost layer id of corresponding group 1 pac transaction
915 -- for a given pac period, corresponding cost group and inventory
916 -- item id
917 CURSOR pac_group1_cost_layer(c_period_id NUMBER
918 ,c_opp_cost_group_id NUMBER
919 ,c_inventory_item_id NUMBER
920 )
921 IS
922 SELECT
923 cost_layer_id
924 FROM
925 CST_PAC_ITEM_COSTS
926 WHERE pac_period_id = c_period_id
927 AND cost_group_id = c_opp_cost_group_id
928 AND inventory_item_id = c_inventory_item_id;
929
930 -- Cursor to obtain interorg receipt and interorg shipment for
931 -- the pac period, cost group and inventory item id
932 -- Bug 9828840 fix: purpose is to check for both interorg shipment
933 -- and interorg receipt of the corresponding cost group before
934 -- inserting into MPACD_TEMP
935 CURSOR pac_intorg_itms_cur(c_period_id NUMBER
936 ,c_opp_cost_group_id NUMBER
937 ,c_inventory_item_id NUMBER
938 )
939 IS
940 SELECT
941 interorg_receipt_flag
942 , interorg_shipment_flag
943 FROM cst_pac_intorg_itms_temp
944 WHERE pac_period_id = c_period_id
945 AND cost_group_id = c_opp_cost_group_id
946 AND inventory_item_id = c_inventory_item_id;
947
948
949 l_correspond_txn_flag VARCHAR2(1);
950 l_correspond_actual_cost NUMBER;
951 l_correspond_cost_group_id NUMBER;
952 l_correspond_transaction_id NUMBER;
953 l_correspond_cost_layer_id NUMBER;
954 l_moh_absorption_cost NUMBER := 0;
955 l_txn_gp_idx BINARY_INTEGER;
956
957 -- Optimal Interorg Flags of Cost Group for the item
958 l_interorg_receipt_flag VARCHAR2(1);
959 l_interorg_shipment_flag VARCHAR2(1);
960
961 BEGIN
962
963 IF NOT pac_interorg_txns_cur%ISOPEN THEN
964 OPEN pac_interorg_txns_cur(p_period_id
965 ,p_inventory_item_id
966 );
967 END IF;
968
969 LOOP
970
971 l_pac_interorg_txns_tab := l_empty_pac_interorg_txns_tab;
972 FETCH pac_interorg_txns_cur BULK COLLECT INTO l_pac_interorg_txns_tab LIMIT l_batch_size;
973
974 l_loop_count := l_pac_interorg_txns_tab.count;
975
976 FOR i IN 1..l_loop_count
977 LOOP
978
979
980 FOR pac_bal_txn_idx IN
981 pac_bal_txn_cursor(l_pac_interorg_txns_tab(i).cost_group_id
982 ,p_period_id
983 ,l_pac_interorg_txns_tab(i).transaction_id
984 ,p_inventory_item_id
985 ) LOOP
986
987 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
988 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
989 , G_MODULE_HEAD || l_routine ||'.txnid'
990 , 'interorg_txn_id:' || l_pac_interorg_txns_tab(i).transaction_id
991 );
992 END IF;
993
994 -- Get the corresponding PAC transaction
995 Get_Correspond_Actual_Cost(p_period_id => p_period_id
996 ,p_cost_type_id => p_cost_type_id
997 ,p_transaction_id => l_pac_interorg_txns_tab(i).transaction_id
998 ,p_transaction_action_id => l_pac_interorg_txns_tab(i).transaction_action_id
999 ,p_organization_id => l_pac_interorg_txns_tab(i).organization_id
1000 ,p_opp_organization_id => l_pac_interorg_txns_tab(i).transfer_organization_id
1001 ,p_opp_transaction_id => l_pac_interorg_txns_tab(i).transfer_transaction_id
1002 ,p_cost_element_id => pac_bal_txn_idx.cost_element_id
1003 ,p_level_type => pac_bal_txn_idx.level_type
1004 ,p_group_num => l_pac_interorg_txns_tab(i).txn_type
1005 ,x_correspond_actual_cost => l_correspond_actual_cost
1006 ,x_correspond_txn_flag => l_correspond_txn_flag
1007 );
1008
1009 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1010 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1011 , G_MODULE_HEAD || l_routine ||'.actual_cost'
1012 , 'Correspond Actual Cost:' || l_correspond_actual_cost
1013 || ' ' || 'Correspond Txn Flag:' || l_correspond_txn_flag
1014 );
1015
1016 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1017 , G_MODULE_HEAD || l_routine ||'.inttxns'
1018 , 'Transfer transaction id:' || l_pac_interorg_txns_tab(i).transfer_transaction_id
1019 || ' ' || 'Transfer Organization id:' || l_pac_interorg_txns_tab(i).transfer_organization_id
1020 );
1021
1022 END IF;
1023
1024 IF l_pac_interorg_txns_tab(i).txn_type = 1 THEN
1025 /* Cost owned (group 1) transactions include receipts
1026 Corresponding cost derived (group 2) txn include shipment
1027 If the corresponding transaction exists
1028 if the cost element = 2 and level type = 1 then
1032 IF l_correspond_txn_flag = 'Y' AND pac_bal_txn_idx.cost_element_id = 2 THEN
1029 get material overhead absorption cost of group 1 txn
1030 update moh absorption cost in pac txn temp table
1031 if the cost element <> 2 then retain as it is */
1033
1034 BEGIN
1035 SELECT SUM(nvl(actual_cost,0)) /* 11670136 */
1036 INTO l_moh_absorption_cost
1037 FROM MTL_PAC_COST_SUBELEMENTS
1038 WHERE cost_group_id = l_pac_interorg_txns_tab(i).cost_group_id
1039 AND transaction_id = l_pac_interorg_txns_tab(i).transaction_id
1040 AND pac_period_id = p_period_id
1041 AND cost_element_id = 2
1042 AND level_type = pac_bal_txn_idx.level_type;
1043
1044 EXCEPTION
1045 WHEN NO_DATA_FOUND THEN
1046 l_moh_absorption_cost := 0;
1047 END;
1048
1049 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1050 FND_LOG.string(FND_LOG.LEVEL_STATEMENT , G_MODULE_HEAD || l_routine ||'.moh_actual_cost'
1051 , 'After MOH Retrieve: Cost Group Id:' || l_pac_interorg_txns_tab(i).cost_group_id ||
1052 ' Transaction Id:' || l_pac_interorg_txns_tab(i).transaction_id ||
1053 ' Period Id:' || p_period_id || ' Level Type:' || pac_bal_txn_idx.level_type
1054 );
1055 END IF;
1056
1057 -- Update moh absorption cost in pac txn temp table
1058 IF pac_bal_txn_idx.cost_element_id = 2 AND l_moh_absorption_cost <> 0 THEN
1059
1060 UPDATE MTL_PAC_ACT_CST_DTL_TEMP
1061 SET moh_absorption_cost = l_moh_absorption_cost
1062 WHERE CURRENT OF pac_bal_txn_cursor;
1063
1064 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1065 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1066 , G_MODULE_HEAD || l_routine ||'.ce'
1067 , 'MOH Absorption Cost Update:' || l_moh_absorption_cost
1068 );
1069 END IF;
1070
1071
1072 END IF; -- check for moh absorption
1073
1074 END IF; -- check for cost element 2 and correspond txn flag
1075
1076 /* If the corresponding transaction NOT exists, then
1077 If cost element = 2 then
1078 NOTE: DO NOT USE this cost owned receipt for comparison
1079 DO NOT delete this record as this record will be put back
1080 into MPACD at the end of iteration process
1081 No logic as the record will be retained as it is */
1082
1083 ELSIF l_pac_interorg_txns_tab(i).txn_type = 2 THEN
1084 -- Cost derived (group 2) transactions include shipments
1085 -- Corresponding cost owned (group 1) txn include receipt
1086 -- If the corresponding transaction exists then retain as it is
1087 -- If the corresponding transaction NOT exists, then insert the
1088 -- corresponding transaction as it is required to be considered for
1089 -- iteration process
1090 -- Insert corresponding group 1 pac transaction if the current
1091 -- group2 transaction exists and the transaction is direct interorg
1092 -- DO NOT insert corresponding group 1 pac transaction if the
1093 -- current group2 transaction is an intransit interorg transaction
1094 -- and DO NOT use for comparison since the corresponding group 1 txn
1095 -- may be across periods.
1096
1097 IF (l_correspond_txn_flag = 'N') AND (l_pac_interorg_txns_tab(i).transaction_action_id = 3 ) THEN
1098
1099 -- Get Corresponding Cost Group Id
1100 l_correspond_cost_group_id := get_cost_group(l_pac_interorg_txns_tab(i).transfer_organization_id);
1101 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1102 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1103 , G_MODULE_HEAD || l_routine || '.cgid'
1104 , 'Corresponding Cost Group Id:' || l_correspond_cost_group_id
1105 );
1106 END IF;
1107 -- for direct interorg: transfer_transaction_id exists
1108 -- for intransit interorg: transfer_transaction_id not exists in mmt
1109 -- for intransit interorg: transaction_id is the transfer_transaction_id
1110 -- with corresponding cost group
1111 l_correspond_transaction_id := nvl(l_pac_interorg_txns_tab(i).transfer_transaction_id,
1112 l_pac_interorg_txns_tab(i).transaction_id);
1113
1114
1115
1116 -- Get cost layer id of corresponding group 1 transaction
1117 OPEN pac_group1_cost_layer(p_period_id
1118 ,l_correspond_cost_group_id
1119 ,p_inventory_item_id
1120 );
1121
1122 FETCH pac_group1_cost_layer
1123 INTO l_correspond_cost_layer_id;
1124
1125 CLOSE pac_group1_cost_layer;
1126
1127
1128 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1129 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1130 , G_MODULE_HEAD || l_routine || '.cortxn'
1131 , 'Corresponding Transaction Id:' || l_correspond_transaction_id ||
1132 ' Corresponding Layer Id:' || l_correspond_cost_layer_id
1133 );
1134 END IF;
1135
1136 -- Bug 9828840 fix: insert into MPACD_TEMP for the
1140 -- optimal sequence 3, with only interorg receipt since these cost groups
1137 -- corresponding cost group having both interorg receipt flag
1138 -- and interorg shipment flag, optimal sequence 2.
1139 -- Not required to insert into MPACD_TEMP for the cost groups in
1141 -- do not undergo consecutive iterations. Also, they are processed at
1142 -- the end of iteration process
1143 OPEN pac_intorg_itms_cur(p_period_id
1144 ,l_correspond_cost_group_id
1145 ,p_inventory_item_id
1146 );
1147
1148 FETCH pac_intorg_itms_cur
1149 INTO l_interorg_receipt_flag
1150 ,l_interorg_shipment_flag;
1151
1152 CLOSE pac_intorg_itms_cur;
1153
1154
1155 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1156 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1157 , G_MODULE_HEAD || l_routine || '.intflag'
1158 , 'Corresponding Cost Group Id:' || l_correspond_cost_group_id ||
1159 ' Interorg Receipt Flag:' || l_interorg_receipt_flag ||
1160 ' Interorg Shipment Flag:' || l_interorg_shipment_flag
1161 );
1162 END IF;
1163
1164
1165 IF l_interorg_receipt_flag = 'Y' AND l_interorg_shipment_flag = 'Y' THEN
1166
1167
1168
1169 -- Insert into MTL_PAC_ACT_CST_DTL_TEMP
1170 -- Cost owned transactions
1171 INSERT INTO MTL_PAC_ACT_CST_DTL_TEMP
1172 ( COST_GROUP_ID
1173 , TRANSACTION_ID
1174 , PAC_PERIOD_ID
1175 , COST_TYPE_ID
1176 , COST_ELEMENT_ID
1177 , LEVEL_TYPE
1178 , INVENTORY_ITEM_ID
1179 , COST_LAYER_ID
1180 , PRIOR_COST
1181 , ACTUAL_COST
1182 , NEW_COST
1183 , PRIOR_BUY_COST
1184 , PRIOR_MAKE_COST
1185 , NEW_BUY_COST
1186 , NEW_MAKE_COST
1187 , USER_ENTERED
1188 , INSERTION_FLAG
1189 , TRANSACTION_COSTED_DATE
1190 , TRANSFER_TRANSACTION_ID
1191 , TRANSFER_COST
1192 , TRANSPORTATION_COST
1193 , MOH_ABSORPTION_COST
1194 ) VALUES
1195 ( l_correspond_cost_group_id
1196 , l_correspond_transaction_id
1197 , p_period_id
1198 , p_cost_type_id
1199 , pac_bal_txn_idx.cost_element_id
1200 , pac_bal_txn_idx.level_type
1201 , p_inventory_item_id
1202 , l_correspond_cost_layer_id
1203 , 0
1204 , pac_bal_txn_idx.actual_cost
1205 , 0
1206 , 0
1207 , 0
1208 , 0
1209 , 0
1210 ,'Y'
1211 ,'N'
1212 ,NULL
1213 ,l_pac_interorg_txns_tab(i).transfer_transaction_id
1214 ,0
1215 ,0
1216 ,0
1217 );
1218
1219 END IF; -- check for opp cost group with interorg receipt and interorg shipment
1220
1221 END IF; -- check for correspond transaction flag N
1222
1223
1224 END IF; -- group check
1225
1226 END LOOP; -- end of pac txn loop
1227
1228 END LOOP; -- FOR i IN 1..l_loop_count
1229
1230 EXIT WHEN pac_interorg_txns_cur%NOTFOUND;
1231 END LOOP; -- FETCH loop
1232 CLOSE pac_interorg_txns_cur;
1233
1234 EXCEPTION
1235 WHEN FND_API.G_EXC_ERROR THEN
1236 RAISE FND_API.G_EXC_ERROR;
1237 WHEN OTHERS THEN
1238
1239 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1240 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION
1241 , G_MODULE_HEAD || l_routine ||'.others_exc'
1242 , 'Item_id' ||p_inventory_item_id || SQLCODE || substr(SQLERRM, 1,200)
1243 );
1244 END IF;
1245 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
1246 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
1247 FND_MESSAGE.set_token('MESSAGE', 'Item_id' ||p_inventory_item_id ||'('||SQLCODE||') '||SQLERRM);
1248 FND_MSG_PUB.Add;
1249 RAISE FND_API.G_EXC_ERROR;
1250
1251 END; -- Balance pac txn
1252
1253 -- ==========================================================================
1254 -- PROCEDURE : Calc_Pmac_For_Interorg PRIVATE
1255 -- COMMENT : This procedure is a copy CSTPPWAC.calculate_periodic_cost with
1256 -- : a minor modification to perform the process only for the
1257 -- : last txn of a transaction category 8 interorg receipts
1258 -- ==========================================================================
1259 PROCEDURE Calc_Pmac_For_Interorg(p_pac_period_id IN NUMBER
1260 ,p_cost_type_id IN NUMBER
1261 ,p_cost_group_id IN NUMBER
1262 ,p_inventory_item_id IN NUMBER
1263 ,p_low_level_code IN NUMBER
1264 ,p_user_id IN NUMBER
1265 ,p_login_id IN NUMBER
1266 ,p_request_id IN NUMBER
1267 ,p_prog_id IN NUMBER
1268 ,p_prog_appl_id IN NUMBER
1269 )
1270 IS
1271
1272 l_routine CONSTANT VARCHAR2(30) := 'calc_pmac_for_interorg';
1273
1274 l_stmt_num NUMBER;
1278 INDEX BY BINARY_INTEGER;
1275 TYPE t_txn_id_tbl IS TABLE OF MTL_PAC_ACTUAL_COST_DETAILS.transaction_id%TYPE
1276 INDEX BY BINARY_INTEGER;
1277 TYPE t_txn_category_tbl IS TABLE OF CST_PAC_PERIOD_BALANCES.txn_category%TYPE
1279 l_last_txn_id_tbl t_txn_id_tbl;
1280 l_txn_category_tbl t_txn_category_tbl;
1281 l_cost_layer_id_tbl CSTPPINV.t_cost_layer_id_tbl;
1282
1283 l_period_quantity NUMBER;
1284 l_period_balance NUMBER;
1285 l_cg_idx BINARY_INTEGER;
1286
1287 BEGIN
1288
1289 FND_FILE.put_line
1290 ( FND_FILE.log
1291 , '>> CST_PERIODIC_ABSORPTION_PROC:Calc_Pmac_For_Interorg'
1292 );
1293
1294 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1295 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1296 ,G_MODULE_HEAD || l_routine || '.begin'
1297 ,l_routine || '<'
1298 );
1299 END IF;
1300
1301 -- Get the period quantity upto interorg receipts
1302 l_cg_idx := to_char(p_cost_group_id);
1303 l_period_quantity := G_CST_GROUP_TBL(l_cg_idx).period_new_quantity;
1304
1305 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1306 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1307 ,G_MODULE_HEAD || l_routine || 'pdqty'
1308 ,'Cost Group Id:' || p_cost_group_id || ' Inventory Item Id:' || p_inventory_item_id || ' Period Quantity upto interorg receipts:' || l_period_quantity
1309 );
1310 END IF;
1311
1312
1313 -- Build temporary tables to hold the last txn id and txn category values for each cost_layer_id
1314 -- of a transaction category 8 interorg receipts across CGs
1315 IF (p_low_level_code = -1) THEN
1316 -- items without completion
1317 l_stmt_num := 10;
1318 SELECT distinct cost_layer_id, mpacd.transaction_id
1319 BULK COLLECT
1320 INTO l_cost_layer_id_tbl, l_last_txn_id_tbl
1321 FROM mtl_pac_actual_cost_details mpacd
1322 WHERE mpacd.transaction_id = (SELECT max(mpacd1.transaction_id)
1323 FROM mtl_pac_actual_cost_details mpacd1
1324 WHERE mpacd1.txn_category = 8
1325 AND mpacd1.inventory_item_id = mpacd.inventory_item_id
1326 AND mpacd1.pac_period_id = p_pac_period_id
1327 AND mpacd1.cost_group_id = p_cost_group_id)
1328 AND mpacd.cost_group_id = p_cost_group_id
1329 AND mpacd.pac_period_id = p_pac_period_id
1330 AND mpacd.inventory_item_id = p_inventory_item_id
1331 AND NOT EXISTS (SELECT 1
1332 FROM cst_pac_low_level_codes cpllc
1333 WHERE cpllc.inventory_item_id = mpacd.inventory_item_id
1334 AND cpllc.pac_period_id = p_pac_period_id
1335 AND cpllc.cost_group_id = p_cost_group_id);
1336 ELSE
1337 -- items with completion
1338 l_stmt_num := 20;
1339 SELECT distinct cost_layer_id, mpacd.transaction_id
1340 BULK COLLECT
1341 INTO l_cost_layer_id_tbl, l_last_txn_id_tbl
1342 FROM mtl_pac_actual_cost_details mpacd
1343 WHERE mpacd.transaction_id = (SELECT max(mpacd1.transaction_id)
1344 FROM mtl_pac_actual_cost_details mpacd1
1345 WHERE mpacd1.txn_category = 8
1346 AND mpacd1.inventory_item_id = mpacd.inventory_item_id
1347 AND mpacd1.pac_period_id = p_pac_period_id
1348 AND mpacd1.cost_group_id = p_cost_group_id)
1349 AND mpacd.cost_group_id = p_cost_group_id
1350 AND mpacd.pac_period_id = p_pac_period_id
1351 AND mpacd.inventory_item_id = p_inventory_item_id
1352 AND EXISTS (SELECT 1
1353 FROM cst_pac_low_level_codes cpllc
1354 WHERE cpllc.inventory_item_id = mpacd.inventory_item_id
1355 AND cpllc.low_level_code = p_low_level_code
1356 AND cpllc.pac_period_id = p_pac_period_id
1357 AND cpllc.cost_group_id = p_cost_group_id);
1358 END IF;
1359
1360 IF SQL%NOTFOUND THEN
1361 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1362 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1363 ,G_MODULE_HEAD || l_routine || 'nompacd'
1364 ,'Stmt Num: ' || l_stmt_num || ' No records in MPACD for txn_category 8'
1365 );
1366 END IF;
1367 END IF;
1368
1369
1370
1371 /****************************************************************************
1372 Post variance to the last transaction in the last cost owned txn category
1373 processed.
1374 ****************************************************************************/
1375 l_stmt_num := 30;
1376 FORALL l_index IN l_cost_layer_id_tbl.FIRST..l_cost_layer_id_tbl.LAST
1377 UPDATE mtl_pac_actual_cost_details mpacd
1378 SET variance_amount = (SELECT decode (sign(l_period_quantity),
1379 0, cpicd.item_balance,
1380 (-1 * sign(cpicd.item_balance)), cpicd.item_balance,
1381 0)
1382 FROM cst_pac_item_costs cpic,
1383 cst_pac_item_cost_details cpicd
1384 WHERE cpic.cost_layer_id = cpicd.cost_layer_id
1385 AND cpicd.cost_layer_id = l_cost_layer_id_tbl (l_index)
1386 AND cpicd.cost_element_id = mpacd.cost_element_id
1387 AND cpicd.level_type = mpacd.level_type),
1388 last_update_date = sysdate,
1392 program_application_id = p_prog_appl_id,
1389 last_updated_by = p_user_id,
1390 last_update_login = p_login_id,
1391 request_id = p_request_id,
1393 program_id = p_prog_id,
1394 program_update_date = sysdate
1395 WHERE transaction_id = l_last_txn_id_tbl (l_index)
1396 AND mpacd.cost_group_id = p_cost_group_id
1397 AND mpacd.pac_period_id = p_pac_period_id
1398 AND mpacd.cost_layer_id = l_cost_layer_id_tbl(l_index)
1399 AND (cost_element_id, level_type) = (SELECT cost_element_id, level_type
1400 FROM cst_pac_item_cost_details cpicd
1401 WHERE cpicd.cost_layer_id = l_cost_layer_id_tbl (l_index)
1402 AND cpicd.cost_element_id = mpacd.cost_element_id
1403 AND cpicd.level_type = mpacd.level_type);
1404
1405
1406 -- Update Item Cost, item balance
1407 l_stmt_num := 50;
1408
1409 IF (p_low_level_code = -1) THEN
1410 -- Items that do not have completion
1411 UPDATE cst_pac_item_cost_details cpicd
1412 SET (last_update_date,
1413 last_updated_by,
1414 last_update_login,
1415 request_id,
1416 program_application_id,
1417 program_id,
1418 program_update_date,
1419 item_cost,
1420 item_buy_cost,
1421 item_make_cost,
1422 item_balance,
1423 buy_balance,
1424 make_balance) =
1425 (SELECT sysdate,
1426 p_user_id,
1427 p_login_id,
1428 p_request_id,
1429 p_prog_appl_id,
1430 p_prog_id,
1431 sysdate,
1432 decode (sign(l_period_quantity),
1433 0, cpicd.item_cost,
1434 (-1 * sign(cpicd.item_balance)), 0,
1435 cpicd.item_balance / l_period_quantity),
1436 decode (sign(l_period_quantity),
1437 0, cpicd.item_buy_cost,
1438 (-1 * sign(cpicd.item_balance)), 0,
1439 decode (cpic.buy_quantity,
1440 0, 0,
1441 cpicd.buy_balance / cpic.buy_quantity)),
1442 decode (sign(l_period_quantity),
1443 0, cpicd.item_make_cost,
1444 (-1 * sign(cpicd.item_balance)), 0,
1445 decode (cpic.make_quantity,
1446 0, 0,
1447 cpicd.make_balance / cpic.make_quantity)),
1448 decode (sign (l_period_quantity),
1449 0, 0,
1450 (-1 * sign(cpicd.item_balance)), 0,
1451 (cpicd.item_balance / l_period_quantity) * cpic.total_layer_quantity),
1452 /* cpicd.item_balance and l_period_quantity correspond to the balance and quantity after processing category 8
1453 cpic.total_layer_quantity corresponds to the quantity after processing category 9 */
1454 decode (sign (l_period_quantity),
1455 0, 0,
1456 (-1 * sign(cpicd.item_balance)), 0,
1457 cpicd.buy_balance),
1458 decode (sign (l_period_quantity),
1459 0, 0,
1460 (-1 * sign(cpicd.item_balance)), 0,
1461 cpicd.make_balance)
1462 FROM cst_pac_item_costs cpic
1463 WHERE cpic.cost_layer_id = cpicd.cost_layer_id)
1464 WHERE cpicd.cost_layer_id IN (select cost_layer_id
1465 from cst_pac_item_costs
1466 where inventory_item_id = p_inventory_item_id
1467 and pac_period_id = p_pac_period_id
1468 and cost_group_id = p_cost_group_id)
1469 AND EXISTS (SELECT 1
1470 FROM cst_pac_period_balances cppb
1471 WHERE cppb.pac_period_id = p_pac_period_id
1472 AND cppb.cost_group_id = p_cost_group_id
1473 AND cppb.cost_layer_id = cpicd.cost_layer_id
1474 AND cppb.cost_element_id = cpicd.cost_element_id
1475 AND cppb.level_type = cpicd.level_type
1476 AND cppb.inventory_item_id = p_inventory_item_id)
1477 AND NOT EXISTS (SELECT 1
1478 FROM cst_pac_low_level_codes cpllc
1479 WHERE cpllc.pac_period_id = p_pac_period_id
1480 AND cpllc.cost_group_id = p_cost_group_id
1481 AND cpllc.inventory_item_id = p_inventory_item_id);
1482
1483 l_stmt_num := 60;
1484 UPDATE cst_pac_item_costs cpic
1485 SET (last_updated_by,
1486 last_update_date,
1487 last_update_login,
1488 request_id,
1489 program_application_id,
1490 program_id,
1491 program_update_date,
1492 pl_material,
1493 pl_material_overhead,
1494 pl_resource,
1495 pl_outside_processing,
1496 pl_overhead,
1497 tl_material,
1498 tl_material_overhead,
1499 tl_resource,
1500 tl_outside_processing,
1501 tl_overhead,
1502 material_cost,
1503 material_overhead_cost,
1504 resource_cost,
1508 tl_item_cost,
1505 outside_processing_cost,
1506 overhead_cost,
1507 pl_item_cost,
1509 item_cost,
1510 item_buy_cost,
1511 item_make_cost,
1512 unburdened_cost,
1513 burden_cost
1514 ) =
1515 (SELECT p_user_id,
1516 sysdate,
1517 p_login_id,
1518 p_request_id,
1519 p_prog_appl_id,
1520 p_prog_id,
1521 sysdate,
1522 SUM(DECODE(level_type,2,DECODE(cost_element_id,1,item_cost,0),0)) ,
1523 SUM(DECODE(level_type,2,DECODE(cost_element_id,2,item_cost,0),0)) ,
1524 SUM(DECODE(level_type,2,DECODE(cost_element_id,3,item_cost,0),0)) ,
1525 SUM(DECODE(level_type,2,DECODE(cost_element_id,4,item_cost,0),0)) ,
1526 SUM(DECODE(level_type,2,DECODE(cost_element_id,5,item_cost,0),0)) ,
1527 SUM(DECODE(level_type,1,DECODE(cost_element_id,1,item_cost,0),0)) ,
1528 SUM(DECODE(level_type,1,DECODE(cost_element_id,2,item_cost,0),0)) ,
1529 SUM(DECODE(level_type,1,DECODE(cost_element_id,3,item_cost,0),0)) ,
1530 SUM(DECODE(level_type,1,DECODE(cost_element_id,4,item_cost,0),0)) ,
1531 SUM(DECODE(level_type,1,DECODE(cost_element_id,5,item_cost,0),0)) ,
1532 SUM(DECODE(cost_element_id,1,item_cost,0)) ,
1533 SUM(DECODE(cost_element_id,2,item_cost,0)) ,
1534 SUM(DECODE(cost_element_id,3,item_cost,0)) ,
1535 SUM(DECODE(cost_element_id,4,item_cost,0)) ,
1536 SUM(DECODE(cost_element_id,5,item_cost,0)) ,
1537 SUM(DECODE(level_type,2,item_cost,0)) ,
1538 SUM(DECODE(level_type,1,item_cost,0)) ,
1539 SUM(item_cost) ,
1540 SUM(item_buy_cost) ,
1541 SUM(item_make_cost),
1542 SUM(DECODE(cost_element_id,2,DECODE(level_type,2,item_cost,0),item_cost)) ,
1543 SUM(DECODE(cost_element_id,2,DECODE(level_type,1,item_cost,0),0))
1544 FROM cst_pac_item_cost_details cpicd
1545 WHERE cpicd.cost_layer_id = cpic.cost_layer_id)
1546 WHERE cpic.pac_period_id = p_pac_period_id
1547 AND cpic.cost_group_id = p_cost_group_id
1548 AND cpic.inventory_item_id = p_inventory_item_id
1549 AND EXISTS (SELECT 1
1550 FROM cst_pac_period_balances cppb
1551 WHERE cppb.pac_period_id = p_pac_period_id
1552 AND cppb.cost_group_id = p_cost_group_id
1553 AND cppb.cost_layer_id = cpic.cost_layer_id
1554 AND cppb.inventory_item_id = p_inventory_item_id)
1555 AND NOT EXISTS (SELECT 1
1556 FROM cst_pac_low_level_codes cpllc
1557 WHERE cpllc.inventory_item_id = cpic.inventory_item_id
1558 AND cpllc.pac_period_id = p_pac_period_id
1559 AND cpllc.cost_group_id = p_cost_group_id)
1560 AND EXISTS
1561 (SELECT 'there is detail cost'
1562 FROM cst_pac_item_cost_details cpicd
1563 WHERE cpicd.cost_layer_id = cpic.cost_layer_id);
1564
1565
1566 ELSE
1567 -- low_level_code <> -1; items having completion
1568
1569 l_stmt_num := 70;
1570 UPDATE cst_pac_item_cost_details cpicd
1571 SET (last_update_date,
1572 last_updated_by,
1573 last_update_login,
1574 request_id,
1575 program_application_id,
1576 program_id,
1577 program_update_date,
1578 item_cost,
1579 item_buy_cost,
1580 item_make_cost,
1581 item_balance,
1582 buy_balance,
1583 make_balance) =
1584 (SELECT sysdate,
1585 p_user_id,
1586 p_login_id,
1587 p_request_id,
1588 p_prog_appl_id,
1589 p_prog_id,
1590 sysdate,
1591 decode (sign(l_period_quantity),
1592 0, cpicd.item_cost,
1593 (-1 * sign(cpicd.item_balance)), 0,
1594 cpicd.item_balance / l_period_quantity),
1595 decode (sign(l_period_quantity),
1596 0, cpicd.item_buy_cost,
1597 (-1 * sign(cpicd.item_balance)), 0,
1598 decode (cpic.buy_quantity,
1599 0, 0,
1600 cpicd.buy_balance / cpic.buy_quantity)),
1601 decode (sign(l_period_quantity),
1602 0, cpicd.item_make_cost,
1603 (-1 * sign(cpicd.item_balance)), 0,
1604 decode (cpic.make_quantity,
1605 0, 0,
1606 cpicd.make_balance / cpic.make_quantity)),
1607 decode (sign (l_period_quantity),
1608 0, 0,
1609 (-1 * sign(cpicd.item_balance)), 0,
1610 (cpicd.item_balance / l_period_quantity) * cpic.total_layer_quantity),
1611 /* cpicd.item_balance and l_period_quantity correspond to the balance and quantity after processing category 8
1612 cpic.total_layer_quantity corresponds to the quantity after processing category 9 */
1613 decode (sign (l_period_quantity),
1617 decode (sign (l_period_quantity),
1614 0, 0,
1615 (-1 * sign(cpicd.item_balance)), 0,
1616 cpicd.buy_balance),
1618 0, 0,
1619 (-1 * sign(cpicd.item_balance)), 0,
1620 cpicd.make_balance)
1621 FROM cst_pac_item_costs cpic
1622 WHERE cpic.cost_layer_id = cpicd.cost_layer_id)
1623 WHERE cpicd.cost_layer_id IN (select cost_layer_id
1624 from cst_pac_item_costs
1625 where inventory_item_id = p_inventory_item_id
1626 and pac_period_id = p_pac_period_id
1627 and cost_group_id = p_cost_group_id)
1628 AND EXISTS (SELECT 1
1629 FROM cst_pac_period_balances cppb
1630 WHERE cppb.pac_period_id = p_pac_period_id
1631 AND cppb.cost_group_id = p_cost_group_id
1632 AND cppb.cost_layer_id = cpicd.cost_layer_id
1633 AND cppb.cost_element_id = cpicd.cost_element_id
1634 AND cppb.level_type = cpicd.level_type
1635 AND cppb.inventory_item_id = p_inventory_item_id)
1636 AND EXISTS (SELECT 1
1637 FROM cst_pac_low_level_codes cpllc
1638 WHERE cpllc.low_level_code = p_low_level_code
1639 AND cpllc.pac_period_id = p_pac_period_id
1640 AND cpllc.cost_group_id = p_cost_group_id
1641 );
1642
1643 l_stmt_num := 80;
1644 UPDATE cst_pac_item_costs cpic
1645 SET (last_updated_by,
1646 last_update_date,
1647 last_update_login,
1648 request_id,
1649 program_application_id,
1650 program_id,
1651 program_update_date,
1652 pl_material,
1653 pl_material_overhead,
1654 pl_resource,
1655 pl_outside_processing,
1656 pl_overhead,
1657 tl_material,
1658 tl_material_overhead,
1659 tl_resource,
1660 tl_outside_processing,
1661 tl_overhead,
1662 material_cost,
1663 material_overhead_cost,
1664 resource_cost,
1665 outside_processing_cost,
1666 overhead_cost,
1667 pl_item_cost,
1668 tl_item_cost,
1669 item_cost,
1670 item_buy_cost,
1671 item_make_cost,
1672 unburdened_cost,
1673 burden_cost
1674 ) =
1675 (SELECT p_user_id,
1676 sysdate,
1677 p_login_id,
1678 p_request_id,
1679 p_prog_appl_id,
1680 p_prog_id,
1681 sysdate,
1682 SUM(DECODE(level_type,2,DECODE(cost_element_id,1,item_cost,0),0)) ,
1683 SUM(DECODE(level_type,2,DECODE(cost_element_id,2,item_cost,0),0)) ,
1684 SUM(DECODE(level_type,2,DECODE(cost_element_id,3,item_cost,0),0)) ,
1685 SUM(DECODE(level_type,2,DECODE(cost_element_id,4,item_cost,0),0)) ,
1686 SUM(DECODE(level_type,2,DECODE(cost_element_id,5,item_cost,0),0)) ,
1687 SUM(DECODE(level_type,1,DECODE(cost_element_id,1,item_cost,0),0)) ,
1688 SUM(DECODE(level_type,1,DECODE(cost_element_id,2,item_cost,0),0)) ,
1689 SUM(DECODE(level_type,1,DECODE(cost_element_id,3,item_cost,0),0)) ,
1690 SUM(DECODE(level_type,1,DECODE(cost_element_id,4,item_cost,0),0)) ,
1691 SUM(DECODE(level_type,1,DECODE(cost_element_id,5,item_cost,0),0)) ,
1692 SUM(DECODE(cost_element_id,1,item_cost,0)) ,
1693 SUM(DECODE(cost_element_id,2,item_cost,0)) ,
1694 SUM(DECODE(cost_element_id,3,item_cost,0)) ,
1695 SUM(DECODE(cost_element_id,4,item_cost,0)) ,
1696 SUM(DECODE(cost_element_id,5,item_cost,0)) ,
1697 SUM(DECODE(level_type,2,item_cost,0)) ,
1698 SUM(DECODE(level_type,1,item_cost,0)) ,
1699 SUM(item_cost) ,
1700 SUM(item_buy_cost) ,
1701 SUM(item_make_cost),
1702 SUM(DECODE(cost_element_id,2,DECODE(level_type,2,item_cost,0),item_cost)) ,
1703 SUM(DECODE(cost_element_id,2,DECODE(level_type,1,item_cost,0),0))
1704 FROM cst_pac_item_cost_details cpicd
1705 WHERE cpicd.cost_layer_id = cpic.cost_layer_id)
1706 WHERE cpic.pac_period_id = p_pac_period_id
1707 AND cpic.cost_group_id = p_cost_group_id
1708 AND cpic.inventory_item_id = p_inventory_item_id
1709 AND EXISTS (SELECT 1
1710 FROM cst_pac_period_balances cppb
1711 WHERE cppb.pac_period_id = p_pac_period_id
1712 AND cppb.cost_group_id = p_cost_group_id
1713 AND cppb.cost_layer_id = cpic.cost_layer_id
1714 AND cppb.inventory_item_id = p_inventory_item_id)
1715 AND EXISTS (SELECT 1
1716 FROM cst_pac_low_level_codes cpllc
1717 WHERE cpllc.low_level_code = p_low_level_code
1718 AND cpllc.inventory_item_id = cpic.inventory_item_id
1719 AND cpllc.pac_period_id = p_pac_period_id
1720 AND cpllc.cost_group_id = p_cost_group_id)
1721 AND EXISTS
1722 (SELECT 'there is detail cost'
1723 FROM cst_pac_item_cost_details cpicd
1727 FND_FILE.put_line
1724 WHERE cpicd.cost_layer_id = cpic.cost_layer_id);
1725 END IF;
1726
1728 ( FND_FILE.log
1729 , '<< CST_PERIODIC_ABSORPTION_PROC:Calc_Pmac_For_Interorg'
1730 );
1731
1732 EXCEPTION
1733 WHEN FND_API.G_EXC_ERROR THEN
1734 RAISE FND_API.G_EXC_ERROR;
1735 WHEN OTHERS THEN
1736 ROLLBACK;
1737 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
1738 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
1739 FND_MESSAGE.set_token('MESSAGE', '('||SQLCODE||') '||SQLERRM);
1740 FND_MSG_PUB.Add;
1741 RAISE FND_API.G_EXC_ERROR;
1742 END Calc_Pmac_For_Interorg;
1743
1744
1745 --=====================================================================================
1746 -- PROCEDURE : Calc_Pmac_Update_Cppb PRIVATE
1747 -- COMMENT : This procedure invokes
1748 -- : logic equivalent of CSTPPWAC.calculate_periodic_cost for each cost group
1749 -- : CSTPPWAC.update_cppb for each cost groups
1750 -- : Calculates PAC in CPICD, CPIC
1751 -- : Variance Amount is updated for last transaction of a transaction
1752 -- : category 8 interorg receipts
1753 -- : Updates CPPB with period balance, variance amount
1754 -- : Procedure is invoked after the iteration process for
1755 -- : each interorg item once the tolerance is achieved
1756 --======================================================================================
1757 PROCEDURE Calc_Pmac_Update_Cppb(p_pac_period_id IN NUMBER
1758 ,p_cost_type_id IN NUMBER
1759 ,p_cost_group_id IN NUMBER
1760 ,p_inventory_item_id IN NUMBER
1761 ,p_end_date IN DATE
1762 ,p_user_id IN NUMBER
1763 ,p_login_id IN NUMBER
1764 ,p_req_id IN NUMBER
1765 ,p_prg_id IN NUMBER
1766 ,p_prg_appid IN NUMBER
1767 )
1768 IS
1769 l_routine CONSTANT VARCHAR2(30) := 'Calc_Pmac_Update_Cppb';
1770
1771 l_cg_idx BINARY_INTEGER;
1772 l_low_level_code NUMBER;
1773 l_cost_group_id NUMBER;
1774 l_period_quantity NUMBER;
1775
1776 -- Cursor to get a low level code for an item in that cost group
1777 CURSOR get_llc_cur(c_pac_period_id NUMBER
1778 ,c_cost_group_id NUMBER
1779 ,c_inventory_item_id NUMBER
1780 )
1781 IS
1782 SELECT
1783 low_level_code
1784 FROM cst_pac_low_level_codes
1785 WHERE pac_period_id = c_pac_period_id
1786 AND cost_group_id = c_cost_group_id
1787 AND inventory_item_id = c_inventory_item_id;
1788
1789 l_error_num NUMBER;
1790 l_error_code VARCHAR2(240);
1791 l_error_msg VARCHAR2(240);
1792
1793 BEGIN
1794
1795 FND_FILE.put_line
1796 ( FND_FILE.log
1797 , '>> CST_PERIODIC_ABSORPTION_PROC.Calc_Pmac_Update_Cppb'
1798 );
1799
1800 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1801 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1802 ,G_MODULE_HEAD || l_routine || '.begin'
1803 ,l_routine || '>'
1804 );
1805 END IF;
1806
1807 l_cg_idx := p_cost_group_id;
1808
1809 l_period_quantity := G_CST_GROUP_TBL(l_cg_idx).period_new_quantity;
1810
1811 l_cost_group_id := p_cost_group_id;
1812
1813 -- Get Low Level Code for an item in the cost group
1814 OPEN get_llc_cur(p_pac_period_id
1815 ,l_cost_group_id
1816 ,p_inventory_item_id
1817 );
1818 FETCH get_llc_cur
1819 INTO l_low_level_code;
1820
1821 IF get_llc_cur%NOTFOUND THEN
1822 -- no completion item
1823 l_low_level_code := -1;
1824 END IF;
1825
1826 CLOSE get_llc_cur;
1827
1828 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1829 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1830 ,G_MODULE_HEAD || l_routine || '.cllc'
1831 ,'Cost Group Id: ' || l_cost_group_id || 'Period Quantity: ' ||
1832 l_period_quantity || 'Low Level Code: ' || l_low_level_code
1833 );
1834 END IF;
1835
1836
1837 -- Calculate PMAC in CPIC, CPICD; Variance amount update in last txn of
1838 -- transaction category 8 interorg receipts across CGs
1839 Calc_Pmac_For_Interorg
1840 (p_pac_period_id => p_pac_period_id
1841 ,p_cost_type_id => p_cost_type_id
1842 ,p_cost_group_id => l_cost_group_id
1843 ,p_inventory_item_id => p_inventory_item_id
1844 ,p_low_level_code => l_low_level_code
1845 ,p_user_id => p_user_id
1846 ,p_login_id => p_login_id
1847 ,p_request_id => p_req_id
1848 ,p_prog_id => p_prg_id
1849 ,p_prog_appl_id => p_prg_appid
1850 );
1851
1852 -- Update cumulative period balances in CPPB for interorg receipts
1853 -- with txn category 8
1854 UPDATE CST_PAC_PERIOD_BALANCES cppb
1855 SET (last_updated_by,
1856 last_update_date,
1857 last_update_login,
1858 request_id,
1859 program_application_id,
1860 program_id,
1861 program_update_date,
1862 txn_category_value,
1863 period_quantity,
1864 period_balance,
1865 periodic_cost,
1866 variance_amount
1867 ) =
1868 (SELECT p_user_id,
1872 p_prg_appid,
1869 sysdate,
1870 p_login_id,
1871 p_req_id,
1873 p_prg_id,
1874 sysdate,
1875 (SELECT sum (nvl (mpacd.actual_cost, 0) * nvl(mmt.periodic_primary_quantity,0))
1876 FROM mtl_pac_actual_cost_details mpacd,
1877 mtl_material_transactions mmt
1878 WHERE mpacd.txn_category = 8
1879 AND mpacd.inventory_item_id = p_inventory_item_id
1880 AND mpacd.pac_period_id = p_pac_period_id
1881 AND mpacd.cost_group_id = l_cost_group_id
1882 AND mpacd.transaction_id = mmt.transaction_id
1883 AND mpacd.inventory_item_id = mmt.inventory_item_id
1884 AND mpacd.cost_layer_id = cppb.cost_layer_id
1885 AND mpacd.cost_element_id = cppb.cost_element_id
1886 AND mpacd.level_type = cppb.level_type),
1887 l_period_quantity,
1888 l_period_quantity * cpicd.item_cost,
1889 cpicd.item_cost,
1890 (SELECT sum (nvl (mpacd.variance_amount, 0))
1891 FROM mtl_pac_actual_cost_details mpacd
1892 WHERE mpacd.txn_category = 8
1893 AND mpacd.inventory_item_id = p_inventory_item_id
1894 AND mpacd.pac_period_id = p_pac_period_id
1895 AND mpacd.cost_group_id = l_cost_group_id
1896 AND mpacd.cost_layer_id = cppb.cost_layer_id
1897 AND mpacd.cost_element_id = cppb.cost_element_id
1898 AND mpacd.level_type = cppb.level_type)
1899 FROM cst_pac_item_cost_details cpicd,
1900 cst_pac_item_costs cpic
1901 WHERE cpic.cost_layer_id = cpicd.cost_layer_id
1902 AND cppb.cost_layer_id = cpicd.cost_layer_id
1903 AND cppb.cost_element_id = cpicd.cost_element_id
1904 AND cppb.level_type = cpicd.level_type)
1905 WHERE cppb.pac_period_id = p_pac_period_id
1906 AND cppb.cost_group_id = l_cost_group_id
1907 AND cppb.inventory_item_id = p_inventory_item_id
1908 AND cppb.txn_category = 8
1909 AND EXISTS (SELECT 1
1910 FROM CST_PAC_ITEM_COST_DETAILS cpicd1
1911 WHERE cppb.cost_layer_id = cpicd1.cost_layer_id
1912 AND cppb.cost_element_id = cpicd1.cost_element_id
1913 AND cppb.level_type = cpicd1.level_type);
1914
1915 UPDATE CST_PAC_PERIOD_BALANCES cppb
1916 SET txn_category_value =
1917 (SELECT sum (nvl (mpacd.actual_cost, 0) * nvl(mmt.periodic_primary_quantity,0))
1918 FROM mtl_pac_actual_cost_details mpacd,
1919 mtl_material_transactions mmt
1920 WHERE mpacd.txn_category = 9
1921 AND mpacd.inventory_item_id = cppb.inventory_item_id
1922 AND mpacd.pac_period_id = cppb.pac_period_id
1923 AND mpacd.transaction_id = mmt.transaction_id
1924 AND mpacd.inventory_item_id = mmt.inventory_item_id
1925 AND mpacd.cost_group_id = cppb.cost_group_id
1926 AND mpacd.cost_layer_id = cppb.cost_layer_id
1927 AND mpacd.cost_element_id = cppb.cost_element_id
1928 AND mpacd.level_type = cppb.level_type)
1929 WHERE cppb.pac_period_id = p_pac_period_id
1930 AND cppb.cost_group_id = l_cost_group_id
1931 AND cppb.inventory_item_id = p_inventory_item_id
1932 AND cppb.txn_category = 9
1933 AND EXISTS (SELECT 1
1934 FROM CST_PAC_ITEM_COST_DETAILS cpicd1
1935 WHERE cppb.cost_layer_id = cpicd1.cost_layer_id
1936 AND cppb.cost_element_id = cpicd1.cost_element_id
1937 AND cppb.level_type = cpicd1.level_type);
1938
1939 FND_FILE.put_line
1940 ( FND_FILE.log
1941 , '<< CST_PERIODIC_ABSORPTION_PROC.Calc_Pmac_Update_Cppb'
1942 );
1943
1944 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1945 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1946 ,G_MODULE_HEAD || l_routine || '.end'
1947 ,l_routine || '>'
1948 );
1949 END IF;
1950
1951
1952 EXCEPTION
1953 WHEN FND_API.G_EXC_ERROR THEN
1954 RAISE FND_API.G_EXC_ERROR;
1955 WHEN OTHERS THEN
1956 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
1957 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
1958 FND_MESSAGE.set_token('MESSAGE', '('||SQLCODE||') '||SQLERRM);
1959 FND_MSG_PUB.Add;
1960 RAISE FND_API.G_EXC_ERROR;
1961 END Calc_Pmac_Update_Cppb;
1962
1963 -- +========================================================================+
1964 -- PROCEDURE: Create_Mpacd_With_New_Values PRIVATE UTILITY
1965 -- PARAMETERS: p_pac_period_id
1966 -- p_inventory_item_id interorg item id
1967 -- COMMENT:
1968 -- This to copy all the records into MPACD from pac transaction temp table
1969 -- only for a given interorg item in the current BOM level for the user
1970 -- specified PAC period
1971 -- Bug 7674673 fix : MPACD.txn_category 8 for cost owned interorg receipts,
1972 -- 9 for cost derived interorg shipments
1973 -- PRE-COND: none
1974 -- EXCEPTIONS: none
1975 -- +========================================================================+
1976 PROCEDURE Create_Mpacd_With_New_Values ( p_pac_period_id IN NUMBER
1977 , p_inventory_item_id IN NUMBER
1978 , p_cost_group_id IN NUMBER DEFAULT NULL
1979 )
1980 IS
1981
1985
1982 l_routine CONSTANT VARCHAR2(30) := 'create_mpacd_with_new_values';
1983
1984 BEGIN
1986 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1987 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1988 , G_MODULE_HEAD || l_routine ||'.begin'
1989 , l_routine || '<'
1990 );
1991 END IF;
1992
1993 INSERT INTO MTL_PAC_ACTUAL_COST_DETAILS
1994 ( COST_GROUP_ID
1995 , TRANSACTION_ID
1996 , PAC_PERIOD_ID
1997 , COST_TYPE_ID
1998 , COST_ELEMENT_ID
1999 , LEVEL_TYPE
2000 , INVENTORY_ITEM_ID
2001 , COST_LAYER_ID
2002 , ACTUAL_COST
2003 , VARIANCE_AMOUNT
2004 , USER_ENTERED
2005 , INSERTION_FLAG
2006 , TRANSACTION_COSTED_DATE
2007 , TXN_CATEGORY -- bug 7674673 fix
2008 , CREATION_DATE
2009 , CREATED_BY
2010 , LAST_UPDATE_DATE
2011 , LAST_UPDATED_BY
2012 , REQUEST_ID
2013 , PROGRAM_APPLICATION_ID
2014 , PROGRAM_ID
2015 , PROGRAM_UPDATE_DATE
2016 , LAST_UPDATE_LOGIN
2017 )
2018 SELECT
2019 mpacdt.cost_group_id
2020 , mpacdt.transaction_id
2021 , mpacdt.pac_period_id
2022 , mpacdt.cost_type_id
2023 , mpacdt.cost_element_id
2024 , mpacdt.level_type
2025 , mpacdt.inventory_item_id
2026 , mpacdt.cost_layer_id
2027 , mpacdt.actual_cost
2028 , mpacdt.variance_amount
2029 , mpacdt.user_entered
2030 , mpacdt.insertion_flag
2031 , mpacdt.transaction_costed_date
2032 , DECODE(cpitt.txn_type,1,8,2,9) txn_category -- bug 7674673 fix
2033 , SYSDATE
2034 , FND_GLOBAL.user_id
2035 , SYSDATE
2036 , FND_GLOBAL.user_id
2037 , FND_GLOBAL.conc_request_id
2038 , FND_GLOBAL.prog_appl_id
2039 , FND_GLOBAL.conc_program_id
2040 , SYSDATE
2041 , FND_GLOBAL.login_id
2042 FROM MTL_PAC_ACT_CST_DTL_TEMP mpacdt
2043 ,CST_PAC_INTERORG_TXNS_TMP cpitt
2044 WHERE mpacdt.pac_period_id = p_pac_period_id
2045 AND mpacdt.inventory_item_id = p_inventory_item_id
2046 AND mpacdt.cost_group_id = nvl(p_cost_group_id, mpacdt.cost_group_id)
2047 AND EXISTS (SELECT 'X'
2048 FROM cst_pac_intorg_itms_temp cpiit
2049 WHERE cpiit.pac_period_id = mpacdt.pac_period_id
2050 AND cpiit.inventory_item_id = mpacdt.inventory_item_id
2051 AND cpiit.cost_group_id = mpacdt.cost_group_id
2052 AND cpiit.diverging_flag = 'N'
2053 AND cpiit.interorg_receipt_flag = 'Y'
2054 AND cpiit.interorg_shipment_flag = 'Y')
2055 AND mpacdt.transaction_id = cpitt.transaction_id
2056 AND mpacdt.pac_period_id = cpitt.pac_period_id
2057 AND mpacdt.cost_group_id = cpitt.cost_group_id
2058 AND mpacdt.inventory_item_id = cpitt.inventory_item_id;
2059
2060
2061 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2062 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2063 , G_MODULE_HEAD || l_routine ||'.end'
2064 , l_routine || '>'
2065 );
2066 END IF;
2067
2068 EXCEPTION
2069 WHEN FND_API.G_EXC_ERROR THEN
2070 RAISE FND_API.G_EXC_ERROR;
2071 WHEN OTHERS THEN
2072 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2073 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION
2074 , G_MODULE_HEAD || l_routine ||'.others_exc'
2075 , 'others:' || SQLCODE || substr(SQLERRM, 1,200)
2076 );
2077 END IF;
2078 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
2079 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
2080 FND_MESSAGE.set_token('MESSAGE', '('||SQLCODE||') '||SQLERRM);
2081 FND_MSG_PUB.Add;
2082 RAISE FND_API.G_EXC_ERROR;
2083
2084 END; -- Create Mpacd With New Values
2085
2086
2087 -- +========================================================================+
2088 -- PROCEDURE: Update_Cpicd_With_New_Values PRIVATE UTILITY
2089 -- PARAMETERS: p_pac_period_id
2090 -- p_inventory_item_id interorg item id
2091 -- COMMENT:
2092 -- To update corresponding layers in cst_pac_item_cost_details
2093 -- with new cost, new buy cost, new make cost from MPACD for the period
2094 -- To update layer cost information in CPIC
2095 -- NOTE: PAC Transactions belongs to current BOM level interorg item
2096 -- PRE-COND: none
2097 -- EXCEPTIONS: none
2098 -- +========================================================================+
2099 PROCEDURE Update_Cpicd_With_New_Values(p_pac_period_id IN NUMBER
2100 ,p_inventory_item_id IN NUMBER
2101 ,p_cost_group_id IN NUMBER DEFAULT NULL
2102 ,p_cost_type_id IN NUMBER
2103 ,p_end_date IN DATE
2104 )
2105 IS
2106
2107 l_routine CONSTANT VARCHAR2(30) := 'update_cpicd_with_new_values';
2108
2109 CURSOR mpacd_distinct_cur(c_pac_period_id NUMBER
2110 ,c_inventory_item_id NUMBER
2111 ,c_cost_group_id NUMBER
2112 )
2113 IS
2114 SELECT
2115 DISTINCT mpacd.cost_layer_id
2116 , mpacd.cost_group_id
2117 , mpacd.cost_element_id
2118 , mpacd.level_type
2119 FROM mtl_pac_actual_cost_details mpacd, cst_pac_intorg_itms_temp cpiit
2120 WHERE cpiit.pac_period_id = c_pac_period_id
2121 AND cpiit.inventory_item_id = c_inventory_item_id
2122 AND cpiit.cost_group_id = nvl(c_cost_group_id, cpiit.cost_group_id)
2123 AND cpiit.diverging_flag = 'N'
2124 AND cpiit.interorg_receipt_flag = 'Y'
2125 AND mpacd.pac_period_id = cpiit.pac_period_id
2126 AND mpacd.inventory_item_id = cpiit.inventory_item_id
2127 AND mpacd.cost_group_id = cpiit.cost_group_id
2131 , mpacd.level_type;
2128 ORDER BY
2129 mpacd.cost_layer_id
2130 , mpacd.cost_element_id
2132
2133 mpacd_distinct_cur_row mpacd_distinct_cur%ROWTYPE;
2134
2135 CURSOR cost_group_cur(c_item_id NUMBER
2136 ,c_pac_period_id NUMBER
2137 )
2138 IS
2139 SELECT cost_group_id
2140 FROM cst_pac_intorg_itms_temp
2141 WHERE inventory_item_id = c_item_id
2142 AND pac_period_id = c_pac_period_id
2143 AND diverging_flag = 'N'
2144 AND interorg_receipt_flag = 'Y';
2145
2146 l_cg_elmnt_lv_idx BINARY_INTEGER;
2147
2148 l_item_cost NUMBER;
2149 l_item_balance NUMBER;
2150
2151 BEGIN
2152
2153 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2154 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2155 , G_MODULE_HEAD || l_routine ||'.begin'
2156 , l_routine || 'Item Id '||p_inventory_item_id|| ' Cost Group Id '||p_cost_group_id|| '<'
2157 );
2158 END IF;
2159
2160 FOR mpacd_idx IN mpacd_distinct_cur(p_pac_period_id
2161 ,p_inventory_item_id
2162 ,p_cost_group_id
2163 ) LOOP
2164
2165 l_cg_elmnt_lv_idx := to_char(mpacd_idx.cost_group_id) ||
2166 to_char(mpacd_idx.cost_element_id) || to_char(mpacd_idx.level_type);
2167 l_item_cost := G_CG_PWAC_COST_TBL(l_cg_elmnt_lv_idx).final_new_cost;
2168 l_item_balance := G_CG_PWAC_COST_TBL(l_cg_elmnt_lv_idx).period_new_balance;
2169
2170 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2171 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
2172 , G_MODULE_HEAD || l_routine ||'.cgellvic'
2173 , 'Cost Layer Id:' || mpacd_idx.cost_layer_id || 'Cost Group element level idx:' || l_cg_elmnt_lv_idx || ' Item Cost:' || l_item_cost || ' Item Balance:' || l_item_balance
2174 );
2175 END IF;
2176
2177 -- ========================================================
2178 -- Update cpicd with new cost, new buy cost, new make cost
2179 -- ========================================================
2180 UPDATE cst_pac_item_cost_details cpicd
2181 SET last_update_date = SYSDATE
2182 ,last_updated_by = FND_GLOBAL.user_id
2183 ,last_update_login = FND_GLOBAL.login_id
2184 ,request_id = FND_GLOBAL.conc_request_id
2185 ,program_application_id = FND_GLOBAL.prog_appl_id
2186 ,program_id = FND_GLOBAL.conc_program_id
2187 ,program_update_date = SYSDATE
2188 ,item_cost = l_item_cost
2189 ,item_balance = l_item_balance
2190 WHERE cpicd.cost_layer_id = mpacd_idx.cost_layer_id
2191 AND cpicd.cost_element_id = mpacd_idx.cost_element_id
2192 AND cpicd.level_type = mpacd_idx.level_type;
2193
2194 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2195 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
2196 , G_MODULE_HEAD || l_routine ||'.upd'
2197 , 'Update CPICD: ' || 'Item Cost:' || l_item_cost || ' Item Balance:' || l_item_balance
2198 );
2199 END IF;
2200
2201
2202 INSERT INTO CST_PAC_ITEM_COST_DETAILS cpicd
2203 (cost_layer_id,
2204 cost_element_id,
2205 level_type,
2206 last_update_date,
2207 last_updated_by,
2208 creation_date,
2209 created_by,
2210 last_update_login,
2211 request_id,
2212 program_application_id,
2213 program_id,
2214 program_update_date,
2215 item_cost,
2216 item_buy_cost,
2217 item_make_cost,
2218 item_balance,
2219 make_balance,
2220 buy_balance)
2221 (SELECT mpacd_idx.cost_layer_id,
2222 mpacd_idx.cost_element_id,
2223 mpacd_idx.level_type,
2224 sysdate,
2225 FND_GLOBAL.user_id,
2226 sysdate,
2227 FND_GLOBAL.user_id,
2228 FND_GLOBAL.login_id,
2229 FND_GLOBAL.conc_request_id,
2230 FND_GLOBAL.prog_appl_id,
2231 FND_GLOBAL.conc_program_id,
2232 sysdate,
2233 l_item_cost,
2234 0,
2235 0,
2236 l_item_balance,
2237 0,
2238 0
2239 FROM dual
2240 WHERE NOT EXISTS (SELECT 1
2241 FROM cst_pac_item_cost_details cpicd1
2242 WHERE cpicd1.cost_layer_id = mpacd_idx.cost_layer_id
2243 AND cpicd1.cost_element_id = mpacd_idx.cost_element_id
2244 AND cpicd1.level_type = mpacd_idx.level_type));
2245 -- =================================================================
2246 -- Update layer costs information
2247 -- =================================================================
2248
2249 UPDATE cst_pac_item_costs cpic
2250 SET (last_updated_by
2251 ,last_update_date
2252 ,last_update_login
2253 ,request_id
2254 ,program_application_id
2255 ,program_id
2256 ,program_update_date
2257 ,pl_material
2258 ,pl_material_overhead
2259 ,pl_resource
2260 ,pl_outside_processing
2261 ,pl_overhead
2262 ,tl_material
2263 ,tl_material_overhead
2267 ,material_cost
2264 ,tl_resource
2265 ,tl_outside_processing
2266 ,tl_overhead
2268 ,material_overhead_cost
2269 ,resource_cost
2270 ,outside_processing_cost
2271 ,overhead_cost
2272 ,pl_item_cost
2273 ,tl_item_cost
2274 ,item_cost
2275 ,item_buy_cost
2276 ,item_make_cost
2277 ,unburdened_cost
2278 ,burden_cost
2279 ) =
2280 (SELECT
2281 FND_GLOBAL.user_id
2282 , SYSDATE
2283 , FND_GLOBAL.login_id
2284 , FND_GLOBAL.conc_request_id
2285 , FND_GLOBAL.prog_appl_id
2286 , FND_GLOBAL.conc_program_id
2287 , SYSDATE
2288 , SUM(DECODE(level_type,2,DECODE(cost_element_id,1,item_cost,0),0))
2289 , SUM(DECODE(level_type,2,DECODE(cost_element_id,2,item_cost,0),0))
2290 , SUM(DECODE(level_type,2,DECODE(cost_element_id,3,item_cost,0),0))
2291 , SUM(DECODE(level_type,2,DECODE(cost_element_id,4,item_cost,0),0))
2292 , SUM(DECODE(level_type,2,DECODE(cost_element_id,5,item_cost,0),0))
2293 , SUM(DECODE(level_type,1,DECODE(cost_element_id,1,item_cost,0),0))
2294 , SUM(DECODE(level_type,1,DECODE(cost_element_id,2,item_cost,0),0))
2295 , SUM(DECODE(level_type,1,DECODE(cost_element_id,3,item_cost,0),0))
2296 , SUM(DECODE(level_type,1,DECODE(cost_element_id,4,item_cost,0),0))
2297 , SUM(DECODE(level_type,1,DECODE(cost_element_id,5,item_cost,0),0))
2298 , SUM(DECODE(cost_element_id,1,item_cost,0))
2299 , SUM(DECODE(cost_element_id,2,item_cost,0))
2300 , SUM(DECODE(cost_element_id,3,item_cost,0))
2301 , SUM(DECODE(cost_element_id,4,item_cost,0))
2302 , SUM(DECODE(cost_element_id,5,item_cost,0))
2303 , SUM(DECODE(level_type,2,item_cost,0))
2304 , SUM(DECODE(level_type,1,item_cost,0))
2305 , SUM(item_cost)
2306 , SUM(item_buy_cost)
2307 , SUM(item_make_cost)
2308 , SUM(DECODE(cost_element_id,2,DECODE(level_type,2,item_cost,0),item_cost))
2309 , SUM(DECODE(cost_element_id,2,DECODE(level_type,1,item_cost,0),0))
2310 FROM cst_pac_item_cost_details cpicd
2311 WHERE cpicd.cost_layer_id = mpacd_idx.cost_layer_id
2312 GROUP BY cpicd.cost_layer_id)
2313 WHERE cpic.cost_layer_id = mpacd_idx.cost_layer_id
2314 AND EXISTS
2315 (SELECT 'there is detail cost'
2316 FROM cst_pac_item_cost_details cpicd
2317 WHERE cpicd.cost_layer_id = mpacd_idx.cost_layer_id);
2318
2319
2320 END LOOP; -- end of mpacd temp loop
2321
2322 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2323 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
2324 , G_MODULE_HEAD || l_routine ||'.mpacdcg'
2325 , 'MPACD Loop complete: ' || ' Cost Group Id:' || p_cost_group_id
2326 );
2327 END IF;
2328
2329
2330 -- ================================================================
2331 -- Calculate Periodic Cost in CPICD, CPIC at the end of iteration
2332 -- process; Update Variance Amount in the last transaction of MPACD
2333 -- at the end of iteration process; Invoke calculate_periodic_cost
2334 -- for each cost group;
2335 -- Update CPPB Invoke Update_item_cppb for each CG
2336 -- ================================================================
2337 IF p_cost_group_id IS NOT NULL THEN
2338 Calc_Pmac_Update_Cppb(p_pac_period_id => p_pac_period_id
2339 ,p_cost_type_id => p_cost_type_id
2340 ,p_cost_group_id => p_cost_group_id
2341 ,p_inventory_item_id => p_inventory_item_id
2342 ,p_end_date => p_end_date
2343 ,p_user_id => FND_GLOBAL.user_id
2344 ,p_login_id => FND_GLOBAL.login_id
2345 ,p_req_id => FND_GLOBAL.conc_request_id
2346 ,p_prg_id => FND_GLOBAL.conc_program_id
2347 ,p_prg_appid => FND_GLOBAL.prog_appl_id
2348 );
2349 ELSE
2350 FOR cost_group_idx IN cost_group_cur(p_inventory_item_id
2351 ,p_pac_period_id)
2352 LOOP
2353 Calc_Pmac_Update_Cppb(p_pac_period_id => p_pac_period_id
2354 ,p_cost_type_id => p_cost_type_id
2355 ,p_cost_group_id => cost_group_idx.cost_group_id
2356 ,p_inventory_item_id => p_inventory_item_id
2357 ,p_end_date => p_end_date
2358 ,p_user_id => FND_GLOBAL.user_id
2359 ,p_login_id => FND_GLOBAL.login_id
2360 ,p_req_id => FND_GLOBAL.conc_request_id
2361 ,p_prg_id => FND_GLOBAL.conc_program_id
2362 ,p_prg_appid => FND_GLOBAL.prog_appl_id
2363 );
2364 END LOOP;
2365 END IF;
2366 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2367 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2368 , G_MODULE_HEAD || l_routine ||'.end'
2369 , l_routine || '>'
2370 );
2371 END IF;
2372
2373 EXCEPTION
2374 WHEN FND_API.G_EXC_ERROR THEN
2375 RAISE FND_API.G_EXC_ERROR;
2376 WHEN OTHERS THEN
2377 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2378 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION
2379 , G_MODULE_HEAD || l_routine ||'.others_exc'
2380 , 'others:' || SQLCODE || substr(SQLERRM, 1,200)
2381 );
2382 END IF;
2383 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
2387 RAISE FND_API.G_EXC_ERROR;
2384 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
2385 FND_MESSAGE.set_token('MESSAGE', '('||SQLCODE||') '||SQLERRM);
2386 FND_MSG_PUB.Add;
2388
2389 END; -- Update Cpicd With New Values
2390
2391
2392 -- +========================================================================+
2393 -- PROCEDURE: Set_Phase5_Status
2394 -- PARAMETERS:
2395 -- p_legal_entity_id NUMBER Legal Entity
2396 -- p_cost_group_id NUMBER Valid Cost Group in LE
2397 -- p_period_id NUMBER PAC Period Id
2398 -- p_phase_status NUMBER
2399 -- Not Applicable(0)
2400 -- Un Processed (1)
2401 -- Running (2)
2402 -- Error (3)
2403 -- Complete (4)
2404 -- COMMENT:
2405 -- This procedure sets the phase 5 status to Un Processed (1)
2406 -- at the end of final iteration or when the tolerance is achieved
2407 --
2408 -- USAGE: This procedure is invoked from api:iteration_process
2409 -- PRE-COND: none
2410 -- EXCEPTIONS: none
2411 -- +========================================================================+
2412 PROCEDURE Set_Phase5_Status(p_legal_entity_id IN NUMBER
2413 ,p_period_id IN NUMBER
2414 ,p_period_end_date IN DATE
2415 ,p_phase_status IN NUMBER
2416 )
2417 IS
2418
2419 l_routine CONSTANT VARCHAR2(30) := 'Set_Phase5_Status';
2420
2421 l_cost_group_id NUMBER;
2422 l_cst_group_idx BINARY_INTEGER;
2423
2424 BEGIN
2425
2426 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2427 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2428 , G_MODULE_HEAD || l_routine ||'.begin'
2429 , l_routine || '<'
2430 );
2431 END IF;
2432
2433 -- Set Phase 5 Status for all the valid cost groups
2434 l_cst_group_idx := G_CST_GROUP_TBL.FIRST;
2435 WHILE (l_cst_group_idx <= G_CST_GROUP_TBL.LAST) LOOP
2436
2437 -- index itself is the Cost Group Id
2438 l_cost_group_id := l_cst_group_idx;
2439
2440 -- Update Process Phases Table for Iteration Process
2441 UPDATE CST_PAC_PROCESS_PHASES
2442 SET process_status = p_phase_status
2443 ,process_date = SYSDATE
2444 ,process_upto_date = decode(p_phase_status,4,p_period_end_date,NULL)
2445 ,last_update_date = SYSDATE
2446 ,last_updated_by = FND_GLOBAL.user_id
2447 ,request_id = FND_GLOBAL.conc_request_id
2448 ,program_application_id = FND_GLOBAL.prog_appl_id
2449 ,program_id = FND_GLOBAL.conc_program_id
2450 ,program_update_date = SYSDATE
2451 ,last_update_login = FND_GLOBAL.login_id
2452 WHERE pac_period_id = p_period_id
2453 AND cost_group_id = l_cost_group_id
2454 AND process_phase = 5;
2455
2456 l_cst_group_idx := G_CST_GROUP_TBL.NEXT(l_cst_group_idx);
2457
2458 END LOOP;
2459
2460 -- the following commit is required to prevent
2461 -- a complete rollback if the process errors out
2462 COMMIT;
2463
2464 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2465 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2466 , G_MODULE_HEAD || l_routine ||'.end'
2467 , l_routine || '>'
2468 );
2469 END IF;
2470
2471 EXCEPTION
2472 WHEN FND_API.G_EXC_ERROR THEN
2473 RAISE FND_API.G_EXC_ERROR;
2474 WHEN OTHERS THEN
2475 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2476 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION
2477 , G_MODULE_HEAD || l_routine ||'.others_exc'
2478 , 'others:' || SQLCODE || substr(SQLERRM, 1,200)
2479 );
2480 END IF;
2481 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
2482 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
2483 FND_MESSAGE.set_token('MESSAGE', '('||SQLCODE||') '||SQLERRM);
2484 FND_MSG_PUB.Add;
2485 RAISE FND_API.G_EXC_ERROR;
2486
2487 END; -- Set_Phase5_Status
2488
2489
2490 -- +========================================================================+
2491 -- PROCEDURE: Set_Process_Status
2492 -- +========================================================================+
2493 -- PROCEDURE: Set_Process_Status
2494 -- PARAMETERS:
2495 -- p_legal_entity_id NUMBER Legal Entity
2496 -- p_period_id NUMBER PAC Period Id
2497 -- p_period_end_date DATE
2498 -- p_phase_status NUMBER
2499 -- Not Applicable(0)
2500 -- Un Processed (1)
2501 -- Running (2)
2502 -- Error (3)
2503 -- Complete (4)
2504 -- Resume (5) used when non-tolerance items exists
2505 -- COMMENT:
2506 -- This procedure sets the Interorg Transfer Cost Processor - iteration
2507 -- process phase status. The phase will be 7. When the iteration process
2508 -- is invoked through main program, the phase status will be set to 1
2509 -- to start with indicating that the status is in Un Processed.
2510 -- When the iteration process begins, the phase status will be set to 2
2511 -- indicating that the status is in Running for all the valid cost groups
2512 -- in the Legal Entity
2513 -- If the iteration process completed with error the status is 3
2514 -- If the iteration process completed where all the items achieved
2515 -- tolerance, then the status is set to 4 - Complete.
2516 -- If the iteration process completed where some of the items are left over
2517 -- with no tolerance achieved AND the resume option is Iteration for non
2518 -- tolerance items, then the status is set to 5 indicating that the
2519 -- status is in Resume where the process is not completed yet.
2523 -- is completed.
2520 -- If the iteration process completed where some of the items are left over
2521 -- with no tolerance achieved AND the resume option is Final Iteration, then
2522 -- the status is set to 4 - Complete indicating that the Iteration Process
2524 -- Update process_upto_date with the user specified process upto date at the
2525 -- end of run options: Start while setting the phase status to 5 - Resume
2526 --
2527 -- USAGE: This procedure is invoked from api:iteration_process
2528 -- PRE-COND: none
2529 -- EXCEPTIONS: none
2530 -- +========================================================================+
2531 PROCEDURE Set_Process_Status( p_legal_entity_id IN NUMBER
2532 , p_period_id IN NUMBER
2533 , p_period_end_date IN DATE
2534 , p_phase_status IN NUMBER
2535 )
2536 IS
2537
2538 l_routine CONSTANT VARCHAR2(30) := 'set_process_status';
2539
2540 l_cost_group_id NUMBER;
2541 l_cst_group_idx BINARY_INTEGER;
2542
2543 BEGIN
2544
2545 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2546 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2547 , G_MODULE_HEAD || l_routine ||'.begin'
2548 , l_routine || '<'
2549 );
2550 END IF;
2551
2552 -- Set Phase Status for all the valid cost groups
2553 l_cst_group_idx := G_CST_GROUP_TBL.FIRST;
2554 WHILE (l_cst_group_idx <= G_CST_GROUP_TBL.LAST) LOOP
2555
2556 -- index itself is the Cost Group Id
2557 l_cost_group_id := l_cst_group_idx;
2558
2559 -- Update Process Phases Table for Iteration Process
2560 UPDATE CST_PAC_PROCESS_PHASES
2561 SET process_status = p_phase_status
2562 ,process_date = SYSDATE
2563 ,process_upto_date = decode(p_phase_status,4,p_period_end_date,
2564 5,p_period_end_date,
2565 3,p_period_end_date,NULL)
2566 ,last_update_date = SYSDATE
2567 ,last_updated_by = FND_GLOBAL.user_id
2568 ,request_id = FND_GLOBAL.conc_request_id
2569 ,program_application_id = FND_GLOBAL.prog_appl_id
2570 ,program_id = FND_GLOBAL.conc_program_id
2571 ,program_update_date = SYSDATE
2572 ,last_update_login = FND_GLOBAL.login_id
2573 WHERE pac_period_id = p_period_id
2574 AND cost_group_id = l_cost_group_id
2575 AND process_phase = 7;
2576
2577 l_cst_group_idx := G_CST_GROUP_TBL.NEXT(l_cst_group_idx);
2578
2579 END LOOP;
2580
2581 -- the following commit is required to prevent
2582 -- a complete rollback if the process errors out
2583
2584 COMMIT;
2585
2586 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2587 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2588 , G_MODULE_HEAD || l_routine ||'.end'
2589 , l_routine || '>'
2590 );
2591 END IF;
2592
2593 EXCEPTION
2594 WHEN FND_API.G_EXC_ERROR THEN
2595 RAISE FND_API.G_EXC_ERROR;
2596 WHEN OTHERS THEN
2597 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2598 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION
2599 , G_MODULE_HEAD || l_routine ||'.others_exc'
2600 , 'others:' || SQLCODE || substr(SQLERRM, 1,200)
2601 );
2602 END IF;
2603 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
2604 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
2605 FND_MESSAGE.set_token('MESSAGE', '('||SQLCODE||') '||SQLERRM);
2606 FND_MSG_PUB.Add;
2607 RAISE FND_API.G_EXC_ERROR;
2608 END; -- Set_Process_Status
2609
2610
2611 -- +============================================================================+
2612 -- PROCEDURE: Verify_Tolerance_Of_Item
2613 -- PARAMETERS:
2614 -- p_cost_type_id NUMBER Cost Type Id
2615 -- p_inventory_item_id NUMBER Interorg Item
2616 -- p_inventory_item_number VARCHAR2 Inventory Item Number
2617 -- p_period_id NUMBER PAC Period Id
2618 -- p_period_start_date DATE PAC Period Start Date
2619 -- p_period_end_date DATE PAC Period End Date
2620 -- p_tolerance NUMBER User specified tolerance
2621 -- p_iteration_num NUMBER Iteration Number
2622 -- p_end_iteration_num NUMBER Last Iteration Number
2623 -- x_tolerance_flag VARCHAR2 Tolerance Flag
2624 -- COMMENT:
2625 -- This procedure determines the difference between PMAC of current iteration
2626 -- and PMAC of previous iteration
2627 -- Difference will be compared with user specified tolerance as given in the
2628 -- parameter: p_tolerance.
2629 -- If the expected tolerance is achieved for all the cost groups, then the
2630 -- tolerance flag x_tolerance_flag is set to 'Y'.
2631 -- If the tolerance NOT achieved for any of the cost groups, then the tolerance
2632 -- flag x_tolerance_flag is set to 'N'.
2633 -- USAGE: This procedure is invoked from api:iteration_process
2634 -- PRE-COND: none
2635 -- EXCEPTIONS: none
2636 -- +============================================================================+
2637 PROCEDURE Verify_Tolerance_Of_Item(p_cost_type_id IN NUMBER
2638 ,p_inventory_item_id IN NUMBER
2639 ,p_inventory_item_number IN VARCHAR2
2640 ,p_period_id IN NUMBER
2641 ,p_period_start_date IN DATE
2642 ,p_period_end_date IN DATE
2643 ,p_tolerance IN NUMBER
2644 ,p_iteration_num IN NUMBER
2648 IS
2645 ,p_end_iteration_num IN NUMBER
2646 ,x_tolerance_flag OUT NOCOPY VARCHAR2
2647 )
2649
2650 l_routine CONSTANT VARCHAR2(30) := 'Verify_Tolerance_Of_Item';
2651
2652 -- Optimal Cost Group according to sequence number
2653 -- NOTE: cost group having both interorg receipts and interorg shipments are verified for tolerance.
2654 -- Bug 9828830 fix: Cost group having both interorg receipts and interorg shipments are verified.
2655 -- condition interorg_shipment_flag = 'Y' added
2656
2657 CURSOR cost_group_item_info_cur(c_item_id NUMBER
2658 ,c_pac_period_id NUMBER
2659 )
2660 IS
2661 SELECT
2662 cost_group_id
2663 , prev_itr_item_cost
2664 , item_cost
2665 FROM cst_pac_intorg_itms_temp
2666 WHERE inventory_item_id = c_item_id
2667 AND pac_period_id = c_pac_period_id
2668 AND interorg_receipt_flag = 'Y'
2669 AND interorg_shipment_flag = 'Y'
2670 AND DIVERGING_FLAG = 'N'
2671 ORDER BY sequence_num;
2672
2673 cost_group_item_info_row cost_group_item_info_cur%ROWTYPE;
2674
2675 l_correspond_actual_cost NUMBER;
2676 l_correspond_txn_flag VARCHAR2(1);
2677 l_cost_group_id NUMBER;
2678 -- Number of receipt cost element tolerance not achieved count
2679 l_non_tol_count NUMBER := 0;
2680
2681 l_inventory_item_number VARCHAR2(1025);
2682 l_correspond_org_id NUMBER;
2683
2684 l_cost_group_id_idx BINARY_INTEGER;
2685 l_cost_group_name VARCHAR2(10);
2686
2687 l_unit_trans_cost NUMBER := 0;
2688 l_moh_absorption_cost NUMBER := 0;
2689
2690 l_diff_cg NUMBER;
2691
2692 -- Message variable to display output file messages
2693 l_message VARCHAR2(2000);
2694
2695 BEGIN
2696
2697 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2698 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2699 , G_MODULE_HEAD || l_routine ||'.begin'
2700 , l_routine || '<'
2701 );
2702 END IF;
2703
2704 -- Assign Inventory Item Number
2705 l_inventory_item_number := p_inventory_item_number;
2706
2707 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2708 FND_LOG.string(FND_LOG.LEVEL_EVENT
2709 , G_MODULE_HEAD || l_routine ||'.item'
2710 , 'Inventory Item Id:' || p_inventory_item_id ||
2711 ' Item Number:' || l_inventory_item_number
2712 );
2713 END IF;
2714
2715 -- ==========================================================
2716 -- Verify tolerance for each cost group
2717 -- ==========================================================
2718
2719 -- Loop for each optimal cost group
2720 -- NOTE: sequence num 1 is not used for tolerance check
2721 -- interorg_receipt_flag should be 'Y'
2722 OPEN cost_group_item_info_cur(p_inventory_item_id
2723 ,p_period_id
2724 );
2725
2726 FETCH cost_group_item_info_cur
2727 INTO cost_group_item_info_row;
2728
2729 WHILE (cost_group_item_info_cur%FOUND ) LOOP
2730
2731 -- Display Inventory Item Id, Item number
2732
2733 l_message := p_inventory_item_id ||' '||l_inventory_item_number||' ';
2734 -- ======================================
2735 -- Display Cost Group PMAC iteration info
2736 -- ======================================
2737 l_cost_group_id_idx := cost_group_item_info_row.cost_group_id;
2738 l_cost_group_name := G_CST_GROUP_TBL(l_cost_group_id_idx).cost_group;
2739
2740 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2741 FND_LOG.string(FND_LOG.LEVEL_EVENT
2742 , G_MODULE_HEAD || l_routine ||'.cgname'
2743 , 'Cost Group Index:' || l_cost_group_id_idx || ' Cost Group Name:' || l_cost_group_name ||
2744 ' Prev Itr Item Cost:' || cost_group_item_info_row.prev_itr_item_cost ||
2745 ' Curr Itr Item Cost:' || cost_group_item_info_row.item_cost
2746 );
2747 END IF;
2748
2749 -- difference between current iteration pmac item cost and previous iteration pmac
2750 -- item cost
2751 l_diff_cg :=
2752 ABS(nvl(cost_group_item_info_row.item_cost,0) - nvl(cost_group_item_info_row.prev_itr_item_cost,0));
2753
2754 -- Output format should be same as specified in message CST_PAC_PMAC_ITR_PROMPT
2755
2756 l_message := l_message||l_cost_group_name||' '||p_iteration_num||' '
2757 ||cost_group_item_info_row.item_cost||' '||cost_group_item_info_row.prev_itr_item_cost||' '||l_diff_cg;
2758 FND_FILE.put_line(FND_FILE.OUTPUT, l_message);
2759
2760 IF l_diff_cg > p_tolerance THEN
2761 l_non_tol_count := 1;
2762 -- EXIT;
2763 END IF;
2764
2765 FETCH cost_group_item_info_cur
2766 INTO cost_group_item_info_row;
2767
2768 END LOOP; -- cost group item info cursor end loop
2769
2770 CLOSE cost_group_item_info_cur;
2771
2772 IF l_non_tol_count = 0 THEN
2773 -- Tolerance achieved for all the interorg receipts
2774 x_tolerance_flag := 'Y';
2775 FND_FILE.put_line(FND_FILE.OUTPUT, G_TOL_ACHIEVED_MESSAGE);
2776
2777 ELSE
2778 -- Tolerance not achieved for the interorg receipts
2779 -- There are some interorg receipts still yet to achieve the tolerance
2780 x_tolerance_flag := 'N';
2781 FND_FILE.put_line(FND_FILE.OUTPUT, G_TOL_NOT_ACHIEVED_MESSAGE);
2782
2783 END IF;
2784
2785 EXCEPTION
2786 WHEN FND_API.G_EXC_ERROR THEN
2787 RAISE FND_API.G_EXC_ERROR;
2788 WHEN OTHERS THEN
2792 , 'Item_id'||p_inventory_item_id ||'Iteration Number' ||p_iteration_num || SQLCODE || substr(SQLERRM, 1,200)
2789 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2790 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION
2791 , G_MODULE_HEAD || l_routine ||'.others_exc'
2793 );
2794 END IF;
2795 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
2796 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
2797 FND_MESSAGE.set_token('MESSAGE', 'Item_id'||p_inventory_item_id ||'Iteration Number' ||p_iteration_num ||'('||SQLCODE||') '||SQLERRM);
2798 FND_MSG_PUB.Add;
2799 RAISE FND_API.G_EXC_ERROR;
2800
2801 END Verify_Tolerance_Of_Item;
2802
2803 -- +========================================================================+
2804 -- PROCEDURE: Initialize
2805 -- PARAMETERS:
2806 -- p_legal_entity_id IN NUMBER
2807 -- COMMENT:
2808 -- This procedure is to initialize Global PL/SQL tables
2809 -- G_CST_GROUP_TBL to store valid Cost Groups in Legal Entity
2810 -- G_CST_GROUP_ORG_TBL to store valid organizations in those cost groups
2811 -- This procedure is called by the API Iteration Process
2812 -- PRE-COND: none
2813 -- EXCEPTIONS: none
2814 -- +========================================================================+
2815 PROCEDURE Initialize
2816 ( p_legal_entity_id IN NUMBER
2817 )
2818 IS
2819 -- routine name local constant variable
2820 l_routine CONSTANT VARCHAR2(30) := 'Initialize';
2821
2822 -- Cursor to get valid Cost Groups
2823 -- and corresponding Item Master Organization
2824 CURSOR cst_group_le_cursor ( c_legal_entity_id NUMBER)
2825 IS
2826 SELECT
2827 cost_group_id
2828 , cost_group
2829 , organization_id master_organization_id
2830 FROM
2831 cst_cost_groups ccg
2832 WHERE legal_entity = c_legal_entity_id;
2833
2834 cst_group_row cst_group_le_cursor%rowtype;
2835
2836 -- Cursor to get all the organizations across Cost Groups in Legal Entity
2837 -- Function: check_cst_group is used to validate for the Cost Group in LE
2838 CURSOR cst_group_org_cursor
2839 IS
2840 SELECT
2841 cost_group_id
2842 , organization_id
2843 FROM
2844 cst_cost_group_assignments ccga
2845 WHERE check_cst_group(ccga.cost_group_id) = 'Y'
2846 ORDER BY cost_group_id;
2847
2848 cst_group_org_row cst_group_org_cursor%rowtype;
2849
2850 -- binary integer variables
2851 l_cost_group_id_idx BINARY_INTEGER;
2852 l_organization_id_idx BINARY_INTEGER;
2853
2854 BEGIN
2855 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2856 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2857 , G_MODULE_HEAD || l_routine ||'.begin'
2858 , 'Initialize <'
2859 );
2860 END IF;
2861
2862 -- Delete records from PL/SQL table
2863 CST_PAC_ITERATION_PROCESS_PVT.G_CST_GROUP_TBL.delete;
2864 CST_PAC_ITERATION_PROCESS_PVT.G_CST_GROUP_ORG_TBL.delete;
2865
2866 -- +================================================================*
2867 -- Get valid cost groups and store it in PL/SQL table
2868 -- G_CST_GROUP_TBL
2869 -- +================================================================*
2870 -- Get valid Cost Groups in Legal Entity
2871 OPEN cst_group_le_cursor(p_legal_entity_id);
2872
2873 FETCH cst_group_le_cursor
2874 INTO cst_group_row;
2875
2876 -- store the valid Cost Groups in PL/SQL table
2877 WHILE cst_group_le_cursor%FOUND LOOP
2878 l_cost_group_id_idx := cst_group_row.cost_group_id;
2879 CST_PAC_ITERATION_PROCESS_PVT.G_CST_GROUP_TBL(l_cost_group_id_idx).cost_group_id := cst_group_row.cost_group_id;
2880 CST_PAC_ITERATION_PROCESS_PVT.G_CST_GROUP_TBL(l_cost_group_id_idx).cost_group := cst_group_row.cost_group;
2881 CST_PAC_ITERATION_PROCESS_PVT.G_CST_GROUP_TBL(l_cost_group_id_idx).master_organization_id := cst_group_row.master_organization_id;
2882 CST_PAC_ITERATION_PROCESS_PVT.G_CST_GROUP_TBL(l_cost_group_id_idx).period_new_quantity := 0;
2883
2884 FETCH cst_group_le_cursor
2885 INTO cst_group_row;
2886
2887 END LOOP;
2888
2889 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2890 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
2891 , G_MODULE_HEAD || l_routine || '.valid_cost_group'
2892 , 'Number of valid cost groups in LE:' || G_CST_GROUP_TBL.COUNT
2893 );
2894 END IF;
2895
2896 CLOSE cst_group_le_cursor;
2897
2898 -- +================================================================*
2899 -- Get all the organizations in valid cost groups
2900 -- Store the organizations in PL/SQL table G_CST_GROUP_ORG_TBL
2901 -- +================================================================*
2902 -- Get All the Organizations in valid cost groups
2903 OPEN cst_group_org_cursor;
2904
2905 FETCH cst_group_org_cursor
2906 INTO cst_group_org_row;
2907
2908 -- Store all the organizations across valid cost groups
2909 WHILE cst_group_org_cursor%FOUND LOOP
2910 l_organization_id_idx := cst_group_org_row.organization_id;
2911 CST_PAC_ITERATION_PROCESS_PVT.G_CST_GROUP_ORG_TBL(l_organization_id_idx).cost_group_id := cst_group_org_row.cost_group_id;
2912
2913 CST_PAC_ITERATION_PROCESS_PVT.G_CST_GROUP_ORG_TBL(l_organization_id_idx).organization_id := cst_group_org_row.organization_id;
2914
2915 FETCH cst_group_org_cursor
2916 INTO cst_group_org_row;
2917
2918 END LOOP;
2919
2920 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2921 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
2922 , G_MODULE_HEAD || l_routine || '.valid_organizations'
2923 , 'Number of valid organizations:'|| G_CST_GROUP_ORG_TBL.COUNT
2924 );
2925 END IF;
2926
2927 CLOSE cst_group_org_cursor;
2931 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2928
2929
2930 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2932 , G_MODULE_HEAD || l_routine ||'.end'
2933 , l_routine || '>'
2934 );
2935 END IF;
2936
2937 EXCEPTION
2938 WHEN FND_API.G_EXC_ERROR THEN
2939 RAISE FND_API.G_EXC_ERROR;
2940 WHEN OTHERS THEN
2941 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2942 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION
2943 , G_MODULE_HEAD || l_routine ||'.others_exc'
2944 , 'others:' || SQLCODE || substr(SQLERRM, 1,200)
2945 );
2946 END IF;
2947 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
2948 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
2949 FND_MESSAGE.set_token('MESSAGE', '('||SQLCODE||') '||SQLERRM);
2950 FND_MSG_PUB.Add;
2951 RAISE FND_API.G_EXC_ERROR;
2952
2953 END Initialize;
2954
2955 -- +========================================================================+
2956 -- PROCEDURE: Populate_Temp_Tables
2957 -- PARAMETERS:
2958 -- p_cost_group_id IN NUMBER
2959 -- p_period_start_date IN DATE
2960 -- p_period_end_date IN DATE
2961 -- COMMENT:
2962 -- This procedure is called by the Iterative PAC Worker
2963 -- Support Transfer pricing option as below
2964 -- Value 0: No include the interorg txns of OM
2965 -- Value 1: Yes, Price not as incoming cost include the interorg txns of OM
2966 -- Value 2: Yes, Price as incoming cost do not include the interorg txns of OM
2967 -- OPM SCENARIO: Exclude any interorg transactions due to OPM organization
2968 -- Cost Owned Txns:
2969 -- Exclude Direct interorg receipt txn coming from OPM org
2970 -- Exclude Intransit Shipment txn FOB:Shipment processed in discrete coming
2971 -- from OPM organization
2972 -- Exclude Logical intransit receipt (15) due to OPM org
2973 -- Cost Derived Txns:
2974 -- Exclude Direct interorg shipment txn to OPM org
2975 -- Exclude Intransit receipt txn FOB:Receipt processed in discrete due to OPM
2976 -- Exclude Logical intransit shipment (22) due to OPM org
2977 -- PRE-COND: none
2978 -- EXCEPTIONS: none
2979 -- +==========================================================================+
2980
2981 PROCEDURE Populate_Temp_Tables
2982 ( p_cost_group_id IN NUMBER
2983 , p_period_id IN NUMBER
2984 , p_period_start_date IN DATE
2985 , p_period_end_date IN DATE
2986 )
2987 IS
2988
2989 -- Routine name local constant variable
2990 l_routine CONSTANT VARCHAR2(30) := 'Populate_Temp_Tables';
2991 l_txn_type NUMBER := 0;
2992 BEGIN
2993
2994 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2995 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2996 , G_MODULE_HEAD || l_routine ||'.begin'
2997 , l_routine || '<'
2998 );
2999 END IF;
3000
3001 DELETE FROM CST_PAC_INTERORG_TXNS_TMP WHERE COST_GROUP_ID = p_cost_group_id AND PAC_PERIOD_ID = p_period_id;
3002
3003 --Cost owned across CG interorg transactions for this cost group being populated in CST_PAC_INTERORG_TXNS_TMP
3004 l_txn_type := 1;
3005
3006 INSERT INTO CST_PAC_INTERORG_TXNS_TMP
3007 ( transaction_id,
3008 transaction_action_id,
3009 transaction_source_type_id,
3010 inventory_item_id,
3011 primary_quantity,
3012 periodic_primary_quantity,
3013 organization_id,
3014 transfer_organization_id,
3015 subinventory_code,
3016 transfer_price,
3017 shipment_number,
3018 transfer_transaction_id,
3019 waybill_airbill,
3020 transfer_cost,
3021 transportation_cost,
3022 transfer_percentage,
3023 cost_group_id,
3024 transfer_cost_group_id,
3025 txn_type,
3026 pac_period_id)
3027 (SELECT
3028 mmt.transaction_id transaction_id
3029 , mmt.transaction_action_id transaction_action_id
3030 , mmt.transaction_source_type_id transaction_source_type_id
3031 , mmt.inventory_item_id inventory_item_id
3032 , mmt.primary_quantity primary_quantity
3033 , mmt.periodic_primary_quantity periodic_primary_quantity
3034 , mmt.organization_id organization_id
3035 , nvl(mmt.transfer_organization_id,-1) transfer_organization_id
3036 , mmt.subinventory_code subinventory_code
3037 , nvl(mmt.transfer_price,0) transfer_price
3038 , mmt.shipment_number shipment_number
3039 , mmt.transfer_transaction_id transfer_transaction_id
3040 , mmt.waybill_airbill waybill_airbill
3041 , nvl(mmt.transfer_cost,0) transfer_cost
3042 , nvl(mmt.transportation_cost,0) transportation_cost
3043 , nvl(mmt.transfer_percentage,0) transfer_percentage
3044 , p_cost_group_id cost_group_id
3045 , decode(c1.cost_group_id, p_cost_group_id, c2.cost_group_id, c1.cost_group_id) transfer_cost_group_id
3046 , l_txn_type txn_type
3047 , p_period_id pac_period_id
3048 FROM
3049 mtl_material_transactions mmt
3050 , mtl_parameters mp
3051 , cst_cost_group_assignments c1
3052 , cst_cost_group_assignments c2
3053 , cst_cost_groups ccg1
3054 , cst_cost_groups ccg2
3055 WHERE
3056 mmt.transaction_date BETWEEN p_period_start_date AND p_period_end_date
3057 AND mmt.organization_id = nvl(mmt.owning_organization_id, mmt.organization_id)
3058 AND nvl(mmt.owning_tp_type,2) = 2
3059 AND mmt.organization_id = mp.organization_id
3060 AND nvl(mp.process_enabled_flag,'N') = 'N'
3061 AND c1.organization_id = mmt.organization_id
3062 AND c2.organization_id = mmt.transfer_organization_id
3063 AND c1.cost_group_id <> c2.cost_group_id
3064 AND c1.cost_group_id = ccg1.cost_group_id
3065 AND c2.cost_group_id = ccg2.cost_group_id
3066 AND ccg1.legal_entity = ccg2.legal_entity
3067 AND (
3068 (mmt.transaction_action_id = 3 AND mmt.transaction_source_type_id = 13
3072 AND ccga1.organization_id = mmt.organization_id
3069 AND EXISTS ( SELECT 'X'
3070 FROM cst_cost_group_assignments ccga1
3071 WHERE ccga1.cost_group_id = p_cost_group_id
3073 AND mmt.primary_quantity > 0))
3074 OR (mmt.transaction_action_id = 21 AND mmt.transaction_source_type_id = 13
3075 AND EXISTS ( SELECT 'X'
3076 FROM mtl_interorg_parameters mip,
3077 cst_cost_group_assignments ccga2
3078 WHERE mip.from_organization_id = mmt.organization_id
3079 AND mip.to_organization_id = mmt.transfer_organization_id
3080 AND nvl(mmt.fob_point,mip.fob_point) = 1
3081 AND ccga2.organization_id = mip.to_organization_id
3082 AND ccga2.cost_group_id = p_cost_group_id))
3083 OR (mmt.transaction_action_id = 12 AND mmt.transaction_source_type_id = 13
3084 AND EXISTS ( SELECT 'X'
3085 FROM mtl_interorg_parameters mip,
3086 cst_cost_group_assignments ccga3
3087 WHERE mip.from_organization_id = mmt.transfer_organization_id
3088 AND mip.to_organization_id = mmt.organization_id
3089 AND nvl(mmt.fob_point,mip.fob_point) = 2
3090 AND ccga3.organization_id = mip.to_organization_id
3091 AND ccga3.cost_group_id = p_cost_group_id))
3092 )
3093 UNION
3094 SELECT
3095 mmt.transaction_id transaction_id
3096 , mmt.transaction_action_id transaction_action_id
3097 , mmt.transaction_source_type_id transaction_source_type_id
3098 , mmt.inventory_item_id inventory_item_id
3099 , mmt.primary_quantity primary_quantity
3100 , mmt.periodic_primary_quantity periodic_primary_quantity
3101 , mmt.organization_id organization_id
3102 , nvl(mmt.transfer_organization_id,-1) transfer_organization_id
3103 , mmt.subinventory_code subinventory_code
3104 , nvl(mmt.transfer_price,0) transfer_price
3105 , mmt.shipment_number shipment_number
3106 , mmt.transfer_transaction_id transfer_transaction_id
3107 , mmt.waybill_airbill waybill_airbill
3108 , nvl(mmt.transfer_cost,0) transfer_cost
3109 , nvl(mmt.transportation_cost,0) transportation_cost
3110 , nvl(mmt.transfer_percentage,0) transfer_percentage
3111 , p_cost_group_id cost_group_id
3112 , decode(c1.cost_group_id, p_cost_group_id, c2.cost_group_id, c1.cost_group_id) transfer_cost_group_id
3113 , l_txn_type txn_type
3114 , p_period_id pac_period_id
3115 FROM
3116 mtl_material_transactions mmt
3117 , mtl_parameters mp
3118 , cst_cost_group_assignments c1
3119 , cst_cost_group_assignments c2
3120 , cst_cost_groups ccg1
3121 , cst_cost_groups ccg2
3122 WHERE
3123 mmt.transaction_date BETWEEN p_period_start_date AND p_period_end_date
3124 AND mmt.organization_id = nvl(mmt.owning_organization_id, mmt.organization_id)
3125 AND nvl(mmt.owning_tp_type,2) = 2
3126 AND mmt.organization_id = mp.organization_id
3127 AND nvl(mp.process_enabled_flag,'N') = 'N'
3128 AND c1.organization_id = mmt.organization_id
3129 AND c2.organization_id = mmt.transfer_organization_id
3130 AND c1.cost_group_id <> c2.cost_group_id
3131 AND c1.cost_group_id = ccg1.cost_group_id
3132 AND c2.cost_group_id = ccg2.cost_group_id
3133 AND ccg1.legal_entity = ccg2.legal_entity
3134 AND NOT EXISTS (SELECT 'X'
3135 FROM mtl_intercompany_parameters mip
3136 WHERE nvl(fnd_profile.value('INV_INTERCOMPANY_INVOICE_INTERNAL_ORDER'),0) = 1
3137 AND mip.flow_type = 1
3138 AND nvl(fnd_profile.value('CST_TRANSFER_PRICING_OPTION'),0) = 2
3139 AND mip.ship_organization_id = (select to_number(hoi.org_information3)
3140 from hr_organization_information hoi
3141 where hoi.organization_id = decode(mmt.transaction_action_id,21,
3142 mmt.organization_id,mmt.transfer_organization_id)
3143 AND hoi.org_information_context = 'Accounting Information')
3144 AND mip.sell_organization_id = (select to_number(hoi2.org_information3)
3145 from hr_organization_information hoi2
3146 where hoi2.organization_id = decode(mmt.transaction_action_id,21,
3147 mmt.transfer_organization_id, mmt.organization_id)
3148 AND hoi2.org_information_context = 'Accounting Information'))
3149 AND (
3150 (mmt.transaction_action_id = 3 AND transaction_source_type_id IN (7,8)
3151 AND EXISTS ( SELECT 'X'
3152 FROM cst_cost_group_assignments ccga1
3153 WHERE ccga1.cost_group_id = p_cost_group_id
3154 AND ccga1.organization_id = mmt.organization_id
3155 AND mmt.primary_quantity > 0))
3156 OR (mmt.transaction_action_id = 21 AND transaction_source_type_id IN (7,8)
3157 AND EXISTS ( SELECT 'X'
3158 FROM mtl_interorg_parameters mip,
3159 cst_cost_group_assignments ccga2
3160 WHERE mip.from_organization_id = mmt.organization_id
3161 AND mip.to_organization_id = mmt.transfer_organization_id
3162 AND nvl(mmt.fob_point,mip.fob_point) = 1
3163 AND ccga2.organization_id = mip.to_organization_id
3164 AND ccga2.cost_group_id = p_cost_group_id))
3165 OR (mmt.transaction_action_id = 12 AND mmt.transaction_source_type_id IN (7,8)
3166 AND EXISTS ( SELECT 'X'
3167 FROM mtl_interorg_parameters mip,
3168 cst_cost_group_assignments ccga3
3169 WHERE mip.from_organization_id = mmt.transfer_organization_id
3170 AND mip.to_organization_id = mmt.organization_id
3171 AND nvl(mmt.fob_point,mip.fob_point) = 2
3172 AND ccga3.organization_id = mip.to_organization_id
3173 AND ccga3.cost_group_id = p_cost_group_id))
3174 ));
3178
3175
3176
3177 --Cost derived across CG interorg transactions for this cost group being populated in CST_PAC_INTERORG_TXNS_TMP
3179 l_txn_type := 2;
3180
3181 INSERT INTO CST_PAC_INTERORG_TXNS_TMP
3182 ( transaction_id,
3183 transaction_action_id,
3184 transaction_source_type_id,
3185 inventory_item_id,
3186 primary_quantity,
3187 periodic_primary_quantity,
3188 organization_id,
3189 transfer_organization_id,
3190 subinventory_code,
3191 transfer_price,
3192 shipment_number,
3193 transfer_transaction_id,
3194 waybill_airbill,
3195 transfer_cost,
3196 transportation_cost,
3197 transfer_percentage,
3198 cost_group_id,
3199 transfer_cost_group_id,
3200 txn_type,
3201 pac_period_id)
3202 (SELECT
3203 mmt.transaction_id transaction_id
3204 , mmt.transaction_action_id transaction_action_id
3205 , mmt.transaction_source_type_id transaction_source_type_id
3206 , mmt.inventory_item_id inventory_item_id
3207 , mmt.primary_quantity primary_quantity
3208 , mmt.periodic_primary_quantity periodic_primary_quantity
3209 , mmt.organization_id organization_id
3210 , nvl(mmt.transfer_organization_id,-1) transfer_organization_id
3211 , mmt.subinventory_code subinventory_code
3212 , nvl(mmt.transfer_price,0) transfer_price
3213 , mmt.shipment_number shipment_number
3214 , mmt.transfer_transaction_id transfer_transaction_id
3215 , mmt.waybill_airbill waybill_airbill
3216 , nvl(mmt.transfer_cost,0) transfer_cost
3217 , nvl(mmt.transportation_cost,0) transportation_cost
3218 , nvl(mmt.transfer_percentage,0) transfer_percentage
3219 , p_cost_group_id cost_group_id
3220 , NULL transfer_cost_group_id
3221 , l_txn_type txn_type
3222 , p_period_id pac_period_id
3223 FROM
3224 mtl_material_transactions mmt
3225 , mtl_parameters mp
3226 , mtl_parameters mptrans
3227 WHERE
3228 mmt.transaction_date BETWEEN p_period_start_date AND p_period_end_date
3229 AND mmt.organization_id = nvl(mmt.owning_organization_id, mmt.organization_id)
3230 AND nvl(mmt.owning_tp_type,2) = 2
3231 AND mmt.organization_id = mp.organization_id
3232 AND nvl(mp.process_enabled_flag, 'N') = 'N'
3233 AND (transaction_action_id in (3,12,21) AND transaction_source_type_id = 13
3234 AND EXISTS (SELECT 'EXISTS'
3235 FROM cst_cost_group_assignments ccga
3236 WHERE ccga.cost_group_id = p_cost_group_id
3237 AND (ccga.organization_id = mmt.organization_id OR
3238 ccga.organization_id = mmt.transfer_organization_id)))
3239 AND mptrans.organization_id = mmt.transfer_organization_id
3240 AND mptrans.process_enabled_flag = 'N'
3241 AND (transaction_action_id IN (3,12,21)
3242 AND NOT EXISTS (
3243 SELECT 'X'
3244 FROM cst_cost_group_assignments c1, cst_cost_group_assignments c2
3245 WHERE c1.organization_id = mmt.organization_id
3246 AND c2.organization_id = mmt.transfer_organization_id
3247 AND c1.cost_group_id = c2.cost_group_id)
3248 AND (
3249 (mmt.transaction_action_id = 3
3250 AND EXISTS (
3251 SELECT 'X'
3252 FROM cst_cost_group_assignments ccga1
3253 WHERE ccga1.cost_group_id = p_cost_group_id
3254 AND ccga1.organization_id = mmt.organization_id
3255 AND mmt.primary_quantity < 0))
3256 OR (mmt.transaction_action_id = 21
3257 AND EXISTS (
3258 SELECT 'X'
3259 FROM cst_cost_group_assignments ccga2
3260 WHERE ccga2.organization_id = mmt.organization_id
3261 AND ccga2.cost_group_id = p_cost_group_id))
3262 OR (mmt.transaction_action_id = 12
3263 AND EXISTS (
3264 SELECT 'X'
3265 FROM mtl_interorg_parameters mip
3266 WHERE mip.from_organization_id = mmt.transfer_organization_id
3267 AND mip.to_organization_id = mmt.organization_id
3268 AND (
3269 (NVL(mmt.fob_point,mip.fob_point) = 1 AND EXISTS (
3270 SELECT 'X'
3271 FROM cst_cost_group_assignments ccga2
3272 WHERE ccga2.organization_id = mip.to_organization_id
3273 AND ccga2.cost_group_id = p_cost_group_id ))
3274 OR (NVL(mmt.fob_point,mip.fob_point) = 2 AND EXISTS (
3275 SELECT 'X'
3276 FROM cst_cost_group_assignments ccga3
3277 WHERE ccga3.organization_id = mip.from_organization_id
3278 AND ccga3.cost_group_id = p_cost_group_id )))))
3279 ))
3280 UNION
3281 SELECT
3282 mmt1.transaction_id transaction_id
3283 , mmt1.transaction_action_id transaction_action_id
3284 , mmt1.transaction_source_type_id transaction_source_type_id
3285 , mmt1.inventory_item_id inventory_item_id
3286 , mmt1.primary_quantity primary_quantity
3287 , mmt1.periodic_primary_quantity periodic_primary_quantity
3288 , mmt1.organization_id organization_id
3289 , nvl(mmt1.transfer_organization_id,-1) transfer_organization_id
3290 , mmt1.subinventory_code subinventory_code
3291 , nvl(mmt1.transfer_price,0) transfer_price
3292 , mmt1.shipment_number shipment_number
3293 , mmt1.transfer_transaction_id transfer_transaction_id
3294 , mmt1.waybill_airbill waybill_airbill
3295 , nvl(mmt1.transfer_cost,0) transfer_cost
3296 , nvl(mmt1.transportation_cost,0) transportation_cost
3297 , nvl(mmt1.transfer_percentage,0) transfer_percentage
3298 , p_cost_group_id cost_group_id
3299 , NULL transfer_cost_group_id
3300 , l_txn_type txn_type
3301 , p_period_id pac_period_id
3302 FROM
3303 mtl_material_transactions mmt1
3304 , mtl_parameters mp1
3305 , mtl_parameters mptrans1
3306 WHERE
3310 AND mmt1.organization_id = mp1.organization_id
3307 mmt1.transaction_date BETWEEN p_period_start_date AND p_period_end_date
3308 AND mmt1.organization_id = nvl(mmt1.owning_organization_id, mmt1.organization_id)
3309 AND nvl(mmt1.owning_tp_type,2) = 2
3311 AND nvl(mp1.process_enabled_flag, 'N') = 'N'
3312 AND (mmt1.transaction_action_id in (3,12,21) AND mmt1.transaction_source_type_id IN (7,8)
3313 AND EXISTS (SELECT 'EXISTS'
3314 FROM cst_cost_group_assignments ccga
3315 WHERE ccga.cost_group_id = p_cost_group_id
3316 AND (ccga.organization_id = mmt1.organization_id OR
3317 ccga.organization_id = mmt1.transfer_organization_id)))
3318 AND mptrans1.organization_id = mmt1.transfer_organization_id
3319 AND mptrans1.process_enabled_flag = 'N'
3320 AND NOT EXISTS (SELECT 'X'
3321 FROM mtl_intercompany_parameters mip
3322 WHERE nvl(fnd_profile.value('INV_INTERCOMPANY_INVOICE_INTERNAL_ORDER'),0) = 1
3323 AND mip.flow_type = 1
3324 AND nvl(fnd_profile.value('CST_TRANSFER_PRICING_OPTION'),0) = 2
3325 AND mip.ship_organization_id = (select to_number(hoi.org_information3)
3326 from hr_organization_information hoi
3327 where hoi.organization_id = decode(mmt1.transaction_action_id,21,
3328 mmt1.organization_id,mmt1.transfer_organization_id)
3329 AND hoi.org_information_context = 'Accounting Information')
3330 AND mip.sell_organization_id = (select to_number(hoi2.org_information3)
3331 from hr_organization_information hoi2
3332 where hoi2.organization_id = decode(mmt1.transaction_action_id,21,
3333 mmt1.transfer_organization_id, mmt1.organization_id)
3334 AND hoi2.org_information_context = 'Accounting Information'))
3335 AND (mmt1.transaction_action_id IN (3,12,21) AND mmt1.transaction_source_type_id IN (7,8)
3336 AND NOT EXISTS (
3337 SELECT 'X'
3338 FROM cst_cost_group_assignments c1, cst_cost_group_assignments c2
3339 WHERE c1.organization_id = mmt1.organization_id
3340 AND c2.organization_id = mmt1.transfer_organization_id
3341 AND c1.cost_group_id = c2.cost_group_id)
3342 AND (
3343 (mmt1.transaction_action_id = 3
3344 AND EXISTS (
3345 SELECT 'X'
3346 FROM cst_cost_group_assignments ccga1
3347 WHERE ccga1.cost_group_id = p_cost_group_id
3348 AND ccga1.organization_id = mmt1.organization_id
3349 AND mmt1.primary_quantity < 0))
3350 OR (mmt1.transaction_action_id = 21
3351 AND EXISTS (
3352 SELECT 'X'
3353 FROM cst_cost_group_assignments ccga2
3354 WHERE ccga2.organization_id = mmt1.organization_id
3355 AND ccga2.cost_group_id = p_cost_group_id))
3356 OR (mmt1.transaction_action_id = 12
3357 AND EXISTS (
3358 SELECT 'X'
3359 FROM mtl_interorg_parameters mip
3360 WHERE mip.from_organization_id = mmt1.transfer_organization_id
3361 AND mip.to_organization_id = mmt1.organization_id
3362 AND (
3363 (NVL(mmt1.fob_point,mip.fob_point) = 1 AND EXISTS (
3364 SELECT 'X'
3365 FROM cst_cost_group_assignments ccga2
3366 WHERE ccga2.organization_id = mip.to_organization_id
3367 AND ccga2.cost_group_id = p_cost_group_id ))
3368 OR (NVL(mmt1.fob_point,mip.fob_point) = 2 AND EXISTS (
3369 SELECT 'X'
3370 FROM cst_cost_group_assignments ccga3
3371 WHERE ccga3.organization_id = mip.from_organization_id
3372 AND ccga3.cost_group_id = p_cost_group_id )))))
3373 )));
3374
3375
3376 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3377 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3378 , G_MODULE_HEAD || l_routine ||'.end'
3379 , l_routine || '>'
3380 );
3381 END IF;
3382
3383 EXCEPTION
3384 WHEN FND_API.G_EXC_ERROR THEN
3385 RAISE FND_API.G_EXC_ERROR;
3386 WHEN OTHERS THEN
3387 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3388 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION
3389 , G_MODULE_HEAD || l_routine ||'.others_exc'
3390 , 'others:' || SQLCODE || substr(SQLERRM, 1,200)
3391 );
3392 END IF;
3393 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
3394 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
3395 FND_MESSAGE.set_token('MESSAGE', '('||SQLCODE||') '||SQLERRM);
3396 FND_MSG_PUB.Add;
3397 RAISE FND_API.G_EXC_ERROR;
3398
3399 END Populate_Temp_Tables;
3400
3401 -- +========================================================================+
3402 -- PROCEDURE: Retrieve_Interorg_Items
3403 -- PARAMETERS:
3404 -- p_period_id IN NUMBER
3405 -- p_cost_group_id IN NUMBER
3406 -- p_period_start_date IN DATE
3407 -- p_period_end_date IN DATE
3408 -- COMMENT:
3409 -- This procedure is called by the API iteration_process
3410 -- Support Transfer pricing option as below
3411 -- Value 0: No include the interorg txns of OM
3412 -- Value 1: Yes, Price not as incoming cost include the interorg txns of OM
3413 -- Value 2: Yes, Price as incoming cost do not include the interorg txns of OM
3414 -- OPM SCENARIO: Exclude any interorg transactions due to OPM organization
3415 -- Cost Owned Txns:
3416 -- Exclude Direct interorg receipt txn coming from OPM org
3417 -- Exclude Intransit Shipment txn FOB:Shipment processed in discrete coming
3418 -- from OPM organization
3422 -- Exclude Intransit receipt txn FOB:Receipt processed in discrete due to OPM
3419 -- Exclude Logical intransit receipt (15) due to OPM org
3420 -- Cost Derived Txns:
3421 -- Exclude Direct interorg shipment txn to OPM org
3423 -- Exclude Logical intransit shipment (22) due to OPM org
3424 -- PRE-COND: none
3425 -- EXCEPTIONS: none
3426 -- +==========================================================================+
3427 PROCEDURE Retrieve_Interorg_Items
3428 ( p_period_id IN NUMBER
3429 , p_cost_group_id IN NUMBER
3430 , p_period_start_date IN DATE
3431 , p_period_end_date IN DATE
3432 )
3433 IS
3434
3435 -- Routine name local constant variable
3436 l_routine CONSTANT VARCHAR2(30) := 'Retrieve_Interorg_Items';
3437
3438 BEGIN
3439
3440 -- ==============================================================
3441 -- Get Interorg Items with no completion
3442 -- Set low level code to 1000
3443 -- ==============================================================
3444 INSERT INTO CST_PAC_INTORG_ITMS_TEMP
3445 ( INVENTORY_ITEM_ID
3446 , COST_GROUP_ID
3447 , PAC_PERIOD_ID
3448 , ITEM_COST
3449 , PREV_ITR_ITEM_COST
3450 , LOW_LEVEL_CODE
3451 , TOLERANCE_FLAG
3452 , ITERATION_COUNT
3453 , DIFFERENCE
3454 , DIVERGING_FLAG
3455 , INTERORG_RECEIPT_FLAG
3456 )
3457 SELECT
3458 distinct ccit.inventory_item_id
3459 , p_cost_group_id
3460 , p_period_id
3461 , 0
3462 , 0
3463 , 1000
3464 , 'N'
3465 , 0
3466 , 0
3467 , 'N'
3468 , 'Y'
3469 FROM CST_PAC_INTERORG_TXNS_TMP ccit, mtl_parameters mp
3470 WHERE ccit.cost_group_id = p_cost_group_id
3471 AND ccit.pac_period_id = p_period_id
3472 AND ccit.txn_type = 1
3473 AND mp.organization_id = ccit.transfer_organization_id
3474 AND mp.process_enabled_flag = 'N'
3475 AND NOT EXISTS (
3476 SELECT 'X'
3477 FROM
3478 cst_pac_low_level_codes cpllc
3479 WHERE cpllc.inventory_item_id = ccit.inventory_item_id
3480 AND cpllc.pac_period_id = p_period_id
3481 AND cpllc.cost_group_id = p_cost_group_id);
3482
3483
3484
3485 INSERT INTO CST_PAC_INTORG_ITMS_TEMP
3486 ( INVENTORY_ITEM_ID
3487 , COST_GROUP_ID
3488 , PAC_PERIOD_ID
3489 , ITEM_COST
3490 , PREV_ITR_ITEM_COST
3491 , LOW_LEVEL_CODE
3492 , TOLERANCE_FLAG
3493 , ITERATION_COUNT
3494 , DIFFERENCE
3495 , DIVERGING_FLAG
3496 , INTERORG_RECEIPT_FLAG
3497 , INTERORG_SHIPMENT_FLAG
3498 , SEQUENCE_NUM
3499 )
3500 SELECT
3501 distinct ccit.inventory_item_id
3502 , p_cost_group_id
3503 , p_period_id
3504 , 0
3505 , 0
3506 , 1000
3507 , 'N'
3508 , 0
3509 , 0
3510 , 'N'
3511 , 'N'
3512 , 'Y'
3513 , 1
3514 FROM CST_PAC_INTERORG_TXNS_TMP ccit
3515 WHERE ccit.cost_group_id = p_cost_group_id
3516 AND ccit.pac_period_id = p_period_id
3517 AND ccit.txn_type = 2
3518 AND NOT EXISTS (
3519 SELECT 'X'
3520 FROM cst_pac_intorg_itms_temp cpiit
3521 WHERE cpiit.cost_group_id = p_cost_group_id
3522 AND cpiit.pac_period_id = p_period_id
3523 AND cpiit.inventory_item_id = ccit.inventory_item_id)
3524 AND NOT EXISTS (
3525 SELECT 'X'
3526 FROM cst_pac_low_level_codes cpllc
3527 WHERE cpllc.inventory_item_id = ccit.inventory_item_id
3528 AND cpllc.pac_period_id = p_period_id
3529 AND cpllc.cost_group_id = p_cost_group_id);
3530
3531
3532 -- ==============================================================
3533 -- Get Interorg Items with completion
3534 -- ==============================================================
3535
3536 INSERT INTO CST_PAC_INTORG_ITMS_TEMP
3537 ( INVENTORY_ITEM_ID
3538 , COST_GROUP_ID
3539 , PAC_PERIOD_ID
3540 , ITEM_COST
3541 , PREV_ITR_ITEM_COST
3542 , LOW_LEVEL_CODE
3543 , TOLERANCE_FLAG
3544 , ITERATION_COUNT
3545 , DIFFERENCE
3546 , DIVERGING_FLAG
3547 , INTERORG_RECEIPT_FLAG
3548 )
3549 SELECT
3550 distinct ccit.inventory_item_id
3551 , p_cost_group_id
3552 , p_period_id
3553 , 0
3554 , 0
3555 , cpllc.low_level_code
3556 , 'N'
3557 , 0
3558 , 0
3559 , 'N'
3560 , 'Y'
3561 FROM CST_PAC_INTERORG_TXNS_TMP ccit, mtl_parameters mp, cst_pac_low_level_codes cpllc
3562 WHERE ccit.cost_group_id = p_cost_group_id
3563 AND ccit.pac_period_id = p_period_id
3564 AND ccit.txn_type = 1
3565 AND mp.organization_id = ccit.transfer_organization_id
3566 AND mp.process_enabled_flag = 'N'
3567 AND cpllc.inventory_item_id = ccit.inventory_item_id
3568 AND cpllc.pac_period_id = p_period_id
3569 AND cpllc.cost_group_id = p_cost_group_id;
3570
3571 INSERT INTO CST_PAC_INTORG_ITMS_TEMP
3572 ( INVENTORY_ITEM_ID
3573 , COST_GROUP_ID
3574 , PAC_PERIOD_ID
3575 , ITEM_COST
3576 , PREV_ITR_ITEM_COST
3577 , LOW_LEVEL_CODE
3578 , TOLERANCE_FLAG
3579 , ITERATION_COUNT
3580 , DIFFERENCE
3581 , DIVERGING_FLAG
3582 , INTERORG_RECEIPT_FLAG
3583 , INTERORG_SHIPMENT_FLAG
3584 , SEQUENCE_NUM
3585 )
3586 SELECT
3590 , 0
3587 distinct ccit.inventory_item_id
3588 , p_cost_group_id
3589 , p_period_id
3591 , 0
3592 , cpllc.low_level_code
3593 , 'N'
3594 , 0
3595 , 0
3596 , 'N'
3597 , 'N'
3598 , 'Y'
3599 , 1
3600 FROM CST_PAC_INTERORG_TXNS_TMP ccit, cst_pac_low_level_codes cpllc
3601 WHERE ccit.cost_group_id = p_cost_group_id
3602 AND ccit.pac_period_id = p_period_id
3603 AND ccit.txn_type = 2
3604 AND cpllc.inventory_item_id = ccit.inventory_item_id
3605 AND cpllc.pac_period_id = p_period_id
3606 AND cpllc.cost_group_id = p_cost_group_id
3607 AND NOT EXISTS (
3608 SELECT 'X'
3609 FROM cst_pac_intorg_itms_temp cpiit
3610 WHERE cpiit.cost_group_id = p_cost_group_id
3611 AND cpiit.pac_period_id = p_period_id
3612 AND cpiit.inventory_item_id = ccit.inventory_item_id);
3613
3614
3615 UPDATE CST_PAC_INTORG_ITMS_TEMP cpiit
3616 SET (INTERORG_SHIPMENT_FLAG,
3617 SEQUENCE_NUM) = (select (case when exists(select 'X'
3618 from CST_PAC_INTERORG_TXNS_TMP ccit
3619 where ccit.pac_period_id = p_period_id
3620 and ccit.cost_group_id = p_cost_group_id
3621 and ccit.inventory_item_id = cpiit.inventory_item_id
3622 and ccit.txn_type = 2)
3623 then 'Y'
3624 else 'N'
3625 end) INTERORG_SHIPMENT_FLAG,
3626 (case when exists(select 'X'
3627 from CST_PAC_INTERORG_TXNS_TMP ccit
3628 where ccit.pac_period_id = p_period_id
3629 and ccit.cost_group_id = p_cost_group_id
3630 and ccit.inventory_item_id = cpiit.inventory_item_id
3631 and ccit.txn_type = 2)
3632 then 2
3633 else 3
3634 end) SEQUENCE_NUM
3635 from dual)
3636 where cpiit.cost_group_id = p_cost_group_id
3637 AND cpiit.pac_period_id = p_period_id
3638 AND cpiit.INTERORG_RECEIPT_FLAG = 'Y';
3639
3640
3641 EXCEPTION
3642 WHEN FND_API.G_EXC_ERROR THEN
3643 RAISE FND_API.G_EXC_ERROR;
3644 WHEN OTHERS THEN
3645
3646 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
3647 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
3648 FND_MESSAGE.set_token('MESSAGE', '('||SQLCODE||') '||SQLERRM);
3649 FND_MSG_PUB.Add;
3650 RAISE FND_API.G_EXC_ERROR;
3651
3652 END Retrieve_Interorg_Items;
3653
3654 -- +========================================================================+
3655 -- PROCEDURES AND FUNCTIONS OF OPTIMAL SEQUENCE COST GROUPS
3656 -- Code Merge of Optimal Sequence Cost Groups
3657 -- +========================================================================+
3658
3659 --========================================================================
3660 -- PROCEDURE : Process_Optimal_Sequence PRIVATE
3661 -- COMMENT : Procedure to determine the optimal sequence of the
3662 -- cost groups.
3663 --========================================================================
3664 PROCEDURE Process_Optimal_Sequence
3665 ( p_period_id IN NUMBER
3666 )
3667 IS
3668 l_routine CONSTANT VARCHAR2(30) := 'Process_Optimal_Sequence';
3669
3670 BEGIN
3671
3672 -- Cost groups with across CG interorg txns are arranged in increasing order of sequence_num, On hand quantity
3673 -- for each inventory item and sequence_num is updated to reflect this order.
3674 -- The inner select query Q selects item, cost group, new sequence number.
3675
3676 update cst_pac_intorg_itms_temp cos1
3677 set cos1.sequence_num =
3678 (select sequence_num
3679 from (select cos.inventory_item_id inventory_item_id,
3680 cos.cost_group_id cost_group_id,
3681 cos.pac_period_id pac_period_id,
3682 row_number() over (partition by cos.inventory_item_id order by cos.sequence_num
3683 ,nvl(cpic.total_layer_quantity,0)
3684 ,cos.cost_group_id
3685 ) sequence_num
3686 FROM cst_pac_item_costs cpic, cst_pac_intorg_itms_temp cos
3687 WHERE cpic.inventory_item_id(+) = cos.inventory_item_id
3688 AND cpic.cost_group_id(+) = cos.cost_group_id
3689 AND cpic.pac_period_id(+) = cos.pac_period_id
3690 AND cos.pac_period_id = p_period_id) Q
3691 WHERE cos1.inventory_item_id = Q.inventory_item_id
3692 and cos1.cost_group_id = Q.cost_group_id
3693 and cos1.pac_period_id = Q.pac_period_id)
3694 WHERE cos1.pac_period_id = p_period_id;
3695
3696 EXCEPTION
3697 WHEN FND_API.G_EXC_ERROR THEN
3698 RAISE FND_API.G_EXC_ERROR;
3699 WHEN OTHERS THEN
3700 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
3701 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
3702 FND_MESSAGE.set_token('MESSAGE', '('||SQLCODE||') '||SQLERRM);
3703 FND_MSG_PUB.Add;
3704 RAISE FND_API.G_EXC_ERROR;
3705 END Process_Optimal_Sequence;
3706
3707 -- +========================================================================+
3708 -- PROCEDURE: Get_Balance_Before_Intorg
3709 -- PARAMETERS:
3710 -- p_period_id IN NUMBER
3711 -- p_cost_group_id IN NUMBER
3712 -- p_inventory_item_id IN NUMBER
3713 -- COMMENT:
3714 -- Retrieve Balance before interorg txns across Cost Groups from CPPB.
3715 -- Store the period quantity, period balance into G_PWAC_NEW_COST_TBL
3716 -- PRE-COND: none
3717 -- EXCEPTIONS: none
3718 -- +==========================================================================+
3722 )
3719 PROCEDURE Get_Balance_Before_Intorg(p_period_id IN NUMBER
3720 ,p_cost_group_id IN NUMBER
3721 ,p_inventory_item_id IN NUMBER
3723 IS
3724
3725 -- routine name local constant variable
3726 l_routine CONSTANT VARCHAR2(30) := 'get_balance_before_intorg';
3727
3728 -- Cursor to retrieve CPPB balance info just before
3729 -- interorg txns across CGs
3730 CURSOR balance_bef_intorg_cur(c_pac_period_id NUMBER
3731 ,c_cost_group_id NUMBER
3732 ,c_inventory_item_id NUMBER
3733 ,c_cost_element_id NUMBER
3734 ,c_level_type NUMBER
3735 )
3736 IS
3737 SELECT
3738 nvl(cppb.period_balance,0)
3739 FROM cst_pac_period_balances cppb
3740 WHERE cppb.pac_period_id = c_pac_period_id
3741 AND cppb.cost_group_id = c_cost_group_id
3742 AND cppb.inventory_item_id = c_inventory_item_id
3743 AND cppb.cost_element_id = c_cost_element_id
3744 AND cppb.level_type = c_level_type
3745 AND cppb.txn_category = (Select MAX(cppb1.txn_category)
3746 FROM cst_pac_period_balances cppb1
3747 WHERE cppb.pac_period_id = cppb1.pac_period_id
3748 AND cppb.cost_group_id = cppb1.cost_group_id
3749 AND cppb.inventory_item_id = cppb1.inventory_item_id
3750 AND cppb.cost_element_id = cppb1.cost_element_id
3751 AND cppb.level_type = cppb1.level_type
3752 AND txn_category < 8);
3753
3754
3755 -- FP bug 8850186 fix
3756 -- Cursor to retrieve CPPB period quantity just before
3757 -- interorg txns across CGs for available CE, LT
3758 -- NOTE: txn_category 8 is for interorg txns
3759 CURSOR period_qty_bef_intorg_cur(c_pac_period_id NUMBER
3760 ,c_cost_group_id NUMBER
3761 ,c_inventory_item_id NUMBER
3762 )
3763 IS
3764 SELECT
3765 nvl(cppb.period_quantity,0)
3766 FROM cst_pac_period_balances cppb
3767 WHERE cppb.pac_period_id = c_pac_period_id
3768 AND cppb.cost_group_id = c_cost_group_id
3769 AND cppb.inventory_item_id = c_inventory_item_id
3770 AND cppb.txn_category = (Select MAX(cppb1.txn_category)
3771 FROM cst_pac_period_balances cppb1
3772 WHERE cppb.pac_period_id = cppb1.pac_period_id
3773 AND cppb.cost_group_id = cppb1.cost_group_id
3774 AND cppb.inventory_item_id = cppb1.inventory_item_id
3775 AND txn_category < 8)
3776 AND rownum = 1;
3777
3778
3779 l_cost_element_id NUMBER;
3780 l_level_type NUMBER;
3781 l_period_qty_bef_intorg NUMBER;
3782 l_period_bal_bef_intorg NUMBER;
3783
3784 l_cg_idx BINARY_INTEGER;
3785
3786 BEGIN
3787
3788 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3789 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3790 , G_MODULE_HEAD || l_routine ||'.begin'
3791 , l_routine || '<'
3792 );
3793 END IF;
3794
3795 -- initialize G_PWAC_NEW_COST_TBL table for all the combination
3796 -- of cost element and level type in each cost group
3797 -- with period balance just before interorg txns
3798 l_cost_element_id := 1;
3799 WHILE l_cost_element_id <= 5 LOOP
3800 l_level_type := 1;
3801 WHILE l_level_type <= 2 LOOP
3802
3803 -- initialize period qty and period balance
3804 -- before interorg txns
3805 l_period_bal_bef_intorg := 0;
3806
3807 OPEN balance_bef_intorg_cur(p_period_id
3808 ,p_cost_group_id
3809 ,p_inventory_item_id
3810 ,l_cost_element_id
3811 ,l_level_type
3812 );
3813 FETCH balance_bef_intorg_cur
3814 INTO l_period_bal_bef_intorg;
3815
3816 IF balance_bef_intorg_cur%FOUND THEN
3817 G_PWAC_NEW_COST_TBL(l_cost_element_id)(l_level_type).period_bal_bef_intorg := l_period_bal_bef_intorg;
3818 G_PWAC_NEW_COST_TBL(l_cost_element_id)(l_level_type).period_new_balance := l_period_bal_bef_intorg;
3819 ELSE
3820 G_PWAC_NEW_COST_TBL(l_cost_element_id)(l_level_type).period_bal_bef_intorg := 0;
3821 G_PWAC_NEW_COST_TBL(l_cost_element_id)(l_level_type).period_new_balance := 0;
3822
3823 END IF;
3824
3825 CLOSE balance_bef_intorg_cur;
3826
3827 l_level_type := l_level_type + 1;
3828 END LOOP;
3829 l_cost_element_id := l_cost_element_id + 1;
3830 END LOOP;
3831
3832
3833 -- FP Bug 8850186 fix: period quantity just before interorg txns
3834 -- Get period quantity just before interorg txns
3835 -- NOTE:Period Quantity is same for all cost elements, level type
3836 l_period_qty_bef_intorg := 0;
3837
3838 OPEN period_qty_bef_intorg_cur(p_period_id
3839 ,p_cost_group_id
3840 ,p_inventory_item_id
3841 );
3842 FETCH period_qty_bef_intorg_cur
3843 INTO l_period_qty_bef_intorg;
3844
3845 IF period_qty_bef_intorg_cur%NOTFOUND THEN
3846 l_period_qty_bef_intorg := 0;
3847 END IF;
3848
3849 CLOSE period_qty_bef_intorg_cur;
3850
3851 -- FP Bug 8850186 fix
3852 -- initialize G_PWAC_NEW_COST_TBL table for all the combination
3853 -- of cost element and level type in each cost group
3857 WHILE l_cost_element_id <= 5 LOOP
3854 -- with period quantity just before interorg txns
3855 -- Period Qty is same for all cost elements, level type
3856 l_cost_element_id := 1;
3858 l_level_type := 1;
3859 WHILE l_level_type <= 2 LOOP
3860 G_PWAC_NEW_COST_TBL(l_cost_element_id)(l_level_type).period_qty_bef_intorg := l_period_qty_bef_intorg;
3861 G_PWAC_NEW_COST_TBL(l_cost_element_id)(l_level_type).period_new_quantity := l_period_qty_bef_intorg;
3862
3863 l_level_type := l_level_type + 1;
3864 END LOOP;
3865 l_cost_element_id := l_cost_element_id + 1;
3866 END LOOP;
3867
3868 -- set period quantity at each cost group
3869 l_cg_idx := p_cost_group_id;
3870 G_CST_GROUP_TBL(l_cg_idx).period_new_quantity := l_period_qty_bef_intorg;
3871
3872
3873 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3874 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
3875 , G_MODULE_HEAD || l_routine ||'.cgpd1'
3876 , 'G_CST_GROUP_TBL Cost Group Id:' || p_cost_group_id || ' Inventory Item Id:' || p_inventory_item_id ||
3877 ' Period Quantity Before Inter org:' || G_CST_GROUP_TBL(l_cg_idx).period_new_quantity
3878 );
3879
3880 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
3881 , G_MODULE_HEAD || l_routine ||'.cgpd2'
3882 , 'G_PWAC_NEW_COST_TBL Cost Group Id:' || p_cost_group_id || ' Inventory Item Id:' || p_inventory_item_id ||
3883 ' Period Qty Before Inter org:' || G_PWAC_NEW_COST_TBL(1)(1).period_qty_bef_intorg
3884 );
3885
3886 END IF;
3887
3888
3889 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3890 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3891 , G_MODULE_HEAD || l_routine ||'.end'
3892 , l_routine || '>'
3893 );
3894 END IF;
3895
3896
3897 EXCEPTION
3898 WHEN FND_API.G_EXC_ERROR THEN
3899 RAISE FND_API.G_EXC_ERROR;
3900 WHEN OTHERS THEN
3901 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3902 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION
3903 , G_MODULE_HEAD || l_routine ||'.others_exc'
3904 , 'others:' || SQLCODE || substr(SQLERRM, 1,200)
3905 );
3906 END IF;
3907 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
3908 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
3909 FND_MESSAGE.set_token('MESSAGE', '('||SQLCODE||') '||SQLERRM);
3910 FND_MSG_PUB.Add;
3911 RAISE FND_API.G_EXC_ERROR;
3912 END Get_Balance_Before_Intorg;
3913
3914 -- +========================================================================+
3915 -- PROCEDURE: PRIVATE UTILITY
3916 -- PARAMETERS: Group1_Interorg_Iteration1
3917 -- Descrition:
3918 -- +========================================================================+
3919 PROCEDURE Group1_Interorg_Iteration1
3920 ( p_legal_entity_id IN NUMBER
3921 , p_master_org_id IN NUMBER
3922 , p_cost_type_id IN NUMBER
3923 , p_cost_method IN NUMBER
3924 , p_cost_group_id IN NUMBER
3925 , p_inventory_item_id IN NUMBER
3926 , p_low_level_code IN NUMBER
3927 , p_period_id IN NUMBER
3928 , p_pac_rates_id IN NUMBER
3929 , p_uom_control IN NUMBER
3930 , p_end_iteration_num IN NUMBER
3931 , p_iteration_proc_flag IN VARCHAR2
3932 )
3933 IS
3934 -- routine name local constant variable
3935 l_routine CONSTANT VARCHAR2(30) := 'Group1_Interorg_Iteration1';
3936
3937 CURSOR group1_interorg_cur(c_cost_group_id NUMBER
3938 ,c_inventory_item_id NUMBER
3939 ,c_period_id NUMBER
3940 )
3941 IS
3942 SELECT
3943 ccit.transaction_id transaction_id
3944 , ccit.transaction_action_id transaction_action_id
3945 , ccit.transaction_source_type_id transaction_source_type_id
3946 , ccit.inventory_item_id inventory_item_id
3947 , ccit.primary_quantity primary_quantity
3948 , ccit.periodic_primary_quantity periodic_primary_quantity
3949 , ccit.organization_id organization_id
3950 , nvl(ccit.transfer_organization_id,-1) transfer_organization_id
3951 , ccit.subinventory_code subinventory_code
3952 , nvl(ccit.transfer_price,0) transfer_price
3953 , ccit.shipment_number shipment_number
3954 , ccit.transfer_transaction_id transfer_transaction_id
3955 , ccit.waybill_airbill waybill_airbill
3956 , nvl(ccit.transfer_cost,0) transfer_cost
3957 , nvl(ccit.transportation_cost,0) transportation_cost
3958 , nvl(ccit.transfer_percentage,0) transfer_percentage
3959 , DECODE(msi.inventory_asset_flag,'Y',0,1) exp_item
3960 , DECODE(msubinv.asset_inventory,1,0,1) exp_flag
3961 FROM
3962 CST_PAC_INTERORG_TXNS_TMP ccit
3963 , mtl_system_items msi
3964 , mtl_secondary_inventories msubinv
3965 WHERE ccit.inventory_item_id = c_inventory_item_id
3966 AND ccit.cost_group_id = c_cost_group_id
3967 AND ccit.pac_period_id = c_period_id
3968 AND ccit.inventory_item_id = msi.inventory_item_id
3969 AND msi.organization_id = ccit.organization_id
3970 AND msubinv.organization_id(+) = ccit.organization_id
3971 AND msubinv.secondary_inventory_name(+) = ccit.subinventory_code
3972 AND ccit.txn_type = 1;
3973
3974 TYPE group1_interorg_tab IS TABLE OF group1_interorg_cur%rowtype INDEX BY BINARY_INTEGER;
3975 l_group1_interorg_tab group1_interorg_tab;
3976 l_empty_gp1_interorg_tab group1_interorg_tab;
3977
3978 l_loop_count NUMBER := 0;
3979 l_batch_size NUMBER := 200;
3980 -- Error message variables
3981 l_error_num NUMBER;
3982 l_error_code VARCHAR2(240);
3983 l_error_msg VARCHAR2(240);
3984 l_return_status VARCHAR2(1);
3985 l_txn_category NUMBER;
3986
3990 INDEX BY BINARY_INTEGER;
3987 TYPE t_txn_id_tbl_type IS TABLE OF MTL_PAC_ACTUAL_COST_DETAILS.transaction_id%TYPE
3988 INDEX BY BINARY_INTEGER;
3989 TYPE t_cost_element_id_tbl_type IS TABLE OF MTL_PAC_ACTUAL_COST_DETAILS.cost_element_id%TYPE
3991 TYPE t_level_type_tbl_type IS TABLE OF MTL_PAC_ACTUAL_COST_DETAILS.level_type%TYPE
3992 INDEX BY BINARY_INTEGER;
3993 TYPE t_variance_amt_tbl_type IS TABLE OF MTL_PAC_ACTUAL_COST_DETAILS.variance_amount%TYPE
3994 INDEX BY BINARY_INTEGER;
3995
3996 l_txn_id_tbl t_txn_id_tbl_type;
3997 l_cost_element_id_tbl t_cost_element_id_tbl_type;
3998 l_level_type_tbl t_level_type_tbl_type;
3999 l_variance_amt_tbl t_variance_amt_tbl_type;
4000
4001 BEGIN
4002
4003 l_txn_category := 8;
4004
4005 IF NOT group1_interorg_cur%ISOPEN THEN
4006 OPEN group1_interorg_cur(p_cost_group_id
4007 ,p_inventory_item_id
4008 ,p_period_id
4009 );
4010 END IF;
4011
4012 LOOP
4013
4014 l_group1_interorg_tab := l_empty_gp1_interorg_tab;
4015 FETCH group1_interorg_cur BULK COLLECT INTO l_group1_interorg_tab LIMIT l_batch_size;
4016
4017 l_loop_count := l_group1_interorg_tab.count;
4018
4019 FOR i IN 1..l_loop_count
4020 LOOP
4021
4022
4023 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4024 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
4025 ,G_MODULE_HEAD || l_routine || '.costinvtxn_gp1'
4026 ,'Transaction Id:'|| l_group1_interorg_tab(i).transaction_id ||
4027 ' Primary Qty:' || l_group1_interorg_tab(i).primary_quantity
4028 );
4029 END IF;
4030
4031 IF (l_group1_interorg_tab(i).subinventory_code IS NULL) THEN
4032 l_group1_interorg_tab(i).exp_flag := l_group1_interorg_tab(i).exp_item;
4033 ELSIF (l_group1_interorg_tab(i).exp_item = 1) THEN
4034 l_group1_interorg_tab(i).exp_flag := 1;
4035 END IF;
4036
4037
4038 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4039 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
4040 ,G_MODULE_HEAD || l_routine || '.exp_flag'
4041 ,'Expense Flag:'|| l_group1_interorg_tab(i).exp_flag ||
4042 ' Expense Item:' || l_group1_interorg_tab(i).exp_item
4043 );
4044 END IF;
4045
4046 -- insert into cppb for 1000 inventory items
4047 l_error_num := 0;
4048 IF (CSTPPINV.l_item_id_tbl.COUNT >= 1000) THEN
4049 CSTPPWAC.insert_into_cppb(i_pac_period_id => p_period_id
4050 ,i_cost_group_id => p_cost_group_id
4051 ,i_txn_category => l_txn_category
4052 ,i_user_id => FND_GLOBAL.user_id
4053 ,i_login_id => FND_GLOBAL.login_id
4054 ,i_request_id => FND_GLOBAL.conc_request_id
4055 ,i_prog_id => FND_GLOBAL.conc_program_id
4056 ,i_prog_appl_id => FND_GLOBAL.prog_appl_id
4057 ,o_err_num => l_error_num
4058 ,o_err_code => l_error_code
4059 ,o_err_msg => l_error_msg
4060 );
4061 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4062 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
4063 ,G_MODULE_HEAD || l_routine || '.incppbir1'
4064 ,'After calling insert_into_cppb:'|| l_error_num || l_error_code || l_error_msg
4065 );
4066 END IF;
4067 END IF; -- plsql item table count
4068
4069 IF l_error_num = 0 THEN
4070
4071 -- Invoke PAC inventory cost processor
4072 CSTPPINV.cost_inv_txn( i_pac_period_id => p_period_id
4073 , i_legal_entity => p_legal_entity_id
4074 , i_cost_type_id => p_cost_type_id
4075 , i_cost_group_id => p_cost_group_id
4076 , i_cost_method => p_cost_method
4077 , i_txn_id => l_group1_interorg_tab(i).transaction_id
4078 , i_txn_action_id => l_group1_interorg_tab(i).transaction_action_id
4079 , i_txn_src_type_id => l_group1_interorg_tab(i).transaction_source_type_id
4080 , i_item_id => l_group1_interorg_tab(i).inventory_item_id
4081 , i_txn_qty => l_group1_interorg_tab(i).primary_quantity
4082 , i_txn_org_id => l_group1_interorg_tab(i).organization_id
4083 , i_txfr_org_id => l_group1_interorg_tab(i).transfer_organization_id
4084 , i_subinventory_code => l_group1_interorg_tab(i).subinventory_code
4085 , i_exp_flag => l_group1_interorg_tab(i).exp_flag
4086 , i_exp_item => l_group1_interorg_tab(i).exp_item
4087 , i_pac_rates_id => p_pac_rates_id
4088 , i_process_group => 1
4089 , i_master_org_id => p_master_org_id
4090 , i_uom_control => p_uom_control
4091 , i_user_id => FND_GLOBAL.user_id
4092 , i_login_id => FND_GLOBAL.login_id
4093 , i_request_id => FND_GLOBAL.conc_request_id
4094 , i_prog_id => FND_GLOBAL.conc_program_id
4095 , i_prog_appl_id => FND_GLOBAL.prog_appl_id
4096 , i_txn_category => l_txn_category
4100 , o_err_msg => l_error_msg
4097 , i_transfer_price_pd => l_group1_interorg_tab(i).transfer_price
4098 , o_err_num => l_error_num
4099 , o_err_code => l_error_code
4101 );
4102
4103 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4104 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
4105 ,G_MODULE_HEAD || l_routine || '.PAC_inv_Processor_gp1'
4106 ,'PAC Inventory Processor:'|| l_error_num || ' ' ||
4107 l_error_code || ' ' || l_error_msg
4108 );
4109 END IF;
4110
4111
4112 END IF; -- error num check
4113
4114 IF l_error_num <> 0 THEN
4115 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4116 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION
4117 , G_MODULE_HEAD || l_routine || '.others'
4118 , 'group1 cost_inv_txn for cost group '|| p_cost_group_id ||' txn id '
4119 || l_group1_interorg_tab(i).transaction_id||' ('||l_error_code||') '||l_error_msg
4120 );
4121 END IF;
4122 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
4123 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
4124 FND_MESSAGE.set_token('MESSAGE', 'group1 cost_inv_txn for cost group '|| p_cost_group_id ||' txn id '
4125 || l_group1_interorg_tab(i).transaction_id||' ('||l_error_code||') '||l_error_msg);
4126 FND_MSG_PUB.Add;
4127 RAISE FND_API.G_EXC_ERROR;
4128 END IF;
4129
4130 -- Insert into MTL_PAC_ACT_CST_DTL_TEMP only if the iteration
4131 -- process flag is enabled with more than 1 iteration
4132
4133 IF p_iteration_proc_flag = 'Y' AND p_end_iteration_num > 1 THEN
4134
4135 -- Cost owned transactions
4136 INSERT INTO MTL_PAC_ACT_CST_DTL_TEMP
4137 ( COST_GROUP_ID
4138 , TRANSACTION_ID
4139 , PAC_PERIOD_ID
4140 , COST_TYPE_ID
4141 , COST_ELEMENT_ID
4142 , LEVEL_TYPE
4143 , INVENTORY_ITEM_ID
4144 , COST_LAYER_ID
4145 , ACTUAL_COST
4146 , VARIANCE_AMOUNT
4147 , USER_ENTERED
4148 , INSERTION_FLAG
4149 , TRANSACTION_COSTED_DATE
4150 , SHIPMENT_NUMBER
4151 , TRANSFER_TRANSACTION_ID
4152 , TRANSPORTATION_COST
4153 , MOH_ABSORPTION_COST
4154 )
4155 SELECT
4156 cost_group_id
4157 , transaction_id
4158 , pac_period_id
4159 , cost_type_id
4160 , cost_element_id
4161 , level_type
4162 , inventory_item_id
4163 , cost_layer_id
4164 , actual_cost
4165 , variance_amount
4166 , user_entered
4167 , insertion_flag
4168 , transaction_costed_date
4169 , l_group1_interorg_tab(i).shipment_number
4170 , l_group1_interorg_tab(i).transfer_transaction_id
4171 , decode(cost_element_id,2,
4172 decode(level_type,1,l_group1_interorg_tab(i).transportation_cost,0),0)
4173 , 0
4174 FROM MTL_PAC_ACTUAL_COST_DETAILS
4175 WHERE transaction_id = l_group1_interorg_tab(i).transaction_id
4176 AND cost_group_id = p_cost_group_id
4177 AND pac_period_id = p_period_id
4178 AND cost_type_id = p_cost_type_id;
4179
4180 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4181 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
4182 ,G_MODULE_HEAD || l_routine || '.After_ins_MPACD_TEMP'
4183 ,'After inserting mtl_pac_act_cst_dtl_temp:'||
4184 l_group1_interorg_tab(i).transaction_id
4185 );
4186 END IF;
4187
4188 -- Bug 5593086 Pad with nonexistent rows
4189 INSERT INTO MTL_PAC_ACT_CST_DTL_TEMP
4190 ( COST_GROUP_ID
4191 , TRANSACTION_ID
4192 , PAC_PERIOD_ID
4193 , COST_TYPE_ID
4194 , COST_ELEMENT_ID
4195 , LEVEL_TYPE
4196 , INVENTORY_ITEM_ID
4197 , COST_LAYER_ID
4198 , ACTUAL_COST
4199 , VARIANCE_AMOUNT
4200 , USER_ENTERED
4201 , INSERTION_FLAG
4202 , TRANSACTION_COSTED_DATE
4203 , SHIPMENT_NUMBER
4204 , TRANSFER_TRANSACTION_ID
4205 , TRANSPORTATION_COST
4206 , MOH_ABSORPTION_COST
4207 )
4208 SELECT
4209 mpacd.cost_group_id
4210 , mpacd.transaction_id
4211 , mpacd.pac_period_id
4212 , mpacd.cost_type_id
4213 , cce.cost_element_id
4214 , lt.level_type
4215 , mpacd.inventory_item_id
4216 , mpacd.cost_layer_id
4217 , 0
4218 , 0
4219 , mpacd.user_entered
4220 , mpacd.insertion_flag
4221 , mpacd.transaction_costed_date
4222 , l_group1_interorg_tab(i).shipment_number
4223 , l_group1_interorg_tab(i).transfer_transaction_id
4224 , 0
4225 , 0
4226 FROM (SELECT *
4227 FROM MTL_PAC_ACTUAL_COST_DETAILS
4228 WHERE transaction_id = l_group1_interorg_tab(i).transaction_id
4229 AND cost_group_id = p_cost_group_id
4230 AND pac_period_id = p_period_id
4231 AND cost_type_id = p_cost_type_id
4235 UNION
4232 AND rownum = 1) mpacd,
4233 CST_COST_ELEMENTS cce,
4234 (SELECT 1 level_type FROM DUAL
4236 SELECT 2 level_type FROM DUAL) lt
4237 WHERE NOT EXISTS
4238 (SELECT 1
4239 FROM mtl_pac_act_cst_dtl_temp mpacdt
4240 WHERE mpacdt.cost_group_id = p_cost_group_id
4241 AND mpacdt.transaction_id = l_group1_interorg_tab(i).transaction_id
4242 AND mpacdt.pac_period_id = p_period_id
4243 AND mpacdt.cost_type_id = p_cost_type_id
4244 AND mpacdt.cost_element_id = cce.cost_element_id
4245 AND mpacdt.level_type = lt.level_type);
4246
4247 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4248 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
4249 ,G_MODULE_HEAD || l_routine || '.After_pad_MPACD_TEMP'
4250 ,'After padding mtl_pac_act_cst_dtl_temp:'||
4251 l_group1_interorg_tab(i).transaction_id
4252 );
4253 END IF;
4254
4255 END IF; --IF p_iteration_proc_flag = 'Y' AND p_end_iteration_num > 1 THEN
4256
4257 END LOOP; -- FOR i IN 1..l_loop_count
4258
4259 EXIT WHEN group1_interorg_cur%NOTFOUND;
4260 END LOOP; -- FETCH loop
4261 CLOSE group1_interorg_cur;
4262
4263
4264 -- ======================================================
4265 -- insert left over interorg receipts into cppb
4266 -- Calculate Periodic Cost if interorg receipts exist
4267 -- Update Variance Amount into MPACD_TEMP if interorg
4268 -- receipts exist and consecutive iterations exist
4269 -- update cppb if interorg receipts exist
4270 -- ======================================================
4271 l_error_num := 0;
4272
4273
4274 IF (CSTPPINV.l_item_id_tbl.COUNT > 0) THEN
4275 CSTPPWAC.insert_into_cppb(i_pac_period_id => p_period_id
4276 ,i_cost_group_id => p_cost_group_id
4277 ,i_txn_category => l_txn_category
4278 ,i_user_id => FND_GLOBAL.user_id
4279 ,i_login_id => FND_GLOBAL.login_id
4280 ,i_request_id => FND_GLOBAL.conc_request_id
4281 ,i_prog_id => FND_GLOBAL.conc_program_id
4282 ,i_prog_appl_id => FND_GLOBAL.prog_appl_id
4283 ,o_err_num => l_error_num
4284 ,o_err_code => l_error_code
4285 ,o_err_msg => l_error_msg
4286 );
4287
4288 l_error_num := NVL(l_error_num, 0);
4289 l_error_code := NVL(l_error_code, 'No Error');
4290 l_error_msg := NVL(l_error_msg, 'No Error');
4291
4292 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4293 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
4294 ,G_MODULE_HEAD || l_routine || '.inscppir2'
4295 ,'After calling insert_into_cppb:'|| l_error_num || l_error_code || l_error_msg
4296 );
4297 END IF;
4298 END IF; -- item existence in item id table
4299
4300 -- ==============================================================
4301 -- Calculate Periodic Cost after processing all interorg receipts
4302 -- Variance Amount will be updated
4303 -- Item Cost will be updated
4304 -- Calculate Periodic Cost in CPICD, CPIC at the FIRST iteration
4305 -- process; Update Variance Amount in the last transaction of MPACD;
4306 -- Update CPPB - Invoke Update_Item_cppb for a given CG
4307 -- Copy of Calc_Pmac_Update_Cppb for a given cost group
4308 -- ================================================================
4309 CSTPPWAC.calculate_periodic_cost(i_pac_period_id => p_period_id
4310 ,i_cost_group_id => p_cost_group_id
4311 ,i_cost_type_id => p_cost_type_id
4312 ,i_low_level_code => p_low_level_code
4313 ,i_item_id => p_inventory_item_id
4314 ,i_user_id => FND_GLOBAL.user_id
4315 ,i_login_id => FND_GLOBAL.login_id
4316 ,i_request_id => FND_GLOBAL.conc_request_id
4317 ,i_prog_id => FND_GLOBAL.conc_program_id
4318 ,i_prog_appl_id => FND_GLOBAL.prog_appl_id
4319 ,o_err_num => l_error_num
4320 ,o_err_code => l_error_code
4321 ,o_err_msg => l_error_msg
4322 );
4323
4324 l_error_num := NVL(l_error_num,0);
4325 l_error_code := NVL(l_error_code, 'No Error');
4326 l_error_msg := NVL(l_error_msg, 'No Error');
4327
4328
4329 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4330 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
4331 ,G_MODULE_HEAD || l_routine || '.calcpmac'
4332 ,'After calling calculate_periodic_cost:'|| l_error_num || l_error_code || l_error_msg
4333 );
4334 END IF;
4335
4336
4337 IF p_end_iteration_num > 1 THEN
4338 -- =====================================
4339 -- Update variance amount in MPACD_TEMP
4340 -- =====================================
4341 SELECT transaction_id, cost_element_id, level_type,variance_amount
4342 BULK COLLECT
4343 INTO l_txn_id_tbl, l_cost_element_id_tbl, l_level_type_tbl,l_variance_amt_tbl
4344 FROM MTL_PAC_ACTUAL_COST_DETAILS
4345 WHERE pac_period_id = p_period_id
4346 AND cost_type_id = p_cost_type_id
4347 AND cost_group_id = p_cost_group_id
4348 AND inventory_item_id = p_inventory_item_id;
4349
4350 FORALL l_mpacd_idx IN l_txn_id_tbl.FIRST .. l_txn_id_tbl.LAST
4351 UPDATE MTL_PAC_ACT_CST_DTL_TEMP
4352 SET variance_amount = l_variance_amt_tbl(l_mpacd_idx)
4353 WHERE pac_period_id = p_period_id
4354 AND cost_type_id = p_cost_type_id
4355 AND cost_group_id = p_cost_group_id
4356 AND inventory_item_id = p_inventory_item_id
4357 AND cost_element_id = l_cost_element_id_tbl(l_mpacd_idx)
4358 AND level_type = l_level_type_tbl(l_mpacd_idx)
4359 AND transaction_id = l_txn_id_tbl(l_mpacd_idx);
4360 END IF;
4361
4362 IF l_error_num <> 0 THEN
4363 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
4364 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
4365 FND_MESSAGE.set_token('MESSAGE', 'CSTPPWAC.update_item_cppb for CG '|| p_cost_group_id ||' txn category '
4366 ||l_txn_category||' t_low_level_code ' ||p_low_level_code
4367 ||' error('||l_error_code||') '||l_error_msg);
4368 FND_MSG_PUB.Add;
4369 RAISE FND_API.G_EXC_ERROR;
4370 END IF;
4371
4372 CSTPPWAC.update_item_cppb (i_pac_period_id => p_period_id,
4373 i_cost_group_id => p_cost_group_id,
4374 i_txn_category => l_txn_category,
4375 i_item_id => p_inventory_item_id,
4376 i_user_id => FND_GLOBAL.user_id,
4377 i_login_id => FND_GLOBAL.login_id,
4378 i_request_id => FND_GLOBAL.conc_request_id,
4379 i_prog_id => FND_GLOBAL.conc_program_id,
4380 i_prog_appl_id => FND_GLOBAL.prog_appl_id,
4381 o_err_num => l_error_num,
4382 o_err_code => l_error_code,
4383 o_err_msg => l_error_msg );
4384
4385 -- Set PWAC Item Cost in interorg items temp table
4386 UPDATE CST_PAC_INTORG_ITMS_TEMP
4387 SET item_cost = (SELECT nvl(item_cost,0)
4388 FROM cst_pac_item_costs cpic
4389 WHERE cpic.pac_period_id = p_period_id
4390 AND cpic.cost_group_id = p_cost_group_id
4391 AND cpic.inventory_item_id = p_inventory_item_id)
4392 WHERE pac_period_id = p_period_id
4393 AND cost_group_id = p_cost_group_id
4394 AND inventory_item_id = p_inventory_item_id;
4395
4396 EXCEPTION
4397 WHEN FND_API.G_EXC_ERROR THEN
4398 RAISE FND_API.G_EXC_ERROR;
4399 WHEN OTHERS THEN
4400 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
4401 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
4402 FND_MESSAGE.set_token('MESSAGE', '('||SQLCODE||') '||SQLERRM);
4403 FND_MSG_PUB.Add;
4404 RAISE FND_API.G_EXC_ERROR;
4405 END Group1_Interorg_Iteration1;
4406
4407 -- +========================================================================+
4408 -- PROCEDURE: PRIVATE UTILITY
4409 -- PARAMETERS: Group2_Interorg_Iteration1
4410 -- Descrition:
4411 -- +========================================================================+
4412 PROCEDURE Group2_Interorg_Iteration1
4413 ( p_legal_entity_id IN NUMBER
4414 , p_master_org_id IN NUMBER
4415 , p_cost_type_id IN NUMBER
4416 , p_cost_method IN NUMBER
4417 , p_cost_group_id IN NUMBER
4418 , p_inventory_item_id IN NUMBER
4419 , p_low_level_code IN NUMBER
4420 , p_period_id IN NUMBER
4421 , p_pac_rates_id IN NUMBER
4422 , p_uom_control IN NUMBER
4423 , p_end_iteration_num IN NUMBER
4424 , p_iteration_proc_flag IN VARCHAR2
4425 )
4426 IS
4427 -- routine name local constant variable
4428 l_routine CONSTANT VARCHAR2(30) := 'Group2_Interorg_Iteration1';
4429
4430 CURSOR group2_interorg_cur(c_cost_group_id NUMBER
4431 ,c_inventory_item_id NUMBER
4432 ,c_period_id NUMBER
4433 )
4434 IS
4435 SELECT
4436 ccit.transaction_id transaction_id
4437 , ccit.transaction_action_id transaction_action_id
4438 , ccit.transaction_source_type_id transaction_source_type_id
4439 , ccit.inventory_item_id inventory_item_id
4440 , ccit.primary_quantity primary_quantity
4441 , ccit.periodic_primary_quantity periodic_primary_quantity
4442 , ccit.organization_id organization_id
4443 , nvl(ccit.transfer_organization_id,-1) transfer_organization_id
4444 , ccit.subinventory_code subinventory_code
4445 , nvl(ccit.transfer_price,0) transfer_price
4446 , ccit.shipment_number shipment_number
4447 , ccit.transfer_transaction_id transfer_transaction_id
4448 , nvl(ccit.transfer_cost,0) transfer_cost
4449 , nvl(ccit.transportation_cost,0) transportation_cost
4450 , nvl(ccit.transfer_percentage,0) transfer_percentage
4451 , DECODE(msi.inventory_asset_flag,'Y',0,1) exp_item
4452 , DECODE(msubinv.asset_inventory,1,0,1) exp_flag
4453 FROM
4454 CST_PAC_INTERORG_TXNS_TMP ccit
4455 , mtl_system_items msi
4456 , mtl_secondary_inventories msubinv
4457 WHERE ccit.inventory_item_id = c_inventory_item_id
4458 AND ccit.cost_group_id = c_cost_group_id
4459 AND ccit.pac_period_id = c_period_id
4460 AND ccit.inventory_item_id = msi.inventory_item_id
4461 AND msi.organization_id = ccit.organization_id
4462 AND msubinv.organization_id(+) = ccit.organization_id
4463 AND msubinv.secondary_inventory_name(+) = ccit.subinventory_code
4464 AND ccit.txn_type = 2;
4465
4466 TYPE group2_interorg_tab IS TABLE OF group2_interorg_cur%rowtype INDEX BY BINARY_INTEGER;
4467 l_group2_interorg_tab group2_interorg_tab;
4468 l_empty_gp2_interorg_tab group2_interorg_tab;
4469
4470 l_loop_count NUMBER := 0;
4471 l_batch_size NUMBER := 200;
4472 -- Error message variables
4473 l_error_num NUMBER;
4474 l_error_code VARCHAR2(240);
4475 l_error_msg VARCHAR2(240);
4476 l_return_status VARCHAR2(1);
4477 l_txn_category NUMBER;
4478
4479 BEGIN
4480
4481 -- initialize transaction category for interorg shipments across CGs as cost derived txns
4482 l_txn_category := 9;
4483
4484 -- ==================================================================
4485 -- Process Group 2 cost derived transactions
4486 -- ==================================================================
4487 IF NOT group2_interorg_cur%ISOPEN THEN
4488 OPEN group2_interorg_cur(p_cost_group_id
4489 ,p_inventory_item_id
4490 ,p_period_id
4491 );
4492 END IF;
4493
4494 LOOP
4495
4496 l_group2_interorg_tab := l_empty_gp2_interorg_tab;
4497 FETCH group2_interorg_cur BULK COLLECT INTO l_group2_interorg_tab LIMIT l_batch_size;
4498
4499 l_loop_count := l_group2_interorg_tab.count;
4500
4501 FOR i IN 1..l_loop_count
4502 LOOP
4503
4504 IF (l_group2_interorg_tab(i).subinventory_code IS NULL) THEN
4505 l_group2_interorg_tab(i).exp_flag := l_group2_interorg_tab(i).exp_item;
4506 ELSIF (l_group2_interorg_tab(i).exp_item = 1) THEN
4507 l_group2_interorg_tab(i).exp_flag := 1;
4508 END IF;
4509
4510 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4511 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
4512 ,G_MODULE_HEAD || l_routine || '.exp_flag'
4513 ,'Exp Flag:' || l_group2_interorg_tab(i).exp_flag || ' ' ||
4514 'Exp Item:' || l_group2_interorg_tab(i).exp_item
4515 );
4516 END IF;
4517
4518 -- insert into cppb for 1000 inventory items
4519 l_error_num := 0;
4520
4521 IF (CSTPPINV.l_item_id_tbl.COUNT >= 1000) THEN
4522 CSTPPWAC.insert_into_cppb(i_pac_period_id => p_period_id
4523 ,i_cost_group_id => p_cost_group_id
4524 ,i_txn_category => l_txn_category
4525 ,i_user_id => FND_GLOBAL.user_id
4526 ,i_login_id => FND_GLOBAL.login_id
4527 ,i_request_id => FND_GLOBAL.conc_request_id
4528 ,i_prog_id => FND_GLOBAL.conc_program_id
4529 ,i_prog_appl_id => FND_GLOBAL.prog_appl_id
4530 ,o_err_num => l_error_num
4531 ,o_err_code => l_error_code
4532 ,o_err_msg => l_error_msg
4533 );
4534 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4535 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
4536 ,G_MODULE_HEAD || l_routine || '.incppbii1'
4537 ,'After calling insert_into_cppb:'|| l_error_num || l_error_code || l_error_msg
4538 );
4539 END IF;
4540 END IF; -- item existence check in item table
4541
4542 IF l_error_num = 0 THEN
4543
4544 -- Invoke PAC inventory cost processor
4545 CSTPPINV.cost_inv_txn( i_pac_period_id => p_period_id
4546 , i_legal_entity => p_legal_entity_id
4547 , i_cost_type_id => p_cost_type_id
4548 , i_cost_group_id => p_cost_group_id
4549 , i_cost_method => p_cost_method
4550 , i_txn_id => l_group2_interorg_tab(i).transaction_id
4551 , i_txn_action_id => l_group2_interorg_tab(i).transaction_action_id
4552 , i_txn_src_type_id => l_group2_interorg_tab(i).transaction_source_type_id
4553 , i_item_id => l_group2_interorg_tab(i).inventory_item_id
4554 , i_txn_qty => l_group2_interorg_tab(i).primary_quantity
4555 , i_txn_org_id => l_group2_interorg_tab(i).organization_id
4556 , i_txfr_org_id => l_group2_interorg_tab(i).transfer_organization_id
4557 , i_subinventory_code => l_group2_interorg_tab(i).subinventory_code
4558 , i_exp_flag => l_group2_interorg_tab(i).exp_flag
4559 , i_exp_item => l_group2_interorg_tab(i).exp_item
4560 , i_pac_rates_id => p_pac_rates_id
4561 , i_process_group => 2
4562 , i_master_org_id => p_master_org_id
4563 , i_uom_control => p_uom_control
4564 , i_user_id => FND_GLOBAL.user_id
4565 , i_login_id => FND_GLOBAL.login_id
4566 , i_request_id => FND_GLOBAL.conc_request_id
4567 , i_prog_id => FND_GLOBAL.conc_program_id
4568 , i_prog_appl_id => FND_GLOBAL.prog_appl_id
4569 , i_txn_category => l_txn_category
4570 , i_transfer_price_pd => l_group2_interorg_tab(i).transfer_price
4571 , o_err_num => l_error_num
4572 , o_err_code => l_error_code
4573 , o_err_msg => l_error_msg
4574 );
4575
4576 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4577 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
4578 ,G_MODULE_HEAD || l_routine || '.PAC_inv_Processor_gp2'
4579 ,'PAC Inventory Processor:'|| l_error_num || ' ' ||
4580 l_error_code || ' ' || l_error_msg
4581 );
4582 END IF;
4583
4584
4585 END IF; -- error num check
4586
4590 , G_MODULE_HEAD || l_routine || '.others'
4587 IF l_error_num <> 0 THEN
4588 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4589 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION
4591 , 'group2 cost_inv_txn for cost group '|| p_cost_group_id ||' txn id '
4592 || l_group2_interorg_tab(i).transaction_id||' ('||l_error_code||') '||l_error_msg
4593 );
4594 END IF;
4595 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
4596 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
4597 FND_MESSAGE.set_token('MESSAGE', 'group2 cost_inv_txn for cost group '|| p_cost_group_id ||' txn id '
4598 || l_group2_interorg_tab(i).transaction_id||' ('||l_error_code||') '||l_error_msg);
4599 FND_MSG_PUB.Add;
4600 RAISE FND_API.G_EXC_ERROR;
4601 END IF;
4602
4603 -- Insert into MTL_PAC_ACT_CST_DTL_TEMP only if the iteration
4604 -- process flag is enabled and consecutive iterations exist
4605
4606 IF p_iteration_proc_flag = 'Y' AND p_end_iteration_num > 1 THEN
4607
4608 -- Cost derived transactions
4609 INSERT INTO MTL_PAC_ACT_CST_DTL_TEMP
4610 ( COST_GROUP_ID
4611 , TRANSACTION_ID
4612 , PAC_PERIOD_ID
4613 , COST_TYPE_ID
4614 , COST_ELEMENT_ID
4615 , LEVEL_TYPE
4616 , INVENTORY_ITEM_ID
4617 , COST_LAYER_ID
4618 , ACTUAL_COST
4619 , USER_ENTERED
4620 , INSERTION_FLAG
4621 , TRANSACTION_COSTED_DATE
4622 , SHIPMENT_NUMBER
4623 , TRANSFER_TRANSACTION_ID
4624 , TRANSPORTATION_COST
4625 , MOH_ABSORPTION_COST
4626 )
4627 SELECT
4628 cost_group_id
4629 , transaction_id
4630 , pac_period_id
4631 , cost_type_id
4632 , cost_element_id
4633 , level_type
4634 , inventory_item_id
4635 , cost_layer_id
4636 , actual_cost
4637 , user_entered
4638 , insertion_flag
4639 , transaction_costed_date
4640 , l_group2_interorg_tab(i).shipment_number
4641 , l_group2_interorg_tab(i).transfer_transaction_id
4642 , decode(cost_element_id,2,
4643 decode(level_type,1,l_group2_interorg_tab(i).transportation_cost,0),0)
4644 , 0
4645 FROM MTL_PAC_ACTUAL_COST_DETAILS
4646 WHERE transaction_id = l_group2_interorg_tab(i).transaction_id
4647 AND cost_group_id = p_cost_group_id
4648 AND pac_period_id = p_period_id
4649 AND cost_type_id = p_cost_type_id;
4650
4651 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4652 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
4653 ,G_MODULE_HEAD || l_routine || '.after_ins_gp2'
4654 ,'After inserting mtl_pac_act_cst_dtl_temp:' ||
4655 l_group2_interorg_tab(i).transaction_id
4656 );
4657 END IF;
4658
4659 -- Bug 5593086 Pad with nonexistent rows
4660 INSERT INTO MTL_PAC_ACT_CST_DTL_TEMP
4661 ( COST_GROUP_ID
4662 , TRANSACTION_ID
4663 , PAC_PERIOD_ID
4664 , COST_TYPE_ID
4665 , COST_ELEMENT_ID
4666 , LEVEL_TYPE
4667 , INVENTORY_ITEM_ID
4668 , COST_LAYER_ID
4669 , ACTUAL_COST
4670 , USER_ENTERED
4671 , INSERTION_FLAG
4672 , TRANSACTION_COSTED_DATE
4673 , SHIPMENT_NUMBER
4674 , TRANSFER_TRANSACTION_ID
4675 , TRANSPORTATION_COST
4676 , MOH_ABSORPTION_COST
4677 )
4678 SELECT
4679 mpacd.cost_group_id
4680 , mpacd.transaction_id
4681 , mpacd.pac_period_id
4682 , mpacd.cost_type_id
4683 , cce.cost_element_id
4684 , lt.level_type
4685 , mpacd.inventory_item_id
4686 , mpacd.cost_layer_id
4687 , 0
4688 , mpacd.user_entered
4689 , mpacd.insertion_flag
4690 , mpacd.transaction_costed_date
4691 , l_group2_interorg_tab(i).shipment_number
4692 , l_group2_interorg_tab(i).transfer_transaction_id
4693 , 0
4694 , 0
4695 FROM (SELECT *
4696 FROM MTL_PAC_ACTUAL_COST_DETAILS
4697 WHERE transaction_id = l_group2_interorg_tab(i).transaction_id
4698 AND cost_group_id = p_cost_group_id
4699 AND pac_period_id = p_period_id
4700 AND cost_type_id = p_cost_type_id
4701 AND rownum = 1) mpacd,
4702 CST_COST_ELEMENTS cce,
4703 (SELECT 1 level_type FROM DUAL
4704 UNION
4705 SELECT 2 level_type FROM DUAL) lt
4706 WHERE NOT EXISTS
4707 (SELECT 1
4708 FROM mtl_pac_act_cst_dtl_temp mpacdt
4709 WHERE mpacdt.cost_group_id = p_cost_group_id
4710 AND mpacdt.transaction_id = l_group2_interorg_tab(i).transaction_id
4711 AND mpacdt.pac_period_id = p_period_id
4712 AND mpacdt.cost_type_id = p_cost_type_id
4713 AND mpacdt.cost_element_id = cce.cost_element_id
4714 AND mpacdt.level_type = lt.level_type);
4715
4716
4717 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4718 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
4719 ,G_MODULE_HEAD || l_routine || '.after_pad_gp2'
4720 ,'After padding mtl_pac_act_cst_dtl_temp:'||
4721 l_group2_interorg_tab(i).transaction_id
4722 );
4723 END IF;
4724
4725 END IF; -- IF p_iteration_proc_flag = 'Y' AND p_end_iteration_num > 1 THEN
4726 END LOOP; -- FOR i IN 1..l_loop_count
4727
4728 EXIT WHEN group2_interorg_cur%NOTFOUND;
4729 END LOOP; -- FETCH loop
4730 CLOSE group2_interorg_cur;
4731
4732 -- ======================================================
4733 -- Only for FIRST iteration perform:
4734 -- insert left over interorg issues into cppb
4735 -- Update CPPB
4736 -- ======================================================
4737 l_error_num := 0;
4738
4739 IF (CSTPPINV.l_item_id_tbl.COUNT > 0) THEN
4740 CSTPPWAC.insert_into_cppb(i_pac_period_id => p_period_id
4741 ,i_cost_group_id => p_cost_group_id
4742 ,i_txn_category => l_txn_category
4743 ,i_user_id => FND_GLOBAL.user_id
4744 ,i_login_id => FND_GLOBAL.login_id
4745 ,i_request_id => FND_GLOBAL.conc_request_id
4746 ,i_prog_id => FND_GLOBAL.conc_program_id
4747 ,i_prog_appl_id => FND_GLOBAL.prog_appl_id
4748 ,o_err_num => l_error_num
4749 ,o_err_code => l_error_code
4750 ,o_err_msg => l_error_msg
4751 );
4752
4753 l_error_num := NVL(l_error_num, 0);
4754 l_error_code := NVL(l_error_code, 'No Error');
4755 l_error_msg := NVL(l_error_msg, 'No Error');
4756
4757 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4758 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
4759 ,G_MODULE_HEAD || l_routine || '.inscppii2'
4760 ,'After calling insert_into_cppb:'|| l_error_num || l_error_code || l_error_msg
4761 );
4762 END IF;
4763 END IF; -- item existence check in item table
4764
4765 IF l_error_num = 0 THEN
4766 CSTPPWAC.update_item_cppb(i_pac_period_id => p_period_id
4767 ,i_cost_group_id => p_cost_group_id
4768 ,i_txn_category => l_txn_category
4769 ,i_item_id => p_inventory_item_id
4770 ,i_user_id => FND_GLOBAL.user_id
4771 ,i_login_id => FND_GLOBAL.login_id
4772 ,i_request_id => FND_GLOBAL.conc_request_id
4773 ,i_prog_id => FND_GLOBAL.conc_program_id
4774 ,i_prog_appl_id => FND_GLOBAL.prog_appl_id
4775 ,o_err_num => l_error_num
4776 ,o_err_code => l_error_code
4777 ,o_err_msg => l_error_msg
4778 );
4779
4780 l_error_num := NVL(l_error_num, 0);
4781 l_error_code := NVL(l_error_code, 'No Error');
4782 l_error_msg := NVL(l_error_msg, 'No Error');
4783
4784 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4785 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
4786 ,G_MODULE_HEAD || l_routine || '.upppii1'
4787 ,'After calling update_item_cppb:'|| l_error_num || l_error_code || l_error_msg
4788 );
4789 END IF;
4790 END IF; -- error check
4791
4792 IF l_error_num <> 0 THEN
4793 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4794 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION
4795 , G_MODULE_HEAD || l_routine || '.others'
4796 , 'CSTPPWAC.update_item_cppb for CG '|| p_cost_group_id ||' txn category '
4797 ||l_txn_category||' t_low_level_code ' ||p_low_level_code
4798 ||' error('||l_error_code||') '||l_error_msg
4799 );
4800 END IF;
4801 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
4802 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
4803 FND_MESSAGE.set_token('MESSAGE', 'CSTPPWAC.update_item_cppb for CG '|| p_cost_group_id ||' txn category '
4804 ||l_txn_category||' t_low_level_code ' ||p_low_level_code
4805 ||' error('||l_error_code||') '||l_error_msg);
4806 FND_MSG_PUB.Add;
4807 RAISE FND_API.G_EXC_ERROR;
4808 END IF;
4809
4810 EXCEPTION
4811 WHEN FND_API.G_EXC_ERROR THEN
4812 RAISE FND_API.G_EXC_ERROR;
4813 WHEN OTHERS THEN
4814 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
4815 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
4816 FND_MESSAGE.set_token('MESSAGE', '('||SQLCODE||') '||SQLERRM);
4817 FND_MSG_PUB.Add;
4818 RAISE FND_API.G_EXC_ERROR;
4819 END Group2_Interorg_Iteration1;
4820 -- +========================================================================+
4821 -- PROCEDURE: Compute_Iterative_Pwac_Cost PRIVATE UTILITY
4822 -- PARAMETERS:
4823 -- p_legal_entity_id NUMBER
4824 -- p_cost_type_id NUMBER
4825 -- p_cost_method NUMBER
4826 -- p_inventory_item_id NUMBER
4827 -- p_period_id NUMBER
4831 -- p_end_iteration_num NUMBER
4828 -- p_period_start_date DATE
4829 -- p_period_end_date DATE
4830 -- p_iteration_num NUMBER
4832 -- p_pac_rates_id NUMBER
4833 -- p_uom_control NUMBER
4834 -- p_iteration_proc_flag VARCHAR2
4835 -- x_return_status OUT NOCOPY VARCHAR2
4836 -- x_error_msg OUT NOCOPY VARCHAR2
4837 -- COMMENT:
4838 -- This procedure is called by the API iteration_process
4839 -- FOR each optimal cost group LOOP
4840 -- Is the iteration is FIRST iteration
4841 -- default current behavior
4842 -- Process Group 1 (cost owned) interorg transactions across CGs
4843 -- using existing api
4844 -- Process Group 2 (cost derived) interorg transactions across CGs
4845 -- using existing api
4846 -- Is the iteration NOT FIRST iteration
4847 -- consecutive iteration process
4848 -- Process Group 1 (cost owned) interorg txns across CGs
4849 -- Process Group 2 (cost owned) interorg txns across CGs
4850 -- END LOOP
4851 -- PRE-COND: none
4852 -- EXCEPTIONS:
4853 -- +========================================================================+
4854 PROCEDURE Compute_Iterative_Pwac_Cost
4855 ( p_legal_entity_id IN NUMBER
4856 , p_cost_type_id IN NUMBER
4857 , p_cost_method IN NUMBER
4858 , p_inventory_item_id IN NUMBER
4859 , p_inventory_item_number IN VARCHAR2
4860 , p_cost_group_id IN NUMBER
4861 , p_low_level_code IN NUMBER
4862 , p_period_id IN NUMBER
4863 , p_period_start_date IN DATE
4864 , p_period_end_date IN DATE
4865 , p_iteration_num IN NUMBER
4866 , p_end_iteration_num IN NUMBER
4867 , p_pac_rates_id IN NUMBER
4868 , p_uom_control IN NUMBER
4869 , p_iteration_proc_flag IN VARCHAR2
4870 )
4871 IS
4872
4873 -- routine name local constant variable
4874 l_routine CONSTANT VARCHAR2(30) := 'Compute_Iterative_Pwac_Cost';
4875
4876 -- ================================================================
4877 -- Cursor to get Group 1 (cost owned) interorg transactions
4878 -- across Cost Groups
4879 -- Support transfer pricing option:
4880 -- Profile option value: 0 and 1: Include interorg txns from OM
4881 -- Profile option value: 2 : Do not include interorg txns from OM
4882 -- OPM Convergence: Exclude opm logical receipt 15
4883 -- normal interorg receipts other than generated through internal
4884 -- sales orders
4885 -- interorg receipts generated through internal sales orders only
4886 -- when the transfer price option is 0 or 1 (not enabled)
4887 -- transaction source type 7 - internal requisition
4888 -- transaction source type 8 - internal order
4889 -- transaction source type 13 - Oracle Inventory
4890 -- ================================================================
4891 CURSOR group1_interorg_cur(c_cost_group_id NUMBER
4892 ,c_inventory_item_id NUMBER
4893 ,c_period_id NUMBER
4894 )
4895 IS
4896 SELECT
4897 ccit.transaction_id transaction_id
4898 , ccit.transaction_action_id transaction_action_id
4899 , ccit.transaction_source_type_id transaction_source_type_id
4900 , ccit.inventory_item_id inventory_item_id
4901 , ccit.primary_quantity primary_quantity
4902 , ccit.periodic_primary_quantity periodic_primary_quantity
4903 , ccit.organization_id organization_id
4904 , nvl(ccit.transfer_organization_id,-1) transfer_organization_id
4905 , ccit.subinventory_code subinventory_code
4906 , nvl(ccit.transfer_price,0) transfer_price
4907 , ccit.shipment_number shipment_number
4908 , ccit.transfer_transaction_id transfer_transaction_id
4909 , ccit.waybill_airbill waybill_airbill
4910 , nvl(ccit.transfer_cost,0) transfer_cost
4911 , nvl(ccit.transportation_cost,0) transportation_cost
4912 , nvl(ccit.transfer_percentage,0) transfer_percentage
4913 , DECODE(msi.inventory_asset_flag,'Y',0,1) exp_item
4914 , DECODE(msubinv.asset_inventory,1,0,1) exp_flag
4915 FROM
4916 CST_PAC_INTERORG_TXNS_TMP ccit
4917 , mtl_system_items msi
4918 , mtl_secondary_inventories msubinv
4919 WHERE ccit.inventory_item_id = c_inventory_item_id
4920 AND ccit.cost_group_id = c_cost_group_id
4921 AND ccit.pac_period_id = c_period_id
4922 AND ccit.inventory_item_id = msi.inventory_item_id
4923 AND msi.organization_id = ccit.organization_id
4924 AND msubinv.organization_id(+) = ccit.organization_id
4925 AND msubinv.secondary_inventory_name(+) = ccit.subinventory_code
4926 AND ccit.txn_type = 1;
4927
4928 TYPE group1_interorg_tab IS TABLE OF group1_interorg_cur%rowtype INDEX BY BINARY_INTEGER;
4929 l_group1_interorg_tab group1_interorg_tab;
4930 l_empty_gp1_interorg_tab group1_interorg_tab;
4931
4932 -- Cursor to obtain PAC transaction cost element, level type and
4933 -- cost information
4934 CURSOR pac_txn_cursor(c_cost_group_id NUMBER
4935 ,c_period_id NUMBER
4936 ,c_transaction_id NUMBER
4937 ,c_inventory_item_id NUMBER
4938 )
4939 IS
4940 SELECT
4941 cost_layer_id
4942 , prior_buy_cost
4943 , prior_make_cost
4944 , new_buy_cost
4945 , new_make_cost
4946 , cost_element_id
4947 , level_type
4948 , prior_cost
4949 , actual_cost
4950 , new_cost
4951 , transfer_transaction_id
4952 , transfer_cost
4953 , transportation_cost
4954 , moh_absorption_cost
4955 , new_buy_quantity
4956 FROM
4957 mtl_pac_act_cst_dtl_temp
4958 WHERE pac_period_id = c_period_id
4962 ORDER BY
4959 AND cost_group_id = c_cost_group_id
4960 AND transaction_id = c_transaction_id
4961 AND inventory_item_id = c_inventory_item_id
4963 cost_element_id
4964 , level_type
4965 , transaction_id
4966 FOR UPDATE;
4967
4968 pac_txn_cursor_row pac_txn_cursor%rowtype;
4969
4970 -- cursor to get pac actual cost from
4971 -- mpacd_temp
4972 CURSOR actual_cost_cur(c_pac_period_id NUMBER
4973 ,c_cost_group_id NUMBER
4974 ,c_inventory_item_id NUMBER
4975 ,c_transaction_id NUMBER
4976 ,c_cost_element_id NUMBER
4977 ,c_level_type NUMBER
4978 )
4979 IS
4980 SELECT
4981 nvl(actual_cost,0) actual_cost
4982 FROM
4983 mtl_pac_act_cst_dtl_temp
4984 WHERE pac_period_id = c_pac_period_id
4985 AND cost_group_id = c_cost_group_id
4986 AND inventory_item_id = c_inventory_item_id
4987 AND transaction_id = c_transaction_id
4988 AND cost_element_id = c_cost_element_id
4989 AND level_type = c_level_type;
4990
4991 -- Cursor to retrieve item cost of a cost group from
4992 -- interorg items temp table
4993 CURSOR prev_itr_item_cost_cur(c_pac_period_id NUMBER
4994 ,c_cost_group_id NUMBER
4995 ,c_inventory_item_id NUMBER
4996 )
4997 IS
4998 SELECT
4999 nvl(item_cost,0),
5000 nvl(difference,0)
5001 FROM CST_PAC_INTORG_ITMS_TEMP
5002 WHERE pac_period_id = c_pac_period_id
5003 AND cost_group_id = c_cost_group_id
5004 AND inventory_item_id = c_inventory_item_id;
5005
5006
5007 l_txn_qty NUMBER := 0;
5008 l_new_correspond_cost NUMBER := 0;
5009 l_correspond_pmac_cost NUMBER := 0;
5010 l_cost_element_id NUMBER := -99;
5011 l_level_type NUMBER := -99;
5012
5013 l_loop_count NUMBER := 0;
5014 l_batch_size NUMBER := 200;
5015
5016 -- Assign transaction variable
5017 l_pwac_new_cost NUMBER := 0;
5018
5019 l_pwac_item_cost NUMBER := 0;
5020 l_old_difference NUMBER;
5021 l_new_difference NUMBER;
5022 -- Previous iteraton item cost variable
5023 l_prev_itr_item_cost NUMBER := 0;
5024 l_transfer_cost NUMBER := 0;
5025 l_unit_transfer_cost NUMBER := 0;
5026
5027 -- Master Organization of Cost Group
5028 -- NOTE: All organizations under the Cost Group have same master item org
5029 -- expense flag variables
5030
5031 l_valid_txn_flag VARCHAR2(1);
5032
5033 -- Error message variables
5034 l_error_num NUMBER;
5035 l_error_code VARCHAR2(240);
5036 l_error_msg VARCHAR2(240);
5037 l_return_status VARCHAR2(1);
5038
5039 -- Transaction category
5040 l_txn_category NUMBER;
5041 l_txn_quantity NUMBER;
5042 l_actual_cost NUMBER;
5043
5044 -- binary index for PWAC New cost table
5045 l_cg_pwac_idx BINARY_INTEGER;
5046
5047 -- period balance including interorg receipts
5048 l_period_quantity NUMBER := 0;
5049 -- period running balance
5050 l_period_new_balance NUMBER;
5051
5052 l_cg_idx BINARY_INTEGER;
5053 l_cost_group_id_idx BINARY_INTEGER;
5054 l_cost_group_name VARCHAR2(10);
5055
5056 -- bug 7674673 fix
5057 l_pcu_value_balance NUMBER;
5058 l_cg_elmnt_lv_idx BINARY_INTEGER;
5059
5060 -- Exceptions
5061 l_diverging_exception EXCEPTION;
5062 BEGIN
5063
5064 -- ==================================================================
5065 -- Retrieve Balance before interorg txns across Cost Groups from CPPB
5066 -- Store the period quantity, period balance into G_PWAC_NEW_COST_TBL
5067 -- NOTE: G_PWAC_NEW_COST_TBL is deleted at the end of each optimal
5068 -- cost group in each iteration
5069 -- period quantity before intorg is stored in G_CST_GROUP_TBL
5070 -- ==================================================================
5071 Get_Balance_Before_Intorg(p_period_id => p_period_id
5072 ,p_cost_group_id => p_cost_group_id
5073 ,p_inventory_item_id => p_inventory_item_id
5074 );
5075 -- =========================================================================
5076 -- initialize period running quantity at each consecutive iteration.
5077 -- period quantity is same for all the cost elements, level type for a given
5078 -- inventory item, cost group, pac period
5079 -- =========================================================================
5080 l_period_quantity := G_PWAC_NEW_COST_TBL(1)(1).period_qty_bef_intorg;
5081
5082 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5083 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
5084 ,G_MODULE_HEAD || l_routine || '.qtybef'
5085 ,'Qty before intorg txns:' || l_period_quantity
5086 );
5087 END IF;
5088
5089
5090 /* If cost is found to be diverging after successive iterations,
5091 the changes done for this CG will be rolled back.
5092 The validation of divergence can be done after 3rd iteration.
5093 Item cost can be said to be diverging if the following is true
5094 abs(current iteration item cost - previous iteration item cost)
5095 is more than or equal to abs(previous iteration item cost - the one before that)
5096 */
5097
5098 SAVEPOINT diverge_case;
5099
5100 -- initialize transaction category for interorg receipts across CGs
5101 l_txn_category := 8;
5102
5103 -- ===================================================================
5104 -- Process Group 1 cost owned interorg transactions
5108 ,p_inventory_item_id
5105 -- ===================================================================
5106 IF NOT group1_interorg_cur%ISOPEN THEN
5107 OPEN group1_interorg_cur(p_cost_group_id
5109 ,p_period_id
5110 );
5111 END IF;
5112
5113 LOOP
5114
5115 l_group1_interorg_tab := l_empty_gp1_interorg_tab;
5116 FETCH group1_interorg_cur BULK COLLECT INTO l_group1_interorg_tab LIMIT l_batch_size;
5117
5118 l_loop_count := l_group1_interorg_tab.count;
5119
5120 FOR i IN 1..l_loop_count
5121 LOOP
5122 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5123 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
5124 ,G_MODULE_HEAD || l_routine || '.group1_interorg'
5125 ,'Group 1 -- Transaction Id:'|| l_group1_interorg_tab(i).transaction_id
5126 );
5127 END IF;
5128
5129 IF (l_group1_interorg_tab(i).subinventory_code IS NULL) THEN
5130 l_group1_interorg_tab(i).exp_flag := l_group1_interorg_tab(i).exp_item;
5131 ELSIF (l_group1_interorg_tab(i).exp_item = 1) THEN
5132 l_group1_interorg_tab(i).exp_flag := 1;
5133 END IF;
5134
5135 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5136 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
5137 ,G_MODULE_HEAD || l_routine || '.exp_flag'
5138 ,'Expense Flag:'|| l_group1_interorg_tab(i).exp_flag ||
5139 ' Expense Item:' || l_group1_interorg_tab(i).exp_item
5140 );
5141 END IF;
5142
5143 -- ================================================================
5144 -- Consecutive Iterations
5145 -- Only for cost groups where interorg receipt exists
5146 -- In other words, sequence number 1 should be processed only once
5147 -- ----------------------------------------------------------------
5148 -- Additional criteria:No PMAC calculation for the below conditions:
5149 -- Direct interorg receipt, received in expense subinventory
5150 -- Intransit receipt FOB:Receipt, received in expense subinventory
5151 -- ----------------------------------------------------------------
5152 -- Perform PMAC calculation always when
5153 -- Intransit shipment FOB:Shipment, irrespective of the receiving
5154 -- in Asset or non-asset sub inventory as it will not be know until
5155 -- the intransit receipt is created
5156 -- At the time of intransit receipt, the deduction of qty is done
5157 -- if received in expense sub inventory. No deduction of qty if
5158 -- received in asset sub inventory. In either case, cost will NOT
5159 -- change
5160 -- ----------------------------------------------------------------
5161 l_valid_txn_flag := 'Y';
5162
5163 IF (l_group1_interorg_tab(i).transaction_action_id = 3 AND
5164 l_group1_interorg_tab(i).primary_quantity > 0) OR
5165 (l_group1_interorg_tab(i).transaction_action_id = 12) THEN
5166
5167 -- Check whether sub inventory is expense sub inventory
5168 IF l_group1_interorg_tab(i).exp_flag = 1 THEN
5169 l_valid_txn_flag := 'N';
5170 END IF;
5171
5172 END IF;
5173
5174 IF l_valid_txn_flag = 'Y' THEN
5175
5176 -- ----------------------------------------------------------------
5177 -- Consecutive Iterations for Cost Owned Transactions
5178 -- Re-calculate PWAC cost
5179 -- --------------------------------------------------------------
5180 -- Reverse the sign of quantity, since shipment is processed by
5181 -- receiving cost group
5182 IF (l_group1_interorg_tab(i).transaction_action_id = 21) THEN
5183 l_txn_qty := l_group1_interorg_tab(i).primary_quantity * -1;
5184 ELSE
5185 l_txn_qty := l_group1_interorg_tab(i).primary_quantity;
5186 END IF;
5187
5188 -- period balance quantity
5189 l_period_quantity := l_period_quantity + l_txn_qty;
5190
5191
5192 -- Get PAC Transaction Cost element Id, Level Type and
5193 -- Cost Information
5194 OPEN pac_txn_cursor(p_cost_group_id
5195 ,p_period_id
5196 ,l_group1_interorg_tab(i).transaction_id
5197 ,p_inventory_item_id
5198 );
5199
5200 FETCH pac_txn_cursor
5201 INTO pac_txn_cursor_row;
5202
5203 l_cost_element_id := pac_txn_cursor_row.cost_element_id;
5204 l_level_type := pac_txn_cursor_row.level_type;
5205
5206
5207 WHILE pac_txn_cursor%FOUND LOOP
5208
5209 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5210 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
5211 ,G_MODULE_HEAD || l_routine || '.pac_txn_cursor'
5212 ,'Cost element Id:' || l_cost_element_id || ' '
5213 || 'Level Type:' || l_level_type
5214 );
5215 END IF;
5216
5217
5218 -- Get New pwac Cost of corresponding Cost Group for the cost element, level type
5219 Get_Correspond_Pwac_New_Cost(p_cost_group_id => p_cost_group_id
5220 ,p_cost_type_id => p_cost_type_id
5221 ,p_opp_transaction_id => l_group1_interorg_tab(i).transfer_transaction_id
5222 ,p_period_id => p_period_id
5223 ,p_organization_id => l_group1_interorg_tab(i).organization_id
5224 ,p_opp_organization_id => l_group1_interorg_tab(i).transfer_organization_id
5225 ,p_transaction_id => l_group1_interorg_tab(i).transaction_id
5226 ,p_transaction_action_id => l_group1_interorg_tab(i).transaction_action_id
5227 ,p_cost_element_id => l_cost_element_id
5228 ,p_level_type => l_level_type
5229 ,p_group_num => 1
5230 ,x_new_correspond_cost => l_new_correspond_cost );
5231
5232 -- ============================================================================
5233 -- Calculate transfer cost as a percentage of PMAC of shipping cost group
5234 -- MOH Cost element, current level
5235 -- ============================================================================
5236 IF l_cost_element_id = 2 AND l_level_type = 1 THEN
5237 l_txn_quantity := abs(l_group1_interorg_tab(i).primary_quantity);
5238
5239 IF l_group1_interorg_tab(i).transfer_percentage <> 0 THEN
5240
5241 -- Get Pmac Cost of corresponding Cost Group - sum of all cost elements, level types
5242 Get_Correspond_Pmac_Cost(p_cost_group_id => p_cost_group_id
5243 ,p_cost_type_id => p_cost_type_id
5244 ,p_opp_transaction_id => l_group1_interorg_tab(i).transfer_transaction_id
5245 ,p_period_id => p_period_id
5246 ,p_organization_id => l_group1_interorg_tab(i).organization_id
5247 ,p_opp_organization_id => l_group1_interorg_tab(i).transfer_organization_id
5248 ,p_transaction_id => l_group1_interorg_tab(i).transaction_id
5249 ,p_transaction_action_id => l_group1_interorg_tab(i).transaction_action_id
5250 ,p_group_num => 1
5251 ,x_correspond_pmac_cost => l_correspond_pmac_cost);
5252
5253 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5254 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
5255 ,G_MODULE_HEAD || l_routine || '.corr_pmac'
5256 ,'Correspond PMAC Cost:' || l_correspond_pmac_cost
5257 );
5258 END IF;
5259
5260 l_unit_transfer_cost :=
5261 (l_correspond_pmac_cost * l_group1_interorg_tab(i).transfer_percentage / 100);
5262
5263 ELSIF l_group1_interorg_tab(i).transfer_cost <> 0 THEN --IF l_group1_idx.transfer_percentage <> 0 THEN
5264
5265 -- Transfer percentage is not set; Get the transfer cost amount from MMT
5266 -- To obtain Unit Transfer Cost: Transfer cost amount of MMT to be divided by txn qty
5267 l_transfer_cost := l_group1_interorg_tab(i).transfer_cost;
5268 l_unit_transfer_cost := l_transfer_cost / l_txn_quantity;
5269
5270 END IF; -- IF l_group1_idx.transfer_percentage <> 0 THEN
5271
5272 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5273 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
5274 ,G_MODULE_HEAD || l_routine || '.txfr_cost1'
5275 ,'Transaction Qty:' || l_txn_quantity || ' Transfer Percentage:' || l_group1_interorg_tab(i).transfer_percentage || ' Unit Transfer Cost:' || l_unit_transfer_cost
5276 );
5277 END IF;
5278
5279 END IF; --IF l_cost_element_id = 2 AND l_level_type = 1
5280
5281
5282 -- =======================================================================
5283 -- Update Actual Cost with New Cost of corresponding transaction
5284 -- For cost element 2 - MOH, actual_cost will be New Cost of corresponding
5285 -- transaction cost + Unit Transfer Cost + Unit Transportation Cost
5286 -- + moh_absorption_cost
5287 -- For all other cost elements, actual_cost will be New Cost of
5288 -- corresponding transaction
5289 -- =======================================================================
5290 UPDATE MTL_PAC_ACT_CST_DTL_TEMP
5291 SET actual_cost = decode(cost_element_id, 2,
5292 decode(level_type,1, (l_new_correspond_cost + l_unit_transfer_cost +
5293 (transportation_cost/l_txn_quantity) + moh_absorption_cost), l_new_correspond_cost),l_new_correspond_cost)
5294 , transfer_cost = DECODE(cost_element_id, 2, decode(level_type,1, l_unit_transfer_cost,0),0)
5295 WHERE CURRENT OF pac_txn_cursor;
5296
5297 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5298 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
5299 ,G_MODULE_HEAD || l_routine || '.upd_with_new_cost'
5300 ,'New Cost of corresponding transaction:' || l_new_correspond_cost
5301 );
5302
5303 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
5304 ,G_MODULE_HEAD || l_routine || '.transmohcost'
5305 ,'Transfer Cost:' || pac_txn_cursor_row.transfer_cost
5306 || ' Transportation Cost:' || pac_txn_cursor_row.transportation_cost || ' Transaction Qty:' || abs(l_group1_interorg_tab(i).primary_quantity) || ' MOH Absorption Cost:' || pac_txn_cursor_row.moh_absorption_cost
5307 );
5308
5309 END IF;
5310
5311 -- ===============================================================================
5312 -- Calculate running period balance at each iteration
5313 -- To store period new balance for each cost element, level type for a given
5314 -- inventory item id, cost group, pac period
5315 -- ===============================================================================
5316
5317
5318 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5319 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
5320 ,G_MODULE_HEAD || l_routine || '.pac_txn_cursor_first'
5321 ,'Cost element Id:' || l_cost_element_id || ' '
5322 || 'Level Type:' || l_level_type
5323 );
5324 END IF;
5325
5326 -- ======================================
5327 -- Get Actual Cost from MPACD TEMP
5328 -- current value of actual cost
5329 -- ======================================
5330 OPEN actual_cost_cur(p_period_id
5331 ,p_cost_group_id
5332 ,p_inventory_item_id
5333 ,l_group1_interorg_tab(i).transaction_id
5334 ,l_cost_element_id
5335 ,l_level_type
5336 );
5337 FETCH actual_cost_cur
5338 INTO l_actual_cost;
5339
5340 IF actual_cost_cur%NOTFOUND THEN
5341 l_actual_cost := 0;
5342 END IF;
5343
5347 -- cumulate the running balance for cost element,level type
5344 CLOSE actual_cost_cur;
5345
5346
5348 G_PWAC_NEW_COST_TBL(l_cost_element_id)(l_level_type).period_new_balance :=
5349 G_PWAC_NEW_COST_TBL(l_cost_element_id)(l_level_type).period_new_balance + l_txn_qty * l_actual_cost;
5350
5351 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5352 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
5353 ,G_MODULE_HEAD || l_routine || '.newbal'
5354 ,'New balance before PCU value change:' || G_PWAC_NEW_COST_TBL(l_cost_element_id)(l_level_type).period_new_balance
5355 );
5356 END IF;
5357
5358
5359
5360 -- ===============================================
5361 -- Calculate Variance Amount and update MPACD_TEMP
5362 -- ===============================================
5363 l_period_new_balance :=
5364 G_PWAC_NEW_COST_TBL(l_cost_element_id)(l_level_type).period_new_balance;
5365
5366 UPDATE mtl_pac_act_cst_dtl_temp
5367 SET variance_amount = decode(sign(l_period_quantity),
5368 0, l_period_new_balance,
5369 (-1 * sign(l_period_new_balance)),
5370 l_period_new_balance,0)
5371 WHERE CURRENT OF pac_txn_cursor;
5372
5373
5374 -- Fetch next PAC transaction
5375 FETCH pac_txn_cursor
5376 INTO pac_txn_cursor_row;
5377
5378 EXIT WHEN pac_txn_cursor%NOTFOUND;
5379
5380 l_cost_element_id := pac_txn_cursor_row.cost_element_id;
5381 l_level_type := pac_txn_cursor_row.level_type;
5382
5383 END LOOP; --WHILE pac_txn_cursor%FOUND LOOP
5384
5385 CLOSE pac_txn_cursor;
5386
5387 END IF; -- valid txn flag check
5388
5389 END LOOP; -- FOR i IN 1..l_loop_count
5390
5391 EXIT WHEN group1_interorg_cur%NOTFOUND;
5392 END LOOP; -- FETCH loop
5393 CLOSE group1_interorg_cur;
5394
5395 -- ============================================================
5399 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5396 -- Re-average to calculate the new periodic moving average cost
5397 -- of the item in each cost element, level type
5398 -- ============================================================
5400 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
5401 ,G_MODULE_HEAD || l_routine || '.qtyaft'
5402 ,'Period Quantity after interorg receipts:'|| l_period_quantity
5403 );
5404 END IF;
5405
5406 -- Calculate the item cost since all cost owned interorg transactions are processed
5407 -- Set period quantity after all interorg receipts
5408 l_cg_idx := p_cost_group_id;
5409 G_CST_GROUP_TBL(l_cg_idx).period_new_quantity := l_period_quantity;
5410
5411 IF p_iteration_num > 1 THEN
5412 l_cost_element_id := 1;
5413 WHILE l_cost_element_id <= 5 LOOP
5414 l_level_type := 1;
5415 WHILE l_level_type <= 2 LOOP
5416
5417 -- =============================
5418 -- Bug 7674673 fix
5419 -- Get PCU Value Change Balance
5420 -- =============================
5421 l_cg_elmnt_lv_idx := TO_CHAR(p_cost_group_id) || TO_CHAR(l_cost_element_id) || TO_CHAR(l_level_type);
5422
5423 IF G_PCU_VALUE_CHANGE_TBL.EXISTS(l_cg_elmnt_lv_idx) THEN
5424 l_pcu_value_balance := G_PCU_VALUE_CHANGE_TBL(l_cg_elmnt_lv_idx).pcu_value_balance;
5425 ELSE
5426 l_pcu_value_balance := 0;
5427 END IF;
5428
5429 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5430 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
5431 ,G_MODULE_HEAD || l_routine || '.pcuvalbal'
5432 ,'PCU value change balance: CG-element-level' || l_cg_elmnt_lv_idx || ' '
5433 || l_pcu_value_balance
5434 );
5435 END IF;
5436
5437
5438 -- ===========================================================================
5439 -- New Period Balance after adding PCU value change at each iteration
5440 -- ===========================================================================
5441 G_PWAC_NEW_COST_TBL(l_cost_element_id)(l_level_type).period_new_balance :=
5442 G_PWAC_NEW_COST_TBL(l_cost_element_id)(l_level_type).period_new_balance + l_pcu_value_balance;
5443
5444 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5445 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
5446 ,G_MODULE_HEAD || l_routine || '.newpcuval'
5447 ,'New balance after PCU value change:' || G_PWAC_NEW_COST_TBL(l_cost_element_id)(l_level_type).period_new_balance
5448 );
5449 END IF;
5450
5454 -- Re-calculate new item cost
5451 l_period_new_balance := G_PWAC_NEW_COST_TBL(l_cost_element_id)(l_level_type).period_new_balance;
5452
5453 -- ===========================
5455 -- ===========================
5456 IF (SIGN(l_period_quantity) = (-1 * SIGN(l_period_new_balance))) OR (l_period_quantity = 0) THEN
5457 -- set final new cost to 0
5458 G_PWAC_NEW_COST_TBL(l_cost_element_id)(l_level_type).final_new_cost := 0;
5459 ELSE
5460 G_PWAC_NEW_COST_TBL(l_cost_element_id)(l_level_type).final_new_cost
5461 := l_period_new_balance / l_period_quantity;
5462 END IF;
5463
5464 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5465 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
5466 ,G_MODULE_HEAD || l_routine || '.pdnewbal'
5467 ,'Cost Element Id:' || l_cost_element_id || ' Level Type:'
5468 || l_level_type || 'Period New Balance:'|| l_period_new_balance
5469 );
5470 END IF;
5471 l_level_type := l_level_type + 1;
5472 END LOOP;
5473 l_cost_element_id := l_cost_element_id + 1;
5474 END LOOP;
5475 END IF;
5476
5477 IF p_iteration_proc_flag = 'Y' AND p_end_iteration_num > 1 THEN
5478 -- ==================================================================
5479 -- Calculate PMAC Item Cost in the current iteration - Sum of all the
5480 -- cost elements,level types
5481 -- Store the current iteration and previous iteration PMAC item cost
5482 -- ==================================================================
5483
5484 -- Sum of all the cost elements and level types
5485 l_pwac_item_cost := 0;
5486 l_cost_element_id := 1;
5487 -- Calculate item cost (sum of all cost elements)
5488 WHILE l_cost_element_id <= 5 LOOP
5489 l_level_type := 1;
5490 WHILE l_level_type <= 2 LOOP
5491 l_pwac_item_cost := l_pwac_item_cost + G_PWAC_NEW_COST_TBL(l_cost_element_id)(l_level_type).final_new_cost;
5492 l_level_type := l_level_type + 1;
5493 END LOOP;
5494 l_cost_element_id := l_cost_element_id + 1;
5495 END LOOP;
5496
5497
5498 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5499 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
5500 , G_MODULE_HEAD || l_routine || '.pwacic'
5501 , 'Cost Group Id:' || p_cost_group_id || 'PWAC Item Cost:' || l_pwac_item_cost
5502 );
5503 END IF;
5504
5505 -- Retrieve previous iteration pmac item cost
5506 -- get item_cost
5507 OPEN prev_itr_item_cost_cur(p_period_id
5508 ,p_cost_group_id
5509 ,p_inventory_item_id
5510 );
5511 FETCH prev_itr_item_cost_cur
5512 INTO l_prev_itr_item_cost,
5513 l_old_difference;
5514 CLOSE prev_itr_item_cost_cur;
5515
5516 l_new_difference := abs(l_pwac_item_cost-l_prev_itr_item_cost);
5517
5518 IF p_iteration_num > 2 and l_new_difference <> 0 and l_new_difference >= l_old_difference THEN
5519 ROLLBACK TO diverge_case;
5520 /* Update PAC tables for diverging cost group since further iterations will not take place
5521 for the item in this cost group and if the item does not achieve tolerance in this run
5522 and user chooses to Resume for Non Tolerance in the next run, the values in PL/SQL table
5523 G_CG_PWAC_COST_TBL will be lost and update_cpicd_with_new_values will error out with no data found */
5524 Create_Mpacd_With_New_Values(p_period_id
5525 ,p_inventory_item_id
5526 ,p_cost_group_id
5527 );
5528
5529 Update_Cpicd_With_New_Values(p_pac_period_id => p_period_id
5530 ,p_inventory_item_id => p_inventory_item_id
5531 ,p_cost_group_id => p_cost_group_id
5532 ,p_cost_type_id => p_cost_type_id
5533 ,p_end_date => p_period_end_date
5534 );
5535 UPDATE CST_PAC_INTORG_ITMS_TEMP
5536 SET DIVERGING_FLAG = 'Y',
5537 TOLERANCE_FLAG = 'Y',
5538 ITERATION_COUNT = p_iteration_num
5542
5539 WHERE pac_period_id = p_period_id
5540 AND cost_group_id = p_cost_group_id
5541 AND inventory_item_id = p_inventory_item_id;
5543 G_PWAC_NEW_COST_TBL.delete;
5544
5545 RAISE l_diverging_exception;
5546 ELSE
5547 -- Set current iteration PMAC Item Cost in interorg items temp table
5548 -- assigned to item_cost
5549 -- Set previous iteration PMAC Item Cost in interorg items temp table
5550 -- assigned to previous iteration item cost
5551 UPDATE CST_PAC_INTORG_ITMS_TEMP
5552 SET prev_itr_item_cost = l_prev_itr_item_cost
5553 ,item_cost = l_pwac_item_cost
5554 ,difference = l_new_difference
5555 WHERE pac_period_id = p_period_id
5556 AND cost_group_id = p_cost_group_id
5557 AND inventory_item_id = p_inventory_item_id;
5558 END IF;
5559 END IF; -- IF p_iteration_proc_flag = 'Y' AND p_end_iteration_num > 1 THEN
5560
5561
5562 -- initialize transaction category for interorg shipments across CGs
5563 -- as cost derived txns
5564 l_txn_category := 9;
5565 -- ===========================================================================
5566 -- Process Group 2 cost derived transactions
5567 -- The following update statement uses bind variables rather than PL/SQL
5568 -- function for performance reasons. PL/SQL Engine can give values to these
5569 -- bind variables before handing over to SQL Engine. With function, it'll have
5570 -- to switch for each record
5571 -- ===========================================================================
5572
5573 update mtl_pac_act_cst_dtl_temp mpacdt
5574 set mpacdt.actual_cost = (select (CASE mpacdt.level_type
5575 WHEN 1 THEN
5576 (CASE mpacdt.cost_element_id
5577 WHEN 1 THEN G_PWAC_NEW_COST_TBL(1)(1).final_new_cost
5578 WHEN 2 THEN G_PWAC_NEW_COST_TBL(2)(1).final_new_cost
5582 END)
5579 WHEN 3 THEN G_PWAC_NEW_COST_TBL(3)(1).final_new_cost
5580 WHEN 4 THEN G_PWAC_NEW_COST_TBL(4)(1).final_new_cost
5581 WHEN 5 THEN G_PWAC_NEW_COST_TBL(5)(1).final_new_cost
5583 WHEN 2 THEN
5584 (CASE mpacdt.cost_element_id
5585 WHEN 1 THEN G_PWAC_NEW_COST_TBL(1)(2).final_new_cost
5586 WHEN 2 THEN G_PWAC_NEW_COST_TBL(2)(2).final_new_cost
5587 WHEN 3 THEN G_PWAC_NEW_COST_TBL(3)(2).final_new_cost
5588 WHEN 4 THEN G_PWAC_NEW_COST_TBL(4)(2).final_new_cost
5589 WHEN 5 THEN G_PWAC_NEW_COST_TBL(5)(2).final_new_cost
5590 END)
5591 END) actual_cost
5592 from dual)
5593 where mpacdt.transaction_id in (select ccit.transaction_id
5594 FROM CST_PAC_INTERORG_TXNS_TMP ccit
5595 WHERE ccit.inventory_item_id = p_inventory_item_id
5596 AND ccit.cost_group_id = p_cost_group_id
5597 AND ccit.pac_period_id = p_period_id
5598 AND ccit.txn_type = 2)
5599 and mpacdt.pac_period_id = p_period_id
5600 and mpacdt.cost_group_id = p_cost_group_id
5601 and mpacdt.inventory_item_id = p_inventory_item_id;
5602
5603 -- ======================================================================
5604 -- Populate G_CG_PWAC_COST_TBL from G_PWAC_NEW_COST_TBL
5605 -- for each cost group, at each iteration when the iteration process flag
5606 -- is enabled
5607 -- ======================================================================
5608 IF p_iteration_proc_flag = 'Y' THEN
5609 l_cost_element_id := 1;
5610 WHILE l_cost_element_id <= 5 LOOP
5611 l_level_type := 1;
5612 WHILE l_level_type <= 2 LOOP
5613 l_cg_pwac_idx := to_char(p_cost_group_id) || to_char(l_cost_element_id) || to_char(l_level_type);
5614
5615 G_CG_PWAC_COST_TBL(l_cg_pwac_idx).final_new_cost := G_PWAC_NEW_COST_TBL(l_cost_element_id)(l_level_type).final_new_cost;
5616
5617 G_CG_PWAC_COST_TBL(l_cg_pwac_idx).period_new_balance := G_PWAC_NEW_COST_TBL(l_cost_element_id)(l_level_type).period_new_balance;
5618
5619 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5620 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
5621 , G_MODULE_HEAD || l_routine || '.fncpnb'
5622 , 'Cost Group Element Level idx:' || l_cg_pwac_idx || ' CG PWAC final new cost:' || G_CG_PWAC_COST_TBL(l_cg_pwac_idx).final_new_cost || ' CG PWAC period new balance:' || G_CG_PWAC_COST_TBL(l_cg_pwac_idx).period_new_balance
5623 );
5624 END IF;
5625 l_level_type := l_level_type + 1;
5626 END LOOP;
5627 l_cost_element_id := l_cost_element_id + 1;
5628 END LOOP;
5629
5630 END IF; -- iteration process flag check
5631
5632 -- Delete global pl/sql table G_PWAC_NEW_COST_TBL at each cost group
5633 G_PWAC_NEW_COST_TBL.delete;
5634
5635 EXCEPTION
5636 WHEN l_diverging_exception THEN
5637 -- Just a warning. No need to raise an exception. The process can continue for other cost groups for the item.
5638 l_cost_group_id_idx := p_cost_group_id;
5639 l_cost_group_name := CST_PAC_ITERATION_PROCESS_PVT.G_CST_GROUP_TBL(l_cost_group_id_idx).cost_group;
5640 FND_MESSAGE.Set_Name('BOM', 'CST_PAC_DIVERGE_WARNING');
5641 FND_MESSAGE.set_token('ITEM', p_inventory_item_number);
5642 FND_MESSAGE.set_token('COSTGROUP', l_cost_group_name);
5643 fnd_file.put_line(FND_FILE.OUTPUT, FND_MESSAGE.GET);
5644 WHEN FND_API.G_EXC_ERROR THEN
5645 RAISE FND_API.G_EXC_ERROR;
5646 WHEN OTHERS THEN
5647 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5648 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION
5649 , G_MODULE_HEAD || l_routine ||'.others_exc'
5650 , 'others:' || SQLCODE || substr(SQLERRM, 1,200)
5651 );
5652 END IF;
5653 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
5654 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
5655 FND_MESSAGE.set_token('MESSAGE', '('||SQLCODE||') '||SQLERRM);
5656 FND_MSG_PUB.Add;
5657 RAISE FND_API.G_EXC_ERROR;
5658 END Compute_Iterative_Pwac_Cost;
5659
5660 -- +========================================================================+
5661 -- PROCEDURE: Iteration_Process PRIVATE UTILITY
5662 -- PARAMETERS:
5663 -- p_init_msg_list IN VARCHAR2
5664 -- p_validation_level IN NUMBER
5665 -- x_return_status OUT VARCHAR2(1)
5666 -- x_msg_count OUT NUMBER
5667 -- x_msg_data OUT VARCHAR2(2000)
5668 -- p_legal_entity_id IN NUMBER
5669 -- p_cost_type_id IN NUMBER
5670 -- p_cost_method IN NUMBER
5671 -- p_iteration_proc_flag IN VARCHAR2(1)
5672 -- p_period_id IN NUMBER
5673 -- p_start_date IN DATE
5674 -- p_end_date IN DATE
5675 -- p_inventory_item_id IN NUMBER
5676 -- p_inventory_item_number IN VARCHAR2
5677 -- p_tolerance IN NUMBER
5678 -- p_iteration_num IN NUMBER
5679 -- p_run_options IN NUMBER
5680 -- p_pac_rates_id IN NUMBER PAC Rate Id for LE and Cost Type
5681 -- p_uom_control IN NUMBER Primary UOM Control Level
5682 -- p_user_id IN NUMBER
5683 -- p_login_id IN NUMBER
5684 -- p_req_id IN NUMBER
5685 -- p_prg_id IN NUMBER
5686 -- p_prg_appid IN NUMBER
5687 -- COMMENT:
5688 -- This procedure is called by the Interorg Transfer Cost Process worker
5689 -- after completing the necessary process in phase 7
5690 -- PRE-COND: none
5691 -- EXCEPTIONS: none
5692 -- +==========================================================================+
5693 PROCEDURE Iteration_Process
5694 ( p_init_msg_list IN VARCHAR2
5695 , p_validation_level IN NUMBER
5696 , p_legal_entity_id IN NUMBER
5697 , p_cost_type_id IN NUMBER
5698 , p_cost_method IN NUMBER
5699 , p_iteration_proc_flag IN VARCHAR2
5700 , p_period_id IN NUMBER
5701 , p_start_date IN DATE
5702 , p_end_date IN DATE
5703 , p_inventory_item_id IN NUMBER
5704 , p_inventory_item_number IN VARCHAR2
5705 , p_tolerance IN NUMBER
5706 , p_iteration_num IN NUMBER
5707 , p_run_options IN NUMBER
5708 , p_pac_rates_id IN NUMBER
5709 , p_uom_control IN NUMBER
5710 , p_user_id IN NUMBER
5711 , p_login_id IN NUMBER
5712 , p_req_id IN NUMBER
5713 , p_prg_id IN NUMBER
5714 , p_prg_appid IN NUMBER
5715 )
5716 IS
5717 l_routine CONSTANT VARCHAR2(30) := 'iteration_process';
5718
5719 -- Optimal Cost Group according to sequence number
5720 -- Diverging flag for a Cost Group is updated to Y when cost is diverging for the CG.
5721 CURSOR optimal_cost_group_cur(c_item_id NUMBER
5722 ,c_pac_period_id NUMBER
5723 )
5724 IS
5725 SELECT
5726 cost_group_id
5727 , interorg_receipt_flag
5728 , interorg_shipment_flag
5729 , low_level_code
5730 FROM cst_pac_intorg_itms_temp
5731 WHERE inventory_item_id = c_item_id
5732 AND pac_period_id = c_pac_period_id
5733 AND diverging_flag = 'N'
5734 ORDER BY sequence_num;
5735
5736
5737 -- =======================================================
5738 -- BUG 7674673 fix
5739 -- Cursor to obtain PCU value change period balance
5740 -- =======================================================
5741 CURSOR pcu_value_change_balance_cur(c_pac_period_id NUMBER
5742 ,c_cost_group_id NUMBER
5743 ,c_inventory_item_id NUMBER
5744 )
5745 IS
5746 SELECT NVL(cppb1.period_balance,0) - NVL(cppb2.period_balance,0) pcu_value_balance
5747 ,cppb1.cost_group_id cost_group_id
5748 ,cppb1.cost_element_id cost_element_id
5749 ,cppb1.level_type level_type
5750 FROM cst_pac_period_balances cppb1
5751 ,cst_pac_period_balances cppb2
5752 where cppb1.pac_period_id = cppb2.pac_period_id
5753 and cppb1.cost_group_id = cppb2.cost_group_id
5754 and cppb1.inventory_item_id = cppb2.inventory_item_id
5755 and cppb1.cost_element_id = cppb2.cost_element_id
5756 and cppb1.level_type = cppb2.level_type
5757 and cppb1.txn_category = 8.5
5758 and cppb2.txn_category = 8
5759 and cppb1.pac_period_id = c_pac_period_id
5760 AND cppb1.cost_group_id = c_cost_group_id
5761 AND cppb1.inventory_item_id = c_inventory_item_id;
5762
5763
5764 -- ==================================================
5765 -- BUG 7674673 fix
5766 -- To check existence of atleast one PCU value change
5767 -- txn for an interorg item
5768 -- ==================================================
5769 CURSOR pcu_value_change_check(c_pac_period_id NUMBER
5770 ,c_cost_group_id NUMBER
5771 ,c_inventory_item_id NUMBER
5772 )
5773 IS
5774 SELECT count(*)
5775 FROM CST_PAC_PERIOD_BALANCES
5776 WHERE pac_period_id = c_pac_period_id
5777 AND cost_group_id = c_cost_group_id
5778 AND inventory_item_id = c_inventory_item_id
5779 AND txn_category = 8.5;
5780
5781
5782 CURSOR c_low_level_code_cur(c_pac_period_id NUMBER, c_cost_group_id NUMBER, c_inventory_item_id NUMBER )
5783 IS
5784 SELECT low_level_code
5785 FROM cst_pac_low_level_codes
5786 WHERE pac_period_id = c_pac_period_id
5787 AND cost_group_id = c_cost_group_id
5788 AND inventory_item_id = c_inventory_item_id
5789 AND rownum = 1;
5790
5791
5792 -- ================================
5793 -- Local variables
5794 -- ===============================
5795 l_inventory_item_id NUMBER;
5796 l_tolerance_flag VARCHAR2(1);
5797 l_iteration_num NUMBER;
5798 t_low_level_code NUMBER;
5799
5800 -- Iteration Num range variables
5801 l_prev_iteration_count NUMBER;
5802 l_start_iteration_num NUMBER;
5803 l_end_iteration_num NUMBER;
5804 l_iteration_num_idx BINARY_INTEGER;
5805 l_master_org_id NUMBER;
5806
5807 -- Bug 7674673 fix
5808 l_pcu_value_change_count NUMBER := 0;
5809 l_cg_elmt_lv_idx BINARY_INTEGER;
5810 l_first_time_flag VARCHAR2(1);
5811
5812 -- Bug 12663454 fix
5813 l_error_num NUMBER;
5814 l_error_code VARCHAR2(240);
5815 l_error_msg VARCHAR2(240);
5816
5817 l_low_level_code_exist NUMBER;
5818
5819 BEGIN
5820 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5821 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
5822 , G_MODULE_HEAD || l_routine ||'.begin'
5823 , l_routine || '<'
5824 );
5825 END IF;
5826
5827 FND_MSG_PUB.initialize;
5828
5829 -- =======================
5830 -- API body
5831 -- =======================
5832
5833 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5834 FND_LOG.string(FND_LOG.LEVEL_EVENT
5835 , G_MODULE_HEAD || l_routine || '.before_set_process'
5836 , 'Before Set Process Status'
5837 );
5838 END IF;
5839
5840 -- Set process status to 2 - Running for all the valid cost groups
5841 -- in Legal Entity
5842 Set_Process_Status( p_legal_entity_id => p_legal_entity_id
5843 , p_period_id => p_period_id
5844 , p_period_end_date => p_end_date
5845 , p_phase_status => 2
5846 );
5850 , p_period_id => p_period_id
5847 -- Set Phase 5 status to 2 - Running for all the CGs to display
5848 -- the Phase 7 status on the screen
5849 Set_Phase5_Status( p_legal_entity_id => p_legal_entity_id
5851 , p_period_end_date => p_end_date
5852 , p_phase_status => 2
5853 );
5854
5855 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5856 FND_LOG.string(FND_LOG.LEVEL_EVENT
5857 , G_MODULE_HEAD || l_routine || '.Before_iteration_loop'
5858 , 'Before Iteration Loop'
5859 );
5860 END IF;
5861
5862 -- =========================================
5863 -- Assign interorg item to a local variable
5864 -- =========================================
5865 l_inventory_item_id := p_inventory_item_id;
5866
5867
5868 -- ==================================================================
5869 -- Get Previous Iteration Count
5870 -- initialize the starting iteration number for the current iteration
5871 -- process
5872 -- for the first iteration process, the starting iteration number
5873 -- will be initialize to 1 and the previous iteration count to 0
5874 -- previous iteration count will be -99 if no interorg items found
5875 -- Previous iteration count of the current bom level interorg item
5876 -- ==================================================================
5877 l_prev_iteration_count :=
5881
5878 Get_Previous_Iteration_Count(p_period_id => p_period_id
5879 ,p_inventory_item_id => l_inventory_item_id
5880 );
5882 -- ==================================================
5883 -- Perform interation only when Interorg items found
5884 -- ==================================================
5885 IF l_prev_iteration_count <> -99 THEN
5886 l_start_iteration_num := l_prev_iteration_count + 1;
5887
5888 IF p_iteration_proc_flag = 'Y' THEN
5889 l_end_iteration_num := l_prev_iteration_count + p_iteration_num;
5890 ELSE
5891 l_end_iteration_num := l_prev_iteration_count + 1;
5892 END IF;
5893
5894
5895 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5896 FND_LOG.string(FND_LOG.LEVEL_EVENT
5897 ,G_MODULE_HEAD || l_routine || '.iteration_num_range'
5898 ,'Starting Iteration Number:'|| l_start_iteration_num
5899 || ' Ending Iteration Number:' || l_end_iteration_num
5900 );
5901 END IF;
5902
5903 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5904 FND_LOG.string(FND_LOG.LEVEL_EVENT
5905 ,G_MODULE_HEAD || l_routine || '.Item'
5906 ,'Inventory Item Id:'|| p_inventory_item_id
5907 );
5908 END IF;
5909
5910 -- ==============================================================
5911 -- begin Bug 12663454 fix
5912 -- IPAC first iteration item cost calculation incorrect due to
5913 -- item cost never being calculated before the start of the first
5914 -- iteration. Therefore, the initial item costs used in the first
5915 -- iteration are 0, causing incorrect item costs after iteration.
5916 -- Added below for loop to calculate initial item cost for each
5917 -- cost group.
5918
5919 FOR l_optimal_cg_idx IN optimal_cost_group_cur(l_inventory_item_id
5920 ,p_period_id
5921 ) LOOP
5922 -- ============================================================
5923 -- check for no completion item in that cost group
5924 -- set the low level code variable to -1 for no completion item
5925 -- ============================================================
5926 IF l_optimal_cg_idx.low_level_code = 1000 THEN
5927 t_low_level_code := -1;
5928 OPEN c_low_level_code_cur(p_period_id, l_optimal_cg_idx.cost_group_id, l_inventory_item_id);
5929 FETCH c_low_level_code_cur INTO l_low_level_code_exist;
5930 IF c_low_level_code_cur%FOUND THEN
5931 t_low_level_code := l_optimal_cg_idx.low_level_code;
5932 END IF;
5933 CLOSE c_low_level_code_cur;
5934 ELSE
5935 t_low_level_code := l_optimal_cg_idx.low_level_code;
5936 END IF;
5937
5938
5939 CSTPPWAC.calculate_periodic_cost(
5940 i_pac_period_id => p_period_id,
5941 i_cost_group_id => l_optimal_cg_idx.cost_group_id,
5942 i_cost_type_id => p_cost_type_id,
5943 i_low_level_code => t_low_level_code,
5944 i_item_id => l_inventory_item_id,
5945 i_user_id => FND_GLOBAL.user_id,
5946 i_login_id => FND_GLOBAL.login_id,
5947 i_request_id => FND_GLOBAL.conc_request_id,
5948 i_prog_id => FND_GLOBAL.conc_program_id,
5949 i_prog_appl_id => FND_GLOBAL.prog_appl_id,
5950 o_err_num => l_error_num,
5951 o_err_code => l_error_code,
5952 o_err_msg => l_error_msg);
5953
5954 l_error_num := NVL(l_error_num,0);
5955 l_error_code := NVL(l_error_code, 'No Error');
5956 l_error_msg := NVL(l_error_msg, 'No Error');
5957
5958 IF l_error_num <> 0 THEN
5959 RAISE FND_API.G_EXC_ERROR;
5960 END IF;
5961
5962 END LOOP;
5963 -- end Bug 12663454 fix
5964 -- ==============================================================
5965
5966 -- ==================================================
5967 -- bug 7674673 fix PCU value change balance retrieval
5968 -- to pl/sql table G_PCU_VALUE_CHANGE_TBL
5969 -- only first time in the consecutive iterations
5970 -- Therefore, l_first_time_flag is set to Y
5971 -- ==================================================
5972 l_first_time_flag := 'Y';
5973
5974 -- ========================================================================
5975 -- Perform Item Iteration LOOP
5976 -- Item --> Iteration --> Optimal Seq cost Group --> interorg Transactions
5977 -- Item --> Iteration --> Invoke Compute_iterative_pwac_cost
5978 -- ========================================================================
5979
5980 FOR l_iteration_num_idx IN l_start_iteration_num .. l_end_iteration_num LOOP
5981
5982 -- Assign iteration number
5983 l_iteration_num := l_iteration_num_idx;
5984
5985 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5986 FND_LOG.string(FND_LOG.LEVEL_EVENT
5987 ,G_MODULE_HEAD || l_routine || '.iteration'
5988 ,'ITERATION NUMBER:'|| l_iteration_num
5989 );
5990 END IF;
5991
5992 -- Loop for each optimal cost group
5993 FOR l_optimal_cg_idx IN optimal_cost_group_cur(l_inventory_item_id
5994 ,p_period_id
5998 -- set the low level code variable to -1 for no completion item
5995 ) LOOP
5996 -- ============================================================
5997 -- check for no completion item in that cost group
5999 -- ============================================================
6000 IF l_optimal_cg_idx.low_level_code = 1000 THEN
6001 t_low_level_code := -1;
6002 OPEN c_low_level_code_cur(p_period_id, l_optimal_cg_idx.cost_group_id, l_inventory_item_id);
6003 FETCH c_low_level_code_cur INTO l_low_level_code_exist;
6004 IF c_low_level_code_cur%FOUND THEN
6005 t_low_level_code := l_optimal_cg_idx.low_level_code;
6006 END IF;
6007 CLOSE c_low_level_code_cur;
6008 ELSE
6009 t_low_level_code := l_optimal_cg_idx.low_level_code;
6010 END IF;
6011
6012 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
6013 FND_LOG.string(FND_LOG.LEVEL_EVENT
6014 ,G_MODULE_HEAD || l_routine || '.tlevel'
6015 ,'Completion or no completion level:' || t_low_level_code
6016 );
6017 END IF;
6018
6019 l_master_org_id := Get_master_org(l_optimal_cg_idx.cost_group_id);
6020
6021 IF l_iteration_num_idx = 1 THEN
6022 IF l_optimal_cg_idx.interorg_receipt_flag = 'Y' THEN
6023
6024 Group1_Interorg_Iteration1( p_legal_entity_id => p_legal_entity_id
6025 , p_master_org_id => l_master_org_id
6026 , p_cost_type_id => p_cost_type_id
6027 , p_cost_method => p_cost_method
6028 , p_cost_group_id => l_optimal_cg_idx.cost_group_id
6029 , p_inventory_item_id => l_inventory_item_id
6030 , p_low_level_code => t_low_level_code
6031 , p_period_id => p_period_id
6032 , p_pac_rates_id => p_pac_rates_id
6033 , p_uom_control => p_uom_control
6034 , p_end_iteration_num => l_end_iteration_num
6035 , p_iteration_proc_flag => p_iteration_proc_flag
6036 );
6037
6038 END IF;
6039
6040
6041 IF l_optimal_cg_idx.interorg_receipt_flag = 'Y' OR l_optimal_cg_idx.interorg_shipment_flag = 'Y' THEN
6042
6043 -- Process Periodic Cost Update Value Change only for interorg item
6044 -- both completion and no completion items are included
6045 CST_PERIODIC_ABSORPTION_PROC.Periodic_Cost_Update_By_Level
6046 (p_period_id => p_period_id
6047 ,p_legal_entity => p_legal_entity_id
6048 ,p_cost_type_id => p_cost_type_id
6049 ,p_cost_group_id => l_optimal_cg_idx.cost_group_id
6050 ,p_inventory_item_id => l_inventory_item_id
6051 ,p_cost_method => p_cost_method
6052 ,p_start_date => p_start_date
6053 ,p_end_date => p_end_date
6054 ,p_pac_rates_id => p_pac_rates_id
6055 ,p_master_org_id => l_master_org_id
6056 ,p_uom_control => p_uom_control
6057 ,p_low_level_code => t_low_level_code
6058 ,p_txn_category => 8.5
6059 ,p_user_id => p_user_id
6060 ,p_login_id => p_login_id
6061 ,p_req_id => p_req_id
6062 ,p_prg_id => p_prg_id
6063 ,p_prg_appid => p_prg_appid);
6064
6065
6066 -- Bug 7674673 fix
6067 -- Set PWAC Item Cost in interorg items temp table
6068 -- after PCU value change
6069 UPDATE CST_PAC_INTORG_ITMS_TEMP
6070 SET item_cost = (SELECT nvl(item_cost,0)
6071 FROM cst_pac_item_costs cpic
6072 WHERE cpic.pac_period_id = p_period_id
6073 AND cpic.cost_group_id = l_optimal_cg_idx.cost_group_id
6074 AND cpic.inventory_item_id = l_inventory_item_id)
6075 WHERE pac_period_id = p_period_id
6076 AND cost_group_id = l_optimal_cg_idx.cost_group_id
6077 AND inventory_item_id = l_inventory_item_id;
6078
6079
6080 END IF; -- PCU value change only for interorg item
6081
6082
6083 IF l_optimal_cg_idx.interorg_shipment_flag = 'Y' THEN
6087 , p_cost_method => p_cost_method
6084 Group2_Interorg_Iteration1( p_legal_entity_id => p_legal_entity_id
6085 , p_master_org_id => l_master_org_id
6086 , p_cost_type_id => p_cost_type_id
6088 , p_cost_group_id => l_optimal_cg_idx.cost_group_id
6089 , p_inventory_item_id => l_inventory_item_id
6090 , p_low_level_code => t_low_level_code
6091 , p_period_id => p_period_id
6092 , p_pac_rates_id => p_pac_rates_id
6093 , p_uom_control => p_uom_control
6094 , p_end_iteration_num => l_end_iteration_num
6095 , p_iteration_proc_flag => p_iteration_proc_flag
6096 );
6097 END IF;
6098
6099 ELSIF l_optimal_cg_idx.interorg_receipt_flag = 'Y' THEN
6100
6101 -- ======================================================
6102 -- Consecutive iteration logic
6103 -- Need to retrieve PCU value change balance first time
6104 -- in the first consecutive iteration. Not necessary to
6105 -- retrieve PCU value change balance for every iteration
6106 IF l_first_time_flag = 'Y' THEN
6107 -- ===========================================================
6108 -- BUG 7674673 fix
6109 -- Check for the existence of atleast one PCU value change txn
6110 -- If exists, store PCU value change balance in pl/sql table
6111 -- ===========================================================
6112 l_pcu_value_change_count := 0;
6113
6114 OPEN pcu_value_change_check(p_period_id
6115 ,l_optimal_cg_idx.cost_group_id
6116 ,l_inventory_item_id
6117 );
6118
6119 FETCH pcu_value_change_check
6120 INTO l_pcu_value_change_count;
6121
6122 CLOSE pcu_value_change_check;
6123
6124
6125 IF l_pcu_value_change_count > 0 THEN
6126 -- PCU value change txn exists
6127
6128 -- populate PCU value balance into pl/sql table
6129 FOR pcu_idx IN pcu_value_change_balance_cur(p_period_id
6130 ,l_optimal_cg_idx.cost_group_id
6131 ,l_inventory_item_id
6135 to_char(pcu_idx.cost_element_id) || to_char(pcu_idx.level_type);
6132 ) LOOP
6133
6134 l_cg_elmt_lv_idx := to_char(pcu_idx.cost_group_id) ||
6136
6137 G_PCU_VALUE_CHANGE_TBL(l_cg_elmt_lv_idx).pcu_value_balance := pcu_idx.pcu_value_balance;
6138 G_PCU_VALUE_CHANGE_TBL(l_cg_elmt_lv_idx).cost_group_id := pcu_idx.cost_group_id;
6139 G_PCU_VALUE_CHANGE_TBL(l_cg_elmt_lv_idx).cost_element_id := pcu_idx.cost_element_id;
6143 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
6140 G_PCU_VALUE_CHANGE_TBL(l_cg_elmt_lv_idx).level_type := pcu_idx.level_type;
6141
6142 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
6144 , G_MODULE_HEAD || l_routine ||'.cgellvic'
6145 , 'PCU Value Change :Cost Group element level idx:' || l_cg_elmt_lv_idx ||
6146 'Balance:' || G_PCU_VALUE_CHANGE_TBL(l_cg_elmt_lv_idx).pcu_value_balance
6147 );
6148 END IF;
6149
6150 END LOOP; -- pcu value change
6151
6152
6153 END IF; -- pcu value change existence check
6154
6155 END IF; -- first time check across cost groups
6156
6157
6158 -- Perform PWAC Calculation
6159 Compute_Iterative_Pwac_Cost(p_legal_entity_id => p_legal_entity_id
6160 ,p_cost_type_id => p_cost_type_id
6161 ,p_cost_method => p_cost_method
6162 ,p_cost_group_id => l_optimal_cg_idx.cost_group_id
6163 ,p_inventory_item_id => l_inventory_item_id
6164 ,p_inventory_item_number => p_inventory_item_number
6165 ,p_low_level_code => t_low_level_code
6166 ,p_period_id => p_period_id
6167 ,p_period_start_date => p_start_date
6168 ,p_period_end_date => p_end_date
6169 ,p_iteration_num => l_iteration_num
6170 ,p_end_iteration_num => l_end_iteration_num
6171 ,p_pac_rates_id => p_pac_rates_id
6172 ,p_uom_control => p_uom_control
6173 ,p_iteration_proc_flag => p_iteration_proc_flag
6174 );
6175 END IF;
6176
6177 END LOOP; -- end of optimal cost group
6178
6179 IF l_iteration_num > 1 THEN
6180 -- consecutive iteration
6181 -- PCU value change balance retrieved
6182 -- first time, so no need to retrieve
6183 -- for further iterations
6184 -- set l_first_time_flag to N
6185 l_first_time_flag := 'N';
6186 END IF;
6187
6188 -- ===================================================================
6189 -- Delete MPACD for the corresponding inserted pac transactions in
6190 -- mtl_pac_act_cst_dtl_temp of an interorg item
6191 -- Only if the iteration process flag is enabled and consecutive
6192 -- iterations exist
6193 -- ===================================================================
6194 IF l_iteration_num = 1 AND p_iteration_proc_flag = 'Y' THEN
6195
6196 DELETE FROM mtl_pac_actual_cost_details mpacd
6197 WHERE mpacd.pac_period_id = p_period_id
6198 AND EXISTS (select 'X'
6199 from cst_pac_intorg_itms_temp
6200 where cost_group_id = mpacd.cost_group_id
6201 and inventory_item_id = l_inventory_item_id
6202 and pac_period_id = p_period_id
6203 and interorg_receipt_flag = 'Y')
6204 AND transaction_id IN (
6205 SELECT transaction_id
6206 FROM mtl_pac_act_cst_dtl_temp
6207 WHERE pac_period_id = p_period_id
6208 AND inventory_item_id = l_inventory_item_id);
6209
6210
6211 -- ==================================================================
6212 -- R12 Enhancements: Iteration as an optional process
6213 -- balance pac txns only if the iteration process is enabled
6214 -- ====================================================================
6215 -- Balance pac transactions
6216 -- This is to determine whether the corresponding pac transaction rows
6217 -- exists for the cost element, level type
6218 -- Group 1 (cost owned) pac transactions:
6219 -- If the corresponding group 2 (cost dervied) transaction exists
6220 -- If it exists and cost element = 2 material overhead
6221 -- get material overhead absorption cost, transfer cost and
6222 -- transportation cost if any for this group 1 transaction
6223 -- Deduct these costs from actual cost
6224 -- Left over actual cost is used for comparision
6225 -- Deducted costs will be added back at the end of iterations
6226 -- If it exists and cost element is other than 2
6227 -- Retain the pac txn as it is
6228 -- If it does not exist in group 2 and cost element = 2,
6229 -- DO NOT USE this cost owned receipt for comparision
6230 -- Do NOT delete this record as this record will be put back
6231 -- into MPACD at the end of iteration process
6232 -- If it does not exist in group 2 and cost element <> 2,
6233 -- Delete this record from cost owned group 1 as it is not
6234 -- required
6235 -- Group 2 (cost derived) pac transactions:
6236 -- If the corresponding group 1 (cost owned transaction exists)
6237 -- If it exists retain as it
6238 -- If it does not exist, create the corresponding group1 pac txn
6239 -- only for direct interorg transaction otherwise retain as it is
6240 -- and DO NOT USE this intransit txn for the comparison
6241 -- ====================================================================
6242 Balance_Pac_Txn
6243 ( p_period_id => p_period_id
6244 , p_inventory_item_id => l_inventory_item_id
6245 , p_cost_type_id => p_cost_type_id
6246 );
6247
6248
6249 END IF; -- first iteration, process flag enabled with consecutive iterations
6250
6251
6252 IF p_iteration_proc_flag = 'Y' THEN
6253 -- =====================================================================
6254 -- Verify tolerance of inventory item
6258 -- tolerance flag to 'Y'. Otherwise, tolerance flag set to 'N'
6255 -- Check the tolerance for each cost group. Compare PMAC item cost of
6256 -- last iteration with the previous iteration
6257 -- If the tolerance achieved for all the cost groups, then set the
6259 -- Display interorg receipts, cost element, level type and the
6263 -- onwards
6260 -- corresponding interorg shipments only for the last iteration or when
6261 -- the tolerance is achieved
6262 -- NOTE: Tolerance verification is performed only from 2nd iteration
6264 -- =====================================================================
6265 IF l_iteration_num > 1 THEN
6266 Verify_Tolerance_Of_Item
6267 (p_cost_type_id => p_cost_type_id
6268 ,p_inventory_item_id => l_inventory_item_id
6269 ,p_inventory_item_number => p_inventory_item_number
6270 ,p_period_id => p_period_id
6271 ,p_period_start_date => p_start_date
6272 ,p_period_end_date => p_end_date
6273 ,p_tolerance => p_tolerance
6274 ,p_iteration_num => l_iteration_num
6275 ,p_end_iteration_num => l_end_iteration_num
6276 ,x_tolerance_flag => l_tolerance_flag
6277 );
6278 ELSE
6279 l_tolerance_flag := 'N';
6280 END IF;
6281
6282 -- =====================================================================
6283 -- Set Tolerance Flag in interorg temp table
6284 -- Update Tolerance Flag set to 'Y' only for the tolerance achieved items
6285 -- Run Option 1 - Start; 2 - Resume from error; 3 - Resume for non-
6286 -- tolerance; 4 - Final
6287 -- =====================================================================
6288 IF l_tolerance_flag = 'Y' THEN
6289
6290 UPDATE CST_PAC_INTORG_ITMS_TEMP
6291 SET tolerance_flag = l_tolerance_flag
6292 ,iteration_count = l_iteration_num
6293 WHERE inventory_item_id = l_inventory_item_id
6294 AND pac_period_id = p_period_id
6295 AND diverging_flag <> 'Y';
6296
6297
6301 , 'Tolerance Flag:' || l_tolerance_flag || ' ' ||
6298 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
6299 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
6300 , G_MODULE_HEAD || l_routine || '.upd_tolerance_flag'
6302 'Inventory Item Id:' || l_inventory_item_id
6303 );
6304 END IF;
6305
6306 IF l_iteration_num > 1 THEN
6307 -- Insert new values into Oracle Costing PAC transaction table only when the
6308 -- tolerance is achieved for the current BOM level interorg item
6309 -- Insert new values into MPACD from pac transaction temp table
6310 Create_Mpacd_With_New_Values(p_period_id
6311 ,l_inventory_item_id
6312 );
6313 Update_Cpicd_With_New_Values(p_pac_period_id => p_period_id
6314 ,p_inventory_item_id => l_inventory_item_id
6315 ,p_cost_type_id => p_cost_type_id
6316 ,p_end_date => p_end_date
6317 );
6318 END IF;
6319
6320 -- no more iterations for this item as tolerance achieved
6321 EXIT;
6322
6323 END IF; -- tolerance flag check
6324
6325 END IF; -- iteration process flag
6326
6327 END LOOP; -- iteration end loop
6328
6329 IF p_iteration_proc_flag = 'Y' THEN
6330
6331 -- Run Option: 4 Final
6332 IF p_run_options = 4 THEN
6333
6334 -- if tolerance is achieved in the final run, then the following steps would have been completed
6335 -- before exiting the above loop.
6336
6337 IF l_tolerance_flag <> 'Y' THEN
6338
6339 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
6340 FND_LOG.string(FND_LOG.LEVEL_EVENT
6341 , G_MODULE_HEAD || l_routine || '.upd_final_iteration'
6342 , 'Final Iteration for the item:' || l_inventory_item_id
6343 );
6344 END IF;
6345
6346 -- Update tolerance flag and final iteration number for this item
6347 -- Set flag to 'F' - Finalized
6348 UPDATE CST_PAC_INTORG_ITMS_TEMP
6349 SET tolerance_flag = 'F'
6350 ,iteration_count = l_end_iteration_num
6351 WHERE pac_period_id = p_period_id
6352 AND inventory_item_id = l_inventory_item_id
6353 AND tolerance_flag = 'N';
6354
6355 -- Insert new values into Oracle Costing PAC transaction table only when the
6356 -- tolerance is achieved for the current BOM level interorg item
6357 -- Insert new values into MPACD from pac transaction temp table
6358 Create_Mpacd_With_New_Values(p_period_id
6359 ,l_inventory_item_id
6360 );
6361
6362 Update_Cpicd_With_New_Values(p_pac_period_id => p_period_id
6363 ,p_inventory_item_id => l_inventory_item_id
6364 ,p_cost_type_id => p_cost_type_id
6365 ,p_end_date => p_end_date
6366 );
6367 END IF;
6368
6369 ELSE
6370 -- Run Options: 1 - Start, 2 - Resume from error, 3 - Resume for
6371 -- non tolerance
6372 -- ===========================================================
6373 -- Update iteration count for the interorg item
6374 -- it is required to store the iteration count which
6375 -- is mainly used in the next iteration process
6376 -- ===========================================================
6377 UPDATE CST_PAC_INTORG_ITMS_TEMP
6378 SET iteration_count = l_end_iteration_num
6379 WHERE pac_period_id = p_period_id
6380 AND tolerance_flag = 'N'
6381 AND inventory_item_id = l_inventory_item_id
6382 AND diverging_flag <> 'Y';
6383
6384 END IF; -- run option check
6385
6386
6387 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
6388 FND_LOG.string(FND_LOG.LEVEL_EVENT
6389 , G_MODULE_HEAD || l_routine || '.After_iteration_loop'
6390 , 'After Iteration Loop'
6391 );
6392 END IF;
6393
6394 END IF; -- iteration process check
6395
6396 -- Delete G_CG_PWAC_COST_TBL after iteration process for an interorg item
6397 G_CG_PWAC_COST_TBL.delete;
6398
6399 -- Delete G_PCU_VALUE_CHANGE_TBL at the end of iteration process for an interorg item
6400 G_PCU_VALUE_CHANGE_TBL.delete;
6401
6402 END IF; -- interorg items found check
6403
6404 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
6408 );
6405 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
6406 , G_MODULE_HEAD || l_routine ||'.end'
6407 , l_routine || '>'
6409 END IF;
6410
6411 EXCEPTION
6412 WHEN FND_API.G_EXC_ERROR THEN
6413 RAISE FND_API.G_EXC_ERROR;
6414 WHEN OTHERS THEN
6415 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
6416 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
6417 FND_MESSAGE.set_token('MESSAGE', '('||SQLCODE||') '||SQLERRM);
6418 FND_MSG_PUB.Add;
6419 RAISE FND_API.G_EXC_ERROR;
6420 END Iteration_Process;
6421
6422 END CST_PAC_ITERATION_PROCESS_PVT;