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 without Noetix

Issued 21 March 2012 Steve Ritchie Tags: Oracle 

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.

We discussed Lot genealogy in Oracle Process Manufacturing with Noetix views in tracking-lot-genealogy-in-oracle-with-noetix.php which in a production environment serves the purpose well of retrieving a full genealogy of a given lot in both directions.

However, we have found that performance is not the best and certainly this method is not recommended where, for example, you may be querying a number of lots and have to work out for each lot the final finished item lot.

So, its back off to the Oracle tables in this scenario. Weve created a function which uses a cursor (below) to query the INV.MTL_OBJECT_GENEALOGY table, joined with items, transaction lots and batch headers .We pair these up by joining Product Object IDs with Ingredient Parent Object IDs, setting a start point of the lot parameter and moving down the hierarchy returning only a row where the Ingredient Item Type is a finished product.

So, in code terms were looking at...


cursor c_get_lot is
select "Ingredient Lot Number"
from (with ProductQuery as (select sysb.segment1 "Product Item",

gg.object_id "Product Object ID",

MTLN.Transaction_Id "Product Transaction",

MTLN.TRANSACTION_SOURCE_ID "Product Trans Source",

gg.Parent_Object_Id "Product Parent Object",

MTLN.Lot_Number "Product Lot Number",

BATCH.batch_no "Product Batch Number",

gg.*

from INV.MTL_OBJECT_GENEALOGY gg,

inv.mtl_transaction_lot_numbers MTLN,

inv.mtl_system_items_b SYSB,

GME.GME_BATCH_HEADER BATCH

where 1 = 1

and gg.origin_txn_id = MTLN.TRANSACTION_ID

and MTLN.Inventory_Item_Id = SYSB.Inventory_Item_Id

and MTLN.TRANSACTION_SOURCE_ID = BATCH.Batch_Id

and SYSB.Organization_Id = 999

and BATCH.ORGANIZATION_ID = 999

and MTLN.Organization_Id = 999),



IngredientQuery as (select sysb.segment1 "Ingredient Item Number",

gg.object_id "Ingredient Object ID",

gg.object_type "Ingredient Object Type",

MTLN.Transaction_Id,

MTLN.TRANSACTION_SOURCE_ID,

gg.Parent_Object_Id "Ingredient Parent Object ID",

MTLN.Lot_Number "Ingredient Lot Number",

BATCH.batch_no "Ingredient Batch Number"

from INV.MTL_OBJECT_GENEALOGY gg,

inv.mtl_transaction_lot_numbers MTLN,

inv.mtl_system_items_b SYSB,

GME.GME_BATCH_HEADER BATCH

where 1 = 1

and gg.origin_txn_id = MTLN.TRANSACTION_ID

and MTLN.Inventory_Item_Id = SYSB.Inventory_Item_Id

and MTLN.TRANSACTION_SOURCE_ID = BATCH.Batch_Id

and SYSB.Organization_Id = 999

and BATCH.ORGANIZATION_ID = 999

and MTLN.Organization_Id = 999)



select LEVEL Hierarchy_Level,

CONNECT_BY_ISLEAF,

CONNECT_BY_ROOT ProductQuery."Product Lot Number",

(select II_P.item_type_code from invg0_items II_P

where II_P.item$item =

IngredientQuery."Ingredient Item Number"

and II_P.ORGANIZATION_CODE = '99') "Ingredient Item Type",

IngredientQuery.*,

ProductQuery.*

from ProductQuery, IngredientQuery

where

ProductQuery."Product Object ID" = IngredientQuery."Ingredient Parent Object ID"

and "Ingredient Object Type" = 1

start with ProductQuery."Product Lot Number" = p_lot_number

connect by NOCYCLE ProductQuery."Product Parent Object" = PRIOR IngredientQuery."Ingredient Object ID")

Where "Ingredient Item Type" = 'BULK';

And there you go. Feel free to email comments, questions or for help. steve.ritchie@spistech.co.uk

For the Noetix Lot Genealogy query, I forgot to mention that you should add something in your query so you do not drill down through lots which are packaging and suchlike.. this can cause a bit of a loop in certain circumstances.


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