Oracle R12 Lot Genealogy and Noetix

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;

1. Which lots were created as a result of purchase order W?
2. Which batches did lot X go into? 
3. Which lots did batch Y go into?
4. 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?