Achieving Inventory segregation in Oracle E-Business suite

INTRODUCTION

There are several business scenarios where organizations have to segregate their inventory or supplies pipelined within a Stock Keeping Unit, based on the respective driving demand’s attributes or business functions. The supplies to be pipelined include Onhand, Open purchase orders, Open manufacturing/assembly orders, transfer requests, etc. In such scenarios, the demands are required to be fulfilled from the respective segregated pool of inventory. Below are a few scenarios driving the inventory segregation.

  • Forecast Protection for Customer/Business Line/Cost Center: Protect the supplies driven by the respective customer/business line/cost center’s forecast. This would also drive the accountability of the forecast. Hi-tech industry distribution industries use forecasts from the CEMs & OEMs to drive the supplies and the distributors are required to pipeline and fulfill the CEMs based on their forecast proportions.
  • Point of Sales information to Suppliers: Some semiconductor manufacturers force the distributors to provide the point of sales-related information while placing the purchase orders. The details distributors had to provide included contract manufacturer, End customer/OEM, and any associated end customer project details associated with the sales.
  • Country of Origin: The same item/SKU can be sourced/manufactured from different sites. Certain customer sales need to be refrained from specific COO (Country of origin) source stock. Ex. COO – Chain, COO – MX. This drives the need for segregating the inventory by COO.
  • Grade/Quality: Segregating the inventory by different grades/quality and control the consumption based on business rules.

Inventory segregation can be accomplished by Oracle ERP’s out-of-the-box functionalities, some potential options are:

  1. Separate inventory organization for each Customer/Business Line.
  2. Separate subinventories for each Customer/Business Line.
  3. Define separate items for each Customer/Business Line.
  4. Implement Oracle Projects to segregate inventory at the locator level.

Though inventory can be segregated using the above solution options, there are some downsides with these options, especially when a business prefers logical segregation over physical segregation, to reduce the operational cost, warehouse size, and inventory optimization.

  • The option of having a separate inventory organization or sub-inventory demands a physical segregation of the inventory within the same warehouse facility.  This means a bigger warehouse and increased workload to manage day-to-day inventory transactions – warehouse operators performing put away and picking & shipping transactions from the exact physical locations will slow down inventory movements.
  • The option of having discrete items will not be efficient as well, as it requires a proliferation of items and maintenance will be huge. This will create a huge dependency on planners and sales representatives to book purchase and sales orders using correct part numbers. This will also need additional space in the warehouse facility.
  • Using Oracle ERP projects to segregate the inventory by projects will be an apt solution for project-driven supply chain environments, but this will be overkill for organizations that are not tracking the expenditure and costs at the business function level. Like the options discussed above, it will also drive additional space and operational costs.

This document provides a detailed solution approach on how to achieve logical segregation of inventory within an organization without physical segregation which is a very prevalent scenario in warehouses, distribution industry, retail inventory, and situations where there are space constraints.

SCOPE

This document discusses an end-to-end supply chain plugin solution for Oracle EBS customers where the inventory had to be segregated by driving demand’s business function, without physical segregation. This solution can manage several inventory segregation scenarios discussed in the introduction. The solution covers supply chain planning and execution functions related to logical inventory segregation not limited to demand planning, supply planning, purchasing, Manufacturing, Inventory Management, and Order Management. Below are inventory segregation features the solution can accommodate.

  1. Demand Forecasting at the business function level.
  2. Supply Planning at the business function level.
  3. Purchasing at the business function level.
  4. Identify the inventory availability of an item at the business function level.
  5. Fulfill the sales order demand at the business function level.
  6. Fulfill the work order demand at the business function level.
  7. Transfer of inventory across business functions.

SOLUTION APPROACH

The key to the solution of logical segregation of Inventory at a business function is to have the business function details tagged against every supply and demand transaction: Forecast, Purchase Orders, Work Orders, Sales Orders, Work Orders, and miscellaneous issue and receipt transactions. This information is used in driving the supply planning process (supply-demand pegging), as well as in deriving the available Onhand for every item at a business function level.

Business function details of the inventory/Onhand are dynamically derived by evaluating the net available quantity of an item for a given business function by deriving this information from the source supply and demand documents pertaining to the transactional data i.e., purchase order receipt, sales order issue, work order completion, component issue to the work order, miscellaneous issue and receipt transactions pertaining to that item. The inventory pertaining to the transactional data that cannot be linked to a business function will result in a common pool inventory.

Below are the Oracle application features leveraged for this solution:

  1. Leveraging Oracle Advance Supply Chain Planning’s Collection Hook (MSC_CL_CLEANSE) for planning ODS data manipulation i.e., project population.
  2. Using MSC_RELEASE_HOOK to populate the business function on the planning recommendations – purchasing requisition, internal requisition, and work order.
  3. Repurposing Demantra Standard level ‘Sales Channel’ to capture the Forecast’s business and enhancing the Sales and Booking History collection to collect the sales order’s business function to generate statistical Forecasts.
  4. Using Value Set to maintain the valid business functions that are applicable in the inventory segregation process.
  5. Having custom inventory transaction types to support miscellaneous receipts and issues.
  6. Using descriptive flex fields to capture business function information at Purchase orders, Work orders, Sales orders, and Custom transaction types.
  7. Using Lookups to maintain the standard and custom transaction types that need to be considered in calculating the net available quantity for a given business function.

Below is the high-level solution schematic, covering the end-to-end solution elements.

SOLUTION DETAILS

Demand Forecasting at the business function level

The solution assumes Oracle Demantra as the demand planning tool. The requirement in the demand planning areas is to manage external demands at the business function level, generate statistical forecasts at the business function level, and publish the final forecast to supply planning at the business function level.

This is achieved by modeling the business function as a Level in Demantra, thereby external demands imported into Demantra are always linked to a business function, and the sales history collected into the Demantra has the custom level defaulted (from the order’s business function). And while publishing the final forecast to planning individual Demantra Integration profiles are created to export forecasts for every business function separately and tag them with the respective business function.

 

Supply Planning at the business function level

To peg the supplies within a business function, ASCP’s project-based pegging feature is leveraged. While the Projects is not enabled for these Inventory Organizations and the supply/demand transactions are not project-driven, additional data manipulation logic is added to massage the collected supply/demand to allocate the business function as project numbers on every supply-demand transaction: Sales Order, Forecast, Onhand, Purchase Order, Work Order, purchase requestion. These project numbers are imaginary sequential numbers representing a unique business function. Below is the list of transactions and their project number derivation logic.

Sales orders: Map the business function information as projects.

Onhand: The Onhand that is available in the Planning ODS snapshot is at an aggregate level, this will need to be split into business functions and the business function had to be tagged as projects. A Custom function is developed for deriving the business function level inventory. This custom function is discussed in the later section.

Forecasts: As Demantra publishes the forecast for every business function as separate profiles representing the business function, the forecast profile name is mapped as the project during forecast data cleansing

Work Orders: Planning recommendations will be released by the planners using the planner workbench. While released, the recommendations for the items for ‘Make’ type and work orders will be released. All these work orders will carry the respective business function information.

Purchase requisitions: While releasing the recommendations for the items that are marked as ‘Buy’ type, purchase requisitions will be released. These purchase requisitions will carry the respective business function information.

Purchase orders: The business function for PO will be derived from Purchase requisitions, which originate from the plan’s recommendation for plan-generated purchases or manually entered by the business while it is an Adhoc PO.

Internal requisitions: While releasing recommendations for items that are marked as ‘Transfer from’ as sourcing, internal requisitions will be released. These internal requisitions will carry the respective business function information.

The Advanced Supply Chain Planning collection program consists of two stages:

Stage 1 – Planning data pull: In the Data Pull Stage – Planning Data Pull, Refresh Snapshot, and Planning Data Pull Worker requests are run.

Stage 2 – Planning ODS Load: In the ODS Load Stage – Planning ODS Load, Planning ODS Load Worker, and Planning Data Collection Purge Staging tables requests are run.

Step 2 of the collection program is modified to accommodate updating the supply/demand in the ODS tables based on business function information on the transaction.  The Oracle standard MSC_CL_CLEANSE Collection Hook Package is called by the Collection program and is customized through a package to update the business function values as project numbers/IDs on the above-mentioned Supply/Demand transactions which are residing in the planning ODS staging tables.

 

Purchase the inventory at the business function level

Purchase requisitions and Purchase orders need a placeholder to capture the Business function information, which is achieved through a new DFF on PR & PO line. While the requisitions are created through the Plan’s recommendation releases, the business function of the respective project will be stored on the purchase requisition DFF through MSC_RELEASE_HOOK custom logic. The PR’s business function would default to the Purchase Order’s Business Function DFF through auto requisition conversion enhancement.

 

Identify inventory availability of an item at the business function level

For deriving inventory snapshots for a given item, warehouse, and business function a custom function is defined to calculate the “Net available inventory per business function” and “Net available common pool inventory.”

Net available inventory per business function is calculated as “Total Receipts  – Total Consumption” for the given item, warehouse, and business function.

    • Total Receipts =  Sum of purchase order receipts + Sum of work order completions + Sum of custom miscellaneous Receipts
    • Total Consumption = Sum of sales order issues + Sum of the reservations against sales order lines + Sum of work order component issues + Sum of reservations against work orders + Sum of custom miscellaneous issues

The receipt and consumption transactions to be considered by the function are derived from a custom lookup for future scalability. As every transaction (Purchase Order, Sales Order, Work Order, and Custom miscellaneous transactions) has the business function identifier as the DFF, the corresponding material transaction’s business function can be identified. This function will be used in building the reports/dashboards to show the data as per the organization/business needs.

Net available common pool inventory is calculated as “Total available Onhand Quantity – Net available inventory associated to business functions” for a given item and warehouse.

Fulfill the sales order demand at the business function level

Sales users enter the business function on sales order lines DFF before booking. For internal sales orders, is workflow is customized to populate the business function in the descriptive flexfields by deriving it from internal requisition.

A custom reservation program will be defined that reserves the sales orders by calling the inventory snapshot function. The reservation program reserves the inventory only if there is enough inventory available for the corresponding business function. Once the inventory is reserved, sales orders will be progressed to create deliveries, pick, pack, and ship out.

The inventory snapshot function derives reservations and sales order issue transactions in calculating the net available inventory for a business function and net common pool inventory.

Fulfill the work order demand at the business function level

Work orders created from the planning process will have the business function populated in the work order descriptive flex fields. On the manually created work orders, production users will enter the business function before releasing them.

A custom reservation program will be defined that reserves the work orders by calling the inventory snapshot function. The reservation program reserves the inventory only if there is enough inventory available for the corresponding business function. Once the inventory is reserved, work orders will be progressed to component issue and work order completion.

The inventory snapshot function derives reservations and work order component issues, work order assembly completion transactions in calculating the net available inventory for a business function, and net common pool inventory.

Transfer of inventory across business functions

A custom program is defined to transfer the inventory across business functions by performing miscellaneous issues and then miscellaneous receipts for the same quantity using custom transaction types. This program will have input parameters such as Item, warehouse, quantity, transfer from business function, and transfer to business function. The program will validate if there is available inventory for the given combination of Item, warehouse, quantity, and transfer from a business function. If there is an available quantity, then the inventory will get issued out by custom miscellaneous issue transaction type by populating the transfer from business function in the transaction descriptive flex field, and for the same quantity, inventory will get received by custom miscellaneous transaction type by populating the transfer to a business function in the transaction descriptive flex field. At the end of the process, inventory will be transferred from the donating business function to the receiving business function. This program will also be applicable for transferring from a common pool to a business function and vice versa.

A report will be designed based on the inventory snapshot function to show the onhand availability at the Item, Org, and business function levels. This report will also be used by inventory users when there is a shortage of inventory for a business function to get the inventory snapshot across all the business functions and the common pool. If the inventory manager decides to consume the common pool inventory before procuring additional supplies, must run this custom program to get the inventory swapped from the donating business function to the receiving business function.

The inventory snapshot function derives the miscellaneous issue and receipt transactions in calculating the net available inventory for a business function and net common pool inventory.

Conclusion:

The need for inventory segregation is widespread in different organizations. This Oracle EBS plugin solution will help these organizations achieve logical inventory segregation with minimal customization by leveraging the existing Oracle hooks, application features – DFFs, workflows, Demantra Levels, and ASCP’s project pegging feature. This system solution manages to segregate the inventory with no additional operational and facility costs. Also, the solution helps to manage optimized inventory levels by providing the capability to monitor the inventory level at different business functions and cross-utilize them through swap features.

References:

·         Data Hooks for Manipulating Data in Value Chain Planning (aka APS Advanced Planning and Scheduling) (Doc ID 976328.1)

·         Understanding and Troubleshooting the Release from Planners Workbench in VCP Applications (Doc ID 1346745.1)

Author Details

Thangarajan Teekaraman

Thangarajan Teekaraman has 19 years of experience in Supply Chain, Consulting and Project Management. He has vast experience in providing Oracle OnPrem & Cloud Supply Chain product-based Solutions, Global & Digital Transformations, Supply Chain product developments for Manufacturing & Hi-Tech clients in North America, Europe, and Asia. He is certified in APICS – CPIM & Oracle Cloud Supply Chain products.

Hari Prasad Reddy Naga

Hari Prasad Reddy Naga has 21 years of experience in consulting, solution architecture, project management, and pre-sales. He has vast experience in providing Oracle OnPrem & Cloud Supply Chain product-based Solutions, Global & Digital Transformations, Supply Chain product developments for Manufacturing & Hi-Tech clients in North America, Europe, and Asia. He is certified in Oracle Cloud and OnPrem Supply Chain products.

Leave a Comment

Your email address will not be published. Required fields are marked *