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,
IDG.BARREL_LENGTH,
GC.CODE AS WEAPON_TYPE_CODE,
GC.DESCRIPTION AS WEAPON_TYPE_DESC,
CALC.CODE AS CALIBER_CODE,
CALC.DESCRIPTION AS CALIBER_DESC,
AC.CODE AS ACTION_CODE,
AC.DESCRIPTION AS ACTION_DESC,
CASE WHEN EXISTS(SELECT 1 FROM ITEM_INSTANCES II WHERE II.DESCRIPTOR_ID = ID.DESCRIPTOR_ID AND SERIALIZED_HINT_FLAG = 'Y') THEN ejs_get_y ELSE ejs_get_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
INNER JOIN ITEM_GUN_DESCRIPTORS IDG ON ID.DESCRIPTOR_ID = IDG.DESCRIPTOR_ID
LEFT OUTER JOIN EJS_CODES GC ON IDG.WEAPON_TYPE_CODE = GC.CODE AND IDG.WEAPON_TYPE_CODE_TYPE = GC.CODE_TYPE
LEFT OUTER JOIN EJS_CODES CALC ON IDG.CALIBER_CODE = CALC.CODE AND IDG.CALIBER_CODE_TYPE = CALC.CODE_TYPE
LEFT OUTER JOIN EJS_CODES AC ON IDG.ACTION_CODE = AC.CODE AND IDG.ACTION_CODE_TYPE = AC.CODE_TYPE
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'
