sQry = "SELECT TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') AS S_DATE \r\n" " FROM DUAL \r\n"; sQry = "SELECT SYST_ID, SYST_TYPE, \r\n" " SYST_IP_1, PRGM_PORT \r\n" " FROM TB_UNIT_SYST \r\n" " WHERE DEL_YN = 'N' \r\n" " ORDER BY SYST_ID \r\n"; sQry = "SELECT PORT \r\n" " FROM PROCESS \r\n" " WHERE PROCESSID = :p01 \r\n"; sQry = "SELECT A.*, \r\n" " (SELECT NVL(ATRB1, '320') \r\n" " FROM TB_CMMN_CD \r\n" " WHERE CMMN_CLSF_CD = 'VMP' \r\n" " AND CMMN_CD = A.VMS_TYPE_CD) AS FORMW, \r\n" " (SELECT NVL(ATRB2, '64') \r\n" " FROM TB_CMMN_CD \r\n" " WHERE CMMN_CLSF_CD = 'VMP' \r\n" " AND CMMN_CD = A.VMS_TYPE_CD) AS FORMH \r\n" " FROM TB_VMS_CTLR A \r\n" " WHERE A.DEL_YN = 'N' \r\n"; sQry = "SELECT A.* \r\n" " FROM TB_VMS_STTS_PRST A, \r\n" " TB_VMS_CTLR B \r\n" " WHERE B.DEL_YN = 'N' \r\n" " AND A.VMS_CTLR_NMBR = B.VMS_CTLR_NMBR \r\n"; sQry = "SELECT A.VMS_CTLR_NMBR, B.VMS_IFSC_ID, \r\n" " B.DSPL_PRRT, C.AXIS_YN \r\n" " FROM TB_VMS_CTLR A, \r\n" " TB_VMS_IFSC_RLTN B, \r\n" " TB_VMS_IFSC C \r\n" " WHERE A.VMS_CTLR_NMBR = B.VMS_CTLR_NMBR \r\n" " AND B.VMS_IFSC_ID = C.VMS_IFSC_ID \r\n" " AND C.EDTN_CD <> 'EDI2' \r\n" " AND A.DEL_YN = 'N' \r\n" " ORDER BY A.VMS_CTLR_NMBR, B.DSPL_PRRT \r\n"; sQry = "SELECT A.VMS_CTLR_NMBR, \r\n" " A.PANL_ON_TIME, \r\n" " A.PANL_OFF_TIME \r\n" " FROM TB_VMS_CTLR A \r\n" " WHERE A.DEL_YN = 'N' \r\n"; //-->용인에서만 사용함 sQry = "SELECT A.VMS_CTLR_NMBR, A.SCH_TIME, A.LUMINANCE \r\n" " FROM TB_VMS_LUM_SET_SCH A, \r\n" " TB_VMS_CTLR B \r\n" " WHERE A.VMS_CTLR_NMBR = B.VMS_CTLR_NMBR \r\n" " AND B.DEL_YN = 'N' \r\n" " AND A.USE_YN = 'Y' \r\n" " AND A.SCH_TIME = TO_CHAR(SYSDATE, 'HH24MI') \r\n"; //사용안함 sQry = "SELECT A.VMS_CTLR_NMBR, A.RGST_DT, \r\n" " A.FILETYPE, A.FILEID \r\n" " FROM TB_VMS_DOWNLOAD A \r\n" " WHERE A.SENDSTARTDATE <= TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') \r\n" " AND A.SENDRESULT = 0 \r\n" " ORDER BY A.VMS_CTLR_NMBR, A.RGST_DT DESC, A.FILETYPE, A.FILEID \r\n"; sQry = "SELECT A.* \r\n" " FROM TB_VMS_SYMB_LIB A \r\n"; sQry = "SELECT B.* \r\n" " FROM TB_VMS_SYMB_LIB A, \r\n" " TB_VMS_SYMB_IFSC B \r\n" " WHERE A.SYMBLIB_NMBR = B.SYMBLIB_NMBR \r\n" " ORDER BY B.SYMBLIB_NMBR, B.CELL_ID \r\n"; sQry = "SELECT A.* \r\n" " FROM TB_VMS_FORM A \r\n"; sQry = "SELECT B.*, NVL(C.IMAG_DATA, B.VMS_DSPL_FIGR) AS IMAG_DATA \r\n" " FROM TB_VMS_FORM A, \r\n" " TB_VMS_FORM_OBJECT B, \r\n" " TB_VMS_SYMB_LIB C \r\n" " WHERE A.VMS_FORM_ID = B.VMS_FORM_ID \r\n" " AND B.SYMBLIB_NMBR = C.SYMBLIB_NMBR(+) \r\n" " ORDER BY B.VMS_FORM_ID, B.FORM_OBJECT_ID \r\n"; sQry = "SELECT A.VMS_IFSC_ID, A.VMS_IFSC_NM, A.DSPL_STRT_NODE_NM, A.DSPL_END_NODE_NM, \r\n" " A.ROAD_NM, A.SPOT_NM, \r\n" " A.DETR_ID, A.AXIS_YN, \r\n" " NVL(B.RGST_DT, '') AS RGST_DT, \r\n" " NVL(B.CMTR_GRAD_CD, '0') AS CMTR_GRAD_CD, \r\n" " NVL(B.SPED, 0) AS SPED, \r\n" " NVL(B.TRVL_HH, 0) AS TRVL_HH \r\n" " FROM TB_VMS_IFSC A, \r\n" " (SELECT * \r\n" " FROM TB_VMS_CMTRINFR \r\n" " WHERE RGST_DT >= TO_CHAR(SYSDATE-10/1440, 'YYYYMMDDHH24MISS') ) B \r\n" " WHERE A.VMS_IFSC_ID = B.VMS_IFSC_ID(+) \r\n"; sQry = "SELECT B.*, DECODE(B.TRFC_SITU_TYPE_CD, '0', 7, \r\n" " '1', 3, \r\n" " '2', 4, \r\n" " '3', 5, \r\n" " '4', 6, \r\n" " '5', 1, \r\n" " '6', 0, \r\n" " '7', 8, 9) AS SCHPRIT \r\n" " FROM TB_VMS_CTLR A, \r\n" " TB_VMS_TRFC_INFR_DSPL_SCH B, \r\n" " TB_VMS_FORM C \r\n" " WHERE A.DEL_YN = 'N' \r\n" " AND A.VMS_CTLR_NMBR = B.VMS_CTLR_NMBR \r\n" " AND B.VMS_FORM_ID = C.VMS_FORM_ID \r\n" " AND B.USE_YN = 'Y' \r\n" " AND TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') BETWEEN \r\n" " B.DSPL_STRT_HH AND B.DSPL_END_HH \r\n"; sQry = "SELECT * \r\n" " FROM ( \r\n" + sQry + " ) \r\n" " ORDER BY VMS_CTLR_NMBR ASC, SCHPRIT ASC, PHASE ASC \r\n"; sQry = "SELECT V.VMS_CTLR_NMBR, V.LINK_ID, V.IFSC_ID, V.VMS_IFSC_ID, \r\n" " V.VMS_IFSC_NM, V.DSPL_STRT_NODE_NM, V.DSPL_END_NODE_NM, \r\n" " V.DETR_ID, V.DSPL_PRRT, I.INCD_OCRR_ID, I.STRT_LCTN_NM, \r\n" " V.ROAD_NM, V.SPOT_NM, \r\n" " I.END_LCTN_NM, I.OCRR_LCTN_NM, \r\n" " (SELECT CMMN_CD_KOR_NM \r\n" " FROM TB_CMMN_CD \r\n" " WHERE CMMN_CD = I.VMS_INCD_TYPE_CD \r\n" " AND CMMN_CLSF_CD = 'VIT') AS VMS_INCD_TYPE_NM, \r\n" " I.VMS_INCD_TYPE_CD, \r\n" " CASE WHEN I.VMS_INCD_TYPE_CD = 'VIT1' THEN \r\n" " (SELECT CMMN_CD_KOR_NM \r\n" " FROM TB_CMMN_CD \r\n" " WHERE CMMN_CD = I.VMS_INCD_DETL_TYPE_CD \r\n" " AND CMMN_CLSF_CD = 'IDT') \r\n" " ELSE \r\n" " (SELECT CMMN_CD_KOR_NM \r\n" " FROM TB_CMMN_CD \r\n" " WHERE CMMN_CD = I.VMS_INCD_DETL_TYPE_CD \r\n" " AND CMMN_CLSF_CD = 'EDT') \r\n" " END AS VMS_INCD_DETL_NM, \r\n" " I.VMS_INCD_DETL_TYPE_CD, \r\n" " (SELECT CMMN_CD_KOR_NM \r\n" " FROM TB_CMMN_CD \r\n" " WHERE CMMN_CD = I.CMTR_GRAD_CD \r\n" " AND CMMN_CLSF_CD = 'LTC') AS CMTR_GRAD_NM, \r\n" " I.CMTR_GRAD_CD, \r\n" " I.INCD_CLSR_LANE, \r\n" " I.INCD_STRT_DT, \r\n" " I.INCD_END_PRAR_DT \r\n" " FROM (SELECT D.VMS_CTLR_NMBR, A.LINK_ID, \r\n" " A.IFSC_ID, B.VMS_IFSC_ID, \r\n" " C.VMS_IFSC_NM, \r\n" " C.DSPL_STRT_NODE_NM, \r\n" " C.DSPL_END_NODE_NM, \r\n" " NVL(C.DETR_ID, 0) AS DETR_ID, \r\n" " C.ROAD_NM, C.SPOT_NM, \r\n" " D.DSPL_PRRT \r\n" " FROM TB_IFSC_LINK_RLTN A, \r\n" " TB_VMS_LINK_IFSC B, \r\n" " TB_VMS_IFSC C, \r\n" " TB_VMS_IFSC_RLTN D \r\n" " WHERE A.IFSC_ID = B.IFSC_ID \r\n" " AND B.VMS_IFSC_ID = C.VMS_IFSC_ID \r\n" " AND C.VMS_IFSC_ID = D.VMS_IFSC_ID ) V, \r\n" " (SELECT * \r\n" " FROM TB_INCD_OCRR_VMS \r\n" " WHERE INCD_PRGR_STEP_CD = 'ISS2' \r\n" " AND VMS_DSPL_YN = 'Y' \r\n" " AND VMS_INCD_TYPE_CD IN ('VIT1', 'VIT2') ) I \r\n" " WHERE V.LINK_ID = I.LINK_ID \r\n" " ORDER BY V.VMS_CTLR_NMBR \r\n"; sQry = "SELECT A.VMS_CTLR_NMBR, A.OPER_MODE, \r\n" " NVL(A.VMS_MAX_PHSE_NUM, 16) AS VMS_MAX_PHSE_NUM \r\n" " FROM TB_VMS_CTLR A \r\n" " WHERE A.DEL_YN = 'N' \r\n"; sQry = "MERGE INTO TB_VMS_STTS_PRST L \r\n" "USING (SELECT :p01 AS VMS_CTLR_NMBR, \r\n" " :p02 AS RGST_DT, \r\n" " :p03 AS CONN_STTS_CD, \r\n" " :p04 AS CBOXDOOR_OPEN_STTS_CD, \r\n" " :p05 AS PWER_STTS_CD, \r\n" " :p06 AS CBOX_TMPR, \r\n" " :p07 AS CBOX_HMDT, \r\n" " :p08 AS FAN_STTS_CD, \r\n" " :p09 AS HETR_STTS_CD, \r\n" " :p10 AS CBOXFAN_STTS_CD \r\n" " FROM DUAL) M \r\n" "ON (L.VMS_CTLR_NMBR = M.VMS_CTLR_NMBR) \r\n" "WHEN MATCHED THEN \r\n" " UPDATE SET L.RGST_DT = M.RGST_DT, \r\n" " L.CONN_STTS_CD = M.CONN_STTS_CD, \r\n" " L.CBOXDOOR_OPEN_STTS_CD = M.CBOXDOOR_OPEN_STTS_CD, \r\n" " L.PWER_STTS_CD = M.PWER_STTS_CD, \r\n" " L.CBOX_TMPR = M.CBOX_TMPR, \r\n" " L.CBOX_HMDT = M.CBOX_HMDT, \r\n" " L.FAN_STTS_CD = M.FAN_STTS_CD, \r\n" " L.HETR_STTS_CD = M.HETR_STTS_CD, \r\n" " L.CBOXFAN_STTS_CD = M.CBOXFAN_STTS_CD \r\n" "WHEN NOT MATCHED THEN \r\n" " INSERT (L.VMS_CTLR_NMBR, \r\n" " L.RGST_DT, \r\n" " L.CONN_STTS_CD, \r\n" " L.CBOXDOOR_OPEN_STTS_CD, \r\n" " L.PWER_STTS_CD, \r\n" " L.CBOX_TMPR, \r\n" " L.CBOX_HMDT, \r\n" " L.FAN_STTS_CD, \r\n" " L.HETR_STTS_CD, \r\n" " L.CBOXFAN_STTS_CD) \r\n" " VALUES (M.VMS_CTLR_NMBR, \r\n" " M.RGST_DT, \r\n" " M.CONN_STTS_CD, \r\n" " M.CBOXDOOR_OPEN_STTS_CD, \r\n" " M.PWER_STTS_CD, \r\n" " M.CBOX_TMPR, \r\n" " M.CBOX_HMDT, \r\n" " M.FAN_STTS_CD, \r\n" " M.HETR_STTS_CD, \r\n" " M.CBOXFAN_STTS_CD) \r\n"; iQry = "INSERT INTO TB_VMS_STTS_HS \r\n" " (VMS_CTLR_NMBR, \r\n" " RGST_DT, \r\n" " CONN_STTS_CD, \r\n" " CBOXDOOR_OPEN_STTS_CD, \r\n" " PWER_STTS_CD, \r\n" " CBOX_TMPR, \r\n" " CBOX_HMDT, \r\n" " FAN_STTS_CD, \r\n" " HETR_STTS_CD, \r\n" " CBOXFAN_STTS_CD) \r\n" " VALUES (:p01, \r\n" " :p02, \r\n" " :p03, \r\n" " :p04, \r\n" " :p05, \r\n" " :p06, \r\n" " :p07, \r\n" " :p08, \r\n" " :p09, \r\n" " :p10) \r\n"; sQry = "MERGE INTO TB_VMS_STTS_PRST L \r\n" "USING (SELECT :p01 AS VMS_CTLR_NMBR, \r\n" " :p02 AS RGST_DT, \r\n" " :p03 AS CONN_STTS_CD, \r\n" " :p04 AS CBOXDOOR_OPEN_STTS_CD, \r\n" " :p05 AS PWER_STTS_CD, \r\n" " :p06 AS CBOX_TMPR, \r\n" " :p07 AS CBOX_HMDT, \r\n" " :p08 AS FAN_STTS_CD, \r\n" " :p09 AS HETR_STTS_CD, \r\n" " :p10 AS CBOXFAN_STTS_CD \r\n" " FROM DUAL) M \r\n" "ON (L.VMS_CTLR_NMBR = M.VMS_CTLR_NMBR) \r\n" "WHEN MATCHED THEN \r\n" " UPDATE SET L.RGST_DT = M.RGST_DT, \r\n" " L.CONN_STTS_CD = M.CONN_STTS_CD, \r\n" " L.CBOXDOOR_OPEN_STTS_CD = M.CBOXDOOR_OPEN_STTS_CD, \r\n" " L.PWER_STTS_CD = M.PWER_STTS_CD, \r\n" " L.CBOX_TMPR = M.CBOX_TMPR, \r\n" " L.CBOX_HMDT = M.CBOX_HMDT, \r\n" " L.FAN_STTS_CD = M.FAN_STTS_CD, \r\n" " L.HETR_STTS_CD = M.HETR_STTS_CD, \r\n" " L.CBOXFAN_STTS_CD = M.CBOXFAN_STTS_CD \r\n" "WHEN NOT MATCHED THEN \r\n" " INSERT (L.VMS_CTLR_NMBR, \r\n" " L.RGST_DT, \r\n" " L.CONN_STTS_CD, \r\n" " L.CBOXDOOR_OPEN_STTS_CD, \r\n" " L.PWER_STTS_CD, \r\n" " L.CBOX_TMPR, \r\n" " L.CBOX_HMDT, \r\n" " L.FAN_STTS_CD, \r\n" " L.HETR_STTS_CD, \r\n" " L.CBOXFAN_STTS_CD) \r\n" " VALUES (M.VMS_CTLR_NMBR, \r\n" " M.RGST_DT, \r\n" " M.CONN_STTS_CD, \r\n" " M.CBOXDOOR_OPEN_STTS_CD, \r\n" " M.PWER_STTS_CD, \r\n" " M.CBOX_TMPR, \r\n" " M.CBOX_HMDT, \r\n" " M.FAN_STTS_CD, \r\n" " M.HETR_STTS_CD, \r\n" " M.CBOXFAN_STTS_CD) \r\n"; sQry = "MERGE INTO TB_VMS_DSPL_PRST L \r\n" "USING (SELECT :p01 AS VMS_CTLR_NMBR, \r\n" " :p02 AS PHASE, \r\n" " :p03 AS DSPL_DT, \r\n" " :p04 AS VMS_DSPL_MSG_DATA, \r\n" " :p05 AS VMS_DSPL_MSG_TXT, \r\n" " :p06 AS TRFC_SITU_TYPE_CD, \r\n" " :p07 AS VMS_MSG_DSPL_DRCT_CD, \r\n" " :p08 AS VMS_MSG_DSPL_MTHD_CD, \r\n" " :p09 AS DSPL_HH, \r\n" " :p10 AS VMS_FORM_ID, \r\n" " :p11 AS DNLD_YN \r\n" " FROM DUAL) M \r\n" " ON (L.VMS_CTLR_NMBR = M.VMS_CTLR_NMBR AND L.PHASE = M.PHASE) \r\n" "WHEN MATCHED THEN \r\n" "UPDATE SET L.DSPL_DT = M.DSPL_DT, \r\n" " L.VMS_DSPL_MSG_DATA = M.VMS_DSPL_MSG_DATA, \r\n" " L.VMS_DSPL_MSG_TXT = M.VMS_DSPL_MSG_TXT, \r\n" " L.TRFC_SITU_TYPE_CD = M.TRFC_SITU_TYPE_CD, \r\n" " L.VMS_MSG_DSPL_DRCT_CD = M.VMS_MSG_DSPL_DRCT_CD, \r\n" " L.VMS_MSG_DSPL_MTHD_CD = M.VMS_MSG_DSPL_MTHD_CD, \r\n" " L.DSPL_HH = M.DSPL_HH, \r\n" " L.VMS_FORM_ID = M.VMS_FORM_ID, \r\n" " L.DNLD_YN = M.DNLD_YN \r\n" "WHEN NOT MATCHED THEN \r\n" "INSERT (VMS_CTLR_NMBR, PHASE, DSPL_DT, \r\n" " VMS_DSPL_MSG_DATA, VMS_DSPL_MSG_TXT, \r\n" " TRFC_SITU_TYPE_CD, VMS_MSG_DSPL_DRCT_CD, \r\n" " VMS_MSG_DSPL_MTHD_CD, DSPL_HH, \r\n" " VMS_FORM_ID, DNLD_YN) \r\n" "VALUES (M.VMS_CTLR_NMBR, M.PHASE, M.DSPL_DT, \r\n" " M.VMS_DSPL_MSG_DATA, M.VMS_DSPL_MSG_TXT, \r\n" " M.TRFC_SITU_TYPE_CD, M.VMS_MSG_DSPL_DRCT_CD, \r\n" " M.VMS_MSG_DSPL_MTHD_CD, M.DSPL_HH, \r\n" " M.VMS_FORM_ID, M.DNLD_YN) \r\n"; uQry = "UPDATE TB_VMS_DSPL_PRST \r\n" " SET VMS_DSPL_MSG_IMAG = :p03 \r\n" " WHERE VMS_CTLR_NMBR = :p01 \r\n" " AND PHASE = :p02 \r\n"; sQry = "INSERT INTO TB_UNIT_SYST_STTS_HS(SYST_ID, CRTN_DT, SYST_STTS_CD) \r\n" " VALUES(:p01, TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS'), :p02) \r\n"; sQry = "INSERT INTO TB_VMS_DSPL_HS \r\n" " (VMS_CTLR_NMBR, DSPL_PHASE_NUM, DSPL_DT, \r\n" " VMS_DSPL_MSG_DATA, VMS_DSPL_MSG_TXT, \r\n" " TRFC_SITU_TYPE_CD, VMS_MSG_DSPL_DRCT_CD, \r\n" " VMS_MSG_DSPL_MTHD_CD, DSPL_HH, \r\n" " VMS_FORM_ID, DNLD_YN) \r\n" "SELECT VMS_CTLR_NMBR, PHASE, DSPL_DT, \r\n" " VMS_DSPL_MSG_DATA, VMS_DSPL_MSG_TXT, \r\n" " TRFC_SITU_TYPE_CD, VMS_MSG_DSPL_DRCT_CD, \r\n" " VMS_MSG_DSPL_MTHD_CD, DSPL_HH, \r\n" " VMS_FORM_ID, DNLD_YN \r\n" " FROM TB_VMS_DSPL_PRST \r\n" " WHERE VMS_CTLR_NMBR = :p01 \r\n" " AND PHASE <= :p02 \r\n"; sQry = "UPDATE TB_VMS_CTLR \r\n" " SET VMS_PHSE_CHNG_CYCL = :p01, \r\n" " VMS_CMNC_ERR_BASS_VAL = :p02, \r\n" " FAN_MTNS_TMPR = :p03, \r\n" " HETR_MTNS_TMPR = :p04, \r\n" " VMS_MODL_ERR_RATE = :p05 \r\n" " WHERE VMS_CTLR_NMBR = :p06 \r\n"; sQry = "UPDATE TB_VMS_DOWNLOAD \r\n" " SET SENDENDDATE = :p05, \r\n" " SENDSTEP = :p06, \r\n" " SENDRESULT = :p07 \r\n" " WHERE RGST_DT = :p01 \r\n" " AND VMS_CTLR_NMBR = :p02 \r\n" " AND FILETYPE = :p03 \r\n" " AND FILEID = :p04 \r\n"; sQry = "UPDATE TB_VMS_DSPL_PRST \r\n" " SET DSPL_DT = :p03, \r\n" " DNLD_YN = :p04 \r\n" " WHERE VMS_CTLR_NMBR = :p01 \r\n" " AND PHASE <= :p02 \r\n";