[Home] [Help]
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;
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
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(
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.
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;
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
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:
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;
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
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;
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;
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
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,
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'
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,
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,
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.
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,
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;
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;