ITS_OP/SRC/SQL/시설물 등록정보.sql

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')