[Home] [Help]
PACKAGE BODY: APPS.PSB_WS_POS_PVT
Source
1 PACKAGE BODY PSB_WS_POS_PVT AS
2 /* $Header: PSBPWCPB.pls 120.8 2006/04/07 11:18:03 shtripat ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PSB_WS_POS_PVT';
5
6 /* ----------------------------------------------------------------------- */
7
8 FUNCTION Check_Allowed
9 ( p_api_version IN NUMBER,
10 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
11 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
12 p_msg_count OUT NOCOPY NUMBER,
13 p_msg_data OUT NOCOPY VARCHAR2,
14 p_worksheet_id IN NUMBER,
15 p_position_budget_group_id IN NUMBER
16 ) RETURN VARCHAR2 IS
17
18 l_api_name CONSTANT VARCHAR2(30) := 'Check_Allowed';
19 l_api_version CONSTANT NUMBER := 1.0;
20
21 BEGIN
22
23 -- Standard call to check for call compatibility.
24
25 if not FND_API.Compatible_API_Call (l_api_version,
26 p_api_version,
27 l_api_name,
28 G_PKG_NAME)
29 then
30 raise FND_API.G_EXC_UNEXPECTED_ERROR;
31 end if;
32
33
34 -- Initialize message list if p_init_msg_list is set to TRUE
35
36 if FND_API.to_Boolean (p_init_msg_list) then
37 FND_MSG_PUB.initialize;
38 end if;
39
40
41 -- Call Private Function
42
43 RETURN PSB_WS_POS1.Check_Allowed
44 (p_api_version => 1.0,
45 p_validation_level => p_validation_level,
46 p_worksheet_id => p_worksheet_id,
47 p_position_budget_group_id => p_position_budget_group_id);
48
49
50 -- Standard call to get message count and if count is 1, get message info
51
52 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
53 p_data => p_msg_data);
54
55
56 EXCEPTION
57
58 when FND_API.G_EXC_ERROR then
59 return FND_API.G_FALSE;
60
61 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
62 p_data => p_msg_data);
63
64
65 when FND_API.G_EXC_UNEXPECTED_ERROR then
66 return FND_API.G_FALSE;
67
68 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
69 p_data => p_msg_data);
70
71
72 when OTHERS then
73 return FND_API.G_FALSE;
74
75 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
76 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
77 l_api_name);
78 end if;
79
80 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
81 p_data => p_msg_data);
82
83 END Check_Allowed;
84
85 /* ----------------------------------------------------------------------- */
86
87 PROCEDURE Create_Position_Lines
88 ( p_api_version IN NUMBER,
89 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
90 p_commit IN VARCHAR2 := FND_API.G_FALSE,
91 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
92 p_return_status OUT NOCOPY VARCHAR2,
93 p_msg_count OUT NOCOPY NUMBER,
94 p_msg_data OUT NOCOPY VARCHAR2,
95 p_position_line_id OUT NOCOPY NUMBER,
96 p_worksheet_id IN NUMBER,
97 p_position_id IN NUMBER,
98 p_budget_group_id IN NUMBER,
99 p_copy_of_position_line_id IN NUMBER := FND_API.G_MISS_NUM
100 ) IS
101
102 l_api_name CONSTANT VARCHAR2(30) := 'Create_Position_Lines';
103 l_api_version CONSTANT NUMBER := 1.0;
104
105 BEGIN
106
107 -- Standard Start of API savepoint
108
109 SAVEPOINT Create_Position_Lines_Pvt;
110
111
112 -- Standard call to check for call compatibility.
113
114 if not FND_API.Compatible_API_Call (l_api_version,
115 p_api_version,
116 l_api_name,
117 G_PKG_NAME)
118 then
119 raise FND_API.G_EXC_UNEXPECTED_ERROR;
120 end if;
121
122
123 -- Initialize message list if p_init_msg_list is set to TRUE
124
125 if FND_API.to_Boolean (p_init_msg_list) then
126 FND_MSG_PUB.initialize;
127 end if;
128
129
130 -- Call Private Function
131
132 PSB_WS_POS1.Create_Position_Lines
133 (p_api_version => 1.0,
134 p_validation_level => p_validation_level,
135 p_return_status => p_return_status,
136 p_position_line_id => p_position_line_id,
137 p_worksheet_id => p_worksheet_id,
138 p_position_id => p_position_id,
139 p_budget_group_id => p_budget_group_id,
140 p_copy_of_position_line_id => p_copy_of_position_line_id);
141
142
143 -- Standard Check of p_commit
144
145 if FND_API.to_Boolean (p_commit) then
146 commit work;
147 end if;
148
149
150 -- Standard call to get message count and if count is 1, get message info
151
152 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
153 p_data => p_msg_data);
154
155
156 EXCEPTION
157
158 when FND_API.G_EXC_ERROR then
159 rollback to Create_Position_Lines_Pvt;
160 p_return_status := FND_API.G_RET_STS_ERROR;
161
162 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
163 p_data => p_msg_data);
164
165
166 when FND_API.G_EXC_UNEXPECTED_ERROR then
167 rollback to Create_Position_Lines_Pvt;
168 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
169
170 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
171 p_data => p_msg_data);
172
173
174 when OTHERS then
175 rollback to Create_Position_Lines_Pvt;
176 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
177
178 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
179 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
180 l_api_name);
181 end if;
182
183 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
184 p_data => p_msg_data);
185
186 END Create_Position_Lines;
187
188 /* ----------------------------------------------------------------------- */
189
190 PROCEDURE Create_Position_Matrix
191 ( p_api_version IN NUMBER,
192 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
193 p_commit IN VARCHAR2 := FND_API.G_FALSE,
194 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
195 p_return_status OUT NOCOPY VARCHAR2,
196 p_msg_count OUT NOCOPY NUMBER,
197 p_msg_data OUT NOCOPY VARCHAR2,
198 p_worksheet_id IN NUMBER,
199 p_position_line_id IN NUMBER,
200 p_freeze_flag IN VARCHAR2 := FND_API.G_FALSE,
201 p_view_line_flag IN VARCHAR2 := FND_API.G_TRUE
202 ) IS
203
204 l_api_name CONSTANT VARCHAR2(30) := 'Create_Position_Matrix';
205 l_api_version CONSTANT NUMBER := 1.0;
206
207 BEGIN
208
209 -- Standard Start of API savepoint
210
211 SAVEPOINT Create_Position_Matrix_Pvt;
212
213
214 -- Standard call to check for call compatibility.
215
216 if not FND_API.Compatible_API_Call (l_api_version,
217 p_api_version,
218 l_api_name,
219 G_PKG_NAME)
220 then
221 raise FND_API.G_EXC_UNEXPECTED_ERROR;
222 end if;
223
224
225 -- Initialize message list if p_init_msg_list is set to TRUE
226
227 if FND_API.to_Boolean (p_init_msg_list) then
228 FND_MSG_PUB.initialize;
229 end if;
230
231
232 -- Call Private Function
233
234 PSB_WS_POS1.Create_Position_Matrix
235 (p_api_version => 1.0,
236 p_validation_level => p_validation_level,
237 p_return_status => p_return_status,
238 p_worksheet_id => p_worksheet_id,
239 p_position_line_id => p_position_line_id,
240 p_freeze_flag => p_freeze_flag,
241 p_view_line_flag => p_view_line_flag);
242
243
244 -- Standard Check of p_commit
245
246 if FND_API.to_Boolean (p_commit) then
247 commit work;
248 end if;
249
250
251 -- Standard call to get message count and if count is 1, get message info
252
253 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
254 p_data => p_msg_data);
255
256
257 EXCEPTION
258
259 when FND_API.G_EXC_ERROR then
260 rollback to Create_Position_Matrix_Pvt;
261 p_return_status := FND_API.G_RET_STS_ERROR;
262
263 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
264 p_data => p_msg_data);
265
266
267 when FND_API.G_EXC_UNEXPECTED_ERROR then
268 rollback to Create_Position_Matrix_Pvt;
269 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
270
271 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
272 p_data => p_msg_data);
273
274
275 when OTHERS then
276 rollback to Create_Position_Matrix_Pvt;
277 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
278
279 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
280 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
281 l_api_name);
282 end if;
283
284 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
285 p_data => p_msg_data);
286
287 END Create_Position_Matrix;
288
289 /* ----------------------------------------------------------------------- */
290
291 PROCEDURE Create_FTE_Lines
292 ( p_api_version IN NUMBER,
293 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
294 p_commit IN VARCHAR2 := FND_API.G_FALSE,
295 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
296 p_return_status OUT NOCOPY VARCHAR2,
297 p_msg_count OUT NOCOPY NUMBER,
298 p_msg_data OUT NOCOPY VARCHAR2,
299 p_fte_line_id OUT NOCOPY NUMBER,
300 p_check_spfl_exists IN VARCHAR2 := FND_API.G_TRUE,
301 p_recalculate_flag IN VARCHAR2 := FND_API.G_FALSE,
302 p_worksheet_id IN NUMBER,
303 p_flex_mapping_set_id IN NUMBER := FND_API.G_MISS_NUM,
304 p_position_line_id IN NUMBER,
305 p_budget_year_id IN NUMBER,
306 p_budget_group_id IN NUMBER := FND_API.G_MISS_NUM,
307 p_annual_fte IN NUMBER := FND_API.G_MISS_NUM,
308 p_service_package_id IN NUMBER,
309 p_stage_set_id IN NUMBER,
310 p_start_stage_seq IN NUMBER := FND_API.G_MISS_NUM,
311 p_current_stage_seq IN NUMBER,
312 p_end_stage_seq IN NUMBER := FND_API.G_MISS_NUM,
313 p_period_fte IN PSB_WS_ACCT1.g_prdamt_tbl_type
314 ) IS
315
316 l_api_name CONSTANT VARCHAR2(30) := 'Create_FTE_Lines';
317 l_api_version CONSTANT NUMBER := 1.0;
318
319 BEGIN
320
321 -- Standard Start of API savepoint
322
323 SAVEPOINT Create_FTE_Lines_Pvt;
324
325
326 -- Standard call to check for call compatibility.
327
328 if not FND_API.Compatible_API_Call (l_api_version,
329 p_api_version,
330 l_api_name,
331 G_PKG_NAME)
332 then
333 raise FND_API.G_EXC_UNEXPECTED_ERROR;
334 end if;
335
336
337 -- Initialize message list if p_init_msg_list is set to TRUE
338
339 if FND_API.to_Boolean (p_init_msg_list) then
340 FND_MSG_PUB.initialize;
341 end if;
342
343
344 -- Call Private Function
345
346 PSB_WS_POS1.Create_FTE_Lines
347 (p_api_version => 1.0,
348 p_validation_level => p_validation_level,
349 p_return_status => p_return_status,
350 p_fte_line_id => p_fte_line_id,
351 p_check_spfl_exists => p_check_spfl_exists,
352 p_recalculate_flag => p_recalculate_flag,
353 p_worksheet_id => p_worksheet_id,
354 p_flex_mapping_set_id => p_flex_mapping_set_id,
355 p_position_line_id => p_position_line_id,
356 p_budget_year_id => p_budget_year_id,
357 p_budget_group_id => p_budget_group_id,
358 p_annual_fte => p_annual_fte,
359 p_service_package_id => p_service_package_id,
360 p_stage_set_id => p_stage_set_id,
361 p_start_stage_seq => p_start_stage_seq,
362 p_current_stage_seq => p_current_stage_seq,
363 p_end_stage_seq => p_end_stage_seq,
364 p_period_fte => p_period_fte);
365
366
367 -- Standard Check of p_commit
368
369 if FND_API.to_Boolean (p_commit) then
370 commit work;
371 end if;
372
373
374 -- Standard call to get message count and if count is 1, get message info
375
376 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
377 p_data => p_msg_data);
378
379
380 EXCEPTION
381
382 when FND_API.G_EXC_ERROR then
383 rollback to Create_FTE_Lines_Pvt;
384 p_return_status := FND_API.G_RET_STS_ERROR;
385
386 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
387 p_data => p_msg_data);
388
389
390 when FND_API.G_EXC_UNEXPECTED_ERROR then
391 rollback to Create_FTE_Lines_Pvt;
392 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
393
394 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
395 p_data => p_msg_data);
396
397
398 when OTHERS then
399 rollback to Create_FTE_Lines_Pvt;
400 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
401
402 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
403 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
404 l_api_name);
405 end if;
406
407 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
408 p_data => p_msg_data);
409
410 END Create_FTE_Lines;
411
412 /* ----------------------------------------------------------------------- */
413
414 PROCEDURE Create_FTE_Lines
415 ( p_api_version IN NUMBER,
416 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
417 p_commit IN VARCHAR2 := FND_API.G_FALSE,
418 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
419 p_return_status OUT NOCOPY VARCHAR2,
420 p_msg_count OUT NOCOPY NUMBER,
421 p_msg_data OUT NOCOPY VARCHAR2,
422 p_check_stages IN VARCHAR2 := FND_API.G_TRUE,
423 p_worksheet_id IN NUMBER,
424 p_fte_line_id IN NUMBER,
425 p_service_package_id IN NUMBER := FND_API.G_MISS_NUM,
426 p_current_stage_seq IN NUMBER := FND_API.G_MISS_NUM,
427 p_period_fte IN PSB_WS_ACCT1.g_prdamt_tbl_type,
428 p_budget_group_id IN NUMBER := FND_API.G_MISS_NUM
429 ) IS
430
431 l_api_name CONSTANT VARCHAR2(30) := 'Create_FTE_Lines';
432 l_api_version CONSTANT NUMBER := 1.0;
433
434 /* Bug 4379636 Start */
435 l_position_line_id NUMBER;
436 l_budget_year_id NUMBER;
437 l_service_package_id NUMBER;
438 l_stage_set_id NUMBER;
439 l_start_stage_seq NUMBER;
440 l_current_stage_seq NUMBER;
441 l_end_stage_seq NUMBER;
442
443 CURSOR c_Wfl IS
444 SELECT position_line_id,
445 budget_year_id,
446 service_package_id,
447 stage_set_id,
448 start_stage_seq,
449 current_stage_seq,
450 end_stage_seq
451 FROM PSB_WS_FTE_LINES
452 WHERE fte_line_id = p_fte_line_id;
453
454 CURSOR l_annual_fte_csr IS
455 SELECT annual_fte
456 FROM PSB_WS_ACCOUNT_LINES
457 WHERE l_current_stage_seq BETWEEN start_stage_seq AND current_stage_seq
458 AND stage_set_id = l_stage_set_id
459 AND service_package_id = l_service_package_id
460 AND budget_year_id = l_budget_year_id
461 AND position_line_id = l_position_line_id;
462
463 CURSOR l_fte_lines IS
464 SELECT annual_fte,
465 period1_fte,
466 period2_fte,
467 period3_fte,
468 period4_fte,
469 period5_fte,
470 period6_fte,
471 period7_fte,
472 period8_fte,
473 period9_fte,
474 period10_fte,
475 period11_fte,
476 period12_fte
477 FROM psb_ws_fte_lines
478 WHERE l_current_stage_seq BETWEEN start_stage_seq AND current_stage_seq
479 AND stage_set_id = l_stage_set_id
480 AND service_package_id = l_service_package_id
481 AND budget_year_id = l_budget_year_id
482 AND position_line_id = l_position_line_id;
483
484
485 l_zero_fte_exists BOOLEAN := FALSE;
486 l_return_status VARCHAR2(1);
487
488 /* Bug 4379636 End */
489
490 BEGIN
491
492 -- Standard Start of API savepoint
493
494 SAVEPOINT Create_FTE_Lines_Pvt;
495
496
497 -- Standard call to check for call compatibility.
498
499 if not FND_API.Compatible_API_Call (l_api_version,
500 p_api_version,
501 l_api_name,
502 G_PKG_NAME)
503 then
504 raise FND_API.G_EXC_UNEXPECTED_ERROR;
505 end if;
506
507
508 -- Initialize message list if p_init_msg_list is set to TRUE
509
510 if FND_API.to_Boolean (p_init_msg_list) then
511 FND_MSG_PUB.initialize;
512 end if;
513
514
515 /* Bug 4379636 Start */
516 FOR c_Wfl_Rec IN c_Wfl LOOP
517 l_position_line_id := c_Wfl_Rec.position_line_id;
518 l_budget_year_id := c_Wfl_Rec.budget_year_id;
519 l_service_package_id := c_Wfl_Rec.service_package_id;
520 l_stage_set_id := c_Wfl_Rec.stage_set_id;
521 l_start_stage_seq := c_Wfl_Rec.start_stage_seq;
522 l_current_stage_seq := c_Wfl_Rec.current_stage_seq;
523 l_end_stage_seq := c_Wfl_Rec.end_stage_seq;
524 END LOOP;
525
526 FOR l_annual_fte_csr_rec IN l_annual_fte_csr
527 LOOP
528
529 IF l_annual_fte_csr_rec.annual_fte =0 THEN
530 l_zero_fte_exists := TRUE;
531 EXIT;
532 END IF;
533
534 END LOOP;
535
536 IF l_zero_fte_exists then
537
538 PSB_WS_POS2.Calculate_Position_Cost
539 (p_api_version => 1.0,
540 p_return_status => l_return_status,
541 p_worksheet_id => p_worksheet_id,
542 p_position_line_id => l_position_line_id,
543 p_budget_year_id => l_budget_year_id);
544
545 END IF;
546 /* Bug 4379636 End */
547
548 -- Call Private Function
549
550 PSB_WS_POS1.Create_FTE_Lines
551 (p_api_version => 1.0,
552 p_validation_level => p_validation_level,
553 p_return_status => p_return_status,
554 p_check_stages => p_check_stages,
555 p_worksheet_id => p_worksheet_id,
556 p_fte_line_id => p_fte_line_id,
557 p_service_package_id => p_service_package_id,
558 p_current_stage_seq => p_current_stage_seq,
559 p_period_fte => p_period_fte,
560 p_budget_group_id => p_budget_group_id);
561
562
563 -- Standard Check of p_commit
564
565 if FND_API.to_Boolean (p_commit) then
566 commit work;
567 end if;
568
569
570 -- Standard call to get message count and if count is 1, get message info
571
572 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
573 p_data => p_msg_data);
574
575
576 EXCEPTION
577
578 when FND_API.G_EXC_ERROR then
579 rollback to Create_FTE_Lines_Pvt;
580 p_return_status := FND_API.G_RET_STS_ERROR;
581
582 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
583 p_data => p_msg_data);
584
585
586 when FND_API.G_EXC_UNEXPECTED_ERROR then
587 rollback to Create_FTE_Lines_Pvt;
588 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
589
590 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
591 p_data => p_msg_data);
592
593
594 when OTHERS then
595 rollback to Create_FTE_Lines_Pvt;
596 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
597
598 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
599 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
600 l_api_name);
601 end if;
602
603 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
604 p_data => p_msg_data);
605
606 END Create_FTE_Lines;
607
608 /* ----------------------------------------------------------------------- */
609
610 PROCEDURE Create_Element_Lines
611 ( p_api_version IN NUMBER,
612 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
613 p_commit IN VARCHAR2 := FND_API.G_FALSE,
614 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
615 p_return_status OUT NOCOPY VARCHAR2,
616 p_msg_count OUT NOCOPY NUMBER,
617 p_msg_data OUT NOCOPY VARCHAR2,
618 p_element_line_id OUT NOCOPY NUMBER,
619 p_check_spel_exists IN VARCHAR2 := FND_API.G_TRUE,
620 p_position_line_id IN NUMBER,
621 p_budget_year_id IN NUMBER,
622 p_pay_element_id IN NUMBER,
623 p_currency_code IN VARCHAR2,
624 p_element_cost IN NUMBER,
625 p_element_set_id IN NUMBER,
626 p_service_package_id IN NUMBER,
627 p_stage_set_id IN NUMBER,
628 p_start_stage_seq IN NUMBER := FND_API.G_MISS_NUM,
629 p_current_stage_seq IN NUMBER,
630 p_end_stage_seq IN NUMBER := FND_API.G_MISS_NUM)
631 IS
632
633 l_api_name CONSTANT VARCHAR2(30) := 'Create_Element_Lines';
634 l_api_version CONSTANT NUMBER := 1.0;
635
636 BEGIN
637
638 -- Standard Start of API savepoint
639
640 SAVEPOINT Create_Element_Lines_Pvt;
641
642
643 -- Standard call to check for call compatibility.
644
645 if not FND_API.Compatible_API_Call (l_api_version,
646 p_api_version,
647 l_api_name,
648 G_PKG_NAME)
649 then
650 raise FND_API.G_EXC_UNEXPECTED_ERROR;
651 end if;
652
653
654 -- Initialize message list if p_init_msg_list is set to TRUE
655
656 if FND_API.to_Boolean (p_init_msg_list) then
657 FND_MSG_PUB.initialize;
658 end if;
659
660
661 -- Call Private Function
662
663 PSB_WS_POS1.Create_Element_Lines
664 (p_api_version => 1.0,
665 p_validation_level => p_validation_level,
666 p_return_status => p_return_status,
667 p_element_line_id => p_element_line_id,
668 p_check_spel_exists => p_check_spel_exists,
669 p_position_line_id => p_position_line_id,
670 p_budget_year_id => p_budget_year_id,
671 p_pay_element_id => p_pay_element_id,
672 p_currency_code => p_currency_code,
673 p_element_cost => p_element_cost,
674 p_element_set_id => p_element_set_id,
675 p_service_package_id => p_service_package_id,
676 p_stage_set_id => p_stage_set_id,
677 p_start_stage_seq => p_start_stage_seq,
678 p_current_stage_seq => p_current_stage_seq,
679 p_end_stage_seq => p_end_stage_seq);
680
681 -- Standard Check of p_commit
682
683 if FND_API.to_Boolean (p_commit) then
684 commit work;
685 end if;
686
687
688 -- Standard call to get message count and if count is 1, get message info
689
690 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
691 p_data => p_msg_data);
692
693
694 EXCEPTION
695
696 when FND_API.G_EXC_ERROR then
697 rollback to Create_Element_Lines_Pvt;
698 p_return_status := FND_API.G_RET_STS_ERROR;
699
700 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
701 p_data => p_msg_data);
702
703
704 when FND_API.G_EXC_UNEXPECTED_ERROR then
705 rollback to Create_Element_Lines_Pvt;
706 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
707
708 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
709 p_data => p_msg_data);
710
711
712 when OTHERS then
713 rollback to Create_Element_Lines_Pvt;
714 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
715
716 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
717 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
718 l_api_name);
719 end if;
720
721 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
722 p_data => p_msg_data);
723
724 END Create_Element_Lines;
725
726 /* ----------------------------------------------------------------------- */
727
728 PROCEDURE Create_Element_Lines
729 ( p_api_version IN NUMBER,
730 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
731 p_commit IN VARCHAR2 := FND_API.G_FALSE,
732 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
733 p_return_status OUT NOCOPY VARCHAR2,
734 p_msg_count OUT NOCOPY NUMBER,
735 p_msg_data OUT NOCOPY VARCHAR2,
736 p_check_stages IN VARCHAR2 := FND_API.G_TRUE,
737 p_element_line_id IN NUMBER,
738 p_service_package_id IN NUMBER := FND_API.G_MISS_NUM,
739 p_current_stage_seq IN NUMBER := FND_API.G_MISS_NUM,
740 p_element_cost IN NUMBER := FND_API.G_MISS_NUM
741 ) IS
742
743 l_api_name CONSTANT VARCHAR2(30) := 'Create_Element_Lines';
744 l_api_version CONSTANT NUMBER := 1.0;
745
746 BEGIN
747
748 -- Standard Start of API savepoint
749
750 SAVEPOINT Create_Element_Lines_Pvt;
751
752
753 -- Standard call to check for call compatibility.
754
755 if not FND_API.Compatible_API_Call (l_api_version,
756 p_api_version,
757 l_api_name,
758 G_PKG_NAME)
759 then
760 raise FND_API.G_EXC_UNEXPECTED_ERROR;
761 end if;
762
763
764 -- Initialize message list if p_init_msg_list is set to TRUE
765
766 if FND_API.to_Boolean (p_init_msg_list) then
767 FND_MSG_PUB.initialize;
768 end if;
769
770
771 -- Call Private Function
772
773 PSB_WS_POS1.Create_Element_Lines
774 (p_api_version => 1.0,
775 p_validation_level => p_validation_level,
776 p_return_status => p_return_status,
777 p_check_stages => p_check_stages,
778 p_element_line_id => p_element_line_id,
779 p_service_package_id => p_service_package_id,
780 p_current_stage_seq => p_current_stage_seq,
781 p_element_cost => p_element_cost);
782
783
784 -- Standard Check of p_commit
785
786 if FND_API.to_Boolean (p_commit) then
787 commit work;
788 end if;
789
790
791 -- Standard call to get message count and if count is 1, get message info
792
793 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
794 p_data => p_msg_data);
795
796
797 EXCEPTION
798
799 when FND_API.G_EXC_ERROR then
800 rollback to Create_Element_Lines_Pvt;
801 p_return_status := FND_API.G_RET_STS_ERROR;
802
803 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
804 p_data => p_msg_data);
805
806
807 when FND_API.G_EXC_UNEXPECTED_ERROR then
808 rollback to Create_Element_Lines_Pvt;
809 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
810
811 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
812 p_data => p_msg_data);
813
814
815 when OTHERS then
816 rollback to Create_Element_Lines_Pvt;
817 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
818
819 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
820 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
821 l_api_name);
822 end if;
823
824 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
825 p_data => p_msg_data);
826
827 END Create_Element_Lines;
828
829 /* ----------------------------------------------------------------------- */
830
831 PROCEDURE Update_Annual_FTE
832 ( p_api_version IN NUMBER,
833 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
834 p_commit IN VARCHAR2 := FND_API.G_FALSE,
835 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
836 p_return_status OUT NOCOPY VARCHAR2,
837 p_msg_count OUT NOCOPY NUMBER,
838 p_msg_data OUT NOCOPY VARCHAR2,
839 p_worksheet_id IN NUMBER,
840 p_position_line_id IN NUMBER,
841 p_budget_year_id IN NUMBER,
842 p_service_package_id IN NUMBER,
843 p_stage_set_id IN NUMBER,
844 p_current_stage_seq IN NUMBER,
845 p_budget_group_id IN NUMBER := FND_API.G_MISS_NUM
846 ) IS
847
848 l_api_name CONSTANT VARCHAR2(30) := 'Update_Annual_FTE';
849 l_api_version CONSTANT NUMBER := 1.0;
850
851 BEGIN
852
853 -- Standard Start of API savepoint
854
855 SAVEPOINT Update_Annual_FTE_Pvt;
856
857
858 -- Standard call to check for call compatibility.
859
860 if not FND_API.Compatible_API_Call (l_api_version,
861 p_api_version,
862 l_api_name,
863 G_PKG_NAME)
864 then
865 raise FND_API.G_EXC_UNEXPECTED_ERROR;
866 end if;
867
868
869 -- Initialize message list if p_init_msg_list is set to TRUE
870
871 if FND_API.to_Boolean (p_init_msg_list) then
872 FND_MSG_PUB.initialize;
873 end if;
874
875
876 -- Call Private Function
877
878 PSB_WS_POS1.Update_Annual_FTE
879 (p_api_version => 1.0,
880 p_validation_level => p_validation_level,
881 p_return_status => p_return_status,
882 p_worksheet_id => p_worksheet_id,
883 p_position_line_id => p_position_line_id,
884 p_budget_year_id => p_budget_year_id,
885 p_service_package_id => p_service_package_id,
886 p_stage_set_id => p_stage_set_id,
887 p_current_stage_seq => p_current_stage_seq,
888 p_budget_group_id => p_budget_group_id);
889
890
891 -- Standard Check of p_commit
892
893 if FND_API.to_Boolean (p_commit) then
894 commit work;
895 end if;
896
897
898 -- Standard call to get message count and if count is 1, get message info
899
900 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
901 p_data => p_msg_data);
902
903
904 EXCEPTION
905
906 when FND_API.G_EXC_ERROR then
907 rollback to Update_Annual_FTE_Pvt;
908 p_return_status := FND_API.G_RET_STS_ERROR;
909
910 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
911 p_data => p_msg_data);
912
913
914 when FND_API.G_EXC_UNEXPECTED_ERROR then
915 rollback to Update_Annual_FTE_Pvt;
916 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
917
918 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
919 p_data => p_msg_data);
920
921
922 when OTHERS then
923 rollback to Update_Annual_FTE_Pvt;
924 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
925
926 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
927 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
928 l_api_name);
929 end if;
930
931 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
932 p_data => p_msg_data);
933
934 END Update_Annual_FTE;
935
936 /* ----------------------------------------------------------------------- */
937
938 PROCEDURE Redistribute_Follow_Salary
939 ( p_api_version IN NUMBER,
940 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
941 p_commit IN VARCHAR2 := FND_API.G_FALSE,
942 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
943 p_return_status OUT NOCOPY VARCHAR2,
944 p_msg_count OUT NOCOPY NUMBER,
945 p_msg_data OUT NOCOPY VARCHAR2,
946 p_worksheet_id IN NUMBER,
947 p_position_line_id IN NUMBER,
948 p_budget_year_id IN NUMBER := FND_API.G_MISS_NUM,
949 p_service_package_id IN NUMBER,
950 p_stage_set_id IN NUMBER,
951 p_func_currency IN VARCHAR2 := FND_API.G_MISS_CHAR
952 ) IS
953
954 l_api_name CONSTANT VARCHAR2(30) := 'Redistribute_Follow_Salary';
955 l_api_version CONSTANT NUMBER := 1.0;
956
957 BEGIN
958
959 -- Standard Start of API savepoint
960
961 SAVEPOINT Redistribute_Follow_Salary_Pvt;
962
963
964 -- Standard call to check for call compatibility.
965
966 if not FND_API.Compatible_API_Call (l_api_version,
967 p_api_version,
968 l_api_name,
969 G_PKG_NAME)
970 then
971 raise FND_API.G_EXC_UNEXPECTED_ERROR;
972 end if;
973
974
975 -- Initialize message list if p_init_msg_list is set to TRUE
976
977 if FND_API.to_Boolean (p_init_msg_list) then
978 FND_MSG_PUB.initialize;
979 end if;
980
981
982 -- Call Private Function
983
984 PSB_WS_POS3.Redistribute_Follow_Salary
985 (p_api_version => 1.0,
986 p_validation_level => p_validation_level,
987 p_return_status => p_return_status,
988 p_worksheet_id => p_worksheet_id,
989 p_position_line_id => p_position_line_id,
990 p_budget_year_id => p_budget_year_id,
991 p_service_package_id => p_service_package_id,
992 p_stage_set_id => p_stage_set_id,
993 p_func_currency => p_func_currency);
994
995
996 -- Standard Check of p_commit
997
998 if FND_API.to_Boolean (p_commit) then
999 commit work;
1000 end if;
1001
1002
1003 -- Standard call to get message count and if count is 1, get message info
1004
1005 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1006 p_data => p_msg_data);
1007
1008
1009 EXCEPTION
1010
1011 when FND_API.G_EXC_ERROR then
1012 rollback to Redistribute_Follow_Salary_Pvt;
1013 p_return_status := FND_API.G_RET_STS_ERROR;
1014
1015 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1016 p_data => p_msg_data);
1017
1018
1019 when FND_API.G_EXC_UNEXPECTED_ERROR then
1020 rollback to Redistribute_Follow_Salary_Pvt;
1021 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1022
1023 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1024 p_data => p_msg_data);
1025
1026
1027 when OTHERS then
1028 rollback to Redistribute_Follow_Salary_Pvt;
1029 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1030
1031 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
1032 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
1033 l_api_name);
1034 end if;
1035
1036 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1037 p_data => p_msg_data);
1038
1039 END Redistribute_Follow_Salary;
1040
1041 /* ----------------------------------------------------------------------- */
1042
1043 PROCEDURE Apply_Element_Parameters
1044 ( p_api_version IN NUMBER,
1045 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1046 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1047 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
1048 p_return_status OUT NOCOPY VARCHAR2,
1049 p_msg_count OUT NOCOPY NUMBER,
1050 p_msg_data OUT NOCOPY VARCHAR2,
1051 p_worksheet_id IN NUMBER,
1052 p_global_worksheet IN VARCHAR2 := FND_API.G_MISS_CHAR,
1053 p_budget_group_id IN NUMBER := FND_API.G_MISS_NUM,
1054 p_data_extract_id IN NUMBER := FND_API.G_MISS_NUM,
1055 p_business_group_id IN NUMBER := FND_API.G_MISS_NUM,
1056 p_func_currency IN VARCHAR2 := FND_API.G_MISS_CHAR,
1057 p_budget_calendar_id IN NUMBER := FND_API.G_MISS_NUM,
1058 p_parameter_set_id IN NUMBER := FND_API.G_MISS_NUM
1059 ) IS
1060
1061 l_api_name CONSTANT VARCHAR2(30) := 'Apply_Element_Parameters';
1062 l_api_version CONSTANT NUMBER := 1.0;
1063
1064 BEGIN
1065
1066 -- Standard Start of API savepoint
1067
1068 SAVEPOINT Apply_Element_Parameters_Pvt;
1069
1070
1071 -- Standard call to check for call compatibility.
1072
1073 if not FND_API.Compatible_API_Call (l_api_version,
1074 p_api_version,
1075 l_api_name,
1076 G_PKG_NAME)
1077 then
1078 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1079 end if;
1080
1081
1082 -- Initialize message list if p_init_msg_list is set to TRUE
1083
1084 if FND_API.to_Boolean (p_init_msg_list) then
1085 FND_MSG_PUB.initialize;
1086 end if;
1087
1088
1089 -- Call Private Function
1090
1091 PSB_WS_POS3.Apply_Element_Parameters
1092 (p_api_version => 1.0,
1093 p_validation_level => p_validation_level,
1094 p_return_status => p_return_status,
1095 p_worksheet_id => p_worksheet_id,
1096 p_global_worksheet => p_global_worksheet,
1097 p_budget_group_id => p_budget_group_id,
1098 p_data_extract_id => p_data_extract_id,
1099 p_business_group_id => p_business_group_id,
1100 p_func_currency => p_func_currency,
1101 p_budget_calendar_id => p_budget_calendar_id,
1102 p_parameter_set_id => p_parameter_set_id);
1103
1104
1105 -- Standard Check of p_commit
1106
1107 if FND_API.to_Boolean (p_commit) then
1108 commit work;
1109 end if;
1110
1111
1112 -- Standard call to get message count and if count is 1, get message info
1113
1114 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1115 p_data => p_msg_data);
1116
1117
1118 EXCEPTION
1119
1120 when FND_API.G_EXC_ERROR then
1121 rollback to Apply_Element_Parameters_Pvt;
1122 p_return_status := FND_API.G_RET_STS_ERROR;
1123
1124 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1125 p_data => p_msg_data);
1126
1127
1128 when FND_API.G_EXC_UNEXPECTED_ERROR then
1129 rollback to Apply_Element_Parameters_Pvt;
1130 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1131
1132 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1133 p_data => p_msg_data);
1134
1135
1136 when OTHERS then
1137 rollback to Apply_Element_Parameters_Pvt;
1138 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1139
1140 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
1141 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
1142 l_api_name);
1143 end if;
1144
1145 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1146 p_data => p_msg_data);
1147
1148 END Apply_Element_Parameters;
1149
1150 /* ----------------------------------------------------------------------- */
1151
1152 PROCEDURE Apply_Position_Parameters
1153 ( p_api_version IN NUMBER,
1154 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1155 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1156 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
1157 p_return_status OUT NOCOPY VARCHAR2,
1158 p_msg_count OUT NOCOPY NUMBER,
1159 p_msg_data OUT NOCOPY VARCHAR2,
1160 p_worksheet_id IN NUMBER,
1161 p_global_worksheet IN VARCHAR2 := FND_API.G_MISS_CHAR,
1162 p_budget_group_id IN NUMBER := FND_API.G_MISS_NUM,
1163 p_data_extract_id IN NUMBER := FND_API.G_MISS_NUM,
1164 p_business_group_id IN NUMBER := FND_API.G_MISS_NUM,
1165 p_func_currency IN VARCHAR2 := FND_API.G_MISS_CHAR,
1166 p_budget_calendar_id IN NUMBER := FND_API.G_MISS_NUM,
1167 p_parameter_set_id IN NUMBER := FND_API.G_MISS_NUM
1168 ) IS
1169
1170 l_api_name CONSTANT VARCHAR2(30) := 'Apply_Position_Parameters';
1171 l_api_version CONSTANT NUMBER := 1.0;
1172
1173 BEGIN
1174
1175 -- Standard Start of API savepoint
1176
1177 SAVEPOINT Apply_Position_Parameters_Pvt;
1178
1179
1180 -- Standard call to check for call compatibility.
1181
1182 if not FND_API.Compatible_API_Call (l_api_version,
1183 p_api_version,
1184 l_api_name,
1185 G_PKG_NAME)
1186 then
1187 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1188 end if;
1189
1190
1191 -- Initialize message list if p_init_msg_list is set to TRUE
1192
1193 if FND_API.to_Boolean (p_init_msg_list) then
1194 FND_MSG_PUB.initialize;
1195 end if;
1196
1197
1198 -- Call Private Function
1199
1200 PSB_WS_POS3.Apply_Position_Parameters
1201 (p_api_version => 1.0,
1202 p_validation_level => p_validation_level,
1203 p_return_status => p_return_status,
1204 p_worksheet_id => p_worksheet_id,
1205 p_global_worksheet => p_global_worksheet,
1206 p_budget_group_id => p_budget_group_id,
1207 p_data_extract_id => p_data_extract_id,
1208 p_business_group_id => p_business_group_id,
1209 p_func_currency => p_func_currency,
1210 p_budget_calendar_id => p_budget_calendar_id,
1211 p_parameter_set_id => p_parameter_set_id);
1212
1213
1214 -- Standard Check of p_commit
1215
1216 if FND_API.to_Boolean (p_commit) then
1217 commit work;
1218 end if;
1219
1220
1221 -- Standard call to get message count and if count is 1, get message info
1222
1223 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1224 p_data => p_msg_data);
1225
1226
1227 EXCEPTION
1228
1229 when FND_API.G_EXC_ERROR then
1230 rollback to Apply_Position_Parameters_Pvt;
1231 p_return_status := FND_API.G_RET_STS_ERROR;
1232
1233 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1234 p_data => p_msg_data);
1235
1236
1237 when FND_API.G_EXC_UNEXPECTED_ERROR then
1238 rollback to Apply_Position_Parameters_Pvt;
1239 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1240
1241 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1242 p_data => p_msg_data);
1243
1244
1245 when OTHERS then
1246 rollback to Apply_Position_Parameters_Pvt;
1247 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1248
1249 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
1250 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
1251 l_api_name);
1252 end if;
1253
1254 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1255 p_data => p_msg_data);
1256
1257 END Apply_Position_Parameters;
1258
1259 /* ----------------------------------------------------------------------- */
1260
1261 PROCEDURE Calculate_Position_Cost
1262 ( p_api_version IN NUMBER,
1263 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1264 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1265 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
1266 p_return_status OUT NOCOPY VARCHAR2,
1267 p_msg_count OUT NOCOPY NUMBER,
1268 p_msg_data OUT NOCOPY VARCHAR2,
1269 p_worksheet_id IN NUMBER,
1270 p_position_line_id IN NUMBER,
1271 p_recalculate_flag IN VARCHAR2 := FND_API.G_TRUE,
1272 p_root_budget_group_id IN NUMBER := FND_API.G_MISS_NUM,
1273 p_global_worksheet_id IN NUMBER := FND_API.G_MISS_NUM,
1274 p_assign_worksheet_id IN NUMBER := FND_API.G_MISS_NUM,
1275 p_worksheet_numyrs IN NUMBER := FND_API.G_MISS_NUM,
1276 p_rounding_factor IN NUMBER := FND_API.G_MISS_NUM,
1277 p_service_package_id IN NUMBER := FND_API.G_MISS_NUM,
1278 p_stage_set_id IN NUMBER := FND_API.G_MISS_NUM,
1279 p_start_stage_seq IN NUMBER := FND_API.G_MISS_NUM,
1280 p_current_stage_seq IN NUMBER := FND_API.G_MISS_NUM,
1281 p_data_extract_id IN NUMBER := FND_API.G_MISS_NUM,
1282 p_business_group_id IN NUMBER := FND_API.G_MISS_NUM,
1283 p_budget_calendar_id IN NUMBER := FND_API.G_MISS_NUM,
1284 p_func_currency IN VARCHAR2 := FND_API.G_MISS_CHAR,
1285 p_flex_mapping_set_id IN NUMBER := FND_API.G_MISS_NUM,
1286 p_flex_code IN NUMBER := FND_API.G_MISS_NUM,
1287 p_position_id IN NUMBER := FND_API.G_MISS_NUM,
1288 p_position_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
1289 p_position_start_date IN DATE := FND_API.G_MISS_DATE,
1290 p_position_end_date IN DATE := FND_API.G_MISS_DATE
1291 ) IS
1292
1293 l_api_name CONSTANT VARCHAR2(30) := 'Calculate_Position_Cost';
1294 l_api_version CONSTANT NUMBER := 1.0;
1295
1296 BEGIN
1297
1298 -- Standard Start of API savepoint
1299
1300 SAVEPOINT Calculate_Position_Cost_Pvt;
1301
1302
1303 -- Standard call to check for call compatibility.
1304
1305 if not FND_API.Compatible_API_Call (l_api_version,
1306 p_api_version,
1307 l_api_name,
1308 G_PKG_NAME)
1309 then
1310 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1311 end if;
1312
1313
1314 -- Initialize message list if p_init_msg_list is set to TRUE
1315
1316 if FND_API.to_Boolean (p_init_msg_list) then
1317 FND_MSG_PUB.initialize;
1318 end if;
1319
1320
1321 -- Call Private Function
1322
1323 PSB_WS_POS2.Calculate_Position_Cost
1324 (p_api_version => 1.0,
1325 p_validation_level => p_validation_level,
1326 p_return_status => p_return_status,
1327 p_worksheet_id => p_worksheet_id,
1328 p_position_line_id => p_position_line_id,
1329 p_recalculate_flag => p_recalculate_flag,
1330 p_root_budget_group_id => p_root_budget_group_id,
1331 p_global_worksheet_id => p_global_worksheet_id,
1332 p_assign_worksheet_id => p_assign_worksheet_id,
1333 p_worksheet_numyrs => p_worksheet_numyrs,
1334 p_rounding_factor => p_rounding_factor,
1335 p_service_package_id => p_service_package_id,
1336 p_stage_set_id => p_stage_set_id,
1337 p_start_stage_seq => p_start_stage_seq,
1338 p_current_stage_seq => p_current_stage_seq,
1339 p_data_extract_id => p_data_extract_id,
1340 p_business_group_id => p_business_group_id,
1341 p_budget_calendar_id => p_budget_calendar_id,
1342 p_func_currency => p_func_currency,
1343 p_flex_mapping_set_id => p_flex_mapping_set_id,
1344 p_flex_code => p_flex_code,
1345 p_position_id => p_position_id,
1346 p_position_name => p_position_name,
1347 p_position_start_date => p_position_start_date,
1348 p_position_end_date => p_position_end_date);
1349
1350
1351 /* Start bug #4167811 */
1352 -- Delete records from psb_ws_account_lines whose budget group is changed.
1353 -- Also delete the mappings for those account lines from psb_ws_lines.
1354 FOR rec_wal IN (SELECT count(*) record_count
1355 FROM psb_ws_account_lines
1356 WHERE position_line_id = p_position_line_id
1357 AND budget_group_changed = 'Y')
1358 LOOP
1359 IF rec_wal.record_count > 0
1360 THEN
1361 DELETE FROM psb_ws_lines
1362 WHERE account_line_id IN (SELECT account_line_id
1363 FROM psb_ws_account_lines
1364 WHERE position_line_id = p_position_line_id
1365 AND budget_group_changed = 'Y');
1366
1367 DELETE FROM psb_ws_account_lines
1368 WHERE position_line_id = p_position_line_id
1369 AND budget_group_changed = 'Y';
1370
1371 END IF;
1372 END LOOP;
1373 /* End bug #4167811 */
1374
1375 -- Standard Check of p_commit
1376
1377 if FND_API.to_Boolean (p_commit) then
1378 commit work;
1379 end if;
1380
1381
1382 -- Standard call to get message count and if count is 1, get message info
1383
1384 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1385 p_data => p_msg_data);
1386
1387
1388 EXCEPTION
1389
1390 when FND_API.G_EXC_ERROR then
1391 rollback to Calculate_Position_Cost_Pvt;
1392 p_return_status := FND_API.G_RET_STS_ERROR;
1393
1394 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1395 p_data => p_msg_data);
1396
1397
1398 when FND_API.G_EXC_UNEXPECTED_ERROR then
1399 rollback to Calculate_Position_Cost_Pvt;
1400 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1401
1402 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1403 p_data => p_msg_data);
1404
1405
1406 when OTHERS then
1407 rollback to Calculate_Position_Cost_Pvt;
1408 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1409
1410 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
1411 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
1412 l_api_name);
1413 end if;
1414
1415 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1416 p_data => p_msg_data);
1417
1418 END Calculate_Position_Cost;
1419
1420 /* ----------------------------------------------------------------------- */
1421
1422 PROCEDURE Revise_Position_Projections
1423 ( p_api_version IN NUMBER,
1424 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1425 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1426 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
1427 p_return_status OUT NOCOPY VARCHAR2,
1428 p_msg_count OUT NOCOPY NUMBER,
1429 p_msg_data OUT NOCOPY VARCHAR2,
1430 p_worksheet_id IN NUMBER,
1431 p_parameter_id IN NUMBER
1432 ) IS
1433
1434 l_api_name CONSTANT VARCHAR2(30) := 'Revise_Position_Projections';
1435 l_api_version CONSTANT NUMBER := 1.0;
1436
1437 l_return_status VARCHAR2(1);
1438 l_msg_data VARCHAR2(2000);
1439 l_msg_count NUMBER;
1440 l_validation_status VARCHAR2(1);
1441
1442 BEGIN
1443
1444 -- Standard Start of API savepoint
1445
1446 SAVEPOINT Revise_Pos_Projections_Pvt;
1447
1448
1449 -- Standard call to check for call compatibility.
1450
1451 if not FND_API.Compatible_API_Call (l_api_version,
1452 p_api_version,
1453 l_api_name,
1454 G_PKG_NAME)
1455 then
1456 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1457 end if;
1458
1459
1460 -- Initialize message list if p_init_msg_list is set to TRUE
1461
1462 if FND_API.to_Boolean (p_init_msg_list) then
1463 FND_MSG_PUB.initialize;
1464 end if;
1465
1466 -- Enforce Concurrency Control
1467
1468 PSB_CONCURRENCY_CONTROL_PVT.Enforce_Concurrency_Control
1469 (p_api_version => 1.0,
1470 p_return_status => l_return_status,
1471 p_concurrency_class => 'WORKSHEET_CREATION',
1472 p_concurrency_entity_name => 'WORKSHEET',
1473 p_concurrency_entity_id => p_worksheet_id);
1474
1475 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1476 raise FND_API.G_EXC_ERROR;
1477 end if;
1478
1479 -- Call Private Function
1480
1481 PSB_WS_POS3.Revise_Position_Projections
1482 (p_api_version => 1.0,
1483 p_validation_level => p_validation_level,
1484 p_return_status => l_return_status,
1485 x_msg_data => l_msg_data,
1486 x_msg_count => l_msg_count,
1487 p_worksheet_id => p_worksheet_id,
1488 p_parameter_id => p_parameter_id);
1489
1490 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1491 raise FND_API.G_EXC_ERROR;
1492 end if;
1493
1494 PSB_WS_ACCT2.Create_Rollup_Totals
1495 (p_api_version => 1.0,
1496 p_return_status => l_return_status,
1497 p_worksheet_id => p_worksheet_id);
1498
1499 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1500 raise FND_API.G_EXC_ERROR;
1501 end if;
1502
1503 PSB_WORKSHEET.Apply_Constraints
1504 (p_api_version => 1.0,
1505 p_return_status => l_return_status,
1506 p_validation_status => l_validation_status,
1507 p_worksheet_id => p_worksheet_id);
1508
1509 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1510 raise FND_API.G_EXC_ERROR;
1511 end if;
1512
1513 PSB_CONCURRENCY_CONTROL_PVT.Release_Concurrency_Control
1514 (p_api_version => 1.0,
1515 p_return_status => l_return_status,
1516 p_concurrency_class => 'WORKSHEET_CREATION',
1517 p_concurrency_entity_name => 'WORKSHEET',
1518 p_concurrency_entity_id => p_worksheet_id);
1519
1520 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1521 RAISE FND_API.G_EXC_ERROR;
1522 end if;
1523
1524
1525 -- Standard Check of p_commit
1526
1527 if FND_API.to_Boolean (p_commit) then
1528 commit work;
1529 end if;
1530
1531
1532 -- Standard call to get message count and if count is 1, get message info
1533
1534 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1535 p_data => p_msg_data);
1536
1537
1538 EXCEPTION
1539
1540 when FND_API.G_EXC_ERROR then
1541 --rollback to Revise_Pos_Projections_Pvt;
1542 p_return_status := FND_API.G_RET_STS_ERROR;
1543 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1544 p_data => p_msg_data);
1545
1546
1547 when FND_API.G_EXC_UNEXPECTED_ERROR then
1548 --rollback to Revise_Pos_Projections_Pvt;
1549 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1550 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1551 p_data => p_msg_data);
1552
1553
1554 when OTHERS then
1555 --rollback to Revise_Pos_Projections_Pvt;
1556 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1557
1558 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
1559 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
1560 l_api_name);
1561 end if;
1562
1563 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1564 p_data => p_msg_data);
1565
1566 END Revise_Position_Projections;
1567
1568 /*===========================================================================+
1569 | PROCEDURE Apply_Element_Parameters_CP |
1570 +===========================================================================*/
1571 --
1572 -- This is the execution file for the concurrent program 'Apply
1573 -- Element Parameters'
1574 --
1575 PROCEDURE Apply_Element_Parameters_CP
1576 (
1577 errbuf OUT NOCOPY VARCHAR2 ,
1578 retcode OUT NOCOPY VARCHAR2 ,
1579 --
1580 p_worksheet_id IN NUMBER
1581 )
1582 IS
1583 --
1584 l_api_name CONSTANT VARCHAR2(30) := 'Apply_Element_Parameters_CP';
1585 l_api_version CONSTANT NUMBER := 1.0 ;
1586 --
1587 l_error_api_name VARCHAR2(2000);
1588 l_return_status VARCHAR2(1) ;
1589 l_msg_count NUMBER ;
1590 l_msg_data VARCHAR2(2000) ;
1591 l_msg_index_out NUMBER;
1592
1593 BEGIN
1594
1595 PSB_CONCURRENCY_CONTROL_PVT.Enforce_Concurrency_Control
1596 (p_api_version => 1.0,
1597 p_return_status => l_return_status,
1598 p_concurrency_class => 'WORKSHEET_CREATION',
1599 p_concurrency_entity_name => 'WORKSHEET',
1600 p_concurrency_entity_id => p_worksheet_id);
1601
1602 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1603 raise FND_API.G_EXC_ERROR;
1604 end if;
1605
1606 PSB_WS_POS_PVT.Apply_Element_Parameters
1607 (p_api_version => 1.0,
1608 p_init_msg_list => FND_API.G_TRUE,
1609 p_return_status => l_return_status,
1610 p_msg_count => l_msg_count,
1611 p_msg_data => l_msg_data,
1612 p_worksheet_id => p_worksheet_id);
1613
1614 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1615 raise FND_API.G_EXC_ERROR;
1616 end if;
1617
1618 --Calling Release_Concurrency_Control
1619
1620 PSB_CONCURRENCY_CONTROL_PVT.Release_Concurrency_Control
1621 (p_api_version => 1.0,
1622 p_return_status => l_return_status,
1623 p_concurrency_class => 'WORKSHEET_CREATION',
1624 p_concurrency_entity_name => 'WORKSHEET',
1625 p_concurrency_entity_id => p_worksheet_id);
1626
1627 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1628 raise FND_API.G_EXC_ERROR;
1629 end if;
1630
1631 PSB_MESSAGE_S.Print_Success;
1632 retcode := 0 ;
1633
1634 COMMIT WORK;
1635
1636 EXCEPTION
1637
1638 WHEN FND_API.G_EXC_ERROR THEN
1639 --
1640
1641 PSB_CONCURRENCY_CONTROL_PVT.Release_Concurrency_Control
1642 (p_api_version => 1.0,
1643 p_return_status => l_return_status,
1644 p_concurrency_class => 'WORKSHEET_CREATION',
1645 p_concurrency_entity_name => 'WORKSHEET',
1646 p_concurrency_entity_id => p_worksheet_id);
1647
1648
1649 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
1650 p_print_header => FND_API.G_TRUE );
1651 retcode := 2 ;
1652 COMMIT WORK ;
1653 --
1654 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1655 --
1656
1657 PSB_CONCURRENCY_CONTROL_PVT.Release_Concurrency_Control
1658 (p_api_version => 1.0,
1659 p_return_status => l_return_status,
1660 p_concurrency_class => 'WORKSHEET_CREATION',
1661 p_concurrency_entity_name => 'WORKSHEET',
1662 p_concurrency_entity_id => p_worksheet_id);
1663
1664 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
1665 p_print_header => FND_API.G_TRUE );
1666 retcode := 2 ;
1667 COMMIT WORK ;
1668 --
1669 WHEN OTHERS THEN
1670
1671 PSB_CONCURRENCY_CONTROL_PVT.Release_Concurrency_Control
1672 (p_api_version => 1.0,
1673 p_return_status => l_return_status,
1674 p_concurrency_class => 'WORKSHEET_CREATION',
1675 p_concurrency_entity_name => 'WORKSHEET',
1676 p_concurrency_entity_id => p_worksheet_id);
1677
1678 --
1679 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1680 --
1681 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,
1682 l_api_name ) ;
1683 END IF ;
1684 --
1685 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
1686 p_print_header => FND_API.G_TRUE );
1687 --
1688 retcode := 2 ;
1689 COMMIT WORK ;
1690 --
1691 END Apply_Element_Parameters_CP;
1692
1693 /*===========================================================================+
1694 | PROCEDURE Validate_Positions_CP |
1695 +===========================================================================*/
1696 --
1697 -- This is the execution file for the concurrent program 'Validate
1698 -- Positions'
1699 --
1700 PROCEDURE Validate_Positions_CP
1701 (
1702 errbuf OUT NOCOPY VARCHAR2 ,
1703 retcode OUT NOCOPY VARCHAR2 ,
1704 --
1705 p_worksheet_id IN NUMBER
1706 )
1707 IS
1708 --
1709 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Positions_CP';
1710 l_api_version CONSTANT NUMBER := 1.0 ;
1711 --
1712 l_error_api_name VARCHAR2(2000);
1713 l_return_status VARCHAR2(1) ;
1714 l_msg_count NUMBER ;
1715 l_msg_data VARCHAR2(2000) ;
1716 l_msg_index_out NUMBER;
1717 l_validation_status VARCHAR2(1);
1718 l_rep_req_id NUMBER;
1719 l_reqid NUMBER;
1720
1721 BEGIN
1722
1723 PSB_POSITIONS_PVT.Position_WS_Validation
1724 (p_api_version => 1.0,
1725 p_return_status => l_return_status,
1726 p_msg_count => l_msg_count,
1727 p_msg_data => l_msg_data,
1728 p_worksheet_id => p_worksheet_id,
1729 p_validation_status => l_validation_status
1730 );
1731
1732 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1733 raise FND_API.G_EXC_ERROR;
1734 end if;
1735
1736 if l_validation_status <> FND_API.G_RET_STS_SUCCESS THEN
1737 -- submit concurrent request for error report
1738 -- waiting for correction to error messages report
1739 --
1740 l_reqid := FND_GLOBAL.CONC_REQUEST_ID;
1741
1742 l_rep_req_id := Fnd_Request.Submit_Request
1743 (application => 'PSB' ,
1744 program => 'PSBRPERR' ,
1745 description => 'Position Worksheet Exception Report',
1746 start_time => NULL ,
1747 sub_request => FALSE ,
1748 argument1 => 'POSITION_WORKSHEET_EXCEPTION',
1749 argument2 => p_worksheet_id,
1750 argument3 => l_reqid
1751 );
1752 --
1753 if l_rep_req_id = 0 then
1754 --
1755 fnd_message.set_name('PSB', 'PSB_FAIL_TO_SUBMIT_REQUEST');
1756 FND_MSG_PUB.Add;
1757 raise FND_API.G_EXC_ERROR;
1758 --
1759 end if;
1760
1761 fnd_message.set_name('PSB', 'PSB_POSITION_WS_EXCEPTION');
1762 FND_MSG_PUB.Add;
1763
1764 raise FND_API.G_EXC_ERROR;
1765
1766 end if;
1767
1768 PSB_MESSAGE_S.Print_Success;
1769 retcode := 0 ;
1770
1771 COMMIT WORK;
1772
1773 EXCEPTION
1774
1775 WHEN FND_API.G_EXC_ERROR THEN
1776
1777 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
1778 p_print_header => FND_API.G_TRUE );
1779 retcode := 2 ;
1780 COMMIT WORK ;
1781 --
1782 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1783
1784 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
1785 p_print_header => FND_API.G_TRUE );
1786 retcode := 2 ;
1787 COMMIT WORK ;
1788 --
1789 WHEN OTHERS THEN
1790
1791 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1792 --
1793 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,
1794 l_api_name ) ;
1795 END IF ;
1796 --
1797 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
1798 p_print_header => FND_API.G_TRUE );
1799 --
1800 retcode := 2 ;
1801 COMMIT WORK ;
1802 --
1803 END Validate_Positions_CP;
1804
1805 /* ---------------------------------------------------------------------------------- */
1806 --
1807 -- This is the execution file for the concurrent program 'Revise_Position_Projections_CP'
1808 --
1809 PROCEDURE Revise_Position_Projections_CP
1810 (
1811 errbuf OUT NOCOPY VARCHAR2,
1812 retcode OUT NOCOPY VARCHAR2,
1813 p_worksheet_id IN NUMBER,
1814 p_parameter_id IN NUMBER
1815 ) IS
1816 --
1817 l_api_name CONSTANT VARCHAR2(30) := 'Revise_Position_Projections_CP';
1818 l_api_version CONSTANT NUMBER := 1.0 ;
1819 --
1820 l_error_api_name VARCHAR2(2000);
1821 l_return_status VARCHAR2(1);
1822 l_msg_count NUMBER;
1823 l_msg_data VARCHAR2(2000);
1824 l_msg_index_out NUMBER;
1825 --
1826 l_set_cp_status BOOLEAN := FALSE ; -- Bug#4675858
1827 --
1828 BEGIN
1829
1830 PSB_WS_POS_PVT.Revise_Position_Projections
1831 (p_api_version => 1.0,
1832 p_init_msg_list => FND_API.G_TRUE,
1833 p_return_status => l_return_status,
1834 p_msg_count => l_msg_count,
1835 p_msg_data => l_msg_data,
1836 p_worksheet_id => p_worksheet_id,
1837 p_parameter_id => p_parameter_id);
1838
1839 -- Bug#4675858 Start
1840 -- Set CP status to warning if flag is true.
1841 IF ( PSB_WS_ACCT1.g_soft_error_flag )
1842 THEN
1843 --
1844 l_set_cp_status
1845 := FND_CONCURRENT.Set_Completion_Status
1846 ( status => 'WARNING'
1847 , message => NULL
1848 ) ;
1849 --
1850 -- Reset the variable as it has to be checked
1851 -- by many packages.
1852 --
1853 PSB_WS_ACCT1.g_soft_error_flag := FALSE ;
1854 --
1855 END IF ;
1856 -- Bug#4675858 End
1857
1858 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1859 raise FND_API.G_EXC_ERROR;
1860 end if;
1861
1862 PSB_MESSAGE_S.Print_Success;
1863 retcode := 0 ;
1864 --
1865 COMMIT WORK;
1866 --
1867 EXCEPTION
1868
1869 WHEN FND_API.G_EXC_ERROR THEN
1870
1871 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
1872 p_print_header => FND_API.G_TRUE );
1873 retcode := 2 ;
1874 COMMIT WORK ;
1875
1876 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1877
1878 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
1879 p_print_header => FND_API.G_TRUE );
1880 retcode := 2 ;
1881 COMMIT WORK ;
1882
1883 WHEN OTHERS THEN
1884
1885 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1886 --
1887 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,
1888 l_api_name ) ;
1889 END IF ;
1890 --
1891 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
1892 p_print_header => FND_API.G_TRUE );
1893 --
1894 retcode := 2 ;
1895 COMMIT WORK ;
1896 --
1897
1898 END Revise_Position_Projections_CP;
1899
1900 /* ----------------------------------------------------------------------- */
1901 /* Bug No 2482305 Start */
1902
1903 PROCEDURE Revise_Element_Projections_CP
1904 (
1905 errbuf OUT NOCOPY VARCHAR2,
1906 retcode OUT NOCOPY VARCHAR2,
1907 p_worksheet_id IN NUMBER,
1908 p_parameter_id IN NUMBER
1909 ) IS
1910 --
1911 l_api_name CONSTANT VARCHAR2(30) := 'Revise_Element_Projections_CP';
1912 l_api_version CONSTANT NUMBER := 1.0 ;
1913 --
1914 l_error_api_name VARCHAR2(2000);
1915 l_return_status VARCHAR2(1);
1916 l_msg_count NUMBER;
1917 l_msg_data VARCHAR2(2000);
1918 l_msg_index_out NUMBER;
1919 --
1920 l_set_cp_status BOOLEAN := FALSE ; -- Bug#4675858
1921 --
1922 BEGIN
1923
1924 PSB_WS_POS_PVT.Revise_Element_Projections
1925 (p_api_version => 1.0,
1926 p_init_msg_list => FND_API.G_TRUE,
1927 p_return_status => l_return_status,
1928 p_msg_count => l_msg_count,
1929 p_msg_data => l_msg_data,
1930 p_worksheet_id => p_worksheet_id,
1931 p_parameter_id => p_parameter_id);
1932
1933 -- Bug#4675858 Start
1934 -- Set CP status to warning if flag is true.
1935 IF ( PSB_WS_ACCT1.g_soft_error_flag )
1936 THEN
1937 --
1938 l_set_cp_status
1939 := FND_CONCURRENT.Set_Completion_Status
1940 ( status => 'WARNING'
1941 , message => NULL
1942 ) ;
1943 --
1944 -- Reset the variable as it has to be checked
1945 -- by many packages.
1946 --
1947 PSB_WS_ACCT1.g_soft_error_flag := FALSE ;
1948 --
1949 END IF ;
1950 -- Bug#4675858 End
1951
1952 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1953 raise FND_API.G_EXC_ERROR;
1954 end if;
1955
1956 PSB_MESSAGE_S.Print_Success;
1957 retcode := 0 ;
1958 --
1959 COMMIT WORK;
1960 --
1961 EXCEPTION
1962
1963 WHEN FND_API.G_EXC_ERROR THEN
1964
1965 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
1966 p_print_header => FND_API.G_TRUE );
1967 retcode := 2 ;
1968 COMMIT WORK ;
1969
1970 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1971
1972 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
1973 p_print_header => FND_API.G_TRUE );
1974 retcode := 2 ;
1975 COMMIT WORK ;
1976
1977 WHEN OTHERS THEN
1978
1979 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1980 --
1981 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,
1982 l_api_name ) ;
1983 END IF ;
1984 --
1985 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
1986 p_print_header => FND_API.G_TRUE );
1987 --
1988 retcode := 2 ;
1989 COMMIT WORK ;
1990 --
1991
1992 END Revise_Element_Projections_CP;
1993
1994 /* ----------------------------------------------------------------------- */
1995
1996 PROCEDURE Revise_Element_Projections
1997 ( p_api_version IN NUMBER,
1998 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1999 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2000 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
2001 p_return_status OUT NOCOPY VARCHAR2,
2002 p_msg_count OUT NOCOPY NUMBER,
2003 p_msg_data OUT NOCOPY VARCHAR2,
2004 p_worksheet_id IN NUMBER,
2005 p_parameter_id IN NUMBER
2006 ) IS
2007
2008 l_api_name CONSTANT VARCHAR2(30) := 'Revise_Element_Projections';
2009 l_api_version CONSTANT NUMBER := 1.0;
2010
2011 l_return_status VARCHAR2(1);
2012 l_validation_status VARCHAR2(1);
2013
2014 BEGIN
2015
2016 -- Standard call to check for call compatibility.
2017
2018 if not FND_API.Compatible_API_Call (l_api_version,
2019 p_api_version,
2020 l_api_name,
2021 G_PKG_NAME)
2022 then
2023 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2024 end if;
2025
2026
2027 -- Initialize message list if p_init_msg_list is set to TRUE
2028
2029 if FND_API.to_Boolean (p_init_msg_list) then
2030 FND_MSG_PUB.initialize;
2031 end if;
2032
2033 -- Enforce Concurrency Control
2034
2035 PSB_CONCURRENCY_CONTROL_PVT.Enforce_Concurrency_Control
2036 (p_api_version => 1.0,
2037 p_return_status => l_return_status,
2038 p_concurrency_class => 'WORKSHEET_CREATION',
2039 p_concurrency_entity_name => 'WORKSHEET',
2040 p_concurrency_entity_id => p_worksheet_id);
2041
2042 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2043 raise FND_API.G_EXC_ERROR;
2044 end if;
2045
2046 -- Call Private Function
2047
2048 PSB_WS_POS3.Revise_Element_Projections
2049 (p_api_version => 1.0,
2050 p_validation_level => p_validation_level,
2051 p_return_status => l_return_status,
2052 p_worksheet_id => p_worksheet_id,
2053 p_parameter_id => p_parameter_id);
2054
2055 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2056 raise FND_API.G_EXC_ERROR;
2057 end if;
2058
2059 PSB_WS_ACCT2.Create_Rollup_Totals
2060 (p_api_version => 1.0,
2061 p_return_status => l_return_status,
2062 p_worksheet_id => p_worksheet_id);
2063
2064 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2065 raise FND_API.G_EXC_ERROR;
2066 end if;
2067
2068 PSB_WORKSHEET.Apply_Constraints
2069 (p_api_version => 1.0,
2070 p_return_status => l_return_status,
2071 p_validation_status => l_validation_status,
2072 p_worksheet_id => p_worksheet_id);
2073
2074 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2075 raise FND_API.G_EXC_ERROR;
2076 end if;
2077
2078 PSB_CONCURRENCY_CONTROL_PVT.Release_Concurrency_Control
2079 (p_api_version => 1.0,
2080 p_return_status => l_return_status,
2081 p_concurrency_class => 'WORKSHEET_CREATION',
2082 p_concurrency_entity_name => 'WORKSHEET',
2083 p_concurrency_entity_id => p_worksheet_id);
2084
2085 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2086 RAISE FND_API.G_EXC_ERROR;
2087 end if;
2088
2089
2090 -- Standard Check of p_commit
2091
2092 if FND_API.to_Boolean (p_commit) then
2093 commit work;
2094 end if;
2095
2096
2097 -- Standard call to get message count and if count is 1, get message info
2098
2099 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
2100 p_data => p_msg_data);
2101
2102
2103 EXCEPTION
2104
2105 when FND_API.G_EXC_ERROR then
2106 rollback;
2107 p_return_status := FND_API.G_RET_STS_ERROR;
2108 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
2109 p_data => p_msg_data);
2110
2111
2112 when FND_API.G_EXC_UNEXPECTED_ERROR then
2113 rollback;
2114 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2115 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
2116 p_data => p_msg_data);
2117
2118
2119 when OTHERS then
2120 rollback;
2121 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2122
2123 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
2124 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
2125 l_api_name);
2126 end if;
2127
2128 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
2129 p_data => p_msg_data);
2130
2131 END Revise_Element_Projections;
2132
2133 /* Bug No 2482305 End */
2134 /* ----------------------------------------------------------------------- */
2135
2136 END PSB_WS_POS_PVT;