Process to Upload On-Hand Inventory Data Using FBDI Template
1. Introduction
      On-Hand Inventory from Fishbowl will be uploaded using an Oracle provided File Based Data Import (FBDI) spreadsheet. Data will be required to be extracted from Fishbowl, mapped to the required columns in the FBDI template, and processed in Oracle.
The Oracle FBDI templates are created with embedded Excel Macro’s, which are used to create the files that are uploaded and processed in Oracle. Worksheets that are edited with Google Sheets, removes those Macros.  As such, the data will need to be cut and pasted back into an Excel workbook, so the Oracle Macro’s can create the data load packages.
2. Pre-Requisites to load the data
Roles :- User must have below roles to create Inventory Transactions 
 1. Warehouse Manager
 2. Warehouse Operator
 3. Inventory Manager
Data Access :- Inventory Organization data access should be there for above roles to create and maintain the inventory items.

3. Import Process:-
 a) Data validation:-
  Step 1: Map and Create Data
•	This procedure uses the Oracle created FBDI Template: InventoryTransactionImportTemplate
•	Only the following tabs in the spreadsheet should have data present:
o	INV_TRANSACTIONS_INTERFACE
o	CST_I_INCOMING_TXN_COST

Populating the Template:

INV_TRANSACTIONS_INTERFACE:
•	Column A: ORGANIZATION_NAME Must be one of the following values:
o	San Francisco Market
o	Phoenix Market
o	Austin Market
•	Column E: TRANSACTION_BATCH_SEQ
o	Unique Identifier for each batch processed. Free format, User Defined Number
o	Should be Numeric
•	Column F: PROCESS_FLAG
o	Value must be 1
•	Column H: ITEM_NUMBER
o	Valid Item Number in Oracle and Inventory Org
•	Column K: SUBINVENTORY_CODE
o	Valid Oracle Sub-Inventory. Must exist in Oracle. 
o	NOTE: Oracle sub-inventory locations DO NOT have any spaces in the name. For example:
o	“1201Stores” NOT “1201 Stores”
o	Remove any spaces in the name before processing in Oracle

•	Column L: LOCATOR_NAME
o	If the Fishbowl Locator is stored in a single string, that value can be populated in this cell, and a spreadsheet function can be performed to unstring the Locator to the correct Oracle Locator Segment cells. Assumes Fishbowl Locator has a delimiter to perform that function.  For example, using Fishbowl Stock Locator: 01.A.03.B
•	Column M: LOC_SEGEMNT1: ZONE
o	01
•	Column N: LOC_SEGMENT2 – ROW
o	A
•	Column O: LOC_SEGMENT3 – RACK
o	03
•	Column P: LOC_SEGEMNT4 - SHELF
o	B

•	Column AG: TRANSACTION_QUANTITY
o	Quantity 
•	Column AH: TRANSACTION_UOM
o	EA
•	Column AI: TRANSACTION_UNIT_OF_MEASURE
o	EA
•	Column AK: TRANSACTION_DATE
o	Column is defined as an Oracle Date Field
o	Enter value in cell such as: 5/10/2023  4:55:00 PM (example – use actual date)
•	Column AL: TRANSACTION_SOURCE_TYPE_NAME
o	Inventory
•	Column AM: TRANSACTION_TYPE_NAME
o	Miscellaneous Receipt
•	Column BK: PRIMARY_QUANTITY
o	=cell AG
•	Column BO:  SOURCE_CODE
o	CONV
•	Column BP: SOURCE_HEADER_ID
o	Should be Numeric and same as Batch ID
•	Column BQ: SOURCE_LINE_ID
o	Must be Numeric – start with 1000
o	For each cell populated, calculate cell vale as “+10” of previous cells value
•	Columns BS through BZ: Use the Account Number provided by Finance to populate the Account string.  For example: GL Account Number: 1001-89040-30001-9991-99992-99993-9994-99995 would populate the cells as follows (remember just an example – use account number provided by Finance):
•	Column BS: DSP_SEGEMENT1: 1001
•	Column BT: DSP_SEGMENT2: 89040
•	Column BU: DSP_SEGMENT3: 30001
•	Column BV: DSP_SEGMENT4: 9991
•	Column BW: DSP_SEGEMNT5: 99992
•	Column BX: DSP_SEGMENT6: 99993
•	Column BY: DSP_SEGMENT7: 9994
•	Column BZ: DSP_SEGMENT8: 99995
•	Column CS: TRANSACTION_MODE
o	3
•	Column CY: LOCK_FLAG:
o	2
•	Column CZ: TRANSACTION_REFERENCE
o	 Fishbowl Conversion
•	Columns DL through DS: Use the Account Number provided by Finance (same account number used in Columns BS-BZ) to populate the Account string.  For example: GL Account Number: 1001-89040-30001-9991-99992-99993-9994-99995 populate the cells as follows (remember just an example – use account number provided by Finance:
•	Column DL: DSP_SEGEMENT1: 1001 (= BS)
•	Column DM: DSP_SEGMENT2: 89040 (= BT)
•	Column DN: DSP_SEGMENT3: 30001 (=BU)
•	Column DO: DSP_SEGMENT4: 9991 (=BV)
•	Column DP: DSP_SEGEMNT5: 99992 (=BW)
•	Column DQ: DSP_SEGMENT6: 99993 (=BX)
•	Column DR: DSP_SEGMENT7: 9994 (=BY)
•	Column DS: DSP_SEGMENT8: 99995 (=BZ)
•	Column HR: TRANSACTION_COST_IDENTIFIER
o	Start with the Value 1
o	For each subsequent cell the value should be “+1” from previous cell
o	Note: this value will be used in the CST_I_INCOMING_TXN_COSTS Tab in this workbook
•	Column IP: USE_CURRENT_COST:
o	N
CST_I_INCOMING_TRANSACTION_COSTS:
•	Column A: TRANSACTION_COST_IDENTIFIER
o	Starting in ROW 5 = EQUALS VALUE in INV_TRANSACTIONS_INTERFACE Row 5 Column HR (TRANSACTION_COST_IDENTIFIER)
o	Populate to match the total number of Rows in the INV_TRANSACTIONS_INTERFACE worksheet. The last value in Column A should match the last value in Column HR in INV_TRANSACTIONS_INTERFACE
•	Column B: COST_COMPONENT_CODE
o	ITEM_PRICE
•	Column C: COST
o	Value must be 0.00 (no nulls or blanks)
b) Process:-
  Oracle Load Process:
•	FBDI Template: InventoryTransactionImportTemplate
o	INV_TRANSACTIONS_INTERFACE
o	CST_I_INCOMING_TXN_COSTS
o	(all other Tabs must be blank)

In the first page of the Workbook generate the CSV and ZIP FIle:

Navigator > Tools > File Import and Export (may be in My Enterprise as well)
•	Add (+)
•	File – Choose Zip File
•	Account: scm/inventoryTransaction/import
•	Save and Close

Navigator > Tools > Scheduled Processes
•	Schedule New Process
•	Name: Load Interface File for Import
•	Import Process: Create Inventory Transactions
•	Data File (select zip file uploaded in previous step)
•	Submit
•	Review Logs to and validate it completed successfully
Navigator > Tools > Scheduled Processes
•	Schedule New Process
o	Search and Select: Manage Inventory Transactions
o	Click Submit and note Job Number
•	Review Logs to and validate it completed successfully
4. Navigation to Verify in Front End
 From Oracle Applications Landing Page:
•	Supply Chain Execution
•	Inventory Management
•	Tasks:
o	Review Pending Transactions and look for errors:

   Identify and Fix Errors

Reprocess/Re-Load failed transactions:
Review Completed Transactions:
From Oracle Applications Landing Page:
•	Supply Chain Execution
•	Inventory Management
•	Tasks:
o	Review and validate Completed Transactions:

5.Extraction Query to Reconcile Data
 After successful migration of data through FBDI we need to Reconcile the data via BIP query.
SELECT esi.item_number,
               esi.description,
               iop.organization_code,
               iodv.organization_name,	    
               SUM(ioqd.transaction_quantity) onhand_qty,    
               ioqd.subinventory_code,
               iil.segment1||'.'||iil.segment2||'.'||iil.segment3||'.'||iil.segment4 locator,
       	       uomv.unit_of_measure,
              DECODE(esi.planning_make_buy_code,2, 'Buy',1, 'Make',esi.planning_make_buy_code)make_buy_code
          FROM inv_onhand_quantities_detail ioqd,
               egp_system_items esi,
               inv_org_parameters iop,
               inv_organization_definitions_v iodv,
               inv_units_of_measure_vl uomv,
               inv_item_locations iil
         WHERE 1 = 1
           AND iodv.organization_id = iop.organization_id
           AND ioqd.inventory_item_id(+)  = esi.inventory_item_id
           AND ioqd.organization_id(+)  = esi.organization_id
           AND esi.organization_id = iop.organization_id
           AND uomv.uom_code = esi.primary_uom_code
           AND esi.organization_id = iil.organization_id (+)
           AND ioqd.subinventory_code = iil.subinventory_code (+)
           AND ioqd.locator_id = iil.inventory_location_id (+)   
		AND ioqd.CREATED_BY =’ABCDEF'
           AND TRUNC(ioqd.creation_date) >= TRUNC(sysdate)
           GROUP BY esi.item_number,
       	            esi.description,
       	            iop.organization_code,
                    iodv.organization_name,    
                    ioqd.subinventory_code,
                    iil.segment1,iil.segment2,iil.segment3,iil.segment4,
       	            uomv.unit_of_measure
                   ,esi.inventory_item_id
                   ,esi.organization_id
                   ,DECODE(esi.planning_make_buy_code,2, 'Buy',1, 'Make',esi.planning_make_buy_code)
6. Error Extract Query
select 
ERROR_CODE,
ERROR_EXPLANATION,
REQUEST_ID,
LOAD_REQUEST_ID,
ORGANIZATION_NAME,
DST_SEGMENT1,
INVENTORY_ITEM,
LOCATOR_NAME,
ORGANIZATION_TYPE,
ITEM_NUMBER
from 
INV_TRANSACTIONS_INTERFACE
WHERE 1=1 
And LOAD_REQUEST_ID=nvl(:p_LOAD_REQUEST_ID,LOAD_REQUEST_ID)
And trunc(creation_date) between nvl(:p_creation_from_date,trunc(creation_date)) and  nvl(:p_creation_to_date,trunc(creation_date))
and created_by=nvl(:p_created_by,created_by)