EBS-值集

换一个新环境开发Report后注册发布并发程序时，常常需要问前辈 值集名称，有时他们很忙，不能及时解答，如果继续等待则工作效率会下降。

故抽空写个查值集查询。

查出结果分析认为 独立值集还是比较有效的，可以显示 值集的值。

表值的值集就不是很理想。

如下 表值值集查询代码： 

SELECT FFVS.FLEX_VALUE_SET_NAME,  -- 值集名稱,
       FFVS.DESCRIPTION AS VALUE_SET_DESC, -- 值集描述
       FA.APPLICATION_NAME, -- 應用程序模塊名稱,如：Inventory、Payables
       FFVT.APPLICATION_TABLE_NAME,  -- 表名
       FFVT.VALUE_COLUMN_NAME,  -- 值列名稱
       FFVT.VALUE_COLUMN_TYPE,  -- 值列名類型,字符型、數值型、日期型
       FFVT.VALUE_COLUMN_SIZE,  -- 值列名長度
       FFVT.MEANING_COLUMN_NAME,-- 描述列名稱
       FFVT.MEANING_COLUMN_TYPE,-- 描述列名類型,字符型、數值型、日期型
       FFVT.MEANING_COLUMN_SIZE,-- 描述列名長度
       FFVT.ID_COLUMN_NAME,     -- ID列名稱
       FFVT.ID_COLUMN_TYPE,     -- ID列名類型,字符型、數值型、日期型
       FFVT.ID_COLUMN_SIZE,     -- ID列名長度
       FFVT.ADDITIONAL_WHERE_CLAUSE as "WHERE_ORDER",
       FFVT.ADDITIONAL_QUICKPICK_COLUMNS AS "ADDITION_COLUMN"
 FROM  FND_FLEX_VALUE_SETS FFVS,
       FND_FLEX_VALIDATION_TABLES  FFVT,  -- 值集表
       FND_APPLICATION_VL FA
WHERE FFVS.FLEX_VALUE_SET_ID = FFVT.FLEX_VALUE_SET_ID
 AND FFVT.TABLE_APPLICATION_ID = FA.APPLICATION_ID
 AND FFVS.FLEX_VALUE_SET_NAME LIKE 'AP%'
 
 
 SELECT * FROM  FND_FLEX_VALUE_SETS FFVS
  WHERE  FFVS.FLEX_VALUE_SET_NAME IN ('NJUSER','NJ_ACTIVITY_FLAG')
  /*
  VALIDATION_TYPE 字段值說明:
  N: None
  D: Dependent
  P: Pair
  U: Special 
  F: TABLE
  I: INDEPENDENT
  X: Translatable Independent
  Y: Translatable Dependent. 
  */
  

　　2、独立值集查询

-- 驗證集 (獨立驗證)
-- N: Application Developer>>Application>>Validation>>Values.  
SELECT FFVS.FLEX_VALUE_SET_NAME AS VALIDATE_SET_NAME, 
      FFVS.DESCRIPTION AS VALIDATE_SET_DESCRIPTION,
      FFVV.FLEX_VALUE_MEANING ,   
      FFVV.FLEX_VALUE,
      FFVV.DESCRIPTION ,
      FFVV.CREATION_DATE,
      FFVV.ENABLED_FLAG            
  FROM 
       FND_FLEX_VALUE_SETS FFVS,  --驗證集表頭
       FND_FLEX_VALUES_VL FFVV
 WHERE 1 = 1 
   AND FFVV.FLEX_VALUE_SET_ID = FFVS.FLEX_VALUE_SET_ID
  -- AND FFVS.FLEX_VALUE_SET_NAME = 'NJ_STONESETTING'
  -- AND NVL(FFVV.ENABLED_FLAG,'N') = 'Y'
   --AND SYSDATE >= NVL(FFVV.START_DATE_ACTIVE,SYSDATE)
   --AND SYSDATE <= NVL(FFVV.END_DATE_ACTIVE,SYSDATE) 
   AND  FFVS.FLEX_VALUE_SET_NAME LIKE '%KH_POSITION' -- '%_HR_POSI' -- 'NJ_HR_POST' -- 'NJ_HR_GRADE%'
  -- AND  FFVS.FLEX_VALUE_SET_NAME LIKE 'NJ_EMP_COMPANY'  -- 驗證集名稱,如: 分公司名稱清單
--   AND FFVV.FLEX_VALUE  LIKE 'N'
  --  AND FFVV.DESCRIPTION LIKE '員工'
 ORDER BY FFVS.FLEX_VALUE_SET_NAME, FFVV.FLEX_VALUE_MEANING 

　　

PS: 

EBS系统值设置：

N: Application Developer >>Application>>Validation >>Set