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