[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.15.12020000.3 2012/10/16 01:41:12 fayang 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
413 IF l_error_num <> 0
414 THEN
415 set_status
416 (p_period_id => p_period_id
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 -- ==============================================================
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.
556 -- ==============================================================
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 + (86399/86400)
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
690 DELETE CST_PAC_INTORG_ITMS_TEMP
691 WHERE pac_period_id = p_period_id
692 AND cost_group_id = p_cost_group_id;
693
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
816 l_upd_val_tab upd_val_tab;
817 l_empty_upd_val_tab upd_val_tab;
818 --=================
819 -- VARIABLES
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
957 ,i_request_id => p_req_id
958 ,i_prog_id => p_prg_id
959 ,i_prog_appl_id => p_prg_appid
960 ,o_err_num => l_error_num
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
1065 ,i_txn_category => l_txn_category
1066 ,i_low_level_code => -2
1067 ,i_user_id => p_user_id
1068 ,i_login_id => p_login_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
1183 WHERE mmt.transaction_date BETWEEN c_period_start_date
1184 AND c_period_end_date
1185 AND ccga.organization_id = mmt.organization_id
1186 AND ccga.cost_group_id = c_cost_group_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 mmt1.transaction_id transaction_id
1266 , mmt1.transaction_action_id transaction_action_id
1267 , mmt1.transaction_source_type_id transaction_source_type_id
1268 , mmt1.inventory_item_id inventory_item_id
1269 , mmt1.primary_quantity primary_quantity
1270 , mmt1.organization_id organization_id
1271 , nvl(mmt1.transfer_organization_id,-1) transfer_organization_id
1272 , mmt1.subinventory_code subinventory_code
1273 , nvl(mmt1.transfer_price,0) transfer_price
1274 FROM
1275 mtl_material_transactions mmt1
1276 , mtl_parameters mp1
1277 WHERE mmt1.transaction_date BETWEEN c_period_start_date AND c_period_end_date
1278 AND mmt1.organization_id = nvl(mmt1.owning_organization_id, mmt1.organization_id)
1279 AND nvl(mmt1.owning_tp_type,2) = 2
1280 AND mmt1.organization_id = mp1.organization_id
1281 AND nvl(mp1.process_enabled_flag,'N') = 'N'
1282 AND NOT EXISTS ( SELECT 'X'
1283 FROM cst_cost_group_assignments c1, cst_cost_group_assignments c2, cst_cost_groups ccg1, cst_cost_groups ccg2
1284 WHERE c1.organization_id = mmt1.organization_id
1285 AND c2.organization_id = mmt1.transfer_organization_id
1286 AND c1.cost_group_id = ccg1.cost_group_id
1287 AND c2.cost_group_id = ccg2.cost_group_id
1288 AND ccg1.legal_entity = ccg2.legal_entity)
1289 AND (
1290 (mmt1.transaction_action_id = 3
1291 AND EXISTS ( SELECT 'X'
1292 FROM cst_cost_group_assignments ccga1
1293 WHERE ccga1.cost_group_id = c_cost_group_id
1294 AND ccga1.organization_id = mmt1.organization_id
1295 AND mmt1.primary_quantity > 0))
1296 OR (mmt1.transaction_action_id = 21
1297 AND EXISTS ( SELECT 'X'
1298 FROM mtl_interorg_parameters mip,
1299 cst_cost_group_assignments ccga2
1300 WHERE mip.from_organization_id = mmt1.organization_id
1301 AND mip.to_organization_id = mmt1.transfer_organization_id
1302 AND nvl(mmt1.fob_point,mip.fob_point) = 1
1303 AND ccga2.organization_id = mip.to_organization_id
1304 AND ccga2.cost_group_id = c_cost_group_id))
1305 OR (mmt1.transaction_action_id = 12
1306 AND EXISTS ( SELECT 'X'
1307 FROM mtl_interorg_parameters mip,
1308 cst_cost_group_assignments ccga2
1309 WHERE mip.from_organization_id = mmt1.transfer_organization_id
1310 AND mip.to_organization_id = mmt1.organization_id
1311 AND nvl(mmt1.fob_point,mip.fob_point) = 2
1312 AND ccga2.organization_id = mip.to_organization_id
1313 AND ccga2.cost_group_id = c_cost_group_id)))
1314 UNION
1315 SELECT
1316 mmt2.transaction_id transaction_id
1317 , mmt2.transaction_action_id transaction_action_id
1318 , mmt2.transaction_source_type_id transaction_source_type_id
1319 , mmt2.inventory_item_id inventory_item_id
1320 , mmt2.primary_quantity primary_quantity
1321 , mmt2.organization_id organization_id
1322 , nvl(mmt2.transfer_organization_id,-1) transfer_organization_id
1323 , mmt2.subinventory_code subinventory_code
1324 , nvl(mmt2.transfer_price,0) transfer_price
1325 FROM
1326 mtl_material_transactions mmt2
1327 , mtl_parameters mp2
1328 WHERE mmt2.transaction_date BETWEEN c_period_start_date AND c_period_end_date
1329 AND mmt2.organization_id = nvl(mmt2.owning_organization_id, mmt2.organization_id)
1330 AND nvl(mmt2.owning_tp_type,2) = 2
1331 AND mmt2.organization_id = mp2.organization_id
1332 AND nvl(mp2.process_enabled_flag,'N') = 'N'
1333 AND mmt2.primary_quantity > 0
1334 AND NOT EXISTS ( SELECT 'X'
1335 FROM cst_cost_group_assignments c1, cst_cost_group_assignments c2
1336 WHERE c1.organization_id = mmt2.organization_id
1337 AND c2.organization_id = mmt2.transfer_organization_id
1338 AND c1.cost_group_id = c2.cost_group_id)
1339 AND (
1340 (mmt2.transaction_action_id = 15
1341 AND EXISTS ( SELECT 'X'
1342 FROM cst_cost_group_assignments ccga2
1343 WHERE ccga2.organization_id = mmt2.organization_id
1344 AND ccga2.cost_group_id = c_cost_group_id))
1345 OR (mmt2.transaction_action_id = 3
1346 AND EXISTS ( SELECT 'X'
1347 FROM cst_cost_group_assignments ccga3
1348 ,mtl_parameters mp3
1349 WHERE mp3.organization_id = mmt2.transfer_organization_id
1350 AND nvl(mp3.process_enabled_flag,'N') = 'Y'
1351 AND ccga3.organization_id = mmt2.organization_id
1352 AND ccga3.cost_group_id = c_cost_group_id ))
1353 )
1354 ORDER BY inventory_item_id;
1355
1356 TYPE cst_own_txns_type IS TABLE OF cst_own_txns%rowtype INDEX BY BINARY_INTEGER;
1357
1358 l_cst_own_txns_tab cst_own_txns_type;
1359 l_empty_cst_own_txns_tab cst_own_txns_type;
1360 --=================
1361 -- VARIABLES
1362 --=================
1363
1364 l_error_num NUMBER;
1365 l_error_code VARCHAR2(240);
1366 l_error_msg VARCHAR2(240);
1367 l_exp_flag NUMBER;
1368 l_exp_item NUMBER;
1369 l_process_group NUMBER := 1;
1370
1371 l_txn_category NUMBER;
1372 l_batch_size NUMBER := 200;
1373 l_loop_count NUMBER := 0;
1374
1375 BEGIN
1376
1377 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1378 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1379 ,G_MODULE_HEAD || l_routine || '.begin'
1380 ,l_routine || '<'
1381 );
1382 END IF;
1383
1384 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1385 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1386 ,G_MODULE_HEAD || l_routine || 'Daterange'
1387 ,'Start Date:' || p_start_date || ' End Date:' ||
1388 p_end_date
1389 );
1390 END IF;
1391
1392 -- initialize transaction category for group 1 cost owned transactions
1393 l_txn_category := 3;
1394
1395 IF NOT cst_own_txns%ISOPEN
1396 THEN
1397 OPEN cst_own_txns(p_start_date
1398 ,p_end_date
1399 ,p_cost_group_id
1400 );
1401 END IF;
1402
1403 LOOP
1404 -- clear the pl/sql table before use
1405 l_cst_own_txns_tab := l_empty_cst_own_txns_tab;
1406 FETCH cst_own_txns BULK COLLECT INTO l_cst_own_txns_tab LIMIT l_batch_size;
1407
1408 l_loop_count := l_cst_own_txns_tab.COUNT;
1409
1410 FOR i IN 1..l_loop_count
1411 LOOP
1412
1413 CST_PERIODIC_ABSORPTION_PROC.get_exp_flag(p_item_id => l_cst_own_txns_tab(i).inventory_item_id
1414 ,p_org_id => l_cst_own_txns_tab(i).organization_id
1415 ,p_subinventory_code => l_cst_own_txns_tab(i).subinventory_code
1416 ,x_exp_flag => l_exp_flag
1417 ,x_exp_item => l_exp_item);
1418
1419 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1420 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1421 ,G_MODULE_HEAD || l_routine || '.befcostinvtxn'
1422 ,'Before calling cost_inv_txn: Cost Group Id:' ||
1423 p_cost_group_id || ' Txn Id:' || l_cst_own_txns_tab(i).transaction_id
1424 );
1425 END IF;
1426
1427 -- insert into cppb
1428 l_error_num := 0;
1429
1430 IF (CSTPPINV.l_item_id_tbl.COUNT >= 1000) THEN
1431 CSTPPWAC.insert_into_cppb(i_pac_period_id => p_period_id
1432 ,i_cost_group_id => p_cost_group_id
1433 ,i_txn_category => l_txn_category
1434 ,i_user_id => p_user_id
1435 ,i_login_id => p_login_id
1436 ,i_request_id => p_req_id
1437 ,i_prog_id => p_prg_id
1438 ,i_prog_appl_id => p_prg_appid
1439 ,o_err_num => l_error_num
1440 ,o_err_code => l_error_code
1441 ,o_err_msg => l_error_msg
1442 );
1443 l_error_num := NVL(l_error_num, 0);
1444 l_error_code := NVL(l_error_code, 'No Error');
1445 l_error_msg := NVL(l_error_msg, 'No Error');
1446
1447 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1448 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1449 ,G_MODULE_HEAD || l_routine || '.inscppb'
1450 ,'After calling insert_into_cppb1:'|| l_error_num || l_error_code || l_error_msg
1451 );
1452 END IF;
1453 END IF;
1454
1455 IF l_error_num = 0 THEN
1456
1457 CSTPPINV.cost_inv_txn(i_pac_period_id => p_period_id
1458 ,i_legal_entity => p_legal_entity
1459 ,i_cost_type_id => p_cost_type_id
1460 ,i_cost_group_id => p_cost_group_id
1461 ,i_cost_method => p_cost_method
1462 ,i_txn_id => l_cst_own_txns_tab(i).transaction_id
1463 ,i_txn_action_id => l_cst_own_txns_tab(i).transaction_action_id
1464 ,i_txn_src_type_id => l_cst_own_txns_tab(i).transaction_source_type_id
1465 ,i_item_id => l_cst_own_txns_tab(i).inventory_item_id
1466 ,i_txn_qty => l_cst_own_txns_tab(i).primary_quantity
1467 ,i_txn_org_id => l_cst_own_txns_tab(i).organization_id
1468 ,i_txfr_org_id => l_cst_own_txns_tab(i).transfer_organization_id
1469 ,i_subinventory_code => l_cst_own_txns_tab(i).subinventory_code
1470 ,i_exp_flag => l_exp_flag
1471 ,i_exp_item => l_exp_item
1472 ,i_pac_rates_id => p_pac_rates_id
1473 ,i_process_group => l_process_group
1474 ,i_master_org_id => p_master_org_id
1475 ,i_uom_control => p_uom_control
1476 ,i_user_id => p_user_id
1477 ,i_login_id => p_login_id
1478 ,i_request_id => p_req_id
1479 ,i_prog_id => p_prg_id
1480 ,i_prog_appl_id => p_prg_appid
1481 ,i_txn_category => l_txn_category
1482 ,i_transfer_price_pd => l_cst_own_txns_tab(i).transfer_price
1483 ,o_err_num => l_error_num
1484 ,o_err_code => l_error_code
1485 ,o_err_msg => l_error_msg);
1486
1487 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1488 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1489 ,G_MODULE_HEAD || l_routine || '.befcostinvtxn'
1490 ,'After calling cost_inv_txn:'|| l_error_num || l_error_code || l_error_msg
1491 );
1492 END IF;
1493
1494 END IF; -- error num check
1495
1496 l_error_num := NVL(l_error_num, 0);
1497 l_error_code := NVL(l_error_code, 'No Error');
1498 l_error_msg := NVL(l_error_msg, 'No Error');
1499
1500 IF l_error_num <> 0
1501 THEN
1502 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1503 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION
1504 , G_MODULE_HEAD || l_routine || '.others'
1505 , 'cost_inv_txn for cost group '||p_cost_group_id||' txn id '
1506 ||l_cst_own_txns_tab(i).transaction_id||' ('||l_error_code||') '||l_error_msg
1507 );
1508 END IF;
1509 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
1510 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
1511 FND_MESSAGE.set_token('MESSAGE', 'cost_inv_txn for cost group '||p_cost_group_id||' txn id '
1512 ||l_cst_own_txns_tab(i).transaction_id||' ('||l_error_code||') '||l_error_msg);
1513 FND_MSG_PUB.Add;
1514 RAISE FND_API.G_EXC_ERROR;
1515 END IF;
1516 END LOOP; -- FOR i IN 1..l_loop_count
1517
1518 EXIT WHEN cst_own_txns%NOTFOUND;
1519 END LOOP; -- FETCH loop
1520 CLOSE cst_own_txns;
1521 -- ======================================================
1522 -- insert left over cost owned transactions into cppb
1523 -- ======================================================
1524 l_error_num := 0;
1525
1526 IF (CSTPPINV.l_item_id_tbl.COUNT > 0) THEN
1527 CSTPPWAC.insert_into_cppb(i_pac_period_id => p_period_id
1528 ,i_cost_group_id => p_cost_group_id
1529 ,i_txn_category => l_txn_category
1530 ,i_user_id => p_user_id
1531 ,i_login_id => p_login_id
1532 ,i_request_id => p_req_id
1533 ,i_prog_id => p_prg_id
1534 ,i_prog_appl_id => p_prg_appid
1535 ,o_err_num => l_error_num
1536 ,o_err_code => l_error_code
1537 ,o_err_msg => l_error_msg
1538 );
1539
1540 l_error_num := NVL(l_error_num, 0);
1541 l_error_code := NVL(l_error_code, 'No Error');
1542 l_error_msg := NVL(l_error_msg, 'No Error');
1543
1544 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1545 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1546 ,G_MODULE_HEAD || l_routine || '.inscppb2'
1547 ,'After calling insert_into_cppb:'|| l_error_num || l_error_code || l_error_msg
1548 );
1549 END IF;
1550
1551 END IF;
1552
1553 IF l_error_num = 0 THEN
1554 CSTPPWAC.update_cppb(i_pac_period_id => p_period_id
1555 ,i_cost_group_id => p_cost_group_id
1556 ,i_txn_category => l_txn_category
1557 ,i_low_level_code => -2
1558 ,i_user_id => p_user_id
1559 ,i_login_id => p_login_id
1560 ,i_request_id => p_req_id
1561 ,i_prog_id => p_prg_id
1562 ,i_prog_appl_id => p_prg_appid
1563 ,o_err_num => l_error_num
1564 ,o_err_code => l_error_code
1565 ,o_err_msg => l_error_msg
1566 );
1567
1568 l_error_num := NVL(l_error_num, 0);
1569 l_error_code := NVL(l_error_code, 'No Error');
1570 l_error_msg := NVL(l_error_msg, 'No Error');
1571
1572 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1573 FND_LOG.string(FND_LOG.LEVEL_STATEMENT
1574 ,G_MODULE_HEAD || l_routine || '.updcppb1'
1575 ,'After calling update_cppb:'|| l_error_num || l_error_code || l_error_msg
1576 );
1577 END IF;
1578
1579 END IF;
1580
1581 IF l_error_num <> 0
1582 THEN
1583 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
1584 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
1585 FND_MESSAGE.set_token('MESSAGE', 'Error in insert/update cppb for '||p_cost_group_id||' ('||l_error_code||') '||l_error_msg);
1586 FND_MSG_PUB.Add;
1587 RAISE FND_API.G_EXC_ERROR;
1588 END IF;
1589
1590
1591 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1592 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1593 ,G_MODULE_HEAD || l_routine || '.end'
1594 ,l_routine || '>'
1595 );
1596 END IF;
1597
1598 EXCEPTION
1599 WHEN FND_API.G_EXC_ERROR THEN
1600 RAISE FND_API.G_EXC_ERROR;
1601 WHEN OTHERS THEN
1602 FND_MESSAGE.Set_Name('BOM', 'CST_INTERORG_PAC_ERROR');
1603 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
1604 FND_MESSAGE.set_token('MESSAGE', '('||SQLCODE||') '||SQLERRM);
1605 FND_MSG_PUB.Add;
1606 RAISE FND_API.G_EXC_ERROR;
1607 END process_cst_own_txns;
1608
1609 --========================================================================
1610 -- PROCEDURE : Process cost owned txns PRIVATE
1611 -- COMMENT :
1612 --=========================================================================
1613
1614 PROCEDURE Process_group1_txns_partial
1615 (p_period_id IN NUMBER
1616 ,p_legal_entity IN NUMBER
1617 ,p_master_org_id IN NUMBER
1618 ,p_start_date IN DATE
1619 ,p_end_date IN DATE
1620 ,p_cost_group_id IN NUMBER
1621 ,p_cost_method IN NUMBER
1622 ,p_cost_type_id IN NUMBER
1623 ,p_pac_rates_id IN NUMBER
1624 ,p_uom_control IN NUMBER
1625 ,p_user_id IN NUMBER
1626 ,p_login_id IN NUMBER
1627 ,p_req_id IN NUMBER
1628 ,p_prg_id IN NUMBER
1629 ,p_prg_appid IN NUMBER
1630 )
1631 IS
1632 l_cost_update_type NUMBER;
1633 BEGIN
1634 l_cost_update_type := 1;
1635
1636 -- Periodic Cost Update New cost, %ge change for all items
1637 periodic_cost_update
1638 (p_period_id => p_period_id
1639 ,p_legal_entity => p_legal_entity
1640 ,p_cost_type_id => p_cost_type_id
1641 ,p_cost_group_id => p_cost_group_id
1642 ,p_cost_method => p_cost_method
1643 ,p_start_date => p_start_date
1644 ,p_end_date => p_end_date
1645 ,p_pac_rates_id => p_pac_rates_id
1646 ,p_master_org_id => p_master_org_id
1647 ,p_cost_update_type => l_cost_update_type
1648 ,p_uom_control => p_uom_control
1649 ,p_user_id => p_user_id
1650 ,p_login_id => p_login_id
1651 ,p_req_id => p_req_id
1652 ,p_prg_id => p_prg_id
1653 ,p_prg_appid => p_prg_appid);
1654
1655 -- Process Group 1 for All items
1656 process_cst_own_txns
1657 (p_period_id => p_period_id
1658 ,p_legal_entity => p_legal_entity
1659 ,p_cost_type_id => p_cost_type_id
1660 ,p_cost_group_id => p_cost_group_id
1661 ,p_cost_method => p_cost_method
1662 ,p_start_date => p_start_date
1663 ,p_end_date => p_end_date
1664 ,p_pac_rates_id => p_pac_rates_id
1665 ,p_master_org_id => p_master_org_id
1666 ,p_uom_control => p_uom_control
1667 ,p_user_id => p_user_id
1668 ,p_login_id => p_login_id
1669 ,p_req_id => p_req_id
1670 ,p_prg_id => p_prg_id
1671 ,p_prg_appid => p_prg_appid);
1672
1673 END Process_group1_txns_partial;
1674
1675 --========================================================================
1676 -- PROCEDURE : Begin_Cost_Processor_Worker PUBLIC
1677 -- COMMENT : This procedure will process phases 1-4 for all transactions
1678 --=========================================================================
1679 PROCEDURE begin_cp_worker
1680 ( x_errbuf OUT NOCOPY VARCHAR2
1681 , x_retcode OUT NOCOPY VARCHAR2
1682 , p_legal_entity IN NUMBER
1683 , p_cost_type_id IN NUMBER
1684 , p_master_org_id IN NUMBER
1685 , p_cost_method IN NUMBER
1686 , p_cost_group_id IN NUMBER
1687 , p_period_id IN NUMBER
1688 , p_prev_period_id IN NUMBER
1689 , p_starting_phase IN NUMBER
1690 , p_pac_rates_id IN NUMBER
1691 , p_uom_control IN NUMBER
1692 , p_start_date IN DATE
1693 , p_end_date IN DATE
1694 )
1695 IS
1696
1697 l_routine CONSTANT VARCHAR2(30) := 'begin_cp_worker';
1698
1699 --=================
1700 -- VARIABLES
1701 --=================
1702
1703 l_current_index BINARY_INTEGER;
1704 l_prg_appid NUMBER;
1705 l_prg_id NUMBER;
1706 l_req_id NUMBER;
1707 l_user_id NUMBER;
1708 l_login_id NUMBER;
1709
1710 BEGIN
1711
1712 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1713 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1714 ,G_MODULE_HEAD || l_routine || '.begin'
1715 ,l_routine || '<'
1716 );
1717 END IF;
1718 -- Getting Program Information
1719 l_prg_appid := FND_GLOBAL.prog_appl_id;
1720 l_prg_id := FND_GLOBAL.conc_program_id;
1721 l_req_id := FND_GLOBAL.conc_request_id;
1722 l_user_id := FND_GLOBAL.user_id;
1723 l_login_id := FND_GLOBAL.login_id;
1724
1725 IF p_starting_phase = 1 THEN
1726 -- Run the Acquisition Cost Processor if the run option
1727 -- is start or Resume from error and acquisition process not complete
1728
1729 run_acquisition_cp
1730 (p_period_id => p_period_id
1731 ,p_start_date => p_start_date
1732 ,p_end_date => p_end_date
1733 ,p_legal_entity => p_legal_entity
1734 ,p_cost_type_id => p_cost_type_id
1735 ,p_cost_group_id => p_cost_group_id
1736 ,p_user_id => l_user_id
1737 ,p_login_id => l_login_id
1738 ,p_req_id => l_req_id
1739 ,p_prg_id => l_prg_id
1740 ,p_prg_appid => l_prg_appid);
1741
1742 END IF;
1743
1744 IF p_starting_phase < 3 THEN
1745
1746 -- Copy the balance from the previous period if the run option
1747 -- is start or a previous run did not complete this phase
1748
1749 copy_balance
1750 (p_period_id => p_period_id
1751 ,p_prev_period_id => p_prev_period_id
1752 ,p_end_date => p_end_date
1753 ,p_legal_entity => p_legal_entity
1754 ,p_cost_type_id => p_cost_type_id
1755 ,p_cost_group_id => p_cost_group_id
1756 ,p_cost_method => p_cost_method
1757 ,p_user_id => l_user_id
1758 ,p_login_id => l_login_id
1759 ,p_req_id => l_req_id
1760 ,p_prg_id => l_prg_id
1761 ,p_prg_appid => l_prg_appid
1762 ,p_starting_phase => p_starting_phase);
1763
1764 END IF;
1765
1766 IF p_starting_phase < 4 THEN
1767
1768 -- Explode the bill of materials if the run option
1769 -- is start or a previous run did not complete this phase
1770
1771 explode_bom
1772 (p_period_id => p_period_id
1773 ,p_cost_group_id => p_cost_group_id
1774 ,p_start_date => p_start_date
1775 ,p_end_date => p_end_date
1776 ,p_user_id => l_user_id
1777 ,p_login_id => l_login_id
1778 ,p_req_id => l_req_id
1779 ,p_prg_id => l_prg_id
1780 ,p_prg_appid => l_prg_appid
1781 );
1782
1783 END IF;
1784
1785 IF p_starting_phase < 5 THEN
1786
1787 build_job_info
1788 (p_period_id => p_period_id
1789 ,p_start_date => p_start_date
1790 ,p_end_date => Trunc(p_end_date) + (86399/86400)
1791 ,p_cost_group_id => p_cost_group_id
1792 ,p_cost_type_id => p_cost_type_id
1793 ,p_pac_rates_id => p_pac_rates_id
1794 ,p_user_id => l_user_id
1795 ,p_login_id => l_login_id
1796 ,p_req_id => l_req_id
1797 ,p_prg_id => l_prg_id
1798 ,p_prg_appid => l_prg_appid);
1799
1800 END IF;
1801
1802 --process Periodic Cost update and Cost Owned transactions
1803
1804 Process_group1_txns_partial
1805 (p_period_id => p_period_id
1806 ,p_legal_entity => p_legal_entity
1807 ,p_master_org_id => p_master_org_id
1808 ,p_start_date => p_start_date
1809 ,p_end_date => Trunc(p_end_date) + (86399/86400) /*Added timestamp for Bug 8503757*/
1810 ,p_cost_group_id => p_cost_group_id
1811 ,p_cost_method => p_cost_method
1812 ,p_cost_type_id => p_cost_type_id
1813 ,p_pac_rates_id => p_pac_rates_id
1814 ,p_uom_control => p_uom_control
1815 ,p_user_id => l_user_id
1816 ,p_login_id => l_login_id
1817 ,p_req_id => l_req_id
1818 ,p_prg_id => l_prg_id
1819 ,p_prg_appid => l_prg_appid);
1820
1821 -- call retrieve interorg items and at the end set the status of phase 7 for the CG to Running.
1822 prepare_absorption_process
1823 (p_period_id => p_period_id
1824 ,p_start_date => p_start_date
1825 ,p_end_date => Trunc(p_end_date) + (86399/86400) /*Added timestamp for Bug 8503757*/
1826 ,p_cost_group_id => p_cost_group_id
1827 ,p_cost_type_id => p_cost_type_id
1828 ,p_pac_rates_id => p_pac_rates_id
1829 ,p_user_id => l_user_id
1830 ,p_login_id => l_login_id
1831 ,p_req_id => l_req_id
1832 ,p_prg_id => l_prg_id
1833 ,p_prg_appid => l_prg_appid);
1834
1835 -- set phase 8 status to 4 success
1836 set_status
1837 ( p_period_id => p_period_id
1838 , p_cost_group_id => p_cost_group_id
1839 , p_phase => 8
1840 , p_status => 4
1841 , p_end_date => p_end_date
1842 , p_user_id => l_user_id
1843 , p_login_id => l_login_id
1844 , p_req_id => l_req_id
1845 , p_prg_id => l_prg_id
1846 , p_prg_appid => l_prg_appid);
1847
1848 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1849 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE
1850 ,G_MODULE_HEAD || l_routine || '.end'
1851 ,l_routine || '>'
1852 );
1853 END IF;
1854
1855 EXCEPTION
1856
1857 WHEN FND_API.G_EXC_ERROR THEN
1858 ROLLBACK;
1859 set_status
1860 ( p_period_id => p_period_id
1861 , p_cost_group_id => p_cost_group_id
1862 , p_phase => 8
1863 , p_status => 3
1864 , p_end_date => p_end_date
1865 , p_user_id => l_user_id
1866 , p_login_id => l_login_id
1867 , p_req_id => l_req_id
1868 , p_prg_id => l_prg_id
1869 , p_prg_appid => l_prg_appid);
1870
1871 set_status
1872 ( p_period_id => p_period_id
1873 , p_cost_group_id => p_cost_group_id
1874 , p_phase => 7
1875 , p_status => 3
1876 , p_end_date => p_end_date
1877 , p_user_id => l_user_id
1878 , p_login_id => l_login_id
1879 , p_req_id => l_req_id
1880 , p_prg_id => l_prg_id
1881 , p_prg_appid => l_prg_appid);
1882
1883 x_retcode := '2';
1884 x_errbuf := substrb(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE)
1885 ,1
1886 ,250);
1887
1888 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1889 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION
1890 , G_MODULE_HEAD || l_routine , x_errbuf
1891 );
1892 END IF;
1893
1894 WHEN OTHERS THEN
1895 ROLLBACK;
1896 set_status
1897 ( p_period_id => p_period_id
1898 , p_cost_group_id => p_cost_group_id
1899 , p_phase => 8
1900 , p_status => 3
1901 , p_end_date => p_end_date
1902 , p_user_id => l_user_id
1903 , p_login_id => l_login_id
1904 , p_req_id => l_req_id
1905 , p_prg_id => l_prg_id
1906 , p_prg_appid => l_prg_appid);
1907
1908 set_status
1909 ( p_period_id => p_period_id
1910 , p_cost_group_id => p_cost_group_id
1911 , p_phase => 7
1912 , p_status => 3
1913 , p_end_date => p_end_date
1914 , p_user_id => l_user_id
1915 , p_login_id => l_login_id
1916 , p_req_id => l_req_id
1917 , p_prg_id => l_prg_id
1918 , p_prg_appid => l_prg_appid);
1919
1920 x_errbuf := SQLCODE || substr(SQLERRM, 1, 200);
1921 x_retcode := '2';
1922
1923 FND_FILE.put_line
1924 ( FND_FILE.log
1925 , 'Error in begin_cp_worker '|| x_errbuf
1926 );
1927
1928 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1929 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION
1930 , G_MODULE_HEAD || l_routine ||'.others_exc'
1931 , 'others:' || x_errbuf
1932 );
1933 END IF;
1934
1935 END begin_cp_worker;
1936
1937 END CST_PERIODIC_AVERAGE_PROC_CP;