GL科目余额获取脚本

CREATE OR REPLACE PACKAGE BODY MEW_GET_GL_BALANCE_PKG IS

  FUNCTION MEW_GET_BEGIN_BALANCE_F(P_CONCATENATED_SEGMENTS IN VARCHAR2
                                  ,P_CURRENCY_CODE         IN VARCHAR2
                                  ,P_SET_OF_BOOKS_ID       IN NUMBER
                                  ,P_PERIOD_NAME           IN VARCHAR2
                                  ,P_CURRENCY_TYPE         IN VARCHAR2 --'B'为本币‘C'为原币)
                                  ,P_BEGIN_OR_END          IN VARCHAR2 --'B'为begin 'E'为END
                                   ) --
   RETURN NUMBER IS
    V_BEGIN_BALANCE NUMBER;
  BEGIN
    --期初
    IF P_BEGIN_OR_END = 'B'
    THEN
   
      IF P_CURRENCY_TYPE = 'B' --本币
      THEN
     
        SELECT SUM(GB.BEGIN_BALANCE_DR_BEQ - GB.BEGIN_BALANCE_CR_BEQ)
          INTO V_BEGIN_BALANCE
          FROM GL_BALANCES          GB
              ,GL_CODE_COMBINATIONS GCC
         WHERE GCC.CODE_COMBINATION_ID = GB.CODE_COMBINATION_ID
              --AND GB.CODE_COMBINATION_ID = P_CODE_COMBINATION_ID
           AND GCC.SEGMENT2 || '.' || GCC.SEGMENT4 || '.' || GCC.SEGMENT5 =
               P_CONCATENATED_SEGMENTS
           AND GCC.SEGMENT1 <> 'T'
           AND GCC.SEGMENT2 <> 'T'
           AND GCC.SEGMENT3 <> 'T'
           AND GCC.SEGMENT4 <> 'T'
           AND GCC.SEGMENT5 <> 'T'
           AND GCC.SEGMENT6 <> 'T'
           AND GCC.SEGMENT7 <> 'T'
              /*         AND GB.PERIOD_YEAR || '-' || LPAD(GB.PERIOD_NUM
              ,2
              ,'0') = P_PERIOD_NAME*/
           AND GB.PERIOD_NAME =
               TO_CHAR(TO_DATE(P_PERIOD_NAME
                              ,'yyyy-mm')
                      ,'Mon-YY'
                      ,'NLS_DATE_LANGUAGE = AMERICAN')
           AND GB.SET_OF_BOOKS_ID = P_SET_OF_BOOKS_ID
           AND GB.CURRENCY_CODE = P_CURRENCY_CODE;
     
      ELSE
        --原币
     
        SELECT SUM(GB.BEGIN_BALANCE_DR - GB.BEGIN_BALANCE_CR)
          INTO V_BEGIN_BALANCE
          FROM GL_BALANCES          GB
              ,GL_CODE_COMBINATIONS GCC
         WHERE GCC.CODE_COMBINATION_ID = GB.CODE_COMBINATION_ID
              --  AND GB.CODE_COMBINATION_ID = P_CODE_COMBINATION_ID
           AND GCC.SEGMENT2 || '.' || GCC.SEGMENT4 || '.' || GCC.SEGMENT5 =
               P_CONCATENATED_SEGMENTS
           AND GCC.SEGMENT1 <> 'T'
           AND GCC.SEGMENT2 <> 'T'
           AND GCC.SEGMENT3 <> 'T'
           AND GCC.SEGMENT4 <> 'T'
           AND GCC.SEGMENT5 <> 'T'
           AND GCC.SEGMENT6 <> 'T'
           AND GCC.SEGMENT7 <> 'T'
              /*           AND GB.PERIOD_YEAR || '-' || LPAD(GB.PERIOD_NUM
              ,2
              ,'0') = P_PERIOD_NAME*/
           AND GB.PERIOD_NAME =
               TO_CHAR(TO_DATE(P_PERIOD_NAME
                              ,'yyyy-mm')
                      ,'Mon-YY'
                      ,'NLS_DATE_LANGUAGE = AMERICAN')
           AND GB.SET_OF_BOOKS_ID = P_SET_OF_BOOKS_ID
           AND GB.CURRENCY_CODE = P_CURRENCY_CODE;
     
      END IF;
      --期末
    ELSE
   
      IF P_CURRENCY_TYPE = 'B' --本币
      THEN
     
        SELECT SUM(GB.BEGIN_BALANCE_DR_BEQ - GB.BEGIN_BALANCE_CR_BEQ +
                   GB.PERIOD_NET_DR_BEQ - GB.PERIOD_NET_CR_BEQ)
          INTO V_BEGIN_BALANCE
          FROM GL_BALANCES          GB
              ,GL_CODE_COMBINATIONS GCC
         WHERE GCC.CODE_COMBINATION_ID = GB.CODE_COMBINATION_ID
              --AND GB.CODE_COMBINATION_ID = P_CODE_COMBINATION_ID
           AND GCC.SEGMENT2 || '.' || GCC.SEGMENT4 || '.' || GCC.SEGMENT5 =
               P_CONCATENATED_SEGMENTS
           AND GCC.SEGMENT1 <> 'T'
           AND GCC.SEGMENT2 <> 'T'
           AND GCC.SEGMENT3 <> 'T'
           AND GCC.SEGMENT4 <> 'T'
           AND GCC.SEGMENT5 <> 'T'
           AND GCC.SEGMENT6 <> 'T'
           AND GCC.SEGMENT7 <> 'T'
              /* AND GB.PERIOD_YEAR || '-' || LPAD(GB.PERIOD_NUM
              ,2
              ,'0') = P_PERIOD_NAME*/
           AND GB.PERIOD_NAME =
               TO_CHAR(TO_DATE(P_PERIOD_NAME
                              ,'yyyy-mm')
                      ,'Mon-YY'
                      ,'NLS_DATE_LANGUAGE = AMERICAN')
           AND GB.SET_OF_BOOKS_ID = P_SET_OF_BOOKS_ID
           AND GB.CURRENCY_CODE = P_CURRENCY_CODE;
     
      ELSE
        --原币
     
        SELECT SUM(GB.BEGIN_BALANCE_DR - GB.BEGIN_BALANCE_CR +
                   GB.PERIOD_NET_DR - GB.PERIOD_NET_CR)
          INTO V_BEGIN_BALANCE
          FROM GL_BALANCES          GB
              ,GL_CODE_COMBINATIONS GCC
         WHERE GCC.CODE_COMBINATION_ID = GB.CODE_COMBINATION_ID
              --AND GB.CODE_COMBINATION_ID = P_CODE_COMBINATION_ID
           AND GCC.SEGMENT2 || '.' || GCC.SEGMENT4 || '.' || GCC.SEGMENT5 =
               P_CONCATENATED_SEGMENTS
           AND GCC.SEGMENT1 <> 'T'
           AND GCC.SEGMENT2 <> 'T'
           AND GCC.SEGMENT3 <> 'T'
           AND GCC.SEGMENT4 <> 'T'
           AND GCC.SEGMENT5 <> 'T'
           AND GCC.SEGMENT6 <> 'T'
           AND GCC.SEGMENT7 <> 'T'
              /* AND GB.PERIOD_YEAR || '-' || LPAD(GB.PERIOD_NUM
              ,2
              ,'0') = P_PERIOD_NAME*/
           AND GB.PERIOD_NAME =
               TO_CHAR(TO_DATE(P_PERIOD_NAME
                              ,'yyyy-mm')
                      ,'Mon-YY'
                      ,'NLS_DATE_LANGUAGE = AMERICAN')
           AND GB.SET_OF_BOOKS_ID = P_SET_OF_BOOKS_ID
           AND GB.CURRENCY_CODE = P_CURRENCY_CODE;
     
      END IF;
    END IF;
 
    RETURN V_BEGIN_BALANCE;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN NULL;
  END;
END;