Oracle EBS 科目余额表取数SQL

最新推荐文章于 2021-08-09 17:44:46 发布

BlackHeart1203

最新推荐文章于 2021-08-09 17:44:46 发布

阅读量6.7k

点赞数 1

文章标签： oracle ebs

本文链接：https://blog.csdn.net/u010304907/article/details/77993840

版权

本文提供两个SQL查询案例，用于从GL_BALANCES表中提取特定期间的财务数据，并结合GL_CODE_COMBINATIONS表进行科目组合信息的展示。第一个查询计算期间的借方、贷方及期初余额等；第二个查询则详细列出科目组合各段描述及其余额。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SQL:1

SELECT SUM(decode(bal.period_name,
                  'Ad3-2016',
                  nvl(begin_balance_dr, 0) - nvl(begin_balance_cr, 0),
                  0)) begin_balance,
       SUM(decode(bal.period_name,
                  'Ad3-2016',
                  nvl(begin_balance_dr, 0) + nvl(period_net_dr, 0),
                  0) - decode(bal.period_name,
                              'Ad3-2016',
                              nvl(begin_balance_dr, 0),
                              0)) period_dr,
       SUM(decode(bal.period_name,
                  'Ad3-2016',
                  nvl(begin_balance_cr, 0) + nvl(period_net_cr, 0),
                  0) - decode(bal.period_name,
                              'Ad3-2016',
                              nvl(begin_balance_cr, 0),
                              0)) period_cr
  FROM gl_balances bal, gl_code_combinations cc
 WHERE bal.actual_flag = 'A'
   AND bal.currency_code = 'CNY'
   AND bal.period_name = 'Ad3-2016'
   AND bal.code_combination_id = cc.code_combination_id
   AND cc.chart_of_accounts_id = 50449
   AND cc.template_id IS NULL
   AND cc.summary_flag = 'N'
   --AND bal.ledger_id = 2021

SQL:2

SELECT GP.PERIOD_NAME
      ,GP.PERIOD_YEAR
      ,GP.PERIOD_NUM
      ,GB.CODE_COMBINATION_ID
      ,GB.CURRENCY_CODE
      ,GC.SEGMENT1
      ,MEW_GET_ACCOUNT_DESC_FNC(GC.CHART_OF_ACCOUNTS_ID
                               ,GC.CODE_COMBINATION_ID
                               ,GC.SEGMENT1
                               ,1) SEGMENT1_DESC

      ,GC.SEGMENT2
      ,MEW_GET_ACCOUNT_DESC_FNC(GC.CHART_OF_ACCOUNTS_ID
                               ,GC.CODE_COMBINATION_ID
                               ,GC.SEGMENT2
                               ,2) SEGMENT2_DESC

      ,GC.SEGMENT3
      ,MEW_GET_ACCOUNT_DESC_FNC(GC.CHART_OF_ACCOUNTS_ID
                               ,GC.CODE_COMBINATION_ID
                               ,GC.SEGMENT3
                               ,3) SEGMENT3_DESC
      ,GC.SEGMENT4
      ,MEW_GET_ACCOUNT_DESC_FNC(GC.CHART_OF_ACCOUNTS_ID
                               ,GC.CODE_COMBINATION_ID
                               ,GC.SEGMENT4
                               ,4) SEGMENT4_DESC
      ,GC.SEGMENT5
      ,MEW_GET_ACCOUNT_DESC_FNC(GC.CHART_OF_ACCOUNTS_ID
                               ,GC.CODE_COMBINATION_ID
                               ,GC.SEGMENT5
                               ,5) SEGMENT5_DESC
      ,GC.SEGMENT6
      ,MEW_GET_ACCOUNT_DESC_FNC(GC.CHART_OF_ACCOUNTS_ID
                               ,GC.CODE_COMBINATION_ID
                               ,GC.SEGMENT6
                               ,6) SEGMENT6_DESC
      ,GC.SEGMENT7
      ,MEW_GET_ACCOUNT_DESC_FNC(GC.CHART_OF_ACCOUNTS_ID
                               ,GC.CODE_COMBINATION_ID
                               ,GC.SEGMENT7
                               ,7) SEGMENT7_DESC
      ,GB.BEGIN_BALANCE_DR - GB.BEGIN_BALANCE_CR BEGIN_BALANCE --期初余额 原币
      ,DECODE(GB.CURRENCY_CODE
             ,'CNY'
             ,GB.BEGIN_BALANCE_DR - GB.BEGIN_BALANCE_CR
             ,GB.BEGIN_BALANCE_DR_BEQ - GB.BEGIN_BALANCE_CR_BEQ) BEGIN_BALANCE_BEQ --期初余额 本币 
      ,GB.PERIOD_NET_DR --本期借方发生额 原币
      ,GB.PERIOD_NET_DR_BEQ --本期借方发生额 本币       
      ,GB.PERIOD_NET_CR --本期贷方发生额 原币
      ,GB.PERIOD_NET_CR_BEQ --本期贷方发生额 本币
      ,(GB.BEGIN_BALANCE_DR - GB.BEGIN_BALANCE_CR + GB.PERIOD_NET_DR -
       GB.PERIOD_NET_CR) YTD_BALANCE --期末余额 原币

      ,(GB.BEGIN_BALANCE_DR_BEQ - GB.BEGIN_BALANCE_CR_BEQ +
       GB.PERIOD_NET_DR_BEQ - GB.PERIOD_NET_CR_BEQ) YTD_BALANCE_BEQ --期末余额 本币
  FROM GL_BALANCES              GB
      ,GL_CODE_COMBINATIONS_KFV GC
      ,GL_PERIOD_STATUSES_V     GP
WHERE GB.ACTUAL_FLAG = 'A'
   AND GB.CODE_COMBINATION_ID = GC.CODE_COMBINATION_ID
   AND GB.PERIOD_NAME = GP.PERIOD_NAME
   AND GB.SET_OF_BOOKS_ID = GP.SET_OF_BOOKS_ID
   AND GP.APPLICATION_ID = 101
   AND GP.SET_OF_BOOKS_ID = 143
   AND GP.PERIOD_YEAR || '-' || LPAD(GP.PERIOD_NUM
                                    ,2
                                    ,'0') >= '2012-01'
   AND GP.PERIOD_YEAR || '-' || LPAD(GP.PERIOD_NUM
                                    ,2
                                    ,'0') <= '2012-03'

函数：MEW_GET_ACCOUNT_DESC_FNC

CREATE OR REPLACE FUNCTION MEW_GET_ACCOUNT_DESC_FNC(P_CHART_OF_ACCOUNTS_ID IN NUMBER --科目结构ID
                                                   ,P_CODE_COMBINATION_ID  IN VARCHAR2 --科目ID
                                                   ,P_SEGMENT              IN VARCHAR2 --子值集段值
                                                   ,P_SEGMENT_NUM          IN NUMBER) --子段值序号


 RETURN VARCHAR2 IS
  V_DESC                  VARCHAR2(240);
  V_VALUESET_ID           NUMBER;
  V_PARENT_VALUESET_ID    NUMBER;
  V_DEPEND_SEGMENT_NUMBER NUMBER;
  V_PARENT_SEGMENT_VALUE  VARCHAR2(30);
BEGIN


  --获取当前段值对应的值集ID
  SELECT FIV.FLEX_VALUE_SET_ID
    INTO V_VALUESET_ID
    FROM APPS.FND_ID_FLEX_SEGMENTS_VL FIV
   WHERE (ID_FLEX_NUM = P_CHART_OF_ACCOUNTS_ID)
     AND (ID_FLEX_CODE = 'GL#')
     AND (APPLICATION_ID = 101)
     AND FIV.SEGMENT_NUM = P_SEGMENT_NUM;


  --判断当前值集是否存在依赖关系
  BEGIN
    SELECT FFVS.PARENT_FLEX_VALUE_SET_ID
      INTO V_PARENT_VALUESET_ID
      FROM FND_FLEX_VALUE_SETS FFVS
     WHERE FFVS.FLEX_VALUE_SET_ID = V_VALUESET_ID
       AND FFVS.PARENT_FLEX_VALUE_SET_ID IS NOT NULL;

    --存在依赖关系 判断依赖哪一段的值集
    SELECT FIV.SEGMENT_NUM
      INTO V_DEPEND_SEGMENT_NUMBER
      FROM APPS.FND_ID_FLEX_SEGMENTS_VL FIV
     WHERE (ID_FLEX_NUM = P_CHART_OF_ACCOUNTS_ID)
       AND (ID_FLEX_CODE = 'GL#')
       AND (APPLICATION_ID = 101)
       AND FIV.FLEX_VALUE_SET_ID = V_PARENT_VALUESET_ID;

    --获取依赖值集段值
    SELECT DECODE(V_DEPEND_SEGMENT_NUMBER
                 ,1
                 ,SEGMENT1
                 ,2
                 ,SEGMENT2
                 ,3
                 ,SEGMENT3
                 ,4
                 ,SEGMENT4
                 ,5
                 ,SEGMENT5
                 ,6
                 ,SEGMENT6)
      INTO V_PARENT_SEGMENT_VALUE
      FROM GL_CODE_COMBINATIONS GCC
     WHERE GCC.CODE_COMBINATION_ID = P_CODE_COMBINATION_ID;
    --获取当前段值描述
    SELECT DESCRIPTION
      INTO V_DESC
      FROM APPS.FND_FLEX_VALUES_VL FFV
     WHERE FFV.FLEX_VALUE_SET_ID IN
           (SELECT FIV.FLEX_VALUE_SET_ID
              FROM APPS.FND_ID_FLEX_SEGMENTS_VL FIV
             WHERE (ID_FLEX_NUM = P_CHART_OF_ACCOUNTS_ID)
               AND (ID_FLEX_CODE = 'GL#')
               AND (APPLICATION_ID = 101)
               AND FIV.SEGMENT_NUM = P_SEGMENT_NUM) --子值集段序号
       AND FFV.FLEX_VALUE = P_SEGMENT --子值集段值
       AND FFV.PARENT_FLEX_VALUE_LOW = V_PARENT_SEGMENT_VALUE --值集依赖（父值集段值）
       AND ROWNUM = 1;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      --不存在依赖关系
      BEGIN
        SELECT DESCRIPTION
          INTO V_DESC
          FROM APPS.FND_FLEX_VALUES_VL FFV
         WHERE FFV.FLEX_VALUE_SET_ID IN
               (SELECT FIV.FLEX_VALUE_SET_ID
                  FROM APPS.FND_ID_FLEX_SEGMENTS_VL FIV
                 WHERE (ID_FLEX_NUM = P_CHART_OF_ACCOUNTS_ID)
                   AND (ID_FLEX_CODE = 'GL#')
                   AND (APPLICATION_ID = 101)
                   AND FIV.SEGMENT_NUM = P_SEGMENT_NUM) --子值集段序号
           AND FFV.FLEX_VALUE = P_SEGMENT --子值集段值
           AND ROWNUM = 1;
      EXCEPTION
        WHEN OTHERS THEN
          V_DESC := P_SEGMENT;
      END;
  END;


  RETURN V_DESC;


EXCEPTION
  /*  WHEN TOO_MANY_ROWS THEN
    RETURN 'TOO_MANY_ROWS';
  WHEN NO_DATA_FOUND THEN
    RETURN 'NO_DATA_FOUND';*/
  WHEN OTHERS THEN
    RETURN NULL;
END;
/