Lot Status History View

On Andy’s blog,  http://snippetsandhelp.blogspot.com, he documented his method of creating a material status history view.  I too have had a similar requirement and I think it could be of help to some people if I post the DDL here:

Create a base view:

 CREATE OR REPLACE FORCE VIEW "NOETIX_SYS"."INVG_LOT_STATUS_HIST_BASE" ("A$INVENTORY_ITEM_ID", "A$ZZ__________________________", "CREATION_DATE", "CURRENT_LOT_STATUS_ID", "INVENTORY_ITEM_ID", "LOT_NUMBER", "ORGANIZATION_ID", "PRIMARY_ONHAND", "PRIOR_LOT_STATUS_ID", "REASON_DESCRIPTION", "REASON_NAME", "REASON_UPDATED_BY", "SECONDARY_ONHAND")
AS
  SELECT HIST.INVENTORY_ITEM_ID A$Inventory_Item_ID,
    'A$ZZ__________________________Copyright Noetix Corporation 1992-2010' A$ZZ__________________________,
    HIST.CREATION_DATE Creation_Date,
    HIST.STATUS_ID Current_Lot_Status_ID,
    HIST.INVENTORY_ITEM_ID Inventory_Item_ID,
    HIST.LOT_NUMBER Lot_Number,
    HIST.ORGANIZATION_ID Organization_ID,
    HIST.PRIMARY_ONHAND Primary_Onhand,
    LAG(HIST.STATUS_ID, 1, 0) OVER (PARTITION BY HIST.LOT_NUMBER ORDER BY HIST.STATUS_UPDATE_ID ASC NULLS FIRST) Prior_Lot_Status_ID,
    REASN.DESCRIPTION Reason_Description,
    REASN.REASON_NAME Reason_Name,
    FUSER.USER_NAME Reason_Updated_By,
    HIST.SECONDARY_ONHAND Secondary_Onhand
  FROM APPLSYS.FND_USER FUSER,
    INV.MTL_TRANSACTION_REASONS REASN,
    INV.MTL_MATERIAL_STATUS_HISTORY HIST
  WHERE 'Copyright Noetix Corporation 1992-2010' IS NOT NULL
  AND REASN.REASON_ID(+)                          = HIST.UPDATE_REASON_ID
  AND HIST.LAST_UPDATED_BY                        = FUSER.USER_ID ;

Create the material status view:

 CREATE OR REPLACE FORCE VIEW "NOETIX_SYS"."INVG_LOT_STATUS_HISTORY" ("A$ITEM$ITEM", "A$ITEM_TYPE", "A$LOTS$BILLET_HEAT", "A$LOT_NUMBER", "A$ORGANIZATION_NAME", "A$PARENT_LOT_NUMBER", "A$ZZ__________________________", "CHANGE_REASON_CODE", "CHANGE_REASON_DESCRIPTION", "CHANGE_REASON_UPDATED_BY", "CURRENT_LOT_STATUS", "CURRENT_LOT_STATUS_CODE", "GRADE_CODE", "ITEM$ITEM", "ITEM_DESCRIPTION", "ITEM_TYPE", "LOTS$ACTUAL_BILLET_WEIGHT__LBS", "LOTS$BILLET_CROSS_SECTION", "LOTS$BILLET_HEAT", "LOTS$BILLET_LENGTH__IN", "LOTS$CONVERSION_INDICATOR", "LOTS$CUSTOMER_LOT_NUMBER", "LOTS$CUSTOMER_NAME", "LOTS$DESIRED_CYCLE", "LOTS$DEVIATION_NUMBER", "LOTS$DISPOSITION_CODE", "LOTS$INPUT_CHILD_LOT", "LOTS$INPUT_GC_GRANDPARENT", "LOTS$INPUT_PC_GRANDPARENT", "LOTS$INPUT_PARENT_LOT_NUMBER", "LOTS$PBL_SUPPLIER_PARENT_LOT", "LOTS$PREVIOUS_FURNACE_LOAD", "LOT_AGE", "LOT_BEST_BY_DATE", "LOT_CREATION_DATE", "LOT_DESCRIPTION", "LOT_DISABLED_FLAG", "LOT_EXPIRATION_ACTION_CODE", "LOT_EXPIRATION_ACTION_DATE",
  "LOT_EXPIRATION_DATE", "LOT_HOLD_DATE", "LOT_ITEM_SIZE", "LOT_LENGTH", "LOT_LENGTH_UOM", "LOT_MATURITY_DATE", "LOT_NUMBER", "LOT_ORIGIN", "LOT_ORIGINATION_DATE", "LOT_ORIGINATION_TYPE", "LOT_RECYCLED_CONTENT", "LOT_RETEST_DATE", "LOT_STATUS_CREATION_DATE", "LOT_THICKNESS", "LOT_THICKNESS_UOM", "LOT_VOLUME", "LOT_VOLUME_UOM", "LOT_WIDTH", "LOT_WIDTH_UOM", "ORGANIZATION", "ORGANIZATION_NAME", "PARENT_LOT_NUMBER", "PRIMARY_ONHAND", "PRIOR_LOT_STATUS", "PRIOR_LOT_STATUS_CODE", "SECONDARY_ONHAND", "SUPPLIER_LOT_NUMBER", "SUPPLIER_NAME", "TERRITORY_CODE")
AS
  SELECT ITEM.SEGMENT1 A$ITEM$Item,
    ITEM.ITEM_TYPE A$Item_Type,
    LOTS.C_ATTRIBUTE3 A$LOTS$Billet_Heat,
    LOTS.LOT_NUMBER A$Lot_Number,
    XMAP.ORGANIZATION_NAME A$Organization_Name,
    LOTS.PARENT_LOT_NUMBER A$Parent_Lot_Number,
    'A$ZZ__________________________Copyright Noetix Corporation 1992-2010' A$ZZ__________________________,
    HIST.REASON_NAME CHANGE_REASON_CODE,
    HIST.REASON_DESCRIPTION CHANGE_REASON_DESCRIPTION,
    HIST.REASON_UPDATED_BY CHANGE_REASON_UPDATED_BY,
    MATSC.DESCRIPTION Current_Lot_Status,
    MATSC.STATUS_CODE Current_Lot_Status_Code,
    LOTS.GRADE_CODE Grade_Code,
    ITEM.SEGMENT1 ITEM$Item,
    ITEML.DESCRIPTION Item_Description,
    ITEM.ITEM_TYPE Item_Type,
    LOTS.C_ATTRIBUTE7 LOTS$Actual_Billet_Weight__LBS,
    LOTS.C_ATTRIBUTE4 LOTS$Billet_Cross_Section,
    LOTS.C_ATTRIBUTE3 LOTS$Billet_Heat,
    LOTS.C_ATTRIBUTE1 LOTS$Billet_Length__IN,
    LOTS.C_ATTRIBUTE18 LOTS$Conversion_Indicator,
    LOTS.C_ATTRIBUTE9 LOTS$Customer_Lot_Number,
    LOTS.C_ATTRIBUTE11 LOTS$Customer_Name,
    LOTS.C_ATTRIBUTE13 LOTS$Desired_Cycle,
    LOTS.C_ATTRIBUTE10 LOTS$Deviation_Number,
    LOTS.C_ATTRIBUTE5 LOTS$Disposition_Code,
    LOTS.C_ATTRIBUTE2 LOTS$Input_Child_Lot,
    LOTS.C_ATTRIBUTE20 LOTS$Input_GC_Grandparent,
    LOTS.C_ATTRIBUTE19 LOTS$Input_PC_Grandparent,
    LOTS.C_ATTRIBUTE8 LOTS$Input_Parent_Lot_Number,
    LOTS.C_ATTRIBUTE6 LOTS$PBL_Supplier_Parent_Lot,
    LOTS.C_ATTRIBUTE12 LOTS$Previous_Furnace_Load,
    LOTS.AGE Lot_Age,
    LOTS.BEST_BY_DATE Lot_Best_By_Date,
    LOTS.CREATION_DATE Lot_Creation_Date,
    LOTS.DESCRIPTION Lot_Description,
    (
    CASE
      WHEN LOTS.DISABLE_FLAG = 1
      THEN 'Y'
      ELSE 'N'
    END) Lot_Disabled_Flag,
    LOTS.EXPIRATION_ACTION_CODE Lot_Expiration_Action_Code,
    LOTS.EXPIRATION_ACTION_DATE Lot_Expiration_Action_Date,
    LOTS.EXPIRATION_DATE Lot_Expiration_Date,
    LOTS.HOLD_DATE Lot_Hold_Date,
    LOTS.ITEM_SIZE Lot_Item_Size,
    LOTS.LENGTH Lot_Length,
    DECODE(LOTS.LENGTH_UOM,'BAT','BAT','CF','CF','DAY','DAY','DME','DME','DRM', 'DRM','EA','EA','FT','FT','GAL','GAL','GT','GT','HR','Hour','HRC','HRC','HRS', 'Hours','KG','KG','LB','LB','M','M','MIN','Minute','MT','MT','NKL','NKL','PCS' ,'PCS','PEN','PEN','PSI' ,'PSI','QUA','QUA','TON','TON','USD','USD',LOTS.LENGTH_UOM) Lot_Length_UOM,
    LOTS.MATURITY_DATE Lot_Maturity_Date,
    LOTS.LOT_NUMBER Lot_Number,
    LOTS.PLACE_OF_ORIGIN Lot_Origin,
    LOTS.ORIGINATION_DATE Lot_Origination_Date,
    DECODE(LOTS.ORIGINATION_TYPE,'0','Lot Master','1','Production','2', 'Quantities','3','Receiving','4','Inventory','5','Returns','6','Other','7', 'Lot Split','8' ,'Lot Merge','9','Lot Translate',LOTS.ORIGINATION_TYPE) Lot_Origination_Type,
    LOTS.RECYCLED_CONTENT Lot_Recycled_Content,
    LOTS.RETEST_DATE Lot_Retest_Date,
    HIST.CREATION_DATE Lot_Status_Creation_Date,
    LOTS.THICKNESS Lot_Thickness,
    DECODE(LOTS.THICKNESS_UOM,'BAT','BAT','CF','CF','DAY','DAY','DME','DME', 'DRM','DRM','EA','EA','FT','FT','GAL','GAL','GT','GT','HR','Hour','HRC','HRC', 'HRS','Hours','KG','KG','LB','LB','M','M','MIN','Minute','MT','MT','NKL','NKL' ,'PCS','PCS','PEN','PEN','PSI','PSI' ,'QUA','QUA','TON','TON','USD','USD',LOTS.THICKNESS_UOM) Lot_Thickness_UOM,
    LOTS.VOLUME Lot_Volume,
    DECODE(LOTS.VOLUME_UOM,'BAT','BAT','CF','CF','DAY','DAY','DME','DME','DRM', 'DRM','EA','EA','FT','FT','GAL','GAL','GT','GT','HR','Hour','HRC','HRC','HRS', 'Hours','KG','KG','LB','LB','M','M','MIN','Minute','MT','MT','NKL','NKL','PCS' ,'PCS','PEN','PEN','PSI' ,'PSI','QUA','QUA','TON','TON','USD','USD',LOTS.VOLUME_UOM) Lot_Volume_UOM,
    LOTS.WIDTH Lot_Width,
    DECODE(LOTS.WIDTH_UOM,'BAT','BAT','CF','CF','DAY','DAY','DME','DME','DRM', 'DRM','EA','EA','FT','FT','GAL','GAL','GT','GT','HR','Hour','HRC','HRC','HRS', 'Hours','KG','KG','LB','LB','M','M','MIN','Minute','MT','MT','NKL','NKL','PCS' ,'PCS','PEN','PEN', 'PSI','PSI','QUA','QUA','TON','TON','USD','USD',LOTS.WIDTH_UOM) Lot_Width_UOM,
    MPARM.ORGANIZATION_CODE Organization,
    XMAP.ORGANIZATION_NAME Organization_Name,
    LOTS.PARENT_LOT_NUMBER Parent_Lot_Number,
    HIST.PRIMARY_ONHAND Primary_Onhand,
    MATSP.DESCRIPTION Prior_Lot_Status,
    MATSP.STATUS_CODE Prior_Lot_Status_Code,
    HIST.SECONDARY_ONHAND Secondary_Onhand,
    LOTS.SUPPLIER_LOT_NUMBER Supplier_Lot_Number,
    LOTS.VENDOR_NAME Supplier_Name,
    LOTS.TERRITORY_CODE Territory_Code
  FROM INV.MTL_PARAMETERS MPARM,
    NOETIX_SYS.INVG_Lot_Status_Hist_Base HIST,
    INV.MTL_SYSTEM_ITEMS_TL ITEML,
    INV.MTL_MATERIAL_STATUSES_TL MATSP,
    INV.MTL_MATERIAL_STATUSES_TL MATSC,
    INV.MTL_SYSTEM_ITEMS_B ITEM,
    NOETIX_SYS.INVG_INV_ACL_Map_Base XMAP,
    INV.MTL_LOT_NUMBERS LOTS
  WHERE 'Copyright Noetix Corporation 1992-2010' IS NOT NULL
  AND XMAP.ORGANIZATION_ID                        = LOTS.ORGANIZATION_ID
  AND XMAP.APPLICATION_LABEL                      = 'INV'
  AND XMAP.APPLICATION_INSTANCE                   = 'G0'
  AND ITEM.INVENTORY_ITEM_ID                      = LOTS.INVENTORY_ITEM_ID
  AND ITEM.ORGANIZATION_ID                        = LOTS.ORGANIZATION_ID
  AND HIST.LOT_NUMBER                             = LOTS.LOT_NUMBER
  AND HIST.ORGANIZATION_ID                        = LOTS.ORGANIZATION_ID
  AND HIST.INVENTORY_ITEM_ID                      = LOTS.INVENTORY_ITEM_ID
  AND MATSC.STATUS_ID(+)                          = HIST.CURRENT_LOT_STATUS_ID
  AND MATSP.STATUS_ID(+)                          = HIST.PRIOR_LOT_STATUS_ID
  AND MATSC.LANGUAGE(+) LIKE NOETIX_ENV_PKG.GET_LANGUAGE
  AND MATSP.LANGUAGE(+) LIKE NOETIX_ENV_PKG.GET_LANGUAGE
  AND ITEML.INVENTORY_ITEM_ID(+) = ITEM.INVENTORY_ITEM_ID
  AND ITEML.ORGANIZATION_ID(+)   = ITEM.ORGANIZATION_ID
  AND ITEML.LANGUAGE(+) LIKE NOETIX_ENV_PKG.GET_LANGUAGE
  AND ITEM.ORGANIZATION_ID = MPARM.ORGANIZATION_ID ;

Patrick