[Home] [Help]
PACKAGE BODY: APPS.PA_YEAR_END_ROLLOVER_PKG
Source
1 PACKAGE BODY PA_Year_End_Rollover_PKG AS
2 -- $Header: PABRLYRB.pls 120.13 2007/02/06 09:26:47 rshaik ship $
3
4 -------------------------------------------------------------------------------------
5 -- Execute the Year End Budget Rollover process
6 -------------------------------------------------------------------------------------
7 P_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
8
9 -- # Forward Declaration
10 PROCEDURE Create_bc_balances(p_budget_version_id IN NUMBER,
11 p_last_baselined_version_id IN NUMBER,
12 p_Set_of_books_id IN NUMBER,
13 p_return_status OUT NOCOPY VARCHAR2);
14
15 -- Procedure used to call pa_debug.write for FND logging
16 PROCEDURE LOG_MESSAGE(p_message in VARCHAR2);
17
18 g_procedure_name VARCHAR2(30);
19
20 -- -------------------------------------------------------------------------------------------------+
21 -- Re-introducing the following procedure as acct. summary needs to be build for the working budget
22 -- this is required to display the account level validatio/ funds check failures ..
23 -- -------------------------------------------------------------------------------------------------+
24
25 -------------------------------------------------------------------------------------
26 -- Insert/Update the Budget Summary Account details
27 -------------------------------------------------------------------------------------
28 PROCEDURE Upd_Ins_Budget_Acct_Line (
29 P_Budget_Version_ID IN PA_Budget_Versions.Budget_Version_ID%TYPE,
30 P_Period_Name IN GL_Periods.Period_Name%TYPE,
31 P_Period_Start_Date IN GL_Periods.Start_Date%TYPE,
32 P_Period_End_Date IN GL_Periods.End_Date%TYPE,
33 P_Transfer_Amount IN NUMBER,
34 P_CCID IN GL_Code_Combinations.Code_Combination_ID%TYPE,
35 P_Request_ID IN FND_Concurrent_Requests.Request_ID%TYPE,
36 X_Return_Status OUT NOCOPY VARCHAR2,
37 X_Msg_Count OUT NOCOPY NUMBER,
38 X_Msg_Data OUT NOCOPY VARCHAR2
39 )
40 IS
41
42 -- Local Variables
43 -- l_Update_Count NUMBER;
44 -- l_Error_Message VARCHAR2(200);
45
46 BEGIN
47
48 g_procedure_name := 'Upd_Ins_Budget_Acct_Line';
49
50 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
51
52 BEGIN
53 IF P_DEBUG_MODE = 'Y' THEN
54 log_message('Year_End_Rollover: ' || 'Before Update in Upd_Ins_Budget_Acct_Line');
55 log_message('Year_End_Rollover: ' || 'Transfer Amount : '|| P_Transfer_Amount);
56 END IF;
57 ---------------------------------------------------------------------------------
58 -- Update the Budget Summary Account's Current version Available Amount
59 ---------------------------------------------------------------------------------
60 UPDATE
61 PA_Budget_Acct_Lines
62 SET
63 Curr_Ver_Budget_Amount = nvl(Curr_Ver_Budget_Amount,0) + P_Transfer_Amount,
64 Curr_Ver_Available_Amount = nvl(Curr_Ver_Available_Amount,0) + P_Transfer_Amount,
65 Request_ID = P_Request_ID
66 WHERE
67 Budget_Version_ID = P_Budget_Version_ID
68 -- AND GL_Period_Name = P_Period_Name
69 AND Start_Date = P_Period_Start_Date
70 AND Code_Combination_ID = P_CCID ;
71
72 -- l_Update_Count := SQL%ROWCOUNT;
73 --l_Error_Message := SUBSTR(SQLERRM, 1, 200);
74 IF P_DEBUG_MODE = 'Y' THEN
75 log_message('Year_End_Rollover: ' || SQL%ROWCOUNT || ' record(s) updated');
76 log_message('Year_End_Rollover: ' || 'After Update in Upd_Ins_Budget_Acct_Line');
77 --log_message('Year_End_Rollover: ' || 'Error Message : '|| l_Error_Message );
78 END IF;
79
80 END;
81
82 /* ============================================================================================== +
83 -- No new records should be created in pa_budget_acct_lines as the records created for the new
84 -- period will not have ccid. CCID is generated when account generator called in SUBMIT MODE.
85 -- Acct. generator in SUBMIT mode will also create data into pa_budget_acct_lines table
86
87 IF l_Update_Count = 0 -- No Data found and no records are updated
88 THEN
89 ---------------------------------------------------------------------------------
90 -- Create new Budget Summary Account's data for the non-existent details
91 ---------------------------------------------------------------------------------
92 INSERT INTO
93 PA_BUDGET_ACCT_LINES (
94 Budget_Acct_Line_ID,
95 Budget_Version_ID,
96 GL_Period_Name,
97 Start_Date,
98 End_Date,
99 Code_Combination_ID,
100 Prev_Ver_Budget_Amount,
101 Prev_Ver_Available_Amount,
102 Curr_Ver_Budget_Amount,
103 Curr_Ver_Available_Amount,
104 Accounted_Amount,
105 Creation_date,
106 Created_By,
107 Last_Update_date,
108 Last_Updated_By,
109 Request_ID,
110 Last_Update_Login
111 )
112 VALUES (
113 PA_BUDGET_ACCT_LINES_S.NextVal,
114 P_Budget_Version_ID,
115 P_Period_Name,
116 P_Period_Start_Date,
117 P_Period_End_Date,
118 P_CCID,
119 0,
120 0,
121 -- 0,
122 P_Transfer_Amount,
123 P_Transfer_Amount,
124 0,
125 sysdate,
126 FND_GLOBAL.User_ID,
127 sysdate,
128 FND_GLOBAL.User_ID,
129 P_Request_ID,
130 FND_GLOBAL.User_ID
131 ) ;
132 IF P_DEBUG_MODE = 'Y' THEN
133 log_message('Year_End_Rollover: ' || 'Error Step 2 : '|| SQLERRM );
134 END IF;
135 END IF;
136 ============================================================================================== */
137
138 RETURN;
139
140 END Upd_Ins_Budget_Acct_Line;
141
142 -- -------------------------------------------------------------------------------------------------+
143 PROCEDURE Year_End_Rollover (
144 P_Closing_Year IN NUMBER, -- PA_Budget_Versions.Closing_Year%TYPE ?
145 P_Organization_ID IN PA_Organizations_V.Organization_ID%TYPE,
146 P_From_Project_Number IN PA_Projects_All.Segment1%TYPE,
147 P_To_Project_Number IN PA_Projects_All.Segment1%TYPE,
148 P_Request_ID IN FND_Concurrent_Requests.Request_ID%TYPE,
149 X_Return_Status OUT NOCOPY VARCHAR2,
150 X_Msg_Count OUT NOCOPY NUMBER,
151 X_Msg_Data OUT NOCOPY VARCHAR2
152 )
153 IS
154
155 -- Local Variables
156 l_Top_Task_ID PA_Tasks.Task_ID%TYPE;
157 l_Parent_Member_ID PA_Resource_List_Members.Parent_Member_ID%TYPE;
158 l_Project_Status_Code VARCHAR2(100);
159 l_Change_Reason_Code VARCHAR2(100);
160 l_Message_Code_Error VARCHAR2(100);
161 l_Message_Name VARCHAR2(100);
162 l_Rel_Lock NUMBER;
163 l_Proceed_Flag BOOLEAN;
164 l_First_Time_Entry BOOLEAN;
165 l_Lock_Name VARCHAR2(200);
166 l_Total_Rollover_Amount NUMBER;
167 l_Transfer_Amount NUMBER;
168 l_UnSwept_Amount NUMBER;
169 l_Funds_Chk_Rsrv_Status VARCHAR2(1);
170
171 l_Accounted_Period_Type GL_Sets_Of_Books.Accounted_Period_Type%TYPE;
172 l_New_Budget_Version_ID PA_Budget_Versions.Budget_Version_ID%TYPE;
173 l_Work_Budget_Version_ID PA_Budget_Versions.Budget_Version_ID%TYPE;
174 l_Work_Resource_Assign_ID PA_Resource_Assignments.Resource_Assignment_ID%TYPE;
175 l_Bslnd_Budget_Version_ID PA_Budget_Versions.Budget_Version_ID%TYPE;
176 l_Project_ID PA_Projects_all.Project_ID%TYPE;
177 l_Project_Completion_Date DATE;
178 l_Task_ID PA_Tasks.Task_ID%TYPE;
179 l_Budget_Type_Code PA_Budget_Types.Budget_Type_Code%TYPE;
180 l_Encumbrance_Type_ID GL_Encumbrance_Types.Encumbrance_Type_ID%TYPE;
181 l_External_Budget_Code PA_Budgetary_Control_Options.External_Budget_Code%TYPE;
182 l_GL_Budget_Version_ID PA_Budgetary_Control_Options.GL_Budget_Version_ID%TYPE;
183 l_Raw_Cost_Flag PA_Budget_Entry_Methods.Raw_Cost_Flag%TYPE;
184 l_Burdened_Cost_Flag PA_Budget_Entry_Methods.Burdened_Cost_Flag%TYPE;
185 l_Entry_Level_Code PA_Budget_Entry_Methods.Entry_Level_Code%TYPE;
186 l_Resource_List_Member_ID PA_Resource_List_Members.Resource_List_Member_ID%TYPE;
187 l_CCID GL_Code_Combinations.Code_Combination_ID%TYPE;
188 l_New_CCID GL_Code_Combinations.Code_Combination_ID%TYPE;
189
190 --
191 l_Set_Of_Books_ID GL_Sets_Of_Books.Set_Of_Books_ID%TYPE;
192 l_Cur_Yr_Last_Prd_Name GL_Periods.Period_Name%TYPE;
193 l_Cur_Yr_First_Prd_Start_Dt GL_Periods.Start_Date%TYPE;
194 l_Cur_Yr_Last_Prd_Start_Dt GL_Periods.Start_Date%TYPE;
195 l_Cur_Yr_Last_Prd_End_Dt GL_Periods.End_Date%TYPE;
196 l_Next_Yr_First_Prd_Name GL_Periods.Period_Name%TYPE;
197 l_Next_Yr_First_Prd_Start_Dt GL_Periods.Start_Date%TYPE;
198 l_Next_Yr_First_Prd_End_Dt GL_Periods.End_Date%TYPE;
199
200
201 l_Return_Status VARCHAR2(100);
202 l_Msg_Count NUMBER;
203 l_Msg_Data VARCHAR2(2000);
204
205 l_Err_Code NUMBER;
206 l_Err_Stage VARCHAR2(200);
207 l_Err_Stack VARCHAR2(200);
208 l_balance_type PA_Budgetary_Control_Options.Balance_Type%type;
209 l_cc_budget_type_code PA_Budgetary_Control_Options.Budget_Type_Code%type;
210
211 -- Local Exception Variables
212 l_IU_Bdgt_Line_ERR EXCEPTION;
213 l_Lock_Bdgt_Err EXCEPTION;
214 l_Get_Res_Assign_Err EXCEPTION;
215 l_PA_BC_GL_FCK_ERR EXCEPTION;
216 l_PA_BC_CC_FCK_ERR EXCEPTION;
217 l_SUBMIT_BASELINE_ERR EXCEPTION;
218 l_IU_Bdgt_Acct_Err EXCEPTION;
219 l_cbc_not_supported EXCEPTION;
220
221 -------------------------------------------------------------------------------------
222 -- Cursor to fetch all the eligible Budget versions that are required to be
223 -- processed for Year End Budget Rollover process
224 -------------------------------------------------------------------------------------
225 CURSOR C1_BUDGET IS
226 SELECT
227 PROJ.project_id Project_ID,
228 PROJ.Project_Status_Code Project_Status_Code,
229 PROJ.Completion_Date Project_Completion_Date,
230 BV.budget_version_id Budget_Version_ID,
231 BV.budget_type_code Budget_Type_Code,
232 BCO.Encumbrance_Type_ID Encumbrance_Type_ID,
233 BCO.External_Budget_Code External_Budget_Code,
234 BCO.GL_Budget_Version_ID GL_Budget_Version_ID,
235 BEM.Raw_Cost_Flag Raw_Cost_Flag,
236 BEM.Burdened_Cost_Flag Burdened_Cost_Flag,
237 BEM.Entry_Level_Code Entry_Level_Code,
238 BCO.Balance_type Balance_Type
239 FROM
240 PA_Budgetary_Control_Options BCO,
241 PA_Projects PROJ,
242 PA_Budget_Versions BV,
243 PA_Budget_Entry_Methods BEM
244 WHERE
245 PROJ.Carrying_Out_organization_id =
246 NVL(P_Organization_ID, PROJ.Carrying_Out_organization_id )
247 AND ( P_From_Project_Number IS NULL OR PROJ.Segment1 >= P_From_Project_Number )
248 AND ( P_To_Project_Number IS NULL OR PROJ.Segment1 <= P_To_Project_Number )
249 AND nvl(PROJ.template_flag,'N') <> 'Y'
250 -- AND PROJ.project_status_code <> 'CLOSED'
251 AND PROJ.Project_ID = BV.Project_ID
252 AND BV.Project_ID = BCO.Project_ID
253 AND BV.Budget_Type_Code = BCO.Budget_Type_Code
254 AND BV.Budget_Status_Code = 'B' -- Baselined ONLY Budget
255 AND BV.Current_Flag = 'Y' -- Latest Budget Version
256 AND BV.Budget_Entry_Method_Code = BEM.Budget_Entry_Method_Code
257 AND BCO.Balance_Type = 'E'
258 AND nvl(BCO.Yr_End_Rollover_Year,-1) <> P_Closing_Year;
259
260 -------------------------------------------------------------------------------------
261 -- Cursor to fetch all the eligible Budget Lines that are required to be
262 -- processed for Year End Budget Rollover process
263 -------------------------------------------------------------------------------------
264 CURSOR C2_BUDGET_LINES IS
265 SELECT
266 BCBL.Resource_List_Member_ID Resource_List_Member_ID,
267 BCBL.Project_ID Project_ID,
268 /* Commented out for bug 2838796 BCBL.Task_ID Task_ID, */
269 RA.Task_ID Task_ID, --Changed to RA.Task_Id bug 2838796
270 BL.Code_Combination_ID CCID,
271 SUM(BCBL.Budget_Period_To_Date -
272 ( BCBL.Actual_Period_To_Date + BCBL.Encumb_Period_To_Date) ) Transfer_Amount,
273 MAX(BL.START_DATE) Budget_period_start_date
274 FROM
275 PA_Resource_Assignments RA,
276 PA_Budget_Lines BL,
277 PA_BC_Balances BCBL
278 WHERE
279 RA.Budget_Version_ID = l_Bslnd_Budget_Version_ID
280 AND RA.Resource_Assignment_ID = BL.Resource_Assignment_ID
281 AND BCBL.Start_Date = BL.Start_Date
282 AND RA.Resource_List_Member_ID= BCBL.Resource_List_Member_ID
283 AND RA.Budget_Version_ID = BCBL.Budget_Version_ID
284 AND RA.Project_ID = BCBL.Project_ID
285 -- AND NVL(RA.Task_ID, 0) = BCBL.Task_ID -- bug 2838796
286 AND ( (BCBL.Balance_Type = 'BGT' and RA.Task_ID = BCBL.Task_ID ) --bug 2838796 added start
287 OR
288 ( BCBL.Balance_Type <> 'BGT' AND
289 (( RA.Task_ID = Decode(l_Entry_Level_Code, 'P', 0,
290 'T', BCBL.Top_Task_ID,
291 'L', BCBL.Task_ID ))
292 OR
293 ( l_Entry_Level_Code = 'M' AND
294 RA.Task_ID IN ( BCBL.Top_Task_ID, BCBL.Task_ID )
295 ))
296 )
297 ) --bug 2838796 added ends
298 AND BCBL.Start_Date BETWEEN l_Cur_Yr_First_Prd_Start_Dt
299 AND l_Cur_Yr_Last_Prd_Start_Dt
300 AND BCBL.Set_Of_Books_ID = l_Set_Of_Books_ID
301 AND BCBL.Start_Date IN ( SELECT A.Start_Date
302 FROM GL_Period_Statuses A
303 WHERE
304 A.application_id = 101
305 AND A.set_of_books_id = l_Set_Of_Books_ID
306 AND A.Period_Year = P_Closing_year
307 AND A.Adjustment_Period_Flag <> 'Y'
308 AND A.Period_Type = l_Accounted_Period_Type
309 )
310 GROUP BY
311 BCBL.Resource_List_Member_ID,
312 BCBL.Project_ID,
313 /* BCBL.Task_ID, commented out for bug 2838796 */
314 RA.Task_ID,
315 BL.Code_Combination_ID
316 HAVING SUM(BCBL.Budget_Period_To_Date -
317 ( BCBL.Actual_Period_To_Date + BCBL.Encumb_Period_To_Date) ) > 0;
318
319 BEGIN -- Begin of executing the Year End Budget Rollover process
320
321 g_procedure_name := 'Year_End_Rollover';
322 -----------------------------------------------------------------------------------+
323 -- Get the Set of Books ID from PA_Implementations
324 -----------------------------------------------------------------------------------+
325 IF P_DEBUG_MODE = 'Y' THEN
326 log_message('Year_End_Rollover: ' || 'Started the Process');
327 --log_message('Year_End_Rollover: '||'DebugChange 03/17/2003 bug 2838796');
328 END IF;
329
330 -----------------------------------------------------------------------------------+
331 -- Invoke the Sweeper process to sweep all the encumbrance etries
332 -- from PA_BC_PACKETS to PA_BC_BALANCES
333 -----------------------------------------------------------------------------------+
334 PA_Sweeper.Update_Act_Enc_Balance (
335 X_Return_Status => l_Return_Status,
336 X_Error_Message_Code => l_Msg_Data
337 );
338
339 IF l_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
340 IF P_DEBUG_MODE = 'Y' THEN
341 PA_Fck_Util.debug_msg('Year_End_Rollover: ' || 'Error occured while running sweeper process PA_Sweeper.Update_Act_Enc_Balance');
342 PA_Fck_Util.debug_msg('Year_End_Rollover: ' || 'Action: Contact Oracle support team');
343 PA_Fck_Util.debug_msg('Year_End_Rollover: ' || 'X_Error_Message_Code:'||l_Msg_Data);
344 END IF;
345 RETURN;
346 END IF;
347
348 -- # Get Ledger_id
349 SELECT Set_Of_Books_ID
350 INTO l_Set_Of_Books_ID
351 FROM PA_Implementations;
352
353 IF P_DEBUG_MODE = 'Y' THEN
354 log_message('Year_End_Rollover: ' || 'Request ID : '|| P_Request_ID);
355 log_message('Year_End_Rollover: ' || 'Set of Books ID : '|| l_Set_Of_Books_ID);
356 log_message('Year_End_Rollover: ' || 'Organization ID : '|| P_Organization_ID);
357 END IF;
358
359 SELECT Accounted_Period_Type
360 INTO l_Accounted_Period_Type
361 FROM GL_Sets_Of_Books
362 WHERE Set_Of_Books_ID = l_Set_Of_Books_ID ;
363
364 IF P_DEBUG_MODE = 'Y' THEN
365 log_message('Year_End_Rollover: ' || 'Accounted Period Type : ' || l_Accounted_Period_Type);
366 END IF;
367
368 -----------------------------------------------------------------------------------+
369 -- Fetch the first/last period details for a given closing fiscal year and
370 -- first period details of a next year
371 -----------------------------------------------------------------------------------+
372 Get_Period_Details (
373 P_Fiscal_Year => P_Closing_year,
374 P_Set_Of_Books_ID => l_Set_Of_Books_ID,
375 P_Accounted_Period_Type => l_Accounted_Period_Type,
376 X_Cur_Yr_Last_Prd_Name => l_Cur_Yr_Last_Prd_Name,
377 X_Cur_Yr_First_Prd_Start_Dt => l_Cur_Yr_First_Prd_Start_Dt,
378 X_Cur_Yr_Last_Prd_Start_Dt => l_Cur_Yr_Last_Prd_Start_Dt,
379 X_Cur_Yr_Last_Prd_End_Dt => l_Cur_Yr_Last_Prd_End_Dt,
380 X_Next_Yr_First_Prd_Name => l_Next_Yr_First_Prd_Name,
381 X_Next_Yr_First_Prd_Start_Dt => l_Next_Yr_First_Prd_Start_Dt,
382 X_Next_Yr_First_Prd_End_Dt => l_Next_Yr_First_Prd_End_Dt,
383 X_Return_Status => l_Return_Status,
384 X_Msg_Count => l_Msg_Count,
385 X_Msg_Data => l_Msg_Data
386 );
387
388 IF l_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN -- Need to test carefully
389 X_Msg_Count := l_Msg_Count;
390 X_Msg_Data := l_Msg_Data;
391 X_Return_Status := l_Return_Status;
392
393 g_procedure_name := 'Year_End_Rollover';
394
395 log_message('Year_End_Rollover: '||'Get_Period_Details-> Error: X_Msg_Data : ' || l_Msg_Data);
396
397 FND_MSG_PUB.add_Exc_msg( P_Pkg_Name => 'PA_Year_End_Rollover_PKG',
398 P_Procedure_Name => 'Get_Period_Details');
399 RETURN;
400 END IF;
401
402 IF P_DEBUG_MODE = 'Y' THEN
403 g_procedure_name := 'Year_End_Rollover';
404 log_message('Year_End_Rollover: ' || 'Executed Get_Period_Details API');
405 END IF;
406
407 -----------------------------------------------------------------------------------+
408 -- Loop thru all the eligible budget versions
409 -----------------------------------------------------------------------------------+
410 FOR C1 IN C1_BUDGET
411 LOOP
412 BEGIN
413
414 IF P_DEBUG_MODE = 'Y' THEN
415 log_message('Year_End_Rollover: ' || 'Total Count in Cursor C1 : ' || C1_Budget%ROWCOUNT );
416 END IF;
417
418 -- Fetch cursor values into local variables (Re-Initialize)
419 l_Project_ID := C1.Project_ID;
420 l_Project_Status_Code := C1.Project_Status_Code;
421 l_Project_Completion_Date := C1.Project_Completion_Date;
422 l_Budget_Type_Code := C1.Budget_Type_Code;
423 l_Bslnd_Budget_Version_ID := C1.Budget_Version_ID;
424 l_Encumbrance_Type_ID := C1.Encumbrance_Type_ID;
425 l_External_Budget_Code := C1.External_Budget_Code;
426 l_GL_Budget_Version_ID := C1.GL_Budget_Version_ID;
427 l_Raw_Cost_Flag := C1.Raw_Cost_Flag;
428 l_Burdened_Cost_Flag := C1.Burdened_Cost_Flag;
429 l_Entry_Level_Code := C1.Entry_Level_Code;
430 l_Balance_Type := C1.Balance_Type;
431
432 IF P_DEBUG_MODE = 'Y' THEN
433 log_message('Year_End_Rollover: ' || '--');
434 log_message('Year_End_Rollover: ' || 'Budget Version Details :');
435 log_message('Year_End_Rollover: ' || '-------------------------');
436 log_message('Year_End_Rollover: ' || 'Project ID :' || l_Project_ID );
437 log_message('Year_End_Rollover: ' || 'Project Status Code :' || l_Project_Status_Code );
438 log_message('Year_End_Rollover: ' || 'Project Completion Date :' || l_Project_Completion_Date );
439 log_message('Year_End_Rollover: ' || 'Budget Type Code :' || l_Budget_Type_Code );
440 log_message('Year_End_Rollover: ' || 'Baselined Budget Ver ID :' || l_Bslnd_Budget_Version_ID );
441 log_message('Year_End_Rollover: ' || 'Encumbrance Type ID :' || l_Encumbrance_Type_ID );
442 log_message('Year_End_Rollover: ' || 'External Budget Code :' || l_External_Budget_Code );
443 log_message('Year_End_Rollover: ' || 'GL Budget Version ID :' || l_GL_Budget_Version_ID );
444 log_message('Year_End_Rollover: ' || 'Raw Cost Flag :' || l_Raw_Cost_Flag );
445 log_message('Year_End_Rollover: ' || 'Burdened Cost Flag :' || l_Burdened_Cost_Flag );
446 log_message('Year_End_Rollover: ' || 'Budget Entry Level Code :' || l_Entry_Level_Code );
447 log_message('Year_End_Rollover: ' || 'Balance Type :' || l_Balance_Type );
448 END IF;
449
450 -----------------------------------------------+
451 -- 1.1: Disabling CBC ...
452 -----------------------------------------------+
453 If nvl(l_Budget_Type_Code,'GL') = 'CC' then
454 RAISE l_cbc_not_supported;
455 End If;
456
457 -- For GL Budget associated to a project with Dual budget enabled
458 If nvl(l_balance_type,'B') = 'E' then
459 Begin
460 select 'CC'
461 into l_cc_budget_type_code
462 from pa_budgetary_control_options cc
463 where cc.project_id = l_Project_ID
464 and cc.external_budget_code = 'CC';
465 Exception
466 When no_data_found then
467 l_cc_budget_type_code := null;
468 When too_many_rows then
469 -- This has been added to handle case if multiple
470 -- budget types could be added 'cause of issue with
471 -- PABDINTG form
472 l_cc_budget_type_code := 'CC';
473 End;
474
475 If nvl(l_cc_budget_type_code,'GL') = 'CC' then
476 RAISE l_cbc_not_supported;
477 End If;
478 End If;
479
480 -- Re-Initialise user lock name
481 l_lock_Name := 'YRENDRLVR:'||l_Project_ID||':'||l_Budget_Type_Code ;
482 l_First_Time_Entry := TRUE;
483
484 l_Total_Rollover_Amount := 0 ;
485 l_Proceed_Flag := FALSE; /* 2699417 */
486
487 log_message('Year_End_Rollover: '||'Lock Name inside C1 :' || l_lock_Name );
488
489 FOR C2 IN C2_BUDGET_LINES
490 LOOP
491
492 log_message('Year_End_Rollover: '||'Total Count in Cursor C2 : ' || C2_BUDGET_LINES%ROWCOUNT );
493 log_message('Year_End_Rollover: ' || '-------------------------');
494 log_message('Year_End_Rollover: Resource_List_Member_ID:' || C2.Resource_List_Member_ID);
495 log_message('Year_End_Rollover: Task_ID:' || C2.Task_ID);
496 log_message('Year_End_Rollover: Code_Combination_ID:' || C2.ccid);
497 log_message('Year_End_Rollover: Transfer_Amount:' || C2.Transfer_Amount);
498 log_message('Year_End_Rollover: Budget_period_start_date:' || C2.Budget_period_start_date);
499 log_message('Year_End_Rollover: ' || '-------------------------');
500
501
502 l_Message_Code_Error := NULL;
503
504 -- Check for project status
505 IF (l_Project_Status_Code = 'CLOSED' OR
506 l_Project_Completion_Date < l_Next_Yr_First_Prd_Start_Dt) THEN
507
508 l_Message_Code_Error := 'PA_BC_PROJ_CLOSED';
509 l_Proceed_Flag := FALSE; -- Not to proceed for further processing
510
511 IF P_DEBUG_MODE = 'Y' THEN
512 log_message('Year_End_Rollover: ' || 'Exiting from Inside Project Status is closed');
513 END IF;
514
515 EXIT;
516 END IF;
517
518 IF l_Project_Completion_Date < l_Next_Yr_First_Prd_Start_Dt THEN
519 l_Message_Code_Error := 'PA_BC_PROJ_END_DATE';
520 l_Proceed_Flag := FALSE; -- Not to proceed for further processing
521
522 IF P_DEBUG_MODE = 'Y' THEN
523 log_message('Year_End_Rollover: ' || 'Exiting from Inside because of Project End Date');
524 END IF;
525
526 EXIT;
527 END IF;
528
529 l_Transfer_Amount := 0 ;
530
531 BEGIN
532
533 IF P_DEBUG_MODE = 'Y' THEN
534 log_message('Year_End_Rollover: ' || 'Entered INTO Cursor C2');
535 END IF;
536
537 IF l_First_Time_Entry = TRUE THEN -- Execute only once in the begining
538
539 IF P_DEBUG_MODE = 'Y' THEN
540 log_message('Year_End_Rollover: ' || 'Creating / copying New Budget');
541 END IF;
542
543 Create_Working_Budget (
544 P_Project_ID => l_Project_ID,
545 P_Budget_Type_Code => l_Budget_Type_Code,
546 P_Budget_Version_ID => l_Bslnd_Budget_Version_ID,
547 P_Request_ID => P_Request_ID,
548 X_Work_Budget_Version_ID => l_Work_Budget_Version_ID,
549 X_Return_Status => l_Return_Status,
550 X_Msg_Count => l_Msg_Count,
551 X_Msg_Data => l_Msg_Data
552 );
553
554 IF l_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
555 IF P_DEBUG_MODE = 'Y' THEN
556 g_procedure_name := 'Year_End_Rollover';
557 log_message(g_procedure_name||':Create_Working_Budget Failed:'||l_Msg_Data);
558 END IF;
559
560 l_First_Time_Entry := FALSE;
561 l_Proceed_Flag := FALSE; /* Not to proceed for further processing in case of failure
562 added for bug 2699417 */
563 EXIT; -- Exit from the inner LOOP to process the next budget version ID
564 END IF;
565
566 l_First_Time_Entry := FALSE; -- Make sure it won't be repeated for
567 -- every Budget Line
568 END IF;
569
570 ---------------------------------------------------------------------------+
571 -- Re-Initialize the local variables after every fetch
572 ---------------------------------------------------------------------------+
573 l_Resource_List_Member_ID := C2.Resource_List_Member_ID;
574 l_Task_ID := C2.Task_ID;
575 l_CCID := C2.CCID;
576 l_Transfer_Amount := C2.Transfer_Amount;
577
578 IF P_DEBUG_MODE = 'Y' THEN
579 g_procedure_name := 'Year_End_Rollover';
580 log_message('Year_End_Rollover: ' || '--------------------------------');
581 log_message('Year_End_Rollover: ' || 'Budget Line Details for Budget Version '||
582 l_Work_Budget_Version_ID ||' are : ');
583 log_message('Year_End_Rollover: ' || '--------------------------------');
584 log_message('Year_End_Rollover: ' || 'Res List Member ID : '|| l_Resource_List_Member_ID);
585 log_message('Year_End_Rollover: ' || 'Task ID : '|| l_Task_ID);
586 log_message('Year_End_Rollover: ' || 'CCID ID : '|| l_CCID);
587 log_message('Year_End_Rollover: ' || 'Transfer Amount : '|| l_Transfer_Amount);
588 log_message('Year_End_Rollover: ' || '--');
589 END IF;
590
591 -- Calculate the unswept amounts from PA_BC_PACKETS
592 BEGIN
593
594 -- Derive Top Task ID
595 BEGIN
596 SELECT Top_Task_ID
597 INTO l_Top_Task_ID
598 FROM PA_TASKS
599 WHERE Task_ID = l_Task_ID
600 AND Project_ID = l_Project_ID ;
601
602 IF P_DEBUG_MODE = 'Y' THEN
603 log_message('Year_End_Rollover: ' || 'PA_BC_PACKETS: Top Task ID : '|| l_Top_Task_ID );
604 END IF;
605
606 EXCEPTION
607 WHEN NO_DATA_FOUND THEN
608 IF P_DEBUG_MODE = 'Y' THEN
609 log_message('Year_End_Rollover: ' || 'Top Task ID Not Found. Task : '|| l_Task_ID );
610 l_top_task_id := 0; --bug 2838796
611 END IF;
612 END;
613
614 -- Derive Parent Member ID
615 BEGIN
616 SELECT Parent_Member_ID
617 INTO l_Parent_Member_ID
618 FROM PA_RESOURCE_LIST_MEMBERS
619 WHERE Resource_List_Member_ID = l_Resource_List_Member_ID;
620
621 IF P_DEBUG_MODE = 'Y' THEN
622 log_message('Year_End_Rollover: ' || 'PA_BC_PACKETS: Parent Member ID : '|| l_Parent_Member_ID );
623 END IF;
624 EXCEPTION
625 WHEN NO_DATA_FOUND THEN
626 IF P_DEBUG_MODE = 'Y' THEN
627 log_message('Year_End_Rollover: ' || 'Parent Mem ID Not Found. Res List Member ID : '||
628 l_Resource_List_Member_ID );
629 END IF;
630 raise; --bug 2838796
631 END;
632
633 -- Fetch the UnSwept amount from PA_BC_PACKETS
634 l_UnSwept_Amount := 0;
635
636 BEGIN
637 IF P_DEBUG_MODE = 'Y' THEN
638 log_message('Year_End_Rollover: ' || 'Fetching amounts from PA_BC_PACKETS');
639 log_message('Year_End_Rollover: ' || 'Budget_Version_ID : ' || l_Work_Budget_Version_ID );
640 log_message('Year_End_Rollover: ' || 'Project_ID : ' || l_Project_ID );
641 log_message('Year_End_Rollover: ' || 'Top_Task_ID : ' || l_Top_Task_ID );
642 END IF;
643
644 SELECT
645 SUM(Accounted_DR - Accounted_CR)
646 INTO
647 l_UnSwept_Amount
648 FROM
649 PA_BC_PACKETS
650 WHERE
651 Budget_Version_ID = l_Bslnd_Budget_Version_ID
652 AND Project_ID = l_Project_ID
653 -- AND nvl(Top_Task_ID,0) = l_Top_Task_ID --bug 2838796 start change
654 AND (( nvl(l_Task_ID,0) = Decode(l_Entry_Level_Code, 'P', 0,
655 'T', Top_Task_ID,
656 'L', Task_ID ))
657 OR
658 ( l_Entry_Level_Code = 'M' AND
659 l_Task_ID IN ( Top_Task_ID, Task_ID ))) --bug 2838796 end change
660 AND Status_Code = 'A' -- Approved Transactions
661 AND Balance_Posted_Flag = 'N' -- Not yet posted/swept
662 AND Result_Code like 'P%' -- Pass Code series
663 AND Resource_List_Member_ID = l_Resource_List_Member_ID
664 AND Parent_Resource_ID = l_Parent_Member_ID ;
665 END;
666
667 IF P_DEBUG_MODE = 'Y' THEN
668 log_message('Year_End_Rollover: ' || 'l_UnSwept_Amount = '|| l_UnSwept_Amount );
669 END IF;
670
671 END;
672
673 --
674 -- Add the unswept amounts to the original transferred amount
675 --
676 l_Transfer_Amount := NVL(l_Transfer_Amount,0) - NVL(l_UnSwept_Amount,0);
677
678 ---------------------------------------------------------------------------+
679 -- Get the Resource Assignment ID for the working Budget Version
680 ---------------------------------------------------------------------------+
681 IF P_DEBUG_MODE = 'Y' THEN
682 log_message('Year_End_Rollover: ' || 'Work Budget Ver ID : '|| l_Work_Budget_Version_ID);
683 END IF;
684
685 BEGIN
686 SELECT
687 RA.Resource_Assignment_ID
688 INTO
689 l_Work_Resource_Assign_ID
690 FROM
691 PA_Budget_Versions BV,
692 PA_Resource_Assignments RA
693 WHERE
694 RA.resource_list_member_id = l_Resource_List_Member_ID
695 AND RA.Budget_Version_ID = l_Work_Budget_Version_ID
696 AND RA.Project_ID = l_Project_ID
697 AND RA.Task_ID = l_Task_ID
698 AND BV.Budget_Status_Code = 'W'
699 AND BV.Budget_Version_ID = RA.Budget_Version_ID
700 AND BV.Project_ID = RA.Project_ID
701 AND BV.Project_ID = l_Project_ID;
702 EXCEPTION
703 WHEN NO_DATA_FOUND THEN
704 IF P_DEBUG_MODE = 'Y' THEN
705 log_message('Year_End_Rollover: ' || 'Res Assignment ID Not Found');
706 END IF;
707 RAISE l_Get_Res_Assign_ERR;
708 END;
709
710 IF P_DEBUG_MODE = 'Y' THEN
711 log_message('Year_End_Rollover: ' || 'Res Assignment ID : '|| l_Work_Resource_Assign_ID);
712 log_message('Year_End_Rollover: ' || '--');
713 END IF;
714
715 ------------------------------------------------------------------------------------+
716 -- Update the last period (where the account exists) of closing year budget line for
717 -- the fetched Budget Version ID
718 ------------------------------------------------------------------------------------+
719 IF P_DEBUG_MODE = 'Y' THEN
720 log_message('Year_End_Rollover: ' || 'API Executing : Upd_Ins_Budget_Line ');
721 log_message('Year_End_Rollover: ' || '--');
722 END IF;
723
724 l_new_ccid := null;
725
726 Upd_Ins_Budget_Line (
727 P_Budget_Version_ID => l_Work_Budget_Version_ID,
728 P_Budget_Type_Code => l_Budget_Type_Code,
729 P_Resource_Assignment_ID => l_Work_Resource_Assign_ID,
730 P_Period_Name => NULL,
731 P_Period_Start_Date => C2.Budget_Period_Start_Date,
732 P_Period_End_Date => NULL,
733 P_Transfer_Amount => (-1)*l_Transfer_Amount,
734 P_Project_ID => l_Project_ID,
735 P_Task_ID => l_Task_ID,
736 P_Resource_List_Member_ID => l_Resource_List_Member_ID,
737 P_Raw_Cost_Flag => l_Raw_Cost_Flag,
738 P_Burdened_Cost_Flag => l_Burdened_Cost_Flag,
739 P_CCID => l_CCID,
740 P_Request_ID => P_Request_ID,
741 P_Period_New_or_Closing => 'CLOSING',
742 P_New_CCID => l_new_ccid,
743 X_Return_Status => l_Return_Status,
744 X_Msg_Count => l_Msg_Count,
745 X_Msg_Data => l_Msg_Data
746 );
747
748 IF l_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
749 log_message('Year_End_Rollover: Old period: Upd_Ins_Budget_Line failed:'||l_Msg_Data);
750 g_procedure_name := 'Year_End_Rollover';
751 RAISE l_IU_Bdgt_Line_ERR;
752 END IF;
753
754 IF P_DEBUG_MODE = 'Y' THEN
755 g_procedure_name := 'Year_End_Rollover';
756 log_message('Year_End_Rollover: ' || 'API Executing : Upd_Ins_Budget_Line - SUCCESSFUL');
757 log_message('Year_End_Rollover: ' || '--');
758 END IF;
759 -- End of Updating the last period Budget Line Record
760
761 ---------------------------------------------------------------------------
762 -- Update the last period of closing year budget account line
763 -- for all the fetched Budget Version IDs
764 ---------------------------------------------------------------------------
765 IF P_DEBUG_MODE = 'Y' THEN
766 log_message('Year_End_Rollover: ' || 'API Executing : Upd_Ins_Budget_Acct_Line ');
767 log_message('Year_End_Rollover: ' || '--');
768 END IF;
769 Upd_Ins_Budget_Acct_Line (
770 P_Budget_Version_ID => l_Work_Budget_Version_ID,
771 P_Period_Name => NULL,
772 P_Period_Start_Date => C2.Budget_Period_Start_Date,
773 P_Period_End_Date => NULL,
774 P_Transfer_Amount => (-1)*l_Transfer_Amount,
775 P_CCID => l_CCID,
776 P_Request_ID => P_Request_ID,
777 X_Return_Status => l_Return_Status,
778 X_Msg_Count => l_Msg_Count,
779 X_Msg_Data => l_Msg_Data
780 );
781
782 IF l_Return_Status <> FND_API.G_RET_STS_SUCCESS
783 THEN
784 log_message('Year_End_Rollover: Old period: Upd_Ins_Budget_Acct_Line failed:'||l_Msg_Data);
785 g_procedure_name := 'Year_End_Rollover';
786 RAISE l_IU_Bdgt_Acct_ERR;
787 END IF ;
788
789 IF P_DEBUG_MODE = 'Y' THEN
790 g_procedure_name := 'Year_End_Rollover';
791 log_message('Year_End_Rollover: ' || 'API Executing : Upd_Ins_Budget_Acct_Line - SUCCESSFUL');
792 log_message('Year_End_Rollover: ' || '--');
793 END IF;
794 -- End of Updating the last period Budget Account Line Record
795
796 ---------------------------------------------------------------------------+
797 -- Update the first period of next year budget line for all
798 -- the fetched Budget Version IDs
799 ---------------------------------------------------------------------------+
800 IF P_DEBUG_MODE = 'Y' THEN
801 log_message('Year_End_Rollover: ' || 'API Executing : Upd_Ins_Budget_Line ');
802 log_message('Year_End_Rollover: ' || '--');
803 END IF;
804
805 l_new_ccid := null;
806
807 Upd_Ins_Budget_Line (
808 P_Budget_Version_ID => l_Work_Budget_Version_ID,
809 P_Budget_Type_Code => l_Budget_Type_Code,
810 P_Resource_Assignment_ID => l_Work_Resource_Assign_ID,
811 P_Period_Name => l_Next_Yr_First_Prd_Name,
812 P_Period_Start_Date => l_Next_Yr_First_Prd_Start_Dt,
813 P_Period_End_Date => l_Next_Yr_First_Prd_End_Dt,
814 P_Transfer_Amount => l_Transfer_Amount,
815 P_Project_ID => l_Project_ID,
816 P_Task_ID => l_Task_ID,
817 P_Resource_List_Member_ID => l_Resource_List_Member_ID,
818 P_Raw_Cost_Flag => l_Raw_Cost_Flag,
819 P_Burdened_Cost_Flag => l_Burdened_Cost_Flag,
820 P_CCID => l_CCID,
821 P_Request_ID => P_Request_ID,
822 P_Period_New_Or_Closing => 'NEW',
823 P_New_CCID => l_new_ccid,
824 X_Return_Status => l_Return_Status,
825 X_Msg_Count => l_Msg_Count,
826 X_Msg_Data => l_Msg_Data
827 );
828
829 IF l_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
830 log_message('Year_End_Rollover: New period: Upd_Ins_Budget_Line failed:'||l_Msg_Data);
831 g_procedure_name := 'Year_End_Rollover';
832 RAISE l_IU_Bdgt_Line_ERR;
833 END IF;
834
835 IF P_DEBUG_MODE = 'Y' THEN
836 g_procedure_name := 'Year_End_Rollover';
837 log_message('Year_End_Rollover: ' || 'API Executing : Upd_Ins_Budget_Line - SUCCESSFUL');
838 log_message('Year_End_Rollover: ' || '--');
839 END IF;
840 -- End of Updating the first period Budget Line Record
841
842 ---------------------------------------------------------------------------
843 -- Update the first period of next year budget account line
844 -- for all the fetched Budget Version IDs
845 ---------------------------------------------------------------------------
846 If l_new_ccid is not null and l_new_ccid <> l_CCID then
847 -- This can happen if there is a line already existing
848 -- for RAID/Start Date/Currency combo with a diff CCID
849 l_CCID := l_new_ccid;
850 End If;
851
852 ---------------------------------------------------------------------------
853 -- Update the first period of next year budget account line
854 -- for all the fetched Budget Version IDs
855 ---------------------------------------------------------------------------
856 Upd_Ins_Budget_Acct_Line (
857 P_Budget_Version_ID => l_Work_Budget_Version_ID,
858 P_Period_Name => l_Next_Yr_First_Prd_Name,
859 P_Period_Start_Date => l_Next_Yr_First_Prd_Start_Dt,
860 P_Period_End_Date => l_Next_Yr_First_Prd_End_Dt,
861 P_Transfer_Amount => l_Transfer_Amount,
862 P_CCID => l_CCID,
863 P_Request_ID => P_Request_ID,
864 X_Return_Status => l_Return_Status,
865 X_Msg_Count => l_Msg_Count,
866 X_Msg_Data => l_Msg_Data
867 );
868
869 IF l_Return_Status <> FND_API.G_RET_STS_SUCCESS
870 THEN
871 log_message('Year_End_Rollover: New period: Upd_Ins_Budget_Acct_Line failed:'||l_Msg_Data);
872 g_procedure_name := 'Year_End_Rollover';
873 RAISE l_IU_Bdgt_Acct_ERR;
874 END IF;
875
876 IF P_DEBUG_MODE = 'Y' THEN
877 g_procedure_name := 'Year_End_Rollover';
878 log_message('Year_End_Rollover: ' || 'API : Upd_Ins_Budget_Acct_Line - SUCCESSFUL');
879 log_message('Year_End_Rollover: ' || '--');
880 END IF;
881 -- End of Updating the last period Budget Account Line Record
882
883 -- Accumulate the Rollover Amount
884 l_Total_Rollover_Amount := nvl(l_Total_Rollover_Amount,0) + nvl(l_Transfer_Amount,0) ;
885
886 IF P_DEBUG_MODE = 'Y' THEN
887 log_message ('Year_End_Rollover: ' || 'Cumulative Amount : ' || l_Total_Rollover_Amount );
888 END IF;
889
890 l_Proceed_Flag := TRUE;
891
892 EXCEPTION
893 WHEN l_Get_Res_Assign_Err THEN
894 l_Proceed_Flag := FALSE;
895 Year_End_Rollover_Log (
896 P_Budget_Version_ID => l_Work_Budget_Version_Id,
897 P_Message_Name => 'PA_BC_GET_RES_ASSIGN_ERR',
898 P_Request_ID => P_Request_ID,
899 P_Lock_Name => l_Lock_Name );
900
901 WHEN l_IU_Bdgt_Line_Err THEN
902 l_Proceed_Flag := FALSE;
903 Year_End_Rollover_Log (
904 P_Budget_Version_ID => l_Work_Budget_Version_Id,
905 P_Message_Name => 'PA_BC_IU_BDGT_LINE_ERR',
906 P_Request_ID => P_Request_ID,
907 P_Lock_Name => l_Lock_Name );
908
909 WHEN l_IU_Bdgt_Acct_Err THEN
910 l_Proceed_Flag := FALSE;
911 Year_End_Rollover_Log (
912 P_Budget_Version_ID => l_Work_Budget_Version_Id,
913 P_Message_Name => upper('pa_bc_IU_Bdgt_Acct_Err'),
914 P_Request_ID => P_Request_ID,
915 P_Lock_Name => l_Lock_Name );
916
917 END; -- End of processing one set (Last/First period) of Budget Line
918 END LOOP ; -- End of reading all the Budget Lines, end of loop C2;
919
920 IF l_Proceed_Flag = TRUE THEN
921
922 COMMIT;
923 -- # This commit is required so that the following call to account generator which is an
924 -- # autonomous procedure will be able to query the working budget's budget lines ..
925
926 -- # Call Account generator to generate accounts on all the new lines
927 -- # that were created, calling it in 'Submit Mode'
928 -- # This call is being made so that the working budget will also have all ccid's
929
930 PA_BUDGET_ACCOUNT_PKG.Gen_Account (
931 P_Budget_Version_ID => l_Work_Budget_Version_Id,
932 P_Calling_Mode => 'SUBMIT',
933 X_Return_Status => l_return_status,
934 X_Msg_Count => l_msg_count,
935 X_Msg_Data => l_msg_data) ;
936
937 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
938 l_Proceed_Flag := FALSE;
939 Year_End_Rollover_Log (
940 P_Budget_Version_ID => l_Work_Budget_Version_Id,
941 P_Message_Name => 'PA_BC_GEN_FAI_ACCT',
942 P_Request_ID => P_Request_ID,
943 P_Lock_Name => l_Lock_Name );
944 END IF;
945
946 -- Commit the working budget
947 COMMIT;
948
949 -- ## Submit Budget
950 IF P_DEBUG_MODE = 'Y' THEN
951 log_message('Year_End_Rollover: ' || 'API Executing : PA_Budget_Utils2.Submit_Budget');
952 log_message('Year_End_Rollover: ' || '--');
953 END IF;
954
955 PA_Budget_Utils2.Submit_Budget (
956 X_Budget_Version_ID => l_Work_Budget_Version_ID,
957 X_Err_Code => l_Err_Code,
958 X_Err_Stage => l_Err_Stage,
959 X_Err_Stack => l_Err_Stack
960 );
961
962 IF l_Err_Code <> 0 THEN
963 log_message('Year_End_Rollover: Submit Budget failed: l_Err_Code['||l_Err_Code||'] l_Err_Stage['||l_Err_Stage||']');
964 log_message('Year_End_Rollover: Submit Budget failed: l_Err_Stack['||l_Err_Stack||']');
965 ROLLBACK;
966 l_Message_Name := 'PA_BC_SBMT_BDGT_ERR';
967 RAISE l_SUBMIT_BASELINE_ERR;
968 END IF;
969
970 IF P_DEBUG_MODE = 'Y' THEN
971 log_message('Year_End_Rollover: ' || 'API : PA_Budget_Utils2.Submit_Budget - SUCCESSFUL');
972 log_message('Year_End_Rollover: ' || '--');
973 END IF;
974 -- End of submitting the modified Budget Version
975
976 -- # Baseline the draft budget
977 IF P_DEBUG_MODE = 'Y' THEN
978 log_message('Year_End_Rollover: ' || 'API Executing : PA_Budget_Core.Baseline');
979 log_message('Year_End_Rollover: ' || '--');
980 END IF;
981
982 PA_Budget_Core.Baseline(
983 X_Draft_Version_ID => l_Work_Budget_Version_ID,
984 X_Mark_as_Original => 'N',
985 X_Verify_Budget_Rules => 'N',
986 X_Err_Code => l_Err_Code,
987 X_Err_Stage => l_Err_Stage,
988 X_Err_Stack => l_Err_Stack
989 );
990
991 IF l_Err_Code <> 0 THEN
992 log_message('Year_End_Rollover: Baseline Budget failed: l_Err_Code['||l_Err_Code||'] l_Err_Stage['||l_Err_Stage||']');
993 log_message('Year_End_Rollover: Baseline Budget failed: l_Err_Stack['||l_Err_Stack||']');
994 ROLLBACK;
995 l_Message_Name := 'PA_BC_BASLN_BDGT_ERR';
996 RAISE l_SUBMIT_BASELINE_ERR;
997 END IF;
998
999 IF P_DEBUG_MODE = 'Y' THEN
1000 log_message('Year_End_Rollover: ' || 'API Executing : PA_Budget_Core.Baseline - SUCCESSFUL');
1001 log_message('Year_End_Rollover: ' || '--');
1002 END IF;
1003 -- End of Baselining the submitted Budget Version
1004
1005 -- Get the latest Baselined Budget Version ID
1006 BEGIN
1007 SELECT Budget_Version_ID
1008 INTO l_New_Budget_Version_ID
1009 FROM PA_Budget_Versions
1010 WHERE Project_ID = l_Project_ID
1011 AND Budget_Type_Code = l_Budget_Type_Code
1012 AND Current_Flag = 'Y'
1013 AND Budget_Status_Code = 'B';
1014
1015 EXCEPTION
1016 WHEN NO_DATA_FOUND THEN
1017 ROLLBACK;
1018 l_Message_Name := 'PA_BC_BASLN_BDGT_ERR';
1019 RAISE l_SUBMIT_BASELINE_ERR;
1020 END;
1021
1022 -- ## Call Budget account generator ..to insert the zero $ budget lines (for missed GL periods)
1023 -- ## and generate the acccount for these lines
1024 -- ## Note: Use Gen_Acct_All_Lines which is a non-autonomous procedure as the
1025 -- ## baselined budget is not saved yet.
1026
1027 PA_BUDGET_ACCOUNT_PKG.Gen_Acct_All_Lines (
1028 P_Budget_Version_ID => l_New_Budget_Version_ID,
1029 P_Calling_Mode => 'BASELINE' ,
1030 P_Budget_Type_Code => l_Budget_Type_Code,
1031 P_Budget_Entry_Level_Code => l_Entry_Level_Code,
1032 P_Project_ID => l_Project_ID,
1033 X_Return_Status => l_return_status,
1034 X_Msg_Count => l_msg_count,
1035 X_Msg_Data => l_msg_data) ;
1036
1037 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1038 ROLLBACK;
1039 l_Message_Name := 'PA_BC_GEN_FAI_ACCT';
1040 RAISE l_SUBMIT_BASELINE_ERR;
1041 END IF;
1042
1043 ---------------------------------------------------------------------------+
1044 -- Rework the budget to bring the updated budget details in Working mode
1045 ---------------------------------------------------------------------------+
1046 IF P_DEBUG_MODE = 'Y' THEN
1047 log_message('Year_End_Rollover: ' || 'API Executing : PA_Budget_Utils2.Rework_Budget');
1048 log_message('Year_End_Rollover: ' || '--');
1049 END IF;
1050
1051 PA_Budget_Utils2.Rework_Budget (
1052 X_Budget_Version_ID => l_Work_Budget_Version_Id,
1053 X_Err_Code => l_Err_Code,
1054 X_Err_Stage => l_Err_Stage,
1055 X_Err_Stack => l_Err_Stack
1056 );
1057
1058 IF l_Err_Code <> 0 THEN
1059 log_message('Year_End_Rollover: Rework Budget failed: l_Err_Code['||l_Err_Code||'] l_Err_Stage['||l_Err_Stage||']');
1060 log_message('Year_End_Rollover: Rework Budget failed: l_Err_Stack['||l_Err_Stack||']');
1061 ROLLBACK;
1062 l_Message_Name := 'PA_BC_RWRK_BDGT_ERR';
1063 RAISE l_SUBMIT_BASELINE_ERR;
1064 END IF;
1065
1066 IF P_DEBUG_MODE = 'Y' THEN
1067 log_message('Year_End_Rollover: ' || 'API : PA_Budget_Utils2.Rework_Budget - SUCCESSFUL');
1068 log_message('Year_End_Rollover: ' || '--');
1069 END IF;
1070 -- End of Updating the modified Baselined Budget Version
1071
1072 l_Return_Status := null;
1073
1074 -- # Create bc balances record for the baselined version ..
1075 IF P_DEBUG_MODE = 'Y' THEN
1076 log_message('Year_End_Rollover: ' || ' Calling create_bc_balances');
1077 END IF;
1078
1079 CREATE_BC_BALANCES(p_budget_version_id => l_New_Budget_Version_ID,
1080 p_last_baselined_version_id => l_Bslnd_Budget_Version_ID,
1081 p_Set_of_books_id => l_Set_Of_Books_ID,
1082 p_return_status => l_Return_Status);
1083
1084 IF P_DEBUG_MODE = 'Y' THEN
1085 g_procedure_name := 'Year_End_Rollover';
1086 log_message('Year_End_Rollover: ' || ' After Calling create_bc_balances,l_Return_Status:'||l_Return_Status);
1087 END IF;
1088
1089 IF l_Return_Status = 'E' THEN -- API returns 'S' for Success and 'E' for Failure
1090
1091 ROLLBACK;
1092
1093 l_Message_Name := 'PA_BC_IU_BC_BAL_ERR';
1094 RAISE l_SUBMIT_BASELINE_ERR;
1095
1096 END IF;
1097
1098 l_Return_Status := null;
1099
1100 -- ## Create Accounting events and funds check
1101 -- Following call will do both
1102 IF P_DEBUG_MODE = 'Y' THEN
1103 log_message('Year_End_Rollover: ' || ' Calling pa_budget_fund_pkg.create_events_and_fundscheck');
1104 log_message('Year_End_Rollover: ' || ' Parameters: l_External_Budget_Code:'||l_External_Budget_Code||';l_New_Budget_Version_ID:'||l_New_Budget_Version_ID);
1105 END IF;
1106
1107 PA_BUDGET_FUND_PKG.CREATE_EVENTS_AND_FUNDSCHECK
1108 (P_calling_module => 'Year_End_Rollover',
1109 P_mode => 'Force',
1110 P_External_Budget_Code => l_External_Budget_Code,
1111 P_budget_version_id => l_New_Budget_Version_ID,
1112 P_cc_budget_version_id => NULL,
1113 P_result_code => l_Return_Status );
1114
1115 IF P_DEBUG_MODE = 'Y' THEN
1116 log_message('Year_End_Rollover: ' || ' After Calling pa_budget_fund_pkg.create_events_and_fundscheck,l_Return_Status:'||l_Return_Status);
1117 END IF;
1118
1119 IF l_Return_Status = 'E' THEN -- API returns 'S' for Success and 'E' for Failure
1120
1121 ROLLBACK;
1122
1123 IF l_External_Budget_Code = 'GL' THEN
1124 l_Message_Name := 'PA_BC_GL_FCK_ERR';
1125 RAISE l_PA_BC_GL_FCK_ERR;
1126 ELSIF l_External_Budget_Code = 'CC' THEN
1127 l_Message_Name := 'PA_BC_CC_FCK_ERR';
1128 RAISE l_PA_BC_CC_FCK_ERR;
1129 END IF;
1130
1131 END IF;
1132
1133 IF P_DEBUG_MODE = 'Y' THEN
1134 log_message('Year_End_Rollover: ' || 'API:pa_budget_fund_pkg.create_events_and_fundscheck- SUCCESSFUL');
1135 END IF;
1136 -- End of executing "create accounting events and funds checking"
1137
1138 -- Replace the Working Budget with Baselined Budget on pa_bc_commitments
1139 IF P_DEBUG_MODE = 'Y' THEN
1140 log_message('Year_End_Rollover: ' || 'Replace the Working Budget with Baselined Budget on pa_bc_commitments');
1141 END IF;
1142
1143 UPDATE PA_BC_COMMITMENTS
1144 SET Budget_Version_ID = l_New_Budget_Version_ID,
1145 Request_ID = P_Request_ID
1146 WHERE Project_ID = l_Project_ID
1147 AND Budget_Version_ID = l_Bslnd_Budget_Version_ID ;
1148
1149 -- Bug 5206537 : Procedure to stamp latest budget version id and budget line id on CDL and bc commitments
1150 PA_FUNDS_CONTROL_UTILS.Update_bvid_blid_on_cdl_bccom ( p_bud_ver_id => l_New_Budget_Version_ID,
1151 p_calling_mode => 'YEAR END ROLLOVER');
1152
1153
1154 END IF; -- If Proceed_flg = TRUE
1155
1156 -----------------------------------------------------------------------------+
1157 -- Mark (update) the Budget Version as successfully carried out the
1158 -- Year End Budget Rollover process
1159 -----------------------------------------------------------------------------+
1160 IF P_DEBUG_MODE = 'Y' THEN
1161 log_message('Year_End_Rollover: ' || 'Updating amounts and flag to Budget Version');
1162 log_message('Year_End_Rollover: ' || '--');
1163 END IF;
1164
1165 IF (l_Proceed_Flag = FALSE AND l_Message_Code_Error IS NOT NULL) THEN
1166
1167 IF P_DEBUG_MODE = 'Y' THEN
1168 log_message('Year_End_Rollover: ' || 'This Project is Closed / End Date problem');
1169 log_message('Total Rollover amount='||l_Total_Rollover_Amount); /* added for bug 2699417 */
1170 log_message('P_Closing_Year='||P_Closing_Year); /* added for bug 2699417 */
1171 log_message('l_Project_ID='||l_Project_ID); /* added for bug 2699417 */
1172 log_message('l_Budget_Type_Code='||l_Budget_Type_Code); /* added for bug 2699417 */
1173 END IF;
1174
1175 UPDATE PA_Budgetary_Control_Options
1176 SET
1177 Yr_End_Rollover_Message = l_Message_Code_Error,
1178 Yr_End_Rollover_Year = -1,
1179 Yr_End_Rollover_Flag = 'E',
1180 Request_ID = P_Request_ID
1181 WHERE
1182 Project_ID = l_Project_ID
1183 AND Budget_Type_Code = l_Budget_Type_Code ;
1184
1185 IF SQL%ROWCOUNT = 0 THEN
1186
1187 IF P_DEBUG_MODE = 'Y' THEN
1188 log_message('Doing ROLLBACK after update PA_Budgetary_Control_Options');
1189 END IF;
1190
1191 ROLLBACK;
1192 l_Message_Name := 'PA_BC_UPD_CNT_OPTN_ERR';
1193 RAISE l_SUBMIT_BASELINE_ERR;
1194 END IF;
1195
1196 IF P_DEBUG_MODE = 'Y' THEN
1197 log_message('Year_End_Rollover: ' || 'Updated as Project Closed / End Date problem');
1198 log_message('Year_End_Rollover: ' || '--');
1199 END IF;
1200
1201 ELSIF l_Proceed_Flag = TRUE THEN
1202
1203 IF P_DEBUG_MODE = 'Y' THEN
1204 log_message('Year_End_Rollover: ' || 'Processing correctly');
1205 END IF;
1206
1207 UPDATE PA_Budgetary_Control_Options
1208 SET
1209 Yr_End_Rollover_Amount = l_Total_Rollover_Amount,
1210 Yr_End_Rollover_Year = P_Closing_Year,
1211 Yr_End_Rollover_Flag = 'S', -- successfully done
1212 Request_ID = P_Request_ID,
1213 Yr_End_Rollover_Message= NULL
1214 WHERE
1215 Project_ID = l_Project_ID
1216 AND Budget_Type_Code = l_Budget_Type_Code ;
1217
1218 IF SQL%ROWCOUNT = 0 THEN
1219 ROLLBACK;
1220 l_Message_Name := 'PA_BC_UPD_CNT_OPTN_ERR';
1221 RAISE l_SUBMIT_BASELINE_ERR;
1222 END IF;
1223
1224 IF P_DEBUG_MODE = 'Y' THEN
1225 log_message('Year_End_Rollover: ' || 'Updated SUCCESSFULLY');
1226 log_message('Year_End_Rollover: ' || '--');
1227 END IF;
1228 -- End of updating the success result for Budget Version
1229
1230 -- Updating Change Reason for new Baselined Budget Version
1231 IF P_DEBUG_MODE = 'Y' THEN
1232 log_message('Year_End_Rollover: ' || 'Updated Budget Version Record');
1233 END IF;
1234
1235 l_Change_Reason_Code := 'YEAR END BUDGET ROLLOVER';
1236 UPDATE PA_Budget_Versions
1237 SET Change_Reason_Code = l_Change_Reason_Code
1238 WHERE Budget_Version_ID = l_New_Budget_Version_ID ;
1239
1240 IF SQL%ROWCOUNT = 0 THEN
1241 ROLLBACK;
1242 l_Message_Name := 'PA_BC_UPD_BDGT_VER_ERR';
1243 RAISE l_SUBMIT_BASELINE_ERR;
1244 END IF;
1245 -- End of updating the success result for Budget Version
1246
1247 /* Commenting for Bug 5726535
1248 -----------------------------------------------------------------------------+
1249 -- UnLock/Release the Budget version record that was acquired in the begining
1250 -----------------------------------------------------------------------------+
1251 IF P_DEBUG_MODE = 'Y' THEN
1252 log_message('Year_End_Rollover: ' || 'Releasing the lock '|| l_Lock_Name );
1253 log_message('Year_End_Rollover: ' || '--');
1254 END IF;
1255
1256 l_Rel_Lock := PA_Debug.Release_User_Lock(l_Lock_Name);
1257
1258 IF P_DEBUG_MODE = 'Y' THEN
1259 log_message('Year_End_Rollover: ' || 'Value of Release Lock '|| l_Rel_Lock );
1260 END IF;
1261
1262 IF l_Rel_Lock <> 0 THEN
1263 RAISE l_Lock_Bdgt_ERR;
1264 END IF;
1265 */
1266
1267 END IF; -- IF (l_Proceed_Flag = FALSE AND l_Message .....
1268
1269 EXCEPTION
1270 WHEN l_cbc_not_supported THEN
1271 l_Proceed_Flag := FALSE;
1272 Year_End_Rollover_Log (
1273 P_Budget_Version_ID => l_Bslnd_Budget_Version_ID,
1274 P_Message_Name => 'PA_CBC_NOT_SUPPORTED',
1275 P_Request_ID => P_Request_ID,
1276 P_Lock_Name => l_Lock_Name );
1277
1278 WHEN l_Lock_Bdgt_Err THEN
1279 X_Msg_Count := 1;
1280 X_Msg_Data := SUBSTR(SQLERRM, 1, 240);
1281 X_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
1282 Year_End_Rollover_Log (
1283 P_Budget_Version_ID => l_Work_Budget_Version_Id,
1284 P_Message_Name => 'PA_BC_LOCK_BDGT_ERR',
1285 P_Request_ID => P_Request_ID,
1286 P_Lock_Name => l_Lock_Name );
1287
1288 WHEN l_SUBMIT_BASELINE_ERR THEN
1289 X_Msg_Count := 1;
1290 X_Msg_Data := SUBSTR(SQLERRM, 1, 240);
1291 X_Return_Status := FND_API.G_RET_STS_ERROR;
1292 Year_End_Rollover_Log (
1293 P_Budget_Version_ID => l_Work_Budget_Version_Id,
1294 P_Message_Name => l_Message_Name,
1295 P_Request_ID => P_Request_ID,
1296 P_Lock_Name => l_Lock_Name
1297 );
1298
1299 -- Added these exceptions for Bug 2699417
1300 WHEN l_PA_BC_GL_FCK_ERR THEN
1301 Year_End_Rollover_Log (
1302 P_Budget_Version_ID => l_Work_Budget_Version_Id,
1303 P_Message_Name => l_Message_Name,
1304 P_Request_ID => P_Request_ID,
1305 P_Lock_Name => l_Lock_Name
1306 );
1307 WHEN l_PA_BC_CC_FCK_ERR THEN
1308 Year_End_Rollover_Log (
1309 P_Budget_Version_ID => l_Work_Budget_Version_Id,
1310 P_Message_Name => l_Message_Name,
1311 P_Request_ID => P_Request_ID,
1312 P_Lock_Name => l_Lock_Name
1313 );
1314 -- End of Bug fix 2699417
1315
1316 END; -- End of processing single Budget Version
1317
1318 COMMIT; -- Commit the all processed project budget versions
1319
1320 /* added for bug 2699417 */
1321 l_Project_ID := null;
1322 l_Project_Status_Code := null;
1323 l_Project_Completion_Date := null;
1324 l_Budget_Type_Code := null;
1325 l_Bslnd_Budget_Version_ID := null;
1326 l_Encumbrance_Type_ID := null;
1327 l_External_Budget_Code := null;
1328 l_GL_Budget_Version_ID := null;
1329 l_Raw_Cost_Flag := null;
1330 l_Burdened_Cost_Flag := null;
1331 l_Entry_Level_Code := null;
1332 l_Balance_Type := null;
1333 l_cc_budget_type_code := null;
1334
1335 END LOOP ; -- End of reading all the Budget Versions
1336
1337 COMMIT; -- Commit the whole process
1338
1339 IF P_DEBUG_MODE = 'Y' THEN
1340 log_message('Year_End_Rollover: ' || 'X_Msg_Count : '|| X_Msg_Count );
1341 log_message('Year_End_Rollover: ' || 'X_Msg_Data : '|| X_Msg_Data );
1342 log_message('Year_End_Rollover: ' || 'X_Return_Status : '|| X_Return_Status );
1343 END IF;
1344
1345 RETURN;
1346
1347 EXCEPTION
1348 WHEN OTHERS THEN
1349 IF P_DEBUG_MODE = 'Y' THEN
1350 log_message('Year_End_Rollover: ' || 'In When Others. End');
1351 END IF;
1352 /* Commenting for Bug 5726535
1353 l_Rel_Lock := PA_Debug.Release_User_Lock(l_Lock_Name);
1354 */
1355 /* Bug 5726535 - The Year End Rollover Flag is updated to 'E' here in an autonomous transaction if an
1356 unhandled exception occurs */
1357 Upd_Yr_End_Rollover_Flag_To_E (
1358 P_Request_ID => P_Request_ID);
1359 /* Bug 5726535 - End */
1360 X_Msg_Count := 1;
1361 X_Msg_Data := SUBSTR(SQLERRM, 1, 240);
1362 X_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
1363 log_message('Year_End_Rollover: ' ||'In When Others. End->X_Msg_Data :'||X_Msg_Data);
1364 ROLLBACK;
1365 RETURN;
1366
1367 END Year_End_Rollover; -- End of executing the Year End Budget Rollover process
1368
1369 -------------------------------------------------------------------------------------
1370 -- To fetch all the necessary Period details for executing Year End Rollover process
1371 --
1372 -- Fetch the first/last period details for a given closing fiscal year and
1373 -- first period details of a next year
1374 -------------------------------------------------------------------------------------
1375 PROCEDURE Get_Period_Details (
1376 P_Fiscal_Year IN NUMBER,
1377 P_Set_Of_Books_ID IN GL_Sets_Of_Books.Set_Of_Books_ID%TYPE,
1378 P_Accounted_Period_Type IN GL_Sets_Of_Books.Accounted_Period_Type%TYPE,
1379 X_Cur_Yr_Last_Prd_Name OUT NOCOPY GL_Periods.Period_Name%TYPE,
1380 X_Cur_Yr_First_Prd_Start_Dt OUT NOCOPY GL_Periods.Start_Date%TYPE,
1381 X_Cur_Yr_Last_Prd_Start_Dt OUT NOCOPY GL_Periods.Start_Date%TYPE,
1382 X_Cur_Yr_Last_Prd_End_Dt OUT NOCOPY GL_Periods.End_Date%TYPE,
1383 X_Next_Yr_First_Prd_Name OUT NOCOPY GL_Periods.Period_Name%TYPE,
1384 X_Next_Yr_First_Prd_Start_Dt OUT NOCOPY GL_Periods.Start_Date%TYPE,
1385 X_Next_Yr_First_Prd_End_Dt OUT NOCOPY GL_Periods.End_Date%TYPE,
1386 X_Return_Status OUT NOCOPY VARCHAR2,
1387 X_Msg_Count OUT NOCOPY NUMBER,
1388 X_Msg_Data OUT NOCOPY VARCHAR2
1389 )
1390 IS
1391
1392 BEGIN
1393
1394 IF P_DEBUG_MODE = 'Y' THEN
1395 g_procedure_name := 'Get_Period_Details';
1396 log_message('Year_End_Rollover: ' || '------------------------------' );
1397 log_message('Year_End_Rollover: ' || 'Start API : Get_Period_Details' );
1398 log_message('Year_End_Rollover: ' || '------------------------------' );
1399 log_message('Year_End_Rollover: ' || 'Fiscal Closing Year : ' || P_Fiscal_year);
1400 log_message('Year_End_Rollover: ' || 'Set Of Books ID : ' || P_Set_Of_Books_ID);
1401 END IF;
1402
1403 -----------------------------------------------------------------------------------+
1404 -- Fetch the first and last period dates for the given closing year
1405 -----------------------------------------------------------------------------------+
1406 BEGIN
1407 SELECT
1408 MIN(PSTS.Start_Date),
1409 MAX(PSTS.start_date),
1410 MAX(PSTS.end_date)
1411 INTO
1412 X_Cur_Yr_First_Prd_Start_Dt,
1413 X_Cur_Yr_Last_Prd_Start_Dt,
1414 X_Cur_Yr_Last_Prd_End_Dt
1415 FROM
1416 GL_Period_Statuses PSTS
1417 WHERE
1418 PSTS.application_id = 101
1419 AND PSTS.set_of_books_id = P_Set_Of_Books_ID
1420 AND PSTS.Period_Year = P_Fiscal_year
1421 AND PSTS.Adjustment_Period_Flag <> 'Y'
1422 AND PSTS.Period_Type = P_Accounted_Period_Type ;
1423 END;
1424 IF P_DEBUG_MODE = 'Y' THEN
1425 log_message('Year_End_Rollover: ' || 'Cur Yr First Prd Start Date : '|| to_char(X_Cur_Yr_First_Prd_Start_Dt, 'DD-MON-YYYY'));
1426 log_message('Year_End_Rollover: ' || 'Cur Yr Last Prd Start Date : '|| to_char(X_Cur_Yr_Last_Prd_Start_Dt, 'DD-MON-YYYY'));
1427 log_message('Year_End_Rollover: ' || 'Cur Yr Last Prd End Date : '|| to_char(X_Cur_Yr_Last_Prd_End_Dt, 'DD-MON-YYYY')); END IF;
1428 -----------------------------------------------------------------------------------+
1429 -- Fetch the First period dates for the next fiscal year w.r.t. given closing year
1430 -----------------------------------------------------------------------------------+
1431 BEGIN
1432 SELECT
1433 MIN(PSTS.Start_Date),
1434 MIN(PSTS.End_Date)
1435 INTO
1436 X_Next_Yr_First_Prd_Start_Dt,
1437 X_Next_Yr_First_Prd_End_Dt
1438 FROM
1439 GL_Period_Statuses PSTS
1440 WHERE
1441 PSTS.application_id = 101 -- = 8721 ?
1442 AND PSTS.set_of_books_id = P_Set_Of_Books_ID
1443 AND PSTS.Period_Year = P_Fiscal_year + 1
1444 AND PSTS.Adjustment_Period_Flag <> 'Y'
1445 AND PSTS.Period_Type = P_Accounted_Period_Type ;
1446 END;
1447 IF P_DEBUG_MODE = 'Y' THEN
1448 log_message('Year_End_Rollover: ' || 'Next Yr First Prd Start Date : '|| to_char(X_Next_Yr_First_Prd_Start_Dt, 'DD-MON-YYYY'));
1449 log_message('Year_End_Rollover: ' || 'Next Yr First Prd End Date : '|| to_char(X_Next_Yr_First_Prd_End_Dt, 'DD-MON-YYYY'));
1450 END IF;
1451 -----------------------------------------------------------------------------------+
1452 -- Fetch the Last Period Name for a given closing year
1453 -----------------------------------------------------------------------------------+
1454 BEGIN
1455 SELECT
1456 GS.Period_Name
1457 INTO
1458 X_Cur_Yr_Last_Prd_Name
1459 FROM
1460 GL_Period_Statuses GS
1461 WHERE
1462 GS.Set_Of_Books_ID = P_Set_Of_Books_ID
1463 AND GS.Application_ID = 101
1464 AND GS.Closing_Status = 'O'
1465 AND GS.Start_Date = X_Cur_Yr_Last_Prd_Start_Dt
1466 AND GS.End_Date = X_Cur_Yr_Last_Prd_End_Dt
1467 AND GS.Period_Type = P_Accounted_Period_Type ;
1468 EXCEPTION
1469 WHEN NO_DATA_FOUND THEN
1470 IF P_DEBUG_MODE = 'Y' THEN
1471 log_message('Year_End_Rollover: ' || 'ERRORS encountered in API : Get_Period_Details' );
1472 log_message('Year_End_Rollover: ' || 'Last Period Closing Fiscal Year is NOT open');
1473 END IF;
1474
1475 X_Msg_Count := 9998;
1476 X_Msg_Data := SUBSTR(SQLERRM, 1, 240);
1477 X_Return_Status := FND_API.G_RET_STS_ERROR;
1478
1479 FND_MSG_PUB.add_Exc_msg( P_Pkg_Name => 'PA_Year_End_Rollover_PKG',
1480 P_Procedure_Name => 'Get_Period_Details');
1481 RETURN;
1482 END;
1483
1484 IF P_DEBUG_MODE = 'Y' THEN
1485 log_message('Year_End_Rollover: ' || 'Cur Yr Last Prd Name : '|| X_Cur_Yr_Last_Prd_Name );
1486 END IF;
1487 -----------------------------------------------------------------------------------+
1488 -- Fetch the First Period Name of a next year w.r.t. a given closing year
1489 -----------------------------------------------------------------------------------+
1490 BEGIN
1491 SELECT
1492 GS.Period_Name
1493 INTO
1494 X_Next_Yr_First_Prd_Name
1495 FROM
1496 GL_Period_Statuses GS
1497 WHERE
1498 GS.Set_Of_Books_ID = P_Set_Of_Books_ID
1499 AND GS.Application_ID = 101
1500 AND GS.Closing_Status IN ('O', 'F')
1501 AND GS.Start_Date = X_Next_Yr_First_Prd_Start_Dt
1502 AND GS.End_Date = X_Next_Yr_First_Prd_End_Dt
1503 AND GS.Period_Type = P_Accounted_Period_Type ;
1504 EXCEPTION
1505 WHEN NO_DATA_FOUND THEN
1506 IF P_DEBUG_MODE = 'Y' THEN
1507 log_message('Year_End_Rollover: ' || 'ERRORS encountered in API : Get_Period_Details' );
1508 log_message('Year_End_Rollover: ' || 'First Period of next year is NOT open');
1509 END IF;
1510
1511 X_Msg_Count := 9999;
1512 X_Msg_Data := SUBSTR(SQLERRM, 1, 240);
1513 X_Return_Status := FND_API.G_RET_STS_ERROR;
1514
1515 FND_MSG_PUB.add_Exc_msg( P_Pkg_Name => 'PA_Year_End_Rollover_PKG',
1516 P_Procedure_Name => 'Get_Period_Details');
1517 RETURN;
1518 END;
1519 IF P_DEBUG_MODE = 'Y' THEN
1520 log_message('Year_End_Rollover: ' || 'Next Yr First Prd Name : '|| X_Next_Yr_First_Prd_Name );
1521 log_message('Year_End_Rollover: ' || '------------------------------' );
1522 log_message('Year_End_Rollover: ' || 'End API : Get_Period_Details' );
1523 log_message('Year_End_Rollover: ' || '------------------------------' );
1524 END IF;
1525
1526 RETURN;
1527
1528 EXCEPTION
1529 WHEN OTHERS THEN
1530 IF P_DEBUG_MODE = 'Y' THEN
1531 log_message('Year_End_Rollover: ' || 'ERRORS encountered in API : Get_Period_Details' );
1532 END IF;
1533 X_Msg_Count := 1;
1534 X_Msg_Data := SUBSTR(SQLERRM, 1, 240);
1535 X_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
1536
1537 FND_MSG_PUB.add_Exc_msg( P_Pkg_Name => 'PA_Year_End_Rollover_PKG',
1538 P_Procedure_Name => 'Get_Period_Details');
1539 END Get_Period_Details;
1540
1541 -------------------------------------------------------------------------------------
1542 -- Update / Insert into PA_BUDGET_LINES
1543 -------------------------------------------------------------------------------------
1544 PROCEDURE Upd_Ins_Budget_Line (
1545 P_Budget_Version_ID IN PA_Budget_Versions.Budget_Version_ID%TYPE,
1546 P_Budget_Type_Code IN PA_Budget_Types.Budget_Type_Code%TYPE,
1547 P_Resource_Assignment_ID IN OUT NOCOPY PA_Resource_Assignments.Resource_Assignment_ID%TYPE,
1548 P_Period_Name IN GL_Periods.Period_Name%TYPE,
1549 P_Period_Start_Date IN GL_Periods.Start_Date%TYPE,
1550 P_Period_End_Date IN GL_Periods.End_Date%TYPE,
1551 P_Transfer_Amount IN NUMBER,
1552 P_Project_ID IN PA_Projects_all.Project_ID%TYPE,
1553 P_Task_ID IN PA_Tasks.Task_ID%TYPE,
1554 P_Resource_List_Member_ID IN PA_Resource_List_Members.Resource_List_Member_ID%TYPE,
1555 P_Raw_Cost_Flag IN PA_Budget_Entry_Methods.Raw_Cost_Flag%TYPE,
1556 P_Burdened_Cost_Flag IN PA_Budget_Entry_Methods.Burdened_Cost_Flag%TYPE,
1557 P_CCID IN GL_Code_Combinations.Code_Combination_ID%TYPE,
1558 P_Request_ID IN FND_Concurrent_Requests.Request_ID%TYPE,
1559 P_Period_New_Or_Closing IN VARCHAR2,
1560 P_New_CCID OUT NOCOPY GL_Code_Combinations.Code_Combination_ID%TYPE,
1561 X_Return_Status OUT NOCOPY VARCHAR2,
1562 X_Msg_Count OUT NOCOPY NUMBER,
1563 X_Msg_Data OUT NOCOPY VARCHAR2
1564 )
1565 IS
1566
1567 -- Local Variables
1568 l_RowID ROWID;
1569 l_Update_Count NUMBER;
1570 l_Raw_Cost NUMBER := 0;
1571 l_Burdened_Cost NUMBER := 0;
1572 l_Quantity NUMBER;
1573 l_revenue NUMBER;
1574 l_Burdened_Transfer_Amount NUMBER;
1575 l_Raw_Transfer_Amount NUMBER;
1576
1577 /* FPB2: MRC */
1578
1579 -- Bug Fix: 4569365. Removed MRC code.
1580 -- l_Mrc_Exception EXCEPTION;
1581 l_Txn_Curr_Code PA_BUDGET_LINES.TXN_CURRENCY_CODE%type;
1582 --l_Budget_Line_Id PA_BUDGET_LINES.BUDGET_LINE_ID%type;
1583
1584 BEGIN
1585 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
1586
1587 -----------------------------------------------------------------------------------+
1588 -- Update the Budget line for a required res. assignment and period
1589 -----------------------------------------------------------------------------------+
1590 IF P_DEBUG_MODE = 'Y' THEN
1591 g_procedure_name := 'Upd_Ins_Budget_Line';
1592 log_message('Year_End_Rollover: ' || 'R Flag : '|| P_Raw_Cost_Flag ||' and B Flag : '|| P_Burdened_Cost_Flag);
1593 END IF;
1594
1595 IF P_Burdened_Cost_Flag = 'Y' AND P_Raw_Cost_Flag = 'N'
1596 THEN
1597 l_Burdened_Transfer_Amount := P_Transfer_Amount;
1598 l_Raw_Transfer_Amount := 0;
1599 ELSIF P_Burdened_Cost_Flag = 'N' AND P_Raw_Cost_Flag = 'Y'
1600 THEN
1601 l_Burdened_Transfer_Amount := 0;
1602 l_Raw_Transfer_Amount := P_Transfer_Amount;
1603 ELSIF P_Burdened_Cost_Flag = 'Y' AND P_Raw_Cost_Flag = 'Y'
1604 THEN
1605 l_Burdened_Transfer_Amount := P_Transfer_Amount;
1606 l_Raw_Transfer_Amount := 0; -- P_Transfer_Amount;
1607 END IF;
1608
1609 /* FPB2: MRC related changes
1610 - Changes done under the assumption that this code is used only in the old model
1611 - Txn_currency_code will always be the projfunc_currency_code
1612 - Adding txn_currency_code in update for more clarity to indicate the update will
1613 always update just one record. We get the budget_line_id of the updated record
1614 and pass to mrc api */
1615
1616 BEGIN
1617 SELECT Projfunc_Currency_Code
1618 INTO l_Txn_Curr_Code
1619 FROM PA_Projects_All a, PA_Budget_Versions b, PA_Resource_Assignments c
1620 WHERE a.Project_Id = b.Project_Id
1621 AND b.Budget_Version_Id = c.Budget_Version_Id
1622 AND c.Resource_Assignment_Id = P_Resource_Assignment_Id;
1623 EXCEPTION
1624 WHEN OTHERS THEN
1625 /* May be the resource assignment id passed is not correct ! */
1626 l_Txn_Curr_Code := NULL;
1627 END;
1628
1629 UPDATE
1630 PA_Budget_Lines
1631 SET
1632 Burdened_Cost = NVL(Burdened_Cost,0) + l_Burdened_Transfer_Amount,
1633 Raw_Cost = NVL(Raw_Cost,0) + l_Raw_Transfer_Amount,
1634 Request_ID = P_Request_ID
1635 WHERE
1636 Resource_Assignment_ID = P_Resource_Assignment_ID
1637 AND Start_Date = P_Period_Start_Date
1638 AND Txn_Currency_Code = l_Txn_Curr_Code /* FPB2: MRC */
1639 AND Code_Combination_ID = P_CCID;
1640 --RETURNING Budget_Line_Id into l_Budget_Line_Id;
1641
1642 l_Update_Count := SQL%ROWCOUNT;
1643
1644 IF P_DEBUG_MODE = 'Y' THEN
1645 log_message('Year_End_Rollover: ' || 'New/Closing,Records updated:'
1646 ||P_Period_New_Or_Closing||';'||l_Update_Count);
1647 END IF;
1648
1649 If l_Update_Count = 0 and P_Period_New_Or_Closing = 'NEW' then
1650
1651 -- Update without using code combination_id ...
1652 -- Case: Where there is a line that is already existing with
1653 -- a diff. code combination ..
1654 UPDATE
1655 PA_Budget_Lines
1656 SET
1657 Burdened_Cost = NVL(Burdened_Cost,0) + l_Burdened_Transfer_Amount,
1658 Raw_Cost = NVL(Raw_Cost,0) + l_Raw_Transfer_Amount,
1659 Request_ID = P_Request_ID
1660 WHERE
1661 Resource_Assignment_ID = P_Resource_Assignment_ID
1662 AND Start_Date = P_Period_Start_Date
1663 AND Txn_Currency_Code = l_Txn_Curr_Code
1664 RETURNING Code_Combination_ID into P_New_CCID;
1665
1666 l_Update_Count := SQL%ROWCOUNT;
1667
1668 IF P_DEBUG_MODE = 'Y' THEN
1669 log_message('Year_End_Rollover: ' || '2nd Update - New/Closing,Records updated,new CCID:'
1670 ||P_Period_New_Or_Closing||';'||p_new_ccid||';'||l_Update_Count);
1671 END IF;
1672
1673 End If;
1674
1675 IF l_Update_Count = 0 -- No Data Found ie. NO record are updated
1676 THEN
1677 IF P_DEBUG_MODE = 'Y' THEN
1678 log_message('Year_End_Rollover: ' || 'Inserting into PA_BUDGET_LINES');
1679 END IF;
1680 ---------------------------------------------------------------------------------+
1681 -- Insert a new Budget Lines data
1682 ---------------------------------------------------------------------------------+
1683 IF P_DEBUG_MODE = 'Y' THEN
1684 log_message('Year_End_Rollover: ' || 'Raw Cost: '|| l_Raw_Cost ||' and Burdened Cost: '|| l_Burdened_Cost );
1685 END IF;
1686 PA_BUDGET_LINES_V_PKG.Insert_Row (
1687 X_ROWID => l_RowID,
1688 X_Resource_Assignment_Id => P_Resource_Assignment_ID,
1689 X_Budget_Version_Id => P_Budget_Version_ID,
1690 X_Project_Id => P_Project_ID,
1691 X_Task_Id => P_Task_ID,
1692 X_Resource_List_Member_Id => P_Resource_List_Member_Id,
1693 X_Description => NULL,
1694 X_Start_Date => P_Period_Start_Date,
1695 X_End_Date => P_Period_End_Date,
1696 X_Period_Name => P_Period_Name,
1697 X_Quantity => l_quantity,
1698 X_Unit_Of_Measure => NULL,
1699 X_Track_As_Labor_Flag => NULL,
1700 X_Raw_Cost => l_Raw_Transfer_Amount,
1701 X_Burdened_Cost => l_Burdened_Transfer_Amount,
1702 X_Revenue => l_revenue,
1703 X_Change_Reason_Code => NULL,
1704 X_Last_Update_Date => SYSDATE,
1705 X_Last_Updated_By => FND_GLOBAL.User_ID,
1706 X_Creation_Date => SYSDATE,
1707 X_Created_By => FND_GLOBAL.User_ID,
1708 X_Last_Update_Login => FND_GLOBAL.User_ID,
1709 X_Attribute_Category => NULL,
1710 X_Attribute1 => NULL,
1711 X_Attribute2 => NULL,
1712 X_Attribute3 => NULL,
1713 X_Attribute4 => NULL,
1714 X_Attribute5 => NULL,
1715 X_Attribute6 => NULL,
1716 X_Attribute7 => NULL,
1717 X_Attribute8 => NULL,
1718 X_Attribute9 => NULL,
1719 X_Attribute10 => NULL,
1720 X_Attribute11 => NULL,
1721 X_Attribute12 => NULL,
1722 X_Attribute13 => NULL,
1723 X_Attribute14 => NULL,
1724 X_Attribute15 => NULL,
1725 -- Bug Fix: 4569365. Removed MRC code.
1726 -- X_Mrc_Flag => 'Y', /* FPB2: MRC */
1727 X_Calling_Process => 'PR',
1728 X_Pm_Product_Code => NULL,
1729 X_Pm_Budget_Line_Reference => NULL,
1730 X_raw_Cost_source => 'M',
1731 X_Burdened_Cost_source => 'M',
1732 X_quantity_source => 'M',
1733 X_revenue_source => 'M',
1734 X_standard_Bill_rate => NULL,
1735 X_Average_Bill_rate => NULL,
1736 X_Average_Cost_rate => NULL,
1737 X_project_Assignment_Id => -1,
1738 X_plan_Error_Code => NULL,
1739 X_total_plan_revenue => NULL,
1740 X_total_plan_raw_Cost => NULL,
1741 X_total_plan_Burdened_Cost => NULL,
1742 X_total_plan_quantity => NULL,
1743 X_Average_Discount_percentage => NULL,
1744 X_Cost_rejection_Code => NULL,
1745 X_Burden_rejection_Code => NULL,
1746 X_revenue_rejection_Code => NULL,
1747 X_other_rejection_Code => NULL);
1748
1749 ---------------------------------------------------------------------------------+
1750 -- Update the CCID for the newly inserted Budget Lines record
1751 ---------------------------------------------------------------------------------+
1752
1753 /* FPB2: As of now amounts are not part of the below update and hence MRC need
1754 not be called. If amount fields are added to the below update, MRC apis need
1755 to be called to maintain MRC in budgets */
1756
1757 -- Updating Budget Version Amounts
1758 UPDATE
1759 PA_Budget_Versions
1760 SET
1761 Raw_Cost = NVL(Raw_Cost,0) - l_Raw_Transfer_Amount,
1762 Burdened_Cost = NVL(Burdened_Cost,0) - l_Burdened_Transfer_Amount
1763 WHERE
1764 Budget_Version_ID = P_Budget_Version_ID;
1765 END IF; -- End of inserting a new record into PA_BUDGET_LINES
1766
1767 RETURN;
1768 -- Bug Fix: 4569365. Removed MRC code.
1769 /* EXCEPTION
1770 WHEN l_Mrc_Exception THEN
1771 IF P_DEBUG_MODE = 'Y' THEN
1772 log_message('Year_End_Rollover: ' || 'In Upd_Ins_Budget_Line : '|| SQLERRM );
1773 END IF;
1774 Raise;
1775 */
1776 END Upd_Ins_Budget_Line;
1777
1778 -----------------------------------------------------------------------------------
1779 -- Create a Draft version of a Baselined Budget
1780 -----------------------------------------------------------------------------------
1781 PROCEDURE Create_Working_Budget (
1782 P_Project_ID IN PA_Projects_all.Project_ID%TYPE,
1783 P_Budget_Type_Code IN PA_Budget_Types.Budget_Type_Code%TYPE,
1784 P_Budget_Version_ID IN PA_Budget_Versions.Budget_Version_ID%TYPE,
1785 P_Request_ID IN FND_Concurrent_Requests.Request_ID%TYPE,
1786 X_Work_Budget_Version_ID OUT NOCOPY PA_Budget_Versions.Budget_Version_ID%TYPE,
1787 X_Return_Status OUT NOCOPY VARCHAR2,
1788 X_Msg_Count OUT NOCOPY NUMBER,
1789 X_Msg_Data OUT NOCOPY VARCHAR2
1790 )
1791 IS
1792
1793 -- Local Variables
1794 l_Work_Budget_Version_ID PA_Budget_Versions.Budget_Version_ID%TYPE;
1795 l_Lock_Name VARCHAR2(100);
1796
1797 l_Exist_Flag NUMBER;
1798
1799 l_Err_Code NUMBER;
1800 l_Err_Stage VARCHAR2(200);
1801 l_Err_Stack VARCHAR2(200);
1802
1803 -- Local Exception Variables
1804 l_Lock_Bdgt_Err EXCEPTION;
1805 l_Get_Work_Bdgt_Err EXCEPTION;
1806 l_Copy_Bdgt_Err EXCEPTION;
1807 l_IU_Bdgt_Acct_Err EXCEPTION;
1808 BEGIN
1809
1810 IF P_DEBUG_MODE = 'Y' THEN
1811 g_procedure_name := 'Create_Working_Budget';
1812 log_message('Create_Working_Budget: Start');
1813 END IF;
1814
1815 -- Generate a lock name
1816 l_lock_Name := 'YRENDRLVR:'||P_Project_ID||':'||P_Budget_Type_Code ;
1817
1818 -------------------------------------------------------------------------------+
1819 -- Check for the working version of the fetched baselined Budget Version
1820 -------------------------------------------------------------------------------+
1821 BEGIN
1822 Select
1823 1
1824 INTO
1825 l_Exist_Flag
1826 FROM
1827 PA_Budget_Versions
1828 WHERE
1829 Project_ID = P_Project_ID
1830 AND Budget_Type_Code = P_Budget_Type_Code
1831 AND Budget_Status_Code = 'W' ;
1832 IF P_DEBUG_MODE = 'Y' THEN
1833 log_message('Year_End_Rollover: ' || '--');
1834 log_message('Year_End_Rollover: ' || 'Working Version of the Budget is existing');
1835 log_message('Year_End_Rollover: ' || '--');
1836 END IF;
1837 EXCEPTION
1838 WHEN NO_DATA_FOUND THEN
1839 l_Work_Budget_Version_Id := P_Budget_Version_ID; /* added for bug 2699417 */
1840 RAISE l_Get_Work_Bdgt_ERR;
1841 END;
1842 -- End of checking the working version of Budget Version
1843
1844 /* Commenting for Bug 5726535
1845 -------------------------------------------------------------------------------+
1846 -- Create (acquire) a lock for the Budget version record
1847 -------------------------------------------------------------------------------+
1848 IF PA_Debug.Acquire_User_Lock(l_Lock_Name) <> 0
1849 THEN
1850 l_Work_Budget_Version_Id := P_Budget_Version_ID;
1851 RAISE l_Lock_Bdgt_ERR;
1852 END IF;
1853 IF P_DEBUG_MODE = 'Y' THEN
1854 log_message('Year_End_Rollover: ' || '--');
1855 log_message('Year_End_Rollover: ' || 'Acquired Lock : '|| l_lock_name );
1856 log_message('Year_End_Rollover: ' || '--');
1857 log_message('Year_End_Rollover: Calling PA_Budget_Core.Copy');
1858 END IF;
1859 -- End of acquiring the user lock
1860 */
1861
1862 /* Bug 5726535 - Start */
1863 -------------------------------------------------------------------------------+
1864 -- Check if Year End Rollover program is already running for this Project and
1865 -- Budget Type
1866 -------------------------------------------------------------------------------+
1867 IF Is_Yr_End_Rollover_Running(P_Project_ID, P_Budget_Type_Code) THEN
1868 l_Work_Budget_Version_Id := P_Budget_Version_ID; /* added for bug 2699417 */
1869 RAISE l_Lock_Bdgt_ERR;
1870 END IF;
1871 IF P_DEBUG_MODE = 'Y' THEN
1872 log_message('Year_End_Rollover: ' || '--');
1873 log_message('Year_End_Rollover: ' || 'No Year End Rollover program running for Project_ID: '|| P_Project_ID
1874 || ' and Budget_Type_Code: ' || P_Budget_Type_Code);
1875 log_message('Year_End_Rollover: ' || '--');
1876 log_message('Year_End_Rollover: Calling Update_Yr_End_Rollover_Flag'); /* Bug 5726535 */
1877 END IF;
1878 -- End of checking if Year End Rollover program is already running for this
1879 -- Project and Budget Type
1880
1881 -------------------------------------------------------------------------------+
1882 -- Update PA_Budgetary_Control_Options.Yr_End_Rollover_Flag to 'P' indicating
1883 -- to other processes that this program is running
1884 -------------------------------------------------------------------------------+
1885 Upd_Yr_End_Rollover_Flag_To_P(
1886 P_Request_ID => P_Request_ID,
1887 P_Project_ID => P_Project_ID,
1888 P_Budget_Type_Code => P_Budget_Type_Code);
1889 IF P_DEBUG_MODE = 'Y' THEN
1890 log_message('Year_End_Rollover: ' || '--');
1891 log_message('Year_End_Rollover: ' || 'Updated PA_Budgetary_Control_Options.Yr_End_Rollover_Flag to ''P''');
1892 log_message('Year_End_Rollover: ' || '--');
1893 log_message('Year_End_Rollover: Calling PA_Budget_Core.Copy');
1894 END IF;
1895 -- End of updating PA_Budgetary_Control_Options.Yr_End_Rollover_Flag to 'P'
1896 /* Bug 5726535 - End */
1897
1898 -------------------------------------------------------------------------------+
1899 -- Create (using PA_Budget_Core.Copy API) a new Draft Budget Version
1900 -- along with its Res. Assignment IDs and Budget Lines.
1901 -------------------------------------------------------------------------------+
1902 PA_Budget_Core.Copy (
1903 X_Src_Version_ID => P_Budget_Version_ID,
1904 X_Amount_Change_Pct => 1,
1905 X_Shift_days => 5,
1906 X_Rounding_Precision => null, -- Need to verify
1907 X_Dest_Project_ID => P_Project_ID,
1908 X_Dest_Budget_Type_Code => P_Budget_Type_Code,
1909 X_Err_Code => l_Err_Code,
1910 X_Err_Stage => l_Err_Stage,
1911 X_Err_Stack => l_Err_Stack
1912 );
1913
1914 IF l_Err_Code <> 0
1915 THEN
1916 log_message('Year_End_Rollover: PA_Budget_Core.Copy failed');
1917 log_message('Year_End_Rollover: X_Err_Stage['||l_Err_Stage||'] X_Err_Code ['||l_Err_Code||']');
1918 log_message('Year_End_Rollover: X_Err_Stack['||l_Err_Stack||']');
1919 l_Work_Budget_Version_Id := P_Budget_Version_ID; /* added for bug 2699417 */
1920 RAISE l_Copy_Bdgt_ERR;
1921 END IF;
1922 IF P_DEBUG_MODE = 'Y' THEN
1923 log_message('Year_End_Rollover: ' || '--');
1924 log_message('Year_End_Rollover: ' || 'Budget successfully copied using API PA_Budget_Core.Copy');
1925 log_message('Year_End_Rollover: ' || '--');
1926 END IF;
1927 -- End of copying the Budget
1928
1929 -------------------------------------------------------------------------------+
1930 -- Get the working Budget Version ID for the above copied Budget
1931 -------------------------------------------------------------------------------+
1932 BEGIN
1933 SELECT
1934 Budget_Version_ID
1935 INTO
1936 X_Work_Budget_Version_ID
1937 FROM
1938 PA_BUDGET_VERSIONS
1939 WHERE
1940 Project_ID = P_Project_ID
1941 AND Budget_Type_Code = P_Budget_Type_Code
1942 AND Budget_Status_Code = 'W';
1943 IF P_DEBUG_MODE = 'Y' THEN
1944 log_message('Year_End_Rollover: ' || '--');
1945 log_message('Year_End_Rollover: ' || 'Working Version of the copied Budget is found');
1946 log_message('Year_End_Rollover: ' || '--');
1947 END IF;
1948 l_Work_Budget_Version_ID := X_Work_Budget_Version_ID ;
1949 EXCEPTION
1950 WHEN NO_DATA_FOUND THEN
1951 l_Work_Budget_Version_Id := P_Budget_Version_ID; /* added for bug 2699417 */
1952 RAISE l_Get_Work_Bdgt_ERR;
1953 END;
1954 -- End of Checking the working version of Budget Version
1955
1956 -- Update all the CCIDs
1957 BEGIN
1958 FOR i IN ( SELECT RA.Resource_List_Member_ID,
1959 RA.Resource_Assignment_ID,
1960 RA.Project_ID,
1961 RA.Task_ID,
1962 BL.Code_Combination_ID,
1963 BL.Start_Date
1964 FROM PA_Resource_Assignments RA,
1965 PA_Budget_Lines BL
1966 WHERE RA.Resource_Assignment_ID = BL.Resource_Assignment_ID
1967 AND RA.Budget_Version_ID = P_Budget_Version_ID )
1968 LOOP
1969
1970 /* FPB2: As of now amounts are not part of the below update and hence MRC need
1971 not be called. If amount fields are added to the below update, MRC apis need
1972 to be called to maintain MRC in budgets */
1973
1974 UPDATE
1975 PA_Budget_Lines BL
1976 SET
1977 BL.Code_Combination_ID = i.Code_Combination_ID
1978 WHERE
1979 BL.Start_Date = i.Start_Date
1980 AND BL.Resource_Assignment_ID = (
1981 SELECT RA.Resource_Assignment_ID
1982 FROM PA_Resource_Assignments RA
1983 WHERE RA.Budget_Version_ID = l_Work_Budget_Version_ID
1984 AND RA.Resource_List_Member_ID = i.Resource_List_Member_ID
1985 AND RA.Project_ID = i.Project_ID
1986 AND RA.Task_ID = i.Task_ID );
1987
1988 END LOOP;
1989 END;
1990 -------------------------------------------------------------------------------
1991 -- Delete if account lines exist ..
1992 -------------------------------------------------------------------------------
1993 Delete from PA_Budget_Acct_Lines where Budget_version_ID = l_Work_Budget_Version_ID;
1994
1995 -------------------------------------------------------------------------------
1996 -- Insert new Account Lines
1997 -------------------------------------------------------------------------------
1998 IF P_DEBUG_MODE = 'Y' THEN
1999 log_message('Year_End_Rollover: ' || '--');
2000 log_message('Year_End_Rollover: ' || 'Inserting new records into PA_Budget_Acct_Lines for newly created Draft Budget Version = '|| l_Work_Budget_Version_ID );
2001 log_message('Year_End_Rollover: ' || '--');
2002 END IF;
2003
2004 BEGIN
2005 INSERT INTO PA_Budget_Acct_Lines (
2006 Budget_Acct_Line_ID,
2007 Budget_version_ID,
2008 GL_Period_Name,
2009 Start_Date,
2010 End_Date,
2011 Code_Combination_ID,
2012 Prev_Ver_Budget_Amount,
2013 Prev_Ver_Available_Amount,
2014 Curr_Ver_Budget_Amount,
2015 Curr_Ver_Available_Amount,
2016 Accounted_Amount,
2017 Last_Update_Date,
2018 Last_Updated_By,
2019 Creation_Date,
2020 Created_By,
2021 Last_Update_Login,
2022 Request_ID
2023 )
2024 SELECT
2025 PA_Budget_Acct_Lines_S.NEXTVAL,
2026 l_Work_Budget_Version_ID, -- Should be working Budget Version
2027 GL_Period_Name,
2028 Start_Date,
2029 End_Date,
2030 Code_Combination_ID,
2031 Curr_Ver_Budget_Amount,
2032 Curr_Ver_Available_Amount,
2033 Curr_Ver_Budget_Amount,
2034 Curr_Ver_Available_Amount,
2035 0,
2036 sysdate,
2037 1234,
2038 sysdate,
2039 1234,
2040 1234,
2041 P_Request_ID
2042 FROM
2043 PA_Budget_Acct_Lines
2044 WHERE
2045 Budget_Version_ID = P_Budget_Version_ID ;
2046
2047 IF SQL%ROWCOUNT = 0
2048 THEN
2049 RAISE l_IU_Bdgt_Acct_ERR;
2050 END IF;
2051
2052 END; -- End of inserting New Budget Account Lines
2053
2054 RETURN;
2055
2056 EXCEPTION
2057 WHEN l_Lock_Bdgt_Err THEN
2058 X_Msg_Count := 1;
2059 X_Msg_Data := SUBSTR(SQLERRM, 1, 240);
2060 X_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
2061 Year_End_Rollover_Log (
2062 P_Budget_Version_ID => l_Work_Budget_Version_Id,
2063 P_Message_Name => 'PA_BC_LOCK_BDGT_ERR',
2064 P_Request_ID => P_Request_ID,
2065 P_Lock_Name => l_Lock_Name );
2066
2067 WHEN l_Get_Work_Bdgt_Err THEN
2068 X_Msg_Count := 1;
2069 X_Msg_Data := SUBSTR(SQLERRM, 1, 240);
2070 X_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
2071 Year_End_Rollover_Log (
2072 P_Budget_Version_ID => l_Work_Budget_Version_Id,
2073 P_Message_Name => 'PA_BC_WORK_BDGT_ERR',
2074 P_Request_ID => P_Request_ID,
2075 P_Lock_Name => l_Lock_Name );
2076 WHEN l_Copy_Bdgt_Err THEN
2077 X_Msg_Count := 1;
2078 X_Msg_Data := SUBSTR(SQLERRM, 1, 240);
2079 X_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
2080 Year_End_Rollover_Log (
2081 P_Budget_Version_ID => l_Work_Budget_Version_Id,
2082 P_Message_Name => 'PA_BC_COPY_BDGT_ERR',
2083 P_Request_ID => P_Request_ID,
2084 P_Lock_Name => l_Lock_Name );
2085 WHEN l_IU_Bdgt_Acct_Err THEN
2086 X_Msg_Count := 1;
2087 X_Msg_Data := SUBSTR(SQLERRM, 1, 240);
2088 X_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
2089 Year_End_Rollover_Log (
2090 P_Budget_Version_ID => l_Work_Budget_Version_Id,
2091 P_Message_Name => upper('PA_BC_IU_Bdgt_Acct_Err'),
2092 P_Request_ID => P_Request_ID,
2093 P_Lock_Name => l_Lock_Name );
2094 END Create_Working_Budget;
2095
2096 -- -------------------------------------------------------------------+
2097 -- This procedure will create budget data in pa_bc_balances (from
2098 -- pa_budget_lines, pa_resource_assignments)
2099 -- -------------------------------------------------------------------+
2100
2101 PROCEDURE Create_bc_balances(p_budget_version_id IN NUMBER,
2102 p_last_baselined_version_id IN NUMBER,
2103 p_Set_of_books_id IN NUMBER,
2104 p_return_status OUT NOCOPY VARCHAR2)
2105 IS
2106 l_date DATE;
2107 l_user NUMBER;
2108 l_request_id NUMBER;
2109 BEGIN
2110 p_return_status := 'S';
2111 l_date := SYSDATE;
2112 l_user := FND_GLOBAL.LOGIN_ID;
2113 l_request_id := FND_GLOBAL.conc_request_id;
2114
2115 IF P_DEBUG_MODE = 'Y' THEN
2116 log_message('Create_bc_balances: Create pa_bc_balances - BGT');
2117 END IF;
2118
2119 -- Budget lines from pa_budget_lines
2120 insert into pa_bc_balances(
2121 PROJECT_ID,
2122 TASK_ID,
2123 TOP_TASK_ID,
2124 RESOURCE_LIST_MEMBER_ID,
2125 BALANCE_TYPE,
2126 SET_OF_BOOKS_ID,
2127 BUDGET_VERSION_ID,
2128 LAST_UPDATE_DATE,
2129 LAST_UPDATED_BY,
2130 CREATED_BY,
2131 CREATION_DATE,
2132 LAST_UPDATE_LOGIN,
2133 PERIOD_NAME,
2134 START_DATE,
2135 END_DATE,
2136 PARENT_MEMBER_ID,
2137 BUDGET_PERIOD_TO_DATE,
2138 ACTUAL_PERIOD_TO_DATE,
2139 ENCUMB_PERIOD_TO_DATE,
2140 REQUEST_ID)
2141 select pa.project_id,
2142 pa.task_id,
2143 pt.top_task_id,
2144 pa.resource_list_member_id,
2145 'BGT',
2146 p_set_of_books_id,
2147 pbv.budget_version_id,
2148 l_date,
2149 l_user,
2150 l_user,
2151 l_date,
2152 l_user,
2153 pb.PERIOD_NAME,
2154 pb.START_DATE,
2155 pb.END_DATE,
2156 rm.PARENT_MEMBER_ID,
2157 pb.burdened_cost,
2158 0,
2159 0,
2160 l_request_id
2161 from pa_budget_lines pb,
2162 pa_resource_assignments pa,
2163 pa_tasks pt,
2164 pa_resource_list_members rm,
2165 pa_budget_versions pbv
2166 where pbv.budget_version_id = p_budget_version_id
2167 and pa.resource_assignment_id = pb.resource_assignment_id
2168 and pa.task_id = pt.task_id (+)
2169 and pa.budget_version_id = pbv.budget_version_id
2170 and rm.resource_list_member_id = pa.resource_list_member_id;
2171
2172 IF P_DEBUG_MODE = 'Y' THEN
2173 log_message('Create_bc_balances: Create pa_bc_balances - Transaction data');
2174 END IF;
2175
2176 -- Transaction data from the last baselined version ..
2177 INSERT INTO
2178 PA_BC_Balances (
2179 Project_ID,
2180 Task_ID,
2181 Resource_List_Member_ID,
2182 Set_Of_Books_ID,
2183 Budget_Version_ID,
2184 Balance_Type,
2185 Start_Date,
2186 End_Date,
2187 Created_By,
2188 Creation_date,
2189 Last_Updated_By,
2190 Last_Update_date,
2191 Last_Update_Login,
2192 Top_Task_ID,
2193 Parent_Member_ID,
2194 Request_ID,
2195 Program_ID,
2196 Program_Application_ID,
2197 Program_Update_Date,
2198 Period_Name,
2199 Actual_Period_To_Date,
2200 Budget_period_To_Date,
2201 Encumb_Period_To_Date
2202 )
2203 SELECT
2204 Project_ID,
2205 Task_ID,
2206 Resource_List_Member_ID,
2207 Set_Of_Books_ID,
2208 p_budget_version_id,
2209 Balance_Type,
2210 Start_Date,
2211 End_Date,
2212 l_user,
2213 l_date, -- Creation_Date
2214 l_user,
2215 l_date, -- Last_Update_Date
2216 l_user,
2217 Top_Task_ID,
2218 Parent_Member_ID,
2219 Request_ID,
2220 Program_ID,
2221 Program_Application_ID,
2222 Program_Update_Date,
2223 Period_Name,
2224 Actual_Period_To_Date,
2225 Budget_period_To_Date,
2226 Encumb_Period_To_Date
2227 FROM
2228 PA_BC_BALANCES
2229 WHERE
2230 Budget_Version_ID = p_last_baselined_version_id
2231 AND Balance_Type <> 'BGT' ;
2232
2233 EXCEPTION
2234 WHEN OTHERS THEN
2235 p_return_status := 'E';
2236 END Create_bc_balances;
2237
2238
2239 -------------------------------------------------------------------------------------
2240 -- Update the message name for a Budget Version that was failed due to some reason
2241 -------------------------------------------------------------------------------------
2242 PROCEDURE Year_End_Rollover_Log (
2243 P_Budget_Version_ID IN PA_Budget_Versions.Budget_Version_ID%TYPE,
2244 P_Message_Name IN FND_New_Messages.Message_Name%TYPE,
2245 P_Request_ID IN FND_Concurrent_Requests.Request_ID%TYPE,
2246 P_Lock_Name IN VARCHAR2
2247 )
2248 IS
2249
2250 -- Local Values
2251 l_Lock_Result NUMBER;
2252 l_Project_ID PA_Projects_all.Project_ID%TYPE;
2253 l_Budget_Type_Code PA_Budget_Types.Budget_Type_Code%TYPE;
2254
2255 BEGIN
2256
2257 /* Commenting for Bug 5726535
2258 -- Release the lock
2259 IF P_DEBUG_MODE = 'Y' THEN
2260 log_message('In Year_End_Rollover_Log API. Releasing Lock Name = '|| P_Lock_Name );
2261 END IF;
2262 l_Lock_Result := PA_Debug.Release_User_Lock(P_Lock_Name);
2263 IF P_DEBUG_MODE = 'Y' THEN
2264 log_message('Year_End_Rollover: ' || 'Lock Result : '|| l_Lock_Result );
2265 END IF;
2266 */
2267 /* Bug 5726535 - Start */
2268 IF P_DEBUG_MODE = 'Y' THEN
2269 log_message('In Year_End_Rollover_Log API.');
2270 END IF;
2271 /* Bug 5726535 - End */
2272 BEGIN
2273 SELECT distinct
2274 Project_ID,
2275 Budget_Type_Code
2276 INTO
2277 l_Project_ID,
2278 l_Budget_Type_Code
2279 FROM
2280 PA_Budget_Versions
2281 WHERE
2282 Budget_Version_ID = P_Budget_Version_ID;
2283 EXCEPTION
2284 WHEN NO_DATA_FOUND THEN
2285 IF P_DEBUG_MODE = 'Y' THEN
2286 log_message('Year_End_Rollover: ' || 'No Data Found in PA_Budget_Versions');
2287 END IF;
2288 END;
2289
2290 -- Update the Budget Version record
2291 IF P_DEBUG_MODE = 'Y' THEN
2292 log_message('Year_End_Rollover: ' || 'Updating with Error in PA_Budgetary_Control_Options');
2293 log_message ('l_Project_ID = '|| l_Project_ID ); /* 2699417 */
2294 log_message ('l_Budget_Type_Code = '|| l_Budget_Type_Code ); /* 2699417 */
2295 log_message ('P_Message_Name = '|| P_Message_Name ); /* 2699417 */
2296 END IF;
2297 UPDATE
2298 PA_Budgetary_Control_Options
2299 SET
2300 Yr_End_Rollover_Message = P_Message_Name,
2301 Yr_End_Rollover_Flag = 'E',
2302 Yr_End_Rollover_Year = -1,
2303 Request_ID = P_Request_ID
2304 WHERE
2305 Project_ID = l_Project_ID
2306 AND Budget_Type_Code = l_Budget_Type_Code;
2307
2308 END Year_End_Rollover_Log;
2309
2310 -- Procedure used to call pa_debug.write for FND logging
2311 PROCEDURE LOG_MESSAGE(p_message in VARCHAR2)
2312 IS
2313 BEGIN
2314 -- IF g_debug_mode = 'Y' then
2315
2316 IF p_message is NOT NULL then
2317 pa_debug.g_err_stage := 'Debug Msg :'||substr(p_message,1,250);
2318
2319 -- Following to write to request log
2320 pa_debug.write_file(pa_debug.g_err_stage);
2321 -- for fnd log
2322 PA_DEBUG.write
2323 (x_Module => 'pa.plsql.PA_YEAR_END_ROLLOVER_PKG.'||g_procedure_name
2324 ,x_Msg => substr(p_message,1,240)
2325 ,x_Log_Level => 3);
2326 END IF;
2327 --END IF;
2328
2329 END LOG_MESSAGE;
2330
2331 /* Bug 5726535 - Start */
2332 /* This function checks if PA_Budgetary_Control_Options.Yr_End_Rollover_Flag
2333 is already set to 'P' for the given Project and Budget Type combination.
2334 If yes, the function returns TRUE. Otherwise, the function returns FALSE */
2335 FUNCTION Is_Yr_End_Rollover_Running(
2336 P_Project_ID IN PA_Projects_all.Project_ID%TYPE,
2337 P_Budget_Type_Code IN PA_Budget_Types.Budget_Type_Code%TYPE
2338 ) RETURN BOOLEAN
2339 IS
2340 l_Yr_End_Rollover_Flag PA_Budgetary_Control_Options.Yr_End_Rollover_Flag%TYPE := NULL;
2341 BEGIN
2342 SELECT Yr_End_Rollover_Flag
2343 INTO l_Yr_End_Rollover_Flag
2344 FROM PA_Budgetary_Control_Options
2345 WHERE Project_ID = p_Project_ID
2346 AND Budget_Type_Code = p_Budget_Type_Code;
2347
2348 IF l_Yr_End_Rollover_Flag = 'P' THEN
2349 RETURN TRUE;
2350 ELSE
2351 RETURN FALSE;
2352 END IF;
2353 END Is_Yr_End_Rollover_Running;
2354
2355 /* This procedure updates PA_Budgetary_Control_Options.Yr_End_Rollover_Flag to 'P'
2356 in an autonomous transaction */
2357 PROCEDURE Upd_Yr_End_Rollover_Flag_To_P(
2358 P_Request_ID IN FND_Concurrent_Requests.Request_ID%TYPE,
2359 P_Project_ID IN PA_Projects_all.Project_ID%TYPE,
2360 P_Budget_Type_Code IN PA_Budget_Types.Budget_Type_Code%TYPE
2361 )
2362 IS
2363 PRAGMA AUTONOMOUS_TRANSACTION;
2364 BEGIN
2365 UPDATE PA_Budgetary_Control_Options
2366 SET Yr_End_Rollover_Flag = 'P',
2367 Request_ID = P_Request_ID
2368 WHERE Project_ID = P_Project_ID
2369 AND Budget_Type_Code = P_Budget_Type_Code;
2370 COMMIT;
2371 END Upd_Yr_End_Rollover_Flag_To_P;
2372
2373 /* This procedure updates PA_Budgetary_Control_Options.Yr_End_Rollover_Flag to 'E'
2374 in an autonomous transaction */
2375 PROCEDURE Upd_Yr_End_Rollover_Flag_To_E(
2376 P_Request_ID IN FND_Concurrent_Requests.Request_ID%TYPE
2377 )
2378 IS
2379 PRAGMA AUTONOMOUS_TRANSACTION;
2380 BEGIN
2381 UPDATE PA_Budgetary_Control_Options
2382 SET Yr_End_Rollover_Flag = 'E'
2383 WHERE Request_ID = P_Request_ID;
2384 COMMIT;
2385 END Upd_Yr_End_Rollover_Flag_To_E;
2386 /* Bug 5726535 - End */
2387
2388 END PA_Year_End_Rollover_PKG ; /* End of package PA_Year_End_Rollover_PKG */