[Home] [Help]
PACKAGE BODY: APPS.CST_PERIODIC_ABSORPTION_PROC
Source
1 PACKAGE BODY CST_PERIODIC_ABSORPTION_PROC AS
2 -- $Header: CSTRITPB.pls 120.62.12020000.5 2013/04/05 12:12:14 pbasrani ship $
3 --+=======================================================================+
4 --| Copyright (c) 2003 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| CSTRITPB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Periodic Absorption Cost Processor Concurrent Program |
13 --| |
14 --| 10/30/2008 vjavli FP 12.1.1 7342514 fix: Periodic_Cost_Update_By_Level|
15 --| for PCU - value change will be invoked after |
16 --| processing all the cost owned transactions,just |
17 --| before processing cost derived transactions |
18 --| Procedure Periodic_Cost_Update_By_Level is |
19 --| is invoked for PCU value change for non interorg |
20 --| items which include for both completion and no |
21 --| completion items |
22 --| Iteration_Process signature changed with who columns|
23 --| 04/26/2008 vjavli FP Bug 7674673 fix:When interorg_item_flag is 1 |
24 --| atleast one of the cost group has valid interorg txn |
25 --| which will get processed in iteration_process proc. |
26 --| It is possible that remaining cost groups for which |
27 --| no interorg txns exists may have to be processed with|
28 --| PCU - value change txns,if any even though interorg_ |
29 --| item_flag is 1 across the cost groups in pac period |
30 --| This means, remaining cost groups with no interorg |
31 --| txns have to be considered to process PCU Value Chng |
32 --| Function Check_For_No_Interorg_CG to validate for non|
33 --| interorg cost group |
34 --+=======================================================================+
35
36 --===================
37 -- GLOBALS
38 --===================
39
40 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CST_PERIODIC_ABSORPTION_PROC';
41 g_org_id NUMBER := FND_PROFILE.value('ORG_ID');
42 -- to store the item and its BOM highest level across cost groups
43 TYPE item_level_rec_type IS RECORD
44 ( inventory_item_id NUMBER
45 );
46
47 TYPE g_item_level_table_type IS TABLE OF item_level_rec_type
48 INDEX BY BINARY_INTEGER;
49 G_ITEM_LEVEL_TBL g_item_level_table_type;
50
51 TYPE PAC_REQUEST_REC IS RECORD
52 ( pac_period_id NUMBER,
53 cost_group_id NUMBER,
54 request_id NUMBER,
55 request_status VARCHAR2(1),
56 phase_status NUMBER
57 );
58
59
60 TYPE PAC_REQUEST_TABLE IS TABLE OF PAC_REQUEST_REC
61 INDEX BY BINARY_INTEGER;
62
63 G_REQUEST_TABLE PAC_REQUEST_TABLE;
64
65 --========================================================================
66 -- PRIVATE CONSTANTS AND VARIABLES
67 --========================================================================
68 G_MODULE_HEAD CONSTANT VARCHAR2(50) := 'cst.plsql.' || G_PKG_NAME || '.';
69 G_TOL_ACHIEVED_FORALL_CG NUMBER := 0;
70 --===========================================================
71 -- PUBLIC FUNCTIONS
72 --===========================================================
73
74
75 --========================================================================
76 -- PROCEDURE : Get Exp Flag PRIVATE
77 -- COMMENT : get exp flag for items considered to be an asset
78 --=========================================================================
79 PROCEDURE get_exp_flag
80 (p_item_id IN NUMBER
81 ,p_org_id IN NUMBER
82 ,p_subinventory_code IN VARCHAR2
83 ,x_exp_flag OUT NOCOPY NUMBER
84 ,x_exp_item OUT NOCOPY NUMBER
85 )
86 IS
87
88 l_routine CONSTANT VARCHAR2(30) := 'get_exp_flag';
89 --=================
90 -- VARIABLES
91 --=================
92
93 l_exp_flag NUMBER;
94 l_exp_item NUMBER;
95
96 BEGIN
97
98 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
99 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
100 ,G_MODULE_HEAD || l_routine || '.begin'
101 ,l_routine || '<'
102 );
103 END IF;
104
105 SELECT DECODE(inventory_asset_flag,'Y',0,1)
106 INTO l_exp_item
107 FROM mtl_system_items
108 WHERE inventory_item_id = p_item_id
109 AND organization_id = p_org_id;
110
111 IF p_subinventory_code IS NULL THEN
112
113 l_exp_flag := l_exp_item;
114
115 ELSE
116
117 SELECT DECODE(l_exp_item,1,1,DECODE(asset_inventory,1,0,1))
118 INTO l_exp_flag
119 FROM mtl_secondary_inventories
120 WHERE secondary_inventory_name = p_subinventory_code
121 AND organization_id = p_org_id;
122
123 END IF;
124
125 x_exp_item := l_exp_item;
126 x_exp_flag := l_exp_flag;
127
128 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
129 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
130 ,G_MODULE_HEAD || l_routine || '.end'
131 ,l_routine || '>'
132 );
133 END IF;
134
135 END get_exp_flag;
136
137
138 -- +========================================================================+
139 -- FUNCTION: Get_Item_Number Local Utility
140 -- PARAMETERS:
141 -- p_inventory_item_id Inventory Item Id
142 -- COMMENT:
143 -- This is to get the Inventory Item Number
144 -- USAGE: This function is used in Absorption_Cost_Process
145 -- PRE-COND: none
146 -- EXCEPTIONS: none
147 -- +========================================================================+
148 FUNCTION Get_Item_Number
149 ( p_inventory_item_id IN NUMBER
150 )
151 RETURN VARCHAR2
152 IS
153 -- Cursor to get the inventory item number
154 CURSOR item_cur(c_inventory_item_id NUMBER)
155 IS
156 SELECT
157 concatenated_segments
158 FROM
159 MTL_SYSTEM_ITEMS_B_KFV
160 WHERE inventory_item_id = c_inventory_item_id
161 AND rownum = 1;
162
163 l_inventory_item_number VARCHAR2(1025);
164
165 BEGIN
166 OPEN item_cur(p_inventory_item_id);
167 FETCH item_cur
168 INTO l_inventory_item_number;
169
170 CLOSE item_cur;
171
172 RETURN l_inventory_item_number;
173
174 END; -- Get_Item_Number
175
176 --===================
177 -- PRIVATE PROCEDURES
178 --===================
179
180 --========================================================================
181 -- PROCEDURE : Get Phase Status PRIVATE
182 -- COMMENT : Get the status of a specific phase
183 --========================================================================
184 PROCEDURE get_phase_status
185 ( p_pac_period_id IN NUMBER
186 , p_phase IN NUMBER
187 , p_cost_group_id IN NUMBER
188 , x_status OUT NOCOPY NUMBER
189 )
190 IS
191
192 l_routine CONSTANT VARCHAR2(30) := 'get_phase_status';
193
194 BEGIN
195
196 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
197 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
198 ,G_MODULE_HEAD || l_routine || '.begin'
199 ,l_routine || '<'
200 );
201 END IF;
202
203 IF p_phase = 7 THEN
204 SELECT
205 process_status
206 INTO x_status
207 FROM
208 cst_pac_process_phases
209 WHERE pac_period_id = p_pac_period_id
210 AND process_phase = p_phase
211 AND rownum = 1;
212 ELSE
213 SELECT
214 process_status
215 INTO x_status
216 FROM
217 cst_pac_process_phases
218 WHERE pac_period_id = p_pac_period_id
219 AND process_phase = p_phase
220 AND cost_group_id = p_cost_group_id;
221 END IF;
222
223 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
224 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
225 ,G_MODULE_HEAD || l_routine || '.end'
226 ,l_routine || '>'
227 );
228 END IF;
229
230 EXCEPTION
231 WHEN FND_API.G_EXC_ERROR THEN
232 RAISE FND_API.G_EXC_ERROR;
233 WHEN OTHERS THEN
234 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
235 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
236 FND_MESSAGE.set_token('MESSAGE', '('||SQLCODE||') '||SQLERRM);
237 FND_MSG_PUB.Add;
238 RAISE FND_API.G_EXC_ERROR;
239 END get_phase_status;
240
241 -- =========================================================================
242 -- FUNCTION Find_Prev_Process_Upto_Date PRIVATE
243 -- PARAMETERS: p_pac_period_id IN NUMBER
244 -- RETURN x_process_upto_date OUT NOCOPY DATE
245 -- This function retrieves Process Upto Date used in first iteration
246 -- Note that process upto date is stored in CST_PAC_PROCESS_PHASES.
247 -- This function is invoked only during consecutive iterations
248 -- =========================================================================
249 FUNCTION Find_Prev_Process_Upto_Date
250 (p_pac_period_id IN NUMBER)
251 RETURN DATE
252 IS
253 l_routine CONSTANT VARCHAR2(30) := 'Find_Prev_Process_Upto_Date';
254
255 -- Cursor to obtain process upto date for a given pac period
256 -- NOTE: process upto date is same for all valid cost groups in the
257 -- legal entity for Phase 7
258 CURSOR process_upto_date_cur(c_pac_period_id NUMBER)
259 IS
260 SELECT
261 TO_CHAR(process_upto_date, 'YYYY/MM/DD HH24:MI:SS')
262 FROM
263 cst_pac_process_phases
264 WHERE pac_period_id = c_pac_period_id
265 AND process_phase = 7;
266
267 -- variables for process upto date
268 l_process_upto_date VARCHAR2(30);
269 x_process_upto_date DATE;
270
271 BEGIN
272
273 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
274 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
275 ,G_MODULE_HEAD || l_routine || '.begin'
276 ,l_routine || '<'
277 );
278 END IF;
279
280
281 OPEN process_upto_date_cur(p_pac_period_id);
282 FETCH process_upto_date_cur
283 INTO l_process_upto_date;
284
285 CLOSE process_upto_date_cur;
286
287 x_process_upto_date :=
288 TRUNC(FND_DATE.canonical_to_date(l_process_upto_date)) + (86399/86400);
289
290 RETURN x_process_upto_date;
291
292 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
293 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
294 ,G_MODULE_HEAD || l_routine || '.end'
295 ,l_routine || '>'
296 );
297 END IF;
298
299 END Find_Prev_Process_Upto_Date;
300
301 --========================================================================
302 -- PROCEDURE : Process cost own completion item PRIVATE
303 -- COMMENT : Run the cost processor for non rework assembly jobs
304 --=========================================================================
305 PROCEDURE Process_Non_Rework_Comps
306 ( p_period_id IN NUMBER
307 , p_start_date IN DATE
308 , p_end_date IN DATE
309 , p_prev_period_id IN NUMBER
310 , p_cost_group_id IN NUMBER
311 , p_inventory_item_id IN NUMBER
312 , p_cost_type_id IN NUMBER
313 , p_legal_entity IN NUMBER
314 , p_cost_method IN NUMBER
315 , p_pac_rates_id IN NUMBER
316 , p_master_org_id IN NUMBER
317 , p_mat_relief_algorithm IN NUMBER
318 , p_uom_control IN NUMBER
319 , p_low_level_code IN NUMBER
320 , p_user_id IN NUMBER
321 , p_login_id IN NUMBER
322 , p_req_id IN NUMBER
323 , p_prg_id IN NUMBER
324 , p_prg_appid IN NUMBER
325 )
326 IS
327
328 l_routine CONSTANT VARCHAR2(30) := 'process_non_rework_comps';
329
330 --=================
331 -- VARIABLES
332 --=================
333
334 l_error_num NUMBER;
335 l_error_code VARCHAR2(240);
336 l_error_msg VARCHAR2(240);
337
338 BEGIN
339
340
341 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
342 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
343 ,G_MODULE_HEAD || l_routine || '.begin'
344 ,l_routine || '<'
345 );
346 END IF;
347
348
349 CSTPPWAS.process_nonreworkassembly_txns
350 (p_pac_period_id => p_period_id
351 ,p_start_date => p_start_date
352 ,p_end_date => p_end_date
353 ,p_prior_period_id => p_prev_period_id
354 ,p_item_id => p_inventory_item_id
355 ,p_cost_group_id => p_cost_group_id
356 ,p_cost_type_id => p_cost_type_id
357 ,p_legal_entity => p_legal_entity
358 ,p_cost_method => p_cost_method
359 ,p_pac_rates_id => p_pac_rates_id
360 ,p_master_org_id => p_master_org_id
361 ,p_material_relief_algorithm => p_mat_relief_algorithm
362 ,p_uom_control => p_uom_control
363 ,p_low_level_code => p_low_level_code
364 ,p_user_id => p_user_id
365 ,p_login_id => p_login_id
366 ,p_request_id => p_req_id
367 ,p_prog_id => p_prg_id
368 ,p_prog_app_id => p_prg_appid
369 ,x_err_num => l_error_num
370 ,x_err_code => l_error_code
371 ,x_err_msg => l_error_msg);
372
373 l_error_num := NVL(l_error_num, 0);
374 l_error_code := NVL(l_error_code, 'No Error');
375 l_error_msg := NVL(l_error_msg, 'No Error');
376
377 IF l_error_num <> 0
378 THEN
379 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
380 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION
381 , G_MODULE_HEAD || l_routine || '.others'
382 , 'process_nonreworkassembly_txns for cost group '||p_cost_group_id||' item id '
383 ||p_inventory_item_id||' ('||l_error_code||') '||l_error_msg
384 );
385 END IF;
386
387 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
388 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
389 FND_MESSAGE.set_token('MESSAGE', 'process_nonreworkassembly_txns for cost group '||p_cost_group_id||' item id '
390 ||p_inventory_item_id||' ('||l_error_code||') '||l_error_msg);
391 FND_MSG_PUB.Add;
392 RAISE FND_API.G_EXC_ERROR;
393
394 END IF;
395
396
397 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
398 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
399 ,G_MODULE_HEAD || l_routine || '.end'
400 ,l_routine || '>'
401 );
402 END IF;
403
404
405 END Process_Non_Rework_Comps;
406
407
408 --=================================================================================
409 -- PROCEDURE : Process cost own completion item PRIVATE
410 -- COMMENT : Run the cost processor for rework assembly issue and completion jobs
411 --=================================================================================
412 PROCEDURE Process_Rework_Issue_Comps
413 ( p_period_id IN NUMBER
414 , p_start_date IN DATE
415 , p_end_date IN DATE
416 , p_prev_period_id IN NUMBER
417 , p_cost_group_id IN NUMBER
418 , p_inventory_item_id IN NUMBER
419 , p_cost_type_id IN NUMBER
420 , p_legal_entity IN NUMBER
421 , p_cost_method IN NUMBER
422 , p_pac_rates_id IN NUMBER
423 , p_master_org_id IN NUMBER
424 , p_mat_relief_algorithm IN NUMBER
425 , p_uom_control IN NUMBER
426 , p_low_level_code IN NUMBER
427 , p_user_id IN NUMBER
428 , p_login_id IN NUMBER
429 , p_req_id IN NUMBER
430 , p_prg_id IN NUMBER
431 , p_prg_appid IN NUMBER
432 )
433 IS
434
435 l_routine CONSTANT VARCHAR2(30) := 'process_rework_issue_comps';
436
437 --=================
438 -- VARIABLES
439 --=================
440
441 l_error_num NUMBER;
442 l_error_code VARCHAR2(240);
443 l_error_msg VARCHAR2(240);
444
445 BEGIN
446
447
448 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
449 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
450 ,G_MODULE_HEAD || l_routine || '.begin'
451 ,l_routine || '<'
452 );
453 END IF;
454
455
456 CSTPPWAS.process_reworkassembly_txns
457 (p_pac_period_id => p_period_id
458 ,p_start_date => p_start_date
459 ,p_end_date => p_end_date
460 ,p_prior_period_id => p_prev_period_id
461 ,p_item_id => p_inventory_item_id
462 ,p_cost_group_id => p_cost_group_id
463 ,p_cost_type_id => p_cost_type_id
464 ,p_legal_entity => p_legal_entity
465 ,p_cost_method => p_cost_method
466 ,p_pac_rates_id => p_pac_rates_id
467 ,p_master_org_id => p_master_org_id
468 ,p_material_relief_algorithm => p_mat_relief_algorithm
469 ,p_uom_control => p_uom_control
470 ,p_low_level_code => p_low_level_code
471 ,p_user_id => p_user_id
472 ,p_login_id => p_login_id
473 ,p_request_id => p_req_id
474 ,p_prog_id => p_prg_id
475 ,p_prog_app_id => p_prg_appid
476 ,x_err_num => l_error_num
477 ,x_err_code => l_error_code
478 ,x_err_msg => l_error_msg);
479
480 l_error_num := NVL(l_error_num, 0);
481 l_error_code := NVL(l_error_code, 'No Error');
482 l_error_msg := NVL(l_error_msg, 'No Error');
483
484 IF l_error_num <> 0
485 THEN
486 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
487 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
488 FND_MESSAGE.set_token('MESSAGE', 'CSTPPWAS.process_reworkassembly_txns for cost group '||p_cost_group_id||' item id '
489 ||p_inventory_item_id||' ('||l_error_code||') '||l_error_msg);
490 FND_MSG_PUB.Add;
491 RAISE FND_API.G_EXC_ERROR;
492
493 END IF;
494
495
496 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
497 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
498 ,G_MODULE_HEAD || l_routine || '.end'
499 ,l_routine || '>'
500 );
501 END IF;
502
503
504 END Process_Rework_Issue_Comps;
505
506 --========================================================================
507 -- PROCEDURE : Periodic Cost Update by level PRIVATE
508 -- COMMENT : Run the cost processor for modes
509 -- : periodic cost update (value change)
510 --=========================================================================
511 PROCEDURE Periodic_Cost_Update_By_Level
512 ( p_period_id IN NUMBER
513 , p_legal_entity IN NUMBER
514 , p_cost_type_id IN NUMBER
515 , p_cost_group_id IN NUMBER
516 , p_inventory_item_id IN NUMBER
517 , p_cost_method IN NUMBER
518 , p_start_date IN DATE
519 , p_end_date IN DATE
520 , p_pac_rates_id IN NUMBER
521 , p_master_org_id IN NUMBER
522 , p_uom_control IN NUMBER
523 , p_low_level_code IN NUMBER
524 , p_txn_category IN NUMBER
525 , p_user_id IN NUMBER
526 , p_login_id IN NUMBER
527 , p_req_id IN NUMBER
528 , p_prg_id IN NUMBER
529 , p_prg_appid IN NUMBER
530 )
531 IS
532
533 l_routine CONSTANT VARCHAR2(30) := 'periodic_cost_update_by_level';
534
535 --===============================================================
536 -- Cursor for Periodic Cost Update for items in the current level
537 --===============================================================
538 CURSOR upd_val_csr_type_level
539 ( c_start_date DATE
540 , c_end_date DATE
541 , c_cost_group_id NUMBER
542 , c_cost_type_id NUMBER
543 , c_inventory_item_id NUMBER
544 )
545 IS
546 SELECT
547 mmt.transaction_id
548 , mmt.transaction_action_id
549 , mmt.transaction_source_type_id
550 , mmt.inventory_item_id
551 , mmt.primary_quantity
552 , mmt.organization_id
553 , nvl(mmt.transfer_organization_id,-1) transfer_organization_id
554 , mmt.subinventory_code
555 FROM mtl_material_transactions mmt
556 WHERE mmt.transaction_date BETWEEN c_start_date AND c_end_date
557 AND mmt.transaction_action_id = 24
558 AND mmt.transaction_source_type_id = 14
559 AND mmt.transaction_type_id = 26/*Added for bug 16056585*/
560 AND value_change IS NOT NULL
561 AND mmt.primary_quantity = 0
562 AND NVL(org_cost_group_id,-1) = c_cost_group_id
563 AND NVL(cost_type_id,-1) = c_cost_type_id
564 AND mmt.inventory_item_id = c_inventory_item_id;
565
566 TYPE upd_val_txn_tab IS TABLE OF upd_val_csr_type_level%rowtype INDEX BY BINARY_INTEGER;
567 l_upd_val_txn_tab upd_val_txn_tab;
568 l_empty_txn_tab upd_val_txn_tab;
569 --=================
570 -- VARIABLES
571 --=================
572
573 l_current_index BINARY_INTEGER := 0;
574 l_error_num NUMBER;
575 l_error_code VARCHAR2(240);
576 l_error_msg VARCHAR2(240);
577 l_process_group NUMBER := 0;
578 l_count NUMBER;
579 l_exp_flag NUMBER;
580 l_exp_item NUMBER;
581
582 -- Transaction Category
583 l_batch_size NUMBER := 200;
584 l_loop_count NUMBER := 0;
585
586 BEGIN
587
588 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
589 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
590 ,G_MODULE_HEAD || l_routine || '.begin'
591 ,l_routine || '<'
592 );
593 END IF;
594
595
596 IF NOT upd_val_csr_type_level%ISOPEN THEN
597 OPEN upd_val_csr_type_level(p_start_date
598 ,p_end_date
599 ,p_cost_group_id
600 ,p_cost_type_id
601 ,p_inventory_item_id
602 );
603 END IF;
604
605 LOOP
606 l_upd_val_txn_tab := l_empty_txn_tab;
607 FETCH upd_val_csr_type_level BULK COLLECT INTO l_upd_val_txn_tab LIMIT l_batch_size;
608
609 l_loop_count := l_upd_val_txn_tab.count;
610
611 FOR i IN 1..l_loop_count
612 LOOP
613 -- insert into cppb
614 l_error_num := 0;
615
616 IF (CSTPPINV.l_item_id_tbl.COUNT >= 1000) THEN
617 CSTPPWAC.insert_into_cppb(i_pac_period_id => p_period_id
618 ,i_cost_group_id => p_cost_group_id
619 ,i_txn_category => p_txn_category
620 ,i_user_id => p_user_id
621 ,i_login_id => p_login_id
622 ,i_request_id => p_req_id
623 ,i_prog_id => p_prg_id
624 ,i_prog_appl_id => p_prg_appid
625 ,o_err_num => l_error_num
626 ,o_err_code => l_error_code
627 ,o_err_msg => l_error_msg
628 );
629 l_error_num := NVL(l_error_num, 0);
630 l_error_code := NVL(l_error_code, 'No Error');
631 l_error_msg := NVL(l_error_msg, 'No Error');
632
633 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
634 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
635 ,G_MODULE_HEAD || l_routine || '.inscppb5'
636 ,'After calling insert_into_cppb:'|| l_error_num || l_error_code || l_error_msg
637 );
638 END IF;
639 END IF; -- item table count check
640
641 IF l_error_num = 0 THEN
642
643 CSTPPINV.cost_inv_txn
644 (i_pac_period_id => p_period_id
645 ,i_legal_entity => p_legal_entity
646 ,i_cost_type_id => p_cost_type_id
647 ,i_cost_group_id => p_cost_group_id
648 ,i_cost_method => p_cost_method
649 ,i_txn_id => l_upd_val_txn_tab(i).transaction_id
650 ,i_txn_action_id => l_upd_val_txn_tab(i).transaction_action_id
651 ,i_txn_src_type_id => l_upd_val_txn_tab(i).transaction_source_type_id
652 ,i_item_id => l_upd_val_txn_tab(i).inventory_item_id
653 ,i_txn_qty => l_upd_val_txn_tab(i).primary_quantity
654 ,i_txn_org_id => l_upd_val_txn_tab(i).organization_id
655 ,i_txfr_org_id => l_upd_val_txn_tab(i).transfer_organization_id
656 ,i_subinventory_code => l_upd_val_txn_tab(i).subinventory_code
657 ,i_exp_flag => l_exp_flag
658 ,i_exp_item => l_exp_item
659 ,i_pac_rates_id => p_pac_rates_id
660 ,i_process_group => l_process_group
661 ,i_master_org_id => p_master_org_id
662 ,i_uom_control => p_uom_control
663 ,i_user_id => p_user_id
664 ,i_login_id => p_login_id
665 ,i_request_id => p_req_id
666 ,i_prog_id => p_prg_id
667 ,i_prog_appl_id => p_prg_appid
668 ,i_txn_category => p_txn_category
669 ,i_transfer_price_pd => 0
670 ,o_err_num => l_error_num
671 ,o_err_code => l_error_code
672 ,o_err_msg => l_error_msg);
673
674 l_error_num := NVL(l_error_num, 0);
675 l_error_code := NVL(l_error_code, 'No Error');
676 l_error_msg := NVL(l_error_msg, 'No Error');
677
678 END IF; -- error number check
679
680 IF l_error_num <> 0 THEN
681 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
682 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION
683 , G_MODULE_HEAD || l_routine || '.others'
684 , 'cost_inv_txn for cost group '||p_cost_group_id||' txn id '
685 ||l_upd_val_txn_tab(i).transaction_id||' ('||l_error_code||') '||l_error_msg
686 );
687 END IF;
688
689 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
690 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
691 FND_MESSAGE.set_token('MESSAGE', 'cost_inv_txn for cost group '||p_cost_group_id||' txn id '
692 ||l_upd_val_txn_tab(i).transaction_id||' ('||l_error_code||') '||l_error_msg);
693 FND_MSG_PUB.Add;
694 RAISE FND_API.G_EXC_ERROR;
695 END IF;
696 END LOOP; -- FOR i IN 1..l_loop_count
697
698 EXIT WHEN upd_val_csr_type_level%NOTFOUND;
699 END LOOP; -- FETCH loop
700 CLOSE upd_val_csr_type_level;
701
702 -- =============================================================
703 -- insert left over cost PCU value change transactions into cppb
704 -- txn_category is either 5 or 8.5
705 -- =============================================================
706 l_error_num := 0;
707
708 IF (CSTPPINV.l_item_id_tbl.COUNT > 0) THEN
709 CSTPPWAC.insert_into_cppb(i_pac_period_id => p_period_id
710 ,i_cost_group_id => p_cost_group_id
711 ,i_txn_category => p_txn_category
712 ,i_user_id => p_user_id
713 ,i_login_id => p_login_id
714 ,i_request_id => p_req_id
715 ,i_prog_id => p_prg_id
716 ,i_prog_appl_id => p_prg_appid
717 ,o_err_num => l_error_num
718 ,o_err_code => l_error_code
719 ,o_err_msg => l_error_msg
720 );
721
722 l_error_num := NVL(l_error_num, 0);
723 l_error_code := NVL(l_error_code, 'No Error');
724 l_error_msg := NVL(l_error_msg, 'No Error');
725
726 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
727 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
728 ,G_MODULE_HEAD || l_routine || '.inscppb6'
729 ,'After calling insert_into_cppb:'|| l_error_num || l_error_code || l_error_msg
730 );
731 END IF;
732
733 END IF;
734
735 -- Calculate Periodic Item Cost and Variance
736 IF l_error_num = 0 THEN
737 CSTPPWAC.calculate_periodic_cost(i_pac_period_id => p_period_id
738 ,i_cost_group_id => p_cost_group_id
739 ,i_cost_type_id => p_cost_type_id
740 ,i_low_level_code => p_low_level_code
741 ,i_item_id => p_inventory_item_id
742 ,i_user_id => p_user_id
743 ,i_login_id => p_login_id
744 ,i_request_id => p_req_id
745 ,i_prog_id => p_prg_id
746 ,i_prog_appl_id => p_prg_appid
747 ,o_err_num => l_error_num
748 ,o_err_code => l_error_code
749 ,o_err_msg => l_error_msg
750 );
751
752 l_error_num := NVL(l_error_num, 0);
753 l_error_code := NVL(l_error_code, 'No Error');
754 l_error_msg := NVL(l_error_msg, 'No Error');
755 END IF;
756
757 -- ==============================================================
758 -- BUG 8547715 fix: Update CPPB with period_balnce, item_cost
759 -- variance_amount for each transaction category
760 -- variance_amount is obtained from the last transaction of txn_category
761 -- txn_category 5 for non-interorg item cost groups invoked outside of
762 -- iteration logic.
763 -- txn_category 8.5 for interorg item cost groups invoked thru
764 -- iteration process
765 -- item-cost group belongs to either txn_category 5 OR 8.5
766 -- ===============================================================
767
768 -- PCU value change with primary qty 0
769
770 IF l_error_num = 0 THEN
771 CSTPPWAC.update_item_cppb(i_pac_period_id => p_period_id
772 ,i_cost_group_id => p_cost_group_id
773 ,i_txn_category => p_txn_category
774 ,i_item_id => p_inventory_item_id
775 ,i_user_id => p_user_id
776 ,i_login_id => p_login_id
777 ,i_request_id => p_req_id
778 ,i_prog_id => p_prg_id
779 ,i_prog_appl_id => p_prg_appid
780 ,o_err_num => l_error_num
781 ,o_err_code => l_error_code
782 ,o_err_msg => l_error_msg
783 );
784
785 l_error_num := NVL(l_error_num, 0);
786 l_error_code := NVL(l_error_code, 'No Error');
787 l_error_msg := NVL(l_error_msg, 'No Error');
788
789 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
790 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
791 ,G_MODULE_HEAD || l_routine || '.updcppb3'
792 ,'After calling update_item_cppb:'|| l_error_num || l_error_code || l_error_msg
793 );
794 END IF;
795
796 END IF;
797
798 IF l_error_num <> 0
799 THEN
800 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
801 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION
802 , G_MODULE_HEAD || l_routine || '.others'
803 , 'Error in cost group ' || p_cost_group_id ||
804 'txn category:' || p_txn_category || ' ('||l_error_code||') '||l_error_msg
805 );
806 END IF;
807
808 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
809 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
810 FND_MESSAGE.set_token('MESSAGE', 'Error for cost group '||p_cost_group_id||' ('||l_error_code||') '||l_error_msg);
811 FND_MSG_PUB.Add;
812 RAISE FND_API.G_EXC_ERROR;
813 END IF;
814
815
816 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
817 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
818 ,G_MODULE_HEAD || l_routine || '.end'
819 ,l_routine || '>'
820 );
821 END IF;
822
823
824 END Periodic_Cost_Update_By_Level;
825
826
827 --========================================================================
828 -- PROCEDURE : Find Starting Phase PRIVATE
829 -- COMMENT : Find the starting phase for the cost group
830 -- : Starting phase depend on Processing Types :
831 -- : 1. Run Acquisition Only (Acquisition Cost SRS)
832 -- : Start Phase = 1.
833 -- : 2. Run From phase 2 (PAC SRS option 1).
834 -- : Validate that phase 1 completed, error out if it's not
835 -- : If phase 1 is completed, then Start Phase = 2.
836 -- : 3. Run From error out phase (PAC SRS option 2)
837 -- : Validate that phase 1 completed, error out if it's not
838 -- : If phase 1 is completed, then Start Phase = Error Out Phase.
839 -- : If no error out phase (All complete/pending),
840 -- : start from phase 2.
841 --=========================================================================
842 PROCEDURE find_starting_phase
843 ( p_legal_entity IN NUMBER
844 , p_cost_type_id IN NUMBER
845 , p_period_id IN NUMBER
846 , p_end_date IN DATE
847 , p_cost_group_id IN NUMBER
848 , p_run_options IN NUMBER
849 , x_starting_phase OUT NOCOPY NUMBER
850 , p_user_id IN NUMBER
851 , p_login_id IN NUMBER
852 , p_req_id IN NUMBER
853 , p_prg_id IN NUMBER
854 , p_prg_appid IN NUMBER
855 )
856 IS
857
858 l_routine CONSTANT VARCHAR2(30) := 'find_starting_phase';
859 --=================
860 -- VARIABLES
861 --=================
862
863 l_starting_phase NUMBER;
864 l_phase_status NUMBER;
865 l_count NUMBER;
866
867 BEGIN
868
869 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
870 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
871 ,G_MODULE_HEAD || l_routine || '.begin'
872 ,l_routine || '<'
873 );
874 END IF;
875
876 -- Run Options: 3 Resume for tolerance; 4 Final iteration
877 IF (p_run_options = 3 OR p_run_options = 4) THEN
878 l_starting_phase := 7;
879 -- Process_upto_date check count
880 l_count := 0;
881
882 ELSIF p_run_options = 1 THEN
883 -- Run Options: 1 Start
884
885 -- Set the starting phase to 1
886 l_starting_phase := 1;
887
888 -- Process_upto_date check count
889 l_count := 0;
890
891 ELSE
892 -- Run Options: 2 Resume from error
893 SELECT nvl(min(process_phase), 1)
894 INTO l_starting_phase
895 FROM cst_pac_process_phases
896 WHERE pac_period_id = p_period_id
897 AND cost_group_id = p_cost_group_id
898 AND process_status = 3
899 AND ( process_phase <= 5 OR process_phase = 7);
900
901 -- Make sure that process_upto_date of the acquisition cost is
902 -- equal or less than the period end date
903 IF l_starting_phase = 7 THEN
904
905 SELECT nvl(min(process_phase), 7)
906 INTO l_starting_phase
907 FROM cst_pac_process_phases
908 WHERE pac_period_id = p_period_id
909 AND cost_group_id = p_cost_group_id
910 AND process_status = 3
911 AND process_phase IN (8);
912
913 SELECT
914 count(1)
915 INTO l_count
916 FROM cst_pac_process_phases
917 WHERE pac_period_id = p_period_id
918 AND cost_group_id = p_cost_group_id
919 AND process_phase = 5
920 AND process_upto_date <= p_end_date;
921
922 ELSIF l_starting_phase <= 5 THEN
923 SELECT
924 count(1)
925 INTO l_count
926 FROM cst_pac_process_phases
927 WHERE pac_period_id = p_period_id
928 AND cost_group_id = p_cost_group_id
929 AND process_phase = l_starting_phase - 1
930 AND process_upto_date <= p_end_date;
931 END IF;
932
933 END IF;
934
935 x_starting_phase := l_starting_phase;
936
937 IF l_count <> 0 THEN
938 -- Set the starting phase status to error
939 CST_PERIODIC_AVERAGE_PROC_CP.set_status
940 ( p_period_id => p_period_id
941 , p_cost_group_id => p_cost_group_id
942 , p_phase => l_starting_phase
943 , p_status => 3
944 , p_end_date => p_end_date
945 , p_user_id => p_user_id
946 , p_login_id => p_login_id
947 , p_req_id => p_req_id
948 , p_prg_id => p_prg_id
949 , p_prg_appid => p_prg_appid);
950
951 FND_MESSAGE.Set_Name('BOM', 'CST_PAC_PROCESS_DATE_ACQ');
952 FND_MSG_PUB.Add;
953 RAISE FND_API.G_EXC_ERROR;
954
955 END IF;
956
957 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
958 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
959 ,G_MODULE_HEAD || l_routine || '.end'
960 ,l_routine || '>'
961 );
962 END IF;
963
964 END find_starting_phase;
965
966 --========================================================================
967 -- FUNCTION : Get Uom Control Level PRIVATE
968 -- COMMENT : Find the cost method
969 --=========================================================================
970 FUNCTION get_uom_control_level
971 RETURN NUMBER
972 IS
973
974 l_routine CONSTANT VARCHAR2(30) := 'get_uom_control_level';
975 --=================
976 -- VARIABLES
977 --=================
978
979 l_uom_control NUMBER;
980
981 BEGIN
982
983 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
984 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
985 ,G_MODULE_HEAD || l_routine || '.begin'
986 ,l_routine || '<'
987 );
988 END IF;
989
990 SELECT control_level
991 INTO l_uom_control
992 FROM mtl_item_attributes
993 WHERE attribute_name = 'MTL_SYSTEM_ITEMS.PRIMARY_UNIT_OF_MEASURE';
994
995 RETURN l_uom_control;
996
997 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
998 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
999 ,G_MODULE_HEAD || l_routine || '.end'
1000 ,l_routine || '>'
1001 );
1002 END IF;
1003
1004 END get_uom_control_level;
1005
1006 --========================================================================
1007 -- PROCEDURE : Validate Master Org PRIVATE
1008 -- COMMENT : Validate Master Organization
1009 --========================================================================
1010 PROCEDURE validate_master_org
1011 ( p_legal_entity IN NUMBER
1012 , p_cost_type_id IN NUMBER
1013 , p_cost_group_id IN NUMBER
1014 , x_master_org_id OUT NOCOPY NUMBER
1015 )
1016 IS
1017
1018 l_routine CONSTANT VARCHAR2(30) := 'validate_master_org';
1019 --=================
1020 -- VARIABLES
1021 --=================
1022
1023 l_master_org_id NUMBER;
1024 l_count NUMBER;
1025 l_message VARCHAR2(250);
1026
1027 BEGIN
1028
1029 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1030 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1031 ,G_MODULE_HEAD || l_routine || '.begin'
1032 ,l_routine || '<'
1033 );
1034 END IF;
1035
1036 SELECT NVL(organization_id,-1)
1037 INTO l_master_org_id
1038 FROM cst_cost_groups
1039 WHERE cost_group_id = p_cost_group_id;
1040
1041 -- Validate that all the orgs under this cost group
1042 -- have the same master.
1043 -- The logic to prove this
1044 -- checks the cost group assignment
1045 -- table for organizations associated
1046 -- with the given cost group when the
1047 -- master org is different than the
1048 -- master org being validated.
1049
1050 SELECT count(1)
1051 INTO l_count
1052 FROM mtl_parameters mp
1053 WHERE mp.master_organization_id <> l_master_org_id
1054 AND mp.organization_id IN (
1055 SELECT organization_id
1056 FROM cst_cost_group_assignments ccga
1057 WHERE ccga.cost_group_id = p_cost_group_id)
1058 AND rownum = 1;
1059
1060 IF l_count = 0
1061 THEN
1062
1063 x_master_org_id := l_master_org_id;
1064
1065 ELSE
1066 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_SOL_MST');
1067 FND_MESSAGE.set_token('CSTGRP', p_cost_group_id);
1068 l_message := FND_MESSAGE.GET;
1069 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
1070 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
1071 FND_MESSAGE.set_token('MESSAGE', l_message);
1072 FND_MSG_PUB.Add;
1073 RAISE FND_API.G_EXC_ERROR;
1074 END IF;
1075
1076 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1077 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1078 ,G_MODULE_HEAD || l_routine || '.end'
1079 ,l_routine || '>'
1080 );
1081 END IF;
1082
1083 END validate_master_org;
1084
1085 --========================================================================
1086 -- PROCEDURE : Find_Pac_Rates_algorithm PRIVATE
1087 -- COMMENT : Find the pac rates and
1088 -- : Material Relief Algorithm (introduced in R12)
1089 -- : 0 - Use Pre-defined Materials
1090 -- : 1 - Use Actual Materials
1091 --=========================================================================
1092 PROCEDURE find_pac_rates_algorithm
1093 ( p_legal_entity IN NUMBER
1094 , p_cost_type_id IN NUMBER
1095 , x_pac_rates_id OUT NOCOPY NUMBER
1096 , x_mat_relief_algorithm OUT NOCOPY NUMBER
1097 )
1098 IS
1099
1100 l_routine CONSTANT VARCHAR2(30) := 'find_pac_rates_algorithm';
1101 --=================
1102 -- VARIABLES
1103 --=================
1104
1105 l_pac_rates_id NUMBER;
1106 l_mat_relief_algorithm NUMBER;
1107
1108 BEGIN
1109
1110 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1111 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1112 ,G_MODULE_HEAD || l_routine || '.begin'
1113 ,l_routine || '<'
1114 );
1115 END IF;
1116
1117 SELECT
1118 nvl(max(pac_rates_cost_type_id),-1)
1119 , nvl(max(material_relief_algorithm),1)
1120 INTO l_pac_rates_id
1121 ,l_mat_relief_algorithm
1122 FROM cst_le_cost_types
1123 WHERE legal_entity = p_legal_entity
1124 AND cost_type_id = p_cost_type_id;
1125
1126 x_pac_rates_id := l_pac_rates_id;
1127 x_mat_relief_algorithm := l_mat_relief_algorithm;
1128
1129 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1130 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1131 ,G_MODULE_HEAD || l_routine || '.end'
1132 ,l_routine || '>'
1133 );
1134 END IF;
1135
1136 END find_pac_rates_algorithm;
1137
1138 --========================================================================
1139 -- PROCEDURE : Validate_Process_Upto_Date PRIVATE
1140 -- COMMENT : Check whether the process upto date lies between the PAC
1141 -- : Start Date and End Date
1142 --=========================================================================
1143 PROCEDURE validate_process_upto_date
1144 ( p_process_upto_date IN VARCHAR2
1145 , p_period_id IN NUMBER
1146 , p_run_options IN NUMBER
1147 )
1148 IS
1149
1150 l_routine CONSTANT VARCHAR2(30) := 'validate_process_upto_date';
1151 --=================
1152 -- VARIABLES
1153 --=================
1154
1155 l_count NUMBER;
1156 l_process_upto_date DATE;
1157
1158 BEGIN
1159
1160 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1161 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1162 ,G_MODULE_HEAD || l_routine || '.begin'
1163 ,l_routine || '<'
1164 );
1165 END IF;
1166
1167 -- Run options 1 - Start
1168 -- Check process upto date is within the range
1169 IF p_run_options = 1 THEN
1170 l_process_upto_date :=
1171 TRUNC(FND_DATE.canonical_to_date(p_process_upto_date));
1172 FND_FILE.put_line
1173 ( FND_FILE.log
1174 , 'Process Upto Date:' || l_process_upto_date
1175 );
1176
1177 SELECT count(1)
1178 INTO l_count
1179 FROM CST_PAC_PERIODS cpp
1180 WHERE cpp.pac_period_id = p_period_id
1181 AND TRUNC(cpp.period_end_date) >= l_process_upto_date
1182 AND TRUNC(cpp.period_start_date) <= l_process_upto_date;
1183
1184 FND_FILE.put_line
1185 ( FND_FILE.log
1186 , ' Count of Periods in the range:' || l_count
1187 );
1188
1189 IF l_count = 0 THEN
1190 FND_MESSAGE.Set_Name('BOM', 'CST_PAC_PROCESS_DATE_ERROR');
1191 FND_MSG_PUB.Add;
1192 RAISE FND_API.G_EXC_ERROR;
1193 END IF;
1194 ELSE
1195 -- All other run options: 2 - Resume for Error, 3 - Resume for non tolerance
1196 -- , 4 - Final process upto date should be NULL
1197 IF p_process_upto_date IS NOT NULL THEN
1198 FND_MESSAGE.Set_Name('BOM', 'CST_PAC_PROCESS_DATE_NULL');
1199 FND_MSG_PUB.Add;
1200 RAISE FND_API.G_EXC_ERROR;
1201 END IF;
1202 END IF; -- run options check
1203
1204 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1205 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1206 ,G_MODULE_HEAD || l_routine || '.end'
1207 ,l_routine || '>'
1208 );
1209 END IF;
1210
1211 END validate_process_upto_date;
1212
1213 --========================================================================
1214 -- PROCEDURE : Number_Of_Assignments PRIVATE
1215 -- COMMENT : find if a cost group has assignments
1216 --=========================================================================
1217 PROCEDURE number_of_assignments
1218 ( p_cost_group_id IN NUMBER
1219 , p_period_id IN NUMBER
1220 , p_user_id IN NUMBER
1221 , p_login_id IN NUMBER
1222 , p_req_id IN NUMBER
1223 , p_prg_id IN NUMBER
1224 , p_prg_appid IN NUMBER
1225 )
1226 IS
1227
1228 l_routine CONSTANT VARCHAR2(30) := 'number_of_assignments';
1229 --=================
1230 -- VARIABLES
1231 --=================
1232
1233 l_num_of_assignments NUMBER;
1234
1235 BEGIN
1236
1237 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1238 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1239 ,G_MODULE_HEAD || l_routine || '.begin'
1240 ,l_routine || '<'
1241 );
1242 END IF;
1243
1244 SELECT count(1)
1245 INTO l_num_of_assignments
1246 FROM cst_cost_group_assignments ccga
1247 WHERE ccga.cost_group_id = p_cost_group_id;
1248
1249 IF l_num_of_assignments = 0 THEN
1250
1251 UPDATE cst_pac_process_phases
1252 SET process_status = 3,
1253 process_date = SYSDATE,
1254 last_update_date = SYSDATE,
1255 last_updated_by = p_user_id,
1256 request_id = p_req_id,
1257 program_application_id = p_prg_appid,
1258 program_id = p_prg_id,
1259 program_update_date = SYSDATE,
1260 last_update_login = p_login_id
1261 WHERE pac_period_id = p_period_id
1262 AND cost_group_id = p_cost_group_id;
1263
1264 /*
1265
1266 AND process_phase = decode(l_processing_options,1,1,2);
1267
1268 */
1269 END IF;
1270
1271 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1272 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1273 ,G_MODULE_HEAD || l_routine || '.end'
1274 ,l_routine || '>'
1275 );
1276 END IF;
1277
1278 END number_of_assignments;
1279
1280 --========================================================================
1281 -- PROCEDURE : find_cost_method PRIVATE
1282 -- COMMENT : Find the cost method
1283 --=========================================================================
1284 PROCEDURE find_cost_method
1285 ( p_legal_entity IN NUMBER
1286 , p_cost_type_id IN NUMBER
1287 , x_cost_method OUT NOCOPY NUMBER
1288 )
1289 IS
1290
1291 l_routine CONSTANT VARCHAR2(30) := 'find_cost_method';
1292 --=================
1293 -- VARIABLES
1294 --=================
1295
1296 l_cost_method NUMBER;
1297
1298 BEGIN
1299
1300 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1301 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1302 ,G_MODULE_HEAD || l_routine || '.begin'
1303 ,l_routine || '<'
1304 );
1305 END IF;
1306
1307 SELECT nvl(max(primary_cost_method),-1)
1308 INTO l_cost_method
1309 FROM cst_le_cost_types clct
1310 WHERE clct.legal_entity = p_legal_entity
1311 AND clct.cost_type_id = p_cost_type_id;
1312
1313 x_cost_method := l_cost_method;
1314
1315 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1316 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1317 ,G_MODULE_HEAD || l_routine || '.end'
1318 ,l_routine || '>'
1319 );
1320 END IF;
1321
1322 END find_cost_method;
1323
1324 --========================================================================
1325 -- PROCEDURE : Validate_Cost_Groups PRIVATE
1326 -- COMMENT : This procedure will find the cost groups that fall
1327 -- : under this cost type/legal entity association and
1328 -- : check their validity
1329 --=========================================================================
1330 PROCEDURE validate_cost_groups
1331 ( p_legal_entity IN NUMBER
1332 , p_cost_type_id IN NUMBER
1333 , p_period_id IN NUMBER
1334 , p_cost_group_id IN NUMBER
1335 )
1336 IS
1337
1338 l_routine CONSTANT VARCHAR2(30) := 'validate_cost_groups';
1339 --=================
1340 -- VARIABLES
1341 --=================
1342 l_count NUMBER;
1343 l_message VARCHAR2(250);
1344 BEGIN
1345
1346 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1347 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1348 ,G_MODULE_HEAD || l_routine || '.begin'
1349 ,l_routine || '<'
1350 );
1351 END IF;
1352
1353 SELECT count(1)
1354 INTO l_count
1355 FROM cst_cost_groups ccg
1356 WHERE ccg.legal_entity = p_legal_entity
1357 AND ccg.cost_group_id = p_cost_group_id
1358 AND trunc(nvl(ccg.disable_date, SYSDATE+1)) > trunc(SYSDATE)
1359 AND EXISTS (
1360 SELECT 'X'
1361 FROM cst_pac_process_phases cppp
1362 WHERE cppp.cost_group_id = ccg.cost_group_id
1363 AND cppp.pac_period_id = p_period_id);
1364
1365 IF l_count = 0
1366 THEN
1367 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_VAL_CG');
1368 FND_MESSAGE.set_token('CSTGRP', p_cost_group_id);
1369 l_message := FND_MESSAGE.GET;
1370 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
1371 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
1372 FND_MESSAGE.set_token('MESSAGE', l_message);
1373 FND_MSG_PUB.Add;
1374 RAISE FND_API.G_EXC_ERROR;
1375 END IF;
1376
1377 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1378 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1379 ,G_MODULE_HEAD || l_routine || '.end'
1380 ,l_routine || '>'
1381 );
1382 END IF;
1383
1384 END validate_cost_groups;
1385
1386 --========================================================================
1387 -- PROCEDURE : Find_Period_Duration PRIVATE
1388 -- COMMENT : Find the Start and End dates for the current period
1389 --=========================================================================
1390 PROCEDURE find_period_duration
1391 ( p_legal_entity IN NUMBER
1392 , p_cost_type_id IN NUMBER
1393 , p_period_id IN NUMBER
1394 , p_process_upto_date IN VARCHAR2
1395 , x_start_date OUT NOCOPY DATE
1396 , x_end_date OUT NOCOPY DATE
1397 )
1398 IS
1399
1400 l_routine CONSTANT VARCHAR2(30) := 'find_period_duration';
1401 --=================
1402 -- VARIABLES
1403 --=================
1404
1405 l_start_date VARCHAR2(30);
1406 l_end_date VARCHAR2(30);
1407
1408 BEGIN
1409
1410 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1411 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1412 ,G_MODULE_HEAD || l_routine || '.begin'
1413 ,l_routine || '<'
1414 );
1415 END IF;
1416
1417 SELECT TO_CHAR(period_start_date,'YYYY/MM/DD HH24:MI:SS')
1418 ,p_process_upto_date
1419 INTO l_start_date
1420 ,l_end_date
1421 FROM cst_pac_periods cpp
1422 WHERE cpp.pac_period_id = p_period_id
1423 AND cpp.legal_entity = p_legal_entity
1424 AND cpp.cost_type_id = p_cost_type_id;
1425
1426 x_start_date := TO_DATE(l_start_date,'YYYY/MM/DD HH24:MI:SS');
1427
1428 -- set to 23:59:59 to retrieve all the records
1429 x_end_date := TRUNC(FND_DATE.canonical_to_date(l_end_date)) + (86399/86400);
1430
1431 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1432 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1433 ,G_MODULE_HEAD || l_routine || '.end'
1434 ,l_routine || '>'
1435 );
1436 END IF;
1437
1438 END find_period_duration;
1439
1440 --========================================================================
1441 -- PROCEDURE : Validate_Phases_Seeded PRIVATE
1442 -- COMMENT : This procedure will ensure all 7 phases are seeded for
1443 -- : each cost group and phase 7 has a process status of 1
1444 --=========================================================================
1445 PROCEDURE validate_phases_seeded
1446 ( p_cost_group_id IN NUMBER
1447 , p_period_id IN NUMBER
1448 )
1449 IS
1450
1451 l_routine CONSTANT VARCHAR2(30) := 'validate_phases_seeded';
1452 --=================
1453 -- VARIABLES
1454 --=================
1455
1456 l_status NUMBER := 1;
1457 l_count NUMBER;
1458 l_message VARCHAR2(250);
1459 BEGIN
1460
1461 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1462 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1463 ,G_MODULE_HEAD || l_routine || '.begin'
1464 ,l_routine || '<'
1465 );
1466 END IF;
1467
1468 SELECT count(1)
1469 INTO l_count
1470 FROM cst_pac_process_phases
1471 WHERE cost_group_id = p_cost_group_id
1472 AND pac_period_id = p_period_id;
1473
1474 IF l_count <> 8
1475 THEN
1476 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_VAL_PHS_SED');
1477 l_message := FND_MESSAGE.GET;
1478 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
1479 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
1480 FND_MESSAGE.set_token('MESSAGE', l_message);
1481 FND_MSG_PUB.Add;
1482 RAISE FND_API.G_EXC_ERROR;
1483 END IF;
1484
1485 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1486 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1487 ,G_MODULE_HEAD || l_routine || '.end'
1488 ,l_routine || '>'
1489 );
1490 END IF;
1491
1492 END validate_phases_seeded;
1493
1494 --========================================================================
1495 -- PROCEDURE : Validate_Previous_Period PRIVATE
1496 -- COMMENT : This procedure will ensure that previous period is closed
1497 --=========================================================================
1498 PROCEDURE validate_previous_period
1499 ( p_legal_entity IN NUMBER
1500 , p_cost_type_id IN NUMBER
1501 , p_period_id IN NUMBER
1502 , x_prev_period_id OUT NOCOPY NUMBER
1503 )
1504 IS
1505
1506 l_routine CONSTANT VARCHAR2(30) := 'validate_previous_period';
1507 --=================
1508 -- VARIABLES
1509 --=================
1510
1511 l_count NUMBER;
1512 l_prev_period_id NUMBER;
1513 l_period_closed NUMBER;
1514 l_period_complete NUMBER;
1515 l_message VARCHAR2(250);
1516 BEGIN
1517
1518 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1519 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1520 ,G_MODULE_HEAD || l_routine || '.begin'
1521 ,l_routine || '<'
1522 );
1523 END IF;
1524
1525 SELECT nvl(max(cpp.pac_period_id), -1)
1526 INTO l_prev_period_id
1527 FROM cst_pac_periods cpp
1528 WHERE cpp.legal_entity = p_legal_entity
1529 AND cpp.cost_type_id = p_cost_type_id
1530 AND cpp.pac_period_id < p_period_id;
1531
1532 IF l_prev_period_id <> -1
1533 THEN
1534
1535 SELECT count(1)
1536 INTO l_period_closed
1537 FROM cst_pac_periods cpp
1538 WHERE cpp.pac_period_id = l_prev_period_id
1539 AND cpp.legal_entity = p_legal_entity
1540 AND cpp.cost_type_id = p_cost_type_id
1541 AND cpp.open_flag = 'N'
1542 AND cpp.period_close_date IS NOT NULL;
1543
1544 IF l_period_closed = 0 THEN
1545 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_VAL_PRE_PER');
1546 l_message := FND_MESSAGE.GET;
1547 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
1548 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
1549 FND_MESSAGE.set_token('MESSAGE', l_message);
1550 FND_MSG_PUB.Add;
1551 RAISE FND_API.G_EXC_ERROR;
1552 END IF;
1553
1554 END IF;
1555
1556 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1557 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1558 ,G_MODULE_HEAD || l_routine || '.prevpd'
1559 ,'Previous Period Id:' || l_prev_period_id
1560 );
1561 END IF;
1562
1563 x_prev_period_id := l_prev_period_id;
1564
1565 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1566 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1567 ,G_MODULE_HEAD || l_routine || '.end'
1568 ,l_routine || '>'
1569 );
1570 END IF;
1571
1572 END validate_previous_period;
1573
1574 --========================================================================
1575 -- PROCEDURE : Validate_Period PRIVATE
1576 -- COMMENT : This procedure checks the current period is open
1577 -- : for the legal entity, cost type association.
1578 --=========================================================================
1579 PROCEDURE validate_period
1580 ( p_legal_entity IN NUMBER
1581 , p_cost_type_id IN NUMBER
1582 , p_period_id IN NUMBER
1583 )
1584 IS
1585
1586 l_routine CONSTANT VARCHAR2(30) := 'validate_period';
1587 --=================
1588 -- VARIABLES
1589 --=================
1590
1591 l_count NUMBER;
1592 l_message VARCHAR2(250);
1593 BEGIN
1594
1595 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1596 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1597 ,G_MODULE_HEAD || l_routine || '.begin'
1598 ,l_routine || '<'
1599 );
1600 END IF;
1601
1602 SELECT count(1)
1603 INTO l_count
1604 FROM cst_pac_periods cpp
1605 WHERE cpp.legal_entity = p_legal_entity
1606 AND cpp.cost_type_id = p_cost_type_id
1607 AND cpp.pac_period_id = p_period_id
1608 AND cpp.open_flag = 'Y'
1609 AND cpp.period_close_date IS NULL;
1610
1611 IF l_count = 0
1612 THEN
1613 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_VAL_CUR_PER');
1614 l_message := FND_MESSAGE.GET;
1615 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
1616 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
1617 FND_MESSAGE.set_token('MESSAGE', l_message);
1618 FND_MSG_PUB.Add;
1619 RAISE FND_API.G_EXC_ERROR;
1620 END IF;
1621
1622 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1623 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1624 ,G_MODULE_HEAD || l_routine || '.end'
1625 ,l_routine || '>'
1626 );
1627 END IF;
1628
1629 END validate_period;
1630
1631 --========================================================================
1632 -- PROCEDURE : Validate_Le_Ct_Association PRIVATE
1633 -- COMMENT : check the validity of cost type, legal entity
1634 -- : and their association
1635 --=========================================================================
1636 PROCEDURE validate_le_ct_association
1637 ( p_legal_entity IN NUMBER
1638 , p_cost_type_id IN NUMBER
1639 )
1640 IS
1641
1642 l_routine CONSTANT VARCHAR2(30) := 'validate_le_ct_association';
1643 --=================
1644 -- VARIABLES
1645 --=================
1646
1647 l_count NUMBER;
1648 l_message VARCHAR2(250);
1649
1650 BEGIN
1651
1652 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1653 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1654 ,G_MODULE_HEAD || l_routine || '.begin'
1655 ,l_routine || '<'
1656 );
1657 END IF;
1658
1659 SELECT count(1)
1660 INTO l_count
1661 FROM cst_cost_types cct
1662 WHERE cct.cost_type_id = p_cost_type_id
1663 AND cct.organization_id IS NULL
1664 AND cct.allow_updates_flag = 2
1665 AND trunc(nvl(cct.disable_date, SYSDATE+1)) > trunc(SYSDATE);
1666
1667 IF l_count = 0
1668 THEN
1669 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_VAL_CT');
1670 l_message := FND_MESSAGE.GET;
1671 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
1672 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
1673 FND_MESSAGE.set_token('MESSAGE', l_message);
1674 FND_MSG_PUB.Add;
1675 RAISE FND_API.G_EXC_ERROR;
1676 END IF;
1677
1678 SELECT count(1)
1679 INTO l_count
1680 FROM cst_le_cost_types clct
1681 WHERE clct.legal_entity = p_legal_entity
1682 AND clct.cost_type_id = p_cost_type_id;
1683
1684 IF l_count = 0
1685 THEN
1686 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_VAL_LE_CT');
1687 l_message := FND_MESSAGE.GET;
1688 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
1689 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
1690 FND_MESSAGE.set_token('MESSAGE', l_message);
1691 FND_MSG_PUB.Add;
1692 RAISE FND_API.G_EXC_ERROR;
1693 END IF;
1694
1695 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1696 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1697 ,G_MODULE_HEAD || l_routine || '.end'
1698 ,l_routine || '>'
1699 );
1700 END IF;
1701
1702 END validate_le_ct_association;
1703
1704 --=========================================================================
1705 -- PROCEDURE : Process_Gp2_Other_Txns
1706 -- COMMENT : This procedure processes items in current BOM
1707 -- : level with interorg txns within the same cost group and non
1708 -- : interorg txns
1709 --=========================================================================
1710 PROCEDURE Process_Gp2_Other_Txns
1711 (p_legal_entity IN NUMBER
1712 ,p_cost_type_id IN NUMBER
1713 ,p_cost_method IN NUMBER
1714 ,p_period_id IN NUMBER
1715 ,p_start_date IN DATE
1716 ,p_end_date IN DATE
1717 ,p_prev_period_id IN NUMBER
1718 ,p_cg_tab IN CST_PERIODIC_ABSORPTION_PROC.tbl_type
1719 ,p_inventory_item_id IN NUMBER
1720 ,p_uom_control IN NUMBER
1721 ,p_pac_rates_id IN NUMBER
1722 ,p_mat_relief_algorithm IN NUMBER
1723 ,p_user_id IN NUMBER
1724 ,p_login_id IN NUMBER
1725 ,p_req_id IN NUMBER
1726 ,p_prg_id IN NUMBER
1727 ,p_prg_appid IN NUMBER
1728 )
1729 IS
1730
1731 l_routine CONSTANT VARCHAR2(30) := 'process_gp2_other_txns';
1732
1733 -- =======================================================================
1734 -- Cursor to retrieve Group 2 Transactions :
1735 -- interorg txns within the same cost group
1736 -- non inter org txns
1737 -- interorg txns across cost groups generated through internal sales orders
1738 -- when the transfer price option is 2
1739 -- OPM convergence - Logical intransit shipment 22 processed at shipping
1740 -- cost group; direct interorg shipment where receiving org is OPM
1741 -- Interorg txns within same CG
1742 -- All items other than interorg items across cost groups
1743 -- =======================================================================
1744 CURSOR group2_other_cur(c_period_start_date DATE
1745 ,c_period_end_date DATE
1746 ,c_pac_period_id NUMBER
1747 ,c_cost_group_id NUMBER
1748 ,c_inventory_item_id NUMBER
1749 )
1750 IS
1751 SELECT
1752 mmt.transaction_id
1753 , mmt.transaction_action_id
1754 , mmt.transaction_source_type_id
1755 , mmt.inventory_item_id
1756 , mmt.primary_quantity
1757 , mmt.organization_id
1758 , nvl(mmt.transfer_organization_id,-1) transfer_organization_id
1759 , mmt.subinventory_code
1760 , nvl(mmt.transfer_price,0) transfer_price
1761 FROM mtl_material_transactions mmt
1762 WHERE transaction_date between c_period_start_date AND c_period_end_date
1763 AND mmt.organization_id = nvl(mmt.owning_organization_id, mmt.organization_id)
1764 AND mmt.inventory_item_id = c_inventory_item_id
1765 AND nvl(mmt.owning_tp_type,2) = 2
1766 AND EXISTS (select 'X'
1767 from mtl_parameters mp
1768 where mmt.organization_id = mp.organization_id
1769 AND nvl(mp.process_enabled_flag, 'N') = 'N')
1770 AND transaction_action_id in (3,12,21)
1771 AND EXISTS (SELECT 'EXISTS'
1772 FROM cst_cost_group_assignments ccga
1773 WHERE ccga.cost_group_id = c_cost_group_id
1774 AND (ccga.organization_id = mmt.organization_id OR
1775 ccga.organization_id = mmt.transfer_organization_id))
1776 AND (
1777 (mmt.transaction_source_type_id = 13
1778 AND EXISTS (select 'X'
1779 from mtl_parameters mp2
1780 where mp2.organization_id = mmt.transfer_organization_id
1781 AND mp2.process_enabled_flag = 'Y'))
1782 OR (mmt.transaction_source_type_id in (7,8)
1783 AND EXISTS (SELECT 'X'
1784 FROM mtl_intercompany_parameters mip
1785 WHERE nvl(fnd_profile.value('INV_INTERCOMPANY_INVOICE_INTERNAL_ORDER'),0) = 1
1786 AND mip.flow_type = 1
1787 AND nvl(fnd_profile.value('CST_TRANSFER_PRICING_OPTION'),0) = 2
1788 AND mip.ship_organization_id = (select to_number(hoi.org_information3)
1789 from hr_organization_information hoi
1790 where hoi.organization_id = decode(mmt.transaction_action_id,21,
1791 mmt.organization_id,mmt.transfer_organization_id)
1792 AND hoi.org_information_context = 'Accounting Information')
1793 AND mip.sell_organization_id = (select to_number(hoi2.org_information3)
1794 from hr_organization_information hoi2
1795 where hoi2.organization_id = decode(mmt.transaction_action_id,21,
1796 mmt.transfer_organization_id, mmt.organization_id)
1797 AND hoi2.org_information_context = 'Accounting Information')))
1798 )
1799 AND (transaction_action_id IN (3,12,21)
1800 AND NOT EXISTS (SELECT 'X'
1801 FROM cst_cost_group_assignments c1, cst_cost_group_assignments c2
1802 WHERE c1.organization_id = mmt.organization_id
1803 AND c2.organization_id = mmt.transfer_organization_id
1804 AND c1.cost_group_id = c2.cost_group_id)
1805 AND (
1806 (mmt.transaction_action_id = 3
1807 AND EXISTS (SELECT 'X'
1808 FROM cst_cost_group_assignments ccga1
1809 WHERE ccga1.cost_group_id = c_cost_group_id
1810 AND ccga1.organization_id = mmt.organization_id
1811 AND mmt.primary_quantity < 0)
1812 )
1813 OR (mmt.transaction_action_id = 21
1814 AND EXISTS (SELECT 'X'
1815 FROM cst_cost_group_assignments ccga2
1816 WHERE ccga2.organization_id = mmt.organization_id
1817 AND ccga2.cost_group_id = c_cost_group_id)
1818 )
1819 OR (mmt.transaction_action_id = 12
1820 AND EXISTS (SELECT 'X'
1821 FROM mtl_interorg_parameters mip
1822 WHERE mip.from_organization_id = mmt.transfer_organization_id
1823 AND mip.to_organization_id = mmt.organization_id
1824 AND (
1825 (NVL(mmt.fob_point,mip.fob_point) = 1
1826 AND EXISTS (SELECT 'X'
1827 FROM cst_cost_group_assignments ccga2
1828 WHERE ccga2.organization_id = mip.to_organization_id
1829 AND ccga2.cost_group_id = c_cost_group_id)
1830 )
1831 OR (NVL(mmt.fob_point,mip.fob_point) = 2
1832 AND EXISTS (SELECT 'X'
1833 FROM cst_cost_group_assignments ccga3
1834 WHERE ccga3.organization_id = mip.from_organization_id
1835 AND ccga3.cost_group_id = c_cost_group_id)
1836 )
1837 )
1838 )
1839 )
1840 )
1841 )
1842 AND NOT EXISTS (SELECT 'X'
1843 FROM cst_pac_low_level_codes cpllc
1844 WHERE cpllc.inventory_item_id = mmt.inventory_item_id
1845 AND cpllc.pac_period_id = c_pac_period_id
1846 AND cpllc.cost_group_id = c_cost_group_id)
1847 UNION ALL
1848 SELECT
1849 mmt.transaction_id
1850 , mmt.transaction_action_id
1851 , mmt.transaction_source_type_id
1852 , mmt.inventory_item_id
1853 , mmt.primary_quantity
1854 , mmt.organization_id
1855 , nvl(mmt.transfer_organization_id,-1) transfer_organization_id
1856 , mmt.subinventory_code
1857 , nvl(mmt.transfer_price,0) transfer_price
1858 FROM mtl_material_transactions mmt
1859 WHERE transaction_date between c_period_start_date AND c_period_end_date
1860 AND mmt.organization_id = nvl(mmt.owning_organization_id, mmt.organization_id)
1861 AND mmt.inventory_item_id = c_inventory_item_id
1862 AND nvl(mmt.owning_tp_type,2) = 2
1863 AND EXISTS (select 'X'
1864 from mtl_parameters mp
1865 where mmt.organization_id = mp.organization_id
1866 AND nvl(mp.process_enabled_flag, 'N') = 'N')
1867 AND (
1868 (mmt.transaction_action_id = 22
1869 AND EXISTS ( SELECT 'X'
1870 FROM cst_cost_group_assignments ccga0
1871 WHERE ccga0.organization_id = mmt.organization_id
1872 AND ccga0.cost_group_id = c_cost_group_id))
1873 OR ( (mmt.transaction_action_id IN (12,21) OR (mmt.transaction_action_id = 3 AND mmt.primary_quantity < 0))
1874 AND EXISTS (SELECT 'X'
1875 FROM cst_cost_group_assignments c1, cst_cost_group_assignments c2
1876 WHERE c1.organization_id = mmt.organization_id
1877 AND c2.organization_id = mmt.transfer_organization_id
1878 AND c1.cost_group_id = c2.cost_group_id
1879 AND c1.cost_group_id = c_cost_group_id))
1880 )
1881 AND NOT EXISTS (SELECT 'X'
1882 FROM cst_pac_low_level_codes cpllc
1883 WHERE cpllc.inventory_item_id = mmt.inventory_item_id
1884 AND cpllc.pac_period_id = c_pac_period_id
1885 AND cpllc.cost_group_id = c_cost_group_id)
1886 UNION ALL
1887 SELECT
1888 mmt.transaction_id
1889 , mmt.transaction_action_id
1890 , mmt.transaction_source_type_id
1891 , mmt.inventory_item_id
1892 , mmt.primary_quantity
1893 , mmt.organization_id
1894 , nvl(mmt.transfer_organization_id,-1) transfer_organization_id
1895 , mmt.subinventory_code
1896 , nvl(mmt.transfer_price,0) transfer_price
1897 FROM
1898 mtl_material_transactions mmt
1899 , cst_cost_group_assignments ccga
1900 WHERE transaction_date between c_period_start_date AND c_period_end_date
1901 AND transaction_action_id in (4,8,28,33,34,1,2,5,27)
1902 AND mmt.organization_id = nvl(mmt.owning_organization_id, mmt.organization_id)
1903 AND mmt.inventory_item_id = c_inventory_item_id
1904 AND nvl(mmt.owning_tp_type,2) = 2
1905 AND ccga.cost_group_id = c_cost_group_id
1906 AND ccga.organization_id = mmt.organization_id
1907 AND nvl(mmt.logical_transactions_created, 1) <> 2
1908 AND nvl(mmt.logical_transaction, 3) <> 1
1909 AND (transaction_action_id IN (4,8,28,33,34)
1910 OR (transaction_action_id IN (2,5) AND primary_quantity < 0)
1911 OR (transaction_action_id in (1, 27)
1912 AND transaction_source_type_id IN (3,6,13)
1913 AND transaction_cost IS NULL)
1914 OR (transaction_action_id in (1,27)
1915 AND transaction_source_type_id NOT IN (1,3,6,13)) )
1916 AND NOT EXISTS (
1917 SELECT 'X'
1918 FROM cst_pac_low_level_codes cpllc
1919 WHERE cpllc.inventory_item_id = mmt.inventory_item_id
1920 AND cpllc.pac_period_id = c_pac_period_id
1921 AND cpllc.cost_group_id = c_cost_group_id);
1922
1923 -- =======================================================================
1924 -- Cursor to retrieve Group 2 Transactions - interorg txns within the same
1925 -- cost group and non inter org txns for completion items in current BOM
1926 -- highest level
1927 -- Interorg txns generated through internal sales orders when transfer
1928 -- price option is enabled - option 2
1929 -- Interorg txns within the same CG
1930 -- OPM equivalent txn (logical shipment 22) to be processed by shipping CG
1931 -- All other cost derived txns
1932 -- =======================================================================
1933 CURSOR group2_other_comp_cur(c_period_start_date DATE
1934 ,c_period_end_date DATE
1935 ,c_pac_period_id NUMBER
1936 ,c_cost_group_id NUMBER
1937 ,c_inventory_item_id NUMBER
1938 )
1939 IS
1940 SELECT
1941 mmt.transaction_id
1942 , mmt.transaction_action_id
1943 , mmt.transaction_source_type_id
1944 , mmt.inventory_item_id
1945 , mmt.primary_quantity
1946 , mmt.organization_id
1947 , nvl(mmt.transfer_organization_id,-1) transfer_organization_id
1948 , mmt.subinventory_code
1949 , nvl(mmt.transfer_price,0) transfer_price
1950 FROM mtl_material_transactions mmt
1951 WHERE transaction_date between c_period_start_date AND c_period_end_date
1952 AND mmt.organization_id = nvl(mmt.owning_organization_id, mmt.organization_id)
1953 AND mmt.inventory_item_id = c_inventory_item_id
1954 AND nvl(mmt.owning_tp_type,2) = 2
1955 AND EXISTS (select 'X'
1956 from mtl_parameters mp
1957 where mmt.organization_id = mp.organization_id
1958 AND nvl(mp.process_enabled_flag, 'N') = 'N')
1959 AND transaction_action_id in (3,12,21)
1960 AND EXISTS (SELECT 'EXISTS'
1961 FROM cst_cost_group_assignments ccga
1962 WHERE ccga.cost_group_id = c_cost_group_id
1963 AND (ccga.organization_id = mmt.organization_id OR
1964 ccga.organization_id = mmt.transfer_organization_id))
1965 AND (
1966 (mmt.transaction_source_type_id = 13
1967 AND EXISTS (select 'X'
1968 from mtl_parameters mp2
1969 where mp2.organization_id = mmt.transfer_organization_id
1970 AND mp2.process_enabled_flag = 'Y'))
1971 OR (mmt.transaction_source_type_id in (7,8)
1972 AND EXISTS (SELECT 'X'
1973 FROM mtl_intercompany_parameters mip
1974 WHERE nvl(fnd_profile.value('INV_INTERCOMPANY_INVOICE_INTERNAL_ORDER'),0) = 1
1975 AND mip.flow_type = 1
1976 AND nvl(fnd_profile.value('CST_TRANSFER_PRICING_OPTION'),0) = 2
1977 AND mip.ship_organization_id = (select to_number(hoi.org_information3)
1978 from hr_organization_information hoi
1979 where hoi.organization_id = decode(mmt.transaction_action_id,21,
1980 mmt.organization_id,mmt.transfer_organization_id)
1981 AND hoi.org_information_context = 'Accounting Information')
1982 AND mip.sell_organization_id = (select to_number(hoi2.org_information3)
1983 from hr_organization_information hoi2
1984 where hoi2.organization_id = decode(mmt.transaction_action_id,21,
1985 mmt.transfer_organization_id, mmt.organization_id)
1986 AND hoi2.org_information_context = 'Accounting Information')))
1987 )
1988 AND (transaction_action_id IN (3,12,21)
1989 AND NOT EXISTS (SELECT 'X'
1990 FROM cst_cost_group_assignments c1, cst_cost_group_assignments c2
1991 WHERE c1.organization_id = mmt.organization_id
1992 AND c2.organization_id = mmt.transfer_organization_id
1993 AND c1.cost_group_id = c2.cost_group_id)
1994 AND (
1995 (mmt.transaction_action_id = 3
1996 AND EXISTS (SELECT 'X'
1997 FROM cst_cost_group_assignments ccga1
1998 WHERE ccga1.cost_group_id = c_cost_group_id
1999 AND ccga1.organization_id = mmt.organization_id
2000 AND mmt.primary_quantity < 0)
2001 )
2002 OR (mmt.transaction_action_id = 21
2003 AND EXISTS (SELECT 'X'
2004 FROM cst_cost_group_assignments ccga2
2005 WHERE ccga2.organization_id = mmt.organization_id
2006 AND ccga2.cost_group_id = c_cost_group_id)
2007 )
2008 OR (mmt.transaction_action_id = 12
2009 AND EXISTS (SELECT 'X'
2010 FROM mtl_interorg_parameters mip
2011 WHERE mip.from_organization_id = mmt.transfer_organization_id
2012 AND mip.to_organization_id = mmt.organization_id
2013 AND (
2014 (NVL(mmt.fob_point,mip.fob_point) = 1
2015 AND EXISTS (SELECT 'X'
2016 FROM cst_cost_group_assignments ccga2
2017 WHERE ccga2.organization_id = mip.to_organization_id
2018 AND ccga2.cost_group_id = c_cost_group_id)
2019 )
2020 OR (NVL(mmt.fob_point,mip.fob_point) = 2
2021 AND EXISTS (SELECT 'X'
2022 FROM cst_cost_group_assignments ccga3
2023 WHERE ccga3.organization_id = mip.from_organization_id
2024 AND ccga3.cost_group_id = c_cost_group_id)
2025 )
2026 )
2027 )
2028 )
2029 )
2030 )
2031 AND EXISTS (SELECT 'X'
2032 FROM cst_pac_low_level_codes cpllc
2033 WHERE cpllc.inventory_item_id = mmt.inventory_item_id
2034 AND cpllc.pac_period_id = c_pac_period_id
2035 AND cpllc.cost_group_id = c_cost_group_id)
2036 UNION ALL
2037 SELECT
2038 mmt.transaction_id
2039 , mmt.transaction_action_id
2040 , mmt.transaction_source_type_id
2041 , mmt.inventory_item_id
2042 , mmt.primary_quantity
2043 , mmt.organization_id
2044 , nvl(mmt.transfer_organization_id,-1) transfer_organization_id
2045 , mmt.subinventory_code
2046 , nvl(mmt.transfer_price,0) transfer_price
2047 FROM mtl_material_transactions mmt
2048 WHERE transaction_date between c_period_start_date AND c_period_end_date
2049 AND mmt.organization_id = nvl(mmt.owning_organization_id, mmt.organization_id)
2050 AND mmt.inventory_item_id = c_inventory_item_id
2051 AND nvl(mmt.owning_tp_type,2) = 2
2052 AND EXISTS (select 'X'
2053 from mtl_parameters mp
2054 where mmt.organization_id = mp.organization_id
2055 AND nvl(mp.process_enabled_flag, 'N') = 'N')
2056 AND (
2057 (mmt.transaction_action_id = 22
2058 AND EXISTS ( SELECT 'X'
2059 FROM cst_cost_group_assignments ccga0
2060 WHERE ccga0.organization_id = mmt.organization_id
2061 AND ccga0.cost_group_id = c_cost_group_id))
2062 OR ( (mmt.transaction_action_id IN (12,21) OR (mmt.transaction_action_id = 3 AND mmt.primary_quantity < 0))
2063 AND EXISTS (SELECT 'X'
2064 FROM cst_cost_group_assignments c1, cst_cost_group_assignments c2
2065 WHERE c1.organization_id = mmt.organization_id
2066 AND c2.organization_id = mmt.transfer_organization_id
2067 AND c1.cost_group_id = c2.cost_group_id
2068 AND c1.cost_group_id = c_cost_group_id))
2069 )
2070 AND EXISTS (SELECT 'X'
2071 FROM cst_pac_low_level_codes cpllc
2072 WHERE cpllc.inventory_item_id = mmt.inventory_item_id
2073 AND cpllc.pac_period_id = c_pac_period_id
2074 AND cpllc.cost_group_id = c_cost_group_id)
2075 UNION ALL
2076 SELECT
2077 mmt.transaction_id
2078 , mmt.transaction_action_id
2079 , mmt.transaction_source_type_id
2080 , mmt.inventory_item_id
2081 , mmt.primary_quantity
2082 , mmt.organization_id
2083 , nvl(mmt.transfer_organization_id,-1) transfer_organization_id
2084 , mmt.subinventory_code
2085 , nvl(mmt.transfer_price,0) transfer_price
2086 FROM
2087 mtl_material_transactions mmt
2088 , cst_cost_group_assignments ccga
2089 WHERE transaction_date between c_period_start_date AND c_period_end_date
2090 AND transaction_action_id in (4,8,28,33,34,1,2,5,27)
2091 AND mmt.organization_id = nvl(mmt.owning_organization_id, mmt.organization_id)
2092 AND mmt.inventory_item_id = c_inventory_item_id
2093 AND nvl(mmt.owning_tp_type,2) = 2
2094 AND ccga.cost_group_id = c_cost_group_id
2095 AND ccga.organization_id = mmt.organization_id
2096 AND nvl(mmt.logical_transactions_created, 1) <> 2
2097 AND nvl(mmt.logical_transaction, 3) <> 1
2098 AND (transaction_action_id IN (4,8,28) /* Bug 8469865: Removed actions 33, 34 from list */
2099 OR (transaction_action_id IN (2,5) AND primary_quantity < 0)
2100 OR (transaction_action_id in (1,27)
2101 AND transaction_source_type_id IN (3,6,13)
2102 AND transaction_cost IS NULL)
2103 OR (transaction_action_id in (1,27)
2104 AND transaction_source_type_id NOT IN (1,3,5,6,13) )
2105 OR (
2106 ((transaction_action_id IN (1,27) AND transaction_source_type_id = 5)
2107 OR transaction_action_id IN (33,34))
2108 AND NOT EXISTS (
2109 SELECT 'X'
2110 FROM wip_entities we
2111 WHERE we.wip_entity_id = mmt.transaction_source_id
2112 AND we.primary_item_id = mmt.inventory_item_id)) )
2113 AND EXISTS (
2114 SELECT 'X'
2115 FROM cst_pac_low_level_codes cpllc
2116 WHERE cpllc.inventory_item_id = mmt.inventory_item_id
2117 AND cpllc.pac_period_id = c_pac_period_id
2118 AND cpllc.cost_group_id = c_cost_group_id);
2119
2120 TYPE group2_other_tab IS TABLE OF group2_other_cur%rowtype INDEX BY BINARY_INTEGER;
2121 l_group2_other_tab group2_other_tab;
2122 l_empty_group2_other_tab group2_other_tab;
2123
2124 l_loop_count NUMBER := 0;
2125 l_batch_size NUMBER := 200;
2126
2127 -- Cursor to get a low level code for an item in that cost group
2128 CURSOR get_llc_cur(c_pac_period_id NUMBER
2129 ,c_cost_group_id NUMBER
2130 ,c_inventory_item_id NUMBER
2131 )
2132 IS
2133 SELECT
2134 low_level_code
2135 FROM cst_pac_low_level_codes
2136 WHERE pac_period_id = c_pac_period_id
2137 AND cost_group_id = c_cost_group_id
2138 AND inventory_item_id = c_inventory_item_id;
2139
2140
2141 -- Variables
2142 l_current_index BINARY_INTEGER;
2143
2144 -- Expense flag variables
2145 l_exp_flag NUMBER;
2146 l_exp_item NUMBER;
2147
2148 -- variable for charge WIP Material
2149 l_hook_used NUMBER;
2150
2151 -- variable to set process group 2
2152 l_process_group NUMBER := 2;
2153
2154 -- Error message variables
2155 l_error_num NUMBER;
2156 l_error_code VARCHAR2(240);
2157 l_error_msg VARCHAR2(240);
2158
2159 -- Transaction Category
2160 l_txn_category NUMBER;
2161 l_low_level_code NUMBER;
2162
2163 -- Exceptions
2164 group2_other_except EXCEPTION;
2165 error_transaction_id NUMBER;
2166 BEGIN
2167
2168 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2169 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2170 ,G_MODULE_HEAD || l_routine || '.begin'
2171 ,l_routine || '<'
2172 );
2173 END IF;
2174 -- initialize transaction category for group 2 (cost derived) transactions
2175 l_txn_category := 9;
2176
2177 l_current_index := p_cg_tab.FIRST;
2178
2179 LOOP
2180 -- Get Low Level Code for an item in the cost group
2181 OPEN get_llc_cur(p_period_id
2182 ,p_cg_tab(l_current_index).cost_group_id
2183 ,p_inventory_item_id
2184 );
2185 FETCH get_llc_cur
2186 INTO l_low_level_code;
2187
2188 -- =============================================================
2189 -- Items across cost groups may be in different BOM levels
2190 -- If item not found in pac low level code, set to -1 inorder to
2191 -- pass the value into update_item_cppb
2192 -- =============================================================
2193 IF get_llc_cur%NOTFOUND THEN
2194 l_low_level_code := -1;
2195 END IF;
2196
2197 CLOSE get_llc_cur;
2198
2199 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2200 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
2201 ,G_MODULE_HEAD || l_routine || '.lowlvcg'
2202 ,'Cost Group Id:' || p_cg_tab(l_current_index).cost_group_id || ' Low Level Code:' || l_low_level_code
2203 );
2204 END IF;
2205
2206 IF l_low_level_code <> -1 THEN
2207 -- completion item
2208 OPEN group2_other_comp_cur
2209 (p_start_date
2210 ,p_end_date
2211 ,p_period_id
2212 ,p_cg_tab(l_current_index).cost_group_id
2213 ,p_inventory_item_id
2214 );
2215
2216 ELSIF (l_low_level_code = -1) THEN
2217 -- no completion item
2218 OPEN group2_other_cur
2219 (p_start_date
2220 ,p_end_date
2221 ,p_period_id
2222 ,p_cg_tab(l_current_index).cost_group_id
2223 ,p_inventory_item_id
2224 );
2225 END IF;
2226
2227 LOOP
2228
2229 l_group2_other_tab := l_empty_group2_other_tab;
2230 IF l_low_level_code <> -1 THEN
2231 FETCH group2_other_comp_cur BULK COLLECT INTO l_group2_other_tab LIMIT l_batch_size;
2232 ELSIF (l_low_level_code = -1) THEN
2233 FETCH group2_other_cur BULK COLLECT INTO l_group2_other_tab LIMIT l_batch_size;
2234 END IF;
2235 l_loop_count := l_group2_other_tab.count;
2236
2237 FOR i IN 1..l_loop_count
2238 LOOP
2239
2240 -- ======================================================================
2241 -- Process Group 2 transactions for a completion item in the current level
2242 -- interorg transactions within the same cost group and
2243 -- non interorg transactions
2244 -- ======================================================================
2245 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2246 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
2247 ,G_MODULE_HEAD || l_routine || '.group2_non_interorg'
2248 ,'Group 2 - Transaction Id:'|| l_group2_other_tab(i).transaction_id );
2249
2250 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
2251 ,G_MODULE_HEAD || l_routine || '.gp2_item_id'
2252 ,'Inventory Item Id:' || p_inventory_item_id || ' ' || 'Cost Group Id:'
2253 || p_cg_tab(l_current_index).cost_group_id || ' Period Id:' || p_period_id
2254 );
2255 END IF;
2256
2257 -- Get Expense Flag
2258 Get_exp_flag(p_item_id => p_inventory_item_id
2259 ,p_org_id => l_group2_other_tab(i).organization_id
2260 ,p_subinventory_code => l_group2_other_tab(i).subinventory_code
2261 ,x_exp_flag => l_exp_flag
2262 ,x_exp_item => l_exp_item
2263 );
2264
2265 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2266 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
2267 ,G_MODULE_HEAD || l_routine || '.exp_flag_wip'
2268 ,'Exp Flag:' || l_exp_flag || ' ' ||
2269 'Exp Item:' || l_exp_item
2270 );
2271 END IF;
2272
2273 IF (l_group2_other_tab(i).transaction_source_type_id = 5 AND l_group2_other_tab(i).transaction_action_id <> 2) THEN
2274
2275 -- ===========================================================
2276 -- insert into cppb
2277 -- ===========================================================
2278 l_error_num := 0;
2279
2280 IF (CSTPPINV.l_item_id_tbl.COUNT >= 1000) THEN
2281 CSTPPWAC.insert_into_cppb(i_pac_period_id => p_period_id
2282 ,i_cost_group_id => p_cg_tab(l_current_index).cost_group_id
2283 ,i_txn_category => l_txn_category
2284 ,i_user_id => p_user_id
2285 ,i_login_id => p_login_id
2286 ,i_request_id => p_req_id
2287 ,i_prog_id => p_prg_id
2288 ,i_prog_appl_id => p_prg_appid
2289 ,o_err_num => l_error_num
2290 ,o_err_code => l_error_code
2291 ,o_err_msg => l_error_msg
2292 );
2293 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2294 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
2295 ,G_MODULE_HEAD || l_routine || '.inscppb10'
2296 ,'After calling insert_into_cppb:'|| l_error_num || l_error_code || l_error_msg
2297 );
2298 END IF;
2299 END IF;
2300
2301 IF l_error_num = 0 THEN
2302
2303 -- Invoke Charge WIP Material
2304 CSTPPWMT.charge_wip_material( p_pac_period_id => p_period_id
2305 , p_cost_group_id => p_cg_tab(l_current_index).cost_group_id
2306 , p_txn_id => l_group2_other_tab(i).transaction_id
2307 , p_exp_item => l_exp_item
2308 , p_exp_flag => l_exp_flag
2309 , p_legal_entity => p_legal_entity
2310 , p_cost_type_id => p_cost_type_id
2311 , p_cost_method => p_cost_method
2312 , p_pac_rates_id => p_pac_rates_id
2313 , p_material_relief_algorithm => p_mat_relief_algorithm
2314 , p_master_org_id => p_cg_tab(l_current_index).master_org_id
2315 , p_uom_control => p_uom_control
2316 , p_user_id => p_user_id
2317 , p_login_id => p_login_id
2318 , p_request_id => p_req_id
2319 , p_prog_id => p_prg_id
2320 , p_prog_app_id => p_prg_appid
2321 , p_txn_category => l_txn_category
2322 , x_cost_method_hook => l_hook_used
2323 , x_err_num => l_error_num
2324 , x_err_code => l_error_code
2325 , x_err_msg => l_error_msg
2326 );
2327
2328 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2329 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
2330 ,G_MODULE_HEAD || l_routine || '.gp2_charge_wip'
2331 ,'Charge WIP Material:'|| l_error_num || ' ' ||
2332 l_error_code || ' ' || l_error_msg
2333 );
2334 END IF;
2335
2336 END IF; -- error number check
2337
2338 IF l_error_num <> 0 THEN
2339 error_transaction_id := l_group2_other_tab(i).transaction_id;
2340 RAISE group2_other_except;
2341 END IF;
2342
2343 ELSE
2344
2345 -- insert into cppb
2346 l_error_num := 0;
2347
2348 IF (CSTPPINV.l_item_id_tbl.COUNT >= 1000) THEN
2349 CSTPPWAC.insert_into_cppb(i_pac_period_id => p_period_id
2350 ,i_cost_group_id => p_cg_tab(l_current_index).cost_group_id
2351 ,i_txn_category => l_txn_category
2352 ,i_user_id => p_user_id
2353 ,i_login_id => p_login_id
2354 ,i_request_id => p_req_id
2355 ,i_prog_id => p_prg_id
2356 ,i_prog_appl_id => p_prg_appid
2357 ,o_err_num => l_error_num
2358 ,o_err_code => l_error_code
2359 ,o_err_msg => l_error_msg
2360 );
2361 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2362 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
2363 ,G_MODULE_HEAD || l_routine || '.inscppb11'
2364 ,'After calling insert_into_cppb:'|| l_error_num || l_error_code || l_error_msg
2365 );
2366 END IF;
2367
2368 IF l_error_num <> 0 THEN
2369 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
2370 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
2371 FND_MESSAGE.set_token('MESSAGE', 'Error in insert/update cpbb for cost group id '
2372 ||p_cg_tab(l_current_index).cost_group_id||' ( '||l_error_code||' ) '||l_error_msg);
2373 FND_MSG_PUB.Add;
2374 RAISE FND_API.G_EXC_ERROR;
2375 END IF;
2376 END IF;
2377
2378 IF l_error_num = 0 THEN
2379
2380 CSTPPINV.cost_inv_txn(i_pac_period_id => p_period_id
2381 ,i_legal_entity => p_legal_entity
2382 ,i_cost_type_id => p_cost_type_id
2383 ,i_cost_group_id => p_cg_tab(l_current_index).cost_group_id
2384 ,i_cost_method => p_cost_method
2385 ,i_txn_id => l_group2_other_tab(i).transaction_id
2386 ,i_txn_action_id => l_group2_other_tab(i).transaction_action_id
2387 ,i_txn_src_type_id => l_group2_other_tab(i).transaction_source_type_id
2388 ,i_item_id => p_inventory_item_id
2389 ,i_txn_qty => l_group2_other_tab(i).primary_quantity
2390 ,i_txn_org_id => l_group2_other_tab(i).organization_id
2391 ,i_txfr_org_id => l_group2_other_tab(i).transfer_organization_id
2392 ,i_subinventory_code => l_group2_other_tab(i).subinventory_code
2393 ,i_exp_flag => l_exp_flag
2394 ,i_exp_item => l_exp_item
2395 ,i_pac_rates_id => p_pac_rates_id
2396 ,i_process_group => l_process_group
2397 ,i_master_org_id => p_cg_tab(l_current_index).master_org_id
2398 ,i_uom_control => p_uom_control
2399 ,i_user_id => p_user_id
2400 ,i_login_id => p_login_id
2401 ,i_request_id => p_req_id
2402 ,i_prog_id => p_prg_id
2403 ,i_prog_appl_id => p_prg_appid
2404 ,i_txn_category => l_txn_category
2405 ,o_err_num => l_error_num
2406 ,o_err_code => l_error_code
2407 ,o_err_msg => l_error_msg);
2408
2409 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2410 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
2411 ,G_MODULE_HEAD || l_routine || '.befcostinvtxn'
2412 ,'After calling cost_inv_txn:'|| l_error_num || l_error_code || l_error_msg);
2413 END IF;
2414
2415 l_error_num := NVL(l_error_num, 0);
2416 l_error_code := NVL(l_error_code, 'No Error');
2417 l_error_msg := NVL(l_error_msg, 'No Error');
2418
2419 END IF; -- error num check
2420
2421 IF l_error_num <> 0 THEN
2422 error_transaction_id := l_group2_other_tab(i).transaction_id;
2423 RAISE group2_other_except;
2424 END IF;
2425
2426 END IF; -- WIP issue check
2427
2428 END LOOP; -- FOR i IN 1..l_loop_count
2429 IF l_low_level_code <> -1 THEN
2430 EXIT WHEN group2_other_comp_cur%NOTFOUND;
2431 ELSIF l_low_level_code = -1 THEN
2432 EXIT WHEN group2_other_cur%NOTFOUND;
2433 END IF;
2434 END LOOP; -- FETCH loop
2435
2436 IF group2_other_cur%ISOPEN THEN
2437 CLOSE group2_other_cur;
2438 END IF;
2439
2440 IF group2_other_comp_cur%ISOPEN THEN
2441 CLOSE group2_other_comp_cur;
2442 END IF;
2443
2444 -- ======================================================
2445 -- insert left over group2 completion txns into cppb
2446 -- ======================================================
2447 l_error_num := 0;
2448
2449 IF (CSTPPINV.l_item_id_tbl.COUNT > 0) THEN
2450 CSTPPWAC.insert_into_cppb(i_pac_period_id => p_period_id
2451 ,i_cost_group_id => p_cg_tab(l_current_index).cost_group_id
2452 ,i_txn_category => l_txn_category
2453 ,i_user_id => p_user_id
2454 ,i_login_id => p_login_id
2455 ,i_request_id => p_req_id
2456 ,i_prog_id => p_prg_id
2457 ,i_prog_appl_id => p_prg_appid
2458 ,o_err_num => l_error_num
2459 ,o_err_code => l_error_code
2460 ,o_err_msg => l_error_msg
2461 );
2462
2463 l_error_num := NVL(l_error_num, 0);
2464 l_error_code := NVL(l_error_code, 'No Error');
2465 l_error_msg := NVL(l_error_msg, 'No Error');
2466
2467 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2468 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
2469 ,G_MODULE_HEAD || l_routine || '.inscppb12'
2470 ,'After calling insert_into_cppb:'|| l_error_num || l_error_code || l_error_msg
2471 );
2472 END IF;
2473
2474 END IF;
2475
2476 IF l_error_num = 0 THEN
2477 CSTPPWAC.update_item_cppb(i_pac_period_id => p_period_id
2478 ,i_cost_group_id => p_cg_tab(l_current_index).cost_group_id
2479 ,i_txn_category => l_txn_category
2480 ,i_item_id => p_inventory_item_id
2481 ,i_user_id => p_user_id
2482 ,i_login_id => p_login_id
2483 ,i_request_id => p_req_id
2484 ,i_prog_id => p_prg_id
2485 ,i_prog_appl_id => p_prg_appid
2486 ,o_err_num => l_error_num
2487 ,o_err_code => l_error_code
2488 ,o_err_msg => l_error_msg
2489 );
2490
2491 l_error_num := NVL(l_error_num, 0);
2492 l_error_code := NVL(l_error_code, 'No Error');
2493 l_error_msg := NVL(l_error_msg, 'No Error');
2494
2495 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2496 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
2497 ,G_MODULE_HEAD || l_routine || '.updcppb11'
2498 ,'After calling update_item_cppb:'|| l_error_num || l_error_code || l_error_msg
2499 );
2500 END IF;
2501
2502 END IF;
2503
2504 IF l_error_num <> 0
2505 THEN
2506 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
2507 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
2508 FND_MESSAGE.set_token('MESSAGE', 'Error in insert/update cpbb for cost group id '||p_cg_tab(l_current_index).cost_group_id||' ( '||l_error_code||' ) '||l_error_msg);
2509 FND_MSG_PUB.Add;
2510 RAISE FND_API.G_EXC_ERROR;
2511 END IF;
2512
2513
2514 EXIT WHEN l_current_index = p_cg_tab.LAST;
2515 l_current_index := p_cg_tab.NEXT(l_current_index);
2516
2517 END LOOP; -- cost group loop
2518
2519 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2520 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2521 ,G_MODULE_HEAD || l_routine || '.end'
2522 ,l_routine || '>'
2523 );
2524 END IF;
2525
2526 EXCEPTION
2527 WHEN FND_API.G_EXC_ERROR THEN
2528 RAISE FND_API.G_EXC_ERROR;
2529 WHEN group2_other_except THEN
2530 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2531 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION
2532 , G_MODULE_HEAD || l_routine || '.group2_other_exc'
2533 , 'group2_other_exc for txn_id '||error_transaction_id || l_error_code || l_error_msg
2534 );
2535 END IF;
2536 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
2537 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
2538 FND_MESSAGE.set_token('MESSAGE', 'group2_other_exc for txn_id '||error_transaction_id|| l_error_code || l_error_msg);
2539 FND_MSG_PUB.Add;
2540 RAISE FND_API.G_EXC_ERROR;
2541 WHEN OTHERS THEN
2542 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
2543 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
2544 FND_MESSAGE.set_token('MESSAGE', '('||SQLCODE||') '||SQLERRM);
2545 FND_MSG_PUB.Add;
2546 RAISE FND_API.G_EXC_ERROR;
2547
2548 END Process_Gp2_Other_Txns;
2549
2550
2551 --=========================================================================
2552 -- PROCEDURE : Process_Comp_Items
2553 -- COMMENT : This procedure processes WIP Assembly and WIP Issue txns for
2554 -- : items having completion and PCU - value change transactions
2555 -- : for items in current BOM level considering the highest BOM
2556 -- : level across cost groups
2557 --=========================================================================
2558 PROCEDURE Process_Comp_Items
2559 (p_legal_entity IN NUMBER
2560 ,p_cost_type_id IN NUMBER
2561 ,p_cost_method IN NUMBER
2562 ,p_period_id IN NUMBER
2563 ,p_start_date IN DATE
2564 ,p_end_date IN DATE
2565 ,p_prev_period_id IN NUMBER
2566 ,p_cg_tab IN CST_PERIODIC_ABSORPTION_PROC.tbl_type
2567 ,p_inventory_item_id IN NUMBER
2568 ,p_uom_control IN NUMBER
2569 ,p_pac_rates_id IN NUMBER
2570 ,p_mat_relief_algorithm IN NUMBER
2571 ,p_user_id IN NUMBER
2572 ,p_login_id IN NUMBER
2573 ,p_req_id IN NUMBER
2574 ,p_prg_id IN NUMBER
2575 ,p_prg_appid IN NUMBER
2576 )
2577 IS
2578
2579 l_routine CONSTANT VARCHAR2(30) := 'process_comp_items';
2580
2581
2582
2583 -- Variables
2584 l_current_index BINARY_INTEGER;
2585 l_cost_update_type NUMBER;
2586 l_low_level_code NUMBER;
2587
2588 cursor c_low_level_code_cur(c_pac_period_id NUMBER
2589 ,c_cost_group_id NUMBER
2590 ,c_inventory_item_id NUMBER
2591 )
2592 IS
2593 SELECT
2594 low_level_code
2595 FROM cst_pac_low_level_codes
2596 WHERE pac_period_id = c_pac_period_id
2597 AND cost_group_id = c_cost_group_id
2598 AND inventory_item_id = c_inventory_item_id;
2599
2600 BEGIN
2601
2602 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2603 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2604 ,G_MODULE_HEAD || l_routine || '.begin'
2605 ,l_routine || '<'
2606 );
2607 END IF;
2608
2609
2610 l_current_index := p_cg_tab.FIRST;
2611
2612 LOOP
2613 -- Get Low Level Code for the Item in that cost group
2614 OPEN c_low_level_code_cur(p_period_id
2615 ,p_cg_tab(l_current_index).cost_group_id
2616 ,p_inventory_item_id
2617 );
2618 FETCH c_low_level_code_cur
2619 INTO l_low_level_code;
2620
2621 -- completion item in the cost group
2622 -- note that an item can exist as a completion item in one cost group and
2623 -- no completion in another cost group
2624 IF c_low_level_code_cur%FOUND THEN
2625
2626 -- ===================================================================
2627 -- Process WIP transactions
2628 -- ===================================================================
2629 -- Process non-rework assembly completion transactons
2630 CST_PERIODIC_ABSORPTION_PROC.Process_Non_Rework_Comps
2631 (p_period_id => p_period_id
2632 ,p_start_date => p_start_date
2633 ,p_end_date => p_end_date
2634 ,p_prev_period_id => p_prev_period_id
2635 ,p_cost_group_id => p_cg_tab(l_current_index).cost_group_id
2636 ,p_inventory_item_id => p_inventory_item_id
2637 ,p_cost_type_id => p_cost_type_id
2638 ,p_legal_entity => p_legal_entity
2639 ,p_cost_method => p_cost_method
2640 ,p_pac_rates_id => p_pac_rates_id
2641 ,p_master_org_id => p_cg_tab(l_current_index).master_org_id
2642 ,p_mat_relief_algorithm => p_mat_relief_algorithm
2643 ,p_uom_control => p_uom_control
2644 ,p_low_level_code => l_low_level_code
2645 ,p_user_id => p_user_id
2646 ,p_login_id => p_login_id
2647 ,p_req_id => p_req_id
2648 ,p_prg_id => p_prg_id
2649 ,p_prg_appid => p_prg_appid);
2650
2651 -- Process rework assembly issue and completion transactons
2652 CST_PERIODIC_ABSORPTION_PROC.Process_Rework_Issue_Comps
2653 (p_period_id => p_period_id
2654 ,p_start_date => p_start_date
2655 ,p_end_date => p_end_date
2656 ,p_prev_period_id => p_prev_period_id
2657 ,p_cost_group_id => p_cg_tab(l_current_index).cost_group_id
2658 ,p_inventory_item_id => p_inventory_item_id
2659 ,p_cost_type_id => p_cost_type_id
2660 ,p_legal_entity => p_legal_entity
2661 ,p_cost_method => p_cost_method
2662 ,p_pac_rates_id => p_pac_rates_id
2663 ,p_master_org_id => p_cg_tab(l_current_index).master_org_id
2664 ,p_mat_relief_algorithm => p_mat_relief_algorithm
2665 ,p_uom_control => p_uom_control
2666 ,p_low_level_code => l_low_level_code
2667 ,p_user_id => p_user_id
2668 ,p_login_id => p_login_id
2669 ,p_req_id => p_req_id
2670 ,p_prg_id => p_prg_id
2671 ,p_prg_appid => p_prg_appid);
2672
2673 END IF;
2674
2675 CLOSE c_low_level_code_cur;
2676
2677 EXIT WHEN l_current_index = p_cg_tab.LAST;
2678
2679 l_current_index := p_cg_tab.NEXT(l_current_index);
2680
2681 END LOOP;
2682
2683
2684 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2685 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2686 ,G_MODULE_HEAD || l_routine || '.end'
2687 ,l_routine || '>'
2688 );
2689 END IF;
2690 EXCEPTION
2691 WHEN FND_API.G_EXC_ERROR THEN
2692 RAISE FND_API.G_EXC_ERROR;
2693 WHEN OTHERS THEN
2694 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
2695 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
2696 FND_MESSAGE.set_token('MESSAGE', '('||SQLCODE||') '||SQLERRM);
2697 FND_MSG_PUB.Add;
2698 RAISE FND_API.G_EXC_ERROR;
2699 END Process_Comp_Items;
2700
2701
2702 --=========================================================================
2703 -- PROCEDURE : Get_Absorption_Level_Of_Items PRIVATE
2704 -- COMMENT : This procedure determines the Absorption Level across
2705 -- : cost groups for each valid inventory item
2706 -- : Absorption Level is determined based on the below criteria
2707 -- 1. BOM Highest Level across Cost Groups
2708 -- 2. Parent item should always have the higher BOM level than
2709 -- all of its child items
2710 -- If Parent item has lower BOM level than one of its child item
2711 -- then the parent item's BOM level will be bumped up to next
2712 -- higher level of its child item.
2713 --=========================================================================
2714 PROCEDURE Get_Absorption_Level_Of_Items(p_period_id IN NUMBER
2715 ,p_legal_entity_id IN NUMBER
2716 ,p_period_start_date IN DATE
2717 ,p_period_end_date IN DATE
2718 )
2719 IS
2720 l_routine CONSTANT VARCHAR2(30) := 'get_absorption_level_of_items';
2721
2722 -- Cursor retrieve items in the current absorption level code
2723 CURSOR items_in_current_absl_cur(c_pac_period_id NUMBER
2724 ,c_absorption_level_code NUMBER
2725 )
2726 IS
2727 SELECT
2728 inventory_item_id
2729 FROM cst_pac_itms_absl_codes
2730 WHERE pac_period_id = c_pac_period_id
2731 AND absorption_level_code = c_absorption_level_code
2732 FOR UPDATE OF absorption_level_code;
2733
2734 items_in_current_absl_row items_in_current_absl_cur%ROWTYPE;
2735
2736
2737 -- Cursor to print in diagnostics the items in a deadlock
2738 CURSOR items_in_deadlock_cur(c_pac_period_id NUMBER
2739 ,c_absorption_level_code NUMBER
2740 )
2741 IS
2742 SELECT distinct(mst.concatenated_segments)
2743 FROM cst_pac_itms_absl_codes cpiac, MTL_SYSTEM_ITEMS_B_KFV mst
2744 WHERE cpiac.pac_period_id = c_pac_period_id
2745 AND cpiac.absorption_level_code < c_absorption_level_code
2746 AND cpiac.inventory_item_id = mst.inventory_item_id;
2747
2748
2749 l_topmost_bom_level_code NUMBER;
2750 l_low_level_count NUMBER;
2751 l_lower_bom_level_code NUMBER;
2752 l_absorption_level_code NUMBER;
2753 l_topmost_absl_level_code NUMBER;
2754 l_min_child_absl_level_code NUMBER;
2755 l_reposition_absl_level_code NUMBER;
2756
2757 l_dead_lock_message VARCHAR2(3000) := ' ';
2758 l_item_name MTL_SYSTEM_ITEMS_B_KFV.concatenated_segments%TYPE;
2759 l_continue_loop_flag VARCHAR2(1) := 'Y';
2760
2761 BEGIN
2762
2763 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2764 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
2765 ,G_MODULE_HEAD || l_routine || '.begin'
2766 ,l_routine || '<'
2767 );
2768 END IF;
2769
2770 -- Purge cst_pac_itms_absl_codes
2771 DELETE cst_pac_itms_absl_codes
2772 WHERE pac_period_id = p_period_id;
2773
2774 INSERT INTO cst_pac_itms_absl_codes
2775 (inventory_item_id
2776 ,pac_period_id
2777 ,absorption_level_code
2778 ,process_flag
2779 )
2780 SELECT
2781 distinct(inventory_item_id)
2782 ,p_period_id
2783 ,1000
2784 ,'N'
2785 FROM
2786 (
2787 SELECT /*+ leading (MMT) INDEX (MMT MTL_MATERIAL_TRANSACTIONS_N5)*/
2788 mmt.inventory_item_id inventory_item_id
2789 FROM mtl_material_transactions mmt, cst_cost_groups ccg, cst_cost_group_assignments ccga
2790 WHERE mmt.transaction_date between p_period_start_date AND p_period_end_date
2791 AND mmt.organization_id = nvl(mmt.owning_organization_id, mmt.organization_id)
2792 AND nvl(mmt.owning_tp_type,2) = 2
2793 AND ccg.legal_entity = p_legal_entity_id
2794 AND ccga.organization_id = mmt.organization_id
2795 AND ccga.cost_group_id = ccg.cost_group_id
2796 AND NOT EXISTS (SELECT /*+ no_unnest*/ 'X'
2797 FROM cst_pac_low_level_codes cpllc
2798 WHERE cpllc.inventory_item_id = mmt.inventory_item_id
2799 AND cpllc.pac_period_id = p_period_id)
2800 UNION ALL
2801 SELECT mmt.inventory_item_id inventory_item_id
2802 FROM mtl_material_transactions mmt, cst_pac_periods cpp, cst_cost_groups ccg
2803 WHERE mmt.transaction_date between p_period_start_date AND p_period_end_date
2804 AND mmt.transaction_action_id = 24
2805 AND mmt.transaction_source_type_id = 14
2806 AND mmt.transaction_type_id = 26
2807 AND mmt.value_change IS NOT NULL
2808 AND NVL(mmt.org_cost_group_id,-1) = ccg.cost_group_id
2809 AND ccg.legal_entity = p_legal_entity_id
2810 AND NVL(mmt.cost_type_id,-1) = cpp.cost_type_id
2811 AND cpp.pac_period_id = p_period_id
2812 AND mmt.primary_quantity = 0
2813 AND NOT EXISTS (
2814 SELECT /*+ no_unnest*/ 'X'
2815 FROM cst_pac_low_level_codes cpllc
2816 WHERE cpllc.inventory_item_id = mmt.inventory_item_id
2817 AND cpllc.pac_period_id = p_period_id)
2818 UNION ALL
2819 SELECT /*+ ORDERED INDEX (MMT MTL_MATERIAL_TRANSACTIONS_N5)*/
2820 mmt.inventory_item_id inventory_item_id
2821 FROM cst_cost_groups ccg,
2822 cst_cost_group_assignments ccga,
2823 mtl_interorg_parameters mip,
2824 mtl_material_transactions mmt
2825 WHERE mmt.transaction_date between p_period_start_date AND p_period_end_date
2826 AND mmt.organization_id = nvl(mmt.owning_organization_id, mmt.organization_id)
2827 AND nvl(mmt.owning_tp_type,2) = 2
2828 AND ccg.legal_entity = p_legal_entity_id
2829 AND ( (mmt.transaction_action_id = 21
2830 and nvl(mmt.fob_point,mip.fob_point) = 1
2831 and ccga.organization_id = mmt.transfer_organization_id
2832 and mip.from_organization_id = mmt.organization_id
2833 and mip.to_organization_id = mmt.transfer_organization_id
2834 and mip.to_organization_id = ccga.organization_id
2835 )
2836 OR
2837 (mmt.transaction_action_id = 12
2838 and nvl(mmt.fob_point,mip.fob_point) = 2
2839 and ccga.organization_id = mmt.transfer_organization_id
2840 and mip.from_organization_id = mmt.transfer_organization_id
2841 and mip.to_organization_id = mmt.organization_id
2842 and mip.from_organization_id = ccga.organization_id
2843 )
2844 )
2845 AND ccga.cost_group_id = ccg.cost_group_id
2846 AND ( MIP.from_organization_id = ccga.organization_id
2847 OR MIP.to_organization_id = ccga.organization_id )
2848 AND NOT EXISTS (SELECT /*+ no_unnest*/ 'X'
2849 FROM cst_pac_low_level_codes cpllc
2850 WHERE cpllc.inventory_item_id = mmt.inventory_item_id
2851 AND cpllc.pac_period_id = p_period_id)
2852 );
2853
2854 -- initialize lower bom level code
2855 l_lower_bom_level_code := 999;
2856
2857 -- Get top most BOM level code across all items in all cost groups
2858 SELECT min(low_level_code) top_most_bom_level_code
2859 , count(low_level_code) low_level_count
2860 INTO l_topmost_bom_level_code
2861 ,l_low_level_count
2862 FROM cst_pac_low_level_codes
2863 WHERE pac_period_id = p_period_id;
2864
2865 IF l_low_level_count = 0 THEN
2866 -- Completion Items not exist
2867 l_topmost_bom_level_code := 1001;
2868 END IF;
2869
2870 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2871 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
2872 ,G_MODULE_HEAD || l_routine || '.topmostbomlvl'
2873 ,'Topmost bom level code across items in all CGs:' || l_topmost_bom_level_code
2874 );
2875 END IF;
2876
2877
2878 -- Any completion item exists
2879 IF l_topmost_bom_level_code <= 1000 THEN
2880
2881 INSERT INTO cst_pac_itms_absl_codes
2882 (pac_period_id
2883 ,inventory_item_id
2884 ,absorption_level_code
2885 ,process_flag
2886 )
2887 SELECT
2888 p_period_id
2889 ,cpllc.inventory_item_id
2890 ,min(low_level_code) bom_highest_level_code
2891 ,'N'
2892 FROM cst_pac_low_level_codes cpllc
2893 WHERE pac_period_id = p_period_id
2894 GROUP BY inventory_item_id;
2895
2896 END IF; -- check for any completion item
2897
2898 -- ===========================================================
2899 -- Determine Absorption Level Codes
2900 -- ===========================================================
2901 IF l_topmost_bom_level_code >= 1000 THEN
2902 l_continue_loop_flag := 'N';
2903 END IF;
2904
2905 WHILE (l_continue_loop_flag = 'Y') LOOP
2906 l_continue_loop_flag := 'N';
2907 l_absorption_level_code := 999;
2908
2909 -- Retrieve topmost absorption level code
2910 SELECT min(absorption_level_code)
2911 INTO l_topmost_absl_level_code
2912 FROM cst_pac_itms_absl_codes
2913 WHERE pac_period_id = p_period_id;
2914
2915 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2916 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
2917 ,G_MODULE_HEAD || l_routine || '.topmostabscode'
2918 ,'Topmost absorption level code:' || l_topmost_absl_level_code
2919 );
2920 END IF;
2921
2922 WHILE (l_absorption_level_code >= l_topmost_absl_level_code) LOOP
2923
2924 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2925 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
2926 ,G_MODULE_HEAD || l_routine || '.abslvcode'
2927 ,'Current absorption level code:' || l_absorption_level_code
2928 );
2929 END IF;
2930
2931 -- Retrieve items in the current absorption level code
2932 OPEN items_in_current_absl_cur(p_period_id
2933 ,l_absorption_level_code
2934 );
2935 FETCH items_in_current_absl_cur
2936 INTO items_in_current_absl_row;
2937
2938 IF items_in_current_absl_cur%NOTFOUND THEN
2939 BEGIN
2940 --CST_PAC_ABS_DEAD_LOCK is "The following items with absorption_level_code greater than 'ABS_CODE' are in a loop halting the process in deadlock. 'ITEMS'"
2941 OPEN items_in_deadlock_cur(p_period_id,l_absorption_level_code);
2942
2943 FETCH items_in_deadlock_cur
2944 INTO l_item_name;
2945 l_dead_lock_message := l_item_name;
2946 FETCH items_in_deadlock_cur
2947 INTO l_item_name;
2948 WHILE (items_in_deadlock_cur%FOUND) LOOP
2949 l_dead_lock_message := l_dead_lock_message || ', '|| l_item_name;
2950 FETCH items_in_deadlock_cur
2951 INTO l_item_name;
2952 END LOOP;
2953 CLOSE items_in_deadlock_cur;
2954
2955 EXCEPTION
2956 WHEN OTHERS THEN
2957 FND_MESSAGE.Set_Name('BOM', 'CST_PAC_ABS_DEAD_LOCK');
2958 FND_MESSAGE.Set_token('ABS_CODE', l_absorption_level_code);
2959 FND_MESSAGE.set_token('ITEMS', l_dead_lock_message);
2960 FND_MSG_PUB.Add;
2961 RAISE FND_API.G_EXC_ERROR;
2962 END;
2963
2964 FND_MESSAGE.Set_Name('BOM', 'CST_PAC_ABS_DEAD_LOCK');
2965 FND_MESSAGE.Set_token('ABS_CODE', l_absorption_level_code);
2966 FND_MESSAGE.set_token('ITEMS', l_dead_lock_message);
2967 FND_MSG_PUB.Add;
2968 RAISE FND_API.G_EXC_ERROR;
2969 END IF;
2970
2971
2972 WHILE (items_in_current_absl_cur%FOUND) LOOP
2973
2974 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2975 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
2976 ,G_MODULE_HEAD || l_routine || '.absitem'
2977 ,'Current Absorption level item:' || items_in_current_absl_row.inventory_item_id
2978 );
2979 END IF;
2980
2981 -- ==================================================================================================
2982 -- Check whether the retrieved item has child items which is in higher absorption level than
2983 -- its retrieved parent item. If so, then bump up the absorption level of the retrieved item
2984 -- one level up than its child item
2985 -- Criteria: Parent item should always have the higher absorption level (lower absorption level code)
2986 -- than all of its child items
2987 -- ==================================================================================================
2988 SELECT min(cpiac.absorption_level_code) min_child_absl_level_code
2989 INTO l_min_child_absl_level_code
2990 FROM cst_pac_itms_absl_codes cpiac
2991 WHERE cpiac.pac_period_id = p_period_id
2992 AND cpiac.inventory_item_id IN (SELECT DISTINCT cpet.component_item_id
2993 FROM cst_pac_explosion_temp cpet
2994 WHERE cpet.pac_period_id = cpiac.pac_period_id
2995 AND cpet.assembly_item_id = items_in_current_absl_row.inventory_item_id
2996 AND cpet.component_item_id <> cpet.assembly_item_id
2997 );
2998
2999 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3000 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
3001 ,G_MODULE_HEAD || l_routine || '.minchildabslv'
3002 ,'Highest absorption level across its child items:' || l_min_child_absl_level_code
3003 );
3004 END IF;
3005
3006 -- Check whether the absorption level of parent item is lower than or equal to its child item
3007 -- NOTE: Lower absorption level will have higher absorption level code
3008 IF l_absorption_level_code >= l_min_child_absl_level_code THEN
3009
3010 -- bump up the absorption level of parent item
3011 l_reposition_absl_level_code := l_min_child_absl_level_code - 1;
3012
3013 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3014 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
3015 ,G_MODULE_HEAD || l_routine || '.reposabslvcode'
3016 ,'Reposition Absorption Level Code:' || l_reposition_absl_level_code || ' Item Id:' || items_in_current_absl_row.inventory_item_id
3017 );
3018 END IF;
3019
3020 UPDATE CST_PAC_ITMS_ABSL_CODES
3021 SET absorption_level_code = l_reposition_absl_level_code
3022 WHERE CURRENT OF items_in_current_absl_cur;
3023 l_continue_loop_flag := 'Y';
3024 END IF;
3025
3026 FETCH items_in_current_absl_cur
3027 INTO items_in_current_absl_row;
3028
3029 END LOOP; -- items in current absorption level
3030
3031 CLOSE items_in_current_absl_cur;
3032
3033 l_absorption_level_code := l_absorption_level_code - 1;
3034 END LOOP; -- absorption level loop
3035
3036 END LOOP; -- absorption loop count
3037
3038 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3039 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3040 ,G_MODULE_HEAD || l_routine || '.end'
3041 ,l_routine || '>'
3042 );
3043 END IF;
3044
3045
3046 EXCEPTION
3047 WHEN FND_API.G_EXC_ERROR THEN
3048 RAISE FND_API.G_EXC_ERROR;
3049 WHEN OTHERS THEN
3050 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
3051 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
3052 FND_MESSAGE.set_token('MESSAGE', '('||SQLCODE||') '||SQLERRM);
3053 FND_MSG_PUB.Add;
3054 RAISE FND_API.G_EXC_ERROR;
3055
3056 END Get_Absorption_Level_Of_Items;
3057
3058 --========================================================================
3059 -- PROCEDURE : Validate_Uncosted_Txns PRIVATE
3060 -- COMMENT : This procedure validates for any uncosted transactions in
3061 -- : all cost groups
3062 -- : Procedure is invoked during run options 3 - resume for
3063 -- : consecutive iterations
3064 --========================================================================
3065 PROCEDURE Validate_Uncosted_Txns
3066 (p_legal_entity_id IN NUMBER
3067 ,p_pac_period_id IN NUMBER
3068 ,p_period_start_date IN DATE
3069 ,p_period_end_date IN DATE
3070 )
3071 IS
3072
3073 l_routine CONSTANT VARCHAR2(30) := 'validate_uncosted_txns';
3074
3075 -- Local Variables
3076 l_pending_txns BOOLEAN;
3077 l_backdated_txns BOOLEAN;
3078 l_count_rows NUMBER;
3079
3080 -- exceptions
3081 l_pending_txns_except EXCEPTION;
3082 l_backdated_txns_except EXCEPTION;
3083
3084 BEGIN
3085
3086 FND_FILE.put_line
3087 ( FND_FILE.log
3088 , '>> CST_PERIODIC_ABSORPTION_PROC:validate_uncosted_txns'
3089 );
3090
3091 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3092 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3093 ,G_MODULE_HEAD || l_routine || '.begin'
3094 ,l_routine || '<'
3095 );
3096 END IF;
3097
3098 -- initialize boolean variables
3099 l_pending_txns := FALSE;
3100 l_backdated_txns := FALSE;
3101 l_count_rows := 0;
3102
3103
3104 -- ===========================================================
3105 -- Validate Pending Transactions
3106 -- ===========================================================
3107 -- Check for pending rows in MMTT
3108 l_count_rows := 0;
3109
3110 SELECT count(1)
3111 INTO l_count_rows
3112 FROM mtl_material_transactions_temp mmtt
3113 WHERE NVL(mmtt.transaction_status,0) <> 2
3114 AND CST_PAC_ITERATION_PROCESS_PVT.Check_Cst_Group_Org(mmtt.organization_id) = 'Y'
3115 AND mmtt.transaction_date BETWEEN p_period_start_date AND p_period_end_date
3116 AND ROWNUM = 1;
3117
3118
3119 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3120 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
3121 ,G_MODULE_HEAD || l_routine || '.pendmmtt'
3122 ,'Pending Txns in MMTT:' || l_count_rows
3123 );
3124 END IF;
3125
3126 IF (l_count_rows <> 0) THEN
3127 l_pending_txns := TRUE;
3128 RAISE l_pending_txns_except;
3129 END IF;
3130
3131
3132 -- Check for pending rows in MTI
3133 l_count_rows := 0;
3134
3135 SELECT count(1)
3136 INTO l_count_rows
3137 FROM mtl_transactions_interface mti
3138 WHERE CST_PAC_ITERATION_PROCESS_PVT.Check_Cst_Group_Org(mti.organization_id) = 'Y'
3139 AND mti.transaction_date BETWEEN p_period_start_date AND p_period_end_date
3140 AND ROWNUM = 1;
3141
3142 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3143 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
3144 ,G_MODULE_HEAD || l_routine || '.pendmti'
3145 ,'Pending Txns in MTI:' || l_count_rows
3146 );
3147 END IF;
3148
3149 IF (l_count_rows <> 0) THEN
3150 l_pending_txns := TRUE;
3151 RAISE l_pending_txns_except;
3152 END IF;
3153
3154
3155 -- Check for pending rows in WCTI
3156 l_count_rows := 0;
3157
3158 SELECT count(1)
3159 INTO l_count_rows
3160 FROM wip_cost_txn_interface wcti
3161 WHERE CST_PAC_ITERATION_PROCESS_PVT.Check_Cst_Group_Org(wcti.organization_id) = 'Y'
3162 AND wcti.transaction_date BETWEEN p_period_start_date AND p_period_end_date
3163 AND ROWNUM = 1;
3164
3165 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3166 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
3167 ,G_MODULE_HEAD || l_routine || '.pendwcti'
3168 ,'Pending Txns in WCTI:' || l_count_rows
3169 );
3170 END IF;
3171
3172 IF (l_count_rows <> 0) THEN
3173 l_pending_txns := TRUE;
3174 RAISE l_pending_txns_except;
3175 END IF;
3176
3177
3178 -- Check for pending rows in RTI
3179 l_count_rows := 0;
3180
3181 SELECT count(1)
3182 INTO l_count_rows
3183 FROM rcv_transactions_interface rti
3184 WHERE rti.to_organization_code IN
3185 (SELECT ood.organization_code
3186 FROM cst_organization_definitions ood
3187 WHERE CST_PAC_ITERATION_PROCESS_PVT.Check_Cst_Group_Org(ood.organization_id) = 'Y'
3188 )
3189 AND rti.transaction_date BETWEEN p_period_start_date AND p_period_end_date
3190 AND ROWNUM = 1;
3191
3192 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3193 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
3194 ,G_MODULE_HEAD || l_routine || '.pendrti'
3195 ,'Pending Txns in RTI:' || l_count_rows
3196 );
3197 END IF;
3198
3199 IF (l_count_rows <> 0) THEN
3200 l_pending_txns := TRUE;
3201 RAISE l_pending_txns_except;
3202 END IF;
3203
3204 -- ========================================================
3205 -- Validate for backdated transactions
3206 -- ========================================================
3207 -- Check for backdated txns in MMT
3208 l_count_rows := 0;
3209
3210 SELECT count(1)
3211 INTO l_count_rows
3212 FROM mtl_material_transactions mmt
3213 WHERE mmt.creation_date > ( SELECT MIN(cppp.process_date)
3214 FROM cst_pac_process_phases cppp
3215 WHERE
3216 ( (cppp.process_phase <= 4 OR cppp.process_phase = 7)
3217 AND cppp.process_upto_date IS NOT NULL)
3218 AND cppp.pac_period_id = p_pac_period_id
3219 AND CST_PAC_ITERATION_PROCESS_PVT.Check_Cst_Group(cppp.cost_group_id) = 'Y'
3220 )
3221 AND CST_PAC_ITERATION_PROCESS_PVT.Check_Cst_Group_Org(mmt.organization_id) = 'Y'
3222 AND mmt.transaction_date BETWEEN p_period_start_date AND p_period_end_date
3223 AND ROWNUM = 1;
3224
3225 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3226 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
3227 ,G_MODULE_HEAD || l_routine || '.backmmt'
3228 ,'Backdated Txns in MMT:' || l_count_rows
3229 );
3230 END IF;
3231
3232 IF (l_count_rows <> 0) THEN
3233 l_backdated_txns := TRUE;
3234 RAISE l_backdated_txns_except;
3235 END IF;
3236
3237
3238 -- Check for backdated txns in WT
3239 l_count_rows := 0;
3240
3241 SELECT count(1)
3242 INTO l_count_rows
3243 FROM wip_transactions wt
3244 WHERE wt.creation_date > ( SELECT MIN(cppp.process_date)
3245 FROM cst_pac_process_phases cppp
3246 WHERE
3247 ( (cppp.process_phase <= 4 OR cppp.process_phase = 7)
3248 AND cppp.process_upto_date IS NOT NULL)
3249 AND cppp.pac_period_id = p_pac_period_id
3250 AND CST_PAC_ITERATION_PROCESS_PVT.Check_Cst_Group(cppp.cost_group_id) = 'Y'
3251 )
3252 AND CST_PAC_ITERATION_PROCESS_PVT.Check_Cst_Group_Org(wt.organization_id) = 'Y'
3253 AND wt.transaction_date BETWEEN p_period_start_date AND p_period_end_date
3254 AND ROWNUM = 1;
3255
3256 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3257 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
3258 ,G_MODULE_HEAD || l_routine || '.backwt'
3259 ,'Backdated Txns in WT:' || l_count_rows
3260 );
3261 END IF;
3262
3263 IF (l_count_rows <> 0) THEN
3264 l_backdated_txns := TRUE;
3265 RAISE l_backdated_txns_except;
3266 END IF;
3267
3268
3269 -- Check for backdated txns in RT
3270 l_count_rows := 0;
3271
3272 SELECT count(1)
3273 INTO l_count_rows
3274 FROM rcv_transactions rt
3275 WHERE rt.creation_date > (SELECT MIN(cppp.process_date)
3276 FROM cst_pac_process_phases cppp
3277 WHERE
3278 ( (cppp.process_phase <= 4 OR cppp.process_phase = 7)
3279 AND cppp.process_upto_date IS NOT NULL)
3280 AND cppp.pac_period_id = p_pac_period_id
3281 AND CST_PAC_ITERATION_PROCESS_PVT.Check_Cst_Group(cppp.cost_group_id) = 'Y'
3282 )
3283 AND CST_PAC_ITERATION_PROCESS_PVT.Check_Cst_Group_Org(rt.organization_id) = 'Y'
3284 AND rt.transaction_date BETWEEN p_period_start_date AND p_period_end_date
3285 AND ROWNUM = 1;
3286
3287 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3288 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
3289 ,G_MODULE_HEAD || l_routine || '.backrt'
3290 ,'Backdated Txns in RT:' || l_count_rows
3291 );
3292 END IF;
3293
3294 IF (l_count_rows <> 0) THEN
3295 l_backdated_txns := TRUE;
3296 RAISE l_backdated_txns_except;
3297 END IF;
3298
3299
3300 -- Check for backdated txns in RAE
3301 l_count_rows := 0;
3302
3303 SELECT count(1)
3304 INTO l_count_rows
3305 FROM rcv_accounting_events rae
3306 WHERE rae.creation_date > ( SELECT MIN(cppp.process_date)
3307 FROM cst_pac_process_phases cppp
3308 WHERE
3309 ( (cppp.process_phase <= 4 OR cppp.process_phase = 7)
3310 AND cppp.process_upto_date IS NOT NULL)
3311 AND cppp.pac_period_id = p_pac_period_id
3312 AND CST_PAC_ITERATION_PROCESS_PVT.Check_Cst_Group(cppp.cost_group_id) = 'Y'
3313 )
3314 AND CST_PAC_ITERATION_PROCESS_PVT.Check_Cst_Group_Org(rae.organization_id) = 'Y'
3315 AND rae.transaction_date BETWEEN p_period_start_date AND p_period_end_date
3316 AND rae.event_type_id IN (7,8, 9, 10)
3317 AND ROWNUM = 1;
3318
3319 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3320 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
3321 ,G_MODULE_HEAD || l_routine || '.backrae'
3322 ,'Backdated Txns in RAE:' || l_count_rows
3323 );
3324 END IF;
3325
3326 IF (l_count_rows <> 0) THEN
3327 l_backdated_txns := TRUE;
3328 RAISE l_backdated_txns_except;
3329 END IF;
3330
3331
3332 FND_FILE.put_line
3333 ( FND_FILE.log
3334 , '<< CST_PERIODIC_ABSORPTION_PROC:validate_uncosted_txns'
3335 );
3336
3337 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3338 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3339 ,G_MODULE_HEAD || l_routine || '.end'
3340 ,l_routine || '>'
3341 );
3342 END IF;
3343
3344
3345 EXCEPTION
3346 WHEN l_pending_txns_except THEN
3347 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3348 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION
3349 , G_MODULE_HEAD || l_routine || '.pendtxn1_exc'
3350 , 'Pending Trasactions exist. Process all the pending transactions by import through applications interface'
3351 );
3352 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION
3353 , G_MODULE_HEAD || l_routine || '.pendtxn2_exc'
3354 , 'Rerun the processor with run options Start'
3355 );
3356 END IF;
3357
3358 FND_MESSAGE.Set_Name('BOM', 'CST_PAC_PENDING_TXN');
3359 FND_MSG_PUB.Add;
3360 RAISE FND_API.G_EXC_ERROR;
3361
3362 WHEN l_backdated_txns_except THEN
3363 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3364 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION
3365 , G_MODULE_HEAD || l_routine || '.backdated_exc'
3366 , 'Backdated Trasactions exist. Rerun the processor with run options Start');
3367 END IF;
3368
3369 FND_MESSAGE.Set_Name('BOM', 'CST_PAC_BACKDATED_TXN');
3370 FND_MSG_PUB.Add;
3371 RAISE FND_API.G_EXC_ERROR;
3372
3373
3374 END Validate_Uncosted_Txns;
3375
3376 -- =======================================================================
3377 -- PROCEDURE : Insert_Ending_Balance_All_Cgs PRIVATE
3378 -- COMMENT : This procedure invokes CSTPPWAC.insert_ending_balance for
3379 -- : each cost group
3380 -- : Inserts to CPPB from CPIC, CPICD, CPQL at the end of PAC
3381 -- : Period
3382 -- =======================================================================
3383 PROCEDURE Insert_Ending_Balance_All_Cgs
3384 (p_pac_period_id IN NUMBER
3385 ,p_cg_tab IN CST_PERIODIC_ABSORPTION_PROC.tbl_type
3386 ,p_end_date IN DATE
3387 ,p_user_id IN NUMBER
3388 ,p_login_id IN NUMBER
3389 ,p_req_id IN NUMBER
3390 ,p_prg_id IN NUMBER
3391 ,p_prg_appid IN NUMBER
3392 )
3393 IS
3394
3395 -- routine name local constant variable
3396 l_routine CONSTANT VARCHAR2(30) := 'Insert_Ending_Balance_All_Cgs';
3397
3398 l_error_num NUMBER;
3399 l_error_code VARCHAR2(240);
3400 l_error_msg VARCHAR2(240);
3401
3402 l_cg_idx BINARY_INTEGER;
3403
3404 BEGIN
3405
3406 FND_FILE.put_line
3407 ( FND_FILE.log
3408 , '>> CST_PERIODIC_ABSORPTION_PROC.Insert_Ending_Balance_All_Cgs'
3409 );
3410
3411 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3412 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3413 ,G_MODULE_HEAD || l_routine || '.begin'
3414 ,l_routine || '<'
3415 );
3416 END IF;
3417
3418 -- ============================================================
3419 -- for each cost group, insert into CPPB from CPIC, CPICD, CPQL
3420 -- ============================================================
3421 l_cg_idx := p_cg_tab.FIRST;
3422 LOOP
3423 CSTPPWAC.insert_ending_balance(i_pac_period_id => p_pac_period_id
3424 ,i_cost_group_id => p_cg_tab(l_cg_idx).cost_group_id
3425 ,i_user_id => p_user_id
3426 ,i_login_id => p_login_id
3427 ,i_request_id => p_req_id
3428 ,i_prog_id => p_prg_id
3429 ,i_prog_appl_id => p_prg_appid
3430 ,o_err_num => l_error_num
3431 ,o_err_code => l_error_code
3432 ,o_err_msg => l_error_msg
3433 );
3434
3435 l_error_num := NVL(l_error_num,0);
3436 l_error_code := NVL(l_error_code, 'No Error');
3437 l_error_msg := NVL(l_error_msg, 'No Error');
3438
3439 IF l_error_num <> 0 THEN
3440 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
3441 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
3442 FND_MESSAGE.set_token('MESSAGE', 'Error in CSTPPWAC.insert_ending_balance for cost group id '||p_cg_tab(l_cg_idx).cost_group_id||' ( '||l_error_code||' ) '||l_error_msg);
3443 FND_MSG_PUB.Add;
3444 RAISE FND_API.G_EXC_ERROR;
3445 END IF;
3446
3447 EXIT WHEN l_cg_idx = p_cg_tab.LAST;
3448
3449 l_cg_idx := p_cg_tab.NEXT(l_cg_idx);
3450
3451 END LOOP;
3452
3453 FND_FILE.put_line
3454 ( FND_FILE.log
3455 , '<< CST_PERIODIC_ABSORPTION_PROC.Insert_Ending_Balance_All_Cgs'
3456 );
3457
3458 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3459 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3460 ,G_MODULE_HEAD || l_routine || '.end'
3461 ,l_routine || '>'
3462 );
3463 END IF;
3464
3465 EXCEPTION
3466 WHEN FND_API.G_EXC_ERROR THEN
3467 RAISE FND_API.G_EXC_ERROR;
3468 WHEN OTHERS THEN
3469 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
3470 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
3471 FND_MESSAGE.set_token('MESSAGE', '('||SQLCODE||') '||SQLERRM);
3472 FND_MSG_PUB.Add;
3473 RAISE FND_API.G_EXC_ERROR;
3474 END Insert_Ending_Balance_All_Cgs;
3475
3476 --=========================================================================
3477 -- FUNCTION : Check_For_No_Interorg_CG PRIVATE
3478 -- COMMENT : This function checks for non interorg cost groups
3479 -- by validating against CST_PAC_INTORG_ITMS_TEMP table
3480 -- CST_PAC_INTORG_ITMS_TEMP contains inventory items and Cost
3481 -- Groups which have only valid interorg txns across cost groups
3482 -- Cost Groups which have no vaid interorg txns are NOT present
3483 -- in this table
3484 -- This function is used to get those non-interorg cost groups
3485 -- for which PCU - value change txns have to be processed.
3486 --=========================================================================
3487 FUNCTION Check_For_No_Interorg_CG
3488 (p_period_id IN NUMBER
3489 ,p_cost_group_id IN NUMBER
3490 ,p_inventory_item_id IN NUMBER
3491 )
3492 RETURN VARCHAR2
3493 IS
3494 l_routine CONSTANT VARCHAR2(30) := 'Check_For_No_Interorg_CG';
3495
3496 -- Cursor to check for the cost group with any interorg transaction
3497 CURSOR c_non_interorg_cg_cur(c_pac_period_id NUMBER
3498 ,c_cost_group_id NUMBER
3499 ,c_inventory_item_id NUMBER
3500 )
3501 IS
3502 SELECT 'X'
3503 FROM CST_PAC_INTORG_ITMS_TEMP
3504 WHERE pac_period_id = c_pac_period_id
3505 AND cost_group_id = c_cost_group_id
3506 AND inventory_item_id = c_inventory_item_id;
3507
3508 l_present_cg VARCHAR2(1);
3509
3510 BEGIN
3511 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3512 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3513 , G_MODULE_HEAD || l_routine || '.begin'
3514 , l_routine || '<'
3515 );
3516 END IF;
3517
3518 OPEN c_non_interorg_cg_cur(p_period_id
3519 ,p_cost_group_id
3520 ,p_inventory_item_id
3521 );
3522
3523
3524 FETCH c_non_interorg_cg_cur
3525 INTO l_present_cg;
3526
3527 IF c_non_interorg_cg_cur%FOUND THEN
3528 -- Cost Group has valid interorg txns and therefore
3529 -- this cost group should not be processed under non-interorg
3530 -- cost group
3531 l_present_cg := 'N' ;
3532 ELSE
3533 -- Cost Group has NO valid interorg txns and therefore
3534 -- require to be processed under non-interorg cost group
3535 l_present_cg := 'Y';
3536 END IF;
3537
3538 CLOSE c_non_interorg_cg_cur;
3539
3540 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3541 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
3542 , G_MODULE_HEAD || l_routine || '.prcg'
3543 , 'Non-Interorg Cost Group(Y/N):' || p_cost_group_id || ' ' || l_present_cg
3544 );
3545 END IF;
3546
3547 RETURN l_present_cg;
3548
3549 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3550 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3551 ,G_MODULE_HEAD || l_routine || '.end'
3552 ,l_routine || '>'
3553 );
3554 END IF;
3555
3556 END Check_For_No_Interorg_CG;
3557
3558
3559 --=========================================================================
3560 -- PROCEDURE : Absorption_Cost_Process PRIVATE
3561 -- COMMENT : This procedure processes all the
3562 -- : cost owned transactions for item transactions
3563 -- : that belong to the legal entity/cost type
3564 -- : association.
3565 -- : This is a preliminary step before calling the iterative
3566 -- : procedure that processes the interorg transactions.
3567 -- : Rollup for all items by BOM level
3568 --=========================================================================
3569 PROCEDURE Absorption_Cost_Process
3570 (p_period_id IN NUMBER
3571 ,p_prev_period_id IN NUMBER
3572 ,p_legal_entity IN NUMBER
3573 ,p_cost_type_id IN NUMBER
3574 ,p_cg_tab IN CST_PERIODIC_ABSORPTION_PROC.tbl_type
3575 ,p_run_options IN NUMBER
3576 ,p_number_of_iterations IN NUMBER
3577 ,p_cost_method IN NUMBER
3578 ,p_start_date IN DATE
3579 ,p_end_date IN DATE
3580 ,p_pac_rates_id IN NUMBER
3581 ,p_mat_relief_algorithm IN NUMBER
3582 ,p_uom_control IN NUMBER
3583 ,p_tolerance IN NUMBER
3584 ,p_user_id IN NUMBER
3585 ,p_login_id IN NUMBER
3586 ,p_req_id IN NUMBER
3587 ,p_prg_id IN NUMBER
3588 ,p_prg_appid IN NUMBER
3589 )
3590 IS
3591
3592 l_routine CONSTANT VARCHAR2(30) := 'absorption_cost_process';
3593
3594
3595 -- Cursor retrieve items in the current absorption level code
3596 -- where the items are unprocessed
3597 CURSOR items_in_cur_absl_level_cur(c_pac_period_id NUMBER
3598 ,c_absorption_level_code NUMBER
3599 )
3600 IS
3601 SELECT
3602 inventory_item_id
3603 FROM cst_pac_itms_absl_codes
3604 WHERE pac_period_id = c_pac_period_id
3605 AND absorption_level_code = c_absorption_level_code
3606 AND process_flag = 'N';
3607
3608 cursor c_low_level_code_cur(c_pac_period_id NUMBER
3609 ,c_inventory_item_id NUMBER
3610 )
3611 IS
3612 SELECT low_level_code
3613 FROM cst_pac_low_level_codes
3614 WHERE pac_period_id = c_pac_period_id
3615 AND inventory_item_id = c_inventory_item_id
3616 AND rownum = 1;
3617
3618 -- Cursor to obtain pac low level code at each cost group
3619 cursor c_low_level_code_cg_cur(c_pac_period_id NUMBER
3620 ,c_cost_group_id NUMBER
3621 ,c_inventory_item_id NUMBER
3622 )
3623 IS
3624 SELECT
3625 low_level_code
3626 FROM cst_pac_low_level_codes
3627 WHERE pac_period_id = c_pac_period_id
3628 AND cost_group_id = c_cost_group_id
3629 AND inventory_item_id = c_inventory_item_id;
3630
3631
3632 --=================
3633 -- VARIABLES
3634 --=================
3635
3636 l_current_level_code NUMBER;
3637 l_inventory_item_id NUMBER;
3638 l_tol_item_flag NUMBER;
3639 l_tolerance_flag NUMBER;
3640 l_item_idx BINARY_INTEGER;
3641 l_interorg_item_flag NUMBER;
3642 l_assembly_processed_flag VARCHAR2(1);
3643 l_assembly_item VARCHAR2(1);
3644 l_run_options NUMBER;
3645 l_inventory_item_number VARCHAR2(1025);
3646 l_interorg_non_tol_lp_cnt NUMBER;
3647 l_low_level_code NUMBER := 0;
3648
3649 l_topmost_absl_level_code NUMBER;
3650 l_lowest_absl_level_code NUMBER;
3651 l_message VARCHAR2(2000);
3652
3653
3654 -- Variables for Iteration Process
3655 l_init_msg_list VARCHAR2(1) := FND_API.G_TRUE;
3656 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
3657 l_iteration_proc_flag VARCHAR2(1);
3658
3659 l_cg_idx BINARY_INTEGER;
3660 l_cost_update_type NUMBER;
3661
3662 -- FP Bug 7674673 fix
3663 l_non_interorg_cg_check VARCHAR2(1);
3664 --Bug 15977287 fix
3665 l_txn_category NUMBER;
3666 l_error_num NUMBER;
3667 l_error_code VARCHAR2(240);
3668 l_error_msg VARCHAR2(240);
3669
3670 BEGIN
3671
3672 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3673 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
3674 ,G_MODULE_HEAD || l_routine || '.begin'
3675 ,l_routine || '<'
3676 );
3677 END IF;
3678
3679 -- Initialize Global PL/SQL tables used in PAC interorg
3680 -- iteration process for each run
3681 CST_PAC_ITERATION_PROCESS_PVT.Initialize
3682 (p_legal_entity_id => p_legal_entity
3683 );
3684 -- TAB is used as a delimiter in output which can be viewed in spreadsheet application to see the progress through iterations.
3685 FND_MESSAGE.Set_Name('BOM', 'CST_PAC_OUTPUT_MESSAGE');
3686 l_message := FND_MESSAGE.Get;
3687 FND_FILE.put_line(FND_FILE.OUTPUT, l_message);
3688
3689 FND_MESSAGE.Set_Name('BOM', 'CST_PAC_PMAC_ITR_PROMPT');
3690 l_message := FND_MESSAGE.Get;
3691 FND_FILE.put_line(FND_FILE.OUTPUT, l_message);
3692 /* Storing the translated messages in global variable since in CST_PAC_ITERATION_PROCESS_PVT.Verify_Tolerance_Of_Item
3693 it need not be translated for each item */
3694 FND_MESSAGE.SET_NAME('BOM', 'CST_PAC_INTORG_TOLERANCE');
3695 CST_PAC_ITERATION_PROCESS_PVT.G_TOL_ACHIEVED_MESSAGE := FND_MESSAGE.GET;
3696 FND_MESSAGE.SET_NAME('BOM', 'CST_PAC_INTORG_NO_TOL');
3697 CST_PAC_ITERATION_PROCESS_PVT.G_TOL_NOT_ACHIEVED_MESSAGE := FND_MESSAGE.GET;
3698
3699 -- Get the iteration process flag to check whether an iteration is an
3700 -- optional process
3701
3702 SELECT nvl(iteration_proc_flag,'N')
3703 INTO l_iteration_proc_flag
3704 FROM cst_le_cost_types
3705 WHERE legal_entity = p_legal_entity
3706 AND cost_type_id = p_cost_type_id;
3707
3708 IF (p_run_options = 1 OR p_run_options = 2) THEN
3709
3710 -- ===============================================================
3711 -- Determine Absorption Level Code of all items across cost groups
3712 -- ===============================================================
3713 Get_Absorption_Level_Of_Items(p_period_id => p_period_id
3714 ,p_legal_entity_id => p_legal_entity
3715 ,p_period_start_date => p_start_date
3716 ,p_period_end_date => p_end_date
3717 );
3718
3719 -- Assign Absorption Level Code to Interorg Items
3720 UPDATE CST_PAC_INTORG_ITMS_TEMP cpiit
3721 SET cpiit.absorption_level_code =
3722 (SELECT absorption_level_code
3723 FROM cst_pac_itms_absl_codes
3724 WHERE pac_period_id = cpiit.pac_period_id
3725 AND inventory_item_id = cpiit.inventory_item_id
3726 )
3727 WHERE cpiit.pac_period_id = p_period_id;
3728
3729 END IF; -- resume option Start
3730
3731 -- ===============================================================
3732 -- Check for uncosted transactions when run options is resume for
3733 -- consecutive iterations
3734 -- To prevent the consecutive iterations when uncosted txn exists
3735 -- ===============================================================
3736 IF p_run_options = 3 THEN
3737 Validate_Uncosted_Txns(p_legal_entity_id => p_legal_entity
3738 ,p_pac_period_id => p_period_id
3739 ,p_period_start_date => p_start_date
3740 ,p_period_end_date => p_end_date
3741 );
3742 END IF;
3743
3744
3745 -- ========================================================================
3746 -- Get topmost absorption level across all items
3747 -- NOTE: for no completion items topmost absorption level code will be 1000
3748 -- ========================================================================
3749 SELECT NVL(min(absorption_level_code),1000)
3750 INTO l_topmost_absl_level_code
3751 FROM cst_pac_itms_absl_codes
3752 WHERE pac_period_id = p_period_id;
3753
3754 -- ========================================
3755 -- initialize the starting Absorption level
3756 -- ========================================
3757 -- get the lowermost absorption level when the
3758 -- run options 3 - Resume or 4 - Final
3759 IF p_run_options = 3 OR p_run_options = 4 THEN
3760
3761 SELECT NVL(max(absorption_level_code),1000)
3762 INTO l_lowest_absl_level_code
3763 FROM cst_pac_itms_absl_codes
3764 WHERE pac_period_id = p_period_id
3765 AND process_flag = 'N';
3766 ELSE
3767 -- run options 1 - start or 2 - error
3768 l_lowest_absl_level_code := 1000;
3769 END IF;
3770
3771 -- ===================================================
3772 -- Periodic Absorption Rollup across absorption levels
3773 -- ===================================================
3774
3775 -- Set run options variable
3776 l_run_options := p_run_options;
3777
3778 -- Initialize interorg items in LOOP count which have not achieved tolerance
3779 l_interorg_non_tol_lp_cnt := 0;
3780
3781
3782
3783 FOR l_current_level_idx IN REVERSE l_topmost_absl_level_code .. l_lowest_absl_level_code LOOP
3784
3785 -- Purge private pl/sql table G_ITEM_LEVEL_TBL containing previous level items
3786 CST_PERIODIC_ABSORPTION_PROC.G_ITEM_LEVEL_TBL.DELETE;
3787
3788 l_current_level_code := l_current_level_idx;
3789
3790 -- Display current BOM level code
3791 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3792 FND_LOG.string(FND_LOG.LEVEL_EVENT
3793 , G_MODULE_HEAD || l_routine || '.currentabslevel'
3794 , 'Current absorption Level Code:' || l_current_level_code
3795 );
3796 END IF;
3797
3798 -- ================================================================
3799 -- Retrieve all items in the current absorption level
3800 -- Store the item and its absorption in private pl/sql table
3801 -- G_ITEM_LEVEL_TBL
3802 -- ================================================================
3803
3804 OPEN items_in_cur_absl_level_cur(p_period_id
3805 ,l_current_level_code
3806 );
3807 FETCH items_in_cur_absl_level_cur BULK COLLECT INTO G_ITEM_LEVEL_TBL;
3808 CLOSE items_in_cur_absl_level_cur;
3809
3810 -- Initialize interorg item non tolerance counter
3811 -- counter for the non tolerance interorg items in each absorption level
3812 l_interorg_non_tol_lp_cnt := 0;
3813
3814 -- =========================================================================
3815 -- Perform Absorption Process in the current absorption level
3816 -- =========================================================================
3817 l_item_idx := G_ITEM_LEVEL_TBL.FIRST;
3818
3819 WHILE (l_item_idx <= G_ITEM_LEVEL_TBL.LAST) LOOP
3820
3821 l_inventory_item_id := G_ITEM_LEVEL_TBL(l_item_idx).inventory_item_id;
3822 l_inventory_item_number := Get_Item_Number(l_inventory_item_id);
3823
3824 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3825 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
3826 , G_MODULE_HEAD || l_routine ||'.item'
3827 , 'Inventory Item Id:' || l_inventory_item_id ||
3828 ' Item Number:' || l_inventory_item_number
3829 );
3830 END IF;
3831 -- Check whether an item is an interorg item
3832 SELECT count(1)
3833 INTO
3834 l_interorg_item_flag
3835 FROM
3836 cst_pac_intorg_itms_temp
3837 WHERE inventory_item_id = l_inventory_item_id
3838 AND pac_period_id = p_period_id
3839 AND absorption_level_code = l_current_level_code
3840 AND rownum = 1;
3841
3842 -- initialize completion item flag
3843 IF l_current_level_code = 1000 THEN
3844 /* assembly items with completion/scrap/return might appear with LLC of 1000 */
3845 OPEN c_low_level_code_cur(p_period_id
3846 ,l_inventory_item_id
3847 );
3848 FETCH c_low_level_code_cur
3849 INTO l_low_level_code;
3850
3851 IF c_low_level_code_cur%FOUND THEN
3852 l_assembly_item := 'Y';
3853 ELSE
3854 -- No completion item
3855 -- No WIP assembly transaction exists, nothing to process
3856 -- set the flag to already processed to avoid Group 1' invoke
3857 l_assembly_item := 'N';
3858 END IF;
3859 CLOSE c_low_level_code_cur;
3860 ELSE
3861 l_assembly_item := 'Y';
3862 END IF;
3863
3864 IF (l_interorg_item_flag = 1 AND l_assembly_item = 'Y') THEN
3865
3866 -- completion item and interorg item
3867 -- maximum iteration count of the current absorption level interorg item
3868 -- iteration count should be 0 for first time invoke
3869 -- l_wip_assembly_process_flag indicates whether wip completion txns are already processed or not
3870 SELECT decode(max(iteration_count), 0, 'N', 'Y')
3871 INTO l_assembly_processed_flag
3872 FROM
3873 cst_pac_intorg_itms_temp
3874 WHERE pac_period_id = p_period_id
3875 AND absorption_level_code = l_current_level_code
3876 AND inventory_item_id = l_inventory_item_id;
3877
3878 ELSIF l_interorg_item_flag = 0 THEN
3879 -- completion, non interorg item
3880 -- it means very first process
3881 -- set wip assembly already processed flag to N
3882 l_assembly_processed_flag := 'N';
3883
3884 END IF;
3885
3886 IF l_assembly_processed_flag = 'N' THEN
3887 -- first time execution for the current absorption level item
3888
3889 -- ===============================================================================
3890 -- Process WIP Assembly, WIP Issue transactions and PCU value change for all items
3891 -- at this level in each cost group
3892 -- Process non-rework assembly txns
3893 -- PCU value change txns by level
3894 -- Process rework issue and assembly txns
3895 -- NOTE: An item may exist in different levels across cost groups
3896 -- ===============================================================================
3897 Process_Comp_Items
3898 (p_legal_entity => p_legal_entity
3899 ,p_cost_type_id => p_cost_type_id
3900 ,p_cost_method => p_cost_method
3901 ,p_period_id => p_period_id
3902 ,p_start_date => p_start_date
3903 ,p_end_date => p_end_date
3904 ,p_prev_period_id => p_prev_period_id
3905 ,p_cg_tab => p_cg_tab
3906 ,p_inventory_item_id => l_inventory_item_id
3907 ,p_uom_control => p_uom_control
3908 ,p_pac_rates_id => p_pac_rates_id
3909 ,p_mat_relief_algorithm => p_mat_relief_algorithm
3910 ,p_user_id => p_user_id
3911 ,p_login_id => p_login_id
3912 ,p_req_id => p_req_id
3913 ,p_prg_id => p_prg_id
3914 ,p_prg_appid => p_prg_appid
3915 );
3916
3917 END IF; -- check to execute first time in each absorption level
3918
3919 -- Perform Iteration Process only for an interorg item
3920 -- NOTE:
3921 IF l_interorg_item_flag = 1 THEN
3922
3923 -- =======================================================================
3924 -- Perform Item Iteration LOOP
3925 -- Item --> Iteration --> Optimal Seq cost Group --> interorg Transactions
3926 -- Item --> Iteration_Process
3927 -- =======================================================================
3928 CST_PAC_ITERATION_PROCESS_PVT.Iteration_Process
3929 (p_init_msg_list => l_init_msg_list
3930 ,p_validation_level => l_validation_level
3931 ,p_legal_entity_id => p_legal_entity
3932 ,p_cost_type_id => p_cost_type_id
3933 ,p_cost_method => p_cost_method
3934 ,p_iteration_proc_flag => l_iteration_proc_flag
3935 ,p_period_id => p_period_id
3936 ,p_start_date => p_start_date
3937 ,p_end_date => p_end_date
3938 ,p_inventory_item_id => l_inventory_item_id
3939 ,p_inventory_item_number => l_inventory_item_number
3940 ,p_tolerance => p_tolerance
3941 ,p_iteration_num => p_number_of_iterations
3942 ,p_run_options => l_run_options
3943 ,p_pac_rates_id => p_pac_rates_id
3944 ,p_uom_control => p_uom_control
3945 ,p_user_id => p_user_id
3946 ,p_login_id => p_login_id
3947 ,p_req_id => p_req_id
3948 ,p_prg_id => p_prg_id
3949 ,p_prg_appid => p_prg_appid
3950 );
3951
3952 END IF; -- bug 7674673 fix iteration process only for interorg item
3953
3954 -- ===================================================================
3955 -- Periodic Cost Update - Value Change for remaing cost groups
3956 -- for which no valid interorg txns exists.
3957 -- FP Bug 7674673 fix:
3958 -- Scenario: If l_interorg_item_flag is 1, atleast there is a
3959 -- cost group for which interorg txn exists. There may be cost
3960 -- groups for which no interorg txns exists for the inventory item
3961 -- , pac period which have to be processed for the PCU - value
3962 -- change transactions, even though the interorg flag is 1 which
3963 -- indicates a presence of an interorg txn in any of the cost groups.
3964 -- Therefore, if l_interorg_item_flag is 1, check for
3965 -- non-interorg cost group to process for PCU - value change txns.
3966 -- Table: CST_PAC_INTORG_ITMS_TEMP contains inventory items of
3967 -- those cost groups having only valid interorg txns or scenario with
3968 -- FOB:shipment
3969 -- Cost groups having no valid interorg txns do not exist in the
3970 -- interorg table and therefore cannot get processed in the
3971 -- iteration_process procedure.
3972 -- bug 7674673 fix : Separate IF condition necessary
3973 -- ===================================================================
3974 IF (p_run_options = 1 OR p_run_options = 2) AND l_interorg_item_flag = 1 THEN
3975
3976 l_cost_update_type := 2; -- PCU Value Change
3977 l_cg_idx := p_cg_tab.FIRST;
3978 LOOP
3979
3980 -- FP Bug 7674673 fix: check for non interorg cost group
3981 -- Process PCU - value change only for those cost groups which have not
3982 -- got processed in iteration_process procedure
3983 l_non_interorg_cg_check := Check_For_No_Interorg_CG
3984 (p_period_id => p_period_id
3985 ,p_cost_group_id => p_cg_tab(l_cg_idx).cost_group_id
3986 ,p_inventory_item_id => l_inventory_item_id
3987 );
3988
3989
3990 IF l_non_interorg_cg_check = 'Y' THEN
3991
3992 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3993 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
3994 ,G_MODULE_HEAD || l_routine || '.noncgid'
3995 ,'Non Interorg Cost Group Id:' || p_cg_tab(l_cg_idx).cost_group_id
3996 );
3997 END IF;
3998
3999
4000 -- Get PAC Low Level Code for the Item in that cost group
4001 OPEN c_low_level_code_cg_cur(p_period_id
4002 ,p_cg_tab(l_cg_idx).cost_group_id
4003 ,l_inventory_item_id
4004 );
4005 FETCH c_low_level_code_cg_cur
4006 INTO l_low_level_code;
4007
4008 -- completion item in the cost group
4009 -- note that an item can exist as a completion item in one cost group and
4010 -- no completion in another cost group
4011 IF c_low_level_code_cg_cur%NOTFOUND THEN
4012 l_low_level_code := -1; -- no completion item
4013 END IF;
4014
4015 CLOSE c_low_level_code_cg_cur;
4016
4017 -- Periodic Cost Update value change only for non-interorg items in
4018 -- the cost group where the same item is an interorg item in other cost groups.
4019 -- both completion and no completion items are included
4020 CST_PERIODIC_ABSORPTION_PROC.Periodic_Cost_Update_By_Level
4021 (p_period_id => p_period_id
4022 ,p_legal_entity => p_legal_entity
4023 ,p_cost_type_id => p_cost_type_id
4024 ,p_cost_group_id => p_cg_tab(l_cg_idx).cost_group_id
4025 ,p_inventory_item_id => l_inventory_item_id
4026 ,p_cost_method => p_cost_method
4027 ,p_start_date => p_start_date
4028 ,p_end_date => p_end_date
4029 ,p_pac_rates_id => p_pac_rates_id
4030 ,p_master_org_id => p_cg_tab(l_cg_idx).master_org_id
4031 ,p_uom_control => p_uom_control
4032 ,p_low_level_code => l_low_level_code
4033 ,p_txn_category => 5
4034 ,p_user_id => p_user_id
4035 ,p_login_id => p_login_id
4036 ,p_req_id => p_req_id
4037 ,p_prg_id => p_prg_id
4038 ,p_prg_appid => p_prg_appid);
4039
4040
4041 SELECT max (txn_category)
4042 into l_txn_category
4043 FROM CST_PAC_PERIOD_BALANCES cppb
4044 WHERE cppb.pac_period_id= p_period_id
4045 AND cppb.cost_group_id =p_cg_tab(l_cg_idx).cost_group_id
4046 AND cppb.inventory_item_id = l_inventory_item_id;
4047
4048 --Bug 15977287 fix:There is a call to calculate periodic cost in Periodic_Cost_Update_By_Level
4049 --which processing both txn_category 5, 8.5 and 3.
4050 --For an interorg item in non interorg cost group, if PCU - value change transactions don't exist,
4051 --we need to call update_item_cppb for txn_category 3
4052 IF l_txn_category = 3 THEN
4053 --update_item_cppb
4054 l_error_num := 0;
4055 CSTPPWAC.update_item_cppb(i_pac_period_id => p_period_id
4056 ,i_cost_group_id => p_cg_tab(l_cg_idx).cost_group_id
4057 ,i_txn_category => 3
4058 ,i_item_id => l_inventory_item_id
4059 ,i_user_id => p_user_id
4060 ,i_login_id => p_login_id
4061 ,i_request_id => p_req_id
4062 ,i_prog_id => p_prg_id
4063 ,i_prog_appl_id => p_prg_appid
4064 ,o_err_num => l_error_num
4065 ,o_err_code => l_error_code
4066 ,o_err_msg => l_error_msg
4067 );
4068 l_error_num := NVL(l_error_num, 0);
4069 l_error_code := NVL(l_error_code, 'No Error');
4070 l_error_msg := NVL(l_error_msg, 'No Error');
4071
4072 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4073 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
4074 ,G_MODULE_HEAD || l_routine || '.update_item_cppb6'
4075 ,'After calling update_item_cppb:'|| l_error_num || l_error_code || l_error_msg
4076 );
4077 END IF;
4078
4079 IF l_error_num <> 0
4080 THEN
4081 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4082 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION
4083 , G_MODULE_HEAD || l_routine || '.others'
4084 , 'Error in cost group ' || p_cg_tab(l_cg_idx).cost_group_id ||
4085 'txn category :' || l_txn_category || ' ('||l_error_code||') '||l_error_msg
4086 );
4087 END IF;
4088
4089 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
4090 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
4091 FND_MESSAGE.set_token('MESSAGE', 'Error for cost group '||p_cg_tab(l_cg_idx).cost_group_id||' ('||l_error_code||') '||l_error_msg);
4092 FND_MSG_PUB.Add;
4093 RAISE FND_API.G_EXC_ERROR;
4094 END IF;
4095
4096 END IF;
4097
4098 END IF; -- non-interorg check
4099
4100 EXIT WHEN l_cg_idx = p_cg_tab.LAST;
4101
4102 l_cg_idx := p_cg_tab.NEXT(l_cg_idx);
4103
4104 END LOOP; -- cost group loop
4105
4106
4107 ELSIF (p_run_options = 1 OR p_run_options = 2) AND l_interorg_item_flag = 0 THEN
4108 -- ============================================================================
4109 -- Periodic Cost Update - Value Change only for non-interorg items
4110 -- Process PCU - value change after processing all the cost owned transactions
4111 -- just before processing cost derived transactions
4112 -- ----------------------------------------------------------------------------
4113 -- Periodic Cost Update - Value Change for all cost groups when interorg
4114 -- item flag is 0. This means none of the cost groups have any valid interorg
4115 -- txns including FOB:shipment kind of a scenario influencing the receiving
4116 -- cost group even when there is no interorg receipt in the receiving cost group
4117 -- ============================================================================
4118 l_cost_update_type := 2; -- PCU Value Change
4119 l_cg_idx := p_cg_tab.FIRST;
4120 LOOP
4121 -- Get PAC Low Level Code for the Item in that cost group
4122 OPEN c_low_level_code_cg_cur(p_period_id
4123 ,p_cg_tab(l_cg_idx).cost_group_id
4124 ,l_inventory_item_id
4125 );
4126 FETCH c_low_level_code_cg_cur
4127 INTO l_low_level_code;
4128
4129 -- completion item in the cost group
4130 -- note that an item can exist as a completion item in one cost group and
4131 -- no completion in another cost group
4132 IF c_low_level_code_cg_cur%NOTFOUND THEN
4133 l_low_level_code := -1; -- no completion item
4134 END IF;
4135
4136 CLOSE c_low_level_code_cg_cur;
4137
4138 -- Periodic Cost Update value change only for non-interorg items
4139 -- both completion and no completion items are included
4140 CST_PERIODIC_ABSORPTION_PROC.Periodic_Cost_Update_By_Level
4141 (p_period_id => p_period_id
4142 ,p_legal_entity => p_legal_entity
4143 ,p_cost_type_id => p_cost_type_id
4144 ,p_cost_group_id => p_cg_tab(l_cg_idx).cost_group_id
4145 ,p_inventory_item_id => l_inventory_item_id
4146 ,p_cost_method => p_cost_method
4147 ,p_start_date => p_start_date
4148 ,p_end_date => p_end_date
4149 ,p_pac_rates_id => p_pac_rates_id
4150 ,p_master_org_id => p_cg_tab(l_cg_idx).master_org_id
4151 ,p_uom_control => p_uom_control
4152 ,p_low_level_code => l_low_level_code
4153 ,p_txn_category => 5
4154 ,p_user_id => p_user_id
4155 ,p_login_id => p_login_id
4156 ,p_req_id => p_req_id
4157 ,p_prg_id => p_prg_id
4158 ,p_prg_appid => p_prg_appid);
4159
4160 SELECT max (txn_category)
4161 into l_txn_category
4162 FROM CST_PAC_PERIOD_BALANCES cppb
4163 WHERE cppb.pac_period_id= p_period_id
4164 AND cppb.cost_group_id =p_cg_tab(l_cg_idx).cost_group_id
4165 AND cppb.inventory_item_id = l_inventory_item_id;
4166
4167 --Bug 15977287 fix:There is a call to calculate periodic cost in Periodic_Cost_Update_By_Level
4168 --which processing both txn_category 5, 8.5 and 3.
4169 --For a non-interorg item, if PCU - value change transactions don't exist,
4170 --we need to call update_item_cppb for txn_category 3
4171 IF l_txn_category = 3 THEN
4172 --update_item_cppb
4173 l_error_num := 0;
4174 CSTPPWAC.update_item_cppb(i_pac_period_id => p_period_id
4175 ,i_cost_group_id => p_cg_tab(l_cg_idx).cost_group_id
4176 ,i_txn_category => 3
4177 ,i_item_id => l_inventory_item_id
4178 ,i_user_id => p_user_id
4179 ,i_login_id => p_login_id
4180 ,i_request_id => p_req_id
4181 ,i_prog_id => p_prg_id
4182 ,i_prog_appl_id => p_prg_appid
4183 ,o_err_num => l_error_num
4184 ,o_err_code => l_error_code
4185 ,o_err_msg => l_error_msg
4186 );
4187 l_error_num := NVL(l_error_num, 0);
4188 l_error_code := NVL(l_error_code, 'No Error');
4189 l_error_msg := NVL(l_error_msg, 'No Error');
4190
4191 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4192 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
4193 ,G_MODULE_HEAD || l_routine || '.update_item_cppb7'
4194 ,'After calling update_item_cppb:'|| l_error_num || l_error_code || l_error_msg
4195 );
4196 END IF;
4197
4198 IF l_error_num <> 0
4199 THEN
4200 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4201 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION
4202 , G_MODULE_HEAD || l_routine || '.others'
4203 , 'Error in cost group ' || p_cg_tab(l_cg_idx).cost_group_id ||
4204 'txn category :' || l_txn_category || ' ('||l_error_code||') '||l_error_msg
4205 );
4206 END IF;
4207
4208 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
4209 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
4210 FND_MESSAGE.set_token('MESSAGE', 'Error for cost group '||p_cg_tab(l_cg_idx).cost_group_id||' ('||l_error_code||') '||l_error_msg);
4211 FND_MSG_PUB.Add;
4212 RAISE FND_API.G_EXC_ERROR;
4213 END IF;
4214
4215 END IF;
4216
4217
4218 EXIT WHEN l_cg_idx = p_cg_tab.LAST;
4219
4220 l_cg_idx := p_cg_tab.NEXT(l_cg_idx);
4221
4222 END LOOP; -- cost group loop
4223
4224
4225 END IF;
4226
4227
4228 -- =====================================================================
4229 -- Process Group 2 Transactions only when the tolerance achieved for an
4230 -- interorg item in the current absorption level or pac item costs finalized
4231 -- For non interorg items, process group 2 transactions
4232 -- =====================================================================
4233
4234 IF (l_interorg_item_flag = 1 AND l_iteration_proc_flag = 'Y') THEN
4235
4236 -- Check whether tolerance achieved for an interorg item in the
4237 -- current absorption level
4238 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4239 FND_LOG.string(FND_LOG.LEVEL_EVENT
4240 , G_MODULE_HEAD || l_routine || '.check_nontol_items_lvl'
4241 , 'Check whether tolerance achieved for the item'
4242 );
4243 END IF;
4244
4245 -- check the tolerance for an interorg item
4246 -- l_tol_item_flag "Tolerance Not achieved for atleast one CG" = 1 "Tolerance achieved for all CGs" = 0
4247 SELECT count(1)
4248 INTO l_tol_item_flag
4249 FROM cst_pac_intorg_itms_temp
4250 WHERE pac_period_id = p_period_id
4251 AND inventory_item_id = l_inventory_item_id
4252 AND tolerance_flag = 'N'
4253 AND rownum = 1;
4254
4255 END IF; -- interorg item and iteration process check
4256
4257
4258
4259 -- ========================================================================
4260 -- Interorg items not achieved tolerance within a user specified
4261 -- number of iterations
4262 -- non tolerance item counter only when iteration process is enabled
4263 -- ========================================================================
4264 IF (l_interorg_item_flag = 1 AND l_tol_item_flag <> G_TOL_ACHIEVED_FORALL_CG
4265 AND l_iteration_proc_flag = 'Y') THEN
4266 l_interorg_non_tol_lp_cnt := l_interorg_non_tol_lp_cnt + 1;
4267 END IF;
4268
4269 -- ==========================================================================
4270 -- tolerance achieved for an interorg item in the current absorption level.
4271 -- tolerance achieved either by matching receipts or finalizing pac item cost.
4272 -- Process Group 2 transactions only when the tolerance is either achieved
4273 -- or finalized for an interorg item OR first time execution for non interorg
4274 -- item provided iteration process should have been enabled
4275 -- If iteration process is not enabled, then process group 2 transactions
4276 -- without any further check
4277 -- ==========================================================================
4278 IF (l_interorg_item_flag = 1 AND l_tol_item_flag = G_TOL_ACHIEVED_FORALL_CG ) OR
4279 (l_interorg_item_flag = 0) OR (l_iteration_proc_flag = 'N' ) THEN
4280
4281 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4282 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
4283 , G_MODULE_HEAD || l_routine || '.gp2txn'
4284 , 'Processing WIP Issue and other group 2 transactions'
4285 );
4286 END IF;
4287
4288 Process_Gp2_Other_Txns
4289 (p_legal_entity => p_legal_entity
4290 ,p_cost_type_id => p_cost_type_id
4291 ,p_cost_method => p_cost_method
4292 ,p_period_id => p_period_id
4293 ,p_start_date => p_start_date
4294 ,p_end_date => p_end_date
4295 ,p_prev_period_id => p_prev_period_id
4296 ,p_cg_tab => p_cg_tab
4297 ,p_inventory_item_id => l_inventory_item_id
4298 ,p_uom_control => p_uom_control
4299 ,p_pac_rates_id => p_pac_rates_id
4300 ,p_mat_relief_algorithm => p_mat_relief_algorithm
4301 ,p_user_id => p_user_id
4302 ,p_login_id => p_login_id
4303 ,p_req_id => p_req_id
4304 ,p_prg_id => p_prg_id
4305 ,p_prg_appid => p_prg_appid
4306 );
4307
4308 -- Set the process flag to Y after processing
4309 -- other cost derived txns - group 2
4310 UPDATE CST_PAC_ITMS_ABSL_CODES
4311 SET process_flag = 'Y'
4312 WHERE pac_period_id = p_period_id
4313 AND inventory_item_id = l_inventory_item_id
4314 AND absorption_level_code = l_current_level_code;
4315
4316 --commit the processing.
4317 COMMIT;
4318 END IF; -- Group 2 processing criteria check
4319
4320
4321 l_item_idx := G_ITEM_LEVEL_TBL.NEXT(l_item_idx);
4322
4323
4324 END LOOP; -- WHILE (l_item_idx <= G_ITEM_LEVEL_TBL.LAST) LOOP
4325
4326
4327 -- ====================================================================
4328 -- Check Run options is Final
4329 -- Run options Final is applicable only for the items in current level
4330 -- of absorption loop
4331 -- For the next levels of absorption loop, run options should be set to
4332 -- Resume
4333 -- ====================================================================
4334 IF l_run_options = 4 THEN
4335 -- set run options to resume for remaining absorption loops
4336 l_run_options := 3;
4337 END IF;
4338
4339 -- ==================================================================
4340 -- Check for any interorg items in the current absorption level which
4341 -- have not yet acheived tolerance.
4342 -- Note that the counter will be incremented only when the iteration
4343 -- process flag is enabled
4344 -- ==================================================================
4345 IF l_interorg_non_tol_lp_cnt <> 0 THEN
4346 -- Set Periodic Absorption Cost Processor status to Resume
4347 -- Set process status to 5 - Resume for all the valid cost groups
4348 -- in Legal Entity
4349 CST_PAC_ITERATION_PROCESS_PVT.Set_Process_Status( p_legal_entity_id => p_legal_entity
4350 , p_period_id => p_period_id
4351 , p_period_end_date => p_end_date
4352 , p_phase_status => 5
4353 );
4354
4355 -- Set Phase 5 status to 5 - Resume for all the CGs to display
4356 -- the Phase 7 status on the screen
4357 CST_PAC_ITERATION_PROCESS_PVT.Set_Phase5_Status
4358 ( p_legal_entity_id => p_legal_entity
4359 , p_period_id => p_period_id
4360 , p_period_end_date => p_end_date
4361 , p_phase_status => 5
4362 );
4363
4364 FND_MESSAGE.Set_Name('BOM', 'CST_PAC_TOL_NOT_ACHIEVED');
4365 fnd_file.put_line(fnd_file.output, fnd_message.get);
4366
4367 EXIT;
4368
4369 END IF;
4370
4371
4372 END LOOP; -- FOR l_current_level_idx IN REVERSE l_topmost_absl_level_code .. l_lowest_absl_level_code LOOP
4373
4374 -- ==========================
4375 -- Insert Ending Balance
4376 -- ==========================
4377 -- insert into CPPB only at the period end
4378 -- check to make sure that no repetitive insertions during start,resume,error or final run options
4379 IF (l_current_level_code = l_topmost_absl_level_code) AND (l_interorg_non_tol_lp_cnt = 0) THEN
4380 Insert_Ending_Balance_All_Cgs(p_pac_period_id => p_period_id
4381 ,p_cg_tab => p_cg_tab
4382 ,p_end_date => p_end_date
4383 ,p_user_id => p_user_id
4384 ,p_login_id => p_login_id
4385 ,p_req_id => p_req_id
4386 ,p_prg_id => p_prg_id
4387 ,p_prg_appid => p_prg_appid
4388 );
4389 END IF; -- insert into CPPB only at the period end
4390
4391
4392 -- Set the Phase status by considering the iteration process flag
4393 IF l_iteration_proc_flag = 'Y' THEN
4394 -- =====================================================================
4395 -- Check for tolerance achieved for all interorg items
4396 -- Interorg items should either be tolerance achieved or pac item
4397 -- costs finalized
4398 -- Set the interorg transfer cost process phase 7 status to 4 - Complete
4399 -- Set the periodic cost process phase 5 status to 1 - unprocessed
4400 -- =====================================================================
4401
4402 SELECT count(1)
4403 INTO l_tolerance_flag
4404 FROM cst_pac_intorg_itms_temp
4405 WHERE pac_period_id = p_period_id
4406 AND tolerance_flag = 'N'
4407 AND rownum = 1;
4408
4409
4410 IF l_tolerance_flag = 0 THEN
4411 -- All the items are absorbed
4412 -- ====================================================================================
4413 -- Set Phase 7 status to 4 - complete for all valid cost groups
4414 -- Set Phase 5 status to 1 - unprocessed for all valid cost groups
4415 -- ====================================================================================
4416 CST_PAC_ITERATION_PROCESS_PVT.Set_Process_Status(p_legal_entity_id => p_legal_entity
4417 ,p_period_id => p_period_id
4418 ,p_period_end_date => p_end_date
4419 ,p_phase_status => 4
4420 );
4421
4422 -- Set Phase 5 status to 1 - Un Processed for all the valid cost
4423 -- groups in Legal Entity
4424 CST_PAC_ITERATION_PROCESS_PVT.Set_Phase5_Status(p_legal_entity_id => p_legal_entity
4425 ,p_period_id => p_period_id
4426 ,p_period_end_date => p_end_date
4427 ,p_phase_status => 1
4428 );
4429
4430
4431
4432 END IF; -- tolerance check
4433
4434 ELSE
4435 -- iteration process is not enabled; only default iteration
4436 -- ====================================================================================
4437 -- Set Phase 7 status to 4 - complete for all valid cost groups
4438 -- Set Phase 5 status to 1 - unprocessed for all valid cost groups
4439 -- ====================================================================================
4440 CST_PAC_ITERATION_PROCESS_PVT.Set_Process_Status(p_legal_entity_id => p_legal_entity
4441 ,p_period_id => p_period_id
4442 ,p_period_end_date => p_end_date
4443 ,p_phase_status => 4
4444 );
4445
4446 -- Set Phase 5 status to 1 - Un Processed for all the valid cost
4447 -- groups in Legal Entity
4448 CST_PAC_ITERATION_PROCESS_PVT.Set_Phase5_Status(p_legal_entity_id => p_legal_entity
4449 ,p_period_id => p_period_id
4450 ,p_period_end_date => p_end_date
4451 ,p_phase_status => 1
4452 );
4453
4454
4455 END IF; -- iteration process enabled check
4456
4457 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4458 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4459 ,G_MODULE_HEAD || l_routine || '.end'
4460 ,l_routine || '>'
4461 );
4462 END IF;
4463
4464 EXCEPTION
4465 WHEN FND_API.G_EXC_ERROR THEN
4466 ROLLBACK;
4467 -- Set Phase 7 Status to error
4468 CST_PAC_ITERATION_PROCESS_PVT.Set_Process_Status(p_legal_entity_id => p_legal_entity
4469 ,p_period_id => p_period_id
4470 ,p_period_end_date => p_end_date
4471 ,p_phase_status => 3
4472 );
4473 COMMIT;
4474 RAISE FND_API.G_EXC_ERROR;
4475
4476 WHEN OTHERS THEN
4477 ROLLBACK;
4478 -- Set Phase 7 Status to error
4479 CST_PAC_ITERATION_PROCESS_PVT.Set_Process_Status(p_legal_entity_id => p_legal_entity
4480 ,p_period_id => p_period_id
4481 ,p_period_end_date => p_end_date
4482 ,p_phase_status => 3
4483 );
4484 COMMIT;
4485 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
4486 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
4487 FND_MESSAGE.set_token('MESSAGE', '('||SQLCODE||') '||SQLERRM);
4488 FND_MSG_PUB.Add;
4489 RAISE FND_API.G_EXC_ERROR;
4490
4491 END Absorption_Cost_Process;
4492
4493 -- =======================================================================
4494 -- PROCEDURE : Process_Logical_Txns PRIVATE
4495 -- COMMENT : This procedure will process all logical transactions
4496 -- Process consigned price update transactions
4497 -- Drop Shipment / global procurement changes
4498 -- Exclude OPM logical intransit receipts
4499 -- =======================================================================
4500 PROCEDURE process_logical_txns
4501 ( p_period_id IN NUMBER
4502 , p_legal_entity_id IN NUMBER
4503 , p_cost_type_id IN NUMBER
4504 , p_cost_group_id IN NUMBER
4505 , p_cost_method IN NUMBER
4506 , p_master_org_id IN NUMBER
4507 , p_uom_control IN NUMBER
4508 , p_start_date IN DATE
4509 , p_end_date IN DATE
4510 , p_user_id IN NUMBER
4511 , p_login_id IN NUMBER
4512 , p_req_id IN NUMBER
4513 , p_prg_id IN NUMBER
4514 , p_prg_appid IN NUMBER
4515 )
4516 IS
4517
4518 l_routine CONSTANT VARCHAR2(30) := 'process_logical_txns';
4519
4520 -- Logical transaction cursor
4521 CURSOR logical_txn_cur(c_cost_group_id NUMBER
4522 ,c_start_date DATE
4523 ,c_end_date DATE
4524 )
4525 IS
4526 SELECT
4527 mmt.transaction_id
4528 , mmt.transaction_action_id
4529 , mmt.transaction_source_type_id
4530 , mmt.inventory_item_id
4531 , mmt.primary_quantity
4532 , mmt.organization_id
4533 , nvl(mmt.transfer_organization_id,-1) transfer_organization_id
4534 , mmt.subinventory_code
4535 FROM
4536 mtl_material_transactions mmt
4537 , cst_cost_group_assignments ccga
4538 WHERE mmt.transaction_date BETWEEN c_start_date AND c_end_date
4539 AND ccga.organization_id = mmt.organization_id
4540 AND ccga.cost_group_id = c_cost_group_id
4541 AND mmt.organization_id = nvl(mmt.owning_organization_id, mmt.organization_id)
4542 AND nvl(mmt.owning_tp_type,2) = 2
4543 /* exclude OPM logical intransit receipts */
4544 AND mmt.transaction_action_id <> 15
4545 /* Ensure that only logical transactions get picked up */
4546 AND ((nvl(mmt.logical_transaction,3) = 1
4547 AND mmt.parent_transaction_id IS NOT NULL
4548 AND nvl(mmt.logical_trx_type_code,6) <= 5)
4549 OR mmt.transaction_type_id = 20)
4550 ORDER BY
4551 transaction_date
4552 , transaction_id;
4553
4554 TYPE logical_txn_tab IS TABLE OF logical_txn_cur%ROWTYPE INDEX BY BINARY_INTEGER;
4555 l_logical_txn_tab logical_txn_tab;
4556 l_empty_logical_txn_tab logical_txn_tab;
4557
4558 l_error_num NUMBER;
4559 l_error_code VARCHAR2(240);
4560 l_error_msg VARCHAR2(240);
4561 l_batch_size NUMBER := 200;
4562 l_loop_count NUMBER := 0;
4563 logical_txn_except EXCEPTION;
4564
4565
4566 BEGIN
4567
4568 FND_FILE.put_line
4569 ( FND_FILE.log
4570 , '>> CST_PERIODIC_ABSORPTION_PROC:process_logical_txns'
4571 );
4572
4573 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4574 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4575 ,G_MODULE_HEAD || l_routine || '.begin'
4576 ,l_routine || '<'
4577 );
4578 END IF;
4579
4580 IF NOT logical_txn_cur%ISOPEN THEN
4581 OPEN logical_txn_cur(p_cost_group_id
4582 ,p_start_date
4583 ,p_end_date
4584 );
4585 END IF;
4586
4587 LOOP
4588 l_logical_txn_tab := l_empty_logical_txn_tab;
4589 FETCH logical_txn_cur BULK COLLECT INTO l_logical_txn_tab LIMIT l_batch_size;
4590
4591 l_loop_count := l_logical_txn_tab.count;
4592
4593 FOR i IN 1..l_loop_count
4594 LOOP
4595 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4596 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
4597 ,G_MODULE_HEAD || l_routine || '.logical_txn1'
4598 ,'logical transaction - Transaction Id:' || l_logical_txn_tab(i).transaction_id || ' Action Id:' || l_logical_txn_tab(i).transaction_action_id
4599 );
4600 END IF;
4601
4602 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4603 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
4604 ,G_MODULE_HEAD || l_routine || '.logical_txn2'
4605 ,'Source Type Id:' || l_logical_txn_tab(i).transaction_source_type_id || ' inventory item id:' || l_logical_txn_tab(i).inventory_item_id
4606 );
4607 END IF;
4608
4609 CSTPPINV.cost_acct_events(i_pac_period_id => p_period_id
4610 ,i_legal_entity => p_legal_entity_id
4611 ,i_cost_type_id => p_cost_type_id
4612 ,i_cost_group_id => p_cost_group_id
4613 ,i_cost_method => p_cost_method
4614 ,i_txn_id => l_logical_txn_tab(i).transaction_id
4615 ,i_item_id => l_logical_txn_tab(i).inventory_item_id
4616 ,i_txn_qty => l_logical_txn_tab(i).primary_quantity
4617 ,i_txn_org_id => l_logical_txn_tab(i).organization_id
4618 ,i_master_org_id => p_master_org_id
4619 ,i_uom_control => p_uom_control
4620 ,i_user_id => p_user_id
4621 ,i_login_id => p_login_id
4622 ,i_request_id => p_req_id
4623 ,i_prog_id => p_prg_id
4624 ,i_prog_appl_id => p_prg_appid
4625 ,o_err_num => l_error_num
4626 ,o_err_code => l_error_code
4627 ,o_err_msg => l_error_msg
4628 );
4629
4630 IF l_error_num <> 0 THEN
4631 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
4632 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
4633 FND_MESSAGE.set_token('MESSAGE', 'CSTPPINV.cost_acct_events for cost group '||p_cost_group_id||' logical txn id '||l_logical_txn_tab(i).transaction_id
4634 ||' item id '||l_logical_txn_tab(i).inventory_item_id||' org id '||l_logical_txn_tab(i).organization_id||' ('||l_error_code||') '||l_error_msg);
4635 FND_MSG_PUB.Add;
4636 -- Set Phase 5 status to Error
4637 CST_PAC_ITERATION_PROCESS_PVT.Set_Phase5_Status(p_legal_entity_id => p_legal_entity_id
4638 ,p_period_id => p_period_id
4639 ,p_period_end_date => p_end_date
4640 ,p_phase_status => 3
4641 );
4642 RAISE FND_API.G_EXC_ERROR;
4643 END IF;
4644
4645
4646 END LOOP;
4647 EXIT WHEN logical_txn_cur%NOTFOUND;
4648 END LOOP; -- FETCH loop
4649 CLOSE logical_txn_cur;
4650
4651 FND_FILE.put_line
4652 ( FND_FILE.log
4653 , '<< CST_PERIODIC_ABSORPTION_PROC:process_logical_txns'
4654 );
4655
4656 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4657 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4658 ,G_MODULE_HEAD || l_routine || '.end'
4659 ,l_routine || '>'
4660 );
4661 END IF;
4662
4663
4664 EXCEPTION
4665 WHEN FND_API.G_EXC_ERROR THEN
4666 RAISE FND_API.G_EXC_ERROR;
4667 WHEN OTHERS THEN
4668 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
4669 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
4670 FND_MESSAGE.set_token('MESSAGE', '('||SQLCODE||') '||SQLERRM);
4671 FND_MSG_PUB.Add;
4672 RAISE FND_API.G_EXC_ERROR;
4673 END process_logical_txns;
4674
4675 --========================================================================
4676 -- PROCEDURE : GET_OPEN_REQUESTS_COUNT
4677 -- COMMENT : Returns the number of Requests still running
4678 --=======================================================================
4679 FUNCTION GET_OPEN_REQUESTS_COUNT
4680 RETURN NUMBER
4681 IS
4682
4683 l_count NUMBER := 0;
4684 l_routine CONSTANT VARCHAR2(30) := 'get_open_requests_count';
4685 -- Cursor to obtain the request status
4686 CURSOR c_check_request_status(c_request_id NUMBER)
4687 IS
4688 SELECT phase_code
4689 FROM FND_CONCURRENT_REQUESTS
4690 WHERE request_id = c_request_id;
4691
4692 BEGIN
4693
4694 FOR i IN 1 .. G_REQUEST_TABLE.COUNT
4695 LOOP
4696 IF G_REQUEST_TABLE(i).request_id is NOT NULL THEN
4697
4698 IF NVL(G_REQUEST_TABLE(i).request_status , 'X') <> 'C' THEN
4699
4700 OPEN c_check_request_status(G_REQUEST_TABLE(i).request_id);
4701
4702 FETCH c_check_request_status
4703 INTO G_REQUEST_TABLE(i).request_status;
4704
4705 CLOSE c_check_request_status;
4706
4707 IF G_REQUEST_TABLE(i).request_status = 'C'
4708 THEN
4709 get_phase_status
4710 ( p_pac_period_id => G_REQUEST_TABLE(i).pac_period_id
4711 ,p_phase => 8
4712 ,p_cost_group_id => G_REQUEST_TABLE(i).cost_group_id
4713 ,x_status => G_REQUEST_TABLE(i).phase_status
4714 );
4715
4716 IF G_REQUEST_TABLE(i).phase_status = 3 THEN
4717 FND_MESSAGE.Set_Name('BOM', 'CST_PAC_AVG_WORKER_ERROR');
4718 FND_MESSAGE.set_token('CG_ID', G_REQUEST_TABLE(i).cost_group_id);
4719 FND_MESSAGE.set_token('REQUEST_ID', G_REQUEST_TABLE(i).request_id);
4720 FND_MSG_PUB.Add;
4721 RAISE FND_API.G_EXC_ERROR;
4722 END IF;
4723 ELSE
4724 l_count := l_count + 1;
4725 END IF;
4726 END IF;
4727 END IF;
4728 END LOOP;
4729
4730 RETURN l_count ;
4731
4732 END GET_OPEN_REQUESTS_COUNT;
4733
4734 --========================================================================
4735 -- PROCEDURE : Transfer_Cost_Processor_Worker PUBLIC
4736 -- COMMENT : This procedure will process phases 1-4 for all transactions
4737 -- and then process phase 7 for only interorg transactions
4738 --=========================================================================
4739 PROCEDURE transfer_cp_worker
4740 ( p_legal_entity IN NUMBER
4741 , p_cost_type_id IN NUMBER
4742 , p_cost_method IN NUMBER
4743 , p_period_id IN NUMBER
4744 , p_prev_period_id IN NUMBER
4745 , p_tolerance IN NUMBER
4746 , p_number_of_iterations IN NUMBER
4747 , p_number_of_workers IN NUMBER
4748 , p_cg_tab IN CST_PERIODIC_ABSORPTION_PROC.tbl_type
4749 , p_uom_control IN NUMBER
4750 , p_pac_rates_id IN NUMBER
4751 , p_mat_relief_algorithm IN NUMBER
4752 , p_start_date IN DATE
4753 , p_end_date IN DATE
4754 , p_run_options IN NUMBER
4755 )
4756 IS
4757
4758 l_routine CONSTANT VARCHAR2(30) := 'transfer_cp_worker';
4759
4760 --=================
4761 -- VARIABLES
4762 --=================
4763
4764 l_current_index BINARY_INTEGER;
4765 l_prg_appid NUMBER;
4766 l_prg_id NUMBER;
4767 l_req_id NUMBER;
4768 l_user_id NUMBER;
4769 l_login_id NUMBER;
4770 l_sleep_time NUMBER := 15;
4771 -- Variables
4772 l_phase7_status NUMBER;
4773 l_phase5_status NUMBER;
4774 l_return_code NUMBER;
4775 l_error_msg VARCHAR2(255);
4776 l_error_code VARCHAR2(15);
4777 l_error_num NUMBER;
4778 l_submit_req_id NUMBER;
4779 l_worker_idx NUMBER := 1;
4780 l_message VARCHAR2(2000);
4781 -- Exceptions
4782 lifo_cost_except EXCEPTION;
4783 wip_close_except EXCEPTION;
4784
4785
4786 BEGIN
4787
4788 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4789 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
4790 ,G_MODULE_HEAD || l_routine || '.begin'
4791 ,l_routine || '<'
4792 );
4793 END IF;
4794
4795 l_prg_appid := FND_GLOBAL.prog_appl_id;
4796 l_prg_id := FND_GLOBAL.conc_program_id;
4797 l_req_id := FND_GLOBAL.conc_request_id;
4798 l_user_id := FND_GLOBAL.user_id;
4799 l_login_id := FND_GLOBAL.login_id;
4800
4801 G_REQUEST_TABLE.delete;
4802
4803 l_current_index := p_cg_tab.FIRST;
4804
4805 LOOP
4806
4807 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4808 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
4809 ,G_MODULE_HEAD || l_routine || '.cgid'
4810 ,'Cost Group Id:' || p_cg_tab(l_current_index).cost_group_id
4811 );
4812 END IF;
4813
4814 IF p_run_options > 2 AND p_cg_tab(l_current_index).starting_phase < 5 THEN
4815 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
4816 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
4817 FND_MESSAGE.set_token('MESSAGE', 'Run Options Resume for Tolerance and Final cannot be chosen since the cost group '
4818 ||p_cg_tab(l_current_index).cost_group_id||' has not completed one of previous phases');
4819 FND_MSG_PUB.Add;
4820 RAISE FND_API.G_EXC_ERROR;
4821 END IF;
4822
4823 IF p_run_options < 3 AND (p_cg_tab(l_current_index).starting_phase < 5 OR p_cg_tab(l_current_index).starting_phase = 8) THEN
4824 -- submit concurrent request. Run Options should never be greater than 2 for these concurrent requests
4825 l_submit_req_id := FND_REQUEST.SUBMIT_REQUEST('BOM',
4826 'CST_PAC_WORKER',
4827 NULL,
4828 NULL,
4829 FALSE,
4830 p_legal_entity,
4831 p_cost_type_id,
4832 p_cg_tab(l_current_index).master_org_id,
4833 p_cost_method,
4834 p_cg_tab(l_current_index).cost_group_id,
4835 p_period_id,
4836 p_prev_period_id,
4837 p_cg_tab(l_current_index).starting_phase,
4838 p_pac_rates_id,
4839 p_uom_control,
4840 p_start_date,
4841 p_end_date
4842 );
4843 COMMIT;
4844 IF (l_submit_req_id = 0) THEN
4845 l_message := fnd_message.get;
4846 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
4847 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
4848 FND_MESSAGE.set_token('MESSAGE', 'Unable to submit request for Iterative PAC Worker '||l_message);
4849 FND_MSG_PUB.Add;
4850 RAISE FND_API.G_EXC_ERROR;
4851 END IF;
4852 -- store the request id in G_REQUEST_TABLE
4853 fnd_file.put_line(FND_FILE.LOG, 'Request Id for Cost Group ' || p_cg_tab(l_current_index).cost_group_id ||' : '||l_submit_req_id);
4854 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4855 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
4856 ,G_MODULE_HEAD || l_routine || '.reqId'
4857 ,'Request '|| l_submit_req_id ||' submitted for Cost Group Id: ' || p_cg_tab(l_current_index).cost_group_id
4858 );
4859 END IF;
4860 G_REQUEST_TABLE(l_worker_idx).request_id := l_submit_req_id;
4861 G_REQUEST_TABLE(l_worker_idx).pac_period_id := p_period_id;
4862 G_REQUEST_TABLE(l_worker_idx).cost_group_id := p_cg_tab(l_current_index).cost_group_id;
4863 l_worker_idx := l_worker_idx + 1;
4864 END IF;
4865
4866 EXIT WHEN l_current_index = p_cg_tab.LAST;
4867
4868 l_current_index := p_cg_tab.NEXT(l_current_index);
4869
4870 LOOP
4871 IF GET_OPEN_REQUESTS_COUNT < p_number_of_workers THEN
4872 EXIT;
4873 END IF;
4874 DBMS_LOCK.sleep(l_sleep_time);
4875 END LOOP;
4876
4877 END LOOP;
4878
4879 LOOP
4880 IF GET_OPEN_REQUESTS_COUNT = 0 THEN
4881 EXIT;
4882 END IF;
4883 DBMS_LOCK.sleep(l_sleep_time);
4884 END LOOP;
4885
4886 --========================================================================================
4887 -- To arrange different cost groups in incresing order of On Hand quantities for each item
4888 --========================================================================================
4889
4890 CST_PAC_ITERATION_PROCESS_PVT.Process_Optimal_Sequence(p_period_id => p_period_id);
4891
4892 -- ====================================================================
4893 -- Absorption Cost Rollup Process
4894 -- ====================================================================
4895 CST_PERIODIC_ABSORPTION_PROC.Absorption_Cost_Process
4896 (p_period_id => p_period_id
4897 ,p_prev_period_id => p_prev_period_id
4898 ,p_legal_entity => p_legal_entity
4899 ,p_cost_type_id => p_cost_type_id
4900 ,p_cg_tab => p_cg_tab
4901 ,p_run_options => p_run_options
4902 ,p_number_of_iterations => p_number_of_iterations
4903 ,p_cost_method => p_cost_method
4904 ,p_start_date => p_start_date
4905 ,p_end_date => p_end_date
4906 ,p_pac_rates_id => p_pac_rates_id
4907 ,p_mat_relief_algorithm => p_mat_relief_algorithm
4908 ,p_uom_control => p_uom_control
4909 ,p_tolerance => p_tolerance
4910 ,p_user_id => l_user_id
4911 ,p_login_id => l_login_id
4912 ,p_req_id => l_req_id
4913 ,p_prg_id => l_prg_id
4914 ,p_prg_appid => l_prg_appid);
4915
4916 -- =============================================================================
4917 -- Invoke Phase 5 Processes
4918 -- If Cost Method is 4 - Incremental LIFO, call lifo_cost_processor
4919 -- Process all logical transactions Drop Shipment / Global Procurement changes
4920 -- Process WIP Close transactions
4921 -- =============================================================================
4922
4923 -- Check for Phase 7 completion and Phase 5 not yet completed
4924 CST_PERIODIC_ABSORPTION_PROC.get_phase_status(p_pac_period_id => p_period_id
4925 ,p_phase => 7
4926 ,p_cost_group_id => NULL
4927 ,x_status => l_phase7_status
4928 );
4929 -- Is Phase 7 complete
4930 IF l_phase7_status = 4 THEN
4931 -- ========================================================
4932 -- Process Phase 5 for all Cost Groups
4933 -- ========================================================
4934
4935 -- Set Phase 5 status to 2 - Running for all Cost Groups
4936 CST_PAC_ITERATION_PROCESS_PVT.Set_Phase5_Status(p_legal_entity_id => p_legal_entity
4937 ,p_period_id => p_period_id
4938 ,p_period_end_date => p_end_date
4939 ,p_phase_status => 2
4940 );
4941
4942 l_current_index := p_cg_tab.FIRST;
4943
4944 LOOP
4945
4946 FND_FILE.put_line
4947 ( FND_FILE.log
4948 , 'Cost Group Id:' || p_cg_tab(l_current_index).cost_group_id
4949 );
4950
4951 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4952 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
4953 ,G_MODULE_HEAD || l_routine || '.cgid'
4954 ,'Cost Group Id:' || p_cg_tab(l_current_index).cost_group_id
4955 );
4956 END IF;
4957
4958 -- Call lifo_cost_processor only if cost method is 4 - Incremental LIFO
4959 IF p_cost_method = 4 THEN
4960
4961 CST_MGD_LIFO_COST_PROCESSOR.Lifo_Cost_Processor
4962 (p_pac_period_id => p_period_id
4963 ,p_cost_group_id => p_cg_tab(l_current_index).cost_group_id
4964 ,p_cost_type_id => p_cost_type_id
4965 ,p_user_id => l_user_id
4966 ,p_login_id => l_login_id
4967 ,p_req_id => l_req_id
4968 ,p_prg_id => l_prg_id
4969 ,p_prg_appl_id => l_prg_appid
4970 ,x_retcode => l_return_code
4971 ,x_errbuff => l_error_msg
4972 ,x_errcode => l_error_code
4973 );
4974
4975 IF l_return_code <> 0 THEN
4976 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
4977 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
4978 FND_MESSAGE.set_token('MESSAGE', 'CST_MGD_LIFO_COST_PROCESSOR.Lifo_Cost_Processor for cost group '||
4979 p_cg_tab(l_current_index).cost_group_id||' ('||l_error_code||') '||l_error_msg);
4980 FND_MSG_PUB.Add;
4981 -- Set Phase 5 status to Error
4982 CST_PAC_ITERATION_PROCESS_PVT.Set_Phase5_Status(p_legal_entity_id => p_legal_entity
4983 ,p_period_id => p_period_id
4984 ,p_period_end_date => p_end_date
4985 ,p_phase_status => 3
4986 );
4987 RAISE FND_API.G_EXC_ERROR;
4988 END IF;
4989
4990 END IF;
4991
4992 -- Process Logical Transactions
4993 CST_PERIODIC_ABSORPTION_PROC.process_logical_txns
4994 (p_period_id => p_period_id
4995 ,p_legal_entity_id => p_legal_entity
4996 ,p_cost_type_id => p_cost_type_id
4997 ,p_cost_group_id => p_cg_tab(l_current_index).cost_group_id
4998 ,p_cost_method => p_cost_method
4999 ,p_master_org_id => p_cg_tab(l_current_index).master_org_id
5000 ,p_uom_control => p_uom_control
5001 ,p_start_date => p_start_date
5002 ,p_end_date => p_end_date
5003 ,p_user_id => l_user_id
5004 ,p_login_id => l_login_id
5005 ,p_req_id => l_req_id
5006 ,p_prg_id => l_prg_id
5007 ,p_prg_appid => l_prg_appid
5008 );
5009
5010 -- Process all close jobs
5011 CSTPPWCL.process_wip_close_txns
5012 (p_pac_period_id => p_period_id
5013 ,p_start_date => p_start_date
5014 ,p_end_date => p_end_date
5015 ,p_cost_group_id => p_cg_tab(l_current_index).cost_group_id
5016 ,p_cost_type_id => p_cost_type_id
5017 ,p_user_id => l_user_id
5018 ,p_login_id => l_login_id
5019 ,p_request_id => l_req_id
5020 ,p_prog_id => l_prg_id
5021 ,p_prog_app_id => l_prg_appid
5022 ,x_err_num => l_error_num
5023 ,x_err_code => l_error_code
5024 ,x_err_msg => l_error_msg
5025 );
5026
5027 IF l_error_num <> 0 THEN
5028 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
5029 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
5030 FND_MESSAGE.set_token('MESSAGE', 'CSTPPWCL.process_wip_close_txns for cost group '||
5031 p_cg_tab(l_current_index).cost_group_id||' ('||l_error_code||') '||l_error_msg);
5032 FND_MSG_PUB.Add;
5033 -- Set Phase 5 status to Error
5034 CST_PAC_ITERATION_PROCESS_PVT.Set_Phase5_Status(p_legal_entity_id => p_legal_entity
5035 ,p_period_id => p_period_id
5036 ,p_period_end_date => p_end_date
5037 ,p_phase_status => 3
5038 );
5039 RAISE FND_API.G_EXC_ERROR;
5040 END IF;
5041
5042
5043 EXIT WHEN l_current_index = p_cg_tab.LAST;
5044
5045 l_current_index := p_cg_tab.NEXT(l_current_index);
5046
5047 END LOOP;
5048
5049 -- Set Phase 5 completion for all cost groups
5050 CST_PAC_ITERATION_PROCESS_PVT.Set_Phase5_Status(p_legal_entity_id => p_legal_entity
5051 ,p_period_id => p_period_id
5052 ,p_period_end_date => p_end_date
5053 ,p_phase_status => 4
5054 );
5055 END IF; -- Phase 7 check
5056
5057
5058 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5059 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
5060 ,G_MODULE_HEAD || l_routine || '.end'
5061 ,l_routine || '>'
5062 );
5063 END IF;
5064
5065 END transfer_cp_worker;
5066
5067 --========================================================================
5068 -- PROCEDURE : Transfer_Cost_Processor_Manager PUBLIC
5069 -- COMMENT : This procedure will perform the validation needed
5070 -- prior to processing the inter-org transfer transactions
5071 --=========================================================================
5072 PROCEDURE transfer_cp_manager
5073 ( p_legal_entity IN NUMBER
5074 , p_cost_type_id IN NUMBER
5075 , p_period_id IN NUMBER
5076 , p_process_upto_date IN VARCHAR2
5077 , p_le_process_upto_date IN VARCHAR2
5078 , p_tolerance IN NUMBER
5079 , p_number_of_iterations IN NUMBER
5080 , p_number_of_workers IN NUMBER
5081 , p_run_options IN NUMBER
5082 , x_return_status OUT NOCOPY VARCHAR2
5083 , x_msg_count OUT NOCOPY NUMBER
5084 , x_msg_data OUT NOCOPY VARCHAR2
5085 )
5086 IS
5087
5088 l_routine CONSTANT VARCHAR2(30) := 'transfer_cp_manager';
5089 --=================
5090 -- CURSORS
5091 --=================
5092
5093 CURSOR cst_grp_csr_type IS
5094 SELECT
5095 ccg.cost_group_id cost_group_id
5096 , ccg.cost_group cost_group
5097 FROM cst_cost_groups ccg
5098 , cst_le_cost_types clct
5099 WHERE ccg.legal_entity = clct.legal_entity
5100 AND clct.legal_entity = p_legal_entity
5101 AND clct.cost_type_id = p_cost_type_id;
5102
5103 cst_grp_csr_row cst_grp_csr_type%rowtype;
5104
5105
5106 --=================
5107 -- VARIABLES
5108 --=================
5109
5110 l_count NUMBER;
5111 l_prev_period_id NUMBER;
5112 l_empty_cons_tab tbl_type;
5113 l_txn_tab tbl_type;
5114 l_current_index BINARY_INTEGER := 0;
5115 l_cost_method NUMBER;
5116 l_uom_control NUMBER;
5117 l_pac_rates_id NUMBER;
5118 l_start_date DATE;
5119 l_end_date DATE;
5120 l_prg_appid NUMBER;
5121 l_prg_id NUMBER;
5122 l_req_id NUMBER;
5123 l_user_id NUMBER;
5124 l_login_id NUMBER;
5125 l_run_options NUMBER;
5126
5127 -- variable for tolerance achieve check
5128 l_tol_achieve_flag VARCHAR2(1);
5129 l_inventory_item_id NUMBER;
5130
5131 -- Material Relief Algorithm - R12 enhancement
5132 l_mat_relief_algorithm NUMBER;
5133
5134 -- Variables for Iteration Process
5135 l_init_msg_list VARCHAR2(1) := FND_API.G_TRUE;
5136 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
5137 l_return_status VARCHAR2(1);
5138 l_msg_count NUMBER;
5139 l_msg_data VARCHAR2(2000);
5140 l_error_msg VARCHAR2(2000);
5141
5142 -- Variable for the pl/sql table l_txn_tab cost group index
5143 l_cost_group_idx BINARY_INTEGER;
5144
5145 BEGIN
5146
5147 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5148 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
5149 ,G_MODULE_HEAD || l_routine || '.begin'
5150 ,l_routine || '<'
5151 );
5152 END IF;
5153
5154 -- initialize the message stack
5155 FND_MSG_PUB.Initialize;
5156
5157 -- Getting Program Information
5158 l_prg_appid := FND_GLOBAL.prog_appl_id;
5159 l_prg_id := FND_GLOBAL.conc_program_id;
5160 l_req_id := FND_GLOBAL.conc_request_id;
5161 l_user_id := FND_GLOBAL.user_id;
5162 l_login_id := FND_GLOBAL.login_id;
5163
5164 l_run_options := p_run_options;
5165
5166 -- Call procedure to check the validity of cost type,
5167 -- legal entity and their association
5168
5169 CST_PERIODIC_ABSORPTION_PROC.validate_le_ct_association
5170 (p_legal_entity => p_legal_entity
5171 ,p_cost_type_id => p_cost_type_id);
5172
5173 -- Call procedure to check that the current period is open
5174 -- legal entity and their association
5175
5176 CST_PERIODIC_ABSORPTION_PROC.validate_period
5177 (p_legal_entity => p_legal_entity
5178 ,p_cost_type_id => p_cost_type_id
5179 ,p_period_id => p_period_id);
5180
5181 -- Validate that previous period has been closed
5182 CST_PERIODIC_ABSORPTION_PROC.validate_previous_period
5183 (p_legal_entity => p_legal_entity
5184 ,p_cost_type_id => p_cost_type_id
5185 ,p_period_id => p_period_id
5186 ,x_prev_period_id => l_prev_period_id);
5187
5188
5189 -- Find the cost method being used for this
5190 -- legal entity/cost type association
5191 -- It needs to be PAC
5192
5193 CST_PERIODIC_ABSORPTION_PROC.find_cost_method
5194 (p_legal_entity => p_legal_entity
5195 ,p_cost_type_id => p_cost_type_id
5196 ,x_cost_method => l_cost_method);
5197
5198 -- Validate that the upto parameter
5199 -- falls within the boundaries of the period when
5200 -- run options is 1 - Start; for all other run
5201 -- options process upto date should be NULL
5202
5203 -- Bug#4351270 fix: time zone validate for process upto date
5204 -- with respect to Legal Entity
5205 CST_PERIODIC_ABSORPTION_PROC.validate_process_upto_date
5206 (p_process_upto_date => p_le_process_upto_date
5207 ,p_period_id => p_period_id
5208 ,p_run_options => p_run_options
5209 );
5210
5211 -- Get Unit of Measure control level
5212
5213 l_uom_control := CST_PERIODIC_ABSORPTION_PROC.get_uom_control_level;
5214
5215
5216 -- Find The Pac Rates and
5217 -- Material Relief Algorithm (introduced in R12)
5218 CST_PERIODIC_ABSORPTION_PROC.find_pac_rates_algorithm
5219 (p_legal_entity => p_legal_entity
5220 ,p_cost_type_id => p_cost_type_id
5221 ,x_pac_rates_id => l_pac_rates_id
5222 ,x_mat_relief_algorithm => l_mat_relief_algorithm
5223 );
5224
5225 -- Get the valid cost groups in a legal entity
5226 IF NOT cst_grp_csr_type%ISOPEN
5227 THEN
5228 OPEN cst_grp_csr_type;
5229 END IF;
5230
5231 -- clear the pl/sql table before use
5232 l_txn_tab := l_empty_cons_tab;
5233
5234 FETCH cst_grp_csr_type
5235 INTO cst_grp_csr_row;
5236
5237 -- Cost Group Id itself is the index
5238 l_cost_group_idx := cst_grp_csr_row.cost_group_id;
5239 l_txn_tab(l_cost_group_idx).cost_group_id := cst_grp_csr_row.cost_group_id;
5240
5241 WHILE cst_grp_csr_type%FOUND
5242 LOOP
5243
5244 FETCH cst_grp_csr_type
5245 INTO cst_grp_csr_row;
5246
5247 l_cost_group_idx := cst_grp_csr_row.cost_group_id;
5248 l_txn_tab(l_cost_group_idx).cost_group_id := cst_grp_csr_row.cost_group_id;
5249
5250 END LOOP;
5251
5252 CLOSE cst_grp_csr_type;
5253
5254 -- Find the Start and End dates for the current period
5255 -- period start date is obtained from cpp
5256 -- period end date is the user entered process upto date
5257 -- For run options 3 - resume for non tolerance and 4 - final iteration
5258 -- l_end_date will be null since the user will not enter any
5259 -- process upto date in the input parameter screen
5260 CST_PERIODIC_ABSORPTION_PROC.find_period_duration
5261 (p_legal_entity => p_legal_entity
5262 ,p_cost_type_id => p_cost_type_id
5263 ,p_period_id => p_period_id
5264 ,p_process_upto_date => p_process_upto_date
5265 ,x_start_date => l_start_date
5266 ,x_end_date => l_end_date);
5267
5268 -- get process upto date for run options 3 - resume for non tolerance
5269 -- and 4 - final iteration
5270 IF l_run_options > 1 THEN
5271
5272 l_end_date := CST_PERIODIC_ABSORPTION_PROC.Find_Prev_Process_Upto_Date
5273 (p_pac_period_id => p_period_id);
5274 END IF;
5275
5276 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5277 FND_LOG.string(FND_LOG.LEVEL_EVENT
5278 ,G_MODULE_HEAD || l_routine || '.dtrange'
5279 ,'Date Range:' || TO_CHAR(l_start_date,'DD-MON-YYYY HH24:MI:SS') || ' ' || TO_CHAR(l_end_date,'DD-MON-YYYY HH24:MI:SS')
5280 );
5281 END IF;
5282
5283 l_current_index := l_txn_tab.FIRST;
5284
5285 LOOP
5286
5287 -- The following checks need to be made for each cost group
5288
5289 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5290 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
5291 ,G_MODULE_HEAD || l_routine || '.cgid'
5292 ,'Cost Group Id:' || l_txn_tab(l_current_index).cost_group_id
5293 );
5294 END IF;
5295
5296 -- Call procedure to validate current cost group
5297
5298 CST_PERIODIC_ABSORPTION_PROC.validate_cost_groups
5299 (p_legal_entity => p_legal_entity
5300 ,p_cost_type_id => p_cost_type_id
5301 ,p_period_id => p_period_id
5302 ,p_cost_group_id => l_txn_tab(l_current_index).cost_group_id);
5303
5304 -- Ensure all appropriate phases in the process phases table
5305 -- are seeded correctly
5306
5307 CST_PERIODIC_ABSORPTION_PROC.validate_phases_seeded
5308 (p_cost_group_id => l_txn_tab(l_current_index).cost_group_id
5309 ,p_period_id => p_period_id);
5310
5311 -- check that the cost group has assignments
5312
5313 CST_PERIODIC_ABSORPTION_PROC.number_of_assignments
5314 (p_cost_group_id => l_txn_tab(l_current_index).cost_group_id
5315 ,p_period_id => p_period_id
5316 ,p_user_id => l_user_id
5317 ,p_login_id => l_login_id
5318 ,p_req_id => l_req_id
5319 ,p_prg_id => l_prg_id
5320 ,p_prg_appid => l_prg_appid);
5321
5322 -- Validate Master Organization
5323
5324 CST_PERIODIC_ABSORPTION_PROC.validate_master_org
5325 (p_legal_entity => p_legal_entity
5326 ,p_cost_type_id => p_cost_type_id
5327 ,p_cost_group_id => l_txn_tab(l_current_index).cost_group_id
5328 ,x_master_org_id => l_txn_tab(l_current_index).master_org_id);
5329
5330 -- Find the starting Phase for the current cost group and
5331 -- store it in a pl/sql table of record
5332
5333 CST_PERIODIC_ABSORPTION_PROC.find_starting_phase
5334 (p_legal_entity => p_legal_entity
5335 ,p_cost_type_id => p_cost_type_id
5336 ,p_period_id => p_period_id
5337 ,p_end_date => l_end_date
5338 ,p_cost_group_id => l_txn_tab(l_current_index).cost_group_id
5339 ,p_run_options => l_run_options
5340 ,x_starting_phase => l_txn_tab(l_current_index).starting_phase
5341 ,p_user_id => l_user_id
5342 ,p_login_id => l_login_id
5343 ,p_req_id => l_req_id
5344 ,p_prg_id => l_prg_id
5345 ,p_prg_appid => l_prg_appid);
5346
5347 FND_FILE.put_line
5348 ( FND_FILE.log
5349 , 'Cost Group Id:' || l_txn_tab(l_current_index).cost_group_id || ' ' ||
5350 'Starting Phase:' || l_txn_tab(l_current_index).starting_phase
5351 );
5352
5353 EXIT WHEN l_current_index = l_txn_tab.LAST;
5354
5355 l_current_index := l_txn_tab.NEXT(l_current_index);
5356
5357 END LOOP;
5358
5359
5360 -- if the run option is 1 then this is the initial
5361 -- processing of these records.
5362 -- if the the run option is 3 then this is a resumption
5363 -- of processing after an error.
5364
5365 CST_PERIODIC_ABSORPTION_PROC.transfer_cp_worker
5366 (p_legal_entity => p_legal_entity
5367 ,p_cost_type_id => p_cost_type_id
5368 ,p_cost_method => l_cost_method
5369 ,p_period_id => p_period_id
5370 ,p_prev_period_id => l_prev_period_id
5371 ,p_tolerance => p_tolerance
5372 ,p_number_of_iterations => p_number_of_iterations
5373 ,p_number_of_workers => p_number_of_workers
5374 ,p_cg_tab => l_txn_tab
5375 ,p_uom_control => l_uom_control
5376 ,p_pac_rates_id => l_pac_rates_id
5377 ,p_mat_relief_algorithm => l_mat_relief_algorithm
5378 ,p_start_date => l_start_date
5379 ,p_end_date => l_end_date
5380 ,p_run_options => l_run_options
5381 );
5382
5383 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5384 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
5385 ,G_MODULE_HEAD || l_routine || '.end'
5386 ,l_routine || '>'
5387 );
5388 END IF;
5389
5390 x_return_status := FND_API.G_RET_STS_SUCCESS;
5391
5392 EXCEPTION
5393 WHEN FND_API.G_EXC_ERROR THEN
5394 IF l_error_msg IS NOT NULL THEN
5395 x_msg_data := l_error_msg;
5396 x_return_status := FND_API.G_RET_STS_ERROR;
5397 ELSE
5398 FND_MSG_PUB.Count_And_Get
5399 (p_encoded => FND_API.G_FALSE
5400 ,p_count => x_msg_count
5401 ,p_data => l_msg_data
5402 );
5403 x_return_status := FND_API.G_RET_STS_ERROR;
5404 x_msg_data := l_msg_data;
5405 END IF;
5406
5407 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5408 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION
5409 , G_MODULE_HEAD || l_routine , x_msg_data
5410 );
5411 END IF;
5412
5413 WHEN OTHERS THEN
5414 x_msg_data := SQLCODE || substr(SQLERRM, 1, 200);
5415 x_return_status := FND_API.G_RET_STS_ERROR;
5416 FND_MSG_PUB.Count_And_Get
5417 (p_encoded => FND_API.G_FALSE
5418 ,p_count => x_msg_count
5419 ,p_data => l_msg_data
5420 );
5421
5422 FND_FILE.put_line
5423 ( FND_FILE.log
5424 , 'Error in transfer_cp_manager '|| x_msg_data || ' ' || substr(l_msg_data, 1,250)
5425 );
5426
5427 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5428 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION
5429 , G_MODULE_HEAD || l_routine ||'.others_exc'
5430 , 'others:' || x_msg_data || ' ' || substr(l_msg_data, 1,250)
5431 );
5432 END IF;
5433
5434 END transfer_cp_manager;
5435
5436 END CST_PERIODIC_ABSORPTION_PROC;