View lawprod.MSCM.INSTRUMENT_HISTORY_VIEW Generated by
SchemaSpy
Legend:
Primary key columns
Columns with indexes
Implied relationships
Excluded column relationships
< n > number of related tables
 
Column Type Size Nulls Auto Default Children Parents Comments
DT_TIME date 7
INSTRUMENT_ID varchar2 32  √  null
COMPANY_NO number 4
COMPANY.COMPANY_NO Implied Constraint R
COMPANY_NAME varchar2 30  √  null
COMPANY_NO_NAME varchar2 73  √  null
LOCATION_ID number 22
LOCATION.LOCATION_ID Implied Constraint R
LOCATION_CODE varchar2 5
LOCATION_NAME varchar2 50
LOCATION_CODE_NAME varchar2 58  √  null
ACTIVITY varchar2 19  √  null
ID_TYPE varchar2 18  √  null
ID_NBR varchar2 16  √  null
USER_NAME varchar2 112  √  null

Analyzed at Wed May 18 12:10 CDT 2011

View Definition:
SELECT ITL.DT_TIME, INSTRUMENT_ID, C.COMPANY_NO, C.DESCRIPTION COMPANY_NAME, concat(concat(C.COMPANY_NO, ' - '), C.DESCRIPTION) COMPANY_NO_NAME, L.LOCATION_ID, L.LOCATION_CODE, L.NAME LOCATION_NAME, concat(concat(L.LOCATION_CODE, ' - '), L.NAME) LOCATION_CODE_NAME, DECODE(ITL.ACTIVITY, 4, 'Assembly', 8, 'Sterilization', 7, 'Picking', 5, 'Transfer', 2, 'Activate', 3, 'Inactivate', 9, 'Edit Mfg. Ser. Nbr.') ACTIVITY, DECODE(ITL.ACTIVITY, 4, 'ASID', 8, 'ASID', 7, 'ASID', 5, DECODE(ID_NBR, '', '', 'ASID'), 2, '', 3, (DECODE(ID_NBR, '', '', 'ASID')), 9, 'Old Mfg. Ser. Nbr.') ID_TYPE, DECODE(ITL.ACTIVITY, 9, OLD_MFG_SR_NBR, ID_NBR) ID_NBR, concat(concat(U.LAST_NAME, ', '), U.FIRST_NAME) USER_NAME FROM INSTRUMENT_TRACK_LOG ITL, INSTRUMENT_TRACK IT, LOCATION L, COMPANY C, USERS U WHERE ITL.INSTRUMENT_TRACK_ID = IT.INSTRUMENT_TRACK_ID AND L.LOCATION_ID = ITL.LOCATION_ID AND C.COMPANY_NO = L.COMPANY_NO AND U.USER_ID = ITL.USER_ID AND IT.STATUS='A'
 
Possibly Referenced Tables/Views:
ASSEMBLY COMPANY INSTRUMENT_TRACK INSTRUMENT_TRACK_LOG LOCATION TRANSFER USERS 


Close relationships: