Subject: Required to check fund is available for the specific account during oracle form validation or reporting level.
Solution: Following procedure will return the available
fund amount &
status (Y- Available, N-InSufficient Fund)
PLSQL Procedure:
create or replace PROCEDURE is_fund_available (p_gl_account IN VARCHAR2,
p_amount IN NUMBER,
p_date IN DATE,
p_module IN VARCHAR2,
px_fund_available OUT NUMBER,
px_result OUT VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
lx_budget NUMBER;
lx_encumbrance NUMBER;
lx_actual NUMBER;
lx_funds_available NUMBER;
lx_req_encumbrance_amount NUMBER;
lx_po_encumbrance_amount NUMBER;
lx_other_encumbrance_amount NUMBER;
l_conversion_rate NUMBER;
l_encumbrance_type_id NUMBER;
l_closing_status VARCHAR2 (2);
l_set_of_books_id NUMBER;
l_period_name VARCHAR2 (30);
l_period_set_name VARCHAR2 (50);
l_period_type VARCHAR2 (30);
l_period_num VARCHAR2 (30);
l_quarter_num VARCHAR2 (30);
l_period_year VARCHAR2 (30);
l_currency_code VARCHAR2 (10);
l_budget_version_id NUMBER;
l_code_combination_id NUMBER;
lx_funds_available_usd NUMBER;
ln_fund_exclusion NUMBER;
p_end_date DATE;
expense_exception EXCEPTION;
l_rei_curr_code VARCHAR2 (10);
ln_period_year gl_periods.period_year%TYPE;
lc_period_name gl_periods.period_year%TYPE;
x_msg_status VARCHAR2 (1000) := NULL;
x_msg_data VARCHAR2 (100);
p_ledger_id NUMBER := 2021;
p_rei_curr_code VARCHAR2 (10) := 'QAR';
l_req_encumbrance_id NUMBER;
l_po_encumbrance_id NUMBER;
l_oblig_revresal_amount NUMBER := 0;
l_oblig_revresal_enucm_id NUMBER := NULL;
lx_funds_available_temp NUMBER := 0;
CURSOR get_period_year
IS
SELECT period_year
FROM gl_periods
WHERE p_date BETWEEN start_date AND end_date;
CURSOR get_last_period
IS
SELECT gp.period_type,
gsob.period_set_name,
gp.period_name,
gp.period_num,
gp.quarter_num,
gp.period_year,
gp.start_date,
gp.end_date,
gp.year_start_date
FROM gl_sets_of_books gsob, gl_periods gp
WHERE gsob.period_set_name = gp.period_set_name
AND gp.period_year = ln_period_year
AND gsob.set_of_books_id = p_ledger_id
AND adjustment_period_flag != 'Y'
ORDER BY period_num;
lcu_get_last_period get_last_period%ROWTYPE;
BEGIN
BEGIN
SELECT code_combination_id
INTO l_code_combination_id
FROM gl_code_combinations_kfv x
WHERE x.concatenated_segments = TRIM (p_gl_account);
EXCEPTION
WHEN OTHERS
THEN
x_msg_status := x_msg_status || 'ERR:CCD';
END;
DBMS_OUTPUT.put_line (
'Code combination id is ' || l_code_combination_id);
DBMS_OUTPUT.put_line ('Set of books id is :' || p_ledger_id);
BEGIN
SELECT 1
INTO ln_fund_exclusion
FROM gl_budget_assignments
WHERE ledger_id = p_ledger_id --l_set_of_books_id
AND code_combination_id = l_code_combination_id--AND amount_type = 'YTD'
--AND funds_check_level_code = 'B'
;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
ln_fund_exclusion := 0;
WHEN OTHERS
THEN
ln_fund_exclusion := 0;
END;
DBMS_OUTPUT.put_line ('Fund Exclusion:' || ln_fund_exclusion);
IF NVL (ln_fund_exclusion, 0) = 1
THEN
BEGIN
SELECT period_name,
period_set_name,
period_type,
period_num,
quarter_num,
period_year
INTO l_period_name,
l_period_set_name,
l_period_type,
l_period_num,
l_quarter_num,
l_period_year
FROM gl_periods
WHERE period_name = TO_CHAR (p_date, 'Mon-rr');
EXCEPTION
WHEN OTHERS
THEN
x_msg_status := x_msg_status || 'ERR:Period';
--RAISE expense_exception;
END;
BEGIN
SELECT currency_code
INTO l_currency_code
FROM gl_sets_of_books
WHERE set_of_books_id = NVL (p_ledger_id, 1001);
EXCEPTION
WHEN OTHERS
THEN
l_currency_code := 'QAR';
END;
BEGIN
SELECT budget_version_id
INTO l_budget_version_id
FROM gl_budgets_with_dates_v
WHERE set_of_books_id = NVL (p_ledger_id, 1001)
AND status != 'R'
AND status = 'C'
-- Status 'C' denotes the Current Active Budget
AND p_date BETWEEN start_date AND end_date;
EXCEPTION
WHEN OTHERS
THEN
l_budget_version_id := 1000; --Approved Budget
END;
OPEN get_period_year;
FETCH get_period_year INTO ln_period_year;
CLOSE get_period_year;
BEGIN
SELECT closing_status
INTO l_closing_status
FROM gl_period_statuses
WHERE period_name = l_period_name
AND application_id = 101
AND set_of_books_id = NVL (p_ledger_id, 1001);
EXCEPTION
WHEN OTHERS
THEN
x_msg_status := x_msg_status || 'ERR:Closing status';
END;
-- DBMS_OUTPUT.put_line('Last Period Name ' ||lcu_get_last_period.period_name);
DBMS_OUTPUT.put_line ('Period Closing status ' || l_closing_status);
/* l_period_type := lcu_get_last_period.period_type;
l_period_set_name := lcu_get_last_period.period_set_name;
l_period_name := lcu_get_last_period.period_name;
l_period_num := lcu_get_last_period.period_num;
l_quarter_num := lcu_get_last_period.quarter_num;
l_period_year := lcu_get_last_period.period_year;*/
/* Budget Validation ends here */
gl_funds_available_pkg.calc_funds (
x_amount_type => 'YTDE',
x_code_combination_id => l_code_combination_id,
x_account_type => 'A',
x_template_id => NULL,
x_ledger_id => p_ledger_id,
x_currency_code => l_currency_code,
x_po_install_flag => 'Y',
x_accounted_period_type => l_period_type,
x_period_set_name => l_period_set_name,
x_period_name => l_period_name,
x_period_num => l_period_num,
x_quarter_num => l_quarter_num,
x_period_year => l_period_year,
x_closing_status => l_closing_status,
x_budget_version_id => l_budget_version_id,
x_encumbrance_type_id => NVL (-1, --l_encumbrance_type_id,
-1), ---1,
x_req_encumbrance_id => NULL, --1000,--l_req_encumbrance_id ,
x_po_encumbrance_id => NULL, --1001,--l_po_encumbrance_id,
x_budget => lx_budget,
x_encumbrance => lx_encumbrance,
x_actual => lx_actual,
x_funds_available => lx_funds_available_temp,
x_req_encumbrance_amount => lx_req_encumbrance_amount,
x_po_encumbrance_amount => lx_po_encumbrance_amount,
x_other_encumbrance_amount => lx_other_encumbrance_amount);
DBMS_OUTPUT.put_line (
'Amount Available (lx_funds_available_temp):'
|| lx_funds_available_temp);
------------------------
DBMS_OUTPUT.put_line ('Budget amount is ' || lx_budget);
DBMS_OUTPUT.put_line ('Encumbrance amount is ' || lx_encumbrance);
DBMS_OUTPUT.put_line ('Actual amount is ' || lx_actual);
DBMS_OUTPUT.put_line ('From Currency is ' || l_currency_code);
DBMS_OUTPUT.put_line ('To Currency is ' || l_rei_curr_code);
IF (NVL (lx_funds_available_temp, 0) < NVL (p_amount, 0))
THEN
x_msg_data := 'Insufficient Funds';
x_msg_status := 'N';
ELSE
x_msg_data := 'sufficient Funds';
x_msg_status := 'Y';
END IF;
END IF;
px_result := x_msg_status;
px_fund_available := NVL (lx_funds_available_temp, 0);
EXCEPTION
WHEN OTHERS
THEN
x_msg_data := 'Insufficient Funds';
DBMS_OUTPUT.put_line ('Error:' || SQLERRM);
x_msg_status := 'insufficient fund3';
px_result := x_msg_status;
END is_fund_available;
----Testing PLSQL Script----
DECLARE
px_fund_available NUMBER:=0;
px_result VARCHAR2(1000):=NULL;
begin
is_fund_available('100.21000.120402.0000000.000.0002.000',0,'01-JUL-19',NULL,px_fund_available,px_result);
insert into temp_tbl values(1,px_fund_available);
insert into temp_tbl values(2,px_result);
commit;
end;
---Output---
You can able check fund for the specific account from oracle application screen.
Navigation: General Ledger Super User > Inquiry > Fund
You can query that specific account code for a specific period and check the funds available.
