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 CodingWe 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
(
select
Product Columns including DOC_ID.....
from
(select
Product.lot_number,
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
(
select
Ingredient Columns including DOC_ID.....
from
(
select
Ingred.Lot_Number,
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......
select
CONNECT_BY_ROOT ProductQuery."Product Lot",
CONNECT_BY_ISCYCLE,
CONNECT_BY_ISLEAF,
LEVEL,
SYS_CONNECT_BY_PATH(ProductQuery."Product Lot", '/') PATH,
ProductQuery.*,
IngredientQuery.*
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…
References
Oracle WITH Clause
Oracle Hierarchical Queries
Lot Genealogy
