|
|
| 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 |
| ASSIGN_ID |
number |
38 |
|
|
|
|
|
| ASSIGN_NUMBER |
varchar2 |
20 |
|
|
|
|
|
| ASSIGN_DATE |
date |
7 |
√ |
|
null |
|
|
| ASSIGN_COMMENT |
varchar2 |
1000 |
√ |
|
null |
|
|
| POSTED |
varchar2 |
1 |
√ |
|
null |
|
|
| AGENCY_CODE |
varchar2 |
30 |
√ |
|
null |
|
|
| AGENCY_DESC |
varchar2 |
40 |
√ |
|
null |
|
|
| ASSIGN_CODE |
varchar2 |
30 |
|
|
|
|
|
| ASSIGN_CODE_DESC |
varchar2 |
50 |
√ |
|
null |
|
|
| SHIFT_CODE |
varchar2 |
30 |
√ |
|
null |
|
|
| SHIFT_CODE_DESC |
varchar2 |
50 |
√ |
|
null |
|
|
| ASSIGN_STATUS |
varchar2 |
30 |
|
|
|
|
|
| ASSIGN_STATUS_DESC |
varchar2 |
4000 |
√ |
|
null |
|
|
| ASSIGN_CREATOR_ID |
varchar2 |
100 |
√ |
|
null |
|
|
| ASSIGN_UPDATOR_ID |
varchar2 |
100 |
√ |
|
null |
|
|
| VEHICLE_ID |
number |
38 |
|
|
|
|
| VEHICLES.VEHICLE_ID
|
Implied Constraint R |
|
| MILEAGE_IN |
number |
0 |
√ |
|
null |
|
|
| MILEAGE_OUT |
number |
0 |
√ |
|
null |
|
|
| TOTAL_MILEAGE |
number |
0 |
√ |
|
null |
|
|
| FUEL_USED |
number |
0 |
√ |
|
null |
|
|
| OIL_USED |
number |
0 |
√ |
|
null |
|
|
| FUEL_COST |
number |
0 |
√ |
|
null |
|
|
| OIL_COST |
number |
0 |
√ |
|
null |
|
|
| REPAIR_COST |
number |
0 |
√ |
|
null |
|
|
| TOTAL_COST |
number |
0 |
√ |
|
null |
|
|
| COMMENTS |
varchar2 |
2000 |
√ |
|
null |
|
|
| UNIT_NUMBER |
varchar2 |
255 |
√ |
|
null |
|
|
| VIN |
varchar2 |
20 |
√ |
|
null |
|
|
| YEAR |
number |
38 |
√ |
|
null |
|
|
| VEHICLE_MAKE_CODE |
varchar2 |
255 |
√ |
|
null |
|
|
| VEHICLE_MAKE_DESC |
varchar2 |
255 |
√ |
|
null |
|
|
| VEHICLE_MODEL_CODE |
varchar2 |
255 |
√ |
|
null |
|
|
| VEHICLE_MODEL_DESC |
varchar2 |
255 |
√ |
|
null |
|
|
| LICENSE_NUMBER |
varchar2 |
255 |
√ |
|
null |
|
|
| DESCRIPTION |
varchar2 |
80 |
√ |
|
null |
|
|
Analyzed at Mon Sep 20 21:05 MDT 2021
|
View Definition:
SELECT
A.ASSIGN_ID,
A.ASSIGN_NUMBER,
A.ASSIGN_DATE,
A.ASSIGN_COMMENT,
A.POSTED,
A.AGENCY_CODE,
ACO.AGENCY_DESC,
A.ASSIGN_CODE,
AC.DESCRIPTION AS ASSIGN_CODE_DESC,
A.SHIFT_CODE,
SHFT.DESCRIPTION AS SHIFT_CODE_DESC,
A.ASSIGN_STATUS,
EC.DESCRIPTION AS ASSIGN_STATUS_DESC,
A.CREATOR_ID,
A.UPDATOR_ID,
AV.VEHICLE_ID,
AV.MILEAGE_IN,
AV.MILEAGE_OUT,
NVL(NVL(av.mileage_out,av.mileage_in) - NVL(av.mileage_in,av.mileage_out),0),
AV.FUEL_USED,
AV.OIL_USED,
AV.FUEL_COST,
AV.OIL_COST,
AV.REPAIR_COST,
NVL(AV.FUEL_COST,0) + NVL(AV.OIL_COST,0) + NVL(AV.REPAIR_COST,0) as TOTAL_COST,
AV.COMMENTS,
DV.UNIT_NUMBER,
DV.VIN,
DV.YEAR,
DV.VEHICLE_MAKE_CODE,
VMA.VEHICLE_MAKE_DESC,
DV.VEHICLE_MODEL_CODE,
VMO.VEHICLE_MODEL_DESC,
DV.LICENSE_NUMBER,
DV.DESCRIPTION
FROM ASSIGNMENTS A
INNER JOIN AGENCY_CODES ACO ON A.AGENCY_CODE = ACO.AGENCY_CODE
INNER JOIN ASSIGNMENT_SHIFT_CODES SHFT ON A.SHIFT_CODE = SHFT.SHIFT_CODE
INNER JOIN ASSIGNMENT_CODES AC ON A.ASSIGN_CODE = AC.ASSIGNMENT_CODE
INNER JOIN EJS_CODES EC ON A.ASSIGN_STATUS = EC.CODE AND A.ASSIGN_STATUS_TYPE = EC.CODE_TYPE
INNER JOIN ASSIGNMENT_VEHICLES AV ON A.ASSIGN_ID = AV.ASSIGN_ID
INNER JOIN DEPARTMENT_VEHICLES DV ON AV.VEHICLE_ID = DV.VEHICLE_ID
LEFT OUTER JOIN VEHICLE_MAKES VMA ON DV.VEHICLE_MAKE_CODE = VMA.VEHICLE_MAKE_CODE
LEFT OUTER JOIN VEHICLE_MODELS VMO ON DV.VEHICLE_MODEL_CODE = VMO.VEHICLE_MODEL_CODE
AND DV.VEHICLE_MAKE_CODE = VMO.VMA_VEHICLE_MAKE_CODE
Possibly Referenced Tables/Views:
