[Home] [Help]
PACKAGE BODY: APPS.PA_BGT_BASELINE_PKG
Source
1 PACKAGE BODY PA_BGT_BASELINE_PKG AS
2 -- $Header: PAFCBALB.pls 120.9.12000000.2 2007/10/24 12:01:39 pvishnoi ship $
3 P_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
4
5 PROCEDURE MAINTAIN_BAL_FCHK(
6 p_project_id IN number,
7 p_budget_version_id IN number,
8 p_baselined_budget_version_id IN NUMBER, --R12 Funds Management Uptake :Parameter to store newly baselined version ID
9 p_bdgt_ctrl_type IN varchar2, --GL, CC
10 p_calling_mode IN varchar2, --CHECK_BASELINE, RESERVE_BASELINE
11 p_bdgt_intg_flag IN varchar2, --Y, N
12 x_return_status OUT NOCOPY varchar2 ,
13 x_error_message_code OUT NOCOPY varchar2) IS
14
15 PRAGMA AUTONOMOUS_TRANSACTION;
16
17 l_sob_id number;
18 l_packet_id number;
19 l_return_status varchar2(1);
20
21 x_error_stage VARCHAR2(100);
22 x_error_msg VARCHAR2(1000);
23 l_org_id number; --Bug 6524116
24 rows NATURAL := 200;
25 --R12 Funds Management Uptake : deleted obsolete variables
26
27 resource_busy exception;
28 pragma exception_init(resource_busy,-00054);
29
30 ACQUIRE_LOCK_EXCEPTION exception;
31
32 --Cursor to lock pa_bc_balances table for the project_id that is being baselined.
33 cursor c_bal_lock is
34 select end_date from pa_bc_balances
35 where project_id = p_project_id
36 for update nowait;
37
38 --When baselining errors with oracle error, insufficient funds and if user rebaselines
39 --then we have to delete from pa_bc_packets all the transactions that were created
40 --during the first baseline. The cursor below is for this purpose
41 -- R12 Funds Management Uptake : Obsolete logic with new architecture
42
43 --Cursor to update status code from C to A for packets that have been funds checked
44 --before the budget baselining notification was approved
45 -- R12 Funds Management Uptake : Obsolete cursor c_updsts with new architecture
46
47 ---------------------------------------------------------------------------------------------
48 -- procedure to create pa_bc_balances records from PA_BUDGET_LINES and to clean up PA_BC_PACKETS
49 /* ========================================================================================== +
50 FOLLOWING CODE MOVED TO PA_BUDGET_FUND_PKG (PABBFNDB.pls)
51
52 PROCEDURE INSERT_BGT_BALANCES(
53 p_project_id in number,
54 p_budget_version_id in number,
55 p_set_of_books_id in number,
56 p_bdgt_intg_flag in varchar2,
57 --p_fc_reqd in varchar2, --R12 Funds Management Uptake
58 x_return_status out NOCOPY varchar2,
59 x_error_message_code out NOCOPY varchar2) is
60
61 l_start_date date;
62 l_end_date date;
63 l_tab_count number := 0;
64 l_tab_periods PA_FUNDS_CONTROL_UTILS.tab_closed_period;
65
66 l_base_version_id number;
67 l_res_list_id number;
68 l_entry_level_code varchar2(1);
69
70 --Cursor to select BGT budget balances from pa_budget_lines,etc.
71 cursor c_bdgt_bal is
72 select pa.project_id,
73 pa.task_id,
74 pt.top_task_id,
75 pa.resource_list_member_id,
76 pbv.budget_version_id,
77 pb.PERIOD_NAME,
78 pb.START_DATE,
79 pb.END_DATE,
80 rm.PARENT_MEMBER_ID,
81 pb.burdened_cost
82 from
83 pa_budget_lines pb,
84 pa_resource_assignments pa,
85 pa_tasks pt,
86 pa_resource_list_members rm,
87 pa_budget_versions pbv
88 where pbv.budget_version_id = p_budget_version_id
89 and pa.resource_assignment_id = pb.resource_assignment_id
90 and pa.task_id = pt.task_id (+)
91 and pa.budget_version_id = pbv.budget_version_id
92 and rm.resource_list_member_id = pa.resource_list_member_id;
93
94 --Cursor to copy actual/encumbrance balance when FC is not reqd
95 -- R12 Funds Management Uptake : Obsolete cursor c_actencbal
96
97 cursor c_actencbal(p_base_version_id in number) is
98 select pb.project_id,
99 pb.task_id,
100 pb.top_task_id,
101 pb.resource_list_member_id,
102 pb.balance_type,
103 pb.set_of_books_id,
104 pb.PERIOD_NAME,
105 pb.START_DATE,
106 pb.END_DATE,
107 pb.PARENT_MEMBER_ID,
108 pb.actual_period_to_date,
109 pb.encumb_period_to_date
110 from pa_bc_balances pb
111 where pb.budget_version_id = p_base_version_id
112 and pb.balance_type <> 'BGT';
113
114 --Tables to insert BGT lines into pa_bc_balances.
115 l_ProjTab PA_PLSQL_DATATYPES.IdTabTyp;
116 l_TaskTab PA_PLSQL_DATATYPES.IdTabTyp;
117 l_TTaskTab PA_PLSQL_DATATYPES.IdTabTyp;
118 l_RlmiTab PA_PLSQL_DATATYPES.IdTabTyp;
119 l_BdgtVerTab PA_PLSQL_DATATYPES.IdTabTyp;
120 l_PeriodTab PA_PLSQL_DATATYPES.Char30TabTyp;
121 l_StDateTab PA_PLSQL_DATATYPES.DateTabTyp;
122 l_EdDateTab PA_PLSQL_DATATYPES.DateTabTyp;
123 l_ParMemTab PA_PLSQL_DATATYPES.IdTabTyp;
124 l_BurdCostTab PA_PLSQL_DATATYPES.NumTabTyp;
125
126 --Tables to copy ACT ENC lines into pa_bc_balances.
127 -- R12 Funds Management Uptake : Deleted variables used in obsolete logic
128
129 l_BalRowIdTab PA_PLSQL_DATATYPES.RowidTabTyp;
130
131 --cursor to delete all versions prior to the latest baselined balance records if the
132 --budget has been baselined before.
133 cursor c_delbal(p_bdgt_ctrl_type in varchar2, p_bdgt_ver in number) is
134 select a.rowid
135 from pa_bc_balances a, pa_budgetary_control_options pbco, pa_budget_versions pbv
136 where pbv.budget_version_id <> p_bdgt_ver
137 and a.project_id = pbco.project_id
138 and a.project_id = pbv.project_id
139 and a.budget_version_id = pbv.budget_version_id
140 and pbco.bdgt_cntrl_flag = 'Y'
141 and pbco.budget_type_code = pbv.budget_type_code
142 and ((p_bdgt_ctrl_type = 'GL' and pbco.external_budget_code = 'GL')
143 or
144 (p_bdgt_ctrl_type = 'CC' and pbco.external_budget_code = 'CC')
145 or
146 (p_bdgt_ctrl_type = 'GL' and pbco.external_budget_code is null))
147 and a.project_id = p_project_id;
148
149 --cursor to delete draft version balance records when there is a failure in budget
150 --baselining and the budget has not been baselined before.
151 cursor c_deldraftbal(p_draft_bdgt_ver in number) is
152 select rowid
153 from pa_bc_balances
154 where budget_version_id = p_draft_bdgt_ver;
155
156 BEGIN
157
158 --Initialize the error stack
159 PA_DEBUG.set_err_stack('Insert BGT Balances');
160
161 --Initialize the return status to success
162 x_return_status := FND_API.G_RET_STS_SUCCESS;
163
164 IF P_DEBUG_MODE = 'Y' THEN
165 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Entering Insert BGT Balances');
166 END IF;
167
168 --Delete all records for the passed draft budget version id
169 --This is to make sure that existing balance records for the draft version id due to
170 --a failure in the budget baselining process will be deleted
171 IF P_DEBUG_MODE = 'Y' THEN
172 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Open cursor to delete draft budget version- '||p_budget_version_id);
173 END IF;
174 OPEN c_deldraftbal(p_budget_version_id);
175 LOOP
176
177 l_BalRowIdTab.Delete;
178
179 FETCH c_deldraftbal bulk collect into
180 l_BalRowIdTab
181 limit rows;
182
183 if l_BalRowIdTab.count = 0 then
184 IF P_DEBUG_MODE = 'Y' THEN
185 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'No rec in c_deldraftbal, exit');
186 END IF;
187 exit;
188 end if;
189
190 IF P_DEBUG_MODE = 'Y' THEN
191 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Delete draft budget versions = ' || l_BalRowIdTab.count);
192 END IF;
193 FORALL j in l_BalRowIdTab.first..l_BalRowIdTab.last
194 delete from pa_bc_balances
195 where rowid = l_BalRowIdTab(j);
196
197 commit;
198 exit when c_deldraftbal%notfound;
199 END LOOP;
200 CLOSE c_deldraftbal;
201 IF P_DEBUG_MODE = 'Y' THEN
202 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'After deleting draft versions, close cursor');
203 END IF;
204
205 --If Budget is baselined before then we need to get the latest baselined
206 --version and delete from pa_bc_balances where budget_version_id not equal
207 --to the latest baselined version. This is to maintain 2 budget versions at any
208 --time. If budget is linked then we need to copy closed period balances to the
209 --current budget version id.
210 --IF (PA_FUNDS_CONTROL_UTILS.Is_Budget_Baselined_Before(p_project_id) = 'Y') then
211
212 --Get latest Baselined budget version id
213 IF P_DEBUG_MODE = 'Y' THEN
214 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Get baselined budget');
215 END IF;
216
217 PA_FUNDS_CONTROL_UTILS.Get_Baselined_Budget_Version(
218 p_calling_mode => p_bdgt_ctrl_type,
219 p_project_id => p_project_id,
220 x_base_version_id => l_base_version_id,
221 x_res_list_id => l_res_list_id,
222 x_entry_level_code => l_entry_level_code,
223 x_return_status => x_return_status,
224 x_error_message_code => x_error_message_code);
225
226 IF P_DEBUG_MODE = 'Y' THEN
227 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'After get baselined budget = '||l_base_version_id||' RetSts = '||x_return_status);
228 END IF;
229
230 --If there is a baselined version then we delete balance records for versions prior to this version
231 IF (l_base_version_id is not null) THEN
232
233 IF P_DEBUG_MODE = 'Y' THEN
234 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Baselined budget exists, delete old versions');
235 END IF;
236 --l_BalRowIdTab.Delete;
237
238 --delete from pa_bc_balances where budget_version_id <> l_base_version_id
239 PA_DEBUG.set_err_stack('Delete');
240 IF P_DEBUG_MODE = 'Y' THEN
241 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Open cursor to delete old budget versions');
242 END IF;
243 OPEN c_delbal(p_bdgt_ctrl_type,l_base_version_id);
244 LOOP
245
246 l_BalRowIdTab.Delete;
247
248 FETCH c_delbal bulk collect into
249 l_BalRowIdTab
250 limit rows;
251
252 if l_BalRowIdTab.count = 0 then
253 IF P_DEBUG_MODE = 'Y' THEN
254 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'No rec in c_delbal, exit');
255 END IF;
256 exit;
257 end if;
258
259 IF P_DEBUG_MODE = 'Y' THEN
260 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Delete old budget versions = ' || l_BalRowIdTab.count);
261 END IF;
262 FORALL j in l_BalRowIdTab.first..l_BalRowIdTab.last
263 delete from pa_bc_balances
264 where rowid = l_BalRowIdTab(j);
265
266 commit;
267 exit when c_delbal%notfound;
268 END LOOP;
269 CLOSE c_delbal;
270 IF P_DEBUG_MODE = 'Y' THEN
271 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'After deleting old budget versions, close cursor');
272 END IF;
273 PA_DEBUG.Reset_Err_Stack; --3912094
274 END IF;
275
276 -- R12 Funds management Uptake : Removed check for if fundscheck required which was always YES
277
278 IF (p_bdgt_intg_flag = 'Y') THEN
279
280 IF P_DEBUG_MODE = 'Y' THEN
281 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Bdgt Intg Flag = Y');
282 END IF;
283
284 PA_DEBUG.set_err_stack('Bdgt linked');
285 begin
286 select min(start_date), max(end_date)
287 into l_start_date, l_end_date
288 from pa_bc_balances
289 where project_id = p_project_id
290 and budget_version_id = l_base_version_id;
291 exception
292 when no_data_found then
293 null;
294 end;
295
296 IF P_DEBUG_MODE = 'Y' THEN
297 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Start,End = '|| l_start_date ||', '||l_end_date);
298 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Calling get gl periods');
299 END IF;
300 PA_DEBUG.Reset_Err_Stack; --3912094
301
302 PA_DEBUG.set_err_stack('Call get_gl_periods');
303
304 --Get all periods given the start and end date.
305 PA_FUNDS_CONTROL_UTILS.get_gl_periods
306 (p_start_date => l_start_date,
307 p_end_date => l_end_date,
308 p_set_of_books_id => p_set_of_books_id,
309 x_tab_count => l_tab_count,
310 x_tab_pds => l_tab_periods,
311 x_return_status => x_return_status);
312
313 IF P_DEBUG_MODE = 'Y' THEN
314 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'After get gl periods, RetSts = '||x_return_status);
315 END IF;
316 PA_DEBUG.Reset_Err_Stack; --3912094
317
318 PA_DEBUG.set_err_stack('Insert Close Period Bal');
319 IF P_DEBUG_MODE = 'Y' THEN
320 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Insert closed period balances, TabCount = '||l_tab_count);
321 END IF;
322
323 FOR i in 1..l_tab_count LOOP
324 begin
325 IF P_DEBUG_MODE = 'Y' THEN
326 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'St,End and Status = ' ||l_tab_periods(i).start_date||':'||
327 l_tab_periods(i).end_date||':'||l_tab_periods(i).closing_status);
328 END IF;
329 insert into pa_bc_balances(
330 PROJECT_ID,
331 TASK_ID,
332 TOP_TASK_ID,
333 RESOURCE_LIST_MEMBER_ID,
334 BALANCE_TYPE,
335 SET_OF_BOOKS_ID,
336 BUDGET_VERSION_ID,
337 LAST_UPDATE_DATE,
338 LAST_UPDATED_BY,
339 CREATED_BY,
340 CREATION_DATE,
341 LAST_UPDATE_LOGIN,
342 PERIOD_NAME,
343 START_DATE,
344 END_DATE,
345 PARENT_MEMBER_ID,
346 ACTUAL_PERIOD_TO_DATE,
347 ENCUMB_PERIOD_TO_DATE)
348 select
349 bal.PROJECT_ID,
350 bal.TASK_ID,
351 bal.TOP_TASK_ID,
352 bal.RESOURCE_LIST_MEMBER_ID,
353 bal.BALANCE_TYPE,
354 bal.SET_OF_BOOKS_ID,
355 p_budget_version_id,
356 sysdate,
357 FND_GLOBAL.USER_ID,
358 FND_GLOBAL.USER_ID,
359 sysdate,
360 FND_GLOBAL.LOGIN_ID,
361 bal.PERIOD_NAME,
362 bal.START_DATE,
363 bal.END_DATE,
364 bal.PARENT_MEMBER_ID,
365 bal.ACTUAL_PERIOD_TO_DATE,
366 bal.ENCUMB_PERIOD_TO_DATE
367 from pa_bc_balances bal
368 where budget_version_id = l_base_version_id
369 and trunc(start_date) = trunc(l_tab_periods(i).start_date)
370 and trunc(end_date) = trunc(l_tab_periods(i).end_date)
371 and l_tab_periods(i).closing_status = 'C'
372 and project_id = p_project_id
373 and balance_type <> 'BGT';
374 exception
375 when no_data_found then
376 null;
377 end;
378 END LOOP;
379 IF P_DEBUG_MODE = 'Y' THEN
380 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Inserted closed period balances');
381 END IF;
382 PA_DEBUG.Reset_Err_Stack; --3912094
383 END IF;
384
385 -- R12 Funds management Uptake : Deleted logic which was fired when fundscheck is not required .
389 IF P_DEBUG_MODE = 'Y' THEN
386 -- This procedure is always fired with fundscheck required as Yes.
387
388 --Insert BGT lines.
390 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Before inserting BGT lines');
391 END IF;
392 PA_DEBUG.set_err_stack('Insert BGT lines');
393
394 open c_bdgt_bal;
395 loop
396 l_ProjTab.Delete;
397 l_TaskTab.Delete;
398 l_TTaskTab.Delete;
399 l_RlmiTab.Delete;
400 l_BdgtVerTab.Delete;
401 l_PeriodTab.Delete;
402 l_StDateTab.Delete;
403 l_EdDateTab.Delete;
404 l_ParMemTab.Delete;
405 l_BurdCostTab.Delete;
406
407 IF P_DEBUG_MODE = 'Y' THEN
408 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Fetch c_bdgt_bal');
409 END IF;
410 fetch c_bdgt_bal bulk collect into
411 l_ProjTab,
412 l_TaskTab,
413 l_TTaskTab,
414 l_RlmiTab,
415 l_BdgtVerTab,
416 l_PeriodTab,
417 l_StDateTab,
418 l_EdDateTab,
419 l_ParMemTab,
420 l_BurdCostTab
421 limit rows;
422
423 IF (l_ProjTab.count = 0) THEN
424 IF P_DEBUG_MODE = 'Y' THEN
425 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'No rec in c_bdgt_bal, exit');
426 END IF;
427 EXIT;
428 END IF;
429
430 IF P_DEBUG_MODE = 'Y' THEN
431 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Before Insert, no. of rec = '|| l_ProjTab.count);
432 END IF;
433 -- insert into pa_bc_balances from pa_budget_lines
434 FORALL i in l_ProjTab.FIRST..l_ProjTab.LAST
435 insert into pa_bc_balances(
436 PROJECT_ID,
437 TASK_ID,
438 TOP_TASK_ID,
439 RESOURCE_LIST_MEMBER_ID,
440 BALANCE_TYPE,
441 SET_OF_BOOKS_ID,
442 BUDGET_VERSION_ID,
443 LAST_UPDATE_DATE,
444 LAST_UPDATED_BY,
445 CREATED_BY,
446 CREATION_DATE,
447 LAST_UPDATE_LOGIN,
448 PERIOD_NAME,
449 START_DATE,
450 END_DATE,
451 PARENT_MEMBER_ID,
452 BUDGET_PERIOD_TO_DATE,
453 ACTUAL_PERIOD_TO_DATE,
454 ENCUMB_PERIOD_TO_DATE)
455 select
456 l_ProjTab(i),
457 l_TaskTab(i),
458 l_TTaskTab(i),
459 l_RlmiTab(i),
460 'BGT',
461 p_set_of_books_id,
462 l_BdgtVerTab(i),
463 sysdate,
464 FND_GLOBAL.USER_ID,
465 FND_GLOBAL.USER_ID,
466 sysdate,
467 FND_GLOBAL.LOGIN_ID,
468 l_PeriodTab(i),
469 l_StDateTab(i),
470 l_EdDateTab(i),
471 l_ParMemTab(i),
472 l_BurdCostTab(i),
473 0,
474 0
475 from dual;
476 commit;
477 IF P_DEBUG_MODE = 'Y' THEN
478 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'After inserting BGT lines');
479 END IF;
480 exit when c_bdgt_bal%notfound;
481 end loop;
482 close c_bdgt_bal;
483 PA_DEBUG.Reset_Err_Stack; --3912094
484 PA_DEBUG.set_err_stack('Inserted BGT lines');
485
486 IF P_DEBUG_MODE = 'Y' THEN
487 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Exiting Insert BGT Balances');
488 END IF;
489
490 --Reset the error stack when returning to the calling program
491 PA_DEBUG.Reset_Err_Stack;
492 PA_DEBUG.Reset_Err_Stack; -- Bug 5064900
493
494 EXCEPTION
495 WHEN OTHERS THEN
496 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_BGT_BASELINE_PKG'
497 ,p_procedure_name => 'INSERT_BGT_BALANCES' --Bug 5064900
498 ,p_error_text => PA_DEBUG.G_Err_Stack );
499
500 IF c_bdgt_bal%ISOPEN THEN
501 close c_bdgt_bal;
502 END IF;
503 IF c_delbal%ISOPEN THEN
504 close c_delbal;
505 END IF;
506 IF c_deldraftbal%ISOPEN THEN
507 close c_delbal;
508 END IF;
509
510 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
511 x_error_message_code := (SQLCODE||' '||SQLERRM);
512 PA_DEBUG.Reset_Err_Stack; --Bug 5064900
513 RAISE;
514 END INSERT_BGT_BALANCES;
515 ================================================================================================ */
516 -----------------------------------------------------------------------------------
517
518
519 /******************************IS FC REQUIRED********************************
520 This funtion will return TRUE for Buza Phase 1.
521 All conditions will be included for Phase 2
522
523 Aug 01 - This function will not be implemented as the current supported
524 document types for budgetary control and top-down integration
525 is going to be AP INV,PO, REQ, INV ADJ. Since the volume will
526 be less this function is not implemented. Moreover there is
527 no design consideration of maintaining amount type/boundary code
531 -- This Function is to see if Funds checking (FC) is required for this Budget.
528 for each budget version id.
529 ****************************************************************************/
530
532
533 -- Conditions for FC requirement is:
534 -- 1. If Budget Entry Method has been changed in the new draft budget.
535 -- 2. If Resource List has been changed in the new draft budget.
536 -- 3. If Budgetted amount is decreased for a particular Resource
537 -- 4. If a new resource is added to the budget (To be enabled later).
538
539 FUNCTION is_fc_required (p_project_id IN NUMBER, p_bdgt_ctrl_type in varchar2) RETURN boolean IS
540
541 l_draft_entry_method varchar2(30);
542 l_draft_res_list_id number;
543 l_draft_burdened_cost number;
544
545 l_baselined_entry_method varchar2(30);
546 l_baselined_res_list_id number;
547 l_baselined_burdened_cost number;
548
549 CURSOR c_bdgt_ver(p_project_id in NUMBER, p_bdgt_ctrl_type in varchar2) IS
550 select
551 draft_array.budget_entry_method_code,
552 baselined_array.budget_entry_method_code,
553 draft_array.resource_list_id,
554 baselined_array.resource_list_id,
555 draft_array.burdened_cost,
556 baselined_array.burdened_cost
557 from
558 (select
559 pbv_b.budget_entry_method_code budget_entry_method_code,
560 pbv_b.resource_list_id resource_list_id,
561 pbl_b.burdened_cost burdened_cost,
562 pra_b.resource_list_member_id resource_list_member_id,
563 pra_b.project_id project_id,
564 pra_b.task_id task_id,
565 pbl_b.start_date start_date
566 from pa_budget_versions pbv_b,
567 pa_budgetary_control_options pbco_b,
568 pa_budget_lines pbl_b,
569 pa_resource_assignments pra_b
570 where pbv_b.project_id = p_project_id
571 and pbv_b.budget_status_code = 'B'
572 and pbv_b.current_flag ='Y'
573 and pbco_b.bdgt_cntrl_flag = 'Y'
574 and pbco_b.budget_type_code = pbv_b.budget_type_code
575 and pbco_b.project_id = pbv_b.project_id
576 and ((p_bdgt_ctrl_type = 'GL' and pbco_b.external_budget_code = 'GL')
577 or
578 (p_bdgt_ctrl_type = 'CC' and pbco_b.external_budget_code = 'CC')
579 or
580 (p_bdgt_ctrl_type = 'GL' and pbco_b.external_budget_code is null))
581 and pbv_b.budget_version_id = pra_b.budget_version_id
582 and pra_b.resource_assignment_id = pbl_b.resource_assignment_id) baselined_array,
583 (select
584 pbv_d.budget_entry_method_code budget_entry_method_code,
585 pbv_d.resource_list_id resource_list_id,
586 pbl_d.burdened_cost burdened_cost,
587 pra_d.resource_list_member_id resource_list_member_id,
588 pra_d.project_id project_id,
589 pra_d.task_id task_id,
590 pbl_d.start_date start_date
591 from pa_budget_versions pbv_d,
592 pa_budgetary_control_options pbco_d,
593 pa_budget_lines pbl_d,
594 pa_resource_assignments pra_d
595 where pbv_d.project_id = p_project_id
596 and pbv_d.budget_status_code in ('W','S')
597 and pbco_d.bdgt_cntrl_flag = 'Y'
598 and pbco_d.budget_type_code = pbv_d.budget_type_code
599 and pbco_d.project_id = pbv_d.project_id
600 and ((p_bdgt_ctrl_type = 'GL' and pbco_d.external_budget_code = 'GL')
601 or
602 (p_bdgt_ctrl_type = 'CC' and pbco_d.external_budget_code = 'CC')
603 or
604 (p_bdgt_ctrl_type = 'GL' and pbco_d.external_budget_code is null))
605 and pbv_d.budget_version_id = pra_d.budget_version_id
606 and pra_d.resource_assignment_id = pbl_d.resource_assignment_id) draft_array
607 where baselined_array.project_id = draft_array.project_id(+)
608 and baselined_array.resource_list_member_id = draft_array.resource_list_member_id (+)
609 and baselined_array.task_id = draft_array.task_id(+)
610 and baselined_array.start_date = draft_array.start_date(+);
611
612 BEGIN
613
614 IF P_DEBUG_MODE = 'Y' THEN
615 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Entering Is FC reqd');
616 END IF;
617
618 /**********************************************************
619 Commented for Phase 1.
620 To be enabled for Phase 2 with additional conditions
621
622 open c_bdgt_ver(p_project_id, p_bdgt_ctrl_type);
623
624 loop
625 fetch c_bdgt_ver into
626 l_draft_entry_method,
627 l_baselined_entry_method,
628 l_draft_res_list_id,
629 l_baselined_res_list_id,
630 l_draft_burdened_cost,
631 l_baselined_burdened_cost;
632
633 exit when c_bdgt_ver%NOTFOUND;
634
635 -- Check to see if Budget Entry Method has been changed in Draft Budget
636 if nvl(l_draft_entry_method,'x') <> nvl(l_baselined_entry_method,'x') then
637 close c_bdgt_ver;
638 return TRUE; -- FC reqd.
639 end if;
640
641 -- Check to see if Resource List has been changed in Draft Budget
645 end if;
642 if nvl(l_draft_res_list_id,0) <> nvl(l_baselined_res_list_id,0) then
643 close c_bdgt_ver;
644 return TRUE; -- FC reqd.
646
647 if nvl(l_draft_burdened_cost,0) < nvl(l_baselined_burdened_cost,0) then
648 close c_bdgt_ver;
649 return TRUE; -- FC reqd.
650 end if;
651
652 end loop;
653
654 IF P_DEBUG_MODE = 'Y' THEN
655 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Exiting Is FC Reqd');
656 END IF;
657
658 close c_bdgt_ver;
659 return FALSE;
660 ***********************************************************/
661
662 return TRUE;
663
664 END is_fc_required;
665 -------------------------------------------------------------------------------
666
667 -- procedure to load all the vendor invoices, expense reports and BTC transactions
668 -- into pa_bc_packets for funds check
669 PROCEDURE INSERT_VI_ER_BTC_TXNS(
670 p_packet_id IN NUMBER,
671 p_sob_id IN NUMBER,
672 p_project_id IN NUMBER,
673 p_budget_version_id IN NUMBER,
674 p_bdgt_intg_flag IN VARCHAR2,
675 x_return_status OUT NOCOPY VARCHAR2,
676 x_error_message_code OUT NOCOPY VARCHAR2) IS
677
678 l_ExpProjTab PA_PLSQL_DATATYPES.IdTabTyp;
679 l_ExpTaskTab PA_PLSQL_DATATYPES.IdTabTyp;
680 l_ExpExpTypTab PA_PLSQL_DATATYPES.Char30TabTyp;
681 l_ExpEiDateTab PA_PLSQL_DATATYPES.DateTabTyp;
682 l_ExpExpOrgTab PA_PLSQL_DATATYPES.IdTabTyp;
683 l_ExpPeriodTab PA_PLSQL_DATATYPES.Char15TabTyp;
684 l_ExpPdYearTab PA_PLSQL_DATATYPES.NumTabTyp;
685 l_ExpPdNumTab PA_PLSQL_DATATYPES.NumTabTyp;
686 l_ExpDocDistTab PA_PLSQL_DATATYPES.IdTabTyp;
687 l_ExpDocHdrTab PA_PLSQL_DATATYPES.IdTabTyp;
688 l_ExpEntDrTab PA_PLSQL_DATATYPES.NumTabTyp;
689 l_ExpEntCrTab PA_PLSQL_DATATYPES.NumTabTyp;
690 l_ExpAcctDrTab PA_PLSQL_DATATYPES.NumTabTyp;
691 l_ExpAcctCrTab PA_PLSQL_DATATYPES.NumTabTyp;
692 l_ExpGlDateTab PA_PLSQL_DATATYPES.DateTabTyp;
693 l_ExpPaDateTab PA_PLSQL_DATATYPES.DateTabTyp;
694 l_ExpTxnCCIDTab PA_PLSQL_DATATYPES.IdTabTyp;
695 l_ExpOrgIdTab PA_PLSQL_DATATYPES.IdTabTyp;
696 l_ExpBdgtCCIDTab PA_PLSQL_DATATYPES.IdTabTyp;
697
698 --PA.M
699 l_ExpPoLineIdTab PA_PLSQL_DATATYPES.IdTabTyp;
700 l_ExpReference1Tab PA_PLSQL_DATATYPES.Char80TabTyp;
701 l_ExpReference2Tab PA_PLSQL_DATATYPES.Char80TabTyp;
702 l_ExpReference3Tab PA_PLSQL_DATATYPES.Char80TabTyp;
703 l_ExpParBcPktIdTab PA_PLSQL_DATATYPES.IdTabTyp;
704
705 TYPE t_ref_cursor IS REF CURSOR;
706
707 c_vierbtc_txns t_ref_cursor;
708
709 PROCEDURE proc_open_cursor(p_project_id in number, p_sob_id in number, p_bdgt_intg_flag in varchar2) IS
710 BEGIN
711 IF (p_bdgt_intg_flag = 'Y') THEN
712 OPEN c_VIERBTC_txns FOR
713 --(p_project_id in number, p_sob_id in number, p_bdgt_intg_flag in varchar2) is
714 select cdl.project_id,
715 cdl.task_id,
716 ei.EXPENDITURE_TYPE,
717 trunc(ei.EXPENDITURE_ITEM_DATE) expenditure_item_date,
718 nvl(ei.override_to_organization_id,exp.incurred_by_organization_id) organization_id,
719 gl.PERIOD_NAME,
720 gl.PERIOD_YEAR,
721 gl.PERIOD_NUM,
722 cdl.expenditure_item_id,
723 cdl.line_num,
724 /** Commented out for burdening enhancements
725 --decode(sign(cdl.denom_burdened_cost),1,cdl.denom_burdened_cost,0) entered_dr,
726 --decode(sign(cdl.denom_burdened_cost),-1,ABS(cdl.denom_burdened_cost),0) entered_cr,
727 --decode(sign(cdl.acct_burdened_cost),1,cdl.acct_burdened_cost,0) accounted_dr,
728 --decode(sign(cdl.acct_burdened_cost),-1,ABS(cdl.acct_burdened_cost),0) accounted_cr,
729 **/
730 decode(sign(nvl(cdl.denom_burdened_cost,0)+nvl(cdl.DENOM_BURDENED_CHANGE,0))
731 ,1,(nvl(cdl.denom_burdened_cost,0)+nvl(cdl.DENOM_BURDENED_CHANGE,0))
732 ,0) entered_dr,
733 decode(sign(nvl(cdl.denom_burdened_cost,0)+nvl(cdl.DENOM_BURDENED_CHANGE,0))
734 ,-1,ABS(nvl(cdl.denom_burdened_cost,0)+nvl(cdl.DENOM_BURDENED_CHANGE,0))
735 ,0) entered_cr,
736 decode(sign(nvl(cdl.acct_burdened_cost,0)+nvl(cdl.ACCT_BURDENED_CHANGE,0))
737 ,1,(nvl(cdl.acct_burdened_cost,0)+nvl(cdl.ACCT_BURDENED_CHANGE,0))
738 ,0) accounted_dr,
739 decode(sign(nvl(cdl.acct_burdened_cost,0)+nvl(cdl.ACCT_BURDENED_CHANGE,0))
740 ,-1,ABS(nvl(cdl.acct_burdened_cost,0)+nvl(cdl.ACCT_BURDENED_CHANGE,0))
741 ,0) accounted_cr,
742 cdl.gl_date,
743 cdl.pa_date,
744 cdl.dr_code_combination_id,
745 cdl.org_id,
746 cdl.budget_ccid
747 --PA.M
748 , ei.po_line_id
749 ,'EXP'
750 ,cdl.expenditure_item_id
751 ,cdl.line_num
752 --PA.M selecting -99 for parent bc pkt for CWK BTC EIs
753 ,decode(ei.system_linkage_function, 'BTC', decode(nvl(ei.po_line_id,-99), -99, null, -99), null)
754 from pa_expenditure_items_all ei,
755 pa_expenditures_all exp,
756 pa_cost_distribution_lines_all cdl,
757 gl_period_STATUSES gl,
758 pa_tasks pt
759 where cdl.project_id = p_project_id
760 and pt.task_id = ei.task_id
761 and ei.expenditure_id = exp.expenditure_id
765 and gl.application_id = 101
762 --and trunc(gl.end_date) = trunc(cdl.gl_date)
763 --commented above since AP gl_date = transaction date rather end date of period
764 and trunc(cdl.gl_date) between trunc(gl.start_date) and trunc(gl.end_date)
766 and gl.set_of_books_id = p_sob_id
767 and gl.adjustment_period_flag = 'N'
768 and gl.closing_status in ('O','F')
769 --and nvl(cdl.amount,0) <> 0 -- filter burden transactions
770 and ei.expenditure_item_id = cdl.expenditure_item_id
771 and cdl.line_type = 'R'
772 and cdl.reversed_flag is null
773 and cdl.line_num_reversed is null
774 --and ei.cost_distributed_flag = 'Y' commented out to handle failed ER batches
775 and nvl(ei.net_zero_adjustment_flag,'N') <> 'Y'
776 --PA.M: Added ST OT where PO Line ID is populated
777 and ((ei.system_linkage_function = 'VI' --'ER'
778 or
779 (ei.system_linkage_function in ('ST', 'OT')
780 and ei.po_line_id is not null)
781 )
782 or (ei.system_linkage_function = 'BTC'
783 --Bug 2795051
784 -- from pa_expenditure_items_all ei1,
785 -- and ei1.expenditure_item_id = cdl1.expenditure_item_id
786 -- and ei1.system_linkage_function = 'VI'
787 --Bug 3019361
788 --Added pa_aud_cost_dist_lines to select BTC EIs of previous run ids
789 --During PA.L testing, the below exists clause performed faster than alternative 2 below.
790 --hence replaced pa_bc_commitments join with pa_expenditure_items_all as it was originally
791 and exists (select null
792 from pa_expenditure_items_all ei1,
793 pa_cost_distribution_lines_all cdl1,
794 pa_aud_cost_dist_lines aud
795 --PA.M added outer join to cdl1 and aud plus changed the or to and
796 -- cos if there are no records in aud,
797 -- then this will not pick up the BTC txns
798 where ( cdl1.burden_sum_source_run_id(+) = ei.burden_sum_dest_run_id
799 and cdl1.expenditure_item_id(+) = ei1.expenditure_item_id
800 )
801 --or
802 and
803 ( aud.burden_sum_source_run_id(+) = ei.burden_sum_dest_run_id
804 and aud.expenditure_item_id(+) = ei1.expenditure_item_id
805 )
806 and cdl1.expenditure_item_id = ei1.expenditure_item_id
807 --PA.M: Added ST OT where PO Line ID is populated
808 and (ei1.system_linkage_function = 'VI'
809 or
810 (ei1.system_linkage_function in ('ST', 'OT')
811 and ei1.po_line_id is not null)
812 )
813 )
814 )
815 );
816
817 /*If above exists clause performs badly, alternative 2 is to replace join to ei with pa_bc_commitments_all */
818 /*Exists clause before fixing bug 3019361 is as follows
819 and exists (select null
820 from pa_bc_commitments_all bc,
821 pa_cost_distribution_lines_all cdl1
822 where cdl1.burden_sum_source_run_id = ei.burden_sum_dest_run_id
823 and bc.document_header_id = to_number(cdl1.system_reference2)
824 and bc.document_distribution_id = to_number(cdl1.system_reference3)))); --'ER'
825 */
826 ELSE
827 OPEN c_VIERBTC_txns FOR
828 select cdl.project_id,
829 cdl.task_id,
830 ei.EXPENDITURE_TYPE,
831 trunc(ei.EXPENDITURE_ITEM_DATE) expenditure_item_date,
832 nvl(ei.override_to_organization_id,exp.incurred_by_organization_id) organization_id,
833 gl.PERIOD_NAME,
834 gl.PERIOD_YEAR,
835 gl.PERIOD_NUM,
836 cdl.expenditure_item_id,
837 cdl.line_num,
838 /** Commented out for Burdening enhancements
839 --decode(sign(cdl.denom_burdened_cost),1,cdl.denom_burdened_cost,0) entered_dr,
840 --decode(sign(cdl.denom_burdened_cost),-1,ABS(cdl.denom_burdened_cost),0) entered_cr,
841 --decode(sign(cdl.acct_burdened_cost),1,cdl.acct_burdened_cost,0) accounted_dr,
842 --decode(sign(cdl.acct_burdened_cost),-1,ABS(cdl.acct_burdened_cost),0) accounted_cr,
843 **/
844 decode(sign(nvl(cdl.denom_burdened_cost,0)+nvl(cdl.DENOM_BURDENED_CHANGE,0))
845 ,1,(nvl(cdl.denom_burdened_cost,0)+nvl(cdl.DENOM_BURDENED_CHANGE,0))
846 ,0) entered_dr,
847 decode(sign(nvl(cdl.denom_burdened_cost,0)+nvl(cdl.DENOM_BURDENED_CHANGE,0))
848 ,-1,ABS(nvl(cdl.denom_burdened_cost,0)+nvl(cdl.DENOM_BURDENED_CHANGE,0))
849 ,0) entered_cr,
850 decode(sign(nvl(cdl.acct_burdened_cost,0)+nvl(cdl.ACCT_BURDENED_CHANGE,0))
851 ,1,(nvl(cdl.acct_burdened_cost,0)+nvl(cdl.ACCT_BURDENED_CHANGE,0))
852 ,0) accounted_dr,
853 decode(sign(nvl(cdl.acct_burdened_cost,0)+nvl(cdl.ACCT_BURDENED_CHANGE,0))
854 ,-1,ABS(nvl(cdl.acct_burdened_cost,0)+nvl(cdl.ACCT_BURDENED_CHANGE,0))
855 ,0) accounted_cr,
856 cdl.gl_date,
857 cdl.pa_date,
861 --PA.M
858 cdl.dr_code_combination_id,
859 cdl.org_id,
860 cdl.budget_ccid
862 ,ei.po_line_id
863 ,'EXP'
864 ,cdl.expenditure_item_id
865 ,cdl.line_num
866 --PA.M selecting -99 for parent bc pkt for CWK BTC EIs
867 ,decode(ei.system_linkage_function, 'BTC', decode(nvl(ei.po_line_id,-99), -99, null, -99), null)
868 from pa_expenditure_items_all ei,
869 pa_expenditures_all exp,
870 pa_cost_distribution_lines_all cdl,
871 gl_period_STATUSES gl,
872 pa_tasks pt
873 where cdl.project_id = p_project_id
874 and pt.task_id = ei.task_id
875 and ei.expenditure_id = exp.expenditure_id
876 --and trunc(gl.end_date) = trunc(cdl.gl_date)
877 --commented above since AP gl_date = transaction date rather end date of period
878 and trunc(cdl.gl_date) between trunc(gl.start_date) and trunc(gl.end_date)
879 and gl.application_id = 101
880 and gl.set_of_books_id = p_sob_id
881 and gl.adjustment_period_flag = 'N'
882 --and gl.closing_status = decode(p_bdgt_intg_flag, 'Y', 'O', gl.closing_status)
883 --and nvl(cdl.amount,0) <> 0 -- filter burden transactions
884 and ei.expenditure_item_id = cdl.expenditure_item_id
885 and cdl.line_type = 'R'
886 and cdl.reversed_flag is null
887 and cdl.line_num_reversed is null
888 --and ei.cost_distributed_flag = 'Y' commented out to handle failed ER batches
889 and nvl(ei.net_zero_adjustment_flag,'N') <> 'Y'
890 --PA.M: Added ST OT where PO Line ID is populated
891 and ((ei.system_linkage_function = 'VI' --'ER'
892 or
893 (ei.system_linkage_function in ('ST', 'OT')
894 and ei.po_line_id is not null)
895 )
896 or (ei.system_linkage_function = 'BTC'
897 --Bug 2795051
898 -- from pa_expenditure_items_all ei1,
899 -- and ei1.expenditure_item_id = cdl1.expenditure_item_id
900 -- and ei1.system_linkage_function = 'VI'
901 --Bug 3019361
902 --Added pa_aud_cost_dist_lines to select BTC EIs of previous run ids
903 --During PA.L testing, the below exists clause performed faster than alternative 2 below.
904 --hence replaced pa_bc_commitments join with pa_expenditure_items_all as it was originally
905 and exists (select null
906 from pa_expenditure_items_all ei1,
907 pa_cost_distribution_lines_all cdl1,
908 pa_aud_cost_dist_lines aud
909 --PA.M added outer join to cdl1 and aud plus changed the or to and
910 -- cos if there are no records in aud,
911 -- then this will not pick up the BTC txns
912 where ( cdl1.burden_sum_source_run_id(+) = ei.burden_sum_dest_run_id
913 and cdl1.expenditure_item_id(+) = ei1.expenditure_item_id
914 )
915 -- or
916 and
917 ( aud.burden_sum_source_run_id(+) = ei.burden_sum_dest_run_id
918 and aud.expenditure_item_id(+) = ei1.expenditure_item_id
919 )
920 and cdl1.expenditure_item_id = ei1.expenditure_item_id
921 --PA.M: Added ST OT where PO Line ID is populated
922 and (ei1.system_linkage_function = 'VI'
923 or
924 (ei1.system_linkage_function in ('ST', 'OT')
925 and ei1.po_line_id is not null)
926 )
927 )
928 )
929 );
930
931 /*If above exists clause performs badly, alternative 2 is to replace join to ei with pa_bc_commitments_all */
932 /*Exists clause before fixing bug 3019361 is as follows
933 and exists (select null
934 from pa_bc_commitments_all bc,
935 pa_cost_distribution_lines_all cdl1
936 where cdl1.burden_sum_source_run_id = ei.burden_sum_dest_run_id
937 and bc.document_header_id = to_number(cdl1.system_reference2)
938 and bc.document_distribution_id = to_number(cdl1.system_reference3)))); --'ER'
939 */
940 END IF;
941 END proc_open_cursor;
942
943 BEGIN
944
945 --Initialize the error stack
946 PA_DEBUG.set_err_stack('Insert VIERBTC Txns');
947
948 --Initialize the return status to success
949 x_return_status := FND_API.G_RET_STS_SUCCESS;
950
951 IF P_DEBUG_MODE = 'Y' THEN
952 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Entering VI ER BTC Project = '|| p_project_id||' SOB = '|| p_sob_id || ' Intg = '||p_bdgt_intg_flag);
953 END IF;
954
955 --OPEN c_VIERBTC_txns(p_project_id, p_sob_id, p_bdgt_intg_flag);
956 --Call procedure to open cursor c_vierbtc_txns
957 proc_open_cursor(p_project_id, p_sob_id, p_bdgt_intg_flag);
958
959 LOOP
960
961 --Initialize pl/sql tables
962 l_ExpProjTab.Delete;
963 l_ExpTaskTab.Delete;
967 l_ExpPeriodTab.Delete;
964 l_ExpExpTypTab.Delete;
965 l_ExpEiDateTab.Delete;
966 l_ExpExpOrgTab.Delete;
968 l_ExpPdYearTab.Delete;
969 l_ExpPdNumTab.Delete;
970 l_ExpDocDistTab.Delete;
971 l_ExpDocHdrTab.Delete;
972 l_ExpEntDrTab.Delete;
973 l_ExpEntCrTab.Delete;
974 l_ExpAcctDrTab.Delete;
975 l_ExpAcctCrTab.Delete;
976 l_ExpGlDateTab.Delete;
977 l_ExpPaDateTab.Delete;
978 l_ExpTxnCCIDTab.Delete;
979 l_ExpOrgIdTab.Delete;
980 l_ExpBdgtCCIDTab.Delete;
981 --PA.M
982 l_ExpPoLineIdTab.Delete;
983 l_ExpReference1Tab.delete;
984 l_ExpReference2Tab.delete;
985 l_ExpReference3Tab.delete;
986 l_ExpParBcPktIdTab.delete;
987
988 IF P_DEBUG_MODE = 'Y' THEN
989 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Before Fetch of c_vierbtc');
990 END IF;
991
992 --Insert ER, VI, BTC
993 FETCH c_VIERBTC_txns bulk collect into
994 l_ExpProjTab,
995 l_ExpTaskTab,
996 l_ExpExpTypTab,
997 l_ExpEiDateTab,
998 l_ExpExpOrgTab,
999 l_ExpPeriodTab,
1000 l_ExpPdYearTab,
1001 l_ExpPdNumTab,
1002 l_ExpDocHdrTab,
1003 l_ExpDocDistTab,
1004 l_ExpEntDrTab,
1005 l_ExpEntCrTab,
1006 l_ExpAcctDrTab,
1007 l_ExpAcctCrTab,
1008 l_ExpGlDateTab,
1009 l_ExpPaDateTab,
1010 l_ExpTxnCCIDTab,
1011 l_ExpOrgIdTab,
1012 l_ExpBdgtCCIDTab
1013 --PA.M
1014 ,l_ExpPOLineIdTab
1015 ,l_ExpReference1Tab
1016 ,l_ExpReference2Tab
1017 ,l_ExpReference3Tab
1018 ,l_ExpParBcPktIdTab
1019 LIMIT rows;
1020
1021 IF P_DEBUG_MODE = 'Y' THEN
1022 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Fetched ER VI BTC, count = ' || l_ExpProjTab.count);
1023 END IF;
1024
1025 IF l_ExpProjTab.count=0 THEN
1026 IF P_DEBUG_MODE = 'Y' THEN
1027 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'No rec in cursor c_vierbtc exit');
1028 END IF;
1029 exit;
1030 END IF;
1031
1032 IF P_DEBUG_MODE = 'Y' THEN
1033 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Before inserting VIERBTC');
1034 END IF;
1035 FORALL m in l_ExpProjTab.FIRST..l_ExpProjTab.LAST
1036 insert into pa_bc_packets (
1037 PACKET_ID,
1038 PROJECT_ID,
1039 TASK_ID,
1040 EXPENDITURE_TYPE,
1041 EXPENDITURE_ITEM_DATE,
1042 ACTUAL_FLAG,
1043 STATUS_CODE,
1044 LAST_UPDATE_DATE,
1045 LAST_UPDATED_BY,
1046 CREATED_BY,
1047 CREATION_DATE,
1048 LAST_UPDATE_LOGIN,
1049 SET_OF_BOOKS_ID,
1050 JE_CATEGORY_NAME,
1051 JE_SOURCE_NAME,
1052 DOCUMENT_TYPE,
1053 EXPENDITURE_ORGANIZATION_ID,
1054 PERIOD_NAME,
1055 PERIOD_YEAR,
1056 PERIOD_NUM,
1057 DOCUMENT_HEADER_ID,
1058 DOCUMENT_DISTRIBUTION_ID,
1059 ENTERED_DR,
1060 ENTERED_CR,
1061 accounted_dr,
1062 accounted_cr,
1063 BUDGET_VERSION_ID,
1064 bc_packet_id,
1065 funds_process_mode,
1066 parent_bc_packet_id,
1067 gl_date,
1068 pa_date,
1069 txn_ccid,
1070 result_code,
1071 balance_posted_flag,
1072 org_id,
1073 burden_cost_flag,
1074 old_budget_ccid
1075 --PA.M
1076 ,document_line_id
1077 ,reference1
1078 ,reference2
1079 ,reference3 )
1080 select
1081 p_packet_id,
1082 l_ExpProjTab(m),
1083 l_ExpTaskTab(m),
1084 l_ExpExpTypTab(m),
1085 l_ExpEiDateTab(m),
1086 'A',
1087 'P',
1088 sysdate,
1089 FND_GLOBAL.USER_ID,
1090 FND_GLOBAL.USER_ID,
1091 sysdate,
1092 FND_GLOBAL.LOGIN_ID,
1093 p_sob_id,
1094 'Project Accounting',
1095 'Expenditures',
1096 'EXP',
1097 l_ExpExpOrgTab(m),
1098 l_ExpPeriodTab(m),
1099 l_ExpPdYearTab(m),
1100 l_ExpPdNumTab(m),
1101 l_ExpDocHdrTab(m),
1102 l_ExpDocDistTab(m),
1103 l_ExpEntDrTab(m),
1104 l_ExpEntCrTab(m),
1105 l_ExpAcctDrTab(m),
1106 l_ExpAcctCrTab(m),
1107 p_budget_version_id,
1108 pa_bc_packets_s.nextval,
1109 'B',
1110 --PA.M insert parent bc pkt as -99 for CWK BTC EIs
1111 --null,
1115 l_ExpTxnCCIDTab(m),
1112 l_ExpParBcPktIdTab(m),
1113 l_ExpGlDateTab(m),
1114 l_ExpPaDateTab(m),
1116 'P',
1117 'N',
1118 l_ExpOrgIdTab(m),
1119 'N',
1120 l_ExpBdgtCCIDTab(m)
1121 --PA.M
1122 ,l_ExpPoLineIdTab(m)
1123 ,l_ExpReference1Tab(m)
1124 ,l_ExpReference2Tab(m)
1125 ,l_ExpReference3Tab(m)
1126 from dual;
1127
1128 commit;
1129 EXIT WHEN c_VIERBTC_txns%NOTFOUND;
1130 END LOOP;
1131 CLOSE c_VIERBTC_txns;
1132 PA_DEBUG.Reset_Err_Stack; --3912094
1133 PA_DEBUG.set_err_stack('Inserted VIERBTC Txns');
1134
1135 IF P_DEBUG_MODE = 'Y' THEN
1136 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Exiting VIERBTC');
1137 END IF;
1138
1139 --Reset the error stack when returning to the calling program
1140 PA_DEBUG.Reset_Err_Stack;
1141
1142 EXCEPTION
1143 WHEN OTHERS THEN
1144 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_BGT_BASELINE_PKG'
1145 ,p_procedure_name => 'INSERT_VI_ER_BTC_TXNS' -- Bug 5064900
1146 ,p_error_text => PA_DEBUG.G_Err_Stack );
1147
1148 IF c_VIERBTC_txns%ISOPEN THEN
1149 close c_VIERBTC_txns;
1150 END IF;
1151
1152 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1153 x_error_message_code := (SQLCODE||' '||SQLERRM);
1154 PA_DEBUG.Reset_Err_Stack; -- Bug 5064900
1155 RAISE;
1156 END INSERT_VI_ER_BTC_TXNS;
1157 ---------------------------------------------------------------------------------------------
1158
1159 -- procedure to load all commitment transactions in pa_bc_packets for funds check
1160 PROCEDURE INSERT_COMMITMENT_TXNS(
1161 p_packet_id IN NUMBER,
1162 p_sob_id IN NUMBER,
1163 p_project_id IN NUMBER,
1164 p_budget_version_id IN NUMBER,
1165 p_bdgt_ctrl_type IN VARCHAR2,
1166 p_bdgt_intg_flag IN VARCHAR2,
1167 x_return_status OUT NOCOPY VARCHAR2,
1168 x_error_message_code OUT NOCOPY VARCHAR2) IS
1169
1170 l_ProjTab PA_PLSQL_DATATYPES.IdTabTyp;
1171 l_TaskTab PA_PLSQL_DATATYPES.IdTabTyp;
1172 l_ExpTypTab PA_PLSQL_DATATYPES.Char30TabTyp;
1173 l_EiDateTab PA_PLSQL_DATATYPES.DateTabTyp;
1174 l_SobTab PA_PLSQL_DATATYPES.IdTabTyp;
1175 l_CatNameTab PA_PLSQL_DATATYPES.Char30TabTyp;
1176 l_SrcNameTab PA_PLSQL_DATATYPES.Char30TabTyp;
1177 l_DocTypTab PA_PLSQL_DATATYPES.Char10TabTyp;
1178 l_ExpOrgTab PA_PLSQL_DATATYPES.IdTabTyp;
1179 l_PeriodTab PA_PLSQL_DATATYPES.Char15TabTyp;
1180 l_PdYearTab PA_PLSQL_DATATYPES.NumTabTyp;
1181 l_PdNumTab PA_PLSQL_DATATYPES.NumTabTyp;
1182 l_DocHdrTab PA_PLSQL_DATATYPES.IdTabTyp;
1183 l_DocDistTab PA_PLSQL_DATATYPES.IdTabTyp;
1184 l_EntDrTab PA_PLSQL_DATATYPES.NumTabTyp;
1185 l_EntCrTab PA_PLSQL_DATATYPES.NumTabTyp;
1186 l_AcctDrTab PA_PLSQL_DATATYPES.NumTabTyp;
1187 l_AcctCrTab PA_PLSQL_DATATYPES.NumTabTyp;
1188 l_BcPktTab PA_PLSQL_DATATYPES.IdTabTyp;
1189 l_ParBCPktTab PA_PLSQL_DATATYPES.IdTabTyp;
1190 l_GlDateTab PA_PLSQL_DATATYPES.DateTabTyp;
1191 l_PaDateTab PA_PLSQL_DATATYPES.DateTabTyp;
1192 l_TxnCCIDTab PA_PLSQL_DATATYPES.IdTabTyp;
1193 l_ParResTab PA_PLSQL_DATATYPES.IdTabTyp;
1194 l_OrgIdTab PA_PLSQL_DATATYPES.IdTabTyp;
1195 l_BurCstFlagTab PA_PLSQL_DATATYPES.Char1TabTyp;
1196 l_BcCommIdTab PA_PLSQL_DATATYPES.IdTabTyp;
1197 l_BdgtCCIDTab PA_PLSQL_DATATYPES.IdTabTyp;
1198 --PA.M
1199 l_DocLineIdTab PA_PLSQL_DATATYPES.IdTabTyp;
1200 l_SummRecFlagTab PA_PLSQL_DATATYPES.Char1TabTyp;
1201 l_PktReference1Tab PA_PLSQL_DATATYPES.Char80TabTyp;
1202 l_PktReference2Tab PA_PLSQL_DATATYPES.Char80TabTyp;
1203 l_PktReference3Tab PA_PLSQL_DATATYPES.Char80TabTyp;
1204 -- R12 Funds Management Uptake
1205 l_BurMethodcodeTab PA_PLSQL_DATATYPES.Char30TabTyp;
1206
1207 --Cursor to select all commitment transactions from pa_bc_commitments_all
1208 --for a given project_id and the current baselined budget version id. The
1209 --check for later is because there might be commitments with old budget
1210 --version id which were funds checked during the delta time.
1211 --Removing the current baselined budget check as now we are handling that
1212 --there is only one commitment line for a tx. This is done in the sweeper while
1213 --moving the delta and new tx. to commitments.
1214 cursor c_bc_comm(p_project_id in number) is
1215 select bc.project_id,
1216 bc.task_id,
1217 bc.EXPENDITURE_TYPE,
1218 trunc(bc.EXPENDITURE_ITEM_DATE) expenditure_item_date,
1219 bc.set_of_books_id,
1220 bc.je_category_name,
1221 bc.je_source_name,
1222 bc.document_type,
1223 bc.expenditure_organization_id,
1224 bc.PERIOD_NAME,
1225 bc.PERIOD_YEAR,
1226 bc.PERIOD_NUM,
1227 bc.document_header_id,
1228 bc.document_distribution_id,
1229 bc.entered_dr,
1230 bc.entered_cr,
1231 bc.accounted_dr,
1232 bc.accounted_cr,
1233 bc.bc_packet_id,
1234 bc.parent_bc_packet_id,
1235 bc.gl_date,
1236 bc.pa_date,
1237 bc.txn_ccid,
1238 bc.org_id,
1239 bc.burden_cost_flag,
1240 bc.bc_commitment_id,
1241 bc.budget_ccid
1242 --PA.M
1246 ,bc.reference2
1243 ,bc.document_line_id
1244 ,bc.summary_record_flag
1245 ,bc.reference1
1247 ,bc.reference3
1248 -- R12 Funds Management Uptake
1249 ,bc.burden_method_code
1250 from pa_bc_commitments_all bc,
1251 pa_tasks pt
1252 --,pa_budget_versions pbv,
1253 --pa_budget_types pbt
1254 where bc.project_id = p_project_id
1255 and pt.task_id = bc.task_id;
1256 --and bc.budget_version_id = pbv.budget_version_id
1257 --and pbv.current_flag = 'Y'
1258 --and pbv.budget_status_code = 'B'
1259 --and pbv.budget_type_code = pbt.budget_type_code
1260 --and pbt.budget_amount_code = 'C';
1261 --and bc.budget_version_id = p_budget_version_id;
1262
1263 BEGIN
1264
1265 --Initialize the error stack
1266 PA_DEBUG.set_err_stack('Insert Commitment Txns');
1267
1268 --Initialize the return status to success
1269 x_return_status := FND_API.G_RET_STS_SUCCESS;
1270
1271 IF P_DEBUG_MODE = 'Y' THEN
1272 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Entering Insert Commitment Txns');
1273 END IF;
1274
1275 OPEN c_bc_comm(p_project_id);
1276 LOOP
1277
1278 --Initialize pl/sql tables
1279 l_ProjTab.Delete;
1280 l_TaskTab.Delete;
1281 l_ExpTypTab.Delete;
1282 l_EiDateTab.Delete;
1283 l_SobTab.Delete;
1284 l_CatNameTab.Delete;
1285 l_SrcNameTab.Delete;
1286 l_DocTypTab.Delete;
1287 l_ExpOrgTab.Delete;
1288 l_PeriodTab.Delete;
1289 l_PdYearTab.Delete;
1290 l_PdNumTab.Delete;
1291 l_DocHdrTab.Delete;
1292 l_DocDistTab.Delete;
1293 l_EntDrTab.Delete;
1294 l_EntCrTab.Delete;
1295 l_AcctDrTab.Delete;
1296 l_AcctCrTab.Delete;
1297 l_BcPktTab.Delete;
1298 l_ParBCPktTab.Delete;
1299 l_GlDateTab.Delete;
1300 l_PaDateTab.Delete;
1301 l_TxnCCIDTab.Delete;
1302 l_ParResTab.Delete;
1303 l_OrgIdTab.Delete;
1304 l_BurCstFlagTab.Delete;
1305 l_BcCommIdTab.Delete;
1306 l_BdgtCCIDTab.Delete;
1307 --PA.M
1308 l_DocLineIdTab.Delete;
1309 l_SummRecFlagTab.Delete;
1310 l_pktReference1Tab.Delete;
1311 l_pktReference2Tab.Delete;
1312 l_pktReference3Tab.Delete;
1313 -- R12 Funds Management Uptake
1314 l_BurMethodcodeTab.delete;
1315
1316 IF P_DEBUG_MODE = 'Y' THEN
1317 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Fetch c_bc_comm cursor');
1318 END IF;
1319
1320 FETCH c_bc_comm BULK COLLECT INTO
1321 l_ProjTab,
1322 l_TaskTab,
1323 l_ExpTypTab,
1324 l_EiDateTab,
1325 l_SobTab,
1326 l_CatNameTab,
1327 l_SrcNameTab,
1328 l_DocTypTab,
1329 l_ExpOrgTab,
1330 l_PeriodTab,
1331 l_PdYearTab,
1332 l_PdNumTab,
1333 l_DocHdrTab,
1334 l_DocDistTab,
1335 l_EntDrTab,
1336 l_EntCrTab,
1337 l_AcctDrTab,
1338 l_AcctCrTab,
1339 l_BcPktTab,
1340 l_ParBCPktTab,
1341 l_GlDateTab,
1342 l_PaDateTab,
1343 l_TxnCCIDTab,
1344 l_OrgIdTab,
1345 l_BurCstFlagTab,
1346 l_BcCommIdTab,
1347 l_BdgtCCIDTab
1348 --l_ParResTab
1349 --Pa.M
1350 , l_DocLineIdTab
1351 , l_SummRecFlagTab
1352 ,l_pktReference1Tab
1353 ,l_pktReference2Tab
1354 ,l_pktReference3Tab
1355 -- R12 Funds Management Uptake
1356 ,l_BurMethodcodeTab
1357 LIMIT rows;
1358
1359 IF l_ProjTab.count = 0 THEN
1360 IF P_DEBUG_MODE = 'Y' THEN
1361 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'No records in c_bc_comm, exit');
1362 END IF;
1363 exit;
1364 END IF;
1365
1366 --If budgetary control type is CC, then load only contract commitment transactions into
1367 --pa_bc_packets. Also if budget is linked then consider only open period
1368 --transactions else consider all transactions.
1369 IF P_DEBUG_MODE = 'Y' THEN
1370 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'No. of rec in c_bc_comm = '|| l_ProjTab.count);
1371 END IF;
1372
1373 IF (p_bdgt_ctrl_type = 'CC') THEN
1374
1375 PA_DEBUG.set_err_stack('CC'); --Bug 3912094
1376 IF P_DEBUG_MODE = 'Y' THEN
1377 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Bdgt Ctrl = CC');
1378 END IF;
1379
1380 -- Insert Contract Commitments
1381 IF (p_bdgt_intg_flag = 'Y') THEN
1382
1383 PA_DEBUG.set_err_stack('Insert CC, linked'); --Bug 3912094
1384 IF P_DEBUG_MODE = 'Y' THEN
1385 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Bdgt Intg Flag = Y');
1386 END IF;
1387 FORALL i in l_ProjTab.FIRST..l_ProjTab.LAST
1388 insert into pa_bc_packets (
1389 PACKET_ID,
1390 PROJECT_ID,
1391 TASK_ID,
1392 EXPENDITURE_TYPE,
1393 EXPENDITURE_ITEM_DATE,
1394 ACTUAL_FLAG,
1395 STATUS_CODE,
1396 LAST_UPDATE_DATE,
1397 LAST_UPDATED_BY,
1398 CREATED_BY,
1399 CREATION_DATE,
1400 LAST_UPDATE_LOGIN,
1401 SET_OF_BOOKS_ID,
1402 JE_CATEGORY_NAME,
1406 PERIOD_NAME,
1403 JE_SOURCE_NAME,
1404 DOCUMENT_TYPE,
1405 EXPENDITURE_ORGANIZATION_ID,
1407 PERIOD_YEAR,
1408 PERIOD_NUM,
1409 DOCUMENT_HEADER_ID,
1410 DOCUMENT_DISTRIBUTION_ID,
1411 ENTERED_DR,
1412 ENTERED_CR,
1413 accounted_dr,
1414 accounted_cr,
1415 BUDGET_VERSION_ID,
1416 bc_packet_id,
1417 funds_process_mode,
1418 parent_bc_packet_id,
1419 gl_date,
1420 pa_date,
1421 txn_ccid,
1422 result_code,
1423 balance_posted_flag,
1424 org_id,
1425 burden_cost_flag,
1426 bc_commitment_id,
1427 old_budget_ccid
1428 --PA.M
1429 ,document_line_id
1430 ,summary_record_flag
1431 ,reference1
1432 ,reference2
1433 ,reference3
1434 -- R12 Funds Management Uptake
1435 ,burden_method_code
1436 )
1437 select
1438 p_packet_id,
1439 l_ProjTab(i),
1440 l_TaskTab(i),
1441 l_ExpTypTab(i),
1442 l_EiDateTab(i),
1443 'E',
1444 'P',
1445 sysdate,
1446 FND_GLOBAL.USER_ID,
1447 FND_GLOBAL.USER_ID,
1448 sysdate,
1449 FND_GLOBAL.LOGIN_ID,
1450 l_SobTab(i),
1451 l_CatNameTab(i),
1452 l_SrcNameTab(i),
1453 l_DocTypTab(i),
1454 l_ExpOrgTab(i),
1455 l_PeriodTab(i),
1456 l_PdYearTab(i),
1457 l_PdNumTab(i),
1458 l_DocHdrTab(i),
1459 l_DocDistTab(i),
1460 l_EntDrTab(i),
1461 l_EntCrTab(i),
1462 l_AcctDrTab(i),
1463 l_AcctCrTab(i),
1464 p_budget_version_id,
1465 l_BcPktTab(i),
1466 'B',
1467 l_ParBCPktTab(i),
1468 l_GlDateTab(i),
1469 l_PaDateTab(i),
1470 l_TxnCCIDTab(i),
1471 'P',
1472 'N',
1473 l_OrgIdTab(i),
1474 l_BurCstFlagTab(i),
1475 l_BcCommIdTab(i),
1476 l_BdgtCCIDTab(i)
1477 --Pa.M
1478 ,l_DocLineIdTab(i)
1479 ,l_SummRecFlagTab(i)
1480 ,l_pktReference1Tab(i)
1481 ,l_pktReference2Tab(i)
1482 ,l_pktReference3Tab(i)
1483 -- R12 Funds Management Uptake
1484 ,l_BurMethodcodeTab(i)
1485 from gl_period_statuses gl
1486 where l_DocTypTab(i) in ('CC_C_CO', 'CC_P_CO')
1487 --and l_GlDateTab(i) = gl.end_date
1488 and trunc(l_GlDateTab(i)) between trunc(gl.start_date) and trunc(gl.end_date)
1489 and gl.application_id = 101
1490 and gl.set_of_books_id = p_sob_id
1491 and gl.adjustment_period_flag = 'N'
1492 and gl.closing_status in ( 'O', 'F');
1493
1494 for i in l_ProjTab.FIRST..l_ProjTab.LAST loop
1495 IF P_DEBUG_MODE = 'Y' THEN
1496 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'CC - Y, record count = '|| SQL%BULK_ROWCOUNT(i));
1497 END IF;
1498 end loop;
1499
1500 PA_DEBUG.Reset_Err_Stack; --3912094
1501 ELSE
1502
1503 PA_DEBUG.set_err_stack('Insert CC, no link'); --Bug 3912094
1504 IF P_DEBUG_MODE = 'Y' THEN
1505 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Bdgt Intg Flag = N');
1506 END IF;
1507
1508 FORALL j in l_ProjTab.FIRST..l_ProjTab.LAST
1509 insert into pa_bc_packets (
1510 PACKET_ID,
1511 PROJECT_ID,
1512 TASK_ID,
1513 EXPENDITURE_TYPE,
1514 EXPENDITURE_ITEM_DATE,
1515 ACTUAL_FLAG,
1516 STATUS_CODE,
1517 LAST_UPDATE_DATE,
1518 LAST_UPDATED_BY,
1519 CREATED_BY,
1520 CREATION_DATE,
1521 LAST_UPDATE_LOGIN,
1522 SET_OF_BOOKS_ID,
1523 JE_CATEGORY_NAME,
1524 JE_SOURCE_NAME,
1525 DOCUMENT_TYPE,
1526 EXPENDITURE_ORGANIZATION_ID,
1527 PERIOD_NAME,
1528 PERIOD_YEAR,
1529 PERIOD_NUM,
1530 DOCUMENT_HEADER_ID,
1531 DOCUMENT_DISTRIBUTION_ID,
1532 ENTERED_DR,
1533 ENTERED_CR,
1534 accounted_dr,
1535 accounted_cr,
1536 BUDGET_VERSION_ID,
1537 bc_packet_id,
1538 funds_process_mode,
1539 parent_bc_packet_id,
1540 gl_date,
1541 pa_date,
1542 txn_ccid,
1543 result_code,
1544 balance_posted_flag,
1545 org_id,
1546 burden_cost_flag,
1547 bc_commitment_id,
1548 old_budget_ccid
1549 --PA.M
1550 ,document_line_id
1551 ,summary_record_flag
1552 ,reference1
1553 ,reference2
1557 )
1554 ,reference3
1555 -- R12 Funds Management Uptake
1556 ,burden_method_code
1558 select
1559 p_packet_id,
1560 l_ProjTab(j),
1561 l_TaskTab(j),
1562 l_ExpTypTab(j),
1563 l_EiDateTab(j),
1564 'E',
1565 'P',
1566 sysdate,
1567 FND_GLOBAL.USER_ID,
1568 FND_GLOBAL.USER_ID,
1569 sysdate,
1570 FND_GLOBAL.LOGIN_ID,
1571 l_SobTab(j),
1572 l_CatNameTab(j),
1573 l_SrcNameTab(j),
1574 l_DocTypTab(j),
1575 l_ExpOrgTab(j),
1576 l_PeriodTab(j),
1577 l_PdYearTab(j),
1578 l_PdNumTab(j),
1579 l_DocHdrTab(j),
1580 l_DocDistTab(j),
1581 l_EntDrTab(j),
1582 l_EntCrTab(j),
1583 l_AcctDrTab(j),
1584 l_AcctCrTab(j),
1585 p_budget_version_id,
1586 l_BcPktTab(j),
1587 'B',
1588 l_ParBCPktTab(j),
1589 l_GlDateTab(j),
1590 l_PaDateTab(j),
1591 l_TxnCCIDTab(j),
1592 'P',
1593 'N',
1594 l_OrgIdTab(j),
1595 l_BurCstFlagTab(j),
1596 l_BcCommIdTab(j),
1597 l_BdgtCCIDTab(j)
1598 --Pa.M
1599 ,l_DocLineIdTab(j)
1600 , l_SummRecFlagTab(j)
1601 ,l_pktReference1Tab(j)
1602 ,l_pktReference2Tab(j)
1603 ,l_pktReference3Tab(j)
1604 -- R12 Funds Management Uptake
1605 ,l_BurMethodcodeTab(j)
1606 from dual
1607 where l_DocTypTab(j) in ('CC_C_CO', 'CC_P_CO');
1608
1609 for i in l_ProjTab.FIRST..l_ProjTab.LAST loop
1610 IF P_DEBUG_MODE = 'Y' THEN
1611 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'CC - N, record count = '|| SQL%BULK_ROWCOUNT(i));
1612 END IF;
1613 end loop;
1614
1615 PA_DEBUG.Reset_Err_Stack; --3912094
1616 END IF;
1617 PA_DEBUG.Reset_Err_Stack; --3912094
1618
1619 --If budgetary control type is GL, then load all commitment transactions
1620 --(AP, PO, REQ, Contract Payments) into pa_bc_packets.
1621 --Also check if budget is linked then consider only open period
1622 --transactions else consider all transactions.
1623 ELSIF (p_bdgt_ctrl_type = ('GL')) THEN
1624
1625 PA_DEBUG.set_err_stack('GL'); --Bug 3912094
1626 IF P_DEBUG_MODE = 'Y' THEN
1627 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Bdgt Ctrl Type = GL');
1628 END IF;
1629
1630 IF (P_bdgt_intg_flag = 'Y') THEN
1631
1632 PA_DEBUG.set_err_stack('Insert Std, linked');
1633 IF P_DEBUG_MODE = 'Y' THEN
1634 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Bdgt Intg Flag = Y');
1635 END IF;
1636
1637 --For Other commitment transactions (AP,PO,REQ,CC_P_PAY,CC_C_PAY)
1638 --when there is a link
1639 FORALL k in l_ProjTab.FIRST..l_ProjTab.LAST
1640 insert into pa_bc_packets (
1641 PACKET_ID,
1642 PROJECT_ID,
1643 TASK_ID,
1644 EXPENDITURE_TYPE,
1645 EXPENDITURE_ITEM_DATE,
1646 ACTUAL_FLAG,
1647 STATUS_CODE,
1648 LAST_UPDATE_DATE,
1649 LAST_UPDATED_BY,
1650 CREATED_BY,
1651 CREATION_DATE,
1652 LAST_UPDATE_LOGIN,
1653 SET_OF_BOOKS_ID,
1654 JE_CATEGORY_NAME,
1655 JE_SOURCE_NAME,
1656 DOCUMENT_TYPE,
1657 EXPENDITURE_ORGANIZATION_ID,
1658 PERIOD_NAME,
1659 PERIOD_YEAR,
1660 PERIOD_NUM,
1661 DOCUMENT_HEADER_ID,
1662 DOCUMENT_DISTRIBUTION_ID,
1663 ENTERED_DR,
1664 ENTERED_CR,
1665 accounted_dr,
1666 accounted_cr,
1667 BUDGET_VERSION_ID,
1668 bc_packet_id,
1669 funds_process_mode,
1670 parent_bc_packet_id,
1671 gl_date,
1672 pa_date,
1673 txn_ccid,
1674 result_code,
1675 balance_posted_flag,
1676 org_id,
1677 burden_cost_flag,
1678 bc_commitment_id,
1679 old_budget_ccid
1680 --PA.M
1681 ,document_line_id
1682 ,summary_record_flag
1683 ,reference1
1684 ,reference2
1685 ,reference3
1686 -- R12 Funds Management Uptake
1687 ,burden_method_code
1688 )
1689 select
1690 p_packet_id,
1691 l_ProjTab(k),
1692 l_TaskTab(k),
1693 l_ExpTypTab(k),
1694 l_EiDateTab(k),
1695 'E',
1696 'P',
1697 sysdate,
1698 FND_GLOBAL.USER_ID,
1699 FND_GLOBAL.USER_ID,
1700 sysdate,
1701 FND_GLOBAL.LOGIN_ID,
1705 l_DocTypTab(k),
1702 l_SobTab(k),
1703 l_CatNameTab(k),
1704 l_SrcNameTab(k),
1706 l_ExpOrgTab(k),
1707 l_PeriodTab(k),
1708 l_PdYearTab(k),
1709 l_PdNumTab(k),
1710 l_DocHdrTab(k),
1711 l_DocDistTab(k),
1712 l_EntDrTab(k),
1713 l_EntCrTab(k),
1714 l_AcctDrTab(k),
1715 l_AcctCrTab(k),
1716 p_budget_version_id,
1717 l_BcPktTab(k),
1718 'B',
1719 l_ParBCPktTab(k),
1720 l_GlDateTab(k),
1721 l_PaDateTab(k),
1722 l_TxnCCIDTab(k),
1723 'P',
1724 'N',
1725 l_OrgIdTab(k),
1726 l_BurCstFlagTab(k),
1727 l_BcCommIdTab(k),
1728 l_BdgtCCIDTab(k)
1729 --Pa.M
1730 ,l_DocLineIdTab(k)
1731 , l_SummRecFlagTab(k)
1732 ,l_pktReference1Tab(k)
1733 ,l_pktReference2Tab(k)
1734 ,l_pktReference3Tab(k)
1735 -- R12 Funds Management Uptake
1736 ,l_BurMethodcodeTab(k)
1737 from gl_period_statuses gl
1738 where l_DocTypTab(k) in ('AP','PO','REQ','CC_C_PAY','CC_P_PAY')
1739 --and l_GlDateTab(k) = gl.end_date
1740 and trunc(l_GlDateTab(k)) between trunc(gl.start_date) and trunc(gl.end_date)
1741 and gl.application_id = 101
1742 and gl.set_of_books_id = p_sob_id
1743 and gl.adjustment_period_flag = 'N'
1744 and gl.closing_status in ( 'O', 'F');
1745
1746 for i in l_ProjTab.FIRST..l_ProjTab.LAST loop
1747 IF P_DEBUG_MODE = 'Y' THEN
1748 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'GL - Y, record count = '|| SQL%BULK_ROWCOUNT(i));
1749 END IF;
1750 end loop;
1751 PA_DEBUG.Reset_Err_Stack; --3912094
1752 ELSE
1753
1754 PA_DEBUG.set_err_stack('Insert Std, no link');
1755 IF P_DEBUG_MODE = 'Y' THEN
1756 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Bdgt Intg Flag = N');
1757 END IF;
1758
1759 --For Other commitment transactions (AP,PO,REQ,CC_C_PAY,CC_P_PAY)
1760 --when there is a link
1761 FORALL l in l_ProjTab.FIRST..l_ProjTab.LAST
1762 insert into pa_bc_packets (
1763 PACKET_ID,
1764 PROJECT_ID,
1765 TASK_ID,
1766 EXPENDITURE_TYPE,
1767 EXPENDITURE_ITEM_DATE,
1768 ACTUAL_FLAG,
1769 STATUS_CODE,
1770 LAST_UPDATE_DATE,
1771 LAST_UPDATED_BY,
1772 CREATED_BY,
1773 CREATION_DATE,
1774 LAST_UPDATE_LOGIN,
1775 SET_OF_BOOKS_ID,
1776 JE_CATEGORY_NAME,
1777 JE_SOURCE_NAME,
1778 DOCUMENT_TYPE,
1779 EXPENDITURE_ORGANIZATION_ID,
1780 PERIOD_NAME,
1781 PERIOD_YEAR,
1782 PERIOD_NUM,
1783 DOCUMENT_HEADER_ID,
1784 DOCUMENT_DISTRIBUTION_ID,
1785 ENTERED_DR,
1786 ENTERED_CR,
1787 accounted_dr,
1788 accounted_cr,
1789 BUDGET_VERSION_ID,
1790 bc_packet_id,
1791 funds_process_mode,
1792 parent_bc_packet_id,
1793 gl_date,
1794 pa_date,
1795 txn_ccid,
1796 result_code,
1797 balance_posted_flag,
1798 org_id,
1799 burden_cost_flag,
1800 bc_commitment_id,
1801 old_budget_ccid
1802 --PA.M
1803 ,document_line_id
1804 ,summary_record_flag
1805 ,reference1
1806 ,reference2
1807 ,reference3
1808 -- R12 Funds Management Uptake
1809 ,burden_method_code
1810 )
1811 select
1812 p_packet_id,
1813 l_ProjTab(l),
1814 l_TaskTab(l),
1815 l_ExpTypTab(l),
1816 l_EiDateTab(l),
1817 'E',
1818 'P',
1819 sysdate,
1820 FND_GLOBAL.USER_ID,
1821 FND_GLOBAL.USER_ID,
1822 sysdate,
1823 FND_GLOBAL.LOGIN_ID,
1824 l_SobTab(l),
1825 l_CatNameTab(l),
1826 l_SrcNameTab(l),
1827 l_DocTypTab(l),
1828 l_ExpOrgTab(l),
1829 l_PeriodTab(l),
1830 l_PdYearTab(l),
1831 l_PdNumTab(l),
1832 l_DocHdrTab(l),
1833 l_DocDistTab(l),
1834 l_EntDrTab(l),
1835 l_EntCrTab(l),
1836 l_AcctDrTab(l),
1837 l_AcctCrTab(l),
1838 p_budget_version_id,
1839 l_BcPktTab(l),
1840 'B',
1841 l_ParBCPktTab(l),
1842 l_GlDateTab(l),
1843 l_PaDateTab(l),
1844 l_TxnCCIDTab(l),
1845 'P',
1846 'N',
1850 l_BdgtCCIDTab(l)
1847 l_OrgIdTab(l),
1848 l_BurCstFlagTab(l),
1849 l_BcCommIdTab(l),
1851 --Pa.M
1852 , l_DocLineIdTab(l)
1853 , l_SummRecFlagTab(l)
1854 ,l_pktReference1Tab(l)
1855 ,l_pktReference2Tab(l)
1856 ,l_pktReference3Tab(l)
1857 -- R12 Funds Management Uptake
1858 ,l_BurMethodcodeTab(l)
1859 from dual
1860 where l_DocTypTab(l) in ('AP','PO','REQ','CC_C_PAY','CC_P_PAY');
1861
1862 for i in l_ProjTab.FIRST..l_ProjTab.LAST loop
1863 IF P_DEBUG_MODE = 'Y' THEN
1864 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'GL - N, record count = '|| SQL%BULK_ROWCOUNT(i));
1865 END IF;
1866 end loop;
1867 PA_DEBUG.Reset_Err_Stack; --3912094
1868
1869 END IF;
1870 PA_DEBUG.Reset_Err_Stack; --3912094
1871 END IF;
1872 commit;
1873 EXIT WHEN c_bc_comm%NOTFOUND;
1874 END LOOP;
1875 CLOSE c_bc_comm;
1876
1877 PA_DEBUG.set_err_stack('Inserted Commitments');
1878
1879 IF P_DEBUG_MODE = 'Y' THEN
1880 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Exiting Insert Commitment Txns');
1881 END IF;
1882
1883 --Reset the error stack when returning to the calling program
1884 PA_DEBUG.Reset_Err_Stack;
1885 PA_DEBUG.Reset_Err_Stack; -- Bug 5064900
1886
1887 EXCEPTION
1888 WHEN OTHERS THEN
1889 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_BGT_BASELINE_PKG'
1890 ,p_procedure_name => 'INSERT_COMMITMENT_TXNS' -- Bug 5064900
1891 ,p_error_text => PA_DEBUG.G_Err_Stack );
1892
1893 IF c_bc_comm%ISOPEN THEN
1894 close c_bc_comm;
1895 END IF;
1896
1897 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1898 x_error_message_code := (SQLCODE||' '||SQLERRM);
1899 PA_DEBUG.Reset_Err_Stack; -- Bug 5064900
1900 RAISE;
1901 END INSERT_COMMITMENT_TXNS;
1902
1903 ---------------------------------------------------------------------------------------------
1904 --R12 Funds Management Uptake : As per new logic, fundscheck will be performed only during
1905 --budget baseline and not during submit.Hence obsoleting this procedure INSERT_DELTA_TXNS
1906 --which holds logic for performing delta fundscheck of transactions which were
1907 --fundschecked against submitted budget and having 'C' status code.
1908 ---------------------------------------------------------------------------------------------
1909
1910 --(MAIN)
1911 BEGIN
1912
1913 --Initialize the error stack
1914 PA_DEBUG.init_err_stack('PA_BGT_BASELINE_PKG.Maintain_Bal_Fchk');
1915
1916 fnd_msg_pub.initialize;
1917
1918 --Initialize the return status to success
1919 x_return_status := FND_API.G_RET_STS_SUCCESS;
1920
1921 IF P_DEBUG_MODE = 'Y' THEN
1922 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Passed parameters = ' || p_project_id || ':' || p_budget_version_id || ':' || p_bdgt_ctrl_type || ':' || p_calling_mode || ':' || p_bdgt_intg_flag);
1923 END IF;
1924
1925 --Bug 6524116
1926 SELECT org_id INTO l_org_id FROM pa_projects_all WHERE project_id = p_project_id;
1927
1928 --Select SET OF BOOKS ID
1929 IF P_DEBUG_MODE = 'Y' THEN
1930 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Before selecting SOB');
1931 END IF;
1932
1933 select to_number(set_of_books_id) into l_sob_id from pa_implementations_all --Bug 6524116
1934 where org_id = l_org_id;
1935
1936 IF P_DEBUG_MODE = 'Y' THEN
1937 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'After selecting SOB = '|| l_sob_id);
1938 END IF;
1939
1940 /* =============================================================================================== +
1941 FOLLOWING CODE MOVED TO PA_BUDGET_FUND_PKG (PABBFNDB.pls)
1942
1943 --Before start of baselining call sweeper to sweep approved packets sitting in
1944 --pa_bc_packets
1945 --Bug 2779986: Run sweeper irrespective of the p_bdgt_ctrl_type
1946 --Revert Bug 2779986 fix: Sweeper process is to be run only the first time this API is
1947 --called from PA_BUDGET_FUNDS_PKG. There are 2 calls to this API, first for
1948 --for p_bdgt_ctrl_type = GL and then for p_bdgt_ctrl_type = CC, hence running the
1949 --sweeper the first time will suffice as per design.
1950
1951 if (p_bdgt_ctrl_type = 'GL') then
1952 pa_sweeper.update_act_enc_balance(
1953 x_return_status => x_return_status,
1954 x_error_message_code => x_error_message_code
1955 --PA.M
1956 ,P_Project_Id => P_project_id);
1957 end if;
1958 ========================================================================================================== */
1959
1960 --lock pa_bc_balances records for the budget version
1961 open c_bal_lock;
1962
1963 --R12 Funds Management Uptake : Deleted logic to pouplate l_reservemode,l_bsnlpkt_id,l_fcreqd
1964 --and l_deltapktid as these variables are obsolete with new architecture.
1965 --Budget fundscheck will always be fired with calling mode = 'RESERVE_BASELINE' and fundscheck
1966 --has to be performed.
1967
1971 IF P_DEBUG_MODE = 'Y' THEN
1968 /* =============================================================================================== +
1969 FOLLOWING CODE MOVED TO PA_BUDGET_FUND_PKG (PABBFNDB.pls)
1970
1972 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Calling Insert BGT Balances');
1973 END IF;
1974 PA_DEBUG.set_err_stack('M:Insert BGT Balances');
1975 --create pa_bc_balances record
1976
1977 INSERT_BGT_BALANCES(
1978 p_project_id => p_project_id,
1979 p_budget_version_id => p_budget_version_id,
1980 p_set_of_books_id => l_sob_id,
1981 p_bdgt_intg_flag => p_bdgt_intg_flag,
1982 --p_fc_reqd => l_FcReqd, --R12 Funds Management Uptake : obsolete variable
1983 x_return_status => x_return_status,
1984 x_error_message_code => x_error_message_code );
1985
1986 IF P_DEBUG_MODE = 'Y' THEN
1987 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'After Insert BGT Balances, RetSts = ' || x_return_status);
1988 END IF;
1989
1990 PA_DEBUG.Reset_Err_Stack; --3912094
1991 ========================================================================================================== */
1992
1993 PA_DEBUG.set_err_stack('M:Is FC Reqd');
1994
1995 -- R12 Funds Management Uptake : Obsolete logic to DELETE packets associated with
1996 -- Draft version Id lying in status 'B'/'R'/'T'.With previous functionality these
1997 -- records were created if baselining errors for some reason after funds check OR
1998 -- if user rebaselines.
1999 -- With new architecture baselining is performed before fundscheck and fundscheck
2000 -- is fired only during baselining for the newly baselined version.If fundscheck fails
2001 -- then baselining will be rolled back and hence there wont be any 'B' status data
2002 -- lying in pa_bc_packets and sweeper will clean the 'R' and 'T' status records.
2003
2004 -- R12 Funds Management Uptake : Removed check of 'IF fc required'
2005
2006 -- Get a new packet id
2007 select gl_bc_packets_s.nextval into l_packet_id from dual;
2008
2009 IF P_DEBUG_MODE = 'Y' THEN
2010 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'PacketId = '|| l_packet_id);
2011 END IF;
2012
2013 PA_DEBUG.set_err_stack('M:Create Dir Cost');
2014
2015 -- Insert commitments into PA_BC_PACKETS
2016 IF P_DEBUG_MODE = 'Y' THEN
2017 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Calling Insert Commitment Txns');
2018 END IF;
2019
2020 INSERT_COMMITMENT_TXNS(
2021 p_packet_id => l_packet_id,
2022 p_sob_id => l_sob_id,
2023 p_project_id => p_project_id,
2024 p_budget_version_id => p_budget_version_id,
2025 p_bdgt_ctrl_type => p_bdgt_ctrl_type,
2026 p_bdgt_intg_flag => p_bdgt_intg_flag,
2027 x_return_status => x_return_status,
2028 x_error_message_code => x_error_message_code);
2029
2030 IF P_DEBUG_MODE = 'Y' THEN
2031 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'After Insert Commitment Txns, RetSts = ' ||x_return_status);
2032 END IF;
2033 PA_DEBUG.Reset_Err_Stack; --3912094
2034
2035 -- If Budgetary Control is GL then also
2036 -- insert vendor invoices, expense reports and BTC txns.
2037 if (p_bdgt_ctrl_type = 'GL') then
2038
2039 PA_DEBUG.set_err_stack('M:Insert VIERBTC');
2040 IF P_DEBUG_MODE = 'Y' THEN
2041 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Calling Insert VI ER BTC');
2042 END IF;
2043
2044 INSERT_VI_ER_BTC_TXNS(
2045 p_packet_id => l_packet_id,
2046 p_sob_id => l_sob_id,
2047 p_project_id => p_project_id,
2048 p_budget_version_id => p_budget_version_id,
2049 p_bdgt_intg_flag => p_bdgt_intg_flag,
2050 x_return_status => x_return_status,
2051 x_error_message_code => x_error_message_code);
2052 IF P_DEBUG_MODE = 'Y' THEN
2053 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'After Insert VI ER BTC, RetSts = ' ||x_return_status);
2054 END IF;
2055 PA_DEBUG.Reset_Err_Stack; --3912094
2056
2057 end if;
2058
2059 -- R12 Funds Management Uptake : Deleted call to main fundscheck API in 'CHECK_BASELINE' mode
2060 -- R12 Funds Management Uptake : Deleted call to PA_BUDGET_FUND_PKG.Upd_Bdgt_Acct_Bal_No_Fck as
2061 -- it will be handled during tieback
2062 -- R12 Funds Management Uptake : Deleted resrver mode check as this procedure is fired for reserve mode only
2063
2064 if (p_bdgt_ctrl_type = 'GL') then
2065
2066 pa_debug.Set_User_Lock_Mode(x_TimeOut => 10);
2067 --acquire lock
2068 IF (pa_debug.acquire_user_lock('BSLNFCHKLOCK:'||to_char(p_project_id)) = 0) THEN
2069
2070 IF P_DEBUG_MODE = 'Y' THEN
2071 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Acquired lock on Proj ' || p_project_id);
2072 END IF;
2073 -- PA_DEBUG.set_err_stack('M:Acquired Lock'); 3912094 This is not required
2074
2075 ELSE
2076
2077 --Unable to acquire user lock
2078 IF P_DEBUG_MODE = 'Y' THEN
2082
2079 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Could not acquire lock');
2080 END IF;
2081 -- PA_DEBUG.set_err_stack('M:Could not acquire lock BSLNFCHKLOCK');
2083 raise ACQUIRE_LOCK_EXCEPTION;
2084
2085 END IF;
2086
2087 end if;
2088
2089 -- R12 Funds Management Uptake : Deleted delta logic as fundscheck will be perfomed only once i.e. during baselining
2090 -- R12 Funds Management Uptake : Deleted logic to update status_code from 'C' to 'A' . With new architecture
2091 -- there will be no delta fundscheck and hence no 'C' status.
2092
2093 PA_DEBUG.set_err_stack('M:FC Reserve Mode');
2094 IF P_DEBUG_MODE = 'Y' THEN
2095 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Call Funds Check in Reserve Mode');
2096 END IF;
2097
2098 --Call Funds Check for the delta transactions
2099 --in RESERVE_BASELINE mode
2100 IF PA_FUNDS_CONTROL_PKG.PA_FUNDS_CHECK(
2101 p_calling_module => 'RESERVE_BASELINE',
2102 p_conc_flag => null,
2103 p_set_of_book_id => l_sob_id,
2104 p_packet_id => l_packet_id,
2105 p_mode => 'B',
2106 p_partial_flag => 'N',
2107 p_reference1 => p_bdgt_intg_flag,
2108 p_reference2 => to_char(p_project_id),
2109 p_reference3 => to_char(p_budget_version_id),
2110 x_return_status => l_return_status,
2111 x_error_stage => x_error_stage,
2112 x_error_msg => x_error_msg) then
2113
2114 IF P_DEBUG_MODE = 'Y' THEN
2115 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'FCHK in Reserve Mode called');
2116 END IF;
2117 --Call Update Bdgt Acct Lines API
2118
2119 -- R12 Funds Management Uptake : Deleted call to PA_FUNDS_CONTROL_PKG.upd_bdgt_encum_bal
2120 -- as it will be fired during tieback .
2121
2122 -- R12 Funds Management Uptake : Added below update for updating pa_bc_packets with
2123 -- uncommited newly created budget version id.
2124
2125 If l_return_status not in ('F','T') then
2126
2127 UPDATE pa_bc_packets
2128 SET budget_version_id = p_baselined_budget_version_id
2129 WHERE packet_id = l_packet_id
2130 AND budget_version_id = p_budget_version_id;
2131
2132 -- R12 Funds Management Uptake : Update pa_bc_balances (draft version) with the latest budget version ..
2133
2134 UPDATE pa_bc_balances
2135 SET budget_version_id = p_baselined_budget_version_id
2136 WHERE budget_version_id = p_budget_version_id;
2137
2138 End If;
2139
2140 PA_DEBUG.Reset_Err_Stack; --3912094
2141
2142 if (l_return_status = 'F') then
2143
2144 -- PA_DEBUG.set_err_stack('M:Funds Check process in reserve mode returned failure');
2145 -- , Stage = '||x_error_stage || ' Msg from Funds Check = '|| x_error_msg);
2146 IF P_DEBUG_MODE = 'Y' THEN
2147 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'FCHK in Reserve Mode returned failure');
2148 END IF;
2149
2150 raise FND_API.G_EXC_ERROR;
2151
2152 elsif (l_return_status = 'T') then
2153
2154 -- PA_DEBUG.set_err_stack('M:Funds Check process in reserve mode returned fatal error');
2155 IF P_DEBUG_MODE = 'Y' THEN
2156 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'FCHK in Reserve Mode returned fatal error');
2157 END IF;
2158
2159 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2160
2161 end if;
2162 IF P_DEBUG_MODE = 'Y' THEN
2163 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'End of FCHK, l_return_status = ' || l_return_status);
2164 END IF;
2165
2166 ELSE
2167
2168 IF P_DEBUG_MODE = 'Y' THEN
2169 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'FCHK in Reserve Mode not called');
2170 END IF;
2171 -- PA_DEBUG.set_err_stack('M:Funds Check process in reserve mode returned failure');
2172 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2173
2174 END IF;
2175
2176
2177 PA_DEBUG.set_err_stack('M:End of API');
2178 IF P_DEBUG_MODE = 'Y' THEN
2179 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'End of API, RetSts = ' || x_return_status);
2180 END IF;
2181
2182 close c_bal_lock;
2183
2184 --Reset the error stack when returning to the calling program
2185 PA_DEBUG.Reset_Err_Stack;
2186 PA_DEBUG.Reset_Err_Stack; -- Bug 5064900
2187 PA_DEBUG.Reset_Err_Stack; -- Bug 5064900
2188
2189 /**************************************************
2190 Sweeper to be called after the tieback API is called
2191 *************************************************/
2192
2193 --We need to issue the below explicit commit since this API is
2194 --in autonomous mode. Or else will encounter ORA-6519 error
2195 commit;
2196
2197 EXCEPTION
2198 when resource_busy then
2199 IF P_DEBUG_MODE = 'Y' THEN
2200 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'In resource busy');
2201 END IF;
2205 PA_DEBUG.Reset_Err_Stack; --Bug 5064900
2202 if c_bal_lock%isopen then
2203 close c_bal_lock ;
2204 end if;
2206 WHEN ACQUIRE_LOCK_EXCEPTION THEN
2207 rollback;
2208 IF P_DEBUG_MODE = 'Y' THEN
2209 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Cannot Acquire lock BSLNFCHKLOCK for GL');
2210 END IF;
2211
2212 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_BGT_BASELINE_PKG'
2213 ,p_procedure_name => 'MAINTAIN_BAL_FCHK' --Bug 5064900
2214 ,p_error_text => PA_DEBUG.G_Err_Stack );
2215
2216 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2217 x_error_message_code := 'PA_BC_CANNOT_ACQUIRE_LOCK';
2218
2219 if c_bal_lock%isopen then
2220 close c_bal_lock;
2221 end if;
2222 PA_DEBUG.Reset_Err_Stack; -- Bug 5064900
2223 PA_DEBUG.Reset_Err_Stack; -- Bug 5064900
2224 raise;
2225
2226 WHEN FND_API.G_EXC_ERROR THEN
2227 --rollback; this exception raised when FC fails
2228 commit; -- commit required to commit failure status_code
2229
2230 IF P_DEBUG_MODE = 'Y' THEN
2231 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Funds check returned failure');
2232 END IF;
2233
2234 IF (pa_debug.release_user_lock('BSLNFCHKLOCK:'||to_char(p_project_id)) = 0) THEN
2235 pa_debug.g_err_stage := ' ' || to_char(p_project_id);
2236 END IF;
2237
2238 --When funds check fails due to insufficient funds then
2239 --we should not add error message to error stack.
2240 /*FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_BGT_BASELINE_PKG'
2241 ,p_procedure_name => PA_DEBUG.G_Err_Stack ); */
2242
2243 x_return_status := FND_API.G_RET_STS_ERROR;
2244
2245 if (p_bdgt_ctrl_type = 'GL') then
2246 x_error_message_code := 'PA_BC_BASELINE_FCHK_FAILED';
2247 elsif (p_bdgt_ctrl_type = 'CC') then
2248 x_error_message_code := 'PA_BC_CC_BSLN_FCHK_FAILED';
2249 end if;
2250
2251 if c_bal_lock%isopen then
2252 close c_bal_lock;
2253 end if;
2254
2255 PA_DEBUG.Reset_Err_Stack; -- Bug 5064900
2256 PA_DEBUG.Reset_Err_Stack; -- Bug 5064900
2257
2258 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2259 If nvl(l_return_status,'P') = 'T' then
2260 commit; -- FC exception
2261 Else
2262 rollback; -- this package exception
2263 End if;
2264
2265 IF P_DEBUG_MODE = 'Y' THEN
2266 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Funds check returned fatal error');
2267 END IF;
2268
2269 IF (pa_debug.release_user_lock('BSLNFCHKLOCK:'||to_char(p_project_id)) = 0) THEN
2270 pa_debug.g_err_stage := ' ' || to_char(p_project_id);
2271 END IF;
2272
2273 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_BGT_BASELINE_PKG'
2274 ,p_procedure_name => 'MAINTAIN_BAL_FCHK' -- Bug 5064900
2275 ,p_error_text => PA_DEBUG.G_Err_Stack );
2276
2277 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2278 x_error_message_code := (SQLCODE||' '||SQLERRM);
2279
2280 IF P_DEBUG_MODE = 'Y' THEN
2281 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || SQLERRM);
2282 END IF;
2283
2284 if c_bal_lock%isopen then
2285 close c_bal_lock;
2286 end if;
2287 PA_DEBUG.Reset_Err_Stack; -- Bug 5064900
2288 PA_DEBUG.Reset_Err_Stack; -- Bug 5064900
2289 raise;
2290
2291 WHEN OTHERS THEN
2292 rollback;
2293 IF P_DEBUG_MODE = 'Y' THEN
2294 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'In others of main');
2295 END IF;
2296
2297 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_BGT_BASELINE_PKG'
2298 ,p_procedure_name => 'MAINTAIN_BAL_FCHK' -- Bug 5064900
2299 ,p_error_text => PA_DEBUG.G_Err_Stack );
2300
2301 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2302 x_error_message_code := (SQLCODE||' '||SQLERRM);
2303
2304 if c_bal_lock%isopen then
2305 close c_bal_lock;
2306 end if;
2307 PA_DEBUG.Reset_Err_Stack; -- Bug 5064900
2308 RAISE;
2309
2310 END MAINTAIN_BAL_FCHK;
2311
2312 END PA_BGT_BASELINE_PKG;