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).

Noetix: Omitting View Templates (Script)

Similarly to a previous post to omit view columns from templates (see here) this script will allow you to omit whole view templates by calling a single file. Here is the script;

define noetix_view = &1;
define update_date = &2;

UPDATE N_View_Templates N
   SET N.USER_INCLUDE_FLAG = 'N',
       n.last_update_date = TO_DATE('&update_date'),
       n.last_updated_by = 'A Pellew'
 WHERE n.view_label = '&noetix_view'
;

COMMIT;

The script file (omit_view_template_xu2.sql - I've called it) takes two parameters the first if the view label to disable and the second is the date to set the Last_Update_Date to.

You can call this script from wnoetxu2.sql. For example;

@omit_view_template_xu2.sql INV_Transaction_Details 17-JUN-2012

The point of doing this was to make sure that, if the way a view is disabled changes, we only have to update a single file. Hope this helps!

Noetix: Omitting A Column From a Template (Script)

As you might be aware Noetix 6.0.2 has introduced a new way to omit columns from view templates. Previous you needed to update the Product_Version column with a product version that would never be used by your installation (we typically picked '8'), from the new version there is a new column called User_Include_Flag which you can set to 'N' to exclude a column.

This makes a lot more sense but it has highlighted the issue that we have a lot of changes to make to our various script files in order to introduce the new functionality - hardly ideal. In order to get round this we have introduced a new script filed called "omit_view_column_template_xu2.sql";

define noetix_view = &1;
define column_label = &2;
define update_date = &3;

UPDATE N_View_Column_Templates N
   SET N.USER_INCLUDE_FLAG = 'N',
       n.last_update_date = TO_DATE('&update_date'),
       n.last_updated_by = 'A Pellew'
 WHERE n.view_label = '&noetix_view'
   AND N.column_label = '&column_label'
;
 
COMMIT;

This is pretty simple, it takes three parameters and omits the specified column from the specified view updating the Last_Update_Date to the date you specify. You'll notice my name is hard-coded in the Last_Updated_By column - feel free to replace with your own.

This way if there is any future changes in Noetix functionality we only need to update a single file to ensure they are taken into account!

As an example the following (from wnoetxu2.sql);

@omit_view_column_template_xu2.sql INV_Onhand_Quantities Organization 21-JUN-2012

Will omit the column "Organization" from the view INV_Onhand_Quantities (setting the date to 21-JUN-2012).

In the logfile you will see the following output when the script is called;

SQL> @omit_view_column_template_xu2.sql INV_Onhand_Quantities Organization 21-JUN-2012
SQL> define noetix_view = &1;
SQL> define column_label = &2;
SQL> define update_date = &3;
SQL>
SQL> UPDATE N_View_Column_Templates N
  2     SET N.USER_INCLUDE_FLAG = 'N',
  3         n.last_update_date = TO_DATE('&update_date'),
  4         n.last_updated_by = 'A Pellew'
  5   WHERE n.view_label = '&noetix_view'
  6     AND N.column_label = '&column_label'
  7  ;
old   3:        n.last_update_date = TO_DATE('&update_date'),
new   3:        n.last_update_date = TO_DATE('21-JUN-2012'),
old   5:  WHERE n.view_label = '&noetix_view'
new   5:  WHERE n.view_label = 'INV_Onhand_Quantities'
old   6:    AND N.column_label = '&column_label'
new   6:    AND N.column_label = 'Organization'

2 rows updated.

SQL>
SQL> COMMIT;

Commit complete.

SQL>
SQL> SQL>

Noetix Customisations (Per Month)

We are in the final stages of completing our R11 to R12 migration project and I thought it would be useful (because someone asked) to talk about the Customisations we have made to NoetixViews during the course of the project.

These are changes that you could submit to Noetix to be covered by a Noetix Customisation Maintenance (NCM) agreement (i.e. standard changes to views/ columns).

Changes Per Month

The chart shows that we have had a peak of over 400 changes in the month before our "go-live" (which was January 2012). This number of changes is likely to be either a new view (or a couple of new views!) or some mass omitting of columns (for example those whose descriptions start with "OBSOLETE" at R12!).

It's worth noting that by definition a row that's created is also updated so there is some "doubling up" in the numbers.

For those of you interested in the RAW data here is is (via Google Docs).

Here are our top customisations per view;

The views I've highlighted in red are completely new views we had to create to fill a gap. The INV_X_Dispensing view is a new view we created because of our customised dispensing module, all the other views we have created are reporting against standard Oracle Functionality (for example the RA_Customers_All view is simply the RA_Customers view without filtering out customers).

Hopefully I'll be able to publish all our customisations at some later date.

Noetix: Extracting All Changes To a NoetixView To A Single File

NOTE: Last update, adding in some more information to the report, new version 0.9.2. It's now a requirement that you use this script to Omit Columns (makes the SQL much more readable, and reduces the amount of code that needs to be updated when Noetix update their processes - as they did at 6.0.2).

The purpose of this script is to combine all the changes made by a specific group of users to a single NoetixView template (in the tables N_View_Column_Templates, N_View_Table_Templates, and N_View_Where_Templates) into a single file split into four sections according to the template below;

-- NOETIX VIEW UPDATE TEMPLATE V0.9.1
-- NOTE: All changes to a single Noetix view should be included in one file (base view changes
--   should be in a separate file)
--
@utlspon

-- Section 1: Removing existing columns, queries, etc use a single update where possible

-- Section 2: Table (and Where clause) additions. Group by the change, not the type (i.e. add the
--   first table, then add the where clauses for the first table, then the second table, then the
--   where clauses for the second table, etc)

-- Section 3: Column Additions (in alphabetical order of Column_Label)

-- Section 4: Updates

COMMIT;

@utlspoff
 
The script is available here (via Google Docs), because it's quite long I've not copied/ pasted it into this blog.

Just to give you some idea we have approximately 3,000 files, the aim of using this script is to reduce this to a more manageable number (in the ten's).

The newly announced NoetixViews Workbench will (hopefully) further reduce the need for these legacy files.

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).

Noetix: Disabling Material Category (XXK_MTL_CAT) caching

This is slightly more complicated than the other "removal of caching" blog posts I've done earlier as there are multiple views (one for each structure) which all need to be updated and replaced at the same time. I've adopted a fairly simple approach;

• Remove any backup (_BK) views
• Create a new view to replace the root XXK_MTL_CAT view
• Go through the ALL_VIEWS system view looking for the structure-specific views and then individually replace each of them with a view based on the new XXK_MTL_CAT view created above
• Rebuild all the objects that have been invalidated by this change

Here is the script (you'll notice the * instead of the important piece of SQL, more details on that later);

@utlspon xu6_2012_replace_xxk_mtl_cat_view

BEGIN
  for v_Data in (SELECT av.view_name
                   FROM ALL_VIEWS av
                  WHERE av.owner = USER
                    and av.view_name LIKE 'XXK_MTL_CAT_%BK') loop
    execute immediate 'drop view ' || v_Data.view_name; -- Remove an existing backup view if it exists
  end loop;
END;
/

RENAME XXK_MTL_CAT TO XXK_MTL_CAT_BK
/

CREATE OR REPLACE VIEW XXK_MTL_CAT AS

*

/

begin
  for v_view in (select av.*
                   from all_views av
                  where av.owner = USER
                    and av.view_name like 'XXK_MTL_CAT_%') loop
    if instr(v_view.text, 'NOETIX_SYS.N_KFF_MTL_CAT') > 0 then
        execute immediate 'CREATE OR REPLACE VIEW ' || v_view.view_name || '_BK AS ' || v_view.text;
        execute immediate 'CREATE OR REPLACE VIEW ' || v_view.view_name || ' AS ' || replace(v_view.text, 'NOETIX_SYS.N_KFF_MTL_CAT', 'NOETIX_SYS.XXK_MTL_CAT');
        end if;
  end loop;
end;
/

begin
  dbms_output.put_line('Recompiling invalid Views under NOETIX_SYS Schema');
  dbms_output.put_line('-------------------------------------------------');
  dbms_output.put_line('Remove Invalid Noetix Views (that are not set to be ommitted)');
  for v_SQL in (select 'ALTER VIEW NOETIX_SYS.' || do.object_name || ' COMPILE' text
                  from dba_objects do
                 where do.status = 'INVALID'
                   and do.OWNER = 'NOETIX_SYS'
                   and do.OBJECT_TYPE = 'VIEW'
                   and do.OBJECT_NAME not in
                       (select view_name
                          from n_views
                         where nvl(omit_flag, 'N') = 'N')) loop
    dbms_output.put_line('  ' || v_SQL.Text);
    execute immediate v_SQL.Text;
  end loop;
  dbms_output.put_line('END');
end;
/

COMMIT;

@utlspoff

*- Now Noetix owns the code for this bit and as it's going to be distinct to every individual system (as it includes references to ID's) you'll need to do a bit of detective work and work it out yourself. I'll tell you how below.


First of all do a search for the object N_KFF_Mtl_Cat in all the .SQL files in your install directory. You shouldn't find many, the one you're after is named something like N_KFF_Mtl_Cat_pkg.sql, open the file and then scroll down to the line;

INSERT  /*+ APPEND */ INTO N_KFF_Mtl_Cat

You'll see that that the basis for this insert statement is a select statement. You need to take this select statement, take the columns in the table and re-format the SQL to return all the columns as specified in table.

When you've done this you'll notice that the column in the source table STRUCTURE_ID is mapping to both the STRUCTURE_ID and STRUCTURE_NAME in the view. If you scroll down quite a bit further you'll notice that there is also an UPDATE statement;

UPDATE N_KFF_Mtl_Cat dtbl
   SET dtbl.STRUCTURE_NAME =

You need to extract this UPDATE statement and put it into the view you're building (as the STRUCTURE_NAME) column.

In the end you'll have a piece of SQL that you can drop into the script above. If everything works when you run the script there will be no (well, the same number as you started with!) invalid views.

Noetix: New view template INV_Material_Status_History

INVG0_Material_Status_History

Using Oracle's Process Manufacturing (OPM) module was part of Oracle e-Business Suite is never easy; there are so few users of process manufacturing - as opposed to it's much more commonly used bigger brother discrete manufacturing - that we often get left out.

(What's the difference between Process and Discrete manufacturing? Discrete manufacturing makes individual items (Cars, TV sets, iPads, etc) these are either complete or not complete while Process Manufacturing feeds ingredients into a Process until they are all used up (Bread, Soft Drinks, Pharmaceuticals, etc). You would be amazed at how many times I have had to explain this to various companies!)

The issue has arisen because as part of our month-end processes in Finance we look at the INV_Period_Close_Details view and get our total stock on-hand. The problem is that we need to see the Material Status at the point in time the period was closed as, obviously, items in Quarantine (or Rejected) status have a value of zero whereas items which are Approved have a saleable value.

(We have already customised done some heavy customisation in this area as, unbelievably, several key fields are missing which make *all* reports meaningless - for example the Inventory Type; in what case would you not wish to differentiate between Finished Goods sitting in the Warehouse and Raw Materials?!)

We had the option of adding the table we needed (INV.MTL_Material_Status_History) into the INV_Period_Close_Details template but the more we looked at it the more other uses we could see for the data (i.e. lot genealogy) and we couldn't see a way of marrying up all the different requirements without creating a new, custom, view.

Here is the list of columns we selected (I've removed the A$ and Z$ columns and truncated the item flexfield);

chart_of_accounts_name
item$... <item flexfields>
item$item
item_description
item_long_description
locator$inventory_location_id
locator$organization_id
locator$sv$stock_locator
locator$stock_locator
lot_number
master_organization
master_organization_name
material_status
material_status_code
organization_code
organization_id
organization_name
primary_unit_of_measure
status_end_date
status_start_date
subinventory

The script necessary to create the view is available in Google Docs here. Save this as an SQL file in your Noetix directory and call it from WNOETXU2.sql. It seems to me that this is something the NoetixViews Product should actually include as there is a clear, demonstrable, use for it - the first question any auditor will ask when you show them your month end stock levels is how much it's worth!

I have not had a great deal of luck getting the changes I've been pushing for into the product. If anyone has any suggestions I'd be happy to hear them!

NOTE: While this view has passed IT-testing it is currently with the business for end-user testing. If any issues come up I'll update this blog post with details as well as a fix.

Noetix: Comparing NOETIX_SYS Schemas

As I'm currently working on upgrading our Noetix installation from 6.0.2 to 6.1 I've been writing some SQL to do a comparison between our new 6.1 build (on a test server) and our current production 6.1 environment. At the moment the only things I'm comparing are the views (looking for new and removed), and columns looking for new and removed and where the source code has changed.

In order to use this script you need to have created a database link in your test environment called NOETIX_TEST that points to your production environment.

The SQL is;

declare
  v_Version varchar2(10) := '0.9.5';
  -- 0.9.2 Added last updated by, last updated date columns to the column source comparrison
  -- 0.9.3 Removed A$ column from the REMOVED/ADDED query, "Query position" no longer a separate line in report,
  --       additional highlighting of viewname added, Local/Remote DB name now used rather than PROD and TEST
  -- 0.9.4 Adding in a check for WHERE clause changes
  -- 0.9.5 For some reason when a column changed type (i.e. COL to EXPR) I'd decided not to report it. Fixed.

  v_ShowDetails         varchar2(1) := 'T';
  v_ShowDangerItemsOnly varchar2(1) := 'F';

  v_LocalDB  V$DATABASE.NAME%TYPE;
  v_RemoteDB V$DATABASE.NAME%TYPE;

  v_ViewCount   integer;
  v_ChangeCount integer;
  v_OldViewName n_views.view_name%TYPE;
begin
  dbms_output.put_line('Noetix Schema Comparison ' ||
                       TO_CHAR(SYSDATE, 'DD-MON-YYYY') || ' (V' ||
                       v_Version || ')');
  dbms_output.put_line('---------------------------------------------');
  for v_Database in (SELECT NAME FROM V$DATABASE@NOETIX_TEST) loop
    v_RemoteDB := v_Database.name;
    dbms_output.put_line('(Remote) Database = ' || v_Database.name);
  end loop;
  for v_Database in (SELECT NAME FROM V$DATABASE) loop
    v_LocalDB := v_Database.name;
    dbms_output.put_line('(Local) Database = ' || v_Database.name);
  end loop;
  dbms_output.put_line(' ');
  dbms_output.put_line('SECTION 001- CHECKING VIEWS');
  dbms_output.put_line('---------------------------');
  if v_ShowDangerItemsOnly = 'F' then
    dbms_output.put_line('New Views');
    dbms_output.put_line('---------');
    v_ChangeCount := 0;
    for v_LocalView in (SELECT DISTINCT nv.View_Name
                          FROM N_Views NV
                         WHERE EXISTS
                         (SELECT 'X'
                                  FROM All_Views AV
                                 WHERE av.Owner = USER
                                   AND av.view_name = UPPER(nv.View_name))
                         ORDER BY nv.View_Name) loop
      SELECT COUNT(*)
        INTO v_ViewCount
        FROM N_Views@NOETIX_TEST NV
       WHERE EXISTS (SELECT 'X'
                FROM All_Views@NOETIX_TEST AV
               WHERE av.Owner = USER
                 AND av.view_name = UPPER(nv.View_name))
         AND nv.view_name = v_LocalView.view_name;
      if v_ViewCount = 0 then
        if v_ShowDetails = 'T' then
          dbms_output.put_line(v_LocalView.view_name);
        end if;
        v_ChangeCount := v_ChangeCount + 1;
      end if;
    end loop;
    dbms_output.put_line('-- Total New View Count = ' ||
                         TO_CHAR(v_ChangeCount));
    dbms_output.put_line(' ');
  end if;
  dbms_output.put_line('Removed Views');
  dbms_output.put_line('-------------');
  v_ChangeCount := 0;
  for v_RemoteView in (SELECT DISTINCT nv.View_Name
                         FROM N_Views@NOETIX_TEST NV
                        WHERE EXISTS
                        (SELECT 'X'
                                 FROM All_Views@NOETIX_TEST AV
                                WHERE av.Owner = USER
                                  AND av.view_name = UPPER(nv.View_name))
                        ORDER BY nv.View_Name) loop
    SELECT COUNT(*)
      INTO v_ViewCount
      FROM N_Views NV
     WHERE EXISTS (SELECT 'X'
              FROM All_Views AV
             WHERE av.Owner = USER
               AND av.view_name = UPPER(nv.View_name))
       AND nv.view_name = v_RemoteView.view_name;
    if v_ViewCount = 0 then
      if v_ShowDetails = 'T' then
        dbms_output.put_line(v_RemoteView.view_name);
      end if;
      v_ChangeCount := v_ChangeCount + 1;
    end if;
  end loop;
  dbms_output.put_line('-- Total Removed View Count = ' ||
                       TO_CHAR(v_ChangeCount));
  dbms_output.put_line(' ');
  dbms_output.put_line('SECTION 002- CHECKING COLUMNS');
  dbms_output.put_line('-----------------------------');
  dbms_output.put_line('Views With Column Changes');
  dbms_output.put_line('-------------------------');
  v_ChangeCount := 0;
  v_OldViewname := '@';
  for v_Column in (select atc.table_name view_name,
                          'ADDED' change_type,
                          atc.column_name
                     from all_tab_columns ATC
                    where atc.owner = user
                      and atc.COLUMN_NAME NOT LIKE 'A$%'
                      and atc.COLUMN_NAME <> 'Z$$_________________________'
                      and atc.TABLE_NAME IN
                          (SELECT UPPER(nv.view_name)
                             FROM N_Views@NOETIX_TEST nv
                            WHERE NVL(nv.omit_flag, 'N') = 'N')
                      AND NOT EXISTS
                    (SELECT 'X'
                             FROM all_tab_columns@NOETIX_TEST ATC1
                            WHERE ATC.OWNER = ATC1.OWNER
                              AND ATC.TABLE_NAME = ATC1.TABLE_Name
                              AND ATC.COLUMN_NAME = ATC1.COLUMN_NAME)
                      AND v_ShowDangerItemsOnly = 'F'
                   UNION
                   select atc.table_name view_name,
                          'REMOVED',
                          atc.column_name
                     from all_tab_columns@NOETIX_TEST ATC
                    where atc.owner = user
                      and atc.COLUMN_NAME NOT LIKE 'A$%'
                      and atc.COLUMN_NAME <> 'Z$$_________________________'
                      and atc.TABLE_NAME IN
                          (SELECT UPPER(nv.view_name)
                             FROM N_Views nv
                            WHERE NVL(nv.omit_flag, 'N') = 'N')
                      AND NOT EXISTS
                    (SELECT 'X'
                             FROM all_tab_columns ATC1
                            WHERE ATC.OWNER = ATC1.OWNER
                              AND ATC.TABLE_NAME = ATC1.TABLE_Name
                              AND ATC.COLUMN_NAME = ATC1.COLUMN_NAME)
                    ORDER BY 1, 2, 3) loop
    v_ChangeCount := v_ChangeCount + 1;
    if v_OldViewname <> v_Column.view_name then
      v_OldViewname := v_Column.view_name;
      dbms_output.put_line(v_Column.view_name);
    end if;
    if v_ShowDetails = 'T' then
      dbms_output.put_line('  ' || INITCAP(v_Column.Change_Type) || ' ' ||
                           v_Column.Column_Name);
    end if;
  end loop;
  dbms_output.put_line('-- Total Column Changes Count = ' ||
                       TO_CHAR(v_ChangeCount));
  dbms_output.put_line(' ');
  dbms_output.put_line('Columns With Modified Source');
  dbms_output.put_line('----------------------------');
  dbms_output.put_line('NOTE: This is a comparison of the Noetix tables, not the view source themselves');
  v_ChangeCount := 0;
  v_OldViewname := '@';
  for v_Column in (SELECT nv.View_Name Test_View_Name,
                          nv.query_position Test_Query_Position,
                          nv.column_name Test_column_name,
                          nv.column_type Test_Column_Type,
                          NVCT.last_updated_by Test_Last_Updated_By,
                          NVCT.Last_update_Date Test_Last_Udpated_Date,
                          NVL(nv.table_alias, '') || '.' ||
                          Replace(Replace(Replace(nv.column_expression,
                                                  '  ',
                                                  ' '),
                                          chr(13),
                                          ''),
                                  chr(10),
                                  '') Test_Column_Detail,
                          nv1.View_Name Prod_View_Name,
                          nv1.query_position Prod_Query_Position,
                          nv1.column_name Prod_column_name,
                          nv1.column_type Prod_Column_Type,
                          NVCT1.last_updated_by Prod_Last_Updated_By,
                          NVCT1.Last_update_Date Prod_Last_Udpated_Date,
                          NVL(nv1.table_alias, '') || '.' ||
                          Replace(Replace(Replace(nv1.column_expression,
                                                  '  ',
                                                  ' '),
                                          chr(13),
                                          ''),
                                  chr(10),
                                  '') Prod_Column_Detail,
                          Replace(Replace(Replace(nv1.column_expression,
                                                  '  ',
                                                  ''),
                                          chr(13),
                                          ''),
                                  chr(10),
                                  '') || ' > ' ||
                          Replace(Replace(Replace(nv.column_expression,
                                                  '  ',
                                                  ''),
                                          chr(13),
                                          ''),
                                  chr(10),
                                  '') Change_Pattern
                     FROM N_View_Columns NV
                     JOIN N_View_Column_Templates NVCT
                       ON NV.T_COLUMN_ID = NVCT.t_Column_Id
                     JOIN n_view_columns@NOETIX_TEST NV1
                       ON nv1.view_name = nv.view_name
                      AND nv1.column_name = nv.column_name
                      AND nv1.query_position = nv.query_position
                      AND UPPER(nv1.View_name) IN
                          (SELECT AV.VIEW_NAME
                             FROM All_Views AV
                            WHERE AV.Owner = USER)
                      AND NVL(NV1.OMIT_FLAG, 'N') = 'N'
                      AND NVL(nv1.column_expression, 'NULL') NOT LIKE
                          '%Copyright Noetix Corporation%'
                     JOIN N_View_Column_Templates@NOETIX_TEST NVCT1
                       ON NV1.column_label = nvct1.column_label
                      AND nv1.view_label = nvct1.view_label
                      AND nv1.query_position = nvct1.query_position
                    WHERE 1 = 1
                      AND UPPER(NV.View_Name) NOT LIKE '%_BASE'
                      AND nv.column_type NOT IN ('GENEXPR')
                      AND NVL(NV.OMIT_FLAG, 'N') = 'N'
                      AND NVL(nv.column_expression, 'NULL') NOT LIKE
                          '%Copyright Noetix Corporation%'
                      AND UPPER(nv.View_name) IN
                          (SELECT AV.VIEW_NAME
                             FROM All_Views@NOETIX_TEST AV
                            WHERE AV.Owner = USER)
                      AND NVL(lower(CASE
                                      WHEN nv1.COLUMN_TYPE = 'EXPR' THEN
                                       ''
                                      ELSE
                                       NVL(nv1.table_alias, '') || '.'
                                    END || Replace(Replace(Replace(nv1.column_expression,
                                                                   ' ',
                                                                   ''),
                                                           chr(13),
                                                           ''),
                                                   chr(10),
                                                   '')),
                              'NULL') <> NVL(lower(CASE
                                                     WHEN nv.COLUMN_TYPE = 'EXPR' THEN
                                                      ''
                                                     ELSE
                                                      NVL(nv.table_alias, '') || '.'
                                                   END || Replace(Replace(Replace(nv.column_expression,
                                                                                  ' ',
                                                                                  ''),
                                                                          chr(13),
                                                                          ''),
                                                                  chr(10),
                                                                  '')),
                                             'NULL')
                    ORDER BY nv.View_Name, nv.query_position, nv.column_name) loop
    --if v_Column.Test_Column_Type = v_Column.Prod_Column_Type then
      v_ChangeCount := v_ChangeCount + 1;
      if v_OldViewname <> v_Column.Test_View_Name then
        v_OldViewname := v_Column.Test_View_Name;
        dbms_output.put_line('===== ' || UPPER(v_Column.Test_View_Name) ||
                             ' =====');
      end if;
      dbms_output.put_line('  ' || v_Column.Test_Column_Name || ' (' ||
                           v_Column.Test_Column_Type || ' Query Position ' ||
                           v_Column.Test_Query_Position || ')');
      if v_ShowDetails = 'T' then
        dbms_output.put_line('    ' || v_LocalDB || '=' ||
                             v_Column.Test_Column_Detail || ' (' ||
                             v_Column.Test_Last_updated_By || ', ' ||
                             TO_CHAR(v_Column.Test_Last_Udpated_Date,
                                     'DD-MON-YYYY') || ')');
        dbms_output.put_line('    ' || v_RemoteDB || '=' ||
                             v_Column.Prod_Column_Detail || ' (' ||
                             v_Column.Prod_Last_updated_By || ', ' ||
                             TO_CHAR(v_Column.Prod_Last_Udpated_Date,
                                     'DD-MON-YYYY') || ')');
      end if;
    --end if;
  end loop;
  dbms_output.put_line('-- Total Modified Column Count = ' ||
                       TO_CHAR(v_ChangeCount));
  dbms_output.put_line('');
  dbms_output.put_line('SECTION 003- CHECKING VIEW COMPOSITION');
  dbms_output.put_line('--------------------------------------');
  dbms_output.put_line('Changed Where Clause Conditions');
  dbms_output.put_line('------------------------------');
  v_ChangeCount := 0;
  for v_WhereChange in (select 'Added' change_direction,
                               NVW.view_name,
                               nvw.query_position,
                               nvw.where_clause_position,
                               nvw.where_clause,
                               nvwt.last_updated_by,
                               nvwt.last_update_date
                          FROM N_View_Wheres NVW
                          JOIN n_View_Where_Templates NVWT
                            ON NVW.View_Label = NVWT.View_Label
                           AND nvw.query_position = nvwt.query_position
                           AND nvw.where_clause_position =
                               nvwt.where_clause_position
                          JOIN All_Views AV
                            ON AV.owner = USER
                           AND AV.VIEW_NAME = UPPER(NVW.view_name)
                         WHERE 1 = 1
                           AND UPPER(NVW.View_Name) NOT LIKE '%_BASE'
                           AND NVL(NVW.Omit_Flag, 'N') = 'N'
                           AND NOT EXISTS
                         (SELECT 'X'
                                  FROM N_View_Wheres@NOETIX_TEST NVW_R
                                 WHERE NVW.VIEW_NAME = NVW_R.view_name
                                   AND NVW.query_position =
                                       NVW_R.query_position
                                   AND NVW.where_clause = NVW_R.where_clause)
                           AND EXISTS
                         (SELECT 'X'
                                  FROM All_Views@NOETIX_TEST AV1
                                 WHERE AV1.owner = USER
                                   AND AV1.VIEW_NAME = UPPER(NVW.view_name))
                        UNION
                        SELECT 'Removed',
                               NVW.view_name,
                               nvw.query_position,
                               nvw.where_clause_position,
                               nvw.where_clause,
                               nvwt.last_updated_by,
                               nvwt.last_update_date
                          FROM N_View_Wheres@NOETIX_TEST NVW
                          JOIN n_View_Where_Templates@NOETIX_TEST NVWT
                            ON NVW.View_Label = NVWT.View_Label
                           AND nvw.query_position = nvwt.query_position
                           AND nvw.where_clause_position =
                               nvwt.where_clause_position
                         WHERE 1 = 1
                           AND UPPER(NVW.View_Name) NOT LIKE '%_BASE'
                           AND NVL(NVW.Omit_Flag, 'N') = 'N'
                           AND EXISTS
                         (SELECT 'X'
                                  FROM All_Views@NOETIX_TEST AV
                                 WHERE AV.owner = USER
                                   AND AV.VIEW_NAME = UPPER(NVW.view_name))
                           AND NOT EXISTS
                         (SELECT 'X'
                                  FROM N_View_Wheres NVW_R
                                 WHERE NVW.VIEW_NAME = NVW_R.view_name
                                   AND NVW.query_position =
                                       NVW_R.query_position
                                   AND NVW.where_clause = NVW_R.where_clause)
                           AND EXISTS
                         (SELECT 'X'
                                  FROM All_Views AV1
                                 WHERE AV1.owner = USER
                                   AND AV1.VIEW_NAME = UPPER(NVW.view_name))
                         ORDER BY 2, 3, 4, 5) loop
    v_ChangeCount := v_ChangeCount + 1;
    if v_OldViewname <> v_WhereChange.View_Name then
      v_OldViewname := v_WhereChange.View_Name;
      dbms_output.put_line('===== ' || UPPER(v_WhereChange.View_Name) ||
                           ' =====');
    end if;
    if v_ShowDetails = 'T' then
      dbms_output.put_line('    Query Position ' ||
                           v_WhereChange.Query_Position || ' ' ||
                           InitCap(v_WhereChange.Change_Direction) || ': ' ||
                           v_WhereChange.Where_Clause || ' (Position = ' ||
                           TO_CHAR(v_WhereChange.Where_Clause_Position) || ', ' ||
                           v_WhereChange.Last_updated_By || ', ' ||
                           TO_CHAR(v_WhereChange.Last_Update_Date,
                                   'DD-MON-YYYY') || ')');
    end if;
  end loop;
  dbms_output.put_line('-- Total Where Clause Changes = ' ||
                       TO_CHAR(v_ChangeCount));
  dbms_output.put_line(' ');

  rollback;
end;

This code is available here (via Google Docs).

NOTE: I've updated the SQL to do some additional comparisons and format the comparisons completely differently.