Account page program source code (PL/SQL)

Create or replace package body account

Procedure accountpage
(
P_acctdate_from in date,
P_acctdate_to in date,
P_acctname in varchar2,
P_cursor out Mr
)
As
V_sqlstring varchar2 (5000 );
V_temp varchar2 (200 );
V_fact_acct_id integer; -- entry ID
V_acctname varchar2 (200); -- account name
V_acctdate date; -- Date
V_amtdr numeric (10, 3): = 0.0; -- debit amount
V_amctl numeric (0.0): =; -- credit amount
V_balance numeric (10, 3): = 0.0; -- balance
V_balance_2 numeric (0.0): =;
Begin
Execute immediate 'drop table t_temp ';
Execute immediate 'Create Global temporary table t_temp
(
F_fact_acct_id integer, -- entry ID
F_acctname varchar2 (200), -- account name
F_acctdate date, -- Date
F_amtdr numeric (), -- debit amount
F_amctl numeric (), -- credit amount
F_balance numeric (10, 3) -- balance
) On commit Delete rows ';
-- Dbms_output.put_line (v_balance );
V_sqlstring: = 'select fa. fact_acct_id, Ce. Name, Fa. dateacct, Fa. amtacctdr, Fa. amtacctcr, 0 from fact_acct fa left join c_elementvalue Ce on
Fa. account_id = Ce. c_elementvalue_id and Ce. isactive = 'y' and fa. isactive = 'y ''';

If (p_acctdate_to is not null) then
If (p_acctdate_from is not null) then
V_sqlstring: = v_sqlstring | 'where fa. dateacct <= to_date (''' | to_char (p_acctdate_to, 'yyyy-mm-dd') | ''', '''yyyy-mm-dd '') and fa. dateacct> = to_date (''' | to_char (p_acctdate_from, 'yyyy-mm-dd') | ''', '''yyyy-mm-dd '')';
-- Calculate the balance earlier than the start date
Select sum (Fa. amtacctdr)-sum (Fa. amtacctcr) into v_balance from fact_acct fa where fa. dateacct <to_date (to_char (p_acctdate_from, 'yyyy-mm-dd'), 'yyyy-mm-dd') and Fa. isactive = 'y ';
If (v_balance is null) then
V_balance: = 0.0;
-- Dbms_output.put_line (v_balance | 'empty ');
End if;
Else
V_sqlstring: = v_sqlstring | 'where fa. dateacct <= to_date (''' | to_char (p_acctdate_to, 'yyyy-mm-dd') | ''', '''yyyy-mm-dd '')';

End if;
End if;

If (p_acctname is not null) then
V_sqlstring: = v_sqlstring | 'where Ce. Name = '| p_acctname;
End if;

Execute immediate 'insert into t_temp ('| v_sqlstring | ')';

V_temp: = 'select * From t_temp order by f_acctdate ';
-- Define a cursor
Declare
Type v_type is ref cursor;
CV v_type;
Begin
Open CV
V_temp;
Fetch CV into v_fact_acct_id, v_acctname, v_acctdate, v_amtdr, v_amctl, v_balance_2;

Loop
V_balance: = v_balance + v_amtdr-v_amctl; -- calculate the balance

If (v_balance <> 0) then
Execute immediate 'Update t_temp set f_balance = '| v_balance | 'where f_fact_acct_id =' | v_fact_acct_id;
End if;
Dbms_output.put_line (v_fact_acct_id | ''| v_acctname |'' | v_acctdate | ''| v_amtdr |'' | v_amctl | ''| v_balance );
Fetch CV into v_fact_acct_id, v_acctname, v_acctdate, v_amtdr, v_amctl, v_balance_2;
Exit when CV % notfound;
End loop;

-- Dbms_output.put_line (v_balance );
Close CV;
End;
-- Execute immediate 'create table t_temp_test parallel as select * From t_temp ';
Open p_cursor
Select * From t_temp;
-- Commit;
End;
End account;
/

Create or replace package account
Type Mr is ref cursor;
Procedure accountpage (p_acctdate_from date, p_acctdate_to date,
P_acctname varchar2, p_cursor out Mr );
End account;
/