Oracle PDH Introduction

DFF EFF KFF  --

  Oracle Inventory -- EBS 20  item 

  

   Oracle Inventory 12 + Oracle PDH   --Fusion 

   

   Product 

   Product Information Management 

   

   Product Data Hub  --PDH Implementation CS

   

   EBS           Fusion 

   

   10 Inv          PDH 

   

   Product Information Management 

   

   

   Item Creation    EGP_SYSTEM_ITEMS_B   

   Organization Assignment 

   

   Manage Items 

   Create Item Categories 

   Create Categories 

   Create Invc Organizations 

   

   EGP_SYSTEM_ITEMS_B   CODE   MANBEN01   

   

   EGP_SYSTEM_ITEMS_TL   --Benisha Mango (Description) ,Long Descriptions 

   

         language=userenv('LANG')

         

         userenv('LANGUAGE')

   

   egp_system_items_tl_   --Audit tables 

   

     

   EGP

   

   PO -Master data Item ,Supplier 

   

   Master Data 

   Transactional Data 

    Create Item Categories 

Organizations 

Templates 

  EFF Attributes   (EFF ,ValueSets )

  Attribute Groups 

    Report Development 

    Understnding PDH Datamodel

    Item Rules -for Validations  /to incorporate business rules  

      Rest and Soap Webservices 

    Itegrations

        DellBhoomi

          Other Bhoomi Developer  --Integrsations 

          Fusion Techical Developer --

BULK Data Load 

     1)Import Map 

2)FBDI --File based data Import 

 

Role to enable Audit  --Audit Admin /Audit Viewer /Audit Write

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

Extensible Flexfields (EFF)

Descriptive Flexfields (DFF)

 

Example 

Setup and maintanance >>Search>>Manage Shipments>>

Supply Chain Execution >>Inventory Management >>Shipments >>Manage Shipments >>Additional Infornation 

I need to configure Manage Shipments level DFF 

How to find name of DFF to be configured ?

User Settings and Actions >>Highlight Flexfields >>Navigate to page >>You can see 2 bottons for every DFF (Information /Configurations)

  What is the navigation  to configure DFF?

Setup and Maintanance >>Global Search>>Manage Descriptive Flexfields >>Enter Your DFF Name Or Code >>Search 

Configure DFF >>

Edit >>Save>Deploy Flexfields >>

Testing 

---------

Open new session and navigate Manage Shipments page and open Additional Information 

To access DFF Data in BIP /OTBI Reports .Will you access it directly or what extra action to be?

BI Enabled -Yes  

Key Flexfields (KFF)

 KFF are used to capture mandatory or Key Business information of the Organization in coded format . Each Key Flex Field is having its won base Table.

A key flexfield is a field made up of segments, each of which has both a value and a meaning. You can think of a key flexfield as an “intelligent” field that your business can use to store information represented as codes.

Most organizations use codes to identify general ledger accounts, part numbers, and other business entities. Each segment in the code represents a characteristic of the entity. A combination of segment values, also known as a key flexfield code combination, uniquely describes a business entity stored in a key flexfield.

The organization decides the following four basic information for each key flexfield:

1. How many segments an entity has?
2. What each segment means?
3. What values each segment can have?
4. What each segment value means?

Few Key Flexfields in different oracle modules are:

Module: GL

1. Accounting

Module: HRMS

1. Job
2. Position
3. Grade
4. Personal Analysis   

Module: Inventory

1. Account Aliases
2. Item Catalogs
3. Item Categories
4. Sales Orders
5. Stock Locations
6. System Items

Module: Fixed Assets

1. Asset
2. Category
3. Location

Module: AR

1. Sales Tax Location
2. Territory

Note:

Flexfields consists of Structures > Structures consists of Segments > Segments consists of Value Set >Value Set consists of Parameters.

Main Tables:

FND_ID_FLEXS:

This table captures the information of all the Key FlexFields. The main columns in this table are:

• APPLICATION_ID ‐ Column consists of Application ID
• ID_FLEX_CODE ‐ Column KFF Code (like ‘GL#’, ‘AR#’ etc.)
• ID_FLEX_NAME  –  KFF Name (like ‘Accounting Flexfield’, ‘Category Flexfield’..etc.)
• APPLICATION_TABLE_NAME – Name of combination table (like ‘GL_CODE_COMBINATIONS’ , ‘FA_LOCATIONS’ etc.)

FND_ID_FLEX_STRUCTURES:

This table stores structure information about key Flexfields. Each Structure is uniquely identified by

• APPLICATION_ID – Module Code
• ID_FLEX_CODE  – Code of KFF
• ID_FLEX_NUM – Number of a Structure

FND_ID_FLEX_SEGMENTS:

It captures the information of Segments. Each Segment is Uniquely identified by

• APPLICATION_ID – Module Code
• ID_FLEX_CODE – Key Flexfield code
• ID_FLEX_NUM – Key flexfield structure number
• SEG_NUM – Segment number
• FLEX_VALUE_SET_ID – Flexfield value set identifier

FND_FLEX_VALUE_SETS:

This table captures the information of each Segment’s Value Set. Each Value Set is Uniquely identified by FLEX_VALUE_SET_ID as Foreign Key of FND_ID_FLEX_SEGMENTS Table.

FND_FLEX_VALUES:

This table captures the information each Value codes of a Value Set of a Segment. Each Value Code is uniquely identified by

• FLEX_VALUE_SET_ID
• FLEX_VALUE_ID

FND_FLEX_VALUES_TL:

This table captures the information of each Value Description of a Value Set of a Segment. Each Value Description is uniquely identified by FLEX_VALUE_ID.

AREA	KFF Name	TABELS
Inventory	Account Alias Flexfield	INV_GENERIC_DISPOSITIONS
General ledger	Accounting Flexfield	AP_INVOICE_LINES_ALL
		EXM_EXPENSE_DISTS
		EXM_SPEND_AUTH_DISTS
		FA_BOOK_CONTROLS
		FV_BE_TRANSACTION_ACCOUNTS
		FV_TR_FUND_ATTRIBUTES
		GL_ACCOUNT_ALIASES_B
		ZX_PROCESS_RESULTS
		GL_ESS_FILTER_SEGMENTS
		GL_JE_HEADERS
		PO_BUYER_ASSIGNMENT_RULES
		GL_CODE_COMBINATIONS
Assets	Asset Key Flexfield	FA_ASSET_KEYWORDS
		FA_ASSET_KEY_ALIASES_B
Budgetary Control	Budgeting Flexfield	XCC_BUDGET_ACCOUNTS
Assets	Category Flexfield	FA_CATEGORIES_B
		FA_CATEGORY_ALIASES_B
Cost Management	Consigned Flexfield	CST_VAL_UNIT_CONS_COMBINATIONS
		CST_VAL_UNIT_CONS_COMBINATIONS
Global Payroll	Cost Allocation Flexfield	PAY_COST_ALLOC_ACCOUNTS
		PAY_COST_ALLOC_KEYFLEX
Product Model	Item Categories	EGP_CATEGORIES_B
Assets	Location Flexfield	FA_LOCATIONS
		FA_DEPRN_TAX_AUTHORITIES
		FA_LOCATIONS_PRTL
		FA_LOCATION_ALIASES_B
Inventory Management	Locator Flexfield	INV_ITEM_LOCATIONS
Global Human Resources	People Group Flexfield	PER_PEOPLE_GROUPS
Revenue Management	Pricing Dimensions Flexfield	VRM_PRICING_COMBINATIONS
Cost Management	Valuation Unit Flex field	CST_VAL_UNIT_COMBINATIONS

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';