[Home] [Help]
PACKAGE BODY: APPS.PSB_EXCEL2_PVT
Source
1 PACKAGE BODY PSB_EXCEL2_PVT AS
2 /* $Header: PSBVXL2B.pls 120.12 2005/08/30 09:04:47 masethur ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PSB_EXCEL2_PVT';
5
6 g_debug_flag VARCHAR2(1) := 'N';
7
8 -- Storage structures from PSBVWP2B.pls start
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
19 TYPE g_poselasgn_tbl_type IS TABLE OF g_poselasgn_rec_type
20 INDEX BY BINARY_INTEGER;
21
22 g_poselem_assignments g_poselasgn_tbl_type;
23 g_num_poselem_assignments NUMBER;
24
25 TYPE g_poselrate_rec_type IS RECORD
26 ( worksheet_id NUMBER,
27 start_date DATE,
28 end_date DATE,
29 pay_element_id NUMBER,
30 pay_element_option_id NUMBER,
31 pay_basis VARCHAR2(10),
32 element_value_type VARCHAR2(2),
33 element_value NUMBER,
34 formula_id NUMBER );
35
36 TYPE g_poselrate_tbl_type IS TABLE OF g_poselrate_rec_type
37 INDEX BY BINARY_INTEGER;
38
39 g_poselem_rates g_poselrate_tbl_type;
40 g_num_poselem_rates NUMBER;
41
42 TYPE g_posfte_rec_type IS RECORD
43 ( worksheet_id NUMBER,
44 start_date DATE,
45 end_date DATE,
46 fte NUMBER );
47
48 TYPE g_posfte_tbl_type IS TABLE OF g_posfte_rec_type
49 INDEX BY BINARY_INTEGER;
50
51 g_posfte_assignments g_posfte_tbl_type;
52 g_num_posfte_assignments NUMBER;
53
54 TYPE g_poswkh_rec_type IS RECORD
55 ( worksheet_id NUMBER,
56 start_date DATE,
57 end_date DATE,
58 default_weekly_hours NUMBER );
59
60 TYPE g_poswkh_tbl_type IS TABLE OF g_poswkh_rec_type
61 INDEX BY BINARY_INTEGER;
62
63 /* Added for Bug 3558916 */
64
65 -- Number array.
66 TYPE Number_tbl_type IS TABLE OF NUMBER
67 INDEX BY BINARY_INTEGER ;
68
69 -- Character array.
70 TYPE Character_tbl_type IS TABLE OF VARCHAR2(150)
71 INDEX BY BINARY_INTEGER ;
72
73 -- Character array.
74 TYPE Big_character_tbl_type IS TABLE OF VARCHAR2(1000)
75 INDEX BY BINARY_INTEGER ;
76
77 g_poswkh_assignments g_poswkh_tbl_type;
78 g_num_poswkh_assignments NUMBER;
79
80 g_fte_profile PSB_WS_ACCT1.g_prdamt_tbl_type;
81 g_num_fte NUMBER;
82
83 -- Storage structures from PSBVWP2B.pls end
84
85 g_worksheets_tbl PSB_WS_Ops_Pvt.Worksheet_Tbl_Type;
86
87 g_ws_line_year_rec psb_ws_matrix.ws_line_year_rec_type;
88
89 g_max_num_cols CONSTANT NUMBER := 12;
90 g_max_num_pos_ws_cols CONSTANT NUMBER := 168;
91
92 g_export_id NUMBER;
93 g_worksheet_id NUMBER;
94 g_user_id NUMBER;
95 g_stage_id NUMBER;
96
97 g_global_worksheet_id NUMBER;
98 g_assignment_worksheet_id NUMBER;
99 g_global_worksheet_flag VARCHAR2(1);
100 g_local_copy_flag VARCHAR2(1);
101
102 --
103 g_budget_group_id NUMBER;
104 g_set_of_books_id NUMBER;
105 g_chart_of_accounts_id NUMBER;
106 g_currency_code VARCHAR2(15);
107 g_stage_set_id NUMBER;
108 g_current_stage_seq NUMBER;
109 g_business_group_id NUMBER;
110 g_data_extract_id NUMBER;
111
112
113 -- Calendar
114 g_budget_calendar_id NUMBER;
115 g_calendar_start_date DATE;
116 g_calendar_end_date DATE;
117 g_cy_end_date DATE;
118 g_pp_start_date DATE;
119 g_base_spid NUMBER;
120 g_translated_base_sp_name VARCHAR2(30);
121 g_translated_sp_desc VARCHAR2(200);
122
123
124 g_account_export_status VARCHAR2(10);
125 g_position_export_status VARCHAR2(10);
126 g_currency_flag VARCHAR2(1);
127 g_budget_by_position VARCHAR2(1);
128
129
130 g_coa_id NUMBER;
131
132 g_pos_ws_col_no NUMBER;
133 g_total_budget_years NUMBER;
134
135
136 g_ws_cols PSB_EXCEL_PVT.g_ws_col_tbl_type;
137 g_pos_ws_cols PSB_EXCEL_PVT.g_pos_ws_col_tbl_type;
138 g_year_amts PSB_EXCEL_PVT.g_year_amount_tbl_type;
139 g_year_num_periods PSB_EXCEL_PVT.g_year_num_periods_tbl_type;
140 g_acl_ids PSB_EXCEL_PVT.g_acl_id_tbl_type;
141 g_wlbi_amounts PSB_EXCEL_PVT.g_period_amount_tbl_type; --1.. 168
142 g_wal_period_amounts PSB_EXCEL_PVT.g_period_amount_tbl_type; --1..12(upto 60)
143 g_fte_period_amounts PSB_EXCEL_PVT.g_period_amount_tbl_type; --1..12(upto 60)
144
145 TYPE g_estimate_year_type IS RECORD
146 (
147 total_column NUMBER,
148 percent_column NUMBER,
149 period_start_column NUMBER,
150 period_end_column NUMBER
151 );
152 TYPE g_estimate_year_tbl_type IS TABLE of g_estimate_year_type
153 INDEX BY BINARY_INTEGER;
154
155 g_estimate_years g_estimate_year_tbl_type;
156
157
158 TYPE g_assignment_type IS RECORD
159 (
160 period NUMBER,
161 new_amount NUMBER
162 );
163
164 g_assignment_count NUMBER;
165 TYPE g_assignment_tbl_type IS TABLE of g_assignment_type
166 INDEX BY BINARY_INTEGER;
167
168 g_assignment g_assignment_tbl_type;
169 g_assignment_index NUMBER;
170 g_assignment_amount NUMBER;
171
172 g_amt_tolerance_value_type VARCHAR2(1);
173 g_amt_tolerance_value NUMBER;
174 g_pct_tolerance_value_type VARCHAR2(1);
175 g_pct_tolerance_value NUMBER;
176
177 /* ----------------------------------------------------------------------- */
178 /* */
179 /* Private Function and Procedure Declaration */
180 /* */
181 /* ----------------------------------------------------------------------- */
182
183 PROCEDURE Clear_WS_Cols;
184 PROCEDURE Clear_POS_WS_Cols;
185
186 PROCEDURE Get_WS_Cols;
187 PROCEDURE Get_POS_WS_Cols;
188
189 PROCEDURE Get_WS_Line_Bal;
190 PROCEDURE Get_POS_WS_Line_Bal;
191
192
193 PROCEDURE Get_SPID
194 (
195 p_worksheet_id IN NUMBER,
196 p_spname IN VARCHAR2,
197 p_spid OUT NOCOPY NUMBER
198 );
199 PROCEDURE Set_SPID_ALL_WLBI;
200
201 PROCEDURE Get_WAL_Element_Cost
202 ( p_position_line_id IN NUMBER,
203 p_element_set_id IN NUMBER,
204 p_budget_year_id IN NUMBER,
205 p_wal_element_cost OUT NOCOPY NUMBER
206 );
207
208 PROCEDURE Get_WLBI_Element_Cost
209 ( p_budget_year_id IN NUMBER,
210 p_wlbi_element_cost OUT NOCOPY NUMBER
211 );
212
213 PROCEDURE Get_WLBI_SP_Element_Cost
214 ( p_budget_year_id IN NUMBER,
215 p_wlbi_sp_element_cost OUT NOCOPY NUMBER
216 );
217
218 PROCEDURE Get_New_Assignments
219 (
220 p_budget_year_id IN NUMBER
221 );
222
223 PROCEDURE Get_Account_Line
224 (
225 p_worksheet_id IN NUMBER,
226 p_ccid IN NUMBER,
227 p_spid IN NUMBER,
228 p_budget_year_id IN NUMBER,
229 p_stage_seq IN NUMBER,
230 p_account_line_id OUT NOCOPY NUMBER,
231 p_found_acct_line OUT NOCOPY BOOLEAN
232 );
233
234 PROCEDURE Delete_Export_Header;
235 PROCEDURE Delete_Export_Details(p_export_worksheet_type IN VARCHAR2);
236 PROCEDURE Cache_Position_Data
237 (
238 p_return_status OUT NOCOPY VARCHAR2,
239 p_position_line_id IN NUMBER,
240 p_position_id IN NUMBER,
241 p_start_date IN DATE,
242 p_end_date IN DATE
243 );
244
245 PROCEDURE Update_Assignments
246 ( p_return_status OUT NOCOPY VARCHAR2,
247 p_position_line_id IN NUMBER
248 );
249
250 PROCEDURE Change_Pos_Year_Assignments
251 ( p_return_status OUT NOCOPY VARCHAR2,
252 p_worksheet_id IN NUMBER,
253 p_budget_calendar_id IN NUMBER,
254 p_data_extract_id IN NUMBER,
255 p_business_group_id IN NUMBER,
256 p_position_line_id IN NUMBER,
257 p_position_id IN NUMBER,
258 p_position_name IN VARCHAR2,
259 p_pay_element_id IN NUMBER,
260 p_amt_tolerance_value_type IN VARCHAR2,
261 p_amt_tolerance_value IN NUMBER,
262 p_pct_tolerance_value_type IN VARCHAR2,
263 p_pct_tolerance_value IN NUMBER,
264 p_budget_year_id IN NUMBER,
265 p_assignments IN g_assignment_tbl_type
266 );
267
268 PROCEDURE Change_Element_Cost
269 ( p_return_status OUT NOCOPY VARCHAR2,
270 p_position_line_id IN NUMBER,
271 p_pay_element_id IN NUMBER,
272 p_element_set_id IN NUMBER
273 );
274
275 PROCEDURE Get_FTE
276 ( p_position_line_id IN NUMBER,
277 p_budget_year_id IN NUMBER
278 );
279
280 PROCEDURE Update_Distributions
281 ( p_return_status OUT NOCOPY VARCHAR2);
282
283 PROCEDURE Get_Element_Set_ID
284 (
285 p_pay_element_id IN NUMBER,
286 p_position_line_id IN NUMBER,
287 p_element_set_id OUT NOCOPY NUMBER,
288 p_found_element_set OUT NOCOPY BOOLEAN
289 );
290
291
292 PROCEDURE Get_Pos_Account_Line
293 (
294 p_worksheet_id IN NUMBER,
295 p_ccid IN NUMBER,
296 p_spid IN NUMBER,
297 p_position_line_id IN NUMBER,
298 p_budget_year_id IN NUMBER,
299 p_element_set_id IN NUMBER,
300 p_account_line_id OUT NOCOPY NUMBER,
301 p_found_pos_acct_line OUT NOCOPY BOOLEAN
302 );
303
304 PROCEDURE Get_Element_Line_ID
305 ( p_position_line_id IN NUMBER,
306 p_budget_year_id IN NUMBER,
307 p_pay_element_id IN NUMBER,
308 p_service_package_id IN NUMBER,
309 p_found_element_line OUT NOCOPY BOOLEAN,
310 p_element_line_id OUT NOCOPY NUMBER
311 );
312
313
314 /* --------------------- Debug Procedure--------------------- */
315
316 PROCEDURE debug
317 ( p_message IN VARCHAR2) IS
318
319 BEGIN
320
321 if g_debug_flag = 'Y' then
322 null;
323 -- dbms_output.put_line(p_message);
324 end if;
325
326 END debug;
327
328 /* --------------------- Import Worksheet Procedure--------------------- */
329
330
331 PROCEDURE Move_To_PSB
332 (
333 p_api_version IN NUMBER ,
334 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
335 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
336 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
337 p_return_status OUT NOCOPY VARCHAR2 ,
338 p_msg_count OUT NOCOPY NUMBER ,
339 p_msg_data OUT NOCOPY VARCHAR2 ,
340 --
341 p_export_id IN NUMBER,
342 p_import_worksheet_type IN VARCHAR2 := FND_API.G_MISS_CHAR,
343 p_amt_tolerance_value_type IN VARCHAR2 := FND_API.G_MISS_CHAR,
344 p_amt_tolerance_value IN NUMBER := FND_API.G_MISS_NUM,
345 p_pct_tolerance_value_type IN VARCHAR2 := FND_API.G_MISS_CHAR,
346 p_pct_tolerance_value IN NUMBER := FND_API.G_MISS_NUM
347 )
348 IS
349
350 l_validation_status VARCHAR2(1);
351 --
352 l_api_name CONSTANT VARCHAR2(30) := 'Move_To_PSB' ;
353 l_api_version CONSTANT NUMBER := 1.0 ;
354 --
355 l_return_status VARCHAR2(1) ;
356 l_msg_count NUMBER ;
357 l_msg_data VARCHAR2(2000) ;
358 --
359
360 l_index BINARY_INTEGER;
361 l_session_id NUMBER;
362 l_import_positions BOOLEAN;
363 l_import_accounts BOOLEAN;
364 l_import_ws_type VARCHAR2(1);
365 l_selected_template_id NUMBER;
366 CURSOR exp_ws_cur IS
367 SELECT worksheet_id, account_export_status, position_export_status,
368 currency_flag, stage_id, budget_by_position, selected_stage_id,
369 selected_template_id
370 FROM psb_worksheets_i
371 WHERE export_id = p_export_id;
372 Recinfo exp_ws_cur%ROWTYPE;
373
374 BEGIN
375 --
376 SAVEPOINT Move_To_PSB_Pvt ;
377 --
378 IF NOT FND_API.Compatible_API_Call ( l_api_version,
379 p_api_version,
380 l_api_name,
381 G_PKG_NAME )
382 THEN
383 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
384 END IF;
385 --
386
387 IF FND_API.To_Boolean ( p_init_msg_list ) THEN
388 FND_MSG_PUB.initialize ;
389 END IF;
390 --
391 p_return_status := FND_API.G_RET_STS_SUCCESS ;
392 --
393 l_validation_status := FND_API.G_RET_STS_SUCCESS;
394
395 g_export_id := p_export_id;
396 g_amt_tolerance_value_type := p_amt_tolerance_value_type;
397 g_amt_tolerance_value := p_amt_tolerance_value;
398 g_pct_tolerance_value_type := p_pct_tolerance_value_type;
399 g_pct_tolerance_value := p_pct_tolerance_value;
400
401 IF p_import_worksheet_type = FND_API.G_MISS_CHAR THEN
402 l_import_ws_type := 'B';
403 ELSE
404 l_import_ws_type := p_import_worksheet_type;
405 END IF;
406
407 -- Check for Worksheet ID
408 OPEN exp_ws_cur;
409 FETCH exp_ws_cur INTO Recinfo;
410 IF exp_ws_cur%FOUND THEN
411
412
413 IF ( nvl(Recinfo.selected_stage_id,0) > 0 ) THEN
414 FND_MESSAGE.SET_NAME('PSB', 'PSB_IMPORT_NOT_ALLOWED');
415 FND_MSG_PUB.Add;
416 RAISE FND_API.G_EXC_ERROR ;
417 END IF;
418
419 l_selected_template_id := Recinfo.selected_template_id;
420 g_position_export_status := Recinfo.position_export_status;
421 g_account_export_status := Recinfo.account_export_status;
422 g_worksheet_id := Recinfo.worksheet_id;
423 g_currency_flag := Recinfo.currency_flag;
424 g_budget_by_position := Recinfo.budget_by_position;
425 g_current_stage_seq := Recinfo.stage_id;
426
427 ELSE
428 FND_MESSAGE.SET_NAME('PSB', 'PSB_INVALID_ARGUMENT');
429 FND_MESSAGE.SET_TOKEN('ROUTINE', 'Import Worksheet Procedure' );
430 FND_MSG_PUB.Add;
434
431 RAISE FND_API.G_EXC_ERROR ;
432 END IF;
433 CLOSE exp_ws_cur;
435 -- Check if the exported worksheet moved stage or is frozen
436 FOR ws_rec IN
437 (SELECT global_worksheet_id, global_worksheet_flag, local_copy_flag,
438 budget_group_id, extract_id , business_group_id,
439 budget_calendar_id, set_of_books_id, freeze_flag,
440 stage_set_id,current_stage_seq,
441 chart_of_accounts_id, currency_code
442 FROM psb_ws_summary_v ws
443 WHERE ws.worksheet_id = g_worksheet_id)
444
445 LOOP
446
447 IF g_current_stage_seq <> ws_rec.current_stage_seq
448 OR nvl(ws_rec.freeze_flag,'N') = 'Y' THEN
449 FND_MESSAGE.SET_NAME('PSB', 'PSB_IMPORT_NOT_ALLOWED');
450 FND_MSG_PUB.Add;
451 RAISE FND_API.G_EXC_ERROR ;
452 END IF;
453
454 g_global_worksheet_flag := ws_rec.global_worksheet_flag;
455 g_local_copy_flag := ws_rec.local_copy_flag;
456
457 IF ws_rec.global_worksheet_flag = 'N' THEN
458 g_global_worksheet_id := ws_rec.global_worksheet_id;
459
460 ELSE
461 g_global_worksheet_id := g_worksheet_id;
462
463 END IF;
464
465 -- Set the assignment worksheet ID; used in Modify Assignments
466 IF nvl(g_local_copy_flag,'N') = 'Y' THEN
467 g_assignment_worksheet_id := g_worksheet_id;
468 ELSE
469 g_assignment_worksheet_id := g_global_worksheet_id;
470 END IF;
471
472
473 g_budget_group_id := ws_rec.budget_group_id;
474 g_budget_calendar_id := ws_rec.budget_calendar_id;
475 g_set_of_books_id := ws_rec.set_of_books_id;
476 g_chart_of_accounts_id := ws_rec.chart_of_accounts_id;
477 g_stage_set_id := ws_rec.stage_set_id;
478 g_current_stage_seq := ws_rec.current_stage_seq;
479 g_business_group_id := ws_rec.business_group_id;
480 g_data_extract_id := ws_rec.extract_id;
481 g_currency_code := ws_rec.currency_code;
482
483 IF g_currency_flag = 'S' THEN
484 g_currency_code := 'STAT';
485 END IF;
486 END LOOP;
487
488 l_import_accounts := FALSE;
489 l_import_positions := FALSE;
490 IF l_import_ws_type IN ('A','B') and
491 nvl(g_account_export_status,'INSERT') = 'VALIDATE' THEN
492 l_import_accounts := TRUE;
493 END IF;
494
495 -- Rewritten Code
496 IF l_import_accounts and ( nvl(l_selected_template_id,0) > 0 ) THEN
497 FND_MESSAGE.SET_NAME('PSB', 'PSB_IMPORT_NOT_ALLOWED');
498 FND_MSG_PUB.Add;
499 RAISE FND_API.G_EXC_ERROR;
500 END IF;
501
502 IF l_import_ws_type IN ('P','B') and
503 nvl(g_position_export_status,'INSERT') = 'VALIDATE' and
504 g_budget_by_position = 'Y' THEN
505 l_import_positions := TRUE;
506 END IF;
507
508 IF ( not l_import_accounts) AND
509 ( not l_import_positions) THEN
510 FND_MESSAGE.SET_NAME('PSB', 'PSB_INVALID_ARGUMENT');
511 FND_MESSAGE.SET_TOKEN('ROUTINE', 'Import Worksheet Procedure' );
512 FND_MSG_PUB.Add;
513 RAISE FND_API.G_EXC_ERROR ;
514 END IF;
515
516 --
517 --comment the line below when done with testing
518 --Not required as these values set by conc manager
519 --FND_GLOBAL.INITIALIZE(l_session_id, 1011, 500003,101,1,999,1,101,1,1);
520
521 -- Get Parent Worksheets
522 IF g_global_worksheet_id <> g_worksheet_id THEN
523
524 FOR i in 1..g_worksheets_tbl.COUNT LOOP
525 g_worksheets_tbl(i) := NULL;
526 END LOOP;
527
528 PSB_WS_Ops_Pvt.Find_Parent_Worksheets
529 (
530 p_api_version => 1.0,
531 p_init_msg_list => FND_API.G_FALSE,
532 p_commit => FND_API.G_FALSE,
533 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
534 p_return_status => l_return_status,
535 p_msg_count => l_msg_count,
536 p_msg_data => l_msg_data,
537 --
538 p_worksheet_id => g_worksheet_id,
539 p_worksheet_tbl => g_worksheets_tbl
540 );
541
542 IF l_return_status <> FND_API.G_RET_STS_SUCCESS then
543 raise FND_API.G_EXC_ERROR;
544 END IF;
545 END IF;
546
547 PSB_WS_ACCT1.Cache_Budget_Calendar
548 (p_return_status => l_return_status,
549 p_budget_calendar_id => g_budget_calendar_id);
550
551 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
552 raise FND_API.G_EXC_ERROR;
553 end if;
554
555 -- Setup the Calendar Info in Globals
556 PSB_EXCEL_PVT.Get_Calendar_Dates
557 ( p_budget_calendar_id => g_budget_calendar_id,
558 p_calendar_start_date => g_calendar_start_date,
559 p_calendar_end_date => g_calendar_end_date,
560 p_cy_end_date => g_cy_end_date,
561 p_pp_start_date => g_pp_start_date
562 );
563
564 -- Create or Get Service Package IDs for Service Package name
565 FND_MESSAGE.SET_NAME('PSB', 'PSB_IMPORT_SERVICE_PACKAGE');
566 g_translated_sp_desc := FND_MESSAGE.GET;
567
568 -- Get Base Service Package ID to improve performance
569 FND_MESSAGE.SET_NAME('PSB', 'PSB_BASE_SERVICE_PACKAGE');
570 g_translated_base_sp_name := FND_MESSAGE.GET;
571 Get_SPID(p_worksheet_id => g_global_worksheet_id,
572 p_spname => g_translated_base_sp_name,
573 p_spid => g_base_spid
574 );
575
576 IF l_import_accounts THEN
577 debug('Import Accounts');
578 Clear_WS_Cols;
579 Get_WS_Cols;
580 Get_WS_Line_Bal;
581 Delete_Export_Details(p_export_worksheet_type => 'A');
582 END IF;
583
584 IF l_import_positions THEN
585 debug('Import Positions');
586 Set_SPID_ALL_WLBI; -- Set SP Ids for all WLBI Position Lines
587 Clear_POS_WS_Cols;
588 Get_POS_WS_Cols;
589
590 Get_POS_WS_Line_Bal;
591 Delete_Export_Details(p_export_worksheet_type => 'P');
592 END IF;
593
594 Delete_Export_Header;
595
596 IF FND_API.to_Boolean (p_commit) then
597 commit work;
598 END IF;
599
600 EXCEPTION
601 --
602 WHEN FND_API.G_EXC_ERROR THEN
603 --
604 ROLLBACK TO Move_To_PSB_Pvt;
605 p_return_status := FND_API.G_RET_STS_ERROR;
606 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
607 p_data => p_msg_data );
608 --
609 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
610 --
611 ROLLBACK TO Move_To_PSB_Pvt;
612 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
613 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
614 p_data => p_msg_data );
615 --
616 WHEN OTHERS THEN
617 --
618 ROLLBACK TO Move_To_PSB_Pvt;
619 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
620 --
621 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
622 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
623 l_api_name);
624 END IF;
625 --
626 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
627 p_data => p_msg_data );
628 --
629 END Move_To_PSB;
630
631 PROCEDURE Set_SPID_ALL_WLBI IS
632 cursor wlbi_cur is
633 select service_package_name
634 from psb_ws_line_balances_i
635 where export_id = g_export_id
636 and export_worksheet_type = 'P'
637 and service_package_id is NULL
638 for update of service_package_id;
639
640 wlbi_rec wlbi_cur%rowtype;
641
642 l_service_package_id NUMBER;
643 l_service_package_name VARCHAR2(30);
644 l_translated_sp_name VARCHAR2(30);
645
646 BEGIN
647
648 open wlbi_cur;
649 LOOP
650 fetch wlbi_cur into wlbi_rec;
651 EXIT WHEN wlbi_cur%NOTFOUND ;
652
653 /* Bug 3428156 start */
654 -- service package name should keep its original cases
655 -- l_service_package_name := upper(wlbi_rec.service_package_name);
656 l_service_package_name := wlbi_rec.service_package_name;
657 /* Bug 3428156 end */
658
659 IF l_service_package_name is null
660 or l_service_package_name = g_translated_base_sp_name THEN
661 l_service_package_id := g_base_spid;
662 ELSE
663 Get_SPID(p_worksheet_id => g_global_worksheet_id,
664 p_spname => l_service_package_name,
665 p_spid => l_service_package_id
666 );
667 END IF;
668
669 update psb_ws_line_balances_i
670 set service_package_id = l_service_package_id
671 where current of wlbi_cur;
672
673 END LOOP;
674 close wlbi_cur;
675 END Set_SPID_ALL_WLBI;
676
677
678 PROCEDURE Get_SPID(p_worksheet_id IN NUMBER,
679 p_spname IN VARCHAR2,
680 p_spid OUT NOCOPY NUMBER
681 )
682 IS
683
684 l_userid NUMBER := FND_GLOBAL.USER_ID;
685 l_loginid NUMBER := FND_GLOBAL.LOGIN_ID;
686
687 CURSOR ws_sp_cur IS
688 SELECT SERVICE_PACKAGE_ID
689 FROM PSB_SERVICE_PACKAGES
690 WHERE GLOBAL_WORKSHEET_ID = p_worksheet_id
691 AND SHORT_NAME = p_spname;
692
693 Recinfo ws_sp_cur%ROWTYPE;
694
695
696 l_spid NUMBER;
697
698 BEGIN
699
700 OPEN ws_sp_cur;
701 FETCH ws_sp_cur INTO Recinfo;
702 IF ws_sp_cur%FOUND THEN
703 p_spid := Recinfo.SERVICE_PACKAGE_ID;
704
705 ELSE
706 select psb_service_packages_s.nextval into l_spid from dual;
707 INSERT INTO PSB_SERVICE_PACKAGES
708 (
709 SERVICE_PACKAGE_ID,
710 GLOBAL_WORKSHEET_ID,
711 BASE_SERVICE_PACKAGE,
712 NAME,
713 SHORT_NAME,
714 DESCRIPTION,
715 PRIORITY,
716 LAST_UPDATE_DATE,
717 LAST_UPDATED_BY,
718 LAST_UPDATE_LOGIN,
719 CREATED_BY,
720 CREATION_DATE
721 ) VALUES
722 ( l_spid,
723 p_worksheet_id,
724 'N',
725 p_spname,
726 substr(p_spname,1,15),
727 g_translated_sp_desc,
728 NULL,
729 SYSDATE,
730 l_userid,
731 l_loginid,
732 l_userid,
733 SYSDATE
734 );
735 p_spid := l_spid;
736 END IF;
737
738 CLOSE ws_sp_cur;
739
740 END Get_SPID;
741
742 PROCEDURE Clear_WS_Cols is
746 g_ws_cols(l_init_index).budget_year_name := null;
743 BEGIN
744 for l_init_index in 1..g_max_num_cols loop
745 g_ws_cols(l_init_index).budget_year_id := null;
747 g_ws_cols(l_init_index).balance_type := null;
748 g_ws_cols(l_init_index).display_balance_type := null;
749 g_ws_cols(l_init_index).year_category_type := null;
750 end loop;
751 END Clear_WS_Cols;
752
753 PROCEDURE Get_WS_Cols
754 IS
755 l_col_count NUMBER := 0;
756 i NUMBER;
757 BEGIN
758
759 FOR ws_col_rec IN
760 ( SELECT
761 COLUMN_NUMBER,
762 BUDGET_YEAR_ID,
763 BUDGET_YEAR_NAME,
764 BALANCE_TYPE,
765 YEAR_CATEGORY_TYPE
766 FROM PSB_WS_COLUMNS_I
767 WHERE EXPORT_ID = g_export_id
768 AND EXPORT_WORKSHEET_TYPE = 'A'
769 ORDER BY COLUMN_NUMBER
770 )
771 LOOP
772 i := ws_col_rec.COLUMN_NUMBER;
773 g_ws_cols(i).budget_year_id := ws_col_rec.budget_year_id;
774 g_ws_cols(i).budget_year_name:= ws_col_rec.budget_year_name;
775 g_ws_cols(i).balance_type:= ws_col_rec.balance_type;
776 g_ws_cols(i).year_category_type:= ws_col_rec.year_category_type;
777 --debug(i);
778 --debug(g_ws_cols(i).year_category_type);
779 l_col_count := l_col_count + 1;
780
781 END LOOP;
782 END Get_WS_Cols;
783
784 PROCEDURE Get_WS_Line_Bal
785 IS
786
787 l_validation_status VARCHAR2(1) ;
788 l_currency_flag VARCHAR2(1);
789
790 l_return_status VARCHAR2(1);
791 l_msg_count NUMBER;
792 l_msg_data VARCHAR2(2000);
793 l_export_status VARCHAR2(10);
794
795
796 l_col_count NUMBER;
797
798 l_ccid NUMBER;
799 l_old_spid NUMBER;
800 l_spid NUMBER;
801 l_spname VARCHAR2(30);
802
803 l_concatenated_account VARCHAR2(2000);
804 l_currency_code VARCHAR2(15);
805
806 va_ccid NUMBER;
807 va_budget_group_id NUMBER;
808 l_budget_group_id NUMBER;
809 ca_account_line_id NUMBER;
810 l_account_line_id NUMBER;
811
812 l_period_amount PSB_WS_ACCT1.g_prdamt_tbl_type;
813 l_amount NUMBER;
814
815 old_acct BOOLEAN;
816 new_acct BOOLEAN;
817 validated_acct BOOLEAN;
818 found_acct_line BOOLEAN;
819
820 l_code_combination_id_tbl Number_tbl_type;
821 l_concat_account_tbl Big_character_tbl_type;
822 l_account_type_tbl Character_tbl_type;
823
824 l_curr_code_tbl Character_tbl_type;
825 l_template_id_tbl Number_tbl_type;
826 l_service_package_id_tbl Number_tbl_type;
827 l_service_package_name_tbl Character_tbl_type;
828 l_amount1_tbl Number_tbl_type;
829 l_amount2_tbl Number_tbl_type;
830 l_amount3_tbl Number_tbl_type;
831 l_amount4_tbl Number_tbl_type;
832 l_amount5_tbl Number_tbl_type;
833 l_amount6_tbl Number_tbl_type;
834 l_amount7_tbl Number_tbl_type;
835 l_amount8_tbl Number_tbl_type;
836 l_amount9_tbl Number_tbl_type;
837 l_amount10_tbl Number_tbl_type;
838 l_amount11_tbl Number_tbl_type;
839 l_amount12_tbl Number_tbl_type;
840 -- bug 3558916
841 l_service_pack_id psb_service_packages.service_package_id%TYPE;
842
843 /* start Bug 3558916 */
844 CURSOR cur_wlbi
845 IS
846 SELECT
847 MAX(CODE_COMBINATION_ID) CODE_COMBINATION_ID,
848 CONCATENATED_ACCOUNT,
849 ACCOUNT_TYPE,
850 CURRENCY_CODE,
851 TEMPLATE_ID,
852 MAX(DECODE(SERVICE_PACKAGE_ID,0,l_service_pack_id,
853 SERVICE_PACKAGE_ID)) SERVICE_PACKAGE_ID,
854 DECODE(NVL(UPPER(TRIM(SERVICE_PACKAGE_NAME)),'BASE'),
855 'BASE', 'BASE',TRIM(SERVICE_PACKAGE_NAME))
856 SERVICE_PACKAGE_NAME,
857 SUM(AMOUNT1) AMOUNT1,
858 SUM(AMOUNT2) AMOUNT2,
859 SUM(AMOUNT3) AMOUNT3,
860 SUM(AMOUNT4) AMOUNT4,
861 SUM(AMOUNT5) AMOUNT5,
862 SUM(AMOUNT6) AMOUNT6,
863 SUM(AMOUNT7) AMOUNT7,
864 SUM(AMOUNT8) AMOUNT8,
865 SUM(AMOUNT9) AMOUNT9,
866 SUM(AMOUNT10) AMOUNT10,
867 SUM(AMOUNT11) AMOUNT11,
868 SUM(AMOUNT12) AMOUNT12
869 FROM PSB_WS_LINE_BALANCES_I
870 WHERE EXPORT_ID = g_export_id
871 AND EXPORT_WORKSHEET_TYPE = 'A'
872 AND NVL(POSITION_ACCOUNT_FLAG,'N') = 'N'
873 GROUP BY CONCATENATED_ACCOUNT,
874 ACCOUNT_TYPE,
875 CURRENCY_CODE, TEMPLATE_ID,
876 DECODE(NVL(UPPER(TRIM(SERVICE_PACKAGE_NAME)),'BASE'),
877 'BASE', 'BASE', TRIM(SERVICE_PACKAGE_NAME))
878 ORDER BY CODE_COMBINATION_ID;
879
880 /* End Bug 3558916 */
881
882 l_budget_period_id NUMBER;
883 l_end_date DATE;
884 l_gl_cutoff_period DATE;
885 l_last_cy_period BOOLEAN;
886
887 BEGIN
888 /* start Bug 3558916 */
889 BEGIN
890 SELECT service_package_id
891 INTO l_service_pack_id
892 FROM psb_service_packages
893 WHERE global_worksheet_id = g_global_worksheet_id
894 AND name = 'BASE'
895 AND rownum = 1;
896 EXCEPTION
897 WHEN no_data_found then
898 l_service_pack_id := 0;
899 END;
900
901
902 FOR cy_budget_year_cur IN
903 (SELECT bp.budget_period_id, bp.end_date
904 FROM psb_budget_year_types yt,
905 psb_budget_periods bp
906 WHERE yt.budget_year_type_id = bp.budget_year_type_id
907 AND bp.budget_period_type = 'Y'
908 AND yt.year_category_type = 'CY'
909 AND bp. budget_calendar_id = g_budget_calendar_id)
910 LOOP
911
912 l_budget_period_id := cy_budget_year_cur.budget_period_id;
913 l_end_date := cy_budget_year_cur.end_date;
914 END LOOP;
915
916
917 FOR ws_gl_cutoff_cur IN
918 (SELECT gl_cutoff_period
919 FROM psb_worksheets
920 WHERE worksheet_id = g_worksheet_id)
921 LOOP
922
923 l_gl_cutoff_period := ws_gl_cutoff_cur.gl_cutoff_period;
924
925 END LOOP;
926 /* End Bug 3558916 */
927
928
929 l_validation_status := FND_API.G_RET_STS_SUCCESS;
930 -- Initialize the table
931 FOR l_index in 1..PSB_WS_ACCT1.G_MAX_NUM_AMOUNTS LOOP
932 l_period_amount(l_index) := NULL;
933 END LOOP;
934
935 -- Start the Validation process
936 /* Modified for Bug 3558916 Start */
937 OPEN cur_wlbi;
938
939 LOOP
940
941 l_code_combination_id_tbl.DELETE;
942 FETCH cur_wlbi BULK COLLECT INTO
943 l_code_combination_id_tbl,
944 l_concat_account_tbl,
945 l_account_type_tbl,
946 l_curr_code_tbl,
947 l_template_id_tbl, l_service_package_id_tbl,
948 l_service_package_name_tbl, l_amount1_tbl,
949 l_amount2_tbl, l_amount3_tbl,
950 l_amount4_tbl, l_amount5_tbl,
951 l_amount6_tbl, l_amount7_tbl, l_amount8_tbl,
952 l_amount9_tbl, l_amount10_tbl,
953 l_amount11_tbl, l_amount12_tbl
954 LIMIT 350;
955
956 BEGIN
957
958 IF l_code_combination_id_tbl.COUNT = 0 THEN
959 EXIT;
960 END IF;
961
962 FOR i IN 1..l_code_combination_id_tbl.COUNT
963 LOOP
964
965 /* Modified for Bug 3558916 End */
966
967 old_acct := FALSE;
968 new_acct := FALSE;
969 --l_ccid := wlbi_rec.CODE_COMBINATION_ID;
970 l_ccid := l_code_combination_id_tbl(i);
971
972 IF nvl(l_ccid,0) = 0 then
973 new_acct := TRUE;
974 ELSE
975 old_acct := TRUE;
976 END IF;
977
978 --l_old_spid := wlbi_rec.SERVICE_PACKAGE_ID; -- required for reassignment
979 l_old_spid := l_service_package_id_tbl(i); -- required for reassignment
980
981 -- Get the Service Package ID
982
983 /* Bug 3428156 start */
984 --service package name should keep its cases
985 --l_spname := upper(wlbi_rec.SERVICE_PACKAGE_NAME);
986 --l_spname := wlbi_rec.SERVICE_PACKAGE_NAME;
987 l_spname := l_service_package_name_tbl(i);
988 /* Bug 3428156 end */
989
990 IF l_spname is null
991 or l_spname = g_translated_base_sp_name THEN
992 l_spid := g_base_spid;
993 ELSE
994 Get_SPID(p_worksheet_id => g_global_worksheet_id,
995 p_spname => l_spname,
996 p_spid => l_spid
997 );
998 END IF;
999
1000
1001 --l_concatenated_account := wlbi_rec.CONCATENATED_ACCOUNT
1002 l_concatenated_account := l_concat_account_tbl(i);
1003
1004 --debug('CCID '||wlbi_rec.CODE_COMBINATION_ID);
1005
1006 /* Bug 3558916: Commented for implementing BULK FETCH
1007 Move the amount to PL/SQL Table
1008 g_year_amts(1).amount := wlbi_rec.amount1;
1009 g_year_amts(2).amount := wlbi_rec.amount2;
1010 g_year_amts(3).amount := wlbi_rec.amount3;
1011 g_year_amts(4).amount := wlbi_rec.amount4;
1012 g_year_amts(5).amount := wlbi_rec.amount5;
1013 g_year_amts(6).amount := wlbi_rec.amount6;
1014 g_year_amts(7).amount := wlbi_rec.amount7;
1015 g_year_amts(8).amount := wlbi_rec.amount8;
1016 g_year_amts(9).amount := wlbi_rec.amount9;
1017 g_year_amts(10).amount := wlbi_rec.amount10;
1018 g_year_amts(11).amount := wlbi_rec.amount11;
1019 g_year_amts(12).amount := wlbi_rec.amount12; */
1020
1021 -- Move the amount to PL/SQL Table
1022 g_year_amts(1).amount := l_amount1_tbl(i);
1023 g_year_amts(2).amount := l_amount2_tbl(i);
1024 g_year_amts(3).amount := l_amount3_tbl(i);
1025 g_year_amts(4).amount := l_amount4_tbl(i);
1026 g_year_amts(5).amount := l_amount5_tbl(i);
1027 g_year_amts(6).amount := l_amount6_tbl(i);
1028 g_year_amts(7).amount := l_amount7_tbl(i);
1029 g_year_amts(8).amount := l_amount8_tbl(i);
1030 g_year_amts(9).amount := l_amount9_tbl(i);
1031 g_year_amts(10).amount := l_amount10_tbl(i);
1032 g_year_amts(11).amount := l_amount11_tbl(i);
1033 g_year_amts(12).amount := l_amount12_tbl(i);
1034
1035 FOR col_index in 1..g_max_num_cols LOOP
1036 g_year_amts(col_index).amount := nvl(g_year_amts(col_index).amount,0);
1037 END LOOP;
1041 found_acct_line := FALSE;
1038
1039
1040 validated_acct := FALSE;
1042
1043 FOR col_index in 1..g_max_num_cols LOOP
1044
1045 IF g_ws_cols(col_index).balance_type = 'E' THEN
1046
1047 found_acct_line := FALSE;
1048
1049 /* Bug 3589696: Added the IF statement */
1050 l_last_cy_period := FALSE;
1051 IF ((l_budget_period_id = g_ws_cols(col_index).budget_year_id)
1052 AND (l_gl_cutoff_period = l_end_date)) THEN
1053 l_last_cy_period := TRUE;
1054 END IF;
1055
1056 IF old_acct THEN
1057
1058 Get_Account_Line
1059 (
1060 p_worksheet_id => g_worksheet_id,
1061 p_ccid => l_ccid,
1062 p_spid => l_old_spid,
1063 p_budget_year_id => g_ws_cols(col_index).budget_year_id,
1064 p_stage_seq => g_current_stage_seq,
1065 p_account_line_id => l_account_line_id,
1066 p_found_acct_line => found_acct_line
1067 );
1068
1069 END IF;
1070
1071
1072 IF old_acct and found_acct_line THEN
1073 --update account
1074
1075 /* Bug 3589696: Added the IF statement */
1076 IF NOT l_last_cy_period THEN
1077
1078 PSB_WS_ACCT_PVT.Create_Account_Dist
1079 (
1080 p_api_version => 1.0,
1081 p_init_msg_list => FND_API.G_FALSE,
1082 p_commit => FND_API.G_FALSE,
1083 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1084 p_return_status => l_return_status,
1085 p_msg_count => l_msg_count,
1086 p_msg_data => l_msg_data,
1087 --
1088 p_distribute_flag => FND_API.G_TRUE,
1089 p_worksheet_id => g_worksheet_id,
1090 p_account_line_id => l_account_line_id,
1091 p_service_package_id => l_spid,
1092 p_ytd_amount => g_year_amts(col_index).amount,
1093 --
1094 p_period_amount => l_period_amount
1095 );
1096
1097 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1098 RAISE FND_API.G_EXC_ERROR ;
1099 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1100 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1101 END IF;
1102
1103 END IF;
1104
1105
1106 ELSIF (new_acct or ( old_acct and (not found_acct_line) )
1107 and g_year_amts(col_index).amount <> 0 ) THEN
1108 -- bug 4464222 added <> in the above line.
1109
1110 --Validate the Account
1111 IF not validated_acct THEN
1112 PSB_VALIDATE_ACCT_PVT.Validate_Account
1113 (
1114 p_api_version => 1.0,
1115 p_init_msg_list => FND_API.G_FALSE,
1116 p_commit => FND_API.G_FALSE,
1117 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1118 p_return_status => l_return_status,
1119 p_msg_count => l_msg_count,
1120 p_msg_data => l_msg_data,
1121 --
1122 p_parent_budget_group_id => g_budget_group_id,
1123 p_startdate_pp => g_pp_start_date,
1124 p_enddate_cy => g_cy_end_date,
1125 p_set_of_books_id => g_set_of_books_id,
1126 p_flex_code => g_chart_of_accounts_id,
1127 p_create_budget_account => FND_API.G_TRUE,
1128 --
1129 p_concatenated_segments => l_concatenated_account,
1130 --
1131 p_worksheet_id => g_global_worksheet_id,
1132 p_in_ccid => FND_API.G_MISS_NUM,
1133 p_out_ccid => va_ccid,
1134 p_budget_group_id => va_budget_group_id
1135 );
1136
1137 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1138 validated_acct := TRUE;
1139 l_budget_group_id := va_budget_group_id;
1140 ELSE
1141 l_validation_status := FND_API.G_RET_STS_ERROR;
1142 FND_MESSAGE.SET_NAME('PSB', 'PSB_INVALID_CC_SEG');
1143 FND_MESSAGE.SET_TOKEN('CC', l_concatenated_account );
1144 FND_MSG_PUB.Add;
1145 RAISE FND_API.G_EXC_ERROR;
1146 END IF;
1147
1148 END IF; -- if not validated even once for the record
1149 -- Create the Account
1150
1151 /* Bug 3589696 : Added the IF statement */
1152 IF l_last_cy_period THEN
1153 g_year_amts(col_index).amount := 0;
1154 END IF;
1155
1156 PSB_WS_ACCT_PVT.Create_Account_Dist
1157 (
1158 p_api_version => 1.0,
1159 p_init_msg_list => FND_API.G_FALSE,
1160 p_commit => FND_API.G_FALSE,
1161 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1162 p_return_status => l_return_status,
1163 p_msg_count => l_msg_count,
1164 p_msg_data => l_msg_data,
1165 --
1166 p_account_line_id => ca_account_line_id,
1167 p_worksheet_id => g_worksheet_id,
1168 p_map_accounts => TRUE,
1169 p_budget_year_id => g_ws_cols(col_index).budget_year_id,
1170 p_budget_group_id => va_budget_group_id,
1171 p_flex_code => g_chart_of_accounts_id,
1172 p_concatenated_segments => l_concatenated_account,
1173 p_currency_code => g_currency_code,
1174 p_balance_type => 'E', -- Always Estimate
1175 p_ytd_amount => g_year_amts(col_index).amount,
1176 p_distribute_flag => FND_API.G_TRUE,
1177 p_period_amount => l_period_amount,
1178 p_service_package_id => l_spid
1179 );
1180
1181
1182 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1183 RAISE FND_API.G_EXC_ERROR ;
1184 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1185 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1186 END IF;
1187
1188
1189 END IF; --new acct or old acct and not found_acct_line
1190
1191
1192 END IF; -- balance type = 'E'
1193 END LOOP; --for col index 1..colcount
1194
1195 END LOOP; -- For 1..tblcount
1196
1197 EXCEPTION -- To handle exceptions at record level and continue with the rest
1198 WHEN FND_API.G_EXC_ERROR then
1199 l_validation_status := FND_API.G_RET_STS_ERROR;
1200 -- Other Exceptions are handled in Import WS procedure
1201 END;
1202 END LOOP; --For each record in WAL Interface
1203
1204 CLOSE cur_wlbi;
1205
1206 IF l_validation_status = FND_API.G_RET_STS_ERROR THEN
1207 raise FND_API.G_EXC_ERROR;
1208 END IF;
1209
1210 END Get_WS_Line_Bal;
1211
1212
1213
1214 PROCEDURE Get_Account_Line
1215 (
1216 p_worksheet_id IN NUMBER,
1217 p_ccid IN NUMBER,
1218 p_spid IN NUMBER,
1219 p_budget_year_id IN NUMBER,
1220 p_stage_seq IN NUMBER,
1221 p_account_line_id OUT NOCOPY NUMBER,
1222 p_found_acct_line OUT NOCOPY BOOLEAN
1223 )
1224 IS
1225
1226 cursor wal_cur is
1227 SELECT wal.account_line_id
1228 FROM psb_ws_account_lines wal,
1229 psb_ws_lines wl
1230 WHERE wl.worksheet_id = p_worksheet_id
1231 AND wl.account_line_id = wal.account_line_id
1232 AND wal.code_combination_id = p_ccid
1233 AND wal.service_package_id = p_spid
1234 AND wal.budget_year_id = p_budget_year_id
1235 AND wal.balance_type = 'E'
1236 AND wal.end_stage_seq is null;
1237
1238
1239 Recinfo wal_cur%ROWTYPE;
1240
1241 BEGIN
1242 p_account_line_id := 0;
1243 OPEN wal_cur;
1244 FETCH wal_cur INTO Recinfo;
1245 IF wal_cur%FOUND THEN
1246 p_found_acct_line := TRUE;
1247 p_account_line_id := Recinfo.account_line_id;
1248 ELSE
1249 p_found_acct_line := FALSE;
1250 END IF;
1251 close wal_cur;
1252
1253 END Get_Account_Line;
1254
1255
1256 PROCEDURE Clear_POS_WS_Cols is
1257 BEGIN
1258 for l_init_index in 1..g_max_num_pos_ws_cols loop
1259 g_pos_ws_cols(l_init_index).column_type := null;
1260 g_pos_ws_cols(l_init_index).budget_year_id := null;
1261 g_pos_ws_cols(l_init_index).budget_year_name := null;
1262 g_pos_ws_cols(l_init_index).budget_period_id := null;
1263 g_pos_ws_cols(l_init_index).budget_period_name := null;
1264 g_pos_ws_cols(l_init_index).balance_type := null;
1265 g_pos_ws_cols(l_init_index).display_balance_type := null;
1266 g_pos_ws_cols(l_init_index).year_category_type := null;
1267 end loop;
1268 END Clear_POS_WS_Cols;
1269
1270 PROCEDURE Get_POS_WS_Cols
1271 IS
1272 l_col_count NUMBER := 1;
1273 i NUMBER;
1274 l_budget_year_id NUMBER;
1275 BEGIN
1276
1277
1278 FOR ws_col_rec IN
1279 ( SELECT
1280 COLUMN_NUMBER,
1281 COLUMN_TYPE,
1282 BUDGET_YEAR_ID,
1283 BUDGET_YEAR_NAME,
1284 BUDGET_PERIOD_ID,
1285 BUDGET_PERIOD_NAME,
1286 BALANCE_TYPE,
1287 YEAR_CATEGORY_TYPE
1288 FROM PSB_WS_COLUMNS_I
1289 WHERE EXPORT_ID = g_export_id
1290 AND EXPORT_WORKSHEET_TYPE = 'P'
1291 ORDER BY COLUMN_NUMBER
1292 )
1293 LOOP
1294 i := ws_col_rec.COLUMN_NUMBER;
1295 g_pos_ws_cols(i).column_type := ws_col_rec.column_type;
1296 g_pos_ws_cols(i).budget_year_id := ws_col_rec.budget_year_id;
1297 g_pos_ws_cols(i).budget_year_name:= ws_col_rec.budget_year_name;
1298 g_pos_ws_cols(i).budget_period_id := ws_col_rec.budget_year_id;
1299 g_pos_ws_cols(i).budget_period_name:= ws_col_rec.budget_year_name;
1300 g_pos_ws_cols(i).balance_type:= ws_col_rec.balance_type;
1301 g_pos_ws_cols(i).year_category_type:= ws_col_rec.year_category_type;
1302
1303 END LOOP;
1304
1305 -- Also create a table of estimate years for easy access
1306
1307 FOR ws_col_rec IN
1308 ( SELECT
1309 min(column_number) total_column,
1310 min(column_number)+1 percent_column,
1311 min(column_number)+2 period_start_column,
1312 max(column_number) period_end_column,
1313 budget_year_id
1314 FROM psb_ws_columns_i
1315 WHERE export_id = g_export_id
1316 AND export_worksheet_type = 'P'
1317 AND balance_type = 'E'
1318 GROUP BY budget_year_id )
1319 LOOP
1320 g_estimate_years(ws_col_rec.budget_year_id).total_column := ws_col_rec.total_column;
1321 g_estimate_years(ws_col_rec.budget_year_id).percent_column := ws_col_rec.percent_column;
1322 g_estimate_years(ws_col_rec.budget_year_id).period_start_column := ws_col_rec.period_start_column;
1323 g_estimate_years(ws_col_rec.budget_year_id).period_end_column := ws_col_rec.period_end_column;
1324
1325 END LOOP;
1326
1327 END Get_POS_WS_Cols;
1328
1329 PROCEDURE Get_POS_WS_Line_Bal
1330 IS
1331
1332 l_return_status VARCHAR2(1);
1333 ua_return_status VARCHAR2(1);
1334 ud_return_status VARCHAR2(1);
1335 l_position_line_id NUMBER;
1336
1337 BEGIN
1338
1339 -- Cache all the Elements
1340 PSB_WS_POS1.Cache_Elements
1341 (p_return_status => l_return_status,
1342 p_data_extract_id => g_data_extract_id,
1343 p_business_group_id => g_business_group_id,
1344 p_worksheet_id => g_worksheet_id);
1345
1346 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1347 raise FND_API.G_EXC_ERROR;
1348 end if;
1349
1350
1351 PSB_WS_POS1.Cache_Named_Attributes
1352 (p_return_status => l_return_status,
1353 p_business_group_id => g_business_group_id);
1354
1355 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1356 raise FND_API.G_EXC_ERROR;
1357 end if;
1358
1359 ua_return_status := FND_API.G_RET_STS_SUCCESS;
1360 ud_return_status := FND_API.G_RET_STS_SUCCESS;
1361
1362
1363 -- Drive the process with Positions
1364 FOR position_rec in
1365 ( select distinct position_line_id
1366 from psb_ws_line_balances_i
1367 where export_id = g_export_id
1368 and export_worksheet_type = 'P'
1369 )
1370 LOOP
1371 l_position_line_id := position_rec.position_line_id;
1372 --debug('Plid'||l_position_line_id);
1373
1374
1375 Update_Assignments(p_return_status => l_return_status,
1376 p_position_line_id => position_rec.position_line_id);
1377
1378 IF l_return_status <> FND_API.G_RET_STS_SUCCESS then
1379 ua_return_status := l_return_status;
1380 END IF;
1381
1382 END LOOP;
1383
1384
1385 -- Account Distribution should be updated irrespective of
1386 -- total element cost change
1387
1388 Update_Distributions(p_return_status => ud_return_status);
1389
1390 -- Using FND_API constants here leads to DB crash
1391 IF (ua_return_status <> 'S' ) OR
1392 (ud_return_status <> 'S' ) THEN
1393 raise FND_API.G_EXC_ERROR;
1394 END IF;
1395
1396 END Get_POS_WS_Line_Bal;
1397
1398
1399 PROCEDURE Update_Distributions
1400 ( p_return_status OUT NOCOPY VARCHAR2)
1401 IS
1402
1403 l_period_start_column NUMBER;
1404 l_period_end_column NUMBER;
1405 l_year_total_column NUMBER;
1406 l_year_amount NUMBER := 0;
1407
1408
1409 l_year_index BINARY_INTEGER;
1410 l_period_index BINARY_INTEGER;
1411 l_wlbi_index BINARY_INTEGER;
1412
1413 l_period_amounts PSB_WS_ACCT1.g_prdamt_tbl_type;
1414 l_validation_status VARCHAR2(1) ;
1415 l_currency_flag VARCHAR2(1);
1416
1417 l_return_status VARCHAR2(1);
1418 l_msg_count NUMBER;
1419 l_msg_data VARCHAR2(2000);
1420 l_export_status VARCHAR2(10);
1421
1422 --
1423 l_col_count NUMBER;
1424
1425 l_pay_element_id NUMBER;
1426 l_position_line_id NUMBER;
1427 l_ccid NUMBER;
1428 l_old_spid NUMBER;
1429 l_spid NUMBER;
1430 l_spname VARCHAR2(30);
1431 l_salary_account_line VARCHAR2(1);
1432 l_element_set_id NUMBER;
1433
1434 l_concatenated_account VARCHAR2(2000);
1435 l_currency_code VARCHAR2(15);
1436
1437 va_ccid NUMBER;
1438 va_budget_group_id NUMBER;
1439 l_budget_group_id NUMBER;
1440 ca_account_line_id NUMBER;
1441 l_account_line_id NUMBER;
1442
1443
1444 l_amount NUMBER;
1445
1446 old_acct BOOLEAN;
1447 new_acct BOOLEAN;
1448 validated_acct BOOLEAN;
1449 found_pos_acct_line BOOLEAN;
1450 found_element_set BOOLEAN;
1451 l_create_follow_sal_acct BOOLEAN;
1452
1453 l_disp_amt number;
1454
1455 BEGIN
1456 l_validation_status := FND_API.G_RET_STS_SUCCESS;
1457
1458 -- Initialize the table
1459 FOR l_index in 1..PSB_WS_ACCT1.G_MAX_NUM_AMOUNTS LOOP
1460 l_period_amounts(l_index) := NULL;
1461 END LOOP;
1462
1463 FOR wlbi_rec IN
1464 (
1465 SELECT
1466 CODE_COMBINATION_ID,
1467 CONCATENATED_ACCOUNT,
1468 ACCOUNT_TYPE,
1469 CURRENCY_CODE,
1470 TEMPLATE_ID,
1471 SERVICE_PACKAGE_ID,
1472 SERVICE_PACKAGE_NAME,
1473 POSITION_LINE_ID,
1474 POSITION_ID,
1475 PAY_ELEMENT_ID,
1476 PAY_ELEMENT_SET_ID,
1477 PAY_ELEMENT_NAME,
1478 FOLLOW_SALARY,
1479 SALARY_ACCOUNT_LINE,
1480 AMOUNT1 a1,AMOUNT2 a2,AMOUNT3 a3,AMOUNT4 a4,AMOUNT5 a5,
1481 AMOUNT6 a6,AMOUNT7 a7,AMOUNT8 a8,AMOUNT9 a9,AMOUNT10 a10,
1482 AMOUNT11 a11,AMOUNT12 a12,AMOUNT13 a13,AMOUNT14 a14,AMOUNT15 a15,
1483 AMOUNT16 a16,AMOUNT17 a17,AMOUNT18 a18,AMOUNT19 a19,AMOUNT20 a20,
1484 AMOUNT21 a21,AMOUNT22 a22,AMOUNT23 a23,AMOUNT24 a24,AMOUNT25 a25,
1485 AMOUNT26 a26,AMOUNT27 a27,AMOUNT28 a28,AMOUNT29 a29,AMOUNT30 a30,
1486 AMOUNT31 a31,AMOUNT32 a32,AMOUNT33 a33,AMOUNT34 a34,AMOUNT35 a35,
1487 AMOUNT36 a36,AMOUNT37 a37,AMOUNT38 a38,AMOUNT39 a39,AMOUNT40 a40,
1488 AMOUNT41 a41,AMOUNT42 a42,AMOUNT43 a43,AMOUNT44 a44,AMOUNT45 a45,
1489 AMOUNT46 a46,AMOUNT47 a47,AMOUNT48 a48,AMOUNT49 a49,AMOUNT50 a50,
1490 AMOUNT51 a51,AMOUNT52 a52,AMOUNT53 a53,AMOUNT54 a54,AMOUNT55 a55,
1491 AMOUNT56 a56,AMOUNT57 a57,AMOUNT58 a58,AMOUNT59 a59,AMOUNT60 a60,
1492 AMOUNT61 a61,AMOUNT62 a62,AMOUNT63 a63,AMOUNT64 a64,AMOUNT65 a65,
1496 AMOUNT81 a81,AMOUNT82 a82,AMOUNT83 a83,AMOUNT84 a84,AMOUNT85 a85,
1493 AMOUNT66 a66,AMOUNT67 a67,AMOUNT68 a68,AMOUNT69 a69,AMOUNT70 a70,
1494 AMOUNT71 a71,AMOUNT72 a72,AMOUNT73 a73,AMOUNT74 a74,AMOUNT75 a75,
1495 AMOUNT76 a76,AMOUNT77 a77,AMOUNT78 a78,AMOUNT79 a79,AMOUNT80 a80,
1497 AMOUNT86 a86,AMOUNT87 a87,AMOUNT88 a88,AMOUNT89 a89,AMOUNT90 a90,
1498 AMOUNT91 a91,AMOUNT92 a92,AMOUNT93 a93,AMOUNT94 a94,AMOUNT95 a95,
1499 AMOUNT96 a96,AMOUNT97 a97,AMOUNT98 a98,AMOUNT99 a99,AMOUNT100 a100,
1500 AMOUNT101 a101,AMOUNT102 a102,AMOUNT103 a103,AMOUNT104 a104,AMOUNT105 a105,
1501 AMOUNT106 a106,AMOUNT107 a107,AMOUNT108 a108,AMOUNT109 a109,AMOUNT110 a110,
1502 AMOUNT111 a111,AMOUNT112 a112,AMOUNT113 a113,AMOUNT114 a114,AMOUNT115 a115,
1503 AMOUNT116 a116,AMOUNT117 a117,AMOUNT118 a118,AMOUNT119 a119,AMOUNT120 a120,
1504 AMOUNT121 a121,AMOUNT122 a122,AMOUNT123 a123,AMOUNT124 a124,AMOUNT125 a125,
1505 AMOUNT126 a126,AMOUNT127 a127,AMOUNT128 a128,AMOUNT129 a129,AMOUNT130 a130,
1506 AMOUNT131 a131,AMOUNT132 a132,AMOUNT133 a133,AMOUNT134 a134,AMOUNT135 a135,
1507 AMOUNT136 a136,AMOUNT137 a137,AMOUNT138 a138,AMOUNT139 a139,AMOUNT140 a140,
1508 AMOUNT141 a141,AMOUNT142 a142,AMOUNT143 a143,AMOUNT144 a144,AMOUNT145 a145,
1509 AMOUNT146 a146,AMOUNT147 a147,AMOUNT148 a148,AMOUNT149 a149,AMOUNT150 a150,
1510 AMOUNT151 a151,AMOUNT152 a152,AMOUNT153 a153,AMOUNT154 a154,AMOUNT155 a155,
1511 AMOUNT156 a156,AMOUNT157 a157,AMOUNT158 a158,AMOUNT159 a159,AMOUNT160 a160,
1512 AMOUNT161 a161,AMOUNT162 a162,AMOUNT163 a163,AMOUNT164 a164,AMOUNT165 a165,
1513 AMOUNT166 a166,AMOUNT167 a167,AMOUNT168 a168
1514 FROM psb_ws_line_balances_i
1515 WHERE export_worksheet_type = 'P'
1516 AND export_id = g_export_id
1517 AND value_type = 'A' -- process only Amount rows
1518 ORDER BY position_line_id, salary_account_line ,code_combination_id
1519 -- Process updates before insert; within updates process non salary account lines first
1520 )
1521 LOOP
1522 BEGIN
1523
1524 -- Move the Amounts to PL/SQL table
1525 g_wlbi_amounts(1).amount := wlbi_rec.a1;
1526 g_wlbi_amounts(2).amount := wlbi_rec.a2;
1527 g_wlbi_amounts(3).amount := wlbi_rec.a3;
1528 g_wlbi_amounts(4).amount := wlbi_rec.a4;
1529 g_wlbi_amounts(5).amount := wlbi_rec.a5;
1530 g_wlbi_amounts(6).amount := wlbi_rec.a6;
1531 g_wlbi_amounts(7).amount := wlbi_rec.a7;
1532 g_wlbi_amounts(8).amount := wlbi_rec.a8;
1533 g_wlbi_amounts(9).amount := wlbi_rec.a9;
1534 g_wlbi_amounts(10).amount := wlbi_rec.a10;
1535
1536 g_wlbi_amounts(11).amount := wlbi_rec.a11;
1537 g_wlbi_amounts(12).amount := wlbi_rec.a12;
1538 g_wlbi_amounts(13).amount := wlbi_rec.a13;
1539 g_wlbi_amounts(14).amount := wlbi_rec.a14;
1540 g_wlbi_amounts(15).amount := wlbi_rec.a15;
1541 g_wlbi_amounts(16).amount := wlbi_rec.a16;
1542 g_wlbi_amounts(17).amount := wlbi_rec.a17;
1543 g_wlbi_amounts(18).amount := wlbi_rec.a18;
1544 g_wlbi_amounts(19).amount := wlbi_rec.a19;
1545 g_wlbi_amounts(20).amount := wlbi_rec.a20;
1546
1547 g_wlbi_amounts(21).amount := wlbi_rec.a21;
1548 g_wlbi_amounts(22).amount := wlbi_rec.a22;
1549 g_wlbi_amounts(23).amount := wlbi_rec.a23;
1550 g_wlbi_amounts(24).amount := wlbi_rec.a24;
1551 g_wlbi_amounts(25).amount := wlbi_rec.a25;
1552 g_wlbi_amounts(26).amount := wlbi_rec.a26;
1553 g_wlbi_amounts(27).amount := wlbi_rec.a27;
1554 g_wlbi_amounts(28).amount := wlbi_rec.a28;
1555 g_wlbi_amounts(29).amount := wlbi_rec.a29;
1556 g_wlbi_amounts(30).amount := wlbi_rec.a30;
1557
1558 g_wlbi_amounts(31).amount := wlbi_rec.a31;
1559 g_wlbi_amounts(32).amount := wlbi_rec.a32;
1560 g_wlbi_amounts(33).amount := wlbi_rec.a33;
1561 g_wlbi_amounts(34).amount := wlbi_rec.a34;
1562 g_wlbi_amounts(35).amount := wlbi_rec.a35;
1563 g_wlbi_amounts(36).amount := wlbi_rec.a36;
1564 g_wlbi_amounts(37).amount := wlbi_rec.a37;
1565 g_wlbi_amounts(38).amount := wlbi_rec.a38;
1566 g_wlbi_amounts(39).amount := wlbi_rec.a39;
1567 g_wlbi_amounts(40).amount := wlbi_rec.a40;
1568
1569 g_wlbi_amounts(41).amount := wlbi_rec.a41;
1570 g_wlbi_amounts(42).amount := wlbi_rec.a42;
1571 g_wlbi_amounts(43).amount := wlbi_rec.a43;
1572 g_wlbi_amounts(44).amount := wlbi_rec.a44;
1573 g_wlbi_amounts(45).amount := wlbi_rec.a45;
1574 g_wlbi_amounts(46).amount := wlbi_rec.a46;
1575 g_wlbi_amounts(47).amount := wlbi_rec.a47;
1576 g_wlbi_amounts(48).amount := wlbi_rec.a48;
1577 g_wlbi_amounts(49).amount := wlbi_rec.a49;
1578 g_wlbi_amounts(50).amount := wlbi_rec.a50;
1579
1580 g_wlbi_amounts(51).amount := wlbi_rec.a51;
1581 g_wlbi_amounts(52).amount := wlbi_rec.a52;
1582 g_wlbi_amounts(53).amount := wlbi_rec.a53;
1583 g_wlbi_amounts(54).amount := wlbi_rec.a54;
1584 g_wlbi_amounts(55).amount := wlbi_rec.a55;
1585 g_wlbi_amounts(56).amount := wlbi_rec.a56;
1586 g_wlbi_amounts(57).amount := wlbi_rec.a57;
1587 g_wlbi_amounts(58).amount := wlbi_rec.a58;
1588 g_wlbi_amounts(59).amount := wlbi_rec.a59;
1589 g_wlbi_amounts(60).amount := wlbi_rec.a60;
1590
1591 g_wlbi_amounts(61).amount := wlbi_rec.a61;
1592 g_wlbi_amounts(62).amount := wlbi_rec.a62;
1593 g_wlbi_amounts(63).amount := wlbi_rec.a63;
1594 g_wlbi_amounts(64).amount := wlbi_rec.a64;
1595 g_wlbi_amounts(65).amount := wlbi_rec.a65;
1599 g_wlbi_amounts(69).amount := wlbi_rec.a69;
1596 g_wlbi_amounts(66).amount := wlbi_rec.a66;
1597 g_wlbi_amounts(67).amount := wlbi_rec.a67;
1598 g_wlbi_amounts(68).amount := wlbi_rec.a68;
1600 g_wlbi_amounts(70).amount := wlbi_rec.a70;
1601
1602 g_wlbi_amounts(71).amount := wlbi_rec.a71;
1603 g_wlbi_amounts(72).amount := wlbi_rec.a72;
1604 g_wlbi_amounts(73).amount := wlbi_rec.a73;
1605 g_wlbi_amounts(74).amount := wlbi_rec.a74;
1606 g_wlbi_amounts(75).amount := wlbi_rec.a75;
1607 g_wlbi_amounts(76).amount := wlbi_rec.a76;
1608 g_wlbi_amounts(77).amount := wlbi_rec.a77;
1609 g_wlbi_amounts(78).amount := wlbi_rec.a78;
1610 g_wlbi_amounts(79).amount := wlbi_rec.a79;
1611 g_wlbi_amounts(80).amount := wlbi_rec.a80;
1612
1613 g_wlbi_amounts(81).amount := wlbi_rec.a81;
1614 g_wlbi_amounts(82).amount := wlbi_rec.a82;
1615 g_wlbi_amounts(83).amount := wlbi_rec.a83;
1616 g_wlbi_amounts(84).amount := wlbi_rec.a84;
1617 g_wlbi_amounts(85).amount := wlbi_rec.a85;
1618 g_wlbi_amounts(86).amount := wlbi_rec.a86;
1619 g_wlbi_amounts(87).amount := wlbi_rec.a87;
1620 g_wlbi_amounts(88).amount := wlbi_rec.a88;
1621 g_wlbi_amounts(89).amount := wlbi_rec.a89;
1622 g_wlbi_amounts(90).amount := wlbi_rec.a90;
1623
1624 g_wlbi_amounts(91).amount := wlbi_rec.a91;
1625 g_wlbi_amounts(92).amount := wlbi_rec.a92;
1626 g_wlbi_amounts(93).amount := wlbi_rec.a93;
1627 g_wlbi_amounts(94).amount := wlbi_rec.a94;
1628 g_wlbi_amounts(95).amount := wlbi_rec.a95;
1629 g_wlbi_amounts(96).amount := wlbi_rec.a96;
1630 g_wlbi_amounts(97).amount := wlbi_rec.a97;
1631 g_wlbi_amounts(98).amount := wlbi_rec.a98;
1632 g_wlbi_amounts(99).amount := wlbi_rec.a99;
1633 g_wlbi_amounts(100).amount := wlbi_rec.a100;
1634
1635 g_wlbi_amounts(101).amount := wlbi_rec.a101;
1636 g_wlbi_amounts(102).amount := wlbi_rec.a102;
1637 g_wlbi_amounts(103).amount := wlbi_rec.a103;
1638 g_wlbi_amounts(104).amount := wlbi_rec.a104;
1639 g_wlbi_amounts(105).amount := wlbi_rec.a105;
1640 g_wlbi_amounts(106).amount := wlbi_rec.a106;
1641 g_wlbi_amounts(107).amount := wlbi_rec.a107;
1642 g_wlbi_amounts(108).amount := wlbi_rec.a108;
1643 g_wlbi_amounts(109).amount := wlbi_rec.a109;
1644 g_wlbi_amounts(110).amount := wlbi_rec.a110;
1645
1646 g_wlbi_amounts(111).amount := wlbi_rec.a111;
1647 g_wlbi_amounts(112).amount := wlbi_rec.a112;
1648 g_wlbi_amounts(113).amount := wlbi_rec.a113;
1649 g_wlbi_amounts(114).amount := wlbi_rec.a114;
1650 g_wlbi_amounts(115).amount := wlbi_rec.a115;
1651 g_wlbi_amounts(116).amount := wlbi_rec.a116;
1652 g_wlbi_amounts(117).amount := wlbi_rec.a117;
1653 g_wlbi_amounts(118).amount := wlbi_rec.a118;
1654 g_wlbi_amounts(119).amount := wlbi_rec.a119;
1655 g_wlbi_amounts(120).amount := wlbi_rec.a120;
1656
1657 g_wlbi_amounts(121).amount := wlbi_rec.a121;
1658 g_wlbi_amounts(122).amount := wlbi_rec.a122;
1659 g_wlbi_amounts(123).amount := wlbi_rec.a123;
1660 g_wlbi_amounts(124).amount := wlbi_rec.a124;
1661 g_wlbi_amounts(125).amount := wlbi_rec.a125;
1662 g_wlbi_amounts(126).amount := wlbi_rec.a126;
1663 g_wlbi_amounts(127).amount := wlbi_rec.a127;
1664 g_wlbi_amounts(128).amount := wlbi_rec.a128;
1665 g_wlbi_amounts(129).amount := wlbi_rec.a129;
1666 g_wlbi_amounts(130).amount := wlbi_rec.a130;
1667
1668 g_wlbi_amounts(131).amount := wlbi_rec.a131;
1669 g_wlbi_amounts(132).amount := wlbi_rec.a132;
1670 g_wlbi_amounts(133).amount := wlbi_rec.a133;
1671 g_wlbi_amounts(134).amount := wlbi_rec.a134;
1672 g_wlbi_amounts(135).amount := wlbi_rec.a135;
1673 g_wlbi_amounts(136).amount := wlbi_rec.a136;
1674 g_wlbi_amounts(137).amount := wlbi_rec.a137;
1675 g_wlbi_amounts(138).amount := wlbi_rec.a138;
1676 g_wlbi_amounts(139).amount := wlbi_rec.a139;
1677 g_wlbi_amounts(140).amount := wlbi_rec.a140;
1678
1679 g_wlbi_amounts(141).amount := wlbi_rec.a141;
1680 g_wlbi_amounts(142).amount := wlbi_rec.a142;
1681 g_wlbi_amounts(143).amount := wlbi_rec.a143;
1682 g_wlbi_amounts(144).amount := wlbi_rec.a144;
1683 g_wlbi_amounts(145).amount := wlbi_rec.a145;
1684 g_wlbi_amounts(146).amount := wlbi_rec.a146;
1685 g_wlbi_amounts(147).amount := wlbi_rec.a147;
1686 g_wlbi_amounts(148).amount := wlbi_rec.a148;
1687 g_wlbi_amounts(149).amount := wlbi_rec.a149;
1688 g_wlbi_amounts(150).amount := wlbi_rec.a150;
1689
1690 g_wlbi_amounts(151).amount := wlbi_rec.a151;
1691 g_wlbi_amounts(152).amount := wlbi_rec.a152;
1692 g_wlbi_amounts(153).amount := wlbi_rec.a153;
1693 g_wlbi_amounts(154).amount := wlbi_rec.a154;
1694 g_wlbi_amounts(155).amount := wlbi_rec.a155;
1695 g_wlbi_amounts(156).amount := wlbi_rec.a156;
1696 g_wlbi_amounts(157).amount := wlbi_rec.a157;
1697 g_wlbi_amounts(158).amount := wlbi_rec.a158;
1698 g_wlbi_amounts(159).amount := wlbi_rec.a159;
1699 g_wlbi_amounts(160).amount := wlbi_rec.a160;
1700
1701 g_wlbi_amounts(161).amount := wlbi_rec.a161;
1702 g_wlbi_amounts(162).amount := wlbi_rec.a162;
1703 g_wlbi_amounts(163).amount := wlbi_rec.a163;
1704 g_wlbi_amounts(164).amount := wlbi_rec.a164;
1705 g_wlbi_amounts(165).amount := wlbi_rec.a165;
1706 g_wlbi_amounts(166).amount := wlbi_rec.a166;
1707 g_wlbi_amounts(167).amount := wlbi_rec.a167;
1708 g_wlbi_amounts(168).amount := wlbi_rec.a168;
1709
1713 l_spid := wlbi_rec.service_package_id;
1710 l_pay_element_id := wlbi_rec.pay_element_id;
1711 l_position_line_id := wlbi_rec.position_line_id;
1712 l_ccid := wlbi_rec.code_combination_id;
1714 l_spname := wlbi_rec.service_package_name;
1715 l_concatenated_account := wlbi_rec.concatenated_account;
1716 l_salary_account_line := wlbi_rec.salary_account_line;
1717
1718
1719
1720 -- Get the Element Set ID - Required for both old and new accounts
1721 found_element_set := FALSE;
1722 Get_Element_Set_ID
1723 (
1724 p_pay_element_id => l_pay_element_id,
1725 p_position_line_id => l_position_line_id,
1726 p_element_set_id => l_element_set_id,
1727 p_found_element_set => found_element_set
1728 );
1729
1730 IF not found_element_set THEN
1731 FND_MESSAGE.SET_NAME('PSB', 'PSB_INVALID_ELEMENT_SET');
1732 FND_MESSAGE.SET_TOKEN('PAY_ELEMENT_ID', l_pay_element_id );
1733 FND_MESSAGE.SET_TOKEN('POSITION_LINE_ID', l_position_line_id );
1734 FND_MSG_PUB.Add;
1735 RAISE FND_API.G_EXC_ERROR ;
1736 END IF;
1737
1738 old_acct := FALSE;
1739 new_acct := FALSE;
1740
1741 IF nvl(l_ccid,0) = 0 then
1742 new_acct := TRUE;
1743 ELSE
1744 old_acct := TRUE;
1745 END IF;
1746
1747 validated_acct := FALSE;
1748 found_pos_acct_line := FALSE;
1749 l_budget_group_id := g_budget_group_id;
1750
1751 l_create_follow_sal_acct := FALSE;
1752
1753
1754 -- Estimate years pl/sql table is indexed by estimate year id
1755 l_year_index := g_estimate_years.FIRST;
1756 WHILE l_year_index IS NOT NULL
1757 LOOP
1758 -- Move the values to Update API's Input PL/SQL table
1759 l_period_index := 0;
1760 -- Initialize the table
1761 FOR l_index in 1..PSB_WS_ACCT1.G_MAX_NUM_AMOUNTS LOOP
1762 l_period_amounts(l_index) := NULL;
1763 END LOOP;
1764
1765 l_period_start_column := g_estimate_years(l_year_index).period_start_column;
1766 l_period_end_column := g_estimate_years(l_year_index).period_end_column;
1767
1768 l_year_total_column := g_estimate_years(l_year_index).total_column;
1769 l_year_amount := nvl(g_wlbi_amounts(l_year_total_column).amount,0);
1770
1771 FOR l_wlbi_index IN l_period_start_column .. l_period_end_column
1772 LOOP
1773 l_period_index := l_period_index +1 ;
1774 l_period_amounts(l_period_index) := nvl(g_wlbi_amounts(l_wlbi_index).amount ,0);
1775 l_disp_amt := nvl(g_wlbi_amounts(l_wlbi_index).amount ,0);
1776
1777 END LOOP;
1778
1779 -----
1780 found_pos_acct_line := FALSE;
1781
1782 IF old_acct THEN
1783 -- Find out Account Line Id based on key Ids
1784 Get_Pos_Account_Line
1785 (
1786 p_worksheet_id => g_worksheet_id,
1787 p_ccid => l_ccid,
1788 p_spid => l_spid,
1789 p_position_line_id => l_position_line_id,
1790 p_budget_year_id => l_year_index,
1791 p_element_set_id => l_element_set_id,
1792 p_account_line_id => l_account_line_id,
1793 p_found_pos_acct_line => found_pos_acct_line
1794 );
1795
1796 END IF; --old acct
1797
1798 IF old_acct and found_pos_acct_line THEN
1799
1800 PSB_WS_ACCT_PVT.Create_Account_Dist
1801 (
1802 p_api_version => 1.0,
1803 p_init_msg_list => FND_API.G_FALSE,
1804 p_commit => FND_API.G_FALSE,
1805 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1806 p_return_status => l_return_status,
1807 p_msg_count => l_msg_count,
1808 p_msg_data => l_msg_data,
1809 --
1810 p_worksheet_id => g_worksheet_id,
1811 p_account_line_id => l_account_line_id,
1812 p_service_package_id => l_spid,
1813 p_ytd_amount => l_year_amount,
1814 --
1815 p_period_amount => l_period_amounts
1816 );
1817
1818 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1819 RAISE FND_API.G_EXC_ERROR ;
1820 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1821 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1822 END IF;
1823
1824 IF ( l_salary_account_line = 'Y') THEN
1825 l_create_follow_sal_acct := TRUE;
1826 END IF;
1827
1828 ELSIF (new_acct or ( old_acct and (not found_pos_acct_line) )
1829 and l_year_amount > 0 ) THEN
1830 --Validate the Account
1831 IF not validated_acct THEN
1832 PSB_VALIDATE_ACCT_PVT.Validate_Account
1833 (
1834 p_api_version => 1.0,
1835 p_init_msg_list => FND_API.G_FALSE,
1836 p_commit => FND_API.G_FALSE,
1837 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1838 p_return_status => l_return_status,
1839 p_msg_count => l_msg_count,
1840 p_msg_data => l_msg_data,
1841 --
1842 p_parent_budget_group_id => g_budget_group_id,
1843 p_startdate_pp => g_pp_start_date,
1844 p_enddate_cy => g_cy_end_date,
1845 p_set_of_books_id => g_set_of_books_id,
1846 p_flex_code => g_chart_of_accounts_id,
1850 --
1847 p_create_budget_account => FND_API.G_TRUE,
1848 --
1849 p_concatenated_segments => l_concatenated_account,
1851 p_worksheet_id => g_global_worksheet_id,
1852 p_in_ccid => FND_API.G_MISS_NUM,
1853 p_out_ccid => va_ccid,
1854 p_budget_group_id => va_budget_group_id
1855 );
1856
1857 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1858 validated_acct := TRUE;
1859 l_budget_group_id := va_budget_group_id;
1860 ELSE
1861 l_validation_status := FND_API.G_RET_STS_ERROR;
1862 FND_MESSAGE.SET_NAME('PSB', 'PSB_INVALID_CC_SEG');
1863 FND_MESSAGE.SET_TOKEN('CC', l_concatenated_account );
1864 FND_MSG_PUB.Add;
1865 RAISE FND_API.G_EXC_ERROR ;
1866 END IF;
1867
1868
1869 END IF; -- if not validated even once for the record
1870
1871 PSB_WS_ACCT_PVT.Create_Account_Dist
1872 (
1873 p_api_version => 1.0,
1874 p_init_msg_list => FND_API.G_FALSE,
1875 p_commit => FND_API.G_FALSE,
1876 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1877 p_return_status => l_return_status,
1878 p_msg_count => l_msg_count,
1879 p_msg_data => l_msg_data,
1880 --
1881 p_account_line_id => ca_account_line_id,
1882 p_worksheet_id => g_worksheet_id,
1883 p_map_accounts => TRUE,
1884 p_budget_year_id => l_year_index,
1885 p_budget_group_id => l_budget_group_id,
1886 p_flex_code => g_chart_of_accounts_id,
1887 p_concatenated_segments => l_concatenated_account,
1888 p_currency_code => g_currency_code,
1889 p_balance_type => 'E', -- Always Estimate
1890 p_ytd_amount => l_year_amount,
1891 p_distribute_flag => FND_API.G_FALSE,
1892 p_period_amount => l_period_amounts,
1893 p_service_package_id => l_spid,
1894 p_position_line_id => l_position_line_id,
1895 p_element_set_id => l_element_set_id,
1896 p_salary_account_line => l_salary_account_line
1897
1898 );
1899
1900 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1901 RAISE FND_API.G_EXC_ERROR ;
1902 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1903 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1904 END IF;
1905
1906 IF ( l_salary_account_line = 'Y') AND
1907 ( nvl(ca_account_line_id,0) <> 0 ) THEN
1908 l_create_follow_sal_acct := TRUE;
1909 END IF;
1910
1911
1912 END IF; --old_acct or (new_acct and valid_acct)
1913 l_year_index:= g_estimate_years.NEXT(l_year_index);
1914 END LOOP; --for each estimate year
1915
1916 -- Call this API only once per record
1917 IF l_create_follow_sal_acct THEN
1918
1919 PSB_WS_POSITION_RFS_PVT.Redistribute_Follow_Salary
1920 (
1921 p_api_version => 1.0,
1922 p_init_msg_list => FND_API.G_FALSE,
1923 p_commit => FND_API.G_FALSE,
1924 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1925 p_return_status => l_return_status,
1926 p_msg_count => l_msg_count,
1927 p_msg_data => l_msg_data,
1928 --
1929 p_worksheet_id => g_worksheet_id,
1930 p_position_line_id => l_position_line_id,
1931 p_service_package_id => l_spid,
1932 p_stage_set_id => g_stage_set_id
1933 );
1934
1935 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1936 RAISE FND_API.G_EXC_ERROR ;
1937 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1938 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1939 END IF;
1940
1941 END IF; --create follow salary acct(called both on insert and update)
1942
1943 EXCEPTION -- To handle exceptions at record level and continue with the rest
1944 WHEN FND_API.G_EXC_ERROR then
1945 l_validation_status := FND_API.G_RET_STS_ERROR;
1946 END;
1947 END LOOP; -- for wlbi rec
1948
1949 IF l_validation_status = FND_API.G_RET_STS_ERROR THEN
1950 p_return_status := FND_API.G_RET_STS_ERROR;
1951 ELSE
1952 p_return_status := FND_API.G_RET_STS_SUCCESS;
1953 END IF;
1954
1955 EXCEPTION
1956
1957 when FND_API.G_EXC_UNEXPECTED_ERROR then
1958 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1959 when OTHERS then
1960 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1961
1962 END Update_Distributions;
1963
1964
1965 PROCEDURE Get_Element_Set_ID
1966 (
1967 p_pay_element_id IN NUMBER,
1968 p_position_line_id IN NUMBER,
1969 p_element_set_id OUT NOCOPY NUMBER,
1970 p_found_element_set OUT NOCOPY BOOLEAN
1971 )
1972 IS
1973 cursor elem_line_cur is
1974 SELECT element_set_id
1975 FROM psb_ws_element_lines
1976 WHERE position_line_id = p_position_line_id
1977 AND pay_element_id = p_pay_element_id
1978 AND end_stage_seq is null;
1979
1980 l_element_set_id NUMBER;
1981 BEGIN
1982 p_found_element_set := FALSE;
1983 OPEN elem_line_cur;
1987 p_found_element_set := TRUE;
1984 FETCH elem_line_cur INTO l_element_set_id;
1985 IF elem_line_cur%FOUND THEN
1986 p_element_set_id := l_element_set_id;
1988 END IF;
1989 CLOSE elem_line_cur;
1990
1991 END Get_Element_Set_ID;
1992
1993 -- Find out Account Line Id based on key Ids
1994 PROCEDURE Get_Pos_Account_Line
1995 (
1996 p_worksheet_id IN NUMBER,
1997 p_ccid IN NUMBER,
1998 p_spid IN NUMBER,
1999 p_position_line_id IN NUMBER,
2000 p_budget_year_id IN NUMBER,
2001 p_element_set_id IN NUMBER,
2002 p_account_line_id OUT NOCOPY NUMBER,
2003 p_found_pos_acct_line OUT NOCOPY BOOLEAN
2004 )
2005 IS
2006
2007
2008 cursor wal_cur is
2009 SELECT wal.account_line_id
2010 FROM psb_ws_account_lines wal,
2011 psb_ws_lines wl
2012 WHERE wl.worksheet_id = p_worksheet_id
2013 AND wl.account_line_id = wal.account_line_id
2014 AND wal.position_line_id = p_position_line_id
2015 AND wal.code_combination_id = p_ccid
2016 AND wal.service_package_id = p_spid
2017 AND wal.budget_year_id = p_budget_year_id
2018 AND wal.end_stage_seq is null
2019 AND wal.element_set_id = p_element_set_id;
2020
2021 Recinfo wal_cur%ROWTYPE;
2022
2023 BEGIN
2024 -- First get Element Set Id based on the Pay element id
2025 p_found_pos_acct_line := FALSE;
2026 p_account_line_id := 0;
2027 OPEN wal_cur;
2028 FETCH wal_cur INTO Recinfo;
2029 IF wal_cur%FOUND THEN
2030 p_found_pos_acct_line := TRUE;
2031 p_account_line_id := Recinfo.account_line_id;
2032 END IF;
2033 CLOSE wal_cur;
2034 END Get_Pos_Account_Line;
2035
2036
2037 -- Cache position data for the calendar period
2038 PROCEDURE Cache_Position_Data
2039 (
2040 p_return_status OUT NOCOPY VARCHAR2,
2041 p_position_line_id IN NUMBER,
2042 p_position_id IN NUMBER,
2043 p_start_date IN DATE,
2044 p_end_date IN DATE
2045 )
2046 IS
2047
2048 l_return_status VARCHAR2(1);
2049 l_position_id NUMBER;
2050 l_start_date DATE;
2051 l_end_date DATE;
2052
2053 cursor c_Positions is
2054 select a.position_id,
2055 a.name,
2056 a.effective_start_date,
2057 a.effective_end_date
2058 from PSB_POSITIONS a,
2059 PSB_WS_POSITION_LINES b
2060 where a.position_id = b.position_id
2061 and b.position_line_id = p_position_line_id;
2062
2063 cursor c_Element_Assignments is
2064 select worksheet_id,
2065 pay_element_id,
2066 pay_element_option_id,
2067 pay_basis,
2068 element_value_type,
2069 element_value,
2070 effective_start_date,
2071 effective_end_date
2072 from PSB_POSITION_ASSIGNMENTS
2073 where (worksheet_id is null or worksheet_id = g_global_worksheet_id)
2074 and currency_code = g_currency_code
2075 and assignment_type = 'ELEMENT'
2076 and (((effective_start_date <= l_end_date)
2077 and (effective_end_date is null))
2078 or ((effective_start_date between l_start_date and l_end_date)
2079 or (effective_end_date between l_start_date and l_end_date)
2080 or ((effective_start_date < l_start_date)
2081 and (effective_end_date > l_end_date))))
2082 and position_id = l_position_id
2083 order by effective_start_date,
2084 effective_end_date,
2085 element_value desc;
2086
2087 cursor c_Element_Rates is
2088 select a.worksheet_id,
2089 a.pay_element_id,
2090 a.pay_element_option_id,
2091 a.pay_basis,
2092 a.element_value_type,
2093 a.element_value,
2094 a.formula_id,
2095 a.effective_start_date,
2096 a.effective_end_date
2097 from PSB_PAY_ELEMENT_RATES a,
2098 PSB_PAY_ELEMENTS b
2099 where (a.worksheet_id is null or a.worksheet_id = g_global_worksheet_id)
2100 and a.currency_code = g_currency_code
2101 and exists
2102 (select 1
2103 from PSB_POSITION_ASSIGNMENTS c
2104 where nvl(c.pay_element_option_id, FND_API.G_MISS_NUM) = nvl(a.pay_element_option_id, FND_API.G_MISS_NUM)
2105 and (c.worksheet_id is null or c.worksheet_id = g_global_worksheet_id)
2106 and c.currency_code = g_currency_code
2107 and (((c.effective_start_date <= l_end_date)
2108 and (c.effective_end_date is null))
2109 or ((c.effective_start_date between l_start_date and l_end_date)
2110 or (c.effective_end_date between l_start_date and l_end_date)
2111 or ((c.effective_start_date < l_start_date)
2112 and (c.effective_end_date > l_end_date))))
2113 and c.pay_element_id = a.pay_element_id
2114 and c.position_id = l_position_id)
2115 and (((a.effective_start_date <= l_end_date)
2116 and (a.effective_end_date is null))
2117 or ((a.effective_start_date between l_start_date and l_end_date)
2118 or (a.effective_end_date between l_start_date and l_end_date)
2119 or ((a.effective_start_date < l_start_date)
2120 and (a.effective_end_date > l_end_date))))
2121 and a.pay_element_id = b.pay_element_id
2122 and b.business_group_id = g_business_group_id
2123 and b.data_extract_id = g_data_extract_id
2124 order by a.worksheet_id,
2125 a.effective_start_date,
2129 cursor c_Attribute_Assignments is
2126 a.effective_end_date,
2127 a.element_value desc;
2128
2130 select worksheet_id,
2131 effective_start_date,
2132 effective_end_date,
2133 attribute_id,
2134 -- Fixed Bug # 3683644
2135 FND_NUMBER.canonical_to_number(attribute_value) attribute_value
2136 from PSB_POSITION_ASSIGNMENTS
2137 where attribute_id = PSB_WS_POS1.g_default_wklyhrs_id
2138 and (worksheet_id is null or worksheet_id = g_global_worksheet_id)
2139 and assignment_type = 'ATTRIBUTE'
2140 and (((effective_start_date <= l_end_date)
2141 and (effective_end_date is null))
2142 or ((effective_start_date between l_start_date and l_end_date)
2143 or (effective_end_date between l_start_date and l_end_date)
2144 or ((effective_start_date < l_start_date)
2145 and (effective_end_date > l_end_date))))
2146 and position_id = l_position_id
2147 order by worksheet_id,
2148 effective_start_date,
2149 effective_end_date,
2150 FND_NUMBER.canonical_to_number(attribute_value) desc; -- Fixed Bug # 3683644
2151
2152 BEGIN
2153 l_position_id := p_position_id;
2154 l_start_date := p_start_date;
2155 l_end_date := p_end_date;
2156
2157 -- Initialize the Cache
2158 for l_init_index in 1..g_poselem_assignments.Count loop
2159 g_poselem_assignments(l_init_index).worksheet_id := null;
2160 g_poselem_assignments(l_init_index).start_date := null;
2161 g_poselem_assignments(l_init_index).end_date := null;
2162 g_poselem_assignments(l_init_index).pay_element_id := null;
2163 g_poselem_assignments(l_init_index).pay_element_option_id := null;
2164 g_poselem_assignments(l_init_index).pay_basis := null;
2165 g_poselem_assignments(l_init_index).element_value_type := null;
2166 g_poselem_assignments(l_init_index).element_value := null;
2167 end loop;
2168
2169 g_num_poselem_assignments := 0;
2170
2171 for l_init_index in 1..g_poselem_rates.Count loop
2172 g_poselem_rates(l_init_index).worksheet_id := null;
2173 g_poselem_rates(l_init_index).start_date := null;
2174 g_poselem_rates(l_init_index).end_date := null;
2175 g_poselem_rates(l_init_index).pay_element_id := null;
2176 g_poselem_rates(l_init_index).pay_element_option_id := null;
2177 g_poselem_rates(l_init_index).pay_basis := null;
2178 g_poselem_rates(l_init_index).element_value_type := null;
2179 g_poselem_rates(l_init_index).element_value := null;
2180 g_poselem_rates(l_init_index).formula_id := null;
2181 end loop;
2182
2183 g_num_poselem_rates := 0;
2184
2185 for l_init_index in 1..g_poswkh_assignments.Count loop
2186 g_poswkh_assignments(l_init_index).worksheet_id := null;
2187 g_poswkh_assignments(l_init_index).start_date := null;
2188 g_poswkh_assignments(l_init_index).end_date := null;
2189 g_poswkh_assignments(l_init_index).default_weekly_hours := null;
2190 end loop;
2191
2192 g_num_poswkh_assignments := 0;
2193
2194 PSB_WS_POS1.Cache_Named_Attribute_Values
2195 (p_return_status => l_return_status,
2196 p_worksheet_id => g_global_worksheet_id,
2197 p_data_extract_id => g_data_extract_id,
2198 p_position_id => l_position_id);
2199
2200
2201 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2202 raise FND_API.G_EXC_ERROR;
2203 end if;
2204
2205 for c_Element_Assignments_Rec in c_Element_Assignments loop
2206
2207 g_num_poselem_assignments := g_num_poselem_assignments + 1;
2208
2209 g_poselem_assignments(g_num_poselem_assignments).worksheet_id := c_Element_Assignments_Rec.worksheet_id;
2210 g_poselem_assignments(g_num_poselem_assignments).start_date := c_Element_Assignments_Rec.effective_start_date;
2211 g_poselem_assignments(g_num_poselem_assignments).end_date := c_Element_Assignments_Rec.effective_end_date;
2212 g_poselem_assignments(g_num_poselem_assignments).pay_element_id := c_Element_Assignments_Rec.pay_element_id;
2213 g_poselem_assignments(g_num_poselem_assignments).pay_element_option_id := c_Element_Assignments_Rec.pay_element_option_id;
2214 g_poselem_assignments(g_num_poselem_assignments).pay_basis := c_Element_Assignments_Rec.pay_basis;
2215 g_poselem_assignments(g_num_poselem_assignments).element_value_type := c_Element_Assignments_Rec.element_value_type;
2216 g_poselem_assignments(g_num_poselem_assignments).element_value := c_Element_Assignments_Rec.element_value;
2217
2218 end loop;
2219
2220 for c_Element_Rates_Rec in c_Element_Rates loop
2221
2222 g_num_poselem_rates := g_num_poselem_rates + 1;
2223
2224 g_poselem_rates(g_num_poselem_rates).worksheet_id := c_Element_Rates_Rec.worksheet_id;
2225 g_poselem_rates(g_num_poselem_rates).start_date := c_Element_Rates_Rec.effective_start_date;
2226 g_poselem_rates(g_num_poselem_rates).end_date := c_Element_Rates_Rec.effective_end_date;
2227 g_poselem_rates(g_num_poselem_rates).pay_element_id := c_Element_Rates_Rec.pay_element_id;
2228 g_poselem_rates(g_num_poselem_rates).pay_element_option_id := c_Element_Rates_Rec.pay_element_option_id;
2229 g_poselem_rates(g_num_poselem_rates).pay_basis := c_Element_Rates_Rec.pay_basis;
2230 g_poselem_rates(g_num_poselem_rates).element_value_type := c_Element_Rates_Rec.element_value_type;
2231 g_poselem_rates(g_num_poselem_rates).element_value := c_Element_Rates_Rec.element_value;
2232 g_poselem_rates(g_num_poselem_rates).formula_id := c_Element_Rates_Rec.formula_id;
2233
2234 end loop;
2235
2236
2240
2237 for c_Attributes_Rec in c_Attribute_Assignments loop
2238
2239 g_num_poswkh_assignments := g_num_poswkh_assignments + 1;
2241 g_poswkh_assignments(g_num_poswkh_assignments).worksheet_id := c_Attributes_Rec.worksheet_id;
2242 g_poswkh_assignments(g_num_poswkh_assignments).start_date := c_Attributes_Rec.effective_start_date;
2243 g_poswkh_assignments(g_num_poswkh_assignments).end_date := c_Attributes_Rec.effective_end_date;
2244 g_poswkh_assignments(g_num_poswkh_assignments).default_weekly_hours := c_Attributes_Rec.attribute_value;
2245
2246 end loop;
2247
2248 p_return_status := FND_API.G_RET_STS_SUCCESS;
2249 EXCEPTION
2250
2251 when FND_API.G_EXC_ERROR then
2252 p_return_status := FND_API.G_RET_STS_ERROR;
2253
2254 when FND_API.G_EXC_UNEXPECTED_ERROR then
2255 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2256
2257 when OTHERS then
2258 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2259
2260 END Cache_Position_Data;
2261
2262
2263 PROCEDURE Update_Assignments
2264 ( p_return_status OUT NOCOPY VARCHAR2,
2265 p_position_line_id IN NUMBER
2266 )
2267 IS
2268
2269 l_return_status VARCHAR2(1);
2270 l_wal_element_cost NUMBER;
2271 l_wlbi_element_cost NUMBER;
2272 i BINARY_INTEGER;
2273
2274 l_position_id NUMBER;
2275 l_position_name VARCHAR2(1000);
2276 l_position_start_date DATE;
2277 l_position_end_date DATE;
2278 l_start_date DATE;
2279 l_end_date DATE;
2280 l_pay_element_id NUMBER;
2281 l_element_set_id NUMBER;
2282 found_element_set BOOLEAN := FALSE;
2283 l_element_cost_update_reqd BOOLEAN := FALSE;
2284
2285 cursor c_Positions is
2286 select a.position_id,
2287 a.name,
2288 a.effective_start_date,
2289 a.effective_end_date
2290 from PSB_POSITIONS a,
2291 PSB_WS_POSITION_LINES b
2292 where a.position_id = b.position_id
2293 and b.position_line_id = p_position_line_id;
2294
2295 BEGIN
2296
2297 for c_Positions_Rec in c_Positions loop
2298 l_position_id := c_Positions_Rec.position_id;
2299 l_position_name := c_Positions_Rec.name;
2300 l_position_start_date := c_Positions_Rec.effective_start_date;
2301 l_position_end_date := c_Positions_Rec.effective_end_date;
2302 end loop;
2303
2304 -- this assumes the budget calendar is cached
2305 l_start_date := greatest(PSB_WS_ACCT1.g_startdate_cy, l_position_start_date);
2306 l_end_date := least(PSB_WS_ACCT1.g_end_est_date,
2307 nvl(l_position_end_date, PSB_WS_ACCT1.g_end_est_date));
2308
2309 -- Get the postion data in PL/SQL tables for the effective date
2310 Cache_Position_Data(p_return_status => l_return_status,
2311 p_position_line_id => p_position_line_id,
2312 p_position_id => l_position_id,
2313 p_start_date => l_start_date,
2314 p_end_date => l_end_date);
2315
2316
2317 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2318 raise FND_API.G_EXC_ERROR;
2319 end if;
2320
2321
2322 --Outer loop (element_rec) created to get rid of group by operation
2323 --which led to sort key too long Oracle error when
2324 --the used columns were increased from 168 to 192
2325
2326 FOR element_rec IN
2327 (
2328 SELECT distinct pay_element_id
2329 FROM psb_ws_line_balances_i
2330 WHERE position_line_id = p_position_line_id
2331 AND export_worksheet_type = 'P'
2332 AND export_id = g_export_id
2333 )
2334 LOOP
2335 l_pay_element_id := element_rec.pay_element_id;
2336
2337 -- Get the Element Set ID
2338 found_element_set := FALSE;
2339 Get_Element_Set_ID
2340 (
2341 p_pay_element_id => l_pay_element_id,
2342 p_position_line_id => p_position_line_id,
2343 p_element_set_id => l_element_set_id,
2344 p_found_element_set => found_element_set
2345 );
2346
2347 IF not found_element_set THEN
2348 FND_MESSAGE.SET_NAME('PSB', 'PSB_INVALID_ELEMENT_SET');
2349 FND_MESSAGE.SET_TOKEN('PAY_ELEMENT_ID', l_pay_element_id );
2350 FND_MESSAGE.SET_TOKEN('POSITION_LINE_ID', p_position_line_id );
2351 FND_MSG_PUB.Add;
2352 RAISE FND_API.G_EXC_ERROR ;
2353 END IF;
2354
2355 -- For each pay element
2356 FOR element_total_rec IN
2357 (
2358 SELECT
2359 SUM(AMOUNT1) a1,SUM(AMOUNT2) a2,SUM(AMOUNT3) a3,SUM(AMOUNT4) a4,SUM(AMOUNT5) a5,
2360 SUM(AMOUNT6) a6,SUM(AMOUNT7) a7,SUM(AMOUNT8) a8,SUM(AMOUNT9) a9,SUM(AMOUNT10) a10,
2361 SUM(AMOUNT11) a11,SUM(AMOUNT12) a12,SUM(AMOUNT13) a13,SUM(AMOUNT14) a14,SUM(AMOUNT15) a15,
2362 SUM(AMOUNT16) a16,SUM(AMOUNT17) a17,SUM(AMOUNT18) a18,SUM(AMOUNT19) a19,SUM(AMOUNT20) a20,
2363 SUM(AMOUNT21) a21,SUM(AMOUNT22) a22,SUM(AMOUNT23) a23,SUM(AMOUNT24) a24,SUM(AMOUNT25) a25,
2364 SUM(AMOUNT26) a26,SUM(AMOUNT27) a27,SUM(AMOUNT28) a28,SUM(AMOUNT29) a29,SUM(AMOUNT30) a30,
2365 SUM(AMOUNT31) a31,SUM(AMOUNT32) a32,SUM(AMOUNT33) a33,SUM(AMOUNT34) a34,SUM(AMOUNT35) a35,
2366 SUM(AMOUNT36) a36,SUM(AMOUNT37) a37,SUM(AMOUNT38) a38,SUM(AMOUNT39) a39,SUM(AMOUNT40) a40,
2367 SUM(AMOUNT41) a41,SUM(AMOUNT42) a42,SUM(AMOUNT43) a43,SUM(AMOUNT44) a44,SUM(AMOUNT45) a45,
2371 SUM(AMOUNT61) a61,SUM(AMOUNT62) a62,SUM(AMOUNT63) a63,SUM(AMOUNT64) a64,SUM(AMOUNT65) a65,
2368 SUM(AMOUNT46) a46,SUM(AMOUNT47) a47,SUM(AMOUNT48) a48,SUM(AMOUNT49) a49,SUM(AMOUNT50) a50,
2369 SUM(AMOUNT51) a51,SUM(AMOUNT52) a52,SUM(AMOUNT53) a53,SUM(AMOUNT54) a54,SUM(AMOUNT55) a55,
2370 SUM(AMOUNT56) a56,SUM(AMOUNT57) a57,SUM(AMOUNT58) a58,SUM(AMOUNT59) a59,SUM(AMOUNT60) a60,
2372 SUM(AMOUNT66) a66,SUM(AMOUNT67) a67,SUM(AMOUNT68) a68,SUM(AMOUNT69) a69,SUM(AMOUNT70) a70,
2373 SUM(AMOUNT71) a71,SUM(AMOUNT72) a72,SUM(AMOUNT73) a73,SUM(AMOUNT74) a74,SUM(AMOUNT75) a75,
2374 SUM(AMOUNT76) a76,SUM(AMOUNT77) a77,SUM(AMOUNT78) a78,SUM(AMOUNT79) a79,SUM(AMOUNT80) a80,
2375 SUM(AMOUNT81) a81,SUM(AMOUNT82) a82,SUM(AMOUNT83) a83,SUM(AMOUNT84) a84,SUM(AMOUNT85) a85,
2376 SUM(AMOUNT86) a86,SUM(AMOUNT87) a87,SUM(AMOUNT88) a88,SUM(AMOUNT89) a89,SUM(AMOUNT90) a90,
2377 SUM(AMOUNT91) a91,SUM(AMOUNT92) a92,SUM(AMOUNT93) a93,SUM(AMOUNT94) a94,SUM(AMOUNT95) a95,
2378 SUM(AMOUNT96) a96,SUM(AMOUNT97) a97,SUM(AMOUNT98) a98,SUM(AMOUNT99) a99,SUM(AMOUNT100) a100,
2379 SUM(AMOUNT101) a101,SUM(AMOUNT102) a102,SUM(AMOUNT103) a103,SUM(AMOUNT104) a104,SUM(AMOUNT105) a105,
2380 SUM(AMOUNT106) a106,SUM(AMOUNT107) a107,SUM(AMOUNT108) a108,SUM(AMOUNT109) a109,SUM(AMOUNT110) a110,
2381 SUM(AMOUNT111) a111,SUM(AMOUNT112) a112,SUM(AMOUNT113) a113,SUM(AMOUNT114) a114,SUM(AMOUNT115) a115,
2382 SUM(AMOUNT116) a116,SUM(AMOUNT117) a117,SUM(AMOUNT118) a118,SUM(AMOUNT119) a119,SUM(AMOUNT120) a120,
2383 SUM(AMOUNT121) a121,SUM(AMOUNT122) a122,SUM(AMOUNT123) a123,SUM(AMOUNT124) a124,SUM(AMOUNT125) a125,
2384 SUM(AMOUNT126) a126,SUM(AMOUNT127) a127,SUM(AMOUNT128) a128,SUM(AMOUNT129) a129,SUM(AMOUNT130) a130,
2385 SUM(AMOUNT131) a131,SUM(AMOUNT132) a132,SUM(AMOUNT133) a133,SUM(AMOUNT134) a134,SUM(AMOUNT135) a135,
2386 SUM(AMOUNT136) a136,SUM(AMOUNT137) a137,SUM(AMOUNT138) a138,SUM(AMOUNT139) a139,SUM(AMOUNT140) a140,
2387 SUM(AMOUNT141) a141,SUM(AMOUNT142) a142,SUM(AMOUNT143) a143,SUM(AMOUNT144) a144,SUM(AMOUNT145) a145,
2388 SUM(AMOUNT146) a146,SUM(AMOUNT147) a147,SUM(AMOUNT148) a148,SUM(AMOUNT149) a149,SUM(AMOUNT150) a150,
2389 SUM(AMOUNT151) a151,SUM(AMOUNT152) a152,SUM(AMOUNT153) a153,SUM(AMOUNT154) a154,SUM(AMOUNT155) a155,
2390 SUM(AMOUNT156) a156,SUM(AMOUNT157) a157,SUM(AMOUNT158) a158,SUM(AMOUNT159) a159,SUM(AMOUNT160) a160,
2391 SUM(AMOUNT161) a161,SUM(AMOUNT162) a162,SUM(AMOUNT163) a163,SUM(AMOUNT164) a164,SUM(AMOUNT165) a165,
2392 SUM(AMOUNT166) a166,SUM(AMOUNT167) a167,SUM(AMOUNT168) a168
2393 FROM psb_ws_line_balances_i
2394 WHERE position_line_id = p_position_line_id
2395 AND export_worksheet_type = 'P'
2396 AND export_id = g_export_id
2397 /* For Bug No. 2378123 : Start */
2398 -- AND ( percent_of_salary_flag = 'N' or value_type = 'P' )
2399 AND value_type = 'A'
2400 /* For Bug No. 2378123 : End */
2401 AND pay_element_id = l_pay_element_id )
2402 LOOP
2403
2404 -- Move the Amounts to PL/SQL table
2405 g_wlbi_amounts(1).amount := element_total_rec.a1;
2406 g_wlbi_amounts(2).amount := element_total_rec.a2;
2407 g_wlbi_amounts(3).amount := element_total_rec.a3;
2408 g_wlbi_amounts(4).amount := element_total_rec.a4;
2409 g_wlbi_amounts(5).amount := element_total_rec.a5;
2410 g_wlbi_amounts(6).amount := element_total_rec.a6;
2411 g_wlbi_amounts(7).amount := element_total_rec.a7;
2412 g_wlbi_amounts(8).amount := element_total_rec.a8;
2413 g_wlbi_amounts(9).amount := element_total_rec.a9;
2414 g_wlbi_amounts(10).amount := element_total_rec.a10;
2415
2416 g_wlbi_amounts(11).amount := element_total_rec.a11;
2417 g_wlbi_amounts(12).amount := element_total_rec.a12;
2418 g_wlbi_amounts(13).amount := element_total_rec.a13;
2419 g_wlbi_amounts(14).amount := element_total_rec.a14;
2420 g_wlbi_amounts(15).amount := element_total_rec.a15;
2421 g_wlbi_amounts(16).amount := element_total_rec.a16;
2422 g_wlbi_amounts(17).amount := element_total_rec.a17;
2423 g_wlbi_amounts(18).amount := element_total_rec.a18;
2424 g_wlbi_amounts(19).amount := element_total_rec.a19;
2425 g_wlbi_amounts(20).amount := element_total_rec.a20;
2426
2427 g_wlbi_amounts(21).amount := element_total_rec.a21;
2428 g_wlbi_amounts(22).amount := element_total_rec.a22;
2429 g_wlbi_amounts(23).amount := element_total_rec.a23;
2430 g_wlbi_amounts(24).amount := element_total_rec.a24;
2431 g_wlbi_amounts(25).amount := element_total_rec.a25;
2432 g_wlbi_amounts(26).amount := element_total_rec.a26;
2433 g_wlbi_amounts(27).amount := element_total_rec.a27;
2434 g_wlbi_amounts(28).amount := element_total_rec.a28;
2435 g_wlbi_amounts(29).amount := element_total_rec.a29;
2436 g_wlbi_amounts(30).amount := element_total_rec.a30;
2437
2438 g_wlbi_amounts(31).amount := element_total_rec.a31;
2439 g_wlbi_amounts(32).amount := element_total_rec.a32;
2440 g_wlbi_amounts(33).amount := element_total_rec.a33;
2441 g_wlbi_amounts(34).amount := element_total_rec.a34;
2442 g_wlbi_amounts(35).amount := element_total_rec.a35;
2443 g_wlbi_amounts(36).amount := element_total_rec.a36;
2444 g_wlbi_amounts(37).amount := element_total_rec.a37;
2445 g_wlbi_amounts(38).amount := element_total_rec.a38;
2446 g_wlbi_amounts(39).amount := element_total_rec.a39;
2447 g_wlbi_amounts(40).amount := element_total_rec.a40;
2448
2449 g_wlbi_amounts(41).amount := element_total_rec.a41;
2450 g_wlbi_amounts(42).amount := element_total_rec.a42;
2451 g_wlbi_amounts(43).amount := element_total_rec.a43;
2452 g_wlbi_amounts(44).amount := element_total_rec.a44;
2453 g_wlbi_amounts(45).amount := element_total_rec.a45;
2457 g_wlbi_amounts(49).amount := element_total_rec.a49;
2454 g_wlbi_amounts(46).amount := element_total_rec.a46;
2455 g_wlbi_amounts(47).amount := element_total_rec.a47;
2456 g_wlbi_amounts(48).amount := element_total_rec.a48;
2458 g_wlbi_amounts(50).amount := element_total_rec.a50;
2459
2460 g_wlbi_amounts(51).amount := element_total_rec.a51;
2461 g_wlbi_amounts(52).amount := element_total_rec.a52;
2462 g_wlbi_amounts(53).amount := element_total_rec.a53;
2463 g_wlbi_amounts(54).amount := element_total_rec.a54;
2464 g_wlbi_amounts(55).amount := element_total_rec.a55;
2465 g_wlbi_amounts(56).amount := element_total_rec.a56;
2466 g_wlbi_amounts(57).amount := element_total_rec.a57;
2467 g_wlbi_amounts(58).amount := element_total_rec.a58;
2468 g_wlbi_amounts(59).amount := element_total_rec.a59;
2469 g_wlbi_amounts(60).amount := element_total_rec.a60;
2470
2471 g_wlbi_amounts(61).amount := element_total_rec.a61;
2472 g_wlbi_amounts(62).amount := element_total_rec.a62;
2473 g_wlbi_amounts(63).amount := element_total_rec.a63;
2474 g_wlbi_amounts(64).amount := element_total_rec.a64;
2475 g_wlbi_amounts(65).amount := element_total_rec.a65;
2476 g_wlbi_amounts(66).amount := element_total_rec.a66;
2477 g_wlbi_amounts(67).amount := element_total_rec.a67;
2478 g_wlbi_amounts(68).amount := element_total_rec.a68;
2479 g_wlbi_amounts(69).amount := element_total_rec.a69;
2480 g_wlbi_amounts(70).amount := element_total_rec.a70;
2481
2482 g_wlbi_amounts(71).amount := element_total_rec.a71;
2483 g_wlbi_amounts(72).amount := element_total_rec.a72;
2484 g_wlbi_amounts(73).amount := element_total_rec.a73;
2485 g_wlbi_amounts(74).amount := element_total_rec.a74;
2486 g_wlbi_amounts(75).amount := element_total_rec.a75;
2487 g_wlbi_amounts(76).amount := element_total_rec.a76;
2488 g_wlbi_amounts(77).amount := element_total_rec.a77;
2489 g_wlbi_amounts(78).amount := element_total_rec.a78;
2490 g_wlbi_amounts(79).amount := element_total_rec.a79;
2491 g_wlbi_amounts(80).amount := element_total_rec.a80;
2492
2493 g_wlbi_amounts(81).amount := element_total_rec.a81;
2494 g_wlbi_amounts(82).amount := element_total_rec.a82;
2495 g_wlbi_amounts(83).amount := element_total_rec.a83;
2496 g_wlbi_amounts(84).amount := element_total_rec.a84;
2497 g_wlbi_amounts(85).amount := element_total_rec.a85;
2498 g_wlbi_amounts(86).amount := element_total_rec.a86;
2499 g_wlbi_amounts(87).amount := element_total_rec.a87;
2500 g_wlbi_amounts(88).amount := element_total_rec.a88;
2501 g_wlbi_amounts(89).amount := element_total_rec.a89;
2502 g_wlbi_amounts(90).amount := element_total_rec.a90;
2503
2504 g_wlbi_amounts(91).amount := element_total_rec.a91;
2505 g_wlbi_amounts(92).amount := element_total_rec.a92;
2506 g_wlbi_amounts(93).amount := element_total_rec.a93;
2507 g_wlbi_amounts(94).amount := element_total_rec.a94;
2508 g_wlbi_amounts(95).amount := element_total_rec.a95;
2509 g_wlbi_amounts(96).amount := element_total_rec.a96;
2510 g_wlbi_amounts(97).amount := element_total_rec.a97;
2511 g_wlbi_amounts(98).amount := element_total_rec.a98;
2512 g_wlbi_amounts(99).amount := element_total_rec.a99;
2513 g_wlbi_amounts(100).amount := element_total_rec.a100;
2514
2515 g_wlbi_amounts(101).amount := element_total_rec.a101;
2516 g_wlbi_amounts(102).amount := element_total_rec.a102;
2517 g_wlbi_amounts(103).amount := element_total_rec.a103;
2518 g_wlbi_amounts(104).amount := element_total_rec.a104;
2519 g_wlbi_amounts(105).amount := element_total_rec.a105;
2520 g_wlbi_amounts(106).amount := element_total_rec.a106;
2521 g_wlbi_amounts(107).amount := element_total_rec.a107;
2522 g_wlbi_amounts(108).amount := element_total_rec.a108;
2523 g_wlbi_amounts(109).amount := element_total_rec.a109;
2524 g_wlbi_amounts(110).amount := element_total_rec.a110;
2525
2526 g_wlbi_amounts(111).amount := element_total_rec.a111;
2527 g_wlbi_amounts(112).amount := element_total_rec.a112;
2528 g_wlbi_amounts(113).amount := element_total_rec.a113;
2529 g_wlbi_amounts(114).amount := element_total_rec.a114;
2530 g_wlbi_amounts(115).amount := element_total_rec.a115;
2531 g_wlbi_amounts(116).amount := element_total_rec.a116;
2532 g_wlbi_amounts(117).amount := element_total_rec.a117;
2533 g_wlbi_amounts(118).amount := element_total_rec.a118;
2534 g_wlbi_amounts(119).amount := element_total_rec.a119;
2535 g_wlbi_amounts(120).amount := element_total_rec.a120;
2536
2537 g_wlbi_amounts(121).amount := element_total_rec.a121;
2538 g_wlbi_amounts(122).amount := element_total_rec.a122;
2539 g_wlbi_amounts(123).amount := element_total_rec.a123;
2540 g_wlbi_amounts(124).amount := element_total_rec.a124;
2541 g_wlbi_amounts(125).amount := element_total_rec.a125;
2542 g_wlbi_amounts(126).amount := element_total_rec.a126;
2543 g_wlbi_amounts(127).amount := element_total_rec.a127;
2544 g_wlbi_amounts(128).amount := element_total_rec.a128;
2545 g_wlbi_amounts(129).amount := element_total_rec.a129;
2546 g_wlbi_amounts(130).amount := element_total_rec.a130;
2547
2548 g_wlbi_amounts(131).amount := element_total_rec.a131;
2549 g_wlbi_amounts(132).amount := element_total_rec.a132;
2550 g_wlbi_amounts(133).amount := element_total_rec.a133;
2551 g_wlbi_amounts(134).amount := element_total_rec.a134;
2552 g_wlbi_amounts(135).amount := element_total_rec.a135;
2556 g_wlbi_amounts(139).amount := element_total_rec.a139;
2553 g_wlbi_amounts(136).amount := element_total_rec.a136;
2554 g_wlbi_amounts(137).amount := element_total_rec.a137;
2555 g_wlbi_amounts(138).amount := element_total_rec.a138;
2557 g_wlbi_amounts(140).amount := element_total_rec.a140;
2558
2559 g_wlbi_amounts(141).amount := element_total_rec.a141;
2560 g_wlbi_amounts(142).amount := element_total_rec.a142;
2561 g_wlbi_amounts(143).amount := element_total_rec.a143;
2562 g_wlbi_amounts(144).amount := element_total_rec.a144;
2563 g_wlbi_amounts(145).amount := element_total_rec.a145;
2564 g_wlbi_amounts(146).amount := element_total_rec.a146;
2565 g_wlbi_amounts(147).amount := element_total_rec.a147;
2566 g_wlbi_amounts(148).amount := element_total_rec.a148;
2567 g_wlbi_amounts(149).amount := element_total_rec.a149;
2568 g_wlbi_amounts(150).amount := element_total_rec.a150;
2569
2570 g_wlbi_amounts(151).amount := element_total_rec.a151;
2571 g_wlbi_amounts(152).amount := element_total_rec.a152;
2572 g_wlbi_amounts(153).amount := element_total_rec.a153;
2573 g_wlbi_amounts(154).amount := element_total_rec.a154;
2574 g_wlbi_amounts(155).amount := element_total_rec.a155;
2575 g_wlbi_amounts(156).amount := element_total_rec.a156;
2576 g_wlbi_amounts(157).amount := element_total_rec.a157;
2577 g_wlbi_amounts(158).amount := element_total_rec.a158;
2578 g_wlbi_amounts(159).amount := element_total_rec.a159;
2579 g_wlbi_amounts(160).amount := element_total_rec.a160;
2580
2581 g_wlbi_amounts(161).amount := element_total_rec.a161;
2582 g_wlbi_amounts(162).amount := element_total_rec.a162;
2583 g_wlbi_amounts(163).amount := element_total_rec.a163;
2584 g_wlbi_amounts(164).amount := element_total_rec.a164;
2585 g_wlbi_amounts(165).amount := element_total_rec.a165;
2586 g_wlbi_amounts(166).amount := element_total_rec.a166;
2587 g_wlbi_amounts(167).amount := element_total_rec.a167;
2588 g_wlbi_amounts(168).amount := element_total_rec.a168;
2589
2590
2591 -- For each estimate get the element costs and check if there is
2592 -- any difference
2593
2594 -- Estimate years pl/sql table is indexed by estimate year id
2595
2596 l_element_cost_update_reqd := FALSE;
2597 i := g_estimate_years.FIRST;
2598 WHILE i IS NOT NULL
2599 LOOP
2600
2601 Get_WAL_Element_Cost
2602 (p_position_line_id => p_position_line_id,
2603 p_element_set_id => l_element_set_id,
2604 p_budget_year_id => i,
2605 p_wal_element_cost => l_wal_element_cost
2606 );
2607
2608 Get_WLBI_Element_Cost
2609 (p_budget_year_id => i,
2610 p_wlbi_element_cost => l_wlbi_element_cost
2611 );
2612 --debug('Bud year id :'||i);
2613 --debug('elsid:'||l_element_set_id);
2614 --debug('WALC :'||l_wal_element_cost||' WLBIC'||l_wlbi_element_cost);
2615
2616 /* For Bug No. 2461802 : Start */
2617 -- IF l_wal_element_cost <> l_wlbi_element_cost THEN
2618 IF ABS(round(l_wal_element_cost) - round(l_wlbi_element_cost)) > 1 THEN
2619 /* For Bug No. 2461802 : End */
2620
2621 -- Init Assignment Table
2622 for l_assignment_index in 1..g_assignment.COUNT loop
2623 g_assignment(l_assignment_index).period := null;
2624 g_assignment(l_assignment_index).new_amount := null;
2625 end loop;
2626
2627 -- Get the new assignments in a PL/SQL table
2628 Get_New_Assignments(p_budget_year_id => i); -- in a PL/SQL table
2629
2630 Get_FTE
2631 ( p_position_line_id => p_position_line_id,
2632 p_budget_year_id => i
2633 );
2634
2635 -- Call assignment API
2636 Change_Pos_Year_Assignments
2637 ( p_return_status => l_return_status,
2638 p_worksheet_id => g_worksheet_id,
2639 p_budget_calendar_id => g_budget_calendar_id,
2640 p_data_extract_id => g_data_extract_id,
2641 p_business_group_id => g_business_group_id,
2642 p_position_line_id => p_position_line_id,
2643 p_position_id => l_position_id,
2644 p_position_name => l_position_name,
2645 p_pay_element_id => element_rec.pay_element_id,
2646 p_amt_tolerance_value_type => g_amt_tolerance_value_type,
2647 p_amt_tolerance_value => g_amt_tolerance_value,
2648 p_pct_tolerance_value_type => g_pct_tolerance_value_type ,
2649 p_pct_tolerance_value => g_pct_tolerance_value,
2650 p_budget_year_id => i,
2651 p_assignments => g_assignment
2652 );
2653
2654
2655 -- Change_Position_Assignments; -- Assignment API
2656 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2657 raise FND_API.G_EXC_ERROR;
2658 end if;
2659
2660 l_element_cost_update_reqd := TRUE;
2661
2662 END IF;
2663
2664 i:= g_estimate_years.NEXT(i);
2665 END LOOP; --for each estimate year
2666
2667 --Update_Element Costs
2668 IF l_element_cost_update_reqd THEN
2669
2670 Change_Element_Cost
2671 ( p_return_status => l_return_status,
2672 p_position_line_id => p_position_line_id,
2673 p_pay_element_id => element_rec.pay_element_id,
2674 p_element_set_id => l_element_set_id
2675 );
2676 END IF;
2677
2678 END LOOP; --for each element total in a position
2682 EXCEPTION
2679 END LOOP; --for each element in a position (added later to avoid group by operation)
2680
2681 p_return_status := FND_API.G_RET_STS_SUCCESS;
2683
2684 when FND_API.G_EXC_ERROR then
2685 p_return_status := FND_API.G_RET_STS_ERROR;
2686
2687 when FND_API.G_EXC_UNEXPECTED_ERROR then
2688 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2689
2690 when OTHERS then
2691 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2692
2693 END Update_Assignments;
2694
2695
2696 PROCEDURE Change_Element_Cost
2697 ( p_return_status OUT NOCOPY VARCHAR2,
2698 p_position_line_id IN NUMBER,
2699 p_pay_element_id IN NUMBER,
2700 p_element_set_id IN NUMBER
2701 )
2702 IS
2703
2704 l_msg_count NUMBER;
2705 l_msg_data VARCHAR2(2000);
2706 l_return_status VARCHAR2(1);
2707 i BINARY_INTEGER;
2708 l_service_package_id NUMBER;
2709 l_wlbi_sp_element_cost NUMBER;
2710 l_found_element_line BOOLEAN;
2711 l_element_line_id NUMBER;
2712
2713 BEGIN
2714
2715 FOR sp_rec IN
2716 ( select distinct service_package_id
2717 from psb_ws_line_balances_i
2718 where export_id = g_export_id
2719 and export_worksheet_type = 'P'
2720 and position_line_id = p_position_line_id
2721 and pay_element_id = p_pay_element_id
2722 )
2723 LOOP
2724
2725
2726 l_service_package_id := sp_rec.service_package_id;
2727 FOR sp_element_cost_rec IN
2728 (
2729 SELECT
2730 SUM(AMOUNT1) a1,SUM(AMOUNT2) a2,SUM(AMOUNT3) a3,SUM(AMOUNT4) a4,SUM(AMOUNT5) a5,
2731 SUM(AMOUNT6) a6,SUM(AMOUNT7) a7,SUM(AMOUNT8) a8,SUM(AMOUNT9) a9,SUM(AMOUNT10) a10,
2732 SUM(AMOUNT11) a11,SUM(AMOUNT12) a12,SUM(AMOUNT13) a13,SUM(AMOUNT14) a14,SUM(AMOUNT15) a15,
2733 SUM(AMOUNT16) a16,SUM(AMOUNT17) a17,SUM(AMOUNT18) a18,SUM(AMOUNT19) a19,SUM(AMOUNT20) a20,
2734 SUM(AMOUNT21) a21,SUM(AMOUNT22) a22,SUM(AMOUNT23) a23,SUM(AMOUNT24) a24,SUM(AMOUNT25) a25,
2735 SUM(AMOUNT26) a26,SUM(AMOUNT27) a27,SUM(AMOUNT28) a28,SUM(AMOUNT29) a29,SUM(AMOUNT30) a30,
2736 SUM(AMOUNT31) a31,SUM(AMOUNT32) a32,SUM(AMOUNT33) a33,SUM(AMOUNT34) a34,SUM(AMOUNT35) a35,
2737 SUM(AMOUNT36) a36,SUM(AMOUNT37) a37,SUM(AMOUNT38) a38,SUM(AMOUNT39) a39,SUM(AMOUNT40) a40,
2738 SUM(AMOUNT41) a41,SUM(AMOUNT42) a42,SUM(AMOUNT43) a43,SUM(AMOUNT44) a44,SUM(AMOUNT45) a45,
2739 SUM(AMOUNT46) a46,SUM(AMOUNT47) a47,SUM(AMOUNT48) a48,SUM(AMOUNT49) a49,SUM(AMOUNT50) a50,
2740 SUM(AMOUNT51) a51,SUM(AMOUNT52) a52,SUM(AMOUNT53) a53,SUM(AMOUNT54) a54,SUM(AMOUNT55) a55,
2741 SUM(AMOUNT56) a56,SUM(AMOUNT57) a57,SUM(AMOUNT58) a58,SUM(AMOUNT59) a59,SUM(AMOUNT60) a60,
2742 SUM(AMOUNT61) a61,SUM(AMOUNT62) a62,SUM(AMOUNT63) a63,SUM(AMOUNT64) a64,SUM(AMOUNT65) a65,
2743 SUM(AMOUNT66) a66,SUM(AMOUNT67) a67,SUM(AMOUNT68) a68,SUM(AMOUNT69) a69,SUM(AMOUNT70) a70,
2744 SUM(AMOUNT71) a71,SUM(AMOUNT72) a72,SUM(AMOUNT73) a73,SUM(AMOUNT74) a74,SUM(AMOUNT75) a75,
2745 SUM(AMOUNT76) a76,SUM(AMOUNT77) a77,SUM(AMOUNT78) a78,SUM(AMOUNT79) a79,SUM(AMOUNT80) a80,
2746 SUM(AMOUNT81) a81,SUM(AMOUNT82) a82,SUM(AMOUNT83) a83,SUM(AMOUNT84) a84,SUM(AMOUNT85) a85,
2747 SUM(AMOUNT86) a86,SUM(AMOUNT87) a87,SUM(AMOUNT88) a88,SUM(AMOUNT89) a89,SUM(AMOUNT90) a90,
2748 SUM(AMOUNT91) a91,SUM(AMOUNT92) a92,SUM(AMOUNT93) a93,SUM(AMOUNT94) a94,SUM(AMOUNT95) a95,
2749 SUM(AMOUNT96) a96,SUM(AMOUNT97) a97,SUM(AMOUNT98) a98,SUM(AMOUNT99) a99,SUM(AMOUNT100) a100,
2750 SUM(AMOUNT101) a101,SUM(AMOUNT102) a102,SUM(AMOUNT103) a103,SUM(AMOUNT104) a104,SUM(AMOUNT105) a105,
2751 SUM(AMOUNT106) a106,SUM(AMOUNT107) a107,SUM(AMOUNT108) a108,SUM(AMOUNT109) a109,SUM(AMOUNT110) a110,
2752 SUM(AMOUNT111) a111,SUM(AMOUNT112) a112,SUM(AMOUNT113) a113,SUM(AMOUNT114) a114,SUM(AMOUNT115) a115,
2753 SUM(AMOUNT116) a116,SUM(AMOUNT117) a117,SUM(AMOUNT118) a118,SUM(AMOUNT119) a119,SUM(AMOUNT120) a120,
2754 SUM(AMOUNT121) a121,SUM(AMOUNT122) a122,SUM(AMOUNT123) a123,SUM(AMOUNT124) a124,SUM(AMOUNT125) a125,
2755 SUM(AMOUNT126) a126,SUM(AMOUNT127) a127,SUM(AMOUNT128) a128,SUM(AMOUNT129) a129,SUM(AMOUNT130) a130,
2756 SUM(AMOUNT131) a131,SUM(AMOUNT132) a132,SUM(AMOUNT133) a133,SUM(AMOUNT134) a134,SUM(AMOUNT135) a135,
2757 SUM(AMOUNT136) a136,SUM(AMOUNT137) a137,SUM(AMOUNT138) a138,SUM(AMOUNT139) a139,SUM(AMOUNT140) a140,
2758 SUM(AMOUNT141) a141,SUM(AMOUNT142) a142,SUM(AMOUNT143) a143,SUM(AMOUNT144) a144,SUM(AMOUNT145) a145,
2759 SUM(AMOUNT146) a146,SUM(AMOUNT147) a147,SUM(AMOUNT148) a148,SUM(AMOUNT149) a149,SUM(AMOUNT150) a150,
2760 SUM(AMOUNT151) a151,SUM(AMOUNT152) a152,SUM(AMOUNT153) a153,SUM(AMOUNT154) a154,SUM(AMOUNT155) a155,
2761 SUM(AMOUNT156) a156,SUM(AMOUNT157) a157,SUM(AMOUNT158) a158,SUM(AMOUNT159) a159,SUM(AMOUNT160) a160,
2762 SUM(AMOUNT161) a161,SUM(AMOUNT162) a162,SUM(AMOUNT163) a163,SUM(AMOUNT164) a164,SUM(AMOUNT165) a165,
2763 SUM(AMOUNT166) a166,SUM(AMOUNT167) a167,SUM(AMOUNT168) a168
2764 FROM psb_ws_line_balances_i
2765 WHERE export_id = g_export_id
2766 AND export_worksheet_type = 'P'
2767 AND position_line_id = p_position_line_id
2768 AND value_type = 'A'
2769 AND pay_element_id = p_pay_element_id
2770 AND service_package_id = l_service_package_id )
2771 LOOP
2772
2773 -- Move the Amounts to PL/SQL table
2774 g_wlbi_amounts(1).amount := sp_element_cost_rec.a1;
2775 g_wlbi_amounts(2).amount := sp_element_cost_rec.a2;
2776 g_wlbi_amounts(3).amount := sp_element_cost_rec.a3;
2777 g_wlbi_amounts(4).amount := sp_element_cost_rec.a4;
2778 g_wlbi_amounts(5).amount := sp_element_cost_rec.a5;
2779 g_wlbi_amounts(6).amount := sp_element_cost_rec.a6;
2783 g_wlbi_amounts(10).amount := sp_element_cost_rec.a10;
2780 g_wlbi_amounts(7).amount := sp_element_cost_rec.a7;
2781 g_wlbi_amounts(8).amount := sp_element_cost_rec.a8;
2782 g_wlbi_amounts(9).amount := sp_element_cost_rec.a9;
2784
2785 g_wlbi_amounts(11).amount := sp_element_cost_rec.a11;
2786 g_wlbi_amounts(12).amount := sp_element_cost_rec.a12;
2787 g_wlbi_amounts(13).amount := sp_element_cost_rec.a13;
2788 g_wlbi_amounts(14).amount := sp_element_cost_rec.a14;
2789 g_wlbi_amounts(15).amount := sp_element_cost_rec.a15;
2790 g_wlbi_amounts(16).amount := sp_element_cost_rec.a16;
2791 g_wlbi_amounts(17).amount := sp_element_cost_rec.a17;
2792 g_wlbi_amounts(18).amount := sp_element_cost_rec.a18;
2793 g_wlbi_amounts(19).amount := sp_element_cost_rec.a19;
2794 g_wlbi_amounts(20).amount := sp_element_cost_rec.a20;
2795
2796 g_wlbi_amounts(21).amount := sp_element_cost_rec.a21;
2797 g_wlbi_amounts(22).amount := sp_element_cost_rec.a22;
2798 g_wlbi_amounts(23).amount := sp_element_cost_rec.a23;
2799 g_wlbi_amounts(24).amount := sp_element_cost_rec.a24;
2800 g_wlbi_amounts(25).amount := sp_element_cost_rec.a25;
2801 g_wlbi_amounts(26).amount := sp_element_cost_rec.a26;
2802 g_wlbi_amounts(27).amount := sp_element_cost_rec.a27;
2803 g_wlbi_amounts(28).amount := sp_element_cost_rec.a28;
2804 g_wlbi_amounts(29).amount := sp_element_cost_rec.a29;
2805 g_wlbi_amounts(30).amount := sp_element_cost_rec.a30;
2806
2807 g_wlbi_amounts(31).amount := sp_element_cost_rec.a31;
2808 g_wlbi_amounts(32).amount := sp_element_cost_rec.a32;
2809 g_wlbi_amounts(33).amount := sp_element_cost_rec.a33;
2810 g_wlbi_amounts(34).amount := sp_element_cost_rec.a34;
2811 g_wlbi_amounts(35).amount := sp_element_cost_rec.a35;
2812 g_wlbi_amounts(36).amount := sp_element_cost_rec.a36;
2813 g_wlbi_amounts(37).amount := sp_element_cost_rec.a37;
2814 g_wlbi_amounts(38).amount := sp_element_cost_rec.a38;
2815 g_wlbi_amounts(39).amount := sp_element_cost_rec.a39;
2816 g_wlbi_amounts(40).amount := sp_element_cost_rec.a40;
2817
2818 g_wlbi_amounts(41).amount := sp_element_cost_rec.a41;
2819 g_wlbi_amounts(42).amount := sp_element_cost_rec.a42;
2820 g_wlbi_amounts(43).amount := sp_element_cost_rec.a43;
2821 g_wlbi_amounts(44).amount := sp_element_cost_rec.a44;
2822 g_wlbi_amounts(45).amount := sp_element_cost_rec.a45;
2823 g_wlbi_amounts(46).amount := sp_element_cost_rec.a46;
2824 g_wlbi_amounts(47).amount := sp_element_cost_rec.a47;
2825 g_wlbi_amounts(48).amount := sp_element_cost_rec.a48;
2826 g_wlbi_amounts(49).amount := sp_element_cost_rec.a49;
2827 g_wlbi_amounts(50).amount := sp_element_cost_rec.a50;
2828
2829 g_wlbi_amounts(51).amount := sp_element_cost_rec.a51;
2830 g_wlbi_amounts(52).amount := sp_element_cost_rec.a52;
2831 g_wlbi_amounts(53).amount := sp_element_cost_rec.a53;
2832 g_wlbi_amounts(54).amount := sp_element_cost_rec.a54;
2833 g_wlbi_amounts(55).amount := sp_element_cost_rec.a55;
2834 g_wlbi_amounts(56).amount := sp_element_cost_rec.a56;
2835 g_wlbi_amounts(57).amount := sp_element_cost_rec.a57;
2836 g_wlbi_amounts(58).amount := sp_element_cost_rec.a58;
2837 g_wlbi_amounts(59).amount := sp_element_cost_rec.a59;
2838 g_wlbi_amounts(60).amount := sp_element_cost_rec.a60;
2839
2840 g_wlbi_amounts(61).amount := sp_element_cost_rec.a61;
2841 g_wlbi_amounts(62).amount := sp_element_cost_rec.a62;
2842 g_wlbi_amounts(63).amount := sp_element_cost_rec.a63;
2843 g_wlbi_amounts(64).amount := sp_element_cost_rec.a64;
2844 g_wlbi_amounts(65).amount := sp_element_cost_rec.a65;
2845 g_wlbi_amounts(66).amount := sp_element_cost_rec.a66;
2846 g_wlbi_amounts(67).amount := sp_element_cost_rec.a67;
2847 g_wlbi_amounts(68).amount := sp_element_cost_rec.a68;
2848 g_wlbi_amounts(69).amount := sp_element_cost_rec.a69;
2849 g_wlbi_amounts(70).amount := sp_element_cost_rec.a70;
2850
2851 g_wlbi_amounts(71).amount := sp_element_cost_rec.a71;
2852 g_wlbi_amounts(72).amount := sp_element_cost_rec.a72;
2853 g_wlbi_amounts(73).amount := sp_element_cost_rec.a73;
2854 g_wlbi_amounts(74).amount := sp_element_cost_rec.a74;
2855 g_wlbi_amounts(75).amount := sp_element_cost_rec.a75;
2856 g_wlbi_amounts(76).amount := sp_element_cost_rec.a76;
2857 g_wlbi_amounts(77).amount := sp_element_cost_rec.a77;
2858 g_wlbi_amounts(78).amount := sp_element_cost_rec.a78;
2859 g_wlbi_amounts(79).amount := sp_element_cost_rec.a79;
2860 g_wlbi_amounts(80).amount := sp_element_cost_rec.a80;
2861
2862 g_wlbi_amounts(81).amount := sp_element_cost_rec.a81;
2863 g_wlbi_amounts(82).amount := sp_element_cost_rec.a82;
2864 g_wlbi_amounts(83).amount := sp_element_cost_rec.a83;
2865 g_wlbi_amounts(84).amount := sp_element_cost_rec.a84;
2866 g_wlbi_amounts(85).amount := sp_element_cost_rec.a85;
2867 g_wlbi_amounts(86).amount := sp_element_cost_rec.a86;
2868 g_wlbi_amounts(87).amount := sp_element_cost_rec.a87;
2869 g_wlbi_amounts(88).amount := sp_element_cost_rec.a88;
2870 g_wlbi_amounts(89).amount := sp_element_cost_rec.a89;
2871 g_wlbi_amounts(90).amount := sp_element_cost_rec.a90;
2872
2873 g_wlbi_amounts(91).amount := sp_element_cost_rec.a91;
2874 g_wlbi_amounts(92).amount := sp_element_cost_rec.a92;
2875 g_wlbi_amounts(93).amount := sp_element_cost_rec.a93;
2879 g_wlbi_amounts(97).amount := sp_element_cost_rec.a97;
2876 g_wlbi_amounts(94).amount := sp_element_cost_rec.a94;
2877 g_wlbi_amounts(95).amount := sp_element_cost_rec.a95;
2878 g_wlbi_amounts(96).amount := sp_element_cost_rec.a96;
2880 g_wlbi_amounts(98).amount := sp_element_cost_rec.a98;
2881 g_wlbi_amounts(99).amount := sp_element_cost_rec.a99;
2882 g_wlbi_amounts(100).amount := sp_element_cost_rec.a100;
2883
2884 g_wlbi_amounts(101).amount := sp_element_cost_rec.a101;
2885 g_wlbi_amounts(102).amount := sp_element_cost_rec.a102;
2886 g_wlbi_amounts(103).amount := sp_element_cost_rec.a103;
2887 g_wlbi_amounts(104).amount := sp_element_cost_rec.a104;
2888 g_wlbi_amounts(105).amount := sp_element_cost_rec.a105;
2889 g_wlbi_amounts(106).amount := sp_element_cost_rec.a106;
2890 g_wlbi_amounts(107).amount := sp_element_cost_rec.a107;
2891 g_wlbi_amounts(108).amount := sp_element_cost_rec.a108;
2892 g_wlbi_amounts(109).amount := sp_element_cost_rec.a109;
2893 g_wlbi_amounts(110).amount := sp_element_cost_rec.a110;
2894
2895 g_wlbi_amounts(111).amount := sp_element_cost_rec.a111;
2896 g_wlbi_amounts(112).amount := sp_element_cost_rec.a112;
2897 g_wlbi_amounts(113).amount := sp_element_cost_rec.a113;
2898 g_wlbi_amounts(114).amount := sp_element_cost_rec.a114;
2899 g_wlbi_amounts(115).amount := sp_element_cost_rec.a115;
2900 g_wlbi_amounts(116).amount := sp_element_cost_rec.a116;
2901 g_wlbi_amounts(117).amount := sp_element_cost_rec.a117;
2902 g_wlbi_amounts(118).amount := sp_element_cost_rec.a118;
2903 g_wlbi_amounts(119).amount := sp_element_cost_rec.a119;
2904 g_wlbi_amounts(120).amount := sp_element_cost_rec.a120;
2905
2906 g_wlbi_amounts(121).amount := sp_element_cost_rec.a121;
2907 g_wlbi_amounts(122).amount := sp_element_cost_rec.a122;
2908 g_wlbi_amounts(123).amount := sp_element_cost_rec.a123;
2909 g_wlbi_amounts(124).amount := sp_element_cost_rec.a124;
2910 g_wlbi_amounts(125).amount := sp_element_cost_rec.a125;
2911 g_wlbi_amounts(126).amount := sp_element_cost_rec.a126;
2912 g_wlbi_amounts(127).amount := sp_element_cost_rec.a127;
2913 g_wlbi_amounts(128).amount := sp_element_cost_rec.a128;
2914 g_wlbi_amounts(129).amount := sp_element_cost_rec.a129;
2915 g_wlbi_amounts(130).amount := sp_element_cost_rec.a130;
2916
2917 g_wlbi_amounts(131).amount := sp_element_cost_rec.a131;
2918 g_wlbi_amounts(132).amount := sp_element_cost_rec.a132;
2919 g_wlbi_amounts(133).amount := sp_element_cost_rec.a133;
2920 g_wlbi_amounts(134).amount := sp_element_cost_rec.a134;
2921 g_wlbi_amounts(135).amount := sp_element_cost_rec.a135;
2922 g_wlbi_amounts(136).amount := sp_element_cost_rec.a136;
2923 g_wlbi_amounts(137).amount := sp_element_cost_rec.a137;
2924 g_wlbi_amounts(138).amount := sp_element_cost_rec.a138;
2925 g_wlbi_amounts(139).amount := sp_element_cost_rec.a139;
2926 g_wlbi_amounts(140).amount := sp_element_cost_rec.a140;
2927
2928 g_wlbi_amounts(141).amount := sp_element_cost_rec.a141;
2929 g_wlbi_amounts(142).amount := sp_element_cost_rec.a142;
2930 g_wlbi_amounts(143).amount := sp_element_cost_rec.a143;
2931 g_wlbi_amounts(144).amount := sp_element_cost_rec.a144;
2932 g_wlbi_amounts(145).amount := sp_element_cost_rec.a145;
2933 g_wlbi_amounts(146).amount := sp_element_cost_rec.a146;
2934 g_wlbi_amounts(147).amount := sp_element_cost_rec.a147;
2935 g_wlbi_amounts(148).amount := sp_element_cost_rec.a148;
2936 g_wlbi_amounts(149).amount := sp_element_cost_rec.a149;
2937 g_wlbi_amounts(150).amount := sp_element_cost_rec.a150;
2938
2939 g_wlbi_amounts(151).amount := sp_element_cost_rec.a151;
2940 g_wlbi_amounts(152).amount := sp_element_cost_rec.a152;
2941 g_wlbi_amounts(153).amount := sp_element_cost_rec.a153;
2942 g_wlbi_amounts(154).amount := sp_element_cost_rec.a154;
2943 g_wlbi_amounts(155).amount := sp_element_cost_rec.a155;
2944 g_wlbi_amounts(156).amount := sp_element_cost_rec.a156;
2945 g_wlbi_amounts(157).amount := sp_element_cost_rec.a157;
2946 g_wlbi_amounts(158).amount := sp_element_cost_rec.a158;
2947 g_wlbi_amounts(159).amount := sp_element_cost_rec.a159;
2948 g_wlbi_amounts(160).amount := sp_element_cost_rec.a160;
2949
2950 g_wlbi_amounts(161).amount := sp_element_cost_rec.a161;
2951 g_wlbi_amounts(162).amount := sp_element_cost_rec.a162;
2952 g_wlbi_amounts(163).amount := sp_element_cost_rec.a163;
2953 g_wlbi_amounts(164).amount := sp_element_cost_rec.a164;
2954 g_wlbi_amounts(165).amount := sp_element_cost_rec.a165;
2955 g_wlbi_amounts(166).amount := sp_element_cost_rec.a166;
2956 g_wlbi_amounts(167).amount := sp_element_cost_rec.a167;
2957 g_wlbi_amounts(168).amount := sp_element_cost_rec.a168;
2958
2959 -- Estimate years pl/sql table is indexed by estimate year id
2960 i := g_estimate_years.FIRST;
2961 WHILE i IS NOT NULL
2962 LOOP
2963 Get_WLBI_SP_Element_Cost
2964 (p_budget_year_id => i,
2965 p_wlbi_sp_element_cost => l_wlbi_sp_element_cost
2966 );
2967
2968 l_found_element_line := FALSE;
2969 Get_Element_Line_ID
2970 (p_position_line_id => p_position_line_id,
2971 p_budget_year_id => i,
2972 p_pay_element_id => p_pay_element_id,
2973 p_service_package_id => l_service_package_id,
2974 p_found_element_line => l_found_element_line,
2975 p_element_line_id => l_element_line_id
2976 );
2980
2977
2978
2979 IF l_found_element_line THEN
2981
2982 PSB_WS_POS_PVT.Create_Element_Lines
2983 (
2984 p_api_version => 1.0,
2985 p_init_msg_list => FND_API.G_FALSE,
2986 p_commit => FND_API.G_FALSE,
2987 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2988 p_return_status => l_return_status,
2989 p_msg_count => l_msg_count,
2990 p_msg_data => l_msg_data,
2991 --
2992 -- p_check_stages => l_check_stages,
2993 p_element_line_id => l_element_line_id,
2994 p_service_package_id => l_service_package_id,
2995 -- p_current_stage_seq => l_current_stage_seq,
2996 p_element_cost => l_wlbi_sp_element_cost
2997 );
2998
2999 --debug('elm_return_status'||l_return_status);
3000 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3001 RAISE FND_API.G_EXC_ERROR ;
3002 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3003 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3004 END IF;
3005
3006 ELSE
3007 -- Call Create Element Lines API
3008
3009 PSB_WS_POS_PVT.Create_Element_Lines
3010 (
3011 p_api_version => 1.0,
3012 p_init_msg_list => FND_API.G_FALSE,
3013 p_commit => FND_API.G_FALSE,
3014 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3015 p_return_status => l_return_status,
3016 p_msg_count => l_msg_count,
3017 p_msg_data => l_msg_data,
3018 --
3019 p_element_line_id => l_element_line_id,
3020 -- p_check_spel_exists => FND_API.G_TRUE,
3021 p_position_line_id => p_position_line_id,
3022 p_budget_year_id => i,
3023 p_pay_element_id => p_pay_element_id,
3024 p_currency_code => g_currency_code,
3025 p_element_cost => l_wlbi_sp_element_cost,
3026 p_element_set_id => p_element_set_id,
3027 p_service_package_id => l_service_package_id,
3028 p_stage_set_id => g_stage_set_id,
3029 p_current_stage_seq => g_current_stage_seq
3030
3031 );
3032
3033 --debug('elc_return_status'||l_return_status);
3034 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3035 RAISE FND_API.G_EXC_ERROR ;
3036 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3037 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3038 END IF;
3039
3040 END IF;
3041
3042
3043 i:= g_estimate_years.NEXT(i);
3044 END LOOP; --for each estimate year
3045 END LOOP; -- For each sp_element_cost_rec
3046 END LOOP; -- For each service package
3047
3048 END Change_Element_Cost;
3049
3050
3051 PROCEDURE Get_Element_Line_ID
3052 ( p_position_line_id IN NUMBER,
3053 p_budget_year_id IN NUMBER,
3054 p_pay_element_id IN NUMBER,
3055 p_service_package_id IN NUMBER,
3056 p_found_element_line OUT NOCOPY BOOLEAN,
3057 p_element_line_id OUT NOCOPY NUMBER
3058 )
3059 IS
3060
3061 cursor wel_cur is
3062 SELECT element_line_id
3063 FROM psb_ws_element_lines
3064 WHERE position_line_id = p_position_line_id
3065 AND budget_year_id = p_budget_year_id
3066 AND service_package_id = p_service_package_id
3067 AND pay_element_id = p_pay_element_id
3068 AND end_stage_seq IS NULL;
3069
3070 Recinfo wel_cur%ROWTYPE;
3071
3072 BEGIN
3073 p_element_line_id := 0;
3074 OPEN wel_cur;
3075 FETCH wel_cur INTO Recinfo;
3076 IF wel_cur%FOUND THEN
3077 p_found_element_line := TRUE;
3078 p_element_line_id := Recinfo.element_line_id;
3079 ELSE
3080 p_found_element_line := FALSE;
3081 END IF;
3082 close wel_cur;
3083
3084 END Get_Element_Line_ID;
3085
3086
3087 PROCEDURE Get_WLBI_SP_Element_Cost
3088 ( p_budget_year_id IN NUMBER,
3089 p_wlbi_sp_element_cost OUT NOCOPY NUMBER
3090 )
3091 IS
3092 l_period_start_column NUMBER;
3093 l_period_end_column NUMBER;
3094 l_year_amount NUMBER := 0 ;
3095
3096 BEGIN
3097 -- Retrieve the sum from PL/SQL Table
3098 l_period_start_column := g_estimate_years(p_budget_year_id).period_start_column ;
3099 l_period_end_column := g_estimate_years(p_budget_year_id).period_end_column;
3100
3101 FOR i IN l_period_start_column .. l_period_end_column
3102 LOOP
3103 l_year_amount := g_wlbi_amounts(i).amount + l_year_amount;
3104 END LOOP;
3105 p_wlbi_sp_element_cost := l_year_amount;
3106
3107 END Get_WLBI_SP_Element_Cost;
3108
3109
3110 -- Before calling this routine :
3111 -- Cache Budget Calendar
3112 -- Cache Elements, Attributes for data extract and business group id
3113 -- Cache Position assignments based on effective dates of the position and
3114 -- the budget calendar(current year start date and estimate year end date)
3115
3119 p_budget_calendar_id IN NUMBER,
3116 PROCEDURE Change_Pos_Year_Assignments
3117 ( p_return_status OUT NOCOPY VARCHAR2,
3118 p_worksheet_id IN NUMBER,
3120 p_data_extract_id IN NUMBER,
3121 p_business_group_id IN NUMBER,
3122 p_position_line_id IN NUMBER,
3123 p_position_id IN NUMBER,
3124 p_position_name IN VARCHAR2,
3125 p_pay_element_id IN NUMBER,
3126 p_amt_tolerance_value_type IN VARCHAR2,
3127 p_amt_tolerance_value IN NUMBER,
3128 p_pct_tolerance_value_type IN VARCHAR2,
3129 p_pct_tolerance_value IN NUMBER,
3130 p_budget_year_id IN NUMBER,
3131 p_assignments IN g_assignment_tbl_type
3132
3133 ) IS
3134
3135 l_return_status VARCHAR2(1);
3136 l_msg_count NUMBER;
3137
3138
3139 l_element_name VARCHAR2(30);
3140 l_processing_type VARCHAR2(1);
3141 l_max_element_value_type VARCHAR2(2);
3142 l_max_element_value NUMBER;
3143 l_salary_flag VARCHAR2(1);
3144 l_salary_type VARCHAR2(10);
3145 l_follow_salary VARCHAR2(1);
3146 l_period_type VARCHAR2(10);
3147 l_process_period_type VARCHAR2(10);
3148
3149 l_fte NUMBER;
3150 l_default_weekly_hours NUMBER;
3151 l_pay_element_id NUMBER;
3152 l_pay_element_option_id NUMBER;
3153 l_pay_basis VARCHAR2(10);
3154 l_element_value_type VARCHAR2(2);
3155 l_element_value NUMBER;
3156 l_formula_id NUMBER;
3157 l_option_flag VARCHAR2(1);
3158 l_overwrite_flag VARCHAR2(1);
3159 l_budget_period_id NUMBER;
3160
3161
3162 l_year_index BINARY_INTEGER;
3163 l_period_index BINARY_INTEGER;
3164 l_calcperiod_index BINARY_INTEGER;
3165 l_element_index BINARY_INTEGER;
3166 l_assign_index BINARY_INTEGER;
3167 l_rate_index BINARY_INTEGER;
3168 l_salary_index BINARY_INTEGER;
3169
3170 l_ws_assignment VARCHAR2(1);
3171 l_element_assigned VARCHAR2(1);
3172
3173 l_factor NUMBER;
3174 l_element_cost NUMBER;
3175 l_ytd_element_cost NUMBER;
3176
3177 l_last_period_index NUMBER;
3178
3179 l_salary_defined VARCHAR2(1) := FND_API.G_FALSE;
3180 l_salary_element_value NUMBER;
3181
3182
3183 l_assign_period VARCHAR2(1);
3184 l_calculate_from_salary VARCHAR2(1);
3185 l_assign_period_index NUMBER;
3186
3187
3188 l_nonrec_calculated VARCHAR2(1);
3189
3190
3191 l_period_num NUMBER;
3192 l_year_period_num NUMBER;
3193 l_num_budget_periods NUMBER;
3194 l_new_salary_element_cost NUMBER;
3195 l_new_element_cost NUMBER;
3196 l_new_element_value NUMBER;
3197
3198 l_budget_period_type VARCHAR2(1);
3199 l_budget_period_start_date DATE;
3200 l_budget_period_end_date DATE;
3201 l_num_calc_periods NUMBER;
3202
3203
3204 l_pos_assignment VARCHAR2(1);
3205 l_value_from_elem_rates VARCHAR2(1);
3206 l_year_start_date DATE;
3207 l_year_end_date DATE;
3208
3209 l_tol_max_element_value NUMBER;
3210 l_tol_min_element_value NUMBER;
3211 l_new_assignment_found VARCHAR2(1);
3212
3213 l_new_element_option_id NUMBER;
3214 l_position_assignment_id NUMBER;
3215 l_rowid VARCHAR2(200);
3216 l_msg_data VARCHAR2(2000);
3217
3218 BEGIN
3219 l_pay_element_id := p_pay_element_id;
3220
3221 for l_element_index in 1..PSB_WS_POS1.g_num_elements loop
3222
3223 if PSB_WS_POS1.g_elements(l_element_index).pay_element_id = l_pay_element_id then
3224
3225 l_element_name := PSB_WS_POS1.g_elements(l_element_index).element_name;
3226 l_processing_type := PSB_WS_POS1.g_elements(l_element_index).processing_type;
3227 l_max_element_value_type := PSB_WS_POS1.g_elements(l_element_index).max_element_value_type;
3228 l_max_element_value := PSB_WS_POS1.g_elements(l_element_index).max_element_value;
3229 l_salary_flag := PSB_WS_POS1.g_elements(l_element_index).salary_flag;
3230 l_salary_type := PSB_WS_POS1.g_elements(l_element_index).salary_type;
3231 l_follow_salary := PSB_WS_POS1.g_elements(l_element_index).follow_salary;
3232 l_period_type := PSB_WS_POS1.g_elements(l_element_index).period_type;
3233 l_process_period_type := PSB_WS_POS1.g_elements(l_element_index).process_period_type;
3234 l_option_flag := PSB_WS_POS1.g_elements(l_element_index).option_flag;
3235 l_overwrite_flag := PSB_WS_POS1.g_elements(l_element_index).overwrite_flag;
3236
3237 exit;
3238 end if;
3239 end loop;
3240
3241
3242 -- Get the last period index
3243 for l_year_index in 1..PSB_WS_ACCT1.g_num_budget_years loop
3244
3245 if PSB_WS_ACCT1.g_budget_years(l_year_index).budget_year_id = p_budget_year_id then
3246 l_last_period_index := PSB_WS_ACCT1.g_budget_years(l_year_index).last_period_index;
3250 exit;
3247 l_year_start_date := PSB_WS_ACCT1.g_budget_years(l_year_index).start_date;
3248 l_year_end_date := PSB_WS_ACCT1.g_budget_years(l_year_index).end_date;
3249
3251 end if;
3252
3253 end loop;
3254
3255
3256 l_pay_element_id := p_pay_element_id;
3257 l_element_assigned := FND_API.G_FALSE;
3258 l_nonrec_calculated := FND_API.G_FALSE;
3259
3260 for l_assign_index in 1..g_num_poselem_assignments loop
3261 if ((g_poselem_assignments(l_assign_index).pay_element_id = l_pay_element_id) and
3262 (((g_poselem_assignments(l_assign_index).start_date <= l_year_end_date) and
3263 (g_poselem_assignments(l_assign_index).end_date is null)) or
3264 ((g_poselem_assignments(l_assign_index).start_date between l_year_start_date and l_year_end_date) or
3265 (g_poselem_assignments(l_assign_index).end_date between l_year_start_date and l_year_end_date) or
3266 ((g_poselem_assignments(l_assign_index).start_date < l_year_start_date) and
3267 (g_poselem_assignments(l_assign_index).end_date > l_year_end_date))))) then
3268 l_element_assigned := FND_API.G_TRUE;
3269
3270 exit;
3271 end if;
3272
3273 end loop;
3274
3275
3276 if nvl(l_process_period_type, 'FIRST') = 'FIRST' then
3277 l_assign_period_index := 1;
3278 else
3279 l_assign_period_index := l_last_period_index;
3280 end if;
3281
3282 l_ytd_element_cost := 0;
3283 l_calculate_from_salary := FND_API.G_FALSE;
3284 l_assign_period := FND_API.G_FALSE;
3285
3286 -- Main Loop
3287 -- For each budget period with a new amount
3288
3289 l_num_budget_periods := (g_estimate_years(p_budget_year_id).period_end_column -
3290 g_estimate_years(p_budget_year_id).period_start_column) + 1;
3291
3292
3293
3294 for l_budget_period in 1..g_assignment_count loop
3295
3296
3297 l_period_num := p_assignments(l_budget_period).period;
3298
3299
3300 l_new_element_cost := p_assignments(l_budget_period).new_amount;
3301
3302 l_new_element_value := 0;
3303
3304 -- Loop thru cached calendar to get start and end dates for the budget period
3305 l_year_period_num := 0;
3306
3307 for l_period_index in 1..PSB_WS_ACCT1.g_num_budget_periods loop
3308
3309 if PSB_WS_ACCT1.g_budget_periods(l_period_index).budget_year_id = p_budget_year_id then
3310 begin
3311 l_year_period_num := l_year_period_num + 1;
3312 IF l_year_period_num = l_period_num THEN
3313
3314 l_budget_period_id := PSB_WS_ACCT1.g_budget_periods(l_period_index).budget_period_id;
3315 l_budget_period_type := PSB_WS_ACCT1.g_budget_periods(l_period_index).budget_period_type;
3316 l_budget_period_start_date := PSB_WS_ACCT1.g_budget_periods(l_period_index).start_date;
3317 l_budget_period_end_date := PSB_WS_ACCT1.g_budget_periods(l_period_index).end_date;
3318 l_budget_period_type := PSB_WS_ACCT1.g_budget_periods(l_period_index).budget_period_type;
3319 l_num_calc_periods := PSB_WS_ACCT1.g_budget_periods(l_period_index).num_calc_periods;
3320
3321 END IF;
3322 end;
3323 end if;
3324 end loop;
3325
3326 -- Get the assignment data
3327 l_ws_assignment := FND_API.G_FALSE;
3328 for l_assign_index in 1..g_num_poselem_assignments loop
3329
3330 if ((g_poselem_assignments(l_assign_index).pay_element_id = l_pay_element_id) and
3331 (g_poselem_assignments(l_assign_index).worksheet_id is not null) and
3332 (((g_poselem_assignments(l_assign_index).start_date <= l_budget_period_start_date) and
3333 (g_poselem_assignments(l_assign_index).end_date is null)) or
3334 ((g_poselem_assignments(l_assign_index).start_date between l_budget_period_start_date and l_budget_period_end_date) or
3335 (g_poselem_assignments(l_assign_index).end_date between l_budget_period_start_date and l_budget_period_end_date) or
3336 ((g_poselem_assignments(l_assign_index).start_date < l_budget_period_start_date) and
3337 (g_poselem_assignments(l_assign_index).end_date > l_budget_period_end_date))))) then
3338 begin
3339
3340 l_ws_assignment := FND_API.G_TRUE;
3341 l_pay_basis := g_poselem_assignments(l_assign_index).pay_basis;
3342 l_pay_element_option_id := g_poselem_assignments(l_assign_index).pay_element_option_id;
3343 l_element_value_type := g_poselem_assignments(l_assign_index).element_value_type;
3344 l_element_value := g_poselem_assignments(l_assign_index).element_value;
3345
3346 if l_processing_type = 'N' then
3347 l_nonrec_calculated := FND_API.G_TRUE;
3348 end if;
3349 exit;
3350
3351 end;
3352 end if;
3353
3354 end loop;
3355
3356
3357 l_pos_assignment := FND_API.G_FALSE;
3358 if not FND_API.to_Boolean(l_ws_assignment) then
3359 begin
3360
3361 for l_assign_index in 1..g_num_poselem_assignments loop
3362
3363 if ((g_poselem_assignments(l_assign_index).pay_element_id = l_pay_element_id) and
3364 (g_poselem_assignments(l_assign_index).worksheet_id is null) and
3365 (((g_poselem_assignments(l_assign_index).start_date <= l_budget_period_end_date) and
3366 (g_poselem_assignments(l_assign_index).end_date is null)) or
3367 ((g_poselem_assignments(l_assign_index).start_date between l_budget_period_start_date and l_budget_period_end_date) or
3368 (g_poselem_assignments(l_assign_index).end_date between l_budget_period_start_date and l_budget_period_end_date) or
3372
3369 ((g_poselem_assignments(l_assign_index).start_date < l_budget_period_start_date) and
3370 (g_poselem_assignments(l_assign_index).end_date > l_budget_period_end_date))))) then
3371 begin
3373 l_pos_assignment := FND_API.G_TRUE;
3374 l_pay_basis := g_poselem_assignments(l_assign_index).pay_basis;
3375 l_pay_element_option_id := g_poselem_assignments(l_assign_index).pay_element_option_id;
3376 l_element_value_type := g_poselem_assignments(l_assign_index).element_value_type;
3377 l_element_value := g_poselem_assignments(l_assign_index).element_value;
3378
3379 if l_processing_type = 'N' then
3380 l_nonrec_calculated := FND_API.G_TRUE;
3381 end if;
3382
3383
3384 exit;
3385 end;
3386 end if;
3387
3388 end loop;
3389
3390 end;
3391 end if; -- if not ws assignment
3392
3393 -- See if the value is arrived from element rates table
3394 l_value_from_elem_rates := FND_API.G_FALSE;
3395 if l_element_value is null then
3396 begin
3397
3398 for l_rate_index in 1..g_num_poselem_rates loop
3399
3400 if ((g_poselem_rates(l_rate_index).pay_element_id = l_pay_element_id) and
3401 (nvl(g_poselem_rates(l_rate_index).pay_element_option_id, FND_API.G_MISS_NUM) = nvl(l_pay_element_option_id, FND_API.G_MISS_NUM)) and
3402 (((g_poselem_rates(l_rate_index).start_date <= l_budget_period_end_date) and
3403 (g_poselem_rates(l_rate_index).end_date is null)) or
3404 ((g_poselem_rates(l_rate_index).start_date between l_budget_period_start_date and l_budget_period_end_date) or
3405 (g_poselem_rates(l_rate_index).end_date between l_budget_period_start_date and l_budget_period_end_date) or
3406 ((g_poselem_rates(l_rate_index).start_date < l_budget_period_start_date) and
3407 (g_poselem_rates(l_rate_index).end_date > l_budget_period_end_date))))) then
3408 begin
3409 l_value_from_elem_rates := FND_API.G_TRUE;
3410 l_element_value_type := g_poselem_rates(l_rate_index).element_value_type;
3411 l_element_value := g_poselem_rates(l_rate_index).element_value;
3412 l_formula_id := g_poselem_rates(l_rate_index).formula_id;
3413 exit;
3414 end;
3415 end if;
3416
3417 end loop;
3418
3419 end;
3420 end if; -- Element value is null
3421
3422 -- Get FTE
3423 l_fte := g_fte_period_amounts(l_period_num).amount;
3424
3425 if g_num_poswkh_assignments > 0 then
3426 for l_assign_index in 1 .. g_num_poswkh_assignments loop
3427
3428 if (((g_poswkh_assignments(l_assign_index).start_date <= l_budget_period_end_date) and
3429 (g_poswkh_assignments(l_assign_index).end_date is null)) or
3430 ((g_poswkh_assignments(l_assign_index).start_date between l_budget_period_start_date and l_budget_period_end_date) or
3431 (g_poswkh_assignments(l_assign_index).end_date between l_budget_period_start_date and l_budget_period_end_date) or
3432 ((g_poswkh_assignments(l_assign_index).start_date < l_budget_period_start_date) and
3433 (g_poswkh_assignments(l_assign_index).end_date > l_budget_period_end_date)))) then
3434 begin
3435
3436 l_default_weekly_hours := g_poswkh_assignments(l_assign_index).default_weekly_hours;
3437
3438 exit;
3439 end;
3440 end if;
3441
3442 end loop;
3443 end if;
3444
3445
3446 if l_salary_flag = 'Y' then
3447 begin
3448
3449 l_new_salary_element_cost := l_new_element_cost;
3450
3451
3452 if l_processing_type = 'N' then
3453 l_new_element_value := l_new_element_cost;
3454 else
3455 begin
3456
3457 if l_pay_basis = 'ANNUAL' then
3458 begin
3459
3460 PSB_WS_POS1.HRMS_Factor
3461 (p_return_status => l_return_status,
3462 p_hrms_period_type => 'Y',
3463 p_budget_period_type => l_budget_period_type,
3464 p_position_name => p_position_name,
3465 p_element_name => l_element_name,
3466 p_start_date => l_budget_period_start_date,
3467 p_end_date => l_budget_period_end_date,
3468 p_factor => l_factor);
3469
3470 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3471 raise FND_API.G_EXC_ERROR;
3472 end if;
3473
3474
3475 l_new_element_value := l_new_element_cost/(l_fte * l_factor * l_num_calc_periods);
3476
3477 end;
3478 elsif l_pay_basis = 'HOURLY' then
3479 begin
3480
3481 PSB_WS_POS1.HRMS_Factor
3482 (p_return_status => l_return_status,
3483 p_hrms_period_type => 'W',
3484 p_budget_period_type => l_budget_period_type,
3485 p_position_name => p_position_name,
3486 p_element_name => l_element_name,
3487 p_start_date => l_budget_period_start_date,
3488 p_end_date => l_budget_period_end_date,
3489 p_factor => l_factor);
3490
3491 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3492 raise FND_API.G_EXC_ERROR;
3493 end if;
3494
3495 if l_default_weekly_hours is null then
3496 --add_message('PSB', 'PSB_INVALID_NAMED_ATTRIBUTE');
3497 null;
3498 end if;
3499
3500 l_new_element_value := l_new_element_cost/(l_fte * l_factor
3501 * l_default_weekly_hours* l_num_calc_periods);
3502
3503 end;
3504 elsif l_pay_basis = 'MONTHLY' then
3505 begin
3506
3507 PSB_WS_POS1.HRMS_Factor
3508 (p_return_status => l_return_status,
3509 p_hrms_period_type => 'CM',
3510 p_budget_period_type => l_budget_period_type,
3511 p_position_name => p_position_name,
3512 p_element_name => l_element_name,
3513 p_start_date => l_budget_period_start_date,
3514 p_end_date => l_budget_period_end_date,
3515 p_factor => l_factor);
3516
3517 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3518 raise FND_API.G_EXC_ERROR;
3519 end if;
3520
3521
3522 l_new_element_value := l_new_element_cost/(l_fte * l_factor * l_num_calc_periods);
3523
3524
3525 end;
3526 elsif l_pay_basis = 'PERIOD' then
3527 begin
3528 PSB_WS_POS1.HRMS_Factor
3529 (p_return_status => l_return_status,
3530 /* For Bug No. 2504333 : Start */
3531 -- p_hrms_period_type => PSB_WS_POS1.g_elements(l_element_index).period_type,
3532 p_hrms_period_type => l_period_type,
3533 /* For Bug No. 2504333 : End */
3534 p_budget_period_type => l_budget_period_type,
3535 p_position_name => p_position_name,
3536 p_element_name => l_element_name,
3537 p_start_date => l_budget_period_start_date,
3538 p_end_date => l_budget_period_end_date,
3539 p_factor => l_factor);
3540 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3541 raise FND_API.G_EXC_ERROR;
3542 end if;
3543
3544 l_new_element_value := l_new_element_cost/(l_fte * l_factor * l_num_calc_periods );
3545
3546 end;
3547
3548 end if; -- pay_basis
3549
3550 end;
3551 end if; -- processing type <> 'N' (Non-recurring)
3552 end;
3553 else --PSB_WS_POS1.g_elements(l_element_index).salary_flag <> 'Y'
3554 begin
3555
3556 if l_element_value_type = 'PS' then
3557 begin
3558 l_new_element_value := l_new_element_cost;
3559 end;
3560 elsif l_element_value_type = 'A' then
3561 begin
3562
3563 if l_processing_type = 'N' then
3564 l_new_element_value := l_new_element_cost;
3565 else
3566 begin
3567 PSB_WS_POS1.HRMS_Factor
3568 (p_return_status => l_return_status,
3569 /* For Bug No. 2504333 : Start */
3570 -- p_hrms_period_type => PSB_WS_POS1.g_elements(l_element_index).period_type,
3571 p_hrms_period_type => l_period_type,
3572 /* For Bug No. 2504333 : End */
3573 p_budget_period_type => l_budget_period_type,
3574 p_position_name => p_position_name,
3575 p_element_name => l_element_name,
3576 p_start_date => l_budget_period_start_date,
3577 p_end_date => l_budget_period_end_date,
3578 p_factor => l_factor);
3579 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3580 raise FND_API.G_EXC_ERROR;
3581 end if;
3582 l_new_element_value := l_new_element_cost/(l_fte * l_factor * l_num_calc_periods );
3583 end;
3584 end if;
3585
3586 end;
3587 --Ignore if l_element_value_type = 'F' (Formula)
3588 end if; -- l_element_value_type
3589
3590 end;
3591 end if; --PSB_WS_POS1.g_elements(l_element_index).salary_flag <> 'Y'
3592
3593
3594 -- Limit the element value to the max value
3595 l_new_element_value := least(l_new_element_value, nvl(l_max_element_value, l_new_element_value));
3596
3597
3598
3599 -- Find out the tolerance amount or percent
3600
3601 if l_new_element_value is not null then
3602 -- Assign default values to tolerance limits
3603 l_tol_max_element_value := l_new_element_value;
3604 l_tol_min_element_value := l_new_element_value;
3605
3606 if l_element_value_type = 'A' then
3607 if p_amt_tolerance_value_type = 'A' then
3608 l_tol_max_element_value := l_new_element_value + p_amt_tolerance_value;
3609 l_tol_min_element_value := l_new_element_value - p_amt_tolerance_value;
3610 elsif p_amt_tolerance_value_type = 'P' then
3611 l_tol_max_element_value := l_new_element_value *( 1 + p_amt_tolerance_value/100);
3612 l_tol_min_element_value := l_new_element_value *( 1 - p_amt_tolerance_value/100);
3613 end if;
3614 elsif l_element_value_type = 'PS' then
3615 if p_pct_tolerance_value_type = 'A' then
3616 l_tol_max_element_value := l_new_element_value + p_pct_tolerance_value;
3617 l_tol_min_element_value := l_new_element_value - p_pct_tolerance_value;
3618 elsif p_pct_tolerance_value_type = 'P' then
3619 l_tol_max_element_value := l_new_element_value * ( 1 + p_pct_tolerance_value/100);
3620 l_tol_min_element_value := l_new_element_value * ( 1 - p_pct_tolerance_value/100);
3621 end if;
3622 end if;
3623 end if;
3624
3625
3626 l_new_assignment_found := FND_API.G_FALSE;
3627 -- Find out the Element Option from element value
3628
3629 l_new_element_option_id := null;
3630 if l_option_flag = 'Y' then
3631
3632 --debug('g_global_worksheet_id'||g_global_worksheet_id);
3633 --debug('g_currency_code'||g_currency_code);
3634 --debug('l_budget_period_start_date'||l_budget_period_start_date);
3635 --debug('l_budget_period_end_date'||l_budget_period_end_date);
3636 --debug('g_business_group_id'||g_business_group_id);
3637 --debug('g_data_extract_id'||g_data_extract_id);
3638
3639
3640 -- pick the element
3641 for elem_asgn_rec in
3642 (select a.worksheet_id,
3643 abs(l_new_element_value - a.element_value) near_el,
3644 a.pay_element_id,
3648 where (a.worksheet_id is null or a.worksheet_id = g_global_worksheet_id)
3645 a.pay_element_option_id
3646 from PSB_PAY_ELEMENT_RATES a,
3647 PSB_PAY_ELEMENTS b
3649 and a.currency_code = g_currency_code
3650 and (((a.effective_start_date <= l_budget_period_end_date)
3651 and (a.effective_end_date is null))
3652 or ((a.effective_start_date between l_budget_period_start_date and l_budget_period_end_date)
3653 or (a.effective_end_date between l_budget_period_start_date and l_budget_period_end_date)
3654 or ((a.effective_start_date < l_budget_period_start_date)
3655 and (a.effective_end_date > l_budget_period_end_date))))
3656 and a.pay_element_id = b.pay_element_id
3657 and b.business_group_id = g_business_group_id
3658 and b.data_extract_id = g_data_extract_id
3659 and a.pay_element_id = l_pay_element_id
3660 and a.element_value between l_tol_min_element_value and l_tol_max_element_value
3661 order by 1,2
3662 )
3663 loop
3664 l_new_element_option_id := elem_asgn_rec.pay_element_option_id;
3665 l_new_assignment_found := FND_API.G_TRUE;
3666
3667
3668 exit;
3669 end loop; --elem_asgn_rec
3670
3671 if l_new_assignment_found = FND_API.G_FALSE then
3672
3673 FND_MESSAGE.SET_NAME('PSB', 'PSB_ELEMENT_OPTION_NOT_FOUND');
3674 FND_MESSAGE.SET_TOKEN('POSITION_NAME', p_position_name);
3675 FND_MESSAGE.SET_TOKEN('PAY_ELEMENT_NAME', l_element_name);
3676 FND_MSG_PUB.Add;
3677 raise FND_API.G_EXC_ERROR;
3678 end if;
3679
3680 elsif l_option_flag = 'N' then
3681
3682 -- retain the old element option if overwrite is allowed
3683 if l_overwrite_flag = 'Y' then
3684 l_new_element_option_id := l_pay_element_option_id;
3685 else
3686 FND_MESSAGE.SET_NAME('PSB', 'PSB_ELEMENT_OVERIDE_NA');
3687 FND_MESSAGE.SET_TOKEN('POSITION_NAME', p_position_name);
3688 FND_MESSAGE.SET_TOKEN('PAY_ELEMENT_NAME', l_element_name);
3689 FND_MSG_PUB.Add;
3690 raise FND_API.G_EXC_ERROR;
3691 end if;
3692
3693
3694 end if; -- option flag = 'Y'
3695
3696
3697
3698
3699 -- Call Modify Assignment API to make assignment change
3700 IF ( nvl(l_new_element_value,0) > 0 )
3701 AND nvl(l_new_element_value,0) <> nvl(l_element_value,0)
3702 AND ( l_new_element_option_id IS NOT NULL )
3703 AND ( l_new_assignment_found = FND_API.G_TRUE
3704 OR
3705 l_overwrite_flag = 'Y' ) THEN
3706
3707 --debug('l_element_value_type'||l_element_value_type);
3708 --debug('l_new_element_value'||l_new_element_value);
3709 --debug('l_new_element_option_id'||l_new_element_option_id);
3710 --debug('p_position_id'||p_position_id);
3711 --debug('p_pay_element_id'||p_pay_element_id);
3712 --debug('l_pay_basis'||l_pay_basis);
3713
3714 -- Change made on 11/10/98
3715 -- Do not populate element value if a matching element option is found
3716 IF l_new_assignment_found = FND_API.G_TRUE THEN
3717 l_new_element_value := null;
3718 END IF;
3719
3720
3721
3722 PSB_POSITIONS_PVT.Modify_Assignment(
3723 p_api_version => 1.0,
3724 p_init_msg_list => FND_API.G_FALSE,
3725 p_commit => FND_API.G_FALSE,
3726 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3727 p_return_status => l_return_status,
3728 p_msg_count => l_msg_count,
3729 p_msg_data => l_msg_data,
3730 p_position_assignment_id => l_position_assignment_id,
3731 p_data_extract_id => g_data_extract_id,
3732 p_worksheet_id => g_assignment_worksheet_id,
3733 p_position_id => p_position_id,
3734 p_assignment_type => 'ELEMENT',
3735 p_attribute_id => null,
3736 p_attribute_value_id => null,
3737 p_attribute_value => null,
3738 p_pay_element_id => p_pay_element_id,
3739 p_pay_element_option_id => l_new_element_option_id,
3740 p_effective_start_date => l_budget_period_start_date,
3741 p_effective_end_date => l_budget_period_end_date,
3742 p_element_value_type => l_element_value_type,
3743 p_element_value => l_new_element_value,
3744 p_currency_code => g_currency_code,
3745 p_pay_basis => l_pay_basis,
3746 p_employee_id => null,
3747 p_primary_employee_flag => null,
3748 p_global_default_flag => null,
3749 p_assignment_default_rule_id => null,
3750 p_modify_flag => null,
3751 p_rowid => l_rowid
3752 );
3753 --debug('new asgn id'||l_position_assignment_id);
3754 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3755 raise FND_API.G_EXC_ERROR;
3756 end if;
3757
3758 --debug('l_element_value_type'||l_element_value_type);
3759 END IF;
3760 end loop; -- For each Budget Period in the input PL/SQL table
3761
3762
3763 p_return_status := FND_API.G_RET_STS_SUCCESS;
3764 EXCEPTION
3765
3766 when FND_API.G_EXC_ERROR then
3767 p_return_status := FND_API.G_RET_STS_ERROR;
3768
3769 when FND_API.G_EXC_UNEXPECTED_ERROR then
3770 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3771
3772 when OTHERS then
3773 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3774
3775 END Change_Pos_Year_Assignments;
3776
3777
3778 PROCEDURE Get_New_Assignments
3779 (
3780 p_budget_year_id IN NUMBER
3781 )
3782 IS
3783
3784 l_wal_index NUMBER;
3785 l_period_start_column NUMBER;
3786 l_period_end_column NUMBER;
3787 l_assignment_index NUMBER;
3788
3789 BEGIN
3790 -- compare the values from wlbi and wal PL/SQL Tables
3791 -- and build the input assignment PL/SQL table
3792 l_period_start_column := g_estimate_years(p_budget_year_id).period_start_column ;
3793 l_period_end_column := g_estimate_years(p_budget_year_id).period_end_column;
3794
3795
3796
3797 l_wal_index := 1;
3798 l_assignment_index := 1;
3799
3800 g_assignment_count := 0;
3801
3802 FOR i IN l_period_start_column .. l_period_end_column
3803 LOOP
3804 IF g_wlbi_amounts(i).amount <> g_wal_period_amounts(l_wal_index).amount THEN
3805 g_assignment_count := g_assignment_count + 1;
3806
3807 g_assignment(l_assignment_index).period := l_wal_index;
3808 g_assignment(l_assignment_index).new_amount := g_wlbi_amounts(i).amount;
3809 l_assignment_index := l_assignment_index + 1;
3810
3811 END IF;
3812 l_wal_index := l_wal_index + 1;
3813
3814 END LOOP;
3815
3816 END Get_New_Assignments;
3817
3818
3819 PROCEDURE Get_WLBI_Element_Cost
3820 ( p_budget_year_id IN NUMBER,
3821 p_wlbi_element_cost OUT NOCOPY NUMBER
3822 )
3823 IS
3824 l_period_start_column NUMBER;
3825 l_period_end_column NUMBER;
3826 l_year_amount NUMBER := 0 ;
3827
3828 /* For Bug No. 2378123 : Start */
3829 l_total_column NUMBER;
3830 /* For Bug No. 2378123 : End */
3831
3832 BEGIN
3833 -- Retrieve the sum from PL/SQL Table
3834
3835 /* For Bug No. 2378123 : Start
3836 Element Cost is taken as YTD amount instead of sum of Period amounts */
3837 -- Following code is commented and next two lines added
3838 /*
3839 l_period_start_column := g_estimate_years(p_budget_year_id).period_start_column ;
3840 l_period_end_column := g_estimate_years(p_budget_year_id).period_end_column;
3841
3842 FOR i IN l_period_start_column .. l_period_end_column
3843 LOOP
3844 l_year_amount := g_wlbi_amounts(i).amount + l_year_amount;
3845 END LOOP;
3846 */
3847
3848 l_total_column := g_estimate_years(p_budget_year_id).total_column;
3849 l_year_amount := g_wlbi_amounts(l_total_column).amount;
3850 /* For Bug No. 2378123 : End */
3851
3852 p_wlbi_element_cost := l_year_amount;
3853
3854 END Get_WLBI_Element_Cost;
3855
3856
3857 PROCEDURE Get_WAL_Element_Cost
3858 ( p_position_line_id IN NUMBER,
3859 p_element_set_id IN NUMBER,
3860 p_budget_year_id IN NUMBER,
3861 p_wal_element_cost OUT NOCOPY NUMBER
3862 )
3863 IS
3864
3865 l_wal_element_cost NUMBER;
3866
3867 BEGIN
3868 FOR wal_rec IN
3869 (SELECT sum(ytd_amount) a,
3870 sum(period1_amount) a1,
3871 sum(period2_amount) a2,
3872 sum(period3_amount) a3,
3873 sum(period4_amount) a4,
3874 sum(period5_amount) a5,
3875 sum(period6_amount) a6,
3876 sum(period7_amount) a7,
3877 sum(period8_amount) a8,
3878 sum(period9_amount) a9,
3879 sum(period10_amount) a10,
3880 sum(period11_amount) a11,
3881 sum(period12_amount) a12
3882 FROM psb_ws_account_lines wal , psb_ws_lines wl
3883 WHERE wal.account_line_id = wl.account_line_id
3884 AND wl.worksheet_id = g_worksheet_id
3885 AND wal.position_line_id = p_position_line_id
3886 AND wal.element_set_id = p_element_set_id
3887 AND wal.budget_year_id = p_budget_year_id
3888 AND wal.template_id IS NULL
3889 AND wal.end_stage_seq is null
3890 )
3891 LOOP
3892
3893 p_wal_element_cost := wal_rec.a;
3894
3895 g_wal_period_amounts(1).amount := wal_rec.a1;
3896 g_wal_period_amounts(2).amount := wal_rec.a2;
3897 g_wal_period_amounts(3).amount := wal_rec.a3;
3898 g_wal_period_amounts(4).amount := wal_rec.a4;
3899 g_wal_period_amounts(5).amount := wal_rec.a5;
3900 g_wal_period_amounts(6).amount := wal_rec.a6;
3901 g_wal_period_amounts(7).amount := wal_rec.a7;
3902 g_wal_period_amounts(8).amount := wal_rec.a8;
3903 g_wal_period_amounts(9).amount := wal_rec.a9;
3904 g_wal_period_amounts(10).amount := wal_rec.a10;
3905 g_wal_period_amounts(11).amount := wal_rec.a11;
3906 g_wal_period_amounts(12).amount := wal_rec.a12;
3907
3908
3909 END LOOP;
3910
3911 END Get_WAL_Element_Cost;
3912
3913 PROCEDURE Get_FTE
3914 ( p_position_line_id IN NUMBER,
3915 p_budget_year_id IN NUMBER
3916 )
3917 IS
3918 BEGIN
3919 -- Initiate
3920 for i in 1.. g_fte_period_amounts.count loop
3921 g_fte_period_amounts(i).amount := null;
3922 end loop;
3923
3924 FOR ws_fte_rec IN
3925 -- Refine this query later to handle template id
3926 (SELECT sum(annual_fte) a,
3927 sum(period1_fte) a1,
3928 sum(period2_fte) a2,
3929 sum(period3_fte) a3,
3930 sum(period4_fte) a4,
3931 sum(period5_fte) a5,
3932 sum(period6_fte) a6,
3933 sum(period7_fte) a7,
3934 sum(period8_fte) a8,
3935 sum(period9_fte) a9,
3936 sum(period10_fte) a10,
3937 sum(period11_fte) a11,
3938 sum(period12_fte) a12
3939 FROM psb_ws_fte_lines fl
3940 WHERE position_line_id = p_position_line_id
3941 AND budget_year_id = p_budget_year_id
3942 AND end_stage_seq is null
3943 )
3944 LOOP
3945 g_fte_period_amounts(1).amount := ws_fte_rec.a1;
3946 g_fte_period_amounts(2).amount := ws_fte_rec.a2;
3947 g_fte_period_amounts(3).amount := ws_fte_rec.a3;
3948 g_fte_period_amounts(4).amount := ws_fte_rec.a4;
3949 g_fte_period_amounts(5).amount := ws_fte_rec.a5;
3950 g_fte_period_amounts(6).amount := ws_fte_rec.a6;
3951 g_fte_period_amounts(7).amount := ws_fte_rec.a7;
3952 g_fte_period_amounts(8).amount := ws_fte_rec.a8;
3953 g_fte_period_amounts(9).amount := ws_fte_rec.a9;
3954 g_fte_period_amounts(10).amount := ws_fte_rec.a10;
3955 g_fte_period_amounts(11).amount := ws_fte_rec.a11;
3956 g_fte_period_amounts(12).amount := ws_fte_rec.a12;
3957
3958 END LOOP;
3959
3960 END Get_FTE;
3961
3962 PROCEDURE Delete_Export_Details(p_export_worksheet_type IN VARCHAR2)
3963 IS
3964 BEGIN
3965
3966 delete from psb_ws_line_balances_i
3967 where export_id = g_export_id
3968 and export_worksheet_type = p_export_worksheet_type;
3969
3970 delete from psb_ws_columns_i
3971 where export_id = g_export_id
3972 and export_worksheet_type = p_export_worksheet_type;
3973
3974 IF p_export_worksheet_type = 'A' THEN
3975 update psb_worksheets_i
3976 set account_export_status = 'DELETE'
3977 where export_id = g_export_id;
3978 g_account_export_status := 'DELETE';
3979
3980 ELSE
3981 update psb_worksheets_i
3982 set position_export_status = 'DELETE'
3983 where export_id = g_export_id;
3984 g_position_export_status := 'DELETE';
3985 END IF;
3986
3987 EXCEPTION
3988 WHEN OTHERS THEN
3989 NULL;
3990 END Delete_Export_Details;
3991
3992 PROCEDURE Delete_Export_Header
3993 IS
3994 BEGIN
3995 IF nvl(g_account_export_status,'DELETE') = 'DELETE'
3996 and nvl(g_position_export_status,'DELETE') = 'DELETE' THEN
3997 delete from psb_worksheets_i
3998 where export_id = g_export_id;
3999 END IF;
4000
4001 EXCEPTION
4002 WHEN OTHERS THEN
4003 NULL;
4004 END Delete_Export_Header;
4005
4006
4007 END PSB_EXCEL2_PVT;