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