Monday, January 21, 2013

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;

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect