[Home] [Help]
PACKAGE BODY: APPS.PA_BGT_BASELINE_PKG
Source
1 PACKAGE BODY PA_BGT_BASELINE_PKG AS
2 -- $Header: PAFCBALB.pls 120.11.12020000.3 2013/03/06 09:26:12 admarath 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 .
386 -- This procedure is always fired with fundscheck required as Yes.
387
388 --Insert BGT lines.
389 IF P_DEBUG_MODE = 'Y' THEN
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
528 for each budget version id.
529 ****************************************************************************/
530
531 -- This Function is to see if Funds checking (FC) is required for this Budget.
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
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.
645 end if;
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, -- 7531681
719 nvl(ei.override_to_organization_id,( select exp.incurred_by_organization_id from pa_expenditures_all exp
720 where exp.expenditure_id = ei.expenditure_id)) organization_id, -- 7531681
721 gl.PERIOD_NAME,
722 gl.PERIOD_YEAR,
723 gl.PERIOD_NUM,
724 cdl.expenditure_item_id,
725 cdl.line_num,
726 /** Commented out for burdening enhancements
727 --decode(sign(cdl.denom_burdened_cost),1,cdl.denom_burdened_cost,0) entered_dr,
728 --decode(sign(cdl.denom_burdened_cost),-1,ABS(cdl.denom_burdened_cost),0) entered_cr,
729 --decode(sign(cdl.acct_burdened_cost),1,cdl.acct_burdened_cost,0) accounted_dr,
730 --decode(sign(cdl.acct_burdened_cost),-1,ABS(cdl.acct_burdened_cost),0) accounted_cr,
731 **/
732 decode(sign(nvl(cdl.denom_burdened_cost,0)+nvl(cdl.DENOM_BURDENED_CHANGE,0))
733 ,1,(nvl(cdl.denom_burdened_cost,0)+nvl(cdl.DENOM_BURDENED_CHANGE,0))
734 ,0) entered_dr,
735 decode(sign(nvl(cdl.denom_burdened_cost,0)+nvl(cdl.DENOM_BURDENED_CHANGE,0))
736 ,-1,ABS(nvl(cdl.denom_burdened_cost,0)+nvl(cdl.DENOM_BURDENED_CHANGE,0))
737 ,0) entered_cr,
738 decode(sign(nvl(cdl.acct_burdened_cost,0)+nvl(cdl.ACCT_BURDENED_CHANGE,0))
739 ,1,(nvl(cdl.acct_burdened_cost,0)+nvl(cdl.ACCT_BURDENED_CHANGE,0))
740 ,0) accounted_dr,
741 decode(sign(nvl(cdl.acct_burdened_cost,0)+nvl(cdl.ACCT_BURDENED_CHANGE,0))
742 ,-1,ABS(nvl(cdl.acct_burdened_cost,0)+nvl(cdl.ACCT_BURDENED_CHANGE,0))
743 ,0) accounted_cr,
744 cdl.gl_date,
745 cdl.pa_date,
746 cdl.dr_code_combination_id,
747 cdl.org_id,
748 cdl.budget_ccid
749 --PA.M
750 , ei.po_line_id
751 ,'EXP'
752 ,cdl.expenditure_item_id
753 ,cdl.line_num
754 --PA.M selecting -99 for parent bc pkt for CWK BTC EIs
755 ,decode(ei.system_linkage_function, 'BTC', decode(nvl(ei.po_line_id,-99), -99, null, -99), null)
756 from pa_expenditure_items_all ei,
757 -- pa_expenditures_all exp, -- 7531681
758 pa_cost_distribution_lines_all cdl,
759 gl_period_STATUSES gl,
760 pa_tasks pt
761 where cdl.project_id = p_project_id
762 and pt.task_id = ei.task_id
763 -- and ei.expenditure_id = exp.expenditure_id -- 7531681
764 --and trunc(gl.end_date) = trunc(cdl.gl_date)
765 --commented above since AP gl_date = transaction date rather end date of period
766 and trunc(cdl.gl_date) between trunc(gl.start_date) and trunc(gl.end_date)
767 and gl.application_id = 101
768 and gl.set_of_books_id = p_sob_id
769 and gl.adjustment_period_flag = 'N'
770 and gl.closing_status in ('O','F')
771 --and nvl(cdl.amount,0) <> 0 -- filter burden transactions
772 and ei.expenditure_item_id = cdl.expenditure_item_id
773 and cdl.line_type = 'R'
774 and cdl.reversed_flag is null
775 and cdl.line_num_reversed is null
776 --and ei.cost_distributed_flag = 'Y' commented out to handle failed ER batches
777 and nvl(ei.net_zero_adjustment_flag,'N') <> 'Y'
778 --PA.M: Added ST OT where PO Line ID is populated
779 and (((ei.system_linkage_function = 'VI' and transaction_source in
780 ('AP INVOICE',
781 'INTERCOMPANY_AP_INVOICES',
782 'INTERPROJECT_AP_INVOICES',
783 'AP NRTAX',
784 'PO RECEIPT',
785 'PO RECEIPT NRTAX',
786 'AP VARIANCE',
787 'AP DISCOUNTS',
788 'PO RECEIPT NRTAX PRICE ADJ',
789 'PO RECEIPT PRICE ADJ',
790 'AP ERV',
791 'AP SELF ASSESSED TAX')
792 ) --'ER'
793 or
794 (ei.system_linkage_function in ('ST', 'OT')
795 and ei.po_line_id is not null)
796 )
797 or (ei.system_linkage_function = 'BTC'
798 /* 7531681 start */
799 and exists (select null
800 from pa_expenditure_items_all ei1,
801 pa_cost_distribution_lines_all cdl1
802 where ( cdl1.burden_sum_source_run_id = ei.burden_sum_dest_run_id
803 and cdl1.project_id = p_project_id
804 and ei1.expenditure_item_id = cdl1.expenditure_item_id
805 )
806 and ((ei1.system_linkage_function = 'VI' and transaction_source in
807 ('AP INVOICE',
808 'INTERCOMPANY_AP_INVOICES',
809 'INTERPROJECT_AP_INVOICES',
810 'AP NRTAX',
811 'PO RECEIPT',
812 'PO RECEIPT NRTAX',
813 'AP VARIANCE',
814 'AP DISCOUNTS',
815 'PO RECEIPT NRTAX PRICE ADJ',
816 'PO RECEIPT PRICE ADJ',
817 'AP ERV',
818 'AP SELF ASSESSED TAX')
819 )
820
821 or
822 (ei1.system_linkage_function in ('ST', 'OT')
823 and ei1.po_line_id is not null)
824 )
825 UNION ALL
826 select null from pa_expenditure_items_all ei1,
827 pa_aud_cost_dist_lines aud
828 WHERE
829 ( aud.burden_sum_source_run_id = ei.burden_sum_dest_run_id
830 and aud.expenditure_item_id = ei1.expenditure_item_id
831 )
832 and ( (ei1.system_linkage_function = 'VI' and transaction_source in
833 ('AP INVOICE',
834 'INTERCOMPANY_AP_INVOICES',
835 'INTERPROJECT_AP_INVOICES',
836 'AP NRTAX',
837 'PO RECEIPT',
838 'PO RECEIPT NRTAX',
839 'AP VARIANCE',
840 'AP DISCOUNTS',
841 'PO RECEIPT NRTAX PRICE ADJ',
842 'PO RECEIPT PRICE ADJ',
843 'AP ERV',
844 'AP SELF ASSESSED TAX')
845 )
846 or
847 (ei1.system_linkage_function in ('ST', 'OT')
848 and ei1.po_line_id is not null)
849 )
850 )));
851 /* 7531681 end */
852 --Bug 2795051
853 -- from pa_expenditure_items_all ei1,
854 -- and ei1.expenditure_item_id = cdl1.expenditure_item_id
855 -- and ei1.system_linkage_function = 'VI'
856 --Bug 3019361
857 --Added pa_aud_cost_dist_lines to select BTC EIs of previous run ids
858 --During PA.L testing, the below exists clause performed faster than alternative 2 below.
859 --hence replaced pa_bc_commitments join with pa_expenditure_items_all as it was originally
860 /* 7531681 and exists (select null
861 from pa_expenditure_items_all ei1,
862 pa_cost_distribution_lines_all cdl1,
863 pa_aud_cost_dist_lines aud
864 --PA.M added outer join to cdl1 and aud plus changed the or to and
865 -- cos if there are no records in aud,
866 -- then this will not pick up the BTC txns
867 where ( cdl1.burden_sum_source_run_id(+) = ei.burden_sum_dest_run_id
868 and cdl1.expenditure_item_id(+) = ei1.expenditure_item_id
869 )
870 --or
871 and
872 ( aud.burden_sum_source_run_id(+) = ei.burden_sum_dest_run_id
873 and aud.expenditure_item_id(+) = ei1.expenditure_item_id
874 )
875 and cdl1.expenditure_item_id = ei1.expenditure_item_id
876 --PA.M: Added ST OT where PO Line ID is populated
877 and ( (ei1.system_linkage_function = 'VI' and transaction_source in
878 ('AP INVOICE',
879 'INTERCOMPANY_AP_INVOICES',
880 'INTERPROJECT_AP_INVOICES',
881 'AP NRTAX',
882 'PO RECEIPT',
883 'PO RECEIPT NRTAX',
884 'AP VARIANCE',
885 'AP DISCOUNTS',
886 'PO RECEIPT NRTAX PRICE ADJ',
887 'PO RECEIPT PRICE ADJ',
888 'AP ERV',
889 'AP SELF ASSESSED TAX')
890 )
891 or
892 (ei1.system_linkage_function in ('ST', 'OT')
893 and ei1.po_line_id is not null)
894 )
895 )
896 )
897 ); 7531681 */
898
899 /*If above exists clause performs badly, alternative 2 is to replace join to ei with pa_bc_commitments_all */
900 /*Exists clause before fixing bug 3019361 is as follows
901 and exists (select null
902 from pa_bc_commitments_all bc,
903 pa_cost_distribution_lines_all cdl1
904 where cdl1.burden_sum_source_run_id = ei.burden_sum_dest_run_id
905 and bc.document_header_id = to_number(cdl1.system_reference2)
906 and bc.document_distribution_id = to_number(cdl1.system_reference3)))); --'ER'
907 */
908 ELSE
909 OPEN c_VIERBTC_txns FOR
910 select cdl.project_id,
911 cdl.task_id,
912 ei.EXPENDITURE_TYPE,
913 trunc(ei.EXPENDITURE_ITEM_DATE) expenditure_item_date,
914 -- nvl(ei.override_to_organization_id,exp.incurred_by_organization_id) organization_id, -- 7531681
915 nvl(ei.override_to_organization_id,( select exp.incurred_by_organization_id from pa_expenditures_all exp
916 where exp.expenditure_id = ei.expenditure_id)) organization_id, -- 7531681
917
918 gl.PERIOD_NAME,
919 gl.PERIOD_YEAR,
920 gl.PERIOD_NUM,
921 cdl.expenditure_item_id,
922 cdl.line_num,
923 /** Commented out for Burdening enhancements
924 --decode(sign(cdl.denom_burdened_cost),1,cdl.denom_burdened_cost,0) entered_dr,
925 --decode(sign(cdl.denom_burdened_cost),-1,ABS(cdl.denom_burdened_cost),0) entered_cr,
926 --decode(sign(cdl.acct_burdened_cost),1,cdl.acct_burdened_cost,0) accounted_dr,
927 --decode(sign(cdl.acct_burdened_cost),-1,ABS(cdl.acct_burdened_cost),0) accounted_cr,
928 **/
929 decode(sign(nvl(cdl.denom_burdened_cost,0)+nvl(cdl.DENOM_BURDENED_CHANGE,0))
930 ,1,(nvl(cdl.denom_burdened_cost,0)+nvl(cdl.DENOM_BURDENED_CHANGE,0))
931 ,0) entered_dr,
932 decode(sign(nvl(cdl.denom_burdened_cost,0)+nvl(cdl.DENOM_BURDENED_CHANGE,0))
933 ,-1,ABS(nvl(cdl.denom_burdened_cost,0)+nvl(cdl.DENOM_BURDENED_CHANGE,0))
934 ,0) entered_cr,
935 decode(sign(nvl(cdl.acct_burdened_cost,0)+nvl(cdl.ACCT_BURDENED_CHANGE,0))
936 ,1,(nvl(cdl.acct_burdened_cost,0)+nvl(cdl.ACCT_BURDENED_CHANGE,0))
937 ,0) accounted_dr,
938 decode(sign(nvl(cdl.acct_burdened_cost,0)+nvl(cdl.ACCT_BURDENED_CHANGE,0))
939 ,-1,ABS(nvl(cdl.acct_burdened_cost,0)+nvl(cdl.ACCT_BURDENED_CHANGE,0))
940 ,0) accounted_cr,
941 cdl.gl_date,
942 cdl.pa_date,
943 cdl.dr_code_combination_id,
944 cdl.org_id,
945 cdl.budget_ccid
946 --PA.M
947 ,ei.po_line_id
948 ,'EXP'
949 ,cdl.expenditure_item_id
950 ,cdl.line_num
951 --PA.M selecting -99 for parent bc pkt for CWK BTC EIs
952 ,decode(ei.system_linkage_function, 'BTC', decode(nvl(ei.po_line_id,-99), -99, null, -99), null)
953 from pa_expenditure_items_all ei,
954 -- pa_expenditures_all exp, -- 7531681
955 pa_cost_distribution_lines_all cdl,
956 gl_period_STATUSES gl,
957 pa_tasks pt
958 where cdl.project_id = p_project_id
959 and pt.task_id = ei.task_id
960 -- and ei.expenditure_id = exp.expenditure_id -- 7531681
961 --and trunc(gl.end_date) = trunc(cdl.gl_date)
962 --commented above since AP gl_date = transaction date rather end date of period
963 and trunc(cdl.gl_date) between trunc(gl.start_date) and trunc(gl.end_date)
964 and gl.application_id = 101
965 and gl.set_of_books_id = p_sob_id
966 and gl.adjustment_period_flag = 'N'
967 --and gl.closing_status = decode(p_bdgt_intg_flag, 'Y', 'O', gl.closing_status)
968 --and nvl(cdl.amount,0) <> 0 -- filter burden transactions
969 and ei.expenditure_item_id = cdl.expenditure_item_id
970 and cdl.line_type = 'R'
971 and cdl.reversed_flag is null
972 and cdl.line_num_reversed is null
973 --and ei.cost_distributed_flag = 'Y' commented out to handle failed ER batches
974 and nvl(ei.net_zero_adjustment_flag,'N') <> 'Y'
975 --PA.M: Added ST OT where PO Line ID is populated
976 and (( (ei.system_linkage_function = 'VI' and transaction_source in
977 ('AP INVOICE',
978 'INTERCOMPANY_AP_INVOICES',
979 'INTERPROJECT_AP_INVOICES',
980 'AP NRTAX',
981 'PO RECEIPT',
982 'PO RECEIPT NRTAX',
983 'AP VARIANCE',
984 'AP DISCOUNTS',
985 'PO RECEIPT NRTAX PRICE ADJ',
986 'PO RECEIPT PRICE ADJ',
987 'AP ERV',
988 'AP SELF ASSESSED TAX')
989 )--'ER'
990 or
991 (ei.system_linkage_function in ('ST', 'OT')
992 and ei.po_line_id is not null)
993 )
994 or (ei.system_linkage_function = 'BTC'
995 /* 7531681 start */
996 and exists (select null
997 from pa_expenditure_items_all ei1,
998 pa_cost_distribution_lines_all cdl1
999 where ( cdl1.burden_sum_source_run_id = ei.burden_sum_dest_run_id
1000 and cdl1.project_id = p_project_id
1001 and ei1.expenditure_item_id = cdl1.expenditure_item_id
1002 )
1003 and (( ei1.system_linkage_function = 'VI' and transaction_source in
1004 ('AP INVOICE',
1005 'INTERCOMPANY_AP_INVOICES',
1006 'INTERPROJECT_AP_INVOICES',
1007 'AP NRTAX',
1008 'PO RECEIPT',
1009 'PO RECEIPT NRTAX',
1010 'AP VARIANCE',
1011 'AP DISCOUNTS',
1012 'PO RECEIPT NRTAX PRICE ADJ',
1013 'PO RECEIPT PRICE ADJ',
1014 'AP ERV',
1015 'AP SELF ASSESSED TAX')
1016 )
1017 or
1018 (ei1.system_linkage_function in ('ST', 'OT')
1019 and ei1.po_line_id is not null)
1020 )
1021 UNION ALL
1022 select null from pa_expenditure_items_all ei1,
1023 pa_aud_cost_dist_lines aud
1024 WHERE
1025 ( aud.burden_sum_source_run_id = ei.burden_sum_dest_run_id
1026 and aud.expenditure_item_id = ei1.expenditure_item_id
1027 )
1028 and ( (ei1.system_linkage_function = 'VI' and transaction_source in
1029 ('AP INVOICE',
1030 'INTERCOMPANY_AP_INVOICES',
1031 'INTERPROJECT_AP_INVOICES',
1032 'AP NRTAX',
1033 'PO RECEIPT',
1034 'PO RECEIPT NRTAX',
1035 'AP VARIANCE',
1036 'AP DISCOUNTS',
1037 'PO RECEIPT NRTAX PRICE ADJ',
1038 'PO RECEIPT PRICE ADJ',
1039 'AP ERV',
1040 'AP SELF ASSESSED TAX')
1041 )
1042 or
1043 (ei1.system_linkage_function in ('ST', 'OT')
1044 and ei1.po_line_id is not null)
1045 )
1046 )
1047 )
1048 );
1049 /* 7531681 end */
1050 --Bug 2795051
1051 -- from pa_expenditure_items_all ei1,
1052 -- and ei1.expenditure_item_id = cdl1.expenditure_item_id
1053 -- and ei1.system_linkage_function = 'VI'
1054 --Bug 3019361
1055 --Added pa_aud_cost_dist_lines to select BTC EIs of previous run ids
1056 --During PA.L testing, the below exists clause performed faster than alternative 2 below.
1057 --hence replaced pa_bc_commitments join with pa_expenditure_items_all as it was originally
1058 /* 7531681 and exists (select null
1059 from pa_expenditure_items_all ei1,
1060 pa_cost_distribution_lines_all cdl1,
1061 pa_aud_cost_dist_lines aud
1062 --PA.M added outer join to cdl1 and aud plus changed the or to and
1063 -- cos if there are no records in aud,
1064 -- then this will not pick up the BTC txns
1065 where ( cdl1.burden_sum_source_run_id(+) = ei.burden_sum_dest_run_id
1066 and cdl1.expenditure_item_id(+) = ei1.expenditure_item_id
1067 )
1068 -- or
1069 and
1070 ( aud.burden_sum_source_run_id(+) = ei.burden_sum_dest_run_id
1071 and aud.expenditure_item_id(+) = ei1.expenditure_item_id
1072 )
1073 and cdl1.expenditure_item_id = ei1.expenditure_item_id
1074 --PA.M: Added ST OT where PO Line ID is populated
1075 and ( (ei1.system_linkage_function = 'VI' and transaction_source in
1076 ('AP INVOICE',
1077 'INTERCOMPANY_AP_INVOICES',
1078 'INTERPROJECT_AP_INVOICES',
1079 'AP NRTAX',
1080 'PO RECEIPT',
1081 'PO RECEIPT NRTAX',
1082 'AP VARIANCE',
1083 'AP DISCOUNTS',
1084 'PO RECEIPT NRTAX PRICE ADJ',
1085 'PO RECEIPT PRICE ADJ',
1086 'AP ERV',
1087 'AP SELF ASSESSED TAX')
1088 )
1089 or
1090 (ei1.system_linkage_function in ('ST', 'OT')
1091 and ei1.po_line_id is not null)
1092 )
1093 )
1094 )
1095 ); 7531681 */
1096
1097 /*If above exists clause performs badly, alternative 2 is to replace join to ei with pa_bc_commitments_all */
1098 /*Exists clause before fixing bug 3019361 is as follows
1099 and exists (select null
1100 from pa_bc_commitments_all bc,
1101 pa_cost_distribution_lines_all cdl1
1102 where cdl1.burden_sum_source_run_id = ei.burden_sum_dest_run_id
1103 and bc.document_header_id = to_number(cdl1.system_reference2)
1104 and bc.document_distribution_id = to_number(cdl1.system_reference3)))); --'ER'
1105 */
1106 END IF;
1107 END proc_open_cursor;
1108
1109 BEGIN
1110
1111 --Initialize the error stack
1112 PA_DEBUG.set_err_stack('Insert VIERBTC Txns');
1113
1114 --Initialize the return status to success
1115 x_return_status := FND_API.G_RET_STS_SUCCESS;
1116
1117 IF P_DEBUG_MODE = 'Y' THEN
1118 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);
1119 END IF;
1120
1121 --OPEN c_VIERBTC_txns(p_project_id, p_sob_id, p_bdgt_intg_flag);
1122 --Call procedure to open cursor c_vierbtc_txns
1123 proc_open_cursor(p_project_id, p_sob_id, p_bdgt_intg_flag);
1124
1125 LOOP
1126
1127 --Initialize pl/sql tables
1128 l_ExpProjTab.Delete;
1129 l_ExpTaskTab.Delete;
1130 l_ExpExpTypTab.Delete;
1131 l_ExpEiDateTab.Delete;
1132 l_ExpExpOrgTab.Delete;
1133 l_ExpPeriodTab.Delete;
1134 l_ExpPdYearTab.Delete;
1135 l_ExpPdNumTab.Delete;
1136 l_ExpDocDistTab.Delete;
1137 l_ExpDocHdrTab.Delete;
1138 l_ExpEntDrTab.Delete;
1139 l_ExpEntCrTab.Delete;
1140 l_ExpAcctDrTab.Delete;
1141 l_ExpAcctCrTab.Delete;
1142 l_ExpGlDateTab.Delete;
1143 l_ExpPaDateTab.Delete;
1144 l_ExpTxnCCIDTab.Delete;
1145 l_ExpOrgIdTab.Delete;
1146 l_ExpBdgtCCIDTab.Delete;
1147 --PA.M
1148 l_ExpPoLineIdTab.Delete;
1149 l_ExpReference1Tab.delete;
1150 l_ExpReference2Tab.delete;
1151 l_ExpReference3Tab.delete;
1152 l_ExpParBcPktIdTab.delete;
1153
1154 IF P_DEBUG_MODE = 'Y' THEN
1155 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Before Fetch of c_vierbtc');
1156 END IF;
1157
1158 --Insert ER, VI, BTC
1159 FETCH c_VIERBTC_txns bulk collect into
1160 l_ExpProjTab,
1161 l_ExpTaskTab,
1162 l_ExpExpTypTab,
1163 l_ExpEiDateTab,
1164 l_ExpExpOrgTab,
1165 l_ExpPeriodTab,
1166 l_ExpPdYearTab,
1167 l_ExpPdNumTab,
1168 l_ExpDocHdrTab,
1169 l_ExpDocDistTab,
1170 l_ExpEntDrTab,
1171 l_ExpEntCrTab,
1172 l_ExpAcctDrTab,
1173 l_ExpAcctCrTab,
1174 l_ExpGlDateTab,
1175 l_ExpPaDateTab,
1176 l_ExpTxnCCIDTab,
1177 l_ExpOrgIdTab,
1178 l_ExpBdgtCCIDTab
1179 --PA.M
1180 ,l_ExpPOLineIdTab
1181 ,l_ExpReference1Tab
1182 ,l_ExpReference2Tab
1183 ,l_ExpReference3Tab
1184 ,l_ExpParBcPktIdTab
1185 LIMIT rows;
1186
1187 IF P_DEBUG_MODE = 'Y' THEN
1188 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Fetched ER VI BTC, count = ' || l_ExpProjTab.count);
1189 END IF;
1190
1191 IF l_ExpProjTab.count=0 THEN
1192 IF P_DEBUG_MODE = 'Y' THEN
1193 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'No rec in cursor c_vierbtc exit');
1194 END IF;
1195 exit;
1196 END IF;
1197
1198 IF P_DEBUG_MODE = 'Y' THEN
1199 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Before inserting VIERBTC');
1200 END IF;
1201 FORALL m in l_ExpProjTab.FIRST..l_ExpProjTab.LAST
1202 insert into pa_bc_packets (
1203 PACKET_ID,
1204 PROJECT_ID,
1205 TASK_ID,
1206 EXPENDITURE_TYPE,
1207 EXPENDITURE_ITEM_DATE,
1208 ACTUAL_FLAG,
1209 STATUS_CODE,
1210 LAST_UPDATE_DATE,
1211 LAST_UPDATED_BY,
1212 CREATED_BY,
1213 CREATION_DATE,
1214 LAST_UPDATE_LOGIN,
1215 SET_OF_BOOKS_ID,
1216 JE_CATEGORY_NAME,
1217 JE_SOURCE_NAME,
1218 DOCUMENT_TYPE,
1219 EXPENDITURE_ORGANIZATION_ID,
1220 PERIOD_NAME,
1221 PERIOD_YEAR,
1222 PERIOD_NUM,
1223 DOCUMENT_HEADER_ID,
1224 DOCUMENT_DISTRIBUTION_ID,
1225 ENTERED_DR,
1226 ENTERED_CR,
1227 accounted_dr,
1228 accounted_cr,
1229 BUDGET_VERSION_ID,
1230 bc_packet_id,
1231 funds_process_mode,
1232 parent_bc_packet_id,
1233 gl_date,
1234 pa_date,
1235 txn_ccid,
1236 result_code,
1237 balance_posted_flag,
1238 org_id,
1239 burden_cost_flag,
1240 old_budget_ccid
1241 --PA.M
1242 ,document_line_id
1243 ,reference1
1244 ,reference2
1245 ,reference3 )
1246 select
1247 p_packet_id,
1248 l_ExpProjTab(m),
1249 l_ExpTaskTab(m),
1250 l_ExpExpTypTab(m),
1251 l_ExpEiDateTab(m),
1252 'A',
1253 'P',
1254 sysdate,
1255 FND_GLOBAL.USER_ID,
1256 FND_GLOBAL.USER_ID,
1257 sysdate,
1258 FND_GLOBAL.LOGIN_ID,
1259 p_sob_id,
1260 'Project Accounting',
1261 'Expenditures',
1262 'EXP',
1263 l_ExpExpOrgTab(m),
1264 l_ExpPeriodTab(m),
1265 l_ExpPdYearTab(m),
1266 l_ExpPdNumTab(m),
1267 l_ExpDocHdrTab(m),
1268 l_ExpDocDistTab(m),
1269 l_ExpEntDrTab(m),
1270 l_ExpEntCrTab(m),
1271 l_ExpAcctDrTab(m),
1272 l_ExpAcctCrTab(m),
1273 p_budget_version_id,
1274 pa_bc_packets_s.nextval,
1275 'B',
1276 --PA.M insert parent bc pkt as -99 for CWK BTC EIs
1277 --null,
1278 l_ExpParBcPktIdTab(m),
1279 l_ExpGlDateTab(m),
1280 l_ExpPaDateTab(m),
1281 l_ExpTxnCCIDTab(m),
1282 'P',
1283 'N',
1284 l_ExpOrgIdTab(m),
1285 'N',
1286 l_ExpBdgtCCIDTab(m)
1287 --PA.M
1288 ,l_ExpPoLineIdTab(m)
1289 ,l_ExpReference1Tab(m)
1290 ,l_ExpReference2Tab(m)
1291 ,l_ExpReference3Tab(m)
1292 from dual;
1293
1294 commit;
1295 EXIT WHEN c_VIERBTC_txns%NOTFOUND;
1296 END LOOP;
1297 CLOSE c_VIERBTC_txns;
1298 PA_DEBUG.Reset_Err_Stack; --3912094
1299 PA_DEBUG.set_err_stack('Inserted VIERBTC Txns');
1300
1301 IF P_DEBUG_MODE = 'Y' THEN
1302 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Exiting VIERBTC');
1303 END IF;
1304
1305 --Reset the error stack when returning to the calling program
1306 PA_DEBUG.Reset_Err_Stack;
1307
1308 EXCEPTION
1309 WHEN OTHERS THEN
1310 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_BGT_BASELINE_PKG'
1311 ,p_procedure_name => 'INSERT_VI_ER_BTC_TXNS' -- Bug 5064900
1312 ,p_error_text => PA_DEBUG.G_Err_Stack );
1313
1314 IF c_VIERBTC_txns%ISOPEN THEN
1315 close c_VIERBTC_txns;
1316 END IF;
1317
1318 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1319 x_error_message_code := (SQLCODE||' '||SQLERRM);
1320 PA_DEBUG.Reset_Err_Stack; -- Bug 5064900
1321 RAISE;
1322 END INSERT_VI_ER_BTC_TXNS;
1323 ---------------------------------------------------------------------------------------------
1324
1325 -- procedure to load all commitment transactions in pa_bc_packets for funds check
1326 PROCEDURE INSERT_COMMITMENT_TXNS(
1327 p_packet_id IN NUMBER,
1328 p_sob_id IN NUMBER,
1329 p_project_id IN NUMBER,
1330 p_budget_version_id IN NUMBER,
1331 p_bdgt_ctrl_type IN VARCHAR2,
1332 p_bdgt_intg_flag IN VARCHAR2,
1333 x_return_status OUT NOCOPY VARCHAR2,
1334 x_error_message_code OUT NOCOPY VARCHAR2) IS
1335
1336 l_ProjTab PA_PLSQL_DATATYPES.IdTabTyp;
1337 l_TaskTab PA_PLSQL_DATATYPES.IdTabTyp;
1338 l_ExpTypTab PA_PLSQL_DATATYPES.Char30TabTyp;
1339 l_EiDateTab PA_PLSQL_DATATYPES.DateTabTyp;
1340 l_SobTab PA_PLSQL_DATATYPES.IdTabTyp;
1341 l_CatNameTab PA_PLSQL_DATATYPES.Char30TabTyp;
1342 l_SrcNameTab PA_PLSQL_DATATYPES.Char30TabTyp;
1343 l_DocTypTab PA_PLSQL_DATATYPES.Char10TabTyp;
1344 l_ExpOrgTab PA_PLSQL_DATATYPES.IdTabTyp;
1345 l_PeriodTab PA_PLSQL_DATATYPES.Char15TabTyp;
1346 l_PdYearTab PA_PLSQL_DATATYPES.NumTabTyp;
1347 l_PdNumTab PA_PLSQL_DATATYPES.NumTabTyp;
1348 l_DocHdrTab PA_PLSQL_DATATYPES.IdTabTyp;
1349 l_DocDistTab PA_PLSQL_DATATYPES.IdTabTyp;
1350 l_EntDrTab PA_PLSQL_DATATYPES.NumTabTyp;
1351 l_EntCrTab PA_PLSQL_DATATYPES.NumTabTyp;
1352 l_AcctDrTab PA_PLSQL_DATATYPES.NumTabTyp;
1353 l_AcctCrTab PA_PLSQL_DATATYPES.NumTabTyp;
1354 l_BcPktTab PA_PLSQL_DATATYPES.IdTabTyp;
1355 l_ParBCPktTab PA_PLSQL_DATATYPES.IdTabTyp;
1356 l_GlDateTab PA_PLSQL_DATATYPES.DateTabTyp;
1357 l_PaDateTab PA_PLSQL_DATATYPES.DateTabTyp;
1358 l_TxnCCIDTab PA_PLSQL_DATATYPES.IdTabTyp;
1359 l_ParResTab PA_PLSQL_DATATYPES.IdTabTyp;
1360 l_OrgIdTab PA_PLSQL_DATATYPES.IdTabTyp;
1361 l_BurCstFlagTab PA_PLSQL_DATATYPES.Char1TabTyp;
1362 l_BcCommIdTab PA_PLSQL_DATATYPES.IdTabTyp;
1363 l_BdgtCCIDTab PA_PLSQL_DATATYPES.IdTabTyp;
1364 --PA.M
1365 l_DocLineIdTab PA_PLSQL_DATATYPES.IdTabTyp;
1366 l_SummRecFlagTab PA_PLSQL_DATATYPES.Char1TabTyp;
1367 l_PktReference1Tab PA_PLSQL_DATATYPES.Char80TabTyp;
1368 l_PktReference2Tab PA_PLSQL_DATATYPES.Char80TabTyp;
1369 l_PktReference3Tab PA_PLSQL_DATATYPES.Char80TabTyp;
1370 -- R12 Funds Management Uptake
1371 l_BurMethodcodeTab PA_PLSQL_DATATYPES.Char30TabTyp;
1372
1373 --Cursor to select all commitment transactions from pa_bc_commitments_all
1374 --for a given project_id and the current baselined budget version id. The
1375 --check for later is because there might be commitments with old budget
1376 --version id which were funds checked during the delta time.
1377 --Removing the current baselined budget check as now we are handling that
1378 --there is only one commitment line for a tx. This is done in the sweeper while
1379 --moving the delta and new tx. to commitments.
1380 cursor c_bc_comm(p_project_id in number) is
1381 select bc.project_id,
1382 bc.task_id,
1383 bc.EXPENDITURE_TYPE,
1384 trunc(bc.EXPENDITURE_ITEM_DATE) expenditure_item_date,
1385 bc.set_of_books_id,
1386 bc.je_category_name,
1387 bc.je_source_name,
1388 bc.document_type,
1389 bc.expenditure_organization_id,
1390 bc.PERIOD_NAME,
1391 bc.PERIOD_YEAR,
1392 bc.PERIOD_NUM,
1393 bc.document_header_id,
1394 bc.document_distribution_id,
1395 bc.entered_dr,
1396 bc.entered_cr,
1397 bc.accounted_dr,
1398 bc.accounted_cr,
1399 bc.bc_packet_id,
1400 bc.parent_bc_packet_id,
1401 bc.gl_date,
1402 bc.pa_date,
1403 bc.txn_ccid,
1404 bc.org_id,
1405 bc.burden_cost_flag,
1406 bc.bc_commitment_id,
1407 bc.budget_ccid
1408 --PA.M
1409 ,bc.document_line_id
1410 ,bc.summary_record_flag
1411 ,bc.reference1
1412 ,bc.reference2
1413 ,bc.reference3
1414 -- R12 Funds Management Uptake
1415 ,bc.burden_method_code
1416 from pa_bc_commitments_all bc,
1417 pa_tasks pt
1418 --,pa_budget_versions pbv,
1419 --pa_budget_types pbt
1420 where bc.project_id = p_project_id
1421 and pt.task_id = bc.task_id;
1422 --and bc.budget_version_id = pbv.budget_version_id
1423 --and pbv.current_flag = 'Y'
1424 --and pbv.budget_status_code = 'B'
1425 --and pbv.budget_type_code = pbt.budget_type_code
1426 --and pbt.budget_amount_code = 'C';
1427 --and bc.budget_version_id = p_budget_version_id;
1428
1429 BEGIN
1430
1431 --Initialize the error stack
1432 PA_DEBUG.set_err_stack('Insert Commitment Txns');
1433
1434 --Initialize the return status to success
1435 x_return_status := FND_API.G_RET_STS_SUCCESS;
1436
1437 IF P_DEBUG_MODE = 'Y' THEN
1438 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Entering Insert Commitment Txns');
1439 END IF;
1440
1441 OPEN c_bc_comm(p_project_id);
1442 LOOP
1443
1444 --Initialize pl/sql tables
1445 l_ProjTab.Delete;
1446 l_TaskTab.Delete;
1447 l_ExpTypTab.Delete;
1448 l_EiDateTab.Delete;
1449 l_SobTab.Delete;
1450 l_CatNameTab.Delete;
1451 l_SrcNameTab.Delete;
1452 l_DocTypTab.Delete;
1453 l_ExpOrgTab.Delete;
1454 l_PeriodTab.Delete;
1455 l_PdYearTab.Delete;
1456 l_PdNumTab.Delete;
1457 l_DocHdrTab.Delete;
1458 l_DocDistTab.Delete;
1459 l_EntDrTab.Delete;
1460 l_EntCrTab.Delete;
1461 l_AcctDrTab.Delete;
1462 l_AcctCrTab.Delete;
1463 l_BcPktTab.Delete;
1464 l_ParBCPktTab.Delete;
1465 l_GlDateTab.Delete;
1466 l_PaDateTab.Delete;
1467 l_TxnCCIDTab.Delete;
1468 l_ParResTab.Delete;
1469 l_OrgIdTab.Delete;
1470 l_BurCstFlagTab.Delete;
1471 l_BcCommIdTab.Delete;
1472 l_BdgtCCIDTab.Delete;
1473 --PA.M
1474 l_DocLineIdTab.Delete;
1475 l_SummRecFlagTab.Delete;
1476 l_pktReference1Tab.Delete;
1477 l_pktReference2Tab.Delete;
1478 l_pktReference3Tab.Delete;
1479 -- R12 Funds Management Uptake
1480 l_BurMethodcodeTab.delete;
1481
1482 IF P_DEBUG_MODE = 'Y' THEN
1483 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Fetch c_bc_comm cursor');
1484 END IF;
1485
1486 FETCH c_bc_comm BULK COLLECT INTO
1487 l_ProjTab,
1488 l_TaskTab,
1489 l_ExpTypTab,
1490 l_EiDateTab,
1491 l_SobTab,
1492 l_CatNameTab,
1493 l_SrcNameTab,
1494 l_DocTypTab,
1495 l_ExpOrgTab,
1496 l_PeriodTab,
1497 l_PdYearTab,
1498 l_PdNumTab,
1499 l_DocHdrTab,
1500 l_DocDistTab,
1501 l_EntDrTab,
1502 l_EntCrTab,
1503 l_AcctDrTab,
1504 l_AcctCrTab,
1505 l_BcPktTab,
1506 l_ParBCPktTab,
1507 l_GlDateTab,
1508 l_PaDateTab,
1509 l_TxnCCIDTab,
1510 l_OrgIdTab,
1511 l_BurCstFlagTab,
1512 l_BcCommIdTab,
1513 l_BdgtCCIDTab
1514 --l_ParResTab
1515 --Pa.M
1516 , l_DocLineIdTab
1517 , l_SummRecFlagTab
1518 ,l_pktReference1Tab
1519 ,l_pktReference2Tab
1520 ,l_pktReference3Tab
1521 -- R12 Funds Management Uptake
1522 ,l_BurMethodcodeTab
1523 LIMIT rows;
1524
1525 IF l_ProjTab.count = 0 THEN
1526 IF P_DEBUG_MODE = 'Y' THEN
1527 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'No records in c_bc_comm, exit');
1528 END IF;
1529 exit;
1530 END IF;
1531
1532 --If budgetary control type is CC, then load only contract commitment transactions into
1533 --pa_bc_packets. Also if budget is linked then consider only open period
1534 --transactions else consider all transactions.
1535 IF P_DEBUG_MODE = 'Y' THEN
1536 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'No. of rec in c_bc_comm = '|| l_ProjTab.count);
1537 END IF;
1538
1539 IF (p_bdgt_ctrl_type = 'CC') THEN
1540
1541 PA_DEBUG.set_err_stack('CC'); --Bug 3912094
1542 IF P_DEBUG_MODE = 'Y' THEN
1543 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Bdgt Ctrl = CC');
1544 END IF;
1545
1546 -- Insert Contract Commitments
1547 IF (p_bdgt_intg_flag = 'Y') THEN
1548
1549 PA_DEBUG.set_err_stack('Insert CC, linked'); --Bug 3912094
1550 IF P_DEBUG_MODE = 'Y' THEN
1551 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Bdgt Intg Flag = Y');
1552 END IF;
1553 FORALL i in l_ProjTab.FIRST..l_ProjTab.LAST
1554 insert into pa_bc_packets (
1555 PACKET_ID,
1556 PROJECT_ID,
1557 TASK_ID,
1558 EXPENDITURE_TYPE,
1559 EXPENDITURE_ITEM_DATE,
1560 ACTUAL_FLAG,
1561 STATUS_CODE,
1562 LAST_UPDATE_DATE,
1563 LAST_UPDATED_BY,
1564 CREATED_BY,
1565 CREATION_DATE,
1566 LAST_UPDATE_LOGIN,
1567 SET_OF_BOOKS_ID,
1568 JE_CATEGORY_NAME,
1569 JE_SOURCE_NAME,
1570 DOCUMENT_TYPE,
1571 EXPENDITURE_ORGANIZATION_ID,
1572 PERIOD_NAME,
1573 PERIOD_YEAR,
1574 PERIOD_NUM,
1575 DOCUMENT_HEADER_ID,
1576 DOCUMENT_DISTRIBUTION_ID,
1577 ENTERED_DR,
1578 ENTERED_CR,
1579 accounted_dr,
1580 accounted_cr,
1581 BUDGET_VERSION_ID,
1582 bc_packet_id,
1583 funds_process_mode,
1584 parent_bc_packet_id,
1585 gl_date,
1586 pa_date,
1587 txn_ccid,
1588 result_code,
1589 balance_posted_flag,
1590 org_id,
1591 burden_cost_flag,
1592 bc_commitment_id,
1593 old_budget_ccid
1594 --PA.M
1595 ,document_line_id
1596 ,summary_record_flag
1597 ,reference1
1598 ,reference2
1599 ,reference3
1600 -- R12 Funds Management Uptake
1601 ,burden_method_code
1602 )
1603 select
1604 p_packet_id,
1605 l_ProjTab(i),
1606 l_TaskTab(i),
1607 l_ExpTypTab(i),
1608 l_EiDateTab(i),
1609 'E',
1610 'P',
1611 sysdate,
1612 FND_GLOBAL.USER_ID,
1613 FND_GLOBAL.USER_ID,
1614 sysdate,
1615 FND_GLOBAL.LOGIN_ID,
1616 l_SobTab(i),
1617 l_CatNameTab(i),
1618 l_SrcNameTab(i),
1619 l_DocTypTab(i),
1620 l_ExpOrgTab(i),
1621 l_PeriodTab(i),
1622 l_PdYearTab(i),
1623 l_PdNumTab(i),
1624 l_DocHdrTab(i),
1625 l_DocDistTab(i),
1626 l_EntDrTab(i),
1627 l_EntCrTab(i),
1628 l_AcctDrTab(i),
1629 l_AcctCrTab(i),
1630 p_budget_version_id,
1631 l_BcPktTab(i),
1632 'B',
1633 l_ParBCPktTab(i),
1634 l_GlDateTab(i),
1635 l_PaDateTab(i),
1636 l_TxnCCIDTab(i),
1637 'P',
1638 'N',
1639 l_OrgIdTab(i),
1640 l_BurCstFlagTab(i),
1641 l_BcCommIdTab(i),
1642 l_BdgtCCIDTab(i)
1643 --Pa.M
1644 ,l_DocLineIdTab(i)
1645 ,l_SummRecFlagTab(i)
1646 ,l_pktReference1Tab(i)
1647 ,l_pktReference2Tab(i)
1648 ,l_pktReference3Tab(i)
1649 -- R12 Funds Management Uptake
1650 ,l_BurMethodcodeTab(i)
1651 from gl_period_statuses gl
1652 where l_DocTypTab(i) in ('CC_C_CO', 'CC_P_CO')
1653 --and l_GlDateTab(i) = gl.end_date
1654 and trunc(l_GlDateTab(i)) between trunc(gl.start_date) and trunc(gl.end_date)
1655 and gl.application_id = 101
1656 and gl.set_of_books_id = p_sob_id
1657 and gl.adjustment_period_flag = 'N'
1658 and gl.closing_status in ( 'O', 'F');
1659
1660 for i in l_ProjTab.FIRST..l_ProjTab.LAST loop
1661 IF P_DEBUG_MODE = 'Y' THEN
1662 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'CC - Y, record count = '|| SQL%BULK_ROWCOUNT(i));
1663 END IF;
1664 end loop;
1665
1666 PA_DEBUG.Reset_Err_Stack; --3912094
1667 ELSE
1668
1669 PA_DEBUG.set_err_stack('Insert CC, no link'); --Bug 3912094
1670 IF P_DEBUG_MODE = 'Y' THEN
1671 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Bdgt Intg Flag = N');
1672 END IF;
1673
1674 FORALL j in l_ProjTab.FIRST..l_ProjTab.LAST
1675 insert into pa_bc_packets (
1676 PACKET_ID,
1677 PROJECT_ID,
1678 TASK_ID,
1679 EXPENDITURE_TYPE,
1680 EXPENDITURE_ITEM_DATE,
1681 ACTUAL_FLAG,
1682 STATUS_CODE,
1683 LAST_UPDATE_DATE,
1684 LAST_UPDATED_BY,
1685 CREATED_BY,
1686 CREATION_DATE,
1687 LAST_UPDATE_LOGIN,
1688 SET_OF_BOOKS_ID,
1689 JE_CATEGORY_NAME,
1690 JE_SOURCE_NAME,
1691 DOCUMENT_TYPE,
1692 EXPENDITURE_ORGANIZATION_ID,
1693 PERIOD_NAME,
1694 PERIOD_YEAR,
1695 PERIOD_NUM,
1696 DOCUMENT_HEADER_ID,
1697 DOCUMENT_DISTRIBUTION_ID,
1698 ENTERED_DR,
1699 ENTERED_CR,
1700 accounted_dr,
1701 accounted_cr,
1702 BUDGET_VERSION_ID,
1703 bc_packet_id,
1704 funds_process_mode,
1705 parent_bc_packet_id,
1706 gl_date,
1707 pa_date,
1708 txn_ccid,
1709 result_code,
1710 balance_posted_flag,
1711 org_id,
1712 burden_cost_flag,
1713 bc_commitment_id,
1714 old_budget_ccid
1715 --PA.M
1716 ,document_line_id
1717 ,summary_record_flag
1718 ,reference1
1719 ,reference2
1720 ,reference3
1721 -- R12 Funds Management Uptake
1722 ,burden_method_code
1723 )
1724 select
1725 p_packet_id,
1726 l_ProjTab(j),
1727 l_TaskTab(j),
1728 l_ExpTypTab(j),
1729 l_EiDateTab(j),
1730 'E',
1731 'P',
1732 sysdate,
1733 FND_GLOBAL.USER_ID,
1734 FND_GLOBAL.USER_ID,
1735 sysdate,
1736 FND_GLOBAL.LOGIN_ID,
1737 l_SobTab(j),
1738 l_CatNameTab(j),
1739 l_SrcNameTab(j),
1740 l_DocTypTab(j),
1741 l_ExpOrgTab(j),
1742 l_PeriodTab(j),
1743 l_PdYearTab(j),
1744 l_PdNumTab(j),
1745 l_DocHdrTab(j),
1746 l_DocDistTab(j),
1747 l_EntDrTab(j),
1748 l_EntCrTab(j),
1749 l_AcctDrTab(j),
1750 l_AcctCrTab(j),
1751 p_budget_version_id,
1752 l_BcPktTab(j),
1753 'B',
1754 l_ParBCPktTab(j),
1755 l_GlDateTab(j),
1756 l_PaDateTab(j),
1757 l_TxnCCIDTab(j),
1758 'P',
1759 'N',
1760 l_OrgIdTab(j),
1761 l_BurCstFlagTab(j),
1762 l_BcCommIdTab(j),
1763 l_BdgtCCIDTab(j)
1764 --Pa.M
1765 ,l_DocLineIdTab(j)
1766 , l_SummRecFlagTab(j)
1767 ,l_pktReference1Tab(j)
1768 ,l_pktReference2Tab(j)
1769 ,l_pktReference3Tab(j)
1770 -- R12 Funds Management Uptake
1771 ,l_BurMethodcodeTab(j)
1772 from dual
1773 where l_DocTypTab(j) in ('CC_C_CO', 'CC_P_CO');
1774
1775 for i in l_ProjTab.FIRST..l_ProjTab.LAST loop
1776 IF P_DEBUG_MODE = 'Y' THEN
1777 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'CC - N, record count = '|| SQL%BULK_ROWCOUNT(i));
1778 END IF;
1779 end loop;
1780
1781 PA_DEBUG.Reset_Err_Stack; --3912094
1782 END IF;
1783 PA_DEBUG.Reset_Err_Stack; --3912094
1784
1785 --If budgetary control type is GL, then load all commitment transactions
1786 --(AP, PO, REQ, Contract Payments) into pa_bc_packets.
1787 --Also check if budget is linked then consider only open period
1788 --transactions else consider all transactions.
1789 ELSIF (p_bdgt_ctrl_type = ('GL')) THEN
1790
1791 PA_DEBUG.set_err_stack('GL'); --Bug 3912094
1792 IF P_DEBUG_MODE = 'Y' THEN
1793 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Bdgt Ctrl Type = GL');
1794 END IF;
1795
1796 IF (P_bdgt_intg_flag = 'Y') THEN
1797
1798 PA_DEBUG.set_err_stack('Insert Std, linked');
1799 IF P_DEBUG_MODE = 'Y' THEN
1800 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Bdgt Intg Flag = Y');
1801 END IF;
1802
1803 --For Other commitment transactions (AP,PO,REQ,CC_P_PAY,CC_C_PAY)
1804 --when there is a link
1805 FORALL k in l_ProjTab.FIRST..l_ProjTab.LAST
1806 insert into pa_bc_packets (
1807 PACKET_ID,
1808 PROJECT_ID,
1809 TASK_ID,
1810 EXPENDITURE_TYPE,
1811 EXPENDITURE_ITEM_DATE,
1812 ACTUAL_FLAG,
1813 STATUS_CODE,
1814 LAST_UPDATE_DATE,
1815 LAST_UPDATED_BY,
1816 CREATED_BY,
1817 CREATION_DATE,
1818 LAST_UPDATE_LOGIN,
1819 SET_OF_BOOKS_ID,
1820 JE_CATEGORY_NAME,
1821 JE_SOURCE_NAME,
1822 DOCUMENT_TYPE,
1823 EXPENDITURE_ORGANIZATION_ID,
1824 PERIOD_NAME,
1825 PERIOD_YEAR,
1826 PERIOD_NUM,
1827 DOCUMENT_HEADER_ID,
1828 DOCUMENT_DISTRIBUTION_ID,
1829 ENTERED_DR,
1830 ENTERED_CR,
1831 accounted_dr,
1832 accounted_cr,
1833 BUDGET_VERSION_ID,
1834 bc_packet_id,
1835 funds_process_mode,
1836 parent_bc_packet_id,
1837 gl_date,
1838 pa_date,
1839 txn_ccid,
1840 result_code,
1841 balance_posted_flag,
1842 org_id,
1843 burden_cost_flag,
1844 bc_commitment_id,
1845 old_budget_ccid
1846 --PA.M
1847 ,document_line_id
1848 ,summary_record_flag
1849 ,reference1
1850 ,reference2
1851 ,reference3
1852 -- R12 Funds Management Uptake
1853 ,burden_method_code
1854 )
1855 select
1856 p_packet_id,
1857 l_ProjTab(k),
1858 l_TaskTab(k),
1859 l_ExpTypTab(k),
1860 l_EiDateTab(k),
1861 'E',
1862 'P',
1863 sysdate,
1864 FND_GLOBAL.USER_ID,
1865 FND_GLOBAL.USER_ID,
1866 sysdate,
1867 FND_GLOBAL.LOGIN_ID,
1868 l_SobTab(k),
1869 l_CatNameTab(k),
1870 l_SrcNameTab(k),
1871 l_DocTypTab(k),
1872 l_ExpOrgTab(k),
1873 l_PeriodTab(k),
1874 l_PdYearTab(k),
1875 l_PdNumTab(k),
1876 l_DocHdrTab(k),
1877 l_DocDistTab(k),
1878 l_EntDrTab(k),
1879 l_EntCrTab(k),
1880 l_AcctDrTab(k),
1881 l_AcctCrTab(k),
1882 p_budget_version_id,
1883 l_BcPktTab(k),
1884 'B',
1885 l_ParBCPktTab(k),
1886 l_GlDateTab(k),
1887 l_PaDateTab(k),
1888 l_TxnCCIDTab(k),
1889 'P',
1890 'N',
1891 l_OrgIdTab(k),
1892 l_BurCstFlagTab(k),
1893 l_BcCommIdTab(k),
1894 l_BdgtCCIDTab(k)
1895 --Pa.M
1896 ,l_DocLineIdTab(k)
1897 , l_SummRecFlagTab(k)
1898 ,l_pktReference1Tab(k)
1899 ,l_pktReference2Tab(k)
1900 ,l_pktReference3Tab(k)
1901 -- R12 Funds Management Uptake
1902 ,l_BurMethodcodeTab(k)
1903 from gl_period_statuses gl
1904 where l_DocTypTab(k) in ('AP','PO','REQ','CC_C_PAY','CC_P_PAY')
1905 --and l_GlDateTab(k) = gl.end_date
1906 and trunc(l_GlDateTab(k)) between trunc(gl.start_date) and trunc(gl.end_date)
1907 and gl.application_id = 101
1908 and gl.set_of_books_id = p_sob_id
1909 and gl.adjustment_period_flag = 'N'
1910 and gl.closing_status in ( 'O', 'F');
1911
1912 for i in l_ProjTab.FIRST..l_ProjTab.LAST loop
1913 IF P_DEBUG_MODE = 'Y' THEN
1914 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'GL - Y, record count = '|| SQL%BULK_ROWCOUNT(i));
1915 END IF;
1916 end loop;
1917 PA_DEBUG.Reset_Err_Stack; --3912094
1918 ELSE
1919
1920 PA_DEBUG.set_err_stack('Insert Std, no link');
1921 IF P_DEBUG_MODE = 'Y' THEN
1922 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Bdgt Intg Flag = N');
1923 END IF;
1924
1925 --For Other commitment transactions (AP,PO,REQ,CC_C_PAY,CC_P_PAY)
1926 --when there is a link
1927 FORALL l in l_ProjTab.FIRST..l_ProjTab.LAST
1928 insert into pa_bc_packets (
1929 PACKET_ID,
1930 PROJECT_ID,
1931 TASK_ID,
1932 EXPENDITURE_TYPE,
1933 EXPENDITURE_ITEM_DATE,
1934 ACTUAL_FLAG,
1935 STATUS_CODE,
1936 LAST_UPDATE_DATE,
1937 LAST_UPDATED_BY,
1938 CREATED_BY,
1939 CREATION_DATE,
1940 LAST_UPDATE_LOGIN,
1941 SET_OF_BOOKS_ID,
1942 JE_CATEGORY_NAME,
1943 JE_SOURCE_NAME,
1944 DOCUMENT_TYPE,
1945 EXPENDITURE_ORGANIZATION_ID,
1946 PERIOD_NAME,
1947 PERIOD_YEAR,
1948 PERIOD_NUM,
1949 DOCUMENT_HEADER_ID,
1950 DOCUMENT_DISTRIBUTION_ID,
1951 ENTERED_DR,
1952 ENTERED_CR,
1953 accounted_dr,
1954 accounted_cr,
1955 BUDGET_VERSION_ID,
1956 bc_packet_id,
1957 funds_process_mode,
1958 parent_bc_packet_id,
1959 gl_date,
1960 pa_date,
1961 txn_ccid,
1962 result_code,
1963 balance_posted_flag,
1964 org_id,
1965 burden_cost_flag,
1966 bc_commitment_id,
1967 old_budget_ccid
1968 --PA.M
1969 ,document_line_id
1970 ,summary_record_flag
1971 ,reference1
1972 ,reference2
1973 ,reference3
1974 -- R12 Funds Management Uptake
1975 ,burden_method_code
1976 )
1977 select
1978 p_packet_id,
1979 l_ProjTab(l),
1980 l_TaskTab(l),
1981 l_ExpTypTab(l),
1982 l_EiDateTab(l),
1983 'E',
1984 'P',
1985 sysdate,
1986 FND_GLOBAL.USER_ID,
1987 FND_GLOBAL.USER_ID,
1988 sysdate,
1989 FND_GLOBAL.LOGIN_ID,
1990 l_SobTab(l),
1991 l_CatNameTab(l),
1992 l_SrcNameTab(l),
1993 l_DocTypTab(l),
1994 l_ExpOrgTab(l),
1995 l_PeriodTab(l),
1996 l_PdYearTab(l),
1997 l_PdNumTab(l),
1998 l_DocHdrTab(l),
1999 l_DocDistTab(l),
2000 l_EntDrTab(l),
2001 l_EntCrTab(l),
2002 l_AcctDrTab(l),
2003 l_AcctCrTab(l),
2004 p_budget_version_id,
2005 l_BcPktTab(l),
2006 'B',
2007 l_ParBCPktTab(l),
2008 l_GlDateTab(l),
2009 l_PaDateTab(l),
2010 l_TxnCCIDTab(l),
2011 'P',
2012 'N',
2013 l_OrgIdTab(l),
2014 l_BurCstFlagTab(l),
2015 l_BcCommIdTab(l),
2016 l_BdgtCCIDTab(l)
2017 --Pa.M
2018 , l_DocLineIdTab(l)
2019 , l_SummRecFlagTab(l)
2020 ,l_pktReference1Tab(l)
2021 ,l_pktReference2Tab(l)
2022 ,l_pktReference3Tab(l)
2023 -- R12 Funds Management Uptake
2024 ,l_BurMethodcodeTab(l)
2025 from dual
2026 where l_DocTypTab(l) in ('AP','PO','REQ','CC_C_PAY','CC_P_PAY');
2027
2028 for i in l_ProjTab.FIRST..l_ProjTab.LAST loop
2029 IF P_DEBUG_MODE = 'Y' THEN
2030 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'GL - N, record count = '|| SQL%BULK_ROWCOUNT(i));
2031 END IF;
2032 end loop;
2033 PA_DEBUG.Reset_Err_Stack; --3912094
2034
2035 END IF;
2036 PA_DEBUG.Reset_Err_Stack; --3912094
2037 END IF;
2038 commit;
2039 EXIT WHEN c_bc_comm%NOTFOUND;
2040 END LOOP;
2041 CLOSE c_bc_comm;
2042
2043 PA_DEBUG.set_err_stack('Inserted Commitments');
2044
2045 IF P_DEBUG_MODE = 'Y' THEN
2046 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Exiting Insert Commitment Txns');
2047 END IF;
2048
2049 --Reset the error stack when returning to the calling program
2050 PA_DEBUG.Reset_Err_Stack;
2051 PA_DEBUG.Reset_Err_Stack; -- Bug 5064900
2052
2053 EXCEPTION
2054 WHEN OTHERS THEN
2055 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_BGT_BASELINE_PKG'
2056 ,p_procedure_name => 'INSERT_COMMITMENT_TXNS' -- Bug 5064900
2057 ,p_error_text => PA_DEBUG.G_Err_Stack );
2058
2059 IF c_bc_comm%ISOPEN THEN
2060 close c_bc_comm;
2061 END IF;
2062
2063 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2064 x_error_message_code := (SQLCODE||' '||SQLERRM);
2065 PA_DEBUG.Reset_Err_Stack; -- Bug 5064900
2066 RAISE;
2067 END INSERT_COMMITMENT_TXNS;
2068
2069 ---------------------------------------------------------------------------------------------
2070 --R12 Funds Management Uptake : As per new logic, fundscheck will be performed only during
2071 --budget baseline and not during submit.Hence obsoleting this procedure INSERT_DELTA_TXNS
2072 --which holds logic for performing delta fundscheck of transactions which were
2073 --fundschecked against submitted budget and having 'C' status code.
2074 ---------------------------------------------------------------------------------------------
2075
2076 --(MAIN)
2077 BEGIN
2078
2079 --Initialize the error stack
2080 PA_DEBUG.init_err_stack('PA_BGT_BASELINE_PKG.Maintain_Bal_Fchk');
2081
2082 fnd_msg_pub.initialize;
2083
2084 --Initialize the return status to success
2085 x_return_status := FND_API.G_RET_STS_SUCCESS;
2086
2087 IF P_DEBUG_MODE = 'Y' THEN
2088 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);
2089 END IF;
2090
2091 --Bug 6524116
2092 SELECT org_id INTO l_org_id FROM pa_projects_all WHERE project_id = p_project_id;
2093
2094 --Select SET OF BOOKS ID
2095 IF P_DEBUG_MODE = 'Y' THEN
2096 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Before selecting SOB');
2097 END IF;
2098
2099 select to_number(set_of_books_id) into l_sob_id from pa_implementations_all --Bug 6524116
2100 where org_id = l_org_id;
2101
2102 IF P_DEBUG_MODE = 'Y' THEN
2103 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'After selecting SOB = '|| l_sob_id);
2104 END IF;
2105
2106 /* =============================================================================================== +
2107 FOLLOWING CODE MOVED TO PA_BUDGET_FUND_PKG (PABBFNDB.pls)
2108
2109 --Before start of baselining call sweeper to sweep approved packets sitting in
2110 --pa_bc_packets
2111 --Bug 2779986: Run sweeper irrespective of the p_bdgt_ctrl_type
2112 --Revert Bug 2779986 fix: Sweeper process is to be run only the first time this API is
2113 --called from PA_BUDGET_FUNDS_PKG. There are 2 calls to this API, first for
2114 --for p_bdgt_ctrl_type = GL and then for p_bdgt_ctrl_type = CC, hence running the
2115 --sweeper the first time will suffice as per design.
2116
2117 if (p_bdgt_ctrl_type = 'GL') then
2118 pa_sweeper.update_act_enc_balance(
2119 x_return_status => x_return_status,
2120 x_error_message_code => x_error_message_code
2121 --PA.M
2122 ,P_Project_Id => P_project_id);
2123 end if;
2124 ========================================================================================================== */
2125
2126 --lock pa_bc_balances records for the budget version
2127 open c_bal_lock;
2128
2129 --R12 Funds Management Uptake : Deleted logic to pouplate l_reservemode,l_bsnlpkt_id,l_fcreqd
2130 --and l_deltapktid as these variables are obsolete with new architecture.
2131 --Budget fundscheck will always be fired with calling mode = 'RESERVE_BASELINE' and fundscheck
2132 --has to be performed.
2133
2134 /* =============================================================================================== +
2135 FOLLOWING CODE MOVED TO PA_BUDGET_FUND_PKG (PABBFNDB.pls)
2136
2137 IF P_DEBUG_MODE = 'Y' THEN
2138 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Calling Insert BGT Balances');
2139 END IF;
2140 PA_DEBUG.set_err_stack('M:Insert BGT Balances');
2141 --create pa_bc_balances record
2142
2143 INSERT_BGT_BALANCES(
2144 p_project_id => p_project_id,
2145 p_budget_version_id => p_budget_version_id,
2146 p_set_of_books_id => l_sob_id,
2147 p_bdgt_intg_flag => p_bdgt_intg_flag,
2148 --p_fc_reqd => l_FcReqd, --R12 Funds Management Uptake : obsolete variable
2149 x_return_status => x_return_status,
2150 x_error_message_code => x_error_message_code );
2151
2152 IF P_DEBUG_MODE = 'Y' THEN
2153 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'After Insert BGT Balances, RetSts = ' || x_return_status);
2154 END IF;
2155
2156 PA_DEBUG.Reset_Err_Stack; --3912094
2157 ========================================================================================================== */
2158
2159 PA_DEBUG.set_err_stack('M:Is FC Reqd');
2160
2161 -- R12 Funds Management Uptake : Obsolete logic to DELETE packets associated with
2162 -- Draft version Id lying in status 'B'/'R'/'T'.With previous functionality these
2163 -- records were created if baselining errors for some reason after funds check OR
2164 -- if user rebaselines.
2165 -- With new architecture baselining is performed before fundscheck and fundscheck
2166 -- is fired only during baselining for the newly baselined version.If fundscheck fails
2167 -- then baselining will be rolled back and hence there wont be any 'B' status data
2168 -- lying in pa_bc_packets and sweeper will clean the 'R' and 'T' status records.
2169
2170 -- R12 Funds Management Uptake : Removed check of 'IF fc required'
2171
2172 -- Get a new packet id
2173 select gl_bc_packets_s.nextval into l_packet_id from dual;
2174
2175 IF P_DEBUG_MODE = 'Y' THEN
2176 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'PacketId = '|| l_packet_id);
2177 END IF;
2178
2179 PA_DEBUG.set_err_stack('M:Create Dir Cost');
2180
2181 -- Insert commitments into PA_BC_PACKETS
2182 IF P_DEBUG_MODE = 'Y' THEN
2183 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Calling Insert Commitment Txns');
2184 END IF;
2185
2186 INSERT_COMMITMENT_TXNS(
2187 p_packet_id => l_packet_id,
2188 p_sob_id => l_sob_id,
2189 p_project_id => p_project_id,
2190 p_budget_version_id => p_budget_version_id,
2191 p_bdgt_ctrl_type => p_bdgt_ctrl_type,
2192 p_bdgt_intg_flag => p_bdgt_intg_flag,
2193 x_return_status => x_return_status,
2194 x_error_message_code => x_error_message_code);
2195
2196 IF P_DEBUG_MODE = 'Y' THEN
2197 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'After Insert Commitment Txns, RetSts = ' ||x_return_status);
2198 END IF;
2199 PA_DEBUG.Reset_Err_Stack; --3912094
2200
2201 -- If Budgetary Control is GL then also
2202 -- insert vendor invoices, expense reports and BTC txns.
2203 if (p_bdgt_ctrl_type = 'GL') then
2204
2205 PA_DEBUG.set_err_stack('M:Insert VIERBTC');
2206 IF P_DEBUG_MODE = 'Y' THEN
2207 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Calling Insert VI ER BTC');
2208 END IF;
2209
2210 INSERT_VI_ER_BTC_TXNS(
2211 p_packet_id => l_packet_id,
2212 p_sob_id => l_sob_id,
2213 p_project_id => p_project_id,
2214 p_budget_version_id => p_budget_version_id,
2215 p_bdgt_intg_flag => p_bdgt_intg_flag,
2216 x_return_status => x_return_status,
2217 x_error_message_code => x_error_message_code);
2218 IF P_DEBUG_MODE = 'Y' THEN
2219 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'After Insert VI ER BTC, RetSts = ' ||x_return_status);
2220 END IF;
2221 PA_DEBUG.Reset_Err_Stack; --3912094
2222
2223 end if;
2224
2225 -- R12 Funds Management Uptake : Deleted call to main fundscheck API in 'CHECK_BASELINE' mode
2226 -- R12 Funds Management Uptake : Deleted call to PA_BUDGET_FUND_PKG.Upd_Bdgt_Acct_Bal_No_Fck as
2227 -- it will be handled during tieback
2228 -- R12 Funds Management Uptake : Deleted resrver mode check as this procedure is fired for reserve mode only
2229
2230 if (p_bdgt_ctrl_type = 'GL') then
2231
2232 pa_debug.Set_User_Lock_Mode(x_TimeOut => 10);
2233 --acquire lock
2234 IF (pa_debug.acquire_user_lock('BSLNFCHKLOCK:'||to_char(p_project_id)) = 0) THEN
2235
2236 IF P_DEBUG_MODE = 'Y' THEN
2237 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Acquired lock on Proj ' || p_project_id);
2238 END IF;
2239 -- PA_DEBUG.set_err_stack('M:Acquired Lock'); 3912094 This is not required
2240
2241 ELSE
2242
2243 --Unable to acquire user lock
2244 IF P_DEBUG_MODE = 'Y' THEN
2245 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Could not acquire lock');
2246 END IF;
2247 -- PA_DEBUG.set_err_stack('M:Could not acquire lock BSLNFCHKLOCK');
2248
2249 raise ACQUIRE_LOCK_EXCEPTION;
2250
2251 END IF;
2252
2253 end if;
2254
2255 -- R12 Funds Management Uptake : Deleted delta logic as fundscheck will be perfomed only once i.e. during baselining
2256 -- R12 Funds Management Uptake : Deleted logic to update status_code from 'C' to 'A' . With new architecture
2257 -- there will be no delta fundscheck and hence no 'C' status.
2258
2259 PA_DEBUG.set_err_stack('M:FC Reserve Mode');
2260 IF P_DEBUG_MODE = 'Y' THEN
2261 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Call Funds Check in Reserve Mode');
2262 END IF;
2263
2264 --Call Funds Check for the delta transactions
2265 --in RESERVE_BASELINE mode
2266 IF PA_FUNDS_CONTROL_PKG.PA_FUNDS_CHECK(
2267 p_calling_module => 'RESERVE_BASELINE',
2268 p_conc_flag => null,
2269 p_set_of_book_id => l_sob_id,
2270 p_packet_id => l_packet_id,
2271 p_mode => 'B',
2272 p_partial_flag => 'N',
2273 p_reference1 => p_bdgt_intg_flag,
2274 p_reference2 => to_char(p_project_id),
2275 p_reference3 => to_char(p_budget_version_id),
2276 x_return_status => l_return_status,
2277 x_error_stage => x_error_stage,
2278 x_error_msg => x_error_msg) then
2279
2280 IF P_DEBUG_MODE = 'Y' THEN
2281 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'FCHK in Reserve Mode called');
2282 END IF;
2283 --Call Update Bdgt Acct Lines API
2284
2285 -- R12 Funds Management Uptake : Deleted call to PA_FUNDS_CONTROL_PKG.upd_bdgt_encum_bal
2286 -- as it will be fired during tieback .
2287
2288 -- R12 Funds Management Uptake : Added below update for updating pa_bc_packets with
2289 -- uncommited newly created budget version id.
2290
2291 If l_return_status not in ('F','T') then
2292
2293 UPDATE pa_bc_packets
2294 SET budget_version_id = p_baselined_budget_version_id
2295 WHERE packet_id = l_packet_id
2296 AND budget_version_id = p_budget_version_id;
2297
2298 -- R12 Funds Management Uptake : Update pa_bc_balances (draft version) with the latest budget version ..
2299
2300 UPDATE pa_bc_balances
2301 SET budget_version_id = p_baselined_budget_version_id
2302 WHERE budget_version_id = p_budget_version_id;
2303
2304 End If;
2305
2306 PA_DEBUG.Reset_Err_Stack; --3912094
2307
2308 if (l_return_status = 'F') then
2309
2310 -- PA_DEBUG.set_err_stack('M:Funds Check process in reserve mode returned failure');
2311 -- , Stage = '||x_error_stage || ' Msg from Funds Check = '|| x_error_msg);
2312 IF P_DEBUG_MODE = 'Y' THEN
2313 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'FCHK in Reserve Mode returned failure');
2314 END IF;
2315
2316 raise FND_API.G_EXC_ERROR;
2317
2318 elsif (l_return_status = 'T') then
2319
2320 -- PA_DEBUG.set_err_stack('M:Funds Check process in reserve mode returned fatal error');
2321 IF P_DEBUG_MODE = 'Y' THEN
2322 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'FCHK in Reserve Mode returned fatal error');
2323 END IF;
2324
2325 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2326
2327 end if;
2328 IF P_DEBUG_MODE = 'Y' THEN
2329 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'End of FCHK, l_return_status = ' || l_return_status);
2330 END IF;
2331
2332 ELSE
2333
2334 IF P_DEBUG_MODE = 'Y' THEN
2335 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'FCHK in Reserve Mode not called');
2336 END IF;
2337 -- PA_DEBUG.set_err_stack('M:Funds Check process in reserve mode returned failure');
2338 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2339
2340 END IF;
2341
2342
2343 PA_DEBUG.set_err_stack('M:End of API');
2344 IF P_DEBUG_MODE = 'Y' THEN
2345 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'End of API, RetSts = ' || x_return_status);
2346 END IF;
2347
2348 close c_bal_lock;
2349
2350 --Reset the error stack when returning to the calling program
2351 PA_DEBUG.Reset_Err_Stack;
2352 PA_DEBUG.Reset_Err_Stack; -- Bug 5064900
2353 PA_DEBUG.Reset_Err_Stack; -- Bug 5064900
2354
2355 /**************************************************
2356 Sweeper to be called after the tieback API is called
2357 *************************************************/
2358
2359 --We need to issue the below explicit commit since this API is
2360 --in autonomous mode. Or else will encounter ORA-6519 error
2361 commit;
2362
2363 EXCEPTION
2364 when resource_busy then
2365 IF P_DEBUG_MODE = 'Y' THEN
2366 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'In resource busy');
2367 END IF;
2368 if c_bal_lock%isopen then
2369 close c_bal_lock ;
2370 end if;
2371 PA_DEBUG.Reset_Err_Stack; --Bug 5064900
2372 WHEN ACQUIRE_LOCK_EXCEPTION THEN
2373 rollback;
2374 IF P_DEBUG_MODE = 'Y' THEN
2375 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Cannot Acquire lock BSLNFCHKLOCK for GL');
2376 END IF;
2377
2378 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_BGT_BASELINE_PKG'
2379 ,p_procedure_name => 'MAINTAIN_BAL_FCHK' --Bug 5064900
2380 ,p_error_text => PA_DEBUG.G_Err_Stack );
2381
2382 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2383 x_error_message_code := 'PA_BC_CANNOT_ACQUIRE_LOCK';
2384
2385 if c_bal_lock%isopen then
2386 close c_bal_lock;
2387 end if;
2388 PA_DEBUG.Reset_Err_Stack; -- Bug 5064900
2389 PA_DEBUG.Reset_Err_Stack; -- Bug 5064900
2390 raise;
2391
2392 WHEN FND_API.G_EXC_ERROR THEN
2393 --rollback; this exception raised when FC fails
2394 commit; -- commit required to commit failure status_code
2395
2396 IF P_DEBUG_MODE = 'Y' THEN
2397 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Funds check returned failure');
2398 END IF;
2399
2400 IF (pa_debug.release_user_lock('BSLNFCHKLOCK:'||to_char(p_project_id)) = 0) THEN
2401 pa_debug.g_err_stage := ' ' || to_char(p_project_id);
2402 END IF;
2403
2404 --When funds check fails due to insufficient funds then
2405 --we should not add error message to error stack.
2406 /*FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_BGT_BASELINE_PKG'
2407 ,p_procedure_name => PA_DEBUG.G_Err_Stack ); */
2408
2409 x_return_status := FND_API.G_RET_STS_ERROR;
2410
2411 if (p_bdgt_ctrl_type = 'GL') then
2412 x_error_message_code := 'PA_BC_BASELINE_FCHK_FAILED';
2413 elsif (p_bdgt_ctrl_type = 'CC') then
2414 x_error_message_code := 'PA_BC_CC_BSLN_FCHK_FAILED';
2415 end if;
2416
2417 if c_bal_lock%isopen then
2418 close c_bal_lock;
2419 end if;
2420
2421 PA_DEBUG.Reset_Err_Stack; -- Bug 5064900
2422 PA_DEBUG.Reset_Err_Stack; -- Bug 5064900
2423
2424 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2425 If nvl(l_return_status,'P') = 'T' then
2426 commit; -- FC exception
2427 Else
2428 rollback; -- this package exception
2429 End if;
2430
2431 IF P_DEBUG_MODE = 'Y' THEN
2432 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'Funds check returned fatal error');
2433 END IF;
2434
2435 IF (pa_debug.release_user_lock('BSLNFCHKLOCK:'||to_char(p_project_id)) = 0) THEN
2436 pa_debug.g_err_stage := ' ' || to_char(p_project_id);
2437 END IF;
2438
2439 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_BGT_BASELINE_PKG'
2440 ,p_procedure_name => 'MAINTAIN_BAL_FCHK' -- Bug 5064900
2441 ,p_error_text => PA_DEBUG.G_Err_Stack );
2442
2443 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2444 x_error_message_code := (SQLCODE||' '||SQLERRM);
2445
2446 IF P_DEBUG_MODE = 'Y' THEN
2447 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || SQLERRM);
2448 END IF;
2449
2450 if c_bal_lock%isopen then
2451 close c_bal_lock;
2452 end if;
2453 PA_DEBUG.Reset_Err_Stack; -- Bug 5064900
2454 PA_DEBUG.Reset_Err_Stack; -- Bug 5064900
2455 raise;
2456
2457 WHEN OTHERS THEN
2458 rollback;
2459 IF P_DEBUG_MODE = 'Y' THEN
2460 pa_fck_util.debug_msg('MAINTAIN_BAL_FCHK: ' || 'In others of main');
2461 END IF;
2462
2463 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_BGT_BASELINE_PKG'
2464 ,p_procedure_name => 'MAINTAIN_BAL_FCHK' -- Bug 5064900
2465 ,p_error_text => PA_DEBUG.G_Err_Stack );
2466
2467 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2468 x_error_message_code := (SQLCODE||' '||SQLERRM);
2469
2470 if c_bal_lock%isopen then
2471 close c_bal_lock;
2472 end if;
2473 PA_DEBUG.Reset_Err_Stack; -- Bug 5064900
2474 RAISE;
2475
2476 END MAINTAIN_BAL_FCHK;
2477
2478 END PA_BGT_BASELINE_PKG;