EBS 值集

最新推荐文章于 2022-09-29 08:37:13 发布

yinjiaxuan

最新推荐文章于 2022-09-29 08:37:13 发布

阅读量1.8k

点赞数 1

分类专栏： form oracle ebs 文章标签： report-值集

本文链接：https://blog.csdn.net/yyyxx6/article/details/79148064

版权

form oracle ebs 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

常用系统内置值集(验证)

公司
FND_MO_OU
AR_SRS_OPERATING_UNITS

帐套
FND_MO_LEDGERS

客户名称 Customer Name
AR_BR_CUSTOMER_NAME

供应商
所有的供应商
AP_SRS_VENDOR_NAME_NUM
根据fnd_mo_ou 地点层获取供应商
AP_SRS_VENDOR_NAME_OU

是否
GL_SRS_YES_NO_MAND
AMS_SRS_YES_NO_MAND

库存组织
INV_SRS_ORG_ACCESS

日期时间
FND_STANDARD_DATETIME

值集查找

SELECT flex_value_set_name          值集名称
      ,description                  值集描述
      ,maximum_size                 值集大小
      ,application_table_name       值集表名
      ,value_column_name            值
      ,value_column_type            值类型 --字符为C 数字为N
      ,value_column_size            值大小
      ,meaning_column_name          含义
      ,meaning_column_type          含义类型
      ,meaning_column_size          含义大小
      ,id_column_name               标识
      ,id_column_type               标识类型
      ,additional_where_clause      where条件
      ,additional_quickpick_columns 附加列
  FROM fnd_flex_validation_tables t
      ,fnd_flex_value_sets
 WHERE 1 = 1
      --AND t.additional_where_clause LIKE '%FND_MO_OU%' -- where条件里面有ou限制的
   AND upper(application_table_name) LIKE '%VENDORS%' -- 供应商的值集
      --and upper(value_column_name) like '%VENDOR%'
   AND t.flex_value_set_id = fnd_flex_value_sets.flex_value_set_id;

值集null占位符

:$FLEX$注意大小写, 建议全大写
WHERE  (GL.ACCESS_SET_ID = :$FLEX$.CUX_AR_SET_OF_BOOKS:NULL OR :$FLEX$.CUX_AR_SET_OF_BOOKS:NULL IS NULL) AND ADJUSTMENT_PERIOD_FLAG = 'N'
   AND GL.DEFAULT_LEDGER_ID = SET_OF_BOOKS_ID
   AND (APPLICATION_ID = 222)
 ORDER BY PERIOD_YEAR DESC
         ,PERIOD_NUM  DESC

值集(总账定义)

主要表

	select * from fnd_flex_value_sets   
	select * from fnd_flex_values       
	select * from fnd_flex_values_vl

常用值集sql

--值集
SELECT flex_value_meaning
      ,description
      ,ffvv.FLEX_VALUE_ID
  FROM fnd_flex_values_vl ffvv
 WHERE ffvv.flex_value_set_id =
       (SELECT flex_value_set_id
          FROM fnd_flex_vset_v
         WHERE 1 = 1
           AND (parent_value_set_name = 'COMPANY_PROL'));

-- 从属值集
SELECT ffvv.flex_value
      ,ffvv.flex_value_meaning
      ,ffvv.description
  FROM fnd_flex_values_vl ffvv
      ,(SELECT flex_value_set_id
              ,flex_value
          FROM fnd_flex_vset_v
         WHERE ((1 = 1))
           AND (validation_type IN ('I'
                                   ,'D'
                                   ,'X'
                                   ,'Y') OR
               (validation_type = 'F' AND summary_allowed_flag = 'Y'))
           AND (dep_value_set_name LIKE 'COMPANY_SUBACC')
           AND (flex_value = '1012010101')) t
 WHERE 1 = 1
   AND (ffvv.flex_value_set_id = t.flex_value_set_id)
   AND (ffvv.parent_flex_value_low = t.flex_value)
 ORDER BY ffvv.flex_value