View: MTL_GRD_STS_HISTORY_V

Product:	INV - Inventory
Description:
Implementation/DBA Data:	APPS.MTL_GRD_STS_HISTORY_V

View Text

SELECT GRD.GRADE_UPDATE_ID HISTORY_ID
, GRD.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, GRD.LOT_NUMBER LOT_NUMBER
, NULL SERIAL_NUMBER
, GRD.OLD_GRADE_CODE OLD_VALUE
, GRD.NEW_GRADE_CODE NEW_VALUE
, GRD.GRADE_UPDATE_DATE DATE_STAMP
, 'GRADE CHANGE' ACTION
, TO_NUMBER(GRD.PRIMARY_QUANTITY) PRIMARY_QUANTITY
, TO_NUMBER(GRD.SECONDARY_QUANTITY) SECONDARY_QUANTITY
, GRD.FROM_MOBILE_APPS_FLAG UPDATE_ON_MOBILE
, MTR.REASON_NAME CHANGE_REASON
, GRD.LAST_UPDATED_BY USER_ID
, GRD.ORGANIZATION_ID ORGANIZATION_ID
, 1 ACTION_CODE
FROM MTL_LOT_GRADE_HISTORY GRD
, MTL_TRANSACTION_REASONS MTR
WHERE GRD.UPDATE_REASON_ID = MTR.REASON_ID(+) UNION ALL ( SELECT STS.STATUS_UPDATE_ID HISTORY_ID
, STS.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, STS.LOT_NUMBER LOT_NUMBER
, STS.SERIAL_NUMBER SERIAL_NUMBER
, STS_OLD.STATUS_CODE OLD_VALUE
, STS_NEW.STATUS_CODE NEW_VALUE
, STS.STATUS_UPDATE_DATE DATE_STAMP
, 'STATUS CHANGE' ACTION
, TO_NUMBER(STS.PRIMARY_ONHAND) PRIMARY_QUANTITY
, TO_NUMBER(STS.SECONDARY_ONHAND) SECONDARY_QUANTITY
, STS.FROM_MOBILE_APPS_FLAG UPDATE_ON_MOBILE
, MTR.REASON_NAME CHANGE_REASON
, STS.LAST_UPDATED_BY USER_ID
, STS.ORGANIZATION_ID ORGANIZATION_ID
, 2 ACTION_CODE
FROM (SELECT STATUS_UPDATE_ID
, ORGANIZATION_ID
, INVENTORY_ITEM_ID
, LOT_NUMBER
, SERIAL_NUMBER
, LOCATOR_ID
, ZONE_CODE SUBINVENTORY
, TO_NUMBER(PRIMARY_ONHAND) PRIMARY_ONHAND
, TO_NUMBER(SECONDARY_ONHAND) SECONDARY_ONHAND
, FROM_MOBILE_APPS_FLAG
, STATUS_ID
, LAG(STATUS_ID
, 1
, NULL) OVER (PARTITION BY ORGANIZATION_ID
, INVENTORY_ITEM_ID
, LOT_NUMBER
, SERIAL_NUMBER
, LOCATOR_ID
, ZONE_CODE ORDER BY CREATION_DATE) PREVIOUS_STATUS_ID
, LAST_UPDATE_DATE STATUS_UPDATE_DATE
, LAST_UPDATED_BY
, UPDATE_REASON_ID
FROM MTL_MATERIAL_STATUS_HISTORY ) STS
, MTL_MATERIAL_STATUSES STS_OLD
, MTL_MATERIAL_STATUSES STS_NEW
, MTL_TRANSACTION_REASONS MTR
WHERE STS.STATUS_ID = STS_NEW.STATUS_ID
AND STS.PREVIOUS_STATUS_ID = STS_OLD.STATUS_ID
AND STS.UPDATE_REASON_ID = MTR.REASON_ID(+) )

Columns