SELECT II.INSTANCE_ID, IEI.ENTRY_ID, II.INSTANCE_NUMBER, II.DESCRIPTOR_ID, II.ORDER_ID, EC.CODE AS STATUS_CODE, EC.DESCRIPTION AS STATUS_DESC, AC.CODE AS USABILITY_CODE, AC.DESCRIPTION AS USABILITY_DESC, II.BARCODE, II.SERIAL_NUMBER, II.EXPIRATION_DATE, II.NEXT_MAINTENANCE_DATE, II.LIC_MONTH, II.LIC_YEAR, II.LIC_PLATE, II.VIN, II.STATE_TITLE, II.LOCATION_ID, II.LAST_WAREHOUSE_ID, II.LAST_LOCATION_ID, II.SUB_LOCATION_ID, II.OFFICER_ID, EJS_GET_NULL as NEW_OFFICER_ID, II.EMPLOYEE_ID, II.ORGANIZATION_ID, II.PARENT_INSTANCE_ID, IL.DESCRIPTION || EJS_GET_SPACE || ISL.DESCRIPTION, II.CUSTODY_ORDER_ID, II.CUSTODY_EVENT_ID, II.CUSTODY_START_DATE, II.CUSTODY_END_DATE, II.CUSTODY_TYPE_CODE, II.SELF_CHECKOUT_FLAG, IE.LOT_NUMBER, IE.LOT_DESC, II.SERIALIZED_HINT_FLAG, II.DISPOSITION_FLAG, II.DISPOSITION_DATE, II.DISPOSITION_METHOD_CODE, II.DISPOSITION_COMMENT, II.DISPOSITION_USER, II.CUSTODY_AGENCY_CODE, CUSTAC.AGENCY_DESC AS CUSTODY_AGENCY_DESC, II.AGENCY_CODE AS AGENCY_CODE, AC.AGENCY_DESC AS AGENCY_DESC, II.CREATOR_ID, II.UPDATOR_ID FROM ITEM_INSTANCES II INNER JOIN ITEM_DESCRIPTORS ID ON II.DESCRIPTOR_ID = ID.DESCRIPTOR_ID INNER JOIN EJS_CODES EC ON II.STATUS_CODE = EC.CODE AND II.STATUS_CODE_TYPE = EC.CODE_TYPE INNER JOIN EJS_CODES AC ON II.USABILITY_CODE = AC.CODE AND II.USABILITY_CODE_TYPE = AC.CODE_TYPE INNER JOIN ITEM_ENTRY_INSTANCES IEI ON II.INSTANCE_ID = IEI.INSTANCE_ID INNER JOIN ITEM_ENTRIES IE ON IEI.ENTRY_ID = IE.ENTRY_ID INNER JOIN AGENCY_CODES CUSTAC ON II.CUSTODY_AGENCY_CODE = CUSTAC.AGENCY_CODE INNER JOIN AGENCY_CODES AC ON II.AGENCY_CODE = AC.AGENCY_CODE INNER JOIN ITEM_LOCATIONS IL ON II.LOCATION_ID = IL.LOCATION_ID INNER JOIN ITEM_SUB_LOCATIONS ISL ON II.SUB_LOCATION_ID = ISL.SUB_LOCATION_ID UNION SELECT II.INSTANCE_ID, IEI.ENTRY_ID, II.INSTANCE_NUMBER, II.DESCRIPTOR_ID, II.ORDER_ID, EC.CODE AS STATUS_CODE, EC.DESCRIPTION AS STATUS_DESC, AC.CODE AS USABILITY_CODE, AC.DESCRIPTION AS USABILITY_DESC, II.BARCODE, II.SERIAL_NUMBER, II.EXPIRATION_DATE, II.NEXT_MAINTENANCE_DATE, II.LIC_MONTH, II.LIC_YEAR, II.LIC_PLATE, II.VIN, II.STATE_TITLE, II.LOCATION_ID, II.LAST_WAREHOUSE_ID, II.LAST_LOCATION_ID, II.SUB_LOCATION_ID, II.OFFICER_ID, OO.OFFICER_ID AS NEW_OFFICER_ID, II.EMPLOYEE_ID, II.ORGANIZATION_ID, II.PARENT_INSTANCE_ID, O.TITLE || EJS_GET_SPACE || O.FNAME || EJS_GET_SPACE || O.LNAME, II.CUSTODY_ORDER_ID, II.CUSTODY_EVENT_ID, II.CUSTODY_START_DATE, II.CUSTODY_END_DATE, II.CUSTODY_TYPE_CODE, II.SELF_CHECKOUT_FLAG, IE.LOT_NUMBER, IE.LOT_DESC, II.SERIALIZED_HINT_FLAG, II.DISPOSITION_FLAG, II.DISPOSITION_DATE, II.DISPOSITION_METHOD_CODE, II.DISPOSITION_COMMENT, II.DISPOSITION_USER, II.CUSTODY_AGENCY_CODE, CUSTAC.AGENCY_DESC AS CUSTODY_AGENCY_DESC, II.AGENCY_CODE AS AGENCY_CODE, AC.AGENCY_DESC AS AGENCY_DESC, II.CREATOR_ID, II.UPDATOR_ID FROM ITEM_INSTANCES II INNER JOIN ITEM_DESCRIPTORS ID ON II.DESCRIPTOR_ID = ID.DESCRIPTOR_ID INNER JOIN EJS_CODES EC ON II.STATUS_CODE = EC.CODE AND II.STATUS_CODE_TYPE = EC.CODE_TYPE INNER JOIN EJS_CODES AC ON II.USABILITY_CODE = AC.CODE AND II.USABILITY_CODE_TYPE = AC.CODE_TYPE INNER JOIN ITEM_ENTRY_INSTANCES IEI ON II.INSTANCE_ID = IEI.INSTANCE_ID INNER JOIN ITEM_ENTRIES IE ON IEI.ENTRY_ID = IE.ENTRY_ID INNER JOIN AGENCY_CODES CUSTAC ON II.CUSTODY_AGENCY_CODE = CUSTAC.AGENCY_CODE INNER JOIN AGENCY_CODES AC ON II.AGENCY_CODE = AC.AGENCY_CODE INNER JOIN OFFICERS O ON II.OFFICER_ID = O.OFFICER_ID LEFT OUTER JOIN OFFICERS OO ON II.OFFICER_ID = OO.OFFICER_ID AND OO.MASTER_OFFICER_ID IS NOT NULL UNION SELECT II.INSTANCE_ID, IEI.ENTRY_ID, II.INSTANCE_NUMBER, II.DESCRIPTOR_ID, II.ORDER_ID, EC.CODE AS STATUS_CODE, EC.DESCRIPTION AS STATUS_DESC, AC.CODE AS USABILITY_CODE, AC.DESCRIPTION AS USABILITY_DESC, II.BARCODE, II.SERIAL_NUMBER, II.EXPIRATION_DATE, II.NEXT_MAINTENANCE_DATE, II.LIC_MONTH, II.LIC_YEAR, II.LIC_PLATE, II.VIN, II.STATE_TITLE, II.LOCATION_ID, II.LAST_WAREHOUSE_ID, II.LAST_LOCATION_ID, II.SUB_LOCATION_ID, II.OFFICER_ID, EJS_GET_NULL as NEW_OFFICER_ID, II.EMPLOYEE_ID, II.ORGANIZATION_ID, II.PARENT_INSTANCE_ID, GET_OFF_EMP_NAME(E.EJS_EMP_ID,'E'), II.CUSTODY_ORDER_ID, II.CUSTODY_EVENT_ID, II.CUSTODY_START_DATE, II.CUSTODY_END_DATE, II.CUSTODY_TYPE_CODE, II.SELF_CHECKOUT_FLAG, IE.LOT_NUMBER, IE.LOT_DESC, II.SERIALIZED_HINT_FLAG, II.DISPOSITION_FLAG, II.DISPOSITION_DATE, II.DISPOSITION_METHOD_CODE, II.DISPOSITION_COMMENT, II.DISPOSITION_USER, II.CUSTODY_AGENCY_CODE, CUSTAC.AGENCY_DESC AS CUSTODY_AGENCY_DESC, II.AGENCY_CODE AS AGENCY_CODE, AC.AGENCY_DESC AS AGENCY_DESC, II.CREATOR_ID, II.UPDATOR_ID FROM ITEM_INSTANCES II INNER JOIN ITEM_DESCRIPTORS ID ON II.DESCRIPTOR_ID = ID.DESCRIPTOR_ID INNER JOIN EJS_CODES EC ON II.STATUS_CODE = EC.CODE AND II.STATUS_CODE_TYPE = EC.CODE_TYPE INNER JOIN EJS_CODES AC ON II.USABILITY_CODE = AC.CODE AND II.USABILITY_CODE_TYPE = AC.CODE_TYPE INNER JOIN ITEM_ENTRY_INSTANCES IEI ON II.INSTANCE_ID = IEI.INSTANCE_ID INNER JOIN ITEM_ENTRIES IE ON IEI.ENTRY_ID = IE.ENTRY_ID INNER JOIN AGENCY_CODES CUSTAC ON II.CUSTODY_AGENCY_CODE = CUSTAC.AGENCY_CODE INNER JOIN AGENCY_CODES AC ON II.AGENCY_CODE = AC.AGENCY_CODE INNER JOIN EMPLOYEES E ON II.EMPLOYEE_ID = E.EJS_EMP_ID UNION SELECT II.INSTANCE_ID, IEI.ENTRY_ID, II.INSTANCE_NUMBER, II.DESCRIPTOR_ID, II.ORDER_ID, EC.CODE AS STATUS_CODE, EC.DESCRIPTION AS STATUS_DESC, AC.CODE AS USABILITY_CODE, AC.DESCRIPTION AS USABILITY_DESC, II.BARCODE, II.SERIAL_NUMBER, II.EXPIRATION_DATE, II.NEXT_MAINTENANCE_DATE, II.LIC_MONTH, II.LIC_YEAR, II.LIC_PLATE, II.VIN, II.STATE_TITLE, II.LOCATION_ID, II.LAST_WAREHOUSE_ID, II.LAST_LOCATION_ID, II.SUB_LOCATION_ID, II.OFFICER_ID, EJS_GET_NULL as NEW_OFFICER_ID, II.EMPLOYEE_ID, II.ORGANIZATION_ID, II.PARENT_INSTANCE_ID, IO.ORGANIZATION_NAME, II.CUSTODY_ORDER_ID, II.CUSTODY_EVENT_ID, II.CUSTODY_START_DATE, II.CUSTODY_END_DATE, II.CUSTODY_TYPE_CODE, II.SELF_CHECKOUT_FLAG, IE.LOT_NUMBER, IE.LOT_DESC, II.SERIALIZED_HINT_FLAG, II.DISPOSITION_FLAG, II.DISPOSITION_DATE, II.DISPOSITION_METHOD_CODE, II.DISPOSITION_COMMENT, II.DISPOSITION_USER, II.CUSTODY_AGENCY_CODE, CUSTAC.AGENCY_DESC AS CUSTODY_AGENCY_DESC, II.AGENCY_CODE AS AGENCY_CODE, AC.AGENCY_DESC AS AGENCY_DESC, II.CREATOR_ID, II.UPDATOR_ID FROM ITEM_INSTANCES II INNER JOIN ITEM_DESCRIPTORS ID ON II.DESCRIPTOR_ID = ID.DESCRIPTOR_ID INNER JOIN EJS_CODES EC ON II.STATUS_CODE = EC.CODE AND II.STATUS_CODE_TYPE = EC.CODE_TYPE INNER JOIN EJS_CODES AC ON II.USABILITY_CODE = AC.CODE AND II.USABILITY_CODE_TYPE = AC.CODE_TYPE INNER JOIN ITEM_ENTRY_INSTANCES IEI ON II.INSTANCE_ID = IEI.INSTANCE_ID INNER JOIN ITEM_ENTRIES IE ON IEI.ENTRY_ID = IE.ENTRY_ID INNER JOIN AGENCY_CODES CUSTAC ON II.CUSTODY_AGENCY_CODE = CUSTAC.AGENCY_CODE INNER JOIN AGENCY_CODES AC ON II.AGENCY_CODE = AC.AGENCY_CODE INNER JOIN ITEM_ORGANIZATIONS IO ON II.ORGANIZATION_ID = IO.ORGANIZATION_ID UNION SELECT II.INSTANCE_ID, IEI.ENTRY_ID, II.INSTANCE_NUMBER, II.DESCRIPTOR_ID, II.ORDER_ID, EC.CODE AS STATUS_CODE, EC.DESCRIPTION AS STATUS_DESC, AC.CODE AS USABILITY_CODE, AC.DESCRIPTION AS USABILITY_DESC, II.BARCODE, II.SERIAL_NUMBER, II.EXPIRATION_DATE, II.NEXT_MAINTENANCE_DATE, II.LIC_MONTH, II.LIC_YEAR, II.LIC_PLATE, II.VIN, II.STATE_TITLE, II.LOCATION_ID, II.LAST_WAREHOUSE_ID, II.LAST_LOCATION_ID, II.SUB_LOCATION_ID, II.OFFICER_ID, EJS_GET_NULL as NEW_OFFICER_ID, II.EMPLOYEE_ID, II.ORGANIZATION_ID, II.PARENT_INSTANCE_ID, PII.INSTANCE_NUMBER, II.CUSTODY_ORDER_ID, II.CUSTODY_EVENT_ID, II.CUSTODY_START_DATE, II.CUSTODY_END_DATE, II.CUSTODY_TYPE_CODE, II.SELF_CHECKOUT_FLAG, IE.LOT_NUMBER, IE.LOT_DESC, II.SERIALIZED_HINT_FLAG, II.DISPOSITION_FLAG, II.DISPOSITION_DATE, II.DISPOSITION_METHOD_CODE, II.DISPOSITION_COMMENT, II.DISPOSITION_USER, II.CUSTODY_AGENCY_CODE, CUSTAC.AGENCY_DESC AS CUSTODY_AGENCY_DESC, II.AGENCY_CODE AS AGENCY_CODE, AC.AGENCY_DESC AS AGENCY_DESC, II.CREATOR_ID, II.UPDATOR_ID FROM ITEM_INSTANCES II INNER JOIN ITEM_DESCRIPTORS ID ON II.DESCRIPTOR_ID = ID.DESCRIPTOR_ID INNER JOIN EJS_CODES EC ON II.STATUS_CODE = EC.CODE AND II.STATUS_CODE_TYPE = EC.CODE_TYPE INNER JOIN EJS_CODES AC ON II.USABILITY_CODE = AC.CODE AND II.USABILITY_CODE_TYPE = AC.CODE_TYPE INNER JOIN ITEM_ENTRY_INSTANCES IEI ON II.INSTANCE_ID = IEI.INSTANCE_ID INNER JOIN ITEM_ENTRIES IE ON IEI.ENTRY_ID = IE.ENTRY_ID INNER JOIN AGENCY_CODES CUSTAC ON II.CUSTODY_AGENCY_CODE = CUSTAC.AGENCY_CODE INNER JOIN AGENCY_CODES AC ON II.AGENCY_CODE = AC.AGENCY_CODE INNER JOIN ITEM_INSTANCES PII ON II.PARENT_INSTANCE_ID = PII.INSTANCE_ID INNER JOIN ITEM_DESCRIPTORS PID ON PII.DESCRIPTOR_ID = PID.DESCRIPTOR_ID INNER JOIN ITEM_CATEGORY_CODES ICC ON PID.CATEGORY_ID = ICC.CATEGORY_ID