If, like me, you work in a process manufacturing* company one of the key things you need to worry about is lot genealogy. Put simply this is the process that a manufacturer uses to determine where the raw ingredients went. When you read about a product recall in the press this is the process the company will have gone through to trace where every product to be recalled went.
Here's a simple diagram of the process;
 |
| Simple Lot Genealogy |
As is made (hopefully!) obvious in the diagram the main issue with reporting on Lot Genealogy is recursion. In Release 11 Oracle used to provide a lot genealogy report, it seems they have removed this report in Release 12 although the same information is available via the standard Oracle Forms (although if you have to file reports with the FDA or HMRC it's questionable how well "we've got it on the screen" will go down).
Each step in the above process is logged as an item transaction (amongst a lot of other events). There are four steps to the process;
- Which lots were created as a result of purchase order W?
- Which batches did lot X go into?
- Which lots did batch Y go into?
- Which lots fulfilled sales order Z?
Working through these in turn and using the Noetix View INVG0_Lot_Transactions the SQL to see which lots were created by a receipt into inventory from a Purchase Order is;
SELECT ilt.lot_number FROM invg0_lot_transactions ilt WHERE 1 = 1 AND ilt.PO_Number = 'XXX' AND ilt.transaction_type = 'PO Receipt';
Now the first thing you'll notice if you're familiar with the view is that I'm using a "PO Number" column - a column that doesn't exist in the basic view as provided by Noetix. In order to simplify not just Lot Genealogy but reporting in general we have added a number of customisations to this view. These are available as a single file
here (via Google Docs). Strictly speaking it's not necessary to add these customisations as we could do it all in the script I'll show you below, but it does make it a great deal easier (for example do you prefer "DECODE( MATTR.TRANSACTION_SOURCE_TYPE_ID, 5, MATTR.Transaction_Source_ID, null)" or just Batch_Id?).
The second point, which batches did lot X go into, is a little more complicated as we are also at this stage looking to exclude batches where the lot was issues incorrectly and then fully returned (using the "WIP Issue" and "WIP Return" transaction types). See below for the SQL;
SELECT ilt.Batch_Number, SUM(ilt.TRANSACTION_QUANTITY)
FROM invg0_lot_transactions ilt
WHERE 1=1
AND ilt.lot_number = 'XXX'
AND ilt.Transaction_Source_Type = 'Job or Schedule'
AND (ilt.transaction_type = 'WIP Issue' OR ilt.Transaction_Type = 'WIP Return')
GROUP BY ilt.batch_number
HAVING SUM(ilt.TRANSACTION_QUANTITY) < 0
The third point, which lots did batch X produce, goes back to the simple-style of the first
SELECT itd.LOT_NUMBER
FROM invg0_lot_transactions itd
WHERE 1=1
AND itd.transaction_type = 'WIP Completion'
AND itd.Transaction_Source_Type = 'Job or Schedule'
AND itd.Batch_Number = 'XXX'
The key is "WIP Completion". Now this is where it gets a little interesting. With the SQL above we will pick up "Work in Progress" batches. These are where we have a multi-stage manufacturing process - such as granulation and then coating - and need to put the intermediary product on the system. So when the result of granulation is transported for coating it is stored on the system as a new batch and then the batch is emptied and input into the coating process - we could have filtered these out using the "WIP Completion Return" type in the same way we did above with WIP Issue/ WIP Return but the view I took when developing this is that correcting a mistake (as is the case with WIP Issue/ WIP Return) is different from materials passing through a batch as part of a process (WIP Completion). Hope that's clear?
The final part, which lots fulfil a sales order, is probably the simplest piece of SQL yet;
SELECT itd.LOT_NUMBER
FROM invg0_lot_transactions itd
WHERE 1=1
AND itd.Sales_Order_Number = 'XXX'
Now we have the four parts of the story we need to work out lot genealogy it seems like it's just a simple matter of adding in a recursive lookup on batches to lots and we're there. And yes - you could do it that way but in my experience what you end up with is the recursion being done in different ways in different reports (for example if we are wanting to know where a raw material went in a report we might want to exclude packaging lots). You then find yourself having to "debug" a problem with a report and spending hours working out what this specific version of log genealogy is doing.
For this reason I have implemented a solution that processes the transactions as they are done and builds a cache of lot genealogy information (with a few bits of useful reporting information to prevent some of the more obvious lookups).
The first step is to build the tables. The three tables I'm using are;
- Lot_Genealogy_Settings (this just holds a setting to record the last transaction processed),
- Lot_Genealogy_WhereUsed (this holds the genealogy information from a "where used" perspective), and
- Lot_Genealogy_Batch_Product (this holds a list of the products for each batch)
Starting with the simplest table the columns for Lot_Genealogy_Settings are;
 |
| Table Description: Lot_Genealogy_Settings |
The creation script is available
here (via Google Docs).
NOTE: You'll need to insert a single record to be picked up as the minimum transaction number (use -1) see the code for the package below to determine the values!
The columns for the Lot_Genealogy_Batch_Product are;
 |
| Table Description: Lot_Genealogy_Batch_Product |
The creation script is available
here (via Google Docs).
You'll notice when you look at the SQL that this table is indexed in order to speed up the retrieval of data. The time taken will depend on the amount of data you have but with the indexs (on this and the following table) the time taken to process eight months of data for us was about 3 minutes (that's approximately 571,000 lot transactions).
The final table (and the most important one) is Lot_Genealogy_WhereUsed, it's columns are;
 |
| Table Description: Lot_Genealogy_WhereUsed |
The creation script for this table is available
here (via Google Docs).
You'll notice that I'm creating all the tables in the NOETIX_SYS schema. The reason for this is fairly simple; as this package (and process) is dependant on Noetix and the Noetix Views will be rebuilt periodically I think it's better to have the creation script for the *package* (below) to be part of Noetix Regenerate; this way it gets automatically tested each time we do a build.
Now here's the script to create the package header;
create or replace package lot_genealogy is
procedure doUpdate;
end lot_genealogy;
That's a little simple, here's the body;
create or replace package body lot_genealogy is
procedure doUpdate as
v_Exists BOOLEAN;
v_MaxTransactionNumber NUMBER;
BEGIN
-- SECTION 0100: Remove Any Existing Records From The Cache
/*DELETE FROM NOETIX_SYS.Lot_Genealogy_WhereUsed;
DELETE FROM NOETIX_SYS.LOT_GENEALOGY_BATCH_PRODUCT;
COMMIT;
UPDATE NOETIX_SYS.Lot_Genealogy_Settings LGS
SET LGS.VALUE_NUMBER = -1
WHERE LGS.Setting_Name = 'MAX_TRANS_NO';*/
FOR v_Check IN (SELECT VALUE_NUMBER
FROM NOETIX_SYS.Lot_Genealogy_Settings LGS
WHERE LGS.Setting_Name = 'MAX_TRANS_NO') LOOP
v_MaxTransactionNumber := v_Check.Value_Number;
END LOOP;
-- SECTION 1000: Process The Lot Transaction Records From INVG0_Lot_Transactions
FOR v_Transaction IN (SELECT ilt.Transaction_Number,
ilt.batch_number,
ilt.transaction_quantity,
ilt.lot_number,
ilt.transaction_type,
ilt.ITEM$Item,
ilt.Item_Description,
ilt.Item_Type_Code
FROM INVG0_LOT_TRANSACTIONS ilt
WHERE ilt.Transaction_Source_Type =
'Job or Schedule'
AND (ilt.transaction_type = 'WIP Issue' OR
--ilt.Transaction_Type = 'WIP Return' OR
ilt.Transaction_Type = 'WIP Completion')
AND ilt.Transaction_Number >
v_MaxTransactionNumber
--AND ilt.lot_number IN ('166130', '035598', '166127')
/*AND ilt.lot_number IN
('029996',
'165507WIP',
'165507C1',
'165507',
'165583',
'167866WIP',
'167866',
'167951')*/
--AND ilt.transaction_number <= 2053180 -- For testing, about 6,000 records
ORDER BY ilt.Transaction_Number) LOOP
-- SECTION 1100: Process WIP Completion Transaction
-- NOTE: This section answers the question "What lots did batch X produce?"
IF (v_Transaction.Transaction_Type = 'WIP Completion') THEN
-- If we have already created records for this batch then we can update them with the new
-- product UNLESS we already have a product for this batch
UPDATE NOETIX_SYS.Lot_Genealogy_WhereUsed LGI
SET LGI.PRODUCT_LOT_NUMBER = v_Transaction.Lot_Number,
LGI.Product_Item$item = v_Transaction.Item$item,
LGI.Product_Item_Description = v_Transaction.Item_Description,
LGI.Product_Item_Type_Code = v_Transaction.Item_Type_Code
WHERE LGI.BATCH_NUMBER = v_Transaction.Batch_Number
AND (LGI.PRODUCT_LOT_NUMBER IS NULL OR -- ... we haven't updated the product lot before
LGI.PRODUCT_LOT_NUMBER = v_Transaction.Lot_Number); -- ... we have but this is the same
IF SQL%ROWCOUNT = 0 THEN
-- Nothing's been updated. New record?
-- Insert the new record for each batch number where it already exists in the LG table (providing
-- it doesn't already exist)
INSERT INTO NOETIX_SYS.Lot_Genealogy_WhereUsed
SELECT DISTINCT LGI.Master_Lot_Number, -- Mater_Lot_Number
LGI.INGRED_LOT_NUMBER, -- Ingred_Lot_Number
LGI.INGRED_ITEM$ITEM, -- INGRED_ITEM$ITEM
LGI.INGRED_ITEM_DESCRIPTION, -- INGRED_ITEM_DESCRIPTION
LGI.INGRED_ITEM_TYPE_CODE, -- INGRED_ITEM_TYPE_CODE
LGI.BATCH_NUMBER,
v_Transaction.Lot_Number, -- Product_Lot_Number
v_Transaction.ITEM$ITEM, -- PRODUCT_ITEM$ITEM
v_Transaction.ITEM_DESCRIPTION, -- PRODUCT_ITEM_DESCRIPTION
v_Transaction.ITEM_TYPE_CODE -- PRODUCT_ITEM_TYPE_CODE
FROM NOETIX_SYS.Lot_Genealogy_WhereUsed LGI
WHERE LGI.BATCH_NUMBER = v_Transaction.Batch_Number
AND NOT EXISTS
(SELECT 1
FROM NOETIX_SYS.Lot_Genealogy_WhereUsed LGI1
WHERE LGI1.MASTER_LOT_NUMBER = LGI.Master_Lot_Number
AND LGI1.Ingred_LOT_NUMBER = LGI.ingred_Lot_Number
AND LGI1.BATCH_NUMBER = LGI.Batch_Number
AND LGI1.PRODUCT_LOT_NUMBER =
v_Transaction.Lot_Number);
END IF;
-- In case this batch appears in another transaction later let's record
-- what the completed products were
INSERT INTO NOETIX_SYS.LOT_GENEALOGY_BATCH_PRODUCT
SELECT v_Transaction.Batch_Number, -- Batch_Number,
v_Transaction.Lot_Number, -- Product_Lot_Number
v_Transaction.ITEM$Item, -- PRODUCT_ITEM$ITEM
v_Transaction.Item_Description, -- Product_Item_Description
v_Transaction.Item_Type_Code -- Product_Item_Type_Code
FROM DUAL
WHERE NOT EXISTS
(SELECT 1
FROM NOETIX_SYS. LGBP
WHERE LGBP.BATCH_NUMBER = v_Transaction.Batch_Number
AND LGBP.Product_Lot_Number = v_Transaction.Lot_Number);
ELSIF (v_Transaction.Transaction_Type = 'WIP Issue') THEN
v_Exists := False;
-- Was this issue to the Batch reversed?
FOR v_Check IN (SELECT 'X'
FROM INVG0_LOT_TRANSACTIONS ilt
WHERE ilt.Transaction_Source_Type =
'Job or Schedule'
AND ilt.Transaction_Type = 'WIP Return'
AND ilt.batch_number = v_Transaction.Batch_Number
and ilt.TRANSACTION_QUANTITY =
(v_Transaction.Transaction_Quantity * -1)
and ilt.LOT_NUMBER = v_Transaction.Lot_Number) LOOP
v_Exists := True; -- Yes it was (WIP Issued in error)
END LOOP;
-- If the WIP Issue wasn't reversed ...
IF (NOT v_Exists) THEN
-- Get the batch product(s) (if we can) otherwise return NULL
FOR v_Product IN (SELECT LGBP.PRODUCT_LOT_NUMBER,
LGBP.PRODUCT_ITEM$ITEM,
LGBP.PRODUCT_ITEM_DESCRIPTION,
LGBP.PRODUCT_ITEM_TYPE_CODE
FROM NOETIX_SYS.LOT_GENEALOGY_BATCH_PRODUCT LGBP
WHERE LGBP.BATCH_NUMBER =
v_Transaction.Batch_Number
UNION
SELECT NULL, NULL, NULL, NULL
FROM DUAL
WHERE NOT EXISTS
(SELECT 1
FROM NOETIX_SYS.LOT_GENEALOGY_BATCH_PRODUCT LGBP
WHERE LGBP.BATCH_NUMBER =
v_Transaction.Batch_Number)) LOOP
-- For each batch product create a LG record (if it doesn't already exist)
INSERT INTO NOETIX_SYS.Lot_Genealogy_WhereUsed
SELECT v_Transaction.Lot_Number, -- Master_Lot_Number
v_Transaction.Lot_Number, -- Ingred_Lot_Number
v_Transaction.Item$item, -- INGRED_ITEM$ITEM
v_Transaction.Item_Description, -- INGRED_ITEM_DESCRIPTION
v_Transaction.Item_Type_Code, -- INGRED_ITEM_TYPE_CODE
v_Transaction.Batch_Number, -- Batch_Number
v_Product.Product_Lot_Number, -- Product_Lot_Number
v_Product.PRODUCT_ITEM$ITEM, -- PRODUCT_ITEM$ITEM
v_Product.PRODUCT_ITEM_DESCRIPTION, -- PRODUCT_ITEM_DESCRIPTION
v_Product.PRODUCT_ITEM_TYPE_CODE -- PRODUCT_ITEM_TYPE_CODE
FROM DUAL
WHERE NOT EXISTS
(SELECT 1
FROM NOETIX_SYS.Lot_Genealogy_WhereUsed LGI
WHERE LGI.MASTER_LOT_NUMBER =
v_Transaction.Lot_Number
AND LGI.Ingred_LOT_NUMBER =
v_Transaction.Lot_Number
AND LGI.Batch_Number = v_Transaction.Batch_Number
AND (v_Product.Product_Lot_Number IS NULL AND
LGI.PRODUCT_LOT_NUMBER IS NULL OR
v_Product.Product_Lot_Number =
LGI.PRODUCT_LOT_NUMBER));
-- ... and add to existing master lot numbers with the same product lot
INSERT INTO NOETIX_SYS.Lot_Genealogy_WhereUsed
SELECT DISTINCT LGI.MASTER_LOT_NUMBER, -- Master_Lot_Number
v_Transaction.Lot_Number, -- Ingred_Lot_Number
v_Transaction.Item$item, -- INGRED_ITEM$ITEM
v_Transaction.Item_Description, -- INGRED_ITEM_DESCRIPTION
v_Transaction.Item_Type_Code, -- INGRED_ITEM_TYPE_CODE
v_Transaction.Batch_Number, -- Batch_Number
v_Product.Product_Lot_Number, -- Product_Lot_Number
v_Product.PRODUCT_ITEM$ITEM, -- PRODUCT_ITEM$ITEM
v_Product.PRODUCT_ITEM_DESCRIPTION, -- PRODUCT_ITEM_DESCRIPTION
v_Product.PRODUCT_ITEM_TYPE_CODE -- PRODUCT_ITEM_TYPE_CODE
FROM NOETIX_SYS.Lot_Genealogy_WhereUsed LGI
WHERE LGI.Product_Lot_Number = v_Transaction.Lot_Number
AND NOT EXISTS
(SELECT 1
FROM NOETIX_SYS.Lot_Genealogy_WhereUsed LGI1
WHERE LGI1.MASTER_LOT_NUMBER = LGI.MASTER_LOT_NUMBER
AND LGI1.Ingred_LOT_NUMBER =
v_Transaction.Lot_Number
AND LGI1.Batch_Number = v_Transaction.Batch_Number
AND (v_Product.Product_Lot_Number IS NULL AND
LGI1.PRODUCT_LOT_NUMBER IS NULL OR
v_Product.Product_Lot_Number =
LGI1.PRODUCT_LOT_NUMBER));
END LOOP;
END IF;
END IF;
-- Record the transaction number we've just processed so we don't process it again
v_MaxTransactionNumber := v_Transaction.Transaction_Number;
END LOOP;
-- Record the last transaction number we processed so we can go from there next time
UPDATE NOETIX_SYS.Lot_Genealogy_Settings LGS
SET LGS.VALUE_NUMBER = v_MaxTransactionNumber
WHERE LGS.Setting_Name = 'MAX_TRANS_NO';
COMMIT;
end doUpdate;
end lot_genealogy;
Now you'll see that I've included a fair amount of comments on what the code is doing and also I've left in (but commented out) some of the debug lines I've been using. you can also tell my clear preference for using FOR ... LOOP instead of SELECT ... INTO statements (mainly so I don't have a temporary variable lying around or end up with a variable called v_Temp).
I hope this is fairly self-explanatory what it's doing. Let me know in the comments below if you have any questions or if anything is not clear.
*- Process Manufacturing means you take ingredients and you feed them into a process and make something as opposed to Discrete Manufacturing where you take parts and assemble them into something. The key difference is that you don't know how much (exactly) you will make in Process Manufacturing whereas for Discrete if you have all the parts of a car you will make precisely one car. Clear?
