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