[Home] [Help]
492: g_ap_matched_case Varchar2(1);
493:
494: -- This procedure will execute the budget account validation, account level FC
495: -- and update budget lines with the new derived accounts, call PROCEDURE
496: -- Build_account_summary to build pa_budget_acct_lines
497:
498: PROCEDURE DO_BUDGET_BASELINE_TIEBACK(p_packet_id IN NUMBER,
499: p_return_status OUT NOCOPY VARCHAR2);
500:
497:
498: PROCEDURE DO_BUDGET_BASELINE_TIEBACK(p_packet_id IN NUMBER,
499: p_return_status OUT NOCOPY VARCHAR2);
500:
501: -- This procedure will build pa_budget_acct_lines
502: -- Called from DO_BUDGET_BASELINE_TIEBACK
503:
504: PROCEDURE Build_account_summary(P_budget_version_id IN NUMBER,
505: P_balance_type IN VARCHAR2,
524: P_budget_ccid IN g_tab_budget_ccid%TYPE,
525: P_allow_flag IN g_tab_allow_flag%TYPE,
526: P_result_code IN VARCHAR2);
527:
528: -- This procedure will update pa_budget_acct_lines with a failure status.
529: -- This procedure is an AUTONOMOUS procedure
530: -- This procedure will be called from do_budget_baseline_tieback
531:
532: PROCEDURE Update_failure_in_acct_summary(P_budget_version_id IN NUMBER,
584: x_result_code OUT NOCOPY Varchar2);
585:
586: -- --------------------------------------------------------------------------------+
587: -- Following procedure resets funds_check_status_code and result code on
588: -- pa_budget_acct_lines for the draft version ..
589: -- --------------------------------------------------------------------------------+
590: PROCEDURE Reset_status_code_on_summary(p_budget_version_id IN Number);
591:
592: -- --------------------------------------------------------------------------------+
14218:
14219: End Fail_bc_pkt_during_baseline;
14220:
14221: -- --------------------------------------------------------------------------------+
14222: -- This procedure will be called from do_budget_baseline to fail pa_budget_acct_lines
14223: -- pa_budget_acct_lines (by account and period) that fails account level validation
14224: -- or account level funds check. Procedure is AUTONOMOUS.
14225: -- --------------------------------------------------------------------------------+
14226: PROCEDURE Update_failure_in_acct_summary(P_budget_version_id IN NUMBER,
14219: End Fail_bc_pkt_during_baseline;
14220:
14221: -- --------------------------------------------------------------------------------+
14222: -- This procedure will be called from do_budget_baseline to fail pa_budget_acct_lines
14223: -- pa_budget_acct_lines (by account and period) that fails account level validation
14224: -- or account level funds check. Procedure is AUTONOMOUS.
14225: -- --------------------------------------------------------------------------------+
14226: PROCEDURE Update_failure_in_acct_summary(P_budget_version_id IN NUMBER,
14227: P_period_name IN g_tab_period_name%TYPE,
14238: log_message(p_msg_token1=>'Update_failure_in_acct_summary: No of records to fail:'||P_budget_ccid.COUNT);
14239: End If;
14240:
14241: Forall i in P_budget_ccid.FIRST..P_budget_ccid.LAST
14242: Update pa_budget_acct_lines
14243: set funds_check_status_code = 'R',
14244: funds_check_result_code = P_result_code
14245: where budget_version_id = P_budget_version_id
14246: and code_combination_id = P_budget_ccid(i)
14253: log_message(p_msg_token1=>'Update_failure_in_acct_summary: Records Updated:'||SQL%ROWCOUNT);
14254: log_message(p_msg_token1=>'Update_failure_in_acct_summary: Fail other records with F170');
14255: End If;
14256:
14257: Update pa_budget_acct_lines
14258: set funds_check_status_code = 'R',
14259: funds_check_result_code = 'F170'
14260: where budget_version_id = P_budget_version_id
14261: and nvl(funds_check_status_code,'P') <> 'R';
14435:
14436: -- --------------------------------------------------------------------------------+
14437: -- This procedure will execute the budget account validation, account level FC
14438: -- and update budget lines with the new derived accounts, call PROCEDURE
14439: -- Build_account_summary to build pa_budget_acct_lines
14440: -- Parameters:
14441: -- p_packet_id : packet being funds checked
14442: -- p_return_status : 'S' for success and 'F' for failure
14443: -- --------------------------------------------------------------------------------+
14568: -- are any combo having sum(available amount) < 0
14569:
14570: Cursor c_acct_lines(p_current_bvid IN NUMBER) is
14571: select pbl.gl_period_name, pbl.code_combination_id ,'N' allow_flag
14572: from pa_budget_acct_lines pbl
14573: where (pbl.budget_version_id,pbl.code_combination_id,pbl.start_date,pbl.end_date) in
14574: (select pbl.budget_version_id,pbl.code_combination_id,pbl.start_date,pbl.end_date
14575: from pa_budget_acct_lines pbl
14576: where (pbl.budget_version_id,pbl.code_combination_id,pbl.start_date,pbl.end_date) in
14571: select pbl.gl_period_name, pbl.code_combination_id ,'N' allow_flag
14572: from pa_budget_acct_lines pbl
14573: where (pbl.budget_version_id,pbl.code_combination_id,pbl.start_date,pbl.end_date) in
14574: (select pbl.budget_version_id,pbl.code_combination_id,pbl.start_date,pbl.end_date
14575: from pa_budget_acct_lines pbl
14576: where (pbl.budget_version_id,pbl.code_combination_id,pbl.start_date,pbl.end_date) in
14577: (select distinct a.budget_version_id,a.budget_ccid,a.fc_start_date,a.fc_end_date
14578: from pa_bc_packets a
14579: where a.budget_version_id = p_current_bvid)
14582: )
14583: UNION
14584: -- Bug 5206341 : Cursor to validate account level balances for Open and Closed periods
14585: select pbl.gl_period_name, pbl.code_combination_id ,'N' allow_flag
14586: from pa_budget_acct_lines pbl
14587: where (pbl.budget_version_id,pbl.code_combination_id) in
14588: (select pbl.budget_version_id,pbl.code_combination_id
14589: from pa_budget_acct_lines pbl
14590: where pbl.budget_version_id = p_current_bvid
14585: select pbl.gl_period_name, pbl.code_combination_id ,'N' allow_flag
14586: from pa_budget_acct_lines pbl
14587: where (pbl.budget_version_id,pbl.code_combination_id) in
14588: (select pbl.budget_version_id,pbl.code_combination_id
14589: from pa_budget_acct_lines pbl
14590: where pbl.budget_version_id = p_current_bvid
14591: group by pbl.budget_version_id,pbl.code_combination_id
14592: having sum(nvl(pbl.Curr_Ver_Available_Amount,0)) < 0
14593: )
14776: x_budget_amount_code=> pa_budget_fund_pkg.g_budget_amount_code,
14777: x_prev_budget_version_id=>pa_budget_fund_pkg.g_cost_prev_bvid,
14778: x_mode=>'PASS');
14779:
14780: -- Autonomous used as we're updating pa_budget_acct_lines in AUTONOMOUS
14781: -- mode for failures later ..
14782: End If;
14783:
14784: -- 1.7: Initalize l_acct_changed
15034: If g_debug_mode = 'Y' then
15035: log_message(p_msg_token1=>l_program_name||': <> CF, Calling Update_failure_in_acct_summary');
15036: End If;
15037:
15038: -- Autonomous call to update pa_budget_acct_lines for the draft version ...
15039: UPDATE_FAILURE_IN_ACCT_SUMMARY(P_budget_version_id => l_draft_budget_version_id,
15040: P_period_name => g_tab_period_name,
15041: P_budget_ccid => t_draft_ccid,
15042: P_allow_flag => g_tab_allow_flag,
15046: If g_debug_mode = 'Y' then
15047: log_message(p_msg_token1=>l_program_name||': = CF, Calling Update_failure_in_acct_summary');
15048: End If;
15049:
15050: -- Autonomous call to update pa_budget_acct_lines for the draft version ...
15051: UPDATE_FAILURE_IN_ACCT_SUMMARY(P_budget_version_id => l_draft_budget_version_id,
15052: P_period_name => g_tab_period_name,
15053: P_budget_ccid => t_bud_ccid,
15054: P_allow_flag => g_tab_allow_flag,
15462: end loop;
15463: End If;
15464: -- -------------------------------------------------------------------------------------------------+
15465:
15466: -- Autonomous call to update pa_budget_acct_lines for the draft version ...
15467: UPDATE_FAILURE_IN_ACCT_SUMMARY(P_budget_version_id => l_draft_budget_version_id,
15468: P_period_name => g_tab_period_name,
15469: P_budget_ccid => t_draft_ccid,
15470: P_allow_flag => g_tab_allow_flag,
15484:
15485: End If;
15486: -- -------------------------------------------------------------------------------------------------+
15487:
15488: -- Autonomous call to update pa_budget_acct_lines for the draft version ...
15489: UPDATE_FAILURE_IN_ACCT_SUMMARY(P_budget_version_id => l_draft_budget_version_id,
15490: P_period_name => g_tab_period_name,
15491: P_budget_ccid => t_bud_ccid,
15492: P_allow_flag => g_tab_allow_flag,
15780: End If;
15781:
15782: -- C. Update Draft version (account summary table) to failure ..
15783:
15784: -- Autonomous call to update pa_budget_acct_lines for the draft version ...
15785:
15786: UPDATE_FAILURE_IN_ACCT_SUMMARY(P_budget_version_id => l_draft_budget_version_id,
15787: P_period_name => g_tab_period_name,
15788: P_budget_ccid => g_tab_budget_ccid,
15837:
15838: End If; -- II If p_return_status = 'S' then (step 5..7)
15839:
15840: -- ------------------------- STEP 8 -----------------------------------+
15841: -- Step 8.0: Update Pass status/result code on pa_budget_acct_lines
15842: -- Step 8.1: Update Pass status/result code on current version (CF/Baseline/Yearend)
15843: /* ==============================================================================================+
15844: -- STEP NOT REQUIRED AS acct. summary initialized/build with 'P101' and 'A' ...
15845:
15847: (pa_budget_fund_pkg.g_processing_mode <> 'CHECK_FUNDS' and p_return_status = 'S')
15848: ) then
15849:
15850: If g_debug_mode = 'Y' then
15851: log_message(p_msg_token1=>'End:'||l_program_name||'Update Pass status/result code on pa_budget_acct_lines - Current');
15852: End If;
15853:
15854: Update pa_budget_acct_lines
15855: set funds_check_status_code = 'A',
15850: If g_debug_mode = 'Y' then
15851: log_message(p_msg_token1=>'End:'||l_program_name||'Update Pass status/result code on pa_budget_acct_lines - Current');
15852: End If;
15853:
15854: Update pa_budget_acct_lines
15855: set funds_check_status_code = 'A',
15856: funds_check_result_code = 'P101'
15857: where budget_version_id = l_current_budget_version_id
15858: and nvl(funds_check_status_code,'A') <> 'R';
15868: If (pa_budget_fund_pkg.g_processing_mode <> 'CHECK_FUNDS' and p_return_status = 'S')
15869: then
15870:
15871: If g_debug_mode = 'Y' then
15872: log_message(p_msg_token1=>'End:'||l_program_name||'Update Pass status/result code on pa_budget_acct_lines - Draft');
15873: End If;
15874:
15875: Update pa_budget_acct_lines
15876: set funds_check_status_code = 'A',
15871: If g_debug_mode = 'Y' then
15872: log_message(p_msg_token1=>'End:'||l_program_name||'Update Pass status/result code on pa_budget_acct_lines - Draft');
15873: End If;
15874:
15875: Update pa_budget_acct_lines
15876: set funds_check_status_code = 'A',
15877: funds_check_result_code = 'P101'
15878: where budget_version_id = l_draft_budget_version_id;
15879:
15936:
15937: END DO_BUDGET_BASELINE_TIEBACK;
15938:
15939: -- --------------------------------------------------------------------------------+
15940: -- This procedure will build pa_budget_acct_lines
15941: -- Called from DO_BUDGET_BASELINE_TIEBACK and PA_GL_CBC_CONFIRMATION (for revenue)
15942: -- Parameters:
15943: -- p_budget_version_id : Current budget version being funds checked
15944: -- p_balance_type : E (For Top Down) and B (Bottom Up)
15996: l_login := fnd_global.login_id;
15997: l_request := fnd_global.conc_request_id;
15998: l_limit := 500;
15999: -- ------------------------------------------------------------------------+
16000: -- 1.0: Creating PA_Budget_Acct_Lines record for the current budget version
16001: -- ------------------------------------------------------------------------+
16002:
16003: -- To make sure that there is no budget acct. line .. acct. gen. seems to be creating summary ..
16004: Delete from PA_Budget_Acct_Lines
16000: -- 1.0: Creating PA_Budget_Acct_Lines record for the current budget version
16001: -- ------------------------------------------------------------------------+
16002:
16003: -- To make sure that there is no budget acct. line .. acct. gen. seems to be creating summary ..
16004: Delete from PA_Budget_Acct_Lines
16005: where budget_version_id = P_budget_version_id;
16006:
16007: If g_debug_mode = 'Y' then
16008: log_message(p_msg_token1=>l_program_name||'Deleted '||SQL%ROWCOUNT||' PA_Budget_Acct_Lines records');
16004: Delete from PA_Budget_Acct_Lines
16005: where budget_version_id = P_budget_version_id;
16006:
16007: If g_debug_mode = 'Y' then
16008: log_message(p_msg_token1=>l_program_name||'Deleted '||SQL%ROWCOUNT||' PA_Budget_Acct_Lines records');
16009: End If;
16010:
16011: If g_debug_mode = 'Y' then
16012: log_message(p_msg_token1=>l_program_name||':Creating records');
16036: end loop;
16037: End If;
16038:
16039: forall i in 1..l_count
16040: INSERT INTO PA_Budget_Acct_Lines (
16041: Budget_Acct_Line_ID,
16042: Budget_version_ID,
16043: GL_Period_Name,
16044: Start_Date,
16056: Last_Update_Login,
16057: Request_ID,
16058: funds_check_status_code,
16059: funds_check_result_code)
16060: VALUES(PA_Budget_Acct_Lines_S.NEXTVAL, -- Budget acct line id
16061: P_budget_version_id, -- Budget version id
16062: t_period_name(i), -- Period name
16063: t_start_date(i), -- Start date
16064: t_end_date(i), -- End date
16093: end loop;
16094: close c_acct_lines;
16095:
16096: If g_debug_mode = 'Y' then
16097: log_message(p_msg_token1=>l_program_name||' After PA_Budget_Acct_Lines Insert DML');
16098: End If;
16099: -- -------------------------------------------------------------------+
16100: -- 2.0: Update previous amounts (budget and available)
16101: -- -------------------------------------------------------------------+
16103: If g_debug_mode = 'Y' then
16104: log_message(p_msg_token1=>l_program_name||':Updating previous amounts');
16105: End If;
16106:
16107: Update PA_Budget_Acct_Lines pbl
16108: set (pbl.Prev_Ver_Budget_Amount,pbl.Prev_Ver_Available_Amount) =
16109: (select pbl1.Curr_Ver_Budget_Amount,
16110: pbl1.Curr_Ver_Available_Amount
16111: from PA_Budget_Acct_Lines pbl1
16107: Update PA_Budget_Acct_Lines pbl
16108: set (pbl.Prev_Ver_Budget_Amount,pbl.Prev_Ver_Available_Amount) =
16109: (select pbl1.Curr_Ver_Budget_Amount,
16110: pbl1.Curr_Ver_Available_Amount
16111: from PA_Budget_Acct_Lines pbl1
16112: where pbl1.budget_version_id = P_prev_budget_version_id
16113: and pbl1.code_combination_id = pbl.code_combination_id
16114: and pbl1.start_date = pbl.start_date)
16115: where pbl.budget_version_id = p_budget_version_id
16114: and pbl1.start_date = pbl.start_date)
16115: where pbl.budget_version_id = p_budget_version_id
16116: and exists
16117: (select 1
16118: from PA_Budget_Acct_Lines pbl2
16119: where pbl2.budget_version_id = P_prev_budget_version_id
16120: and pbl2.code_combination_id = pbl.code_combination_id
16121: and pbl2.start_date = pbl.start_date);
16122: -- Bottom "exists clause" reqd, else if there is no record then the
16134: -- That means there were txn. for 10 against prev. budget (100-90)
16135: -- Current available = 50 - (100-90) = 40
16136: -- Accounted = 100-50 = 50
16137:
16138: Update PA_Budget_Acct_Lines
16139: set Curr_Ver_Available_Amount = Curr_Ver_Budget_Amount -
16140: (Prev_Ver_Budget_Amount - Prev_Ver_Available_Amount),
16141: Accounted_Amount = Curr_Ver_Budget_Amount - Prev_Ver_Budget_Amount
16142: -- Accounted_Amount = Prev_Ver_Budget_Amount - Curr_Ver_Budget_Amount
16159: -- This IF..END IF ..is reqd. as this procedure is called for the draft version
16160: -- during <> 'CF' mode ..to rebuild the acct. sumamry for the draft version
16161: -- In this case, the else part should fire ..
16162:
16163: INSERT INTO PA_BUDGET_ACCT_LINES (
16164: Budget_Acct_Line_ID,
16165: Budget_Version_ID,
16166: GL_Period_Name,
16167: Start_Date,
16181: funds_check_status_code,
16182: funds_check_result_code
16183: )
16184: SELECT
16185: PA_BUDGET_ACCT_LINES_S.nextval,
16186: P_Budget_Version_ID, -- current version
16187: BL1.GL_Period_Name,
16188: BL1.Start_Date,
16189: BL1.End_Date,
16200: l_login,
16201: l_request,
16202: 'A',
16203: 'P101'
16204: FROM PA_BUDGET_ACCT_LINES BL1
16205: WHERE BL1.Budget_Version_ID = P_prev_budget_version_id
16206: AND NOT EXISTS
16207: ( SELECT 'x'
16208: FROM PA_BUDGET_ACCT_LINES BL2
16204: FROM PA_BUDGET_ACCT_LINES BL1
16205: WHERE BL1.Budget_Version_ID = P_prev_budget_version_id
16206: AND NOT EXISTS
16207: ( SELECT 'x'
16208: FROM PA_BUDGET_ACCT_LINES BL2
16209: WHERE BL2.Budget_Version_ID = P_budget_version_id
16210: AND BL2.Code_Combination_ID = BL1.Code_Combination_ID
16211: AND BL2.Start_Date = BL1.Start_Date ) ;
16212:
16219: -- And also, the missing lines are created with current amount = 0
16220:
16221: Else
16222:
16223: INSERT INTO PA_BUDGET_ACCT_LINES (
16224: Budget_Acct_Line_ID,
16225: Budget_Version_ID,
16226: GL_Period_Name,
16227: Start_Date,
16241: funds_check_status_code,
16242: funds_check_result_code
16243: )
16244: SELECT
16245: PA_BUDGET_ACCT_LINES_S.nextval,
16246: P_Budget_Version_ID, -- current version
16247: BL1.GL_Period_Name,
16248: BL1.Start_Date,
16249: BL1.End_Date,
16260: l_login,
16261: l_request,
16262: 'A',
16263: 'P101'
16264: FROM PA_BUDGET_ACCT_LINES BL1
16265: WHERE BL1.Budget_Version_ID = P_prev_budget_version_id
16266: AND (BL1.Curr_Ver_Budget_Amount <> 0 OR
16267: BL1.Prev_Ver_Budget_Amount <> 0)
16268: -- this is to filter the zero $ lines ...
16267: BL1.Prev_Ver_Budget_Amount <> 0)
16268: -- this is to filter the zero $ lines ...
16269: AND NOT EXISTS
16270: ( SELECT 'x'
16271: FROM PA_BUDGET_ACCT_LINES BL2
16272: WHERE BL2.Budget_Version_ID = P_budget_version_id
16273: AND BL2.Code_Combination_ID = BL1.Code_Combination_ID
16274: AND BL2.Start_Date = BL1.Start_Date ) ;
16275: End If;
16317: BULK COLLECT
16318: into tt_gl_period_name,
16319: tt_code_combination_id,
16320: tt_result_code
16321: from pa_budget_acct_lines
16322: where budget_version_id = X_budget_version_id
16323: and funds_check_result_code like 'F%';
16324: Exception
16325: When no_data_found then
16336:
16337: If X_mode = 'FAIL' then
16338: If tt_code_combination_id.exists(1) then
16339: forall x in tt_result_code.FIRST..tt_result_code.LAST
16340: update pa_budget_acct_lines
16341: set funds_check_result_code = tt_result_code(x)
16342: where budget_version_id = X_budget_version_id
16343: and code_combination_id = tt_code_combination_id(x)
16344: and gl_period_name = tt_gl_period_name(x);
16360: End Build_account_summary_auto;
16361:
16362: -- --------------------------------------------------------------------------------+
16363: -- Following procedure resets funds_check_status_code and result code on
16364: -- pa_budget_acct_lines for the draft version ..
16365: -- 8/22: Initalizing to pass status ..
16366: -- --------------------------------------------------------------------------------+
16367: PROCEDURE Reset_status_code_on_summary(p_budget_version_id IN Number)
16368: IS
16368: IS
16369: PRAGMA AUTONOMOUS_TRANSACTION;
16370: Begin
16371:
16372: Update pa_budget_acct_lines
16373: set funds_check_status_code = 'A', --null,
16374: funds_check_result_code = 'P101' --null
16375: where budget_version_id = p_budget_version_id;
16376: --and (funds_check_status_code is not null or funds_check_result_code is not null);