- Most of the business an on-going requirements to mass update the item attributes thru the FBDI Load in Oracle Fusion Product Information Management (PIM). To update the items thru the FBDI load, First we need to create the item import files using the standaard Item Import Template file. To build the item import template, First we need to get the item extract info from the Oracle Fusion for the key attributes like template name (Item Class – Item User Type) etc., Based on the item extract info, The item import template will be created.
- Secondly, The ttems info to be extracted for the business analysis like reviewing lead time, planning method, planner codes, list price, serial controlled items, item categories (Inventory / Costing / Planning / TAX / Purchasing), OM Attributes (Back 2 Back, ATP, Sales Account) and Item cost etc.,
Here is the Item extract SQL which i used for the item data analysis and item updates thru FBDI.
SELECT ESI.INVENTORY_ITEM_ID,
ESI.ORGANIZATION_ID,
ESI.ITEM_NUMBER,
IOP.ORGANIZATION_CODE INV_ORG,
— EIC.ITEM_CLASS_ID,
EIC.ITEM_CLASS_NAME ITEM_CLASS,
ESI.ITEM_TYPE ITEM_TYPE,
(SELECT MEANING
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE = ‘EGP_ITEM_TYPE’
AND lookup_code = ESI.ITEM_TYPE) User_Item_type,
ESI.PRIMARY_UOM_CODE PRIMARY_UNIT_OF_MEASURE,
— ESI.WIP_SUPPLY_TYPE BUILD_IN_WIP,
ESI.BUILD_IN_WIP_FLAG BUILD_IN_WIP,
ESI.COSTING_ENABLED_FLAG COSTING_ENABLED,
ESI.INVENTORY_ASSET_FLAG INVENTORY_ASSET_VALUE,
DECODE (ESI.WIP_SUPPLY_TYPE,
1, ‘Push’,
2, ‘Assembly Pull’,
3, ‘Operation Pull’,
4, ‘Bulk’,
5, ‘Vendor’,
6, ‘Phantom’) PIM_SUPPLY_TYPE,
(SELECT MEANING
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE = ‘EGP_SERIAL_NUMBER_CONTROL_TYPE’
AND LOOKUP_CODE = ESI.SERIAL_NUMBER_CONTROL_CODE
AND ROWNUM = 1) SERIAL_NUMBER_CONTROL,
(SELECT MEANING
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE = ‘EGP_ORDATPCHECKVS_TYPE’
AND LOOKUP_CODE = ESI.ATP_FLAG
AND ROWNUM = 1) CHECK_ATP,
(SELECT MEANING
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE = ‘EGP_ORDATPCHECKVS_TYPE’
AND LOOKUP_CODE = ESI.ATP_COMPONENTS_FLAG
AND ROWNUM = 1) CHECK_ATP_COMPONENTS,
ESI.BACK_TO_BACK_ENABLED,
— ESI.SALES_ACCOUNT,
( GCC.SEGMENT1
|| ‘-‘
|| GCC.SEGMENT2
|| ‘-‘
|| GCC.SEGMENT3
|| ‘-‘
|| GCC.SEGMENT4
|| ‘-‘
|| GCC.SEGMENT5
|| ‘-‘
|| GCC.SEGMENT6
|| ‘-‘
|| GCC.SEGMENT7) SALES_ACCOUNT_CONCATENATED_SEGMENTS,
(SELECT MEANING
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE = ‘EGP_PLANNING_MAKE_BUY’
AND LOOKUP_CODE = ESI.PLANNING_MAKE_BUY_CODE
AND ROWNUM = 1) MAKE_OR_BUY,
MPWPV.PLANNER_CODE,
ESI.FIXED_LEAD_TIME,
ESI.VARIABLE_LEAD_TIME,
ESI.PREPROCESSING_LEAD_TIME,
ESI.POSTPROCESSING_LEAD_TIME,
ESI.FULL_LEAD_TIME PROCESSING_TIME,
ESI.CUMULATIVE_TOTAL_LEAD_TIME,
ESI.CUM_MANUFACTURING_LEAD_TIME,
ESI.LEAD_TIME_LOT_SIZE,
(SELECT MEANING
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE = ‘INV_MATERIAL_PLANNING’
AND LOOKUP_CODE = ESI.INVENTORY_PLANNING_CODE
AND ROWNUM = 1) INVENTORY_PLANNING_METHOD,
ESI.FIXED_ORDER_QUANTITY,
ESI.FIXED_LOT_MULTIPLIER,
ESI.FIXED_DAYS_SUPPLY,
ESI.MINIMUM_ORDER_QUANTITY,
ESI.MAXIMUM_ORDER_QUANTITY,
ESI.SAFETY_STOCK_PLANNING_METHOD,
ESI.DAYS_OF_COVER,
ESI.MRP_PLANNING_CODE,
(SELECT MEANING
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE = ‘EGP_PLANNING_CODE’
AND LOOKUP_CODE = ESI.MRP_PLANNING_CODE
AND ROWNUM = 1) MRP_MPS_PLANNING_METHOD,
ESI.CREATE_SUPPLY_FLAG CREATE_SUPPLY,
ESI.MINIMUM_ORDER_QUANTITY MINIMUM_ORDER_QTY,
ESI.MAXIMUM_ORDER_QUANTITY MAXIMUM_ORDER_QTY,
ESI.FIXED_ORDER_QUANTITY FIXED_ORDER,
ESI.MIN_MINMAX_QUANTITY MIN_MAX_MINIMUM_UNITS,
ESI.MAX_MINMAX_QUANTITY MIN_MAX_MAXIMUM_UNITS,
(SELECT CIC.TOTAL_COST
FROM CST_STD_COSTS CIC –CST_ITEM_COSTS_V CIC, CST_COST_ORG_PARAMETERS COSTORGPARAMETERS
WHERE 1=1
— AND CIC.COST_ORG_ID = COSTORGPARAMETERS.COST_ORG_ID
— AND COSTORGPARAMETERS.VALIDATION_ORGANIZATION_ID = ESI.ORGANIZATION_ID
AND CIC.INVENTORY_ITEM_ID = ESI.INVENTORY_ITEM_ID
AND SYSDATE BETWEEN NVL(CIC.EFFECTIVE_START_DATE,SYSDATE-1) AND NVL(CIC.EFFECTIVE_END_DATE,SYSDATE+1)
AND STATUS_CODE = ‘PUBLISHED’) UNIT_COST, —
ESI.CREATED_BY ITEM_CREATED_BY,
ESI.CREATION_DATE ITEM_CREATION_DATE,
EISV.INVENTORY_ITEM_STATUS_NAME ITEM_STATUS,
ESI.DEFAULT_INCLUDE_IN_ROLLUP_FLAG INCLUDE_IN_ROLLUP,
ESI.LIST_PRICE_PER_UNIT LIST_PRICE
FROM fusion.EGP_SYSTEM_ITEMS_B_V ESI,
fusion.EGP_ITEM_CLASSES_VL EIC,
fusion.EGP_ITEM_STATUS_VL EISV,
fusion.GL_CODE_COMBINATIONS GCC,
fusion.MSC_PLANNERS_WITH_PIMID_V MPWPV,
FUSION.INV_ORG_PARAMETERS IOP
WHERE 1 = 1
AND ESI.ITEM_CATALOG_GROUP_ID = EIC.ITEM_CLASS_ID
AND ESI.INVENTORY_ITEM_STATUS_CODE = EISV.INVENTORY_ITEM_STATUS_CODE(+)
AND ESI.SALES_ACCOUNT = GCC.CODE_COMBINATION_ID(+)
AND ESI.PLANNER_CODE = MPWPV.PLANNER_CODE(+)
AND ESI.ORGANIZATION_ID = MPWPV.ORGANIZATION_ID(+)
AND ESI.ORGANIZATION_ID = iop.ORGANIZATION_ID
— AND ESI.item_number = ‘<Item Number >’
— AND ESI.ORGANIZATION_ID = <Item org Id>