CREATE OR REPLACE TRIGGER QMAILB.T_MSG_SI_TRIGGERAFTER INSERTON QMAILB.T_MSG_MO REFERENCING NEW AS NEW OLD AS OLDFOR EACH ROWDECLAREtmpVar NUMBER; v_link_id t_msg_mt.LINK_ID% type; v_src_termid t_msg_mt.SRC_TERMID% type; v_dst_termid t_msg_mt.DST_TERMID% type; v_dst_termtype t_msg_mt.DST_TERMTYPE% type; v_fee_termid t_msg_mt.FEE_TERMID% type; v_fee_termtype t_msg_mt.FEE_TERMTYPE% type; v_fee_type t_msg_mt.FEE_TYPE% type; v_fee_code t_msg_mt.FEE_CODE% type; v_fee_usertype t_msg_mt.FEE_USERTYPE% type; v_service_code t_msg_mt.SERVICE_CODE% type; v_mt_type t_msg_mt.MT_TYPE% type; v_udhi t_msg_mt.UDHI% type; v_pid t_msg_mt.PID% type; v_schedule_time t_msg_mt.SCHEDULE_TIME% type; v_expire_time t_msg_mt.EXPIRE_TIME% type; v_pk_total t_msg_mt.PK_TOTAL% type; v_pk_num t_msg_mt.PK_NUM% type; v_report_flag t_msg_mt.REPORT_FLAG% type; v_msg_level t_msg_mt .Msg_level% type; v_msg_format t_msg_mt.msg_format% type; v_msg t_msg_mt.msg% type; v_msg_uid t_msg_mt.msg_uid% type;
v_url mobile_type_list.link_url% type; v_explain mobile_type_list.explain_info% type; v_phone_type mobile_type_list.mobile_type% type; v_mo_msg t_msg_mo.MSG% type; v_version mobile_type_list.VERSION% type; v_ip vendor.VENDOR_IP% type; v_mail_addr push_user.EMAIL_ADDR% type; v_pop_port cef.CMPOP_PORT% type; v_smtp_port cef.CMSMTP_PORT% type; v_dst_port cef.CMSMTP_PORT% type; v_src_port cef.CMSMTP_PORT% type; - v_ca mobile_type_list.mobile_type% type; strLength number;
/ ************************************************** ************************************* Name: Purpose:
Revisions: Ver Date Author Description ------------------ -------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------- 1.0 2005-4-6 1. Created this Trigger.
NOTES:
Automatically Available Auto Replace Keywords: Object Name: Sysdate: 2005-4-6 Date: 2005-4-6, 13:53:23:23 Username: (Set in Toad Options) , Proc Templates) Table Name: (SET in the "New PL / SQL Object" Dialog) Trigger Options: (SET in the "new pl / sql object" Dialog) ************* *********************************************************** ************** / beginv_link_id: = null; v_src_termid: =: new.dst_termid; v_dst_termid: =: new.SRC_TERMID; v_dst_termtype: = 0; v_fee_termid: = null; v_fee_termtype: = 0 ; v_fee_type: = '01'; v_fee_code: = '00'; v_fee_usertype: = 0; v_mt_type: = 0; v_pid: = 0; v_schedule_time: = null; v_expire_time: = null; v_pk_total: = 0; v_pk_num: = 0; v_report_flag: = 0; v_msg_level: = 2; v_msg_uid: = null;
V_MO_MSG: = Upper (: new.msg); - ************************************************ ************* - ******************** Send Si *********** ******* - **************************************************** **********
IF SUBSTR (V_MO_MSG, 0, 2) = 'Si' Then Strlength: = Length (Substr (V_MO_MSG, 3)); --NOT HAVE ACCOUN Pushmail, Only Send The Soft Link Bases on The Mobile Type and Number; if Strlength > 0 then v_phone_type: = substr (v_mo_msg, 3); select mobile_type_list.LINK_URL, mobile_type_list.EXPLAIN_INFO into v_url, v_explain from mobile_type_list where mobile_type = v_phone_type;
--have account in pushmail yet, else select mobile_type_list.LINK_URL, mobile_type_list.EXPLAIN_INFO, push_user.DEV_ID into v_url, v_explain, v_phone_type from mobile_type_list, push_user where push_user.PUSH_USER_ID =: new.src_termid and push_user.DEV_ID = mobile_type_list.MOBILE_TYPE; end if; v_msg: = system.SENDSMS.makeSiData (v_url, v_explain, v_phone_type); if substr (v_phone_type, 0,3) = 'DPD' then v_udhi: = 0; v_msg_format: = 15; else v_udhi: = 1; v_msg_format: = 4; end if; --insert the record to t_msg_mt table insert into T_MSG_MT (lINK_ID, SRC_TERMID, DST_TERMID, DST_TERMTYPE, FEE_TERMID, FEE_TERMTYPE, SERVICE_CODE, FEE_TYPE, FEE_CODE, FEE_USERTYPE, MT_TYPE, UDHI, PID, SCHEDULE_TIME, eXPIRE_TIME, PK_TOTAL, PK_NUM, REPORT_FLAG, MSG_LEVEL, MSG_FORMAT, MSG, msg_uid) values (v_LINK_ID, v_SRC_TERMID, v_DST_TERMID, v_DST_TERMTYPE, v_FEE_TERMID, v_FEE_TERMTYPE, v_SERVICE_CODE, v_FEE_TYPE, v_FEE_CODE, v_FEE_USERTYPE, v_MT_TYPE, v_UDHI, v_PID, v_SCHEDULE_TIME, v_EXPIRE_TIM E, V_PK_TOTAL, V_PK_NUM, V_REPORT_FLAG, V_MSG_LEVEL, V_MSG_FORMAT, V_MSG, V_MSG_UID); endiff; - *********************************************** ************** - ************* SEND CA ********* *******8 --**************************************** *********** if v_mo_msg = 'ca' Then
select mobile_type_list.LINK_URL, mobile_type_list.EXPLAIN_INFO into v_url, v_explain from mobile_type_list where mobile_type = 'CA'; v_phone_type: = 'CA'; v_msg: = system.SENDSMS.makeCaData (v_url, v_explain, v_phone_type);
v_udhi: = 1; v_msg_format: = 4; --insert the record to t_msg_mt table insert into T_MSG_MT (LINK_ID, SRC_TERMID, DST_TERMID, DST_TERMTYPE, FEE_TERMID, FEE_TERMTYPE, SERVICE_CODE, FEE_TYPE, FEE_CODE, FEE_USERTYPE, MT_TYPE, UDHI, PID, SCHEDULE_TIME, eXPIRE_TIME, PK_TOTAL, PK_NUM, REPORT_FLAG, MSG_LEVEL, MSG_FORMAT, MSG, msg_uid) values (v_LINK_ID, v_SRC_TERMID, v_DST_TERMID, v_DST_TERMTYPE, v_FEE_TERMID, v_FEE_TERMTYPE, v_SERVICE_CODE, v_FEE_TYPE, v_FEE_CODE, v_FEE_USERTYPE, v_MT_TYPE, v_UDHI, v_PID, v_SCHEDULE_TIME, v_EXPIRE_TIME, v_PK_TOTAL, v_PK_NUM, v_REPORT_FLAG, v_MSG_LEVEL, v_MSG_FORMAT, v_MSG, v_msg_uid); end if; if v_mo_msg = 'CADPD' thenselect mobile_type_list.LINK_URL, mobile_type_list.EXPLAIN_INFO into v_url, v_explain from mobile_type_list where mobile_type = 'CA'; v_phone_type: = 'DPD'; v_msg: = system.sendsms.makecadata (v_url, v_explain, v_phone_type);
v_udhi: = 0; v_msg_format: = 15;
--insert the record to t_msg_mt table insert into T_MSG_MT (LINK_ID, SRC_TERMID, DST_TERMID, DST_TERMTYPE, FEE_TERMID, FEE_TERMTYPE, SERVICE_CODE, FEE_TYPE, FEE_CODE, FEE_USERTYPE, MT_TYPE, UDHI, PID, SCHEDULE_TIME, EXPIRE_TIME, PK_TOTAL, PK_NUM, REPORT_FLAG, MSG_LEVEL, MSG_FORMAT, MSG, msg_uid) values (v_LINK_ID, v_SRC_TERMID, v_DST_TERMID, v_DST_TERMTYPE, v_FEE_TERMID, v_FEE_TERMTYPE, v_SERVICE_CODE, v_FEE_TYPE, v_FEE_CODE, v_FEE_USERTYPE, v_MT_TYPE, v_UDHI, v_PID, v_SCHEDULE_TIME, v_EXPIRE_TIME, v_PK_TOTAL, v_PK_NUM, v_REPORT_FLAG, v_MSG_LEVEL, v_MSG_FORMAT, v_MSG, V_msg_uid); end if; - **************************************************** ******** - ****************** SEND SETTING **************** - * *********************************************************** IF SUBSTR (V_MO_MSG, 0, 2) = 'St' Then
tmpVar: = 0; --set msg parameters from tables bases on the mobile number (src_termid) select mobile_type_list.LINK_URL, mobile_type_list.EXPLAIN_INFO, mobile_type_list.MOBILE_TYPE, cef.SMS_NUMBER, mobile_type_list.VERSION, vendor.vendor_ip, push_user.email_addr, cef .cmpop_port, cef.cmsmtp_port into v_url, v_explain, v_phone_type, v_service_code, v_version, v_ip, v_mail_addr, v_pop_port, v_smtp_port from push_user, mobile_type_list, cef, vendor where push_user.PUSH_USER_ID =: new.src_termid and mobile_type_list.MOBILE_TYPE = push_user.DEV_ID and Push_user.cid = cef.cid and vendor.vendor_id = cef.vendor_id; if Substr (Upper (v_phone_type), 0, 3) = 'DPD' THEN V_DST_PORT: = '2948'; Else V_DST_PORT: = '16000'; end if ; v_src_port: = '9200'; v_version: = '2.0'; v_msg: = system.SENDSMS.makeSettingData (v_dst_port, v_src_port, v_version, 'Y', 'N', '9999', v_ip, v_smtp_port, v_ip, v_pop_port, v_mail_addr); dbms_output.put _line (v_msg); v_udhi: = 1; v_msg_format: = 4; --insert the record to t_msg_mt table insert into T_MSG_MT (LINK_ID, SRC_TERMID, DST_TERMID, DST_TERMTYPE, FEE_TERMID, FEE_TERMTYPE, SERVICE_CODE, FEE_TYPE, FEE_CODE, FEE_USERTYPE, MT_TYPE, UDHI , PID, SCHEDULE_TIME, eXPIRE_TIME, PK_TOTAL, PK_NUM, REPORT_FLAG, MSG_LEVEL, MSG_FORMAT, MSG, msg_uid) values (v_LINK_ID, v_SRC_TERMID, v_DST_TERMID, v_DST_TERMTYPE, v_FEE_TERMID, v_FEE_TERMTYPE, v_SERVICE_CODE, v_FEE_TYPE, v_FEE_CODE, v_FEE_USERTYPE, v_MT_TYPE, v_UDHI, v_PID, v_SCHEDULE_TIME , v_expire_time, v_PK_TOTAL, V_PK_NUM, V_REPORT_FLAG, V_MSG_LEVEL, V_MSG_FORMAT, V_MSG, V_MSG_UID, END IF;
