CREATE OR REPLACE PACKAGE BODY account AS
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; --分录ID
v_ACCTNAME VARCHAR2(200); --科目名称
v_ACCTDATE DATE; --日期
v_AMTDR NUMERIC(10,3) := 0.0; --借方金额
v_AMTCR NUMERIC(10,3) := 0.0; --贷方金额
v_BALANCE NUMERIC(10,3) := 0.0; --余额
v_BALANCE_2 NUMERIC(10,3) := 0.0;
BEGIN
EXECUTE IMMEDIATE 'drop table T_TEMP';
EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE T_TEMP
(
F_FACT_ACCT_ID INTEGER, --分录ID
F_ACCTNAME VARCHAR2(200), --科目名称
F_ACCTDATE DATE, --日期
F_AMTDR NUMERIC(10,3), --借方金额
F_AMTCR NUMERIC(10,3), --贷方金额
F_BALANCE NUMERIC(10,3) --余额
)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'')';
--计算小于起始日期之前的余额
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 ||'是空的');
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 ';
--定义游标
DECLARE
TYPE v_type IS REF CURSOR;
cv v_type;
BEGIN
OPEN cv FOR
v_temp;
FETCH cv INTO v_FACT_ACCT_ID,v_ACCTNAME,v_ACCTDATE,v_AMTDR,v_AMTCR,v_BALANCE_2;
LOOP
v_BALANCE := v_BALANCE + v_AMTDR - v_AMTCR ;--计算余额
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_AMTCR||' '||v_BALANCE);
FETCH cv INTO v_FACT_ACCT_ID,v_ACCTNAME,v_ACCTDATE,v_AMTDR,v_AMTCR,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 FOR
SELECT * FROM T_TEMP;
-- COMMIT;
END;
END account;
/
CREATE OR REPLACE PACKAGE account AS
TYPE mr IS REF CURSOR;
PROCEDURE ACCOUNTPAGE(P_ACCTDATE_FROM DATE,P_ACCTDATE_TO DATE,
P_ACCTNAME VARCHAR2,p_CURSOR out mr);
END account;
/
