[Home] [Help]
PACKAGE BODY: APPS.PSB_WS_POS2
Source
1 PACKAGE BODY PSB_WS_POS2 AS
2 /* $Header: PSBVWP2B.pls 120.22 2006/05/05 11:09:32 matthoma ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PSB_WS_POS2';
5
6 g_default_fte CONSTANT NUMBER := 1;
7 g_pos_start_date DATE; -- bug 5065066
8
9 TYPE g_poselasgn_rec_type IS RECORD
10 ( worksheet_id NUMBER,
11 start_date DATE,
12 end_date DATE,
13 pay_element_id NUMBER,
14 pay_element_option_id NUMBER,
15 pay_basis VARCHAR2(10),
16 element_value_type VARCHAR2(2),
17 element_value NUMBER,
18 use_in_calc BOOLEAN );
19
20 TYPE g_poselasgn_tbl_type IS TABLE OF g_poselasgn_rec_type
21 INDEX BY BINARY_INTEGER;
22
23 g_poselem_assignments g_poselasgn_tbl_type;
24 g_num_poselem_assignments NUMBER;
25
26 TYPE g_poselrate_rec_type IS RECORD
27 ( worksheet_id NUMBER,
28 start_date DATE,
29 end_date DATE,
30 pay_element_id NUMBER,
31 pay_element_option_id NUMBER,
32 pay_basis VARCHAR2(10),
33 element_value_type VARCHAR2(2),
34 element_value NUMBER,
35 formula_id NUMBER );
36
37 TYPE g_poselrate_tbl_type IS TABLE OF g_poselrate_rec_type
38 INDEX BY BINARY_INTEGER;
39
40 g_poselem_rates g_poselrate_tbl_type;
41 g_num_poselem_rates NUMBER;
42
43 TYPE g_posfte_rec_type IS RECORD
44 ( worksheet_id NUMBER,
45 start_date DATE,
46 end_date DATE,
47 fte NUMBER );
48
49 TYPE g_posfte_tbl_type IS TABLE OF g_posfte_rec_type
50 INDEX BY BINARY_INTEGER;
51
52 g_posfte_assignments g_posfte_tbl_type;
53 g_num_posfte_assignments NUMBER;
54
55 TYPE g_poswkh_rec_type IS RECORD
56 ( worksheet_id NUMBER,
57 start_date DATE,
58 end_date DATE,
59 default_weekly_hours NUMBER );
60
61 TYPE g_poswkh_tbl_type IS TABLE OF g_poswkh_rec_type
62 INDEX BY BINARY_INTEGER;
63
64 g_poswkh_assignments g_poswkh_tbl_type;
65 g_num_poswkh_assignments NUMBER;
66
67 g_monthly_profile PSB_WS_ACCT1.g_prdamt_tbl_type;
68 g_num_monthly_profile NUMBER := 0;
69
70 g_quarterly_profile PSB_WS_ACCT1.g_prdamt_tbl_type;
71 g_num_quarterly_profile NUMBER := 0;
72
73 g_semiannual_profile PSB_WS_ACCT1.g_prdamt_tbl_type;
74 g_num_semiannual_profile NUMBER := 0;
75
76 TYPE g_posrecalc_rec_type IS RECORD
77 ( element_line_id NUMBER, pay_element_id NUMBER,
78 service_package_id NUMBER, recalc_flag VARCHAR2(1));
79
80 TYPE g_posrecalc_tbl_type IS TABLE OF g_posrecalc_rec_type
81 INDEX BY BINARY_INTEGER;
82
83 g_pc_recalc_costs g_posrecalc_tbl_type;
84 g_num_pc_recalc_costs NUMBER;
85
86 TYPE g_posredist_rec_type IS RECORD
87 ( account_line_id NUMBER, ccid NUMBER, service_package_id NUMBER,
88 element_set_id NUMBER, budget_group_id NUMBER, redist_flag VARCHAR2(1));
89
90 TYPE g_posredist_tbl_type IS TABLE OF g_posredist_rec_type
91 INDEX BY BINARY_INTEGER;
92
93 g_pd_recalc_costs g_posredist_tbl_type;
94 g_num_pd_recalc_costs NUMBER;
95
96 TYPE g_posrecalcfte_rec_type IS RECORD
97 ( fte_line_id NUMBER, annual_fte NUMBER, ratio NUMBER,
98 service_package_id NUMBER, recalc_flag VARCHAR2(1));
99
100 TYPE g_posrecalcfte_tbl_type IS TABLE OF g_posrecalcfte_rec_type
101 INDEX BY BINARY_INTEGER;
102
103 g_pf_recalc_fte g_posrecalcfte_tbl_type;
104 g_num_pf_recalc_fte NUMBER;
105
106 TYPE TokNameArray IS TABLE OF VARCHAR2(100)
107 INDEX BY BINARY_INTEGER;
108
109 TYPE TokValArray IS TABLE OF VARCHAR2(1000)
110 INDEX BY BINARY_INTEGER;
111
112 /* Bug No 1920021 Start */
113 g_weekly_hours_worksheet_id NUMBER;
114 /* Bug No 1920021 End */
115
116 /*For Bug No : 2811698 Start*/
117 g_fte_profile_option VARCHAR2(1);
118 /*For Bug No : 2811698 End*/
119
120 -- Number of Message Tokens
121
122 no_msg_tokens NUMBER := 0;
123
124 -- Message Token Name
125
126 msg_tok_names TokNameArray;
127
128 -- Message Token Value
129
130 msg_tok_val TokValArray;
131
132 g_dbug VARCHAR2(1000);
133
134 /* ----------------------------------------------------------------------- */
135 /* Private Function Definition */
136 /* ----------------------------------------------------------------------- */
137
138
139 /*===========================================================================+
140 | PROCEDURE pd |
141 +===========================================================================*/
142 --
143 -- API to print debug information, used during only development.
144 --
145 PROCEDURE pd( p_message IN VARCHAR2)
146 IS
147 BEGIN
148 NULL ;
149 --DBMS_OUTPUT.Put_Line(p_message) ;
150 END pd ;
151 /*---------------------------------------------------------------------------*/
152
153
154 PROCEDURE Calculate_Position_Cost_Year
155 ( p_return_status OUT NOCOPY VARCHAR2,
156 p_data_extract_id IN NUMBER,
157 p_business_group_id IN NUMBER,
158 p_position_line_id IN NUMBER,
159 p_position_id IN NUMBER,
160 p_position_name IN VARCHAR2,
161 p_budget_year_id IN NUMBER,
162 p_year_start_date IN DATE,
163 p_year_end_date IN DATE
164 );
165
166 PROCEDURE Cache_FTE_Profile
167 ( p_return_status OUT NOCOPY VARCHAR2,
168 p_position_id IN NUMBER,
169 p_data_extract_id IN NUMBER,
170 p_business_group_id IN NUMBER
171 );
172
173 PROCEDURE Distribute_Position_Cost
174 ( p_return_status OUT NOCOPY VARCHAR2,
175 p_root_budget_group_id IN NUMBER,
176 p_flex_code IN NUMBER,
177 p_rounding_factor IN NUMBER,
178 p_data_extract_id IN NUMBER,
179 p_business_group_id IN NUMBER,
180 p_position_id IN NUMBER,
181 p_position_line_id IN NUMBER,
182 p_budget_year_id IN NUMBER,
183 p_start_date IN DATE,
184 p_end_date IN DATE
185 );
186
187 PROCEDURE Distribute_Salary
188 ( p_return_status OUT NOCOPY VARCHAR2,
189 p_pay_element_id IN NUMBER,
190 p_root_budget_group_id IN NUMBER,
191 p_data_extract_id IN NUMBER,
192 p_flex_code IN NUMBER,
193 p_rounding_factor IN NUMBER,
194 p_position_line_id IN NUMBER,
195 p_position_id IN NUMBER,
196 p_budget_year_id IN NUMBER,
197 p_start_date IN DATE,
198 p_end_date IN DATE
199 );
200
201 PROCEDURE Distribute_Other_Elements
202 ( p_return_status OUT NOCOPY VARCHAR2,
203 p_pay_element_id IN NUMBER,
204 p_data_extract_id IN NUMBER,
205 p_flex_code IN NUMBER,
206 p_rounding_factor IN NUMBER,
207 p_position_line_id IN NUMBER,
208 p_position_id IN NUMBER,
209 p_budget_year_id IN NUMBER,
210 p_start_date IN DATE,
211 p_end_date IN DATE
212 );
213
214 PROCEDURE Update_Position_Cost
215 ( p_return_status OUT NOCOPY VARCHAR2,
216 p_position_line_id IN NUMBER,
217 p_position_start_date IN DATE,
218 p_position_end_date IN DATE,
219 p_worksheet_id IN NUMBER,
220 p_flex_mapping_set_id IN NUMBER,
221 p_global_worksheet_id IN NUMBER,
222 p_func_currency IN VARCHAR2,
223 p_rounding_factor IN NUMBER,
224 p_service_package_id IN NUMBER,
225 p_stage_set_id IN NUMBER,
226 p_start_stage_seq IN NUMBER,
227 p_current_stage_seq IN NUMBER,
228 p_budget_year_id IN NUMBER,
229 p_budget_group_id IN NUMBER
230 );
231
232 PROCEDURE message_token
233 ( tokname IN VARCHAR2,
234 tokval IN VARCHAR2
235 );
236
237 PROCEDURE add_message
238 ( appname IN VARCHAR2,
239 msgname IN VARCHAR2
240 );
241
242 /* ----------------------------------------------------------------------- */
243
244 PROCEDURE Create_Worksheet_Positions
245 ( p_return_status OUT NOCOPY VARCHAR2,
246 p_root_budget_group_id IN NUMBER,
247 p_global_worksheet_id IN NUMBER,
248 p_worksheet_id IN NUMBER,
249 p_global_worksheet IN VARCHAR2,
250 p_budget_group_id IN NUMBER,
251 p_worksheet_numyrs IN NUMBER,
252 p_rounding_factor IN NUMBER,
253 p_service_package_id IN NUMBER,
254 p_stage_set_id IN NUMBER,
255 p_start_stage_seq IN NUMBER,
256 p_current_stage_seq IN NUMBER,
257 p_data_extract_id IN NUMBER,
258 p_business_group_id IN NUMBER,
259 p_budget_calendar_id IN NUMBER,
260 p_parameter_set_id IN NUMBER,
261 p_func_currency IN VARCHAR2,
262 p_flex_mapping_set_id IN NUMBER,
263 p_flex_code IN NUMBER,
264 p_apply_element_parameters IN VARCHAR2,
265 p_apply_position_parameters IN VARCHAR2
266 ) IS
267
268 l_position_line_id NUMBER;
269 l_num_positions NUMBER := 0;
270
271 l_return_status VARCHAR2(1);
272
273 cursor c_Positions is
274 select position_id,
275 name,
276 effective_start_date,
277 effective_end_date
278 from PSB_POSITIONS
279 where (((effective_start_date <= PSB_WS_ACCT1.g_end_est_date)
280 and (effective_end_date is null))
281 or ((effective_start_date between PSB_WS_ACCT1.g_startdate_cy and PSB_WS_ACCT1.g_end_est_date)
282 or (effective_end_date between PSB_WS_ACCT1.g_startdate_cy and PSB_WS_ACCT1.g_end_est_date)
283 or ((effective_start_date < PSB_WS_ACCT1.g_startdate_cy)
284 and (effective_end_date > PSB_WS_ACCT1.g_end_est_date))))
285 and business_group_id = p_business_group_id
286 and data_extract_id = p_data_extract_id;
287
288 BEGIN
289
290 if ((nvl(PSB_WS_POS1.g_data_extract_id, FND_API.G_MISS_NUM) <> p_data_extract_id) or
291 (nvl(PSB_WS_POS1.g_business_group_id, FND_API.G_MISS_NUM) <> p_business_group_id)) then
292 begin
293
294 PSB_WS_POS1.Cache_Elements
295 (p_return_status => l_return_status,
296 p_data_extract_id => p_data_extract_id,
297 p_business_group_id => p_business_group_id,
298 p_worksheet_id => p_worksheet_id);
299
300 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
301 raise FND_API.G_EXC_ERROR;
302 end if;
303
304 end;
305 end if;
306
307 if nvl(PSB_WS_POS1.g_attr_busgrp_id, FND_API.G_MISS_NUM) <> p_business_group_id then
308 begin
309
310 PSB_WS_POS1.Cache_Named_Attributes
311 (p_return_status => l_return_status,
312 p_business_group_id => p_business_group_id);
313
314 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
315 raise FND_API.G_EXC_ERROR;
316 end if;
317
318 end;
319 end if;
320
321 if FND_API.to_Boolean(p_apply_element_parameters) then
322 begin
323
324 PSB_WS_POS3.Apply_Element_Parameters
325 (p_api_version => 1.0,
326 p_worksheet_id => p_worksheet_id,
327 p_global_worksheet => p_global_worksheet,
328 p_budget_group_id => p_budget_group_id,
329 p_data_extract_id => p_data_extract_id,
330 p_business_group_id => p_business_group_id,
331 p_func_currency => p_func_currency,
332 p_budget_calendar_id => p_budget_calendar_id,
333 p_parameter_set_id => p_parameter_set_id,
334 p_return_status => l_return_status);
335
336 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
337 raise FND_API.G_EXC_ERROR;
338 end if;
339
340 end;
341 end if;
342
343 commit work;
344
345 if FND_API.to_Boolean(p_apply_position_parameters) then
346 begin
347
348 PSB_WS_POS3.Apply_Position_Parameters
349 (p_api_version => 1.0,
350 p_worksheet_id => p_worksheet_id,
351 p_global_worksheet => p_global_worksheet,
352 p_budget_group_id => p_budget_group_id,
353 p_data_extract_id => p_data_extract_id,
354 p_business_group_id => p_business_group_id,
355 p_func_currency => p_func_currency,
356 p_budget_calendar_id => p_budget_calendar_id,
357 p_parameter_set_id => p_parameter_set_id,
358 p_return_status => l_return_status);
359
360 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
361 raise FND_API.G_EXC_ERROR;
362 end if;
363
364 end;
365 end if;
366
367 commit work;
368
369 for c_Positions_Rec in c_Positions loop
370
371 PSB_WS_POS1.g_salary_budget_group_id := null;
372
373 PSB_WS_POS1.Cache_Salary_Dist
374 (p_return_status => l_return_status,
375 p_worksheet_id => p_global_worksheet_id,
376 p_root_budget_group_id => p_root_budget_group_id,
377 p_flex_code => p_flex_code,
378 p_data_extract_id => p_data_extract_id,
379 p_position_id => c_Positions_Rec.position_id,
380 p_position_name => c_Positions_Rec.name,
381 p_start_date => PSB_WS_ACCT1.g_startdate_cy,
382 p_end_date => PSB_WS_ACCT1.g_end_est_date);
383
384 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
385 raise FND_API.G_EXC_ERROR;
386 end if;
387
388 PSB_WS_POS1.Create_Position_Lines
389 (p_api_version => 1.0,
390 p_return_status => l_return_status,
391 p_position_line_id => l_position_line_id,
392 p_worksheet_id => p_worksheet_id,
393 p_position_id => c_Positions_Rec.position_id,
394 p_budget_group_id => PSB_WS_POS1.g_salary_budget_group_id);
395
396 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
397 raise FND_API.G_EXC_ERROR;
398 end if;
399
400 G_POS_START_DATE := c_Positions_Rec.effective_start_date; -- Bug 5065066
401
402 Calculate_Position_Cost
403 (p_api_version => 1.0,
404 p_return_status => l_return_status,
405 p_worksheet_id => p_worksheet_id,
406 p_position_line_id => l_position_line_id,
407 p_recalculate_flag => FND_API.G_TRUE,
408 p_root_budget_group_id => p_root_budget_group_id,
409 p_global_worksheet_id => p_global_worksheet_id,
410 p_assign_worksheet_id => p_global_worksheet_id,
411 p_worksheet_numyrs => p_worksheet_numyrs,
412 p_rounding_factor => p_rounding_factor,
413 p_service_package_id => p_service_package_id,
414 p_stage_set_id => p_stage_set_id,
415 p_start_stage_seq => p_start_stage_seq,
416 p_current_stage_seq => p_current_stage_seq,
417 p_data_extract_id => p_data_extract_id,
418 p_business_group_id => p_business_group_id,
419 p_budget_calendar_id => p_budget_calendar_id,
420 p_func_currency => p_func_currency,
421 p_flex_mapping_set_id => p_flex_mapping_set_id,
422 p_flex_code => p_flex_code,
423 p_position_id => c_Positions_Rec.position_id,
424 p_position_name => c_Positions_Rec.name,
425 p_position_start_date => c_Positions_Rec.effective_start_date,
426 p_position_end_date => c_Positions_Rec.effective_end_date);
427
428 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
429 raise FND_API.G_EXC_ERROR;
430 end if;
431
432 l_num_positions := l_num_positions + 1;
433
434 if l_num_positions > PSB_WS_ACCT1.g_checkpoint_save then
435 commit work;
436 l_num_positions := 0;
437 end if;
438
439 end loop;
440
441
442 -- Initialize API return status to success
443
444 p_return_status := FND_API.G_RET_STS_SUCCESS;
445
446
447 EXCEPTION
448
449 when FND_API.G_EXC_ERROR then
450 p_return_status := FND_API.G_RET_STS_ERROR;
451
452 when FND_API.G_EXC_UNEXPECTED_ERROR then
453 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
454
455 when OTHERS then
456 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
457 if FND_MSG_PUB.Check_Msg_Level
458 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
459 then
460 FND_MSG_PUB.Add_Exc_Msg
461 (p_pkg_name => G_PKG_NAME,
462 p_procedure_name => 'Create_Worksheet_Positions');
463 end if;
464
465 END Create_Worksheet_Positions;
466
467 /* ----------------------------------------------------------------------- */
468
469 PROCEDURE Calculate_Position_Cost
470 ( p_api_version IN NUMBER,
471 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
472 p_return_status OUT NOCOPY VARCHAR2,
473 p_worksheet_id IN NUMBER,
474 p_position_line_id IN NUMBER,
475 p_recalculate_flag IN VARCHAR2 := FND_API.G_TRUE,
476 p_root_budget_group_id IN NUMBER,
477 p_global_worksheet_id IN NUMBER,
478 p_assign_worksheet_id IN NUMBER,
479 p_worksheet_numyrs IN NUMBER,
480 p_rounding_factor IN NUMBER,
481 p_service_package_id IN NUMBER,
482 p_stage_set_id IN NUMBER,
483 p_start_stage_seq IN NUMBER,
484 p_current_stage_seq IN NUMBER,
485 p_data_extract_id IN NUMBER,
486 p_business_group_id IN NUMBER,
487 p_budget_calendar_id IN NUMBER,
488 p_func_currency IN VARCHAR2,
489 p_flex_mapping_set_id IN NUMBER,
490 p_flex_code IN NUMBER,
491 p_position_id IN NUMBER,
492 p_position_name IN VARCHAR2,
493 p_position_start_date IN DATE,
494 p_position_end_date IN DATE,
495 p_budget_year_id IN NUMBER
496 ) IS
497
498 l_api_name CONSTANT VARCHAR2(30) := 'Calculate_Position_Cost';
499 l_api_version CONSTANT NUMBER := 1.0;
500
501 l_start_date DATE;
502 l_end_date DATE;
503
504 l_num_budget_years NUMBER;
505 l_num_proposed_years NUMBER := 0;
506
507 l_init_index BINARY_INTEGER;
508 l_year_index BINARY_INTEGER;
509
510 l_global_worksheet_id NUMBER;
511 l_assign_worksheet_id NUMBER;
512 l_stage_set_id NUMBER;
513 l_data_extract_id NUMBER;
514 l_budget_group_id NUMBER;
515 l_budget_calendar_id NUMBER;
516 l_ws_num_years NUMBER;
517 l_rounding_factor NUMBER;
518 l_current_stage_seq NUMBER;
519
520 l_root_budget_group_id NUMBER;
521 l_business_group_id NUMBER;
522 l_func_currency VARCHAR2(15);
523 l_flex_code NUMBER;
524
525 l_position_line_id NUMBER;
526 l_position_id NUMBER;
527 l_position_name VARCHAR2(240);
528 l_position_start_date DATE;
529 l_position_end_date DATE;
530
531 /* bug 3446226 Start */
532 l_fte_start_date DATE;
533 l_fte_end_date DATE;
534 /* bug 3446226 End */
535
536 l_service_package_id NUMBER;
537 l_start_stage_seq NUMBER;
538
539 l_element_found BOOLEAN;
540
541 l_attribute_value NUMBER;
542
543 l_return_status VARCHAR2(1);
544 l_msg_count NUMBER;
545 l_msg_data VARCHAR2(2000);
546
547 cursor c_WS is
548 select nvl(global_worksheet_id, worksheet_id) global_worksheet_id,
549 decode(local_copy_flag, 'Y', worksheet_id, nvl(global_worksheet_id, worksheet_id)) assign_worksheet_id,
550 stage_set_id,
551 nvl(data_extract_id, global_data_extract_id) data_extract_id,
552 budget_group_id,
553 budget_calendar_id,
554 num_proposed_years,
555 rounding_factor,
556 current_stage_seq
557 from PSB_WORKSHEETS_V
558 where worksheet_id = p_worksheet_id;
559
560 cursor c_SP is
561 select service_package_id
562 from PSB_SERVICE_PACKAGES
563 where base_service_package = 'Y'
564 and global_worksheet_id = l_global_worksheet_id;
565
566 cursor c_BG is
567 select nvl(root_budget_group_id, budget_group_id) root_budget_group_id,
568 nvl(business_group_id, root_business_group_id) business_group_id,
569 nvl(currency_code, root_currency_code) currency_code,
570 nvl(chart_of_accounts_id, root_chart_of_accounts_id) chart_of_accounts_id
571 from PSB_BUDGET_GROUPS_V
572 where budget_group_id = l_budget_group_id;
573
574 cursor c_Positions is
575 select a.position_id,
576 a.name,
577 a.effective_start_date,
578 a.effective_end_date
579 from PSB_POSITIONS a,
580 PSB_WS_POSITION_LINES b
581 where a.position_id = b.position_id
582 and b.position_line_id = p_position_line_id;
583
584 /* Bug No 2521570 Start */
585 /***** cursor c_Element_Assignments is
586 select worksheet_id,
587 pay_element_id,
588 pay_element_option_id,
589 pay_basis,
590 element_value_type,
591 element_value,
592 effective_start_date,
593 effective_end_date
594 from PSB_POSITION_ASSIGNMENTS
595 where (worksheet_id is null or worksheet_id = l_assign_worksheet_id)
596 and currency_code = l_func_currency
597 and assignment_type = 'ELEMENT'
598 and (((effective_start_date <= l_end_date)
599 and (effective_end_date is null))
600 or ((effective_start_date between l_start_date and l_end_date)
601 or (effective_end_date between l_start_date and l_end_date)
602 or ((effective_start_date < l_start_date)
603 and (effective_end_date > l_end_date))))
604 and position_id = l_position_id
605 order by effective_start_date,
606 effective_end_date,
607 element_value desc;*****/
608
609 cursor c_Element_Assignments is
610 select worksheet_id,
611 pay_element_id,
612 pay_element_option_id,
613 pay_basis,
614 element_value_type,
615 element_value,
616 effective_start_date,
617 effective_end_date
618 from PSB_POSITION_ASSIGNMENTS a
619 where currency_code = l_func_currency
620 and assignment_type = 'ELEMENT'
621 and position_id = l_position_id
622 and ((worksheet_id = l_assign_worksheet_id)
623 or (worksheet_id IS NULL
624 and not exists
625 (select 1
626 from psb_position_assignments c
627 where (
628 ( nvl(c.effective_start_date, l_end_date + 1)
629 between nvl(a.effective_start_date, l_end_date)
630 and nvl(a.effective_end_date, nvl(l_end_date, c.effective_start_date)))
631 OR ( nvl(a.effective_start_date, l_end_date + 1)
632 between nvl(c.effective_start_date, l_end_date)
633 and nvl(c.effective_end_date, nvl(l_end_date, a.effective_start_date)))
634 )
635 and c.position_id = a.position_id
636 and c.pay_element_id = a.pay_element_id
637 and c.worksheet_id = l_assign_worksheet_id
638 )
639 ))
640 order by effective_start_date,
641 effective_end_date,
642 element_value desc;
643 /* Bug No 2521570 End */
644
645 cursor c_Element_Rates is
646 select a.worksheet_id,
647 a.pay_element_id,
648 a.pay_element_option_id,
649 a.pay_basis,
650 a.element_value_type,
651 a.element_value,
652 a.formula_id,
653 a.effective_start_date,
654 a.effective_end_date
655 from PSB_PAY_ELEMENT_RATES a,
656 PSB_PAY_ELEMENTS b
657 where (a.worksheet_id is null or a.worksheet_id = l_global_worksheet_id)
658 and a.currency_code = l_func_currency
659 and exists
660 (select 1
661 from PSB_POSITION_ASSIGNMENTS c
662 where nvl(c.pay_element_option_id, FND_API.G_MISS_NUM) = nvl(a.pay_element_option_id, FND_API.G_MISS_NUM)
663 and (c.worksheet_id is null or c.worksheet_id = l_assign_worksheet_id)
664 and c.currency_code = l_func_currency
665 and (((c.effective_start_date <= l_end_date)
666 and (c.effective_end_date is null))
667 or ((c.effective_start_date between l_start_date and l_end_date)
668 or (c.effective_end_date between l_start_date and l_end_date)
669 or ((c.effective_start_date < l_start_date)
670 and (c.effective_end_date > l_end_date))))
671 and c.pay_element_id = a.pay_element_id
672 and c.position_id = l_position_id)
673 and (((a.effective_start_date <= l_end_date)
674 and (a.effective_end_date is null))
675 or ((a.effective_start_date between l_start_date and l_end_date)
676 or (a.effective_end_date between l_start_date and l_end_date)
677 or ((a.effective_start_date < l_start_date)
678 and (a.effective_end_date > l_end_date))))
679 and a.pay_element_id = b.pay_element_id
680 and b.business_group_id = l_business_group_id
681 and b.data_extract_id = l_data_extract_id
682 order by a.worksheet_id,
683 a.effective_start_date,
684 a.effective_end_date,
685 a.element_value desc;
686
687 cursor c_Attribute_Assignments is
688 select worksheet_id,
689 effective_start_date,
690 effective_end_date,
691 attribute_id,
692 -- Fixed bug # 3683644
693 FND_NUMBER.canonical_to_number(attribute_value) attribute_value,
694 attribute_value_id
695 from PSB_POSITION_ASSIGNMENTS
696 where attribute_id in (PSB_WS_POS1.g_fte_id, PSB_WS_POS1.g_default_wklyhrs_id)
697 and (worksheet_id is null or worksheet_id = l_assign_worksheet_id)
698 and assignment_type = 'ATTRIBUTE'
699 and (((effective_start_date <= l_end_date)
700 and (effective_end_date is null))
701 or ((effective_start_date between l_start_date and l_end_date)
702 or (effective_end_date between l_start_date and l_end_date)
703 or ((effective_start_date < l_start_date)
704 and (effective_end_date > l_end_date))))
705 and position_id = l_position_id
706 order by worksheet_id,
707 effective_start_date,
708 effective_end_date,
709 FND_NUMBER.canonical_to_number(attribute_value) desc; -- Fixed bug # 3683644
710
711 BEGIN
712
713 -- Standard call to check for call compatibility
714
715 if not FND_API.Compatible_API_Call (l_api_version,
716 p_api_version,
717 l_api_name,
718 G_PKG_NAME)
719 then
720 raise FND_API.G_EXC_UNEXPECTED_ERROR;
721 end if;
722
723 if ((nvl(p_global_worksheet_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM) or
724 (nvl(p_assign_worksheet_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM) or
725 (nvl(p_worksheet_numyrs, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM) or
726 (nvl(p_rounding_factor, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM) or
727 (nvl(p_stage_set_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM) or
728 (nvl(p_current_stage_seq, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM) or
729 (nvl(p_data_extract_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM) or
730 (nvl(p_budget_calendar_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)) then
731 begin
732
733 for c_WS_Rec in c_WS loop
734 l_global_worksheet_id := c_WS_Rec.global_worksheet_id;
735 l_assign_worksheet_id := c_WS_Rec.assign_worksheet_id;
736 l_ws_num_years := c_WS_Rec.num_proposed_years;
737 l_rounding_factor := c_WS_Rec.rounding_factor;
738 l_stage_set_id := c_WS_Rec.stage_set_id;
739 l_current_stage_seq := c_WS_Rec.current_stage_seq;
740 l_data_extract_id := c_WS_Rec.data_extract_id;
741 l_budget_calendar_id := c_WS_Rec.budget_calendar_id;
742 l_budget_group_id := c_WS_Rec.budget_group_id;
743 end loop;
744
745 end;
746 end if;
747
748 if nvl(p_global_worksheet_id, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM then
749 l_global_worksheet_id := p_global_worksheet_id;
750 end if;
751
752 if nvl(p_assign_worksheet_id, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM then
753 l_assign_worksheet_id := p_assign_worksheet_id;
754 end if;
755
756 /* Bug No 1920021 Start */
757 g_weekly_hours_worksheet_id := l_assign_worksheet_id;
758 /* Bug No 1920021 End */
759
760 /*For Bug No : 2811698 Start*/
761 --Cache the FTE profile option in a global field due to performance issues
762 --and this will be used across the process
763 IF g_fte_profile_option IS NULL THEN
764 g_fte_profile_option := nvl(fnd_profile.value('PSB_USE_FTE_ALLOCATION'),'N');
765 END IF;
766 /*For Bug No : 2811698 End*/
767
768 if nvl(p_worksheet_numyrs, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM then
769 l_ws_num_years := p_worksheet_numyrs;
770 end if;
771
772 if nvl(p_rounding_factor, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM then
773 l_rounding_factor := p_rounding_factor;
774 end if;
775
776 if nvl(p_stage_set_id, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM then
777 l_stage_set_id := p_stage_set_id;
778 end if;
779
780 if nvl(p_current_stage_seq, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM then
781 l_current_stage_seq := p_current_stage_seq;
782 end if;
783
784 if nvl(p_data_extract_id, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM then
785 l_data_extract_id := p_data_extract_id;
786 end if;
787
788 if nvl(p_budget_calendar_id, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM then
789 l_budget_calendar_id := p_budget_calendar_id;
790 end if;
791
792 if nvl(p_service_package_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM then
793 begin
794
795 for c_sp_rec in c_sp loop
796 l_service_package_id := c_sp_rec.service_package_id;
797 end loop;
798
799 end;
800 else
801 l_service_package_id := p_service_package_id;
802 end if;
803
804 if nvl(p_start_stage_seq, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM then
805 l_start_stage_seq := l_current_stage_seq;
806 else
807 l_start_stage_seq := p_start_stage_seq;
808 end if;
809
810 if ((nvl(p_root_budget_group_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM) or
811 (nvl(p_business_group_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM) or
812 (nvl(p_func_currency, FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR) or
813 (nvl(p_flex_code, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)) then
814 begin
815
816 for c_BG_Rec in c_BG loop
817 l_root_budget_group_id := c_BG_Rec.root_budget_group_id;
818 l_business_group_id := c_BG_Rec.business_group_id;
819 l_func_currency := c_BG_Rec.currency_code;
820 l_flex_code := c_BG_Rec.chart_of_accounts_id;
821 end loop;
822
823 end;
824 end if;
825
826 if nvl(p_root_budget_group_id, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM then
827 l_root_budget_group_id := p_root_budget_group_id;
828 end if;
829
830 if nvl(p_business_group_id, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM then
831 l_business_group_id := p_business_group_id;
832 end if;
833
834 if nvl(p_func_currency, FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR then
835 l_func_currency := p_func_currency;
836 end if;
837
838 if nvl(p_flex_code, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM then
839 l_flex_code := p_flex_code;
840 end if;
841
842 if ((nvl(p_position_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM) or
843 (nvl(p_position_name, FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR) or
844 (nvl(p_position_start_date, FND_API.G_MISS_DATE) = FND_API.G_MISS_DATE) or
845 (nvl(p_position_end_date, FND_API.G_MISS_DATE) = FND_API.G_MISS_DATE)) then
846 begin
847
848 for c_Positions_Rec in c_Positions loop
849 l_position_id := c_Positions_Rec.position_id;
850 l_position_name := c_Positions_Rec.name;
851 l_position_start_date := c_Positions_Rec.effective_start_date;
852 l_position_end_date := c_Positions_Rec.effective_end_date;
853 end loop;
854
855 end;
856 end if;
857
858 if nvl(p_position_id, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM then
859 l_position_id := p_position_id;
860 end if;
861
862 if nvl(p_position_name, FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR then
863 l_position_name := p_position_name;
864 end if;
865
866 if nvl(p_position_start_date, FND_API.G_MISS_DATE) <> FND_API.G_MISS_DATE then
867 l_position_start_date := p_position_start_date;
868 end if;
869
870 if nvl(p_position_end_date, FND_API.G_MISS_DATE) <> FND_API.G_MISS_DATE then
871 l_position_end_date := p_position_end_date;
872 end if;
873
874 if l_budget_calendar_id <> nvl(PSB_WS_ACCT1.g_budget_calendar_id, FND_API.G_MISS_NUM) then
875 begin
876
877 PSB_WS_ACCT1.Cache_Budget_Calendar
878 (p_return_status => l_return_status,
879 p_budget_calendar_id => l_budget_calendar_id);
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 end if;
887
888 l_start_date := greatest(PSB_WS_ACCT1.g_startdate_cy, l_position_start_date);
889 l_end_date := least(PSB_WS_ACCT1.g_end_est_date, nvl(l_position_end_date, PSB_WS_ACCT1.g_end_est_date));
890
891 if FND_API.to_Boolean(p_recalculate_flag) then
892 begin
893
894 PSB_WS_POS1.g_salary_budget_group_id := null;
895
896 PSB_WS_POS1.Cache_Salary_Dist
897 (p_return_status => l_return_status,
898 p_worksheet_id => l_global_worksheet_id,
899 p_root_budget_group_id => l_root_budget_group_id,
900 p_flex_code => l_flex_code,
901 p_data_extract_id => l_data_extract_id,
902 p_position_id => l_position_id,
903 p_position_name => l_position_name,
904 p_start_date => l_start_date,
905 p_end_date => l_end_date);
906
907 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
908 raise FND_API.G_EXC_ERROR;
909 end if;
910
911 PSB_WS_POS1.Create_Position_Lines
912 (p_api_version => 1.0,
913 p_return_status => l_return_status,
914 p_position_line_id => l_position_line_id,
915 p_worksheet_id => p_worksheet_id,
916 p_position_id => l_position_id,
917 p_budget_group_id => PSB_WS_POS1.g_salary_budget_group_id);
918
919 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
920 raise FND_API.G_EXC_ERROR;
921 end if;
922
923 end;
924 end if;
925
926 for l_init_index in 1..g_poselem_assignments.Count loop
927 g_poselem_assignments(l_init_index).worksheet_id := null;
928 g_poselem_assignments(l_init_index).start_date := null;
929 g_poselem_assignments(l_init_index).end_date := null;
930 g_poselem_assignments(l_init_index).pay_element_id := null;
931 g_poselem_assignments(l_init_index).pay_element_option_id := null;
932 g_poselem_assignments(l_init_index).pay_basis := null;
933 g_poselem_assignments(l_init_index).element_value_type := null;
934 g_poselem_assignments(l_init_index).element_value := null;
935 g_poselem_assignments(l_init_index).use_in_calc := null;
936 end loop;
937
938 g_num_poselem_assignments := 0;
939
940 for l_init_index in 1..g_poselem_rates.Count loop
941 g_poselem_rates(l_init_index).worksheet_id := null;
942 g_poselem_rates(l_init_index).start_date := null;
943 g_poselem_rates(l_init_index).end_date := null;
944 g_poselem_rates(l_init_index).pay_element_id := null;
945 g_poselem_rates(l_init_index).pay_element_option_id := null;
946 g_poselem_rates(l_init_index).pay_basis := null;
947 g_poselem_rates(l_init_index).element_value_type := null;
948 g_poselem_rates(l_init_index).element_value := null;
949 g_poselem_rates(l_init_index).formula_id := null;
950 end loop;
951
952 g_num_poselem_rates := 0;
953
954 for l_init_index in 1..g_posfte_assignments.Count loop
955 g_posfte_assignments(l_init_index).worksheet_id := null;
956 g_posfte_assignments(l_init_index).start_date := null;
957 g_posfte_assignments(l_init_index).end_date := null;
958 g_posfte_assignments(l_init_index).fte := null;
959 end loop;
960
961 g_num_posfte_assignments := 0;
962
963 for l_init_index in 1..g_poswkh_assignments.Count loop
964 g_poswkh_assignments(l_init_index).worksheet_id := null;
965 g_poswkh_assignments(l_init_index).start_date := null;
966 g_poswkh_assignments(l_init_index).end_date := null;
967 g_poswkh_assignments(l_init_index).default_weekly_hours := null;
968 end loop;
969
970 g_num_poswkh_assignments := 0;
971
972 if nvl(PSB_WS_POS1.g_attr_busgrp_id, FND_API.G_MISS_NUM) <> l_business_group_id then
973 begin
974
975 PSB_WS_POS1.Cache_Named_Attributes
976 (p_return_status => l_return_status,
977 p_business_group_id => l_business_group_id);
978
979 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
980 raise FND_API.G_EXC_ERROR;
981 end if;
982
983 end;
984 end if;
985
986 PSB_WS_POS1.Cache_Named_Attribute_Values
987 (p_return_status => l_return_status,
988 p_worksheet_id => l_assign_worksheet_id,
989 p_data_extract_id => l_data_extract_id,
990 p_position_id => l_position_id);
991
992 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
993 raise FND_API.G_EXC_ERROR;
994 end if;
995
996 if ((nvl(PSB_WS_POS1.g_data_extract_id, FND_API.G_MISS_NUM) <> l_data_extract_id) or
997 (nvl(PSB_WS_POS1.g_business_group_id, FND_API.G_MISS_NUM) <> l_business_group_id)) then
998 begin
999
1000 PSB_WS_POS1.Cache_Elements
1001 (p_return_status => l_return_status,
1002 p_data_extract_id => l_data_extract_id,
1003 p_business_group_id => l_business_group_id,
1004 p_worksheet_id => p_worksheet_id);
1005
1006 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1007 raise FND_API.G_EXC_ERROR;
1008 end if;
1009
1010 end;
1011 end if;
1012
1013 for c_Element_Assignments_Rec in c_Element_Assignments loop
1014
1015 g_num_poselem_assignments := g_num_poselem_assignments + 1;
1016
1017 g_poselem_assignments(g_num_poselem_assignments).worksheet_id := c_Element_Assignments_Rec.worksheet_id;
1018 g_poselem_assignments(g_num_poselem_assignments).start_date := c_Element_Assignments_Rec.effective_start_date;
1019 g_poselem_assignments(g_num_poselem_assignments).end_date := c_Element_Assignments_Rec.effective_end_date;
1020 g_poselem_assignments(g_num_poselem_assignments).pay_element_id := c_Element_Assignments_Rec.pay_element_id;
1021 g_poselem_assignments(g_num_poselem_assignments).pay_element_option_id := c_Element_Assignments_Rec.pay_element_option_id;
1022 g_poselem_assignments(g_num_poselem_assignments).pay_basis := c_Element_Assignments_Rec.pay_basis;
1023 g_poselem_assignments(g_num_poselem_assignments).element_value_type := c_Element_Assignments_Rec.element_value_type;
1024 g_poselem_assignments(g_num_poselem_assignments).element_value := c_Element_Assignments_Rec.element_value;
1025 g_poselem_assignments(g_num_poselem_assignments).use_in_calc := FALSE;
1026
1027 end loop;
1028
1029 for l_element_index in 1..PSB_WS_POS1.g_num_elements loop
1030
1031 l_element_found := FALSE;
1032
1033 for l_elemassign_index in 1..g_num_poselem_assignments loop
1034
1035 if ((g_poselem_assignments(l_elemassign_index).pay_element_id = PSB_WS_POS1.g_elements(l_element_index).pay_element_id) and
1036 (g_poselem_assignments(l_elemassign_index).worksheet_id is not null)) then
1037 begin
1038 l_element_found := TRUE;
1039 g_poselem_assignments(l_elemassign_index).use_in_calc := TRUE;
1040 end;
1041 end if;
1042
1043 end loop;
1044
1045 if not (l_element_found) then
1046 begin
1047
1048 for l_elemassign_index in 1..g_num_poselem_assignments loop
1049
1050 if ((g_poselem_assignments(l_elemassign_index).pay_element_id = PSB_WS_POS1.g_elements(l_element_index).pay_element_id) and
1051 (g_poselem_assignments(l_elemassign_index).worksheet_id is null)) then
1052 g_poselem_assignments(l_elemassign_index).use_in_calc := TRUE;
1053 end if;
1054
1055 end loop;
1056
1057 end;
1058 end if;
1059
1060 end loop;
1061
1062 for c_Element_Rates_Rec in c_Element_Rates loop
1063
1064 g_num_poselem_rates := g_num_poselem_rates + 1;
1065
1066 g_poselem_rates(g_num_poselem_rates).worksheet_id := c_Element_Rates_Rec.worksheet_id;
1067 g_poselem_rates(g_num_poselem_rates).start_date := c_Element_Rates_Rec.effective_start_date;
1068 g_poselem_rates(g_num_poselem_rates).end_date := c_Element_Rates_Rec.effective_end_date;
1069 g_poselem_rates(g_num_poselem_rates).pay_element_id := c_Element_Rates_Rec.pay_element_id;
1070 g_poselem_rates(g_num_poselem_rates).pay_element_option_id := c_Element_Rates_Rec.pay_element_option_id;
1071 g_poselem_rates(g_num_poselem_rates).pay_basis := c_Element_Rates_Rec.pay_basis;
1072 g_poselem_rates(g_num_poselem_rates).element_value_type := c_Element_Rates_Rec.element_value_type;
1073 g_poselem_rates(g_num_poselem_rates).element_value := c_Element_Rates_Rec.element_value;
1074 g_poselem_rates(g_num_poselem_rates).formula_id := c_Element_Rates_Rec.formula_id;
1075
1076 end loop;
1077
1078 for c_Attributes_Rec in c_Attribute_Assignments loop
1079
1080 l_attribute_value := null;
1081
1082 if ((c_Attributes_Rec.attribute_value is null) and (c_Attributes_Rec.attribute_value_id is not null)) then
1083 l_attribute_value := Get_Attribute_Value(c_Attributes_Rec.attribute_value_id);
1084 end if;
1085
1086 if c_Attributes_Rec.attribute_id = PSB_WS_POS1.g_fte_id then
1087 begin
1088
1089 g_num_posfte_assignments := g_num_posfte_assignments + 1;
1090
1091 g_posfte_assignments(g_num_posfte_assignments).worksheet_id := c_Attributes_Rec.worksheet_id;
1092 g_posfte_assignments(g_num_posfte_assignments).start_date := c_Attributes_Rec.effective_start_date;
1093 g_posfte_assignments(g_num_posfte_assignments).end_date := c_Attributes_Rec.effective_end_date;
1094 g_posfte_assignments(g_num_posfte_assignments).fte := nvl(c_Attributes_Rec.attribute_value, l_attribute_value);
1095
1096 end;
1097 elsif c_Attributes_Rec.attribute_id = PSB_WS_POS1.g_default_wklyhrs_id then
1098 begin
1099
1100 g_num_poswkh_assignments := g_num_poswkh_assignments + 1;
1101
1102 g_poswkh_assignments(g_num_poswkh_assignments).worksheet_id := c_Attributes_Rec.worksheet_id;
1103 g_poswkh_assignments(g_num_poswkh_assignments).start_date := c_Attributes_Rec.effective_start_date;
1104 g_poswkh_assignments(g_num_poswkh_assignments).end_date := c_Attributes_Rec.effective_end_date;
1105 g_poswkh_assignments(g_num_poswkh_assignments).default_weekly_hours := nvl(c_Attributes_Rec.attribute_value, l_attribute_value);
1106
1107 end;
1108 end if;
1109
1110 end loop;
1111
1112 if g_num_poselem_assignments > 0 then
1113 begin
1114
1115 PSB_WS_POS1.Initialize_Calc;
1116
1117 PSB_WS_POS1.Initialize_Dist;
1118
1119 /*For Bug No : 2811698 Start*/
1120 --Call the folowing procedure when FTE profile option is set to 'Y'
1121 if g_fte_profile_option = 'Y' then
1122 Cache_FTE_Profile
1123 (p_return_status => l_return_status,
1124 p_position_id => l_position_id,
1125 p_data_extract_id => l_data_extract_id,
1126 p_business_group_id => l_business_group_id);
1127
1128 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1129 raise FND_API.G_EXC_ERROR;
1130 end if;
1131 end if;
1132 /*For Bug No : 2811698 End*/
1133
1134 -- the following IF clause is added as part of bug fix#4379636
1135 IF P_BUDGET_YEAR_ID IS NOT NULL THEN
1136
1137 FOR l_year_index IN 1..PSB_WS_ACCT1.g_num_budget_years LOOP
1138
1139 if PSB_WS_ACCT1.g_budget_years(l_year_index).year_type in ('CY', 'PP')
1140 and PSB_WS_ACCT1.g_budget_years(l_year_index).budget_year_id = p_budget_year_id then
1141 begin
1142
1143 Calculate_Position_Cost_Year
1144 (p_return_status => l_return_status,
1145 p_data_extract_id => l_data_extract_id,
1146 p_business_group_id => l_business_group_id,
1147 p_position_line_id => p_position_line_id,
1148 p_position_id => l_position_id,
1149 p_position_name => l_position_name,
1150 p_budget_year_id => p_budget_year_id,
1151 p_year_start_date => PSB_WS_ACCT1.g_budget_years(l_year_index).start_date,
1152 p_year_end_date => PSB_WS_ACCT1.g_budget_years(l_year_index).end_date);
1153
1154 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1155 raise FND_API.G_EXC_ERROR;
1156 end if;
1157
1158 end;
1159 end if;
1160
1161 END LOOP;
1162
1163 ELSE
1164
1165 FOR l_year_index IN 1..PSB_WS_ACCT1.g_num_budget_years LOOP
1166
1167 if PSB_WS_ACCT1.g_budget_years(l_year_index).year_type in ('CY', 'PP') then
1168 begin
1169
1170 Calculate_Position_Cost_Year
1171 (p_return_status => l_return_status,
1172 p_data_extract_id => l_data_extract_id,
1173 p_business_group_id => l_business_group_id,
1174 p_position_line_id => p_position_line_id,
1175 p_position_id => l_position_id,
1176 p_position_name => l_position_name,
1177 p_budget_year_id => PSB_WS_ACCT1.g_budget_years(l_year_index).budget_year_id,
1178 p_year_start_date => PSB_WS_ACCT1.g_budget_years(l_year_index).start_date,
1179 p_year_end_date => PSB_WS_ACCT1.g_budget_years(l_year_index).end_date);
1180
1181 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1182 raise FND_API.G_EXC_ERROR;
1183 end if;
1184
1185 end;
1186 end if;
1187
1188 END LOOP;
1189 END IF;
1190
1191 -- the following IF clause is added as part of bug fix#4379636
1192 IF P_BUDGET_YEAR_ID IS NOT NULL THEN
1193
1194 FOR l_year_index IN 1..PSB_WS_ACCT1.g_num_budget_years LOOP
1195
1196 IF PSB_WS_ACCT1.g_budget_years(l_year_index).year_type in ('CY', 'PP') AND
1197 PSB_WS_ACCT1.g_budget_years(l_year_index).budget_year_id = p_budget_year_id THEN
1198 begin
1199
1200 l_start_date := greatest(PSB_WS_ACCT1.g_budget_years(l_year_index).start_date, l_position_start_date);
1201 l_end_date := least(PSB_WS_ACCT1.g_budget_years(l_year_index).end_date, nvl(l_position_end_date,
1202 PSB_WS_ACCT1.g_budget_years(l_year_index).end_date));
1203
1204 Distribute_Position_Cost
1205 (p_return_status => l_return_status,
1206 p_root_budget_group_id => l_root_budget_group_id,
1207 p_flex_code => l_flex_code,
1208 p_rounding_factor => l_rounding_factor,
1209 p_data_extract_id => l_data_extract_id,
1210 p_business_group_id => l_business_group_id,
1211 p_position_id => l_position_id,
1212 p_position_line_id => p_position_line_id,
1213 p_budget_year_id => p_budget_year_id,
1214 p_start_date => l_start_date,
1215 p_end_date => l_end_date);
1216
1217 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1218 raise FND_API.G_EXC_ERROR;
1219 end if;
1220
1221 end;
1222 end if;
1223
1224 END LOOP;
1225
1226 ELSE
1227
1228 FOR l_year_index in 1..PSB_WS_ACCT1.g_num_budget_years LOOP
1229
1230 if PSB_WS_ACCT1.g_budget_years(l_year_index).year_type in ('CY', 'PP') then
1231 begin
1232
1233 l_start_date := greatest(PSB_WS_ACCT1.g_budget_years(l_year_index).start_date, l_position_start_date);
1234 l_end_date := least(PSB_WS_ACCT1.g_budget_years(l_year_index).end_date, nvl(l_position_end_date,
1235 PSB_WS_ACCT1.g_budget_years(l_year_index).end_date));
1236
1237 Distribute_Position_Cost
1238 (p_return_status => l_return_status,
1239 p_root_budget_group_id => l_root_budget_group_id,
1240 p_flex_code => l_flex_code,
1241 p_rounding_factor => l_rounding_factor,
1242 p_data_extract_id => l_data_extract_id,
1243 p_business_group_id => l_business_group_id,
1244 p_position_id => l_position_id,
1245 p_position_line_id => p_position_line_id,
1246 p_budget_year_id => PSB_WS_ACCT1.g_budget_years(l_year_index).budget_year_id,
1247 p_start_date => l_start_date,
1248 p_end_date => l_end_date);
1249
1250 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1251 raise FND_API.G_EXC_ERROR;
1252 end if;
1253
1254 end;
1255 end if;
1256 END LOOP;
1257 END IF;
1258
1259 /* bug 3446226 Start */
1260 FOR l_init_index in 1..g_posfte_assignments.Count LOOP
1261
1262 IF g_posfte_assignments(l_init_index).start_date > l_position_start_date THEN
1263 l_fte_start_date:= g_posfte_assignments(l_init_index).start_date;
1264 ELSE
1265 l_fte_start_date := l_position_start_date;
1266 END IF;
1267
1268 IF g_posfte_assignments(l_init_index).end_date < l_position_end_date THEN
1269 l_fte_end_date := g_posfte_assignments(l_init_index).end_date;
1270 ELSE
1271 l_fte_end_date := l_position_end_date;
1272 END IF;
1273
1274 END LOOP;
1275 /* bug 3446226 End */
1276
1277 -- the following IF clause is added as part of bug fix#4379636
1278 IF p_budget_year_id IS NOT NULL THEN
1279
1280 FOR l_year_index in 1..PSB_WS_ACCT1.g_num_budget_years LOOP
1281
1282 IF PSB_WS_ACCT1.g_budget_years(l_year_index).year_type in ('CY', 'PP') AND
1283 PSB_WS_ACCT1.g_budget_years(l_year_index).budget_year_id = p_budget_year_id THEN
1284
1285 begin
1286
1287 l_num_budget_years := least(ceil(months_between(nvl(l_position_end_date, PSB_WS_ACCT1.g_end_est_date),
1288 nvl(l_position_start_date, PSB_WS_ACCT1.g_startdate_pp)) / 12),
1289 nvl(greatest(nvl(l_ws_num_years, 0), nvl(PSB_WS_POS1.g_budget_group_numyrs, 0)),
1290 PSB_WS_ACCT1.g_max_num_years));
1291
1292 if (l_num_proposed_years < (l_num_budget_years + 1)) then
1293 begin
1294 -- bug 3446226 passed l_fte_start_date and l_fte_end_date instead of
1295 -- l_position_start_date and l_position_end_date
1296
1297 Update_Position_Cost
1298 (p_return_status => l_return_status,
1299 p_position_line_id => p_position_line_id,
1300 p_position_start_date => l_fte_start_date,
1301 p_position_end_date => l_fte_end_date,
1302 p_worksheet_id => p_worksheet_id,
1303 p_flex_mapping_set_id => p_flex_mapping_set_id,
1304 p_global_worksheet_id => l_global_worksheet_id,
1305 p_func_currency => l_func_currency,
1306 p_rounding_factor => l_rounding_factor,
1307 p_service_package_id => l_service_package_id,
1308 p_stage_set_id => l_stage_set_id,
1309 p_start_stage_seq => l_start_stage_seq,
1310 p_current_stage_seq => l_current_stage_seq,
1311 p_budget_year_id => p_budget_year_id,
1312 p_budget_group_id => PSB_WS_POS1.g_salary_budget_group_id);
1313
1314 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1315 raise FND_API.G_EXC_ERROR;
1316 end if;
1317
1318 l_num_proposed_years := l_num_proposed_years + 1;
1319
1320 end;
1321 end if;
1322
1323 end;
1324 end if;
1325
1326 end loop;
1327
1328 ELSE
1329
1330 FOR l_year_index in 1..PSB_WS_ACCT1.g_num_budget_years LOOP
1331
1332 IF PSB_WS_ACCT1.g_budget_years(l_year_index).year_type in ('CY', 'PP') THEN
1333 BEGIN
1334
1335 l_num_budget_years := least(ceil(months_between(nvl(l_position_end_date, PSB_WS_ACCT1.g_end_est_date),
1336 nvl(l_position_start_date, PSB_WS_ACCT1.g_startdate_pp)) / 12),
1337 nvl(greatest(nvl(l_ws_num_years, 0), nvl(PSB_WS_POS1.g_budget_group_numyrs, 0)),
1338 PSB_WS_ACCT1.g_max_num_years));
1339
1340 if (l_num_proposed_years < (l_num_budget_years + 1)) then
1341 begin
1342 -- bug 3446226 passed l_fte_start_date and l_fte_end_date instead of
1343 -- l_position_start_date and l_position_end_date
1344
1345 Update_Position_Cost
1346 (p_return_status => l_return_status,
1347 p_position_line_id => p_position_line_id,
1348 p_position_start_date => l_fte_start_date,
1349 p_position_end_date => l_fte_end_date,
1350 p_worksheet_id => p_worksheet_id,
1351 p_flex_mapping_set_id => p_flex_mapping_set_id,
1352 p_global_worksheet_id => l_global_worksheet_id,
1353 p_func_currency => l_func_currency,
1354 p_rounding_factor => l_rounding_factor,
1355 p_service_package_id => l_service_package_id,
1356 p_stage_set_id => l_stage_set_id,
1357 p_start_stage_seq => l_start_stage_seq,
1358 p_current_stage_seq => l_current_stage_seq,
1359 p_budget_year_id => PSB_WS_ACCT1.g_budget_years(l_year_index).budget_year_id,
1360 p_budget_group_id => PSB_WS_POS1.g_salary_budget_group_id);
1361
1362 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1363 raise FND_API.G_EXC_ERROR;
1364 end if;
1365
1366 l_num_proposed_years := l_num_proposed_years + 1;
1367
1368 end;
1369 end if;
1370
1371 end;
1372 end if;
1373
1374 END LOOP;
1375
1376 END IF;
1377
1378 END;
1379 END IF;
1380
1381
1382 -- Initialize API return status to success
1383
1384 p_return_status := FND_API.G_RET_STS_SUCCESS;
1385
1386
1387 EXCEPTION
1388
1389 when FND_API.G_EXC_ERROR then
1390 p_return_status := FND_API.G_RET_STS_ERROR;
1391 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
1392 p_data => l_msg_data);
1393
1394
1395 when FND_API.G_EXC_UNEXPECTED_ERROR then
1396 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1397 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
1398 p_data => l_msg_data);
1399
1400 when OTHERS then
1401 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1402
1403 if FND_MSG_PUB.Check_Msg_Level
1404 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1405 then
1406 FND_MSG_PUB.Add_Exc_Msg
1407 (p_pkg_name => G_PKG_NAME,
1408 p_procedure_name => l_api_name);
1409 end if;
1410
1411 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
1412 p_data => l_msg_data);
1413
1414 END Calculate_Position_Cost;
1415
1416 /* ----------------------------------------------------------------------- */
1417
1418 PROCEDURE Calculate_Position_Cost_Year
1419 ( p_return_status OUT NOCOPY VARCHAR2,
1420 p_data_extract_id IN NUMBER,
1421 p_business_group_id IN NUMBER,
1422 p_position_line_id IN NUMBER,
1423 p_position_id IN NUMBER,
1424 p_position_name IN VARCHAR2,
1425 p_budget_year_id IN NUMBER,
1426 p_year_start_date IN DATE,
1427 p_year_end_date IN DATE
1428 ) IS
1429
1430 /* Bug#1920021: Start
1431 Bug#3212814: Modified the first statment and added the second one. We
1432 need to find attribute values based on value table flag for attribute.
1433 */
1434 CURSOR l_default_weekly_hrs_csr IS
1435 -- Fixed bug # 3683644
1436 SELECT MAX(FND_NUMBER.canonical_TO_NUMBER(attribute_value)) attribute_value
1437 FROM psb_position_assignments
1438 WHERE attribute_id = PSB_WS_POS1.g_default_wklyhrs_id
1439 AND (worksheet_id IS NULL OR worksheet_id = g_weekly_hours_worksheet_id)
1440 AND assignment_type = 'ATTRIBUTE'
1441 AND position_id = p_position_id ;
1442 --
1443 CURSOR l_default_weekly_hrs_vt_csr IS
1444 -- Fixed bug # 3683644
1445 SELECT MAX(FND_NUMBER.canonical_TO_NUMBER(vals.attribute_value)) attribute_value
1446 FROM psb_position_assignments asgn ,
1447 psb_attribute_values vals
1448 WHERE asgn.attribute_id = PSB_WS_POS1.g_default_wklyhrs_id
1449 AND ( asgn.worksheet_id IS NULL
1450 OR
1451 asgn.worksheet_id = g_weekly_hours_worksheet_id
1452 )
1453 AND asgn.assignment_type = 'ATTRIBUTE'
1454 AND asgn.position_id = p_position_id
1455 and vals.attribute_value_id = asgn.attribute_value_id ;
1456 --
1457 /* Bug No 1920021 End */
1458
1459 l_fte NUMBER;
1460 l_fte_profile NUMBER;
1461 l_default_weekly_hours NUMBER;
1462 l_pay_element_id NUMBER;
1463 l_pay_element_option_id NUMBER;
1464 l_element_name VARCHAR2(30);
1465 l_pay_basis VARCHAR2(10);
1466 l_element_value_type VARCHAR2(2);
1467 l_element_value NUMBER;
1468 l_formula_id NUMBER;
1469
1470 l_budget_period_id NUMBER;
1471 l_budget_period_type VARCHAR2(1);
1472 l_calc_period_type VARCHAR2(1);
1473 l_calc_start_date DATE;
1474 l_calc_end_date DATE;
1475
1476 l_long_index NUMBER;
1477
1478 l_year_index BINARY_INTEGER;
1479 l_period_index BINARY_INTEGER;
1480 l_calcperiod_index BINARY_INTEGER;
1481 l_element_index BINARY_INTEGER;
1482 l_assign_index BINARY_INTEGER;
1483 l_rate_index BINARY_INTEGER;
1484 l_salary_index BINARY_INTEGER;
1485
1486 l_ws_assignment VARCHAR2(1);
1487 l_element_assigned VARCHAR2(1);
1488 l_calc_element_assigned VARCHAR2(1);
1489
1490 l_factor NUMBER;
1491 l_element_cost NUMBER;
1492 l_ytd_element_cost NUMBER;
1493
1494 l_last_period_index NUMBER;
1495
1496 l_salary_defined VARCHAR2(1) := FND_API.G_FALSE;
1497 l_salary_element_value NUMBER;
1498 l_max_element_value NUMBER;
1499
1500 -- Bug#3140849: To stores maximum element value per FTE.
1501 l_max_elem_value_per_fte NUMBER;
1502
1503 l_assign_period VARCHAR2(1);
1504 l_calculate_from_salary VARCHAR2(1);
1505 l_assign_period_index NUMBER;
1506
1507 l_processing_type VARCHAR2(1);
1508 l_nonrec_calculated VARCHAR2(1);
1509
1510 l_salary_indexes PSB_WS_ACCT1.g_prdamt_tbl_type;
1511 l_num_salary_indexes NUMBER;
1512
1513 l_return_status VARCHAR2(1);
1514 l_msg_data VARCHAR2(2000);
1515 l_msg_count NUMBER;
1516
1517 -- for bug 2622404
1518 l_nonelm_calc_flag VARCHAR2(1) := fnd_api.g_false;
1519
1520 /* Bug 5065066 Start */
1521 l_year_start_date DATE;
1522 l_period_ctr NUMBER;
1523 /* Bug 5065066 End */
1524
1525 l_api_name CONSTANT VARCHAR2(30) := 'Calculate_Position_Cost_Year';
1526
1527 BEGIN
1528
1529 for l_year_index in 1..PSB_WS_ACCT1.g_num_budget_years loop
1530
1531 if PSB_WS_ACCT1.g_budget_years(l_year_index).budget_year_id = p_budget_year_id then
1532 l_last_period_index := PSB_WS_ACCT1.g_budget_years(l_year_index).last_period_index;
1533 exit;
1534 end if;
1535
1536 end loop;
1537
1538 for l_salary_index in 1..PSB_WS_ACCT1.g_max_num_amounts loop
1539 l_salary_indexes(l_salary_index) := null;
1540 end loop;
1541
1542 l_num_salary_indexes := 0;
1543 l_salary_element_value := 0;
1544
1545 for l_element_index in 1..PSB_WS_POS1.g_num_elements loop
1546
1547 l_pay_element_id := PSB_WS_POS1.g_elements(l_element_index).pay_element_id;
1548 l_element_name := PSB_WS_POS1.g_elements(l_element_index).element_name;
1549 l_processing_type :=PSB_WS_POS1.g_elements(l_element_index).processing_type;
1550 l_element_assigned := FND_API.G_FALSE;
1551 l_nonrec_calculated := FND_API.G_FALSE;
1552
1553 for l_assign_index in 1..g_num_poselem_assignments loop
1554
1555 if ((g_poselem_assignments(l_assign_index).pay_element_id = l_pay_element_id) and
1556 (((g_poselem_assignments(l_assign_index).start_date <= p_year_end_date) and
1557 (g_poselem_assignments(l_assign_index).end_date is null)) or
1558 ((g_poselem_assignments(l_assign_index).start_date between p_year_start_date and p_year_end_date) or
1559 (g_poselem_assignments(l_assign_index).end_date between p_year_start_date and p_year_end_date) or
1560 ((g_poselem_assignments(l_assign_index).start_date < p_year_start_date) and
1561 (g_poselem_assignments(l_assign_index).end_date > p_year_end_date)))) and
1562 (g_poselem_assignments(l_assign_index).use_in_calc)) then
1563 l_element_assigned := FND_API.G_TRUE;
1564 exit;
1565 end if;
1566
1567 end loop;
1568
1569 if FND_API.to_Boolean(l_element_assigned) then
1570 begin
1571
1572 /* Bug 5065066 Start */
1573 FOR l_year_index in 1..PSB_WS_ACCT1.g_num_budget_years
1574 LOOP
1575 IF PSB_WS_ACCT1.g_budget_years(l_year_index).budget_year_id
1576 = p_budget_year_id THEN
1577 l_year_start_date
1578 := PSB_WS_ACCT1.g_budget_years(l_year_index).start_date;
1579 END IF;
1580 END LOOP;
1581 /* Bug 5065066 End */
1582
1583 if nvl(PSB_WS_POS1.g_elements(l_element_index).process_period_type, 'FIRST') = 'FIRST' then
1584 /* Bug 5065066 Start */
1585 -- l_assign_period_index := 1;
1586 IF l_year_start_date < g_pos_start_date THEN
1587 l_period_ctr := 0;
1588 FOR l_period_index IN 1..PSB_WS_ACCT1.g_num_budget_periods
1589 LOOP
1590 IF PSB_WS_ACCT1.g_budget_periods(l_period_index).budget_year_id =
1591 p_budget_year_id THEN
1592 l_period_ctr := l_period_ctr +1 ;
1593 END IF;
1594
1595 IF PSB_WS_ACCT1.g_budget_periods(l_period_index).budget_year_id =
1596 p_budget_year_id AND
1597 g_pos_start_date BETWEEN
1598 PSB_WS_ACCT1.g_budget_periods(l_period_index).start_date AND
1599 PSB_WS_ACCT1.g_budget_periods(l_period_index).end_date THEN
1600
1601 l_assign_period_index := l_period_ctr;
1602 END IF;
1603 END LOOP;
1604 ELSE
1605 l_assign_period_index := 1 ;
1606 END IF;
1607 /* Bug 5065066 End */
1608 else
1609 l_assign_period_index := l_last_period_index;
1610 end if;
1611
1612 PSB_WS_POS1.g_num_pc_costs := PSB_WS_POS1.g_num_pc_costs + 1;
1613
1614 PSB_WS_POS1.Initialize_Calc
1615 (p_init_index => PSB_WS_POS1.g_num_pc_costs);
1616
1617 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).pay_element_id := l_pay_element_id;
1618 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).budget_year_id := p_budget_year_id;
1619
1620 l_ytd_element_cost := 0;
1621
1622 l_calculate_from_salary := FND_API.G_FALSE;
1623 l_assign_period := FND_API.G_FALSE;
1624
1625 for l_period_index in 1..PSB_WS_ACCT1.g_num_budget_periods loop
1626
1627 if PSB_WS_ACCT1.g_budget_periods(l_period_index).budget_year_id = p_budget_year_id then
1628 begin
1629
1630 l_element_cost := 0;
1631
1632 l_long_index := PSB_WS_ACCT1.g_budget_periods(l_period_index).long_sequence_no;
1633 l_budget_period_id := PSB_WS_ACCT1.g_budget_periods(l_period_index).budget_period_id;
1634 l_budget_period_type := PSB_WS_ACCT1.g_budget_periods(l_period_index).budget_period_type;
1635
1636 for l_calcperiod_index in 1..PSB_WS_ACCT1.g_num_calc_periods loop
1637
1638 if ((PSB_WS_ACCT1.g_calculation_periods(l_calcperiod_index).budget_period_id = l_budget_period_id) and
1639 ((l_processing_type = 'R') or
1640 ((l_processing_type = 'N') and (not FND_API.to_Boolean(l_nonrec_calculated))))) then
1641 begin
1642
1643 l_ws_assignment := FND_API.G_FALSE;
1644 l_calc_element_assigned := FND_API.G_FALSE;
1645
1646 -- for bug 2622404
1647 l_nonelm_calc_flag := fnd_api.g_false;
1648
1649 l_calc_period_type := PSB_WS_ACCT1.g_calculation_periods(l_calcperiod_index).calc_period_type;
1650 l_calc_start_date := PSB_WS_ACCT1.g_calculation_periods(l_calcperiod_index).start_date;
1651 l_calc_end_date := PSB_WS_ACCT1.g_calculation_periods(l_calcperiod_index).end_date;
1652
1653 /*For Bug No : 2811698 Start*/
1654 --commented the following code as this is moved to other place
1655 /*if l_calc_period_type = 'M' and g_num_monthly_profile > 0 then
1656 l_fte_profile := nvl(g_monthly_profile(l_long_index), 0);
1657 elsif l_calc_period_type = 'Q' and g_num_quarterly_profile > 0 then
1658 l_fte_profile := nvl(g_quarterly_profile(l_long_index), 0);
1659 elsif l_calc_period_type = 'S' and g_num_semiannual_profile > 0 then
1660 l_fte_profile := nvl(g_semiannual_profile(l_long_index), 0);
1661 end if;*/
1662 /*For Bug No : 2811698 End*/
1663
1664 for l_assign_index in 1..g_num_poselem_assignments loop
1665
1666 if ((g_poselem_assignments(l_assign_index).pay_element_id = l_pay_element_id) and
1667 (g_poselem_assignments(l_assign_index).worksheet_id is not null) and
1668 (((g_poselem_assignments(l_assign_index).start_date <= l_calc_end_date) and
1669 (g_poselem_assignments(l_assign_index).end_date is null)) or
1670 ((g_poselem_assignments(l_assign_index).start_date between l_calc_start_date and l_calc_end_date) or
1671 (g_poselem_assignments(l_assign_index).end_date between l_calc_start_date and l_calc_end_date) or
1672 ((g_poselem_assignments(l_assign_index).start_date < l_calc_start_date) and
1673 (g_poselem_assignments(l_assign_index).end_date > l_calc_end_date))))) then
1674 begin
1675
1676 l_ws_assignment := FND_API.G_TRUE;
1677 l_calc_element_assigned := FND_API.G_TRUE;
1678
1679 l_pay_basis := g_poselem_assignments(l_assign_index).pay_basis;
1680 l_pay_element_option_id := g_poselem_assignments(l_assign_index).pay_element_option_id;
1681 l_element_value_type := g_poselem_assignments(l_assign_index).element_value_type;
1682 l_element_value := g_poselem_assignments(l_assign_index).element_value;
1683
1684 if l_processing_type = 'N' then
1685 l_nonrec_calculated := FND_API.G_TRUE;
1686 end if;
1687
1688 exit;
1689
1690 end;
1691 end if;
1692
1693 end loop;
1694
1695 if not FND_API.to_Boolean(l_ws_assignment) then
1696 begin
1697
1698 for l_assign_index in 1..g_num_poselem_assignments loop
1699
1700 if ((g_poselem_assignments(l_assign_index).pay_element_id = l_pay_element_id) and
1701 (g_poselem_assignments(l_assign_index).worksheet_id is null) and
1702 (((g_poselem_assignments(l_assign_index).start_date <= l_calc_end_date) and
1703 (g_poselem_assignments(l_assign_index).end_date is null)) or
1704 ((g_poselem_assignments(l_assign_index).start_date between l_calc_start_date and l_calc_end_date) or
1705 (g_poselem_assignments(l_assign_index).end_date between l_calc_start_date and l_calc_end_date) or
1706 ((g_poselem_assignments(l_assign_index).start_date < l_calc_start_date) and
1707 (g_poselem_assignments(l_assign_index).end_date > l_calc_end_date)))) and
1708 (g_poselem_assignments(l_assign_index).use_in_calc)) then
1709 begin
1710
1711 l_calc_element_assigned := FND_API.G_TRUE;
1712
1713 l_pay_basis := g_poselem_assignments(l_assign_index).pay_basis;
1714 l_pay_element_option_id := g_poselem_assignments(l_assign_index).pay_element_option_id;
1715 l_element_value_type := g_poselem_assignments(l_assign_index).element_value_type;
1716 l_element_value := g_poselem_assignments(l_assign_index).element_value;
1717
1718 if l_processing_type = 'N' then
1719 l_nonrec_calculated := FND_API.G_TRUE;
1720 end if;
1721
1722 exit;
1723
1724 end;
1725 end if;
1726
1727 end loop;
1728
1729 end;
1730 end if;
1731
1732 if FND_API.to_Boolean(l_calc_element_assigned) then
1733 begin
1734
1735 -- for bug 2622404
1736 l_nonelm_calc_flag := fnd_api.g_true;
1737
1738 if l_element_value is null then
1739 begin
1740
1741 for l_rate_index in 1..g_num_poselem_rates loop
1742
1743 if ((g_poselem_rates(l_rate_index).pay_element_id = l_pay_element_id) and
1744 (nvl(g_poselem_rates(l_rate_index).pay_element_option_id, FND_API.G_MISS_NUM) =
1745 nvl(l_pay_element_option_id, FND_API.G_MISS_NUM)) and
1746 (((g_poselem_rates(l_rate_index).start_date <= l_calc_end_date) and
1747 (g_poselem_rates(l_rate_index).end_date is null)) or
1748 ((g_poselem_rates(l_rate_index).start_date between l_calc_start_date and l_calc_end_date) or
1749 (g_poselem_rates(l_rate_index).end_date between l_calc_start_date and l_calc_end_date) or
1750 ((g_poselem_rates(l_rate_index).start_date < l_calc_start_date) and
1751 (g_poselem_rates(l_rate_index).end_date > l_calc_end_date))))) then
1752 begin
1753
1754 if l_pay_basis is null then
1755 l_pay_basis := g_poselem_rates(l_rate_index).pay_basis;
1756 end if;
1757
1758 l_element_value_type := g_poselem_rates(l_rate_index).element_value_type;
1759 l_element_value := g_poselem_rates(l_rate_index).element_value;
1760 l_formula_id := g_poselem_rates(l_rate_index).formula_id;
1761 exit;
1762
1763 end;
1764 end if;
1765
1766 end loop;
1767
1768 end;
1769 end if;
1770
1771 for l_assign_index in 1..g_num_posfte_assignments loop
1772
1773 if (((g_posfte_assignments(l_assign_index).start_date <= l_calc_end_date) and
1774 (g_posfte_assignments(l_assign_index).end_date is null)) or
1775 ((g_posfte_assignments(l_assign_index).start_date between l_calc_start_date and l_calc_end_date) or
1776 (g_posfte_assignments(l_assign_index).end_date between l_calc_start_date and l_calc_end_date) or
1777 ((g_posfte_assignments(l_assign_index).start_date < l_calc_start_date) and
1778 (g_posfte_assignments(l_assign_index).end_date > l_calc_end_date)))) then
1779 begin
1780 /*For Bug No : 2811698 Start*/
1781 --l_fte := g_posfte_assignments(l_assign_index).fte;
1782 if g_fte_profile_option = 'Y' then
1783 l_fte := nvl(g_posfte_assignments(l_assign_index).fte,g_default_fte);
1784 else
1785 l_fte := g_posfte_assignments(l_assign_index).fte;
1786 end if;
1787 /*For Bug No : 2811698 End*/
1788 exit;
1789
1790 end;
1791 end if;
1792
1793 end loop;
1794
1795 for l_assign_index in 1..g_num_poswkh_assignments loop
1796
1797 /* Bug No 2539186 Start */
1798 l_default_weekly_hours := NULL;
1799 /* Bug No 2539186 End */
1800
1801 if (((g_poswkh_assignments(l_assign_index).start_date <= l_calc_end_date) and
1802 (g_poswkh_assignments(l_assign_index).end_date is null)) or
1803 ((g_poswkh_assignments(l_assign_index).start_date between l_calc_start_date and l_calc_end_date) or
1804 (g_poswkh_assignments(l_assign_index).end_date between l_calc_start_date and l_calc_end_date) or
1805 ((g_poswkh_assignments(l_assign_index).start_date < l_calc_start_date) and
1806 (g_poswkh_assignments(l_assign_index).end_date > l_calc_end_date)))) then
1807 begin
1808
1809 l_default_weekly_hours := g_poswkh_assignments(l_assign_index).default_weekly_hours;
1810 exit;
1811
1812 end;
1813 end if;
1814
1815 end loop;
1816
1817 /* Bug No 1920021 Start */
1818 if l_default_weekly_hours is NULL and l_pay_basis = 'HOURLY'
1819 then
1820
1821 IF PSB_WS_POS1.g_default_wklyhrs_vt_flag = 'N' THEN
1822 --
1823 for l_default_weekly_hrs_rec in l_default_weekly_hrs_csr
1824 loop
1825 l_default_weekly_hours :=
1826 l_default_weekly_hrs_rec.attribute_value ;
1827 end loop;
1828 --
1829 ELSIF PSB_WS_POS1.g_default_wklyhrs_vt_flag = 'Y' THEN
1830 --
1831 for l_default_weekly_hrs_rec in l_default_weekly_hrs_vt_csr
1832 loop
1833 l_default_weekly_hours :=
1834 l_default_weekly_hrs_rec.attribute_value ;
1835 end loop;
1836 --
1837 END IF ;
1838
1839 end if;
1840 /* Bug No 1920021 End */
1841
1842 if l_element_value_type = 'PI' then
1843 message_token('ELEMENT_VALUE_TYPE', l_element_value_type);
1844 message_token('ELEMENT', PSB_WS_POS1.g_elements(l_element_index).element_name);
1845 message_token('POSITION', p_position_name);
1846 add_message('PSB', 'PSB_INVALID_ASSIGNMENT_TYPE');
1847 raise FND_API.G_EXC_ERROR;
1848 end if;
1849
1850 /*For Bug No : 2811698 Start*/
1851 --changed the l_fte_profile values when null from g_default_fte to 0
1852 --added the following IF condition to make sure that this
1853 --will be done when FTE profile option is set to 'Y'
1854 --l_fte_profile will hold the fte attribute value, if no FTE profile is
1855 --available in the allocation rule
1856 l_fte_profile := l_fte;
1857 if g_fte_profile_option = 'Y' then
1858 if l_calc_period_type = 'M' and g_num_monthly_profile > 0 then
1859 l_fte_profile := nvl(g_monthly_profile(l_long_index), 0);
1860 elsif l_calc_period_type = 'Q' and g_num_quarterly_profile > 0 then
1861 l_fte_profile := nvl(g_quarterly_profile(l_long_index), 0);
1862 elsif l_calc_period_type = 'S' and g_num_semiannual_profile > 0 then
1863 l_fte_profile := nvl(g_semiannual_profile(l_long_index), 0);
1864 end if;
1865 end if;
1866 /*For Bug No : 2811698 End*/
1867
1868 if PSB_WS_POS1.g_elements(l_element_index).salary_flag = 'Y' then
1869 begin
1870
1871 /* start bug 2622404 */
1872 IF l_element_value IS NULL THEN
1873 l_element_value := 0;
1874 END IF;
1875 /* end bug 2622404 */
1876
1877
1878 if l_processing_type = 'N' then
1879 l_element_cost := l_element_value;
1880 else
1881 begin
1882
1883 if l_pay_basis = 'ANNUAL' then
1884 begin
1885
1886 PSB_WS_POS1.HRMS_Factor
1887 (p_return_status => l_return_status,
1888 p_hrms_period_type => 'Y',
1889 p_budget_period_type => l_calc_period_type,
1890 p_position_name => p_position_name,
1891 p_element_name => l_element_name,
1892 p_start_date => l_calc_start_date,
1893 p_end_date => l_calc_end_date,
1894 p_factor => l_factor);
1895
1896 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1897 raise FND_API.G_EXC_ERROR;
1898 end if;
1899
1900 /*For Bug No : 2811698 Start*/
1901 if g_fte_profile_option = 'Y' then
1902 l_element_cost := l_element_cost +
1903 l_element_value * l_fte_profile * l_factor;
1904 else
1905 l_element_cost := l_element_cost +
1906 l_element_value * nvl(l_fte, g_default_fte) * l_factor;
1907 end if;
1908 /*For Bug No : 2811698 End*/
1909
1910 end;
1911 elsif l_pay_basis = 'HOURLY' then
1912 begin
1913
1914 PSB_WS_POS1.HRMS_Factor
1915 (p_return_status => l_return_status,
1916 p_hrms_period_type => 'W',
1917 p_budget_period_type => l_calc_period_type,
1918 p_position_name => p_position_name,
1919 p_element_name => l_element_name,
1920 p_start_date => l_calc_start_date,
1921 p_end_date => l_calc_end_date,
1922 p_factor => l_factor);
1923
1924 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1925 raise FND_API.G_EXC_ERROR;
1926 end if;
1927
1928 if l_default_weekly_hours is null then
1929 message_token('ATTRIBUTE', 'DEFAULT_WEEKLY_HOURS');
1930 message_token('POSITION', p_position_name);
1931 message_token('START_DATE', l_calc_start_date);
1932 message_token('END_DATE', l_calc_end_date);
1933 add_message('PSB', 'PSB_INVALID_NAMED_ATTRIBUTE');
1934 raise FND_API.G_EXC_ERROR;
1935 end if;
1936
1937 /*For Bug No : 2811698 Start*/
1938 if g_fte_profile_option = 'Y' then
1939 l_element_cost := l_element_cost +
1940 l_element_value * l_fte_profile * l_default_weekly_hours * l_factor;
1941 else
1942 l_element_cost := l_element_cost +
1943 l_element_value * nvl(l_fte, g_default_fte) * l_default_weekly_hours * l_factor;
1944 end if;
1945 /*For Bug No : 2811698 End*/
1946
1947 end;
1948 elsif l_pay_basis = 'MONTHLY' then
1949 begin
1950
1951 PSB_WS_POS1.HRMS_Factor
1952 (p_return_status => l_return_status,
1953 p_hrms_period_type => 'CM',
1954 p_budget_period_type => l_calc_period_type,
1955 p_position_name => p_position_name,
1956 p_element_name => l_element_name,
1957 p_start_date => l_calc_start_date,
1958 p_end_date => l_calc_end_date,
1959 p_factor => l_factor);
1960
1961 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1962 raise FND_API.G_EXC_ERROR;
1963 end if;
1964
1965 /*For Bug No : 2811698 Start*/
1966 if g_fte_profile_option = 'Y' then
1967 l_element_cost := l_element_cost +
1968 l_element_value * l_fte_profile * l_factor;
1969 else
1970 l_element_cost := l_element_cost +
1971 l_element_value * nvl(l_fte, g_default_fte) * l_factor;
1972 end if;
1973 /*For Bug No : 2811698 End*/
1974
1975 end;
1976 elsif l_pay_basis = 'PERIOD' then
1977 begin
1978
1979 PSB_WS_POS1.HRMS_Factor
1980 (p_return_status => l_return_status,
1981 p_hrms_period_type => PSB_WS_POS1.g_elements(l_element_index).period_type,
1982 p_budget_period_type => l_calc_period_type,
1983 p_position_name => p_position_name,
1984 p_element_name => l_element_name,
1985 p_start_date => l_calc_start_date,
1986 p_end_date => l_calc_end_date,
1987 p_factor => l_factor);
1988
1989 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1990 raise FND_API.G_EXC_ERROR;
1991 end if;
1992
1993 /*For Bug No : 2811698 Start*/
1994 if g_fte_profile_option = 'Y' then
1995 l_element_cost := l_element_cost +
1996 l_element_value * l_fte_profile * l_factor;
1997 else
1998 l_element_cost := l_element_cost +
1999 l_element_value * nvl(l_fte, g_default_fte) * l_factor;
2000 end if;
2001 /*For Bug No : 2811698 End*/
2002
2003 end;
2004 else
2005 message_token('POSITION', p_position_name);
2006 message_token('START_DATE', l_calc_start_date);
2007 message_token('END_DATE', l_calc_end_date);
2008 add_message('PSB', 'PSB_INVALID_SALARY_BASIS');
2009 raise FND_API.G_EXC_ERROR;
2010 end if;
2011
2012 end;
2013 end if;
2014
2015 end;
2016 else
2017 begin
2018
2019 /* start bug 2622404 */
2020 IF l_element_value IS NULL THEN
2021 l_element_value := 0;
2022 END IF;
2023 /* end bug 2622404 */
2024
2025 if l_element_value_type = 'PS' then
2026 begin
2027
2028 -- bug 3786457.commented out the following IF
2029 -- if l_element_value >= 1 then
2030 l_element_value := l_element_value / 100;
2031 -- end if;
2032
2033 l_calculate_from_salary := FND_API.G_TRUE;
2034 exit;
2035
2036 end;
2037 elsif l_element_value_type = 'A' then
2038 begin
2039
2040 if l_processing_type = 'N' then
2041 l_element_cost := l_element_value;
2042 else
2043 begin
2044
2045 PSB_WS_POS1.HRMS_Factor
2046 (p_return_status => l_return_status,
2047 p_hrms_period_type => PSB_WS_POS1.g_elements(l_element_index).period_type,
2048 p_budget_period_type => l_calc_period_type,
2049 p_position_name => p_position_name,
2050 p_element_name => l_element_name,
2051 p_start_date => l_calc_start_date,
2052 p_end_date => l_calc_end_date,
2053 p_factor => l_factor);
2054
2055 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2056 raise FND_API.G_EXC_ERROR;
2057 end if;
2058
2059 /*For Bug No : 2811698 Start*/
2060 if g_fte_profile_option = 'Y' then
2061 if l_factor < 1 then
2062 l_ytd_element_cost := l_ytd_element_cost +
2063 l_element_value * l_fte_profile * l_factor;
2064 l_assign_period := FND_API.G_TRUE;
2065 else
2066 l_element_cost := l_element_cost +
2067 l_element_value * l_fte_profile * l_factor;
2068 end if;
2069 else
2070 if l_factor < 1 then
2071 l_ytd_element_cost := l_ytd_element_cost +
2072 l_element_value * nvl(l_fte, g_default_fte) * l_factor;
2073 l_assign_period := FND_API.G_TRUE;
2074 else
2075 l_element_cost := l_element_cost +
2076 l_element_value * nvl(l_fte, g_default_fte) * l_factor;
2077 end if;
2078 end if;
2079 /*For Bug No : 2811698 End*/
2080
2081 end;
2082 end if;
2083
2084 end;
2085 end if;
2086
2087 end;
2088 end if;
2089
2090 end;
2091 end if;
2092
2093 end;
2094 end if;
2095
2096 end loop; /* Calculation Periods */
2097
2098 --
2099 -- Bug#3140849: Enforce maximum element value by FTE.
2100 --
2101 --pd('l_element_name:' || l_element_name);
2102 IF PSB_WS_POS1.g_elements(l_element_index).max_element_value_type
2103 = 'A'
2104 THEN
2105 l_max_elem_value_per_fte :=
2106 PSB_WS_POS1.g_elements(l_element_index).max_element_value;
2107 ELSE
2108
2109 -- bug 3786457. commented out the following IF
2110 /* IF PSB_WS_POS1.g_elements(l_element_index).max_element_value < 1
2111 THEN
2112 l_max_elem_value_per_fte := PSB_WS_POS1.g_elements(l_element_index).max_element_value * l_salary_element_value;
2113 ELSE
2114 l_max_elem_value_per_fte := PSB_WS_POS1.g_elements(l_element_index).max_element_value * l_salary_element_value / 100;
2115 END IF ; */
2116
2117 l_max_elem_value_per_fte := PSB_WS_POS1.g_elements(l_element_index).max_element_value * l_salary_element_value / 100;
2118
2119 --
2120 END IF ;
2121
2122 -- Set variable as it is used in all subsequence processing.
2123 l_max_element_value := l_max_elem_value_per_fte ;
2124
2125 IF PSB_WS_POS1.g_elements(l_element_index).max_element_value_type
2126 = 'A'
2127 THEN
2128 l_max_element_value := l_max_elem_value_per_fte * l_fte_profile ;
2129 END IF ;
2130 --pd('l_max_element_value:' || l_max_element_value);
2131 --
2132 -- Bug#3140849: End
2133 --
2134
2135 if ((FND_API.to_Boolean(l_calculate_from_salary)) and
2136 (FND_API.to_Boolean(l_salary_defined)) and
2137 (l_ytd_element_cost <= nvl(l_max_element_value, l_ytd_element_cost))) then
2138 begin
2139
2140 for l_salary_index in 1..l_num_salary_indexes loop
2141
2142 -- for bug 2622404
2143 -- added additional check of calculation flag. Repeated the same
2144 -- check for all the 60 periods defined.
2145
2146 if l_long_index = 1 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
2147 begin
2148
2149 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period1_amount * l_element_value;
2150
2151 if l_element_cost <> 0 then
2152 begin
2153
2154 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
2155
2156 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
2157 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
2158 end if;
2159
2160 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period1_amount := l_element_cost;
2161
2162 end;
2163 end if;
2164
2165 end;
2166 elsif l_long_index = 2 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
2167 begin
2168
2169 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period2_amount * l_element_value;
2170
2171 if l_element_cost <> 0 then
2172 begin
2173
2174 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
2175
2176 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
2177 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
2178 end if;
2179
2180 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period2_amount := l_element_cost;
2181
2182 end;
2183 end if;
2184
2185 end;
2186 elsif l_long_index = 3 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
2187 begin
2188
2189 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period3_amount * l_element_value;
2190
2191 if l_element_cost <> 0 then
2192 begin
2193
2194 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
2195
2196 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
2197 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
2198 end if;
2199
2200 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period3_amount := l_element_cost;
2201
2202 end;
2203 end if;
2204
2205 end;
2206 elsif l_long_index = 4 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
2207 begin
2208
2209 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period4_amount * l_element_value;
2210
2211 if l_element_cost <> 0 then
2212 begin
2213
2214 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
2215
2216 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
2217 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
2218 end if;
2219
2220 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period4_amount := l_element_cost;
2221
2222 end;
2223 end if;
2224
2225 end;
2226 elsif l_long_index = 5 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
2227 begin
2228
2229 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period5_amount * l_element_value;
2230
2231 if l_element_cost <> 0 then
2232 begin
2233
2234 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
2235
2236 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
2237 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
2238 end if;
2239
2240 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period5_amount := l_element_cost;
2241
2242 end;
2243 end if;
2244
2245 end;
2246 elsif l_long_index = 6 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
2247 begin
2248
2249 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period6_amount * l_element_value;
2250
2251 if l_element_cost <> 0 then
2252 begin
2253
2254 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
2255
2256 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
2257 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
2258 end if;
2259
2260 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period6_amount := l_element_cost;
2261
2262 end;
2263 end if;
2264
2265 end;
2266 elsif l_long_index = 7 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
2267 begin
2268
2269 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period7_amount * l_element_value;
2270
2271 if l_element_cost <> 0 then
2272 begin
2273
2274 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
2275
2276 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
2277 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
2278 end if;
2279
2280 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period7_amount := l_element_cost;
2281
2282 end;
2283 end if;
2284
2285 end;
2286 elsif l_long_index = 8 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
2287 begin
2288
2289 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period8_amount * l_element_value;
2290
2291 if l_element_cost <> 0 then
2292 begin
2293
2294 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
2295
2296 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
2297 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
2298 end if;
2299
2300 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period8_amount := l_element_cost;
2301
2302 end;
2303 end if;
2304
2305 end;
2306 elsif l_long_index = 9 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
2307 begin
2308
2309 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period9_amount * l_element_value;
2310
2311 if l_element_cost <> 0 then
2312 begin
2313
2314 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
2315
2316 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
2317 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
2318 end if;
2319
2320 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period9_amount := l_element_cost;
2321
2322 end;
2323 end if;
2324
2325 end;
2326 elsif l_long_index = 10 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
2327 begin
2328
2329 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period10_amount * l_element_value;
2330
2331 if l_element_cost <> 0 then
2332 begin
2333
2334 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
2335
2336 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
2337 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
2338 end if;
2339
2340 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period10_amount := l_element_cost;
2341
2342 end;
2343 end if;
2344
2345 end;
2346 elsif l_long_index = 11 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
2347 begin
2348
2349 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period11_amount * l_element_value;
2350
2351 if l_element_cost <> 0 then
2352 begin
2353
2354 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
2355
2356 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
2357 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
2358 end if;
2359
2360 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period11_amount := l_element_cost;
2361
2362 end;
2363 end if;
2364
2365 end;
2366 elsif l_long_index = 12 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
2367 begin
2368
2369 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period12_amount * l_element_value;
2370
2371 if l_element_cost <> 0 then
2372 begin
2373
2374 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
2375
2376 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
2377 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
2378 end if;
2379
2380 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period12_amount := l_element_cost;
2381
2382 end;
2383 end if;
2384
2385 end;
2386 elsif l_long_index = 13 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
2387 begin
2388
2389 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period13_amount * l_element_value;
2390
2391 if l_element_cost <> 0 then
2392 begin
2393
2394 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
2395
2396 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
2397 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
2398 end if;
2399
2400 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period13_amount := l_element_cost;
2401
2402 end;
2403 end if;
2404
2405 end;
2406 elsif l_long_index = 14 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
2407 begin
2408
2409 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period14_amount * l_element_value;
2410
2411 if l_element_cost <> 0 then
2412 begin
2413
2414 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
2415
2416 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
2417 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
2418 end if;
2419
2420 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period14_amount := l_element_cost;
2421
2422 end;
2423 end if;
2424
2425 end;
2426 elsif l_long_index = 15 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
2427 begin
2428
2429 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period15_amount * l_element_value;
2430
2431 if l_element_cost <> 0 then
2432 begin
2433
2434 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
2435
2436 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
2437 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
2438 end if;
2439
2440 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period15_amount := l_element_cost;
2441
2442 end;
2443 end if;
2444
2445 end;
2446 elsif l_long_index = 16 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
2447 begin
2448
2449 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period16_amount * l_element_value;
2450
2451 if l_element_cost <> 0 then
2452 begin
2453
2454 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
2455
2456 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
2457 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
2458 end if;
2459
2460 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period16_amount := l_element_cost;
2461
2462 end;
2463 end if;
2464
2465 end;
2466 elsif l_long_index = 17 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
2467 begin
2468
2469 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period17_amount * l_element_value;
2470
2471 if l_element_cost <> 0 then
2472 begin
2473
2474 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
2475
2476 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
2477 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
2478 end if;
2479
2480 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period17_amount := l_element_cost;
2481
2482 end;
2483 end if;
2484
2485 end;
2486 elsif l_long_index = 18 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
2487 begin
2488
2489 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period18_amount * l_element_value;
2490
2491 if l_element_cost <> 0 then
2492 begin
2493
2494 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
2495
2496 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
2497 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
2498 end if;
2499
2500 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period18_amount := l_element_cost;
2501
2502 end;
2503 end if;
2504
2505 end;
2506 elsif l_long_index = 19 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
2507 begin
2508
2509 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period19_amount * l_element_value;
2510
2511 if l_element_cost <> 0 then
2512 begin
2513
2514 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
2515
2516 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
2517 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
2518 end if;
2519
2520 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period19_amount := l_element_cost;
2521
2522 end;
2523 end if;
2524
2525 end;
2526 elsif l_long_index = 20 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
2527 begin
2528
2529 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period20_amount * l_element_value;
2530
2531 if l_element_cost <> 0 then
2532 begin
2533
2534 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
2535
2536 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
2537 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
2538 end if;
2539
2540 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period20_amount := l_element_cost;
2541
2542 end;
2543 end if;
2544
2545 end;
2546 elsif l_long_index = 21 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
2547 begin
2548
2549 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period21_amount * l_element_value;
2550
2551 if l_element_cost <> 0 then
2552 begin
2553
2554 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
2555
2556 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
2557 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
2558 end if;
2559
2560 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period21_amount := l_element_cost;
2561
2562 end;
2563 end if;
2564
2565 end;
2566 elsif l_long_index = 22 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
2567 begin
2568
2569 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period22_amount * l_element_value;
2570
2571 if l_element_cost <> 0 then
2572 begin
2573
2574 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
2575
2576 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
2577 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
2578 end if;
2579
2580 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period22_amount := l_element_cost;
2581
2582 end;
2583 end if;
2584
2585 end;
2586 elsif l_long_index = 23 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
2587 begin
2588
2589 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period23_amount * l_element_value;
2590
2591 if l_element_cost <> 0 then
2592 begin
2593
2594 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
2595
2596 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
2597 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
2598 end if;
2599
2600 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period23_amount := l_element_cost;
2601
2602 end;
2603 end if;
2604
2605 end;
2606 elsif l_long_index = 24 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
2607 begin
2608
2609 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period24_amount * l_element_value;
2610
2611 if l_element_cost <> 0 then
2612 begin
2613
2614 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
2615
2616 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
2617 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
2618 end if;
2619
2620 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period24_amount := l_element_cost;
2621
2622 end;
2623 end if;
2624
2625 end;
2626 elsif l_long_index = 25 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
2627 begin
2628
2629 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period25_amount * l_element_value;
2630
2631 if l_element_cost <> 0 then
2632 begin
2633
2634 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
2635
2636 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
2637 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
2638 end if;
2639
2640 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period25_amount := l_element_cost;
2641
2642 end;
2643 end if;
2644
2645 end;
2646 elsif l_long_index = 26 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
2647 begin
2648
2649 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period26_amount * l_element_value;
2650
2651 if l_element_cost <> 0 then
2652 begin
2653
2654 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
2655
2656 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
2657 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
2658 end if;
2659
2660 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period26_amount := l_element_cost;
2661
2662 end;
2663 end if;
2664
2665 end;
2666 elsif l_long_index = 27 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
2667 begin
2668
2669 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period27_amount * l_element_value;
2670
2671 if l_element_cost <> 0 then
2672 begin
2673
2674 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
2675
2676 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
2677 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
2678 end if;
2679
2680 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period27_amount := l_element_cost;
2681
2682 end;
2683 end if;
2684
2685 end;
2686 elsif l_long_index = 28 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
2687 begin
2688
2689 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period28_amount * l_element_value;
2690
2691 if l_element_cost <> 0 then
2692 begin
2693
2694 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
2695
2696 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
2697 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
2698 end if;
2699
2700 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period28_amount := l_element_cost;
2701
2702 end;
2703 end if;
2704
2705 end;
2706 elsif l_long_index = 29 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
2707 begin
2708
2709 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period29_amount * l_element_value;
2710
2711 if l_element_cost <> 0 then
2712 begin
2713
2714 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
2715
2716 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
2717 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
2718 end if;
2719
2720 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period29_amount := l_element_cost;
2721
2722 end;
2723 end if;
2724
2725 end;
2726 elsif l_long_index = 30 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
2727 begin
2728
2729 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period30_amount * l_element_value;
2730
2731 if l_element_cost <> 0 then
2732 begin
2733
2734 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
2735
2736 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
2737 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
2738 end if;
2739
2740 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period30_amount := l_element_cost;
2741
2742 end;
2743 end if;
2744
2745 end;
2746 elsif l_long_index = 31 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
2747 begin
2748
2749 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period31_amount * l_element_value;
2750
2751 if l_element_cost <> 0 then
2752 begin
2753
2754 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
2755
2756 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
2757 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
2758 end if;
2759
2760 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period31_amount := l_element_cost;
2761
2762 end;
2763 end if;
2764
2765 end;
2766 elsif l_long_index = 32 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
2767 begin
2768
2769 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period32_amount * l_element_value;
2770
2771 if l_element_cost <> 0 then
2772 begin
2773
2774 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
2775
2776 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
2777 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
2778 end if;
2779
2780 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period32_amount := l_element_cost;
2781
2782 end;
2783 end if;
2784
2785 end;
2786 elsif l_long_index = 33 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
2787 begin
2788
2789 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period33_amount * l_element_value;
2790
2791 if l_element_cost <> 0 then
2792 begin
2793
2794 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
2795
2796 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
2797 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
2798 end if;
2799
2800 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period33_amount := l_element_cost;
2801
2802 end;
2803 end if;
2804
2805 end;
2806 elsif l_long_index = 34 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
2807 begin
2808
2809 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period34_amount * l_element_value;
2810
2811 if l_element_cost <> 0 then
2812 begin
2813
2814 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
2815
2816 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
2817 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
2818 end if;
2819
2820 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period34_amount := l_element_cost;
2821
2822 end;
2823 end if;
2824
2825 end;
2826 elsif l_long_index = 35 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
2827 begin
2828
2829 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period35_amount * l_element_value;
2830
2831 if l_element_cost <> 0 then
2832 begin
2833
2834 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
2835
2836 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
2837 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
2838 end if;
2839
2840 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period35_amount := l_element_cost;
2841
2842 end;
2843 end if;
2844
2845 end;
2846 elsif l_long_index = 36 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
2847 begin
2848
2849 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period36_amount * l_element_value;
2850
2851 if l_element_cost <> 0 then
2852 begin
2853
2854 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
2855
2856 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
2857 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
2858 end if;
2859
2860 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period36_amount := l_element_cost;
2861
2862 end;
2863 end if;
2864
2865 end;
2866 elsif l_long_index = 37 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
2867 begin
2868
2869 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period37_amount * l_element_value;
2870
2871 if l_element_cost <> 0 then
2872 begin
2873
2874 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
2875
2876 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
2877 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
2878 end if;
2879
2880 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period37_amount := l_element_cost;
2881
2882 end;
2883 end if;
2884
2885 end;
2886 elsif l_long_index = 38 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
2887 begin
2888
2889 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period38_amount * l_element_value;
2890
2891 if l_element_cost <> 0 then
2892 begin
2893
2894 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
2895
2896 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
2897 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
2898 end if;
2899
2900 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period38_amount := l_element_cost;
2901
2902 end;
2903 end if;
2904
2905 end;
2906 elsif l_long_index = 39 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
2907 begin
2908
2909 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period39_amount * l_element_value;
2910
2911 if l_element_cost <> 0 then
2912 begin
2913
2914 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
2915
2916 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
2917 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
2918 end if;
2919
2920 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period39_amount := l_element_cost;
2921
2922 end;
2923 end if;
2924
2925 end;
2926 elsif l_long_index = 40 and (fnd_api.to_boolean(l_nonelm_calc_flag)) and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
2927 begin
2928
2929 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period40_amount * l_element_value;
2930
2931 if l_element_cost <> 0 then
2932 begin
2933
2934 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
2935
2936 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
2937 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
2938 end if;
2939
2940 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period40_amount := l_element_cost;
2941
2942 end;
2943 end if;
2944
2945 end;
2946 elsif l_long_index = 41 then
2947 begin
2948
2949 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period41_amount * l_element_value;
2950
2951 if l_element_cost <> 0 then
2952 begin
2953
2954 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
2955
2956 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
2957 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
2958 end if;
2959
2960 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period41_amount := l_element_cost;
2961
2962 end;
2963 end if;
2964
2965 end;
2966 elsif l_long_index = 42 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
2967 begin
2968
2969 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period42_amount * l_element_value;
2970
2971 if l_element_cost <> 0 then
2972 begin
2973
2974 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
2975
2976 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
2977 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
2978 end if;
2979
2980 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period42_amount := l_element_cost;
2981
2982 end;
2983 end if;
2984
2985 end;
2986 elsif l_long_index = 43 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
2987 begin
2988
2989 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period43_amount * l_element_value;
2990
2991 if l_element_cost <> 0 then
2992 begin
2993
2994 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
2995
2996 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
2997 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
2998 end if;
2999
3000 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period43_amount := l_element_cost;
3001
3002 end;
3003 end if;
3004
3005 end;
3006 elsif l_long_index = 44 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
3007 begin
3008
3009 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period44_amount * l_element_value;
3010
3011 if l_element_cost <> 0 then
3012 begin
3013
3014 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
3015
3016 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
3017 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
3018 end if;
3019
3020 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period44_amount := l_element_cost;
3021
3022 end;
3023 end if;
3024
3025 end;
3026 elsif l_long_index = 45 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
3027 begin
3028
3029 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period45_amount * l_element_value;
3030
3031 if l_element_cost <> 0 then
3032 begin
3033
3034 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
3035
3036 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
3037 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
3038 end if;
3039
3040 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period45_amount := l_element_cost;
3041
3042 end;
3043 end if;
3044
3045 end;
3046 elsif l_long_index = 46 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
3047 begin
3048
3049 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period46_amount * l_element_value;
3050
3051 if l_element_cost <> 0 then
3052 begin
3053
3054 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
3055
3056 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
3057 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
3058 end if;
3059
3060 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period46_amount := l_element_cost;
3061
3062 end;
3063 end if;
3064
3065 end;
3066 elsif l_long_index = 47 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
3067 begin
3068
3069 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period47_amount * l_element_value;
3070
3071 if l_element_cost <> 0 then
3072 begin
3073
3074 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
3075
3076 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
3077 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
3078 end if;
3079
3080 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period47_amount := l_element_cost;
3081
3082 end;
3083 end if;
3084
3085 end;
3086 elsif l_long_index = 48 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
3087 begin
3088
3089 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period48_amount * l_element_value;
3090
3091 if l_element_cost <> 0 then
3092 begin
3093
3094 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
3095
3096 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
3097 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
3098 end if;
3099
3100 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period48_amount := l_element_cost;
3101
3102 end;
3103 end if;
3104
3105 end;
3106 elsif l_long_index = 49 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
3107 begin
3108
3109 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period49_amount * l_element_value;
3110
3111 if l_element_cost <> 0 then
3112 begin
3113
3114 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
3115
3116 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
3117 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
3118 end if;
3119
3120 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period49_amount := l_element_cost;
3121
3122 end;
3123 end if;
3124
3125 end;
3126 elsif l_long_index = 50 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
3127 begin
3128
3129 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period50_amount * l_element_value;
3130
3131 if l_element_cost <> 0 then
3132 begin
3133
3134 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
3135
3136 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
3137 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
3138 end if;
3139
3140 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period50_amount := l_element_cost;
3141
3142 end;
3143 end if;
3144
3145 end;
3146 elsif l_long_index = 51 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
3147 begin
3148
3149 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period51_amount * l_element_value;
3150
3151 if l_element_cost <> 0 then
3152 begin
3153
3154 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
3155
3156 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
3157 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
3158 end if;
3159
3160 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period51_amount := l_element_cost;
3161
3162 end;
3163 end if;
3164
3165 end;
3166 elsif l_long_index = 52 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
3167 begin
3168
3169 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period52_amount * l_element_value;
3170
3171 if l_element_cost <> 0 then
3172 begin
3173
3174 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
3175
3176 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
3177 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
3178 end if;
3179
3180 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period52_amount := l_element_cost;
3181
3182 end;
3183 end if;
3184
3185 end;
3186 elsif l_long_index = 53 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
3187 begin
3188
3189 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period53_amount * l_element_value;
3190
3191 if l_element_cost <> 0 then
3192 begin
3193
3194 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
3195
3196 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
3197 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
3198 end if;
3199
3200 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period53_amount := l_element_cost;
3201
3202 end;
3203 end if;
3204
3205 end;
3206 elsif l_long_index = 54 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
3207 begin
3208
3209 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period54_amount * l_element_value;
3210
3211 if l_element_cost <> 0 then
3212 begin
3213
3214 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
3215
3216 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
3217 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
3218 end if;
3219
3220 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period54_amount := l_element_cost;
3221
3222 end;
3223 end if;
3224
3225 end;
3226 elsif l_long_index = 55 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
3227 begin
3228
3229 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period55_amount * l_element_value;
3230
3231 if l_element_cost <> 0 then
3232 begin
3233
3234 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
3235
3236 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
3237 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
3238 end if;
3239
3240 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period55_amount := l_element_cost;
3241
3242 end;
3243 end if;
3244
3245 end;
3246 elsif l_long_index = 56 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
3247 begin
3248
3249 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period56_amount * l_element_value;
3250
3251 if l_element_cost <> 0 then
3252 begin
3253
3254 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
3255
3256 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
3257 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
3258 end if;
3259
3260 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period56_amount := l_element_cost;
3261
3262 end;
3263 end if;
3264
3265 end;
3266 elsif l_long_index = 57 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
3267 begin
3268
3269 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period57_amount * l_element_value;
3270
3271 if l_element_cost <> 0 then
3272 begin
3273
3274 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
3275
3276 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
3277 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
3278 end if;
3279
3280 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period57_amount := l_element_cost;
3281
3282 end;
3283 end if;
3284
3285 end;
3286 elsif l_long_index = 58 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
3287 begin
3288
3289 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period58_amount * l_element_value;
3290
3291 if l_element_cost <> 0 then
3292 begin
3293
3294 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
3295
3296 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
3297 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
3298 end if;
3299
3300 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period58_amount := l_element_cost;
3301
3302 end;
3303 end if;
3304
3305 end;
3306 elsif l_long_index = 59 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
3307 begin
3308
3309 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period59_amount * l_element_value;
3310
3311 if l_element_cost <> 0 then
3312 begin
3313
3314 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
3315
3316 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
3317 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
3318 end if;
3319
3320 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period59_amount := l_element_cost;
3321
3322 end;
3323 end if;
3324
3325 end;
3326 elsif l_long_index = 60 and (fnd_api.to_boolean(l_nonelm_calc_flag)) then
3327 begin
3328
3329 l_element_cost := PSB_WS_POS1.g_pc_costs(l_salary_indexes(l_salary_index)).period60_amount * l_element_value;
3330
3331 if l_element_cost <> 0 then
3332 begin
3333
3334 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
3335
3336 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
3337 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
3338 end if;
3339
3340 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period60_amount := l_element_cost;
3341
3342 end;
3343 end if;
3344
3345 end;
3346 end if;
3347
3348 end loop;
3349
3350 end;
3351 else
3352 begin
3353
3354 if not FND_API.to_Boolean(l_assign_period) then
3355 begin
3356
3357 l_ytd_element_cost := l_ytd_element_cost + l_element_cost;
3358
3359 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
3360 l_element_cost := greatest((l_max_element_value - (l_ytd_element_cost - l_element_cost)), 0);
3361 end if;
3362
3363 if l_long_index = 1 then
3364 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period1_amount := l_element_cost;
3365 elsif l_long_index = 2 then
3366 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period2_amount := l_element_cost;
3367 elsif l_long_index = 3 then
3368 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period3_amount := l_element_cost;
3369 elsif l_long_index = 4 then
3370 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period4_amount := l_element_cost;
3371 elsif l_long_index = 5 then
3372 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period5_amount := l_element_cost;
3373 elsif l_long_index = 6 then
3374 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period6_amount := l_element_cost;
3375 elsif l_long_index = 7 then
3376 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period7_amount := l_element_cost;
3377 elsif l_long_index = 8 then
3378 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period8_amount := l_element_cost;
3379 elsif l_long_index = 9 then
3380 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period9_amount := l_element_cost;
3381 elsif l_long_index = 10 then
3382 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period10_amount := l_element_cost;
3383 elsif l_long_index = 11 then
3384 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period11_amount := l_element_cost;
3385 elsif l_long_index = 12 then
3386 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period12_amount := l_element_cost;
3387 elsif l_long_index = 13 then
3388 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period13_amount := l_element_cost;
3389 elsif l_long_index = 14 then
3390 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period14_amount := l_element_cost;
3391 elsif l_long_index = 15 then
3392 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period15_amount := l_element_cost;
3393 elsif l_long_index = 16 then
3394 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period16_amount := l_element_cost;
3395 elsif l_long_index = 17 then
3396 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period17_amount := l_element_cost;
3397 elsif l_long_index = 18 then
3398 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period18_amount := l_element_cost;
3399 elsif l_long_index = 19 then
3400 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period19_amount := l_element_cost;
3401 elsif l_long_index = 20 then
3402 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period20_amount := l_element_cost;
3403 elsif l_long_index = 21 then
3404 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period21_amount := l_element_cost;
3405 elsif l_long_index = 22 then
3406 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period22_amount := l_element_cost;
3407 elsif l_long_index = 23 then
3408 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period23_amount := l_element_cost;
3409 elsif l_long_index = 24 then
3410 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period24_amount := l_element_cost;
3411 elsif l_long_index = 25 then
3412 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period25_amount := l_element_cost;
3413 elsif l_long_index = 26 then
3414 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period26_amount := l_element_cost;
3415 elsif l_long_index = 27 then
3416 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period27_amount := l_element_cost;
3417 elsif l_long_index = 28 then
3418 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period28_amount := l_element_cost;
3419 elsif l_long_index = 29 then
3420 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period29_amount := l_element_cost;
3421 elsif l_long_index = 30 then
3422 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period30_amount := l_element_cost;
3423 elsif l_long_index = 31 then
3424 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period31_amount := l_element_cost;
3425 elsif l_long_index = 32 then
3426 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period32_amount := l_element_cost;
3427 elsif l_long_index = 33 then
3428 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period33_amount := l_element_cost;
3429 elsif l_long_index = 34 then
3430 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period34_amount := l_element_cost;
3431 elsif l_long_index = 35 then
3432 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period35_amount := l_element_cost;
3433 elsif l_long_index = 36 then
3434 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period36_amount := l_element_cost;
3435 elsif l_long_index = 37 then
3436 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period37_amount := l_element_cost;
3437 elsif l_long_index = 38 then
3438 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period38_amount := l_element_cost;
3439 elsif l_long_index = 39 then
3440 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period39_amount := l_element_cost;
3441 elsif l_long_index = 40 then
3442 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period40_amount := l_element_cost;
3443 elsif l_long_index = 41 then
3444 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period41_amount := l_element_cost;
3445 elsif l_long_index = 42 then
3446 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period42_amount := l_element_cost;
3447 elsif l_long_index = 43 then
3448 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period43_amount := l_element_cost;
3449 elsif l_long_index = 44 then
3450 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period44_amount := l_element_cost;
3451 elsif l_long_index = 45 then
3452 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period45_amount := l_element_cost;
3453 elsif l_long_index = 46 then
3454 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period46_amount := l_element_cost;
3455 elsif l_long_index = 47 then
3456 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period47_amount := l_element_cost;
3457 elsif l_long_index = 48 then
3458 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period48_amount := l_element_cost;
3459 elsif l_long_index = 49 then
3460 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period49_amount := l_element_cost;
3461 elsif l_long_index = 50 then
3462 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period50_amount := l_element_cost;
3463 elsif l_long_index = 51 then
3464 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period51_amount := l_element_cost;
3465 elsif l_long_index = 52 then
3466 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period52_amount := l_element_cost;
3467 elsif l_long_index = 53 then
3468 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period53_amount := l_element_cost;
3469 elsif l_long_index = 54 then
3470 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period54_amount := l_element_cost;
3471 elsif l_long_index = 55 then
3472 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period55_amount := l_element_cost;
3473 elsif l_long_index = 56 then
3474 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period56_amount := l_element_cost;
3475 elsif l_long_index = 57 then
3476 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period57_amount := l_element_cost;
3477 elsif l_long_index = 58 then
3478 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period58_amount := l_element_cost;
3479 elsif l_long_index = 59 then
3480 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period59_amount := l_element_cost;
3481 elsif l_long_index = 60 then
3482 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period60_amount := l_element_cost;
3483 end if;
3484
3485 end;
3486 end if;
3487
3488 end;
3489 end if;
3490
3491 end;
3492 end if;
3493
3494 end loop; /* Budget Periods */
3495
3496 if FND_API.to_Boolean(l_assign_period) then
3497 begin
3498
3499 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
3500 l_ytd_element_cost := l_max_element_value;
3501 end if;
3502
3503 if l_assign_period_index = 1 then
3504 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period1_amount := l_ytd_element_cost;
3505 elsif l_assign_period_index = 2 then
3506 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period2_amount := l_ytd_element_cost;
3507 elsif l_assign_period_index = 3 then
3508 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period3_amount := l_ytd_element_cost;
3509 elsif l_assign_period_index = 4 then
3510 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period4_amount := l_ytd_element_cost;
3511 elsif l_assign_period_index = 5 then
3512 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period5_amount := l_ytd_element_cost;
3513 elsif l_assign_period_index = 6 then
3514 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period6_amount := l_ytd_element_cost;
3515 elsif l_assign_period_index = 7 then
3516 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period7_amount := l_ytd_element_cost;
3517 elsif l_assign_period_index = 8 then
3518 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period8_amount := l_ytd_element_cost;
3519 elsif l_assign_period_index = 9 then
3520 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period9_amount := l_ytd_element_cost;
3521 elsif l_assign_period_index = 10 then
3522 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period10_amount := l_ytd_element_cost;
3523 elsif l_assign_period_index = 11 then
3524 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period11_amount := l_ytd_element_cost;
3525 elsif l_assign_period_index = 12 then
3526 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period12_amount := l_ytd_element_cost;
3527 elsif l_assign_period_index = 13 then
3528 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period13_amount := l_ytd_element_cost;
3529 elsif l_assign_period_index = 14 then
3530 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period14_amount := l_ytd_element_cost;
3531 elsif l_assign_period_index = 15 then
3532 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period15_amount := l_ytd_element_cost;
3533 elsif l_assign_period_index = 16 then
3534 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period16_amount := l_ytd_element_cost;
3535 elsif l_assign_period_index = 17 then
3536 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period17_amount := l_ytd_element_cost;
3537 elsif l_assign_period_index = 18 then
3538 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period18_amount := l_ytd_element_cost;
3539 elsif l_assign_period_index = 19 then
3540 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period19_amount := l_ytd_element_cost;
3541 elsif l_assign_period_index = 20 then
3542 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period20_amount := l_ytd_element_cost;
3543 elsif l_assign_period_index = 21 then
3544 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period21_amount := l_ytd_element_cost;
3545 elsif l_assign_period_index = 22 then
3546 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period22_amount := l_ytd_element_cost;
3547 elsif l_assign_period_index = 23 then
3548 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period23_amount := l_ytd_element_cost;
3549 elsif l_assign_period_index = 24 then
3550 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period24_amount := l_ytd_element_cost;
3551 elsif l_assign_period_index = 25 then
3552 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period25_amount := l_ytd_element_cost;
3553 elsif l_assign_period_index = 26 then
3554 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period26_amount := l_ytd_element_cost;
3555 elsif l_assign_period_index = 27 then
3556 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period27_amount := l_ytd_element_cost;
3557 elsif l_assign_period_index = 28 then
3558 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period28_amount := l_ytd_element_cost;
3559 elsif l_assign_period_index = 29 then
3560 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period29_amount := l_ytd_element_cost;
3561 elsif l_assign_period_index = 30 then
3562 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period30_amount := l_ytd_element_cost;
3563 elsif l_assign_period_index = 31 then
3564 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period31_amount := l_ytd_element_cost;
3565 elsif l_assign_period_index = 32 then
3566 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period32_amount := l_ytd_element_cost;
3567 elsif l_assign_period_index = 33 then
3568 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period33_amount := l_ytd_element_cost;
3569 elsif l_assign_period_index = 34 then
3570 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period34_amount := l_ytd_element_cost;
3571 elsif l_assign_period_index = 35 then
3572 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period35_amount := l_ytd_element_cost;
3573 elsif l_assign_period_index = 36 then
3574 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period36_amount := l_ytd_element_cost;
3575 elsif l_assign_period_index = 37 then
3576 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period37_amount := l_ytd_element_cost;
3577 elsif l_assign_period_index = 38 then
3578 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period38_amount := l_ytd_element_cost;
3579 elsif l_assign_period_index = 39 then
3580 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period39_amount := l_ytd_element_cost;
3581 elsif l_assign_period_index = 40 then
3582 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period40_amount := l_ytd_element_cost;
3583 elsif l_assign_period_index = 41 then
3584 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period41_amount := l_ytd_element_cost;
3585 elsif l_assign_period_index = 42 then
3586 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period42_amount := l_ytd_element_cost;
3587 elsif l_assign_period_index = 43 then
3588 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period43_amount := l_ytd_element_cost;
3589 elsif l_assign_period_index = 44 then
3590 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period44_amount := l_ytd_element_cost;
3591 elsif l_assign_period_index = 45 then
3592 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period45_amount := l_ytd_element_cost;
3593 elsif l_assign_period_index = 46 then
3594 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period46_amount := l_ytd_element_cost;
3595 elsif l_assign_period_index = 47 then
3596 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period47_amount := l_ytd_element_cost;
3597 elsif l_assign_period_index = 48 then
3598 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period48_amount := l_ytd_element_cost;
3599 elsif l_assign_period_index = 49 then
3600 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period49_amount := l_ytd_element_cost;
3601 elsif l_assign_period_index = 50 then
3602 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period50_amount := l_ytd_element_cost;
3603 elsif l_assign_period_index = 51 then
3604 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period51_amount := l_ytd_element_cost;
3605 elsif l_assign_period_index = 52 then
3606 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period52_amount := l_ytd_element_cost;
3607 elsif l_assign_period_index = 53 then
3608 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period53_amount := l_ytd_element_cost;
3609 elsif l_assign_period_index = 54 then
3610 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period54_amount := l_ytd_element_cost;
3611 elsif l_assign_period_index = 55 then
3612 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period55_amount := l_ytd_element_cost;
3613 elsif l_assign_period_index = 56 then
3614 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period56_amount := l_ytd_element_cost;
3615 elsif l_assign_period_index = 57 then
3616 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period57_amount := l_ytd_element_cost;
3617 elsif l_assign_period_index = 58 then
3618 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period58_amount := l_ytd_element_cost;
3619 elsif l_assign_period_index = 59 then
3620 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period59_amount := l_ytd_element_cost;
3621 elsif l_assign_period_index = 60 then
3622 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).period60_amount := l_ytd_element_cost;
3623 end if;
3624
3625 end;
3626 end if;
3627
3628 /* For Bug No : 2115867 Start */
3629 --The following one line has been commented because of the maximum value
3630 -- has to be taken when YTD_Amount is more than it
3631 -- PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).element_cost := l_ytd_element_cost;
3632
3633 if l_ytd_element_cost > nvl(l_max_element_value, l_ytd_element_cost) then
3634 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).element_cost := l_max_element_value;
3635 else
3636 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).element_cost := l_ytd_element_cost;
3637 end if;
3638 /* For Bug No : 2115867 End */
3639
3640 if PSB_WS_POS1.g_elements(l_element_index).salary_flag = 'Y' then
3641 l_salary_defined := FND_API.G_TRUE;
3642 l_num_salary_indexes := l_num_salary_indexes + 1;
3643
3644 -- Bug#3140849: Enforce maximum element value by FTE.
3645 -- Fixing the following statement. We need to use value computed in
3646 -- the immediate prior IF statement.
3647 /*l_salary_element_value:=l_salary_element_value+l_ytd_element_cost;*/
3648 l_salary_element_value := l_salary_element_value +
3649 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).element_cost;
3650 --
3651 l_salary_indexes(l_num_salary_indexes) := PSB_WS_POS1.g_num_pc_costs;
3652 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).element_type := 'S';
3653 end if;
3654
3655 if PSB_WS_POS1.g_elements(l_element_index).follow_salary = 'Y' then
3656 PSB_WS_POS1.g_pc_costs(PSB_WS_POS1.g_num_pc_costs).element_type := 'F';
3657 end if;
3658
3659 end;
3660 end if;
3661
3662
3663 end loop; /* Elements */
3664
3665
3666 -- Initialize API return status to success
3667
3668 p_return_status := FND_API.G_RET_STS_SUCCESS;
3669
3670
3671 EXCEPTION
3672
3673 when FND_API.G_EXC_ERROR then
3674 p_return_status := FND_API.G_RET_STS_ERROR;
3675 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
3676 p_data => l_msg_data);
3677
3678
3679 when FND_API.G_EXC_UNEXPECTED_ERROR then
3680 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3681 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
3682 p_data => l_msg_data);
3683
3684 when OTHERS then
3685 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3686
3687 if FND_MSG_PUB.Check_Msg_Level
3688 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3689 then
3690 FND_MSG_PUB.Add_Exc_Msg
3691 (p_pkg_name => G_PKG_NAME,
3692 p_procedure_name => l_api_name);
3693 end if;
3694
3695 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
3696 p_data => l_msg_data);
3697
3698 END Calculate_Position_Cost_Year;
3699
3700 /* ----------------------------------------------------------------------- */
3701
3702 PROCEDURE Cache_FTE_Profile
3703 ( p_return_status OUT NOCOPY VARCHAR2,
3704 p_position_id IN NUMBER,
3705 p_data_extract_id IN NUMBER,
3706 p_business_group_id IN NUMBER
3707 ) IS
3708
3709 l_entity_id NUMBER;
3710
3711 l_init_index BINARY_INTEGER;
3712
3713 /*For Bug No : 2811698 Start*/
3714 --added the entity_id in order by clause of all the following
3715 --select statements to make sure that we get right values when
3716 --there are more than one profiles with same priority
3717 cursor c_Monthly_Profile is
3718 select b.entity_id,
3719 a.percent
3720 from PSB_ALLOCRULE_PERCENTS a,
3721 PSB_DEFAULTS b
3722 where a.number_of_periods = 12
3723 and a.allocation_rule_id = b.entity_id
3724 and ((nvl(b.global_default_flag, 'N') = 'N'
3725 and exists
3726 (select 1
3727 from PSB_BUDGET_POSITIONS c,
3728 PSB_SET_RELATIONS d
3729 where c.position_id = p_position_id
3730 and c.account_position_set_id = d.account_position_set_id
3731 and d.default_rule_id = b.default_rule_id))
3732 or b.global_default_flag = 'Y')
3733 and b.business_group_id = p_business_group_id
3734 and b.data_extract_id = p_data_extract_id
3735 order by b.priority,b.entity_id,
3736 a.period_num;
3737
3738 cursor c_Quarterly_Profile is
3739 select b.entity_id,
3740 a.percent
3741 from PSB_ALLOCRULE_PERCENTS a,
3742 PSB_DEFAULTS b
3743 where a.number_of_periods = 4
3744 and a.allocation_rule_id = b.entity_id
3745 and ((nvl(b.global_default_flag, 'N') = 'N'
3746 and exists
3747 (select 1
3748 from PSB_BUDGET_POSITIONS c,
3749 PSB_SET_RELATIONS d
3750 where c.position_id = p_position_id
3751 and c.account_position_set_id = d.account_position_set_id
3752 and d.default_rule_id = b.default_rule_id))
3753 or b.global_default_flag = 'Y')
3754 and b.business_group_id = p_business_group_id
3755 and b.data_extract_id = p_data_extract_id
3756 order by b.priority,b.entity_id,
3757 a.period_num;
3758
3759 cursor c_Semiannual_Profile is
3760 select b.entity_id,
3761 a.percent
3762 from PSB_ALLOCRULE_PERCENTS a,
3763 PSB_DEFAULTS b
3764 where a.number_of_periods = 2
3765 and a.allocation_rule_id = b.entity_id
3766 and ((nvl(b.global_default_flag, 'N') = 'N'
3767 and exists
3768 (select 1
3769 from PSB_BUDGET_POSITIONS c,
3770 PSB_SET_RELATIONS d
3771 where c.position_id = p_position_id
3772 and c.account_position_set_id = d.account_position_set_id
3773 and d.default_rule_id = b.default_rule_id))
3774 or b.global_default_flag = 'Y')
3775 and b.business_group_id = p_business_group_id
3776 and b.data_extract_id = p_data_extract_id
3777 order by b.priority,b.entity_id,
3778 a.period_num;
3779
3780 BEGIN
3781
3782 for l_init_index in 1..PSB_WS_ACCT1.g_max_num_amounts loop
3783 g_monthly_profile(l_init_index) := null;
3784 g_quarterly_profile(l_init_index) := null;
3785 g_semiannual_profile(l_init_index) := null;
3786 end loop;
3787
3788 g_num_monthly_profile := 0;
3789 g_num_quarterly_profile := 0;
3790 g_num_semiannual_profile := 0;
3791
3792 for c_Monthly_Profile_Rec in c_Monthly_Profile loop
3793
3794 if c_Monthly_Profile_Rec.entity_id <> nvl(l_entity_id, FND_API.G_MISS_NUM) then
3795 begin
3796
3797 for l_init_index in 1..PSB_WS_ACCT1.g_max_num_amounts loop
3798 g_monthly_profile(l_init_index) := null;
3799 end loop;
3800
3801 g_num_monthly_profile := 0;
3802
3803 l_entity_id := c_Monthly_Profile_Rec.entity_id;
3804
3805 end;
3806 end if;
3807
3808 g_num_monthly_profile := g_num_monthly_profile + 1;
3809 g_monthly_profile(g_num_monthly_profile) := c_Monthly_Profile_Rec.percent;
3810
3811 end loop;
3812
3813 l_entity_id := null;
3814
3815 for c_Quarterly_Profile_Rec in c_Quarterly_Profile loop
3816
3817 if c_Quarterly_Profile_Rec.entity_id <> nvl(l_entity_id, FND_API.G_MISS_NUM) then
3818 begin
3819
3820 for l_init_index in 1..PSB_WS_ACCT1.g_max_num_amounts loop
3821 g_quarterly_profile(l_init_index) := null;
3822 end loop;
3823
3824 g_num_quarterly_profile := 0;
3825
3826 l_entity_id := c_Quarterly_Profile_Rec.entity_id;
3827
3828 end;
3829 end if;
3830
3831 g_num_quarterly_profile := g_num_quarterly_profile + 1;
3832 g_quarterly_profile(g_num_quarterly_profile) := c_Quarterly_Profile_Rec.percent;
3833
3834 end loop;
3835
3836 l_entity_id := null;
3837
3838 for c_Semiannual_Profile_Rec in c_Semiannual_Profile loop
3839
3840 if c_Semiannual_Profile_Rec.entity_id <> nvl(l_entity_id, FND_API.G_MISS_NUM) then
3841 begin
3842
3843 for l_init_index in 1..PSB_WS_ACCT1.g_max_num_amounts loop
3844 g_semiannual_profile(l_init_index) := null;
3845 end loop;
3846
3847 g_num_semiannual_profile := 0;
3848
3849 l_entity_id := c_Semiannual_Profile_Rec.entity_id;
3850
3851 end;
3852 end if;
3853
3854 g_num_semiannual_profile := g_num_semiannual_profile + 1;
3855 g_semiannual_profile(g_num_semiannual_profile) := c_Semiannual_Profile_Rec.percent;
3856
3857 end loop;
3858
3859
3860 -- Initialize API return status to success
3861
3862 p_return_status := FND_API.G_RET_STS_SUCCESS;
3863
3864
3865 EXCEPTION
3866
3867 when FND_API.G_EXC_ERROR then
3868 p_return_status := FND_API.G_RET_STS_ERROR;
3869
3870 when FND_API.G_EXC_UNEXPECTED_ERROR then
3871 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3872
3873 when OTHERS then
3874 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3875 if FND_MSG_PUB.Check_Msg_Level
3876 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3877 then
3878 FND_MSG_PUB.Add_Exc_Msg
3879 (p_pkg_name => G_PKG_NAME,
3880 p_procedure_name => 'Cache_FTE_Profile');
3881 end if;
3882
3883 END Cache_FTE_Profile;
3884
3885 /* ----------------------------------------------------------------------- */
3886
3887 PROCEDURE Distribute_Position_Cost
3888 ( p_return_status OUT NOCOPY VARCHAR2,
3889 p_root_budget_group_id IN NUMBER,
3890 p_flex_code IN NUMBER,
3891 p_rounding_factor IN NUMBER,
3892 p_data_extract_id IN NUMBER,
3893 p_business_group_id IN NUMBER,
3894 p_position_id IN NUMBER,
3895 p_position_line_id IN NUMBER,
3896 p_budget_year_id IN NUMBER,
3897 p_start_date IN DATE,
3898 p_end_date IN DATE
3899 ) IS
3900
3901 l_element_index BINARY_INTEGER;
3902
3903 l_return_status VARCHAR2(1);
3904 l_msg_data VARCHAR2(2000);
3905 l_msg_count NUMBER;
3906
3907 l_api_name CONSTANT VARCHAR2(30) := 'Distribute_Position_Cost';
3908
3909 BEGIN
3910
3911 for l_element_index in 1..PSB_WS_POS1.g_num_elements loop
3912
3913 if PSB_WS_POS1.g_elements(l_element_index).salary_flag = 'Y' then
3914 begin
3915
3916 Distribute_Salary
3917 (p_return_status => l_return_status,
3918 p_pay_element_id => PSB_WS_POS1.g_elements(l_element_index).pay_element_id,
3919 p_root_budget_group_id => p_root_budget_group_id,
3920 p_data_extract_id => p_data_extract_id,
3921 p_flex_code => p_flex_code,
3922 p_rounding_factor => p_rounding_factor,
3923 p_position_line_id => p_position_line_id,
3924 p_position_id => p_position_id,
3925 p_budget_year_id => p_budget_year_id,
3926 p_start_date => p_start_date,
3927 p_end_date => p_end_date);
3928
3929 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3930 raise FND_API.G_EXC_ERROR;
3931 end if;
3932
3933 end;
3934 else
3935 begin
3936
3937 if PSB_WS_POS1.g_elements(l_element_index).follow_salary = 'Y' then
3938 begin
3939
3940 PSB_WS_POS1.Distribute_Following_Elements
3941 (p_return_status => l_return_status,
3942 p_pay_element_id => PSB_WS_POS1.g_elements(l_element_index).pay_element_id,
3943 p_data_extract_id => p_data_extract_id,
3944 p_flex_code => p_flex_code,
3945 p_business_group_id => p_business_group_id,
3946 p_rounding_factor => p_rounding_factor,
3947 p_position_line_id => p_position_line_id,
3948 p_position_id => p_position_id,
3949 p_budget_year_id => p_budget_year_id,
3950 p_start_date => p_start_date,
3951 p_end_date => p_end_date);
3952
3953 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3954 raise FND_API.G_EXC_ERROR;
3955 end if;
3956
3957 end;
3958 else
3959 begin
3960
3961 Distribute_Other_Elements
3962 (p_return_status => l_return_status,
3963 p_pay_element_id => PSB_WS_POS1.g_elements(l_element_index).pay_element_id,
3964 p_data_extract_id => p_data_extract_id,
3965 p_flex_code => p_flex_code,
3966 p_rounding_factor => p_rounding_factor,
3967 p_position_line_id => p_position_line_id,
3968 p_position_id => p_position_id,
3969 p_budget_year_id => p_budget_year_id,
3970 p_start_date => p_start_date,
3971 p_end_date => p_end_date);
3972
3973 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3974 raise FND_API.G_EXC_ERROR;
3975 end if;
3976
3977 end;
3978 end if;
3979
3980 end;
3981 end if;
3982
3983 end loop;
3984
3985
3986 -- Initialize API return status to success
3987
3988 p_return_status := FND_API.G_RET_STS_SUCCESS;
3989
3990
3991 EXCEPTION
3992
3993
3994 when FND_API.G_EXC_ERROR then
3995 p_return_status := FND_API.G_RET_STS_ERROR;
3996 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
3997 p_data => l_msg_data);
3998
3999
4000 when FND_API.G_EXC_UNEXPECTED_ERROR then
4001 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4002 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
4003 p_data => l_msg_data);
4004
4005 when OTHERS then
4006 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4007
4008 if FND_MSG_PUB.Check_Msg_Level
4009 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4010 then
4011 FND_MSG_PUB.Add_Exc_Msg
4012 (p_pkg_name => G_PKG_NAME,
4013 p_procedure_name => l_api_name);
4014 end if;
4015
4016 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
4017 p_data => l_msg_data);
4018 END Distribute_Position_Cost;
4019
4020 /* ----------------------------------------------------------------------- */
4021
4022 PROCEDURE Distribute_Salary
4023 ( p_return_status OUT NOCOPY VARCHAR2,
4024 p_pay_element_id IN NUMBER,
4025 p_root_budget_group_id IN NUMBER,
4026 p_data_extract_id IN NUMBER,
4027 p_flex_code IN NUMBER,
4028 p_rounding_factor IN NUMBER,
4029 p_position_line_id IN NUMBER,
4030 p_position_id IN NUMBER,
4031 p_budget_year_id IN NUMBER,
4032 p_start_date IN DATE,
4033 p_end_date IN DATE
4034 ) IS
4035
4036 l_salary_index BINARY_INTEGER;
4037 l_calc_index BINARY_INTEGER;
4038 l_saldist_index BINARY_INTEGER;
4039
4040 l_percent NUMBER;
4041
4042 l_elem_found VARCHAR2(1) := FND_API.G_FALSE;
4043
4044 l_new_start_date DATE;
4045 l_new_end_date DATE;
4046 l_update_dist BOOLEAN;
4047
4048 /* Bug No 2278216 Start */
4049 l_return_status VARCHAR2(1);
4050 l_rounding_difference NUMBER;
4051 /* Bug No 2278216 End */
4052 l_msg_data VARCHAR2(2000);
4053 l_msg_count NUMBER;
4054
4055 l_api_name CONSTANT VARCHAR2(30) := 'Distribute_Salary';
4056
4057 BEGIN
4058
4059 for l_calc_index in 1..PSB_WS_POS1.g_num_pc_costs loop
4060
4061 if ((PSB_WS_POS1.g_pc_costs(l_calc_index).budget_year_id = p_budget_year_id) and
4062 (PSB_WS_POS1.g_pc_costs(l_calc_index).pay_element_id = p_pay_element_id)) then
4063 l_salary_index := l_calc_index;
4064 l_elem_found := FND_API.G_TRUE;
4065 exit;
4066 end if;
4067
4068 end loop;
4069
4070 if FND_API.to_Boolean(l_elem_found) then
4071 begin
4072
4073 /* Bug No 2278216 Start */
4074 PSB_WS_POS1.Initialize_Period_Dist;
4075 /* Bug No 2278216 End */
4076
4077 PSB_WS_POS1.g_pc_costs(l_salary_index).element_set_id := p_pay_element_id;
4078
4079 l_rounding_difference := 0;
4080
4081 for l_saldist_index in 1..PSB_WS_POS1.g_num_salary_dist loop
4082
4083 l_update_dist := FALSE;
4084
4085 if (((PSB_WS_POS1.g_salary_dist(l_saldist_index).start_date <= p_end_date) and
4086 (PSB_WS_POS1.g_salary_dist(l_saldist_index).end_date is null)) or
4087 ((PSB_WS_POS1.g_salary_dist(l_saldist_index).start_date between p_start_date and p_end_date) or
4088 (PSB_WS_POS1.g_salary_dist(l_saldist_index).end_date between p_start_date and p_end_date) or
4089 ((PSB_WS_POS1.g_salary_dist(l_saldist_index).start_date < p_start_date) and
4090 (PSB_WS_POS1.g_salary_dist(l_saldist_index).end_date > p_end_date)))) then
4091 begin
4092
4093 l_new_start_date := greatest(p_start_date, PSB_WS_POS1.g_salary_dist(l_saldist_index).start_date);
4094 l_new_end_date := least(p_end_date, nvl(PSB_WS_POS1.g_salary_dist(l_saldist_index).end_date, p_end_date));
4095
4096 -- commented for bug # 4502946
4097 /*if PSB_WS_POS1.g_salary_dist(l_saldist_index).percent < 1 then
4098 l_percent := PSB_WS_POS1.g_salary_dist(l_saldist_index).percent;
4099 else
4100 l_percent := PSB_WS_POS1.g_salary_dist(l_saldist_index).percent / 100;
4101 end if;*/
4102
4103 -- added for bug # 4502946
4104 l_percent := PSB_WS_POS1.g_salary_dist(l_saldist_index).percent / 100;
4105
4106 for j in 1..PSB_WS_POS1.g_num_pd_costs loop
4107
4108 if ((PSB_WS_POS1.g_pd_costs(j).ccid = PSB_WS_POS1.g_salary_dist(l_saldist_index).ccid) and
4109 (PSB_WS_POS1.g_pd_costs(j).element_set_id = p_pay_element_id) and
4110 (PSB_WS_POS1.g_pd_costs(j).budget_year_id = p_budget_year_id)) then
4111 /*For Bug No : 2782604 Start*/
4112 --PSB_WS_POS1.g_num_pd_costs := j;
4113 /*For Bug No : 2782604 End*/
4114 l_update_dist := TRUE;
4115 exit;
4116 end if;
4117
4118 end loop;
4119
4120 /* Bug No 2278216 Start */
4121 -- Created a separate procedure for calculating period amounts
4122
4123 if not (l_update_dist) then
4124 begin
4125
4126 PSB_WS_POS1.g_num_pd_costs := PSB_WS_POS1.g_num_pd_costs + 1;
4127
4128 PSB_WS_POS1.g_pd_costs(PSB_WS_POS1.g_num_pd_costs).ccid := PSB_WS_POS1.g_salary_dist(l_saldist_index).ccid;
4129 PSB_WS_POS1.g_pd_costs(PSB_WS_POS1.g_num_pd_costs).budget_year_id := p_budget_year_id;
4130 PSB_WS_POS1.g_pd_costs(PSB_WS_POS1.g_num_pd_costs).element_type := 'S';
4131 PSB_WS_POS1.g_pd_costs(PSB_WS_POS1.g_num_pd_costs).element_set_id := p_pay_element_id;
4132
4133 end;
4134 end if;
4135
4136 PSB_WS_POS1.g_num_periods := PSB_WS_POS1.g_num_periods + 1;
4137
4138 PSB_WS_POS1.g_periods(PSB_WS_POS1.g_num_periods).ccid := PSB_WS_POS1.g_salary_dist(l_saldist_index).ccid;
4139 PSB_WS_POS1.g_periods(PSB_WS_POS1.g_num_periods).budget_year_id := p_budget_year_id;
4140 PSB_WS_POS1.g_periods(PSB_WS_POS1.g_num_periods).element_type := 'S';
4141 PSB_WS_POS1.g_periods(PSB_WS_POS1.g_num_periods).element_set_id := p_pay_element_id;
4142 PSB_WS_POS1.g_periods(PSB_WS_POS1.g_num_periods).percent := l_percent;
4143 PSB_WS_POS1.g_periods(PSB_WS_POS1.g_num_periods).period_start_date := l_new_start_date;
4144 PSB_WS_POS1.g_periods(PSB_WS_POS1.g_num_periods).period_end_date := l_new_end_date;
4145
4146 end;
4147 end if;
4148
4149 end loop;
4150
4151 for l_dist_index in 1..PSB_WS_POS1.g_num_pd_costs loop
4152
4153 if ((PSB_WS_POS1.g_pd_costs(l_dist_index).budget_year_id = p_budget_year_id)
4154 and (PSB_WS_POS1.g_pd_costs(l_dist_index).element_type = 'S')
4155 and (PSB_WS_POS1.g_pd_costs(l_dist_index).element_set_id = p_pay_element_id)) then
4156
4157 for l_period_index in 1..PSB_WS_POS1.g_num_periods loop
4158
4159 if ((PSB_WS_POS1.g_periods(l_period_index).ccid = PSB_WS_POS1.g_pd_costs(l_dist_index).ccid)
4160 and (PSB_WS_POS1.g_periods(l_period_index).element_set_id = PSB_WS_POS1.g_pd_costs(l_dist_index).element_set_id)
4161 and (PSB_WS_POS1.g_periods(l_period_index).element_type = 'S')
4162 and (PSB_WS_POS1.g_periods(l_period_index).budget_year_id = PSB_WS_POS1.g_pd_costs(l_dist_index).budget_year_id)) then
4163
4164 PSB_WS_POS1.Distribute_Periods
4165 (p_return_status => l_return_status,
4166 p_ccid => PSB_WS_POS1.g_pd_costs(l_dist_index).ccid,
4167 p_element_type => 'S',
4168 p_element_set_id => PSB_WS_POS1.g_pd_costs(l_dist_index).element_set_id,
4169 p_budget_year_id => p_budget_year_id,
4170 p_dist_start_date => PSB_WS_POS1.g_periods(l_period_index).period_start_date,
4171 p_dist_end_date => PSB_WS_POS1.g_periods(l_period_index).period_end_date,
4172 p_start_date => p_start_date,
4173 p_end_date => p_end_date,
4174 p_element_index => l_salary_index,
4175 p_dist_index => l_dist_index,
4176 p_percent => PSB_WS_POS1.g_periods(l_period_index).percent);
4177
4178 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
4179 raise FND_API.G_EXC_ERROR;
4180 end if;
4181
4182 end if;
4183
4184 end loop;
4185
4186 end if;
4187 end loop;
4188
4189 /* Bug No 2278216 End */
4190
4191 if p_rounding_factor is not null then
4192 l_rounding_difference := l_rounding_difference +
4193 (round(PSB_WS_POS1.g_pc_costs(l_salary_index).element_cost / p_rounding_factor)
4194 * p_rounding_factor - PSB_WS_POS1.g_pc_costs(l_salary_index).element_cost);
4195 end if;
4196
4197 PSB_WS_POS1.g_pc_costs(l_salary_index).element_cost := PSB_WS_POS1.g_pc_costs(l_salary_index).element_cost +
4198 l_rounding_difference;
4199
4200 end;
4201 end if;
4202
4203 -- Initialize API return status to success
4204
4205 p_return_status := FND_API.G_RET_STS_SUCCESS;
4206
4207 EXCEPTION
4208
4209 when FND_API.G_EXC_ERROR then
4210 p_return_status := FND_API.G_RET_STS_ERROR;
4211 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
4212 p_data => l_msg_data);
4213
4214
4215 when FND_API.G_EXC_UNEXPECTED_ERROR then
4216 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4217 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
4218 p_data => l_msg_data);
4219
4220 when OTHERS then
4221 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4222
4223 if FND_MSG_PUB.Check_Msg_Level
4224 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4225 then
4226 FND_MSG_PUB.Add_Exc_Msg
4227 (p_pkg_name => G_PKG_NAME,
4228 p_procedure_name => l_api_name);
4229 end if;
4230
4231 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
4232 p_data => l_msg_data);
4233
4234 END Distribute_Salary;
4235
4236 /* ----------------------------------------------------------------------- */
4237
4238 PROCEDURE Distribute_Other_Elements
4239 ( p_return_status OUT NOCOPY VARCHAR2,
4240 p_pay_element_id IN NUMBER,
4241 p_data_extract_id IN NUMBER,
4242 p_flex_code IN NUMBER,
4243 p_rounding_factor IN NUMBER,
4244 p_position_line_id IN NUMBER,
4245 p_position_id IN NUMBER,
4246 p_budget_year_id IN NUMBER,
4247 p_start_date IN DATE,
4248 p_end_date IN DATE
4249 ) IS
4250
4251 l_rounding_difference NUMBER;
4252
4253 l_calc_index BINARY_INTEGER;
4254 l_element_index BINARY_INTEGER;
4255
4256 l_percent NUMBER;
4257 l_elem_found VARCHAR2(1) := FND_API.G_FALSE;
4258
4259 l_start_date DATE;
4260 l_end_date DATE;
4261
4262 l_update_dist BOOLEAN;
4263
4264 /* Bug No 2278216 Start */
4265 l_return_status VARCHAR2(1);
4266 /* Bug No 2278216 End */
4267
4268 l_msg_data VARCHAR2(2000);
4269 l_msg_count NUMBER;
4270
4271 l_api_name CONSTANT VARCHAR2(30) := 'Distribute_Other_Elements';
4272
4273 cursor c_Dist is
4274 select a.code_combination_id,
4275 a.distribution_percent,
4276 a.effective_start_date, a.effective_end_date
4277 from PSB_PAY_ELEMENT_DISTRIBUTIONS a,
4278 PSB_ELEMENT_POS_SET_GROUPS b,
4279 PSB_SET_RELATIONS c,
4280 PSB_BUDGET_POSITIONS d
4281 where a.chart_of_accounts_id = p_flex_code
4282 and (((a.effective_start_date <= p_end_date)
4283 and (a.effective_end_date is null))
4284 or ((a.effective_start_date between p_start_date and p_end_date)
4285 or (a.effective_end_date between p_start_date and p_end_date)
4286 or ((a.effective_start_date < p_start_date)
4287 and (a.effective_end_date > p_end_date))))
4288 and a.position_set_group_id = b.position_set_group_id
4289 and b.position_set_group_id = c.position_set_group_id
4290 and b.pay_element_id = p_pay_element_id
4291 and c.account_position_set_id = d.account_position_set_id
4292 and d.data_extract_id = p_data_extract_id
4293 and d.position_id = p_position_id
4294 order by a.distribution_percent desc;
4295
4296 BEGIN
4297
4298 for l_calc_index in 1..PSB_WS_POS1.g_num_pc_costs loop
4299
4300 if ((PSB_WS_POS1.g_pc_costs(l_calc_index).budget_year_id = p_budget_year_id) and
4301 (PSB_WS_POS1.g_pc_costs(l_calc_index).pay_element_id = p_pay_element_id)) then
4302 l_element_index := l_calc_index;
4303 l_elem_found := FND_API.G_TRUE;
4304 exit;
4305 end if;
4306
4307 end loop;
4308
4309 if FND_API.to_Boolean(l_elem_found) then
4310 begin
4311
4312 /* Bug No 2278216 Start */
4313 PSB_WS_POS1.Initialize_Period_Dist;
4314 /* Bug No 2278216 End */
4315
4316 l_rounding_difference := 0;
4317
4318 for c_Dist_Rec in c_Dist loop
4319
4320 l_update_dist := FALSE;
4321
4322 if (((c_Dist_Rec.effective_start_date <= p_start_date) and
4323 (c_Dist_Rec.effective_end_date is null)) or
4324 ((c_Dist_Rec.effective_start_date between p_start_date and p_end_date) or
4325 (c_Dist_Rec.effective_end_date between p_start_date and p_end_date) or
4326 ((c_Dist_Rec.effective_start_date < p_start_date) and
4327 (c_Dist_Rec.effective_end_date > p_end_date)))) then
4328 begin
4329 l_start_date := greatest(p_start_date, c_Dist_Rec.effective_start_date);
4330 l_end_date := least(p_end_date, nvl(c_Dist_Rec.effective_end_date, p_end_date));
4331
4332 PSB_WS_POS1.g_pc_costs(l_element_index).element_set_id := p_pay_element_id;
4333
4334 -- commented for bug # 4502946
4335 /*if c_Dist_Rec.distribution_percent < 1 then
4336 l_percent := c_Dist_Rec.distribution_percent;
4337 else
4338 l_percent := c_Dist_Rec.distribution_percent / 100;
4339 end if;*/
4340
4341 -- added for bug # 4502946
4342 l_percent := c_Dist_Rec.distribution_percent / 100;
4343
4344 for j in 1..PSB_WS_POS1.g_num_pd_costs loop
4345 if ((PSB_WS_POS1.g_pd_costs(j).ccid = c_Dist_Rec.code_combination_id) and
4346 (PSB_WS_POS1.g_pd_costs(j).element_set_id = p_pay_element_id) and
4347 (PSB_WS_POS1.g_pd_costs(j).budget_year_id = p_budget_year_id)) then
4348 /*For Bug No : 2782604 Start*/
4349 --PSB_WS_POS1.g_num_pd_costs := j;
4350 /*For Bug No : 2782604 End*/
4351 l_update_dist := TRUE;
4352 exit;
4353 end if;
4354 end loop;
4355
4356 /* Bug No 2278216 Start */
4357 -- Created a separate procedure for calculating period amounts
4358
4359 if not (l_update_dist) then
4360 begin
4361 PSB_WS_POS1.g_num_pd_costs := PSB_WS_POS1.g_num_pd_costs + 1;
4362
4363 PSB_WS_POS1.g_pd_costs(PSB_WS_POS1.g_num_pd_costs).budget_year_id := p_budget_year_id;
4364 PSB_WS_POS1.g_pd_costs(PSB_WS_POS1.g_num_pd_costs).element_type := 'O';
4365 PSB_WS_POS1.g_pd_costs(PSB_WS_POS1.g_num_pd_costs).ccid := c_Dist_Rec.code_combination_id;
4366 PSB_WS_POS1.g_pd_costs(PSB_WS_POS1.g_num_pd_costs).element_set_id := p_pay_element_id;
4367 end;
4368 end if;
4369
4370 PSB_WS_POS1.g_num_periods := PSB_WS_POS1.g_num_periods + 1;
4371
4372 PSB_WS_POS1.g_periods(PSB_WS_POS1.g_num_periods).ccid := c_Dist_Rec.code_combination_id;
4373 PSB_WS_POS1.g_periods(PSB_WS_POS1.g_num_periods).budget_year_id := p_budget_year_id;
4374 PSB_WS_POS1.g_periods(PSB_WS_POS1.g_num_periods).element_type := 'O';
4375 PSB_WS_POS1.g_periods(PSB_WS_POS1.g_num_periods).element_set_id := p_pay_element_id;
4376 PSB_WS_POS1.g_periods(PSB_WS_POS1.g_num_periods).percent := l_percent;
4377 PSB_WS_POS1.g_periods(PSB_WS_POS1.g_num_periods).period_start_date := l_start_date;
4378 PSB_WS_POS1.g_periods(PSB_WS_POS1.g_num_periods).period_end_date := l_end_date;
4379
4380 end;
4381 end if;
4382
4383 end loop;
4384
4385 for l_dist_index in 1..PSB_WS_POS1.g_num_pd_costs loop
4386
4387 if ((PSB_WS_POS1.g_pd_costs(l_dist_index).budget_year_id = p_budget_year_id)
4388 and (PSB_WS_POS1.g_pd_costs(l_dist_index).element_type = 'O')
4389 and (PSB_WS_POS1.g_pd_costs(l_dist_index).element_set_id = p_pay_element_id)) then
4390
4391 for l_period_index in 1..PSB_WS_POS1.g_num_periods loop
4392
4393 if ((PSB_WS_POS1.g_periods(l_period_index).ccid = PSB_WS_POS1.g_pd_costs(l_dist_index).ccid)
4394 and (PSB_WS_POS1.g_periods(l_period_index).element_set_id = PSB_WS_POS1.g_pd_costs(l_dist_index).element_set_id)
4395 and (PSB_WS_POS1.g_periods(l_period_index).element_type = 'O')
4396 and (PSB_WS_POS1.g_periods(l_period_index).budget_year_id = PSB_WS_POS1.g_pd_costs(l_dist_index).budget_year_id)) then
4397
4398 PSB_WS_POS1.Distribute_Periods
4399 (p_return_status => l_return_status,
4400 p_ccid => PSB_WS_POS1.g_pd_costs(l_dist_index).ccid,
4401 p_element_type => 'O',
4402 p_element_set_id => PSB_WS_POS1.g_pd_costs(l_dist_index).element_set_id,
4403 p_budget_year_id => p_budget_year_id,
4404 p_dist_start_date => PSB_WS_POS1.g_periods(l_period_index).period_start_date,
4405 p_dist_end_date => PSB_WS_POS1.g_periods(l_period_index).period_end_date,
4406 p_start_date => p_start_date,
4407 p_end_date => p_end_date,
4408 p_element_index => l_element_index,
4409 p_dist_index => l_dist_index,
4410 p_percent => PSB_WS_POS1.g_periods(l_period_index).percent);
4411
4412 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
4413 raise FND_API.G_EXC_ERROR;
4414 end if;
4415
4416 end if;
4417
4418 end loop;
4419
4420 end if;
4421 end loop;
4422
4423 /* Bug No 2278216 End */
4424
4425 if p_rounding_factor is not null then
4426 l_rounding_difference := l_rounding_difference +
4427 (round(PSB_WS_POS1.g_pc_costs(l_element_index).element_cost / p_rounding_factor)
4428 * p_rounding_factor - PSB_WS_POS1.g_pc_costs(l_element_index).element_cost);
4429 end if;
4430
4431 PSB_WS_POS1.g_pc_costs(l_element_index).element_cost := PSB_WS_POS1.g_pc_costs(l_element_index).element_cost +
4432 l_rounding_difference;
4433
4434 end;
4435 end if;
4436
4437 -- Initialize API return status to success
4438
4439 p_return_status := FND_API.G_RET_STS_SUCCESS;
4440
4441
4442 EXCEPTION
4443
4444 when FND_API.G_EXC_ERROR then
4445 p_return_status := FND_API.G_RET_STS_ERROR;
4446 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
4447 p_data => l_msg_data);
4448
4449
4450 when FND_API.G_EXC_UNEXPECTED_ERROR then
4451 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4452 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
4453 p_data => l_msg_data);
4454
4455 when OTHERS then
4456 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4457
4458 if FND_MSG_PUB.Check_Msg_Level
4459 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4460 then
4461 FND_MSG_PUB.Add_Exc_Msg
4462 (p_pkg_name => G_PKG_NAME,
4463 p_procedure_name => l_api_name);
4464 end if;
4465
4466 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
4467 p_data => l_msg_data);
4468
4469 END Distribute_Other_Elements;
4470
4471 /* ----------------------------------------------------------------------- */
4472
4473 PROCEDURE Update_Position_Cost
4474 ( p_return_status OUT NOCOPY VARCHAR2,
4475 p_position_line_id IN NUMBER,
4476 p_position_start_date IN DATE,
4477 p_position_end_date IN DATE,
4478 p_worksheet_id IN NUMBER,
4479 p_flex_mapping_set_id IN NUMBER,
4480 p_global_worksheet_id IN NUMBER,
4481 p_func_currency IN VARCHAR2,
4482 p_rounding_factor IN NUMBER,
4483 p_service_package_id IN NUMBER,
4484 p_stage_set_id IN NUMBER,
4485 p_start_stage_seq IN NUMBER,
4486 p_current_stage_seq IN NUMBER,
4487 p_budget_year_id IN NUMBER,
4488 p_budget_group_id IN NUMBER
4489 ) IS
4490
4491 l_start_date DATE;
4492 l_end_date DATE;
4493 l_budget_period_type VARCHAR2(1);
4494 l_long_sequence_no NUMBER;
4495
4496 l_annual_fte NUMBER;
4497 l_period_fte PSB_WS_ACCT1.g_prdamt_tbl_type;
4498 l_num_budget_periods NUMBER;
4499
4500 l_fte_line_id NUMBER;
4501 l_element_line_id NUMBER;
4502 l_account_line_id NUMBER;
4503
4504 l_fte_assigned VARCHAR2(1);
4505 l_position_exists VARCHAR2(1);
4506
4507 l_sal_account_line VARCHAR2(1);
4508
4509 l_period_amount PSB_WS_ACCT1.g_prdamt_tbl_type;
4510
4511 l_fte_index BINARY_INTEGER;
4512 l_elemcost_index BINARY_INTEGER;
4513 l_element_index BINARY_INTEGER;
4514 l_wel_index BINARY_INTEGER;
4515 l_dist_index BINARY_INTEGER;
4516 l_wal_index BINARY_INTEGER;
4517 l_init_index BINARY_INTEGER;
4518 l_assign_index BINARY_INTEGER;
4519 l_year_index BINARY_INTEGER;
4520 l_period_index BINARY_INTEGER;
4521 l_recalc_cost_index BINARY_INTEGER;
4522 l_recalc_dist_index BINARY_INTEGER;
4523
4524 l_element_set_id NUMBER;
4525 l_elemdist_exists BOOLEAN;
4526 l_year_calculated BOOLEAN := FALSE;
4527
4528 l_processing_type VARCHAR2(1);
4529 l_factor NUMBER;
4530
4531 l_old_ytd_amount NUMBER;
4532 l_new_ytd_amount NUMBER;
4533 l_element_cost NUMBER;
4534 l_old_rounding_diff NUMBER;
4535 l_new_rounding_diff NUMBER;
4536
4537 l_note VARCHAR2(4000); --Bug#4571412
4538 l_return_status VARCHAR2(1);
4539 l_msg_data VARCHAR2(2000);
4540 l_msg_count NUMBER;
4541
4542 l_budget_year_name VARCHAR2(15); -- Bug#4571412
4543
4544 l_api_name CONSTANT VARCHAR2(30) := 'Update_Position_Cost';
4545
4546 cursor c_Position_Dist is
4547 select /*+ ORDERED INDEX(a PSB_WS_ACCOUNT_LINES_N5) */
4548 account_line_id, code_combination_id,
4549 service_package_id, element_set_id, budget_group_id
4550 from PSB_WS_ACCOUNT_LINES a
4551 where position_line_id = p_position_line_id
4552 and budget_year_id = p_budget_year_id
4553 and end_stage_seq is null;
4554
4555 cursor c_Position_Cost is
4556 select element_line_id, service_package_id, pay_element_id
4557 from PSB_WS_ELEMENT_LINES
4558 where position_line_id = p_position_line_id
4559 and budget_year_id = p_budget_year_id
4560 and end_stage_seq is null;
4561
4562 cursor c_Position_FTE is
4563 select /*+ ORDERED INDEX(a PSB_WS_FTE_LINES_N1) */
4564 fte_line_id, annual_fte, service_package_id
4565 from PSB_WS_FTE_LINES a
4566 where position_line_id = p_position_line_id
4567 and budget_year_id = p_budget_year_id
4568 and end_stage_seq is null;
4569
4570 cursor c_sp is
4571 select service_package_id
4572 from PSB_SERVICE_PACKAGES
4573 where global_worksheet_id = p_global_worksheet_id;
4574
4575 BEGIN
4576
4577 for l_init_index in 1..g_pf_recalc_fte.Count loop
4578 g_pf_recalc_fte(l_init_index).fte_line_id := null;
4579 g_pf_recalc_fte(l_init_index).annual_fte := null;
4580 g_pf_recalc_fte(l_init_index).service_package_id := null;
4581 g_pf_recalc_fte(l_init_index).ratio := null;
4582 g_pf_recalc_fte(l_init_index).recalc_flag := null;
4583 end loop;
4584
4585 g_num_pf_recalc_fte := 0;
4586
4587 for l_init_index in 1..g_pc_recalc_costs.Count loop
4588 g_pc_recalc_costs(l_init_index).element_line_id := null;
4589 g_pc_recalc_costs(l_init_index).pay_element_id := null;
4590 g_pc_recalc_costs(l_init_index).service_package_id := null;
4591 g_pc_recalc_costs(l_init_index).recalc_flag := null;
4592 end loop;
4593
4594 g_num_pc_recalc_costs := 0;
4595
4596 for l_init_index in 1..g_pd_recalc_costs.Count loop
4597 g_pd_recalc_costs(l_init_index).account_line_id := null;
4598 g_pd_recalc_costs(l_init_index).ccid := null;
4599 g_pd_recalc_costs(l_init_index).element_set_id := null;
4600 g_pd_recalc_costs(l_init_index).service_package_id := null;
4601 g_pd_recalc_costs(l_init_index).budget_group_id := null;
4602 g_pd_recalc_costs(l_init_index).redist_flag := null;
4603 end loop;
4604
4605 g_num_pd_recalc_costs := 0;
4606
4607
4608 -- Store all the current costs, account distribution and FTE distribution
4609 for c_Position_FTE_Rec in c_Position_FTE loop
4610 g_num_pf_recalc_fte := g_num_pf_recalc_fte + 1;
4611 g_pf_recalc_fte(g_num_pf_recalc_fte).fte_line_id := c_Position_FTE_Rec.fte_line_id;
4612 g_pf_recalc_fte(g_num_pf_recalc_fte).service_package_id := c_Position_FTE_Rec.service_package_id;
4613 g_pf_recalc_fte(g_num_pf_recalc_fte).annual_fte := c_Position_FTE_Rec.annual_fte;
4614 g_pf_recalc_fte(g_num_pf_recalc_fte).ratio := null;
4615 end loop;
4616
4617 for c_Position_Cost_Rec in c_Position_Cost loop
4618 g_num_pc_recalc_costs := g_num_pc_recalc_costs + 1;
4619 g_pc_recalc_costs(g_num_pc_recalc_costs).element_line_id := c_Position_Cost_Rec.element_line_id;
4620 g_pc_recalc_costs(g_num_pc_recalc_costs).pay_element_id := c_Position_Cost_Rec.pay_element_id;
4621 g_pc_recalc_costs(g_num_pc_recalc_costs).service_package_id := c_Position_Cost_Rec.service_package_id;
4622 end loop;
4623
4624 for c_Position_Dist_Rec in c_Position_Dist loop
4625 g_num_pd_recalc_costs := g_num_pd_recalc_costs + 1;
4626 g_pd_recalc_costs(g_num_pd_recalc_costs).account_line_id := c_Position_Dist_Rec.account_line_id;
4627 g_pd_recalc_costs(g_num_pd_recalc_costs).ccid := c_Position_Dist_Rec.code_combination_id;
4628 g_pd_recalc_costs(g_num_pd_recalc_costs).element_set_id := c_Position_Dist_Rec.element_set_id;
4629 g_pd_recalc_costs(g_num_pd_recalc_costs).service_package_id := c_Position_Dist_Rec.service_package_id;
4630 g_pd_recalc_costs(g_num_pd_recalc_costs).budget_group_id := c_Position_Dist_Rec.budget_group_id;
4631 end loop;
4632
4633 l_annual_fte := 0;
4634
4635 for l_init_index in 1..PSB_WS_ACCT1.g_max_num_amounts loop
4636 l_period_fte(l_init_index) := null;
4637 end loop;
4638
4639 for l_elemcost_index in 1..PSB_WS_POS1.g_num_pc_costs loop
4640
4641 if ((PSB_WS_POS1.g_pc_costs(l_elemcost_index).budget_year_id = p_budget_year_id) and
4642 (PSB_WS_POS1.g_pc_costs(l_elemcost_index).element_set_id is not null)) then
4643 l_year_calculated := TRUE;
4644 exit;
4645 end if;
4646
4647 end loop;
4648
4649 if l_year_calculated then
4650 begin
4651
4652 for l_year_index in 1..PSB_WS_ACCT1.g_num_budget_years loop
4653
4654 if PSB_WS_ACCT1.g_budget_years(l_year_index).budget_year_id = p_budget_year_id then
4655 l_num_budget_periods := PSB_WS_ACCT1.g_budget_years(l_year_index).num_budget_periods;
4656 l_budget_year_name := PSB_WS_ACCT1.g_budget_years(l_year_index).year_name; -- Bug#4571412
4657 end if;
4658
4659 end loop;
4660
4661 for l_period_index in 1..PSB_WS_ACCT1.g_num_budget_periods loop
4662
4663 if PSB_WS_ACCT1.g_budget_periods(l_period_index).budget_year_id = p_budget_year_id then
4664 begin
4665
4666 l_start_date := PSB_WS_ACCT1.g_budget_periods(l_period_index).start_date;
4667 l_end_date := PSB_WS_ACCT1.g_budget_periods(l_period_index).end_date;
4668 l_budget_period_type := PSB_WS_ACCT1.g_budget_periods(l_period_index).budget_period_type;
4669 l_long_sequence_no := PSB_WS_ACCT1.g_budget_periods(l_period_index).long_sequence_no;
4670
4671 l_fte_assigned := FND_API.G_FALSE;
4672
4673 /*For Bug No : 2811698 Start*/
4674 --Following condition has been changed to execute when there is no FTE profile option set
4675 --or when there is FTE profile option not set and corresponding profile is not available
4676 if ((g_fte_profile_option = 'N') OR
4677 ((l_budget_period_type = 'M' AND g_num_monthly_profile = 0) OR
4678 (l_budget_period_type = 'Q' AND g_num_quarterly_profile = 0) OR
4679 (l_budget_period_type = 'S' AND g_num_semiannual_profile = 0))) then
4680
4681 for l_assign_index in 1..g_num_posfte_assignments loop
4682
4683 if (((g_posfte_assignments(l_assign_index).start_date <= l_start_date) and
4684 (g_posfte_assignments(l_assign_index).end_date is null)) or
4685 ((g_posfte_assignments(l_assign_index).start_date between l_start_date and l_end_date) or
4686 (g_posfte_assignments(l_assign_index).end_date between l_start_date and l_end_date) or
4687 ((g_posfte_assignments(l_assign_index).start_date < l_start_date) and
4688 (g_posfte_assignments(l_assign_index).end_date > l_end_date)))) then
4689 begin
4690
4691 l_period_fte(l_long_sequence_no) := g_posfte_assignments(l_assign_index).fte;
4692 l_annual_fte := l_annual_fte + g_posfte_assignments(l_assign_index).fte;
4693 l_fte_assigned := FND_API.G_TRUE;
4694 exit;
4695
4696 end;
4697 end if;
4698
4699 end loop;
4700 end if;
4701 /*For Bug No : 2811698 End*/
4702
4703 if not FND_API.to_Boolean(l_fte_assigned) then
4704 begin
4705
4706 l_position_exists := FND_API.G_FALSE;
4707
4708 if (((p_position_start_date <= l_start_date) and
4709 (p_position_end_date is null)) or
4710 ((p_position_start_date between l_start_date and l_end_date) or
4711 (p_position_end_date between l_start_date and l_end_date) or
4712 ((p_position_start_date < l_start_date) and
4713 (p_position_end_date > l_end_date)))) then
4714
4715 l_position_exists := FND_API.G_TRUE;
4716 end if;
4717
4718 if FND_API.to_Boolean(l_position_exists) then
4719 begin
4720
4721 if l_budget_period_type = 'M' then
4722 begin
4723
4724 if g_num_monthly_profile = 0 then
4725 l_period_fte(l_long_sequence_no) := g_default_fte;
4726 l_annual_fte := l_annual_fte + g_default_fte;
4727 else
4728 /*For Bug No : 2811698 Start*/
4729 --replaced the g_default_fte to zero in the folowing code
4730 l_period_fte(l_long_sequence_no) := nvl(g_monthly_profile(l_long_sequence_no), 0);
4731 l_annual_fte := l_annual_fte + nvl(g_monthly_profile(l_long_sequence_no), 0);
4732 /*For Bug No : 2811698 End*/
4733 end if;
4734
4735 end;
4736 elsif l_budget_period_type = 'Q' then
4737 begin
4738
4739 if g_num_quarterly_profile = 0 then
4740 l_period_fte(l_long_sequence_no) := g_default_fte;
4741 l_annual_fte := l_annual_fte + g_default_fte;
4742 else
4743 /*For Bug No : 2811698 Start*/
4744 --replaced the g_default_fte to zero in the folowing code
4745 l_period_fte(l_long_sequence_no) := nvl(g_quarterly_profile(l_long_sequence_no), 0);
4746 l_annual_fte := l_annual_fte + nvl(g_quarterly_profile(l_long_sequence_no), 0);
4747 /*For Bug No : 2811698 End*/
4748 end if;
4749
4750 end;
4751 elsif l_budget_period_type = 'S' then
4752 begin
4753
4754 if g_num_semiannual_profile = 0 then
4755 l_period_fte(l_long_sequence_no) := g_default_fte;
4756 l_annual_fte := l_annual_fte + g_default_fte;
4757 else
4758 /*For Bug No : 2811698 Start*/
4759 --replaced the g_default_fte to zero in the folowing code
4760 l_period_fte(l_long_sequence_no) := nvl(g_semiannual_profile(l_long_sequence_no), 0);
4761 l_annual_fte := l_annual_fte + nvl(g_semiannual_profile(l_long_sequence_no), 0);
4762 /*For Bug No : 2811698 End*/
4763 end if;
4764
4765 end;
4766 end if;
4767
4768 end;
4769 end if;
4770
4771 end;
4772 end if;
4773
4774 end;
4775 end if;
4776
4777 end loop;
4778
4779 if l_num_budget_periods <> 0 then
4780 l_annual_fte := l_annual_fte / l_num_budget_periods;
4781 end if;
4782
4783 if g_num_pf_recalc_fte > 0 then
4784 begin
4785
4786 for l_fte_index in 1..g_num_pf_recalc_fte loop
4787
4788 if g_pf_recalc_fte(l_fte_index).service_package_id = p_service_package_id then
4789 begin
4790
4791 g_pf_recalc_fte(l_fte_index).ratio := l_annual_fte;
4792
4793 PSB_WS_POS1.Create_FTE_Lines
4794 (p_api_version => 1.0,
4795 p_return_status => l_return_status,
4796 p_worksheet_id => p_worksheet_id,
4797 p_fte_line_id => g_pf_recalc_fte(l_fte_index).fte_line_id,
4798 p_current_stage_seq => p_current_stage_seq,
4799 p_period_fte => l_period_fte,
4800 p_budget_group_id => p_budget_group_id);
4801
4802 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
4803 raise FND_API.G_EXC_ERROR;
4804 end if;
4805
4806 end;
4807 else
4808 begin
4809
4810 if l_annual_fte <> 0 then
4811 g_pf_recalc_fte(l_fte_index).ratio := g_pf_recalc_fte(l_fte_index).annual_fte / l_annual_fte;
4812 end if;
4813
4814 end;
4815 end if;
4816
4817 end loop;
4818
4819 end;
4820 else
4821 begin
4822
4823 PSB_WS_POS1.Create_FTE_Lines
4824 (p_api_version => 1.0,
4825 p_return_status => l_return_status,
4826 p_fte_line_id => l_fte_line_id,
4827 p_check_spfl_exists => FND_API.G_FALSE,
4828 p_worksheet_id => p_worksheet_id,
4829 p_flex_mapping_set_id => p_flex_mapping_set_id,
4830 p_position_line_id => p_position_line_id,
4831 p_budget_year_id => p_budget_year_id,
4832 p_budget_group_id => p_budget_group_id,
4833 p_annual_fte => l_annual_fte,
4834 p_service_package_id => p_service_package_id,
4835 p_stage_set_id => p_stage_set_id,
4836 p_start_stage_seq => p_start_stage_seq,
4837 p_current_stage_seq => p_current_stage_seq,
4838 p_period_fte => l_period_fte);
4839
4840 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
4841 raise FND_API.G_EXC_ERROR;
4842 end if;
4843
4844 g_num_pf_recalc_fte := g_num_pf_recalc_fte + 1;
4845 g_pf_recalc_fte(g_num_pf_recalc_fte).fte_line_id := l_fte_line_id;
4846 g_pf_recalc_fte(g_num_pf_recalc_fte).service_package_id := p_service_package_id;
4847 g_pf_recalc_fte(g_num_pf_recalc_fte).annual_fte := l_annual_fte;
4848 g_pf_recalc_fte(g_num_pf_recalc_fte).ratio := l_annual_fte;
4849
4850 end;
4851 end if;
4852
4853 for l_elemcost_index in 1..PSB_WS_POS1.g_num_pc_costs loop
4854
4855 if ((PSB_WS_POS1.g_pc_costs(l_elemcost_index).budget_year_id = p_budget_year_id) and
4856 (PSB_WS_POS1.g_pc_costs(l_elemcost_index).element_set_id is not null)) then
4857 begin
4858
4859 l_element_set_id := PSB_WS_POS1.g_pc_costs(l_elemcost_index).element_set_id;
4860 l_elemdist_exists := FALSE;
4861
4862 for l_element_index in 1..PSB_WS_POS1.g_num_elements loop
4863
4864 if PSB_WS_POS1.g_elements(l_element_index).pay_element_id = PSB_WS_POS1.g_pc_costs(l_elemcost_index).pay_element_id then
4865 l_processing_type := PSB_WS_POS1.g_elements(l_element_index).processing_type;
4866 exit;
4867 end if;
4868
4869 end loop;
4870
4871 for l_fte_index in 1..g_num_pf_recalc_fte loop
4872
4873 l_old_rounding_diff := 0;
4874 l_new_rounding_diff := 0;
4875
4876 if ((l_processing_type = 'N') or (g_pf_recalc_fte(l_fte_index).service_package_id = p_service_package_id)) then
4877 l_factor := 1;
4878 else
4879 l_factor := g_pf_recalc_fte(l_fte_index).ratio;
4880 end if;
4881
4882 for l_dist_index in 1..PSB_WS_POS1.g_num_pd_costs loop
4883
4884 if ((PSB_WS_POS1.g_pd_costs(l_dist_index).budget_year_id = p_budget_year_id) and
4885 (PSB_WS_POS1.g_pd_costs(l_dist_index).element_set_id = l_element_set_id)) then
4886 begin
4887
4888 l_elemdist_exists := TRUE;
4889
4890 for l_init_index in 1..PSB_WS_ACCT1.g_max_num_amounts loop
4891 l_period_amount(l_init_index) := null;
4892 end loop;
4893
4894 l_period_amount(1) := PSB_WS_POS1.g_pd_costs(l_dist_index).period1_amount;
4895 l_period_amount(2) := PSB_WS_POS1.g_pd_costs(l_dist_index).period2_amount;
4896 l_period_amount(3) := PSB_WS_POS1.g_pd_costs(l_dist_index).period3_amount;
4897 l_period_amount(4) := PSB_WS_POS1.g_pd_costs(l_dist_index).period4_amount;
4898 l_period_amount(5) := PSB_WS_POS1.g_pd_costs(l_dist_index).period5_amount;
4899 l_period_amount(6) := PSB_WS_POS1.g_pd_costs(l_dist_index).period6_amount;
4900 l_period_amount(7) := PSB_WS_POS1.g_pd_costs(l_dist_index).period7_amount;
4901 l_period_amount(8) := PSB_WS_POS1.g_pd_costs(l_dist_index).period8_amount;
4902 l_period_amount(9) := PSB_WS_POS1.g_pd_costs(l_dist_index).period9_amount;
4903 l_period_amount(10) := PSB_WS_POS1.g_pd_costs(l_dist_index).period10_amount;
4904 l_period_amount(11) := PSB_WS_POS1.g_pd_costs(l_dist_index).period11_amount;
4905 l_period_amount(12) := PSB_WS_POS1.g_pd_costs(l_dist_index).period12_amount;
4906 l_period_amount(13) := PSB_WS_POS1.g_pd_costs(l_dist_index).period13_amount;
4907 l_period_amount(14) := PSB_WS_POS1.g_pd_costs(l_dist_index).period14_amount;
4908 l_period_amount(15) := PSB_WS_POS1.g_pd_costs(l_dist_index).period15_amount;
4909 l_period_amount(16) := PSB_WS_POS1.g_pd_costs(l_dist_index).period16_amount;
4910 l_period_amount(17) := PSB_WS_POS1.g_pd_costs(l_dist_index).period17_amount;
4911 l_period_amount(18) := PSB_WS_POS1.g_pd_costs(l_dist_index).period18_amount;
4912 l_period_amount(19) := PSB_WS_POS1.g_pd_costs(l_dist_index).period19_amount;
4913 l_period_amount(20) := PSB_WS_POS1.g_pd_costs(l_dist_index).period20_amount;
4914 l_period_amount(21) := PSB_WS_POS1.g_pd_costs(l_dist_index).period21_amount;
4915 l_period_amount(22) := PSB_WS_POS1.g_pd_costs(l_dist_index).period22_amount;
4916 l_period_amount(23) := PSB_WS_POS1.g_pd_costs(l_dist_index).period23_amount;
4917 l_period_amount(24) := PSB_WS_POS1.g_pd_costs(l_dist_index).period24_amount;
4918 l_period_amount(25) := PSB_WS_POS1.g_pd_costs(l_dist_index).period25_amount;
4919 l_period_amount(26) := PSB_WS_POS1.g_pd_costs(l_dist_index).period26_amount;
4920 l_period_amount(27) := PSB_WS_POS1.g_pd_costs(l_dist_index).period27_amount;
4921 l_period_amount(28) := PSB_WS_POS1.g_pd_costs(l_dist_index).period28_amount;
4922 l_period_amount(29) := PSB_WS_POS1.g_pd_costs(l_dist_index).period29_amount;
4923 l_period_amount(30) := PSB_WS_POS1.g_pd_costs(l_dist_index).period30_amount;
4924 l_period_amount(31) := PSB_WS_POS1.g_pd_costs(l_dist_index).period31_amount;
4925 l_period_amount(32) := PSB_WS_POS1.g_pd_costs(l_dist_index).period32_amount;
4926 l_period_amount(33) := PSB_WS_POS1.g_pd_costs(l_dist_index).period33_amount;
4927 l_period_amount(34) := PSB_WS_POS1.g_pd_costs(l_dist_index).period34_amount;
4928 l_period_amount(35) := PSB_WS_POS1.g_pd_costs(l_dist_index).period35_amount;
4929 l_period_amount(36) := PSB_WS_POS1.g_pd_costs(l_dist_index).period36_amount;
4930 l_period_amount(37) := PSB_WS_POS1.g_pd_costs(l_dist_index).period37_amount;
4931 l_period_amount(38) := PSB_WS_POS1.g_pd_costs(l_dist_index).period38_amount;
4932 l_period_amount(39) := PSB_WS_POS1.g_pd_costs(l_dist_index).period39_amount;
4933 l_period_amount(40) := PSB_WS_POS1.g_pd_costs(l_dist_index).period40_amount;
4934 l_period_amount(41) := PSB_WS_POS1.g_pd_costs(l_dist_index).period41_amount;
4935 l_period_amount(42) := PSB_WS_POS1.g_pd_costs(l_dist_index).period42_amount;
4936 l_period_amount(43) := PSB_WS_POS1.g_pd_costs(l_dist_index).period43_amount;
4937 l_period_amount(44) := PSB_WS_POS1.g_pd_costs(l_dist_index).period44_amount;
4938 l_period_amount(45) := PSB_WS_POS1.g_pd_costs(l_dist_index).period45_amount;
4939 l_period_amount(46) := PSB_WS_POS1.g_pd_costs(l_dist_index).period46_amount;
4940 l_period_amount(47) := PSB_WS_POS1.g_pd_costs(l_dist_index).period47_amount;
4941 l_period_amount(48) := PSB_WS_POS1.g_pd_costs(l_dist_index).period48_amount;
4942 l_period_amount(49) := PSB_WS_POS1.g_pd_costs(l_dist_index).period49_amount;
4943 l_period_amount(50) := PSB_WS_POS1.g_pd_costs(l_dist_index).period50_amount;
4944 l_period_amount(51) := PSB_WS_POS1.g_pd_costs(l_dist_index).period51_amount;
4945 l_period_amount(52) := PSB_WS_POS1.g_pd_costs(l_dist_index).period52_amount;
4946 l_period_amount(53) := PSB_WS_POS1.g_pd_costs(l_dist_index).period53_amount;
4947 l_period_amount(54) := PSB_WS_POS1.g_pd_costs(l_dist_index).period54_amount;
4948 l_period_amount(55) := PSB_WS_POS1.g_pd_costs(l_dist_index).period55_amount;
4949 l_period_amount(56) := PSB_WS_POS1.g_pd_costs(l_dist_index).period56_amount;
4950 l_period_amount(57) := PSB_WS_POS1.g_pd_costs(l_dist_index).period57_amount;
4951 l_period_amount(58) := PSB_WS_POS1.g_pd_costs(l_dist_index).period58_amount;
4952 l_period_amount(59) := PSB_WS_POS1.g_pd_costs(l_dist_index).period59_amount;
4953 l_period_amount(60) := PSB_WS_POS1.g_pd_costs(l_dist_index).period60_amount;
4954
4955 if PSB_WS_POS1.g_pd_costs(l_dist_index).element_type = 'S' then
4956 l_sal_account_line := FND_API.G_TRUE;
4957 else
4958 l_sal_account_line := FND_API.G_FALSE;
4959 end if;
4960
4961 -- For each recalculated distribution, match the recalculated distribution
4962 -- with the current distribution for the position
4963
4964 l_wal_index := null;
4965
4966 for l_init_index in 1..PSB_WS_ACCT1.g_max_num_amounts loop
4967 l_period_amount(l_init_index) := l_period_amount(l_init_index) * l_factor;
4968 end loop;
4969
4970 l_old_ytd_amount := PSB_WS_POS1.g_pd_costs(l_dist_index).ytd_amount;
4971 l_new_ytd_amount := PSB_WS_POS1.g_pd_costs(l_dist_index).ytd_amount * l_factor;
4972
4973 if ((p_rounding_factor is not null) and
4974 (g_pf_recalc_fte(l_fte_index).service_package_id <> p_service_package_id)) then
4975 l_old_rounding_diff := l_old_rounding_diff +
4976 (round(l_old_ytd_amount / p_rounding_factor) * p_rounding_factor - l_old_ytd_amount);
4977 end if;
4978
4979 if ((p_rounding_factor is not null) and
4980 (g_pf_recalc_fte(l_fte_index).service_package_id <> p_service_package_id)) then
4981 l_new_rounding_diff := l_new_rounding_diff +
4982 (round(l_new_ytd_amount / p_rounding_factor) * p_rounding_factor - l_new_ytd_amount);
4983 end if;
4984
4985 for l_recalc_dist_index in 1..g_num_pd_recalc_costs loop
4986
4987 if ((PSB_WS_POS1.g_pd_costs(l_dist_index).ccid = g_pd_recalc_costs(l_recalc_dist_index).ccid) and
4988 (PSB_WS_POS1.g_pd_costs(l_dist_index).element_set_id = g_pd_recalc_costs(l_recalc_dist_index).element_set_id) and
4989 (PSB_WS_POS1.g_salary_budget_group_id = g_pd_recalc_costs(l_recalc_dist_index).budget_group_id) and
4990 (g_pf_recalc_fte(l_fte_index).service_package_id = g_pd_recalc_costs(l_recalc_dist_index).service_package_id)) then
4991 begin
4992
4993 g_pd_recalc_costs(l_recalc_dist_index).redist_flag := 'Y';
4994 l_wal_index := l_recalc_dist_index;
4995
4996 PSB_WS_ACCT1.Create_Account_Dist
4997 (p_api_version => 1.0,
4998 p_return_status => l_return_status,
4999 p_worksheet_id => p_worksheet_id,
5000 p_account_line_id => g_pd_recalc_costs(l_recalc_dist_index).account_line_id,
5001 p_ytd_amount => l_new_ytd_amount,
5002 p_period_amount => l_period_amount,
5003 p_current_stage_seq => p_current_stage_seq,
5004 p_budget_group_id => p_budget_group_id);
5005
5006 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
5007 raise FND_API.G_EXC_ERROR;
5008 end if;
5009
5010 /* Bug No 1584464 Start */
5011 ---- Create Note Id and Inserts a record in PSB_WS_ACCOUNT_LINE_NOTES table
5012
5013 if PSB_WS_POS3.g_note_parameter_name is not null then
5014 begin
5015
5016 FND_MESSAGE.SET_NAME('PSB', 'PSB_PARAMETER_NOTE_CREATION');
5017 FND_MESSAGE.SET_TOKEN('NAME', PSB_WS_POS3.g_note_parameter_name);
5018 FND_MESSAGE.SET_TOKEN('DATE', sysdate);
5019 l_note := FND_MESSAGE.GET;
5020
5021 -- Bug#4571412.
5022 -- Added parameters to make the call in sync
5023 -- with its definition.
5024 PSB_WS_ACCT1.Create_Note
5025 ( p_return_status => l_return_status
5026 , p_account_line_id => g_pd_recalc_costs(l_recalc_dist_index).account_line_id
5027 , p_note => l_note
5028 , p_chart_of_accounts_id => PSB_WS_POS3.g_flex_code -- Bug#4675858
5029 , p_budget_year => l_budget_year_name
5030 , p_cc_id => g_pd_recalc_costs(l_recalc_dist_index).ccid
5031 , p_concatenated_segments => NULL
5032 ) ;
5033
5034 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
5035 raise FND_API.G_EXC_ERROR;
5036 end if;
5037
5038 ----
5039 /* Bug No 1584464 End */
5040
5041 end;
5042 end if;
5043
5044 end;
5045 end if;
5046
5047 end loop;
5048
5049 if l_wal_index is null then
5050 begin
5051
5052 PSB_WS_ACCT1.Create_Account_Dist
5053 (p_api_version => 1.0,
5054 p_return_status => l_return_status,
5055 p_account_line_id => l_account_line_id,
5056 p_worksheet_id => p_worksheet_id,
5057 p_flex_mapping_set_id => p_flex_mapping_set_id,
5058 p_map_accounts => TRUE,
5059 p_check_spal_exists => FND_API.G_FALSE,
5060 p_gl_cutoff_period => null,
5061 p_allocrule_set_id => null,
5062 p_budget_calendar_id => null,
5063 p_rounding_factor => p_rounding_factor,
5064 p_stage_set_id => p_stage_set_id,
5065 p_budget_year_id => p_budget_year_id,
5066 p_budget_group_id => PSB_WS_POS1.g_salary_budget_group_id,
5067 p_ccid => PSB_WS_POS1.g_pd_costs(l_dist_index).ccid,
5068 p_currency_code => p_func_currency,
5069 p_balance_type => 'E',
5070 p_ytd_amount => l_new_ytd_amount,
5071 p_annual_fte => l_annual_fte,--Bug 3140801
5072 p_period_amount => l_period_amount,
5073 p_position_line_id => p_position_line_id,
5074 p_element_set_id => PSB_WS_POS1.g_pd_costs(l_dist_index).element_set_id,
5075 p_salary_account_line => l_sal_account_line,
5076 p_service_package_id => g_pf_recalc_fte(l_fte_index).service_package_id,
5077 p_start_stage_seq => p_start_stage_seq,
5078 p_current_stage_seq => p_current_stage_seq);
5079
5080 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
5081 raise FND_API.G_EXC_ERROR;
5082 end if;
5083
5084 end;
5085 end if;
5086
5087 end;
5088 end if;
5089
5090 end loop;
5091
5092 if l_elemdist_exists then
5093 begin
5094
5095 -- For each recalculated cost, match the recalculated cost with the current cost for the position
5096
5097 l_wel_index := null;
5098
5099 if g_pf_recalc_fte(l_fte_index).service_package_id = p_service_package_id then
5100 l_element_cost := PSB_WS_POS1.g_pc_costs(l_elemcost_index).element_cost * l_factor;
5101 else
5102 l_element_cost := (PSB_WS_POS1.g_pc_costs(l_elemcost_index).element_cost - l_old_rounding_diff) * l_factor + l_new_rounding_diff;
5103 end if;
5104
5105 for l_recalc_cost_index in 1..g_num_pc_recalc_costs loop
5106
5107 if ((PSB_WS_POS1.g_pc_costs(l_elemcost_index).pay_element_id = g_pc_recalc_costs(l_recalc_cost_index).pay_element_id) and
5108 (g_pf_recalc_fte(l_fte_index).service_package_id = g_pc_recalc_costs(l_recalc_cost_index).service_package_id)) then
5109 begin
5110
5111 g_pc_recalc_costs(l_recalc_cost_index).recalc_flag := 'Y';
5112 l_wel_index := l_recalc_cost_index;
5113
5114 PSB_WS_POS1.Create_Element_Lines
5115 (p_api_version => 1.0,
5116 p_return_status => l_return_status,
5117 p_element_line_id => g_pc_recalc_costs(l_recalc_cost_index).element_line_id,
5118 p_current_stage_seq => p_current_stage_seq,
5119 p_element_cost => l_element_cost);
5120
5121 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
5122 raise FND_API.G_EXC_ERROR;
5123 end if;
5124
5125 end;
5126 end if;
5127
5128 end loop;
5129
5130 if l_wel_index is null then
5131 begin
5132
5133 PSB_WS_POS1.Create_Element_Lines
5134 (p_api_version => 1.0,
5135 p_return_status => l_return_status,
5136 p_element_line_id => l_element_line_id,
5137 p_check_spel_exists => FND_API.G_FALSE,
5138 p_position_line_id => p_position_line_id,
5139 p_budget_year_id => p_budget_year_id,
5140 p_pay_element_id => PSB_WS_POS1.g_pc_costs(l_elemcost_index).pay_element_id,
5141 p_currency_code => p_func_currency,
5142 p_element_cost => l_element_cost,
5143 p_element_set_id => PSB_WS_POS1.g_pc_costs(l_elemcost_index).element_set_id,
5144 p_service_package_id => g_pf_recalc_fte(l_fte_index).service_package_id,
5145 p_stage_set_id => p_stage_set_id,
5146 p_start_stage_seq => p_start_stage_seq,
5147 p_current_stage_seq => p_current_stage_seq);
5148
5149 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
5150 raise FND_API.G_EXC_ERROR;
5151 end if;
5152
5153 end;
5154 end if;
5155
5156 end;
5157 end if; /* elemdist exists */
5158
5159 end loop; /* Loop for Service Package */
5160
5161 end;
5162 end if;
5163
5164 end loop;
5165
5166 end;
5167 end if; /* year calculated */
5168
5169
5170 /* commented for bug 4627338
5171 as th updation has been moved outside the
5172 loop after the call to create_account_dist
5173 which reflects the latest YTD amount */
5174
5175 /*
5176 for c_sp_rec in c_sp loop
5177
5178 PSB_WS_POS1.Update_Annual_FTE
5179 (p_api_version => 1.0,
5180 p_return_status => l_return_status,
5181 p_worksheet_id => p_worksheet_id,
5182 p_position_line_id => p_position_line_id,
5183 p_budget_year_id => p_budget_year_id,
5184 p_service_package_id => c_sp_rec.service_package_id,
5185 p_stage_set_id => p_stage_set_id,
5186 p_current_stage_seq => p_current_stage_seq,
5187 p_budget_group_id => p_budget_group_id);
5188
5189 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
5190 raise FND_API.G_EXC_ERROR;
5191 end if;
5192
5193 end loop;
5194 */
5195
5196
5197 /*For Bug No : 3008353 Start*/
5198 --we shouldn't remove any lines from the prev stages when the
5199 --cost is computed as zero in the current stage. Hence assiging the
5200 --NULL values to the table so as the new cost will be zero and old cost
5201 --will remain exists for the previous stages with the new API call
5202
5203 for l_recalc_cost_index in 1..g_num_pc_recalc_costs loop
5204
5205 if g_pc_recalc_costs(l_recalc_cost_index).recalc_flag is null then
5206 begin
5207
5208 PSB_WS_POS1.Create_Element_Lines
5209 (p_api_version => 1.0,
5210 p_return_status => l_return_status,
5211 p_element_line_id => g_pc_recalc_costs(l_recalc_cost_index).element_line_id,
5212 p_current_stage_seq => p_current_stage_seq,
5213 p_element_cost => 0);
5214
5215 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
5216 raise FND_API.G_EXC_ERROR;
5217 end if;
5218
5219 end;
5220 end if;
5221
5222 end loop;
5223
5224 for l_init_index in 1..PSB_WS_ACCT1.g_max_num_amounts loop
5225 l_period_amount(l_init_index) := NULL;
5226 end loop;
5227
5228 for l_recalc_dist_index in 1..g_num_pd_recalc_costs loop
5229
5230 if g_pd_recalc_costs(l_recalc_dist_index).redist_flag is null then
5231 begin
5232
5233 PSB_WS_ACCT1.Create_Account_Dist
5234 (p_api_version => 1.0,
5235 p_return_status => l_return_status,
5236 p_worksheet_id => p_worksheet_id,
5237 p_account_line_id => g_pd_recalc_costs(l_recalc_dist_index).account_line_id,
5238 p_ytd_amount => 0,
5239 p_period_amount => l_period_amount,
5240 p_current_stage_seq => p_current_stage_seq,
5241 p_budget_group_id => p_budget_group_id);
5242
5243 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
5244 raise FND_API.G_EXC_ERROR;
5245 end if;
5246
5247 end;
5248 end if;
5249
5250 end loop;
5251
5252 /*For Bug No : 3008353 End*/
5253
5254 -- for bug 4627338
5255 for c_sp_rec in c_sp loop
5256
5257 PSB_WS_POS1.Update_Annual_FTE
5258 (p_api_version => 1.0,
5259 p_return_status => l_return_status,
5260 p_worksheet_id => p_worksheet_id,
5261 p_position_line_id => p_position_line_id,
5262 p_budget_year_id => p_budget_year_id,
5263 p_service_package_id => c_sp_rec.service_package_id,
5264 p_stage_set_id => p_stage_set_id,
5265 p_current_stage_seq => p_current_stage_seq,
5266 p_budget_group_id => p_budget_group_id);
5267
5268 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
5269 raise FND_API.G_EXC_ERROR;
5270 end if;
5271
5272 end loop;
5273
5274 -- Initialize API return status to success
5275
5276 p_return_status := FND_API.G_RET_STS_SUCCESS;
5277
5278
5279 EXCEPTION
5280
5281 when FND_API.G_EXC_ERROR then
5282 p_return_status := FND_API.G_RET_STS_ERROR;
5283 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
5284 p_data => l_msg_data);
5285
5286
5287 when FND_API.G_EXC_UNEXPECTED_ERROR then
5288 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5289 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
5290 p_data => l_msg_data);
5291
5292 when OTHERS then
5293 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5294
5295 if FND_MSG_PUB.Check_Msg_Level
5296 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5297 then
5298 FND_MSG_PUB.Add_Exc_Msg
5299 (p_pkg_name => G_PKG_NAME,
5300 p_procedure_name => l_api_name);
5301 end if;
5302
5303 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
5304 p_data => l_msg_data);
5305
5306 END Update_Position_Cost;
5307
5308 /* ----------------------------------------------------------------------- */
5309
5310 -- Add Token and Value to the Message Token array
5311
5312 PROCEDURE message_token(tokname IN VARCHAR2,
5313 tokval IN VARCHAR2) IS
5314
5315 BEGIN
5316
5317 if no_msg_tokens is null then
5318 no_msg_tokens := 1;
5319 else
5320 no_msg_tokens := no_msg_tokens + 1;
5321 end if;
5322
5323 msg_tok_names(no_msg_tokens) := tokname;
5324 msg_tok_val(no_msg_tokens) := tokval;
5325
5326 END message_token;
5327
5328 /* ----------------------------------------------------------------------- */
5329
5330 -- Define a Message Token with a Value and set the Message Name
5331
5332 -- Calls FND_MESSAGE server package to set the Message Stack. This message is
5333 -- retrieved by the calling program.
5334
5335 PROCEDURE add_message(appname IN VARCHAR2,
5336 msgname IN VARCHAR2) IS
5337
5338 i BINARY_INTEGER;
5339
5340 BEGIN
5341
5342 if ((appname is not null) and
5343 (msgname is not null)) then
5344
5345 FND_MESSAGE.SET_NAME(appname, msgname);
5346
5347 if no_msg_tokens is not null then
5348 for i in 1..no_msg_tokens loop
5349 FND_MESSAGE.SET_TOKEN(msg_tok_names(i), msg_tok_val(i));
5350 end loop;
5351 end if;
5352
5353 FND_MSG_PUB.Add;
5354
5355 end if;
5356
5357 -- Clear Message Token stack
5358
5359 no_msg_tokens := 0;
5360
5361 END add_message;
5362
5363 /* ----------------------------------------------------------------------- */
5364
5365 -- Get Attribute Value for an Attribute Value Identifier
5366
5367 FUNCTION Get_Attribute_Value (p_attribute_value_id IN NUMBER) RETURN NUMBER IS
5368
5369 l_attribute_value NUMBER;
5370
5371 cursor c_AttrVal is
5372 -- Fixed bug # 3683644
5373 select FND_NUMBER.canonical_to_number(attribute_value) attribute_value
5374 from PSB_ATTRIBUTE_VALUES
5375 where attribute_value_id = p_attribute_value_id;
5376
5377 BEGIN
5378
5379 for c_AttrVal_Rec in c_AttrVal loop
5380 l_attribute_value := c_AttrVal_Rec.attribute_value;
5381 end loop;
5382
5383 return l_attribute_value;
5384
5385 END Get_Attribute_Value;
5386
5387 /* ----------------------------------------------------------------------- */
5388
5389
5390 END PSB_WS_POS2;