|
|
| 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 |
| DESCRIPTOR_ID |
number |
0 |
|
|
|
|
|
| CATEGORY_ID |
number |
0 |
|
|
|
|
|
| CATEGORY_DESC |
varchar2 |
255 |
|
|
|
|
|
| ITEM_TYPE_CODE |
varchar2 |
30 |
|
|
|
|
|
| ITEM_TYPE_DESC |
varchar2 |
255 |
|
|
|
|
|
| ITEM_NUMBER |
varchar2 |
255 |
√ |
|
null |
|
|
| GROUP_FLAG |
varchar2 |
1 |
√ |
|
null |
|
|
| GROUP_NUMBER |
varchar2 |
255 |
√ |
|
null |
|
|
| MAKE_ID |
number |
0 |
√ |
|
null |
|
|
| MAKE_DESC |
varchar2 |
100 |
√ |
|
null |
|
|
| MODEL_ID |
number |
0 |
√ |
|
null |
|
|
| MODEL_DESC |
varchar2 |
100 |
√ |
|
null |
|
|
| COLOR_CODE |
varchar2 |
30 |
√ |
|
null |
|
|
| COLOR_DESC |
varchar2 |
4000 |
√ |
|
null |
|
|
| SEC_COLOR_CODE |
varchar2 |
30 |
√ |
|
null |
|
|
| SEC_COLOR_DESC |
varchar2 |
4000 |
√ |
|
null |
|
|
| SIZE_ID |
number |
0 |
√ |
|
null |
|
|
| SIZE_DESC |
varchar2 |
100 |
√ |
|
null |
|
|
| LINE_ID |
number |
0 |
√ |
|
null |
|
|
| LINE_DESC |
varchar2 |
100 |
√ |
|
null |
|
|
| DESCRIPTION |
varchar2 |
500 |
√ |
|
null |
|
|
| SERVICE_LIFE_MONTHS |
number |
0 |
√ |
|
null |
|
|
| SERVICE_LIFE_YEARS |
number |
0 |
√ |
|
null |
|
|
| SELF_CHECKOUT_FLAG |
varchar2 |
1 |
√ |
|
null |
|
|
| CONSUMABLE_FLAG |
varchar2 |
1 |
√ |
|
null |
|
|
| QUANTITY_ID |
number |
0 |
√ |
|
null |
|
|
| QUANTITY_DESC |
varchar2 |
255 |
√ |
|
null |
|
|
| QUANTITY_AMOUNT |
number |
36,2 |
√ |
|
null |
|
|
| SUB_QUANTITY_ID |
number |
0 |
√ |
|
null |
|
|
| SUB_QUANTITY_DESC |
varchar2 |
255 |
√ |
|
null |
|
|
| SUB_QUANTITY_AMOUNT |
number |
36,2 |
√ |
|
null |
|
|
| AGENCY_CODE |
varchar2 |
30 |
|
|
|
|
|
| AGENCY_DESC |
varchar2 |
40 |
√ |
|
null |
|
|
| ORG_AGENCY_CODE |
varchar2 |
30 |
√ |
|
null |
|
|
| SERIALIZED_HINT_FLAG |
char |
1 |
√ |
|
null |
|
|
Analyzed at Mon Sep 20 21:05 MDT 2021
|
View Definition:
SELECT
ID.DESCRIPTOR_ID,
ID.CATEGORY_ID,
ICC.DESCRIPTION AS CATEGORY_DESC,
ITC.ITEM_TYPE_CODE,
ITC.DESCRIPTION AS ITEM_TYPE_DESC,
ID.ITEM_NUMBER,
ID.GROUP_FLAG,
ID.GROUP_NUMBER,
ID.MAKE_ID,
IM.DESCRIPTION AS MAKE_DESC,
ID.MODEL_ID,
IMO.DESCRIPTION AS MODEL_DESC,
CC.CODE AS COLOR_CODE,
CC.DESCRIPTION AS COLOR_DESC,
SC.CODE AS SEC_COLOR_CODE,
SC.DESCRIPTION AS SEC_COLOR_DESC,
ID.SIZE_ID,
ISZ.DESCRIPTION AS SIZE_DESC,
ID.LINE_ID,
IL.DESCRIPTION AS LINE_DESC,
ID.DESCRIPTION,
ID.SERVICE_LIFE_MONTHS,
ID.SERVICE_LIFE_YEARS,
ID.SELF_CHECKOUT_FLAG,
ID.CONSUMABLE_FLAG,
ID.QUANTITY_ID,
IQC.DESCRIPTION AS QUANTITY_DESC,
ID.QUANTITY_AMOUNT,
ID.SUB_QUANTITY_ID,
SQC.DESCRIPTION AS SUB_QUANTITY_DESC,
ID.SUB_QUANTITY_AMOUNT,
AC.AGENCY_CODE,
AC.AGENCY_DESC,
CASE WHEN ROD.AGENCY_CODE IS NOT NULL THEN ROD.AGENCY_CODE ELSE AC.AGENCY_CODE END AS ORG_AGENCY_CODE,
CASE WHEN EXISTS(SELECT 1 FROM ITEM_INSTANCES II WHERE II.DESCRIPTOR_ID = ID.DESCRIPTOR_ID AND SERIALIZED_HINT_FLAG = 'Y') THEN 'Y' ELSE 'N' END AS SERIALIZED_HINT_FLAG
FROM ITEM_DESCRIPTORS ID
LEFT OUTER JOIN ITEM_MAKE_CODES IM ON ID.MAKE_ID = IM.MAKE_ID
LEFT OUTER JOIN ITEM_MODEL_CODES IMO ON ID.MODEL_ID = IMO.MODEL_ID
LEFT OUTER JOIN ITEM_SIZE_CODES ISZ ON ID.SIZE_ID = ISZ.SIZE_ID
LEFT OUTER JOIN ITEM_LINE_CODES IL ON ID.LINE_ID = IL.LINE_ID
LEFT OUTER JOIN EJS_CODES CC ON ID.COLOR_CODE = CC.CODE AND ID.COLOR_CODE_TYPE = CC.CODE_TYPE
LEFT OUTER JOIN EJS_CODES SC ON ID.SEC_COLOR_CODE = SC.CODE AND ID.SEC_COLOR_CODE_TYPE = SC.CODE_TYPE
LEFT OUTER JOIN ITEM_QUANTITY_CODES IQC ON ID.QUANTITY_ID = IQC.QUANTITY_ID
LEFT OUTER JOIN ITEM_QUANTITY_CODES SQC ON ID.SUB_QUANTITY_ID = SQC.QUANTITY_ID
INNER JOIN ITEM_CATEGORY_CODES ICC ON ID.CATEGORY_ID = ICC.CATEGORY_ID
INNER JOIN ITEM_TYPE_CODES ITC ON ICC.ITEM_TYPE_CODE = ITC.ITEM_TYPE_CODE
INNER JOIN AGENCY_CODES AC ON ID.AGENCY_CODE = AC.AGENCY_CODE
LEFT OUTER JOIN PSEUDO_ORG_DESCRIPTORS POD ON AC.AGENCY_CODE = POD.AGENCY_CODE
LEFT OUTER JOIN PSEUDO_ORG_DESCRIPTORS ROD ON POD.STRUCTURE_ID = ROD.STRUCTURE_ID AND ROD.IS_ROOT = 'Y'
Possibly Referenced Tables/Views:
