[Home] [Help]
495: g_ap_matched_case Varchar2(1);
496:
497: -- This procedure will execute the budget account validation, account level FC
498: -- and update budget lines with the new derived accounts, call PROCEDURE
499: -- Build_account_summary to build pa_budget_acct_lines
500:
501: PROCEDURE DO_BUDGET_BASELINE_TIEBACK(p_packet_id IN NUMBER,
502: p_return_status OUT NOCOPY VARCHAR2);
503:
500:
501: PROCEDURE DO_BUDGET_BASELINE_TIEBACK(p_packet_id IN NUMBER,
502: p_return_status OUT NOCOPY VARCHAR2);
503:
504: -- This procedure will build pa_budget_acct_lines
505: -- Called from DO_BUDGET_BASELINE_TIEBACK
506:
507: PROCEDURE Build_account_summary(P_budget_version_id IN NUMBER,
508: P_balance_type IN VARCHAR2,
527: P_budget_ccid IN g_tab_budget_ccid%TYPE,
528: P_allow_flag IN g_tab_allow_flag%TYPE,
529: P_result_code IN VARCHAR2);
530:
531: -- This procedure will update pa_budget_acct_lines with a failure status.
532: -- This procedure is an AUTONOMOUS procedure
533: -- This procedure will be called from do_budget_baseline_tieback
534:
535: PROCEDURE Update_failure_in_acct_summary(P_budget_version_id IN NUMBER,
587: x_result_code OUT NOCOPY Varchar2);
588:
589: -- --------------------------------------------------------------------------------+
590: -- Following procedure resets funds_check_status_code and result code on
591: -- pa_budget_acct_lines for the draft version ..
592: -- --------------------------------------------------------------------------------+
593: PROCEDURE Reset_status_code_on_summary(p_budget_version_id IN Number);
594:
595: -- --------------------------------------------------------------------------------+
14640:
14641: End Fail_bc_pkt_during_baseline;
14642:
14643: -- --------------------------------------------------------------------------------+
14644: -- This procedure will be called from do_budget_baseline to fail pa_budget_acct_lines
14645: -- pa_budget_acct_lines (by account and period) that fails account level validation
14646: -- or account level funds check. Procedure is AUTONOMOUS.
14647: -- --------------------------------------------------------------------------------+
14648: PROCEDURE Update_failure_in_acct_summary(P_budget_version_id IN NUMBER,
14641: End Fail_bc_pkt_during_baseline;
14642:
14643: -- --------------------------------------------------------------------------------+
14644: -- This procedure will be called from do_budget_baseline to fail pa_budget_acct_lines
14645: -- pa_budget_acct_lines (by account and period) that fails account level validation
14646: -- or account level funds check. Procedure is AUTONOMOUS.
14647: -- --------------------------------------------------------------------------------+
14648: PROCEDURE Update_failure_in_acct_summary(P_budget_version_id IN NUMBER,
14649: P_period_name IN g_tab_period_name%TYPE,
14660: log_message(p_msg_token1=>'Update_failure_in_acct_summary: No of records to fail:'||P_budget_ccid.COUNT);
14661: End If;
14662:
14663: Forall i in P_budget_ccid.FIRST..P_budget_ccid.LAST
14664: Update pa_budget_acct_lines
14665: set funds_check_status_code = 'R',
14666: funds_check_result_code = P_result_code
14667: where budget_version_id = P_budget_version_id
14668: and code_combination_id = P_budget_ccid(i)
14675: log_message(p_msg_token1=>'Update_failure_in_acct_summary: Records Updated:'||SQL%ROWCOUNT);
14676: log_message(p_msg_token1=>'Update_failure_in_acct_summary: Fail other records with F170');
14677: End If;
14678:
14679: Update pa_budget_acct_lines
14680: set funds_check_status_code = 'R',
14681: funds_check_result_code = 'F170'
14682: where budget_version_id = P_budget_version_id
14683: and nvl(funds_check_status_code,'P') <> 'R';
14857:
14858: -- --------------------------------------------------------------------------------+
14859: -- This procedure will execute the budget account validation, account level FC
14860: -- and update budget lines with the new derived accounts, call PROCEDURE
14861: -- Build_account_summary to build pa_budget_acct_lines
14862: -- Parameters:
14863: -- p_packet_id : packet being funds checked
14864: -- p_return_status : 'S' for success and 'F' for failure
14865: -- --------------------------------------------------------------------------------+
14990: -- are any combo having sum(available amount) < 0
14991:
14992: Cursor c_acct_lines(p_current_bvid IN NUMBER) is
14993: select pbl.gl_period_name, pbl.code_combination_id ,'N' allow_flag
14994: from pa_budget_acct_lines pbl
14995: where (pbl.budget_version_id,pbl.code_combination_id,pbl.start_date,pbl.end_date) in
14996: (select pbl.budget_version_id,pbl.code_combination_id,pbl.start_date,pbl.end_date
14997: from pa_budget_acct_lines pbl
14998: where (pbl.budget_version_id,pbl.code_combination_id,pbl.start_date,pbl.end_date) in
14993: select pbl.gl_period_name, pbl.code_combination_id ,'N' allow_flag
14994: from pa_budget_acct_lines pbl
14995: where (pbl.budget_version_id,pbl.code_combination_id,pbl.start_date,pbl.end_date) in
14996: (select pbl.budget_version_id,pbl.code_combination_id,pbl.start_date,pbl.end_date
14997: from pa_budget_acct_lines pbl
14998: where (pbl.budget_version_id,pbl.code_combination_id,pbl.start_date,pbl.end_date) in
14999: (select distinct a.budget_version_id,a.budget_ccid,a.fc_start_date,a.fc_end_date
15000: from pa_bc_packets a
15001: where a.budget_version_id = p_current_bvid)
15004: )
15005: UNION
15006: -- Bug 5206341 : Cursor to validate account level balances for Open and Closed periods
15007: select pbl.gl_period_name, pbl.code_combination_id ,'N' allow_flag
15008: from pa_budget_acct_lines pbl
15009: where (pbl.budget_version_id,pbl.code_combination_id) in
15010: (select pbl.budget_version_id,pbl.code_combination_id
15011: from pa_budget_acct_lines pbl
15012: where pbl.budget_version_id = p_current_bvid
15007: select pbl.gl_period_name, pbl.code_combination_id ,'N' allow_flag
15008: from pa_budget_acct_lines pbl
15009: where (pbl.budget_version_id,pbl.code_combination_id) in
15010: (select pbl.budget_version_id,pbl.code_combination_id
15011: from pa_budget_acct_lines pbl
15012: where pbl.budget_version_id = p_current_bvid
15013: group by pbl.budget_version_id,pbl.code_combination_id
15014: having sum(nvl(pbl.Curr_Ver_Available_Amount,0)) < 0
15015: )
15201: x_mode=>'PASS');
15202: If g_debug_mode = 'Y' then
15203: log_message(p_msg_token1=>l_program_name||'After BUILD_ACCOUNT_SUMMARY_AUTO call');
15204: End If;
15205: -- Autonomous used as we're updating pa_budget_acct_lines in AUTONOMOUS
15206: -- mode for failures later ..
15207: End If;
15208:
15209: -- 1.7: Initalize l_acct_changed
15465: If g_debug_mode = 'Y' then
15466: log_message(p_msg_token1=>l_program_name||': <> CF, Calling Update_failure_in_acct_summary');
15467: End If;
15468:
15469: -- Autonomous call to update pa_budget_acct_lines for the draft version ...
15470: UPDATE_FAILURE_IN_ACCT_SUMMARY(P_budget_version_id => l_draft_budget_version_id,
15471: P_period_name => g_tab_period_name,
15472: P_budget_ccid => t_draft_ccid,
15473: P_allow_flag => g_tab_allow_flag,
15477: If g_debug_mode = 'Y' then
15478: log_message(p_msg_token1=>l_program_name||': = CF, Calling Update_failure_in_acct_summary');
15479: End If;
15480:
15481: -- Autonomous call to update pa_budget_acct_lines for the draft version ...
15482: UPDATE_FAILURE_IN_ACCT_SUMMARY(P_budget_version_id => l_draft_budget_version_id,
15483: P_period_name => g_tab_period_name,
15484: P_budget_ccid => t_bud_ccid,
15485: P_allow_flag => g_tab_allow_flag,
15893: end loop;
15894: End If;
15895: -- -------------------------------------------------------------------------------------------------+
15896:
15897: -- Autonomous call to update pa_budget_acct_lines for the draft version ...
15898: UPDATE_FAILURE_IN_ACCT_SUMMARY(P_budget_version_id => l_draft_budget_version_id,
15899: P_period_name => g_tab_period_name,
15900: P_budget_ccid => t_draft_ccid,
15901: P_allow_flag => g_tab_allow_flag,
15915:
15916: End If;
15917: -- -------------------------------------------------------------------------------------------------+
15918:
15919: -- Autonomous call to update pa_budget_acct_lines for the draft version ...
15920: UPDATE_FAILURE_IN_ACCT_SUMMARY(P_budget_version_id => l_draft_budget_version_id,
15921: P_period_name => g_tab_period_name,
15922: P_budget_ccid => t_bud_ccid,
15923: P_allow_flag => g_tab_allow_flag,
16211: End If;
16212:
16213: -- C. Update Draft version (account summary table) to failure ..
16214:
16215: -- Autonomous call to update pa_budget_acct_lines for the draft version ...
16216:
16217: UPDATE_FAILURE_IN_ACCT_SUMMARY(P_budget_version_id => l_draft_budget_version_id,
16218: P_period_name => g_tab_period_name,
16219: P_budget_ccid => g_tab_budget_ccid,
16268:
16269: End If; -- II If p_return_status = 'S' then (step 5..7)
16270:
16271: -- ------------------------- STEP 8 -----------------------------------+
16272: -- Step 8.0: Update Pass status/result code on pa_budget_acct_lines
16273: -- Step 8.1: Update Pass status/result code on current version (CF/Baseline/Yearend)
16274: /* ==============================================================================================+
16275: -- STEP NOT REQUIRED AS acct. summary initialized/build with 'P101' and 'A' ...
16276:
16278: (pa_budget_fund_pkg.g_processing_mode <> 'CHECK_FUNDS' and p_return_status = 'S')
16279: ) then
16280:
16281: If g_debug_mode = 'Y' then
16282: log_message(p_msg_token1=>'End:'||l_program_name||'Update Pass status/result code on pa_budget_acct_lines - Current');
16283: End If;
16284:
16285: Update pa_budget_acct_lines
16286: set funds_check_status_code = 'A',
16281: If g_debug_mode = 'Y' then
16282: log_message(p_msg_token1=>'End:'||l_program_name||'Update Pass status/result code on pa_budget_acct_lines - Current');
16283: End If;
16284:
16285: Update pa_budget_acct_lines
16286: set funds_check_status_code = 'A',
16287: funds_check_result_code = 'P101'
16288: where budget_version_id = l_current_budget_version_id
16289: and nvl(funds_check_status_code,'A') <> 'R';
16299: If (pa_budget_fund_pkg.g_processing_mode <> 'CHECK_FUNDS' and p_return_status = 'S')
16300: then
16301:
16302: If g_debug_mode = 'Y' then
16303: log_message(p_msg_token1=>'End:'||l_program_name||'Update Pass status/result code on pa_budget_acct_lines - Draft');
16304: End If;
16305:
16306: Update pa_budget_acct_lines
16307: set funds_check_status_code = 'A',
16302: If g_debug_mode = 'Y' then
16303: log_message(p_msg_token1=>'End:'||l_program_name||'Update Pass status/result code on pa_budget_acct_lines - Draft');
16304: End If;
16305:
16306: Update pa_budget_acct_lines
16307: set funds_check_status_code = 'A',
16308: funds_check_result_code = 'P101'
16309: where budget_version_id = l_draft_budget_version_id;
16310:
16367:
16368: END DO_BUDGET_BASELINE_TIEBACK;
16369:
16370: -- --------------------------------------------------------------------------------+
16371: -- This procedure will build pa_budget_acct_lines
16372: -- Called from DO_BUDGET_BASELINE_TIEBACK and PA_GL_CBC_CONFIRMATION (for revenue)
16373: -- Parameters:
16374: -- p_budget_version_id : Current budget version being funds checked
16375: -- p_balance_type : E (For Top Down) and B (Bottom Up)
16427: l_login := fnd_global.login_id;
16428: l_request := fnd_global.conc_request_id;
16429: l_limit := 500;
16430: -- ------------------------------------------------------------------------+
16431: -- 1.0: Creating PA_Budget_Acct_Lines record for the current budget version
16432: -- ------------------------------------------------------------------------+
16433:
16434: -- To make sure that there is no budget acct. line .. acct. gen. seems to be creating summary ..
16435: Delete from PA_Budget_Acct_Lines
16431: -- 1.0: Creating PA_Budget_Acct_Lines record for the current budget version
16432: -- ------------------------------------------------------------------------+
16433:
16434: -- To make sure that there is no budget acct. line .. acct. gen. seems to be creating summary ..
16435: Delete from PA_Budget_Acct_Lines
16436: where budget_version_id = P_budget_version_id;
16437:
16438: If g_debug_mode = 'Y' then
16439: log_message(p_msg_token1=>l_program_name||'Deleted '||SQL%ROWCOUNT||' PA_Budget_Acct_Lines records');
16435: Delete from PA_Budget_Acct_Lines
16436: where budget_version_id = P_budget_version_id;
16437:
16438: If g_debug_mode = 'Y' then
16439: log_message(p_msg_token1=>l_program_name||'Deleted '||SQL%ROWCOUNT||' PA_Budget_Acct_Lines records');
16440: End If;
16441:
16442: If g_debug_mode = 'Y' then
16443: log_message(p_msg_token1=>l_program_name||':Creating records');
16467: end loop;
16468: End If;
16469:
16470: forall i in 1..l_count
16471: INSERT INTO PA_Budget_Acct_Lines (
16472: Budget_Acct_Line_ID,
16473: Budget_version_ID,
16474: GL_Period_Name,
16475: Start_Date,
16487: Last_Update_Login,
16488: Request_ID,
16489: funds_check_status_code,
16490: funds_check_result_code)
16491: VALUES(PA_Budget_Acct_Lines_S.NEXTVAL, -- Budget acct line id
16492: P_budget_version_id, -- Budget version id
16493: t_period_name(i), -- Period name
16494: t_start_date(i), -- Start date
16495: t_end_date(i), -- End date
16524: end loop;
16525: close c_acct_lines;
16526:
16527: If g_debug_mode = 'Y' then
16528: log_message(p_msg_token1=>l_program_name||' After PA_Budget_Acct_Lines Insert DML');
16529: End If;
16530: -- -------------------------------------------------------------------+
16531: -- 2.0: Update previous amounts (budget and available)
16532: -- -------------------------------------------------------------------+
16534: If g_debug_mode = 'Y' then
16535: log_message(p_msg_token1=>l_program_name||':Updating previous amounts');
16536: End If;
16537:
16538: Update PA_Budget_Acct_Lines pbl
16539: set (pbl.Prev_Ver_Budget_Amount,pbl.Prev_Ver_Available_Amount) =
16540: (select pbl1.Curr_Ver_Budget_Amount,
16541: pbl1.Curr_Ver_Available_Amount
16542: from PA_Budget_Acct_Lines pbl1
16538: Update PA_Budget_Acct_Lines pbl
16539: set (pbl.Prev_Ver_Budget_Amount,pbl.Prev_Ver_Available_Amount) =
16540: (select pbl1.Curr_Ver_Budget_Amount,
16541: pbl1.Curr_Ver_Available_Amount
16542: from PA_Budget_Acct_Lines pbl1
16543: where pbl1.budget_version_id = P_prev_budget_version_id
16544: and pbl1.code_combination_id = pbl.code_combination_id
16545: and pbl1.start_date = pbl.start_date)
16546: where pbl.budget_version_id = p_budget_version_id
16545: and pbl1.start_date = pbl.start_date)
16546: where pbl.budget_version_id = p_budget_version_id
16547: and exists
16548: (select 1
16549: from PA_Budget_Acct_Lines pbl2
16550: where pbl2.budget_version_id = P_prev_budget_version_id
16551: and pbl2.code_combination_id = pbl.code_combination_id
16552: and pbl2.start_date = pbl.start_date);
16553: -- Bottom "exists clause" reqd, else if there is no record then the
16565: -- That means there were txn. for 10 against prev. budget (100-90)
16566: -- Current available = 50 - (100-90) = 40
16567: -- Accounted = 100-50 = 50
16568:
16569: Update PA_Budget_Acct_Lines
16570: set Curr_Ver_Available_Amount = Curr_Ver_Budget_Amount -
16571: (Prev_Ver_Budget_Amount - Prev_Ver_Available_Amount),
16572: Accounted_Amount = Curr_Ver_Budget_Amount - Prev_Ver_Budget_Amount
16573: -- Accounted_Amount = Prev_Ver_Budget_Amount - Curr_Ver_Budget_Amount
16590: -- This IF..END IF ..is reqd. as this procedure is called for the draft version
16591: -- during <> 'CF' mode ..to rebuild the acct. sumamry for the draft version
16592: -- In this case, the else part should fire ..
16593:
16594: INSERT INTO PA_BUDGET_ACCT_LINES (
16595: Budget_Acct_Line_ID,
16596: Budget_Version_ID,
16597: GL_Period_Name,
16598: Start_Date,
16612: funds_check_status_code,
16613: funds_check_result_code
16614: )
16615: SELECT
16616: PA_BUDGET_ACCT_LINES_S.nextval,
16617: P_Budget_Version_ID, -- current version
16618: BL1.GL_Period_Name,
16619: BL1.Start_Date,
16620: BL1.End_Date,
16631: l_login,
16632: l_request,
16633: 'A',
16634: 'P101'
16635: FROM PA_BUDGET_ACCT_LINES BL1
16636: WHERE BL1.Budget_Version_ID = P_prev_budget_version_id
16637: AND NOT EXISTS
16638: ( SELECT 'x'
16639: FROM PA_BUDGET_ACCT_LINES BL2
16635: FROM PA_BUDGET_ACCT_LINES BL1
16636: WHERE BL1.Budget_Version_ID = P_prev_budget_version_id
16637: AND NOT EXISTS
16638: ( SELECT 'x'
16639: FROM PA_BUDGET_ACCT_LINES BL2
16640: WHERE BL2.Budget_Version_ID = P_budget_version_id
16641: AND BL2.Code_Combination_ID = BL1.Code_Combination_ID
16642: AND BL2.Start_Date = BL1.Start_Date ) ;
16643:
16650: -- And also, the missing lines are created with current amount = 0
16651:
16652: Else
16653:
16654: INSERT INTO PA_BUDGET_ACCT_LINES (
16655: Budget_Acct_Line_ID,
16656: Budget_Version_ID,
16657: GL_Period_Name,
16658: Start_Date,
16672: funds_check_status_code,
16673: funds_check_result_code
16674: )
16675: SELECT
16676: PA_BUDGET_ACCT_LINES_S.nextval,
16677: P_Budget_Version_ID, -- current version
16678: BL1.GL_Period_Name,
16679: BL1.Start_Date,
16680: BL1.End_Date,
16691: l_login,
16692: l_request,
16693: 'A',
16694: 'P101'
16695: FROM PA_BUDGET_ACCT_LINES BL1
16696: WHERE BL1.Budget_Version_ID = P_prev_budget_version_id
16697: AND (BL1.Curr_Ver_Budget_Amount <> 0 OR
16698: BL1.Prev_Ver_Budget_Amount <> 0)
16699: -- this is to filter the zero $ lines ...
16698: BL1.Prev_Ver_Budget_Amount <> 0)
16699: -- this is to filter the zero $ lines ...
16700: AND NOT EXISTS
16701: ( SELECT 'x'
16702: FROM PA_BUDGET_ACCT_LINES BL2
16703: WHERE BL2.Budget_Version_ID = P_budget_version_id
16704: AND BL2.Code_Combination_ID = BL1.Code_Combination_ID
16705: AND BL2.Start_Date = BL1.Start_Date ) ;
16706: End If;
16748: BULK COLLECT
16749: into tt_gl_period_name,
16750: tt_code_combination_id,
16751: tt_result_code
16752: from pa_budget_acct_lines
16753: where budget_version_id = X_budget_version_id
16754: and funds_check_result_code like 'F%';
16755: Exception
16756: When no_data_found then
16767:
16768: If X_mode = 'FAIL' then
16769: If tt_code_combination_id.exists(1) then
16770: forall x in tt_result_code.FIRST..tt_result_code.LAST
16771: update pa_budget_acct_lines
16772: set funds_check_result_code = tt_result_code(x)
16773: where budget_version_id = X_budget_version_id
16774: and code_combination_id = tt_code_combination_id(x)
16775: and gl_period_name = tt_gl_period_name(x);
16791: End Build_account_summary_auto;
16792:
16793: -- --------------------------------------------------------------------------------+
16794: -- Following procedure resets funds_check_status_code and result code on
16795: -- pa_budget_acct_lines for the draft version ..
16796: -- 8/22: Initalizing to pass status ..
16797: -- --------------------------------------------------------------------------------+
16798: PROCEDURE Reset_status_code_on_summary(p_budget_version_id IN Number)
16799: IS
16799: IS
16800: PRAGMA AUTONOMOUS_TRANSACTION;
16801: Begin
16802:
16803: Update pa_budget_acct_lines
16804: set funds_check_status_code = 'A', --null,
16805: funds_check_result_code = 'P101' --null
16806: where budget_version_id = p_budget_version_id;
16807: --and (funds_check_status_code is not null or funds_check_result_code is not null);