[Home] [Help]
PACKAGE BODY: APPS.CST_PERIODIC_AVERAGE_PROC_CP
Source
1 PACKAGE BODY CST_PERIODIC_AVERAGE_PROC_CP AS
2 -- $Header: CSTVITPB.pls 120.7.12010000.4 2008/11/29 16:28:07 anjha ship $
3 --+=======================================================================+
4 --| Copyright (c) 2003 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| CSTVITPB.pls Created By Vamshi Mutyala |
10 --| |
11 --| DESCRIPTION |
12 --| Periodic Average Cost Processor Concurrent Program |
13 --| |
14 --| Bug 6699275 FP:11I-12.0 fix: CSTPPWRO.process_wip_resovhd_txns |
15 --| In the invoked procedure l_err_msg length changed from VARCHAR2(255) |
16 --| to VARCHAR2(2000). Invoked proc build_job_info |
17 --| |
18 --| FP BUG 7342514 FIX: periodic_cost_update value change is removed. |
19 --| periodic_cost_update procedure for PCU value change has to be invoked |
20 --| after processing all the cost owned txns including inter-org receipts |
21 --| across cost groups, after first iteration in the iteration package |
22 --| CSTVIIPB.pls |
23 --| For non-interorg items, periodic_cost_update procedure is invoked |
24 --| in the Periodic Absorption Cost processor outside of iteration proc |
25 --| Periodic_Cost_Update procedure : PCU value change cursor and logic |
26 --| removed since PCU value change is performed using Periodic_Cost_Update|
27 --| _By_Level procedure |
28 --+========================================================================
29
30 --===================
31 -- GLOBALS
32 --===================
33
34 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CST_PERIODIC_AVERAGE_PROC';
35
36 --========================================================================
37 -- PRIVATE CONSTANTS AND VARIABLES
38 --========================================================================
39 G_MODULE_HEAD CONSTANT VARCHAR2(50) := 'cst.plsql.' || G_PKG_NAME || '.';
40
41 TYPE g_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
42 TYPE g_tbl_char_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
43
44 --========================================================================
45 -- PROCEDURE : Set Status PRIVATE
46 -- COMMENT : Set the status of a specific phase
47 --========================================================================
48 PROCEDURE set_status
49 ( p_period_id IN NUMBER
50 , p_cost_group_id IN NUMBER
51 , p_phase IN NUMBER
52 , p_status IN NUMBER
53 , p_end_date IN DATE
54 , p_user_id IN NUMBER
55 , p_login_id IN NUMBER
56 , p_req_id IN NUMBER
57 , p_prg_id IN NUMBER
58 , p_prg_appid IN NUMBER
59 )
60 IS
61
62 l_routine CONSTANT VARCHAR2(30) := 'set_status';
63 --=================
64 -- VARIABLES
65 --=================
66
67 BEGIN
68
69 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
70 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
71 ,G_MODULE_HEAD || l_routine || '.begin'
72 ,l_routine || '<'
73 );
74 END IF;
75
76 UPDATE cst_pac_process_phases
77 SET process_status = p_status,
78 process_date = SYSDATE,
79 process_upto_date = p_end_date,
80 last_update_date = SYSDATE,
81 last_updated_by = p_user_id,
82 request_id = p_req_id,
83 program_application_id = p_prg_appid,
84 program_id = p_prg_id,
85 program_update_date = SYSDATE,
86 last_update_login = p_login_id
87 WHERE pac_period_id = p_period_id
88 AND cost_group_id = p_cost_group_id
89 AND process_phase = p_phase;
90
91 -- the following commit is required to prevent
92 -- a complete rollback if the process errors out
93
94 COMMIT;
95
96 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
97 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
98 ,G_MODULE_HEAD || l_routine || '.end'
99 ,l_routine || '>'
100 );
101 END IF;
102
103 END set_status;
104
105 --========================================================================
106 -- PROCEDURE : Build Job Info PRIVATE
107 -- COMMENT : Build Job Info (essentially Phase 4 for all cost groups)
108 --=========================================================================
109 PROCEDURE build_job_info
110 ( p_period_id IN NUMBER
111 , p_start_date IN DATE
112 , p_end_date IN DATE
113 , p_cost_group_id IN NUMBER
114 , p_cost_type_id IN NUMBER
115 , p_pac_rates_id IN NUMBER
116 , p_user_id IN NUMBER
117 , p_login_id IN NUMBER
118 , p_req_id IN NUMBER
119 , p_prg_id IN NUMBER
120 , p_prg_appid IN NUMBER
121 )
122 IS
123
124 l_routine CONSTANT VARCHAR2(30) := 'build_job_info';
125 --=================
126 -- VARIABLES
127 --=================
128
129 l_item_id NUMBER := NULL;
130 l_error_num NUMBER;
131 l_error_code VARCHAR2(240);
132 l_error_msg VARCHAR2(2000);
133
134 BEGIN
135
136 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
137 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
138 ,G_MODULE_HEAD || l_routine || '.begin'
139 ,l_routine || '<'
140 );
141 END IF;
142
143 set_status
144 (p_period_id => p_period_id
145 ,p_cost_group_id => p_cost_group_id
146 ,p_phase => 4
147 ,p_status => 2
148 ,p_end_date => p_end_date
149 ,p_user_id => p_user_id
150 ,p_login_id => p_login_id
151 ,p_req_id => p_req_id
152 ,p_prg_id => p_prg_id
153 ,p_prg_appid => p_prg_appid);
154
155 CSTPPWRO.process_wip_resovhd_txns
156 (p_pac_period_id => p_period_id
157 ,p_start_date => p_start_date
158 ,p_end_date => p_end_date
159 ,p_cost_group_id => p_cost_group_id
160 ,p_cost_type_id => p_cost_type_id
161 ,p_item_id => l_item_id
162 ,p_pac_ct_id => p_pac_rates_id
163 ,p_user_id => p_user_id
164 ,p_login_id => p_login_id
165 ,p_request_id => p_req_id
166 ,p_prog_id => p_prg_id
167 ,p_prog_app_id => p_prg_appid
168 ,x_err_num => l_error_num
169 ,x_err_code => l_error_code
170 ,x_err_msg => l_error_msg);
171
172 l_error_num := NVL(l_error_num, 0);
173 l_error_code := NVL(l_error_code, 'No Error');
174 l_error_msg := NVL(l_error_msg, 'No Error');
175
176 IF l_error_num <> 0
177 THEN
178
179 set_status
180 (p_period_id => p_period_id
181 ,p_cost_group_id => p_cost_group_id
182 ,p_phase => 4
183 ,p_status => 3
184 ,p_end_date => p_end_date
185 ,p_user_id => p_user_id
186 ,p_login_id => p_login_id
187 ,p_req_id => p_req_id
188 ,p_prg_id => p_prg_id
189 ,p_prg_appid => p_prg_appid);
190
191 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
192 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
193 FND_MESSAGE.set_token('MESSAGE', 'process_wip_resovhd_txns for cost group '||p_cost_group_id||' ('||l_error_code||') '||l_error_msg);
194 FND_MSG_PUB.Add;
195 RAISE FND_API.G_EXC_ERROR;
196
197 ELSE
198
199 set_status
200 (p_period_id => p_period_id
201 ,p_cost_group_id => p_cost_group_id
202 ,p_phase => 4
203 ,p_status => 4
204 ,p_end_date => p_end_date
205 ,p_user_id => p_user_id
206 ,p_login_id => p_login_id
207 ,p_req_id => p_req_id
208 ,p_prg_id => p_prg_id
209 ,p_prg_appid => p_prg_appid);
210
211 END IF;
212
213 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
214 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
215 ,G_MODULE_HEAD || l_routine || '.end'
216 ,l_routine || '>'
217 );
218 END IF;
219
220 END build_job_info;
221
222 --========================================================================
223 -- PROCEDURE : Explode Bom PRIVATE
224 -- COMMENT : Explode Bill Of Materials for all Cost Groups
225 --=========================================================================
226 PROCEDURE explode_bom
227 ( p_period_id IN NUMBER
228 , p_cost_group_id IN NUMBER
229 , p_start_date IN DATE
230 , p_end_date IN DATE
231 , p_user_id IN NUMBER
232 , p_login_id IN NUMBER
233 , p_req_id IN NUMBER
234 , p_prg_id IN NUMBER
235 , p_prg_appid IN NUMBER
236 )
237 IS
238
239 l_routine CONSTANT VARCHAR2(30) := 'explode_bom';
240 --=================
241 -- VARIABLES
242 --=================
243
244 l_error_num NUMBER;
245 l_error_code VARCHAR2(240);
246 l_error_msg VARCHAR2(240);
247
248 BEGIN
249
250 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
251 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
252 ,G_MODULE_HEAD || l_routine || '.begin'
253 ,l_routine || '<'
254 );
255 END IF;
256
257 set_status
258 (p_period_id => p_period_id
259 ,p_cost_group_id => p_cost_group_id
260 ,p_phase => 3
261 ,p_status => 2
262 ,p_end_date => p_end_date
263 ,p_user_id => p_user_id
264 ,p_login_id => p_login_id
265 ,p_req_id => p_req_id
266 ,p_prg_id => p_prg_id
267 ,p_prg_appid => p_prg_appid);
268
269 CSTPPLLC.pac_low_level_codes
270 (i_pac_period_id => p_period_id
271 ,i_cost_group_id => p_cost_group_id
272 ,i_start_date => p_start_date
273 ,i_end_date => p_end_date
274 ,i_user_id => p_user_id
275 ,i_login_id => p_login_id
276 ,i_request_id => p_req_id
277 ,i_prog_id => p_prg_id
278 ,i_prog_app_id => p_prg_appid
279 ,o_err_num => l_error_num
280 ,o_err_code => l_error_code
281 ,o_err_msg => l_error_msg);
282
283 l_error_num := NVL(l_error_num, 0);
284 l_error_code := NVL(l_error_code, 'No Error');
285 l_error_msg := NVL(l_error_msg, 'No Error');
286
287 IF l_error_num <> 0
288 THEN
289
290 set_status
291 (p_period_id => p_period_id
292 ,p_cost_group_id => p_cost_group_id
293 ,p_phase => 3
294 ,p_status => 3
295 ,p_end_date => p_end_date
296 ,p_user_id => p_user_id
297 ,p_login_id => p_login_id
298 ,p_req_id => p_req_id
299 ,p_prg_id => p_prg_id
300 ,p_prg_appid => p_prg_appid);
301
302 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
303 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
304 ,G_MODULE_HEAD || l_routine || '.paclowcode'
305 ,l_error_msg
306 );
307 END IF;
308
309 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
310 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
311 FND_MESSAGE.set_token('MESSAGE', 'pac_low_level_codes for cost group '||p_cost_group_id||' ('||l_error_code||') '||l_error_msg);
312 FND_MSG_PUB.Add;
313 RAISE FND_API.G_EXC_ERROR;
314
315 ELSE
316
317 set_status
318 (p_period_id => p_period_id
319 ,p_cost_group_id => p_cost_group_id
320 ,p_phase => 3
321 ,p_status => 4
322 ,p_end_date => p_end_date
323 ,p_user_id => p_user_id
324 ,p_login_id => p_login_id
325 ,p_req_id => p_req_id
326 ,p_prg_id => p_prg_id
327 ,p_prg_appid => p_prg_appid);
328
329 END IF;
330
331 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
332 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
333 ,G_MODULE_HEAD || l_routine || '.end'
334 ,l_routine || '>'
335 );
336 END IF;
337
338 END explode_bom;
339
340 --========================================================================
341 -- PROCEDURE : Copy Balance PRIVATE
342 -- COMMENT : Bring Forward the Beginning Balance from the previous period
343 --=========================================================================
344 PROCEDURE copy_balance
345 ( p_period_id IN NUMBER
346 , p_prev_period_id IN NUMBER
347 , p_end_date IN DATE
348 , p_legal_entity IN NUMBER
349 , p_cost_type_id IN NUMBER
350 , p_cost_group_id IN NUMBER
351 , p_cost_method IN NUMBER
352 , p_user_id IN NUMBER
353 , p_login_id IN NUMBER
354 , p_req_id IN NUMBER
355 , p_prg_id IN NUMBER
356 , p_prg_appid IN NUMBER
357 , p_starting_phase IN NUMBER
358 )
359 IS
360
361 l_routine CONSTANT VARCHAR2(30) := 'copy_balance';
362 --=================
363 -- VARIABLES
364 --=================
365
366 -- Initialize to purge phase 2 to 5
367 l_acquisition_flag NUMBER := 0;
368
369 l_error_num NUMBER;
370 l_error_code VARCHAR2(240);
371 l_error_msg VARCHAR2(240);
372
373 BEGIN
374
375 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
376 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
377 ,G_MODULE_HEAD || l_routine || '.begin'
378 ,l_routine || '<'
379 );
380 END IF;
381
382 set_status
383 (p_period_id => p_period_id
384 ,p_cost_group_id => p_cost_group_id
385 ,p_phase => 2
386 ,p_status => 2
387 ,p_end_date => p_end_date
388 ,p_user_id => p_user_id
389 ,p_login_id => p_login_id
390 ,p_req_id => p_req_id
391 ,p_prg_id => p_prg_id
392 ,p_prg_appid => p_prg_appid);
393
394 IF p_starting_phase = 2 THEN
395 CSTPPPUR.purge_period_data
396 ( i_pac_period_id => p_period_id
397 , i_legal_entity => p_legal_entity
398 , i_cost_group_id => p_cost_group_id
399 , i_acquisition_flag => l_acquisition_flag
400 , i_user_id => p_user_id
401 , i_login_id => p_login_id
402 , i_request_id => p_req_id
403 , i_prog_id => p_prg_id
404 , i_prog_app_id => p_prg_appid
405 , o_err_num => l_error_num
406 , o_err_code => l_error_code
407 , o_err_msg => l_error_msg);
408
409 l_error_num := NVL(l_error_num, 0);
410 l_error_code := NVL(l_error_code, 'No Error');
411 l_error_msg := NVL(l_error_msg, 'No Error');
412
416 (p_period_id => p_period_id
413 IF l_error_num <> 0
414 THEN
415 set_status
417 ,p_cost_group_id => p_cost_group_id
418 ,p_phase => 2
419 ,p_status => 3
420 ,p_end_date => p_end_date
421 ,p_user_id => p_user_id
422 ,p_login_id => p_login_id
423 ,p_req_id => p_req_id
424 ,p_prg_id => p_prg_id
425 ,p_prg_appid => p_prg_appid);
426
427 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
428 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
429 FND_MESSAGE.set_token('MESSAGE', 'purge_period_data for cost group '||p_cost_group_id||' ('||l_error_code||') '||l_error_msg);
430 FND_MSG_PUB.Add;
431 RAISE FND_API.G_EXC_ERROR;
432 END IF;
433 END IF;
434
435 CSTPPBBS.copy_prior_info
436 ( i_pac_period_id => p_period_id
437 , i_prior_pac_period_id => p_prev_period_id
438 , i_legal_entity => p_legal_entity
439 , i_cost_type_id => p_cost_type_id
440 , i_cost_group_id => p_cost_group_id
441 , i_cost_method => p_cost_method
442 , i_user_id => p_user_id
443 , i_login_id => p_login_id
444 , i_request_id => p_req_id
445 , i_prog_id => p_prg_id
446 , i_prog_app_id => p_prg_appid
447 , o_err_num => l_error_num
448 , o_err_code => l_error_code
449 , o_err_msg => l_error_msg);
450
451 l_error_num := NVL(l_error_num, 0);
452 l_error_code := NVL(l_error_code, 'No Error');
453 l_error_msg := NVL(l_error_msg, 'No Error');
454
455 IF l_error_num <> 0 THEN
456 -- Set phase 2 to 3 - Error
457 set_status
458 (p_period_id => p_period_id
459 ,p_cost_group_id => p_cost_group_id
460 ,p_phase => 2
461 ,p_status => 3
462 ,p_end_date => p_end_date
463 ,p_user_id => p_user_id
464 ,p_login_id => p_login_id
465 ,p_req_id => p_req_id
466 ,p_prg_id => p_prg_id
467 ,p_prg_appid => p_prg_appid);
468
469 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
470 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
471 FND_MESSAGE.set_token('MESSAGE', 'copy_prior_info for cost group '||p_cost_group_id||' ('||l_error_code||') '||l_error_msg);
472 FND_MSG_PUB.Add;
473 RAISE FND_API.G_EXC_ERROR;
474
475 ELSE
476 -- Set Phase 2 to 4 - Completion
477 set_status
478 (p_period_id => p_period_id
479 ,p_cost_group_id => p_cost_group_id
480 ,p_phase => 2
481 ,p_status => 4
482 ,p_end_date => p_end_date
483 ,p_user_id => p_user_id
484 ,p_login_id => p_login_id
485 ,p_req_id => p_req_id
486 ,p_prg_id => p_prg_id
487 ,p_prg_appid => p_prg_appid);
488
489 END IF;
490
491 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
492 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
493 ,G_MODULE_HEAD || l_routine || '.end'
494 ,l_routine || '>'
495 );
496 END IF;
497
498 END copy_balance;
499
500 --========================================================================
501 -- PROCEDURE : Run Acquisition Cost Processor PRIVATE
502 -- COMMENT : Aquisition Cost Processor
503 --=========================================================================
504 PROCEDURE run_acquisition_cp
505 ( p_period_id IN NUMBER
506 , p_start_date IN DATE
507 , p_end_date IN DATE
508 , p_legal_entity IN NUMBER
509 , p_cost_type_id IN NUMBER
510 , p_cost_group_id IN NUMBER
511 , p_user_id IN NUMBER
512 , p_login_id IN NUMBER
513 , p_req_id IN NUMBER
514 , p_prg_id IN NUMBER
515 , p_prg_appid IN NUMBER
516 )
517 IS
518
519 l_routine CONSTANT VARCHAR2(30) := 'run_acquisition_cp';
520 --=================
521 -- VARIABLES
522 --=================
523
524 -- Initialize to purge all phases
525 l_acquisition_flag NUMBER := 1;
526
527 l_error_num NUMBER;
528 l_error_code VARCHAR2(240);
529 l_error_msg VARCHAR2(240);
530
531 BEGIN
532
533 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
534 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
535 ,G_MODULE_HEAD || l_routine || '.begin'
536 ,l_routine || '<'
537 );
538 END IF;
539
540 set_status
541 (p_period_id => p_period_id
542 ,p_cost_group_id => p_cost_group_id
543 ,p_phase => 1
544 ,p_status => 2
545 ,p_end_date => p_end_date
546 ,p_user_id => p_user_id
547 ,p_login_id => p_login_id
548 ,p_req_id => p_req_id
549 ,p_prg_id => p_prg_id
550 ,p_prg_appid => p_prg_appid);
551
552 -- ==============================================================
556 -- ==============================================================
553 -- Prerequisite for Phase 1 is to purge all phases
554 -- If purging errors out, the status of phase 1 will be set to
555 -- ERROR. Otherwise it continues to process phase 1.
557 CSTPPPUR.purge_period_data
558 ( i_pac_period_id => p_period_id
559 , i_legal_entity => p_legal_entity
560 , i_cost_group_id => p_cost_group_id
561 , i_acquisition_flag => l_acquisition_flag
562 , i_user_id => p_user_id
563 , i_login_id => p_login_id
564 , i_request_id => p_req_id
565 , i_prog_id => p_prg_id
566 , i_prog_app_id => p_prg_appid
567 , o_err_num => l_error_num
568 , o_err_code => l_error_code
569 , o_err_msg => l_error_msg);
570
571 l_error_num := NVL(l_error_num, 0);
572 l_error_code := NVL(l_error_code, 'No Error');
573 l_error_msg := NVL(l_error_msg, 'No Error');
574
575 IF l_error_num <> 0
576 THEN
577 set_status
578 (p_period_id => p_period_id
579 ,p_cost_group_id => p_cost_group_id
580 ,p_phase => 1
581 ,p_status => 3
582 ,p_end_date => p_end_date
583 ,p_user_id => p_user_id
584 ,p_login_id => p_login_id
585 ,p_req_id => p_req_id
586 ,p_prg_id => p_prg_id
587 ,p_prg_appid => p_prg_appid);
588
589 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
590 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
591 FND_MESSAGE.set_token('MESSAGE', 'purge_period_data for cost group '||p_cost_group_id||' ('||l_error_code||') '||l_error_msg);
592 FND_MSG_PUB.Add;
593 RAISE FND_API.G_EXC_ERROR;
594 END IF;
595
596 CSTPPACQ.acq_cost_processor
597 (i_period => p_period_id
598 ,i_start_date => p_start_date
599 ,i_end_date => p_end_date
600 ,i_cost_type_id => p_cost_type_id
601 ,i_cost_group_id => p_cost_group_id
602 ,i_user_id => p_user_id
603 ,i_login_id => p_login_id
604 ,i_req_id => p_req_id
605 ,i_prog_id => p_prg_id
606 ,i_prog_appl_id => p_prg_appid
607 ,o_err_num => l_error_num
608 ,o_err_code => l_error_code
609 ,o_err_msg => l_error_msg);
610
611 l_error_num := NVL(l_error_num, 0);
612 l_error_code := NVL(l_error_code, 'No Error');
613 l_error_msg := NVL(l_error_msg, 'No Error');
614
615 IF l_error_num <> 0
616 THEN
617
618 set_status
619 (p_period_id => p_period_id
620 ,p_cost_group_id => p_cost_group_id
621 ,p_phase => 1
622 ,p_status => 3
623 ,p_end_date => p_end_date
624 ,p_user_id => p_user_id
625 ,p_login_id => p_login_id
626 ,p_req_id => p_req_id
627 ,p_prg_id => p_prg_id
628 ,p_prg_appid => p_prg_appid);
629
630 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
631 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
632 FND_MESSAGE.set_token('MESSAGE', 'acq_cost_processor for cost group '||p_cost_group_id||' ('||l_error_code||') '||l_error_msg);
633 FND_MSG_PUB.Add;
634 RAISE FND_API.G_EXC_ERROR;
635
636 ELSE
637
638 set_status
639 (p_period_id => p_period_id
640 ,p_cost_group_id => p_cost_group_id
641 ,p_phase => 1
642 ,p_status => 4
643 ,p_end_date => p_end_date
644 ,p_user_id => p_user_id
645 ,p_login_id => p_login_id
646 ,p_req_id => p_req_id
647 ,p_prg_id => p_prg_id
648 ,p_prg_appid => p_prg_appid);
649
650 END IF;
651
652 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
653 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
654 ,G_MODULE_HEAD || l_routine || '.end'
655 ,l_routine || '>'
656 );
657 END IF;
658
659 END run_acquisition_cp;
660
661 --========================================================================
662 -- PROCEDURE : prepare absorption process PRIVATE
663 -- COMMENT :
664 --=========================================================================
665
666 PROCEDURE prepare_absorption_process
667 (p_period_id IN NUMBER
668 ,p_start_date IN DATE
669 ,p_end_date IN DATE
670 ,p_cost_group_id IN NUMBER
671 ,p_cost_type_id IN NUMBER
672 ,p_pac_rates_id IN NUMBER
673 ,p_user_id IN NUMBER
674 ,p_login_id IN NUMBER
675 ,p_req_id IN NUMBER
676 ,p_prg_id IN NUMBER
677 ,p_prg_appid IN NUMBER
678 )
679 IS
680
681 l_routine CONSTANT VARCHAR2(30) := 'prepare_absorption_process';
682
683 l_return_status VARCHAR2(1);
684 l_msg_count NUMBER;
685 l_msg_data VARCHAR2(2000);
686
687 BEGIN
688
689 -- Delete interorg item records for the period
693
690 DELETE CST_PAC_INTORG_ITMS_TEMP
691 WHERE pac_period_id = p_period_id
692 AND cost_group_id = p_cost_group_id;
694 -- Delete MPACD TEMP records for the period
695 DELETE MTL_PAC_ACT_CST_DTL_TEMP
696 WHERE pac_period_id = p_period_id
697 AND cost_group_id = p_cost_group_id;
698
699 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
700 FND_LOG.string(FND_LOG.LEVEL_EVENT
701 , G_MODULE_HEAD || l_routine || '.Before_retrieve_items'
702 , 'Before Retrieve interorg items'
703 );
704 END IF;
705
706 -- ========================================================
707 -- Populate temporary table CST_PAC_INTERORG_TXNS_TMP
708 -- with across CG interorg transactions
709 -- ========================================================
710 CST_PAC_ITERATION_PROCESS_PVT.Populate_Temp_Tables(
711 p_cost_group_id => p_cost_group_id
712 ,p_period_id => p_period_id
713 ,p_period_start_date => p_start_date
714 ,p_period_end_date => p_end_date
715 );
716 -- ========================================================
717 -- Retrieve Interorg Items for the period
718 -- This will be invoked only once during Run Options Start
719 -- Interorg Items are stored in temporary table
720 -- Interorg Items are assigned with Absorption Level Codes
721 -- ========================================================
722 CST_PAC_ITERATION_PROCESS_PVT.Retrieve_Interorg_Items
723 (p_period_id => p_period_id
724 ,p_cost_group_id => p_cost_group_id
725 ,p_period_start_date => p_start_date
726 ,p_period_end_date => p_end_date
727 );
728
729 END prepare_absorption_process;
730
731 --========================================================================
732 -- PROCEDURE : Periodic Cost Update PRIVATE
733 -- COMMENT : Run the cost processor for modes
734 -- : periodic cost update (new cost, and % change
735 -- : new cost, and %ge change has txn category 2
736 --=========================================================================
737 PROCEDURE Periodic_Cost_Update
738 ( p_period_id IN NUMBER
739 , p_legal_entity IN NUMBER
740 , p_cost_type_id IN NUMBER
741 , p_cost_group_id IN NUMBER
742 , p_cost_method IN NUMBER
743 , p_start_date IN DATE
744 , p_end_date IN DATE
745 , p_pac_rates_id IN NUMBER
746 , p_master_org_id IN NUMBER
747 , p_cost_update_type IN NUMBER
748 , p_uom_control IN NUMBER
749 , p_user_id IN NUMBER
750 , p_login_id IN NUMBER
751 , p_req_id IN NUMBER
752 , p_prg_id IN NUMBER
753 , p_prg_appid IN NUMBER
754 )
755 IS
756
757 l_routine CONSTANT VARCHAR2(30) := 'periodic_cost_update';
758
759
760 --=================
761 -- CURSORS
762 --=================
763
764 -- =============================================
765 -- PCU New Cost or %ge txns for all items
766 -- =============================================
767 CURSOR upd_new_csr_type IS
768 SELECT mmt.transaction_id
769 , mmt.transaction_action_id
770 , mmt.transaction_source_type_id
771 , mmt.inventory_item_id
772 , mmt.primary_quantity
773 , mmt.organization_id
774 , nvl(mmt.transfer_organization_id,-1) transfer_organization_id
775 , mmt.subinventory_code
776 FROM mtl_material_transactions mmt
777 , mtl_transaction_types mtt
778 WHERE mmt.transaction_date between p_start_date
779 AND p_end_date
780 AND mmt.transaction_action_id = 24
781 AND mmt.transaction_source_type_id = 14
782 AND mtt.transaction_action_id = mmt.transaction_action_id
783 AND mtt.transaction_source_type_id = mmt.transaction_source_type_id
784 AND mmt.transaction_type_id = mtt.transaction_type_id
785 AND (new_cost IS NOT NULL or percentage_change IS NOT NULL)
786 AND NVL(org_cost_group_id,-1) = p_cost_group_id
787 AND NVL(cost_type_id,-1) = p_cost_type_id;
788
789 CURSOR upd_val_csr_type IS
790 SELECT mmt.transaction_id
791 , mmt.transaction_action_id
792 , mmt.transaction_source_type_id
793 , mmt.inventory_item_id
794 , mmt.primary_quantity
795 , mmt.organization_id
796 , nvl(mmt.transfer_organization_id,-1) transfer_organization_id
797 , mmt.subinventory_code
798 FROM mtl_material_transactions mmt
799 , mtl_transaction_types mtt
800 WHERE mmt.transaction_date between p_start_date
801 AND p_end_date
802 AND mmt.transaction_action_id = 24
803 AND mmt.transaction_source_type_id = 14
804 AND mtt.transaction_action_id = mmt.transaction_action_id
805 AND mtt.transaction_source_type_id = mmt.transaction_source_type_id
806 AND mmt.transaction_type_id = mtt.transaction_type_id
807 AND mmt.value_change IS NOT NULL
808 AND mmt.primary_quantity > 0
809 AND NVL(org_cost_group_id,-1) = p_cost_group_id
810 AND NVL(cost_type_id,-1) = p_cost_type_id;
811
812
813
814 TYPE upd_val_tab IS TABLE OF upd_new_csr_type%rowtype INDEX BY BINARY_INTEGER;
815
819 -- VARIABLES
816 l_upd_val_tab upd_val_tab;
817 l_empty_upd_val_tab upd_val_tab;
818 --=================
820 --=================
821
822
823 l_error_num NUMBER;
824 l_error_code VARCHAR2(240);
825 l_error_msg VARCHAR2(240);
826 l_exp_flag NUMBER;
827 l_exp_item NUMBER;
828 l_process_group NUMBER := 0;
829
830 l_loop_count NUMBER := 0;
831 -- Transaction category variable
832 l_txn_category NUMBER;
833
834 BEGIN
835
836 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
837 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
838 ,G_MODULE_HEAD || l_routine || '.begin'
839 ,l_routine || '<'
840 );
841 END IF;
842
843 -- ===============================
844 -- Initialize transaction category
845 -- ===============================
846 -- Set Transaction category for PCU New Cost or %ge
847 l_txn_category := 2;
848
849
850 -- PCU New Cost or %ge txns
851 IF NOT upd_new_csr_type%ISOPEN
852 THEN
853 OPEN upd_new_csr_type;
854 END IF;
855
856 -- clear the pl/sql table before use
857 l_upd_val_tab := l_empty_upd_val_tab;
858 FETCH upd_new_csr_type BULK COLLECT INTO l_upd_val_tab;
859
860 CLOSE upd_new_csr_type;
861
862
863 l_loop_count := l_upd_val_tab.COUNT;
864
865 l_error_num := 0;
866
867 FOR i IN 1..l_loop_count
868 LOOP
869
870 IF l_error_num = 0 THEN
871
872 CSTPPINV.cost_inv_txn
873 (i_pac_period_id => p_period_id
874 ,i_legal_entity => p_legal_entity
875 ,i_cost_type_id => p_cost_type_id
876 ,i_cost_group_id => p_cost_group_id
877 ,i_cost_method => p_cost_method
878 ,i_txn_id => l_upd_val_tab(i).transaction_id
879 ,i_txn_action_id => l_upd_val_tab(i).transaction_action_id
880 ,i_txn_src_type_id => l_upd_val_tab(i).transaction_source_type_id
881 ,i_item_id => l_upd_val_tab(i).inventory_item_id
882 ,i_txn_qty => l_upd_val_tab(i).primary_quantity
883 ,i_txn_org_id => l_upd_val_tab(i).organization_id
884 ,i_txfr_org_id => l_upd_val_tab(i).transfer_organization_id
885 ,i_subinventory_code => l_upd_val_tab(i).subinventory_code
886 ,i_exp_flag => l_exp_flag
887 ,i_exp_item => l_exp_item
888 ,i_pac_rates_id => p_pac_rates_id
889 ,i_process_group => l_process_group
890 ,i_master_org_id => p_master_org_id
891 ,i_uom_control => p_uom_control
892 ,i_user_id => p_user_id
893 ,i_login_id => p_login_id
894 ,i_request_id => p_req_id
895 ,i_prog_id => p_prg_id
896 ,i_prog_appl_id => p_prg_appid
897 ,i_txn_category => l_txn_category
898 ,i_transfer_price_pd => 0
899 ,o_err_num => l_error_num
900 ,o_err_code => l_error_code
901 ,o_err_msg => l_error_msg);
902
903 l_error_num := NVL(l_error_num, 0);
904 l_error_code := NVL(l_error_code, 'No Error');
905 l_error_msg := NVL(l_error_msg, 'No Error');
906
907 IF l_error_num <> 0
908 THEN
909 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
910 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION
911 , G_MODULE_HEAD || l_routine || '.others'
912 , 'cost_inv_txn for cost group '||p_cost_group_id||' txn id '
913 ||l_upd_val_tab(i).transaction_id||' ('||l_error_code||') '||l_error_msg
914 );
915 END IF;
916
917 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
918 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
919 FND_MESSAGE.set_token('MESSAGE', 'cost_inv_txn for cost group '||p_cost_group_id||' txn id '
920 ||l_upd_val_tab(i).transaction_id||' ('||l_error_code||') '||l_error_msg);
921 FND_MSG_PUB.Add;
922 RAISE FND_API.G_EXC_ERROR;
923 END IF;
924
925 END IF; -- error num check
926
927 END LOOP;
928
929 l_txn_category := 2.5;
930
931
932 -- PCU value change with qty txns
933 IF NOT upd_val_csr_type%ISOPEN
934 THEN
935 OPEN upd_val_csr_type;
936 END IF;
937
938 -- clear the pl/sql table before use
939 l_upd_val_tab := l_empty_upd_val_tab;
940 FETCH upd_val_csr_type BULK COLLECT INTO l_upd_val_tab;
941
942 CLOSE upd_val_csr_type;
943
944 l_loop_count := 0;
945 l_loop_count := l_upd_val_tab.COUNT;
946
947
948 FOR i IN 1..l_loop_count
949 LOOP
950 l_error_num := 0;
951 IF (CSTPPINV.l_item_id_tbl.COUNT >= 1000) THEN
952 CSTPPWAC.insert_into_cppb(i_pac_period_id => p_period_id
953 ,i_cost_group_id => p_cost_group_id
954 ,i_txn_category => l_txn_category
955 ,i_user_id => p_user_id
956 ,i_login_id => p_login_id
960 ,o_err_num => l_error_num
957 ,i_request_id => p_req_id
958 ,i_prog_id => p_prg_id
959 ,i_prog_appl_id => p_prg_appid
961 ,o_err_code => l_error_code
962 ,o_err_msg => l_error_msg
963 );
964 l_error_num := NVL(l_error_num, 0);
965 l_error_code := NVL(l_error_code, 'No Error');
966 l_error_msg := NVL(l_error_msg, 'No Error');
967
968 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
969 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
970 ,G_MODULE_HEAD || l_routine || '.inscppb5'
971 ,'After calling insert_into_cppb:'|| l_error_num || l_error_code || l_error_msg
972 );
973 END IF;
974 END IF; -- item table count check
975
976 IF l_error_num = 0 THEN
977
978 CSTPPINV.cost_inv_txn
979 (i_pac_period_id => p_period_id
980 ,i_legal_entity => p_legal_entity
981 ,i_cost_type_id => p_cost_type_id
982 ,i_cost_group_id => p_cost_group_id
983 ,i_cost_method => p_cost_method
984 ,i_txn_id => l_upd_val_tab(i).transaction_id
985 ,i_txn_action_id => l_upd_val_tab(i).transaction_action_id
986 ,i_txn_src_type_id => l_upd_val_tab(i).transaction_source_type_id
987 ,i_item_id => l_upd_val_tab(i).inventory_item_id
988 ,i_txn_qty => l_upd_val_tab(i).primary_quantity
989 ,i_txn_org_id => l_upd_val_tab(i).organization_id
990 ,i_txfr_org_id => l_upd_val_tab(i).transfer_organization_id
991 ,i_subinventory_code => l_upd_val_tab(i).subinventory_code
992 ,i_exp_flag => l_exp_flag
993 ,i_exp_item => l_exp_item
994 ,i_pac_rates_id => p_pac_rates_id
995 ,i_process_group => 1
996 ,i_master_org_id => p_master_org_id
997 ,i_uom_control => p_uom_control
998 ,i_user_id => p_user_id
999 ,i_login_id => p_login_id
1000 ,i_request_id => p_req_id
1001 ,i_prog_id => p_prg_id
1002 ,i_prog_appl_id => p_prg_appid
1003 ,i_txn_category => l_txn_category
1004 ,i_transfer_price_pd => 0
1005 ,o_err_num => l_error_num
1006 ,o_err_code => l_error_code
1007 ,o_err_msg => l_error_msg);
1008
1009 l_error_num := NVL(l_error_num, 0);
1010 l_error_code := NVL(l_error_code, 'No Error');
1011 l_error_msg := NVL(l_error_msg, 'No Error');
1012
1013 IF l_error_num <> 0
1014 THEN
1015 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1016 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION
1017 , G_MODULE_HEAD || l_routine || '.others'
1018 , 'cost_inv_txn for cost group '||p_cost_group_id||' txn id '
1019 ||l_upd_val_tab(i).transaction_id||' ('||l_error_code||') '||l_error_msg
1020 );
1021 END IF;
1022
1023 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
1024 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
1025 FND_MESSAGE.set_token('MESSAGE', 'cost_inv_txn for cost group '||p_cost_group_id||' txn id '
1026 ||l_upd_val_tab(i).transaction_id||' ('||l_error_code||') '||l_error_msg);
1027 FND_MSG_PUB.Add;
1028 RAISE FND_API.G_EXC_ERROR;
1029 END IF;
1030
1031 END IF;
1032
1033
1034 END LOOP;
1035 IF (CSTPPINV.l_item_id_tbl.COUNT > 0) THEN
1036 CSTPPWAC.insert_into_cppb(i_pac_period_id => p_period_id
1037 ,i_cost_group_id => p_cost_group_id
1038 ,i_txn_category => l_txn_category
1039 ,i_user_id => p_user_id
1040 ,i_login_id => p_login_id
1041 ,i_request_id => p_req_id
1042 ,i_prog_id => p_prg_id
1043 ,i_prog_appl_id => p_prg_appid
1044 ,o_err_num => l_error_num
1045 ,o_err_code => l_error_code
1046 ,o_err_msg => l_error_msg
1047 );
1048
1049 l_error_num := NVL(l_error_num, 0);
1050 l_error_code := NVL(l_error_code, 'No Error');
1051 l_error_msg := NVL(l_error_msg, 'No Error');
1052
1053 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1054 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1055 ,G_MODULE_HEAD || l_routine || '.inscppb'
1056 ,'After calling insert_into_cppb:'|| l_error_num || l_error_code || l_error_msg
1057 );
1058 END IF;
1059
1060 END IF;
1061
1062 IF (l_error_num = 0 AND l_loop_count > 0) THEN
1063 CSTPPWAC.update_cppb(i_pac_period_id => p_period_id
1064 ,i_cost_group_id => p_cost_group_id
1068 ,i_login_id => p_login_id
1065 ,i_txn_category => l_txn_category
1066 ,i_low_level_code => -2
1067 ,i_user_id => p_user_id
1069 ,i_request_id => p_req_id
1070 ,i_prog_id => p_prg_id
1071 ,i_prog_appl_id => p_prg_appid
1072 ,o_err_num => l_error_num
1073 ,o_err_code => l_error_code
1074 ,o_err_msg => l_error_msg
1075 );
1076
1077 l_error_num := NVL(l_error_num, 0);
1078 l_error_code := NVL(l_error_code, 'No Error');
1079 l_error_msg := NVL(l_error_msg, 'No Error');
1080
1081 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1082 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1083 ,G_MODULE_HEAD || l_routine || '.updcppb3'
1084 ,'After calling update_item_cppb:'|| l_error_num || l_error_code || l_error_msg
1085 );
1086 END IF;
1087
1088 END IF;
1089
1090 IF l_error_num <> 0
1091 THEN
1092 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1093 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION
1094 , G_MODULE_HEAD || l_routine || '.others'
1095 , 'Error for cost group '||p_cost_group_id||' ('||l_error_code||') '||l_error_msg
1096 );
1097 END IF;
1098
1099 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
1100 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
1101 FND_MESSAGE.set_token('MESSAGE', 'Error for cost group '||p_cost_group_id||' ('||l_error_code||') '||l_error_msg);
1102 FND_MSG_PUB.Add;
1103 RAISE FND_API.G_EXC_ERROR;
1104 END IF;
1105
1106 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1107 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1108 ,G_MODULE_HEAD || l_routine || '.end'
1109 ,l_routine || '>'
1110 );
1111 END IF;
1112
1113 EXCEPTION
1114 WHEN FND_API.G_EXC_ERROR THEN
1115 RAISE FND_API.G_EXC_ERROR;
1116 WHEN OTHERS THEN
1117 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
1118 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
1119 FND_MESSAGE.set_token('MESSAGE', '('||SQLCODE||') '||SQLERRM);
1120 FND_MSG_PUB.Add;
1121 RAISE FND_API.G_EXC_ERROR;
1122
1123 END Periodic_Cost_Update;
1124
1125 --========================================================================
1126 -- PROCEDURE : Process Cost Owned Transactions PRIVATE
1127 -- COMMENT : Run the cost processor for all cost owned items
1128 --=========================================================================
1129 PROCEDURE process_cst_own_txns
1130 ( p_period_id IN NUMBER
1131 , p_legal_entity IN NUMBER
1132 , p_cost_type_id IN NUMBER
1133 , p_cost_group_id IN NUMBER
1134 , p_cost_method IN NUMBER
1135 , p_start_date IN DATE
1136 , p_end_date IN DATE
1137 , p_pac_rates_id IN NUMBER
1138 , p_master_org_id IN NUMBER
1139 , p_uom_control IN NUMBER
1140 , p_user_id IN NUMBER
1141 , p_login_id IN NUMBER
1142 , p_req_id IN NUMBER
1143 , p_prg_id IN NUMBER
1144 , p_prg_appid IN NUMBER
1145 )
1146 IS
1147
1148 l_routine CONSTANT VARCHAR2(30) := 'process_cst_own_txns';
1149 --=================
1150 -- CURSORS
1151 --=================
1152
1153 -- ===========================================================
1154 -- Cursor to get Group 1 (cost owned) transactions
1155 -- Normal cost owned txns
1156 -- Interorg transactions across Cost Groups originating from
1157 -- Sales Orders or internal requisitions when the transfer
1158 -- price profile option value is 2 - Yes, Transfer Price as
1159 -- incoming cost;
1160 -- Transaction Source Type Id is 7 for Internal Requisition
1161 -- and 8 for Internal Order
1162 -- OPM Convergence - transaction action id 15 for logical
1163 -- intransit receipt to be processed for the receiving CG
1164 -- Direct interorg receipt due to OPM org to be processed for
1165 -- the receiving org
1166 -- ===========================================================
1167 CURSOR cst_own_txns(c_period_start_date DATE
1168 ,c_period_end_date DATE
1169 ,c_cost_group_id NUMBER
1170 )
1171 IS
1172 SELECT mmt.transaction_id transaction_id
1173 , mmt.transaction_action_id transaction_action_id
1174 , mmt.transaction_source_type_id transaction_source_type_id
1175 , mmt.inventory_item_id inventory_item_id
1176 , mmt.primary_quantity primary_quantity
1177 , mmt.organization_id organization_id
1178 , NVL(mmt.transfer_organization_id,-1) transfer_organization_id
1179 , mmt.subinventory_code subinventory_code
1180 , nvl(mmt.transfer_price,0) transfer_price
1181 FROM mtl_material_transactions mmt
1182 , cst_cost_group_assignments ccga
1186 AND ccga.cost_group_id = c_cost_group_id
1183 WHERE mmt.transaction_date BETWEEN c_period_start_date
1184 AND c_period_end_date
1185 AND ccga.organization_id = mmt.organization_id
1187 AND mmt.organization_id = nvl(mmt.owning_organization_id,mmt.organization_id)
1188 AND nvl(mmt.owning_tp_type,2) = 2
1189 AND mmt.parent_transaction_id is null
1190 AND mmt.transaction_type_id <> 20
1191 AND (transaction_source_type_id = 1
1192 OR transaction_action_id = 29
1193 OR ((transaction_action_id = 1
1194 OR transaction_action_id = 27
1195 OR transaction_action_id = 6)
1196 AND transaction_source_type_id IN (3,6,13)
1197 AND transaction_cost IS NOT NULL)
1198 OR (transaction_action_id = 27 AND transaction_source_type_id = 12))
1199 UNION
1200 SELECT
1201 mmt1.transaction_id transaction_id
1202 , mmt1.transaction_action_id transaction_action_id
1203 , mmt1.transaction_source_type_id transaction_source_type_id
1204 , mmt1.inventory_item_id inventory_item_id
1205 , mmt1.primary_quantity primary_quantity
1206 , mmt1.organization_id organization_id
1207 , nvl(mmt1.transfer_organization_id,-1) transfer_organization_id
1208 , mmt1.subinventory_code subinventory_code
1209 , nvl(mmt1.transfer_price,0) transfer_price
1210 FROM
1211 mtl_material_transactions mmt1
1212 , mtl_parameters mp1
1213 WHERE mmt1.transaction_date BETWEEN c_period_start_date AND c_period_end_date
1214 AND mmt1.organization_id = nvl(mmt1.owning_organization_id, mmt1.organization_id)
1215 AND nvl(mmt1.owning_tp_type,2) = 2
1216 AND mmt1.organization_id = mp1.organization_id
1217 AND nvl(mp1.process_enabled_flag,'N') = 'N'
1218 AND EXISTS (SELECT 'X'
1219 FROM mtl_intercompany_parameters mip
1220 WHERE nvl(fnd_profile.value('INV_INTERCOMPANY_INVOICE_INTERNAL_ORDER'),0) = 1
1221 AND mip.flow_type = 1
1222 AND nvl(fnd_profile.value('CST_TRANSFER_PRICING_OPTION'),0) = 2
1223 AND mip.ship_organization_id = (select to_number(hoi.org_information3)
1224 from hr_organization_information hoi
1225 where hoi.organization_id = decode(mmt1.transaction_action_id,21,
1226 mmt1.organization_id,mmt1.transfer_organization_id)
1227 AND hoi.org_information_context = 'Accounting Information')
1228 AND mip.sell_organization_id = (select to_number(hoi2.org_information3)
1229 from hr_organization_information hoi2
1230 where hoi2.organization_id = decode(mmt1.transaction_action_id,21,
1231 mmt1.transfer_organization_id, mmt1.organization_id)
1232 AND hoi2.org_information_context = 'Accounting Information'))
1233 AND NOT EXISTS ( SELECT 'X'
1234 FROM cst_cost_group_assignments c1, cst_cost_group_assignments c2
1235 WHERE c1.organization_id = mmt1.organization_id
1236 AND c2.organization_id = mmt1.transfer_organization_id
1237 AND c1.cost_group_id = c2.cost_group_id)
1238 AND (
1239 (mmt1.transaction_action_id = 3 AND mmt1.transaction_source_type_id = 8
1240 AND EXISTS ( SELECT 'X'
1241 FROM cst_cost_group_assignments ccga1
1242 WHERE ccga1.cost_group_id = c_cost_group_id
1243 AND ccga1.organization_id = mmt1.organization_id
1244 AND mmt1.primary_quantity > 0))
1245 OR (mmt1.transaction_action_id = 21 AND mmt1.transaction_source_type_id IN (7,8)
1246 AND EXISTS ( SELECT 'X'
1247 FROM mtl_interorg_parameters mip,
1248 cst_cost_group_assignments ccga2
1249 WHERE mip.from_organization_id = mmt1.organization_id
1250 AND mip.to_organization_id = mmt1.transfer_organization_id
1251 AND nvl(mmt1.fob_point,mip.fob_point) = 1
1252 AND ccga2.organization_id = mip.to_organization_id
1253 AND ccga2.cost_group_id = c_cost_group_id))
1254 OR (mmt1.transaction_action_id = 12 AND mmt1.transaction_source_type_id IN (7,8)
1255 AND EXISTS ( SELECT 'X'
1256 FROM mtl_interorg_parameters mip,
1257 cst_cost_group_assignments ccga2
1258 WHERE mip.from_organization_id = mmt1.transfer_organization_id
1259 AND mip.to_organization_id = mmt1.organization_id
1260 AND nvl(mmt1.fob_point,mip.fob_point) = 2
1261 AND ccga2.organization_id = mip.to_organization_id
1262 AND ccga2.cost_group_id = c_cost_group_id)))
1263 UNION
1264 SELECT
1265 mmt2.transaction_id transaction_id
1266 , mmt2.transaction_action_id transaction_action_id
1267 , mmt2.transaction_source_type_id transaction_source_type_id
1268 , mmt2.inventory_item_id inventory_item_id
1269 , mmt2.primary_quantity primary_quantity
1270 , mmt2.organization_id organization_id
1271 , nvl(mmt2.transfer_organization_id,-1) transfer_organization_id
1272 , mmt2.subinventory_code subinventory_code
1273 , nvl(mmt2.transfer_price,0) transfer_price
1274 FROM
1275 mtl_material_transactions mmt2
1276 , mtl_parameters mp2
1277 WHERE mmt2.transaction_date BETWEEN c_period_start_date AND c_period_end_date
1278 AND mmt2.organization_id = nvl(mmt2.owning_organization_id, mmt2.organization_id)
1279 AND nvl(mmt2.owning_tp_type,2) = 2
1280 AND mmt2.organization_id = mp2.organization_id
1281 AND nvl(mp2.process_enabled_flag,'N') = 'N'
1285 WHERE c1.organization_id = mmt2.organization_id
1282 AND mmt2.primary_quantity > 0
1283 AND NOT EXISTS ( SELECT 'X'
1284 FROM cst_cost_group_assignments c1, cst_cost_group_assignments c2
1286 AND c2.organization_id = mmt2.transfer_organization_id
1287 AND c1.cost_group_id = c2.cost_group_id)
1288 AND (
1289 (mmt2.transaction_action_id = 15
1290 AND EXISTS ( SELECT 'X'
1291 FROM cst_cost_group_assignments ccga2
1292 WHERE ccga2.organization_id = mmt2.organization_id
1293 AND ccga2.cost_group_id = c_cost_group_id))
1294 OR (mmt2.transaction_action_id = 3
1295 AND EXISTS ( SELECT 'X'
1296 FROM cst_cost_group_assignments ccga3
1297 ,mtl_parameters mp3
1298 WHERE mp3.organization_id = mmt2.transfer_organization_id
1299 AND nvl(mp3.process_enabled_flag,'N') = 'Y'
1300 AND ccga3.organization_id = mmt2.organization_id
1301 AND ccga3.cost_group_id = c_cost_group_id ))
1302 )
1303 ORDER BY inventory_item_id;
1304
1305 TYPE cst_own_txns_type IS TABLE OF cst_own_txns%rowtype INDEX BY BINARY_INTEGER;
1306
1307 l_cst_own_txns_tab cst_own_txns_type;
1308 l_empty_cst_own_txns_tab cst_own_txns_type;
1309 --=================
1310 -- VARIABLES
1311 --=================
1312
1313 l_error_num NUMBER;
1314 l_error_code VARCHAR2(240);
1315 l_error_msg VARCHAR2(240);
1316 l_exp_flag NUMBER;
1317 l_exp_item NUMBER;
1318 l_process_group NUMBER := 1;
1319
1320 l_txn_category NUMBER;
1321 l_batch_size NUMBER := 200;
1322 l_loop_count NUMBER := 0;
1323
1324 BEGIN
1325
1326 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1327 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1328 ,G_MODULE_HEAD || l_routine || '.begin'
1329 ,l_routine || '<'
1330 );
1331 END IF;
1332
1333 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1334 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1335 ,G_MODULE_HEAD || l_routine || 'Daterange'
1336 ,'Start Date:' || p_start_date || ' End Date:' ||
1337 p_end_date
1338 );
1339 END IF;
1340
1341 -- initialize transaction category for group 1 cost owned transactions
1342 l_txn_category := 3;
1343
1344 IF NOT cst_own_txns%ISOPEN
1345 THEN
1346 OPEN cst_own_txns(p_start_date
1347 ,p_end_date
1348 ,p_cost_group_id
1349 );
1350 END IF;
1351
1352 LOOP
1353 -- clear the pl/sql table before use
1354 l_cst_own_txns_tab := l_empty_cst_own_txns_tab;
1355 FETCH cst_own_txns BULK COLLECT INTO l_cst_own_txns_tab LIMIT l_batch_size;
1356
1357 l_loop_count := l_cst_own_txns_tab.COUNT;
1358
1359 FOR i IN 1..l_loop_count
1360 LOOP
1361
1362 CST_PERIODIC_ABSORPTION_PROC.get_exp_flag(p_item_id => l_cst_own_txns_tab(i).inventory_item_id
1363 ,p_org_id => l_cst_own_txns_tab(i).organization_id
1364 ,p_subinventory_code => l_cst_own_txns_tab(i).subinventory_code
1365 ,x_exp_flag => l_exp_flag
1366 ,x_exp_item => l_exp_item);
1367
1368 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1369 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1370 ,G_MODULE_HEAD || l_routine || '.befcostinvtxn'
1371 ,'Before calling cost_inv_txn: Cost Group Id:' ||
1372 p_cost_group_id || ' Txn Id:' || l_cst_own_txns_tab(i).transaction_id
1373 );
1374 END IF;
1375
1376 -- insert into cppb
1377 l_error_num := 0;
1378
1379 IF (CSTPPINV.l_item_id_tbl.COUNT >= 1000) THEN
1380 CSTPPWAC.insert_into_cppb(i_pac_period_id => p_period_id
1381 ,i_cost_group_id => p_cost_group_id
1382 ,i_txn_category => l_txn_category
1383 ,i_user_id => p_user_id
1384 ,i_login_id => p_login_id
1385 ,i_request_id => p_req_id
1386 ,i_prog_id => p_prg_id
1387 ,i_prog_appl_id => p_prg_appid
1388 ,o_err_num => l_error_num
1389 ,o_err_code => l_error_code
1390 ,o_err_msg => l_error_msg
1391 );
1392 l_error_num := NVL(l_error_num, 0);
1393 l_error_code := NVL(l_error_code, 'No Error');
1394 l_error_msg := NVL(l_error_msg, 'No Error');
1395
1396 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1397 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1398 ,G_MODULE_HEAD || l_routine || '.inscppb'
1399 ,'After calling insert_into_cppb1:'|| l_error_num || l_error_code || l_error_msg
1400 );
1401 END IF;
1402 END IF;
1403
1404 IF l_error_num = 0 THEN
1405
1406 CSTPPINV.cost_inv_txn(i_pac_period_id => p_period_id
1407 ,i_legal_entity => p_legal_entity
1408 ,i_cost_type_id => p_cost_type_id
1412 ,i_txn_action_id => l_cst_own_txns_tab(i).transaction_action_id
1409 ,i_cost_group_id => p_cost_group_id
1410 ,i_cost_method => p_cost_method
1411 ,i_txn_id => l_cst_own_txns_tab(i).transaction_id
1413 ,i_txn_src_type_id => l_cst_own_txns_tab(i).transaction_source_type_id
1414 ,i_item_id => l_cst_own_txns_tab(i).inventory_item_id
1415 ,i_txn_qty => l_cst_own_txns_tab(i).primary_quantity
1416 ,i_txn_org_id => l_cst_own_txns_tab(i).organization_id
1417 ,i_txfr_org_id => l_cst_own_txns_tab(i).transfer_organization_id
1418 ,i_subinventory_code => l_cst_own_txns_tab(i).subinventory_code
1419 ,i_exp_flag => l_exp_flag
1420 ,i_exp_item => l_exp_item
1421 ,i_pac_rates_id => p_pac_rates_id
1422 ,i_process_group => l_process_group
1423 ,i_master_org_id => p_master_org_id
1424 ,i_uom_control => p_uom_control
1425 ,i_user_id => p_user_id
1426 ,i_login_id => p_login_id
1427 ,i_request_id => p_req_id
1428 ,i_prog_id => p_prg_id
1429 ,i_prog_appl_id => p_prg_appid
1430 ,i_txn_category => l_txn_category
1431 ,i_transfer_price_pd => l_cst_own_txns_tab(i).transfer_price
1432 ,o_err_num => l_error_num
1433 ,o_err_code => l_error_code
1434 ,o_err_msg => l_error_msg);
1435
1436 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1437 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1438 ,G_MODULE_HEAD || l_routine || '.befcostinvtxn'
1439 ,'After calling cost_inv_txn:'|| l_error_num || l_error_code || l_error_msg
1440 );
1441 END IF;
1442
1443 END IF; -- error num check
1444
1445 l_error_num := NVL(l_error_num, 0);
1446 l_error_code := NVL(l_error_code, 'No Error');
1447 l_error_msg := NVL(l_error_msg, 'No Error');
1448
1449 IF l_error_num <> 0
1450 THEN
1451 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1452 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION
1453 , G_MODULE_HEAD || l_routine || '.others'
1454 , 'cost_inv_txn for cost group '||p_cost_group_id||' txn id '
1455 ||l_cst_own_txns_tab(i).transaction_id||' ('||l_error_code||') '||l_error_msg
1456 );
1457 END IF;
1458 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
1459 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
1460 FND_MESSAGE.set_token('MESSAGE', 'cost_inv_txn for cost group '||p_cost_group_id||' txn id '
1461 ||l_cst_own_txns_tab(i).transaction_id||' ('||l_error_code||') '||l_error_msg);
1462 FND_MSG_PUB.Add;
1463 RAISE FND_API.G_EXC_ERROR;
1464 END IF;
1465 END LOOP; -- FOR i IN 1..l_loop_count
1466
1467 EXIT WHEN cst_own_txns%NOTFOUND;
1468 END LOOP; -- FETCH loop
1469 CLOSE cst_own_txns;
1470 -- ======================================================
1471 -- insert left over cost owned transactions into cppb
1472 -- ======================================================
1473 l_error_num := 0;
1474
1475 IF (CSTPPINV.l_item_id_tbl.COUNT > 0) THEN
1476 CSTPPWAC.insert_into_cppb(i_pac_period_id => p_period_id
1477 ,i_cost_group_id => p_cost_group_id
1478 ,i_txn_category => l_txn_category
1479 ,i_user_id => p_user_id
1480 ,i_login_id => p_login_id
1481 ,i_request_id => p_req_id
1482 ,i_prog_id => p_prg_id
1483 ,i_prog_appl_id => p_prg_appid
1484 ,o_err_num => l_error_num
1485 ,o_err_code => l_error_code
1486 ,o_err_msg => l_error_msg
1487 );
1488
1489 l_error_num := NVL(l_error_num, 0);
1490 l_error_code := NVL(l_error_code, 'No Error');
1491 l_error_msg := NVL(l_error_msg, 'No Error');
1492
1493 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1494 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1495 ,G_MODULE_HEAD || l_routine || '.inscppb2'
1496 ,'After calling insert_into_cppb:'|| l_error_num || l_error_code || l_error_msg
1497 );
1498 END IF;
1499
1500 END IF;
1501
1502 IF l_error_num = 0 THEN
1503 CSTPPWAC.update_cppb(i_pac_period_id => p_period_id
1504 ,i_cost_group_id => p_cost_group_id
1505 ,i_txn_category => l_txn_category
1506 ,i_low_level_code => -2
1507 ,i_user_id => p_user_id
1508 ,i_login_id => p_login_id
1509 ,i_request_id => p_req_id
1510 ,i_prog_id => p_prg_id
1511 ,i_prog_appl_id => p_prg_appid
1515 );
1512 ,o_err_num => l_error_num
1513 ,o_err_code => l_error_code
1514 ,o_err_msg => l_error_msg
1516
1517 l_error_num := NVL(l_error_num, 0);
1518 l_error_code := NVL(l_error_code, 'No Error');
1519 l_error_msg := NVL(l_error_msg, 'No Error');
1520
1521 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1522 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1523 ,G_MODULE_HEAD || l_routine || '.updcppb1'
1524 ,'After calling update_cppb:'|| l_error_num || l_error_code || l_error_msg
1525 );
1526 END IF;
1527
1528 END IF;
1529
1530 IF l_error_num <> 0
1531 THEN
1532 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
1533 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
1534 FND_MESSAGE.set_token('MESSAGE', 'Error in insert/update cppb for '||p_cost_group_id||' ('||l_error_code||') '||l_error_msg);
1535 FND_MSG_PUB.Add;
1536 RAISE FND_API.G_EXC_ERROR;
1537 END IF;
1538
1539
1540 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1541 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1542 ,G_MODULE_HEAD || l_routine || '.end'
1543 ,l_routine || '>'
1544 );
1545 END IF;
1546
1547 EXCEPTION
1548 WHEN FND_API.G_EXC_ERROR THEN
1549 RAISE FND_API.G_EXC_ERROR;
1550 WHEN OTHERS THEN
1551 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
1552 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
1553 FND_MESSAGE.set_token('MESSAGE', '('||SQLCODE||') '||SQLERRM);
1554 FND_MSG_PUB.Add;
1555 RAISE FND_API.G_EXC_ERROR;
1556 END process_cst_own_txns;
1557
1558 --========================================================================
1559 -- PROCEDURE : Process cost owned txns PRIVATE
1560 -- COMMENT :
1561 --=========================================================================
1562
1563 PROCEDURE Process_group1_txns_partial
1564 (p_period_id IN NUMBER
1565 ,p_legal_entity IN NUMBER
1566 ,p_master_org_id IN NUMBER
1567 ,p_start_date IN DATE
1568 ,p_end_date IN DATE
1569 ,p_cost_group_id IN NUMBER
1570 ,p_cost_method IN NUMBER
1571 ,p_cost_type_id IN NUMBER
1572 ,p_pac_rates_id IN NUMBER
1573 ,p_uom_control IN NUMBER
1574 ,p_user_id IN NUMBER
1575 ,p_login_id IN NUMBER
1576 ,p_req_id IN NUMBER
1577 ,p_prg_id IN NUMBER
1578 ,p_prg_appid IN NUMBER
1579 )
1580 IS
1581 l_cost_update_type NUMBER;
1582 BEGIN
1583 l_cost_update_type := 1;
1584
1585 -- Periodic Cost Update New cost, %ge change for all items
1586 periodic_cost_update
1587 (p_period_id => p_period_id
1588 ,p_legal_entity => p_legal_entity
1589 ,p_cost_type_id => p_cost_type_id
1590 ,p_cost_group_id => p_cost_group_id
1591 ,p_cost_method => p_cost_method
1592 ,p_start_date => p_start_date
1593 ,p_end_date => p_end_date
1594 ,p_pac_rates_id => p_pac_rates_id
1595 ,p_master_org_id => p_master_org_id
1596 ,p_cost_update_type => l_cost_update_type
1597 ,p_uom_control => p_uom_control
1598 ,p_user_id => p_user_id
1599 ,p_login_id => p_login_id
1600 ,p_req_id => p_req_id
1601 ,p_prg_id => p_prg_id
1602 ,p_prg_appid => p_prg_appid);
1603
1604 -- Process Group 1 for All items
1605 process_cst_own_txns
1606 (p_period_id => p_period_id
1607 ,p_legal_entity => p_legal_entity
1608 ,p_cost_type_id => p_cost_type_id
1609 ,p_cost_group_id => p_cost_group_id
1610 ,p_cost_method => p_cost_method
1611 ,p_start_date => p_start_date
1612 ,p_end_date => p_end_date
1613 ,p_pac_rates_id => p_pac_rates_id
1614 ,p_master_org_id => p_master_org_id
1615 ,p_uom_control => p_uom_control
1616 ,p_user_id => p_user_id
1617 ,p_login_id => p_login_id
1618 ,p_req_id => p_req_id
1619 ,p_prg_id => p_prg_id
1620 ,p_prg_appid => p_prg_appid);
1621
1622 END Process_group1_txns_partial;
1623
1624 --========================================================================
1625 -- PROCEDURE : Begin_Cost_Processor_Worker PUBLIC
1626 -- COMMENT : This procedure will process phases 1-4 for all transactions
1627 --=========================================================================
1628 PROCEDURE begin_cp_worker
1629 ( x_errbuf OUT NOCOPY VARCHAR2
1630 , x_retcode OUT NOCOPY VARCHAR2
1631 , p_legal_entity IN NUMBER
1632 , p_cost_type_id IN NUMBER
1633 , p_master_org_id IN NUMBER
1634 , p_cost_method IN NUMBER
1638 , p_starting_phase IN NUMBER
1635 , p_cost_group_id IN NUMBER
1636 , p_period_id IN NUMBER
1637 , p_prev_period_id IN NUMBER
1639 , p_pac_rates_id IN NUMBER
1640 , p_uom_control IN NUMBER
1641 , p_start_date IN DATE
1642 , p_end_date IN DATE
1643 )
1644 IS
1645
1646 l_routine CONSTANT VARCHAR2(30) := 'begin_cp_worker';
1647
1648 --=================
1649 -- VARIABLES
1650 --=================
1651
1652 l_current_index BINARY_INTEGER;
1653 l_prg_appid NUMBER;
1654 l_prg_id NUMBER;
1655 l_req_id NUMBER;
1656 l_user_id NUMBER;
1657 l_login_id NUMBER;
1658
1659 BEGIN
1660
1661 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1662 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1663 ,G_MODULE_HEAD || l_routine || '.begin'
1664 ,l_routine || '<'
1665 );
1666 END IF;
1667 -- Getting Program Information
1668 l_prg_appid := FND_GLOBAL.prog_appl_id;
1669 l_prg_id := FND_GLOBAL.conc_program_id;
1670 l_req_id := FND_GLOBAL.conc_request_id;
1671 l_user_id := FND_GLOBAL.user_id;
1672 l_login_id := FND_GLOBAL.login_id;
1673
1674 IF p_starting_phase = 1 THEN
1675 -- Run the Acquisition Cost Processor if the run option
1676 -- is start or Resume from error and acquisition process not complete
1677
1678 run_acquisition_cp
1679 (p_period_id => p_period_id
1680 ,p_start_date => p_start_date
1681 ,p_end_date => p_end_date
1682 ,p_legal_entity => p_legal_entity
1683 ,p_cost_type_id => p_cost_type_id
1684 ,p_cost_group_id => p_cost_group_id
1685 ,p_user_id => l_user_id
1686 ,p_login_id => l_login_id
1687 ,p_req_id => l_req_id
1688 ,p_prg_id => l_prg_id
1689 ,p_prg_appid => l_prg_appid);
1690
1691 END IF;
1692
1693 IF p_starting_phase < 3 THEN
1694
1695 -- Copy the balance from the previous period if the run option
1696 -- is start or a previous run did not complete this phase
1697
1698 copy_balance
1699 (p_period_id => p_period_id
1700 ,p_prev_period_id => p_prev_period_id
1701 ,p_end_date => p_end_date
1702 ,p_legal_entity => p_legal_entity
1703 ,p_cost_type_id => p_cost_type_id
1704 ,p_cost_group_id => p_cost_group_id
1705 ,p_cost_method => p_cost_method
1706 ,p_user_id => l_user_id
1707 ,p_login_id => l_login_id
1708 ,p_req_id => l_req_id
1709 ,p_prg_id => l_prg_id
1710 ,p_prg_appid => l_prg_appid
1711 ,p_starting_phase => p_starting_phase);
1712
1713 END IF;
1714
1715 IF p_starting_phase < 4 THEN
1716
1717 -- Explode the bill of materials if the run option
1718 -- is start or a previous run did not complete this phase
1719
1720 explode_bom
1721 (p_period_id => p_period_id
1722 ,p_cost_group_id => p_cost_group_id
1723 ,p_start_date => p_start_date
1724 ,p_end_date => p_end_date
1725 ,p_user_id => l_user_id
1726 ,p_login_id => l_login_id
1727 ,p_req_id => l_req_id
1728 ,p_prg_id => l_prg_id
1729 ,p_prg_appid => l_prg_appid
1730 );
1731
1732 END IF;
1733
1734 IF p_starting_phase < 5 THEN
1735
1736 build_job_info
1737 (p_period_id => p_period_id
1738 ,p_start_date => p_start_date
1739 ,p_end_date => p_end_date
1740 ,p_cost_group_id => p_cost_group_id
1741 ,p_cost_type_id => p_cost_type_id
1742 ,p_pac_rates_id => p_pac_rates_id
1743 ,p_user_id => l_user_id
1744 ,p_login_id => l_login_id
1745 ,p_req_id => l_req_id
1746 ,p_prg_id => l_prg_id
1747 ,p_prg_appid => l_prg_appid);
1748
1749 END IF;
1750
1751 --process Periodic Cost update and Cost Owned transactions
1752
1753 Process_group1_txns_partial
1754 (p_period_id => p_period_id
1755 ,p_legal_entity => p_legal_entity
1756 ,p_master_org_id => p_master_org_id
1757 ,p_start_date => p_start_date
1758 ,p_end_date => p_end_date
1759 ,p_cost_group_id => p_cost_group_id
1760 ,p_cost_method => p_cost_method
1761 ,p_cost_type_id => p_cost_type_id
1762 ,p_pac_rates_id => p_pac_rates_id
1763 ,p_uom_control => p_uom_control
1764 ,p_user_id => l_user_id
1765 ,p_login_id => l_login_id
1766 ,p_req_id => l_req_id
1767 ,p_prg_id => l_prg_id
1768 ,p_prg_appid => l_prg_appid);
1769
1770 -- call retrieve interorg items and at the end set the status of phase 7 for the CG to Running.
1771 prepare_absorption_process
1772 (p_period_id => p_period_id
1773 ,p_start_date => p_start_date
1774 ,p_end_date => p_end_date
1775 ,p_cost_group_id => p_cost_group_id
1776 ,p_cost_type_id => p_cost_type_id
1777 ,p_pac_rates_id => p_pac_rates_id
1778 ,p_user_id => l_user_id
1779 ,p_login_id => l_login_id
1780 ,p_req_id => l_req_id
1781 ,p_prg_id => l_prg_id
1782 ,p_prg_appid => l_prg_appid);
1783
1784 -- set phase 8 status to 4 success
1785 set_status
1786 ( p_period_id => p_period_id
1787 , p_cost_group_id => p_cost_group_id
1788 , p_phase => 8
1789 , p_status => 4
1790 , p_end_date => p_end_date
1791 , p_user_id => l_user_id
1792 , p_login_id => l_login_id
1793 , p_req_id => l_req_id
1794 , p_prg_id => l_prg_id
1795 , p_prg_appid => l_prg_appid);
1796
1797 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1798 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1799 ,G_MODULE_HEAD || l_routine || '.end'
1800 ,l_routine || '>'
1801 );
1802 END IF;
1803
1804 EXCEPTION
1805
1806 WHEN FND_API.G_EXC_ERROR THEN
1807 ROLLBACK;
1808 set_status
1809 ( p_period_id => p_period_id
1810 , p_cost_group_id => p_cost_group_id
1811 , p_phase => 8
1812 , p_status => 3
1813 , p_end_date => p_end_date
1814 , p_user_id => l_user_id
1815 , p_login_id => l_login_id
1816 , p_req_id => l_req_id
1817 , p_prg_id => l_prg_id
1818 , p_prg_appid => l_prg_appid);
1819
1820 set_status
1821 ( p_period_id => p_period_id
1822 , p_cost_group_id => p_cost_group_id
1823 , p_phase => 7
1824 , p_status => 3
1825 , p_end_date => p_end_date
1826 , p_user_id => l_user_id
1827 , p_login_id => l_login_id
1828 , p_req_id => l_req_id
1829 , p_prg_id => l_prg_id
1830 , p_prg_appid => l_prg_appid);
1831
1832 x_retcode := '2';
1833 x_errbuf := substrb(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE)
1834 ,1
1835 ,250);
1836
1837 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1838 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION
1839 , G_MODULE_HEAD || l_routine , x_errbuf
1840 );
1841 END IF;
1842
1843 WHEN OTHERS THEN
1844 ROLLBACK;
1845 set_status
1846 ( p_period_id => p_period_id
1850 , p_end_date => p_end_date
1847 , p_cost_group_id => p_cost_group_id
1848 , p_phase => 8
1849 , p_status => 3
1851 , p_user_id => l_user_id
1852 , p_login_id => l_login_id
1853 , p_req_id => l_req_id
1854 , p_prg_id => l_prg_id
1855 , p_prg_appid => l_prg_appid);
1856
1857 set_status
1858 ( p_period_id => p_period_id
1859 , p_cost_group_id => p_cost_group_id
1860 , p_phase => 7
1861 , p_status => 3
1862 , p_end_date => p_end_date
1863 , p_user_id => l_user_id
1864 , p_login_id => l_login_id
1865 , p_req_id => l_req_id
1866 , p_prg_id => l_prg_id
1867 , p_prg_appid => l_prg_appid);
1868
1869 x_errbuf := SQLCODE || substr(SQLERRM, 1, 200);
1870 x_retcode := '2';
1871
1872 FND_FILE.put_line
1873 ( FND_FILE.log
1874 , 'Error in begin_cp_worker '|| x_errbuf
1875 );
1876
1877 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1878 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION
1879 , G_MODULE_HEAD || l_routine ||'.others_exc'
1880 , 'others:' || x_errbuf
1881 );
1882 END IF;
1883
1884 END begin_cp_worker;
1885
1886 END CST_PERIODIC_AVERAGE_PROC_CP;