Quick Notes:
This is a ready made package for the Fund checking, you can directly use this package, for testing make sure you feed the correct code combinations with correct separators of segments('.' or '-' etc.)
The Procedure returns the following outputs:
1. Available Fund
2. Encumbered Amount
3. Actual Amount
4. Committed Amount
5. Obligated Amount
The sample call is also shared in the specification region as a commented content.
Package Specification :
CREATE OR REPLACE PACKAGE xxcust_bud_util_pkg
AS
PROCEDURE is_fund_available(p_gl_account
IN VARCHAR2
,p_ledger_id IN NUMBER
,p_date_char IN VARCHAR2
,px_fund_available OUT NUMBER
,px_encumbrance_amount OUT NUMBER
,px_actual_amount OUT NUMBER
,px_commitment_amount OUT NUMBER
,px_obligation_amount OUT NUMBER
,px_result OUT VARCHAR2);
----Testing PLSQL Script----
/*DECLARE
px_fund_available
NUMBER:=0;
px_result
VARCHAR2(1000):=NULL;
begin
is_fund_available('XXX-XXXX-XXX-XX-XX',LedgerID,'JUL-21',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---
END;
Package Body:
CREATE OR REPLACE PACKAGE BODY xxcust_bud_util_pkg AS
PROCEDURE is_fund_available
(
p_gl_account IN VARCHAR2
,p_ledger_id IN NUMBER
,p_date_char IN VARCHAR2
,px_fund_available OUT NUMBER
,px_encumbrance_amount OUT NUMBER
,px_actual_amount OUT NUMBER
,px_commitment_amount OUT NUMBER
,px_obligation_amount 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_date DATE;
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) := 'AED';
l_req_encumbrance_id NUMBER DEFAULT 1000;
l_po_encumbrance_id NUMBER DEFAULT 1001;
l_oblig_revresal_amount NUMBER := 0;
l_oblig_revresal_enucm_id NUMBER := NULL;
lx_funds_available_temp NUMBER := 0;
CURSOR get_period_year(cp_date DATE) IS
SELECT period_year
FROM gl_periods
WHERE cp_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
dbms_output.put_line('******************************** Enter Budget Fund Check
****************************** ');
SELECT to_date('01-' || p_date_char,
'dd/mm/rrrr')
INTO l_date
FROM dual;
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
dbms_output.put_line('NO Data ?');
ln_fund_exclusion := 0;
WHEN OTHERS THEN
dbms_output.put_line('Others ?' || SQLERRM);
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 =
p_date_char;
EXCEPTION
WHEN OTHERS THEN
x_msg_status :=
x_msg_status || 'ERR:Period';
--RAISE expense_exception;
END;
dbms_output.put_line('l_period_name ' ||
l_period_name);
dbms_output.put_line('period_set_name ' ||
l_period_set_name);
dbms_output.put_line('period_type ' ||
l_period_type);
dbms_output.put_line('period_num ' ||
l_period_num);
dbms_output.put_line('l_quarter_num ' ||
l_quarter_num);
dbms_output.put_line('l_period_year ' ||
l_period_year);
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 := 'AED';
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 l_date BETWEEN start_date AND end_date;
dbms_output.put_line('Budget Version ID
' || l_budget_version_id);
EXCEPTION
WHEN OTHERS THEN
l_budget_version_id := 1000; --Approved Budget
END;
OPEN get_period_year(l_date);
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);
dbms_output.put_line('l_closing_status
:' || l_closing_status);
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 */
dbms_output.put_line('All Details for Call : gl_funds_available_pkg.calc_funds');
dbms_output.put_line('l_code_combination_id:' ||
l_code_combination_id);
dbms_output.put_line('p_ledger_id:' ||
p_ledger_id);
dbms_output.put_line('l_currency_code:' ||
l_currency_code);
dbms_output.put_line('l_period_type:' ||
l_period_type);
dbms_output.put_line('l_period_set_name:' ||
l_period_set_name);
dbms_output.put_line('l_period_name:' ||
l_period_name);
dbms_output.put_line('l_period_num:' ||
l_period_num);
dbms_output.put_line('l_quarter_num:' ||
l_quarter_num);
dbms_output.put_line('l_period_year:' ||
l_period_year);
dbms_output.put_line('l_closing_status:' ||
l_closing_status);
dbms_output.put_line('l_budget_version_id:' ||
l_budget_version_id);
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 =>
l_req_encumbrance_id
, --1000,--l_req_encumbrance_id ,
x_po_encumbrance_id =>
l_po_encumbrance_id
, --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);
px_encumbrance_amount :=
nvl(lx_encumbrance, 0);
px_actual_amount := nvl(lx_actual, 0);
px_commitment_amount := nvl(lx_req_encumbrance_amount, 0);
px_obligation_amount := nvl(lx_po_encumbrance_amount, 0);
dbms_output.put_line('******************************** Exit Budget Fund Check
****************************** ');
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;
END;
Regards,
Jayaraj
No comments:
Post a Comment