VIEW: APPS.MTL_GRD_STS_HISTORY_V

Source

View Text - Preformatted

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 , NULL group_id , NULL locator_id , NULL subinventory_code , NULL pending_status 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 , sts.group_id group_id , sts.locator_id locator_id , sts.subinventory subinventory_code , sts.pending_status pending_status FROM (select status_update_id , organization_id , inventory_item_id , lot_number , serial_number , 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 , lpn_id order by creation_date) previous_status_id , last_update_date status_update_date , last_updated_by , update_reason_id , group_id , locator_id , zone_code subinventory , pending_status 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(+) AND sts.previous_status_id <> sts.status_id )

View Text - HTML Formatted

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
, NULL GROUP_ID
, NULL LOCATOR_ID
, NULL SUBINVENTORY_CODE
, NULL PENDING_STATUS
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
, STS.GROUP_ID GROUP_ID
, STS.LOCATOR_ID LOCATOR_ID
, STS.SUBINVENTORY SUBINVENTORY_CODE
, STS.PENDING_STATUS PENDING_STATUS
FROM (SELECT STATUS_UPDATE_ID
, ORGANIZATION_ID
, INVENTORY_ITEM_ID
, LOT_NUMBER
, SERIAL_NUMBER
, 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
, LPN_ID ORDER BY CREATION_DATE) PREVIOUS_STATUS_ID
, LAST_UPDATE_DATE STATUS_UPDATE_DATE
, LAST_UPDATED_BY
, UPDATE_REASON_ID
, GROUP_ID
, LOCATOR_ID
, ZONE_CODE SUBINVENTORY
, PENDING_STATUS
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(+)
AND STS.PREVIOUS_STATUS_ID <> STS.STATUS_ID )