Flexfields with Differences

 

Flexfields	DFF	KFF	EFF
Differences	1. DFF is used to capture additional information at screen level or task level 2. DFF data will be captured into attribute columns within the same table	1. key information in coded format can be captured 2. KFF will  be captured in to segment columns	1. EFF = extensible flex field (you can create a table like field with multiple entries). 2. EFF data can be captured as multiple rows in _EFF_B table 3. It is One to Many
Tables	DFF will get store in same table attribute Columns fnd_descriptive_flexs_vl ffv, fnd_descr_flex_contexts_vl ffc, fnd_descr_flex_col_usage_vl att, fnd_flex_value_sets fvs, fnd_application_vl ap	KFF will get store in same table Segment Columns  FND_ID_FLEXS FIF    ,  FND_ID_FLEX_STRUCTURES FIFS ,  FND_ID_FLEX_SEGMENTS FIFSE	EFF data can be captured as multiple rows in _EFF_B table 1.Item  (ego_item_eff_b, ego_item_eff_tl) 2.Sales Order(doo_headers_eff_b, doo_headers_eff_tl)
Examples	Majority of  the Screens	1. Accounting Key FlexFields 2. Location Flexfields 3. Asset Category 4. Job 5. Position	Item EFF Order Fulfillment Line Information

Query:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

select  FIF.APPLICATION_ID  ,        FIF.ID_FLEX_CODE    ,         FIF.ID_FLEX_NAME    ,         FIF.APPLICATION_TABLE_NAME ,         FIF.DESCRIPTION     ,         FIFS.ID_FLEX_NUM    ,         FIFS.ID_FLEX_STRUCTURE_CODE  ,         FIFSE.SEGMENT_NAME,         FIFSE.SEGMENT_NUM,         FIFSE.FLEX_VALUE_SET_ID from    FND_ID_FLEXS FIF    ,         FND_ID_FLEX_STRUCTURES FIFS ,         FND_ID_FLEX_SEGMENTS FIFSE where   FIF.APPLICATION_ID = FIFS.APPLICATION_ID and     FIF.ID_FLEX_CODE   = FIFS.ID_FLEX_CODE and     FIFSE.APPLICATION_ID = FIF.APPLICATION_ID and     FIFSE.ID_FLEX_CODE = FIF.ID_FLEX_CODE and     FIFSE.ID_FLEX_NUM = FIFS.ID_FLEX_NUM and     FIF.ID_FLEX_CODE LIKE 'GL#' and     FIF.ID_FLEX_NAME LIKE 'Accounting Flexfield';

--Query to fetch DFF Entity name

SELECT

APPLICATION_TABLE_NAME,

CONTEXT_COLUMN_NAME ,

TITLE,

DESCRIPTION

FROM fnd_descriptive_flexs_vl 

where 1=1

-- AND application_table_name='PO_HEADERS_ALL'

--Query to fetch all the enabled DFF attributes for a specific entity (ex : PO_HEADERS_ALL)

SELECT ffv.descriptive_flexfield_name "DFF Name",

ffv.application_table_name "Table Name",

ffv.title "Title",

ap.application_name "Application",

ffc.descriptive_flex_context_code "Context Code",

ffc.descriptive_flex_context_name "Context Name",

ffc.description "Context Desc",

ffc.enabled_flag "Context Enable Flag",

att.column_seq_num "Segment Number",

att.form_left_prompt "Segment Name",

att.application_column_name "Column",

fvs.flex_value_set_name "Value Set",

att.display_flag "Displayed",

att.enabled_flag "Enabled",

att.required_flag "Required"

FROM 

fnd_descriptive_flexs_vl ffv,

fnd_descr_flex_contexts_vl ffc,

fnd_descr_flex_col_usage_vl att,

fnd_flex_value_sets fvs,

fnd_application_vl ap

WHERE ffv.descriptive_flexfield_name = att.descriptive_flexfield_name

AND ap.application_id=ffv.application_id

AND ffv.descriptive_flexfield_name = ffc.descriptive_flexfield_name

AND ffv.application_id = ffc.application_id

AND ffc.descriptive_flex_context_code=att.descriptive_flex_context_code

AND fvs.flex_value_set_id=att.flex_value_set_id

AND ffv.application_table_name='PO_HEADERS_ALL'

Query --EFF 

---------------

FROM   fusion.fnd_df_contexts_b AG,

       fusion.fnd_df_segments_b ATTR,

       ego_item_eff_b ESEB,

       egp_system_items_b ESI,

       egp_item_classes_tl eict,

       inv_org_parameters iop,

       egp_system_items_tl esit

WHERE  1 = 1

       AND (esi.Item_Number IN (:p_item_number) OR COALESCE(:p_item_number,NULL)IS NULL)

       AND AG.descriptive_flexfield_code = 'EGO_ITEM_EFF'

       AND AG.context_code = ATTR.context_code

       AND ESEB.context_code = ATTR.context_code

       AND ESEB.inventory_item_id = ESI.inventory_item_id

       AND ESEB.organization_id = ESI.organization_id

       AND eict.item_class_id = esi.item_catalog_group_id

       AND iop.organization_id = ESI.organization_id

       AND EICT.LANGUAGE = 'US'

       AND ESEB.acd_type = 'PROD'

       AND ESI.acd_type = 'PROD'

   AND esi.approval_status ='A'

       AND iop.organization_code='GLO'

       AND eict.item_class_name='Billing'

       AND ESIT.inventory_item_id = ESI.inventory_item_id

       AND ESIT.organization_id = ESI.organization_id

       AND ESIT.LANGUAGE = 'US'

       AND exists (select 1 from event 

                   where inventory_item_id=ESI.inventory_item_id)) aa,

       event     

WHERE  event.inventory_item_id = aa.itemid  

AND (aa.ItemNumber IN (:p_item_number) OR COALESCE(:p_item_number,NULL)IS NULL)

order by 18