SELECT 'CCTV' AS FAC_TYPE, CCTV_CTLR_ID AS FAC_ID, ISTL_LCTN_NM AS FAC_NM, DEL_YN FROM TB_CCTV_CTLR WHERE DEL_YN = 'N' UNION SELECT 'VMS' AS FAC_TYPE, VMS_CTLR_ID AS FAC_ID, VMS_NM AS FAC_NM, DEL_YN FROM TB_VMS_CTLR WHERE DEL_YN = 'N' UNION SELECT 'RSE' AS FAC_TYPE, RSEID AS FAC_ID, LOCATION AS FAC_NM, 'N' AS DEL_YN FROM RUTIS.RSEMST UNION SELECT 'VDS' AS FAC_TYPE, CTLR_MNGM_NMBR AS FAC_ID, LCTN AS FAC_N, DEL_YN FROM TB_VDS_CTLR WHERE DEL_YN = 'N' UNION SELECT 'AVI' AS FAC_TYPE, AVI_ID AS FAC_ID, AVI_LCTN_NM AS FAC_NM, DEL_YN FROM TB_AVI_CTLR WHERE DEL_YN = 'N' UNION SELECT 'DSRC' AS FAC_TYPE, RSE_ID AS FAC_ID, ISTL_LCTN_NM AS FAC_NM, DEL_YN FROM TB_RSE_MSTR WHERE DEL_YN = 'N' --½Ã¼³¹° »óÅÂÁ¤º¸ SELECT 'CCTV' AS FAC_TYPE, TO_CHAR(CCTV_MNGM_NMBR) AS FAC_ID, DECODE(CMNC_STTS_CD, 'CMS0', '1', 'CMS1', '0', '2') AS COMM, DECODE(CBOX_DOOR_STTS_CD, 'CDS0', '0', 'CDS1', '1', '0') AS DOOR FROM TB_CCTV_STTS WHERE UPDT_DT >= TO_CHAR(SYSDATE-2/1440, 'YYYYMMDDHH24MISS') UNION SELECT 'VMS' AS FAC_TYPE, TO_CHAR(VMS_CTLR_NMBR) AS FAC_ID, DECODE(CONN_STTS_CD, 'CMS0', '1', 'CMS1', '0', '2') AS COMM, DECODE(CBOXDOOR_OPEN_STTS_CD, 'CDS0', '0', 'CDS1', '1', '0') AS DOOR FROM TB_VMS_STTS_PRST WHERE RGST_DT >= TO_CHAR(SYSDATE-2/1440, 'YYYYMMDDHH24MISS') UNION SELECT 'RSE' AS FAC_TYPE, B.RSEID AS FAC_ID, '1' AS COMM, '0' AS DOOR FROM RUTIS.RSEOPSTATE A, RUTIS.RSEMST B WHERE B.RSEID = A.RSEID AND A.COLLDT > SYSDATE - 30/1440 UNION SELECT 'VDS' AS FAC_TYPE, CTLR_MNGM_NMBR AS FAC_ID, DECODE(CMNC_STTS_CD, 'CMS0', '1', 'CMS1', '0', '2') AS COMM, DECODE(CBOX_DOOR_STTS_CD, 'CDS0', '0', 'CDS1', '1', '0') AS DOOR FROM TB_VDS_CTLR_STTS WHERE UPDT_DT >= TO_CHAR(SYSDATE-2/1440, 'YYYYMMDDHH24MISS') UNION SELECT 'AVI' AS FAC_TYPE, AVI_CTLR_MNGM_NMBR AS FAC_ID, DECODE(CMNC_STTS_CD, 'CMS0', '1', 'CMS1', '0', '2') AS COMM, DECODE(CBOX_DOOR_STTS_CD, 'CDS0', '0', 'CDS1', '1', '0') AS DOOR FROM TB_AVI_CTLR_STTS WHERE UPDT_DT >= TO_CHAR(SYSDATE-2/1440, 'YYYYMMDDHH24MISS') UNION SELECT 'DSRC' AS FAC_TYPE, ID AS FAC_ID, DECODE(CMNC_STTS, 'CMS0', '1', 'CMS1', '0', '0') AS COMM, '0' AS DOOR FROM TB_RSE_STTS_PNST WHERE CLCT_DT >= TO_CHAR(SYSDATE-2/1440, 'YYYYMMDDHH24MISS')