CREATE OR REPLACE procedure BILLDV.nms_send_data ( i_operator_id VARCHAR2, i_cust VARCHAR2, i_wip_number NUMBER, i_outlet_num INTEGER, i_work_type VARCHAR2, i_device_kind VARCHAR2, c_result OUT ref_cursor_wfapkg.ref_cursor ) IS v_error_code INTEGER; v_error_msg VARCHAR2(255); v_sql VARCHAR2(4000); v_sql1 VARCHAR2(4000); v_cm_num VARCHAR2(12); v_l3_code VARCHAR2(12); v_l2_code VARCHAR2(12); v_l2_port VARCHAR2(4); v_new_cm_num VARCHAR2(12); v_new_l3_code VARCHAR2(12); v_new_l2_code VARCHAR2(12); v_new_l2_port VARCHAR2(4); v_class_id VARCHAR2(20); v_oper_corp VARCHAR2(5); v_oper_name VARCHAR2(20); --NMS 변수 v_class_sql VARCHAR2(4000); v_package_id VARCHAR2(10); v_id_order_cnt NUMBER; v_id_change_cnt NUMBER; v_transaction_id VARCHAR2(30); v_ip_cnt NUMBER; v_command VARCHAR2(20); v_nmsstring VARCHAR2(4000); v_nmsstring2 VARCHAR2(4000); v_box_type VARCHAR2(1); v_device_gbn VARCHAR2(1); v_rate_sign VARCHAR2(1); v_device_vocheck VARCHAR2(1); v_ent_name VARCHAR2(20); v_new_ent_name VARCHAR2(20); TYPE CLASS_CUR IS REF CURSOR ; class_cursor CLASS_CUR; BEGIN v_error_code := 0; v_error_msg := ''; /************************* *** 사용자 ID체크 *** *************************/ BEGIN select corp, oper_name into v_oper_corp, v_oper_name from operator where oper_id = i_operator_id; EXCEPTION WHEN NO_DATA_FOUND THEN v_error_code := -1; v_error_msg := '사용자 ID가 잘못되었습니다'; v_sql := 'select ' || v_error_code || ' as error_code , ''' || rtrim(v_error_msg) || ''' as error_msg from dual'; OPEN c_result FOR v_sql; RETURN; WHEN OTHERS THEN v_error_code := -2; v_error_msg := '사용자 ID 체크시 예상치 않은 오류['||SQLERRM||']'; v_sql := 'select ' || v_error_code || ' as error_code , ''' || rtrim(v_error_msg) || ''' as error_msg from dual'; OPEN c_result FOR v_sql; RETURN; END; /*********************** *** 장치구분 추출 *** ***********************/ IF i_work_type = '6' THEN BEGIN IF i_device_kind = '5' THEN select distinct NVL(new_voip_device_gbn, '') as device_gbn into v_device_gbn from wip_outlet_as a where a.outlet_num = (select b.parent_outlet_num from wip_outlet_as_port b where a.cust=b.cust and a.wip_number=b.wip_number and b.outlet_num = i_outlet_num) and a.cust = i_cust and a.wip_number = i_wip_number; ELSE select distinct NVL(device_gbn, '') as device_gbn into v_device_gbn from rate_device where rate_code in (select rate_code from wip_cust_rate_as where cust = i_cust and wip_number = i_wip_number and outlet_num = i_outlet_num and rate_type = 'B' and rate_sign = '^') and active_status = 'Y' order by device_gbn; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN v_error_code := -3; v_error_msg := ' 전송할 장치구분가 존재하지 않습니다. 장치선택여부 확인하여주세요!'; v_sql := 'select ' || v_error_code || ' as error_code , ''' || rtrim(v_error_msg) || ''' as error_msg from dual'; OPEN c_result FOR v_sql; RETURN; WHEN OTHERS THEN v_error_code := -4; v_error_msg := ' 전송 장치구분 체크시 예상치 않은 오류['||SQLERRM||']'|| v_error_code; v_sql := 'select ' || v_error_code || ' as error_code , ''' || rtrim(v_error_msg) || ''' as error_msg from dual'; OPEN c_result FOR v_sql; RETURN; END; ELSE BEGIN IF i_device_kind = '5' THEN select distinct NVL(voip_device_gbn, '') as device_gbn into v_device_gbn from wip_outlet a where a.outlet_num = (select b.parent_outlet_num from wip_outlet_port b where a.cust=b.cust and a.wip_number=b.wip_number and b.outlet_num = i_outlet_num) and a.cust = i_cust and a.wip_number = i_wip_number; ELSE IF i_work_type = '1' THEN v_rate_sign := '+'; ELSIF i_work_type = '2' THEN v_rate_sign := '-'; ELSIF i_work_type = '3' THEN v_rate_sign := '!'; ELSIF i_work_type = '4' THEN v_rate_sign := '#'; ELSIF i_work_type = '9' THEN v_rate_sign := '<'; ELSE v_rate_sign := '+'; END IF; select distinct NVL(device_gbn, '') as device_gbn into v_device_gbn from rate_device where rate_code in (select rate_code from wip_cust_rate where cust = i_cust and wip_number = i_wip_number and outlet_num = i_outlet_num and rate_type = 'B' and rate_sign = v_rate_sign) and active_status = 'Y' and device_kind = i_device_kind order by device_gbn; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN v_error_code := -5; v_error_msg := ' 전송할 장치구분이 존재하지 않습니다. 장치선택여부 확인하여주세요'; v_sql := 'select ' || v_error_code || ' as error_code , ''' || rtrim(v_error_msg) || ''' as error_msg from dual'; OPEN c_result FOR v_sql; RETURN; WHEN OTHERS THEN v_error_code := -6; v_error_msg := ' 전송 장치구분 체크시 예상치 않은 오류['||SQLERRM||']'|| v_error_code; v_sql := 'select ' || v_error_code || ' as error_code , ''' || rtrim(v_error_msg) || ''' as error_msg from dual'; OPEN c_result FOR v_sql; RETURN; END; END IF; /**************************** *** 장치 선택여부 체크 *** ****************************/ IF i_work_type = '6' THEN BEGIN IF i_device_kind = '5' THEN select a.voip_box_num, a.l3_code, a.l2_code, a.l2_port,a.new_voip_box_num, a.new_l3_code, a.new_l2_code, a.new_l2_port, a.ENT_NAME, a.NEW_ENT_NAME into v_cm_num, v_l3_code, v_l2_code, v_l2_port,v_new_cm_num, v_new_l3_code, v_new_l2_code, v_new_l2_port, v_ent_name, v_new_ent_name from wip_outlet_as a where a.cust = i_cust and a.wip_number = i_wip_number and a.outlet_num = (select b.parent_outlet_num from wip_outlet_as_port b where a.cust=b.cust and a.wip_number=b.wip_number and b.outlet_num = i_outlet_num); ELSE select cm_num, l3_code, l2_code, l2_port, new_cm_num, new_l3_code, new_l2_code, new_l2_port, ENT_NAME, NEW_ENT_NAME into v_cm_num, v_l3_code, v_l2_code, v_l2_port, v_new_cm_num, v_new_l3_code, v_new_l2_code, v_new_l2_port, v_ent_name, v_new_ent_name from wip_outlet_as where cust = i_cust and wip_number = i_wip_number and outlet_num = i_outlet_num; -- CM, PON, MOCA setting IF v_device_gbn = '2' or v_device_gbn = '8' or v_device_gbn = 'A' or v_device_gbn = 'C' THEN IF NVL(v_new_cm_num, ' ') <> ' ' THEN v_cm_num := v_new_cm_num; END IF; END IF; -- VDSL, METRO setting IF v_device_gbn = '8' or v_device_gbn = '9' THEN IF NVL(v_new_l3_code, ' ') <> ' ' THEN v_l3_code := v_new_l3_code; ELSIF NVL(v_new_l2_code, ' ') <> ' ' THEN v_l2_code := v_new_l3_code; ELSIF NVL(v_new_l2_port, ' ') <> ' ' THEN v_l2_port := v_new_l2_port; END IF; END IF; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN v_error_code := -7; v_error_msg := '전송할 장치가 존재하지 않습니다. 장치선택여부 확인하여주세요!'; v_sql := 'select ' || v_error_code || ' as error_code , ''' || rtrim(v_error_msg) || ''' as error_msg from dual'; OPEN c_result FOR v_sql; RETURN; WHEN OTHERS THEN v_error_code := -8; v_error_msg := ' 전송 장치 체크시 예상치 않은 오류['||SQLERRM||']'|| v_error_code; v_sql := 'select ' || v_error_code || ' as error_code , ''' || rtrim(v_error_msg) || ''' as error_msg from dual'; OPEN c_result FOR v_sql; RETURN; END; ELSE BEGIN IF i_device_kind = '5' THEN select a.voip_box_num, a.l3_code, a.l2_code, a.l2_port, a.ent_name into v_cm_num, v_l3_code, v_l2_code, v_l2_port, v_ent_name from wip_outlet a where a.cust = i_cust and a.wip_number = i_wip_number and a.outlet_num = (select b.parent_outlet_num from wip_outlet_port b where a.cust=b.cust and a.wip_number=b.wip_number and b.outlet_num = i_outlet_num); ELSE select cm_num, l3_code, l2_code, l2_port, ent_name into v_cm_num, v_l3_code, v_l2_code, v_l2_port, v_ent_name from wip_outlet where cust = i_cust and wip_number = i_wip_number and outlet_num = i_outlet_num; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN v_error_code := -9; v_error_msg := '전송할 장치가 존재하지 않습니다. 장치선택여부 확인하여주세요'; v_sql := 'select ' || v_error_code || ' as error_code , ''' || rtrim(v_error_msg) || ''' as error_msg from dual'; OPEN c_result FOR v_sql; RETURN; WHEN OTHERS THEN v_error_code := -10; v_error_msg := ' 전송 장치 체크시 예상치 않은 오류['||SQLERRM||']'|| v_error_code; v_sql := 'select ' || v_error_code || ' as error_code , ''' || rtrim(v_error_msg) || ''' as error_msg from dual'; OPEN c_result FOR v_sql; RETURN; END; END IF; -- CM ckeck IF v_device_gbn = '2' or v_device_gbn = 'A' or v_device_gbn = 'C' THEN -- v_device_gbn = '8' 일경우는 뺌(vdsl일경우는 cm check안한다) IF NVL(v_cm_num, ' ') = ' ' THEN v_error_code := -11; v_error_msg := '전송할 장치가 존재하지 않습니다. 장치선택여부 확인하여주세요'; v_sql := 'select ' || v_error_code || ' as error_code , ''' || rtrim(v_error_msg) || ''' as error_msg from dual'; OPEN c_result FOR v_sql; RETURN; END IF; END IF; -- VDSL, METRO check IF v_device_gbn = '8' or v_device_gbn = '9' THEN IF NVL(v_l3_code, ' ') = ' ' or NVL(v_l2_code, ' ') = ' ' or NVL(v_l2_port, ' ') = ' ' THEN v_error_code := -12; v_error_msg := 'L3_CODE, L2_CODE, L2_PORT중 선택안한 장치가 존재합니다..'; v_sql := 'select ' || v_error_code || ' as error_code , ''' || rtrim(v_error_msg) || ''' as error_msg from dual'; OPEN c_result FOR v_sql; RETURN; END IF; END IF; /******************************************************************************* * infra911 연동 API * ******************************************************************************* * - 기술자출고, 기술자 철거 * * 0 커맨드[command] : CM_TECH_SET * * 1 트랜잭션ID[transaction_id] : sysdate || transaction_id_seq * * 2 MAC[mac address] : cm_num * * 3 서비스코드[service_code] : 'T' * * 4 IP수[ip count] : 1 * * * * - 설치, 요율변경 Reset기능 포함 * * 0 커맨드[command] : CM_SVC_SET * * 1 트랜잭션ID[transaction_id] : sysdate || transaction_id_seq * * 2 MAC[mac address] : cm_num * * 3 서비스코드[service_code] : * * 4 IP수[ip count] : * * 5 메트로 L3_CODE[metro l3code] : L3_CODE * * 6 메트로 L2_CODE[metro l2code] : L2_CODE * * 7 메트로 모뎀[metro modidx] : '1' * * 8 메트로 포트[metro portidx] : L2_PORT * * * * - 해지, 정지 * * 0 커맨드[command] : CM_STOP * * 1 트랜잭션ID[transaction_id] : sysdate || transaction_id_seq * * 2 MAC[mac address] : cm_num * * 3 메트로 L3_CODE[metro l3code] : L3_CODE * * 4 메트로 L2_CODE[metro l2code] : L2_CODE * * 5 메트로 모뎀[metro modidx] : '1' * * 6 메트로 포트[metro portidx] : L2_PORT * * * *******************************************************************************/ /**************************************** * class_id 추출 * ****************************************/ v_class_id := ''; IF (i_work_type <> '4') AND (i_device_kind <> '5') THEN BEGIN IF i_work_type = '6' THEN v_class_sql := ' select package_id_org ' || ' from ta_package_master ' || ' where package_id in (select distinct package_id ' || ' from rate ' || ' where rate_code in (select rate_code ' || ' from wip_cust_rate_as ' || ' where cust = ''' || i_cust || '''' || ' and wip_number = ' || i_wip_number || ' and outlet_num = ' || i_outlet_num || ') ' || ' and rate_type = ''B'' ' || ' and package_id is not null) '; ELSE v_class_sql := ' select package_id_org ' || ' from ta_package_master ' || ' where package_id in (select distinct package_id ' || ' from rate ' || ' where rate_code in (select rate_code ' || ' from wip_cust_rate ' || ' where cust = ''' || i_cust || '''' || ' and wip_number = ' || i_wip_number || ' and outlet_num = ' || i_outlet_num || ') ' || ' and rate_type = ''B'' ' || ' and package_id is not null) '; END IF; OPEN class_cursor FOR v_class_sql; LOOP FETCH class_cursor INTO v_package_id; EXIT WHEN class_cursor%NOTFOUND; v_class_id := v_class_id || v_package_id; END LOOP; CLOSE class_cursor; EXCEPTION WHEN OTHERS THEN CLOSE class_cursor; v_error_code := -13; v_error_msg := 'class_id 추출시 예상치 않은 오류 ['||SQLERRM||']'||v_error_code; v_sql := ' select ' || v_error_code || ' as error_code, rtrim(''' || v_error_msg || ''') as error_msg from dual'; OPEN c_result FOR v_sql; RETURN; END; END IF; /**************************************** * ip_cnt 추출 * ****************************************/ IF (i_work_type IN ('1', '3', '4', '6', '7', '9')) AND (i_device_kind <> '5') THEN IF i_work_type = '6' THEN BEGIN select id_order_cnt, id_change_cnt into v_id_order_cnt, v_id_change_cnt from wip_cust_rate_as where cust = i_cust and wip_number = i_wip_number and outlet_num = i_outlet_num and rate_type = 'B' and rate_sign = '^'; EXCEPTION WHEN NO_DATA_FOUND THEN v_error_code := -14; v_error_msg := ' [wip_cust_rate_as] id_cnt DATA가 존재하지 않습니다. '; v_sql := ' select ' || v_error_code || ' as error_code, rtrim(''' || v_error_msg || ''') as error_msg from dual'; OPEN c_result FOR v_sql; RETURN; WHEN OTHERS THEN v_error_code := -15; v_error_msg := ' ip_cnt[wip_cust_rate_as] 추출시 예상치 않은 오류 ['||SQLERRM||']'||v_error_code; v_sql := ' select ' || v_error_code || ' as error_code, rtrim(''' || v_error_msg || ''') as error_msg from dual'; OPEN c_result FOR v_sql; RETURN; END; v_ip_cnt := v_id_order_cnt; IF NVL(to_char(v_id_change_cnt), '0') <> '0' THEN v_ip_cnt := v_id_change_cnt; END IF; ELSE BEGIN select id_order_cnt, id_change_cnt into v_id_order_cnt, v_id_change_cnt from wip_cust_rate where cust = i_cust and wip_number = i_wip_number and outlet_num = i_outlet_num and rate_type = 'B' and rate_sign = v_rate_sign; EXCEPTION WHEN NO_DATA_FOUND THEN v_error_code := -16; v_error_msg := ' [wip_cust_rate] id_cnt DATA가 존재하지 않습니다. '; v_sql := ' select ' || v_error_code || ' as error_code, rtrim(''' || v_error_msg || ''') as error_msg from dual'; OPEN c_result FOR v_sql; RETURN; WHEN OTHERS THEN v_error_code := -17; v_error_msg := ' ip_cnt[wip_cust_rate] 추출시 예상치 않은 오류 ['||SQLERRM||']'||v_error_code; v_sql := ' select ' || v_error_code || ' as error_code, rtrim(''' || v_error_msg || ''') as error_msg from dual'; OPEN c_result FOR v_sql; RETURN; END; v_ip_cnt := v_id_order_cnt; IF NVL(to_char(v_id_change_cnt), '0') <> '0' THEN v_ip_cnt := v_id_change_cnt; END IF; END IF; END IF; /**************************************** * transaction_id 추출 * ****************************************/ BEGIN select TO_CHAR(sysdate, 'yyyymmddhh24miss') || trim(TO_CHAR(seq_aps_connect_history.nextval)) into v_transaction_id from dual; EXCEPTION WHEN OTHERS THEN v_error_code := -18; v_error_msg := ' ip_cnt[wip_cust_rate] 추출시 예상치 않은 오류 ['||SQLERRM||']'||v_error_code; v_sql := ' select ' || v_error_code || ' as error_code, rtrim(''' || v_error_msg || ''') as error_msg from dual'; OPEN c_result FOR v_sql; RETURN; END; /********************************** * box_type 추출 * **********************************/ BEGIN select box_type into v_box_type from converter_inventory where box_num = v_cm_num; EXCEPTION WHEN NO_DATA_FOUND THEN v_box_type := ''; END; v_command := ''; v_nmsstring := ''; v_nmsstring2 := ''; IF i_device_kind = '5' THEN IF (v_device_gbn = 'I') THEN IF i_work_type IN ('1', '3', '7', '9', '21', '22', '23', '27') THEN v_device_vocheck := v_device_gbn; v_command := 'VMTACM_SVC_SET'; BEGIN select RPAD('!' || RPAD(i_cust, 10, ' ') || RPAD(TO_CHAR(i_outlet_num), 10, ' ') || RPAD(TO_CHAR(i_wip_number), 10, ' ') || RPAD(v_oper_corp, 10, ' ') || RPAD(i_device_kind, 1, ' ') || RPAD(v_device_vocheck, 1, ' ') || RPAD(v_box_type, 1, ' '), 50, ' ') || v_command || ';' || RPAD(v_transaction_id, 17, ' ') || ';' || v_cm_num || ';' || RPAD(v_class_id, 1, ' ') || ';' || to_char(v_ip_cnt)|| ';' || v_l3_code || ';' || v_l2_code || ';' || '1' || ';' || v_l2_port || ';' || v_ent_name || ';' into v_nmsstring from dual; EXCEPTION WHEN OTHERS THEN v_error_code := -19; v_error_msg := ' 예상치 않은 오류 ['||SQLERRM||']'||v_error_code; v_sql := ' select ' || v_error_code || ' as error_code, rtrim(''' || v_error_msg || ''') as error_msg from dual'; OPEN c_result FOR v_sql; RETURN; END; ELSIF i_work_type ='6' THEN v_device_vocheck := v_device_gbn; v_command := 'VMTACM_SVC_SET'; BEGIN select RPAD('!' || RPAD(i_cust, 10, ' ') || RPAD(TO_CHAR(i_outlet_num), 10, ' ') || RPAD(TO_CHAR(i_wip_number), 10, ' ') || RPAD(v_oper_corp, 10, ' ') || RPAD(i_device_kind, 1, ' ') || RPAD(v_device_vocheck, 1, ' ') || RPAD(v_box_type, 1, ' '), 50, ' ') || v_command || ';' || RPAD(v_transaction_id, 17, ' ') || ';' || v_new_cm_num || ';' || RPAD(v_class_id, 1, ' ') || ';' || to_char(v_ip_cnt)|| ';' || v_new_l3_code || ';' || v_new_l2_code || ';' || '1' || ';' || v_new_l2_port || ';' || v_new_ent_name || ';' into v_nmsstring from dual; EXCEPTION WHEN OTHERS THEN v_error_code := -19; v_error_msg := ' 예상치 않은 오류 ['||SQLERRM||']'||v_error_code; v_sql := ' select ' || v_error_code || ' as error_code, rtrim(''' || v_error_msg || ''') as error_msg from dual'; OPEN c_result FOR v_sql; RETURN; END; ELSIF i_work_type in ('2', '24', '25', '26') THEN v_device_vocheck := v_device_gbn; v_command := 'VMTACM_STOP'; BEGIN select RPAD('!' || RPAD(i_cust, 10, ' ') || RPAD(TO_CHAR(i_outlet_num), 10, ' ') || RPAD(TO_CHAR(i_wip_number), 10, ' ') || RPAD(v_oper_corp, 10, ' ') || RPAD(i_device_kind, 1, ' ') || RPAD(v_device_vocheck, 1, ' ') || RPAD(v_box_type, 1, ' '), 50, ' ') || rtrim(v_Command) ||';'||rtrim(v_transaction_id)||';'||rtrim(v_cm_num)|| ';' || v_l3_code || ';' || v_l2_code || ';' || '1' || ';' || v_l2_port || ';' || v_ent_name || ';' into v_nmsstring from dual; EXCEPTION WHEN OTHERS THEN v_error_code := -16; v_error_msg := ' 예상치 않은 오류 ['||SQLERRM||']'||v_error_code; v_sql := ' select ' || v_error_code || ' as error_code, rtrim(''' || v_error_msg || ''') as error_msg from dual'; OPEN c_result FOR v_sql; RETURN; END; END IF; END IF; ELSE IF i_work_type IN ('1', '3', '6', '7', '9') THEN BEGIN IF v_device_gbn = 'A' THEN BEGIN IF i_work_type IN ('1', '3', '7' ,'9') THEN BEGIN v_command := 'PON_CM_SET'; v_nmsstring := v_command || ';' || v_cm_num || ';' || v_class_id || ';' ; EXCEPTION WHEN OTHERS THEN v_error_code := -19; v_error_msg := ' 예상치 않은 오류 ['||SQLERRM||']'||v_error_code; v_sql := ' select ' || v_error_code || ' as error_code, rtrim(''' || v_error_msg || ''') as error_msg from dual'; OPEN c_result FOR v_sql; RETURN; END; ELSIF i_work_type = '6' THEN BEGIN IF ( (v_new_cm_num is not null and (v_cm_num <> v_new_cm_num)) ) THEN v_command := 'PON_CM_STOP'; v_nmsstring := v_command || ';' || v_cm_num || ';' || 'T' ; v_command := 'PON_CM_SET'; v_nmsstring2 := v_command || ';' || v_new_cm_num || ';' || v_class_id || ';' ; END IF; EXCEPTION WHEN OTHERS THEN v_error_code := -19; v_error_msg := ' 예상치 않은 오류 ['||SQLERRM||']'||v_error_code; v_sql := ' select ' || v_error_code || ' as error_code, rtrim(''' || v_error_msg || ''') as error_msg from dual'; OPEN c_result FOR v_sql; RETURN; END; END IF; END; ELSIF v_device_gbn = 'C' THEN BEGIN IF i_work_type IN ('1', '3', '7' ,'9') THEN BEGIN v_command := 'MOCA_CM_SET'; v_nmsstring := v_command || ';' || v_cm_num || ';' || v_class_id || ';' ; EXCEPTION WHEN OTHERS THEN v_error_code := -19; v_error_msg := ' 예상치 않은 오류 ['||SQLERRM||']'||v_error_code; v_sql := ' select ' || v_error_code || ' as error_code, rtrim(''' || v_error_msg || ''') as error_msg from dual'; OPEN c_result FOR v_sql; RETURN; END; ELSIF i_work_type = '6' THEN BEGIN IF ( (v_new_cm_num is not null and (v_cm_num <> v_new_cm_num)) ) THEN v_command := 'MOCA_CM_STOP'; v_nmsstring := v_command || ';' || v_cm_num || ';' || 'T' ; v_command := 'MOCA_CM_SET'; v_nmsstring2 := v_command || ';' || v_new_cm_num || ';' || v_class_id || ';' ; END IF; EXCEPTION WHEN OTHERS THEN v_error_code := -19; v_error_msg := ' 예상치 않은 오류 ['||SQLERRM||']'||v_error_code; v_sql := ' select ' || v_error_code || ' as error_code, rtrim(''' || v_error_msg || ''') as error_msg from dual'; OPEN c_result FOR v_sql; RETURN; END; END IF; END; ELSE BEGIN v_command := 'CM_SVC_SET'; select RPAD('!' || RPAD(i_cust, 10, ' ') || RPAD(TO_CHAR(i_outlet_num), 10, ' ') || RPAD(TO_CHAR(i_wip_number), 10, ' ') || RPAD(v_oper_corp, 10, ' ') || RPAD(i_device_kind, 1, ' ') || RPAD(v_box_type, 1, ' '), 50, ' ') || v_command || ';' || RPAD(v_transaction_id, 17, ' ') || ';' || v_cm_num || ';' || RPAD(v_class_id, 1, ' ') || ';' || to_char(v_ip_cnt)|| ';' || v_l3_code || ';' || v_l2_code || ';' || '1' || ';' || v_l2_port || ';' || v_ent_name || ';' into v_nmsstring from dual; EXCEPTION WHEN OTHERS THEN v_error_code := -19; v_error_msg := ' 예상치 않은 오류 ['||SQLERRM||']'||v_error_code; v_sql := ' select ' || v_error_code || ' as error_code, rtrim(''' || v_error_msg || ''') as error_msg from dual'; OPEN c_result FOR v_sql; RETURN; END; END IF; END; ELSIF i_work_type = '2' THEN BEGIN IF v_device_gbn = 'A' THEN BEGIN v_command := 'PON_CM_STOP'; v_nmsstring := v_command || ';' || v_cm_num || ';' || 'T' ; EXCEPTION WHEN OTHERS THEN v_error_code := -16; v_error_msg := ' 예상치 않은 오류 ['||SQLERRM||']'||v_error_code; v_sql := ' select ' || v_error_code || ' as error_code, rtrim(''' || v_error_msg || ''') as error_msg from dual'; OPEN c_result FOR v_sql; RETURN; END; ELSIF v_device_gbn = 'C' THEN BEGIN v_command := 'MOCA_CM_STOP'; v_nmsstring := v_command || ';' || v_cm_num || ';' || 'T' ; EXCEPTION WHEN OTHERS THEN v_error_code := -16; v_error_msg := ' 예상치 않은 오류 ['||SQLERRM||']'||v_error_code; v_sql := ' select ' || v_error_code || ' as error_code, rtrim(''' || v_error_msg || ''') as error_msg from dual'; OPEN c_result FOR v_sql; RETURN; END; ELSE BEGIN v_command := 'CM_STOP'; v_nmsstring := v_command || ';' || v_transaction_id || ';' || v_cm_num || ';' || v_l3_code || ';' || v_l2_code || ';' || '1' || ';' || v_l2_port || ';' || v_ent_name || ';' ; EXCEPTION WHEN OTHERS THEN v_error_code := -19; v_error_msg := ' 예상치 않은 오류 ['||SQLERRM||']'||v_error_code; v_sql := ' select ' || v_error_code || ' as error_code, rtrim(''' || v_error_msg || ''') as error_msg from dual'; OPEN c_result FOR v_sql; RETURN; END; END IF; END; ELSIF i_work_type = '4' AND i_device_kind = '2' THEN BEGIN v_command := 'CM_TECH_SET'; select RPAD('!' || RPAD(i_cust, 10, ' ') || RPAD(TO_CHAR(i_outlet_num), 10, ' ') || RPAD(TO_CHAR(i_wip_number), 10, ' ') || RPAD(v_oper_corp, 10, ' ') || RPAD(i_device_kind, 1, ' ') || RPAD(v_box_type, 1, ' '), 50, ' ') || v_command || ';' || v_transaction_id || ';' || v_cm_num || ';' || 'T' || ';' || to_char(v_ip_cnt) || ';' into v_nmsstring from dual; EXCEPTION WHEN OTHERS THEN v_error_code := -20; v_error_msg := ' 예상치 않은 오류 ['||SQLERRM||']'||v_error_code; v_sql := ' select ' || v_error_code || ' as error_code, rtrim(''' || v_error_msg || ''') as error_msg from dual'; OPEN c_result FOR v_sql; RETURN; END; END IF; END IF; --수정----------------------------------------------------------------------------------------------------------------------------------- IF v_device_gbn IN ('2', '8', '9') THEN BEGIN v_sql := ' select ' || v_error_code || ' as error_code, rtrim(''' || v_error_msg || ''') as error_msg, ' || ' nms_ip_address, nms_ip_port, rtrim( ''' || v_nmsstring || ''') as nmsstring ' || ' from system_param ' || ' where corp = ''' || v_oper_corp || ''' '; EXCEPTION WHEN OTHERS THEN v_error_code := -21; v_error_msg := ' 예상치 않은 오류 ['||SQLERRM||']'||v_error_code; v_sql := ' select ' || v_error_code || ' as error_code, rtrim(''' || v_error_msg || ''') as error_msg from dual'; OPEN c_result FOR v_sql; RETURN; END; ELSIF v_device_gbn IN ('A', 'C') THEN BEGIN IF v_nmsstring is not null THEN v_sql := ' select ' || v_error_code || ' as error_code, rtrim(''' || v_error_msg || ''') as error_msg, ' || ' moca_ip_address as nms_ip_address, moca_ip_port as nms_ip_port, rtrim( ''' || v_nmsstring || ''') as nmsstring ' || ' from system_param ' || ' where corp = ''' || v_oper_corp || ''''; END IF; IF v_nmsstring2 is not null THEN IF v_nmsstring is not null THEN v_sql := v_sql || ' union all ' ; END IF; v_sql := v_sql || ' select ' || v_error_code || ' as error_code, rtrim(''' || v_error_msg || ''') as error_msg, ' || ' moca_ip_address as nms_ip_address, moca_ip_port as nms_ip_port, rtrim( ''' || v_nmsstring2 || ''') as nmsstring ' || ' from system_param ' || ' where corp = ''' || v_oper_corp || ''''; END IF; EXCEPTION WHEN OTHERS THEN v_error_code := -21; v_error_msg := ' 예상치 않은 오류 ['||SQLERRM||']'||v_error_code; v_sql := ' select ' || v_error_code || ' as error_code, rtrim(''' || v_error_msg || ''') as error_msg from dual'; OPEN c_result FOR v_sql; RETURN; END; END IF; --수정----------------------------------------------------------------------------------------------------------------------------------- OPEN c_result FOR v_sql; EXCEPTION WHEN OTHERS THEN v_error_code := -99; v_error_msg := ' 프로시져 호출[nms_send_data]시 예상치 않은 오류'; v_sql := ' select ' || v_error_code || ' as error_code , ''' || rtrim(v_error_msg) || '[ERRMSG]: ' || SQLERRM|| ''' as error_msg from dual'; OPEN c_result FOR v_sql; RETURN; END; /