|
|
| 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 |
| VEHICLE_ID |
number |
0 |
|
|
|
|
|
| UNIT_NUMBER |
varchar2 |
255 |
√ |
|
null |
|
|
| VEHICLE_AGENCY_CODE |
varchar2 |
30 |
√ |
|
null |
|
|
| VEHICLE_TYPE |
varchar2 |
100 |
√ |
|
null |
|
|
| VEHICLE_MAKE |
varchar2 |
100 |
√ |
|
null |
|
|
| VEHICLE_MODEL |
varchar2 |
100 |
√ |
|
null |
|
|
| VEHICLE_CATEGORY |
varchar2 |
4000 |
√ |
|
null |
|
|
| VEHICLE_CATEGORY_CODE |
varchar2 |
30 |
√ |
|
null |
|
|
| VEHICLE_CATEGORY_CODE_TYPE |
varchar2 |
30 |
√ |
|
null |
|
|
| VEHICLE_STATUS |
varchar2 |
4000 |
√ |
|
null |
|
|
| TOTAL_MILES |
number |
0 |
√ |
|
null |
|
|
| TOTAL_COST |
number |
0 |
√ |
|
null |
|
|
| COST_PER_MILE |
number |
0 |
√ |
|
null |
|
|
| TOTAL_HOURS_DOWN |
number |
0 |
√ |
|
null |
|
|
| ASSIGNMENT_CODE |
varchar2 |
30 |
√ |
|
null |
|
|
| ASSIGNMENT_DESC |
varchar2 |
4000 |
√ |
|
null |
|
|
| HAS_OPEN_ASSIGNMENTS |
char |
1 |
√ |
|
null |
|
|
Analyzed at Mon Sep 20 21:05 MDT 2021
|
View Definition:
(
SELECT
DV.VEHICLE_ID,
DV.UNIT_NUMBER,
DV.AGENCY_CODE,
(SELECT DESCRIPTION FROM ITEM_SUB_TYPE_CODES IST WHERE IST.TYPE_ID = DV.TYPE_ID),
(SELECT DESCRIPTION FROM ITEM_MAKE_CODES IMC WHERE IMC.MAKE_ID = DV.MAKE_ID),
(SELECT DESCRIPTION FROM ITEM_MODEL_CODES IMC WHERE IMC.MAKE_ID = DV.MAKE_ID AND IMC.MODEL_ID = DV.MODEL_ID),
(SELECT EC.DESCRIPTION FROM EJS_CODES EC WHERE EC.CODE_TYPE=DV.VEH_CATEGORY_CODE_TYPE AND EC.CODE=DV.VEH_CATEGORY_CODE),
DV.VEH_CATEGORY_CODE,
DV.VEH_CATEGORY_CODE_TYPE,
(SELECT EC.DESCRIPTION FROM EJS_CODES EC WHERE EC.CODE_TYPE = DV.STATUS_CODE_TYPE AND EC.CODE = DV.STATUS_CODE),
NVL(EJS_FLEET_MGMT_SEARCH_PK.GET_MILES_HOURS_USED(DV.VEHICLE_ID),0),
NVL(EJS_FLEET_MGMT_SEARCH_PK.GET_TOTAL_COST(DV.VEHICLE_ID),0),
CASE
WHEN EJS_FLEET_MGMT_SEARCH_PK.GET_MILES_HOURS_USED (DV.VEHICLE_ID) = 0 THEN 0
ELSE NVL(EJS_FLEET_MGMT_SEARCH_PK.GET_TOTAL_COST (DV.VEHICLE_ID) / EJS_FLEET_MGMT_SEARCH_PK.GET_MILES_HOURS_USED (DV.VEHICLE_ID),0)
END,
NVL(EJS_FLEET_MGMT_SEARCH_PK.GET_TOTAL_HRS_DOWN(DV.VEHICLE_ID),0),
DV.ASSIGNMENT_CODE,
(SELECT EC.DESCRIPTION FROM EJS_CODES EC WHERE EC.CODE_TYPE = DV.ASSIGNMENT_CODE_TYPE AND EC.CODE = DV.ASSIGNMENT_CODE),
CASE
WHEN (SELECT COUNT(*) FROM DV_ASSIGNMENTS DVA
WHERE DVA.VEHICLE_ID = DV.VEHICLE_ID
AND DVA.ASSIGN_END_DATE IS NULL) = 0
THEN
'N'
ELSE
'Y'
END
FROM DEPARTMENT_VEHICLES DV
)
Possibly Referenced Tables/Views:
