Formula based Material Issue

By | February 25, 2021

This “Formula based material issue” concept will be mostly useful in Pharmaceutical Industries, as these type of industries produce medicines. In manufacturing process, drugs will be considered as Raw Material to produce one particular tablet/ medicine. For this, quantity of required drugs should be correctly calculated. By using this formula based concept we will be making tablets/medicines using the appropriate quantities of the drugs required.

In Sage X3, while creating a work order when user selects a FG/SFG product, its respective component will get automatically populated on work order screen in component tab as per the defined BOM and BOM code. And as per the BOM quantity allocation is done.
But one of our client wanted to have a control on the issuance of the raw material to production on the basis of formula they have already defined. When GRN is received QC is done, on the basis of results QC department note down the Assays and Moisture contain in raw material.

In order to compensate this loss additional quantities of that particular material should be issued to production when that particular LOT is used in production on the basis of the formula defined.

Let’s understand this concept with an example:

When GRN is done QC entry is created automatically in Sage X3. When QC user does the quality testing we will create the technical sheet at QC entry where he/she will enter Assays and Moisture attribute as shown in the below screenshot.

New Stuff: How to solve ‘User does not have any Badge for product’ error while running the batch server during Migration process in Sage X3

Technical Sheet
[Technical Sheet]

We have entered values in Technical Sheet for raw material “RA010” i.e. Assay: 98 and Moisture: 2.
These attributes will be linked to that particular LOT.

At Work Order, the allocation of the LOT will be as per FEFO basis but even if user wishes to change the allocation he/she can alter it. Now we will see the requirement quantity of “RA010” before allocation in the work order as mentioned in the BOM.

Requirement Qty before Allocation
[Requirement Qty before Allocation]

Formula which we are using is:


We will use this formula in our example for better understanding:

We have a work order which determines that 80000 quantity of FG1 (TEST Product) needs to be produce. RA010 = 24 KG is required to produce 1 Batch (80000) of FG1 which is already mentioned in BOM as shown earlier.

ASSAY: 98 %, MOISTURE: 2 %
BATCH SIZE: 8000, KG per Batch: 24

Formula used in example

= 24.989 Kg per Batch

Now, we have customized the screen in such a way that when user clicks on the ‘SAVE’ button of Work Order after creation and lot allocation, components quantity gets updated as per the quantity derived after formulation:

Requirement Qty after Allocation
[Requirement Qty after Allocation]

NOTE: To enable the save button, you need to select the “Allocation” checkbox in the header after lot allocation.

Quantity for “RA010” is updated as expected but for “RA051” component we have not entered values in the technical sheet. Due to which quantity remains same for that component.

In this way on the basis of formula, allocation will be done. By doing this, correct calculated quantity of required materials will be issued on every work order in the manufacturing of the FG Product.