Join the OracleApps88 Telegram group @OracleApps88to get more information on Oracle EBS R12/Oracle Fusion applications.

If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.

Wednesday, October 17, 2012

Query to get Flex Values based on Segment Independent Names



SELECT
      DISTINCT FIF.ID_FLEX_CODE "Id Flex Code"
    , FIF.ID_FLEX_NAME "Title"
    , FIFST.ID_FLEX_STRUCTURE_NAME "Structure"
    , FIF.DESCRIPTION "Description"
    , FIFS.APPLICATION_COLUMN_NAME "Table Column"
    , FIFS.SEGMENT_NAME "Segment Independent"
    , FFV.FLEX_VALUE "Value"
    , FFVT.DESCRIPTION "Description"
    , FL.MEANING "Enabled"
    , FL_PARENT.MEANING "Parent"

FROM FND_ID_FLEXS FIF
   ,FND_ID_FLEX_STRUCTURES_TL FIFST
   ,FND_ID_FLEX_SEGMENTS FIFS
   ,FND_FLEX_VALUES FFV
   ,FND_FLEX_VALUES_TL FFVT
   ,FND_LOOKUPS FL
   ,FND_LOOKUPS FL_PARENT

WHERE
 -- RESTRICTIONS TO GET TITLE FIRST
     FIF.ID_FLEX_NAME LIKE 'Category Flexfield'
-- RESTRICTIONS TO GET STRUCTURE
  AND FIFST.ID_FLEX_CODE = FIF.ID_FLEX_CODE
  AND FIFST.LANGUAGE = 'US'
--RESTRICTIONS TO GET SEGMENT INDEPENDENT
  AND FIFS.ID_FLEX_CODE = FIF.ID_FLEX_CODE
   -- ENTER MAJOR CATEGORY, MINOR CATEGORY, ACCOUNT, COMPANY ETC BASED ON OUR REQUIREMENTS
  AND FIFS.SEGMENT_NAME = '&INDEPENDENT_SEGMENT'
-- RESTRICTIONS TO GET VALUES (NOTHING BUT ACCOUNT NUMBERS BASED ON SEGMENT EX. DEPARTMENT, ACCOUNT, COMPANY, PRODUCT SOON.)
  AND FFV.FLEX_VALUE_SET_ID = FIFS.FLEX_VALUE_SET_ID
  AND FFVT.FLEX_VALUE_ID = FFV.FLEX_VALUE_ID
  AND FFVT.LANGUAGE = 'US'
  AND FL.LOOKUP_TYPE (+) = 'YES_NO'
  AND FL.LOOKUP_CODE (+) = FFV.ENABLED_FLAG
  AND FL_PARENT.LOOKUP_TYPE (+) = 'YES_NO'
  AND FL_PARENT.LOOKUP_CODE (+) = FFV.SUMMARY_FLAG

ORDER BY 7;

2 comments:

Sanjeev Bajpai said...

Good query

APD said...

Hi How to include org_id in this query .I want to display Operating Unit idn while displaying flex_value and description .How to achieve that ?.Do i need to include hr_operating_unit table in this if so what is the joining condition >.Please help

Post a Comment

If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.
Best Blogger TipsGet Flower Effect