查询科目余额-带FSG行集

SELECT GCC.SEGMENT3,
       (select ffv.DESCRIPTION
          from FND_FLEX_VALUES_VL ffv
         where ffv.FLEX_VALUE_SET_ID = '&科目值集ID'
           and ffv.FLEX_VALUE = gcc.segment3) acc_des,
       GB.BEGIN_BALANCE_DR - GB.BEGIN_BALANCE_CR + GB.PERIOD_NET_DR -
       GB.PERIOD_NET_CR QM,
       Cux_Get_Fsg_Item(gcc.segment3, '&行集名称') zz
  from gl_balances gb, gl_code_combinations gcc
 where gb.code_combination_id = gcc.code_combination_id
   and gcc.segment1 = '&segment1'
   and gb.period_name = '&period_name'
   and gcc.summary_flag = 'N'
   and exists (select 1
          from gl_ledgers gl
         where gl.ledger_id = gb.ledger_id
           and gb.currency_code = gl.currency_code)
   and (GB.BEGIN_BALANCE_DR - GB.BEGIN_BALANCE_CR +
       GB.PERIOD_NET_DR - GB.PERIOD_NET_CR) <> 0
 order by 1

 

FUNCTION CUX_GET_FSG_ITEM(ACC           IN VARCHAR2
                           ,AXIS_SET_NAME IN VARCHAR2) RETURN VARCHAR2 AS
    BS_ITEM VARCHAR2(260);
  BEGIN
    SELECT AXESV.DESCRIPTION
      INTO BS_ITEM
      FROM APPS.RG_REPORT_AXES_V      AXESV
          ,RG.RG_REPORT_AXIS_CONTENTS AXISC
          ,RG.RG_REPORT_CALCULATIONS  RC
          ,RG_REPORT_AXIS_SETS_V      RR
     WHERE AXESV.AXIS_SET_ID = AXISC.AXIS_SET_ID(+)
       AND AXISC.AXIS_SEQ(+) = AXESV.SEQUENCE
       AND RC.AXIS_SET_ID(+) = AXESV.AXIS_SET_ID
       AND RC.AXIS_SEQ(+) = AXESV.SEQUENCE
       AND RR.AXIS_SET_ID = AXESV.AXIS_SET_ID
       AND RR.NAME = AXIS_SET_NAME
       AND AXISC.SEGMENT3_LOW <= ACC
       AND AXISC.SEGMENT3_HIGH >= ACC;
    RETURN BS_ITEM;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN(0);
  END CUX_GET_FSG_ITEM;