[Home] [Help]
PACKAGE BODY: APPS.PSB_EXCEL_PVT
Source
1 PACKAGE BODY PSB_EXCEL_PVT AS
2 /* $Header: PSBVXLEB.pls 120.7 2004/12/08 06:49:03 maniskum ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PSB_EXCEL_PVT';
5
6 g_ws_line_year_rec psb_ws_matrix.ws_line_year_rec_type;
7 g_export_id NUMBER;
8 g_msg_export_id NUMBER; -- Used in 'Log Messages'
9 g_worksheet_id NUMBER;
10 g_global_worksheet_id NUMBER;
11 g_data_extract_id NUMBER;
12 g_currency_code VARCHAR2(15);
13 g_budget_group_id NUMBER;
14 g_business_group_id NUMBER;
15 g_user_id NUMBER;
16 g_stage_id NUMBER;
17 g_budget_by_position VARCHAR2(1);
18 g_budget_calendar_id NUMBER;
19
20 g_max_num_cols CONSTANT NUMBER := 12;
21 g_max_num_pos_ws_cols CONSTANT NUMBER := 168;
22 g_coa_id NUMBER;
23
24 g_pos_ws_col_no NUMBER;
25 g_total_budget_years NUMBER;
26 g_total_ps_elements NUMBER;
27
28
29 --Variables to store Profile Options
30 g_currency_flag VARCHAR2(1) := 'C';
31 g_year_flag VARCHAR2(1) := 'S';
32 g_service_package_flag VARCHAR2(1) := 'S';
33
34 /* Bug No 2008329 Start */
35 -- g_account_flag VARCHAR2(1) := 'A';
36 -- Changed lookup code from 'C' to 'T' for all accounts for bug 3191611
37 g_account_flag VARCHAR2(1) := 'T';
38 /* Bug No 2008329 End */
39
40 g_template_id NUMBER;
41
42 /* Following three global variables added for DDSP to store the valid profile worksheet ID and User ID. */
43 g_profile_worksheet_id NUMBER;
44 g_profile_user_id NUMBER;
45 g_global_profile_user_id CONSTANT NUMBER := NULL;
46
47 g_allow_account_import VARCHAR2(1) := 'Y';
48 g_allow_position_import VARCHAR2(1) := 'Y';
49
50 -- Storage structures from PSBVWP2B.pls start
51 TYPE g_poselasgn_rec_type IS RECORD
52 ( worksheet_id NUMBER,
53 start_date DATE,
54 end_date DATE,
55 pay_element_id NUMBER,
56 pay_element_option_id NUMBER,
57 pay_basis VARCHAR2(10),
58 element_value_type VARCHAR2(2),
59 element_value NUMBER );
60
61 TYPE g_poselasgn_tbl_type IS TABLE OF g_poselasgn_rec_type
62 INDEX BY BINARY_INTEGER;
63
64 g_poselem_assignments g_poselasgn_tbl_type;
65 g_num_poselem_assignments NUMBER;
66
67 TYPE g_poselrate_rec_type IS RECORD
68 ( worksheet_id NUMBER,
69 start_date DATE,
70 end_date DATE,
71 pay_element_id NUMBER,
72 pay_element_option_id NUMBER,
73 pay_basis VARCHAR2(10),
74 element_value_type VARCHAR2(2),
75 element_value NUMBER,
76 formula_id NUMBER );
77
78 TYPE g_poselrate_tbl_type IS TABLE OF g_poselrate_rec_type
79 INDEX BY BINARY_INTEGER;
80
81 g_poselem_rates g_poselrate_tbl_type;
82 g_num_poselem_rates NUMBER;
83
84 -- Storage structures from PSBVWP2B.pls End
85
86
87 /* ----------------------------------------------------------------------- */
88 /* */
89 /* Private Function and Procedure Declaration */
90 /* */
91 /* ----------------------------------------------------------------------- */
92 -- Routines used by Export_WS
93 PROCEDURE Get_WS_User_Profile;
94 PROCEDURE Populate_WS_Columns;
95 PROCEDURE Populate_WS_Lines;
96 PROCEDURE Populate_POS_WS_Columns;
97 PROCEDURE Populate_POS_WS_Lines;
98
99 PROCEDURE Cache_Position_Data
100 (
101 p_return_status OUT NOCOPY VARCHAR2,
102 p_position_line_id IN NUMBER,
103 p_position_id IN NUMBER,
104 p_start_date IN DATE,
105 p_end_date IN DATE
106 );
107
108 PROCEDURE Setup_Year_View
109 (
110 p_worksheet_id IN NUMBER,
111 p_user_id IN NUMBER,
112 p_stage_id IN NUMBER := FND_API.G_MISS_NUM
113 );
114
115 -- Routines used by Setup_Year_View
116 FUNCTION Get_Current_Stage_Seq RETURN NUMBER;
117 PROCEDURE Get_Calendar_Years;
118 PROCEDURE Get_Saved_Year_Profile;
119 PROCEDURE Set_WS_Matrix_View;
120
121 /* ---------------Exposed Packages---------------- */
122 PROCEDURE Move_To_Interface
123 (
124 p_api_version IN NUMBER ,
125 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
126 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
127 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
128 p_return_status OUT NOCOPY VARCHAR2 ,
129 p_msg_count OUT NOCOPY NUMBER ,
130 p_msg_data OUT NOCOPY VARCHAR2 ,
131 --
132 p_export_name IN VARCHAR2,
133 p_worksheet_id IN NUMBER,
134 p_stage_id IN NUMBER := FND_API.G_MISS_NUM,
135 p_export_worksheet_type IN VARCHAR2 := FND_API.G_MISS_CHAR
136
137 )
138 IS
139
140 l_export_positions BOOLEAN;
141 l_export_accounts BOOLEAN;
142 l_export_worksheet_type VARCHAR2(1);
143 l_account_export_status VARCHAR2(10);
144 l_position_export_status VARCHAR2(10);
145
146 l_export_seq NUMBER;
147 l_export_name psb_worksheets_i.export_name%TYPE;
148
149 l_api_name CONSTANT VARCHAR2(30) := 'Move_To_Interface' ;
150 l_api_version CONSTANT NUMBER := 1.0 ;
151 --
152 l_return_status VARCHAR2(1) ;
153 l_msg_count NUMBER ;
154 l_msg_data VARCHAR2(2000) ;
155 --
156 l_current_stage_seq NUMBER;
157
158 l_ws_rec_found BOOLEAN;
159 BEGIN
160 --dbms_output.put_line('Exporting ...');
161 --
162 SAVEPOINT Move_To_Interface_Pvt ;
163 --
164 IF NOT FND_API.Compatible_API_Call ( l_api_version,
165 p_api_version,
166 l_api_name,
167 G_PKG_NAME )
168 THEN
169 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
170 END IF;
171 --
172
173 IF FND_API.To_Boolean ( p_init_msg_list ) THEN
174 FND_MSG_PUB.initialize ;
175 END IF;
176 --
177 p_return_status := FND_API.G_RET_STS_SUCCESS ;
178
179 l_ws_rec_found := FALSE;
180 FOR ws_recinfo IN
181 (SELECT nvl(global_worksheet_id,worksheet_id) global_worksheet_id,
182 global_worksheet_flag, local_copy_flag,
183 budget_group_id, extract_id , business_group_id,
184 budget_calendar_id, set_of_books_id, freeze_flag, budget_by_position,
185 stage_set_id, current_stage_seq,
186 chart_of_accounts_id, currency_code
187 FROM psb_ws_summary_v
188 WHERE worksheet_id = p_worksheet_id)
189 LOOP
190 l_ws_rec_found := TRUE;
191 g_budget_by_position := ws_recinfo.budget_by_position;
192 g_budget_calendar_id := ws_recinfo.budget_calendar_id;
193 g_business_group_id := ws_recinfo.business_group_id;
194 l_current_stage_seq := ws_recinfo.current_stage_seq;
195 g_budget_group_id := ws_recinfo.budget_group_id;
196 g_global_worksheet_id := ws_recinfo.global_worksheet_id;
197 g_data_extract_id := ws_recinfo.extract_id;
198 g_currency_code := ws_recinfo.currency_code;
199 END LOOP;
200
201 IF NOT l_ws_rec_found THEN
202 FND_MESSAGE.SET_NAME('PSB', 'PSB_INVALID_ARGUMENT');
203 FND_MESSAGE.SET_TOKEN('ROUTINE', 'Export Worksheet Procedure' );
204 FND_MSG_PUB.Add;
205 RAISE FND_API.G_EXC_ERROR ;
206 END IF;
207
208 g_worksheet_id := p_worksheet_id;
209 g_user_id := FND_GLOBAL.USER_ID;
210
211 /* Following procedure called for DDSP To get the values of Data Selection profile WS Id and User ID
212 Start */
213
214 PSB_WS_PERIOD_TOTAL.get_data_selection_profile
215 (p_current_worksheet_id => g_worksheet_id,
216 p_current_user_id => g_user_id,
217 p_global_profile_user_id => g_global_profile_user_id,
218 p_profile_worksheet_id => g_profile_worksheet_id,
219 p_profile_user_id => g_profile_user_id);
220
221 /* End */
222
223 --g_user_id := 0;
224 -- comment line above when running from conc. manager
225 l_export_accounts := FALSE;
226 l_export_positions := FALSE;
227
228 IF p_export_worksheet_type = FND_API.G_MISS_CHAR THEN
229 l_export_worksheet_type := 'B';
230 ELSE
231 l_export_worksheet_type := p_export_worksheet_type;
232 END IF;
233
234 IF l_export_worksheet_type IN ('A','B') THEN
235 l_export_accounts := TRUE;
236 l_account_export_status := 'INSERT';
237 END IF;
238
239 IF l_export_worksheet_type IN ('P','B') and
240 g_budget_by_position = 'Y' THEN
241 l_export_positions := TRUE;
242 l_position_export_status := 'INSERT';
243 END IF;
244
245 IF ( not l_export_accounts) AND
246 ( not l_export_positions) THEN
247 FND_MESSAGE.SET_NAME('PSB', 'PSB_INVALID_ARGUMENT');
248 FND_MESSAGE.SET_TOKEN('ROUTINE', 'Export Worksheet Procedure' );
249 FND_MSG_PUB.Add;
250 RAISE FND_API.G_EXC_ERROR;
251 END IF;
252
253 -- Set the correct worksheet type
254 IF l_export_accounts and l_export_positions THEN
255 l_export_worksheet_type := 'B';
256 ELSIF l_export_accounts THEN
257 l_export_worksheet_type := 'A';
258 ELSIF l_export_positions THEN
259 l_export_worksheet_type := 'P';
260 END IF;
261
262 l_export_seq := Get_Next_Export_Seq;
263 g_export_id := l_export_seq;
264 g_msg_export_id := l_export_seq;
265
266 -- The psb_worksheets_i.export_name is VARCHAR2(80). If necessary, truncate
267 -- export_name to accomodate the export_id at the end.
268 l_export_name := substr( p_export_name,
269 1,
270 80 - length(to_char(l_export_seq)) - 1)
271 || '-' || to_char(l_export_seq) ;
272
273
274 Get_WS_User_Profile;
275
276 -- Set default values for Allow Account Export and Allow Position Import
277 g_allow_account_import := 'Y';
278 g_allow_position_import := 'Y';
279
280 -- g_template_id is set by Get WS User Profile
281 IF g_template_id IS NOT NULL THEN
282 g_allow_account_import := 'N';
283 END IF;
284
285 IF ( p_stage_id = FND_API.G_MISS_NUM) or (p_stage_id IS NULL) THEN
286 g_stage_id := 0;
287 ELSE
288 g_allow_position_import := 'N';
289 g_allow_account_import := 'N';
290 g_stage_id := p_stage_id;
291 END IF;
292
293 Setup_Year_View(p_worksheet_id, g_user_id);
294
295 Insert into PSB_WORKSHEETS_I
296 (EXPORT_ID,
297 EXPORT_NAME,
298 WORKSHEET_ID,
299 STAGE_ID,
300 SELECTED_STAGE_ID,
301 SELECTED_TEMPLATE_ID,
302 ACCOUNT_EXPORT_STATUS,
303 POSITION_EXPORT_STATUS,
304 BUDGET_BY_POSITION,
305 CURRENCY_FLAG,
306 EXPORT_WORKSHEET_TYPE,
307 ALLOW_ACCOUNT_IMPORT,
308 ALLOW_POSITION_IMPORT,
309 LAST_UPDATE_DATE,
310 LAST_UPDATED_BY,
311 LAST_UPDATE_LOGIN,
312 CREATED_BY,
313 CREATION_DATE)
314 values
315 ( g_export_id,
316 l_export_name,
317 p_worksheet_id,
318 l_current_stage_seq,
319 g_stage_id,
320 g_template_id,
321 l_account_export_status,
322 l_position_export_status,
323 g_budget_by_position,
324 g_currency_flag,
325 l_export_worksheet_type,
326 g_allow_account_import,
327 g_allow_position_import,
328 SYSDATE,
329 g_user_id,
330 g_user_id,
331 g_user_id,
332 SYSDATE );
333
334 IF l_export_accounts THEN
335 --dbms_output.put_line('Exporting Accounts');
336 Populate_WS_Columns;
337 Populate_WS_Lines;
338 END IF;
339
340 IF l_export_positions THEN
341 --dbms_output.put_line('Exporting Positions');
342 Populate_POS_WS_Columns;
343 Populate_POS_WS_Lines;
344
345 END IF;
346
347 -- Populate Position WS Lines sets the global variable g_allow_position_import
348 update psb_worksheets_i
349 set allow_position_import = g_allow_position_import
350 where export_id = g_export_id;
351
352 IF FND_API.to_Boolean (p_commit) then
353 commit work;
354 END IF;
355
356
357 EXCEPTION
358
359 --
360 WHEN FND_API.G_EXC_ERROR THEN
361
362 ROLLBACK TO Move_To_Interface_Pvt;
363 p_return_status := FND_API.G_RET_STS_ERROR;
364 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
365 p_data => p_msg_data );
366 --
367 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
368
369 ROLLBACK TO Move_To_Interface_Pvt;
370 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
371 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
372 p_data => p_msg_data );
373 --
374 WHEN OTHERS THEN
375 ROLLBACK TO Move_To_Interface_Pvt;
376 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
377 --
381 END if;
378 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
379 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
380 l_api_name);
382 --
383 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
384 p_data => p_msg_data );
385 --
386
387 END Move_To_Interface;
388
389
390 PROCEDURE Get_WS_User_Profile
391 IS
392
393 --Query Profile
394 CURSOR q_rec IS
395 select
396 TEMPLATE_ID,
397 CURRENCY_FLAG,
398 YEAR_FLAG,
399 SERVICE_PACKAGE_FLAG,
400 ACCOUNT_FLAG
401 from
402 PSB_WS_QUERY_PROFILES_V
403 /* Following 2 lines commented and next 2 lines added for DDSP */
404 -- where WORKSHEET_ID = g_worksheet_id
405 -- and USER_ID = g_user_id;
406 where WORKSHEET_ID = g_profile_worksheet_id
407 and (USER_ID = g_profile_user_id or (g_profile_user_id IS NULL AND USER_ID IS NULL));
408
409 q_recinfo q_rec%ROWTYPE;
410
411 --Year Profile
412 CURSOR y_rec IS
413 SELECT budget_period_id
414 FROM psb_ws_year_profiles_v
415 /* Following 2 lines commented and next 2 lines added for DDSP */
416 -- where WORKSHEET_ID = g_worksheet_id
417 -- and USER_ID = g_user_id;
418 where WORKSHEET_ID = g_profile_worksheet_id
419 and (USER_ID = g_profile_user_id or (g_profile_user_id IS NULL AND USER_ID IS NULL));
420
421 y_recinfo y_rec%ROWTYPE;
422
423 --Service Package Profile
424 CURSOR sp_rec IS
425 SELECT service_package_id
426 FROM PSB_WS_SERVICE_PKG_PROFILES_V
427 /* Following 2 lines commented and next 2 lines added for DDSP */
428 -- where WORKSHEET_ID = g_worksheet_id
429 -- and USER_ID = g_user_id;
430 where WORKSHEET_ID = g_profile_worksheet_id
431 and (USER_ID = g_profile_user_id or (g_profile_user_id IS NULL AND USER_ID IS NULL));
432
433 sp_recinfo sp_rec%ROWTYPE;
434
435 BEGIN
436
437 OPEN q_rec;
438 FETCH q_rec INTO q_recinfo;
439 IF q_rec%FOUND THEN
440 g_currency_flag := q_recinfo.CURRENCY_FLAG;
441 g_year_flag := q_recinfo.YEAR_FLAG;
442 g_service_package_flag := q_recinfo.SERVICE_PACKAGE_FLAG;
443 g_account_flag := q_recinfo.ACCOUNT_FLAG;
444 g_template_id := q_recinfo.template_id;
445 ELSE --Set Default Values
446 g_currency_flag := 'C'; --Currency
447 g_year_flag := 'S'; --Selected
448 g_service_package_flag := 'S'; --Selected
449
450 /* Bug No 2008329 Start */
451 -- g_account_flag := 'A'; --All
452 g_account_flag := 'T'; --All
453 /* Bug No 2008329 End */
454
455 g_template_id := NULL;
456 END IF;
457 CLOSE q_rec;
458
459 --
460 IF g_year_flag = 'S' THEN
461
462 OPEN y_rec;
463 FETCH y_rec INTO y_recinfo;
464 IF y_rec%NOTFOUND THEN
465 g_year_flag := 'A'; --All from Calendar
466 END IF;
467 CLOSE y_rec;
468 END IF;
469
470 IF g_service_package_flag = 'S' THEN
471 OPEN sp_rec;
472 FETCH sp_rec INTO sp_recinfo;
473 IF (sp_rec%NOTFOUND) then
474 g_service_package_flag := 'A';
475 END IF;
476 CLOSE sp_rec;
477 END IF;
478 END Get_WS_User_Profile;
479
480
481 PROCEDURE Setup_Year_View
482 (
483 p_worksheet_id IN NUMBER,
484 p_user_id IN NUMBER,
485 p_stage_id IN NUMBER := FND_API.G_MISS_NUM
486 ) IS
487
488 BEGIN
489
490
491 g_worksheet_id := p_worksheet_id;
492 g_user_id := p_user_id;
493
494 select chart_of_accounts_id into g_coa_id from psb_ws_summary_v
495 where worksheet_id = g_worksheet_id;
496
497
498 -- Get the year ids and balance in PL/SQL table(g_ws_cols)
499
500 IF g_year_flag = 'A' THEN
501 Get_Calendar_Years;
502 ELSE
503 Get_Saved_Year_Profile;
504
505 END IF;
506
507 Set_WS_Matrix_View;
508 --Setup Global for total budget years
509 g_total_budget_years := 0;
510 for i in 1..g_max_num_cols loop
511 IF g_ws_cols(i).budget_year_id IS NULL THEN
512 EXIT;
513 ELSE
514 g_total_budget_years := g_total_budget_years + 1;
515 END IF;
516 end loop;
517
518 END Setup_Year_View;
519
520
521 FUNCTION Get_Current_Stage_Seq RETURN NUMBER
522 IS
523 l_current_stage_seq NUMBER := 0;
524 CURSOR ws_cur IS
525 SELECT current_stage_seq
526 FROM psb_worksheets
527 WHERE worksheet_id = g_worksheet_id;
528 Recinfo ws_cur%ROWTYPE;
529 BEGIN
530 OPEN ws_cur;
531 FETCH ws_cur INTO Recinfo;
532 IF ws_cur%FOUND THEN
533 l_current_stage_seq := Recinfo.current_stage_seq;
534 END IF;
535 CLOSE ws_cur;
536 RETURN l_current_stage_seq;
537
538 END Get_Current_Stage_Seq;
539
540
541 PROCEDURE Clear_WS_Cols is
542 BEGIN
546 g_ws_cols(l_init_index).balance_type := null;
543 for l_init_index in 1..g_max_num_cols loop
544 g_ws_cols(l_init_index).budget_year_id := null;
545 g_ws_cols(l_init_index).budget_year_name := null;
547 g_ws_cols(l_init_index).display_balance_type := null;
548 g_ws_cols(l_init_index).year_category_type := null;
549 end loop;
550 END Clear_WS_Cols;
551
552 PROCEDURE Clear_POS_WS_Cols is
553 BEGIN
554 for l_init_index in 1..g_max_num_pos_ws_cols loop
555 g_pos_ws_cols(l_init_index).column_type := null;
556 g_pos_ws_cols(l_init_index).budget_year_id := null;
557 g_pos_ws_cols(l_init_index).budget_year_name := null;
558 g_pos_ws_cols(l_init_index).balance_type := null;
559 g_pos_ws_cols(l_init_index).display_balance_type := null;
560 g_pos_ws_cols(l_init_index).year_category_type := null;
561 end loop;
562 END Clear_POS_WS_Cols;
563
564
565
566 PROCEDURE Set_POS_WS_Cols
567 ( p_budget_year_id IN NUMBER,
568 p_budget_year_name IN VARCHAR2,
569 p_balance_type IN VARCHAR2,
570 p_display_balance_type IN VARCHAR2,
571 p_category_type IN VARCHAR2
572 )
573 IS
574 CURSOR C IS
575 SELECT budget_period_id,
576 name
577 FROM psb_budget_periods
578 WHERE parent_budget_period_id = p_budget_year_id
579 AND budget_period_type = 'P';
580
581 Recinfo C%ROWTYPE;
582 l_num_of_periods NUMBER;
583 l_first_rec_flag VARCHAR2(1);
584 BEGIN
585 l_num_of_periods := 0;
586 l_first_rec_flag := 'Y';
587
588
589 OPEN C;
590 LOOP
591
592 FETCH C INTO Recinfo;
593
594 EXIT WHEN C%NOTFOUND;
595
596 IF l_first_rec_flag = 'Y' THEN
597
598 l_first_rec_flag := 'N';
599
600 -- Create a column for Totals
601 g_pos_ws_col_no := g_pos_ws_col_no + 1;
602 g_pos_ws_cols(g_pos_ws_col_no).column_type := 'T';
603 g_pos_ws_cols(g_pos_ws_col_no).budget_period_id := NULL;
604 g_pos_ws_cols(g_pos_ws_col_no).budget_period_name := NULL;
605 g_pos_ws_cols(g_pos_ws_col_no).budget_year_id := p_budget_year_id;
606 g_pos_ws_cols(g_pos_ws_col_no).budget_year_name := p_budget_year_name;
607 g_pos_ws_cols(g_pos_ws_col_no).balance_type := p_balance_type;
608 g_pos_ws_cols(g_pos_ws_col_no).display_balance_type := p_display_balance_type;
609 g_pos_ws_cols(g_pos_ws_col_no).year_category_type := p_category_type;
610
611 -- Create a column for Percentage
612 g_pos_ws_col_no := g_pos_ws_col_no + 1;
613 g_pos_ws_cols(g_pos_ws_col_no).column_type := 'P';
614 g_pos_ws_cols(g_pos_ws_col_no).budget_period_id := NULL;
615 g_pos_ws_cols(g_pos_ws_col_no).budget_period_name := NULL;
616 g_pos_ws_cols(g_pos_ws_col_no).budget_year_id := p_budget_year_id;
617 g_pos_ws_cols(g_pos_ws_col_no).budget_year_name := p_budget_year_name;
618 g_pos_ws_cols(g_pos_ws_col_no).balance_type := p_balance_type;
619 g_pos_ws_cols(g_pos_ws_col_no).display_balance_type := p_display_balance_type;
620 g_pos_ws_cols(g_pos_ws_col_no).year_category_type := p_category_type;
621
622
623
624 END IF;
625 l_num_of_periods := l_num_of_periods + 1;
626 g_pos_ws_col_no := g_pos_ws_col_no + 1;
627 g_pos_ws_cols(g_pos_ws_col_no).column_type := 'A';
628 g_pos_ws_cols(g_pos_ws_col_no).budget_period_id := recinfo.budget_period_id;
629 g_pos_ws_cols(g_pos_ws_col_no).budget_period_name := recinfo.name;
630 g_pos_ws_cols(g_pos_ws_col_no).budget_year_id := p_budget_year_id;
631 g_pos_ws_cols(g_pos_ws_col_no).budget_year_name := p_budget_year_name;
632 g_pos_ws_cols(g_pos_ws_col_no).balance_type := p_balance_type;
633 g_pos_ws_cols(g_pos_ws_col_no).display_balance_type := p_display_balance_type;
634 g_pos_ws_cols(g_pos_ws_col_no).year_category_type := p_category_type;
635
636 END LOOP;
637 g_year_num_periods(p_budget_year_id).num_of_periods := l_num_of_periods;
638
639
640 END Set_POS_WS_Cols;
641
642 PROCEDURE Get_Calendar_Years is
643
644 col_no NUMBER := 0;
645 l_init_index BINARY_INTEGER;
646
647 CURSOR C IS
648 SELECT budget_period_id,
649 budget_period_name,
650 year_category_type ,
651 sequence_number
652 FROM psb_ws_budget_years_v
653 WHERE worksheet_id = g_worksheet_id
654 ORDER by sequence_number;
655 Recinfo C%ROWTYPE;
656 Start_Flag_Is_Set BOOLEAN := FALSE;
657
658 BEGIN
659
660 IF g_budget_by_position = 'Y' THEN
661 g_pos_ws_col_no := 0;
662 Clear_POS_WS_Cols;
663 END IF;
664
665 Clear_WS_Cols;
666
667 OPEN C;
668 LOOP
669 FETCH C INTO Recinfo;
670 EXIT WHEN C%NOTFOUND;
671 IF ( Recinfo.year_category_type = 'PY' )
672 OR ( Recinfo.year_category_type = 'CY' )then
673 -- Add a record for Budget
674 col_no := col_no +1;
675 IF col_no > g_max_num_cols THEN
676 EXIT ;
677 END IF;
678 g_ws_cols(col_no).budget_year_id := recinfo.budget_period_id;
682 g_ws_cols(col_no).year_category_type := Recinfo.year_category_type;
679 g_ws_cols(col_no).budget_year_name := recinfo.budget_period_name;
680 g_ws_cols(col_no).balance_type := 'B';
681 g_ws_cols(col_no).display_balance_type := 'Budget';
683
684 IF g_budget_by_position = 'Y' THEN
685 Set_POS_WS_Cols(p_budget_year_id => recinfo.budget_period_id,
686 p_budget_year_name => recinfo.budget_period_name,
687 p_balance_type => 'B',
688 p_display_balance_type => 'Budget',
689 p_category_type => Recinfo.year_category_type
690 );
691 END IF;
692
693 -- Add a record for Actual
694 col_no := col_no +1;
695 IF col_no > g_max_num_cols THEN
696 EXIT ;
697 END IF;
698 g_ws_cols(col_no).budget_year_id := recinfo.budget_period_id;
699 g_ws_cols(col_no).budget_year_name := recinfo.budget_period_name;
700 g_ws_cols(col_no).balance_type := 'A';
701 g_ws_cols(col_no).display_balance_type := 'Actual';
702 g_ws_cols(col_no).year_category_type := Recinfo.year_category_type;
703
704
705 IF g_budget_by_position = 'Y' THEN
706 Set_POS_WS_Cols(p_budget_year_id => recinfo.budget_period_id,
707 p_budget_year_name => recinfo.budget_period_name,
708 p_balance_type => 'A',
709 p_display_balance_type => 'Actual',
710 p_category_type => Recinfo.year_category_type
711 );
712 END IF;
713
714 /* Bug No 2656353 Start */
715 -- Add a record for Encumbrance
716 col_no := col_no +1;
717 IF col_no > g_max_num_cols THEN
718 EXIT ;
719 END IF;
720 g_ws_cols(col_no).budget_year_id := recinfo.budget_period_id;
721 g_ws_cols(col_no).budget_year_name := recinfo.budget_period_name;
722 g_ws_cols(col_no).balance_type := 'X';
723 g_ws_cols(col_no).display_balance_type := 'Encumbrance';
724 g_ws_cols(col_no).year_category_type := Recinfo.year_category_type;
725
726 IF g_budget_by_position = 'Y' THEN
727 Set_POS_WS_Cols(p_budget_year_id => recinfo.budget_period_id,
728 p_budget_year_name => recinfo.budget_period_name,
729 p_balance_type => 'X',
730 p_display_balance_type => 'Encumbrance',
731 p_category_type => Recinfo.year_category_type
732 );
733 END IF;
734 /* Bug No 2656353 End */
735
736 END IF;
737
738 IF ( Recinfo.year_category_type = 'PP' )
739 OR ( Recinfo.year_category_type = 'CY' )then
740 -- Add a record for Estimate
741 col_no := col_no +1;
742 IF col_no > g_max_num_cols THEN
743 EXIT ;
744 END IF;
745 g_ws_cols(col_no).budget_year_id := recinfo.budget_period_id;
746 g_ws_cols(col_no).budget_year_name := recinfo.budget_period_name;
747 g_ws_cols(col_no).balance_type := 'E';
748 g_ws_cols(col_no).display_balance_type := 'Estimate';
749 g_ws_cols(col_no).year_category_type := Recinfo.year_category_type;
750
751 IF g_budget_by_position = 'Y' THEN
752 Set_POS_WS_Cols(p_budget_year_id => recinfo.budget_period_id,
753 p_budget_year_name => recinfo.budget_period_name,
754 p_balance_type => 'E',
755 p_display_balance_type => 'Estimate',
756 p_category_type => Recinfo.year_category_type
757 );
758 END IF;
759
760 END IF;
761
762 END LOOP;
763
764 END Get_Calendar_Years;
765
766 PROCEDURE Get_Saved_Year_Profile is
767 col_no NUMBER := 0;
768 CURSOR C IS
769 SELECT budget_period_id,
770 budget_period_name,
771 year_category_type,
772 fte_flag,
773 actual_flag,
774 budget_flag,
775 estimate_flag,
776 /* Bug No 2656353 Start */
777 encumbrance_flag,
778 /* Bug No 2656353 End */
779 start_year_flag
780 FROM psb_ws_year_profiles_v
781 /* Following 2 lines commented and next 2 lines added for DDSP */
782 -- where WORKSHEET_ID = g_worksheet_id
783 -- and USER_ID = g_user_id
784 where WORKSHEET_ID = g_profile_worksheet_id
785 and (USER_ID = g_profile_user_id or (g_profile_user_id IS NULL AND USER_ID IS NULL))
786 ORDER by sequence_number;
787 Recinfo C%ROWTYPE;
788 Start_Flag_Is_Set BOOLEAN := FALSE;
789
790 BEGIN
791 col_no := 0;
792 Clear_WS_Cols;
793 IF g_budget_by_position = 'Y' THEN
794 g_pos_ws_col_no := 0;
795 Clear_POS_WS_Cols;
796 END IF;
797
798
799 OPEN C;
800 LOOP
801
802 FETCH C INTO Recinfo;
803 EXIT WHEN C%NOTFOUND;
804
805 IF ( Recinfo.fte_flag = 'Y') then
806 col_no := col_no +1;
807 IF col_no > g_max_num_cols THEN
808 EXIT ;
809 END IF;
810 g_ws_cols(col_no).budget_year_id := recinfo.budget_period_id;
811 g_ws_cols(col_no).budget_year_name := recinfo.budget_period_name;
812 g_ws_cols(col_no).balance_type := 'F';
813 g_ws_cols(col_no).display_balance_type := 'FTE';
817 IF g_budget_by_position = 'Y' THEN
814 g_ws_cols(col_no).year_category_type := recinfo.year_category_type;
815
816
818 Set_POS_WS_Cols(p_budget_year_id => recinfo.budget_period_id,
819 p_budget_year_name => recinfo.budget_period_name,
820 p_balance_type => 'F',
821 p_display_balance_type => 'FTE',
822 p_category_type => Recinfo.year_category_type
823 );
824 END IF;
825
826
827 END IF;
828
829 IF ( Recinfo.Budget_flag = 'Y') then
830 col_no := col_no +1;
831 IF col_no > g_max_num_cols THEN
832 EXIT ;
833 END IF;
834 g_ws_cols(col_no).budget_year_id := recinfo.budget_period_id;
835 g_ws_cols(col_no).budget_year_name := recinfo.budget_period_name;
836 g_ws_cols(col_no).balance_type := 'B';
837 g_ws_cols(col_no).display_balance_type := 'Budget';
838 g_ws_cols(col_no).year_category_type := Recinfo.year_category_type;
839
840 IF g_budget_by_position = 'Y' THEN
841 Set_POS_WS_Cols(p_budget_year_id => recinfo.budget_period_id,
842 p_budget_year_name => recinfo.budget_period_name,
843 p_balance_type => 'B',
844 p_display_balance_type => 'Budget',
845 p_category_type => Recinfo.year_category_type
846 );
847 END IF;
848
849
850 END IF;
851
852
853 IF ( Recinfo.Actual_flag = 'Y') then
854 -- Add a record for Actual
855 col_no := col_no +1;
856 IF col_no > g_max_num_cols THEN
857 EXIT ;
858 END IF;
859 g_ws_cols(col_no).budget_year_id := recinfo.budget_period_id;
860 g_ws_cols(col_no).budget_year_name := recinfo.budget_period_name;
861 g_ws_cols(col_no).balance_type := 'A';
862 g_ws_cols(col_no).display_balance_type := 'Actual';
863 g_ws_cols(col_no).year_category_type := Recinfo.year_category_type;
864
865
866 IF g_budget_by_position = 'Y' THEN
867 Set_POS_WS_Cols(p_budget_year_id => recinfo.budget_period_id,
868 p_budget_year_name => recinfo.budget_period_name,
869 p_balance_type => 'A',
870 p_display_balance_type => 'Actual',
871 p_category_type => Recinfo.year_category_type
872 );
873 END IF;
874
875 END IF;
876
877 /* Bug No 2656353 Start */
878 IF ( Recinfo.Encumbrance_flag = 'Y') then
879 -- Add a record for Encumbrance
880 col_no := col_no +1;
881 IF col_no > g_max_num_cols THEN
882 EXIT ;
883 END IF;
884 g_ws_cols(col_no).budget_year_id := recinfo.budget_period_id;
885 g_ws_cols(col_no).budget_year_name := recinfo.budget_period_name;
886 g_ws_cols(col_no).balance_type := 'X';
887 g_ws_cols(col_no).display_balance_type := 'Encumbrance';
888 g_ws_cols(col_no).year_category_type := Recinfo.year_category_type;
889
890
891 IF g_budget_by_position = 'Y' THEN
892 Set_POS_WS_Cols(p_budget_year_id => recinfo.budget_period_id,
893 p_budget_year_name => recinfo.budget_period_name,
894 p_balance_type => 'X',
895 p_display_balance_type => 'Encumbrance',
896 p_category_type => Recinfo.year_category_type
897 );
898 END IF;
899
900 END IF;
901 /* Bug No 2656353 End */
902
903 IF ( Recinfo.Estimate_flag = 'Y') then
904 -- Add a record for Estimate
905 col_no := col_no +1;
906 IF col_no > g_max_num_cols THEN
907 EXIT ;
908 END IF;
909 g_ws_cols(col_no).budget_year_id := recinfo.budget_period_id;
910 g_ws_cols(col_no).budget_year_name := recinfo.budget_period_name;
911 g_ws_cols(col_no).balance_type := 'E';
912 g_ws_cols(col_no).display_balance_type := 'Estimate';
913 g_ws_cols(col_no).year_category_type := Recinfo.year_category_type;
914
915
916 IF g_budget_by_position = 'Y' THEN
917
918
919 Set_POS_WS_Cols(p_budget_year_id => recinfo.budget_period_id,
920 p_budget_year_name => recinfo.budget_period_name,
921 p_balance_type => 'E',
922 p_display_balance_type => 'Estimate',
923 p_category_type => Recinfo.year_category_type
924 );
925 END IF;
926
927 END IF;
928
929 END LOOP;
930
931 END Get_Saved_Year_Profile;
932
933 PROCEDURE Set_WS_Matrix_View IS
934 BEGIN
935
936 g_ws_line_year_rec.stage := g_stage_id;
937 g_ws_line_year_rec.c1_year_id := g_ws_cols(1).budget_year_id;
938 g_ws_line_year_rec.c2_year_id := g_ws_cols(2).budget_year_id;
939 g_ws_line_year_rec.c3_year_id := g_ws_cols(3).budget_year_id;
940 g_ws_line_year_rec.c4_year_id := g_ws_cols(4).budget_year_id;
941 g_ws_line_year_rec.c5_year_id := g_ws_cols(5).budget_year_id;
942 g_ws_line_year_rec.c6_year_id := g_ws_cols(6).budget_year_id;
943 g_ws_line_year_rec.c7_year_id := g_ws_cols(7).budget_year_id;
944 g_ws_line_year_rec.c8_year_id := g_ws_cols(8).budget_year_id;
948 g_ws_line_year_rec.c12_year_id := g_ws_cols(12).budget_year_id;
945 g_ws_line_year_rec.c9_year_id := g_ws_cols(9).budget_year_id;
946 g_ws_line_year_rec.c10_year_id := g_ws_cols(10).budget_year_id;
947 g_ws_line_year_rec.c11_year_id := g_ws_cols(11).budget_year_id;
949 g_ws_line_year_rec.c1_amount_type := g_ws_cols(1).balance_type;
950 g_ws_line_year_rec.c2_amount_type := g_ws_cols(2).balance_type;
951 g_ws_line_year_rec.c3_amount_type := g_ws_cols(3).balance_type;
952 g_ws_line_year_rec.c4_amount_type := g_ws_cols(4).balance_type;
953 g_ws_line_year_rec.c5_amount_type := g_ws_cols(5).balance_type;
954 g_ws_line_year_rec.c6_amount_type := g_ws_cols(6).balance_type;
955 g_ws_line_year_rec.c7_amount_type := g_ws_cols(7).balance_type;
956 g_ws_line_year_rec.c8_amount_type := g_ws_cols(8).balance_type;
957 g_ws_line_year_rec.c9_amount_type := g_ws_cols(9).balance_type;
958 g_ws_line_year_rec.c10_amount_type := g_ws_cols(10).balance_type;
959 g_ws_line_year_rec.c11_amount_type := g_ws_cols(11).balance_type;
960 g_ws_line_year_rec.c12_amount_type := g_ws_cols(12).balance_type;
961 psb_ws_matrix.Set_WS_Line_Years(g_ws_line_year_rec);
962
963 END Set_WS_Matrix_View;
964
965
966
967 PROCEDURE Populate_WS_Columns IS
968 BEGIN
969
970
971 for i in 1..g_total_budget_years loop
972
973 insert into psb_ws_columns_i(
974 EXPORT_ID,
975 EXPORT_WORKSHEET_TYPE,
976 COLUMN_NUMBER,
977 BUDGET_YEAR_ID,
978 BUDGET_YEAR_NAME,
979 BALANCE_TYPE,
980 DISPLAYED_BALANCE_TYPE,
981 YEAR_CATEGORY_TYPE,
982 DISPLAYED_YEAR_CATEGORY_TYPE,
983 LAST_UPDATE_DATE,
984 LAST_UPDATED_BY,
985 LAST_UPDATE_LOGIN,
986 CREATED_BY,
987 CREATED_DATE)
988 values(
989 g_export_id,
990 'A', -- for All Accounts
991 i,
992 g_ws_cols(i).budget_year_id,
993 g_ws_cols(i).budget_year_name,
994 g_ws_cols(i).balance_type,
995 g_ws_cols(i).display_balance_type, -- Get translated msg
996 g_ws_cols(i).year_category_type,
997 g_ws_cols(i).year_category_type, -- Get translated msg
998 SYSDATE,
999 g_user_id,
1000 g_user_id,
1001 g_user_id,
1002 SYSDATE
1003 );
1004
1005 end loop;
1006
1007 END Populate_WS_Columns;
1008
1009
1010 PROCEDURE Populate_POS_WS_Columns IS
1011 BEGIN
1012 for i in 1..g_max_num_pos_ws_cols loop
1013 IF g_pos_ws_cols(i).budget_year_id IS NULL THEN
1014 EXIT;
1015
1016 END IF;
1017 insert into psb_ws_columns_i(
1018 EXPORT_ID,
1019 EXPORT_WORKSHEET_TYPE,
1020 COLUMN_NUMBER,
1021 COLUMN_TYPE,
1022 BUDGET_YEAR_ID,
1023 BUDGET_YEAR_NAME,
1024 BUDGET_PERIOD_ID,
1025 BUDGET_PERIOD_NAME,
1026 BALANCE_TYPE,
1027 DISPLAYED_BALANCE_TYPE,
1028 YEAR_CATEGORY_TYPE,
1029 DISPLAYED_YEAR_CATEGORY_TYPE,
1030 LAST_UPDATE_DATE,
1031 LAST_UPDATED_BY,
1032 LAST_UPDATE_LOGIN,
1033 CREATED_BY,
1034 CREATED_DATE)
1035 values(
1036 g_export_id,
1037 'P', -- for Position Accounts
1038 i,
1039 g_pos_ws_cols(i).column_type,
1040 g_pos_ws_cols(i).budget_year_id,
1041 g_pos_ws_cols(i).budget_year_name,
1042 g_pos_ws_cols(i).budget_period_id,
1043 g_pos_ws_cols(i).budget_period_name,
1044 g_pos_ws_cols(i).balance_type,
1045 g_pos_ws_cols(i).display_balance_type, -- Get translated msg
1046 g_pos_ws_cols(i).year_category_type,
1047 g_pos_ws_cols(i).year_category_type, -- Get translated msg
1048 SYSDATE,
1049 g_user_id,
1050 g_user_id,
1051 g_user_id,
1052 SYSDATE
1053 );
1054
1055 end loop;
1056
1057 END Populate_POS_WS_Columns;
1058
1059 PROCEDURE Populate_WS_Lines IS
1060 l_account_segments VARCHAR2(1000);
1061 l_account_desc VARCHAR2(1000);
1062
1063 l_position_account_flag VARCHAR2(1);
1064 l_ccid_type VARCHAR2(30);
1065 l_return_status VARCHAR2(1);
1066 l_msg_count NUMBER;
1067 l_msg_data VARCHAR2(2000);
1068 l_root_budget_group_id NUMBER;
1069 BEGIN
1070
1071 FOR l_bg_rec IN
1072 (
1073 SELECT nvl(root_budget_group_id, budget_group_id) root_budget_group_id
1074 FROM psb_budget_groups_v
1075 WHERE budget_group_id = g_budget_group_id
1076 )
1077 LOOP
1078 l_root_budget_group_id := l_bg_rec.root_budget_group_id;
1079 END LOOP;
1080
1081
1082 FOR wal_rec IN (
1083 select
1084 CODE_COMBINATION_ID,
1085 ACCOUNT_TYPE,
1086 SERVICE_PACKAGE_ID,
1087 SERVICE_PACKAGE_NAME,
1088 PRIORITY,
1089 TEMPLATE_ID,
1090 CURRENCY_CODE,
1091 COLUMN1,
1092 COLUMN2,
1093 COLUMN3,
1094 COLUMN4,
1095 COLUMN5,
1096 COLUMN6,
1097 COLUMN7,
1098 COLUMN8,
1099 COLUMN9,
1100 COLUMN10,
1101 COLUMN11,
1102 COLUMN12
1103 from PSB_WS_LINE_YEAR_XL_V
1104 where WORKSHEET_ID = g_worksheet_id
1105
1106 /* Bug No 2008329 Start */
1107 -- and ( g_account_flag = 'A' or ACCOUNT_TYPE = g_account_flag )
1111 )
1108 and ( g_account_flag = 'T' or ACCOUNT_TYPE = g_account_flag
1109 OR ( account_type = DECODE(g_account_flag,'P','R')
1110 OR account_type = DECODE(g_account_flag,'P','E')
1112 OR ( account_type = DECODE(g_account_flag,'N','A' )
1113 OR account_type = DECODE(g_account_flag,'N','L')
1114 )
1115 OR ( account_type = DECODE(g_account_flag,'B','C' )
1116 OR account_type = DECODE(g_account_flag,'B','D')
1117 )
1118
1119 )
1120 /* Bug No 2008329 End */
1121
1122 and ( (g_template_id is null and TEMPLATE_ID is null)
1123 or
1124 (TEMPLATE_ID = g_template_id) )
1125 and ( ( g_currency_flag = 'C' and CURRENCY_CODE <> 'STAT' )
1126 or
1127 ( g_currency_flag = 'S' and CURRENCY_CODE = 'STAT')
1128 )
1129 and ( g_service_package_flag = 'A' or
1130 SERVICE_PACKAGE_ID in
1131 (SELECT SERVICE_PACKAGE_ID
1132 FROM PSB_WS_SERVICE_PKG_PROFILES_V
1133 /* Following 2 lines commented and next 2 lines added for DDSP */
1134 -- where WORKSHEET_ID = g_worksheet_id
1135 -- and USER_ID = g_user_id
1136 where WORKSHEET_ID = g_profile_worksheet_id
1137 and (USER_ID = g_profile_user_id or (g_profile_user_id IS NULL AND USER_ID IS NULL)) ) )
1138 )
1139
1140 LOOP
1141
1142 IF FND_FLEX_KEYVAL.validate_ccid('SQLGL', 'GL#', g_coa_id, wal_rec.CODE_COMBINATION_ID) THEN
1143 l_account_segments := substr(FND_FLEX_KEYVAL.concatenated_values,1,1000);
1144 l_account_desc := substr(FND_FLEX_KEYVAL.concatenated_descriptions,1,1000);
1145 END IF;
1146
1147 l_position_account_flag := NULL;
1148
1149 -- Set the value for position_account_flag
1150
1151 -- added the following IF condition as part of bug fix 3575197.
1152 IF NVL(g_budget_by_position,'N') = 'Y' THEN
1153 BEGIN
1154 PSB_WS_ACCT_PVT.CHECK_CCID_TYPE
1155 (
1156 p_api_version => 1.0,
1157 p_init_msg_list => FND_API.G_TRUE,
1158 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1159 p_return_status => l_return_status,
1160 p_msg_count => l_msg_count,
1161 p_msg_data => l_msg_data,
1162 --
1163 p_ccid_type => l_ccid_type,
1164 p_flex_code => g_coa_id,
1165 p_ccid => wal_rec.code_combination_id,
1166 p_budget_group_id => l_root_budget_group_id
1167 );
1168 -- If not able to decide status set the record to non updateable
1169 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1170 l_position_account_flag := 'Y';
1171 ELSE
1172 IF l_ccid_type = 'PERSONNEL_SERVICES' THEN
1173 l_position_account_flag := 'Y';
1174 ELSE
1175 l_position_account_flag := 'N';
1176 END IF;
1177 END IF;
1178 EXCEPTION
1179 WHEN OTHERS THEN
1180 l_position_account_flag := 'Y';
1181 END;
1182 ELSE
1183 l_position_account_flag := 'N';
1184 END IF;
1185
1186 insert into psb_ws_line_balances_i(
1187 EXPORT_ID,
1188 EXPORT_WORKSHEET_TYPE,
1189 CODE_COMBINATION_ID,
1190 CONCATENATED_ACCOUNT,
1191 ACCOUNT_DESCRIPTION,
1192 SERVICE_PACKAGE_ID,
1193 SERVICE_PACKAGE_NAME,
1194 POSITION_ACCOUNT_FLAG,
1195 AMOUNT1,
1196 AMOUNT2,
1197 AMOUNT3,
1198 AMOUNT4,
1199 AMOUNT5,
1200 AMOUNT6,
1201 AMOUNT7,
1202 AMOUNT8,
1203 AMOUNT9,
1204 AMOUNT10,
1205 AMOUNT11,
1206 AMOUNT12,
1207 LAST_UPDATE_DATE,
1208 LAST_UPDATED_BY,
1209 LAST_UPDATE_LOGIN,
1210 CREATED_BY,
1211 CREATED_DATE)
1212 values(
1213 g_export_id,
1214 'A',
1215 wal_rec.CODE_COMBINATION_ID,
1216 l_account_segments,
1217 l_account_desc,
1218 wal_rec.SERVICE_PACKAGE_ID,
1219 wal_rec.SERVICE_PACKAGE_NAME,
1220 l_position_account_flag,
1221 wal_rec.COLUMN1,
1222 wal_rec.COLUMN2,
1223 wal_rec.COLUMN3,
1224 wal_rec.COLUMN4,
1225 wal_rec.COLUMN5,
1226 wal_rec.COLUMN6,
1227 wal_rec.COLUMN7,
1228 wal_rec.COLUMN8,
1229 wal_rec.COLUMN9,
1230 wal_rec.COLUMN10,
1231 wal_rec.COLUMN11,
1232 wal_rec.COLUMN12,
1233 SYSDATE,
1234 g_user_id,
1235 g_user_id,
1236 g_user_id,
1237 SYSDATE
1238 );
1239
1240 END LOOP;
1241
1242 END Populate_WS_Lines;
1243
1244
1245 PROCEDURE Clear_PS_Elements_Tbl is
1246 i BINARY_INTEGER;
1247 BEGIN
1248 i := g_ps_elements.FIRST;
1249
1250 WHILE i IS NOT NULL LOOP
1251 g_ps_elements(i).pay_element_id := null;
1252 g_ps_elements(i).pay_element_period_type := null;
1253 g_ps_elements(i).pay_element_name := null;
1254 g_ps_elements(i).pay_element_set_id := null;
1255
1256 i := g_ps_elements.NEXT(i);
1257
1258 END LOOP;
1259 END Clear_PS_Elements_Tbl;
1260
1261 PROCEDURE Cache_PS_Elements
1265 p_pay_element_set_id NUMBER
1262 (p_pay_element_id NUMBER,
1263 p_pay_element_name VARCHAR2,
1264 p_pay_element_period_type VARCHAR2,
1266 )
1267 IS
1268 l_ps_element_exists BOOLEAN := FALSE;
1269 BEGIN
1270 -- Add the PS element only if it doesn't exist already
1271 IF g_ps_elements.COUNT > 0 THEN
1272 FOR i IN 1.. g_ps_elements.COUNT LOOP
1273 IF g_ps_elements(i).pay_element_id = p_pay_element_id THEN
1274 l_ps_element_exists := TRUE;
1275 exit;
1276 END IF;
1277 END LOOP;
1278 IF NOT l_ps_element_exists THEN
1279 g_ps_elements(g_ps_elements.COUNT + 1 ).pay_element_id := p_pay_element_id;
1280 g_ps_elements(g_ps_elements.COUNT + 1 ).pay_element_name := p_pay_element_name;
1281 g_ps_elements(g_ps_elements.COUNT + 1 ).pay_element_period_type := p_pay_element_period_type;
1282 g_ps_elements(g_ps_elements.COUNT + 1 ).pay_element_set_id := p_pay_element_set_id;
1283 END IF;
1284 ELSE
1285 g_ps_elements(1).pay_element_id := p_pay_element_id;
1286 g_ps_elements(1).pay_element_name := p_pay_element_name;
1287 g_ps_elements(1).pay_element_period_type := p_pay_element_period_type;
1288 g_ps_elements(1).pay_element_set_id := p_pay_element_set_id;
1289 END IF;
1290
1291
1292
1293 END Cache_PS_Elements;
1294
1295 -- Cache position data for the calendar period
1296 PROCEDURE Cache_Position_Data
1297 (
1298 p_return_status OUT NOCOPY VARCHAR2,
1299 p_position_line_id IN NUMBER,
1300 p_position_id IN NUMBER,
1301 p_start_date IN DATE,
1302 p_end_date IN DATE
1303 )
1304 IS
1305
1306 l_return_status VARCHAR2(1);
1307 l_position_id NUMBER;
1308 l_start_date DATE;
1309 l_end_date DATE;
1310
1311 cursor c_Positions is
1312 select a.position_id,
1313 a.name,
1314 a.effective_start_date,
1315 a.effective_end_date
1316 from PSB_POSITIONS a,
1317 PSB_WS_POSITION_LINES b
1318 where a.position_id = b.position_id
1319 and b.position_line_id = p_position_line_id;
1320
1321 cursor c_Element_Assignments is
1322 select worksheet_id,
1323 pay_element_id,
1324 pay_element_option_id,
1325 pay_basis,
1326 element_value_type,
1327 element_value,
1328 effective_start_date,
1329 effective_end_date
1330 from PSB_POSITION_ASSIGNMENTS
1331 where (worksheet_id is null or worksheet_id = g_global_worksheet_id)
1332 and element_value_type = 'PS'
1333 and currency_code = g_currency_code
1334 and assignment_type = 'ELEMENT'
1335 and (((effective_start_date <= l_end_date)
1336 and (effective_end_date is null))
1337 or ((effective_start_date between l_start_date and l_end_date)
1338 or (effective_end_date between l_start_date and l_end_date)
1339 or ((effective_start_date < l_start_date)
1340 and (effective_end_date > l_end_date))))
1341 and position_id = l_position_id
1342 order by effective_start_date,
1343 effective_end_date,
1344 element_value desc;
1345
1346 cursor c_Element_Rates is
1347 select a.worksheet_id,
1348 a.pay_element_id,
1349 a.pay_element_option_id,
1350 a.pay_basis,
1351 a.element_value_type,
1352 a.element_value,
1353 a.formula_id,
1354 a.effective_start_date,
1355 a.effective_end_date
1356 from PSB_PAY_ELEMENT_RATES a,
1357 PSB_PAY_ELEMENTS b
1358 where (a.worksheet_id is null or a.worksheet_id = g_global_worksheet_id)
1359 and a.currency_code = g_currency_code
1360 and a.element_value_type = 'PS'
1361 and exists
1362 (select 1
1363 from PSB_POSITION_ASSIGNMENTS c
1364 where nvl(c.pay_element_option_id, FND_API.G_MISS_NUM) = nvl(a.pay_element_option_id, FND_API.G_MISS_NUM)
1365 and (c.worksheet_id is null or c.worksheet_id = g_global_worksheet_id)
1366 and c.currency_code = g_currency_code
1367 and (((c.effective_start_date <= l_end_date)
1368 and (c.effective_end_date is null))
1369 or ((c.effective_start_date between l_start_date and l_end_date)
1370 or (c.effective_end_date between l_start_date and l_end_date)
1371 or ((c.effective_start_date < l_start_date)
1372 and (c.effective_end_date > l_end_date))))
1373 and c.pay_element_id = a.pay_element_id
1374 and c.position_id = l_position_id)
1375 and (((a.effective_start_date <= l_end_date)
1376 and (a.effective_end_date is null))
1377 or ((a.effective_start_date between l_start_date and l_end_date)
1378 or (a.effective_end_date between l_start_date and l_end_date)
1379 or ((a.effective_start_date < l_start_date)
1380 and (a.effective_end_date > l_end_date))))
1381 and a.pay_element_id = b.pay_element_id
1382 and b.business_group_id = g_business_group_id
1383 and b.data_extract_id = g_data_extract_id
1384 order by a.worksheet_id,
1385 a.effective_start_date,
1386 a.effective_end_date,
1387 a.element_value desc;
1388
1389 BEGIN
1390 l_position_id := p_position_id;
1391 l_start_date := p_start_date;
1392 l_end_date := p_end_date;
1393
1394 -- Initialize the Cache
1395 for l_init_index in 1..g_poselem_assignments.Count loop
1396 g_poselem_assignments(l_init_index).worksheet_id := null;
1400 g_poselem_assignments(l_init_index).pay_element_option_id := null;
1397 g_poselem_assignments(l_init_index).start_date := null;
1398 g_poselem_assignments(l_init_index).end_date := null;
1399 g_poselem_assignments(l_init_index).pay_element_id := null;
1401 g_poselem_assignments(l_init_index).pay_basis := null;
1402 g_poselem_assignments(l_init_index).element_value_type := null;
1403 g_poselem_assignments(l_init_index).element_value := null;
1404 end loop;
1405
1406 g_num_poselem_assignments := 0;
1407
1408 for l_init_index in 1..g_poselem_rates.Count loop
1409 g_poselem_rates(l_init_index).worksheet_id := null;
1410 g_poselem_rates(l_init_index).start_date := null;
1411 g_poselem_rates(l_init_index).end_date := null;
1412 g_poselem_rates(l_init_index).pay_element_id := null;
1413 g_poselem_rates(l_init_index).pay_element_option_id := null;
1414 g_poselem_rates(l_init_index).pay_basis := null;
1415 g_poselem_rates(l_init_index).element_value_type := null;
1416 g_poselem_rates(l_init_index).element_value := null;
1417 g_poselem_rates(l_init_index).formula_id := null;
1418 end loop;
1419
1420 g_num_poselem_rates := 0;
1421
1422
1423 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1424 raise FND_API.G_EXC_ERROR;
1425 end if;
1426
1427 for c_Element_Assignments_Rec in c_Element_Assignments loop
1428
1429 g_num_poselem_assignments := g_num_poselem_assignments + 1;
1430
1431 g_poselem_assignments(g_num_poselem_assignments).worksheet_id := c_Element_Assignments_Rec.worksheet_id;
1432 g_poselem_assignments(g_num_poselem_assignments).start_date := c_Element_Assignments_Rec.effective_start_date;
1433 g_poselem_assignments(g_num_poselem_assignments).end_date := c_Element_Assignments_Rec.effective_end_date;
1434 g_poselem_assignments(g_num_poselem_assignments).pay_element_id := c_Element_Assignments_Rec.pay_element_id;
1435 g_poselem_assignments(g_num_poselem_assignments).pay_element_option_id := c_Element_Assignments_Rec.pay_element_option_id;
1436 g_poselem_assignments(g_num_poselem_assignments).pay_basis := c_Element_Assignments_Rec.pay_basis;
1437 g_poselem_assignments(g_num_poselem_assignments).element_value_type := c_Element_Assignments_Rec.element_value_type;
1438 g_poselem_assignments(g_num_poselem_assignments).element_value := c_Element_Assignments_Rec.element_value;
1439
1440 end loop;
1441
1442 for c_Element_Rates_Rec in c_Element_Rates loop
1443
1444 g_num_poselem_rates := g_num_poselem_rates + 1;
1445
1446 g_poselem_rates(g_num_poselem_rates).worksheet_id := c_Element_Rates_Rec.worksheet_id;
1447 g_poselem_rates(g_num_poselem_rates).start_date := c_Element_Rates_Rec.effective_start_date;
1448 g_poselem_rates(g_num_poselem_rates).end_date := c_Element_Rates_Rec.effective_end_date;
1449 g_poselem_rates(g_num_poselem_rates).pay_element_id := c_Element_Rates_Rec.pay_element_id;
1450 g_poselem_rates(g_num_poselem_rates).pay_element_option_id := c_Element_Rates_Rec.pay_element_option_id;
1451 g_poselem_rates(g_num_poselem_rates).pay_basis := c_Element_Rates_Rec.pay_basis;
1452 g_poselem_rates(g_num_poselem_rates).element_value_type := c_Element_Rates_Rec.element_value_type;
1453 g_poselem_rates(g_num_poselem_rates).element_value := c_Element_Rates_Rec.element_value;
1454 g_poselem_rates(g_num_poselem_rates).formula_id := c_Element_Rates_Rec.formula_id;
1455
1456 end loop;
1457
1458 p_return_status := FND_API.G_RET_STS_SUCCESS;
1459 EXCEPTION
1460
1461 when FND_API.G_EXC_ERROR then
1462 p_return_status := FND_API.G_RET_STS_ERROR;
1463
1464 when FND_API.G_EXC_UNEXPECTED_ERROR then
1465 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1466
1467 when OTHERS then
1468 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1469
1470 END Cache_Position_Data;
1471
1472
1473 PROCEDURE Populate_PS_Element_Pct
1474 ( p_budget_year_id IN NUMBER,
1475 p_position_id IN NUMBER,
1476 p_pay_element_id IN NUMBER,
1477 p_element_period_type IN VARCHAR2,
1478 p_found_ps_pct OUT NOCOPY BOOLEAN
1479 )
1480 IS
1481 l_year_num_periods NUMBER;
1482 l_budget_period_id NUMBER;
1483 l_budget_period_type VARCHAR2(10);
1484 l_budget_period_start_date DATE;
1485 l_budget_period_end_date DATE;
1486
1487 l_pay_element_id NUMBER;
1488 l_pay_element_option_id NUMBER;
1489
1490 l_element_assigned VARCHAR2(1);
1491 l_ws_assignment VARCHAR2(1);
1492 l_element_value NUMBER;
1493 l_pos_assignment VARCHAR2(1);
1494 l_year_period_num NUMBER;
1495 l_assign_index BINARY_INTEGER;
1496 l_value_from_elem_rates VARCHAR2(1);
1497 l_hrms_factor NUMBER;
1498 BEGIN
1499 l_pay_element_id := p_pay_element_id;
1500 l_year_num_periods := g_year_num_periods(p_budget_year_id).num_of_periods;
1501
1502 -- for each budget period in the budget year
1503 FOR l_period_num IN 1.. l_year_num_periods LOOP
1504 l_element_value := NULL;
1505 -- Loop thru cached calendar to get start and end dates for the budget period
1506 l_year_period_num := 0;
1507
1508 for l_period_index in 1..PSB_WS_ACCT1.g_num_budget_periods loop
1509
1510 if PSB_WS_ACCT1.g_budget_periods(l_period_index).budget_year_id = p_budget_year_id then
1511 l_year_period_num := l_year_period_num + 1;
1512 IF l_year_period_num = l_period_num THEN
1516 l_budget_period_start_date := PSB_WS_ACCT1.g_budget_periods(l_period_index).start_date;
1513
1514 l_budget_period_id := PSB_WS_ACCT1.g_budget_periods(l_period_index).budget_period_id;
1515 l_budget_period_type := PSB_WS_ACCT1.g_budget_periods(l_period_index).budget_period_type;
1517 l_budget_period_end_date := PSB_WS_ACCT1.g_budget_periods(l_period_index).end_date;
1518 exit;
1519 END IF;
1520
1521 end if;
1522 end loop;
1523
1524 -- Set Allow_Position_Import Flag
1525 for factor_rec in
1526 ( select factor
1527 from PSB_HRMS_FACTORS
1528 where hrms_period_type = p_element_period_type
1529 and budget_period_type = l_budget_period_type)
1530 loop
1531 l_hrms_factor := factor_rec.factor;
1532 IF l_hrms_factor < 1 THEN
1533 g_allow_position_import := 'N';
1534 END IF;
1535 exit;
1536 end loop;
1537
1538 -- Get the assignment data
1539 l_ws_assignment := FND_API.G_FALSE;
1540 for l_assign_index in 1..g_num_poselem_assignments loop
1541
1542 if ((g_poselem_assignments(l_assign_index).pay_element_id = l_pay_element_id) and
1543 (g_poselem_assignments(l_assign_index).worksheet_id is not null) and
1544 (((g_poselem_assignments(l_assign_index).start_date <= l_budget_period_start_date) and
1545 (g_poselem_assignments(l_assign_index).end_date is null)) or
1546 ((g_poselem_assignments(l_assign_index).start_date between l_budget_period_start_date and l_budget_period_end_date) or
1547 (g_poselem_assignments(l_assign_index).end_date between l_budget_period_start_date and l_budget_period_end_date) or
1548 ((g_poselem_assignments(l_assign_index).start_date < l_budget_period_start_date) and
1549 (g_poselem_assignments(l_assign_index).end_date > l_budget_period_end_date))))) then
1550 begin
1551 l_ws_assignment := FND_API.G_TRUE;
1552 l_element_value := g_poselem_assignments(l_assign_index).element_value;
1553 l_pay_element_option_id := g_poselem_assignments(l_assign_index).pay_element_option_id;
1554 exit;
1555 end;
1556 end if;
1557 end loop;
1558
1559 l_pos_assignment := FND_API.G_FALSE;
1560 if not FND_API.to_Boolean(l_ws_assignment) then
1561 begin
1562
1563 for l_assign_index in 1..g_num_poselem_assignments loop
1564
1565 if ((g_poselem_assignments(l_assign_index).pay_element_id = l_pay_element_id) and
1566 (g_poselem_assignments(l_assign_index).worksheet_id is null) and
1567 (((g_poselem_assignments(l_assign_index).start_date <= l_budget_period_end_date) and
1568 (g_poselem_assignments(l_assign_index).end_date is null)) or
1569 ((g_poselem_assignments(l_assign_index).start_date between l_budget_period_start_date and l_budget_period_end_date) or
1570 (g_poselem_assignments(l_assign_index).end_date between l_budget_period_start_date and l_budget_period_end_date) or
1571 ((g_poselem_assignments(l_assign_index).start_date < l_budget_period_start_date) and
1572 (g_poselem_assignments(l_assign_index).end_date > l_budget_period_end_date))))) then
1573 begin
1574
1575 l_pos_assignment := FND_API.G_TRUE;
1576 l_pay_element_option_id := g_poselem_assignments(l_assign_index).pay_element_option_id;
1577 l_element_value := g_poselem_assignments(l_assign_index).element_value;
1578 exit;
1579 end;
1580 end if;
1581
1582 end loop;
1583
1584 end;
1585 end if; -- if not ws assignment
1586
1587 -- See if the value is arrived from element rates table
1588 l_value_from_elem_rates := FND_API.G_FALSE;
1589 if l_element_value is null then
1590
1591 for l_rate_index in 1..g_num_poselem_rates loop
1592
1593 if ((g_poselem_rates(l_rate_index).pay_element_id = l_pay_element_id) and
1594 (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
1595 (((g_poselem_rates(l_rate_index).start_date <= l_budget_period_end_date) and
1596 (g_poselem_rates(l_rate_index).end_date is null)) or
1597 ((g_poselem_rates(l_rate_index).start_date between l_budget_period_start_date and l_budget_period_end_date) or
1598 (g_poselem_rates(l_rate_index).end_date between l_budget_period_start_date and l_budget_period_end_date) or
1599 ((g_poselem_rates(l_rate_index).start_date < l_budget_period_start_date) and
1600 (g_poselem_rates(l_rate_index).end_date > l_budget_period_end_date))))) then
1601 l_value_from_elem_rates := FND_API.G_TRUE;
1602 l_element_value := g_poselem_rates(l_rate_index).element_value;
1603 exit;
1604 end if;
1605
1606 end loop;
1607
1608 end if; -- Element value is null
1609
1610 if l_element_value is not null then
1611 p_found_ps_pct := TRUE;
1612 g_ps_element_pct(l_period_num).amount:= l_element_value;
1613 end if;
1614
1615 END LOOP; -- For each budget period in the budget year
1616
1617 END Populate_PS_Element_Pct;
1618
1619
1620 PROCEDURE Populate_POS_WS_Lines IS
1621 l_account_segments VARCHAR2(1000);
1622 l_account_desc VARCHAR2(1000);
1623
1624 l_budget_calendar_id NUMBER;
1625 l_calendar_start_date DATE;
1626 l_calendar_end_date DATE;
1627 l_cy_end_date DATE;
1628 l_pp_start_date DATE;
1629
1630
1631
1632 l_position_line_id NUMBER;
1633 l_position_id NUMBER;
1634 l_position_name VARCHAR2(240);
1638 l_service_package_id NUMBER;
1635 l_code_combination_id NUMBER;
1636 l_budget_group_id NUMBER;
1637 l_element_set_id NUMBER;
1639 l_service_package_name VARCHAR2(30);
1640
1641 l_element_id NUMBER;
1642 l_element_name VARCHAR2(30);
1643 l_follow_salary VARCHAR2(1);
1644
1645 l_template_id NUMBER;
1646 l_wlbi_start_index NUMBER;
1647 l_wlbi_end_index NUMBER;
1648 l_budget_year_id NUMBER;
1649 l_balance_type VARCHAR2(1);
1650 l_year_num_periods NUMBER; -- number of periods in the budget year
1651 l_wal_col_index NUMBER;
1652
1653 l_start_stage_seq NUMBER;
1654 l_current_stage_seq NUMBER;
1655
1656 l_wal_rec_found VARCHAR2(1);
1657 l_fte_rec_found VARCHAR2(1);
1658 l_element_value_type VARCHAR2(2);
1659 l_found_ps_pct BOOLEAN;
1660
1661 l_position_start_date DATE;
1662 l_position_end_date DATE;
1663 l_start_date DATE;
1664 l_end_date DATE;
1665 l_period_type VARCHAR2(10);
1666
1667 l_return_status VARCHAR2(1);
1668 l_percent_of_salary_flag VARCHAR2(1);
1669
1670 l_employee_id NUMBER;
1671 l_employee_number VARCHAR2(30);
1672 l_employee_name VARCHAR2(240);
1673 l_job_name VARCHAR2(240);
1674 l_job_attribute_id NUMBER;
1675
1676 BEGIN
1677 -- Processing Logic
1678
1679 -- Get all the positions for the Worksheet
1680 -- For each position execute the view 'PSB_WS_YEAR_POSITION_AMOUNTS'
1681 -- and get the various Account Line IDs.
1682 -- Move all account line ids to a PL/SQL table
1683 -- For each Account Line Id, Get the period amounts and move it to a PL/SQL table
1684 -- Move the values to the insert table for only as many periods for the budget year
1685
1686 Get_Calendar_Dates
1687 ( p_budget_calendar_id => g_budget_calendar_id,
1688 p_calendar_start_date => l_calendar_start_date,
1689 p_calendar_end_date => l_calendar_end_date,
1690 p_cy_end_date => l_cy_end_date,
1691 p_pp_start_date => l_pp_start_date
1692 );
1693
1694 PSB_WS_ACCT1.Cache_Budget_Calendar
1695 (p_return_status => l_return_status,
1696 p_budget_calendar_id => g_budget_calendar_id);
1697
1698 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1699 raise FND_API.G_EXC_ERROR;
1700 end if;
1701
1702 -- First set up the Positions view
1703 psb_positions_i_pvt.initialize_view
1704 ( p_worksheet_id => g_worksheet_id,
1705 p_start_date => l_calendar_start_date,
1706 p_end_date => l_calendar_end_date,
1707 p_select_date => l_pp_start_date
1708 );
1709
1710 -- Clear wlbi_rec
1711 FOR l_wlbi_col_index in 1..g_max_num_pos_ws_cols LOOP
1712 g_wlbi_amounts(l_wlbi_col_index).amount := NULL;
1713 END LOOP;
1714
1715 -- Work with one position at a time
1716 FOR position_rec in
1717 (
1718 SELECT
1719 position_line_id,
1720 position_id,
1721 position_name,
1722 position_definition_id,
1723 position_segments
1724 FROM psb_ws_select_positions_v
1725 WHERE worksheet_id = g_worksheet_id
1726
1727 )
1728 LOOP
1729 l_position_line_id := position_rec.position_line_id;
1730 l_position_name := position_rec.position_name;
1731 l_position_id := position_rec.position_id;
1732
1733 g_ps_elements.DELETE;
1734
1735 /* Bug 2959635 Start */
1736
1737 FOR emp_rec IN
1738 (
1739 SELECT
1740 emp.employee_id,
1741 emp.employee_number,
1742 emp.full_name
1743 FROM
1744 psb_employees emp, psb_position_assignments pavb
1745 WHERE pavb.assignment_type = 'EMPLOYEE'
1746 AND pavb.position_id = l_position_id
1747 AND emp.employee_id = pavb.employee_id
1748 AND (pavb.worksheet_id = g_worksheet_id
1749 OR pavb.worksheet_id IS NULL)
1750 ORDER BY pavb.effective_start_date DESC, NVL(pavb.worksheet_id,0) DESC
1751 )
1752 LOOP
1753 l_employee_id := emp_rec.employee_id;
1754 l_employee_number := emp_rec.employee_number;
1755 l_employee_name := emp_rec.full_name;
1756 EXIT;
1757 END LOOP;
1758
1759 FOR job_rec IN
1760 (
1761 SELECT
1762 pava.attribute_id,
1763 patv.attribute_value
1764 FROM psb_attribute_values patv, psb_position_assignments pava
1765 WHERE patv.attribute_value_id = pava.attribute_value_id
1766 AND pava.position_id = l_position_id
1767 AND EXISTS (SELECT 1 FROM psb_attributes pat
1768 WHERE pat.attribute_id = pava.attribute_id
1769 AND pat.system_attribute_type = 'JOB_CLASS')
1770 AND (pava.worksheet_id = g_worksheet_id
1771 OR pava.worksheet_id IS NULL)
1772 ORDER BY pava.effective_start_date DESC, NVL(pava.worksheet_id,0) DESC
1773 )
1774 LOOP
1775 l_job_attribute_id := job_rec.attribute_id;
1776 l_job_name := job_rec.attribute_value;
1777 EXIT;
1781
1778 END LOOP;
1779
1780 /* Bug 2959635 End */
1782 for c_Positions_Rec in (
1783 select a.position_id,
1784 a.name,
1785 a.effective_start_date,
1786 a.effective_end_date
1787 from PSB_POSITIONS a,
1788 PSB_WS_POSITION_LINES b
1789 where a.position_id = b.position_id
1790 and b.position_line_id = l_position_line_id)
1791 loop
1792 l_position_start_date := c_Positions_Rec.effective_start_date;
1793 l_position_end_date := c_Positions_Rec.effective_end_date;
1794 end loop;
1795 -- this assumes the budget calendar is cached
1796 l_start_date := greatest(PSB_WS_ACCT1.g_startdate_cy, l_position_start_date);
1797 l_end_date := least(PSB_WS_ACCT1.g_end_est_date,
1798 nvl(l_position_end_date, PSB_WS_ACCT1.g_end_est_date));
1799
1800
1801 -- Get the postion data in PL/SQL tables for the effective date
1802 Cache_Position_Data(p_return_status => l_return_status,
1803 p_position_line_id => l_position_line_id,
1804 p_position_id => l_position_id,
1805 p_start_date => l_start_date,
1806 p_end_date => l_end_date);
1807
1808
1809 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1810 raise FND_API.G_EXC_ERROR;
1811 end if;
1812
1813
1814
1815 -- Get the Account Lines for each position id
1816 FOR position_acct_rec IN
1817 (
1818 SELECT worksheet_id
1819 ,element_set_id
1820 ,position_line_id
1821 ,code_combination_id
1822 ,service_package_id
1823 ,service_package_name
1824 ,account_type
1825 ,currency_code
1826 ,stage_set_id
1827 ,budget_group_id
1828 ,template_id
1829 ,salary_account_line
1830 FROM psb_ws_year_positions_v
1831 WHERE worksheet_id = g_worksheet_id
1832 AND position_line_id = l_position_line_id
1833
1834 /* Bug No 2008329 Start */
1835 -- AND ( g_account_flag = 'A' or account_type = g_account_flag )
1836 and ( g_account_flag = 'T' or ACCOUNT_TYPE = g_account_flag
1837 OR ( account_type = DECODE(g_account_flag,'P','R')
1838 OR account_type = DECODE(g_account_flag,'P','E')
1839 )
1840 OR ( account_type = DECODE(g_account_flag,'N','A' )
1841 OR account_type = DECODE(g_account_flag,'N','L')
1842 )
1843 OR ( account_type = DECODE(g_account_flag,'B','C' )
1844 OR account_type = DECODE(g_account_flag,'B','D')
1845 )
1846 )
1847 /* Bug No 2008329 End */
1848
1849 AND ( ( g_currency_flag = 'C' and currency_code <> 'STAT' )
1850 or
1851 ( g_currency_flag = 'S' and currency_code = 'STAT')
1852 )
1853 AND ( g_service_package_flag = 'A' or
1854 service_package_id in
1855 (select service_package_id
1856 from psb_ws_service_pkg_profiles_v
1857 /* Following 2 lines commented and next 2 lines added for DDSP */
1858 -- where WORKSHEET_ID = g_worksheet_id
1859 -- and USER_ID = g_user_id
1860 where WORKSHEET_ID = g_profile_worksheet_id
1861 and (USER_ID = g_profile_user_id or (g_profile_user_id IS NULL AND USER_ID IS NULL)) )
1862 )
1863 )
1864 LOOP
1865 l_element_value_type := NULL;
1866 l_code_combination_id := position_acct_rec.code_combination_id;
1867 l_element_set_id := position_acct_rec.element_set_id;
1868 l_service_package_id := position_acct_rec.service_package_id;
1869 l_service_package_name := substr(position_acct_rec.service_package_name,1,30);
1870 l_budget_group_id := position_acct_rec.budget_group_id;
1871
1872 --Get Element name (Code taken from post query in POS WS form)
1873 FOR pay_element_rec IN
1874 (
1875 SELECT distinct pe.pay_element_id, pe.name, pe.follow_salary, pe.element_value_type, pe.period_type
1876 FROM psb_ws_element_lines wel, psb_pay_elements_v pe
1877 WHERE wel.position_line_id = l_position_line_id
1878 AND wel.element_set_id = l_element_set_id
1879 AND wel.pay_element_id = pe.pay_element_id)
1880 LOOP
1881 l_element_id := pay_element_rec.pay_element_id;
1882 l_element_name := pay_element_rec.name;
1883
1884
1885 l_follow_salary := pay_element_rec.follow_salary;
1886 l_element_value_type := pay_element_rec.element_value_type;
1887 l_period_type := pay_element_rec.period_type;
1888 END LOOP;
1889
1890 l_percent_of_salary_flag := 'N';
1891 IF l_element_value_type = 'PS' THEN
1892 Cache_PS_Elements(p_pay_element_id => l_element_id,
1893 p_pay_element_period_type => l_period_type,
1894 p_pay_element_name => l_element_name,
1895 p_pay_element_set_id => l_element_set_id
1896 );
1897 l_percent_of_salary_flag := 'Y';
1898 END IF;
1899
1900 FOR position_year_amt_rec IN
1901 (
1902 SELECT column1_id
1903 ,column2_id
1904 ,column3_id
1905 ,column4_id
1906 ,column5_id
1907 ,column6_id
1908 ,column7_id
1909 ,column8_id
1910 ,column9_id
1911 ,column10_id
1912 ,column11_id
1913 ,column12_id
1914 FROM psb_ws_year_position_amounts_v
1915 WHERE worksheet_id = g_worksheet_id
1916 AND code_combination_id = l_code_combination_id
1917 AND service_package_id = l_service_package_id
1921 /* Bug No 2008329 Start */
1918 AND position_line_id = l_position_line_id
1919 AND element_set_id = l_element_set_id
1920
1922 -- AND ( g_account_flag = 'A' or account_type = g_account_flag )
1923 and ( g_account_flag = 'T' or ACCOUNT_TYPE = g_account_flag
1924 OR ( account_type = DECODE(g_account_flag,'P','R')
1925 OR account_type = DECODE(g_account_flag,'P','E')
1926 )
1927 OR ( account_type = DECODE(g_account_flag,'N','A' )
1928 OR account_type = DECODE(g_account_flag,'N','L')
1929 )
1930 OR ( account_type = DECODE(g_account_flag,'B','C' )
1931 OR account_type = DECODE(g_account_flag,'B','D')
1932 )
1933 )
1934 /* Bug No 2008329 End */
1935
1936 AND ( ( g_currency_flag = 'C' and currency_code <> 'STAT' )
1937 or
1938 ( g_currency_flag = 'S' and currency_code = 'STAT')
1939 )
1940 )
1941 LOOP
1942 -- Get the Account Line IDs and move them to a PL/SQL table
1943 g_acl_ids(1).acl_id := position_year_amt_rec.column1_id;
1944 g_acl_ids(2).acl_id := position_year_amt_rec.column2_id;
1945 g_acl_ids(3).acl_id := position_year_amt_rec.column3_id;
1946 g_acl_ids(4).acl_id := position_year_amt_rec.column4_id;
1947 g_acl_ids(5).acl_id := position_year_amt_rec.column5_id;
1948 g_acl_ids(6).acl_id := position_year_amt_rec.column6_id;
1949 g_acl_ids(7).acl_id := position_year_amt_rec.column7_id;
1950 g_acl_ids(8).acl_id := position_year_amt_rec.column8_id;
1951 g_acl_ids(9).acl_id := position_year_amt_rec.column9_id;
1952 g_acl_ids(10).acl_id := position_year_amt_rec.column10_id;
1953 g_acl_ids(11).acl_id := position_year_amt_rec.column11_id;
1954 g_acl_ids(12).acl_id := position_year_amt_rec.column12_id;
1955
1956
1957 l_wlbi_start_index := 1;
1958
1959 --dbms_output.put_line('Tot Bud Yrs' ||g_total_budget_years);
1960
1961 --- For each budget year get period amts using wal or fte table
1962 FOR col_index in 1..g_total_budget_years LOOP
1963
1964
1965 -- First Get the number of periods in the budget year
1966 l_budget_year_id := g_ws_cols(col_index).budget_year_id;
1967 l_balance_type := g_ws_cols(col_index).balance_type;
1968 l_year_num_periods := g_year_num_periods(l_budget_year_id).num_of_periods;
1969 l_wlbi_end_index := l_wlbi_start_index + l_year_num_periods + 1;
1970
1971 l_fte_rec_found := FND_API.G_FALSE;
1972 l_wal_rec_found := FND_API.G_FALSE;
1973
1974 IF l_balance_type = 'F' THEN
1975 FOR fte_lines_rec IN
1976 (
1977 select
1978 annual_fte
1979 ,period1_fte
1980 ,period2_fte
1981 ,period3_fte
1982 ,period4_fte
1983 ,period5_fte
1984 ,period6_fte
1985 ,period7_fte
1986 ,period8_fte
1987 ,period9_fte
1988 ,period10_fte
1989 ,period11_fte
1990 ,period12_fte
1991 from psb_ws_fte_lines
1992 where position_line_id = l_position_line_id
1993 and budget_year_id = l_budget_year_id
1994 and service_package_id = l_service_package_id
1995 and ( (g_stage_id = 0 and end_stage_seq is null )
1996 or
1997 (g_stage_id between start_stage_seq and nvl(end_stage_seq, 9.99e125) ))
1998 )
1999 LOOP
2000 l_fte_rec_found := FND_API.G_TRUE;
2001 g_wlbi_amounts(l_wlbi_start_index).amount := fte_lines_rec.annual_fte;
2002 g_wal_period_amounts(1).amount := fte_lines_rec.period1_fte;
2003 g_wal_period_amounts(2).amount := fte_lines_rec.period2_fte;
2004 g_wal_period_amounts(3).amount := fte_lines_rec.period3_fte;
2005 g_wal_period_amounts(4).amount := fte_lines_rec.period4_fte;
2006 g_wal_period_amounts(5).amount := fte_lines_rec.period5_fte;
2007 g_wal_period_amounts(6).amount := fte_lines_rec.period6_fte;
2008 g_wal_period_amounts(7).amount := fte_lines_rec.period7_fte;
2009 g_wal_period_amounts(8).amount := fte_lines_rec.period8_fte;
2010 g_wal_period_amounts(9).amount := fte_lines_rec.period9_fte;
2011 g_wal_period_amounts(10).amount := fte_lines_rec.period10_fte;
2012 g_wal_period_amounts(11).amount := fte_lines_rec.period11_fte;
2013 g_wal_period_amounts(12).amount := fte_lines_rec.period12_fte;
2014
2015 END LOOP;
2016
2017
2018 ELSIF ( g_acl_ids(col_index).acl_id > 0 ) THEN -- balance_type in (A,B,E)
2019
2020 FOR wal_rec IN
2021 (
2022 -- Include the other period columns if supporting more than 12
2023 SELECT ytd_amount
2024 ,start_stage_seq
2025 ,current_stage_seq
2026 ,period1_amount
2027 ,period2_amount
2028 ,period3_amount
2029 ,period4_amount
2030 ,period5_amount
2031 ,period6_amount
2032 ,period7_amount
2033 ,period8_amount
2034 ,period9_amount
2035 ,period10_amount
2036 ,period11_amount
2037 ,period12_amount
2038 FROM psb_ws_account_lines
2039 WHERE account_line_id = g_acl_ids(col_index).acl_id
2040 )
2041 LOOP
2042
2043 l_wal_rec_found := FND_API.G_TRUE;
2044 g_wlbi_amounts(l_wlbi_start_index).amount := wal_rec.ytd_amount;
2045 l_start_stage_seq := wal_rec.start_stage_seq;
2046 l_current_stage_seq := wal_rec.current_stage_seq;
2047
2048 g_wal_period_amounts(1).amount := wal_rec.period1_amount;
2049 g_wal_period_amounts(2).amount := wal_rec.period2_amount;
2053 g_wal_period_amounts(6).amount := wal_rec.period6_amount;
2050 g_wal_period_amounts(3).amount := wal_rec.period3_amount;
2051 g_wal_period_amounts(4).amount := wal_rec.period4_amount;
2052 g_wal_period_amounts(5).amount := wal_rec.period5_amount;
2054 g_wal_period_amounts(7).amount := wal_rec.period7_amount;
2055 g_wal_period_amounts(8).amount := wal_rec.period8_amount;
2056 g_wal_period_amounts(9).amount := wal_rec.period9_amount;
2057 g_wal_period_amounts(10).amount := wal_rec.period10_amount;
2058 g_wal_period_amounts(11).amount := wal_rec.period11_amount;
2059 g_wal_period_amounts(12).amount := wal_rec.period12_amount;
2060 END LOOP; --wal_rec
2061 END IF; -- balance type = 'F' or (balance_type in (A,B,E) and acl id >0)
2062
2063 -- Set the second column to zero percent for each year
2064 g_wlbi_amounts(l_wlbi_start_index+1).amount := 0; -- Set percentage to zero ; dummy column
2065
2066 --
2067 IF ( l_wal_rec_found = FND_API.G_TRUE ) OR
2068 ( l_fte_rec_found = FND_API.G_TRUE ) THEN
2069 l_wal_col_index := 0;
2070 FOR l_wlbi_col_index in l_wlbi_start_index+2 .. l_wlbi_end_index
2071 LOOP
2072 l_wal_col_index := l_wal_col_index + 1;
2073 g_wlbi_amounts(l_wlbi_col_index).amount := g_wal_period_amounts(l_wal_col_index).amount;
2074 END LOOP;
2075 ELSE
2076 -- if no wal or fte rec found set values to null
2077 FOR l_wlbi_col_index in l_wlbi_start_index .. l_wlbi_end_index
2078 LOOP
2079 g_wlbi_amounts(l_wlbi_col_index).amount := NULL;
2080 END LOOP;
2081 END IF;
2082 l_wlbi_start_index := l_wlbi_end_index + 1;
2083
2084 END LOOP; -- For each Budget Year Balance
2085
2086 IF FND_FLEX_KEYVAL.validate_ccid('SQLGL', 'GL#', g_coa_id, l_code_combination_id) THEN
2087 l_account_segments := substr(FND_FLEX_KEYVAL.concatenated_values,1,1000);
2088 l_account_desc := substr(FND_FLEX_KEYVAL.concatenated_descriptions,1,1000);
2089 END IF;
2090
2091 -- Maximum columns used = 168
2092 -- (12 years * 12 periods ) + 12 years * 2 misc columns = 168
2093 -- 4 misc columns (Year Total, Percentage, Start stage seq, End stage seq)
2094 -- Later include amount169..amount220 when supporting
2095 -- more than 12 periods for an year
2096 INSERT INTO psb_ws_line_balances_i(
2097 EXPORT_ID,
2098 EXPORT_WORKSHEET_TYPE,
2099 CODE_COMBINATION_ID,
2100 BUDGET_GROUP_ID,
2101 CONCATENATED_ACCOUNT,
2102 ACCOUNT_DESCRIPTION,
2103 SERVICE_PACKAGE_ID,
2104 SERVICE_PACKAGE_NAME,
2105 PAY_ELEMENT_SET_ID,
2106 PAY_ELEMENT_ID,
2107 PAY_ELEMENT_NAME,
2108 SALARY_ACCOUNT_LINE,
2109 FOLLOW_SALARY,
2110 POSITION_LINE_ID,
2111 POSITION_ID,
2112 POSITION_NAME,
2113 POSITION_SEGMENTS,
2114 JOB_NAME,
2115 EMPLOYEE_ID,
2116 EMPLOYEE_NUMBER,
2117 EMPLOYEE_NAME,
2118 VALUE_TYPE,
2119 PERCENT_OF_SALARY_FLAG,
2120 AMOUNT1,
2121 AMOUNT2,
2122 AMOUNT3,
2123 AMOUNT4,
2124 AMOUNT5,
2125 AMOUNT6,
2126 AMOUNT7,
2127 AMOUNT8,
2128 AMOUNT9,
2129 AMOUNT10,
2130 AMOUNT11,
2131 AMOUNT12,
2132 AMOUNT13,
2133 AMOUNT14,
2134 AMOUNT15,
2135 AMOUNT16,
2136 AMOUNT17,
2137 AMOUNT18,
2138 AMOUNT19,
2139 AMOUNT20,
2140 AMOUNT21,
2141 AMOUNT22,
2142 AMOUNT23,
2143 AMOUNT24,
2144 AMOUNT25,
2145 AMOUNT26,
2146 AMOUNT27,
2147 AMOUNT28,
2148 AMOUNT29,
2149 AMOUNT30,
2150 AMOUNT31,
2151 AMOUNT32,
2152 AMOUNT33,
2153 AMOUNT34,
2154 AMOUNT35,
2155 AMOUNT36,
2156 AMOUNT37,
2157 AMOUNT38,
2158 AMOUNT39,
2159 AMOUNT40,
2160 AMOUNT41,
2161 AMOUNT42,
2162 AMOUNT43,
2163 AMOUNT44,
2164 AMOUNT45,
2165 AMOUNT46,
2166 AMOUNT47,
2167 AMOUNT48,
2168 AMOUNT49,
2169 AMOUNT50,
2170 AMOUNT51,
2171 AMOUNT52,
2172 AMOUNT53,
2173 AMOUNT54,
2174 AMOUNT55,
2175 AMOUNT56,
2176 AMOUNT57,
2177 AMOUNT58,
2178 AMOUNT59,
2179 AMOUNT60,
2180 AMOUNT61,
2181 AMOUNT62,
2182 AMOUNT63,
2183 AMOUNT64,
2184 AMOUNT65,
2185 AMOUNT66,
2186 AMOUNT67,
2187 AMOUNT68,
2188 AMOUNT69,
2189 AMOUNT70,
2190 AMOUNT71,
2191 AMOUNT72,
2192 AMOUNT73,
2193 AMOUNT74,
2194 AMOUNT75,
2195 AMOUNT76,
2196 AMOUNT77,
2197 AMOUNT78,
2198 AMOUNT79,
2199 AMOUNT80,
2200 AMOUNT81,
2201 AMOUNT82,
2202 AMOUNT83,
2203 AMOUNT84,
2204 AMOUNT85,
2205 AMOUNT86,
2206 AMOUNT87,
2207 AMOUNT88,
2208 AMOUNT89,
2209 AMOUNT90,
2210 AMOUNT91,
2211 AMOUNT92,
2212 AMOUNT93,
2213 AMOUNT94,
2214 AMOUNT95,
2215 AMOUNT96,
2216 AMOUNT97,
2217 AMOUNT98,
2218 AMOUNT99,
2219 AMOUNT100,
2223 AMOUNT104,
2220 AMOUNT101,
2221 AMOUNT102,
2222 AMOUNT103,
2224 AMOUNT105,
2225 AMOUNT106,
2226 AMOUNT107,
2227 AMOUNT108,
2228 AMOUNT109,
2229 AMOUNT110,
2230 AMOUNT111,
2231 AMOUNT112,
2232 AMOUNT113,
2233 AMOUNT114,
2234 AMOUNT115,
2235 AMOUNT116,
2236 AMOUNT117,
2237 AMOUNT118,
2238 AMOUNT119,
2239 AMOUNT120,
2240 AMOUNT121,
2241 AMOUNT122,
2242 AMOUNT123,
2243 AMOUNT124,
2244 AMOUNT125,
2245 AMOUNT126,
2246 AMOUNT127,
2247 AMOUNT128,
2248 AMOUNT129,
2249 AMOUNT130,
2250 AMOUNT131,
2251 AMOUNT132,
2252 AMOUNT133,
2253 AMOUNT134,
2254 AMOUNT135,
2255 AMOUNT136,
2256 AMOUNT137,
2257 AMOUNT138,
2258 AMOUNT139,
2259 AMOUNT140,
2260 AMOUNT141,
2261 AMOUNT142,
2262 AMOUNT143,
2263 AMOUNT144,
2264 AMOUNT145,
2265 AMOUNT146,
2266 AMOUNT147,
2267 AMOUNT148,
2268 AMOUNT149,
2269 AMOUNT150,
2270 AMOUNT151,
2271 AMOUNT152,
2272 AMOUNT153,
2273 AMOUNT154,
2274 AMOUNT155,
2275 AMOUNT156,
2276 AMOUNT157,
2277 AMOUNT158,
2278 AMOUNT159,
2279 AMOUNT160,
2280 AMOUNT161,
2281 AMOUNT162,
2282 AMOUNT163,
2283 AMOUNT164,
2284 AMOUNT165,
2285 AMOUNT166,
2286 AMOUNT167,
2287 AMOUNT168,
2288 LAST_UPDATE_DATE,
2289 LAST_UPDATED_BY,
2290 LAST_UPDATE_LOGIN,
2291 CREATED_BY,
2292 CREATED_DATE)
2293 values(
2294 g_export_id,
2295 'P',
2296 l_code_combination_id,
2297 l_budget_group_id,
2298 l_account_segments,
2299 l_account_desc,
2300 l_service_package_id,
2301 l_service_package_name,
2302 l_element_set_id,
2303 l_element_id,
2304 l_element_name,
2305 nvl(position_acct_rec.salary_account_line,'N'),
2306 nvl(l_follow_salary,'N'),
2307 position_rec.position_line_id,
2308 position_rec.position_id,
2309 position_rec.position_name,
2310 position_rec.position_segments,
2311 l_job_name,
2312 l_employee_id,
2313 l_employee_number,
2314 l_employee_name,
2315 'A', -- For Amount
2316 l_percent_of_salary_flag,
2317 g_wlbi_amounts(1).amount,
2318 g_wlbi_amounts(2).amount,
2319 g_wlbi_amounts(3).amount,
2320 g_wlbi_amounts(4).amount,
2321 g_wlbi_amounts(5).amount,
2322 g_wlbi_amounts(6).amount,
2323 g_wlbi_amounts(7).amount,
2324 g_wlbi_amounts(8).amount,
2325 g_wlbi_amounts(9).amount,
2326 g_wlbi_amounts(10).amount,
2327 g_wlbi_amounts(11).amount,
2328 g_wlbi_amounts(12).amount,
2329 g_wlbi_amounts(13).amount,
2330 g_wlbi_amounts(14).amount,
2331 g_wlbi_amounts(15).amount,
2332 g_wlbi_amounts(16).amount,
2333 g_wlbi_amounts(17).amount,
2334 g_wlbi_amounts(18).amount,
2335 g_wlbi_amounts(19).amount,
2336 g_wlbi_amounts(20).amount,
2337 g_wlbi_amounts(21).amount,
2338 g_wlbi_amounts(22).amount,
2339 g_wlbi_amounts(23).amount,
2340 g_wlbi_amounts(24).amount,
2341 g_wlbi_amounts(25).amount,
2342 g_wlbi_amounts(26).amount,
2343 g_wlbi_amounts(27).amount,
2344 g_wlbi_amounts(28).amount,
2345 g_wlbi_amounts(29).amount,
2346 g_wlbi_amounts(30).amount,
2347 g_wlbi_amounts(31).amount,
2348 g_wlbi_amounts(32).amount,
2349 g_wlbi_amounts(33).amount,
2350 g_wlbi_amounts(34).amount,
2351 g_wlbi_amounts(35).amount,
2352 g_wlbi_amounts(36).amount,
2353 g_wlbi_amounts(37).amount,
2354 g_wlbi_amounts(38).amount,
2355 g_wlbi_amounts(39).amount,
2356 g_wlbi_amounts(40).amount,
2357 g_wlbi_amounts(41).amount,
2358 g_wlbi_amounts(42).amount,
2359 g_wlbi_amounts(43).amount,
2360 g_wlbi_amounts(44).amount,
2361 g_wlbi_amounts(45).amount,
2362 g_wlbi_amounts(46).amount,
2363 g_wlbi_amounts(47).amount,
2364 g_wlbi_amounts(48).amount,
2365 g_wlbi_amounts(49).amount,
2366 g_wlbi_amounts(50).amount,
2367 g_wlbi_amounts(51).amount,
2368 g_wlbi_amounts(52).amount,
2369 g_wlbi_amounts(53).amount,
2370 g_wlbi_amounts(54).amount,
2371 g_wlbi_amounts(55).amount,
2372 g_wlbi_amounts(56).amount,
2373 g_wlbi_amounts(57).amount,
2374 g_wlbi_amounts(58).amount,
2375 g_wlbi_amounts(59).amount,
2376 g_wlbi_amounts(60).amount,
2377 g_wlbi_amounts(61).amount,
2378 g_wlbi_amounts(62).amount,
2379 g_wlbi_amounts(63).amount,
2380 g_wlbi_amounts(64).amount,
2381 g_wlbi_amounts(65).amount,
2382 g_wlbi_amounts(66).amount,
2383 g_wlbi_amounts(67).amount,
2384 g_wlbi_amounts(68).amount,
2385 g_wlbi_amounts(69).amount,
2386 g_wlbi_amounts(70).amount,
2387 g_wlbi_amounts(71).amount,
2388 g_wlbi_amounts(72).amount,
2389 g_wlbi_amounts(73).amount,
2390 g_wlbi_amounts(74).amount,
2391 g_wlbi_amounts(75).amount,
2395 g_wlbi_amounts(79).amount,
2392 g_wlbi_amounts(76).amount,
2393 g_wlbi_amounts(77).amount,
2394 g_wlbi_amounts(78).amount,
2396 g_wlbi_amounts(80).amount,
2397 g_wlbi_amounts(81).amount,
2398 g_wlbi_amounts(82).amount,
2399 g_wlbi_amounts(83).amount,
2400 g_wlbi_amounts(84).amount,
2401 g_wlbi_amounts(85).amount,
2402 g_wlbi_amounts(86).amount,
2403 g_wlbi_amounts(87).amount,
2404 g_wlbi_amounts(88).amount,
2405 g_wlbi_amounts(89).amount,
2406 g_wlbi_amounts(90).amount,
2407 g_wlbi_amounts(91).amount,
2408 g_wlbi_amounts(92).amount,
2409 g_wlbi_amounts(93).amount,
2410 g_wlbi_amounts(94).amount,
2411 g_wlbi_amounts(95).amount,
2412 g_wlbi_amounts(96).amount,
2413 g_wlbi_amounts(97).amount,
2414 g_wlbi_amounts(98).amount,
2415 g_wlbi_amounts(99).amount,
2416 g_wlbi_amounts(100).amount,
2417 g_wlbi_amounts(101).amount,
2418 g_wlbi_amounts(102).amount,
2419 g_wlbi_amounts(103).amount,
2420 g_wlbi_amounts(104).amount,
2421 g_wlbi_amounts(105).amount,
2422 g_wlbi_amounts(106).amount,
2423 g_wlbi_amounts(107).amount,
2424 g_wlbi_amounts(108).amount,
2425 g_wlbi_amounts(109).amount,
2426 g_wlbi_amounts(110).amount,
2427 g_wlbi_amounts(111).amount,
2428 g_wlbi_amounts(112).amount,
2429 g_wlbi_amounts(113).amount,
2430 g_wlbi_amounts(114).amount,
2431 g_wlbi_amounts(115).amount,
2432 g_wlbi_amounts(116).amount,
2433 g_wlbi_amounts(117).amount,
2434 g_wlbi_amounts(118).amount,
2435 g_wlbi_amounts(119).amount,
2436 g_wlbi_amounts(120).amount,
2437 g_wlbi_amounts(121).amount,
2438 g_wlbi_amounts(122).amount,
2439 g_wlbi_amounts(123).amount,
2440 g_wlbi_amounts(124).amount,
2441 g_wlbi_amounts(125).amount,
2442 g_wlbi_amounts(126).amount,
2443 g_wlbi_amounts(127).amount,
2444 g_wlbi_amounts(128).amount,
2445 g_wlbi_amounts(129).amount,
2446 g_wlbi_amounts(130).amount,
2447 g_wlbi_amounts(131).amount,
2448 g_wlbi_amounts(132).amount,
2449 g_wlbi_amounts(133).amount,
2450 g_wlbi_amounts(134).amount,
2451 g_wlbi_amounts(135).amount,
2452 g_wlbi_amounts(136).amount,
2453 g_wlbi_amounts(137).amount,
2454 g_wlbi_amounts(138).amount,
2455 g_wlbi_amounts(139).amount,
2456 g_wlbi_amounts(140).amount,
2457 g_wlbi_amounts(141).amount,
2458 g_wlbi_amounts(142).amount,
2459 g_wlbi_amounts(143).amount,
2460 g_wlbi_amounts(144).amount,
2461 g_wlbi_amounts(145).amount,
2462 g_wlbi_amounts(146).amount,
2463 g_wlbi_amounts(147).amount,
2464 g_wlbi_amounts(148).amount,
2465 g_wlbi_amounts(149).amount,
2466 g_wlbi_amounts(150).amount,
2467 g_wlbi_amounts(151).amount,
2468 g_wlbi_amounts(152).amount,
2469 g_wlbi_amounts(153).amount,
2470 g_wlbi_amounts(154).amount,
2471 g_wlbi_amounts(155).amount,
2472 g_wlbi_amounts(156).amount,
2473 g_wlbi_amounts(157).amount,
2474 g_wlbi_amounts(158).amount,
2475 g_wlbi_amounts(159).amount,
2476 g_wlbi_amounts(160).amount,
2477 g_wlbi_amounts(161).amount,
2478 g_wlbi_amounts(162).amount,
2479 g_wlbi_amounts(163).amount,
2480 g_wlbi_amounts(164).amount,
2481 g_wlbi_amounts(165).amount,
2482 g_wlbi_amounts(166).amount,
2483 g_wlbi_amounts(167).amount,
2484 g_wlbi_amounts(168).amount,
2485 SYSDATE,
2486 g_user_id,
2487 g_user_id,
2488 g_user_id,
2489 SYSDATE
2490 );
2491
2492 END LOOP; -- position_year_amt_rec
2493 END LOOP; -- position_acct_rec
2494
2495 -- Insert PS Element Percentages; Added on 11/18/98
2496 IF g_ps_elements.COUNT > 0 THEN
2497
2498
2499 FOR i in 1.. g_ps_elements.COUNT LOOP
2500 l_wlbi_start_index := 1;
2501 --dbms_output.put_line('Tot Bud Yrs' ||g_total_budget_years);
2502
2503 -- For each budget year get ps element percentages
2504 FOR col_index in 1..g_total_budget_years LOOP
2505
2506 -- First Get the number of periods in the budget year
2507 l_budget_year_id := g_ws_cols(col_index).budget_year_id;
2508 l_balance_type := g_ws_cols(col_index).balance_type;
2509 l_year_num_periods := g_year_num_periods(l_budget_year_id).num_of_periods;
2510 l_wlbi_end_index := l_wlbi_start_index + l_year_num_periods + 1;
2511 l_found_ps_pct := FALSE;
2512
2513 -- Set the first and second column to zero (not applicable for PS Element Lines)
2514 g_wlbi_amounts(l_wlbi_start_index).amount := 0;
2515 g_wlbi_amounts(l_wlbi_start_index+1).amount := 0;
2516
2517 IF l_balance_type IN ('A','B','E') THEN
2518
2519 Populate_PS_Element_Pct ( p_budget_year_id => l_budget_year_id,
2520 p_position_id => l_position_id,
2521 p_pay_element_id => g_ps_elements(i).pay_element_id,
2522 p_element_period_type => g_ps_elements(i).pay_element_period_type,
2523 p_found_ps_pct => l_found_ps_pct
2524 );
2525 END IF;
2526
2527 IF l_found_ps_pct THEN
2528 l_wal_col_index := 0;
2529 FOR l_wlbi_col_index in l_wlbi_start_index+2 .. l_wlbi_end_index
2530 LOOP
2531 l_wal_col_index := l_wal_col_index + 1;
2535 FOR l_wlbi_col_index in l_wlbi_start_index .. l_wlbi_end_index
2532 g_wlbi_amounts(l_wlbi_col_index).amount := g_ps_element_pct(l_wal_col_index).amount;
2533 END LOOP;
2534 ELSE -- populate null
2536 LOOP
2537 g_wlbi_amounts(l_wlbi_col_index).amount := NULL;
2538 END LOOP;
2539 END IF; -- found_ps_pct
2540 l_wlbi_start_index := l_wlbi_end_index + 1;
2541
2542 END LOOP; -- for each budget year and balance;
2543
2544 -- Insert Statement
2545 INSERT INTO psb_ws_line_balances_i(
2546 EXPORT_ID,
2547 EXPORT_WORKSHEET_TYPE,
2548 CODE_COMBINATION_ID,
2549 BUDGET_GROUP_ID,
2550 CONCATENATED_ACCOUNT,
2551 ACCOUNT_DESCRIPTION,
2552 SERVICE_PACKAGE_ID,
2553 SERVICE_PACKAGE_NAME,
2554 PAY_ELEMENT_SET_ID,
2555 PAY_ELEMENT_ID,
2556 PAY_ELEMENT_NAME,
2557 SALARY_ACCOUNT_LINE,
2558 FOLLOW_SALARY,
2559 POSITION_LINE_ID,
2560 POSITION_ID,
2561 POSITION_NAME,
2562 POSITION_SEGMENTS,
2563 JOB_NAME,
2564 EMPLOYEE_ID,
2565 EMPLOYEE_NUMBER,
2566 EMPLOYEE_NAME,
2567 VALUE_TYPE,
2568 PERCENT_OF_SALARY_FLAG,
2569 AMOUNT1,
2570 AMOUNT2,
2571 AMOUNT3,
2572 AMOUNT4,
2573 AMOUNT5,
2574 AMOUNT6,
2575 AMOUNT7,
2576 AMOUNT8,
2577 AMOUNT9,
2578 AMOUNT10,
2579 AMOUNT11,
2580 AMOUNT12,
2581 AMOUNT13,
2582 AMOUNT14,
2583 AMOUNT15,
2584 AMOUNT16,
2585 AMOUNT17,
2586 AMOUNT18,
2587 AMOUNT19,
2588 AMOUNT20,
2589 AMOUNT21,
2590 AMOUNT22,
2591 AMOUNT23,
2592 AMOUNT24,
2593 AMOUNT25,
2594 AMOUNT26,
2595 AMOUNT27,
2596 AMOUNT28,
2597 AMOUNT29,
2598 AMOUNT30,
2599 AMOUNT31,
2600 AMOUNT32,
2601 AMOUNT33,
2602 AMOUNT34,
2603 AMOUNT35,
2604 AMOUNT36,
2605 AMOUNT37,
2606 AMOUNT38,
2607 AMOUNT39,
2608 AMOUNT40,
2609 AMOUNT41,
2610 AMOUNT42,
2611 AMOUNT43,
2612 AMOUNT44,
2613 AMOUNT45,
2614 AMOUNT46,
2615 AMOUNT47,
2616 AMOUNT48,
2617 AMOUNT49,
2618 AMOUNT50,
2619 AMOUNT51,
2620 AMOUNT52,
2621 AMOUNT53,
2622 AMOUNT54,
2623 AMOUNT55,
2624 AMOUNT56,
2625 AMOUNT57,
2626 AMOUNT58,
2627 AMOUNT59,
2628 AMOUNT60,
2629 AMOUNT61,
2630 AMOUNT62,
2631 AMOUNT63,
2632 AMOUNT64,
2633 AMOUNT65,
2634 AMOUNT66,
2635 AMOUNT67,
2636 AMOUNT68,
2637 AMOUNT69,
2638 AMOUNT70,
2639 AMOUNT71,
2640 AMOUNT72,
2641 AMOUNT73,
2642 AMOUNT74,
2643 AMOUNT75,
2644 AMOUNT76,
2645 AMOUNT77,
2646 AMOUNT78,
2647 AMOUNT79,
2648 AMOUNT80,
2649 AMOUNT81,
2650 AMOUNT82,
2651 AMOUNT83,
2652 AMOUNT84,
2653 AMOUNT85,
2654 AMOUNT86,
2655 AMOUNT87,
2656 AMOUNT88,
2657 AMOUNT89,
2658 AMOUNT90,
2659 AMOUNT91,
2660 AMOUNT92,
2661 AMOUNT93,
2662 AMOUNT94,
2663 AMOUNT95,
2664 AMOUNT96,
2665 AMOUNT97,
2666 AMOUNT98,
2667 AMOUNT99,
2668 AMOUNT100,
2669 AMOUNT101,
2670 AMOUNT102,
2671 AMOUNT103,
2672 AMOUNT104,
2673 AMOUNT105,
2674 AMOUNT106,
2675 AMOUNT107,
2676 AMOUNT108,
2677 AMOUNT109,
2678 AMOUNT110,
2679 AMOUNT111,
2680 AMOUNT112,
2681 AMOUNT113,
2682 AMOUNT114,
2683 AMOUNT115,
2684 AMOUNT116,
2685 AMOUNT117,
2686 AMOUNT118,
2687 AMOUNT119,
2688 AMOUNT120,
2689 AMOUNT121,
2690 AMOUNT122,
2691 AMOUNT123,
2692 AMOUNT124,
2693 AMOUNT125,
2694 AMOUNT126,
2695 AMOUNT127,
2696 AMOUNT128,
2697 AMOUNT129,
2698 AMOUNT130,
2699 AMOUNT131,
2700 AMOUNT132,
2701 AMOUNT133,
2702 AMOUNT134,
2703 AMOUNT135,
2704 AMOUNT136,
2705 AMOUNT137,
2706 AMOUNT138,
2707 AMOUNT139,
2708 AMOUNT140,
2709 AMOUNT141,
2710 AMOUNT142,
2711 AMOUNT143,
2712 AMOUNT144,
2713 AMOUNT145,
2714 AMOUNT146,
2715 AMOUNT147,
2716 AMOUNT148,
2717 AMOUNT149,
2718 AMOUNT150,
2719 AMOUNT151,
2720 AMOUNT152,
2721 AMOUNT153,
2722 AMOUNT154,
2723 AMOUNT155,
2724 AMOUNT156,
2725 AMOUNT157,
2726 AMOUNT158,
2727 AMOUNT159,
2728 AMOUNT160,
2729 AMOUNT161,
2730 AMOUNT162,
2731 AMOUNT163,
2732 AMOUNT164,
2733 AMOUNT165,
2734 AMOUNT166,
2735 AMOUNT167,
2736 AMOUNT168,
2737 LAST_UPDATE_DATE,
2738 LAST_UPDATED_BY,
2739 LAST_UPDATE_LOGIN,
2740 CREATED_BY,
2741 CREATED_DATE)
2742 values(
2743 g_export_id,
2747 null,
2744 'P',
2745 null,
2746 null,
2748 null,
2749 null,
2750 null,
2751 g_ps_elements(i).pay_element_set_id,
2752 g_ps_elements(i).pay_element_id,
2753 g_ps_elements(i).pay_element_name,
2754 null,
2755 null,
2756 position_rec.position_line_id,
2757 position_rec.position_id,
2758 position_rec.position_name,
2759 position_rec.position_segments,
2760 l_job_name,
2761 l_employee_id,
2762 l_employee_number,
2763 l_employee_name,
2764 'P', -- For Percent rows
2765 null,
2766 g_wlbi_amounts(1).amount,
2767 g_wlbi_amounts(2).amount,
2768 g_wlbi_amounts(3).amount,
2769 g_wlbi_amounts(4).amount,
2770 g_wlbi_amounts(5).amount,
2771 g_wlbi_amounts(6).amount,
2772 g_wlbi_amounts(7).amount,
2773 g_wlbi_amounts(8).amount,
2774 g_wlbi_amounts(9).amount,
2775 g_wlbi_amounts(10).amount,
2776 g_wlbi_amounts(11).amount,
2777 g_wlbi_amounts(12).amount,
2778 g_wlbi_amounts(13).amount,
2779 g_wlbi_amounts(14).amount,
2780 g_wlbi_amounts(15).amount,
2781 g_wlbi_amounts(16).amount,
2782 g_wlbi_amounts(17).amount,
2783 g_wlbi_amounts(18).amount,
2784 g_wlbi_amounts(19).amount,
2785 g_wlbi_amounts(20).amount,
2786 g_wlbi_amounts(21).amount,
2787 g_wlbi_amounts(22).amount,
2788 g_wlbi_amounts(23).amount,
2789 g_wlbi_amounts(24).amount,
2790 g_wlbi_amounts(25).amount,
2791 g_wlbi_amounts(26).amount,
2792 g_wlbi_amounts(27).amount,
2793 g_wlbi_amounts(28).amount,
2794 g_wlbi_amounts(29).amount,
2795 g_wlbi_amounts(30).amount,
2796 g_wlbi_amounts(31).amount,
2797 g_wlbi_amounts(32).amount,
2798 g_wlbi_amounts(33).amount,
2799 g_wlbi_amounts(34).amount,
2800 g_wlbi_amounts(35).amount,
2801 g_wlbi_amounts(36).amount,
2802 g_wlbi_amounts(37).amount,
2803 g_wlbi_amounts(38).amount,
2804 g_wlbi_amounts(39).amount,
2805 g_wlbi_amounts(40).amount,
2806 g_wlbi_amounts(41).amount,
2807 g_wlbi_amounts(42).amount,
2808 g_wlbi_amounts(43).amount,
2809 g_wlbi_amounts(44).amount,
2810 g_wlbi_amounts(45).amount,
2811 g_wlbi_amounts(46).amount,
2812 g_wlbi_amounts(47).amount,
2813 g_wlbi_amounts(48).amount,
2814 g_wlbi_amounts(49).amount,
2815 g_wlbi_amounts(50).amount,
2816 g_wlbi_amounts(51).amount,
2817 g_wlbi_amounts(52).amount,
2818 g_wlbi_amounts(53).amount,
2819 g_wlbi_amounts(54).amount,
2820 g_wlbi_amounts(55).amount,
2821 g_wlbi_amounts(56).amount,
2822 g_wlbi_amounts(57).amount,
2823 g_wlbi_amounts(58).amount,
2824 g_wlbi_amounts(59).amount,
2825 g_wlbi_amounts(60).amount,
2826 g_wlbi_amounts(61).amount,
2827 g_wlbi_amounts(62).amount,
2828 g_wlbi_amounts(63).amount,
2829 g_wlbi_amounts(64).amount,
2830 g_wlbi_amounts(65).amount,
2831 g_wlbi_amounts(66).amount,
2832 g_wlbi_amounts(67).amount,
2833 g_wlbi_amounts(68).amount,
2834 g_wlbi_amounts(69).amount,
2835 g_wlbi_amounts(70).amount,
2836 g_wlbi_amounts(71).amount,
2837 g_wlbi_amounts(72).amount,
2838 g_wlbi_amounts(73).amount,
2839 g_wlbi_amounts(74).amount,
2840 g_wlbi_amounts(75).amount,
2841 g_wlbi_amounts(76).amount,
2842 g_wlbi_amounts(77).amount,
2843 g_wlbi_amounts(78).amount,
2844 g_wlbi_amounts(79).amount,
2845 g_wlbi_amounts(80).amount,
2846 g_wlbi_amounts(81).amount,
2847 g_wlbi_amounts(82).amount,
2848 g_wlbi_amounts(83).amount,
2849 g_wlbi_amounts(84).amount,
2850 g_wlbi_amounts(85).amount,
2851 g_wlbi_amounts(86).amount,
2852 g_wlbi_amounts(87).amount,
2853 g_wlbi_amounts(88).amount,
2854 g_wlbi_amounts(89).amount,
2855 g_wlbi_amounts(90).amount,
2856 g_wlbi_amounts(91).amount,
2857 g_wlbi_amounts(92).amount,
2858 g_wlbi_amounts(93).amount,
2859 g_wlbi_amounts(94).amount,
2860 g_wlbi_amounts(95).amount,
2861 g_wlbi_amounts(96).amount,
2862 g_wlbi_amounts(97).amount,
2863 g_wlbi_amounts(98).amount,
2864 g_wlbi_amounts(99).amount,
2865 g_wlbi_amounts(100).amount,
2866 g_wlbi_amounts(101).amount,
2867 g_wlbi_amounts(102).amount,
2868 g_wlbi_amounts(103).amount,
2869 g_wlbi_amounts(104).amount,
2870 g_wlbi_amounts(105).amount,
2871 g_wlbi_amounts(106).amount,
2872 g_wlbi_amounts(107).amount,
2873 g_wlbi_amounts(108).amount,
2874 g_wlbi_amounts(109).amount,
2875 g_wlbi_amounts(110).amount,
2876 g_wlbi_amounts(111).amount,
2877 g_wlbi_amounts(112).amount,
2878 g_wlbi_amounts(113).amount,
2879 g_wlbi_amounts(114).amount,
2880 g_wlbi_amounts(115).amount,
2881 g_wlbi_amounts(116).amount,
2882 g_wlbi_amounts(117).amount,
2883 g_wlbi_amounts(118).amount,
2884 g_wlbi_amounts(119).amount,
2885 g_wlbi_amounts(120).amount,
2886 g_wlbi_amounts(121).amount,
2887 g_wlbi_amounts(122).amount,
2888 g_wlbi_amounts(123).amount,
2889 g_wlbi_amounts(124).amount,
2890 g_wlbi_amounts(125).amount,
2891 g_wlbi_amounts(126).amount,
2892 g_wlbi_amounts(127).amount,
2893 g_wlbi_amounts(128).amount,
2894 g_wlbi_amounts(129).amount,
2895 g_wlbi_amounts(130).amount,
2899 g_wlbi_amounts(134).amount,
2896 g_wlbi_amounts(131).amount,
2897 g_wlbi_amounts(132).amount,
2898 g_wlbi_amounts(133).amount,
2900 g_wlbi_amounts(135).amount,
2901 g_wlbi_amounts(136).amount,
2902 g_wlbi_amounts(137).amount,
2903 g_wlbi_amounts(138).amount,
2904 g_wlbi_amounts(139).amount,
2905 g_wlbi_amounts(140).amount,
2906 g_wlbi_amounts(141).amount,
2907 g_wlbi_amounts(142).amount,
2908 g_wlbi_amounts(143).amount,
2909 g_wlbi_amounts(144).amount,
2910 g_wlbi_amounts(145).amount,
2911 g_wlbi_amounts(146).amount,
2912 g_wlbi_amounts(147).amount,
2913 g_wlbi_amounts(148).amount,
2914 g_wlbi_amounts(149).amount,
2915 g_wlbi_amounts(150).amount,
2916 g_wlbi_amounts(151).amount,
2917 g_wlbi_amounts(152).amount,
2918 g_wlbi_amounts(153).amount,
2919 g_wlbi_amounts(154).amount,
2920 g_wlbi_amounts(155).amount,
2921 g_wlbi_amounts(156).amount,
2922 g_wlbi_amounts(157).amount,
2923 g_wlbi_amounts(158).amount,
2924 g_wlbi_amounts(159).amount,
2925 g_wlbi_amounts(160).amount,
2926 g_wlbi_amounts(161).amount,
2927 g_wlbi_amounts(162).amount,
2928 g_wlbi_amounts(163).amount,
2929 g_wlbi_amounts(164).amount,
2930 g_wlbi_amounts(165).amount,
2931 g_wlbi_amounts(166).amount,
2932 g_wlbi_amounts(167).amount,
2933 g_wlbi_amounts(168).amount,
2934 SYSDATE,
2935 g_user_id,
2936 g_user_id,
2937 g_user_id,
2938 SYSDATE
2939 );
2940 END LOOP; --For each PS element
2941 END IF; -- IF PS Elements exists
2942
2943
2944
2945 END LOOP; -- position_rec
2946 END Populate_POS_WS_Lines;
2947
2948 FUNCTION Get_Next_Export_Seq RETURN NUMBER IS
2949 l_export_id NUMBER;
2950 BEGIN
2951 select psb_export_s.nextval into l_export_id from dual;
2952 RETURN l_export_id;
2953 END;
2954
2955 -- Get the current year end date and first proposed year start date
2956 -- from the calendar.
2957 PROCEDURE Get_Calendar_Dates
2958 ( p_budget_calendar_id IN NUMBER,
2959 p_calendar_start_date OUT NOCOPY DATE,
2960 p_calendar_end_date OUT NOCOPY DATE,
2961 p_cy_end_date OUT NOCOPY DATE,
2962 p_pp_start_date OUT NOCOPY DATE
2963 )
2964 IS
2965 cursor c1 is
2966 select MIN(start_date) start_date , MAX(end_date) end_date
2967 from psb_budget_periods bp
2968 where bp.budget_calendar_id = p_budget_calendar_id
2969 and budget_period_type = 'Y';
2970
2971 cursor c2 is
2972 select bp.budget_period_id, yt.year_category_type, bp.start_date, bp.end_date
2973 from psb_budget_year_types yt,
2974 psb_budget_periods bp
2975 where
2976 yt.budget_year_type_id = bp.budget_year_type_id
2977 and bp.budget_period_type = 'Y'
2978 and bp.budget_calendar_id = p_budget_calendar_id
2979 order by bp.start_date;
2980
2981 BEGIN
2982
2983 FOR c1_rec in c1 LOOP
2984 p_calendar_start_date := C1_rec.Start_Date;
2985 p_calendar_end_date := C1_rec.End_Date;
2986 END LOOP;
2987
2988 FOR c2_rec in c2 LOOP
2989 IF c2_rec.year_category_type = 'CY' THEN
2990 p_cy_end_date := c2_rec.End_Date;
2991 END IF;
2992
2993 IF c2_rec.year_category_type = 'PP' THEN
2994 p_pp_start_date := c2_rec.start_date;
2995 EXIT; -- No need to continue, this is sorted by date
2996 END IF;
2997 END LOOP;
2998
2999 END Get_Calendar_Dates;
3000
3001 -- Call this routine on Error
3002 PROCEDURE Log_Messages (p_source_process IN VARCHAR2)
3003 IS
3004 l_return_status VARCHAR2(1);
3005 l_msg_buf VARCHAR2(2000);
3006 l_msg_count NUMBER;
3007 l_reqid NUMBER;
3008 l_rep_req_id NUMBER;
3009 l_userid NUMBER;
3010 retcode NUMBER;
3011 l_desc VARCHAR2(80);
3012 l_program_name VARCHAR2(80);
3013
3014 BEGIN
3015
3016 IF p_source_process = 'MOVE_TO_PSB' THEN
3017 -- g_msg_export_id set while calling PSB_EXCEL2_PVT.Move to PSB
3018 FND_MESSAGE.SET_NAME('PSB', 'PSB_MOVE_TO_PSB_ERR_MSG_HDR');
3019 ELSIF p_source_process = 'MOVE_TO_INTERFACE' THEN
3020 g_msg_export_id := g_export_id;
3021 FND_MESSAGE.SET_NAME('PSB', 'PSB_MOVE_TO_INTF_ERR_MSG_HDR');
3022 ELSIF p_source_process = 'DELETE_WORKSHEET' THEN
3023 FND_MESSAGE.SET_NAME('PSB', 'PSB_DEL_WORKSHT_ERR_MSG_HDR');
3024 END IF;
3025
3026 l_desc := FND_MESSAGE.GET;
3027 l_reqid := FND_GLOBAL.CONC_REQUEST_ID;
3028 l_userid := FND_GLOBAL.USER_ID;
3029
3030 delete from PSB_ERROR_MESSAGES
3031 where source_process = p_source_process
3032 and process_id = g_msg_export_id;
3033
3034 FND_MSG_PUB.Count_And_Get ( p_count => l_msg_count,
3035 p_data => l_msg_buf );
3036
3037
3038
3039 PSB_MESSAGE_S.Insert_Error ( p_source_process => p_source_process,
3040 p_process_id => g_msg_export_id,
3041 p_msg_count => l_msg_count,
3042 p_msg_data => l_msg_buf) ;
3043
3044
3048 program => 'PSBRPERR' ,
3045 -- Also Submit Concurrent Request to Print Error Report
3046 l_rep_req_id := Fnd_Request.Submit_Request
3047 (application => 'PSB' ,
3049 description => l_desc ,
3050 start_time => NULL ,
3051 sub_request => FALSE ,
3052 argument1 => l_reqid
3053 );
3054 --
3055 if l_rep_req_id = 0 then
3056 --
3057 fnd_message.set_name('PSB', 'PSB_FAIL_TO_SUBMIT_REQUEST');
3058 --
3059 end if;
3060
3061 FND_MSG_PUB.initialize;
3062
3063
3064 END Log_Messages;
3065 /*---------------------------------------------------------------------------*/
3066
3067 PROCEDURE Move_To_PSB
3068 (
3069 p_api_version IN NUMBER ,
3070 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
3071 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
3072 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
3073 p_return_status OUT NOCOPY VARCHAR2 ,
3074 p_msg_count OUT NOCOPY NUMBER ,
3075 p_msg_data OUT NOCOPY VARCHAR2 ,
3076 --
3077 p_export_id IN NUMBER,
3078 p_import_worksheet_type IN VARCHAR2 := FND_API.G_MISS_CHAR,
3079 p_amt_tolerance_value_type IN VARCHAR2 := FND_API.G_MISS_CHAR,
3080 p_amt_tolerance_value IN NUMBER := FND_API.G_MISS_NUM,
3081 p_pct_tolerance_value_type IN VARCHAR2 := FND_API.G_MISS_CHAR,
3082 p_pct_tolerance_value IN NUMBER := FND_API.G_MISS_NUM
3083 )
3084 IS
3085 BEGIN
3086 g_msg_export_id := p_export_id;
3087
3088 PSB_EXCEL2_PVT.Move_To_PSB
3089 (
3090 p_api_version => p_api_version,
3091 p_init_msg_list => p_init_msg_list,
3092 p_commit => p_commit,
3093 p_validation_level => p_validation_level,
3094 p_return_status => p_return_status,
3095 p_msg_count => p_msg_count,
3096 p_msg_data => p_msg_data,
3097 --
3098 p_export_id => p_export_id,
3099 p_import_worksheet_type => p_import_worksheet_type,
3100 p_amt_tolerance_value_type => p_amt_tolerance_value_type,
3101 p_amt_tolerance_value => p_amt_tolerance_value,
3102 p_pct_tolerance_value_type => p_pct_tolerance_value_type,
3103 p_pct_tolerance_value => p_pct_tolerance_value
3104 );
3105
3106 END Move_To_PSB;
3107
3108
3109 /*===========================================================================+
3110 | PROCEDURE Move_To_Inter_CP |
3111 +==========================================================================*/
3112 --
3113 -- The Concurrent Program execution file for the program 'Transfer Worksheet
3114 -- from PSB to Interface'.
3115 --
3116 PROCEDURE Move_To_Inter_CP
3117 (
3118 errbuf OUT NOCOPY VARCHAR2 ,
3119 retcode OUT NOCOPY VARCHAR2 ,
3120 --
3121 p_export_name IN VARCHAR2 ,
3122 p_worksheet_id IN NUMBER ,
3123 p_stage_id IN NUMBER := FND_API.G_MISS_NUM ,
3124 p_export_worksheet_type IN VARCHAR2
3125 )
3126 IS
3127 --
3128 l_api_name CONSTANT VARCHAR2(30) := 'Move_To_Inter_CP' ;
3129 l_api_version CONSTANT NUMBER := 1.0 ;
3130 --
3131 l_return_status VARCHAR2(1) ;
3132 l_msg_count NUMBER ;
3133 l_msg_data VARCHAR2(2000) ;
3134 l_msg_index_out NUMBER;
3135 --
3136 BEGIN
3137 --
3138 SAVEPOINT Move_To_Inter_CP_Pvt ;
3139 --
3140 PSB_Excel_Pvt.Move_To_Interface
3141 (
3142 p_api_version => 1.0 ,
3143 p_init_msg_list => FND_API.G_TRUE ,
3144 p_commit => FND_API.G_FALSE ,
3145 p_validation_level => FND_API.G_VALID_LEVEL_FULL ,
3146 p_return_status => l_return_status ,
3147 p_msg_count => l_msg_count ,
3148 p_msg_data => l_msg_data ,
3149 --
3150 p_export_name => p_export_name ,
3151 p_worksheet_id => p_worksheet_id ,
3152 p_stage_id => p_stage_id ,
3153 p_export_worksheet_type => p_export_worksheet_type
3154 );
3155 --
3156 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3157 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3158 END IF ;
3159 --
3160 /* Start Bug No. 2322856 */
3161 -- PSB_MESSAGE_S.Print_Success ;
3162 /* End Bug No. 2322856 */
3163 retcode := 0 ;
3164 --
3165 COMMIT WORK;
3166 --
3167 EXCEPTION
3168 --
3169 WHEN FND_API.G_EXC_ERROR THEN
3170 --
3171 ROLLBACK TO Move_To_Inter_CP_Pvt ;
3172 --
3176 --
3173 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
3174 p_print_header => FND_API.G_TRUE ) ;
3175 retcode := 2 ;
3177 Log_Messages(p_source_process => 'MOVE_TO_INTERFACE');
3178 COMMIT WORK ;
3179 --
3180 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3181 --
3182 ROLLBACK TO Move_To_Inter_CP_Pvt ;
3183 --
3184 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
3185 p_print_header => FND_API.G_TRUE ) ;
3186 retcode := 2 ;
3187 --
3188 Log_Messages(p_source_process => 'MOVE_TO_INTERFACE');
3189 COMMIT WORK ;
3190 --
3191 WHEN OTHERS THEN
3192 --
3193 ROLLBACK TO Move_To_Inter_CP_Pvt ;
3194 --
3195 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
3196 --
3197 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,
3198 l_api_name ) ;
3199 END IF ;
3200 --
3201 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
3202 p_print_header => FND_API.G_TRUE ) ;
3203 retcode := 2 ;
3204 --
3205 Log_Messages(p_source_process => 'MOVE_TO_INTERFACE');
3206 COMMIT WORK ;
3207 --
3208 END Move_To_Inter_CP ;
3209 /*---------------------------------------------------------------------------*/
3210
3211
3212
3213 /*===========================================================================+
3214 | PROCEDURE Move_To_PSB_CP |
3215 +==========================================================================*/
3216 --
3217 -- The Concurrent Program execution file for the program 'Transfer Worksheet
3218 -- from Interface to PSB'.
3219 --
3220 PROCEDURE Move_To_PSB_CP
3221 (
3222 errbuf OUT NOCOPY VARCHAR2 ,
3223 retcode OUT NOCOPY VARCHAR2 ,
3224 --
3225 p_export_id IN NUMBER ,
3226 p_import_worksheet_type IN VARCHAR2 ,
3227 p_amt_tolerance_value_type IN VARCHAR2 ,
3228 p_amt_tolerance_value IN NUMBER ,
3229 p_pct_tolerance_value_type IN VARCHAR2 ,
3230 p_pct_tolerance_value IN NUMBER
3231 )
3232 IS
3233 --
3234 l_api_name CONSTANT VARCHAR2(30) := 'Move_To_PSB_CP' ;
3235 l_api_version CONSTANT NUMBER := 1.0 ;
3236 --
3237 l_error_api_name VARCHAR2(2000);
3238 l_return_status VARCHAR2(1) ;
3239 l_msg_count NUMBER ;
3240 l_msg_data VARCHAR2(2000) ;
3241 l_msg_index_out NUMBER;
3242 --
3243 BEGIN
3244 --
3245 SAVEPOINT Move_To_PSB_CP_Pvt ;
3246 --
3247 PSB_Excel_Pvt.Move_TO_PSB
3248 (
3249 p_api_version => 1.0 ,
3250 p_init_msg_list => FND_API.G_TRUE ,
3251 p_commit => FND_API.G_FALSE ,
3252 p_validation_level => FND_API.G_VALID_LEVEL_FULL ,
3253 p_return_status => l_return_status ,
3254 p_msg_count => l_msg_count ,
3255 p_msg_data => l_msg_data ,
3256 --
3257 p_export_id => p_export_id ,
3258 p_import_worksheet_type => p_import_worksheet_type ,
3259 p_amt_tolerance_value_type => p_amt_tolerance_value_type ,
3260 p_amt_tolerance_value => p_amt_tolerance_value ,
3261 p_pct_tolerance_value_type => p_pct_tolerance_value_type ,
3262 p_pct_tolerance_value => p_pct_tolerance_value
3263 );
3264 --
3265 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3266 l_error_api_name := 'PSB_Excel_Pvt.Move_TO_PSB' ;
3267 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3268 END IF ;
3269 --
3270 retcode := 0 ;
3271 PSB_MESSAGE_S.Get_Success_Message( p_msg_string => l_msg_data ) ;
3272 errbuf := l_msg_data ;
3273 --
3274 COMMIT WORK;
3275 --
3276 EXCEPTION
3277 --
3278 WHEN FND_API.G_EXC_ERROR THEN
3279 --
3280 ROLLBACK TO Move_To_PSB_CP_Pvt ;
3281 --
3282 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
3283 p_print_header => FND_API.G_TRUE ) ;
3284 retcode := 2 ;
3285 --
3286 Log_Messages(p_source_process => 'MOVE_TO_PSB');
3287 COMMIT WORK ;
3288 --
3289 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3290 --
3291 ROLLBACK TO Move_To_PSB_CP_Pvt ;
3292 --
3293 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
3294 p_print_header => FND_API.G_TRUE ) ;
3295 retcode := 2 ;
3296 --
3297 Log_Messages(p_source_process => 'MOVE_TO_PSB');
3298 COMMIT WORK ;
3299 --
3300 WHEN OTHERS THEN
3301 --
3302 ROLLBACK TO Move_To_PSB_CP_Pvt ;
3303 --
3304 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
3305 --
3306 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,
3307 l_api_name ) ;
3308 END IF ;
3309 --
3310 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
3311 p_print_header => FND_API.G_TRUE ) ;
3312 retcode := 2 ;
3313 --
3314 Log_Messages(p_source_process => 'MOVE_TO_PSB');
3315 COMMIT WORK ;
3316 --
3320
3317 END Move_To_PSB_CP ;
3318 /*---------------------------------------------------------------------------*/
3319
3321
3322
3323 /*===========================================================================+
3324 | PROCEDURE Delete_Worksheet |
3325 +==========================================================================*/
3326 --
3327 -- The Program Deletes the Worksheet from the Interface
3328 --
3329 PROCEDURE Del_Worksheet
3330 (
3331 p_api_version IN NUMBER ,
3332 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
3333 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
3334 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
3335 p_return_status OUT NOCOPY VARCHAR2 ,
3336 p_msg_count OUT NOCOPY NUMBER ,
3337 p_msg_data OUT NOCOPY VARCHAR2 ,
3338 --
3339 p_export_id IN NUMBER
3340 )
3341 IS
3342
3343 l_api_name CONSTANT VARCHAR2(30) := 'Del_Worksheet' ;
3344 l_api_version CONSTANT NUMBER := 1.0 ;
3345 --
3346 l_return_status VARCHAR2(1) ;
3347 l_msg_count NUMBER ;
3348 l_msg_data VARCHAR2(2000) ;
3349 --
3350 BEGIN
3351 --dbms_output.put_line('Exporting ...');
3352 --
3353 SAVEPOINT Del_Worksheet_Pvt ;
3354 --
3355 IF NOT FND_API.Compatible_API_Call ( l_api_version,
3356 p_api_version,
3357 l_api_name,
3358 G_PKG_NAME )
3359 THEN
3360 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3361 END IF;
3362 --
3363
3364 IF FND_API.To_Boolean ( p_init_msg_list ) THEN
3365 FND_MSG_PUB.initialize ;
3366 END IF;
3367 --
3368 p_return_status := FND_API.G_RET_STS_SUCCESS ;
3369
3370 DELETE FROM psb_ws_line_balances_i
3371 WHERE export_id = p_export_id;
3372
3373 DELETE FROM psb_ws_columns_i
3374 WHERE export_id = p_export_id;
3375
3376 DELETE FROM psb_worksheets_i
3377 WHERE export_id = p_export_id;
3378
3379 IF FND_API.to_Boolean (p_commit) then
3380 commit work;
3381 END IF;
3382
3383
3384 EXCEPTION
3385
3386 --
3387 WHEN FND_API.G_EXC_ERROR THEN
3388
3389 ROLLBACK TO Del_Worksheet_Pvt;
3390 p_return_status := FND_API.G_RET_STS_ERROR;
3391 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
3392 p_data => p_msg_data );
3393 --
3394 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3395
3396 ROLLBACK TO Del_Worksheet_Pvt;
3397 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3398 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
3399 p_data => p_msg_data );
3400 --
3401 WHEN OTHERS THEN
3402 ROLLBACK TO Del_Worksheet_Pvt;
3403 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3404 --
3405 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3406 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
3407 l_api_name);
3408 END if;
3409 --
3410 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
3411 p_data => p_msg_data );
3412 --
3413
3414 END Del_Worksheet;
3415
3416 /* ----------------------------------------------------------------------- */
3417
3418 /*===========================================================================+
3419 | PROCEDURE Del_Worksheet_CP |
3420 +==========================================================================*/
3421 --
3422 -- The Concurrent Program execution file for the program 'Delete Worksheet
3423 -- from Interface '.
3424 --
3425 PROCEDURE Del_Worksheet_CP
3426 (
3427 errbuf OUT NOCOPY VARCHAR2 ,
3428 retcode OUT NOCOPY VARCHAR2 ,
3429 --
3430 p_export_id IN NUMBER
3431 )
3432 IS
3433 --
3434 l_api_name CONSTANT VARCHAR2(30) := 'Del_Worksheet_CP' ;
3435 l_api_version CONSTANT NUMBER := 1.0 ;
3436 --
3437 l_error_api_name VARCHAR2(2000);
3438 l_return_status VARCHAR2(1) ;
3439 l_msg_count NUMBER ;
3440 l_msg_data VARCHAR2(2000) ;
3441 l_msg_index_out NUMBER;
3442 --
3443 BEGIN
3444 --
3445 SAVEPOINT Del_Worksheet_CP_Pvt ;
3446 --
3447 PSB_Excel_Pvt.Del_Worksheet
3448 (
3449 p_api_version => 1.0 ,
3450 p_init_msg_list => FND_API.G_TRUE ,
3451 p_commit => FND_API.G_FALSE ,
3452 p_validation_level => FND_API.G_VALID_LEVEL_FULL ,
3453 p_return_status => l_return_status ,
3454 p_msg_count => l_msg_count ,
3455 p_msg_data => l_msg_data ,
3456 --
3457 p_export_id => p_export_id
3458 );
3459 --
3460 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3461 l_error_api_name := 'PSB_Excel_Pvt.Del_Worksheet' ;
3462 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3463 END IF ;
3464 --
3465 retcode := 0 ;
3469 COMMIT WORK;
3466 PSB_MESSAGE_S.Get_Success_Message( p_msg_string => l_msg_data ) ;
3467 errbuf := l_msg_data ;
3468 --
3470 --
3471 EXCEPTION
3472 --
3473 WHEN FND_API.G_EXC_ERROR THEN
3474 --
3475 ROLLBACK TO Del_Worksheet_CP_Pvt ;
3476 --
3477 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
3478 p_print_header => FND_API.G_TRUE ) ;
3479 retcode := 2 ;
3480 --
3481 Log_Messages(p_source_process => 'DELETE_WORKSHEET');
3482 COMMIT WORK ;
3483 --
3484 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3485 --
3486 ROLLBACK TO Del_Worksheet_CP_Pvt ;
3487 --
3488 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
3489 p_print_header => FND_API.G_TRUE ) ;
3490 retcode := 2 ;
3491 --
3492 Log_Messages(p_source_process => 'DELETE_WORKSHEET');
3493 COMMIT WORK ;
3494 --
3495 WHEN OTHERS THEN
3496 --
3497 ROLLBACK TO Del_Worksheet_CP_Pvt ;
3498 --
3499 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
3500 --
3501 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,
3502 l_api_name ) ;
3503 END IF ;
3504 --
3505 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
3506 p_print_header => FND_API.G_TRUE ) ;
3507 retcode := 2 ;
3508 --
3509 Log_Messages(p_source_process => 'DELETE_WORKSHEET');
3510 COMMIT WORK ;
3511 --
3512 END Del_Worksheet_CP ;
3513
3514
3515 END PSB_EXCEL_PVT;