[Home] [Help]
PACKAGE BODY: APPS.PA_BUDGET_FUND_PKG
Source
1 PACKAGE BODY PA_BUDGET_FUND_PKG as
2 -- $Header: PABBFNDB.pls 120.33 2007/12/04 07:41:53 lamalviy ship $
3
4 --
5 -- Procedure : pa_budget_funds
6 -- Purpose : procedure called from budgets forms.
7 -- This process will verify if funds check is required
8 -- or not. Accordingly the procedure will call gl and/or
9 -- cbc funds check procedure for each account summary line
10 -- for input budget version id.
11
12 --Parameters :
13 -- p_calling_code : CHECK_FUNDS/CHECK_BASELINE/RESERVE_BASELINE
14 -- x_dual_bdgt_cntrl_flag : Y --> Yes, N --> No
15
16 CURSOR c_close_period_check( c_set_of_books_id NUMBER, c_budget_version_id NUMBER ) IS
17 SELECT 'X'
18 FROM dual
19 WHERE EXISTS
20 (SELECT 'X'
21 FROM PA_BUDGET_ACCT_LINES PBA,
22 GL_PERIOD_STATUSES GLS
23 WHERE GLS.application_id = PA_Period_Process_Pkg.Application_ID -- 101
24 AND GLS.set_of_books_id = c_set_of_books_id
25 AND GLS.period_name = PBA.gl_period_name
26 AND GLS.closing_status not in ('O' , 'F' )
27 AND PBA.curr_ver_available_amount <> PBA.prev_ver_available_amount
28 AND PBA.budget_version_id = c_budget_version_id) ;
29
30
31 -- For R12 Start ------------------------------------------------+
32 g_packet_id pa_bc_packets.packet_id%TYPE;
33 g_msg_data VARCHAR2(1000);
34 g_procedure_name VARCHAR2(30);
35 g_debug_mode VARCHAR2(10);
36 g_draft_bvid pa_budget_versions.budget_version_id%type;
37 g_project_id pa_projects_all.project_id%type;
38 g_org_id pa_implementations_all.org_id%type; --Bug 6524116
39
40 -- This is an autonomous procedure ...
41 PROCEDURE Update_bc_packets_fail(p_bud_ver_id IN NUMBER,
42 p_status_code IN VARCHAR2);
43 -- p_status: 'R' (Reject) or 'T' (Fatal)
44
45 -- This is an non-autonomous procedure ...
46 PROCEDURE Update_bc_packets_pass(p_bud_ver_id IN NUMBER);
47 -- p_status: 'A' (Pass)
48
49 PROCEDURE ADD_MESSAGE(p_message IN VARCHAR2);
50
51 PROCEDURE LOG_MESSAGE(p_message IN VARCHAR2);
52
53 PROCEDURE DELETE_DRAFT_BC_PACKETS(p_draft_bud_ver_id IN NUMBER);
54
55 -- This procedure is called to determine if any CDLs
56 -- that could be burdened have not been burdened
57 -- This can lead to burden cost being dropped off during rebaseline
58
59 -- FUNCTION Unburdened_cdl_exists(X_project_id IN Number)
60 -- RETURN BOOLEAN;
61 -- Defined at Spec level .. accessed from Budget (PAXBUEBU) form ..
62
63 -- This procedure will update the failure code on the draft version
64 -- account summary (update only if the lines still have a passed status)
65 -- We will pass the status of PSA API ...
66
67 Procedure Fail_draft_acct_summary(p_draft_version_id IN Number,
68 p_failure_status IN Varchar2);
69
70 -- For R12 End ------------------------------------------------+
71
72 PROCEDURE cc_funds_chk_rsrv ( p_enc_type_id IN NUMBER,
73 p_budget_version_id IN NUMBER,
74 p_calling_mode IN VARCHAR2,
75 p_balance_type IN VARCHAR2,
76 x_funds_chk_rsrv_status OUT NOCOPY VARCHAR2,
77 x_return_status OUT NOCOPY VARCHAR2,
78 x_msg_count OUT NOCOPY NUMBER,
79 x_msg_data OUT NOCOPY VARCHAR2 )
80 IS
81
82
83 l_cc_funds_resv_fail EXCEPTION;
84
85 l_funds_chk_rsrv_status VARCHAR2(1);
86
87
88 l_header_id number; --IGC_CC_INTERFACE.cc_header_id%TYPE ;
89 l_set_of_books_id pa_implementations_all.set_of_books_id%type;
90 l_category_name gl_je_categories.user_je_category_name%type ;
91 l_source_name gl_je_sources.user_je_source_name%type;
92 l_batch_line_num number; --IGC_CC_INTERFACE.batch_line_num%TYPE ;
93 l_code_combination_id gl_code_combinations.code_combination_id%TYPE ;
94 l_actual_flag gl_bc_packets.actual_flag%TYPE ;
95 l_currency_code gl_sets_of_books.currency_code%TYPE ;
96 l_last_update_date DATE;
97 l_last_updated_by NUMBER(15);
98 l_creation_date DATE;
99 l_created_by NUMBER(15);
100 l_last_update_login NUMBER(15);
101 l_call_mode VARCHAR2(1) ;
102 l_ret_code boolean ;
103 --l_result_code IGC_CC_INTERFACE.cbc_result_code%TYPE;
104 l_result_code pa_bc_packets.result_code%TYPE;
105 l_return_status VARCHAR2(100) ;
106 l_msg_index_out NUMBER ;
107 l_chk_res_unres_multi NUMBER ;
108
109 l_period_set_name VARCHAR2(15);
110 l_project_num pa_projects_all.segment1%type; --Bug 6524116
111
112 --PRAGMA AUTONOMOUS_TRANSACTION;--Bug 6524116; Removed as this is causing issue with IGC funds checking.
113
114 BEGIN
115
116 log_message('Entering cc_funds_chk_rsrv API .........') ;
117 x_return_status := FND_API.G_RET_STS_SUCCESS;
118
119 SELECT imp.set_of_books_id, sob.currency_code, SOB.PERIOD_SET_NAME
120 INTO l_set_of_books_id, l_currency_code, l_period_set_name
121 FROM PA_IMPLEMENTATIONS_all IMP, GL_SETS_OF_BOOKS SOB
122 where IMP.set_of_books_id = SOB.set_of_books_id
123 AND imp.org_id = nvl(g_org_id, imp.org_id);
124
125 select user_je_category_name
126 into l_category_name
127 from gl_je_categories
128 where je_category_name = 'Budget' ;
129
130 select user_je_source_name
131 into l_source_name
132 from gl_je_sources
133 where je_source_name = 'Project Accounting' ;
134
135 -----------------------------------------------------
136 -- deriving calling mode
137 -- l_chk_res_unres_multi is used to switch amounts
138 -- if the procedure is called i UNRESERVE mode
139 -----------------------------------------------------
140 if ( p_calling_mode = 'CHECK' ) then
141 l_call_mode := 'C';
142 l_chk_res_unres_multi := 1;
143 elsif ( p_calling_mode = 'RESERVE') then
144 l_call_mode := 'R';
145 l_chk_res_unres_multi := 1;
146 elsif ( p_calling_mode = 'UNRESERVE') then
147 l_call_mode := 'R';
148 l_chk_res_unres_multi := -1;
149 -----------------------------------------------------------------
150 -- Following code added for Buza Year End Budget Rollover Process
151 -----------------------------------------------------------------
152 elsif ( p_calling_mode = 'YEAR_END_ROLLOVER') then
153 l_call_mode := 'F';
154 l_chk_res_unres_multi := 1;
155 elsif ( p_calling_mode = 'UNRESERVE_YEAR_END_ROLLOVER') then
156 l_call_mode := 'F';
157 l_chk_res_unres_multi := -1;
158 -----------------------------------------------------------------
159 end if;
160
161 begin
162 delete from IGC_CC_INTERFACE
163 where cc_header_id = p_budget_version_id
164 and document_type = 'PA';
165 exception
166 when no_data_found then
167 null;
168 end;
169
170 log_message('l_call_mode, l_chk_res_unres_multi .........'||l_call_mode||','||l_chk_res_unres_multi) ;
171
172 log_message('l_source_name ... '||l_source_name);
173 log_message('l_category_name ... '||l_source_name);
174 log_message('l_set_of_books_id ...'||l_set_of_books_id);
175 log_message('p_enc_type_id .....'||p_enc_type_id);
176 log_message('l_currency_code ...'||l_currency_code);
177 log_message('l_period_set_name ...'||l_period_set_name);
178 log_message('p_budget_version_id .. '||p_budget_version_id);
179
180 -- added for bug 1824164
181 log_message('updated pa_budget_acct_lines table with accounted_amount ');
182
183 /* Commented for bug 3039985
184 UPDATE PA_BUDGET_ACCT_LINES PBA
185 SET PBA.accounted_amount = nvl(PBA.curr_ver_available_amount,0) - nvl(PBA.prev_ver_available_amount,0)
186 WHERE PBA.budget_version_id = p_budget_version_id; */
187
188 /* Added for bug 3039985 */
189 UPDATE PA_BUDGET_ACCT_LINES PBA
190 SET PBA.accounted_amount = nvl(PBA.curr_ver_budget_amount,0) - nvl(PBA.prev_ver_budget_amount,0)
191 WHERE PBA.budget_version_id = p_budget_version_id;
192
193 log_message('rows updated .........'||to_char(sql%rowcount)) ;
194
195 --Bug 6524116
196 select segment1
197 into l_project_num
198 from pa_projects_all p,
199 pa_budget_versions bv
200 where p.project_id = bv.project_id
201 and bv.budget_version_id = p_budget_version_id;
202
203 log_message('inserting into table IGC_CC_INTERFACE ..... ');
204
205 Insert INTO IGC_CC_INTERFACE (
206 CC_HEADER_ID ,
207 DOCUMENT_TYPE ,
208 CODE_COMBINATION_ID ,
209 PERIOD_SET_NAME ,
210 PERIOD_NAME ,
211 BATCH_LINE_NUM ,
212 CC_TRANSACTION_DATE ,
213 CC_FUNC_CR_AMT , --Bug 6633262
214 CC_FUNC_DR_AMT ,
215 JE_SOURCE_NAME ,
216 JE_CATEGORY_NAME ,
217 ACTUAL_FLAG ,
218 BUDGET_DEST_FLAG ,
219 SET_OF_BOOKS_ID ,
220 ENCUMBRANCE_TYPE_ID ,
221 CURRENCY_CODE ,
222 REFERENCE_1 ,
223 REFERENCE_2 ,
224 REFERENCE_3 ,
225 REFERENCE_4 ,
226 REFERENCE_5 ,
227 creation_date ,
228 created_by ,
229 LAST_UPDATE_DATE ,
230 LAST_UPDATED_BY ,
231 CC_ACCT_LINE_ID
232 )
233 SELECT
234 p_budget_version_id,
235 'PA',
236 PBA.code_combination_id,
237 l_period_set_name,
238 PBA.gl_period_name,
239 to_number(rownum),
240 PBA.start_date ,
241 --Bug 6524116 Changed 0 to NULL
242 decode(GL.account_type, -- CC_FUNC_CR_AMT column
243 'A',decode(sign(PBA.accounted_amount * l_chk_res_unres_multi ),
244 1, (PBA.accounted_amount * l_chk_res_unres_multi ),
245 -1, NULL),
246 'E',decode(sign(PBA.accounted_amount * l_chk_res_unres_multi ),
247 1, (PBA.accounted_amount * l_chk_res_unres_multi ),
248 -1, NULL),
249 'L',decode(sign(PBA.accounted_amount * l_chk_res_unres_multi ),
250 1, NULL ,
251 -1,abs(PBA.accounted_amount * l_chk_res_unres_multi )),
252 'R',decode(sign(PBA.accounted_amount * l_chk_res_unres_multi ),
253 1, NULL ,
254 -1,abs(PBA.accounted_amount * l_chk_res_unres_multi )),
255 'O',decode(sign(PBA.accounted_amount * l_chk_res_unres_multi ),
256 1, NULL ,
257 -1,abs(PBA.accounted_amount * l_chk_res_unres_multi )),
258 NULL ),
259 decode(GL.account_type, -- CC_FUNC_DR_AMT column
260 'A',decode(sign(PBA.accounted_amount * l_chk_res_unres_multi ),
261 -1,abs(PBA.accounted_amount * l_chk_res_unres_multi ),
262 1, NULL),
263 'E',decode(sign(PBA.accounted_amount * l_chk_res_unres_multi ),
264 -1,abs(PBA.accounted_amount * l_chk_res_unres_multi ),
265 1, NULL),
266 'L',decode(sign(PBA.accounted_amount * l_chk_res_unres_multi ),
267 -1, NULL ,
268 1,PBA.accounted_amount * l_chk_res_unres_multi ),
269 'R',decode(sign(PBA.accounted_amount * l_chk_res_unres_multi ),
270 -1, NULL ,
271 1,PBA.accounted_amount * l_chk_res_unres_multi ),
272 'O',decode(sign(PBA.accounted_amount * l_chk_res_unres_multi ),
273 -1, NULL ,
274 1,PBA.accounted_amount * l_chk_res_unres_multi ),
275
276 NULL ),
277 -- l_source_name,
278 -- l_category_name,
279 'Project Accounting',
280 'Budget',
281 'E' ,
282 'C' ,
283 l_set_of_books_id,
284 p_enc_type_id ,
285 l_currency_code ,
286 'PA',
287 p_budget_version_id,
288 PBA.budget_acct_line_id,
289 l_project_num, --Bug 6524116
290 null,
291 sysdate,
292 fnd_global.user_id,
293 sysdate,
294 fnd_global.user_id,
295 pba.budget_acct_line_id
296 FROM
297 PA_BUDGET_ACCT_LINES PBA,
298 GL_PERIOD_STATUSES GP,
299 GL_CODE_COMBINATIONS GL
300 WHERE PBA.accounted_amount <> 0
301 AND PBA.budget_version_id = p_budget_version_id
302 AND GL.code_combination_id = PBA.code_combination_id
303 AND GP.set_of_books_id = l_set_of_books_id
304 AND GP.period_name = PBA.gl_period_name
305 AND GP.application_id = PA_Period_Process_Pkg.Application_ID; -- 101
306
307 /* AND GP.closing_status in ('O','F') ; Commented out period status check. for Bug 2950493 */
308
309 log_message('rows inserted into IGC Table .. '||sql%rowcount);
310 -- Call the CC Funds Check FUnction by passing the packet_id
311
312 -- IF (p_calling_mode = 'CHECK_FUNDS' ) THEN
313
314 -- R12 Funds management Uptake: Commenting out the call to procedure which is obsolete in R12
315 -- Currently CBC is not supported in R12 , hence this will never get fired.
316 l_ret_code:=
317 IGC_CBC_FUNDS_CHECKER.igcfck(p_sobid => l_set_of_books_id ,
318 p_header_id => p_budget_version_id,
319 p_mode => l_call_mode,
320 p_actual_flag => 'E' ,
321 p_doc_type => 'PA' ,
322 p_ret_status => l_return_status,
323 p_batch_result_code => l_result_code );
324
325
326 log_message(' l_return_status '||l_return_status);
327 log_message(' l_result_code '||l_result_code);
328 log_message(' get mesage '||fnd_msg_pub.get(1));
329
330
331 -- Update the PA_BUDGET_ACCT_LINES table from the CC Funds Check Results
332 UPDATE PA_BUDGET_ACCT_LINES PBA
333 SET (PBA.funds_check_status_code,
334 PBA.funds_check_result_code) = (SELECT ICC.status_code,
335 ICC.cbc_result_code
336 FROM IGC_CC_INTERFACE ICC
337 WHERE ICC.DOCUMENT_TYPE = 'PA'
338 AND ICC.CC_HEADER_ID = p_budget_version_id
339 AND ICC.reference_1 = 'PA'
340 AND ICC.reference_2 = PBA.budget_version_id
341 AND ICC.reference_3 = PBA.budget_acct_line_id)
342 WHERE ((PBA.budget_version_id,
343 PBA.budget_acct_line_id) IN
344 (SELECT ICC.reference_2,
345 ICC.reference_3
346 FROM IGC_CC_INTERFACE ICC
347 WHERE ICC.cc_header_id = p_budget_version_id
348 AND ICC.DOCUMENT_TYPE = 'PA'
349 AND ICC.reference_1 = 'PA')) ;
350 log_message('rows updated into PA_BUDGET_ACCT_LINES Table .. '||sql%rowcount);
351
352 -- x_funds_chk_rsrv_status = Decode(l_ret_code,
353 -- Funds Check Return Code for the Packet processed. Valid Return Codes
354 -- are : 'S' for Success, 'A' for Advisory, 'F' for Failure, 'P' for Partial,
355 -- and 'T' for Fatal
356
357 SELECT decode(p_calling_mode,'CHECK',
358 (decode(substr(l_return_status,1,1),'S','P',
359 'A','P',
360 'F','F',
361 'T','F' )),
362 'RESERVE',
363 (decode(substr(l_return_status,1,1),'S','A',
364 'A','A',
365 'F','F',
366 'T','F' )),
367 'UNRESERVE',
368 (decode(substr(l_return_status,1,1),'S','A',
369 'A','A',
370 'F','F',
371 'T','F' )),
372 'YEAR_END_ROLLOVER',
373 (decode(substr(l_return_status,1,1),'S','A',
374 'A','A',
375 'F','F',
376 'T','F' )),
377 'UNRESERVE_YEAR_END_ROLLOVER',
378 (decode(substr(l_return_status,1,1),'S','A',
379 'A','A',
380 'F','F',
381 'T','F' )),
382 'F')
383 INTO l_funds_chk_rsrv_status
384 FROM dual ;
385
386 --commit;--Bug 6524116; Removed as this is causing issue with IGC funds checking.
387
388 x_funds_chk_rsrv_status := l_funds_chk_rsrv_status;
389
390 log_message('l_funds_chk_rsrv_status ... '||l_funds_chk_rsrv_status);
391 if ( l_funds_chk_rsrv_status = 'F' ) then
392 raise l_cc_funds_resv_fail;
393 end if;
394
395 EXCEPTION
396 WHEN l_cc_funds_resv_fail THEN
397 PA_UTILS.Add_Message('PA', 'PA_BC_CC_FNDS_RESV_FAIL');
398 x_return_status := FND_API.G_RET_STS_ERROR;
399 x_msg_data := 'PA_BC_CC_FNDS_RESV_FAIL';
400 x_msg_count := 1;
401 WHEN OTHERS THEN
402 log_message('Error in cc_funds_chk_rsrv ... '||SQLERRM);
403 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
404 x_msg_count := 1;
405 x_msg_data := substr(SQLERRM,1,240);
406 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_BUDGET_FUND_PKG',
407 p_procedure_name => 'cc_funds_chk_rsrv');
408 raise;
409
410 END cc_funds_chk_rsrv;
411
412 -- ------------------------------------------------------------------------ +
413 -- zero $ budget lines cannot be created in pa_bgt_baseline_pkg as it is
414 -- not visible in the AUTONOMOUS package
415
416 PROCEDURE Create_txn_lines_in_bc_balance(
417 p_project_id in number,
418 p_draft_budget_version_id in number,
419 p_set_of_books_id in number,
420 p_bdgt_intg_flag in varchar2,
421 x_return_status out NOCOPY varchar2,
422 x_error_message_code out NOCOPY varchar2) is
423
424 PRAGMA AUTONOMOUS_TRANSACTION;
425
426 l_start_date date;
427 l_end_date date;
428 l_tab_count number := 0;
429 l_tab_periods PA_FUNDS_CONTROL_UTILS.tab_closed_period;
430
431 l_BalRowIdTab PA_PLSQL_DATATYPES.RowidTabTyp;
432
433 --cursor to delete all versions prior to the latest baselined balance records if the
434 --budget has been baselined before.
435 cursor c_delbal(p_bdgt_ctrl_type in varchar2, p_bdgt_ver in number) is
436 select a.rowid
437 from pa_bc_balances a, pa_budgetary_control_options pbco, pa_budget_versions pbv
438 where pbv.budget_version_id <> p_bdgt_ver
439 and a.project_id = pbco.project_id
440 and a.project_id = pbv.project_id
441 and a.budget_version_id = pbv.budget_version_id
442 and pbco.bdgt_cntrl_flag = 'Y'
443 and pbco.budget_type_code = pbv.budget_type_code
444 and ((p_bdgt_ctrl_type = 'GL' and pbco.external_budget_code = 'GL')
445 or
446 (p_bdgt_ctrl_type = 'CC' and pbco.external_budget_code = 'CC')
447 or
448 (p_bdgt_ctrl_type = 'GL' and pbco.external_budget_code is null)
449 or
450 (p_bdgt_ctrl_type is null and pbco.external_budget_code is null))
451 and a.project_id = p_project_id;
452
453 -- who variables ..
454 l_date date;
455 l_login_id number;
456 BEGIN
457 g_procedure_name := 'Create_txn_lines_in_bc_balance';
458 log_message('Create_txn_lines_in_bc_balance:Start');
459
460 --Initialize the return status to success
461 x_return_status := FND_API.G_RET_STS_SUCCESS;
462
463 log_message('Create_txn_lines_in_bc_balance: Is it a re-baseline:'||g_cost_rebaseline_flag);
464
465 If ( g_processing_mode = 'BASELINE' and g_cost_rebaseline_flag = 'Y') then
466 log_message('Create_txn_lines_in_bc_balance:Before calling Sweeper');
467
468 If g_external_link = 'GL' then
469 PA_SWEEPER.UPDATE_ACT_ENC_BALANCE(
470 x_return_status => x_return_status,
471 x_error_message_code => x_error_message_code,
472 p_project_id => p_project_id);
473
474 End if;
475
476 log_message('Create_txn_lines_in_bc_balance:After calling Sweeper,RetSts = '||x_return_status);
477
478 If x_return_status <> FND_API.G_RET_STS_SUCCESS then
479 GOTO no_processing;
480 End If;
481 End If;
482
483 --Delete all records for the passed draft budget version id
484 --This is to make sure that existing balance records for the draft version id due to
485 --a failure in the budget baselining process will be deleted
486 log_message('Create_txn_lines_in_bc_balance:'|| 'Delete draft budget version- '||p_draft_budget_version_id);
487
488 Delete pa_bc_balances
489 where budget_version_id = p_draft_budget_version_id;
490
491 log_message('Create_txn_lines_in_bc_balance:'||SQL%ROWCOUNT||' records deleted from pa_bc_balances');
492
493 -- -------------------------------------------------------------------------------------------------+
494 IF (g_processing_mode = 'BASELINE' and g_cost_rebaseline_flag = 'Y') then -- II
495
496 -- Bug 5379210 : Deleting pa_bc_packets for draft version
497 Delete pa_bc_packets
498 where budget_version_id = p_draft_budget_version_id;
499
500 log_message('Create_txn_lines_in_bc_balance:'||SQL%ROWCOUNT||' records deleted from pa_bc_packets');
501
502 log_message('Create_txn_lines_in_bc_balance: Last baselined version:'||g_cost_prev_bvid);
503
504 --If there is a baselined version then we delete balance records for versions prior to this version
505 IF (g_cost_prev_bvid is not null) THEN
506
507 log_message('Create_txn_lines_in_bc_balance: Open cursor c_delbal to delete old verisons of budget');
508
509 OPEN c_delbal(g_external_link,g_cost_prev_bvid);
510 LOOP
511
512 l_BalRowIdTab.Delete;
513
514 FETCH c_delbal bulk collect into
515 l_BalRowIdTab
516 limit 1000;
517
518 if l_BalRowIdTab.count = 0 then
519 log_message('Create_txn_lines_in_bc_balance: No record to delete, exit');
520 exit;
521 end if;
522
523 log_message('Create_txn_lines_in_bc_balance:'||l_BalRowIdTab.count||' records being deleted');
524
525 FORALL j in l_BalRowIdTab.first..l_BalRowIdTab.last
526 delete from pa_bc_balances
527 where rowid = l_BalRowIdTab(j);
528
529 exit when c_delbal%notfound;
530 END LOOP;
531 CLOSE c_delbal;
532
533 log_message('Create_txn_lines_in_bc_balance: Closed cursor:c_delbal');
534
535 END IF;
536
537 End If; -- IF (p_calling_mode = 'RESERVE_BASELINE' and g_cost_rebaseline_flag = 'Y') then -- II
538 -- -------------------------------------------------------------------------------------------------+
539
540 IF (p_bdgt_intg_flag = 'Y') THEN
541 log_message('Create_txn_lines_in_bc_balance:Bdgt Intg Flag = Y');
542
543 Begin
544 select min(start_date), max(end_date)
545 into l_start_date, l_end_date
546 from pa_bc_balances
547 where project_id = p_project_id
548 and budget_version_id = g_cost_prev_bvid;
549 Exception
550 when no_data_found then
551 null;
552 End;
553
554 log_message('Create_txn_lines_in_bc_balance:Start Date,End Date = '|| l_start_date ||', '||l_end_date);
555 log_message('Create_txn_lines_in_bc_balance:Calling get gl periods');
556
557 --Get all periods given the start and end date.
558 PA_FUNDS_CONTROL_UTILS.get_gl_periods
559 (p_start_date => l_start_date,
560 p_end_date => l_end_date,
561 p_set_of_books_id => p_set_of_books_id,
562 x_tab_count => l_tab_count,
563 x_tab_pds => l_tab_periods,
564 x_return_status => x_return_status);
565
566 log_message('Create_txn_lines_in_bc_balance:After calling get gl periods,RetSts = '||x_return_status);
567 log_message('Create_txn_lines_in_bc_balance:Insert Close Period Balances, TabCount = '||l_tab_count);
568
569 If x_return_status <> FND_API.G_RET_STS_SUCCESS then
570 log_message('Create_txn_lines_in_bc_balance: PA_FUNDS_CONTROL_UTILS.get_gl_periods failed');
571 GOTO no_processing;
572 End If;
573
574 l_login_id := FND_GLOBAL.LOGIN_ID;
575 l_date := SYSDATE;
576
577 FOR i in 1..l_tab_count LOOP
578 log_message('Create_txn_lines_in_bc_balance:St,End and Status = ' ||l_tab_periods(i).start_date||':'||
579 l_tab_periods(i).end_date||':'||l_tab_periods(i).closing_status);
580
581 insert into pa_bc_balances(
582 PROJECT_ID,
583 TASK_ID,
584 TOP_TASK_ID,
585 RESOURCE_LIST_MEMBER_ID,
586 BALANCE_TYPE,
587 SET_OF_BOOKS_ID,
588 BUDGET_VERSION_ID,
589 LAST_UPDATE_DATE,
590 LAST_UPDATED_BY,
591 CREATED_BY,
592 CREATION_DATE,
593 LAST_UPDATE_LOGIN,
594 PERIOD_NAME,
595 START_DATE,
596 END_DATE,
597 PARENT_MEMBER_ID,
598 ACTUAL_PERIOD_TO_DATE,
599 ENCUMB_PERIOD_TO_DATE)
600 select
601 bal.PROJECT_ID,
602 bal.TASK_ID,
603 bal.TOP_TASK_ID,
604 bal.RESOURCE_LIST_MEMBER_ID,
605 bal.BALANCE_TYPE,
606 bal.SET_OF_BOOKS_ID,
607 p_draft_budget_version_id,
608 l_date,
609 l_login_id,
610 l_login_id,
611 l_date,
612 l_login_id,
613 bal.PERIOD_NAME,
614 bal.START_DATE,
615 bal.END_DATE,
616 bal.PARENT_MEMBER_ID,
617 bal.ACTUAL_PERIOD_TO_DATE,
618 bal.ENCUMB_PERIOD_TO_DATE
619 from pa_bc_balances bal
620 where budget_version_id = g_cost_prev_bvid
621 and trunc(start_date) = trunc(l_tab_periods(i).start_date)
622 and trunc(end_date) = trunc(l_tab_periods(i).end_date)
623 and l_tab_periods(i).closing_status = 'C'
624 and project_id = p_project_id
625 and balance_type <> 'BGT';
626
627 log_message('Create_txn_lines_in_bc_balance:'||SQL%ROWCOUNT||' records inserted');
628
629 END LOOP;
630
631 log_message('Create_txn_lines_in_bc_balance: Inserted closed period balances');
632
633 END IF; -- IF (p_bdgt_intg_flag = 'Y') THEN
634
635 <<no_processing>>
636 log_message('Create_txn_lines_in_bc_balance: Commit');
637 commit;
638
639 log_message('Create_txn_lines_in_bc_balance: End');
640
641 EXCEPTION
642 WHEN OTHERS THEN
643 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_BUDGET_FUND_PKG'
644 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
645
646 IF c_delbal%ISOPEN THEN
647 close c_delbal;
648 END IF;
649
650 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
651 x_error_message_code := (SQLCODE||' '||SQLERRM);
652 log_message('Create_txn_lines_in_bc_balance: x_error_message_code'||x_error_message_code);
653 RAISE;
654 END Create_txn_lines_in_bc_balance;
655
656 PROCEDURE Create_bgt_lines_in_bc_balance(
657 p_set_of_books_id in number,
658 p_budget_version_id in number,
659 p_project_id in number,
660 t_task_id in PA_PLSQL_DATATYPES.IdTabTyp,
661 t_top_task_id in PA_PLSQL_DATATYPES.IdTabTyp,
662 t_rlmi in PA_PLSQL_DATATYPES.IdTabTyp,
663 t_parent_rlmi in PA_PLSQL_DATATYPES.IdTabTyp,
664 t_period in PA_PLSQL_DATATYPES.Char30TabTyp,
665 t_start_date in PA_PLSQL_DATATYPES.DateTabTyp,
666 t_end_date in PA_PLSQL_DATATYPES.DateTabTyp,
667 t_burden_cost in PA_PLSQL_DATATYPES.NumTabTyp,
668 x_ret_status out NOCOPY varchar2,
669 x_err_message_code out NOCOPY varchar2) is
670
671 PRAGMA AUTONOMOUS_TRANSACTION;
672
673 -- who variables ..
674 l_date date;
675 l_login_id number;
676
677 Begin
678 g_procedure_name := 'Create_bgt_lines_in_bc_balance';
679 log_message('Create_bgt_lines_in_bc_balance: Start');
680 l_login_id := FND_GLOBAL.LOGIN_ID;
681 l_date := SYSDATE;
682 x_ret_status := FND_API.G_RET_STS_SUCCESS;
683
684 FORALL i in t_task_id.FIRST..t_task_id.LAST
685 insert into pa_bc_balances(
686 PROJECT_ID,
687 TASK_ID,
688 TOP_TASK_ID,
689 RESOURCE_LIST_MEMBER_ID,
690 BALANCE_TYPE,
691 SET_OF_BOOKS_ID,
692 BUDGET_VERSION_ID,
693 LAST_UPDATE_DATE,
694 LAST_UPDATED_BY,
695 CREATED_BY,
696 CREATION_DATE,
697 LAST_UPDATE_LOGIN,
698 PERIOD_NAME,
699 START_DATE,
700 END_DATE,
701 PARENT_MEMBER_ID,
702 BUDGET_PERIOD_TO_DATE,
703 ACTUAL_PERIOD_TO_DATE,
704 ENCUMB_PERIOD_TO_DATE)
705 values(
706 p_project_id,
707 t_task_id(i),
708 t_top_task_id(i),
709 t_rlmi(i),
710 'BGT',
711 p_set_of_books_id,
712 p_budget_version_id,
713 l_date,
714 l_login_id,
715 l_login_id,
716 l_date,
717 l_login_id,
718 t_period(i),
719 t_start_date(i),
720 t_end_date(i),
721 t_parent_rlmi(i),
722 t_burden_cost(i),
723 0,
724 0);
725 log_message('Create_bgt_lines_in_bc_balance: End');
726 COMMIT;
727 EXCEPTION
728 WHEN OTHERS THEN
729 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_BUDGET_FUND_PKG'
730 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
731
732 x_ret_status := FND_API.G_RET_STS_UNEXP_ERROR ;
733 x_err_message_code := (SQLCODE||' '||SQLERRM);
734 log_message('Create_bgt_lines_in_bc_balance: x_err_message_code'||x_err_message_code);
735 RAISE;
736 End Create_bgt_lines_in_bc_balance;
737
738 PROCEDURE Establish_bc_balances(
739 p_project_id in number,
740 p_draft_budget_version_id in number,
741 p_base_budget_version_id in number,
742 p_bdgt_intg_flag in varchar2,
743 x_return_status out NOCOPY varchar2,
744 x_error_message_code out NOCOPY varchar2) is
745
746 --Cursor to select BGT budget balances from pa_budget_lines,etc.
747 cursor c_bdgt_bal is
748 select pa.task_id,
749 pt.top_task_id,
750 pa.resource_list_member_id,
751 pb.PERIOD_NAME,
752 pb.START_DATE,
753 pb.END_DATE,
754 rm.PARENT_MEMBER_ID,
755 pb.burdened_cost
756 from
757 pa_budget_lines pb,
758 pa_resource_assignments pa,
759 pa_tasks pt,
760 pa_resource_list_members rm,
761 pa_budget_versions pbv
762 where pbv.budget_version_id = p_base_budget_version_id
763 and pa.resource_assignment_id = pb.resource_assignment_id
764 and pa.task_id = pt.task_id (+)
765 and pa.budget_version_id = pbv.budget_version_id
766 and rm.resource_list_member_id = pa.resource_list_member_id;
767
768 --Tables to insert BGT lines into pa_bc_balances.
769 l_TaskTab PA_PLSQL_DATATYPES.IdTabTyp;
770 l_TTaskTab PA_PLSQL_DATATYPES.IdTabTyp;
771 l_RlmiTab PA_PLSQL_DATATYPES.IdTabTyp;
772 l_PeriodTab PA_PLSQL_DATATYPES.Char30TabTyp;
773 l_StDateTab PA_PLSQL_DATATYPES.DateTabTyp;
774 l_EdDateTab PA_PLSQL_DATATYPES.DateTabTyp;
775 l_ParMemTab PA_PLSQL_DATATYPES.IdTabTyp;
776 l_BurdCostTab PA_PLSQL_DATATYPES.NumTabTyp;
777
778 l_sob_id pa_implementations_all.set_of_books_id%TYPE;
779
780 BEGIN
781 g_procedure_name := 'Establish_bc_balances';
782 log_message('Establish_bc_balances:Start');
783
784 --Initialize the return status to success
785 x_return_status := FND_API.G_RET_STS_SUCCESS;
786
787 log_message('Establish_bc_balances: Get SOB_ID');
788
789 select to_number(set_of_books_id) into l_sob_id from pa_implementations_all
790 where org_id = g_org_id;
791
792 log_message('Establish_bc_balances: SOB_ID:'||l_sob_id);
793
794 log_message('Establish_bc_balances: Before calling Create_txn_lines_in_bc_balance');
795
796 CREATE_TXN_LINES_IN_BC_BALANCE(
797 p_project_id => p_project_id,
798 p_draft_budget_version_id => p_draft_budget_version_id,
799 p_set_of_books_id => l_sob_id,
800 p_bdgt_intg_flag => p_bdgt_intg_flag,
801 x_return_status => x_return_status,
802 x_error_message_code => x_error_message_code);
803
804 g_procedure_name := 'Establish_bc_balances';
805 log_message('Establish_bc_balances: After calling Create_txn_lines_in_bc_balance,RetSts = '||x_return_status);
806
807
808 --Insert BGT lines.
809 log_message('Establish_bc_balances: Before inserting BGT lines');
810
811 open c_bdgt_bal;
812 loop
813 l_TaskTab.Delete;
814 l_TTaskTab.Delete;
815 l_RlmiTab.Delete;
816 l_PeriodTab.Delete;
817 l_StDateTab.Delete;
818 l_EdDateTab.Delete;
819 l_ParMemTab.Delete;
820 l_BurdCostTab.Delete;
821
822 log_message('Establish_bc_balances: Fetch c_bdgt_bal');
823
824 fetch c_bdgt_bal bulk collect into
825 l_TaskTab,
826 l_TTaskTab,
827 l_RlmiTab,
828 l_PeriodTab,
829 l_StDateTab,
830 l_EdDateTab,
831 l_ParMemTab,
832 l_BurdCostTab
833 limit 1000;
834
835 IF (l_TaskTab.count = 0) THEN
836 log_message('Establish_bc_balances: No rec in c_bdgt_bal, exit');
837 EXIT;
838 END IF;
839
840 log_message('Establish_bc_balances: Before Insert, no. of rec = '|| l_TaskTab.count);
841
842 log_message('Establish_bc_balances: Before calling Create_bgt_lines_in_bc_balance');
843
844
845 CREATE_BGT_LINES_IN_BC_BALANCE(
846 p_set_of_books_id => l_sob_id,
847 p_budget_version_id => p_draft_budget_version_id,
848 p_project_id => p_project_id,
849 t_task_id => l_TaskTab,
850 t_top_task_id => l_TTaskTab,
851 t_rlmi => l_RlmiTab,
852 t_parent_rlmi => l_ParMemTab,
853 t_period => l_PeriodTab,
854 t_start_date => l_StDateTab,
855 t_end_date => l_EdDateTab,
856 t_burden_cost => l_BurdCostTab,
857 x_ret_status => x_return_status,
858 x_err_message_code => x_error_message_code);
859
860 g_procedure_name := 'Establish_bc_balances';
861 log_message('Establish_bc_balances: After calling Create_bgt_lines_in_bc_balance,RetSts = '||x_return_status);
862 exit when c_bdgt_bal%notfound;
863 end loop;
864 close c_bdgt_bal;
865
866 log_message('Establish_bc_balances: End');
867
868 EXCEPTION
869 WHEN OTHERS THEN
870 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_BUDGET_FUND_PKG'
871 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
872
873 IF c_bdgt_bal%ISOPEN THEN
874 close c_bdgt_bal;
875 END IF;
876 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
877 x_error_message_code := (SQLCODE||' '||SQLERRM);
878 log_message('Establish_bc_balances: x_error_message_code'||x_error_message_code);
879 RAISE;
880 END Establish_bc_balances;
881 -- zero $ budget lines fix ..
882 -- ------------------------------------------------------------------------ +
883
884 -- -------------------------------------------------------------------+
885 -- This is the main procedure called from budget integration/workflow
886 -- -------------------------------------------------------------------+
887 PROCEDURE check_or_reserve_funds ( p_project_id IN NUMBER,
888 p_budget_version_id IN NUMBER,
889 p_calling_mode IN VARCHAR2,
890 x_dual_bdgt_cntrl_flag OUT NOCOPY VARCHAR2,
891 x_cc_budget_version_id OUT NOCOPY NUMBER,
892 x_return_status OUT NOCOPY VARCHAR2,
893 x_msg_count OUT NOCOPY NUMBER,
894 x_msg_data OUT NOCOPY VARCHAR2 )
895 IS
896
897 l_budget_processing_failure EXCEPTION;
898
899 l_top_down_bdgt_failed EXCEPTION;
900
901 l_dual_mode_acct_gen_failed EXCEPTION;
902 l_dual_bc_fc_failed EXCEPTION;
903 l_cbc_not_supported EXCEPTION;
904
905 l_draft_budget_version_id PA_BUDGET_VERSIONS.budget_version_id%TYPE;
906 l_cc_draft_budget_version_id PA_BUDGET_VERSIONS.budget_version_id%TYPE;
907 l_total_cc_bdgt_amount PA_BUDGET_LINES.burdened_cost%TYPE ;
908 l_total_gl_bdgt_amount PA_BUDGET_LINES.burdened_cost%TYPE ;
909 l_rebaseline_flag VARCHAR2(1);
910 l_cc_budget_type_code VARCHAR2(30) ;
911 l_cc_encumbrance_type_id NUMBER ;
912
913 l_funds_chk_rsrv_status VARCHAR2(1);
914 l_calling_module VARCHAR2(20);
915
916 l_budget_type_code PA_BUDGET_VERSIONS.budget_type_code%TYPE ;
917 l_balance_type PA_BUDGETARY_CONTROL_OPTIONS.Balance_type%TYPE ;
918 l_external_budget_code PA_BUDGETARY_CONTROL_OPTIONS.External_budget_code%TYPE ;
919 l_encumbrance_Type_Id PA_BUDGETARY_CONTROL_OPTIONS.Encumbrance_Type_Id%TYPE ;
920 l_bdgt_cntrl_flag PA_BUDGETARY_CONTROL_OPTIONS.Bdgt_cntrl_flag%TYPE ;
921 l_budget_type PA_BUDGETARY_CONTROL_OPTIONS.budget_type_code%TYPE ;
922 l_budget_amount_code PA_BUDGET_TYPES.Budget_amount_code%TYPE;
923 l_budget_entry_level_code PA_Budget_Entry_Methods.entry_level_code%TYPE;
924
925 l_dual_bdgt_cntrl_flag VARCHAR2(1);
926 l_cc_budget_version_id NUMBER;
927 l_gl_budget_version_id NUMBER;
928 l_return_status VARCHAR2(1) ;
929 t_return_status VARCHAR2(1) ;
930 l_msg_count NUMBER ;
931 l_msg_data VARCHAR2(240) ;
932 l_dummy number;
933 l_sqlerrm VARCHAR2(4000);
934 l_request_id NUMBER;
935 l_template_flag pa_projects_all.template_flag%type;
936 --Bug 6524116
937 l_cc_budget_entry_level_code VARCHAR2(30) ;
938 P_prev_budget_version_id number;
939 rejected_event_id_tab PSA_FUNDS_CHECKER_PKG.num_rec;
940 ledger_id_tab PSA_FUNDS_CHECKER_PKG.num_rec;
941
942 CURSOR c_budget_sum_amt(c_budget_version_id NUMBER) IS
943 SELECT sum(decode(nvl(PBL.burdened_cost,0),
944 0,nvl(PBL.raw_cost,0),
945 PBL.burdened_cost))
946 FROM PA_BUDGET_LINES PBL,
947 PA_BUDGET_VERSIONS PBV,
948 PA_RESOURCE_ASSIGNMENTS PRA
949 WHERE PBV.project_id = p_project_id
950 AND PBV.budget_version_id = PRA.budget_version_id
951 AND PRA.resource_assignment_id = PBL.resource_assignment_id
952 AND PBV.budget_version_id = c_budget_version_id;
953
954
955 Cursor c_Budget_funds is
956 Select PBV.Budget_type_code,
957 PBCO.Balance_type,
958 PBCO.External_budget_code,
959 PBCO.Encumbrance_Type_Id,
960 PBCO.Bdgt_cntrl_flag,
961 PBCO.gl_budget_version_id,
962 PBT.budget_amount_code,
963 BEM.entry_level_code
964 From PA_BUDGETARY_CONTROL_OPTIONS PBCO ,
965 PA_BUDGET_VERSIONS PBV,
966 PA_BUDGET_TYPES PBT,
967 PA_BUDGET_ENTRY_METHODS BEM
968 WHERE PBCO.Project_Id = p_project_id
969 AND PBV.Budget_version_id = p_Budget_version_id
970 AND PBV.Budget_Type_Code = PBCO.Budget_Type_Code
971 AND PBT.Budget_type_code = PBV.Budget_type_code
972 AND PBT.Budget_type_code = PBV.Budget_type_code
973 AND BEM.Budget_Entry_Method_Code = PBV.Budget_Entry_Method_Code;
974
975 --Bug 6524116
976 cursor get_rejected_event_id is
977 select bc_event_id, g_org_id
978 from pa_budget_lines
979 where budget_version_id = P_Budget_version_id
980 and bc_event_id is not null
981 union
982 select bc_rev_event_id, g_org_id
983 from pa_budget_lines
984 where budget_version_id = P_prev_budget_version_id
985 and bc_rev_event_id is not null;
986
987 BEGIN
988
989 --x_return_status := FND_API.G_RET_STS_SUCCESS;
990 --l_return_status := FND_API.G_RET_STS_SUCCESS; /*Bug 3794994 */
991
992 -----------------------------------------------+
993 -- 0.0: initalize ...
994 -----------------------------------------------+
995 g_procedure_name := 'check_or_reserve_funds';
996 g_project_id := p_project_id;
997
998 fnd_profile.get('PA_DEBUG_MODE',g_debug_mode);
999
1000 g_debug_mode := nvl(g_debug_mode,'N');
1001
1002 --- Initialize the error statck
1003 PA_DEBUG.init_err_stack ('PA_BUDGET_FUND_PKG.check_or_reserve_funds');
1004
1005 log_message('Entering check_or_reserve_funds API .........') ;
1006
1007 -- ----------------------------------------------------------------------+
1008 -- Check if the project is a template project, if so exit with success ..
1009 -- You should not call FC for template project ...
1010 -- ----------------------------------------------------------------------+
1011 SELECT nvl(template_flag,'N'), org_id
1012 INTO l_template_flag , g_org_id
1013 FROM pa_projects_all
1014 WHERE project_id = p_project_id;
1015
1016 If l_template_flag = 'Y' then
1017 log_message('check_or_reserve_funds called for template project ..exit with success');
1018 x_return_status := FND_API.G_RET_STS_SUCCESS;
1019 RETURN;
1020 End If;
1021
1022 x_dual_bdgt_cntrl_flag := 'N';
1023 x_cc_budget_version_id := NULL;
1024
1025 -----------------------------------------------+
1026 -- 1.0: Get the budgetary control options
1027 -----------------------------------------------+
1028 log_message('Opening budget_funds cursor') ;
1029 log_message('proj_id '||to_char(p_project_id)||'bver_id '
1030 ||to_char(p_budget_version_id ));
1031
1032 OPEN c_Budget_funds ;
1033 FETCH c_Budget_funds
1034 INTO l_budget_type_code ,
1035 l_balance_type,
1036 l_external_budget_code,
1037 l_encumbrance_Type_Id,
1038 l_bdgt_cntrl_flag ,
1039 l_gl_budget_version_id,
1040 l_budget_amount_code,
1041 l_budget_entry_level_code;
1042
1043 IF c_Budget_funds%NOTFOUND THEN
1044 l_msg_data := 'PA_BC_NO_BGT_CNTL';
1045 RAISE l_budget_processing_failure;
1046
1047 END IF;
1048
1049 CLOSE c_Budget_funds ;
1050
1051 log_message('Fetched Values ... ');
1052 log_message('--------------------------------');
1053 log_message(' budget_type_code : '||l_budget_type_code );
1054 log_message(' balance_type : '||l_balance_type );
1055 log_message(' external_budget_code : '||l_external_budget_code );
1056 log_message(' encumbrance_type_id : '||to_char(l_encumbrance_type_id) );
1057 log_message(' l_bdg_cntrl_flag : '||l_bdgt_cntrl_flag );
1058 log_message(' l_gl_budget_version_id : '||to_char(l_gl_budget_version_id) );
1059 log_message(' l_budget_amount_code : '||l_budget_amount_code);
1060 log_message(' l_budget_entry_level_code : '||l_budget_entry_level_code);
1061
1062 -----------------------------------------------+
1063 -- 1.1: Disabling CBC ...
1064 -----------------------------------------------+
1065 /* Commented for bug 6524116
1066 If l_balance_type = 'E' then
1067 Begin
1068 select 'CC'
1069 into l_cc_budget_type_code
1070 from pa_budgetary_control_options cc
1071 where cc.project_id = p_project_id
1072 and cc.external_budget_code = 'CC';
1073 Exception
1074 When no_data_found then
1075 l_cc_budget_type_code := null;
1076 When too_many_rows then
1077 -- This has been added to handle case if multiple
1078 -- budget types could be added 'cause of issue with
1079 -- PABDINTG form
1080 l_cc_budget_type_code := 'CC';
1081 End;
1082
1083 If l_cc_budget_type_code = 'CC' then
1084 l_msg_data := 'PA_CBC_NOT_SUPPORTED';
1085 RAISE l_cbc_not_supported;
1086 End If;
1087 End If;
1088 */
1089
1090 -- ------------------------------------------------------------+
1091 -- 2.0: Check if revenue budget is not a bottom up budget
1092 -- ------------------------------------------------------------+
1093
1094 -- Following code was added in r12 but being commented out as the
1095 -- budgetary control form executes this validation
1096 -- IF (l_budget_amount_code = 'R' and l_balance_type <> 'E') THEN
1097 -- log_message(' Revenue Budget Validation');
1098 -- l_msg_data := 'PA_BC_REV_BUD_ERR';
1099 -- RAISE l_budget_processing_failure;
1100 -- END IF;
1101
1102 -- -----------------------------------------------------------+
1103 -- 3.0: Set global variables .. Used in funds check -tieback
1104 -- -----------------------------------------------------------+
1105
1106 log_message(' Set global variables ');
1107
1108 g_cost_current_bvid := p_budget_version_id;
1109 g_budget_amount_code := l_budget_amount_code;
1110 g_balance_type := l_balance_type;
1111
1112 Begin
1113 Select 'Y'
1114 into g_cost_rebaseline_flag
1115 from pa_budget_versions pbv
1116 where pbv.project_id = p_project_id
1117 and pbv.budget_version_id <> p_budget_version_id -- not the current budget
1118 and pbv.budget_status_code = 'B'
1119 and pbv.budget_type_code = l_budget_type_code
1120 and rownum =1;
1121 Exception
1122 When no_data_found then
1123 g_cost_rebaseline_flag := 'N';
1124 End;
1125
1126 log_message('g_cost_rebaseline_flag : '||g_cost_rebaseline_flag );
1127
1128 --g_cost_rebaseline_flag := PA_FUNDS_CONTROL_UTILS.Is_Budget_Baselined_Before(p_project_id);
1129 -- Issue with above API was that even for first time baseline, the flag was being set to 'Y'
1130
1131 g_cc_rebaseline_flag := g_cost_rebaseline_flag;
1132
1133 If p_calling_mode = 'CHECK_FUNDS' then
1134 g_processing_mode := 'CHECK_FUNDS';
1135 Elsif p_calling_mode = 'RESERVE_BASELINE' then
1136 g_processing_mode := 'BASELINE';
1137 End if;
1138
1139 log_message('g_processing_mode:'||g_processing_mode);
1140
1141 If ( l_external_budget_code is not null and l_external_budget_code <> 'CC') then
1142 g_external_link := 'GL';
1143 ElsIf l_external_budget_code = 'CC' then
1144 If g_processing_mode = 'CHECK_FUNDS' then
1145 g_external_link := 'CC';
1146 ElsIf g_processing_mode = 'BASELINE' then
1147 g_external_link := 'DUAL';
1148 End If;
1149 End If;
1150
1151 log_message('g_external_link:'||g_external_link);
1152
1153
1154 If (g_cost_rebaseline_flag = 'Y' and l_External_budget_code is not null) then -- B1
1155 log_message('Get previous baselined version');
1156
1157 If g_processing_mode = 'CHECK_FUNDS' then
1158
1159 g_cost_prev_bvid := GET_PREVIOUS_BVID(p_project_id => p_project_id,
1160 p_budget_type_code => l_budget_type_code,
1161 p_curr_budget_status_code => 'S');
1162
1163 Elsif g_processing_mode = 'BASELINE' then
1164
1165 g_cost_prev_bvid := GET_PREVIOUS_BVID(p_project_id => p_project_id,
1166 p_budget_type_code => l_budget_type_code,
1167 p_curr_budget_status_code => 'B');
1168
1169
1170 End If; -- processing mode
1171
1172 log_message('g_cost_prev_bvid:'||g_cost_prev_bvid);
1173
1174 End If; -- B1
1175
1176 -- ------------------------------------------------------------+
1177 -- 4.0: For a non- integrated budget or a top down budget, in
1178 -- reserve mode, do the following:
1179 -- 4A. Create budgetary control records ..
1180 --
1181 -- For top-down and bottom up in reserve mode
1182 -- 4B. Derive draft version ..
1183 -- ------------------------------------------------------------+
1184
1185 IF p_calling_mode = 'RESERVE_BASELINE' THEN -- 4.0
1186
1187 IF nvl(l_balance_type,'E') <> 'B' THEN -- 4.1
1188
1189 -- 4A. Create budgetary control records
1190 log_message('Create budgetary control records');
1191
1192 pa_budgetary_controls_pkg.bud_ctrl_create
1193 (x_budget_version_id => p_budget_version_id,
1194 x_calling_mode => 'BASELINE',
1195 X_Return_Status => l_return_status,
1196 X_Msg_Count => l_msg_count,
1197 X_Msg_Data => l_msg_data) ;
1198
1199 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1200 RAISE l_budget_processing_failure;
1201 END IF ;
1202
1203 END IF; --IF (nvl(l_balance_type,'E') <> 'B' THEN -- 4.1
1204 g_procedure_name := 'check_or_reserve_funds';
1205
1206 -- -------------------------------------------------------------+
1207 --If g_cost_rebaseline_flag = 'Y' then
1208
1209 -- 4B: Derive draft version
1210 -- This is reqd. as PA FC will be executed with the draft version and then the budget version will
1211 -- be udpated to the baselined version. Why was this done? PA FC is called in autonomous mode
1212 -- where the baselined version will not be visible. Also note: that in PA FC, account level
1213 -- FC is not executed. Account level FC is executed during FC tieback when called fomr BC API ..
1214
1215 log_message(' Derive Draft version');
1216
1217 Begin
1218
1219 Select pbv.budget_version_id
1220 into l_draft_budget_version_id
1221 from pa_budget_versions pbv
1222 where pbv.project_id = p_project_id
1223 and pbv.budget_status_code = 'S' -- Changed from 'W' to 'S' (UT code fix)
1224 and pbv.budget_type_code = l_budget_type_code;
1225
1226 Exception
1227 When no_data_found then
1228 l_msg_data := 'PA_GET_DRAFT_VERSION_FAILED';
1229 RAISE l_budget_processing_failure;
1230 End;
1231 --End If; --If g_cost_rebaseline_flag = 'Y' then
1232
1233 log_message(' draft version:'||l_draft_budget_version_id);
1234
1235 END IF; -- 4.0
1236
1237 If p_calling_mode = 'CHECK_FUNDS' then
1238 g_draft_bvid := P_Budget_version_id;
1239 Else
1240 g_draft_bvid := l_draft_budget_version_id;
1241 End If;
1242
1243 log_message(' Main processing');
1244
1245 -- -----------------------------------------------+
1246 -- 5.0: Check Funds Processing ...
1247 -- -----------------------------------------------+
1248
1249 IF (p_calling_mode = 'CHECK_FUNDS') THEN -- I
1250
1251 log_message(' Calling mode CHECK_FUNDS ') ;
1252
1253 IF ( l_external_budget_code is not null and l_external_budget_code <> 'CC') THEN
1254
1255 -- Handle CC here ..
1256
1257 -- GL Budget, Call create_events_and_fundscheck in 'Check_Baseline' Mode
1258 -- Only coding for cost budget currently ...
1259
1260 log_message('Calling CREATE_EVENTS_AND_FUNDSCHECK');
1261
1262 CREATE_EVENTS_AND_FUNDSCHECK
1263 (P_calling_module => 'Cost_Budget',
1264 P_mode => 'Check_Baseline',
1265 P_External_Budget_Code => 'GL',
1266 P_budget_version_id => P_Budget_version_id,
1267 P_cc_budget_version_id => NULL,
1268 P_result_code => l_return_status);
1269
1270 g_procedure_name := 'check_or_reserve_funds';
1271 log_message('After call to CREATE_EVENTS_AND_FUNDSCHECK,l_return_status['||l_return_status
1272 ||']');
1273
1274 IF l_return_status = 'E' THEN
1275 If g_msg_data is null then
1276 g_msg_data := 'PA_BC_FATAL';
1277 End If;
1278 l_msg_data := g_msg_data;
1279 RAISE l_budget_processing_failure ;
1280 END IF;
1281
1282 If l_return_status = 'S' THEN
1283 x_msg_data := 'PA_CHK_FUNDS_SUCCESSFUL';
1284 x_msg_count := 1;
1285 PA_UTILS.Add_Message('PA', x_msg_data);
1286 End If;
1287
1288 COMMIT; -- Success , so commit .. this is reqd. so that the budget lines
1289 -- reflect the event_id that gets stamped and also the account
1290 -- change that happens during FC is reflected on budget lines
1291 -- anda ccount summary table is rebuild with the new acct.
1292
1293 END IF ; /* external_budget_code if statement */
1294
1295 ELSIF (p_calling_mode = 'RESERVE_BASELINE') THEN -- I
1296
1297 log_message('Reserve Baseline Mode');
1298
1299 -- --------------------------------------------------------------+
1300 -- 6.A: Delete data from pa_bc_packets for the draft version ..
1301 -- Basically, data will exist if prev. baseline failed
1302 -- --------------------------------------------------------------+
1303 If (p_calling_mode = 'RESERVE_BASELINE' and g_cost_rebaseline_flag ='Y') then
1304 log_message('Calling Delete draft bc pkt');
1305
1306 DELETE_DRAFT_BC_PACKETS(p_draft_bud_ver_id => l_draft_budget_version_id);
1307
1308 g_procedure_name := 'check_or_reserve_funds';
1309 log_message(' After Calling Delete draft bc pkt');
1310 End if;
1311
1312
1313 -- -----------------------------------------------+
1314 -- 6.0: Reserve (Baseline) - Non Integrated
1315 -- -----------------------------------------------+
1316
1317 IF ( l_External_budget_code is null ) then -- II
1318
1319 --If g_cost_rebaseline_flag ='Y' then
1320
1321 log_message('Non-Integrated Budgets');
1322
1323 log_message('Calling Establish_Bc_Balances');
1324
1325 ESTABLISH_BC_BALANCES(
1326 p_project_id => p_project_id,
1327 p_draft_budget_version_id => l_draft_budget_version_id,
1328 p_base_budget_version_id => p_budget_version_id,
1329 p_bdgt_intg_flag => 'N',
1330 x_return_status => l_return_status,
1331 x_error_message_code => l_msg_data);
1332
1333 g_procedure_name := 'check_or_reserve_funds';
1334 log_message('After calling Establish_Bc_Balances,x_return_status:'||x_return_status);
1335
1336 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1337 RAISE l_budget_processing_failure ;
1338 END IF;
1339
1340 log_message('Calling maintain_bal_fchk');
1341
1342 pa_bgt_baseline_pkg.maintain_bal_fchk(
1343 P_PROJECT_ID => p_project_id,
1344 P_BUDGET_VERSION_ID => l_draft_budget_version_id,
1345 P_BASELINED_BUDGET_VERSION_ID => p_budget_version_id,
1346 P_BDGT_CTRL_TYPE => 'GL' ,
1347 P_CALLING_MODE => p_calling_mode,
1348 P_BDGT_INTG_FLAG => 'N',
1349 X_RETURN_STATUS => l_return_status,
1350 X_ERROR_MESSAGE_CODE => l_msg_data) ;
1351
1352 log_message('After Calling maintain_bal_fchk,x_return_status:'||x_return_status);
1353
1354 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1355 RAISE l_budget_processing_failure ;
1356 Else
1357
1358 -- Update pa_bc_packets.status_code = 'A'
1359 Update_bc_packets_pass(p_bud_ver_id => p_budget_version_id);
1360 -- Bug 5206537 : Procedure to stamp latest budget version id and budget line id on CDL
1361 PA_FUNDS_CONTROL_UTILS.Update_bvid_blid_on_cdl_bccom ( p_bud_ver_id => p_budget_version_id,
1362 p_calling_mode => p_calling_mode);
1363
1364 END IF ;
1365
1366 --End If;
1367
1368 ELSIF ( l_balance_type = 'B') then -- II
1369
1370 -- -----------------------------------------------+
1371 -- 7.0: Reserve (Baseline) - Bottom Up
1372 -- -----------------------------------------------+
1373 -- bottom_up budgeting
1374 log_message('implemented bottom up budgeting ....') ;
1375
1376 log_message('Call Account generator');
1377
1378 /* =========================================================== +
1379
1380 || -----------------------------------------------------------+
1381 || Following code is being commented out ... 11/29/05
1382 || There is no need to call account generator to create the
1383 || zero $ lines for bottom up budgeting
1384 || All other records will have ccid as submit creates the ccid
1385 || Creating zero $ records for a closed period will fail GL FC
1386 || with F26. To minimize this ..commenting following code ..
1387 || -----------------------------------------------------------+
1388
1389 -- ## Call Budget account generator ..
1390 -- Calling gen_acct_all_lines which is non autonomous ..
1391
1392 PA_BUDGET_ACCOUNT_PKG.Gen_Acct_All_Lines (
1393 P_Budget_Version_ID => p_budget_version_id,
1394 P_Calling_Mode => 'BASELINE' ,
1395 P_Budget_Type_Code => l_budget_type_code,
1396 P_Budget_Entry_Level_Code => l_budget_entry_level_code,
1397 P_Project_ID => p_project_id,
1398 X_Return_Status => l_return_status,
1399 X_Msg_Count => l_msg_count,
1400 X_Msg_Data => l_msg_data) ;
1401
1402 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1403 RAISE l_budget_processing_failure ;
1404 END IF ;
1405
1406 ============================================================== */
1407
1408 log_message('Call BC Funds Check API');
1409
1410 -- ## Call Budgetary Control Funds Check API
1411
1412 Select decode(g_budget_amount_code,'C','Cost_Budget','R','Revenue_Budget')
1413 into l_calling_module
1414 from dual;
1415
1416 log_message('Calling CREATE_EVENTS_AND_FUNDSCHECK');
1417
1418 CREATE_EVENTS_AND_FUNDSCHECK
1419 (P_calling_module => l_calling_module,
1420 P_mode => 'Reserve_Baseline',
1421 P_External_Budget_Code => 'GL',
1422 P_budget_version_id => P_Budget_version_id,
1423 P_cc_budget_version_id => NULL,
1424 P_result_code => l_return_status);
1425
1426 g_procedure_name := 'check_or_reserve_funds';
1427 log_message('After call to CREATE_EVENTS_AND_FUNDSCHECK,l_return_status['||l_return_status
1428 ||']');
1429
1430 IF l_return_status = 'E' THEN
1431 l_msg_data := g_msg_data;
1432 RAISE l_budget_processing_failure;
1433 END IF;
1434
1435 elsif ( l_balance_type = 'E' ) then -- II
1436 -- -----------------------------------------------+
1437 -- 8.0: Reserve (Baseline) - Top Down
1438 -- -----------------------------------------------+
1439
1440 -- top down budgeting
1441 log_message('implemented top down budgeting ....') ;
1442
1443 -- ## 1. Check if its dual budgeting ...
1444 log_message(' Check if its dual budgeting ...');
1445
1446 BEGIN
1447 Select budget_type_code, encumbrance_type_id
1448 into l_cc_budget_type_code, l_cc_encumbrance_type_id
1449 from pa_budgetary_control_options
1450 where project_id = p_project_id
1451 and external_budget_code = 'CC' ;
1452 EXCEPTION
1453 WHEN NO_DATA_FOUND THEN
1454 l_cc_budget_type_code := NULL ;
1455 l_cc_encumbrance_type_id := NULL ;
1456 END ;
1457
1458 log_message('l_cc_budget_type_code, l_cc_encumbrance_type_id := '
1459 ||l_cc_budget_type_code||','||to_char(l_cc_encumbrance_type_id)) ;
1460
1461 -- ------------------------------------------------------------+
1462 If ( l_cc_budget_type_code is null ) then -- III
1463 -- ## 2. Its not Dual integration
1464
1465 --If g_cost_rebaseline_flag = 'Y' then
1466 -- ## 2.1 Call Budget Account generator ..
1467 -- Calling gen_acct_all_lines which is non autonomous ..
1468 log_message('Call Budget Account generator');
1469
1470 PA_BUDGET_ACCOUNT_PKG.Gen_Acct_All_Lines (
1471 P_Budget_Version_ID => p_budget_version_id,
1472 P_Calling_Mode => 'BASELINE' ,
1473 P_Budget_Type_Code => l_budget_type_code,
1474 P_Budget_Entry_Level_Code => l_budget_entry_level_code,
1475 P_Project_ID => p_project_id,
1476 X_Return_Status => l_return_status,
1477 X_Msg_Count => l_msg_count,
1478 X_Msg_Data => l_msg_data) ;
1479
1480 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1481 RAISE l_budget_processing_failure ;
1482 END IF ;
1483
1484 -- ## 2.2 Call Establish_bc_balances
1485 log_message('Calling Establish_Bc_Balances');
1486
1487 ESTABLISH_BC_BALANCES(
1488 p_project_id => p_project_id,
1489 p_draft_budget_version_id => l_draft_budget_version_id,
1490 p_base_budget_version_id => p_budget_version_id,
1491 p_bdgt_intg_flag => 'Y',
1492 x_return_status => l_return_status,
1493 x_error_message_code => l_msg_data);
1494
1495 g_procedure_name := 'check_or_reserve_funds';
1496
1497 log_message('After Establish_Bc_Balances,x_return_status:'||x_return_status);
1498 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1499 RAISE l_budget_processing_failure ;
1500 END IF;
1501
1502 -- ## 2.3 Call PA FCK ..
1503 log_message('Call PA Funds Check');
1504
1505 pa_bgt_baseline_pkg.maintain_bal_fchk(
1506 P_PROJECT_ID => p_project_id,
1507 P_BUDGET_VERSION_ID => l_draft_budget_version_id,
1508 P_BASELINED_BUDGET_VERSION_ID => p_budget_version_id,
1509 P_BDGT_CTRL_TYPE => 'GL' ,
1510 P_CALLING_MODE => p_calling_mode,
1511 P_BDGT_INTG_FLAG => 'Y',
1512 X_RETURN_STATUS => l_return_status,
1513 X_ERROR_MESSAGE_CODE => l_msg_data) ;
1514
1515 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1516 RAISE l_budget_processing_failure ;
1517 END IF ;
1518
1519 --End If; -- re-baseline check ..
1520
1521 -- ## 2.4 Call BC Funds check API ..
1522 log_message('Calling CREATE_EVENTS_AND_FUNDSCHECK');
1523
1524 CREATE_EVENTS_AND_FUNDSCHECK
1525 (P_calling_module => 'Cost_Budget',
1526 P_mode => 'Reserve_Baseline',
1527 P_External_Budget_Code => 'GL',
1528 P_budget_version_id => P_Budget_version_id,
1529 P_cc_budget_version_id => NULL,
1530 P_result_code => l_return_status);
1531
1532 g_procedure_name := 'check_or_reserve_funds';
1533 log_message('After call to CREATE_EVENTS_AND_FUNDSCHECK,l_return_status['||l_return_status
1534 ||']');
1535
1536 IF l_return_status = 'E' THEN
1537 l_msg_data := g_msg_data;
1538 RAISE l_top_down_bdgt_failed;
1539 END IF;
1540
1541 -- Update pa_bc_packets.status_code = 'A' .. as the last step ..
1542 Update_bc_packets_pass(p_bud_ver_id => p_budget_version_id);
1543 -- Bug 5206537 : Procedure to stamp latest budget version id and budget line id on CDL
1544 PA_FUNDS_CONTROL_UTILS.Update_bvid_blid_on_cdl_bccom ( p_bud_ver_id => p_budget_version_id,
1545 p_calling_mode => p_calling_mode);
1546 -- ------------------------------------------------------------+
1547 Elsif ( l_cc_budget_type_code is not null ) then -- III
1548
1549 -- Following code needs to be uncommented for CC Integration .
1550 -- Fix the account generator issue by calling gen_Account_all_lines
1551
1552 -- Overried g_external_link, used in pa_funds_control_pkg (tieback code)
1553 g_external_link := 'DUAL';
1554
1555 -- ## 3 Dual Integration
1556 -- ## 3.1 Check if CC Budget exists
1557 -- ## 3.2 Check if CC and GL, total budget amounts match
1558 -- ## 3.3 Call PA FC for GL Budget
1559 -- ## 3.4 Get CC Draft version
1560 -- ## 3.5 Build budgetary control for CC Budget
1561 -- ## 3.6 Call PA FC for CC Budget
1562 -- ## 3.7 Call Acount generator for GL Budget
1563 -- ## 3.8 Call Account generator for CC Budget
1564 -- ## 3.9 Call BC FC
1565
1566 Begin
1567 -- ## 3.1 Check if CC Budget exists
1568
1569 select budget_version_id
1570 into g_cc_current_bvid
1571 from pa_budget_versions
1572 where project_id = p_project_id
1573 and budget_type_code = l_cc_budget_type_code
1574 and budget_status_code = 'B'
1575 and current_flag ='Y';
1576
1577 l_cc_budget_version_id := g_cc_current_bvid;
1578 Exception
1579 When no_data_found then
1580 l_msg_data := 'PA_BC_CC_BDGT_ID_ERR';
1581 RAISE l_budget_processing_failure;
1582 End;
1583
1584 /*Commented for Bug 6524116
1585 If g_cost_rebaseline_flag ='Y' then
1586
1587 g_cc_prev_bvid :=GET_PREVIOUS_BVID(p_project_id => p_project_id,
1588 p_budget_type_code => l_cc_budget_type_code,
1589 p_curr_budget_status_code => 'B');
1590 End IF;
1591 */
1592
1593 -- ## 3.2 Check if CC and GL, total budget amounts match
1594 --------------------------------------------------------------+
1595 -- Get the total budgeted amounts for CC and GL budgets
1596 --------------------------------------------------------------+
1597
1598 log_message('Opening Cursor c_budget_sum_amt with cc version id') ;
1599
1600 OPEN c_budget_sum_amt(g_cc_current_bvid) ;
1601 FETCH c_budget_sum_amt INTO l_total_cc_bdgt_amount ;
1602 CLOSE c_budget_sum_amt ;
1603
1604 log_message('Opening Cursor c_budget_sum_amt with p_budget version id') ;
1605
1606 OPEN c_budget_sum_amt(g_cost_current_bvid) ;
1607 FETCH c_budget_sum_amt INTO l_total_gl_bdgt_amount ;
1608 CLOSE c_budget_sum_amt ;
1609
1610 --------------------------------------------------------------+
1611 -- Total Budget Amounts of CC and GL Budgets Should match.
1612 --------------------------------------------------------------+
1613
1614 log_message('l_total_cc_bdgt_amount '||l_total_cc_bdgt_amount);
1615 log_message('l_total_gl_bdgt_amount '||l_total_gl_bdgt_amount);
1616
1617 IF ( l_total_cc_bdgt_amount <> l_total_gl_bdgt_amount ) THEN
1618 l_msg_data := 'PA_BC_CC_GL_AMT_NOT_EQL';
1619 RAISE l_budget_processing_failure ;
1620 END IF ;
1621
1622 --START Bug 6524116
1623 log_message('Call Budget Account generator');
1624
1625 PA_BUDGET_ACCOUNT_PKG.Gen_Acct_All_Lines (
1626 P_Budget_Version_ID => p_budget_version_id,
1627 P_Calling_Mode => 'BASELINE' ,
1628 P_Budget_Type_Code => l_budget_type_code,
1629 P_Budget_Entry_Level_Code => l_budget_entry_level_code,
1630 P_Project_ID => p_project_id,
1631 X_Return_Status => l_return_status,
1632 X_Msg_Count => l_msg_count,
1633 X_Msg_Data => l_msg_data) ;
1634
1635 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1636 RAISE l_budget_processing_failure ;
1637 END IF ;
1638
1639 log_message('Calling Establish_Bc_Balances');
1640
1641 ESTABLISH_BC_BALANCES(
1642 p_project_id => p_project_id,
1643 p_draft_budget_version_id => l_draft_budget_version_id,
1644 p_base_budget_version_id => p_budget_version_id,
1645 p_bdgt_intg_flag => 'Y',
1646 x_return_status => l_return_status,
1647 x_error_message_code => l_msg_data);
1648
1649 g_procedure_name := 'check_or_reserve_funds';
1650
1651 log_message('After Establish_Bc_Balances,x_return_status:'||x_return_status);
1652 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1653 RAISE l_budget_processing_failure ;
1654 END IF;
1655 --END Bug 6524116
1656
1657 -- ------------------------------------------------------------+
1658 -- ## 3.3 Call PA FC for GL Budget
1659 -- ------------------------------------------------------------+
1660 --If g_cost_rebaseline_flag = 'Y' then
1661 log_message('Call PA Funds Check for GL budget');
1662
1663 pa_bgt_baseline_pkg.maintain_bal_fchk(
1664 P_PROJECT_ID => p_project_id,
1665 P_BUDGET_VERSION_ID => l_draft_budget_version_id,
1666 P_BASELINED_BUDGET_VERSION_ID => p_budget_version_id,
1667 P_BDGT_CTRL_TYPE => 'GL' ,
1668 P_CALLING_MODE => p_calling_mode,
1669 P_BDGT_INTG_FLAG => 'Y',
1670 X_RETURN_STATUS => l_return_status,
1671 X_ERROR_MESSAGE_CODE => l_msg_data) ;
1672
1673 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1674 RAISE l_budget_processing_failure ;
1675 END IF ;
1676
1677 --End IF; -- rebaseline
1678
1679 -- ------------------------------------------------------------+
1680 -- ## 3.9 Call BC FC
1681 -- ------------------------------------------------------------+
1682 log_message('Call BC FC API');
1683
1684 CREATE_EVENTS_AND_FUNDSCHECK
1685 (P_calling_module => 'Dual_Budget',
1686 P_mode => 'Reserve_Baseline',
1687 P_External_Budget_Code => 'Dual',
1688 P_budget_version_id => P_Budget_version_id,
1689 P_cc_budget_version_id => l_cc_budget_version_id,
1690 P_result_code => l_return_status);
1691
1692 IF l_return_status = 'E' THEN
1693 l_msg_data := g_msg_data;
1694 RAISE l_dual_bc_fc_failed;
1695
1696 ELSE
1697 x_cc_budget_version_id := l_cc_budget_version_id;
1698 x_dual_bdgt_cntrl_flag := 'Y';
1699
1700 END IF;
1701
1702 -- ------------------------------------------------------------+
1703 -- ## 3.4 Get CC Draft version
1704 -- ------------------------------------------------------------+
1705
1706 log_message(' Derive CC Draft version');
1707
1708 Begin
1709
1710 Select pbv.budget_version_id, budget_entry_method_code
1711 into l_cc_budget_version_id, l_cc_budget_entry_level_code
1712 from pa_budget_versions pbv
1713 where pbv.project_id = p_project_id
1714 and pbv.budget_type_code = l_cc_budget_type_code
1715 and pbv.budget_status_code = 'B'
1716 and pbv.current_flag = 'Y';
1717
1718
1719 Exception
1720 When no_data_found then
1721 l_msg_data := 'PA_GET_DRAFT_VERSION_FAILED';
1722 RAISE l_top_down_bdgt_failed;
1723 End;
1724
1725 -- ------------------------------------------------------------+
1726 -- ## 3.5 Build budgetary control for CC Budget
1727 -- ------------------------------------------------------------+
1728 log_message('Build budgetary control for CC Budget');
1729
1730 pa_budgetary_controls_pkg.bud_ctrl_create
1731 (x_budget_version_id => l_cc_budget_version_id,
1732 x_calling_mode => 'BASELINE',
1733 X_Return_Status => l_return_status,
1734 X_Msg_Count => l_msg_count,
1735 X_Msg_Data => l_msg_data) ;
1736
1737 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1738
1739 RAISE l_top_down_bdgt_failed;
1740
1741 END IF ;
1742
1743 --START BUG 6524116
1744 log_message('Call Budget Account generator');
1745
1746 PA_BUDGET_ACCOUNT_PKG.Gen_Acct_All_Lines (
1747 P_Budget_Version_ID => l_cc_budget_version_id,
1748 P_Calling_Mode => 'BASELINE' ,
1749 P_Budget_Type_Code => l_cc_budget_type_code,
1750 P_Budget_Entry_Level_Code => l_cc_budget_entry_level_code,
1751 P_Project_ID => p_project_id,
1752 X_Return_Status => l_return_status,
1753 X_Msg_Count => l_msg_count,
1754 X_Msg_Data => l_msg_data) ;
1755
1756 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1757 RAISE l_budget_processing_failure ;
1758 END IF ;
1759
1760 log_message('Calling Establish_Bc_Balances');
1761
1762 ESTABLISH_BC_BALANCES(
1763 p_project_id => p_project_id,
1764 p_draft_budget_version_id => l_cc_budget_version_id,
1765 p_base_budget_version_id => g_cc_current_bvid,
1766 p_bdgt_intg_flag => 'Y',
1767 x_return_status => l_return_status,
1768 x_error_message_code => l_msg_data);
1769
1770 g_procedure_name := 'check_or_reserve_funds';
1771
1772 log_message('After Establish_Bc_Balances,x_return_status:'||x_return_status);
1773 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1774 RAISE l_budget_processing_failure ;
1775 END IF;
1776 --END BUG 6524116
1777
1778 -- ------------------------------------------------------------+
1779 -- ## 3.6 Call PA FC for CC Budget
1780 -- ------------------------------------------------------------+
1781 log_message('Call PA Funds Check for CC budget');
1782
1783 --If g_cc_rebaseline_flag = 'Y' then
1784
1785 pa_bgt_baseline_pkg.maintain_bal_fchk(
1786 P_PROJECT_ID => p_project_id,
1787 P_BUDGET_VERSION_ID => l_cc_budget_version_id,
1788 P_BASELINED_BUDGET_VERSION_ID => g_cc_current_bvid,
1789 P_BDGT_CTRL_TYPE => 'CC' ,
1790 P_CALLING_MODE => p_calling_mode,
1791 P_BDGT_INTG_FLAG => 'Y',
1792 X_RETURN_STATUS => l_return_status,
1793 X_ERROR_MESSAGE_CODE => l_msg_data) ;
1794
1795 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1796
1797 RAISE l_top_down_bdgt_failed;
1798 END IF ;
1799 --End If; -- rebaseline
1800
1801 /* Commented for bug Bug 6524116
1802 -- ------------------------------------------------------------+
1803 -- ## 3.7 Call Acount generator for GL Budget
1804 -- ------------------------------------------------------------+
1805 log_message('Call Budget Account generator for GL budget');
1806
1807 PA_BUDGET_ACCOUNT_PKG.Gen_Account (
1808 P_Budget_Version_ID => l_draft_budget_version_id,
1809 P_Calling_Mode => 'BASELINE' ,
1810 X_Return_Status => l_return_status,
1811 X_Msg_Count => l_msg_count,
1812 X_Msg_Data => l_msg_data) ;
1813
1814 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1815 RAISE l_dual_mode_acct_gen_failed ;
1816 END IF ;
1817
1818 -- ------------------------------------------------------------+
1819 -- ## 3.8 Call Account generator for CC Budget
1820 -- ------------------------------------------------------------+
1821 log_message('Call Budget Account generator for CC budget');
1822
1823 PA_BUDGET_ACCOUNT_PKG.Gen_Account (
1824 P_Budget_Version_ID => l_cc_budget_version_id,
1825 P_Calling_Mode => 'BASELINE' ,
1826 X_Return_Status => l_return_status,
1827 X_Msg_Count => l_msg_count,
1828 X_Msg_Data => l_msg_data) ;
1829
1830 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1831 RAISE l_dual_mode_acct_gen_failed ;
1832 END IF ;
1833 */
1834
1835 --Start Bug 6524116
1836 CC_FUNDS_CHK_RSRV(
1837 P_ENC_TYPE_ID => l_Encumbrance_Type_Id,
1838 P_BUDGET_VERSION_ID => l_cc_Budget_version_id,
1839 p_calling_mode => 'RESERVE' ,
1840 p_Balance_Type => l_Balance_type,
1841 x_funds_chk_rsrv_status => l_funds_chk_rsrv_status,
1842 x_return_status => l_return_status,
1843 x_msg_count => l_msg_count,
1844 x_msg_data =>l_msg_data ) ;
1845
1846 IF nvl(l_return_status, 'E') <> 'S' then
1847 --Collect all event_ids
1848 if g_cost_rebaseline_flag = 'Y' then
1849 P_prev_budget_version_id := GET_PREVIOUS_BVID(p_project_id => p_project_id,
1850 p_budget_type_code => l_budget_type_code,
1851 p_curr_budget_status_code => 'B');
1852 open get_rejected_event_id;
1853 fetch get_rejected_event_id bulk collect into rejected_event_id_tab, ledger_id_tab;
1854 close get_rejected_event_id;
1855 else
1856 select bc_event_id, g_org_id
1857 bulk collect into rejected_event_id_tab, ledger_id_tab
1858 from pa_budget_lines bl
1859 where budget_version_id = P_Budget_version_id
1860 and bc_event_id is not null;
1861 end if;
1862 /*PSA_FUNDS_CHECKER_PKG.sync_xla_errors(p_failed_ldgr_array => ledger_id_tab,
1863 p_failed_evnt_array => rejected_event_id_tab);*/
1864 RAISE l_dual_bc_fc_failed;
1865 END if;
1866 --END Bug 6524116
1867
1868 -- As the last step, update pa_bc to pass
1869 Update_bc_packets_pass(p_bud_ver_id => p_budget_version_id);
1870 Update_bc_packets_pass(p_bud_ver_id =>l_cc_budget_version_id);
1871 -- Bug 5206537 : Procedure to stamp latest budget version id and budget line id on CDL
1872 PA_FUNDS_CONTROL_UTILS.Update_bvid_blid_on_cdl_bccom ( p_bud_ver_id => p_budget_version_id,
1873 p_calling_mode => p_calling_mode);
1874
1875 End If; -- III (Top Down: GL or Dual check)
1876
1877
1878 end if; /* top-down and bottom-up check if */ -- II
1879
1880 END IF; /* calling mode if */ -- I
1881
1882 -- ------------------------------------------------------------+
1883 -- 9.0 : Calling Sweeper
1884 -- ------------------------------------------------------------+
1885 -- Sweeper process need to be called after the tieback api.
1886 -- The success/failure of sweeper process should not stop the
1887 -- baseline process.
1888
1889 IF (p_calling_mode = 'RESERVE_BASELINE' and g_cost_rebaseline_flag = 'Y' ) then
1890
1891 log_message('Calling Sweeper ..');
1892
1893 Begin
1894 l_request_id := pa_funds_control_utils.runsweeper ;
1895 Exception
1896 When others then
1897 null;
1898 End;
1899
1900 log_message('After Calling Sweeper,request:'||l_request_id);
1901
1902 End If;
1903
1904 -- ------------------------------------------------------------+
1905 -- 10.0 : Release BC locks
1906 -- ------------------------------------------------------------+
1907 IF p_calling_mode = 'RESERVE_BASELINE' then
1908
1909 log_message('Calling release lock ..');
1910
1911 release_bc_lock (p_project_id => p_project_id,
1912 x_return_status => x_return_status,
1913 x_msg_count => x_msg_count,
1914 x_msg_data => x_msg_data);
1915
1916 log_message('After calling release lock ..');
1917
1918 END IF;
1919
1920 -- ------------------------------------------------------------+
1921 -- 11.0 : Pass warning message if unburdened CDL's exist ..
1922 -- ------------------------------------------------------------+
1923 -- Following code was added to check whether all CDL's have been
1924 -- burdened. If not, the actual cost can decrease as burden is not
1925 -- accounted ...
1926
1927 --IF (p_calling_mode = 'RESERVE_BASELINE' and
1928 -- g_cost_rebaseline_flag = 'Y' and
1929 -- (l_External_budget_code is null OR l_balance_type = 'E' )
1930 -- ) then
1931
1932 -- IF Unburdened_cdl_exists(X_project_id => p_project_id) then
1933 -- l_msg_data := 'PA_UNBURDENED_CDL_EXISTS';
1934 -- PA_UTILS.Add_Message('PA', l_msg_data);
1935 -- x_msg_data := l_msg_data;
1936 -- x_msg_count := 1;
1937
1938 -- END IF;
1939
1940 --END IF;
1941
1942 -- Unburdened_cdl_exists will be called from PAXBUEBU.fmb ..
1943
1944 -- ------------------------------------------------------------+
1945
1946
1947 -- ## RETURN STATUS .. PASSED :)
1948
1949 l_return_status := FND_API.G_RET_STS_SUCCESS;
1950 x_return_status := l_return_status;
1951
1952 log_message('exiting check_reserve_funds .........') ;
1953 log_message('l_return_status.........'||l_return_status) ;
1954
1955 EXCEPTION
1956
1957 WHEN l_cbc_not_supported THEN
1958
1959 g_procedure_name := 'check_or_reserve_funds';
1960 log_message(' Exception: l_cbc_not_supported');
1961
1962 x_return_status := FND_API.G_RET_STS_ERROR;
1963
1964 PA_UTILS.Add_Message('PA', l_msg_data);
1965 x_msg_data := l_msg_data;
1966 x_msg_count := 1;
1967
1968 WHEN l_top_down_bdgt_failed THEN
1969
1970 g_procedure_name := 'check_or_reserve_funds';
1971 log_message(' Exception: l_top_down_bdgt_failed');
1972
1973 IF p_calling_mode = 'RESERVE_BASELINE' then
1974
1975
1976 Update_bc_packets_fail(p_bud_ver_id => l_draft_budget_version_id,
1977 p_status_code=> 'R');
1978
1979 release_bc_lock (p_project_id => p_project_id,
1980 x_return_status => x_return_status,
1981 x_msg_count => x_msg_count,
1982 x_msg_data => x_msg_data);
1983 END IF;
1984
1985 -- This should be set at the end .. do not change this position ..
1986 x_return_status := FND_API.G_RET_STS_ERROR;
1987
1988 PA_UTILS.Add_Message('PA', l_msg_data);
1989 x_msg_data := l_msg_data;
1990 x_msg_count := 3; -- made to 3 so that it can read all message buffers
1991 -- if you dont have enough messages then it does not error out
1992
1993 WHEN l_dual_mode_acct_gen_failed OR l_dual_bc_fc_failed THEN
1994
1995 g_procedure_name := 'check_or_reserve_funds';
1996 log_message(' Exception: l_dual_mode_acct_gen_failed OR l_dual_bc_fc_failed');
1997
1998 IF p_calling_mode = 'RESERVE_BASELINE' then
1999
2000 release_bc_lock (p_project_id => p_project_id,
2001 x_return_status => x_return_status,
2002 x_msg_count => x_msg_count,
2003 x_msg_data => x_msg_data);
2004
2005 -- Fail pa_bc_packets for Cost and Commitment budget ..
2006 -- Fail pa_bc_packets for Cost budget ..
2007 Update_bc_packets_fail(p_bud_ver_id => l_draft_budget_version_id,
2008 p_status_code => 'R');
2009 Update_bc_packets_fail(p_bud_ver_id =>l_cc_budget_version_id,
2010 p_status_code=>'R');
2011 END IF;
2012
2013 -- This should be set at the end .. do not change this position ..
2014 x_return_status := FND_API.G_RET_STS_ERROR;
2015
2016 PA_UTILS.Add_Message('PA', l_msg_data);
2017 x_msg_data := l_msg_data;
2018 x_msg_count := 3; -- made to 3 so that it can read all message buffers
2019 -- if you dont have enough messages then it does not error out
2020
2021 WHEN l_budget_processing_failure THEN
2022
2023 g_procedure_name := 'check_or_reserve_funds';
2024 log_message(' Exception: l_budget_processing_failure');
2025
2026 IF p_calling_mode = 'RESERVE_BASELINE' then
2027
2028 release_bc_lock (p_project_id => p_project_id,
2029 x_return_status => x_return_status,
2030 x_msg_count => x_msg_count,
2031 x_msg_data => x_msg_data);
2032 END IF;
2033
2034 -- This should be set at the end .. do not change this position ..
2035 x_return_status := FND_API.G_RET_STS_ERROR;
2036
2037 PA_UTILS.Add_Message('PA', l_msg_data);
2038 x_msg_data := l_msg_data;
2039 x_msg_count := 3; -- made to 3 so that it can read all message buffers
2040 -- if you dont have enough messages then it does not error out
2041
2042 WHEN OTHERS THEN
2043
2044 g_procedure_name := 'check_or_reserve_funds';
2045 log_message(' Exception: WHEN OTHERS');
2046
2047 IF p_calling_mode = 'RESERVE_BASELINE' then
2048
2049 release_bc_lock (p_project_id => p_project_id,
2050 x_return_status => x_return_status,
2051 x_msg_count => x_msg_count,
2052 x_msg_data => x_msg_data);
2053
2054 Update_bc_packets_fail(p_bud_ver_id => l_draft_budget_version_id,
2055 p_status_code => 'T');
2056
2057 If l_cc_budget_version_id is not null then
2058 Update_bc_packets_fail(p_bud_ver_id =>l_cc_budget_version_id,
2059 p_status_code=>'T');
2060 End If;
2061
2062 END IF;
2063
2064 -- This should be set at the end .. do not change this position ..
2065 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2066
2067 -- Following code was modified to enhance error handling
2068 -- If there is an exception raised, g_msg_data may be populated
2069 -- (like in create_events_and_fundscheck)
2070 -- Else, we get SQLERRM. When there is an event creation error, the
2071 -- SQLERRM length is more than 240 and the message has lot of data that users
2072 -- would like to get.
2073
2074 x_msg_count := 3;
2075
2076 If g_msg_data is not null then
2077 x_msg_data := g_msg_data;
2078 PA_UTILS.Add_Message('PA',x_msg_data);
2079
2080 log_message('When Others:x_msg_data:'||x_msg_data);
2081
2082 Else -- SQLERRM
2083
2084 l_sqlerrm := SQLERRM;
2085 x_msg_data := substr(SQLERRM,1,240);
2086
2087 select length(l_sqlerrm) into l_dummy from dual;
2088
2089 If l_dummy > 0 then
2090 ADD_MESSAGE(substr(SQLERRM,1,200)||'...');
2091 log_message('When Others:'||substr(SQLERRM,1,200));
2092 End If;
2093 If l_dummy > 201 then
2094 ADD_MESSAGE(substr(SQLERRM,201,400));
2095 log_message('When Others:'||substr(SQLERRM,201,400));
2096 End If;
2097
2098 End If;
2099
2100 END check_or_reserve_funds;
2101
2102 --
2103 -- Procedure : get_budget_ctrl_options
2104 -- Purpose : To get Budget Control Options for given project id and
2105 -- calling mode.
2106 --Parameters :
2107 -- p_calling_code : STANDARD/COMMITMENT/BUDGET
2108 -- x_fck_req_flag --> Y (Yes), N (No)
2109 -- x_bdgt_intg_flag --> G (GL), ,C ( CC ), N (No)
2110
2111 -- sqlplus apps/apps@padev115 @PABBFNDB.pls
2112
2113 PROCEDURE get_budget_ctrl_options ( p_project_id IN NUMBER,
2114 p_budget_type_code IN VARCHAR2,
2115 p_calling_mode IN VARCHAR2,
2116 x_fck_req_flag OUT NOCOPY VARCHAR2,
2117 x_bdgt_intg_flag OUT NOCOPY VARCHAR2,
2118 x_bdgt_ver_id OUT NOCOPY NUMBER,
2119 x_encum_type_id OUT NOCOPY NUMBER,
2120 x_balance_type OUT NOCOPY VARCHAR2,
2121 x_return_status OUT NOCOPY VARCHAR2,
2122 x_msg_count OUT NOCOPY NUMBER,
2123 x_msg_data OUT NOCOPY VARCHAR2 )
2124 IS
2125
2126 CURSOR C_BUDGET_CONTROL IS
2127 SELECT budget_type_code,
2128 encumbrance_type_id,
2129 external_budget_code,
2130 balance_type,
2131 bdgt_cntrl_flag
2132 FROM PA_BUDGETARY_CONTROL_OPTIONS
2133 WHERE project_id = p_project_id
2134 AND ( ( p_calling_mode = 'STANDARD' AND
2135 ( nvl(external_budget_code,'-1') IN ('GL','-1') ) )
2136 OR
2137 ( p_calling_mode = 'COMMITMENT' AND
2138 ( nvl(external_budget_code,'-1') = 'CC') )
2139 OR
2140 ( p_calling_mode = 'BUDGET' )
2141 )
2142 AND ( ( p_calling_mode = 'BUDGET' AND
2143 ( nvl(budget_type_code,'-1') = p_budget_type_code ) )
2144 OR
2145 ( p_calling_mode IN ('COMMITMENT','STANDARD') )
2146 )
2147 AND (
2148 ( p_calling_mode = 'STANDARD'
2149 AND ( ( nvl(balance_type,'-1') = 'E' AND
2150 nvl(external_budget_code,'-1') = 'GL' )
2151 OR ( nvl(balance_type,'-1') = '-1' AND
2152 nvl(external_budget_code,'-1') = '-1' ))
2153 )
2154 OR
2155 ( p_calling_mode = 'COMMITMENT' AND
2156 nvl(balance_type,'-1') = 'E'
2157 )
2158 OR
2159 ( p_calling_mode = 'BUDGET' ) );
2160
2161 CURSOR C_BUDGET_VERSION ( c_budget_type_code VARCHAR ) IS
2162 SELECT budget_version_id
2163 FROM PA_BUDGET_VERSIONS
2164 WHERE project_id = p_project_id
2165 AND budget_type_code = c_budget_type_code
2166 AND budget_status_code = 'B'
2167 AND version_number = (Select MAX(version_number)
2168 FROM PA_BUDGET_VERSIONS
2169 WHERE project_id = p_project_id
2170 AND budget_type_code = c_budget_type_code
2171 AND budget_status_code = 'B' );
2172
2173 l_lock_name VARCHAR2(200);
2174 l_acquire_lock NUMBER;
2175 l_release_lock NUMBER;
2176
2177
2178 l_encumbrance_type_id NUMBER ;
2179
2180 l_budget_type_code pa_budget_versions.budget_type_code%TYPE ;
2181 l_balance_type pa_budgetary_control_options.balance_type%TYPE ;
2182 l_bdgt_cntrl_flag pa_budgetary_control_options.bdgt_cntrl_flag%TYPE ;
2183 l_external_budget_code pa_budgetary_control_options.external_budget_code%TYPE ;
2184 l_budget_version_id pa_budget_versions.budget_version_id%TYPE ;
2185 l_msg_index_out NUMBER;
2186
2187 l_rel_status NUMBER;
2188 BEGIN
2189
2190 x_return_status := FND_API.G_RET_STS_SUCCESS;
2191
2192 OPEN C_BUDGET_CONTROL ;
2193
2194 FETCH C_BUDGET_CONTROL INTO
2195 l_budget_type_code,
2196 l_encumbrance_type_id ,
2197 l_external_budget_code,
2198 l_balance_type,
2199 l_bdgt_cntrl_flag;
2200
2201 IF C_BUDGET_CONTROL%NOTFOUND THEN
2202 --dbms_output.put_line('record not found ');
2203 x_fck_req_flag := 'N' ;
2204 x_bdgt_intg_flag := 'N' ;
2205 x_bdgt_ver_id := NULL;
2206 x_encum_type_id := NULL ;
2207 x_balance_type := NULL ;
2208
2209 -- Add X_msg_data and x_msg count
2210
2211 ELSE
2212 --dbms_output.put_line('record found ');
2213
2214 --ELSIF C_BUDGET_CONTROL%FOUND THEN
2215 x_balance_type := l_balance_type ;
2216 IF (p_calling_mode = 'BUDGET') THEN
2217
2218 /* *****
2219 -- to support this the changes to
2220 -- the baseline API are huge.
2221 -- This will be revisited after testing.
2222 if l_bdgt_cntrl_flag = 'Y' then
2223 x_fck_req_flag := 'Y';
2224 else
2225 x_fck_req_flag := 'N';
2226 end if;
2227 *** */
2228
2229 x_fck_req_flag := 'Y';
2230
2231 if ( nvl(x_balance_type,'X') = 'B' ) then
2232 x_bdgt_intg_flag := 'G';
2233 else
2234 SELECT decode(nvl(l_external_budget_code,'X'),'GL','G','CC','C','N')
2235 into x_bdgt_intg_flag from dual ;
2236 end if;
2237
2238 ELSE
2239 OPEN C_BUDGET_VERSION( l_budget_type_code ) ;
2240 FETCH C_BUDGET_VERSION INTO l_budget_version_id ;
2241 IF C_BUDGET_VERSION%NOTFOUND THEN
2242 -- then error messages
2243 x_fck_req_flag := NULL ;
2244 x_bdgt_intg_flag := NULL ;
2245 x_bdgt_ver_id := NULL;
2246 x_encum_type_id := NULL ;
2247 x_balance_type := NULL ;
2248
2249 -- Add X_msg_data and x_msg count
2250
2251 ELSIF C_BUDGET_VERSION%FOUND THEN
2252 IF l_external_budget_code is not null THEN
2253 SELECT decode(l_external_budget_code,'GL','G','CC','C','N') into
2254 x_bdgt_intg_flag from dual ;
2255 x_encum_type_id := l_encumbrance_type_id ;
2256 ELSE
2257 x_bdgt_intg_flag := 'N' ;
2258 x_encum_type_id := NULL ;
2259 END IF ;
2260 x_fck_req_flag := 'Y' ;
2261 x_bdgt_ver_id := l_budget_version_id ;
2262 -- SET the return status
2263 END IF ;
2264 CLOSE C_BUDGET_VERSION ;
2265 END IF ;
2266 END IF ;
2267 CLOSE C_BUDGET_CONTROL ;
2268
2269 BEGIN
2270 IF X_Bdgt_Intg_Flag IN ('G','C')
2271 THEN
2272 /* Commenting for Bug 5726535
2273 l_lock_name := 'YRENDRLVR:'||P_Project_ID||':'||P_Budget_Type_Code;
2274 -- Check whether the Budget is locked
2275 IF NOT Is_Budget_Locked ( l_Lock_Name )
2276 */
2277 IF PA_Year_End_Rollover_PKG.Is_Yr_End_Rollover_Running(P_Project_ID, l_budget_type_code) /* Bug 5726535 */
2278 THEN
2279 X_Fck_Req_Flag := 'R'; -- Indicates that Year End Rollover is in progress
2280 X_Bdgt_Intg_Flag := 'R'; -- Indicates that Year End Rollover is in progress
2281 END IF;
2282 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
2283 END IF;
2284 END;
2285
2286 EXCEPTION
2287 WHEN OTHERS THEN
2288 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2289 x_msg_count := 1;
2290 x_msg_data := substr(SQLERRM,1,240);
2291 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_BUDGET_FUND_PKG',
2292 p_procedure_name => 'get_budget_ctrl_options');
2293
2294 END get_budget_ctrl_options;
2295
2296 --
2297 -- Procedure : upd_bdgt_acct_bal
2298 -- Purpose : Update the account level balances for given account, period
2299 -- and budget version id.
2300 --Parameters :
2301 -- p_amount
2302 -- +ve amount : means amount is send for liquidation.
2303 -- -ve amount : means amount is send for reservation.
2304 -- process : Update the available balance field with :
2305 -- available_balance - p_amount.
2306 --
2307
2308 PROCEDURE upd_bdgt_acct_bal ( p_gl_period_name IN VARCHAR2,
2309 p_budget_version_id IN NUMBER,
2310 p_ccid IN NUMBER,
2311 p_amount IN NUMBER,
2312 x_return_status OUT NOCOPY VARCHAR2,
2313 x_msg_count OUT NOCOPY NUMBER,
2314 x_msg_data OUT NOCOPY VARCHAR2 )
2315 IS
2316 l_msg_index_out NUMBER;
2317 BEGIN
2318
2319 x_return_status := FND_API.G_RET_STS_SUCCESS;
2320
2321 UPDATE PA_BUDGET_ACCT_LINES
2322 SET curr_ver_available_amount = curr_ver_available_amount - p_amount
2323 WHERE budget_version_id = p_budget_version_id
2324 AND gl_period_name = p_gl_period_name
2325 AND code_combination_id = p_ccid ;
2326
2327 EXCEPTION
2328 WHEN OTHERS THEN
2329
2330 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2331 x_msg_count := 1;
2332 x_msg_data := substr(SQLERRM,1,240);
2333 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_BUDGET_FUND_PKG',
2334 p_procedure_name => 'upd_bdgt_acct_bal');
2335 raise;
2336
2337 END upd_bdgt_acct_bal;
2338
2339 --
2340 -- Procedure : upd_bdgt_acct_bal_no_fck
2341 -- Purpose : Update the amount available column in pa_budget_acct_lines
2342 -- table during budget baselining process. This is called when
2343 -- funds check is not required during baselining process.
2344 -- The projects funds check process deternimes this by comparing
2345 -- current budget's budget lines with the previous budget's budget
2346 -- lines. In this case only amounts have changed.
2347 -- Apply the following formula :
2348 -- CA = ( CB - PB ) + PA
2349 --
2350 -- CA : Current Available Amount
2351 -- CB : Current Budget Amount
2352 -- PB : Previous Budget Amount
2353 -- PA : Previous Available Amount
2354 --Parameters :
2355 -- p_amount
2356 -- +ve amount : means amount is send for liquidation.
2357 -- -ve amount : means amount is send for reservation.
2358 -- process : Update the available balance field with :
2359 -- available_balance - p_amount.
2360 --
2361
2362 PROCEDURE upd_bdgt_acct_bal_no_fck ( p_budget_version_id IN NUMBER,
2363 x_return_status OUT NOCOPY VARCHAR2,
2364 x_msg_count OUT NOCOPY NUMBER,
2365 x_msg_data OUT NOCOPY VARCHAR2 )
2366 IS
2367 l_msg_index_out NUMBER;
2368
2369 BEGIN
2370
2371 log_message('Entering upd_bdgt_acct_bal_no_fck ..... ');
2372 x_return_status := FND_API.G_RET_STS_SUCCESS;
2373
2374 log_message('budget_version_id '||to_char(p_budget_version_id));
2375 UPDATE PA_BUDGET_ACCT_LINES
2376 SET curr_ver_available_amount = (curr_ver_budget_amount - prev_ver_budget_amount
2377 + prev_ver_available_amount)
2378 WHERE budget_version_id = p_budget_version_id ;
2379
2380 log_message('Updated '||to_char(sql%rowcount));
2381 log_message('Existing upd_bdgt_acct_bal_no_fck ..... ');
2382
2383 EXCEPTION
2384 WHEN OTHERS THEN
2385 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2386 x_msg_count := 1;
2387 x_msg_data := substr(SQLERRM,1,240);
2388 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_BUDGET_FUND_PKG',
2389 p_procedure_name => 'upd_bdgt_acct_bal_no_fck');
2390 raise;
2391
2392 END upd_bdgt_acct_bal_no_fck;
2393
2394 --
2395 -- Function : Is_bdgt_intg_enabled
2396 -- Purpose : This functions returns a true/false for a given project_id
2397 -- and mode
2398 -- Parameters : P_mode S-> Standard , C -> Commitment , A -> All
2399 -- p_project_id
2400
2401 FUNCTION Is_bdgt_intg_enabled (p_project_id IN NUMBER,
2402 p_mode IN VARCHAR2 )
2403 RETURN BOOLEAN IS
2404
2405 l_msg_index_out NUMBER;
2406 l_ret_value BOOLEAN ;
2407 l_bdgt_enabled VARCHAR2(1) ;
2408
2409 CURSOR c_bdgt_enabled IS
2410 SELECT 'X'
2411 FROM DUAL
2412 WHERE EXISTS
2413 ( SELECT 'x'
2414 FROM PA_BUDGETARY_CONTROL_OPTIONS PBA
2415 WHERE PBA.project_id = p_project_id
2416 AND ( ( p_mode ='A' )
2417 OR
2418 ( p_mode <> 'A' AND
2419 PBA.external_budget_code = decode(p_mode,'S','GL','C','CC','-1'))));
2420
2421 BEGIN
2422
2423 OPEN c_bdgt_enabled ;
2424 FETCH c_bdgt_enabled INTO l_bdgt_enabled ;
2425 IF c_bdgt_enabled%NOTFOUND THEN
2426 l_ret_value := FALSE ;
2427 ELSE
2428 l_ret_value := TRUE ;
2429 END IF;
2430 CLOSE c_bdgt_enabled ;
2431
2432 RETURN l_ret_value ;
2433
2434 END Is_bdgt_intg_enabled ;
2435
2436 --
2437 -- Procedure : copy_budgetary_controls
2438 -- Purpose : This procedure is called from the copy project api.
2439 -- This api will copy budgetary controls from one
2440 -- project to another project.
2441 -- Parameters :
2442
2443
2444 PROCEDURE copy_budgetary_controls (p_from_project_id IN NUMBER,
2445 p_to_project_id IN NUMBER,
2446 x_return_status OUT NOCOPY VARCHAR2,
2447 x_msg_count OUT NOCOPY NUMBER,
2448 x_msg_data OUT NOCOPY VARCHAR2 )
2449 IS
2450 BEGIN
2451
2452 x_return_status := FND_API.G_RET_STS_SUCCESS;
2453
2454 insert into PA_BUDGETARY_CONTROL_OPTIONS
2455 (
2456 PROJECT_TYPE,
2457 PROJECT_ID,
2458 BALANCE_TYPE,
2459 EXTERNAL_BUDGET_CODE,
2460 GL_BUDGET_VERSION_ID,
2461 ENCUMBRANCE_TYPE_ID,
2462 BDGT_CNTRL_FLAG,
2463 AMOUNT_TYPE,
2464 BOUNDARY_CODE,
2465 FUND_CONTROL_LEVEL_PROJECT,
2466 FUND_CONTROL_LEVEL_TASK,
2467 FUND_CONTROL_LEVEL_RES_GRP,
2468 FUND_CONTROL_LEVEL_RES,
2469 BUDGET_TYPE_CODE,
2470 PROJECT_TYPE_ORG_ID ,
2471 LAST_UPDATE_DATE ,
2472 LAST_UPDATED_BY ,
2473 CREATION_DATE ,
2474 CREATED_BY ,
2475 LAST_UPDATE_LOGIN
2476
2477 )
2478 select
2479 PROJECT_TYPE,
2480 p_to_project_id,
2481 BALANCE_TYPE,
2482 EXTERNAL_BUDGET_CODE,
2483 GL_BUDGET_VERSION_ID,
2484 ENCUMBRANCE_TYPE_ID,
2485 BDGT_CNTRL_FLAG,
2486 AMOUNT_TYPE,
2487 BOUNDARY_CODE,
2488 FUND_CONTROL_LEVEL_PROJECT,
2489 FUND_CONTROL_LEVEL_TASK,
2490 FUND_CONTROL_LEVEL_RES_GRP,
2491 FUND_CONTROL_LEVEL_RES,
2492 BUDGET_TYPE_CODE,
2493 PROJECT_TYPE_ORG_ID ,
2494 SYSDATE ,
2495 -1 ,
2496 SYSDATE ,
2497 -1 ,
2498 -1
2499 from PA_BUDGETARY_CONTROL_OPTIONS
2500 where PROJECT_ID = p_from_project_id;
2501
2502 EXCEPTION
2503 WHEN NO_DATA_FOUND THEN
2504 null;
2505 WHEN OTHERS THEN
2506 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2507 x_msg_count := 1;
2508 x_msg_data := substr(SQLERRM,1,240);
2509 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_BUDGET_FUND_PKG',
2510 p_procedure_name => 'copy_budgetary_controls');
2511 raise;
2512 END copy_budgetary_controls;
2513
2514 --
2515 -- Procedure : release_bc_lock
2516 -- Purpose :
2517
2518 -- Parameters :
2519
2520 PROCEDURE release_bc_lock (p_project_id IN NUMBER ,
2521 x_return_status OUT NOCOPY VARCHAR2,
2522 x_msg_count OUT NOCOPY NUMBER,
2523 x_msg_data OUT NOCOPY VARCHAR2 )
2524
2525 IS
2526 l_temp_ret_status NUMBER;
2527 PRAGMA AUTONOMOUS_TRANSACTION;
2528 BEGIN
2529
2530 x_return_status := FND_API.G_RET_STS_SUCCESS;
2531
2532 log_message('entering release_bc_lock ..... ');
2533
2534 l_temp_ret_status := pa_debug.release_user_lock('BSLNFCHKLOCK:'||to_char(p_project_id));
2535
2536 log_message('after release_user_lock ..... ');
2537
2538 commit;
2539 log_message('exiting release_bc_lock ..... ');
2540
2541 EXCEPTION
2542 WHEN OTHERS THEN
2543 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2544 x_msg_count := 1;
2545 x_msg_data := substr(SQLERRM,1,240);
2546 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_BUDGET_FUND_PKG',
2547 p_procedure_name => 'release_bc_lock');
2548 raise;
2549 END release_bc_lock;
2550
2551 --
2552 -- Function : Is_pa_bc_enabled
2553 -- Purpose : This functions returns true if the profile option
2554 -- PA_BC_ENABLED is set as 'Y' otherwise flase.
2555 -- Parameters : None.
2556 --
2557
2558 FUNCTION Is_pa_bc_enabled RETURN BOOLEAN IS
2559
2560 l_return_value VARCHAR(30);
2561 BEGIN
2562
2563 FND_PROFILE.GET('PA_BC_ENABLED',l_return_value );
2564
2565 if ( l_return_value = 'Y' ) then
2566 return TRUE;
2567 else
2568 return FALSE;
2569 end if;
2570
2571 END Is_pa_bc_enabled ;
2572
2573 --
2574 -- Function : Is_Budget_Locked
2575 -- Purpose : This functions returns true if the Budget is locked
2576 -- otherwise false.
2577 -- Parameters : Lock_Name
2578 --
2579
2580 FUNCTION Is_Budget_Locked (
2581 P_Lock_Name IN VARCHAR2
2582 )
2583 RETURN BOOLEAN IS
2584 l_release_lock NUMBER;
2585 PRAGMA AUTONOMOUS_TRANSACTION;
2586
2587 BEGIN
2588 IF PA_Debug.Acquire_User_Lock(P_Lock_Name)=0 -- Acquired the lock successfully
2589 THEN
2590 l_release_lock := PA_Debug.Release_User_Lock(P_Lock_Name); -- Release the lock
2591 RETURN True;
2592 ELSE
2593 RETURN False;
2594 END IF;
2595 END Is_Budget_Locked;
2596
2597 -- ------------------------------------------------------------------------------+
2598 -- ------------------------------------ R12 Start -------------------------------+
2599 -- ------------------------------------------------------------------------------+
2600
2601 -- ---------------------------------------------------------------------------------------------------+
2602 -- This procedure will be called during a recosting scenario to clean up date
2603 -- from pa_bc_packets for the "draft version" (meaning if re-baseline had earlier failed)
2604 -- bug reference for this fix: 5253834
2605 -- ---------------------------------------------------------------------------------------------------+
2606 PROCEDURE Delete_draft_bc_packets(p_draft_bud_ver_id IN NUMBER)
2607 IS
2608 PRAGMA AUTONOMOUS_TRANSACTION;
2609 BEGIN
2610 g_procedure_name := 'Delete_draft_bc_packets';
2611 log_message( 'Before delete');
2612
2613 Delete from pa_bc_packets where budget_version_id = p_draft_bud_ver_id;
2614
2615 log_message( 'After delete');
2616
2617 COMMIT;
2618 END;
2619
2620 -- ----------------------------------------------------------------+
2621 -- ## Following procedure will update pa_bc_packets status
2622 -- ----------------------------------------------------------------+
2623 PROCEDURE Update_bc_packets_pass(p_bud_ver_id IN NUMBER)
2624 IS
2625 BEGIN
2626
2627 g_procedure_name := 'Update_bc_packets_pass';
2628 log_message( 'Before bcpkt update');
2629
2630 Update pa_bc_packets
2631 set status_code = 'A'
2632 where budget_version_id = p_bud_ver_id;
2633
2634 log_message(SQL%ROWCOUNT||' records updated');
2635
2636 END Update_bc_packets_pass;
2637
2638 -- ---------------------------------------------------------------------------------------------------+
2639 -- This procedure will be called in budget baseline fails
2640 -- Draft budget version will be passed to this procedure, reason: the baselined version
2641 -- is updated only if the budget baseline passed (PAFCBALB: pa_bgt_baseline_pkg.maintain_bal_fchk)
2642 -- ---------------------------------------------------------------------------------------------------+
2643 PROCEDURE Update_bc_packets_fail(p_bud_ver_id IN NUMBER, p_status_code IN VARCHAR2)
2644 IS
2645 PRAGMA AUTONOMOUS_TRANSACTION;
2646 BEGIN
2647
2648 g_procedure_name := 'Update_bc_packets_fail';
2649 log_message( 'Before bcpkt update');
2650
2651 Update pa_bc_packets
2652 set status_code = decode(status_code,'R',status_code,p_status_code)
2653 where budget_version_id = p_bud_ver_id;
2654
2655 log_message(SQL%ROWCOUNT||' records updated');
2656
2657 Delete pa_bc_balances
2658 where budget_version_id = p_bud_ver_id;
2659
2660 log_message(SQL%ROWCOUNT||' records deleted');
2661
2662 COMMIT;
2663
2664 END Update_bc_packets_fail;
2665
2666 -- ----------------------------------------------------------------+
2667 -- ## Following procedure will derive the previous budget version
2668 -- ## Note:
2669 -- Before check_or_reserve_funds (which calls this function)
2670 -- is executed, a baselined budget version is established.
2671 -- ----------------------------------------------------------------+
2672 FUNCTION Get_previous_bvid(p_project_id IN NUMBER,
2673 p_budget_type_code IN VARCHAR2,
2674 p_curr_budget_status_code IN VARCHAR2)
2675 return NUMBER
2676 Is
2677 l_prev_budget_version_id pa_budget_versions.budget_version_id%TYPE;
2678 Begin
2679 log_message('In function Get_previous_bvid');
2680
2681 If p_curr_budget_status_code = 'S' then
2682 -- Draft version is used during "check funds", so get the current
2683 -- baselined version for reversal
2684 Select budget_version_id
2685 into l_prev_budget_version_id
2686 from pa_budget_versions
2687 where project_id = p_project_id
2688 and budget_type_code = p_budget_type_code
2689 and budget_status_code = 'B'
2690 and current_flag = 'Y';
2691
2692 ElsIf p_curr_budget_status_code = 'B' then
2693 -- Baselined version is used during "baseline", so get the last
2694 -- baselined version for reversal
2695
2696 Select MAX(budget_version_id)
2697 into l_prev_budget_version_id
2698 from pa_budget_versions
2699 where project_id = p_project_id
2700 and budget_type_code = p_budget_type_code
2701 and budget_status_code = 'B'
2702 and current_flag = 'N';
2703 -- Note: If p_old_budget_version_id is null means first time baseline ..
2704 End If;
2705
2706 log_message('In function Get_previous_bvid, Prev. budget version id is:'||l_prev_budget_version_id );
2707
2708 RETURN l_prev_budget_version_id;
2709
2710 End Get_previous_bvid;
2711
2712 -- -------------------------------------------------------------------------------+
2713 -- PROCEDURE Create_events_and_fundscheck
2714 -- Purpose: This procedure create accounting events and calls BC Funds check
2715 -- API for budget baseline/re-baseline/year-end processing/check funds
2716 -- for budget
2717 -- Parameters and values:
2718 -- p_calling_module - 'Year_End_Rollover' (Year End)/'Cost_Budget'/
2719 -- 'Cmt_Budget'/'Revenue_Budget'/'Dual_Budget'(Budgets)
2720 -- p_mode - 'Reserve_Baseline'/'Check_Baseline'/'Force'(Year-end)
2721 -- p_external_budget_code - 'GL'/'CC'/'Dual'
2722 -- p_budget_version_id - GL Budget version id
2723 -- p_cc_budget_version_id - CC Budget version id
2724 -- p_Result_code - 'S' for success amd 'E' for failure (OUT parameter)
2725 --
2726 -- Called from : check_or_reserve_funds
2727 -- pa_year_end_rollover_pkg.year_end_rollover
2728 -- -------------------------------------------------------------------------------+
2729 PROCEDURE Create_events_and_fundscheck
2730 (P_calling_module IN Varchar2,
2731 P_mode IN Varchar2,
2732 P_External_Budget_Code IN Varchar2,
2733 P_budget_version_id IN Number,
2734 P_cc_budget_version_id IN Number,
2735 P_result_code OUT NOCOPY Varchar2)
2736 IS
2737 l_calling_module VARCHAR2(20);
2738 l_cc_calling_module VARCHAR2(20);
2739 l_data_set_id pa_budget_versions.budget_version_id%TYPE;
2740 l_cc_data_set_id pa_budget_versions.budget_version_id%TYPE;
2741 l_project_id pa_projects_all.project_id%TYPE;
2742 l_budget_type_code pa_budget_versions.Budget_type_code%TYPE;
2743
2744 l_return_status VARCHAR2(1);
2745 l_msg_count NUMBER;
2746 l_msg_data VARCHAR2(4000);
2747 l_result_status_code VARCHAR2(20);
2748 l_bc_mode VARCHAR2(1);
2749
2750 BC_SLA_FAILURE EXCEPTION;
2751
2752 Cursor c_bc_api_error is
2753 Select distinct encoded_msg
2754 from xla_accounting_errors
2755 where event_id in
2756 (select evt.event_id
2757 from xla_events evt,
2758 psa_bc_xla_events_gt tmp
2759 where evt.event_id = tmp.event_id
2760 and evt.process_status_code in ('E','U'));
2761
2762 Cursor c_event_status is
2763 select result_code from psa_bc_xla_events_gt
2764 where result_code in ('FAIL','XLA_ERROR','FATAL','XLA_NO_JOURNAL');
2765
2766 l_dummy number;
2767
2768 Begin
2769
2770 log_message(' In Create_events_and_fundscheck: Start');
2771 log_message(' Parameters:P_calling_module:['||P_calling_module||']P_mode:['
2772 ||P_mode||']P_External_Budget_Code:['||P_External_Budget_Code
2773 ||']P_budget_version_id:['||P_budget_version_id||']');
2774
2775
2776 g_procedure_name := 'Create_events_and_fundscheck';
2777
2778 -- --------------------------------------------------------------+
2779 -- ## 1.0: Set result code ..
2780 -- --------------------------------------------------------------+
2781 p_result_code := 'S';
2782
2783
2784 -- --------------------------------------------------------------+
2785 -- ## 2.0: Set global variables for YEAR-END
2786 -- --------------------------------------------------------------+
2787
2788 -- !!!!!!!!!!! CC NOT HANDLED ... !!!!!!!!!!!!
2789 -- Added this check for the added requirement mentioned in the federal Tracking bug 5686300
2790 -- for baselining cost budget.
2791
2792 if ( P_calling_module <> 'Revenue_Budget' and NVL(FND_PROFILE.value('FV_ENABLED'), 'N') = 'Y') then
2793 null;
2794 else
2795
2796 If P_calling_module = 'Year_End_Rollover' then
2797
2798 If nvl(g_debug_mode,'N') = 'N' then
2799 fnd_profile.get('PA_DEBUG_MODE',g_debug_mode);
2800 g_debug_mode := nvl(g_debug_mode,'N');
2801 End If;
2802
2803 log_message(' Set global variables for Year-End processing');
2804
2805 If p_budget_version_id is not null then
2806
2807 Select PBCO.Balance_type,
2808 PBT.budget_amount_code,
2809 PBV.project_id,
2810 PBV.Budget_type_code
2811 into g_balance_type,
2812 g_budget_amount_code,
2813 l_project_id,
2814 l_budget_type_code
2815 from PA_BUDGETARY_CONTROL_OPTIONS PBCO ,
2816 PA_BUDGET_VERSIONS PBV,
2817 PA_BUDGET_TYPES PBT
2818 where PBV.Budget_version_id = p_budget_version_id
2819 and PBCO.Budget_Type_Code = PBV.Budget_Type_Code
2820 and PBCO.project_id = PBV.project_id
2821 and PBT.Budget_type_code = PBV.Budget_type_code;
2822
2823 End If;
2824
2825 log_message('--------------------------------');
2826 log_message(' YREND:budget_type_code : '||l_budget_type_code );
2827 log_message(' YREND:balance_type : '||g_balance_type );
2828 log_message(' YREND:l_budget_amount_code : '||g_budget_amount_code);
2829 log_message('--------------------------------');
2830
2831 g_cost_rebaseline_flag := PA_FUNDS_CONTROL_UTILS.Is_Budget_Baselined_Before(l_project_id);
2832 g_cost_current_bvid := p_budget_version_id;
2833 g_processing_mode := 'YEAR_END';
2834
2835 log_message(' YREND:g_cost_rebaseline_flag : '||g_cost_rebaseline_flag );
2836 log_message(' YREND:g_cost_current_bvid : '||g_cost_current_bvid);
2837
2838 If g_cost_rebaseline_flag = 'Y' then
2839
2840 g_cost_prev_bvid := GET_PREVIOUS_BVID(p_project_id => l_project_id,
2841 p_budget_type_code => l_budget_type_code,
2842 p_curr_budget_status_code => 'B');
2843 End If;
2844
2845 If P_External_Budget_Code = 'GL' then
2846 g_external_link := 'GL';
2847 Else
2848 g_external_link := 'DUAL';
2849 End If;
2850
2851 log_message(' YREND:g_cost_prev_bvid : '||g_cost_prev_bvid );
2852 log_message(' YREND:g_external_link : '||g_external_link);
2853
2854 End If; -- For Year-End ..
2855
2856
2857 -- --------------------------------------------------------------+
2858 -- ## 3.0: Create encumbrance accounting events ...
2859 -- --------------------------------------------------------------+
2860 log_message('Create encumbrance accounting events');
2861
2862 -- --------------------------------------------------------------+
2863 -- ## 3.1 Derive 'Calling Module'
2864 -- --------------------------------------------------------------+
2865 log_message('Derive Calling Module');
2866
2867 If (P_calling_module = 'Year_End_Rollover' and
2868 P_External_Budget_Code = 'GL'
2869 ) then
2870
2871 l_calling_module := 'COST_BUDGET_YEAR_END';
2872 l_data_set_id := P_budget_version_id;
2873
2874 ElsIf (P_calling_module = 'Year_End_Rollover' and
2875 P_External_Budget_Code = 'CC'
2876 ) then
2877
2878 l_calling_module := 'CC_BUDGET_YEAR_END';
2879 l_data_set_id := P_cc_budget_version_id;
2880
2881 ElsIf (P_calling_module = 'Cost_Budget' and
2882 P_External_Budget_Code = 'GL'
2883 ) then
2884
2885 l_calling_module := 'COST_BUDGET';
2886 l_data_set_id := P_budget_version_id;
2887
2888 ElsIf (P_calling_module = 'Dual_Budget' and
2889 P_External_Budget_Code = 'Dual'
2890 ) then
2891
2892 l_calling_module := 'COST_BUDGET';
2893 l_data_set_id := P_budget_version_id;
2894
2895 l_cc_calling_module := 'CC_BUDGET';
2896 l_cc_data_set_id := P_cc_budget_version_id;
2897
2898 ElsIf (P_calling_module = 'Revenue_Budget' and
2899 P_External_Budget_Code = 'GL'
2900 ) then
2901
2902 l_calling_module := 'REVENUE_BUDGET';
2903 l_data_set_id := P_budget_version_id;
2904
2905 End If;
2906
2907 -- --------------------------------------------------------------+
2908 -- ## 3.2 Call pa_xla_interface_pkg.create_events
2909 -- --------------------------------------------------------------+
2910 declare
2911 MOAC_Current_Org NUMBER;
2912 Begin
2913 MOAC_Current_Org := MO_GLOBAL.GET_CURRENT_ORG_ID;
2914 log_message(' In Create_events_and_fundscheck:Calling create events for GL ');
2915
2916 pa_xla_interface_pkg.create_events
2917 (p_calling_module => l_calling_module,
2918 p_data_set_id => l_data_set_id,
2919 x_result_code => l_result_status_code); /*6647310*/
2920
2921 PA_MOAC_UTILS.set_policy_context('S', MOAC_Current_Org);
2922
2923 If l_result_status_code <> 'Success' then
2924 log_message(' In Create_events_and_fundscheck: Error creating GL events');
2925 g_msg_data := 'PA_XLA_EVENT_CREATION_FAILURE';
2926 RAISE BC_SLA_FAILURE;
2927 End If;
2928
2929 Exception
2930 When others then
2931 log_message(' In Create_events_and_fundscheck: When Others: Error creating GL events');
2932 g_msg_data := SQLERRM;
2933
2934 select length(g_msg_data) into l_dummy from dual;
2935 If l_dummy > 0 then
2936 ADD_MESSAGE(substr(g_msg_data,1,200));
2937 log_message('Create events failed:'||substr(g_msg_data,1,200));
2938 End if;
2939 If l_dummy > 200 then
2940 ADD_MESSAGE(substr(g_msg_data,201,400));
2941 log_message('Create events failed:'||substr(g_msg_data,201,400));
2942 End if;
2943
2944 g_msg_data := 'PA_XLA_EVENT_CREATION_FAILURE';
2945 RAISE BC_SLA_FAILURE;
2946 End;
2947
2948 -- --------------------------------------------------------------+
2949 -- ## 3.3 Call pa_xla_interface_pkg.create_events for CC
2950 -- --------------------------------------------------------------+
2951 /*Commented for bug 6524116
2952 If (l_cc_calling_module IS NOT null and p_result_code = 'S') then
2953
2954 log_message(' In Create_events_and_fundscheck:Calling create events for CC ');
2955
2956 --Begin
2957 pa_xla_interface_pkg.create_events
2958 (p_calling_module => l_cc_calling_module,
2959 p_data_set_id => l_cc_data_set_id,
2960 x_result_code => l_result_status_code);
2961
2962 If l_result_status_code <> 'Success' then
2963 log_message(' In Create_events_and_fundscheck: Error creating CC events');
2964 g_msg_data := 'PA_XLA_EVENT_CREATION_FAILURE';
2965 RAISE BC_SLA_FAILURE;
2966 End If;
2967
2968 --Exception
2969 --When others then
2970 --log_message(' In Create_events_and_fundscheck: When Others: Error creating CC events');
2971 --g_msg_data := 'PA_XLA_EVENT_CREATION_FAILURE';
2972 --RAISE BC_SLA_FAILURE;
2973 --End;
2974
2975 End If; --If l_cc_clling_module IS NOT null then
2976 */
2977
2978 -- --------------------------------------------------------------+
2979 log_message(' In Create_events_and_fundscheck: Populating psa_bc_xla_events_gt');
2980
2981 If p_result_code = 'S' then -- I
2982
2983 -- --------------------------------------------------------------+
2984 -- ## 4.0: Populate budgetary control global table
2985 -- --------------------------------------------------------------+
2986
2987 -- Code has been moved to PAXLAIFB.pls ..
2988
2989 -- --------------------------------------------------------------+
2990 -- ## 5.0: Call Budgetary Controls Funds check API
2991 -- --------------------------------------------------------------+
2992 log_message(' In Create_events_and_fundscheck: Calling PSA_BC_XLA_PUB.Budgetary_Control');
2993
2994 Select decode(p_mode,'Force','F', -- 'Year End'
2995 'Check_Baseline','C', -- 'Check funds'
2996 'Reserve_Baseline','R') -- 'Baseline'
2997 into l_bc_mode
2998 from dual;
2999
3000 PSA_BC_XLA_PUB.Budgetary_Control
3001 (p_api_version => 1.0
3002 ,p_init_msg_list => FND_API.G_FALSE
3003 ,x_return_status => l_return_status
3004 ,x_msg_count => l_msg_count
3005 ,x_msg_data => l_msg_data
3006 ,p_application_id => 275
3007 ,p_bc_mode => l_bc_mode
3008 ,x_status_code => l_result_status_code
3009 ,x_packet_id => g_packet_id
3010 );
3011 -- Following paramters are optional and not used:
3012 --,P_override_flag,P_user_id,P_user_resp_Id
3013
3014 log_message(' In Create_events_and_fundscheck: After Calling PSA_BC_XLA_PUB.Budgetary_Control');
3015 log_message(' In Create_events_and_fundscheck: l_return_status,l_result_status_code:'||
3016 l_return_status||';'||l_result_status_code);
3017
3018 If l_return_status in ('E','U') then
3019
3020 log_message('In Create_events_and_fundscheck:Budgetary control API failed');
3021 p_result_code := 'E';
3022
3023 -- -----------------------------------------------------------------------------+
3024 -- ERROR HANDLING START .....
3025 -- -----------------------------------------------------------------------------+
3026
3027 If l_result_status_code is NULL then
3028
3029 open c_event_status;
3030 loop
3031 fetch c_Event_Status into l_result_status_code;
3032 exit; -- basically exit after the 1st fetch ..
3033 end loop;
3034 close c_event_status;
3035
3036 log_message(' In Create_events_and_fundscheck: After c_event_status:l_result_status_code:'||l_result_status_code);
3037
3038 End If;
3039
3040 for x in c_bc_api_error
3041 loop
3042
3043 If p_mode = 'Check_Baseline' then
3044
3045 select length(x.encoded_msg) into l_dummy from dual;
3046
3047 If l_dummy > 0 then
3048
3049 ADD_MESSAGE(substr(x.encoded_msg,1,200)||'...');
3050
3051 --FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_BUDGET_FUND_PKG',
3052 -- p_procedure_name => g_procedure_name,
3053 -- p_error_text => substr(x.encoded_msg,1,230)||'...');
3054 End If;
3055
3056 If l_dummy > 200 then
3057
3058 ADD_MESSAGE('...'||substr(x.encoded_msg,201,400));
3059 --FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_BUDGET_FUND_PKG',
3060 -- p_procedure_name => g_procedure_name,
3061 -- p_error_text => '...'||substr(x.encoded_msg,231,460));
3062 End If;
3063
3064 End If;
3065
3066 log_message(substr(x.encoded_msg,1,230)||'...');
3067 log_message('...'||substr(x.encoded_msg,231,460));
3068
3069 end loop;
3070
3071 If l_dummy is null then
3072 Begin
3073 select -1 into l_dummy from dual where exists
3074 (select evt.event_id
3075 from xla_events evt,
3076 psa_bc_xla_events_gt tmp
3077 where evt.event_id = tmp.event_id
3078 and evt.process_status_code = 'U');
3079
3080 If p_mode = 'Check_Baseline' then
3081
3082 If l_result_status_code is null then
3083 g_msg_data := 'PA_BC_EVENTS_NOT_PROCESSED';
3084 COMMIT;
3085 RAISE BC_SLA_FAILURE;
3086 Else
3087 ADD_MESSAGE('PA_BC_EVENTS_NOT_PROCESSED');
3088 End If;
3089
3090 End If;
3091
3092 log_message('Events not processed');
3093
3094
3095 Exception
3096 when no_data_found then
3097 NULL;
3098 End;
3099
3100 End If;
3101
3102 -- -----------------------------------------------------------------------------+
3103 -- ERROR HANDLING ENDS .....
3104 -- -----------------------------------------------------------------------------+
3105
3106 End If;
3107
3108 -- ----------------------------------------------------------------------------------+
3109 -- Following code is to fail draft account, if there is a failure in SLA ...
3110 -- ----------------------------------------------------------------------------------+
3111 If ((l_return_status in ('E','U')) OR
3112 (l_result_status_code in ('FAIL','XLA_ERROR','XLA_NO_JOURNAL','FATAL'))
3113 ) then
3114 -- -------------------------------------------------------------------------------+
3115 -- For Year end, we need draft version, to pass to Fail_draft_acct_summary below
3116 -- -------------------------------------------------------------------------------+
3117 If p_mode = 'Force' then
3118 Begin
3119
3120 Select pbv.budget_version_id
3121 into g_draft_bvid
3122 from pa_budget_versions pbv
3123 where pbv.project_id = g_project_id
3124 and pbv.budget_status_code = 'W'
3125 and pbv.budget_type_code = l_budget_type_code;
3126
3127 log_message('Force mode, draft bvid: '||g_draft_bvid);
3128
3129 Exception
3130 When no_data_found then
3131 null;
3132 End;
3133
3134 End If;
3135
3136
3137 -- This new procedure is being called to fail account summary
3138 -- in case there is a failure in SLA setup ..
3139 Fail_draft_acct_summary(p_draft_version_id => g_draft_bvid,
3140 p_failure_status => l_result_status_code);
3141
3142
3143 End if; -- If l_return_status in ('E','U') then
3144 -- ----------------------------------------------------------------------------------+
3145
3146 If p_mode = 'Check_Baseline' then
3147
3148 COMMIT; -- this commit will help debug as the events will be visible
3149 -- in case of failure. For baseline, we cannot do this commit
3150 -- as the bselined budget will get commited ...
3151
3152 End If;
3153
3154 If l_result_status_code = 'FAIL' then
3155 log_message('BC Funds Check validation failed');
3156 g_msg_data := 'PA_BC_GL_FNDS_RESV_FAIL';
3157 RAISE BC_SLA_FAILURE;
3158
3159 ElsIf l_result_status_code = 'XLA_ERROR' then
3160 log_message('XLA_ERROR: SLA validation failed');
3161 g_msg_data := 'PA_BC_XLA_ERROR';
3162 RAISE BC_SLA_FAILURE;
3163
3164 ElsIf l_result_status_code = 'XLA_NO_JOURNAL' then
3165 log_message('XLA_NO_JOURNAL: SLA validation complete');
3166 g_msg_data := 'PA_BC_XLA_ERROR';
3167 RAISE BC_SLA_FAILURE;
3168
3169 ElsIf l_result_status_code = 'FATAL' then
3170 log_message('Fatal error in budgetary control API');
3171 g_msg_data := 'PA_BC_FATAL';
3172 RAISE BC_SLA_FAILURE;
3173
3174 End If;
3175
3176
3177 End If; -- Status_code = 'S' -- I
3178 End if ; -- End for Bug 5686300
3179
3180 log_message(' In Create_events_and_fundscheck: End');
3181
3182 Exception
3183 When BC_SLA_FAILURE then
3184 p_result_code := 'E';
3185
3186 -- When others then
3187 -- log_message(' In Create_events_and_fundscheck: '||SQLERRM);
3188 -- p_result_code := 'E';
3189 -- g_msg_data := substr(SQLERRM,1,240);
3190 -- RAISE;
3191
3192 End Create_events_and_fundscheck;
3193
3194 -- Procedure used to add message to stack
3195 PROCEDURE ADD_MESSAGE(p_message IN VARCHAR2)
3196 IS
3197 BEGIN
3198 FND_MESSAGE.SET_NAME('PA','PA_UNEXPECTED_ERR_AMG');
3199 FND_MESSAGE.SET_TOKEN('ORAERR',p_message);
3200 FND_MSG_PUB.ADD;
3201
3202 END ADD_MESSAGE;
3203
3204 -- Procedure used to call pa_debug.write for FND logging
3205 PROCEDURE LOG_MESSAGE(p_message in VARCHAR2)
3206 IS
3207 BEGIN
3208 IF g_debug_mode = 'Y' then
3209
3210 IF p_message is NOT NULL then
3211 pa_debug.g_err_stage := 'Error Msg :'||substr(p_message,1,250);
3212 --pa_debug.write_file('LOG: '||pa_debug.g_err_stage);
3213 PA_DEBUG.write
3214 (x_Module => 'pa.plsql.PA_BUDGET_FUND_PKG.'||g_procedure_name
3215 ,x_Msg => substr(p_message,1,240)
3216 ,x_Log_Level => 3);
3217 END IF;
3218 END IF;
3219
3220 END LOG_MESSAGE;
3221
3222 -- -----------------------------------------------------------------------+
3223 -- This function is called to determine if any CDLs
3224 -- that could be burdened have not been burdened
3225 -- This can lead to burden cost being dropped off during rebaseline
3226 -- Called from PAXBUEBU.fmb - Budgets form ...
3227 -- -----------------------------------------------------------------------+
3228
3229 FUNCTION Unburdened_cdl_exists(X_project_id IN Number)
3230 RETURN BOOLEAN
3231 IS
3232 l_burden_method pa_project_types.burden_amt_display_method%Type;
3233 l_exists varchar2(1);
3234 BEGIN
3235
3236 log_message(' In Unburdened_cdl_exists:Check burden method');
3237
3238 Select decode(NVL(ppt.burden_cost_flag, 'N'),'Y',
3239 NVL(ppt.burden_amt_display_method,'S'),'N')
3240 into l_burden_method
3241 from pa_project_types ppt,
3242 pa_projects_all pp
3243 where ppt.project_type = pp.project_type
3244 and pp.project_id = X_project_id;
3245
3246 log_message(' In Unburdened_cdl_exists:Burden method is :'||l_burden_method);
3247
3248 If l_burden_method <> 'D' then
3249 -- For no burden 'N', there is no issue
3250 -- For same line burden 'S', there is no issue as we use burdened cost
3251 -- during FC ...
3252 RETURN FALSE;
3253 Else
3254 -- Sep line is the issue, as we use BTC for the burden ..
3255 Select 'Y' into l_exists
3256 from dual
3257 where exists
3258 (select 1
3259 from pa_cost_distribution_lines_all cdl
3260 where project_id = X_project_id
3261 and burden_sum_source_run_id = -9999
3262 and line_type = 'R'); -- Added for Bug 5864881
3263
3264 log_message(' In Unburdened_cdl_exists: Unburdened CDLs exist');
3265
3266 RETURN TRUE;
3267 End If;
3268 EXCEPTION
3269 WHEN NO_DATA_FOUND THEN
3270 RETURN FALSE;
3271 End Unburdened_cdl_exists;
3272
3273
3274 -- -----------------------------------------------------------------------+
3275 -- This procedure will update the failure code on the draft version
3276 -- account summary (update only if the lines still have a passed status)
3277 -- We will pass the status of PSA API ...
3278 -- -----------------------------------------------------------------------+
3279
3280 Procedure Fail_draft_acct_summary(p_draft_version_id IN Number,
3281 p_failure_status IN Varchar2)
3282 IS
3283 PRAGMA AUTONOMOUS_TRANSACTION;
3284 Begin
3285
3286 log_message(' In Fail_draft_acct_summary');
3287 Update pa_budget_acct_lines
3288 set funds_check_status_code = 'R',
3289 funds_check_result_code = decode(substr(nvl(funds_check_result_code,'P'),1,1),'P',
3290 decode(p_failure_status,
3291 'FAIL',decode(g_processing_mode,
3292 'CHECK_FUNDS','F150','F155'),
3293 'XLA_ERROR','F172',
3294 'XLA_NO_JOURNAL','F172',
3295 'FATAL','F172',
3296 'F172'),funds_check_result_code
3297 )
3298 where budget_version_id = p_draft_version_id
3299 and (funds_check_status_code = 'A' or
3300 nvl(funds_check_result_code,'P') like 'P%'
3301 );
3302
3303 log_message(' In Fail_draft_acct_summary: Updated '||sql%rowcount||' records');
3304 COMMIT;
3305
3306 End Fail_draft_acct_summary;
3307
3308 -- ------------------------------------------------------------------------------+
3309 -- ------------------------------------ R12 End ---------------------------------+
3310 -- ------------------------------------------------------------------------------+
3311
3312 END PA_BUDGET_FUND_PKG;