Latest news

iSocialCare - Integrated Care Management

i-SocialCare is a suite of residential care home software developed in consultation with care home manager and local authority input.

Updated 21 March 2012

Tracking Lot Genealogy in Oracle without Noetix

Using Noetix works ok on a single lot, but performance issues arise when its part of a query for many lots. So its back to the Oracle tables.

Updated 21 March 2012

Noetix consultancy, customisation and implementation

Discover how Spis Tech Ltd can help you with your Noetix Implementations

Updated 05 December 2011

Tracking Lot Genealogy in Oracle with Noetix

Lot Genealogy in oracle Process Manufacturing provides the ability to track the progress of a lot through its life to the point where it becomes a finished good and is sold to a customer.

Updated 31 October 2011

What sort of company benefits from better data storage and access?

What sort of company doesn't? Spis Tech Ltd can make your data more accessible, more meaningful and more effective. Read more about our Services.

Updated 28 October 2011

Blog Categories: All | Noetix | Oracle

Tracking Lot Genealogy in Oracle with Noetix

Issued 31 October 2011 Steve Ritchie Tags: OracleNoetix 

Lot Genealogy in oracle Process Manufacturing provides the ability to track the progress of a lot through its life to the point where it becomes a finished good and is sold to a customer.

Lot genealogy in Oracle Process Manufacturing allows a lot, or a batch of manufactured items, to be tracked through the manufacturing, packing and distribution processes right up to the point itís sold to the final customer Ė the end user.

Tracking lot genealogy is essential for those occasions where a product recall must be made. In the pharmaceutical industry, for instance, imagine a particular consignment of ingredients, which had been used in the manufacture of a certain medicine, had been contaminated or damaged in some way. The medicine produced using that damaged ingredient could well have a harmful effect on the person prescribed the drug. By tracking lot genealogy you can locate every single pack of the contaminated tablets, and make product recalls quickly and hopefully without a health disaster, and the ensuing PR and legal issues that could arise.

Despite claims to the contrary, there is no Noetix lot genealogy view in either R11 or R12, so we have to make this work for ourselves.

R11 Coding

We are pairing Material Receipt and Material Issue transactions with matching DOC_ID (GMI.IC_TRAN_PND). In Noetix terms the view GMIG0_Inv_Transactions can be used if the column DOC_ID is exposed.

For improved performance only the first of the four unions is required, so maybe a lot genealogy version of the view can be used. Check with your IT support department!

First, letís consider the query. I like to define the query using the Oracle WITH clause. We have a query which lists the product and ingredient matched by DOC_ID for all records in the database.

with ProductQuery as



Product Columns including DOC_ID.....




Product.doc_id...... +Other Columns

from GMIG0_Inv_Transactions_LG Product where

and Product.document_type_code = 'PROD'

and Product.LINE_TYPE = 'Material Receipt'

and Product.doc_id is not null

...... ) Prod )


IngredientQuery as



Ingredient Columns including DOC_ID.....





Ingred.doc_id....... +Other Columns

from GMIG0_Inv_Transactions_LG Ingred where

and Ingred.document_type_code = 'PROD'

and Ingred.LINE_TYPE = 'Material Issue'

and Ingred.doc_id is not null

...... ) Ingred )

Now, to execute the query......


CONNECT_BY_ROOT ProductQuery."Product Lot",




SYS_CONNECT_BY_PATH(ProductQuery."Product Lot", '/') PATH,



from ProductQuery, IngredientQuery where ProductQuery."Product Document ID" = IngredientQuery."Ingred Document ID"

start with ProductQuery."Product Lot" = :ParameterLotNumber

connect by NOCYCLE ProductQuery."Product Lot" = PRIOR IngredientQuery."Ingred Lot"

By passing the Lot Number as :ParameterLotNumber, we can specify the starting point in the hierarchy. Placement of the PRIOR allows the direction to be set to either down or up the hierarchy.

R12 coding

In R12 installations, a different Noetix view should be selected and the query should use transaction type, rather than line type.

In the real world, I include a query which will optionally return all orders and on-hand inventory details for inventory types which are saleable/shippable. This forms the basis for a product recall report.

From there, itís just about speed of communication with the recipients of those contaminated tabletsÖ


Oracle WITH Clause

Oracle Hierarchical Queries

Lot Genealogy

Spis Tech provide Oracle E-Business, Application and Database Consultancy with expertise in NoetixViews, Microsoft Reporting Services, SAP Business Objects and web technologies.

©2010 SPIS TECH Limited. Registered in England. Company Number 070911557