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;

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect