SELECT IC.CUSTODY_ID, IC.INSTANCE_ID, AC.AGENCY_CODE AS CUSTODY_AGENCY_CODE, AC.AGENCY_DESC AS CUSTODY_AGENCY_DESC, IC.CUSTODY_EVENT_ID, IC.CUSTODY_START_DATE, IC.CUSTODY_END_DATE, IC.ACTIVE_FLAG, IC.SELF_CHECKOUT_FLAG, IC.LOCATION_ID, IC.SUB_LOCATION_ID, IC.ORGANIZATION_ID, IC.OFFICER_ID, IC.EMPLOYEE_ID, IC.PARENT_INSTANCE_ID, IL.DESCRIPTION AS LOCATION_DESC, ISL.DESCRIPTION as SUB_LOCATION_DESC, null as OFFICER_DESC, null as EMPLOYEE_DESC, null as ORGANIZATION_DESC, null as PARENT_INSTANCE_DESC, ICE.NOTES, IUP.FNAME || ' ' || IUP.LNAME, IC.CREATOR_DATE FROM ITEM_CUSTODIES IC INNER JOIN AGENCY_CODES AC ON IC.AGENCY_CODE = AC.AGENCY_CODE INNER JOIN ITEM_CUSTODY_EVENTS ICE ON IC.CUSTODY_EVENT_ID = ICE.CUSTODY_EVENT_ID INNER JOIN ITEM_LOCATIONS IL ON IC.LOCATION_ID = IL.LOCATION_ID INNER JOIN ITEM_SUB_LOCATIONS ISL ON IC.SUB_LOCATION_ID = ISL.SUB_LOCATION_ID LEFT OUTER JOIN IJIS_USER_PROFILES IUP ON IC.CREATOR_ID = IUP.LOGIN_ID UNION SELECT IC.CUSTODY_ID, IC.INSTANCE_ID, AC.AGENCY_CODE AS CUSTODY_AGENCY_CODE, AC.AGENCY_DESC AS CUSTODY_AGENCY_DESC, IC.CUSTODY_EVENT_ID, IC.CUSTODY_START_DATE, IC.CUSTODY_END_DATE, IC.ACTIVE_FLAG, IC.SELF_CHECKOUT_FLAG, IC.LOCATION_ID, IC.SUB_LOCATION_ID, IC.ORGANIZATION_ID, IC.OFFICER_ID, IC.EMPLOYEE_ID, IC.PARENT_INSTANCE_ID, null as LOCATION_DESC, null as SUB_LOCATION_DESC, O.TITLE || ' ' || O.FNAME || ' '|| O.LNAME as OFFICER_DESC, null as EMPLOYEE_DESC, null as ORGANIZATION_DESC, null as PARENT_INSTANCE_DESC, ICE.NOTES, IUP.FNAME || ' ' || IUP.LNAME, IC.CREATOR_DATE FROM ITEM_CUSTODIES IC INNER JOIN AGENCY_CODES AC ON IC.AGENCY_CODE = AC.AGENCY_CODE INNER JOIN OFFICERS O ON IC.OFFICER_ID = O.OFFICER_ID INNER JOIN ITEM_CUSTODY_EVENTS ICE ON IC.CUSTODY_EVENT_ID = ICE.CUSTODY_EVENT_ID LEFT OUTER JOIN IJIS_USER_PROFILES IUP ON IC.CREATOR_ID = IUP.LOGIN_ID UNION SELECT IC.CUSTODY_ID, IC.INSTANCE_ID, AC.AGENCY_CODE AS CUSTODY_AGENCY_CODE, AC.AGENCY_DESC AS CUSTODY_AGENCY_DESC, IC.CUSTODY_EVENT_ID, IC.CUSTODY_START_DATE, IC.CUSTODY_END_DATE, IC.ACTIVE_FLAG, IC.SELF_CHECKOUT_FLAG, IC.LOCATION_ID, IC.SUB_LOCATION_ID, IC.ORGANIZATION_ID, IC.OFFICER_ID, IC.EMPLOYEE_ID, IC.PARENT_INSTANCE_ID, null as LOCATION_DESC, null as SUB_LOCATION_DESC, null as OFFICER_DESC, GET_OFF_EMP_NAME(E.EJS_EMP_ID,'E') as EMPLOYEE_DESC, null as ORGANIZATION_DESC, null as PARENT_INSTANCE_DESC, ICE.NOTES, IUP.FNAME || ' ' || IUP.LNAME, IC.CREATOR_DATE FROM ITEM_CUSTODIES IC INNER JOIN AGENCY_CODES AC ON IC.AGENCY_CODE = AC.AGENCY_CODE INNER JOIN EMPLOYEES E ON IC.EMPLOYEE_ID = E.EJS_EMP_ID INNER JOIN ITEM_CUSTODY_EVENTS ICE ON IC.CUSTODY_EVENT_ID = ICE.CUSTODY_EVENT_ID LEFT OUTER JOIN IJIS_USER_PROFILES IUP ON IC.CREATOR_ID = IUP.LOGIN_ID UNION SELECT IC.CUSTODY_ID, IC.INSTANCE_ID, AC.AGENCY_CODE AS CUSTODY_AGENCY_CODE, AC.AGENCY_DESC AS CUSTODY_AGENCY_DESC, IC.CUSTODY_EVENT_ID, IC.CUSTODY_START_DATE, IC.CUSTODY_END_DATE, IC.ACTIVE_FLAG, IC.SELF_CHECKOUT_FLAG, IC.LOCATION_ID, IC.SUB_LOCATION_ID, IC.ORGANIZATION_ID, IC.OFFICER_ID, IC.EMPLOYEE_ID, IC.PARENT_INSTANCE_ID, null as LOCATION_DESC, null as SUB_LOCATION_DESC, null as OFFICER_DESC, null as EMPLOYEE_DESC, IO.ORGANIZATION_NAME, null as PARENT_INSTANCE_DESC, ICE.NOTES, IUP.FNAME || ' ' || IUP.LNAME, IC.CREATOR_DATE FROM ITEM_CUSTODIES IC INNER JOIN AGENCY_CODES AC ON IC.AGENCY_CODE = AC.AGENCY_CODE INNER JOIN ITEM_ORGANIZATIONS IO ON IC.ORGANIZATION_ID = IO.ORGANIZATION_ID INNER JOIN ITEM_CUSTODY_EVENTS ICE ON IC.CUSTODY_EVENT_ID = ICE.CUSTODY_EVENT_ID LEFT OUTER JOIN IJIS_USER_PROFILES IUP ON IC.CREATOR_ID = IUP.LOGIN_ID UNION SELECT IC.CUSTODY_ID, IC.INSTANCE_ID, AC.AGENCY_CODE AS CUSTODY_AGENCY_CODE, AC.AGENCY_DESC AS CUSTODY_AGENCY_DESC, IC.CUSTODY_EVENT_ID, IC.CUSTODY_START_DATE, IC.CUSTODY_END_DATE, IC.ACTIVE_FLAG, IC.SELF_CHECKOUT_FLAG, IC.LOCATION_ID, IC.SUB_LOCATION_ID, IC.ORGANIZATION_ID, IC.OFFICER_ID, IC.EMPLOYEE_ID, IC.PARENT_INSTANCE_ID, null as LOCATION_DESC, null as SUB_LOCATION_DESC, null as OFFICER_DESC, null as EMPLOYEE_DESC, null as ORGANIZATION_DESC, II.INSTANCE_NUMBER AS PARENT_INSTANCE_DESC, ICE.NOTES, IUP.FNAME || ' ' || IUP.LNAME, IC.CREATOR_DATE FROM ITEM_CUSTODIES IC INNER JOIN AGENCY_CODES AC ON IC.AGENCY_CODE = AC.AGENCY_CODE INNER JOIN ITEM_INSTANCES II ON IC.PARENT_INSTANCE_ID = II.INSTANCE_ID INNER JOIN ITEM_DESCRIPTORS ID ON II.DESCRIPTOR_ID = ID.DESCRIPTOR_ID INNER JOIN ITEM_CUSTODY_EVENTS ICE ON IC.CUSTODY_EVENT_ID = ICE.CUSTODY_EVENT_ID LEFT OUTER JOIN IJIS_USER_PROFILES IUP ON IC.CREATOR_ID = IUP.LOGIN_ID