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