EBS:会计科目-值集

-- FND_科目.sql
SELECT ffvs.flex_value_set_name as "值集-名称",
       ffvs.description as "值集-说明",
       DECODE(FFVS.VALIDATION_TYPE,'F','表','I','独立',FFVS.VALIDATION_TYPE) AS "验证类型",
       FFV.FLEX_VALUE AS "值",
       FFV.FLEX_VALUE_MEANING AS"转换的值",
       FFV.DESCRIPTION AS "说明",
       (SELECT FFH.HIERARCHY_CODE FROM  APPS.FND_FLEX_HIERARCHIES_VL FFH
        WHERE  FFH.HIERARCHY_ID = FFV.STRUCTURED_HIERARCHY_LEVEL
         AND ROWNUM=1) AS "组", 
       FFV.COMPILED_VALUE_ATTRIBUTES AS "限定词",
       SUBSTR( FFV.COMPILED_VALUE_ATTRIBUTES, 1, INSTR(FFV.COMPILED_VALUE_ATTRIBUTES, CHR(10),1)-1) AS "允许预算ID",
       SUBSTR( FFV.COMPILED_VALUE_ATTRIBUTES, INSTR(FFV.COMPILED_VALUE_ATTRIBUTES, CHR(10),1)+1,1) AS "允许过帐ID",
     --  SUBSTR( FFV.COMPILED_VALUE_ATTRIBUTES, INSTR(FFV.COMPILED_VALUE_ATTRIBUTES, CHR(10),3)+1,1) AS "帐户类型ID",
     --  SUBSTR( FFV.COMPILED_VALUE_ATTRIBUTES, INSTR(FFV.COMPILED_VALUE_ATTRIBUTES, CHR(10),5)+1,1) AS "第三方控制帐户ID",
     --  SUBSTR( FFV.COMPILED_VALUE_ATTRIBUTES, INSTR(FFV.COMPILED_VALUE_ATTRIBUTES, CHR(10),7)+1,1) AS "调节ID",
       DECODE(SUBSTR( FFV.COMPILED_VALUE_ATTRIBUTES, 1, INSTR(FFV.COMPILED_VALUE_ATTRIBUTES, CHR(10),1)-1),'Y','是','N','否')  AS "允许预算",
       DECODE(SUBSTR( FFV.COMPILED_VALUE_ATTRIBUTES, INSTR(FFV.COMPILED_VALUE_ATTRIBUTES, CHR(10),1)+1,1),'Y','是','N','否')  AS "允许过帐",
     --  DECODE(SUBSTR( FFV.COMPILED_VALUE_ATTRIBUTES, INSTR(FFV.COMPILED_VALUE_ATTRIBUTES, CHR(10),3)+1,1),'A','资产','L','负债',
    --      'O','所有权/股东的权益','E','费用','R','收入')  AS "帐户类型",
    --   DECODE(SUBSTR( FFV.COMPILED_VALUE_ATTRIBUTES, INSTR(FFV.COMPILED_VALUE_ATTRIBUTES, CHR(10),5)+1,1),'Y','是','N','否')  AS "第三方控制帐户",
    --   DECODE(SUBSTR( FFV.COMPILED_VALUE_ATTRIBUTES, INSTR(FFV.COMPILED_VALUE_ATTRIBUTES, CHR(10),7)+1,1),'Y','是','N','否')  AS "调节",
       ffv.ENABLED_FLAG AS "启用",
       FFV.SUMMARY_FLAG AS "父" ,
       FFV.VALUE_CATEGORY AS "上下文",  -- 弹性域段值（CUX_COA_ACC） 
       FFV.ATTRIBUTE1 AS "余额方向",
       FFV.ATTRIBUTE2 AS "费用类别",
       FFV.ATTRIBUTE3 AS "帐户类别"
      -- ,FFV.* 
 FROM APPS.FND_FLEX_VALUE_SETS  FFVS, --值集
      APPS.FND_FLEX_VALUES_VL FFV   --科目明细
WHERE ffvs.FLEX_VALUE_SET_ID = ffv.FLEX_VALUE_SET_ID
 -- and FFV.FLEX_VALUE  LIKE '611103%'
  AND FFVS.FLEX_VALUE_SET_NAME LIKE 'CUX_COA%'
  AND FFVS.FLEX_VALUE_SET_NAME NOT IN ( 'CUX_COA_ACC')  
ORDER BY   ffvs.flex_value_set_name, FFV.FLEX_VALUE   
 -- 1：A:资产
 --2: L:负债
--4：O: 所有权/股东的权益
-- 51: E: 费用  
--6: R:收入



/*
SELECT * FROM APPS.FND_FLEX_VALUE_SETS  FFVS
 WHERE FFVS.FLEX_VALUE_SET_ID = 1014967
 */