oracle

CREATE OR REPLACE PROCEDURE PROC_DAY_OVER_CALCULATE AS
  V_DAY_IDX                   DATE;
  V_COUNT                     NUMBER;
  V_ORDER_ID                  VARCHAR2(50);
  V_PAYMENT_ORDER_NO          VARCHAR2(50);
  V_ACCOUNT_NO                NUMBER(16);
  V_SUBJECT_DR_CR             VARCHAR2(4);
  V_CASH_AMOUNT               NUMBER;
  V_FREEZE_AMOUNT             NUMBER;
  V_OVERDRAFT_AMOUNT_LIMIT    NUMBER;
  V_CUR_DR_AMOUNT             NUMBER;
  V_CUR_CR_AMOUNT             NUMBER;
  V_DR_AMOUNT                 NUMBER;
  V_CR_AMOUNT                 NUMBER;
  V_YESTODAY_CASH_AMOUNT      NUMBER;
  V_YESTODAY_FREEZE_AMOUNT    NUMBER;
  V_YESTODAY_OVERDRAFT_AMOUNT NUMBER;
  V_YESTODAY_DR_AMOUNT        NUMBER;
  V_YESTODAY_CR_AMOUNT        NUMBER;
  V_TEMP_BALANCE              NUMBER;
  V_ACCRUAL                   NUMBER;
  V_SQL                       VARCHAR2(255);
  V_ERRTEXT                   VARCHAR2(255);
  V_ADJUST                    NUMBER;
  CURSOR CUR_ACC_DR_CR(V_DAY_IDX DATE) IS
    SELECT T.ORDER_ID,
           R.ACCOUNT_NO,
           R.PAYMENT_ORDER_NO,
           R.DR_TOTAL,
           R.CR_TOTAL
      FROM (SELECT T_ACCOUNT_WATER.PAYMENT_ORDER_NO,
                   T_ACCOUNT_WATER.ACCOUNT_NO,
                   SUM(CASE T_ACCOUNT_WATER.DR_CR_FLAG
                         WHEN 'DR' THEN
                          T_ACCOUNT_WATER.DR_CR_AMOUNT
                       END) DR_TOTAL,
                   SUM(CASE T_ACCOUNT_WATER.DR_CR_FLAG
                         WHEN 'CR' THEN
                          T_ACCOUNT_WATER.DR_CR_AMOUNT
                       END) CR_TOTAL

              FROM T_ACCOUNT_WATER
             WHERE T_ACCOUNT_WATER.STATUS = 'SUCCESS'
               AND T_ACCOUNT_WATER.ACCOUNT_DATE =
                   TO_CHAR(V_DAY_IDX, 'yyyyMMdd')

             GROUP BY T_ACCOUNT_WATER.PAYMENT_ORDER_NO,
                      T_ACCOUNT_WATER.ACCOUNT_NO
            HAVING SUM(CASE T_ACCOUNT_WATER.DR_CR_FLAG
              WHEN 'DR' THEN
               T_ACCOUNT_WATER.DR_CR_AMOUNT
            END) <> SUM(CASE T_ACCOUNT_WATER.DR_CR_FLAG
              WHEN 'CR' THEN
               T_ACCOUNT_WATER.DR_CR_AMOUNT
            END)) R
      LEFT JOIN MV_TRADE T
        ON R.PAYMENT_ORDER_NO = T.PAYMENT_ID;

  CURSOR CUR_ACC_BALANCE(V_DAY_IDX DATE) IS
    SELECT RET.ACCOUNT_NO,
           RET.SUBJECT_DR_CR,
           RET.CASH_AMOUNT,
           RET.FREEZE_AMOUNT,
           RET.OVERDRAFT_AMOUNT_LIMIT,
           RET.CUR_DR_AMOUNT,
           RET.CUR_CR_AMOUNT,
           RET.DR_AMOUNT,
           RET.CR_AMOUNT,
           Y_RET.YESTODAY_CASH_AMOUNT,
           Y_RET.YESTODAY_FREEZE_AMOUNT,
           Y_RET.YESTODAY_OVERDRAFT_AMOUNT,
           Y_RET.YESTODAY_DR_AMOUNT,
           Y_RET.YESTODAY_CR_AMOUNT
      FROM (SELECT T_ACCOUNT.ACCOUNT_NO,
                   T_ACCOUNT.SUBJECT_DR_CR,
                   T_ACCOUNT.CASH_AMOUNT,
                   T_ACCOUNT.FREEZE_AMOUNT,
                   T_ACCOUNT.OVERDRAFT_AMOUNT_LIMIT,
                   T_ACCOUNT.DR_AMOUNT              CUR_DR_AMOUNT,
                   T_ACCOUNT.CR_AMOUNT              CUR_CR_AMOUNT,
                   R.DR_AMOUNT,
                   R.CR_AMOUNT
              FROM T_ACCOUNT,
                   (SELECT SUM(CASE T_ACCOUNT_WATER.DR_CR_FLAG
                                 WHEN 'DR' THEN
                                  T_ACCOUNT_WATER.DR_CR_AMOUNT
                               END) DR_AMOUNT,
                           SUM(CASE T_ACCOUNT_WATER.DR_CR_FLAG
                                 WHEN 'CR' THEN
                                  T_ACCOUNT_WATER.DR_CR_AMOUNT
                               END) CR_AMOUNT,
                           T_ACCOUNT_WATER.ACCOUNT_NO
                      FROM T_ACCOUNT_WATER
                     WHERE T_ACCOUNT_WATER.ACCOUNT_DATE =
                           TO_CHAR(V_DAY_IDX, 'yyyyMMdd')

                       AND T_ACCOUNT_WATER.STATUS = 'SUCCESS'
                     GROUP BY T_ACCOUNT_WATER.ACCOUNT_NO) R
             WHERE T_ACCOUNT.ACCOUNT_NO = R.ACCOUNT_NO

            ) RET
      LEFT JOIN (SELECT T_ACCOUNT_DAILY.CASH_AMOUNT            YESTODAY_CASH_AMOUNT,
                        T_ACCOUNT_DAILY.FREEZE_AMOUNT          YESTODAY_FREEZE_AMOUNT,
                        T_ACCOUNT_DAILY.OVERDRAFT_AMOUNT_LIMIT YESTODAY_OVERDRAFT_AMOUNT,
                        T_ACCOUNT_DAILY.DR_AMOUNT              YESTODAY_DR_AMOUNT,
                        T_ACCOUNT_DAILY.CR_AMOUNT              YESTODAY_CR_AMOUNT,
                        T_ACCOUNT_DAILY.ACCOUNT_NO
                   FROM T_ACCOUNT_DAILY
                  WHERE T_ACCOUNT_DAILY.ACCOUNT_DATE =
                        TO_CHAR(V_DAY_IDX - 1, 'yyyyMMdd')) Y_RET
        ON RET.ACCOUNT_NO = Y_RET.ACCOUNT_NO;

BEGIN

  BEGIN
    --当前工作日
    SELECT TO_DATE(PARAM_VALUE, 'yyyyMMdd')
      INTO V_DAY_IDX
      FROM T_DAY_CHANGE
     WHERE T_DAY_CHANGE.PARAM_NAME = 'CUR_DAY';
    --同一支付流水记账借贷是否平衡
    OPEN CUR_ACC_DR_CR(V_DAY_IDX);
    --T.ORDER_ID, R.ACCOUNT_NO, R.PAYMENT_ORDER_NO, R.DR_TOTAL, R.CR_TOTAL
    FETCH CUR_ACC_DR_CR
      INTO V_ORDER_ID,
           V_ACCOUNT_NO,
           V_PAYMENT_ORDER_NO,
           V_DR_AMOUNT,
           V_CR_AMOUNT;

    WHILE CUR_ACC_DR_CR%FOUND LOOP
      ---订单是否需要冻结
      --  UPDATE T_RECHARGE TR SET TR.SETT_STATE ='FREEZEN' WHERE TR.ORDER_ID=V_ORDER_ID
      --将账户冻结
      UPDATE T_ACCOUNT
         SET T_ACCOUNT.ACCOUNT_STATE = 'FREEZE'
       WHERE T_ACCOUNT.ACCOUNT_NO = V_ACCOUNT_NO;
      --记录异常日志
      INSERT INTO T_ACCOUNT_LOG
        (ID, T_LOG_LEVEL, T_LOG_CONTENT, UPDATE_TIME)
      VALUES
        (SEQ_ACCOUNT_LOG.NEXTVAL,
         'ERROR',
         '账户(' || V_ACCOUNT_NO || ')支付流水号为：' || V_PAYMENT_ORDER_NO ||
         '借贷不平衡',
         CURRENT_TIMESTAMP);

      FETCH CUR_ACC_DR_CR
        INTO V_ORDER_ID,
             V_ACCOUNT_NO,
             V_PAYMENT_ORDER_NO,
             V_DR_AMOUNT,
             V_CR_AMOUNT;
    END LOOP;
    CLOSE CUR_ACC_DR_CR;
    ----- 账户当前余额=昨日余额+当日发生额
    OPEN CUR_ACC_BALANCE(V_DAY_IDX);

    FETCH CUR_ACC_BALANCE
      INTO V_ACCOUNT_NO,
           V_SUBJECT_DR_CR,
           V_CASH_AMOUNT,
           V_FREEZE_AMOUNT,
           V_OVERDRAFT_AMOUNT_LIMIT,
           V_CUR_DR_AMOUNT,
           V_CUR_CR_AMOUNT,
           V_DR_AMOUNT,
           V_CR_AMOUNT,
           V_YESTODAY_CASH_AMOUNT,
           V_YESTODAY_FREEZE_AMOUNT,
           V_YESTODAY_OVERDRAFT_AMOUNT,
           V_YESTODAY_DR_AMOUNT,
           V_YESTODAY_CR_AMOUNT;

    WHILE CUR_ACC_BALANCE%FOUND LOOP
      --数据错误则冻结账户
      IF V_YESTODAY_DR_AMOUNT > 0 AND V_YESTODAY_CR_AMOUNT = 0 THEN
        --余额在DR方
        V_TEMP_BALANCE := V_YESTODAY_DR_AMOUNT + V_DR_AMOUNT - V_CR_AMOUNT;
        V_ACCRUAL := V_DR_AMOUNT - V_CR_AMOUNT;

        IF V_TEMP_BALANCE > 0 THEN
          --余额在DR

          IF (V_CUR_CR_AMOUNT <> 0 OR V_CUR_DR_AMOUNT <> V_TEMP_BALANCE) THEN
            --调整账户余额 账户当前余额=昨日余额+当日发生额
            V_ADJUST := 1;
            V_ERRTEXT :=V_ACCOUNT_NO||'的DR余额应为：'||V_TEMP_BALANCE||',CR余额应为：0';
            CONTINUE;
          END IF;
        ELSIF V_TEMP_BALANCE < 0 THEN
          --余额在CR

          IF (V_CUR_DR_AMOUNT <> 0 OR
             V_CUR_CR_AMOUNT <> V_TEMP_BALANCE * -1) THEN
            --调整账户余额 账户当前余额=昨日余额+当日发生额
            V_ADJUST := 1;
           V_ERRTEXT :=V_ACCOUNT_NO||'的CR余额应为：'||V_TEMP_BALANCE * -1||',DR余额应为：0';
            CONTINUE;
          END IF;

        END IF;

      ELSIF V_YESTODAY_CR_AMOUNT > 0 AND V_YESTODAY_DR_AMOUNT = 0 THEN
        --昨日余额在CR方
        V_TEMP_BALANCE := V_YESTODAY_CR_AMOUNT + V_CR_AMOUNT - V_DR_AMOUNT;
         V_ACCRUAL := V_CR_AMOUNT -V_DR_AMOUNT;
        IF V_TEMP_BALANCE > 0 THEN
          --余额任在CR

          IF (V_CUR_DR_AMOUNT <> 0 OR V_CUR_CR_AMOUNT <> V_TEMP_BALANCE) THEN
            V_ADJUST := 1;
            V_ERRTEXT :=V_ACCOUNT_NO||'的CR余额应为：'||V_TEMP_BALANCE ||',DR余额应为：0';
             CONTINUE;
          END IF;
        ELSIF V_TEMP_BALANCE < 0 THEN
          --余额反向在DR
          IF (V_CUR_CR_AMOUNT <> 0 OR
             V_CUR_DR_AMOUNT <> V_TEMP_BALANCE * -1) THEN

            V_ADJUST := 1;
           V_ERRTEXT :=V_ACCOUNT_NO||'的DR余额应为：'||V_TEMP_BALANCE * -1 ||',CR余额应为：0';
            CONTINUE;
          END IF;

        END IF;

      ELSIF V_YESTODAY_CR_AMOUNT = 0 AND V_YESTODAY_DR_AMOUNT = 0 THEN
        --无昨日余额
        V_TEMP_BALANCE := V_CR_AMOUNT - V_DR_AMOUNT;
        V_ACCRUAL := V_CR_AMOUNT - V_DR_AMOUNT;
        IF V_TEMP_BALANCE > 0 THEN
          --余额任在CR

          IF (V_CUR_DR_AMOUNT <> 0 OR V_CUR_CR_AMOUNT <> V_TEMP_BALANCE) THEN
            V_ADJUST := 1;
            V_ERRTEXT :=V_ACCOUNT_NO||'的CR余额应为：'||V_TEMP_BALANCE  ||',DR余额应为：0';
            CONTINUE;
          END IF;
        ELSIF V_TEMP_BALANCE < 0 THEN
          --余额反向在DR
          IF (V_CUR_CR_AMOUNT <> 0 OR
             V_CUR_DR_AMOUNT <> V_TEMP_BALANCE * -1) THEN
            V_ADJUST := 1;
          V_ERRTEXT :=V_ACCOUNT_NO||'的DR余额应为：'||V_TEMP_BALANCE * -1  ||',CR余额应为：0';
           CONTINUE;
          END IF;

        END IF;

      END IF;
      IF (V_YESTODAY_CASH_AMOUNT +V_YESTODAY_FREEZE_AMOUNT+V_ACCRUAL <>V_CASH_AMOUNT + V_FREEZE_AMOUNT) THEN
          V_ADJUST := 1;
          V_ERRTEXT :=V_ACCOUNT_NO||'的余额错误,昨日余额为：'||(V_YESTODAY_CASH_AMOUNT +V_YESTODAY_FREEZE_AMOUNT) ||
          ',今日借方发生额：'||V_DR_AMOUNT||',贷方发生额'||V_CR_AMOUNT||',今日余额应为：'||(V_CASH_AMOUNT + V_FREEZE_AMOUNT);
          END IF;
      IF (V_ADJUST = 1) THEN
        UPDATE T_ACCOUNT
           SET T_ACCOUNT.ACCOUNT_STATE = 'FREEZE'
         WHERE T_ACCOUNT.ACCOUNT_NO = V_ACCOUNT_NO;
        --记录异常日志
        INSERT INTO T_ACCOUNT_LOG
          (ID, T_LOG_LEVEL, T_LOG_CONTENT, UPDATE_TIME)
        VALUES
          (SEQ_ACCOUNT_LOG.NEXTVAL,
           'ERROR',
           V_ERRTEXT,
           CURRENT_TIMESTAMP);
      END IF;
      FETCH CUR_ACC_BALANCE
        INTO V_ACCOUNT_NO,
             V_SUBJECT_DR_CR,
             V_CASH_AMOUNT,
             V_FREEZE_AMOUNT,
             V_OVERDRAFT_AMOUNT_LIMIT,
             V_CUR_DR_AMOUNT,
             V_CUR_CR_AMOUNT,
             V_DR_AMOUNT,
             V_CR_AMOUNT,
             V_YESTODAY_CASH_AMOUNT,
             V_YESTODAY_FREEZE_AMOUNT,
             V_YESTODAY_OVERDRAFT_AMOUNT,
             V_YESTODAY_DR_AMOUNT,
             V_YESTODAY_CR_AMOUNT;
    END LOOP;
    CLOSE CUR_ACC_BALANCE;

  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK WORK;
      RAISE_APPLICATION_ERROR(-20001, '日切出错出错!');
      RETURN;
  END;

END PROC_DAY_OVER_CALCULATE;




















CREATE OR REPLACE PROCEDURE PROC_ACCOUNT_DAILY AS

  V_DAY_IDX VARCHAR2(8);
  --V_COUNT   NUMBER;
  --V_SEQ_VAL NUMBER(15);

BEGIN

    /**、
    SELECT TO_DATE(PARAM_VALUE, 'yyyyMMdd')
    INTO V_DAY_IDX
    FROM T_DAY_CHANGE
   WHERE T_DAY_CHANGE.PARAM_NAME = 'CUR_DAY';
  SELECT COUNT(ID)
    INTO V_COUNT
    FROM T_ACCOUNT_DAILY
   WHERE T_ACCOUNT_DAILY.ACCOUNT_DATE = TO_CHAR(V_DAY_IDX, 'yyyyMMdd');
  IF V_COUNT > 0 THEN
    DELETE FROM T_ACCOUNT_DAILY
     WHERE T_ACCOUNT_DAILY.ACCOUNT_DATE = TO_CHAR(V_DAY_IDX, 'yyyyMMdd');
  END IF;
  */
  --更新日切时间
  --1、保留前日切时间
  --2、更新下一日切时间
   --记录异常日志
   INSERT INTO T_ACCOUNT_LOG (ID, T_LOG_LEVEL, T_LOG_CONTENT, UPDATE_TIME)
      VALUES (SEQ_ACCOUNT_LOG.NEXTVAL, 'INFO',
         '更新日切时间开始',
         CURRENT_TIMESTAMP);
 
  select t.param_value into V_DAY_IDX from t_day_change t where t.param_name='CUR_DAY';
  update t_day_change t
  set t.param_value = to_char(to_date(t.param_value, 'yyyyMMdd') + 1, 'yyyyMMdd'),
  t.update_time = sysdate;
 
  commit;
 
  INSERT INTO T_ACCOUNT_LOG (ID, T_LOG_LEVEL, T_LOG_CONTENT, UPDATE_TIME)
      VALUES (SEQ_ACCOUNT_LOG.NEXTVAL, 'INFO',
         '更新日切时间结束',
         CURRENT_TIMESTAMP);
 
 
  --账户日切表
  BEGIN
 
  INSERT INTO T_ACCOUNT_LOG (ID, T_LOG_LEVEL, T_LOG_CONTENT, UPDATE_TIME)
      VALUES (SEQ_ACCOUNT_LOG.NEXTVAL, 'INFO',
         '账户日切开始',
         CURRENT_TIMESTAMP);
 
    INSERT INTO T_ACCOUNT_DAILY
      (ID,
       ACCOUNT_DATE,
       ACCOUNT_TYPE,
       ACCOUNT_NO,
       ACCOUNT_NAME,
       SUBJECT_NO,
       SUBJECT_DR_CR,
       ACCOUNT_STATE,
       OVERDRAFT_FLAG,
       DR_AMOUNT,
       CR_AMOUNT,
       CASH_AMOUNT,
       FREEZE_AMOUNT,
       OVERDRAFT_AMOUNT_LIMIT,
       ALL_DR_AMOUNT,
       ALL_CR_AMOUNT,
       UPDATE_TIME)
      SELECT SEQ_ACCOUNT_DAILY.NEXTVAL,
             TO_CHAR(V_DAY_IDX, 'yyyyMMdd'),
             T_ACCOUNT.ACCOUNT_TYPE,
             T_ACCOUNT.ACCOUNT_NO,
             T_ACCOUNT.ACCOUNT_NAME,
             T_ACCOUNT.SUBJECT_NO,
             T_ACCOUNT.SUBJECT_DR_CR,
             T_ACCOUNT.ACCOUNT_STATE,
             T_ACCOUNT.OVERDRAFT_FLAG,
             T_ACCOUNT.DR_AMOUNT,
             T_ACCOUNT.CR_AMOUNT,
             T_ACCOUNT.CASH_AMOUNT,
             T_ACCOUNT.FREEZE_AMOUNT,
             T_ACCOUNT.OVERDRAFT_AMOUNT_LIMIT,
             NVL(A_STAT.DR_AMOUNT, 0),
             NVL(A_STAT.CR_AMOUNT, 0),
             CURRENT_TIMESTAMP
        FROM T_ACCOUNT
        LEFT JOIN (SELECT SUM(CASE T_ACCOUNT_WATER.DR_CR_FLAG
                                WHEN 'DR' THEN
                                 T_ACCOUNT_WATER.DR_CR_AMOUNT
                              END) DR_AMOUNT,
                          SUM(CASE T_ACCOUNT_WATER.DR_CR_FLAG
                                WHEN 'CR' THEN
                                 T_ACCOUNT_WATER.DR_CR_AMOUNT
                              END) CR_AMOUNT,
                          T_ACCOUNT_WATER.ACCOUNT_NO
                     FROM T_ACCOUNT_WATER
                    WHERE T_ACCOUNT_WATER.ACCOUNT_DATE =
                          TO_CHAR(V_DAY_IDX, 'yyyyMMdd')
                      AND T_ACCOUNT_WATER.STATUS = 'SUCCESS'
                    GROUP BY T_ACCOUNT_WATER.ACCOUNT_NO) A_STAT
          ON T_ACCOUNT.ACCOUNT_NO = A_STAT.ACCOUNT_NO;
    COMMIT;
   
    INSERT INTO T_ACCOUNT_LOG (ID, T_LOG_LEVEL, T_LOG_CONTENT, UPDATE_TIME)
      VALUES (SEQ_ACCOUNT_LOG.NEXTVAL, 'INFO',
         '账户日切结束',
         CURRENT_TIMESTAMP);
        
        
  EXCEPTION
    WHEN OTHERS THEN
      INSERT INTO T_ACCOUNT_LOG (ID, T_LOG_LEVEL, T_LOG_CONTENT, UPDATE_TIME)
      VALUES (SEQ_ACCOUNT_LOG.NEXTVAL,
       'ERROR',
         '账户日切异常',
         CURRENT_TIMESTAMP);
         commit;
  END;
  ----录入科目日均表
  BEGIN
  INSERT INTO T_ACCOUNT_LOG (ID, T_LOG_LEVEL, T_LOG_CONTENT, UPDATE_TIME)
      VALUES (SEQ_ACCOUNT_LOG.NEXTVAL,
       'INFO',
         '科目日报表统计开始',
         CURRENT_TIMESTAMP);
 
    INSERT INTO T_SUBJECT_DAY_BANLANCE_RPT
      (ID,
       SUBJECT_NO,
       SUBJECT_NAME,
       STA_DATE,
       DR_AMT,
       CR_AMT,
       TODAY_DR_BALANCE,
       TODAY_CR_BALANCE,
       PRE_DR_BALANCE,
       PRE_CR_BALANCE)
      SELECT SEQ_SUB_DAY_BALRPT.NEXTVAL,
             T_SUBJECT.SUBJECT_NO,
             T_SUBJECT.SUBJECT_NAME,
             V_DAY_IDX,
             NVL(RET.ALL_DR_AMOUNT, 0),
             NVL(RET.ALL_CR_AMOUNT, 0),
             NVL(RET.DR_AMOUNT, 0),
             NVL(RET.CR_AMOUNT, 0),
             NVL(RET.PRE_DR_AMOUNT, 0),
             NVL(RET.PRE_CR_AMOUNT, 0)
        FROM T_SUBJECT
        LEFT JOIN (SELECT CUR.*,
                          PRE.DR_AMOUNT PRE_DR_AMOUNT,
                          PRE.CR_AMOUNT PRE_CR_AMOUNT
                     FROM (SELECT T_ACCOUNT_DAILY.SUBJECT_NO,
                                  T_ACCOUNT_DAILY.ACCOUNT_DATE,
                                  SUM(T_ACCOUNT_DAILY.ALL_DR_AMOUNT) ALL_DR_AMOUNT,
                                  SUM(T_ACCOUNT_DAILY.ALL_CR_AMOUNT) ALL_CR_AMOUNT,
                                  SUM(T_ACCOUNT_DAILY.DR_AMOUNT) DR_AMOUNT,
                                  SUM(T_ACCOUNT_DAILY.CR_AMOUNT) CR_AMOUNT
                             FROM T_ACCOUNT_DAILY
                            WHERE T_ACCOUNT_DAILY.ACCOUNT_DATE =
                                  TO_CHAR(V_DAY_IDX, 'yyyyMMdd')
                            GROUP BY T_ACCOUNT_DAILY.ACCOUNT_DATE,
                                     T_ACCOUNT_DAILY.SUBJECT_NO) CUR
                     LEFT JOIN (SELECT T_ACCOUNT_DAILY.SUBJECT_NO,
                                      T_ACCOUNT_DAILY.ACCOUNT_DATE,
                                      SUM(T_ACCOUNT_DAILY.DR_AMOUNT) DR_AMOUNT,
                                      SUM(T_ACCOUNT_DAILY.CR_AMOUNT) CR_AMOUNT
                                 FROM T_ACCOUNT_DAILY
                                WHERE T_ACCOUNT_DAILY.ACCOUNT_DATE =
                                      TO_CHAR(V_DAY_IDX - 1, 'yyyyMMdd')
                                GROUP BY T_ACCOUNT_DAILY.ACCOUNT_DATE,
                                         T_ACCOUNT_DAILY.SUBJECT_NO) PRE
                       ON CUR.SUBJECT_NO = PRE.SUBJECT_NO
                      AND CUR.ACCOUNT_DATE = PRE.ACCOUNT_DATE) RET
          ON T_SUBJECT.SUBJECT_NO = RET.SUBJECT_NO;
         
          commit;
         
          INSERT INTO T_ACCOUNT_LOG (ID, T_LOG_LEVEL, T_LOG_CONTENT, UPDATE_TIME)
      VALUES (SEQ_ACCOUNT_LOG.NEXTVAL,
       'INFO',
         '科目日报表统计结束',
         CURRENT_TIMESTAMP);
        
  EXCEPTION
    WHEN OTHERS THEN
      INSERT INTO T_ACCOUNT_LOG (ID, T_LOG_LEVEL, T_LOG_CONTENT, UPDATE_TIME)
      VALUES (SEQ_ACCOUNT_LOG.NEXTVAL,
       'ERROR',
         '',
         CURRENT_TIMESTAMP);
         commit;
  END;

END PROC_ACCOUNT_DAILY;











CREATE OR REPLACE VIEW MV_TRADE AS
SELECT
  T .ORDER_ID,
  T .AMOUNT,
  T .SUBJECT,
  T .TRADE_CATEGORY,
  T .TRADE_TYPE,
  T .STATUS,
  T .BUYER_ID,
  T .BUYER_LOGIN_ID,
  T .BUYER_NAME,
  T .SELLER_ID,
  T .SELLER_LOGIN_ID,
  T .SELLER_NAME,
  T .PAYMENT_ID,
  T .CREATE_DATE,
  T .UPDATE_USER
FROM
  T_RECHARGE T
UNION
  SELECT
    T .ORDER_ID,
    T .AMOUNT,
    T .SUBJECT,
    T .TRADE_CATEGORY,
    T .TRADE_TYPE,
    T .STATUS,
    T .BUYER_ID,
    T .BUYER_LOGIN_ID,
    T .BUYER_NAME,
    T .SELLER_ID,
    T .SELLER_LOGIN_ID,
    T .SELLER_NAME,
    T .PAYMENT_ID,
    T .CREATE_DATE,
    T .UPDATE_USER
  FROM
    T_TRADE_ORDER T
  UNION
    SELECT
      T .ORDER_ID,
      T .AMOUNT,
      T .SUBJECT,
      T .TRADE_CATEGORY,
      T .TRADE_TYPE,
      T .STATUS,
      T .BUYER_ID,
      T .BUYER_LOGIN_ID,
      T .BUYER_NAME,
      T .SELLER_ID,
      T .SELLER_LOGIN_ID,
      T .SELLER_NAME,
      T .PAYMENT_ID,
      T .CREATE_DATE,
      T .UPDATE_USER
    FROM
      T_WITHDRAW T;