[Home] [Help]
PACKAGE BODY: APPS.PSB_WS_POS3
Source
1 PACKAGE BODY PSB_WS_POS3 AS
2 /* $Header: PSBVWP3B.pls 120.15 2006/04/07 11:18:33 shtripat ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PSB_WS_POS3';
5
6 TYPE TokNameArray IS TABLE OF VARCHAR2(100)
7 INDEX BY BINARY_INTEGER;
8
9 TYPE TokValArray IS TABLE OF VARCHAR2(1000)
10 INDEX BY BINARY_INTEGER;
11
12 -- Number of Message Tokens
13
14 no_msg_tokens NUMBER := 0;
15
16 -- Message Token Name
17
18 msg_tok_names TokNameArray;
19
20 -- Message Token Value
21
22 msg_tok_val TokValArray;
23
24 g_dbug VARCHAR2(1000);
25
26 /* start bug 4104890 */
27 -- variable to hold the global profile option for
28 -- auto increment cost calculation period : PSB_AUTOINC_COST_CALPERIOD
29 g_autoinc_period_profile VARCHAR2(1);
30 g_budget_calendar_id NUMBER;
31 /* End bug 4104890 */
32
33 /* ----------------------------------------------------------------------- */
34 /* */
35 /* Private Function Definition */
36 /* */
37 /* ----------------------------------------------------------------------- */
38
39 PROCEDURE Process_ElemParam_Option
40 ( p_return_status OUT NOCOPY VARCHAR2,
41 p_pay_element_id IN NUMBER,
42 p_start_date IN DATE,
43 p_end_date IN DATE,
44 p_worksheet_id IN NUMBER,
45 p_element_value_type IN VARCHAR2,
46 p_element_value IN NUMBER,
47 p_currency_code IN VARCHAR2
48 );
49
50 PROCEDURE Process_ElemParam_PI
51 ( p_return_status OUT NOCOPY VARCHAR2,
52 p_worksheet_id IN NUMBER,
53 p_currency_code IN VARCHAR2,
54 p_start_date IN DATE,
55 p_end_date IN DATE,
56 p_compound_annually IN VARCHAR2,
57 p_compound_factor IN NUMBER,
58 p_pay_element_id IN NUMBER,
59 p_pay_element_option_id IN NUMBER,
60 p_element_value IN NUMBER
61 );
62
63 PROCEDURE Process_PosParam_PI
64 ( p_return_status OUT NOCOPY VARCHAR2,
65 p_worksheet_id IN NUMBER,
66 p_global_worksheet_id IN NUMBER,
67 p_data_extract_id IN NUMBER,
68 p_position_id IN NUMBER,
69 p_currency_code IN VARCHAR2,
70 p_start_date IN DATE,
71 p_end_date IN DATE,
72 p_compound_annually IN VARCHAR2,
73 p_compound_factor IN NUMBER,
74 p_pay_element_id IN NUMBER,
75 p_pay_element_option_id IN NUMBER,
76 p_element_value IN NUMBER
77 );
78
79 PROCEDURE Process_PosParam_AutoInc_Step
80 ( p_return_status OUT NOCOPY VARCHAR2,
81 p_worksheet_id IN NUMBER,
82 p_data_extract_id IN NUMBER,
83 p_business_group_id IN NUMBER,
84 p_position_id IN NUMBER,
85 p_currency_code IN VARCHAR2,
86 p_start_date IN DATE,
87 p_end_date IN DATE,
88 p_compound_annually IN VARCHAR2,
89 p_compound_factor IN NUMBER := FND_API.G_MISS_NUM,
90 p_increment_type IN VARCHAR2,
91 p_increment_by IN NUMBER
92 );
93
94 PROCEDURE Process_PosCons_Detailed
95 ( p_return_status OUT NOCOPY VARCHAR2,
96 p_constraint_validation_status OUT NOCOPY VARCHAR2,
97 p_worksheet_id IN NUMBER,
98 p_data_extract_id IN NUMBER,
99 p_business_group_id IN NUMBER,
100 p_sp_exists IN VARCHAR2,
101 p_constraint_set_name IN VARCHAR2,
102 p_constraint_threshold IN NUMBER,
103 p_constraint_id IN NUMBER,
104 p_constraint_name IN VARCHAR2,
105 p_fte_constraint IN VARCHAR2,
106 p_budget_year_id IN NUMBER,
107 p_budget_year_name IN VARCHAR2,
108 p_year_start_date IN DATE,
109 p_year_end_date IN DATE,
110 p_currency_code IN VARCHAR2,
111 p_severity_level IN NUMBER
112 );
113
114 PROCEDURE Process_PosCons
115 ( p_return_status OUT NOCOPY VARCHAR2,
116 p_constraint_validation_status OUT NOCOPY VARCHAR2,
117 p_worksheet_id IN NUMBER,
118 p_data_extract_id IN NUMBER,
119 p_business_group_id IN NUMBER,
120 p_sp_exists IN VARCHAR2,
121 p_constraint_set_name IN VARCHAR2,
122 p_constraint_threshold IN NUMBER,
123 p_constraint_id IN NUMBER,
124 p_constraint_name IN VARCHAR2,
125 p_position_line_id IN NUMBER := FND_API.G_MISS_NUM,
126 p_position_id IN NUMBER := FND_API.G_MISS_NUM,
127 p_position_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
128 p_budget_year_id IN NUMBER,
129 p_budget_year_name IN VARCHAR2,
130 p_year_start_date IN DATE,
131 p_year_end_date IN DATE,
132 p_currency_code IN VARCHAR2,
133 p_severity_level IN NUMBER,
134 p_summ_flag IN VARCHAR2
135 );
136
137 PROCEDURE Process_PosCons_Step
138 ( p_return_status OUT NOCOPY VARCHAR2,
139 p_constraint_validation_status OUT NOCOPY VARCHAR2,
140 p_worksheet_id IN NUMBER,
141 p_data_extract_id IN NUMBER,
142 p_business_group_id IN NUMBER,
143 p_sp_exists IN VARCHAR2,
144 p_constraint_set_name IN VARCHAR2,
145 p_constraint_threshold IN NUMBER,
146 p_constraint_id IN NUMBER,
147 p_constraint_name IN VARCHAR2,
148 p_position_line_id IN NUMBER := FND_API.G_MISS_NUM,
149 p_position_id IN NUMBER := FND_API.G_MISS_NUM,
150 p_position_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
151 p_budget_year_id IN NUMBER,
152 p_budget_year_name IN VARCHAR2,
153 p_year_start_date IN DATE,
154 p_year_end_date IN DATE,
155 p_currency_code IN VARCHAR2,
156 p_severity_level IN NUMBER,
157 p_summ_flag IN VARCHAR2,
158 p_pay_element_id IN NUMBER,
159 p_pay_element_option_id IN NUMBER,
160 p_prefix_operator IN VARCHAR2,
161 p_element_value_type IN VARCHAR2,
162 p_element_value IN NUMBER
163 );
164
165 PROCEDURE Process_FTECons
166 ( p_return_status OUT NOCOPY VARCHAR2,
167 p_constraint_validation_status OUT NOCOPY VARCHAR2,
168 p_worksheet_id IN NUMBER,
169 p_data_extract_id IN NUMBER,
170 p_sp_exists IN VARCHAR2,
171 p_constraint_set_name IN VARCHAR2,
172 p_constraint_threshold IN NUMBER,
173 p_constraint_id IN NUMBER,
174 p_constraint_name IN VARCHAR2,
175 p_position_line_id IN NUMBER := FND_API.G_MISS_NUM,
176 p_position_id IN NUMBER := FND_API.G_MISS_NUM,
177 p_position_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
178 p_budget_year_id IN NUMBER,
179 p_budget_year_name IN VARCHAR2,
180 p_currency_code IN VARCHAR2,
181 p_severity_level IN NUMBER,
182 p_summ_flag IN VARCHAR2
183 );
184
185 PROCEDURE Redist_Follow_Salary_Year
186 ( p_return_status OUT NOCOPY VARCHAR2,
187 p_worksheet_id IN NUMBER,
188 p_flex_mapping_set_id IN NUMBER,
189 p_rounding_factor IN NUMBER,
190 p_data_extract_id IN NUMBER,
191 p_business_group_id IN NUMBER,
192 p_flex_code IN NUMBER,
193 p_position_line_id IN NUMBER,
194 p_position_id IN NUMBER,
195 p_position_start_date IN DATE,
196 p_position_end_date IN DATE,
197 p_budget_year_id IN NUMBER,
198 p_year_start_date IN DATE,
199 p_year_end_date IN DATE,
200 p_service_package_id IN NUMBER,
201 p_stage_set_id IN NUMBER,
202 p_start_stage_seq IN NUMBER,
203 p_current_stage_seq IN NUMBER,
204 p_func_currency IN VARCHAR2
205 );
206
207 PROCEDURE message_token
208 ( tokname IN VARCHAR2,
209 tokval IN VARCHAR2
210 );
211
212 PROCEDURE add_message
213 ( appname IN VARCHAR2,
214 msgname IN VARCHAR2
215 );
216
217 /* ----------------------------------------------------------------------- */
218
219 PROCEDURE Apply_Element_Parameters
220 ( p_api_version IN NUMBER,
221 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
222 p_return_status OUT NOCOPY VARCHAR2,
223 p_worksheet_id IN NUMBER,
224 p_global_worksheet IN VARCHAR2,
225 p_budget_group_id IN NUMBER,
226 p_data_extract_id IN NUMBER,
227 p_business_group_id IN NUMBER,
228 p_func_currency IN VARCHAR2,
229 p_budget_calendar_id IN NUMBER,
230 p_parameter_set_id IN NUMBER
231 ) IS
232
233 l_api_name CONSTANT VARCHAR2(30) := 'Apply_Element_Parameters';
234 l_api_version CONSTANT NUMBER := 1.0;
235
236 l_global_worksheet VARCHAR2(1);
237 l_budget_group_id NUMBER;
238 l_data_extract_id NUMBER;
239 l_budget_calendar_id NUMBER;
240 l_parameter_set_id NUMBER;
241
242 l_business_group_id NUMBER;
243 l_func_currency VARCHAR2(10);
244
245 l_compound_annually VARCHAR2(1);
246 l_compound_factor NUMBER;
247
248 l_year_index BINARY_INTEGER;
249
250 l_msg_count NUMBER;
251 l_msg_data VARCHAR2(2000);
252 l_return_status VARCHAR2(1);
253
254 cursor c_WS is
255 select global_worksheet_flag,
256 budget_group_id,
257 nvl(data_extract_id, global_data_extract_id) data_extract_id,
258 budget_calendar_id,
259 nvl(parameter_set_id, global_parameter_set_id) parameter_set_id
260 from PSB_WORKSHEETS_V
261 where worksheet_id = p_worksheet_id;
262
263 cursor c_BG is
264 select nvl(business_group_id, root_business_group_id) business_group_id,
265 nvl(currency_code, root_currency_code) currency_code
266 from PSB_BUDGET_GROUPS_V
267 where budget_group_id = l_budget_group_id;
268
269 cursor c_Parameter (Year_Start_Date DATE,
270 Year_End_Date DATE) is
271 select parameter_id,
272 name,
273 parameter_autoinc_rule,
274 parameter_compound_annually,
275 currency_code,
276 effective_start_date,
277 effective_end_date
278 from PSB_PARAMETER_ASSIGNMENTS_V
279 where data_extract_id = l_data_extract_id
280 and parameter_type = 'ELEMENT'
281 and (((effective_start_date <= Year_End_Date)
282 and (effective_end_date is null))
283 or ((effective_start_date between Year_Start_Date and Year_End_Date)
284 or (effective_end_date between Year_Start_Date and Year_End_Date)
285 or ((effective_start_date < Year_Start_Date)
286 and (effective_end_date > Year_End_Date))))
287 and parameter_set_id = l_parameter_set_id
288 order by effective_start_date,
289 priority;
290
291 BEGIN
292
293 -- Standard call to check for call compatibility
294
295 if not FND_API.Compatible_API_Call (l_api_version,
296 p_api_version,
297 l_api_name,
298 G_PKG_NAME)
299 then
300 raise FND_API.G_EXC_UNEXPECTED_ERROR;
301 end if;
302
303 if ((p_global_worksheet = FND_API.G_MISS_CHAR) or
304 (p_budget_group_id = FND_API.G_MISS_NUM) or
305 (p_data_extract_id = FND_API.G_MISS_NUM) or
306 (p_budget_calendar_id = FND_API.G_MISS_NUM) or
307 (p_parameter_set_id = FND_API.G_MISS_NUM)) then
308 begin
309
310 for c_WS_Rec in c_WS loop
311 l_global_worksheet := c_WS_Rec.global_worksheet_flag;
312 l_budget_group_id := c_WS_Rec.budget_group_id;
313 l_data_extract_id := c_WS_Rec.data_extract_id;
314 l_budget_calendar_id := c_WS_Rec.budget_calendar_id;
315 l_parameter_set_id := c_WS_Rec.parameter_set_id;
316 end loop;
317
318 end;
319 end if;
320
321 if ((l_global_worksheet is null) or (l_global_worksheet = 'N')) then
322 l_global_worksheet := FND_API.G_FALSE;
323 else
324 l_global_worksheet := FND_API.G_TRUE;
325 end if;
326
327 if p_global_worksheet <> FND_API.G_MISS_CHAR then
328 l_global_worksheet := p_global_worksheet;
329 end if;
330
331 if p_budget_group_id <> FND_API.G_MISS_NUM then
332 l_budget_group_id := p_budget_group_id;
333 end if;
334
335 if p_data_extract_id <> FND_API.G_MISS_NUM then
336 l_data_extract_id := p_data_extract_id;
337 end if;
338
339 if p_budget_calendar_id <> FND_API.G_MISS_NUM then
340 l_budget_calendar_id := p_budget_calendar_id;
341 end if;
342
343 if p_parameter_set_id <> FND_API.G_MISS_NUM then
344 l_parameter_set_id := p_parameter_set_id;
345 end if;
346
347 if ((p_business_group_id = FND_API.G_MISS_NUM) or
348 (p_func_currency = FND_API.G_MISS_CHAR)) then
349 begin
350
351 for c_BG_Rec in c_BG loop
352 l_business_group_id := c_BG_Rec.business_group_id;
353 l_func_currency := c_BG_Rec.currency_code;
354 end loop;
355
356 end;
357 end if;
358
359 if p_business_group_id <> FND_API.G_MISS_NUM then
360 l_business_group_id := p_business_group_id;
361 end if;
362
363 if p_func_currency <> FND_API.G_MISS_CHAR then
364 l_func_currency := p_func_currency;
365 end if;
366
367 if l_budget_calendar_id <> nvl(PSB_WS_ACCT1.g_budget_calendar_id, FND_API.G_MISS_NUM) then
368 begin
369
370 PSB_WS_ACCT1.Cache_Budget_Calendar
371 (p_return_status => l_return_status,
372 p_budget_calendar_id => l_budget_calendar_id);
373
374 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
375 raise FND_API.G_EXC_ERROR;
376 end if;
377
378 end;
379 end if;
380
381 for l_year_index in 1..PSB_WS_ACCT1.g_num_budget_years loop
382
383 if PSB_WS_ACCT1.g_budget_years(l_year_index).year_type in ('CY', 'PP') then
384 begin
385
386 for c_Parameter_Rec in c_Parameter (PSB_WS_ACCT1.g_budget_years(l_year_index).start_date,
387 PSB_WS_ACCT1.g_budget_years(l_year_index).end_date) loop
388
389 if ((c_Parameter_Rec.parameter_autoinc_rule is null) or
390 (c_Parameter_Rec.parameter_autoinc_rule = 'N')) then
391 begin
392
393 if ((c_Parameter_Rec.parameter_compound_annually is null) or
394 (c_Parameter_Rec.parameter_compound_annually = 'N')) then
395 l_compound_annually := FND_API.G_FALSE;
396 else
397 l_compound_annually := FND_API.G_TRUE;
398 l_compound_factor := greatest(ceil(months_between(PSB_WS_ACCT1.g_budget_years(l_year_index).start_date,
399 c_Parameter_Rec.effective_start_date) / 12), 0) + 1;
400 end if;
401
402 Process_ElemParam
403 (p_return_status => l_return_status,
404 p_worksheet_id => p_worksheet_id,
405 p_parameter_id => c_Parameter_Rec.parameter_id,
406 p_currency_code => nvl(c_Parameter_Rec.currency_code, l_func_currency),
407 p_start_date => greatest(PSB_WS_ACCT1.g_budget_years(l_year_index).start_date,
408 c_Parameter_Rec.effective_start_date),
409 p_end_date => least(PSB_WS_ACCT1.g_budget_years(l_year_index).end_date,
410 nvl(c_Parameter_Rec.effective_end_date, PSB_WS_ACCT1.g_budget_years(l_year_index).end_date)),
411 p_compound_annually => l_compound_annually,
412 p_compound_factor => l_compound_factor);
413
414 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
415 raise FND_API.G_EXC_ERROR;
416 end if;
417
418 end;
419 else
420 begin
421
422 if FND_API.to_Boolean(l_global_worksheet) then
423 begin
424
425 l_compound_factor := greatest(ceil(months_between(PSB_WS_ACCT1.g_budget_years(l_year_index).start_date,
426 c_Parameter_Rec.effective_start_date) / 12), 0) + 1;
427
428 Process_ElemParam_AutoInc
429 (p_return_status => l_return_status,
430 p_worksheet_id => p_worksheet_id,
431 p_data_extract_id => l_data_extract_id,
432 p_business_group_id => l_business_group_id,
433 p_parameter_id => c_Parameter_Rec.parameter_id,
434 p_currency_code => nvl(c_Parameter_Rec.currency_code, l_func_currency),
435 p_start_date => greatest(PSB_WS_ACCT1.g_budget_years(l_year_index).start_date,
436 c_Parameter_Rec.effective_start_date),
437 p_end_date => least(PSB_WS_ACCT1.g_budget_years(l_year_index).end_date,
438 nvl(c_Parameter_Rec.effective_end_date, PSB_WS_ACCT1.g_budget_years(l_year_index).end_date)),
439 p_compound_factor => l_compound_factor);
440
441 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
442 raise FND_API.G_EXC_ERROR;
443 end if;
444
445 end;
446 end if;
447
448 end;
449 end if;
450
451 end loop;
452
453 end;
454 end if;
455
456 end loop;
457
458
459 -- Initialize API return status to success
460
461 p_return_status := FND_API.G_RET_STS_SUCCESS;
462
463
464 EXCEPTION
465
466 when FND_API.G_EXC_ERROR then
467 p_return_status := FND_API.G_RET_STS_ERROR;
468
469 when FND_API.G_EXC_UNEXPECTED_ERROR then
470 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
471
472 when OTHERS then
473 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
474
475 if FND_MSG_PUB.Check_Msg_Level
476 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
477 then
478 FND_MSG_PUB.Add_Exc_Msg
479 (p_pkg_name => G_PKG_NAME,
480 p_procedure_name => l_api_name);
481 end if;
482
483 END Apply_Element_Parameters;
484
485 /* ----------------------------------------------------------------------- */
486 /* Bug No 2482305 Start */
487
488 PROCEDURE Revise_Element_Projections
489 ( p_api_version IN NUMBER,
490 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
491 p_return_status OUT NOCOPY VARCHAR2,
492 p_worksheet_id IN NUMBER,
493 p_parameter_id IN NUMBER,
494 p_recalculate_flag IN BOOLEAN
495 ) IS
496
497 l_api_name CONSTANT VARCHAR2(30) := 'Revise_Element_Projections';
498 l_api_version CONSTANT NUMBER := 1.0;
499
500 l_global_worksheet VARCHAR2(1);
501 l_global_worksheet_id NUMBER;
502 l_data_extract_id NUMBER;
503 l_budget_calendar_id NUMBER;
504 l_business_group_id NUMBER;
505 l_func_currency VARCHAR2(10);
506
507 l_parameter_name VARCHAR2(30);
508 l_currency_code VARCHAR2(15);
509 l_effective_start_date DATE;
510 l_effective_end_date DATE;
511 l_compound_annually VARCHAR2(1);
512 l_compound_factor NUMBER;
513 l_autoinc_rule VARCHAR2(1);
514
515 l_year_start_date DATE;
516 l_year_end_date DATE;
517
518 l_year_index BINARY_INTEGER;
519
520 l_msg_count NUMBER;
521 l_msg_data VARCHAR2(2000);
522 l_return_status VARCHAR2(1);
523
524 l_position_line_id NUMBER;
525 l_num_positions NUMBER := 0;
526 l_root_budget_group_id NUMBER;
527 l_set_of_books_id NUMBER;
528 l_flex_code NUMBER;
529
530 cursor c_Positions is
531 select pp.position_id, pp.name
532 from PSB_POSITIONS pp
533 where pp.data_extract_id = l_data_extract_id
534 and exists
535 (select 1
536 from PSB_POSITION_ASSIGNMENTS pa,
537 PSB_PARAMETER_FORMULAS pf
538 where pa.position_id = pp.position_id
539 and pa.data_extract_id = pp.data_extract_id
540 and pa.assignment_type = 'ELEMENT'
541 and (pa.worksheet_id is null or pa.worksheet_id = p_worksheet_id)
542 and pf.parameter_id = p_parameter_id
543 and pa.pay_element_id = pf.pay_element_id)
544 and exists
545 (select 1
546 from PSB_WS_POSITION_LINES wpl,
547 PSB_WS_LINES_POSITIONS wlp
548 where wpl.position_line_id = wlp.position_line_id
549 and wlp.worksheet_id = p_worksheet_id
550 and wpl.position_id = pp.position_id);
551
552 cursor c_WS is
553 select a.global_worksheet_flag,
554 nvl(a.global_worksheet_id, a.worksheet_id) global_worksheet_id,
555 nvl(a.data_extract_id, a.global_data_extract_id) data_extract_id,
556 a.budget_calendar_id,
557 nvl(b.business_group_id, b.root_business_group_id) business_group_id,
558 nvl(b.currency_code, b.root_currency_code) currency_code,
559 nvl(b.root_budget_group_id, b.budget_group_id) root_budget_group_id,
560 nvl(b.set_of_books_id, b.root_set_of_books_id) set_of_books_id
561 from PSB_WORKSHEETS_V a,
562 PSB_BUDGET_GROUPS_V b
563 where a.worksheet_id = p_worksheet_id
564 and b.budget_group_id = a.budget_group_id;
565
566 cursor c_Parameter is
567 select name,
568 currency_code,
569 effective_start_date,
570 effective_end_date,
571 parameter_compound_annually,
572 parameter_autoinc_rule
573 from PSB_PARAMETERS_V
574 where parameter_id = p_parameter_id
575 and parameter_type = 'ELEMENT';
576
577 cursor c_SOB is
578 select chart_of_accounts_id
579 from GL_SETS_OF_BOOKS
580 where set_of_books_id = l_set_of_books_id;
581
582 BEGIN
583
584 -- Standard Start of API savepoint
585
586 SAVEPOINT Revise_Element_Projections_Pvt;
587
588 -- Standard call to check for call compatibility
589
590 if not FND_API.Compatible_API_Call (l_api_version,
591 p_api_version,
592 l_api_name,
593 G_PKG_NAME)
594 then
595 raise FND_API.G_EXC_UNEXPECTED_ERROR;
596 end if;
597
598 for c_WS_Rec in c_WS loop
599 l_global_worksheet := c_WS_Rec.global_worksheet_flag;
600 l_global_worksheet_id := c_WS_Rec.global_worksheet_id;
601 l_data_extract_id := c_WS_Rec.data_extract_id;
602 l_budget_calendar_id := c_WS_Rec.budget_calendar_id;
603 l_business_group_id := c_WS_Rec.business_group_id;
604 l_func_currency := c_WS_Rec.currency_code;
605 l_root_budget_group_id := c_WS_Rec.root_budget_group_id;
606 l_set_of_books_id := c_WS_Rec.set_of_books_id;
607 end loop;
608
609 for c_SOB_Rec in c_SOB loop
610 l_flex_code := c_SOB_Rec.chart_of_accounts_id;
611 g_flex_code := l_flex_code ; -- Bug#4675858
612 end loop;
613
614 if ((l_global_worksheet is null) or (l_global_worksheet = 'N')) then
615 l_global_worksheet := FND_API.G_FALSE;
616 else
617 l_global_worksheet := FND_API.G_TRUE;
618 end if;
619
620 if l_budget_calendar_id <> nvl(PSB_WS_ACCT1.g_budget_calendar_id, FND_API.G_MISS_NUM) then
621 begin
622
623 PSB_WS_ACCT1.Cache_Budget_Calendar
624 (p_return_status => l_return_status,
625 p_budget_calendar_id => l_budget_calendar_id);
626
627 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
628 raise FND_API.G_EXC_ERROR;
629 end if;
630
631 end;
632 end if;
633
634 for c_Parameter_rec in c_Parameter loop
635 g_note_parameter_name := c_Parameter_Rec.name; -- Bug#4675858
636 l_parameter_name := c_Parameter_Rec.name;
637 l_currency_code := c_Parameter_Rec.currency_code;
638 l_effective_start_date := c_Parameter_Rec.effective_start_date;
639 l_effective_end_date := c_Parameter_Rec.effective_end_date;
640 l_compound_annually := c_Parameter_Rec.parameter_compound_annually;
641 l_autoinc_rule := c_Parameter_Rec.parameter_autoinc_rule;
642 end loop;
643
644 if ((l_compound_annually is null) or (l_compound_annually = 'N')) then
645 l_compound_annually := FND_API.G_FALSE;
646 else
647 l_compound_annually := FND_API.G_TRUE;
648 end if;
649
650 if ((l_autoinc_rule is null) or (l_autoinc_rule = 'N')) then
651 l_autoinc_rule := FND_API.G_FALSE;
652 else
653 l_autoinc_rule := FND_API.G_TRUE;
654 end if;
655
656 for l_year_index in 1..PSB_WS_ACCT1.g_num_budget_years loop
657
658 if PSB_WS_ACCT1.g_budget_years(l_year_index).year_type in ('CY', 'PP') then
659 begin
660
661 l_year_start_date := PSB_WS_ACCT1.g_budget_years(l_year_index).start_date;
662 l_year_end_date := PSB_WS_ACCT1.g_budget_years(l_year_index).end_date;
663
664 if (((l_effective_start_date <= l_year_end_date) and
665 (l_effective_end_date is null)) or
666 ((l_effective_start_date between l_year_start_date and l_year_end_date) or
667 (l_effective_end_date between l_year_start_date and l_year_end_date) or
668 ((l_effective_start_date < l_year_start_date) and
669 (l_effective_end_date > l_year_end_date)))) then
670 begin
671
672 if NOT FND_API.to_Boolean(l_autoinc_rule) then
673 begin
674
675 if FND_API.to_Boolean(l_compound_annually) then
676 l_compound_factor := greatest(ceil(months_between(l_year_start_date, l_effective_start_date) / 12), 0) + 1;
677 end if;
678
679 Process_ElemParam
680 (p_return_status => l_return_status,
681 p_worksheet_id => p_worksheet_id,
682 p_parameter_id => p_parameter_id,
683 p_currency_code => nvl(l_currency_code, l_func_currency),
684 p_start_date => greatest(l_year_start_date, l_effective_start_date),
685 p_end_date => least(l_year_end_date, nvl(l_effective_end_date, l_year_end_date)),
686 p_compound_annually => l_compound_annually,
687 p_compound_factor => l_compound_factor);
688
689 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
690 raise FND_API.G_EXC_ERROR;
691 end if;
692
693 end;
694 else
695 begin
696
697 if FND_API.to_Boolean(l_global_worksheet) then
698 begin
699
700 l_compound_factor := greatest(ceil(months_between(l_year_start_date, l_effective_start_date) / 12), 0) + 1;
701
702 Process_ElemParam_AutoInc
703 (p_return_status => l_return_status,
704 p_worksheet_id => p_worksheet_id,
705 p_data_extract_id => l_data_extract_id,
706 p_business_group_id => l_business_group_id,
707 p_parameter_id => p_parameter_id,
708 p_currency_code => nvl(l_currency_code, l_func_currency),
709 p_start_date => greatest(l_year_start_date, l_effective_start_date),
710 p_end_date => least(l_year_end_date, nvl(l_effective_end_date, l_year_end_date)),
711 p_compound_factor => l_compound_factor);
712
713 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
714 raise FND_API.G_EXC_ERROR;
715 end if;
716 end;
717 end if;
718
719 end;
720 end if; -- End of autoinc_rule check
721
722 end;
723 end if;
724
725 end;
726 end if;
727
728 end loop;
729
730 for c_positions_rec in c_positions loop
731 begin
732
733 if p_recalculate_flag then
734 begin
735
736 PSB_WS_POS1.g_salary_budget_group_id := null;
737
738 PSB_WS_POS1.Cache_Salary_Dist
739 (p_return_status => l_return_status,
740 p_worksheet_id => l_global_worksheet_id,
741 p_root_budget_group_id => l_root_budget_group_id,
742 p_flex_code => l_flex_code,
743 p_data_extract_id => l_data_extract_id,
744 p_position_id => c_Positions_Rec.position_id,
745 p_position_name => c_Positions_Rec.name,
746 p_start_date => PSB_WS_ACCT1.g_startdate_cy,
747 p_end_date => PSB_WS_ACCT1.g_end_est_date);
748
749 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
750 raise FND_API.G_EXC_ERROR;
751 end if;
752
753 PSB_WS_POS1.Create_Position_Lines
754 (p_api_version => 1.0,
755 p_return_status => l_return_status,
756 p_position_line_id => l_position_line_id,
757 p_worksheet_id => p_worksheet_id,
758 p_position_id => c_Positions_Rec.position_id,
759 p_budget_group_id => PSB_WS_POS1.g_salary_budget_group_id);
760
761 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
762 raise FND_API.G_EXC_ERROR;
763 end if;
764
765 PSB_WS_POS2.Calculate_Position_Cost
766 (p_api_version => 1.0,
767 p_return_status => l_return_status,
768 p_worksheet_id => p_worksheet_id,
769 p_position_line_id => l_position_line_id);
770
771 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
772 raise FND_API.G_EXC_ERROR;
773 end if;
774
775 end;
776 end if;
777
778 l_num_positions := l_num_positions + 1;
779
780 if l_num_positions > PSB_WS_ACCT1.g_checkpoint_save then
781 commit work;
782 l_num_positions := 0;
783 savepoint Revise_Element_Projections_Pvt;
784 end if;
785
786 end;
787 end loop;
788
789 -- Initialize API return status to success
790
791 p_return_status := FND_API.G_RET_STS_SUCCESS;
792
793
794 EXCEPTION
795
796 when FND_API.G_EXC_ERROR then
797 rollback to Revise_Element_Projections_Pvt;
798 p_return_status := FND_API.G_RET_STS_ERROR;
799
800 when FND_API.G_EXC_UNEXPECTED_ERROR then
801 rollback to Revise_Element_Projections_Pvt;
802 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
803
804 when OTHERS then
805 rollback to Revise_Element_Projections_Pvt;
806 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
807
808 if FND_MSG_PUB.Check_Msg_Level
809 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
810 then
811 FND_MSG_PUB.Add_Exc_Msg
812 (p_pkg_name => G_PKG_NAME,
813 p_procedure_name => l_api_name);
814 end if;
815
816 END Revise_Element_Projections;
817
818 /* Bug No 2482305 End */
819 /* ----------------------------------------------------------------------- */
820
821 PROCEDURE Process_ElemParam
822 ( p_return_status OUT NOCOPY VARCHAR2,
823 p_worksheet_id IN NUMBER,
824 p_parameter_id IN NUMBER,
825 p_currency_code IN VARCHAR2,
826 p_start_date IN DATE,
827 p_end_date IN DATE,
828 p_compound_annually IN VARCHAR2,
829 p_compound_factor IN NUMBER
830 ) IS
831
832 l_msg_count NUMBER;
833 l_msg_data VARCHAR2(2000);
834
835 l_element_value NUMBER;
836 l_element_value_type VARCHAR(2);
837
838 l_start_date DATE;
839 l_end_date DATE;
840
841 l_return_status VARCHAR2(1);
842
843 cursor c_Formula is
844 select pay_element_id,
845 pay_element_option_id,
846 element_value_type,
847 element_value,
848 effective_start_date,
849 effective_end_date
850 from PSB_PARAMETER_FORMULAS
851 where parameter_id = p_parameter_id
852 order by step_number;
853
854 BEGIN
855
856 for c_Formula_Rec in c_Formula loop
857
858 l_start_date := greatest(nvl(c_Formula_Rec.effective_start_date, p_start_date), p_start_date);
859 l_end_date := least(nvl(c_Formula_Rec.effective_end_date, p_end_date), p_end_date);
860
861 if ((l_start_date <= l_end_date) and
862 ((l_start_date between p_start_date and p_end_date) or
863 (l_end_date between p_start_date and p_end_date))) then
864 begin
865
866 if c_Formula_Rec.element_value_type = 'PI' then
867 begin
868
869 Process_ElemParam_PI
870 (p_return_status => l_return_status,
871 p_worksheet_id => p_worksheet_id,
872 p_currency_code => p_currency_code,
873 p_start_date => l_start_date,
874 p_end_date => l_end_date,
875 p_compound_annually => p_compound_annually,
876 p_compound_factor => p_compound_factor,
877 p_pay_element_id => c_Formula_Rec.pay_element_id,
878 p_pay_element_option_id => c_Formula_Rec.pay_element_option_id,
879 p_element_value => c_Formula_Rec.element_value);
880
881 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
882 raise FND_API.G_EXC_ERROR;
883 end if;
884
885 end;
886 elsif c_Formula_Rec.element_value_type = 'PS' then
887 begin
888
889 if FND_API.to_Boolean(p_compound_annually) then
890 begin
891
892 /* Bug 3786457 Start */
893 /* if c_Formula_Rec.element_value < 1 then
894 l_element_value := c_Formula_Rec.element_value * POWER(1 + c_Formula_Rec.element_value, p_compound_factor);
895 else
896 l_element_value := c_Formula_Rec.element_value * POWER(1 + c_Formula_Rec.element_value / 100, p_compound_factor);
897 end if; */
898 l_element_value
899 := c_Formula_Rec.element_value * POWER(1 + c_Formula_Rec.element_value / 100, p_compound_factor);
900 /* Bug 3786457 End */
901
902 end;
903 else
904 l_element_value := c_Formula_Rec.element_value;
905 end if;
906
907 l_element_value_type := c_Formula_Rec.element_value_type;
908
909 end;
910 else
911 l_element_value := c_Formula_Rec.element_value;
912 l_element_value_type := c_Formula_Rec.element_value_type;
913 end if;
914
915 if c_Formula_Rec.element_value_type <> 'PI' then
916 begin
917
918 if c_Formula_Rec.pay_element_option_id is null then
919 begin
920
921 Process_ElemParam_Option
922 (p_return_status => l_return_status,
923 p_pay_element_id => c_Formula_Rec.pay_element_id,
924 p_start_date => l_start_date,
925 p_end_date => l_end_date,
926 p_worksheet_id => p_worksheet_id,
927 p_element_value_type => l_element_value_type,
928 p_element_value => l_element_value,
929 p_currency_code => p_currency_code);
930
931 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
932 raise FND_API.G_EXC_ERROR;
933 end if;
934
935 end;
936 else
937 begin
938
939 PSB_PAY_ELEMENT_RATES_PVT.Modify_Element_Rates
940 (p_api_version => 1.0,
941 p_return_status => l_return_status,
942 p_msg_count => l_msg_count,
943 p_msg_data => l_msg_data,
944 p_pay_element_id => c_Formula_Rec.pay_element_id,
945 p_pay_element_option_id => c_Formula_Rec.pay_element_option_id,
946 p_effective_start_date => l_start_date,
947 p_effective_end_date => l_end_date,
948 p_worksheet_id => p_worksheet_id,
949 p_element_value_type => l_element_value_type,
950 p_element_value => l_element_value,
951 p_formula_id => null,
952 p_pay_basis => null,
953 p_maximum_value => null,
954 p_mid_value => null,
955 p_minimum_value => null,
956 p_currency_code => p_currency_code);
957
958 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
959 raise FND_API.G_EXC_ERROR;
960 end if;
961
962 end;
963 end if;
964
965 end;
966 end if;
967
968 end;
969 end if;
970
971 end loop;
972
973
974 -- Initialize API return status to success
975
976 p_return_status := FND_API.G_RET_STS_SUCCESS;
977
978
979 EXCEPTION
980
981 when FND_API.G_EXC_ERROR then
982 p_return_status := FND_API.G_RET_STS_ERROR;
983
984 when FND_API.G_EXC_UNEXPECTED_ERROR then
985 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
986
987 when OTHERS then
988 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
989 if FND_MSG_PUB.Check_Msg_Level
990 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
991 then
992 FND_MSG_PUB.Add_Exc_Msg
993 (p_pkg_name => G_PKG_NAME,
994 p_procedure_name => 'Process_ElemParam');
995 end if;
996
997 END Process_ElemParam;
998
999 /* ----------------------------------------------------------------------- */
1000
1001 PROCEDURE Process_ElemParam_Option
1002 ( p_return_status OUT NOCOPY VARCHAR2,
1003 p_pay_element_id IN NUMBER,
1004 p_start_date IN DATE,
1005 p_end_date IN DATE,
1006 p_worksheet_id IN NUMBER,
1007 p_element_value_type IN VARCHAR2,
1008 p_element_value IN NUMBER,
1009 p_currency_code IN VARCHAR2
1010 ) IS
1011
1012 l_msg_count NUMBER;
1013 l_msg_data VARCHAR2(2000);
1014
1015 l_return_status VARCHAR2(1);
1016 l_option_flag VARCHAR2(1);
1017
1018 cursor c_ElemOption is
1019 select pay_element_option_id
1020 from PSB_PAY_ELEMENT_OPTIONS
1021 where pay_element_id = p_pay_element_id;
1022
1023 BEGIN
1024
1025 l_option_flag := 'N';
1026
1027 for c_ElemOption_Rec in c_ElemOption loop
1028
1029 l_option_flag := 'Y';
1030
1031 PSB_PAY_ELEMENT_RATES_PVT.Modify_Element_Rates
1032 (p_api_version => 1.0,
1033 p_return_status => l_return_status,
1034 p_msg_count => l_msg_count,
1035 p_msg_data => l_msg_data,
1036 p_pay_element_id => p_pay_element_id,
1037 p_pay_element_option_id => c_ElemOption_Rec.pay_element_option_id,
1038 p_effective_start_date => p_start_date,
1039 p_effective_end_date => p_end_date,
1040 p_worksheet_id => p_worksheet_id,
1041 p_element_value_type => p_element_value_type,
1042 p_element_value => p_element_value,
1043 p_formula_id => null,
1044 p_pay_basis => null,
1045 p_maximum_value => null,
1046 p_mid_value => null,
1047 p_minimum_value => null,
1048 p_currency_code => p_currency_code);
1049
1050 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1051 raise FND_API.G_EXC_ERROR;
1052 end if;
1053
1054 end loop;
1055
1056 if (l_option_flag = 'N') then
1057 PSB_PAY_ELEMENT_RATES_PVT.Modify_Element_Rates
1058 (p_api_version => 1.0,
1059 p_return_status => l_return_status,
1060 p_msg_count => l_msg_count,
1061 p_msg_data => l_msg_data,
1062 p_pay_element_id => p_pay_element_id,
1063 p_pay_element_option_id => null,
1064 p_effective_start_date => p_start_date,
1065 p_effective_end_date => p_end_date,
1066 p_worksheet_id => p_worksheet_id,
1067 p_element_value_type => p_element_value_type,
1068 p_element_value => p_element_value,
1069 p_formula_id => null,
1070 p_pay_basis => null,
1071 p_maximum_value => null,
1072 p_mid_value => null,
1073 p_minimum_value => null,
1074 p_currency_code => p_currency_code);
1075
1076 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1077 raise FND_API.G_EXC_ERROR;
1078 end if;
1079
1080 end if;
1081
1082
1083 -- Initialize API return status to success
1084
1085 p_return_status := FND_API.G_RET_STS_SUCCESS;
1086
1087
1088 EXCEPTION
1089
1090 when FND_API.G_EXC_ERROR then
1091 p_return_status := FND_API.G_RET_STS_ERROR;
1092
1093 when FND_API.G_EXC_UNEXPECTED_ERROR then
1094 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1095
1096 when OTHERS then
1097 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1098 if FND_MSG_PUB.Check_Msg_Level
1099 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1100 then
1101 FND_MSG_PUB.Add_Exc_Msg
1102 (p_pkg_name => G_PKG_NAME,
1103 p_procedure_name => 'Process_ElemParam_Option');
1104 end if;
1105
1106 END Process_ElemParam_Option;
1107
1108 /* ----------------------------------------------------------------------- */
1109
1110 PROCEDURE Process_ElemParam_PI
1111 ( p_return_status OUT NOCOPY VARCHAR2,
1112 p_worksheet_id IN NUMBER,
1113 p_currency_code IN VARCHAR2,
1114 p_start_date IN DATE,
1115 p_end_date IN DATE,
1116 p_compound_annually IN VARCHAR2,
1117 p_compound_factor IN NUMBER,
1118 p_pay_element_id IN NUMBER,
1119 p_pay_element_option_id IN NUMBER,
1120 p_element_value IN NUMBER
1121 ) IS
1122
1123 l_msg_count NUMBER;
1124 l_msg_data VARCHAR2(2000);
1125
1126 l_element_value NUMBER;
1127 l_element_value_type VARCHAR(2);
1128
1129 l_return_status VARCHAR2(1);
1130
1131 cursor c_ElemRates is
1132 select pay_element_option_id,
1133 effective_start_date,
1134 effective_end_date,
1135 element_value_type,
1136 element_value,
1137 formula_id,
1138 pay_basis
1139 from PSB_PAY_ELEMENT_RATES
1140 where worksheet_id is null
1141 and currency_code = p_currency_code
1142 and ((p_pay_element_option_id is null)
1143 or (pay_element_option_id = p_pay_element_option_id))
1144 and (((effective_start_date <= p_end_date)
1145 and (effective_end_date is null))
1146 or ((effective_start_date between p_start_date and p_end_date)
1147 or (effective_end_date between p_start_date and p_end_date)
1148 or ((effective_start_date < p_start_date)
1149 and (effective_end_date > p_end_date))))
1150 and pay_element_id = p_pay_element_id;
1151
1152 BEGIN
1153
1154 for c_ElemRates_Rec in c_ElemRates loop
1155
1156 if c_ElemRates_Rec.element_value_type in ('A', 'PS') then
1157 begin
1158
1159 if FND_API.to_Boolean(p_compound_annually) then
1160 begin
1161
1162 /* Bug 3786457 Start */
1163 /* IF p_element_value < 1 THEN
1164 l_element_value := c_ElemRates_Rec.element_value * POWER(1 + p_element_value, p_compound_factor);
1165 ELSE
1166 l_element_value := c_ElemRates_Rec.element_value * POWER(1 + p_element_value / 100, p_compound_factor);
1167 END IF; */
1168 l_element_value
1169 := c_ElemRates_Rec.element_value * POWER(1 + p_element_value / 100, p_compound_factor);
1170 /* Bug 3786457 End */
1171
1172 end;
1173 else
1174 begin
1175
1176 /* Bug 3786457 Start */
1177 /* IF p_element_value < 1 THEN
1178 l_element_value := c_ElemRates_Rec.element_value * (1 + p_element_value);
1179 ELSE
1180 l_element_value := c_ElemRates_Rec.element_value * (1 + p_element_value / 100);
1181 END IF; */
1182 l_element_value
1183 := c_ElemRates_Rec.element_value * (1 + p_element_value / 100);
1184 /* Bug 3786457 End */
1185
1186 end;
1187 end if;
1188
1189 l_element_value_type := c_ElemRates_Rec.element_value_type;
1190
1191 PSB_PAY_ELEMENT_RATES_PVT.Modify_Element_Rates
1192 (p_api_version => 1.0,
1193 p_return_status => l_return_status,
1194 p_msg_count => l_msg_count,
1195 p_msg_data => l_msg_data,
1196 p_pay_element_id => p_pay_element_id,
1197 p_pay_element_option_id => c_ElemRates_Rec.pay_element_option_id,
1198 p_effective_start_date => greatest(c_ElemRates_Rec.effective_start_date, p_start_date),
1199 p_effective_end_date => least(nvl(c_ElemRates_Rec.effective_end_date, p_end_date), p_end_date),
1200 p_worksheet_id => p_worksheet_id,
1201 p_element_value_type => l_element_value_type,
1202 p_element_value => l_element_value,
1203 p_formula_id => null,
1204 p_pay_basis => c_ElemRates_Rec.pay_basis,
1205 p_maximum_value => null,
1206 p_mid_value => null,
1207 p_minimum_value => null,
1208 p_currency_code => p_currency_code);
1209
1210 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1211 raise FND_API.G_EXC_ERROR;
1212 end if;
1213
1214 end;
1215 end if;
1216
1217 end loop;
1218
1219
1220 -- Initialize API return status to success
1221
1222 p_return_status := FND_API.G_RET_STS_SUCCESS;
1223
1224
1225 EXCEPTION
1226
1227 when FND_API.G_EXC_ERROR then
1228 p_return_status := FND_API.G_RET_STS_ERROR;
1229
1230 when FND_API.G_EXC_UNEXPECTED_ERROR then
1231 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1232
1233 when OTHERS then
1234 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1235 if FND_MSG_PUB.Check_Msg_Level
1236 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1237 then
1238 FND_MSG_PUB.Add_Exc_Msg
1239 (p_pkg_name => G_PKG_NAME,
1240 p_procedure_name => 'Process_ElemParam_PI');
1241 end if;
1242
1243 END Process_ElemParam_PI;
1244
1245 /* ----------------------------------------------------------------------- */
1246
1247 PROCEDURE Process_ElemParam_AutoInc
1248 ( p_return_status OUT NOCOPY VARCHAR2,
1249 p_worksheet_id IN NUMBER,
1250 p_data_extract_id IN NUMBER,
1251 p_business_group_id IN NUMBER,
1252 p_parameter_id IN NUMBER,
1253 p_currency_code IN VARCHAR2,
1254 p_start_date IN DATE,
1255 p_end_date IN DATE,
1256 p_compound_factor IN NUMBER
1257 ) IS
1258
1259 l_increment_by NUMBER;
1260 l_increment_type VARCHAR2(1);
1261
1262 l_msg_count NUMBER;
1263 l_msg_data VARCHAR2(2000);
1264
1265 l_element_value NUMBER;
1266 l_maximum_value NUMBER;
1267 l_mid_value NUMBER;
1268 l_minimum_value NUMBER;
1269
1270 l_return_status VARCHAR2(1);
1271
1272 cursor c_Formula is
1273 select increment_by,
1274 increment_type
1275 from PSB_PARAMETER_FORMULAS
1276 where parameter_id = p_parameter_id;
1277
1278 cursor c_ElemRates is
1279 select a.pay_element_id,
1280 a.pay_element_option_id,
1281 a.effective_start_date,
1282 a.effective_end_date,
1283 a.element_value_type,
1284 a.element_value,
1285 a.formula_id,
1286 a.pay_basis,
1287 a.maximum_value,
1288 a.mid_value,
1289 a.minimum_value
1290 from PSB_PAY_ELEMENT_RATES a,
1291 PSB_PAY_ELEMENTS b
1292 where a.worksheet_id is null
1293 and a.currency_code = p_currency_code
1294 and (((a.effective_start_date <= p_end_date)
1295 and (a.effective_end_date is null))
1296 or ((a.effective_start_date between p_start_date and p_end_date)
1297 or (a.effective_end_date between p_start_date and p_end_date)
1298 or ((a.effective_start_date < p_start_date)
1299 and (a.effective_end_date > p_end_date))))
1300 and a.pay_element_id = b.pay_element_id
1301 and b.salary_flag = 'Y'
1302 and b.processing_type = 'R'
1303 and b.business_group_id = p_business_group_id
1304 and b.data_extract_id = p_data_extract_id;
1305
1306 BEGIN
1307
1308 for c_Formula_Rec in c_Formula loop
1309 l_increment_by := c_Formula_Rec.increment_by;
1310 l_increment_type := c_Formula_Rec.increment_type;
1311 end loop;
1312
1313 for c_ElemRates_Rec in c_ElemRates loop
1314
1315 if l_increment_type = 'A' then
1316 begin
1317
1318 if c_ElemRates_Rec.element_value_type = 'A' then
1319 begin
1320
1321 l_element_value := c_ElemRates_Rec.element_value + l_increment_by * p_compound_factor;
1322 l_maximum_value := c_ElemRates_Rec.maximum_value + l_increment_by * p_compound_factor;
1323 l_mid_value := c_ElemRates_Rec.mid_value + l_increment_by * p_compound_factor;
1324 l_minimum_value := c_ElemRates_Rec.minimum_value + l_increment_by * p_compound_factor;
1325
1326 PSB_PAY_ELEMENT_RATES_PVT.Modify_Element_Rates
1327 (p_api_version => 1.0,
1328 p_return_status => l_return_status,
1329 p_msg_count => l_msg_count,
1330 p_msg_data => l_msg_data,
1331 p_pay_element_id => c_ElemRates_Rec.pay_element_id,
1332 p_pay_element_option_id => c_ElemRates_Rec.pay_element_option_id,
1333 p_effective_start_date => greatest(c_ElemRates_Rec.effective_start_date, p_start_date),
1334 p_effective_end_date => least(nvl(c_ElemRates_Rec.effective_end_date, p_end_date), p_end_date),
1335 p_worksheet_id => p_worksheet_id,
1336 p_element_value_type => c_ElemRates_Rec.element_value_type,
1337 p_element_value => l_element_value,
1338 p_formula_id => c_ElemRates_Rec.formula_id,
1339 p_pay_basis => c_ElemRates_Rec.pay_basis,
1340 p_maximum_value => l_maximum_value,
1341 p_mid_value => l_mid_value,
1342 p_minimum_value => l_minimum_value,
1343 p_currency_code => p_currency_code);
1344
1345 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1346 raise FND_API.G_EXC_ERROR;
1347 end if;
1348
1349 end;
1350 end if;
1351
1352 end;
1353 elsif l_increment_type = 'P' then
1354 begin
1355
1356 if c_ElemRates_Rec.element_value_type = 'A' then
1357 begin
1358
1359 /* Bug 2820755 Start */
1360
1361 /* if l_increment_by < 1 then
1362 l_element_value := c_ElemRates_Rec.element_value * POWER(1 + l_increment_by, p_compound_factor);
1363 l_maximum_value := c_ElemRates_Rec.maximum_value * POWER(1 + l_increment_by, p_compound_factor);
1364 l_mid_value := c_ElemRates_Rec.mid_value * POWER(1 + l_increment_by, p_compound_factor);
1365 l_minimum_value := c_ElemRates_Rec.minimum_value * POWER(1 + l_increment_by, p_compound_factor);
1366 else
1367 l_element_value := c_ElemRates_Rec.element_value * POWER(1 + l_increment_by / 100, p_compound_factor);
1368 l_maximum_value := c_ElemRates_Rec.maximum_value * POWER(1 + l_increment_by / 100, p_compound_factor);
1369 l_mid_value := c_ElemRates_Rec.mid_value * POWER(1 + l_increment_by / 100, p_compound_factor);
1370 l_minimum_value := c_ElemRates_Rec.minimum_value * POWER(1 + l_increment_by / 100, p_compound_factor);
1371 end if; */
1372
1373 l_element_value
1374 := c_ElemRates_Rec.element_value * POWER(1 + l_increment_by / 100, p_compound_factor);
1375 l_maximum_value
1376 := c_ElemRates_Rec.maximum_value * POWER(1 + l_increment_by / 100, p_compound_factor);
1377 l_mid_value
1378 := c_ElemRates_Rec.mid_value * POWER(1 + l_increment_by / 100, p_compound_factor);
1379 l_minimum_value
1380 := c_ElemRates_Rec.minimum_value * POWER(1 + l_increment_by / 100, p_compound_factor);
1381 /* Bug 2820755 End */
1382
1383 PSB_PAY_ELEMENT_RATES_PVT.Modify_Element_Rates
1384 (p_api_version => 1.0,
1385 p_return_status => l_return_status,
1386 p_msg_count => l_msg_count,
1387 p_msg_data => l_msg_data,
1388 p_pay_element_id => c_ElemRates_Rec.pay_element_id,
1389 p_pay_element_option_id => c_ElemRates_Rec.pay_element_option_id,
1390 p_effective_start_date => greatest(c_ElemRates_Rec.effective_start_date, p_start_date),
1391 p_effective_end_date => least(nvl(c_ElemRates_Rec.effective_end_date, p_end_date), p_end_date),
1392 p_worksheet_id => p_worksheet_id,
1393 p_element_value_type => c_ElemRates_Rec.element_value_type,
1394 p_element_value => l_element_value,
1395 p_formula_id => c_ElemRates_Rec.formula_id,
1396 p_pay_basis => c_ElemRates_Rec.pay_basis,
1397 p_maximum_value => l_maximum_value,
1398 p_mid_value => l_mid_value,
1399 p_minimum_value => l_minimum_value,
1400 p_currency_code => p_currency_code);
1401
1402 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1403 raise FND_API.G_EXC_ERROR;
1404 end if;
1405
1406 end;
1407 end if;
1408
1409 end;
1410 end if;
1411
1412 end loop;
1413
1414
1415 -- Initialize API return status to success
1416
1417 p_return_status := FND_API.G_RET_STS_SUCCESS;
1418
1419
1420 EXCEPTION
1421
1422 when FND_API.G_EXC_ERROR then
1423 p_return_status := FND_API.G_RET_STS_ERROR;
1424
1425 when FND_API.G_EXC_UNEXPECTED_ERROR then
1426 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1427
1428 when OTHERS then
1429 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1430 if FND_MSG_PUB.Check_Msg_Level
1431 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1432 then
1433 FND_MSG_PUB.Add_Exc_Msg
1434 (p_pkg_name => G_PKG_NAME,
1435 p_procedure_name => 'Process_ElemParam_AutoInc');
1436 end if;
1437
1438 END Process_ElemParam_AutoInc;
1439
1440 /* ----------------------------------------------------------------------- */
1441
1442 FUNCTION Position_Exists
1443 ( p_event_type VARCHAR2,
1444 p_worksheet_id NUMBER,
1445 p_position_id NUMBER) RETURN BOOLEAN IS
1446
1447 l_position_exists BOOLEAN := FALSE;
1448
1449 cursor c_WS is
1450 select 'Exists'
1451 from dual
1452 where exists
1453 (select 1 from PSB_WS_LINES_POSITIONS wlp, PSB_WS_POSITION_LINES wpl
1454 where wlp.worksheet_id = p_worksheet_id
1455 and wpl.position_line_id = wlp.position_line_id
1456 and wpl.position_id = p_position_id);
1457
1458 cursor c_BR is
1459 select 'Exists'
1460 from dual
1461 where exists
1462 (select 1 from PSB_BUDGET_REVISION_POS_LINES brpl, PSB_BUDGET_REVISION_POSITIONS brp
1463 where brpl.budget_revision_id = p_worksheet_id
1464 and brp.budget_revision_pos_line_id = brpl.budget_revision_pos_line_id
1465 and brp.position_id = p_position_id);
1466
1467 BEGIN
1468
1469 if p_event_type = 'BP' then
1470 for c_WS_Rec in c_WS loop
1471 l_position_exists := TRUE;
1472 end loop;
1473 elsif p_event_type = 'BR' then
1474 for c_BR_Rec in c_BR loop
1475 l_position_exists := TRUE;
1476 end loop;
1477 end if;
1478
1479 return l_position_exists;
1480
1481 END Position_Exists;
1482
1483 /* ----------------------------------------------------------------------- */
1484
1485 PROCEDURE Revise_Position_Projections
1486 ( p_api_version IN NUMBER,
1487 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
1488 p_return_status OUT NOCOPY VARCHAR2,
1489 x_msg_data OUT NOCOPY VARCHAR2,
1490 x_msg_count OUT NOCOPY NUMBER,
1491 p_worksheet_id IN NUMBER,
1492 p_parameter_id IN NUMBER
1493 ) IS
1494
1495 l_api_name CONSTANT VARCHAR2(30) := 'Revise_Position_Projections';
1496 l_api_version CONSTANT NUMBER := 1.0;
1497
1498 l_global_worksheet VARCHAR2(1);
1499 l_global_worksheet_id NUMBER;
1500 l_data_extract_id NUMBER;
1501 l_budget_calendar_id NUMBER;
1502 l_business_group_id NUMBER;
1503 l_func_currency VARCHAR2(10);
1504
1505 l_parameter_name VARCHAR2(30);
1506 l_currency_code VARCHAR2(15);
1507 l_effective_start_date DATE;
1508 l_effective_end_date DATE;
1509 l_compound_annually VARCHAR2(1);
1510 l_compound_factor NUMBER;
1511 l_autoinc_rule VARCHAR2(1);
1512
1513 l_year_start_date DATE;
1514 l_year_end_date DATE;
1515
1516 l_msg_count NUMBER;
1517 l_msg_data VARCHAR2(2000);
1518 l_return_status VARCHAR2(1);
1519
1520 cursor c_WS is
1521 select a.global_worksheet_flag,
1522 nvl(a.global_worksheet_id, a.worksheet_id) global_worksheet_id,
1523 nvl(a.data_extract_id, a.global_data_extract_id) data_extract_id,
1524 a.budget_calendar_id,
1525 nvl(b.business_group_id, b.root_business_group_id) business_group_id,
1526 nvl(b.currency_code, b.root_currency_code) currency_code
1527 from PSB_WORKSHEETS_V a,
1528 PSB_BUDGET_GROUPS_V b
1529 where a.worksheet_id = p_worksheet_id
1530 and b.budget_group_id = a.budget_group_id;
1531
1532 cursor c_Parameter is
1533 select name,
1534 currency_code,
1535 effective_start_date,
1536 effective_end_date,
1537 parameter_compound_annually,
1538 parameter_autoinc_rule
1539 from PSB_PARAMETERS_V
1540 where parameter_id = p_parameter_id
1541 and parameter_type = 'POSITION';
1542
1543 BEGIN
1544
1545 -- Standard call to check for call compatibility
1546
1547 if not FND_API.Compatible_API_Call (l_api_version,
1548 p_api_version,
1549 l_api_name,
1550 G_PKG_NAME)
1551 then
1552 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1553 end if;
1554
1555 /* start bug 4104890*/
1556 -- this is applied for local parameters
1557 FND_PROFILE.GET('PSB_AUTOINC_COST_CALPERIOD', g_autoinc_period_profile);
1558 /* end bug 4104890*/
1559
1560
1561 for c_WS_Rec in c_WS loop
1562 l_global_worksheet := c_WS_Rec.global_worksheet_flag;
1563 l_global_worksheet_id := c_WS_Rec.global_worksheet_id;
1564 l_data_extract_id := c_WS_Rec.data_extract_id;
1565 l_budget_calendar_id := c_WS_Rec.budget_calendar_id;
1566 l_business_group_id := c_WS_Rec.business_group_id;
1567 l_func_currency := c_WS_Rec.currency_code;
1568 end loop;
1569
1570 /* start bug 4104890 */
1571 -- get the calendar Id
1572 g_budget_calendar_id := l_budget_calendar_id;
1573 /* end bug 4104890 */
1574
1575 if ((l_global_worksheet is null) or (l_global_worksheet = 'N')) then
1576 l_global_worksheet := FND_API.G_FALSE;
1577 else
1578 l_global_worksheet := FND_API.G_TRUE;
1579 end if;
1580
1581 if l_budget_calendar_id <> nvl(PSB_WS_ACCT1.g_budget_calendar_id, FND_API.G_MISS_NUM) then
1582 begin
1583
1584 PSB_WS_ACCT1.Cache_Budget_Calendar
1585 (p_return_status => l_return_status,
1586 p_budget_calendar_id => l_budget_calendar_id);
1587
1588 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1589 raise FND_API.G_EXC_ERROR;
1590 end if;
1591
1592 end;
1593 end if;
1594
1595 if nvl(PSB_WS_POS1.g_attr_busgrp_id, FND_API.G_MISS_NUM) <> l_business_group_id then
1596 begin
1597
1598 PSB_WS_POS1.Cache_Named_Attributes
1599 (p_return_status => l_return_status,
1600 p_business_group_id => l_business_group_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 end;
1607 end if;
1608
1609 for c_Parameter_rec in c_Parameter loop
1610 l_parameter_name := c_Parameter_Rec.name;
1611 l_currency_code := c_Parameter_Rec.currency_code;
1612 l_effective_start_date := c_Parameter_Rec.effective_start_date;
1613 l_effective_end_date := c_Parameter_Rec.effective_end_date;
1614 l_compound_annually := c_Parameter_Rec.parameter_compound_annually;
1615 l_autoinc_rule := c_Parameter_Rec.parameter_autoinc_rule;
1616 end loop;
1617
1618 if ((l_compound_annually is null) or (l_compound_annually = 'N')) then
1619 l_compound_annually := FND_API.G_FALSE;
1620 else
1621 l_compound_annually := FND_API.G_TRUE;
1622 end if;
1623
1624 if ((l_autoinc_rule is null) or (l_autoinc_rule = 'N')) then
1625 l_autoinc_rule := FND_API.G_FALSE;
1626 else
1627 l_autoinc_rule := FND_API.G_TRUE;
1628 end if;
1629
1630 /* Bug No 2482305 Start */
1631 -- added NOT in the condition
1632
1633 if not FND_API.to_Boolean(l_autoinc_rule) then
1634 /* Bug No 2482305 End */
1635 begin
1636
1637 for l_year_index in 1..PSB_WS_ACCT1.g_num_budget_years loop
1638
1639 if PSB_WS_ACCT1.g_budget_years(l_year_index).year_type in ('CY', 'PP') then
1640 begin
1641
1642 l_year_start_date := PSB_WS_ACCT1.g_budget_years(l_year_index).start_date;
1643 l_year_end_date := PSB_WS_ACCT1.g_budget_years(l_year_index).end_date;
1644
1645 if (((l_effective_start_date <= l_year_end_date) and
1646 (l_effective_end_date is null)) or
1647 ((l_effective_start_date between l_year_start_date and l_year_end_date) or
1648 (l_effective_end_date between l_year_start_date and l_year_end_date) or
1649 ((l_effective_start_date < l_year_start_date) and
1650 (l_effective_end_date > l_year_end_date)))) then
1651 begin
1652
1653 if FND_API.to_Boolean(l_compound_annually) then
1654 l_compound_factor := greatest(ceil(months_between(l_year_start_date, l_effective_start_date) / 12), 0) + 1;
1655 end if;
1656
1657
1658 Process_PosParam_Detailed
1659 (p_return_status => l_return_status,
1660 p_event_type => 'BP',
1661 p_local_parameter => 'Y',
1662 p_worksheet_id => p_worksheet_id,
1663 p_global_worksheet_id => l_global_worksheet_id,
1664 p_global_worksheet => l_global_worksheet,
1665 p_data_extract_id => l_data_extract_id,
1666 p_business_group_id => l_business_group_id,
1667 p_parameter_id => p_parameter_id,
1668 p_parameter_start_date => l_effective_start_date,
1669 p_compound_annually => l_compound_annually,
1670 p_compound_factor => l_compound_factor,
1671 /* Bug No 2482305 Start */
1672 -- p_parameter_autoinc_rule => 'Y',
1673 p_parameter_autoinc_rule => 'N',
1674 /* Bug No 2482305 End */
1675 p_currency_code => nvl(l_currency_code, l_func_currency),
1676 p_start_date => greatest(l_year_start_date, l_effective_start_date),
1677 p_end_date => least(l_year_end_date, nvl(l_effective_end_date, l_year_end_date)),
1678 p_recalculate_flag => TRUE);
1679
1680 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1681 raise FND_API.G_EXC_ERROR;
1682 end if;
1683
1684 end;
1685 end if;
1686
1687 end;
1688 end if;
1689
1690 end loop;
1691
1692 end;
1693 else
1694 begin
1695
1696 -- Process all the non-Autoincrement rules
1697 Process_PosParam_Detailed
1698 (p_return_status => l_return_status,
1699 p_event_type => 'BP',
1700 p_local_parameter => 'Y',
1701 p_worksheet_id => p_worksheet_id,
1702 p_global_worksheet_id => l_global_worksheet_id,
1703 p_global_worksheet => l_global_worksheet,
1704 p_data_extract_id => l_data_extract_id,
1705 p_business_group_id => l_business_group_id,
1706 p_parameter_id => p_parameter_id,
1707 p_parameter_start_date => l_effective_start_date,
1708 p_compound_annually => l_compound_annually,
1709 /* Bug No 2482305 Start */
1710 -- p_parameter_autoinc_rule => 'N',
1711 p_parameter_autoinc_rule => 'Y',
1712 /* Bug No 2482305 End */
1713 p_currency_code => nvl(l_currency_code, l_func_currency),
1714 p_start_date => greatest(PSB_WS_ACCT1.g_startdate_cy, l_effective_start_date),
1715 p_end_date => least(PSB_WS_ACCT1.g_end_est_date, nvl(l_effective_end_date, PSB_WS_ACCT1.g_end_est_date)),
1716 p_recalculate_flag => TRUE);
1717
1718 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1719 raise FND_API.G_EXC_ERROR;
1720 end if;
1721
1722 end;
1723 end if;
1724
1725 g_note_parameter_name := null;
1726
1727 -- Initialize API return status to success
1728
1729 p_return_status := FND_API.G_RET_STS_SUCCESS;
1730
1731
1732 EXCEPTION
1733
1734 when FND_API.G_EXC_ERROR then
1735 p_return_status := FND_API.G_RET_STS_ERROR;
1736 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
1737 p_data => x_msg_data);
1738
1739
1740 when FND_API.G_EXC_UNEXPECTED_ERROR then
1741 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1742 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
1743 p_data => x_msg_data);
1744
1745 when OTHERS then
1746 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1747
1748 if FND_MSG_PUB.Check_Msg_Level
1749 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1750 then
1751 FND_MSG_PUB.Add_Exc_Msg
1752 (p_pkg_name => G_PKG_NAME,
1753 p_procedure_name => l_api_name);
1754 end if;
1755
1756 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
1757 p_data => x_msg_data);
1758
1759 END Revise_Position_Projections;
1760
1761 /* ----------------------------------------------------------------------- */
1762
1763 PROCEDURE Apply_Position_Parameters
1764 ( p_api_version IN NUMBER,
1765 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
1766 p_return_status OUT NOCOPY VARCHAR2,
1767 p_worksheet_id IN NUMBER,
1768 p_global_worksheet IN VARCHAR2 := FND_API.G_MISS_CHAR,
1769 p_budget_group_id IN NUMBER := FND_API.G_MISS_NUM,
1770 p_data_extract_id IN NUMBER := FND_API.G_MISS_NUM,
1771 p_business_group_id IN NUMBER := FND_API.G_MISS_NUM,
1772 p_func_currency IN VARCHAR2 := FND_API.G_MISS_CHAR,
1773 p_budget_calendar_id IN NUMBER := FND_API.G_MISS_NUM,
1774 p_parameter_set_id IN NUMBER := FND_API.G_MISS_NUM
1775 ) IS
1776
1777 l_api_name CONSTANT VARCHAR2(30) := 'Apply_Position_Parameters';
1778 l_api_version CONSTANT NUMBER := 1.0;
1779
1780 l_global_worksheet VARCHAR2(1);
1781 l_budget_group_id NUMBER;
1782 l_data_extract_id NUMBER;
1783 l_budget_calendar_id NUMBER;
1784 l_parameter_set_id NUMBER;
1785
1786 l_business_group_id NUMBER;
1787 l_func_currency VARCHAR2(10);
1788
1789 l_compound_annually VARCHAR2(1);
1790 l_compound_factor NUMBER;
1791
1792 l_msg_count NUMBER;
1793 l_msg_data VARCHAR2(2000);
1794 l_return_status VARCHAR2(1);
1795
1796 cursor c_WS is
1797 select global_worksheet_flag,
1798 budget_group_id,
1799 nvl(data_extract_id, global_data_extract_id) data_extract_id,
1800 budget_calendar_id,
1801 nvl(parameter_set_id, global_parameter_set_id) parameter_set_id
1802 from PSB_WORKSHEETS_V
1803 where worksheet_id = p_worksheet_id;
1804
1805 cursor c_BG is
1806 select nvl(business_group_id, root_business_group_id) business_group_id,
1807 nvl(currency_code, root_currency_code) currency_code
1808 from PSB_BUDGET_GROUPS_V
1809 where budget_group_id = l_budget_group_id;
1810
1811 cursor c_Parameter (Year_Start_Date DATE,
1812 Year_End_Date DATE) is
1813 select parameter_id,
1814 name,
1815 parameter_compound_annually,
1816 currency_code,
1817 effective_start_date,
1818 effective_end_date
1819 from PSB_PARAMETER_ASSIGNMENTS_V
1820 where parameter_autoinc_rule = 'N'
1821 and data_extract_id = l_data_extract_id
1822 and parameter_type = 'POSITION'
1823 and (((effective_start_date <= Year_End_Date)
1824 and (effective_end_date is null))
1825 or ((effective_start_date between Year_Start_Date and Year_End_Date)
1826 or (effective_end_date between Year_Start_Date and Year_End_Date)
1827 or ((effective_start_date < Year_Start_Date)
1828 and (effective_end_date > Year_End_Date))))
1829 and parameter_set_id = l_parameter_set_id
1830 order by effective_start_date,
1831 priority;
1832
1833 cursor c_ParamAutoInc (Start_Date DATE,
1834 End_Date DATE) is
1835 select parameter_id,
1836 name,
1837 parameter_compound_annually,
1838 currency_code,
1839 effective_start_date,
1840 effective_end_date
1841 from PSB_PARAMETER_ASSIGNMENTS_V
1842 where parameter_autoinc_rule = 'Y'
1843 and data_extract_id = l_data_extract_id
1844 and parameter_type = 'POSITION'
1845 and (((effective_start_date <= End_Date)
1846 and (effective_end_date is null))
1847 or ((effective_start_date between Start_Date and End_Date)
1848 or (effective_end_date between Start_Date and End_Date)
1849 or ((effective_start_date < Start_Date)
1850 and (effective_end_date > End_Date))))
1851 and parameter_set_id = l_parameter_set_id
1852 order by effective_start_date,
1853 priority;
1854
1855 BEGIN
1856
1857 -- Standard call to check for call compatibility
1858
1859 if not FND_API.Compatible_API_Call (l_api_version,
1860 p_api_version,
1861 l_api_name,
1862 G_PKG_NAME)
1863 then
1864 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1865 end if;
1866
1867 /* start bug 4104890*/
1868 FND_PROFILE.GET('PSB_AUTOINC_COST_CALPERIOD', g_autoinc_period_profile);
1869 /* end bug 4104890*/
1870
1871 if ((p_global_worksheet = FND_API.G_MISS_CHAR) or
1872 (p_budget_group_id = FND_API.G_MISS_NUM) or
1873 (p_data_extract_id = FND_API.G_MISS_NUM) or
1874 (p_budget_calendar_id = FND_API.G_MISS_NUM) or
1875 (p_parameter_set_id = FND_API.G_MISS_NUM)) then
1876 begin
1877
1878 for c_WS_Rec in c_WS loop
1879 l_global_worksheet := c_WS_Rec.global_worksheet_flag;
1880 l_budget_group_id := c_WS_Rec.budget_group_id;
1881 l_data_extract_id := c_WS_Rec.data_extract_id;
1882 l_budget_calendar_id := c_WS_Rec.budget_calendar_id;
1883 l_parameter_set_id := c_WS_Rec.parameter_set_id;
1884 end loop;
1885
1886 end;
1887 end if;
1888
1889 /* start bug 4104890 */
1890 IF (p_budget_calendar_id = FND_API.G_MISS_NUM) THEN
1891 g_budget_calendar_id := l_budget_calendar_id;
1892 ELSE
1893 g_budget_calendar_id := p_budget_calendar_id;
1894 END IF;
1895 /* end bug 4104890 */
1896
1897
1898 if ((l_global_worksheet is null) or (l_global_worksheet = 'N')) then
1899 l_global_worksheet := FND_API.G_FALSE;
1900 else
1901 l_global_worksheet := FND_API.G_TRUE;
1902 end if;
1903
1904 if p_global_worksheet <> FND_API.G_MISS_CHAR then
1905 l_global_worksheet := p_global_worksheet;
1906 end if;
1907
1908 if p_budget_group_id <> FND_API.G_MISS_NUM then
1909 l_budget_group_id := p_budget_group_id;
1910 end if;
1911
1912 if p_data_extract_id <> FND_API.G_MISS_NUM then
1913 l_data_extract_id := p_data_extract_id;
1914 end if;
1915
1916 if p_budget_calendar_id <> FND_API.G_MISS_NUM then
1917 l_budget_calendar_id := p_budget_calendar_id;
1918 end if;
1919
1920 if p_parameter_set_id <> FND_API.G_MISS_NUM then
1921 l_parameter_set_id := p_parameter_set_id;
1922 end if;
1923
1924 if ((p_business_group_id = FND_API.G_MISS_NUM) or
1925 (p_func_currency = FND_API.G_MISS_CHAR)) then
1926 begin
1927
1928 for c_BG_Rec in c_BG loop
1929 l_business_group_id := c_BG_Rec.business_group_id;
1930 l_func_currency := c_BG_Rec.currency_code;
1931 end loop;
1932
1933 end;
1934 end if;
1935
1936 if p_business_group_id <> FND_API.G_MISS_NUM then
1937 l_business_group_id := p_business_group_id;
1938 end if;
1939
1940 if p_func_currency <> FND_API.G_MISS_CHAR then
1941 l_func_currency := p_func_currency;
1942 end if;
1943
1944 if l_budget_calendar_id <> nvl(PSB_WS_ACCT1.g_budget_calendar_id, FND_API.G_MISS_NUM) then
1945 begin
1946
1947 PSB_WS_ACCT1.Cache_Budget_Calendar
1948 (p_return_status => l_return_status,
1949 p_budget_calendar_id => l_budget_calendar_id);
1950
1951 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1952 raise FND_API.G_EXC_ERROR;
1953 end if;
1954
1955 end;
1956 end if;
1957
1958 if nvl(PSB_WS_POS1.g_attr_busgrp_id, FND_API.G_MISS_NUM) <> l_business_group_id then
1959 begin
1960
1961 PSB_WS_POS1.Cache_Named_Attributes
1962 (p_return_status => l_return_status,
1963 p_business_group_id => l_business_group_id);
1964
1965 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1966 raise FND_API.G_EXC_ERROR;
1967 end if;
1968
1969 end;
1970 end if;
1971
1972 for l_year_index in 1..PSB_WS_ACCT1.g_num_budget_years loop
1973
1974 if PSB_WS_ACCT1.g_budget_years(l_year_index).year_type in ('CY', 'PP') then
1975 begin
1976
1977 for c_Parameter_Rec in c_Parameter (PSB_WS_ACCT1.g_budget_years(l_year_index).start_date,
1978 PSB_WS_ACCT1.g_budget_years(l_year_index).end_date) loop
1979
1980 if ((c_Parameter_Rec.parameter_compound_annually is null) or
1981 (c_Parameter_Rec.parameter_compound_annually = 'N')) then
1982 l_compound_annually := FND_API.G_FALSE;
1983 else
1984 l_compound_annually := FND_API.G_TRUE;
1985 l_compound_factor := greatest(ceil(months_between(PSB_WS_ACCT1.g_budget_years(l_year_index).start_date,
1986 c_Parameter_Rec.effective_start_date) / 12), 0) + 1;
1987 end if;
1988
1989 Process_PosParam_Detailed
1990 (p_return_status => l_return_status,
1991 p_event_type => 'BP',
1992 p_local_parameter => 'N',
1993 p_worksheet_id => p_worksheet_id,
1994 p_global_worksheet_id => p_worksheet_id,
1995 p_global_worksheet => l_global_worksheet,
1996 p_data_extract_id => l_data_extract_id,
1997 p_business_group_id => l_business_group_id,
1998 p_parameter_id => c_Parameter_Rec.parameter_id,
1999 p_parameter_start_date => c_Parameter_Rec.effective_start_date,
2000 p_compound_annually => l_compound_annually,
2001 p_compound_factor => l_compound_factor,
2002 p_parameter_autoinc_rule => 'N',
2003 p_currency_code => nvl(c_Parameter_Rec.currency_code, l_func_currency),
2004 p_start_date => greatest(PSB_WS_ACCT1.g_budget_years(l_year_index).start_date, c_Parameter_Rec.effective_start_date),
2005 p_end_date => least(PSB_WS_ACCT1.g_budget_years(l_year_index).end_date,
2006 nvl(c_Parameter_Rec.effective_end_date, PSB_WS_ACCT1.g_budget_years(l_year_index).end_date)));
2007
2008 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2009 raise FND_API.G_EXC_ERROR;
2010 end if;
2011
2012 end loop;
2013
2014 end;
2015 end if;
2016
2017 end loop;
2018
2019 -- Process all the Autoincrement rules
2020
2021 for c_Parameter_Rec in c_ParamAutoInc (PSB_WS_ACCT1.g_startdate_cy,
2022 PSB_WS_ACCT1.g_end_est_date) loop
2023
2024 if ((c_Parameter_Rec.parameter_compound_annually is null) or
2025 (c_Parameter_Rec.parameter_compound_annually = 'N')) then
2026 l_compound_annually := FND_API.G_FALSE;
2027 else
2028 l_compound_annually := FND_API.G_TRUE;
2029 end if;
2030
2031 Process_PosParam_Detailed
2032 (p_return_status => l_return_status,
2033 p_event_type => 'BP',
2034 p_local_parameter => 'N',
2035 p_worksheet_id => p_worksheet_id,
2036 p_global_worksheet_id => p_worksheet_id,
2037 p_global_worksheet => l_global_worksheet,
2038 p_data_extract_id => l_data_extract_id,
2039 p_business_group_id => l_business_group_id,
2040 p_parameter_id => c_Parameter_Rec.parameter_id,
2041 p_parameter_start_date => c_Parameter_Rec.effective_start_date,
2042 p_compound_annually => l_compound_annually,
2043 p_parameter_autoinc_rule => 'Y',
2044 p_currency_code => nvl(c_Parameter_Rec.currency_code, l_func_currency),
2045 p_start_date => greatest(PSB_WS_ACCT1.g_startdate_cy, c_Parameter_Rec.effective_start_date),
2046 p_end_date => least(PSB_WS_ACCT1.g_end_est_date, nvl(c_Parameter_Rec.effective_end_date, PSB_WS_ACCT1.g_end_est_date)));
2047
2048 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2049 raise FND_API.G_EXC_ERROR;
2050 end if;
2051
2052 end loop;
2053
2054
2055 -- Initialize API return status to success
2056
2057 p_return_status := FND_API.G_RET_STS_SUCCESS;
2058
2059
2060 EXCEPTION
2061
2062 when FND_API.G_EXC_ERROR then
2063 p_return_status := FND_API.G_RET_STS_ERROR;
2064
2065 when FND_API.G_EXC_UNEXPECTED_ERROR then
2066 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2067
2068 when OTHERS then
2069 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2070
2071 if FND_MSG_PUB.Check_Msg_Level
2072 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2073 then
2074 FND_MSG_PUB.Add_Exc_Msg
2075 (p_pkg_name => G_PKG_NAME,
2076 p_procedure_name => l_api_name);
2077 end if;
2078
2079 END Apply_Position_Parameters;
2080
2081 /* ----------------------------------------------------------------------- */
2082
2083 PROCEDURE Process_PosParam_Detailed
2084 ( p_return_status OUT NOCOPY VARCHAR2,
2085 p_event_type IN VARCHAR2,
2086 p_local_parameter IN VARCHAR2,
2087 p_global_worksheet_id IN NUMBER,
2088 p_worksheet_id IN NUMBER,
2089 p_global_worksheet IN VARCHAR2,
2090 p_data_extract_id IN NUMBER,
2091 p_business_group_id IN NUMBER,
2092 p_parameter_id IN NUMBER,
2093 p_parameter_start_date IN DATE,
2094 p_compound_annually IN VARCHAR2,
2095 p_compound_factor IN NUMBER := FND_API.G_MISS_NUM,
2096 p_parameter_autoinc_rule IN VARCHAR2,
2097 p_currency_code IN VARCHAR2,
2098 p_start_date IN DATE,
2099 p_end_date IN DATE,
2100 p_recalculate_flag IN BOOLEAN := FALSE
2101 ) IS
2102
2103 l_root_budget_group_id NUMBER;
2104 l_set_of_books_id NUMBER;
2105 l_flex_code NUMBER;
2106 l_position_line_id NUMBER;
2107
2108 l_num_positions NUMBER := 0;
2109 l_return_status VARCHAR2(1);
2110
2111 l_api_name CONSTANT VARCHAR2(30) := 'Process_PosParam_Detailed';
2112
2113 /* Bug No 2594596 Start */
2114 l_compound_factor NUMBER;
2115 /* Bug No 2594596 End */
2116
2117 /* Bug No 1808330 Start */
2118 l_position_id NUMBER(20);
2119 l_budget_revision_pos_line_id NUMBER(20);
2120 l_note VARCHAR2(4000); -- Bug#4571412
2121
2122 l_msg_data VARCHAR2(2000);
2123 l_msg_count NUMBER;
2124
2125 cursor c_BR_positions is
2126 select a.budget_revision_pos_line_id
2127 from PSB_BUDGET_REVISION_POSITIONS a,
2128 PSB_BUDGET_REVISION_POS_LINES b
2129 where b.budget_revision_id = p_worksheet_id
2130 and a.position_id = l_position_id
2131 and a.budget_revision_pos_line_id = b.budget_revision_pos_line_id;
2132 /* Bug No 1808330 End */
2133
2134 cursor c_Positions is
2135 select a.position_id,
2136 c.name
2137 from PSB_BUDGET_POSITIONS a,
2138 PSB_SET_RELATIONS b,
2139 PSB_POSITIONS c
2140 where a.data_extract_id = p_data_extract_id
2141 and a.account_position_set_id = b.account_position_set_id
2142 and b.parameter_id = p_parameter_id
2143 and c.position_id = a.position_id;
2144
2145 cursor c_BG is
2146 select nvl(b.root_budget_group_id, b.budget_group_id) root_budget_group_id,
2147 nvl(b.set_of_books_id, b.root_set_of_books_id) set_of_books_id
2148 from PSB_WORKSHEETS_V a,
2149 PSB_BUDGET_GROUPS_V b
2150 where a.worksheet_id = p_worksheet_id
2151 and b.budget_group_id = a.budget_group_id;
2152
2153 cursor c_SOB is
2154 select chart_of_accounts_id
2155 from GL_SETS_OF_BOOKS
2156 where set_of_books_id = l_set_of_books_id;
2157
2158 cursor c_ParamName is
2159 select name from PSB_ENTITY where entity_id = p_parameter_id;
2160
2161 BEGIN
2162
2163 if p_recalculate_flag then
2164 begin
2165
2166 for c_BG_Rec in c_BG loop
2167 l_root_budget_group_id := c_BG_Rec.root_budget_group_id;
2168 l_set_of_books_id := c_BG_Rec.set_of_books_id;
2169 end loop;
2170
2171 for c_SOB_Rec in c_SOB loop
2172 l_flex_code := c_SOB_Rec.chart_of_accounts_id;
2173 g_flex_code := l_flex_code ; -- Bug#4675858
2174 end loop;
2175
2176 end;
2177 end if;
2178
2179 /* Bug No 2594596 Start */
2180 if p_compound_factor <> FND_API.G_MISS_NUM then
2181 l_compound_factor := p_compound_factor;
2182 else
2183 l_compound_factor := 1;
2184 end if;
2185 /* Bug No 2594596 End */
2186
2187 for c_Positions_Rec in c_Positions loop
2188
2189 if ((p_local_parameter = 'N') or ((p_local_parameter = 'Y') and Position_Exists(p_event_type, p_worksheet_id, c_Positions_Rec.position_id))) then
2190 begin
2191
2192 if p_local_parameter = 'Y' then
2193 begin
2194
2195 for c_ParamName_Rec in c_ParamName loop
2196 g_note_parameter_name := c_ParamName_Rec.name;
2197 end loop;
2198
2199 /* Bug No 1808330 Start */
2200 if p_event_type = 'BR' then
2201 l_position_id := c_Positions_Rec.position_id;
2202
2203 for c_BR_positions_rec in c_BR_positions loop
2204 l_budget_revision_pos_line_id := c_BR_positions_rec.budget_revision_pos_line_id;
2205 end loop;
2206
2207 ---- Create Note Id and Inserts a record in PSB_WS_ACCOUNT_LINE_NOTES table
2208
2209 FND_MESSAGE.SET_NAME('PSB', 'PSB_PARAMETER_NOTE_CREATION');
2210 FND_MESSAGE.SET_TOKEN('NAME', g_note_parameter_name);
2211 FND_MESSAGE.SET_TOKEN('DATE', sysdate);
2212 l_note := FND_MESSAGE.GET;
2213
2214 PSB_BUDGET_REVISIONS_PVT.Create_Note
2215 ( p_return_status => l_return_status
2216 , p_account_line_id => NULL
2217 , p_position_line_id => l_budget_revision_pos_line_id
2218 , p_note => l_note
2219 , p_flex_code => g_flex_code -- Bug#4675858
2220 , p_cc_id => NULL -- Bug#4675858
2221 ) ;
2222
2223 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2224 raise FND_API.G_EXC_ERROR;
2225 end if;
2226 end if;
2227 ----
2228 /* Bug No 1808330 End */
2229
2230 end;
2231 end if;
2232
2233 if ((p_parameter_autoinc_rule is null) or (p_parameter_autoinc_rule = 'N')) then
2234 begin
2235
2236 /* Bug No 2594596 Start */
2237 -- input parameter changed from p_compound_factor to l_compound_factor
2238
2239 Process_PosParam
2240 (p_return_status => l_return_status,
2241 x_msg_data => l_msg_data,
2242 x_msg_count => l_msg_count,
2243 p_worksheet_id => p_worksheet_id,
2244 p_global_worksheet_id => p_global_worksheet_id,
2245 p_data_extract_id => p_data_extract_id,
2246 p_position_id => c_Positions_Rec.position_id,
2247 p_parameter_id => p_parameter_id,
2248 p_currency_code => p_currency_code,
2249 p_start_date => p_start_date,
2250 p_end_date => p_end_date,
2251 p_compound_annually => p_compound_annually,
2252 p_compound_factor => l_compound_factor);
2253
2254 /* Bug No 2594596 End */
2255 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2256 raise FND_API.G_EXC_ERROR;
2257 end if;
2258
2259 end;
2260 else
2261 begin
2262
2263 if FND_API.to_Boolean(p_global_worksheet) then
2264 begin
2265
2266 Process_PosParam_AutoInc
2267 (p_return_status => l_return_status,
2268 x_msg_data => l_msg_data,
2269 x_msg_count => l_msg_count,
2270 p_worksheet_id => p_worksheet_id,
2271 p_data_extract_id => p_data_extract_id,
2272 p_business_group_id => p_business_group_id,
2273 p_position_id => c_Positions_Rec.position_id,
2274 p_parameter_id => p_parameter_id,
2275 p_parameter_start_date => p_parameter_start_date,
2276 p_currency_code => p_currency_code,
2277 p_start_date => p_start_date,
2278 p_end_date => p_end_date);
2279
2280 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2281 raise FND_API.G_EXC_ERROR;
2282 end if;
2283
2284 end;
2285 end if;
2286
2287 end;
2288 end if;
2289
2290 if p_recalculate_flag then
2291 begin
2292
2293 PSB_WS_POS1.g_salary_budget_group_id := null;
2294
2295 PSB_WS_POS1.Cache_Salary_Dist
2296 (p_return_status => l_return_status,
2297 p_worksheet_id => p_global_worksheet_id,
2298 p_root_budget_group_id => l_root_budget_group_id,
2299 p_flex_code => l_flex_code,
2300 p_data_extract_id => p_data_extract_id,
2301 p_position_id => c_Positions_Rec.position_id,
2302 p_position_name => c_Positions_Rec.name,
2303 p_start_date => PSB_WS_ACCT1.g_startdate_cy,
2304 p_end_date => PSB_WS_ACCT1.g_end_est_date);
2305
2306 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2307 raise FND_API.G_EXC_ERROR;
2308 end if;
2309
2310 PSB_WS_POS1.Create_Position_Lines
2311 (p_api_version => 1.0,
2312 p_return_status => l_return_status,
2313 p_position_line_id => l_position_line_id,
2314 p_worksheet_id => p_worksheet_id,
2315 p_position_id => c_Positions_Rec.position_id,
2316 p_budget_group_id => PSB_WS_POS1.g_salary_budget_group_id);
2317
2318 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2319 raise FND_API.G_EXC_ERROR;
2320 end if;
2321
2322 PSB_WS_POS2.Calculate_Position_Cost
2323 (p_api_version => 1.0,
2324 p_return_status => l_return_status,
2325 p_worksheet_id => p_worksheet_id,
2326 p_position_line_id => l_position_line_id);
2327
2328 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2329 raise FND_API.G_EXC_ERROR;
2330 end if;
2331
2332 end;
2333 end if;
2334
2335 l_num_positions := l_num_positions + 1;
2336
2337 if l_num_positions > PSB_WS_ACCT1.g_checkpoint_save then
2338 commit work;
2339 l_num_positions := 0;
2340 end if;
2341
2342 end;
2343 end if;
2344
2345 end loop;
2346
2347
2348 -- Initialize API return status to success
2349
2350 p_return_status := FND_API.G_RET_STS_SUCCESS;
2351
2352
2353 EXCEPTION
2354
2355 when FND_API.G_EXC_ERROR then
2356 p_return_status := FND_API.G_RET_STS_ERROR;
2357 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
2358 p_data => l_msg_data);
2359
2360
2361 when FND_API.G_EXC_UNEXPECTED_ERROR then
2362 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2363 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
2364 p_data => l_msg_data);
2365
2366 when OTHERS then
2367 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2368
2369 if FND_MSG_PUB.Check_Msg_Level
2370 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2371 then
2372 FND_MSG_PUB.Add_Exc_Msg
2373 (p_pkg_name => G_PKG_NAME,
2374 p_procedure_name => l_api_name);
2375 end if;
2376
2377 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
2378 p_data => l_msg_data);
2379
2380 END Process_PosParam_Detailed;
2381
2382 /* ----------------------------------------------------------------------- */
2383
2384 PROCEDURE Process_PosParam
2385 ( p_return_status OUT NOCOPY VARCHAR2,
2386 x_msg_data OUT NOCOPY VARCHAR2,
2387 x_msg_count OUT NOCOPY NUMBER,
2388 p_worksheet_id IN NUMBER,
2389 p_global_worksheet_id IN NUMBER,
2390 p_data_extract_id IN NUMBER,
2391 p_position_id IN NUMBER,
2392 p_parameter_id IN NUMBER,
2393 p_currency_code IN VARCHAR2,
2394 p_start_date IN DATE,
2395 p_end_date IN DATE,
2396 p_compound_annually IN VARCHAR2,
2397 p_compound_factor IN NUMBER
2398 ) IS
2399
2400 l_element_value_type VARCHAR2(2);
2401 l_element_value NUMBER;
2402
2403 l_msg_count NUMBER;
2404 l_msg_data VARCHAR2(2000);
2405
2406 l_posasgn_id NUMBER;
2407 l_rowid VARCHAR2(100);
2408
2409 l_start_date DATE;
2410 l_end_date DATE;
2411
2412 l_return_status VARCHAR2(1);
2413
2414 l_api_name CONSTANT VARCHAR2(30) := 'Process_PosParam';
2415
2416 cursor c_Formula is
2417 select assignment_type,
2418 attribute_id,
2419 attribute_value,
2420 pay_element_id,
2421 pay_element_option_id,
2422 element_value_type,
2423 element_value,
2424 effective_start_date,
2425 effective_end_date
2426 from PSB_PARAMETER_FORMULAS
2427 where parameter_id = p_parameter_id
2428 order by step_number;
2429
2430 BEGIN
2431
2432 for c_Formula_Rec in c_Formula loop
2433
2434 l_start_date := greatest(nvl(c_Formula_Rec.effective_start_date, p_start_date), p_start_date);
2435 l_end_date := least(nvl(c_Formula_Rec.effective_end_date, p_end_date), p_end_date);
2436
2437 if ((l_start_date <= l_end_date) and
2438 ((l_start_date between p_start_date and p_end_date) or
2439 (l_end_date between p_start_date and p_end_date))) then
2440 begin
2441
2442 if c_Formula_Rec.assignment_type = 'ELEMENT' then
2443 begin
2444
2445 if c_Formula_Rec.element_value_type = 'PI' then
2446 begin
2447
2448 Process_PosParam_PI
2449 (p_return_status => l_return_status,
2450 p_worksheet_id => p_worksheet_id,
2451 p_global_worksheet_id => p_global_worksheet_id,
2452 p_data_extract_id => p_data_extract_id,
2453 p_position_id => p_position_id,
2454 p_currency_code => p_currency_code,
2455 p_start_date => l_start_date,
2456 p_end_date => l_end_date,
2457 p_compound_annually => p_compound_annually,
2458 p_compound_factor => p_compound_factor,
2459 p_pay_element_id => c_Formula_Rec.pay_element_id,
2460 p_pay_element_option_id => c_Formula_Rec.pay_element_option_id,
2461 p_element_value => c_Formula_Rec.element_value);
2462
2463 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2464 raise FND_API.G_EXC_ERROR;
2465 end if;
2466
2467 end;
2468 elsif c_Formula_Rec.element_value_type = 'PS' then
2469 begin
2470
2471 if FND_API.to_Boolean(p_compound_annually) then
2472 begin
2473
2474 -- Bug 3786457 commented the following
2475 /* if c_Formula_Rec.element_value < 1 then
2476 l_element_value := c_Formula_Rec.element_value * POWER(1 + c_Formula_Rec.element_value, p_compound_factor);
2477 else
2478 l_element_value := c_Formula_Rec.element_value * POWER(1 + c_Formula_Rec.element_value / 100, p_compound_factor);
2479 end if; */
2480 l_element_value
2481 := c_Formula_Rec.element_value * POWER(1 + c_Formula_Rec.element_value / 100, p_compound_factor);
2482 /* Bug 3786457 End */
2483
2484 end;
2485 else
2486 l_element_value := c_Formula_Rec.element_value;
2487 end if;
2488
2489 l_element_value_type := c_Formula_Rec.element_value_type;
2490
2491 PSB_POSITIONS_PVT.Modify_Assignment
2492 (p_api_version => 1.0,
2493 p_return_status => l_return_status,
2494 p_msg_count => l_msg_count,
2495 p_msg_data => l_msg_data,
2496 p_position_assignment_id => l_posasgn_id,
2497 p_data_extract_id => p_data_extract_id,
2498 p_worksheet_id => p_global_worksheet_id,
2499 p_position_id => p_position_id,
2500 p_assignment_type => c_Formula_Rec.assignment_type,
2501 p_attribute_id => null,
2502 p_attribute_value_id => null,
2503 p_attribute_value => null,
2504 p_pay_element_id => c_Formula_Rec.pay_element_id,
2505 p_pay_element_option_id => c_Formula_Rec.pay_element_option_id,
2506 p_effective_start_date => l_start_date,
2507 p_effective_end_date => l_end_date,
2508 p_element_value_type => l_element_value_type,
2509 p_element_value => l_element_value,
2510 p_currency_code => p_currency_code,
2511 p_pay_basis => null,
2512 p_global_default_flag => null,
2513 p_assignment_default_rule_id => null,
2514 p_modify_flag => null,
2515 p_rowid => l_rowid,
2516 p_employee_id => null,
2517 p_primary_employee_flag => null);
2518
2519 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2520 raise FND_API.G_EXC_ERROR;
2521 end if;
2522
2523 end;
2524 else
2525 begin
2526
2527 l_element_value := c_Formula_Rec.element_value;
2528 l_element_value_type := c_Formula_Rec.element_value_type;
2529
2530 PSB_POSITIONS_PVT.Modify_Assignment
2531 (p_api_version => 1.0,
2532 p_return_status => l_return_status,
2533 p_msg_count => l_msg_count,
2534 p_msg_data => l_msg_data,
2535 p_position_assignment_id => l_posasgn_id,
2536 p_data_extract_id => p_data_extract_id,
2537 p_worksheet_id => p_global_worksheet_id,
2538 p_position_id => p_position_id,
2539 p_assignment_type => c_Formula_Rec.assignment_type,
2540 p_attribute_id => null,
2541 p_attribute_value_id => null,
2542 p_attribute_value => null,
2543 p_pay_element_id => c_Formula_Rec.pay_element_id,
2544 p_pay_element_option_id => c_Formula_Rec.pay_element_option_id,
2545 p_effective_start_date => l_start_date,
2546 p_effective_end_date => l_end_date,
2547 p_element_value_type => l_element_value_type,
2548 p_element_value => l_element_value,
2549 p_currency_code => p_currency_code,
2550 p_pay_basis => null,
2551 p_global_default_flag => null,
2552 p_assignment_default_rule_id => null,
2553 p_modify_flag => null,
2554 p_rowid => l_rowid,
2555 p_employee_id => null,
2556 p_primary_employee_flag => null);
2557
2558 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2559 raise FND_API.G_EXC_ERROR;
2560 end if;
2561
2562 end;
2563 end if;
2564
2565 end;
2566 elsif c_Formula_Rec.assignment_type = 'ATTRIBUTE' then
2567 begin
2568
2569 PSB_POSITIONS_PVT.Modify_Assignment
2570 (p_api_version => 1.0,
2571 p_return_status => l_return_status,
2572 p_msg_count => l_msg_count,
2573 p_msg_data => l_msg_data,
2574 p_position_assignment_id => l_posasgn_id,
2575 p_data_extract_id => p_data_extract_id,
2576 p_worksheet_id => p_global_worksheet_id,
2577 p_position_id => p_position_id,
2578 p_assignment_type => c_Formula_Rec.assignment_type,
2579 p_attribute_id => c_Formula_Rec.attribute_id,
2580 p_attribute_value_id => null,
2581 p_attribute_value => c_Formula_Rec.attribute_value,
2582 p_pay_element_id => null,
2583 p_pay_element_option_id => null,
2584 p_effective_start_date => l_start_date,
2585 p_effective_end_date => l_end_date,
2586 p_element_value_type => null,
2587 p_element_value => null,
2588 p_currency_code => null,
2589 p_pay_basis => null,
2590 p_global_default_flag => null,
2591 p_assignment_default_rule_id => null,
2592 p_modify_flag => null,
2593 p_rowid => l_rowid,
2594 p_employee_id => null,
2595 p_primary_employee_flag => null);
2596
2597 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2598 raise FND_API.G_EXC_ERROR;
2599 end if;
2600
2601 end;
2602 end if;
2603
2604 end;
2605 end if;
2606
2607 end loop;
2608
2609
2610 -- Initialize API return status to success
2611
2612 p_return_status := FND_API.G_RET_STS_SUCCESS;
2613
2614
2615 EXCEPTION
2616
2617 when FND_API.G_EXC_ERROR then
2618 p_return_status := FND_API.G_RET_STS_ERROR;
2619 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
2620 p_data => x_msg_data);
2621
2622
2623 when FND_API.G_EXC_UNEXPECTED_ERROR then
2624 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2625 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
2626 p_data => x_msg_data);
2627
2628 when OTHERS then
2629 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2630
2631 if FND_MSG_PUB.Check_Msg_Level
2632 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2633 then
2634 FND_MSG_PUB.Add_Exc_Msg
2635 (p_pkg_name => G_PKG_NAME,
2636 p_procedure_name => l_api_name);
2637 end if;
2638
2639 END Process_PosParam;
2640
2641 /* ----------------------------------------------------------------------- */
2642
2643 PROCEDURE Process_PosParam_PI
2644 ( p_return_status OUT NOCOPY VARCHAR2,
2645 p_worksheet_id IN NUMBER,
2646 p_global_worksheet_id IN NUMBER,
2647 p_data_extract_id IN NUMBER,
2648 p_position_id IN NUMBER,
2649 p_currency_code IN VARCHAR2,
2650 p_start_date IN DATE,
2651 p_end_date IN DATE,
2652 p_compound_annually IN VARCHAR2,
2653 p_compound_factor IN NUMBER,
2654 p_pay_element_id IN NUMBER,
2655 p_pay_element_option_id IN NUMBER,
2656 p_element_value IN NUMBER
2657 ) IS
2658
2659 l_element_value NUMBER;
2660
2661 l_msg_count NUMBER;
2662 l_msg_data VARCHAR2(2000);
2663
2664 l_posasgn_id NUMBER;
2665 l_rowid VARCHAR2(100);
2666
2667 l_return_status VARCHAR2(1);
2668
2669 cursor c_Elem is
2670 select pay_element_option_id,
2671 effective_start_date,
2672 effective_end_date,
2673 element_value_type,
2674 element_value,
2675 pay_basis
2676 from PSB_POSITION_ASSIGNMENTS
2677 where worksheet_id is null
2678 and currency_code = p_currency_code
2679 and ((p_pay_element_option_id is null) or (pay_element_option_id = p_pay_element_option_id))
2680 and (((effective_start_date <= p_end_date)
2681 and (effective_end_date is null))
2682 or ((effective_start_date between p_start_date and p_end_date)
2683 or (effective_end_date between p_start_date and p_end_date)
2684 or ((effective_start_date < p_start_date)
2685 and (effective_end_date > p_end_date))))
2686 and pay_element_id = p_pay_element_id
2687 and position_id = p_position_id;
2688
2689 cursor c_ElemRates (StartDate DATE,
2690 EndDate DATE) is
2691 select pay_element_option_id,
2692 effective_start_date,
2693 effective_end_date,
2694 element_value_type,
2695 element_value,
2696 formula_id,
2697 pay_basis
2698 from PSB_PAY_ELEMENT_RATES
2699 where worksheet_id is null
2700 and ((p_pay_element_option_id is null) or (pay_element_option_id = p_pay_element_option_id))
2701 and (((effective_start_date <= EndDate)
2702 and (effective_end_date is null))
2703 or ((effective_start_date between StartDate and EndDate)
2704 or (effective_end_date between StartDate and EndDate)
2705 or ((effective_start_date < StartDate)
2706 and (effective_end_date > EndDate))))
2707 and pay_element_id = p_pay_element_id;
2708
2709 l_api_name CONSTANT VARCHAR2(30) := 'Process_PosParam_PI';
2710
2711 BEGIN
2712
2713 for c_Elem_Rec in c_Elem loop
2714
2715 if c_Elem_Rec.element_value is null then
2716 begin
2717
2718 /* Bug No 2482305 Start */
2719 -- for c_ElemRates_Rec in c_ElemRates (c_Elem_Rec.effective_start_date, c_Elem_Rec.effective_end_date) loop
2720
2721 for c_ElemRates_Rec in c_ElemRates (c_Elem_Rec.effective_start_date, nvl(c_Elem_Rec.effective_end_date, p_end_date)) loop
2722 /* Bug No 2482305 End */
2723
2724 if c_ElemRates_Rec.element_value_type in ('A', 'PS') then
2725 begin
2726
2727 if FND_API.to_Boolean(p_compound_annually) then
2728 begin
2729
2730 /* Bug 3786457 Start */
2731 /* IF p_element_value < 1 THEN
2732 l_element_value
2733 := c_ElemRates_Rec.element_value * POWER(1 + p_element_value, p_compound_factor);
2734 ELSE
2735 l_element_value
2736 := c_ElemRates_Rec.element_value * POWER(1 + p_element_value / 100, p_compound_factor);
2737 END IF; */
2738 l_element_value
2739 := c_ElemRates_Rec.element_value * POWER(1 + p_element_value / 100, p_compound_factor);
2740 /* Bug 3786457 End */
2741
2742 end;
2743 else
2744 begin
2745
2746 /* Bug 3786457 Start */
2747
2748 /*IF p_element_value < 1 THEN
2749 l_element_value := c_ElemRates_Rec.element_value * (1 + p_element_value);
2750 ELSE
2751 l_element_value := c_ElemRates_Rec.element_value * (1 + p_element_value / 100);
2752 END IF; */
2753 l_element_value := c_ElemRates_Rec.element_value * (1 + p_element_value / 100);
2754 /* Bug 3786457 End */
2755
2756 end;
2757 end if;
2758
2759 PSB_POSITIONS_PVT.Modify_Assignment
2760 (p_api_version => 1.0,
2761 p_return_status => l_return_status,
2762 p_msg_count => l_msg_count,
2763 p_msg_data => l_msg_data,
2764 p_position_assignment_id => l_posasgn_id,
2765 p_data_extract_id => p_data_extract_id,
2766 p_worksheet_id => p_global_worksheet_id,
2767 p_position_id => p_position_id,
2768 p_assignment_type => 'ELEMENT',
2769 p_attribute_id => null,
2770 p_attribute_value_id => null,
2771 p_attribute_value => null,
2772 p_pay_element_id => p_pay_element_id,
2773 p_pay_element_option_id => c_ElemRates_Rec.pay_element_option_id,
2774 p_effective_start_date => greatest(c_ElemRates_Rec.effective_start_date, p_start_date),
2775 p_effective_end_date => least(nvl(c_ElemRates_Rec.effective_end_date, p_end_date), p_end_date),
2776 p_element_value_type => c_ElemRates_Rec.element_value_type,
2777 p_element_value => l_element_value,
2778 p_currency_code => p_currency_code,
2779 p_pay_basis => c_ElemRates_Rec.pay_basis,
2780 p_global_default_flag => null,
2781 p_assignment_default_rule_id => null,
2782 p_modify_flag => null,
2783 p_rowid => l_rowid,
2784 p_employee_id => null,
2785 p_primary_employee_flag => null);
2786
2787 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2788 raise FND_API.G_EXC_ERROR;
2789 end if;
2790
2791 end;
2792 end if;
2793
2794 end loop;
2795
2796 end;
2797 else
2798 begin
2799
2800
2801
2802
2803 IF c_Elem_Rec.element_value_type IN ('A','PS') THEN
2804
2805 begin
2806 /* Bug 3786457 Start */
2807 /* if p_element_value < 1 then
2808 l_element_value := c_Elem_Rec.element_value * POWER(1 + p_element_value, p_compound_factor);
2809 else
2810 l_element_value := c_Elem_Rec.element_value * POWER(1 + p_element_value / 100, p_compound_factor);
2811 end if; */
2812 l_element_value
2813 := c_Elem_Rec.element_value * POWER(1 + p_element_value / 100, p_compound_factor);
2814 /* Bug 3786457 End */
2815 end;
2816 else
2817 begin
2818
2819 if p_element_value < 1 then
2820 l_element_value := c_Elem_Rec.element_value * (1 + p_element_value);
2821 else
2822 l_element_value := c_Elem_Rec.element_value * (1 + p_element_value / 100);
2823 end if;
2824
2825 end;
2826 end if;
2827
2828 PSB_POSITIONS_PVT.Modify_Assignment
2829 (p_api_version => 1.0,
2830 p_return_status => l_return_status,
2831 p_msg_count => l_msg_count,
2832 p_msg_data => l_msg_data,
2833 p_position_assignment_id => l_posasgn_id,
2834 p_data_extract_id => p_data_extract_id,
2835 p_worksheet_id => p_global_worksheet_id,
2836 p_position_id => p_position_id,
2837 p_assignment_type => 'ELEMENT',
2838 p_attribute_id => null,
2839 p_attribute_value_id => null,
2840 p_attribute_value => null,
2841 p_pay_element_id => p_pay_element_id,
2842 p_pay_element_option_id => c_Elem_Rec.pay_element_option_id,
2843 p_effective_start_date => greatest(c_Elem_Rec.effective_start_date, p_start_date),
2844 p_effective_end_date => least(nvl(c_Elem_Rec.effective_end_date, p_end_date), p_end_date),
2845 p_element_value_type => c_Elem_Rec.element_value_type,
2846 p_element_value => l_element_value,
2847 p_currency_code => p_currency_code,
2848 p_pay_basis => c_Elem_Rec.pay_basis,
2849 p_global_default_flag => null,
2850 p_assignment_default_rule_id => null,
2851 p_modify_flag => null,
2852 p_rowid => l_rowid,
2853 p_employee_id => null,
2854 p_primary_employee_flag => null);
2855
2856 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2857 raise FND_API.G_EXC_ERROR;
2858 end if;
2859
2860 end;
2861 end if;
2862
2863 -- end;
2864 -- end if;
2865
2866 end loop;
2867
2868
2869 -- Initialize API return status to success
2870
2871 p_return_status := FND_API.G_RET_STS_SUCCESS;
2872
2873
2874 EXCEPTION
2875
2876 when FND_API.G_EXC_ERROR then
2877 p_return_status := FND_API.G_RET_STS_ERROR;
2878 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
2879 p_data => l_msg_data);
2880
2881
2882 when FND_API.G_EXC_UNEXPECTED_ERROR then
2883 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2884 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
2885 p_data => l_msg_data);
2886
2887 when OTHERS then
2888 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2889
2890 if FND_MSG_PUB.Check_Msg_Level
2891 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2892 then
2893 FND_MSG_PUB.Add_Exc_Msg
2894 (p_pkg_name => G_PKG_NAME,
2895 p_procedure_name => l_api_name);
2896 end if;
2897
2898 END Process_PosParam_PI;
2899
2900 /* ----------------------------------------------------------------------- */
2901
2902 PROCEDURE Process_PosParam_AutoInc
2903 ( p_return_status OUT NOCOPY VARCHAR2,
2904 x_msg_data OUT NOCOPY VARCHAR2,
2905 x_msg_count OUT NOCOPY NUMBER,
2906 p_worksheet_id IN NUMBER,
2907 p_data_extract_id IN NUMBER,
2908 p_business_group_id IN NUMBER,
2909 p_position_id IN NUMBER,
2910 p_parameter_id IN NUMBER,
2911 p_parameter_start_date IN DATE,
2912 p_currency_code IN VARCHAR2,
2913 p_start_date IN DATE,
2914 p_end_date IN DATE
2915 ) IS
2916
2917 l_start_date DATE;
2918 l_end_date DATE;
2919
2920 l_multiplier NUMBER;
2921
2922 l_compound_annually VARCHAR2(1);
2923 l_compound_factor NUMBER;
2924
2925 l_hiredate_between_from NUMBER;
2926 l_hiredate_between_to NUMBER;
2927 l_adjdate_between_from NUMBER;
2928 l_adjdate_between_to NUMBER;
2929 l_increment_by NUMBER;
2930 l_increment_type VARCHAR2(1);
2931
2932 l_return_status VARCHAR2(1);
2933
2934 /* start bug 4104890*/
2935 l_mid_point NUMBER;
2936 l_current_day NUMBER;
2937 l_cp_start_date DATE;
2938 l_np_start_date DATE;
2939 /* end bug 4104890*/
2940
2941
2942 l_api_name CONSTANT VARCHAR2(30) := 'Process_PosParam_AutoInc';
2943
2944 cursor c_Formula is
2945 select hiredate_between_from,
2946 hiredate_between_to,
2947 adjdate_between_from,
2948 adjdate_between_to,
2949 increment_by,
2950 increment_type
2951 from PSB_PARAMETER_FORMULAS
2952 where parameter_id = p_parameter_id;
2953
2954 BEGIN
2955
2956 for c_Formula_Rec in c_Formula loop
2957 l_hiredate_between_from := c_Formula_Rec.hiredate_between_from;
2958 l_hiredate_between_to := c_Formula_Rec.hiredate_between_to;
2959 l_adjdate_between_from := c_Formula_Rec.adjdate_between_from;
2960 l_adjdate_between_to := c_Formula_Rec.adjdate_between_to;
2961 l_increment_by := c_Formula_Rec.increment_by;
2962 l_increment_type := c_Formula_Rec.increment_type;
2963 end loop;
2964
2965 -- fix done for bug no 4104890
2966 -- added the p_local_parameter flag
2967 PSB_WS_POS1.Cache_Named_Attribute_Values
2968 (p_return_status => l_return_status,
2969 p_worksheet_id => p_worksheet_id,
2970 p_data_extract_id => p_data_extract_id,
2971 p_position_id => p_position_id,
2972 p_local_parameter_flag => 'Y');
2973
2974
2975 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2976 raise FND_API.G_EXC_ERROR;
2977 end if;
2978
2979 if l_hiredate_between_from is not null then
2980 begin
2981
2982 l_compound_factor := greatest(ceil((p_start_date - p_parameter_start_date) / l_hiredate_between_from), 0) + 1;
2983 l_multiplier := greatest(ceil((p_start_date - PSB_WS_POS1.g_hiredate) / l_hiredate_between_from), 0);
2984
2985 /* start bug 4104890 */
2986 IF l_multiplier <= 0 THEN
2987 l_multiplier := 1;
2988 END IF;
2989 /* start bug 4104890 */
2990
2991 loop
2992
2993 /* start bug 4104890*/
2994 fnd_file.put_line(fnd_file.log, 'position id : '||p_position_id||' hire date : '||PSB_WS_POS1.g_hiredate);
2995 /* End bug 4104890*/
2996
2997 l_start_date := PSB_WS_POS1.g_hiredate + l_hiredate_between_from * l_multiplier;
2998
2999 /* start bug 4104890*/
3000 FOR l_periods IN
3001 (SELECT start_date,
3002 end_date+1 end_date
3003 FROM psb_budget_periods
3004 WHERE budget_period_type = 'C'
3005 AND l_start_date between start_date AND end_date
3006 AND budget_calendar_id = g_budget_calendar_id
3007 )
3008 LOOP
3009 l_cp_start_date := l_periods.start_date;
3010 l_np_start_date := l_periods.end_date;
3011 fnd_file.put_line(fnd_file.log, ' current period :'||l_cp_start_date||' next period : '||l_np_start_date);
3012 END LOOP;
3013
3014 IF l_start_date between p_start_date and p_end_date THEN
3015 IF g_autoinc_period_profile = 'C' THEN
3016 -- current period
3017 l_start_date := l_cp_start_date;
3018
3019 ELSIF g_autoinc_period_profile = 'N' THEN
3020 -- next period
3021 l_start_date := l_np_start_date;
3022 ELSE
3023 -- month mid point
3024 l_mid_point := ceil((trunc(l_np_start_date - 1) - trunc(l_cp_start_date))/2);
3025 l_current_day := trunc(l_start_date) - trunc(l_cp_start_date);
3026
3027 IF l_current_day <= l_mid_point THEN
3028 l_start_date := l_cp_start_date;
3029 ELSE
3030 l_start_date := l_np_start_date;
3031 END IF;
3032 END IF;
3033 END IF;
3034 /* End bug 4104890 */
3035
3036 if l_start_date between p_start_date and p_end_date then
3037 begin
3038
3039 Process_PosParam_AutoInc_Step
3040 (p_return_status => l_return_status,
3041 p_worksheet_id => p_worksheet_id,
3042 p_data_extract_id => p_data_extract_id,
3043 p_business_group_id => p_business_group_id,
3044 p_position_id => p_position_id,
3045 p_currency_code => p_currency_code,
3046 p_start_date => l_start_date,
3047 p_end_date => null,
3048 p_compound_annually => FND_API.G_TRUE,
3049 p_compound_factor => l_compound_factor,
3050 p_increment_type => l_increment_type,
3051 p_increment_by => l_increment_by);
3052
3053 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3054 raise FND_API.G_EXC_ERROR;
3055 end if;
3056
3057 l_compound_factor := l_compound_factor + 1;
3058 l_multiplier := l_multiplier + 1;
3059
3060 end;
3061 else
3062 exit;
3063 end if;
3064
3065 end loop;
3066
3067 end;
3068 elsif l_adjdate_between_from is not null then
3069 begin
3070
3071 l_compound_factor := greatest(ceil((p_start_date - p_parameter_start_date) / l_adjdate_between_from), 0) + 1;
3072 l_multiplier := greatest(ceil((p_start_date - PSB_WS_POS1.g_adjustment_date) / l_adjdate_between_from), 0);
3073
3074 /* start bug 4104890 */
3075 IF l_multiplier <= 0 THEN
3076 l_multiplier := 1;
3077 END IF;
3078 /* start bug 4104890 */
3079
3080 loop
3081
3082 /* start bug 4104890*/
3083 fnd_file.put_line(fnd_file.log, 'position id : '||p_position_id||' adjustment date : '||PSB_WS_POS1.g_adjustment_date);
3084 /* End bug 4104890*/
3085
3086 l_start_date := PSB_WS_POS1.g_adjustment_date + l_adjdate_between_from * l_multiplier;
3087
3088 /* start bug 4104890*/
3089 FOR l_periods IN
3090 (SELECT start_date ,
3091 end_date+1 end_date
3092 FROM psb_budget_periods
3093 WHERE budget_period_type = 'C'
3094 AND l_start_date between start_date AND end_date
3095 AND budget_calendar_id = g_budget_calendar_id
3096 )
3097 LOOP
3098 l_cp_start_date := l_periods.start_date;
3099 l_np_start_date := l_periods.end_date;
3100 fnd_file.put_line(fnd_file.log, ' current period :'||l_cp_start_date||' next period : '||l_np_start_date);
3101 END LOOP;
3102
3103
3104 IF l_start_date between p_start_date and p_end_date THEN
3105 IF g_autoinc_period_profile = 'C' THEN
3106 -- current period
3107 l_start_date := l_cp_start_date;
3108
3109 ELSIF g_autoinc_period_profile = 'N' THEN
3110 -- next period
3111 l_start_date := l_np_start_date;
3112
3113 ELSE
3114 -- month mid point
3115 l_mid_point := ceil((trunc(l_np_start_date - 1) - trunc(l_cp_start_date))/2);
3116 l_current_day := trunc(l_start_date) - trunc(l_cp_start_date);
3117
3118 IF l_current_day <= l_mid_point THEN
3119 l_start_date := l_cp_start_date;
3120 ELSE
3121 l_start_date := l_np_start_date;
3122 END IF;
3123 END IF;
3124 END IF;
3125 /* End bug 4104890 */
3126
3127 if l_start_date between p_start_date and p_end_date then
3128 begin
3129
3130 Process_PosParam_AutoInc_Step
3131 (p_return_status => l_return_status,
3132 p_worksheet_id => p_worksheet_id,
3133 p_data_extract_id => p_data_extract_id,
3134 p_business_group_id => p_business_group_id,
3135 p_position_id => p_position_id,
3136 p_currency_code => p_currency_code,
3137 p_start_date => l_start_date,
3138 p_end_date => null,
3139 p_compound_annually => FND_API.G_TRUE,
3140 p_compound_factor => l_compound_factor,
3141 p_increment_type => l_increment_type,
3142 p_increment_by => l_increment_by);
3143
3144 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3145 raise FND_API.G_EXC_ERROR;
3146 end if;
3147
3148 l_compound_factor := l_compound_factor + 1;
3149 l_multiplier := l_multiplier + 1;
3150
3151 end;
3152 else
3153 exit;
3154 end if;
3155
3156 end loop;
3157
3158 end;
3159 end if;
3160
3161
3162 -- Initialize API return status to success
3163
3164 p_return_status := FND_API.G_RET_STS_SUCCESS;
3165
3166
3167 EXCEPTION
3168
3169 when FND_API.G_EXC_ERROR then
3170 p_return_status := FND_API.G_RET_STS_ERROR;
3171 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
3172 p_data => x_msg_data);
3173
3174
3175 when FND_API.G_EXC_UNEXPECTED_ERROR then
3176 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3177 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
3178 p_data => x_msg_data);
3179
3180 when OTHERS then
3181 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3182
3183 if FND_MSG_PUB.Check_Msg_Level
3184 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3185 then
3186 FND_MSG_PUB.Add_Exc_Msg
3187 (p_pkg_name => G_PKG_NAME,
3188 p_procedure_name => l_api_name);
3189 end if;
3190
3191 END Process_PosParam_AutoInc;
3192
3193 /* ----------------------------------------------------------------------- */
3194
3195 PROCEDURE Process_PosParam_AutoInc_Step
3196 ( p_return_status OUT NOCOPY VARCHAR2,
3197 p_worksheet_id IN NUMBER,
3198 p_data_extract_id IN NUMBER,
3199 p_business_group_id IN NUMBER,
3200 p_position_id IN NUMBER,
3201 p_currency_code IN VARCHAR2,
3202 p_start_date IN DATE,
3203 p_end_date IN DATE,
3204 p_compound_annually IN VARCHAR2,
3205 p_compound_factor IN NUMBER := FND_API.G_MISS_NUM,
3206 p_increment_type IN VARCHAR2,
3207 p_increment_by IN NUMBER
3208 ) IS
3209
3210 l_element_value NUMBER;
3211
3212 l_nextrate_found VARCHAR2(1);
3213 l_option_name VARCHAR2(80);
3214 l_sequence_number NUMBER;
3215 l_increment_by NUMBER;
3216 l_pay_element_option_id NUMBER;
3217
3218 l_posasgn_id NUMBER;
3219 l_rowid VARCHAR2(100);
3220
3221 l_num_steps NUMBER;
3222
3223 l_return_status VARCHAR2(1);
3224 l_msg_data VARCHAR2(2000);
3225 l_msg_count NUMBER;
3226
3227 l_api_name CONSTANT VARCHAR2(30) := 'Process_PosParam_AutoInc_Step';
3228
3229 cursor c_Elem is
3230 select a.pay_element_id,
3231 a.pay_element_option_id,
3232 a.effective_start_date,
3233 a.effective_end_date,
3234 a.element_value_type,
3235 a.element_value,
3236 a.pay_basis,
3237 /* For Bug No. 2263220 : Start */
3238 b.option_flag
3239 /* For Bug No. 2263220 : End */
3240 from PSB_POSITION_ASSIGNMENTS a,
3241 PSB_PAY_ELEMENTS b
3242 where a.worksheet_id is null
3243 and a.currency_code = p_currency_code
3244 and (((p_end_date is not null)
3245 and (((a.effective_start_date <= p_end_date)
3246 and (a.effective_end_date is null))
3247 or ((a.effective_start_date between p_start_date and p_end_date)
3248 or (a.effective_end_date between p_start_date and p_end_date)
3249 or ((a.effective_start_date < p_start_date)
3250 and (a.effective_end_date > p_end_date)))))
3251 or ((p_end_date is null)
3252 and (nvl(a.effective_end_date, p_start_date) >= p_start_date)))
3253 and a.pay_element_id = b.pay_element_id
3254 and a.position_id = p_position_id
3255 and b.salary_flag = 'Y'
3256 and b.processing_type = 'R'
3257 and b.business_group_id = p_business_group_id
3258 and b.data_extract_id = p_data_extract_id;
3259
3260 cursor c_ElemRates (ElemID NUMBER,
3261 ElemOptID NUMBER,
3262 StartDate DATE,
3263 EndDate DATE) is
3264 select element_value_type,
3265 element_value,
3266 formula_id,
3267 pay_basis,
3268 effective_start_date,
3269 effective_end_date
3270 from PSB_PAY_ELEMENT_RATES
3271 where worksheet_id is null
3272 and ((ElemOptID is null) or (pay_element_option_id = ElemOptID))
3273 and (((EndDate is not null)
3274 and (((effective_start_date <= EndDate)
3275 and (effective_end_date is null))
3276 or ((effective_start_date between StartDate and EndDate)
3277 or (effective_end_date between StartDate and EndDate)
3278 or ((effective_start_date < StartDate)
3279 and (effective_end_date > EndDate)))))
3280 or ((EndDate is null)
3281 and (nvl(effective_end_date, StartDate) >= StartDate)))
3282 and pay_element_id = ElemID;
3283
3284 cursor c_ElemOptions (ElemOptID NUMBER) is
3285 select name,
3286 sequence_number
3287 from PSB_PAY_ELEMENT_OPTIONS
3288 where pay_element_option_id = ElemOptID;
3289
3290 cursor c_NextOption (ElementID NUMBER,
3291 OptionName VARCHAR2,
3292 SeqNum NUMBER) is
3293 select pay_element_option_id,
3294 sequence_number
3295 from PSB_PAY_ELEMENT_OPTIONS
3296 where sequence_number =
3297 (select min(sequence_number)
3298 from PSB_PAY_ELEMENT_OPTIONS
3299 where sequence_number > SeqNum
3300 and name = OptionName
3301 and pay_element_id = ElementID)
3302 and name = OptionName
3303 and pay_element_id = ElementID;
3304
3305 BEGIN
3306
3307 -- Loop for all Positions assigned to recurring Salary Elements
3308
3309 for c_Elem_Rec in c_Elem loop
3310
3311 if p_increment_type = 'A' then
3312 begin
3313
3314 if c_Elem_Rec.element_value is null then
3315 begin
3316
3317 for c_ElemRates_Rec in c_ElemRates (c_Elem_Rec.pay_element_id,
3318 c_Elem_Rec.pay_element_option_id,
3319 c_Elem_Rec.effective_start_date,
3320 c_Elem_Rec.effective_end_date) loop
3321
3322 if c_ElemRates_Rec.element_value_type = 'A' then
3323 begin
3324
3325 if FND_API.to_Boolean(p_compound_annually) then
3326 l_element_value := c_ElemRates_Rec.element_value + p_increment_by * p_compound_factor;
3327 else
3328 l_element_value := c_ElemRates_Rec.element_value + p_increment_by;
3329 end if;
3330
3331 PSB_POSITIONS_PVT.Modify_Assignment
3332 (p_api_version => 1.0,
3333 p_return_status => l_return_status,
3334 p_msg_count => l_msg_count,
3335 p_msg_data => l_msg_data,
3336 p_position_assignment_id => l_posasgn_id,
3337 p_data_extract_id => p_data_extract_id,
3338 p_worksheet_id => p_worksheet_id,
3339 p_position_id => p_position_id,
3340 p_assignment_type => 'ELEMENT',
3341 p_attribute_id => null,
3342 p_attribute_value_id => null,
3343 p_attribute_value => null,
3344 p_pay_element_id => c_Elem_Rec.pay_element_id,
3345 p_pay_element_option_id => c_Elem_Rec.pay_element_option_id,
3346 p_effective_start_date => greatest(c_ElemRates_Rec.effective_start_date, p_start_date),
3347 p_effective_end_date => least(nvl(c_ElemRates_Rec.effective_end_date, p_end_date), p_end_date),
3348 p_element_value_type => c_ElemRates_Rec.element_value_type,
3349 p_element_value => l_element_value,
3350 p_currency_code => p_currency_code,
3351 p_pay_basis => c_ElemRates_Rec.pay_basis,
3352 p_global_default_flag => null,
3353 p_assignment_default_rule_id => null,
3354 p_modify_flag => null,
3355 p_rowid => l_rowid,
3356 p_employee_id => null,
3357 p_primary_employee_flag => null);
3358
3359 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3360 raise FND_API.G_EXC_ERROR;
3361 end if;
3362
3363 end;
3364 end if;
3365
3366 end loop;
3367
3368 end;
3369 else
3370 begin
3371
3372 if c_Elem_Rec.element_value_type = 'A' then
3373 begin
3374
3375 if FND_API.to_Boolean(p_compound_annually) then
3376 l_element_value := c_Elem_Rec.element_value + p_increment_by * p_compound_factor;
3377 else
3378 l_element_value := c_Elem_Rec.element_value + p_increment_by;
3379 end if;
3380
3381 PSB_POSITIONS_PVT.Modify_Assignment
3382 (p_api_version => 1.0,
3383 p_return_status => l_return_status,
3384 p_msg_count => l_msg_count,
3385 p_msg_data => l_msg_data,
3386 p_position_assignment_id => l_posasgn_id,
3387 p_data_extract_id => p_data_extract_id,
3388 p_worksheet_id => p_worksheet_id,
3389 p_position_id => p_position_id,
3390 p_assignment_type => 'ELEMENT',
3391 p_attribute_id => null,
3392 p_attribute_value_id => null,
3393 p_attribute_value => null,
3394 p_pay_element_id => c_Elem_Rec.pay_element_id,
3395 p_pay_element_option_id => c_Elem_Rec.pay_element_option_id,
3396 p_effective_start_date => greatest(c_Elem_Rec.effective_start_date, p_start_date),
3397 p_effective_end_date => least(nvl(c_Elem_Rec.effective_end_date, p_end_date), p_end_date),
3398 p_element_value_type => c_Elem_Rec.element_value_type,
3399 p_element_value => l_element_value,
3400 p_currency_code => p_currency_code,
3401 p_pay_basis => c_Elem_Rec.pay_basis,
3402 p_global_default_flag => null,
3403 p_assignment_default_rule_id => null,
3404 p_modify_flag => null,
3405 p_rowid => l_rowid,
3406 p_employee_id => null,
3407 p_primary_employee_flag => null);
3408
3409 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3410 raise FND_API.G_EXC_ERROR;
3411 end if;
3412
3413 end;
3414 end if;
3415
3416 end;
3417 end if;
3418
3419 end;
3420 elsif p_increment_type = 'P' then
3421 begin
3422
3423 if c_Elem_Rec.element_value is null then
3424 begin
3425
3426 for c_ElemRates_Rec in c_ElemRates (c_Elem_Rec.pay_element_id,
3427 c_Elem_Rec.pay_element_option_id,
3428 c_Elem_Rec.effective_start_date,
3429 c_Elem_Rec.effective_end_date) loop
3430
3431 if c_ElemRates_Rec.element_value_type = 'A' then
3432 begin
3433
3434 if FND_API.to_Boolean(p_compound_annually) then
3435 begin
3436 /* Bug 2820755 Start */
3437
3438 /* if p_increment_by < 1 then
3439 l_element_value := c_ElemRates_Rec.element_value * POWER(1 + p_increment_by, p_compound_factor);
3440 else
3441 l_element_value := c_ElemRates_Rec.element_value * POWER(1 + p_increment_by / 100, p_compound_factor);
3442 end if; */
3443
3444 fnd_file.put_line(fnd_file.log, 'source element value : '||c_ElemRates_Rec.element_value);
3445 fnd_file.put_line(fnd_file.log, 'increment factor : '||p_increment_by);
3446
3447 l_element_value
3448 := c_ElemRates_Rec.element_value * POWER(1 + p_increment_by / 100, p_compound_factor);
3449 /* Bug 2820755 End */
3450
3451 end;
3452 else
3453 begin
3454 /* Bug 2820755 Start */
3455
3456 /* if p_increment_by < 1 then
3457 l_element_value := c_ElemRates_Rec.element_value * (1 + p_increment_by);
3458 else
3459 l_element_value := c_ElemRates_Rec.element_value * (1 + p_increment_by / 100);
3460 end if; */
3461
3462 fnd_file.put_line(fnd_file.log, 'source element value : '||c_ElemRates_Rec.element_value);
3463 fnd_file.put_line(fnd_file.log, 'increment factor : '||p_increment_by);
3464
3465 l_element_value
3466 := c_ElemRates_Rec.element_value * (1 + p_increment_by / 100);
3467 /* Bug 2820755 End */
3468
3469 end;
3470 end if;
3471
3472 PSB_POSITIONS_PVT.Modify_Assignment
3473 (p_api_version => 1.0,
3474 p_return_status => l_return_status,
3475 p_msg_count => l_msg_count,
3476 p_msg_data => l_msg_data,
3477 p_position_assignment_id => l_posasgn_id,
3478 p_data_extract_id => p_data_extract_id,
3479 p_worksheet_id => p_worksheet_id,
3480 p_position_id => p_position_id,
3481 p_assignment_type => 'ELEMENT',
3482 p_attribute_id => null,
3483 p_attribute_value_id => null,
3484 p_attribute_value => null,
3485 p_pay_element_id => c_Elem_Rec.pay_element_id,
3486 p_pay_element_option_id => c_Elem_Rec.pay_element_option_id,
3487 p_effective_start_date => greatest(c_ElemRates_Rec.effective_start_date, p_start_date),
3488 p_effective_end_date => least(nvl(c_ElemRates_Rec.effective_end_date, p_end_date), p_end_date),
3489 p_element_value_type => c_ElemRates_Rec.element_value_type,
3490 p_element_value => l_element_value,
3491 p_currency_code => p_currency_code,
3492 p_pay_basis => c_ElemRates_Rec.pay_basis,
3493 p_global_default_flag => null,
3494 p_assignment_default_rule_id => null,
3495 p_modify_flag => null,
3496 p_rowid => l_rowid,
3497 p_employee_id => null,
3498 p_primary_employee_flag => null);
3499
3500 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3501 raise FND_API.G_EXC_ERROR;
3502 end if;
3503
3504 end;
3505 end if;
3506
3507 end loop;
3508 end;
3509 else
3510 begin
3511
3512 if c_Elem_Rec.element_value_type = 'A' then
3513 begin
3514
3515 if FND_API.to_Boolean(p_compound_annually) then
3516 begin
3517
3518 /* Start Bug No : 4281800 */
3519 -- commented for bug no 4281800
3520 /* if p_increment_by < 1 then
3521 l_element_value := c_Elem_Rec.element_value * POWER(1 + p_increment_by, p_compound_factor);
3522 else
3523 l_element_value := c_Elem_Rec.element_value * POWER(1 + p_increment_by / 100, p_compound_factor);
3524 end if; */
3525 /* End Bug No : 4281800 */
3526
3527 /* Start Bug No : 4281800 */
3528 fnd_file.put_line(fnd_file.log, 'source element value : '||c_Elem_Rec.element_value);
3529 fnd_file.put_line(fnd_file.log, 'increment factor : '||p_increment_by);
3530
3531 l_element_value
3532 := c_Elem_Rec.element_value * POWER(1 + p_increment_by / 100, p_compound_factor);
3533
3534 /* End Bug No : 4281800 */
3535
3536 end;
3537 else
3538 begin
3539
3540 /* Start Bug No : 4281800 */
3541 -- commented for bug no 4281800
3542 /* if p_increment_by < 1 then
3543 l_element_value := c_Elem_Rec.element_value * (1 + p_increment_by);
3544 else
3545 l_element_value := c_Elem_Rec.element_value * (1 + p_increment_by / 100);
3546 end if; */
3547 /* End Bug No : 4281800 */
3548
3549 /* Start Bug No : 4281800 */
3550
3551 fnd_file.put_line(fnd_file.log, 'source element value : '||c_Elem_Rec.element_value);
3552 fnd_file.put_line(fnd_file.log, 'increment factor : '||p_increment_by);
3553
3554 l_element_value
3555 := c_Elem_Rec.element_value * (1 + p_increment_by / 100);
3556
3557 /* End Bug No : 4281800 */
3558
3559 end;
3560 end if;
3561
3562 PSB_POSITIONS_PVT.Modify_Assignment
3563 (p_api_version => 1.0,
3564 p_return_status => l_return_status,
3565 p_msg_count => l_msg_count,
3566 p_msg_data => l_msg_data,
3567 p_position_assignment_id => l_posasgn_id,
3568 p_data_extract_id => p_data_extract_id,
3569 p_worksheet_id => p_worksheet_id,
3570 p_position_id => p_position_id,
3571 p_assignment_type => 'ELEMENT',
3572 p_attribute_id => null,
3573 p_attribute_value_id => null,
3574 p_attribute_value => null,
3575 p_pay_element_id => c_Elem_Rec.pay_element_id,
3576 p_pay_element_option_id => c_Elem_Rec.pay_element_option_id,
3577 p_effective_start_date => greatest(c_Elem_Rec.effective_start_date, p_start_date),
3578 p_effective_end_date => least(nvl(c_Elem_Rec.effective_end_date, p_end_date), p_end_date),
3579 p_element_value_type => c_Elem_Rec.element_value_type,
3580 p_element_value => l_element_value,
3581 p_currency_code => p_currency_code,
3582 p_pay_basis => c_Elem_Rec.pay_basis,
3583 p_global_default_flag => null,
3584 p_assignment_default_rule_id => null,
3585 p_modify_flag => null,
3586 p_rowid => l_rowid,
3587 p_employee_id => null,
3588 p_primary_employee_flag => null);
3589
3590 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3591 raise FND_API.G_EXC_ERROR;
3592 end if;
3593
3594 end;
3595 end if;
3596
3597 end;
3598 end if;
3599
3600 end;
3601
3602 /* For Bug No. 2263220 : Start */
3603 -- If Increment Type is 'Step' then, the parameter should not be applied to Non Grade Salary elements.
3604 --elsif p_increment_type = 'S' then
3605 elsif (p_increment_type = 'S' and c_Elem_Rec.option_flag = 'Y') then
3606 /* For Bug No. 2263220 : End */
3607
3608 begin
3609
3610 l_nextrate_found := FND_API.G_FALSE;
3611
3612 for c_ElemOptions_Rec in c_ElemOptions (c_Elem_Rec.pay_element_option_id) loop
3613 l_option_name := c_ElemOptions_Rec.name;
3614 l_sequence_number := c_ElemOptions_Rec.sequence_number;
3615 end loop;
3616
3617 if FND_API.to_Boolean(p_compound_annually) then
3618 l_increment_by := p_increment_by * p_compound_factor;
3619 else
3620 l_increment_by := p_increment_by;
3621 end if;
3622
3623 for l_num_steps in 1..l_increment_by loop
3624
3625 for c_NextOption_Rec in c_NextOption (c_Elem_Rec.pay_element_id,
3626 l_option_name,
3627 l_sequence_number) loop
3628
3629 l_nextrate_found := FND_API.G_TRUE;
3630 l_pay_element_option_id := c_NextOption_Rec.pay_element_option_id;
3631 l_sequence_number := c_NextOption_Rec.sequence_number;
3632 end loop;
3633
3634 end loop;
3635
3636 if not FND_API.to_Boolean(l_nextrate_found) then
3637 l_pay_element_option_id := c_Elem_Rec.pay_element_option_id;
3638 end if;
3639
3640 PSB_POSITIONS_PVT.Modify_Assignment
3641 (p_api_version => 1.0,
3642 p_return_status => l_return_status,
3643 p_msg_count => l_msg_count,
3644 p_msg_data => l_msg_data,
3645 p_position_assignment_id => l_posasgn_id,
3646 p_data_extract_id => p_data_extract_id,
3647 p_worksheet_id => p_worksheet_id,
3648 p_position_id => p_position_id,
3649 p_assignment_type => 'ELEMENT',
3650 p_attribute_id => null,
3651 p_attribute_value_id => null,
3652 p_attribute_value => null,
3653 p_pay_element_id => c_Elem_Rec.pay_element_id,
3654 p_pay_element_option_id => l_pay_element_option_id,
3655 p_effective_start_date => greatest(c_Elem_Rec.effective_start_date, p_start_date),
3656 p_effective_end_date => least(nvl(c_Elem_Rec.effective_end_date, p_end_date), p_end_date),
3657 p_element_value_type => null,
3658 p_element_value => null,
3659 p_currency_code => p_currency_code,
3660 p_pay_basis => null,
3661 p_global_default_flag => null,
3662 p_assignment_default_rule_id => null,
3663 p_modify_flag => null,
3664 p_rowid => l_rowid,
3665 p_employee_id => null,
3666 p_primary_employee_flag => null);
3667
3668 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3669 raise FND_API.G_EXC_ERROR;
3670 end if;
3671
3672 end;
3673
3674 end if;
3675
3676 end loop;
3677
3678
3679 -- Initialize API return status to success
3680
3681 p_return_status := FND_API.G_RET_STS_SUCCESS;
3682
3683
3684 EXCEPTION
3685
3686 when FND_API.G_EXC_ERROR then
3687 p_return_status := FND_API.G_RET_STS_ERROR;
3688 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
3689 p_data => l_msg_data);
3690
3691
3692 when FND_API.G_EXC_UNEXPECTED_ERROR then
3693 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3694 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
3695 p_data => l_msg_data);
3696
3697 when OTHERS then
3698 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3699
3700 if FND_MSG_PUB.Check_Msg_Level
3701 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3702 then
3703 FND_MSG_PUB.Add_Exc_Msg
3704 (p_pkg_name => G_PKG_NAME,
3705 p_procedure_name => l_api_name);
3706 end if;
3707
3708
3709 END Process_PosParam_AutoInc_Step;
3710
3711 /* ----------------------------------------------------------------------- */
3712
3713 PROCEDURE Redistribute_Follow_Salary
3714 ( p_api_version IN NUMBER,
3715 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
3716 p_return_status OUT NOCOPY VARCHAR2,
3717 p_worksheet_id IN NUMBER,
3718 p_position_line_id IN NUMBER,
3719 p_budget_year_id IN NUMBER := FND_API.G_MISS_NUM,
3720 p_service_package_id IN NUMBER,
3721 p_stage_set_id IN NUMBER,
3722 p_func_currency IN VARCHAR2 := FND_API.G_MISS_CHAR
3723 ) IS
3724
3725 l_api_name CONSTANT VARCHAR2(30) := 'Redistribute_Follow_Salary';
3726 l_api_version CONSTANT NUMBER := 1.0;
3727
3728 l_budget_calendar_id NUMBER;
3729 l_flex_mapping_set_id NUMBER;
3730 l_rounding_factor NUMBER;
3731 l_data_extract_id NUMBER;
3732 l_budget_group_id NUMBER;
3733 l_current_stage_seq NUMBER;
3734
3735 l_start_stage_seq NUMBER;
3736
3737 l_business_group_id NUMBER;
3738 l_flex_code NUMBER;
3739 l_func_currency VARCHAR2(15);
3740
3741 l_position_id NUMBER;
3742 l_position_start_date DATE;
3743 l_position_end_date DATE;
3744
3745 l_year_index BINARY_INTEGER;
3746 l_year_start_date DATE;
3747 l_year_end_date DATE;
3748
3749 l_return_status VARCHAR2(1);
3750
3751 cursor c_WS is
3752 select budget_calendar_id,
3753 flex_mapping_set_id,
3754 rounding_factor,
3755 nvl(data_extract_id, global_data_extract_id) data_extract_id,
3756 budget_group_id,
3757 current_stage_seq
3758 from PSB_WORKSHEETS_V
3759 where worksheet_id = p_worksheet_id;
3760
3761 cursor c_BG is
3762 select nvl(business_group_id, root_business_group_id) business_group_id,
3763 nvl(chart_of_accounts_id, root_chart_of_accounts_id) chart_of_accounts_id,
3764 nvl(currency_code, root_currency_code) currency_code
3765 from PSB_BUDGET_GROUPS_V
3766 where budget_group_id = l_budget_group_id;
3767
3768 cursor c_Positions is
3769 select a.position_id,
3770 a.effective_start_date,
3771 a.effective_end_date
3772 from PSB_POSITIONS a,
3773 PSB_WS_POSITION_LINES b
3774 where a.position_id = b.position_id
3775 and b.position_line_id = p_position_line_id;
3776
3777 cursor c_Year is
3778 select start_date,
3779 end_date
3780 from PSB_BUDGET_PERIODS
3781 where budget_period_id = p_budget_year_id;
3782
3783 BEGIN
3784
3785 -- Standard call to check for call compatibility.
3786
3787 if not FND_API.Compatible_API_Call (l_api_version,
3788 p_api_version,
3789 l_api_name,
3790 G_PKG_NAME)
3791 then
3792 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3793 end if;
3794
3795 for c_WS_Rec in c_WS loop
3796 l_budget_calendar_id := c_WS_Rec.budget_calendar_id;
3797 l_flex_mapping_set_id := c_WS_Rec.flex_mapping_set_id;
3798 l_rounding_factor := c_WS_Rec.rounding_factor;
3799 l_data_extract_id := c_WS_Rec.data_extract_id;
3800 l_budget_group_id := c_WS_Rec.budget_group_id;
3801 l_current_stage_seq := c_WS_Rec.current_stage_seq;
3802 end loop;
3803
3804 l_start_stage_seq := l_current_stage_seq;
3805
3806 for c_BG_Rec in c_BG loop
3807 l_business_group_id := c_BG_Rec.business_group_id;
3808 l_flex_code := c_BG_Rec.chart_of_accounts_id;
3809 l_func_currency := c_BG_Rec.currency_code;
3810 end loop;
3811
3812 if p_func_currency <> FND_API.G_MISS_CHAR then
3813 l_func_currency := p_func_currency;
3814 end if;
3815
3816 for c_Positions_Rec in c_Positions loop
3817 l_position_id := c_Positions_Rec.position_id;
3818 l_position_start_date := c_Positions_Rec.effective_start_date;
3819 l_position_end_date := c_Positions_Rec.effective_end_date;
3820 end loop;
3821
3822 if ((nvl(PSB_WS_POS1.g_data_extract_id, FND_API.G_MISS_NUM) <> l_data_extract_id) or
3823 (nvl(PSB_WS_POS1.g_business_group_id, FND_API.G_MISS_NUM) <> l_business_group_id)) then
3824 begin
3825
3826 PSB_WS_POS1.Cache_Elements
3827 (p_return_status => l_return_status,
3828 p_data_extract_id => l_data_extract_id,
3829 p_business_group_id => l_business_group_id,
3830 p_worksheet_id => p_worksheet_id);
3831
3832 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3833 raise FND_API.G_EXC_ERROR;
3834 end if;
3835
3836 end;
3837 end if;
3838
3839 if l_flex_code <> nvl(PSB_WS_ACCT1.g_flex_code, FND_API.G_MISS_NUM) then
3840 begin
3841
3842 PSB_WS_ACCT1.Flex_Info
3843 (p_return_status => l_return_status,
3844 p_flex_code => l_flex_code);
3845
3846 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3847 raise FND_API.G_EXC_ERROR;
3848 end if;
3849
3850 end;
3851 end if;
3852
3853 if p_budget_year_id <> FND_API.G_MISS_NUM then
3854 begin
3855
3856 for c_Year_Rec in c_Year loop
3857 l_year_start_date := c_Year_Rec.start_date;
3858 l_year_end_date := c_Year_Rec.end_date;
3859 end loop;
3860
3861 PSB_WS_POS1.g_salary_budget_group_id := null;
3862
3863 Redist_Follow_Salary_Year
3864 (p_return_status => l_return_status,
3865 p_worksheet_id => p_worksheet_id,
3866 p_flex_mapping_set_id => l_flex_mapping_set_id,
3867 p_rounding_factor => l_rounding_factor,
3868 p_data_extract_id => l_data_extract_id,
3869 p_business_group_id => l_business_group_id,
3870 p_flex_code => l_flex_code,
3871 p_position_line_id => p_position_line_id,
3872 p_position_id => l_position_id,
3873 p_position_start_date => l_position_start_date,
3874 p_position_end_date => l_position_end_date,
3875 p_budget_year_id => p_budget_year_id,
3876 p_year_start_date => l_year_start_date,
3877 p_year_end_date => l_year_end_date,
3878 p_service_package_id => p_service_package_id,
3879 p_stage_set_id => p_stage_set_id,
3880 p_start_stage_seq => l_start_stage_seq,
3881 p_current_stage_seq => l_current_stage_seq,
3882 p_func_currency => l_func_currency);
3883
3884 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3885 raise FND_API.G_EXC_ERROR;
3886 end if;
3887
3888 end;
3889 else
3890 begin
3891
3892 if l_budget_calendar_id <> nvl(PSB_WS_ACCT1.g_budget_calendar_id, FND_API.G_MISS_NUM) then
3893 begin
3894
3895 PSB_WS_ACCT1.Cache_Budget_Calendar
3896 (p_return_status => l_return_status,
3897 p_budget_calendar_id => l_budget_calendar_id);
3898
3899 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3900 raise FND_API.G_EXC_ERROR;
3901 end if;
3902
3903 end;
3904 end if;
3905
3906 for l_year_index in 1..PSB_WS_ACCT1.g_num_budget_years loop
3907
3908 PSB_WS_POS1.g_salary_budget_group_id := null;
3909
3910 Redist_Follow_Salary_Year
3911 (p_return_status => l_return_status,
3912 p_worksheet_id => p_worksheet_id,
3913 p_flex_mapping_set_id => l_flex_mapping_set_id,
3914 p_rounding_factor => l_rounding_factor,
3915 p_data_extract_id => l_data_extract_id,
3916 p_business_group_id => l_business_group_id,
3917 p_flex_code => l_flex_code,
3918 p_position_line_id => p_position_line_id,
3919 p_position_id => l_position_id,
3920 p_position_start_date => l_position_start_date,
3921 p_position_end_date => l_position_end_date,
3922 p_budget_year_id => PSB_WS_ACCT1.g_budget_years(l_year_index).budget_year_id,
3923 p_year_start_date => PSB_WS_ACCT1.g_budget_years(l_year_index).start_date,
3924 p_year_end_date => PSB_WS_ACCT1.g_budget_years(l_year_index).end_date,
3925 p_service_package_id => p_service_package_id,
3926 p_stage_set_id => p_stage_set_id,
3927 p_start_stage_seq => l_start_stage_seq,
3928 p_current_stage_seq => l_current_stage_seq,
3929 p_func_currency => l_func_currency);
3930
3931 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3932 raise FND_API.G_EXC_ERROR;
3933 end if;
3934
3935 end loop;
3936
3937 end;
3938 end if;
3939
3940 p_return_status := FND_API.G_RET_STS_SUCCESS;
3941
3942
3943 EXCEPTION
3944
3945 when FND_API.G_EXC_ERROR then
3946 p_return_status := FND_API.G_RET_STS_ERROR;
3947
3948 when FND_API.G_EXC_UNEXPECTED_ERROR then
3949 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3950
3951 when OTHERS then
3952 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3953
3954 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
3955 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
3956 l_api_name);
3957 end if;
3958
3959 END Redistribute_Follow_Salary;
3960
3961 /* ----------------------------------------------------------------------- */
3962
3963 PROCEDURE Redist_Follow_Salary_Year
3964 ( p_return_status OUT NOCOPY VARCHAR2,
3965 p_worksheet_id IN NUMBER,
3966 p_flex_mapping_set_id IN NUMBER,
3967 p_rounding_factor IN NUMBER,
3968 p_data_extract_id IN NUMBER,
3969 p_business_group_id IN NUMBER,
3970 p_flex_code IN NUMBER,
3971 p_position_line_id IN NUMBER,
3972 p_position_id IN NUMBER,
3973 p_position_start_date IN DATE,
3974 p_position_end_date IN DATE,
3975 p_budget_year_id IN NUMBER,
3976 p_year_start_date IN DATE,
3977 p_year_end_date IN DATE,
3978 p_service_package_id IN NUMBER,
3979 p_stage_set_id IN NUMBER,
3980 p_start_stage_seq IN NUMBER,
3981 p_current_stage_seq IN NUMBER,
3982 p_func_currency IN VARCHAR2
3983 ) IS
3984
3985 l_start_date DATE;
3986 l_end_date DATE;
3987
3988 l_account_line_id NUMBER;
3989 l_ytd_amount NUMBER;
3990 l_period_amount PSB_WS_ACCT1.g_prdamt_tbl_type;
3991
3992 l_init_index BINARY_INTEGER;
3993 l_element_index BINARY_INTEGER;
3994 l_pdist_index BINARY_INTEGER;
3995 l_eldist_index BINARY_INTEGER;
3996 l_dist_index BINARY_INTEGER;
3997
3998 l_return_status VARCHAR2(1);
3999
4000 cursor c_Salary_Dist is
4001 select /*+ ORDERED INDEX(a PSB_WS_ACCOUNT_LINES_N5) */
4002 code_combination_id,
4003 budget_group_id,
4004 ytd_amount
4005 from PSB_WS_ACCOUNT_LINES a
4006 where salary_account_line = 'Y'
4007 and p_current_stage_seq between start_stage_seq and current_stage_seq
4008 and currency_code = p_func_currency
4009 and stage_set_id = p_stage_set_id
4010 and service_package_id = p_service_package_id
4011 and budget_year_id = p_budget_year_id
4012 and position_line_id = p_position_line_id;
4013
4014 cursor c_Element_Cost is
4015 select /*+ ORDERED USE_NL(a b) INDEX(a PSB_WS_ELEMENT_LINES_N1) INDEX(b PSB_PAY_ELEMENTS_U1) */
4016 a.pay_element_id,
4017 a.element_set_id,
4018 a.element_cost
4019 from PSB_WS_ELEMENT_LINES a,
4020 PSB_PAY_ELEMENTS b
4021 where p_current_stage_seq between a.start_stage_seq and a.current_stage_seq
4022 and a.currency_code = p_func_currency
4023 and a.pay_element_id = b.pay_element_id
4024 and a.stage_set_id = p_stage_set_id
4025 and a.service_package_id = p_service_package_id
4026 and a.budget_year_id = p_budget_year_id
4027 and a.position_line_id = p_position_line_id
4028 and b.follow_salary = 'Y'
4029 and b.business_group_id = p_business_group_id
4030 and b.data_extract_id = p_data_extract_id;
4031
4032 cursor c_Element_Dist is
4033 select /*+ ORDERED USE_NL(a b c) INDEX(a PSB_WS_ACCOUNT_LINES_N5) INDEX(b PSB_WS_ELEMENT_LINES_N1) INDEX(c PSB_PAY_ELEMENTS_U1) */
4034 a.account_line_id,
4035 a.code_combination_id,
4036 a.ytd_amount,
4037 a.period1_amount, a.period2_amount, a.period3_amount,
4038 a.period4_amount, a.period5_amount, a.period6_amount,
4039 a.period7_amount, a.period8_amount, a.period9_amount,
4040 a.period10_amount, a.period11_amount, a.period12_amount,
4041 a.period13_amount, a.period14_amount, a.period15_amount,
4042 a.period16_amount, a.period17_amount, a.period18_amount,
4043 a.period19_amount, a.period20_amount, a.period21_amount,
4044 a.period22_amount, a.period23_amount, a.period24_amount,
4045 a.period25_amount, a.period26_amount, a.period27_amount,
4046 a.period28_amount, a.period29_amount, a.period30_amount,
4047 a.period31_amount, a.period32_amount, a.period33_amount,
4048 a.period34_amount, a.period35_amount, a.period36_amount,
4049 a.period37_amount, a.period38_amount, a.period39_amount,
4050 a.period40_amount, a.period41_amount, a.period42_amount,
4051 a.period43_amount, a.period44_amount, a.period45_amount,
4052 a.period46_amount, a.period47_amount, a.period48_amount,
4053 a.period49_amount, a.period50_amount, a.period51_amount,
4054 a.period52_amount, a.period53_amount, a.period54_amount,
4055 a.period55_amount, a.period56_amount, a.period57_amount,
4056 a.period58_amount, a.period59_amount, a.period60_amount
4057 from PSB_WS_ACCOUNT_LINES a,
4058 PSB_WS_ELEMENT_LINES b,
4059 PSB_PAY_ELEMENTS c
4060 where a.element_set_id = b.element_set_id
4061 and p_current_stage_seq between a.start_stage_seq and a.current_stage_seq
4062 and a.stage_set_id = p_stage_set_id
4063 and a.service_package_id = p_service_package_id
4064 and a.budget_year_id = p_budget_year_id
4065 and a.position_line_id = p_position_line_id
4066 and p_current_stage_seq between b.start_stage_seq and b.current_stage_seq
4067 and b.currency_code = p_func_currency
4068 and b.pay_element_id = c.pay_element_id
4069 and b.stage_set_id = p_stage_set_id
4070 and b.service_package_id = p_service_package_id
4071 and b.budget_year_id = p_budget_year_id
4072 and b.position_line_id = p_position_line_id
4073 and c.follow_salary = 'Y'
4074 and c.business_group_id = p_business_group_id
4075 and c.data_extract_id = p_data_extract_id;
4076
4077 BEGIN
4078
4079 PSB_WS_POS1.Initialize_Calc;
4080
4081 PSB_WS_POS1.Initialize_Dist;
4082
4083 PSB_WS_POS1.Initialize_Salary_Dist;
4084
4085 PSB_WS_POS1.Initialize_Element_Dist;
4086
4087 for c_Salary_Rec in c_Salary_Dist loop
4088
4089 PSB_WS_POS1.g_num_salary_dist := PSB_WS_POS1.g_num_salary_dist + 1;
4090 l_ytd_amount := nvl(l_ytd_amount, 0) + c_Salary_Rec.ytd_amount;
4091
4092 PSB_WS_POS1.g_salary_dist(PSB_WS_POS1.g_num_salary_dist).ccid := c_Salary_Rec.code_combination_id;
4093 PSB_WS_POS1.g_salary_dist(PSB_WS_POS1.g_num_salary_dist).amount := c_Salary_Rec.ytd_amount;
4094 PSB_WS_POS1.g_salary_dist(PSB_WS_POS1.g_num_salary_dist).start_date := p_year_start_date;
4095 PSB_WS_POS1.g_salary_dist(PSB_WS_POS1.g_num_salary_dist).end_date := p_year_end_date;
4096
4097 if nvl(PSB_WS_POS1.g_salary_budget_group_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM then
4098 PSB_WS_POS1.g_salary_budget_group_id := c_Salary_Rec.budget_group_id;
4099 end if;
4100
4101 end loop;
4102
4103 for l_salary_index in 1..PSB_WS_POS1.g_num_salary_dist loop
4104
4105 if l_ytd_amount = 0 then
4106 PSB_WS_POS1.g_salary_dist(l_salary_index).percent := 0;
4107 else
4108 PSB_WS_POS1.g_salary_dist(l_salary_index).percent := PSB_WS_POS1.g_salary_dist(l_salary_index).amount / l_ytd_amount * 100;
4109 end if;
4110
4111 end loop;
4112
4113 for c_Element_Cost_Rec in c_Element_Cost loop
4114
4115 PSB_WS_POS1.g_num_pc_costs := PSB_WS_POS1.g_num_pc_costs + 1;
4116
4117 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).pay_element_id := c_Element_Cost_Rec.pay_element_id;
4118 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).element_set_id := c_Element_Cost_Rec.element_set_id;
4119 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).element_cost := c_Element_Cost_Rec.element_cost;
4120 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).budget_year_id := p_budget_year_id;
4121
4122 end loop;
4123
4124 for c_Element_Dist_Rec in c_Element_Dist loop
4125
4126 PSB_WS_POS1.g_num_element_dist := PSB_WS_POS1.g_num_element_dist + 1;
4127
4128 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).account_line_id := c_Element_Dist_Rec.account_line_id;
4129 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).ccid := c_Element_Dist_Rec.code_combination_id;
4130 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).ytd_amount := c_Element_Dist_Rec.ytd_amount;
4131 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period1_amount := c_Element_Dist_Rec.period1_amount;
4132 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period2_amount := c_Element_Dist_Rec.period2_amount;
4133 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period3_amount := c_Element_Dist_Rec.period3_amount;
4134 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period4_amount := c_Element_Dist_Rec.period4_amount;
4135 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period5_amount := c_Element_Dist_Rec.period5_amount;
4136 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period6_amount := c_Element_Dist_Rec.period6_amount;
4137 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period7_amount := c_Element_Dist_Rec.period7_amount;
4138 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period8_amount := c_Element_Dist_Rec.period8_amount;
4139 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period9_amount := c_Element_Dist_Rec.period9_amount;
4140 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period10_amount := c_Element_Dist_Rec.period10_amount;
4141 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period11_amount := c_Element_Dist_Rec.period11_amount;
4142 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period12_amount := c_Element_Dist_Rec.period12_amount;
4143 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period13_amount := c_Element_Dist_Rec.period13_amount;
4144 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period14_amount := c_Element_Dist_Rec.period14_amount;
4145 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period15_amount := c_Element_Dist_Rec.period15_amount;
4146 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period16_amount := c_Element_Dist_Rec.period16_amount;
4147 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period17_amount := c_Element_Dist_Rec.period17_amount;
4148 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period18_amount := c_Element_Dist_Rec.period18_amount;
4149 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period19_amount := c_Element_Dist_Rec.period19_amount;
4150 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period20_amount := c_Element_Dist_Rec.period20_amount;
4151 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period21_amount := c_Element_Dist_Rec.period21_amount;
4152 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period22_amount := c_Element_Dist_Rec.period22_amount;
4153 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period23_amount := c_Element_Dist_Rec.period23_amount;
4154 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period24_amount := c_Element_Dist_Rec.period24_amount;
4155 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period25_amount := c_Element_Dist_Rec.period25_amount;
4156 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period26_amount := c_Element_Dist_Rec.period26_amount;
4157 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period27_amount := c_Element_Dist_Rec.period27_amount;
4158 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period28_amount := c_Element_Dist_Rec.period28_amount;
4159 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period29_amount := c_Element_Dist_Rec.period29_amount;
4160 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period30_amount := c_Element_Dist_Rec.period30_amount;
4161 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period31_amount := c_Element_Dist_Rec.period31_amount;
4162 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period32_amount := c_Element_Dist_Rec.period32_amount;
4163 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period33_amount := c_Element_Dist_Rec.period33_amount;
4164 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period34_amount := c_Element_Dist_Rec.period34_amount;
4165 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period35_amount := c_Element_Dist_Rec.period35_amount;
4166 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period36_amount := c_Element_Dist_Rec.period36_amount;
4167 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period37_amount := c_Element_Dist_Rec.period37_amount;
4168 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period38_amount := c_Element_Dist_Rec.period38_amount;
4169 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period39_amount := c_Element_Dist_Rec.period39_amount;
4170 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period40_amount := c_Element_Dist_Rec.period40_amount;
4171 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period41_amount := c_Element_Dist_Rec.period41_amount;
4172 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period42_amount := c_Element_Dist_Rec.period42_amount;
4173 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period43_amount := c_Element_Dist_Rec.period43_amount;
4174 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period44_amount := c_Element_Dist_Rec.period44_amount;
4175 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period45_amount := c_Element_Dist_Rec.period45_amount;
4176 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period46_amount := c_Element_Dist_Rec.period46_amount;
4177 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period47_amount := c_Element_Dist_Rec.period47_amount;
4178 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period48_amount := c_Element_Dist_Rec.period48_amount;
4179 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period49_amount := c_Element_Dist_Rec.period49_amount;
4180 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period50_amount := c_Element_Dist_Rec.period50_amount;
4181 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period51_amount := c_Element_Dist_Rec.period51_amount;
4182 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period52_amount := c_Element_Dist_Rec.period52_amount;
4183 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period53_amount := c_Element_Dist_Rec.period53_amount;
4184 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period54_amount := c_Element_Dist_Rec.period54_amount;
4185 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period55_amount := c_Element_Dist_Rec.period55_amount;
4186 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period56_amount := c_Element_Dist_Rec.period56_amount;
4187 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period57_amount := c_Element_Dist_Rec.period57_amount;
4188 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period58_amount := c_Element_Dist_Rec.period58_amount;
4189 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period59_amount := c_Element_Dist_Rec.period59_amount;
4190 PSB_WS_POS1.g_element_dist(PSB_WS_POS1.g_num_element_dist).period60_amount := c_Element_Dist_Rec.period60_amount;
4191
4192 end loop;
4193
4194 l_start_date := greatest(p_year_start_date, p_position_start_date);
4195 l_end_date := least(p_year_end_date, nvl(p_position_end_date, p_year_end_date));
4196
4197 for l_element_index in 1..PSB_WS_POS1.g_num_elements loop
4198
4199 if PSB_WS_POS1.g_elements(l_element_index).follow_salary = 'Y' then
4200 begin
4201
4202 PSB_WS_POS1.Distribute_Following_Elements
4203 (p_return_status => l_return_status,
4204 p_redistribute => FND_API.G_TRUE,
4205 p_pay_element_id => PSB_WS_POS1.g_elements(l_element_index).pay_element_id,
4206 p_data_extract_id => p_data_extract_id,
4207 p_flex_code => p_flex_code,
4208 p_business_group_id => p_business_group_id,
4209 p_rounding_factor => p_rounding_factor,
4210 p_position_line_id => p_position_line_id,
4211 p_position_id => p_position_id,
4212 p_budget_year_id => p_budget_year_id,
4213 p_start_date => l_start_date,
4214 p_end_date => l_end_date);
4215
4216 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
4217 raise FND_API.G_EXC_ERROR;
4218 end if;
4219
4220 end;
4221 end if;
4222
4223 end loop;
4224
4225 for l_pdist_index in 1..PSB_WS_POS1.g_num_pd_costs loop
4226
4227 l_dist_index := null;
4228
4229 for l_eldist_index in 1..PSB_WS_POS1.g_num_element_dist loop
4230
4231 if PSB_WS_POS1.g_pd_costs(l_pdist_index).ccid = PSB_WS_POS1.g_element_dist(l_eldist_index).ccid then
4232 PSB_WS_POS1.g_element_dist(l_eldist_index).redist_flag := 'Y';
4233 l_dist_index := l_eldist_index;
4234 exit;
4235 end if;
4236
4237 end loop;
4238
4239 if l_dist_index is null then
4240 begin
4241
4242 for l_init_index in 1..PSB_WS_ACCT1.g_max_num_amounts loop
4243 l_period_amount(l_init_index) := null;
4244 end loop;
4245
4246 if PSB_WS_POS1.g_num_element_dist > 0 then
4247 begin
4248
4249 l_period_amount(1) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4250 PSB_WS_POS1.g_element_dist(1).period1_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4251 l_period_amount(2) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4252 PSB_WS_POS1.g_element_dist(1).period2_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4253 l_period_amount(3) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4254 PSB_WS_POS1.g_element_dist(1).period3_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4255 l_period_amount(4) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4256 PSB_WS_POS1.g_element_dist(1).period4_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4257 l_period_amount(5) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4258 PSB_WS_POS1.g_element_dist(1).period5_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4259 l_period_amount(6) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4260 PSB_WS_POS1.g_element_dist(1).period6_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4261 l_period_amount(7) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4262 PSB_WS_POS1.g_element_dist(1).period7_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4263 l_period_amount(8) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4264 PSB_WS_POS1.g_element_dist(1).period8_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4265 l_period_amount(9) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4266 PSB_WS_POS1.g_element_dist(1).period9_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4267 l_period_amount(10) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4268 PSB_WS_POS1.g_element_dist(1).period10_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4269 l_period_amount(11) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4270 PSB_WS_POS1.g_element_dist(1).period11_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4271 l_period_amount(12) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4272 PSB_WS_POS1.g_element_dist(1).period12_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4273 l_period_amount(13) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4274 PSB_WS_POS1.g_element_dist(1).period13_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4275 l_period_amount(14) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4276 PSB_WS_POS1.g_element_dist(1).period14_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4277 l_period_amount(15) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4278 PSB_WS_POS1.g_element_dist(1).period15_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4279 l_period_amount(16) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4280 PSB_WS_POS1.g_element_dist(1).period16_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4281 l_period_amount(17) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4282 PSB_WS_POS1.g_element_dist(1).period17_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4283 l_period_amount(18) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4284 PSB_WS_POS1.g_element_dist(1).period18_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4285 l_period_amount(19) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4286 PSB_WS_POS1.g_element_dist(1).period19_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4287 l_period_amount(20) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4288 PSB_WS_POS1.g_element_dist(1).period20_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4289 l_period_amount(21) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4290 PSB_WS_POS1.g_element_dist(1).period21_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4291 l_period_amount(22) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4292 PSB_WS_POS1.g_element_dist(1).period22_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4293 l_period_amount(23) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4294 PSB_WS_POS1.g_element_dist(1).period23_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4295 l_period_amount(24) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4296 PSB_WS_POS1.g_element_dist(1).period24_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4297 l_period_amount(25) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4298 PSB_WS_POS1.g_element_dist(1).period25_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4299 l_period_amount(26) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4300 PSB_WS_POS1.g_element_dist(1).period26_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4301 l_period_amount(27) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4302 PSB_WS_POS1.g_element_dist(1).period27_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4303 l_period_amount(28) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4304 PSB_WS_POS1.g_element_dist(1).period28_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4305 l_period_amount(29) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4306 PSB_WS_POS1.g_element_dist(1).period29_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4307 l_period_amount(30) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4308 PSB_WS_POS1.g_element_dist(1).period30_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4309 l_period_amount(31) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4310 PSB_WS_POS1.g_element_dist(1).period31_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4311 l_period_amount(32) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4312 PSB_WS_POS1.g_element_dist(1).period32_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4313 l_period_amount(33) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4314 PSB_WS_POS1.g_element_dist(1).period33_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4315 l_period_amount(34) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4316 PSB_WS_POS1.g_element_dist(1).period34_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4317 l_period_amount(35) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4318 PSB_WS_POS1.g_element_dist(1).period35_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4319 l_period_amount(36) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4320 PSB_WS_POS1.g_element_dist(1).period36_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4321 l_period_amount(37) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4322 PSB_WS_POS1.g_element_dist(1).period37_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4323 l_period_amount(38) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4324 PSB_WS_POS1.g_element_dist(1).period38_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4325 l_period_amount(39) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4326 PSB_WS_POS1.g_element_dist(1).period39_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4327 l_period_amount(40) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4328 PSB_WS_POS1.g_element_dist(1).period40_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4329 l_period_amount(41) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4330 PSB_WS_POS1.g_element_dist(1).period41_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4331 l_period_amount(42) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4332 PSB_WS_POS1.g_element_dist(1).period42_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4333 l_period_amount(43) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4334 PSB_WS_POS1.g_element_dist(1).period43_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4335 l_period_amount(44) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4336 PSB_WS_POS1.g_element_dist(1).period44_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4337 l_period_amount(45) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4338 PSB_WS_POS1.g_element_dist(1).period45_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4339 l_period_amount(46) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4340 PSB_WS_POS1.g_element_dist(1).period46_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4341 l_period_amount(47) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4342 PSB_WS_POS1.g_element_dist(1).period47_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4343 l_period_amount(48) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4344 PSB_WS_POS1.g_element_dist(1).period48_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4345 l_period_amount(49) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4346 PSB_WS_POS1.g_element_dist(1).period49_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4347 l_period_amount(50) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4348 PSB_WS_POS1.g_element_dist(1).period50_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4349 l_period_amount(51) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4350 PSB_WS_POS1.g_element_dist(1).period51_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4351 l_period_amount(52) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4352 PSB_WS_POS1.g_element_dist(1).period52_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4353 l_period_amount(53) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4354 PSB_WS_POS1.g_element_dist(1).period53_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4355 l_period_amount(54) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4356 PSB_WS_POS1.g_element_dist(1).period54_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4357 l_period_amount(55) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4358 PSB_WS_POS1.g_element_dist(1).period55_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4359 l_period_amount(56) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4360 PSB_WS_POS1.g_element_dist(1).period56_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4361 l_period_amount(57) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4362 PSB_WS_POS1.g_element_dist(1).period57_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4363 l_period_amount(58) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4364 PSB_WS_POS1.g_element_dist(1).period58_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4365 l_period_amount(59) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4366 PSB_WS_POS1.g_element_dist(1).period59_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4367 l_period_amount(60) := PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount *
4368 PSB_WS_POS1.g_element_dist(1).period60_amount / PSB_WS_POS1.g_element_dist(1).ytd_amount;
4369
4370 end;
4371 end if;
4372
4373 PSB_WS_ACCT1.Create_Account_Dist
4374 (p_api_version => 1.0,
4375 p_return_status => l_return_status,
4376 p_account_line_id => l_account_line_id,
4377 p_worksheet_id => p_worksheet_id,
4378 p_flex_mapping_set_id => p_flex_mapping_set_id,
4379 p_map_accounts => TRUE,
4380 p_check_spal_exists => FND_API.G_FALSE,
4381 p_gl_cutoff_period => null,
4382 p_allocrule_set_id => null,
4383 p_budget_calendar_id => null,
4384 p_rounding_factor => p_rounding_factor,
4385 p_stage_set_id => p_stage_set_id,
4386 p_budget_year_id => p_budget_year_id,
4387 p_budget_group_id => PSB_WS_POS1.g_salary_budget_group_id,
4388 p_ccid => PSB_WS_POS1.g_pd_costs(l_pdist_index).ccid,
4389 p_currency_code => p_func_currency,
4390 p_balance_type => 'E',
4391 p_ytd_amount => PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount,
4392 p_period_amount => l_period_amount,
4393 p_position_line_id => p_position_line_id,
4394 p_element_set_id => PSB_WS_POS1.g_pd_costs(l_pdist_index).element_set_id,
4395 p_service_package_id => p_service_package_id,
4396 p_start_stage_seq => p_start_stage_seq,
4397 p_current_stage_seq => p_current_stage_seq);
4398
4399 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
4400 raise FND_API.G_EXC_ERROR;
4401 end if;
4402
4403 end;
4404 else
4405 begin
4406
4407 for l_init_index in 1..PSB_WS_ACCT1.g_max_num_amounts loop
4408 l_period_amount(l_init_index) := null;
4409 end loop;
4410
4411 l_period_amount(1) := PSB_WS_POS1.g_element_dist(l_dist_index).period1_amount;
4412 l_period_amount(2) := PSB_WS_POS1.g_element_dist(l_dist_index).period2_amount;
4413 l_period_amount(3) := PSB_WS_POS1.g_element_dist(l_dist_index).period3_amount;
4414 l_period_amount(4) := PSB_WS_POS1.g_element_dist(l_dist_index).period4_amount;
4415 l_period_amount(5) := PSB_WS_POS1.g_element_dist(l_dist_index).period5_amount;
4416 l_period_amount(6) := PSB_WS_POS1.g_element_dist(l_dist_index).period6_amount;
4417 l_period_amount(7) := PSB_WS_POS1.g_element_dist(l_dist_index).period7_amount;
4418 l_period_amount(8) := PSB_WS_POS1.g_element_dist(l_dist_index).period8_amount;
4419 l_period_amount(9) := PSB_WS_POS1.g_element_dist(l_dist_index).period9_amount;
4420 l_period_amount(10) := PSB_WS_POS1.g_element_dist(l_dist_index).period10_amount;
4421 l_period_amount(11) := PSB_WS_POS1.g_element_dist(l_dist_index).period11_amount;
4422 l_period_amount(12) := PSB_WS_POS1.g_element_dist(l_dist_index).period12_amount;
4423 l_period_amount(13) := PSB_WS_POS1.g_element_dist(l_dist_index).period13_amount;
4424 l_period_amount(14) := PSB_WS_POS1.g_element_dist(l_dist_index).period14_amount;
4425 l_period_amount(15) := PSB_WS_POS1.g_element_dist(l_dist_index).period15_amount;
4426 l_period_amount(16) := PSB_WS_POS1.g_element_dist(l_dist_index).period16_amount;
4427 l_period_amount(17) := PSB_WS_POS1.g_element_dist(l_dist_index).period17_amount;
4428 l_period_amount(18) := PSB_WS_POS1.g_element_dist(l_dist_index).period18_amount;
4429 l_period_amount(19) := PSB_WS_POS1.g_element_dist(l_dist_index).period19_amount;
4430 l_period_amount(20) := PSB_WS_POS1.g_element_dist(l_dist_index).period20_amount;
4431 l_period_amount(21) := PSB_WS_POS1.g_element_dist(l_dist_index).period21_amount;
4432 l_period_amount(22) := PSB_WS_POS1.g_element_dist(l_dist_index).period22_amount;
4433 l_period_amount(23) := PSB_WS_POS1.g_element_dist(l_dist_index).period23_amount;
4434 l_period_amount(24) := PSB_WS_POS1.g_element_dist(l_dist_index).period24_amount;
4435 l_period_amount(25) := PSB_WS_POS1.g_element_dist(l_dist_index).period25_amount;
4436 l_period_amount(26) := PSB_WS_POS1.g_element_dist(l_dist_index).period26_amount;
4437 l_period_amount(27) := PSB_WS_POS1.g_element_dist(l_dist_index).period27_amount;
4438 l_period_amount(28) := PSB_WS_POS1.g_element_dist(l_dist_index).period28_amount;
4439 l_period_amount(29) := PSB_WS_POS1.g_element_dist(l_dist_index).period29_amount;
4440 l_period_amount(30) := PSB_WS_POS1.g_element_dist(l_dist_index).period30_amount;
4441 l_period_amount(31) := PSB_WS_POS1.g_element_dist(l_dist_index).period31_amount;
4442 l_period_amount(32) := PSB_WS_POS1.g_element_dist(l_dist_index).period32_amount;
4443 l_period_amount(33) := PSB_WS_POS1.g_element_dist(l_dist_index).period33_amount;
4444 l_period_amount(34) := PSB_WS_POS1.g_element_dist(l_dist_index).period34_amount;
4445 l_period_amount(35) := PSB_WS_POS1.g_element_dist(l_dist_index).period35_amount;
4446 l_period_amount(36) := PSB_WS_POS1.g_element_dist(l_dist_index).period36_amount;
4447 l_period_amount(37) := PSB_WS_POS1.g_element_dist(l_dist_index).period37_amount;
4448 l_period_amount(38) := PSB_WS_POS1.g_element_dist(l_dist_index).period38_amount;
4449 l_period_amount(39) := PSB_WS_POS1.g_element_dist(l_dist_index).period39_amount;
4450 l_period_amount(40) := PSB_WS_POS1.g_element_dist(l_dist_index).period40_amount;
4451 l_period_amount(41) := PSB_WS_POS1.g_element_dist(l_dist_index).period41_amount;
4452 l_period_amount(42) := PSB_WS_POS1.g_element_dist(l_dist_index).period42_amount;
4453 l_period_amount(43) := PSB_WS_POS1.g_element_dist(l_dist_index).period43_amount;
4454 l_period_amount(44) := PSB_WS_POS1.g_element_dist(l_dist_index).period44_amount;
4455 l_period_amount(45) := PSB_WS_POS1.g_element_dist(l_dist_index).period45_amount;
4456 l_period_amount(46) := PSB_WS_POS1.g_element_dist(l_dist_index).period46_amount;
4457 l_period_amount(47) := PSB_WS_POS1.g_element_dist(l_dist_index).period47_amount;
4458 l_period_amount(48) := PSB_WS_POS1.g_element_dist(l_dist_index).period48_amount;
4459 l_period_amount(49) := PSB_WS_POS1.g_element_dist(l_dist_index).period49_amount;
4460 l_period_amount(50) := PSB_WS_POS1.g_element_dist(l_dist_index).period50_amount;
4461 l_period_amount(51) := PSB_WS_POS1.g_element_dist(l_dist_index).period51_amount;
4462 l_period_amount(52) := PSB_WS_POS1.g_element_dist(l_dist_index).period52_amount;
4463 l_period_amount(53) := PSB_WS_POS1.g_element_dist(l_dist_index).period53_amount;
4464 l_period_amount(54) := PSB_WS_POS1.g_element_dist(l_dist_index).period54_amount;
4465 l_period_amount(55) := PSB_WS_POS1.g_element_dist(l_dist_index).period55_amount;
4466 l_period_amount(56) := PSB_WS_POS1.g_element_dist(l_dist_index).period56_amount;
4467 l_period_amount(57) := PSB_WS_POS1.g_element_dist(l_dist_index).period57_amount;
4468 l_period_amount(58) := PSB_WS_POS1.g_element_dist(l_dist_index).period58_amount;
4469 l_period_amount(59) := PSB_WS_POS1.g_element_dist(l_dist_index).period59_amount;
4470 l_period_amount(60) := PSB_WS_POS1.g_element_dist(l_dist_index).period60_amount;
4471
4472 PSB_WS_ACCT1.Create_Account_Dist
4473 (p_api_version => 1.0,
4474 p_return_status => l_return_status,
4475 p_worksheet_id => p_worksheet_id,
4476 p_distribute_flag => FND_API.G_TRUE,
4477 p_account_line_id => PSB_WS_POS1.g_element_dist(l_dist_index).account_line_id,
4478 p_check_stages => FND_API.G_FALSE,
4479 p_ytd_amount => PSB_WS_POS1.g_pd_costs(l_pdist_index).ytd_amount,
4480 p_period_amount => l_period_amount,
4481 p_budget_group_id => PSB_WS_POS1.g_salary_budget_group_id);
4482
4483 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
4484 raise FND_API.G_EXC_ERROR;
4485 end if;
4486
4487 end;
4488 end if;
4489
4490 end loop;
4491
4492 for l_eldist_index in 1..PSB_WS_POS1.g_num_element_dist loop
4493
4494 if PSB_WS_POS1.g_element_dist(l_eldist_index).redist_flag is null then
4495 begin
4496
4497 PSB_WORKSHEET.Delete_WAL
4498 (p_api_version => 1.0,
4499 p_return_status => l_return_status,
4500 p_account_line_id => PSB_WS_POS1.g_element_dist(l_eldist_index).account_line_id);
4501
4502 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
4503 raise FND_API.G_EXC_ERROR;
4504 end if;
4505
4506 end;
4507 end if;
4508
4509 end loop;
4510
4511 PSB_WS_POS1.Update_Annual_FTE
4512 (p_api_version => 1.0,
4513 p_return_status => p_return_status,
4514 p_worksheet_id => p_worksheet_id,
4515 p_position_line_id => p_position_line_id,
4516 p_budget_year_id => p_budget_year_id,
4517 p_service_package_id => p_service_package_id,
4518 p_stage_set_id => p_stage_set_id,
4519 p_current_stage_seq => p_current_stage_seq,
4520 p_budget_group_id => PSB_WS_POS1.g_salary_budget_group_id);
4521
4522 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
4523 raise FND_API.G_EXC_ERROR;
4524 end if;
4525
4526 p_return_status := FND_API.G_RET_STS_SUCCESS;
4527
4528
4529 EXCEPTION
4530
4531 when FND_API.G_EXC_ERROR then
4532 p_return_status := FND_API.G_RET_STS_ERROR;
4533
4534 when FND_API.G_EXC_UNEXPECTED_ERROR then
4535 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4536
4537 when OTHERS then
4538 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4539 if FND_MSG_PUB.Check_Msg_Level
4540 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4541 then
4542 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
4543 'Redist_Follow_Salary_Year');
4544 end if;
4545
4546 END Redist_Follow_Salary_Year;
4547
4548 /* ----------------------------------------------------------------------- */
4549
4550 PROCEDURE Apply_Element_Constraints
4551 ( p_return_status OUT NOCOPY VARCHAR2,
4552 p_worksheet_id IN NUMBER,
4553 p_budget_calendar_id IN NUMBER,
4554 p_data_extract_id IN NUMBER,
4555 p_constraint_set_id IN NUMBER,
4556 p_constraint_set_name IN VARCHAR2,
4557 p_constraint_threshold IN NUMBER
4558 ) IS
4559
4560 l_return_status VARCHAR2(1);
4561
4562 cursor c_Constraint is
4563 select constraint_id,
4564 name,
4565 currency_code,
4566 severity_level,
4567 effective_start_date,
4568 effective_end_date
4569 from PSB_CONSTRAINT_ASSIGNMENTS_V
4570 where constraint_type = 'ELEMENT'
4571 and (((effective_start_date <= PSB_WS_ACCT1.g_end_est_date)
4572 and (effective_end_date is null))
4573 or ((effective_start_date between PSB_WS_ACCT1.g_startdate_cy and PSB_WS_ACCT1.g_end_est_date)
4574 or (effective_end_date between PSB_WS_ACCT1.g_startdate_cy and PSB_WS_ACCT1.g_end_est_date)
4575 or ((effective_start_date < PSB_WS_ACCT1.g_startdate_cy)
4576 and (effective_end_date > PSB_WS_ACCT1.g_end_est_date))))
4577 and constraint_set_id = p_constraint_set_id;
4578
4579 BEGIN
4580
4581 for c_Constraint_Rec in c_Constraint loop
4582
4583 Process_ElemCons_Detailed
4584 (p_return_status => l_return_status,
4585 p_worksheet_id => p_worksheet_id,
4586 p_data_extract_id => p_data_extract_id,
4587 p_constraint_id => c_Constraint_Rec.constraint_id,
4588 p_start_date => c_Constraint_Rec.effective_start_date,
4589 p_end_date => nvl(c_Constraint_Rec.effective_end_date, PSB_WS_ACCT1.g_end_est_date));
4590
4591 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
4592 raise FND_API.G_EXC_ERROR;
4593 end if;
4594
4595 end loop;
4596
4597
4598 -- Initialize API return status to success
4599
4600 p_return_status := FND_API.G_RET_STS_SUCCESS;
4601
4602
4603 EXCEPTION
4604
4605 when FND_API.G_EXC_ERROR then
4606 p_return_status := FND_API.G_RET_STS_ERROR;
4607
4608 when FND_API.G_EXC_UNEXPECTED_ERROR then
4609 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4610
4611 when OTHERS then
4612 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4613 if FND_MSG_PUB.Check_Msg_Level
4614 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4615 then
4616 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
4617 'Apply_Element_Constraints');
4618 end if;
4619
4620 END Apply_Element_Constraints;
4621
4622 /* ----------------------------------------------------------------------- */
4623
4624 PROCEDURE Process_ElemCons_Detailed
4625 ( p_return_status OUT NOCOPY VARCHAR2,
4626 p_worksheet_id IN NUMBER,
4627 p_data_extract_id IN NUMBER,
4628 p_constraint_id IN NUMBER,
4629 p_start_date IN DATE,
4630 p_end_date IN DATE
4631 ) IS
4632
4633 l_posasgn_id NUMBER;
4634 l_rowid VARCHAR2(100);
4635
4636 l_msg_count NUMBER;
4637 l_msg_data VARCHAR2(2000);
4638
4639 l_return_status VARCHAR2(1);
4640
4641 cursor c_Formula is
4642 select pay_element_id,
4643 pay_element_option_id,
4644 nvl(effective_start_date, p_start_date) effective_start_date,
4645 nvl(effective_end_date, p_end_date) effective_end_date,
4646 allow_modify
4647 from PSB_CONSTRAINT_FORMULAS
4648 where constraint_id = p_constraint_id
4649 order by step_number;
4650
4651 cursor c_Positions is
4652 select a.position_id
4653 from PSB_BUDGET_POSITIONS a,
4654 PSB_SET_RELATIONS b
4655 where a.data_extract_id = p_data_extract_id
4656 and a.account_position_set_id = b.account_position_set_id
4657 and b.constraint_id = p_constraint_id;
4658
4659 BEGIN
4660
4661 for c_Formula_Rec in c_Formula loop
4662
4663 if c_Formula_Rec.allow_modify = 'N' then
4664 begin
4665
4666 for c_Positions_Rec in c_Positions loop
4667
4668 PSB_POSITIONS_PVT.Modify_Assignment
4669 (p_api_version => 1.0,
4670 p_return_status => l_return_status,
4671 p_msg_count => l_msg_count,
4672 p_msg_data => l_msg_data,
4673 p_position_assignment_id => l_posasgn_id,
4674 p_data_extract_id => p_data_extract_id,
4675 p_worksheet_id => p_worksheet_id,
4676 p_position_id => c_Positions_Rec.position_id,
4677 p_assignment_type => 'ELEMENT',
4678 p_attribute_id => null,
4679 p_attribute_value_id => null,
4680 p_attribute_value => null,
4681 p_pay_element_id => c_Formula_Rec.pay_element_id,
4682 p_pay_element_option_id => c_Formula_Rec.pay_element_option_id,
4683 p_effective_start_date => c_Formula_Rec.effective_start_date,
4684 p_effective_end_date => c_Formula_Rec.effective_end_date,
4685 p_element_value_type => null,
4686 p_element_value => null,
4687 p_currency_code => null,
4688 p_pay_basis => null,
4689 p_global_default_flag => null,
4690 p_assignment_default_rule_id => null,
4691 p_modify_flag => c_Formula_Rec.allow_modify,
4692 p_rowid => l_rowid,
4693 p_employee_id => null,
4694 p_primary_employee_flag => null);
4695
4696 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
4697 raise FND_API.G_EXC_ERROR;
4698 end if;
4699
4700 end loop;
4701
4702 end;
4703 end if;
4704
4705 end loop;
4706
4707
4708 -- Initialize API return status to success
4709
4710 p_return_status := FND_API.G_RET_STS_SUCCESS;
4711
4712
4713 EXCEPTION
4714
4715 when FND_API.G_EXC_ERROR then
4716 p_return_status := FND_API.G_RET_STS_ERROR;
4717
4718 when FND_API.G_EXC_UNEXPECTED_ERROR then
4719 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4720
4721 when OTHERS then
4722 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4723 if FND_MSG_PUB.Check_Msg_Level
4724 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4725 then
4726 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
4727 'Process_ElemCons_Detailed');
4728 end if;
4729
4730 END Process_ElemCons_Detailed;
4731
4732 /* ----------------------------------------------------------------------- */
4733
4734 PROCEDURE Apply_Position_Constraints
4735 ( p_return_status OUT NOCOPY VARCHAR2,
4736 p_validation_status OUT NOCOPY VARCHAR2,
4737 p_worksheet_id IN NUMBER,
4738 p_budget_calendar_id IN NUMBER,
4739 p_data_extract_id IN NUMBER,
4740 p_business_group_id IN NUMBER,
4741 p_func_currency IN VARCHAR2,
4742 p_constraint_set_id IN NUMBER,
4743 p_constraint_set_name IN VARCHAR2,
4744 p_constraint_threshold IN NUMBER
4745 ) IS
4746
4747 l_year_index BINARY_INTEGER;
4748
4749 l_cons_validation_status VARCHAR2(1);
4750 l_consset_validation_status VARCHAR2(1) := 'S';
4751
4752 l_sp_exists VARCHAR2(1) := FND_API.G_FALSE;
4753
4754 l_return_status VARCHAR2(1);
4755
4756 cursor c_Constraint (Year_Start_Date DATE,
4757 Year_End_Date DATE) is
4758 select constraint_id,
4759 name,
4760 currency_code,
4761 severity_level,
4762 fte_constraint,
4763 effective_start_date,
4764 effective_end_date,
4765 constraint_detailed_flag
4766 from PSB_CONSTRAINT_ASSIGNMENTS_V
4767 where constraint_type = 'POSITION'
4768 and (((effective_start_date <= Year_End_Date)
4769 and (effective_end_date is null))
4770 or ((effective_start_date between Year_Start_Date and Year_End_Date)
4771 or (effective_end_date between Year_Start_Date and Year_End_Date)
4772 or ((effective_start_date < Year_Start_Date)
4773 and (effective_end_date > Year_End_Date))))
4774 and constraint_set_id = p_constraint_set_id
4775 order by severity_level desc;
4776
4777 -- Check whether Constraints should be applied for specific Service Packages
4778
4779 cursor c_SP is
4780 select 'x'
4781 from dual
4782 where exists
4783 (select 'Service Package Exists'
4784 from PSB_WS_SUBMIT_SERVICE_PACKAGES
4785 where worksheet_id = p_worksheet_id);
4786
4787 BEGIN
4788
4789 for c_SP_Rec in c_SP loop
4790 l_sp_exists := FND_API.G_TRUE;
4791 end loop;
4792
4793 for l_year_index in 1..PSB_WS_ACCT1.g_num_budget_years loop
4794
4795 if PSB_WS_ACCT1.g_budget_years(l_year_index).year_type in ('CY', 'PP') then
4796 begin
4797
4798 for c_Constraint_Rec in c_Constraint (PSB_WS_ACCT1.g_budget_years(l_year_index).start_date,
4799 PSB_WS_ACCT1.g_budget_years(l_year_index).end_date) loop
4800
4801 if ((c_Constraint_Rec.constraint_detailed_flag is null) or
4802 (c_Constraint_Rec.constraint_detailed_flag = 'N')) then
4803 begin
4804
4805 if ((c_Constraint_Rec.fte_constraint is null) or (c_Constraint_Rec.fte_constraint = 'N')) then
4806 begin
4807
4808 Process_PosCons
4809 (p_worksheet_id => p_worksheet_id,
4810 p_data_extract_id => p_data_extract_id,
4811 p_business_group_id => p_business_group_id,
4812 p_sp_exists => l_sp_exists,
4813 p_constraint_set_name => p_constraint_set_name,
4814 p_constraint_threshold => p_constraint_threshold,
4815 p_constraint_id => c_Constraint_Rec.constraint_id,
4816 p_constraint_name => c_Constraint_Rec.name,
4817 p_budget_year_id => PSB_WS_ACCT1.g_budget_years(l_year_index).budget_year_id,
4818 p_budget_year_name => PSB_WS_ACCT1.g_budget_years(l_year_index).year_name,
4819 p_year_start_date => PSB_WS_ACCT1.g_budget_years(l_year_index).start_date,
4820 p_year_end_date => PSB_WS_ACCT1.g_budget_years(l_year_index).end_date,
4821 p_currency_code => nvl(c_Constraint_Rec.currency_code, p_func_currency),
4822 p_severity_level => c_Constraint_Rec.severity_level,
4823 p_summ_flag => FND_API.G_TRUE,
4824 p_constraint_validation_status => l_cons_validation_status,
4825 p_return_status => l_return_status);
4826
4827 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
4828 raise FND_API.G_EXC_ERROR;
4829 end if;
4830
4831 end;
4832 else
4833 begin
4834
4835 Process_FTECons
4836 (p_worksheet_id => p_worksheet_id,
4837 p_data_extract_id => p_data_extract_id,
4838 p_sp_exists => l_sp_exists,
4839 p_constraint_set_name => p_constraint_set_name,
4840 p_constraint_threshold => p_constraint_threshold,
4841 p_constraint_id => c_Constraint_Rec.constraint_id,
4842 p_constraint_name => c_Constraint_Rec.name,
4843 p_budget_year_id => PSB_WS_ACCT1.g_budget_years(l_year_index).budget_year_id,
4844 p_budget_year_name => PSB_WS_ACCT1.g_budget_years(l_year_index).year_name,
4845 p_currency_code => nvl(c_Constraint_Rec.currency_code, p_func_currency),
4846 p_severity_level => c_Constraint_Rec.severity_level,
4847 p_summ_flag => FND_API.G_TRUE,
4848 p_constraint_validation_status => l_cons_validation_status,
4849 p_return_status => l_return_status);
4850
4851 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
4852 raise FND_API.G_EXC_ERROR;
4853 end if;
4854
4855 end;
4856 end if;
4857
4858 if ((l_consset_validation_status = 'S') and
4859 (l_cons_validation_status <> 'S')) then
4860 l_consset_validation_status := l_cons_validation_status;
4861 elsif ((l_consset_validation_status = 'E') and
4862 (l_cons_validation_status = 'F')) then
4863 l_consset_validation_status := l_cons_validation_status;
4864 elsif ((l_consset_validation_status = 'W') and
4865 (l_cons_validation_status in ('F', 'E'))) then
4866 l_consset_validation_status := l_cons_validation_status;
4867 end if;
4868
4869 end;
4870 else
4871 begin
4872
4873 Process_PosCons_Detailed
4874 (p_return_status => l_return_status,
4875 p_constraint_validation_status => l_cons_validation_status,
4876 p_worksheet_id => p_worksheet_id,
4877 p_data_extract_id => p_data_extract_id,
4878 p_business_group_id => p_business_group_id,
4879 p_sp_exists => l_sp_exists,
4880 p_constraint_set_name => p_constraint_set_name,
4881 p_constraint_threshold => p_constraint_threshold,
4882 p_constraint_id => c_Constraint_Rec.constraint_id,
4883 p_constraint_name => c_Constraint_Rec.name,
4884 p_fte_constraint => c_Constraint_Rec.fte_constraint,
4885 p_budget_year_id => PSB_WS_ACCT1.g_budget_years(l_year_index).budget_year_id,
4886 p_budget_year_name => PSB_WS_ACCT1.g_budget_years(l_year_index).year_name,
4887 p_year_start_date => PSB_WS_ACCT1.g_budget_years(l_year_index).start_date,
4888 p_year_end_date => PSB_WS_ACCT1.g_budget_years(l_year_index).end_date,
4889 p_currency_code => nvl(c_Constraint_Rec.currency_code, p_func_currency),
4890 p_severity_level => c_Constraint_Rec.severity_level);
4891
4892 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
4893 raise FND_API.G_EXC_ERROR;
4894 end if;
4895
4896 if ((l_consset_validation_status = 'S') and
4897 (l_cons_validation_status <> 'S')) then
4898 l_consset_validation_status := l_cons_validation_status;
4899 elsif ((l_consset_validation_status = 'E') and
4900 (l_cons_validation_status = 'F')) then
4901 l_consset_validation_status := l_cons_validation_status;
4902 elsif ((l_consset_validation_status = 'W') and
4903 (l_cons_validation_status in ('F', 'E'))) then
4904 l_consset_validation_status := l_cons_validation_status;
4905 end if;
4906
4907 end;
4908 end if;
4909
4910 end loop;
4911
4912 end;
4913 end if;
4914
4915 end loop;
4916
4917
4918 -- Initialize API return status to success
4919
4920 p_validation_status := l_consset_validation_status;
4921 p_return_status := FND_API.G_RET_STS_SUCCESS;
4922
4923
4924 EXCEPTION
4925
4926 when FND_API.G_EXC_ERROR then
4927 p_return_status := FND_API.G_RET_STS_ERROR;
4928
4929 when FND_API.G_EXC_UNEXPECTED_ERROR then
4930 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4931
4932 when OTHERS then
4933 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4934 if FND_MSG_PUB.Check_Msg_Level
4935 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4936 then
4937 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
4938 'Apply_Position_Constraints');
4939 end if;
4940
4941 END Apply_Position_Constraints;
4942
4943 /* ----------------------------------------------------------------------- */
4944
4945 PROCEDURE Process_PosCons_Detailed
4946 ( p_return_status OUT NOCOPY VARCHAR2,
4947 p_constraint_validation_status OUT NOCOPY VARCHAR2,
4948 p_worksheet_id IN NUMBER,
4949 p_data_extract_id IN NUMBER,
4950 p_business_group_id IN NUMBER,
4951 p_sp_exists IN VARCHAR2,
4952 p_constraint_set_name IN VARCHAR2,
4953 p_constraint_threshold IN NUMBER,
4954 p_constraint_id IN NUMBER,
4955 p_constraint_name IN VARCHAR2,
4956 p_fte_constraint IN VARCHAR2,
4957 p_budget_year_id IN NUMBER,
4958 p_budget_year_name IN VARCHAR2,
4959 p_year_start_date IN DATE,
4960 p_year_end_date IN DATE,
4961 p_currency_code IN VARCHAR2,
4962 p_severity_level IN NUMBER
4963 ) IS
4964
4965 l_cons_validation_status VARCHAR2(1) := 'S';
4966 l_detailed_status VARCHAR2(1);
4967
4968 l_return_status VARCHAR2(1);
4969
4970 cursor c_Positions is
4971 select d.position_id,
4972 c.name,
4973 a.position_line_id
4974 from PSB_WS_LINES_POSITIONS a,
4975 PSB_WS_POSITION_LINES b,
4976 PSB_POSITIONS c,
4977 PSB_BUDGET_POSITIONS d,
4978 PSB_SET_RELATIONS e
4979 where a.position_line_id = b.position_line_id
4980 and a.worksheet_id = p_worksheet_id
4981 and b.position_id = c.position_id
4982 and c.position_id = d.position_id
4983 and d.data_extract_id = p_data_extract_id
4984 and d.account_position_set_id = e.account_position_set_id
4985 and e.constraint_id = p_constraint_id;
4986
4987 BEGIN
4988
4989 for c_Positions_Rec in c_Positions loop
4990
4991 if ((p_fte_constraint is null) or (p_fte_constraint = 'N')) then
4992 begin
4993
4994 Process_PosCons
4995 (p_worksheet_id => p_worksheet_id,
4996 p_data_extract_id => p_data_extract_id,
4997 p_business_group_id => p_business_group_id,
4998 p_sp_exists => p_sp_exists,
4999 p_constraint_set_name => p_constraint_set_name,
5000 p_constraint_threshold => p_constraint_threshold,
5001 p_constraint_id => p_constraint_id,
5002 p_constraint_name => p_constraint_name,
5003 p_position_line_id => c_Positions_Rec.position_line_id,
5004 p_position_id => c_Positions_Rec.position_id,
5005 p_position_name => c_Positions_Rec.name,
5006 p_budget_year_id => p_budget_year_id,
5007 p_budget_year_name => p_budget_year_name,
5008 p_year_start_date => p_year_start_date,
5009 p_year_end_date => p_year_end_date,
5010 p_currency_code => p_currency_code,
5011 p_severity_level => p_severity_level,
5012 p_summ_flag => FND_API.G_FALSE,
5013 p_constraint_validation_status => l_detailed_status,
5014 p_return_status => l_return_status);
5015
5016 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
5017 raise FND_API.G_EXC_ERROR;
5018 end if;
5019
5020 end;
5021 else
5022 begin
5023
5024 Process_FTECons
5025 (p_worksheet_id => p_worksheet_id,
5026 p_data_extract_id => p_data_extract_id,
5027 p_sp_exists => p_sp_exists,
5028 p_constraint_set_name => p_constraint_set_name,
5029 p_constraint_threshold => p_constraint_threshold,
5030 p_constraint_id => p_constraint_id,
5031 p_constraint_name => p_constraint_name,
5032 p_position_line_id => c_Positions_Rec.position_line_id,
5033 p_position_id => c_Positions_Rec.position_id,
5034 p_position_name => c_Positions_Rec.name,
5035 p_budget_year_id => p_budget_year_id,
5036 p_budget_year_name => p_budget_year_name,
5037 p_currency_code => p_currency_code,
5038 p_severity_level => p_severity_level,
5039 p_summ_flag => FND_API.G_FALSE,
5040 p_constraint_validation_status => l_detailed_status,
5041 p_return_status => l_return_status);
5042
5043 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
5044 raise FND_API.G_EXC_ERROR;
5045 end if;
5046
5047 end;
5048 end if;
5049
5050 if ((l_cons_validation_status = 'S') and
5051 (l_detailed_status <> 'S')) then
5052 l_cons_validation_status := l_detailed_status;
5053 elsif ((l_cons_validation_status = 'E') and
5054 (l_detailed_status = 'F')) then
5055 l_cons_validation_status := l_detailed_status;
5056 end if;
5057
5058 end loop;
5059
5060
5061 -- Initialize API return status to success
5062
5063 p_constraint_validation_status := l_cons_validation_status;
5064 p_return_status := FND_API.G_RET_STS_SUCCESS;
5065
5066
5067 EXCEPTION
5068
5069 when FND_API.G_EXC_ERROR then
5070 p_return_status := FND_API.G_RET_STS_ERROR;
5071
5072 when FND_API.G_EXC_UNEXPECTED_ERROR then
5073 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5074
5075 when OTHERS then
5076 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5077 if FND_MSG_PUB.Check_Msg_Level
5078 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5079 then
5080 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
5081 'Process_PosCons_Detailed');
5082 end if;
5083
5084 END Process_PosCons_Detailed;
5085
5086 /* ----------------------------------------------------------------------- */
5087
5088 PROCEDURE Process_PosCons
5089 ( p_return_status OUT NOCOPY VARCHAR2,
5090 p_constraint_validation_status OUT NOCOPY VARCHAR2,
5091 p_worksheet_id IN NUMBER,
5092 p_data_extract_id IN NUMBER,
5093 p_business_group_id IN NUMBER,
5094 p_sp_exists IN VARCHAR2,
5095 p_constraint_set_name IN VARCHAR2,
5096 p_constraint_threshold IN NUMBER,
5097 p_constraint_id IN NUMBER,
5098 p_constraint_name IN VARCHAR2,
5099 p_position_line_id IN NUMBER := FND_API.G_MISS_NUM,
5100 p_position_id IN NUMBER := FND_API.G_MISS_NUM,
5101 p_position_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
5102 p_budget_year_id IN NUMBER,
5103 p_budget_year_name IN VARCHAR2,
5104 p_year_start_date IN DATE,
5105 p_year_end_date IN DATE,
5106 p_currency_code IN VARCHAR2,
5107 p_severity_level IN NUMBER,
5108 p_summ_flag IN VARCHAR2
5109 ) IS
5110
5111 l_cons_validation_status VARCHAR2(1) := 'S';
5112 l_detailed_status VARCHAR2(1);
5113
5114 l_return_status VARCHAR2(1);
5115
5116 cursor c_Formula is
5117 select pay_element_id,
5118 pay_element_option_id,
5119 prefix_operator,
5120 nvl(currency_code, p_currency_code) currency_code,
5121 element_value_type,
5122 element_value
5123 from PSB_CONSTRAINT_FORMULAS
5124 where constraint_id = p_constraint_id
5125 order by step_number;
5126
5127 BEGIN
5128
5129 for c_Formula_Rec in c_Formula loop
5130
5131 Process_PosCons_Step
5132 (p_return_status => l_return_status,
5133 p_constraint_validation_status => l_detailed_status,
5134 p_worksheet_id => p_worksheet_id,
5135 p_data_extract_id => p_data_extract_id,
5136 p_business_group_id => p_business_group_id,
5137 p_sp_exists => p_sp_exists,
5138 p_constraint_set_name => p_constraint_set_name,
5139 p_constraint_threshold => p_constraint_threshold,
5140 p_constraint_id => p_constraint_id,
5141 p_constraint_name => p_constraint_name,
5142 p_position_line_id => p_position_line_id,
5143 p_position_id => p_position_id,
5144 p_position_name => p_position_name,
5145 p_budget_year_id => p_budget_year_id,
5146 p_budget_year_name => p_budget_year_name,
5147 p_year_start_date => p_year_start_date,
5148 p_year_end_date => p_year_end_date,
5149 p_currency_code => c_Formula_Rec.currency_code,
5150 p_severity_level => p_severity_level,
5151 p_summ_flag => p_summ_flag,
5152 p_pay_element_id => c_Formula_Rec.pay_element_id,
5153 p_pay_element_option_id => c_Formula_Rec.pay_element_option_id,
5154 p_prefix_operator => c_Formula_Rec.prefix_operator,
5155 p_element_value_type => c_Formula_Rec.element_value_type,
5156 p_element_value => c_Formula_Rec.element_value);
5157
5158 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
5159 raise FND_API.G_EXC_ERROR;
5160 end if;
5161
5162 if ((l_cons_validation_status = 'S') and
5163 (l_detailed_status <> 'S')) then
5164 l_cons_validation_status := l_detailed_status;
5165 elsif ((l_cons_validation_status = 'E') and
5166 (l_detailed_status = 'F')) then
5167 l_cons_validation_status := l_detailed_status;
5168 end if;
5169
5170 end loop;
5171
5172
5173 -- Initialize API return status to success
5174
5175 p_constraint_validation_status := l_cons_validation_status;
5176 p_return_status := FND_API.G_RET_STS_SUCCESS;
5177
5178
5179 EXCEPTION
5180
5181 when FND_API.G_EXC_ERROR then
5182 p_return_status := FND_API.G_RET_STS_ERROR;
5183
5184 when FND_API.G_EXC_UNEXPECTED_ERROR then
5185 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5186
5187 when OTHERS then
5188 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5189 if FND_MSG_PUB.Check_Msg_Level
5190 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5191 then
5192 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
5193 'Process_PosCons');
5194 end if;
5195
5196 END Process_PosCons;
5197
5198 /* ----------------------------------------------------------------------- */
5199
5200 PROCEDURE Process_PosCons_Step
5201 ( p_return_status OUT NOCOPY VARCHAR2,
5202 p_constraint_validation_status OUT NOCOPY VARCHAR2,
5203 p_worksheet_id IN NUMBER,
5204 p_data_extract_id IN NUMBER,
5205 p_business_group_id IN NUMBER,
5206 p_sp_exists IN VARCHAR2,
5207 p_constraint_set_name IN VARCHAR2,
5208 p_constraint_threshold IN NUMBER,
5209 p_constraint_id IN NUMBER,
5210 p_constraint_name IN VARCHAR2,
5211 p_position_line_id IN NUMBER := FND_API.G_MISS_NUM,
5212 p_position_id IN NUMBER := FND_API.G_MISS_NUM,
5213 p_position_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
5214 p_budget_year_id IN NUMBER,
5215 p_budget_year_name IN VARCHAR2,
5216 p_year_start_date IN DATE,
5217 p_year_end_date IN DATE,
5218 p_currency_code IN VARCHAR2,
5219 p_severity_level IN NUMBER,
5220 p_summ_flag IN VARCHAR2,
5221 p_pay_element_id IN NUMBER,
5222 p_pay_element_option_id IN NUMBER,
5223 p_prefix_operator IN VARCHAR2,
5224 p_element_value_type IN VARCHAR2,
5225 p_element_value IN NUMBER
5226 ) IS
5227
5228 l_cons_failed VARCHAR2(1) := FND_API.G_FALSE;
5229
5230 l_salary_total NUMBER := 0;
5231 l_posset_total NUMBER := 0;
5232 l_cons_total NUMBER := 0;
5233
5234 l_reqid NUMBER;
5235 l_userid NUMBER;
5236 l_description VARCHAR2(2000);
5237
5238 l_grade_name VARCHAR2(80);
5239 l_grade_step NUMBER;
5240
5241 cursor c_Grade is
5242 select name grade_name,
5243 grade_step
5244 from PSB_PAY_ELEMENT_OPTIONS
5245 where pay_element_option_id = p_pay_element_option_id;
5246
5247 cursor c_SalaryNeqAll is
5248 select a.name position_name,
5249 b.name,
5250 b.grade_step
5251 from PSB_POSITIONS a,
5252 PSB_PAY_ELEMENT_OPTIONS b,
5253 PSB_POSITION_ASSIGNMENTS c
5254 where exists
5255 (select 1
5256 from PSB_BUDGET_POSITIONS d,
5257 PSB_SET_RELATIONS e
5258 where d.data_extract_id = p_data_extract_id
5259 and d.position_id = c.position_id
5260 and d.account_position_set_id = e.account_position_set_id
5261 and e.constraint_id = p_constraint_id)
5262 and a.position_id = c.position_id
5263 and b.pay_element_option_id = c.pay_element_option_id
5264 and c.pay_element_option_id <> p_pay_element_option_id
5265 and ((c.worksheet_id is null) or (c.worksheet_id = p_worksheet_id))
5266 and (((c.effective_start_date <= p_year_start_date)
5267 and (c.effective_end_date is null))
5268 or ((c.effective_start_date between p_year_start_date and p_year_end_date)
5269 or (c.effective_end_date between p_year_start_date and p_year_end_date)
5270 or ((c.effective_start_date < p_year_start_date)
5271 and (c.effective_end_date > p_year_end_date))))
5272 and c.pay_element_id = p_pay_element_id;
5273
5274 cursor c_SalaryNeq is
5275 select a.name,
5276 a.grade_step
5277 from PSB_PAY_ELEMENT_OPTIONS a,
5278 PSB_POSITION_ASSIGNMENTS b
5279 where a.pay_element_option_id = b.pay_element_option_id
5280 and b.pay_element_option_id <> p_pay_element_option_id
5281 and (((b.effective_start_date <= p_year_start_date)
5282 and (b.effective_end_date is null))
5283 or ((b.effective_start_date between p_year_start_date and p_year_end_date)
5284 or (b.effective_end_date between p_year_start_date and p_year_end_date)
5285 or ((b.effective_start_date < p_year_start_date)
5286 and (b.effective_end_date > p_year_end_date))))
5287 and b.pay_element_id = p_pay_element_id
5288 and b.position_id = p_position_id;
5289
5290 cursor c_SumAll is
5291 select sum(nvl(a.element_cost, 0)) Sum_Elem
5292 from PSB_WS_ELEMENT_LINES a,
5293 PSB_WORKSHEETS b
5294 where exists
5295 (select 1
5296 from PSB_WS_LINES_POSITIONS c,
5297 PSB_WS_POSITION_LINES d,
5298 PSB_BUDGET_POSITIONS e,
5299 PSB_SET_RELATIONS f
5300 where c.position_line_id = a.position_line_id
5301 and c.position_line_id = d.position_line_id
5302 and c.worksheet_id = p_worksheet_id
5303 and d.position_id = e.position_id
5304 and e.data_extract_id = p_data_extract_id
5305 and e.account_position_set_id = f.account_position_set_id
5306 and f.constraint_id = p_constraint_id)
5307 and b.current_stage_seq between a.start_stage_seq and a.current_stage_seq
5308 and a.currency_code = p_currency_code
5309 and a.stage_set_id = b.stage_set_id
5310 and a.budget_year_id = p_budget_year_id
5311 and a.pay_element_id = p_pay_element_id
5312 and b.worksheet_id = p_worksheet_id;
5313
5314 cursor c_SumAll_Salary is
5315 select sum(nvl(a.element_cost, 0)) Sum_Elem
5316 from PSB_WS_ELEMENT_LINES a,
5317 PSB_WORKSHEETS b,
5318 PSB_PAY_ELEMENTS c
5319 where exists
5320 (select 1
5321 from PSB_WS_LINES_POSITIONS d,
5322 PSB_WS_POSITION_LINES e,
5323 PSB_BUDGET_POSITIONS f,
5324 PSB_SET_RELATIONS g
5325 where d.position_line_id = a.position_line_id
5326 and d.position_line_id = e.position_line_id
5327 and d.worksheet_id = p_worksheet_id
5328 and e.position_id = f.position_id
5329 and f.data_extract_id = p_data_extract_id
5330 and f.account_position_set_id = g.account_position_set_id
5331 and g.constraint_id = p_constraint_id)
5332 and b.current_stage_seq between a.start_stage_seq and a.current_stage_seq
5333 and a.currency_code = p_currency_code
5334 and a.stage_set_id = b.stage_set_id
5335 and a.budget_year_id = p_budget_year_id
5336 and a.pay_element_id = c.pay_element_id
5337 and b.worksheet_id = p_worksheet_id
5338 and c.processing_type = 'R'
5339 and c.salary_flag = 'Y'
5340 and c.business_group_id = p_business_group_id
5341 and c.data_extract_id = p_data_extract_id;
5342
5343 cursor c_SumAllSP is
5344 select sum(nvl(a.element_cost, 0)) Sum_Elem
5345 from PSB_WS_ELEMENT_LINES a,
5346 PSB_WORKSHEETS b
5347 where exists
5348 (select 1
5349 from PSB_WS_LINES_POSITIONS c,
5350 PSB_WS_POSITION_LINES d,
5351 PSB_BUDGET_POSITIONS e,
5352 PSB_SET_RELATIONS f
5353 where c.position_line_id = a.position_line_id
5354 and c.position_line_id = d.position_line_id
5355 and c.worksheet_id = p_worksheet_id
5356 and d.position_id = e.position_id
5357 and e.data_extract_id = p_data_extract_id
5358 and e.account_position_set_id = f.account_position_set_id
5359 and f.constraint_id = p_constraint_id)
5360 and exists
5361 (select 1
5362 from PSB_WS_SUBMIT_SERVICE_PACKAGES g
5363 where g.service_package_id = a.service_package_id
5364 and g.worksheet_id = p_worksheet_id)
5365 and b.current_stage_seq between a.start_stage_seq and a.current_stage_seq
5366 and a.currency_code = p_currency_code
5367 and a.stage_set_id = b.stage_set_id
5368 and a.budget_year_id = p_budget_year_id
5369 and a.pay_element_id = p_pay_element_id
5370 and b.worksheet_id = p_worksheet_id;
5371
5372 cursor c_SumAllSP_Salary is
5373 select sum(nvl(a.element_cost, 0)) Sum_Elem
5374 from PSB_WS_ELEMENT_LINES a,
5375 PSB_WORKSHEETS b,
5376 PSB_PAY_ELEMENTS c
5377 where exists
5378 (select 1
5379 from PSB_WS_LINES_POSITIONS d,
5380 PSB_WS_POSITION_LINES e,
5381 PSB_BUDGET_POSITIONS f,
5382 PSB_SET_RELATIONS g
5383 where d.position_line_id = a.position_line_id
5384 and d.position_line_id = e.position_line_id
5385 and d.worksheet_id = p_worksheet_id
5386 and e.position_id = f.position_id
5387 and f.data_extract_id = p_data_extract_id
5388 and f.account_position_set_id = g.account_position_set_id
5389 and g.constraint_id = p_constraint_id)
5390 and exists
5391 (select 1
5392 from PSB_WS_SUBMIT_SERVICE_PACKAGES h
5393 where h.service_package_id = a.service_package_id
5394 and h.worksheet_id = p_worksheet_id)
5395 and b.current_stage_seq between a.start_stage_seq and a.current_stage_seq
5396 and a.currency_code = p_currency_code
5397 and a.stage_set_id = b.stage_set_id
5398 and a.budget_year_id = p_budget_year_id
5399 and a.pay_element_id = c.pay_element_id
5400 and b.worksheet_id = p_worksheet_id
5401 and c.processing_type = 'R'
5402 and c.salary_flag = 'Y'
5403 and c.business_group_id = p_business_group_id
5404 and c.data_extract_id = p_data_extract_id;
5405
5406 cursor c_Sum is
5407 select /*+ ORDERED USE_NL(b a) INDEX(b PSB_WORKSHEETS_U1) INDEX(a PSB_WS_ELEMENT_LINES_N1) */
5408 sum(nvl(a.element_cost, 0)) Sum_Elem
5409 from PSB_WS_ELEMENT_LINES a,
5410 PSB_WORKSHEETS b
5411 where a.currency_code = p_currency_code
5412 and a.stage_set_id = b.stage_set_id
5413 and a.pay_element_id = p_pay_element_id
5414 and b.current_stage_seq between a.start_stage_seq and a.current_stage_seq
5415 and a.budget_year_id = p_budget_year_id
5416 and a.position_line_id = p_position_line_id
5417 and b.worksheet_id = p_worksheet_id;
5418
5419 cursor c_Sum_Salary is
5420 select /*+ ORDERED INDEX(a PSB_WS_ELEMENT_LINES_N1) */
5421 sum(nvl(a.element_cost, 0)) Sum_Elem
5422 from PSB_WS_ELEMENT_LINES a,
5423 PSB_WORKSHEETS b,
5424 PSB_PAY_ELEMENTS c
5425 where a.currency_code = p_currency_code
5426 and a.stage_set_id = b.stage_set_id
5427 and a.pay_element_id = c.pay_element_id
5428 and b.current_stage_seq between a.start_stage_seq and a.current_stage_seq
5429 and a.budget_year_id = p_budget_year_id
5430 and a.position_line_id = p_position_line_id
5431 and b.worksheet_id = p_worksheet_id
5432 and c.processing_type = 'R'
5433 and c.salary_flag = 'Y'
5434 and c.business_group_id = p_business_group_id
5435 and c.data_extract_id = p_data_extract_id;
5436
5437 cursor c_SumSP is
5438 select /*+ ORDERED USE_NL(b a) INDEX(b PSB_WORKSHEETS_U1) INDEX(a PSB_WS_ELEMENT_LINES_N1) */
5439 sum(nvl(a.element_cost, 0)) Sum_Elem
5440 from PSB_WS_ELEMENT_LINES a,
5441 PSB_WORKSHEETS b
5442 where exists
5443 (select 1
5444 from PSB_WS_SUBMIT_SERVICE_PACKAGES d
5445 where d.service_package_id = a.service_package_id
5446 and d.worksheet_id = p_worksheet_id)
5447 and a.currency_code = p_currency_code
5448 and a.stage_set_id = b.stage_set_id
5449 and a.pay_element_id = p_pay_element_id
5450 and b.current_stage_seq between a.start_stage_seq and a.current_stage_seq
5451 and a.budget_year_id = p_budget_year_id
5452 and a.position_line_id = p_position_line_id
5453 and b.worksheet_id = p_worksheet_id;
5454
5455 cursor c_SumSP_Salary is
5456 select /*+ ORDERED INDEX(a PSB_WS_ELEMENT_LINES_N1) */
5457 sum(nvl(a.element_cost, 0)) Sum_Elem
5458 from PSB_WS_ELEMENT_LINES a,
5459 PSB_WORKSHEETS b,
5460 PSB_PAY_ELEMENTS c
5461 where exists
5462 (select 1
5463 from PSB_WS_SUBMIT_SERVICE_PACKAGES d
5464 where d.service_package_id = a.service_package_id
5465 and d.worksheet_id = p_worksheet_id)
5466 and a.currency_code = p_currency_code
5467 and a.stage_set_id = b.stage_set_id
5468 and a.pay_element_id = c.pay_element_id
5469 and b.current_stage_seq between a.start_stage_seq and a.current_stage_seq
5470 and a.budget_year_id = p_budget_year_id
5471 and a.position_line_id = p_position_line_id
5472 and b.worksheet_id = p_worksheet_id
5473 and c.processing_type = 'R'
5474 and c.salary_flag = 'Y'
5475 and c.business_group_id = p_business_group_id
5476 and c.data_extract_id = p_data_extract_id;
5477
5478 BEGIN
5479
5480 if not FND_API.to_Boolean(p_summ_flag) then
5481 begin
5482
5483 if FND_API.to_Boolean(p_sp_exists) then
5484 begin
5485
5486 if p_pay_element_option_id is null then
5487 begin
5488
5489 for c_Sum_Rec in c_SumSP loop
5490 l_posset_total := c_Sum_Rec.Sum_Elem;
5491 end loop;
5492
5493 if p_element_value_type = 'PS' then
5494 begin
5495
5496 for c_Sum_Salary_Rec in c_SumSP_Salary loop
5497 l_salary_total := c_Sum_Salary_Rec.Sum_Elem;
5498 end loop;
5499
5500 end;
5501 end if;
5502
5503 end;
5504 end if;
5505
5506 end;
5507 else
5508 begin
5509
5510 if p_pay_element_option_id is null then
5511 begin
5512
5513 for c_Sum_Rec in c_Sum loop
5514 l_posset_total := c_Sum_Rec.Sum_Elem;
5515 end loop;
5516
5517 if p_element_value_type = 'PS' then
5518 begin
5519
5520 for c_Sum_Salary_Rec in c_Sum_Salary loop
5521 l_salary_total := c_Sum_Salary_Rec.Sum_Elem;
5522 end loop;
5523
5524 end;
5525 end if;
5526
5527 end;
5528 end if;
5529
5530 end;
5531 end if;
5532
5533 if p_pay_element_option_id is not null then
5534 begin
5535
5536 for c_Grade_Rec in c_Grade loop
5537 l_grade_name := c_Grade_Rec.grade_name;
5538 l_grade_step := c_Grade_Rec.grade_step;
5539 end loop;
5540
5541 if p_prefix_operator = '<>' then
5542 begin
5543
5544 l_userid := FND_GLOBAL.USER_ID;
5545 l_reqid := FND_GLOBAL.CONC_REQUEST_ID;
5546
5547 for c_SalaryNeq_Rec in c_SalaryNeq loop
5548
5549 message_token('CONSTRAINT_SET', p_constraint_set_name);
5550 message_token('THRESHOLD', p_constraint_threshold);
5551 message_token('CONSTRAINT', p_constraint_name);
5552 message_token('SEVERITY_LEVEL', p_severity_level);
5553 message_token('ASSIGNMENT_VALUE', c_SalaryNeq_Rec.name || ' ' || c_SalaryNeq_Rec.grade_step);
5554 message_token('OPERATOR', p_prefix_operator);
5555 message_token('FORMULA_VALUE', l_grade_name || ' ' || l_grade_step);
5556 message_token('NAME', p_position_name);
5557 message_token('YEAR', p_budget_year_name);
5558 add_message('PSB', 'PSB_CONSTRAINT_FAILURE');
5559
5560 l_description := FND_MSG_PUB.Get
5561 (p_encoded => FND_API.G_FALSE);
5562 FND_MSG_PUB.Delete_Msg;
5563
5564 insert into PSB_ERROR_MESSAGES
5565 (Concurrent_Request_ID,
5566 Process_ID,
5567 Source_Process,
5568 Description,
5569 Creation_Date,
5570 Created_By)
5571 values (l_reqid,
5572 p_worksheet_id,
5573 'WORKSHEET_CREATION',
5574 l_description,
5575 sysdate,
5576 l_userid);
5577
5578 if nvl(p_severity_level, -1) >= p_constraint_threshold then
5579 p_constraint_validation_status := 'F';
5580 else
5581 p_constraint_validation_status := 'E';
5582 end if;
5583
5584 end loop;
5585
5586 end;
5587 end if;
5588
5589 end;
5590 end if;
5591
5592 end;
5593 else
5594 begin
5595
5596 if FND_API.to_Boolean(p_sp_exists) then
5597 begin
5598
5599 if p_pay_element_option_id is null then
5600 begin
5601
5602 for c_SumAll_Rec in c_SumAllSP loop
5603 l_posset_total := c_SumAll_Rec.Sum_Elem;
5604 end loop;
5605
5606 if p_element_value_type = 'PS' then
5607 begin
5608
5609 for c_SumAll_Salary_Rec in c_SumAllSP_Salary loop
5610 l_salary_total := c_SumAll_Salary_Rec.Sum_Elem;
5611 end loop;
5612
5613 end;
5614 end if;
5615
5616 end;
5617 end if;
5618
5619 end;
5620 else
5621 begin
5622
5623 if p_pay_element_option_id is null then
5624 begin
5625
5626 for c_SumAll_Rec in c_SumAll loop
5627 l_posset_total := c_SumAll_Rec.Sum_Elem;
5628 end loop;
5629
5630 if p_element_value_type = 'PS' then
5631 begin
5632
5633 for c_SumAll_Salary_Rec in c_SumAll_Salary loop
5634 l_salary_total := c_SumAll_Salary_Rec.Sum_Elem;
5635 end loop;
5636
5637 end;
5638 end if;
5639
5640 end;
5641 end if;
5642
5643 end;
5644 end if;
5645
5646 if p_pay_element_option_id is not null then
5647 begin
5648
5649 for c_Grade_Rec in c_Grade loop
5650 l_grade_name := c_Grade_Rec.grade_name;
5651 l_grade_step := c_Grade_Rec.grade_step;
5652 end loop;
5653
5654 if p_prefix_operator = '<>' then
5655 begin
5656
5657 l_userid := FND_GLOBAL.USER_ID;
5658 l_reqid := FND_GLOBAL.CONC_REQUEST_ID;
5659
5660 for c_SalaryNeqAll_Rec in c_SalaryNeqAll loop
5661
5662 message_token('CONSTRAINT_SET', p_constraint_set_name);
5663 message_token('THRESHOLD', p_constraint_threshold);
5664 message_token('CONSTRAINT', p_constraint_name);
5665 message_token('SEVERITY_LEVEL', p_severity_level);
5666 message_token('ASSIGNMENT_VALUE', c_SalaryNeqAll_Rec.name || ' ' || c_SalaryNeqAll_Rec.grade_step);
5667 message_token('OPERATOR', p_prefix_operator);
5668 message_token('FORMULA_VALUE', l_grade_name || ' ' || l_grade_step);
5669 message_token('NAME', c_SalaryNeqAll_Rec.position_name);
5670 message_token('YEAR', p_budget_year_name);
5671 add_message('PSB', 'PSB_CONSTRAINT_FAILURE');
5672
5673 l_description := FND_MSG_PUB.Get
5674 (p_encoded => FND_API.G_FALSE);
5675 FND_MSG_PUB.Delete_Msg;
5676
5677 insert into PSB_ERROR_MESSAGES
5678 (Concurrent_Request_ID,
5679 Process_ID,
5680 Source_Process,
5681 Description,
5682 Creation_Date,
5683 Created_By)
5684 values (l_reqid,
5685 p_worksheet_id,
5686 'WORKSHEET_CREATION',
5687 l_description,
5688 sysdate,
5689 l_userid);
5690
5691 if nvl(p_severity_level, -1) >= p_constraint_threshold then
5692 p_constraint_validation_status := 'F';
5693 else
5694 p_constraint_validation_status := 'E';
5695 end if;
5696
5697 end loop;
5698
5699 end;
5700 end if;
5701
5702 end;
5703 end if;
5704
5705 end;
5706 end if;
5707
5708 if p_element_value_type = 'PS' then
5709 begin
5710 /* Bug 3786457 Start */
5711 /* if p_element_value < 1 then
5712 l_cons_total := p_element_value * l_salary_total;
5713 else
5714 l_cons_total := p_element_value * l_salary_total / 100;
5715 end if; */
5716 l_cons_total := p_element_value * l_salary_total / 100;
5717 /* Bug 3786457 End */
5718
5719 end;
5720 elsif p_element_value_type = 'A' then
5721 l_cons_total := p_element_value;
5722 end if;
5723
5724 if l_posset_total is not null then
5725 begin
5726
5727 if p_prefix_operator = '<=' then
5728
5729 if l_posset_total <= l_cons_total then
5730 l_cons_failed := FND_API.G_TRUE;
5731 end if;
5732
5733 elsif p_prefix_operator = '>=' then
5734
5735 if l_posset_total >= l_cons_total then
5736 l_cons_failed := FND_API.G_TRUE;
5737 end if;
5738
5739 elsif p_prefix_operator = '<' then
5740
5741 if l_posset_total < l_cons_total then
5742 l_cons_failed := FND_API.G_TRUE;
5743 end if;
5744
5745 elsif p_prefix_operator = '>' then
5746
5747 if l_posset_total > l_cons_total then
5748 l_cons_failed := FND_API.G_TRUE;
5749 end if;
5750
5751 elsif p_prefix_operator = '=' then
5752
5753 if l_posset_total = l_cons_total then
5754 l_cons_failed := FND_API.G_TRUE;
5755 end if;
5756
5757 elsif p_prefix_operator = '<>' then
5758
5759 if l_posset_total <> l_cons_total then
5760 l_cons_failed := FND_API.G_TRUE;
5761 end if;
5762
5763 end if;
5764
5765 end;
5766 end if;
5767
5768 if FND_API.to_Boolean(l_cons_failed) then
5769 begin
5770
5771 if nvl(p_severity_level, -1) >= p_constraint_threshold then
5772 p_constraint_validation_status := 'F';
5773 else
5774 p_constraint_validation_status := 'E';
5775 end if;
5776
5777 l_userid := FND_GLOBAL.USER_ID;
5778 l_reqid := FND_GLOBAL.CONC_REQUEST_ID;
5779
5780 message_token('CONSTRAINT_SET', p_constraint_set_name);
5781 message_token('THRESHOLD', p_constraint_threshold);
5782 message_token('CONSTRAINT', p_constraint_name);
5783 message_token('SEVERITY_LEVEL', p_severity_level);
5784 message_token('ASSIGNMENT_VALUE', l_posset_total);
5785 message_token('OPERATOR', p_prefix_operator);
5786 message_token('FORMULA_VALUE', l_cons_total);
5787
5788 if FND_API.to_Boolean(p_summ_flag) then
5789 message_token('NAME', p_constraint_name);
5790 else
5791 message_token('NAME', p_position_name);
5792 end if;
5793
5794 message_token('YEAR', p_budget_year_name);
5795 add_message('PSB', 'PSB_CONSTRAINT_FAILURE');
5796
5797 l_description := FND_MSG_PUB.Get
5798 (p_encoded => FND_API.G_FALSE);
5799 FND_MSG_PUB.Delete_Msg;
5800
5801 insert into PSB_ERROR_MESSAGES
5802 (Concurrent_Request_ID,
5803 Process_ID,
5804 Source_Process,
5805 Description,
5806 Creation_Date,
5807 Created_By)
5808 values (l_reqid,
5809 p_worksheet_id,
5810 'WORKSHEET_CREATION',
5811 l_description,
5812 sysdate,
5813 l_userid);
5814
5815 end;
5816 else
5817 p_constraint_validation_status := 'S';
5818 end if;
5819
5820
5821 -- Initialize API return status to success
5822
5823 p_return_status := FND_API.G_RET_STS_SUCCESS;
5824
5825
5826 EXCEPTION
5827
5828 when FND_API.G_EXC_ERROR then
5829 p_return_status := FND_API.G_RET_STS_ERROR;
5830
5831 when FND_API.G_EXC_UNEXPECTED_ERROR then
5832 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5833
5834 when OTHERS then
5835 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5836 if FND_MSG_PUB.Check_Msg_Level
5837 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5838 then
5839 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
5840 'Process_PosCons_Step');
5841 end if;
5842
5843 END Process_PosCons_Step;
5844
5845 /* ----------------------------------------------------------------------- */
5846
5847 PROCEDURE Process_FTECons
5848 ( p_return_status OUT NOCOPY VARCHAR2,
5849 p_constraint_validation_status OUT NOCOPY VARCHAR2,
5850 p_worksheet_id IN NUMBER,
5851 p_data_extract_id IN NUMBER,
5852 p_sp_exists IN VARCHAR2,
5853 p_constraint_set_name IN VARCHAR2,
5854 p_constraint_threshold IN NUMBER,
5855 p_constraint_id IN NUMBER,
5856 p_constraint_name IN VARCHAR2,
5857 p_position_line_id IN NUMBER := FND_API.G_MISS_NUM,
5858 p_position_id IN NUMBER := FND_API.G_MISS_NUM,
5859 p_position_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
5860 p_budget_year_id IN NUMBER,
5861 p_budget_year_name IN VARCHAR2,
5862 p_currency_code IN VARCHAR2,
5863 p_severity_level IN NUMBER,
5864 p_summ_flag IN VARCHAR2
5865 ) IS
5866
5867 l_cons_failed VARCHAR2(1) := FND_API.G_FALSE;
5868
5869 l_posset_total NUMBER := 0;
5870 l_cons_total NUMBER := 0;
5871
5872 l_reqid NUMBER;
5873 l_userid NUMBER;
5874 l_description VARCHAR2(2000);
5875
5876 cursor c_Formula is
5877 select prefix_operator,
5878 amount
5879 from PSB_CONSTRAINT_FORMULAS
5880 where constraint_id = p_constraint_id;
5881
5882 cursor c_SumAll is
5883 select sum(nvl(a.annual_fte, 0)) Sum_FTE
5884 from PSB_WS_FTE_LINES a,
5885 PSB_WORKSHEETS b
5886 where exists
5887 (select 1
5888 from PSB_WS_LINES_POSITIONS c,
5889 PSB_WS_POSITION_LINES d,
5890 PSB_BUDGET_POSITIONS e,
5891 PSB_SET_RELATIONS f
5892 where c.position_line_id = a.position_line_id
5893 and c.position_line_id = d.position_line_id
5894 and c.worksheet_id = p_worksheet_id
5895 and d.position_id = e.position_id
5896 and e.data_extract_id = p_data_extract_id
5897 and e.account_position_set_id = f.account_position_set_id
5898 and f.constraint_id = p_constraint_id)
5899 and b.current_stage_seq between a.start_stage_seq and a.current_stage_seq
5900 and a.stage_set_id = b.stage_set_id
5901 and a.budget_year_id = p_budget_year_id
5902 and b.worksheet_id = p_worksheet_id;
5903
5904 cursor c_SumAllSP is
5905 select sum(nvl(a.annual_fte, 0)) Sum_FTE
5906 from PSB_WS_FTE_LINES a,
5907 PSB_WORKSHEETS b
5908 where exists
5909 (select 1
5910 from PSB_WS_LINES_POSITIONS c,
5911 PSB_WS_POSITION_LINES d,
5912 PSB_BUDGET_POSITIONS e,
5913 PSB_SET_RELATIONS f
5914 where c.position_line_id = a.position_line_id
5915 and c.position_line_id = d.position_line_id
5916 and c.worksheet_id = p_worksheet_id
5917 and d.position_id = e.position_id
5918 and e.data_extract_id = p_data_extract_id
5919 and e.account_position_set_id = f.account_position_set_id
5920 and f.constraint_id = p_constraint_id)
5921 and exists
5922 (select 1
5923 from PSB_WS_SUBMIT_SERVICE_PACKAGES g
5924 where g.service_package_id = a.service_package_id
5925 and g.worksheet_id = p_worksheet_id)
5926 and b.current_stage_seq between a.start_stage_seq and a.current_stage_seq
5927 and a.stage_set_id = b.stage_set_id
5928 and a.budget_year_id = p_budget_year_id
5929 and b.worksheet_id = p_worksheet_id;
5930
5931 cursor c_Sum is
5932 select /*+ ORDERED USE_NL(b a) INDEX(b PSB_WORKSHEETS_U1) INDEX(a PSB_WS_ELEMENT_LINES_N1) */
5933 sum(nvl(a.annual_fte, 0)) Sum_FTE
5934 from PSB_WS_FTE_LINES a,
5935 PSB_WORKSHEETS b
5936 where b.current_stage_seq between a.start_stage_seq and a.current_stage_seq
5937 and a.stage_set_id = b.stage_set_id
5938 and a.budget_year_id = p_budget_year_id
5939 and a.position_line_id = p_position_line_id
5940 and b.worksheet_id = p_worksheet_id;
5941
5942 cursor c_SumSP is
5943 select /*+ ORDERED USE_NL(b a) INDEX(b PSB_WORKSHEETS_U1) INDEX(a PSB_WS_ELEMENT_LINES_N1) */
5944 sum(nvl(a.annual_fte, 0)) Sum_FTE
5945 from PSB_WS_FTE_LINES a,
5946 PSB_WORKSHEETS b
5947 where exists
5948 (select 1
5949 from PSB_WS_SUBMIT_SERVICE_PACKAGES d
5950 where d.worksheet_id = p_worksheet_id
5951 and d.service_package_id = a.service_package_id)
5952 and b.current_stage_seq between a.start_stage_seq and a.current_stage_seq
5953 and a.stage_set_id = b.stage_set_id
5954 and a.budget_year_id = p_budget_year_id
5955 and a.position_line_id = p_position_line_id
5956 and b.worksheet_id = p_worksheet_id;
5957
5958 BEGIN
5959
5960 for c_Formula_Rec in c_Formula loop
5961
5962 l_cons_total := c_Formula_Rec.amount;
5963
5964 if not FND_API.to_Boolean(p_summ_flag) then
5965 begin
5966
5967 if FND_API.to_Boolean(p_sp_exists) then
5968 begin
5969
5970 for c_Sum_Rec in c_SumSP loop
5971 l_posset_total := c_Sum_Rec.Sum_FTE;
5972 end loop;
5973
5974 end;
5975 else
5976 begin
5977
5978 for c_Sum_Rec in c_Sum loop
5979 l_posset_total := c_Sum_Rec.Sum_FTE;
5980 end loop;
5981
5982 end;
5983 end if;
5984
5985 end;
5986 else
5987 begin
5988
5989 if FND_API.to_Boolean(p_sp_exists) then
5990 begin
5991
5992 for c_SumAll_Rec in c_SumAllSP loop
5993 l_posset_total := c_SumAll_Rec.Sum_FTE;
5994 end loop;
5995
5996 end;
5997 else
5998 begin
5999
6000 for c_SumAll_Rec in c_SumAll loop
6001 l_posset_total := c_SumAll_Rec.Sum_FTE;
6002 end loop;
6003
6004 end;
6005 end if;
6006
6007 end;
6008 end if;
6009
6010 if l_posset_total is not null then
6011 begin
6012
6013 if c_Formula_Rec.prefix_operator = '<=' then
6014
6015 if l_posset_total <= l_cons_total then
6016 l_cons_failed := FND_API.G_TRUE;
6017 end if;
6018
6019 elsif c_Formula_Rec.prefix_operator = '>=' then
6020
6021 if l_posset_total >= l_cons_total then
6022 l_cons_failed := FND_API.G_TRUE;
6023 end if;
6024
6025 elsif c_Formula_Rec.prefix_operator = '<' then
6026
6027 if l_posset_total < l_cons_total then
6028 l_cons_failed := FND_API.G_TRUE;
6029 end if;
6030
6031 elsif c_Formula_Rec.prefix_operator = '>' then
6032
6033 if l_posset_total > l_cons_total then
6034 l_cons_failed := FND_API.G_TRUE;
6035 end if;
6036
6037 elsif c_Formula_Rec.prefix_operator = '=' then
6038
6039 if l_posset_total = l_cons_total then
6040 l_cons_failed := FND_API.G_TRUE;
6041 end if;
6042
6043 elsif c_Formula_Rec.prefix_operator = '<>' then
6044
6045 if l_posset_total = l_cons_total then
6046 l_cons_failed := FND_API.G_TRUE;
6047 end if;
6048
6049 end if;
6050
6051 end;
6052 end if;
6053
6054 if FND_API.to_Boolean(l_cons_failed) then
6055 begin
6056
6057 if nvl(p_severity_level, -1) >= p_constraint_threshold then
6058 p_constraint_validation_status := 'F';
6059 else
6060 p_constraint_validation_status := 'E';
6061 end if;
6062
6063 l_userid := FND_GLOBAL.USER_ID;
6064 l_reqid := FND_GLOBAL.CONC_REQUEST_ID;
6065
6066 message_token('CONSTRAINT_SET', p_constraint_set_name);
6067 message_token('THRESHOLD', p_constraint_threshold);
6068 message_token('CONSTRAINT', p_constraint_name);
6069 message_token('SEVERITY_LEVEL', p_severity_level);
6070 message_token('ASSIGNMENT_VALUE', l_posset_total);
6071 message_token('OPERATOR', c_Formula_Rec.prefix_operator);
6072 message_token('FORMULA_VALUE', l_cons_total);
6073
6074 if FND_API.to_Boolean(p_summ_flag) then
6075 message_token('NAME', p_constraint_name);
6076 else
6077 message_token('NAME', p_position_name);
6078 end if;
6079
6080 message_token('YEAR', p_budget_year_name);
6081 add_message('PSB', 'PSB_CONSTRAINT_FAILURE');
6082
6083 l_description := FND_MSG_PUB.Get
6084 (p_encoded => FND_API.G_FALSE);
6085 FND_MSG_PUB.Delete_Msg;
6086
6087 insert into PSB_ERROR_MESSAGES
6088 (Concurrent_Request_ID,
6089 Process_ID,
6090 Source_Process,
6091 Description,
6092 Creation_Date,
6093 Created_By)
6094 values (l_reqid,
6095 p_worksheet_id,
6096 'WORKSHEET_CREATION',
6097 l_description,
6098 sysdate,
6099 l_userid);
6100
6101 end;
6102 else
6103 p_constraint_validation_status := 'S';
6104 end if;
6105
6106 end loop;
6107
6108
6109 -- Initialize API return status to success
6110
6111 p_return_status := FND_API.G_RET_STS_SUCCESS;
6112
6113
6114 EXCEPTION
6115
6116 when FND_API.G_EXC_ERROR then
6117 p_return_status := FND_API.G_RET_STS_ERROR;
6118
6119 when FND_API.G_EXC_UNEXPECTED_ERROR then
6120 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6121
6122 when OTHERS then
6123 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6124 if FND_MSG_PUB.Check_Msg_Level
6125 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
6126 then
6127 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
6128 'Process_FTECons');
6129 end if;
6130
6131 END Process_FTECons;
6132
6133 /* ----------------------------------------------------------------------- */
6134
6135 -- Add Token and Value to the Message Token array
6136
6137 PROCEDURE message_token(tokname IN VARCHAR2,
6138 tokval IN VARCHAR2) IS
6139
6140 BEGIN
6141
6142 if no_msg_tokens is null then
6143 no_msg_tokens := 1;
6144 else
6145 no_msg_tokens := no_msg_tokens + 1;
6146 end if;
6147
6148 msg_tok_names(no_msg_tokens) := tokname;
6149 msg_tok_val(no_msg_tokens) := tokval;
6150
6151 END message_token;
6152
6153 /* ----------------------------------------------------------------------- */
6154
6155 -- Define a Message Token with a Value and set the Message Name
6156
6157 -- Calls FND_MESSAGE server package to set the Message Stack. This message is
6158 -- retrieved by the calling program.
6159
6160 PROCEDURE add_message(appname IN VARCHAR2,
6161 msgname IN VARCHAR2) IS
6162
6163 i BINARY_INTEGER;
6164
6165 BEGIN
6166
6167 if ((appname is not null) and
6168 (msgname is not null)) then
6169
6170 FND_MESSAGE.SET_NAME(appname, msgname);
6171
6172 if no_msg_tokens is not null then
6173 for i in 1..no_msg_tokens loop
6174 FND_MESSAGE.SET_TOKEN(msg_tok_names(i), msg_tok_val(i));
6175 end loop;
6176 end if;
6177
6178 FND_MSG_PUB.Add;
6179
6180 end if;
6181
6182 -- Clear Message Token stack
6183
6184 no_msg_tokens := 0;
6185
6186 END add_message;
6187
6188 /* ----------------------------------------------------------------------- */
6189
6190 -- Get Debug Information
6191
6192 -- This Module is used to retrieve Debug Information for this Package. It
6193 -- prints Debug Information when run as a Batch Process from SQL*Plus. For
6194 -- the Debug Information to be printed on the Screen, the SQL*Plus parameter
6195 -- 'Serveroutput' should be set to 'ON'
6196
6197 FUNCTION Get_Debug RETURN VARCHAR2 IS
6198
6199 BEGIN
6200
6201 return(g_dbug);
6202
6203 END Get_Debug;
6204
6205 /* ----------------------------------------------------------------------- */
6206
6207
6208 END PSB_WS_POS3;