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?

Noetix: Working With Item Cost Summaries

If you are, like us, users of Oracle Process Manufacturing then you'll have written a lot of reports which rely on Item Costings (both for products and ingredients). One of the things that makes the current structure of the Noetix View difficult to work with is that it provides a detailed breakdown of the costs - something that's useful if you're reporting on the costs themselves - which is not useful if you're reporting on, for example, the value of inventory where you're just interested in the *total* cost.

Of course it's fairly easy to get there, you just total up all the costs but what it does do is introduce unnecessary complexity in your SQL often leading to sub-selects in JOINS like the following;

select 

  gic.ITEM$Item,
  sum(decode(gic.Component_Group, 'LAB', gic.Item_Cost)) as TOTAL_LABOUR_COST,
  sum(decode(gic.Component_Group, 'OVE', gic.Item_Cost)) as TOTAL_OVERHEAD_COST,
  sum(decode(gic.Component_Group, 'MAT', gic.Item_Cost)) as TOTAL_MATERIAL_COST
from gmfg0_item_costs gic
where gic.PERIOD_CODE = :PERIODCODE
group by gic.ITEM$Item

As you can see we have three component groups LABour, OVErhead, and MATerial and while this is a fairly understandable piece of SQL what happens is that it gets repeated over and over again in various reports and when, as recently, we change the way costing works (by moving costs from one Organsiation to anotehr) it becomes very complex to make the change to all the affected reports.

Because of this we've introduced a summary view which sits on top of GMFG0_Item_Costs called GMFG0_Item_Cost_Summary which looks like this;

Name                Type         Nullable Default Comments
------------------- ------------ -------- ------- --------
ORGANIZATION_CODE   VARCHAR2(40) Y                        
PERIOD_CODE         VARCHAR2(10)                          
Z$INVG0_ITEMS       ROWID        Y                        
ITEM$ITEM           VARCHAR2(40) Y                        
TOTAL_LABOUR_COST   NUMBER       Y                        
TOTAL_OVERHEAD_COST NUMBER       Y                        
TOTAL_MATERIAL_COST NUMBER       Y                        
TOTAL_ITEM_COST     NUMBER       Y  

The SQL to generate it is;

create or replace view gmfg0_item_cost_summary as
select gic.Organization_Code,
       gic.period_code,
       gic.Z$INVG0_Items,
       gic.ITEM$Item,
       sum(decode(gic.Component_Group, 'LAB', gic.Item_Cost)) as TOTAL_LABOUR_COST,
       sum(decode(gic.Component_Group, 'OVE', gic.Item_Cost)) as TOTAL_OVERHEAD_COST,
       sum(decode(gic.Component_Group, 'MAT', gic.Item_Cost)) as TOTAL_MATERIAL_COST,
       sum(gic.Item_Cost) as TOTAL_ITEM_COST
  from gmfg0_item_costs gic
 group by gic.Organization_Code,
          gic.period_code,
          gic.Z$INVG0_Items,
          gic.ITEM$Item;

To have this view rebuilt every time we do a Noetix regenerate we have created a XU6 script to automatically build the view after every regenerate - it's available here (via Google Docs).

Oracle R12: Reporting Issues With Subledger Accounting

On 1st January this year we successfully migrated our existing R11 Oracle e-Business Suite installation to R12 and went live. The huge bulk of our reports had been upgraded to deal with the changes (largely now using Noetix Views) and with only about 100 reports remaining we were expecting to have completed the migration of existing reports and the creation of any new reports by the end of June 2012 (I'm writing this at the end of the May).

One of the process changes that was made as part of the R12 upgrade project was to enable some rules in Subledger Accounting. These are fairly simple rules and it probably helps to give an example so here goes;

A purchase order is raised for Item 7426-4 and then journalled into the GL (via the SLA). The original GL code defined with the purchase order line is;

2.001.9999.0000.0000.0000

It's not really important what the segments mean, but suffice to say when the final transaction is posted to the GL the account code has changed (by an SLA rule) to;

2.000.11111.0000.FOIL.0000

Looking at the lines in the GL (in Oracle) we see;

GL Journal Lines Showing the New Account Segments

From a Finance perspective this is good news as it dramatically reduces the chance of something being miss-coded by ignoring the codes the user has entered and replacing them with some pre-defined values.

However from a reporting standpoint this is a complete and total nightmare.

Why? Because the GL account code changes are not rolled back into the values defined for the Purchase Order. This means that if we are using the GL Account code in order to break down spending, which we are, then the only "true" value is in the GL.

On it's own this wouldn't be an issue except when you combine it with two others;

1. Subledger records in XLA_Distribution_Links only allow a GL Line to be linked to a *single* external entity. Thus if, for example, you want GL Lines with their Purchase Orders on a report (for example a Department Spend Report) then you need to look both at XLA_Distribution_Links that are linked directly to purchase orders, then those that are linked to invoices (which in turn are linked to purchase orders), then those lines which are linked to inventory transactions and then linked to purchase orders, etc. In short a relatively simple query against PO.PO_DISTRIBUTIONS_ALL becomes a complete nightmare of multiple UNIONS
2. The relationship between GL_JE_Lines and PO_Distributions_All isn't one-to-one. The tax line of distributions are merged into a single GL Line which means in XLA_Distribution_Links that single GL line is linked to every line on the purchase order.

Does anyone have a solution to this problem?!

Noetix: Release 11 to Release 12 Migration (Replacing Obsolete Views)

While migration to R12 with Noetix is very easy and relatively painless in some areas the significant changes in Inventory between the two versions make it practically impossible for Noetix views to handle the migration "invisibly" (as they tend to do in other areas like Account Payable).

This is especially true if your organisation, like mine, is taking this opportunity to do a bit of restructuring the system so that, for example, rather than using stock being in a particular warehouse to mean that it's a retained sample achieving the same thing using statuses and getting rid of the warehouse.

Here is a list of R11 and R12 Noetix View Templates with "suggested" replacements. It's not a complete list, but it is the list we have been working of for all our reports;

View Label >>>> Recommended Replacement
GMP_Forecast_Details >>>> INV_Forecasts
GMI_Unallocated_Inventory >>>> INV_Unallocated_Inventory view
GMI_Onhand_Inv_By_Lot  >>>> INV_Item_Onhand_By_Lot*
GMI_Onhand_Inv_By_Location  >>>> INV_Onhand_Quantities*
GMI_Month_End_Inventory  >>>> INV_Period_Close_Details*
GMI_Item_Master  >>>> INV_Items and INV_Item_Inventory_Attributes
GMI_Inv_Transactions  >>>> INV_Transactions or INV_Transaction_Details
GMF_Update_Subledger  >>>> GMF_SLA_Cost_Subledger
GMF_Cost_Warehouse_Assoc  >>>> GMF_Cost_Organization_Assc
GMF_Order_Details_Base  >>>> GMF_SLA_Cost_Subledger

*- The migration from R11 Process Manufacturing Inventory to R12 Common Inventory for us has been less than smooth and has not been helped by a few, dare I say it, "obvious" issues that Noetix should have addressed prior to us attempting the upgrade.

The most obvious issue was that in R12 there was no view that would tell us the lot number, the location, and the onhand quantity. The best we could do is two out of three. I just cannot imagine any organisation out there not wanting to see all three on the same report. This led to us having to make significant customisations in order for the migration path to actually be a migration path.

For example looking at the view template INV_Item_Onhand_By_Lot we have added the following customisation columns (with some additional information where appropriate);

Days_Since_First_Transaction
ITEM  (Table Alias= ITEM, Column expression=ITEM) << Flexfield
Item_Type  (Table Alias= ITEM, Column expression=ITEM_TYPE)
Item_Type_Code  (Table Alias= ITEM, Column expression=ITEM_TYPE)
LOCT  (Table Alias= LOCT, Column expression=LOCT) << Flexfield
Material_Status  (Table Alias= MMST, Column expression=DESCRIPTION)
Material_Status_Code  (Table Alias= MMST, Column expression=STATUS_CODE)
Organization_Code  (Table Alias= MPARM, Column expression=ORGANIZATION_CODE)










Here is our list of customisation to the GMI_Onhand_Inv_By_Lot view;
Batch_Id 
Company_Code  (Table Alias= XMAP, Column expression=COMPANY_CODE)
Company_Name  (Table Alias= XMAP, Column expression=COMPANY_NAME)
Days_Since_First_Transaction
Inventory_Class  (Table Alias= ITEM, Column expression=INV_CLASS)
Item_Cost_Class  (Table Alias= ITEM, Column expression=ITEMCOST_CLASS)
Item_Id  (Table Alias= ITEM, Column expression=Item_Id)
Lots_MFD_Flag  (Table Alias= LOTS, Column expression=DECODE(LOTS.DELETE_MARK,0,'N',1,'Y'))
Qc_Hold_Reason_Code  (Table Alias= LOINV, Column expression=QCHOLD_RES_CODE)




I've highlighted the location flexfield which if you look at the Noetix columns for the original view seems to directly map to the INVENTORY_LOCATION_CODE. There seems to be no mapping for this column down the migration path we were recommended to take.

I'm currently going through the process of sorting out our customisations (something that I'm sure will greatly relieve Noetix Support!) and I'll be publishing as much as I possibly can of the work we've done (including the difference between Lot Status and Material_Status which I'm sure will intrigue and worry people).

Oracle PL/SQL: Querying Inventory Quantities in R12.

As you have probably gathered from my other posts the company I work for uses NoetixViews for reporting against Oracle e-Business Suite however it looks like, at Release 12, Oracle has started providing some reasonably useful API's to get inventory quantities.

Take a look at the INV_Quantity_Tree_Pub package (under the APPS user) in your database. The "query_quantities" API has the following parameters;

P_API_VERSION_NUMBER    NUMBER    IN   
P_INIT_MSG_LST    VARCHAR2    IN    Y
X_RETURN_STATUS    VARCHAR2    OUT   
X_MSG_COUNT    NUMBER    OUT   
X_MSG_DATA    VARCHAR2    OUT   
P_ORGANIZATION_ID    NUMBER    IN   
P_INVENTORY_ITEM_ID    NUMBER    IN   
P_TREE_MODE    NUMBER    IN   
P_IS_REVISION_CONTROL    BOOLEAN    IN   
P_IS_LOT_CONTROL    BOOLEAN    IN   
P_IS_SERIAL_CONTROL    BOOLEAN    IN   
P_DEMAND_SOURCE_TYPE_ID    NUMBER    IN    Y
P_DEMAND_SOURCE_HEADER_ID    NUMBER    IN    Y
P_DEMAND_SOURCE_LINE_ID    NUMBER    IN    Y
P_DEMAND_SOURCE_NAME    VARCHAR2    IN    Y
P_LOT_EXPIRATION_DATE    DATE    IN    Y
P_REVISION    VARCHAR2    IN   
P_LOT_NUMBER    VARCHAR2    IN   
P_SUBINVENTORY_CODE    VARCHAR2    IN   
P_LOCATOR_ID    NUMBER    IN   
P_ONHAND_SOURCE    NUMBER    IN    Y
X_QOH    NUMBER    OUT   
X_RQOH    NUMBER    OUT   
X_QR    NUMBER    OUT   
X_QS    NUMBER    OUT   
X_ATT    NUMBER    OUT   
X_ATR    NUMBER    OUT   
P_TRANSFER_SUBINVENTORY_CODE    VARCHAR2    IN    Y
P_COST_GROUP_ID    NUMBER    IN    Y
P_LPN_ID    NUMBER    IN    Y
P_TRANSFER_LOCATOR_ID    NUMBER    IN    Y

I said they have "provided a reasonably useful API" not that they had provided an "easy to use API"! Clearly if you want to get anything useful out of this API then you need to build a wrapper package for this (particularly if you want to be able to query quantities in SQL rather than PL/SQL - which I'm assuming you do).

I'm going to suggest you create a wrapper function called "get_onhand_quantity" as an example, it should be pretty clear how you alter this to return some of the other quantities associated with the item, here is the SQL:

CREATE OR REPLACE FUNCTION get_onhand_quantity(p_organization_id   IN NUMBER,
                                               p_inventory_item_id IN NUMBER,
                                               p_subinventory_code IN VARCHAR2,
                                               p_locator_id        IN NUMBER,
                                               p_lot_number        IN VARCHAR2)
  RETURN NUMBER IS
  -- The various quantity values returned
  v_QuantityOnhand              NUMBER;
  v_QuantityReservableOnhand    NUMBER;
  v_QuantityReserved            NUMBER;
  v_QuantitySuggested           NUMBER;
  v_QuantityAvailableToTransact NUMBER;
  v_QuantityAvailableToReserve  NUMBER;

  -- Monitor the return status of the API
  v_ReturnStatus       VARCHAR2(1);
  v_ReturnMessageCount NUMBER;
  v_ReturnMessageData  VARCHAR2(256);
begin
  INV_QUANTITY_TREE_PUB.query_quantities(p_api_version_number         => 1,
                                         p_init_msg_lst               => fnd_api.g_false,
                                         x_return_status              => v_ReturnStatus,
                                         x_msg_count                  => v_ReturnMessageCount,
                                         x_msg_data                   => v_ReturnMessageData,
                                         p_organization_id            => p_organization_id,
                                         p_inventory_item_id          => p_inventory_item_id,
                                         p_tree_mode                  => apps.INV_Quantity_Tree_PUB.g_transaction_mode,
                                         p_is_revision_control        => FALSE,
                                         p_is_lot_control             => TRUE,
                                         p_is_serial_control          => FALSE,
                                         p_demand_source_type_id      => -9999,
                                         p_demand_source_header_id    => -9999,
                                         p_demand_source_line_id      => -9999,
                                         p_demand_source_name         => NULL,
                                         p_lot_expiration_date        => NULL,
                                         p_revision                   => NULL,
                                         p_lot_number                 => p_lot_number,
                                         p_subinventory_code          => p_subinventory_code,
                                         p_locator_id                 => p_locator_id,
                                         p_onhand_source              => inv_quantity_tree_pvt.g_all_subs,
                                         x_qoh                        => v_QuantityOnhand,
                                         x_rqoh                       => v_QuantityReservableOnhand,
                                         x_qr                         => v_QuantityReserved,
                                         x_qs                         => v_QuantitySuggested,
                                         x_att                        => v_QuantityAvailableToTransact,
                                         x_atr                        => v_QuantityAvailableToReserve,
                                         p_transfer_subinventory_code => NULL,
                                         p_cost_group_id              => NULL,
                                         p_lpn_id                     => NULL,
                                         p_transfer_locator_id        => NULL);

  RETURN(v_QuantityOnhand);
END get_onhand_quantity;

The script is available as a Google Document here (which might be slightly easier to see).

Now a quick test script to demonstrate how this works;

select ii.Onhand_Quantity
  from noetix_sys.INVG0_item_onhand_by_lot II
 where ii.ORGANIZATION_CODE = '01'
   and ii.Onhand_Quantity > 0
   and ii.Inventory_Item_ID = 5097
   and ii.lot_number = '155498RET'
   and rownum = 1;

select get_onhand_quantity(302, 5097, 'RETAIN', 37976, '155498RET') Onhand_Quantity
  from dual

The first step (at the top) is to use Noetix views to find the details of an item with Onhand Quantity greater than zero so that can be plugged into the Get_Onhand_Quantity function (it was also slightly comforting to see that both queries returned the same value).

What's it like for speed? Well that's an interesting question; it's substantially faster (as you'd expect; it's not going through a view) but as the timings for the first and second queries are 0.093 and 0.031 seconds respectively the difference isn't really significant in the wider scheme of things.

It will be interesting to see if a future version of Noetix uses the API (I'd certainly recommend it!).

Noetix: Expanding Basic Forecasting Functionality in R12

This blog post contains details of how to expand the basic Noetix Forecasting template (INV_Forecasts) to add in some of the nice new functionality in R12 such as grouping forecasts into Sets (i.e. all export forecasts could be in a set called EXPORTS and domestic forecasts one called DOMESTIC).

Looking at the existing Noetix view it accesses the Forecast data in a single table (MRP.MRP_FORECAST_DATES). The MRP Forecasting schema contains the additional tables MRP_Forecast_Designators (description and set information), MRP_Forecast_Items, MRP_Forecast_Updates, etc.

This change will add in the set and forecast description from the MRP_Forecast_Designators table. Looking at the possible joins between the two the easiest is;

This simply joins the two tables on the Forecast_Designator and Organziation_Id  if they exist in the designators table.

Looking at the INV_Forecasts queries that make up this view using the SQL;

select n.view_label || ', ' || 

       to_char(n.query_position) || ', ' ||
       nvl(n.union_minus_intersection, 'null') || ', ' || 

       n.view_comment "label,position,umi,comment"
  from n_view_query_templates n
 where n.view_label = 'INV_Forecasts'

Shows the following results;


label,position,umi,comment
INV_Forecasts, 1, null, forecast with day buckets
INV_Forecasts, 2, UNION ALL, forecast with week buckets
INV_Forecasts, 3, UNION ALL, forecast with period buckets

In order to get the view working we need to insert the new tables and columns into each of the queries (in effect doing the same thing three times).

Using the XU2 column addition generation script I have blogged about previously with the values INV_Forecasts, Forecast_Designator, and Y (include tables) gives you a script that can be quickly and easily edited.

As I'm sure you're aware you need to add in the table prior to adding in the columns and that each of the column addition scripts needs to be in a separate file. Because of this I'm going to add the table in the script which adds the Forecast_Description column.

The changes required to add the new table (and columns) to the view are;

In the SELECT;

• MFD.Description Forecast_Description,
• MFD.Disable_Date Forecast_Disable_Date
• MFD.FORECAST_SET Forecast_Set

In the FROM clause;

• MRP.Mrp_Forecast_Designators MFD

And finally in the WHERE clause;

• AND FODAT.Forecast_Designator = MFD.Forecast_Designator(+)
• AND FODAT.Organization_Id = MFD.Organization_Id(+) 

The three files are (all files stored in Google Docs);

inv_forecasts_forecast_description_xu2.sql

inv_forecasts_disable_date_xu2.sql
inv_forecasts_forecast_set_xu2.sql

Whilst we're here I'm going to add another two files to show who created the Forecast and when they did it (which I tend to find always useful - especially when identifying people to test changes!).

inv_forecasts_created_by_xu2.sql
inv_forecasts_creation_date_xu2.sql

You'll notice if you look in the "created_by" file that I'm using the AUTOJOIN column type in Noetix to quickly get this information. It can be a quick way of getting one field from another table but it does create problems if, for example, I wanted two fields (like the name and email address) and can introduce interdependencies between files that might cause problems if Noetix ever changes the way it works.

The next three changes I'm going to suggest are  replacing the existing Forecast_Quantity column with the Current and Original Forecast Quantities from the forecast table. It is, frankly, rubbish to try and hide these columns and I feel represents a complete lack of understanding on how companies use forecasting; "How close are we to the forecast?" is a frequent question and so hiding the columns just makes absolutely no logical sense. To make this change requires three files;

inv_forecasts_current_forecast_quantity_xu2
inv_forecasts_original_forecast_quantity_xu2
inv_forecasts_forecast_quantity_upd_xu2

Once you've downloaded these files and added them into your install directory you need to add a call to them in the xu2 file (wnoetxu2.sql) and then you can run a regenerate and do some additional testing. Remember that the forecast description needs to be added first as it adds the table to the view (required by the other two) for example;

@inv_forecasts_created_by_xu2.sql
@inv_forecasts_creation_date_xu2.sql
@inv_forecasts_current_forecast_quantity_xu2.sql
@inv_forecasts_forecast_description_xu2.sql -- Added MRP.MRP_FORECAST_DESIGNATORS(MFD)
@inv_forecasts_forecast_quantity_upd_xu2.sql
@inv_forecasts_disable_date_xu2.sql -- Requires MFD
@inv_forecasts_forecast_set_xu2.sql -- requires MFD
@inv_forecasts_original_forecast_quantity_xu2.sql

Hope this helps! Any questions leave a comment ...

Noetix: Removing Obsolete Columns at R12

This blog post includes a script-generating piece of PL/SQL that will write a series of scripts for you that will remove all the obsolete columns from your Noetix Views following a R12 upgrade.

For example if you look at the Finance views for Payables then you will see the column "Set_Of_Books_Name". Oracle have replaced sets of books in R12 so this column is no longer relevant. Noetix, rather than removing the column, have changed it so that rather than displaying data it just displays the results of a TO_CHAR(NULL) function call - i.e. Nothing.

If you speak to Noetix they will tell you that this allows your code to work across versions *however* in our experience of an R12 upgrade all this allowed was code that needed re-pointing to the new structures to *appear* to work. In the specific case of significant change like this experience has shown me that it's better to have everything collapsing in a big heap than appearing to work when it doesn't!

The following SQL detects the new "obsolete" columns at R12;

select n.view_label, n.column_label, n.query_position
  from n_view_column_templates n
 where n.column_expression like '%(NULL)%'
   and n.include_flag = 'Y'
   and n.product_version like '12%'
   and not exists (select 1
          from n_view_column_templates t
         where t.column_expression not like '%(NULL)%'
           and t.include_flag = 'Y'
           and t.product_version like '12%'
           and t.view_label = n.view_label
           and t.column_label = n.column_label)
 order by n.view_label, n.column_label, n.query_position

When you run it it will give you some idea of the extent of your problem (which will obviously be more significant the more you use oracle - for us this query returned move than 5,000 rows).

For every record returned by this query the script will generate output. In most cases there is a single query for each of the columns so you will see something like;

@utlspon ap_checks_set_of_books_name_upd_xu2
 
UPDATE n_view_column_templates
   SET product_version = '8',
       last_update_date = TO_DATE('17-NOV-2011'),
       last_updated_by = 'A Pellew'
 WHERE UPPER(view_label) = UPPER('AP_Checks')
   AND UPPER(column_label) = UPPER('Set_Of_Books_Name')
   AND QUERY_POSITION = 1
   AND PRODUCT_VERSION LIKE '12%'
;
 
COMMIT;
 
@utlspoff
----------------------------------------

In this case this is updating the view template AP_Checks, and moving the product_version of the "Set_Of_Books_Name" column back to version 8 - this will prevent it being picked up during a regenerate.

In the case of multiple queries the script will generate something similar to;

@utlspon ap_invoice_distributions_posted_amount_upd_xu2
 
UPDATE n_view_column_templates
   SET product_version = '8',
       last_update_date = TO_DATE('17-NOV-2011'),
       last_updated_by = 'A Pellew'
 WHERE UPPER(view_label) = UPPER('AP_Invoice_Distributions')
   AND UPPER(column_label) = UPPER('Posted_Amount')
   AND QUERY_POSITION = 4
   AND PRODUCT_VERSION LIKE '12%'
;
UPDATE n_view_column_templates
   SET product_version = '8',
       last_update_date = TO_DATE('17-NOV-2011'),
       last_updated_by = 'A Pellew'
 WHERE UPPER(view_label) = UPPER('AP_Invoice_Distributions')
   AND UPPER(column_label) = UPPER('Posted_Amount')
   AND QUERY_POSITION = 5
   AND PRODUCT_VERSION LIKE '12%'
;
 
COMMIT;
 
@utlspoff
----------------------------------------

This is removing the column "Posted_Amount" from the "AP_Invoice_Distributions" template where it appears in the 4th and 5th query positions.

The script is given below;

declare
  v_OldViewLabel   n_view_column_templates.view_label%TYPE := '@';
  v_OldColumnLabel n_view_column_templates.column_label%TYPE := '@';

  v_LastUpdateDate n_view_column_templates.last_update_date%TYPE := SYSDATE;
  v_LastUpdatedBy  n_view_column_templates.last_updated_by%TYPE := 'A Pellew';
begin
  for v_Data in (select n.view_label, n.column_label, n.query_position
                   from n_view_column_templates n
                  where n.column_expression like '%(NULL)%'
                    and n.include_flag = 'Y'
                    and n.product_version like '12%'
                    and not exists
                  (select 1
                           from n_view_column_templates t
                          where t.column_expression not like '%(NULL)%'
                            and t.include_flag = 'Y'
                            and t.product_version like '12%'
                            and t.view_label = n.view_label
                            and t.column_label = n.column_label)
                  order by n.view_label, n.column_label, n.query_position) loop
    if v_Data.view_label <> v_OldViewLabel or
       v_Data.column_label <> v_OldColumnLabel then
      if v_OldViewLabel <> '@' then
        dbms_output.put_line(' ');
        dbms_output.put_line('COMMIT; ');
        dbms_output.put_line(' ');
        dbms_output.put_line('@utlspoff ');
        dbms_output.put_line(LPAD('-', 40, '-'));
      end if;
      dbms_output.put_line('@utlspon ' || lower(v_Data.view_label) || '_' ||
                           lower(v_Data.column_label) || '_upd_xu2 ');
      v_OldViewLabel   := v_Data.view_label;
      v_OldColumnLabel := v_Data.column_label;
      dbms_output.put_line(' ');
    end if;
    dbms_output.put_line('UPDATE n_view_column_templates ');
    dbms_output.put_line('   SET product_version = ''8'', ');
    dbms_output.put_line('       last_update_date = TO_DATE(''' ||
                         TO_CHAR(v_LastUpdateDate, 'DD-MON-YYYY') ||
                         '''), ');
    dbms_output.put_line('       last_updated_by = ''' || v_LastUpdatedBy ||
                         ''' ');
    dbms_output.put_line(' WHERE UPPER(view_label) = UPPER(''' ||
                         v_Data.view_label || ''') ');
    dbms_output.put_line('   AND UPPER(column_label) = UPPER(''' ||
                         v_Data.column_label || ''') ');
    dbms_output.put_line('   AND QUERY_POSITION = ' ||
                         TO_CHAR(v_Data.Query_Position));
    dbms_output.put_line('   AND PRODUCT_VERSION LIKE ''12%'' ');
    dbms_output.put_line(';');
  end loop;
  dbms_output.put_line(' ');
  dbms_output.put_line('COMMIT; ');
  dbms_output.put_line(' ');
  dbms_output.put_line('@utlspoff ');
  dbms_output.put_line(LPAD('-', 40, '-'));
end;

You should change "A Pellew" at the top to be your own name!

NOTE: Two files are generated in error (due to the base data from Noetix not following their own standards - or at least not following any standards they tell developers to follow!). The two files (in our installation, there might be others in yours) are;

ar_std_rcpt_dist_sla_gl_je_line_item_number_upd_xu2.sql (Receivables)
fa_adjustments_sla_gl_je_acct$_upd_xu2.sql (Fixed Assets)

When you run your regenerate after adding all the files errors quickly show up. Just stop using files which prevent your regenerate from working (did that need saying?!). If you consider that we added almost 500 files finding 2 with errors is a pretty good error rate - imagine trying to write them all by hand.